View: CSP_REQUIRED_PARTS_V

Product:	CSP - Spares Management
Description:	View which gives you the complete status of spare part requirements
Implementation/DBA Data:	APPS.CSP_REQUIRED_PARTS_V

View Text

SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, TO_CHAR(NULL) PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, TO_NUMBER(NULL) ORDER_HEADER_ID
, TO_NUMBER(NULL) ORDER_LINE_ID
, MR.RESERVATION_ID ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPCV.CATEGORY
, 'RESERVED'
, FL1.MEANING STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, MR.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, TO_CHAR(NULL) FREIGHT_CARRIER_CODE
, TO_CHAR(NULL) FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, MR.RESERVATION_QUANTITY ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, TO_CHAR(NULL) SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, TO_NUMBER(NULL) RECEIVED_QUANTITY
, TO_CHAR(NULL) RECEIVED_QUANTITY_UOM
, TO_NUMBER(NULL) CANCELLED_QUANTITY
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) REQUEST_DATE
, TO_DATE(NULL) PROMISE_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
FROM CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, CSP_REQUIREMENT_HEADERS CRH
, MTL_RESERVATIONS MR
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, FND_LOOKUPS FL
, FND_LOOKUPS FL1
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MPD
, CSP_PART_CATEGORIES_V CPCV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
WHERE CRLD.SOURCE_TYPE = 'RES'
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND CRLD.SOURCE_ID = MR.RESERVATION_ID
AND MP.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MPD.ORGANIZATION_ID = CRH.DESTINATION_ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
AND FL1.LOOKUP_CODE = 'RESERVED'
AND FL1.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND MSIBK1.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND NVL(CRL.LIKELIHOOD
, 100) BETWEEN CPCV.LOW+0.0000001
AND CPCV.HIGH
AND CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+) UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL) SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, TO_CHAR(NULL) PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, TO_NUMBER(NULL) ORDER_HEADER_ID
, TO_NUMBER(NULL) ORDER_LINE_ID
, TO_NUMBER(NULL) ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPCV.CATEGORY
, 'OPEN'
, FL.MEANING STATUS_MEANING
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, TO_CHAR(NULL) FREIGHT_CARRIER_CODE
, TO_CHAR(NULL) FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, TO_NUMBER(NULL) ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, TO_CHAR(NULL) SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, TO_NUMBER(NULL) RECEIVED_QUANTITY
, TO_CHAR(NULL) RECEIVED_QUANTITY_UOM
, TO_NUMBER(NULL) CANCELLED_QUANTITY
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) REQUEST_DATE
, TO_DATE(NULL) PROMISE_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
FROM CSP_REQUIREMENT_LINES CRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, FND_LOOKUPS FL
, CSP_PART_CATEGORIES_V CPCV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
, MTL_PARAMETERS MPD
WHERE CRL.REQUIREMENT_LINE_ID NOT IN (SELECT DISTINCT(REQUIREMENT_LINE_ID)
FROM CSP_REQ_LINE_DETAILS)
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND FL.LOOKUP_CODE = 'OPEN'
AND FL.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND NVL(CRL.LIKELIHOOD
, 100) BETWEEN CPCV.LOW+0.0000001
AND CPCV.HIGH
AND CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+)
AND MPD.ORGANIZATION_ID (+)= CRH.DESTINATION_ORGANIZATION_ID UNION ALL SELECT CRH.REQUIREMENT_HEADER_ID REQUIREMENT_NUMBER
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, NULL PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, OOLA.HEADER_ID ORDER_HEADER_ID
, OOLA.LINE_ID ORDER_LINE_ID
, OOHA.ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM_NUMBER
, CPCV.CATEGORY
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE) STATUS
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('STATUS_MEANING') STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, OOLA.SHIP_FROM_ORG_ID
, MPS.ORGANIZATION_CODE SOURCE_ORGANIZATION_CODE
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE DESTINATION_ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, OOLA.FREIGHT_CARRIER_CODE
, OFC.DESCRIPTION FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, OOLA.ORDERED_QUANTITY
, OOLA.ORDER_QUANTITY_UOM
, OOLA.FULFILLED_QUANTITY
, OOLA.SHIPPING_QUANTITY
, OOLA.SHIPPING_QUANTITY_UOM
, OOLA.SHIPPED_QUANTITY
, CSP_PICK_UTILS.GET_RECEIVED_QTY RECEIVED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('RECEIVED_QTY_UOM') RECEIVED_QUANTITY_UOM
, OOLA.CANCELLED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('DELIVERY_NUMBER')
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL') WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, OOLA.REQUEST_DATE
, OOLA.PROMISE_DATE
, OOLA.SCHEDULE_SHIP_DATE
, OOLA.ACTUAL_SHIPMENT_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE
, OOLA.ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
FROM OE_ORDER_LINES_ALL OOLA
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_PART_CATEGORIES_V CPCV
, FND_LOOKUPS FL
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MPS
, MTL_PARAMETERS MPD
, ORG_FREIGHT OFC
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_TYPE = 'IO'
AND CRLD.SOURCE_ID = OOLA.LINE_ID
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
AND CRL.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOLA.INVENTORY_ITEM_ID = MSIBK1.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND MPS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND MPD.ORGANIZATION_ID (+) = CRH.DESTINATION_ORGANIZATION_ID
AND OOLA.FREIGHT_CARRIER_CODE = OFC.FREIGHT_CODE (+)
AND OFC.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+)
AND NVL(CRL.LIKELIHOOD
, 100) BETWEEN CPCV.LOW+0.0000001
AND CPCV.HIGH UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEAING
, CRLD.SOURCE_ID
, POR.REQUISITION_HEADER_ID
, PRH.SEGMENT1 PO_REQUISITION_NUMBER
, PLL.LINE_LOCATION_ID
, PLL.PO_HEADER_ID ORDER_HEADER_ID
, PLL.PO_LINE_ID
, TO_NUMBER(NVL(POH.SEGMENT1
, PRH.SEGMENT1)) ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM_NUMBER
, CPCV.CATEGORY
, DECODE(POH.PO_HEADER_ID
, NULL
, PRH.AUTHORIZATION_STATUS
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')) STATUS
, DECODE(POH.PO_HEADER_ID
, NULL
, PLC1.DISPLAYED_FIELD
, PLC.DISPLAYED_FIELD) STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, TO_NUMBER(NULL) SOURCE_ORGANIZATION_ID
, NULL SOURCE_ORGANIZATION_CODE
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE DESTINATION_ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, NULL FRIEGHT_CARRIER_CODE
, NULL FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, PLL.QUANTITY
, CRL.UOM_CODE ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULLFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, NULL SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, PLL.QUANTITY_RECEIVED
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.QUANTITY_CANCELLED
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, PRH.CREATION_DATE
, PLL.PROMISED_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
FROM PO_HEADERS POH
, PO_LINE_LOCATIONS_V PLL
, PO_REQUISITION_LINES POR
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, FND_LOOKUPS FL
, PO_REQUISITION_HEADERS PRH
, CSP_REQUIREMENT_HEADERS CRH
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_PARAMETERS MPD
, CSP_PART_CATEGORIES_V CPCV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_TYPE = 'POREQ'
AND CRLD.SOURCE_ID = POR.REQUISITION_LINE_ID(+)
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
AND PLL.LINE_LOCATION_ID(+) = POR.LINE_LOCATION_ID
AND POH.PO_HEADER_ID(+) = PLL.PO_HEADER_ID
AND PRH.REQUISITION_HEADER_ID(+) = POR.REQUISITION_HEADER_ID
AND PLC.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC.LOOKUP_CODE(+) = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC1.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC1.LOOKUP_CODE(+) = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK1.INVENTORY_ITEM_ID(+) = POR.ITEM_ID
AND MSIBK1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND NVL(CRL.LIKELIHOOD
, 100) BETWEEN CPCV.LOW+0.0000001
AND CPCV.HIGH
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MPD.ORGANIZATION_ID (+) = CRH.DESTINATION_ORGANIZATION_ID
AND JTV.TASK_ID(+) = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTA.ASSIGNEE_ROLE(+) = 'ASSIGNEE'
AND JOV.OBJECT_CODE(+) = JTA.RESOURCE_TYPE_CODE
AND JOV1.OBJECT_CODE(+) = CRH.RESOURCE_TYPE

Columns