[Home] [Help]
339: csp_dedicated_sites cds,
340: jtf_tasks_b jtb,
341: csp_requirement_headers crh,
342: hz_party_sites hps,
343: hz_locations hl
344: where cds.planning_parameters_id = cpp.planning_parameters_id
345: and nvl(cpp.stocking_site_excl,'N') = 'N'
346: and cpp.stocking_site_type = 'DEDICATED'
347: and jtb.address_id = cds.party_site_id
476: csi.owner_resource_type,
477: csi.owner_resource_id
478: from csp_sec_inventories csi,
479: csp_planning_parameters cpp,
480: hz_locations hl
481: where cpp.organization_id = csi.organization_id
482: and cpp.secondary_inventory = csi.secondary_inventory_name
483: and cpp.stocking_site_type = 'TECHNICIAN'
484: and hl.location_id(+) = cpp.hz_location_id
643: from
644: csp_required_parts_temp crpt,
645: csp_planning_parameters cpp,
646: csp_open_closed_v cocv,
647: hz_locations hl
648: where cpp.stocking_site_type = 'UNMANNED'
649: and nvl(cpp.stocking_site_excl,'N') = 'N'
650: and decode(p_search_params.distance,null,-1,
651: round(sdo_geom.sdo_distance(
740: from
741: csp_required_parts_temp crpt,
742: csp_planning_parameters cpp,
743: csp_open_closed_v cocv,
744: hz_locations hl
745: where cpp.stocking_site_type = 'MANNED'
746: and cpp.organization_id = cr.organization_id
747: and nvl(cpp.stocking_site_excl,'N') = 'N'
748: and hl.location_id (+) = cpp.hz_location_id
835: from
836: csp_required_parts_temp crpt,
837: csp_planning_parameters cpp,
838: csp_open_closed_v cocv,
839: hz_locations hl
840: where cpp.organization_id = p_search_params.source_organization_id
841: and nvl(cpp.stocking_site_excl,'N') = 'N'
842: and hl.location_id (+) = cpp.hz_location_id
843: and decode(p_search_params.distance,null,-1,
1063: l_result_array csf_lf_pub.csf_lf_resultarray;
1064:
1065: cursor c_my_location is
1066: select hl.geometry
1067: from hz_locations hl,
1068: csp_planning_parameters cpp
1069: where cpp.organization_id = l_organization_id
1070: and cpp.secondary_inventory = l_subinventory_code
1071: and hl.location_id = cpp.hz_location_id;
1071: and hl.location_id = cpp.hz_location_id;
1072:
1073: cursor c_to_location is
1074: select geometry
1075: from hz_locations
1076: where location_id = l_to_hz_location_id;
1077:
1078: cursor c_address is
1079: select hl.address1,hl.address2,hl.address3,hl.address4,
1078: cursor c_address is
1079: select hl.address1,hl.address2,hl.address3,hl.address4,
1080: hl.city,hl.postal_code,hl.state,ftt.territory_short_name,
1081: hl.province,hl.county
1082: from hz_locations hl, fnd_territories_tl ftt
1083: where hl.location_id = l_to_hz_location_id
1084: and ftt.territory_code = hl.country
1085: and ftt.language = 'US';
1086:
2010: l_country varchar2(150);
2011:
2012: cursor c_ship_to is
2013: select postal_code,city,decode(COUNTRY, 'CA', nvl(PROVINCE, STATE), STATE),country,nvl(timezone_id, -9999)
2014: from hz_locations
2015: where location_id = p_location_id
2016: and p_location_source = 'HZ'
2017: union all
2018: select postal_code,town_or_city,upper(region_1),country,-9999
2026: decode(hl.COUNTRY, 'CA', nvl(hl.PROVINCE, hl.STATE), hl.STATE),
2027: hl.country,
2028: hl.location_id,
2029: nvl(hl.timezone_id, -9999)
2030: FROM hz_locations hl,
2031: hz_party_sites hps,
2032: HZ_CUST_ACCT_SITES_ALL hcas,
2033: PO_LOCATION_ASSOCIATIONS_ALL pol
2034: WHERE hl.location_id = hps.location_id
2101:
2102: -- first find out source geocode
2103: SELECT GEOMETRY
2104: INTO l_src_geo
2105: FROM HZ_LOCATIONS
2106: WHERE location_id =
2107: ( SELECT DISTINCT hz_location_id
2108: FROM csp_planning_parameters
2109: WHERE organization_id = p_src_org_id
2112:
2113: -- now get destination ship_to address's geocode
2114: SELECT hloc.GEOMETRY
2115: INTO l_dest_geo
2116: FROM hz_locations hloc,
2117: hz_party_sites hps
2118: WHERE hloc.location_id = hps.location_id
2119: AND hps.party_site_id =
2120: ( SELECT DISTINCT party_site_id