VIEW: APPS.ECX_TP_HEADERS_V

Source

View Text - Preformatted

SELECT etph.TP_HEADER_ID tp_header_id, elk.description party_type , etph.PARTY_ID party_id , etph.PARTY_SITE_ID party_site_id, hp.party_name party_name, hzl.address1 || ' ' || hzl.address2 || ' '||hzl.city ||' ' || hzl.state || ' '|| hzl.postal_code party_site_name, etph.COMPANY_ADMIN_EMAIL company_admin_email, etph.LAST_UPDATE_DATE, etph.LAST_UPDATED_BY , etph.CREATION_DATE , etph.CREATED_BY , etph.LAST_UPDATE_LOGIN , hzc.ORG_ID ORG_ID from hz_party_sites hps, hz_parties hp,hz_locations hzl, hz_cust_acct_sites hzc, ecx_lookup_values elk, ecx_tp_headers etph where hp.party_id = etph.party_id and hp.party_id = hps.party_id and hps.party_site_id = etph.party_site_id and hps.party_site_id = hzc.party_site_id and hzl.location_id = hps.location_id and etph.party_type = 'C' and elk.lookup_code = etph.party_type and elk.lookup_type = 'PARTY_TYPE' union select etph.tp_header_id tp_header_id, elk.description party_type , etph.party_id party_id, etph.party_site_id party_site_id, pv.vendor_name party_name, pvs.address_line1 || ' '|| pvs.address_line2|| ' '||pvs.city || ' ' ||pvs.state || ' '|| pvs.zip party_site_name, etph.COMPANY_ADMIN_EMAIL company_admin_email, etph.LAST_UPDATE_DATE, etph.LAST_UPDATED_BY , etph.CREATION_DATE , etph.CREATED_BY , etph.LAST_UPDATE_LOGIN, pvs.ORG_ID ORG_ID from po_vendors pv, po_vendor_sites pvs, ecx_lookup_values elk, ecx_tp_headers etph where pv.vendor_id = etph.party_id and pv.vendor_id = pvs.vendor_id and pvs.vendor_site_id = etph.party_site_id and etph.party_type = 'S' and elk.lookup_code = etph.party_type and elk.lookup_type = 'PARTY_TYPE' union select etph.tp_header_id tp_header_id, elk.description party_type , etph.party_id party_id, etph.party_site_id party_site_id, cbbv.bank_branch_name party_name, cbbv.address_line1 || ' '||cbbv.address_line2 || ' '|| cbbv.address_line3||' '||cbbv.address_line4||' '|| cbbv.city || ' ' || cbbv.state || ' '||cbbv.zip party_site_name, etph.COMPANY_ADMIN_EMAIL company_admin_email, etph.LAST_UPDATE_DATE, etph.LAST_UPDATED_BY , etph.CREATION_DATE , etph.CREATED_BY , etph.LAST_UPDATE_LOGIN, to_number(null) ORG_ID from ce_bank_branches_v cbbv, ecx_lookup_values elk, ecx_tp_headers etph where cbbv.branch_party_id = etph.party_id and etph.party_type = 'B' and elk.lookup_code = etph.party_type and elk.lookup_type = 'PARTY_TYPE' union select etph.tp_header_id tp_header_id, elk.description party_type , etph.party_id party_id, etph.party_site_id party_site_id, hrl.location_code party_name, hrl.address_line_1 || ' '||hrl.address_line_2 || hrl.address_line_3 || ' '||hrl.town_or_city || ' ' || hrl.region_2 || ' '||hrl.postal_code party_site_name, etph.COMPANY_ADMIN_EMAIL company_admin_email, etph.LAST_UPDATE_DATE, etph.LAST_UPDATED_BY , etph.CREATION_DATE , etph.CREATED_BY , etph.LAST_UPDATE_LOGIN, to_number(null) ORG_ID from hr_locations hrl, ecx_lookup_values elk, ecx_tp_headers etph where hrl.location_id = etph.party_id and etph.party_type = 'I' and elk.lookup_code = etph.party_type and elk.lookup_type = 'PARTY_TYPE' union select etph.TP_HEADER_ID tp_header_id, elk.description party_type , etph.PARTY_ID party_id , etph.PARTY_SITE_ID party_site_id, hp.party_name party_name, hzl.address1 || ' ' || hzl.address2 || ' '||hzl.city ||' ' || hzl.state || ' '|| hzl.postal_code party_site_name, etph.COMPANY_ADMIN_EMAIL company_admin_email, etph.LAST_UPDATE_DATE, etph.LAST_UPDATED_BY , etph.CREATION_DATE , etph.CREATED_BY , etph.LAST_UPDATE_LOGIN, to_number(null) ORG_ID from hz_party_sites hps, hz_parties hp,hz_locations hzl, ecx_lookup_values elk, ecx_tp_headers etph where hp.party_id = etph.party_id and hp.party_id = hps.party_id and hps.party_site_id = etph.party_site_id and hzl.location_id = hps.location_id and etph.party_type = 'CARRIER' and elk.lookup_code = etph.party_type and elk.lookup_type = 'PARTY_TYPE'

View Text - HTML Formatted

SELECT ETPH.TP_HEADER_ID TP_HEADER_ID
, ELK.DESCRIPTION PARTY_TYPE
, ETPH.PARTY_ID PARTY_ID
, ETPH.PARTY_SITE_ID PARTY_SITE_ID
, HP.PARTY_NAME PARTY_NAME
, HZL.ADDRESS1 || ' ' || HZL.ADDRESS2 || ' '||HZL.CITY ||' ' || HZL.STATE || ' '|| HZL.POSTAL_CODE PARTY_SITE_NAME
, ETPH.COMPANY_ADMIN_EMAIL COMPANY_ADMIN_EMAIL
, ETPH.LAST_UPDATE_DATE
, ETPH.LAST_UPDATED_BY
, ETPH.CREATION_DATE
, ETPH.CREATED_BY
, ETPH.LAST_UPDATE_LOGIN
, HZC.ORG_ID ORG_ID
FROM HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, HZ_LOCATIONS HZL
, HZ_CUST_ACCT_SITES HZC
, ECX_LOOKUP_VALUES ELK
, ECX_TP_HEADERS ETPH
WHERE HP.PARTY_ID = ETPH.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = ETPH.PARTY_SITE_ID
AND HPS.PARTY_SITE_ID = HZC.PARTY_SITE_ID
AND HZL.LOCATION_ID = HPS.LOCATION_ID
AND ETPH.PARTY_TYPE = 'C'
AND ELK.LOOKUP_CODE = ETPH.PARTY_TYPE
AND ELK.LOOKUP_TYPE = 'PARTY_TYPE' UNION SELECT ETPH.TP_HEADER_ID TP_HEADER_ID
, ELK.DESCRIPTION PARTY_TYPE
, ETPH.PARTY_ID PARTY_ID
, ETPH.PARTY_SITE_ID PARTY_SITE_ID
, PV.VENDOR_NAME PARTY_NAME
, PVS.ADDRESS_LINE1 || ' '|| PVS.ADDRESS_LINE2|| ' '||PVS.CITY || ' ' ||PVS.STATE || ' '|| PVS.ZIP PARTY_SITE_NAME
, ETPH.COMPANY_ADMIN_EMAIL COMPANY_ADMIN_EMAIL
, ETPH.LAST_UPDATE_DATE
, ETPH.LAST_UPDATED_BY
, ETPH.CREATION_DATE
, ETPH.CREATED_BY
, ETPH.LAST_UPDATE_LOGIN
, PVS.ORG_ID ORG_ID
FROM PO_VENDORS PV
, PO_VENDOR_SITES PVS
, ECX_LOOKUP_VALUES ELK
, ECX_TP_HEADERS ETPH
WHERE PV.VENDOR_ID = ETPH.PARTY_ID
AND PV.VENDOR_ID = PVS.VENDOR_ID
AND PVS.VENDOR_SITE_ID = ETPH.PARTY_SITE_ID
AND ETPH.PARTY_TYPE = 'S'
AND ELK.LOOKUP_CODE = ETPH.PARTY_TYPE
AND ELK.LOOKUP_TYPE = 'PARTY_TYPE' UNION SELECT ETPH.TP_HEADER_ID TP_HEADER_ID
, ELK.DESCRIPTION PARTY_TYPE
, ETPH.PARTY_ID PARTY_ID
, ETPH.PARTY_SITE_ID PARTY_SITE_ID
, CBBV.BANK_BRANCH_NAME PARTY_NAME
, CBBV.ADDRESS_LINE1 || ' '||CBBV.ADDRESS_LINE2 || ' '|| CBBV.ADDRESS_LINE3||' '||CBBV.ADDRESS_LINE4||' '|| CBBV.CITY || ' ' || CBBV.STATE || ' '||CBBV.ZIP PARTY_SITE_NAME
, ETPH.COMPANY_ADMIN_EMAIL COMPANY_ADMIN_EMAIL
, ETPH.LAST_UPDATE_DATE
, ETPH.LAST_UPDATED_BY
, ETPH.CREATION_DATE
, ETPH.CREATED_BY
, ETPH.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) ORG_ID
FROM CE_BANK_BRANCHES_V CBBV
, ECX_LOOKUP_VALUES ELK
, ECX_TP_HEADERS ETPH
WHERE CBBV.BRANCH_PARTY_ID = ETPH.PARTY_ID
AND ETPH.PARTY_TYPE = 'B'
AND ELK.LOOKUP_CODE = ETPH.PARTY_TYPE
AND ELK.LOOKUP_TYPE = 'PARTY_TYPE' UNION SELECT ETPH.TP_HEADER_ID TP_HEADER_ID
, ELK.DESCRIPTION PARTY_TYPE
, ETPH.PARTY_ID PARTY_ID
, ETPH.PARTY_SITE_ID PARTY_SITE_ID
, HRL.LOCATION_CODE PARTY_NAME
, HRL.ADDRESS_LINE_1 || ' '||HRL.ADDRESS_LINE_2 || HRL.ADDRESS_LINE_3 || ' '||HRL.TOWN_OR_CITY || ' ' || HRL.REGION_2 || ' '||HRL.POSTAL_CODE PARTY_SITE_NAME
, ETPH.COMPANY_ADMIN_EMAIL COMPANY_ADMIN_EMAIL
, ETPH.LAST_UPDATE_DATE
, ETPH.LAST_UPDATED_BY
, ETPH.CREATION_DATE
, ETPH.CREATED_BY
, ETPH.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) ORG_ID
FROM HR_LOCATIONS HRL
, ECX_LOOKUP_VALUES ELK
, ECX_TP_HEADERS ETPH
WHERE HRL.LOCATION_ID = ETPH.PARTY_ID
AND ETPH.PARTY_TYPE = 'I'
AND ELK.LOOKUP_CODE = ETPH.PARTY_TYPE
AND ELK.LOOKUP_TYPE = 'PARTY_TYPE' UNION SELECT ETPH.TP_HEADER_ID TP_HEADER_ID
, ELK.DESCRIPTION PARTY_TYPE
, ETPH.PARTY_ID PARTY_ID
, ETPH.PARTY_SITE_ID PARTY_SITE_ID
, HP.PARTY_NAME PARTY_NAME
, HZL.ADDRESS1 || ' ' || HZL.ADDRESS2 || ' '||HZL.CITY ||' ' || HZL.STATE || ' '|| HZL.POSTAL_CODE PARTY_SITE_NAME
, ETPH.COMPANY_ADMIN_EMAIL COMPANY_ADMIN_EMAIL
, ETPH.LAST_UPDATE_DATE
, ETPH.LAST_UPDATED_BY
, ETPH.CREATION_DATE
, ETPH.CREATED_BY
, ETPH.LAST_UPDATE_LOGIN
, TO_NUMBER(NULL) ORG_ID
FROM HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, HZ_LOCATIONS HZL
, ECX_LOOKUP_VALUES ELK
, ECX_TP_HEADERS ETPH
WHERE HP.PARTY_ID = ETPH.PARTY_ID
AND HP.PARTY_ID = HPS.PARTY_ID
AND HPS.PARTY_SITE_ID = ETPH.PARTY_SITE_ID
AND HZL.LOCATION_ID = HPS.LOCATION_ID
AND ETPH.PARTY_TYPE = 'CARRIER'
AND ELK.LOOKUP_CODE = ETPH.PARTY_TYPE
AND ELK.LOOKUP_TYPE = 'PARTY_TYPE'