View: BIC_INSTALLED_BASE_SUMMARY_V

View Text

SELECT PRD.CUSTOMER_PRODUCT_ID
, PRD.CUSTOMER_ID CUSTOMER_ID
, PRD.ORG_ID ORG_ID
, ITM.DESCRIPTION PRODUCT_NAME
, REV.REVISION PRODUCT_REVISION
, PRD.TYPE_CODE PRODUCT_TYPE
, ITMF.DESCRIPTION PRODUCT_FAMILY
, PS.NAME STATUS
, LOCS.LOCATION_ID SHIPTO_SITE
, LOCS.ADDRESS SHIPTO_ADDR
, LOCI.LOCATION_ID INSTALL_SITE
, LOCI.ADDRESS INSTALL_ADDR
, PRD.INSTALLATION_DATE INSTALLATION_DATE
FROM CS_CUSTOMER_PRODUCTS_ALL PRD
, MTL_SYSTEM_ITEMS ITM
, MTL_SYSTEM_ITEMS ITMF
, CS_CP_REVISIONS REV
, CS_CUSTOMER_PRODUCT_STATUSES PS
, HZ_CUST_SITE_USES_ALL CSUS
, HZ_CUST_ACCT_SITES_ALL CASS
, HZ_PARTY_SITES PASS
, BIC_ADDRESS_V LOCS
, HZ_CUST_SITE_USES_ALL CSUB
, HZ_CUST_ACCT_SITES_ALL CASB
, HZ_PARTY_SITES PASB
, BIC_ADDRESS_V LOCB
, HZ_CUST_SITE_USES_ALL CSUI
, HZ_CUST_ACCT_SITES_ALL CASI
, HZ_PARTY_SITES PASI
, BIC_ADDRESS_V LOCI
WHERE PRD.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND ITM.PRODUCT_FAMILY_ITEM_ID = ITMF.INVENTORY_ITEM_ID(+)
AND ITM.ORGANIZATION_ID = FND_PROFILE.VALUE('ASO_PRODUCT_ORGANIZATION_ID')
AND PRD.CURRENT_CP_REVISION_ID = REV.CP_REVISION_ID(+)
AND PRD.CUSTOMER_PRODUCT_STATUS_ID = PS.CUSTOMER_PRODUCT_STATUS_ID
AND PRD.BILL_TO_SITE_USE_ID = CSUB.SITE_USE_ID(+)
AND CSUB.CUST_ACCT_SITE_ID = CASB.CUST_ACCT_SITE_ID(+)
AND CASB.PARTY_SITE_ID = PASB.PARTY_SITE_ID(+)
AND PASB.LOCATION_ID = LOCB.LOCATION_ID(+)
AND PRD.SHIP_TO_SITE_USE_ID = CSUS.SITE_USE_ID(+)
AND CSUS.CUST_ACCT_SITE_ID = CASS.CUST_ACCT_SITE_ID(+)
AND CASS.PARTY_SITE_ID = PASS.PARTY_SITE_ID(+)
AND PASS.LOCATION_ID = LOCS.LOCATION_ID(+)
AND PRD.INSTALL_SITE_USE_ID = CSUI.SITE_USE_ID(+)
AND CSUI.CUST_ACCT_SITE_ID = CASI.CUST_ACCT_SITE_ID(+)
AND CASI.PARTY_SITE_ID = PASI.PARTY_SITE_ID(+)
AND PASI.LOCATION_ID = LOCI.LOCATION_ID(+) WITH READ ONLY

Columns