View: BIC_INSTALLED_BASE_DETAIL_V

View Text

SELECT PRD.CUSTOMER_ID CUSTOMER_ID
, PRD.CUSTOMER_PRODUCT_ID CUSTOMER_PRODUCT_ID
, DECODE(PA.PARTY_TYPE
, 'PERSON'
, PA.PERSON_FIRST_NAME||' '||PA.PERSON_MIDDLE_NAME||' '||PA.PERSON_LAST_NAME
, 'ORGANIZATION'
, PA.PARTY_NAME) CUSTOMER_NAME
, CA.ACCOUNT_NUMBER CUST_ACCT_NUMBER
, ITM.DESCRIPTION PRODUCT_NAME
, REV.REVISION PRODUCT_REVISION
, PRD.TYPE_CODE PRODUCT_TYPE
, ITMF.DESCRIPTION PRODUCT_FAMILY
, PS.NAME PRODUCT_STATUS
, UOM.UNIT_OF_MEASURE UNIT_OF_MEASURE
, PRD.QUANTITY QUANTITY
, PRD.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, PRD.CURRENT_SERIAL_NUMBER SERIAL_NUMBER
, PRD.REFERENCE_NUMBER REFERENCE_NUMBER
, PRD.LOT_NUMBER LOT_NUMBER
, ASOH.ORDERED_DATE ORIGINAL_ORDER_DATE
, ASOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, ASOL.ORDERED_QUANTITY * ASOL.UNIT_SELLING_PRICE ORIGINAL_NET_AMOUNT
, PRD.INSTALLATION_DATE INSTALLATION_DATE
, PRD.SHIPPED_DATE SHIPPED_DATE
FROM CS_CUSTOMER_PRODUCTS_ALL PRD
, MTL_UNITS_OF_MEASURE UOM
, HZ_CUST_ACCOUNTS CA
, HZ_PARTIES PA
, MTL_SYSTEM_ITEMS ITM
, MTL_SYSTEM_ITEMS ITMF
, CS_CP_REVISIONS REV
, CS_CUSTOMER_PRODUCT_STATUSES PS
, CS_CONTACTS CCS
, BIC_SITE_ADDRESS_V SAI
, ASO_I_OE_ORDER_LINES_V ASOL
, ASO_I_OE_ORDER_HEADERS_V ASOH
WHERE PRD.CUSTOMER_ID = CA.CUST_ACCOUNT_ID(+)
AND CA.PARTY_ID = PA.PARTY_ID(+)
AND PRD.ORIGINAL_ORDER_LINE_ID = ASOL.LINE_ID
AND ASOL.HEADER_ID = ASOH.HEADER_ID
AND PRD.UNIT_OF_MEASURE_CODE = UOM.UOM_CODE(+)
AND PRD.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID
AND ITM.ORGANIZATION_ID = FND_PROFILE.VALUE_WNPS('ASO_PRODUCT_ORGANIZATION_ID')
AND ITM.PRODUCT_FAMILY_ITEM_ID = ITMF.INVENTORY_ITEM_ID(+)
AND PRD.CURRENT_CP_REVISION_ID = REV.CP_REVISION_ID(+)
AND PRD.CUSTOMER_PRODUCT_STATUS_ID = PS.CUSTOMER_PRODUCT_STATUS_ID(+)
AND PRD.CUSTOMER_PRODUCT_ID = CCS.SOURCE_OBJECT_ID(+)
AND CCS.CONTACT_TYPE(+) = 'SERVICE_REQUEST'
AND PRD.INSTALL_SITE_USE_ID = SAI.SITE_USE_ID(+) WITH READ ONLY

Columns