VIEW: APPS.IEX_LS_ACC_QUICK_V

Source

View Text - Preformatted

SELECT PARTY.PARTY_ID PARTY_ID, PARTY.PARTY_NAME PARTY_NAME, PARTY.ADDRESS1 || PARTY.ADDRESS2 || PARTY.ADDRESS3 || PARTY.ADDRESS4 ADDRESS, PARTY.CITY CITY, PARTY.STATE STATE, PARTY.POSTAL_CODE POSTAL_CODE, PARTY.PROVINCE PROVINCE, PARTY.COUNTRY COUNTRY, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS, ACC.STATUS STATUS, ALK.MEANING STATUS_MEAN, PARTY.URL URL, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, PARTY.PERSON_FIRST_NAME FIRST_NAME, PARTY.PERSON_LAST_NAME LAST_NAME, TO_CHAR(NULL) ROLE_TYPE, TO_CHAR(NULL) MEANING, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, PARTY.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE FROM HZ_PARTIES PARTY, FND_TERRITORIES_TL FTT, AR_LOOKUPS ALK, HZ_CUST_ACCOUNTS ACC, HZ_CUST_ACCT_SITES CA, HZ_CUST_SITE_USES SU WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' OR PARTY.PARTY_TYPE = 'PERSON') AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+) AND PARTY.PARTY_ID = ACC.PARTY_ID AND FTT.LANGUAGE(+) = USERENV('LANG') AND FTT.SOURCE_LANG(+) = USERENV('LANG') AND ALK.LOOKUP_TYPE = 'CODE_STATUS' AND ALK.LOOKUP_CODE = ACC.STATUS AND ACC.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID AND CA.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID GROUP BY PARTY.PARTY_ID, PARTY.PARTY_NAME, PARTY.ADDRESS1, PARTY.ADDRESS2, PARTY.ADDRESS3, PARTY.ADDRESS4, PARTY.CITY, PARTY.STATE, PARTY.POSTAL_CODE, PARTY.PROVINCE, PARTY.COUNTRY, FTT.TERRITORY_SHORT_NAME, PARTY.EMAIL_ADDRESS, ACC.STATUS, ALK.MEANING, PARTY.URL, ACC.CUST_ACCOUNT_ID, PARTY.PERSON_FIRST_NAME, PARTY.PERSON_LAST_NAME, ACC.ACCOUNT_NUMBER, ACC.ORIG_SYSTEM_REFERENCE, PARTY.JGZZ_FISCAL_CODE UNION ALL SELECT RELP.PARTY_ID PARTY_ID, PARTY.PARTY_NAME PARTY_NAME, PARTY.ADDRESS1 || PARTY.ADDRESS2 || PARTY.ADDRESS3 || PARTY.ADDRESS4 ADDRESS, PARTY.CITY CITY, PARTY.STATE STATE, PARTY.POSTAL_CODE POSTAL_CODE, PARTY.PROVINCE PROVINCE, PARTY.COUNTRY COUNTRY, NULL COUNTRY_NAME, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS, ROLE.STATUS STATUS, IEX_UTILITIES.GET_LOOKUP_MEANING('CODE_STATUS', ROLE.STATUS) STATUS_MEAN, PARTY.URL URL, ROLE.CUST_ACCOUNT_ID CUST_ACCOUNT_ID, PARTY.PERSON_FIRST_NAME FIRST_NAME, PARTY.PERSON_LAST_NAME LAST_NAME, ROLE.ROLE_TYPE ROLE_TYPE, LKUP.MEANING MEANING, ACC.ACCOUNT_NUMBER, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, PARTY.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE FROM HZ_PARTIES PARTY, HZ_RELATIONSHIPS RELP, HZ_CUST_ACCOUNT_ROLES ROLE, HZ_CUST_ACCOUNTS ACC, HZ_CUST_ACCT_SITES CA, HZ_CUST_SITE_USES SU, AR_LOOKUPS LKUP WHERE ROLE.PARTY_ID = RELP.PARTY_ID AND RELP.SUBJECT_ID = PARTY.PARTY_ID AND RELP.OBJECT_ID = ACC.PARTY_ID AND ACC.CUST_ACCOUNT_ID = ROLE.CUST_ACCOUNT_ID AND ROLE.ROLE_TYPE = LKUP.LOOKUP_CODE AND LKUP.LOOKUP_TYPE = 'ACCT_ROLE_TYPE' AND ACC.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID AND CA.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID GROUP BY RELP.PARTY_ID, PARTY.PARTY_NAME, PARTY.ADDRESS1, PARTY.ADDRESS2, PARTY.ADDRESS3, PARTY.ADDRESS4, PARTY.CITY, PARTY.STATE, PARTY.POSTAL_CODE, PARTY.PROVINCE, PARTY.COUNTRY, PARTY.EMAIL_ADDRESS, ROLE.STATUS, PARTY.URL, ROLE.CUST_ACCOUNT_ID, PARTY.PERSON_FIRST_NAME, PARTY.PERSON_LAST_NAME, ROLE.ROLE_TYPE, LKUP.MEANING, ACC.ACCOUNT_NUMBER, ACC.ORIG_SYSTEM_REFERENCE, PARTY.JGZZ_FISCAL_CODE

View Text - HTML Formatted

SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.ADDRESS1 || PARTY.ADDRESS2 || PARTY.ADDRESS3 || PARTY.ADDRESS4 ADDRESS
, PARTY.CITY CITY
, PARTY.STATE STATE
, PARTY.POSTAL_CODE POSTAL_CODE
, PARTY.PROVINCE PROVINCE
, PARTY.COUNTRY COUNTRY
, FTT.TERRITORY_SHORT_NAME COUNTRY_NAME
, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS
, ACC.STATUS STATUS
, ALK.MEANING STATUS_MEAN
, PARTY.URL URL
, ACC.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, TO_CHAR(NULL) ROLE_TYPE
, TO_CHAR(NULL) MEANING
, ACC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, PARTY.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE
FROM HZ_PARTIES PARTY
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, HZ_CUST_ACCOUNTS ACC
, HZ_CUST_ACCT_SITES CA
, HZ_CUST_SITE_USES SU
WHERE (PARTY.PARTY_TYPE = 'ORGANIZATION' OR PARTY.PARTY_TYPE = 'PERSON')
AND PARTY.COUNTRY = FTT.TERRITORY_CODE(+)
AND PARTY.PARTY_ID = ACC.PARTY_ID
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK.LOOKUP_TYPE = 'CODE_STATUS'
AND ALK.LOOKUP_CODE = ACC.STATUS
AND ACC.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID GROUP BY PARTY.PARTY_ID
, PARTY.PARTY_NAME
, PARTY.ADDRESS1
, PARTY.ADDRESS2
, PARTY.ADDRESS3
, PARTY.ADDRESS4
, PARTY.CITY
, PARTY.STATE
, PARTY.POSTAL_CODE
, PARTY.PROVINCE
, PARTY.COUNTRY
, FTT.TERRITORY_SHORT_NAME
, PARTY.EMAIL_ADDRESS
, ACC.STATUS
, ALK.MEANING
, PARTY.URL
, ACC.CUST_ACCOUNT_ID
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_LAST_NAME
, ACC.ACCOUNT_NUMBER
, ACC.ORIG_SYSTEM_REFERENCE
, PARTY.JGZZ_FISCAL_CODE UNION ALL SELECT RELP.PARTY_ID PARTY_ID
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.ADDRESS1 || PARTY.ADDRESS2 || PARTY.ADDRESS3 || PARTY.ADDRESS4 ADDRESS
, PARTY.CITY CITY
, PARTY.STATE STATE
, PARTY.POSTAL_CODE POSTAL_CODE
, PARTY.PROVINCE PROVINCE
, PARTY.COUNTRY COUNTRY
, NULL COUNTRY_NAME
, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS
, ROLE.STATUS STATUS
, IEX_UTILITIES.GET_LOOKUP_MEANING('CODE_STATUS'
, ROLE.STATUS) STATUS_MEAN
, PARTY.URL URL
, ROLE.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, PARTY.PERSON_FIRST_NAME FIRST_NAME
, PARTY.PERSON_LAST_NAME LAST_NAME
, ROLE.ROLE_TYPE ROLE_TYPE
, LKUP.MEANING MEANING
, ACC.ACCOUNT_NUMBER
, ACC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, PARTY.JGZZ_FISCAL_CODE JGZZ_FISCAL_CODE
FROM HZ_PARTIES PARTY
, HZ_RELATIONSHIPS RELP
, HZ_CUST_ACCOUNT_ROLES ROLE
, HZ_CUST_ACCOUNTS ACC
, HZ_CUST_ACCT_SITES CA
, HZ_CUST_SITE_USES SU
, AR_LOOKUPS LKUP
WHERE ROLE.PARTY_ID = RELP.PARTY_ID
AND RELP.SUBJECT_ID = PARTY.PARTY_ID
AND RELP.OBJECT_ID = ACC.PARTY_ID
AND ACC.CUST_ACCOUNT_ID = ROLE.CUST_ACCOUNT_ID
AND ROLE.ROLE_TYPE = LKUP.LOOKUP_CODE
AND LKUP.LOOKUP_TYPE = 'ACCT_ROLE_TYPE'
AND ACC.CUST_ACCOUNT_ID = CA.CUST_ACCOUNT_ID
AND CA.CUST_ACCT_SITE_ID = SU.CUST_ACCT_SITE_ID GROUP BY RELP.PARTY_ID
, PARTY.PARTY_NAME
, PARTY.ADDRESS1
, PARTY.ADDRESS2
, PARTY.ADDRESS3
, PARTY.ADDRESS4
, PARTY.CITY
, PARTY.STATE
, PARTY.POSTAL_CODE
, PARTY.PROVINCE
, PARTY.COUNTRY
, PARTY.EMAIL_ADDRESS
, ROLE.STATUS
, PARTY.URL
, ROLE.CUST_ACCOUNT_ID
, PARTY.PERSON_FIRST_NAME
, PARTY.PERSON_LAST_NAME
, ROLE.ROLE_TYPE
, LKUP.MEANING
, ACC.ACCOUNT_NUMBER
, ACC.ORIG_SYSTEM_REFERENCE
, PARTY.JGZZ_FISCAL_CODE