VIEW: APPS.CS_SR_PRODUCTS_MAINT_V

Source

View Text - Preformatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME, CP.CUSTOMER_PRODUCT_ID , CP.CUSTOMER_ID , CP.INVENTORY_ITEM_ID, CP.MOST_RECENT_FLAG , CP.CURRENT_SERIAL_NUMBER , CP.TYPE_CODE , CP.SYSTEM_ID , S.NAME SYSTEM, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER, CP.INSTALL_SITE_USE_ID , CP.PARENT_CP_ID , CP.CUSTOMER_PRODUCT_STATUS_ID , CP.REFERENCE_NUMBER , OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM , CP.LOT_NUMBER CP_LOT_NUMBER, R.REVISION , SI.DESCRIPTION PRODUCT_DESCRIPTION , L2.LOOKUP_CODE SERVICED_STATUS_FLAG, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER, CP.SHIPPED_DATE, CP.QUANTITY from HZ_CUST_ACCOUNTS HZA, HZ_PARTIES HZP, CS_LOOKUPS L2, CS_CP_REVISIONS R, MTL_SYSTEM_ITEMS SI, ASO_I_OE_ORDER_HEADERS_V OOH, ASO_I_OE_ORDER_LINES_V OOL, CS_CUSTOMER_PRODUCTS_ALL CP, CS_SYSTEMS S WHERE CP.CUSTOMER_PRODUCT_ID = R.CUSTOMER_PRODUCT_ID AND CP.CURRENT_CP_REVISION_ID = R.CP_REVISION_ID AND CP.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID AND SI.ORGANIZATION_ID = ( SELECT CS_STD.Get_Item_Valdn_Orgzn_Id FROM DUAL ) AND L2.LOOKUP_TYPE = 'CP_SERVICE_STATUS' AND L2.LOOKUP_CODE = CS_STD.CS_GET_SERVICED_STATUS(CP.CUSTOMER_PRODUCT_ID) AND CP.ORIGINAL_ORDER_LINE_ID = OOL.LINE_ID(+) AND OOL.HEADER_ID = OOH.HEADER_ID(+) AND CP.CUSTOMER_ID = HZA.CUST_ACCOUNT_ID AND CP.CUSTOMER_PRODUCT_STATUS_ID NOT IN (4,1003) AND HZA.PARTY_ID = HZP.PARTY_ID AND CP.SYSTEM_ID = S.SYSTEM_ID(+)

View Text - HTML Formatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME
, CP.CUSTOMER_PRODUCT_ID
, CP.CUSTOMER_ID
, CP.INVENTORY_ITEM_ID
, CP.MOST_RECENT_FLAG
, CP.CURRENT_SERIAL_NUMBER
, CP.TYPE_CODE
, CP.SYSTEM_ID
, S.NAME SYSTEM
, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE
, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, CP.INSTALL_SITE_USE_ID
, CP.PARENT_CP_ID
, CP.CUSTOMER_PRODUCT_STATUS_ID
, CP.REFERENCE_NUMBER
, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUM
, CP.LOT_NUMBER CP_LOT_NUMBER
, R.REVISION
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, L2.LOOKUP_CODE SERVICED_STATUS_FLAG
, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER
, CP.SHIPPED_DATE
, CP.QUANTITY
FROM HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
, CS_LOOKUPS L2
, CS_CP_REVISIONS R
, MTL_SYSTEM_ITEMS SI
, ASO_I_OE_ORDER_HEADERS_V OOH
, ASO_I_OE_ORDER_LINES_V OOL
, CS_CUSTOMER_PRODUCTS_ALL CP
, CS_SYSTEMS S
WHERE CP.CUSTOMER_PRODUCT_ID = R.CUSTOMER_PRODUCT_ID
AND CP.CURRENT_CP_REVISION_ID = R.CP_REVISION_ID
AND CP.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND SI.ORGANIZATION_ID = ( SELECT CS_STD.GET_ITEM_VALDN_ORGZN_ID
FROM DUAL )
AND L2.LOOKUP_TYPE = 'CP_SERVICE_STATUS'
AND L2.LOOKUP_CODE = CS_STD.CS_GET_SERVICED_STATUS(CP.CUSTOMER_PRODUCT_ID)
AND CP.ORIGINAL_ORDER_LINE_ID = OOL.LINE_ID(+)
AND OOL.HEADER_ID = OOH.HEADER_ID(+)
AND CP.CUSTOMER_ID = HZA.CUST_ACCOUNT_ID
AND CP.CUSTOMER_PRODUCT_STATUS_ID NOT IN (4
, 1003)
AND HZA.PARTY_ID = HZP.PARTY_ID
AND CP.SYSTEM_ID = S.SYSTEM_ID(+)