[Home] [Help]
504: , wsh_delivery_assignments wda
505: , hz_cust_site_uses_all hcsua
506: , hz_party_sites party_site
507: --, hz_loc_assignments loc_assign --13683119
508: , hz_locations loc
509: , hz_cust_acct_sites_all acct_site
510: where wdd.delivery_detail_id = wda.delivery_detail_id
511: and wdd.container_flag = 'N'
512: AND wda.delivery_id=p_delivery_detail_id
520: AND DECODE ( NVL(
521: (SELECT 1
522: FROM hz_geo_struct_map map
523: WHERE map.country_code = loc.country
524: AND map.loc_tbl_name = 'HZ_LOCATIONS'
525: AND rownum = 1
526: ), 0), 1, NVL(
527: (SELECT 2
528: FROM hz_geo_name_reference_log log1
527: (SELECT 2
528: FROM hz_geo_name_reference_log log1
529: WHERE 2 = 2
530: AND log1.location_id = loc.location_id
531: AND log1.location_table_name = 'HZ_LOCATIONS'
532: AND log1.usage_code = 'TAX'
533: AND log1.map_status = 'S'
534: AND ROWNUM = 1
535: ), 0), NVL(
1156: ,hz.address2 address_line_2,hz.address3 address_line_3
1157: ,hz.address4 address_line_4,hz.city city,hz.postal_code postal_code
1158: ,hz.state state,hz.county county,hz.country country,hz.province province
1159: ,hz.description location_code, hz.description location_description
1160: FROM hz_locations hz) hr
1161: WHERE hr.location_id = p_location_id;
1162:
1163: CURSOR c_location(p_site_use_id NUMBER) IS
1164: SELECT location
1168: CURSOR c_phone_fax(p_location_id NUMBER, p_type VARCHAR2) IS
1169: SELECT hcp.phone_country_code||decode(hcp.phone_country_code, NULL, '',' ')||
1170: decode(hcp.phone_area_code,NULL,'','(')||hcp.phone_area_code||decode(hcp.phone_area_code,NULL,'',')')||
1171: hcp.phone_number customer_site_tel_number
1172: FROM hz_party_sites hps, hz_locations hl, hz_contact_points hcp
1173: WHERE hps.location_id = hl.location_id
1174: AND hcp.owner_table_name = 'HZ_PARTY_SITES'
1175: AND hcp.owner_table_id = hps.party_site_id
1176: AND (((hcp.phone_line_type IN ('PHONE','GEN')) AND (p_type = 'PHONE')) OR