[Home] [Help]
PACKAGE BODY: APPS.JTF_TERR_ASSIGN_PUB
Source
1 Package Body JTF_TERR_ASSIGN_PUB AS
2 /* $Header: jtfptrwb.pls 120.7 2006/03/30 17:15:37 achanda ship $ */
3 ---------------------------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTF_TERR_ASSIGN_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- Joint task force applications territory manager public 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 -- Valid values for USE_TYPE:
20 -- TERRITORY - return only the distinct winning territories
21 -- RESOURCE - return resources of all winning territories
22 -- LOOKUP - return resource information as needed in territory Lookup
23 --
24 -- Program Flow:
25 -- Check usage to call proper API.
26 -- set output to lx_win_rec
27 -- Process lx_win_rec for output depending on USE_TYPE
28 --
29 -- Terminology: ---------------------------------------------------------
30 --
31 -- jtf_account_bulk_rec_type, jtf_lead_bulk_rec_type - known as
32 -- TRANSACTION-BASED input types, since they are different for each transaction, bulk or not.
33 --
34 -- jtf_terr_assign.bulk_gen_trans_rec_type - known as
35 -- GENERIC-TRANSACTION bulk input type, since it handles all transaction assignment requests.
36 --
37 -- Variable Names
38 -- bulk_winners_rec_type may have several uses depending on use_type
39 -- The names of these variables to be used will be as follows:
40 -- use_type variable Name
41 -- -----------------------------------
42 -- RESOURCE <not needed - simply copy dyn ouput to API output>
43 -- TERRITORY lx_terr_win_rec
44 -- LOOKUP lx_lookup_bulk_winners_rec
45 --
46 --
47 -- HISTORY
48 -- 06/21/2001 EIHSU CREATED
49 -- 07/12/01 jdochert creating additional parameters
50 -- 07/17/2001 EIHSU Add logic control for formatting different
51 -- outputs based on USE_TYPE
52 -- 07/19/2001 EIHSU Sales/Account:
53 -- Convert generic bulk type to transaction-based bulk type
54 -- to call sales/account dyn; and converts
55 -- output to gen output type (this until dyn packages are changed)
56 -- 07/23/2001 EIHSU TERRITORY use type code completed for distinct terr_id output
57 -- 07/23/2001 EIHSU Usage logic and use_type output logic separated
58 -- (see "Program Flow" above.)
59 -- 07/24/2001 arpatel Added call to JTF_TERR_1003_CLAIM_DYN for Trade Management/Claim
60 -- 10/01/2001 arpatel Now convert bulk_gen_trans_rec_type to specific transaction bulk type
61 -- for Sales and Telesales/Account and for all use_types
62 -- 01/22/2002 eihsu Fix bug 2185024
63 -- 02/05/2002 eihsu Fix bugs 2212655 2185024
64 -- 02/14/02 sp Added call to JTF_TERR_1500_KREN_DYN for Contracts for bug 2220941
65 -- 10/11/2002 jradhakr Added call to JTF_TERR_1600_DELQCY_DYN for including Collections qualifiers
66 -- bug 1677560
67 -- 04/28/2004 achanda bug 3562041 : remove the outer join to hz_parties
68 -- 05/26/2005 achanda modified to the new 12.0 architecture
69 -- End of Comments
70
71 -- ***************************************************
72 -- GLOBAL VARIABLES and RECORD TYPE DEFINITIONS
73 -- ***************************************************
74
75 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_TERR_ASSIGN_PUB';
76 G_FILE_NAME CONSTANT VARCHAR2(12):='jtfptrwb.pls';
77
78 G_NEW_LINE VARCHAR2(02) := fnd_global.local_chr(10);
79 G_APPL_ID NUMBER := FND_GLOBAL.Prog_Appl_Id;
80 G_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
81 G_PROGRAM_ID NUMBER := FND_GLOBAL.Conc_Program_Id;
82 G_USER_ID NUMBER := FND_GLOBAL.User_Id;
83 G_REQUEST_ID NUMBER := FND_GLOBAL.Conc_Request_Id;
84 G_APP_SHORT_NAME VARCHAR2(15) := FND_GLOBAL.Application_Short_Name;
85
86 -- ***************************************************
87 -- API Body Definitions
88 -- ***************************************************
89 PROCEDURE get_winners
90 ( p_api_version_number IN NUMBER,
91 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
92 p_use_type IN VARCHAR2 := 'RESOURCE',
93 p_source_id IN NUMBER,
94 p_trans_id IN NUMBER,
95 p_trans_rec IN bulk_trans_rec_type,
96 p_resource_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
97 p_role IN VARCHAR2 := FND_API.G_MISS_CHAR,
98 p_top_level_terr_id IN NUMBER := FND_API.G_MISS_NUM,
99 p_num_winners IN NUMBER := FND_API.G_MISS_NUM,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2,
103 x_winners_rec OUT NOCOPY bulk_winners_rec_type
104 )
105 AS
106
107 l_api_name CONSTANT VARCHAR2(30) := 'Get_Winners';
108 l_api_version_number CONSTANT NUMBER := 1.0;
109 l_count1 NUMBER;
110 l_num_of_terr NUMBER;
111 l_Counter NUMBER;
112
113 l_program_name VARCHAR2(60);
114 l_sysdate DATE;
115
116 l_role VARCHAR2(60);
117 l_resource_type VARCHAR2(60);
118
119 lx_winners_rec JTY_ASSIGN_REALTIME_PUB.bulk_winners_rec_type;
120
121 BEGIN
122
123 -- debug message
124 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
125 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
126 'jtf.plsql.jtf_terr_assign_pub.get_winners.begin',
127 'Start of the procedure jtf_terr_assign_pub.get_winners');
128 END IF;
129
130 -- Standard call to check for call compatibility.
131 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
132 p_api_version_number,
133 l_api_name,
134 G_PKG_NAME) THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138 -- Initialize message list if p_init_msg_list is set to TRUE.
139 IF FND_API.to_Boolean( p_init_msg_list ) THEN
140 FND_MSG_PUB.initialize;
141 END IF;
142
143 ------------------
144 -- API body
145 ------------------
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147 l_sysdate := SYSDATE;
148 l_count1 := 0;
149 l_num_of_terr := 0;
150 l_counter := 0;
151 IF (p_role = FND_API.G_MISS_CHAR) THEN
152 l_role := null;
153 ELSE
154 l_role := p_role;
155 END IF;
156 IF (p_resource_type = FND_API.G_MISS_CHAR) THEN
157 l_resource_type := null;
158 ELSE
159 l_resource_type := p_resource_type;
160 END IF;
161
162 /* Check for territories for this Usage/Transaction Type */
163 BEGIN
164
165 /* ARPATEL: Sales records are no longer striped by tx type */
166 if p_source_id = -1001 then
167 SELECT COUNT(*)
168 INTO l_num_of_terr
169 FROM jtf_terr_denorm_rules_all jtdr
170 WHERE jtdr.source_id = p_source_id
171 AND rownum < 2;
172 else
173 SELECT COUNT(*)
174 INTO l_num_of_terr
175 FROM jtf_terr_denorm_rules_all jtdr
176 WHERE jtdr.source_id = p_source_id
177 AND rownum < 2;
178 end if;
179
180 EXCEPTION
181 WHEN NO_DATA_FOUND Then
182 l_num_of_terr := 0;
183 END;
184
185 /* Only call assignment APIs if valid territories exist */
186 IF ( l_num_of_terr > 0 ) THEN
187
188 /* Trade Management/Offer */
189 IF ( p_source_id = -1003 AND p_trans_id = -1007) THEN
190 l_program_name := 'TRADE MANAGEMENT/OFFER PROGRAM';
191 DELETE jty_terr_1003_offer_trans_gt;
192
193 FORALL i IN p_trans_rec.trans_object_id.FIRST .. p_trans_rec.trans_object_id.LAST
194 INSERT INTO jty_terr_1003_offer_trans_gt (
195 use_type
196 ,source_id
197 ,transaction_id
198 ,trans_object_id
199 ,trans_detail_object_id
200 ,SQUAL_CHAR01
201 ,SQUAL_CHAR02
202 ,SQUAL_CHAR03
203 ,SQUAL_CHAR04
204 ,SQUAL_CHAR05
205 ,SQUAL_CHAR06
206 ,SQUAL_CHAR07
207 ,SQUAL_CHAR08
208 ,SQUAL_CHAR09
209 ,SQUAL_CHAR10
210 ,SQUAL_CHAR11
211 ,SQUAL_CHAR12
212 ,SQUAL_CHAR13
213 ,SQUAL_CHAR14
214 ,SQUAL_CHAR15
215 ,SQUAL_CHAR16
216 ,SQUAL_CHAR17
217 ,SQUAL_CHAR18
218 ,SQUAL_CHAR19
219 ,SQUAL_CHAR20
220 ,SQUAL_CHAR21
221 ,SQUAL_CHAR22
222 ,SQUAL_CHAR23
223 ,SQUAL_CHAR24
224 ,SQUAL_CHAR25
225 ,SQUAL_CHAR26
226 ,SQUAL_CHAR27
227 ,SQUAL_CHAR28
228 ,SQUAL_CHAR29
229 ,SQUAL_CHAR30
230 ,SQUAL_CHAR31
231 ,SQUAL_CHAR32
232 ,SQUAL_CHAR33
233 ,SQUAL_CHAR34
234 ,SQUAL_CHAR35
235 ,SQUAL_CHAR36
236 ,SQUAL_CHAR37
237 ,SQUAL_CHAR38
238 ,SQUAL_CHAR39
239 ,SQUAL_CHAR40
240 ,SQUAL_CHAR41
241 ,SQUAL_CHAR42
242 ,SQUAL_CHAR43
243 ,SQUAL_CHAR44
244 ,SQUAL_CHAR45
245 ,SQUAL_CHAR46
246 ,SQUAL_CHAR47
247 ,SQUAL_CHAR48
248 ,SQUAL_CHAR49
249 ,SQUAL_CHAR50
250 ,SQUAL_NUM01
251 ,SQUAL_NUM02
252 ,SQUAL_NUM03
253 ,SQUAL_NUM04
254 ,SQUAL_NUM05
255 ,SQUAL_NUM06
256 ,SQUAL_NUM07
257 ,SQUAL_NUM08
258 ,SQUAL_NUM09
259 ,SQUAL_NUM10
260 ,SQUAL_NUM11
261 ,SQUAL_NUM12
262 ,SQUAL_NUM13
263 ,SQUAL_NUM14
264 ,SQUAL_NUM15
265 ,SQUAL_NUM16
266 ,SQUAL_NUM17
267 ,SQUAL_NUM18
268 ,SQUAL_NUM19
269 ,SQUAL_NUM20
270 ,SQUAL_NUM21
271 ,SQUAL_NUM22
272 ,SQUAL_NUM23
273 ,SQUAL_NUM24
274 ,SQUAL_NUM25
275 ,SQUAL_NUM26
276 ,SQUAL_NUM27
277 ,SQUAL_NUM28
278 ,SQUAL_NUM29
279 ,SQUAL_NUM30
280 ,SQUAL_NUM31
281 ,SQUAL_NUM32
282 ,SQUAL_NUM33
283 ,SQUAL_NUM34
284 ,SQUAL_NUM35
285 ,SQUAL_NUM36
286 ,SQUAL_NUM37
287 ,SQUAL_NUM38
288 ,SQUAL_NUM39
289 ,SQUAL_NUM40
290 ,SQUAL_NUM41
291 ,SQUAL_NUM42
292 ,SQUAL_NUM43
293 ,SQUAL_NUM44
294 ,SQUAL_NUM45
295 ,SQUAL_NUM46
296 ,SQUAL_NUM47
297 ,SQUAL_NUM48
298 ,SQUAL_NUM49
299 ,SQUAL_NUM50
300 ,txn_date
301 )
302 VALUES (
303 p_use_type
304 ,p_source_id
305 ,p_trans_id
306 ,p_trans_rec.trans_object_id(i)
307 ,p_trans_rec.trans_detail_object_id(i)
308 ,p_trans_rec.SQUAL_CHAR01(i)
309 ,p_trans_rec.SQUAL_CHAR02(i)
310 ,p_trans_rec.SQUAL_CHAR03(i)
311 ,p_trans_rec.SQUAL_CHAR04(i)
312 ,p_trans_rec.SQUAL_CHAR05(i)
313 ,p_trans_rec.SQUAL_CHAR06(i)
314 ,p_trans_rec.SQUAL_CHAR07(i)
315 ,p_trans_rec.SQUAL_CHAR08(i)
316 ,p_trans_rec.SQUAL_CHAR09(i)
317 ,p_trans_rec.SQUAL_CHAR10(i)
318 ,p_trans_rec.SQUAL_CHAR11(i)
319 ,p_trans_rec.SQUAL_CHAR12(i)
320 ,p_trans_rec.SQUAL_CHAR13(i)
321 ,p_trans_rec.SQUAL_CHAR14(i)
322 ,p_trans_rec.SQUAL_CHAR15(i)
323 ,p_trans_rec.SQUAL_CHAR16(i)
324 ,p_trans_rec.SQUAL_CHAR17(i)
325 ,p_trans_rec.SQUAL_CHAR18(i)
326 ,p_trans_rec.SQUAL_CHAR19(i)
327 ,p_trans_rec.SQUAL_CHAR20(i)
328 ,p_trans_rec.SQUAL_CHAR21(i)
329 ,p_trans_rec.SQUAL_CHAR22(i)
330 ,p_trans_rec.SQUAL_CHAR23(i)
331 ,p_trans_rec.SQUAL_CHAR24(i)
332 ,p_trans_rec.SQUAL_CHAR25(i)
333 ,p_trans_rec.SQUAL_CHAR26(i)
334 ,p_trans_rec.SQUAL_CHAR27(i)
335 ,p_trans_rec.SQUAL_CHAR28(i)
336 ,p_trans_rec.SQUAL_CHAR29(i)
337 ,p_trans_rec.SQUAL_CHAR30(i)
338 ,p_trans_rec.SQUAL_CHAR31(i)
339 ,p_trans_rec.SQUAL_CHAR32(i)
340 ,p_trans_rec.SQUAL_CHAR33(i)
341 ,p_trans_rec.SQUAL_CHAR34(i)
342 ,p_trans_rec.SQUAL_CHAR35(i)
343 ,p_trans_rec.SQUAL_CHAR36(i)
344 ,p_trans_rec.SQUAL_CHAR37(i)
345 ,p_trans_rec.SQUAL_CHAR38(i)
346 ,p_trans_rec.SQUAL_CHAR39(i)
347 ,p_trans_rec.SQUAL_CHAR40(i)
348 ,p_trans_rec.SQUAL_CHAR41(i)
349 ,p_trans_rec.SQUAL_CHAR42(i)
350 ,p_trans_rec.SQUAL_CHAR43(i)
351 ,p_trans_rec.SQUAL_CHAR44(i)
352 ,p_trans_rec.SQUAL_CHAR45(i)
353 ,p_trans_rec.SQUAL_CHAR46(i)
354 ,p_trans_rec.SQUAL_CHAR47(i)
355 ,p_trans_rec.SQUAL_CHAR48(i)
356 ,p_trans_rec.SQUAL_CHAR49(i)
357 ,p_trans_rec.SQUAL_CHAR50(i)
358 ,p_trans_rec.SQUAL_NUM01(i)
359 ,p_trans_rec.SQUAL_NUM02(i)
360 ,p_trans_rec.SQUAL_NUM03(i)
361 ,p_trans_rec.SQUAL_NUM04(i)
362 ,p_trans_rec.SQUAL_NUM05(i)
363 ,p_trans_rec.SQUAL_NUM06(i)
364 ,p_trans_rec.SQUAL_NUM07(i)
365 ,p_trans_rec.SQUAL_NUM08(i)
366 ,p_trans_rec.SQUAL_NUM09(i)
367 ,p_trans_rec.SQUAL_NUM10(i)
368 ,p_trans_rec.SQUAL_NUM11(i)
369 ,p_trans_rec.SQUAL_NUM12(i)
370 ,p_trans_rec.SQUAL_NUM13(i)
371 ,p_trans_rec.SQUAL_NUM14(i)
372 ,p_trans_rec.SQUAL_NUM15(i)
373 ,p_trans_rec.SQUAL_NUM16(i)
374 ,p_trans_rec.SQUAL_NUM17(i)
375 ,p_trans_rec.SQUAL_NUM18(i)
376 ,p_trans_rec.SQUAL_NUM19(i)
377 ,p_trans_rec.SQUAL_NUM20(i)
378 ,p_trans_rec.SQUAL_NUM21(i)
379 ,p_trans_rec.SQUAL_NUM22(i)
380 ,p_trans_rec.SQUAL_NUM23(i)
381 ,p_trans_rec.SQUAL_NUM24(i)
382 ,p_trans_rec.SQUAL_NUM25(i)
383 ,p_trans_rec.SQUAL_NUM26(i)
384 ,p_trans_rec.SQUAL_NUM27(i)
385 ,p_trans_rec.SQUAL_NUM28(i)
386 ,p_trans_rec.SQUAL_NUM29(i)
387 ,p_trans_rec.SQUAL_NUM30(i)
388 ,p_trans_rec.SQUAL_NUM31(i)
389 ,p_trans_rec.SQUAL_NUM32(i)
390 ,p_trans_rec.SQUAL_NUM33(i)
391 ,p_trans_rec.SQUAL_NUM34(i)
392 ,p_trans_rec.SQUAL_NUM35(i)
393 ,p_trans_rec.SQUAL_NUM36(i)
394 ,p_trans_rec.SQUAL_NUM37(i)
395 ,p_trans_rec.SQUAL_NUM38(i)
396 ,p_trans_rec.SQUAL_NUM39(i)
397 ,p_trans_rec.SQUAL_NUM40(i)
398 ,p_trans_rec.SQUAL_NUM41(i)
399 ,p_trans_rec.SQUAL_NUM42(i)
400 ,p_trans_rec.SQUAL_NUM43(i)
404 ,p_trans_rec.SQUAL_NUM47(i)
401 ,p_trans_rec.SQUAL_NUM44(i)
402 ,p_trans_rec.SQUAL_NUM45(i)
403 ,p_trans_rec.SQUAL_NUM46(i)
405 ,p_trans_rec.SQUAL_NUM48(i)
406 ,p_trans_rec.SQUAL_NUM49(i)
407 ,p_trans_rec.SQUAL_NUM50(i)
408 ,l_sysdate
409 );
410
411 /*
412 JTF_TERR_1003_OFFER_DYN.search_Terr_Rules(
413 p_Rec => p_trans_rec,
414 x_rec => lx_win_rec,
415 p_role => p_role,
416 p_resource_type => p_resource_type
417 );
418 */
419
420 /* Trade Management/Claim */
421 ELSIF ( p_source_id = -1003 AND p_trans_id = -1302) THEN
422 l_program_name := 'TRADE MANAGEMENT/CLAIM PROGRAM';
423 DELETE jty_terr_1003_claim_trans_gt;
424
425 FORALL i IN p_trans_rec.trans_object_id.FIRST .. p_trans_rec.trans_object_id.LAST
426 INSERT INTO jty_terr_1003_claim_trans_gt (
427 use_type
428 ,source_id
429 ,transaction_id
430 ,trans_object_id
431 ,trans_detail_object_id
432 ,SQUAL_CHAR01
433 ,SQUAL_CHAR02
434 ,SQUAL_CHAR03
435 ,SQUAL_CHAR04
436 ,SQUAL_CHAR05
437 ,SQUAL_CHAR06
438 ,SQUAL_CHAR07
439 ,SQUAL_CHAR08
440 ,SQUAL_CHAR09
441 ,SQUAL_CHAR10
442 ,SQUAL_CHAR11
443 ,SQUAL_CHAR12
444 ,SQUAL_CHAR13
445 ,SQUAL_CHAR14
446 ,SQUAL_CHAR15
447 ,SQUAL_CHAR16
448 ,SQUAL_CHAR17
449 ,SQUAL_CHAR18
450 ,SQUAL_CHAR19
451 ,SQUAL_CHAR20
452 ,SQUAL_CHAR21
453 ,SQUAL_CHAR22
454 ,SQUAL_CHAR23
455 ,SQUAL_CHAR24
456 ,SQUAL_CHAR25
457 ,SQUAL_CHAR26
458 ,SQUAL_CHAR27
459 ,SQUAL_CHAR28
460 ,SQUAL_CHAR29
461 ,SQUAL_CHAR30
462 ,SQUAL_CHAR31
463 ,SQUAL_CHAR32
464 ,SQUAL_CHAR33
465 ,SQUAL_CHAR34
466 ,SQUAL_CHAR35
467 ,SQUAL_CHAR36
468 ,SQUAL_CHAR37
469 ,SQUAL_CHAR38
470 ,SQUAL_CHAR39
471 ,SQUAL_CHAR40
472 ,SQUAL_CHAR41
473 ,SQUAL_CHAR42
474 ,SQUAL_CHAR43
475 ,SQUAL_CHAR44
476 ,SQUAL_CHAR45
477 ,SQUAL_CHAR46
478 ,SQUAL_CHAR47
479 ,SQUAL_CHAR48
480 ,SQUAL_CHAR49
481 ,SQUAL_CHAR50
482 ,SQUAL_NUM01
483 ,SQUAL_NUM02
484 ,SQUAL_NUM03
485 ,SQUAL_NUM04
486 ,SQUAL_NUM05
487 ,SQUAL_NUM06
488 ,SQUAL_NUM07
489 ,SQUAL_NUM08
490 ,SQUAL_NUM09
491 ,SQUAL_NUM10
492 ,SQUAL_NUM11
493 ,SQUAL_NUM12
494 ,SQUAL_NUM13
495 ,SQUAL_NUM14
496 ,SQUAL_NUM15
497 ,SQUAL_NUM16
498 ,SQUAL_NUM17
499 ,SQUAL_NUM18
500 ,SQUAL_NUM19
501 ,SQUAL_NUM20
502 ,SQUAL_NUM21
503 ,SQUAL_NUM22
504 ,SQUAL_NUM23
505 ,SQUAL_NUM24
506 ,SQUAL_NUM25
507 ,SQUAL_NUM26
508 ,SQUAL_NUM27
509 ,SQUAL_NUM28
510 ,SQUAL_NUM29
511 ,SQUAL_NUM30
512 ,SQUAL_NUM31
513 ,SQUAL_NUM32
514 ,SQUAL_NUM33
515 ,SQUAL_NUM34
516 ,SQUAL_NUM35
517 ,SQUAL_NUM36
518 ,SQUAL_NUM37
519 ,SQUAL_NUM38
520 ,SQUAL_NUM39
521 ,SQUAL_NUM40
522 ,SQUAL_NUM41
523 ,SQUAL_NUM42
524 ,SQUAL_NUM43
525 ,SQUAL_NUM44
526 ,SQUAL_NUM45
527 ,SQUAL_NUM46
528 ,SQUAL_NUM47
529 ,SQUAL_NUM48
530 ,SQUAL_NUM49
531 ,SQUAL_NUM50
532 ,txn_date
533 )
534 VALUES (
535 p_use_type
536 ,p_source_id
537 ,p_trans_id
538 ,p_trans_rec.trans_object_id(i)
539 ,p_trans_rec.trans_detail_object_id(i)
540 ,p_trans_rec.SQUAL_CHAR01(i)
541 ,p_trans_rec.SQUAL_CHAR02(i)
542 ,p_trans_rec.SQUAL_CHAR03(i)
543 ,p_trans_rec.SQUAL_CHAR04(i)
544 ,p_trans_rec.SQUAL_CHAR05(i)
545 ,p_trans_rec.SQUAL_CHAR06(i)
546 ,p_trans_rec.SQUAL_CHAR07(i)
547 ,p_trans_rec.SQUAL_CHAR08(i)
548 ,p_trans_rec.SQUAL_CHAR09(i)
549 ,p_trans_rec.SQUAL_CHAR10(i)
550 ,p_trans_rec.SQUAL_CHAR11(i)
551 ,p_trans_rec.SQUAL_CHAR12(i)
552 ,p_trans_rec.SQUAL_CHAR13(i)
553 ,p_trans_rec.SQUAL_CHAR14(i)
554 ,p_trans_rec.SQUAL_CHAR15(i)
555 ,p_trans_rec.SQUAL_CHAR16(i)
556 ,p_trans_rec.SQUAL_CHAR17(i)
557 ,p_trans_rec.SQUAL_CHAR18(i)
558 ,p_trans_rec.SQUAL_CHAR19(i)
559 ,p_trans_rec.SQUAL_CHAR20(i)
563 ,p_trans_rec.SQUAL_CHAR24(i)
560 ,p_trans_rec.SQUAL_CHAR21(i)
561 ,p_trans_rec.SQUAL_CHAR22(i)
562 ,p_trans_rec.SQUAL_CHAR23(i)
564 ,p_trans_rec.SQUAL_CHAR25(i)
565 ,p_trans_rec.SQUAL_CHAR26(i)
566 ,p_trans_rec.SQUAL_CHAR27(i)
567 ,p_trans_rec.SQUAL_CHAR28(i)
568 ,p_trans_rec.SQUAL_CHAR29(i)
569 ,p_trans_rec.SQUAL_CHAR30(i)
570 ,p_trans_rec.SQUAL_CHAR31(i)
571 ,p_trans_rec.SQUAL_CHAR32(i)
572 ,p_trans_rec.SQUAL_CHAR33(i)
573 ,p_trans_rec.SQUAL_CHAR34(i)
574 ,p_trans_rec.SQUAL_CHAR35(i)
575 ,p_trans_rec.SQUAL_CHAR36(i)
576 ,p_trans_rec.SQUAL_CHAR37(i)
577 ,p_trans_rec.SQUAL_CHAR38(i)
578 ,p_trans_rec.SQUAL_CHAR39(i)
579 ,p_trans_rec.SQUAL_CHAR40(i)
580 ,p_trans_rec.SQUAL_CHAR41(i)
581 ,p_trans_rec.SQUAL_CHAR42(i)
582 ,p_trans_rec.SQUAL_CHAR43(i)
583 ,p_trans_rec.SQUAL_CHAR44(i)
584 ,p_trans_rec.SQUAL_CHAR45(i)
585 ,p_trans_rec.SQUAL_CHAR46(i)
586 ,p_trans_rec.SQUAL_CHAR47(i)
587 ,p_trans_rec.SQUAL_CHAR48(i)
588 ,p_trans_rec.SQUAL_CHAR49(i)
589 ,p_trans_rec.SQUAL_CHAR50(i)
590 ,p_trans_rec.SQUAL_NUM01(i)
591 ,p_trans_rec.SQUAL_NUM02(i)
592 ,p_trans_rec.SQUAL_NUM03(i)
593 ,p_trans_rec.SQUAL_NUM04(i)
594 ,p_trans_rec.SQUAL_NUM05(i)
595 ,p_trans_rec.SQUAL_NUM06(i)
596 ,p_trans_rec.SQUAL_NUM07(i)
597 ,p_trans_rec.SQUAL_NUM08(i)
598 ,p_trans_rec.SQUAL_NUM09(i)
599 ,p_trans_rec.SQUAL_NUM10(i)
600 ,p_trans_rec.SQUAL_NUM11(i)
601 ,p_trans_rec.SQUAL_NUM12(i)
602 ,p_trans_rec.SQUAL_NUM13(i)
603 ,p_trans_rec.SQUAL_NUM14(i)
604 ,p_trans_rec.SQUAL_NUM15(i)
605 ,p_trans_rec.SQUAL_NUM16(i)
606 ,p_trans_rec.SQUAL_NUM17(i)
607 ,p_trans_rec.SQUAL_NUM18(i)
608 ,p_trans_rec.SQUAL_NUM19(i)
609 ,p_trans_rec.SQUAL_NUM20(i)
610 ,p_trans_rec.SQUAL_NUM21(i)
611 ,p_trans_rec.SQUAL_NUM22(i)
612 ,p_trans_rec.SQUAL_NUM23(i)
613 ,p_trans_rec.SQUAL_NUM24(i)
614 ,p_trans_rec.SQUAL_NUM25(i)
615 ,p_trans_rec.SQUAL_NUM26(i)
616 ,p_trans_rec.SQUAL_NUM27(i)
617 ,p_trans_rec.SQUAL_NUM28(i)
618 ,p_trans_rec.SQUAL_NUM29(i)
619 ,p_trans_rec.SQUAL_NUM30(i)
620 ,p_trans_rec.SQUAL_NUM31(i)
621 ,p_trans_rec.SQUAL_NUM32(i)
622 ,p_trans_rec.SQUAL_NUM33(i)
623 ,p_trans_rec.SQUAL_NUM34(i)
624 ,p_trans_rec.SQUAL_NUM35(i)
625 ,p_trans_rec.SQUAL_NUM36(i)
626 ,p_trans_rec.SQUAL_NUM37(i)
627 ,p_trans_rec.SQUAL_NUM38(i)
628 ,p_trans_rec.SQUAL_NUM39(i)
629 ,p_trans_rec.SQUAL_NUM40(i)
630 ,p_trans_rec.SQUAL_NUM41(i)
631 ,p_trans_rec.SQUAL_NUM42(i)
632 ,p_trans_rec.SQUAL_NUM43(i)
633 ,p_trans_rec.SQUAL_NUM44(i)
634 ,p_trans_rec.SQUAL_NUM45(i)
635 ,p_trans_rec.SQUAL_NUM46(i)
636 ,p_trans_rec.SQUAL_NUM47(i)
637 ,p_trans_rec.SQUAL_NUM48(i)
638 ,p_trans_rec.SQUAL_NUM49(i)
639 ,p_trans_rec.SQUAL_NUM50(i)
640 ,l_sysdate
641 );
642
643 /*
644 JTF_TERR_1003_CLAIM_DYN.search_terr_rules(
645 p_rec => p_trans_rec
646 , x_rec => lx_win_rec
647 , p_role => p_role
648 , p_resource_type => p_resource_type );
649 */
650
651 /* Contracts/Contract Renewal */
652 ELSIF ( p_source_id = -1500 AND p_trans_id = -1501) THEN
653 l_program_name := 'CONTRACTS/CONTRACT RENEWALS PROGRAM';
654 DELETE jty_terr_1500_cntrct_trans_gt;
655
656 FORALL i IN p_trans_rec.trans_object_id.FIRST .. p_trans_rec.trans_object_id.LAST
657 INSERT INTO jty_terr_1500_cntrct_trans_gt (
658 use_type
659 ,source_id
660 ,transaction_id
661 ,trans_object_id
662 ,trans_detail_object_id
663 ,SQUAL_CHAR01
664 ,SQUAL_CHAR04
665 ,SQUAL_CHAR07
666 ,SQUAL_NUM01
667 ,txn_date
668 )
669 VALUES (
670 p_use_type
671 ,p_source_id
672 ,p_trans_id
673 ,p_trans_rec.trans_object_id(i)
674 ,p_trans_rec.trans_detail_object_id(i)
675 ,p_trans_rec.SQUAL_CHAR01(i)
676 ,p_trans_rec.SQUAL_CHAR04(i)
677 ,p_trans_rec.SQUAL_CHAR07(i)
678 ,p_trans_rec.SQUAL_NUM01(i)
679 ,l_sysdate
680 );
681
682 /*
683 JTF_TERR_1500_KREN_DYN.search_terr_rules(
684 p_rec => p_trans_rec
685 , x_rec => lx_win_rec
686 , p_role => p_role
687 , p_resource_type => p_resource_type );
688 */
689
690 /* Partner Management/Partner */
691 ELSIF (p_source_id = -1700 AND p_trans_id = -1701) THEN
695 FORALL i IN p_trans_rec.trans_object_id.FIRST .. p_trans_rec.trans_object_id.LAST
692 l_program_name := 'TRADE MANAGEMENT/PARTNER PROGRAM';
693 DELETE jty_terr_1700_partner_trans_gt;
694
696 INSERT INTO jty_terr_1700_partner_trans_gt (
697 use_type
698 ,source_id
699 ,transaction_id
700 ,trans_object_id
701 ,trans_detail_object_id
702 ,SQUAL_CHAR01
703 ,SQUAL_CHAR02
704 ,SQUAL_CHAR03
705 ,SQUAL_CHAR04
706 ,SQUAL_CHAR05
707 ,SQUAL_CHAR06
708 ,SQUAL_CHAR07
709 ,SQUAL_CHAR08
710 ,SQUAL_CHAR09
711 ,SQUAL_CHAR10
712 ,SQUAL_CHAR11
713 ,SQUAL_CHAR12
714 ,SQUAL_CHAR13
715 ,SQUAL_CHAR14
716 ,SQUAL_CHAR15
717 ,SQUAL_CHAR16
718 ,SQUAL_CHAR17
719 ,SQUAL_CHAR18
720 ,SQUAL_CHAR19
721 ,SQUAL_CHAR20
722 ,SQUAL_NUM01
723 ,SQUAL_NUM02
724 ,SQUAL_NUM03
725 ,SQUAL_NUM04
726 ,SQUAL_NUM05
727 ,SQUAL_NUM06
728 ,SQUAL_NUM07
729 ,SQUAL_NUM08
730 ,SQUAL_NUM09
731 ,SQUAL_NUM10
732 ,SQUAL_NUM11
733 ,SQUAL_NUM12
734 ,SQUAL_NUM13
735 ,SQUAL_NUM14
736 ,SQUAL_NUM15
737 ,SQUAL_NUM16
738 ,SQUAL_NUM17
739 ,SQUAL_NUM18
740 ,SQUAL_NUM19
741 ,SQUAL_NUM20
742 ,SQUAL_CURC01
743 ,txn_date
744 )
745 VALUES (
746 p_use_type
747 ,-1700
748 ,-1701
749 ,p_trans_rec.trans_object_id(i)
750 ,p_trans_rec.trans_detail_object_id(i)
751 ,p_trans_rec.SQUAL_CHAR01(i)
752 ,p_trans_rec.SQUAL_CHAR02(i)
753 ,p_trans_rec.SQUAL_CHAR03(i)
754 ,p_trans_rec.SQUAL_CHAR04(i)
755 ,p_trans_rec.SQUAL_CHAR05(i)
756 ,p_trans_rec.SQUAL_CHAR06(i)
757 ,p_trans_rec.SQUAL_CHAR07(i)
758 ,p_trans_rec.SQUAL_CHAR08(i)
759 ,p_trans_rec.SQUAL_CHAR09(i)
760 ,p_trans_rec.SQUAL_CHAR10(i)
761 ,p_trans_rec.SQUAL_CHAR11(i)
762 ,p_trans_rec.SQUAL_CHAR12(i)
763 ,p_trans_rec.SQUAL_CHAR13(i)
764 ,p_trans_rec.SQUAL_CHAR14(i)
765 ,p_trans_rec.SQUAL_CHAR15(i)
766 ,p_trans_rec.SQUAL_CHAR16(i)
767 ,p_trans_rec.SQUAL_CHAR17(i)
768 ,p_trans_rec.SQUAL_CHAR18(i)
769 ,p_trans_rec.SQUAL_CHAR19(i)
770 ,p_trans_rec.SQUAL_CHAR20(i)
771 ,p_trans_rec.SQUAL_NUM01(i)
772 ,p_trans_rec.SQUAL_NUM02(i)
773 ,p_trans_rec.SQUAL_NUM03(i)
774 ,p_trans_rec.SQUAL_NUM04(i)
775 ,p_trans_rec.SQUAL_NUM05(i)
776 ,p_trans_rec.SQUAL_NUM06(i)
777 ,p_trans_rec.SQUAL_NUM07(i)
778 ,p_trans_rec.SQUAL_NUM08(i)
779 ,p_trans_rec.SQUAL_NUM09(i)
780 ,p_trans_rec.SQUAL_NUM10(i)
781 ,p_trans_rec.SQUAL_NUM11(i)
782 ,p_trans_rec.SQUAL_NUM12(i)
783 ,p_trans_rec.SQUAL_NUM13(i)
784 ,p_trans_rec.SQUAL_NUM14(i)
785 ,p_trans_rec.SQUAL_NUM15(i)
786 ,p_trans_rec.SQUAL_NUM16(i)
787 ,p_trans_rec.SQUAL_NUM17(i)
788 ,p_trans_rec.SQUAL_NUM18(i)
789 ,p_trans_rec.SQUAL_NUM19(i)
790 ,p_trans_rec.SQUAL_NUM20(i)
791 ,p_trans_rec.SQUAL_CURC01(i)
792 ,l_sysdate
793 );
794
795 /*
796 JTF_TERR_1700_PARTNER_DYN.search_terr_rules(
797 p_rec => p_trans_rec
798 , x_rec => lx_win_rec
799 , p_role => p_role
800 , p_resource_type => p_resource_type );
801 */
802
803 ELSE /* No Usage/Transaction Captured */
804
805 NULL;
806
807 END IF;
808
809 JTY_ASSIGN_REALTIME_PUB.process_match (
810 p_source_id => p_source_id
811 ,p_trans_id => p_trans_id
812 ,p_mode => 'REAL TIME:' || p_use_type
813 ,p_program_name => l_program_name
814 ,x_return_status => x_return_status
815 ,x_msg_count => x_msg_count
816 ,x_msg_data => x_msg_data);
817
818 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
819 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
820 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
821 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_match',
822 'API JTY_ASSIGN_REALTIME_PUB.process_match has failed');
823 END IF;
824 RAISE FND_API.G_EXC_ERROR;
825 END IF;
826
827 -- debug message
828 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
829 FND_LOG.string(FND_LOG.LEVEL_EVENT,
830 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_match',
831 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_match');
832 END IF;
833
834 JTY_ASSIGN_REALTIME_PUB.process_winners (
838 ,p_mode => 'REAL TIME:' || p_use_type
835 p_source_id => p_source_id
836 ,p_trans_id => p_trans_id
837 ,p_program_name => l_program_name
839 ,p_role => l_role
840 ,p_resource_type => l_resource_type
841 ,x_return_status => x_return_status
842 ,x_msg_count => x_msg_count
843 ,x_msg_data => x_msg_data
844 ,x_winners_rec => lx_winners_rec);
845
846 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
847 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
848 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
849 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_winners',
850 'JTY_ASSIGN_REALTIME_PUB.process_winners has failed');
851 END IF;
852 RAISE FND_API.G_EXC_ERROR;
853 END IF;
854
855 -- debug message
856 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
857 FND_LOG.string(FND_LOG.LEVEL_EVENT,
858 'jtf.plsql.jtf_terr_assign_pub.get_winners.process_winners',
859 'Finish calling procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
860 END IF;
861
862 l_counter := lx_winners_rec.terr_id.FIRST;
863 WHILE (l_counter <= lx_winners_rec.terr_id.LAST) LOOP
864
865 x_winners_rec.trans_object_id.EXTEND;
866 x_winners_rec.trans_detail_object_id.EXTEND;
867 x_winners_rec.terr_id.EXTEND;
868 x_winners_rec.terr_rsc_id.EXTEND;
869 x_winners_rec.terr_name.EXTEND;
870 x_winners_rec.top_level_terr_id.EXTEND;
871 x_winners_rec.absolute_rank.EXTEND;
872 x_winners_rec.resource_id.EXTEND;
873 x_winners_rec.resource_type.EXTEND;
874 x_winners_rec.group_id.EXTEND;
875 x_winners_rec.role.EXTEND;
876 x_winners_rec.full_access_flag.EXTEND;
877 x_winners_rec.primary_contact_flag.EXTEND;
878
879 x_winners_rec.trans_object_id(l_counter) := lx_winners_rec.trans_object_id(l_counter);
880 x_winners_rec.trans_detail_object_id(l_counter) := lx_winners_rec.trans_detail_object_id(l_counter);
881 x_winners_rec.terr_id(l_counter) := lx_winners_rec.terr_id(l_counter);
882 x_winners_rec.terr_rsc_id(l_counter) := lx_winners_rec.terr_rsc_id(l_counter);
883 x_winners_rec.terr_name(l_counter) := lx_winners_rec.terr_name(l_counter);
884 x_winners_rec.top_level_terr_id(l_counter) := lx_winners_rec.top_level_terr_id(l_counter);
885 x_winners_rec.absolute_rank(l_counter) := lx_winners_rec.absolute_rank(l_counter);
886 x_winners_rec.resource_id(l_counter) := lx_winners_rec.resource_id(l_counter);
887 x_winners_rec.resource_type(l_counter) := lx_winners_rec.resource_type(l_counter);
888 x_winners_rec.group_id(l_counter) := lx_winners_rec.group_id(l_counter);
889 x_winners_rec.role(l_counter) := lx_winners_rec.role(l_counter);
890 x_winners_rec.full_access_flag(l_counter) := lx_winners_rec.full_access_flag(l_counter);
891 x_winners_rec.primary_contact_flag(l_counter) := lx_winners_rec.primary_contact_flag(l_counter);
892
893 l_counter := l_counter + 1;
894
895 END LOOP;
896
897 END IF; /* IF ( l_num_of_terr > 0 ) THEN */
898
899 -- debug message
900 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
901 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
902 'jtf.plsql.jtf_terr_assign_pub.get_winners.end',
903 'End of the procedure jtf_terr_assign_pub.get_winners');
904 END IF;
905
906 EXCEPTION
907 WHEN FND_API.G_EXC_ERROR THEN
908 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
909 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
910 'jtf.plsql.jtf_terr_assign_pub.get_winners.g_exc_error',
911 substr(x_msg_data, 1, 4000));
912 END IF;
913
914 WHEN OTHERS THEN
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
916 x_msg_data := SQLCODE || ' : ' || SQLERRM;
917 x_msg_count := 1;
918 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
919 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
920 'jtf.plsql.jtf_terr_assign_pub.get_winners.other',
921 substr(x_msg_data, 1, 4000));
922 END IF;
923
924 End Get_Winners;
925
926 END JTF_TERR_ASSIGN_PUB;