VIEW: APPS.IEX_LM_CKEY_CSI_V

Source

View Text - Preformatted

SELECT csii.instance_id INSTANCE_ID, csii.serial_number SERIAL_NUMBER, csii.external_reference EXTERNAL_REFERENCE, csii.system_id SYSTEM_NUMBER, csiip.party_id PARTY_ID, 0 person_id, 0 relationship_id, csiip.relationship_type_code RELATIONSHIP_CODE, hca.account_number ACCOUNT_NUMBER, hca.account_name ACCOUNT_NAME, hp.party_name PARTY_NAME, alk.meaning party_type, alk.lookup_code party_type_code, hp.address1 ADDRESS1, hp.address2 ADDRESS2, hp.address3 ADDRESS3, hp.address4 ADDRESS4, hp.city CITY, hp.state STATE, hp.province PROVINCE, hp.postal_code POSTAL_CODE, hp.county COUNTY, fnd.territory_short_name territory_short_name, hp.email_address 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 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 INSTANCE_ID
, CSII.SERIAL_NUMBER SERIAL_NUMBER
, CSII.EXTERNAL_REFERENCE EXTERNAL_REFERENCE
, CSII.SYSTEM_ID SYSTEM_NUMBER
, CSIIP.PARTY_ID PARTY_ID
, 0 PERSON_ID
, 0 RELATIONSHIP_ID
, CSIIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_CODE
, HCA.ACCOUNT_NUMBER ACCOUNT_NUMBER
, HCA.ACCOUNT_NAME ACCOUNT_NAME
, HP.PARTY_NAME PARTY_NAME
, ALK.MEANING PARTY_TYPE
, ALK.LOOKUP_CODE PARTY_TYPE_CODE
, HP.ADDRESS1 ADDRESS1
, HP.ADDRESS2 ADDRESS2
, HP.ADDRESS3 ADDRESS3
, HP.ADDRESS4 ADDRESS4
, HP.CITY CITY
, HP.STATE STATE
, HP.PROVINCE PROVINCE
, HP.POSTAL_CODE POSTAL_CODE
, HP.COUNTY COUNTY
, FND.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, HP.EMAIL_ADDRESS 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
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')