VIEW: APPS.AST_LM_CKEY_CSI_V

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')