[Home] [Help]
PACKAGE: APPS.JTF_TERR_LOOKUP_PUB
Source
1 Package JTF_TERR_LOOKUP_PUB AUTHID CURRENT_USER AS
2 /* $Header: jtfplkus.pls 120.0 2005/06/02 18:20:45 appldev ship $ */
3
4 ---------------------------------------------------------
5 -- Start of Comments
6 -- ---------------------------------------------------
7 -- PACKAGE NAME: JTF_TERR_LOOKUP_PUB
8 -- ---------------------------------------------------
9 -- PURPOSE
10 -- Joint task force territory lookup tool api's.
11 -- This package is a public API for getting winning territories
12 -- or territory resources.
13 --
14 -- Procedures:
15 -- (see below for specification)
16 --
17 -- NOTES
18 -- This package is publicly available for use
19 --
20 -- HISTORY
21 -- 11/06/00 EIHSU Created
22 -- 01/07/01 EIHSU API-side resource detail extraction
23 -- 01/29/01 EIHSU Includes 255 -> 360 fix for bug 1614487
24 -- 09/28/01 ARPATEL changing to generic table-of-records architecture
25 -- 10/10/01 ARPATEL added p_source_id and p_trans_id to get_Winners
26 -- 10/22/01 ARPATEL adding extra parameters to get_org_contacts
27 -- 10/23/01 ARPATEL changing org_name_rec_type to include area_code, employees_total, category_code and sic_code
28 -- End of Comments
29 --
30
31 ---------------------------------------------------------
32 -- Account record format
33 -- ---------------------------------------------------
34 -- Parameters:
35 -- Required:
36 -- Defaults:
37 -- Note:
38 --
39 -- End of Comments
40
41 -- ***************************************************
42 -- GLOBAL VARIABLES and RECORD TYPE DEFINITIONS
43 -- ***************************************************
44
45 /* ---------------------------------------------------
46 -- RECORD TYPE: trans_rec_type
47 --
48 -- Description:
49 -- Territories generic assignment request type.
50 -- All requests for territory assignments inputted with
51 -- this type.
52 -- Notes:
53 -- GENERIC BULK record format copied from JTF_TERRITORY_PUB
54 -- On 6/25/2001
55 --
56 -- ----------------------------------------------------*/
57
58 TYPE trans_rec_type IS RECORD (
59
60 -- logic control properties
61 use_type VARCHAR2(30), -- refer to body for valid values of this parameter
62 source_id NUMBER,
63 transaction_id NUMBER,
64 trans_object_id NUMBER := FND_API.G_MISS_NUM,
65 trans_detail_object_id NUMBER := FND_API.G_MISS_NUM,
66
67 -- transaction qualifier values
68 SQUAL_CHAR01 VARCHAR2(360) := FND_API.G_MISS_CHAR,
69 SQUAL_CHAR02 VARCHAR2(360) := FND_API.G_MISS_CHAR,
70 SQUAL_CHAR03 VARCHAR2(360) := FND_API.G_MISS_CHAR,
71 SQUAL_CHAR04 VARCHAR2(360) := FND_API.G_MISS_CHAR,
72 SQUAL_CHAR05 VARCHAR2(360) := FND_API.G_MISS_CHAR,
73 SQUAL_CHAR06 VARCHAR2(360) := FND_API.G_MISS_CHAR,
74 SQUAL_CHAR07 VARCHAR2(360) := FND_API.G_MISS_CHAR,
75 SQUAL_CHAR08 VARCHAR2(360) := FND_API.G_MISS_CHAR,
76 SQUAL_CHAR09 VARCHAR2(360) := FND_API.G_MISS_CHAR,
77 SQUAL_CHAR10 VARCHAR2(360) := FND_API.G_MISS_CHAR,
78 SQUAL_CHAR11 VARCHAR2(360) := FND_API.G_MISS_CHAR,
79 SQUAL_CHAR12 VARCHAR2(360) := FND_API.G_MISS_CHAR,
80 SQUAL_CHAR13 VARCHAR2(360) := FND_API.G_MISS_CHAR,
81 SQUAL_CHAR14 VARCHAR2(360) := FND_API.G_MISS_CHAR,
82 SQUAL_CHAR15 VARCHAR2(360) := FND_API.G_MISS_CHAR,
83 SQUAL_CHAR16 VARCHAR2(360) := FND_API.G_MISS_CHAR,
84 SQUAL_CHAR17 VARCHAR2(360) := FND_API.G_MISS_CHAR,
85 SQUAL_CHAR18 VARCHAR2(360) := FND_API.G_MISS_CHAR,
86 SQUAL_CHAR19 VARCHAR2(360) := FND_API.G_MISS_CHAR,
87 SQUAL_CHAR20 VARCHAR2(360) := FND_API.G_MISS_CHAR,
88 SQUAL_CHAR21 VARCHAR2(360) := FND_API.G_MISS_CHAR,
89 SQUAL_CHAR22 VARCHAR2(360) := FND_API.G_MISS_CHAR,
90 SQUAL_CHAR23 VARCHAR2(360) := FND_API.G_MISS_CHAR,
91 SQUAL_CHAR24 VARCHAR2(360) := FND_API.G_MISS_CHAR,
92 SQUAL_CHAR25 VARCHAR2(360) := FND_API.G_MISS_CHAR,
93 SQUAL_CHAR26 VARCHAR2(360) := FND_API.G_MISS_CHAR,
94 SQUAL_CHAR27 VARCHAR2(360) := FND_API.G_MISS_CHAR,
95 SQUAL_CHAR28 VARCHAR2(360) := FND_API.G_MISS_CHAR,
96 SQUAL_CHAR29 VARCHAR2(360) := FND_API.G_MISS_CHAR,
97 SQUAL_CHAR30 VARCHAR2(360) := FND_API.G_MISS_CHAR,
98 SQUAL_CHAR31 VARCHAR2(360) := FND_API.G_MISS_CHAR,
99 SQUAL_CHAR32 VARCHAR2(360) := FND_API.G_MISS_CHAR,
100 SQUAL_CHAR33 VARCHAR2(360) := FND_API.G_MISS_CHAR,
101 SQUAL_CHAR34 VARCHAR2(360) := FND_API.G_MISS_CHAR,
102 SQUAL_CHAR35 VARCHAR2(360) := FND_API.G_MISS_CHAR,
103 SQUAL_CHAR36 VARCHAR2(360) := FND_API.G_MISS_CHAR,
104 SQUAL_CHAR37 VARCHAR2(360) := FND_API.G_MISS_CHAR,
105 SQUAL_CHAR38 VARCHAR2(360) := FND_API.G_MISS_CHAR,
106 SQUAL_CHAR39 VARCHAR2(360) := FND_API.G_MISS_CHAR,
107 SQUAL_CHAR40 VARCHAR2(360) := FND_API.G_MISS_CHAR,
108 SQUAL_CHAR41 VARCHAR2(360) := FND_API.G_MISS_CHAR,
109 SQUAL_CHAR42 VARCHAR2(360) := FND_API.G_MISS_CHAR,
110 SQUAL_CHAR43 VARCHAR2(360) := FND_API.G_MISS_CHAR,
111 SQUAL_CHAR44 VARCHAR2(360) := FND_API.G_MISS_CHAR,
112 SQUAL_CHAR45 VARCHAR2(360) := FND_API.G_MISS_CHAR,
113 SQUAL_CHAR46 VARCHAR2(360) := FND_API.G_MISS_CHAR,
114 SQUAL_CHAR47 VARCHAR2(360) := FND_API.G_MISS_CHAR,
115 SQUAL_CHAR48 VARCHAR2(360) := FND_API.G_MISS_CHAR,
116 SQUAL_CHAR49 VARCHAR2(360) := FND_API.G_MISS_CHAR,
117 SQUAL_CHAR50 VARCHAR2(360) := FND_API.G_MISS_CHAR,
118
119 SQUAL_NUM01 NUMBER := FND_API.G_MISS_NUM,
120 SQUAL_NUM02 NUMBER := FND_API.G_MISS_NUM,
121 SQUAL_NUM03 NUMBER := FND_API.G_MISS_NUM,
122 SQUAL_NUM04 NUMBER := FND_API.G_MISS_NUM,
123 SQUAL_NUM05 NUMBER := FND_API.G_MISS_NUM,
124 SQUAL_NUM06 NUMBER := FND_API.G_MISS_NUM,
125 SQUAL_NUM07 NUMBER := FND_API.G_MISS_NUM,
126 SQUAL_NUM08 NUMBER := FND_API.G_MISS_NUM,
127 SQUAL_NUM09 NUMBER := FND_API.G_MISS_NUM,
128 SQUAL_NUM10 NUMBER := FND_API.G_MISS_NUM,
129 SQUAL_NUM11 NUMBER := FND_API.G_MISS_NUM,
130 SQUAL_NUM12 NUMBER := FND_API.G_MISS_NUM,
131 SQUAL_NUM13 NUMBER := FND_API.G_MISS_NUM,
132 SQUAL_NUM14 NUMBER := FND_API.G_MISS_NUM,
133 SQUAL_NUM15 NUMBER := FND_API.G_MISS_NUM,
134 SQUAL_NUM16 NUMBER := FND_API.G_MISS_NUM,
135 SQUAL_NUM17 NUMBER := FND_API.G_MISS_NUM,
136 SQUAL_NUM18 NUMBER := FND_API.G_MISS_NUM,
137 SQUAL_NUM19 NUMBER := FND_API.G_MISS_NUM,
138 SQUAL_NUM20 NUMBER := FND_API.G_MISS_NUM,
139 SQUAL_NUM21 NUMBER := FND_API.G_MISS_NUM,
140 SQUAL_NUM22 NUMBER := FND_API.G_MISS_NUM,
141 SQUAL_NUM23 NUMBER := FND_API.G_MISS_NUM,
142 SQUAL_NUM24 NUMBER := FND_API.G_MISS_NUM,
143 SQUAL_NUM25 NUMBER := FND_API.G_MISS_NUM,
144 SQUAL_NUM26 NUMBER := FND_API.G_MISS_NUM,
145 SQUAL_NUM27 NUMBER := FND_API.G_MISS_NUM,
146 SQUAL_NUM28 NUMBER := FND_API.G_MISS_NUM,
147 SQUAL_NUM29 NUMBER := FND_API.G_MISS_NUM,
148 SQUAL_NUM30 NUMBER := FND_API.G_MISS_NUM,
149 SQUAL_NUM31 NUMBER := FND_API.G_MISS_NUM,
150 SQUAL_NUM32 NUMBER := FND_API.G_MISS_NUM,
151 SQUAL_NUM33 NUMBER := FND_API.G_MISS_NUM,
152 SQUAL_NUM34 NUMBER := FND_API.G_MISS_NUM,
153 SQUAL_NUM35 NUMBER := FND_API.G_MISS_NUM,
154 SQUAL_NUM36 NUMBER := FND_API.G_MISS_NUM,
155 SQUAL_NUM37 NUMBER := FND_API.G_MISS_NUM,
156 SQUAL_NUM38 NUMBER := FND_API.G_MISS_NUM,
157 SQUAL_NUM39 NUMBER := FND_API.G_MISS_NUM,
158 SQUAL_NUM40 NUMBER := FND_API.G_MISS_NUM,
159 SQUAL_NUM41 NUMBER := FND_API.G_MISS_NUM,
160 SQUAL_NUM42 NUMBER := FND_API.G_MISS_NUM,
161 SQUAL_NUM43 NUMBER := FND_API.G_MISS_NUM,
162 SQUAL_NUM44 NUMBER := FND_API.G_MISS_NUM,
163 SQUAL_NUM45 NUMBER := FND_API.G_MISS_NUM,
164 SQUAL_NUM46 NUMBER := FND_API.G_MISS_NUM,
165 SQUAL_NUM47 NUMBER := FND_API.G_MISS_NUM,
166 SQUAL_NUM48 NUMBER := FND_API.G_MISS_NUM,
167 SQUAL_NUM49 NUMBER := FND_API.G_MISS_NUM,
168 SQUAL_NUM50 NUMBER := FND_API.G_MISS_NUM
169 );
170
171 G_MISS_TRANS_REC trans_rec_type;
172
173 /* TYPE trans_tbl_type IS TABLE OF trans_rec_type
174 INDEX BY BINARY_INTEGER;
175 */
176
177
178 TYPE org_name_rec_type IS record
179 (party_id number(15),
180 location_id number(15),
181 party_site_id number(15),
182 party_site_use_id number(15),
183 party_name varchar2(360),
184 address varchar2(240),
185 city varchar2(60),
186 state varchar2(60),
187 province varchar2(60),
188 postal_code varchar2(60),
189 area_code varchar2(60),
190 county varchar2(60),
191 country varchar2(60),
192 employees_total number(15),
193 category_code varchar2(60),
194 sic_code varchar2(60),
195 primary_flag varchar2(1),
196 status varchar2(1),
197 address_type varchar2(30),
198 property1 varchar2(60),
199 property2 varchar2(60),
200 property3 varchar2(60),
201 property4 varchar2(60),
202 property5 varchar2(60));
203
204 TYPE org_name_tbl_type IS table OF org_name_rec_type
205 INDEX BY binary_integer;
206
207
208 -- This record_type stores the winning resource and their properties for the territory lookup
209 TYPE win_rsc_rec_type IS record
210 (resource_id NUMBER := FND_API.G_MISS_NUM,
211 terr_id NUMBER := FND_API.G_MISS_NUM,
212 resource_name varchar2(150) := FND_API.G_MISS_CHAR,
213 resource_phone varchar2(150) := FND_API.G_MISS_CHAR,
214 resource_job_title varchar2(150) := FND_API.G_MISS_CHAR,
215 resource_email varchar2(150) := FND_API.G_MISS_CHAR,
216 resource_mgr_name varchar2(150) := FND_API.G_MISS_CHAR,
217 resource_mgr_phone varchar2(150) := FND_API.G_MISS_CHAR,
218 resource_mgr_email varchar2(150) := FND_API.G_MISS_CHAR,
219 resource_property1 varchar2(150) := FND_API.G_MISS_CHAR,
220 resource_property2 varchar2(150) := FND_API.G_MISS_CHAR,
221 resource_property3 varchar2(150) := FND_API.G_MISS_CHAR,
222 resource_property4 varchar2(150) := FND_API.G_MISS_CHAR
223 );
224
225 TYPE win_rsc_tbl_type IS table of win_rsc_rec_type
226 INDEX BY binary_integer;
227
228
229 -- This record_type stores the winning resource and their properties for the territory lookup
230
231 TYPE winners_rec_type IS RECORD (
232
233 -- logic control properties
234
235 use_type VARCHAR2(30), -- refer to body for valid values of this parameter
236 source_id NUMBER,
237 transaction_id NUMBER,
238 trans_object_id NUMBER := FND_API.G_MISS_NUM,
239 trans_detail_object_id NUMBER := FND_API.G_MISS_NUM,
240
241 -- territory definition properties
242 terr_id NUMBER := FND_API.G_MISS_NUM,
243 terr_rsc_id NUMBER := FND_API.G_MISS_NUM,
244 terr_name NUMBER := FND_API.G_MISS_NUM,
245 top_level_terr_id NUMBER := FND_API.G_MISS_NUM,
246 absolute_rank NUMBER := FND_API.G_MISS_NUM,
247
248 -- resource definition properties
249 resource_id NUMBER := FND_API.G_MISS_NUM,
250 resource_type VARCHAR2(360) := FND_API.G_MISS_CHAR,
251 group_id NUMBER := FND_API.G_MISS_NUM,
252 role VARCHAR2(360) := FND_API.G_MISS_CHAR,
253 full_access_flag VARCHAR2(360) := FND_API.G_MISS_CHAR,
257 resource_phone VARCHAR2(360) := FND_API.G_MISS_CHAR,
254 primary_contact_flag VARCHAR2(360) := FND_API.G_MISS_CHAR,
255 resource_name VARCHAR2(360) := FND_API.G_MISS_CHAR,
256 resource_job_title VARCHAR2(360) := FND_API.G_MISS_CHAR,
258 resource_email VARCHAR2(360) := FND_API.G_MISS_CHAR,
259 resource_mgr_name VARCHAR2(360) := FND_API.G_MISS_CHAR,
260 resource_mgr_phone VARCHAR2(360) := FND_API.G_MISS_CHAR,
261 resource_mgr_email VARCHAR2(360) := FND_API.G_MISS_CHAR,
262 property1 VARCHAR2(360) := FND_API.G_MISS_CHAR,
263 property2 VARCHAR2(360) := FND_API.G_MISS_CHAR,
264 property3 VARCHAR2(360) := FND_API.G_MISS_CHAR,
265 property4 VARCHAR2(360) := FND_API.G_MISS_CHAR,
266 property5 VARCHAR2(360) := FND_API.G_MISS_CHAR,
267 property6 VARCHAR2(360) := FND_API.G_MISS_CHAR,
268 property7 VARCHAR2(360) := FND_API.G_MISS_CHAR,
269 property8 VARCHAR2(360) := FND_API.G_MISS_CHAR,
270 property9 VARCHAR2(360) := FND_API.G_MISS_CHAR,
271 property10 VARCHAR2(360) := FND_API.G_MISS_CHAR,
272 property11 VARCHAR2(360) := FND_API.G_MISS_CHAR,
273 property12 VARCHAR2(360) := FND_API.G_MISS_CHAR,
274 property13 VARCHAR2(360) := FND_API.G_MISS_CHAR,
275 property14 VARCHAR2(360) := FND_API.G_MISS_CHAR,
276 property15 VARCHAR2(360) := FND_API.G_MISS_CHAR
277
278
279 ); -- end bulk_terr_winners_rec_type
280
281
282 TYPE winners_tbl_type IS table of winners_rec_type
283 INDEX BY binary_integer;
284
285
286 -- ***************************************************
287 -- start of comments
288 -- ***************************************************
289 -- api name : Get_Organizations
290 -- type : public.
291 -- function : Get the Organization Contact info
292 -- pre-reqs : depends on hz_parties table
293 -- parameters :
294 -- end of comments
295
296 procedure Get_Org_Contacts
297 ( p_range_low IN NUMBER,
298 p_range_high IN NUMBER,
299 p_search_name IN VARCHAR2,
300 p_state IN VARCHAR2,
301 p_country IN VARCHAR2,
302 p_postal_code IN VARCHAR2,
303 p_attribute1 IN VARCHAR2,
304 p_attribute2 IN VARCHAR2,
305 p_attribute3 IN VARCHAR2,
306 p_attribute4 IN VARCHAR2,
307 p_attribute5 IN VARCHAR2,
308 p_attribute6 IN VARCHAR2,
309 p_attribute7 IN VARCHAR2,
310 p_attribute8 IN VARCHAR2,
311 p_attribute9 IN VARCHAR2,
312 p_attribute10 IN VARCHAR2,
313 p_attribute11 IN VARCHAR2,
314 p_attribute12 IN VARCHAR2,
315 p_attribute13 IN VARCHAR2,
316 p_attribute14 IN VARCHAR2,
317 p_attribute15 IN VARCHAR2,
318 x_total_rows OUT NOCOPY NUMBER,
319 x_result_tbl OUT NOCOPY org_name_tbl_type);
320
321
322 -- ***************************************************
323 -- start of comments
324 -- ***************************************************
325 -- api name : Get_WinningTerrMembers
326 -- type : public.
327 -- function : Get winning territories members for an ACCOUNT
328 -- pre-reqs : Territories needs to be setup first
329 -- parameters :
330 --
331 -- IN:
332 -- p_api_version_number IN number required
333 -- p_init_msg_list IN varchar2 optional --default = fnd_api.g_false
334 -- p_commit IN varchar2 optional --default = fnd_api.g_false
335 -- p_Org_Id IN number required
336 -- p_TerrAccount_Rec IN JTF_Account_rec_type
337 -- p_Resource_Type IN varchar2
338 -- p_Role IN varchar2,
339 --
340 -- out:
341 -- x_return_status out varchar2(1)
342 -- x_msg_count out number
343 -- x_msg_data out varchar2(2000)
344 -- x_TerrRes_tbl out TerrRes_tbl_type
345 --
346 -- requirements :
347 -- business rules :
348
349 -- version : current version 1.0
350 -- initial version: initial version 1.0
351 --
352 -- notes: Public API for retreving a set of winning
353 -- territories resources. This is an overloaded
354 -- procedure for accounts,lead, oppor, service
355 -- requests, and collections.
356 --
357 -- end of comments
358 procedure Get_Winners
359 ( p_api_version_number IN number,
360 p_init_msg_list IN varchar2 := fnd_api.g_false,
361 p_trans_rec IN trans_rec_type,
362 p_source_id IN number,
363 p_trans_id IN number,
364 p_Resource_Type IN varchar2,
365 p_Role IN varchar2,
366 x_return_status OUT NOCOPY varchar2,
367 x_msg_count OUT NOCOPY number,
368 X_msg_data OUT NOCOPY varchar2,
369 x_winners_tbl OUT NOCOPY winners_tbl_type
370 );
371
372 END JTF_TERR_LOOKUP_PUB;