View: CSP_DC_PARTS_V

Product:	CSP - Spares Management
Description:
Implementation/DBA Data:	APPS.CSP_DC_PARTS_V

View Text

SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, 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
, HRS.NAME SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, MR.RESERVATION_QUANTITY ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
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
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_PARAMETERS MP
, CSP_PART_CATEGORIES_V CPCV
, FND_LOOKUPS FL1
, HR_ALL_ORGANIZATION_UNITS_VL HRS
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 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 HRS.ORGANIZATION_ID(+) = MR.ORGANIZATION_ID UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL) SOURCE_TYPE_MEANING
, 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)
, TO_CHAR(NULL) SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, TO_NUMBER(NULL) ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
FROM CSP_REQUIREMENT_LINES CRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, FND_LOOKUPS FL
, CSP_PART_CATEGORIES_V CPCV
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 UNION ALL SELECT CRH.REQUIREMENT_HEADER_ID REQUIREMENT_NUMBER
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, 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
, HRS.NAME SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, OOLA.ORDERED_QUANTITY
, OOLA.ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
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
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MPS
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, HR_ALL_ORGANIZATION_UNITS_VL HRS
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 MPS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND NVL(CRL.LIKELIHOOD
, 100) BETWEEN CPCV.LOW+0.0000001
AND CPCV.HIGH
AND HRS.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEAING
, 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
, TO_CHAR(NULL) SOURCE_ORGANIZATION_CODE
, TO_CHAR(NULL) SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, PLL.QUANTITY
, CRL.UOM_CODE ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
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
, CSP_PART_CATEGORIES_V CPCV
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

Columns