View: CS_CUSTOMER_PRODUCTS_REPORT_V

Product:	CS - Service
Description:	- Retrofitted
Implementation/DBA Data:	APPS.CS_CUSTOMER_PRODUCTS_REPORT_V

View Text

SELECT HCA_P.ACCOUNT_NUMBER
, HP_P.PARTY_NAME
, HL_I.CITY INSTALL_LOCATION
, HL_I.ADDRESS1 || DECODE( HL_I.ADDRESS1
, ''
, ''
, DECODE(HL_I.ADDRESS2
, ''
, ''
, CHR(44) || CHR(32))) || HL_I.ADDRESS2 INSTALL_ADDRESS1
, HL_I.ADDRESS3 || DECODE( HL_I.ADDRESS3
, ''
, ''
, DECODE( HL_I.ADDRESS4
, ''
, ''
, CHR(44)||CHR(32))) || HL_I.ADDRESS4 INSTALL_ADDRESS2
, SUBSTR( HL_I.CITY || DECODE( HL_I.CITY
, ''
, ''
, DECODE(HL_I.STATE|| HL_I.COUNTRY || HL_I.POSTAL_CODE
, ''
, ''
, CHR(44) || CHR(32))) || HL_I.STATE ||CHR(32)||CHR(32) || HL_I.POSTAL_CODE|| CHR(32) || CHR (32) || HL_I.COUNTRY
, 1
, 220) INSTALL_ADDRESS3
, CCPA.INVENTORY_ITEM_ID
, CCPA.CURRENT_SERIAL_NUMBER
, CCPA.UNIT_OF_MEASURE_CODE
, CCPS.NAME CP_STATUS
, MSI.DESCRIPTION PRODUCT_DESCRIPTION
, CCPA.REFERENCE_NUMBER
, CCPA.QUANTITY
, CCR.REVISION
, CS.NAME SYSTEM
, CCPA.ORIGINAL_ORDER_LINE_ID
, CCPA.PURCHASE_ORDER_NUM
, CCPA.START_DATE_ACTIVE
, CCPA.END_DATE_ACTIVE
, CL1.MEANING PRODUCT_TYPE
, NULL SERVICED_STATUS
FROM CS_CUSTOMER_PRODUCTS_ALL CCPA
, HZ_CUST_ACCOUNTS HCA_P
, HZ_PARTIES HP_P
, HZ_PARTY_SITES HPS_I
, HZ_LOCATIONS HL_I
, CS_CUSTOMER_PRODUCT_STATUSES CCPS
, MTL_SYSTEM_ITEMS MSI
, CS_CP_REVISIONS CCR
, CS_SYSTEMS CS
, CS_LOOKUPS CL1
WHERE CCPA.CUSTOMER_ID = HCA_P.CUST_ACCOUNT_ID
AND HCA_P.PARTY_ID = HP_P.PARTY_ID
AND CCPA.INSTALL_SITE_USE_ID = HPS_I.PARTY_SITE_ID (+)
AND HPS_I.LOCATION_ID = HL_I.LOCATION_ID (+)
AND CCPA.CUSTOMER_PRODUCT_STATUS_ID = CCPS.CUSTOMER_PRODUCT_STATUS_ID
AND CCPA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID IN ( SELECT CS_STD.GET_ITEM_VALDN_ORGZN_ID
FROM DUAL )
AND CCPA.CUSTOMER_PRODUCT_ID = CCR.CUSTOMER_PRODUCT_ID
AND CCPA.CURRENT_CP_REVISION_ID = CCR.CP_REVISION_ID
AND CCPA.SYSTEM_ID = CS.SYSTEM_ID (+)
AND CCPA.TYPE_CODE = CL1.LOOKUP_CODE (+)
AND CL1.LOOKUP_TYPE (+) = 'CUSTOMER_PRODUCT_TYPE'

Columns