[Home] [Help]
PACKAGE BODY: APPS.CN_PROC_BATCHES_PKG
Source
1 PACKAGE BODY cn_proc_batches_pkg AS
2 /* $Header: cnsybatb.pls 120.26 2011/09/24 16:32:03 rnagired ship $ */
3
4 /*
5 Date Name Description
6 =============================================================================
7 19-MAY-95 P Cook Created
8 08-JUN-95 P Cook Only pass process_type 'CALCULATION' to message_pkg
9 - previously used TRX_MAIN.., TRX_BATCH.., TRX_RUN..
10 26-JUN-95 P Cook Replaced 'payee' process with rollup and populate
11 05-JUL-95 P Cook Removed call to cn_periods_pkg.set_processing_status.
12 Call is now made by the cn_srp_periods_pkg.raise_status
13 14-JUL-95 P Cook Raise exception with mesg if class pkg does not exist
14 Fixed populate batch proc to prevent duplicate keys
15 15-JUL-95 P Cook Modified calls to begin batch
16 01-AUG-95 P Cook Added populate_calcsub_batches
17 10-AUG-95 P Cook Modified populate_calcsub_batches to populate
18 cn_process_batches with all salesrep/periods that are
19 impacted by running calc on a salesrep.
20 11-AUG-95 P Cook Revised calcsub to populate correct impacted salesreps
21 14-AUG-95 P Cook Replace hardcoded trx batch size with system_batch_size
22 18-AUG-95 P Cook Handle no_data_found in flood routine excep handlers
23 30-AUG-95 P Cook Replaced 'raise app_exception' with 'raise' in
24 'when others' to show the actual exception being raised
25 31-AUG-95 P Cook Pass process_audit_id to cn_message_pkg.end_batch
26 so that it can fill in the completion timestamp.
27 Changed the 'no transactions to process' message from
28 debug to translated.
29 26-OCT-95 P Cook Bug: 300974. Modified 'main' to give nice message if
30 cn_classification pkg body doesn't exist. Added output
31 of status so that calcsub form can give a succ/fail
32 message depending on basic validation.
33 21-NOV-95 P Cook Added who column support
34 19-FEB-96 P Cook Bug:335401. Improved error handling and no longer
35 re-raise server errors in calc submission form.
36 08-MAR-96 P Cook Bug:346965. Modified procedure populate_calcsub_batches
37 to identify impact on parents when a subordinate is
38 calculated (instead of identifying impacted
39 subordinates when calcing a parent).
40 12-MAR-96 P COOK Bug: 348351. Modified calcsub to differentiate between
41 impacted reps that need calc and thos that need revert.
42
43 11-Feb-98 Achung reference CLIENT_INFO need to use SUBSTRB
44
45 19-Apr-99 H Chen change parameter type x_num to varchar2 in
46 get_person_name_num
47 27-Sep-00 M Blum added check of pay group assignments before allowing
48 calculation
49 17-Oct-00 M Blum added calculation concurrent program
50 13-Sep-07 achanda fix bug # 6376880
51
52 Purpose
53 Sequentially or concurrently Process batches of transactions.
54
55 Notes
56
57 1. This program controls all commits during calculation. The calculation
58 routine does not and should not commit.
59
60 2. i. The physical batch cannot be smaller than a complete srp_period.
61 i.e phys batch1 and physbatch2 cannot both refer to srp_period1.
62 ii.The system's rollback segment must be large enough to deal with the
63 maximum number of commission lines in an srp_period. See Issues.
64
65 3. Currently the processor waits for a lock on srp periods.
66 This could be improved with some kind of timeout and retry mechanism.
67
68 Potential Issues
69 1. Calling this program concurrently spawns many child processes ('runner')
70 where each runner will process an individual physical batch.
71 Each batch may reference more than one srp period and therefore more than
72 one period.
73 To maintain consistency between the srp_period and cn_period processing
74 statii the child process commits after setting both the srp and cn period
75 in each physical batch.
76 Each child process may well be trying to set the cn_period status while
77 another child process is doing the same. This contention will slow down
78 concurrent execution but is preferable to locking the period up front
79 which would prevent other children who referenced the same cn_period
80 from executing.
81
82 2. Commission lines are processed in groups of physical batches. After each
83 physical batch is processed a commit is made.
84 A physical batch represents the srp periods that can be processed before a
85 commit is required. A fuller explanation is that the physical batch
86 represents the srp periods whose total number of commission lines can be
87 processed before a commit is required (to prevent a rollback segment error).
88 Since a physical batch must represent at least on srp period, the number of
89 commission lines that belong to each srp period must be handled by the
90 rollback segment.
91 When the system is setup the rollback segment must be large enough to
92 deal with the max transactions expected in each srp period. Problem is that
93 this is fairly difficult to estimate.
94 Enhancements to this process could be to supplement or ditch the batch
95 mechanism with a simple counting of commission lines processed. Obviously
96 this means extensive modifications to all routines that are called
97 (payee, calc etc) to remove any set based processing.
98
99 3. When running concurrently the spawned runners will be executing in parallel
100 thus using up the same rollback segment. So instead of requiring a rollback
101 segment large enough to handle the largest phys batch you need one to
102 handle the total number of comm lines in largest physical batches that may
103 be processed in parallel. Parallelism is governed by the conc mgr setup.
104
105
106 Baic Program Structure
107 ======================
108
109 Main (Public)
110 |
111 Processor
112 |
113 assign
114 |
115 ----------------
116 online=Y online=N
117 | |
118 seq_dispatch conc_dispatch (concurrent prog)
119 | |
120 | |
121 | |
122 | conc_submit
123 | |
124 ----------------
125 |
126 runner (concurrent prog)
127
128
129 */
130
131 /* ----------------------------------------------------------------------------
132 | Global Variables |
133 ----------------------------------------------------------------------------*/
134 g_logical_process VARCHAR2(30);
135 g_logical_batch_id NUMBER;
136 g_org_id NUMBER;
137 g_parent_proc_audit_id NUMBER := NULL;
138 g_unreverted CONSTANT VARCHAR2(30) := 'UNREVERTED';
139 g_reverted CONSTANT VARCHAR2(30) := 'REVERTED';
140 g_unclassified CONSTANT VARCHAR2(30) := 'UNCLASSIFIED';
141 g_classified CONSTANT VARCHAR2(30) := 'CLASSIFIED';
142 g_rolled_up CONSTANT VARCHAR2(30) := 'ROLLED_UP';
143 g_populated CONSTANT VARCHAR2(30) := 'POPULATED';
144 g_calculated CONSTANT VARCHAR2(30) := 'CALCULATED';
145 g_revert CONSTANT VARCHAR2(30) := 'REVERT';
146 g_collection CONSTANT VARCHAR2(30) := 'COLLECTION';
147 g_load CONSTANT VARCHAR2(30) := 'LOAD';
148 g_post CONSTANT VARCHAR2(30) := 'POST';
149 g_classification CONSTANT VARCHAR2(30) := 'CLASSIFICATION';
150 g_calculation CONSTANT VARCHAR2(30) := 'CALCULATION';
151 g_rollup CONSTANT VARCHAR2(30) := 'ROLLUP';
152 g_population CONSTANT VARCHAR2(30) := 'POPULATION';
153 g_creation_date DATE := SYSDATE;
154 g_created_by NUMBER := fnd_global.user_id;
155 g_calc_type VARCHAR2(30);
156 /* ----------------------------------------------------------------------------
157 | Pragmas |
158 ----------------------------------------------------------------------------*/
159 ABORT EXCEPTION;
160 program_unit_missing EXCEPTION;
161 no_comm_lines EXCEPTION;
162 conc_fail EXCEPTION;
163 no_one_with_complete_plan EXCEPTION;
164 api_call_failed EXCEPTION;
165 PRAGMA EXCEPTION_INIT(program_unit_missing, -6508);
166
167 /* ----------------------------------------------------------------------------
168 | Global Cursor |
169 ----------------------------------------------------------------------------*/-- Get individual physical batch id's for the entire logical batch
170 -- no point joining to periods for the status because it may have
171 -- changed by the time we come to process the records
172 CURSOR physical_batches IS
173 SELECT a.physical_batch_id
174 FROM cn_process_batches_all a
175 , (SELECT MAX(physical_batch_id) physical_batch_id
176 , SUM(sales_lines_total) + 1 rec_total
177 FROM cn_process_batches_all
178 WHERE logical_batch_id = g_logical_batch_id) b
179 WHERE a.logical_batch_id = g_logical_batch_id AND a.status_code = 'IN_USE'
180 GROUP BY a.physical_batch_id
181 ORDER BY SUM(DECODE(a.physical_batch_id, b.physical_batch_id, b.rec_total, a.sales_lines_total)) DESC;
182
183 CURSOR physical_batches2 IS
184 SELECT a.physical_batch_id
185 FROM cn_process_batches_all a
186 , (SELECT MAX(physical_batch_id) physical_batch_id
187 , SUM(commission_headers_count) + 1 rec_total
188 FROM cn_process_batches_all
189 WHERE logical_batch_id = g_logical_batch_id) b
190 WHERE a.logical_batch_id = g_logical_batch_id AND a.status_code = 'IN_USE'
191 GROUP BY a.physical_batch_id
192 ORDER BY SUM(
193 DECODE(
194 a.physical_batch_id
195 , b.physical_batch_id, b.rec_total
196 , commission_headers_count
197 )
198 ) DESC;
199
200 physical_rec physical_batches%ROWTYPE;
201
202 /* ---------------------------------------------------------------------------
203 | Forward Declaration for Bonus Calc |
204 ----------------------------------------------------------------------------*/
205 PROCEDURE populate_bonus_process_batch(p_calc_sub_batch_id NUMBER);
206
207 FUNCTION check_active_plan_assign(
208 p_salesrep_id NUMBER
209 , p_start_date DATE
210 , p_end_date DATE
211 , p_interval_type_id NUMBER
212 , p_calc_sub_batch_id NUMBER
213 , p_org_id NUMBER
214 )
215 RETURN BOOLEAN;
216
217 /* ----------------------------------------------------------------------------
218 | Private Routines |
219 ----------------------------------------------------------------------------*/-- Procedure Name
220 -- Flood_rev_classes
221 -- Purpose
222 -- Flood the rev class hierarchy with any missing 1:1 nodes for those
223 -- salesreps in the logical batch of transactions
224 PROCEDURE flood_rev_classes IS
225 CURSOR periods IS
226 SELECT start_date
227 , end_date
228 FROM cn_calc_submission_batches_all
229 WHERE logical_batch_id = g_logical_batch_id;
230
231 x_dim_hierarchy NUMBER;
232
233 CURSOR l_dim_hierarchy_csr(l_start_date DATE, l_end_date DATE) IS
234 SELECT dim_hierarchy_id
235 FROM cn_dim_hierarchies_all
236 WHERE header_dim_hierarchy_id = (SELECT rev_class_hierarchy_id
237 FROM cn_repositories_all
238 WHERE org_id = g_org_id)
239 AND org_id = g_org_id
240 AND (
241 (start_date < l_start_date AND(end_date IS NULL OR l_start_date <= end_date))
242 OR (start_date BETWEEN l_start_date AND l_end_date)
243 );
244
245 l_user_id NUMBER(15) := fnd_global.user_id;
246 l_resp_id NUMBER(15) := fnd_global.resp_id;
247 l_login_id NUMBER(15) := fnd_global.login_id;
248 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
249 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
250 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
251 BEGIN
252 FOR per IN periods LOOP
253 FOR dim IN l_dim_hierarchy_csr(per.start_date, per.end_date) LOOP
254 x_dim_hierarchy := dim.dim_hierarchy_id;
255
256 DECLARE
257 CURSOR classes IS
258 SELECT revenue_class_id
259 , NAME
260 FROM cn_revenue_classes_all rc
261 WHERE org_id = g_org_id
262 AND NOT EXISTS(
263 SELECT 1
264 FROM cn_dim_explosion_all
265 WHERE dim_hierarchy_id = x_dim_hierarchy
266 AND value_external_id = rc.revenue_class_id
267 AND ancestor_external_id = rc.revenue_class_id);
268 BEGIN
269 FOR CLASS IN classes LOOP
270 UPDATE cn_hierarchy_nodes_all
271 SET external_id = CLASS.revenue_class_id
272 , last_update_date = SYSDATE
273 , last_update_login = l_login_id
274 , last_updated_by = l_user_id
275 , request_id = l_conc_request_id
276 , program_application_id = l_prog_appl_id
277 , program_id = l_conc_prog_id
278 , program_update_date = SYSDATE
279 WHERE external_id IS NULL
280 AND NAME = CLASS.NAME
281 AND dim_hierarchy_id = x_dim_hierarchy
282 AND org_id = g_org_id;
283
284 INSERT INTO cn_hierarchy_nodes_all
285 (
286 dim_hierarchy_id
287 , value_id
288 , external_id
289 , NAME
290 , ref_count
291 , hierarchy_level
292 , creation_date
293 , created_by
294 , last_update_date
295 , last_updated_by
296 , last_update_login
297 , request_id
298 , program_application_id
299 , program_id
300 , program_update_date
301 , org_id
302 )
303 (SELECT x_dim_hierarchy
304 , cn_hierarchy_nodes_s.NEXTVAL
305 , CLASS.revenue_class_id
306 , CLASS.NAME
307 , 0
308 , 1
309 , SYSDATE
310 , l_user_id
311 , SYSDATE
312 , l_user_id
313 , l_login_id
314 , l_conc_request_id
315 , l_prog_appl_id
316 , l_conc_prog_id
317 , SYSDATE
318 , g_org_id
319 FROM SYS.DUAL
320 WHERE NOT EXISTS(
321 SELECT 1
322 FROM cn_hierarchy_nodes_all
323 WHERE dim_hierarchy_id = x_dim_hierarchy
324 AND external_id = CLASS.revenue_class_id
325 AND org_id = g_org_id));
326
327 UPDATE cn_dim_explosion_all
328 SET ancestor_external_id = CLASS.revenue_class_id
329 WHERE ancestor_id IN(
330 SELECT value_id
331 FROM cn_hierarchy_nodes_all
332 WHERE dim_hierarchy_id = x_dim_hierarchy
333 AND external_id = CLASS.revenue_class_id
334 AND org_id = g_org_id)
335 AND dim_hierarchy_id = x_dim_hierarchy
336 AND ancestor_external_id IS NULL
337 AND org_id = g_org_id;
338
339 UPDATE cn_dim_explosion_all
340 SET value_external_id = CLASS.revenue_class_id
341 WHERE value_id IN(
342 SELECT value_id
343 FROM cn_hierarchy_nodes_all
344 WHERE dim_hierarchy_id = x_dim_hierarchy
345 AND external_id = CLASS.revenue_class_id
346 AND org_id = g_org_id)
347 AND dim_hierarchy_id = x_dim_hierarchy
348 AND value_external_id IS NULL
349 AND org_id = g_org_id;
350 END LOOP;
351 END;
352 END LOOP; -- end of dim_hierarchy_id
353 END LOOP; -- end of period
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 NULL;
357 -- prevents no data found when a process batch period does not exist in
358 -- the rev class hierarchy
359 END flood_rev_classes;
360
361 -- Procedure Name
362 -- Process_status
363 -- Purpose
364 -- For a given physical process get the status of the periods that can be
365 -- processed and the status that these periods will be set to when the
366 -- process completes.
367 PROCEDURE process_status(
368 x_physical_process IN VARCHAR2
369 , x_curr_status OUT NOCOPY VARCHAR2
370 , x_new_status OUT NOCOPY VARCHAR2
371 ) IS
372 newst VARCHAR2(30);
373 currst VARCHAR2(30);
374 BEGIN
375 IF x_physical_process = g_collection THEN
376 -- If we've collected new records into the period then the period
377 -- must become unclassified regardless of its current status
378 currst := NULL;
379 newst := g_unclassified;
380 ELSIF x_physical_process = g_revert THEN
381 currst := g_unreverted;
382 newst := g_reverted;
383 ELSIF x_physical_process = g_classification THEN
384 currst := g_reverted;
385 newst := g_classified;
386 ELSIF x_physical_process = g_rollup THEN
387 currst := g_classified;
388 newst := g_rolled_up;
389 ELSIF x_physical_process = g_population THEN
390 currst := g_rolled_up;
391 newst := g_populated;
392 ELSIF x_physical_process = g_calculation THEN
393 currst := g_populated;
394 newst := g_calculated;
395 ELSIF x_physical_process = g_load THEN
396 currst := g_load;
397 newst := g_load;
398 ELSIF x_physical_process = g_post THEN
399 currst := g_post;
400 newst := g_post;
401 ELSE
402 cn_message_pkg.DEBUG('Invalid process code: ' || x_physical_process);
403 fnd_file.put_line(fnd_file.LOG, 'Invalid process code: ' || x_physical_process);
404 RAISE ABORT;
405 END IF;
406
407 x_curr_status := currst;
408 x_new_status := newst;
409 END process_status;
410
411 -- Procedure Name
412 -- next_process
413 -- Purpose
414 -- For given logical and physical processes get the name of the
415 -- subsequent process that must be run.
416 --
417 PROCEDURE next_process(x_physical_process IN OUT NOCOPY VARCHAR2) IS
418 newpr VARCHAR2(30);
419 BEGIN
420 IF g_calc_type = 'BONUS' THEN
421 IF x_physical_process IS NULL THEN
422 x_physical_process := g_revert;
423 ELSIF x_physical_process = g_revert THEN
424 x_physical_process := g_calculation; --g_logical_process;
425 ELSIF x_physical_process = g_calculation THEN
426 x_physical_process := NULL;
427 ELSE
428 cn_message_pkg.DEBUG(
429 'Invalid process code: '
430 || x_physical_process
431 || ' (logical process: '
432 || g_logical_process
433 || ')'
434 );
435 fnd_file.put_line(
436 fnd_file.LOG
437 , 'Invalid process code: '
438 || x_physical_process
439 || ' (logical process: '
440 || g_logical_process
441 || ')'
442 );
443 RAISE ABORT;
444 END IF;
445
446 RETURN;
447 END IF;
448
449 IF g_logical_process = g_collection THEN
450 IF x_physical_process IS NULL THEN
451 newpr := g_collection;
452 ELSIF x_physical_process = g_collection THEN
453 newpr := NULL;
454 ELSE
455 newpr := g_collection;
456 END IF;
457 ELSIF g_logical_process = g_classification THEN
458 IF x_physical_process IS NULL THEN
459 newpr := g_classification;
460 ELSIF x_physical_process = g_classification THEN
461 newpr := NULL;
462 ELSE
463 cn_message_pkg.DEBUG(
464 'Invalid process code: '
465 || x_physical_process
466 || ' (logical process: '
467 || g_logical_process
468 || ')'
469 );
470 fnd_file.put_line(
471 fnd_file.LOG
472 , 'Invalid process code: '
473 || x_physical_process
474 || ' (logical process: '
475 || g_logical_process
476 || ')'
477 );
478 RAISE ABORT;
479 END IF;
480 ELSIF g_logical_process = g_rollup THEN
481 IF x_physical_process IS NULL THEN
482 newpr := g_classification;
483 ELSIF x_physical_process = g_classification THEN
484 newpr := g_rollup;
485 ELSIF x_physical_process = g_rollup THEN
486 newpr := NULL;
487 ELSE
488 cn_message_pkg.DEBUG(
489 'Invalid process code: '
490 || x_physical_process
491 || ' (logical process: '
492 || g_logical_process
493 || ')'
494 );
495 fnd_file.put_line(
496 fnd_file.LOG
497 , 'Invalid process code: '
498 || x_physical_process
499 || ' (logical process: '
500 || g_logical_process
501 || ')'
502 );
503 RAISE ABORT;
504 END IF;
505 ELSIF g_logical_process = g_population THEN
506 IF x_physical_process IS NULL THEN
507 newpr := g_classification;
508 ELSIF x_physical_process = g_classification THEN
509 newpr := g_rollup;
510 ELSIF x_physical_process = g_rollup THEN
511 newpr := g_population;
512 ELSIF x_physical_process = g_population THEN
513 newpr := NULL;
514 ELSE
515 cn_message_pkg.DEBUG(
516 'Invalid process code: '
517 || x_physical_process
518 || ' (logical process: '
519 || g_logical_process
520 || ')'
521 );
522 fnd_file.put_line(
523 fnd_file.LOG
524 , 'Invalid process code: '
525 || x_physical_process
526 || ' (logical process: '
527 || g_logical_process
528 || ')'
529 );
530 RAISE ABORT;
531 END IF;
532 ELSIF g_logical_process = g_calculation THEN
533 IF x_physical_process IS NULL THEN
534 newpr := g_revert;
535 ELSIF x_physical_process = g_revert THEN
536 newpr := g_classification;
537 ELSIF x_physical_process = g_classification THEN
538 newpr := g_rollup;
539 ELSIF x_physical_process = g_rollup THEN
540 newpr := g_population;
541 ELSIF x_physical_process = g_population THEN
542 newpr := g_calculation;
543 ELSIF x_physical_process = g_calculation THEN
544 newpr := NULL;
545 ELSE
546 cn_message_pkg.DEBUG(
547 'Invalid process code: '
548 || x_physical_process
549 || ' (logical process: '
550 || g_logical_process
551 || ')'
552 );
553 fnd_file.put_line(
554 fnd_file.LOG
555 , 'Invalid process code: '
556 || x_physical_process
557 || ' (logical process: '
558 || g_logical_process
559 || ')'
560 );
561 RAISE ABORT;
562 END IF;
563 END IF;
564
565 x_physical_process := newpr;
566 END next_process;
567
568 -- Procedure Name
569 -- Populate_calcsub_batches
570 -- Purpose
571 -- insert entry into cn_process_batches for this srp/period and entries of
572 -- all srps below this srp depending on the value of x_entire_hierarchy
573 -- Notes
574 -- 12-Jul-1998, Richard Jin Created
575 -- 05-Jun-1999, Richard Jin Modified 11.5
576 PROCEDURE populate_calcsub_batches(
577 p_salesrep_id NUMBER
578 , p_start_date DATE
579 , p_end_date DATE
580 , p_start_period_id NUMBER
581 , p_end_period_id NUMBER
582 , p_logical_batch_id NUMBER
583 , p_entire_hierarchy VARCHAR2
584 ) IS
585 -- The cursor Impacted_Reps looks up the rollup hierarchy of the
586 -- salesrep being calculated
587 -- will call Ram's API to get impacted salesreps
588 l_process_batch_id NUMBER;
589 l_user_id NUMBER(15) := fnd_global.user_id;
590 l_resp_id NUMBER(15) := fnd_global.resp_id;
591 l_login_id NUMBER(15) := fnd_global.login_id;
592 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
593 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
594 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
595 l_srp_rec cn_rollup_pvt.srp_rec_type;
596 l_descendant_tbl cn_rollup_pvt.srp_tbl_type;
597 l_counter NUMBER;
598 l_msg_count NUMBER;
599 l_msg_data VARCHAR2(2000);
600 l_return_status VARCHAR2(30);
601 BEGIN
602 g_logical_batch_id := p_logical_batch_id;
603
604 SELECT org_id
605 INTO g_org_id
606 FROM cn_calc_submission_batches_all
607 WHERE logical_batch_id = p_logical_batch_id;
608
609 IF p_entire_hierarchy = 'Y' THEN
610 l_srp_rec.salesrep_id := p_salesrep_id;
611 l_srp_rec.start_date := p_start_date;
612 l_srp_rec.end_date := p_end_date;
613 cn_rollup_pvt.get_descendant_salesrep(
614 p_api_version => 1.0
615 , p_init_msg_list => fnd_api.g_true
616 , p_commit => fnd_api.g_false
617 , x_return_status => l_return_status
618 , x_msg_count => l_msg_count
619 , x_msg_data => l_msg_data
620 , p_srp => l_srp_rec
621 , x_srp => l_descendant_tbl
622 , p_org_id => g_org_id
623 );
624
625 IF l_return_status <> fnd_api.g_ret_sts_success THEN
626 cn_message_pkg.DEBUG('Exception occurs in getting the descendants:');
627
628 FOR l_counter IN 1 .. l_msg_count LOOP
629 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
630 , p_encoded => fnd_api.g_false));
631 END LOOP;
632
633 RAISE api_call_failed;
634 END IF;
635
636 IF l_descendant_tbl.COUNT > 0 THEN
637 FOR l_counter IN l_descendant_tbl.FIRST .. l_descendant_tbl.LAST LOOP
638 BEGIN
639 INSERT INTO cn_process_batches_all
640 (
641 process_batch_id
642 , logical_batch_id
643 , srp_period_id
644 , period_id
645 , end_period_id
646 , start_date
647 , end_date
648 , salesrep_id
649 , status_code
650 , process_batch_type
651 , creation_date
652 , created_by
653 , last_update_date
654 , last_updated_by
655 , last_update_login
656 , request_id
657 , program_application_id
658 , program_id
659 , program_update_date
660 , org_id
661 )
662 SELECT cn_process_batches_s1.NEXTVAL
663 , g_logical_batch_id
664 , 1 /* use a dummy value since this is a not null column */
665 , p_start_period_id
666 , p_end_period_id
667 , p_start_date
668 , p_end_date
669 , l_descendant_tbl(l_counter).salesrep_id
670 , 'IN_USE'
671 , 'TO_REVERT_BASE_REP'
672 , SYSDATE
673 , l_user_id
674 , SYSDATE
675 , l_user_id
676 , l_login_id
677 , l_conc_request_id
678 , l_prog_appl_id
679 , l_conc_prog_id
680 , SYSDATE
681 , g_org_id
682 FROM DUAL
683 WHERE NOT EXISTS(
684 SELECT 1
685 FROM cn_process_batches_all
686 WHERE logical_batch_id = g_logical_batch_id
687 AND salesrep_id = l_descendant_tbl(l_counter).salesrep_id
688 AND period_id = p_start_period_id
689 AND end_period_id = p_end_period_id
690 AND start_date = p_start_date
691 AND end_date = p_end_date);
692 EXCEPTION
693 WHEN OTHERS THEN
694 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
695 fnd_log.STRING(
696 fnd_log.level_unexpected
697 , 'cn.plsql.cn_proc_batches_pkg.populate_calcsub_batches.loop'
698 , SQLERRM
699 );
700 END IF;
701
702 cn_message_pkg.DEBUG
703 ('Exception occurs in including the descendants in the calc process:');
704 cn_message_pkg.DEBUG(SQLERRM);
705 RAISE;
706 END;
707 END LOOP;
708 END IF; -- checking l_descendant_tbl.count > 0
709 END IF;
710
711 -- case1: not entire hierarchy
712 -- only insert the base reps
713 -- case2: entire hierarchy. since get_descendants does not return the base salesrep
714 -- in the list, need to do the insert here.
715 INSERT INTO cn_process_batches_all
716 (
717 process_batch_id
718 , logical_batch_id
719 , srp_period_id
720 , period_id
721 , end_period_id
722 , start_date
723 , end_date
724 , salesrep_id
725 , status_code
726 , process_batch_type
727 , creation_date
728 , created_by
729 , last_update_date
730 , last_updated_by
731 , last_update_login
732 , request_id
733 , program_application_id
734 , program_id
735 , program_update_date
736 , org_id
737 )
738 SELECT cn_process_batches_s1.NEXTVAL
739 , g_logical_batch_id
740 , 1 /* use a dummy value since this is a not null column */
741 , p_start_period_id
742 , p_end_period_id
743 , p_start_date
744 , p_end_date
745 , p_salesrep_id
746 , 'IN_USE'
747 , 'TO_REVERT_BASE_REP'
748 , SYSDATE
749 , l_user_id
750 , SYSDATE
751 , l_user_id
752 , l_login_id
753 , l_conc_request_id
754 , l_prog_appl_id
755 , l_conc_prog_id
756 , SYSDATE
757 , g_org_id
758 FROM DUAL
759 WHERE NOT EXISTS(
760 SELECT 1
761 FROM cn_process_batches_all
762 WHERE logical_batch_id = g_logical_batch_id
763 AND salesrep_id = p_salesrep_id
764 AND period_id = p_start_period_id
765 AND end_period_id = p_end_period_id
766 AND start_date = p_start_date
767 AND end_date = p_end_date);
768
769 COMMIT;
770 EXCEPTION
771 WHEN api_call_failed THEN
772 IF (l_msg_count > 0) THEN
773 FOR l_counter IN 1 .. l_msg_count LOOP
774 fnd_file.put_line(fnd_file.LOG
775 , fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false));
776 END LOOP;
777 END IF;
778
779 cn_message_pkg.FLUSH;
780 WHEN OTHERS THEN
781 fnd_file.put_line(fnd_file.LOG, 'In cn_proc_batches_pkg.populate_calcsub_batch: ' || SQLERRM);
782 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_calcsub_batch:');
783 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
784
785 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
786 fnd_log.STRING(
787 fnd_log.level_unexpected
788 , 'cn.plsql.cn_proc_batches_pkg.populate_calcsub_batches.exception'
789 , SQLERRM
790 );
791 END IF;
792
793 RAISE;
794 END populate_calcsub_batches;
795
796 PROCEDURE get_calc_sub_periods(
797 p_start_date DATE
798 , p_end_date DATE
799 , x_start_date OUT NOCOPY DATE
800 , x_end_date OUT NOCOPY DATE
801 , x_calc_from_period_id OUT NOCOPY NUMBER
802 , x_calc_to_period_id OUT NOCOPY NUMBER
803 , p_org_id NUMBER
804 ) IS
805 CURSOR l_period_csr(l_date DATE) IS
806 SELECT period.period_id
807 , period.start_date
808 , period.end_date
809 FROM cn_period_statuses_all period
810 WHERE l_date BETWEEN period.start_date AND period.end_date AND org_id = p_org_id;
811
812 dummy DATE;
813 BEGIN
814 OPEN l_period_csr(p_start_date);
815 FETCH l_period_csr INTO x_calc_from_period_id, x_start_date, dummy;
816 CLOSE l_period_csr;
817
818 OPEN l_period_csr(p_end_date);
819 FETCH l_period_csr INTO x_calc_to_period_id, dummy, x_end_date;
820
821 CLOSE l_period_csr;
822 END get_calc_sub_periods;
823
824 PROCEDURE initialize_logical_batch(p_calc_sub_batch_id NUMBER) IS
825 BEGIN
826 SELECT cn_process_batches_s2.NEXTVAL
827 INTO g_logical_batch_id
828 FROM DUAL;
829
830 UPDATE cn_calc_submission_batches_all
831 SET logical_batch_id = g_logical_batch_id
832 WHERE calc_sub_batch_id = p_calc_sub_batch_id
833 RETURNING org_id
834 INTO g_org_id;
835
836 COMMIT;
837 END initialize_logical_batch;
838
839 FUNCTION find_srp_incomplete_plan(p_calc_sub_batch_id NUMBER)
840 RETURN BOOLEAN IS
841 l_calc_from_period_id NUMBER;
842 l_calc_to_period_id NUMBER;
843 l_salesrep_option VARCHAR2(20);
844 l_org_id NUMBER;
845 l_start_date_orig DATE;
846 l_end_date_orig DATE;
847 l_start_date_adj DATE;
848 l_end_date_adj DATE;
849 l_creation_date DATE := SYSDATE;
850 l_created_by NUMBER := fnd_global.user_id;
851 l_affected_all_reps VARCHAR2(1) := 'N';
852 l_invalid_plans_cnt PLS_INTEGER := 0;
853 l_validated_plans_cnt PLS_INTEGER := 0;
854 l_comp_plan_rec cn_comp_plan_pvt.comp_plan_rec_type;
855 l_status_code VARCHAR2(30);
856 l_return_status VARCHAR2(30);
857 l_msg_count NUMBER;
858 l_msg_data VARCHAR2(2000);
859
860 CURSOR l_sub_batch_csr IS
861 SELECT start_date
862 , end_date
863 , salesrep_option
864 , org_id
865 FROM cn_calc_submission_batches_all
866 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
867
868 CURSOR l_affected_all_csr IS
869 SELECT 'Y'
870 FROM cn_notify_log_all
871 WHERE org_id = l_org_id
872 AND salesrep_id = -1000
873 AND period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
874 AND status = 'INCOMPLETE'
875 AND revert_state <> 'NCALC';
876
877 CURSOR l_invalid_plans IS
878 SELECT comp_plan_id
879 FROM cn_calc_sub_validations_all
880 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
881
882 CURSOR plan_status(p_comp_plan_id NUMBER) IS
883 SELECT status_code
884 FROM cn_comp_plans_all
885 WHERE comp_plan_id = p_comp_plan_id;
886 BEGIN
887 OPEN l_sub_batch_csr;
888 FETCH l_sub_batch_csr INTO l_start_date_orig, l_end_date_orig, l_salesrep_option, l_org_id;
889 CLOSE l_sub_batch_csr;
890
891 get_calc_sub_periods(
892 l_start_date_orig
893 , l_end_date_orig
894 , l_start_date_adj
895 , l_end_date_adj
896 , l_calc_from_period_id
897 , l_calc_to_period_id
898 , l_org_id
899 );
900
901 DELETE FROM cn_calc_sub_validations_all
902 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
903
904 -- Get validation result if SALESREP_OPTION = 'ALL_REPS'
905 IF (l_salesrep_option = 'ALL_REPS') THEN
906 INSERT INTO cn_calc_sub_validations_all
907 (
908 org_id
909 , calc_sub_batch_id
910 , comp_plan_id
911 , affected_reps
912 , created_by
913 , creation_date
914 )
915 SELECT l_org_id
916 , p_calc_sub_batch_id
917 , v.comp_plan_id
918 , v.num_of_affected_reps
919 , l_created_by
920 , l_creation_date
921 FROM (SELECT PLAN.comp_plan_id
922 , COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
923 FROM cn_srp_plan_assigns_all spa
924 , cn_srp_role_dtls_all srd
925 , cn_comp_plans_all PLAN
926 WHERE PLAN.org_id = l_org_id
927 AND PLAN.status_code = 'INCOMPLETE'
928 AND spa.comp_plan_id = PLAN.comp_plan_id
929 AND GREATEST(spa.start_date, l_start_date_adj) <=
930 LEAST(NVL(spa.end_date, l_end_date_adj), l_end_date_adj)
931 AND srd.srp_role_id(+) = spa.srp_role_id
932 AND (
933 srd.plan_activate_status = 'PUSHED'
934 OR srd.plan_activate_status IS NULL
935 OR srd.org_code = 'EMPTY'
936 )
937 GROUP BY PLAN.comp_plan_id) v;
938
939 IF (SQL%FOUND) THEN
940 NULL; --return true;
941 END IF;
942 -- Get validation result if SALESREP_OPTION = 'USER_SPECIFY'
943 ELSIF(l_salesrep_option = 'USER_SPECIFY') THEN
944 INSERT INTO cn_calc_sub_validations_all
945 (
946 org_id
947 , calc_sub_batch_id
948 , comp_plan_id
949 , affected_reps
950 , created_by
951 , creation_date
952 )
953 SELECT l_org_id
954 , p_calc_sub_batch_id
955 , v.comp_plan_id
956 , v.num_of_affected_reps
957 , l_created_by
958 , l_creation_date
959 FROM (SELECT PLAN.comp_plan_id
960 , COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
961 FROM cn_calc_submission_entries cse
962 , cn_srp_plan_assigns_all spa
963 , cn_srp_role_dtls_all srd
964 , cn_comp_plans_all PLAN
965 WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
966 AND spa.salesrep_id = cse.salesrep_id
967 AND PLAN.org_id = l_org_id
968 AND PLAN.status_code = 'INCOMPLETE'
969 AND PLAN.comp_plan_id = spa.comp_plan_id
970 AND GREATEST(spa.start_date, l_start_date_adj) <=
971 LEAST(NVL(spa.end_date, l_end_date_adj), l_end_date_adj)
972 AND srd.srp_role_id(+) = spa.srp_role_id
973 AND (
974 srd.plan_activate_status = 'PUSHED'
975 OR srd.plan_activate_status IS NULL
976 OR srd.org_code = 'EMPTY'
977 )
978 GROUP BY PLAN.comp_plan_id) v;
979
980 IF (SQL%FOUND) THEN
981 NULL; --return true;
982 END IF;
983 -- Get validation result if SALESREP_OPTION = 'REPS_IN_NOTIFY_LOG'
984 ELSIF(l_salesrep_option = 'REPS_IN_NOTIFY_LOG') THEN
985 OPEN l_affected_all_csr;
986 FETCH l_affected_all_csr INTO l_affected_all_reps;
987 CLOSE l_affected_all_csr;
988
989 IF (l_affected_all_reps = 'Y') THEN
990 INSERT INTO cn_calc_sub_validations_all
991 (
992 org_id
993 , calc_sub_batch_id
994 , comp_plan_id
995 , affected_reps
996 , created_by
997 , creation_date
998 )
999 SELECT l_org_id
1000 , p_calc_sub_batch_id
1001 , v.comp_plan_id
1002 , v.num_of_affected_reps
1003 , l_created_by
1004 , l_creation_date
1005 FROM (SELECT PLAN.comp_plan_id
1006 , COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
1007 FROM cn_srp_plan_assigns_all spa
1008 , cn_srp_role_dtls_all srd
1009 , cn_comp_plans_all PLAN
1010 WHERE PLAN.org_id = l_org_id
1011 AND PLAN.status_code = 'INCOMPLETE'
1012 AND spa.comp_plan_id = PLAN.comp_plan_id
1013 AND GREATEST(spa.start_date, l_start_date_orig) <=
1014 LEAST(NVL(spa.end_date, l_end_date_orig), l_end_date_orig)
1015 AND srd.srp_role_id(+) = spa.srp_role_id
1016 AND (
1017 srd.plan_activate_status = 'PUSHED'
1018 OR srd.plan_activate_status IS NULL
1019 OR srd.org_code = 'EMPTY'
1020 )
1021 GROUP BY PLAN.comp_plan_id) v;
1022
1023 IF (SQL%FOUND) THEN
1024 NULL; --return true;
1025 END IF;
1026 ELSE
1027 INSERT INTO cn_calc_sub_validations_all
1028 (
1029 org_id
1030 , calc_sub_batch_id
1031 , comp_plan_id
1032 , affected_reps
1033 , created_by
1034 , creation_date
1035 )
1036 SELECT l_org_id
1037 , p_calc_sub_batch_id
1038 , v.comp_plan_id
1039 , v.num_of_affected_reps
1040 , l_created_by
1041 , l_creation_date
1042 FROM (SELECT PLAN.comp_plan_id
1043 , COUNT(DISTINCT spa.salesrep_id) num_of_affected_reps
1044 FROM cn_srp_plan_assigns_all spa
1045 , cn_srp_role_dtls_all srd
1046 , cn_comp_plans_all PLAN
1047 WHERE PLAN.org_id = l_org_id
1048 AND PLAN.status_code = 'INCOMPLETE'
1049 AND spa.comp_plan_id = PLAN.comp_plan_id
1050 AND GREATEST(spa.start_date, l_start_date_orig) <=
1051 LEAST(NVL(spa.end_date, l_end_date_orig), l_end_date_orig)
1052 AND srd.srp_role_id(+) = spa.srp_role_id
1053 AND (
1054 srd.plan_activate_status = 'PUSHED'
1055 OR srd.plan_activate_status IS NULL
1056 OR srd.org_code = 'EMPTY'
1057 )
1058 AND EXISTS(
1059 SELECT 1
1060 FROM cn_notify_log_all
1061 WHERE salesrep_id = spa.salesrep_id
1062 AND org_id = l_org_id
1063 AND period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1064 AND status = 'INCOMPLETE'
1065 AND revert_state <> 'NCALC')
1066 GROUP BY PLAN.comp_plan_id) v;
1067
1068 IF (SQL%FOUND) THEN
1069 NULL; --return true;
1070 END IF;
1071 END IF;
1072 END IF;
1073
1074 FOR invalid_plan IN l_invalid_plans LOOP
1075 l_invalid_plans_cnt := l_invalid_plans_cnt + 1;
1076
1077 IF (l_invalid_plans_cnt = 51) THEN
1078 EXIT;
1079 END IF;
1080
1081 l_comp_plan_rec.comp_plan_id := invalid_plan.comp_plan_id;
1082 cn_comp_plan_pvt.validate_comp_plan(
1083 p_api_version => 1.0
1084 , p_comp_plan => l_comp_plan_rec
1085 , x_return_status => l_return_status
1086 , x_msg_count => l_msg_count
1087 , x_msg_data => l_msg_data
1088 );
1089
1090 OPEN plan_status(invalid_plan.comp_plan_id);
1091 FETCH plan_status INTO l_status_code;
1092 CLOSE plan_status;
1093
1094 IF (l_status_code = 'COMPLETE') THEN
1095 DELETE FROM cn_calc_sub_validations_all
1096 WHERE calc_sub_batch_id = p_calc_sub_batch_id
1097 AND comp_plan_id = invalid_plan.comp_plan_id;
1098
1099 l_validated_plans_cnt := l_validated_plans_cnt + 1;
1100 END IF;
1101 END LOOP;
1102
1103 IF (l_invalid_plans_cnt = l_validated_plans_cnt) THEN
1104 RETURN FALSE;
1105 ELSE
1106 RETURN TRUE;
1107 END IF;
1108 EXCEPTION
1109 WHEN OTHERS THEN
1110 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1111 fnd_log.STRING(
1112 fnd_log.level_unexpected
1113 , 'cn.plsql.cn_proc_batches_pkg.find_srp_incomplete_plan.exception'
1114 , SQLERRM
1115 );
1116 END IF;
1117
1118 RAISE;
1119 END find_srp_incomplete_plan;
1120
1121 -- Procedure Name
1122 -- Populate_process_batches
1123 -- Purpose
1124 -- populate the cn_process_batches for an entry in cn_calc_submission_batches
1125 -- Notes
1126 -- 12-Jul-1998, Richard Jin Created
1127 -- 19-Sep-2002, Arvind Krishnan BUG:2509788 - Improved the performance of query in l_all_reps_csr
1128 PROCEDURE populate_process_batch(p_calc_sub_batch_id NUMBER) IS
1129 l_calc_from_period_id NUMBER;
1130 l_calc_to_period_id NUMBER;
1131 l_intelligent_flag VARCHAR2(1);
1132 l_hierarchy_flag VARCHAR2(1);
1133 l_salesrep_option VARCHAR2(20);
1134 l_org_id NUMBER;
1135 l_counter NUMBER;
1136 l_start_date_orig DATE;
1137 l_end_date_orig DATE;
1138 l_start_date_adj DATE;
1139 l_end_date_adj DATE;
1140
1141 CURSOR l_sub_batch_csr IS
1142 SELECT start_date
1143 , end_date
1144 , intelligent_flag
1145 , NVL(hierarchy_flag, 'N')
1146 , salesrep_option
1147 , org_id
1148 FROM cn_calc_submission_batches_all
1149 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
1150 BEGIN
1151 l_counter := 0;
1152
1153 OPEN l_sub_batch_csr;
1154 FETCH l_sub_batch_csr
1155 INTO l_start_date_orig
1156 , l_end_date_orig
1157 , l_intelligent_flag
1158 , l_hierarchy_flag
1159 , l_salesrep_option
1160 , l_org_id;
1161 CLOSE l_sub_batch_csr;
1162
1163 get_calc_sub_periods(
1164 l_start_date_orig
1165 , l_end_date_orig
1166 , l_start_date_adj
1167 , l_end_date_adj
1168 , l_calc_from_period_id
1169 , l_calc_to_period_id
1170 , l_org_id
1171 );
1172
1173 IF l_salesrep_option = 'ALL_REPS' THEN
1174 DECLARE
1175 CURSOR l_all_reps_csr(l_start_date DATE, l_end_date DATE) IS
1176 SELECT DISTINCT intel.salesrep_id
1177 FROM cn_srp_intel_periods_all intel
1178 WHERE org_id = l_org_id
1179 AND intel.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1180 AND (
1181 EXISTS(
1182 SELECT 1
1183 FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
1184 WHERE spa.salesrep_id = intel.salesrep_id
1185 AND spa.org_id = intel.org_id
1186 AND (
1187 (
1188 spa.start_date < l_start_date
1189 AND (
1190 spa.end_date IS NULL
1191 OR l_start_date <= spa.end_date
1192 )
1193 )
1194 OR (spa.start_date BETWEEN l_start_date AND l_end_date)
1195 )
1196 AND spa.comp_plan_id = PLAN.comp_plan_id
1197 AND PLAN.status_code = 'COMPLETE')
1198 OR EXISTS(
1199 SELECT 1
1200 FROM cn_commission_lines_all
1201 WHERE credited_salesrep_id = intel.salesrep_id
1202 AND processed_period_id BETWEEN l_calc_from_period_id
1203 AND l_calc_to_period_id
1204 AND org_id = intel.org_id
1205 AND processed_date BETWEEN l_start_date AND l_end_date)
1206 OR EXISTS(
1207 SELECT 1
1208 FROM cn_commission_headers_all
1209 WHERE direct_salesrep_id = intel.salesrep_id
1210 AND org_id = intel.org_id
1211 AND processed_date BETWEEN l_start_date AND l_end_date)
1212 );
1213 -- salesrep has no plan assign within the date range but has trxs
1214 -- since the rollup has been done in loader, we don't have to pick
1215 -- those salesrep any more.
1216 BEGIN
1217 IF l_intelligent_flag = 'Y' THEN
1218 FOR rep IN l_all_reps_csr(l_start_date_adj, l_end_date_adj) LOOP
1219 l_counter := l_counter + 1;
1220 populate_calcsub_batches(
1221 rep.salesrep_id
1222 , l_start_date_adj
1223 , l_end_date_adj
1224 , l_calc_from_period_id
1225 , l_calc_to_period_id
1226 , g_logical_batch_id
1227 , l_hierarchy_flag
1228 );
1229 END LOOP;
1230 ELSE
1231 FOR rep IN l_all_reps_csr(l_start_date_orig, l_end_date_adj) LOOP
1232 l_counter := l_counter + 1;
1233 populate_calcsub_batches(
1234 rep.salesrep_id
1235 , l_start_date_orig
1236 , l_end_date_adj
1237 , l_calc_from_period_id
1238 , l_calc_to_period_id
1239 , g_logical_batch_id
1240 , l_hierarchy_flag
1241 );
1242 END LOOP;
1243 END IF;
1244
1245 IF l_counter = 0 THEN /* no one to be calculated */
1246 fnd_message.set_name('CN', 'CNSBCS_NO_ONE_TO_CALCULATE');
1247
1248 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1249 fnd_log.MESSAGE(
1250 fnd_log.level_exception
1251 , 'cn.plsql.cn_proc_batches_pkg.populate_process_batches.all_reps'
1252 , FALSE
1253 );
1254 END IF;
1255
1256 RAISE no_one_with_complete_plan;
1257 END IF;
1258 END;
1259 ELSIF l_salesrep_option = 'USER_SPECIFY' THEN
1260 DECLARE
1261 CURSOR l_reps_csr(l_start_date DATE, l_end_date DATE) IS
1262 SELECT cse.salesrep_id
1263 , NVL(cse.hierarchy_flag, 'N') hierarchy_flag
1264 FROM cn_calc_submission_entries_all cse
1265 WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
1266 AND (
1267 (
1268 EXISTS(
1269 SELECT 1
1270 FROM cn_notify_log_all LOG
1271 WHERE LOG.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1272 AND LOG.status = 'INCOMPLETE'
1273 AND LOG.org_id = cse.org_id
1274 AND (LOG.salesrep_id = -1000 OR LOG.salesrep_id = cse.salesrep_id))
1275 )
1276 OR (
1277 EXISTS(
1278 SELECT 1
1279 FROM cn_commission_lines_all
1280 WHERE credited_salesrep_id = cse.salesrep_id
1281 AND processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1282 AND status <> 'OBSOLETE'
1283 AND org_id = cse.org_id)
1284 )
1285 OR (
1286 EXISTS(
1287 SELECT 1
1288 FROM cn_commission_headers_all
1289 WHERE direct_salesrep_id = cse.salesrep_id
1290 AND processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1291 AND status <> 'OBSOLETE'
1292 AND org_id = cse.org_id)
1293 )
1294 OR (
1295 EXISTS -- salesrep has an active complete plan within the date range
1296 (
1297 SELECT 1
1298 FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
1299 WHERE spa.salesrep_id = cse.salesrep_id
1300 AND spa.org_id = cse.org_id
1301 AND (
1302 (
1303 spa.start_date < l_start_date
1304 AND (spa.end_date IS NULL OR l_start_date <= spa.end_date)
1305 )
1306 OR (spa.start_date BETWEEN l_start_date AND l_end_date)
1307 )
1308 AND spa.comp_plan_id = PLAN.comp_plan_id
1309 AND PLAN.status_code = 'COMPLETE')
1310 )
1311 );
1312 BEGIN
1313 IF l_intelligent_flag = 'Y' THEN
1314 FOR rep IN l_reps_csr(l_start_date_adj, l_end_date_adj) LOOP
1315 l_counter := l_counter + 1;
1316 populate_calcsub_batches(
1317 rep.salesrep_id
1318 , l_start_date_adj
1319 , l_end_date_adj
1320 , l_calc_from_period_id
1321 , l_calc_to_period_id
1322 , g_logical_batch_id
1323 , rep.hierarchy_flag
1324 );
1325 END LOOP;
1326 ELSE
1327 FOR rep IN l_reps_csr(l_start_date_orig, l_end_date_adj) LOOP
1328 l_counter := l_counter + 1;
1329 populate_calcsub_batches(
1330 rep.salesrep_id
1331 , l_start_date_orig
1332 , l_end_date_adj
1333 , l_calc_from_period_id
1334 , l_calc_to_period_id
1335 , g_logical_batch_id
1336 , rep.hierarchy_flag
1337 );
1338 END LOOP;
1339 END IF;
1340
1341 IF l_counter = 0 THEN /* no one to be calculated */
1342 fnd_message.set_name('CN', 'CNSBCS_NO_ONE_TO_CALCULATE');
1343
1344 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1345 fnd_log.MESSAGE(
1346 fnd_log.level_exception
1347 , 'cn.plsql.cn_proc_batches_pkg.populate_process_batches.user_specify'
1348 , FALSE
1349 );
1350 END IF;
1351
1352 RAISE no_one_with_complete_plan;
1353 END IF;
1354 END;
1355 ELSE -- l_salesrep_option = 'REPS_IN_NOTIFY_LOG''
1356 -- only available when it's intelligent calc
1357 DECLARE
1358 -- select srp/period pair with a entry in notify_log
1359 -- select all srp/period if there is entry in notify log for
1360 -- a period and salesrep_id = -1000
1361 -- make sure srp has an active comp_plan in a open period NOT ANY MORE
1362
1363 -- 10/25/1999. Now as long as there is an entry in notify_log, we don't check
1364 -- the existence of a complete compensation plan
1365 l_return_status VARCHAR2(30);
1366 l_msg_count NUMBER;
1367 l_msg_data VARCHAR2(2000);
1368 l_srp cn_rollup_pvt.srp_rec_type;
1369 l_active_group cn_rollup_pvt.active_group_tbl_type;
1370 l_srp_group cn_rollup_pvt.srp_group_rec_type;
1371 l_srp_group_ancestor cn_rollup_pvt.srp_group_tbl_type;
1372 l_system_rollup_flag VARCHAR2(1);
1373
1374 CURSOR missed_reps IS
1375 SELECT DISTINCT ch.direct_salesrep_id
1376 , ch.processed_period_id
1377 , ch.processed_date
1378 , NVL(ch.rollup_date, ch.processed_date) rollup_date
1379 FROM cn_commission_headers_all ch
1380 WHERE ch.direct_salesrep_id IN(
1381 SELECT salesrep_id
1382 FROM cn_srp_intel_periods_all
1383 WHERE period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1384 AND org_id = g_org_id)
1385 AND ch.org_id = g_org_id
1386 AND ch.processed_date BETWEEN l_start_date_adj AND l_end_date_adj
1387 AND ch.status IN('COL', 'CLS');
1388
1389 CURSOR missed_lines IS
1390 SELECT DISTINCT cl.credited_salesrep_id
1391 , p.start_date
1392 , p.end_date
1393 , p.period_id
1394 FROM cn_commission_lines_all cl, cn_period_statuses_all p
1395 WHERE cl.processed_period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1396 AND cl.status IN('ROLL', 'POP')
1397 AND cl.org_id = g_org_id
1398 AND cl.processed_period_id = p.period_id
1399 AND p.org_id = g_org_id;
1400
1401 CURSOR log_reps IS
1402 SELECT DISTINCT LOG.salesrep_id
1403 , period.start_date
1404 , period.end_date
1405 , period.period_id
1406 FROM cn_notify_log_all LOG, cn_period_statuses_all period
1407 WHERE period.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1408 AND period.org_id = g_org_id
1409 AND LOG.period_id = period.period_id
1410 AND LOG.org_id = g_org_id
1411 AND LOG.status = 'INCOMPLETE'
1412 AND LOG.salesrep_id <> -1000
1413 AND LOG.revert_state <> 'NCALC'
1414 UNION
1415 SELECT DISTINCT intel.salesrep_id
1416 , period.start_date
1417 , period.end_date
1418 , period.period_id
1419 FROM cn_period_statuses_all period
1420 , cn_notify_log_all LOG
1421 , cn_srp_intel_periods_all intel
1422 WHERE period.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
1423 AND period.org_id = g_org_id
1424 AND LOG.period_id = period.period_id
1425 AND LOG.org_id = g_org_id
1426 AND LOG.salesrep_id = -1000
1427 AND LOG.status = 'INCOMPLETE'
1428 AND LOG.revert_state <> 'NCALC'
1429 AND intel.period_id = period.period_id
1430 AND intel.org_id = g_org_id;
1431 BEGIN
1432 SELECT NVL(srp_rollup_flag, 'N')
1433 INTO l_system_rollup_flag
1434 FROM cn_repositories_all
1435 WHERE org_id = g_org_id;
1436
1437 FOR missed_line IN missed_lines LOOP
1438 cn_mark_events_pkg.mark_notify(
1439 p_salesrep_id => missed_line.credited_salesrep_id
1440 , p_period_id => missed_line.period_id
1441 , p_start_date => missed_line.start_date
1442 , p_end_date => missed_line.end_date
1443 , p_quota_id => NULL
1444 , p_revert_to_state => 'CALC'
1445 , p_event_log_id => NULL
1446 , p_org_id => g_org_id
1447 );
1448 END LOOP;
1449
1450 COMMIT;
1451
1452 FOR missed_rep IN missed_reps LOOP
1453 cn_mark_events_pkg.mark_notify(
1454 p_salesrep_id => missed_rep.direct_salesrep_id
1455 , p_period_id => missed_rep.processed_period_id
1456 , p_start_date => missed_rep.processed_date
1457 , p_end_date => missed_rep.processed_date
1458 , p_quota_id => NULL
1459 , p_revert_to_state => 'CALC'
1460 , p_event_log_id => NULL
1461 , p_org_id => g_org_id
1462 );
1463
1464 IF (l_system_rollup_flag = 'Y') THEN
1465 l_srp.salesrep_id := missed_rep.direct_salesrep_id;
1466 l_srp.start_date := missed_rep.rollup_date;
1467 l_srp.end_date := missed_rep.rollup_date;
1468 l_active_group.DELETE;
1469 cn_rollup_pvt.get_active_group(
1470 p_api_version => 1.0
1471 , x_return_status => l_return_status
1472 , x_msg_count => l_msg_count
1473 , x_msg_data => l_msg_data
1474 , p_srp => l_srp
1475 , x_active_group => l_active_group
1476 , p_org_id => g_org_id
1477 );
1478
1479 IF (l_active_group.COUNT > 0) THEN
1480 FOR i IN l_active_group.FIRST .. l_active_group.LAST LOOP
1481 l_srp_group_ancestor.DELETE;
1482 l_srp_group.salesrep_id := l_srp.salesrep_id;
1483 l_srp_group.GROUP_ID := l_active_group(i).GROUP_ID;
1484 l_srp_group.start_date := l_active_group(i).start_date;
1485 l_srp_group.end_date := l_active_group(i).end_date;
1486 cn_rollup_pvt.get_ancestor_salesrep(
1487 p_api_version => 1.0
1488 , x_return_status => l_return_status
1489 , x_msg_count => l_msg_count
1490 , x_msg_data => l_msg_data
1491 , p_srp => l_srp_group
1492 , x_srp => l_srp_group_ancestor
1493 , p_org_id => g_org_id
1494 );
1495
1496 IF (l_srp_group_ancestor.COUNT > 0) THEN
1497 FOR eachsrp IN l_srp_group_ancestor.FIRST .. l_srp_group_ancestor.LAST LOOP
1498 cn_mark_events_pkg.mark_notify
1499 (
1500 p_salesrep_id => l_srp_group_ancestor(eachsrp).salesrep_id
1501 , p_period_id => missed_rep.processed_period_id
1502 , p_start_date => missed_rep.processed_date
1503 , p_end_date => missed_rep.processed_date
1504 , p_quota_id => NULL
1505 , p_revert_to_state => 'CALC'
1506 , p_event_log_id => NULL
1507 , p_org_id => g_org_id
1508 );
1509 END LOOP;
1510 END IF;
1511 END LOOP;
1512 END IF;
1513 END IF;
1514
1515 COMMIT;
1516 END LOOP;
1517
1518 FOR rep IN log_reps LOOP
1519 l_counter := 1;
1520 populate_calcsub_batches(
1521 rep.salesrep_id
1522 , rep.start_date
1523 , rep.end_date
1524 , rep.period_id
1525 , rep.period_id
1526 , g_logical_batch_id
1527 , l_hierarchy_flag
1528 );
1529 END LOOP;
1530
1531 IF l_counter = 0 THEN /* no one to be calculated */
1532 --fnd_message.set_name('CN', 'CNSBCS_NO_ONE_IN_NOTIFY_LOG');
1533 --raise no_one_with_complete_plan;
1534 -- clku, bug 2783261, we won;t error out if we do not find any reps in
1535 -- notify log, we just set status to complete and return
1536 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1537 fnd_log.STRING
1538 (
1539 fnd_log.level_exception
1540 , 'cn.plsql.cn_proc_batches_pkg.populate_process_batches.reps_in_notify_log'
1541 , 'No salesreps to calculate ...'
1542 );
1543 END IF;
1544
1545 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
1546 END IF;
1547 END;
1548 END IF;
1549 EXCEPTION
1550 WHEN no_one_with_complete_plan THEN
1551 fnd_file.put_line
1552 (
1553 fnd_file.LOG
1554 , 'Exception occurs in cn_proc_batches_pkg.populate_process_batch: No one with complete compensation plan to calculate.'
1555 );
1556 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_process_batch:');
1557 cn_message_pkg.rollback_errormsg_commit
1558 ('No one with complete compensation plan to calculate.');
1559 RAISE;
1560 WHEN OTHERS THEN
1561 fnd_file.put_line(fnd_file.LOG, 'In cn_proc_batches_pkg.populate_process_batch:' || SQLERRM);
1562
1563 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1564 fnd_log.STRING(
1565 fnd_log.level_unexpected
1566 , 'cn.plsql.cn_proc_batches_pkg.populate_process_batch.exception'
1567 , SQLERRM
1568 );
1569 END IF;
1570
1571 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_process_batch: ');
1572 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
1573 RAISE;
1574 END populate_process_batch;
1575
1576 -- Procedure Name
1577 -- calculation_submission
1578 -- Purpose
1579 -- start the calculation process when called from calc submission form
1580 -- Notes
1581 -- 12-Jul-1998, Richard Jin Created
1582 PROCEDURE calculation_submission(
1583 p_calc_sub_batch_id NUMBER
1584 , x_process_audit_id OUT NOCOPY NUMBER
1585 , x_process_status_code OUT NOCOPY VARCHAR2
1586 ) IS
1587 CURSOR l_calc_batch_csr IS
1588 SELECT status
1589 , concurrent_flag
1590 , logical_batch_id
1591 , calc_type
1592 , start_date
1593 , end_date
1594 , org_id
1595 FROM cn_calc_submission_batches_all
1596 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
1597
1598 l_batch_rec l_calc_batch_csr%ROWTYPE;
1599 l_status cn_calc_submission_batches.status%TYPE;
1600 BEGIN
1601 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1602 fnd_log.STRING(
1603 fnd_log.level_procedure
1604 , 'cn.plsql.cn_proc_batches_pkg.calculation_submission.begin'
1605 , 'Beginning of calculation submission procedure ...'
1606 );
1607 END IF;
1608
1609 OPEN l_calc_batch_csr;
1610 FETCH l_calc_batch_csr INTO l_batch_rec;
1611 CLOSE l_calc_batch_csr;
1612
1613 g_calc_type := l_batch_rec.calc_type;
1614
1615 IF l_batch_rec.status = 'COMPLETE' THEN
1616 -- once completed, can not start it again
1617 NULL;
1618 ELSE
1619 cn_message_pkg.begin_batch(
1620 x_process_type => 'CALCULATION'
1621 , x_parent_proc_audit_id => NULL
1622 , x_process_audit_id => x_process_audit_id
1623 , x_request_id => fnd_global.conc_request_id
1624 , p_org_id => l_batch_rec.org_id
1625 );
1626
1627 UPDATE cn_calc_submission_batches_all
1628 SET process_audit_id = x_process_audit_id
1629 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
1630
1631 IF (
1632 (l_batch_rec.status = 'FAILED' AND l_batch_rec.concurrent_flag = 'N')
1633 OR g_calc_type = 'BONUS'
1634 ) THEN
1635 -- purge the previous run from cn_process_batch before restart as a new logical batch
1636 DELETE cn_process_batches_all
1637 WHERE logical_batch_id = l_batch_rec.logical_batch_id;
1638 END IF;
1639
1640 IF (
1641 (l_batch_rec.status <> 'FAILED' OR l_batch_rec.concurrent_flag = 'N')
1642 OR g_calc_type = 'BONUS'
1643 ) THEN
1644 initialize_logical_batch(p_calc_sub_batch_id);
1645 ELSE
1646 g_logical_batch_id := l_batch_rec.logical_batch_id;
1647 g_org_id := l_batch_rec.org_id;
1648 END IF;
1649
1650 IF g_calc_type = 'BONUS' THEN
1651 populate_bonus_process_batch(p_calc_sub_batch_id);
1652 ELSE
1653 IF (l_batch_rec.status <> 'FAILED' OR l_batch_rec.concurrent_flag = 'N') THEN
1654 populate_process_batch(p_calc_sub_batch_id);
1655 END IF;
1656 END IF;
1657
1658 -- clku, bug 2783261, check if the status is complete before calling main
1659 SELECT status
1660 INTO l_status
1661 FROM cn_calc_submission_batches_all
1662 WHERE logical_batch_id = g_logical_batch_id;
1663
1664 IF l_status <> 'COMPLETE' THEN
1665 cn_global_var.initialize_instance_info(l_batch_rec.org_id);
1666 cn_proc_batches_pkg.main(
1667 p_concurrent_flag => l_batch_rec.concurrent_flag
1668 , p_process_name => 'CALCULATION'
1669 , p_logical_batch_id => g_logical_batch_id
1670 , p_start_date => l_batch_rec.start_date
1671 , p_end_date => l_batch_rec.end_date
1672 , p_salesrep_id => NULL
1673 , p_comp_plan_id => NULL
1674 , x_process_audit_id => x_process_audit_id
1675 , x_process_status_code => x_process_status_code
1676 );
1677 ELSE
1678 x_process_status_code := 'SUCCESS';
1679 cn_message_pkg.set_name('CN', 'ALL_PROCESS_DONE_OK');
1680 cn_message_pkg.end_batch(x_process_audit_id);
1681 END IF;
1682 END IF;
1683
1684 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1685 fnd_log.STRING(
1686 fnd_log.level_procedure
1687 , 'cn.plsql.cn_proc_batches_pkg.calculation_submission.end'
1688 , 'End of calculation submission procedure.'
1689 );
1690 END IF;
1691 EXCEPTION
1692 WHEN no_one_with_complete_plan THEN
1693 fnd_file.put_line
1694 (
1695 fnd_file.LOG
1696 , 'no_one_with_complete_plan EXCEPTION in cn_proc_batches_pkg.calculation_submission'
1697 );
1698 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.calculation_submission');
1699 cn_message_pkg.rollback_errormsg_commit
1700 ('No resource with complete compensation plan to calculate.');
1701 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
1702 COMMIT;
1703 app_exception.raise_exception;
1704 WHEN OTHERS THEN
1705 x_process_status_code := 'FAIL';
1706 fnd_file.put_line(fnd_file.LOG, 'Error in cn_proc_batches_pkg.calculation_submission.');
1707 fnd_file.put_line(fnd_file.LOG, SQLERRM);
1708 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.calculation_submission:');
1709 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
1710 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
1711 COMMIT;
1712
1713 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1714 fnd_log.STRING(
1715 fnd_log.level_unexpected
1716 , 'cn.plsql.cn_proc_batches_pkg.calculation_submission.exception'
1717 , SQLERRM
1718 );
1719 END IF;
1720 END calculation_submission;
1721
1722 -- Procedure Name
1723 -- get_physical_batch_id
1724 -- Purpose
1725 FUNCTION get_physical_batch_id
1726 RETURN NUMBER IS
1727 x_physical_batch_id NUMBER;
1728 BEGIN
1729 SELECT cn_process_batches_s3.NEXTVAL
1730 INTO x_physical_batch_id
1731 FROM SYS.DUAL;
1732
1733 RETURN x_physical_batch_id;
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1737 fnd_log.STRING(
1738 fnd_log.level_unexpected
1739 , 'cn.plsql.cn_proc_batches_pkg.get_physical_batch_id.exception'
1740 , SQLERRM
1741 );
1742 END IF;
1743
1744 fnd_file.put_line(fnd_file.LOG, 'In cn_proc_batches.get_physical_batch_id: ' || SQLERRM);
1745 RAISE;
1746 END get_physical_batch_id;
1747
1748 -- Procedure Name
1749 -- void_batches
1750 -- Purpose
1751 -- VOID the batches that have successfully moved to the required status
1752 -- to prevent them being picked up in any retries.
1753 -- Unlockable batches will remain for the requred number of retries
1754 -- Called just before program completes to purge the table of any remaining
1755 -- unprocessed records that were not procesed during retries.
1756 PROCEDURE void_batches(x_physical_batch_id VARCHAR2) IS
1757 l_user_id NUMBER(15) := fnd_global.user_id;
1758 l_resp_id NUMBER(15) := fnd_global.resp_id;
1759 l_login_id NUMBER(15) := fnd_global.login_id;
1760 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
1761 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
1762 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
1763 BEGIN
1764 IF x_physical_batch_id IS NULL THEN
1765 UPDATE cn_process_batches_all
1766 SET status_code = 'VOID'
1767 , last_update_date = SYSDATE
1768 , last_update_login = l_login_id
1769 , last_updated_by = l_user_id
1770 , request_id = l_conc_request_id
1771 , program_application_id = l_prog_appl_id
1772 , program_id = l_conc_prog_id
1773 , program_update_date = SYSDATE
1774 WHERE logical_batch_id = g_logical_batch_id;
1775 ELSE
1776 UPDATE cn_process_batches_all
1777 SET status_code = 'VOID'
1778 , last_update_date = SYSDATE
1779 , last_update_login = l_login_id
1780 , last_updated_by = l_user_id
1781 , request_id = l_conc_request_id
1782 , program_application_id = l_prog_appl_id
1783 , program_id = l_conc_prog_id
1784 , program_update_date = SYSDATE
1785 WHERE physical_batch_id = x_physical_batch_id;
1786 END IF;
1787 END void_batches;
1788
1789 -- Procedure Name
1790 -- Assign
1791 -- Purpose
1792 -- Split the logical batch into smaller physical batches of N srp_periods
1793 -- Notes
1794 -- Cannot restrict by current status because we may be executing
1795 -- many physical processes for a logical process. If the first physical
1796 -- process can't find any records in it's curr state, the second process
1797 -- will still need the physical batch id's
1798 -- Unable to lock for update because runner commits after one physical
1799 -- batch is processed. Cannot fetch from a for update cursor after a
1800 -- commit since the locks are acquired when the cursor is opened and
1801 -- released after commit
1802 PROCEDURE assign IS
1803 TYPE num_tbl_type IS TABLE OF NUMBER
1804 INDEX BY BINARY_INTEGER;
1805
1806 reps_tbl num_tbl_type;
1807 nums_tbl num_tbl_type;
1808 bids_tbl num_tbl_type;
1809 x_physical_batch_id NUMBER;
1810 l_trx_count NUMBER := 0;
1811 l_srp_count NUMBER := 0;
1812 l_user_id NUMBER(15) := fnd_global.user_id;
1813 l_resp_id NUMBER(15) := fnd_global.resp_id;
1814 l_login_id NUMBER(15) := fnd_global.login_id;
1815 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
1816 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
1817 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
1818 l_pre_logical_batch_id NUMBER;
1819 l_srp_batch_size_flag VARCHAR2(1);
1820
1821 CURSOR batch_info IS
1822 SELECT NAME
1823 , calc_type
1824 , intelligent_flag
1825 , NVL(hierarchy_flag, 'N')
1826 , salesrep_option
1827 , start_date
1828 , end_date
1829 , org_id
1830 FROM cn_calc_submission_batches_all
1831 WHERE logical_batch_id = g_logical_batch_id;
1832
1833 l_batch_info batch_info%ROWTYPE;
1834
1835 CURSOR pre_batch_info IS
1836 SELECT MAX(logical_batch_id)
1837 FROM cn_calc_submission_batches_all
1838 WHERE logical_batch_id >(g_logical_batch_id - 1000)
1839 AND logical_batch_id < g_logical_batch_id
1840 AND salesrep_option = 'ALL_REPS'
1841 AND calc_type = 'COMMISSION'
1842 AND NVL(hierarchy_flag, 'N') = 'N'
1843 AND intelligent_flag = l_batch_info.intelligent_flag
1844 AND start_date = l_batch_info.start_date
1845 AND end_date = l_batch_info.end_date
1846 AND org_id = l_batch_info.org_id;
1847
1848 CURSOR reps IS
1849 SELECT salesrep_id
1850 , DECODE(sales_lines_total, 0, commission_headers_count, sales_lines_total)
1851 FROM cn_process_batches
1852 WHERE logical_batch_id = g_logical_batch_id AND status_code = 'IN_USE'
1853 ORDER BY salesrep_id DESC;
1854
1855 CURSOR rep_lines_info(p_salesrep_id NUMBER) IS
1856 SELECT COUNT(1)
1857 FROM cn_commission_lines_all line, cn_process_batches_all batch
1858 WHERE batch.logical_batch_id = g_logical_batch_id
1859 AND batch.salesrep_id = p_salesrep_id
1860 AND batch.status_code = 'IN_USE'
1861 AND line.credited_salesrep_id = p_salesrep_id
1862 AND line.processed_period_id BETWEEN batch.period_id AND batch.end_period_id
1863 AND line.processed_date BETWEEN batch.start_date AND batch.end_date
1864 AND line.org_id = batch.org_id;
1865
1866 CURSOR action_links IS
1867 SELECT DISTINCT action_link_id
1868 FROM cn_process_batches_all batch, cn_notify_log_all LOG
1869 WHERE batch.logical_batch_id = g_logical_batch_id
1870 AND batch.status_code = 'IN_USE'
1871 AND LOG.salesrep_id = batch.salesrep_id
1872 AND LOG.period_id BETWEEN batch.period_id AND batch.end_period_id
1873 AND LOG.status = 'INCOMPLETE'
1874 AND LOG.action_link_id IS NOT NULL
1875 AND LOG.org_id = batch.org_id;
1876 BEGIN
1877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
1878 fnd_log.STRING(
1879 fnd_log.level_procedure
1880 , 'cn.plsql.cn_proc_batches_pkg.assign.begin'
1881 , 'Beginning of assigning resources to physical batches ...'
1882 );
1883 END IF;
1884
1885 l_srp_batch_size_flag := NVL(fnd_profile.VALUE('CN_SRP_ONLY_BATCH_SIZE'), 'N');
1886
1887 OPEN batch_info;
1888 FETCH batch_info INTO l_batch_info;
1889 CLOSE batch_info;
1890
1891 IF (
1892 l_batch_info.salesrep_option = 'ALL_REPS'
1893 AND l_batch_info.calc_type = 'COMMISSION'
1894 AND MOD(NVL(cn_global_var.get_srp_batch_size(l_batch_info.org_id), 0), 10) = 0
1895 ) THEN
1896 OPEN pre_batch_info;
1897 FETCH pre_batch_info INTO l_pre_logical_batch_id;
1898 CLOSE pre_batch_info;
1899 END IF;
1900
1901 IF ((l_pre_logical_batch_id IS NOT NULL) AND(l_srp_batch_size_flag <> 'Y')) THEN
1902 UPDATE cn_process_batches_all a
1903 SET (a.sales_lines_total, a.commission_headers_count) =
1904 (SELECT sales_lines_total
1905 , commission_headers_count
1906 FROM cn_process_batches_all
1907 WHERE logical_batch_id = l_pre_logical_batch_id AND salesrep_id = a.salesrep_id)
1908 WHERE logical_batch_id = g_logical_batch_id;
1909
1910 UPDATE cn_process_batches_all a
1911 SET a.sales_lines_total =
1912 (SELECT COUNT(1)
1913 FROM cn_commission_lines_all
1914 WHERE credited_salesrep_id = a.salesrep_id
1915 AND org_id = a.org_id
1916 AND processed_period_id BETWEEN a.period_id AND a.end_period_id
1917 AND processed_date BETWEEN a.start_date AND a.end_date)
1918 , a.commission_headers_count =
1919 (SELECT COUNT(1)
1920 FROM cn_commission_headers_all
1921 WHERE direct_salesrep_id = a.salesrep_id
1922 AND org_id = a.org_id
1923 AND processed_period_id BETWEEN a.period_id AND a.end_period_id
1924 AND processed_date BETWEEN a.start_date AND a.end_date)
1925 WHERE a.logical_batch_id = g_logical_batch_id AND a.sales_lines_total IS NULL;
1926
1927 OPEN reps;
1928 FETCH reps BULK COLLECT INTO reps_tbl, nums_tbl;
1929 CLOSE reps;
1930 ELSIF((l_batch_info.calc_type = 'COMMISSION') AND(l_srp_batch_size_flag <> 'Y')) THEN
1931 UPDATE cn_process_batches_all a
1932 SET a.sales_lines_total =
1933 (SELECT COUNT(1)
1934 FROM cn_commission_lines_all
1935 WHERE credited_salesrep_id = a.salesrep_id
1936 AND org_id = a.org_id
1937 AND processed_period_id BETWEEN a.period_id AND a.end_period_id
1938 AND processed_date BETWEEN a.start_date AND a.end_date)
1939 , a.commission_headers_count =
1940 (SELECT COUNT(1)
1941 FROM cn_commission_headers_all
1942 WHERE direct_salesrep_id = a.salesrep_id
1943 AND org_id = a.org_id
1944 AND processed_period_id BETWEEN a.period_id AND a.end_period_id
1945 AND processed_date BETWEEN a.start_date AND a.end_date)
1946 WHERE a.logical_batch_id = g_logical_batch_id;
1947
1948 OPEN reps;
1949 FETCH reps BULK COLLECT INTO reps_tbl, nums_tbl;
1950 CLOSE reps;
1951 ELSE
1952 OPEN reps;
1953 FETCH reps BULK COLLECT INTO reps_tbl, nums_tbl;
1954 CLOSE reps;
1955 END IF;
1956
1957 IF (reps_tbl.COUNT = 0) THEN
1958 cn_message_pkg.set_name('CN', 'PROC_NO_TRX_TO_PROCESS');
1959
1960 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1961 fnd_log.STRING(
1962 fnd_log.level_exception
1963 , 'cn.plsql.cn_proc_batches_pkg.assign.reps_count'
1964 , 'No salesreps to process.'
1965 );
1966 END IF;
1967
1968 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
1969 RAISE no_comm_lines;
1970 END IF;
1971
1972 -- get the first physical batch id
1973 x_physical_batch_id := get_physical_batch_id;
1974
1975 IF ((l_batch_info.calc_type = 'COMMISSION') AND(l_srp_batch_size_flag <> 'Y')) THEN
1976 FOR i IN reps_tbl.FIRST .. reps_tbl.LAST LOOP
1977 IF (nums_tbl(i) IS NULL) THEN
1978 OPEN rep_lines_info(reps_tbl(i));
1979 FETCH rep_lines_info INTO nums_tbl(i);
1980 CLOSE rep_lines_info;
1981 END IF;
1982
1983 l_srp_count := l_srp_count + 1;
1984 l_trx_count := l_trx_count + nums_tbl(i);
1985
1986 IF (l_trx_count >= cn_global_var.get_srp_batch_size(l_batch_info.org_id)) THEN
1987 IF (l_srp_count > 1) THEN
1988 -- this salesrep should go to next batch
1989 l_trx_count := nums_tbl(i);
1990 l_srp_count := 1;
1991 x_physical_batch_id := get_physical_batch_id;
1992 END IF;
1993 ELSIF(l_srp_count > cn_global_var.get_salesrep_batch_size(l_batch_info.org_id)) THEN
1994 -- the current batch has enough reps, this rep needs to go to next batch
1995 l_trx_count := nums_tbl(i);
1996 l_srp_count := 1;
1997 x_physical_batch_id := get_physical_batch_id;
1998 END IF;
1999
2000 bids_tbl(i) := x_physical_batch_id;
2001 END LOOP;
2002 ELSE
2003 FOR i IN reps_tbl.FIRST .. reps_tbl.LAST LOOP
2004 l_srp_count := l_srp_count + 1;
2005
2006 IF (l_srp_count > cn_global_var.get_salesrep_batch_size(l_batch_info.org_id)) THEN
2007 -- the current batch has enough reps, this rep needs to go to next batch
2008 l_srp_count := 1;
2009 x_physical_batch_id := get_physical_batch_id;
2010 END IF;
2011
2012 bids_tbl(i) := x_physical_batch_id;
2013 END LOOP;
2014 END IF;
2015
2016 FORALL i IN reps_tbl.FIRST .. reps_tbl.LAST
2017 UPDATE cn_process_batches_all
2018 SET physical_batch_id = bids_tbl(i)
2019 ,
2020 --sales_lines_total = nums_tbl(i),
2021 last_update_date = SYSDATE
2022 , last_update_login = l_login_id
2023 , last_updated_by = l_user_id
2024 , request_id = l_conc_request_id
2025 , program_application_id = l_prog_appl_id
2026 , program_id = l_conc_prog_id
2027 , program_update_date = SYSDATE
2028 WHERE salesrep_id = reps_tbl(i) AND logical_batch_id = g_logical_batch_id;
2029
2030 -- assign the last physical_batch_id to those actions
2031 -- generated from change_srp_hierarchy event
2032 IF (l_batch_info.calc_type = 'COMMISSION' AND l_batch_info.intelligent_flag = 'Y') THEN
2033 FOR action_link IN action_links LOOP
2034 UPDATE cn_notify_log_all
2035 SET physical_batch_id = x_physical_batch_id
2036 WHERE notify_log_id = action_link.action_link_id AND status = 'INCOMPLETE';
2037
2038 UPDATE cn_notify_log_all
2039 SET physical_batch_id = x_physical_batch_id
2040 WHERE action_link_id = action_link.action_link_id AND status = 'INCOMPLETE';
2041 END LOOP;
2042 END IF;
2043
2044 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2045 fnd_log.STRING(
2046 fnd_log.level_procedure
2047 , 'cn.plsql.cn_proc_batches_pkg.assign.end'
2048 , 'Finish assigning resources to physical batches.'
2049 );
2050 END IF;
2051
2052 cn_message_pkg.DEBUG('Finish assigning resources to physical batches.');
2053 cn_message_pkg.FLUSH;
2054 COMMIT;
2055 EXCEPTION
2056 WHEN no_comm_lines THEN
2057 fnd_file.put_line(fnd_file.LOG, 'no_comm_lines exception in cn_proc_batches_pkg.assign');
2058 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.assign:');
2059 cn_message_pkg.DEBUG('No transactions to process.');
2060 RAISE;
2061 WHEN OTHERS THEN
2062 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2063 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_proc_batches_pkg.assign.exception'
2064 , SQLERRM);
2065 END IF;
2066
2067 fnd_file.put_line(fnd_file.LOG, 'In cn_proc_batches.assign: ' || SQLERRM);
2068 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.assign:');
2069 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
2070 RAISE;
2071 END assign;
2072
2073 PROCEDURE update_error(x_physical_batch_id NUMBER) IS
2074 l_user_id NUMBER(15) := fnd_global.user_id;
2075 l_resp_id NUMBER(15) := fnd_global.resp_id;
2076 l_login_id NUMBER(15) := fnd_global.login_id;
2077 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
2078 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
2079 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
2080 BEGIN
2081 -- Giving the batch an 'ERROR' status prevents subsequent
2082 -- physical processes picking it up.
2083 UPDATE cn_process_batches_all
2084 SET status_code = 'ERROR'
2085 , last_update_date = SYSDATE
2086 , last_update_login = l_login_id
2087 , last_updated_by = l_user_id
2088 , request_id = l_conc_request_id
2089 , program_application_id = l_prog_appl_id
2090 , program_id = l_conc_prog_id
2091 , program_update_date = SYSDATE
2092 WHERE physical_batch_id = x_physical_batch_id;
2093 END update_error;
2094
2095 PROCEDURE conc_submit(
2096 x_conc_program VARCHAR2
2097 , x_parent_proc_audit_id NUMBER
2098 , x_logical_process VARCHAR2
2099 , x_physical_process VARCHAR2
2100 , x_physical_batch_id NUMBER
2101 , x_request_id IN OUT NOCOPY NUMBER
2102 ) IS
2103 BEGIN
2104 fnd_request.set_org_id(g_org_id);
2105 x_request_id :=
2106 fnd_request.submit_request(
2107 application => 'CN'
2108 , program => x_conc_program
2109 , description => NULL
2110 , start_time => NULL
2111 , sub_request => NULL
2112 , argument1 => x_parent_proc_audit_id
2113 , argument2 => x_logical_process
2114 , argument3 => x_physical_process
2115 , argument4 => x_physical_batch_id
2116 );
2117
2118 IF (x_request_id = 0 OR x_request_id IS NULL) THEN
2119 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
2120 fnd_log.STRING(
2121 fnd_log.level_error
2122 , 'cn.plsql.cn_proc_batches_pkg.conc_submit.submission_status'
2123 , 'Submission failure for batch: ' || x_physical_batch_id
2124 );
2125 END IF;
2126
2127 fnd_file.put_line(fnd_file.LOG, 'Submission failure for batch' || x_physical_batch_id);
2128 cn_message_pkg.DEBUG('Failed to submit concurrent request (batch ID=' || x_physical_batch_id
2129 || ')');
2130 cn_message_pkg.DEBUG(fnd_message.get);
2131 -- raise conc_fail;
2132 ELSE
2133 cn_message_pkg.FLUSH;
2134 COMMIT;
2135 END IF;
2136 END conc_submit;
2137
2138 -- Procedure Name
2139 -- Conc_Dispatch
2140 -- Purpose
2141 -- Performs a process on all physical batches in the logical batch
2142 -- before moving on to the next process.
2143 -- e.g will classify all transactions in the logical batch and then
2144 -- move on to population etc
2145
2146 -- Submits independent concurrent programs for each physical batch.
2147 -- These physical batches will be executed in parallel.
2148 -- A subsequent physical process cannot begin until all physical
2149 -- batches in its prerequisite process have completed.
2150 PROCEDURE conc_dispatch(x_parent_proc_audit_id NUMBER) IS
2151 TYPE num_tbl IS TABLE OF NUMBER(15)
2152 INDEX BY BINARY_INTEGER;
2153
2154 TYPE str30_tbl IS TABLE OF VARCHAR2(30)
2155 INDEX BY BINARY_INTEGER;
2156
2157 l_primary_request_stack num_tbl;
2158 l_primary_batch_stack num_tbl;
2159 l_current_phase_stack str30_tbl;
2160 l_process_order num_tbl;
2161 n NUMBER := 0;
2162 x_batch_total NUMBER := 0;
2163 l_temp_id NUMBER := 0;
2164 l_new_status VARCHAR2(30) := NULL;
2165 l_curr_status VARCHAR2(30) := NULL;
2166 l_curr_process VARCHAR2(30) := NULL;
2167 l_temp_phys_batch_id NUMBER;
2168 primary_ptr NUMBER := 1; -- Must start at 1
2169 l_dev_phase VARCHAR2(80);
2170 l_dev_status VARCHAR2(80);
2171 l_request_id NUMBER;
2172 l_completed_revert_count NUMBER := 0;
2173 l_completed_classify_count NUMBER := 0;
2174 l_completed_rollup_count NUMBER := 0;
2175 l_completed_populate_count NUMBER := 0;
2176 l_completed_calculate_count NUMBER := 0;
2177 l_call_status BOOLEAN;
2178 l_next_process VARCHAR2(30);
2179 l_dummy VARCHAR2(2000);
2180 unfinished BOOLEAN := TRUE;
2181 l_user_id NUMBER(15) := fnd_global.user_id;
2182 l_resp_id NUMBER(15) := fnd_global.resp_id;
2183 l_login_id NUMBER(15) := fnd_global.login_id;
2184 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
2185 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
2186 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
2187 debug_v NUMBER;
2188 l_sleep_time NUMBER := 180;
2189 l_sleep_time_char VARCHAR2(30);
2190 l_failed_request_id NUMBER;
2191 l_intelligent_flag VARCHAR2(1);
2192 l_start_date DATE;
2193 l_end_date DATE;
2194 l_payee_count NUMBER;
2195 l_new_request_submitted BOOLEAN;
2196 BEGIN
2197 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2198 fnd_log.STRING(
2199 fnd_log.level_procedure
2200 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.begin'
2201 , 'Beginning of conc_dispatch...'
2202 );
2203 END IF;
2204
2205 -- Flood procedure calls
2206 -- flood_salesreps;
2207 flood_rev_classes;
2208 cn_message_pkg.FLUSH;
2209 COMMIT;
2210
2211 SELECT intelligent_flag, start_date, end_date
2212 INTO l_intelligent_flag, l_start_date, l_end_date
2213 FROM cn_calc_submission_batches
2214 WHERE logical_batch_id = g_logical_batch_id;
2215
2216 IF g_calc_type = 'BONUS' THEN
2217 IF l_curr_process IS NULL THEN
2218 l_curr_process := g_revert;
2219 ELSE
2220 l_curr_process := g_calculation; --g_logical_process;
2221 END IF;
2222 ELSE
2223 next_process(x_physical_process => l_curr_process);
2224 END IF;
2225
2226 l_payee_count := 0;
2227
2228 SELECT COUNT(*)
2229 INTO l_payee_count
2230 FROM cn_srp_payee_assigns a
2231 WHERE a.start_date <= l_end_date AND(a.end_date IS NULL OR a.end_date >= l_start_date);
2232
2233 FOR physical_rec IN physical_batches LOOP
2234 conc_submit(
2235 x_conc_program => 'BATCH_RUNNER'
2236 , x_parent_proc_audit_id => x_parent_proc_audit_id
2237 , x_logical_process => g_logical_process
2238 , x_physical_process => l_curr_process
2239 , x_physical_batch_id => physical_rec.physical_batch_id
2240 , x_request_id => l_temp_id
2241 );
2242 x_batch_total := x_batch_total + 1;
2243 l_primary_batch_stack(x_batch_total) := physical_rec.physical_batch_id;
2244 l_primary_request_stack(x_batch_total) := l_temp_id;
2245 l_current_phase_stack(x_batch_total) := l_curr_process;
2246
2247 cn_message_pkg.debug(
2248 'Submitted request for Physical Batch ' || physical_rec.physical_batch_id ||
2249 ' for process ' || l_curr_process || ' : Request = ' || l_temp_id
2250 );
2251
2252 IF (l_temp_id = 0 OR l_temp_id IS NULL) THEN
2253 l_temp_phys_batch_id := physical_rec.physical_batch_id;
2254 l_failed_request_id := l_temp_id;
2255 RAISE conc_fail;
2256 END IF;
2257 END LOOP;
2258 l_new_request_submitted := TRUE;
2259
2260 UPDATE cn_process_batches_all
2261 SET trx_batch_id = l_primary_batch_stack(x_batch_total)
2262 WHERE physical_batch_id = l_primary_batch_stack(x_batch_total);
2263
2264 COMMIT;
2265
2266 -- batches should be sorted by commission_headers_count in classification/rollup phase
2267 n := 1;
2268 FOR p_rec IN physical_batches2 LOOP
2269 FOR i IN 1 .. x_batch_total LOOP
2270 IF (l_primary_batch_stack(i) = p_rec.physical_batch_id) THEN
2271 l_process_order(n) := i;
2272 EXIT;
2273 END IF;
2274 END LOOP;
2275
2276 n := n + 1;
2277 END LOOP;
2278
2279 l_sleep_time_char := fnd_profile.VALUE('CN_SLEEP_TIME');
2280 IF l_sleep_time_char IS NOT NULL THEN
2281 l_sleep_time := TO_NUMBER(l_sleep_time_char);
2282 END IF;
2283
2284 WHILE unfinished LOOP
2285 /*
2286 * Bug#7265394 - Not sure whats been implemented here.
2287 * This can lead to bypassing SLEEP and calling FND_CONCURRENT.GET_REQUEST_STATUS
2288 * continuously. Therefore rewrote the logic in a different way.
2289 *
2290 IF (l_completed_revert_count = x_batch_total AND l_completed_classify_count = 0) THEN
2291 NULL;
2292 ELSIF(l_completed_classify_count = x_batch_total AND l_completed_rollup_count = 0) THEN
2293 NULL;
2294 ELSIF(l_completed_rollup_count = x_batch_total AND l_completed_populate_count = 0) THEN
2295 NULL;
2296 ELSIF(l_completed_populate_count = x_batch_total AND l_completed_calculate_count = 0) THEN
2297 NULL;
2298 ELSE
2299 DBMS_LOCK.sleep(l_sleep_time);
2300 END IF;
2301 */
2302
2303 cn_message_pkg.debug(
2304 'Check whether we can sleep for sometime to get the requests completed...'
2305 || ' Batch Total = ' || x_batch_total
2306 || ' : Reverted = ' || l_completed_revert_count
2307 || ' : Classified = ' || l_completed_classify_count
2308 || ' : Rolled = ' || l_completed_rollup_count
2309 || ' : Populated = ' || l_completed_populate_count
2310 || ' : Calculated = ' || l_completed_calculate_count
2311 );
2312
2313 IF l_new_request_submitted THEN
2314 cn_message_pkg.debug('A new request has been submitted.. Lets check once more whether any other request has completed.');
2315 l_new_request_submitted := FALSE;
2316 ELSE
2317 cn_message_pkg.debug('There is no change evident in this iteration. Therefore sleep for ' || l_sleep_time);
2318 DBMS_LOCK.sleep(l_sleep_time);
2319 END IF;
2320
2321 FOR i IN 1 .. x_batch_total LOOP
2322 primary_ptr := l_process_order(i);
2323
2324 IF (l_primary_request_stack(primary_ptr) IS NOT NULL) THEN
2325 l_call_status :=
2326 fnd_concurrent.get_request_status(
2327 request_id => l_primary_request_stack(primary_ptr)
2328 , phase => l_dummy
2329 , status => l_dummy
2330 , dev_phase => l_dev_phase
2331 , dev_status => l_dev_status
2332 , MESSAGE => l_dummy
2333 );
2334
2335 IF (NOT l_call_status) THEN
2336 l_failed_request_id := l_primary_request_stack(primary_ptr);
2337 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2338
2339 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2340 fnd_log.STRING(
2341 fnd_log.level_unexpected
2342 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.request_status'
2343 , 'Request ' || l_failed_request_id || ' failed (batch_id = ' || l_temp_phys_batch_id
2344 );
2345 END IF;
2346
2347 cn_message_pkg.DEBUG('Concurrent Request#' || l_failed_request_id || ' for Physical Batch#' || l_temp_phys_batch_id || ' completed with error');
2348 fnd_file.put_line(fnd_file.LOG, 'Conc_dispatch: Request completed with error for ' || l_failed_request_id);
2349 fnd_file.put_line(fnd_file.LOG, 'Conc_dispatch: Request failed for physical batch' || l_temp_phys_batch_id);
2350 RAISE conc_fail;
2351 END IF;
2352
2353 IF l_dev_phase = 'COMPLETE' THEN
2354 l_failed_request_id := l_primary_request_stack(primary_ptr);
2355 l_primary_request_stack(primary_ptr) := NULL;
2356
2357 IF (l_current_phase_stack(primary_ptr) = g_revert) THEN
2358 l_completed_revert_count := l_completed_revert_count + 1;
2359 ELSIF(l_current_phase_stack(primary_ptr) = g_classification) THEN
2360 l_completed_classify_count := l_completed_classify_count + 1;
2361 ELSIF(l_current_phase_stack(primary_ptr) = g_rollup) THEN
2362 l_completed_rollup_count := l_completed_rollup_count + 1;
2363
2364 -- upon completion of rollup phase, switch the processing order back to lines_total-based
2365 IF (l_completed_rollup_count = x_batch_total) THEN
2366 FOR x IN 1 .. x_batch_total LOOP
2367 l_process_order(x) := x;
2368 END LOOP;
2369
2370 EXIT;
2371 END IF;
2372 ELSIF(l_current_phase_stack(primary_ptr) = g_population) THEN
2373 l_completed_populate_count := l_completed_populate_count + 1;
2374 ELSIF(l_current_phase_stack(primary_ptr) = g_calculation) THEN
2375 l_completed_calculate_count := l_completed_calculate_count + 1;
2376 END IF;
2377
2378 IF l_dev_status IN('ERROR', 'TERMINATING', 'TERMINATED', 'DELETED') THEN
2379 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2380
2381 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
2382 fnd_log.STRING(
2383 fnd_log.level_error
2384 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.request_status'
2385 , 'Request ' || l_failed_request_id || ' failed (batch_id = '
2386 || l_temp_phys_batch_id
2387 );
2388 END IF;
2389
2390 cn_message_pkg.DEBUG('Concurrent Request#' || l_failed_request_id || ' for Physical Batch#' || l_temp_phys_batch_id || ' completed with error');
2391 fnd_file.put_line(fnd_file.LOG, 'Conc_dispatch: Request completed with error for ' || l_failed_request_id);
2392 fnd_file.put_line(fnd_file.LOG, 'Conc_dispatch: Request failed for physical_batch' || l_temp_phys_batch_id);
2393 RAISE conc_fail;
2394 END IF; -- If error
2395 END IF; -- If complete
2396 END IF; -- If request_id is not null
2397
2398 -- if waiting to proceed, then check the conditions for moving on
2399 IF (l_primary_request_stack(primary_ptr) IS NULL) THEN
2400 l_curr_process := NULL;
2401
2402 -- check whether the current batch can start the next phase
2403 IF (l_current_phase_stack(primary_ptr) = g_revert) THEN
2404 -- go to classification phase directly
2405 l_curr_process := l_current_phase_stack(primary_ptr);
2406 next_process(x_physical_process => l_curr_process);
2407 ELSIF(l_current_phase_stack(primary_ptr) = g_classification) THEN
2408 -- if all batches are done with revert phase, then start rollup phase
2409 IF (l_completed_revert_count = x_batch_total) THEN
2410 IF (l_intelligent_flag = 'Y') THEN
2411 -- for intelligent calculation, the first batch should complete rollup phase
2412 -- before the other batches do
2413 IF (l_completed_rollup_count = 0 AND primary_ptr = 1) THEN
2414 l_curr_process := l_current_phase_stack(primary_ptr);
2415 next_process(x_physical_process => l_curr_process);
2416 ELSIF(l_completed_rollup_count > 0) THEN
2417 l_curr_process := l_current_phase_stack(primary_ptr);
2418 next_process(x_physical_process => l_curr_process);
2419 END IF;
2420 ELSE
2421 l_curr_process := l_current_phase_stack(primary_ptr);
2422 next_process(x_physical_process => l_curr_process);
2423 END IF;
2424 END IF;
2425 ELSIF(l_current_phase_stack(primary_ptr) = g_rollup) THEN
2426 -- if all batches are done with rollup phase, then start population phase
2427 IF (l_completed_rollup_count = x_batch_total) THEN
2428 l_curr_process := l_current_phase_stack(primary_ptr);
2429 next_process(x_physical_process => l_curr_process);
2430 END IF;
2431 ELSIF(l_current_phase_stack(primary_ptr) = g_population) THEN
2432 IF l_payee_count > 0 THEN
2433 IF (
2434 (
2435 primary_ptr = x_batch_total
2436 AND l_completed_calculate_count =(x_batch_total - 1)
2437 )
2438 OR (primary_ptr < x_batch_total)
2439 ) THEN
2440 l_curr_process := l_current_phase_stack(primary_ptr);
2441 next_process(x_physical_process => l_curr_process);
2442 END IF;
2443 ELSE
2444 l_curr_process := l_current_phase_stack(primary_ptr);
2445 next_process(x_physical_process => l_curr_process);
2446 END IF;
2447 ELSIF(l_current_phase_stack(primary_ptr) = g_calculation) THEN
2448 IF (l_completed_calculate_count = x_batch_total) THEN
2449 cn_message_pkg.DEBUG('All concurrent requests complete phase ' || g_logical_process);
2450 unfinished := FALSE;
2451 END IF;
2452 END IF;
2453
2454 -- submit request for next phase if all the conditions to proceed are met
2455 IF (l_curr_process IS NOT NULL) THEN
2456 conc_submit(
2457 x_conc_program => 'BATCH_RUNNER'
2458 , x_parent_proc_audit_id => x_parent_proc_audit_id
2459 , x_logical_process => g_logical_process
2460 , x_physical_process => l_curr_process
2461 , x_physical_batch_id => l_primary_batch_stack(primary_ptr)
2462 , x_request_id => l_temp_id
2463 );
2464 l_primary_request_stack(primary_ptr) := l_temp_id;
2465 l_current_phase_stack(primary_ptr) := l_curr_process;
2466 l_new_request_submitted := TRUE;
2467
2468 cn_message_pkg.debug(
2469 'Moving Physical Batch#' || l_primary_batch_stack(primary_ptr) ||
2470 ' to next process ' || l_curr_process || ' Request = ' || l_temp_id
2471 );
2472
2473 IF (l_temp_id = 0 OR l_temp_id IS NULL) THEN
2474 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2475 l_failed_request_id := l_temp_id;
2476 RAISE conc_fail;
2477 END IF;
2478 END IF; -- If l_curr_process is not null
2479 END IF; -- If request_id is null
2480 END LOOP; -- for primary_ptr in 1..x_batch_total
2481 END LOOP; -- while unfinished loop
2482
2483 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2484 fnd_log.STRING(
2485 fnd_log.level_procedure
2486 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.end'
2487 , 'End of conc_dispatch.'
2488 );
2489 END IF;
2490 EXCEPTION
2491 WHEN conc_fail THEN
2492 fnd_file.put_line(fnd_file.LOG, 'conc_fail exception in cn_proc_batches_pkg.conc_dispatch');
2493 update_error(l_temp_phys_batch_id);
2494
2495 -- canceling running/pending requests
2496 IF (l_primary_request_stack.COUNT > 0) THEN
2497 FOR i IN l_primary_request_stack.FIRST .. l_primary_request_stack.LAST LOOP
2498 IF (l_primary_request_stack(i) > 0) THEN
2499 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2500 fnd_log.STRING(
2501 fnd_log.level_exception
2502 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.exception'
2503 , 'Cancelling request: ' || l_primary_request_stack(i)
2504 );
2505 END IF;
2506
2507 l_call_status := fnd_concurrent.cancel_request(l_primary_request_stack(i), l_dummy);
2508 cn_message_pkg.DEBUG('Cancelling request (ID=' || l_primary_request_stack(i)
2509 || ' Status=' || l_dummy || ')');
2510 END IF;
2511 END LOOP;
2512 END IF;
2513
2514 cn_message_pkg.end_batch(x_parent_proc_audit_id);
2515 WHEN OTHERS THEN
2516 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2517 fnd_log.STRING(fnd_log.level_unexpected
2518 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch.exception', SQLERRM);
2519 END IF;
2520
2521 fnd_file.put_line(fnd_file.LOG, 'unexpected exception in cn_proc_batches_pkg.conc_dispatch');
2522 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.conc_dispatch:');
2523 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
2524 RAISE;
2525 END conc_dispatch;
2526
2527 PROCEDURE conc_dispatch2(x_parent_proc_audit_id NUMBER) IS
2528 TYPE requests IS TABLE OF NUMBER(15)
2529 INDEX BY BINARY_INTEGER;
2530
2531 TYPE str30_tbl IS TABLE OF VARCHAR2(30)
2532 INDEX BY BINARY_INTEGER;
2533
2534 l_primary_request_stack requests;
2535 l_primary_batch_stack requests;
2536 l_current_phase_stack str30_tbl;
2537 l_process_order requests;
2538 n NUMBER := 0;
2539 g_batch_total NUMBER := 0;
2540 l_temp_id NUMBER := 0;
2541 l_new_status VARCHAR2(30) := NULL;
2542 l_curr_status VARCHAR2(30) := NULL;
2543 l_curr_process VARCHAR2(30) := NULL;
2544 l_temp_phys_batch_id NUMBER;
2545 primary_ptr NUMBER := 1;
2546 l_dev_phase VARCHAR2(80);
2547 l_dev_status VARCHAR2(80);
2548 l_request_id NUMBER;
2549 l_completed_revert_count NUMBER := 0;
2550 l_completed_classify_count NUMBER := 0;
2551 l_completed_rollup_count NUMBER := 0;
2552 l_completed_populate_count NUMBER := 0;
2553 l_completed_calculate_count NUMBER := 0;
2554 l_call_status BOOLEAN;
2555 l_next_process VARCHAR2(30);
2556 l_dummy1 VARCHAR2(2000);
2557 l_dummy2 VARCHAR2(500);
2558 l_dummy3 VARCHAR2(500);
2559 unfinished BOOLEAN := TRUE;
2560 l_user_id NUMBER(15) := fnd_global.user_id;
2561 l_resp_id NUMBER(15) := fnd_global.resp_id;
2562 l_login_id NUMBER(15) := fnd_global.login_id;
2563 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
2564 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
2565 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
2566 l_sleep_time_char VARCHAR2(30);
2567 l_sleep_time NUMBER := 180;
2568 l_failed_request_id NUMBER;
2569 g_first_run VARCHAR2(1) := 'Y';
2570 l_return_status VARCHAR2(30);
2571 l_msg_count NUMBER;
2572 l_msg_data VARCHAR2(2000);
2573 l_flag VARCHAR2(30);
2574 l_intelligent_flag VARCHAR2(1);
2575 l_parent_request_id NUMBER;
2576 l_start_date DATE;
2577 l_end_date DATE;
2578 l_payee_count NUMBER;
2579 l_new_request_submitted BOOLEAN;
2580
2581 CURSOR parent_request IS
2582 SELECT MAX(fcr.parent_request_id)
2583 FROM fnd_concurrent_requests fcr
2584 WHERE fcr.program_application_id = 283
2585 AND fcr.concurrent_program_id =
2586 (SELECT concurrent_program_id
2587 FROM fnd_concurrent_programs
2588 WHERE application_id = 283 AND concurrent_program_name = 'BATCH_RUNNER')
2589 AND fcr.phase_code = 'C'
2590 AND fcr.status_code <> 'C'
2591 AND EXISTS(
2592 SELECT 1
2593 FROM cn_process_batches
2594 WHERE logical_batch_id = g_logical_batch_id
2595 AND physical_batch_id = fcr.argument4);
2596
2597 CURSOR success_phase(p_physical_batch_id NUMBER) IS
2598 SELECT fcr.argument3 phase
2599 FROM fnd_concurrent_requests fcr
2600 WHERE fcr.parent_request_id = l_parent_request_id
2601 AND fcr.phase_code = 'C'
2602 AND fcr.status_code = 'C'
2603 AND argument4 = p_physical_batch_id
2604 ORDER BY request_id DESC;
2605
2606 CURSOR physical_batches IS
2607 SELECT DISTINCT physical_batch_id
2608 FROM cn_process_batches_all
2609 WHERE logical_batch_id = g_logical_batch_id
2610 ORDER BY physical_batch_id DESC;
2611 BEGIN
2612 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2613 fnd_log.STRING(
2614 fnd_log.level_procedure
2615 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.begin'
2616 , 'Beginning of conc_dispatch2...'
2617 );
2618 END IF;
2619
2620 cn_message_pkg.FLUSH;
2621 COMMIT;
2622
2623 SELECT intelligent_flag, start_date, end_date
2624 INTO l_intelligent_flag, l_start_date, l_end_date
2625 FROM cn_calc_submission_batches_all
2626 WHERE logical_batch_id = g_logical_batch_id;
2627
2628 OPEN parent_request;
2629 FETCH parent_request INTO l_parent_request_id;
2630 CLOSE parent_request;
2631
2632 l_payee_count := 0;
2633
2634 SELECT COUNT(*)
2635 INTO l_payee_count
2636 FROM cn_srp_payee_assigns a
2637 WHERE a.start_date <= l_end_date AND(a.end_date IS NULL OR a.end_date >= l_start_date);
2638
2639 -- get highest successful phase of completion for each batch
2640 FOR physical_batch IN physical_batches LOOP
2641 l_curr_process := NULL;
2642
2643 OPEN success_phase(physical_batch.physical_batch_id);
2644 FETCH success_phase INTO l_curr_process;
2645 CLOSE success_phase;
2646
2647 g_batch_total := g_batch_total + 1;
2648 l_primary_batch_stack(g_batch_total) := physical_batch.physical_batch_id;
2649 l_primary_request_stack(g_batch_total) := NULL;
2650 l_current_phase_stack(g_batch_total) := l_curr_process;
2651
2652 IF (l_curr_process = g_revert) THEN
2653 l_completed_revert_count := l_completed_revert_count + 1;
2654 ELSIF(l_curr_process = g_classification) THEN
2655 l_completed_revert_count := l_completed_revert_count + 1;
2656 l_completed_classify_count := l_completed_classify_count + 1;
2657 ELSIF(l_curr_process = g_rollup) THEN
2658 l_completed_revert_count := l_completed_revert_count + 1;
2659 l_completed_classify_count := l_completed_classify_count + 1;
2660 l_completed_rollup_count := l_completed_rollup_count + 1;
2661 ELSIF(l_curr_process = g_population) THEN
2662 l_completed_revert_count := l_completed_revert_count + 1;
2663 l_completed_classify_count := l_completed_classify_count + 1;
2664 l_completed_rollup_count := l_completed_rollup_count + 1;
2665 l_completed_populate_count := l_completed_populate_count + 1;
2666 ELSIF(l_curr_process = g_calculation) THEN
2667 l_completed_revert_count := l_completed_revert_count + 1;
2668 l_completed_classify_count := l_completed_classify_count + 1;
2669 l_completed_rollup_count := l_completed_rollup_count + 1;
2670 l_completed_populate_count := l_completed_populate_count + 1;
2671 l_completed_calculate_count := l_completed_calculate_count + 1;
2672 END IF;
2673 END LOOP;
2674
2675 UPDATE cn_process_batches_all
2676 SET trx_batch_id = l_primary_batch_stack(g_batch_total)
2677 WHERE physical_batch_id = l_primary_batch_stack(g_batch_total);
2678
2679 COMMIT;
2680 -- batches should be sorted by commission_headers_count in classification/rollup phase
2681 n := 1;
2682
2683 IF (l_completed_revert_count = g_batch_total AND l_completed_rollup_count < g_batch_total) THEN
2684 FOR p_rec IN physical_batches2 LOOP
2685 FOR i IN 1 .. g_batch_total LOOP
2686 IF (l_primary_batch_stack(i) = p_rec.physical_batch_id) THEN
2687 l_process_order(n) := i;
2688 EXIT;
2689 END IF;
2690 END LOOP;
2691
2692 n := n + 1;
2693 END LOOP;
2694 ELSE
2695 FOR i IN 1 .. g_batch_total LOOP
2696 l_process_order(n) := i;
2697 n := n + 1;
2698 END LOOP;
2699 END IF;
2700
2701 l_sleep_time_char := fnd_profile.VALUE('CN_SLEEP_TIME');
2702 IF l_sleep_time_char IS NOT NULL THEN
2703 l_sleep_time := TO_NUMBER(l_sleep_time_char);
2704 END IF;
2705
2706 l_new_request_submitted := FALSE;
2707
2708 WHILE unfinished LOOP
2709 FOR i IN 1 .. g_batch_total LOOP
2710 primary_ptr := l_process_order(i);
2711
2712 IF (l_primary_request_stack(primary_ptr) IS NOT NULL) THEN
2713 l_call_status :=
2714 fnd_concurrent.get_request_status(
2715 request_id => l_primary_request_stack(primary_ptr)
2716 , phase => l_dummy1
2717 , status => l_dummy2
2718 , dev_phase => l_dev_phase
2719 , dev_status => l_dev_status
2720 , MESSAGE => l_dummy3
2721 );
2722
2723 IF (NOT l_call_status) THEN
2724 l_failed_request_id := l_primary_request_stack(primary_ptr);
2725 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2726
2727 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2728 fnd_log.STRING(
2729 fnd_log.level_unexpected
2730 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.request_status'
2731 , 'Request ' || l_failed_request_id || ' failed (batch_id = ' || l_temp_phys_batch_id
2732 );
2733 END IF;
2734
2735 cn_message_pkg.DEBUG('Concurrent request completed with error (request ID='
2736 || l_failed_request_id || ')');
2737 cn_message_pkg.DEBUG('(physical batch ID=' || l_temp_phys_batch_id || ')');
2738 fnd_file.put_line(
2739 fnd_file.LOG
2740 , 'Conc_dispatch2: Request completed with error for ' || l_failed_request_id
2741 );
2742 fnd_file.put_line(
2743 fnd_file.LOG
2744 , 'Conc_dispatch2: Request failed for physical batch' || l_temp_phys_batch_id
2745 );
2746 RAISE conc_fail;
2747 END IF;
2748
2749 IF l_dev_phase = 'COMPLETE' THEN
2750 l_failed_request_id := l_primary_request_stack(primary_ptr);
2751 l_primary_request_stack(primary_ptr) := NULL;
2752
2753 IF (l_current_phase_stack(primary_ptr) = g_revert) THEN
2754 l_completed_revert_count := l_completed_revert_count + 1;
2755 ELSIF(l_current_phase_stack(primary_ptr) = g_classification) THEN
2756 l_completed_classify_count := l_completed_classify_count + 1;
2757 ELSIF(l_current_phase_stack(primary_ptr) = g_rollup) THEN
2758 l_completed_rollup_count := l_completed_rollup_count + 1;
2759
2760 -- upon completion of rollup phase, swith the processing order back to lines_total-based
2761 IF (l_completed_rollup_count = g_batch_total) THEN
2762 FOR x IN 1 .. g_batch_total LOOP
2763 l_process_order(x) := x;
2764 END LOOP;
2765
2766 EXIT;
2767 END IF;
2768 ELSIF(l_current_phase_stack(primary_ptr) = g_population) THEN
2769 l_completed_populate_count := l_completed_populate_count + 1;
2770 ELSIF(l_current_phase_stack(primary_ptr) = g_calculation) THEN
2771 l_completed_calculate_count := l_completed_calculate_count + 1;
2772 END IF;
2773
2774 IF l_dev_status IN('ERROR', 'TERMINATING', 'TERMINATED', 'DELETED') THEN
2775 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2776
2777 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
2778 fnd_log.STRING(
2779 fnd_log.level_error
2780 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.request_status'
2781 , 'Request ' || l_failed_request_id || ' failed (batch_id = '
2782 || l_temp_phys_batch_id
2783 );
2784 END IF;
2785
2786 cn_message_pkg.DEBUG('Concurrent request completed with error (request ID='
2787 || l_failed_request_id || ')');
2788 cn_message_pkg.DEBUG('(physical batch ID=' || l_temp_phys_batch_id || ')');
2789 fnd_file.put_line(
2790 fnd_file.LOG
2791 , 'Conc_dispatch2: Request completed with error for ' || l_failed_request_id
2792 );
2793 fnd_file.put_line(
2794 fnd_file.LOG
2795 , 'Conc_dispatch2: Request failed for physical_batch' || l_temp_phys_batch_id
2796 );
2797 RAISE conc_fail;
2798 END IF; -- If error
2799 END IF; -- If complete
2800 END IF; -- If request_id not null
2801
2802 -- if waiting to proceed, then check the conditions for moving on
2803 IF (l_primary_request_stack(primary_ptr) IS NULL) THEN
2804 l_curr_process := NULL;
2805
2806 -- check whether the current batch can start the next phase
2807 IF (l_current_phase_stack(primary_ptr) = g_revert) THEN
2808 -- go to classification phase directly
2809 l_curr_process := l_current_phase_stack(primary_ptr);
2810 next_process(x_physical_process => l_curr_process);
2811 ELSIF(l_current_phase_stack(primary_ptr) = g_classification) THEN
2812 -- if all batches are done with revert phase, then start rollup phase
2813 IF (l_completed_revert_count = g_batch_total) THEN
2814 IF (l_intelligent_flag = 'Y') THEN
2815 -- for intelligent calculation, the first batch should complete rollup phase
2816 -- before the other batches do
2817 IF (l_completed_rollup_count = 0 AND primary_ptr = 1) THEN
2818 l_curr_process := l_current_phase_stack(primary_ptr);
2819 next_process(x_physical_process => l_curr_process);
2820 ELSIF(l_completed_rollup_count > 0) THEN
2821 l_curr_process := l_current_phase_stack(primary_ptr);
2822 next_process(x_physical_process => l_curr_process);
2823 END IF;
2824 ELSE
2825 l_curr_process := l_current_phase_stack(primary_ptr);
2826 next_process(x_physical_process => l_curr_process);
2827 END IF;
2828 END IF;
2829 ELSIF(l_current_phase_stack(primary_ptr) = g_rollup) THEN
2830 -- if all batches are done with rollup phase, then start population phase
2831 IF (l_completed_rollup_count = g_batch_total) THEN
2832 l_curr_process := l_current_phase_stack(primary_ptr);
2833 next_process(x_physical_process => l_curr_process);
2834 END IF;
2835 ELSIF(l_current_phase_stack(primary_ptr) = g_population) THEN
2836 IF l_payee_count > 0 THEN
2837 IF (
2838 (
2839 primary_ptr = g_batch_total
2840 AND l_completed_calculate_count =(g_batch_total - 1)
2841 )
2842 OR (primary_ptr < g_batch_total)
2843 ) THEN
2844 l_curr_process := l_current_phase_stack(primary_ptr);
2845 next_process(x_physical_process => l_curr_process);
2846 END IF;
2847 ELSE
2848 l_curr_process := l_current_phase_stack(primary_ptr);
2849 next_process(x_physical_process => l_curr_process);
2850 END IF;
2851 ELSIF(l_current_phase_stack(primary_ptr) = g_calculation) THEN
2852 IF (l_completed_calculate_count = g_batch_total) THEN
2853 cn_message_pkg.DEBUG('All requests complete phase ' || g_logical_process);
2854 unfinished := FALSE;
2855 END IF;
2856 END IF;
2857
2858 -- submit request for next phase if all the conditions to proceed are met
2859 IF (l_curr_process IS NOT NULL) THEN
2860 conc_submit(
2861 x_conc_program => 'BATCH_RUNNER'
2862 , x_parent_proc_audit_id => x_parent_proc_audit_id
2863 , x_logical_process => g_logical_process
2864 , x_physical_process => l_curr_process
2865 , x_physical_batch_id => l_primary_batch_stack(primary_ptr)
2866 , x_request_id => l_temp_id
2867 );
2868 l_primary_request_stack(primary_ptr) := l_temp_id;
2869 l_current_phase_stack(primary_ptr) := l_curr_process;
2870 l_new_request_submitted := TRUE;
2871
2872 cn_message_pkg.debug(
2873 'Moving Physical Batch#' || l_primary_batch_stack(primary_ptr) ||
2874 ' to next process ' || l_curr_process || ' Request = ' || l_temp_id
2875 );
2876
2877 IF (l_temp_id = 0 OR l_temp_id IS NULL) THEN
2878 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
2879 l_failed_request_id := l_temp_id;
2880 RAISE conc_fail;
2881 END IF;
2882 END IF; -- If l_curr_process is not null
2883 END IF; -- If request_id is null
2884 END LOOP; -- for primary_pointer in 1..g_batch_total
2885
2886 /*
2887 * Bug#7265394 - Not sure whats been implemented here.
2888 * This can lead to bypassing SLEEP and calling FND_CONCURRENT.GET_REQUEST_STATUS
2889 * continuously. Therefore rewrote the logic in a different way.
2890 *
2891 IF (l_completed_revert_count = g_batch_total AND l_completed_classify_count = 0) THEN
2892 NULL;
2893 ELSIF(l_completed_classify_count = g_batch_total AND l_completed_rollup_count = 0) THEN
2894 NULL;
2895 ELSIF(l_completed_rollup_count = g_batch_total AND l_completed_populate_count = 0) THEN
2896 NULL;
2897 ELSIF(l_completed_populate_count = g_batch_total AND l_completed_calculate_count = 0) THEN
2898 NULL;
2899 ELSE
2900 DBMS_LOCK.sleep(l_sleep_time);
2901 END IF;
2902 */
2903
2904 cn_message_pkg.debug(
2905 'Check whether we can sleep for sometime to get the requests completed...'
2906 || ' Batch Total = ' || g_batch_total
2907 || ' : Reverted = ' || l_completed_revert_count
2908 || ' : Classified = ' || l_completed_classify_count
2909 || ' : Rolled = ' || l_completed_rollup_count
2910 || ' : Populated = ' || l_completed_populate_count
2911 || ' : Calculated = ' || l_completed_calculate_count
2912 );
2913
2914 IF l_new_request_submitted THEN
2915 cn_message_pkg.debug('A new request has been submitted.. Lets check once more whether any other request has completed.');
2916 l_new_request_submitted := FALSE;
2917 ELSE
2918 cn_message_pkg.debug('There is no change evident in this iteration. Therefore sleep for ' || l_sleep_time);
2919 DBMS_LOCK.sleep(l_sleep_time);
2920 END IF;
2921
2922 END LOOP;
2923
2924 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
2925 fnd_log.STRING(
2926 fnd_log.level_procedure
2927 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.end'
2928 , 'End of conc_dispatch.'
2929 );
2930 END IF;
2931 EXCEPTION
2932 WHEN conc_fail THEN
2933 fnd_file.put_line(fnd_file.LOG, 'conc_fail exception in cn_proc_batches_pkg.conc_dispatch');
2934 update_error(l_temp_phys_batch_id);
2935
2936 -- canceling running/pending requests
2937 IF (l_primary_request_stack.COUNT > 0) THEN
2938 FOR i IN l_primary_request_stack.FIRST .. l_primary_request_stack.LAST LOOP
2939 IF (l_primary_request_stack(i) > 0) THEN
2940 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
2941 fnd_log.STRING(
2942 fnd_log.level_exception
2943 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.exception'
2944 , 'Cancelling request: ' || l_primary_request_stack(i)
2945 );
2946 END IF;
2947
2948 l_call_status := fnd_concurrent.cancel_request(l_primary_request_stack(i), l_dummy1);
2949 cn_message_pkg.DEBUG(
2950 'Cancelling request (ID=' || l_primary_request_stack(i) || ' Status=' || l_dummy1
2951 || ')'
2952 );
2953 END IF;
2954 END LOOP;
2955 END IF;
2956
2957 cn_message_pkg.DEBUG('Concurrent request failed (physical batch ID=' || l_temp_phys_batch_id
2958 || ')');
2959 cn_message_pkg.end_batch(x_parent_proc_audit_id);
2960 WHEN OTHERS THEN
2961 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
2962 fnd_log.STRING(
2963 fnd_log.level_unexpected
2964 , 'cn.plsql.cn_proc_batches_pkg.conc_dispatch2.exception'
2965 , SQLERRM
2966 );
2967 END IF;
2968
2969 fnd_file.put_line(fnd_file.LOG, 'unexpected exception in cn_proc_batches_pkg.conc_dispatch');
2970 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.conc_dispatch2:');
2971 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
2972 RAISE;
2973 END conc_dispatch2;
2974
2975 -- Procedure Name
2976 --
2977 -- Purpose
2978 -- Accept a logical process name and logical batch and execute all
2979 -- required physical processes.
2980 --
2981 -- Notes
2982 -- We must wait for a process to complete across the entire logical batch
2983 -- before executing the next process othwerwise we could start to
2984 -- calculate before classifying
2985 PROCEDURE seq_dispatch(x_parent_proc_audit_id NUMBER) IS
2986 l_dummy VARCHAR2(80);
2987 finished BOOLEAN := FALSE;
2988 l_new_status VARCHAR2(30) := NULL;
2989 l_curr_process VARCHAR2(30) := NULL;
2990 l_new_process VARCHAR2(30) := NULL;
2991 l_count NUMBER := 0;
2992 g_batch_total NUMBER;
2993
2994 CURSOR bc IS
2995 SELECT COUNT(DISTINCT physical_batch_id)
2996 FROM cn_process_batches_all
2997 WHERE logical_batch_id = g_logical_batch_id AND status_code = 'IN_USE';
2998 BEGIN
2999 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3000 fnd_log.STRING(
3001 fnd_log.level_procedure
3002 , 'cn.plsql.cn_proc_batches_pkg.seq_dispatch.begin'
3003 , 'Beginning of seq_dispatch...'
3004 );
3005 END IF;
3006
3007 OPEN bc;
3008 FETCH bc INTO g_batch_total;
3009 CLOSE bc;
3010
3011 flood_rev_classes;
3012 cn_message_pkg.FLUSH;
3013 COMMIT;
3014
3015 WHILE NOT finished LOOP
3016 IF g_calc_type = 'BONUS' THEN
3017 IF l_curr_process IS NULL THEN
3018 l_curr_process := g_revert;
3019 ELSE
3020 l_curr_process := g_calculation; --g_logical_process;
3021 END IF;
3022 ELSE
3023 next_process(x_physical_process => l_curr_process);
3024 END IF;
3025
3026 -- Any batch with an 'ERROR' status will not be selected
3027 FOR physical_rec IN physical_batches LOOP
3028 IF (l_curr_process = g_calculation) THEN
3029 l_count := l_count + 1;
3030
3031 IF (l_count = g_batch_total) THEN
3032 UPDATE cn_process_batches_all
3033 SET trx_batch_id = physical_rec.physical_batch_id
3034 WHERE physical_batch_id = physical_rec.physical_batch_id;
3035
3036 COMMIT;
3037 END IF;
3038 END IF;
3039
3040 runner(
3041 errbuf => l_dummy
3042 , retcode => l_dummy
3043 , p_parent_proc_audit_id => x_parent_proc_audit_id
3044 , p_logical_process => g_logical_process
3045 , p_physical_process => l_curr_process
3046 , p_physical_batch_id => physical_rec.physical_batch_id
3047 );
3048 END LOOP;
3049
3050 IF g_logical_process = l_curr_process THEN
3051 finished := TRUE;
3052 END IF;
3053 END LOOP;
3054
3055 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3056 fnd_log.STRING(
3057 fnd_log.level_procedure
3058 , 'cn.plsql.cn_proc_batches_pkg.seq_dispatch.end'
3059 , 'End of seq_dispatch.'
3060 );
3061 END IF;
3062 EXCEPTION
3063 WHEN OTHERS THEN
3064 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3065 fnd_log.STRING(fnd_log.level_unexpected
3066 , 'cn.plsql.cn_proc_batches_pkg.seq_dispatch.exception', SQLERRM);
3067 END IF;
3068
3069 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches.seq_dispatch:');
3070 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
3071 RAISE;
3072 END seq_dispatch;
3073
3074 /* ----------------------------------------------------------------------------
3075 | Public Routines |
3076 ----------------------------------------------------------------------------*/
3077 PROCEDURE calculate_batch(
3078 errbuf OUT NOCOPY VARCHAR2
3079 , retcode OUT NOCOPY NUMBER
3080 , p_calc_sub_batch_id IN cn_calc_submission_batches.calc_sub_batch_id%TYPE
3081 ) IS
3082 l_return_status VARCHAR2(30);
3083 l_msg_count NUMBER;
3084 l_msg_data VARCHAR2(2000);
3085 l_process_audit_id NUMBER;
3086 l_org_id NUMBER;
3087 BEGIN
3088 retcode := 0; -- success = 0, warning = 1, fail = 2
3089
3090 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3091 fnd_log.STRING(
3092 fnd_log.level_procedure
3093 , 'cn.plsql.cn_proc_batches_pkg.calculate_batch.begin'
3094 , 'Beginning of calculate_batch...'
3095 );
3096 END IF;
3097
3098 SELECT org_id
3099 INTO l_org_id
3100 FROM cn_calc_submission_batches
3101 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
3102
3103 cn_message_pkg.begin_batch(
3104 x_process_type => 'CALCULATION'
3105 , x_parent_proc_audit_id => l_process_audit_id
3106 , x_process_audit_id => l_process_audit_id
3107 , x_request_id => fnd_global.conc_request_id
3108 , p_org_id => l_org_id
3109 );
3110 fnd_file.put_line(fnd_file.LOG, 'Beginning of calculate_batch...');
3111 cn_message_pkg.DEBUG('Beginning of calculate_batch...');
3112
3113 UPDATE cn_calc_submission_batches_all
3114 SET process_audit_id = l_process_audit_id,
3115 status='PROCESSING'
3116 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
3117
3118 COMMIT;
3119 cn_calc_submission_pvt.calculate(
3120 p_api_version => 1.0
3121 , p_init_msg_list => fnd_api.g_true
3122 , p_validation_level => fnd_api.g_valid_level_full
3123 , x_return_status => l_return_status
3124 , x_msg_count => l_msg_count
3125 , x_msg_data => l_msg_data
3126 , p_calc_sub_batch_id => p_calc_sub_batch_id
3127 );
3128
3129 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3130 FOR l_counter IN 1 .. l_msg_count LOOP
3131 l_msg_data := fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false);
3132 fnd_file.put_line(fnd_file.LOG, l_msg_data);
3133 cn_message_pkg.DEBUG(l_msg_data);
3134 END LOOP;
3135
3136 retcode := 2;
3137 errbuf := l_msg_data;
3138 END IF;
3139
3140 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3141 fnd_log.STRING(
3142 fnd_log.level_procedure
3143 , 'cn.plsql.cn_proc_batches_pkg.calculate_batch.end'
3144 , 'End of calculate_batch.'
3145 );
3146 END IF;
3147
3148 fnd_file.put_line(fnd_file.LOG, 'End of the calculation process.');
3149 cn_message_pkg.DEBUG('End of the calculation process.');
3150 cn_message_pkg.end_batch(l_process_audit_id);
3151 EXCEPTION
3152 WHEN OTHERS THEN
3153 retcode := 2;
3154 errbuf := SQLERRM;
3155
3156 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3157 fnd_log.STRING(
3158 fnd_log.level_unexpected
3159 , 'cn.plsql.cn_proc_batches_pkg.calculate_batch.exception'
3160 , SQLERRM
3161 );
3162 END IF;
3163
3164 cn_message_pkg.end_batch(l_process_audit_id);
3165 END calculate_batch;
3166
3167 PROCEDURE calc(
3168 errbuf OUT NOCOPY VARCHAR2
3169 , retcode OUT NOCOPY NUMBER
3170 , p_batch_name VARCHAR2
3171 , p_start_date DATE
3172 , p_end_date DATE
3173 , p_calc_type VARCHAR2
3174 , p_salesrep_option VARCHAR2
3175 , p_hierarchy_flag VARCHAR2
3176 , p_intelligent_flag VARCHAR2
3177 , p_interval_type_id NUMBER
3178 , p_salesrep_id NUMBER
3179 , p_quota_id NUMBER
3180 ) IS
3181 l_calc_sub_batch_id NUMBER(15);
3182 l_calc_sub_entry_id NUMBER(15);
3183 l_process_audit_id NUMBER(15);
3184 l_process_status_code VARCHAR2(30);
3185 l_error_message VARCHAR2(200);
3186 l_counter NUMBER;
3187 l_org_id NUMBER;
3188
3189 CURSOR l_chk_start_date_csr IS
3190 SELECT 1
3191 FROM DUAL
3192 WHERE EXISTS(SELECT 1
3193 FROM cn_acc_period_statuses_v
3194 WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3195
3196 CURSOR l_chk_end_date_csr IS
3197 SELECT 1
3198 FROM DUAL
3199 WHERE EXISTS(SELECT 1
3200 FROM cn_acc_period_statuses_v
3201 WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3202
3203 CURSOR l_batch_name_csr IS
3204 SELECT 1
3205 FROM DUAL
3206 WHERE EXISTS(SELECT 1
3207 FROM cn_calc_submission_batches_all
3208 WHERE NAME = p_batch_name AND org_id = l_org_id);
3209
3210 l_incomplete_plan BOOLEAN := FALSE;
3211 BEGIN
3212 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3213 fnd_log.STRING(
3214 fnd_log.level_procedure
3215 , 'cn.plsql.cn_proc_batches_pkg.calc.begin'
3216 , 'Beginning of concurrent program calc ...'
3217 );
3218 END IF;
3219
3220 fnd_file.put_line(fnd_file.LOG, 'Beginning of concurrent program calc ... ');
3221 -- Concurrent Manager will call set_policy_context('S', user_selected_org)
3222 l_org_id := mo_global.get_current_org_id;
3223
3224 -- check uniqueness of batch name
3225 OPEN l_batch_name_csr;
3226 FETCH l_batch_name_csr INTO l_counter;
3227
3228 IF l_batch_name_csr%FOUND THEN
3229 CLOSE l_batch_name_csr;
3230
3231 fnd_message.set_name('CN', 'CN_CALC_SUB_EXISTS');
3232 fnd_message.set_token('BATCH_NAME'
3233 , cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
3234 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3235
3236 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3237 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3238 END IF;
3239
3240 RAISE ABORT;
3241 END IF;
3242
3243 CLOSE l_batch_name_csr;
3244
3245 -- check the validility of p_start_date and p_end_date
3246 IF p_start_date > p_end_date THEN
3247 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
3248 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3249
3250 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3251 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3252 END IF;
3253
3254 RAISE ABORT;
3255 ELSE
3256 OPEN l_chk_start_date_csr;
3257 FETCH l_chk_start_date_csr INTO l_counter;
3258
3259 IF l_chk_start_date_csr%NOTFOUND THEN
3260 fnd_message.set_name('CN', 'CN_CALC_SUB_OPEN_DATE');
3261 fnd_message.set_token('DATE', p_start_date);
3262 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3263
3264 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3265 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3266 END IF;
3267
3268 RAISE ABORT;
3269 END IF;
3270
3271 CLOSE l_chk_start_date_csr;
3272
3273 OPEN l_chk_end_date_csr;
3274 FETCH l_chk_end_date_csr INTO l_counter;
3275
3276 IF l_chk_end_date_csr%NOTFOUND THEN
3277 fnd_message.set_name('CN', 'CN_CALC_SUB_OPEN_DATE');
3278 fnd_message.set_token('DATE', p_end_date);
3279 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3280
3281 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3282 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation'
3283 , TRUE);
3284 END IF;
3285
3286 RAISE ABORT;
3287 END IF;
3288
3289 CLOSE l_chk_end_date_csr;
3290 END IF;
3291
3292 IF p_salesrep_option = 'USER_SPECIFY' AND p_salesrep_id IS NULL THEN
3293 fnd_message.set_name('CN', 'CN_CALC_NO_SALESREP');
3294 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3295
3296 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3297 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3298 END IF;
3299
3300 RAISE ABORT;
3301 END IF;
3302
3303 l_calc_sub_batch_id := cn_calc_sub_batches_pkg.get_calc_sub_batch_id;
3304 cn_calc_sub_batches_pkg.begin_record
3305 (
3306 p_operation => 'INSERT'
3307 , p_calc_sub_batch_id => l_calc_sub_batch_id
3308 , p_name => p_batch_name
3309 , p_start_date => p_start_date
3310 , p_end_date => p_end_date
3311 , p_calc_type => p_calc_type
3312 , p_salesrep_option => p_salesrep_option
3313 , p_hierarchy_flag => 'N'
3314 , --p_hierarchy_flag,
3315 p_concurrent_flag => 'Y'
3316 , -- always not on-line, so concurrently
3317 p_intelligent_flag => p_intelligent_flag
3318 , p_status => 'INCOMPLETE'
3319 , p_interval_type_id => p_interval_type_id
3320 , p_org_id => l_org_id
3321 );
3322
3323 IF p_salesrep_option = 'USER_SPECIFY' THEN
3324 l_calc_sub_entry_id := cn_calc_sub_entries_pkg.get_calc_sub_entry_id;
3325 cn_calc_sub_entries_pkg.begin_record(
3326 p_operation => 'INSERT'
3327 , p_calc_sub_batch_id => l_calc_sub_batch_id
3328 , p_calc_sub_entry_id => l_calc_sub_entry_id
3329 , p_salesrep_id => p_salesrep_id
3330 , p_hierarchy_flag => p_hierarchy_flag
3331 , p_org_id => l_org_id
3332 );
3333 END IF;
3334
3335 COMMIT;
3336
3337 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level) THEN
3338 fnd_log.STRING(
3339 fnd_log.level_event
3340 , 'cn.plsql.cn_proc_batches_pkg.calc.submission'
3341 , 'Successfully created submission records.'
3342 );
3343 END IF;
3344
3345 IF find_srp_incomplete_plan(l_calc_sub_batch_id) THEN
3346 fnd_file.put_line(fnd_file.LOG
3347 , 'Abort the process because there is rep with incomplete comp plans.');
3348 RAISE ABORT;
3349 ELSE
3350 calculation_submission(
3351 p_calc_sub_batch_id => l_calc_sub_batch_id
3352 , x_process_audit_id => l_process_audit_id
3353 , x_process_status_code => l_process_status_code
3354 );
3355 END IF;
3356
3357 IF (l_process_status_code = 'FAIL') THEN
3358 retcode := 2;
3359 errbuf := 'Calculation fails';
3360 END IF;
3361
3362 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3363 fnd_log.STRING(
3364 fnd_log.level_procedure
3365 , 'cn.plsql.cn_proc_batches_pkg.calc.end'
3366 , 'End of concurrent program calc.'
3367 );
3368 END IF;
3369 EXCEPTION
3370 WHEN fnd_file.utl_file_error THEN
3371 retcode := 2;
3372 errbuf := SUBSTR(fnd_message.get, 1, 254);
3373 WHEN ABORT THEN
3374 retcode := 2;
3375 errbuf := 'Please check request log file for further information. ';
3376
3377 IF l_chk_start_date_csr%ISOPEN THEN
3378 CLOSE l_chk_start_date_csr;
3379 END IF;
3380
3381 IF l_chk_end_date_csr%ISOPEN THEN
3382 CLOSE l_chk_end_date_csr;
3383 END IF;
3384 WHEN OTHERS THEN
3385 retcode := 2;
3386 errbuf := SQLERRM;
3387 END calc;
3388
3389 PROCEDURE calc_curr(
3390 errbuf OUT NOCOPY VARCHAR2
3391 , retcode OUT NOCOPY NUMBER
3392 , p_batch_name VARCHAR2
3393 , p_start_date VARCHAR2
3394 , p_end_date VARCHAR2
3395 , p_calc_type VARCHAR2
3396 , p_salesrep_option VARCHAR2
3397 , p_hierarchy_flag VARCHAR2
3398 , p_intelligent_flag VARCHAR2
3399 , p_salesrep_id NUMBER
3400 ) IS
3401 BEGIN
3402 -- this is a wrapper around the calc procedure to be called from
3403 -- a concurrent program. it eliminates the obsolete variables
3404 -- p_interval_type_id and p_quota_id. it also converts the dates
3405 -- from FND_STANDARD_DATE
3406 calc(
3407 errbuf => errbuf
3408 , retcode => retcode
3409 , p_batch_name => p_batch_name
3410 , p_start_date => fnd_date.canonical_to_date(p_start_date)
3411 , p_end_date => fnd_date.canonical_to_date(p_end_date)
3412 , p_calc_type => p_calc_type
3413 , p_salesrep_option => p_salesrep_option
3414 , p_hierarchy_flag => p_hierarchy_flag
3415 , p_intelligent_flag => p_intelligent_flag
3416 , p_salesrep_id => p_salesrep_id
3417 , p_interval_type_id => -1000
3418 , -- means 'Period'
3419 p_quota_id => NULL
3420 );
3421 END calc_curr;
3422
3423 PROCEDURE collection(
3424 errbuf OUT NOCOPY VARCHAR2
3425 , retcode OUT NOCOPY NUMBER
3426 , p_start_date DATE
3427 , p_end_date DATE
3428 , p_salesrep_id NUMBER
3429 , p_comp_plan_id NUMBER
3430 ) IS
3431 dummy NUMBER;
3432 dummy_char VARCHAR2(30);
3433 BEGIN
3434 cn_proc_batches_pkg.main(
3435 p_concurrent_flag => 'Y'
3436 , p_process_name => 'COLLECTION'
3437 , p_logical_batch_id => NULL
3438 , p_start_date => p_start_date
3439 , p_end_date => p_end_date
3440 , p_salesrep_id => p_salesrep_id
3441 , p_comp_plan_id => p_comp_plan_id
3442 , x_process_audit_id => dummy
3443 , x_process_status_code => dummy_char
3444 );
3445 END collection;
3446
3447 -- Procedure Name
3448 -- Runner (PUBLIC Concurrent Program)
3449 -- Purpose
3450 -- For each distinct physical batch lock the impacted srp_periods and
3451 -- execute collection, classification, roll, populate or calculation
3452 -- on the cn_trx records identified by the physical batch.
3453 -- If the srp_periods cannot be locked the produre executes successfully
3454 -- but doesn't update the srp_period status to the new one.
3455 -- Commits after each batch to help control rollback segment problems
3456 PROCEDURE runner(
3457 errbuf OUT NOCOPY VARCHAR2
3458 , retcode OUT NOCOPY NUMBER
3459 , p_parent_proc_audit_id NUMBER
3460 , p_logical_process VARCHAR2
3461 , p_physical_process VARCHAR2
3462 , p_physical_batch_id NUMBER
3463 , p_salesrep_id NUMBER := NULL
3464 , p_start_date DATE := NULL
3465 , p_end_date DATE := NULL
3466 , p_cls_rol_flag VARCHAR2 := NULL
3467 ) IS
3468 l_curr_status VARCHAR2(30);
3469 l_new_status VARCHAR2(30);
3470 l_tries NUMBER(1);
3471 l_request_id NUMBER(15) := NULL;
3472 l_cls_total NUMBER;
3473 l_xcls_total NUMBER;
3474 l_process_audit_id NUMBER(15);
3475 l_org_id NUMBER;
3476 dummy NUMBER(1);
3477 l_counter NUMBER;
3478 l_msg_count NUMBER;
3479 l_msg_data VARCHAR2(2000);
3480 l_return_status VARCHAR2(30);
3481 l_period_set_id NUMBER;
3482 l_period_type_id NUMBER;
3483 l_calc_from_period_id NUMBER;
3484 l_calc_to_period_id NUMBER;
3485 BEGIN
3486 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3487 fnd_log.STRING(
3488 fnd_log.level_procedure
3489 , 'cn.plsql.cn_proc_batches_pkg.runner.begin'
3490 , 'Beginning of batch runner '
3491 || p_physical_batch_id
3492 || ' in the phase of '
3493 || p_physical_process
3494 || ' at '
3495 || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')
3496 );
3497 END IF;
3498
3499 fnd_file.put_line(fnd_file.LOG
3500 , 'Inside batch runner: ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3501
3502 SELECT org_id
3503 INTO l_org_id
3504 FROM cn_process_batches_all
3505 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1;
3506
3507 l_request_id := fnd_global.conc_request_id;
3508
3509 IF l_request_id <> -1 THEN
3510 cn_message_pkg.begin_batch(
3511 x_process_type => 'CALCULATION'
3512 , x_parent_proc_audit_id => p_parent_proc_audit_id
3513 , x_process_audit_id => l_process_audit_id
3514 , x_request_id => fnd_global.conc_request_id
3515 , p_org_id => l_org_id
3516 );
3517 END IF;
3518
3519 cn_message_pkg.DEBUG(
3520 'Start batch runner (phase=' || p_physical_process || ', batch ID=' || p_physical_batch_id
3521 || ')'
3522 );
3523 cn_proc_batches_pkg.process_status(
3524 x_physical_process => p_physical_process
3525 , x_curr_status => l_curr_status
3526 , x_new_status => l_new_status
3527 );
3528
3529 -- This is the hook if we need to add the collection process
3530 IF p_physical_process = g_collection THEN
3531 NULL;
3532 --cn_collection_pkg.collect(p_physical_batch_id);
3533 ELSIF p_physical_process = g_load THEN
3534 cn_transaction_load_pkg.load_worker(
3535 p_physical_batch_id => p_physical_batch_id
3536 , p_salesrep_id => p_salesrep_id
3537 , p_start_date => p_start_date
3538 , p_end_date => p_end_date
3539 , p_cls_rol_flag => p_cls_rol_flag
3540 );
3541 ELSIF(p_physical_process = g_post) THEN
3542 cn_posting_pvt.post_worker
3543 (
3544 p_parent_proc_audit_id => p_parent_proc_audit_id
3545 , p_posting_batch_id => p_salesrep_id
3546 , -- use p_salesrep_id to pass in posting_batch_id
3547 p_physical_batch_id => p_physical_batch_id
3548 , p_start_date => p_start_date
3549 , p_end_date => p_end_date
3550 );
3551 ELSIF p_physical_process = g_revert THEN
3552 cn_formula_common_pkg.revert_batch(p_physical_batch_id);
3553 ELSIF p_physical_process = g_classification THEN
3554 cn_calc_classify_pvt.classify_batch(
3555 p_api_version => 1.0
3556 , p_init_msg_list => fnd_api.g_true
3557 , p_commit => fnd_api.g_true
3558 , x_return_status => l_return_status
3559 , x_msg_count => l_msg_count
3560 , x_msg_data => l_msg_data
3561 , p_physical_batch_id => p_physical_batch_id
3562 );
3563
3564 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3565 cn_message_pkg.DEBUG('Exception occurs in classification process:');
3566
3567 FOR l_counter IN 1 .. l_msg_count LOOP
3568 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3569 , p_encoded => fnd_api.g_false));
3570 END LOOP;
3571
3572 RAISE api_call_failed;
3573 END IF;
3574 ELSIF p_physical_process = g_rollup THEN
3575 cn_calc_rollup_pvt.rollup_batch(
3576 p_api_version => 1.0
3577 , p_init_msg_list => fnd_api.g_true
3578 , p_commit => fnd_api.g_true
3579 , x_return_status => l_return_status
3580 , x_msg_count => l_msg_count
3581 , x_msg_data => l_msg_data
3582 , p_physical_batch_id => p_physical_batch_id
3583 );
3584
3585 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3586 cn_message_pkg.DEBUG('Exception occurs in rollup phase:');
3587
3588 FOR l_counter IN 1 .. l_msg_count LOOP
3589 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3590 , p_encoded => fnd_api.g_false));
3591 END LOOP;
3592
3593 RAISE api_call_failed;
3594 END IF;
3595 ELSIF p_physical_process = g_population THEN
3596 cn_calc_populate_pvt.populate_batch(
3597 p_api_version => 1.0
3598 , p_init_msg_list => fnd_api.g_true
3599 , p_commit => fnd_api.g_true
3600 , x_return_status => l_return_status
3601 , x_msg_count => l_msg_count
3602 , x_msg_data => l_msg_data
3603 , p_physical_batch_id => p_physical_batch_id
3604 );
3605
3606 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3607 cn_message_pkg.DEBUG('Exception occurs in population phase:');
3608
3609 FOR l_counter IN 1 .. l_msg_count LOOP
3610 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3611 , p_encoded => fnd_api.g_false));
3612 END LOOP;
3613
3614 RAISE api_call_failed;
3615 END IF;
3616 ELSIF p_physical_process = g_calculation THEN
3617 cn_global_var.initialize_instance_info(l_org_id);
3618 cn_formula_common_pkg.calculate_batch(p_physical_batch_id);
3619 END IF;
3620
3621 cn_message_pkg.DEBUG(
3622 'Complete batch runner (phase='
3623 || p_physical_process
3624 || ', batch ID='
3625 || p_physical_batch_id
3626 || ')'
3627 );
3628
3629 IF ((p_physical_process = g_load) AND(p_cls_rol_flag = 'N' OR p_cls_rol_flag IS NULL)) THEN
3630 NULL; -- do not update processing_status_code
3631 ELSIF(p_physical_process IN(g_revert, g_classification, g_rollup, g_population, g_calculation)) THEN
3632 -- raise cn_srp_intel_periods.processing_status_code from 'CLEAN' to 'NOT CLEAN'
3633 SELECT period_set_id
3634 , period_type_id
3635 INTO l_period_set_id
3636 , l_period_type_id
3637 FROM cn_repositories_all
3638 WHERE org_id = l_org_id;
3639
3640 SELECT MAX(period_id)
3641 , MAX(end_period_id)
3642 INTO l_calc_from_period_id
3643 , l_calc_to_period_id
3644 FROM cn_process_batches_all
3645 WHERE physical_batch_id = p_physical_batch_id;
3646
3647 UPDATE cn_srp_intel_periods_all
3648 SET processing_status_code =
3649 DECODE(
3650 p_physical_process
3651 , g_revert, g_reverted
3652 , g_classification, g_classified
3653 , g_rollup, g_rolled_up
3654 , g_population, g_populated
3655 , g_calculation, g_calculated
3656 , g_unclassified
3657 )
3658 WHERE (salesrep_id, period_id) IN(
3659 SELECT batch.salesrep_id
3660 , per.period_id
3661 FROM cn_process_batches_all batch, cn_period_statuses_all per
3662 WHERE batch.physical_batch_id = p_physical_batch_id
3663 AND per.period_id BETWEEN batch.period_id AND batch.end_period_id
3664 AND per.org_id = batch.org_id
3665 AND per.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
3666 AND per.period_set_id = l_period_set_id
3667 AND per.period_type_id = l_period_type_id);
3668 END IF;
3669
3670 cn_message_pkg.FLUSH;
3671 COMMIT;
3672
3673 -- If run as a conc program it will have its own process audit id
3674 -- and request id therefore we need to give info on the process
3675 IF l_request_id <> -1 THEN
3676 cn_message_pkg.end_batch(l_process_audit_id);
3677 END IF;
3678
3679 retcode := 0;
3680 errbuf := 'Batch runner completes successfully.';
3681
3682 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3683 fnd_log.STRING(
3684 fnd_log.level_procedure
3685 , 'cn.plsql.cn_proc_batches_pkg.runner.end'
3686 , 'End of batch runner '
3687 || p_physical_batch_id
3688 || 'at '
3689 || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')
3690 );
3691 END IF;
3692
3693 cn_message_pkg.DEBUG('Time is ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3694 fnd_file.put_line(fnd_file.LOG, 'Time is ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3695 EXCEPTION
3696 WHEN api_call_failed THEN
3697 retcode := 2;
3698 errbuf := SUBSTR(fnd_message.get, 1, 254);
3699
3700 IF (l_msg_count > 0) THEN
3701 FOR l_counter IN 1 .. l_msg_count LOOP
3702 fnd_file.put_line(fnd_file.LOG
3703 , fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false));
3704 END LOOP;
3705 END IF;
3706
3707 cn_message_pkg.rollback_errormsg_commit('Exception occurs in batch runner (ID='
3708 || p_physical_batch_id || ')');
3709 update_error(p_physical_batch_id);
3710
3711 -- if concurrent program, commit and close log file
3712 -- if sequential calcualtion, commit and leave log file open
3713 IF l_request_id <> -1 THEN
3714 cn_message_pkg.end_batch(l_process_audit_id);
3715 ELSE
3716 COMMIT;
3717 END IF;
3718 WHEN OTHERS THEN
3719 -- Return to concurrent manager with error. Xinyang Fan 4/13/98
3720 retcode := 2;
3721 errbuf := SQLERRM;
3722 -- to make sure we record the updates made by update_error
3723 -- we roll back first
3724 cn_message_pkg.DEBUG('Exception occurs in batch runner (ID=' || p_physical_batch_id || ')');
3725 cn_message_pkg.rollback_errormsg_commit(errbuf);
3726 update_error(p_physical_batch_id);
3727
3728 -- if concurrent program, commit and close log file
3729 -- if sequential calcualtion, commit and leave log file open
3730 IF l_request_id <> -1 THEN
3731 cn_message_pkg.end_batch(l_process_audit_id);
3732 ELSE
3733 COMMIT;
3734 END IF;
3735
3736 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3737 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_proc_batches_pkg.runner.exception'
3738 , SQLERRM);
3739 END IF;
3740 END runner;
3741
3742 -- Check if the period submitted for calc is covered by one or more rulesets.
3743 -- NOTE: it does not check for the validity of the ruleset. That is done by validate_ruleset_status
3744 PROCEDURE validate_ruleset_coverage(
3745 p_start_date DATE
3746 , p_end_date DATE
3747 , x_covered OUT NOCOPY BOOLEAN
3748 , p_org_id NUMBER
3749 ) IS
3750 CURSOR ruleset_cur IS
3751 SELECT ruleset_id
3752 , start_date
3753 , NVL(end_date, p_end_date) end_date
3754 FROM cn_rulesets_all_b
3755 WHERE org_id = p_org_id
3756 AND (
3757 (start_date <= p_start_date AND NVL(end_date, p_start_date) >= p_start_date)
3758 OR (start_date BETWEEN p_start_date AND p_end_date)
3759 )
3760 ORDER BY start_date;
3761
3762 l_cur_end_date DATE := p_start_date;
3763 BEGIN
3764 x_covered := FALSE;
3765
3766 FOR ruleset IN ruleset_cur LOOP
3767 IF (ruleset.start_date <= l_cur_end_date) OR(ruleset.start_date =(l_cur_end_date + 1)) THEN
3768 l_cur_end_date := ruleset.end_date;
3769 ELSE
3770 x_covered := FALSE;
3771 EXIT;
3772 END IF;
3773
3774 IF ruleset.end_date >= p_end_date THEN
3775 x_covered := TRUE;
3776 EXIT;
3777 END IF;
3778 END LOOP;
3779
3780 IF x_covered = FALSE THEN
3781 cn_message_pkg.DEBUG(
3782 'No classification ruleset is defined for the period from '
3783 || p_start_date
3784 || ' to '
3785 || p_end_date
3786 );
3787 END IF;
3788 END validate_ruleset_coverage;
3789
3790 FUNCTION validate_ruleset_status(p_start_date DATE, p_end_date DATE, p_org_id NUMBER)
3791 RETURN BOOLEAN IS
3792 CURSOR l_rulesets_csr IS
3793 SELECT ruleset_id
3794 , NAME
3795 , ruleset_status
3796 , start_date
3797 , end_date
3798 FROM cn_rulesets_all_vl
3799 WHERE org_id = p_org_id
3800 AND start_date <= p_end_date
3801 AND p_start_date <= NVL(end_date, p_end_date)
3802 AND module_type = 'REVCLS';
3803
3804 CURSOR l_chk_rule_package_csr(l_pkg_name user_objects.object_name%TYPE) IS
3805 SELECT COUNT(*)
3806 FROM user_objects ob
3807 WHERE ob.object_name = l_pkg_name AND ob.object_type = 'PACKAGE BODY';
3808
3809 cached_org_id INTEGER;
3810 cached_org_append VARCHAR2(100);
3811 l_cls_pkg_name user_objects.object_name%TYPE;
3812 l_error_ctr NUMBER := 0;
3813 l_counter NUMBER := 0;
3814 x_covered BOOLEAN := FALSE;
3815 BEGIN
3816 -- check to make sure the specified dates are covered by one or more rulesets
3817 validate_ruleset_coverage(p_start_date, p_end_date, x_covered, p_org_id);
3818
3819 IF x_covered = FALSE THEN
3820 RETURN FALSE;
3821 END IF;
3822
3823 cached_org_id := p_org_id;
3824
3825 IF cached_org_id = -99 THEN
3826 cached_org_append := '_MINUS99';
3827 ELSE
3828 cached_org_append := '_' || cached_org_id;
3829 END IF;
3830
3831 FOR l_set IN l_rulesets_csr LOOP
3832 IF l_set.ruleset_status IN('UNSYNC', 'INSTINPG', 'INSTFAIL', 'CONCFAIL') THEN
3833 cn_message_pkg.DEBUG('Please synchronize ruleset (Name=' || l_set.NAME || ')');
3834 l_error_ctr := 1;
3835 ELSE
3836 l_cls_pkg_name := 'cn_clsfn_' || TO_CHAR(ABS(l_set.ruleset_id)) || cached_org_append;
3837
3838 OPEN l_chk_rule_package_csr(UPPER(l_cls_pkg_name));
3839 FETCH l_chk_rule_package_csr INTO l_counter;
3840 CLOSE l_chk_rule_package_csr;
3841
3842 IF l_counter = 0 THEN
3843 cn_message_pkg.DEBUG('Please synchronize ruleset (name=' || l_set.NAME || ')');
3844 cn_message_pkg.DEBUG('Classification package is missing (name=' || l_cls_pkg_name || ')');
3845 l_error_ctr := 1;
3846 END IF;
3847 END IF;
3848 END LOOP;
3849
3850 IF l_error_ctr = 1 THEN
3851 fnd_message.set_name('CN', 'PROC_CLS_PKG_MISSING');
3852
3853 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3854 fnd_log.MESSAGE(
3855 fnd_log.level_error
3856 , 'cn.plsql.cn_proc_batches_pkg.validate_ruleset_status.validation'
3857 , TRUE
3858 );
3859 END IF;
3860
3861 RETURN FALSE;
3862 END IF;
3863
3864 RETURN TRUE;
3865 END validate_ruleset_status;
3866
3867 -- Procedure Name
3868 -- Processor
3869 -- Purpose
3870 -- Called from concurrent manager or from main
3871 -- If called from SRS the procedure must create the logical batch from
3872 -- the passed parameters.
3873 -- If called from the commissions batch processing UI's the logical id
3874 -- will be present and the logical batch already exists.
3875
3876 -- Notes
3877 -- Logical flag is null when called as a conc program
3878 -- Online flag allows the trx_processor to determine whether it can submit
3879 -- its own concurrent programs
3880 --
3881 PROCEDURE processor(
3882 errbuf OUT NOCOPY VARCHAR2
3883 , retcode OUT NOCOPY NUMBER
3884 , p_parent_proc_audit_id NUMBER
3885 , p_concurrent_flag VARCHAR2
3886 , p_process_name VARCHAR2
3887 , p_logical_batch_id NUMBER
3888 , p_start_date DATE
3889 , p_end_date DATE
3890 , p_salesrep_id NUMBER
3891 , p_comp_plan_id NUMBER
3892 ) IS
3893 l_process_audit_id NUMBER(15);
3894 l_request_id NUMBER(15);
3895 l_paid NUMBER(15);
3896 l_temp NUMBER;
3897 l_logical_batch_id NUMBER(15);
3898 l_calc_sub_batch_id NUMBER(15);
3899 l_salesrep_option VARCHAR2(30);
3900 -- for update payee subledger
3901 l_loading_status VARCHAR2(50);
3902 l_return_status VARCHAR2(50);
3903 l_msg_count NUMBER;
3904 l_msg_data VARCHAR2(2000);
3905 l_ledger_je_batch cn_calc_subledger_pvt.je_batch_rec_type;
3906 l_ledger_je_batch_id NUMBER(15);
3907 BEGIN
3908 l_request_id := fnd_global.conc_request_id;
3909 g_logical_process := p_process_name;
3910 g_logical_batch_id := p_logical_batch_id;
3911
3912 SELECT calc_type
3913 , org_id
3914 INTO g_calc_type
3915 , g_org_id
3916 FROM cn_calc_submission_batches_all
3917 WHERE logical_batch_id = g_logical_batch_id;
3918
3919 -- Accept the current parent id and get the id for this batch if
3920 -- this is a concurrent request and it wasn't submitted from the
3921 -- calc submission form.
3922 -- If it is a conc request and was submitted from the form then
3923 -- we will already have the process audit id
3924 IF l_request_id <> -1 THEN
3925 cn_message_pkg.begin_batch(
3926 x_process_type => 'CALCULATION'
3927 , x_parent_proc_audit_id => p_parent_proc_audit_id
3928 , x_process_audit_id => l_process_audit_id
3929 , x_request_id => fnd_global.conc_request_id
3930 , p_org_id => g_org_id
3931 );
3932 END IF;
3933
3934 -- Group the srp periods into physical batches
3935 BEGIN
3936 SELECT physical_batch_id
3937 INTO l_temp
3938 FROM cn_process_batches_all
3939 WHERE logical_batch_id = g_logical_batch_id AND ROWNUM = 1;
3940 EXCEPTION
3941 WHEN OTHERS THEN
3942 l_temp := -1;
3943 END;
3944
3945 IF (l_temp IS NULL) THEN
3946 assign;
3947 ELSIF(l_temp = -1) THEN
3948 GOTO end_no_trx;
3949 END IF;
3950
3951 -- Only pass the new one if running concurrently
3952 l_paid := NVL(l_process_audit_id, p_parent_proc_audit_id);
3953
3954 UPDATE cn_calc_submission_batches_all
3955 SET --ledger_je_batch_id = l_ledger_je_batch_id,
3956 process_audit_id = l_paid
3957 WHERE logical_batch_id = g_logical_batch_id;
3958
3959 IF p_concurrent_flag = 'N' THEN
3960 seq_dispatch(l_paid);
3961 ELSE
3962 IF (l_temp IS NULL) THEN
3963 conc_dispatch(l_paid);
3964 ELSE
3965 conc_dispatch2(l_paid);
3966 END IF;
3967
3968 BEGIN
3969 SELECT 1
3970 INTO l_temp
3971 FROM SYS.DUAL
3972 WHERE NOT EXISTS(SELECT 1
3973 FROM cn_process_batches_all
3974 WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
3975
3976 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
3977 fnd_message.set_name('CN', 'ALL_PROCESS_DONE_OK');
3978
3979 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level) THEN
3980 fnd_log.MESSAGE(fnd_log.level_event, 'cn.plsql.cn_proc_batches_pkg.processor.event'
3981 , TRUE);
3982 END IF;
3983 EXCEPTION
3984 WHEN NO_DATA_FOUND THEN
3985 retcode := 2;
3986 errbuf := 'Completed with error. Please see the log file for details.';
3987 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
3988 fnd_message.set_name('CN', 'ALL_PROCESS_DONE_FAIL');
3989
3990 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3991 fnd_log.MESSAGE(fnd_log.level_error
3992 , 'cn.plsql.cn_proc_batches_pkg.processor.exception', TRUE);
3993 END IF;
3994 END;
3995
3996 -- Mark the processed batches for deletion
3997 void_batches(NULL);
3998 END IF;
3999
4000 <<end_no_trx>>
4001 cn_message_pkg.end_batch(l_paid);
4002 EXCEPTION
4003 WHEN OTHERS THEN
4004 retcode := 2;
4005 errbuf := SQLERRM;
4006 fnd_file.put_line(fnd_file.LOG, 'Unexpected exception in cn_proc_batches_pkg.processor');
4007 fnd_file.put_line(fnd_file.LOG, SQLERRM);
4008 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.processor:');
4009 cn_message_pkg.rollback_errormsg_commit(errbuf);
4010 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4011 cn_message_pkg.end_batch(l_paid);
4012
4013 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4014 fnd_log.STRING(fnd_log.level_unexpected
4015 , 'cn.plsql.cn_proc_batches_pkg.processor.exception', SQLERRM);
4016 END IF;
4017
4018 RAISE;
4019 END processor;
4020
4021 -- processor concurrent wrapper on top of processor, called by the concurrent program CN_BATPROC.
4022 -- Do the Canonical-to-Date conversion on the date prarmeters, bug 2610735
4023 PROCEDURE processor_curr(
4024 errbuf OUT NOCOPY VARCHAR2
4025 , retcode OUT NOCOPY NUMBER
4026 , p_parent_proc_audit_id NUMBER
4027 , p_concurrent_flag VARCHAR2
4028 , p_process_name VARCHAR2
4029 , p_logical_batch_id NUMBER
4030 , p_start_date VARCHAR2
4031 , p_end_date VARCHAR2
4032 , p_salesrep_id NUMBER
4033 , p_comp_plan_id NUMBER
4034 ) IS
4035 BEGIN
4036 processor(
4037 errbuf => errbuf
4038 , retcode => retcode
4039 , p_parent_proc_audit_id => p_parent_proc_audit_id
4040 , p_concurrent_flag => p_concurrent_flag
4041 , p_process_name => p_process_name
4042 , p_logical_batch_id => p_logical_batch_id
4043 , p_start_date => fnd_date.canonical_to_date(p_start_date)
4044 , p_end_date => fnd_date.canonical_to_date(p_end_date)
4045 , p_salesrep_id => p_salesrep_id
4046 , p_comp_plan_id => p_comp_plan_id
4047 );
4048 END processor_curr;
4049
4050 PROCEDURE main(
4051 p_concurrent_flag VARCHAR2 DEFAULT 'N'
4052 , p_process_name VARCHAR2 DEFAULT 'CALCULATION'
4053 , p_logical_batch_id NUMBER
4054 , p_start_date DATE
4055 , p_end_date DATE
4056 , p_salesrep_id NUMBER
4057 , p_comp_plan_id NUMBER
4058 , x_process_audit_id IN OUT NOCOPY NUMBER
4059 , x_process_status_code OUT NOCOPY VARCHAR2
4060 ) IS
4061 l_temp NUMBER(1);
4062 l_dummy VARCHAR2(80);
4063 l_request_id NUMBER;
4064 l_process_audit_id NUMBER(15);
4065 BEGIN
4066 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4067 fnd_log.STRING(
4068 fnd_log.level_procedure
4069 , 'cn.plsql.cn_proc_batches_pkg.main.begin'
4070 , 'Beginning of cn_proc_batches_pkg.main...'
4071 );
4072 END IF;
4073
4074 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'PROCESSING');
4075 COMMIT;
4076
4077 -- The process audit_id is passed in when called from the trx form.
4078 IF x_process_audit_id IS NULL THEN
4079 cn_message_pkg.begin_batch(
4080 x_process_type => 'CALCULATION'
4081 , x_parent_proc_audit_id => NULL
4082 , x_process_audit_id => x_process_audit_id
4083 , x_request_id => fnd_global.conc_request_id
4084 , p_org_id => g_org_id
4085 );
4086
4087 UPDATE cn_calc_submission_batches_all
4088 SET process_audit_id = x_process_audit_id
4089 WHERE logical_batch_id = p_logical_batch_id;
4090 END IF;
4091
4092 -- Validate the process name parameter. Currently we only pass 'calculation'
4093 -- but in future we may call collection and other processes.
4094 -- And validate ruleset's status
4095 IF p_process_name = g_collection THEN
4096 NULL;
4097 ELSIF p_process_name IN(g_classification, g_rollup, g_population, g_calculation) THEN
4098 IF NOT validate_ruleset_status(p_start_date, p_end_date, g_org_id) THEN
4099 fnd_file.put_line(fnd_file.LOG, 'classification ruleset is not valid');
4100 RAISE ABORT;
4101 END IF;
4102 ELSE
4103 fnd_file.put_line(fnd_file.LOG
4104 , 'cn_proc_batches_pkg.main: bad process name: ' || p_process_name);
4105 cn_message_pkg.DEBUG('Invalid process code (' || p_process_name || ')');
4106 RAISE ABORT;
4107 END IF;
4108
4109 IF NVL(cn_global_var.g_system_batch_size, 0) = 0 THEN
4110 cn_message_pkg.set_name('CN', 'PROC_BAD_BATCH_SIZE');
4111 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4112 RAISE ABORT;
4113 END IF;
4114
4115 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4116 fnd_log.STRING(
4117 fnd_log.level_procedure
4118 , 'cn.plsql.cn_proc_batches_pkg.main.run_processor'
4119 , 'Before calling procedure processor.'
4120 );
4121 END IF;
4122
4123 IF (p_concurrent_flag = 'N' OR(p_concurrent_flag = 'Y' AND fnd_global.conc_program_id <> -1)) THEN
4124 cn_message_pkg.DEBUG('Start processing transactions (non concurrent calculation)');
4125 cn_proc_batches_pkg.processor(
4126 l_dummy
4127 , l_temp
4128 , x_process_audit_id
4129 , p_concurrent_flag
4130 , p_process_name
4131 , p_logical_batch_id
4132 , p_start_date
4133 , p_end_date
4134 , p_salesrep_id
4135 , p_comp_plan_id
4136 );
4137 cn_message_pkg.DEBUG('End processing transactions (non concurrent calculation)');
4138
4139 IF (l_temp = 2) THEN
4140 x_process_status_code := 'FAIL';
4141 ELSE
4142 BEGIN
4143 SELECT 1
4144 INTO l_temp
4145 FROM SYS.DUAL
4146 WHERE NOT EXISTS(SELECT 1
4147 FROM cn_process_batches_all
4148 WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
4149
4150 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
4151 x_process_status_code := 'SUCCESS';
4152 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_OK');
4153 cn_message_pkg.end_batch(x_process_audit_id);
4154 EXCEPTION
4155 WHEN NO_DATA_FOUND THEN
4156 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4157 x_process_status_code := 'FAIL';
4158 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_FAIL');
4159 cn_message_pkg.end_batch(x_process_audit_id);
4160 END;
4161
4162 COMMIT;
4163 END IF;
4164
4165 -- Mark the processed batches for deletion
4166 void_batches(NULL);
4167 ELSE
4168 fnd_request.set_org_id(g_org_id);
4169 l_request_id :=
4170 fnd_request.submit_request(
4171 application => 'CN'
4172 , program => 'BATCH_PROCESSOR'
4173 , description => NULL
4174 , start_time => NULL
4175 , sub_request => NULL
4176 , argument1 => x_process_audit_id
4177 , argument2 => p_concurrent_flag
4178 , argument3 => p_process_name
4179 , argument4 => p_logical_batch_id
4180 , argument5 => fnd_date.date_to_canonical(p_start_date)
4181 , argument6 => fnd_date.date_to_canonical(p_end_date)
4182 , argument7 => p_salesrep_id
4183 , argument8 => p_comp_plan_id
4184 );
4185
4186 IF l_request_id = 0 THEN
4187 fnd_file.put_line(fnd_file.LOG
4188 , 'cn_proc_batches_pkg.main: unable to submit batch_processor');
4189 cn_message_pkg.DEBUG('Failed to submit concurrent request (Batch Processor)');
4190 cn_message_pkg.DEBUG(fnd_message.get);
4191 x_process_status_code := 'FAIL';
4192
4193 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4194 fnd_log.STRING(
4195 fnd_log.level_unexpected
4196 , 'cn.plsql.cn_proc_batches_pkg.main.exception'
4197 , 'Failed to submit request for BATCH_PROCESSOR.'
4198 );
4199 END IF;
4200
4201 RAISE ABORT;
4202 ELSE
4203 x_process_status_code := 'SUCCESS';
4204 -- a separate process is being called so we need to wrap up this
4205 -- batch of messages
4206 cn_message_pkg.end_batch(x_process_audit_id);
4207 -- for concurrent request, it makes more sense to return request_id.
4208 -- It is better to use another parameter to return request_id.
4209 x_process_audit_id := l_request_id;
4210 cn_message_pkg.FLUSH;
4211 COMMIT;
4212 END IF;
4213 END IF;
4214
4215 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4216 fnd_log.STRING(
4217 fnd_log.level_procedure
4218 , 'cn.plsql.cn_proc_batches_pkg.main.run_processor'
4219 , 'After calling procedure processor.'
4220 );
4221 END IF;
4222
4223 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4224 fnd_log.STRING(
4225 fnd_log.level_procedure
4226 , 'cn.plsql.cn_proc_batches_pkg.main.end'
4227 , 'End of cn_proc_batches_pkg.main...'
4228 );
4229 END IF;
4230 EXCEPTION
4231 WHEN ABORT THEN
4232 cn_message_pkg.rollback_errormsg_commit('Exception occurs in cn_proc_batches_pkg.main.');
4233 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4234 COMMIT;
4235 x_process_status_code := 'FAIL';
4236 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4237 cn_message_pkg.end_batch(x_process_audit_id);
4238 WHEN OTHERS THEN
4239 fnd_file.put_line(fnd_file.LOG, 'unexpected exception in cn_proc_batches_pkg.main');
4240 fnd_file.put_line(fnd_file.LOG, SQLERRM);
4241 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.main: ');
4242 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
4243 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4244 COMMIT;
4245 x_process_status_code := 'FAIL';
4246 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_FAIL');
4247 cn_message_pkg.end_batch(x_process_audit_id);
4248
4249 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4250 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_proc_batches_pkg.main.exception'
4251 , SQLERRM);
4252 END IF;
4253 END main;
4254
4255 FUNCTION get_period_name(x_period_id IN NUMBER, p_org_id IN NUMBER)
4256 RETURN VARCHAR2 IS
4257 x_period_name VARCHAR2(30);
4258 BEGIN
4259 IF x_period_id IS NOT NULL THEN
4260 SELECT period_name
4261 INTO x_period_name
4262 FROM cn_period_statuses_all
4263 WHERE period_id = x_period_id AND org_id = p_org_id;
4264
4265 RETURN x_period_name;
4266 END IF;
4267
4268 RETURN NULL;
4269 END;
4270
4271 PROCEDURE get_person_name_num(
4272 x_salesrep_id NUMBER
4273 , p_org_id NUMBER
4274 , x_name IN OUT NOCOPY VARCHAR2
4275 , x_num IN OUT NOCOPY VARCHAR2
4276 ) IS
4277 BEGIN
4278 SELECT NAME
4279 , employee_number
4280 INTO x_name
4281 , x_num
4282 FROM cn_salesreps
4283 WHERE salesrep_id = x_salesrep_id AND org_id = p_org_id;
4284 END get_person_name_num;
4285
4286 --
4287 -- Name
4288 -- check_end_of_interval
4289 -- Purpose
4290 -- Returns 1 if the specified period is the end of an interval of the
4291 -- type listed int he X_Interval string.
4292 -- History
4293 -- 06/13/95 Created Rjin
4294 --
4295 FUNCTION check_end_of_interval(p_period_id NUMBER, p_interval_type_id NUMBER, p_org_id NUMBER)
4296 RETURN BOOLEAN IS
4297 l_end_period_id NUMBER(15);
4298 BEGIN
4299 SELECT MAX(ps2.period_id)
4300 INTO l_end_period_id
4301 FROM cn_period_statuses_all ps1, cn_period_statuses_all ps2
4302 WHERE ps1.org_id = p_org_id
4303 AND ps1.period_id = p_period_id
4304 AND ps2.period_set_id = ps1.period_set_id
4305 AND ps2.period_type_id = ps1.period_type_id
4306 AND ps2.period_year = ps1.period_year
4307 AND ps2.org_id = ps1.org_id
4308 AND (
4309 (p_interval_type_id = -1001 AND ps2.quarter_num = ps1.quarter_num) -- quarter interval
4310 OR p_interval_type_id = -1002
4311 ); -- year interval
4312
4313 IF p_period_id = l_end_period_id THEN
4314 RETURN TRUE;
4315 ELSE
4316 RETURN FALSE;
4317 END IF;
4318 EXCEPTION
4319 WHEN OTHERS THEN
4320 RETURN FALSE;
4321 END check_end_of_interval;
4322
4323 --
4324 -- Name
4325 -- check_active_plan_assign
4326 -- Purpose
4327 -- Returns 1 if the specified period is the end of an interval of the
4328 -- type listed int he X_Interval string.
4329 -- History
4330 -- 06/13/95 Created Tony Lower
4331 --
4332 FUNCTION check_active_plan_assign(
4333 p_salesrep_id NUMBER
4334 , p_start_date DATE
4335 , p_end_date DATE
4336 , p_interval_type_id NUMBER
4337 , p_calc_sub_batch_id NUMBER
4338 , p_org_id NUMBER
4339 )
4340 RETURN BOOLEAN IS
4341 CURSOR l_active_plan_csr IS
4342 SELECT 1
4343 FROM DUAL
4344 WHERE EXISTS(
4345 SELECT 1
4346 FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
4347 WHERE spa.salesrep_id = p_salesrep_id
4348 AND spa.org_id = p_org_id
4349 AND (
4350 (
4351 spa.end_date IS NOT NULL
4352 AND p_end_date BETWEEN spa.start_date AND spa.end_date
4353 )
4354 OR (p_end_date >= spa.start_date AND spa.end_date IS NULL)
4355 )
4356 AND spa.comp_plan_id = PLAN.comp_plan_id
4357 AND PLAN.status_code = 'COMPLETE')
4358 OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
4359 (
4360 SELECT 1
4361 FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
4362 WHERE spa.salesrep_id = p_salesrep_id
4363 AND spa.org_id = p_org_id
4364 AND spa.end_date >= p_start_date
4365 AND spa.end_date < p_end_date
4366 AND qa.comp_plan_id = spa.comp_plan_id
4367 AND qa.quota_id = pe.quota_id
4368 AND pe.incentive_type_code = 'BONUS'
4369 AND pe.salesreps_enddated_flag = 'Y'
4370 AND pe.interval_type_id = p_interval_type_id
4371 -- plan element is effective on spa.end_date
4372 AND (
4373 (
4374 pe.end_date IS NOT NULL
4375 AND spa.end_date BETWEEN pe.start_date AND pe.end_date
4376 )
4377 OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
4378 )
4379 -- check if in cn_calc_sub_quotas if that exists
4380 AND (
4381 (0 = (SELECT COUNT(*)
4382 FROM cn_calc_sub_quotas
4383 WHERE calc_sub_batch_id = p_calc_sub_batch_id))
4384 OR (pe.quota_id IN(SELECT csq.quota_id
4385 FROM cn_calc_sub_quotas csq
4386 WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
4387 ));
4388
4389 dummy NUMBER := 0;
4390 BEGIN
4391 OPEN l_active_plan_csr;
4392 FETCH l_active_plan_csr INTO dummy;
4393 CLOSE l_active_plan_csr;
4394
4395 IF dummy = 1 THEN
4396 RETURN TRUE;
4397 ELSE
4398 RETURN FALSE;
4399 END IF;
4400 END check_active_plan_assign;
4401
4402 -- Procedure Name
4403 -- Populate_bonus_process_batch
4404 -- Purpose
4405 -- populate the cn_process_batch for an entry in cn_calc_submission_batches
4406 -- Notes
4407 -- 12-Jul-1998, Richard Jin Created
4408 PROCEDURE populate_bonus_process_batch(p_calc_sub_batch_id NUMBER) IS
4409 l_intelligent_flag VARCHAR2(1);
4410 l_hierarchy_flag VARCHAR2(1);
4411 l_salesrep_option VARCHAR2(20);
4412 l_counter NUMBER;
4413 l_interval_type_id NUMBER(15);
4414 l_start_date DATE;
4415 l_end_date DATE;
4416 l_start_period_id NUMBER;
4417 l_end_period_id NUMBER;
4418 l_org_id NUMBER;
4419
4420 -- cursors to select salesrep with active comp plan within the calc_submission
4421 -- start_date and end_date
4422 CURSOR l_all_reps_csr IS
4423 SELECT DISTINCT spa.salesrep_id
4424 FROM cn_srp_plan_assigns_all spa, cn_calc_submission_batches_all bat
4425 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4426 AND spa.org_id = bat.org_id
4427 AND spa.start_date <= bat.end_date
4428 AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
4429 --code added for forwardport bug 6600074
4430 AND EXISTS(SELECT 1
4431 FROM cn_comp_plans
4432 WHERE comp_plan_id = spa.comp_plan_id AND status_code = 'COMPLETE')
4433 AND EXISTS(
4434 SELECT 1
4435 FROM cn_quota_assigns a, cn_quotas b
4436 WHERE a.comp_plan_id = spa.comp_plan_id
4437 AND a.quota_id = b.quota_id
4438 AND b.incentive_type_code = 'BONUS'
4439 AND GREATEST(bat.start_date, b.start_date) <=
4440 LEAST(bat.end_date, NVL(b.end_date, bat.end_date)))
4441 --end of code added for forwardport bug 6600074
4442 UNION
4443 SELECT salesrep_id
4444 FROM cn_srp_intel_periods_all sip
4445 WHERE period_id BETWEEN l_start_period_id AND l_end_period_id
4446 AND org_id = l_org_id
4447 AND processing_status_code <> 'CLEAN'
4448 AND NOT EXISTS(
4449 SELECT 1
4450 FROM cn_srp_plan_assigns_all
4451 WHERE salesrep_id = sip.salesrep_id
4452 AND org_id = sip.org_id
4453 AND start_date <= sip.end_date
4454 AND NVL(end_date, sip.start_date) >= sip.start_date)
4455 AND EXISTS(
4456 SELECT 1
4457 FROM cn_commission_headers_all h
4458 WHERE h.direct_salesrep_id = sip.salesrep_id
4459 AND h.org_id = sip.org_id
4460 AND h.processed_date BETWEEN sip.start_date AND sip.end_date
4461 AND h.trx_type = 'BONUS');
4462
4463 CURSOR l_user_reps_csr IS
4464 SELECT cse.salesrep_id
4465 , NVL(cse.hierarchy_flag, 'N') hierarchy_flag
4466 FROM cn_calc_submission_entries_all cse
4467 WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
4468 AND (
4469 EXISTS(
4470 SELECT 1
4471 FROM cn_srp_plan_assigns_all spa
4472 , cn_calc_submission_batches_all bat
4473 ,
4474 --code added for forwardport bug 6600074
4475 cn_comp_plans PLAN
4476 , cn_quota_assigns a
4477 , cn_quotas b
4478 --end of code added for forwardport bug 6600074
4479 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4480 AND spa.salesrep_id = cse.salesrep_id
4481 AND spa.org_id = bat.org_id
4482 AND spa.start_date <= bat.end_date
4483 AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
4484 --code added for forwardport bug 6600074
4485 AND spa.comp_plan_id = PLAN.comp_plan_id
4486 AND PLAN.status_code = 'COMPLETE'
4487 AND a.comp_plan_id = spa.comp_plan_id
4488 AND a.quota_id = b.quota_id
4489 AND b.incentive_type_code = 'BONUS'
4490 AND GREATEST(bat.start_date, b.start_date) <=
4491 LEAST(bat.end_date, NVL(b.end_date, bat.end_date))
4492 --end of code added for forwardport bug 6600074
4493 )
4494 OR EXISTS(
4495 SELECT 1
4496 FROM cn_commission_headers_all h
4497 WHERE h.direct_salesrep_id = cse.salesrep_id
4498 AND h.processed_date BETWEEN l_start_date AND l_end_date
4499 AND h.org_id = cse.org_id
4500 AND h.trx_type = 'BONUS')
4501 );
4502
4503 -- cursors for selecting pay period in which to calculate bonus
4504 CURSOR l_period_int_periods_csr(p_salesrep_id NUMBER, p_interval_type_id NUMBER) IS
4505 SELECT period.period_id
4506 , period.start_date
4507 , period.end_date
4508 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4509 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4510 AND period.org_id = bat.org_id
4511 AND (period.period_set_id, period.period_type_id) = (SELECT period_set_id
4512 , period_type_id
4513 FROM cn_repositories_all
4514 WHERE org_id = bat.org_id)
4515 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4516 AND (
4517 EXISTS -- on period.end_date there is an active comp_plan
4518 (
4519 SELECT 1
4520 FROM cn_srp_plan_assigns_all spa
4521 WHERE spa.salesrep_id = p_salesrep_id
4522 AND spa.org_id = bat.org_id
4523 AND (
4524 (
4525 spa.end_date IS NOT NULL
4526 AND period.end_date BETWEEN spa.start_date AND spa.end_date
4527 )
4528 OR (period.end_date >= spa.start_date AND spa.end_date IS NULL)
4529 ))
4530 OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
4531 (
4532 SELECT 1
4533 FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
4534 WHERE spa.salesrep_id = p_salesrep_id
4535 AND spa.org_id = bat.org_id
4536 AND spa.end_date >= period.start_date
4537 AND spa.end_date < period.end_date
4538 AND qa.comp_plan_id = spa.comp_plan_id
4539 AND qa.quota_id = pe.quota_id
4540 AND pe.incentive_type_code = 'BONUS'
4541 AND pe.salesreps_enddated_flag = 'Y'
4542 AND (
4543 (p_interval_type_id = -1000 AND pe.interval_type_id = -1000)
4544 OR (p_interval_type_id = -1001 AND pe.interval_type_id = -1001)
4545 OR (p_interval_type_id = -1002 AND pe.interval_type_id = -1002)
4546 OR (
4547 p_interval_type_id = -1003
4548 AND pe.interval_type_id IN(-1000, -1001, -1002)
4549 )
4550 )
4551 -- plan element is effective on spa.end_date
4552 AND (
4553 (
4554 pe.end_date IS NOT NULL
4555 AND spa.end_date BETWEEN pe.start_date AND pe.end_date
4556 )
4557 OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
4558 )
4559 -- check if in cn_calc_sub_quotas if that exists
4560 AND (
4561 (0 = (SELECT COUNT(*)
4562 FROM cn_calc_sub_quotas
4563 WHERE calc_sub_batch_id = p_calc_sub_batch_id))
4564 OR (pe.quota_id IN(SELECT csq.quota_id
4565 FROM cn_calc_sub_quotas csq
4566 WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
4567 ))
4568 OR EXISTS(
4569 SELECT 1
4570 FROM cn_commission_headers_all
4571 WHERE direct_salesrep_id = p_salesrep_id
4572 AND org_id = bat.org_id
4573 AND processed_date BETWEEN period.start_date AND period.end_date
4574 AND trx_type = 'BONUS')
4575 );
4576
4577 CURSOR l_quarter_int_periods_csr(l_salesrep_id NUMBER) IS
4578 SELECT MIN(period.period_id) min_period_id
4579 , MAX(period.period_id) max_period_id
4580 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4581 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4582 AND period.org_id = bat.org_id
4583 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4584 AND (period.period_set_id, period.period_type_id) =
4585 (SELECT period_set_id
4586 , period_type_id
4587 FROM cn_repositories_all
4588 WHERE org_id = bat.org_id)
4589 GROUP BY period.quarter_num;
4590
4591 CURSOR l_year_int_periods_csr(l_salesrep_id NUMBER) IS
4592 SELECT MIN(period.period_id) min_period_id
4593 , MAX(period.period_id) max_period_id
4594 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4595 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4596 AND period.org_id = bat.org_id
4597 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4598 AND (period.period_set_id, period.period_type_id) =
4599 (SELECT period_set_id
4600 , period_type_id
4601 FROM cn_repositories_all
4602 WHERE org_id = bat.org_id)
4603 GROUP BY period.period_year;
4604
4605 CURSOR l_period_info_csr(l_period_id NUMBER) IS
4606 SELECT period.period_id
4607 , period.start_date
4608 , period.end_date
4609 , period.period_set_id
4610 , period.period_type_id
4611 , period.period_year
4612 , period.quarter_num
4613 FROM cn_period_statuses_all period
4614 WHERE period.period_id = l_period_id AND org_id = g_org_id;
4615
4616 l_prd l_period_info_csr%ROWTYPE;
4617 l_end_prd l_period_info_csr%ROWTYPE;
4618 l_start_prd l_period_info_csr%ROWTYPE;
4619
4620 CURSOR l_sub_batch_csr IS
4621 SELECT NVL(hierarchy_flag, 'N')
4622 , salesrep_option
4623 , interval_type_id
4624 , start_date
4625 , end_date
4626 , org_id
4627 FROM cn_calc_submission_batches_all
4628 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
4629 BEGIN
4630 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4631 fnd_log.STRING(
4632 fnd_log.level_procedure
4633 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.begin'
4634 , 'Beginning of poulate_bonus_process_batch ...'
4635 );
4636 END IF;
4637
4638 l_counter := 0;
4639
4640 OPEN l_sub_batch_csr;
4641 FETCH l_sub_batch_csr INTO l_hierarchy_flag, l_salesrep_option, l_interval_type_id, l_start_date, l_end_date, l_org_id;
4642 CLOSE l_sub_batch_csr;
4643
4644 SELECT period_id
4645 INTO l_start_period_id
4646 FROM cn_period_statuses_all
4647 WHERE l_start_date BETWEEN start_date AND end_date
4648 AND org_id = l_org_id
4649 AND (period_set_id, period_type_id) = (SELECT period_set_id
4650 , period_type_id
4651 FROM cn_repositories_all
4652 WHERE org_id = l_org_id);
4653
4654 SELECT period_id
4655 INTO l_end_period_id
4656 FROM cn_period_statuses_all
4657 WHERE l_end_date BETWEEN start_date AND end_date
4658 AND org_id = l_org_id
4659 AND (period_set_id, period_type_id) = (SELECT period_set_id
4660 , period_type_id
4661 FROM cn_repositories_all
4662 WHERE org_id = l_org_id);
4663
4664 IF l_salesrep_option = 'ALL_REPS' THEN
4665 IF l_interval_type_id = -1000 OR l_interval_type_id = -1003 THEN
4666 FOR l_srp IN l_all_reps_csr LOOP
4667 FOR l_period IN l_period_int_periods_csr(l_srp.salesrep_id, l_interval_type_id) LOOP
4668 l_counter := 1;
4669
4670 OPEN l_period_info_csr(l_period.period_id);
4671 FETCH l_period_info_csr INTO l_prd;
4672 CLOSE l_period_info_csr;
4673
4674 populate_calcsub_batches(
4675 l_srp.salesrep_id
4676 , l_prd.start_date
4677 , l_prd.end_date
4678 , l_prd.period_id
4679 , l_prd.period_id
4680 , g_logical_batch_id
4681 , l_hierarchy_flag
4682 );
4683 END LOOP;
4684 END LOOP;
4685 ELSIF l_interval_type_id = -1001 THEN
4686 FOR l_srp IN l_all_reps_csr LOOP
4687 FOR l_period IN l_quarter_int_periods_csr(l_srp.salesrep_id) LOOP
4688 -- then check if the period is at the end_of_interval (quarter)
4689 -- and there is active plan on the period.end_date
4690 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4691 -- get the min start date and the max end date for period submitted
4692 OPEN l_period_info_csr(l_period.min_period_id);
4693 FETCH l_period_info_csr INTO l_start_prd;
4694 CLOSE l_period_info_csr;
4695
4696 OPEN l_period_info_csr(l_period.max_period_id);
4697 FETCH l_period_info_csr INTO l_end_prd;
4698 CLOSE l_period_info_csr;
4699
4700 IF check_active_plan_assign(
4701 l_srp.salesrep_id
4702 , l_start_prd.start_date
4703 , l_end_prd.end_date
4704 , l_interval_type_id
4705 , p_calc_sub_batch_id
4706 , g_org_id
4707 ) THEN
4708 l_counter := 1;
4709 populate_calcsub_batches(
4710 l_srp.salesrep_id
4711 , l_start_prd.start_date
4712 , l_end_prd.end_date
4713 , l_end_prd.period_id
4714 , l_end_prd.period_id
4715 , g_logical_batch_id
4716 , l_hierarchy_flag
4717 );
4718 END IF;
4719 END IF;
4720 END LOOP;
4721 END LOOP;
4722 ELSIF l_interval_type_id = -1002 THEN
4723 FOR l_srp IN l_all_reps_csr LOOP
4724 FOR l_period IN l_year_int_periods_csr(l_srp.salesrep_id) LOOP
4725 --then check if the period is at the end_of_interval (quarter)
4726 -- and there is active plan on the period.end_date
4727 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4728 -- get the min start date and the max end date for period submitted
4729 OPEN l_period_info_csr(l_period.min_period_id);
4730 FETCH l_period_info_csr INTO l_start_prd;
4731 CLOSE l_period_info_csr;
4732
4733 OPEN l_period_info_csr(l_period.max_period_id);
4734 FETCH l_period_info_csr INTO l_end_prd;
4735 CLOSE l_period_info_csr;
4736
4737 IF check_active_plan_assign(
4738 l_srp.salesrep_id
4739 , l_start_prd.start_date
4740 , l_end_prd.end_date
4741 , l_interval_type_id
4742 , p_calc_sub_batch_id
4743 , g_org_id
4744 ) THEN
4745 l_counter := 1;
4746 populate_calcsub_batches(
4747 l_srp.salesrep_id
4748 , l_start_prd.start_date
4749 , l_end_prd.end_date
4750 , l_end_prd.period_id
4751 , l_end_prd.period_id
4752 , g_logical_batch_id
4753 , l_hierarchy_flag
4754 );
4755 END IF;
4756 END IF;
4757 END LOOP;
4758 END LOOP;
4759 END IF;
4760 ELSIF l_salesrep_option = 'USER_SPECIFY' THEN
4761 IF l_interval_type_id = -1000 OR l_interval_type_id = -1003 THEN
4762 FOR l_srp IN l_user_reps_csr LOOP
4763 FOR l_period IN l_period_int_periods_csr(l_srp.salesrep_id, l_interval_type_id) LOOP
4764 l_counter := 1;
4765
4766 OPEN l_period_info_csr(l_period.period_id);
4767 FETCH l_period_info_csr INTO l_prd;
4768 CLOSE l_period_info_csr;
4769
4770 populate_calcsub_batches(
4771 l_srp.salesrep_id
4772 , l_prd.start_date
4773 , l_prd.end_date
4774 , l_prd.period_id
4775 , l_prd.period_id
4776 , g_logical_batch_id
4777 , l_srp.hierarchy_flag
4778 );
4779 END LOOP;
4780 END LOOP;
4781 ELSIF l_interval_type_id = -1001 THEN
4782 FOR l_srp IN l_user_reps_csr LOOP
4783 FOR l_period IN l_quarter_int_periods_csr(l_srp.salesrep_id) LOOP
4784 --then check if the period is at the end_of_interval (quarter)
4785 -- and there is active plan on the period.end_date
4786 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4787 -- get the min start date and the max end date for period submitted
4788 OPEN l_period_info_csr(l_period.min_period_id);
4789 FETCH l_period_info_csr INTO l_start_prd;
4790 CLOSE l_period_info_csr;
4791
4792 OPEN l_period_info_csr(l_period.max_period_id);
4793 FETCH l_period_info_csr INTO l_end_prd;
4794 CLOSE l_period_info_csr;
4795
4796 IF check_active_plan_assign(
4797 l_srp.salesrep_id
4798 , l_start_prd.start_date
4799 , l_end_prd.end_date
4800 , l_interval_type_id
4801 , p_calc_sub_batch_id
4802 , g_org_id
4803 ) THEN
4804 l_counter := 1;
4805 populate_calcsub_batches(
4806 l_srp.salesrep_id
4807 , l_start_prd.start_date
4808 , l_end_prd.end_date
4809 , l_end_prd.period_id
4810 , l_end_prd.period_id
4811 , g_logical_batch_id
4812 , l_srp.hierarchy_flag
4813 );
4814 END IF;
4815 END IF;
4816 END LOOP;
4817 END LOOP;
4818 ELSIF l_interval_type_id = -1002 THEN
4819 FOR l_srp IN l_user_reps_csr LOOP
4820 FOR l_period IN l_year_int_periods_csr(l_srp.salesrep_id) LOOP
4821 --then check if the period is at the end_of_interval (quarter)
4822 -- and there is active plan on the period.end_date
4823 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4824 -- get the min start date and the max end date for period submitted
4825 OPEN l_period_info_csr(l_period.min_period_id);
4826
4827 FETCH l_period_info_csr
4828 INTO l_start_prd;
4829
4830 CLOSE l_period_info_csr;
4831
4832 OPEN l_period_info_csr(l_period.max_period_id);
4833
4834 FETCH l_period_info_csr
4835 INTO l_end_prd;
4836
4837 CLOSE l_period_info_csr;
4838
4839 IF check_active_plan_assign(
4840 l_srp.salesrep_id
4841 , l_start_prd.start_date
4842 , l_end_prd.end_date
4843 , l_interval_type_id
4844 , p_calc_sub_batch_id
4845 , g_org_id
4846 ) THEN
4847 l_counter := 1;
4848 populate_calcsub_batches(
4849 l_srp.salesrep_id
4850 , l_start_prd.start_date
4851 , l_end_prd.end_date
4852 , l_end_prd.period_id
4853 , l_end_prd.period_id
4854 , g_logical_batch_id
4855 , l_srp.hierarchy_flag
4856 );
4857 END IF;
4858 END IF;
4859 END LOOP;
4860 END LOOP;
4861 END IF;
4862 END IF;
4863
4864 IF l_counter = 0 THEN /* no one to be calculated */
4865 fnd_message.set_name('CN', 'CNSBCS_NO_ONE_TO_BONUS');
4866
4867 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
4868 fnd_log.MESSAGE(
4869 fnd_log.level_exception
4870 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.error'
4871 , TRUE
4872 );
4873 END IF;
4874
4875 RAISE no_comm_lines;
4876 END IF;
4877
4878 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4879 fnd_log.STRING(
4880 fnd_log.level_procedure
4881 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.end'
4882 , 'Beginning of poulate_bonus_process_batch ...'
4883 );
4884 END IF;
4885 EXCEPTION
4886 WHEN no_comm_lines THEN
4887 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_bonus_process_batch:');
4888 cn_message_pkg.rollback_errormsg_commit
4889 (
4890 'No one with complete compensation plan to calculate or the period specified is not at the end of the plan element interval'
4891 );
4892 fnd_file.put_line
4893 (
4894 fnd_file.LOG
4895 , 'Exception in cn_proc_batches_pkg.populate_bonus_process_batch: no one with complete '
4896 || 'compensation plan or the period specified is not at the end of the interval'
4897 );
4898 RAISE;
4899 WHEN OTHERS THEN
4900 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4901 fnd_log.STRING(
4902 fnd_log.level_unexpected
4903 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.exception'
4904 , SQLERRM
4905 );
4906 END IF;
4907
4908 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_bonus_process_batch: ');
4909 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
4910 fnd_file.put_line(fnd_file.LOG
4911 , 'Exception in cn_proc_batches_pkg.populate_bonus_process_batch: ' || SQLERRM);
4912 RAISE;
4913 END populate_bonus_process_batch;
4914 END cn_proc_batches_pkg;