[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_LOOKUP_PUB
Source
1 PACKAGE BODY JTF_TERR_LOOKUP_PUB AS
2 /* $Header: jtfplkub.pls 120.3 2008/08/07 09:34:29 gmarwah ship $ */
3 ---------------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_LOOKUP_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force territory lookup tool api's.
10 -- This package is a public API for getting winning territories
11 -- or territory resources.
12 --
13 -- Procedures:
14 -- (see below for specification)
15 --
16 -- NOTES
17 -- This package is publicly available for use
18 --
19 -- HISTORY
20 -- 11/06/00 EIHSU Created
21 -- 01/03/01 EIHSU performance upgrade
22 -- 01/26/01 JDOCHERT 1614487 bug fix
23 -- 03/21/01 EIHSU modified to handle output of win_rsc_tbl_rec from 1001_ACCT_DYN
24 -- 07/26/01 EIHSU modified all referenced charlist to 360list
25 -- 09/28/01 ARPATEL changing to generic table-of-records architecture
26 -- now calling JTF_TERR_ASSIGN_PUB
27 -- 10/10/01 ARPATEL added p_source_id and p_trans_id to get_Winners
28 -- 10/22/01 ARPATEL adding extra parameters to get_org_contacts
29 -- 10/25/01 EIHSU Get_Addn_Params added. This Procedure will serve
30 -- to fetch any additional information before assignment request made
31 -- 01/08/01 EIHSU bug 2170096
32 -- 03/11/04 SHLI added certification level and sort into cursor get_data
33 -- 05/18/2004 ACHANDA Bug # 3610389 : Make call to WF_NOTIFICATION.SubstituteSpecialChars
34 -- before rendering the data in jsp
35 -- 28/07/2008 GMARWAH Added code for Bug #7237992
36
37 -- End of Comments
38 --
39 -- ***************************************************
40 -- GLOBAL VARIABLES
41 -- ***************************************************
42 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_LOOKUP_PUB';
43 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfplkub.pls';
44
45 G_NEW_LINE VARCHAR2(02) := FND_GLOBAL.Local_Chr(10);
46 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
47 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
48 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
49 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
50 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
51 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
52
53
54 --
55 -- ***************************************************
56 -- start of comments
57 -- ***************************************************
58 -- api name : Get_Addn_Params
59 -- type : private.
60 -- function : Sets Additional assignment parameters for lookup
61 -- using existing parameter values
62 -- pre-reqs : requires populated generic transaction type record
63 -- party_site_id or party_id instantiated
64 -- parameters :
65
66 -- REQUIRES: llp_trans_rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type
67 -- MODIFIES: modifies SQUAL elements
68 -- EFFECTS: sets additional qualifier values
69 --
70
71 -- end of comments
72
73 procedure Get_Addn_Params
74 ( p_api_version_number IN number,
75 p_init_msg_list IN varchar2 := fnd_api.g_false,
76 llp_trans_rec IN OUT NOCOPY JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type,
77 llp_source_id IN number,
78 llp_trans_id IN number,
79 x_return_status OUT NOCOPY varchar2
80 )
81 IS
82 l_party_id NUMBER := llp_trans_rec.SQUAL_NUM01(1);
83 l_party_site_id NUMBER := llp_trans_rec.SQUAL_NUM02(1);
84 l_area_code NUMBER;
85
86
87 l_api_name CONSTANT VARCHAR2(30) := 'Get_Addn_Params';
88 l_api_version_number CONSTANT NUMBER := 1.0;
89 l_return_status VARCHAR2(1);
90 l_Counter NUMBER := 0;
91 l_RscCounter NUMBER := 0;
92 l_NumberOfWinners NUMBER ;
93 l_RetCode BOOLEAN;
94
95 BEGIN
96
97 --dbms_output.put_line('initial value - l_area_code = ' || l_area_code);
98 --dbms_output.put_line('p_trans_rec.SQUAL_CHAR08 =' || llp_trans_rec.SQUAL_CHAR08(1));
99 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB.Get_Addn_Params: Begin ');
100
101
102 FND_MSG_PUB.initialize;
103
104 -- Standard call to check for call compatibility.
105 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
106 p_api_version_number,
107 l_api_name,
108 G_PKG_NAME)
109 THEN
110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
111 END IF;
112 -- Initialize message list if p_init_msg_list is set to TRUE.
113 IF FND_API.to_Boolean( p_init_msg_list )
114 THEN
115 FND_MSG_PUB.initialize;
116 END IF;
117
118 -- Debug Message
119 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
120 THEN
121 FND_MESSAGE.Set_Name('JTF', 'Get_Addn_Params_start');
122 FND_MSG_PUB.Add;
123 END IF;
124
125 -- API body
126 x_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 If llp_source_id = -1001 then
129 -- Sales and Telesales
130 If ((llp_trans_id = -1002) and
131 ((l_party_id is not null) or (l_party_site_id is not null))) then
132 -- Account
133 --dbms_output.put_line('Check if select SQUAL_CHAR08 already populated');
134 --dbms_output.put_line('Initial SQUAL_CHAR08 value = ' || llp_trans_rec.SQUAL_CHAR08(1));
135
136 SELECT oilv.area_code into l_area_code -- , oilv.order_by_col
137 FROM (
138 SELECT iilv.area_code, iilv.order_by_col
139 FROM (
140 SELECT phon.phone_area_code area_code
141 , 0 order_by_col
142 FROM
143 HZ_CONTACT_POINTS phon, AR_LOOKUPS look
144 WHERE phon.owner_table_name(+) = 'HZ_PARTY_SITES'
145 and phon.primary_flag(+) = 'Y'
146 and phon.status(+) <> 'I'
147 and phon.phone_line_type = look.lookup_code(+)
148 and look.lookup_type(+) = 'PHONE_LINE_TYPE'
149 and phon.CONTACT_POINT_TYPE = 'PHONE'
150 and phon.owner_table_id(+) = l_party_site_id
151 UNION
152 SELECT phon.phone_area_code area_code
153 , 1 order_by_col
154 FROM
155 HZ_CONTACT_POINTS phon, AR_LOOKUPS look
156 WHERE phon.owner_table_name(+) = 'HZ_PARTIES'
157 and phon.primary_flag(+) = 'Y'
158 and phon.status(+) <> 'I'
159 and phon.phone_line_type = look.lookup_code(+)
160 and look.lookup_type(+) = 'PHONE_LINE_TYPE'
161 and phon.CONTACT_POINT_TYPE = 'PHONE'
162 and phon.owner_table_id(+) = l_party_id
163 UNION
164 Select '' area_code
165 , 2 order_by_col
166 from dual
167
168 ) iilv
169 --WHERE iilv.area_code IS NOT NULL -- empty string value permitted
170 -- eihsu bug 2170096
171 ORDER BY iilv.order_by_col
172 ) oilv
173 WHERE rownum < 2;
174 -- only if area code null or empty strig do we want to set param value
175 if llp_trans_rec.SQUAL_CHAR08(1) is null
176 then
177 llp_trans_rec.SQUAL_CHAR08(1) := l_area_code;
178 else
179 if llp_trans_rec.SQUAL_CHAR08(1) <> ''
180 then
181 llp_trans_rec.SQUAL_CHAR08(1) := l_area_code;
182 end if;
183 end if;
184 --dbms_output.put_line('llp_trans_rec.SQUAL_CHAR08(1) = ' || llp_trans_rec.SQUAL_CHAR08(1));
185
186 end if; -- transaction type
187 end if; -- source_id
188
189 --dbms_output.put_line('p_trans_rec.SQUAL_CHAR08= ' || llp_trans_rec.SQUAL_CHAR08(1));
190 END Get_Addn_Params;
191
192
193
194 --
195 -- ***************************************************
196 -- start of comments
197 -- ***************************************************
198 -- api name : Get_Organizations
199 -- type : public.
200 -- function : Get the Organization Contact info
201 -- pre-reqs : depends on hz_parties table
202 -- parameters :
203 -- end of comments
204
205 procedure Get_Org_Contacts
206 ( p_range_low IN NUMBER,
207 p_range_high IN NUMBER,
208 p_search_name IN VARCHAR2,
209 p_state IN VARCHAR2,
210 p_country IN VARCHAR2,
211 p_postal_code IN VARCHAR2,
212 p_attribute1 IN VARCHAR2,
213 p_attribute2 IN VARCHAR2,
214 p_attribute3 IN VARCHAR2,
215 p_attribute4 IN VARCHAR2,
216 p_attribute5 IN VARCHAR2,
217 p_attribute6 IN VARCHAR2,
218 p_attribute7 IN VARCHAR2,
219 p_attribute8 IN VARCHAR2,
220 p_attribute9 IN VARCHAR2,
221 p_attribute10 IN VARCHAR2,
222 p_attribute11 IN VARCHAR2,
223 p_attribute12 IN VARCHAR2,
224 p_attribute13 IN VARCHAR2,
225 p_attribute14 IN VARCHAR2,
226 p_attribute15 IN VARCHAR2,
227 x_total_rows OUT NOCOPY NUMBER,
228 x_result_tbl OUT NOCOPY org_name_tbl_type
229 )
230 IS
231 rec org_name_rec_type;
232 l_index NUMBER := 0;
233 l_search_name VARCHAR2(100) := UPPER(p_search_name) || '%';
234 l_state VARCHAR2(100) := UPPER(p_state);
235 l_country VARCHAR2(100) := UPPER(p_country);
236 l_postal_code VARCHAR2(100) := UPPER(p_postal_code);
237 l_low_bound_excl NUMBER;
238 l_high_bound_excl NUMBER;
239 l_row_count NUMBER;
240 l_total_rows NUMBER;
241 l_certfication_level VARCHAR2(100) := UPPER(p_attribute1);
242
243 cursor get_data(lc_search_name varchar2, lc_state varchar2, lc_country varchar2, lc_postal_code varchar2, lc_certification_level varchar2) is
244 SELECT
245 party.party_id party_id,
246 loc.location_id location_id,
247 site.party_site_id party_site_id,
248 0000 party_site_use_id,
249 WF_NOTIFICATION.SubstituteSpecialChars(party.party_name) party_name,
250 --'NO_CODE' category_code,
251 WF_NOTIFICATION.SubstituteSpecialChars(loc.address1) || ' '||
252 WF_NOTIFICATION.SubstituteSpecialChars(loc.address2) || ' '||
253 WF_NOTIFICATION.SubstituteSpecialChars(loc.address3) || ' '||
254 WF_NOTIFICATION.SubstituteSpecialChars(loc.address4) address,
255 WF_NOTIFICATION.SubstituteSpecialChars(loc.city) city,
256 WF_NOTIFICATION.SubstituteSpecialChars(loc.state) state,
257 WF_NOTIFICATION.SubstituteSpecialChars(loc.province) province,
258 WF_NOTIFICATION.SubstituteSpecialChars(loc.postal_code) postal_code,
259 '' area_code, --NEW
260 WF_NOTIFICATION.SubstituteSpecialChars(loc.county) county,
261 WF_NOTIFICATION.SubstituteSpecialChars(loc.country) country,
262 party.employees_total employees_total, --NEW
263 party.category_code category_code, --NEW
264 party.sic_code sic_code, --NEW
265 'X' primary_flag, --NEEDED??
266 'X' status, --NEEDED??
267 'No_type' address_type, --NEEDED??
268 WF_NOTIFICATION.SubstituteSpecialChars(arlu.meaning) property1,
269 '' property2,
270 '' property3,
271 '' property4,
272 '' property5
273
274 from HZ_PARTY_SITES site,
275 HZ_LOCATIONS loc,
276 HZ_PARTIES party,
277 AR_LOOKUPS arlu
278 WHERE site.location_id = loc.location_id(+)
279 and party.party_id = site.party_id(+)
280 AND site.status = 'A'
281 and party.party_type = 'ORGANIZATION'
282 AND party.status = 'A'
283 and ( UPPER(loc.state) = lc_state OR lc_state IS NULL )
284 and ( UPPER(loc.country) = lc_country OR lc_country IS NULL )
285 and ( UPPER(loc.postal_code) = lc_postal_code OR lc_postal_code IS NULL )
286 and ( party.certification_level = lc_certification_level or lc_certification_level IS NULL)
287 and party.certification_level = arlu.lookup_code(+)
288 and 'HZ_PARTY_CERT_LEVEL' = arlu.lookup_type(+)
289 and UPPER(party.party_name) LIKE lc_search_name
290 order by arlu.lookup_code /*, party.party_name*/ ;
291
292 -- JDOCHERT: 05/30/02: Performance reasons
293 --order by UPPER(party.party_name);
294
295
296
297 BEGIN
298
299 SELECT count(*) into l_total_rows
300 from HZ_PARTY_SITES site,
301 HZ_LOCATIONS loc,
302 HZ_PARTIES party
303 WHERE site.location_id = loc.location_id(+)
304 and party.party_id = site.party_id(+)
305 AND site.status = 'A'
306 and party.party_type = 'ORGANIZATION'
307 AND party.status = 'A'
308 and ( UPPER(loc.state) = p_state OR p_state IS NULL )
309 and ( UPPER(loc.country) = p_country OR p_country IS NULL )
310 and ( UPPER(loc.postal_code) = p_postal_code OR p_postal_code IS NULL )
311 and ( party.certification_level = p_attribute1 OR p_attribute1 IS NULL )
312 and UPPER(party.party_name) LIKE l_search_name;
313
314 x_total_rows := l_total_rows;
315
316
317 l_row_count := 0;
318
319 if p_range_low = 1 then
320 l_low_bound_excl := p_range_low - 1;
321 else
322 l_low_bound_excl := p_range_low;
323 end if;
324
325 if p_range_high < 1 then
326 l_high_bound_excl := 10; -- + 1;
327 else
328 l_high_bound_excl := p_range_high; -- + 1;
329 end if;
330
331 open get_data(l_search_name, l_state, l_country, l_postal_code, l_certfication_level);
332 loop
333 fetch get_data into rec;
334 exit when l_row_count = l_high_bound_excl;
335 exit when get_data%notfound;
336
337 l_row_count := l_row_count + 1;
338 if (l_row_count between l_low_bound_excl and l_high_bound_excl) then
339 l_index := l_index + 1;
340 x_result_tbl(l_index) := rec;
341 end if;
342 end loop;
343 close get_data;
344
345
346 END Get_Org_Contacts;
347
348
349 -- ***************************************************
350 -- start of comments
351 -- ***************************************************
352 -- api name : Get_Winners
353 -- type : public.
354 -- function : Get winning territories members for an ACCOUNT
355 -- pre-reqs : Territories needs to be setup first
356 -- parameters :
357 --
358 -- end of comments
359 procedure Get_Winners
360 ( p_api_version_number IN number,
361 p_init_msg_list IN varchar2 := fnd_api.g_false,
362 p_trans_rec IN trans_rec_type,
363 p_source_id IN number,
364 p_trans_id IN number,
365 p_Resource_Type IN varchar2,
366 p_Role IN varchar2,
367 x_return_status OUT NOCOPY varchar2,
368 x_msg_count OUT NOCOPY number,
369 x_msg_data OUT NOCOPY varchar2,
370 x_winners_tbl OUT NOCOPY winners_tbl_type
371 )
372 AS
373 l_Terr_Id NUMBER := 0;
374 lP_Init_Msg_List VARCHAR2(2000);
375 lP_resource_type VARCHAR2(60) := NULL;
376 lP_role VARCHAR2(60) := NULL;
377 lX_Return_Status VARCHAR2(01);
378 lX_Msg_Count NUMBER;
379 lX_Msg_Data VARCHAR2(2000);
380
381 --arpatel 09/28 now using generic bulk record types
382 --lp_Rec JTF_TERRITORY_PUB.JTF_Account_bulk_rec_type;
383 --lp_trans_Rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
384 lp_trans_Rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
385
386 --lx_rec JTF_TERRITORY_PUB.jtf_win_rsc_bulk_rec_type;
387 --lx_rec win_rsc_tbl_type;
388 --lx_winners_rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
389 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
390
391
392 l_api_name CONSTANT VARCHAR2(30) := 'Get_Winners';
393 l_api_version_number CONSTANT NUMBER := 1.0;
394 l_return_status VARCHAR2(1);
395 l_Counter NUMBER := 0;
396 l_RscCounter NUMBER := 0;
397 l_NumberOfWinners NUMBER ;
398 l_RetCode BOOLEAN;
399
400 dummy1 VARCHAR2(30);
401
402 l_program_name VARCHAR2(60);
403
404 BEGIN
405
406 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: begin ');
407 -- convert JTF_TERR_LOOKUP_PUB.JTF_Account_rec_type
408 -- to JTF_TERRITORY_PUB.JTF_Account_bulk_rec_type
409
410 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: Convert to bulk ');
411
412 FND_MSG_PUB.initialize;
413
414 -- Standard call to check for call compatibility.
415 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
416 p_api_version_number,
417 l_api_name,
418 G_PKG_NAME)
419 THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422 -- Initialize message list if p_init_msg_list is set to TRUE.
423 IF FND_API.to_Boolean( p_init_msg_list )
424 THEN
425 FND_MSG_PUB.initialize;
426 END IF;
427
428 -- Debug Message
429 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
430 THEN
431 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MEMBERS_ACCT_START');
432 FND_MSG_PUB.Add;
433 END IF;
434
435 -- API body
436 x_return_status := FND_API.G_RET_STS_SUCCESS;
437
438
439
440 --loop thru p_Terrlookup_tbl and assign to lp_trans_Rec
441 ---
442 --for i in p_trans_tbl.FIRST..p_trans_tbl.LAST loop
443
444 lp_trans_Rec.SQUAL_CHAR01.EXTEND;
445 lp_trans_Rec.SQUAL_CHAR02.EXTEND;
446 lp_trans_Rec.SQUAL_CHAR03.EXTEND;
447 lp_trans_Rec.SQUAL_CHAR04.EXTEND;
448 lp_trans_Rec.SQUAL_CHAR04.EXTEND;
449 lp_trans_Rec.SQUAL_CHAR05.EXTEND;
450 lp_trans_Rec.SQUAL_CHAR06.EXTEND;
451 lp_trans_Rec.SQUAL_CHAR07.EXTEND;
452 lp_trans_Rec.SQUAL_CHAR08.EXTEND;
453 lp_trans_Rec.SQUAL_CHAR09.EXTEND;
454 lp_trans_Rec.SQUAL_CHAR10.EXTEND;
455 lp_trans_Rec.SQUAL_CHAR11.EXTEND;
456 lp_trans_Rec.SQUAL_CHAR12.EXTEND;
457 lp_trans_Rec.SQUAL_CHAR13.EXTEND;
458 lp_trans_Rec.SQUAL_CHAR14.EXTEND;
459 lp_trans_Rec.SQUAL_CHAR15.EXTEND;
460 lp_trans_Rec.SQUAL_CHAR16.EXTEND;
461 lp_trans_Rec.SQUAL_CHAR17.EXTEND;
462 lp_trans_Rec.SQUAL_CHAR18.EXTEND;
463 lp_trans_Rec.SQUAL_CHAR19.EXTEND;
464 lp_trans_Rec.SQUAL_CHAR20.EXTEND;
465 lp_trans_Rec.SQUAL_CHAR21.EXTEND;
466 lp_trans_Rec.SQUAL_CHAR22.EXTEND;
467 lp_trans_Rec.SQUAL_CHAR23.EXTEND;
468 lp_trans_Rec.SQUAL_CHAR24.EXTEND;
469 lp_trans_Rec.SQUAL_CHAR25.EXTEND;
470 lp_trans_Rec.SQUAL_CHAR26.EXTEND;
471 lp_trans_Rec.SQUAL_CHAR27.EXTEND;
472 lp_trans_Rec.SQUAL_CHAR28.EXTEND;
473 lp_trans_Rec.SQUAL_CHAR29.EXTEND;
474 lp_trans_Rec.SQUAL_CHAR30.EXTEND;
475 lp_trans_Rec.SQUAL_CHAR31.EXTEND;
476 lp_trans_Rec.SQUAL_CHAR32.EXTEND;
477 lp_trans_Rec.SQUAL_CHAR33.EXTEND;
478 lp_trans_Rec.SQUAL_CHAR34.EXTEND;
479 lp_trans_Rec.SQUAL_CHAR35.EXTEND;
480 lp_trans_Rec.SQUAL_CHAR36.EXTEND;
481 lp_trans_Rec.SQUAL_CHAR37.EXTEND;
482 lp_trans_Rec.SQUAL_CHAR38.EXTEND;
483 lp_trans_Rec.SQUAL_CHAR39.EXTEND;
484 lp_trans_Rec.SQUAL_CHAR40.EXTEND;
485 lp_trans_Rec.SQUAL_CHAR41.EXTEND;
486 lp_trans_Rec.SQUAL_CHAR42.EXTEND;
487 lp_trans_Rec.SQUAL_CHAR43.EXTEND;
488 lp_trans_Rec.SQUAL_CHAR44.EXTEND;
489 lp_trans_Rec.SQUAL_CHAR45.EXTEND;
490 lp_trans_Rec.SQUAL_CHAR46.EXTEND;
491 lp_trans_Rec.SQUAL_CHAR47.EXTEND;
492 lp_trans_Rec.SQUAL_CHAR48.EXTEND;
493 lp_trans_Rec.SQUAL_CHAR49.EXTEND;
494 lp_trans_Rec.SQUAL_CHAR50.EXTEND;
495
496 lp_trans_Rec.SQUAL_NUM01.EXTEND;
497 lp_trans_Rec.SQUAL_NUM02.EXTEND;
498 lp_trans_Rec.SQUAL_NUM03.EXTEND;
499 lp_trans_Rec.SQUAL_NUM04.EXTEND;
500 lp_trans_Rec.SQUAL_NUM05.EXTEND;
501 lp_trans_Rec.SQUAL_NUM06.EXTEND;
502 lp_trans_Rec.SQUAL_NUM07.EXTEND;
503 lp_trans_Rec.SQUAL_NUM08.EXTEND;
504 lp_trans_Rec.SQUAL_NUM09.EXTEND;
505 lp_trans_Rec.SQUAL_NUM10.EXTEND;
506 lp_trans_Rec.SQUAL_NUM11.EXTEND;
507 lp_trans_Rec.SQUAL_NUM12.EXTEND;
508 lp_trans_Rec.SQUAL_NUM13.EXTEND;
509 lp_trans_Rec.SQUAL_NUM14.EXTEND;
510 lp_trans_Rec.SQUAL_NUM15.EXTEND;
511 lp_trans_Rec.SQUAL_NUM16.EXTEND;
512 lp_trans_Rec.SQUAL_NUM17.EXTEND;
513 lp_trans_Rec.SQUAL_NUM18.EXTEND;
514 lp_trans_Rec.SQUAL_NUM19.EXTEND;
515 lp_trans_Rec.SQUAL_NUM20.EXTEND;
516 lp_trans_Rec.SQUAL_NUM21.EXTEND;
517 lp_trans_Rec.SQUAL_NUM22.EXTEND;
518 lp_trans_Rec.SQUAL_NUM23.EXTEND;
519 lp_trans_Rec.SQUAL_NUM24.EXTEND;
520 lp_trans_Rec.SQUAL_NUM25.EXTEND;
521 lp_trans_Rec.SQUAL_NUM26.EXTEND;
522 lp_trans_Rec.SQUAL_NUM27.EXTEND;
523 lp_trans_Rec.SQUAL_NUM28.EXTEND;
524 lp_trans_Rec.SQUAL_NUM29.EXTEND;
525 lp_trans_Rec.SQUAL_NUM30.EXTEND;
526 lp_trans_Rec.SQUAL_NUM31.EXTEND;
527 lp_trans_Rec.SQUAL_NUM32.EXTEND;
528 lp_trans_Rec.SQUAL_NUM33.EXTEND;
529 lp_trans_Rec.SQUAL_NUM34.EXTEND;
530 lp_trans_Rec.SQUAL_NUM35.EXTEND;
531 lp_trans_Rec.SQUAL_NUM36.EXTEND;
532 lp_trans_Rec.SQUAL_NUM37.EXTEND;
533 lp_trans_Rec.SQUAL_NUM38.EXTEND;
534 lp_trans_Rec.SQUAL_NUM39.EXTEND;
535 lp_trans_Rec.SQUAL_NUM40.EXTEND;
536 lp_trans_Rec.SQUAL_NUM41.EXTEND;
537 lp_trans_Rec.SQUAL_NUM42.EXTEND;
538 lp_trans_Rec.SQUAL_NUM43.EXTEND;
539 lp_trans_Rec.SQUAL_NUM44.EXTEND;
540 lp_trans_Rec.SQUAL_NUM45.EXTEND;
541 lp_trans_Rec.SQUAL_NUM46.EXTEND;
542 lp_trans_Rec.SQUAL_NUM47.EXTEND;
543 lp_trans_Rec.SQUAL_NUM48.EXTEND;
544 lp_trans_Rec.SQUAL_NUM49.EXTEND;
545 lp_trans_Rec.SQUAL_NUM50.EXTEND;
546 lp_trans_Rec.trans_object_id.EXTEND;
547 lp_trans_Rec.trans_detail_object_id.EXTEND;
548
549
550 lp_trans_Rec.trans_object_id(1) := p_trans_rec.trans_object_id;
551 lp_trans_Rec.trans_detail_object_id(1):= p_trans_rec.trans_detail_object_id;
552
553 lp_trans_Rec.SQUAL_CHAR01(1) := p_trans_rec.SQUAL_CHAR01;
554 lp_trans_Rec.SQUAL_CHAR02(1) := p_trans_rec.SQUAL_CHAR02;
555 lp_trans_Rec.SQUAL_CHAR03(1) := p_trans_rec.SQUAL_CHAR03;
556 lp_trans_Rec.SQUAL_CHAR04(1) := p_trans_rec.SQUAL_CHAR04;
557 lp_trans_Rec.SQUAL_CHAR05(1) := p_trans_rec.SQUAL_CHAR05;
558 lp_trans_Rec.SQUAL_CHAR06(1) := p_trans_rec.SQUAL_CHAR06;
559 lp_trans_Rec.SQUAL_CHAR07(1) := p_trans_rec.SQUAL_CHAR07;
560 lp_trans_Rec.SQUAL_CHAR08(1) := p_trans_rec.SQUAL_CHAR08;
561 lp_trans_Rec.SQUAL_CHAR09(1) := p_trans_rec.SQUAL_CHAR09;
562 lp_trans_Rec.SQUAL_CHAR10(1) := p_trans_rec.SQUAL_CHAR10;
563 lp_trans_Rec.SQUAL_CHAR11(1) := p_trans_rec.SQUAL_CHAR11;
564 lp_trans_Rec.SQUAL_CHAR12(1) := p_trans_rec.SQUAL_CHAR12;
565 lp_trans_Rec.SQUAL_CHAR13(1) := p_trans_rec.SQUAL_CHAR13;
566 lp_trans_Rec.SQUAL_CHAR14(1) := p_trans_rec.SQUAL_CHAR14;
567 lp_trans_Rec.SQUAL_CHAR15(1) := p_trans_rec.SQUAL_CHAR15;
568 lp_trans_Rec.SQUAL_CHAR16(1) := p_trans_rec.SQUAL_CHAR16;
569 lp_trans_Rec.SQUAL_CHAR17(1) := p_trans_rec.SQUAL_CHAR17;
570 lp_trans_Rec.SQUAL_CHAR18(1) := p_trans_rec.SQUAL_CHAR18;
571 lp_trans_Rec.SQUAL_CHAR19(1) := p_trans_rec.SQUAL_CHAR19;
572 lp_trans_Rec.SQUAL_CHAR20(1) := p_trans_rec.SQUAL_CHAR20;
573 lp_trans_Rec.SQUAL_CHAR21(1) := p_trans_rec.SQUAL_CHAR21;
574 lp_trans_Rec.SQUAL_CHAR22(1) := p_trans_rec.SQUAL_CHAR22;
575 lp_trans_Rec.SQUAL_CHAR23(1) := p_trans_rec.SQUAL_CHAR23;
576 lp_trans_Rec.SQUAL_CHAR24(1) := p_trans_rec.SQUAL_CHAR24;
577 lp_trans_Rec.SQUAL_CHAR25(1) := p_trans_rec.SQUAL_CHAR25;
578 lp_trans_Rec.SQUAL_CHAR26(1) := p_trans_rec.SQUAL_CHAR26;
579 lp_trans_Rec.SQUAL_CHAR27(1) := p_trans_rec.SQUAL_CHAR27;
580 lp_trans_Rec.SQUAL_CHAR28(1) := p_trans_rec.SQUAL_CHAR28;
581 lp_trans_Rec.SQUAL_CHAR29(1) := p_trans_rec.SQUAL_CHAR29;
582 lp_trans_Rec.SQUAL_CHAR30(1) := p_trans_rec.SQUAL_CHAR30;
583 lp_trans_Rec.SQUAL_CHAR31(1) := p_trans_rec.SQUAL_CHAR31;
584 lp_trans_Rec.SQUAL_CHAR32(1) := p_trans_rec.SQUAL_CHAR32;
585 lp_trans_Rec.SQUAL_CHAR33(1) := p_trans_rec.SQUAL_CHAR33;
586 lp_trans_Rec.SQUAL_CHAR34(1) := p_trans_rec.SQUAL_CHAR34;
587 lp_trans_Rec.SQUAL_CHAR35(1) := p_trans_rec.SQUAL_CHAR35;
588 lp_trans_Rec.SQUAL_CHAR36(1) := p_trans_rec.SQUAL_CHAR36;
589 lp_trans_Rec.SQUAL_CHAR37(1) := p_trans_rec.SQUAL_CHAR37;
590 lp_trans_Rec.SQUAL_CHAR38(1) := p_trans_rec.SQUAL_CHAR38;
591 lp_trans_Rec.SQUAL_CHAR39(1) := p_trans_rec.SQUAL_CHAR39;
592 lp_trans_Rec.SQUAL_CHAR40(1) := p_trans_rec.SQUAL_CHAR40;
593 lp_trans_Rec.SQUAL_CHAR41(1) := p_trans_rec.SQUAL_CHAR41;
594 lp_trans_Rec.SQUAL_CHAR42(1) := p_trans_rec.SQUAL_CHAR42;
595 lp_trans_Rec.SQUAL_CHAR43(1) := p_trans_rec.SQUAL_CHAR43;
596 lp_trans_Rec.SQUAL_CHAR44(1) := p_trans_rec.SQUAL_CHAR44;
597 lp_trans_Rec.SQUAL_CHAR45(1) := p_trans_rec.SQUAL_CHAR45;
598 lp_trans_Rec.SQUAL_CHAR46(1) := p_trans_rec.SQUAL_CHAR46;
599 lp_trans_Rec.SQUAL_CHAR47(1) := p_trans_rec.SQUAL_CHAR47;
600 lp_trans_Rec.SQUAL_CHAR48(1) := p_trans_rec.SQUAL_CHAR48;
601 lp_trans_Rec.SQUAL_CHAR49(1) := p_trans_rec.SQUAL_CHAR49;
602 lp_trans_Rec.SQUAL_CHAR50(1) := p_trans_rec.SQUAL_CHAR50;
603
604 lp_trans_Rec.SQUAL_NUM01(1) := p_trans_rec.SQUAL_NUM01;
605 lp_trans_Rec.SQUAL_NUM02(1) := p_trans_rec.SQUAL_NUM02;
606 lp_trans_Rec.SQUAL_NUM03(1) := p_trans_rec.SQUAL_NUM03;
607 lp_trans_Rec.SQUAL_NUM04(1) := p_trans_rec.SQUAL_NUM04;
608 lp_trans_Rec.SQUAL_NUM05(1) := p_trans_rec.SQUAL_NUM05;
609 lp_trans_Rec.SQUAL_NUM06(1) := p_trans_rec.SQUAL_NUM06;
610 lp_trans_Rec.SQUAL_NUM07(1) := p_trans_rec.SQUAL_NUM07;
611 lp_trans_Rec.SQUAL_NUM08(1) := p_trans_rec.SQUAL_NUM08;
612 lp_trans_Rec.SQUAL_NUM09(1) := p_trans_rec.SQUAL_NUM09;
613 lp_trans_Rec.SQUAL_NUM10(1) := p_trans_rec.SQUAL_NUM10;
614 lp_trans_Rec.SQUAL_NUM11(1) := p_trans_rec.SQUAL_NUM11;
615 lp_trans_Rec.SQUAL_NUM12(1) := p_trans_rec.SQUAL_NUM12;
616 lp_trans_Rec.SQUAL_NUM13(1) := p_trans_rec.SQUAL_NUM13;
617 lp_trans_Rec.SQUAL_NUM14(1) := p_trans_rec.SQUAL_NUM14;
618 lp_trans_Rec.SQUAL_NUM15(1) := p_trans_rec.SQUAL_NUM15;
619 lp_trans_Rec.SQUAL_NUM16(1) := p_trans_rec.SQUAL_NUM16;
620 lp_trans_Rec.SQUAL_NUM17(1) := p_trans_rec.SQUAL_NUM17;
621 lp_trans_Rec.SQUAL_NUM18(1) := p_trans_rec.SQUAL_NUM18;
622 lp_trans_Rec.SQUAL_NUM19(1) := p_trans_rec.SQUAL_NUM19;
623 lp_trans_Rec.SQUAL_NUM20(1) := p_trans_rec.SQUAL_NUM20;
624 lp_trans_Rec.SQUAL_NUM21(1) := p_trans_rec.SQUAL_NUM21;
625 lp_trans_Rec.SQUAL_NUM22(1) := p_trans_rec.SQUAL_NUM22;
626 lp_trans_Rec.SQUAL_NUM23(1) := p_trans_rec.SQUAL_NUM23;
627 lp_trans_Rec.SQUAL_NUM24(1) := p_trans_rec.SQUAL_NUM24;
628 lp_trans_Rec.SQUAL_NUM25(1) := p_trans_rec.SQUAL_NUM25;
629 lp_trans_Rec.SQUAL_NUM26(1) := p_trans_rec.SQUAL_NUM26;
630 lp_trans_Rec.SQUAL_NUM27(1) := p_trans_rec.SQUAL_NUM27;
631 lp_trans_Rec.SQUAL_NUM28(1) := p_trans_rec.SQUAL_NUM28;
632 lp_trans_Rec.SQUAL_NUM29(1) := p_trans_rec.SQUAL_NUM29;
633 lp_trans_Rec.SQUAL_NUM30(1) := p_trans_rec.SQUAL_NUM30;
634 lp_trans_Rec.SQUAL_NUM31(1) := p_trans_rec.SQUAL_NUM31;
635 lp_trans_Rec.SQUAL_NUM32(1) := p_trans_rec.SQUAL_NUM32;
636 lp_trans_Rec.SQUAL_NUM33(1) := p_trans_rec.SQUAL_NUM33;
637 lp_trans_Rec.SQUAL_NUM34(1) := p_trans_rec.SQUAL_NUM34;
638 lp_trans_Rec.SQUAL_NUM35(1) := p_trans_rec.SQUAL_NUM35;
639 lp_trans_Rec.SQUAL_NUM36(1) := p_trans_rec.SQUAL_NUM36;
640 lp_trans_Rec.SQUAL_NUM37(1) := p_trans_rec.SQUAL_NUM37;
641 lp_trans_Rec.SQUAL_NUM38(1) := p_trans_rec.SQUAL_NUM38;
642 lp_trans_Rec.SQUAL_NUM39(1) := p_trans_rec.SQUAL_NUM39;
643 lp_trans_Rec.SQUAL_NUM40(1) := p_trans_rec.SQUAL_NUM40;
644 lp_trans_Rec.SQUAL_NUM41(1) := p_trans_rec.SQUAL_NUM41;
645 lp_trans_Rec.SQUAL_NUM42(1) := p_trans_rec.SQUAL_NUM42;
646 lp_trans_Rec.SQUAL_NUM43(1) := p_trans_rec.SQUAL_NUM43;
647 lp_trans_Rec.SQUAL_NUM44(1) := p_trans_rec.SQUAL_NUM44;
648 lp_trans_Rec.SQUAL_NUM45(1) := p_trans_rec.SQUAL_NUM45;
649 lp_trans_Rec.SQUAL_NUM46(1) := p_trans_rec.SQUAL_NUM46;
650 lp_trans_Rec.SQUAL_NUM47(1) := p_trans_rec.SQUAL_NUM47;
651 lp_trans_Rec.SQUAL_NUM48(1) := p_trans_rec.SQUAL_NUM48;
652 lp_trans_Rec.SQUAL_NUM49(1) := p_trans_rec.SQUAL_NUM49;
653 lp_trans_Rec.SQUAL_NUM50(1) := p_trans_rec.SQUAL_NUM50;
654
655 /* ARPATEL 10/08/03 bug#3178500 fix */
656 IF ( lp_trans_Rec.SQUAL_NUM01(1) IS NULL ) THEN
657
658 --Need to set this to a dummy value for new multiple winners processing to return results
659 lp_trans_Rec.SQUAL_NUM01(1) := -777666555444333222111;
660
661 END If;
662
663 -- Need to initialise this to NULL for API to execute successfully
664 lp_trans_Rec.SQUAL_CURC01.EXTEND;
665 lp_trans_Rec.SQUAL_CURC01(1) := NULL;
666
667 /* ARPATEL 10/08/03 bug#3178500 end fix */
668
669 -- end loop;
670
671 --dbms_output.put_line('Prior to get_addn_params lp_trans_Rec.SQUAL_CHAR08(1)= ' || lp_trans_Rec.SQUAL_CHAR08(1));
672 -- set any additional qualifier values before assignment request
673 --dbms_output.put_line('Getting Additional Params ');
674 Get_Addn_Params
675 ( p_api_version_number => 1.0,
676 p_init_msg_list => lP_Init_Msg_List,
677 llp_trans_rec => lp_trans_Rec,
678 llp_source_id => p_source_id,
679 llp_trans_id => p_trans_id,
680 x_return_status => lx_return_status
681 );
682
683 --dbms_output.put_line('Resetting global vars ');
684 --Reset the global variables
685 l_RetCode := JTF_TERRITORY_GLOBAL_PUB.Reset;
686
687
688 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: Call API ');
689 -- API Call for winning territory resources
690 /*
691 jtf_terr_1001_account_dyn.search_terr_rules_all(
692 p_Rec => lp_Rec,
693 x_rec => x_winners_tbl
694 );
695 */
696
697 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: Call assign API ');
698 --------arpatel 09/28 Now Calling Generic Assign package----------
699 /*
700 JTF_TERR_ASSIGN_PUB.get_winners
701 ( p_api_version_number => p_api_version_number,
702 p_init_msg_list => p_init_msg_list,
703 p_use_type => 'LOOKUP',
704 p_source_id => p_source_id, -- -1001 Oracle Sales
705 p_trans_id => p_trans_id, -- -1002 Account
706 p_trans_rec => lp_trans_Rec,
707 p_resource_type => FND_API.G_MISS_CHAR,
708 p_role => FND_API.G_MISS_CHAR,
709 p_top_level_terr_id => FND_API.G_MISS_NUM,
710 p_num_winners => FND_API.G_MISS_NUM,
711 x_return_status => lx_return_status,
712 x_msg_count => lx_msg_count,
713 x_msg_data => lx_msg_data,
714 x_winners_rec => lx_winners_rec
715 );
716 */ -- Code commented for bug# 7237992
717
718 -- Code added for bug# 7237992
719 -- Please not that the code has been derived from JTF_TERR_ASSIGN_PUB.get_winners
720 -- in future if there is a change in the package, please make the corresponding changes to
721 -- this package too.
722 IF ( p_source_id = -1001 AND p_trans_id = -1002) THEN
723 l_program_name := 'SALES/ACCOUNT PROGRAM';
724
725 DELETE jty_terr_1001_account_trans_gt;
726
727 FORALL i IN lp_trans_rec.trans_object_id.FIRST .. lp_trans_rec.trans_object_id.LAST
728 INSERT INTO jty_terr_1001_account_trans_gt (
729 TRANS_OBJECT_ID
730 ,TRANS_DETAIL_OBJECT_ID
731 ,COMP_NAME_RANGE
732 ,POSTAL_CODE
733 ,COUNTRY
734 ,CITY
735 ,STATE
736 ,PROVINCE
737 ,COUNTY
738 ,INTEREST_TYPE_ID
739 ,PARTY_ID
740 ,PARTY_SITE_ID
741 ,AREA_CODE
742 ,PARTNER_ID
743 ,NUM_OF_EMPLOYEES
744 ,CATEGORY_CODE
745 ,PARTY_RELATIONSHIP_ID
746 ,SIC_CODE
747 ,SQUAL_NUM06
748 ,CAR_CURRENCY_CODE
749 ,ATTRIBUTE5
750 ,SQUAL_CHAR11
751 ,txn_date
752 )
753 VALUES (
754 -1001
755 ,-1002
756 ,lp_trans_rec.SQUAL_CHAR01(i) -- comp_name_range
757 ,lp_trans_rec.SQUAL_CHAR06(i) -- postal code
758 ,lp_trans_rec.SQUAL_CHAR07(i) -- country
759 ,lp_trans_rec.SQUAL_CHAR02(i) -- city
760 ,lp_trans_rec.SQUAL_CHAR04(i) -- state
761 ,lp_trans_rec.SQUAL_CHAR05(i) -- province
762 ,lp_trans_rec.SQUAL_CHAR03(i) -- county
763 ,lp_trans_rec.SQUAL_NUM07(i) --INTEREST_TYPE_ID
764 ,lp_trans_rec.SQUAL_NUM01(i) --PARTY_ID
765 ,lp_trans_rec.SQUAL_NUM02(i)--PARTY_SITE_ID
766 ,lp_trans_rec.SQUAL_CHAR08(i) --AREA_CODE
767 ,lp_trans_rec.SQUAL_NUM03(i) --PARTNER_ID
768 ,lp_trans_rec.SQUAL_NUM05(i)--NUM_OF_EMPLOYEES
769 ,lp_trans_rec.SQUAL_CHAR09(i) --CATEGORY_CODE
770 ,NULL--PARTY_RELATIONSHIP_ID
771 ,lp_trans_rec.SQUAL_CHAR10(i)--SIC_CODE
772 ,lp_trans_rec.SQUAL_NUM06(i)--SQUAL_NUM06
773 ,NULL--CAR_CURRENCY_CODE
774 ,NULL--ATTRIBUTE5
775 ,lp_trans_rec.SQUAL_CHAR11(i)--SQUAL_CHAR11
776 ,sysdate
777 );
778 commit;
779 END IF;
780 JTY_ASSIGN_REALTIME_PUB.process_match (
781 p_source_id => p_source_id
782 ,p_trans_id => p_trans_id
783 ,p_mode => 'REAL TIME:LOOKUP'
784 ,p_program_name => l_program_name
785 ,x_return_status => lx_return_status
786 ,x_msg_count => lx_msg_count
787 ,x_msg_data => lx_msg_data);
788
789 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
790 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
791 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
792 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_match',
793 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
794 END IF;
795 RAISE FND_API.G_EXC_ERROR;
796 END IF;
797
798 -- debug message
799 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
800 FND_LOG.string(FND_LOG.LEVEL_EVENT,
801 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_match',
802 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
803 END IF;
804
805 JTY_ASSIGN_REALTIME_PUB.process_winners (
806 p_source_id => p_source_id
807 ,p_trans_id => p_trans_id
808 ,p_program_name => l_program_name
809 ,p_mode => 'REAL TIME:LOOKUP'
810 ,p_role => null
811 ,p_resource_type => null
812 ,x_return_status => lx_return_status
813 ,x_msg_count => lx_msg_count
814 ,x_msg_data => lx_msg_data
815 ,x_winners_rec => lx_winners_rec);
816
817 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
818 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
819 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
820 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_winners',
821 'JTY_ASSIGN_REALTIME_PUB.process_winners has failed');
822 END IF;
823 RAISE FND_API.G_EXC_ERROR;
824 END IF;
825
826 -- debug message
827 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
828 FND_LOG.string(FND_LOG.LEVEL_EVENT,
829 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_winners',
830 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
831 END IF;
832
833 -- End of Code addition
834
835 --loop thru lx_winners_rec and assign to x_winners_tbl
836 ---
837
838 --dbms_output.put_line('lx_winners_rec.terr_id.FIRST: ' || lx_winners_rec.terr_id.FIRST);
839 --dbms_output.put_line('lx_winners_rec.terr_id.LAST: ' || lx_winners_rec.terr_id.LAST);
840
841 if lx_winners_rec.terr_id.FIRST is not null then
842 for i in lx_winners_rec.terr_id.FIRST..lx_winners_rec.terr_id.LAST loop
843
844 -- Added for Bug 7237992 for handling null values
845 lx_winners_rec.trans_object_id.EXTEND;
846 lx_winners_rec.trans_detail_object_id.EXTEND;
847 lx_winners_rec.terr_rsc_id.EXTEND;
848 lx_winners_rec.absolute_rank.EXTEND;
849 lx_winners_rec.resource_id.EXTEND;
850 lx_winners_rec.resource_type.EXTEND;
851 lx_winners_rec.group_id.EXTEND;
852 lx_winners_rec.role.EXTEND;
853 lx_winners_rec.full_access_flag.EXTEND;
854 lx_winners_rec.primary_contact_flag.EXTEND;
855 lx_winners_rec.resource_name.EXTEND;
856 lx_winners_rec.resource_job_title.EXTEND;
857 lx_winners_rec.resource_phone.EXTEND;
858 lx_winners_rec.resource_email.EXTEND;
859 lx_winners_rec.resource_mgr_name.EXTEND;
860 lx_winners_rec.resource_mgr_phone.EXTEND;
861 lx_winners_rec.resource_mgr_email.EXTEND;
862 lx_winners_rec.terr_id.EXTEND;
863 lx_winners_rec.property1.EXTEND;
864 lx_winners_rec.property2.EXTEND;
865 lx_winners_rec.property3.EXTEND;
866 lx_winners_rec.property4.EXTEND;
867 lx_winners_rec.property5.EXTEND;
868 lx_winners_rec.property6.EXTEND;
869 lx_winners_rec.property7.EXTEND;
870 lx_winners_rec.property8.EXTEND;
871 lx_winners_rec.property9.EXTEND;
872 lx_winners_rec.property10.EXTEND;
873 lx_winners_rec.property11.EXTEND;
874 lx_winners_rec.property12.EXTEND;
875 lx_winners_rec.property13.EXTEND;
876 lx_winners_rec.property14.EXTEND;
877 lx_winners_rec.property15.EXTEND;
878 -- End of addition
879
880 /*
881 x_winners_tbl(i).use_type := lx_winners_rec.use_type;
882 x_winners_tbl(i).source_id := lx_winners_rec.source_id;
883 x_winners_tbl(i).transaction_id := lx_winners_rec.transaction_id;
884 x_winners_tbl(i).terr_name := lx_winners_rec.terr_name(i);
885 x_winners_tbl(i).top_level_terr_id := lx_winners_rec.top_level_terr_id(i);
886 */
887 x_winners_tbl(i).trans_object_id := lx_winners_rec.trans_object_id(i);
888 x_winners_tbl(i).trans_detail_object_id := lx_winners_rec.trans_detail_object_id(i);
889 x_winners_tbl(i).terr_rsc_id := lx_winners_rec.terr_rsc_id(i);
890 x_winners_tbl(i).absolute_rank := lx_winners_rec.absolute_rank(i);
891 x_winners_tbl(i).resource_id := lx_winners_rec.resource_id(i);
892 x_winners_tbl(i).resource_type := lx_winners_rec.resource_type(i);
893 x_winners_tbl(i).group_id := lx_winners_rec.group_id(i);
894 x_winners_tbl(i).role := lx_winners_rec.role(i);
895 x_winners_tbl(i).full_access_flag := lx_winners_rec.full_access_flag(i);
896 x_winners_tbl(i).primary_contact_flag := lx_winners_rec.primary_contact_flag(i);
897 x_winners_tbl(i).resource_name := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_name(i));
898 x_winners_tbl(i).resource_job_title := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_job_title(i));
899 x_winners_tbl(i).resource_phone := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_phone(i));
900 x_winners_tbl(i).resource_email := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_email(i));
901 x_winners_tbl(i).resource_mgr_name := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_mgr_name(i));
902 x_winners_tbl(i).resource_mgr_phone := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_mgr_phone(i));
903 x_winners_tbl(i).resource_mgr_email := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.resource_mgr_email(i));
904 x_winners_tbl(i).terr_id := lx_winners_rec.terr_id(i);
905 x_winners_tbl(i).property1 := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.property1(i));
906 x_winners_tbl(i).property2 := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.property2(i));
907 x_winners_tbl(i).property3 := WF_NOTIFICATION.SubstituteSpecialChars(lx_winners_rec.property3(i));
908 x_winners_tbl(i).property4 := lx_winners_rec.property4(i);
909 x_winners_tbl(i).property5 := lx_winners_rec.property5(i);
910 x_winners_tbl(i).property6 := lx_winners_rec.property6(i);
911 x_winners_tbl(i).property7 := lx_winners_rec.property7(i);
912 x_winners_tbl(i).property8 := lx_winners_rec.property8(i);
913 x_winners_tbl(i).property9 := lx_winners_rec.property9(i);
914 x_winners_tbl(i).property10 := lx_winners_rec.property10(i);
915 x_winners_tbl(i).property11 := lx_winners_rec.property11(i);
916 x_winners_tbl(i).property12 := lx_winners_rec.property12(i);
917 x_winners_tbl(i).property13 := lx_winners_rec.property13(i);
918 x_winners_tbl(i).property14 := lx_winners_rec.property14(i);
919 x_winners_tbl(i).property15 := lx_winners_rec.property15(i);
920
921 end loop;
922 end if;
923
924
925 --x_winners_tbl := lx_rec;
926 --l_NumberOfWinners := JTF_TERRITORY_GLOBAL_PUB.get_RecordCount;
927 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: API returned - number of winners: ' || x_winners_tbl.count );
928 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: Convert from bulk ');
929 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: Conversion complete ');
930 -- Debug Message
931 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
932 THEN
933 FND_MESSAGE.Set_Name('JTF', 'JTF_TERR_MEMBERS_ACCT_END');
934 FND_MSG_PUB.Add;
935 END IF;
936
937 -- Standard call to get message count and if count is 1, get message info.
938 FND_MSG_PUB.Count_And_Get
939 ( p_count => x_msg_count,
940 p_data => x_msg_data
941 );
942 --dbms_output.put_line('JTF_TERR_LOOKUP_PUB: End ');
943 EXCEPTION
944 WHEN OTHERS THEN
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
946 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
947 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
948 END IF;
949 FND_MSG_PUB.Count_And_Get
950 ( p_count => x_msg_count,
951 p_data => x_msg_data
952 );
953
954 End Get_Winners;
955
956 END JTF_TERR_LOOKUP_PUB;