查詢ATO流程中SO與WIP Job的相關資訊的SQL

查詢ATO流程中SO與WIP Job的相關資訊的SQL[@more@]

select we.wip_entity_name wo,
wdj.organization_id,
wdj.creation_date,
wdj.scheduled_start_date,
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.start_quantity,
wr.segment1 so,
ool.line_number,
ool.ordered_item,
ool.ordered_quantity
from apps.wip_discrete_jobs wdj,
apps.wip_reservations_v wr,
apps.wip_entities we,
apps.oe_order_lines_all ool
where wdj.source_line_id = wr.demand_source_line_id(+)
and wdj.wip_entity_id = we.wip_entity_id
and wr.demand_source_line_id = ool.line_id(+)
--and we.wip_entity_name ='186540'
and wdj.organization_id = 1032
and trunc(wdj.creation_date) = trunc(sysdate)

========================================================

select wip_so.sale_order_number so_num,
wip_so.line_number so_line_num,
------------------------------------------
wdj.rowid row_id,
wdj.organization_id,
wdj.wip_entity_id,
we.wip_entity_name,
wdj.primary_item_id,
msik.segment1 fg_item_number,
msik.description fg_item_desc,
msik.primary_uom_code fg_uom_code,
wdj.completion_subinventory,
wdj.scheduled_start_date,
wdj.scheduled_completion_date,
wdj.creation_date job_creation_date,
wdj.created_by job_created_by,
wdj.last_update_date job_last_update_date,
wdj.last_updated_by job_last_updated_by,
wdj.description job_desc,
wdj.status_type,
wdj.job_type,
lu3.meaning job_type_name,
wdj.wip_supply_type comp_supply_type,
wdj.class_code,
wdj.date_released,
wdj.date_completed,
wdj.date_closed,
wdj.start_quantity,
decode(wdj.quantity_completed, 0, null, wdj.quantity_completed) "QUANTITY_COMPLETED",
decode(wdj.start_quantity - wdj.quantity_completed -
wdj.quantity_scrapped,
0,
null,
wdj.start_quantity - wdj.quantity_completed -
wdj.quantity_scrapped) "QUANTITY_REMAINING",
decode(wdj.quantity_scrapped, 0, null, wdj.quantity_scrapped) "QUANTITY_SCRAPPED",
wdj.net_quantity,
wdj.common_bom_sequence_id,
wdj.common_routing_sequence_id,
wdj.bom_revision,
wdj.routing_revision,
wdj.bom_revision_date,
wdj.routing_revision_date,
wdj.completion_locator_id,
decode(wdj.completion_subinventory, null, 1, msub.locator_type) sub_locator_control,
wdj.demand_class,
wsg.schedule_group_name,
wdj.schedule_group_id,
wdj.build_sequence,
wl.line_code,
wdj.line_id,
--WDJ.ATTRIBUTE1,
--WDJ.ATTRIBUTE2,
--WDJ.ATTRIBUTE3,
lu1.meaning status_type_disp,
lu2.meaning wip_supply_type_disp,
wdj.overcompletion_tolerance_type,
wdj.overcompletion_tolerance_value,
wdj.priority,
we.entity_type,
-----------------------------------------------
wro.rowid comp_row_id,
wro.inventory_item_id com_item_id,
msik1.segment1 comp_item_code,
msik1.description comp_item_desc,
msik1.item_type comp_item_type,
msik1.primary_uom_code comp_item_uom,
msik1.attribute9 supplier_name,
msik1.buyer_id,
wro.operation_seq_num,
wro.repetitive_schedule_id,
wro.last_update_date comp_last_update_date,
wro.last_updated_by comp_last_updated_by,
wro.creation_date comp_creation_date,
wro.created_by comp_created_by,
wro.department_id,
bd.department_code,
wro.wip_supply_type,
ml1.meaning wip_supply_meaning,
wro.date_required,
wro.required_quantity,
decode(wro.quantity_issued, 0, null, wro.quantity_issued) quantity_issued,
decode((wro.required_quantity - wro.quantity_issued),
0,
null,
decode(sign(wro.required_quantity),
-1 * sign(wro.quantity_issued),
(wro.required_quantity - wro.quantity_issued),
decode(sign(abs(wro.required_quantity) -
abs(wro.quantity_issued)),
-1,
null,
(wro.required_quantity - wro.quantity_issued)))) quantity_open,
wro.quantity_per_assembly,
wro.supply_subinventory,
wro.supply_locator_id,
t.long_description,
msik1.dimension_uom_code,
msik1.unit_length,
msik1.unit_width,
msik1.unit_height,
decode(msik1.unit_length, null, null, 'D') || '' ||
msik1.unit_length ||
decode(msik1.unit_length,
null,
null,
decode(msik1.unit_width, null, null, ' x ')) ||
decode(msik1.unit_width, null, null, 'W') || msik1.unit_width ||
decode(msik1.unit_length || msik1.unit_width,
null,
null,
decode(msik1.unit_height, null, null, ' x ')) ||
decode(msik1.unit_height, null, null, 'H') || msik1.unit_height comp_item_dimension
from mtl_secondary_inventories msub,
mfg_lookups lu1,
mfg_lookups lu2,
mfg_lookups lu3,
wip_entities we,
wip_discrete_jobs wdj,
wip_requirement_operations wro,
bom_departments bd,
wip_lines wl,
wip_schedule_groups wsg,
mtl_system_items_b msik,
mtl_system_items_b msik1,
mfg_lookups ml1,
mtl_system_items_tl t,
(select mr.reservation_id,
mr.organization_id org_id,
mr.demand_source_type_id,
mr.demand_source_name,
mr.demand_source_header_id,
mr.demand_source_line_id,
ool.line_number,
mso.segment1 sale_order_number,
mso.segment2, /* 10 */
mso.segment3
from mtl_reservations mr,
oe_order_lines_all ool,
mtl_sales_orders mso
where mso.sales_order_id = mr.demand_source_header_id
and mr.demand_source_line_id = ool.line_id
and mr.demand_source_type_id in (2, 8) /* INV_RESERVATION_GLOBAL.G_SOURCE_TYPE_OE */
and mr.supply_source_type_id = 5) wip_so --,
--MTL_ITEM_CATEGORIES MIC,
--MTL_CATEGORIES_B MCB
where we.wip_entity_id = wdj.wip_entity_id
and wdj.wip_entity_id = wro.wip_entity_id
and wdj.status_type = 3 --Job Status:Released
and msub.secondary_inventory_name(+) = wdj.completion_subinventory
and msub.organization_id(+) = wdj.organization_id
and wsg.schedule_group_id(+) = wdj.schedule_group_id
and wl.line_id(+) = wdj.line_id
and wl.organization_id(+) = wdj.organization_id
and lu2.lookup_type = 'WIP_SUPPLY'
and lu1.lookup_type = 'WIP_JOB_STATUS'
and lu3.lookup_type = 'WIP_DISCRETE_JOB'
and lu1.lookup_code = wdj.status_type
and lu2.lookup_code = wdj.wip_supply_type
and lu3.lookup_code = wdj.job_type
and msik.inventory_item_id = wdj.primary_item_id
and msik.organization_id = wdj.organization_id
and msik1.inventory_item_id = wro.inventory_item_id
and msik1.organization_id = wro.organization_id
and bd.department_id(+) = wro.department_id
and ml1.lookup_code = wro.wip_supply_type
and ml1.lookup_type = 'WIP_SUPPLY'
and msik1.inventory_item_id = t.inventory_item_id
and msik1.organization_id = t.organization_id
and t.language = userenv('LANG')
and wdj.source_line_id = wip_so.demand_source_line_id(+)
--AND MSIK.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
--AND MSIK.ORGANIZATION_ID = MIC.ORGANIZATION_ID
--AND MIC.CATEGORY_SET_ID = 63 --CATEGORY_SET_NAME ='MEW_WIP_CLASS'
--AND MIC.CATEGORY_ID = MCB.CATEGORY_ID
--AND MCB.SEGMENT1 = '886'
--AND MCB.SEGMENT2 = 'S'
--AND MCB.SEGMENT3 = '2200'
--AND WE.WIP_ENTITY_ID = 910797
--AND WE.WIP_ENTITY_NAME = '1009469'
and wdj.organization_id = 112