View: CZ_SYSTEM_COMPONENTS_V

View Text

SELECT SOD.SYSTEM_ID
, CS.CONFIG_SYSTEM_TYPE
, SOL.HEADER_ID
, SOD.LINE_ID
, SOL.ITEM_TYPE_CODE
, SOL.PARENT_LINE_ID
, SOL.ATO_LINE_ID
, SOL.INVENTORY_ITEM_ID
, SOL.WAREHOUSE_ID
, SOL.ORDERED_QUANTITY
, SOL.CANCELLED_QUANTITY
, 1 PARENT_QUANTITY
, SOD.CUSTOMER_PRODUCT_QUANTITY
, SOL.UNIT_CODE
FROM SO_LINES_ALL SOL
, CS_SYSTEMS_ALL CS
, SO_LINE_SERVICE_DETAILS SOD
, CS_TRANSACTION_TYPES CST
WHERE CS.SYSTEM_ID = SOD.SYSTEM_ID
AND SOD.LINE_ID = SOL.LINE_ID
AND CST.TRANSACTION_TYPE_ID = SOD.TRANSACTION_TYPE_ID
AND CST.REVISION_FLAG IN ( 'N'
, 'Y')
AND SOD.LINE_ID NOT IN (SELECT CP1.ORIGINAL_ORDER_LINE_ID
FROM CS_CUSTOMER_PRODUCTS_ALL CP1
, SO_LINE_SERVICE_DETAILS SOD1
, CS_TRANSACTION_TYPES CST1
WHERE SOD1.CUSTOMER_PRODUCT_ID = CP1.CUSTOMER_PRODUCT_ID
AND CST1.REVISION_FLAG = 'R'
AND SOD1.SYSTEM_ID = SOD.SYSTEM_ID
AND SOD1.TRANSACTION_TYPE_ID = CST1.TRANSACTION_TYPE_ID) UNION SELECT SOD.SYSTEM_ID
, CS.CONFIG_SYSTEM_TYPE
, SOL.HEADER_ID
, SOD.LINE_ID
, SOL.ITEM_TYPE_CODE
, SOL.PARENT_LINE_ID
, SOL.ATO_LINE_ID
, SOL.INVENTORY_ITEM_ID
, SOL.WAREHOUSE_ID
, SOL.ORDERED_QUANTITY
, SOL.CANCELLED_QUANTITY
, 1 PARENT_QUANTITY
, SOD.CUSTOMER_PRODUCT_QUANTITY
, SOL.UNIT_CODE
FROM SO_LINES_ALL SOL
, CS_SYSTEMS_ALL CS
, SO_LINE_SERVICE_DETAILS SOD
, CS_TRANSACTION_TYPES CST
WHERE CS.SYSTEM_ID = SOD.SYSTEM_ID
AND SOD.LINE_ID = SOL.LINE_ID
AND CST.TRANSACTION_TYPE_ID = SOD.TRANSACTION_TYPE_ID
AND CST.REVISION_FLAG = 'R'

Columns