1 PACKAGE PV_LOCATOR AUTHID CURRENT_USER AS
2 /* $Header: pvxvlcrs.pls 115.15 2003/02/25 01:23:47 amaram ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30):='PV_LOCATOR';
5
6 TYPE party_address_rec_type IS RECORD
7 (
8 PARTY_RELATION_ID HZ_PARTIES.PARTY_ID%TYPE,
9 PARTY_NAME HZ_PARTIES.PARTY_NAME%TYPE,
10 LOCATION_ID HZ_LOCATIONS.LOCATION_ID%TYPE,
11 ADDRESS_LINE1 HZ_LOCATIONS.ADDRESS1%TYPE,
12 ADDRESS_LINE2 HZ_LOCATIONS.ADDRESS2%TYPE,
13 ADDRESS_LINE3 HZ_LOCATIONS.ADDRESS3%TYPE,
14 CITY HZ_LOCATIONS.CITY%TYPE,
15 STATE HZ_LOCATIONS.STATE%TYPE,
16 COUNTRY HZ_LOCATIONS.COUNTRY%TYPE,
17 POSTAL_CODE HZ_LOCATIONS.POSTAL_CODE%TYPE,
18 DISTANCE NUMBER,
19 DISTANCE_UNIT VARCHAR2(10)
20 );
21
22
23 type party_address_rec_tbl is TABLE OF party_address_rec_type INDEX BY BINARY_INTEGER;
24
25 --getting some profile values related to tcp proticol like proxy, sontent types
26 g_input_content_type CONSTANT VARCHAR2(100):= nvl(fnd_profile.value('PV_LOCATOR_XML_CONTENT_TYPE'), 'application/x-www-form-urlencoded');
27 --g_proxy_server CONSTANT VARCHAR2(120):=nvl(fnd_profile.value('PV_LOCATOR_PROXY_SERVER'), 'www-proxy.us.oracle.com');
28 --g_proxy_port CONSTANT NUMBER:=to_number(nvl(fnd_profile.value('PV_LOCATOR_PROXY_PORT'), '80'));
29
30
31 g_proxy_server CONSTANT VARCHAR2(120):=fnd_profile.value('PV_LOCATOR_PROXY_SERVER');
32 g_proxy_port CONSTANT NUMBER:=to_number(fnd_profile.value('PV_LOCATOR_PROXY_PORT'));
33
34 --g_route_url CONSTANT VARCHAR2(128) := nvl(fnd_profile.value('PV_LOCATOR_URL_MULTI'), 'http://nh2p1.us.oracle.com:8888/servlet/routem');
35 g_geocode_url CONSTANT VARCHAR2(128) := nvl(fnd_profile.value('PV_LOCATOR_URL'), 'http://elocation.us.oracle.com/servlets/lbs');
36 g_route_url CONSTANT VARCHAR2(128) := nvl(fnd_profile.value('PV_LOCATOR_URL'), 'http://elocation.us.oracle.com/servlets/lbs');
37 --getting server related profile values
38 g_skip_server CONSTANT VARCHAR(1) :=nvl(fnd_profile.value('PV_SKIP_ELOCATION_FOR_MATCHING'), 'N');
39
40 g_distance_unit_mile CONSTANT VARCHAR2(30) :='mile';
41 g_distance_unit_km CONSTANT VARCHAR2(30) :='km';
42 g_distance_unit_meter CONSTANT VARCHAR2(30) :='meter';
43
44 --g_distance_unit_mile CONSTANT VARCHAR2(30) :=nvl(fnd_profile.value('PV_LOCATOR_DISTANCE_UNIT_MILE'), 'mile');
45 --g_distance_unit_km CONSTANT VARCHAR2(30) :=nvl(fnd_profile.value('PV_LOCATOR_DISTANCE_UNIT_KM'), 'km');
46 --g_distance_unit_meter CONSTANT VARCHAR2(30) :=nvl(fnd_profile.value('PV_LOCATOR_DISTANCE_UNIT_METER'), 'meter');
47
48
49
50 --getting some geometry related profile values that will be used in making geometry object
51 g_geometry_param1 CONSTANT NUMBER :=2001;
52 g_geometry_param2 CONSTANT NUMBER :=8307;
53
54
55 g_miles_per_meter CONSTANT NUMBER :=0.000621;
56 g_meters_per_km CONSTANT NUMBER :=1000;
57
58 --getting some spatial related profile values that will be used in distance finding function
59 --g_pi_value CONSTANT VARCHAR2(30) :=3.1415926535897932;
60 --g_torad_degree CONSTANT VARCHAR2(30) :=180.0;
61 --g_earth_radious CONSTANT VARCHAR2(30) :=6371007.000;
62
63
64
65 ---------------------------------------------------------------------
66 -- PROCEDURE
67 -- get_partners
68 --
69 -- PURPOSE
70 -- Based on the starting address, the API finds the all the partners
71 -- limited by the max number of partner returned within the distance provided
72 -- This API is used from the wrapper API for locator and opportunity matching
73 --
74 -- PARAMETERS
75 -- p_party_address_rec: the record to hold customer address.
76 -- p_partner_tbl: returns the list of partners sorted based on the distance
77 --
78 -- NOTES
79 -- 1. object_version_number will be set to 1.
80 ---------------------------------------------------------------------
81 PROCEDURE Get_Partners(
82 p_api_version IN NUMBER
83 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
84 ,p_commit IN VARCHAR2 := FND_API.g_false
85 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
86
87 ,p_customer_address IN party_address_rec_type
88 ,p_partner_tbl IN JTF_NUMBER_TABLE
89 ,p_max_no_partners IN NUMBER
90 ,p_distance IN NUMBER
91 ,p_distance_unit IN VARCHAR2
92 ,p_sort_by_distance IN VARCHAR2 := 'T'
93 ,x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE
94 ,x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE
95 ,x_distance_unit OUT NOCOPY VARCHAR2
96 ,x_return_status OUT NOCOPY VARCHAR2
97 ,x_msg_count OUT NOCOPY NUMBER
98 ,x_msg_data OUT NOCOPY VARCHAR2
99 );
100
101 ---------------------------------------------------------------------
102 -- PROCEDURE
103 -- get_locator_partners
104 --
105 -- PURPOSE
106 -- Based on the starting address, the API finds the all the partners
107 -- limited by the max number of partner returned within the distance provided
108 -- This API is used from the wrapper API for locator and opportunity matching
109 --
110 -- PARAMETERS
111 -- p_party_address_rec: the record to hold customer address.
112 -- p_partner_tbl: returns the list of partners sorted based on the distance
113 --
114 -- NOTES
115 -- 1. object_version_number will be set to 1.
116 ---------------------------------------------------------------------
117 PROCEDURE Get_Locator_Partners(
118 p_api_version IN NUMBER
119 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
120 ,p_commit IN VARCHAR2 := FND_API.g_false
121 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
122 ,p_customer_address1 IN HZ_LOCATIONS.ADDRESS1%TYPE
123 ,p_customer_address2 IN HZ_LOCATIONS.ADDRESS2%TYPE
124 ,p_customer_address3 IN HZ_LOCATIONS.ADDRESS3%TYPE
125 ,p_customer_city IN HZ_LOCATIONS.CITY%TYPE
126 ,p_customer_state IN HZ_LOCATIONS.STATE%TYPE
127 ,p_customer_country IN HZ_LOCATIONS.COUNTRY%TYPE
128 ,p_customer_postalcode IN HZ_LOCATIONS.POSTAL_CODE%TYPE
129 ,p_customer_lattitude IN VARCHAR2
130 ,p_customer_longitude IN VARCHAR2
131 ,p_max_no_partners IN NUMBER
132 ,p_distance IN NUMBER
133 ,p_distance_unit IN VARCHAR2
134 ,p_sql_query IN VARCHAR2
135 ,p_attr_id_tbl IN OUT NOCOPY JTF_NUMBER_TABLE
136 ,p_attr_value_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_4000
137 ,p_attr_operator_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100
138 ,p_attr_data_type_tbl IN OUT NOCOPY JTF_VARCHAR2_TABLE_100
139 ,x_partner_tbl OUT NOCOPY JTF_NUMBER_TABLE
140 ,x_distance_tbl OUT NOCOPY JTF_NUMBER_TABLE
141 ,x_return_status OUT NOCOPY VARCHAR2
142 ,x_msg_count OUT NOCOPY NUMBER
143 ,x_msg_data OUT NOCOPY VARCHAR2
144
145 );
146 ---------------------------------------------------------------------
147 -- PROCEDURE
148 -- get_partners_from_elocation
149 --
150 -- PURPOSE
151 --
152 --
153 -- PARAMETERS
154 -- p_party_address_rec: the record to hold customer address.
155 -- p_partner_tbl: returns the list of partners sorted based on the distance
156 --
157 -- NOTES
158 -- 1. object_version_number will be set to 1.
159 ---------------------------------------------------------------------
160 PROCEDURE Get_Partners_From_ELocation(
161 p_api_version IN NUMBER
162 ,p_init_msg_list IN VARCHAR2 := FND_API.g_false
163 ,p_commit IN VARCHAR2 := FND_API.g_false
164 ,p_validation_level IN NUMBER := FND_API.g_valid_level_full
165
166 ,p_customer_address IN party_address_rec_type
167 ,p_partner_tbl IN party_address_rec_tbl
168 ,p_max_no_partners IN NUMBER
169 ,p_distance IN NUMBER
170 ,p_distance_unit IN VARCHAR2
171 ,p_sort_by_distance IN VARCHAR2 := 'T'
172 ,x_partner_tbl OUT NOCOPY party_address_rec_tbl
173 ,x_return_status OUT NOCOPY VARCHAR2
174 ,x_msg_count OUT NOCOPY NUMBER
175 ,x_msg_data OUT NOCOPY VARCHAR2
176 );
177
178 --------------------------------------------------------------------------------
179 --
180 -- This routine computes the distance between two point geometries
181 --
182 -- mdsys.sdo_geometry geom1, The first geometry
183 -- mdsys.sdo_geometry geom2, The second geometry
184 --
185 --------------------------------------------------------------------------------
186 FUNCTION geocode_distance (geom1 MDSYS.SDO_GEOMETRY,
187 geom2 MDSYS.SDO_GEOMETRY,
188 distance_unit VARCHAR2 )
189 RETURN NUMBER DETERMINISTIC;
190
191
192 FUNCTION address_to_geometry( name VARCHAR2,
193 street VARCHAR2,
194 city VARCHAR2,
195 state VARCHAR2,
196 zip_code VARCHAR2)
197 RETURN MDSYS.SDO_GEOMETRY DETERMINISTIC;
198
199 END PV_LOCATOR;
200
[Home] [Help]