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