[Home] [Help]
PACKAGE BODY: APPS.CN_TRANSACTION_LOAD_PUB
Source
1 PACKAGE BODY cn_transaction_load_pub AS
2 -- $Header: cnploadb.pls 120.10.12020000.2 2012/07/20 07:09:54 nbombili ship $
3 --+
4 --+ Global Variable
5 --+
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_TRANSACTION_LOAD_PUB';
7 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cnploadb.pls';
8 G_LAST_UPDATE_DATE DATE := sysdate;
9 G_LAST_UPDATED_BY NUMBER := FND_GLOBAL.USER_ID;
10 G_CREATION_DATE DATE := sysdate;
11 G_CREATED_BY NUMBER := FND_GLOBAL.USER_ID;
12 G_LAST_UPDATE_LOGIN NUMBER := FND_GLOBAL.LOGIN_ID;
13 G_ROWID VARCHAR2(30);
14 G_PROGRAM_TYPE VARCHAR2(30);
15
16 g_logical_process VARCHAR2(30) := 'LOAD';
17 g_physical_process VARCHAR2(30) := 'LOAD';
18 no_valid_transactions EXCEPTION;
19
20
21 -- Local Procedure for showing debug msg
22
23 PROCEDURE debugmsg(msg VARCHAR2) IS
24 BEGIN
25 cn_message_pkg.debug(substr(msg,1,254));
26 END debugmsg;
27
28
29 -- Procedure Name
30 -- get_physical_batch_id
31 -- Purpose : get the unique physical batch id
32
33 FUNCTION get_physical_batch_id RETURN NUMBER IS
34 x_physical_batch_id NUMBER;
35 BEGIN
36 -- sequence s3 is for physical batch id
37 SELECT cn_process_batches_s3.nextval
38 INTO x_physical_batch_id
39 FROM sys.dual;
40
41 RETURN x_physical_batch_id;
42 EXCEPTION
43 WHEN no_data_found THEN raise NO_DATA_FOUND;
44 END get_physical_batch_id;
45
46
47 PROCEDURE update_error (x_physical_batch_id NUMBER) IS
48 l_user_id NUMBER(15) := fnd_global.user_id;
49 l_resp_id NUMBER(15) := fnd_global.resp_id;
50 l_login_id NUMBER(15) := fnd_global.login_id;
51 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
52 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
53 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
54
55 BEGIN
56 -- Giving the batch an 'ERROR' status prevents subsequent
57 -- physical processes picking it up.
58 UPDATE cn_process_batches
59 SET status_code = 'ERROR'
60 ,last_update_date = sysdate
61 ,last_update_login = l_login_id
62 ,last_updated_by = l_user_id
63 ,request_id = l_conc_request_id
64 ,program_application_id = l_prog_appl_id
65 ,program_id = l_conc_prog_id
66 ,program_update_date = sysdate
67 WHERE physical_batch_id = x_physical_batch_id;
68 END update_error;
69
70 -- Procedure: load_worker
71 PROCEDURE load_worker
72 (
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2,
76 p_physical_batch_id IN NUMBER,
77 p_salesrep_id IN NUMBER,
78 p_start_date IN DATE,
79 p_end_date IN DATE,
80 p_cls_rol_flag IN VARCHAR2,
81 p_loading_status IN VARCHAR2,
82 p_org_id IN NUMBER,
83 x_loading_status OUT NOCOPY VARCHAR2
84 ) IS
85
86 l_api_name CONSTANT VARCHAR2(30) := 'load_worker';
87
88 CURSOR batches IS
89 SELECT
90 salesrep_id,
91 period_id,
92 start_date,
93 end_date,
94 sales_lines_total trx_count
95 FROM
96 cn_process_batches
97 WHERE
98 physical_batch_id = p_physical_batch_id AND
99 status_code = 'IN_USE';
100
101
102 Counter NUMBER;
103
104 l_counter NUMBER;
105 l_init_commission_header_id NUMBER;
106
107 l_skip_credit_flag VARCHAR2(1);
108
109 BEGIN
110
111 -- Initialize API return status to success
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113 x_loading_status := p_loading_status;
114
115 -- Start of API body
116
117 Counter := 0;
118
119 /* Get the value of the profile "OIC: Skip Credit Allocation" */
120 l_skip_credit_flag := 'Y';
121 IF (Fnd_Profile.DEFINED('CN_SKIP_CREDIT_ALLOCATION')) THEN
122 l_skip_credit_flag := NVL(Fnd_Profile.VALUE('CN_SKIP_CREDIT_ALLOCATION'), 'Y');
123 END IF;
124
125 -- this is used to make it more restrict for handling reversal trx later on
126 SELECT cn_commission_headers_s.NEXTVAL
127 INTO l_init_commission_header_id FROM dual;
128
129 FOR batch IN batches LOOP
130
131 debugmsg('Loader : Load_Worker : Load ' ||
132 to_char(batch.trx_count) ||
133 ' lines for physical batch = ' ||
134 p_physical_batch_id ||
135 ' salesrep id = ' || batch.salesrep_id ||
136 ' period_id = ' || batch.period_id ||
137 ' p_salesrep_id = ' || p_salesrep_id ||
138 ' p_start_date = ' || p_start_date ||
139 ' p_end_date = ' || p_end_date ||
140 ' p_cls_rol_flag = ' || p_cls_rol_flag);
141
142 Counter := Counter + batch.trx_count;
143
144 IF (l_skip_credit_flag = 'Y') THEN
145 INSERT INTO cn_commission_headers_all
146 (commission_header_id,
147 direct_salesrep_id,
148 processed_date,
149 processed_period_id,
150 rollup_date,
151 transaction_amount,
152 quantity,
153 discount_percentage,
154 margin_percentage,
155 orig_currency_code,
156 TRANSACTION_AMOUNT_ORIG,
157 trx_type,
158 status,
159 pre_processed_code,
160 COMM_LINES_API_ID,
161 SOURCE_DOC_TYPE,
162 SOURCE_TRX_NUMBER,
163 quota_id,
164 srp_plan_assign_id,
165 revenue_class_id,
166 role_id,
167 comp_group_id,
168 commission_amount,
169 reversal_flag,
170 reversal_header_id,
171 reason_code,
172 attribute_category,
173 attribute1,
174 attribute2,
175 attribute3,
176 attribute4,
177 attribute5,
178 attribute6,
179 attribute7,
180 attribute8,
181 attribute9,
182 attribute10,
183 attribute11,
184 attribute12,
185 attribute13,
186 attribute14,
187 attribute15,
188 attribute16,
189 attribute17,
190 attribute18,
191 attribute19,
192 attribute20,
193 attribute21,
194 attribute22,
195 attribute23,
196 attribute24,
197 attribute25,
198 attribute26,
199 attribute27,
200 attribute28,
201 attribute29,
202 attribute30,
203 attribute31,
204 attribute32,
205 attribute33,
206 attribute34,
207 attribute35,
208 attribute36,
209 attribute37,
210 attribute38,
211 attribute39,
212 attribute40,
213 attribute41,
214 attribute42,
215 attribute43,
216 attribute44,
217 attribute45,
218 attribute46,
219 attribute47,
220 attribute48,
221 attribute49,
222 attribute50,
223 attribute51,
224 attribute52,
225 attribute53,
226 attribute54,
227 attribute55,
228 attribute56,
229 attribute57,
230 attribute58,
231 attribute59,
232 attribute60,
233 attribute61,
234 attribute62,
235 attribute63,
236 attribute64,
237 attribute65,
238 attribute66,
239 attribute67,
240 attribute68,
241 attribute69,
242 attribute70,
243 attribute71,
244 attribute72,
245 attribute73,
246 attribute74,
247 attribute75,
248 attribute76,
249 attribute77,
250 attribute78,
251 attribute79,
252 attribute80,
253 attribute81,
254 attribute82,
255 attribute83,
256 attribute84,
257 attribute85,
258 attribute86,
259 attribute87,
260 attribute88,
261 attribute89,
262 attribute90,
263 attribute91,
264 attribute92,
265 attribute93,
266 attribute94,
267 attribute95,
268 attribute96,
269 attribute97,
270 attribute98,
271 attribute99,
272 attribute100,
273 last_update_date,
274 last_updated_by,
275 last_update_login,
276 creation_date,
277 created_by,
278 EXCHANGE_RATE,
279 FORECAST_ID,
280 UPSIDE_QUANTITY,
281 UPSIDE_AMOUNT,
282 UOM_CODE,
283 SOURCE_TRX_ID,
284 SOURCE_TRX_LINE_ID,
285 SOURCE_TRX_SALES_LINE_ID,
286 NEGATED_FLAG,
287 CUSTOMER_ID,
288 INVENTORY_ITEM_ID,
289 ORDER_NUMBER,
290 BOOKED_DATE,
291 INVOICE_NUMBER,
292 INVOICE_DATE,
293 BILL_TO_ADDRESS_ID,
294 SHIP_TO_ADDRESS_ID,
295 BILL_TO_CONTACT_ID,
296 SHIP_TO_CONTACT_ID,
297 ADJ_COMM_LINES_API_ID,
298 ADJUST_DATE,
299 ADJUSTED_BY,
300 REVENUE_TYPE,
301 ADJUST_ROLLUP_FLAG,
302 ADJUST_COMMENTS,
303 ADJUST_STATUS,
304 line_number,
305 type,
306 sales_channel,
307 split_pct,
308 split_status,
309 org_id)
310 (SELECT
311 cn_commission_headers_s.nextval,
312 batch.salesrep_id,
313 Trunc(api.processed_date),
314 batch.period_id,
315 Trunc(api.rollup_date),
316 api.acctd_transaction_amount,
317 api.quantity,
318 api.discount_percentage,
319 api.margin_percentage,
320 api.transaction_currency_code,
321 api.transaction_amount,
322 api.trx_type,
323 'COL',
324 Nvl(api.pre_processed_code,'CRPC'),
325 api.comm_lines_api_id,
326 api.source_doc_type,
327 api.source_trx_number,
328 api.quota_id,
329 api.srp_plan_assign_id,
330 api.revenue_class_id,
331 api.role_id,
332 api.comp_group_id,
333 api.commission_amount,
334 api.reversal_flag,
335 api.reversal_header_id,
336 api.reason_code,
337 api.attribute_category,
338 api.attribute1,
339 api.attribute2,
340 api.attribute3,
341 api.attribute4,
342 api.attribute5,
343 api.attribute6,
344 api.attribute7,
345 api.attribute8,
346 api.attribute9,
347 api.attribute10,
348 api.attribute11,
349 api.attribute12,
350 api.attribute13,
351 api.attribute14,
352 api.attribute15,
353 api.attribute16,
354 api.attribute17,
355 api.attribute18,
356 api.attribute19,
357 api.attribute20,
358 api.attribute21,
359 api.attribute22,
360 api.attribute23,
361 api.attribute24,
362 api.attribute25,
363 api.attribute26,
364 api.attribute27,
365 api.attribute28,
366 api.attribute29,
367 api.attribute30,
368 api.attribute31,
369 api.attribute32,
370 api.attribute33,
371 api.attribute34,
372 api.attribute35,
373 api.attribute36,
374 api.attribute37,
375 api.attribute38,
376 api.attribute39,
377 api.attribute40,
378 api.attribute41,
379 api.attribute42,
380 api.attribute43,
381 api.attribute44,
382 api.attribute45,
383 api.attribute46,
384 api.attribute47,
385 api.attribute48,
386 api.attribute49,
387 api.attribute50,
388 api.attribute51,
389 api.attribute52,
390 api.attribute53,
391 api.attribute54,
392 api.attribute55,
393 api.attribute56,
394 api.attribute57,
395 api.attribute58,
396 api.attribute59,
397 api.attribute60,
398 api.attribute61,
399 api.attribute62,
400 api.attribute63,
401 api.attribute64,
402 api.attribute65,
403 api.attribute66,
404 api.attribute67,
405 api.attribute68,
406 api.attribute69,
407 api.attribute70,
408 api.attribute71,
409 api.attribute72,
410 api.attribute73,
411 api.attribute74,
412 api.attribute75,
413 api.attribute76,
414 api.attribute77,
415 api.attribute78,
416 api.attribute79,
417 api.attribute80,
418 api.attribute81,
419 api.attribute82,
420 api.attribute83,
421 api.attribute84,
422 api.attribute85,
423 api.attribute86,
424 api.attribute87,
425 api.attribute88,
426 api.attribute89,
427 api.attribute90,
428 api.attribute91,
429 api.attribute92,
430 api.attribute93,
431 api.attribute94,
432 api.attribute95,
433 api.attribute96,
434 api.attribute97,
435 api.attribute98,
436 api.attribute99,
437 api.attribute100,
438 sysdate,
439 api.last_updated_by,
440 api.last_update_login,
441 sysdate,
442 api.created_by,
443 api.exchange_rate,
444 api.FORECAST_ID,
445 api.UPSIDE_QUANTITY,
446 api.UPSIDE_AMOUNT,
447 api.UOM_CODE,
448 api.SOURCE_TRX_ID,
449 api.SOURCE_TRX_LINE_ID,
450 api.SOURCE_TRX_SALES_LINE_ID,
451 api.NEGATED_FLAG,
452 api.CUSTOMER_ID,
453 api.INVENTORY_ITEM_ID,
454 api.ORDER_NUMBER,
455 api.BOOKED_DATE,
456 api.INVOICE_NUMBER,
457 api.INVOICE_DATE,
458 api.BILL_TO_ADDRESS_ID,
459 api.SHIP_TO_ADDRESS_ID,
460 api.BILL_TO_CONTACT_ID,
461 api.SHIP_TO_CONTACT_ID,
462 api.ADJ_COMM_LINES_API_ID,
463 api.ADJUST_DATE,
464 api.ADJUSTED_BY,
465 api.REVENUE_TYPE,
466 api.ADJUST_ROLLUP_FLAG,
467 api.ADJUST_COMMENTS,
468 NVL(api.ADJUST_STATUS,'NEW'),
469 api.line_number,
470 api.type,
471 api.sales_channel,
472 api.split_pct,
473 api.split_status,
474 api.org_id
475 FROM
476 cn_comm_lines_api_all api
477 WHERE
478 api.load_status = 'UNLOADED' AND
479 api.processed_date >= TRUNC(p_start_date) AND
480 api.processed_date < (TRUNC(p_end_date) + 1) AND
481 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
482 api.trx_type <> 'FORECAST' AND
483 (api.adjust_status <> 'SCA_PENDING') AND --OR api.adjust_status IS NULL) AND
484 api.salesrep_id = batch.salesrep_id AND
485 api.processed_date >= Trunc(batch.start_date) AND
486 api.processed_date < (Trunc(batch.end_date) + 1) AND
487 api.org_id = p_org_id);
488 ELSE
489 INSERT INTO cn_commission_headers_all
490 (commission_header_id,
491 direct_salesrep_id,
492 processed_date,
493 processed_period_id,
494 rollup_date,
495 transaction_amount,
496 quantity,
497 discount_percentage,
498 margin_percentage,
499 orig_currency_code,
500 TRANSACTION_AMOUNT_ORIG,
501 trx_type,
502 status,
503 pre_processed_code,
504 COMM_LINES_API_ID,
505 SOURCE_DOC_TYPE,
506 SOURCE_TRX_NUMBER,
507 quota_id,
508 srp_plan_assign_id,
509 revenue_class_id,
510 role_id,
511 comp_group_id,
512 commission_amount,
513 reversal_flag,
514 reversal_header_id,
515 reason_code,
516 attribute_category,
517 attribute1,
518 attribute2,
519 attribute3,
520 attribute4,
521 attribute5,
522 attribute6,
523 attribute7,
524 attribute8,
525 attribute9,
526 attribute10,
527 attribute11,
528 attribute12,
529 attribute13,
530 attribute14,
531 attribute15,
532 attribute16,
533 attribute17,
534 attribute18,
535 attribute19,
536 attribute20,
537 attribute21,
538 attribute22,
539 attribute23,
540 attribute24,
541 attribute25,
542 attribute26,
543 attribute27,
544 attribute28,
545 attribute29,
546 attribute30,
547 attribute31,
548 attribute32,
549 attribute33,
550 attribute34,
551 attribute35,
552 attribute36,
553 attribute37,
554 attribute38,
555 attribute39,
556 attribute40,
557 attribute41,
558 attribute42,
559 attribute43,
560 attribute44,
561 attribute45,
562 attribute46,
563 attribute47,
564 attribute48,
565 attribute49,
566 attribute50,
567 attribute51,
568 attribute52,
569 attribute53,
570 attribute54,
571 attribute55,
572 attribute56,
573 attribute57,
574 attribute58,
575 attribute59,
576 attribute60,
577 attribute61,
578 attribute62,
579 attribute63,
580 attribute64,
581 attribute65,
582 attribute66,
583 attribute67,
584 attribute68,
585 attribute69,
586 attribute70,
587 attribute71,
588 attribute72,
589 attribute73,
590 attribute74,
591 attribute75,
592 attribute76,
593 attribute77,
594 attribute78,
595 attribute79,
596 attribute80,
597 attribute81,
598 attribute82,
599 attribute83,
600 attribute84,
601 attribute85,
602 attribute86,
603 attribute87,
604 attribute88,
605 attribute89,
606 attribute90,
607 attribute91,
608 attribute92,
609 attribute93,
610 attribute94,
611 attribute95,
612 attribute96,
613 attribute97,
614 attribute98,
615 attribute99,
616 attribute100,
617 last_update_date,
618 last_updated_by,
619 last_update_login,
620 creation_date,
621 created_by,
622 EXCHANGE_RATE,
623 FORECAST_ID,
624 UPSIDE_QUANTITY,
625 UPSIDE_AMOUNT,
626 UOM_CODE,
627 SOURCE_TRX_ID,
628 SOURCE_TRX_LINE_ID,
629 SOURCE_TRX_SALES_LINE_ID,
630 NEGATED_FLAG,
631 CUSTOMER_ID,
632 INVENTORY_ITEM_ID,
633 ORDER_NUMBER,
634 BOOKED_DATE,
635 INVOICE_NUMBER,
636 INVOICE_DATE,
637 BILL_TO_ADDRESS_ID,
638 SHIP_TO_ADDRESS_ID,
639 BILL_TO_CONTACT_ID,
640 SHIP_TO_CONTACT_ID,
641 ADJ_COMM_LINES_API_ID,
642 ADJUST_DATE,
643 ADJUSTED_BY,
644 REVENUE_TYPE,
645 ADJUST_ROLLUP_FLAG,
646 ADJUST_COMMENTS,
647 ADJUST_STATUS,
648 line_number,
649 type,
650 sales_channel,
651 split_pct,
652 split_status,
653 org_id)
654 (SELECT
655 cn_commission_headers_s.nextval,
656 batch.salesrep_id,
657 Trunc(api.processed_date),
658 batch.period_id,
659 Trunc(api.rollup_date),
660 api.acctd_transaction_amount,
661 api.quantity,
662 api.discount_percentage,
663 api.margin_percentage,
664 api.transaction_currency_code,
665 api.transaction_amount,
666 api.trx_type,
667 'COL',
668 Nvl(api.pre_processed_code,'CRPC'),
669 api.comm_lines_api_id,
670 api.source_doc_type,
671 api.source_trx_number,
672 api.quota_id,
673 api.srp_plan_assign_id,
674 api.revenue_class_id,
675 api.role_id,
676 api.comp_group_id,
677 api.commission_amount,
678 api.reversal_flag,
679 api.reversal_header_id,
680 api.reason_code,
681 api.attribute_category,
682 api.attribute1,
683 api.attribute2,
684 api.attribute3,
685 api.attribute4,
686 api.attribute5,
687 api.attribute6,
688 api.attribute7,
689 api.attribute8,
690 api.attribute9,
691 api.attribute10,
692 api.attribute11,
693 api.attribute12,
694 api.attribute13,
695 api.attribute14,
696 api.attribute15,
697 api.attribute16,
698 api.attribute17,
699 api.attribute18,
700 api.attribute19,
701 api.attribute20,
702 api.attribute21,
703 api.attribute22,
704 api.attribute23,
705 api.attribute24,
706 api.attribute25,
707 api.attribute26,
708 api.attribute27,
709 api.attribute28,
710 api.attribute29,
711 api.attribute30,
712 api.attribute31,
713 api.attribute32,
714 api.attribute33,
715 api.attribute34,
716 api.attribute35,
717 api.attribute36,
718 api.attribute37,
719 api.attribute38,
720 api.attribute39,
721 api.attribute40,
722 api.attribute41,
723 api.attribute42,
724 api.attribute43,
725 api.attribute44,
726 api.attribute45,
727 api.attribute46,
728 api.attribute47,
729 api.attribute48,
730 api.attribute49,
731 api.attribute50,
732 api.attribute51,
733 api.attribute52,
734 api.attribute53,
735 api.attribute54,
736 api.attribute55,
737 api.attribute56,
738 api.attribute57,
739 api.attribute58,
740 api.attribute59,
741 api.attribute60,
742 api.attribute61,
743 api.attribute62,
744 api.attribute63,
745 api.attribute64,
746 api.attribute65,
747 api.attribute66,
748 api.attribute67,
749 api.attribute68,
750 api.attribute69,
751 api.attribute70,
752 api.attribute71,
753 api.attribute72,
754 api.attribute73,
755 api.attribute74,
756 api.attribute75,
757 api.attribute76,
758 api.attribute77,
759 api.attribute78,
760 api.attribute79,
761 api.attribute80,
762 api.attribute81,
763 api.attribute82,
764 api.attribute83,
765 api.attribute84,
766 api.attribute85,
767 api.attribute86,
768 api.attribute87,
769 api.attribute88,
770 api.attribute89,
771 api.attribute90,
772 api.attribute91,
773 api.attribute92,
774 api.attribute93,
775 api.attribute94,
776 api.attribute95,
777 api.attribute96,
778 api.attribute97,
779 api.attribute98,
780 api.attribute99,
781 api.attribute100,
782 sysdate,
783 api.last_updated_by,
784 api.last_update_login,
785 sysdate,
786 api.created_by,
787 api.exchange_rate,
788 api.FORECAST_ID,
789 api.UPSIDE_QUANTITY,
790 api.UPSIDE_AMOUNT,
791 api.UOM_CODE,
792 api.SOURCE_TRX_ID,
793 api.SOURCE_TRX_LINE_ID,
794 api.SOURCE_TRX_SALES_LINE_ID,
795 api.NEGATED_FLAG,
796 api.CUSTOMER_ID,
797 api.INVENTORY_ITEM_ID,
798 api.ORDER_NUMBER,
799 api.BOOKED_DATE,
800 api.INVOICE_NUMBER,
801 api.INVOICE_DATE,
802 api.BILL_TO_ADDRESS_ID,
803 api.SHIP_TO_ADDRESS_ID,
804 api.BILL_TO_CONTACT_ID,
805 api.SHIP_TO_CONTACT_ID,
806 api.ADJ_COMM_LINES_API_ID,
807 api.ADJUST_DATE,
808 api.ADJUSTED_BY,
809 api.REVENUE_TYPE,
810 api.ADJUST_ROLLUP_FLAG,
811 api.ADJUST_COMMENTS,
812 NVL(api.ADJUST_STATUS,'NEW'),
813 api.line_number,
814 api.type,
815 api.sales_channel,
816 api.split_pct,
817 api.split_status,
818 api.org_id
819 FROM
820 cn_comm_lines_api_all api
821 WHERE
822 api.load_status = 'UNLOADED' AND
823 api.processed_date >= TRUNC(p_start_date) AND
824 api.processed_date < (TRUNC(p_end_date) + 1) AND
825 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
826 api.trx_type <> 'FORECAST' AND
827 (api.adjust_status <> 'SCA_PENDING') AND -- OR api.adjust_status IS NULL) AND
828 api.salesrep_id = batch.salesrep_id AND
829 api.processed_date >= Trunc(batch.start_date) AND
830 api.processed_date < (Trunc(batch.end_date) + 1) AND
831 (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') AND
832 api.org_id = p_org_id);
833 END IF;
834
835 debugmsg('Loader : number of loaded trx = ' ||to_char(SQL%ROWCOUNT));
836
837 IF (l_skip_credit_flag = 'Y') THEN
838 UPDATE cn_comm_lines_api_all api
839 SET load_Status = 'LOADED'
840 WHERE
841 api.load_status = 'UNLOADED' AND
842 api.processed_date >= TRUNC(p_start_date) AND
843 api.processed_date < (TRUNC(p_end_date) + 1) AND
844 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
845 api.trx_type <> 'FORECAST' AND
846 (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
847 api.salesrep_id = batch.salesrep_id AND
848 api.processed_date >= Trunc(batch.start_date) AND
849 api.processed_date < (Trunc(batch.end_date) + 1) AND
850 api.org_id = p_org_id;
851 ELSE
852 UPDATE cn_comm_lines_api_all api
853 SET load_Status = 'LOADED'
854 WHERE
855 api.load_status = 'UNLOADED' AND
856 api.processed_date >= TRUNC(p_start_date) AND
857 api.processed_date < (TRUNC(p_end_date) + 1) AND
858 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
859 api.trx_type <> 'FORECAST' AND
860 (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
861 api.salesrep_id = batch.salesrep_id AND
862 api.processed_date >= Trunc(batch.start_date) AND
863 api.processed_date < (Trunc(batch.end_date) + 1) AND
864 (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y') AND
865 api.org_id = p_org_id;
866 END IF;
867
868 END LOOP;
869
870 -- Handle reversal transaction add on 10/15/99
871 DECLARE
872 CURSOR l_headers IS
873 SELECT cch.commission_header_id, cch.reversal_flag, cch.reversal_header_id
874 FROM cn_commission_headers cch,
875 (SELECT DISTINCT salesrep_id
876 FROM cn_process_batches
877 WHERE physical_batch_id = p_physical_batch_id
878 AND status_code = 'IN_USE') pb
879 WHERE cch.direct_salesrep_id = pb.salesrep_id
880 AND cch.commission_header_id > l_init_commission_header_id;
881 BEGIN
882 FOR l_header IN l_headers LOOP
883 -- Only pass in the "reversal" trx into handle_reversal_trx
884 -- Do not pass in the original trx eventhough its reversal_flag = 'Y'
885 IF (l_header.reversal_flag = 'Y') AND
886 (l_header.commission_header_id <> l_header.reversal_header_id) THEN
887 cn_formula_common_pkg.handle_reversal_trx(l_header.commission_header_id);
888 END IF;
889 END LOOP;
890 END;
891
892 IF (p_cls_rol_flag = 'Y') THEN
893
894 debugmsg('Loader : Load_Worker : Classify : p_physical_batch_id = '
895 || p_physical_batch_id);
896 debugmsg('Loader : Load_Worker : Classify : calling cn_calc_classify_pvt.classify_batch');
897
898
899 cn_calc_classify_pvt.classify_batch
900 ( p_api_version => 1.0,
901 p_init_msg_list => fnd_api.g_true,
902 p_commit => fnd_api.g_true ,
903 x_return_status => x_return_status,
904 x_msg_count => x_msg_count,
905 x_msg_data => x_msg_data,
906 p_physical_batch_id => p_physical_batch_id,
907 p_mode => 'NEW');
908
909 debugmsg('Loader : Load_Workder : Classify : return status is '
910 || x_return_status );
911 debugmsg('Loader : Load_Workder : Classify : l_msg_count is '
912 || x_msg_count );
913 debugmsg('Loader : Load_Workder : Classify : l_msg_data is '
914 || x_msg_data );
915
916 FOR l_counter IN 1..x_msg_count LOOP
917 debugmsg( FND_MSG_PUB.get(p_msg_index => l_counter,
918 p_encoded => FND_API.G_FALSE));
919 END LOOP;
920
921 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
922 debugmsg('Loader : load_worker : Classification Failed.');
923 x_loading_status := 'CN_FAIL_CLASSIFICATION';
924 RAISE FND_API.G_EXC_ERROR;
925 END IF;
926
927 debugmsg('Loader : Load_Worker : Rollup : p_physical_batch_id = '
928 || p_physical_batch_id);
929 debugmsg('Loader : Load_Worker : Rollup : calling cn_calc_classify_pvt.classify_batch');
930
931 cn_calc_rollup_pvt.rollup_batch
932 ( p_api_version => 1.0,
933 p_init_msg_list => fnd_api.g_true,
934 p_commit => fnd_api.g_true ,
935 x_return_status => x_return_status,
936 x_msg_count => x_msg_count,
937 x_msg_data => x_msg_data,
938 p_physical_batch_id => p_physical_batch_id,
939 p_mode => 'NEW');
940
941 debugmsg('Loader : Load_Workder : Rollup : return status is '
942 || x_return_status );
943 debugmsg('Loader : Load_Workder : Rollup : l_msg_count is '
944 || x_msg_count );
945 debugmsg('Loader : Load_Workder : Rollup : l_msg_data is '
946 || x_msg_data );
947
948
949 FOR l_counter IN 1..x_msg_count LOOP
950 debugmsg( FND_MSG_PUB.get(p_msg_index => l_counter,
951 p_encoded => FND_API.G_FALSE));
952 END LOOP;
953
954 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
955 debugmsg('Loader : load_worker : Rollup Failed.');
956 x_loading_status := 'CN_FAIL_ROLLUP';
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959
960 ELSE
961
962 debugmsg('Loader : Load_Worker : classification/rollup flag is NO. Skip Classification and Rollup.');
963
964 END IF;
965 -- End of API body.
966
967 -- Standard call to get message count and if count is 1, get message info.
968
969 FND_MSG_PUB.Count_And_Get
970 (
971 p_count => x_msg_count ,
972 p_data => x_msg_data ,
973 p_encoded => FND_API.G_FALSE
974 );
975
976 EXCEPTION
977 WHEN FND_API.G_EXC_ERROR THEN
978 x_return_status := FND_API.G_RET_STS_ERROR ;
979 debugmsg('Loader : load_worker : Exception : Error msg : ' || x_msg_data);
980 FND_MSG_PUB.Count_And_Get
981 (
982 p_count => x_msg_count ,
983 p_data => x_msg_data ,
984 p_encoded => FND_API.G_FALSE
985 );
986 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
988 x_loading_status := 'UNEXPECTED_ERR';
989 debugmsg('Loader : load_worker : Exception : Unexpected Error : Error message : ' || x_msg_data);
990 FND_MSG_PUB.Count_And_Get
991 (
992 p_count => x_msg_count ,
993 p_data => x_msg_data ,
994 p_encoded => FND_API.G_FALSE
995 );
996 WHEN OTHERS THEN
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
998 x_loading_status := 'UNEXPECTED_ERR';
999 debugmsg('Loader : load_worker : Exception Others : Error : Error message : ' || x_msg_data);
1000 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1001 THEN
1002 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1003 END IF;
1004 FND_MSG_PUB.Count_And_Get
1005 (
1006 p_count => x_msg_count ,
1007 p_data => x_msg_data ,
1008 p_encoded => FND_API.G_FALSE
1009 );
1010 END Load_worker;
1011
1012
1013 -- Start of Comments
1014 -- API name : Load
1015 -- Type : Public.
1016 -- Pre-reqs : None.
1017 -- Usage : Used to load transactions from API table (CN_COMM_LINES_API)
1018 -- to HEADER table (CN_COMMISSION_HEADERS)
1019 -- Parameters :
1020 -- IN : p_api_version IN NUMBER Require
1021 -- : p_init_msg_list IN VARCHAR2 Optional
1022 -- Default = FND_API.G_FALSE
1023 -- : p_commit IN VARCHAR2 Optional
1024 -- Default = FND_API.G_FALSE
1025 -- : p_validation_level IN NUMBER Optional
1026 -- Default = FND_API.G_VALID_LEVEL_FULL
1027 -- OUT : x_return_status OUT VARCHAR2(1)
1028 -- : x_msg_count OUT NUMBER
1029 -- : x_msg_data OUT VARCHAR2(2000)
1030 -- : x_loading_status OUT VARCHAR2
1031 -- Version : Current version 1.0
1032 -- Initial version 1.0
1033 -- Notes
1034 -- Description : This procedure loads trx from CN_COMM_LINES_API to
1035 -- CN_COMMISSION_HEADERS, update cn_process_batches,
1036 -- and perform trx classification, and trx rollup.
1037 --
1038 -- Special Notes : This public API will load trx sequentially instead
1039 -- of submitting concurrent process to load trx in parallel
1040 -- as the regular loader does.
1041 -- End of comments
1042
1043
1044 -- Call begin_batch to get process_audit_id
1045 -- Insert into cn_process_batches, populate logical_batch_id
1046 -- Call Assign : populate physical_batch_id
1047 -- Call Conc_dispatch : For each physical_batch_id call load_worker.
1048 -- Note that this public API will load trx sequentially instead of submitting
1049 -- concurrent process to load trx in parallel as the regular loader does
1050 -- Load_worker :
1051 -- 1.load trx into HEADER
1052 -- 2.perform trx classification
1053 -- 3.perform trx rollup
1054
1055
1056 PROCEDURE load
1057 (
1058 p_api_version IN NUMBER,
1059 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1060 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1061 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1062 p_salesrep_id IN NUMBER,
1063 p_start_date IN DATE,
1064 p_end_date IN DATE,
1065 p_cls_rol_flag IN VARCHAR2,
1066 p_org_id IN NUMBER,
1067 x_return_status OUT NOCOPY VARCHAR2,
1068 x_msg_count OUT NOCOPY NUMBER,
1069 x_msg_data OUT NOCOPY VARCHAR2,
1070 x_loading_status OUT NOCOPY VARCHAR2,
1071 x_process_audit_id OUT NOCOPY NUMBER
1072 ) IS
1073
1074
1075 l_api_name CONSTANT VARCHAR2(30) := 'Load';
1076 l_api_version CONSTANT NUMBER := 1.0;
1077
1078 TYPE l_emp_no_tbl_type IS TABLE OF cn_comm_lines_api.employee_number%TYPE;
1079 TYPE l_srp_id_tbl_type IS TABLE OF cn_comm_lines_api.salesrep_id%TYPE;
1080 TYPE l_period_id_tbl_type IS TABLE OF cn_acc_period_statuses_v.period_id%TYPE;
1081 TYPE l_start_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.start_date%TYPE;
1082 TYPE l_end_date_tbl_type IS TABLE OF cn_acc_period_statuses_v.end_date%TYPE;
1083 TYPE l_count_tbl_type IS TABLE OF NUMBER;
1084
1085 l_emp_no_tbl l_emp_no_tbl_type;
1086 l_srp_id_tbl l_srp_id_tbl_type;
1087 l_period_id_tbl l_period_id_tbl_type;
1088 l_start_date_tbl l_start_date_tbl_type;
1089 l_end_date_tbl l_end_date_tbl_type;
1090 l_count_tbl l_count_tbl_type;
1091
1092
1093 l_user_id NUMBER(15) := fnd_global.user_id;
1094 l_resp_id NUMBER(15) := fnd_global.resp_id;
1095 l_login_id NUMBER(15) := fnd_global.login_id;
1096 l_conc_prog_id NUMBER(15) := fnd_global.conc_program_id;
1097 l_conc_request_id NUMBER(15) := fnd_global.conc_request_id;
1098 l_prog_appl_id NUMBER(15) := fnd_global.prog_appl_id;
1099
1100 l_logical_batch_id NUMBER;
1101 l_process_batch_id NUMBER;
1102
1103 --+
1104 --+ Added Counter Variable : Hithanki 03/06/2003 For Bug Fix : 2781346
1105 --+
1106 l_process_rec_cnt NUMBER := 0;
1107
1108 l_skip_credit_flag VARCHAR2(1);
1109 l_logical_batch_count NUMBER;
1110 l_return_status VARCHAR2(1);
1111 l_msg_count NUMBER;
1112 l_msg_data VARCHAR2(2000);
1113 l_loading_status VARCHAR2(200);
1114
1115 -- Declaration for user hooks
1116 l_OAI_array JTF_USR_HKS.oai_data_array_type;
1117 l_bind_data_id NUMBER;
1118
1119 -- get the number of valid transactions to load
1120 CURSOR valid_transactions (p_logical_batch_id NUMBER) IS
1121 SELECT salesrep_id, SUM(sales_lines_total) srp_trx_count
1122 FROM cn_process_batches
1123 WHERE logical_batch_id = p_logical_batch_id
1124 AND status_code = 'IN_USE'
1125 GROUP BY salesrep_id;
1126
1127 valid_rec valid_transactions%ROWTYPE;
1128
1129 -- get the trx count for each srp-period
1130 CURSOR logical_batches(p_salesrep_id NUMBER,
1131 p_start_date DATE,
1132 p_end_date DATE,
1133 p_org_id NUMBER) IS
1134 SELECT
1135 api.employee_number employee_number,
1136 api.salesrep_id salesrep_id,
1137 acc.period_id period_id,
1138 acc.start_date start_date,
1139 acc.end_date end_date,
1140 count(*) trx_count
1141 FROM
1142 cn_comm_lines_api_all api,
1143 cn_acc_period_statuses_v acc
1144 WHERE
1145 api.load_status = 'UNLOADED' AND
1146 api.trx_type <> 'FORECAST' AND
1147 (api.adjust_status <> 'SCA_PENDING' ) AND -- OR api.adjust_status IS NULL) AND
1148 api.processed_date >= TRUNC(p_start_date) AND
1149 api.processed_date < (TRUNC(p_end_date) + 1) AND
1150 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
1151 api.processed_date >= acc.start_date AND
1152 api.processed_date < (acc.end_date + 1) AND
1153 api.org_id = p_org_id AND
1154 acc.org_id = p_org_id --added for the bug 7494675
1155 GROUP BY
1156 api.employee_number,
1157 api.salesrep_id,
1158 acc.period_id,
1159 acc.start_date,
1160 acc.end_date;
1161
1162 CURSOR logical_batches2(p_salesrep_id NUMBER,
1163 p_start_date DATE,
1164 p_end_date DATE,
1165 p_org_id NUMBER) IS
1166 SELECT
1167 api.employee_number employee_number,
1168 api.salesrep_id salesrep_id,
1169 acc.period_id period_id,
1170 acc.start_date start_date,
1171 acc.end_date end_date,
1172 count(*) trx_count
1173 FROM
1174 cn_comm_lines_api_all api,
1175 cn_acc_period_statuses_v acc
1176 WHERE
1177 api.load_status = 'UNLOADED' AND
1178 api.trx_type <> 'FORECAST' AND
1179 (adjust_status <> 'SCA_PENDING' ) AND -- OR adjust_status IS NULL) AND
1180 api.processed_date >= TRUNC(p_start_date) AND
1181 api.processed_date < (TRUNC(p_end_date) + 1) AND
1182 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
1183 api.processed_date >= acc.start_date AND
1184 api.processed_date < (acc.end_date + 1) AND
1185 api.org_id = p_org_id AND
1186 acc.org_id = p_org_id AND --added for the bug 7494675
1187 (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y')
1188 GROUP BY
1189 api.employee_number,
1190 api.salesrep_id,
1191 acc.period_id,
1192 acc.start_date,
1193 acc.end_date;
1194
1195 -- Get individual physical batch id
1196 CURSOR physical_batches(l_logical_batch_id NUMBER) IS
1197 SELECT DISTINCT physical_batch_id
1198 FROM cn_process_batches
1199 WHERE logical_batch_id = l_logical_batch_id
1200 AND status_code = 'IN_USE';
1201
1202 physical_rec physical_batches%ROWTYPE;
1203 l_count NUMBER;
1204
1205 BEGIN
1206
1207 -- Standard Start of API savepoint
1208 SAVEPOINT load_savepoint;
1209
1210 -- Standard call to check for call compatibility.
1211 IF NOT FND_API.compatible_api_call
1212 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1213 THEN
1214 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1215 END IF;
1216
1217 -- Initialize message list if p_init_msg_list is set to TRUE.
1218 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1219 FND_MSG_PUB.initialize;
1220 END IF;
1221
1222 -- Initialize API return status to success
1223 x_return_status := FND_API.G_RET_STS_SUCCESS;
1224 x_loading_status := 'CN_LOADED';
1225
1226
1227 -- User hooks
1228 -- Customer pre-processing section
1229
1230 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1231 'LOAD',
1232 'B',
1233 'C')
1234 THEN
1235
1236 cn_transaction_load_pub_cuhk.load_pre
1237 (p_api_version => p_api_version,
1238 p_init_msg_list => p_init_msg_list,
1239 p_commit => p_commit,
1240 p_validation_level => p_validation_level,
1241 x_return_status => x_return_status,
1242 x_msg_count => x_msg_count,
1243 x_msg_data => x_msg_data,
1244 x_loading_status => x_loading_status
1245 );
1246
1247 IF (x_return_status = FND_API.G_RET_STS_ERROR )
1248 THEN
1249 RAISE FND_API.G_EXC_ERROR;
1250 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1251 THEN
1252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1253 END IF;
1254 END IF;
1255
1256
1257 -- Vertical industry pre-processing section
1258
1259 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1260 'LOAD',
1261 'B',
1262 'V')
1263 THEN
1264
1265 cn_transaction_load_pub_vuhk.load_pre
1266 (p_api_version => p_api_version,
1267 p_init_msg_list => p_init_msg_list,
1268 p_commit => p_commit,
1269 p_validation_level => p_validation_level,
1270 x_return_status => x_return_status,
1271 x_msg_count => x_msg_count,
1272 x_msg_data => x_msg_data,
1273 x_loading_status => x_loading_status
1274 );
1275
1276 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1277 THEN
1278 RAISE FND_API.G_EXC_ERROR;
1279 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1280 THEN
1281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1282 END IF;
1283 END IF;
1284
1285
1286 -- API body starts here
1287
1288 -- Call begin_batch to get process_audit_id for debug log file
1289
1290 debugmsg('Loader : Call begin_batch');
1291
1292 cn_message_pkg.begin_batch
1293 (x_process_type => 'LOADER',
1294 x_parent_proc_audit_id => null,
1295 x_process_audit_id => x_process_audit_id,
1296 x_request_id => null,
1297 p_org_id => p_org_id);
1298
1299 debugmsg('Loader : Start of Loader');
1300 debugmsg('Loader : process_audit_id is ' ||
1301 x_process_audit_id );
1302
1303 /* verify that parameter start date is within an open acc period */
1304 l_count := 0;
1305 select count(*)
1306 into l_count
1307 from cn_period_statuses_all
1308 where period_status = 'O'
1309 and org_id = p_org_id
1310 and (period_set_id, period_type_id) =
1311 (select period_set_id, period_type_id
1312 from cn_repositories_all
1313 where org_id = p_org_id)
1314 and p_start_date between start_date and end_date;
1315 IF (l_count = 0) THEN
1316 debugmsg('Loader : Parameter Start Date is not within an open acc period');
1317
1318 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1319 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
1320 FND_MESSAGE.SET_TOKEN('DATE', p_start_date);
1321 FND_MSG_PUB.Add;
1322 END IF;
1323
1324 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
1325 RAISE FND_API.G_EXC_ERROR ;
1326 END IF;
1327
1328 /* verify that parameter end date is within an open acc period */
1329 l_count := 0;
1330 select count(*)
1331 into l_count
1332 from cn_period_statuses_all
1333 where period_status = 'O'
1334 and org_id = p_org_id
1335 and (period_set_id, period_type_id) =
1336 (select period_set_id, period_type_id
1337 from cn_repositories_all
1338 where org_id = p_org_id)
1339 and p_end_date between start_date and end_date;
1340 IF (l_count = 0) THEN
1341 debugmsg('Loader : Parameter End Date is not within an open acc period');
1342
1343 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1344 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
1345 FND_MESSAGE.SET_TOKEN('DATE', p_end_date);
1346 FND_MSG_PUB.Add;
1347 END IF;
1348
1349 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
1350 RAISE FND_API.G_EXC_ERROR ;
1351 END IF;
1352
1353 /* Get the value of the profile "OIC: Skip Credit Allocation" */
1354 l_skip_credit_flag := 'Y';
1355 IF (Fnd_Profile.DEFINED('CN_SKIP_CREDIT_ALLOCATION')) THEN
1356 l_skip_credit_flag := NVL(Fnd_Profile.VALUE('CN_SKIP_CREDIT_ALLOCATION'), 'Y');
1357 END IF;
1358
1359 -- Check Data in API table
1360 cn_transaction_load_pkg.check_api_data(p_start_date => p_start_date,
1361 p_end_date => p_end_date,
1362 p_org_id => p_org_id);
1363
1364
1365 -- Validate ruleset status if the classification and
1366 -- rollup option is checked.
1367 IF (p_cls_rol_flag = 'Y') THEN
1368 debugmsg('Loader : validate ruleset status : load_start_date = '
1369 || p_start_date);
1370 debugmsg('Loader : validate ruleset status : load_end_date = '
1371 || p_end_date);
1372
1373 IF NOT cn_proc_batches_pkg.validate_ruleset_status
1374 (p_start_date, p_end_date, p_org_id) THEN
1375 debugmsg('Loader : validate ruleset fails.');
1376 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1377 THEN
1378 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_INVALID_RULESET');
1379 FND_MSG_PUB.Add;
1380 END IF;
1381 x_loading_status := 'CN_LOAD_INVALID_RULESET';
1382 RAISE FND_API.G_EXC_ERROR ;
1383 END IF;
1384 END IF;
1385
1386
1387 -- Insert into cn_process_batches for each srp-period
1388 -- Populate logical_batch_id. One logical_batch_id for one load
1389 -- Physical_batch_id is still empty, will be populated in Assign
1390
1391
1392 -- sequence s2 is for logical batch id
1393 SELECT cn_process_batches_s2.NEXTVAL
1394 INTO l_logical_batch_id
1395 FROM sys.dual;
1396
1397
1398 debugmsg('Loader : Logical batch id = '||l_logical_batch_id);
1399
1400 IF (l_skip_credit_flag = 'Y') THEN
1401 OPEN logical_batches(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1402 FETCH logical_batches BULK COLLECT INTO
1403 l_emp_no_tbl,
1404 l_srp_id_tbl,
1405 l_period_id_tbl,
1406 l_start_date_tbl,
1407 l_end_date_tbl,
1408 l_count_tbl;
1409 CLOSE logical_batches;
1410 ELSE
1411 OPEN logical_batches2(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1412 FETCH logical_batches2 BULK COLLECT INTO
1413 l_emp_no_tbl,
1414 l_srp_id_tbl,
1415 l_period_id_tbl,
1416 l_start_date_tbl,
1417 l_end_date_tbl,
1418 l_count_tbl;
1419 CLOSE logical_batches2;
1420 END IF;
1421
1422 l_logical_batch_count := l_srp_id_tbl.COUNT;
1423
1424 IF (l_logical_batch_count <= 0) THEN
1425 debugmsg('Loader : No transactions to load.');
1426
1427 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1428 THEN
1429 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_NO_TRX_TO_LOAD');
1430 FND_MSG_PUB.Add;
1431 END IF;
1432 x_loading_status := 'CN_LOAD_NO_TRX_TO_LOAD';
1433 RAISE FND_API.G_EXC_ERROR ;
1434 ELSE
1435 FOR i IN l_srp_id_tbl.FIRST .. l_srp_id_tbl.LAST LOOP
1436
1437 -- sequence s1 is for process batch id
1438 SELECT cn_process_batches_s1.NEXTVAL
1439 INTO l_process_batch_id
1440 FROM sys.dual;
1441 debugmsg('Loader : insert into cn_process_batches....');
1442 debugmsg('l_process_batch_id = ' || l_process_batch_id);
1443 debugmsg('l_logical_batch_id = '||l_logical_batch_id);
1444 debugmsg('period_id = '||l_period_id_tbl(i) );
1445 debugmsg('start_date = '||l_start_date_tbl(i));
1446 debugmsg('end_date '|| l_end_date_tbl(i));
1447 debugmsg('salesrep_id '|| l_srp_id_tbl(i) );
1448 debugmsg('employee_number '|| l_emp_no_tbl(i));
1449 debugmsg('trx_count '|| l_count_tbl(i));
1450
1451 IF (l_srp_id_tbl(i) IS NOT NULL) THEN
1452
1453 --+
1454 --+ Added Counter Variable : Hithanki 03/06/2003 For Bug Fix : 2781346
1455 --+
1456
1457 l_process_rec_cnt := l_process_rec_cnt + 1;
1458
1459 INSERT INTO cn_process_batches_all
1460 ( process_batch_id
1461 ,logical_batch_id
1462 ,srp_period_id
1463 ,period_id
1464 ,end_period_id
1465 ,start_date
1466 ,end_date
1467 ,salesrep_id
1468 ,sales_lines_total
1469 ,status_code
1470 ,process_batch_type
1471 ,creation_date
1472 ,created_by
1473 ,last_update_date
1474 ,last_updated_by
1475 ,last_update_login
1476 ,request_id
1477 ,program_application_id
1478 ,program_id
1479 ,program_update_date
1480 ,org_id)
1481 VALUES
1482 (
1483 l_process_batch_id
1484 ,l_logical_batch_id
1485 ,l_process_batch_id -- a dummy value for a not null column
1486 ,l_period_id_tbl(i) -- start_period_id
1487 ,l_period_id_tbl(i) -- end_period_id
1488 ,l_start_date_tbl(i) -- start_date
1489 ,l_end_date_tbl(i) -- end_date
1490 ,l_srp_id_tbl(i) -- salesrep_id
1491 ,l_count_tbl(i) -- sales_lines_total
1492 ,'IN_USE' -- status_code
1493 ,'CREATED_BY_LOADER' -- process_batch_type
1494 ,sysdate
1495 ,l_user_id
1496 ,sysdate
1497 ,l_user_id
1498 ,l_login_id
1499 ,l_conc_request_id
1500 ,l_prog_appl_id
1501 ,l_conc_prog_id
1502 ,sysdate
1503 ,p_org_id);
1504
1505 END IF;
1506
1507 END LOOP;
1508 END IF;
1509
1510 OPEN valid_transactions(l_logical_batch_id);
1511 FETCH valid_transactions INTO valid_rec;
1512
1513 IF (valid_transactions%NOTFOUND) THEN
1514
1515 debugmsg('Loader : All transactions to load have invalid salesrep id.');
1516
1517 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1518 THEN
1519 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_SALESREP_ERROR');
1520 FND_MSG_PUB.Add;
1521 END IF;
1522 x_loading_status := 'CN_LOAD_SALESREP_ERROR';
1523
1524 RAISE no_valid_transactions;
1525
1526 END IF;
1527
1528 CLOSE valid_transactions;
1529
1530 --+
1531 --+ Added If Check : Hithanki 03/06/2003 For Bug Fix : 2781346
1532 --+
1533 IF l_process_rec_cnt > 0 THEN
1534
1535 -- Split the logical batch into smaller physical batches
1536 -- populate the physical_batch_id in cn_process_batches
1537
1538
1539
1540 cn_transaction_load_pkg.assign(p_logical_batch_id => l_logical_batch_id, p_org_id => p_org_id);
1541
1542 -- Call load worker for each physical batch
1543 -- for regular loader, we call conc_dispatch to submit concurrent procedure.
1544 -- But for this public API the loader will do the job sequentially.
1545
1546 commit;
1547
1548 cn_transaction_load_pkg.pre_conc_dispatch(p_salesrep_id => p_salesrep_id,
1549 p_start_date => p_start_date,
1550 p_end_date => p_end_date,
1551 p_org_id => p_org_id);
1552
1553 commit;
1554
1555 FOR physical_rec IN physical_batches(l_logical_batch_id)
1556 LOOP
1557
1558 debugmsg('Loader : call load_worker'
1559 ||'physical_rec.physical_batch_id = '
1560 || physical_rec.physical_batch_id );
1561
1562 commit;
1563
1564
1565 load_worker(
1566 x_return_status => l_return_status,
1567 x_msg_count => l_msg_count,
1568 x_msg_data => l_msg_data,
1569 p_physical_batch_id => physical_rec.physical_batch_id,
1570 p_salesrep_id => p_salesrep_id,
1571 p_start_date => p_start_date,
1572 p_end_date => p_end_date,
1573 p_cls_rol_flag => p_cls_rol_flag,
1574 p_loading_status => x_loading_status,
1575 p_org_id => p_org_id,
1576 x_loading_status => l_loading_status
1577 );
1578
1579 commit;
1580
1581 END LOOP;
1582 END IF;
1583
1584 commit;
1585
1586 cn_transaction_load_pkg.post_conc_dispatch
1587 (p_salesrep_id => p_salesrep_id,
1588 p_start_date => p_start_date,
1589 p_end_date => p_end_date,
1590 p_org_id => p_org_id);
1591 --+
1592 --+ Added If Check : Hithanki 03/06/2003 For Bug Fix : 2781346
1593 --+
1594 IF l_process_rec_cnt > 0
1595 THEN
1596
1597 -- Mark the processed batches for deletion
1598 cn_transaction_load_pkg.void_batches
1599 (p_physical_batch_id => null,
1600 p_logical_batch_id => l_logical_batch_id);
1601 END IF;
1602
1603 -- Call end_batch to end debug log file
1604 debugmsg('Loader : Call end_batch');
1605 cn_message_pkg.end_batch(x_process_audit_id);
1606 debugmsg('Loader : End of Loader');
1607
1608 -- End of API body.
1609
1610
1611 -- Post processing hooks
1612
1613 -- User hooks
1614
1615 -- Customer post-processing section
1616
1617 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1618 'LOAD',
1619 'A',
1620 'V')
1621 THEN
1622
1623 cn_transaction_load_pub_cuhk.load_post
1624 (p_api_version => p_api_version,
1625 p_init_msg_list => p_init_msg_list,
1626 p_commit => p_commit,
1627 p_validation_level => p_validation_level,
1628 x_return_status => x_return_status,
1629 x_msg_count => x_msg_count,
1630 x_msg_data => x_msg_data,
1631 x_loading_status => x_loading_status
1632 );
1633
1634 IF (x_return_status = FND_API.G_RET_STS_ERROR )
1635 THEN
1636 RAISE FND_API.G_EXC_ERROR;
1637 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1638 THEN
1639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640 END IF;
1641 END IF;
1642
1643
1644 -- Vertical industry post-processing section
1645
1646 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1647 'LOAD',
1648 'A',
1649 'C')
1650 THEN
1651
1652 cn_transaction_load_pub_vuhk.load_post
1653 (p_api_version => p_api_version,
1654 p_init_msg_list => p_init_msg_list,
1655 p_commit => p_commit,
1656 p_validation_level => p_validation_level,
1657 x_return_status => x_return_status,
1658 x_msg_count => x_msg_count,
1659 x_msg_data => x_msg_data,
1660 x_loading_status => x_loading_status
1661 );
1662
1663 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1664 THEN
1665 RAISE FND_API.G_EXC_ERROR;
1666 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1667 THEN
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 END IF;
1670 END IF;
1671
1672
1673 -- Message enable hook
1674
1675 IF JTF_USR_HKS.Ok_to_execute('CN_TRANSACTION_LOAD_PUB',
1676 'LOAD',
1677 'M',
1678 'M')
1679 THEN
1680 IF cn_transaction_load_pub_cuhk.ok_to_generate_msg
1681 THEN
1682 -- Clear bind variables
1683 -- XMLGEN.clearBindValues;
1684
1685 -- Set values for bind variables,
1686 -- call this for all bind variables in the business object
1687 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
1688
1689 -- Get a ID for workflow/ business object instance
1690 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1691
1692 -- Do this for all the bind variables in the Business Object
1693 JTF_USR_HKS.load_bind_data
1694 (l_bind_data_id, 'PROCESS_AUDIT_ID', x_process_audit_id, 'S', 'S');
1695
1696 -- Message generation API
1697 JTF_USR_HKS.generate_message
1698 (p_prod_code => 'CN',
1699 p_bus_obj_code => 'TRXLOAD',
1700 p_bus_obj_name => 'TRX_LOAD',
1701 p_action_code => 'I',
1702 p_bind_data_id => l_bind_data_id,
1703 p_oai_param => null,
1704 p_oai_array => l_oai_array,
1705 x_return_code => x_return_status) ;
1706
1707 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1708 THEN
1709 RAISE FND_API.G_EXC_ERROR;
1710 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1711 THEN
1712 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1713 END IF;
1714 END IF;
1715 END IF;
1716
1717
1718 -- Standard call to get message count and if count is 1, get message info.
1719 FND_MSG_PUB.Count_And_Get
1720 (p_count => x_msg_count ,
1721 p_data => x_msg_data ,
1722 p_encoded => FND_API.G_FALSE
1723 );
1724
1725 EXCEPTION
1726 WHEN no_valid_transactions THEN
1727 OPEN logical_batches(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1728 ROLLBACK TO load_savepoint;
1729
1730 UPDATE cn_comm_lines_api_all
1731 SET load_status = 'SALESREP ERROR'
1732 WHERE
1733 load_status = 'UNLOADED' AND
1734 trx_type <> 'FORECAST' AND
1735 (adjust_status <> 'SCA_PENDING' ) AND -- OR adjust_status IS NULL) AND
1736 processed_date >= TRUNC(p_start_date) AND
1737 processed_date < (TRUNC(p_end_date) + 1) AND
1738 ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id)) AND
1739 org_id = p_org_id;
1740 commit;
1741
1742 debugmsg('Loader : exception : exc_error : End of Loader');
1743 cn_message_pkg.end_batch(x_process_audit_id);
1744 x_loading_status := 'EXC_ERR';
1745 x_return_status := FND_API.G_RET_STS_ERROR ;
1746
1747 FND_MSG_PUB.Count_And_Get
1748 (
1749 p_count => x_msg_count ,
1750 p_data => x_msg_data ,
1751 p_encoded => FND_API.G_FALSE
1752 );
1753 WHEN FND_API.G_EXC_ERROR THEN
1754 ROLLBACK TO load_savepoint;
1755 debugmsg('Loader : exception : exc_error : End of Loader');
1756 cn_message_pkg.end_batch(x_process_audit_id);
1757 x_loading_status := 'EXC_ERR';
1758 x_return_status := FND_API.G_RET_STS_ERROR ;
1759 FND_MSG_PUB.Count_And_Get
1760 (
1761 p_count => x_msg_count ,
1762 p_data => x_msg_data ,
1763 p_encoded => FND_API.G_FALSE
1764 );
1765 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1766 ROLLBACK TO load_savepoint;
1767 debugmsg('Loader : exception : unexc_error : End of Loader');
1768 cn_message_pkg.end_batch(x_process_audit_id);
1769 x_loading_status := 'UNEXPECTED_ERR';
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1771 FND_MSG_PUB.Count_And_Get
1772 (
1773 p_count => x_msg_count ,
1774 p_data => x_msg_data ,
1775 p_encoded => FND_API.G_FALSE
1776 );
1777 WHEN OTHERS THEN
1778 ROLLBACK TO load_savepoint;
1779 debugmsg('Loader : exception : others : End of Loader');
1780 cn_message_pkg.end_batch(x_process_audit_id);
1781 x_loading_status := 'UNEXPECTED_ERR';
1782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1783 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1784 THEN
1785 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1786 END IF;
1787 FND_MSG_PUB.Count_And_Get
1788 (
1789 p_count => x_msg_count ,
1790 p_data => x_msg_data ,
1791 p_encoded => FND_API.G_FALSE
1792 );
1793 END load;
1794
1795 END cn_transaction_load_pub;