[Home] [Help]
PACKAGE BODY: APPS.CN_SCA_TRX_PROC_PVT
Source
1 PACKAGE BODY CN_SCA_TRX_PROC_PVT AS
2 -- $Header: cnvstrpb.pls 120.7.12010000.2 2009/01/29 06:44:42 gmarwah ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8
9
10 -- Package Name
11 -- cn_sca_trx_proc_pvt
12 -- Purpose
13 -- Procedures TO populate transactions from CN_COMM_LINES_API into SCA interface tables and
14 -- transfer results back to CN_COMM_LINES_API from SCA output tables after credit allocation
15 -- History
16 -- 06/02/03 Mike Ting Created
17 -- Nov 17, 2005 vensrini add call to fnd_request.set_org_id to call_populate_resuts
18 -- before calling fnd_request.submit_request
19 -- Mar 31, 2005 vensrini Bug fix 5125980
20 --
21
22
23 -- Global Variable
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SCA_TRX_PROC_PVT';
25
26 no_trx_lines EXCEPTION;
27 no_trx EXCEPTION;
28 conc_fail EXCEPTION;
29 api_call_failed EXCEPTION;
30 fail_populate EXCEPTION;
31 g_cn_debug VARCHAR2(1) := fnd_profile.value('CN_DEBUG');
32
33 FUNCTION get_adjusted_by
34 RETURN VARCHAR2 IS
35 l_adjusted_by VARCHAR2(100) := '0';
36 BEGIN
37 SELECT user_name
38 INTO l_adjusted_by
39 FROM fnd_user
40 WHERE user_id = fnd_profile.value('USER_ID');
41 RETURN l_adjusted_by;
42 EXCEPTION
43 WHEN OTHERS THEN
44 RETURN l_adjusted_by;
45 END;
46
47 -- Local Procedure for showing debug msg
48
49 PROCEDURE debugmsg(msg VARCHAR2) IS
50 BEGIN
51
52 IF g_cn_debug = 'Y' THEN
53 cn_message_pkg.debug(substr(msg,1,254));
54 fnd_file.put_line(fnd_file.Log, msg); -- Bug fix 5125980
55 END IF;
56 -- comment out dbms_output before checking in file
57 -- dbms_output.put_line(substr(msg,1,254));
58 END debugmsg;
59
60 -- Procedure for submitting concurrent requests
61
62 PROCEDURE Conc_Submit(x_conc_program VARCHAR2
63 ,x_parent_proc_audit_id NUMBER
64 ,x_process VARCHAR2
65 ,x_physical_batch_id NUMBER
66 ,x_start_date DATE
67 ,x_end_date DATE
68 ,x_request_id IN OUT NOCOPY NUMBER) IS
69
70 l_org_id cn_sca_process_batches.org_id%TYPE;
71
72 BEGIN
73
74 debugmsg('Conc_Submit : x_process = '|| x_process);
75 debugmsg('Conc_Submit : x_start_date = '|| x_start_date);
76 debugmsg('Conc_Submit : x_end_date = '|| x_end_date);
77 debugmsg('Conc_Submit : x_physical_batch_id = '|| x_physical_batch_id);
78
79 SELECT org_id
80 INTO l_org_id
81 FROM cn_sca_process_batches
82 WHERE sca_process_batch_id = x_physical_batch_id;
83
84 fnd_request.set_org_id( l_org_id );
85
86 x_request_id := fnd_request.submit_request
87 (
88 application => 'CN'
89 ,program => x_conc_program
90 ,argument1 => x_parent_proc_audit_id
91 ,argument2 => x_process
92 ,argument3 => x_physical_batch_id
93 ,argument4 => x_start_date
94 ,argument5 => x_end_date
95 ,argument6 => l_org_id);
96
97 debugmsg('Conc_Submit : x_request_id = ' || x_request_id);
98
99 IF x_request_id = 0 THEN
100 debugmsg('Loader : Conc_Submit : Submit failure for phys batch '
101 || x_physical_batch_id);
102 debugmsg('Loader : Conc_Submit: ' || fnd_message.get);
103 debugmsg('Loader : Conc_Submit : Submit failure for phys batch '
104 || x_physical_batch_id);
105 ELSE
106 cn_message_pkg.flush;
107 commit; -- Commit for each concurrent program i.e. runner
108 END IF;
109
110 END conc_submit;
111
112
113 -- Procedure for submitting one request for each physical batch within a logical batch
114
115 PROCEDURE conc_dispatch(x_parent_proc_audit_id NUMBER,
116 x_start_date DATE,
117 x_end_date DATE,
118 x_logical_batch_id NUMBER,
119 x_process VARCHAR2
120 ) IS
121
122 TYPE requests IS TABLE of NUMBER(15) INDEX BY BINARY_INTEGER;
123 TYPE batches IS TABLE of NUMBER(15) INDEX BY BINARY_INTEGER;
124
125 l_primary_request_stack REQUESTS;
126 l_primary_batch_stack BATCHES;
127 l_empty_request_stack REQUESTS;
128 l_empty_batch_stack BATCHES;
129
130 x_batch_total NUMBER := 0;
131 l_temp_id NUMBER := 0;
132 l_temp_phys_batch_id NUMBER;
133
134 primary_ptr NUMBER := 1; -- Must start at 1
135
136 l_dev_phase VARCHAR2(80);
137 l_dev_status VARCHAR2(80);
138 l_request_id NUMBER ;
139
140 l_completed_batch_count NUMBER :=0 ;
141 l_call_status BOOLEAN ;
142
143 l_next_process VARCHAR2(30);
144 l_dummy VARCHAR2(500);
145 unfinished BOOLEAN := TRUE;
146
147 l_user_id NUMBER(15) := fnd_global.user_id;
148 l_resp_id NUMBER(15) := fnd_global.resp_id;
149 l_login_id NUMBER(15) := fnd_global.login_id;
150 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
151 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
152 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
153
154 x_debug number;
155 debug_v number;
156
157 conc_status boolean;
158
159 l_sleep_time number := 180;
160 l_sleep_time_char varchar2(30);
161
162 -- Get individual physical batch id's for the entire logical batch
163 CURSOR physical_batches IS
164
165 SELECT distinct sca_process_batch_id
166 FROM cn_sca_process_batches
167 WHERE logical_batch_id = x_logical_batch_id;
168
169 physical_rec physical_batches%ROWTYPE;
170
171 BEGIN
172
173 debugmsg('Conc_Dispatch : Start of Conc_Dispatch');
174 debugmsg('Conc_Dispatch : Logical Batch ID = '|| x_logical_batch_id);
175
176 WHILE unfinished LOOP
177 l_primary_request_stack := l_empty_request_stack;
178 l_primary_batch_stack := l_empty_batch_stack ;
179
180 primary_ptr := 1; -- Start at element one not element zero
181 l_completed_batch_count := 0;
182 x_batch_total := 0;
183
184 FOR physical_rec IN physical_batches LOOP
185 debugmsg('Conc_Dispatch : Calling conc_submit. '
186 ||'physical_rec.sca_process_batch_id = '
187 || physical_rec.sca_process_batch_id );
188
189 debugmsg('Conc_Dispatch : call SCA_BATCH_RUNNER');
190
191 conc_submit(x_conc_program => 'SCA_BATCH_RUNNER'
192 ,x_parent_proc_audit_id => x_parent_proc_audit_id
193 ,x_process => x_process
194 ,x_physical_batch_id => physical_rec.sca_process_batch_id
195 ,x_start_date => x_start_date
196 ,x_end_date => x_end_date
197 ,x_request_id => l_temp_id);
198
199 debugmsg('Conc_Dispatch : done SCA_BATCH_RUNNER');
200
201 x_batch_total := x_batch_total + 1;
202
203 l_primary_request_stack(x_batch_total) := l_temp_id;
204 l_primary_batch_stack(x_batch_total) := physical_rec.sca_process_batch_id;
205
206 -- If submission failed update the batch record and bail
207
208 IF l_temp_id = 0 THEN
209 --cn_debug.print_msg('conc disp submit failed',1);
210 l_temp_phys_batch_id := physical_rec.sca_process_batch_id;
211 RAISE conc_fail;
212 END IF;
213
214 END LOOP;
215
216 debugmsg('Conc_Dispatch : Total conc requests submitted : '||x_batch_total);
217
218 debug_v := l_primary_request_stack(primary_ptr);
219
220 l_sleep_time_char := fnd_profile.value('CN_SLEEP_TIME');
221
222 IF l_sleep_time_char IS NOT NULL THEN
223 l_sleep_time := to_number(l_sleep_time_char);
224 END IF;
225
226 dbms_lock.sleep(l_sleep_time);
227
228 WHILE l_completed_batch_count <= x_batch_total LOOP
229
230 IF l_primary_request_stack(primary_ptr) IS NOT NULL THEN
231
232 l_call_status := fnd_concurrent.get_request_status(
233 request_id => l_primary_request_stack(primary_ptr)
234 ,phase => l_dummy
235 ,status => l_dummy
236 ,dev_phase => l_dev_phase
237 ,dev_status => l_dev_status
238 ,message => l_dummy);
239
240 IF (NOT l_call_status) THEN
241
242 debugmsg('Conc_Dispatch : request_id is '
243 ||l_primary_request_stack(primary_ptr));
244 RAISE conc_fail;
245 END IF;
246
247 IF l_dev_phase = 'COMPLETE' THEN
248
249 debug_v := l_primary_request_stack(primary_ptr);
250 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
251
252 l_primary_batch_stack(primary_ptr) := null;
253 l_primary_request_stack(primary_ptr) := null;
254 l_completed_batch_count := l_completed_batch_count +1;
255
256 IF l_dev_status = 'ERROR' THEN
257 debugmsg('Conc_Dispatch : '
258 ||'Request completed with error for '
259 ||debug_v);
260 raise conc_fail;
261
262 ELSIF l_dev_status = 'NORMAL' THEN
263 x_debug := l_primary_batch_stack(primary_ptr);
264 END IF; -- If error
265 END IF; -- If complete
266 END IF; -- If null ptr
267
268 primary_ptr := primary_ptr+1;
269
270 IF l_completed_batch_count = x_batch_total THEN
271
272 -- Get out of the loop by adding 1
273 l_completed_batch_count := l_completed_batch_count + 1;
274
275 debugmsg('Conc_Dispatch : All requests complete for '||
276 'logical process : '|| x_process);
277 unfinished := FALSE;
278
279 ELSE
280 -- Made a complete pass through the srp_periods in this physical
281 -- batch and some conc requests have not completed.
282 -- Give the conc requests a few minutes to run before
283 -- checking their status
284 IF primary_ptr > x_batch_total THEN
285 dbms_lock.sleep(l_sleep_time);
286 primary_ptr := 1;
287 END IF;
288 END IF;
289 END LOOP;
290 END LOOP;
291 EXCEPTION
292 WHEN no_data_found THEN
293 debugmsg('Conc_Dispatch : no rows for process '
294 ||x_process);
295 -- conc_status := fnd_concurrent.set_completion_status(
296 -- status => 'ERROR',
297 -- message => '');
298 -- cn_message_pkg.end_batch(x_parent_proc_audit_id);
299 WHEN conc_fail THEN
300 -- update_error(l_temp_phys_batch_id);
301 debugmsg('Conc_Dispatch : Exception conc_fail');
302 -- cn_message_pkg.end_batch(x_parent_proc_audit_id);
303 conc_status := fnd_concurrent.set_completion_status(
304 status => 'ERROR',
305 message => '');
306 RAISE;
307 WHEN OTHERS THEN
308 debugmsg('Conc_Dispatch : Unexpected Exception');
309 RAISE;
310 END conc_dispatch;
311
312
313 PROCEDURE ASSIGN(p_logical_batch_id NUMBER,
314 p_start_date DATE,
315 p_end_date DATE,
316 batch_type VARCHAR2,
317 p_org_id NUMBER,
318 x_size OUT NOCOPY NUMBER) IS
319
320
321 l_sql_stmt VARCHAR2(10000);
322 l_sql_stmt_count VARCHAR2(10000);
323 l_sql_stmt_id VARCHAR2(10000);
324 l_sql_stmt_divider VARCHAR2(10000);
325 l_sql_stmt_resource VARCHAR2(10000);
326 l_no_trx BOOLEAN;
327 l_sca_process_batch_id cn_sca_process_batches.sca_process_batch_id%TYPE;
328
329 TYPE rc IS REF CURSOR;
330 TYPE divider_type IS TABLE OF NUMBER;
331
332 query_cur rc;
333 i NUMBER;
334 l_header_rec cn_comm_lines_api%ROWTYPE;
335 l_lines_output_id cn_sca_lines_output.sca_lines_output_id%TYPE;
336 l_header_interface_id cn_sca_headers_interface.sca_headers_interface_id%TYPE;
337 l_comm_lines_api_id cn_comm_lines_api.comm_lines_api_id%TYPE;
338 l_source_id cn_sca_headers_interface.source_id%TYPE;
339 l_order_number cn_comm_lines_api.order_number%TYPE;
340 l_invoice_number cn_comm_lines_api.invoice_number%TYPE;
341 l_id cn_sca_process_batches.start_id%TYPE;
342
343 l_logical_batch_size NUMBER;
344 l_worker_num NUMBER;
345 l_physical_batch_size NUMBER;
346 l_divider_size NUMBER;
347 divider divider_type := divider_type();
348 loop_count NUMBER;
349 l_start_id cn_sca_process_batches.start_id%TYPE;
350 l_end_id cn_sca_process_batches.end_id%TYPE;
351
352 l_user_id NUMBER(15) := fnd_global.user_id;
353 l_login_id NUMBER(15) := fnd_global.login_id;
354
355 BEGIN
356
357 debugmsg(batch_type || ': Assign : Start ');
358 debugmsg(batch_type || ': Assign : Start Date = ' || p_start_date);
359 debugmsg(batch_type || ': Assign : End Date = ' || p_end_date);
360
361 -- Get the number of transactions that needs to be processed, i.e. the logical batch size
362
363 if (batch_type = 'SCA_ORD') then
364
365 l_sql_stmt_count :=
366
367 'select count(distinct(source_id)) from cn_sca_headers_interface ';
368
369 l_sql_stmt :=
370
371 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
372 'and ((transaction_status is null) or (transaction_status not in ''ADJUSTED'')) ' ||
373 'and source_type = :p_source_type ' ||
374 'order by source_id asc ';
375
376
377 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
378
379 end if;
380
381 if (batch_type = 'SCA_INV') then
382
383 l_sql_stmt_count :=
384
385 'select count(distinct(source_id)) from cn_sca_headers_interface ';
386
387 l_sql_stmt :=
388
389 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
390 'and ((transaction_status is null) or (transaction_status not in ''ADJUSTED'')) ' ||
391 'and source_type = :p_source_type ' ||
392 'order by source_id asc ';
393
394 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
395
396 end if;
397
398
399 if (batch_type = 'ORD') then
400
401 l_sql_stmt_count :=
402
403 'select count(distinct(order_number)) from cn_comm_lines_api ';
404
405 l_sql_stmt :=
406
407 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
408 'and load_status = ''UNLOADED'' ' ||
409 'and ( --(adjust_status is null) or
410 (adjust_status not in (''SCA_PENDING'', ''SCA_ALLOCATED'', ''SCA_NO_RULE'', ''SCA_NOT_ALLOCATED'', ' ||
411 '''SCA_NOT_ELIGIBLE'', ''REVERSAL'', ''FROZEN''))) ' ||
412 'and ((trx_type = ''ORD'') or (trx_type = ''MAN''))' ||
413 'and order_number is not null ' ||
414 'and line_number is not null ' ||
415 'and invoice_number is null ' ||
416 'order by order_number asc ';
417
418 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
419
420 /*dbms_output.put_line(substr(l_sql_stmt_count, 1, 100));
421 dbms_output.put_line(substr(l_sql_stmt_count, 101, 150));
422 dbms_output.put_line(substr(l_sql_stmt_count, 151, 200));
423 dbms_output.put_line(substr(l_sql_stmt_count, 201, 250));
424 dbms_output.put_line(' ');
425 */
426 end if;
427
428 if (batch_type = 'INV') then
429
430 l_sql_stmt_count :=
431
432 'select count(distinct(invoice_number)) from cn_comm_lines_api ';
433
434 l_sql_stmt :=
435
436 'where trunc(processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
437 'and load_status = ''UNLOADED'' ' ||
438 'and (--(adjust_status is null) or
439 (adjust_status not in (''SCA_PENDING'', ''SCA_ALLOCATED'', ''SCA_NO_RULE'', ''SCA_NOT_ALLOCATED'', ' ||
440 '''SCA_NOT_ELIGIBLE'', ''REVERSAL'', ''FROZEN''))) ' ||
441 'and ((trx_type = ''INV'') or (trx_type = ''MAN''))' ||
442 'and invoice_number is not null ' ||
443 'and line_number is not null ' ||
444 'order by invoice_number asc ';
445
446 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
447
448 end if;
449
450 if (batch_type = 'CSHI') then
451
452 l_sql_stmt_count :=
453
454 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
455
456 l_sql_stmt :=
457
458 'where trunc(CSHI.processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
459 'and CSHI.process_status <> ''SCA_UNPROCESSED'' ' ||
460 'and CSHI.transaction_status = ''SCA_UNPROCESSED'' ' ||
461 'and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id ' ||
462 'order by CSLI.sca_lines_interface_id ';
463
464 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
465
466 end if;
467
468 if (batch_type = 'CSLO') then
469
470 l_sql_stmt_count :=
471
472 'select count(1) from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
473
474 l_sql_stmt :=
475
476 'where trunc(CSHI.processed_date) between trunc(:p_start_date) and trunc(:p_end_date) ' ||
477 'and CSHI.sca_headers_interface_id = CSLO.sca_headers_interface_id ' ||
478 'and CSHI.process_status <> ''SCA_UNPROCESSED'' ' ||
479 'and CSHI.transaction_status = ''SCA_UNPROCESSED'' ' ||
480 'order by CSLO.sca_lines_output_id ' ;
481
482 l_sql_stmt_count := l_sql_stmt_count || l_sql_stmt;
483
484 end if;
485
486 if (batch_type = 'SCA_ORD') then
487
488 OPEN query_cur FOR l_sql_stmt_count
489 USING
490 p_start_date,
491 p_end_date,
492 'ORD';
493
494 elsif (batch_type = 'SCA_INV') then
495
496 OPEN query_cur FOR l_sql_stmt_count
497 USING
498 p_start_date,
499 p_end_date,
500 'INV';
501
502 else
503
504 OPEN query_cur FOR l_sql_stmt_count
505 USING
506 p_start_date,
507 p_end_date;
508
509 end if;
510
511 FETCH query_cur INTO l_logical_batch_size;
512 x_size := l_logical_batch_size;
513
514 l_worker_num := NVL(fnd_profile.value('CN_NUMBER_OF_WORKERS'), 1);
515
516 if (l_worker_num < 1) then
517
518 l_worker_num := 1;
519
520 end if;
521
522 debugmsg(batch_type || ': Assign : Logical Batch Size = ' || to_char(l_logical_batch_size));
523 debugmsg(batch_type || ': Assign : Number of Workers = ' || to_char(l_worker_num));
524
525 -- calculate the minimas and maximas of the physical batches
526
527 if (l_logical_batch_size > l_worker_num) then
528 l_physical_batch_size := floor(l_logical_batch_size/l_worker_num);
529 l_divider_size := l_worker_num * 2;
530
531 divider.EXTEND;
532 divider(1):= 1;
533 divider.EXTEND;
534 divider(2):= divider(1) + l_physical_batch_size - 1;
535
536 debugmsg(batch_type || ': Assign : Minima1 = ' || to_char(divider(1)));
537 debugmsg(batch_type || ': Assign : Maxima1 = ' || to_char(divider(2)));
538
539 for counter in 2..l_worker_num LOOP
540
541 divider.EXTEND;
542 divider(2*counter-1) := divider(2*counter-2) + 1;
543 divider.EXTEND;
544 divider(2*counter) := divider(2*counter-1) + l_physical_batch_size - 1;
545
546 debugmsg(batch_type || ': Assign : Minima' || counter || ' = ' || to_char(divider(2*counter-1)));
547
548 if (counter <> l_worker_num) then
549 debugmsg(batch_type || ': Assign : Maxima' || counter || ' = ' || to_char(divider(2*counter)));
550 end if;
551
552 END LOOP;
553
554 divider(l_divider_size) := l_logical_batch_size;
555 debugmsg(batch_type || ': Assign : Maxima' || l_worker_num || ' = ' || to_char(divider(l_divider_size)));
556
557 else
558
559 l_physical_batch_size := 0;
560
561 for counter in 1..l_logical_batch_size LOOP
562
563 divider.EXTEND;
564 divider(2*counter-1) := counter;
565 divider.EXTEND;
566 divider(2*counter) := counter;
567
568 debugmsg(batch_type || ': Assign : Minima' || counter || ' = ' || to_char(divider(2*counter-1)));
569 debugmsg(batch_type || ': Assign : Maxima' || counter || ' = ' || to_char(divider(2*counter)));
570
571 END LOOP;
572
573 end if;
574
575 if (divider.count = 0) then
576
577 l_no_trx := true;
578 RAISE no_trx;
579
580 else
581
582 l_no_trx := false;
583 l_sql_stmt_divider :=
584 '(''' || divider(divider.FIRST) || '''';
585
586 i := divider.NEXT(divider.FIRST);
587
588 while i IS NOT NULL LOOP
589
590 l_sql_stmt_divider := l_sql_stmt_divider || ', ''' || divider(i) || '''';
591 i := divider.NEXT(i);
592
593 END LOOP;
594
595 l_sql_stmt_divider := l_sql_stmt_divider || ')';
596
597 end if;
598
599 if (not l_no_trx) then
600
601 if ((batch_type = 'SCA_ORD') or (batch_type = 'SCA_INV')) then
602
603 l_sql_stmt_id :=
604 'select distinct(source_id) from cn_sca_headers_interface ';
605
606 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
607
608 l_sql_stmt_id :=
609
610 'select source_id from ' ||
611 '(select rownum row_number, source_id from ' ||
612 '(' || l_sql_stmt_id || ')) sca_table ' ||
613 'where sca_table.row_number in ' ||
614 l_sql_stmt_divider;
615
616 end if;
617
618 if (batch_type = 'ORD') then
619
620 l_sql_stmt_id := 'select distinct(order_number) from cn_comm_lines_api ';
621 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
622
623 l_sql_stmt_id :=
624
625 'select order_number from ' ||
626 '(select rownum row_number, order_number from ' ||
627 '(' || l_sql_stmt_id || ')) api_ord_table ' ||
628 'where api_ord_table.row_number in ' ||
629 l_sql_stmt_divider;
630
631 end if;
632
633 if (batch_type = 'INV') then
634
635 l_sql_stmt_id := 'select distinct(invoice_number) from cn_comm_lines_api ';
636 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
637
638 l_sql_stmt_id :=
639
640 'select invoice_number from ' ||
641 '(select rownum row_number, invoice_number from ' ||
642 '(' || l_sql_stmt_id || ')) api_inv_table ' ||
643 'where api_inv_table.row_number in ' ||
644 l_sql_stmt_divider;
645
646 END IF;
647
648 if (batch_type = 'CSHI') then
649
650 l_sql_stmt_id := 'select CSLI.sca_lines_interface_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI ';
651 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
652
653 l_sql_stmt_id :=
654
655 'select sca_lines_interface_id from ' ||
656 '(select rownum row_number, sca_lines_interface_id from ' ||
657 '(' || l_sql_stmt_id || ')) sca_lines_table ' ||
658 'where sca_lines_table.row_number in '||
659 l_sql_stmt_divider;
660
661 end if;
662
663 if (batch_type = 'CSLO') then
664
665 l_sql_stmt_id := 'select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO ';
666 l_sql_stmt_id := l_sql_stmt_id || l_sql_stmt;
667
668 l_sql_stmt_id :=
669
670 'select sca_lines_output_id from ' ||
671 '(select rownum row_number, sca_lines_output_id from ' ||
672 '(' || l_sql_stmt_id || ')) sca_output_table ' ||
673 'where sca_output_table.row_number in ' ||
674 l_sql_stmt_divider;
675
676 end if;
677
678 if (batch_type = 'SCA_ORD') then
679
680 OPEN query_cur FOR l_sql_stmt_id
681 USING
682 p_start_date,
683 p_end_date,
684 'ORD';
685
686 elsif (batch_type = 'SCA_INV') then
687
688 OPEN query_cur FOR l_sql_stmt_id
689 USING
690 p_start_date,
691 p_end_date,
692 'INV';
693
694 else
695
696 OPEN query_cur FOR l_sql_stmt_id
697 USING
698 p_start_date,
699 p_end_date;
700
701 end if;
702
703 loop_count := 1;
704
705 debugmsg(batch_type || ': Assign : Insert into CN_SCA_PROCESS_BATCHES ');
706
707 if (l_physical_batch_size >= 2) then
708
709 LOOP
710 FETCH query_cur INTO l_id;
711 EXIT WHEN query_cur%NOTFOUND;
712
713 if ((loop_count mod 2) = 1) then
714 l_start_id := l_id;
715 end if;
716
717 if ((loop_count mod 2) = 0) then
718 l_end_id := l_id;
719
720 SELECT cn_sca_process_batches_s.NEXTVAL
721 INTO l_sca_process_batch_id
722 FROM sys.dual;
723
724 insert into CN_SCA_PROCESS_BATCHES
725 ( sca_process_batch_id,
726 start_id,
727 end_id,
728 type,
729 logical_batch_id,
730 creation_date,
731 created_by,
732 last_update_date,
733 last_updated_by,
734 last_update_login,
735 org_id)
736 values
737 ( l_sca_process_batch_id,
738 l_start_id,
739 l_end_id,
740 batch_type,
741 p_logical_batch_id,
742 sysdate,
743 l_user_id,
744 sysdate,
745 l_user_id,
746 l_login_id,
747 p_org_id);
748
749 debugmsg(batch_type || ': Assign : sca_process_batch_id = ' || to_char(l_sca_process_batch_id));
750 debugmsg(batch_type || ': Assign : start_id = ' || l_start_id);
751 debugmsg(batch_type || ': Assign : end_id = ' || l_end_id);
752 debugmsg(batch_type || ': Assign : logical_batch_id = ' || to_char(p_logical_batch_id));
753 debugmsg(batch_type || ': Assign : batch_type = ' || batch_type);
754
755 end if;
756
757 loop_count := loop_count + 1;
758
759 END LOOP;
760
761 else
762
763 LOOP
764 FETCH query_cur INTO l_id;
765 EXIT WHEN query_cur%NOTFOUND;
766
767 if (loop_count = l_worker_num and l_physical_batch_size = 1) then
768 l_start_id := l_id;
769 end if;
770
771 if (loop_count > l_worker_num and l_physical_batch_size = 1) then
772 l_end_id := l_id;
773
774 SELECT cn_sca_process_batches_s.NEXTVAL
775 INTO l_sca_process_batch_id
776 FROM sys.dual;
777
778 insert into CN_SCA_PROCESS_BATCHES
779 ( sca_process_batch_id,
780 start_id,
781 end_id,
782 type,
783 logical_batch_id,
784 creation_date,
785 created_by,
786 last_update_date,
787 last_updated_by,
788 last_update_login,
789 org_id)
790 values
791 ( l_sca_process_batch_id,
792 l_start_id,
793 l_end_id,
794 batch_type,
795 p_logical_batch_id,
796 sysdate,
797 l_user_id,
798 sysdate,
799 l_user_id,
800 l_login_id,
801 p_org_id);
802
803 debugmsg(batch_type || ': Assign : sca_process_batch_id = ' || to_char(l_sca_process_batch_id));
804 debugmsg(batch_type || ': Assign : start_id = ' || l_start_id);
805 debugmsg(batch_type || ': Assign : end_id = ' || l_end_id);
806 debugmsg(batch_type || ': Assign : logical_batch_id = ' || to_char(p_logical_batch_id));
807 debugmsg(batch_type || ': Assign : batch_type = ' || batch_type);
808
809 end if;
810
811
812 if (loop_count < l_worker_num or (loop_count = l_worker_num and l_physical_batch_size < 1)) then
813
814 SELECT cn_sca_process_batches_s.NEXTVAL
815 INTO l_sca_process_batch_id
816 FROM sys.dual;
817
818 insert into CN_SCA_PROCESS_BATCHES
819 ( sca_process_batch_id,
820 start_id,
821 end_id,
822 type,
823 logical_batch_id,
824 creation_date,
825 created_by,
826 last_update_date,
827 last_updated_by,
828 last_update_login,
829 org_id)
830 values
831 ( l_sca_process_batch_id,
832 l_id,
833 l_id,
834 batch_type,
835 p_logical_batch_id,
836 sysdate,
837 l_user_id,
838 sysdate,
839 l_user_id,
840 l_login_id,
841 p_org_id);
842
843 debugmsg(batch_type || ': Assign : sca_process_batch_id = ' || to_char(l_sca_process_batch_id));
844 debugmsg(batch_type || ': Assign : start_id = ' || l_id);
845 debugmsg(batch_type || ': Assign : end_id = ' || l_id);
846 debugmsg(batch_type || ': Assign : logical_batch_id = ' || to_char(p_logical_batch_id));
847 debugmsg(batch_type || ': Assign : batch_type = ' || batch_type);
848
849 end if;
850
851 loop_count := loop_count + 1;
852
853 END LOOP;
854
855 end if;
856
857 end if;
858
859 EXCEPTION
860 WHEN no_trx THEN
861 debugmsg(batch_type || ': Assign : No transactions to process ');
862
863 WHEN OTHERS THEN
864 debugmsg(batch_type || ': Assign : Unexpected Exception');
865 RAISE;
866
867 END assign;
868
869 PROCEDURE create_trx (
870 p_start_date DATE,
871 p_end_date DATE,
872 p_physical_batch_id NUMBER) IS
873
874 l_start_id cn_sca_process_batches.start_id%TYPE;
875 l_end_id cn_sca_process_batches.end_id%TYPE;
876 l_adjusted_by VARCHAR2(30);
877 conc_status BOOLEAN;
878
879 CURSOR sca_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
880
881 select CSLO.sca_lines_output_id from cn_sca_headers_interface CSHI, cn_sca_lines_output CSLO
882 where CSLO.sca_headers_interface_id = CSHI.sca_headers_interface_id
883 and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
884 and CSHI.process_status <> 'SCA_UNPROCESSED'
885 and CSHI.transaction_status = 'SCA_UNPROCESSED'
886 and CSLO.sca_lines_output_id between start_id and end_id;
887
888 TYPE sca_lines_tbl IS TABLE OF cn_sca_lines_output.sca_lines_output_id%TYPE;
889 sca_lines sca_lines_tbl;
890
891 BEGIN
892
893 select start_id, end_id into
894 l_start_id, l_end_id
895 from cn_sca_process_batches
896 where sca_process_batch_id = p_physical_batch_id;
897
898 debugmsg('Populate results back to API: Creating Transactions ');
899 debugmsg('Populate results back to API: Start ID = ' || l_start_id);
900 debugmsg('Populate results back to API: End ID = ' || l_end_id);
901
902 l_adjusted_by := get_adjusted_by;
903
904 OPEN sca_lines_cur (l_start_id, l_end_id);
905 FETCH sca_lines_cur BULK COLLECT INTO sca_lines limit 1000;
906
907 FORALL j IN 1..sca_lines.COUNT
908
909 INSERT into CN_COMM_LINES_API
910 ( SALESREP_ID,
911 PROCESSED_DATE,
912 PROCESSED_PERIOD_ID,
913 TRANSACTION_AMOUNT,
914 TRX_TYPE,
915 REVENUE_CLASS_ID,
916 LOAD_STATUS,
917 ATTRIBUTE_CATEGORY,
918 ATTRIBUTE1,
919 ATTRIBUTE2,
920 ATTRIBUTE3,
921 ATTRIBUTE4,
922 ATTRIBUTE5,
923 ATTRIBUTE6,
924 ATTRIBUTE7,
925 ATTRIBUTE8,
926 ATTRIBUTE9,
927 ATTRIBUTE10,
928 ATTRIBUTE11,
929 ATTRIBUTE12,
930 ATTRIBUTE13,
931 ATTRIBUTE14,
932 ATTRIBUTE15,
933 ATTRIBUTE16,
934 ATTRIBUTE17,
935 ATTRIBUTE18,
936 ATTRIBUTE19,
937 ATTRIBUTE20,
938 ATTRIBUTE21,
939 ATTRIBUTE22,
940 ATTRIBUTE23,
941 ATTRIBUTE24,
942 ATTRIBUTE25,
943 ATTRIBUTE26,
944 ATTRIBUTE27,
945 ATTRIBUTE28,
946 ATTRIBUTE29,
947 ATTRIBUTE30,
948 ATTRIBUTE31,
949 ATTRIBUTE32,
950 ATTRIBUTE33,
951 ATTRIBUTE34,
952 ATTRIBUTE35,
953 ATTRIBUTE36,
954 ATTRIBUTE37,
955 ATTRIBUTE38,
956 ATTRIBUTE39,
957 ATTRIBUTE40,
958 ATTRIBUTE41,
959 ATTRIBUTE42,
960 ATTRIBUTE43,
961 ATTRIBUTE44,
962 ATTRIBUTE45,
963 ATTRIBUTE46,
964 ATTRIBUTE47,
965 ATTRIBUTE48,
966 ATTRIBUTE49,
967 ATTRIBUTE50,
968 ATTRIBUTE51,
969 ATTRIBUTE52,
970 ATTRIBUTE53,
971 ATTRIBUTE54,
972 ATTRIBUTE55,
973 ATTRIBUTE56,
974 ATTRIBUTE57,
975 ATTRIBUTE58,
976 ATTRIBUTE59,
977 ATTRIBUTE60,
978 ATTRIBUTE61,
979 ATTRIBUTE62,
980 ATTRIBUTE63,
981 ATTRIBUTE64,
982 ATTRIBUTE65,
983 ATTRIBUTE66,
984 ATTRIBUTE67,
985 ATTRIBUTE68,
986 ATTRIBUTE69,
987 ATTRIBUTE70,
988 ATTRIBUTE71,
989 ATTRIBUTE72,
990 ATTRIBUTE73,
991 ATTRIBUTE74,
992 ATTRIBUTE75,
993 ATTRIBUTE76,
994 ATTRIBUTE77,
995 ATTRIBUTE78,
996 ATTRIBUTE79,
997 ATTRIBUTE80,
998 ATTRIBUTE81,
999 ATTRIBUTE82,
1000 ATTRIBUTE83,
1001 ATTRIBUTE84,
1002 ATTRIBUTE85,
1003 ATTRIBUTE86,
1004 ATTRIBUTE87,
1005 ATTRIBUTE88,
1006 ATTRIBUTE89,
1007 ATTRIBUTE90,
1008 ATTRIBUTE91,
1009 ATTRIBUTE92,
1010 ATTRIBUTE93,
1011 ATTRIBUTE94,
1012 ATTRIBUTE95,
1013 ATTRIBUTE96,
1014 ATTRIBUTE97,
1015 ATTRIBUTE98,
1016 ATTRIBUTE99,
1017 ATTRIBUTE100,
1018 COMM_LINES_API_ID,
1019 CONC_BATCH_ID,
1020 PROCESS_BATCH_ID,
1021 SALESREP_NUMBER,
1022 ROLLUP_DATE,
1023 SOURCE_DOC_ID,
1024 SOURCE_DOC_TYPE,
1025 CREATED_BY,
1026 CREATION_DATE,
1027 LAST_UPDATED_BY,
1028 LAST_UPDATE_DATE,
1029 LAST_UPDATE_LOGIN,
1030 TRANSACTION_CURRENCY_CODE,
1031 EXCHANGE_RATE,
1032 ACCTD_TRANSACTION_AMOUNT,
1033 TRX_ID,
1034 TRX_LINE_ID,
1035 TRX_SALES_LINE_ID,
1036 QUANTITY,
1037 SOURCE_TRX_NUMBER,
1038 DISCOUNT_PERCENTAGE,
1039 MARGIN_PERCENTAGE,
1040 SOURCE_TRX_ID,
1041 SOURCE_TRX_LINE_ID,
1042 SOURCE_TRX_SALES_LINE_ID,
1043 NEGATED_FLAG,
1044 CUSTOMER_ID,
1045 INVENTORY_ITEM_ID,
1046 ORDER_NUMBER,
1047 BOOKED_DATE,
1048 INVOICE_NUMBER,
1049 INVOICE_DATE,
1050 ADJUST_DATE,
1051 ADJUSTED_BY,
1052 REVENUE_TYPE,
1053 ADJUST_ROLLUP_FLAG,
1054 ADJUST_COMMENTS,
1055 ADJUST_STATUS,
1056 LINE_NUMBER,
1057 BILL_TO_ADDRESS_ID,
1058 SHIP_TO_ADDRESS_ID,
1059 BILL_TO_CONTACT_ID,
1060 SHIP_TO_CONTACT_ID,
1061 ADJ_COMM_LINES_API_ID,
1062 PRE_DEFINED_RC_FLAG,
1063 ROLLUP_FLAG,
1064 FORECAST_ID,
1065 UPSIDE_QUANTITY,
1066 UPSIDE_AMOUNT,
1067 UOM_CODE,
1068 REASON_CODE,
1069 TYPE,
1070 PRE_PROCESSED_CODE,
1071 QUOTA_ID,
1072 SRP_PLAN_ASSIGN_ID,
1073 ROLE_ID,
1074 COMP_GROUP_ID,
1075 COMMISSION_AMOUNT,
1076 EMPLOYEE_NUMBER,
1077 REVERSAL_FLAG,
1078 REVERSAL_HEADER_ID,
1079 SALES_CHANNEL,
1080 OBJECT_VERSION_NUMBER,
1081 SPLIT_PCT,
1082 SPLIT_status,
1083 ORG_ID)
1084
1085 (select
1086 CS.SALESREP_ID,
1087 CCLA.PROCESSED_DATE,
1088 CCLA.PROCESSED_PERIOD_ID,
1089 (CSLO.allocation_percentage/100) * NVL(CSHI.transaction_amount, 0),
1090 CCLA.TRX_TYPE,
1091 CCLA.REVENUE_CLASS_ID,
1092 'UNLOADED',
1093 CCLA.ATTRIBUTE_CATEGORY,
1094 CCLA.ATTRIBUTE1,
1095 CCLA.ATTRIBUTE2,
1096 CCLA.ATTRIBUTE3,
1097 CCLA.ATTRIBUTE4,
1098 CCLA.ATTRIBUTE5,
1099 CCLA.ATTRIBUTE6,
1100 CCLA.ATTRIBUTE7,
1101 CCLA.ATTRIBUTE8,
1102 CCLA.ATTRIBUTE9,
1103 CCLA.ATTRIBUTE10,
1104 CCLA.ATTRIBUTE11,
1105 CCLA.ATTRIBUTE12,
1106 CCLA.ATTRIBUTE13,
1107 CCLA.ATTRIBUTE14,
1108 CCLA.ATTRIBUTE15,
1109 CCLA.ATTRIBUTE16,
1110 CCLA.ATTRIBUTE17,
1111 CCLA.ATTRIBUTE18,
1112 CCLA.ATTRIBUTE19,
1113 CCLA.ATTRIBUTE20,
1114 CCLA.ATTRIBUTE21,
1115 CCLA.ATTRIBUTE22,
1116 CCLA.ATTRIBUTE23,
1117 CCLA.ATTRIBUTE24,
1118 CCLA.ATTRIBUTE25,
1119 CCLA.ATTRIBUTE26,
1120 CCLA.ATTRIBUTE27,
1121 CCLA.ATTRIBUTE28,
1122 CCLA.ATTRIBUTE29,
1123 CCLA.ATTRIBUTE30,
1124 CCLA.ATTRIBUTE31,
1125 CCLA.ATTRIBUTE32,
1126 CCLA.ATTRIBUTE33,
1127 CCLA.ATTRIBUTE34,
1128 CCLA.ATTRIBUTE35,
1129 CCLA.ATTRIBUTE36,
1130 CCLA.ATTRIBUTE37,
1131 CCLA.ATTRIBUTE38,
1132 CCLA.ATTRIBUTE39,
1133 CCLA.ATTRIBUTE40,
1134 CCLA.ATTRIBUTE41,
1135 CCLA.ATTRIBUTE42,
1136 CCLA.ATTRIBUTE43,
1137 CCLA.ATTRIBUTE44,
1138 CCLA.ATTRIBUTE45,
1139 CCLA.ATTRIBUTE46,
1140 CCLA.ATTRIBUTE47,
1141 CCLA.ATTRIBUTE48,
1142 CCLA.ATTRIBUTE49,
1143 CCLA.ATTRIBUTE50,
1144 CCLA.ATTRIBUTE51,
1145 CCLA.ATTRIBUTE52,
1146 CCLA.ATTRIBUTE53,
1147 CCLA.ATTRIBUTE54,
1148 CCLA.ATTRIBUTE55,
1149 CCLA.ATTRIBUTE56,
1150 CCLA.ATTRIBUTE57,
1151 CCLA.ATTRIBUTE58,
1152 CCLA.ATTRIBUTE59,
1153 CCLA.ATTRIBUTE60,
1154 CCLA.ATTRIBUTE61,
1155 CCLA.ATTRIBUTE62,
1156 CCLA.ATTRIBUTE63,
1157 CCLA.ATTRIBUTE64,
1158 CCLA.ATTRIBUTE65,
1159 CCLA.ATTRIBUTE66,
1160 CCLA.ATTRIBUTE67,
1161 CCLA.ATTRIBUTE68,
1162 CCLA.ATTRIBUTE69,
1163 CCLA.ATTRIBUTE70,
1164 CCLA.ATTRIBUTE71,
1165 CCLA.ATTRIBUTE72,
1166 CCLA.ATTRIBUTE73,
1167 CCLA.ATTRIBUTE74,
1168 CCLA.ATTRIBUTE75,
1169 CCLA.ATTRIBUTE76,
1170 CCLA.ATTRIBUTE77,
1171 CCLA.ATTRIBUTE78,
1172 CCLA.ATTRIBUTE79,
1173 CCLA.ATTRIBUTE80,
1174 CCLA.ATTRIBUTE81,
1175 CCLA.ATTRIBUTE82,
1176 CCLA.ATTRIBUTE83,
1177 CCLA.ATTRIBUTE84,
1178 CCLA.ATTRIBUTE85,
1179 CCLA.ATTRIBUTE86,
1180 CCLA.ATTRIBUTE87,
1181 CCLA.ATTRIBUTE88,
1182 CCLA.ATTRIBUTE89,
1183 CCLA.ATTRIBUTE90,
1184 CCLA.ATTRIBUTE91,
1185 CCLA.ATTRIBUTE92,
1186 CCLA.ATTRIBUTE93,
1187 CCLA.ATTRIBUTE94,
1188 CCLA.ATTRIBUTE95,
1189 CCLA.ATTRIBUTE96,
1190 CCLA.ATTRIBUTE97,
1191 CCLA.ATTRIBUTE98,
1192 CCLA.ATTRIBUTE99,
1193 CCLA.ATTRIBUTE100,
1194 cn_comm_lines_api_s.NEXTVAL,
1195 CCLA.CONC_BATCH_ID,
1196 CCLA.PROCESS_BATCH_ID,
1197 NULL,
1198 CCLA.ROLLUP_DATE,
1199 CCLA.SOURCE_DOC_ID,
1200 CCLA.SOURCE_DOC_TYPE,
1201 fnd_global.user_id,
1202 Sysdate,
1203 fnd_global.user_id,
1204 Sysdate,
1205 fnd_global.login_id,
1206 CCLA.TRANSACTION_CURRENCY_CODE,
1207 CCLA.EXCHANGE_RATE,
1208 NULL,
1209 CCLA.TRX_ID,
1210 CCLA.TRX_LINE_ID,
1211 CCLA.TRX_SALES_LINE_ID,
1212 CCLA.QUANTITY,
1213 CCLA.SOURCE_TRX_NUMBER,
1214 CCLA.DISCOUNT_PERCENTAGE,
1215 CCLA.MARGIN_PERCENTAGE,
1216 CCLA.SOURCE_TRX_ID,
1217 CCLA.SOURCE_TRX_LINE_ID,
1218 CCLA.SOURCE_TRX_SALES_LINE_ID,
1219 CCLA.NEGATED_FLAG,
1220 CCLA.CUSTOMER_ID,
1221 CCLA.INVENTORY_ITEM_ID,
1222 CCLA.ORDER_NUMBER,
1223 CCLA.BOOKED_DATE,
1224 CCLA.INVOICE_NUMBER,
1225 CCLA.INVOICE_DATE,
1226 SYSDATE,
1227 l_adjusted_by,
1228 CSLO.REVENUE_TYPE,
1229 CCLA.ADJUST_ROLLUP_FLAG,
1230 'Created by SCA',
1231 'SCA_ALLOCATED',
1232 CCLA.LINE_NUMBER,
1233 CCLA.BILL_TO_ADDRESS_ID,
1234 CCLA.SHIP_TO_ADDRESS_ID,
1235 CCLA.BILL_TO_CONTACT_ID,
1236 CCLA.SHIP_TO_CONTACT_ID,
1237 CSLO.SOURCE_TRX_ID,
1238 CCLA.PRE_DEFINED_RC_FLAG,
1239 CCLA.ROLLUP_FLAG,
1240 CCLA.FORECAST_ID,
1241 CCLA.UPSIDE_QUANTITY,
1242 CCLA.UPSIDE_AMOUNT,
1243 CCLA.UOM_CODE,
1244 CCLA.REASON_CODE,
1245 CCLA.TYPE,
1246 CCLA.PRE_PROCESSED_CODE,
1247 CCLA.QUOTA_ID,
1248 CCLA.SRP_PLAN_ASSIGN_ID,
1249 CSLO.ROLE_ID,
1250 CCLA.COMP_GROUP_ID,
1251 CCLA.COMMISSION_AMOUNT,
1252 CS.EMPLOYEE_NUMBER,
1253 CCLA.REVERSAL_FLAG,
1254 CCLA.REVERSAL_HEADER_ID,
1255 CCLA.SALES_CHANNEL,
1256 CCLA.OBJECT_VERSION_NUMBER,
1257 CCLA.SPLIT_PCT,
1258 CCLA.SPLIT_status,
1259 ccla.org_id
1260 from
1261 cn_sca_lines_output CSLO, cn_salesreps CS, cn_comm_lines_api CCLA, cn_sca_headers_interface CSHI
1262 where CS.resource_id = CSLO.resource_id -- added org_id join, since one resource can belong to more than one org
1263 and cslo.org_id = cs.org_id
1264 and ccla.org_id = cslo.org_id
1265 and CCLA.comm_lines_api_id = CSLO.source_trx_id
1266 and CSHI.sca_headers_interface_id = CSLO.sca_headers_interface_id
1267 and CSLO.sca_lines_output_id = sca_lines(j));
1268
1269 debugmsg('Populate results back to API: Done with Creating Transactions ');
1270
1271 EXCEPTION
1272
1273 WHEN OTHERS THEN
1274
1275 debugmsg('Populate results back to API : Unexpected exception');
1276 debugmsg('Populate results back to API : Done with Creating Transactions ');
1277
1278 conc_status := fnd_concurrent.set_completion_status(
1279 status => 'ERROR',
1280 message => '');
1281
1282 RAISE;
1283
1284 END create_trx;
1285
1286 PROCEDURE negate_trx (
1287 p_start_date DATE,
1288 p_end_date DATE,
1289 p_physical_batch_id NUMBER) IS
1290
1291 CURSOR sca_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1292
1293 select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1294 where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1295 and CSHI.process_status <> 'SCA_UNPROCESSED'
1296 and CSHI.transaction_status = 'SCA_UNPROCESSED'
1297 and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
1298 and CSLI.sca_lines_interface_id between start_id and end_id;
1299
1300 CURSOR sca_no_rule_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1301
1302 select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1303 where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1304 and CSHI.process_status = 'NO RULE'
1305 and CSHI.transaction_status = 'SCA_UNPROCESSED'
1306 and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
1307 and CSLI.sca_lines_interface_id between start_id and end_id;
1308
1309 CURSOR sca_not_allocate_lines_cur (start_id VARCHAR2, end_id VARCHAR2) IS
1310
1311 select CSLI.source_trx_id from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
1312 where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
1313 and CSHI.process_status = 'NOT ALLOCATED'
1314 and CSHI.transaction_status = 'SCA_UNPROCESSED'
1315 and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
1316 and CSLI.sca_lines_interface_id between start_id and end_id;
1317
1318 TYPE sca_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
1319 TYPE sca_no_rule_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
1320 TYPE sca_not_allocate_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
1321
1322 sca_lines sca_lines_tbl;
1323 sca_no_rule_lines sca_no_rule_lines_tbl;
1324 sca_not_allocate_lines sca_not_allocate_lines_tbl;
1325
1326 l_start_id cn_sca_process_batches.start_id%TYPE;
1327 l_end_id cn_sca_process_batches.end_id%TYPE;
1328
1329 l_adjusted_by VARCHAR2(30);
1330 conc_status BOOLEAN;
1331
1332 BEGIN
1333
1334 l_adjusted_by := get_adjusted_by;
1335
1336 select start_id, end_id into
1337 l_start_id, l_end_id
1338 from cn_sca_process_batches
1339 where sca_process_batch_id = p_physical_batch_id;
1340
1341 debugmsg('Populate results back to API: Negating Transactions ');
1342 debugmsg('Populate results back to API: Start ID = ' || l_start_id);
1343 debugmsg('Populate results back to API: End ID = ' || l_end_id);
1344
1345 OPEN sca_lines_cur (l_start_id, l_end_id);
1346 FETCH sca_lines_cur BULK COLLECT INTO sca_lines limit 1000;
1347
1348 OPEN sca_no_rule_lines_cur (l_start_id, l_end_id);
1349 FETCH sca_no_rule_lines_cur BULK COLLECT INTO sca_no_rule_lines limit 1000;
1350
1351 OPEN sca_not_allocate_lines_cur (l_start_id, l_end_id);
1352 FETCH sca_not_allocate_lines_cur BULK COLLECT INTO sca_not_allocate_lines limit 1000;
1353
1354 debugmsg('Populate results back to API: Inserting Transactions into API for ''No Rules'', ''No Credit'' headers');
1355
1356 FORALL j IN 1..sca_lines.COUNT
1357
1358 UPDATE cn_comm_lines_api api
1359 SET load_status = 'OBSOLETE',
1360 adjust_status = 'FROZEN',
1361 adjust_date = sysdate,
1362 adjusted_by = l_adjusted_by,
1363 adjust_comments = 'Negated for SCA'
1364 WHERE comm_lines_api_id = sca_lines(j);
1365
1366
1367
1368 FORALL j IN 1..sca_not_allocate_lines.COUNT
1369
1370 INSERT into CN_COMM_LINES_API
1371 ( SALESREP_ID,
1372 PROCESSED_DATE,
1373 PROCESSED_PERIOD_ID,
1374 TRANSACTION_AMOUNT,
1375 TRX_TYPE,
1376 REVENUE_CLASS_ID,
1377 LOAD_STATUS,
1378 ATTRIBUTE_CATEGORY,
1379 ATTRIBUTE1,
1380 ATTRIBUTE2,
1381 ATTRIBUTE3,
1382 ATTRIBUTE4,
1383 ATTRIBUTE5,
1384 ATTRIBUTE6,
1385 ATTRIBUTE7,
1386 ATTRIBUTE8,
1387 ATTRIBUTE9,
1388 ATTRIBUTE10,
1389 ATTRIBUTE11,
1390 ATTRIBUTE12,
1391 ATTRIBUTE13,
1392 ATTRIBUTE14,
1393 ATTRIBUTE15,
1394 ATTRIBUTE16,
1395 ATTRIBUTE17,
1396 ATTRIBUTE18,
1397 ATTRIBUTE19,
1398 ATTRIBUTE20,
1399 ATTRIBUTE21,
1400 ATTRIBUTE22,
1401 ATTRIBUTE23,
1402 ATTRIBUTE24,
1403 ATTRIBUTE25,
1404 ATTRIBUTE26,
1405 ATTRIBUTE27,
1406 ATTRIBUTE28,
1407 ATTRIBUTE29,
1408 ATTRIBUTE30,
1409 ATTRIBUTE31,
1410 ATTRIBUTE32,
1411 ATTRIBUTE33,
1412 ATTRIBUTE34,
1413 ATTRIBUTE35,
1414 ATTRIBUTE36,
1415 ATTRIBUTE37,
1416 ATTRIBUTE38,
1417 ATTRIBUTE39,
1418 ATTRIBUTE40,
1419 ATTRIBUTE41,
1420 ATTRIBUTE42,
1421 ATTRIBUTE43,
1422 ATTRIBUTE44,
1423 ATTRIBUTE45,
1424 ATTRIBUTE46,
1425 ATTRIBUTE47,
1426 ATTRIBUTE48,
1427 ATTRIBUTE49,
1428 ATTRIBUTE50,
1429 ATTRIBUTE51,
1430 ATTRIBUTE52,
1431 ATTRIBUTE53,
1432 ATTRIBUTE54,
1433 ATTRIBUTE55,
1434 ATTRIBUTE56,
1435 ATTRIBUTE57,
1436 ATTRIBUTE58,
1437 ATTRIBUTE59,
1438 ATTRIBUTE60,
1439 ATTRIBUTE61,
1440 ATTRIBUTE62,
1441 ATTRIBUTE63,
1442 ATTRIBUTE64,
1443 ATTRIBUTE65,
1444 ATTRIBUTE66,
1445 ATTRIBUTE67,
1446 ATTRIBUTE68,
1447 ATTRIBUTE69,
1448 ATTRIBUTE70,
1449 ATTRIBUTE71,
1450 ATTRIBUTE72,
1451 ATTRIBUTE73,
1452 ATTRIBUTE74,
1453 ATTRIBUTE75,
1454 ATTRIBUTE76,
1455 ATTRIBUTE77,
1456 ATTRIBUTE78,
1457 ATTRIBUTE79,
1458 ATTRIBUTE80,
1459 ATTRIBUTE81,
1460 ATTRIBUTE82,
1461 ATTRIBUTE83,
1462 ATTRIBUTE84,
1463 ATTRIBUTE85,
1464 ATTRIBUTE86,
1465 ATTRIBUTE87,
1466 ATTRIBUTE88,
1467 ATTRIBUTE89,
1468 ATTRIBUTE90,
1469 ATTRIBUTE91,
1470 ATTRIBUTE92,
1471 ATTRIBUTE93,
1472 ATTRIBUTE94,
1473 ATTRIBUTE95,
1474 ATTRIBUTE96,
1475 ATTRIBUTE97,
1476 ATTRIBUTE98,
1477 ATTRIBUTE99,
1478 ATTRIBUTE100,
1479 COMM_LINES_API_ID,
1480 CONC_BATCH_ID,
1481 PROCESS_BATCH_ID,
1482 SALESREP_NUMBER,
1483 ROLLUP_DATE,
1484 SOURCE_DOC_ID,
1485 SOURCE_DOC_TYPE,
1486 CREATED_BY,
1487 CREATION_DATE,
1488 LAST_UPDATED_BY,
1489 LAST_UPDATE_DATE,
1490 LAST_UPDATE_LOGIN,
1491 TRANSACTION_CURRENCY_CODE,
1492 EXCHANGE_RATE,
1493 ACCTD_TRANSACTION_AMOUNT,
1494 TRX_ID,
1495 TRX_LINE_ID,
1496 TRX_SALES_LINE_ID,
1497 QUANTITY,
1498 SOURCE_TRX_NUMBER,
1499 DISCOUNT_PERCENTAGE,
1500 MARGIN_PERCENTAGE,
1501 SOURCE_TRX_ID,
1502 SOURCE_TRX_LINE_ID,
1503 SOURCE_TRX_SALES_LINE_ID,
1504 NEGATED_FLAG,
1505 CUSTOMER_ID,
1506 INVENTORY_ITEM_ID,
1507 ORDER_NUMBER,
1508 BOOKED_DATE,
1509 INVOICE_NUMBER,
1510 INVOICE_DATE,
1511 ADJUST_DATE,
1512 ADJUSTED_BY,
1513 REVENUE_TYPE,
1514 ADJUST_ROLLUP_FLAG,
1515 ADJUST_COMMENTS,
1516 ADJUST_STATUS,
1517 LINE_NUMBER,
1518 BILL_TO_ADDRESS_ID,
1519 SHIP_TO_ADDRESS_ID,
1520 BILL_TO_CONTACT_ID,
1521 SHIP_TO_CONTACT_ID,
1522 ADJ_COMM_LINES_API_ID,
1523 PRE_DEFINED_RC_FLAG,
1524 ROLLUP_FLAG,
1525 FORECAST_ID,
1526 UPSIDE_QUANTITY,
1527 UPSIDE_AMOUNT,
1528 UOM_CODE,
1529 REASON_CODE,
1530 TYPE,
1531 PRE_PROCESSED_CODE,
1532 QUOTA_ID,
1533 SRP_PLAN_ASSIGN_ID,
1534 ROLE_ID,
1535 COMP_GROUP_ID,
1536 COMMISSION_AMOUNT,
1537 EMPLOYEE_NUMBER,
1538 REVERSAL_FLAG,
1539 REVERSAL_HEADER_ID,
1540 SALES_CHANNEL,
1541 OBJECT_VERSION_NUMBER,
1542 SPLIT_PCT,
1543 SPLIT_status,
1544 org_id)
1545 (select
1546 CCLA.SALESREP_ID,
1547 CCLA.PROCESSED_DATE,
1548 CCLA.PROCESSED_PERIOD_ID,
1549 CCLA.TRANSACTION_AMOUNT,
1550 CCLA.TRX_TYPE,
1551 CCLA.REVENUE_CLASS_ID,
1552 'UNLOADED',
1553 CCLA.ATTRIBUTE_CATEGORY,
1554 CCLA.ATTRIBUTE1,
1555 CCLA.ATTRIBUTE2,
1556 CCLA.ATTRIBUTE3,
1557 CCLA.ATTRIBUTE4,
1558 CCLA.ATTRIBUTE5,
1559 CCLA.ATTRIBUTE6,
1560 CCLA.ATTRIBUTE7,
1561 CCLA.ATTRIBUTE8,
1562 CCLA.ATTRIBUTE9,
1563 CCLA.ATTRIBUTE10,
1564 CCLA.ATTRIBUTE11,
1565 CCLA.ATTRIBUTE12,
1566 CCLA.ATTRIBUTE13,
1567 CCLA.ATTRIBUTE14,
1568 CCLA.ATTRIBUTE15,
1569 CCLA.ATTRIBUTE16,
1570 CCLA.ATTRIBUTE17,
1571 CCLA.ATTRIBUTE18,
1572 CCLA.ATTRIBUTE19,
1573 CCLA.ATTRIBUTE20,
1574 CCLA.ATTRIBUTE21,
1575 CCLA.ATTRIBUTE22,
1576 CCLA.ATTRIBUTE23,
1577 CCLA.ATTRIBUTE24,
1578 CCLA.ATTRIBUTE25,
1579 CCLA.ATTRIBUTE26,
1580 CCLA.ATTRIBUTE27,
1581 CCLA.ATTRIBUTE28,
1582 CCLA.ATTRIBUTE29,
1583 CCLA.ATTRIBUTE30,
1584 CCLA.ATTRIBUTE31,
1585 CCLA.ATTRIBUTE32,
1586 CCLA.ATTRIBUTE33,
1587 CCLA.ATTRIBUTE34,
1588 CCLA.ATTRIBUTE35,
1589 CCLA.ATTRIBUTE36,
1590 CCLA.ATTRIBUTE37,
1591 CCLA.ATTRIBUTE38,
1592 CCLA.ATTRIBUTE39,
1593 CCLA.ATTRIBUTE40,
1594 CCLA.ATTRIBUTE41,
1595 CCLA.ATTRIBUTE42,
1596 CCLA.ATTRIBUTE43,
1597 CCLA.ATTRIBUTE44,
1598 CCLA.ATTRIBUTE45,
1599 CCLA.ATTRIBUTE46,
1600 CCLA.ATTRIBUTE47,
1601 CCLA.ATTRIBUTE48,
1602 CCLA.ATTRIBUTE49,
1603 CCLA.ATTRIBUTE50,
1604 CCLA.ATTRIBUTE51,
1605 CCLA.ATTRIBUTE52,
1606 CCLA.ATTRIBUTE53,
1607 CCLA.ATTRIBUTE54,
1608 CCLA.ATTRIBUTE55,
1609 CCLA.ATTRIBUTE56,
1610 CCLA.ATTRIBUTE57,
1611 CCLA.ATTRIBUTE58,
1612 CCLA.ATTRIBUTE59,
1613 CCLA.ATTRIBUTE60,
1614 CCLA.ATTRIBUTE61,
1615 CCLA.ATTRIBUTE62,
1616 CCLA.ATTRIBUTE63,
1617 CCLA.ATTRIBUTE64,
1618 CCLA.ATTRIBUTE65,
1619 CCLA.ATTRIBUTE66,
1620 CCLA.ATTRIBUTE67,
1621 CCLA.ATTRIBUTE68,
1622 CCLA.ATTRIBUTE69,
1623 CCLA.ATTRIBUTE70,
1624 CCLA.ATTRIBUTE71,
1625 CCLA.ATTRIBUTE72,
1626 CCLA.ATTRIBUTE73,
1627 CCLA.ATTRIBUTE74,
1628 CCLA.ATTRIBUTE75,
1629 CCLA.ATTRIBUTE76,
1630 CCLA.ATTRIBUTE77,
1631 CCLA.ATTRIBUTE78,
1632 CCLA.ATTRIBUTE79,
1633 CCLA.ATTRIBUTE80,
1634 CCLA.ATTRIBUTE81,
1635 CCLA.ATTRIBUTE82,
1636 CCLA.ATTRIBUTE83,
1637 CCLA.ATTRIBUTE84,
1638 CCLA.ATTRIBUTE85,
1639 CCLA.ATTRIBUTE86,
1640 CCLA.ATTRIBUTE87,
1641 CCLA.ATTRIBUTE88,
1642 CCLA.ATTRIBUTE89,
1643 CCLA.ATTRIBUTE90,
1644 CCLA.ATTRIBUTE91,
1645 CCLA.ATTRIBUTE92,
1646 CCLA.ATTRIBUTE93,
1647 CCLA.ATTRIBUTE94,
1648 CCLA.ATTRIBUTE95,
1649 CCLA.ATTRIBUTE96,
1650 CCLA.ATTRIBUTE97,
1651 CCLA.ATTRIBUTE98,
1652 CCLA.ATTRIBUTE99,
1653 CCLA.ATTRIBUTE100,
1654 cn_comm_lines_api_s.NEXTVAL,
1655 CCLA.CONC_BATCH_ID,
1656 CCLA.PROCESS_BATCH_ID,
1657 NULL,
1658 CCLA.ROLLUP_DATE,
1659 CCLA.SOURCE_DOC_ID,
1660 CCLA.SOURCE_DOC_TYPE,
1661 fnd_global.user_id,
1662 Sysdate,
1663 fnd_global.user_id,
1664 Sysdate,
1665 fnd_global.login_id,
1666 CCLA.TRANSACTION_CURRENCY_CODE,
1667 CCLA.EXCHANGE_RATE,
1668 CCLA.ACCTD_TRANSACTION_AMOUNT,
1669 CCLA.TRX_ID,
1670 CCLA.TRX_LINE_ID,
1671 CCLA.TRX_SALES_LINE_ID,
1672 CCLA.QUANTITY,
1673 CCLA.SOURCE_TRX_NUMBER,
1674 CCLA.DISCOUNT_PERCENTAGE,
1675 CCLA.MARGIN_PERCENTAGE,
1676 CCLA.SOURCE_TRX_ID,
1677 CCLA.SOURCE_TRX_LINE_ID,
1678 CCLA.SOURCE_TRX_SALES_LINE_ID,
1679 CCLA.NEGATED_FLAG,
1680 CCLA.CUSTOMER_ID,
1681 CCLA.INVENTORY_ITEM_ID,
1682 CCLA.ORDER_NUMBER,
1683 CCLA.BOOKED_DATE,
1684 CCLA.INVOICE_NUMBER,
1685 CCLA.INVOICE_DATE,
1686 SYSDATE,
1687 l_adjusted_by,
1688 CCLA.REVENUE_TYPE,
1689 CCLA.ADJUST_ROLLUP_FLAG,
1690 'Created by SCA',
1691 'SCA_NOT_ALLOCATED',
1692 CCLA.LINE_NUMBER,
1693 CCLA.BILL_TO_ADDRESS_ID,
1694 CCLA.SHIP_TO_ADDRESS_ID,
1695 CCLA.BILL_TO_CONTACT_ID,
1696 CCLA.SHIP_TO_CONTACT_ID,
1697 CCLA.COMM_LINES_API_ID,
1698 CCLA.PRE_DEFINED_RC_FLAG,
1699 CCLA.ROLLUP_FLAG,
1700 CCLA.FORECAST_ID,
1701 CCLA.UPSIDE_QUANTITY,
1702 CCLA.UPSIDE_AMOUNT,
1703 CCLA.UOM_CODE,
1704 CCLA.REASON_CODE,
1705 CCLA.TYPE,
1706 CCLA.PRE_PROCESSED_CODE,
1707 CCLA.QUOTA_ID,
1708 CCLA.SRP_PLAN_ASSIGN_ID,
1709 CCLA.ROLE_ID,
1710 CCLA.COMP_GROUP_ID,
1711 CCLA.COMMISSION_AMOUNT,
1712 CCLA.EMPLOYEE_NUMBER,
1713 CCLA.REVERSAL_FLAG,
1714 CCLA.REVERSAL_HEADER_ID,
1715 CCLA.SALES_CHANNEL,
1716 CCLA.OBJECT_VERSION_NUMBER,
1717 CCLA.SPLIT_PCT,
1718 CCLA.SPLIT_status,
1719 ccla.org_id
1720 from
1721 cn_comm_lines_api CCLA
1722 where CCLA.comm_lines_api_id = sca_not_allocate_lines(j));
1723
1724 FORALL j IN 1..sca_no_rule_lines.COUNT
1725
1726 INSERT into CN_COMM_LINES_API
1727 ( SALESREP_ID,
1728 PROCESSED_DATE,
1729 PROCESSED_PERIOD_ID,
1730 TRANSACTION_AMOUNT,
1731 TRX_TYPE,
1732 REVENUE_CLASS_ID,
1733 LOAD_STATUS,
1734 ATTRIBUTE_CATEGORY,
1735 ATTRIBUTE1,
1736 ATTRIBUTE2,
1737 ATTRIBUTE3,
1738 ATTRIBUTE4,
1739 ATTRIBUTE5,
1740 ATTRIBUTE6,
1741 ATTRIBUTE7,
1742 ATTRIBUTE8,
1743 ATTRIBUTE9,
1744 ATTRIBUTE10,
1745 ATTRIBUTE11,
1746 ATTRIBUTE12,
1747 ATTRIBUTE13,
1748 ATTRIBUTE14,
1749 ATTRIBUTE15,
1750 ATTRIBUTE16,
1751 ATTRIBUTE17,
1752 ATTRIBUTE18,
1753 ATTRIBUTE19,
1754 ATTRIBUTE20,
1755 ATTRIBUTE21,
1756 ATTRIBUTE22,
1757 ATTRIBUTE23,
1758 ATTRIBUTE24,
1759 ATTRIBUTE25,
1760 ATTRIBUTE26,
1761 ATTRIBUTE27,
1762 ATTRIBUTE28,
1763 ATTRIBUTE29,
1764 ATTRIBUTE30,
1765 ATTRIBUTE31,
1766 ATTRIBUTE32,
1767 ATTRIBUTE33,
1768 ATTRIBUTE34,
1769 ATTRIBUTE35,
1770 ATTRIBUTE36,
1771 ATTRIBUTE37,
1772 ATTRIBUTE38,
1773 ATTRIBUTE39,
1774 ATTRIBUTE40,
1775 ATTRIBUTE41,
1776 ATTRIBUTE42,
1777 ATTRIBUTE43,
1778 ATTRIBUTE44,
1779 ATTRIBUTE45,
1780 ATTRIBUTE46,
1781 ATTRIBUTE47,
1782 ATTRIBUTE48,
1783 ATTRIBUTE49,
1784 ATTRIBUTE50,
1785 ATTRIBUTE51,
1786 ATTRIBUTE52,
1787 ATTRIBUTE53,
1788 ATTRIBUTE54,
1789 ATTRIBUTE55,
1790 ATTRIBUTE56,
1791 ATTRIBUTE57,
1792 ATTRIBUTE58,
1793 ATTRIBUTE59,
1794 ATTRIBUTE60,
1795 ATTRIBUTE61,
1796 ATTRIBUTE62,
1797 ATTRIBUTE63,
1798 ATTRIBUTE64,
1799 ATTRIBUTE65,
1800 ATTRIBUTE66,
1801 ATTRIBUTE67,
1802 ATTRIBUTE68,
1803 ATTRIBUTE69,
1804 ATTRIBUTE70,
1805 ATTRIBUTE71,
1806 ATTRIBUTE72,
1807 ATTRIBUTE73,
1808 ATTRIBUTE74,
1809 ATTRIBUTE75,
1810 ATTRIBUTE76,
1811 ATTRIBUTE77,
1812 ATTRIBUTE78,
1813 ATTRIBUTE79,
1814 ATTRIBUTE80,
1815 ATTRIBUTE81,
1816 ATTRIBUTE82,
1817 ATTRIBUTE83,
1818 ATTRIBUTE84,
1819 ATTRIBUTE85,
1820 ATTRIBUTE86,
1821 ATTRIBUTE87,
1822 ATTRIBUTE88,
1823 ATTRIBUTE89,
1824 ATTRIBUTE90,
1825 ATTRIBUTE91,
1826 ATTRIBUTE92,
1827 ATTRIBUTE93,
1828 ATTRIBUTE94,
1829 ATTRIBUTE95,
1830 ATTRIBUTE96,
1831 ATTRIBUTE97,
1832 ATTRIBUTE98,
1833 ATTRIBUTE99,
1834 ATTRIBUTE100,
1835 COMM_LINES_API_ID,
1836 CONC_BATCH_ID,
1837 PROCESS_BATCH_ID,
1838 SALESREP_NUMBER,
1839 ROLLUP_DATE,
1840 SOURCE_DOC_ID,
1841 SOURCE_DOC_TYPE,
1842 CREATED_BY,
1843 CREATION_DATE,
1844 LAST_UPDATED_BY,
1845 LAST_UPDATE_DATE,
1846 LAST_UPDATE_LOGIN,
1847 TRANSACTION_CURRENCY_CODE,
1848 EXCHANGE_RATE,
1849 ACCTD_TRANSACTION_AMOUNT,
1850 TRX_ID,
1851 TRX_LINE_ID,
1852 TRX_SALES_LINE_ID,
1853 QUANTITY,
1854 SOURCE_TRX_NUMBER,
1855 DISCOUNT_PERCENTAGE,
1856 MARGIN_PERCENTAGE,
1857 SOURCE_TRX_ID,
1858 SOURCE_TRX_LINE_ID,
1859 SOURCE_TRX_SALES_LINE_ID,
1860 NEGATED_FLAG,
1861 CUSTOMER_ID,
1862 INVENTORY_ITEM_ID,
1863 ORDER_NUMBER,
1864 BOOKED_DATE,
1865 INVOICE_NUMBER,
1866 INVOICE_DATE,
1867 ADJUST_DATE,
1868 ADJUSTED_BY,
1869 REVENUE_TYPE,
1870 ADJUST_ROLLUP_FLAG,
1871 ADJUST_COMMENTS,
1872 ADJUST_STATUS,
1873 LINE_NUMBER,
1874 BILL_TO_ADDRESS_ID,
1875 SHIP_TO_ADDRESS_ID,
1876 BILL_TO_CONTACT_ID,
1877 SHIP_TO_CONTACT_ID,
1878 ADJ_COMM_LINES_API_ID,
1879 PRE_DEFINED_RC_FLAG,
1880 ROLLUP_FLAG,
1881 FORECAST_ID,
1882 UPSIDE_QUANTITY,
1883 UPSIDE_AMOUNT,
1884 UOM_CODE,
1885 REASON_CODE,
1886 TYPE,
1887 PRE_PROCESSED_CODE,
1888 QUOTA_ID,
1889 SRP_PLAN_ASSIGN_ID,
1890 ROLE_ID,
1891 COMP_GROUP_ID,
1892 COMMISSION_AMOUNT,
1893 EMPLOYEE_NUMBER,
1894 REVERSAL_FLAG,
1895 REVERSAL_HEADER_ID,
1896 SALES_CHANNEL,
1897 OBJECT_VERSION_NUMBER,
1898 SPLIT_PCT,
1899 SPLIT_status,
1900 org_id)
1901
1902 (select
1903 CCLA.SALESREP_ID,
1904 CCLA.PROCESSED_DATE,
1905 CCLA.PROCESSED_PERIOD_ID,
1906 CCLA.TRANSACTION_AMOUNT,
1907 CCLA.TRX_TYPE,
1908 CCLA.REVENUE_CLASS_ID,
1909 'UNLOADED',
1910 CCLA.ATTRIBUTE_CATEGORY,
1911 CCLA.ATTRIBUTE1,
1912 CCLA.ATTRIBUTE2,
1913 CCLA.ATTRIBUTE3,
1914 CCLA.ATTRIBUTE4,
1915 CCLA.ATTRIBUTE5,
1916 CCLA.ATTRIBUTE6,
1917 CCLA.ATTRIBUTE7,
1918 CCLA.ATTRIBUTE8,
1919 CCLA.ATTRIBUTE9,
1920 CCLA.ATTRIBUTE10,
1921 CCLA.ATTRIBUTE11,
1922 CCLA.ATTRIBUTE12,
1923 CCLA.ATTRIBUTE13,
1924 CCLA.ATTRIBUTE14,
1925 CCLA.ATTRIBUTE15,
1926 CCLA.ATTRIBUTE16,
1927 CCLA.ATTRIBUTE17,
1928 CCLA.ATTRIBUTE18,
1929 CCLA.ATTRIBUTE19,
1930 CCLA.ATTRIBUTE20,
1931 CCLA.ATTRIBUTE21,
1932 CCLA.ATTRIBUTE22,
1933 CCLA.ATTRIBUTE23,
1934 CCLA.ATTRIBUTE24,
1935 CCLA.ATTRIBUTE25,
1936 CCLA.ATTRIBUTE26,
1937 CCLA.ATTRIBUTE27,
1938 CCLA.ATTRIBUTE28,
1939 CCLA.ATTRIBUTE29,
1940 CCLA.ATTRIBUTE30,
1941 CCLA.ATTRIBUTE31,
1942 CCLA.ATTRIBUTE32,
1943 CCLA.ATTRIBUTE33,
1944 CCLA.ATTRIBUTE34,
1945 CCLA.ATTRIBUTE35,
1946 CCLA.ATTRIBUTE36,
1947 CCLA.ATTRIBUTE37,
1948 CCLA.ATTRIBUTE38,
1949 CCLA.ATTRIBUTE39,
1950 CCLA.ATTRIBUTE40,
1951 CCLA.ATTRIBUTE41,
1952 CCLA.ATTRIBUTE42,
1953 CCLA.ATTRIBUTE43,
1954 CCLA.ATTRIBUTE44,
1955 CCLA.ATTRIBUTE45,
1956 CCLA.ATTRIBUTE46,
1957 CCLA.ATTRIBUTE47,
1958 CCLA.ATTRIBUTE48,
1959 CCLA.ATTRIBUTE49,
1960 CCLA.ATTRIBUTE50,
1961 CCLA.ATTRIBUTE51,
1962 CCLA.ATTRIBUTE52,
1963 CCLA.ATTRIBUTE53,
1964 CCLA.ATTRIBUTE54,
1965 CCLA.ATTRIBUTE55,
1966 CCLA.ATTRIBUTE56,
1967 CCLA.ATTRIBUTE57,
1968 CCLA.ATTRIBUTE58,
1969 CCLA.ATTRIBUTE59,
1970 CCLA.ATTRIBUTE60,
1971 CCLA.ATTRIBUTE61,
1972 CCLA.ATTRIBUTE62,
1973 CCLA.ATTRIBUTE63,
1974 CCLA.ATTRIBUTE64,
1975 CCLA.ATTRIBUTE65,
1976 CCLA.ATTRIBUTE66,
1977 CCLA.ATTRIBUTE67,
1978 CCLA.ATTRIBUTE68,
1979 CCLA.ATTRIBUTE69,
1980 CCLA.ATTRIBUTE70,
1981 CCLA.ATTRIBUTE71,
1982 CCLA.ATTRIBUTE72,
1983 CCLA.ATTRIBUTE73,
1984 CCLA.ATTRIBUTE74,
1985 CCLA.ATTRIBUTE75,
1986 CCLA.ATTRIBUTE76,
1987 CCLA.ATTRIBUTE77,
1988 CCLA.ATTRIBUTE78,
1989 CCLA.ATTRIBUTE79,
1990 CCLA.ATTRIBUTE80,
1991 CCLA.ATTRIBUTE81,
1992 CCLA.ATTRIBUTE82,
1993 CCLA.ATTRIBUTE83,
1994 CCLA.ATTRIBUTE84,
1995 CCLA.ATTRIBUTE85,
1996 CCLA.ATTRIBUTE86,
1997 CCLA.ATTRIBUTE87,
1998 CCLA.ATTRIBUTE88,
1999 CCLA.ATTRIBUTE89,
2000 CCLA.ATTRIBUTE90,
2001 CCLA.ATTRIBUTE91,
2002 CCLA.ATTRIBUTE92,
2003 CCLA.ATTRIBUTE93,
2004 CCLA.ATTRIBUTE94,
2005 CCLA.ATTRIBUTE95,
2006 CCLA.ATTRIBUTE96,
2007 CCLA.ATTRIBUTE97,
2008 CCLA.ATTRIBUTE98,
2009 CCLA.ATTRIBUTE99,
2010 CCLA.ATTRIBUTE100,
2011 cn_comm_lines_api_s.NEXTVAL,
2012 CCLA.CONC_BATCH_ID,
2013 CCLA.PROCESS_BATCH_ID,
2014 NULL,
2015 CCLA.ROLLUP_DATE,
2016 CCLA.SOURCE_DOC_ID,
2017 CCLA.SOURCE_DOC_TYPE,
2018 fnd_global.user_id,
2019 Sysdate,
2020 fnd_global.user_id,
2021 Sysdate,
2022 fnd_global.login_id,
2023 CCLA.TRANSACTION_CURRENCY_CODE,
2024 CCLA.EXCHANGE_RATE,
2025 CCLA.ACCTD_TRANSACTION_AMOUNT,
2026 CCLA.TRX_ID,
2027 CCLA.TRX_LINE_ID,
2028 CCLA.TRX_SALES_LINE_ID,
2029 CCLA.QUANTITY,
2030 CCLA.SOURCE_TRX_NUMBER,
2031 CCLA.DISCOUNT_PERCENTAGE,
2032 CCLA.MARGIN_PERCENTAGE,
2033 CCLA.SOURCE_TRX_ID,
2034 CCLA.SOURCE_TRX_LINE_ID,
2035 CCLA.SOURCE_TRX_SALES_LINE_ID,
2036 CCLA.NEGATED_FLAG,
2037 CCLA.CUSTOMER_ID,
2038 CCLA.INVENTORY_ITEM_ID,
2039 CCLA.ORDER_NUMBER,
2040 CCLA.BOOKED_DATE,
2041 CCLA.INVOICE_NUMBER,
2042 CCLA.INVOICE_DATE,
2043 SYSDATE,
2044 l_adjusted_by,
2045 CCLA.REVENUE_TYPE,
2046 CCLA.ADJUST_ROLLUP_FLAG,
2047 'Created by SCA',
2048 'SCA_NO_RULE',
2049 CCLA.LINE_NUMBER,
2050 CCLA.BILL_TO_ADDRESS_ID,
2051 CCLA.SHIP_TO_ADDRESS_ID,
2052 CCLA.BILL_TO_CONTACT_ID,
2053 CCLA.SHIP_TO_CONTACT_ID,
2054 CCLA.COMM_LINES_API_ID,
2055 CCLA.PRE_DEFINED_RC_FLAG,
2056 CCLA.ROLLUP_FLAG,
2057 CCLA.FORECAST_ID,
2058 CCLA.UPSIDE_QUANTITY,
2059 CCLA.UPSIDE_AMOUNT,
2060 CCLA.UOM_CODE,
2061 CCLA.REASON_CODE,
2062 CCLA.TYPE,
2063 CCLA.PRE_PROCESSED_CODE,
2064 CCLA.QUOTA_ID,
2065 CCLA.SRP_PLAN_ASSIGN_ID,
2066 CCLA.ROLE_ID,
2067 CCLA.COMP_GROUP_ID,
2068 CCLA.COMMISSION_AMOUNT,
2069 CCLA.EMPLOYEE_NUMBER,
2070 CCLA.REVERSAL_FLAG,
2071 CCLA.REVERSAL_HEADER_ID,
2072 CCLA.SALES_CHANNEL,
2073 CCLA.OBJECT_VERSION_NUMBER,
2074 CCLA.SPLIT_PCT,
2075 CCLA.SPLIT_status,
2076 ccla.org_id
2077 from
2078 cn_comm_lines_api CCLA
2079 where CCLA.comm_lines_api_id = sca_no_rule_lines(j));
2080
2081
2082 debugmsg('Populate results back to API: Done with Negating Transactions ');
2083
2084 EXCEPTION
2085
2086 WHEN OTHERS THEN
2087
2088 debugmsg('Populate results back to API : Unexpected exception');
2089 debugmsg('Populate results back to API : Done with Negating Transactions ');
2090
2091 conc_status := fnd_concurrent.set_completion_status(
2092 status => 'ERROR',
2093 message => '');
2094
2095 RAISE;
2096
2097 END negate_trx;
2098
2099 PROCEDURE populate_results (
2100 errbuf OUT NOCOPY VARCHAR2,
2101 retcode OUT NOCOPY NUMBER,
2102 pp_start_date VARCHAR2,
2103 pp_end_date VARCHAR2,
2104 p_org_id IN VARCHAR2) IS
2105
2106
2107 CURSOR sca_update_headers_cur (p_start_date DATE, p_end_date DATE) IS
2108
2109 select CSHI.sca_headers_interface_id from cn_sca_headers_interface CSHI
2110 where trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2111 and CSHI.process_status <> 'SCA_UNPROCESSED'
2112 and CSHI.transaction_status = 'SCA_UNPROCESSED'
2113 AND cshi.org_id = p_org_id;
2114
2115 TYPE sca_update_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2116
2117 sca_update_headers sca_update_headers_tbl;
2118
2119 p_start_date DATE;
2120 p_end_date DATE;
2121 l_process_audit_id NUMBER;
2122 l_create_logical_batch_id NUMBER;
2123 l_negate_logical_batch_id NUMBER;
2124 conc_status boolean;
2125 x_negate_size NUMBER;
2126 x_create_size NUMBER;
2127
2128 BEGIN
2129
2130 -- Standard Start of API savepoint
2131 SAVEPOINT populate_results_savepoint;
2132
2133 -- Convert the dates for the varchar2 parameters passed in from concurrent program
2134 p_start_date := fnd_date.canonical_to_date(pp_start_date);
2135 p_end_date := fnd_date.canonical_to_date(pp_end_date);
2136
2137 SELECT cn_sca_logical_batches_s.NEXTVAL
2138 INTO l_create_logical_batch_id
2139 FROM sys.dual;
2140
2141 SELECT cn_sca_logical_batches_s.NEXTVAL
2142 INTO l_negate_logical_batch_id
2143 FROM sys.dual;
2144
2145 assign(l_negate_logical_batch_id,
2146 p_start_date,
2147 p_end_date,
2148 'CSHI',
2149 p_org_id,
2150 x_negate_size);
2151
2152 assign(l_create_logical_batch_id,
2153 p_start_date,
2154 p_end_date,
2155 'CSLO',
2156 p_org_id,
2157 x_create_size);
2158
2159 --+
2160 --+ Call begin_batch to get process_audit_id for debug log file
2161 --+
2162
2163 cn_message_pkg.begin_batch
2164 (x_process_type => 'RESULTS_TRANSFER',
2165 x_parent_proc_audit_id => null,
2166 x_process_audit_id => l_process_audit_id,
2167 x_request_id => fnd_global.conc_request_id,
2168 p_org_id => p_org_id);
2169
2170 debugmsg('Results Transfer : Start of Transfer');
2171 debugmsg('Results Transfer : process_audit_id is ' || l_process_audit_id );
2172 debugmsg('Results Transfer : negate_logical_batch_id is ' || l_negate_logical_batch_id );
2173 debugmsg('Results Transfer : negate logical batch size = ' || x_negate_size );
2174 debugmsg('Results Transfer : create_logical_batch_id is ' || l_create_logical_batch_id );
2175 debugmsg('Results Transfer : create logical batch size = ' || x_create_size );
2176
2177 if (x_create_size > 0) then
2178
2179 debugmsg('Result Transfer : Start of Create Transactions Batch');
2180
2181 conc_dispatch(
2182 x_parent_proc_audit_id => l_process_audit_id,
2183 x_start_date => p_start_date,
2184 x_end_date => p_end_date,
2185 x_logical_batch_id => l_create_logical_batch_id,
2186 x_process => 'Create_trx');
2187
2188 debugmsg('Result Transfer : End of Create Transactions Batch');
2189
2190 end if;
2191
2192 if (x_negate_size > 0) then
2193
2194 debugmsg('Result Transfer : Start of Negate Transactions Batch');
2195
2196 conc_dispatch(
2197 x_parent_proc_audit_id => l_process_audit_id,
2198 x_start_date => p_start_date,
2199 x_end_date => p_end_date,
2200 x_logical_batch_id => l_negate_logical_batch_id,
2201 x_process => 'Negate_trx');
2202
2203 debugmsg('Result Transfer : End of Negate Transactions Batch');
2204
2205 end if;
2206
2207 debugmsg('Results Transfer : Updating Headers to ''Populated''');
2208
2209 OPEN sca_update_headers_cur (p_start_date, p_end_date);
2210 FETCH sca_update_headers_cur BULK COLLECT INTO sca_update_headers;
2211
2212 if (sca_update_headers.COUNT = 0) then
2213
2214 raise no_trx_lines;
2215
2216 end if;
2217
2218 FORALL j IN 1..sca_update_headers.COUNT
2219
2220 UPDATE cn_sca_headers_interface
2221 SET transaction_status = 'SCA_POPULATED'
2222 WHERE sca_headers_interface_id = sca_update_headers(j);
2223
2224 debugmsg('Results Transfer : End of Transfer');
2225
2226 cn_message_pkg.end_batch(l_process_audit_id);
2227
2228 EXCEPTION
2229
2230 WHEN no_trx_lines THEN
2231
2232 ROLLBACK TO populate_results_savepoint;
2233
2234 -- Call end_batch to end debug log file
2235 debugmsg('Results Transfer : No transactions to transfer');
2236 debugmsg('Results Transfer : End of Transfer');
2237
2238 conc_status := fnd_concurrent.set_completion_status(
2239 status => 'ERROR',
2240 message => '');
2241
2242 cn_message_pkg.end_batch(l_process_audit_id);
2243
2244 WHEN OTHERS THEN
2245
2246 ROLLBACK TO populate_results_savepoint;
2247
2248 debugmsg('Results Transfer : Unexpected exception');
2249 debugmsg('Results Transfer : End of Transfer');
2250
2251 conc_status := fnd_concurrent.set_completion_status(
2252 status => 'ERROR',
2253 message => '');
2254
2255 cn_message_pkg.end_batch(l_process_audit_id);
2256
2257 END populate_results;
2258
2259
2260 PROCEDURE call_populate_results (
2261 p_api_version IN NUMBER,
2262 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2263 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2264 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
2265 p_start_date IN DATE,
2266 p_end_date IN DATE,
2267 p_org_id IN NUMBER,
2268 x_return_status OUT NOCOPY VARCHAR2,
2269 x_msg_count OUT NOCOPY NUMBER,
2270 x_msg_data OUT NOCOPY VARCHAR2,
2271 x_process_audit_id OUT NOCOPY NUMBER) IS
2272
2273 l_api_name CONSTANT VARCHAR2(30) := 'call_populate_results';
2274 l_api_version CONSTANT NUMBER := 1.0;
2275
2276 p_errbuf VARCHAR2(1000);
2277 p_retcode NUMBER;
2278 i NUMBER;
2279 x_size NUMBER;
2280
2281
2282 BEGIN
2283
2284 -- Standard Start of API savepoint
2285 SAVEPOINT call_populate_results;
2286
2287 -- Standard call to check for call compatibility.
2288 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2289 p_api_version ,
2290 l_api_name,
2291 G_PKG_NAME ) THEN
2292 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2293 END IF;
2294
2295 -- Initialize message list if p_init_msg_list is set to TRUE.
2296 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2297 FND_MSG_PUB.initialize;
2298 END IF;
2299
2300 -- Initialize API return status to success
2301 x_return_status := FND_API.G_RET_STS_SUCCESS;
2302
2303 fnd_request.set_org_id( p_org_id ); -- vensrini
2304
2305 x_process_audit_id :=
2306
2307 FND_REQUEST.SUBMIT_REQUEST(
2308 application => 'CN',
2309 program => 'CN_SCA_POPULATE_RESULTS',
2310 argument1 => TO_CHAR(p_start_date,'YYYY/MM/DD HH24:MI:SS'),
2311 argument2 => TO_CHAR(p_end_date,'YYYY/MM/DD HH24:MI:SS'),
2312 argument3 => p_org_id);
2313 commit;
2314
2315 EXCEPTION
2316 WHEN FND_API.G_EXC_ERROR THEN
2317 ROLLBACK TO call_populate_results;
2318 x_return_status := FND_API.G_RET_STS_ERROR ;
2319 FND_MSG_PUB.Count_And_Get(
2320 p_count => x_msg_count ,
2321 p_data => x_msg_data ,
2322 p_encoded => FND_API.G_FALSE);
2323 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2324 ROLLBACK TO call_populate_results;
2325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2326 FND_MSG_PUB.Count_And_Get(
2327 p_count => x_msg_count ,
2328 p_data => x_msg_data ,
2329 p_encoded => FND_API.G_FALSE);
2330 WHEN OTHERS THEN
2331 ROLLBACK TO call_populate_results;
2332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2333 IF FND_MSG_PUB.Check_Msg_Level(
2334 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2335 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2336 END IF;
2337 FND_MSG_PUB.Count_And_Get(
2338 p_count => x_msg_count ,
2339 p_data => x_msg_data ,
2340 p_encoded => FND_API.G_FALSE);
2341
2342 END call_populate_results;
2343
2344
2345 PROCEDURE populate_data (
2346 errbuf OUT NOCOPY VARCHAR2,
2347 retcode OUT NOCOPY NUMBER,
2348 pp_start_date VARCHAR2,
2349 pp_end_date VARCHAR2,
2350 p_checkbox_value VARCHAR2) IS
2351
2352 p_start_date DATE;
2353 p_end_date DATE;
2354 l_process_audit_id NUMBER;
2355
2356 l_logical_batch_id NUMBER;
2357 x_size_inv NUMBER;
2358 x_size_ord NUMBER;
2359 x_size NUMBER;
2360 conc_status boolean;
2361 p_org_id number;
2362
2363 BEGIN
2364
2365 -- Standard Start of API savepoint
2366 SAVEPOINT populate_data_savepoint;
2367
2368 p_org_id := mo_global.get_current_org_id();
2369
2370 -- Convert the dates for the varchar2 parameters passed in from concurrent program
2371
2372 p_start_date := fnd_date.canonical_to_date(pp_start_date);
2373 p_end_date := fnd_date.canonical_to_date(pp_end_date);
2374
2375 --+
2376 --+ Call begin_batch to get process_audit_id for debug log file
2377 --+
2378
2379 cn_message_pkg.begin_batch
2380 (x_process_type => 'ALLOCATION_TRANSFER',
2381 x_parent_proc_audit_id => null,
2382 x_process_audit_id => l_process_audit_id,
2383 x_request_id => fnd_global.conc_request_id,
2384 p_org_id => p_org_id);
2385
2386 SELECT cn_sca_logical_batches_s.NEXTVAL
2387 INTO l_logical_batch_id
2388 FROM sys.dual;
2389
2390 debugmsg('Allocation Transfer : Start of Transfer');
2391 debugmsg('Allocation Transfer : process_audit_id is ' || l_process_audit_id );
2392 debugmsg('Allocation Transfer : logical_batch_id is ' || l_logical_batch_id );
2393 debugmsg('Allocation Transfer : mo_global.get_current_org_id is - ' || p_org_id);
2394
2395 assign(l_logical_batch_id,
2396 p_start_date,
2397 p_end_date,
2398 'ORD',
2399 p_org_id,
2400 x_size);
2401
2402 assign(l_logical_batch_id,
2403 p_start_date,
2404 p_end_date,
2405 'INV',
2406 p_org_id,
2407 x_size);
2408
2409 conc_dispatch(
2410 x_parent_proc_audit_id => l_process_audit_id,
2411 x_start_date => p_start_date,
2412 x_end_date => p_end_date,
2413 x_logical_batch_id => l_logical_batch_id,
2414 x_process => 'Check_comm_lines_api_adjusted');
2415
2416
2417 if (p_checkbox_value = 'Y') then
2418
2419 SELECT cn_sca_logical_batches_s.NEXTVAL
2420 INTO l_logical_batch_id
2421 FROM sys.dual;
2422
2423 debugmsg('Allocation Transfer : Need to Rerun ');
2424 debugmsg('Allocation Transfer : Rerun: Logical batch id = ' || l_logical_batch_id);
2425
2426 assign(l_logical_batch_id,
2427 p_start_date,
2428 p_end_date,
2429 'SCA_ORD',
2430 p_org_id,
2431 x_size);
2432
2433 assign(l_logical_batch_id,
2434 p_start_date,
2435 p_end_date,
2436 'SCA_INV',
2437 p_org_id,
2438 x_size);
2439
2440 conc_dispatch(
2441 x_parent_proc_audit_id => l_process_audit_id,
2442 x_start_date => p_start_date,
2443 x_end_date => p_end_date,
2444 x_logical_batch_id => l_logical_batch_id,
2445 x_process => 'Rollback_data');
2446
2447 end if;
2448
2449 SELECT cn_sca_logical_batches_s.NEXTVAL
2450 INTO l_logical_batch_id
2451 FROM sys.dual;
2452
2453 debugmsg('Allocation Transfer : Get the set of transactions that needs to be transferred ');
2454 debugmsg('Allocation Transfer : Logical batch id = ' || l_logical_batch_id);
2455
2456 assign(l_logical_batch_id,
2457 p_start_date,
2458 p_end_date,
2459 'ORD',
2460 p_org_id,
2461 x_size_ord);
2462
2463 assign(l_logical_batch_id,
2464 p_start_date,
2465 p_end_date,
2466 'INV',
2467 p_org_id,
2468 x_size_inv);
2469
2470 if ((x_size_inv > 0) or (x_size_ord > 0)) then
2471
2472 conc_dispatch(
2473 x_parent_proc_audit_id => l_process_audit_id,
2474 x_start_date => p_start_date,
2475 x_end_date => p_end_date,
2476 x_logical_batch_id => l_logical_batch_id,
2477 x_process => 'Populate_data');
2478
2479 else
2480
2481 raise no_trx_lines;
2482
2483 end if;
2484
2485 cn_message_pkg.end_batch(l_process_audit_id);
2486
2487 EXCEPTION
2488
2489 WHEN no_trx_lines THEN
2490
2491 ROLLBACK TO populate_data_savepoint;
2492
2493 -- Call end_batch to end debug log file
2494 debugmsg('Allocation Transfer : No transactions to transfer');
2495 debugmsg('Allocation Transfer : End of Transfer');
2496
2497 conc_status := fnd_concurrent.set_completion_status(
2498 status => 'ERROR',
2499 message => '');
2500
2501 cn_message_pkg.end_batch(l_process_audit_id);
2502
2503 WHEN OTHERS THEN
2504
2505 ROLLBACK TO populate_data_savepoint;
2506
2507 debugmsg('Allocation Transfer : Unexpected exception');
2508 debugmsg('Allocation Transfer : End of Transfer');
2509
2510 conc_status := fnd_concurrent.set_completion_status(
2511 status => 'ERROR',
2512 message => '');
2513
2514 cn_message_pkg.end_batch(l_process_audit_id);
2515
2516 END populate_data;
2517
2518
2519 PROCEDURE call_populate_data (
2520 p_api_version IN NUMBER,
2521 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
2522 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2523 p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
2524 p_start_date IN DATE,
2525 p_end_date IN DATE,
2526 p_checkbox_value IN VARCHAR2,
2527 x_return_status OUT NOCOPY VARCHAR2,
2528 x_msg_count OUT NOCOPY NUMBER,
2529 x_msg_data OUT NOCOPY VARCHAR2,
2530 x_process_audit_id OUT NOCOPY NUMBER) IS
2531
2532 l_api_name CONSTANT VARCHAR2(30) := 'call_populate_data';
2533 l_api_version CONSTANT NUMBER := 1.0;
2534 l_checkbox_value CHAR(1) := 'Y';
2535 l_gen_status CN_REPOSITORIES.SCA_MAPPING_STATUS%TYPE;
2536
2537 BEGIN
2538
2539 -- Standard Start of API savepoint
2540 SAVEPOINT call_populate_data;
2541
2542 -- Standard call to check for call compatibility.
2543 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
2544 p_api_version ,
2545 l_api_name,
2546 G_PKG_NAME ) THEN
2547 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2548 END IF;
2549
2550 -- Initialize message list if p_init_msg_list is set to TRUE.
2551 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2552 FND_MSG_PUB.initialize;
2553 END IF;
2554
2555 -- Initialize API return status to success
2556 x_return_status := FND_API.G_RET_STS_SUCCESS;
2557
2558 IF (p_checkbox_value <> 'Y') THEN
2559 l_checkbox_value := 'N';
2560 END IF;
2561
2562 select sca_mapping_status into
2563 l_gen_status
2564 from cn_repositories;
2565
2566 IF (l_gen_status <> 'GENERATED') THEN
2567
2568 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
2569 FND_MESSAGE.SET_NAME ('CN' , 'CN_DYNAMIC_PKG_NOT_GEN');
2570 FND_MSG_PUB.Add;
2571 END IF;
2572 RAISE FND_API.G_EXC_ERROR ;
2573
2574 END IF;
2575
2576 x_process_audit_id :=
2577 FND_REQUEST.SUBMIT_REQUEST(
2578 application => 'CN',
2579 program => 'CN_SCA_POPULATE_DATA',
2580 argument1 => TO_CHAR(p_start_date,'YYYY/MM/DD HH24:MI:SS'),
2581 argument2 => TO_CHAR(p_end_date,'YYYY/MM/DD HH24:MI:SS'),
2582 argument3 => l_checkbox_value);
2583 commit;
2584
2585 EXCEPTION
2586 WHEN FND_API.G_EXC_ERROR THEN
2587
2588 ROLLBACK TO call_populate_data;
2589
2590 x_return_status := FND_API.G_RET_STS_ERROR ;
2591 FND_MSG_PUB.Count_And_Get(
2592 p_count => x_msg_count ,
2593 p_data => x_msg_data ,
2594 p_encoded => FND_API.G_FALSE);
2595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2596 ROLLBACK TO call_populate_data;
2597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2598 FND_MSG_PUB.Count_And_Get(
2599 p_count => x_msg_count ,
2600 p_data => x_msg_data ,
2601 p_encoded => FND_API.G_FALSE);
2602 WHEN OTHERS THEN
2603 ROLLBACK TO call_populate_data;
2604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2605 IF FND_MSG_PUB.Check_Msg_Level(
2606 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2607 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
2608 END IF;
2609 FND_MSG_PUB.Count_And_Get(
2610 p_count => x_msg_count ,
2611 p_data => x_msg_data ,
2612 p_encoded => FND_API.G_FALSE);
2613
2614 END call_populate_data;
2615
2616 Procedure check_api_adjusted (
2617 p_start_date DATE,
2618 p_end_date DATE,
2619 p_physical_batch_id NUMBER) IS
2620
2621 l_batch_type cn_sca_process_batches.type%TYPE;
2622 l_start_id cn_sca_process_batches.start_id%TYPE;
2623 l_end_id cn_sca_process_batches.end_id%TYPE;
2624
2625 conc_status boolean;
2626
2627 cursor api_adjust_lines_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2628
2629 select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2630 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2631 and CCLA.load_status = 'UNLOADED'
2632 and (--(CCLA.adjust_status is null) or
2633 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2634 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2635 and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
2636 and CCLA.line_number is not null
2637 and CCLA.invoice_number is not null
2638 and CCLA.invoice_number between l_start_id and l_end_id
2639 and exists
2640
2641 (SELECT 1
2642 from cn_sca_headers_interface CSHI
2643 where CSHI.transaction_status = 'ADJUSTED'
2644 and CSHI.source_id = CCLA.invoice_number
2645 and CSHI.source_type = 'INV');
2646
2647 cursor sca_adjust_headers_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2648
2649 select sca_headers_interface_id
2650 from cn_sca_headers_interface
2651 where source_type = 'INV'
2652 and source_id in
2653 (select invoice_number
2654 from
2655 (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2656 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2657 and CCLA.load_status = 'UNLOADED'
2658 and (--(CCLA.adjust_status is null) or
2659 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2660 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2661 and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
2662 and CCLA.line_number is not null
2663 and CCLA.invoice_number is not null
2664 and CCLA.invoice_number between l_start_id and l_end_id) SCA_SOURCE_ID
2665
2666 where exists
2667 (SELECT 1
2668 FROM cn_comm_lines_api CCLA_ORIG
2669 where CCLA_ORIG.adj_comm_lines_api_id in
2670
2671 (SELECT CSLI.source_trx_id
2672 from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
2673 where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
2674 and CSHI.source_id = SCA_SOURCE_ID.invoice_number
2675 and CSHI.source_type = 'INV')
2676
2677 and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2678 and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2679 (CCLA_ORIG.load_status = 'LOADED' and exists
2680
2681 (select 1 from cn_comm_lines_api CCLA
2682 where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2683 and CCLA.adjust_status = 'REVERSAL'
2684 and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2685 )))));
2686
2687 CURSOR sca_rollback_headers_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2688
2689 select sca_headers_interface_id
2690 from cn_sca_headers_interface
2691 where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2692 and source_type = 'INV'
2693 and source_id in
2694
2695 (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2696 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2697 and CCLA.load_status = 'UNLOADED'
2698 and (--(CCLA.adjust_status is null) or
2699 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2700 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2701 and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
2702 and CCLA.line_number is not null
2703 and CCLA.invoice_number is not null
2704 and CCLA.invoice_number between l_start_id and l_end_id);
2705
2706 CURSOR sca_rollback_lines_inv_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2707
2708 select CSLI.source_trx_id
2709 from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
2710 where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
2711 and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2712 and CSHI.source_type = 'INV'
2713 and CSHI.source_id in
2714
2715 (select distinct(invoice_number) invoice_number from cn_comm_lines_api CCLA
2716 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2717 and CCLA.load_status = 'UNLOADED'
2718 and (--(CCLA.adjust_status is null) or
2719 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2720 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2721 and ((CCLA.trx_type = 'INV') or (CCLA.trx_type = 'MAN'))
2722 and CCLA.line_number is not null
2723 and CCLA.invoice_number is not null
2724 and CCLA.invoice_number between l_start_id and l_end_id);
2725
2726 cursor api_adjust_lines_ord_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2727
2728 select CCLA.comm_lines_api_id from cn_comm_lines_api CCLA
2729 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2730 and CCLA.load_status = 'UNLOADED'
2731 and (--(CCLA.adjust_status is null) or
2732 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2733 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2734 and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
2735 and CCLA.line_number is not null
2736 and CCLA.invoice_number is null
2737 and CCLA.order_number is not null
2738 and CCLA.order_number between l_start_id and l_end_id
2739 and exists
2740
2741 (SELECT 1
2742 from cn_sca_headers_interface CSHI
2743 where CSHI.transaction_status = 'ADJUSTED'
2744 and CSHI.source_id = CCLA.order_number
2745 and CSHI.source_type = 'ORD');
2746
2747 cursor sca_adjust_headers_ord_cur (l_start_id VARCHAR2, l_end_id VARCHAR2) IS
2748
2749 select sca_headers_interface_id
2750 from cn_sca_headers_interface
2751 where source_type = 'ORD'
2752 and source_id in
2753 (select order_number
2754 from
2755 (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2756 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2757 and CCLA.load_status = 'UNLOADED'
2758 and (--(CCLA.adjust_status is null) or
2759 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2760 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2761 and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
2762 and CCLA.line_number is not null
2763 and CCLA.invoice_number is null
2764 and CCLA.order_number is not null
2765 and CCLA.order_number between l_start_id and l_end_id) SCA_SOURCE_ID
2766
2767 where exists
2768 (SELECT 1
2769 FROM cn_comm_lines_api CCLA_ORIG
2770 where CCLA_ORIG.adj_comm_lines_api_id in
2771
2772 (SELECT CSLI.source_trx_id
2773 from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
2774 where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
2775 and CSHI.source_id = SCA_SOURCE_ID.order_number
2776 and CSHI.source_type = 'ORD')
2777
2778 and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
2779 and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
2780 (CCLA_ORIG.load_status = 'LOADED' and exists
2781
2782 (select 1 from cn_comm_lines_api CCLA
2783 where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
2784 and CCLA.adjust_status = 'REVERSAL'
2785 and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
2786 )))));
2787
2788 CURSOR sca_rollback_headers_ord_cur (start_id VARCHAR2, end_id VARCHAR2) IS
2789
2790 select sca_headers_interface_id
2791 from cn_sca_headers_interface
2792 where ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
2793 and source_type = 'ORD'
2794 and source_id in
2795
2796 (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2797 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2798 and CCLA.load_status = 'UNLOADED'
2799 and (--(CCLA.adjust_status is null) or
2800 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2801 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2802 and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
2803 and CCLA.line_number is not null
2804 and CCLA.invoice_number is null
2805 and CCLA.order_number is not null
2806 and CCLA.order_number between l_start_id and l_end_id);
2807
2808 CURSOR sca_rollback_lines_ord_cur (start_id VARCHAR2, end_id VARCHAR2) IS
2809
2810 select CSLI.source_trx_id
2811 from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
2812 where CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id
2813 and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
2814 and CSHI.source_type = 'ORD'
2815 and CSHI.source_id in
2816
2817 (select distinct(order_number) order_number from cn_comm_lines_api CCLA
2818 where trunc(CCLA.processed_date) between trunc(p_start_date) and trunc(p_end_date)
2819 and CCLA.load_status = 'UNLOADED'
2820 and (--(CCLA.adjust_status is null) or
2821 (CCLA.adjust_status not in ('SCA_PENDING', 'SCA_ALLOCATED', 'SCA_NO_RULE', 'SCA_NOT_ALLOCATED',
2822 'SCA_NOT_ELIGIBLE', 'REVERSAL', 'FROZEN')))
2823 and ((CCLA.trx_type = 'ORD') or (CCLA.trx_type = 'MAN'))
2824 and CCLA.line_number is not null
2825 and CCLA.invoice_number is null
2826 and CCLA.order_number is not null
2827 and CCLA.order_number between l_start_id and l_end_id);
2828
2829 TYPE sca_adjust_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2830 TYPE api_adjust_lines_tbl IS TABLE OF cn_comm_lines_api.comm_lines_api_id%TYPE;
2831 TYPE sca_rollback_lines_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
2832 TYPE sca_rollback_headers_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
2833
2834 sca_adjust_headers sca_adjust_headers_tbl;
2835 api_adjust_lines api_adjust_lines_tbl;
2836 sca_rollback_lines sca_rollback_lines_tbl;
2837 sca_rollback_headers sca_rollback_headers_tbl;
2838 l_adjusted_by VARCHAR2(30);
2839
2840 BEGIN
2841
2842 l_adjusted_by := get_adjusted_by;
2843
2844 select start_id, end_id, type into
2845 l_start_id, l_end_id, l_batch_type
2846 from cn_sca_process_batches
2847 where sca_process_batch_id = p_physical_batch_id;
2848
2849 debugmsg('Check_comm_lines_api_adjusted : Check if the transactions are eligible for SCA');
2850
2851 if (l_batch_type = 'INV') then
2852
2853 OPEN sca_adjust_headers_inv_cur (l_start_id, l_end_id);
2854 FETCH sca_adjust_headers_inv_cur BULK COLLECT INTO sca_adjust_headers limit 1000;
2855
2856 end if;
2857
2858 if (l_batch_type = 'ORD') then
2859
2860 OPEN sca_adjust_headers_ord_cur (l_start_id, l_end_id);
2861 FETCH sca_adjust_headers_ord_cur BULK COLLECT INTO sca_adjust_headers limit 1000;
2862
2863 end if;
2864
2865 FORALL j IN 1..sca_adjust_headers.COUNT
2866
2867 UPDATE cn_sca_headers_interface
2868 SET transaction_status = 'ADJUSTED'
2869 WHERE sca_headers_interface_id = sca_adjust_headers(j);
2870
2871 debugmsg('Check_comm_lines_api_adjusted : Update the adjust_status of transactions that are not eligible for SCA');
2872
2873 if (l_batch_type = 'INV') then
2874
2875 OPEN api_adjust_lines_inv_cur (l_start_id, l_end_id);
2876 FETCH api_adjust_lines_inv_cur BULK COLLECT INTO api_adjust_lines limit 1000;
2877
2878 OPEN sca_rollback_headers_inv_cur (l_start_id, l_end_id);
2879 FETCH sca_rollback_headers_inv_cur BULK COLLECT INTO sca_rollback_headers limit 1000;
2880
2881 OPEN sca_rollback_lines_inv_cur (l_start_id, l_end_id);
2882 FETCH sca_rollback_lines_inv_cur BULK COLLECT INTO sca_rollback_lines limit 1000;
2883
2884 end if;
2885
2886 if (l_batch_type = 'ORD') then
2887
2888 OPEN api_adjust_lines_ord_cur (l_start_id, l_end_id);
2889 FETCH api_adjust_lines_ord_cur BULK COLLECT INTO api_adjust_lines limit 1000;
2890
2891 OPEN sca_rollback_headers_ord_cur (l_start_id, l_end_id);
2892 FETCH sca_rollback_headers_ord_cur BULK COLLECT INTO sca_rollback_headers limit 1000;
2893
2894 OPEN sca_rollback_lines_ord_cur (l_start_id, l_end_id);
2895 FETCH sca_rollback_lines_ord_cur BULK COLLECT INTO sca_rollback_lines limit 1000;
2896
2897 end if;
2898
2899 FORALL j IN 1..api_adjust_lines.COUNT
2900
2901 UPDATE cn_comm_lines_api
2902 SET adjust_status = 'SCA_NOT_ELIGIBLE',
2903 adjust_date = sysdate,
2904 adjusted_by = l_adjusted_by,
2905 adjust_comments = 'SCA Check'
2906 WHERE comm_lines_api_id = api_adjust_lines(j);
2907
2908 debugmsg('Check_comm_lines_api_adjusted : Update the adjust_status of transactions that are not eligible for SCA');
2909
2910 debugmsg('Check_comm_lines_api_adjusted : Roll back previous SCA results ');
2911
2912 FORALL j IN 1..sca_rollback_lines.COUNT
2913
2914 UPDATE cn_comm_lines_api API
2915 SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
2916 adjust_status = NVL(DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),'NEW'),
2917 adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
2918 adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
2919 adjust_comments = DECODE(API.load_status, 'UNLOADED', 'SCA_ROLLBACK', API.adjust_comments)
2920 WHERE adj_comm_lines_api_id = sca_rollback_lines(j);
2921
2922 debugmsg('Check_comm_lines_api_adjusted : Obsoleting unloaded transactions that have been created for previous SCA results');
2923
2924 FORALL j IN 1..sca_rollback_lines.COUNT
2925
2926 INSERT into CN_COMM_LINES_API
2927 ( SALESREP_ID,
2928 PROCESSED_DATE,
2929 PROCESSED_PERIOD_ID,
2930 TRANSACTION_AMOUNT,
2931 TRX_TYPE,
2932 REVENUE_CLASS_ID,
2933 LOAD_STATUS,
2934 ATTRIBUTE_CATEGORY,
2935 ATTRIBUTE1,
2936 ATTRIBUTE2,
2937 ATTRIBUTE3,
2938 ATTRIBUTE4,
2939 ATTRIBUTE5,
2940 ATTRIBUTE6,
2941 ATTRIBUTE7,
2942 ATTRIBUTE8,
2943 ATTRIBUTE9,
2944 ATTRIBUTE10,
2945 ATTRIBUTE11,
2946 ATTRIBUTE12,
2947 ATTRIBUTE13,
2948 ATTRIBUTE14,
2949 ATTRIBUTE15,
2950 ATTRIBUTE16,
2951 ATTRIBUTE17,
2952 ATTRIBUTE18,
2953 ATTRIBUTE19,
2954 ATTRIBUTE20,
2955 ATTRIBUTE21,
2956 ATTRIBUTE22,
2957 ATTRIBUTE23,
2958 ATTRIBUTE24,
2959 ATTRIBUTE25,
2960 ATTRIBUTE26,
2961 ATTRIBUTE27,
2962 ATTRIBUTE28,
2963 ATTRIBUTE29,
2964 ATTRIBUTE30,
2965 ATTRIBUTE31,
2966 ATTRIBUTE32,
2967 ATTRIBUTE33,
2968 ATTRIBUTE34,
2969 ATTRIBUTE35,
2970 ATTRIBUTE36,
2971 ATTRIBUTE37,
2972 ATTRIBUTE38,
2973 ATTRIBUTE39,
2974 ATTRIBUTE40,
2975 ATTRIBUTE41,
2976 ATTRIBUTE42,
2977 ATTRIBUTE43,
2978 ATTRIBUTE44,
2979 ATTRIBUTE45,
2980 ATTRIBUTE46,
2981 ATTRIBUTE47,
2982 ATTRIBUTE48,
2983 ATTRIBUTE49,
2984 ATTRIBUTE50,
2985 ATTRIBUTE51,
2986 ATTRIBUTE52,
2987 ATTRIBUTE53,
2988 ATTRIBUTE54,
2989 ATTRIBUTE55,
2990 ATTRIBUTE56,
2991 ATTRIBUTE57,
2992 ATTRIBUTE58,
2993 ATTRIBUTE59,
2994 ATTRIBUTE60,
2995 ATTRIBUTE61,
2996 ATTRIBUTE62,
2997 ATTRIBUTE63,
2998 ATTRIBUTE64,
2999 ATTRIBUTE65,
3000 ATTRIBUTE66,
3001 ATTRIBUTE67,
3002 ATTRIBUTE68,
3003 ATTRIBUTE69,
3004 ATTRIBUTE70,
3005 ATTRIBUTE71,
3006 ATTRIBUTE72,
3007 ATTRIBUTE73,
3008 ATTRIBUTE74,
3009 ATTRIBUTE75,
3010 ATTRIBUTE76,
3011 ATTRIBUTE77,
3012 ATTRIBUTE78,
3013 ATTRIBUTE79,
3014 ATTRIBUTE80,
3015 ATTRIBUTE81,
3016 ATTRIBUTE82,
3017 ATTRIBUTE83,
3018 ATTRIBUTE84,
3019 ATTRIBUTE85,
3020 ATTRIBUTE86,
3021 ATTRIBUTE87,
3022 ATTRIBUTE88,
3023 ATTRIBUTE89,
3024 ATTRIBUTE90,
3025 ATTRIBUTE91,
3026 ATTRIBUTE92,
3027 ATTRIBUTE93,
3028 ATTRIBUTE94,
3029 ATTRIBUTE95,
3030 ATTRIBUTE96,
3031 ATTRIBUTE97,
3032 ATTRIBUTE98,
3033 ATTRIBUTE99,
3034 ATTRIBUTE100,
3035 COMM_LINES_API_ID,
3036 CONC_BATCH_ID,
3037 PROCESS_BATCH_ID,
3038 SALESREP_NUMBER,
3039 ROLLUP_DATE,
3040 SOURCE_DOC_ID,
3041 SOURCE_DOC_TYPE,
3042 CREATED_BY,
3043 CREATION_DATE,
3044 LAST_UPDATED_BY,
3045 LAST_UPDATE_DATE,
3046 LAST_UPDATE_LOGIN,
3047 TRANSACTION_CURRENCY_CODE,
3048 EXCHANGE_RATE,
3049 ACCTD_TRANSACTION_AMOUNT,
3050 TRX_ID,
3051 TRX_LINE_ID,
3052 TRX_SALES_LINE_ID,
3053 QUANTITY,
3054 SOURCE_TRX_NUMBER,
3055 DISCOUNT_PERCENTAGE,
3056 MARGIN_PERCENTAGE,
3057 SOURCE_TRX_ID,
3058 SOURCE_TRX_LINE_ID,
3059 SOURCE_TRX_SALES_LINE_ID,
3060 NEGATED_FLAG,
3061 CUSTOMER_ID,
3062 INVENTORY_ITEM_ID,
3063 ORDER_NUMBER,
3064 BOOKED_DATE,
3065 INVOICE_NUMBER,
3066 INVOICE_DATE,
3067 ADJUST_DATE,
3068 ADJUSTED_BY,
3069 REVENUE_TYPE,
3070 ADJUST_ROLLUP_FLAG,
3071 ADJUST_COMMENTS,
3072 ADJUST_STATUS,
3073 LINE_NUMBER,
3074 BILL_TO_ADDRESS_ID,
3075 SHIP_TO_ADDRESS_ID,
3076 BILL_TO_CONTACT_ID,
3077 SHIP_TO_CONTACT_ID,
3078 ADJ_COMM_LINES_API_ID,
3079 PRE_DEFINED_RC_FLAG,
3080 ROLLUP_FLAG,
3081 FORECAST_ID,
3082 UPSIDE_QUANTITY,
3083 UPSIDE_AMOUNT,
3084 UOM_CODE,
3085 REASON_CODE,
3086 TYPE,
3087 PRE_PROCESSED_CODE,
3088 QUOTA_ID,
3089 SRP_PLAN_ASSIGN_ID,
3090 ROLE_ID,
3091 COMP_GROUP_ID,
3092 COMMISSION_AMOUNT,
3093 EMPLOYEE_NUMBER,
3094 REVERSAL_FLAG,
3095 REVERSAL_HEADER_ID,
3096 SALES_CHANNEL,
3097 OBJECT_VERSION_NUMBER,
3098 SPLIT_PCT,
3099 SPLIT_status,
3100 org_id)
3101
3102 (select
3103 CCH.DIRECT_SALESREP_ID,
3104 CCH.PROCESSED_DATE,
3105 CCH.PROCESSED_PERIOD_ID,
3106 -1 * NVL(CCH.TRANSACTION_AMOUNT_ORIG, 0),
3107 CCH.TRX_TYPE,
3108 CCH.REVENUE_CLASS_ID,
3109 'UNLOADED',
3110 CCH.ATTRIBUTE_CATEGORY,
3111 CCH.ATTRIBUTE1,
3112 CCH.ATTRIBUTE2,
3113 CCH.ATTRIBUTE3,
3114 CCH.ATTRIBUTE4,
3115 CCH.ATTRIBUTE5,
3116 CCH.ATTRIBUTE6,
3117 CCH.ATTRIBUTE7,
3118 CCH.ATTRIBUTE8,
3119 CCH.ATTRIBUTE9,
3120 CCH.ATTRIBUTE10,
3121 CCH.ATTRIBUTE11,
3122 CCH.ATTRIBUTE12,
3123 CCH.ATTRIBUTE13,
3124 CCH.ATTRIBUTE14,
3125 CCH.ATTRIBUTE15,
3126 CCH.ATTRIBUTE16,
3127 CCH.ATTRIBUTE17,
3128 CCH.ATTRIBUTE18,
3129 CCH.ATTRIBUTE19,
3130 CCH.ATTRIBUTE20,
3131 CCH.ATTRIBUTE21,
3132 CCH.ATTRIBUTE22,
3133 CCH.ATTRIBUTE23,
3134 CCH.ATTRIBUTE24,
3135 CCH.ATTRIBUTE25,
3136 CCH.ATTRIBUTE26,
3137 CCH.ATTRIBUTE27,
3138 CCH.ATTRIBUTE28,
3139 CCH.ATTRIBUTE29,
3140 CCH.ATTRIBUTE30,
3141 CCH.ATTRIBUTE31,
3142 CCH.ATTRIBUTE32,
3143 CCH.ATTRIBUTE33,
3144 CCH.ATTRIBUTE34,
3145 CCH.ATTRIBUTE35,
3146 CCH.ATTRIBUTE36,
3147 CCH.ATTRIBUTE37,
3148 CCH.ATTRIBUTE38,
3149 CCH.ATTRIBUTE39,
3150 CCH.ATTRIBUTE40,
3151 CCH.ATTRIBUTE41,
3152 CCH.ATTRIBUTE42,
3153 CCH.ATTRIBUTE43,
3154 CCH.ATTRIBUTE44,
3155 CCH.ATTRIBUTE45,
3156 CCH.ATTRIBUTE46,
3157 CCH.ATTRIBUTE47,
3158 CCH.ATTRIBUTE48,
3159 CCH.ATTRIBUTE49,
3160 CCH.ATTRIBUTE50,
3161 CCH.ATTRIBUTE51,
3162 CCH.ATTRIBUTE52,
3163 CCH.ATTRIBUTE53,
3164 CCH.ATTRIBUTE54,
3165 CCH.ATTRIBUTE55,
3166 CCH.ATTRIBUTE56,
3167 CCH.ATTRIBUTE57,
3168 CCH.ATTRIBUTE58,
3169 CCH.ATTRIBUTE59,
3170 CCH.ATTRIBUTE60,
3171 CCH.ATTRIBUTE61,
3172 CCH.ATTRIBUTE62,
3173 CCH.ATTRIBUTE63,
3174 CCH.ATTRIBUTE64,
3175 CCH.ATTRIBUTE65,
3176 CCH.ATTRIBUTE66,
3177 CCH.ATTRIBUTE67,
3178 CCH.ATTRIBUTE68,
3179 CCH.ATTRIBUTE69,
3180 CCH.ATTRIBUTE70,
3181 CCH.ATTRIBUTE71,
3182 CCH.ATTRIBUTE72,
3183 CCH.ATTRIBUTE73,
3184 CCH.ATTRIBUTE74,
3185 CCH.ATTRIBUTE75,
3186 CCH.ATTRIBUTE76,
3187 CCH.ATTRIBUTE77,
3188 CCH.ATTRIBUTE78,
3189 CCH.ATTRIBUTE79,
3190 CCH.ATTRIBUTE80,
3191 CCH.ATTRIBUTE81,
3192 CCH.ATTRIBUTE82,
3193 CCH.ATTRIBUTE83,
3194 CCH.ATTRIBUTE84,
3195 CCH.ATTRIBUTE85,
3196 CCH.ATTRIBUTE86,
3197 CCH.ATTRIBUTE87,
3198 CCH.ATTRIBUTE88,
3199 CCH.ATTRIBUTE89,
3200 CCH.ATTRIBUTE90,
3201 CCH.ATTRIBUTE91,
3202 CCH.ATTRIBUTE92,
3203 CCH.ATTRIBUTE93,
3204 CCH.ATTRIBUTE94,
3205 CCH.ATTRIBUTE95,
3206 CCH.ATTRIBUTE96,
3207 CCH.ATTRIBUTE97,
3208 CCH.ATTRIBUTE98,
3209 CCH.ATTRIBUTE99,
3210 CCH.ATTRIBUTE100,
3211 cn_comm_lines_api_s.NEXTVAL,
3212 NULL,
3213 NULL,
3214 NULL,
3215 CCH.ROLLUP_DATE,
3216 NULL,
3217 CCH.SOURCE_DOC_TYPE,
3218 fnd_global.user_id,
3219 Sysdate,
3220 fnd_global.user_id,
3221 Sysdate,
3222 fnd_global.login_id,
3223 CCH.ORIG_CURRENCY_CODE,
3224 CCH.EXCHANGE_RATE,
3225 -1 * NVL(CCH.TRANSACTION_AMOUNT, 0),
3226 NULL, -- CCH.TRX_ID,
3227 NULL, -- CCH.TRX_LINE_ID,
3228 NULL, -- CCH.TRX_SALES_LINE_ID,
3229 -1 * CCH.QUANTITY,
3230 CCH.SOURCE_TRX_NUMBER,
3231 CCH.DISCOUNT_PERCENTAGE,
3232 CCH.MARGIN_PERCENTAGE,
3233 CCH.SOURCE_TRX_ID,
3234 CCH.SOURCE_TRX_LINE_ID,
3235 CCH.SOURCE_TRX_SALES_LINE_ID,
3236 'Y',
3237 CCH.CUSTOMER_ID,
3238 CCH.INVENTORY_ITEM_ID,
3239 CCH.ORDER_NUMBER,
3240 CCH.BOOKED_DATE,
3241 CCH.INVOICE_NUMBER,
3242 CCH.INVOICE_DATE,
3243 SYSDATE,
3244 l_adjusted_by,
3245 CCH.REVENUE_TYPE,
3246 CCH.ADJUST_ROLLUP_FLAG,
3247 'SCA_ROLLBACK',
3248 'REVERSAL',
3249 CCH.LINE_NUMBER,
3250 CCH.BILL_TO_ADDRESS_ID,
3251 CCH.SHIP_TO_ADDRESS_ID,
3252 CCH.BILL_TO_CONTACT_ID,
3253 CCH.SHIP_TO_CONTACT_ID,
3254 CCH.COMM_LINES_API_ID,
3255 NULL, -- CCH.PRE_DEFINED_RC_FLAG,
3256 NULL, -- CCH.ROLLUP_FLAG,
3257 CCH.FORECAST_ID,
3258 CCH.UPSIDE_QUANTITY,
3259 CCH.UPSIDE_AMOUNT,
3260 CCH.UOM_CODE,
3261 CCH.REASON_CODE,
3262 CCH.TYPE,
3263 CCH.PRE_PROCESSED_CODE,
3264 CCH.QUOTA_ID,
3265 CCH.SRP_PLAN_ASSIGN_ID,
3266 CCH.ROLE_ID,
3267 CCH.COMP_GROUP_ID,
3268 CCH.COMMISSION_AMOUNT,
3269 CS.EMPLOYEE_NUMBER,
3270 'Y',
3271 CCH.COMMISSION_HEADER_ID,
3272 CCH.SALES_CHANNEL,
3273 CCH.OBJECT_VERSION_NUMBER,
3274 CCH.SPLIT_PCT,
3275 CCH.SPLIT_status,
3276 cch.org_id
3277 FROM cn_commission_headers CCH, cn_salesreps CS
3278 WHERE CCH.adj_comm_lines_api_id = sca_rollback_lines(j)
3279 AND CS.salesrep_id = CCH.direct_salesrep_id
3280 AND (--(CCH.adjust_status IS NULL) or
3281 (CCH.adjust_status <> 'FROZEN')));
3282
3283 debugmsg('Check_comm_lines_api_adjusted : Creating reversals for loaded transactions that have been created for previous SCA results');
3284
3285 FORALL j IN 1..sca_rollback_lines.COUNT
3286
3287 UPDATE cn_commission_headers CSH
3288 SET adjust_status = 'FROZEN',
3289 reversal_header_id = CSH.commission_header_id,
3290 reversal_flag = 'Y',
3291 adjust_date = sysdate,
3292 adjusted_by = l_adjusted_by,
3293 adjust_comments = 'SCA_ROLLBACK'
3294 WHERE adj_comm_lines_api_id = sca_rollback_lines(j)
3295 AND (--(adjust_status IS NULL) or
3296 (adjust_status <> 'FROZEN'));
3297
3298 debugmsg('Check_comm_lines_api_adjusted : Obsoleting loaded transactions that have been created for previous SCA results');
3299
3300 FORALL j IN 1..sca_rollback_lines.COUNT
3301
3302 UPDATE cn_comm_lines_api
3303 SET adjust_status = 'NEW', --NULL,
3304 load_status = 'UNLOADED',
3305 adjust_date = sysdate,
3306 adjusted_by = l_adjusted_by,
3307 adjust_comments = 'SCA_ROLLBACK'
3308 WHERE comm_lines_api_id = sca_rollback_lines(j);
3309
3310 debugmsg('Check_comm_lines_api_adjusted : Set the original transactions back to active');
3311
3312 FORALL j IN 1..sca_rollback_headers.COUNT
3313
3314 DELETE FROM cn_sca_lines_interface
3315 WHERE sca_headers_interface_id = sca_rollback_headers(j);
3316
3317 FORALL j IN 1..sca_rollback_headers.COUNT
3318
3319 DELETE FROM cn_sca_lines_output
3320 WHERE sca_headers_interface_id = sca_rollback_headers(j);
3321
3322 FORALL j IN 1..sca_rollback_headers.COUNT
3323
3324 DELETE FROM cn_sca_headers_interface
3325 WHERE sca_headers_interface_id = sca_rollback_headers(j);
3326
3327 debugmsg('Check_comm_lines_api_adjusted : Removing records from SCA headers, lines and output table');
3328 debugmsg('Check_comm_lines_api_adjusted : End of checking if transactions are eligible for SCA');
3329
3330 EXCEPTION
3331
3332 WHEN OTHERS THEN
3333
3334 debugmsg('Check_comm_lines_api_adjusted : Checking if transactions are eligible for SCA : Unexpected exception');
3335 debugmsg('Check_comm_lines_api_adjusted : End of checking if transactions are eligible for SCA');
3336
3337 conc_status := fnd_concurrent.set_completion_status(
3338 status => 'ERROR',
3339 message => '');
3340
3341 RAISE;
3342
3343 end check_api_adjusted;
3344
3345
3346 Procedure check_adjusted (
3347 p_start_date DATE,
3348 p_end_date DATE,
3349 p_physical_batch_id NUMBER) IS
3350
3351 cursor sca_headers_adjust_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3352
3353 select sca_headers_interface_id
3354 from cn_sca_headers_interface
3355 where source_type = l_trx_type
3356 and source_id in
3357 (select source_id
3358 from
3359 (select distinct(source_id) source_id
3360 from cn_sca_headers_interface
3361 where source_type = l_trx_type
3362 and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
3363 and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
3364 and source_id between l_start_id and l_end_id) SCA_SOURCE_ID
3365
3366 where exists
3367 (SELECT 1
3368 FROM cn_comm_lines_api CCLA_ORIG
3369 where CCLA_ORIG.adj_comm_lines_api_id in
3370
3371 (SELECT CSLI.source_trx_id
3372 from cn_sca_lines_interface CSLI, cn_sca_headers_interface CSHI
3373 where CSLI.sca_headers_interface_id = CSHI.sca_headers_interface_id
3374 and CSHI.source_id = SCA_SOURCE_ID.source_id
3375 and CSHI.source_type = l_trx_type)
3376
3377 and ((CCLA_ORIG.adjust_status = 'FROZEN' and CCLA_ORIG.load_status = 'OBSOLETE'
3378 and (CCLA_ORIG.adjust_comments is null or CCLA_ORIG.adjust_comments <> 'SCA_ROLLBACK')) or
3379 (CCLA_ORIG.load_status = 'LOADED' and exists
3380
3381 (select 1 from cn_comm_lines_api CCLA
3382 where CCLA.adj_comm_lines_api_id = CCLA_ORIG.comm_lines_api_id
3383 and CCLA.adjust_status = 'REVERSAL'
3384 and (CCLA.adjust_comments is null or CCLA.adjust_comments <> 'SCA_ROLLBACK')
3385 )))));
3386
3387
3388 cursor sca_headers_rollback_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3389
3390 select sca_headers_interface_id
3391 from cn_sca_headers_interface
3392 where source_type = l_trx_type
3393 and trunc(processed_date) between trunc(p_start_date) and trunc(p_end_date)
3394 and ((transaction_status is null) or (transaction_status <> 'ADJUSTED'))
3395 and source_id between l_start_id and l_end_id;
3396
3397 cursor sca_lines_rollback_cur (l_trx_type VARCHAR2, l_start_id VARCHAR2, l_end_id VARCHAR2) IS
3398
3399 select CSLI.source_trx_id
3400 from cn_sca_headers_interface CSHI, cn_sca_lines_interface CSLI
3401 where CSHI.source_type = l_trx_type
3402 and trunc(CSHI.processed_date) between trunc(p_start_date) and trunc(p_end_date)
3403 and ((CSHI.transaction_status is null) or (CSHI.transaction_status <> 'ADJUSTED'))
3404 and CSHI.source_id between l_start_id and l_end_id
3405 and CSHI.sca_headers_interface_id = CSLI.sca_headers_interface_id;
3406
3407
3408 TYPE sca_headers_adjust_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3409 TYPE sca_headers_rollback_tbl IS TABLE OF cn_sca_headers_interface.sca_headers_interface_id%TYPE;
3410 TYPE sca_lines_rollback_tbl IS TABLE OF cn_sca_lines_interface.source_trx_id%TYPE;
3411
3412 sca_headers_adjust sca_headers_adjust_tbl;
3413 sca_headers_rollback sca_headers_rollback_tbl;
3414 sca_lines_rollback sca_lines_rollback_tbl;
3415
3416 l_sql_stmt VARCHAR2(1000);
3417 l_trx_type VARCHAR2(10);
3418
3419 l_batch_type cn_sca_process_batches.type%TYPE;
3420 l_start_id cn_sca_process_batches.start_id%TYPE;
3421 l_end_id cn_sca_process_batches.end_id%TYPE;
3422 l_source_id cn_sca_headers_interface.source_id%TYPE;
3423
3424 l_adjusted_by VARCHAR2(30);
3425 conc_status boolean;
3426
3427 BEGIN
3428
3429 l_adjusted_by := get_adjusted_by;
3430
3431 select start_id, end_id, type into
3432 l_start_id, l_end_id, l_batch_type
3433 from cn_sca_process_batches
3434 where sca_process_batch_id = p_physical_batch_id;
3435
3436 if (l_batch_type = 'SCA_ORD') then
3437
3438 l_batch_type := 'ORD';
3439
3440 end if;
3441
3442 if (l_batch_type = 'SCA_INV') then
3443
3444 l_batch_type := 'INV';
3445
3446 end if;
3447
3448 debugmsg('Allocation Transfer : Start of checking if headers can be rerun ');
3449 debugmsg('Allocation Transfer : Rerun : Batch Type = ' || l_batch_type);
3450
3451 OPEN sca_headers_adjust_cur (l_batch_type, l_start_id, l_end_id);
3452 FETCH sca_headers_adjust_cur BULK COLLECT INTO sca_headers_adjust limit 1000;
3453
3454 debugmsg('Allocation Transfer : Rerun : Mark those headers that have been populated and adjusted');
3455
3456 FORALL j IN 1..sca_headers_adjust.COUNT
3457
3458 UPDATE cn_sca_headers_interface
3459 SET transaction_status = 'ADJUSTED'
3460 WHERE sca_headers_interface_id = sca_headers_adjust(j);
3461
3462 OPEN sca_headers_rollback_cur (l_batch_type, l_start_id, l_end_id);
3463 FETCH sca_headers_rollback_cur BULK COLLECT INTO sca_headers_rollback limit 1000;
3464
3465 OPEN sca_lines_rollback_cur (l_batch_type, l_start_id, l_end_id);
3466 FETCH sca_lines_rollback_cur BULK COLLECT INTO sca_lines_rollback limit 1000;
3467
3468 debugmsg('Allocation Transfer : Rerun : Obsoleting unloaded transactions that have been created for previous SCA results');
3469
3470 FORALL j IN 1..sca_lines_rollback.COUNT
3471
3472 UPDATE cn_comm_lines_api API
3473 SET load_status = DECODE(API.load_status, 'UNLOADED', 'OBSOLETE', API.load_status),
3474 adjust_status = NVL(DECODE(API.load_status, 'UNLOADED', 'FROZEN', API.adjust_status),'NEW'),
3475 adjust_date = DECODE(API.load_status, 'UNLOADED', sysdate, API.adjust_date),
3476 adjusted_by = DECODE(API.load_status, 'UNLOADED', l_adjusted_by, API.adjusted_by),
3477 adjust_comments = DECODE(API.load_status, 'UNLOADED', 'SCA_ROLLBACK', API.adjust_comments)
3478 WHERE adj_comm_lines_api_id = sca_lines_rollback(j);
3479
3480 debugmsg('Allocation Transfer : Rerun : Creating reversals for loaded transactions that have been created for previous SCA results');
3481
3482 FORALL j IN 1..sca_lines_rollback.COUNT
3483
3484 INSERT into CN_COMM_LINES_API
3485 ( SALESREP_ID,
3486 PROCESSED_DATE,
3487 PROCESSED_PERIOD_ID,
3488 TRANSACTION_AMOUNT,
3489 TRX_TYPE,
3490 REVENUE_CLASS_ID,
3491 LOAD_STATUS,
3492 ATTRIBUTE_CATEGORY,
3493 ATTRIBUTE1,
3494 ATTRIBUTE2,
3495 ATTRIBUTE3,
3496 ATTRIBUTE4,
3497 ATTRIBUTE5,
3498 ATTRIBUTE6,
3499 ATTRIBUTE7,
3500 ATTRIBUTE8,
3501 ATTRIBUTE9,
3502 ATTRIBUTE10,
3503 ATTRIBUTE11,
3504 ATTRIBUTE12,
3505 ATTRIBUTE13,
3506 ATTRIBUTE14,
3507 ATTRIBUTE15,
3508 ATTRIBUTE16,
3509 ATTRIBUTE17,
3510 ATTRIBUTE18,
3511 ATTRIBUTE19,
3512 ATTRIBUTE20,
3513 ATTRIBUTE21,
3514 ATTRIBUTE22,
3515 ATTRIBUTE23,
3516 ATTRIBUTE24,
3517 ATTRIBUTE25,
3518 ATTRIBUTE26,
3519 ATTRIBUTE27,
3520 ATTRIBUTE28,
3521 ATTRIBUTE29,
3522 ATTRIBUTE30,
3523 ATTRIBUTE31,
3524 ATTRIBUTE32,
3525 ATTRIBUTE33,
3526 ATTRIBUTE34,
3527 ATTRIBUTE35,
3528 ATTRIBUTE36,
3529 ATTRIBUTE37,
3530 ATTRIBUTE38,
3531 ATTRIBUTE39,
3532 ATTRIBUTE40,
3533 ATTRIBUTE41,
3534 ATTRIBUTE42,
3535 ATTRIBUTE43,
3536 ATTRIBUTE44,
3537 ATTRIBUTE45,
3538 ATTRIBUTE46,
3539 ATTRIBUTE47,
3540 ATTRIBUTE48,
3541 ATTRIBUTE49,
3542 ATTRIBUTE50,
3543 ATTRIBUTE51,
3544 ATTRIBUTE52,
3545 ATTRIBUTE53,
3546 ATTRIBUTE54,
3547 ATTRIBUTE55,
3548 ATTRIBUTE56,
3549 ATTRIBUTE57,
3550 ATTRIBUTE58,
3551 ATTRIBUTE59,
3552 ATTRIBUTE60,
3553 ATTRIBUTE61,
3554 ATTRIBUTE62,
3555 ATTRIBUTE63,
3556 ATTRIBUTE64,
3557 ATTRIBUTE65,
3558 ATTRIBUTE66,
3559 ATTRIBUTE67,
3560 ATTRIBUTE68,
3561 ATTRIBUTE69,
3562 ATTRIBUTE70,
3563 ATTRIBUTE71,
3564 ATTRIBUTE72,
3565 ATTRIBUTE73,
3566 ATTRIBUTE74,
3567 ATTRIBUTE75,
3568 ATTRIBUTE76,
3569 ATTRIBUTE77,
3570 ATTRIBUTE78,
3571 ATTRIBUTE79,
3572 ATTRIBUTE80,
3573 ATTRIBUTE81,
3574 ATTRIBUTE82,
3575 ATTRIBUTE83,
3576 ATTRIBUTE84,
3577 ATTRIBUTE85,
3578 ATTRIBUTE86,
3579 ATTRIBUTE87,
3580 ATTRIBUTE88,
3581 ATTRIBUTE89,
3582 ATTRIBUTE90,
3583 ATTRIBUTE91,
3584 ATTRIBUTE92,
3585 ATTRIBUTE93,
3586 ATTRIBUTE94,
3587 ATTRIBUTE95,
3588 ATTRIBUTE96,
3589 ATTRIBUTE97,
3590 ATTRIBUTE98,
3591 ATTRIBUTE99,
3592 ATTRIBUTE100,
3593 COMM_LINES_API_ID,
3594 CONC_BATCH_ID,
3595 PROCESS_BATCH_ID,
3596 SALESREP_NUMBER,
3597 ROLLUP_DATE,
3598 SOURCE_DOC_ID,
3599 SOURCE_DOC_TYPE,
3600 CREATED_BY,
3601 CREATION_DATE,
3602 LAST_UPDATED_BY,
3603 LAST_UPDATE_DATE,
3604 LAST_UPDATE_LOGIN,
3605 TRANSACTION_CURRENCY_CODE,
3606 EXCHANGE_RATE,
3607 ACCTD_TRANSACTION_AMOUNT,
3608 TRX_ID,
3609 TRX_LINE_ID,
3610 TRX_SALES_LINE_ID,
3611 QUANTITY,
3612 SOURCE_TRX_NUMBER,
3613 DISCOUNT_PERCENTAGE,
3614 MARGIN_PERCENTAGE,
3615 SOURCE_TRX_ID,
3616 SOURCE_TRX_LINE_ID,
3617 SOURCE_TRX_SALES_LINE_ID,
3618 NEGATED_FLAG,
3619 CUSTOMER_ID,
3620 INVENTORY_ITEM_ID,
3621 ORDER_NUMBER,
3622 BOOKED_DATE,
3623 INVOICE_NUMBER,
3624 INVOICE_DATE,
3625 ADJUST_DATE,
3626 ADJUSTED_BY,
3627 REVENUE_TYPE,
3628 ADJUST_ROLLUP_FLAG,
3629 ADJUST_COMMENTS,
3630 ADJUST_STATUS,
3631 LINE_NUMBER,
3632 BILL_TO_ADDRESS_ID,
3633 SHIP_TO_ADDRESS_ID,
3634 BILL_TO_CONTACT_ID,
3635 SHIP_TO_CONTACT_ID,
3636 ADJ_COMM_LINES_API_ID,
3637 PRE_DEFINED_RC_FLAG,
3638 ROLLUP_FLAG,
3639 FORECAST_ID,
3640 UPSIDE_QUANTITY,
3641 UPSIDE_AMOUNT,
3642 UOM_CODE,
3643 REASON_CODE,
3644 TYPE,
3645 PRE_PROCESSED_CODE,
3646 QUOTA_ID,
3647 SRP_PLAN_ASSIGN_ID,
3648 ROLE_ID,
3649 COMP_GROUP_ID,
3650 COMMISSION_AMOUNT,
3651 EMPLOYEE_NUMBER,
3652 REVERSAL_FLAG,
3653 REVERSAL_HEADER_ID,
3654 SALES_CHANNEL,
3655 OBJECT_VERSION_NUMBER,
3656 SPLIT_PCT,
3657 SPLIT_status,
3658 org_id)
3659
3660 (select
3661 CCH.DIRECT_SALESREP_ID,
3662 CCH.PROCESSED_DATE,
3663 CCH.PROCESSED_PERIOD_ID,
3664 -1 * NVL(CCH.TRANSACTION_AMOUNT_ORIG, 0),
3665 CCH.TRX_TYPE,
3666 CCH.REVENUE_CLASS_ID,
3667 'UNLOADED',
3668 CCH.ATTRIBUTE_CATEGORY,
3669 CCH.ATTRIBUTE1,
3670 CCH.ATTRIBUTE2,
3671 CCH.ATTRIBUTE3,
3672 CCH.ATTRIBUTE4,
3673 CCH.ATTRIBUTE5,
3674 CCH.ATTRIBUTE6,
3675 CCH.ATTRIBUTE7,
3676 CCH.ATTRIBUTE8,
3677 CCH.ATTRIBUTE9,
3678 CCH.ATTRIBUTE10,
3679 CCH.ATTRIBUTE11,
3680 CCH.ATTRIBUTE12,
3681 CCH.ATTRIBUTE13,
3682 CCH.ATTRIBUTE14,
3683 CCH.ATTRIBUTE15,
3684 CCH.ATTRIBUTE16,
3685 CCH.ATTRIBUTE17,
3686 CCH.ATTRIBUTE18,
3687 CCH.ATTRIBUTE19,
3688 CCH.ATTRIBUTE20,
3689 CCH.ATTRIBUTE21,
3690 CCH.ATTRIBUTE22,
3691 CCH.ATTRIBUTE23,
3692 CCH.ATTRIBUTE24,
3693 CCH.ATTRIBUTE25,
3694 CCH.ATTRIBUTE26,
3695 CCH.ATTRIBUTE27,
3696 CCH.ATTRIBUTE28,
3697 CCH.ATTRIBUTE29,
3698 CCH.ATTRIBUTE30,
3699 CCH.ATTRIBUTE31,
3700 CCH.ATTRIBUTE32,
3701 CCH.ATTRIBUTE33,
3702 CCH.ATTRIBUTE34,
3703 CCH.ATTRIBUTE35,
3704 CCH.ATTRIBUTE36,
3705 CCH.ATTRIBUTE37,
3706 CCH.ATTRIBUTE38,
3707 CCH.ATTRIBUTE39,
3708 CCH.ATTRIBUTE40,
3709 CCH.ATTRIBUTE41,
3710 CCH.ATTRIBUTE42,
3711 CCH.ATTRIBUTE43,
3712 CCH.ATTRIBUTE44,
3713 CCH.ATTRIBUTE45,
3714 CCH.ATTRIBUTE46,
3715 CCH.ATTRIBUTE47,
3716 CCH.ATTRIBUTE48,
3717 CCH.ATTRIBUTE49,
3718 CCH.ATTRIBUTE50,
3719 CCH.ATTRIBUTE51,
3720 CCH.ATTRIBUTE52,
3721 CCH.ATTRIBUTE53,
3722 CCH.ATTRIBUTE54,
3723 CCH.ATTRIBUTE55,
3724 CCH.ATTRIBUTE56,
3725 CCH.ATTRIBUTE57,
3726 CCH.ATTRIBUTE58,
3727 CCH.ATTRIBUTE59,
3728 CCH.ATTRIBUTE60,
3729 CCH.ATTRIBUTE61,
3730 CCH.ATTRIBUTE62,
3731 CCH.ATTRIBUTE63,
3732 CCH.ATTRIBUTE64,
3733 CCH.ATTRIBUTE65,
3734 CCH.ATTRIBUTE66,
3735 CCH.ATTRIBUTE67,
3736 CCH.ATTRIBUTE68,
3737 CCH.ATTRIBUTE69,
3738 CCH.ATTRIBUTE70,
3739 CCH.ATTRIBUTE71,
3740 CCH.ATTRIBUTE72,
3741 CCH.ATTRIBUTE73,
3742 CCH.ATTRIBUTE74,
3743 CCH.ATTRIBUTE75,
3744 CCH.ATTRIBUTE76,
3745 CCH.ATTRIBUTE77,
3746 CCH.ATTRIBUTE78,
3747 CCH.ATTRIBUTE79,
3748 CCH.ATTRIBUTE80,
3749 CCH.ATTRIBUTE81,
3750 CCH.ATTRIBUTE82,
3751 CCH.ATTRIBUTE83,
3752 CCH.ATTRIBUTE84,
3753 CCH.ATTRIBUTE85,
3754 CCH.ATTRIBUTE86,
3755 CCH.ATTRIBUTE87,
3756 CCH.ATTRIBUTE88,
3757 CCH.ATTRIBUTE89,
3758 CCH.ATTRIBUTE90,
3759 CCH.ATTRIBUTE91,
3760 CCH.ATTRIBUTE92,
3761 CCH.ATTRIBUTE93,
3762 CCH.ATTRIBUTE94,
3763 CCH.ATTRIBUTE95,
3764 CCH.ATTRIBUTE96,
3765 CCH.ATTRIBUTE97,
3766 CCH.ATTRIBUTE98,
3767 CCH.ATTRIBUTE99,
3768 CCH.ATTRIBUTE100,
3769 cn_comm_lines_api_s.NEXTVAL,
3770 NULL,
3771 NULL,
3772 NULL,
3773 CCH.ROLLUP_DATE,
3774 NULL,
3775 CCH.SOURCE_DOC_TYPE,
3776 fnd_global.user_id,
3777 Sysdate,
3778 fnd_global.user_id,
3779 Sysdate,
3780 fnd_global.login_id,
3781 CCH.ORIG_CURRENCY_CODE,
3782 CCH.EXCHANGE_RATE,
3783 -1 * NVL(CCH.TRANSACTION_AMOUNT, 0),
3784 NULL, -- CCH.TRX_ID,
3785 NULL, -- CCH.TRX_LINE_ID,
3786 NULL, -- CCH.TRX_SALES_LINE_ID,
3787 -1 * CCH.QUANTITY,
3788 CCH.SOURCE_TRX_NUMBER,
3789 CCH.DISCOUNT_PERCENTAGE,
3790 CCH.MARGIN_PERCENTAGE,
3791 CCH.SOURCE_TRX_ID,
3792 CCH.SOURCE_TRX_LINE_ID,
3793 CCH.SOURCE_TRX_SALES_LINE_ID,
3794 'Y',
3795 CCH.CUSTOMER_ID,
3796 CCH.INVENTORY_ITEM_ID,
3797 CCH.ORDER_NUMBER,
3798 CCH.BOOKED_DATE,
3799 CCH.INVOICE_NUMBER,
3800 CCH.INVOICE_DATE,
3801 SYSDATE,
3802 l_adjusted_by,
3803 CCH.REVENUE_TYPE,
3804 CCH.ADJUST_ROLLUP_FLAG,
3805 'SCA_ROLLBACK',
3806 'REVERSAL',
3807 CCH.LINE_NUMBER,
3808 CCH.BILL_TO_ADDRESS_ID,
3809 CCH.SHIP_TO_ADDRESS_ID,
3810 CCH.BILL_TO_CONTACT_ID,
3811 CCH.SHIP_TO_CONTACT_ID,
3812 CCH.COMM_LINES_API_ID,
3813 NULL, -- CCH.PRE_DEFINED_RC_FLAG,
3814 NULL, -- CCH.ROLLUP_FLAG,
3815 CCH.FORECAST_ID,
3816 CCH.UPSIDE_QUANTITY,
3817 CCH.UPSIDE_AMOUNT,
3818 CCH.UOM_CODE,
3819 CCH.REASON_CODE,
3820 CCH.TYPE,
3821 CCH.PRE_PROCESSED_CODE,
3822 CCH.QUOTA_ID,
3823 CCH.SRP_PLAN_ASSIGN_ID,
3824 CCH.ROLE_ID,
3825 CCH.COMP_GROUP_ID,
3826 CCH.COMMISSION_AMOUNT,
3827 CS.EMPLOYEE_NUMBER,
3828 'Y',
3829 CCH.COMMISSION_HEADER_ID,
3830 CCH.SALES_CHANNEL,
3831 CCH.OBJECT_VERSION_NUMBER,
3832 CCH.SPLIT_PCT,
3833 CCH.SPLIT_status,
3834 cch.org_id
3835 FROM cn_commission_headers CCH, cn_salesreps CS
3836 WHERE CCH.adj_comm_lines_api_id = sca_lines_rollback(j)
3837 AND CS.salesrep_id = CCH.direct_salesrep_id
3838 AND (--(CCH.adjust_status IS NULL) or
3839 (CCH.adjust_status <> 'FROZEN')));
3840
3841 debugmsg('Allocation Transfer : Rerun : Obsoleting loaded transactions that have been created for previous SCA results');
3842
3843 FORALL j IN 1..sca_lines_rollback.COUNT
3844
3845 UPDATE cn_commission_headers CSH
3846 SET adjust_status = 'FROZEN',
3847 reversal_header_id = CSH.commission_header_id,
3848 reversal_flag = 'Y',
3849 adjust_date = sysdate,
3850 adjusted_by = l_adjusted_by,
3851 adjust_comments = 'SCA_ROLLBACK'
3852 WHERE adj_comm_lines_api_id = sca_lines_rollback(j)
3853 AND (--(adjust_status IS NULL) or
3854 (adjust_status <> 'FROZEN'));
3855
3856 debugmsg('Allocation Transfer : Rerun : Obsoleting loaded transactions that have been created for previous error SCA results');
3857
3858 FORALL j IN 1..sca_lines_rollback.COUNT
3859
3860 UPDATE cn_comm_lines_api api
3861 SET load_status = 'UNLOADED',
3862 adjust_status = 'NEW', --NULL,
3863 adjust_date = sysdate,
3864 adjusted_by = l_adjusted_by,
3865 adjust_comments = 'SCA_ROLLBACK'
3866 WHERE comm_lines_api_id = sca_lines_rollback(j);
3867
3868 debugmsg('Allocation Transfer : Rerun : Deleting all headers and lines for previous SCA');
3869
3870 FORALL j IN 1..sca_headers_rollback.COUNT
3871
3872 DELETE FROM cn_sca_lines_output
3873 where sca_headers_interface_id = sca_headers_rollback(j);
3874
3875 FORALL j IN 1..sca_headers_rollback.COUNT
3876
3877 DELETE FROM cn_sca_lines_interface
3878 where sca_headers_interface_id = sca_headers_rollback(j);
3879
3880 FORALL j IN 1..sca_headers_rollback.COUNT
3881
3882 DELETE FROM cn_sca_headers_interface
3883 WHERE sca_headers_interface_id = sca_headers_rollback(j);
3884
3885 debugmsg('Allocation Transfer : Rerun : End');
3886
3887 EXCEPTION
3888
3889 WHEN OTHERS THEN
3890
3891 debugmsg('Allocation Transfer : Rerun : Unexpected exception');
3892 debugmsg('Allocation Transfer : Rerun : End');
3893
3894 conc_status := fnd_concurrent.set_completion_status(
3895 status => 'ERROR',
3896 message => '');
3897
3898 RAISE;
3899
3900 END check_adjusted;
3901
3902
3903 --+ Procedure Name
3904 --+ Assign
3905 --+ Purpose : Split the logical batch into smaller physical batches
3906 --+ populate the physical_batch_id in cn_process_batches
3907
3908
3909 PROCEDURE sca_batch_runner( errbuf OUT NOCOPY VARCHAR2
3910 ,retcode OUT NOCOPY NUMBER
3911 ,p_parent_proc_audit_id NUMBER
3912 ,p_process VARCHAR2
3913 ,p_physical_batch_id NUMBER
3914 ,p_start_date DATE := NULL
3915 ,p_end_date DATE := NULL
3916 ,p_org_id IN NUMBER) IS
3917
3918
3919 l_request_id NUMBER(15) := NULL;
3920 l_process_audit_id NUMBER(15);
3921 l_msg_count NUMBER;
3922 l_msg_data VARCHAR2(2000);
3923 l_return_status VARCHAR2(30);
3924
3925 l_org_id INTEGER;
3926 l_org_append varchar2(100);
3927
3928 c integer;
3929 rows_processed integer;
3930 statement varchar2(1000);
3931
3932
3933 BEGIN
3934
3935 l_request_id := fnd_global.conc_request_id;
3936
3937 cn_message_pkg.begin_batch(
3938 x_process_type => 'SCA Batch Runner'
3939 ,x_parent_proc_audit_id => p_parent_proc_audit_id
3940 ,x_process_audit_id => l_process_audit_id
3941 ,x_request_id => l_request_id
3942 ,p_org_id => p_org_id);
3943
3944 debugmsg(p_process || ' : SCA Batch Runner : Start ');
3945
3946 IF (p_process = 'Create_trx') THEN
3947
3948 create_trx (
3949 p_start_date => p_start_date,
3950 p_end_date => p_end_date,
3951 p_physical_batch_id => p_physical_batch_id);
3952
3953 ELSIF (p_process = 'Negate_trx') THEN
3954
3955 negate_trx (
3956 p_start_date => p_start_date,
3957 p_end_date => p_end_date,
3958 p_physical_batch_id => p_physical_batch_id);
3959
3960 ElSIF (p_process = 'Populate_data') THEN
3961
3962 -- select NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1), ' ', NULL,
3963 -- SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
3964 -- into l_org_id
3965 -- from dual;
3966
3967 l_org_id := p_org_id; -- replaced the able select statement with the assignment statement
3968
3969 if l_org_id = -99 then
3970 l_org_append := '_M99';
3971 else
3972 l_org_append := '_' || l_org_id;
3973 end if;
3974
3975 --+
3976 -- Construct the call to the collect procedure of the package
3977 --+
3978
3979 c := dbms_sql.open_cursor;
3980
3981 statement := 'begin cn_sca_map_cn'||l_org_append||
3982 '.map(:sca_proc_batch_id, :start_date, :end_date, :api_version, :init_msg_list, :commit, :validation_level, '||
3983 ':org_id, :return_status, :msg_count, :msg_data); end;';
3984
3985 dbms_sql.parse(c, statement, dbms_sql.native);
3986
3987 dbms_sql.bind_variable(c,'sca_proc_batch_id', p_physical_batch_id, 30);
3988 dbms_sql.bind_variable(c,'start_date', p_start_date);
3989 dbms_sql.bind_variable(c,'end_date', p_end_date);
3990 dbms_sql.bind_variable(c,'api_version', 1.0);
3991 dbms_sql.bind_variable(c,'init_msg_list', FND_API.G_FALSE);
3992 dbms_sql.bind_variable(c,'commit', FND_API.G_FALSE);
3993 dbms_sql.bind_variable(c,'validation_level', FND_API.G_VALID_LEVEL_FULL);
3994 dbms_sql.bind_variable(c,'org_id', p_org_id);
3995 dbms_sql.bind_variable(c,'return_status', l_return_status, 50);
3996 dbms_sql.bind_variable(c,'msg_count', l_msg_count);
3997 dbms_sql.bind_variable(c,'msg_data', l_msg_data, 200);
3998
3999 rows_processed := dbms_sql.execute(c);
4000
4001 dbms_sql.variable_value(c,'return_status', l_return_status);
4002 dbms_sql.variable_value(c,'msg_count', l_msg_count);
4003 dbms_sql.variable_value(c,'msg_data', l_msg_data);
4004 dbms_sql.close_cursor(c);
4005
4006 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
4007
4008 RAISE fail_populate;
4009
4010 end if;
4011
4012 ELSIF (p_process = 'Rollback_data') THEN
4013
4014 check_adjusted (
4015 p_start_date => p_start_date,
4016 p_end_date => p_end_date,
4017 p_physical_batch_id => p_physical_batch_id);
4018
4019 ELSIF (p_process = 'Check_comm_lines_api_adjusted') THEN
4020
4021 check_api_adjusted (
4022 p_start_date => p_start_date,
4023 p_end_date => p_end_date,
4024 p_physical_batch_id => p_physical_batch_id);
4025
4026 END IF;
4027
4028 debugmsg(p_process || ' : SCA Batch Runner : Completed over physical batch : ' || p_physical_batch_id);
4029 debugmsg(p_process || ' : SCA Batch Runner : End ');
4030
4031 -- cn_message_pkg.flush;
4032 -- COMMIT;
4033
4034 -- cn_message_pkg.set_name('CN','ALL_PROCESS_DONE_OK');
4035 cn_message_pkg.end_batch(l_process_audit_id);
4036
4037 retcode := 0;
4038 errbuf := 'Successful.';
4039
4040
4041 EXCEPTION
4042
4043 WHEN fail_populate THEN
4044
4045 retcode := 2;
4046 debugmsg(p_process || ' : SCA Batch Runner : Failed in populating : ' || p_physical_batch_id);
4047 debugmsg(p_process || ' : SCA Batch Runner : End ');
4048 cn_message_pkg.end_batch(l_process_audit_id);
4049
4050 WHEN others THEN
4051
4052 retcode := 2;
4053 debugmsg(p_process || ' : SCA Batch Runner : Unexpected Exception : ' || p_physical_batch_id);
4054 debugmsg(p_process || ' : SCA Batch Runner : End ');
4055 cn_message_pkg.end_batch(l_process_audit_id);
4056
4057 END sca_batch_runner;
4058
4059
4060 END cn_sca_trx_proc_pvt;