View: WIP_OSP_REQS_POS_V

Product:	WIP - Work in Process
Description:	Requisitions and purchase orders information for a job or repetitive schedule
Implementation/DBA Data:	APPS.WIP_OSP_REQS_POS_V

View Text

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)))

Columns