[Home] [Help]
PACKAGE BODY: APPS.CN_TRANSACTION_LOAD_PKG
Source
1 PACKAGE BODY cn_transaction_load_pkg AS
2 -- $Header: cnloadb.pls 120.20 2010/07/09 10:51:46 sseshaiy ship $
3 -- +======================================================================+
4 -- | Copyright (c) 1994 Oracle Corporation |
5 -- | Redwood Shores, California, USA |
6 -- | All rights reserved. |
7 -- +======================================================================+
8
9 -- Package Name
10 -- cn_transaction_load_pkg
11 -- Purpose
12 -- Procedures TO load trx FROM cn_comm_lines_api TO cn_commission_headers
13 -- History
14 -- 10/21/99 Harlen Chen Created
15 -- 08/28/01 Rao Chenna acctd_transaction_amount column update logic
16 -- is modified.
17 -- 03/31/03 Hithanki Modified Procedure Assign For bug Fix 2781346
18 -- Added Exception Handler For No-Trx-Lines Error.
19 --
20 -- Nov 22, 2005 vensrini Added org_id joins to the subqueries in
21 -- check_api_data procedure
22 --
23 -- Fixes for transaction load thru concurrent request
24
25 -- Global Variable
26 g_logical_process VARCHAR2(30) := 'LOAD';
27 g_physical_process VARCHAR2(30) := 'LOAD';
28 no_trx_lines EXCEPTION;
29 fail_validate_ruleset EXCEPTION;
30 conc_fail EXCEPTION;
31 invalid_date EXCEPTION;
32
33 -- Local Procedure for showing debug msg
34 PROCEDURE debugmsg(msg VARCHAR2) IS
35 BEGIN
36 cn_message_pkg.DEBUG(SUBSTR(msg, 1, 254));
37 -- comment out dbms_output before checking in file
38 -- dbms_output.put_line(substr(msg,1,254));
39 END debugmsg;
40
41 -- Procedure Name
42 -- get_physical_batch_id
43 -- Purpose : get the unique physical batch id
44 FUNCTION get_physical_batch_id
45 RETURN NUMBER IS
46 x_physical_batch_id NUMBER;
47 BEGIN
48 -- sequence s3 is for physical batch id
49 SELECT cn_process_batches_s3.NEXTVAL
50 INTO x_physical_batch_id
51 FROM SYS.DUAL;
52
53 RETURN x_physical_batch_id;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 RAISE NO_DATA_FOUND;
57 END get_physical_batch_id;
58
59 -- Procedure Name
60 -- void_batches
61 -- Purpose
62 -- VOID the batches that have successfully moved to the required status
63 -- to prevent them being picked up in any retries.
64 -- Unlockable batches will remain for the requred number of retries
65 -- Called just before program completes to purge the table of any remaining
66 -- unprocessed records that were not procesed during retries.
67 PROCEDURE void_batches(p_physical_batch_id NUMBER, p_logical_batch_id NUMBER) IS
68 l_user_id NUMBER(15) := fnd_global.user_id;
69 l_resp_id NUMBER(15) := fnd_global.resp_id;
70 l_login_id NUMBER(15) := fnd_global.login_id;
71 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
72 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
73 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
74 BEGIN
75 debugmsg(
76 'Void_batches : For physical batch : '
77 || p_physical_batch_id
78 || ' Logical batch '
79 || p_logical_batch_id
80 );
81
82 IF p_physical_batch_id IS NULL THEN
83 UPDATE cn_process_batches
84 SET status_code = 'VOID'
85 , last_update_date = SYSDATE
86 , last_update_login = l_login_id
87 , last_updated_by = l_user_id
88 , request_id = l_conc_request_id
89 , program_application_id = l_prog_appl_id
90 , program_id = l_conc_prog_id
91 , program_update_date = SYSDATE
92 WHERE logical_batch_id = p_logical_batch_id;
93 ELSE
94 UPDATE cn_process_batches
95 SET status_code = 'VOID'
96 , last_update_date = SYSDATE
97 , last_update_login = l_login_id
98 , last_updated_by = l_user_id
99 , request_id = l_conc_request_id
100 , program_application_id = l_prog_appl_id
101 , program_id = l_conc_prog_id
102 , program_update_date = SYSDATE
103 WHERE physical_batch_id = p_physical_batch_id;
104 END IF;
105
106 IF SQL%FOUND THEN
107 debugmsg('Void_batches : found ');
108 ELSIF SQL%NOTFOUND THEN
109 debugmsg('Void_batches : not found');
110 END IF;
111 END void_batches;
112
113 --+ Procedure Name
114 --+ Assign
115 --+ Purpose : Split the logical batch into smaller physical batches
116 --+ populate the physical_batch_id in cn_process_batches
117 PROCEDURE assign(p_logical_batch_id NUMBER, p_org_id NUMBER) IS
118 x_physical_batch_id NUMBER;
119 l_srp_trx_count NUMBER := 0;
120 l_trx_count NUMBER := 0; -- number of trx in current physical batch
121 l_srp_count NUMBER := 0; -- number of srp in current physical batch
122 l_user_id NUMBER(15) := fnd_global.user_id;
123 l_resp_id NUMBER(15) := fnd_global.resp_id;
124 l_login_id NUMBER(15) := fnd_global.login_id;
125 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
126 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
127 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
128
129 CURSOR logical_batches IS
130 SELECT salesrep_id
131 , SUM(sales_lines_total) srp_trx_count
132 FROM cn_process_batches
133 WHERE logical_batch_id = p_logical_batch_id AND status_code = 'IN_USE'
134 GROUP BY salesrep_id
135 ORDER BY salesrep_id DESC;
136
137 logical_rec logical_batches%ROWTYPE;
138 BEGIN
139 -- Get the first physical batch id
140 x_physical_batch_id := get_physical_batch_id;
141 cn_global_var.initialize_instance_info(p_org_id);
142
143
144 OPEN logical_batches;
145
146 LOOP
147 FETCH logical_batches INTO logical_rec;
148
149 IF (logical_batches%FOUND) THEN
150 l_srp_count := l_srp_count + 1;
151 l_srp_trx_count := logical_rec.srp_trx_count;
152
153 IF ((l_trx_count + l_srp_trx_count) >= cn_global_var.g_system_batch_size) THEN
154 IF (l_srp_count > 1) THEN
155 -- This case, done with current batch.
156 debugmsg(
157 'Loader : Assign : Case1 Physical batch id : '
158 || x_physical_batch_id
159 || ' Total trx lines : '
160 || l_trx_count
161 || ' Total salesrep : '
162 || TO_CHAR(l_srp_count - 1)
163 );
164 -- This salesrep should go into next batch.
165 l_trx_count := l_srp_trx_count;
166 l_srp_count := 1;
167 x_physical_batch_id := get_physical_batch_id;
168 ELSE
169 -- This is the first salerep in this batch, this salesrep
170 -- has to be in this batch.
171 l_trx_count := l_srp_trx_count;
172 END IF;
173 ELSIF(l_srp_count > cn_global_var.get_salesrep_batch_size(p_org_id)) THEN
174 -- too many salesreps in this physical batch.
175 -- this salesrep should go into next batch.
176 debugmsg(
177 'Loader : Assign : Case 2 Physical batch id : '
178 || x_physical_batch_id
179 || ' Total trx lines : '
180 || l_trx_count
181 || ' Total salesrep : '
182 || TO_CHAR(l_srp_count - 1)
183 );
184 l_trx_count := l_srp_trx_count;
185 l_srp_count := 1;
186 x_physical_batch_id := get_physical_batch_id;
187 ELSE
188 -- continue with current batch
189 l_trx_count := l_trx_count + l_srp_trx_count;
190 END IF;
191
192 debugmsg(
193 'Loader : Assign : Physical batch id : '
194 || x_physical_batch_id
195 || ' Salesrep ID : '
196 || logical_rec.salesrep_id
197 || ' and # of trx : '
198 || l_srp_trx_count
199 );
200
201 UPDATE cn_process_batches
202 SET physical_batch_id = x_physical_batch_id
203 , last_update_date = SYSDATE
204 , last_update_login = l_login_id
205 , last_updated_by = l_user_id
206 , request_id = l_conc_request_id
207 , program_application_id = l_prog_appl_id
208 , program_id = l_conc_prog_id
209 , program_update_date = SYSDATE
210 WHERE salesrep_id = logical_rec.salesrep_id
211 AND logical_batch_id = p_logical_batch_id
212 AND status_code = 'IN_USE';
213 ELSE -- logical_batches not FOUND
214 IF (logical_batches%ROWCOUNT = 0) THEN
215 -- Added By HITHANKI Start
216 -- On 03/31/03 For Bug Fix 2781346
217 -- Replaced this RAISE call with standard way of handling User Defined Exceptions
218 -- RAISE no_trx_lines;
219
220 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
221 fnd_message.set_name('CN', 'CN_NO_TRX_LINES');
222 fnd_msg_pub.ADD;
223 -- END IF;
224 EXIT;
225 RAISE fnd_api.g_exc_error;
226 -- Added By HITHANKI End
227 ELSE
228 -- assign is completed
229 debugmsg(
230 'Loader : Assign : Case 3 Physical batch id : '
231 || x_physical_batch_id
232 || ' Total trx lines : '
233 || l_trx_count
234 || ' Total salesrep : '
235 || l_srp_count
236 );
237 debugmsg('Loader : Assign : successfully completed');
238 END IF;
239
240 EXIT;
241 END IF;
242 END LOOP;
243
244 CLOSE logical_batches;
245
246 cn_message_pkg.FLUSH;
247 COMMIT;
248 debugmsg('Loader : Assign : Assignment commit complete.');
249 EXCEPTION
250 WHEN OTHERS THEN
251 debugmsg('Loader : Assign : Unexpected exception.');
252 RAISE;
253 -- Commented Out
254 -- Hithanki 05/03/03 For Bug Fix 2781346
255 -- WHEN no_trx_lines THEN
256 -- debugmsg('Loader : Assign : No transactions found.');
257 -- RAISE;
258 END assign;
259
260 PROCEDURE update_error(x_physical_batch_id NUMBER) IS
261 l_user_id NUMBER(15) := fnd_global.user_id;
262 l_resp_id NUMBER(15) := fnd_global.resp_id;
263 l_login_id NUMBER(15) := fnd_global.login_id;
264 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
265 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
266 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
267 BEGIN
268 -- Giving the batch an 'ERROR' status prevents subsequent
269 -- physical processes picking it up.
270 UPDATE cn_process_batches
271 SET status_code = 'ERROR'
272 , last_update_date = SYSDATE
273 , last_update_login = l_login_id
274 , last_updated_by = l_user_id
275 , request_id = l_conc_request_id
276 , program_application_id = l_prog_appl_id
277 , program_id = l_conc_prog_id
278 , program_update_date = SYSDATE
279 WHERE physical_batch_id = x_physical_batch_id;
280 END update_error;
281
282 -- Procedure Name
283 -- Conc_Submit
284 -- Purpose
285 PROCEDURE conc_submit(
286 x_conc_program VARCHAR2
287 , x_parent_proc_audit_id NUMBER
288 , x_logical_process VARCHAR2
289 , x_physical_process VARCHAR2
290 , x_physical_batch_id NUMBER
291 , x_salesrep_id NUMBER
292 , x_start_date DATE
293 , x_end_date DATE
294 , x_cls_rol_flag VARCHAR2
295 , x_request_id IN OUT NOCOPY NUMBER
296 ) IS
297 l_org_id NUMBER; -- vensrini transaction load fix
298 BEGIN
299 debugmsg('Conc_Submit : x_logical_process = ' || x_logical_process);
300 debugmsg('Conc_Submit : x_salesrep_id = ' || x_salesrep_id);
301 debugmsg('Conc_Submit : x_start_date = ' || x_start_date);
302 debugmsg('Conc_Submit : x_end_date = ' || x_end_date);
303 debugmsg('Conc_Submit : x_cls_rol_flag = ' || x_cls_rol_flag);
304
305 -- transaction load
306 SELECT org_id INTO l_org_id
307 FROM cn_process_batches
308 WHERE physical_batch_id = x_physical_batch_id AND ROWNUM = 1;
309
310 fnd_request.set_org_id(l_org_id);
311 -- transaction load
312 x_request_id :=
313 fnd_request.submit_request(
314 application => 'CN'
315 , program => x_conc_program
316 , description => NULL
317 , start_time => NULL
318 , sub_request => NULL
319 , argument1 => x_parent_proc_audit_id
320 , argument2 => x_logical_process
321 , argument3 => x_physical_process
322 , argument4 => x_physical_batch_id
323 , argument5 => x_salesrep_id
324 , argument6 => x_start_date
325 , argument7 => x_end_date
326 , argument8 => x_cls_rol_flag
327 );
328 debugmsg('Conc_Submit : x_request_id = ' || x_request_id);
329
330 IF x_request_id = 0 THEN
331 debugmsg('Loader : Conc_Submit : Submit failure for phys batch ' || x_physical_batch_id);
332 debugmsg('Loader : Conc_Submit: ' || fnd_message.get);
333 debugmsg('Loader : Conc_Submit : Submit failure for phys batch ' || x_physical_batch_id);
334 ELSE
335 cn_message_pkg.FLUSH;
336 COMMIT; -- Commit for each concurrent program i.e. runner
337 END IF;
338 END conc_submit;
339
340 -- Procedure Name
341 -- Conc_Dispatch
342 -- Purpose
343 -- Submits independent concurrent programs for each physical batch.
344 -- These physical batches will be executed in parallel.
345 -- A subsequent physical process cannot begin until all physical
346 -- batches in its prerequisite process have completed.
347 PROCEDURE conc_dispatch(
348 x_parent_proc_audit_id NUMBER
349 , x_salesrep_id NUMBER
350 , x_start_date DATE
351 , x_end_date DATE
352 , x_cls_rol_flag VARCHAR2
353 , x_logical_batch_id NUMBER
354 ) IS
355 TYPE requests IS TABLE OF NUMBER(15)
356 INDEX BY BINARY_INTEGER;
357
358 TYPE batches IS TABLE OF NUMBER(15)
359 INDEX BY BINARY_INTEGER;
360
361 l_primary_request_stack requests;
362 l_primary_batch_stack batches;
363 l_empty_request_stack requests;
364 l_empty_batch_stack batches;
365 x_batch_total NUMBER := 0;
366 l_temp_id NUMBER := 0;
367 l_temp_phys_batch_id NUMBER;
368 primary_ptr NUMBER := 1; -- Must start at 1
369 l_dev_phase VARCHAR2(80);
370 l_dev_status VARCHAR2(80);
371 l_request_id NUMBER;
372 l_completed_batch_count NUMBER := 0;
373 l_call_status BOOLEAN;
374 l_next_process VARCHAR2(30);
375 l_dummy VARCHAR2(500);
376 unfinished BOOLEAN := TRUE;
377 l_user_id NUMBER(15) := fnd_global.user_id;
378 l_resp_id NUMBER(15) := fnd_global.resp_id;
379 l_login_id NUMBER(15) := fnd_global.login_id;
380 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
381 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
382 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
383 x_debug NUMBER;
384 debug_v NUMBER;
385 conc_status BOOLEAN;
386 l_sleep_time NUMBER := 180;
387 l_sleep_time_char VARCHAR2(30);
388
389 -- Get individual physical batch id's for the entire logical batch
390 CURSOR physical_batches IS
391 SELECT DISTINCT physical_batch_id
392 FROM cn_process_batches
393 WHERE logical_batch_id = x_logical_batch_id AND status_code = 'IN_USE';
394
395 physical_rec physical_batches%ROWTYPE;
396 BEGIN
397 debugmsg('Loader : Conc_Dispatch : Start of Conc_Dispatch');
398
399 WHILE unfinished LOOP
400 l_primary_request_stack := l_empty_request_stack;
401 l_primary_batch_stack := l_empty_batch_stack;
402 primary_ptr := 1; -- Start at element one not element zero
403 l_completed_batch_count := 0;
404 x_batch_total := 0;
405
406 FOR physical_rec IN physical_batches LOOP
407 debugmsg(
408 'Loader : Conc_Dispatch : Calling conc_submit. '
409 || 'physical_rec.physical_batch_id = '
410 || physical_rec.physical_batch_id
411 );
412 debugmsg('conc_dispatch : call BATCH_RUNNER');
413 conc_submit(
414 x_conc_program => 'BATCH_RUNNER'
415 , x_parent_proc_audit_id => x_parent_proc_audit_id
416 , x_logical_process => g_logical_process -- = 'LOAD'
417 , x_physical_process => g_physical_process -- = 'LOAD'
418 , x_physical_batch_id => physical_rec.physical_batch_id
419 , x_salesrep_id => x_salesrep_id
420 , x_start_date => x_start_date
421 , x_end_date => x_end_date
422 , x_cls_rol_flag => x_cls_rol_flag
423 , x_request_id => l_temp_id
424 );
425 debugmsg('conc_dispatch : done BATCH_RUNNER');
426 x_batch_total := x_batch_total + 1;
427 l_primary_request_stack(x_batch_total) := l_temp_id;
428 l_primary_batch_stack(x_batch_total) := physical_rec.physical_batch_id;
429
430 -- If submission failed update the batch record and bail
431 IF l_temp_id = 0 THEN
432 --cn_debug.print_msg('conc disp submit failed',1);
433 l_temp_phys_batch_id := physical_rec.physical_batch_id;
434 RAISE conc_fail;
435 END IF;
436 END LOOP;
437
438 debugmsg('Loader : Conc_Dispatch : Total conc requests submitted : ' || x_batch_total);
439 debugmsg('Total conc requests submitted : ' || x_batch_total);
440 debug_v := l_primary_request_stack(primary_ptr);
441 l_sleep_time_char := fnd_profile.VALUE('CN_SLEEP_TIME');
442
443 IF l_sleep_time_char IS NOT NULL THEN
444 l_sleep_time := TO_NUMBER(l_sleep_time_char);
445 END IF;
446
447 DBMS_LOCK.sleep(l_sleep_time);
448
449 WHILE l_completed_batch_count <= x_batch_total LOOP
450 IF l_primary_request_stack(primary_ptr) IS NOT NULL THEN
451 l_call_status :=
452 fnd_concurrent.get_request_status(
453 request_id => l_primary_request_stack(primary_ptr)
454 , phase => l_dummy
455 , status => l_dummy
456 , dev_phase => l_dev_phase
457 , dev_status => l_dev_status
458 , MESSAGE => l_dummy
459 );
460
461 IF (NOT l_call_status) THEN
462 debugmsg('Loader : Conc_Dispatch : request_id is '
463 || l_primary_request_stack(primary_ptr));
464 RAISE conc_fail;
465 END IF;
466
467 IF l_dev_phase = 'COMPLETE' THEN
468 debug_v := l_primary_request_stack(primary_ptr);
469 l_temp_phys_batch_id := l_primary_batch_stack(primary_ptr);
470 l_primary_batch_stack(primary_ptr) := NULL;
471 l_primary_request_stack(primary_ptr) := NULL;
472 l_completed_batch_count := l_completed_batch_count + 1;
473
474 IF l_dev_status = 'ERROR' THEN
475 debugmsg('Loader : Conc_Dispatch : ' || 'Request completed with error for '
476 || debug_v);
477 RAISE conc_fail;
478 ELSIF l_dev_status = 'NORMAL' THEN
479 x_debug := l_primary_batch_stack(primary_ptr);
480 END IF; -- If error
481 END IF; -- If complete
482 END IF; -- If null ptr
483
484 primary_ptr := primary_ptr + 1;
485
486 IF l_completed_batch_count = x_batch_total THEN
487 debugmsg(
488 'Loade : Conc_Dispatch : All requests complete for physical '
489 || 'process : '
490 || g_physical_process
491 );
492 -- Get out of the loop by adding 1
493 l_completed_batch_count := l_completed_batch_count + 1;
494 debugmsg(
495 'Loader : Conc_Dispatch : All requests complete for '
496 || 'logical process : '
497 || g_logical_process
498 );
499 unfinished := FALSE;
500 ELSE
501 -- Made a complete pass through the srp_periods in this physical
502 -- batch and some conc requests have not completed.
503 -- Give the conc requests a few minutes to run before
504 -- checking their status
505 IF primary_ptr > x_batch_total THEN
506 DBMS_LOCK.sleep(l_sleep_time);
507 primary_ptr := 1;
508 END IF;
509 END IF;
510 END LOOP;
511 END LOOP;
512 EXCEPTION
513 WHEN NO_DATA_FOUND THEN
514 debugmsg('Loader : Conc_Dispatch : no rows for process ' || g_physical_process);
515 cn_message_pkg.end_batch(x_parent_proc_audit_id);
516 WHEN conc_fail THEN
517 update_error(l_temp_phys_batch_id);
518 debugmsg('Loader : Conc_Dispatch : Exception conc_fail');
519 cn_message_pkg.end_batch(x_parent_proc_audit_id);
520 conc_status := fnd_concurrent.set_completion_status(status => 'ERROR', MESSAGE => '');
521 WHEN OTHERS THEN
522 debugmsg('Loader : Conc_Dispatch : Unexpected Exception');
523 RAISE;
524 END conc_dispatch;
525
526 -- Procedure Name
527 -- Pre_Conc_Dispatch
528 -- Purpose
529 PROCEDURE pre_conc_dispatch(
530 p_salesrep_id NUMBER
531 , p_start_date DATE
532 , p_end_date DATE
533 , p_org_id NUMBER
534 ) IS
535 x_trx_batch NUMBER(15);
536 user_id NUMBER;
537 functionalcurrency VARCHAR2(15);
538 BEGIN
539 /*****************************************/
540 /* The following Updates do a check for */
541 /* no prior adjustment if profile option set to 'Y'*/
542 /*****************************************/
543 IF (cn_system_parameters.VALUE('CN_PRIOR_ADJUSTMENT', p_org_id) = 'N') THEN
544 DECLARE
545 x_latest_processed_date DATE;
546 BEGIN
547 SELECT NVL(latest_processed_date, TO_DATE('01/01/1900', 'DD/MM/YYYY'))
548 INTO x_latest_processed_date
549 FROM cn_repositories_all
550 WHERE org_id = p_org_id;
551
552 -- Commented this query to fix bug# 1772128
553 /*
554 UPDATE cn_comm_lines_api_all
555 SET load_status = 'ERROR - PRIOR ADJUSTMENT'
556 WHERE load_status = 'UNLOADED'
557 AND Trunc(processed_date) >= Trunc(p_start_date)
558 AND Trunc(processed_date) <= Trunc(p_end_date)
559 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
560 AND trx_type <> 'FORECAST'
561 AND processed_date < x_latest_processed_date; */
562 IF (p_salesrep_id IS NULL) THEN
563 UPDATE cn_comm_lines_api_all
564 SET load_status = 'ERROR - PRIOR ADJUSTMENT'
565 WHERE load_status = 'UNLOADED'
566 AND processed_date >= TRUNC(p_start_date)
567 AND processed_date <(TRUNC(p_end_date) + 1)
568 AND trx_type <> 'FORECAST'
569 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
570 AND processed_date < x_latest_processed_date
571 AND org_id = p_org_id;
572 ELSE
573 UPDATE cn_comm_lines_api_all
574 SET load_status = 'ERROR - PRIOR ADJUSTMENT'
575 WHERE load_status = 'UNLOADED'
576 AND processed_date >= TRUNC(p_start_date)
577 AND processed_date <(TRUNC(p_end_date) + 1)
578 AND salesrep_id = p_salesrep_id
579 AND trx_type <> 'FORECAST'
580 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
581 AND processed_date < x_latest_processed_date
582 AND org_id = p_org_id;
583 END IF;
584 END;
585
586 NULL;
587 END IF;
588
589 /*****************************************/
590 /* The following Updates do a check for */
591 /* failures in the foreign key references*/
592 /*****************************************/
593
594 -- Commented this query to fix bug# 1772128
595 /*
596 UPDATE cn_comm_lines_api SET load_status = 'ERROR - TRX_TYPE'
597 WHERE load_status = 'UNLOADED'
598 AND Trunc(processed_date) >= Trunc(p_start_date)
599 AND Trunc(processed_date) <= Trunc(p_end_date)
600 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
601 AND trx_type <> 'FORECAST'
602 AND NOT EXISTS
603 (SELECT 1 FROM cn_lookups WHERE lookup_type = 'TRX TYPES'
604 AND lookup_code =
605 cn_comm_lines_api.trx_type); */
606 -- Added by rchenna on 06/12/01
607 IF (p_salesrep_id IS NULL) THEN
608 UPDATE cn_comm_lines_api_all
609 SET load_status = 'ERROR - TRX_TYPE'
610 WHERE load_status = 'UNLOADED'
611 AND processed_date >= TRUNC(p_start_date)
612 AND processed_date <(TRUNC(p_end_date) + 1)
613 AND trx_type <> 'FORECAST'
614 AND (adjust_status <> 'SCA_PENDING') --OR adjust_status IS NULL)
615 AND org_id = p_org_id
616 AND NOT EXISTS(
617 SELECT 1
618 FROM cn_lookups
619 WHERE lookup_type = 'TRX TYPES'
620 AND lookup_code = cn_comm_lines_api_all.trx_type);
621 ELSE
622 UPDATE cn_comm_lines_api_all
623 SET load_status = 'ERROR - TRX_TYPE'
624 WHERE load_status = 'UNLOADED'
625 AND processed_date >= TRUNC(p_start_date)
626 AND processed_date <(TRUNC(p_end_date) + 1)
627 AND salesrep_id = p_salesrep_id
628 AND trx_type <> 'FORECAST'
629 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
630 AND org_id = p_org_id
631 AND NOT EXISTS(
632 SELECT 1
633 FROM cn_lookups
634 WHERE lookup_type = 'TRX TYPES'
635 AND lookup_code = cn_comm_lines_api_all.trx_type);
636 END IF;
637
638 --
639 -- Commented this query to fix bug# 1772128
640 /*
641 UPDATE cn_comm_lines_api SET load_status = 'ERROR - REVENUE_CLASS'
642 WHERE load_status = 'UNLOADED'
643 AND Trunc(processed_date) >= Trunc(p_start_date)
644 AND Trunc(processed_date) <= Trunc(p_end_date)
645 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
646 AND trx_type <> 'FORECAST'
647 AND revenue_class_id IS NOT NULL
648 AND NOT EXISTS
649 (SELECT 1 FROM cn_revenue_classes
650 WHERE cn_revenue_classes.revenue_class_id =
651 cn_comm_lines_api.revenue_class_id); */
652 -- Added by rchenna on 06/12/01
653 IF (p_salesrep_id IS NULL) THEN
654 UPDATE cn_comm_lines_api_all
655 SET load_status = 'ERROR - REVENUE_CLASS'
656 WHERE load_status = 'UNLOADED'
657 AND processed_date >= TRUNC(p_start_date)
658 AND processed_date <(TRUNC(p_end_date) + 1)
659 AND trx_type <> 'FORECAST'
660 AND (adjust_status <> 'SCA_PENDING')--OR adjust_status IS NULL)
661 AND revenue_class_id IS NOT NULL
662 AND org_id = p_org_id
663 AND NOT EXISTS(
664 SELECT 1
665 FROM cn_revenue_classes
666 WHERE cn_revenue_classes.revenue_class_id =
667 cn_comm_lines_api_all.revenue_class_id);
668 ELSE
669 UPDATE cn_comm_lines_api_all
670 SET load_status = 'ERROR - REVENUE_CLASS'
671 WHERE load_status = 'UNLOADED'
672 AND processed_date >= TRUNC(p_start_date)
673 AND processed_date <(TRUNC(p_end_date) + 1)
674 AND salesrep_id = p_salesrep_id
675 AND trx_type <> 'FORECAST'
676 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
677 AND revenue_class_id IS NOT NULL
678 AND org_id = p_org_id
679 AND NOT EXISTS(
680 SELECT 1
681 FROM cn_revenue_classes
682 WHERE cn_revenue_classes.revenue_class_id =
683 cn_comm_lines_api_all.revenue_class_id);
684 END IF;
685
686 --
687
688 /*****************************************/
689 /* Validation for multi-currency */
690 /*****************************************/
691 functionalcurrency := cn_general_utils.get_currency(p_org_id);
692
693 -- If transaction currency = functional currency, then OK
694 -- if exch rate is NULL, fill in before rate check
695 -- Commented this query to fix bug# 1772128
696 /*
697 UPDATE cn_comm_lines_api
698 SET acctd_transaction_amount = transaction_amount,
699 exchange_rate = 1
700 WHERE load_status = 'UNLOADED'
701 AND Trunc(processed_date) >= Trunc(p_start_date)
702 AND Trunc(processed_date) <= Trunc(p_end_date)
703 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
704 AND ((acctd_transaction_amount IS NULL) OR
705 (acctd_transaction_amount = transaction_amount))
706 AND exchange_rate IS NULL
707 AND trx_type <> 'FORECAST'
708 AND transaction_currency_code IS NOT NULL
709 AND transaction_currency_code = FunctionalCurrency; */
710 IF (p_salesrep_id IS NULL) THEN
711 UPDATE cn_comm_lines_api_all
712 SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
713 WHERE load_status = 'UNLOADED'
714 AND processed_date >= TRUNC(p_start_date)
715 AND processed_date <(TRUNC(p_end_date) + 1)
716 AND acctd_transaction_amount IS NULL
717 AND trx_type <> 'FORECAST'
718 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
719 AND org_id = p_org_id
720 AND transaction_currency_code = functionalcurrency;
721 ELSE
722 UPDATE cn_comm_lines_api_all
723 SET acctd_transaction_amount = transaction_amount * NVL(exchange_rate, 1)
724 WHERE load_status = 'UNLOADED'
725 AND processed_date >= TRUNC(p_start_date)
726 AND processed_date <(TRUNC(p_end_date) + 1)
727 AND salesrep_id = p_salesrep_id
728 AND acctd_transaction_amount IS NULL
729 AND trx_type <> 'FORECAST'
730 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
731 AND org_id = p_org_id
732 AND transaction_currency_code = functionalcurrency;
733 END IF;
734
735 debugmsg(
736 'Loader : Pre_Conc_Dispatch : Multi-currency: '
737 || TO_CHAR(SQL%ROWCOUNT)
738 || ' records given in same currency as functional.'
739 );
740
741 /* Error when conversion needed but no rate given */
742 -- Commented this query to fix bug# 1772128
743 /*
744 UPDATE cn_comm_lines_api SET load_status = 'ERROR - NO EXCH RATE GIVEN'
745 WHERE load_status = 'UNLOADED'
746 AND Trunc(processed_date) >= Trunc(p_start_date)
747 AND Trunc(processed_date) <= Trunc(p_end_date)
748 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
749 AND trx_type <> 'FORECAST'
750 AND transaction_currency_code IS NOT NULL
751 AND exchange_rate IS NULL; */
752 IF (p_salesrep_id IS NULL) THEN
753 UPDATE cn_comm_lines_api_all
754 SET load_status = 'ERROR - NO EXCH RATE GIVEN'
755 WHERE load_status = 'UNLOADED'
756 AND processed_date >= TRUNC(p_start_date)
757 AND processed_date <(TRUNC(p_end_date) + 1)
758 AND trx_type <> 'FORECAST'
759 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
760 AND transaction_currency_code IS NOT NULL
761 AND exchange_rate IS NULL
762 -- Added to fix the above problem.
763 AND acctd_transaction_amount IS NULL
764 AND org_id = p_org_id;
765 ELSE
766 UPDATE cn_comm_lines_api_all
767 SET load_status = 'ERROR - NO EXCH RATE GIVEN'
768 WHERE load_status = 'UNLOADED'
769 AND processed_date >= TRUNC(p_start_date)
770 AND processed_date <(TRUNC(p_end_date) + 1)
771 AND salesrep_id = p_salesrep_id
772 AND trx_type <> 'FORECAST'
773 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
774 AND transaction_currency_code IS NOT NULL
775 AND exchange_rate IS NULL
776 -- Added to fix the above problem.
777 AND acctd_transaction_amount IS NULL
778 AND org_id = p_org_id;
779 END IF;
780
781 /* Error when no rate and code given but functional <> foreign */
782 -- Commented this query to fix bug# 1772128
783 /*
784 UPDATE cn_comm_lines_api SET load_status = 'ERROR - INCORRECT CONV GIVEN'
785 WHERE load_status = 'UNLOADED'
786 AND Trunc(processed_date) >= Trunc(p_start_date)
787 AND Trunc(processed_date) <= Trunc(p_end_date)
788 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
789 AND trx_type <> 'FORECAST'
790 AND transaction_currency_code IS NULL
791 AND exchange_rate IS NULL
792 AND acctd_transaction_amount IS NOT NULL
793 AND acctd_transaction_amount <> transaction_amount; */
794 IF (p_salesrep_id IS NULL) THEN
795 UPDATE cn_comm_lines_api_all
796 SET load_status = 'ERROR - INCORRECT CONV GIVEN'
797 WHERE load_status = 'UNLOADED'
798 AND processed_date >= TRUNC(p_start_date)
799 AND processed_date <(TRUNC(p_end_date) + 1)
800 AND trx_type <> 'FORECAST'
801 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
802 AND transaction_currency_code IS NULL
803 AND exchange_rate IS NULL
804 AND acctd_transaction_amount IS NOT NULL
805 AND acctd_transaction_amount <> transaction_amount
806 AND org_id = p_org_id;
807 ELSE
808 UPDATE cn_comm_lines_api_all
809 SET load_status = 'ERROR - INCORRECT CONV GIVEN'
810 WHERE load_status = 'UNLOADED'
811 AND processed_date >= TRUNC(p_start_date)
812 AND processed_date <(TRUNC(p_end_date) + 1)
813 AND salesrep_id = p_salesrep_id
814 AND trx_type <> 'FORECAST'
815 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
816 AND transaction_currency_code IS NULL
817 AND exchange_rate IS NULL
818 AND acctd_transaction_amount IS NOT NULL
819 AND acctd_transaction_amount <> transaction_amount
820 AND org_id = p_org_id;
821 END IF;
822
823 /* Do foreign-to-functional currency conversion */
824 -- Commented this query to fix bug# 1772128
825 /*
826 UPDATE cn_comm_lines_api
827 SET acctd_transaction_amount = (transaction_amount * exchange_rate)
828 WHERE load_status = 'UNLOADED'
829 AND Trunc(processed_date) >= Trunc(p_start_date)
830 AND Trunc(processed_date) <= Trunc(p_end_date)
831 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
832 AND trx_type <> 'FORECAST'
833 AND acctd_transaction_amount IS NULL
834 AND exchange_rate IS NOT NULL
835 AND transaction_currency_code IS NOT NULL; */
836 IF (p_salesrep_id IS NULL) THEN
837 UPDATE cn_comm_lines_api_all
838 SET acctd_transaction_amount =(transaction_amount * exchange_rate)
839 WHERE load_status = 'UNLOADED'
840 AND processed_date >= TRUNC(p_start_date)
841 AND processed_date <(TRUNC(p_end_date) + 1)
842 AND trx_type <> 'FORECAST'
843 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
844 AND acctd_transaction_amount IS NULL
845 AND exchange_rate IS NOT NULL
846 AND transaction_currency_code IS NOT NULL
847 AND org_id = p_org_id;
848 ELSE
849 UPDATE cn_comm_lines_api_all
850 SET acctd_transaction_amount =(transaction_amount * exchange_rate)
851 WHERE load_status = 'UNLOADED'
852 AND processed_date >= TRUNC(p_start_date)
853 AND processed_date <(TRUNC(p_end_date) + 1)
854 AND salesrep_id = p_salesrep_id
855 AND trx_type <> 'FORECAST'
856 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
857 AND acctd_transaction_amount IS NULL
858 AND exchange_rate IS NOT NULL
859 AND transaction_currency_code IS NOT NULL
860 AND org_id = p_org_id;
861 END IF;
862
863 debugmsg(
864 'Loader : Pre_Conc_Dispatch : Multi-currency: '
865 || TO_CHAR(SQL%ROWCOUNT)
866 || ' records transaction-to-functional currency conversion performed.'
867 );
868
869 /* Default lines w/o both curr code and exch rate to functional currency */
870 -- Commented this query to fix bug# 1772128
871 /*
872 UPDATE cn_comm_lines_api SET acctd_transaction_amount = transaction_amount,
873 transaction_currency_code = FunctionalCurrency, exchange_rate = 1
874 WHERE load_status = 'UNLOADED'
875 AND Trunc(processed_date) >= Trunc(p_start_date)
876 AND Trunc(processed_date) <= Trunc(p_end_date)
877 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
878 AND trx_type <> 'FORECAST'
879 AND acctd_transaction_amount IS NULL
880 AND exchange_rate IS NULL
881 AND transaction_currency_code IS NULL; */
882 IF (p_salesrep_id IS NULL) THEN
883 UPDATE cn_comm_lines_api_all
884 SET acctd_transaction_amount = transaction_amount
885 , transaction_currency_code = functionalcurrency
886 , exchange_rate = 1
887 WHERE load_status = 'UNLOADED'
888 AND processed_date >= TRUNC(p_start_date)
889 AND processed_date <(TRUNC(p_end_date) + 1)
890 AND trx_type <> 'FORECAST'
891 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
892 AND acctd_transaction_amount IS NULL
893 AND exchange_rate IS NULL
894 AND transaction_currency_code IS NULL
895 AND org_id = p_org_id;
896 ELSE
897 UPDATE cn_comm_lines_api_all
898 SET acctd_transaction_amount = transaction_amount
899 , transaction_currency_code = functionalcurrency
900 , exchange_rate = 1
901 WHERE load_status = 'UNLOADED'
902 AND processed_date >= TRUNC(p_start_date)
903 AND processed_date <(TRUNC(p_end_date) + 1)
904 AND salesrep_id = p_salesrep_id
905 AND trx_type <> 'FORECAST'
906 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
907 AND acctd_transaction_amount IS NULL
908 AND exchange_rate IS NULL
909 AND transaction_currency_code IS NULL
910 AND org_id = p_org_id;
911 END IF;
912
913 debugmsg(
914 'Loader : Pre_Conc_Dispatch : Multi-currency: '
915 || TO_CHAR(SQL%ROWCOUNT)
916 || ' records defaulted to functional currency.'
917 );
918
919 /* Catch any lines that couldn't be converted, last ditch */
920 -- Commented this query to fix bug# 1772128
921 /*
922 UPDATE cn_comm_lines_api SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
923 WHERE load_status = 'UNLOADED'
924 AND Trunc(processed_date) >= Trunc(p_start_date)
925 AND Trunc(processed_date) <= Trunc(p_end_date)
926 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
927 AND trx_type <> 'FORECAST'
928 AND acctd_transaction_amount IS NULL; */
929 IF (p_salesrep_id IS NULL) THEN
930 UPDATE cn_comm_lines_api_all
931 SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
932 WHERE load_status = 'UNLOADED'
933 AND processed_date >= TRUNC(p_start_date)
934 AND processed_date <(TRUNC(p_end_date) + 1)
935 AND trx_type <> 'FORECAST'
936 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
937 AND acctd_transaction_amount IS NULL
938 AND org_id = p_org_id;
939 ELSE
940 UPDATE cn_comm_lines_api_all
941 SET load_status = 'ERROR - CANNOT CONV/DEFAULT'
942 WHERE load_status = 'UNLOADED'
943 AND processed_date >= TRUNC(p_start_date)
944 AND processed_date <(TRUNC(p_end_date) + 1)
945 AND salesrep_id = p_salesrep_id
946 AND trx_type <> 'FORECAST'
947 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
948 AND acctd_transaction_amount IS NULL
949 AND org_id = p_org_id;
950 END IF;
951
952 debugmsg(
953 'Loader : Pre_Conc_Dispatch : Multi-currency: '
954 || TO_CHAR(SQL%ROWCOUNT)
955 || ' records could not be converted nor defaulted.'
956 );
957 /*****************************************/
958 /* End of multi-currency validation */
959 /*****************************************/
960 END pre_conc_dispatch;
961
962 -- Procedure Name
963 -- Post_Conc_Dispatch
964 -- Purpose
965 PROCEDURE post_conc_dispatch(
966 p_salesrep_id NUMBER
967 , p_start_date DATE
968 , p_end_date DATE
969 , p_org_id NUMBER
970 ) IS
971 BEGIN
972 -- Commented this query to fix bug# 1772128
973 /*
974 UPDATE cn_comm_lines_api SET load_status = 'SALESREP ERROR'
975 WHERE load_Status = 'UNLOADED'
976 AND Trunc(processed_date) >= Trunc(p_start_date)
977 AND Trunc(processed_date) <= Trunc(p_end_date)
978 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
979 AND trx_type <> 'FORECAST'
980 AND NOT EXISTS (SELECT 1 FROM cn_salesreps
981 WHERE employee_number =
982 cn_comm_lines_api.employee_number); */
983 IF (p_salesrep_id IS NULL) THEN
984 UPDATE cn_comm_lines_api_all
985 SET load_status = 'SALESREP ERROR'
986 WHERE load_status = 'UNLOADED'
987 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
988 AND processed_date >= TRUNC(p_start_date)
989 AND processed_date <(TRUNC(p_end_date) + 1)
990 AND trx_type <> 'FORECAST'
991 AND org_id = p_org_id
992 AND NOT EXISTS(SELECT 1
993 FROM cn_salesreps
994 WHERE employee_number = cn_comm_lines_api_all.employee_number);
995 ELSE
996 UPDATE cn_comm_lines_api_all
997 SET load_status = 'SALESREP ERROR'
998 WHERE load_status = 'UNLOADED'
999 AND (adjust_status <> 'SCA_PENDING') -- OR adjust_status IS NULL)
1000 AND processed_date >= TRUNC(p_start_date)
1001 AND processed_date <(TRUNC(p_end_date) + 1)
1002 AND salesrep_id = p_salesrep_id
1003 AND trx_type <> 'FORECAST'
1004 AND org_id = p_org_id
1005 AND NOT EXISTS(SELECT 1
1006 FROM cn_salesreps
1007 WHERE employee_number = cn_comm_lines_api_all.employee_number);
1008 END IF;
1009
1010 debugmsg('Loader : Post_Conc_Dispatch : # of SALESREP ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1011
1012 -- Commented this query to fix bug# 1772128
1013 /*
1014 UPDATE cn_comm_lines_api SET load_status = 'PERIOD ERROR'
1015 WHERE load_Status = 'UNLOADED'
1016 AND Trunc(processed_date) >= Trunc(p_start_date)
1017 AND Trunc(processed_date) <= Trunc(p_end_date)
1018 AND ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id))
1019 AND trx_type <> 'FORECAST';*/
1020 IF (p_salesrep_id IS NULL) THEN
1021 UPDATE cn_comm_lines_api_all
1022 SET load_status = 'PERIOD ERROR'
1023 WHERE load_status = 'UNLOADED'
1024 AND processed_date >= TRUNC(p_start_date)
1025 AND processed_date <(TRUNC(p_end_date) + 1)
1026 AND trx_type <> 'FORECAST'
1027 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1028 AND org_id = p_org_id;
1029 ELSE
1030 UPDATE cn_comm_lines_api_all
1031 SET load_status = 'PERIOD ERROR'
1032 WHERE load_status = 'UNLOADED'
1033 AND processed_date >= TRUNC(p_start_date)
1034 AND processed_date <(TRUNC(p_end_date) + 1)
1035 AND salesrep_id = p_salesrep_id
1036 AND trx_type <> 'FORECAST'
1037 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1038 AND org_id = p_org_id;
1039 END IF;
1040
1041 debugmsg('Loader : Post_Conc_Dispatch : # of PERIOD ERROR = ' || TO_CHAR(SQL%ROWCOUNT));
1042 END post_conc_dispatch;
1043
1044 -- Procedure Name
1045 -- Check_Api_Data
1046 -- Purpose
1047 PROCEDURE check_api_data(p_start_date DATE, p_end_date DATE, p_org_id NUMBER) IS
1048 l_cn_reset_error_trx VARCHAR2(1);
1049 BEGIN
1050 --+
1051 --+ Reset the error transactions
1052 --+
1053
1054 -- performance bug 1690393 : full table scan
1055 -- original statement
1056 -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1057 -- SET api.load_status = 'UNLOADED'
1058 -- WHERE api.load_status <> 'UNLOADED'
1059 -- AND api.trx_type <> 'FORECAST'
1060 -- AND api.load_status <> 'OBSOLETE'
1061 -- AND api.load_status <> 'FILTERED' -- for v1152
1062 -- AND api.load_status <> 'LOADED'
1063 -- AND Trunc(api.processed_date) >= Trunc(p_start_date)
1064 -- AND Trunc(api.processed_date) <= Trunc(p_end_date);
1065
1066 -- new statment
1067
1068 -- performance bug 2035228
1069 l_cn_reset_error_trx := cn_system_parameters.VALUE('CN_RESET_ERROR_TRX', p_org_id);
1070 debugmsg('Loader : OSC Profile - Reset Error Transaction is ' || l_cn_reset_error_trx);
1071
1072 IF l_cn_reset_error_trx = 'Y' THEN
1073 debugmsg('Reset load status of error transactions to UNLOADED');
1074
1075 -- UPDATE /* index (api, mis_cn_comm_lines_api_n1) */ cn_comm_lines_api api
1076 UPDATE cn_comm_lines_api_all api
1077 SET api.load_status = 'UNLOADED'
1078 WHERE api.trx_type <> 'FORECAST'
1079 AND api.load_status IN(
1080 'ERROR - PRIOR ADJUSTMENT'
1081 , 'ERROR - TRX_TYPE'
1082 , 'ERROR - REVENUE_CLASS'
1083 , 'ERROR - NO EXCH RATE GIVEN'
1084 , 'ERROR - INCORRECT CONV GIVEN'
1085 , 'ERROR - CANNOT CONV/DEFAULT'
1086 , 'SALESREP ERROR'
1087 , 'PERIOD ERROR'
1088 )
1089 AND api.processed_date >= TRUNC(p_start_date)
1090 AND api.processed_date <(TRUNC(p_end_date) + 1)
1091 AND api.org_id = p_org_id;
1092 ELSE
1093 debugmsg('Loader : Skip the process of reseting error transactions.');
1094 END IF;
1095
1096 --+
1097 --+ Update null salerep_id based on the given employee_number
1098 --+
1099 UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
1100 SET api.salesrep_id =
1101 (SELECT cs1.salesrep_id
1102 FROM cn_salesreps cs1
1103 WHERE cs1.employee_number = api.employee_number
1104 AND cs1.org_id = api.org_id -- vensrini
1105 AND cs1.org_id = p_org_id) -- vensrini
1106 WHERE api.salesrep_id IS NULL
1107 AND api.load_status = 'UNLOADED'
1108 AND api.trx_type <> 'FORECAST'
1109 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1110 AND EXISTS(
1111 SELECT /*+ NO_UNNEST */ employee_number
1112 FROM cn_salesreps cs
1113 WHERE api.employee_number = cs.employee_number
1114 AND cs.org_id = api.org_id -- vensrini
1115 AND cs.org_id = p_org_id) -- vensrini
1116 AND api.processed_date >= TRUNC(p_start_date)
1117 AND api.processed_date <(TRUNC(p_end_date) + 1)
1118 AND api.org_id = p_org_id;
1119
1120 --+
1121 --+ Update null employee_number based on the given salesrep_id
1122 --+
1123 -- UPDATE /*+ index(api, cn_comm_lines_api_n1)*/ cn_comm_lines_api_all api
1124 UPDATE cn_comm_lines_api_all api
1125 SET employee_number =
1126 (SELECT employee_number
1127 FROM cn_salesreps cs1
1128 WHERE cs1.salesrep_id = api.salesrep_id
1129 AND cs1.org_id = api.org_id -- vensrini
1130 AND cs1.org_id = p_org_id) -- vensrini
1131 WHERE employee_number IS NULL
1132 AND load_status = 'UNLOADED'
1133 AND trx_type <> 'FORECAST'
1134 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1135 AND EXISTS(
1136 SELECT /*+ NO_UNNEST*/ salesrep_id
1137 FROM cn_salesreps cs
1138 WHERE api.salesrep_id = cs.salesrep_id
1139 AND cs.org_id = api.org_id -- vensrini
1140 AND cs.org_id = p_org_id) -- vensrini
1141 AND api.processed_date >= TRUNC(p_start_date)
1142 AND api.processed_date <(TRUNC(p_end_date) + 1)
1143 AND org_id = p_org_id;
1144
1145 --+
1146 --+ IF both salesrep_id and employee_number are null,
1147 --+ set load_status to SALESREP ERROR
1148 --+
1149 UPDATE /*+ index(api, cn_comm_lines_api_f2)*/ cn_comm_lines_api_all api
1150 SET api.load_status = 'SALESREP ERROR'
1151 WHERE api.load_status = 'UNLOADED'
1152 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1153 AND api.salesrep_id IS NULL
1154 AND api.employee_number IS NULL
1155 AND api.processed_date >= TRUNC(p_start_date)
1156 AND api.processed_date <(TRUNC(p_end_date) + 1)
1157 AND api.org_id = p_org_id;
1158 END check_api_data;
1159
1160 -- Name:
1161 -- Load
1162 -- Purpose:
1163 -- This procedure loads trx from CN_COMM_LINES_API to CN_COMMISSION_HEADERS,
1164 -- update cn_process_batches, and perform classification, and rollup phases
1165 PROCEDURE load(
1166 errbuf OUT NOCOPY VARCHAR2
1167 , retcode OUT NOCOPY NUMBER
1168 , p_salesrep_id IN NUMBER
1169 , pp_start_date IN VARCHAR2
1170 , pp_end_date IN VARCHAR2
1171 , p_cls_rol_flag IN VARCHAR2
1172 , p_org_id IN NUMBER
1173 ) IS
1174 l_skip_credit_flag VARCHAR2(1);
1175 l_logical_batch_id NUMBER;
1176 l_process_audit_id NUMBER;
1177
1178 l_start_date DATE;
1179 l_end_date DATE;
1180 l_open_period NUMBER;
1181 BEGIN
1182 -- Convert the dates for the varchar2 parameters passed in from concurrent program
1183 l_start_date := fnd_date.canonical_to_date(pp_start_date);
1184 l_end_date := fnd_date.canonical_to_date(pp_end_date);
1185
1186 --+
1187 --+ Call begin_batch to get process_audit_id for debug log file
1188 --+
1189 cn_message_pkg.begin_batch(
1190 x_process_type => 'LOADER'
1191 , x_parent_proc_audit_id => NULL
1192 , x_process_audit_id => l_process_audit_id
1193 , x_request_id => fnd_global.conc_request_id
1194 , p_org_id => p_org_id
1195 );
1196
1197 debugmsg('Loader : Start of Loader');
1198 debugmsg('Loader : process_audit_id is ' || l_process_audit_id);
1199
1200 /* verify that parameter end date is within an open acc period */
1201 l_open_period := 0;
1202 SELECT COUNT(*) INTO l_open_period
1203 FROM cn_period_statuses_all
1204 WHERE period_status = 'O'
1205 AND org_id = p_org_id
1206 AND (period_set_id, period_type_id) =
1207 (SELECT period_set_id, period_type_id
1208 FROM cn_repositories_all
1209 WHERE org_id = p_org_id)
1210 AND l_end_date BETWEEN start_date AND end_date;
1211
1212 IF (l_open_period = 0) THEN
1213 debugmsg('Loader : Parameter End Date is not within an open acc period');
1214 RAISE invalid_date;
1215 END IF;
1216
1217 /* Get the value of the profile "OIC: Skip Credit Allocation" */
1218 l_skip_credit_flag := 'Y';
1219 IF (fnd_profile.defined('CN_SKIP_CREDIT_ALLOCATION')) THEN
1220 l_skip_credit_flag := NVL(fnd_profile.VALUE('CN_SKIP_CREDIT_ALLOCATION'), 'Y');
1221 END IF;
1222
1223 --+
1224 --+ Check Data in API table
1225 --+
1226 check_api_data(p_start_date => l_start_date, p_end_date => l_end_date, p_org_id => p_org_id);
1227
1228 --+
1229 --+ Validate ruleset status if the classification and
1230 --+ rollup option is checked.
1231 --+
1232 IF (p_cls_rol_flag = 'Y') THEN
1233 debugmsg('Loader : validate ruleset status : p_start_date = ' || l_start_date);
1234 debugmsg('Loader : validate ruleset status : p_end_date = ' || l_end_date);
1235
1236 IF NOT cn_proc_batches_pkg.validate_ruleset_status(l_start_date, l_end_date, p_org_id) THEN
1237 debugmsg('Loader : validate ruleset fails.');
1238 RAISE fail_validate_ruleset;
1239 END IF;
1240 END IF;
1241
1242 SELECT cn_process_batches_s2.NEXTVAL INTO l_logical_batch_id FROM dual;
1243
1244 INSERT INTO cn_process_batches(
1245 process_batch_id
1246 , logical_batch_id
1247 , srp_period_id
1248 , period_id
1249 , end_period_id
1250 , start_date
1251 , end_date
1252 , salesrep_id
1253 , sales_lines_total
1254 , status_code
1255 , process_batch_type
1256 , creation_date
1257 , created_by
1258 , last_update_date
1259 , last_updated_by
1260 , last_update_login
1261 , request_id
1262 , program_application_id
1263 , program_id
1264 , program_update_date
1265 , org_id
1266 )
1267 ( SELECT cn_process_batches_s1.NEXTVAL
1268 , l_logical_batch_id
1269 , 1 -- a dummy value for a not null column
1270 , batch.period_id -- Start Period Id
1271 , batch.period_id -- End Period Id
1272 , batch.start_date
1273 , batch.end_date
1274 , batch.salesrep_id
1275 , batch.trx_count
1276 , 'IN_USE' -- Status Code
1277 , 'CREATED_BY_LOADER' -- Process Batch Type
1278 , SYSDATE
1279 , fnd_global.user_id
1280 , SYSDATE
1281 , fnd_global.user_id
1282 , fnd_global.login_id
1283 , fnd_global.conc_request_id
1284 , fnd_global.prog_appl_id
1285 , fnd_global.conc_program_id
1286 , SYSDATE
1287 , p_org_id
1288 FROM (
1289 SELECT api.employee_number employee_number
1290 , api.salesrep_id salesrep_id
1291 , acc.period_id period_id
1292 , acc.start_date start_date
1293 , acc.end_date end_date
1294 , COUNT(*) trx_count
1295 FROM cn_comm_lines_api api, cn_acc_period_statuses_v acc
1296 WHERE api.load_status = 'UNLOADED'
1297 AND api.trx_type <> 'FORECAST'
1298 AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
1299 AND api.processed_date >= TRUNC(l_start_date)
1300 AND api.processed_date <(TRUNC(l_end_date) + 1)
1301 AND ((p_salesrep_id IS NULL) OR(api.salesrep_id = p_salesrep_id))
1302 AND api.salesrep_id IS NOT NULL
1303 AND api.processed_date >= acc.start_date
1304 AND api.processed_date <(acc.end_date + 1)
1305 AND ( l_skip_credit_flag = 'Y'
1306 OR (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') )
1307 GROUP BY api.employee_number, api.salesrep_id, acc.period_id, acc.start_date, acc.end_date
1308 ) batch );
1309
1310 --+
1311 --+ If no trx to load, raise exception
1312 --+
1313 IF (SQL%ROWCOUNT = 0) THEN
1314 debugmsg('Loader : No transactions to load.');
1315 RAISE no_trx_lines;
1316 END IF;
1317
1318 -- Split the logical batch into smaller physical batches
1319 -- populate the physical_batch_id in cn_process_batches
1320 assign(p_logical_batch_id => l_logical_batch_id, p_org_id => p_org_id);
1321
1322 -- Submit independent concurrent programs for each physical batch
1323 -- These physical batches will be executed in parallel
1324 pre_conc_dispatch(
1325 p_salesrep_id => p_salesrep_id
1326 , p_start_date => l_start_date
1327 , p_end_date => l_end_date
1328 , p_org_id => p_org_id
1329 );
1330
1331 conc_dispatch(
1332 x_parent_proc_audit_id => l_process_audit_id
1333 , x_salesrep_id => p_salesrep_id
1334 , x_start_date => l_start_date
1335 , x_end_date => l_end_date
1336 , x_cls_rol_flag => p_cls_rol_flag
1337 , x_logical_batch_id => l_logical_batch_id
1338 );
1339
1340 post_conc_dispatch(
1341 p_salesrep_id => p_salesrep_id
1342 , p_start_date => l_start_date
1343 , p_end_date => l_end_date
1344 , p_org_id => p_org_id
1345 );
1346
1347 -- Mark the processed batches for deletion
1348 void_batches(p_physical_batch_id => NULL, p_logical_batch_id => l_logical_batch_id);
1349
1350 -- Call end_batch to end debug log file
1351 debugmsg('Loader : End of Loader');
1352 cn_message_pkg.end_batch(l_process_audit_id);
1353
1354 EXCEPTION
1355 WHEN invalid_date THEN
1356 -- Call end_batch to end debug log file
1357 errbuf := 'Parameter End Date is not within an open acc period';
1358 retcode := 1;
1359 debugmsg('Loader : End of Loader');
1360 cn_message_pkg.end_batch(l_process_audit_id);
1361 WHEN no_trx_lines THEN
1362 -- Call end_batch to end debug log file
1363 errbuf := 'No transactions to load';
1364 retcode := 1;
1365 debugmsg('Loader : End of Loader');
1366 cn_message_pkg.end_batch(l_process_audit_id);
1367 WHEN fail_validate_ruleset THEN
1368 errbuf := 'Ruleset validation failed';
1369 retcode := 1;
1370 debugmsg('Loader : validate ruleset fails.');
1371 debugmsg('Loader : End of Loader');
1372 cn_message_pkg.end_batch(l_process_audit_id);
1373 WHEN OTHERS THEN
1374 debugmsg('Loader : Unexpected exception.');
1375 -- Call end_batch to end debug log file
1376 errbuf := SQLERRM;
1377 retcode := 2;
1378 debugmsg('Loader : End of Loader');
1379 cn_message_pkg.end_batch(l_process_audit_id);
1380 END LOAD;
1381
1382 -- Procedure Name
1383 -- Assign
1384 -- Purpose
1385 PROCEDURE load_worker(
1386 p_physical_batch_id NUMBER
1387 , p_salesrep_id NUMBER
1388 , p_start_date DATE
1389 , p_end_date DATE
1390 , p_cls_rol_flag VARCHAR2
1391 ) IS
1392 CURSOR batches IS
1393 SELECT salesrep_id
1394 , period_id
1395 , start_date
1396 , end_date
1397 , sales_lines_total trx_count
1398 FROM cn_process_batches
1399 WHERE physical_batch_id = p_physical_batch_id AND status_code = 'IN_USE';
1400
1401 counter NUMBER;
1402 l_counter NUMBER;
1403 l_msg_count NUMBER;
1404 l_msg_data VARCHAR2(2000);
1405 l_return_status VARCHAR2(30);
1406 l_init_commission_header_id NUMBER;
1407 l_skip_credit_flag VARCHAR2(1);
1408 BEGIN
1409 counter := 0;
1410 /* Get the value of the profile "OIC: Skip Credit Allocation" */
1411 l_skip_credit_flag := 'Y';
1412
1413 IF (fnd_profile.defined('CN_SKIP_CREDIT_ALLOCATION')) THEN
1414 l_skip_credit_flag := NVL(fnd_profile.VALUE('CN_SKIP_CREDIT_ALLOCATION'), 'Y');
1415 END IF;
1416
1417 -- this is used to make it more restrict for handling reversal trx later on
1418 SELECT cn_commission_headers_s.NEXTVAL
1419 INTO l_init_commission_header_id
1420 FROM DUAL;
1421
1422 FOR batch IN batches LOOP
1423 debugmsg(
1424 'Loader : Load_Worker : Load '
1425 || TO_CHAR(batch.trx_count)
1426 || ' lines for physical batch = '
1427 || p_physical_batch_id
1428 || ' salesrep id = '
1429 || batch.salesrep_id
1430 || ' period_id = '
1431 || batch.period_id
1432 || ' p_salesrep_id = '
1433 || p_salesrep_id
1434 || ' p_start_date = '
1435 || p_start_date
1436 || ' p_end_date = '
1437 || p_end_date
1438 || ' p_cls_rol_flag = '
1439 || p_cls_rol_flag
1440 );
1441 counter := counter + batch.trx_count;
1442
1443 IF (l_skip_credit_flag = 'Y') THEN
1444 INSERT INTO cn_commission_headers
1445 (
1446 commission_header_id
1447 , direct_salesrep_id
1448 , processed_date
1449 , processed_period_id
1450 , rollup_date
1451 , transaction_amount
1452 , quantity
1453 , discount_percentage
1454 , margin_percentage
1455 , orig_currency_code
1456 , transaction_amount_orig
1457 , trx_type
1458 , status
1459 , pre_processed_code
1460 , comm_lines_api_id
1461 , source_doc_type
1462 , source_trx_number
1463 , quota_id
1464 , srp_plan_assign_id
1465 , revenue_class_id
1466 , role_id
1467 , comp_group_id
1468 , commission_amount
1469 , reversal_flag
1470 , reversal_header_id
1471 , reason_code
1472 , attribute_category
1473 , attribute1
1474 , attribute2
1475 , attribute3
1476 , attribute4
1477 , attribute5
1478 , attribute6
1479 , attribute7
1480 , attribute8
1481 , attribute9
1482 , attribute10
1483 , attribute11
1484 , attribute12
1485 , attribute13
1486 , attribute14
1487 , attribute15
1488 , attribute16
1489 , attribute17
1490 , attribute18
1491 , attribute19
1492 , attribute20
1493 , attribute21
1494 , attribute22
1495 , attribute23
1496 , attribute24
1497 , attribute25
1498 , attribute26
1499 , attribute27
1500 , attribute28
1501 , attribute29
1502 , attribute30
1503 , attribute31
1504 , attribute32
1505 , attribute33
1506 , attribute34
1507 , attribute35
1508 , attribute36
1509 , attribute37
1510 , attribute38
1511 , attribute39
1512 , attribute40
1513 , attribute41
1514 , attribute42
1515 , attribute43
1516 , attribute44
1517 , attribute45
1518 , attribute46
1519 , attribute47
1520 , attribute48
1521 , attribute49
1522 , attribute50
1523 , attribute51
1524 , attribute52
1525 , attribute53
1526 , attribute54
1527 , attribute55
1528 , attribute56
1529 , attribute57
1530 , attribute58
1531 , attribute59
1532 , attribute60
1533 , attribute61
1534 , attribute62
1535 , attribute63
1536 , attribute64
1537 , attribute65
1538 , attribute66
1539 , attribute67
1540 , attribute68
1541 , attribute69
1542 , attribute70
1543 , attribute71
1544 , attribute72
1545 , attribute73
1546 , attribute74
1547 , attribute75
1548 , attribute76
1549 , attribute77
1550 , attribute78
1551 , attribute79
1552 , attribute80
1553 , attribute81
1554 , attribute82
1555 , attribute83
1556 , attribute84
1557 , attribute85
1558 , attribute86
1559 , attribute87
1560 , attribute88
1561 , attribute89
1562 , attribute90
1563 , attribute91
1564 , attribute92
1565 , attribute93
1566 , attribute94
1567 , attribute95
1568 , attribute96
1569 , attribute97
1570 , attribute98
1571 , attribute99
1572 , attribute100
1573 , last_update_date
1574 , last_updated_by
1575 , last_update_login
1576 , creation_date
1577 , created_by
1578 , exchange_rate
1579 , forecast_id
1580 , upside_quantity
1581 , upside_amount
1582 , uom_code
1583 , source_trx_id
1584 , source_trx_line_id
1585 , source_trx_sales_line_id
1586 , negated_flag
1587 , customer_id
1588 , inventory_item_id
1589 , order_number
1590 , booked_date
1591 , invoice_number
1592 , invoice_date
1593 , bill_to_address_id
1594 , ship_to_address_id
1595 , bill_to_contact_id
1596 , ship_to_contact_id
1597 , adj_comm_lines_api_id
1598 , adjust_date
1599 , adjusted_by
1600 , revenue_type
1601 , adjust_rollup_flag
1602 , adjust_comments
1603 , adjust_status
1604 , line_number
1605 , TYPE
1606 , sales_channel
1607 , split_pct
1608 , split_status
1609 , org_id
1610 ) -- vensrini transaction load fix
1611 (SELECT cn_commission_headers_s.NEXTVAL
1612 , batch.salesrep_id
1613 , TRUNC(api.processed_date)
1614 , batch.period_id
1615 , TRUNC(api.rollup_date)
1616 , api.acctd_transaction_amount
1617 , api.quantity
1618 , api.discount_percentage
1619 , api.margin_percentage
1620 , api.transaction_currency_code
1621 , api.transaction_amount
1622 , api.trx_type
1623 , 'COL'
1624 , NVL(api.pre_processed_code, 'CRPC')
1625 , api.comm_lines_api_id
1626 , api.source_doc_type
1627 , api.source_trx_number
1628 , api.quota_id
1629 , api.srp_plan_assign_id
1630 , api.revenue_class_id
1631 , api.role_id
1632 , api.comp_group_id
1633 , api.commission_amount
1634 , api.reversal_flag
1635 , api.reversal_header_id
1636 , api.reason_code
1637 , api.attribute_category
1638 , api.attribute1
1639 , api.attribute2
1640 , api.attribute3
1641 , api.attribute4
1642 , api.attribute5
1643 , api.attribute6
1644 , api.attribute7
1645 , api.attribute8
1646 , api.attribute9
1647 , api.attribute10
1648 , api.attribute11
1649 , api.attribute12
1650 , api.attribute13
1651 , api.attribute14
1652 , api.attribute15
1653 , api.attribute16
1654 , api.attribute17
1655 , api.attribute18
1656 , api.attribute19
1657 , api.attribute20
1658 , api.attribute21
1659 , api.attribute22
1660 , api.attribute23
1661 , api.attribute24
1662 , api.attribute25
1663 , api.attribute26
1664 , api.attribute27
1665 , api.attribute28
1666 , api.attribute29
1667 , api.attribute30
1668 , api.attribute31
1669 , api.attribute32
1670 , api.attribute33
1671 , api.attribute34
1672 , api.attribute35
1673 , api.attribute36
1674 , api.attribute37
1675 , api.attribute38
1676 , api.attribute39
1677 , api.attribute40
1678 , api.attribute41
1679 , api.attribute42
1680 , api.attribute43
1681 , api.attribute44
1682 , api.attribute45
1683 , api.attribute46
1684 , api.attribute47
1685 , api.attribute48
1686 , api.attribute49
1687 , api.attribute50
1688 , api.attribute51
1689 , api.attribute52
1690 , api.attribute53
1691 , api.attribute54
1692 , api.attribute55
1693 , api.attribute56
1694 , api.attribute57
1695 , api.attribute58
1696 , api.attribute59
1697 , api.attribute60
1698 , api.attribute61
1699 , api.attribute62
1700 , api.attribute63
1701 , api.attribute64
1702 , api.attribute65
1703 , api.attribute66
1704 , api.attribute67
1705 , api.attribute68
1706 , api.attribute69
1707 , api.attribute70
1708 , api.attribute71
1709 , api.attribute72
1710 , api.attribute73
1711 , api.attribute74
1712 , api.attribute75
1713 , api.attribute76
1714 , api.attribute77
1715 , api.attribute78
1716 , api.attribute79
1717 , api.attribute80
1718 , api.attribute81
1719 , api.attribute82
1720 , api.attribute83
1721 , api.attribute84
1722 , api.attribute85
1723 , api.attribute86
1724 , api.attribute87
1725 , api.attribute88
1726 , api.attribute89
1727 , api.attribute90
1728 , api.attribute91
1729 , api.attribute92
1730 , api.attribute93
1731 , api.attribute94
1732 , api.attribute95
1733 , api.attribute96
1734 , api.attribute97
1735 , api.attribute98
1736 , api.attribute99
1737 , api.attribute100
1738 , SYSDATE
1739 , api.last_updated_by
1740 , api.last_update_login
1741 , SYSDATE
1742 , api.created_by
1743 , api.exchange_rate
1744 , api.forecast_id
1745 , api.upside_quantity
1746 , api.upside_amount
1747 , api.uom_code
1748 , api.source_trx_id
1749 , api.source_trx_line_id
1750 , api.source_trx_sales_line_id
1751 , api.negated_flag
1752 , api.customer_id
1753 , api.inventory_item_id
1754 , api.order_number
1755 , api.booked_date
1756 , api.invoice_number
1757 , api.invoice_date
1758 , api.bill_to_address_id
1759 , api.ship_to_address_id
1760 , api.bill_to_contact_id
1761 , api.ship_to_contact_id
1762 , api.adj_comm_lines_api_id
1763 , api.adjust_date
1764 , api.adjusted_by
1765 , api.revenue_type
1766 , api.adjust_rollup_flag
1767 , api.adjust_comments
1768 , NVL(api.adjust_status,'NEW')
1769 , api.line_number
1770 , api.TYPE
1771 , api.sales_channel
1772 , api.split_pct
1773 , api.split_status
1774 , api.org_id -- vensrini transaction load fix
1775 FROM cn_comm_lines_api api
1776 WHERE api.load_status = 'UNLOADED'
1777 AND api.processed_date >= TRUNC(p_start_date)
1778 AND api.processed_date <(TRUNC(p_end_date) + 1)
1779 AND api.trx_type <> 'FORECAST'
1780 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
1781 AND api.salesrep_id = batch.salesrep_id
1782 AND api.processed_date >= TRUNC(batch.start_date)
1783 AND api.processed_date <(TRUNC(batch.end_date) + 1)
1784 AND NOT EXISTS(SELECT 'this transaction has already been loaded'
1785 FROM cn_commission_headers_all cmh
1786 WHERE cmh.comm_lines_api_id = api.comm_lines_api_id));
1787 ELSE
1788 INSERT INTO cn_commission_headers
1789 (
1790 commission_header_id
1791 , direct_salesrep_id
1792 , processed_date
1793 , processed_period_id
1794 , rollup_date
1795 , transaction_amount
1796 , quantity
1797 , discount_percentage
1798 , margin_percentage
1799 , orig_currency_code
1800 , transaction_amount_orig
1801 , trx_type
1802 , status
1803 , pre_processed_code
1804 , comm_lines_api_id
1805 , source_doc_type
1806 , source_trx_number
1807 , quota_id
1808 , srp_plan_assign_id
1809 , revenue_class_id
1810 , role_id
1811 , comp_group_id
1812 , commission_amount
1813 , reversal_flag
1814 , reversal_header_id
1815 , reason_code
1816 , attribute_category
1817 , attribute1
1818 , attribute2
1819 , attribute3
1820 , attribute4
1821 , attribute5
1822 , attribute6
1823 , attribute7
1824 , attribute8
1825 , attribute9
1826 , attribute10
1827 , attribute11
1828 , attribute12
1829 , attribute13
1830 , attribute14
1831 , attribute15
1832 , attribute16
1833 , attribute17
1834 , attribute18
1835 , attribute19
1836 , attribute20
1837 , attribute21
1838 , attribute22
1839 , attribute23
1840 , attribute24
1841 , attribute25
1842 , attribute26
1843 , attribute27
1844 , attribute28
1845 , attribute29
1846 , attribute30
1847 , attribute31
1848 , attribute32
1849 , attribute33
1850 , attribute34
1851 , attribute35
1852 , attribute36
1853 , attribute37
1854 , attribute38
1855 , attribute39
1856 , attribute40
1857 , attribute41
1858 , attribute42
1859 , attribute43
1860 , attribute44
1861 , attribute45
1862 , attribute46
1863 , attribute47
1864 , attribute48
1865 , attribute49
1866 , attribute50
1867 , attribute51
1868 , attribute52
1869 , attribute53
1870 , attribute54
1871 , attribute55
1872 , attribute56
1873 , attribute57
1874 , attribute58
1875 , attribute59
1876 , attribute60
1877 , attribute61
1878 , attribute62
1879 , attribute63
1880 , attribute64
1881 , attribute65
1882 , attribute66
1883 , attribute67
1884 , attribute68
1885 , attribute69
1886 , attribute70
1887 , attribute71
1888 , attribute72
1889 , attribute73
1890 , attribute74
1891 , attribute75
1892 , attribute76
1893 , attribute77
1894 , attribute78
1895 , attribute79
1896 , attribute80
1897 , attribute81
1898 , attribute82
1899 , attribute83
1900 , attribute84
1901 , attribute85
1902 , attribute86
1903 , attribute87
1904 , attribute88
1905 , attribute89
1906 , attribute90
1907 , attribute91
1908 , attribute92
1909 , attribute93
1910 , attribute94
1911 , attribute95
1912 , attribute96
1913 , attribute97
1914 , attribute98
1915 , attribute99
1916 , attribute100
1917 , last_update_date
1918 , last_updated_by
1919 , last_update_login
1920 , creation_date
1921 , created_by
1922 , exchange_rate
1923 , forecast_id
1924 , upside_quantity
1925 , upside_amount
1926 , uom_code
1927 , source_trx_id
1928 , source_trx_line_id
1929 , source_trx_sales_line_id
1930 , negated_flag
1931 , customer_id
1932 , inventory_item_id
1933 , order_number
1934 , booked_date
1935 , invoice_number
1936 , invoice_date
1937 , bill_to_address_id
1938 , ship_to_address_id
1939 , bill_to_contact_id
1940 , ship_to_contact_id
1941 , adj_comm_lines_api_id
1942 , adjust_date
1943 , adjusted_by
1944 , revenue_type
1945 , adjust_rollup_flag
1946 , adjust_comments
1947 , adjust_status
1948 , line_number
1949 , TYPE
1950 , sales_channel
1951 , split_pct
1952 , split_status
1953 , org_id
1954 ) -- vensrini transaction load fix
1955 (SELECT cn_commission_headers_s.NEXTVAL
1956 , batch.salesrep_id
1957 , TRUNC(api.processed_date)
1958 , batch.period_id
1959 , TRUNC(api.rollup_date)
1960 , api.acctd_transaction_amount
1961 , api.quantity
1962 , api.discount_percentage
1963 , api.margin_percentage
1964 , api.transaction_currency_code
1965 , api.transaction_amount
1966 , api.trx_type
1967 , 'COL'
1968 , NVL(api.pre_processed_code, 'CRPC')
1969 , api.comm_lines_api_id
1970 , api.source_doc_type
1971 , api.source_trx_number
1972 , api.quota_id
1973 , api.srp_plan_assign_id
1974 , api.revenue_class_id
1975 , api.role_id
1976 , api.comp_group_id
1977 , api.commission_amount
1978 , api.reversal_flag
1979 , api.reversal_header_id
1980 , api.reason_code
1981 , api.attribute_category
1982 , api.attribute1
1983 , api.attribute2
1984 , api.attribute3
1985 , api.attribute4
1986 , api.attribute5
1987 , api.attribute6
1988 , api.attribute7
1989 , api.attribute8
1990 , api.attribute9
1991 , api.attribute10
1992 , api.attribute11
1993 , api.attribute12
1994 , api.attribute13
1995 , api.attribute14
1996 , api.attribute15
1997 , api.attribute16
1998 , api.attribute17
1999 , api.attribute18
2000 , api.attribute19
2001 , api.attribute20
2002 , api.attribute21
2003 , api.attribute22
2004 , api.attribute23
2005 , api.attribute24
2006 , api.attribute25
2007 , api.attribute26
2008 , api.attribute27
2009 , api.attribute28
2010 , api.attribute29
2011 , api.attribute30
2012 , api.attribute31
2013 , api.attribute32
2014 , api.attribute33
2015 , api.attribute34
2016 , api.attribute35
2017 , api.attribute36
2018 , api.attribute37
2019 , api.attribute38
2020 , api.attribute39
2021 , api.attribute40
2022 , api.attribute41
2023 , api.attribute42
2024 , api.attribute43
2025 , api.attribute44
2026 , api.attribute45
2027 , api.attribute46
2028 , api.attribute47
2029 , api.attribute48
2030 , api.attribute49
2031 , api.attribute50
2032 , api.attribute51
2033 , api.attribute52
2034 , api.attribute53
2035 , api.attribute54
2036 , api.attribute55
2037 , api.attribute56
2038 , api.attribute57
2039 , api.attribute58
2040 , api.attribute59
2041 , api.attribute60
2042 , api.attribute61
2043 , api.attribute62
2044 , api.attribute63
2045 , api.attribute64
2046 , api.attribute65
2047 , api.attribute66
2048 , api.attribute67
2049 , api.attribute68
2050 , api.attribute69
2051 , api.attribute70
2052 , api.attribute71
2053 , api.attribute72
2054 , api.attribute73
2055 , api.attribute74
2056 , api.attribute75
2057 , api.attribute76
2058 , api.attribute77
2059 , api.attribute78
2060 , api.attribute79
2061 , api.attribute80
2062 , api.attribute81
2063 , api.attribute82
2064 , api.attribute83
2065 , api.attribute84
2066 , api.attribute85
2067 , api.attribute86
2068 , api.attribute87
2069 , api.attribute88
2070 , api.attribute89
2071 , api.attribute90
2072 , api.attribute91
2073 , api.attribute92
2074 , api.attribute93
2075 , api.attribute94
2076 , api.attribute95
2077 , api.attribute96
2078 , api.attribute97
2079 , api.attribute98
2080 , api.attribute99
2081 , api.attribute100
2082 , SYSDATE
2083 , api.last_updated_by
2084 , api.last_update_login
2085 , SYSDATE
2086 , api.created_by
2087 , api.exchange_rate
2088 , api.forecast_id
2089 , api.upside_quantity
2090 , api.upside_amount
2091 , api.uom_code
2092 , api.source_trx_id
2093 , api.source_trx_line_id
2094 , api.source_trx_sales_line_id
2095 , api.negated_flag
2096 , api.customer_id
2097 , api.inventory_item_id
2098 , api.order_number
2099 , api.booked_date
2100 , api.invoice_number
2101 , api.invoice_date
2102 , api.bill_to_address_id
2103 , api.ship_to_address_id
2104 , api.bill_to_contact_id
2105 , api.ship_to_contact_id
2106 , api.adj_comm_lines_api_id
2107 , api.adjust_date
2108 , api.adjusted_by
2109 , api.revenue_type
2110 , api.adjust_rollup_flag
2111 , api.adjust_comments
2112 , NVL(api.adjust_status,'NEW')
2113 , api.line_number
2114 , api.TYPE
2115 , api.sales_channel
2116 , api.split_pct
2117 , api.split_status
2118 , api.org_id -- vensrini transaction load fix
2119 FROM cn_comm_lines_api api
2120 WHERE api.load_status = 'UNLOADED'
2121 AND api.processed_date >= TRUNC(p_start_date)
2122 AND api.processed_date <(TRUNC(p_end_date) + 1)
2123 AND api.trx_type <> 'FORECAST'
2124 AND (adjust_status <> 'SCA_PENDING')-- OR adjust_status IS NULL)
2125 AND api.salesrep_id = batch.salesrep_id
2126 AND api.processed_date >= TRUNC(batch.start_date)
2127 AND api.processed_date <(TRUNC(batch.end_date) + 1)
2128 AND (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y')
2129 AND NOT EXISTS(SELECT 'this transaction has already been loaded'
2130 FROM cn_commission_headers_all cmh
2131 WHERE cmh.comm_lines_api_id = api.comm_lines_api_id));
2132 END IF; /* end if l_skip_credit_flag */
2133
2134 debugmsg('Loader : number of loaded trx = ' || TO_CHAR(SQL%ROWCOUNT));
2135
2136 -- Commented this query to fix bug# 1772128
2137 /*
2138 UPDATE cn_comm_lines_api api
2139 SET load_Status = 'LOADED'
2140 WHERE
2141 api.load_status = 'UNLOADED' AND
2142 Trunc(api.processed_date) >= Trunc(p_start_date) AND
2143 Trunc(api.processed_date) <= Trunc(p_end_date) AND
2144 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
2145 api.trx_type <> 'FORECAST' AND
2146 api.salesrep_id = batch.salesrep_id AND
2147 Trunc(api.processed_date) >= Trunc(batch.start_date) AND
2148 Trunc(api.processed_date) <= Trunc(batch.end_date); */
2149 IF (l_skip_credit_flag = 'Y') THEN
2150 UPDATE cn_comm_lines_api api
2151 SET load_status = 'LOADED'
2152 WHERE api.load_status = 'UNLOADED'
2153 AND api.processed_date >= TRUNC(p_start_date)
2154 AND api.processed_date <(TRUNC(p_end_date) + 1)
2155 AND api.trx_type <> 'FORECAST'
2156 AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
2157 AND api.salesrep_id = batch.salesrep_id
2158 AND api.processed_date >= TRUNC(batch.start_date)
2159 AND api.processed_date <(TRUNC(batch.end_date) + 1);
2160 ELSE
2161 UPDATE cn_comm_lines_api api
2162 SET load_status = 'LOADED'
2163 WHERE api.load_status = 'UNLOADED'
2164 AND api.processed_date >= TRUNC(p_start_date)
2165 AND api.processed_date <(TRUNC(p_end_date) + 1)
2166 AND api.trx_type <> 'FORECAST'
2167 AND (adjust_status <> 'SCA_PENDING' )-- OR adjust_status IS NULL)
2168 AND api.salesrep_id = batch.salesrep_id
2169 AND api.processed_date >= TRUNC(batch.start_date)
2170 AND api.processed_date <(TRUNC(batch.end_date) + 1)
2171 AND (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y');
2172 END IF;
2173 END LOOP;
2174
2175 -- Handle reversal transaction add on 10/15/99
2176 DECLARE
2177 CURSOR l_headers IS
2178 SELECT cch.commission_header_id
2179 , cch.reversal_flag
2180 , cch.reversal_header_id
2181 FROM cn_commission_headers cch
2182 , (SELECT DISTINCT salesrep_id
2183 FROM cn_process_batches
2184 WHERE physical_batch_id = p_physical_batch_id AND status_code = 'IN_USE') pb
2185 WHERE cch.direct_salesrep_id = pb.salesrep_id
2186 AND cch.commission_header_id > l_init_commission_header_id;
2187 BEGIN
2188 FOR l_header IN l_headers LOOP
2189 -- Only pass in the "reversal" trx into handle_reversal_trx
2190 -- Do not pass in the original trx eventhough its reversal_flag = 'Y'
2191 IF (l_header.reversal_flag = 'Y')
2192 AND (l_header.commission_header_id <> l_header.reversal_header_id) THEN
2193 cn_formula_common_pkg.handle_reversal_trx(l_header.commission_header_id);
2194 END IF;
2195 END LOOP;
2196 END;
2197
2198 IF (p_cls_rol_flag = 'Y') THEN
2199 debugmsg('Loader : Load_Worker : Classify : p_physical_batch_id = ' || p_physical_batch_id);
2200 debugmsg('Loader : Load_Worker : Classify : calling cn_calc_classify_pvt.classify_batch');
2201 cn_calc_classify_pvt.classify_batch(
2202 p_api_version => 1.0
2203 , p_init_msg_list => fnd_api.g_true
2204 , p_commit => fnd_api.g_true
2205 , x_return_status => l_return_status
2206 , x_msg_count => l_msg_count
2207 , x_msg_data => l_msg_data
2208 , p_physical_batch_id => p_physical_batch_id
2209 , p_mode => 'NEW'
2210 );
2211 debugmsg('Loader : Load_Worker : Classify : return status is ' || l_return_status);
2212 debugmsg('Loader : Load_Worker : Classify : l_msg_count is ' || l_msg_count);
2213 debugmsg('Loader : Load_Worker : Classify : l_msg_data is ' || l_msg_data);
2214
2215 FOR l_counter IN 1 .. l_msg_count LOOP
2216 debugmsg(fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false));
2217 END LOOP;
2218
2219 debugmsg('Loader : Load_Worker : Rollup : p_physical_batch_id = ' || p_physical_batch_id);
2220 debugmsg('Loader : Load_Worker : Rollup : calling cn_calc_classify_pvt.classify_batch');
2221 cn_calc_rollup_pvt.rollup_batch(
2222 p_api_version => 1.0
2223 , p_init_msg_list => fnd_api.g_true
2224 , p_commit => fnd_api.g_true
2225 , x_return_status => l_return_status
2226 , x_msg_count => l_msg_count
2227 , x_msg_data => l_msg_data
2228 , p_physical_batch_id => p_physical_batch_id
2229 , p_mode => 'NEW'
2230 );
2231 debugmsg('Loader : Load_Worker : Rollup : return status is ' || l_return_status);
2232 debugmsg('Loader : Load_Worker : Rollup : l_msg_count is ' || l_msg_count);
2233 debugmsg('Loader : Load_Worker : Rollup : l_msg_data is ' || l_msg_data);
2234
2235 FOR l_counter IN 1 .. l_msg_count LOOP
2236 debugmsg(fnd_msg_pub.get(p_msg_index => l_counter, p_encoded => fnd_api.g_false));
2237 END LOOP;
2238 ELSE
2239 debugmsg
2240 ('Loader : Load_Worker : classification/rollup flag is NO. Skip Classification and Rollup.');
2241 END IF;
2242 END load_worker;
2243 END cn_transaction_load_pkg;