[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cas.cust_account_id,pt.party_name, --Bug 2722334
ca.account_number
INTO x_entity_id_out,x_tp_location_name_out,
x_translator_code_out
FROM hz_cust_acct_sites_all cas,
hz_cust_accounts ca,
hz_parties pt
WHERE cas.cust_acct_site_id = x_address_id_out
AND cas.cust_account_id = ca.cust_account_id
AND ca.party_id = pt.party_id;
SELECT pvs.vendor_id,pv.vendor_name
INTO x_entity_id_out,x_tp_location_name_out
FROM po_vendor_sites_all pvs,
po_vendors pv
WHERE pvs.vendor_site_id = x_address_id_out AND
pvs.vendor_id = pv.vendor_id AND
ROWNUM = 1;
SELECT TO_NUMBER(NULL) org_id,
cbb.branch_party_id address_id,
hcp.edi_ece_tp_location_code tp_location_code,
cbb.bank_branch_name tp_location_name,
cbb.address_line1 address_line1,
cbb.address_line2 address_line2,
cbb.address_line3 address_line3,
cbb.address_line4 address_line4,
hzl.address_lines_phonetic address_line_alt,
cbb.city city,
hzl.county county,
cbb.state state,
cbb.zip zip,
cbb.province province,
cbb.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM ce_bank_branches_v cbb,
hz_contact_points hcp,
hz_locations hzl,
hz_party_sites hps
WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
NVL(UPPER(cbb.address_line1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(cbb.address_line2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(cbb.address_line3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(cbb.address_line4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
NVL(UPPER(hzl.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
NVL(UPPER(cbb.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(cbb.zip),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
hcp.owner_table_id = cbb.branch_party_id AND
hcp.owner_table_name = 'HZ_PARTIES' AND
hcp.contact_point_type = 'EDI' AND
hps.party_id = cbb.branch_party_id AND
hps.identifying_address_flag = 'Y' AND
hzl.location_id = hps.party_id;
SELECT TO_NUMBER(NULL) org_id,
cbb.branch_party_id address_id,
hcp.edi_ece_tp_location_code tp_location_code,
cbb.bank_branch_name tp_location_name,
cbb.address_line1 address_line1,
cbb.address_line2 address_line2,
cbb.address_line3 address_line3,
cbb.address_line4 address_line4,
hzl.address_lines_phonetic address_line_alt,
cbb.city city,
hzl.county county,
cbb.state state,
cbb.zip zip,
cbb.province province,
cbb.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM ce_bank_branches_v cbb,
ece_tp_details etd,
hz_contact_points hcp,
hz_locations hzl,
hz_party_sites hps
WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
hcp.edi_tp_header_id = etd.tp_header_id AND
etd.document_id = UPPER(cp_transaction_type) AND
NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
hcp.owner_table_id = cbb.branch_party_id AND
hcp.owner_table_name = 'HZ_PARTIES' AND
hcp.contact_point_type = 'EDI' AND
hps.party_id = cbb.branch_party_id AND
hps.identifying_address_flag = 'Y' AND
hzl.location_id = hps.party_id;
SELECT TO_NUMBER(NULL) org_id,
hrl.location_id address_id,
hrl.ece_tp_location_code tp_location_code,
hrl.location_code tp_location_name,
hrl.address_line_1 address_line1,
hrl.address_line_2 address_line2,
hrl.address_line_3 address_line3,
TO_CHAR(NULL) address_line4,
TO_CHAR(NULL) address_line_alt,
hrl.town_or_city city,
TO_CHAR(NULL) county,
TO_CHAR(NULL) state,
hrl.postal_code zip,
TO_CHAR(NULL) province,
hrl.country country,
hrl.region_1 region_1,
hrl.region_2 region_2,
hrl.region_3 region_3,
TO_CHAR(hrl.inventory_organization_id) inv_organization_id --Bug 2570369
FROM hr_locations_all hrl,
hr_locations_all_tl hrlt
WHERE NVL(UPPER(hrl.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
NVL(UPPER(hrl.address_line_1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(hrl.address_line_2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(hrl.address_line_3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(hrl.town_or_city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(hrl.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
hrl.location_id = hrlt.location_id AND
hrlt.language = userenv('LANG') AND
nvl(hrl.business_group_id, nvl(hr_general.get_business_group_id,-99)) =
nvl(hr_general.get_business_group_id,-99);
SELECT TO_NUMBER(NULL) org_id,
hrl.location_id address_id,
TO_CHAR(NULL) tp_location_code,
TO_CHAR(NULL) tp_location_name,
hrl.address1 address_line1,
hrl.address2 address_line2,
hrl.address3 address_line3,
hrl.address4 address_line4,
TO_CHAR(NULL) address_line_alt,
hrl.city city,
hrl.county county,
hrl.state state,
hrl.postal_code zip,
hrl.province province,
hrl.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM hz_locations hrl
WHERE NVL(UPPER(hrl.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(hrl.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(hrl.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(hrl.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(hrl.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%');
SELECT pvs.org_id org_id,
pvs.vendor_site_id address_id,
pvs.ece_tp_location_code tp_location_code,
TO_CHAR(NULL) tp_location_name,
pvs.address_line1 address_line1,
pvs.address_line2 address_line2,
pvs.address_line3 address_line3,
pvs.address_line4 address_line4,
pvs.address_lines_alt address_line_alt,
pvs.city city,
pvs.county county,
pvs.state state,
pvs.zip zip,
pvs.province province,
pvs.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM po_vendor_sites_all pvs
WHERE nvl(pvs.org_id,-99) = NVL(cp_org_id_in,nvl(pvs.org_id,-99)) AND
NVL(UPPER(pvs.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
NVL(UPPER(pvs.address_line1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(pvs.address_line2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(pvs.address_line3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(pvs.address_line4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
NVL(UPPER(pvs.address_lines_alt),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
NVL(UPPER(pvs.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(pvs.zip),' ') LIKE NVL(UPPER(cp_zip_in),'%') ;
SELECT pvs.org_id org_id,
pvs.vendor_site_id address_id,
pvs.ece_tp_location_code tp_location_code,
TO_CHAR(NULL) tp_location_name,
pvs.address_line1 address_line1,
pvs.address_line2 address_line2,
pvs.address_line3 address_line3,
pvs.address_line4 address_line4,
pvs.address_lines_alt address_line_alt,
pvs.city city,
pvs.county county,
pvs.state state,
pvs.zip zip,
pvs.province province,
pvs.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM po_vendor_sites_all pvs,
ece_tp_details etd
WHERE nvl(pvs.org_id,-99) = NVL(cp_org_id_in,nvl(pvs.org_id,-99)) AND
NVL(UPPER(pvs.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
pvs.tp_header_id = etd.tp_header_id AND
NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
etd.document_id LIKE UPPER(cp_transaction_type);
SELECT cas.org_id org_id,
cas.cust_acct_site_id address_id,
cas.ece_tp_location_code tp_location_code,
TO_CHAR(NULL) tp_location_name,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.address_lines_phonetic address_line_alt,
loc.city city,
loc.county county,
loc.state state,
loc.postal_code zip,
loc.province province,
loc.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM hz_cust_acct_sites_all cas,
hz_party_sites pts,
hz_locations loc
WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
-- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
cas.party_site_id = pts.party_site_id AND
pts.location_id = loc.location_id;
SELECT cas.org_id org_id,
cas.cust_acct_site_id address_id,
cas.ece_tp_location_code tp_location_code,
cas.party_site_id party_site_id
FROM hz_cust_acct_sites_all cas
WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') ;
SELECT loc.location_id location_id,
TO_CHAR(NULL) tp_location_name,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.address_lines_phonetic address_line_alt,
loc.city city,
loc.county county,
loc.state state,
loc.postal_code zip,
loc.province province,
loc.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM hz_locations loc
WHERE NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') ;
SELECT cas.org_id org_id,
cas.cust_acct_site_id address_id,
cas.ece_tp_location_code tp_location_code,
cas.party_site_id party_site_id
FROM hz_cust_acct_sites_all cas,
ece_tp_details etd
WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
cas.tp_header_id = etd.tp_header_id AND
NVL(UPPER(etd.translator_code),' ') LIKE NVL(UPPER(cp_tp_translator_code),'%') AND
etd.document_id = UPPER(cp_transaction_type);
SELECT cas.org_id org_id,
cas.cust_acct_site_id address_id,
cas.ece_tp_location_code tp_location_code,
TO_CHAR(NULL) tp_location_name,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.address_lines_phonetic address_line_alt,
loc.city city,
loc.county county,
loc.state state,
loc.postal_code zip,
loc.province province,
loc.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
FROM hz_cust_acct_sites_all cas,
hz_party_sites pts,
hz_locations loc,
hz_cust_accounts ca,
hz_parties pt
WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
-- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
NVL(UPPER(loc.address_lines_phonetic),' ') LIKE NVL(UPPER(cp_address_line_alt_in),'%') AND
NVL(UPPER(loc.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
NVL(UPPER(loc.postal_code),' ') LIKE NVL(UPPER(cp_zip_in),'%') AND
NVL(UPPER(pt.party_name),' ') LIKE NVL(UPPER(cp_customer_name_in),'%') AND
NVL(UPPER(ca.account_number),' ') LIKE NVL(UPPER(cp_customer_number_in),'%') AND
cas.party_site_id = pts.party_site_id AND
pts.location_id = loc.location_id AND
cas.cust_account_id = ca.cust_account_id AND
ca.party_id = pt.party_id;
SELECT COUNT(*) INTO n_org_id
FROM hr_organization_units
WHERE organization_id = p_org_id_in;
SELECT TO_NUMBER(NULL),
cbb.bank_party_id,
hcp.edi_ece_tp_location_code,
p_translator_code_in,
cbb.bank_branch_name,
cbb.address_line1,
cbb.address_line2,
cbb.address_line3,
cbb.address_line4,
hzl.address_lines_phonetic,
cbb.city,
hzl.county,
cbb.state,
cbb.zip,
cbb.province,
cbb.country,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)
INTO x_org_id_out,
x_address_id_out,
x_tp_location_code_out,
x_translator_code_out,
x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM ce_bank_branches_v cbb,
hz_contact_points hcp,
hz_locations hzl,
hz_party_sites hps
WHERE cbb.branch_party_id = p_address_id_in AND
hcp.owner_table_id = cbb.branch_party_id AND
hcp.owner_table_name = 'HZ_PARTIES' AND
hcp.contact_point_type = 'EDI' AND
hps.party_id = cbb.branch_party_id AND
hps.identifying_address_flag = 'Y' AND
hzl.location_id = hps.party_id;
SELECT cas.org_id,
cas.cust_acct_site_id,
cas.ece_tp_location_code,
p_translator_code_in,
TO_CHAR(NULL),
loc.address1,
loc.address2,
loc.address3,
loc.address4,
loc.address_lines_phonetic,
loc.city,
loc.county,
loc.state,
loc.postal_code,
loc.province,
loc.country,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)
INTO x_org_id_out,
x_address_id_out,
x_tp_location_code_out,
x_translator_code_out,
x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM hz_cust_acct_sites_all cas,
hz_party_sites pts,
hz_locations loc
WHERE cas.party_site_id = pts.party_site_id AND
pts.location_id = loc.location_id AND
cas.cust_acct_site_id = p_address_id_in;
SELECT TO_CHAR(NULL) tp_location_name,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.address_lines_phonetic address_line_alt,
loc.city city,
loc.county county,
loc.state state,
loc.postal_code zip,
loc.province province,
loc.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
INTO x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM hz_party_sites pts,
hz_locations loc
WHERE pts.location_id = loc.location_id
AND pts.party_site_id = r_addr_rec.party_site_id;
SELECT TO_CHAR(NULL) tp_location_name,
loc.address1 address_line1,
loc.address2 address_line2,
loc.address3 address_line3,
loc.address4 address_line4,
loc.address_lines_phonetic address_line_alt,
loc.city city,
loc.county county,
loc.state state,
loc.postal_code zip,
loc.province province,
loc.country country,
TO_CHAR(NULL) region_1,
TO_CHAR(NULL) region_2,
TO_CHAR(NULL) region_3
INTO x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM hz_party_sites pts,
hz_locations loc
WHERE pts.location_id = loc.location_id
AND pts.party_site_id = r_addr_rec.party_site_id;
SELECT cas.org_id,
cas.cust_acct_site_id,
cas.ece_tp_location_code
INTO
x_org_id_out,
x_address_id_out,
x_tp_location_code_out
FROM hz_party_sites pts,
hz_cust_acct_sites_all cas
WHERE cas.party_site_id = pts.party_site_id
AND pts.location_id = r_addr_rec.location_id
AND nvl(cas.org_id,-99) = nvl(p_org_id_in,nvl(cas.org_id,-99));
SELECT -- hrl.org_id org_id,
hrl.location_id,
hrl.ece_tp_location_code,
p_translator_code_in,
hrl.location_code,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
TO_CHAR(NULL),
TO_CHAR(NULL),
hrl.town_or_city,
TO_CHAR(NULL),
TO_CHAR(NULL),
hrl.postal_code,
TO_CHAR(NULL),
hrl.country,
hrl.region_1,
hrl.region_2,
hrl.region_3
INTO -- x_org_id_out,
x_address_id_out,
x_tp_location_code_out,
x_translator_code_out,
x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM hr_locations_all hrl
WHERE hrl.location_id = p_address_id_in;
SELECT -- hrl.org_id org_id,
hrl.location_id,
TO_CHAR(NULL),
p_translator_code_in,
TO_CHAR(NULL),
hrl.address1,
hrl.address2,
hrl.address3,
hrl.address4,
TO_CHAR(NULL),
hrl.city,
hrl.county,
hrl.state,
hrl.postal_code,
hrl.province,
hrl.country,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)
INTO -- x_org_id_out,
x_address_id_out,
x_tp_location_code_out,
x_translator_code_out,
x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM hz_locations hrl
WHERE hrl.location_id = p_address_id_in;
SELECT pvs.org_id,
pvs.vendor_site_id,
pvs.ece_tp_location_code,
p_translator_code_in,
pvs.vendor_site_code,
pvs.address_line1,
pvs.address_line2,
pvs.address_line3,
pvs.address_line4,
pvs.address_lines_alt,
pvs.city,
pvs.county,
pvs.state,
pvs.zip,
pvs.province,
pvs.country,
TO_CHAR(NULL),
TO_CHAR(NULL),
TO_CHAR(NULL)
INTO x_org_id_out,
x_address_id_out,
x_tp_location_code_out,
x_translator_code_out,
x_tp_location_name_out,
x_address_line1_out,
x_address_line2_out,
x_address_line3_out,
x_address_line4_out,
x_address_line_alt_out,
x_city_out,
x_county_out,
x_state_out,
x_zip_out,
x_province_out,
x_country_out,
x_region_1_out,
x_region_2_out,
x_region_3_out
FROM po_vendor_sites_all pvs
WHERE pvs.vendor_site_id = p_address_id_in;
select cas.cust_account_id,
cas.cust_acct_site_id
from hz_cust_acct_sites cas,
hz_cust_accounts ca,
hz_parties pt,
ece_tp_details etd
where
etd.translator_code = p_translator_code
and cas.ece_tp_location_code = p_location_code_ext
and etd.tp_header_id = cas.tp_header_id
and cas.cust_account_id = ca.cust_account_id
and ca.party_id = pt.party_id;
select pv.vendor_id, pvs.vendor_site_id
from po_vendors pv, po_vendor_sites pvs,
-- ece_tp_headers ec,
ece_tp_details etd
where
etd.translator_code = p_translator_code
-- and etd.tp_header_id = ec.tp_header_id
and pvs.ece_tp_location_code = p_location_code_ext
and etd.tp_header_id = pvs.tp_header_id
and pvs.vendor_id = pv.vendor_id;
select cbb.branch_party_id
from ce_bank_branches_v cbb,
ece_tp_details etd,
hz_contact_points hcp
where
etd.translator_code = p_translator_code
and hcp.edi_ece_tp_location_code = p_location_code_ext
and etd.tp_header_id = hcp.edi_tp_header_id
and hcp.owner_table_id = cbb.branch_party_id
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.contact_point_type = 'EDI';
select cas.cust_account_id,
cas.cust_acct_site_id
from hz_cust_acct_sites cas,
hz_cust_accounts ca,
hz_parties pt,
ece_tp_headers eth
where
eth.tp_reference_ext1 = p_reference_ext1
and eth.tp_reference_ext2 = p_reference_ext2
and eth.tp_header_id = cas.tp_header_id
and cas.cust_account_id = ca.cust_account_id
and ca.party_id = pt.party_id;
select pv.vendor_id, pvs.vendor_site_id
from po_vendors pv, po_vendor_sites pvs,
ece_tp_headers eth
where
eth.tp_reference_ext1 = p_reference_ext1
and eth.tp_reference_ext2 = p_reference_ext2
and eth.tp_header_id = pvs.tp_header_id
and pvs.vendor_id = pv.vendor_id;
select cbb.bank_party_id address_id
from ce_bank_branches_v cbb,
ece_tp_headers eth,
hz_contact_points hcp
where
eth.tp_reference_ext1 = p_reference_ext1
and eth.tp_reference_ext2 = p_reference_ext2
and eth.tp_header_id = hcp.edi_tp_header_id
and hcp.owner_table_id = cbb.branch_party_id
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.contact_point_type = 'EDI';
select
cas.ece_tp_location_code,
ec.tp_reference_ext1,
ec.tp_reference_ext2
from hz_cust_acct_sites cas,
ece_tp_headers ec
where
ec.tp_header_id = cas.tp_header_id
and cas.cust_acct_site_id = p_entity_address_id;
select pvs.ece_tp_location_code,
ec.tp_reference_ext1,
ec.tp_reference_ext2
from ece_tp_headers ec, po_vendor_sites pvs
where
pvs.vendor_site_id = p_entity_address_id
and pvs.tp_header_id = ec.tp_header_id;
select hcp.edi_ece_tp_location_code,
ec.tp_reference_ext1,
ec.tp_reference_ext2
from ece_tp_headers ec, ce_bank_branches_v cbb,
hz_contact_points hcp
where
cbb.branch_party_id = p_entity_address_id
and hcp.edi_tp_header_id = ec.tp_header_id
and hcp.owner_table_id = cbb.branch_party_id
and hcp.owner_table_name = 'HZ_PARTIES'
and hcp.contact_point_type = 'EDI';