eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WIP_OSP_REQS_POS_V

Source

View Text - Preformatted


SELECT ph.SEGMENT1, ph.type_lookup_code, polc.displayed_field, ph.authorization_status, ps.NEED_BY_DATE, ps.PROMISED_DATE, bcd1.calendar_date + (DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date) - TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date))), WIP_OSP.ConvertToPrimaryMoveQty(pl.item_id, pd.destination_organization_id, GREATEST(pd.QUANTITY_ORDERED - NVL(pd.QUANTITY_DELIVERED, 0) - NVL(pd.QUANTITY_CANCELLED, 0), 0), pl.unit_meas_lookup_code, msi.primary_unit_of_measure, (select wor.usage_rate_or_amount from wip_operation_resources wor where wor.wip_entity_id = pd.wip_entity_id and NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1) and wor.operation_seq_num = pd.wip_operation_seq_num and wor.organization_id = pd.destination_organization_id and wor.resource_seq_num = pd.wip_resource_seq_num)), msi.primary_unit_of_measure, pd.WIP_ENTITY_ID, pd.WIP_REPETITIVE_SCHEDULE_ID, pd.DESTINATION_ORGANIZATION_ID, pd.WIP_OPERATION_SEQ_NUM, pd.wip_resource_seq_num, bso.operation_code, pd.PO_HEADER_ID, to_number(null), pd.PO_LINE_ID, pd.LINE_LOCATION_ID, pd.PO_DISTRIBUTION_ID, pd.org_id, pl.item_id, pd.QUANTITY_ORDERED, pl.unit_meas_lookup_code, null, to_number(null), to_number(null), pd.CREATION_DATE, pd.CREATED_BY, pd.LAST_UPDATE_DATE, pd.LAST_UPDATED_BY, ps.cancel_flag FROM WIP_ENTITIES WE, MTL_SYSTEM_ITEMS MSI, WIP_OPERATIONS WO, BOM_STANDARD_OPERATIONS BSO, PO_DISTRIBUTIONS_ALL PD, PO_LINE_LOCATIONS_ALL PS, PO_LINES_ALL PL, PO_HEADERS_ALL PH, PO_DOCUMENT_TYPES_ALL PDT, PO_LOOKUP_CODES POLC, mtl_system_items msi2, bom_calendar_dates bcd1, bom_calendar_dates bcd2 , mtl_parameters mp, wip_operations wo2 WHERE PDT.DOCUMENT_TYPE_CODE = 'PO' AND PH.TYPE_LOOKUP_CODE = 'STANDARD' AND PH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE AND PH.ORG_ID = PDT.ORG_ID AND polc.lookup_code = nvl(ph.authorization_status, 'INCOMPLETE') AND polc.lookup_type = 'AUTHORIZATION STATUS' AND PD.PO_HEADER_ID = PH.PO_HEADER_ID AND PD.PO_LINE_ID = PL.PO_LINE_ID AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND PD.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND PD.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID AND PD.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND NVL(PD.WIP_REPETITIVE_SCHEDULE_ID,-1) = NVL(WO.REPETITIVE_SCHEDULE_ID,-1) AND WO.standard_operation_id = BSO.standard_operation_id(+) AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+) AND WE.PRIMARY_ITEM_ID = MSI.inventory_item_id(+) AND WE.organization_id = MSI.organization_id(+) AND pl.item_id = msi2.inventory_item_id AND pd.destination_organization_id = msi2.organization_id AND mp.organization_id = pd.destination_organization_id AND wo2.organization_id = wo.organization_id AND wo2.wip_entity_id = wo.wip_entity_id AND NVL(wo2.repetitive_schedule_id, -1) = NVL(wo.repetitive_schedule_id, -1) AND ((wo.next_operation_seq_num IS NOT NULL AND wo.next_operation_seq_num = wo2.operation_seq_num) OR (wo.next_operation_seq_num IS NULL AND wo2.operation_seq_num = wo.operation_seq_num)) AND bcd2.calendar_code = mp.calendar_code AND bcd2.exception_set_id = mp.calendar_exception_set_id AND bcd2.calendar_date = TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date)) AND bcd1.calendar_code = mp.calendar_code AND bcd1.exception_set_id = mp.calendar_exception_set_id AND bcd1.seq_num = (bcd2.next_seq_num - CEIL(NVL(msi2.postprocessing_lead_time,0))) UNION ALL SELECT ph.SEGMENT1||'-'||pr.RELEASE_NUM, ph.type_lookup_code, polc.displayed_field, pr.authorization_status, ps.NEED_BY_DATE, ps.PROMISED_DATE, bcd1.calendar_date + (DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date) - TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date))), WIP_OSP.ConvertToPrimaryMoveQty(pl.item_id, pd.destination_organization_id, GREATEST(pd.QUANTITY_ORDERED - NVL(pd.QUANTITY_DELIVERED, 0) - NVL(pd.QUANTITY_CANCELLED, 0),0), pl.unit_meas_lookup_code, msi.primary_unit_of_measure, (select wor.usage_rate_or_amount from wip_operation_resources wor where wor.wip_entity_id = pd.wip_entity_id and NVL(wor.repetitive_schedule_id, -1) = NVL(pd.wip_repetitive_schedule_id, -1) and wor.operation_seq_num = pd.wip_operation_seq_num and wor.organization_id = pd.destination_organization_id and wor.resource_seq_num = pd.wip_resource_seq_num)), msi.primary_unit_of_measure, pd.WIP_ENTITY_ID, pd.WIP_REPETITIVE_SCHEDULE_ID, pd.DESTINATION_ORGANIZATION_ID, pd.WIP_OPERATION_SEQ_NUM, pd.wip_resource_seq_num, bso.OPERATION_CODE, pd.PO_HEADER_ID, pr.po_release_id, pd.PO_LINE_ID, pd.LINE_LOCATION_ID, pd.PO_DISTRIBUTION_ID, pd.org_id, pl.item_id, pd.QUANTITY_ORDERED, pl.unit_meas_lookup_code, null, to_number(null), to_number(null), pd.CREATION_DATE, pd.CREATED_BY, pd.LAST_UPDATE_DATE, pd.LAST_UPDATED_BY, ps.cancel_flag FROM WIP_ENTITIES WE, MTL_SYSTEM_ITEMS MSI, WIP_OPERATIONS WO, BOM_STANDARD_OPERATIONS BSO, PO_DISTRIBUTIONS_ALL PD, PO_LINE_LOCATIONS_ALL PS, PO_LINES_ALL PL, PO_DOCUMENT_TYPES_ALL PDT, PO_HEADERS_ALL PH, PO_LOOKUP_CODES POLC, PO_RELEASES_ALL PR, mtl_system_items msi2, bom_calendar_dates bcd1, bom_calendar_dates bcd2 , mtl_parameters mp, wip_operations wo2 WHERE PDT.DOCUMENT_TYPE_CODE = 'RELEASE' AND PH.TYPE_LOOKUP_CODE = 'BLANKET' AND PR.RELEASE_TYPE = PDT.DOCUMENT_SUBTYPE AND PR.org_id = PDT.org_id AND polc.lookup_code = nvl(pr.authorization_status, 'INCOMPLETE') AND polc.lookup_type = 'AUTHORIZATION STATUS' AND PR.po_release_id = PS.po_release_id AND PR.PO_HEADER_ID = PH.PO_HEADER_ID AND PD.PO_HEADER_ID = PH.PO_HEADER_ID AND PD.PO_LINE_ID = PL.PO_LINE_ID AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND PD.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND PD.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID AND PD.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND NVL(PD.WIP_REPETITIVE_SCHEDULE_ID,-1) = NVL(WO.REPETITIVE_SCHEDULE_ID, -1) AND WO.standard_operation_id = BSO.standard_operation_id(+) AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+) AND WE.PRIMARY_ITEM_ID = MSI.inventory_item_id(+) AND WE.organization_id = MSI.organization_id(+) AND pl.item_id = msi2.inventory_item_id AND pd.destination_organization_id = msi2.organization_id AND mp.organization_id = pd.destination_organization_id AND wo2.organization_id = wo.organization_id AND wo2.wip_entity_id = wo.wip_entity_id AND NVL(wo2.repetitive_schedule_id, -1) = NVL(wo.repetitive_schedule_id, -1) AND ((wo.next_operation_seq_num IS NOT NULL AND wo.next_operation_seq_num = wo2.operation_seq_num) OR (wo.next_operation_seq_num IS NULL AND wo2.operation_seq_num = wo.operation_seq_num)) AND bcd2.calendar_code = mp.calendar_code AND bcd2.exception_set_id = mp.calendar_exception_set_id AND bcd2.calendar_date = TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date)) AND bcd1.calendar_code = mp.calendar_code AND bcd1.exception_set_id = mp.calendar_exception_set_id AND bcd1.seq_num = (bcd2.next_seq_num - CEIL(NVL(msi2.postprocessing_lead_time,0))) UNION ALL SELECT null, 'REQUISITION', polc.displayed_field, prh.authorization_status, prl.NEED_BY_DATE, to_date(null), bcd1.calendar_date + (DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date) - TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date))), WIP_OSP.ConvertToPrimaryMoveQty(prl.item_id, prl.destination_organization_id, prl.QUANTITY, prl.unit_meas_lookup_code, msi.primary_unit_of_measure, (select wor.usage_rate_or_amount from wip_operation_resources wor where wor.wip_entity_id = prl.wip_entity_id and NVL(wor.repetitive_schedule_id, -1) = NVL(prl.wip_repetitive_schedule_id, -1) and wor.operation_seq_num = prl.wip_operation_seq_num and wor.organization_id = prl.destination_organization_id and wor.resource_seq_num = prl.wip_resource_seq_num)), msi.primary_unit_of_measure, prl.WIP_ENTITY_ID, prl.WIP_REPETITIVE_SCHEDULE_ID, prl.DESTINATION_ORGANIZATION_ID, prl.WIP_OPERATION_SEQ_NUM, prl.wip_resource_seq_num, bso.OPERATION_CODE, to_number(null), to_number(null), to_number(null), to_number(null), to_number(null), prl.org_id, prl.item_id, prl.quantity, prl.unit_meas_lookup_code, prh.segment1, prh.requisition_header_id, prl.requisition_line_id, prl.CREATION_DATE, prl.CREATED_BY, prl.LAST_UPDATE_DATE, prl.LAST_UPDATED_BY, prl.cancel_flag FROM WIP_ENTITIES WE, MTL_SYSTEM_ITEMS MSI, WIP_OPERATIONS WO, BOM_STANDARD_OPERATIONS BSO, PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL, PO_LOOKUP_CODES POLC, mtl_system_items msi2, bom_calendar_dates bcd1, bom_calendar_dates bcd2 , mtl_parameters mp, wip_operations wo2 WHERE not exists (select 1 from po_line_locations_all pll where prl.line_location_id = pll.line_location_id) AND polc.lookup_code = nvl(prh.authorization_status, 'INCOMPLETE') AND polc.lookup_type = 'AUTHORIZATION STATUS' AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID AND PRL.WIP_ENTITY_ID = WE.WIP_ENTITY_ID AND PRL.WIP_ENTITY_ID = WO.WIP_ENTITY_ID AND PRL.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID AND PRL.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND NVL(PRL.WIP_REPETITIVE_SCHEDULE_ID,-1) = NVL(WO.REPETITIVE_SCHEDULE_ID, -1) AND WO.standard_operation_id = BSO.standard_operation_id(+) AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+) AND WE.PRIMARY_ITEM_ID = MSI.inventory_item_id(+) AND WE.organization_id = MSI.organization_id(+) AND prl.item_id = msi2.inventory_item_id AND prl.destination_organization_id = msi2.organization_id AND mp.organization_id = prl.destination_organization_id AND wo2.organization_id = wo.organization_id AND wo2.wip_entity_id = wo.wip_entity_id AND NVL(wo2.repetitive_schedule_id, -1) = NVL(wo.repetitive_schedule_id, -1) AND ((wo.next_operation_seq_num IS NOT NULL AND wo.next_operation_seq_num = wo2.operation_seq_num) OR (wo.next_operation_seq_num IS NULL AND wo2.operation_seq_num = wo.operation_seq_num)) AND bcd2.calendar_code = mp.calendar_code AND bcd2.exception_set_id = mp.calendar_exception_set_id AND bcd2.calendar_date = TRUNC(DECODE(wo.next_operation_seq_num, NULL, wo.last_unit_completion_date, wo2.first_unit_start_date)) AND bcd1.calendar_code = mp.calendar_code AND bcd1.exception_set_id = mp.calendar_exception_set_id AND bcd1.seq_num = (bcd2.next_seq_num - CEIL(NVL(msi2.postprocessing_lead_time,0)))

View Text - HTML Formatted

SELECT PH.SEGMENT1
, PH.TYPE_LOOKUP_CODE
, POLC.DISPLAYED_FIELD
, PH.AUTHORIZATION_STATUS
, PS.NEED_BY_DATE
, PS.PROMISED_DATE
, BCD1.CALENDAR_DATE + (DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE) - TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE)))
, WIP_OSP.CONVERTTOPRIMARYMOVEQTY(PL.ITEM_ID
, PD.DESTINATION_ORGANIZATION_ID
, GREATEST(PD.QUANTITY_ORDERED - NVL(PD.QUANTITY_DELIVERED
, 0) - NVL(PD.QUANTITY_CANCELLED
, 0)
, 0)
, PL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, (SELECT WOR.USAGE_RATE_OR_AMOUNT
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM))
, MSI.PRIMARY_UNIT_OF_MEASURE
, PD.WIP_ENTITY_ID
, PD.WIP_REPETITIVE_SCHEDULE_ID
, PD.DESTINATION_ORGANIZATION_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.WIP_RESOURCE_SEQ_NUM
, BSO.OPERATION_CODE
, PD.PO_HEADER_ID
, TO_NUMBER(NULL)
, PD.PO_LINE_ID
, PD.LINE_LOCATION_ID
, PD.PO_DISTRIBUTION_ID
, PD.ORG_ID
, PL.ITEM_ID
, PD.QUANTITY_ORDERED
, PL.UNIT_MEAS_LOOKUP_CODE
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PD.CREATION_DATE
, PD.CREATED_BY
, PD.LAST_UPDATE_DATE
, PD.LAST_UPDATED_BY
, PS.CANCEL_FLAG
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_OPERATIONS WO
, BOM_STANDARD_OPERATIONS BSO
, PO_DISTRIBUTIONS_ALL PD
, PO_LINE_LOCATIONS_ALL PS
, PO_LINES_ALL PL
, PO_HEADERS_ALL PH
, PO_DOCUMENT_TYPES_ALL PDT
, PO_LOOKUP_CODES POLC
, MTL_SYSTEM_ITEMS MSI2
, BOM_CALENDAR_DATES BCD1
, BOM_CALENDAR_DATES BCD2
, MTL_PARAMETERS MP
, WIP_OPERATIONS WO2
WHERE PDT.DOCUMENT_TYPE_CODE = 'PO'
AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PH.TYPE_LOOKUP_CODE = PDT.DOCUMENT_SUBTYPE
AND PH.ORG_ID = PDT.ORG_ID
AND POLC.LOOKUP_CODE = NVL(PH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID
AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND PD.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND PD.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID
AND PD.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID(+)
AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+)
AND WE.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND PL.ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND PD.DESTINATION_ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WO2.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WO2.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND NVL(WO2.REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND ((WO.NEXT_OPERATION_SEQ_NUM IS NOT NULL
AND WO.NEXT_OPERATION_SEQ_NUM = WO2.OPERATION_SEQ_NUM) OR (WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WO2.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM))
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE))
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD1.SEQ_NUM = (BCD2.NEXT_SEQ_NUM - CEIL(NVL(MSI2.POSTPROCESSING_LEAD_TIME
, 0))) UNION ALL SELECT PH.SEGMENT1||'-'||PR.RELEASE_NUM
, PH.TYPE_LOOKUP_CODE
, POLC.DISPLAYED_FIELD
, PR.AUTHORIZATION_STATUS
, PS.NEED_BY_DATE
, PS.PROMISED_DATE
, BCD1.CALENDAR_DATE + (DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE) - TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE)))
, WIP_OSP.CONVERTTOPRIMARYMOVEQTY(PL.ITEM_ID
, PD.DESTINATION_ORGANIZATION_ID
, GREATEST(PD.QUANTITY_ORDERED - NVL(PD.QUANTITY_DELIVERED
, 0) - NVL(PD.QUANTITY_CANCELLED
, 0)
, 0)
, PL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, (SELECT WOR.USAGE_RATE_OR_AMOUNT
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PD.WIP_ENTITY_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND WOR.OPERATION_SEQ_NUM = PD.WIP_OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = PD.WIP_RESOURCE_SEQ_NUM))
, MSI.PRIMARY_UNIT_OF_MEASURE
, PD.WIP_ENTITY_ID
, PD.WIP_REPETITIVE_SCHEDULE_ID
, PD.DESTINATION_ORGANIZATION_ID
, PD.WIP_OPERATION_SEQ_NUM
, PD.WIP_RESOURCE_SEQ_NUM
, BSO.OPERATION_CODE
, PD.PO_HEADER_ID
, PR.PO_RELEASE_ID
, PD.PO_LINE_ID
, PD.LINE_LOCATION_ID
, PD.PO_DISTRIBUTION_ID
, PD.ORG_ID
, PL.ITEM_ID
, PD.QUANTITY_ORDERED
, PL.UNIT_MEAS_LOOKUP_CODE
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PD.CREATION_DATE
, PD.CREATED_BY
, PD.LAST_UPDATE_DATE
, PD.LAST_UPDATED_BY
, PS.CANCEL_FLAG
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_OPERATIONS WO
, BOM_STANDARD_OPERATIONS BSO
, PO_DISTRIBUTIONS_ALL PD
, PO_LINE_LOCATIONS_ALL PS
, PO_LINES_ALL PL
, PO_DOCUMENT_TYPES_ALL PDT
, PO_HEADERS_ALL PH
, PO_LOOKUP_CODES POLC
, PO_RELEASES_ALL PR
, MTL_SYSTEM_ITEMS MSI2
, BOM_CALENDAR_DATES BCD1
, BOM_CALENDAR_DATES BCD2
, MTL_PARAMETERS MP
, WIP_OPERATIONS WO2
WHERE PDT.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PR.RELEASE_TYPE = PDT.DOCUMENT_SUBTYPE
AND PR.ORG_ID = PDT.ORG_ID
AND POLC.LOOKUP_CODE = NVL(PR.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PR.PO_RELEASE_ID = PS.PO_RELEASE_ID
AND PR.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.PO_HEADER_ID = PH.PO_HEADER_ID
AND PD.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PS.LINE_LOCATION_ID
AND PD.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND PD.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND PD.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID
AND PD.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND NVL(PD.WIP_REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID(+)
AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+)
AND WE.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND PL.ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND PD.DESTINATION_ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID
AND WO2.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WO2.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND NVL(WO2.REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND ((WO.NEXT_OPERATION_SEQ_NUM IS NOT NULL
AND WO.NEXT_OPERATION_SEQ_NUM = WO2.OPERATION_SEQ_NUM) OR (WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WO2.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM))
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE))
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD1.SEQ_NUM = (BCD2.NEXT_SEQ_NUM - CEIL(NVL(MSI2.POSTPROCESSING_LEAD_TIME
, 0))) UNION ALL SELECT NULL
, 'REQUISITION'
, POLC.DISPLAYED_FIELD
, PRH.AUTHORIZATION_STATUS
, PRL.NEED_BY_DATE
, TO_DATE(NULL)
, BCD1.CALENDAR_DATE + (DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE) - TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE)))
, WIP_OSP.CONVERTTOPRIMARYMOVEQTY(PRL.ITEM_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.QUANTITY
, PRL.UNIT_MEAS_LOOKUP_CODE
, MSI.PRIMARY_UNIT_OF_MEASURE
, (SELECT WOR.USAGE_RATE_OR_AMOUNT
FROM WIP_OPERATION_RESOURCES WOR
WHERE WOR.WIP_ENTITY_ID = PRL.WIP_ENTITY_ID
AND NVL(WOR.REPETITIVE_SCHEDULE_ID
, -1) = NVL(PRL.WIP_REPETITIVE_SCHEDULE_ID
, -1)
AND WOR.OPERATION_SEQ_NUM = PRL.WIP_OPERATION_SEQ_NUM
AND WOR.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND WOR.RESOURCE_SEQ_NUM = PRL.WIP_RESOURCE_SEQ_NUM))
, MSI.PRIMARY_UNIT_OF_MEASURE
, PRL.WIP_ENTITY_ID
, PRL.WIP_REPETITIVE_SCHEDULE_ID
, PRL.DESTINATION_ORGANIZATION_ID
, PRL.WIP_OPERATION_SEQ_NUM
, PRL.WIP_RESOURCE_SEQ_NUM
, BSO.OPERATION_CODE
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRL.ORG_ID
, PRL.ITEM_ID
, PRL.QUANTITY
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRH.SEGMENT1
, PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, PRL.CREATION_DATE
, PRL.CREATED_BY
, PRL.LAST_UPDATE_DATE
, PRL.LAST_UPDATED_BY
, PRL.CANCEL_FLAG
FROM WIP_ENTITIES WE
, MTL_SYSTEM_ITEMS MSI
, WIP_OPERATIONS WO
, BOM_STANDARD_OPERATIONS BSO
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_LOOKUP_CODES POLC
, MTL_SYSTEM_ITEMS MSI2
, BOM_CALENDAR_DATES BCD1
, BOM_CALENDAR_DATES BCD2
, MTL_PARAMETERS MP
, WIP_OPERATIONS WO2
WHERE NOT EXISTS (SELECT 1
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID)
AND POLC.LOOKUP_CODE = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND POLC.LOOKUP_TYPE = 'AUTHORIZATION STATUS'
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND PRL.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND PRL.DESTINATION_ORGANIZATION_ID = WO.ORGANIZATION_ID
AND PRL.WIP_OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM
AND NVL(PRL.WIP_REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND WO.STANDARD_OPERATION_ID = BSO.STANDARD_OPERATION_ID(+)
AND WO.ORGANIZATION_ID = BSO.ORGANIZATION_ID(+)
AND WE.PRIMARY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WE.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND PRL.ITEM_ID = MSI2.INVENTORY_ITEM_ID
AND PRL.DESTINATION_ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID
AND WO2.ORGANIZATION_ID = WO.ORGANIZATION_ID
AND WO2.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND NVL(WO2.REPETITIVE_SCHEDULE_ID
, -1) = NVL(WO.REPETITIVE_SCHEDULE_ID
, -1)
AND ((WO.NEXT_OPERATION_SEQ_NUM IS NOT NULL
AND WO.NEXT_OPERATION_SEQ_NUM = WO2.OPERATION_SEQ_NUM) OR (WO.NEXT_OPERATION_SEQ_NUM IS NULL
AND WO2.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM))
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(DECODE(WO.NEXT_OPERATION_SEQ_NUM
, NULL
, WO.LAST_UNIT_COMPLETION_DATE
, WO2.FIRST_UNIT_START_DATE))
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD1.SEQ_NUM = (BCD2.NEXT_SEQ_NUM - CEIL(NVL(MSI2.POSTPROCESSING_LEAD_TIME
, 0)))