View: CS_SR_PRODUCTS_MAINT_V

Product:	CS - Service
Description:	This view contains the information about all products in the InstallBase. This view is using to get all product information for a perticular account in Service request.
Implementation/DBA Data:	APPS.CS_SR_PRODUCTS_MAINT_V

View Text

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(+)

Columns