[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_CREDITS_BATCH_PUB
Source
1 PACKAGE BODY cn_sca_credits_batch_pub AS
2 -- $Header: cnpscabb.pls 120.5.12010000.8 2008/12/19 06:03:15 gmarwah ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8 --
9 -- Package Name
10 -- CN_SCA_CREDITS_BATCH_PUB
11 -- Purpose
12 -- Package Body to process the Sales Credit Allocations
13 -- Add the flow diagram here.
14 -- History
15 -- 06/26/03 Rao.Chenna Created
16 -- Nov 17, 2005 vensrini Added org_id to insert into
17 -- CN_SCA_PROCESS_BATCHES stmt
18 --
19 -- Added fnd_request.set_org_id
20 -- call in conc_submit proc
21 --
22 g_pkg_name CONSTANT VARCHAR2(30) := 'CN_SCA_CREDITS_BATCH_PUB';
23 g_file_name CONSTANT VARCHAR2(12) := 'cnpscabb.pls';
24 no_trx EXCEPTION;
25 conc_fail EXCEPTION;
26 api_call_failed EXCEPTION;
27 g_cn_debug VARCHAR2(1) := fnd_profile.VALUE('CN_DEBUG');
28 g_login_id NUMBER := fnd_global.conc_login_id;
29 g_sysdate DATE := SYSDATE;
30 g_program_id NUMBER := fnd_global.conc_program_id;
31 g_user_id NUMBER := fnd_global.user_id;
32 g_request_id NUMBER := fnd_global.conc_request_id;
33 g_fetch_limit NUMBER := 10000;
34
35 TYPE g_rowid_tbl_type IS TABLE OF ROWID;
36
37 TYPE g_comm_lines_api_id_tbl_type IS TABLE OF cn_comm_lines_api_all.comm_lines_api_id%TYPE;
38
39 TYPE g_trans_object_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.trans_object_id%TYPE;
40
41 TYPE g_terr_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.terr_id%TYPE;
42
43 TYPE g_salesrep_id_tbl_type IS TABLE OF cn_salesreps.salesrep_id%TYPE;
44
45 TYPE g_emp_no_tbl_type IS TABLE OF cn_salesreps.employee_number%TYPE;
46
47 TYPE g_role_id_tbl_type IS TABLE OF jtf_tae_1001_sc_winners.role_id%TYPE;
48
49 TYPE g_split_pctg_tbl_type IS TABLE OF jtf_terr_rsc_all.attribute1%TYPE;
50
51 TYPE g_rev_type_tbl_type IS TABLE OF jtf_terr_rsc_all.attribute1%TYPE;
52
53 TYPE g_terr_name_tbl_type IS TABLE OF jtf_terr_all.NAME%TYPE;
54
55 TYPE g_del_flag_tbl_type IS TABLE OF VARCHAR2(1);
56
57 g_unloaded_txn_tbl g_rowid_tbl_type;
58 g_loaded_txn_rowid_tbl g_rowid_tbl_type;
59 g_loaded_txn_comid_tbl g_comm_lines_api_id_tbl_type;
60
61 --
62 PROCEDURE debugmsg(msg VARCHAR2) IS
63 BEGIN
64 IF g_cn_debug = 'Y' THEN
65 cn_message_pkg.DEBUG(SUBSTR(msg, 1, 254));
66 fnd_file.put_line(fnd_file.LOG, msg);
67 END IF;
68 END debugmsg;
69
70 --
71 PROCEDURE conc_submit(
72 x_conc_program IN VARCHAR2
73 , x_parent_proc_audit_id IN NUMBER
74 , x_physical_batch_id IN NUMBER
75 , x_start_date IN DATE
76 , x_end_date IN DATE
77 , p_transaction_source IN VARCHAR2
78 , p_org_id IN NUMBER
79 , x_request_id IN OUT NOCOPY NUMBER
80 ) IS
81 BEGIN
82 debugmsg('Conc_Submit : p_transaction_source = ' || p_transaction_source);
83 debugmsg('Conc_Submit : x_start_date = ' || x_start_date);
84 debugmsg('Conc_Submit : x_end_date = ' || x_end_date);
85 debugmsg('Conc_Submit : x_physical_batch_id = ' || x_physical_batch_id);
86 fnd_request.set_org_id(p_org_id); -- vensrini Nov 17, 2005
87 x_request_id :=
88 fnd_request.submit_request(
89 application => 'CN'
90 , program => x_conc_program
91 , argument1 => x_parent_proc_audit_id
92 , argument2 => x_physical_batch_id
93 , argument3 => p_transaction_source
94 , argument4 => x_start_date
95 , argument5 => x_end_date
96 , argument6 => p_org_id
97 );
98 debugmsg('Conc_Submit : x_request_id = ' || x_request_id);
99
100 IF x_request_id = 0 THEN
101 debugmsg('Loader : Conc_Submit : Submit failure for phys batch ' || x_physical_batch_id);
102 debugmsg('Loader : Conc_Submit: ' || fnd_message.get);
103 debugmsg('Loader : Conc_Submit : Submit failure for phys batch ' || x_physical_batch_id);
104 ELSE
105 cn_message_pkg.FLUSH;
106 COMMIT; -- Commit for each concurrent program i.e. runner
107 END IF;
108
109 debugmsg('Conc_Submit : End Procedure');
110 END conc_submit;
111
112 --
113 PROCEDURE conc_dispatch(
114 x_parent_proc_audit_id IN NUMBER
115 , x_start_date IN DATE
116 , x_end_date IN DATE
117 , x_logical_batch_id IN NUMBER
118 , x_transaction_source IN VARCHAR2
119 , p_org_id IN NUMBER
120 ) IS
121 TYPE requests IS TABLE OF NUMBER(15)
122 INDEX BY BINARY_INTEGER;
123
124 TYPE batches IS TABLE OF NUMBER(15)
125 INDEX BY BINARY_INTEGER;
126
127 l_primary_request_stack requests;
128 l_primary_batch_stack batches;
129 l_empty_request_stack requests;
130 l_empty_batch_stack batches;
131 x_batch_total NUMBER := 0;
132 l_temp_id NUMBER := 0;
133 l_temp_phys_batch_id NUMBER;
134 primary_ptr NUMBER := 1; -- Must start at 1
135 l_dev_phase VARCHAR2(80);
136 l_dev_status VARCHAR2(80);
137 l_request_id NUMBER;
138 l_completed_batch_count NUMBER := 0;
139 l_call_status BOOLEAN;
140 l_next_process VARCHAR2(30);
141 l_dummy VARCHAR2(500);
142 unfinished BOOLEAN := TRUE;
143 l_user_id NUMBER(15) := fnd_global.user_id;
144 l_resp_id NUMBER(15) := fnd_global.resp_id;
145 l_login_id NUMBER(15) := fnd_global.login_id;
146 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
147 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
148 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
149 x_debug NUMBER;
150 debug_v NUMBER;
151 conc_status BOOLEAN;
152 l_sleep_time NUMBER := 180;
153 l_sleep_time_char VARCHAR2(30);
154 l_errbuf VARCHAR2(1000);
155 l_retcode NUMBER;
156
157 -- Get individual physical batch id's for the entire logical batch
158 CURSOR physical_batches IS
159 SELECT DISTINCT sca_process_batch_id
160 FROM cn_sca_process_batches
161 WHERE logical_batch_id = x_logical_batch_id;
162
163 physical_rec physical_batches%ROWTYPE;
164 BEGIN
165 debugmsg('SCA : Conc_Dispatch : Start of Conc_Dispatch');
166 debugmsg('SCA : Conc_Dispatch : Logical Batch ID = ' || x_logical_batch_id);
167
168 WHILE unfinished LOOP
169 l_primary_request_stack := l_empty_request_stack;
170 l_primary_batch_stack := l_empty_batch_stack;
171 primary_ptr := 1; -- Start at element one not element zero
172 l_completed_batch_count := 0;
173 x_batch_total := 0;
174
175 FOR physical_rec IN physical_batches LOOP
176 debugmsg(
177 'Conc_Dispatch : Calling conc_submit. '
178 || 'physical_rec.sca_process_batch_id = '
179 || physical_rec.sca_process_batch_id
180 );
181 debugmsg('SCA : Conc_Dispatch : call SCA_BATCH_RUNNER');
182 cn_sca_credits_batch_pub.conc_submit
183 (
184 x_conc_program => 'CN_SCA_PROCESS_BATCH_RULES'
185 , x_parent_proc_audit_id => x_parent_proc_audit_id
186 , x_physical_batch_id => physical_rec.sca_process_batch_id
187 , x_start_date => x_start_date
188 , x_end_date => x_end_date
189 , p_transaction_source => x_transaction_source
190 , p_org_id => p_org_id
191 , x_request_id => l_temp_id
192 );
193 debugmsg('SCA : Conc_Dispatch : done SCA_BATCH_RUNNER');
194 x_batch_total := x_batch_total + 1;
195 l_primary_request_stack(x_batch_total) := l_temp_id;
196 l_primary_batch_stack(x_batch_total) := physical_rec.sca_process_batch_id;
197
198 -- If submission failed update the batch record and bail
199 IF l_temp_id = 0 THEN
200 --cn_debug.print_msg('conc disp submit failed',1);
201 l_temp_phys_batch_id := physical_rec.sca_process_batch_id;
202 RAISE conc_fail;
203 END IF;
204 END LOOP;
205
206 debugmsg('SCA : Conc_Dispatch : Total conc requests submitted : ' || x_batch_total);
207 debugmsg('Total conc requests submitted : ' || x_batch_total);
208 --cn_message_pkg.flush;
209 debug_v := l_primary_request_stack(primary_ptr);
210 l_sleep_time_char := fnd_profile.VALUE('CN_SLEEP_TIME');
211
212 IF l_sleep_time_char IS NOT NULL THEN
213 l_sleep_time := TO_NUMBER(l_sleep_time_char);
214 END IF;
215
216 DBMS_LOCK.sleep(l_sleep_time);
217
218 WHILE l_completed_batch_count <= x_batch_total LOOP
219 IF l_primary_request_stack(primary_ptr) IS NOT NULL THEN
220 l_call_status :=
221 fnd_concurrent.get_request_status(
222 request_id => l_primary_request_stack(primary_ptr)
223 , phase => l_dummy
224 , status => l_dummy
225 , dev_phase => l_dev_phase
226 , dev_status => l_dev_status
227 , MESSAGE => l_dummy
228 );
229
230 IF (NOT l_call_status) THEN
231 debugmsg('SCA : Conc_Dispatch : request_id is ' || l_primary_request_stack(primary_ptr));
232 RAISE conc_fail;
233 END IF;
234
235 IF (l_dev_phase = 'COMPLETE') THEN
236 debug_v := l_primary_request_stack(primary_ptr);
237 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
238 l_primary_batch_stack(primary_ptr) := NULL;
239 l_primary_request_stack(primary_ptr) := NULL;
240 l_completed_batch_count := l_completed_batch_count + 1;
241
242 IF (l_dev_status = 'ERROR') THEN
243 debugmsg('SCA : Conc_Dispatch : ' || 'Request completed with error for ' || debug_v);
244 RAISE conc_fail;
245 ELSIF l_dev_status = 'NORMAL' THEN
246 x_debug := l_primary_batch_stack(primary_ptr);
247 END IF; -- If error
248 END IF; -- If complete
249 END IF; -- If null ptr
250
251 primary_ptr := primary_ptr + 1;
252
253 IF (l_completed_batch_count = x_batch_total) THEN
254 debugmsg(
255 'SCA : Conc_Dispatch : All requests complete for physical '
256 || 'transaction_source : '
257 || x_transaction_source
258 );
259 -- Get out of the loop by adding 1
260 l_completed_batch_count := l_completed_batch_count + 1;
261 debugmsg(
262 'SCA : Conc_Dispatch : All requests complete for '
263 || 'logical process : '
264 || x_transaction_source
265 );
266 unfinished := FALSE;
267 ELSE
268 -- Made a complete pass through the srp_periods in this physical
269 -- batch and some conc requests have not completed.
270 -- Give the conc requests a few minutes to run before
271 -- checking their status
272 IF (primary_ptr > x_batch_total) THEN
273 DBMS_LOCK.sleep(l_sleep_time);
274 primary_ptr := 1;
275 END IF;
276 END IF;
277 END LOOP;
278 END LOOP;
279 EXCEPTION
280 WHEN NO_DATA_FOUND THEN
281 debugmsg('SCA : Conc_Dispatch : no rows for process ' || x_transaction_source);
282 WHEN conc_fail THEN
283 debugmsg('SCA : Conc_Dispatch : Exception conc_fail');
284 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
285 RAISE;
286 WHEN OTHERS THEN
287 debugmsg('SCA : Conc_Dispatch : Unexpected Exception');
288 RAISE;
289 END conc_dispatch;
290
291 --
292 PROCEDURE split_batches(
293 p_logical_batch_id IN NUMBER
294 , p_start_date IN DATE
295 , p_end_date IN DATE
296 , p_transaction_source IN VARCHAR2
297 , p_org_id IN NUMBER
298 , x_size OUT NOCOPY NUMBER
299 ) IS
300 l_sql_stmt VARCHAR2(10000);
301 l_sql_stmt_count VARCHAR2(10000);
302 l_sql_stmt_id VARCHAR2(10000);
303 l_sql_stmt_divider VARCHAR2(10000);
304 l_sql_stmt_resource VARCHAR2(10000);
305 l_no_trx BOOLEAN;
306 l_sca_process_batch_id cn_sca_process_batches.sca_process_batch_id%TYPE;
307
308 TYPE rc IS REF CURSOR;
309
310 TYPE divider_type IS TABLE OF NUMBER;
311
312 query_cur rc;
313 i NUMBER;
314 l_header_rec cn_comm_lines_api%ROWTYPE;
315 l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
316 l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
317 l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
318 l_source_id cn_sca_headers_interface.source_id%TYPE;
319 l_order_number cn_comm_lines_api.order_number%TYPE;
320 l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
321 l_id NUMBER;
322 l_logical_batch_size NUMBER;
323 l_worker_num NUMBER;
324 l_physical_batch_size NUMBER;
325 l_divider_size NUMBER;
326 divider divider_type := divider_type
327 ();
328 loop_count NUMBER;
329 l_start_id cn_sca_process_batches.start_id%TYPE;
330 l_end_id cn_sca_process_batches.end_id%TYPE;
331 l_user_id NUMBER(15) := fnd_global.user_id;
332 l_login_id NUMBER(15) := fnd_global.login_id;
333 BEGIN
334 debugmsg('Allocation Process : Split Batches Start ');
335 debugmsg('Allocation Process : p_start_date = ' || p_start_date);
336 debugmsg('Allocation Process : p_end_date = ' || p_end_date);
337 -- Get the number of transactions that needs to be processed,
338 -- i.e. the logical batch size
339 l_sql_stmt_count := 'SELECT count(1) FROM cn_sca_headers_interface cshi ';
340 l_sql_stmt :=
341 'WHERE cshi.processed_date BETWEEN :p_start_date AND :p_end_date '
342 || 'AND cshi.transaction_source = :p_transaction_source '
343 || 'AND cshi.process_status = ''SCA_UNPROCESSED'' '
344 || 'AND cshi.org_id = :p_org_id '
345 || 'ORDER BY cshi.sca_headers_interface_id ';
346 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
347
348 OPEN query_cur
349 FOR l_sql_stmt_count USING p_start_date, p_end_date, p_transaction_source, p_org_id;
350
351 FETCH query_cur
352 INTO l_logical_batch_size;
353
354 x_size := l_logical_batch_size;
355 l_worker_num := NVL(fnd_profile.VALUE('CN_NUMBER_OF_WORKERS'), 1);
356
357 IF (l_worker_num < 1) THEN
358 l_worker_num := 1;
359 END IF;
360
361 debugmsg(p_transaction_source || ': Assign : Logical Batch Size = '
362 || TO_CHAR(l_logical_batch_size));
363 debugmsg(p_transaction_source || ': Assign : Number of Workers = ' || TO_CHAR(l_worker_num));
364
365 -- calculate the minimas and maximas of the physical batches
366 IF (l_logical_batch_size > l_worker_num) THEN
367 l_physical_batch_size := FLOOR(l_logical_batch_size / l_worker_num);
368 l_divider_size := l_worker_num * 2;
369 divider.EXTEND;
370 divider(1) := 1;
371 divider.EXTEND;
372 divider(2) := divider(1) + l_physical_batch_size - 1;
373
374 FOR counter IN 2 .. l_worker_num LOOP
375 divider.EXTEND;
376 divider(2 * counter - 1) := divider(2 * counter - 2) + 1;
377 divider.EXTEND;
378 divider(2 * counter) := divider(2 * counter - 1) + l_physical_batch_size - 1;
379
380 IF (counter <> l_worker_num) THEN
381 debugmsg(
382 p_transaction_source
383 || ': Assign : Maxima'
384 || counter
385 || ' = '
386 || TO_CHAR(divider(2 * counter))
387 );
388 END IF;
389 END LOOP;
390
391 divider(l_divider_size) := l_logical_batch_size;
392 ELSE
393 l_physical_batch_size := 0;
394
395 FOR counter IN 1 .. l_logical_batch_size LOOP
396 divider.EXTEND;
397 divider(2 * counter - 1) := counter;
398 divider.EXTEND;
399 divider(2 * counter) := counter;
400 END LOOP;
401 END IF;
402
403 --
404 IF (divider.COUNT = 0) THEN
405 l_no_trx := TRUE;
406 RAISE no_trx;
407 ELSE
408 l_no_trx := FALSE;
409 l_sql_stmt_divider := '(''' || divider(divider.FIRST) || '''';
410 i := divider.NEXT(divider.FIRST);
411
412 WHILE i IS NOT NULL LOOP
413 l_sql_stmt_divider := l_sql_stmt_divider || ', ''' || divider(i) || '''';
414 i := divider.NEXT(i);
415 END LOOP;
416
417 l_sql_stmt_divider := l_sql_stmt_divider || ')';
418 END IF;
419
420 IF (NOT l_no_trx) THEN
421 l_sql_stmt_id :=
422 'SELECT cshi.sca_headers_interface_id ' || 'FROM cn_sca_headers_interface CSHI ';
423 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
424 l_sql_stmt_id :=
425 'SELECT sca_headers_interface_id FROM '
426 || '(SELECT rownum row_number, sca_headers_interface_id FROM '
427 || '('
428 || l_sql_stmt_id
429 || ')) sca_headers_table '
430 || 'WHERE sca_headers_table.row_number IN '
431 || l_sql_stmt_divider;
432
433 OPEN query_cur
434 FOR l_sql_stmt_id USING p_start_date, p_end_date, p_transaction_source, p_org_id;
435
436 loop_count := 1;
437 debugmsg(p_transaction_source || ': Assign : Insert into CN_SCA_PROCESS_BATCHES ');
438
439 IF (l_physical_batch_size >= 2) THEN
440 LOOP
441 FETCH query_cur
442 INTO l_id;
443
444 EXIT WHEN query_cur%NOTFOUND;
445
446 IF ((loop_count MOD 2) = 1) THEN
447 l_start_id := l_id;
448 END IF;
449
450 IF ((loop_count MOD 2) = 0) THEN
451 l_end_id := l_id;
452
453 SELECT cn_sca_process_batches_s.NEXTVAL
454 INTO l_sca_process_batch_id
455 FROM SYS.DUAL;
456
457 INSERT INTO cn_sca_process_batches
458 (
459 sca_process_batch_id
460 , start_id
461 , end_id
462 , TYPE
463 , logical_batch_id
464 , creation_date
465 , created_by
466 , last_update_date
467 , last_updated_by
468 , last_update_login
469 , org_id
470 )
471 VALUES (
472 l_sca_process_batch_id
473 , l_start_id
474 , l_end_id
475 , p_transaction_source
476 , p_logical_batch_id
477 , SYSDATE
478 , l_user_id
479 , SYSDATE
480 , l_user_id
481 , l_login_id
482 , p_org_id
483 );
484
485 debugmsg(
486 p_transaction_source
487 || ': Assign : sca_process_batch_id = '
488 || TO_CHAR(l_sca_process_batch_id)
489 );
490 debugmsg(p_transaction_source || ': Assign : start_id = ' || l_start_id);
491 debugmsg(p_transaction_source || ': Assign : end_id = ' || l_end_id);
492 debugmsg(
493 p_transaction_source || ': Assign : logical_batch_id = '
494 || TO_CHAR(p_logical_batch_id)
495 );
496 debugmsg(p_transaction_source || ': Assign : batch_type = ' || p_transaction_source);
497 END IF;
498
499 loop_count := loop_count + 1;
500 END LOOP;
501 ELSE
502 LOOP
503 FETCH query_cur
504 INTO l_id;
505
506 EXIT WHEN query_cur%NOTFOUND;
507
508 IF (loop_count = l_worker_num AND l_physical_batch_size = 1) THEN
509 l_start_id := l_id;
510 END IF;
511
512 IF (loop_count > l_worker_num AND l_physical_batch_size = 1) THEN
513 l_end_id := l_id;
514
515 SELECT cn_sca_process_batches_s.NEXTVAL
516 INTO l_sca_process_batch_id
517 FROM SYS.DUAL;
518
519 INSERT INTO cn_sca_process_batches
520 (
521 sca_process_batch_id
522 , start_id
523 , end_id
524 , TYPE
525 , logical_batch_id
526 , creation_date
527 , created_by
528 , last_update_date
529 , last_updated_by
530 , last_update_login
531 , org_id
532 )
533 VALUES (
534 l_sca_process_batch_id
535 , l_start_id
536 , l_end_id
537 , p_transaction_source
538 , p_logical_batch_id
539 , SYSDATE
540 , l_user_id
541 , SYSDATE
542 , l_user_id
543 , l_login_id
544 , p_org_id
545 );
546
547 debugmsg(
548 p_transaction_source
549 || ': Assign : sca_process_batch_id = '
550 || TO_CHAR(l_sca_process_batch_id)
551 );
552 debugmsg(p_transaction_source || ': Assign : start_id = ' || l_start_id);
553 debugmsg(p_transaction_source || ': Assign : end_id = ' || l_end_id);
554 debugmsg(
555 p_transaction_source || ': Assign : logical_batch_id = '
556 || TO_CHAR(p_logical_batch_id)
557 );
558 debugmsg(p_transaction_source || ': Assign : batch_type = ' || p_transaction_source);
559 END IF;
560
561 IF (
562 loop_count < l_worker_num OR(loop_count = l_worker_num AND l_physical_batch_size < 1)
563 ) THEN
564 SELECT cn_sca_process_batches_s.NEXTVAL
565 INTO l_sca_process_batch_id
566 FROM SYS.DUAL;
567
568 INSERT INTO cn_sca_process_batches
569 (
570 sca_process_batch_id
571 , start_id
572 , end_id
573 , TYPE
574 , logical_batch_id
575 , creation_date
576 , created_by
577 , last_update_date
578 , last_updated_by
579 , last_update_login
580 , org_id
581 )
582 VALUES (
583 l_sca_process_batch_id
584 , l_id
585 , l_id
586 , p_transaction_source
587 , p_logical_batch_id
588 , SYSDATE
589 , l_user_id
590 , SYSDATE
591 , l_user_id
592 , l_login_id
593 , p_org_id
594 );
595
596 debugmsg(
597 p_transaction_source
598 || ': Assign : sca_process_batch_id = '
599 || TO_CHAR(l_sca_process_batch_id)
600 );
601 debugmsg(p_transaction_source || ': Assign : start_id = ' || l_id);
602 debugmsg(p_transaction_source || ': Assign : end_id = ' || l_id);
603 debugmsg(
604 p_transaction_source || ': Assign : logical_batch_id = '
605 || TO_CHAR(p_logical_batch_id)
606 );
607 debugmsg(p_transaction_source || ': Assign : batch_type = ' || p_transaction_source);
608 END IF;
609
610 loop_count := loop_count + 1;
611 END LOOP;
612 END IF;
613 END IF;
614 EXCEPTION
615 WHEN no_trx THEN
616 debugmsg(p_transaction_source || ': Assign : No transactions to process ');
617 WHEN OTHERS THEN
618 debugmsg(p_transaction_source || ': Assign : Unexpected Error');
619 RAISE;
620 END split_batches;
621
622 --
623 PROCEDURE get_sales_credits(
624 errbuf OUT NOCOPY VARCHAR2
625 , retcode OUT NOCOPY NUMBER
626 , p_transaction_source IN VARCHAR2
627 , p_start_date IN VARCHAR2
628 , p_end_date IN VARCHAR2
629 ) IS
630 --+
631 --+ Variable Declaration
632 --+
633 l_start_date DATE;
634 l_end_date DATE;
635 l_process_audit_id NUMBER;
636 l_logical_batch_id NUMBER;
637 x_size_inv NUMBER;
638 x_size_ord NUMBER;
639 x_size NUMBER;
640 conc_status BOOLEAN;
641 l_wf_item_key VARCHAR2(240);
642 l_return_status VARCHAR2(1);
643 l_rule_count NUMBER := 0;
644 l_status VARCHAR2(1);
645 l_industry VARCHAR2(1);
646 l_oracle_schema VARCHAR2(30);
647 l_return BOOLEAN;
648 p_org_id NUMBER;
649 --+
650 --+ Exceptions Declaration
651 --+
652 index_ex EXCEPTION;
653 no_rule_ex EXCEPTION;
654 BEGIN
655 p_org_id := mo_global.get_current_org_id();
656 cn_message_pkg.begin_batch(
657 x_process_type => 'ALLOCATION_PROCESS'
658 , x_parent_proc_audit_id => NULL
659 , x_process_audit_id => l_process_audit_id
660 , x_request_id => fnd_global.conc_request_id
661 , p_org_id => p_org_id
662 );
663 -- Convert the dates for the varchar2 parameters passed in from
664 -- concurrent program
665 l_start_date := fnd_date.canonical_to_date(p_start_date);
666 l_end_date := fnd_date.canonical_to_date(p_end_date);
667
668 --+
669 --+ Call begin_batch to get process_audit_id for debug log file
670 --+
671 SELECT cn_sca_logical_batches_s.NEXTVAL
672 INTO l_logical_batch_id
673 FROM SYS.DUAL;
674
675 debugmsg('Allocation Process : Start of Transfer');
676 debugmsg('Allocation Process : process_audit_id is ' || l_process_audit_id);
677 --dbms_output.put_line('Allocation Process : process_audit_id is ' || l_process_audit_id );
678 debugmsg('Allocation Process : logical_batch_id is ' || l_logical_batch_id);
679 debugmsg('Allocation Process : p_start_date is ' || p_start_date);
680 debugmsg('Allocation Process : p_end_date is ' || p_end_date);
681 debugmsg('Allocation Process : mo_global.get_current_org_id is - ' || p_org_id);
682
683 --+
684 --+ Check whether credit rules existing for a given transaction source and
685 --+ operating unit
686 --+
687 SELECT COUNT(1)
688 INTO l_rule_count
689 FROM cn_sca_denorm_rules a
690 WHERE a.transaction_source = p_transaction_source AND a.org_id = p_org_id;
691
692 IF (l_rule_count = 0) THEN
693 RAISE no_rule_ex;
694 END IF;
695
696 --+
697 --+ Call this procedure to to find out the number of records and split
698 --+ them into multiple physical batches.
699 --+
700 cn_sca_credits_batch_pub.split_batches(l_logical_batch_id, l_start_date, l_end_date
701 , p_transaction_source, p_org_id, x_size);
702 COMMIT;
703
704 IF (x_size = 0) THEN
705 RAISE no_trx;
706 END IF;
707
708 --+
709 --+ Getting the schema name and use it as a parameter in DDL statements.
710 --+ to fix bug# 3537330 (04/23/04)
711 --+
712 l_return :=
713 fnd_installation.get_app_info(
714 application_short_name => 'CN'
715 , status => l_status
716 , industry => l_industry
717 , oracle_schema => l_oracle_schema
718 );
719 debugmsg('Allocation Process : Schema Name: ' || l_oracle_schema);
720 --+
721 --+ Removing data from intermediate tables
722 --+
723 debugmsg('Allocation Process : Removing data from intermediate tables');
724
725 BEGIN
726 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema || '.cn_sca_matches_all REUSE STORAGE';
727 EXCEPTION
728 WHEN OTHERS THEN
729 debugmsg('Allocation Process : Unable to trancate cn_sca_matches' || SQLERRM);
730 RAISE;
731 END;
732
733 BEGIN
734 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_oracle_schema || '.cn_sca_winners_all REUSE STORAGE';
735 EXCEPTION
736 WHEN OTHERS THEN
737 debugmsg('Allocation Process : Unable to trancate cn_sca_winners' || SQLERRM);
738 RAISE;
739 END;
740
741 COMMIT;
742 --+
743 --+ Setting the tables in NOLOGGING mode
744 --+
745 debugmsg('Allocation Process : Set the tables to NOLOGGING Mode');
746
747 BEGIN
748 EXECUTE IMMEDIATE 'ALTER TABLE ' || l_oracle_schema || '.cn_sca_matches_all NOLOGGING';
749 EXCEPTION
750 WHEN OTHERS THEN
751 debugmsg('Allocation Process : Unable to set NOLOGGING for cn_sca_matches' || SQLERRM);
752 RAISE;
753 END;
754
755 BEGIN
756 EXECUTE IMMEDIATE 'ALTER TABLE ' || l_oracle_schema || '.cn_sca_winners_all NOLOGGING';
757 EXCEPTION
758 WHEN OTHERS THEN
759 debugmsg('Allocation Process : Unable to set NOLOGGING for cn_sca_winners' || SQLERRM);
760 RAISE;
761 END;
762
763 --+
764 --+ Delete existing indexes and create indexes on ATTRIBUTE columns of
765 --+ input interface table.
766 --+
767 cn_sca_utl_pvt.manage_indexes(
768 p_transaction_source => p_transaction_source
769 , p_org_id => p_org_id
770 , x_return_status => l_return_status
771 );
772
773 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
774 RAISE index_ex;
775 END IF;
776
777 COMMIT;
778 --+
779 --+ Once physical batches are created, this procedure assign each physical
780 --+ batch as a concurrent program
781 --+
782 cn_sca_credits_batch_pub.conc_dispatch(
783 x_parent_proc_audit_id => l_process_audit_id
784 , x_start_date => l_start_date
785 , x_end_date => l_end_date
786 , x_transaction_source => p_transaction_source
787 , p_org_id => p_org_id
788 , x_logical_batch_id => l_logical_batch_id
789 );
790 --+
791 --+ Once processing is done, call workflow process to execute the calling
792 --+ module's procedure to populate data from SCA tables to their tables.
793 --+
794 debugmsg('Allocation Process : Calling WF to execute Calling Module Procedure');
795
796 BEGIN
797 cn_sca_wf_pkg.start_process(
798 p_start_date => l_start_date
799 , p_end_date => l_end_date
800 , p_trx_source => p_transaction_source
801 , p_org_id => p_org_id
802 , p_wf_process => 'CN_SCA_TRX_LOAD_PR'
803 , p_wf_item_type => 'CNSCARPR'
804 , x_wf_item_key => l_wf_item_key
805 );
806 debugmsg('Allocation Process : Executed Calling Module Procedure');
807 EXCEPTION
808 WHEN OTHERS THEN
809 debugmsg('Allocation Process : Error while processing Calling Module Procedure');
810 END;
811
812 COMMIT WORK;
813 debugmsg('Allocation Process : Ending: get_sales_credits ');
814 cn_message_pkg.end_batch(l_process_audit_id);
815 EXCEPTION
816 WHEN no_trx THEN
817 debugmsg('Get Sales Credits : No input transactions found for Rules Engine Processing');
818 debugmsg('Get Sales Credits : Rules Engine Processing ended with errors');
819 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
820 cn_message_pkg.end_batch(l_process_audit_id);
821 WHEN no_rule_ex THEN
822 debugmsg('Get Sales Credits : No Credit Rules found for Rules Engine Processing');
823 debugmsg('Get Sales Credits : Rules Engine Processing ended with errors');
824 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
825 cn_message_pkg.end_batch(l_process_audit_id);
826 WHEN index_ex THEN
827 debugmsg('Get Sales Credits : Error occured while creating indexes dynamically');
828 debugmsg('Get Sales Credits : Rules Engine Processing ended with errors');
829 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
830 cn_message_pkg.end_batch(l_process_audit_id);
831 WHEN OTHERS THEN
832 debugmsg('Get Sales Credits : Unexpected exception');
833 debugmsg('Get Sales Credits : Oracle Error: ' || SQLERRM);
834 debugmsg('Get Sales Credits : Rules Engine Processing ended with errors');
835 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
836 cn_message_pkg.end_batch(l_process_audit_id);
837 END get_sales_credits;
838
839 --
840
841 /**************************************/
842 /* Start of the new crediting process */
843 /**************************************/
844
845 /* This procedure returns the appropiate where clause to select */
846 /* data from the table cn_comm_lines_api_all depending on run mode */
847 PROCEDURE get_where_clause(
848 p_start_date IN DATE
849 , p_end_date IN DATE
850 , p_org_id IN NUMBER
851 , p_run_mode IN VARCHAR2
852 , x_where_clause OUT NOCOPY VARCHAR2
853 , errbuf IN OUT NOCOPY VARCHAR2
854 , retcode IN OUT NOCOPY VARCHAR2
855 ) IS
856 BEGIN
857 debugmsg('SCA : Start of get_where_clause');
858 errbuf := NULL;
859 retcode := 0;
860 x_where_clause := 'WHERE org_id = ' || p_org_id || ' ';
861 x_where_clause :=
862 x_where_clause
863 || 'AND txn_date between '
864 || 'to_date('''
865 || TO_CHAR(p_start_date, 'dd/mm/yyyy')
866 || ''',''dd/mm/yyyy'')'
867 || ' and to_date('''
868 || TO_CHAR(p_end_date, 'dd/mm/yyyy')
869 || ''',''dd/mm/yyyy'')'
870 || ' ';
871 /* only the collected txns are selected and not the ones generated by this process */
872 x_where_clause := x_where_clause || 'AND terr_id IS NULL ';
873
874
875 IF (p_run_mode = 'ALL') THEN
876 x_where_clause := x_where_clause || 'AND load_status IN ( ''LOADED'',''ERROR - PRIOR ADJUSTMENT'', ''ERROR - TRX_TYPE'', ''ERROR - REVENUE_CLASS''
877 ,''ERROR - NO EXCH RATE GIVEN'', ''ERROR - INCORRECT CONV GIVEN'', ''ERROR - CANNOT CONV/DEFAULT''
878 ,''SALESREP ERROR'', ''PERIOD ERROR'', ''UNLOADED''
879 ,''CREDITED'', ''SCA_ALLOCATED'' ) ';
880
881 ELSE
882 /* loaded txns are not considered for crediting in NEW mode */
883 x_where_clause :=
884 x_where_clause || 'AND load_status IN (''ERROR - PRIOR ADJUSTMENT'', ''ERROR - TRX_TYPE'', ''ERROR - REVENUE_CLASS''
885 ,''ERROR - NO EXCH RATE GIVEN'', ''ERROR - INCORRECT CONV GIVEN'', ''ERROR - CANNOT CONV/DEFAULT''
886 ,''SALESREP ERROR'', ''PERIOD ERROR'', ''UNLOADED''
887 ,''CREDITED'', ''SCA_ALLOCATED'' ) ';
888
889
890
891
892 END IF;
893
894 x_where_clause :=
895 x_where_clause
896 || 'AND (adjust_status IS NULL OR adjust_status NOT IN (''FROZEN'', ''REVERSAL'')) ';
897 /* donot select txns for which user has checked the "preserve credit override flag" to bypass crediting process */
898 x_where_clause :=
899 x_where_clause
900 || 'AND (preserve_credit_override_flag IS NULL OR preserve_credit_override_flag <> ''Y'') ';
901
902 IF (p_run_mode = 'NEW') THEN
903 /* only the txns which are not processed previously by crediting process are selected in NEW mode */
904 x_where_clause :=
905 x_where_clause
906 || 'AND NOT EXISTS ( '
907 || ' SELECT /*+ NO_UNNEST */ 1 '
908 || ' FROM cn_comm_lines_api_all '
909 || ' WHERE adj_comm_lines_api_id = trans_object_id )';
910 END IF;
911
912 debugmsg('SCA : where clause : ' || x_where_clause);
913 debugmsg('SCA : End of get_where_clause');
914 EXCEPTION
915 WHEN OTHERS THEN
916 debugmsg('SCA : Unexpected exception in get_where_clause');
917 debugmsg('SCA : SQLCODE : ' || SQLCODE);
918 debugmsg('SCA : SQLERRM : ' || SQLERRM);
919 retcode := 2;
920 errbuf := 'CN_SCATM_TAE_PUB.get_where_clause.others';
921 END get_where_clause;
922
923 /* This procedure returns the flex field names that are used */
924 /* in TM to store the split percentage and revenue type */
925 PROCEDURE get_flex_field_names(
926 p_ffname_split_pctg OUT NOCOPY VARCHAR2
927 , p_ffname_rev_type OUT NOCOPY VARCHAR2
928 , errbuf IN OUT NOCOPY VARCHAR2
929 , retcode IN OUT NOCOPY VARCHAR2
930 ) IS
931 l_invalid_ffnames EXCEPTION;
932 BEGIN
933 debugmsg('SCA : Start of get_flex_field_names');
934 errbuf := NULL;
935 retcode := 0;
936
937 /* Get the flex field name corresponding to split percentage */
938 IF (fnd_profile.defined('CN_FFNAME_SPLIT_PCTG')) THEN
939 p_ffname_split_pctg := fnd_profile.VALUE('CN_FFNAME_SPLIT_PCTG');
940 END IF;
941
942 /* Get the flex field name corresponding to revenue type */
943 IF (fnd_profile.defined('CN_FFNAME_REV_TYPE')) THEN
944 p_ffname_rev_type := fnd_profile.VALUE('CN_FFNAME_REV_TYPE');
945 END IF;
946
947 /* the two flex field names should not be same and they should one of the fields attribute1 .. 15 */
948 IF (
949 (
950 p_ffname_split_pctg NOT IN
951 (
952 'ATTRIBUTE1'
953 , 'ATTRIBUTE2'
954 , 'ATTRIBUTE3'
955 , 'ATTRIBUTE4'
956 , 'ATTRIBUTE5'
957 , 'ATTRIBUTE6'
958 , 'ATTRIBUTE7'
959 , 'ATTRIBUTE8'
960 , 'ATTRIBUTE9'
961 , 'ATTRIBUTE10'
962 , 'ATTRIBUTE11'
963 , 'ATTRIBUTE12'
964 , 'ATTRIBUTE13'
965 , 'ATTRIBUTE14'
966 , 'ATTRIBUTE15'
967 )
968 )
969 OR (
970 p_ffname_rev_type NOT IN
971 (
972 'ATTRIBUTE1'
973 , 'ATTRIBUTE2'
974 , 'ATTRIBUTE3'
975 , 'ATTRIBUTE4'
976 , 'ATTRIBUTE5'
977 , 'ATTRIBUTE6'
978 , 'ATTRIBUTE7'
979 , 'ATTRIBUTE8'
980 , 'ATTRIBUTE9'
981 , 'ATTRIBUTE10'
982 , 'ATTRIBUTE11'
983 , 'ATTRIBUTE12'
984 , 'ATTRIBUTE13'
985 , 'ATTRIBUTE14'
986 , 'ATTRIBUTE15'
987 )
988 )
989 OR (p_ffname_split_pctg = p_ffname_rev_type)
990 ) THEN
991 RAISE l_invalid_ffnames;
992 END IF;
993
994 debugmsg('SCA : Flex field name for split pctg : ' || p_ffname_split_pctg);
995 debugmsg('SCA : Flex field name for revenue type : ' || p_ffname_rev_type);
996 debugmsg('SCA : End of get_flex_field_names');
997 EXCEPTION
998 WHEN l_invalid_ffnames THEN
999 debugmsg('SCA : Invalid flex field name specification');
1000 retcode := 2;
1001 errbuf := 'Invalid flex field name specification';
1002 WHEN OTHERS THEN
1003 debugmsg('SCA : Unexpected exception in get_flex_field_names');
1004 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1005 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1006 retcode := 2;
1007 errbuf := 'CN_SCATM_TAE_PUB.get_flex_field_names.others';
1008 END get_flex_field_names;
1009
1010 /* This procedure marks the txns processed as CREDITED */
1011 PROCEDURE update_txns_processed(errbuf IN OUT NOCOPY VARCHAR2, retcode IN OUT NOCOPY VARCHAR2,p_worker_id IN NUMBER) IS
1012 l_no_of_records NUMBER;
1013 BEGIN
1014 debugmsg('SCA : Start of update_txns_processed');
1015 errbuf := NULL;
1016 retcode := 0;
1017
1018 /* mark the transactions in the api table as CREDITED */
1019 /* for which territory manager has returned a valid credited txn */
1020 UPDATE /*+ parallel(cla) */ cn_comm_lines_api_all cla
1021 SET load_status = 'CREDITED', adjust_status = 'SCA_ALLOCATED'
1022 WHERE comm_lines_api_id IN (
1023 SELECT /*+ parallel(a) leading(a) use_nl(b) cardinality(a,1) */ trans_object_id
1024 FROM jtf_tae_1001_sc_winners a, cn_comm_lines_api_all b
1025 WHERE b.adj_comm_lines_api_id = a.trans_object_id
1026 AND b.terr_id IS NOT NULL
1027 AND a.worker_id =p_worker_id
1028 );
1029
1030 debugmsg('SCA : End of update_txns_processed');
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 debugmsg('SCA : Unexpected exception in update_txns_processed');
1034 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1035 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1036 retcode := 2;
1037 errbuf := 'CN_SCATM_TAE_PUB.update_txns_processed.others';
1038 END update_txns_processed;
1039
1040 /* This procedure inserts credited txns into api table */
1041 PROCEDURE insert_api_txns(
1042 p_org_id IN NUMBER
1043 , p_trans_object_id_tbl IN OUT NOCOPY g_trans_object_id_tbl_type
1044 , p_salesrep_id_tbl IN OUT NOCOPY g_salesrep_id_tbl_type
1045 , p_emp_no_tbl IN OUT NOCOPY g_emp_no_tbl_type
1046 , p_role_id_tbl IN OUT NOCOPY g_role_id_tbl_type
1047 , p_split_pctg_tbl IN OUT NOCOPY g_split_pctg_tbl_type
1048 , p_rev_type_tbl IN OUT NOCOPY g_rev_type_tbl_type
1049 , p_terr_id_tbl IN OUT NOCOPY g_terr_id_tbl_type
1050 , p_terr_name_tbl IN OUT NOCOPY g_terr_name_tbl_type
1051 , p_del_flag_tbl IN OUT NOCOPY g_del_flag_tbl_type
1052 , errbuf IN OUT NOCOPY VARCHAR2
1053 , retcode IN OUT NOCOPY VARCHAR2
1054 ) IS
1055 l_no_of_records NUMBER;
1056 l_error_index NUMBER;
1057 ERRORS NUMBER;
1058 dml_errors EXCEPTION;
1059 PRAGMA EXCEPTION_INIT(dml_errors, -24381);
1060 BEGIN
1061 debugmsg('SCA : Start of insert_api_txns');
1062 errbuf := NULL;
1063 retcode := 0;
1064 l_no_of_records := p_trans_object_id_tbl.COUNT;
1065 debugmsg('SCA : Number of rows to be inserted : ' || l_no_of_records);
1066
1067 IF (l_no_of_records > 0) THEN
1068 /* insert the credited transactions into api table */
1069 /* process all the rows even if some of them fail */
1070 FORALL i IN p_trans_object_id_tbl.FIRST .. p_trans_object_id_tbl.LAST SAVE EXCEPTIONS
1071 INSERT INTO cn_comm_lines_api_all
1072 (
1073 salesrep_id
1074 , processed_date
1075 , processed_period_id
1076 , transaction_amount
1077 , trx_type
1078 , revenue_class_id
1079 , load_status
1080 , attribute_category
1081 , attribute1
1082 , attribute2
1083 , attribute3
1084 , attribute4
1085 , attribute5
1086 , attribute6
1087 , attribute7
1088 , attribute8
1089 , attribute9
1090 , attribute10
1091 , attribute11
1092 , attribute12
1093 , attribute13
1094 , attribute14
1095 , attribute15
1096 , attribute16
1097 , attribute17
1098 , attribute18
1099 , attribute19
1100 , attribute20
1101 , attribute21
1102 , attribute22
1103 , attribute23
1104 , attribute24
1105 , attribute25
1106 , attribute26
1107 , attribute27
1108 , attribute28
1109 , attribute29
1110 , attribute30
1111 , attribute31
1112 , attribute32
1113 , attribute33
1114 , attribute34
1115 , attribute35
1116 , attribute36
1117 , attribute37
1118 , attribute38
1119 , attribute39
1120 , attribute40
1121 , attribute41
1122 , attribute42
1123 , attribute43
1124 , attribute44
1125 , attribute45
1126 , attribute46
1127 , attribute47
1128 , attribute48
1129 , attribute49
1130 , attribute50
1131 , attribute51
1132 , attribute52
1133 , attribute53
1134 , attribute54
1135 , attribute55
1136 , attribute56
1137 , attribute57
1138 , attribute58
1139 , attribute59
1140 , attribute60
1141 , attribute61
1142 , attribute62
1143 , attribute63
1144 , attribute64
1145 , attribute65
1146 , attribute66
1147 , attribute67
1148 , attribute68
1149 , attribute69
1150 , attribute70
1151 , attribute71
1152 , attribute72
1153 , attribute73
1154 , attribute74
1155 , attribute75
1156 , attribute76
1157 , attribute77
1158 , attribute78
1159 , attribute79
1160 , attribute80
1161 , attribute81
1162 , attribute82
1163 , attribute83
1164 , attribute84
1165 , attribute85
1166 , attribute86
1167 , attribute87
1168 , attribute88
1169 , attribute89
1170 , attribute90
1171 , attribute91
1172 , attribute92
1173 , attribute93
1174 , attribute94
1175 , attribute95
1176 , attribute96
1177 , attribute97
1178 , attribute98
1179 , attribute99
1180 , attribute100
1181 , comm_lines_api_id
1182 , conc_batch_id
1183 , process_batch_id
1184 , salesrep_number
1185 , rollup_date
1186 , source_doc_id
1187 , source_doc_type
1188 , created_by
1189 , creation_date
1190 , last_updated_by
1191 , last_update_date
1192 , last_update_login
1193 , transaction_currency_code
1194 , exchange_rate
1195 , acctd_transaction_amount
1196 , trx_id
1197 , trx_line_id
1198 , trx_sales_line_id
1199 , quantity
1200 , source_trx_number
1201 , discount_percentage
1202 , margin_percentage
1203 , source_trx_id
1204 , source_trx_line_id
1205 , source_trx_sales_line_id
1206 , negated_flag
1207 , customer_id
1208 , inventory_item_id
1209 , order_number
1210 , booked_date
1211 , invoice_number
1212 , invoice_date
1213 , adjust_date
1214 , adjusted_by
1215 , revenue_type
1216 , adjust_rollup_flag
1217 , adjust_comments
1218 , adjust_status
1219 , line_number
1220 , bill_to_address_id
1221 , ship_to_address_id
1222 , bill_to_contact_id
1223 , ship_to_contact_id
1224 , adj_comm_lines_api_id
1225 , pre_defined_rc_flag
1226 , rollup_flag
1227 , forecast_id
1228 , upside_quantity
1229 , upside_amount
1230 , uom_code
1231 , reason_code
1232 , TYPE
1233 , pre_processed_code
1234 , quota_id
1235 , srp_plan_assign_id
1236 , role_id
1237 , comp_group_id
1238 , commission_amount
1239 , employee_number
1240 , reversal_flag
1241 , reversal_header_id
1242 , sales_channel
1243 , object_version_number
1244 , split_pct
1245 , split_status
1246 , org_id
1247 , terr_id
1248 , terr_name
1249 )
1250 SELECT p_salesrep_id_tbl(i)
1251 , ccla.processed_date
1252 , ccla.processed_period_id
1253 , ROUND(NVL((ccla.transaction_amount * p_split_pctg_tbl(i)) / 100, 0), 2)
1254 , ccla.trx_type
1255 , ccla.revenue_class_id
1256 , 'UNLOADED'
1257 , ccla.attribute_category
1258 , ccla.attribute1
1259 , ccla.attribute2
1260 , ccla.attribute3
1261 , ccla.attribute4
1262 , ccla.attribute5
1263 , ccla.attribute6
1264 , ccla.attribute7
1265 , ccla.attribute8
1266 , ccla.attribute9
1267 , ccla.attribute10
1268 , ccla.attribute11
1269 , ccla.attribute12
1270 , ccla.attribute13
1271 , ccla.attribute14
1272 , ccla.attribute15
1273 , ccla.attribute16
1274 , ccla.attribute17
1275 , ccla.attribute18
1276 , ccla.attribute19
1277 , ccla.attribute20
1278 , ccla.attribute21
1279 , ccla.attribute22
1280 , ccla.attribute23
1281 , ccla.attribute24
1282 , ccla.attribute25
1283 , ccla.attribute26
1284 , ccla.attribute27
1285 , ccla.attribute28
1286 , ccla.attribute29
1287 , ccla.attribute30
1288 , ccla.attribute31
1289 , ccla.attribute32
1290 , ccla.attribute33
1291 , ccla.attribute34
1292 , ccla.attribute35
1293 , ccla.attribute36
1294 , ccla.attribute37
1295 , ccla.attribute38
1296 , ccla.attribute39
1297 , ccla.attribute40
1298 , ccla.attribute41
1299 , ccla.attribute42
1300 , ccla.attribute43
1301 , ccla.attribute44
1302 , ccla.attribute45
1303 , ccla.attribute46
1304 , ccla.attribute47
1305 , ccla.attribute48
1306 , ccla.attribute49
1307 , ccla.attribute50
1308 , ccla.attribute51
1309 , ccla.attribute52
1310 , ccla.attribute53
1311 , ccla.attribute54
1312 , ccla.attribute55
1313 , ccla.attribute56
1314 , ccla.attribute57
1315 , ccla.attribute58
1316 , ccla.attribute59
1317 , ccla.attribute60
1318 , ccla.attribute61
1319 , ccla.attribute62
1320 , ccla.attribute63
1321 , ccla.attribute64
1322 , ccla.attribute65
1323 , ccla.attribute66
1324 , ccla.attribute67
1325 , ccla.attribute68
1326 , ccla.attribute69
1327 , ccla.attribute70
1328 , ccla.attribute71
1329 , ccla.attribute72
1330 , ccla.attribute73
1331 , ccla.attribute74
1332 , ccla.attribute75
1333 , ccla.attribute76
1334 , ccla.attribute77
1335 , ccla.attribute78
1336 , ccla.attribute79
1337 , ccla.attribute80
1338 , ccla.attribute81
1339 , ccla.attribute82
1340 , ccla.attribute83
1341 , ccla.attribute84
1342 , ccla.attribute85
1343 , ccla.attribute86
1344 , ccla.attribute87
1345 , ccla.attribute88
1346 , ccla.attribute89
1347 , ccla.attribute90
1348 , ccla.attribute91
1349 , ccla.attribute92
1350 , ccla.attribute93
1351 , ccla.attribute94
1352 , ccla.attribute95
1353 , ccla.attribute96
1354 , ccla.attribute97
1355 , ccla.attribute98
1356 , ccla.attribute99
1357 , ccla.attribute100
1358 , cn_comm_lines_api_s.NEXTVAL
1359 , ccla.conc_batch_id
1360 , ccla.process_batch_id
1361 , NULL
1362 , ccla.rollup_date
1363 , ccla.source_doc_id
1364 , ccla.source_doc_type
1365 , g_user_id
1366 , g_sysdate
1367 , g_user_id
1368 , g_sysdate
1369 , g_login_id
1370 , ccla.transaction_currency_code
1371 , ccla.exchange_rate
1372 , NULL
1373 , ccla.trx_id
1374 , ccla.trx_line_id
1375 , ccla.trx_sales_line_id
1376 , ccla.quantity
1377 , ccla.source_trx_number
1378 , ccla.discount_percentage
1379 , ccla.margin_percentage
1380 , ccla.source_trx_id
1381 , ccla.source_trx_line_id
1382 , ccla.source_trx_sales_line_id
1383 , ccla.negated_flag
1384 , ccla.customer_id
1385 , ccla.inventory_item_id
1386 , ccla.order_number
1387 , ccla.booked_date
1388 , ccla.invoice_number
1389 , ccla.invoice_date
1390 , g_sysdate
1391 , g_user_id
1392 , p_rev_type_tbl(i)
1393 , ccla.adjust_rollup_flag
1394 , 'Created by TAE'
1395 , ccla.adjust_status
1396 , ccla.line_number
1397 , ccla.bill_to_address_id
1398 , ccla.ship_to_address_id
1399 , ccla.bill_to_contact_id
1400 , ccla.ship_to_contact_id
1401 , ccla.comm_lines_api_id
1402 , ccla.pre_defined_rc_flag
1403 , ccla.rollup_flag
1404 , ccla.forecast_id
1405 , ccla.upside_quantity
1406 , ccla.upside_amount
1407 , ccla.uom_code
1408 , ccla.reason_code
1409 , ccla.TYPE
1410 , ccla.pre_processed_code
1411 , ccla.quota_id
1412 , ccla.srp_plan_assign_id
1413 , p_role_id_tbl(i)
1414 , ccla.comp_group_id
1415 , ccla.commission_amount
1416 , p_emp_no_tbl(i)
1417 , ccla.reversal_flag
1418 , ccla.reversal_header_id
1419 , ccla.sales_channel
1420 , ccla.object_version_number
1421 , p_split_pctg_tbl(i)
1422 , ccla.split_status
1423 , ccla.org_id
1424 , p_terr_id_tbl(i)
1425 , p_terr_name_tbl(i)
1426 FROM cn_comm_lines_api_all ccla
1427 WHERE ccla.comm_lines_api_id = p_trans_object_id_tbl(i)
1428 AND ccla.org_id = p_org_id
1429 AND p_del_flag_tbl(i) <> 'Y';
1430 END IF;
1431
1432 debugmsg('SCA : End of insert_api_txns');
1433 EXCEPTION
1434 WHEN dml_errors THEN
1435 ERRORS := SQL%BULK_EXCEPTIONS.COUNT;
1436 debugmsg('SCA : Number of transactions that failed : ' || ERRORS);
1437
1438 /* Log the erroneous txns to log file */
1439 FOR i IN 1 .. ERRORS LOOP
1440 l_error_index := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX;
1441 debugmsg(
1442 'SCA : Error #'
1443 || i
1444 || ' occurred during comm_lines_api_id : '
1445 || p_trans_object_id_tbl(l_error_index)
1446 );
1447 debugmsg('SCA : Error message is ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
1448 END LOOP;
1449 WHEN OTHERS THEN
1450 debugmsg('SCA : Unexpected exception in insert_api_txns');
1451 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1452 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1453 retcode := 2;
1454 errbuf := 'CN_SCATM_TAE_PUB.insert_api_txns.others';
1455 END insert_api_txns;
1456
1457 /* This procedure calls the territory APIs and get the winning */
1458 /* salesreps and split percentages for each transaction */
1459 PROCEDURE get_credited_txns(
1460 p_where_clause IN VARCHAR2
1461 , p_request_id IN NUMBER
1462 , errbuf IN OUT NOCOPY VARCHAR2
1463 , retcode IN OUT NOCOPY VARCHAR2
1464 ) IS
1465 l_return_status VARCHAR2(30);
1466 l_msg_count NUMBER;
1467 l_msg_data VARCHAR2(3000);
1468 l_num_workers NUMBER;
1469 BEGIN
1470 debugmsg('SCA : Start of get_credited_txns');
1471 errbuf := NULL;
1472 retcode := 0;
1473 debugmsg('SCA : Populating data to TRANS table');
1474
1475 SELECT TO_NUMBER(NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'),1))
1476 INTO l_num_workers
1477 FROM dual;
1478
1479 IF l_num_workers > 10
1480 THEN
1481 l_num_workers := 10;
1482 ELSIF l_num_workers < 1
1483 THEN
1484 l_num_workers := 1;
1485 END IF;
1486
1487 /* insert the selected transactions from cn_comm_lines_api_all table */
1488 /* to the interface table jtf_tae_1001_sc_dea_trans */
1489 jty_assign_bulk_pub.collect_trans_data
1490 (
1491 p_api_version_number => 1.0
1492 , p_init_msg_list => fnd_api.g_false
1493 , p_source_id => -1001
1494 , p_trans_id => -1002
1495 , p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM'
1496 , p_mode => 'DATE EFFECTIVE'
1497 , p_where => p_where_clause
1498 , p_no_of_workers => l_num_workers
1499 , p_percent_analyzed => 20
1500 , -- this value can be either a profile option or a parameter to conc program
1501 p_request_id => p_request_id
1502 , -- request id of the concurrent program
1503 x_return_status => l_return_status
1504 , x_msg_count => l_msg_count
1505 , x_msg_data => l_msg_data
1506 , errbuf => errbuf
1507 , retcode => retcode
1508 );
1509
1510 IF (retcode <> 0) THEN
1511 debugmsg('SCA : jty_assign_bulk_pub.collect_trans_data has failed');
1512 RAISE fnd_api.g_exc_error;
1513 END IF;
1514
1515 debugmsg('SCA : jty_assign_bulk_pub.collect_trans_data completed successfully');
1516 debugmsg('SCA : Populating data to WINNERS table');
1517 /* this api will apply the rules to the transactions present in jtf_tae_1001_sc_dea_trans */
1518 /* and populate the winning salesreps for each transaction in the table jtf_tae_1001_sc_winners */
1519
1520 FOR l_worker_id IN 1..l_num_workers
1521 LOOP
1522 jty_assign_bulk_pub.get_winners
1523 (
1524 p_api_version_number => 1.0
1525 , p_init_msg_list => fnd_api.g_false
1526 , p_source_id => -1001
1527 , p_trans_id => -1002
1528 , p_program_name => 'SALES/INCENTIVE COMPENSATION PROGRAM'
1529 , p_mode => 'DATE EFFECTIVE'
1530 , p_percent_analyzed => 20
1531 , -- this value can be either a profile option or a parameter to conc program
1532 p_worker_id => l_worker_id
1533 , x_return_status => l_return_status
1534 , x_msg_count => l_msg_count
1535 , x_msg_data => l_msg_data
1536 , errbuf => errbuf
1537 , retcode => retcode
1538 );
1539
1540 IF (retcode <> 0) THEN
1541 debugmsg('SCA : jty_assign_bulk_pub.get_winners has failed');
1542 RAISE fnd_api.g_exc_error;
1543 END IF;
1544 END LOOP;
1545
1546 debugmsg('SCA : jty_assign_bulk_pub.get_winners completed successfully');
1547 debugmsg('SCA : End of get_credited_txns');
1548 EXCEPTION
1549 WHEN fnd_api.g_exc_error THEN
1550 debugmsg('SCA : CN_SCATM_TAE_PUB.get_credited_txns.g_exc_error');
1551 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1552 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1553 WHEN OTHERS THEN
1554 debugmsg('SCA : Unexpected exception in get_credited_txns');
1555 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1556 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1557 retcode := 2;
1558 errbuf := 'CN_SCATM_TAE_PUB.get_credited_txns.others';
1559 END get_credited_txns;
1560
1561 /* This procedure gets the winning salesreps, split percentages and revenue types from the */
1562 /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
1563 PROCEDURE process_new_txns(
1564 p_org_id IN NUMBER
1565 , p_worker_id IN NUMBER
1566 , errbuf IN OUT NOCOPY VARCHAR2
1567 , retcode IN OUT NOCOPY VARCHAR2
1568 ) IS
1569 TYPE l_credited_txn_curtyp IS REF CURSOR;
1570
1571 c_credited_txn_cur l_credited_txn_curtyp;
1572 l_ffname_split_pctg VARCHAR2(15);
1573 l_ffname_rev_type VARCHAR2(15);
1574 l_no_of_errors NUMBER;
1575 l_trans_object_id_tbl g_trans_object_id_tbl_type;
1576 l_terr_id_tbl g_terr_id_tbl_type;
1577 l_terr_name_tbl g_terr_name_tbl_type;
1578 l_salesrep_id_tbl g_salesrep_id_tbl_type;
1579 l_emp_no_tbl g_emp_no_tbl_type;
1580 l_role_id_tbl g_role_id_tbl_type;
1581 l_split_pctg_tbl g_split_pctg_tbl_type;
1582 l_rev_type_tbl g_rev_type_tbl_type;
1583 l_del_flag_tbl g_del_flag_tbl_type;
1584 BEGIN
1585 debugmsg('SCA : Start of process_new_txns');
1586 errbuf := NULL;
1587 retcode := 0;
1588 /* Get name of the flex fields used in TM */
1589 /* to store split pctg and revenue type */
1590 get_flex_field_names(
1591 p_ffname_split_pctg => l_ffname_split_pctg
1592 , p_ffname_rev_type => l_ffname_rev_type
1593 , errbuf => errbuf
1594 , retcode => retcode
1595 );
1596
1597 IF (retcode <> 0) THEN
1598 debugmsg('SCA : CN_SCATM_TAE_PUB.get_flex_field_names has failed');
1599 RAISE fnd_api.g_exc_error;
1600 END IF;
1601
1602 debugmsg('SCA : CN_SCATM_TAE_PUB.get_flex_field_names completed successfully');
1603
1604 /* Cursor definition to select all winning resources from winners table */
1605 OPEN c_credited_txn_cur
1606 FOR 'SELECT a.trans_object_id, '
1607 || ' a.terr_id, '
1608 || ' c.name, '
1609 || ' d.salesrep_id, '
1610 || ' d.employee_number, '
1611 || ' a.role_id, '
1612 || ' ''N'', '
1613 || ' b.'
1614 || l_ffname_split_pctg
1615 || ', '
1616 || ' b.'
1617 || l_ffname_rev_type
1618 || ' '
1619 || 'FROM jtf_tae_1001_sc_winners a, '
1620 || ' jtf_terr_rsc_all b, '
1621 || ' jtf_terr_all c, '
1622 || ' cn_salesreps d '
1623 || 'WHERE a.terr_rsc_id = b.terr_rsc_id '
1624 || 'AND a.terr_id = c.terr_id '
1625 || 'AND a.resource_id = d.resource_id '
1626 || 'AND a.worker_id = '||p_worker_id;
1627
1628 /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
1629 /* and insert the records in the table cn_comm_lines_api_all */
1630 LOOP
1631 FETCH c_credited_txn_cur
1632 BULK COLLECT INTO l_trans_object_id_tbl
1633 , l_terr_id_tbl
1634 , l_terr_name_tbl
1635 , l_salesrep_id_tbl
1636 , l_emp_no_tbl
1637 , l_role_id_tbl
1638 , l_del_flag_tbl
1639 , l_split_pctg_tbl
1640 , l_rev_type_tbl LIMIT g_fetch_limit;
1641
1642 EXIT WHEN l_trans_object_id_tbl.COUNT <= 0;
1643 debugmsg('SCA : Number of winning rows returned : ' || l_trans_object_id_tbl.COUNT);
1644 /* insert the credited txns into api table */
1645 insert_api_txns(
1646 p_org_id => p_org_id
1647 , p_trans_object_id_tbl => l_trans_object_id_tbl
1648 , p_salesrep_id_tbl => l_salesrep_id_tbl
1649 , p_emp_no_tbl => l_emp_no_tbl
1650 , p_role_id_tbl => l_role_id_tbl
1651 , p_split_pctg_tbl => l_split_pctg_tbl
1652 , p_rev_type_tbl => l_rev_type_tbl
1653 , p_terr_id_tbl => l_terr_id_tbl
1654 , p_terr_name_tbl => l_terr_name_tbl
1655 , p_del_flag_tbl => l_del_flag_tbl
1656 , errbuf => errbuf
1657 , retcode => retcode
1658 );
1659
1660 IF (retcode <> 0) THEN
1661 debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns has failed');
1662 RAISE fnd_api.g_exc_error;
1663 END IF;
1664
1665 debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns completed successfully');
1666 END LOOP;
1667
1668 CLOSE c_credited_txn_cur;
1669
1670 debugmsg('SCA : End of process_new_txns');
1671 EXCEPTION
1672 WHEN fnd_api.g_exc_error THEN
1673 IF c_credited_txn_cur%ISOPEN THEN
1674 CLOSE c_credited_txn_cur;
1675 END IF;
1676
1677 debugmsg('SCA : CN_SCATM_TAE_PUB.process_new_txns.g_exc_error');
1678 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1679 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1680 WHEN OTHERS THEN
1681 IF c_credited_txn_cur%ISOPEN THEN
1682 CLOSE c_credited_txn_cur;
1683 END IF;
1684
1685 debugmsg('SCA : Unexpected exception in process_new_txns');
1686 debugmsg('SCA : SQLCODE : ' || SQLCODE);
1687 debugmsg('SCA : SQLERRM : ' || SQLERRM);
1688 retcode := 2;
1689 errbuf := 'CN_SCATM_TAE_PUB.process_new_txns.others';
1690 END process_new_txns;
1691
1692 /* This procedure does the following for txns that have been loaded for calc */
1693 /* -- obsolete the corresponding record in cn_commission_headers_all */
1694 /* -- create a reversal entry in cn_comm_lines_api_all */
1695 PROCEDURE api_negate_record(
1696 p_api_id_tbl IN OUT NOCOPY g_comm_lines_api_id_tbl_type
1697 , p_rowid_tbl IN OUT NOCOPY g_rowid_tbl_type
1698 , errbuf IN OUT NOCOPY VARCHAR2
1699 , retcode IN OUT NOCOPY VARCHAR2
1700 ) IS
1701 BEGIN
1702 debugmsg('SCA : Start of api_negate_record');
1703 errbuf := NULL;
1704 retcode := 0;
1705
1706 IF (p_api_id_tbl.COUNT <= 0) THEN
1707 RETURN;
1708 END IF;
1709
1710 /* create the reversal entry in api table */
1711 FORALL i IN p_rowid_tbl.FIRST .. p_rowid_tbl.LAST
1712 INSERT INTO cn_comm_lines_api_all
1713 (
1714 salesrep_id
1715 , processed_date
1716 , processed_period_id
1717 , transaction_amount
1718 , trx_type
1719 , revenue_class_id
1720 , load_status
1721 , attribute_category
1722 , attribute1
1723 , attribute2
1724 , attribute3
1725 , attribute4
1726 , attribute5
1727 , attribute6
1728 , attribute7
1729 , attribute8
1730 , attribute9
1731 , attribute10
1732 , attribute11
1733 , attribute12
1734 , attribute13
1735 , attribute14
1736 , attribute15
1737 , attribute16
1738 , attribute17
1739 , attribute18
1740 , attribute19
1741 , attribute20
1742 , attribute21
1743 , attribute22
1744 , attribute23
1745 , attribute24
1746 , attribute25
1747 , attribute26
1748 , attribute27
1749 , attribute28
1750 , attribute29
1751 , attribute30
1752 , attribute31
1753 , attribute32
1754 , attribute33
1755 , attribute34
1756 , attribute35
1757 , attribute36
1758 , attribute37
1759 , attribute38
1760 , attribute39
1761 , attribute40
1762 , attribute41
1763 , attribute42
1764 , attribute43
1765 , attribute44
1766 , attribute45
1767 , attribute46
1768 , attribute47
1769 , attribute48
1770 , attribute49
1771 , attribute50
1772 , attribute51
1773 , attribute52
1774 , attribute53
1775 , attribute54
1776 , attribute55
1777 , attribute56
1778 , attribute57
1779 , attribute58
1780 , attribute59
1781 , attribute60
1782 , attribute61
1783 , attribute62
1784 , attribute63
1785 , attribute64
1786 , attribute65
1787 , attribute66
1788 , attribute67
1789 , attribute68
1790 , attribute69
1791 , attribute70
1792 , attribute71
1793 , attribute72
1794 , attribute73
1795 , attribute74
1796 , attribute75
1797 , attribute76
1798 , attribute77
1799 , attribute78
1800 , attribute79
1801 , attribute80
1802 , attribute81
1803 , attribute82
1804 , attribute83
1805 , attribute84
1806 , attribute85
1807 , attribute86
1808 , attribute87
1809 , attribute88
1810 , attribute89
1811 , attribute90
1812 , attribute91
1813 , attribute92
1814 , attribute93
1815 , attribute94
1816 , attribute95
1817 , attribute96
1818 , attribute97
1819 , attribute98
1820 , attribute99
1821 , attribute100
1822 , comm_lines_api_id
1823 , conc_batch_id
1824 , process_batch_id
1825 , salesrep_number
1826 , rollup_date
1827 , source_doc_id
1828 , source_doc_type
1829 , created_by
1830 , creation_date
1831 , last_updated_by
1832 , last_update_date
1833 , last_update_login
1834 , transaction_currency_code
1835 , exchange_rate
1836 , acctd_transaction_amount
1837 , trx_id
1838 , trx_line_id
1839 , trx_sales_line_id
1840 , quantity
1841 , source_trx_number
1842 , discount_percentage
1843 , margin_percentage
1844 , source_trx_id
1845 , source_trx_line_id
1846 , source_trx_sales_line_id
1847 , negated_flag
1848 , customer_id
1849 , inventory_item_id
1850 , order_number
1851 , booked_date
1852 , invoice_number
1853 , invoice_date
1854 , adjust_date
1855 , adjusted_by
1856 , revenue_type
1857 , adjust_rollup_flag
1858 , adjust_comments
1859 , adjust_status
1860 , line_number
1861 , bill_to_address_id
1862 , ship_to_address_id
1863 , bill_to_contact_id
1864 , ship_to_contact_id
1865 , adj_comm_lines_api_id
1866 , pre_defined_rc_flag
1867 , rollup_flag
1868 , forecast_id
1869 , upside_quantity
1870 , upside_amount
1871 , uom_code
1872 , reason_code
1873 , TYPE
1874 , pre_processed_code
1875 , quota_id
1876 , srp_plan_assign_id
1877 , role_id
1878 , comp_group_id
1879 , commission_amount
1880 , employee_number
1881 , reversal_flag
1882 , reversal_header_id
1883 , sales_channel
1884 , object_version_number
1885 , split_pct
1886 , split_status
1887 , org_id
1888 , terr_id
1889 , terr_name
1890 )
1891 SELECT ccla.salesrep_id
1892 , ccla.processed_date
1893 , ccla.processed_period_id
1894 , -1 * NVL(ccla.transaction_amount, 0)
1895 , ccla.trx_type
1896 , ccla.revenue_class_id
1897 , 'UNLOADED'
1898 , ccla.attribute_category
1899 , ccla.attribute1
1900 , ccla.attribute2
1901 , ccla.attribute3
1902 , ccla.attribute4
1903 , ccla.attribute5
1904 , ccla.attribute6
1905 , ccla.attribute7
1906 , ccla.attribute8
1907 , ccla.attribute9
1908 , ccla.attribute10
1909 , ccla.attribute11
1910 , ccla.attribute12
1911 , ccla.attribute13
1912 , ccla.attribute14
1913 , ccla.attribute15
1914 , ccla.attribute16
1915 , ccla.attribute17
1916 , ccla.attribute18
1917 , ccla.attribute19
1918 , ccla.attribute20
1919 , ccla.attribute21
1920 , ccla.attribute22
1921 , ccla.attribute23
1922 , ccla.attribute24
1923 , ccla.attribute25
1924 , ccla.attribute26
1925 , ccla.attribute27
1926 , ccla.attribute28
1927 , ccla.attribute29
1928 , ccla.attribute30
1929 , ccla.attribute31
1930 , ccla.attribute32
1931 , ccla.attribute33
1932 , ccla.attribute34
1933 , ccla.attribute35
1934 , ccla.attribute36
1935 , ccla.attribute37
1936 , ccla.attribute38
1937 , ccla.attribute39
1938 , ccla.attribute40
1939 , ccla.attribute41
1940 , ccla.attribute42
1941 , ccla.attribute43
1942 , ccla.attribute44
1943 , ccla.attribute45
1944 , ccla.attribute46
1945 , ccla.attribute47
1946 , ccla.attribute48
1947 , ccla.attribute49
1948 , ccla.attribute50
1949 , ccla.attribute51
1950 , ccla.attribute52
1951 , ccla.attribute53
1952 , ccla.attribute54
1953 , ccla.attribute55
1954 , ccla.attribute56
1955 , ccla.attribute57
1956 , ccla.attribute58
1957 , ccla.attribute59
1958 , ccla.attribute60
1959 , ccla.attribute61
1960 , ccla.attribute62
1961 , ccla.attribute63
1962 , ccla.attribute64
1963 , ccla.attribute65
1964 , ccla.attribute66
1965 , ccla.attribute67
1966 , ccla.attribute68
1967 , ccla.attribute69
1968 , ccla.attribute70
1969 , ccla.attribute71
1970 , ccla.attribute72
1971 , ccla.attribute73
1972 , ccla.attribute74
1973 , ccla.attribute75
1974 , ccla.attribute76
1975 , ccla.attribute77
1976 , ccla.attribute78
1977 , ccla.attribute79
1978 , ccla.attribute80
1979 , ccla.attribute81
1980 , ccla.attribute82
1981 , ccla.attribute83
1982 , ccla.attribute84
1983 , ccla.attribute85
1984 , ccla.attribute86
1985 , ccla.attribute87
1986 , ccla.attribute88
1987 , ccla.attribute89
1988 , ccla.attribute90
1989 , ccla.attribute91
1990 , ccla.attribute92
1991 , ccla.attribute93
1992 , ccla.attribute94
1993 , ccla.attribute95
1994 , ccla.attribute96
1995 , ccla.attribute97
1996 , ccla.attribute98
1997 , ccla.attribute99
1998 , ccla.attribute100
1999 , cn_comm_lines_api_s.NEXTVAL
2000 , NULL
2001 , NULL
2002 , NULL
2003 , ccla.rollup_date
2004 , NULL
2005 , ccla.source_doc_type
2006 , g_user_id
2007 , g_sysdate
2008 , g_user_id
2009 , g_sysdate
2010 , g_login_id
2011 , ccla.transaction_currency_code
2012 , ccla.exchange_rate
2013 , -1 * NVL(ccla.acctd_transaction_amount, 0)
2014 , NULL
2015 , NULL
2016 , NULL
2017 , -1 * ccla.quantity
2018 , ccla.source_trx_number
2019 , ccla.discount_percentage
2020 , ccla.margin_percentage
2021 , ccla.source_trx_id
2022 , ccla.source_trx_line_id
2023 , ccla.source_trx_sales_line_id
2024 , 'Y'
2025 , ccla.customer_id
2026 , ccla.inventory_item_id
2027 , ccla.order_number
2028 , ccla.booked_date
2029 , ccla.invoice_number
2030 , ccla.invoice_date
2031 , g_sysdate
2032 , g_user_id
2033 , ccla.revenue_type
2034 , ccla.adjust_rollup_flag
2035 , 'Created by TAE'
2036 , 'REVERSAL'
2037 , ccla.line_number
2038 , ccla.bill_to_address_id
2039 , ccla.ship_to_address_id
2040 , ccla.bill_to_contact_id
2041 , ccla.ship_to_contact_id
2042 , ccla.comm_lines_api_id
2043 , ccla.pre_defined_rc_flag
2044 , ccla.rollup_flag
2045 , ccla.forecast_id
2046 , ccla.upside_quantity
2047 , ccla.upside_amount
2048 , ccla.uom_code
2049 , ccla.reason_code
2050 , ccla.TYPE
2051 , ccla.pre_processed_code
2052 , ccla.quota_id
2053 , ccla.srp_plan_assign_id
2054 , ccla.role_id
2055 , ccla.comp_group_id
2056 , ccla.commission_amount
2057 , ccla.employee_number
2058 , 'Y'
2059 , ccha.commission_header_id
2060 , ccla.sales_channel
2061 , ccla.object_version_number
2062 , ccla.split_pct
2063 , ccla.split_status
2064 , ccla.org_id
2065 , ccla.terr_id
2066 , ccla.terr_name
2067 FROM cn_comm_lines_api ccla, cn_commission_headers_all ccha
2068 WHERE ccla.ROWID = p_rowid_tbl(i)
2069 AND ccha.comm_lines_api_id = ccla.comm_lines_api_id
2070 AND ((ccha.adjust_status NOT IN('FROZEN', 'REVERSAL')) OR(ccha.adjust_status IS NULL))
2071 AND ccha.trx_type NOT IN('ITD', 'GRP', 'THR');
2072 /* update the corresponding records in commission_headers */
2073 FORALL i IN p_api_id_tbl.FIRST .. p_api_id_tbl.LAST
2074 UPDATE cn_commission_headers
2075 SET adjust_status = 'FROZEN'
2076 , reversal_header_id =
2077 (SELECT commission_header_id
2078 FROM cn_commission_headers_all
2079 WHERE comm_lines_api_id = p_api_id_tbl(i)
2080 AND ((adjust_status NOT IN('FROZEN', 'REVERSAL')) OR(adjust_status IS NULL))
2081 AND trx_type NOT IN('ITD', 'GRP', 'THR'))
2082 , reversal_flag = 'Y'
2083 , adjust_date = g_sysdate
2084 , adjusted_by = g_user_id
2085 , adjust_comments = 'Created by SCA'
2086 , last_update_date = g_sysdate
2087 , last_updated_by = g_user_id
2088 , last_update_login = g_login_id
2089 WHERE comm_lines_api_id = p_api_id_tbl(i);
2090 debugmsg('SCA : End of api_negate_record');
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 debugmsg('SCA : Unexpected exception in api_negate_record');
2094 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2095 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2096 retcode := 2;
2097 errbuf := 'CN_SCATM_TAE_PUB.api_negate_record.others';
2098 END api_negate_record;
2099
2100 /* This procedure deletes the child transaction records */
2101 /* from api table which have not been loaded for calculation */
2102 PROCEDURE handle_unloaded_txns(
2103 l_unloaded_txn_tbl IN OUT NOCOPY g_rowid_tbl_type
2104 , p_rowid IN ROWID
2105 , p_update_flag IN BOOLEAN
2106 , errbuf IN OUT NOCOPY VARCHAR2
2107 , retcode IN OUT NOCOPY VARCHAR2
2108 ) IS
2109 l_no_of_records NUMBER;
2110 BEGIN
2111 debugmsg('SCA : Start of handle_unloaded_txns');
2112 errbuf := NULL;
2113 retcode := 0;
2114
2115 /* Store the txn in the global pl/sql table if a valid txn is passed */
2116 IF (p_rowid IS NOT NULL) THEN
2117 l_unloaded_txn_tbl.EXTEND;
2118 l_unloaded_txn_tbl(l_unloaded_txn_tbl.LAST) := p_rowid;
2119 END IF;
2120
2121 l_no_of_records := l_unloaded_txn_tbl.COUNT;
2122
2123 /* change DB if the # of records in the pl/sql table becomes greater than */
2124 /* "g_fetch_limit" or if the procedure is called exclusively to update the table */
2125 IF (l_no_of_records > 0) THEN
2126 IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
2127 FORALL i IN l_unloaded_txn_tbl.FIRST .. l_unloaded_txn_tbl.LAST
2128 DELETE cn_comm_lines_api_all
2129 WHERE ROWID = l_unloaded_txn_tbl(i);
2130 l_unloaded_txn_tbl.TRIM(l_no_of_records);
2131 END IF;
2132 END IF;
2133
2134 debugmsg('SCA : End of handle_unloaded_txns');
2135 EXCEPTION
2136 WHEN OTHERS THEN
2137 debugmsg('SCA : Unexpected exception in handle_unloaded_txns');
2138 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2139 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2140 retcode := 2;
2141 errbuf := 'CN_SCATM_TAE_PUB.handle_unloaded_txns.others';
2142 END handle_unloaded_txns;
2143
2144 /* This procedure processes the child transactions that have been loaded for calc */
2145 PROCEDURE handle_loaded_txns(
2146 l_loaded_txn_rowid_tbl IN OUT NOCOPY g_rowid_tbl_type
2147 , l_loaded_txn_comid_tbl IN OUT NOCOPY g_comm_lines_api_id_tbl_type
2148 , p_rowid IN ROWID
2149 , p_api_id IN NUMBER
2150 , p_update_flag IN BOOLEAN
2151 , errbuf IN OUT NOCOPY VARCHAR2
2152 , retcode IN OUT NOCOPY VARCHAR2
2153 ) IS
2154 l_no_of_records NUMBER;
2155 BEGIN
2156 debugmsg('SCA : Start of handle_loaded_txns');
2157 errbuf := NULL;
2158 retcode := 0;
2159
2160 /* Store the txn in the global pl/sql table if a valid txn is passed */
2161 IF (p_rowid IS NOT NULL) THEN
2162 l_loaded_txn_rowid_tbl.EXTEND;
2163 l_loaded_txn_rowid_tbl(l_loaded_txn_rowid_tbl.LAST) := p_rowid;
2164 l_loaded_txn_comid_tbl.EXTEND;
2165 l_loaded_txn_comid_tbl(l_loaded_txn_comid_tbl.LAST) := p_api_id;
2166 END IF;
2167
2168 l_no_of_records := l_loaded_txn_rowid_tbl.COUNT;
2169
2170 /* change DB if the # of records in the pl/sql table becomes greater than */
2171 /* "g_fetch_limit" or if the procedure is called exclusively to update the table */
2172 IF (l_no_of_records > 0) THEN
2173 IF ((l_no_of_records >= g_fetch_limit) OR(p_update_flag)) THEN
2174 api_negate_record(
2175 p_api_id_tbl => l_loaded_txn_comid_tbl
2176 , p_rowid_tbl => l_loaded_txn_rowid_tbl
2177 , errbuf => errbuf
2178 , retcode => retcode
2179 );
2180
2181 IF (retcode <> 0) THEN
2182 debugmsg('SCA : CN_SCATM_TAE_PUB.api_negate_record has failed');
2183 RAISE fnd_api.g_exc_error;
2184 END IF;
2185
2186 debugmsg('SCA : CN_SCATM_TAE_PUB.api_negate_record completed successfully');
2187 l_loaded_txn_rowid_tbl.TRIM(l_no_of_records);
2188 l_loaded_txn_comid_tbl.TRIM(l_no_of_records);
2189 END IF;
2190 END IF;
2191
2192 debugmsg('SCA : End of handle_loaded_txns');
2193 EXCEPTION
2194 WHEN fnd_api.g_exc_error THEN
2195 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_loaded_txns.g_exc_error');
2196 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2197 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2198 WHEN OTHERS THEN
2199 debugmsg('SCA : Unexpected exception in handle_loaded_txns');
2200 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2201 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2202 retcode := 2;
2203 errbuf := 'CN_SCATM_TAE_PUB.handle_loaded_txns.others';
2204 END handle_loaded_txns;
2205
2206 /* This procedure gets the winning salesreps, split percentages and revenue types from the */
2207 /* table jtf_tae_1001_sc_winners and create credited transactions in the table cn_comm_lines_api_all */
2208 PROCEDURE process_all_txns(
2209 p_org_id IN NUMBER
2210 , p_worker_id IN NUMBER
2211 , errbuf IN OUT NOCOPY VARCHAR2
2212 , retcode IN OUT NOCOPY VARCHAR2
2213 ) IS
2214 TYPE l_credited_txn_curtyp IS REF CURSOR;
2215
2216 TYPE l_txn_amt_tbl_type IS TABLE OF cn_comm_lines_api_all.transaction_amount%TYPE;
2217
2218 TYPE l_no_of_credits_tbl_type IS TABLE OF NUMBER;
2219
2220 TYPE l_child_load_status_tbl_type IS TABLE OF cn_comm_lines_api_all.load_status%TYPE;
2221
2222 c_credited_txn_cur l_credited_txn_curtyp;
2223 l_ffname_split_pctg VARCHAR2(15);
2224 l_ffname_rev_type VARCHAR2(15);
2225 l_error_index NUMBER;
2226 l_table_index NUMBER;
2227 l_no_of_credits NUMBER;
2228 l_api_id NUMBER;
2229 l_rows_fetched NUMBER;
2230 l_match_found BOOLEAN;
2231 l_txn_amt NUMBER;
2232 l_temp_index NUMBER;
2233 l_no_of_errors NUMBER;
2234 l_rowid_tbl g_rowid_tbl_type;
2235 l_api_id_tbl g_trans_object_id_tbl_type;
2236 l_terr_id_tbl g_terr_id_tbl_type;
2237 l_terr_name_tbl g_terr_name_tbl_type;
2238 l_salesrep_id_tbl g_salesrep_id_tbl_type;
2239 l_emp_no_tbl g_emp_no_tbl_type;
2240 l_role_id_tbl g_role_id_tbl_type;
2241 l_txn_amt_tbl l_txn_amt_tbl_type;
2242 l_split_pctg_tbl g_split_pctg_tbl_type;
2243 l_rev_type_tbl g_rev_type_tbl_type;
2244 l_del_flag_tbl g_del_flag_tbl_type;
2245 l_no_of_credits_tbl l_no_of_credits_tbl_type;
2246 l_child_rowid_tbl g_rowid_tbl_type;
2247 l_child_api_id_tbl g_trans_object_id_tbl_type;
2248 l_child_salesrep_id_tbl g_salesrep_id_tbl_type;
2249 l_child_txn_amt_tbl l_txn_amt_tbl_type;
2250 l_child_role_id_tbl g_role_id_tbl_type;
2251 l_child_terr_id_tbl g_terr_id_tbl_type;
2252 l_child_split_pctg_tbl g_split_pctg_tbl_type;
2253 l_child_rev_type_tbl g_rev_type_tbl_type;
2254 l_child_load_status_tbl l_child_load_status_tbl_type;
2255
2256 l_unloaded_txn_tbl g_rowid_tbl_type;
2257 l_loaded_txn_rowid_tbl g_rowid_tbl_type;
2258 l_loaded_txn_comid_tbl g_comm_lines_api_id_tbl_type;
2259
2260 BEGIN
2261 debugmsg('SCA : Start of process_all_txns');
2262
2263 -- initialise the tables
2264 l_unloaded_txn_tbl := g_rowid_tbl_type();
2265 l_loaded_txn_rowid_tbl := g_rowid_tbl_type();
2266 l_loaded_txn_comid_tbl := g_comm_lines_api_id_tbl_type();
2267
2268 errbuf := NULL;
2269 retcode := 0;
2270 /* Get name of the flex fields used in TM */
2271 /* to store split pctg and revenue type */
2272 get_flex_field_names(
2273 p_ffname_split_pctg => l_ffname_split_pctg
2274 , p_ffname_rev_type => l_ffname_rev_type
2275 , errbuf => errbuf
2276 , retcode => retcode
2277 );
2278
2279 IF (retcode <> 0) THEN
2280 debugmsg('SCA : CN_SCATM_TAE_PUB.get_flex_field_names has failed');
2281 RAISE fnd_api.g_exc_error;
2282 END IF;
2283
2284 debugmsg('SCA : CN_SCATM_TAE_PUB.get_flex_field_names completed successfully');
2285
2286 /* Cursor definition to get all the winning resources returned by TAE */
2287 OPEN c_credited_txn_cur
2288 FOR 'SELECT d.rowid, '
2289 || ' d.comm_lines_api_id, '
2290 || ' a.terr_id, '
2291 || ' c.name, '
2292 || ' e.salesrep_id, '
2293 || ' e.employee_number, '
2294 || ' a.role_id, '
2295 || ' d.transaction_amount, '
2296 || ' b.'
2297 || l_ffname_split_pctg
2298 || ', '
2299 || ' b.'
2300 || l_ffname_rev_type
2301 || ', '
2302 || ' ''N'', '
2303 || ' count(*) over(partition by d.comm_lines_api_id) '
2304 || 'FROM jtf_tae_1001_sc_winners a, '
2305 || ' jtf_terr_rsc_all b, '
2306 || ' jtf_terr_all c, '
2307 || ' cn_comm_lines_api_all d, '
2308 || ' cn_salesreps e '
2309 || 'WHERE a.terr_rsc_id = b.terr_rsc_id '
2310 || 'AND a.terr_id = c.terr_id '
2311 || 'AND a.trans_object_id = d.comm_lines_api_id '
2312 || 'AND a.resource_id = e.resource_id '
2313 || 'AND a.worker_id = '||p_worker_id;
2314
2315 /* loop through the winning resources in batches , "g_fetch_limit" records per batch, */
2316 /* and insert the records in the table cn_comm_lines_api_all */
2317 LOOP
2318 FETCH c_credited_txn_cur
2319 BULK COLLECT INTO l_rowid_tbl
2320 , l_api_id_tbl
2321 , l_terr_id_tbl
2322 , l_terr_name_tbl
2323 , l_salesrep_id_tbl
2324 , l_emp_no_tbl
2325 , l_role_id_tbl
2326 , l_txn_amt_tbl
2327 , l_split_pctg_tbl
2328 , l_rev_type_tbl
2329 , l_del_flag_tbl
2330 , l_no_of_credits_tbl LIMIT g_fetch_limit;
2331
2332 EXIT WHEN l_rowid_tbl.COUNT <= 0;
2333 debugmsg('SCA : Number of winning rows returned : ' || l_rowid_tbl.COUNT);
2334 /* Start Code to make sure that the winning records of a */
2335 /* particular transactions are not fetched across batches */
2336 debugmsg('SCA : Start of fetching remaining winning records for last txn');
2337 l_table_index := l_rowid_tbl.LAST;
2338 l_no_of_credits := l_no_of_credits_tbl(l_table_index);
2339 l_api_id := l_api_id_tbl(l_table_index);
2340 l_rows_fetched := 0;
2341
2342 /* Get the number of rows fetched for the last transaction */
2343 LOOP
2344 l_rows_fetched := l_rows_fetched + 1;
2345
2346 IF (
2347 (l_rows_fetched = l_no_of_credits)
2348 OR (l_table_index = l_rowid_tbl.FIRST)
2349 OR (l_api_id <> l_api_id_tbl(l_table_index - 1))
2350 ) THEN
2351 EXIT;
2352 END IF;
2353
2354 /* go to previous row */
2355 l_table_index := l_table_index - 1;
2356 END LOOP;
2357
2358 /* fetch the remaining winning records for the last transaction */
2359 FOR i IN 1 ..(l_no_of_credits - l_rows_fetched) LOOP
2360 l_rowid_tbl.EXTEND;
2361 l_api_id_tbl.EXTEND;
2362 l_terr_id_tbl.EXTEND;
2363 l_terr_name_tbl.EXTEND;
2364 l_salesrep_id_tbl.EXTEND;
2365 l_emp_no_tbl.EXTEND;
2366 l_role_id_tbl.EXTEND;
2367 l_txn_amt_tbl.EXTEND;
2368 l_split_pctg_tbl.EXTEND;
2369 l_rev_type_tbl.EXTEND;
2370 l_del_flag_tbl.EXTEND;
2371 l_no_of_credits_tbl.EXTEND;
2372
2373 FETCH c_credited_txn_cur
2374 INTO l_rowid_tbl(l_rowid_tbl.LAST)
2375 , l_api_id_tbl(l_api_id_tbl.LAST)
2376 , l_terr_id_tbl(l_terr_id_tbl.LAST)
2377 , l_terr_name_tbl(l_terr_name_tbl.LAST)
2378 , l_salesrep_id_tbl(l_salesrep_id_tbl.LAST)
2379 , l_emp_no_tbl(l_emp_no_tbl.LAST)
2380 , l_role_id_tbl(l_role_id_tbl.LAST)
2381 , l_txn_amt_tbl(l_txn_amt_tbl.LAST)
2382 , l_split_pctg_tbl(l_split_pctg_tbl.LAST)
2383 , l_rev_type_tbl(l_rev_type_tbl.LAST)
2384 , l_del_flag_tbl(l_del_flag_tbl.LAST)
2385 , l_no_of_credits_tbl(l_no_of_credits_tbl.LAST);
2386 END LOOP; /* end loop for */
2387
2388 debugmsg('SCA : End of fetching remaining winning records for last txn');
2389 /* End Code to make sure that the winning records of a */
2390 /* particular transactions are not fetched across batches */
2391
2392 /* Process all the winning records row by row */
2393 l_table_index := l_rowid_tbl.FIRST;
2394
2395 LOOP
2396 /* exit after we have processed the last row */
2397 IF (l_table_index > l_rowid_tbl.LAST) THEN
2398 EXIT;
2399 END IF;
2400
2401 debugmsg('SCA : Now processing transaction with id : ' || l_api_id_tbl(l_table_index));
2402
2403 /* Get all children of the transaction which are */
2404 /* active and generated by this crediting process */
2405 SELECT ROWID
2406 , comm_lines_api_id
2407 , load_status
2408 , salesrep_id
2409 , transaction_amount
2410 , role_id
2411 , terr_id
2412 , split_pct
2413 , revenue_type
2414 BULK COLLECT INTO l_child_rowid_tbl
2415 , l_child_api_id_tbl
2416 , l_child_load_status_tbl
2417 , l_child_salesrep_id_tbl
2418 , l_child_txn_amt_tbl
2419 , l_child_role_id_tbl
2420 , l_child_terr_id_tbl
2421 , l_child_split_pctg_tbl
2422 , l_child_rev_type_tbl
2423 FROM cn_comm_lines_api_all
2424 WHERE load_status NOT IN('OBSOLETE', 'FILTERED')
2425 AND ((adjust_status IS NULL) OR(adjust_status NOT IN('FROZEN', 'REVERSAL')))
2426 START WITH comm_lines_api_id = l_api_id_tbl(l_table_index)
2427 CONNECT BY PRIOR comm_lines_api_id = adj_comm_lines_api_id;
2428
2429 IF (l_child_rowid_tbl.COUNT > 0) THEN
2430 FOR i IN l_child_rowid_tbl.FIRST .. l_child_rowid_tbl.LAST LOOP
2431 debugmsg('SCA : Now processing child transaction with id : ' || l_child_api_id_tbl(i));
2432
2433 /* if the child has not been loaded for calculation */
2434 /* delete the child record from cn_comm_lines_api_all */
2435 IF (l_child_load_status_tbl(i) <> 'LOADED') THEN
2436 /* delete the row if it is not the same txn that we have processed */
2437 IF (
2438 (l_child_api_id_tbl(i) <> l_api_id_tbl(l_table_index))
2439 AND (l_child_terr_id_tbl(i) IS NOT NULL)
2440 ) THEN
2441
2442
2443
2444 /* start of code : logic used here is similar to used for loaded tansaction. Reference bug 7589796 */
2445 l_match_found := FALSE;
2446
2447 /* check to see if the child matches with any of the credited transaction */
2448 /* if so, donot obsolete the child instead donot insert the new credited txn generated */
2449 FOR j IN 1 .. l_no_of_credits_tbl(l_table_index) LOOP
2450 l_temp_index := l_table_index +(j - 1);
2451
2452 /* update txn amt to -1 if user either has not specified anything for split pctg */
2453 /* or has specified an invalid chaaracter (anything other than numbers) for it */
2454 BEGIN
2455 IF (l_split_pctg_tbl(l_temp_index) IS NULL) THEN
2456 l_txn_amt := -1;
2457 ELSE
2458 l_txn_amt :=
2459 ROUND(
2460 NVL((l_txn_amt_tbl(l_temp_index) * l_split_pctg_tbl(l_temp_index)) / 100, 0)
2461 , 2
2462 );
2463 END IF;
2464 EXCEPTION
2465 WHEN VALUE_ERROR THEN
2466 l_txn_amt := -1;
2467 WHEN OTHERS THEN
2468 RAISE;
2469 END;
2470
2471 IF (
2472 (l_child_salesrep_id_tbl(i) = l_salesrep_id_tbl(l_temp_index))
2473 AND (l_child_txn_amt_tbl(i) = l_txn_amt)
2474 -- AND (l_child_role_id_tbl(i) = l_role_id_tbl(l_temp_index))
2475 AND (nvl(l_child_role_id_tbl(i),-1) = nvl(l_role_id_tbl(l_temp_index),-1)) -- Fix for bug 7298004
2476 AND (l_child_terr_id_tbl(i) = l_terr_id_tbl(l_temp_index))
2477 AND (l_child_split_pctg_tbl(i) = l_split_pctg_tbl(l_temp_index))
2478 AND (l_child_rev_type_tbl(i) = l_rev_type_tbl(l_temp_index))
2479 AND (l_del_flag_tbl(l_temp_index) = 'N')
2480 ) THEN
2481 /* if a match is found then exit the loop after marking the newly generated */
2482 /* credited txn not to be inserted in the api table */
2483 l_del_flag_tbl(l_temp_index) := 'Y';
2484 l_match_found := TRUE;
2485 EXIT;
2486 END IF;
2487 END LOOP;
2488
2489 IF (NOT l_match_found) THEN
2490 debugmsg
2491 (
2492 'SCA : Calling CN_SCATM_TAE_PUB.handle_unloaded_txns for child transaction : '
2493 || l_child_api_id_tbl(i)
2494 );
2495 handle_unloaded_txns(
2496 l_unloaded_txn_tbl
2497 , p_rowid => l_child_rowid_tbl(i)
2498 , p_update_flag => FALSE
2499 , errbuf => errbuf
2500 , retcode => retcode
2501 );
2502
2503 IF (retcode <> 0) THEN
2504 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_unloaded_txns has failed');
2505 RAISE fnd_api.g_exc_error;
2506 END IF;
2507
2508 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_unloaded_txns completed successfully');
2509 END IF;
2510 /* end of code : logic used here is similar to used for loaded tansactions. Reference bug 7589796 */
2511 END IF;
2512 ELSE
2513 /* if the child has been loaded for calculation */
2514 l_match_found := FALSE;
2515
2516 /* check to see if the child matches with any of the credited transaction */
2517 /* if so, donot obsolete the child instead donot insert the new credited txn generated */
2518 FOR j IN 1 .. l_no_of_credits_tbl(l_table_index) LOOP
2519 l_temp_index := l_table_index +(j - 1);
2520
2521 /* update txn amt to -1 if user either has not specified anything for split pctg */
2522 /* or has specified an invalid chaaracter (anything other than numbers) for it */
2523 BEGIN
2524 IF (l_split_pctg_tbl(l_temp_index) IS NULL) THEN
2525 l_txn_amt := -1;
2526 ELSE
2527 l_txn_amt :=
2528 ROUND(
2529 NVL((l_txn_amt_tbl(l_temp_index) * l_split_pctg_tbl(l_temp_index)) / 100, 0)
2530 , 2
2531 );
2532 END IF;
2533 EXCEPTION
2534 WHEN VALUE_ERROR THEN
2535 l_txn_amt := -1;
2536 WHEN OTHERS THEN
2537 RAISE;
2538 END;
2539
2540 IF (
2541 (l_child_salesrep_id_tbl(i) = l_salesrep_id_tbl(l_temp_index))
2542 AND (l_child_txn_amt_tbl(i) = l_txn_amt)
2543 -- AND (l_child_role_id_tbl(i) = l_role_id_tbl(l_temp_index))
2544 AND (nvl(l_child_role_id_tbl(i),-1) = nvl(l_role_id_tbl(l_temp_index),-1)) -- Fix for bug 7298004
2545 AND (l_child_terr_id_tbl(i) = l_terr_id_tbl(l_temp_index))
2546 AND (l_child_split_pctg_tbl(i) = l_split_pctg_tbl(l_temp_index))
2547 AND (l_child_rev_type_tbl(i) = l_rev_type_tbl(l_temp_index))
2548 AND (l_del_flag_tbl(l_temp_index) = 'N')
2549 ) THEN
2550 /* if a match is found then exit the loop after marking the newly generated */
2551 /* credited txn not to be inserted in the api table */
2552 l_del_flag_tbl(l_temp_index) := 'Y';
2553 l_match_found := TRUE;
2554 EXIT;
2555 END IF;
2556 END LOOP;
2557
2558 IF (NOT l_match_found) THEN
2559 /* if no match is found then create reversal entry */
2560 debugmsg
2561 (
2562 'SCA : Calling CN_SCATM_TAE_PUB.handle_loaded_txns for child transaction : '
2563 || l_child_api_id_tbl(i)
2564 );
2565 handle_loaded_txns(
2566 l_loaded_txn_rowid_tbl => l_loaded_txn_rowid_tbl
2567 , l_loaded_txn_comid_tbl => l_loaded_txn_comid_tbl
2568 , p_rowid => l_child_rowid_tbl(i)
2569 , p_api_id => l_child_api_id_tbl(i)
2570 , p_update_flag => FALSE
2571 , errbuf => errbuf
2572 , retcode => retcode
2573 );
2574
2575 IF (retcode <> 0) THEN
2576 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_loaded_txns has failed');
2577 RAISE fnd_api.g_exc_error;
2578 END IF;
2579
2580 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_loaded_txns completed successfully');
2581 END IF;
2582 END IF; /* end if load_status <> 'LOADED' */
2583 END LOOP; /* end processing all the children */
2584 END IF; /* end if l_child_rowid_tbl.COUNT > 0 */
2585
2586 /* increase the table index to point to the next transaction */
2587 l_table_index := l_table_index + l_no_of_credits_tbl(l_table_index);
2588 END LOOP; /* end loop processing winning records row by row */
2589
2590 /* Make changes to DB for unloaded txns present in the PL/SQL table */
2591 debugmsg('SCA : Calling CN_SCATM_TAE_PUB.handle_unloaded_txns');
2592 handle_unloaded_txns(l_unloaded_txn_tbl => l_unloaded_txn_tbl,
2593 p_rowid => NULL, p_update_flag => TRUE, errbuf => errbuf
2594 , retcode => retcode);
2595
2596 IF (retcode <> 0) THEN
2597 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_unloaded_txns has failed');
2598 RAISE fnd_api.g_exc_error;
2599 END IF;
2600
2601 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_unloaded_txns completed successfully');
2602 /* Make changes to DB for loaded txns present in the PL/SQL table */
2603 debugmsg('SCA : Calling CN_SCATM_TAE_PUB.handle_loaded_txns');
2604 handle_loaded_txns(
2605 l_loaded_txn_rowid_tbl => l_loaded_txn_rowid_tbl
2606 , l_loaded_txn_comid_tbl => l_loaded_txn_comid_tbl
2607 , p_rowid => NULL
2608 , p_api_id => NULL
2609 , p_update_flag => TRUE
2610 , errbuf => errbuf
2611 , retcode => retcode
2612 );
2613
2614 IF (retcode <> 0) THEN
2615 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_loaded_txns has failed');
2616 RAISE fnd_api.g_exc_error;
2617 END IF;
2618
2619 debugmsg('SCA : CN_SCATM_TAE_PUB.handle_loaded_txns completed successfully');
2620 /* insert the credited txns into api table */
2621 insert_api_txns(
2622 p_org_id => p_org_id
2623 , p_trans_object_id_tbl => l_api_id_tbl
2624 , p_salesrep_id_tbl => l_salesrep_id_tbl
2625 , p_emp_no_tbl => l_emp_no_tbl
2626 , p_role_id_tbl => l_role_id_tbl
2627 , p_split_pctg_tbl => l_split_pctg_tbl
2628 , p_rev_type_tbl => l_rev_type_tbl
2629 , p_terr_id_tbl => l_terr_id_tbl
2630 , p_terr_name_tbl => l_terr_name_tbl
2631 , p_del_flag_tbl => l_del_flag_tbl
2632 , errbuf => errbuf
2633 , retcode => retcode
2634 );
2635
2636 IF (retcode <> 0) THEN
2637 debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns has failed');
2638 RAISE fnd_api.g_exc_error;
2639 END IF;
2640
2641 debugmsg('SCA : CN_SCATM_TAE_PUB.insert_api_txns completed successfully');
2642 END LOOP; /* end loop fetch winning records */
2643
2644 CLOSE c_credited_txn_cur;
2645
2646 debugmsg('SCA : End of process_all_txns');
2647 EXCEPTION
2648 WHEN fnd_api.g_exc_error THEN
2649 IF c_credited_txn_cur%ISOPEN THEN
2650 CLOSE c_credited_txn_cur;
2651 END IF;
2652
2653 debugmsg('SCA : CN_SCATM_TAE_PUB.process_all_txns.g_exc_error');
2654 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2655 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2656 WHEN OTHERS THEN
2657 IF c_credited_txn_cur%ISOPEN THEN
2658 CLOSE c_credited_txn_cur;
2659 END IF;
2660
2661 debugmsg('SCA : Unexpected exception in process_all_txns');
2662 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2663 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2664 retcode := 2;
2665 errbuf := 'CN_SCATM_TAE_PUB.process_all_txns.others';
2666 END process_all_txns;
2667
2668 /* Entry point of credit allocation process */
2669 PROCEDURE get_assignments(
2670 errbuf OUT NOCOPY VARCHAR2
2671 , retcode OUT NOCOPY VARCHAR2
2672 , p_org_id IN NUMBER
2673 , p_start_date IN VARCHAR2
2674 , p_end_date IN VARCHAR2
2675 , p_run_mode IN VARCHAR2
2676 ) IS
2677 l_start_date DATE;
2678 l_end_date DATE;
2679 l_process_audit_id NUMBER;
2680 l_where_clause VARCHAR2(1000);
2681 l_skip_credit_flag VARCHAR2(1);
2682 l_count NUMBER;
2683 l_invalid_run_mode EXCEPTION;
2684 l_skip_crediting EXCEPTION;
2685 l_invalid_date_range EXCEPTION;
2686
2687 l_req_id NUMBER;
2688 l_num_workers NUMBER;
2689
2690 BEGIN
2691 retcode := 0;
2692 errbuf := NULL;
2693 /* Convert the dates for the varchar2 parameters passed in from concurrent program */
2694 l_start_date := fnd_date.canonical_to_date(p_start_date);
2695 l_end_date := fnd_date.canonical_to_date(p_end_date);
2696 /* Call begin_batch to get process_audit_id for debug log file */
2697 cn_message_pkg.begin_batch(
2698 x_process_type => 'SCATM'
2699 , x_parent_proc_audit_id => NULL
2700 , x_process_audit_id => l_process_audit_id
2701 , x_request_id => fnd_global.conc_request_id
2702 , p_org_id => p_org_id
2703 );
2704 debugmsg('SCA : Start of Credit Allocation using TM');
2705 debugmsg('SCA : process_audit_id is ' || l_process_audit_id);
2706 /* Continue only if the profile "OIC: Skip Credit Allocation" is set to No */
2707 l_skip_credit_flag := 'Y';
2708
2709 IF (fnd_profile.defined('CN_SKIP_CREDIT_ALLOCATION')) THEN
2710 l_skip_credit_flag := fnd_profile.VALUE('CN_SKIP_CREDIT_ALLOCATION');
2711 END IF;
2712
2713 IF (l_skip_credit_flag <> 'N') THEN
2714 debugmsg('SCA : Profile OIC: Skip Credit Allocation is set to Yes');
2715 retcode := 1;
2716 errbuf := 'SCA : Profile OIC: Skip Credit Allocation is set to Yes';
2717 RAISE l_skip_crediting;
2718 END IF;
2719
2720 /* run mode should be either NEW or ALL */
2721 IF ((p_run_mode <> 'NEW') AND(p_run_mode <> 'ALL')) THEN
2722 debugmsg('SCA : Invalid Run Mode');
2723 retcode := 2;
2724 errbuf := 'Inavlid Run Mode parameter value';
2725 RAISE l_invalid_run_mode;
2726 END IF;
2727
2728 /* Verify that the start date is within open period */
2729 l_count := 0;
2730
2731 SELECT COUNT(*)
2732 INTO l_count
2733 FROM cn_acc_period_statuses_v acc
2734 WHERE TRUNC(l_start_date) BETWEEN TRUNC(acc.start_date) AND TRUNC(acc.end_date)
2735 AND acc.period_status = 'O'
2736 AND acc.org_id = p_org_id
2737 AND ROWNUM = 1;
2738
2739 IF (l_count = 0) THEN
2740 debugmsg('SCA : Start Date is not within open period');
2741 retcode := 2;
2742 errbuf := 'Start Date is not within open period';
2743 RAISE l_invalid_date_range;
2744 END IF;
2745
2746 /* Verify that the end date is within open period */
2747 l_count := 0;
2748
2749 SELECT COUNT(*)
2750 INTO l_count
2751 FROM cn_acc_period_statuses_v acc
2752 WHERE TRUNC(l_end_date) BETWEEN TRUNC(acc.start_date) AND TRUNC(acc.end_date)
2753 AND acc.period_status = 'O'
2754 AND acc.org_id = p_org_id
2755 AND ROWNUM = 1;
2756
2757 IF (l_count = 0) THEN
2758 debugmsg('SCA : End Date is not within open period');
2759 retcode := 2;
2760 errbuf := 'End Date is not within open period';
2761 RAISE l_invalid_date_range;
2762 END IF;
2763
2764 /* Initialize global pl/sql tables */
2765 g_unloaded_txn_tbl := g_rowid_tbl_type();
2766 g_loaded_txn_rowid_tbl := g_rowid_tbl_type();
2767 g_loaded_txn_comid_tbl := g_comm_lines_api_id_tbl_type();
2768 /* Get the criterion to select transactions from api table */
2769 get_where_clause(
2770 p_start_date => l_start_date
2771 , p_end_date => l_end_date
2772 , p_org_id => p_org_id
2773 , p_run_mode => p_run_mode
2774 , x_where_clause => l_where_clause
2775 , errbuf => errbuf
2776 , retcode => retcode
2777 );
2778
2779 IF (retcode <> 0) THEN
2780 debugmsg('SCA : CN_SCATM_TAE_PUB.get_where_clause has failed');
2781 RAISE fnd_api.g_exc_error;
2782 END IF;
2783
2784 debugmsg('SCA : CN_SCATM_TAE_PUB.get_where_clause completed successfully');
2785 /* Call the territory APIs and get the winning salesreps */
2786 /* and split percentages for each transaction */
2787 get_credited_txns(
2788 p_where_clause => l_where_clause
2789 , p_request_id => g_request_id
2790 , errbuf => errbuf
2791 , retcode => retcode
2792 );
2793
2794 IF (retcode <> 0) THEN
2795 debugmsg('SCA : CN_SCATM_TAE_PUB.get_credited_txns has failed');
2796 RAISE fnd_api.g_exc_error;
2797 END IF;
2798
2799 debugmsg('SCA : CN_SCATM_TAE_PUB.get_credited_txns completed successfully');
2800
2801 SELECT TO_NUMBER(NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'),1)) INTO l_num_workers
2802 FROM dual;
2803
2804 IF l_num_workers < 1
2805 THEN
2806 l_num_workers := 1;
2807 ELSIF l_num_workers > 10
2808 THEN
2809 l_num_workers :=10;
2810 END IF;
2811
2812 debugmsg('SCA : CN_SCATM_TAE_PUB.Number of Workers '||l_num_workers);
2813
2814 FOR l_worker_id IN 1..l_num_workers
2815 LOOP
2816
2817 l_req_id := FND_REQUEST.SUBMIT_REQUEST('CN', -- Application
2818 'CN_SCATM_CRED_ALLOC_TXN_BATCH' , -- Concurrent Program
2819 '', -- description
2820 '', -- start time
2821 FALSE, -- sub request flag
2822 p_org_id, -- Parameters Org Id
2823 p_run_mode, --Parameter Run Mode
2824 l_worker_id -- parameter worker id
2825 );
2826
2827 END LOOP;
2828
2829
2830 -- IF (p_run_mode = 'NEW') THEN
2831 -- /* Process new and adjusted transactions */
2832 -- process_new_txns(p_org_id => p_org_id, errbuf => errbuf, retcode => retcode);
2833 --
2834 -- IF (retcode <> 0) THEN
2835 -- debugmsg('SCA : CN_SCATM_TAE_PUB.process_new_txns has failed');
2836 -- RAISE fnd_api.g_exc_error;
2837 -- END IF;
2838
2839 -- debugmsg('SCA : CN_SCATM_TAE_PUB.process_new_txns completed successfully');
2840 -- ELSIF(p_run_mode = 'ALL') THEN
2841 -- /* Process all transactions */
2842 -- process_all_txns(p_org_id => p_org_id, errbuf => errbuf, retcode => retcode);
2843 --
2844 -- IF (retcode <> 0) THEN
2845 -- debugmsg('SCA : CN_SCATM_TAE_PUB.process_all_txns has failed');
2846 -- RAISE fnd_api.g_exc_error;
2847 -- END IF;
2848 --
2849 -- debugmsg('SCA : CN_SCATM_TAE_PUB.process_all_txns completed successfully');
2850 -- END IF;
2851
2852 /* update the txns processed in api table */
2853 -- update_txns_processed(errbuf => errbuf, retcode => retcode);
2854
2855 IF (retcode <> 0) THEN
2856 debugmsg('SCA : CN_SCATM_TAE_PUB.update_txns_processed has failed');
2857 RAISE fnd_api.g_exc_error;
2858 END IF;
2859
2860 debugmsg('SCA : CN_SCATM_TAE_PUB.update_txns_processed completed successfully');
2861 COMMIT;
2862 -- Call end_batch to end debug log file
2863 debugmsg('SCA : End of SCATM');
2864 cn_message_pkg.end_batch(l_process_audit_id);
2865 EXCEPTION
2866 WHEN l_invalid_date_range THEN
2867 debugmsg('SCA : CN_SCATM_TAE_PUB.get_assignments.l_invalid_date_range');
2868 debugmsg('SCA : End of SCATM');
2869 cn_message_pkg.end_batch(l_process_audit_id);
2870 WHEN l_skip_crediting THEN
2871 debugmsg('SCA : CN_SCATM_TAE_PUB.get_assignments.l_skip_crediting');
2872 debugmsg('SCA : End of SCATM');
2873 cn_message_pkg.end_batch(l_process_audit_id);
2874 WHEN l_invalid_run_mode THEN
2875 debugmsg('SCA : CN_SCATM_TAE_PUB.get_assignments.l_invalid_run_mode');
2876 debugmsg('SCA : End of SCATM');
2877 cn_message_pkg.end_batch(l_process_audit_id);
2878 WHEN fnd_api.g_exc_error THEN
2879 debugmsg('SCA : CN_SCATM_TAE_PUB.get_assignments.g_exc_error');
2880 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2881 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2882 debugmsg('SCA : End of SCATM');
2883 cn_message_pkg.end_batch(l_process_audit_id);
2884 WHEN OTHERS THEN
2885 debugmsg('SCA : Unexpected exception');
2886 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2887 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2888 -- Call end_batch to end debug log file
2889 debugmsg('SCA : End of SCATM');
2890 cn_message_pkg.end_batch(l_process_audit_id);
2891 retcode := 2;
2892 errbuf := 'Unexpected Error : ' || SQLERRM;
2893 END get_assignments;
2894
2895 PROCEDURE batch_process_txns(
2896 errbuf OUT NOCOPY VARCHAR2
2897 ,retcode OUT NOCOPY VARCHAR2
2898 ,p_org_id NUMBER
2899 ,p_run_mode VARCHAR2
2900 ,p_worker_id NUMBER)
2901 IS
2902
2903 BEGIN
2904
2905 IF (p_run_mode = 'NEW') THEN
2906 /* Process new and adjusted transactions */
2907 process_new_txns(p_org_id => p_org_id, p_worker_id => p_worker_id,
2908 errbuf => errbuf, retcode => retcode);
2909
2910
2911 IF (retcode <> 0) THEN
2912 debugmsg('SCA : CN_SCATM_TAE_PUB.process_new_txns has failed');
2913 RAISE fnd_api.g_exc_error;
2914 END IF;
2915
2916
2917 debugmsg('SCA : CN_SCATM_TAE_PUB.process_new_txns completed successfully');
2918 ELSIF(p_run_mode = 'ALL') THEN
2919 /* Process all transactions */
2920 process_all_txns(p_org_id => p_org_id, p_worker_id => p_worker_id,
2921 errbuf => errbuf, retcode => retcode);
2922
2923
2924 IF (retcode <> 0) THEN
2925 debugmsg('SCA : CN_SCATM_TAE_PUB.process_all_txns has failed');
2926 RAISE fnd_api.g_exc_error;
2927 END IF;
2928
2929 debugmsg('SCA : CN_SCATM_TAE_PUB.process_all_txns completed successfully');
2930 END IF;
2931
2932 update_txns_processed(errbuf => errbuf, retcode => retcode,
2933 p_worker_id => p_worker_id);
2934
2935 COMMIT;
2936
2937 EXCEPTION
2938 WHEN fnd_api.g_exc_error THEN
2939 debugmsg('SCA : CN_SCATM_TAE_PUB.get_assignments.g_exc_error');
2940 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2941 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2942 debugmsg('SCA : End of SCATM');
2943 -- cn_message_pkg.end_batch(l_process_audit_id);
2944 WHEN OTHERS THEN
2945 debugmsg('SCA : Unexpected exception');
2946 debugmsg('SCA : SQLCODE : ' || SQLCODE);
2947 debugmsg('SCA : SQLERRM : ' || SQLERRM);
2948 -- Call end_batch to end debug log file
2949 debugmsg('SCA : End of SCATM');
2950 --cn_message_pkg.end_batch(l_process_audit_id);
2951 retcode := 2;
2952 errbuf := 'Unexpected Error : ' || SQLERRM;
2953
2954 END batch_process_txns;
2955
2956 END cn_sca_credits_batch_pub;