[Home] [Help]
PACKAGE BODY: APPS.CN_TRANSACTION_LOAD_PUB
Source
1 PACKAGE BODY cn_transaction_load_pub AS
2 -- $Header: cnploadb.pls 120.4 2007/10/25 00:17:00 achanda 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 api.ADJUST_STATUS,
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' 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 api.ADJUST_STATUS,
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' 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' 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' 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' 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
1154 GROUP BY
1155 api.employee_number,
1156 api.salesrep_id,
1157 acc.period_id,
1158 acc.start_date,
1159 acc.end_date;
1160
1161 CURSOR logical_batches2(p_salesrep_id NUMBER,
1162 p_start_date DATE,
1163 p_end_date DATE,
1164 p_org_id NUMBER) IS
1165 SELECT
1166 api.employee_number employee_number,
1167 api.salesrep_id salesrep_id,
1168 acc.period_id period_id,
1169 acc.start_date start_date,
1170 acc.end_date end_date,
1171 count(*) trx_count
1172 FROM
1173 cn_comm_lines_api_all api,
1174 cn_acc_period_statuses_v acc
1175 WHERE
1176 api.load_status = 'UNLOADED' AND
1177 api.trx_type <> 'FORECAST' AND
1178 (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL) AND
1179 api.processed_date >= TRUNC(p_start_date) AND
1180 api.processed_date < (TRUNC(p_end_date) + 1) AND
1181 ((p_salesrep_id IS NULL) OR (api.salesrep_id = p_salesrep_id)) AND
1182 api.processed_date >= acc.start_date AND
1183 api.processed_date < (acc.end_date + 1) AND
1184 api.org_id = p_org_id AND
1185 (api.terr_id IS NOT NULL OR api.preserve_credit_override_flag = 'Y')
1186 GROUP BY
1187 api.employee_number,
1188 api.salesrep_id,
1189 acc.period_id,
1190 acc.start_date,
1191 acc.end_date;
1192
1193 -- Get individual physical batch id
1194 CURSOR physical_batches(l_logical_batch_id NUMBER) IS
1195 SELECT DISTINCT physical_batch_id
1196 FROM cn_process_batches
1197 WHERE logical_batch_id = l_logical_batch_id
1198 AND status_code = 'IN_USE';
1199
1200 physical_rec physical_batches%ROWTYPE;
1201 l_count NUMBER;
1202
1203 BEGIN
1204
1205 -- Standard Start of API savepoint
1206 SAVEPOINT load_savepoint;
1207
1208 -- Standard call to check for call compatibility.
1209 IF NOT FND_API.compatible_api_call
1210 ( l_api_version ,p_api_version ,l_api_name ,G_PKG_NAME )
1211 THEN
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213 END IF;
1214
1215 -- Initialize message list if p_init_msg_list is set to TRUE.
1216 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1217 FND_MSG_PUB.initialize;
1218 END IF;
1219
1220 -- Initialize API return status to success
1221 x_return_status := FND_API.G_RET_STS_SUCCESS;
1222 x_loading_status := 'CN_LOADED';
1223
1224
1225 -- User hooks
1226 -- Customer pre-processing section
1227
1228 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1229 'LOAD',
1230 'B',
1231 'C')
1232 THEN
1233
1234 cn_transaction_load_pub_cuhk.load_pre
1235 (p_api_version => p_api_version,
1236 p_init_msg_list => p_init_msg_list,
1237 p_commit => p_commit,
1238 p_validation_level => p_validation_level,
1239 x_return_status => x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data,
1242 x_loading_status => x_loading_status
1243 );
1244
1245 IF (x_return_status = FND_API.G_RET_STS_ERROR )
1246 THEN
1247 RAISE FND_API.G_EXC_ERROR;
1248 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1249 THEN
1250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1251 END IF;
1252 END IF;
1253
1254
1255 -- Vertical industry pre-processing section
1256
1257 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1258 'LOAD',
1259 'B',
1260 'V')
1261 THEN
1262
1263 cn_transaction_load_pub_vuhk.load_pre
1264 (p_api_version => p_api_version,
1265 p_init_msg_list => p_init_msg_list,
1266 p_commit => p_commit,
1267 p_validation_level => p_validation_level,
1268 x_return_status => x_return_status,
1269 x_msg_count => x_msg_count,
1270 x_msg_data => x_msg_data,
1271 x_loading_status => x_loading_status
1272 );
1273
1274 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1275 THEN
1276 RAISE FND_API.G_EXC_ERROR;
1277 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1278 THEN
1279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1280 END IF;
1281 END IF;
1282
1283
1284 -- API body starts here
1285
1286 -- Call begin_batch to get process_audit_id for debug log file
1287
1288 debugmsg('Loader : Call begin_batch');
1289
1290 cn_message_pkg.begin_batch
1291 (x_process_type => 'LOADER',
1292 x_parent_proc_audit_id => null,
1293 x_process_audit_id => x_process_audit_id,
1294 x_request_id => null,
1295 p_org_id => p_org_id);
1296
1297 debugmsg('Loader : Start of Loader');
1298 debugmsg('Loader : process_audit_id is ' ||
1299 x_process_audit_id );
1300
1301 /* verify that parameter start date is within an open acc period */
1302 l_count := 0;
1303 select count(*)
1304 into l_count
1305 from cn_period_statuses_all
1306 where period_status = 'O'
1307 and org_id = p_org_id
1308 and (period_set_id, period_type_id) =
1309 (select period_set_id, period_type_id
1310 from cn_repositories_all
1311 where org_id = p_org_id)
1312 and p_start_date between start_date and end_date;
1313 IF (l_count = 0) THEN
1314 debugmsg('Loader : Parameter Start Date is not within an open acc period');
1315
1316 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1317 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
1318 FND_MESSAGE.SET_TOKEN('DATE', p_start_date);
1319 FND_MSG_PUB.Add;
1320 END IF;
1321
1322 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
1323 RAISE FND_API.G_EXC_ERROR ;
1324 END IF;
1325
1326 /* verify that parameter end date is within an open acc period */
1327 l_count := 0;
1328 select count(*)
1329 into l_count
1330 from cn_period_statuses_all
1331 where period_status = 'O'
1332 and org_id = p_org_id
1333 and (period_set_id, period_type_id) =
1334 (select period_set_id, period_type_id
1335 from cn_repositories_all
1336 where org_id = p_org_id)
1337 and p_end_date between start_date and end_date;
1338 IF (l_count = 0) THEN
1339 debugmsg('Loader : Parameter End Date is not within an open acc period');
1340
1341 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
1342 FND_MESSAGE.SET_NAME ('CN' , 'CN_CALC_SUB_OPEN_DATE');
1343 FND_MESSAGE.SET_TOKEN('DATE', p_end_date);
1344 FND_MSG_PUB.Add;
1345 END IF;
1346
1347 x_loading_status := 'CN_CALC_SUB_OPEN_DATE';
1348 RAISE FND_API.G_EXC_ERROR ;
1349 END IF;
1350
1351 /* Get the value of the profile "OIC: Skip Credit Allocation" */
1352 l_skip_credit_flag := 'Y';
1353 IF (Fnd_Profile.DEFINED('CN_SKIP_CREDIT_ALLOCATION')) THEN
1354 l_skip_credit_flag := NVL(Fnd_Profile.VALUE('CN_SKIP_CREDIT_ALLOCATION'), 'Y');
1355 END IF;
1356
1357 -- Check Data in API table
1358 cn_transaction_load_pkg.check_api_data(p_start_date => p_start_date,
1359 p_end_date => p_end_date,
1360 p_org_id => p_org_id);
1361
1362
1363 -- Validate ruleset status if the classification and
1364 -- rollup option is checked.
1365 IF (p_cls_rol_flag = 'Y') THEN
1366 debugmsg('Loader : validate ruleset status : load_start_date = '
1367 || p_start_date);
1368 debugmsg('Loader : validate ruleset status : load_end_date = '
1369 || p_end_date);
1370
1371 IF NOT cn_proc_batches_pkg.validate_ruleset_status
1372 (p_start_date, p_end_date, p_org_id) THEN
1373 debugmsg('Loader : validate ruleset fails.');
1374 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1375 THEN
1376 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_INVALID_RULESET');
1377 FND_MSG_PUB.Add;
1378 END IF;
1379 x_loading_status := 'CN_LOAD_INVALID_RULESET';
1380 RAISE FND_API.G_EXC_ERROR ;
1381 END IF;
1382 END IF;
1383
1384
1385 -- Insert into cn_process_batches for each srp-period
1386 -- Populate logical_batch_id. One logical_batch_id for one load
1387 -- Physical_batch_id is still empty, will be populated in Assign
1388
1389
1390 -- sequence s2 is for logical batch id
1391 SELECT cn_process_batches_s2.NEXTVAL
1392 INTO l_logical_batch_id
1393 FROM sys.dual;
1394
1395
1396 debugmsg('Loader : Logical batch id = '||l_logical_batch_id);
1397
1398 IF (l_skip_credit_flag = 'Y') THEN
1399 OPEN logical_batches(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1400 FETCH logical_batches BULK COLLECT INTO
1401 l_emp_no_tbl,
1402 l_srp_id_tbl,
1403 l_period_id_tbl,
1404 l_start_date_tbl,
1405 l_end_date_tbl,
1406 l_count_tbl;
1407 CLOSE logical_batches;
1408 ELSE
1409 OPEN logical_batches2(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1410 FETCH logical_batches2 BULK COLLECT INTO
1411 l_emp_no_tbl,
1412 l_srp_id_tbl,
1413 l_period_id_tbl,
1414 l_start_date_tbl,
1415 l_end_date_tbl,
1416 l_count_tbl;
1417 CLOSE logical_batches2;
1418 END IF;
1419
1420 l_logical_batch_count := l_srp_id_tbl.COUNT;
1421
1422 IF (l_logical_batch_count <= 0) THEN
1423 debugmsg('Loader : No transactions to load.');
1424
1425 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1426 THEN
1427 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_NO_TRX_TO_LOAD');
1428 FND_MSG_PUB.Add;
1429 END IF;
1430 x_loading_status := 'CN_LOAD_NO_TRX_TO_LOAD';
1431 RAISE FND_API.G_EXC_ERROR ;
1432 ELSE
1433 FOR i IN l_srp_id_tbl.FIRST .. l_srp_id_tbl.LAST LOOP
1434
1435 -- sequence s1 is for process batch id
1436 SELECT cn_process_batches_s1.NEXTVAL
1437 INTO l_process_batch_id
1438 FROM sys.dual;
1439 debugmsg('Loader : insert into cn_process_batches....');
1440 debugmsg('l_process_batch_id = ' || l_process_batch_id);
1441 debugmsg('l_logical_batch_id = '||l_logical_batch_id);
1442 debugmsg('period_id = '||l_period_id_tbl(i) );
1443 debugmsg('start_date = '||l_start_date_tbl(i));
1444 debugmsg('end_date '|| l_end_date_tbl(i));
1445 debugmsg('salesrep_id '|| l_srp_id_tbl(i) );
1446 debugmsg('employee_number '|| l_emp_no_tbl(i));
1447 debugmsg('trx_count '|| l_count_tbl(i));
1448
1449 IF (l_srp_id_tbl(i) IS NOT NULL) THEN
1450
1451 --+
1452 --+ Added Counter Variable : Hithanki 03/06/2003 For Bug Fix : 2781346
1453 --+
1454
1455 l_process_rec_cnt := l_process_rec_cnt + 1;
1456
1457 INSERT INTO cn_process_batches_all
1458 ( process_batch_id
1459 ,logical_batch_id
1460 ,srp_period_id
1461 ,period_id
1462 ,end_period_id
1463 ,start_date
1464 ,end_date
1465 ,salesrep_id
1466 ,sales_lines_total
1467 ,status_code
1468 ,process_batch_type
1469 ,creation_date
1470 ,created_by
1471 ,last_update_date
1472 ,last_updated_by
1473 ,last_update_login
1474 ,request_id
1475 ,program_application_id
1476 ,program_id
1477 ,program_update_date
1478 ,org_id)
1479 VALUES
1480 (
1481 l_process_batch_id
1482 ,l_logical_batch_id
1483 ,l_process_batch_id -- a dummy value for a not null column
1484 ,l_period_id_tbl(i) -- start_period_id
1485 ,l_period_id_tbl(i) -- end_period_id
1486 ,l_start_date_tbl(i) -- start_date
1487 ,l_end_date_tbl(i) -- end_date
1488 ,l_srp_id_tbl(i) -- salesrep_id
1489 ,l_count_tbl(i) -- sales_lines_total
1490 ,'IN_USE' -- status_code
1491 ,'CREATED_BY_LOADER' -- process_batch_type
1492 ,sysdate
1493 ,l_user_id
1494 ,sysdate
1495 ,l_user_id
1496 ,l_login_id
1497 ,l_conc_request_id
1498 ,l_prog_appl_id
1499 ,l_conc_prog_id
1500 ,sysdate
1501 ,p_org_id);
1502
1503 END IF;
1504
1505 END LOOP;
1506 END IF;
1507
1508 OPEN valid_transactions(l_logical_batch_id);
1509 FETCH valid_transactions INTO valid_rec;
1510
1511 IF (valid_transactions%NOTFOUND) THEN
1512
1513 debugmsg('Loader : All transactions to load have invalid salesrep id.');
1514
1515 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1516 THEN
1517 FND_MESSAGE.SET_NAME ('CN' , 'CN_LOAD_SALESREP_ERROR');
1518 FND_MSG_PUB.Add;
1519 END IF;
1520 x_loading_status := 'CN_LOAD_SALESREP_ERROR';
1521
1522 RAISE no_valid_transactions;
1523
1524 END IF;
1525
1526 CLOSE valid_transactions;
1527
1528 --+
1529 --+ Added If Check : Hithanki 03/06/2003 For Bug Fix : 2781346
1530 --+
1531 IF l_process_rec_cnt > 0 THEN
1532
1533 -- Split the logical batch into smaller physical batches
1534 -- populate the physical_batch_id in cn_process_batches
1535
1536
1537
1538 cn_transaction_load_pkg.assign(p_logical_batch_id => l_logical_batch_id, p_org_id => p_org_id);
1539
1540 -- Call load worker for each physical batch
1541 -- for regular loader, we call conc_dispatch to submit concurrent procedure.
1542 -- But for this public API the loader will do the job sequentially.
1543
1544 commit;
1545
1546 cn_transaction_load_pkg.pre_conc_dispatch(p_salesrep_id => p_salesrep_id,
1547 p_start_date => p_start_date,
1548 p_end_date => p_end_date,
1549 p_org_id => p_org_id);
1550
1551 commit;
1552
1553 FOR physical_rec IN physical_batches(l_logical_batch_id)
1554 LOOP
1555
1556 debugmsg('Loader : call load_worker'
1557 ||'physical_rec.physical_batch_id = '
1558 || physical_rec.physical_batch_id );
1559
1560 commit;
1561
1562
1563 load_worker(
1564 x_return_status => l_return_status,
1565 x_msg_count => l_msg_count,
1566 x_msg_data => l_msg_data,
1567 p_physical_batch_id => physical_rec.physical_batch_id,
1568 p_salesrep_id => p_salesrep_id,
1569 p_start_date => p_start_date,
1570 p_end_date => p_end_date,
1571 p_cls_rol_flag => p_cls_rol_flag,
1572 p_loading_status => x_loading_status,
1573 p_org_id => p_org_id,
1574 x_loading_status => l_loading_status
1575 );
1576
1577 commit;
1578
1579 END LOOP;
1580 END IF;
1581
1582 commit;
1583
1584 cn_transaction_load_pkg.post_conc_dispatch
1585 (p_salesrep_id => p_salesrep_id,
1586 p_start_date => p_start_date,
1587 p_end_date => p_end_date,
1588 p_org_id => p_org_id);
1589 --+
1590 --+ Added If Check : Hithanki 03/06/2003 For Bug Fix : 2781346
1591 --+
1592 IF l_process_rec_cnt > 0
1593 THEN
1594
1595 -- Mark the processed batches for deletion
1596 cn_transaction_load_pkg.void_batches
1597 (p_physical_batch_id => null,
1598 p_logical_batch_id => l_logical_batch_id);
1599 END IF;
1600
1601 -- Call end_batch to end debug log file
1602 debugmsg('Loader : Call end_batch');
1603 cn_message_pkg.end_batch(x_process_audit_id);
1604 debugmsg('Loader : End of Loader');
1605
1606 -- End of API body.
1607
1608
1609 -- Post processing hooks
1610
1611 -- User hooks
1612
1613 -- Customer post-processing section
1614
1615 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1616 'LOAD',
1617 'A',
1618 'V')
1619 THEN
1620
1621 cn_transaction_load_pub_cuhk.load_post
1622 (p_api_version => p_api_version,
1623 p_init_msg_list => p_init_msg_list,
1624 p_commit => p_commit,
1625 p_validation_level => p_validation_level,
1626 x_return_status => x_return_status,
1627 x_msg_count => x_msg_count,
1628 x_msg_data => x_msg_data,
1629 x_loading_status => x_loading_status
1630 );
1631
1632 IF (x_return_status = FND_API.G_RET_STS_ERROR )
1633 THEN
1634 RAISE FND_API.G_EXC_ERROR;
1635 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1636 THEN
1637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1638 END IF;
1639 END IF;
1640
1641
1642 -- Vertical industry post-processing section
1643
1644 IF JTF_USR_HKS.Ok_to_Execute('CN_TRANSACTION_LOAD_PUB',
1645 'LOAD',
1646 'A',
1647 'C')
1648 THEN
1649
1650 cn_transaction_load_pub_vuhk.load_post
1651 (p_api_version => p_api_version,
1652 p_init_msg_list => p_init_msg_list,
1653 p_commit => p_commit,
1654 p_validation_level => p_validation_level,
1655 x_return_status => x_return_status,
1656 x_msg_count => x_msg_count,
1657 x_msg_data => x_msg_data,
1658 x_loading_status => x_loading_status
1659 );
1660
1661 IF ( x_return_status = FND_API.G_RET_STS_ERROR )
1662 THEN
1663 RAISE FND_API.G_EXC_ERROR;
1664 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1665 THEN
1666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1667 END IF;
1668 END IF;
1669
1670
1671 -- Message enable hook
1672
1673 IF JTF_USR_HKS.Ok_to_execute('CN_TRANSACTION_LOAD_PUB',
1674 'LOAD',
1675 'M',
1676 'M')
1677 THEN
1678 IF cn_transaction_load_pub_cuhk.ok_to_generate_msg
1679 THEN
1680 -- Clear bind variables
1681 -- XMLGEN.clearBindValues;
1682
1683 -- Set values for bind variables,
1684 -- call this for all bind variables in the business object
1685 -- XMLGEN.setBindValue('SRP_PMT_PLAN_ID', x_srp_pmt_plan_id);
1686
1687 -- Get a ID for workflow/ business object instance
1688 l_bind_data_id := JTF_USR_HKS.get_bind_data_id;
1689
1690 -- Do this for all the bind variables in the Business Object
1691 JTF_USR_HKS.load_bind_data
1692 (l_bind_data_id, 'PROCESS_AUDIT_ID', x_process_audit_id, 'S', 'S');
1693
1694 -- Message generation API
1695 JTF_USR_HKS.generate_message
1696 (p_prod_code => 'CN',
1697 p_bus_obj_code => 'TRXLOAD',
1698 p_bus_obj_name => 'TRX_LOAD',
1699 p_action_code => 'I',
1700 p_bind_data_id => l_bind_data_id,
1701 p_oai_param => null,
1702 p_oai_array => l_oai_array,
1703 x_return_code => x_return_status) ;
1704
1705 IF (x_return_status = FND_API.G_RET_STS_ERROR)
1706 THEN
1707 RAISE FND_API.G_EXC_ERROR;
1708 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR )
1709 THEN
1710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1711 END IF;
1712 END IF;
1713 END IF;
1714
1715
1716 -- Standard call to get message count and if count is 1, get message info.
1717 FND_MSG_PUB.Count_And_Get
1718 (p_count => x_msg_count ,
1719 p_data => x_msg_data ,
1720 p_encoded => FND_API.G_FALSE
1721 );
1722
1723 EXCEPTION
1724 WHEN no_valid_transactions THEN
1725 OPEN logical_batches(p_salesrep_id, p_start_date, p_end_date, p_org_id);
1726 ROLLBACK TO load_savepoint;
1727
1728 UPDATE cn_comm_lines_api_all
1729 SET load_status = 'SALESREP_ERROR'
1730 WHERE
1731 load_status = 'UNLOADED' AND
1732 trx_type <> 'FORECAST' AND
1733 (adjust_status <> 'SCA_PENDING' OR adjust_status IS NULL) AND
1734 processed_date >= TRUNC(p_start_date) AND
1735 processed_date < (TRUNC(p_end_date) + 1) AND
1736 ((p_salesrep_id IS NULL) OR (salesrep_id = p_salesrep_id)) AND
1737 org_id = p_org_id;
1738 commit;
1739
1740 debugmsg('Loader : exception : exc_error : End of Loader');
1741 cn_message_pkg.end_batch(x_process_audit_id);
1742 x_loading_status := 'EXC_ERR';
1743 x_return_status := FND_API.G_RET_STS_ERROR ;
1744
1745 FND_MSG_PUB.Count_And_Get
1746 (
1747 p_count => x_msg_count ,
1748 p_data => x_msg_data ,
1749 p_encoded => FND_API.G_FALSE
1750 );
1751 WHEN FND_API.G_EXC_ERROR THEN
1752 ROLLBACK TO load_savepoint;
1753 debugmsg('Loader : exception : exc_error : End of Loader');
1754 cn_message_pkg.end_batch(x_process_audit_id);
1755 x_loading_status := 'EXC_ERR';
1756 x_return_status := FND_API.G_RET_STS_ERROR ;
1757 FND_MSG_PUB.Count_And_Get
1758 (
1759 p_count => x_msg_count ,
1760 p_data => x_msg_data ,
1761 p_encoded => FND_API.G_FALSE
1762 );
1763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1764 ROLLBACK TO load_savepoint;
1765 debugmsg('Loader : exception : unexc_error : End of Loader');
1766 cn_message_pkg.end_batch(x_process_audit_id);
1767 x_loading_status := 'UNEXPECTED_ERR';
1768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1769 FND_MSG_PUB.Count_And_Get
1770 (
1771 p_count => x_msg_count ,
1772 p_data => x_msg_data ,
1773 p_encoded => FND_API.G_FALSE
1774 );
1775 WHEN OTHERS THEN
1776 ROLLBACK TO load_savepoint;
1777 debugmsg('Loader : exception : others : End of Loader');
1778 cn_message_pkg.end_batch(x_process_audit_id);
1779 x_loading_status := 'UNEXPECTED_ERR';
1780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1781 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1782 THEN
1783 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
1784 END IF;
1785 FND_MSG_PUB.Count_And_Get
1786 (
1787 p_count => x_msg_count ,
1788 p_data => x_msg_data ,
1789 p_encoded => FND_API.G_FALSE
1790 );
1791 END load;
1792
1793 END cn_transaction_load_pub;