View: AST_LM_CKEY_CSI_V

Product:	AST - TeleSales
Description:
Implementation/DBA Data:	APPS.AST_LM_CKEY_CSI_V

View Text

SELECT CSII.INSTANCE_ID
, CSII.SERIAL_NUMBER
, CSII.EXTERNAL_REFERENCE
, CSII.SYSTEM_ID
, CSIIP.PARTY_ID
, 0 PERSON_ID
, 0 RELATIONSHIP_ID
, CSIIP.RELATIONSHIP_TYPE_CODE
, HCA.ACCOUNT_NUMBER
, HCA.ACCOUNT_NAME
, HP.PARTY_NAME
, ALK.MEANING PARTY_TYPE
, ALK.LOOKUP_CODE PARTY_TYPE_CODE
, HP.ADDRESS1
, HP.ADDRESS2
, HP.ADDRESS3
, HP.ADDRESS4
, HP.CITY
, HP.STATE
, HP.PROVINCE
, HP.POSTAL_CODE
, HP.COUNTY
, FND.TERRITORY_SHORT_NAME
, HP.EMAIL_ADDRESS
, HCP.PHONE_COUNTRY_CODE ||' '|| HCP.PHONE_AREA_CODE ||' '|| HCP.PHONE_NUMBER ||' '|| HCP.PHONE_EXTENSION PHONE_NUMBER
, ALK1.MEANING STATUS
, HP.STATUS STATUS_CODE
, CSII.INSTANCE_DESCRIPTION
FROM CSI_ITEM_INSTANCES CSII
, CSI_I_PARTIES CSIIP
, CSI_IP_ACCOUNTS CSIAC
, HZ_CUST_ACCOUNTS HCA
, HZ_PARTIES HP
, HZ_CONTACT_POINTS HCP
, FND_TERRITORIES_TL FND
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
WHERE CSII.INSTANCE_ID=CSIIP.INSTANCE_ID(+)
AND CSIIP.INSTANCE_PARTY_ID=CSIAC.INSTANCE_PARTY_ID(+)
AND CSIAC.PARTY_ACCOUNT_ID=HCA.CUST_ACCOUNT_ID(+)
AND CSIIP.PARTY_ID=HP.PARTY_ID
AND HP.PARTY_ID=HCP.OWNER_TABLE_ID(+)
AND HCP.CONTACT_POINT_TYPE(+)='PHONE'
AND HCP.PRIMARY_FLAG(+)='Y'
AND ALK.LOOKUP_TYPE='PARTY_TYPE'
AND ALK.LOOKUP_CODE=HP.PARTY_TYPE
AND ALK.ENABLED_FLAG='Y'
AND ALK1.LOOKUP_TYPE='CODE_STATUS'
AND ALK1.LOOKUP_CODE=HP.STATUS
AND ALK1.ENABLED_FLAG='Y'
AND HP.COUNTRY = FND.TERRITORY_CODE(+)
AND FND.LANGUAGE(+) = USERENV('LANG')

Columns