Introduction:
This Post illustrates the steps required to Fetch Open work orders in Oracle EBS.
Query to Fetch the Open Work Orders
SELECT DISTINCT a.job, a.item item, a.item_description, a.product_category,
a.assembly_qty, a.quantity_completed, a.start_date,
a.scheduled_completion_date, a.released_start_date,
a.released_completed_date, a.close_date, a.status, a.reason,
a.component component, a.component_item_description,
a.component_product_category, a.component_uom,
a.quantity_per_assembly, a.required_quantity,
a.quantity_issued, a.job_type, a.job_class, a.item_id,
a.comp_item_id, a.organization_id
FROM (SELECT we.wip_entity_name job, msi.segment1 item,
msi.description item_description,
msi.inventory_item_id item_id,
(SELECT conversion_rate
FROM mtl_uom_conversions
WHERE inventory_item_id =
msi.inventory_item_id)
pack_size,
(SELECT item_cost
FROM cst_item_cost_type_v
WHERE inventory_item_id =
msi.inventory_item_id
AND organization_id = we.organization_id
AND cost_type_id = 1) frozen_cost,
micv1.category_concat_segs product_category,
wdj.start_quantity assembly_qty,
wdj.quantity_completed quantity_completed,
wdj.scheduled_start_date start_date,
wdj.scheduled_completion_date,
wdj.date_released released_start_date,
wdj.date_completed released_completed_date,
wdj.date_closed close_date, ml1.meaning status,
wdj.attribute6 reason, msi1.segment1 component,
msi1.description component_item_description,
micv3.category_concat_segs component_product_category,
msi1.primary_unit_of_measure component_uom,
msi1.inventory_item_id comp_item_id,
(SELECT conversion_rate
FROM mtl_uom_conversions
WHERE inventory_item_id =
msi1.inventory_item_id
AND uom_code NOT IN (‘PL’)) comp_pack_size,
(SELECT item_cost
FROM cst_item_cost_type_v
WHERE inventory_item_id =
msi1.inventory_item_id
AND organization_id = we.organization_id
AND cost_type_id = 1) comp_frozen_cost,
wro.quantity_per_assembly, wro.required_quantity,
wro.quantity_issued, we.organization_id,
we.wip_entity_id, ml2.meaning job_type,
wdj.class_code job_class, SYSDATE creation_date,
‘-1’ created_by
FROM mfg_lookups ml1,
mfg_lookups ml2,
wip_entities we,
wip_discrete_jobs wdj,
mtl_item_categories_v micv1,
mtl_system_items_b msi,
wip_requirement_operations wro,
mtl_item_categories_v micv3,
mtl_system_items_b msi1
WHERE ml1.lookup_type = ‘WIP_JOB_STATUS’
AND ml1.lookup_code = wdj.status_type
AND we.wip_entity_id = wdj.wip_entity_id
AND ml2.lookup_type = ‘WIP_DISCRETE_JOB’
AND ml2.lookup_code = wdj.job_type
AND msi.inventory_item_id = wdj.primary_item_id
AND msi.organization_id = we.organization_id
AND msi.organization_id = micv1.organization_id
AND msi.inventory_item_id = micv1.inventory_item_id
AND micv1.category_set_name IN (‘Inventory’)
AND micv1.category_concat_segs =
micv1.category_concat_segs
AND TRUNC (wdj.creation_date) >= ’01-JAN-2015′
AND ml2.lookup_code = ml2.lookup_code
AND wdj.class_code = wdj.class_code
AND ml1.lookup_code IN (1, 3, 6)
AND wro.wip_entity_id = we.wip_entity_id
AND wro.organization_id = we.organization_id
AND wro.organization_id = micv3.organization_id
AND wro.inventory_item_id = micv3.inventory_item_id
AND micv3.category_set_name IN (‘Inventory’)
AND micv3.category_concat_segs =
micv3.category_concat_segs
AND wro.organization_id = msi1.organization_id
AND wro.inventory_item_id = msi1.inventory_item_id) a
Queries
Do drop a note by writing us at venkatesh.b@doyensys.com or use the comment section below to ask your questions
