[Home] [Help]
PACKAGE BODY: APPS.JTY_ASSIGN_REALTIME_PUB
Source
1 Package Body JTY_ASSIGN_REALTIME_PUB AS
2 /* $Header: jtftraeb.pls 120.12.12010000.5 2008/12/11 07:14:02 vpalle ship $ */
3 ---------------------------------------------------------------------
4 -- Start of Comments
5 -- ---------------------------------------------------
6 -- PACKAGE NAME: JTY_ASSIGN_REALTIME_PUB
7 -- ---------------------------------------------------
8 -- PURPOSE
9 -- This package is a public API for getting winning territories
10 -- or territory resources in real time.
11 --
12 -- Procedures:
13 -- (see below for specification)
14 --
15 -- NOTES
16 -- This package is publicly available for use
17 --
18 -- HISTORY
19 -- 05/04/2005 achanda CREATED
20 -- End of Comments
21
22 -- ***************************************************
23 -- GLOBAL VARIABLES and RECORD TYPE DEFINITIONS
24 -- ***************************************************
25
26 G_PKG_NAME CONSTANT VARCHAR2(30):='JTY_ASSIGN_REALTIME_PUB';
27
28 -- ***************************************************
29 -- API Body Definitions
30 -- ***************************************************
31
32 /* this procedure find out the territories that are */
33 /* matching the attributes of the transaction objects */
34 PROCEDURE process_match
35 ( p_source_id IN NUMBER,
36 p_trans_id IN NUMBER,
37 p_mode IN VARCHAR2,
38 p_program_name IN VARCHAR2,
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 ) AS
43
44 NO_TERR_ERROR EXCEPTION;
45 NO_MATCH_SQL_ERROR EXCEPTION;
46
47 l_num_terr NUMBER;
48 l_match_no_of_records NUMBER;
49 l_matching_sql VARCHAR2(32767);
50 l_sysdate DATE;
51
52 BEGIN
53
54 -- debug message
55 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
57 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.begin',
58 'Start of the procedure JTY_ASSIGN_REALTIME_PUB.process_match');
59 END IF;
60
61 x_return_status := FND_API.G_RET_STS_SUCCESS;
62 l_num_terr := 0;
63 l_sysdate := SYSDATE;
64
65 /* Depending on the mode, get the real time matching sql generated by GTP */
66 /* Raise the exception NO_MATCH_SQL_ERROR if matching SQL is not found */
67 BEGIN
68 IF (p_mode LIKE 'DATE EFFECTIVE%') THEN
69 SELECT tup.real_time_match_dea_sql
70 INTO l_matching_sql
71 FROM jty_trans_usg_pgm_details tup
72 WHERE tup.source_id = p_source_id
73 AND tup.trans_type_id = p_trans_id
74 AND tup.program_name = p_program_name;
75
76 ELSIF (p_mode LIKE 'REAL TIME%') THEN
77 SELECT tup.real_time_match_sql
78 INTO l_matching_sql
79 FROM jty_trans_usg_pgm_details tup
80 WHERE tup.source_id = p_source_id
81 AND tup.trans_type_id = p_trans_id
82 AND tup.program_name = p_program_name;
83
84 END IF;
85
86 IF (l_matching_sql IS NULL) THEN
87 RAISE NO_MATCH_SQL_ERROR;
88 END IF;
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 RAISE NO_MATCH_SQL_ERROR;
92
93 WHEN OTHERS THEN
94 RAISE;
95 END;
96
97 -- debug message
98 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
99 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
100 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.matching_sql',
101 substr(l_matching_sql, 1, 4000));
102 END IF;
103
104 /* Execute the matching SQL, which will insert matching territories */
105 /* corresponding to the transaction objects into jtf_terr_results_gt_mt */
106 DELETE jtf_terr_results_gt_mt;
107 EXECUTE IMMEDIATE l_matching_sql;
108
109 -- debug message
110 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
111 SELECT COUNT(*)
112 INTO l_match_no_of_records
113 FROM jtf_terr_results_gt_mt;
114
115 FND_LOG.string(FND_LOG.LEVEL_EVENT,
116 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.match_data',
117 'Number of records inserted into jtf_terr_results_gt_mt table : ' || l_match_no_of_records);
118 END IF;
119
120 -- debug message
121 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
122 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
123 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.end',
124 'End of the procedure JTY_ASSIGN_REALTIME_PUB.process_match');
125 END IF;
126
127 EXCEPTION
128
129 WHEN NO_MATCH_SQL_ERROR THEN
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131 x_msg_data := 'No matching SQL exist for this usage, transaction type and program name';
132 x_msg_count := 1;
133 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
134 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
135 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.mo_match_sql_error',
136 x_msg_data);
137 END IF;
138
139 WHEN NO_TERR_ERROR THEN
140 x_return_status := FND_API.G_RET_STS_SUCCESS;
141 x_msg_data := 'No active territories exist for this usage and transaction type';
142 x_msg_count := 1;
143 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
144 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
145 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.no_terr_error',
146 x_msg_data);
147 END IF;
148
149 WHEN OTHERS THEN
150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
151 x_msg_data := SQLCODE || ' : ' || SQLERRM;
152 x_msg_count := 1;
153 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
154 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
155 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_match.other',
156 substr(x_msg_data, 1, 4000));
157 END IF;
158
159 End process_match;
160
161 /* this procedure find out the winning territories for */
162 /* txn types that uses single level winning process */
163 PROCEDURE single_level_winning_process
164 ( p_source_id IN NUMBER,
165 p_trans_id IN NUMBER,
166 p_mode IN VARCHAR2,
167 p_role IN VARCHAR2,
168 p_resource_type IN VARCHAR2,
169 p_plan_start_date IN DATE DEFAULT NULL,
170 p_plan_end_date IN DATE DEFAULT NULL,
171 x_return_status OUT NOCOPY VARCHAR2,
172 x_msg_count OUT NOCOPY NUMBER,
173 x_msg_data OUT NOCOPY VARCHAR2,
174 x_winners_rec OUT NOCOPY bulk_winners_rec_type
175 ) AS
176
177 BEGIN
178
179 -- debug message
180 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
181 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
182 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.single_level_winning_process.begin',
183 'Start of the procedure JTY_ASSIGN_REALTIME_PUB.single_level_winning_process');
184 END IF;
185
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 IF (p_mode LIKE '%RESOURCE') THEN
189 IF ( p_source_id = -1002 AND p_trans_id IN (-1005,-1009 ) AND
190 p_plan_start_date IS NOT NULL AND p_plan_end_date IS NOT NULL ) THEN
191
192 /* -1005 Service Request
193 -1009 Service Request and Tasks
194 Return the resources which are active between p_plan_start_date and p_plan_end_date.
195 */
196
197 SELECT DISTINCT
198 WT.trans_object_id
199 ,WT.trans_detail_object_id
200 ,WT.txn_date
201 ,WT.terr_id
202 ,jta.org_id
203 ,jtr.person_id
204 ,jta.start_date_active
205 ,jta.end_date_active
206 ,jtr.terr_rsc_id
207 ,jta.name
208 ,null top_level_terr_id
209 ,jta.absolute_rank absolute_rank
210 ,jtr.resource_id
211 ,jtr.start_date_active
212 ,jtr.end_date_active
213 ,jtr.resource_type
214 ,jtr.group_id
215 ,inv.role_id
216 ,jtr.role
217 ,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
218 ,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
219 ,jta.attribute_category terr_attr_category
220 ,jta.attribute1 terr_attribute1
221 ,jta.attribute2 terr_attribute2
222 ,jta.attribute3 terr_attribute3
223 ,jta.attribute4 terr_attribute4
224 ,jta.attribute5 terr_attribute5
225 ,jta.attribute6 terr_attribute6
226 ,jta.attribute7 terr_attribute7
227 ,jta.attribute8 terr_attribute8
228 ,jta.attribute9 terr_attribute9
229 ,jta.attribute10 terr_attribute10
230 ,jta.attribute11 terr_attribute11
231 ,jta.attribute12 terr_attribute12
232 ,jta.attribute13 terr_attribute13
233 ,jta.attribute14 terr_attribute14
234 ,jta.attribute15 terr_attribute15
235 ,jtr.attribute_category rsc_attr_category
236 ,jtr.attribute1 rsc_attribute1
237 ,jtr.attribute2 rsc_attribute2
238 ,jtr.attribute3 rsc_attribute3
239 ,jtr.attribute4 rsc_attribute4
240 ,jtr.attribute5 rsc_attribute5
241 ,jtr.attribute6 rsc_attribute6
242 ,jtr.attribute7 rsc_attribute7
243 ,jtr.attribute8 rsc_attribute8
244 ,jtr.attribute9 rsc_attribute9
245 ,jtr.attribute10 rsc_attribute10
246 ,jtr.attribute11 rsc_attribute11
247 ,jtr.attribute12 rsc_attribute12
248 ,jtr.attribute13 rsc_attribute13
249 ,jtr.attribute14 rsc_attribute14
250 ,jtr.attribute15 rsc_attribute15
251 BULK COLLECT INTO
252 x_winners_rec.trans_object_id
253 ,x_winners_rec.trans_detail_object_id
254 ,x_winners_rec.txn_date
255 ,x_winners_rec.terr_id
256 ,x_winners_rec.org_id
257 ,x_winners_rec.person_id
258 ,x_winners_rec.terr_start_date
259 ,x_winners_rec.terr_end_date
260 ,x_winners_rec.terr_rsc_id
261 ,x_winners_rec.terr_name
262 ,x_winners_rec.top_level_terr_id
263 ,x_winners_rec.absolute_rank
264 ,x_winners_rec.resource_id
265 ,x_winners_rec.rsc_start_date
266 ,x_winners_rec.rsc_end_date
267 ,x_winners_rec.resource_type
268 ,x_winners_rec.group_id
269 ,x_winners_rec.role_id
270 ,x_winners_rec.role
271 ,x_winners_rec.full_access_flag
272 ,x_winners_rec.primary_contact_flag
273 ,x_winners_rec.terr_attr_category
274 ,x_winners_rec.terr_attribute1
275 ,x_winners_rec.terr_attribute2
276 ,x_winners_rec.terr_attribute3
277 ,x_winners_rec.terr_attribute4
278 ,x_winners_rec.terr_attribute5
279 ,x_winners_rec.terr_attribute6
280 ,x_winners_rec.terr_attribute7
281 ,x_winners_rec.terr_attribute8
282 ,x_winners_rec.terr_attribute9
283 ,x_winners_rec.terr_attribute10
284 ,x_winners_rec.terr_attribute11
285 ,x_winners_rec.terr_attribute12
286 ,x_winners_rec.terr_attribute13
287 ,x_winners_rec.terr_attribute14
288 ,x_winners_rec.terr_attribute15
289 ,x_winners_rec.rsc_attr_category
290 ,x_winners_rec.rsc_attribute1
291 ,x_winners_rec.rsc_attribute2
292 ,x_winners_rec.rsc_attribute3
293 ,x_winners_rec.rsc_attribute4
294 ,x_winners_rec.rsc_attribute5
295 ,x_winners_rec.rsc_attribute6
296 ,x_winners_rec.rsc_attribute7
297 ,x_winners_rec.rsc_attribute8
298 ,x_winners_rec.rsc_attribute9
299 ,x_winners_rec.rsc_attribute10
300 ,x_winners_rec.rsc_attribute11
301 ,x_winners_rec.rsc_attribute12
302 ,x_winners_rec.rsc_attribute13
303 ,x_winners_rec.rsc_attribute14
304 ,x_winners_rec.rsc_attribute15
305 FROM
306 ( SELECT
307 o.trans_object_id
308 ,o.trans_detail_object_id
309 ,o.terr_id
310 ,o.txn_date
311 FROM
312 ( SELECT
313 i.trans_id
314 ,i.trans_object_id
315 ,i.trans_detail_object_id
316 ,i.terr_id
317 ,i.top_level_terr_id
318 ,i.txn_date
319 ,RANK() OVER ( PARTITION BY
320 i.trans_id
321 , i.trans_object_id
322 , i.trans_detail_object_id
323 , i.top_level_terr_id
324 ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK
325 FROM jtf_terr_results_GT_MT i ) o
326 WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id)
327 ) WT
328 , jtf_terr_all jta
329 , jtf_terr_rsc_all jtr
330 , jtf_terr_rsc_access_all jtra
331 , jtf_qual_types_all jqta
332 , jtf_rs_roles_b inv
333 WHERE WT.terr_id = jta.terr_id
334 AND WT.terr_id = jtr.terr_id
335 AND ( (TRUNC(jtr.start_date_active) BETWEEN trunc(p_plan_start_date) AND trunc(p_plan_end_date) ) OR
336 ( TRUNC(jtr.end_date_active) BETWEEN trunc(p_plan_start_date) AND trunc(p_plan_end_date) ) OR
337 ( TRUNC(jtr.start_date_active) <= trunc(p_plan_start_date) AND TRUNC(jtr.end_date_active) >= trunc(p_plan_end_date) )
338 )
339 AND jtr.resource_type <> 'RS_ROLE'
340 AND jtr.terr_rsc_id = jtra.terr_rsc_id
341 AND jtra.access_type = jqta.name
342 AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
343 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
344 AND jtr.role = inv.role_code(+)
345 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
346 AND jqta.qual_type_id = p_trans_id
347 ORDER BY WT.trans_object_id, jta.org_id, WT.terr_id, jtr.resource_id;
348
349 ELSE -- ( p_source_id <> -1002 AND p_trans_id NOT IN (-1005,-1009 )
350
351 SELECT DISTINCT
352 WT.trans_object_id
353 ,WT.trans_detail_object_id
354 ,WT.txn_date
355 ,WT.terr_id
356 ,jta.org_id
357 ,jtr.person_id
358 ,jta.start_date_active
359 ,jta.end_date_active
360 ,jtr.terr_rsc_id
361 ,jta.name
362 ,null top_level_terr_id
363 ,jta.absolute_rank absolute_rank
364 ,jtr.resource_id
365 ,jtr.start_date_active
366 ,jtr.end_date_active
367 ,jtr.resource_type
368 ,jtr.group_id
369 ,inv.role_id
370 ,jtr.role
371 ,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
372 ,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
373 ,jta.attribute_category terr_attr_category
374 ,jta.attribute1 terr_attribute1
375 ,jta.attribute2 terr_attribute2
376 ,jta.attribute3 terr_attribute3
377 ,jta.attribute4 terr_attribute4
378 ,jta.attribute5 terr_attribute5
379 ,jta.attribute6 terr_attribute6
380 ,jta.attribute7 terr_attribute7
381 ,jta.attribute8 terr_attribute8
382 ,jta.attribute9 terr_attribute9
383 ,jta.attribute10 terr_attribute10
384 ,jta.attribute11 terr_attribute11
385 ,jta.attribute12 terr_attribute12
386 ,jta.attribute13 terr_attribute13
387 ,jta.attribute14 terr_attribute14
388 ,jta.attribute15 terr_attribute15
389 ,jtr.attribute_category rsc_attr_category
390 ,jtr.attribute1 rsc_attribute1
391 ,jtr.attribute2 rsc_attribute2
392 ,jtr.attribute3 rsc_attribute3
393 ,jtr.attribute4 rsc_attribute4
394 ,jtr.attribute5 rsc_attribute5
395 ,jtr.attribute6 rsc_attribute6
396 ,jtr.attribute7 rsc_attribute7
397 ,jtr.attribute8 rsc_attribute8
398 ,jtr.attribute9 rsc_attribute9
399 ,jtr.attribute10 rsc_attribute10
400 ,jtr.attribute11 rsc_attribute11
401 ,jtr.attribute12 rsc_attribute12
402 ,jtr.attribute13 rsc_attribute13
403 ,jtr.attribute14 rsc_attribute14
404 ,jtr.attribute15 rsc_attribute15
405 BULK COLLECT INTO
406 x_winners_rec.trans_object_id
407 ,x_winners_rec.trans_detail_object_id
408 ,x_winners_rec.txn_date
409 ,x_winners_rec.terr_id
410 ,x_winners_rec.org_id
411 ,x_winners_rec.person_id
412 ,x_winners_rec.terr_start_date
413 ,x_winners_rec.terr_end_date
414 ,x_winners_rec.terr_rsc_id
415 ,x_winners_rec.terr_name
416 ,x_winners_rec.top_level_terr_id
417 ,x_winners_rec.absolute_rank
418 ,x_winners_rec.resource_id
419 ,x_winners_rec.rsc_start_date
420 ,x_winners_rec.rsc_end_date
421 ,x_winners_rec.resource_type
422 ,x_winners_rec.group_id
423 ,x_winners_rec.role_id
424 ,x_winners_rec.role
425 ,x_winners_rec.full_access_flag
426 ,x_winners_rec.primary_contact_flag
427 ,x_winners_rec.terr_attr_category
428 ,x_winners_rec.terr_attribute1
429 ,x_winners_rec.terr_attribute2
430 ,x_winners_rec.terr_attribute3
431 ,x_winners_rec.terr_attribute4
432 ,x_winners_rec.terr_attribute5
433 ,x_winners_rec.terr_attribute6
434 ,x_winners_rec.terr_attribute7
435 ,x_winners_rec.terr_attribute8
436 ,x_winners_rec.terr_attribute9
437 ,x_winners_rec.terr_attribute10
438 ,x_winners_rec.terr_attribute11
439 ,x_winners_rec.terr_attribute12
440 ,x_winners_rec.terr_attribute13
441 ,x_winners_rec.terr_attribute14
442 ,x_winners_rec.terr_attribute15
443 ,x_winners_rec.rsc_attr_category
444 ,x_winners_rec.rsc_attribute1
445 ,x_winners_rec.rsc_attribute2
446 ,x_winners_rec.rsc_attribute3
447 ,x_winners_rec.rsc_attribute4
448 ,x_winners_rec.rsc_attribute5
449 ,x_winners_rec.rsc_attribute6
450 ,x_winners_rec.rsc_attribute7
451 ,x_winners_rec.rsc_attribute8
452 ,x_winners_rec.rsc_attribute9
453 ,x_winners_rec.rsc_attribute10
454 ,x_winners_rec.rsc_attribute11
455 ,x_winners_rec.rsc_attribute12
456 ,x_winners_rec.rsc_attribute13
457 ,x_winners_rec.rsc_attribute14
458 ,x_winners_rec.rsc_attribute15
459 FROM
460 ( SELECT
461 o.trans_object_id
462 ,o.trans_detail_object_id
463 ,o.terr_id
464 ,o.txn_date
465 FROM
466 ( SELECT
467 i.trans_id
468 ,i.trans_object_id
469 ,i.trans_detail_object_id
470 ,i.terr_id
471 ,i.top_level_terr_id
472 ,i.txn_date
473 ,RANK() OVER ( PARTITION BY
474 i.trans_id
475 , i.trans_object_id
476 , i.trans_detail_object_id
477 , i.top_level_terr_id
478 ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK
479 FROM jtf_terr_results_GT_MT i ) o
480 WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id)
481 ) WT
482 , jtf_terr_all jta
483 , jtf_terr_rsc_all jtr
484 , jtf_terr_rsc_access_all jtra
485 , jtf_qual_types_all jqta
486 , jtf_rs_roles_b inv
487 WHERE WT.terr_id = jta.terr_id
488 AND WT.terr_id = jtr.terr_id
489 AND jtr.end_date_active >= WT.txn_date
490 AND jtr.start_date_active <= WT.txn_date
491 AND jtr.resource_type <> 'RS_ROLE'
492 AND jtr.terr_rsc_id = jtra.terr_rsc_id
493 AND jtra.access_type = jqta.name
494 AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
495 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
496 AND jtr.role = inv.role_code(+)
497 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
498 AND jqta.qual_type_id = p_trans_id
499 ORDER BY WT.trans_object_id, jta.org_id, WT.terr_id, jtr.resource_id;
500 END IF; -- End of IF ( p_source_id = -1002 AND p_trans_id IN (-1005,-1009 ) THEN
501
502 ELSIF (p_mode LIKE '%TERRITORY') THEN
503 SELECT DISTINCT
504 WT.trans_object_id
505 ,WT.trans_detail_object_id
506 ,WT.txn_date
507 ,WT.terr_id
508 ,jta.org_id
509 ,jta.start_date_active
510 ,jta.end_date_active
511 ,jta.name
512 ,null top_level_terr_id
513 ,jta.absolute_rank absolute_rank
514 ,jta.attribute_category terr_attr_category
515 ,jta.attribute1 terr_attribute1
516 ,jta.attribute2 terr_attribute2
517 ,jta.attribute3 terr_attribute3
518 ,jta.attribute4 terr_attribute4
519 ,jta.attribute5 terr_attribute5
520 ,jta.attribute6 terr_attribute6
521 ,jta.attribute7 terr_attribute7
522 ,jta.attribute8 terr_attribute8
523 ,jta.attribute9 terr_attribute9
524 ,jta.attribute10 terr_attribute10
525 ,jta.attribute11 terr_attribute11
526 ,jta.attribute12 terr_attribute12
527 ,jta.attribute13 terr_attribute13
528 ,jta.attribute14 terr_attribute14
529 ,jta.attribute15 terr_attribute15
530 BULK COLLECT INTO
531 x_winners_rec.trans_object_id
532 ,x_winners_rec.trans_detail_object_id
533 ,x_winners_rec.txn_date
534 ,x_winners_rec.terr_id
535 ,x_winners_rec.org_id
536 ,x_winners_rec.terr_start_date
537 ,x_winners_rec.terr_end_date
538 ,x_winners_rec.terr_name
539 ,x_winners_rec.top_level_terr_id
540 ,x_winners_rec.absolute_rank
541 ,x_winners_rec.terr_attr_category
542 ,x_winners_rec.terr_attribute1
543 ,x_winners_rec.terr_attribute2
544 ,x_winners_rec.terr_attribute3
545 ,x_winners_rec.terr_attribute4
546 ,x_winners_rec.terr_attribute5
547 ,x_winners_rec.terr_attribute6
548 ,x_winners_rec.terr_attribute7
549 ,x_winners_rec.terr_attribute8
550 ,x_winners_rec.terr_attribute9
551 ,x_winners_rec.terr_attribute10
552 ,x_winners_rec.terr_attribute11
553 ,x_winners_rec.terr_attribute12
554 ,x_winners_rec.terr_attribute13
555 ,x_winners_rec.terr_attribute14
556 ,x_winners_rec.terr_attribute15
557 FROM
558 ( SELECT
559 o.trans_object_id
560 ,o.trans_detail_object_id
561 ,o.terr_id
562 ,o.txn_date
563 FROM
564 ( SELECT
565 i.trans_id
566 ,i.trans_object_id
567 ,i.trans_detail_object_id
568 ,i.terr_id
569 ,i.top_level_terr_id
570 ,i.txn_date
571 ,RANK() OVER ( PARTITION BY
572 i.trans_id
573 , i.trans_object_id
574 , i.trans_detail_object_id
575 , i.top_level_terr_id
576 ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK
577 FROM jtf_terr_results_GT_MT i ) o
578 WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id)
579 ) WT
580 , jtf_terr_all jta
581 WHERE WT.terr_id = jta.terr_id
582 AND EXISTS (
583 SELECT 1
584 FROM
585 jtf_terr_rsc_all jtr
586 , jtf_terr_rsc_access_all jtra
587 , jtf_qual_types_all jqta
588 WHERE WT.terr_id = jtr.terr_id
589 AND jtr.end_date_active >= WT.txn_date
590 AND jtr.start_date_active <= WT.txn_date
591 AND jtr.resource_type <> 'RS_ROLE'
592 AND jtr.terr_rsc_id = jtra.terr_rsc_id
593 AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
594 AND jtra.access_type = jqta.name
595 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
596 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
597 AND jqta.qual_type_id = p_trans_id);
598
599 ELSIF (p_mode LIKE '%LOOKUP') THEN
600 SELECT /*+ PUSH_PRED(rsv) */ DISTINCT
601 WT.trans_object_id
602 ,WT.trans_detail_object_id
603 ,WT.txn_date
604 ,WT.terr_id
605 ,jta.org_id
606 ,jtr.person_id
607 ,jta.start_date_active
608 ,jta.end_date_active
609 ,jtr.terr_rsc_id
610 ,jta.name
611 ,null top_level_terr_id
612 ,jta.absolute_rank absolute_rank
613 ,jtr.resource_id
614 ,jtr.start_date_active
615 ,jtr.end_date_active
616 ,jtr.resource_type
617 ,jtr.group_id
618 ,role.role_id
619 ,jtr.role
620 ,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
621 ,decode(jtra.trans_access_code, 'TEAM_LEADER', 'Y', 'PRIMARY_CONTACT', 'Y', 'N') --jtr.primary_contact_flag
622 ,rsv.resource_name
623 ,rsc.source_job_title resource_job_title
624 ,rsc.source_phone resource_phone
625 ,rsc.source_email resource_email
626 ,rsc.source_mgr_name resource_mgr_name
627 ,mgr.source_phone resource_mgr_phone
628 ,mgr.source_email resource_mgr_email
629 ,jta.name property1
630 ,role.role_name property2
631 ,jta_p.name property3
632 ,rsc.attribute4 property4
633 ,rsc.attribute5 property5
634 ,rsc.attribute6 property6
635 ,rsc.attribute7 property7
636 ,rsc.attribute8 property8
637 ,rsc.attribute9 property9
638 ,rsc.attribute10 property10
639 ,rsc.attribute11 property11
640 ,rsc.attribute12 property12
641 ,rsc.attribute13 property13
642 ,rsc.attribute14 property14
643 ,rsc.attribute15 property15
644 ,jta.attribute_category terr_attr_category
645 ,jta.attribute1 terr_attribute1
646 ,jta.attribute2 terr_attribute2
647 ,jta.attribute3 terr_attribute3
648 ,jta.attribute4 terr_attribute4
649 ,jta.attribute5 terr_attribute5
650 ,jta.attribute6 terr_attribute6
651 ,jta.attribute7 terr_attribute7
652 ,jta.attribute8 terr_attribute8
653 ,jta.attribute9 terr_attribute9
654 ,jta.attribute10 terr_attribute10
655 ,jta.attribute11 terr_attribute11
656 ,jta.attribute12 terr_attribute12
657 ,jta.attribute13 terr_attribute13
658 ,jta.attribute14 terr_attribute14
659 ,jta.attribute15 terr_attribute15
660 ,jtr.attribute_category rsc_attr_category
661 ,jtr.attribute1 rsc_attribute1
662 ,jtr.attribute2 rsc_attribute2
663 ,jtr.attribute3 rsc_attribute3
664 ,jtr.attribute4 rsc_attribute4
665 ,jtr.attribute5 rsc_attribute5
666 ,jtr.attribute6 rsc_attribute6
667 ,jtr.attribute7 rsc_attribute7
668 ,jtr.attribute8 rsc_attribute8
669 ,jtr.attribute9 rsc_attribute9
670 ,jtr.attribute10 rsc_attribute10
671 ,jtr.attribute11 rsc_attribute11
672 ,jtr.attribute12 rsc_attribute12
673 ,jtr.attribute13 rsc_attribute13
674 ,jtr.attribute14 rsc_attribute14
675 ,jtr.attribute15 rsc_attribute15
676 BULK COLLECT INTO
677 x_winners_rec.trans_object_id
678 ,x_winners_rec.trans_detail_object_id
679 ,x_winners_rec.txn_date
680 ,x_winners_rec.terr_id
681 ,x_winners_rec.org_id
682 ,x_winners_rec.person_id
683 ,x_winners_rec.terr_start_date
684 ,x_winners_rec.terr_end_date
685 ,x_winners_rec.terr_rsc_id
686 ,x_winners_rec.terr_name
687 ,x_winners_rec.top_level_terr_id
688 ,x_winners_rec.absolute_rank
689 ,x_winners_rec.resource_id
690 ,x_winners_rec.rsc_start_date
691 ,x_winners_rec.rsc_end_date
692 ,x_winners_rec.resource_type
693 ,x_winners_rec.group_id
694 ,x_winners_rec.role_id
695 ,x_winners_rec.role
696 ,x_winners_rec.full_access_flag
697 ,x_winners_rec.primary_contact_flag
698 ,x_winners_rec.resource_name
699 ,x_winners_rec.resource_job_title
700 ,x_winners_rec.resource_phone
701 ,x_winners_rec.resource_email
702 ,x_winners_rec.resource_mgr_name
703 ,x_winners_rec.resource_mgr_phone
704 ,x_winners_rec.resource_mgr_email
705 ,x_winners_rec.property1
706 ,x_winners_rec.property2
707 ,x_winners_rec.property3
708 ,x_winners_rec.property4
709 ,x_winners_rec.property5
710 ,x_winners_rec.property6
711 ,x_winners_rec.property7
712 ,x_winners_rec.property8
713 ,x_winners_rec.property9
714 ,x_winners_rec.property10
715 ,x_winners_rec.property11
716 ,x_winners_rec.property12
717 ,x_winners_rec.property13
718 ,x_winners_rec.property14
719 ,x_winners_rec.property15
720 ,x_winners_rec.terr_attr_category
721 ,x_winners_rec.terr_attribute1
722 ,x_winners_rec.terr_attribute2
723 ,x_winners_rec.terr_attribute3
724 ,x_winners_rec.terr_attribute4
725 ,x_winners_rec.terr_attribute5
726 ,x_winners_rec.terr_attribute6
727 ,x_winners_rec.terr_attribute7
728 ,x_winners_rec.terr_attribute8
729 ,x_winners_rec.terr_attribute9
730 ,x_winners_rec.terr_attribute10
731 ,x_winners_rec.terr_attribute11
732 ,x_winners_rec.terr_attribute12
733 ,x_winners_rec.terr_attribute13
734 ,x_winners_rec.terr_attribute14
735 ,x_winners_rec.terr_attribute15
736 ,x_winners_rec.rsc_attr_category
737 ,x_winners_rec.rsc_attribute1
738 ,x_winners_rec.rsc_attribute2
739 ,x_winners_rec.rsc_attribute3
740 ,x_winners_rec.rsc_attribute4
741 ,x_winners_rec.rsc_attribute5
742 ,x_winners_rec.rsc_attribute6
743 ,x_winners_rec.rsc_attribute7
744 ,x_winners_rec.rsc_attribute8
745 ,x_winners_rec.rsc_attribute9
746 ,x_winners_rec.rsc_attribute10
747 ,x_winners_rec.rsc_attribute11
748 ,x_winners_rec.rsc_attribute12
749 ,x_winners_rec.rsc_attribute13
750 ,x_winners_rec.rsc_attribute14
751 ,x_winners_rec.rsc_attribute15
752 FROM
753 ( SELECT
754 o.trans_object_id
755 ,o.trans_detail_object_id
756 ,o.terr_id
757 ,o.txn_date
758 FROM
759 ( SELECT
760 i.trans_id
761 ,i.trans_object_id
762 ,i.trans_detail_object_id
763 ,i.terr_id
764 ,i.top_level_terr_id
765 ,i.txn_date
766 ,RANK() OVER ( PARTITION BY
767 i.trans_id
768 , i.trans_object_id
769 , i.trans_detail_object_id
770 , i.top_level_terr_id
771 ORDER BY i.absolute_rank DESC, i.terr_id) AS TERR_RANK
772 FROM jtf_terr_results_GT_MT i ) o
773 WHERE o.TERR_RANK <= (SELECT NVL(t.num_winners, 1) FROM jtf_terr_all t WHERE t.terr_id = o.top_level_terr_id)
774 ) WT
775 , jtf_terr_all jta
776 , jtf_terr_all jta_p
777 , jtf_terr_rsc_all jtr
778 , jtf_terr_rsc_access_all jtra
779 , jtf_qual_types_all jqta
780 , (select a.group_id resource_id, a.group_name resource_name, 'RS_GROUP' resource_type
781 from jtf_rs_groups_tl a
782 where a.language = userenv('LANG')
783 union all
784 select a.team_id resource_id, a.team_name resource_name, 'RS_TEAM' resource_type
785 from jtf_rs_teams_tl a
786 where a.language = userenv('LANG')
787 union all
788 select a.resource_id resource_id, a.resource_name resource_name,
789 decode(a.category ,'EMPLOYEE', 'RS_EMPLOYEE','PARTNER', 'RS_PARTNER','SUPPLIER_CONTACT',
790 'RS_SUPPLIER_CONTACT' , 'PARTY', 'RS_PARTY' , 'OTHER',
791 'RS_OTHER', 'TBH', 'RS_TBH') RESOURCE_TYPE
792 from jtf_rs_resource_extns_tl a
793 where a.language = userenv('LANG')
794 ) rsv
795 , jtf_rs_resource_extns rsc
796 , jtf_rs_resource_extns mgr
797 , jtf_rs_roles_vl role
798 WHERE WT.terr_id = jta.terr_id
799 AND WT.terr_id = jtr.terr_id
800 AND jtr.end_date_active >= WT.txn_date
801 AND jtr.start_date_active <= WT.txn_date
802 AND jtr.resource_type <> 'RS_ROLE'
803 AND jtr.terr_rsc_id = jtra.terr_rsc_id
804 AND jtra.trans_access_code NOT IN ('NONE', 'ESC_OWNER')
805 AND jtra.access_type = jqta.name
806 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
807 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
808 AND jqta.qual_type_id = p_trans_id
809 AND jtr.resource_id = rsv.resource_id
810 AND jtr.resource_type = rsv.resource_type
811 AND decode(jtr.resource_type, 'RS_EMPLOYEE', jtr.resource_id, -999) = rsc.resource_id(+)
812 AND rsc.source_mgr_id = mgr.source_id(+)
813 AND rsc.category = mgr.category(+)
814 AND jta.parent_territory_id = jta_p.terr_id
815 AND jtr.role = role.role_code(+);
816 END IF;
817
818
819 IF (x_winners_rec.trans_object_id.COUNT > 0) THEN
820 x_winners_rec.use_type := p_mode;
821 x_winners_rec.source_id := p_source_id;
822 x_winners_rec.trans_id := p_trans_id;
823 END IF;
824
825 -- debug message
826 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
827 FND_LOG.string(FND_LOG.LEVEL_EVENT,
828 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.single_level_winning_process.winners',
829 'Number of winning territories : ' || x_winners_rec.trans_object_id.COUNT);
830 END IF;
831
832 -- debug message
833 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
834 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
835 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.single_level_winning_process.end',
836 'End of the procedure JTY_ASSIGN_REALTIME_PUB.single_level_winning_process');
837 END IF;
838
839 EXCEPTION
840
841 WHEN OTHERS THEN
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
843 x_msg_data := SQLCODE || ' : ' || SQLERRM;
844 x_msg_count := 1;
845 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
846 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
847 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.single_level_winning_process.other',
848 substr(x_msg_data, 1, 4000));
849 END IF;
850
851 END single_level_winning_process;
852
853 /* this procedure find out the winning territories for */
854 /* txn types that uses multiple level winning process */
855 PROCEDURE multi_level_winning_process
856 ( p_source_id IN NUMBER,
857 p_trans_id IN NUMBER,
858 p_mode IN VARCHAR2,
859 p_role IN VARCHAR2,
860 p_resource_type IN VARCHAR2,
861 x_return_status OUT NOCOPY VARCHAR2,
862 x_msg_count OUT NOCOPY NUMBER,
863 x_msg_data OUT NOCOPY VARCHAR2,
864 x_winners_rec OUT NOCOPY bulk_winners_rec_type
865 ) AS
866
867 l_matches_target VARCHAR2(30);
868 l_terr_L1_target VARCHAR2(30);
869 l_terr_L2_target VARCHAR2(30);
870 l_terr_L3_target VARCHAR2(30);
871 l_terr_L4_target VARCHAR2(30);
872 l_terr_L5_target VARCHAR2(30);
873 l_terr_WT_target VARCHAR2(30);
874 l_date_effective BOOLEAN;
875
876 l_winner_no_of_records NUMBER;
877 l_worker_id NUMBER;
878
879 BEGIN
880
881 -- debug message
882 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
883 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
884 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.begin',
885 'Start of the procedure JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process');
886 END IF;
887
888 x_return_status := FND_API.G_RET_STS_SUCCESS;
889 l_worker_id := 1;
890 l_matches_target := 'JTF_TERR_RESULTS_GT_MT';
891 l_terr_L1_target := 'JTF_TERR_RESULTS_GT_L1';
892 l_terr_L2_target := 'JTF_TERR_RESULTS_GT_L2';
893 l_terr_L3_target := 'JTF_TERR_RESULTS_GT_L3';
894 l_terr_L4_target := 'JTF_TERR_RESULTS_GT_L4';
895 l_terr_L5_target := 'JTF_TERR_RESULTS_GT_L5';
896 l_terr_WT_target := 'JTF_TERR_RESULTS_GT_WT';
897
898 IF (p_mode LIKE 'DATE EFFECTIVE%') THEN
899 l_date_effective := true;
900 ELSE
901 l_date_effective := false;
902 END IF;
903
904 DELETE FROM JTF_TERR_RESULTS_GT_L1;
905 DELETE FROM JTF_TERR_RESULTS_GT_L2;
906 DELETE FROM JTF_TERR_RESULTS_GT_L3;
907 DELETE FROM JTF_TERR_RESULTS_GT_L4;
908 DELETE FROM JTF_TERR_RESULTS_GT_L5;
909 DELETE FROM JTF_TERR_RESULTS_GT_WT;
910
911 JTY_ASSIGN_BULK_PUB.Process_Level_Winners (
912 p_terr_LEVEL_target_tbl => l_terr_L1_target,
913 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
914 p_UPPER_LEVEL_FROM_ROOT => 1,
915 p_LOWER_LEVEL_FROM_ROOT => 1,
916 p_matches_target => l_matches_target,
917 p_source_id => p_source_id,
918 p_run_mode => 'REAL TIME',
919 p_date_effective => l_date_effective,
920 x_return_status => x_return_status,
921 p_worker_id => l_worker_id
922 );
923
924 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
925 -- debug message
926 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed for level 1';
927 x_msg_count := 1;
928 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
929 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
930 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l1',
931 x_msg_data);
932 END IF;
933
934 RAISE FND_API.G_EXC_ERROR;
935 END IF;
936
937 -- debug message
938 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
939 SELECT COUNT(*)
940 INTO l_winner_no_of_records
941 FROM jtf_terr_results_gt_l1;
942
943 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
944 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l1',
945 'Number of records inserted into jtf_terr_results_gt_l1 table : ' || l_winner_no_of_records);
946 END IF;
947
948 JTY_ASSIGN_BULK_PUB.Process_Level_Winners (
949 p_terr_LEVEL_target_tbl => l_terr_L2_target,
950 p_terr_PARENT_LEVEL_tbl => l_terr_L1_target,
951 p_UPPER_LEVEL_FROM_ROOT => 1,
952 p_LOWER_LEVEL_FROM_ROOT => 2,
953 p_matches_target => l_matches_target,
954 p_source_id => p_source_id,
955 p_run_mode => 'REAL TIME',
956 p_date_effective => l_date_effective,
957 x_return_status => x_return_status,
958 p_worker_id => l_worker_id
959 );
960
961 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
962 -- debug message
963 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed for level 2';
964 x_msg_count := 1;
965 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
966 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
967 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l2',
968 x_msg_data);
969 END IF;
970
971 RAISE FND_API.G_EXC_ERROR;
972 END IF;
973
974 -- debug message
975 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
976 SELECT COUNT(*)
977 INTO l_winner_no_of_records
978 FROM jtf_terr_results_gt_l2;
979
980 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
981 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l2',
982 'Number of records inserted into jtf_terr_results_gt_l2 table : ' || l_winner_no_of_records);
983 END IF;
984
985 JTY_ASSIGN_BULK_PUB.Process_Level_Winners (
986 p_terr_LEVEL_target_tbl => l_terr_L3_target,
987 p_terr_PARENT_LEVEL_tbl => l_terr_L2_target,
988 p_UPPER_LEVEL_FROM_ROOT => 2,
989 p_LOWER_LEVEL_FROM_ROOT => 3,
990 p_matches_target => l_matches_target,
991 p_source_id => p_source_id,
992 p_run_mode => 'REAL TIME',
993 p_date_effective => l_date_effective,
994 x_return_status => x_return_status,
995 p_worker_id => l_worker_id
996 );
997
998 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
999 -- debug message
1000 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed for level 3';
1001 x_msg_count := 1;
1002 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1003 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1004 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l3',
1005 x_msg_data);
1006 END IF;
1007
1008 RAISE FND_API.G_EXC_ERROR;
1009 END IF;
1010
1011 -- debug message
1012 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1013 SELECT COUNT(*)
1014 INTO l_winner_no_of_records
1015 FROM jtf_terr_results_gt_l3;
1016
1017 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1018 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l3',
1019 'Number of records inserted into jtf_terr_results_gt_l3 table : ' || l_winner_no_of_records);
1020 END IF;
1021
1022 JTY_ASSIGN_BULK_PUB.Process_Level_Winners (
1023 p_terr_LEVEL_target_tbl => l_terr_L4_target,
1024 p_terr_PARENT_LEVEL_tbl => l_terr_L3_target,
1025 p_UPPER_LEVEL_FROM_ROOT => 3,
1026 p_LOWER_LEVEL_FROM_ROOT => 4,
1027 p_matches_target => l_matches_target,
1028 p_source_id => p_source_id,
1029 p_run_mode => 'REAL TIME',
1030 p_date_effective => l_date_effective,
1031 x_return_status => x_return_status,
1032 p_worker_id => l_worker_id
1033 );
1034
1035 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1036 -- debug message
1037 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed for level 4';
1038 x_msg_count := 1;
1039 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1040 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1041 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l4',
1042 x_msg_data);
1043 END IF;
1044
1045 RAISE FND_API.G_EXC_ERROR;
1046 END IF;
1047
1048 -- debug message
1049 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1050 SELECT COUNT(*)
1051 INTO l_winner_no_of_records
1052 FROM jtf_terr_results_gt_l4;
1053
1054 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1055 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l4',
1056 'Number of records inserted into jtf_terr_results_gt_l4 table : ' || l_winner_no_of_records);
1057 END IF;
1058
1059 JTY_ASSIGN_BULK_PUB.Process_Level_Winners (
1060 p_terr_LEVEL_target_tbl => l_terr_L5_target,
1061 p_terr_PARENT_LEVEL_tbl => l_terr_L4_target,
1062 p_UPPER_LEVEL_FROM_ROOT => 4,
1063 p_LOWER_LEVEL_FROM_ROOT => 5,
1064 p_matches_target => l_matches_target,
1065 p_source_id => p_source_id,
1066 p_run_mode => 'REAL TIME',
1067 p_date_effective => l_date_effective,
1068 x_return_status => x_return_status,
1069 p_worker_id => l_worker_id
1070 );
1071
1072 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1073 -- debug message
1074 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Level_Winners has failed for level 5';
1075 x_msg_count := 1;
1076 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1077 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1078 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l5',
1079 x_msg_data);
1080 END IF;
1081
1082 RAISE FND_API.G_EXC_ERROR;
1083 END IF;
1084
1085 -- debug message
1086 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1087 SELECT COUNT(*)
1088 INTO l_winner_no_of_records
1089 FROM jtf_terr_results_gt_l5;
1090
1091 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1092 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.l5',
1093 'Number of records inserted into jtf_terr_results_gt_l5 table : ' || l_winner_no_of_records);
1094 END IF;
1095
1096 JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners (
1097 p_terr_LEVEL_target_tbl => l_terr_WT_target,
1098 p_terr_L5_target_tbl => l_terr_L5_target,
1099 p_matches_target => l_matches_target,
1100 p_source_id => p_source_id,
1101 p_run_mode => 'REAL TIME',
1102 p_date_effective => l_date_effective,
1103 x_return_status => x_return_status,
1104 p_worker_id => l_worker_id
1105 );
1106
1107 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1108 -- debug message
1109 x_msg_data := 'API JTY_ASSIGN_BULK_PUB.Process_Final_Level_Winners has failed';
1110 x_msg_count := 1;
1111 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1112 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1113 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.wt',
1114 x_msg_data);
1115 END IF;
1116
1117 RAISE FND_API.G_EXC_ERROR;
1118 END IF;
1119
1120 -- debug message
1121 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1122 SELECT COUNT(*)
1123 INTO l_winner_no_of_records
1124 FROM jtf_terr_results_gt_wt;
1125
1126 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1127 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.wt',
1128 'Number of records inserted into jtf_terr_results_gt_wt table : ' || l_winner_no_of_records);
1129 END IF;
1130
1131 BEGIN
1132
1133 IF (p_mode LIKE '%RESOURCE') THEN
1134 SELECT DISTINCT
1135 WINNERS.trans_object_id
1136 ,WINNERS.trans_detail_object_id
1137 ,WINNERS.txn_date
1138 ,WINNERS.win_terr_id
1139 ,jta.org_id
1140 ,jtr.person_id
1141 ,jta.start_date_active
1142 ,jta.end_date_active
1143 ,jtr.terr_rsc_id
1144 ,jta.name
1145 ,null top_level_terr_id
1146 ,jta.absolute_rank absolute_rank
1147 ,jtr.resource_id
1148 ,jtr.start_date_active
1149 ,jtr.end_date_active
1150 ,jtr.resource_type
1151 ,jtr.group_id
1152 ,inv.role_id
1153 ,jtr.role
1154 ,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
1155 ,jtr.primary_contact_flag
1156 ,jta.attribute_category terr_attr_category
1157 ,jta.attribute1 terr_attribute1
1158 ,jta.attribute2 terr_attribute2
1159 ,jta.attribute3 terr_attribute3
1160 ,jta.attribute4 terr_attribute4
1161 ,jta.attribute5 terr_attribute5
1162 ,jta.attribute6 terr_attribute6
1163 ,jta.attribute7 terr_attribute7
1164 ,jta.attribute8 terr_attribute8
1165 ,jta.attribute9 terr_attribute9
1166 ,jta.attribute10 terr_attribute10
1167 ,jta.attribute11 terr_attribute11
1168 ,jta.attribute12 terr_attribute12
1169 ,jta.attribute13 terr_attribute13
1170 ,jta.attribute14 terr_attribute14
1171 ,jta.attribute15 terr_attribute15
1172 ,jtr.attribute_category rsc_attr_category
1173 ,jtr.attribute1 rsc_attribute1
1174 ,jtr.attribute2 rsc_attribute2
1175 ,jtr.attribute3 rsc_attribute3
1176 ,jtr.attribute4 rsc_attribute4
1177 ,jtr.attribute5 rsc_attribute5
1178 ,jtr.attribute6 rsc_attribute6
1179 ,jtr.attribute7 rsc_attribute7
1180 ,jtr.attribute8 rsc_attribute8
1181 ,jtr.attribute9 rsc_attribute9
1182 ,jtr.attribute10 rsc_attribute10
1183 ,jtr.attribute11 rsc_attribute11
1184 ,jtr.attribute12 rsc_attribute12
1185 ,jtr.attribute13 rsc_attribute13
1186 ,jtr.attribute14 rsc_attribute14
1187 ,jtr.attribute15 rsc_attribute15
1188 BULK COLLECT INTO
1189 x_winners_rec.trans_object_id
1190 ,x_winners_rec.trans_detail_object_id
1191 ,x_winners_rec.txn_date
1192 ,x_winners_rec.terr_id
1193 ,x_winners_rec.org_id
1194 ,x_winners_rec.person_id
1195 ,x_winners_rec.terr_start_date
1196 ,x_winners_rec.terr_end_date
1197 ,x_winners_rec.terr_rsc_id
1198 ,x_winners_rec.terr_name
1199 ,x_winners_rec.top_level_terr_id
1200 ,x_winners_rec.absolute_rank
1201 ,x_winners_rec.resource_id
1202 ,x_winners_rec.rsc_start_date
1203 ,x_winners_rec.rsc_end_date
1204 ,x_winners_rec.resource_type
1205 ,x_winners_rec.group_id
1206 ,x_winners_rec.role_id
1207 ,x_winners_rec.role
1208 ,x_winners_rec.full_access_flag
1209 ,x_winners_rec.primary_contact_flag
1210 ,x_winners_rec.terr_attr_category
1211 ,x_winners_rec.terr_attribute1
1212 ,x_winners_rec.terr_attribute2
1213 ,x_winners_rec.terr_attribute3
1214 ,x_winners_rec.terr_attribute4
1215 ,x_winners_rec.terr_attribute5
1216 ,x_winners_rec.terr_attribute6
1217 ,x_winners_rec.terr_attribute7
1218 ,x_winners_rec.terr_attribute8
1219 ,x_winners_rec.terr_attribute9
1220 ,x_winners_rec.terr_attribute10
1221 ,x_winners_rec.terr_attribute11
1222 ,x_winners_rec.terr_attribute12
1223 ,x_winners_rec.terr_attribute13
1224 ,x_winners_rec.terr_attribute14
1225 ,x_winners_rec.terr_attribute15
1226 ,x_winners_rec.rsc_attr_category
1227 ,x_winners_rec.rsc_attribute1
1228 ,x_winners_rec.rsc_attribute2
1229 ,x_winners_rec.rsc_attribute3
1230 ,x_winners_rec.rsc_attribute4
1231 ,x_winners_rec.rsc_attribute5
1232 ,x_winners_rec.rsc_attribute6
1233 ,x_winners_rec.rsc_attribute7
1234 ,x_winners_rec.rsc_attribute8
1235 ,x_winners_rec.rsc_attribute9
1236 ,x_winners_rec.rsc_attribute10
1237 ,x_winners_rec.rsc_attribute11
1238 ,x_winners_rec.rsc_attribute12
1239 ,x_winners_rec.rsc_attribute13
1240 ,x_winners_rec.rsc_attribute14
1241 ,x_winners_rec.rsc_attribute15
1242 FROM
1243 (
1244 /* WINNERS ILV */
1245 SELECT LX.trans_object_id
1246 , LX.trans_detail_object_id
1247 , LX.WIN_TERR_ID
1248 , LX.txn_date
1249 FROM jtf_terr_results_GT_L1 LX
1250 , ( SELECT trans_object_id
1251 , trans_detail_object_id
1252 , WIN_TERR_ID WIN_TERR_ID
1253 FROM JTF_terr_results_GT_L1
1254 MINUS
1255 SELECT trans_object_id
1256 , trans_detail_object_id
1257 , ul_terr_id WIN_TERR_ID
1258 FROM JTF_terr_results_GT_L2 ) ILV
1259 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1260 OR
1261 LX.trans_detail_object_id IS NULL )
1262 AND LX.trans_object_id = ILV.trans_object_id
1263 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1264
1265 UNION ALL
1266
1267 SELECT LX.trans_object_id
1268 , LX.trans_detail_object_id
1269 , LX.WIN_TERR_ID
1270 , LX.txn_date
1271 FROM jtf_terr_results_GT_L2 LX
1272 , ( SELECT trans_object_id
1273 , trans_detail_object_id
1274 , WIN_TERR_ID WIN_TERR_ID
1275 FROM JTF_terr_results_GT_L2
1276 MINUS
1277 SELECT trans_object_id
1278 , trans_detail_object_id
1279 , ul_terr_id WIN_TERR_ID
1280 FROM JTF_terr_results_GT_L3 ) ILV
1281 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1282 OR
1283 LX.trans_detail_object_id IS NULL )
1284 AND LX.trans_object_id = ILV.trans_object_id
1285 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1286
1287 UNION ALL
1288
1289 SELECT LX.trans_object_id
1290 , LX.trans_detail_object_id
1291 , LX.WIN_TERR_ID
1292 , LX.txn_date
1293 FROM jtf_terr_results_GT_L3 LX
1294 , ( SELECT trans_object_id
1295 , trans_detail_object_id
1296 , WIN_TERR_ID WIN_TERR_ID
1297 FROM JTF_terr_results_GT_L3
1298 MINUS
1299 SELECT trans_object_id
1300 , trans_detail_object_id
1301 , ul_terr_id WIN_TERR_ID
1302 FROM JTF_terr_results_GT_L4 ) ILV
1303 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1304 OR
1305 LX.trans_detail_object_id IS NULL )
1306 AND LX.trans_object_id = ILV.trans_object_id
1307 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1308
1309 UNION ALL
1310
1311 SELECT LX.trans_object_id
1312 , LX.trans_detail_object_id
1313 , LX.WIN_TERR_ID
1314 , LX.txn_date
1315 FROM jtf_terr_results_GT_L4 LX
1316 , ( SELECT trans_object_id
1317 , trans_detail_object_id
1318 , WIN_TERR_ID WIN_TERR_ID
1319 FROM JTF_terr_results_GT_L4
1320 MINUS
1321 SELECT trans_object_id
1322 , trans_detail_object_id
1323 , ul_terr_id WIN_TERR_ID
1324 FROM JTF_terr_results_GT_L5 ) ILV
1325 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1326 OR
1327 LX.trans_detail_object_id IS NULL )
1328 AND LX.trans_object_id = ILV.trans_object_id
1329 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1330
1331 UNION ALL
1332
1333 SELECT LX.trans_object_id
1334 , LX.trans_detail_object_id
1335 , LX.WIN_TERR_ID
1336 , LX.txn_date
1337 FROM jtf_terr_results_GT_L5 LX
1338 , ( SELECT trans_object_id
1339 , trans_detail_object_id
1340 , WIN_TERR_ID WIN_TERR_ID
1341 FROM JTF_terr_results_GT_L5
1342 MINUS
1343 SELECT trans_object_id
1344 , trans_detail_object_id
1345 , ul_terr_id WIN_TERR_ID
1346 FROM JTF_terr_results_GT_WT ) ILV
1347 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1348 OR
1349 LX.trans_detail_object_id IS NULL )
1350 AND LX.trans_object_id = ILV.trans_object_id
1351 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1352
1353 UNION ALL
1354
1355 SELECT trans_object_id
1356 , trans_detail_object_id
1357 , WIN_TERR_ID
1358 , txn_date
1359 FROM jtf_terr_results_GT_wt
1360
1361 ) WINNERS
1362 , jtf_terr_all jta
1363 , jtf_terr_rsc_all jtr
1364 , jtf_terr_rsc_access_all jtra
1365 , jtf_qual_types_all jqta
1366 , jtf_rs_roles_b inv
1367 WHERE WINNERS.win_terr_id = jta.terr_id
1368 AND WINNERS.win_terr_id = jtr.terr_id
1369 AND jtr.end_date_active >= WINNERS.txn_date
1370 AND jtr.start_date_active <= WINNERS.txn_date
1371 AND jtr.resource_type <> 'RS_ROLE'
1372 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1373 AND jtra.trans_access_code <> 'NONE'
1374 AND jtra.access_type = jqta.name
1375 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
1376 AND jtr.role = inv.role_code(+)
1377 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
1378 AND jqta.qual_type_id = p_trans_id;
1379
1380 ELSIF (p_mode LIKE '%TERRITORY') THEN
1381 SELECT DISTINCT
1382 WINNERS.trans_object_id
1383 ,WINNERS.trans_detail_object_id
1384 ,WINNERS.txn_date
1385 ,WINNERS.win_terr_id
1386 ,jta.org_id
1387 ,jta.start_date_active
1388 ,jta.end_date_active
1389 ,jta.name
1390 ,null top_level_terr_id
1391 ,jta.absolute_rank absolute_rank
1392 ,jta.attribute_category terr_attr_category
1393 ,jta.attribute1 terr_attribute1
1394 ,jta.attribute2 terr_attribute2
1395 ,jta.attribute3 terr_attribute3
1396 ,jta.attribute4 terr_attribute4
1397 ,jta.attribute5 terr_attribute5
1398 ,jta.attribute6 terr_attribute6
1399 ,jta.attribute7 terr_attribute7
1400 ,jta.attribute8 terr_attribute8
1401 ,jta.attribute9 terr_attribute9
1402 ,jta.attribute10 terr_attribute10
1403 ,jta.attribute11 terr_attribute11
1404 ,jta.attribute12 terr_attribute12
1405 ,jta.attribute13 terr_attribute13
1406 ,jta.attribute14 terr_attribute14
1407 ,jta.attribute15 terr_attribute15
1408 BULK COLLECT INTO
1409 x_winners_rec.trans_object_id
1410 ,x_winners_rec.trans_detail_object_id
1411 ,x_winners_rec.txn_date
1412 ,x_winners_rec.terr_id
1413 ,x_winners_rec.org_id
1414 ,x_winners_rec.terr_start_date
1415 ,x_winners_rec.terr_end_date
1416 ,x_winners_rec.terr_name
1417 ,x_winners_rec.top_level_terr_id
1418 ,x_winners_rec.absolute_rank
1419 ,x_winners_rec.terr_attr_category
1420 ,x_winners_rec.terr_attribute1
1421 ,x_winners_rec.terr_attribute2
1422 ,x_winners_rec.terr_attribute3
1423 ,x_winners_rec.terr_attribute4
1424 ,x_winners_rec.terr_attribute5
1425 ,x_winners_rec.terr_attribute6
1426 ,x_winners_rec.terr_attribute7
1427 ,x_winners_rec.terr_attribute8
1428 ,x_winners_rec.terr_attribute9
1429 ,x_winners_rec.terr_attribute10
1430 ,x_winners_rec.terr_attribute11
1431 ,x_winners_rec.terr_attribute12
1432 ,x_winners_rec.terr_attribute13
1433 ,x_winners_rec.terr_attribute14
1434 ,x_winners_rec.terr_attribute15
1435 FROM
1436 (
1437 /* WINNERS ILV */
1438 SELECT LX.trans_object_id
1439 , LX.trans_detail_object_id
1440 , LX.WIN_TERR_ID
1441 , LX.txn_date
1442 FROM jtf_terr_results_GT_L1 LX
1443 , ( SELECT trans_object_id
1444 , trans_detail_object_id
1445 , WIN_TERR_ID WIN_TERR_ID
1446 FROM JTF_terr_results_GT_L1
1447 MINUS
1448 SELECT trans_object_id
1449 , trans_detail_object_id
1450 , ul_terr_id WIN_TERR_ID
1451 FROM JTF_terr_results_GT_L2 ) ILV
1452 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1453 OR
1454 LX.trans_detail_object_id IS NULL )
1455 AND LX.trans_object_id = ILV.trans_object_id
1456 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1457
1458 UNION ALL
1459
1460 SELECT LX.trans_object_id
1461 , LX.trans_detail_object_id
1462 , LX.WIN_TERR_ID
1463 , LX.txn_date
1464 FROM jtf_terr_results_GT_L2 LX
1465 , ( SELECT trans_object_id
1466 , trans_detail_object_id
1467 , WIN_TERR_ID WIN_TERR_ID
1468 FROM JTF_terr_results_GT_L2
1469 MINUS
1470 SELECT trans_object_id
1471 , trans_detail_object_id
1472 , ul_terr_id WIN_TERR_ID
1473 FROM JTF_terr_results_GT_L3 ) ILV
1474 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1475 OR
1476 LX.trans_detail_object_id IS NULL )
1477 AND LX.trans_object_id = ILV.trans_object_id
1478 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1479
1480 UNION ALL
1481
1482 SELECT LX.trans_object_id
1483 , LX.trans_detail_object_id
1484 , LX.WIN_TERR_ID
1485 , LX.txn_date
1486 FROM jtf_terr_results_GT_L3 LX
1487 , ( SELECT trans_object_id
1488 , trans_detail_object_id
1489 , WIN_TERR_ID WIN_TERR_ID
1490 FROM JTF_terr_results_GT_L3
1491 MINUS
1492 SELECT trans_object_id
1493 , trans_detail_object_id
1494 , ul_terr_id WIN_TERR_ID
1495 FROM JTF_terr_results_GT_L4 ) ILV
1496 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1497 OR
1498 LX.trans_detail_object_id IS NULL )
1499 AND LX.trans_object_id = ILV.trans_object_id
1500 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1501
1502 UNION ALL
1503
1504 SELECT LX.trans_object_id
1505 , LX.trans_detail_object_id
1506 , LX.WIN_TERR_ID
1507 , LX.txn_date
1508 FROM jtf_terr_results_GT_L4 LX
1509 , ( SELECT trans_object_id
1510 , trans_detail_object_id
1511 , WIN_TERR_ID WIN_TERR_ID
1512 FROM JTF_terr_results_GT_L4
1513 MINUS
1514 SELECT trans_object_id
1515 , trans_detail_object_id
1516 , ul_terr_id WIN_TERR_ID
1517 FROM JTF_terr_results_GT_L5 ) ILV
1518 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1519 OR
1520 LX.trans_detail_object_id IS NULL )
1521 AND LX.trans_object_id = ILV.trans_object_id
1522 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1523
1524 UNION ALL
1525
1526 SELECT LX.trans_object_id
1527 , LX.trans_detail_object_id
1528 , LX.WIN_TERR_ID
1529 , LX.txn_date
1530 FROM jtf_terr_results_GT_L5 LX
1531 , ( SELECT trans_object_id
1532 , trans_detail_object_id
1533 , WIN_TERR_ID WIN_TERR_ID
1534 FROM JTF_terr_results_GT_L5
1535 MINUS
1536 SELECT trans_object_id
1537 , trans_detail_object_id
1538 , ul_terr_id WIN_TERR_ID
1539 FROM JTF_terr_results_GT_WT ) ILV
1540 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1541 OR
1542 LX.trans_detail_object_id IS NULL )
1543 AND LX.trans_object_id = ILV.trans_object_id
1544 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1545
1546 UNION ALL
1547
1548 SELECT trans_object_id
1549 , trans_detail_object_id
1550 , WIN_TERR_ID
1551 , txn_date
1552 FROM jtf_terr_results_GT_wt
1553
1554 ) WINNERS
1555 , jtf_terr_all jta
1556 WHERE WINNERS.win_terr_id = jta.terr_id
1557 AND EXISTS (
1558 SELECT 1
1559 FROM
1560 jtf_terr_rsc_all jtr
1561 , jtf_terr_rsc_access_all jtra
1562 , jtf_qual_types_all jqta
1563 WHERE WINNERS.win_terr_id = jtr.terr_id
1564 AND jtr.end_date_active >= WINNERS.txn_date
1565 AND jtr.start_date_active <= WINNERS.txn_date
1566 AND jtr.resource_type <> 'RS_ROLE'
1567 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1568 AND jtra.trans_access_code <> 'NONE'
1569 AND jtra.access_type = jqta.name
1570 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
1571 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
1572 AND jqta.qual_type_id = p_trans_id);
1573
1574 ELSIF (p_mode LIKE '%LOOKUP') THEN
1575 SELECT /*+ PUSH_PRED(rsv) */ DISTINCT
1576 WINNERS.trans_object_id
1577 ,WINNERS.trans_detail_object_id
1578 ,WINNERS.txn_date
1579 ,WINNERS.win_terr_id
1580 ,jta.org_id
1581 ,jtr.person_id
1582 ,jta.start_date_active
1583 ,jta.end_date_active
1584 ,jtr.terr_rsc_id
1585 ,jta.name
1586 ,null top_level_terr_id
1587 ,jta.absolute_rank absolute_rank
1588 ,jtr.resource_id
1589 ,jtr.start_date_active
1590 ,jtr.end_date_active
1591 ,jtr.resource_type
1592 ,jtr.group_id
1593 ,role.role_id
1594 ,jtr.role
1595 ,decode(jtra.trans_access_code, 'FULL_ACCESS', 'Y', 'N')
1596 ,jtr.primary_contact_flag
1597 ,rsv.resource_name
1598 ,rsc.source_job_title resource_job_title
1599 ,rsc.source_phone resource_phone
1600 ,rsc.source_email resource_email
1601 ,rsc.source_mgr_name resource_mgr_name
1602 ,mgr.source_phone resource_mgr_phone
1603 ,mgr.source_email resource_mgr_email
1604 ,jta.name property1
1605 ,role.role_name property2
1606 ,jta_p.name property3
1607 ,rsc.attribute4 property4
1608 ,rsc.attribute5 property5
1609 ,rsc.attribute6 property6
1610 ,rsc.attribute7 property7
1611 ,rsc.attribute8 property8
1612 ,rsc.attribute9 property9
1613 ,rsc.attribute10 property10
1614 ,rsc.attribute11 property11
1615 ,rsc.attribute12 property12
1616 ,rsc.attribute13 property13
1617 ,rsc.attribute14 property14
1618 ,rsc.attribute15 property15
1619 ,jta.attribute_category terr_attr_category
1620 ,jta.attribute1 terr_attribute1
1621 ,jta.attribute2 terr_attribute2
1622 ,jta.attribute3 terr_attribute3
1623 ,jta.attribute4 terr_attribute4
1624 ,jta.attribute5 terr_attribute5
1625 ,jta.attribute6 terr_attribute6
1626 ,jta.attribute7 terr_attribute7
1627 ,jta.attribute8 terr_attribute8
1628 ,jta.attribute9 terr_attribute9
1629 ,jta.attribute10 terr_attribute10
1630 ,jta.attribute11 terr_attribute11
1631 ,jta.attribute12 terr_attribute12
1632 ,jta.attribute13 terr_attribute13
1633 ,jta.attribute14 terr_attribute14
1634 ,jta.attribute15 terr_attribute15
1635 ,jtr.attribute_category rsc_attr_category
1636 ,jtr.attribute1 rsc_attribute1
1637 ,jtr.attribute2 rsc_attribute2
1638 ,jtr.attribute3 rsc_attribute3
1639 ,jtr.attribute4 rsc_attribute4
1640 ,jtr.attribute5 rsc_attribute5
1641 ,jtr.attribute6 rsc_attribute6
1642 ,jtr.attribute7 rsc_attribute7
1643 ,jtr.attribute8 rsc_attribute8
1644 ,jtr.attribute9 rsc_attribute9
1645 ,jtr.attribute10 rsc_attribute10
1646 ,jtr.attribute11 rsc_attribute11
1647 ,jtr.attribute12 rsc_attribute12
1648 ,jtr.attribute13 rsc_attribute13
1649 ,jtr.attribute14 rsc_attribute14
1650 ,jtr.attribute15 rsc_attribute15
1651 BULK COLLECT INTO
1652 x_winners_rec.trans_object_id
1653 ,x_winners_rec.trans_detail_object_id
1654 ,x_winners_rec.txn_date
1655 ,x_winners_rec.terr_id
1656 ,x_winners_rec.org_id
1657 ,x_winners_rec.person_id
1658 ,x_winners_rec.terr_start_date
1659 ,x_winners_rec.terr_end_date
1660 ,x_winners_rec.terr_rsc_id
1661 ,x_winners_rec.terr_name
1662 ,x_winners_rec.top_level_terr_id
1663 ,x_winners_rec.absolute_rank
1664 ,x_winners_rec.resource_id
1665 ,x_winners_rec.rsc_start_date
1666 ,x_winners_rec.rsc_end_date
1667 ,x_winners_rec.resource_type
1668 ,x_winners_rec.group_id
1669 ,x_winners_rec.role_id
1670 ,x_winners_rec.role
1671 ,x_winners_rec.full_access_flag
1672 ,x_winners_rec.primary_contact_flag
1673 ,x_winners_rec.resource_name
1674 ,x_winners_rec.resource_job_title
1675 ,x_winners_rec.resource_phone
1676 ,x_winners_rec.resource_email
1677 ,x_winners_rec.resource_mgr_name
1678 ,x_winners_rec.resource_mgr_phone
1679 ,x_winners_rec.resource_mgr_email
1680 ,x_winners_rec.property1
1681 ,x_winners_rec.property2
1682 ,x_winners_rec.property3
1683 ,x_winners_rec.property4
1684 ,x_winners_rec.property5
1685 ,x_winners_rec.property6
1686 ,x_winners_rec.property7
1687 ,x_winners_rec.property8
1688 ,x_winners_rec.property9
1689 ,x_winners_rec.property10
1690 ,x_winners_rec.property11
1691 ,x_winners_rec.property12
1692 ,x_winners_rec.property13
1693 ,x_winners_rec.property14
1694 ,x_winners_rec.property15
1695 ,x_winners_rec.terr_attr_category
1696 ,x_winners_rec.terr_attribute1
1697 ,x_winners_rec.terr_attribute2
1698 ,x_winners_rec.terr_attribute3
1699 ,x_winners_rec.terr_attribute4
1700 ,x_winners_rec.terr_attribute5
1701 ,x_winners_rec.terr_attribute6
1702 ,x_winners_rec.terr_attribute7
1703 ,x_winners_rec.terr_attribute8
1704 ,x_winners_rec.terr_attribute9
1705 ,x_winners_rec.terr_attribute10
1706 ,x_winners_rec.terr_attribute11
1707 ,x_winners_rec.terr_attribute12
1708 ,x_winners_rec.terr_attribute13
1709 ,x_winners_rec.terr_attribute14
1710 ,x_winners_rec.terr_attribute15
1711 ,x_winners_rec.rsc_attr_category
1712 ,x_winners_rec.rsc_attribute1
1713 ,x_winners_rec.rsc_attribute2
1714 ,x_winners_rec.rsc_attribute3
1715 ,x_winners_rec.rsc_attribute4
1716 ,x_winners_rec.rsc_attribute5
1717 ,x_winners_rec.rsc_attribute6
1718 ,x_winners_rec.rsc_attribute7
1719 ,x_winners_rec.rsc_attribute8
1720 ,x_winners_rec.rsc_attribute9
1721 ,x_winners_rec.rsc_attribute10
1722 ,x_winners_rec.rsc_attribute11
1723 ,x_winners_rec.rsc_attribute12
1724 ,x_winners_rec.rsc_attribute13
1725 ,x_winners_rec.rsc_attribute14
1726 ,x_winners_rec.rsc_attribute15
1727 FROM
1728 (
1729 /* WINNERS ILV */
1730 SELECT LX.trans_object_id
1731 , LX.trans_detail_object_id
1732 , LX.WIN_TERR_ID
1733 , LX.txn_date
1734 FROM jtf_terr_results_GT_L1 LX
1735 , ( SELECT trans_object_id
1736 , trans_detail_object_id
1737 , WIN_TERR_ID WIN_TERR_ID
1738 FROM JTF_terr_results_GT_L1
1739 MINUS
1740 SELECT trans_object_id
1741 , trans_detail_object_id
1742 , ul_terr_id WIN_TERR_ID
1743 FROM JTF_terr_results_GT_L2 ) ILV
1744 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1745 OR
1746 LX.trans_detail_object_id IS NULL )
1747 AND LX.trans_object_id = ILV.trans_object_id
1748 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1749
1750 UNION ALL
1751
1752 SELECT LX.trans_object_id
1753 , LX.trans_detail_object_id
1754 , LX.WIN_TERR_ID
1755 , LX.txn_date
1756 FROM jtf_terr_results_GT_L2 LX
1757 , ( SELECT trans_object_id
1758 , trans_detail_object_id
1759 , WIN_TERR_ID WIN_TERR_ID
1760 FROM JTF_terr_results_GT_L2
1761 MINUS
1762 SELECT trans_object_id
1763 , trans_detail_object_id
1764 , ul_terr_id WIN_TERR_ID
1765 FROM JTF_terr_results_GT_L3 ) ILV
1766 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1767 OR
1768 LX.trans_detail_object_id IS NULL )
1769 AND LX.trans_object_id = ILV.trans_object_id
1770 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1771
1772 UNION ALL
1773
1774 SELECT LX.trans_object_id
1775 , LX.trans_detail_object_id
1776 , LX.WIN_TERR_ID
1777 , LX.txn_date
1778 FROM jtf_terr_results_GT_L3 LX
1779 , ( SELECT trans_object_id
1780 , trans_detail_object_id
1781 , WIN_TERR_ID WIN_TERR_ID
1782 FROM JTF_terr_results_GT_L3
1783 MINUS
1784 SELECT trans_object_id
1785 , trans_detail_object_id
1786 , ul_terr_id WIN_TERR_ID
1787 FROM JTF_terr_results_GT_L4 ) ILV
1788 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1789 OR
1790 LX.trans_detail_object_id IS NULL )
1791 AND LX.trans_object_id = ILV.trans_object_id
1792 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1793
1794 UNION ALL
1795
1796 SELECT LX.trans_object_id
1797 , LX.trans_detail_object_id
1798 , LX.WIN_TERR_ID
1799 , LX.txn_date
1800 FROM jtf_terr_results_GT_L4 LX
1801 , ( SELECT trans_object_id
1802 , trans_detail_object_id
1803 , WIN_TERR_ID WIN_TERR_ID
1804 FROM JTF_terr_results_GT_L4
1805 MINUS
1806 SELECT trans_object_id
1807 , trans_detail_object_id
1808 , ul_terr_id WIN_TERR_ID
1809 FROM JTF_terr_results_GT_L5 ) ILV
1810 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1811 OR
1812 LX.trans_detail_object_id IS NULL )
1813 AND LX.trans_object_id = ILV.trans_object_id
1814 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1815
1816 UNION ALL
1817
1818 SELECT LX.trans_object_id
1819 , LX.trans_detail_object_id
1820 , LX.WIN_TERR_ID
1821 , LX.txn_date
1822 FROM jtf_terr_results_GT_L5 LX
1823 , ( SELECT trans_object_id
1824 , trans_detail_object_id
1825 , WIN_TERR_ID WIN_TERR_ID
1826 FROM JTF_terr_results_GT_L5
1827 MINUS
1828 SELECT trans_object_id
1829 , trans_detail_object_id
1830 , ul_terr_id WIN_TERR_ID
1831 FROM JTF_terr_results_GT_WT ) ILV
1832 WHERE ( LX.trans_detail_object_id = ILV.trans_detail_object_id
1833 OR
1834 LX.trans_detail_object_id IS NULL )
1835 AND LX.trans_object_id = ILV.trans_object_id
1836 AND LX.WIN_TERR_ID = ILV.WIN_TERR_ID
1837
1838 UNION ALL
1839
1840 SELECT trans_object_id
1841 , trans_detail_object_id
1842 , WIN_TERR_ID
1843 , txn_date
1844 FROM jtf_terr_results_GT_wt
1845
1846 ) WINNERS
1847 , jtf_terr_all jta
1848 , jtf_terr_all jta_p
1849 , jtf_terr_rsc_all jtr
1850 , jtf_terr_rsc_access_all jtra
1851 , jtf_qual_types_all jqta
1852 , (select a.group_id resource_id, a.group_name resource_name, 'RS_GROUP' resource_type
1853 from jtf_rs_groups_tl a
1854 where a.language = userenv('LANG')
1855 union all
1856 select a.team_id resource_id, a.team_name resource_name, 'RS_TEAM' resource_type
1857 from jtf_rs_teams_tl a
1858 where a.language = userenv('LANG')
1859 union all
1860 select a.resource_id resource_id, a.resource_name resource_name,
1861 decode(a.category ,'EMPLOYEE', 'RS_EMPLOYEE','PARTNER', 'RS_PARTNER','SUPPLIER_CONTACT',
1862 'RS_SUPPLIER_CONTACT' , 'PARTY', 'RS_PARTY' , 'OTHER',
1863 'RS_OTHER', 'TBH', 'RS_TBH') RESOURCE_TYPE
1864 from jtf_rs_resource_extns_tl a
1865 where a.language = userenv('LANG')
1866 ) rsv
1867 , jtf_rs_resource_extns rsc
1868 , jtf_rs_resource_extns mgr
1869 , jtf_rs_roles_vl role
1870 WHERE WINNERS.win_terr_id = jta.terr_id
1871 AND WINNERS.win_terr_id = jtr.terr_id
1872 AND jtr.end_date_active >= WINNERS.txn_date
1873 AND jtr.start_date_active <= WINNERS.txn_date
1874 AND jtr.resource_type <> 'RS_ROLE'
1875 AND jtr.terr_rsc_id = jtra.terr_rsc_id
1876 AND jtra.trans_access_code <> 'NONE'
1877 AND jtra.access_type = jqta.name
1878 AND (( p_role IS NULL ) OR ( jtr.role = p_role ))
1879 AND (( p_resource_type IS NULL) OR ( jtr.resource_type = p_resource_type ))
1880 AND jqta.qual_type_id = p_trans_id
1881 AND jtr.resource_id = rsv.resource_id
1882 AND jtr.resource_type = rsv.resource_type
1883 AND decode(jtr.resource_type, 'RS_EMPLOYEE', jtr.resource_id, -999) = rsc.resource_id(+)
1884 AND rsc.source_mgr_id = mgr.source_id(+)
1885 AND rsc.category = mgr.category(+)
1886 AND jta.parent_territory_id = jta_p.terr_id
1887 AND jtr.role = role.role_code(+);
1888
1889 END IF;
1890
1891 EXCEPTION
1892 WHEN NO_DATA_FOUND THEN
1893 NULL;
1894 END;
1895
1896 IF (x_winners_rec.trans_object_id.COUNT > 0) THEN
1897 x_winners_rec.use_type := p_mode;
1898 x_winners_rec.source_id := p_source_id;
1899 x_winners_rec.trans_id := p_trans_id;
1900 END IF;
1901
1902 -- debug message
1903 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1904 FND_LOG.string(FND_LOG.LEVEL_EVENT,
1905 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.winners',
1906 'Number of winning territories : ' || x_winners_rec.trans_object_id.COUNT);
1907 END IF;
1908
1909 -- debug message
1910 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1911 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1912 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.end',
1913 'End of the procedure JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process');
1914 END IF;
1915
1916 EXCEPTION
1917
1918 WHEN FND_API.G_EXC_ERROR THEN
1919 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1920 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1921 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.other',
1922 substr(x_msg_data, 1, 4000));
1923 END IF;
1924
1925 WHEN OTHERS THEN
1926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1927 x_msg_data := SQLCODE || ' : ' || SQLERRM;
1928 x_msg_count := 1;
1929 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1930 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
1931 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.multi_level_winning_process.other',
1932 substr(x_msg_data, 1, 4000));
1933 END IF;
1934 END multi_level_winning_process;
1935
1936
1937 /* this procedure gets the winning territories of the transaction ojects */
1938 PROCEDURE process_winners
1939 ( p_source_id IN NUMBER,
1940 p_trans_id IN NUMBER,
1941 p_program_name IN VARCHAR2,
1942 p_mode IN VARCHAR2,
1943 p_role IN VARCHAR2,
1944 p_resource_type IN VARCHAR2,
1945 p_plan_start_date IN DATE DEFAULT NULL,
1946 p_plan_end_date IN DATE DEFAULT NULL,
1947 x_return_status OUT NOCOPY VARCHAR2,
1948 x_msg_count OUT NOCOPY NUMBER,
1949 x_msg_data OUT NOCOPY VARCHAR2,
1950 x_winners_rec OUT NOCOPY bulk_winners_rec_type
1951 ) AS
1952
1953 l_multi_level_winning_flag VARCHAR2(1);
1954
1955 BEGIN
1956
1957 -- debug message
1958 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1959 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
1960 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.begin',
1961 'Start of the procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
1962 END IF;
1963
1964 x_return_status := FND_API.G_RET_STS_SUCCESS;
1965
1966 /* Get the multi level winning flag corresponding */
1967 /* to the usage, transaction type and program name */
1968 SELECT tup.multi_level_winning_flag
1969 INTO l_multi_level_winning_flag
1970 FROM jty_trans_usg_pgm_details tup
1971 WHERE tup.source_id = p_source_id
1972 AND tup.trans_type_id = p_trans_id
1973 AND tup.program_name = p_program_name;
1974
1975 -- debug message
1976 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1977 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
1978 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.winning_flag',
1979 'Value of multi level winning flag : ' || l_multi_level_winning_flag);
1980 END IF;
1981
1982 IF (l_multi_level_winning_flag = 'Y') THEN
1983 multi_level_winning_process (
1984 p_source_id => p_source_id
1985 ,p_trans_id => p_trans_id
1986 ,p_mode => p_mode
1987 ,p_role => p_role
1988 ,p_resource_type => p_resource_type
1989 ,x_return_status => x_return_status
1990 ,x_msg_count => x_msg_count
1991 ,x_msg_data => x_msg_data
1992 ,x_winners_rec => x_winners_rec);
1993 ELSE
1994 single_level_winning_process (
1995 p_source_id => p_source_id
1996 ,p_trans_id => p_trans_id
1997 ,p_mode => p_mode
1998 ,p_role => p_role
1999 ,p_resource_type => p_resource_type
2000 ,p_plan_start_date => p_plan_start_date
2001 ,p_plan_end_date => p_plan_end_date
2002 ,x_return_status => x_return_status
2003 ,x_msg_count => x_msg_count
2004 ,x_msg_data => x_msg_data
2005 ,x_winners_rec => x_winners_rec);
2006 END IF;
2007
2008 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2009 -- debug message
2010 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2011 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2012 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.winning_process',
2013 'JTY_ASSIGN_REALTIME_PUB.winning_process API has failed');
2014 END IF;
2015
2016 RAISE FND_API.G_EXC_ERROR;
2017 END IF;
2018
2019 -- debug message
2020 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2021 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2022 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.end',
2023 'End of the procedure JTY_ASSIGN_REALTIME_PUB.process_winners');
2024 END IF;
2025
2026 EXCEPTION
2027
2028 WHEN FND_API.G_EXC_ERROR THEN
2029 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2030 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2031 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.g_exc_error',
2032 x_msg_data);
2033 END IF;
2034
2035 WHEN NO_DATA_FOUND THEN
2036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2037 x_msg_data := 'No record in the table jty_trans_usg_pgm_details for the usage : ' || p_source_id ||
2038 ', transaction type : ' || p_trans_id || ' and program name : ' || p_program_name;
2039 x_msg_count := 1;
2040 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2041 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2042 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.no_data_found_error',
2043 x_msg_data);
2044 END IF;
2045
2046 WHEN OTHERS THEN
2047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2048 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2049 x_msg_count := 1;
2050 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2051 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2052 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.process_winners.other',
2053 substr(x_msg_data, 1, 4000));
2054 END IF;
2055 End process_winners;
2056
2057 /* product team should call this procedure, which first find out the matching */
2058 /* territories and then the winning territories from the matching set */
2059 PROCEDURE get_winners
2060 ( p_api_version_number IN NUMBER,
2061 p_init_msg_list IN VARCHAR2,
2062 p_source_id IN NUMBER,
2063 p_trans_id IN NUMBER,
2064 p_mode IN VARCHAR2,
2065 p_param_passing_mechanism IN VARCHAR2,
2066 p_program_name IN VARCHAR2,
2067 p_trans_rec IN bulk_trans_id_type,
2068 p_name_value_pair IN bulk_name_value_pair_type,
2069 p_role IN VARCHAR2,
2070 p_resource_type IN VARCHAR2,
2071 x_return_status OUT NOCOPY VARCHAR2,
2072 x_msg_count OUT NOCOPY NUMBER,
2073 x_msg_data OUT NOCOPY VARCHAR2,
2074 x_winners_rec OUT NOCOPY bulk_winners_rec_type
2075 ) AS
2076
2077 l_api_name CONSTANT VARCHAR2(30) := 'get_winners';
2078 l_api_version_number CONSTANT NUMBER := 1.0;
2079
2080 NO_TXN_SQL_ERROR EXCEPTION;
2081 NO_TRANS_TABLE_ERROR EXCEPTION;
2082 INVALID_PRM_PSS_MCH EXCEPTION;
2083
2084 l_real_time_trans_table_name VARCHAR2(30);
2085 l_delete_stmt VARCHAR2(100);
2086 l_debug_stmt VARCHAR2(100);
2087 l_trans_no_of_records NUMBER;
2088 l_insert_stmt VARCHAR2(32767);
2089 l_plsql_block VARCHAR2(32767);
2090 l_trans_rec_records NUMBER;
2091 l_nvp_records NUMBER;
2092 l_sysdate DATE;
2093
2094 BEGIN
2095
2096 -- debug message
2097 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2098 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2099 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.begin',
2100 'Start of the procedure JTY_ASSIGN_REALTIME_PUB.get_winners');
2101 END IF;
2102
2103 -- Standard call to check for call compatibility.
2104 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2105 p_api_version_number,
2106 l_api_name,
2107 G_PKG_NAME) THEN
2108
2109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2110 END IF;
2111
2112 -- Initialize message list if p_init_msg_list is set to TRUE.
2113 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2114 FND_MSG_PUB.initialize;
2115 END IF;
2116
2117 x_return_status := FND_API.G_RET_STS_SUCCESS;
2118 l_sysdate := SYSDATE;
2119 l_trans_rec_records := 0;
2120 l_nvp_records := 0;
2121
2122
2123 /* In case of pass by reference, get the number of transaction objects */
2124 /* In case of pass by value, get the number of attributes of the transaction object */
2125 IF (p_param_passing_mechanism = 'PBR') THEN
2126 l_trans_rec_records := p_trans_rec.trans_object_id1.COUNT();
2127 ELSE
2128 l_nvp_records := p_name_value_pair.attribute_name.COUNT();
2129 END IF;
2130
2131 -- debug message
2132 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2133 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2134 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.parameters',
2135 'Source : ' || p_source_id || ' Transaction Type : ' || p_trans_id || ' Program Name : ' || p_program_name ||
2136 ' Mode : ' || p_mode || ' Parameter passing Mechanism : ' || p_param_passing_mechanism ||
2137 ' Role : ' || p_role || ' Resource Type : ' || p_resource_type);
2138
2139 IF (p_mode = 'PBR') THEN
2140 IF (l_trans_rec_records > 0) THEN
2141 FOR i IN p_trans_rec.trans_object_id1.FIRST .. p_trans_rec.trans_object_id1.LAST LOOP
2142 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2143 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.parameters',
2144 'PK1 : ' || p_trans_rec.trans_object_id1(i) || ' PK2 : ' || p_trans_rec.trans_object_id2(i) ||
2145 ' PK3 : ' || p_trans_rec.trans_object_id3(i) || ' PK4 : ' || p_trans_rec.trans_object_id4(i) ||
2146 ' PK5 : ' || p_trans_rec.trans_object_id5(i) || ' Txn Date : ' || p_trans_rec.txn_date(i));
2147 END LOOP;
2148 ELSE
2149 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2150 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.parameters',
2151 'No transaction object passed');
2152 END IF; /* end IF (l_trans_rec_records > 0) */
2153 ELSE
2154 IF (l_nvp_records > 0) THEN
2155 FOR i IN p_name_value_pair.attribute_name.FIRST .. p_name_value_pair.attribute_name.LAST LOOP
2156 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2157 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.parameters',
2158 'Attribute Name : ' || p_name_value_pair.attribute_name(i) || ' NumberValue : ' || p_name_value_pair.num_value(i)
2159 || ' Char Value : ' || p_name_value_pair.char_value(i) || ' Date Value : ' || p_name_value_pair.date_value(i));
2160 END LOOP;
2161 ELSE
2162 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2163 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.parameters',
2164 'No attribute name value pair passed');
2165 END IF; /* end IF (l_nvp_records > 0) */
2166 END IF; /* end IF (p_mode = 'PBR') */
2167 END IF; /* end IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) */
2168
2169 /* Get the real time trans table corresponding */
2170 /* to the usage, transaction type and program name */
2171 BEGIN
2172 SELECT tup.real_time_trans_table_name
2173 INTO l_real_time_trans_table_name
2174 FROM jty_trans_usg_pgm_details tup
2175 WHERE tup.source_id = p_source_id
2176 AND tup.trans_type_id = p_trans_id
2177 AND tup.program_name = p_program_name;
2178
2179 IF (l_real_time_trans_table_name IS NULL) THEN
2180 RAISE NO_TRANS_TABLE_ERROR;
2181 END IF;
2182 EXCEPTION
2183 WHEN NO_DATA_FOUND THEN
2184 RAISE NO_TRANS_TABLE_ERROR;
2185
2186 WHEN OTHERS THEN
2187 RAISE;
2188 END;
2189
2190 -- debug message
2191 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2192 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2193 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.real_time_trans_table',
2194 'Real Time Trans Table for the usage, transaction type and program: ' || l_real_time_trans_table_name);
2195 END IF;
2196
2197 /* Truncate the real time trans table, a global temporary table */
2198 /* Delete stmt is used instead of truncate as # of rows will be small */
2199 l_delete_stmt := 'DELETE FROM ' || l_real_time_trans_table_name;
2200 EXECUTE IMMEDIATE l_delete_stmt;
2201
2202 /* Code to insert the transaction objects into TRANS table */
2203 IF (p_param_passing_mechanism = 'PBR') THEN -- if parameter passing mechanism is pass by reference
2204 BEGIN
2205 SELECT tups.real_time_insert
2206 INTO l_insert_stmt
2207 FROM jty_trans_usg_pgm_sql tups
2208 WHERE tups.source_id = p_source_id
2209 AND tups.trans_type_id = p_trans_id
2210 AND tups.program_name = p_program_name
2211 AND tups.enabled_flag = 'Y';
2212
2213 IF (l_insert_stmt IS NULL) THEN
2214 RAISE NO_TXN_SQL_ERROR;
2215 END IF;
2216
2217 -- debug message
2218 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2219 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2220 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.transaction_type_sql',
2221 substr(l_insert_stmt, 1, 4000));
2222 END IF;
2223
2224 /* Dynammic pl/sql block to bind the PK values and execute the insert statement */
2225 l_plsql_block :=
2226 'declare ' ||
2227 'l_trans_object_id1 number; ' ||
2228 'l_trans_object_id2 number; ' ||
2229 'l_trans_object_id3 number; ' ||
2230 'l_trans_object_id4 number; ' ||
2231 'l_trans_object_id5 number; ' ||
2232 'l_txn_date date; ' ||
2233 'begin ' ||
2234 'l_trans_object_id1 := :1; ' ||
2235 'l_trans_object_id2 := :2; ' ||
2236 'l_trans_object_id3 := :3; ' ||
2237 'l_trans_object_id4 := :4; ' ||
2238 'l_trans_object_id5 := :5; ' ||
2239 'l_txn_date := :6; ' ||
2240
2241 l_insert_stmt ||
2242
2243 'exception ' ||
2244 'when others then raise; ' ||
2245 'end; ';
2246
2247 IF (p_mode LIKE 'REAL TIME%') THEN
2248 FOR i IN p_trans_rec.trans_object_id1.FIRST .. p_trans_rec.trans_object_id1.LAST LOOP
2249 EXECUTE IMMEDIATE l_plsql_block USING
2250 p_trans_rec.trans_object_id1(i),
2251 p_trans_rec.trans_object_id2(i),
2252 p_trans_rec.trans_object_id3(i),
2253 p_trans_rec.trans_object_id4(i),
2254 p_trans_rec.trans_object_id5(i),
2255 l_sysdate;
2256 END LOOP;
2257 ELSIF (p_mode LIKE 'DATE EFFECTIVE%') THEN
2258 FOR i IN p_trans_rec.trans_object_id1.FIRST .. p_trans_rec.trans_object_id1.LAST LOOP
2259 EXECUTE IMMEDIATE l_plsql_block USING
2260 p_trans_rec.trans_object_id1(i),
2261 p_trans_rec.trans_object_id2(i),
2262 p_trans_rec.trans_object_id3(i),
2263 p_trans_rec.trans_object_id4(i),
2264 p_trans_rec.trans_object_id5(i),
2265 p_trans_rec.txn_date(i);
2266 END LOOP;
2267 END IF;
2268
2269 -- debug message
2270 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2271 l_debug_stmt := 'SELECT COUNT(*) FROM ' || l_real_time_trans_table_name;
2272 EXECUTE IMMEDIATE l_debug_stmt INTO l_trans_no_of_records;
2273
2274 FND_LOG.string(FND_LOG.LEVEL_EVENT,
2275 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.insert_trans_data_pbr',
2276 'Number of records inserted into TRANS table : ' || l_trans_no_of_records);
2277 END IF;
2278
2279
2280 EXCEPTION
2281 WHEN NO_DATA_FOUND THEN
2282 RAISE NO_TXN_SQL_ERROR;
2283
2284 WHEN OTHERS THEN
2285 RAISE;
2286 END;
2287
2288 ELSIF (p_param_passing_mechanism = 'PBV') THEN -- if parameter passing mechanism is pass by value
2289 FORALL i IN p_name_value_pair.attribute_name.FIRST .. p_name_value_pair.attribute_name.LAST
2290 INSERT INTO jty_terr_nvp_trans_gt (
2291 attribute_name
2292 ,char_value
2293 ,num_value
2294 ,date_value )
2295 VALUES (
2296 p_name_value_pair.attribute_name(i)
2297 ,p_name_value_pair.char_value(i)
2298 ,p_name_value_pair.num_value(i)
2299 ,p_name_value_pair.date_value(i));
2300
2301 -- debug message
2302 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2303 SELECT COUNT(*)
2304 INTO l_trans_no_of_records
2305 FROM jty_terr_nvp_trans_gt;
2306
2307 FND_LOG.string(FND_LOG.LEVEL_EVENT,
2308 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.insert_trans_data_pbv',
2309 'Number of records inserted into TRANS table : ' || l_trans_no_of_records);
2310 END IF;
2311
2312 ELSE
2313 RAISE INVALID_PRM_PSS_MCH;
2314 END IF; -- end IF (p_param_passing_mechanism = 'PBR')
2315
2316 /* get the matching territories corresponding to the transaction objects */
2317 process_match (
2318 p_source_id => p_source_id,
2319 p_trans_id => p_trans_id,
2320 p_mode => p_mode,
2321 p_program_name => p_program_name,
2322 x_return_status => x_return_status,
2323 x_msg_count => x_msg_count,
2324 x_msg_data => x_msg_data
2325 );
2326
2327 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2328 -- debug message
2329 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2330 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2331 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.process_match',
2332 'JTY_ASSIGN_REALTIME_PUB.process_match API has failed');
2333 END IF;
2334
2335 RAISE FND_API.G_EXC_ERROR;
2336 END IF;
2337
2338 -- debug message
2339 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2340 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2341 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.process_match',
2342 'Finish calling procedure process_match');
2343 END IF;
2344
2345 /* get the winning territories/resources corresponding to the transaction objects */
2346 process_winners (
2347 p_source_id => p_source_id,
2348 p_trans_id => p_trans_id,
2349 p_program_name => p_program_name,
2350 p_mode => p_mode,
2351 p_role => p_role,
2352 p_resource_type => p_resource_type,
2353 x_return_status => x_return_status,
2354 x_msg_count => x_msg_count,
2355 x_msg_data => x_msg_data,
2356 x_winners_rec => x_winners_rec
2357 );
2358
2359 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2360 -- debug message
2361 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2362 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2363 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.process_winners',
2364 'JTY_ASSIGN_REALTIME_PUB.process_winners API has failed');
2365 END IF;
2366
2367 RAISE FND_API.G_EXC_ERROR;
2368 END IF;
2369
2370 -- debug message
2371 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2372 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,
2373 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.process_winners',
2374 'Finish calling procedure process_winners');
2375 END IF;
2376
2377 -- debug message
2378 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2379 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,
2380 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.end',
2381 'End of the procedure JTY_ASSIGN_REALTIME_PUB.get_winners');
2382 END IF;
2383
2384 EXCEPTION
2385 WHEN FND_API.G_EXC_ERROR THEN
2386 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2387 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2388 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.g_exc_error',
2389 x_msg_data);
2390 END IF;
2391
2392
2393 WHEN INVALID_PRM_PSS_MCH THEN
2394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2395 x_msg_data := 'Invalid Parameter Passing Mechanism : valid values are PBR and PBV';
2396 x_msg_count := 1;
2397 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2398 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2399 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.invalid_prm_pss_mch',
2400 x_msg_data);
2401 END IF;
2402
2403
2404 WHEN NO_TXN_SQL_ERROR THEN
2405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2406 x_msg_data := 'Invalid real time transaction SQL for usage : ' || p_source_id ||
2407 ', transaction type : ' || p_trans_id || ' and program name : ' || p_program_name;
2408 x_msg_count := 1;
2409 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2410 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2411 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.no_txn_sql_error',
2412 x_msg_data);
2413 END IF;
2414
2415 WHEN NO_TRANS_TABLE_ERROR THEN
2416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2417 x_msg_data := 'Invalid real time trans table for usage : ' || p_source_id ||
2418 ', transaction type : ' || p_trans_id || ' and program name : ' || p_program_name;
2419 x_msg_count := 1;
2420 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2421 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2422 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.no_trans_table_error',
2423 x_msg_data);
2424 END IF;
2425
2426 WHEN OTHERS THEN
2427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2428 x_msg_data := SQLCODE || ' : ' || SQLERRM;
2429 x_msg_count := 1;
2430 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2431 FND_LOG.string(FND_LOG.LEVEL_EXCEPTION,
2432 'jtf.plsql.JTY_ASSIGN_REALTIME_PUB.get_winners.other',
2433 substr(x_msg_data, 1, 4000));
2434 END IF;
2435
2436 End get_winners;
2437
2438 END JTY_ASSIGN_REALTIME_PUB;