Product: | CSP - Spares Management |
---|---|
Description: | Information about requirement details |
Implementation/DBA Data: | APPS.CSP_REQ_LINE_DETAILS_V |
SELECT CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CRL.ITEM_SCRATCHPAD
, CRL.UOM_CODE
, CRL.REVISION
, CRL.REQUIRED_QUANTITY
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.INVENTORY_ITEM_ID
, 'IO'
, OOLA.INVENTORY_ITEM_ID
, 'POREQ'
, PRLA.ITEM_ID)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MSIBK1.CONCATENATED_SEGMENTS
, 'IO'
, MSIBK2.CONCATENATED_SEGMENTS
, 'POREQ'
, MSIBK3.CONCATENATED_SEGMENTS)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.RESERVATION_QUANTITY
, 'IO'
, OOLA.ORDERED_QUANTITY
, 'POREQ'
, PRLA.QUANTITY)
, CRL.SHIP_COMPLETE_FLAG
, CRLD.SOURCE_TYPE
, FL.MEANING
, CRLD.SOURCE_ID
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.RESERVATION_ID
, 'IO'
, OOHA.ORDER_NUMBER
, 'POREQ'
, PRHA.SEGMENT1)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.ORGANIZATION_ID
, 'IO'
, OOLA.SHIP_FROM_ORG_ID
, NULL)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MP.ORGANIZATION_CODE
, 'IO'
, MP1.ORGANIZATION_CODE
, NULL)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.SUBINVENTORY_CODE
, 'IO'
, OOLA.SUBINVENTORY
, NULL)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, NULL
, 'IO'
, NVL(OOLA.ACTUAL_ARRIVAL_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE)
, 'POREQ'
, PRLA.NEED_BY_DATE)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, NULL
, 'IO'
, OOLA.SHIPPING_METHOD_CODE
, NULL)
, CRL.LIKELIHOOD
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, 'RESERVED'
, 'IO'
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE)
, 'POREQ'
, PRHA.AUTHORIZATION_STATUS)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, 'RESERVED'
, 'IO'
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('STATUS_MEANING')
, 'POREQ'
, PLC.DISPLAYED_FIELD)
, CRL.CREATED_BY
, CRL.CREATION_DATE
, CRL.LAST_UPDATED_BY
, CRL.LAST_UPDATE_DATE
, CRL.LAST_UPDATE_LOGIN
, CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, CRL.ATTRIBUTE_CATEGORY
, CRL.ATTRIBUTE1
, CRL.ATTRIBUTE2
, CRL.ATTRIBUTE3
, CRL.ATTRIBUTE4
, CRL.ATTRIBUTE5
, CRL.ATTRIBUTE6
, CRL.ATTRIBUTE7
, CRL.ATTRIBUTE8
, CRL.ATTRIBUTE9
, CRL.ATTRIBUTE10
, CRL.ATTRIBUTE11
, CRL.ATTRIBUTE12
, CRL.ATTRIBUTE13
, CRL.ATTRIBUTE14
, CRL.ATTRIBUTE15
FROM CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_RESERVATIONS MR
, OE_ORDER_LINES_ALL OOLA
, PO_REQUISITION_LINES_ALL PRLA
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, FND_LOOKUPS FL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_SYSTEM_ITEMS_B_KFV MSIBK2
, MTL_SYSTEM_ITEMS_B_KFV MSIBK3
, CSP_REQUIREMENT_HEADERS CRH
, OE_ORDER_HEADERS_ALL OOHA
, PO_REQUISITION_HEADERS_ALL PRHA
, PO_LOOKUP_CODES PLC
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
WHERE CRLD.REQUIREMENT_LINE_ID(+) = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_ID = MR.RESERVATION_ID(+)
AND CRLD.SOURCE_ID = OOLA.LINE_ID(+)
AND CRLD.SOURCE_ID = PRLA.REQUISITION_LINE_ID(+)
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MP.ORGANIZATION_ID(+) = MR.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_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 MSIBK1.INVENTORY_ITEM_ID (+) = MR.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK2.INVENTORY_ITEM_ID (+) = OOLA.INVENTORY_ITEM_ID
AND MSIBK2.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK3.INVENTORY_ITEM_ID (+) = PRLA.ITEM_ID
AND MSIBK3.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND OOHA.HEADER_ID(+) = OOLA.HEADER_ID
AND PRHA.REQUISITION_HEADER_ID(+) = PRLA.REQUISITION_HEADER_ID
AND PLC.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC.LOOKUP_CODE(+) = PRHA.AUTHORIZATION_STATUS