VIEW: APPS.CS_CUSTOMER_PRODUCTS_REPORT_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'