View: AST_LM_CKEY_PARTY_V

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

View Text

SELECT PARTY.PARTY_ID
, 0 RELATIONSHIP_ID
, NULL RELATIONSHIP_TYPE
, 0
, PARTY.PARTY_NAME
, ALK1.MEANING PARTY_TYPE
, ALK1.LOOKUP_CODE PARTY_TYPE_CODE
, 0 PERSON_ID
, NULL JOB_TITLE
, PARTY.ADDRESS1
, PARTY.ADDRESS2
, PARTY.ADDRESS3
, PARTY.ADDRESS4
, PARTY.CITY
, PARTY.STATE
, PARTY.PROVINCE
, PARTY.POSTAL_CODE
, FTT.TERRITORY_SHORT_NAME
, CPT.PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE
, CPT.PHONE_NUMBER
, CPT.PHONE_EXTENSION
, CPT.EMAIL_ADDRESS
, PARTY.JGZZ_FISCAL_CODE
, PARTY.ORIG_SYSTEM_REFERENCE
, CPT.PRIMARY_FLAG
, ALK.MEANING STATUS
, PARTY.STATUS STATUS_CODE FROM HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CPT
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK
, FND_TERRITORIES_TL FTT
WHERE PARTY.PARTY_ID = CPT.OWNER_TABLE_ID AND CPT.OWNER_TABLE_NAME = 'HZ_PARTIES' AND CPT.STATUS = 'A' AND CPT.CONTACT_POINT_TYPE IN ('PHONE'
, 'EMAIL') AND PARTY.PARTY_TYPE = 'PERSON' AND ALK1.LOOKUP_TYPE ='PARTY_TYPE' AND ALK1.LOOKUP_CODE = PARTY.PARTY_TYPE AND ALK1.ENABLED_FLAG = 'Y' AND ALK.LOOKUP_TYPE ='CODE_STATUS' AND ALK.LOOKUP_CODE = PARTY.STATUS AND ALK.ENABLED_FLAG = 'Y' AND FTT.LANGUAGE(+) = USERENV('LANG') AND FTT.SOURCE_LANG(+) = USERENV('LANG') AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) UNION SELECT HZ.PARTY_ID
, 0 RELATIONSHIP_ID
, NULL RELATIONSHIP_TYPE
, 0 LOCATION_ID
, HZ.PARTY_NAME
, ALK.MEANING PARTY_TYPE
, ALK.LOOKUP_CODE PARTY_TYPE_CODE
, 0 PERSON_ID
, NULL JOB_TITLE
, HZ.ADDRESS1
, HZ.ADDRESS2
, HZ.ADDRESS3
, HZ.ADDRESS4
, HZ.CITY
, HZ.STATE
, HZ.PROVINCE
, HZ.POSTAL_CODE
, FND.TERRITORY_SHORT_NAME
, HCP.PHONE_COUNTRY_CODE
, HCP.PHONE_AREA_CODE
, HCP.PHONE_NUMBER
, HCP.PHONE_EXTENSION
, HCP.EMAIL_ADDRESS
, HZ.JGZZ_FISCAL_CODE
, HZ.ORIG_SYSTEM_REFERENCE
, HCP.PRIMARY_FLAG
, ALK1.MEANING STATUS
, HZ.STATUS STATUS_CODE FROM HZ_PARTIES HZ
, HZ_CONTACT_POINTS HCP
, FND_TERRITORIES_TL FND
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1 WHERE HZ.PARTY_ID = HCP.OWNER_TABLE_ID AND HCP.OWNER_TABLE_NAME = 'HZ_PARTIES' AND HCP.CONTACT_POINT_TYPE IN ('PHONE'
, 'EMAIL') AND HCP.STATUS ='A' AND HZ.PARTY_TYPE='ORGANIZATION' AND HZ.COUNTRY = FND.TERRITORY_CODE(+) AND FND.LANGUAGE(+) = USERENV('LANG') AND ALK.LOOKUP_TYPE ='PARTY_TYPE' AND ALK.LOOKUP_CODE = HZ.PARTY_TYPE AND ALK.ENABLED_FLAG = 'Y' AND ALK1.LOOKUP_TYPE ='CODE_STATUS' AND ALK1.LOOKUP_CODE = HZ.STATUS AND ALK1.ENABLED_FLAG = 'Y' UNION SELECT O.PARTY_ID
, R.PARTY_ID RELATIONSHIP_ID
, ALK.MEANING RELATIONSHIP_TYPE
, 0
, R.PARTY_NAME
, ALK2.MEANING PARTY_TYPE
, ALK2.LOOKUP_CODE PARTY_TYPE_CODE
, P.PARTY_ID PERSON_ID
, CNT.JOB_TITLE
, R.ADDRESS1
, R.ADDRESS2
, R.ADDRESS3
, R.ADDRESS4
, R.CITY
, R.STATE
, R.PROVINCE
, R.POSTAL_CODE
, FTT.TERRITORY_SHORT_NAME
, CPT.PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE
, CPT.PHONE_NUMBER
, CPT.PHONE_EXTENSION
, CPT.EMAIL_ADDRESS
, NULL
, R.ORIG_SYSTEM_REFERENCE
, CPT.PRIMARY_FLAG
, ALK3.MEANING STATUS
, REL.STATUS STATUS_CODE FROM HZ_RELATIONSHIPS REL
, HZ_PARTIES P
, HZ_PARTIES O
, HZ_PARTIES R
, HZ_CONTACT_POINTS CPT
, HZ_ORG_CONTACTS CNT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, AR_LOOKUPS ALK2
, AR_LOOKUPS ALK3
, FND_TERRITORIES_TL FTT
, HZ_RELATIONSHIP_TYPES HZ WHERE O.PARTY_TYPE = 'ORGANIZATION' AND O.PARTY_ID = REL.OBJECT_ID AND REL.OBJECT_TYPE = O.PARTY_TYPE AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.SUBJECT_ID = P.PARTY_ID AND REL.SUBJECT_TYPE = P.PARTY_TYPE AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES' AND REL.RELATIONSHIP_ID = CNT.PARTY_RELATIONSHIP_ID AND R.PARTY_ID = REL.PARTY_ID AND R.PARTY_TYPE = 'PARTY_RELATIONSHIP' AND CPT.OWNER_TABLE_NAME = 'HZ_PARTIES' AND CPT.OWNER_TABLE_ID = REL.PARTY_ID AND CPT.CONTACT_POINT_TYPE IN ('PHONE'
, 'EMAIL') AND CPT.STATUS = 'A' AND ALK.LOOKUP_TYPE(+) = 'HZ_RELATIONSHIP_ROLE' AND HZ.ROLE = ALK.LOOKUP_CODE(+) AND REL.OBJECT_TYPE = HZ.OBJECT_TYPE AND REL.SUBJECT_TYPE = HZ.SUBJECT_TYPE AND REL.RELATIONSHIP_TYPE= HZ.RELATIONSHIP_TYPE AND REL.RELATIONSHIP_CODE= HZ.FORWARD_REL_CODE AND ALK.ENABLED_FLAG = 'Y' AND R.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = USERENV('LANG') AND FTT.SOURCE_LANG(+) = USERENV('LANG') AND ALK2.LOOKUP_TYPE ='PARTY_TYPE' AND ALK2.LOOKUP_CODE = R.PARTY_TYPE AND ALK2.ENABLED_FLAG = 'Y' AND ALK3.LOOKUP_TYPE ='CODE_STATUS' AND ALK3.LOOKUP_CODE = O.STATUS AND ALK3.ENABLED_FLAG = 'Y' AND ALK1.LOOKUP_TYPE(+) = 'RESPONSIBILITY' AND ALK1.ENABLED_FLAG(+) = 'Y' AND ALK1.LOOKUP_CODE(+) = CNT.JOB_TITLE_CODE

Columns