View: CSD_CUSTOMER_SEARCH_V

Product:	CSD - Depot Repair
Description:
Implementation/DBA Data:	APPS.CSD_CUSTOMER_SEARCH_V

View Text

SELECT HP.PARTY_ID PARTY_ID
, HP.PARTY_NAME PARTY_NAME
, HP.PARTY_NUMBER PARTY_NUMBER
, HP.PARTY_TYPE PARTY_TYPE
, HCA.CUST_ACCOUNT_ID ACCOUNT_ID
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.ACCOUNT_NAME ACCOUNT_NAME
, HC.PHONE_COUNTRY_CODE||''||HC.PHONE_AREA_CODE||''|| HC.PHONE_NUMBER PHONE_NUMBER
, HC.PHONE_LINE_TYPE PHONE_TYPE
, ARL.MEANING PHONE_TYPE_MEANING
, A.SERIAL_NUMBER SERIAL_NUMBER
, A.INSTANCE_ID INSTANCE_ID
, A.INSTANCE_NUMBER INSTANCE_NUMBER
, A.INVENTORY_REVISION ITEM_REVISION
, A.LOT_NUMBER LOT_NUMBER
, A.QUANTITY QTY
, A.UNIT_OF_MEASURE UOM
, A.MFG_SERIAL_NUMBER_FLAG MFG_SERIAL_NUMBER_FLAG
, B.ORGANIZATION_ID INVENTORY_ORG_ID
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS PRODUCT_NAME
, B.ORGANIZATION_ID ORGANIZATION_ID
, B.DESCRIPTION PRODUCT_DESC
, B.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, B.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
FROM CSI_ITEM_INSTANCES A
, MTL_SYSTEM_ITEMS_KFV B
, HZ_PARTIES HP
, HZ_CONTACT_POINTS HC
, HZ_CUST_ACCOUNTS HCA
, AR_LOOKUPS ARL
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(NVL(A.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(NVL(A.ACTIVE_END_DATE
, SYSDATE))
AND B.INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND B.SERVICE_ITEM_FLAG = 'N'
AND B.ENABLED_FLAG = 'Y'
AND A.LOCATION_TYPE_CODE IN ('HZ_PARTY_SITES'
, 'HZ_LOCATIONS')
AND B.SERV_REQ_ENABLED_CODE = 'E'
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(B.START_DATE_ACTIVE
, SYSDATE))
AND TRUNC(NVL(B.END_DATE_ACTIVE
, SYSDATE))
AND HP.STATUS = 'A'
AND HP.PARTY_ID = HCA.PARTY_ID(+)
AND HCA.STATUS(+) = 'A'
AND HP.PARTY_ID = HC.OWNER_TABLE_ID(+)
AND HC.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HC.PRIMARY_FLAG(+) = 'Y'
AND HC.CONTACT_POINT_TYPE(+)='PHONE'
AND HC.STATUS(+) = 'A'
AND A.OWNER_PARTY_ACCOUNT_ID = HCA.CUST_ACCOUNT_ID
AND A.OWNER_PARTY_ID = HP.PARTY_ID
AND ARL.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE'
AND ARL.LOOKUP_CODE(+) = HC.PHONE_LINE_TYPE

Columns