VIEW: APPS.EGO_COMPANIES_V

Source

View Text - Preformatted

SELECT company.party_id company_id, company.party_name company_name, company.address1 address1, company.address2 address2, company.address3 address3, company.address4 address4, company.city city, company.county county, company.province province, company.state state, company.postal_code postal_code, (SELECT territory_short_name FROM fnd_territories_tl WHERE company.country = territory_code AND LANGUAGE = USERENV('LANG')) AS country, CASE WHEN ( EXISTS (SELECT 1 FROM HZ_CODE_ASSIGNMENTS WHERE owner_table_id = company.party_id AND class_category = 'POS_PARTICIPANT_TYPE' AND class_code = 'ENTERPRISE' ) ) THEN ( SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'COMPANY_TYPE' AND lookup_code = 'ENTERPRISE' AND language = USERENV('LANG') ) WHEN ( EXISTS (SELECT 1 FROM hz_cust_accounts customer_account WHERE customer_account.party_id = company.party_id AND customer_account.status = 'A' ) ) THEN ( SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'COMPANY_TYPE' AND lookup_code = 'CUSTOMER' AND language = USERENV('LANG') ) ELSE ( SELECT meaning FROM fnd_lookup_values WHERE lookup_type = 'COMPANY_TYPE' AND LOOKUP_CODE = 'VENDOR' AND LANGUAGE = USERENV('LANG') ) END AS company_type FROM hz_parties company WHERE company.party_type = 'ORGANIZATION' AND company.status = 'A' AND company.party_name <> '####' AND ( EXISTS (SELECT 1 FROM hz_code_assignments WHERE owner_table_id = company.party_id AND class_category = 'POS_PARTICIPANT_TYPE' AND class_code = 'ENTERPRISE' ) OR EXISTS (SELECT 1 FROM hz_cust_accounts WHERE party_id = company.party_id AND status = 'A' ) OR EXISTS (SELECT 1 FROM ap_suppliers WHERE party_id = company.party_id) )

View Text - HTML Formatted

SELECT COMPANY.PARTY_ID COMPANY_ID
, COMPANY.PARTY_NAME COMPANY_NAME
, COMPANY.ADDRESS1 ADDRESS1
, COMPANY.ADDRESS2 ADDRESS2
, COMPANY.ADDRESS3 ADDRESS3
, COMPANY.ADDRESS4 ADDRESS4
, COMPANY.CITY CITY
, COMPANY.COUNTY COUNTY
, COMPANY.PROVINCE PROVINCE
, COMPANY.STATE STATE
, COMPANY.POSTAL_CODE POSTAL_CODE
, (SELECT TERRITORY_SHORT_NAME
FROM FND_TERRITORIES_TL
WHERE COMPANY.COUNTRY = TERRITORY_CODE
AND LANGUAGE = USERENV('LANG')) AS COUNTRY
, CASE WHEN ( EXISTS (SELECT 1
FROM HZ_CODE_ASSIGNMENTS
WHERE OWNER_TABLE_ID = COMPANY.PARTY_ID
AND CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
AND CLASS_CODE = 'ENTERPRISE' ) ) THEN ( SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'COMPANY_TYPE'
AND LOOKUP_CODE = 'ENTERPRISE'
AND LANGUAGE = USERENV('LANG') ) WHEN ( EXISTS (SELECT 1
FROM HZ_CUST_ACCOUNTS CUSTOMER_ACCOUNT
WHERE CUSTOMER_ACCOUNT.PARTY_ID = COMPANY.PARTY_ID
AND CUSTOMER_ACCOUNT.STATUS = 'A' ) ) THEN ( SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'COMPANY_TYPE'
AND LOOKUP_CODE = 'CUSTOMER'
AND LANGUAGE = USERENV('LANG') ) ELSE ( SELECT MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'COMPANY_TYPE'
AND LOOKUP_CODE = 'VENDOR'
AND LANGUAGE = USERENV('LANG') ) END AS COMPANY_TYPE
FROM HZ_PARTIES COMPANY
WHERE COMPANY.PARTY_TYPE = 'ORGANIZATION'
AND COMPANY.STATUS = 'A'
AND COMPANY.PARTY_NAME <> '####'
AND ( EXISTS (SELECT 1
FROM HZ_CODE_ASSIGNMENTS
WHERE OWNER_TABLE_ID = COMPANY.PARTY_ID
AND CLASS_CATEGORY = 'POS_PARTICIPANT_TYPE'
AND CLASS_CODE = 'ENTERPRISE' ) OR EXISTS (SELECT 1
FROM HZ_CUST_ACCOUNTS
WHERE PARTY_ID = COMPANY.PARTY_ID
AND STATUS = 'A' ) OR EXISTS (SELECT 1
FROM AP_SUPPLIERS
WHERE PARTY_ID = COMPANY.PARTY_ID) )