[Home] [Help]
273: TO_CHAR(NULL) region_2,
274: TO_CHAR(NULL) region_3
275: FROM ce_bank_branches_v cbb,
276: hz_contact_points hcp,
277: hz_locations hzl,
278: hz_party_sites hps
279: WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
280: NVL(UPPER(cbb.address_line1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
281: NVL(UPPER(cbb.address_line2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
316: TO_CHAR(NULL) region_3
317: FROM ce_bank_branches_v cbb,
318: ece_tp_details etd,
319: hz_contact_points hcp,
320: hz_locations hzl,
321: hz_party_sites hps
322: WHERE NVL(UPPER(hcp.edi_ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
323: hcp.edi_tp_header_id = etd.tp_header_id AND
324: etd.document_id = UPPER(cp_transaction_type) AND
417: hrl.country country,
418: TO_CHAR(NULL) region_1,
419: TO_CHAR(NULL) region_2,
420: TO_CHAR(NULL) region_3
421: FROM hz_locations hrl
422: WHERE NVL(UPPER(hrl.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
423: NVL(UPPER(hrl.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
424: NVL(UPPER(hrl.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
425: NVL(UPPER(hrl.city),' ') LIKE NVL(UPPER(cp_city_in),'%') AND
550: TO_CHAR(NULL) region_2,
551: TO_CHAR(NULL) region_3
552: FROM hz_cust_acct_sites_all cas,
553: hz_party_sites pts,
554: hz_locations loc
555: WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
556: -- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
557: NVL(UPPER(cas.ece_tp_location_code),' ') LIKE NVL(UPPER(cp_tp_location_code_in),'%') AND
558: NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
607: loc.country country,
608: TO_CHAR(NULL) region_1,
609: TO_CHAR(NULL) region_2,
610: TO_CHAR(NULL) region_3
611: FROM hz_locations loc
612: WHERE NVL(UPPER(loc.address1),' ') LIKE NVL(UPPER(cp_address_line1_in),'%') AND
613: NVL(UPPER(loc.address2),' ') LIKE NVL(UPPER(cp_address_line2_in),'%') AND
614: NVL(UPPER(loc.address3),' ') LIKE NVL(UPPER(cp_address_line3_in),'%') AND
615: NVL(UPPER(loc.address4),' ') LIKE NVL(UPPER(cp_address_line4_in),'%') AND
673: TO_CHAR(NULL) region_2,
674: TO_CHAR(NULL) region_3
675: FROM hz_cust_acct_sites_all cas,
676: hz_party_sites pts,
677: hz_locations loc,
678: hz_cust_accounts ca,
679: hz_parties pt
680: WHERE nvl(cas.org_id,-99) = NVL(cp_org_id_in,nvl(cas.org_id,-99)) AND
681: -- cas.cust_acct_site_id = NVL(cp_address_id_in,cas.cust_acct_site_id) AND
883: x_region_2_out,
884: x_region_3_out
885: FROM ce_bank_branches_v cbb,
886: hz_contact_points hcp,
887: hz_locations hzl,
888: hz_party_sites hps
889: WHERE cbb.branch_party_id = p_address_id_in AND
890: hcp.owner_table_id = cbb.branch_party_id AND
891: hcp.owner_table_name = 'HZ_PARTIES' AND
1330: x_region_2_out,
1331: x_region_3_out
1332: FROM hz_cust_acct_sites_all cas,
1333: hz_party_sites pts,
1334: hz_locations loc
1335:
1336:
1337: WHERE cas.party_site_id = pts.party_site_id AND
1338: pts.location_id = loc.location_id AND
1445: x_region_1_out,
1446: x_region_2_out,
1447: x_region_3_out
1448: FROM hz_party_sites pts,
1449: hz_locations loc
1450: WHERE pts.location_id = loc.location_id
1451: AND pts.party_site_id = r_addr_rec.party_site_id;
1452:
1453: EXCEPTION
1499: x_region_1_out,
1500: x_region_2_out,
1501: x_region_3_out
1502: FROM hz_party_sites pts,
1503: hz_locations loc
1504: WHERE pts.location_id = loc.location_id
1505: AND pts.party_site_id = r_addr_rec.party_site_id;
1506:
1507: EXCEPTION
1996: x_country_out,
1997: x_region_1_out,
1998: x_region_2_out,
1999: x_region_3_out
2000: FROM hz_locations hrl
2001: WHERE hrl.location_id = p_address_id_in;
2002:
2003: EXCEPTION
2004: WHEN NO_DATA_FOUND THEN