[Home] [Help]
PACKAGE BODY: APPS.CN_PROC_BATCHES_PKG
Source
1 PACKAGE BODY cn_proc_batches_pkg AS
2 /* $Header: cnsybatb.pls 120.20.12010000.2 2008/09/26 10:07:47 venjayar 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 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
3116
3117 COMMIT;
3118 cn_calc_submission_pvt.calculate(
3119 p_api_version => 1.0
3120 , p_init_msg_list => fnd_api.g_true
3121 , p_validation_level => fnd_api.g_valid_level_full
3122 , x_return_status => l_return_status
3123 , x_msg_count => l_msg_count
3124 , x_msg_data => l_msg_data
3125 , p_calc_sub_batch_id => p_calc_sub_batch_id
3126 );
3127
3128 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3129 FOR l_counter IN 1 .. l_msg_count LOOP
3130 l_msg_data := fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false);
3131 fnd_file.put_line(fnd_file.LOG, l_msg_data);
3132 cn_message_pkg.DEBUG(l_msg_data);
3133 END LOOP;
3134
3135 retcode := 2;
3136 errbuf := l_msg_data;
3137 END IF;
3138
3139 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3140 fnd_log.STRING(
3141 fnd_log.level_procedure
3142 , 'cn.plsql.cn_proc_batches_pkg.calculate_batch.end'
3143 , 'End of calculate_batch.'
3144 );
3145 END IF;
3146
3147 fnd_file.put_line(fnd_file.LOG, 'End of the calculation process.');
3148 cn_message_pkg.DEBUG('End of the calculation process.');
3149 cn_message_pkg.end_batch(l_process_audit_id);
3150 EXCEPTION
3151 WHEN OTHERS THEN
3152 retcode := 2;
3153 errbuf := SQLERRM;
3154
3155 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3156 fnd_log.STRING(
3157 fnd_log.level_unexpected
3158 , 'cn.plsql.cn_proc_batches_pkg.calculate_batch.exception'
3159 , SQLERRM
3160 );
3161 END IF;
3162
3163 cn_message_pkg.end_batch(l_process_audit_id);
3164 END calculate_batch;
3165
3166 PROCEDURE calc(
3167 errbuf OUT NOCOPY VARCHAR2
3168 , retcode OUT NOCOPY NUMBER
3169 , p_batch_name VARCHAR2
3170 , p_start_date DATE
3171 , p_end_date DATE
3172 , p_calc_type VARCHAR2
3173 , p_salesrep_option VARCHAR2
3174 , p_hierarchy_flag VARCHAR2
3175 , p_intelligent_flag VARCHAR2
3176 , p_interval_type_id NUMBER
3177 , p_salesrep_id NUMBER
3178 , p_quota_id NUMBER
3179 ) IS
3180 l_calc_sub_batch_id NUMBER(15);
3181 l_calc_sub_entry_id NUMBER(15);
3182 l_process_audit_id NUMBER(15);
3183 l_process_status_code VARCHAR2(30);
3184 l_error_message VARCHAR2(200);
3185 l_counter NUMBER;
3186 l_org_id NUMBER;
3187
3188 CURSOR l_chk_start_date_csr IS
3189 SELECT 1
3190 FROM DUAL
3191 WHERE EXISTS(SELECT 1
3192 FROM cn_acc_period_statuses_v
3193 WHERE period_status = 'O' AND org_id = l_org_id AND p_start_date >= start_date);
3194
3195 CURSOR l_chk_end_date_csr IS
3196 SELECT 1
3197 FROM DUAL
3198 WHERE EXISTS(SELECT 1
3199 FROM cn_acc_period_statuses_v
3200 WHERE period_status = 'O' AND org_id = l_org_id AND p_end_date <= end_date);
3201
3202 CURSOR l_batch_name_csr IS
3203 SELECT 1
3204 FROM DUAL
3205 WHERE EXISTS(SELECT 1
3206 FROM cn_calc_submission_batches_all
3207 WHERE NAME = p_batch_name AND org_id = l_org_id);
3208
3209 l_incomplete_plan BOOLEAN := FALSE;
3210 BEGIN
3211 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3212 fnd_log.STRING(
3213 fnd_log.level_procedure
3214 , 'cn.plsql.cn_proc_batches_pkg.calc.begin'
3215 , 'Beginning of concurrent program calc ...'
3216 );
3217 END IF;
3218
3219 fnd_file.put_line(fnd_file.LOG, 'Beginning of concurrent program calc ... ');
3220 -- Concurrent Manager will call set_policy_context('S', user_selected_org)
3221 l_org_id := mo_global.get_current_org_id;
3222
3223 -- check uniqueness of batch name
3224 OPEN l_batch_name_csr;
3225 FETCH l_batch_name_csr INTO l_counter;
3226
3227 IF l_batch_name_csr%FOUND THEN
3228 CLOSE l_batch_name_csr;
3229
3230 fnd_message.set_name('CN', 'CN_CALC_SUB_EXISTS');
3231 fnd_message.set_token('BATCH_NAME'
3232 , cn_api.get_lkup_meaning('NAME', 'CALC_SUBMISSION_OBJECT_TYPE'));
3233 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3234
3235 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3236 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3237 END IF;
3238
3239 RAISE ABORT;
3240 END IF;
3241
3242 CLOSE l_batch_name_csr;
3243
3244 -- check the validility of p_start_date and p_end_date
3245 IF p_start_date > p_end_date THEN
3246 fnd_message.set_name('CN', 'CN_INVALID_DATE_RANGE');
3247 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3248
3249 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3250 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3251 END IF;
3252
3253 RAISE ABORT;
3254 ELSE
3255 OPEN l_chk_start_date_csr;
3256 FETCH l_chk_start_date_csr INTO l_counter;
3257
3258 IF l_chk_start_date_csr%NOTFOUND THEN
3259 fnd_message.set_name('CN', 'CN_CALC_SUB_OPEN_DATE');
3260 fnd_message.set_token('DATE', p_start_date);
3261 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3262
3263 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3264 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3265 END IF;
3266
3267 RAISE ABORT;
3268 END IF;
3269
3270 CLOSE l_chk_start_date_csr;
3271
3272 OPEN l_chk_end_date_csr;
3273 FETCH l_chk_end_date_csr INTO l_counter;
3274
3275 IF l_chk_end_date_csr%NOTFOUND THEN
3276 fnd_message.set_name('CN', 'CN_CALC_SUB_OPEN_DATE');
3277 fnd_message.set_token('DATE', p_end_date);
3278 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3279
3280 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3281 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation'
3282 , TRUE);
3283 END IF;
3284
3285 RAISE ABORT;
3286 END IF;
3287
3288 CLOSE l_chk_end_date_csr;
3289 END IF;
3290
3291 IF p_salesrep_option = 'USER_SPECIFY' AND p_salesrep_id IS NULL THEN
3292 fnd_message.set_name('CN', 'CN_CALC_NO_SALESREP');
3293 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3294
3295 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3296 fnd_log.MESSAGE(fnd_log.level_error, 'cn.plsql.cn_proc_batches_pkg.calc.validation', TRUE);
3297 END IF;
3298
3299 RAISE ABORT;
3300 END IF;
3301
3302 l_calc_sub_batch_id := cn_calc_sub_batches_pkg.get_calc_sub_batch_id;
3303 cn_calc_sub_batches_pkg.begin_record
3304 (
3305 p_operation => 'INSERT'
3306 , p_calc_sub_batch_id => l_calc_sub_batch_id
3307 , p_name => p_batch_name
3308 , p_start_date => p_start_date
3309 , p_end_date => p_end_date
3310 , p_calc_type => p_calc_type
3311 , p_salesrep_option => p_salesrep_option
3312 , p_hierarchy_flag => 'N'
3313 , --p_hierarchy_flag,
3314 p_concurrent_flag => 'Y'
3315 , -- always not on-line, so concurrently
3316 p_intelligent_flag => p_intelligent_flag
3317 , p_status => 'INCOMPLETE'
3318 , p_interval_type_id => p_interval_type_id
3319 , p_org_id => l_org_id
3320 );
3321
3322 IF p_salesrep_option = 'USER_SPECIFY' THEN
3323 l_calc_sub_entry_id := cn_calc_sub_entries_pkg.get_calc_sub_entry_id;
3324 cn_calc_sub_entries_pkg.begin_record(
3325 p_operation => 'INSERT'
3326 , p_calc_sub_batch_id => l_calc_sub_batch_id
3327 , p_calc_sub_entry_id => l_calc_sub_entry_id
3328 , p_salesrep_id => p_salesrep_id
3329 , p_hierarchy_flag => p_hierarchy_flag
3330 , p_org_id => l_org_id
3331 );
3332 END IF;
3333
3334 COMMIT;
3335
3336 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level) THEN
3337 fnd_log.STRING(
3338 fnd_log.level_event
3339 , 'cn.plsql.cn_proc_batches_pkg.calc.submission'
3340 , 'Successfully created submission records.'
3341 );
3342 END IF;
3343
3344 IF find_srp_incomplete_plan(l_calc_sub_batch_id) THEN
3345 fnd_file.put_line(fnd_file.LOG
3346 , 'Abort the process because there is rep with incomplete comp plans.');
3347 RAISE ABORT;
3348 ELSE
3349 calculation_submission(
3350 p_calc_sub_batch_id => l_calc_sub_batch_id
3351 , x_process_audit_id => l_process_audit_id
3352 , x_process_status_code => l_process_status_code
3353 );
3354 END IF;
3355
3356 IF (l_process_status_code = 'FAIL') THEN
3357 retcode := 2;
3358 errbuf := 'Calculation fails';
3359 END IF;
3360
3361 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3362 fnd_log.STRING(
3363 fnd_log.level_procedure
3364 , 'cn.plsql.cn_proc_batches_pkg.calc.end'
3365 , 'End of concurrent program calc.'
3366 );
3367 END IF;
3368 EXCEPTION
3369 WHEN fnd_file.utl_file_error THEN
3370 retcode := 2;
3371 errbuf := SUBSTR(fnd_message.get, 1, 254);
3372 WHEN ABORT THEN
3373 retcode := 2;
3374 errbuf := 'Please check request log file for further information. ';
3375
3376 IF l_chk_start_date_csr%ISOPEN THEN
3377 CLOSE l_chk_start_date_csr;
3378 END IF;
3379
3380 IF l_chk_end_date_csr%ISOPEN THEN
3381 CLOSE l_chk_end_date_csr;
3382 END IF;
3383 WHEN OTHERS THEN
3384 retcode := 2;
3385 errbuf := SQLERRM;
3386 END calc;
3387
3388 PROCEDURE calc_curr(
3389 errbuf OUT NOCOPY VARCHAR2
3390 , retcode OUT NOCOPY NUMBER
3391 , p_batch_name VARCHAR2
3392 , p_start_date VARCHAR2
3393 , p_end_date VARCHAR2
3394 , p_calc_type VARCHAR2
3395 , p_salesrep_option VARCHAR2
3396 , p_hierarchy_flag VARCHAR2
3397 , p_intelligent_flag VARCHAR2
3398 , p_salesrep_id NUMBER
3399 ) IS
3400 BEGIN
3401 -- this is a wrapper around the calc procedure to be called from
3402 -- a concurrent program. it eliminates the obsolete variables
3403 -- p_interval_type_id and p_quota_id. it also converts the dates
3404 -- from FND_STANDARD_DATE
3405 calc(
3406 errbuf => errbuf
3407 , retcode => retcode
3408 , p_batch_name => p_batch_name
3409 , p_start_date => fnd_date.canonical_to_date(p_start_date)
3410 , p_end_date => fnd_date.canonical_to_date(p_end_date)
3411 , p_calc_type => p_calc_type
3412 , p_salesrep_option => p_salesrep_option
3413 , p_hierarchy_flag => p_hierarchy_flag
3414 , p_intelligent_flag => p_intelligent_flag
3415 , p_salesrep_id => p_salesrep_id
3416 , p_interval_type_id => -1000
3417 , -- means 'Period'
3418 p_quota_id => NULL
3419 );
3420 END calc_curr;
3421
3422 PROCEDURE collection(
3423 errbuf OUT NOCOPY VARCHAR2
3424 , retcode OUT NOCOPY NUMBER
3425 , p_start_date DATE
3426 , p_end_date DATE
3427 , p_salesrep_id NUMBER
3428 , p_comp_plan_id NUMBER
3429 ) IS
3430 dummy NUMBER;
3431 dummy_char VARCHAR2(30);
3432 BEGIN
3433 cn_proc_batches_pkg.main(
3434 p_concurrent_flag => 'Y'
3435 , p_process_name => 'COLLECTION'
3436 , p_logical_batch_id => NULL
3437 , p_start_date => p_start_date
3438 , p_end_date => p_end_date
3439 , p_salesrep_id => p_salesrep_id
3440 , p_comp_plan_id => p_comp_plan_id
3441 , x_process_audit_id => dummy
3442 , x_process_status_code => dummy_char
3443 );
3444 END collection;
3445
3446 -- Procedure Name
3447 -- Runner (PUBLIC Concurrent Program)
3448 -- Purpose
3449 -- For each distinct physical batch lock the impacted srp_periods and
3450 -- execute collection, classification, roll, populate or calculation
3451 -- on the cn_trx records identified by the physical batch.
3452 -- If the srp_periods cannot be locked the produre executes successfully
3453 -- but doesn't update the srp_period status to the new one.
3454 -- Commits after each batch to help control rollback segment problems
3455 PROCEDURE runner(
3456 errbuf OUT NOCOPY VARCHAR2
3457 , retcode OUT NOCOPY NUMBER
3458 , p_parent_proc_audit_id NUMBER
3459 , p_logical_process VARCHAR2
3460 , p_physical_process VARCHAR2
3461 , p_physical_batch_id NUMBER
3462 , p_salesrep_id NUMBER := NULL
3463 , p_start_date DATE := NULL
3464 , p_end_date DATE := NULL
3465 , p_cls_rol_flag VARCHAR2 := NULL
3466 ) IS
3467 l_curr_status VARCHAR2(30);
3468 l_new_status VARCHAR2(30);
3469 l_tries NUMBER(1);
3470 l_request_id NUMBER(15) := NULL;
3471 l_cls_total NUMBER;
3472 l_xcls_total NUMBER;
3473 l_process_audit_id NUMBER(15);
3474 l_org_id NUMBER;
3475 dummy NUMBER(1);
3476 l_counter NUMBER;
3477 l_msg_count NUMBER;
3478 l_msg_data VARCHAR2(2000);
3479 l_return_status VARCHAR2(30);
3480 l_period_set_id NUMBER;
3481 l_period_type_id NUMBER;
3482 l_calc_from_period_id NUMBER;
3483 l_calc_to_period_id NUMBER;
3484 BEGIN
3485 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3486 fnd_log.STRING(
3487 fnd_log.level_procedure
3488 , 'cn.plsql.cn_proc_batches_pkg.runner.begin'
3489 , 'Beginning of batch runner '
3490 || p_physical_batch_id
3491 || ' in the phase of '
3492 || p_physical_process
3493 || ' at '
3494 || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')
3495 );
3496 END IF;
3497
3498 fnd_file.put_line(fnd_file.LOG
3499 , 'Inside batch runner: ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3500
3501 SELECT org_id
3502 INTO l_org_id
3503 FROM cn_process_batches_all
3504 WHERE physical_batch_id = p_physical_batch_id AND ROWNUM = 1;
3505
3506 l_request_id := fnd_global.conc_request_id;
3507
3508 IF l_request_id <> -1 THEN
3509 cn_message_pkg.begin_batch(
3510 x_process_type => 'CALCULATION'
3511 , x_parent_proc_audit_id => p_parent_proc_audit_id
3512 , x_process_audit_id => l_process_audit_id
3513 , x_request_id => fnd_global.conc_request_id
3514 , p_org_id => l_org_id
3515 );
3516 END IF;
3517
3518 cn_message_pkg.DEBUG(
3519 'Start batch runner (phase=' || p_physical_process || ', batch ID=' || p_physical_batch_id
3520 || ')'
3521 );
3522 cn_proc_batches_pkg.process_status(
3523 x_physical_process => p_physical_process
3524 , x_curr_status => l_curr_status
3525 , x_new_status => l_new_status
3526 );
3527
3528 -- This is the hook if we need to add the collection process
3529 IF p_physical_process = g_collection THEN
3530 NULL;
3531 --cn_collection_pkg.collect(p_physical_batch_id);
3532 ELSIF p_physical_process = g_load THEN
3533 cn_transaction_load_pkg.load_worker(
3534 p_physical_batch_id => p_physical_batch_id
3535 , p_salesrep_id => p_salesrep_id
3536 , p_start_date => p_start_date
3537 , p_end_date => p_end_date
3538 , p_cls_rol_flag => p_cls_rol_flag
3539 );
3540 ELSIF(p_physical_process = g_post) THEN
3541 cn_posting_pvt.post_worker
3542 (
3543 p_parent_proc_audit_id => p_parent_proc_audit_id
3544 , p_posting_batch_id => p_salesrep_id
3545 , -- use p_salesrep_id to pass in posting_batch_id
3546 p_physical_batch_id => p_physical_batch_id
3547 , p_start_date => p_start_date
3548 , p_end_date => p_end_date
3549 );
3550 ELSIF p_physical_process = g_revert THEN
3551 cn_formula_common_pkg.revert_batch(p_physical_batch_id);
3552 ELSIF p_physical_process = g_classification THEN
3553 cn_calc_classify_pvt.classify_batch(
3554 p_api_version => 1.0
3555 , p_init_msg_list => fnd_api.g_true
3556 , p_commit => fnd_api.g_true
3557 , x_return_status => l_return_status
3558 , x_msg_count => l_msg_count
3559 , x_msg_data => l_msg_data
3560 , p_physical_batch_id => p_physical_batch_id
3561 );
3562
3563 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3564 cn_message_pkg.DEBUG('Exception occurs in classification process:');
3565
3566 FOR l_counter IN 1 .. l_msg_count LOOP
3567 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3568 , p_encoded => fnd_api.g_false));
3569 END LOOP;
3570
3571 RAISE api_call_failed;
3572 END IF;
3573 ELSIF p_physical_process = g_rollup THEN
3574 cn_calc_rollup_pvt.rollup_batch(
3575 p_api_version => 1.0
3576 , p_init_msg_list => fnd_api.g_true
3577 , p_commit => fnd_api.g_true
3578 , x_return_status => l_return_status
3579 , x_msg_count => l_msg_count
3580 , x_msg_data => l_msg_data
3581 , p_physical_batch_id => p_physical_batch_id
3582 );
3583
3584 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3585 cn_message_pkg.DEBUG('Exception occurs in rollup phase:');
3586
3587 FOR l_counter IN 1 .. l_msg_count LOOP
3588 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3589 , p_encoded => fnd_api.g_false));
3590 END LOOP;
3591
3592 RAISE api_call_failed;
3593 END IF;
3594 ELSIF p_physical_process = g_population THEN
3595 cn_calc_populate_pvt.populate_batch(
3596 p_api_version => 1.0
3597 , p_init_msg_list => fnd_api.g_true
3598 , p_commit => fnd_api.g_true
3599 , x_return_status => l_return_status
3600 , x_msg_count => l_msg_count
3601 , x_msg_data => l_msg_data
3602 , p_physical_batch_id => p_physical_batch_id
3603 );
3604
3605 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3606 cn_message_pkg.DEBUG('Exception occurs in population phase:');
3607
3608 FOR l_counter IN 1 .. l_msg_count LOOP
3609 cn_message_pkg.DEBUG(fnd_msg_pub.get(p_msg_index => l_counter
3610 , p_encoded => fnd_api.g_false));
3611 END LOOP;
3612
3613 RAISE api_call_failed;
3614 END IF;
3615 ELSIF p_physical_process = g_calculation THEN
3616 cn_global_var.initialize_instance_info(l_org_id);
3617 cn_formula_common_pkg.calculate_batch(p_physical_batch_id);
3618 END IF;
3619
3620 cn_message_pkg.DEBUG(
3621 'Complete batch runner (phase='
3622 || p_physical_process
3623 || ', batch ID='
3624 || p_physical_batch_id
3625 || ')'
3626 );
3627
3628 IF ((p_physical_process = g_load) AND(p_cls_rol_flag = 'N' OR p_cls_rol_flag IS NULL)) THEN
3629 NULL; -- do not update processing_status_code
3630 ELSIF(p_physical_process IN(g_revert, g_classification, g_rollup, g_population, g_calculation)) THEN
3631 -- raise cn_srp_intel_periods.processing_status_code from 'CLEAN' to 'NOT CLEAN'
3632 SELECT period_set_id
3633 , period_type_id
3634 INTO l_period_set_id
3635 , l_period_type_id
3636 FROM cn_repositories_all
3637 WHERE org_id = l_org_id;
3638
3639 SELECT MAX(period_id)
3640 , MAX(end_period_id)
3641 INTO l_calc_from_period_id
3642 , l_calc_to_period_id
3643 FROM cn_process_batches_all
3644 WHERE physical_batch_id = p_physical_batch_id;
3645
3646 UPDATE cn_srp_intel_periods_all
3647 SET processing_status_code =
3648 DECODE(
3649 p_physical_process
3650 , g_revert, g_reverted
3651 , g_classification, g_classified
3652 , g_rollup, g_rolled_up
3653 , g_population, g_populated
3654 , g_calculation, g_calculated
3655 , g_unclassified
3656 )
3657 WHERE (salesrep_id, period_id) IN(
3658 SELECT batch.salesrep_id
3659 , per.period_id
3660 FROM cn_process_batches_all batch, cn_period_statuses_all per
3661 WHERE batch.physical_batch_id = p_physical_batch_id
3662 AND per.period_id BETWEEN batch.period_id AND batch.end_period_id
3663 AND per.org_id = batch.org_id
3664 AND per.period_id BETWEEN l_calc_from_period_id AND l_calc_to_period_id
3665 AND per.period_set_id = l_period_set_id
3666 AND per.period_type_id = l_period_type_id);
3667 END IF;
3668
3669 cn_message_pkg.FLUSH;
3670 COMMIT;
3671
3672 -- If run as a conc program it will have its own process audit id
3673 -- and request id therefore we need to give info on the process
3674 IF l_request_id <> -1 THEN
3675 cn_message_pkg.end_batch(l_process_audit_id);
3676 END IF;
3677
3678 retcode := 0;
3679 errbuf := 'Batch runner completes successfully.';
3680
3681 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3682 fnd_log.STRING(
3683 fnd_log.level_procedure
3684 , 'cn.plsql.cn_proc_batches_pkg.runner.end'
3685 , 'End of batch runner '
3686 || p_physical_batch_id
3687 || 'at '
3688 || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS')
3689 );
3690 END IF;
3691
3692 cn_message_pkg.DEBUG('Time is ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3693 fnd_file.put_line(fnd_file.LOG, 'Time is ' || TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS'));
3694 EXCEPTION
3695 WHEN api_call_failed THEN
3696 retcode := 2;
3697 errbuf := SUBSTR(fnd_message.get, 1, 254);
3698
3699 IF (l_msg_count > 0) THEN
3700 FOR l_counter IN 1 .. l_msg_count LOOP
3701 fnd_file.put_line(fnd_file.LOG
3702 , fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false));
3703 END LOOP;
3704 END IF;
3705
3706 cn_message_pkg.rollback_errormsg_commit('Exception occurs in batch runner (ID='
3707 || p_physical_batch_id || ')');
3708 update_error(p_physical_batch_id);
3709
3710 -- if concurrent program, commit and close log file
3711 -- if sequential calcualtion, commit and leave log file open
3712 IF l_request_id <> -1 THEN
3713 cn_message_pkg.end_batch(l_process_audit_id);
3714 ELSE
3715 COMMIT;
3716 END IF;
3717 WHEN OTHERS THEN
3718 -- Return to concurrent manager with error. Xinyang Fan 4/13/98
3719 retcode := 2;
3720 errbuf := SQLERRM;
3721 -- to make sure we record the updates made by update_error
3722 -- we roll back first
3723 cn_message_pkg.DEBUG('Exception occurs in batch runner (ID=' || p_physical_batch_id || ')');
3724 cn_message_pkg.rollback_errormsg_commit(errbuf);
3725 update_error(p_physical_batch_id);
3726
3727 -- if concurrent program, commit and close log file
3728 -- if sequential calcualtion, commit and leave log file open
3729 IF l_request_id <> -1 THEN
3730 cn_message_pkg.end_batch(l_process_audit_id);
3731 ELSE
3732 COMMIT;
3733 END IF;
3734
3735 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
3736 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_proc_batches_pkg.runner.exception'
3737 , SQLERRM);
3738 END IF;
3739 END runner;
3740
3741 -- Check if the period submitted for calc is covered by one or more rulesets.
3742 -- NOTE: it does not check for the validity of the ruleset. That is done by validate_ruleset_status
3743 PROCEDURE validate_ruleset_coverage(
3744 p_start_date DATE
3745 , p_end_date DATE
3746 , x_covered OUT NOCOPY BOOLEAN
3747 , p_org_id NUMBER
3748 ) IS
3749 CURSOR ruleset_cur IS
3750 SELECT ruleset_id
3751 , start_date
3752 , NVL(end_date, p_end_date) end_date
3753 FROM cn_rulesets_all_b
3754 WHERE org_id = p_org_id
3755 AND (
3756 (start_date <= p_start_date AND NVL(end_date, p_start_date) >= p_start_date)
3757 OR (start_date BETWEEN p_start_date AND p_end_date)
3758 )
3759 ORDER BY start_date;
3760
3761 l_cur_end_date DATE := p_start_date;
3762 BEGIN
3763 x_covered := FALSE;
3764
3765 FOR ruleset IN ruleset_cur LOOP
3766 IF (ruleset.start_date <= l_cur_end_date) OR(ruleset.start_date =(l_cur_end_date + 1)) THEN
3767 l_cur_end_date := ruleset.end_date;
3768 ELSE
3769 x_covered := FALSE;
3770 EXIT;
3771 END IF;
3772
3773 IF ruleset.end_date >= p_end_date THEN
3774 x_covered := TRUE;
3775 EXIT;
3776 END IF;
3777 END LOOP;
3778
3779 IF x_covered = FALSE THEN
3780 cn_message_pkg.DEBUG(
3781 'No classification ruleset is defined for the period from '
3782 || p_start_date
3783 || ' to '
3784 || p_end_date
3785 );
3786 END IF;
3787 END validate_ruleset_coverage;
3788
3789 FUNCTION validate_ruleset_status(p_start_date DATE, p_end_date DATE, p_org_id NUMBER)
3790 RETURN BOOLEAN IS
3791 CURSOR l_rulesets_csr IS
3792 SELECT ruleset_id
3793 , NAME
3794 , ruleset_status
3795 , start_date
3796 , end_date
3797 FROM cn_rulesets_all_vl
3798 WHERE org_id = p_org_id
3799 AND start_date <= p_end_date
3800 AND p_start_date <= NVL(end_date, p_end_date)
3801 AND module_type = 'REVCLS';
3802
3803 CURSOR l_chk_rule_package_csr(l_pkg_name user_objects.object_name%TYPE) IS
3804 SELECT COUNT(*)
3805 FROM user_objects ob
3806 WHERE ob.object_name = l_pkg_name AND ob.object_type = 'PACKAGE BODY';
3807
3808 cached_org_id INTEGER;
3809 cached_org_append VARCHAR2(100);
3810 l_cls_pkg_name user_objects.object_name%TYPE;
3811 l_error_ctr NUMBER := 0;
3812 l_counter NUMBER := 0;
3813 x_covered BOOLEAN := FALSE;
3814 BEGIN
3815 -- check to make sure the specified dates are covered by one or more rulesets
3816 validate_ruleset_coverage(p_start_date, p_end_date, x_covered, p_org_id);
3817
3818 IF x_covered = FALSE THEN
3819 RETURN FALSE;
3820 END IF;
3821
3822 cached_org_id := p_org_id;
3823
3824 IF cached_org_id = -99 THEN
3825 cached_org_append := '_MINUS99';
3826 ELSE
3827 cached_org_append := '_' || cached_org_id;
3828 END IF;
3829
3830 FOR l_set IN l_rulesets_csr LOOP
3831 IF l_set.ruleset_status IN('UNSYNC', 'INSTINPG', 'INSTFAIL', 'CONCFAIL') THEN
3832 cn_message_pkg.DEBUG('Please synchronize ruleset (Name=' || l_set.NAME || ')');
3833 l_error_ctr := 1;
3834 ELSE
3835 l_cls_pkg_name := 'cn_clsfn_' || TO_CHAR(ABS(l_set.ruleset_id)) || cached_org_append;
3836
3837 OPEN l_chk_rule_package_csr(UPPER(l_cls_pkg_name));
3838 FETCH l_chk_rule_package_csr INTO l_counter;
3839 CLOSE l_chk_rule_package_csr;
3840
3841 IF l_counter = 0 THEN
3842 cn_message_pkg.DEBUG('Please synchronize ruleset (name=' || l_set.NAME || ')');
3843 cn_message_pkg.DEBUG('Classification package is missing (name=' || l_cls_pkg_name || ')');
3844 l_error_ctr := 1;
3845 END IF;
3846 END IF;
3847 END LOOP;
3848
3849 IF l_error_ctr = 1 THEN
3850 fnd_message.set_name('CN', 'PROC_CLS_PKG_MISSING');
3851
3852 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3853 fnd_log.MESSAGE(
3854 fnd_log.level_error
3855 , 'cn.plsql.cn_proc_batches_pkg.validate_ruleset_status.validation'
3856 , TRUE
3857 );
3858 END IF;
3859
3860 RETURN FALSE;
3861 END IF;
3862
3863 RETURN TRUE;
3864 END validate_ruleset_status;
3865
3866 -- Procedure Name
3867 -- Processor
3868 -- Purpose
3869 -- Called from concurrent manager or from main
3870 -- If called from SRS the procedure must create the logical batch from
3871 -- the passed parameters.
3872 -- If called from the commissions batch processing UI's the logical id
3873 -- will be present and the logical batch already exists.
3874
3875 -- Notes
3876 -- Logical flag is null when called as a conc program
3877 -- Online flag allows the trx_processor to determine whether it can submit
3878 -- its own concurrent programs
3879 --
3880 PROCEDURE processor(
3881 errbuf OUT NOCOPY VARCHAR2
3882 , retcode OUT NOCOPY NUMBER
3883 , p_parent_proc_audit_id NUMBER
3884 , p_concurrent_flag VARCHAR2
3885 , p_process_name VARCHAR2
3886 , p_logical_batch_id NUMBER
3887 , p_start_date DATE
3888 , p_end_date DATE
3889 , p_salesrep_id NUMBER
3890 , p_comp_plan_id NUMBER
3891 ) IS
3892 l_process_audit_id NUMBER(15);
3893 l_request_id NUMBER(15);
3894 l_paid NUMBER(15);
3895 l_temp NUMBER;
3896 l_logical_batch_id NUMBER(15);
3897 l_calc_sub_batch_id NUMBER(15);
3898 l_salesrep_option VARCHAR2(30);
3899 -- for update payee subledger
3900 l_loading_status VARCHAR2(50);
3901 l_return_status VARCHAR2(50);
3902 l_msg_count NUMBER;
3903 l_msg_data VARCHAR2(2000);
3904 l_ledger_je_batch cn_calc_subledger_pvt.je_batch_rec_type;
3905 l_ledger_je_batch_id NUMBER(15);
3906 BEGIN
3907 l_request_id := fnd_global.conc_request_id;
3908 g_logical_process := p_process_name;
3909 g_logical_batch_id := p_logical_batch_id;
3910
3911 SELECT calc_type
3912 , org_id
3913 INTO g_calc_type
3914 , g_org_id
3915 FROM cn_calc_submission_batches_all
3916 WHERE logical_batch_id = g_logical_batch_id;
3917
3918 -- Accept the current parent id and get the id for this batch if
3919 -- this is a concurrent request and it wasn't submitted from the
3920 -- calc submission form.
3921 -- If it is a conc request and was submitted from the form then
3922 -- we will already have the process audit id
3923 IF l_request_id <> -1 THEN
3924 cn_message_pkg.begin_batch(
3925 x_process_type => 'CALCULATION'
3926 , x_parent_proc_audit_id => p_parent_proc_audit_id
3927 , x_process_audit_id => l_process_audit_id
3928 , x_request_id => fnd_global.conc_request_id
3929 , p_org_id => g_org_id
3930 );
3931 END IF;
3932
3933 -- Group the srp periods into physical batches
3934 BEGIN
3935 SELECT physical_batch_id
3936 INTO l_temp
3937 FROM cn_process_batches_all
3938 WHERE logical_batch_id = g_logical_batch_id AND ROWNUM = 1;
3939 EXCEPTION
3940 WHEN OTHERS THEN
3941 l_temp := -1;
3942 END;
3943
3944 IF (l_temp IS NULL) THEN
3945 assign;
3946 ELSIF(l_temp = -1) THEN
3947 GOTO end_no_trx;
3948 END IF;
3949
3950 -- Only pass the new one if running concurrently
3951 l_paid := NVL(l_process_audit_id, p_parent_proc_audit_id);
3952
3953 UPDATE cn_calc_submission_batches_all
3954 SET --ledger_je_batch_id = l_ledger_je_batch_id,
3955 process_audit_id = l_paid
3956 WHERE logical_batch_id = g_logical_batch_id;
3957
3958 IF p_concurrent_flag = 'N' THEN
3959 seq_dispatch(l_paid);
3960 ELSE
3961 IF (l_temp IS NULL) THEN
3962 conc_dispatch(l_paid);
3963 ELSE
3964 conc_dispatch2(l_paid);
3965 END IF;
3966
3967 BEGIN
3968 SELECT 1
3969 INTO l_temp
3970 FROM SYS.DUAL
3971 WHERE NOT EXISTS(SELECT 1
3972 FROM cn_process_batches_all
3973 WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
3974
3975 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
3976 fnd_message.set_name('CN', 'ALL_PROCESS_DONE_OK');
3977
3978 IF (fnd_log.level_event >= fnd_log.g_current_runtime_level) THEN
3979 fnd_log.MESSAGE(fnd_log.level_event, 'cn.plsql.cn_proc_batches_pkg.processor.event'
3980 , TRUE);
3981 END IF;
3982 EXCEPTION
3983 WHEN NO_DATA_FOUND THEN
3984 retcode := 2;
3985 errbuf := 'Completed with error. Please see the log file for details.';
3986 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
3987 fnd_message.set_name('CN', 'ALL_PROCESS_DONE_FAIL');
3988
3989 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
3990 fnd_log.MESSAGE(fnd_log.level_error
3991 , 'cn.plsql.cn_proc_batches_pkg.processor.exception', TRUE);
3992 END IF;
3993 END;
3994
3995 -- Mark the processed batches for deletion
3996 void_batches(NULL);
3997 END IF;
3998
3999 <<end_no_trx>>
4000 cn_message_pkg.end_batch(l_paid);
4001 EXCEPTION
4002 WHEN OTHERS THEN
4003 retcode := 2;
4004 errbuf := SQLERRM;
4005 fnd_file.put_line(fnd_file.LOG, 'Unexpected exception in cn_proc_batches_pkg.processor');
4006 fnd_file.put_line(fnd_file.LOG, SQLERRM);
4007 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.processor:');
4008 cn_message_pkg.rollback_errormsg_commit(errbuf);
4009 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4010 cn_message_pkg.end_batch(l_paid);
4011
4012 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4013 fnd_log.STRING(fnd_log.level_unexpected
4014 , 'cn.plsql.cn_proc_batches_pkg.processor.exception', SQLERRM);
4015 END IF;
4016
4017 RAISE;
4018 END processor;
4019
4020 -- processor concurrent wrapper on top of processor, called by the concurrent program CN_BATPROC.
4021 -- Do the Canonical-to-Date conversion on the date prarmeters, bug 2610735
4022 PROCEDURE processor_curr(
4023 errbuf OUT NOCOPY VARCHAR2
4024 , retcode OUT NOCOPY NUMBER
4025 , p_parent_proc_audit_id NUMBER
4026 , p_concurrent_flag VARCHAR2
4027 , p_process_name VARCHAR2
4028 , p_logical_batch_id NUMBER
4029 , p_start_date VARCHAR2
4030 , p_end_date VARCHAR2
4031 , p_salesrep_id NUMBER
4032 , p_comp_plan_id NUMBER
4033 ) IS
4034 BEGIN
4035 processor(
4036 errbuf => errbuf
4037 , retcode => retcode
4038 , p_parent_proc_audit_id => p_parent_proc_audit_id
4039 , p_concurrent_flag => p_concurrent_flag
4040 , p_process_name => p_process_name
4041 , p_logical_batch_id => p_logical_batch_id
4042 , p_start_date => fnd_date.canonical_to_date(p_start_date)
4043 , p_end_date => fnd_date.canonical_to_date(p_end_date)
4044 , p_salesrep_id => p_salesrep_id
4045 , p_comp_plan_id => p_comp_plan_id
4046 );
4047 END processor_curr;
4048
4049 PROCEDURE main(
4050 p_concurrent_flag VARCHAR2 DEFAULT 'N'
4051 , p_process_name VARCHAR2 DEFAULT 'CALCULATION'
4052 , p_logical_batch_id NUMBER
4053 , p_start_date DATE
4054 , p_end_date DATE
4055 , p_salesrep_id NUMBER
4056 , p_comp_plan_id NUMBER
4057 , x_process_audit_id IN OUT NOCOPY NUMBER
4058 , x_process_status_code OUT NOCOPY VARCHAR2
4059 ) IS
4060 l_temp NUMBER(1);
4061 l_dummy VARCHAR2(80);
4062 l_request_id NUMBER;
4063 l_process_audit_id NUMBER(15);
4064 BEGIN
4065 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4066 fnd_log.STRING(
4067 fnd_log.level_procedure
4068 , 'cn.plsql.cn_proc_batches_pkg.main.begin'
4069 , 'Beginning of cn_proc_batches_pkg.main...'
4070 );
4071 END IF;
4072
4073 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'PROCESSING');
4074 COMMIT;
4075
4076 -- The process audit_id is passed in when called from the trx form.
4077 IF x_process_audit_id IS NULL THEN
4078 cn_message_pkg.begin_batch(
4079 x_process_type => 'CALCULATION'
4080 , x_parent_proc_audit_id => NULL
4081 , x_process_audit_id => x_process_audit_id
4082 , x_request_id => fnd_global.conc_request_id
4083 , p_org_id => g_org_id
4084 );
4085
4086 UPDATE cn_calc_submission_batches_all
4087 SET process_audit_id = x_process_audit_id
4088 WHERE logical_batch_id = p_logical_batch_id;
4089 END IF;
4090
4091 -- Validate the process name parameter. Currently we only pass 'calculation'
4092 -- but in future we may call collection and other processes.
4093 -- And validate ruleset's status
4094 IF p_process_name = g_collection THEN
4095 NULL;
4096 ELSIF p_process_name IN(g_classification, g_rollup, g_population, g_calculation) THEN
4097 IF NOT validate_ruleset_status(p_start_date, p_end_date, g_org_id) THEN
4098 fnd_file.put_line(fnd_file.LOG, 'classification ruleset is not valid');
4099 RAISE ABORT;
4100 END IF;
4101 ELSE
4102 fnd_file.put_line(fnd_file.LOG
4103 , 'cn_proc_batches_pkg.main: bad process name: ' || p_process_name);
4104 cn_message_pkg.DEBUG('Invalid process code (' || p_process_name || ')');
4105 RAISE ABORT;
4106 END IF;
4107
4108 IF NVL(cn_global_var.g_system_batch_size, 0) = 0 THEN
4109 cn_message_pkg.set_name('CN', 'PROC_BAD_BATCH_SIZE');
4110 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4111 RAISE ABORT;
4112 END IF;
4113
4114 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4115 fnd_log.STRING(
4116 fnd_log.level_procedure
4117 , 'cn.plsql.cn_proc_batches_pkg.main.run_processor'
4118 , 'Before calling procedure processor.'
4119 );
4120 END IF;
4121
4122 IF (p_concurrent_flag = 'N' OR(p_concurrent_flag = 'Y' AND fnd_global.conc_program_id <> -1)) THEN
4123 cn_message_pkg.DEBUG('Start processing transactions (non concurrent calculation)');
4124 cn_proc_batches_pkg.processor(
4125 l_dummy
4126 , l_temp
4127 , x_process_audit_id
4128 , p_concurrent_flag
4129 , p_process_name
4130 , p_logical_batch_id
4131 , p_start_date
4132 , p_end_date
4133 , p_salesrep_id
4134 , p_comp_plan_id
4135 );
4136 cn_message_pkg.DEBUG('End processing transactions (non concurrent calculation)');
4137
4138 IF (l_temp = 2) THEN
4139 x_process_status_code := 'FAIL';
4140 ELSE
4141 BEGIN
4142 SELECT 1
4143 INTO l_temp
4144 FROM SYS.DUAL
4145 WHERE NOT EXISTS(SELECT 1
4146 FROM cn_process_batches_all
4147 WHERE logical_batch_id = p_logical_batch_id AND status_code = 'ERROR');
4148
4149 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'COMPLETE');
4150 x_process_status_code := 'SUCCESS';
4151 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_OK');
4152 cn_message_pkg.end_batch(x_process_audit_id);
4153 EXCEPTION
4154 WHEN NO_DATA_FOUND THEN
4155 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4156 x_process_status_code := 'FAIL';
4157 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_FAIL');
4158 cn_message_pkg.end_batch(x_process_audit_id);
4159 END;
4160
4161 COMMIT;
4162 END IF;
4163
4164 -- Mark the processed batches for deletion
4165 void_batches(NULL);
4166 ELSE
4167 fnd_request.set_org_id(g_org_id);
4168 l_request_id :=
4169 fnd_request.submit_request(
4170 application => 'CN'
4171 , program => 'BATCH_PROCESSOR'
4172 , description => NULL
4173 , start_time => NULL
4174 , sub_request => NULL
4175 , argument1 => x_process_audit_id
4176 , argument2 => p_concurrent_flag
4177 , argument3 => p_process_name
4178 , argument4 => p_logical_batch_id
4179 , argument5 => fnd_date.date_to_canonical(p_start_date)
4180 , argument6 => fnd_date.date_to_canonical(p_end_date)
4181 , argument7 => p_salesrep_id
4182 , argument8 => p_comp_plan_id
4183 );
4184
4185 IF l_request_id = 0 THEN
4186 fnd_file.put_line(fnd_file.LOG
4187 , 'cn_proc_batches_pkg.main: unable to submit batch_processor');
4188 cn_message_pkg.DEBUG('Failed to submit concurrent request (Batch Processor)');
4189 cn_message_pkg.DEBUG(fnd_message.get);
4190 x_process_status_code := 'FAIL';
4191
4192 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4193 fnd_log.STRING(
4194 fnd_log.level_unexpected
4195 , 'cn.plsql.cn_proc_batches_pkg.main.exception'
4196 , 'Failed to submit request for BATCH_PROCESSOR.'
4197 );
4198 END IF;
4199
4200 RAISE ABORT;
4201 ELSE
4202 x_process_status_code := 'SUCCESS';
4203 -- a separate process is being called so we need to wrap up this
4204 -- batch of messages
4205 cn_message_pkg.end_batch(x_process_audit_id);
4206 -- for concurrent request, it makes more sense to return request_id.
4207 -- It is better to use another parameter to return request_id.
4208 x_process_audit_id := l_request_id;
4209 cn_message_pkg.FLUSH;
4210 COMMIT;
4211 END IF;
4212 END IF;
4213
4214 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4215 fnd_log.STRING(
4216 fnd_log.level_procedure
4217 , 'cn.plsql.cn_proc_batches_pkg.main.run_processor'
4218 , 'After calling procedure processor.'
4219 );
4220 END IF;
4221
4222 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4223 fnd_log.STRING(
4224 fnd_log.level_procedure
4225 , 'cn.plsql.cn_proc_batches_pkg.main.end'
4226 , 'End of cn_proc_batches_pkg.main...'
4227 );
4228 END IF;
4229 EXCEPTION
4230 WHEN ABORT THEN
4231 cn_message_pkg.rollback_errormsg_commit('Exception occurs in cn_proc_batches_pkg.main.');
4232 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4233 COMMIT;
4234 x_process_status_code := 'FAIL';
4235 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4236 cn_message_pkg.end_batch(x_process_audit_id);
4237 WHEN OTHERS THEN
4238 fnd_file.put_line(fnd_file.LOG, 'unexpected exception in cn_proc_batches_pkg.main');
4239 fnd_file.put_line(fnd_file.LOG, SQLERRM);
4240 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.main: ');
4241 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
4242 cn_calc_sub_batches_pkg.update_calc_sub_batch(g_logical_batch_id, 'FAILED');
4243 COMMIT;
4244 x_process_status_code := 'FAIL';
4245 --cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_FAIL');
4246 cn_message_pkg.end_batch(x_process_audit_id);
4247
4248 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4249 fnd_log.STRING(fnd_log.level_unexpected, 'cn.plsql.cn_proc_batches_pkg.main.exception'
4250 , SQLERRM);
4251 END IF;
4252 END main;
4253
4254 FUNCTION get_period_name(x_period_id IN NUMBER, p_org_id IN NUMBER)
4255 RETURN VARCHAR2 IS
4256 x_period_name VARCHAR2(30);
4257 BEGIN
4258 IF x_period_id IS NOT NULL THEN
4259 SELECT period_name
4260 INTO x_period_name
4261 FROM cn_period_statuses_all
4262 WHERE period_id = x_period_id AND org_id = p_org_id;
4263
4264 RETURN x_period_name;
4265 END IF;
4266
4267 RETURN NULL;
4268 END;
4269
4270 PROCEDURE get_person_name_num(
4271 x_salesrep_id NUMBER
4272 , p_org_id NUMBER
4273 , x_name IN OUT NOCOPY VARCHAR2
4274 , x_num IN OUT NOCOPY VARCHAR2
4275 ) IS
4276 BEGIN
4277 SELECT NAME
4278 , employee_number
4279 INTO x_name
4280 , x_num
4281 FROM cn_salesreps
4282 WHERE salesrep_id = x_salesrep_id AND org_id = p_org_id;
4283 END get_person_name_num;
4284
4285 --
4286 -- Name
4287 -- check_end_of_interval
4288 -- Purpose
4289 -- Returns 1 if the specified period is the end of an interval of the
4290 -- type listed int he X_Interval string.
4291 -- History
4292 -- 06/13/95 Created Rjin
4293 --
4294 FUNCTION check_end_of_interval(p_period_id NUMBER, p_interval_type_id NUMBER, p_org_id NUMBER)
4295 RETURN BOOLEAN IS
4296 l_end_period_id NUMBER(15);
4297 BEGIN
4298 SELECT MAX(ps2.period_id)
4299 INTO l_end_period_id
4300 FROM cn_period_statuses_all ps1, cn_period_statuses_all ps2
4301 WHERE ps1.org_id = p_org_id
4302 AND ps1.period_id = p_period_id
4303 AND ps2.period_set_id = ps1.period_set_id
4304 AND ps2.period_type_id = ps1.period_type_id
4305 AND ps2.period_year = ps1.period_year
4306 AND ps2.org_id = ps1.org_id
4307 AND (
4308 (p_interval_type_id = -1001 AND ps2.quarter_num = ps1.quarter_num) -- quarter interval
4309 OR p_interval_type_id = -1002
4310 ); -- year interval
4311
4312 IF p_period_id = l_end_period_id THEN
4313 RETURN TRUE;
4314 ELSE
4315 RETURN FALSE;
4316 END IF;
4317 EXCEPTION
4318 WHEN OTHERS THEN
4319 RETURN FALSE;
4320 END check_end_of_interval;
4321
4322 --
4323 -- Name
4324 -- check_active_plan_assign
4325 -- Purpose
4326 -- Returns 1 if the specified period is the end of an interval of the
4327 -- type listed int he X_Interval string.
4328 -- History
4329 -- 06/13/95 Created Tony Lower
4330 --
4331 FUNCTION check_active_plan_assign(
4332 p_salesrep_id NUMBER
4333 , p_start_date DATE
4334 , p_end_date DATE
4335 , p_interval_type_id NUMBER
4336 , p_calc_sub_batch_id NUMBER
4337 , p_org_id NUMBER
4338 )
4339 RETURN BOOLEAN IS
4340 CURSOR l_active_plan_csr IS
4341 SELECT 1
4342 FROM DUAL
4343 WHERE EXISTS(
4344 SELECT 1
4345 FROM cn_srp_plan_assigns_all spa, cn_comp_plans_all PLAN
4346 WHERE spa.salesrep_id = p_salesrep_id
4347 AND spa.org_id = p_org_id
4348 AND (
4349 (
4350 spa.end_date IS NOT NULL
4351 AND p_end_date BETWEEN spa.start_date AND spa.end_date
4352 )
4353 OR (p_end_date >= spa.start_date AND spa.end_date IS NULL)
4354 )
4355 AND spa.comp_plan_id = PLAN.comp_plan_id
4356 AND PLAN.status_code = 'COMPLETE')
4357 OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
4358 (
4359 SELECT 1
4360 FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
4361 WHERE spa.salesrep_id = p_salesrep_id
4362 AND spa.org_id = p_org_id
4363 AND spa.end_date >= p_start_date
4364 AND spa.end_date < p_end_date
4365 AND qa.comp_plan_id = spa.comp_plan_id
4366 AND qa.quota_id = pe.quota_id
4367 AND pe.incentive_type_code = 'BONUS'
4368 AND pe.salesreps_enddated_flag = 'Y'
4369 AND pe.interval_type_id = p_interval_type_id
4370 -- plan element is effective on spa.end_date
4371 AND (
4372 (
4373 pe.end_date IS NOT NULL
4374 AND spa.end_date BETWEEN pe.start_date AND pe.end_date
4375 )
4376 OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
4377 )
4378 -- check if in cn_calc_sub_quotas if that exists
4379 AND (
4380 (0 = (SELECT COUNT(*)
4381 FROM cn_calc_sub_quotas
4382 WHERE calc_sub_batch_id = p_calc_sub_batch_id))
4383 OR (pe.quota_id IN(SELECT csq.quota_id
4384 FROM cn_calc_sub_quotas csq
4385 WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
4386 ));
4387
4388 dummy NUMBER := 0;
4389 BEGIN
4390 OPEN l_active_plan_csr;
4391 FETCH l_active_plan_csr INTO dummy;
4392 CLOSE l_active_plan_csr;
4393
4394 IF dummy = 1 THEN
4395 RETURN TRUE;
4396 ELSE
4397 RETURN FALSE;
4398 END IF;
4399 END check_active_plan_assign;
4400
4401 -- Procedure Name
4402 -- Populate_bonus_process_batch
4403 -- Purpose
4404 -- populate the cn_process_batch for an entry in cn_calc_submission_batches
4405 -- Notes
4406 -- 12-Jul-1998, Richard Jin Created
4407 PROCEDURE populate_bonus_process_batch(p_calc_sub_batch_id NUMBER) IS
4408 l_intelligent_flag VARCHAR2(1);
4409 l_hierarchy_flag VARCHAR2(1);
4410 l_salesrep_option VARCHAR2(20);
4411 l_counter NUMBER;
4412 l_interval_type_id NUMBER(15);
4413 l_start_date DATE;
4414 l_end_date DATE;
4415 l_start_period_id NUMBER;
4416 l_end_period_id NUMBER;
4417 l_org_id NUMBER;
4418
4419 -- cursors to select salesrep with active comp plan within the calc_submission
4420 -- start_date and end_date
4421 CURSOR l_all_reps_csr IS
4422 SELECT DISTINCT spa.salesrep_id
4423 FROM cn_srp_plan_assigns_all spa, cn_calc_submission_batches_all bat
4424 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4425 AND spa.org_id = bat.org_id
4426 AND spa.start_date <= bat.end_date
4427 AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
4428 --code added for forwardport bug 6600074
4429 AND EXISTS(SELECT 1
4430 FROM cn_comp_plans
4431 WHERE comp_plan_id = spa.comp_plan_id AND status_code = 'COMPLETE')
4432 AND EXISTS(
4433 SELECT 1
4434 FROM cn_quota_assigns a, cn_quotas b
4435 WHERE a.comp_plan_id = spa.comp_plan_id
4436 AND a.quota_id = b.quota_id
4437 AND b.incentive_type_code = 'BONUS'
4438 AND GREATEST(bat.start_date, b.start_date) <=
4439 LEAST(bat.end_date, NVL(b.end_date, bat.end_date)))
4440 --end of code added for forwardport bug 6600074
4441 UNION
4442 SELECT salesrep_id
4443 FROM cn_srp_intel_periods_all sip
4444 WHERE period_id BETWEEN l_start_period_id AND l_end_period_id
4445 AND org_id = l_org_id
4446 AND processing_status_code <> 'CLEAN'
4447 AND NOT EXISTS(
4448 SELECT 1
4449 FROM cn_srp_plan_assigns_all
4450 WHERE salesrep_id = sip.salesrep_id
4451 AND org_id = sip.org_id
4452 AND start_date <= sip.end_date
4453 AND NVL(end_date, sip.start_date) >= sip.start_date)
4454 AND EXISTS(
4455 SELECT 1
4456 FROM cn_commission_headers_all h
4457 WHERE h.direct_salesrep_id = sip.salesrep_id
4458 AND h.org_id = sip.org_id
4459 AND h.processed_date BETWEEN sip.start_date AND sip.end_date
4460 AND h.trx_type = 'BONUS');
4461
4462 CURSOR l_user_reps_csr IS
4463 SELECT cse.salesrep_id
4464 , NVL(cse.hierarchy_flag, 'N') hierarchy_flag
4465 FROM cn_calc_submission_entries_all cse
4466 WHERE cse.calc_sub_batch_id = p_calc_sub_batch_id
4467 AND (
4468 EXISTS(
4469 SELECT 1
4470 FROM cn_srp_plan_assigns_all spa
4471 , cn_calc_submission_batches_all bat
4472 ,
4473 --code added for forwardport bug 6600074
4474 cn_comp_plans PLAN
4475 , cn_quota_assigns a
4476 , cn_quotas b
4477 --end of code added for forwardport bug 6600074
4478 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4479 AND spa.salesrep_id = cse.salesrep_id
4480 AND spa.org_id = bat.org_id
4481 AND spa.start_date <= bat.end_date
4482 AND (spa.end_date IS NULL OR spa.end_date >= bat.start_date)
4483 --code added for forwardport bug 6600074
4484 AND spa.comp_plan_id = PLAN.comp_plan_id
4485 AND PLAN.status_code = 'COMPLETE'
4486 AND a.comp_plan_id = spa.comp_plan_id
4487 AND a.quota_id = b.quota_id
4488 AND b.incentive_type_code = 'BONUS'
4489 AND GREATEST(bat.start_date, b.start_date) <=
4490 LEAST(bat.end_date, NVL(b.end_date, bat.end_date))
4491 --end of code added for forwardport bug 6600074
4492 )
4493 OR EXISTS(
4494 SELECT 1
4495 FROM cn_commission_headers_all h
4496 WHERE h.direct_salesrep_id = cse.salesrep_id
4497 AND h.processed_date BETWEEN l_start_date AND l_end_date
4498 AND h.org_id = cse.org_id
4499 AND h.trx_type = 'BONUS')
4500 );
4501
4502 -- cursors for selecting pay period in which to calculate bonus
4503 CURSOR l_period_int_periods_csr(p_salesrep_id NUMBER, p_interval_type_id NUMBER) IS
4504 SELECT period.period_id
4505 , period.start_date
4506 , period.end_date
4507 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4508 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4509 AND period.org_id = bat.org_id
4510 AND (period.period_set_id, period.period_type_id) = (SELECT period_set_id
4511 , period_type_id
4512 FROM cn_repositories_all
4513 WHERE org_id = bat.org_id)
4514 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4515 AND (
4516 EXISTS -- on period.end_date there is an active comp_plan
4517 (
4518 SELECT 1
4519 FROM cn_srp_plan_assigns_all spa
4520 WHERE spa.salesrep_id = p_salesrep_id
4521 AND spa.org_id = bat.org_id
4522 AND (
4523 (
4524 spa.end_date IS NOT NULL
4525 AND period.end_date BETWEEN spa.start_date AND spa.end_date
4526 )
4527 OR (period.end_date >= spa.start_date AND spa.end_date IS NULL)
4528 ))
4529 OR EXISTS -- comp_plan is active between period start and end date AND a plan element has the salesreps_enddated_flag set to "Y"
4530 (
4531 SELECT 1
4532 FROM cn_srp_plan_assigns_all spa, cn_quota_assigns_all qa, cn_quotas_all pe
4533 WHERE spa.salesrep_id = p_salesrep_id
4534 AND spa.org_id = bat.org_id
4535 AND spa.end_date >= period.start_date
4536 AND spa.end_date < period.end_date
4537 AND qa.comp_plan_id = spa.comp_plan_id
4538 AND qa.quota_id = pe.quota_id
4539 AND pe.incentive_type_code = 'BONUS'
4540 AND pe.salesreps_enddated_flag = 'Y'
4541 AND (
4542 (p_interval_type_id = -1000 AND pe.interval_type_id = -1000)
4543 OR (p_interval_type_id = -1001 AND pe.interval_type_id = -1001)
4544 OR (p_interval_type_id = -1002 AND pe.interval_type_id = -1002)
4545 OR (
4546 p_interval_type_id = -1003
4547 AND pe.interval_type_id IN(-1000, -1001, -1002)
4548 )
4549 )
4550 -- plan element is effective on spa.end_date
4551 AND (
4552 (
4553 pe.end_date IS NOT NULL
4554 AND spa.end_date BETWEEN pe.start_date AND pe.end_date
4555 )
4556 OR (spa.end_date >= pe.start_date AND pe.end_date IS NULL)
4557 )
4558 -- check if in cn_calc_sub_quotas if that exists
4559 AND (
4560 (0 = (SELECT COUNT(*)
4561 FROM cn_calc_sub_quotas
4562 WHERE calc_sub_batch_id = p_calc_sub_batch_id))
4563 OR (pe.quota_id IN(SELECT csq.quota_id
4564 FROM cn_calc_sub_quotas csq
4565 WHERE csq.calc_sub_batch_id = p_calc_sub_batch_id))
4566 ))
4567 OR EXISTS(
4568 SELECT 1
4569 FROM cn_commission_headers_all
4570 WHERE direct_salesrep_id = p_salesrep_id
4571 AND org_id = bat.org_id
4572 AND processed_date BETWEEN period.start_date AND period.end_date
4573 AND trx_type = 'BONUS')
4574 );
4575
4576 CURSOR l_quarter_int_periods_csr(l_salesrep_id NUMBER) IS
4577 SELECT MIN(period.period_id) min_period_id
4578 , MAX(period.period_id) max_period_id
4579 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4580 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4581 AND period.org_id = bat.org_id
4582 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4583 AND (period.period_set_id, period.period_type_id) =
4584 (SELECT period_set_id
4585 , period_type_id
4586 FROM cn_repositories_all
4587 WHERE org_id = bat.org_id)
4588 GROUP BY period.quarter_num;
4589
4590 CURSOR l_year_int_periods_csr(l_salesrep_id NUMBER) IS
4591 SELECT MIN(period.period_id) min_period_id
4592 , MAX(period.period_id) max_period_id
4593 FROM cn_period_statuses_all period, cn_calc_submission_batches_all bat
4594 WHERE bat.calc_sub_batch_id = p_calc_sub_batch_id
4595 AND period.org_id = bat.org_id
4596 AND period.end_date BETWEEN bat.start_date AND bat.end_date
4597 AND (period.period_set_id, period.period_type_id) =
4598 (SELECT period_set_id
4599 , period_type_id
4600 FROM cn_repositories_all
4601 WHERE org_id = bat.org_id)
4602 GROUP BY period.period_year;
4603
4604 CURSOR l_period_info_csr(l_period_id NUMBER) IS
4605 SELECT period.period_id
4606 , period.start_date
4607 , period.end_date
4608 , period.period_set_id
4609 , period.period_type_id
4610 , period.period_year
4611 , period.quarter_num
4612 FROM cn_period_statuses_all period
4613 WHERE period.period_id = l_period_id AND org_id = g_org_id;
4614
4615 l_prd l_period_info_csr%ROWTYPE;
4616 l_end_prd l_period_info_csr%ROWTYPE;
4617 l_start_prd l_period_info_csr%ROWTYPE;
4618
4619 CURSOR l_sub_batch_csr IS
4620 SELECT NVL(hierarchy_flag, 'N')
4621 , salesrep_option
4622 , interval_type_id
4623 , start_date
4624 , end_date
4625 , org_id
4626 FROM cn_calc_submission_batches_all
4627 WHERE calc_sub_batch_id = p_calc_sub_batch_id;
4628 BEGIN
4629 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4630 fnd_log.STRING(
4631 fnd_log.level_procedure
4632 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.begin'
4633 , 'Beginning of poulate_bonus_process_batch ...'
4634 );
4635 END IF;
4636
4637 l_counter := 0;
4638
4639 OPEN l_sub_batch_csr;
4640 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;
4641 CLOSE l_sub_batch_csr;
4642
4643 SELECT period_id
4644 INTO l_start_period_id
4645 FROM cn_period_statuses_all
4646 WHERE l_start_date BETWEEN start_date AND end_date
4647 AND org_id = l_org_id
4648 AND (period_set_id, period_type_id) = (SELECT period_set_id
4649 , period_type_id
4650 FROM cn_repositories_all
4651 WHERE org_id = l_org_id);
4652
4653 SELECT period_id
4654 INTO l_end_period_id
4655 FROM cn_period_statuses_all
4656 WHERE l_end_date BETWEEN start_date AND end_date
4657 AND org_id = l_org_id
4658 AND (period_set_id, period_type_id) = (SELECT period_set_id
4659 , period_type_id
4660 FROM cn_repositories_all
4661 WHERE org_id = l_org_id);
4662
4663 IF l_salesrep_option = 'ALL_REPS' THEN
4664 IF l_interval_type_id = -1000 OR l_interval_type_id = -1003 THEN
4665 FOR l_srp IN l_all_reps_csr LOOP
4666 FOR l_period IN l_period_int_periods_csr(l_srp.salesrep_id, l_interval_type_id) LOOP
4667 l_counter := 1;
4668
4669 OPEN l_period_info_csr(l_period.period_id);
4670 FETCH l_period_info_csr INTO l_prd;
4671 CLOSE l_period_info_csr;
4672
4673 populate_calcsub_batches(
4674 l_srp.salesrep_id
4675 , l_prd.start_date
4676 , l_prd.end_date
4677 , l_prd.period_id
4678 , l_prd.period_id
4679 , g_logical_batch_id
4680 , l_hierarchy_flag
4681 );
4682 END LOOP;
4683 END LOOP;
4684 ELSIF l_interval_type_id = -1001 THEN
4685 FOR l_srp IN l_all_reps_csr LOOP
4686 FOR l_period IN l_quarter_int_periods_csr(l_srp.salesrep_id) LOOP
4687 -- then check if the period is at the end_of_interval (quarter)
4688 -- and there is active plan on the period.end_date
4689 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4690 -- get the min start date and the max end date for period submitted
4691 OPEN l_period_info_csr(l_period.min_period_id);
4692 FETCH l_period_info_csr INTO l_start_prd;
4693 CLOSE l_period_info_csr;
4694
4695 OPEN l_period_info_csr(l_period.max_period_id);
4696 FETCH l_period_info_csr INTO l_end_prd;
4697 CLOSE l_period_info_csr;
4698
4699 IF check_active_plan_assign(
4700 l_srp.salesrep_id
4701 , l_start_prd.start_date
4702 , l_end_prd.end_date
4703 , l_interval_type_id
4704 , p_calc_sub_batch_id
4705 , g_org_id
4706 ) THEN
4707 l_counter := 1;
4708 populate_calcsub_batches(
4709 l_srp.salesrep_id
4710 , l_start_prd.start_date
4711 , l_end_prd.end_date
4712 , l_end_prd.period_id
4713 , l_end_prd.period_id
4714 , g_logical_batch_id
4715 , l_hierarchy_flag
4716 );
4717 END IF;
4718 END IF;
4719 END LOOP;
4720 END LOOP;
4721 ELSIF l_interval_type_id = -1002 THEN
4722 FOR l_srp IN l_all_reps_csr LOOP
4723 FOR l_period IN l_year_int_periods_csr(l_srp.salesrep_id) LOOP
4724 --then check if the period is at the end_of_interval (quarter)
4725 -- and there is active plan on the period.end_date
4726 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4727 -- get the min start date and the max end date for period submitted
4728 OPEN l_period_info_csr(l_period.min_period_id);
4729 FETCH l_period_info_csr INTO l_start_prd;
4730 CLOSE l_period_info_csr;
4731
4732 OPEN l_period_info_csr(l_period.max_period_id);
4733 FETCH l_period_info_csr INTO l_end_prd;
4734 CLOSE l_period_info_csr;
4735
4736 IF check_active_plan_assign(
4737 l_srp.salesrep_id
4738 , l_start_prd.start_date
4739 , l_end_prd.end_date
4740 , l_interval_type_id
4741 , p_calc_sub_batch_id
4742 , g_org_id
4743 ) THEN
4744 l_counter := 1;
4745 populate_calcsub_batches(
4746 l_srp.salesrep_id
4747 , l_start_prd.start_date
4748 , l_end_prd.end_date
4749 , l_end_prd.period_id
4750 , l_end_prd.period_id
4751 , g_logical_batch_id
4752 , l_hierarchy_flag
4753 );
4754 END IF;
4755 END IF;
4756 END LOOP;
4757 END LOOP;
4758 END IF;
4759 ELSIF l_salesrep_option = 'USER_SPECIFY' THEN
4760 IF l_interval_type_id = -1000 OR l_interval_type_id = -1003 THEN
4761 FOR l_srp IN l_user_reps_csr LOOP
4762 FOR l_period IN l_period_int_periods_csr(l_srp.salesrep_id, l_interval_type_id) LOOP
4763 l_counter := 1;
4764
4765 OPEN l_period_info_csr(l_period.period_id);
4766 FETCH l_period_info_csr INTO l_prd;
4767 CLOSE l_period_info_csr;
4768
4769 populate_calcsub_batches(
4770 l_srp.salesrep_id
4771 , l_prd.start_date
4772 , l_prd.end_date
4773 , l_prd.period_id
4774 , l_prd.period_id
4775 , g_logical_batch_id
4776 , l_srp.hierarchy_flag
4777 );
4778 END LOOP;
4779 END LOOP;
4780 ELSIF l_interval_type_id = -1001 THEN
4781 FOR l_srp IN l_user_reps_csr LOOP
4782 FOR l_period IN l_quarter_int_periods_csr(l_srp.salesrep_id) LOOP
4783 --then check if the period is at the end_of_interval (quarter)
4784 -- and there is active plan on the period.end_date
4785 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4786 -- get the min start date and the max end date for period submitted
4787 OPEN l_period_info_csr(l_period.min_period_id);
4788 FETCH l_period_info_csr INTO l_start_prd;
4789 CLOSE l_period_info_csr;
4790
4791 OPEN l_period_info_csr(l_period.max_period_id);
4792 FETCH l_period_info_csr INTO l_end_prd;
4793 CLOSE l_period_info_csr;
4794
4795 IF check_active_plan_assign(
4796 l_srp.salesrep_id
4797 , l_start_prd.start_date
4798 , l_end_prd.end_date
4799 , l_interval_type_id
4800 , p_calc_sub_batch_id
4801 , g_org_id
4802 ) THEN
4803 l_counter := 1;
4804 populate_calcsub_batches(
4805 l_srp.salesrep_id
4806 , l_start_prd.start_date
4807 , l_end_prd.end_date
4808 , l_end_prd.period_id
4809 , l_end_prd.period_id
4810 , g_logical_batch_id
4811 , l_srp.hierarchy_flag
4812 );
4813 END IF;
4814 END IF;
4815 END LOOP;
4816 END LOOP;
4817 ELSIF l_interval_type_id = -1002 THEN
4818 FOR l_srp IN l_user_reps_csr LOOP
4819 FOR l_period IN l_year_int_periods_csr(l_srp.salesrep_id) LOOP
4820 --then check if the period is at the end_of_interval (quarter)
4821 -- and there is active plan on the period.end_date
4822 IF check_end_of_interval(l_period.max_period_id, l_interval_type_id, g_org_id) THEN
4823 -- get the min start date and the max end date for period submitted
4824 OPEN l_period_info_csr(l_period.min_period_id);
4825
4826 FETCH l_period_info_csr
4827 INTO l_start_prd;
4828
4829 CLOSE l_period_info_csr;
4830
4831 OPEN l_period_info_csr(l_period.max_period_id);
4832
4833 FETCH l_period_info_csr
4834 INTO l_end_prd;
4835
4836 CLOSE l_period_info_csr;
4837
4838 IF check_active_plan_assign(
4839 l_srp.salesrep_id
4840 , l_start_prd.start_date
4841 , l_end_prd.end_date
4842 , l_interval_type_id
4843 , p_calc_sub_batch_id
4844 , g_org_id
4845 ) THEN
4846 l_counter := 1;
4847 populate_calcsub_batches(
4848 l_srp.salesrep_id
4849 , l_start_prd.start_date
4850 , l_end_prd.end_date
4851 , l_end_prd.period_id
4852 , l_end_prd.period_id
4853 , g_logical_batch_id
4854 , l_srp.hierarchy_flag
4855 );
4856 END IF;
4857 END IF;
4858 END LOOP;
4859 END LOOP;
4860 END IF;
4861 END IF;
4862
4863 IF l_counter = 0 THEN /* no one to be calculated */
4864 fnd_message.set_name('CN', 'CNSBCS_NO_ONE_TO_BONUS');
4865
4866 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
4867 fnd_log.MESSAGE(
4868 fnd_log.level_exception
4869 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.error'
4870 , TRUE
4871 );
4872 END IF;
4873
4874 RAISE no_comm_lines;
4875 END IF;
4876
4877 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4878 fnd_log.STRING(
4879 fnd_log.level_procedure
4880 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.end'
4881 , 'Beginning of poulate_bonus_process_batch ...'
4882 );
4883 END IF;
4884 EXCEPTION
4885 WHEN no_comm_lines THEN
4886 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_bonus_process_batch:');
4887 cn_message_pkg.rollback_errormsg_commit
4888 (
4889 'No one with complete compensation plan to calculate or the period specified is not at the end of the plan element interval'
4890 );
4891 fnd_file.put_line
4892 (
4893 fnd_file.LOG
4894 , 'Exception in cn_proc_batches_pkg.populate_bonus_process_batch: no one with complete '
4895 || 'compensation plan or the period specified is not at the end of the interval'
4896 );
4897 RAISE;
4898 WHEN OTHERS THEN
4899 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
4900 fnd_log.STRING(
4901 fnd_log.level_unexpected
4902 , 'cn.plsql.cn_proc_batches_pkg.populate_bonus_process_batch.exception'
4903 , SQLERRM
4904 );
4905 END IF;
4906
4907 cn_message_pkg.DEBUG('Exception occurs in cn_proc_batches_pkg.populate_bonus_process_batch: ');
4908 cn_message_pkg.rollback_errormsg_commit(SQLERRM);
4909 fnd_file.put_line(fnd_file.LOG
4910 , 'Exception in cn_proc_batches_pkg.populate_bonus_process_batch: ' || SQLERRM);
4911 RAISE;
4912 END populate_bonus_process_batch;
4913 END cn_proc_batches_pkg;