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