[Home] [Help]
PACKAGE BODY: APPS.PO_ASL_API_PVT
Source
1 PACKAGE BODY PO_ASL_API_PVT AS
2 /* $Header: PO_ASL_API_PVT.plb 120.2.12020000.2 2013/02/11 13:53:34 vegajula noship $*/
3
4 g_session_key NUMBER;
5
6 --------------------------------------------------------------------------------
7 --Start of Comments
8
9 --Name: process
10
11 --Function:
12 -- It will create / update records in the base tables for the non rejected
13 -- records in the gt tables.
14
15 --Parameters:
16
17 --IN:
18 -- p_session_key NUMBER
19
20 --OUT:
21 -- x_return_status VARCHAR2
22 -- x_return_msg VARCHAR2
23
24 --End of Comments
25 --------------------------------------------------------------------------------
26
27 PROCEDURE process(
28 p_session_key IN NUMBER
29 , x_return_status OUT NOCOPY VARCHAR2
30 , x_return_msg OUT NOCOPY VARCHAR2
31 )
32 IS
33 l_progress NUMBER := 0;
34
35 BEGIN
36 PO_ASL_API_PVT.Log('START ::: PO_ASL_API_PVT.process ');
37 PO_ASL_API_PVT.Log('p_session_key:' || p_session_key);
38 g_session_key := p_session_key;
39 --create/update/delete records in the base tables for the non rejected
40 --records in the gt tables.
41
42 ------------------------------CREATE MODE starts------------------------------
43 INSERT INTO po_approved_supplier_list
44 ( asl_id ,
45 using_organization_id ,
46 owning_organization_id ,
47 vendor_business_type ,
48 asl_status_id ,
49 last_update_date ,
50 last_updated_by ,
51 creation_date ,
52 created_by ,
53 manufacturer_id ,
54 vendor_id, item_id ,
55 category_id ,
56 vendor_site_id ,
57 primary_vendor_item ,
58 manufacturer_asl_id ,
59 review_by_date ,
60 comments ,
61 attribute_category ,
62 attribute1 ,
63 attribute2 ,
64 attribute3 ,
65 attribute4 ,
66 attribute5 ,
67 attribute6 ,
68 attribute7 ,
69 attribute8 ,
70 attribute9 ,
71 attribute10 ,
72 attribute11 ,
73 attribute12 ,
74 attribute13 ,
75 attribute14 ,
76 attribute15 ,
77 last_update_login ,
78 request_id ,
79 program_application_id ,
80 program_id ,
81 program_update_date ,
82 disable_flag
83 )
84 (SELECT
85 asl_id ,
86 using_organization_id ,
87 owning_organization_id ,
88 Upper(vendor_business_type) ,
89 asl_status_id ,
90 SYSDATE ,
91 fnd_global.USER_ID ,
92 SYSDATE ,
93 fnd_global.USER_ID ,
94 manufacturer_id ,
95 vendor_id, item_id ,
96 category_id ,
97 vendor_site_id ,
98 primary_vendor_item ,
99 manufacturer_asl_id ,
100 review_by_date ,
101 comments ,
102 attribute_category ,
103 attribute1 ,
104 attribute2 ,
105 attribute3 ,
106 attribute4 ,
107 attribute5 ,
108 attribute6 ,
109 attribute7 ,
110 attribute8 ,
111 attribute9 ,
112 attribute10 ,
113 attribute11 ,
114 attribute12 ,
115 attribute13 ,
116 attribute14 ,
117 attribute15 ,
118 fnd_global.LOGIN_ID ,
119 request_id ,
120 program_application_id ,
121 program_id ,
122 program_update_date ,
123 Decode(disable_flag,'Y','Y','N')
124 FROM po_approved_supplier_list_gt
125 WHERE process_action = PO_ASL_API_PUB.g_ACTION_CREATE AND
126 process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED
127 );
128 po_asl_api_pvt.log('number of rows inserted into po_approved_supplier_list:'
129 || SQL%ROWCOUNT);
130 l_progress := 10;
131
132 INSERT INTO po_asl_attributes
133 ( asl_id ,
134 using_organization_id ,
135 last_update_date ,
136 last_updated_by ,
137 creation_date ,
138 created_by ,
139 document_sourcing_method ,
140 release_generation_method ,
141 purchasing_unit_of_measure ,
142 enable_plan_schedule_flag ,
143 enable_ship_schedule_flag ,
144 plan_schedule_type ,
145 ship_schedule_type ,
146 plan_bucket_pattern_id ,
147 ship_bucket_pattern_id ,
148 enable_autoschedule_flag ,
149 scheduler_id ,
150 enable_authorizations_flag ,
151 vendor_id ,
152 vendor_site_id ,
153 item_id ,
154 category_id ,
155 attribute_category ,
156 attribute1 ,
157 attribute2 ,
158 attribute3 ,
159 attribute4 ,
160 attribute5 ,
161 attribute6 ,
162 attribute7 ,
163 attribute8 ,
164 attribute9 ,
165 attribute10 ,
166 attribute11 ,
167 attribute12 ,
168 attribute13 ,
169 attribute14 ,
170 attribute15 ,
171 last_update_login ,
172 request_id ,
173 program_application_id ,
174 program_id ,
175 program_update_date ,
176 price_update_tolerance ,
177 processing_lead_time ,
178 min_order_qty ,
179 fixed_lot_multiple ,
180 delivery_calendar ,
181 country_of_origin_code ,
182 enable_vmi_flag ,
183 vmi_min_qty ,
184 vmi_max_qty ,
185 enable_vmi_auto_replenish_flag ,
186 vmi_replenishment_approval ,
187 consigned_from_supplier_flag ,
188 last_billing_date ,
189 consigned_billing_cycle ,
190 consume_on_aging_flag ,
191 aging_period ,
192 replenishment_method ,
193 vmi_min_days ,
194 vmi_max_days ,
195 fixed_order_quantity ,
196 forecast_horizon
197 )
198 (SELECT
199 asl_id ,
200 using_organization_id ,
201 SYSDATE ,
202 fnd_global.USER_ID ,
203 SYSDATE ,
204 fnd_global.USER_ID ,
205 document_sourcing_method ,
206 release_generation_method ,
207 purchasing_unit_of_measure_dsp ,
208 Decode(enable_plan_schedule_flag_dsp,'Y','Y','N') ,
209 Decode(enable_ship_schedule_flag_dsp,'Y','Y','N') ,
210 plan_schedule_type ,
211 ship_schedule_type ,
212 plan_bucket_pattern_id ,
213 ship_bucket_pattern_id ,
214 Decode(enable_autoschedule_flag_dsp,'Y','Y','N') ,
215 scheduler_id ,
216 Decode(enable_authorizations_flag_dsp,'Y','Y','N') ,
217 vendor_id ,
218 vendor_site_id ,
219 item_id ,
220 category_id ,
221 attribute_category ,
222 attribute1 ,
223 attribute2 ,
224 attribute3 ,
225 attribute4 ,
226 attribute5 ,
227 attribute6 ,
228 attribute7 ,
229 attribute8 ,
230 attribute9 ,
231 attribute10 ,
232 attribute11 ,
233 attribute12 ,
234 attribute13 ,
235 attribute14 ,
236 attribute15 ,
237 fnd_global.LOGIN_ID ,
238 request_id ,
239 program_application_id ,
240 program_id ,
241 program_update_date ,
242 price_update_tolerance_dsp ,
243 processing_lead_time_dsp ,
244 min_order_qty_dsp ,
245 fixed_lot_multiple_dsp ,
246 delivery_calendar_dsp ,
247 country_of_origin_code_dsp ,
248 Decode(enable_vmi_flag_dsp,'Y','Y','N') ,
249 vmi_min_qty_dsp ,
250 vmi_max_qty_dsp ,
251 Decode(enable_vmi_auto_replenish_flag,'Y','Y','N') ,
252 vmi_replenishment_approval ,
253 Decode(consigned_from_supp_flag_dsp,'Y','Y','N') ,
254 last_billing_date ,
255 consigned_billing_cycle_dsp ,
256 Decode(consume_on_aging_flag_dsp,'Y','Y','N') ,
257 aging_period_dsp ,
258 replenishment_method ,
259 vmi_min_days_dsp ,
260 vmi_max_days_dsp ,
261 fixed_order_quantity_dsp ,
262 forecast_horizon_dsp
263 FROM po_asl_attributes_gt
264 WHERE user_key IN
265 (SELECT user_key
266 FROM po_approved_supplier_list_gt
267 WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED) AND
268 process_action = PO_ASL_API_PUB.g_ACTION_ADD
269 );
270 po_asl_api_pvt.log('number of rows inserted into po_asl_attributes:'
271 || SQL%ROWCOUNT);
272 l_progress := 15;
273
274 INSERT INTO po_asl_attributes
275 ( asl_id ,
276 using_organization_id ,
277 last_update_date ,
278 last_updated_by ,
279 creation_date ,
280 created_by ,
281 document_sourcing_method ,
282 release_generation_method ,
283 purchasing_unit_of_measure ,
284 enable_plan_schedule_flag ,
285 enable_ship_schedule_flag ,
286 plan_schedule_type ,
287 ship_schedule_type ,
288 plan_bucket_pattern_id ,
289 ship_bucket_pattern_id ,
290 enable_autoschedule_flag ,
291 scheduler_id ,
292 enable_authorizations_flag ,
293 vendor_id ,
294 vendor_site_id ,
295 item_id ,
296 category_id ,
297 attribute_category ,
298 attribute1 ,
299 attribute2 ,
300 attribute3 ,
301 attribute4 ,
302 attribute5 ,
303 attribute6 ,
304 attribute7 ,
305 attribute8 ,
306 attribute9 ,
307 attribute10 ,
308 attribute11 ,
309 attribute12 ,
310 attribute13 ,
311 attribute14 ,
312 attribute15 ,
313 last_update_login ,
314 request_id ,
315 program_application_id ,
316 program_id ,
317 program_update_date ,
318 price_update_tolerance ,
319 processing_lead_time ,
320 min_order_qty ,
321 fixed_lot_multiple ,
322 delivery_calendar ,
323 country_of_origin_code ,
324 enable_vmi_flag ,
325 vmi_min_qty ,
326 vmi_max_qty ,
327 enable_vmi_auto_replenish_flag ,
328 vmi_replenishment_approval ,
329 consigned_from_supplier_flag ,
330 last_billing_date ,
331 consigned_billing_cycle ,
332 consume_on_aging_flag ,
333 aging_period ,
334 replenishment_method ,
335 vmi_min_days ,
336 vmi_max_days ,
337 fixed_order_quantity ,
338 forecast_horizon
339 )
340 (SELECT
341 asl_id ,
342 using_organization_id ,
343 SYSDATE ,
344 fnd_global.USER_ID ,
345 SYSDATE ,
346 fnd_global.USER_ID ,
347 'ASL' ,
348 NULL ,
349 NULL ,
350 'N' ,
351 'N' ,
352 NULL ,
353 NULL ,
354 NULL ,
355 NULL ,
356 'N' ,
357 NULL ,
358 'N' ,
359 NULL ,
360 NULL ,
361 item_id ,
362 category_id ,
363 NULL ,
364 NULL ,
365 NULL ,
366 NULL ,
367 NULL ,
368 NULL ,
369 NULL ,
370 NULL ,
371 NULL ,
372 NULL ,
373 NULL ,
374 NULL ,
375 NULL ,
376 NULL ,
377 NULL ,
378 NULL ,
379 fnd_global.LOGIN_ID ,
380 NULL ,
381 NULL ,
382 NULL ,
383 NULL ,
384 NULL ,
385 NULL ,
386 NULL ,
387 NULL ,
388 NULL ,
389 NULL ,
390 NULL ,
391 NULL ,
392 NULL ,
393 NULL ,
394 NULL ,
395 NULL ,
396 NULL ,
397 NULL ,
398 NULL ,
399 NULL ,
400 NULL ,
401 NULL ,
402 NULL ,
403 NULL ,
404 NULL
405 FROM po_approved_supplier_list_gt PASL
406 WHERE PASL.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED AND
407 PASL.process_action = PO_ASL_API_PUB.g_ACTION_CREATE AND
408 (Upper(PASL.vendor_business_type) = 'MANUFACTURER' OR
409 NOT EXISTS
410 (SELECT PAA.user_key
411 FROM po_asl_attributes_gt PAA
412 WHERE PAA.user_key = PASL.user_key))
413 );
414 po_asl_api_pvt.log('number of DEFAULT rows inserted into po_asl_attributes:'
418 INSERT INTO po_asl_documents
415 || SQL%ROWCOUNT);
416 l_progress := 18;
417
419 ( asl_id ,
420 using_organization_id ,
421 sequence_num ,
422 document_type_code ,
423 document_header_id ,
424 document_line_id ,
425 last_update_date ,
426 last_updated_by ,
427 creation_date ,
428 created_by ,
429 attribute_category ,
430 attribute1 ,
431 attribute2 ,
432 attribute3 ,
433 attribute4 ,
434 attribute5 ,
435 attribute6 ,
436 attribute7 ,
437 attribute8 ,
438 attribute9 ,
439 attribute10 ,
440 attribute11 ,
441 attribute12 ,
442 attribute13 ,
443 attribute14 ,
444 attribute15 ,
445 last_update_login ,
446 request_id ,
447 program_application_id ,
448 program_id ,
449 program_update_date ,
450 org_id
451 )
452 (SELECT
453 asl_id ,
454 using_organization_id ,
455 sequence_num ,
456 document_type_code ,
457 document_header_id ,
458 document_line_id ,
459 SYSDATE ,
460 fnd_global.USER_ID ,
461 SYSDATE ,
462 fnd_global.USER_ID ,
463 attribute_category ,
464 attribute1 ,
465 attribute2 ,
466 attribute3 ,
467 attribute4 ,
468 attribute5 ,
469 attribute6 ,
470 attribute7 ,
471 attribute8 ,
472 attribute9 ,
473 attribute10 ,
474 attribute11 ,
475 attribute12 ,
476 attribute13 ,
477 attribute14 ,
478 attribute15 ,
479 fnd_global.LOGIN_ID ,
480 request_id ,
481 program_application_id ,
482 program_id ,
483 program_update_date ,
484 org_id
485 FROM po_asl_documents_gt
486 WHERE user_key IN
487 (SELECT user_key
488 FROM po_approved_supplier_list_gt
489 WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
490 AND process_action = PO_ASL_API_PUB.g_ACTION_ADD
491 );
492 po_asl_api_pvt.log('number of rows inserted into po_asl_documents:'
493 || SQL%ROWCOUNT);
494 l_progress := 20;
495
496 INSERT INTO chv_authorizations
497 ( reference_id ,
498 reference_type ,
499 authorization_code ,
500 authorization_sequence ,
501 last_update_date ,
502 last_updated_by ,
503 creation_date ,
504 created_by ,
505 timefence_days ,
506 attribute_category ,
507 attribute1 ,
508 attribute2 ,
509 attribute3 ,
510 attribute4 ,
511 attribute5 ,
512 attribute6 ,
513 attribute7 ,
514 attribute8 ,
515 attribute9 ,
516 attribute10 ,
517 attribute11 ,
518 attribute12 ,
519 attribute13 ,
520 attribute14 ,
521 attribute15 ,
522 last_update_login ,
523 request_id ,
524 program_application_id ,
525 program_id ,
526 program_update_date ,
527 using_organization_id
528 )
529 (SELECT
530 reference_id ,
531 reference_type ,
532 authorization_code ,
533 authorization_sequence_dsp ,
534 SYSDATE ,
535 fnd_global.USER_ID ,
536 SYSDATE ,
537 fnd_global.USER_ID ,
538 timefence_days_dsp ,
539 attribute_category ,
540 attribute1 ,
541 attribute2 ,
542 attribute3 ,
543 attribute4 ,
544 attribute5 ,
545 attribute6 ,
546 attribute7 ,
547 attribute8 ,
548 attribute9 ,
549 attribute10 ,
550 attribute11 ,
551 attribute12 ,
552 attribute13 ,
553 attribute14 ,
554 attribute15 ,
558 program_id ,
555 fnd_global.LOGIN_ID ,
556 request_id ,
557 program_application_id ,
559 program_update_date ,
560 using_organization_id
561 FROM chv_authorizations_gt
562 WHERE user_key IN
563 (SELECT user_key
564 FROM po_approved_supplier_list_gt
565 WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
566 AND process_action = PO_ASL_API_PUB.g_ACTION_ADD
567 );
568 po_asl_api_pvt.log('number of rows inserted into chv_authorizations:'
569 || SQL%ROWCOUNT);
570 l_progress := 25;
571
572 INSERT INTO po_supplier_item_capacity
573 ( capacity_id ,
574 asl_id ,
575 using_organization_id ,
576 from_date ,
577 To_Date ,
578 capacity_per_day ,
579 last_update_date ,
580 last_updated_by ,
581 creation_date ,
582 created_by ,
583 attribute_category ,
584 attribute1 ,
585 attribute2 ,
586 attribute3 ,
587 attribute4 ,
588 attribute5 ,
589 attribute6 ,
590 attribute7 ,
591 attribute8 ,
592 attribute9 ,
593 attribute10 ,
594 attribute11 ,
595 attribute12 ,
596 attribute13 ,
597 attribute14 ,
598 attribute15 ,
599 last_update_login ,
600 request_id ,
601 program_application_id ,
602 program_id ,
603 program_update_date
604 )
605 (SELECT
606 capacity_id ,
607 asl_id ,
608 using_organization_id ,
609 from_date_dsp ,
610 to_Date_dsp ,
611 capacity_per_day_dsp ,
612 SYSDATE ,
613 fnd_global.USER_ID ,
614 SYSDATE ,
615 fnd_global.USER_ID ,
616 attribute_category ,
617 attribute1 ,
618 attribute2 ,
619 attribute3 ,
620 attribute4 ,
621 attribute5 ,
622 attribute6 ,
623 attribute7 ,
624 attribute8 ,
625 attribute9 ,
626 attribute10 ,
627 attribute11 ,
628 attribute12 ,
629 attribute13 ,
630 attribute14 ,
631 attribute15 ,
632 fnd_global.LOGIN_ID ,
633 request_id ,
634 program_application_id ,
635 program_id ,
636 program_update_date
637 FROM po_supplier_item_capacity_gt PSIC
638 WHERE user_key IN
639 (SELECT user_key
640 FROM po_approved_supplier_list_gt
641 WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
642 AND process_action = PO_ASL_API_PUB.g_ACTION_ADD
643 );
644 po_asl_api_pvt.log('number of rows inserted into po_supplier_item_capacity:'
645 || SQL%ROWCOUNT);
646 l_progress := 30;
647
648 INSERT INTO po_supplier_item_tolerance
649 ( asl_id ,
650 using_organization_id ,
651 number_of_days ,
652 tolerance ,
653 last_update_date ,
654 last_updated_by ,
655 creation_date ,
656 created_by ,
657 attribute_category ,
658 attribute1 ,
659 attribute2 ,
660 attribute3 ,
661 attribute4 ,
662 attribute5 ,
663 attribute6 ,
664 attribute7 ,
665 attribute8 ,
666 attribute9 ,
667 attribute10 ,
668 attribute11 ,
669 attribute12 ,
670 attribute13 ,
671 attribute14 ,
672 attribute15 ,
673 last_update_login ,
674 request_id ,
675 program_application_id ,
676 program_id ,
677 program_update_date
678 )
679 (SELECT
680 asl_id ,
681 using_organization_id ,
682 number_of_days_dsp ,
683 tolerance_dsp ,
684 SYSDATE ,
685 fnd_global.USER_ID ,
686 SYSDATE ,
687 fnd_global.USER_ID ,
688 attribute_category ,
689 attribute1 ,
690 attribute2 ,
691 attribute3 ,
692 attribute4 ,
696 attribute8 ,
693 attribute5 ,
694 attribute6 ,
695 attribute7 ,
697 attribute9 ,
698 attribute10 ,
699 attribute11 ,
700 attribute12 ,
701 attribute13 ,
702 attribute14 ,
703 attribute15 ,
704 fnd_global.LOGIN_ID ,
705 request_id ,
706 program_application_id ,
707 program_id ,
708 program_update_date
709 FROM po_supplier_item_tolerance_gt
710 WHERE user_key IN
711 (SELECT user_key
712 FROM po_approved_supplier_list_gt
713 WHERE process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
714 AND process_action = PO_ASL_API_PUB.g_ACTION_ADD
715 );
716 po_asl_api_pvt.log('Num of rows inserted into po_supplier_item_tolerance:'
717 || SQL%ROWCOUNT);
718 l_progress := 35;
719 ------------------------------CREATE MODE END---------------------------------
720
721 ------------------------------UPDATE MODE STARTS------------------------------
722 UPDATE po_approved_supplier_list PASL
723 SET
724 (PASL.vendor_business_type ,
725 PASL.asl_status_id ,
726 PASL.last_update_date ,
727 PASL.last_updated_by ,
728 PASL.manufacturer_asl_id ,
729 PASL.review_by_date ,
730 PASL.comments ,
731 PASL.attribute_category ,
732 PASL.attribute1 ,
733 PASL.attribute2 ,
734 PASL.attribute3 ,
735 PASL.attribute4 ,
736 PASL.attribute5 ,
737 PASL.attribute6 ,
738 PASL.attribute7 ,
739 PASL.attribute8 ,
740 PASL.attribute9 ,
741 PASL.attribute10 ,
742 PASL.attribute11 ,
743 PASL.attribute12 ,
744 PASL.attribute13 ,
745 PASL.attribute14 ,
746 PASL.attribute15 ,
747 PASL.request_id ,
748 PASL.program_application_id ,
749 PASL.program_id ,
750 PASL.program_update_date ,
751 PASL.disable_flag ,
752 PASL.last_update_login )
753 = (SELECT ASLGT.vendor_business_type ,
754 ASLGT.asl_status_id ,
755 SYSDATE ,
756 fnd_global.USER_ID ,
757 ASLGT.manufacturer_id ,
758 ASLGT.review_by_date ,
759 ASLGT.comments ,
760 ASLGT.attribute_category ,
761 ASLGT.attribute1 ,
762 ASLGT.attribute2 ,
763 ASLGT.attribute3 ,
764 ASLGT.attribute4 ,
765 ASLGT.attribute5 ,
766 ASLGT.attribute6 ,
767 ASLGT.attribute7 ,
768 ASLGT.attribute8 ,
769 ASLGT.attribute9 ,
770 ASLGT.attribute10 ,
771 ASLGT.attribute11 ,
772 ASLGT.attribute12 ,
773 ASLGT.attribute13 ,
774 ASLGT.attribute14 ,
775 ASLGT.attribute15 ,
776 ASLGT.request_id ,
777 ASLGT.program_application_id,
778 ASLGT.program_id ,
779 ASLGT.program_update_date ,
780 Decode(ASLGT.disable_flag,'Y','Y','N') ,
781 fnd_global.LOGIN_ID
782 FROM po_approved_supplier_list_gt ASLGT
783 WHERE PASL.asl_id = ASLGT.asl_id
784 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
785 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
786 WHERE PASL.asl_id IN
787 (SELECT ASLGT.asl_id
788 FROM po_approved_supplier_list_gt ASLGT
789 WHERE PASL.asl_id = ASLGT.asl_id
790 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
791 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
792
793 po_asl_api_pvt.log('number of rows updated in po_approved_supplier_list:'
794 || SQL%ROWCOUNT);
795 l_progress := 40;
796
797 UPDATE po_asl_attributes PAA
798 SET
799 (PAA.last_update_date ,
800 PAA.last_updated_by ,
801 PAA.purchasing_unit_of_measure ,
802 PAA.release_generation_method ,
803 PAA.enable_plan_schedule_flag ,
804 PAA.enable_ship_schedule_flag ,
805 PAA.plan_schedule_type ,
806 PAA.ship_schedule_type ,
807 PAA.plan_bucket_pattern_id ,
808 PAA.ship_bucket_pattern_id ,
809 PAA.enable_autoschedule_flag ,
810 PAA.scheduler_id ,
811 PAA.attribute_category ,
812 PAA.attribute1 ,
813 PAA.attribute2 ,
814 PAA.attribute3 ,
815 PAA.attribute4 ,
816 PAA.attribute5 ,
817 PAA.attribute6 ,
818 PAA.attribute7 ,
819 PAA.attribute8 ,
823 PAA.attribute12 ,
820 PAA.attribute9 ,
821 PAA.attribute10 ,
822 PAA.attribute11 ,
824 PAA.attribute13 ,
825 PAA.attribute14 ,
826 PAA.attribute15 ,
827 PAA.request_id ,
828 PAA.program_application_id ,
829 PAA.program_id ,
830 PAA.program_update_date ,
831 PAA.enable_authorizations_flag ,
832 PAA.last_update_login ,
833 PAA.price_update_tolerance ,
834 PAA.processing_lead_time ,
835 PAA.min_order_qty ,
836 PAA.fixed_lot_multiple ,
837 PAA.delivery_calendar ,
838 PAA.country_of_origin_code ,
839 PAA.enable_vmi_flag ,
840 PAA.vmi_min_qty ,
841 PAA.vmi_max_qty ,
842 PAA.enable_vmi_auto_replenish_flag ,
843 PAA.vmi_replenishment_approval ,
844 PAA.consigned_from_supplier_flag ,
845 PAA.consigned_billing_cycle ,
846 PAA.consume_on_aging_flag ,
847 PAA.aging_period ,
848 PAA.replenishment_method ,
849 PAA.vmi_min_days ,
850 PAA.vmi_max_days ,
851 PAA.fixed_order_quantity ,
852 PAA.forecast_horizon)
853 = (SELECT SYSDATE ,
854 fnd_global.USER_ID ,
855 PAAGT.purchasing_unit_of_measure_dsp ,
856 PAAGT.release_generation_method ,
857 Decode(PAAGT.enable_plan_schedule_flag_dsp,'Y','Y','N') ,
858 Decode(PAAGT.enable_ship_schedule_flag_dsp,'Y','Y','N') ,
859 PAAGT.plan_schedule_type ,
860 PAAGT.ship_schedule_type ,
861 PAAGT.plan_bucket_pattern_id ,
862 PAAGT.ship_bucket_pattern_id ,
863 Decode(PAAGT.enable_autoschedule_flag_dsp,'Y','Y','N') ,
864 PAAGT.scheduler_id ,
865 PAAGT.attribute_category ,
866 PAAGT.attribute1 ,
867 PAAGT.attribute2 ,
868 PAAGT.attribute3 ,
869 PAAGT.attribute4 ,
870 PAAGT.attribute5 ,
871 PAAGT.attribute6 ,
872 PAAGT.attribute7 ,
873 PAAGT.attribute8 ,
874 PAAGT.attribute9 ,
875 PAAGT.attribute10 ,
876 PAAGT.attribute11 ,
877 PAAGT.attribute12 ,
878 PAAGT.attribute13 ,
879 PAAGT.attribute14 ,
880 PAAGT.attribute15 ,
881 PAAGT.request_id ,
882 PAAGT.program_application_id ,
883 PAAGT.program_id ,
884 PAAGT.program_update_date ,
885 Decode(PAAGT.enable_authorizations_flag_dsp,'Y','Y','N') ,
886 fnd_global.LOGIN_ID ,
887 PAAGT.price_update_tolerance_dsp ,
888 PAAGT.processing_lead_time_dsp ,
889 PAAGT.min_order_qty_dsp ,
890 PAAGT.fixed_lot_multiple_dsp ,
891 PAAGT.delivery_calendar_dsp ,
892 PAAGT.country_of_origin_code_dsp ,
893 Decode(PAAGT.enable_vmi_flag_dsp,'Y','Y','N') ,
894 PAAGT.vmi_min_qty_dsp ,
895 PAAGT.vmi_max_qty_dsp ,
896 Decode(PAAGT.enable_vmi_auto_replenish_flag,'Y','Y','N') ,
897 PAAGT.vmi_replenishment_approval ,
898 Decode(PAAGT.consigned_from_supp_flag_dsp,'Y','Y','N') ,
899 PAAGT.consigned_billing_cycle_dsp ,
900 Decode(PAAGT.consume_on_aging_flag_dsp,'Y','Y','N') ,
901 PAAGT.aging_period_dsp ,
902 PAAGT.replenishment_method ,
903 PAAGT.vmi_min_days_dsp ,
904 PAAGT.vmi_max_days_dsp ,
905 PAAGT.fixed_order_quantity_dsp ,
906 PAAGT.forecast_horizon_dsp
907 FROM po_asl_attributes_gt PAAGT ,
908 po_approved_supplier_list_gt ASLGT
909 WHERE PAAGT.user_key = ASLGT.user_key
910 AND PAAGT.asl_id = PAA.asl_id
911 AND PAAGT.using_organization_id = PAA.using_organization_id
912 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
913 AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
914 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
915 WHERE (PAA.asl_id, PAA.using_organization_id) IN
916 (SELECT PAAGT.asl_id, PAAGT.using_organization_id
917 FROM po_asl_attributes_gt PAAGT,
918 po_approved_supplier_list_gt ASLGT
919 WHERE ASLGT.user_key = PAAGT.user_key
920 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
921 AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
922 AND PAAGT.asl_id = PAA.asl_id
923 AND PAAGT.using_organization_id = PAA.using_organization_id
924 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
925
929
926 po_asl_api_pvt.log('number of rows updated in po_asl_attributes:'
927 || SQL%ROWCOUNT);
928 l_progress := 45;
930 UPDATE po_asl_documents PAD
931 SET
932 (PAD.document_line_id ,
933 PAD.last_update_date ,
934 PAD.last_updated_by ,
935 PAD.attribute_category ,
936 PAD.attribute1 ,
937 PAD.attribute2 ,
938 PAD.attribute3 ,
939 PAD.attribute4 ,
940 PAD.attribute5 ,
941 PAD.attribute6 ,
942 PAD.attribute7 ,
943 PAD.attribute8 ,
944 PAD.attribute9 ,
945 PAD.attribute10 ,
946 PAD.attribute11 ,
947 PAD.attribute12 ,
948 PAD.attribute13 ,
949 PAD.attribute14 ,
950 PAD.attribute15 ,
951 PAD.last_update_login ,
952 PAD.request_id ,
953 PAD.program_application_id ,
954 PAD.program_id ,
955 PAD.program_update_date)
956 = (SELECT PADGT.document_line_id ,
957 SYSDATE ,
958 fnd_global.USER_ID ,
959 PAD.attribute_category ,
960 PADGT.attribute1 ,
961 PADGT.attribute2 ,
962 PADGT.attribute3 ,
963 PADGT.attribute4 ,
964 PADGT.attribute5 ,
965 PADGT.attribute6 ,
966 PADGT.attribute7 ,
967 PADGT.attribute8 ,
968 PADGT.attribute9 ,
969 PADGT.attribute10 ,
970 PADGT.attribute11 ,
971 PADGT.attribute12 ,
972 PADGT.attribute13 ,
973 PADGT.attribute14 ,
974 PADGT.attribute15 ,
975 fnd_global.LOGIN_ID ,
976 PADGT.request_id ,
977 PADGT.program_application_id ,
978 PADGT.program_id ,
979 PADGT.program_update_date
980 FROM po_asl_documents_gt PADGT,
981 po_approved_supplier_list_gt ASLGT
982 WHERE PADGT.user_key = ASLGT.user_key
983 AND PADGT.asl_id = PAD.asl_id
984 AND PADGT.using_organization_id = PAD.using_organization_id
985 AND PADGT.document_header_id = PAD.document_header_id
986 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
987 AND PADGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
988 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
989 WHERE (PAD.asl_id, PAD.using_organization_id, PAD.document_header_id) IN
990 (SELECT PADGT.asl_id, PADGT.using_organization_id, PADGT.document_header_id
991 FROM po_asl_documents_gt PADGT,
992 po_approved_supplier_list_gt ASLGT
993 WHERE PADGT.user_key = ASLGT.user_key
994 AND PADGT.asl_id = PAD.asl_id
995 AND PADGT.using_organization_id = PAD.using_organization_id
996 AND PADGT.document_header_id = PAD.document_header_id
997 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
998 AND PADGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
999 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1000
1001 po_asl_api_pvt.log('number of rows updated in po_asl_documents:'
1002 || SQL%ROWCOUNT);
1003 l_progress := 50;
1004
1005 UPDATE chv_authorizations CHV
1006 SET
1007 (CHV.last_update_date ,
1008 CHV.last_updated_by ,
1009 CHV.timefence_days ,
1010 CHV.attribute_category ,
1011 CHV.attribute1 ,
1012 CHV.attribute2 ,
1013 CHV.attribute3 ,
1014 CHV.attribute4 ,
1015 CHV.attribute5 ,
1016 CHV.attribute6 ,
1017 CHV.attribute7 ,
1018 CHV.attribute8 ,
1019 CHV.attribute9 ,
1020 CHV.attribute10 ,
1021 CHV.attribute11 ,
1022 CHV.attribute12 ,
1023 CHV.attribute13 ,
1024 CHV.attribute14 ,
1025 CHV.attribute15 ,
1026 CHV.last_update_login ,
1027 CHV.request_id ,
1028 CHV.program_application_id ,
1029 CHV.program_id ,
1030 CHV.program_update_date)
1031 = (SELECT SYSDATE ,
1032 fnd_global.USER_ID ,
1033 CHVGT.timefence_days_dsp ,
1034 CHVGT.attribute_category ,
1035 CHVGT.attribute1 ,
1036 CHVGT.attribute2 ,
1037 CHVGT.attribute3 ,
1038 CHVGT.attribute4 ,
1039 CHVGT.attribute5 ,
1040 CHVGT.attribute6 ,
1041 CHVGT.attribute7 ,
1042 CHVGT.attribute8 ,
1046 CHVGT.attribute12 ,
1043 CHVGT.attribute9 ,
1044 CHVGT.attribute10 ,
1045 CHVGT.attribute11 ,
1047 CHVGT.attribute13 ,
1048 CHVGT.attribute14 ,
1049 CHVGT.attribute15 ,
1050 fnd_global.LOGIN_ID ,
1051 CHVGT.request_id ,
1052 CHVGT.program_application_id ,
1053 CHVGT.program_id ,
1054 CHVGT.program_update_date
1055 FROM chv_authorizations_gt CHVGT,
1056 po_approved_supplier_list_gt ASLGT
1057 WHERE CHVGT.user_key = ASLGT.user_key
1058 AND CHVGT.reference_id = CHV.reference_id
1059 AND CHVGT.using_organization_id = CHV.using_organization_id
1060 AND CHVGT.authorization_code = CHV.authorization_code
1061 AND CHVGT.authorization_sequence_dsp = CHV.authorization_sequence
1062 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1063 AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1064 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
1065 WHERE (CHV.reference_id, CHV.using_organization_id,
1066 CHV.authorization_code, CHV.authorization_sequence) IN
1067 (SELECT CHVGT.reference_id ,
1068 CHVGT.using_organization_id ,
1069 CHVGT.authorization_code ,
1070 CHVGT.authorization_sequence_dsp
1071 FROM chv_authorizations_gt CHVGT,
1072 po_approved_supplier_list_gt ASLGT
1073 WHERE CHVGT.user_key = ASLGT.user_key
1074 AND CHVGT.reference_id = CHV.reference_id
1075 AND CHVGT.using_organization_id = CHV.using_organization_id
1076 AND CHVGT.authorization_code = CHV.authorization_code
1077 AND CHVGT.authorization_sequence_dsp = CHV.authorization_sequence
1078 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1079 AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1080 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1081
1082 po_asl_api_pvt.log('number of rows updated in chv_authorizations:'
1083 || SQL%ROWCOUNT);
1084 l_progress := 55;
1085
1086 UPDATE po_supplier_item_capacity PSIC
1087 SET
1088 (PSIC.capacity_per_day ,
1089 PSIC.last_update_date ,
1090 PSIC.last_updated_by ,
1091 PSIC.attribute_category ,
1092 PSIC.attribute1 ,
1093 PSIC.attribute2 ,
1094 PSIC.attribute3 ,
1095 PSIC.attribute4 ,
1096 PSIC.attribute5 ,
1097 PSIC.attribute6 ,
1098 PSIC.attribute7 ,
1099 PSIC.attribute8 ,
1100 PSIC.attribute9 ,
1101 PSIC.attribute10 ,
1102 PSIC.attribute11 ,
1103 PSIC.attribute12 ,
1104 PSIC.attribute13 ,
1105 PSIC.attribute14 ,
1106 PSIC.attribute15 ,
1107 PSIC.last_update_login ,
1108 PSIC.request_id ,
1109 PSIC.program_application_id ,
1110 PSIC.program_id ,
1111 PSIC.program_update_date)
1112 = (SELECT GT.capacity_per_day_dsp ,
1113 SYSDATE ,
1114 fnd_global.USER_ID ,
1115 GT.attribute_category ,
1116 GT.attribute1 ,
1117 GT.attribute2 ,
1118 GT.attribute3 ,
1119 GT.attribute4 ,
1120 GT.attribute5 ,
1121 GT.attribute6 ,
1122 GT.attribute7 ,
1123 GT.attribute8 ,
1124 GT.attribute9 ,
1125 GT.attribute10 ,
1126 GT.attribute11 ,
1127 GT.attribute12 ,
1128 GT.attribute13 ,
1129 GT.attribute14 ,
1130 GT.attribute15 ,
1131 fnd_global.LOGIN_ID ,
1132 GT.request_id ,
1133 GT.program_application_id ,
1134 GT.program_id ,
1135 GT.program_update_date
1136 FROM po_supplier_item_capacity_gt GT,
1137 po_approved_supplier_list_gt ASLGT
1138 WHERE GT.user_key = ASLGT.user_key
1139 AND GT.asl_id = PSIC.asl_id
1140 AND GT.using_organization_id = PSIC.using_organization_id
1141 AND GT.from_date_dsp = PSIC.from_date
1142 AND Nvl(GT.to_date_dsp, SYSDATE) = Nvl(PSIC.To_Date, SYSDATE)
1143 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1144 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1145 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
1146 WHERE (PSIC.asl_id, PSIC.using_organization_id,
1147 PSIC.from_date, PSIC.To_Date) IN
1148 (SELECT GT.asl_id ,
1149 GT.using_organization_id ,
1150 GT.from_date_dsp ,
1151 GT.to_date_dsp
1152 FROM po_supplier_item_capacity_gt GT,
1153 po_approved_supplier_list_gt ASLGT
1154 WHERE GT.user_key = ASLGT.user_key
1155 AND GT.asl_id = PSIC.asl_id
1159 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1156 AND GT.using_organization_id = PSIC.using_organization_id
1157 AND GT.from_date_dsp = PSIC.from_date
1158 AND Nvl(GT.to_date_dsp, SYSDATE) = Nvl(PSIC.To_Date, SYSDATE)
1160 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1161 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1162
1163 po_asl_api_pvt.log('number of rows updated in po_supplier_item_capacity:'
1164 || SQL%ROWCOUNT);
1165 l_progress := 60;
1166
1167 UPDATE po_supplier_item_tolerance PSIT
1168 SET
1169 (PSIT.last_update_date ,
1170 PSIT.last_updated_by ,
1171 PSIT.attribute_category ,
1172 PSIT.attribute1 ,
1173 PSIT.attribute2 ,
1174 PSIT.attribute3 ,
1175 PSIT.attribute4 ,
1176 PSIT.attribute5 ,
1177 PSIT.attribute6 ,
1178 PSIT.attribute7 ,
1179 PSIT.attribute8 ,
1180 PSIT.attribute9 ,
1181 PSIT.attribute10 ,
1182 PSIT.attribute11 ,
1183 PSIT.attribute12 ,
1184 PSIT.attribute13 ,
1185 PSIT.attribute14 ,
1186 PSIT.attribute15 ,
1187 PSIT.last_update_login ,
1188 PSIT.request_id ,
1189 PSIT.program_application_id ,
1190 PSIT.program_id ,
1191 PSIT.program_update_date)
1192 = (SELECT SYSDATE ,
1193 fnd_global.USER_ID ,
1194 GT.attribute_category ,
1195 GT.attribute1 ,
1196 GT.attribute2 ,
1197 GT.attribute3 ,
1198 GT.attribute4 ,
1199 GT.attribute5 ,
1200 GT.attribute6 ,
1201 GT.attribute7 ,
1202 GT.attribute8 ,
1203 GT.attribute9 ,
1204 GT.attribute10 ,
1205 GT.attribute11 ,
1206 GT.attribute12 ,
1207 GT.attribute13 ,
1208 GT.attribute14 ,
1209 GT.attribute15 ,
1210 fnd_global.LOGIN_ID ,
1211 GT.request_id ,
1212 GT.program_application_id,
1213 GT.program_id ,
1214 GT.program_update_date
1215 FROM po_supplier_item_tolerance_gt GT,
1216 po_approved_supplier_list_gt ASLGT
1217 WHERE GT.user_key = ASLGT.user_key
1218 AND GT.asl_id = PSIT.asl_id
1219 AND GT.using_organization_id = PSIT.using_organization_id
1220 AND GT.tolerance_dsp = PSIT.tolerance
1221 AND GT.number_of_days_dsp = PSIT.number_of_days
1222 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1223 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1224 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED)
1225 WHERE (PSIT.asl_id, PSIT.using_organization_id,
1226 PSIT.tolerance, PSIT.number_of_days) IN
1227 (SELECT GT.asl_id ,
1228 GT.using_organization_id ,
1229 GT.tolerance_dsp ,
1230 GT.number_of_days_dsp
1231 FROM po_supplier_item_tolerance_gt GT,
1232 po_approved_supplier_list_gt ASLGT
1233 WHERE GT.user_key = ASLGT.user_key
1234 AND GT.asl_id = PSIT.asl_id
1235 AND GT.using_organization_id = PSIT.using_organization_id
1236 AND GT.tolerance_dsp = PSIT.tolerance
1237 AND GT.number_of_days_dsp = PSIT.number_of_days
1238 AND ASLGT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1239 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_UPDATE
1240 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1241
1242 po_asl_api_pvt.log('number of rows updated in po_supplier_item_tolerance:'
1243 || SQL%ROWCOUNT);
1244 l_progress := 65;
1245 ------------------------------UPDATE MODE ENDS--------------------------------
1246
1247 ------------------------------DELETE MODE START-------------------------------
1248 DELETE FROM po_asl_attributes PAA
1249 WHERE (asl_id, using_organization_id) IN
1250 (SELECT PAAGT.asl_id,
1251 PAAGT.using_organization_id
1252 FROM po_asl_attributes_gt PAAGT,
1253 po_approved_supplier_list_gt ASLGT
1254 WHERE ASLGT.user_key = PAAGT.user_key
1255 AND PAAGT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1256 AND PAAGT.asl_id = PAA.asl_id
1257 AND PAAGT.using_organization_id = PAA.using_organization_id
1258 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1259
1260 po_asl_api_pvt.log('number of rows deleted from po_asl_attributes:'
1261 || SQL%ROWCOUNT);
1262 l_progress := 70;
1263
1264 DELETE FROM po_asl_documents PAD
1265 WHERE (asl_id,using_organization_id,document_header_id) IN
1266 (SELECT PADGT.asl_id ,
1267 PADGT.using_organization_id ,
1268 PADGT.document_header_id
1269 FROM po_asl_documents_gt PADGT,
1270 po_approved_supplier_list_gt ASLGT
1271 WHERE ASLGT.user_key = PADGT.user_key
1272 AND PADGT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1273 AND PADGT.asl_id = PAD.asl_id
1274 AND PADGT.using_organization_id = PAD.using_organization_id
1278 po_asl_api_pvt.log('1.number of rows deleted from po_asl_documents:'
1275 AND PADGT.document_header_id = PAD.document_header_id
1276 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1277
1279 || SQL%ROWCOUNT);
1280 l_progress := 73;
1281 --Delete documents, if asl_attributes doesn't exist
1282 DELETE FROM po_asl_documents PAD
1283 WHERE NOT EXISTS
1284 (SELECT 1
1285 FROM po_asl_attributes PAA
1286 WHERE PAA.asl_id = PAD.asl_id
1287 AND PAA.using_organization_id = PAD.using_organization_id)
1288 AND EXISTS
1289 (SELECT 1,
1290 ASLGT.using_organization_id
1291 FROM po_approved_supplier_list_gt ASLGT
1292 WHERE ASLGT.asl_id = PAD.asl_id
1293 AND ASLGT.using_organization_id = PAD.using_organization_id);
1294
1295 po_asl_api_pvt.log('2.number of rows deleted from po_asl_documents:'
1296 || SQL%ROWCOUNT);
1297
1298 l_progress := 75;
1299
1300 DELETE FROM chv_authorizations CHV
1301 WHERE (reference_id, using_organization_id, authorization_code) IN
1302 (SELECT CHVGT.reference_id ,
1303 CHVGT.using_organization_id ,
1304 CHVGT.authorization_code
1305 FROM chv_authorizations_gt CHVGT,
1306 po_approved_supplier_list_gt ASLGT
1307 WHERE ASLGT.user_key = CHVGT.user_key
1308 AND CHVGT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1309 AND CHVGT.reference_id = CHV.reference_id
1310 AND CHVGT.using_organization_id = CHV.using_organization_id
1311 AND CHVGT.authorization_code = CHV.authorization_code
1312 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1313
1314 po_asl_api_pvt.log('1.number of rows deleted from chv_authorizations:'
1315 || SQL%ROWCOUNT);
1316 l_progress := 77;
1317
1318 --Delete authorizations, if asl_attributes modified with
1319 --authorizations_flag unchekced or plan schedule flag unchecked
1320 DELETE FROM chv_authorizations CHV
1321 WHERE (reference_id, using_organization_id) IN
1322 (SELECT PAA.asl_id,
1323 PAA.using_organization_id
1324 FROM po_asl_attributes PAA
1325 WHERE PAA.asl_id = CHV.reference_id
1326 AND PAA.using_organization_id = CHV.using_organization_id
1327 AND (Nvl(PAA.enable_authorizations_flag, 'N') <> 'Y'
1328 OR Nvl(PAA.enable_plan_schedule_flag , 'N') <> 'Y'))
1329 AND EXISTS
1330 (SELECT 1,
1331 ASLGT.using_organization_id
1332 FROM po_approved_supplier_list_gt ASLGT
1333 WHERE aslgt.asl_id = CHV.reference_id
1334 AND ASLGT.using_organization_id = CHV.using_organization_id);
1335
1336 po_asl_api_pvt.log('2.number of rows deleted from chv_authorizations:'
1337 || SQL%ROWCOUNT);
1338 l_progress := 79;
1339
1340 --Delete authorizations, if asl_attributes doesn't exist
1341 DELETE FROM chv_authorizations CHV
1342 WHERE NOT EXISTS
1343 (SELECT 1
1344 FROM po_asl_attributes PAA
1345 WHERE PAA.asl_id = CHV.reference_id
1346 AND PAA.using_organization_id = CHV.using_organization_id)
1347 AND EXISTS
1348 (SELECT 1,
1349 ASLGT.using_organization_id
1350 FROM po_approved_supplier_list_gt ASLGT
1351 WHERE ASLGT.asl_id = CHV.reference_id
1352 AND ASLGT.using_organization_id = CHV.using_organization_id);
1353
1354 po_asl_api_pvt.log('3.number of rows deleted from chv_authorizations:'
1355 || SQL%ROWCOUNT);
1356 l_progress := 80;
1357
1358 DELETE FROM po_supplier_item_capacity PSIC
1359 WHERE (asl_id, using_organization_id, from_date, To_Date) IN
1360 (SELECT GT.asl_id ,
1361 GT.using_organization_id ,
1362 GT.from_date_dsp ,
1363 GT.to_date_dsp
1364 FROM po_supplier_item_capacity_gt GT,
1365 po_approved_supplier_list_gt ASLGT
1366 WHERE ASLGT.user_key = GT.user_key
1367 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1368 AND GT.asl_id = PSIC.asl_id
1369 AND GT.from_date_dsp = PSIC.from_date
1370 AND Nvl(GT.to_date_dsp, SYSDATE)= Nvl(PSIC.To_Date, SYSDATE)
1371 AND GT.capacity_per_day_dsp = PSIC.capacity_per_day
1372 AND GT.using_organization_id = PSIC.using_organization_id
1373 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1374
1375 po_asl_api_pvt.log('1.number of rows deleted from po_supplier_item_capacity:'
1376 || SQL%ROWCOUNT);
1377 l_progress := 85;
1378
1379 --Delete capacity, if asl_attributes modified with enable_vmi_flag unchekced
1380 DELETE FROM po_supplier_item_capacity PSIC
1381 WHERE (asl_id, using_organization_id) IN
1382 (SELECT PAA.asl_id,
1383 PAA.using_organization_id
1384 FROM po_asl_attributes PAA
1385 WHERE PAA.asl_id = PSIC.asl_id
1386 AND PAA.using_organization_id = PSIC.using_organization_id
1387 AND PAA.using_organization_id <> -1
1388 AND Nvl(PAA.enable_vmi_flag , 'N') <> 'Y')
1389 AND EXISTS
1390 (SELECT 1,
1391 ASLGT.using_organization_id
1392 FROM po_approved_supplier_list_gt ASLGT
1393 WHERE ASLGT.asl_id = PSIC.asl_id
1394 AND ASLGT.using_organization_id = PSIC.using_organization_id);
1395
1396 l_progress := 88;
1397 po_asl_api_pvt.log('2.number of rows deleted from po_supplier_item_capacity:'
1398 || SQL%ROWCOUNT);
1399
1400 --Delete capacities, if asl_attributes doesn't exist
1404 FROM po_asl_attributes PAA
1401 DELETE FROM po_supplier_item_capacity PSIC
1402 WHERE NOT EXISTS
1403 (SELECT 1
1405 WHERE PAA.asl_id = PSIC.asl_id
1406 AND PAA.using_organization_id = PSIC.using_organization_id)
1407 AND EXISTS
1408 (SELECT 1,
1409 ASLGT.using_organization_id
1410 FROM po_approved_supplier_list_gt ASLGT
1411 WHERE ASLGT.asl_id = PSIC.asl_id
1412 AND ASLGT.using_organization_id = PSIC.using_organization_id);
1413
1414 po_asl_api_pvt.log('3.number of rows deleted from po_supplier_item_capacity:'
1415 || SQL%ROWCOUNT);
1416 l_progress := 90;
1417
1418 DELETE FROM po_supplier_item_tolerance PSIT
1419 WHERE (asl_id, using_organization_id, number_of_days, tolerance) IN
1420 (SELECT GT.asl_id ,
1421 GT.using_organization_id ,
1422 GT.number_of_days_dsp ,
1423 GT.tolerance_dsp
1424 FROM po_supplier_item_tolerance_gt GT,
1425 po_approved_supplier_list_gt ASLGT
1426 WHERE ASLGT.user_key = GT.user_key
1427 AND GT.process_action = PO_ASL_API_PUB.g_ACTION_DELETE
1428 AND GT.asl_id = PSIT.asl_id
1429 AND GT.using_organization_id = PSIT.using_organization_id
1430 AND GT.number_of_days_dsp = PSIT.number_of_days
1431 AND GT.tolerance_dsp = PSIT.tolerance
1432 AND ASLGT.process_status <> PO_ASL_API_PVT.g_STATUS_REJECTED);
1433
1434 po_asl_api_pvt.log('1.number of rows deleted from po_supplier_item_tolerance:'
1435 || SQL%ROWCOUNT);
1436 l_progress := 95;
1437
1438 --Delete tolerance, if asl_attributes modified with enable_vmi_flag unchekced
1439 DELETE FROM po_supplier_item_tolerance PSIT
1440 WHERE (asl_id, using_organization_id) IN
1441 (SELECT PAA.asl_id,
1442 PAA.using_organization_id
1443 FROM po_asl_attributes PAA
1444 WHERE PAA.asl_id = PSIT.asl_id
1445 AND PAA.using_organization_id = PSIT.using_organization_id
1446 AND PAA.using_organization_id <> -1
1447 AND Nvl(PAA.enable_vmi_flag , 'N') <> 'Y')
1448 AND EXISTS
1449 (SELECT 1,
1450 ASLGT.using_organization_id
1451 FROM po_approved_supplier_list_gt ASLGT
1452 WHERE ASLGT.asl_id = PSIT.asl_id
1453 AND ASLGT.using_organization_id = PSIT.using_organization_id);
1454
1455 l_progress := 97;
1456 po_asl_api_pvt.log('2.number of rows deleted from po_supplier_item_tolerance:'
1457 || SQL%ROWCOUNT);
1458
1459 --Delete tolerance, if asl_attributes doesn't exist
1460 DELETE FROM po_supplier_item_tolerance PSIT
1461 WHERE NOT EXISTS
1462 (SELECT 1
1463 FROM po_asl_attributes PAA
1464 WHERE PAA.asl_id = PSIT.asl_id
1465 AND PAA.using_organization_id = PSIT.using_organization_id)
1466 AND EXISTS
1467 (SELECT 1,
1468 ASLGT.using_organization_id
1469 FROM po_approved_supplier_list_gt ASLGT
1470 WHERE ASLGT.asl_id = PSIT.asl_id
1471 AND ASLGT.using_organization_id = PSIT.using_organization_id);
1472
1473 po_asl_api_pvt.log('3.number of rows deleted from po_supplier_item_tolerance:'
1474 || SQL%ROWCOUNT);
1475 l_progress := 98;
1476 ------------------------------DELETE MODE END---------------------------------
1477
1478 --Update process_status column to 'Processed' in GT table
1479 UPDATE po_approved_supplier_list_gt
1480 SET process_status = PO_ASL_API_PVT.g_STATUS_SUCCESS
1481 WHERE process_status = PO_ASL_API_PVT.g_STATUS_PENDING;
1482
1483 po_asl_api_pvt.log('status count of success from pending:' || SQL%ROWCOUNT);
1484 l_progress := 100;
1485 PO_ASL_API_PVT.Log('END ::: PO_ASL_API_PVT.process ');
1486
1487 EXCEPTION
1488
1489 WHEN OTHERS THEN
1490
1491 PO_ASL_API_PVT.Log('PO_ASL_API_PVT.process : when others exception at '
1492 || l_progress || ';' || SQLERRM );
1493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1494 x_return_msg := SQLERRM;
1495 END process;
1496
1497
1498 --------------------------------------------------------------------------------
1499 --Start of Comments
1500
1501 --Name: reject_asl_record
1502
1503 --Function:
1504 -- Reject the record by mariking the column 'PROCESS_STATUS' to 'REJECT'.
1505 -- bulk insert into po_asl_api_errors with the rejection_reason, user_key
1506 -- and session_key
1507
1508 --Parameters:
1509
1510 --IN:
1511 -- p_user_key_tbl po_tbl_number,
1512 -- p_rejection_reason po_tbl_varchar2000,
1513 -- p_entity_name po_tbl_varchar30
1514
1515 --OUT:
1516 -- x_return_status VARCHAR2
1517 -- x_return_msg VARCHAR2
1518
1519 --End of Comments
1520 --------------------------------------------------------------------------------
1521
1522 PROCEDURE reject_asl_record(
1523 p_user_key_tbl IN po_tbl_number
1524 , p_rejection_reason IN po_tbl_varchar2000
1525 , p_entity_name IN po_tbl_varchar30
1526 , p_session_key IN NUMBER
1527 , x_return_status OUT NOCOPY VARCHAR2
1528 , x_return_msg OUT NOCOPY VARCHAR2
1529 )
1530 IS
1531 l_progress NUMBER := 0;
1532
1533 BEGIN
1534 x_return_msg := NULL;
1535 PO_ASL_API_PVT.Log('START ::: reject_asl_record ');
1536 PO_ASL_API_PVT.Log(p_user_key_tbl);
1537 PO_ASL_API_PVT.Log(p_rejection_reason);
1538 PO_ASL_API_PVT.Log(p_entity_name);
1539
1540 --Reject the records in po_approved_supplier_list_gt
1541 FORALL l_index IN 1 .. p_user_key_tbl.Count
1545
1542 UPDATE po_approved_supplier_list_gt PAST
1543 SET PAST.process_status = PO_ASL_API_PVT.g_STATUS_REJECTED
1544 WHERE PAST.user_key = p_user_key_tbl(l_index);
1546 PO_ASL_API_PVT.Log('reject_asl_record update rowcount:' || SQL%ROWCOUNT);
1547 l_progress := 50;
1548
1549 --Dump all the errors into po_asl_api_errors
1550 FORALL l_index IN 1 .. p_user_key_tbl.Count
1551 INSERT INTO po_asl_api_errors (
1552 user_key ,
1553 session_key ,
1554 entity_name ,
1555 rejection_reason
1556 ) VALUES (
1557 p_user_key_tbl(l_index) ,
1558 p_session_key ,
1559 p_entity_name(l_index) ,
1560 p_rejection_reason(l_index)
1561 );
1562
1563 PO_ASL_API_PVT.Log('reject_asl_record insert rowcount:' || SQL%ROWCOUNT);
1564 l_progress := 100;
1565
1566 PO_ASL_API_PVT.Log('END ::: reject_asl_record ');
1567
1568 EXCEPTION
1569
1570 WHEN OTHERS THEN
1571
1572 PO_ASL_API_PVT.Log('reject_asl_record : WHEN OTHERS EXCEPTION at '
1573 || l_progress || ';' || SQLERRM );
1574 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1575 x_return_msg := SQLERRM;
1576
1577 END reject_asl_record;
1578
1579 --------------------------------------------------------------------------------
1580 --Start of Comments
1581
1582 --Name: log
1583
1584 --Function:
1585 -- For logging messages
1586
1587 --Parameters:
1588
1589 --IN:
1590 --p_log_message varchar2
1591
1592 --End of Comments
1593 --------------------------------------------------------------------------------
1594
1595 PROCEDURE log(
1596 p_log_message IN VARCHAR2
1597 )
1598 AS
1599 PRAGMA AUTONOMOUS_TRANSACTION;
1600
1601 l_results PO_VALIDATION_RESULTS_TYPE;
1602
1603 BEGIN
1604 --INSERT
1605 --INTO test_fnd_messages
1606 --VALUES (test_sequence.NEXTVAL , p_log_message);
1607 PO_LOG.stmt(p_module_base => 'ASL API',
1608 p_position => NULL ,
1609 p_message_text => p_log_message);
1610
1611 COMMIT;
1612 END log;
1613
1614 --------------------------------------------------------------------------------
1615 --Start of Comments
1616
1617 --Name: log
1618
1619 --Function:
1620 -- For logging po_approved_supplier_list_rec records
1621
1622 --Parameters:
1623
1624 --IN:
1625 --p_asl_rec po_approved_supplier_list_rec
1626
1627 --End of Comments
1628 --------------------------------------------------------------------------------
1629
1630 PROCEDURE log(
1631 p_asl_rec IN po_approved_supplier_list_rec
1632 )
1633 AS
1634
1635 BEGIN
1636 IF p_asl_rec.user_key IS NOT NULL THEN
1637 FOR l_index IN 1 .. p_asl_rec.user_key.Count
1638 LOOP
1639 Log('' || p_asl_rec.user_key(l_index) ||
1640 ',' || p_asl_rec.process_action(l_index) ||
1641 ',' || p_asl_rec.global_flag(l_index) ||
1642 ',' || p_asl_rec.owning_organization_id(l_index) ||
1643 ',' || p_asl_rec.owning_organization_dsp(l_index) ||
1644 ',' || p_asl_rec.vendor_business_type(l_index) ||
1645 ',' || p_asl_rec.asl_status_id(l_index) ||
1646 ',' || p_asl_rec.asl_status_dsp(l_index) ||
1647 ',' || p_asl_rec.manufacturer_id(l_index) ||
1648 ',' || p_asl_rec.manufacturer_dsp(l_index) ||
1649 ',' || p_asl_rec.vendor_id(l_index) ||
1650 ',' || p_asl_rec.vendor_dsp(l_index) ||
1651 ',' || p_asl_rec.item_id(l_index) ||
1652 ',' || p_asl_rec.item_dsp(l_index) ||
1653 ',' || p_asl_rec.category_id(l_index) ||
1654 ',' || p_asl_rec.category_dsp(l_index) ||
1655 ',' || p_asl_rec.vendor_site_id(l_index) ||
1656 ',' || p_asl_rec.vendor_site_dsp(l_index) ||
1657 ',' || p_asl_rec.primary_vendor_item(l_index) ||
1658 ',' || p_asl_rec.manufacturer_asl_id(l_index) ||
1659 ',' || p_asl_rec.manufacturer_asl_dsp(l_index) ||
1660 ',' || p_asl_rec.review_by_date(l_index) ||
1661 ',' || p_asl_rec.comments(l_index) ||
1662 ',' || p_asl_rec.attribute_category(l_index) ||
1663 ',' || p_asl_rec.attribute1(l_index) ||
1664 ',' || p_asl_rec.attribute2(l_index) ||
1665 ',' || p_asl_rec.attribute3(l_index) ||
1666 ',' || p_asl_rec.attribute4(l_index) ||
1667 ',' || p_asl_rec.attribute5(l_index) ||
1668 ',' || p_asl_rec.attribute6(l_index) ||
1669 ',' || p_asl_rec.attribute7(l_index) ||
1670 ',' || p_asl_rec.attribute8(l_index) ||
1671 ',' || p_asl_rec.attribute9(l_index) ||
1672 ',' || p_asl_rec.attribute10(l_index) ||
1673 ',' || p_asl_rec.attribute11(l_index) ||
1674 ',' || p_asl_rec.attribute12(l_index) ||
1675 ',' || p_asl_rec.attribute13(l_index) ||
1676 ',' || p_asl_rec.attribute14(l_index) ||
1677 ',' || p_asl_rec.attribute15(l_index) ||
1678 ',' || p_asl_rec.request_id(l_index) ||
1682 ',' || p_asl_rec.disable_flag(l_index) );
1679 ',' || p_asl_rec.program_application_id(l_index) ||
1680 ',' || p_asl_rec.program_id(l_index) ||
1681 ',' || p_asl_rec.program_update_date(l_index) ||
1683 END LOOP;
1684 END IF;
1685 END log;
1686
1687 --------------------------------------------------------------------------------
1688 --Start of Comments
1689
1690 --Name: log
1691
1692 --Function:
1693 -- For logging po_asl_attributes_rec records
1694
1695 --Parameters:
1696
1697 --IN:
1698 --p_attr_rec po_asl_attributes_rec
1699
1700 --End of Comments
1701 --------------------------------------------------------------------------------
1702
1703 PROCEDURE log(
1704 p_attr_rec IN po_asl_attributes_rec
1705 )
1706 AS
1707
1708 BEGIN
1709 IF p_attr_rec.user_key IS NOT NULL THEN
1710 FOR l_index IN 1 .. p_attr_rec.user_key.Count
1711 LOOP
1712 log('' || p_attr_rec.user_key(l_index) ||
1713 ',' || p_attr_rec.using_organization_id(l_index) ||
1714 ',' || p_attr_rec.using_organization_dsp(l_index) ||
1715 ',' || p_attr_rec.release_generation_method(l_index) ||
1716 ',' || p_attr_rec.release_generation_method_dsp(l_index) ||
1717 ',' || p_attr_rec.purchasing_unit_of_measure_dsp(l_index) ||
1718 ',' || p_attr_rec.enable_plan_schedule_flag_dsp(l_index) ||
1719 ',' || p_attr_rec.enable_ship_schedule_flag_dsp(l_index) ||
1720 ',' || p_attr_rec.plan_schedule_type(l_index) ||
1721 ',' || p_attr_rec.plan_schedule_type_dsp(l_index) ||
1722 ',' || p_attr_rec.ship_schedule_type(l_index) ||
1723 ',' || p_attr_rec.ship_schedule_type_dsp(l_index) ||
1724 ',' || p_attr_rec.plan_bucket_pattern_id(l_index) ||
1725 ',' || p_attr_rec.plan_bucket_pattern_dsp(l_index) ||
1726 ',' || p_attr_rec.ship_bucket_pattern_id(l_index) ||
1727 ',' || p_attr_rec.ship_bucket_pattern_dsp(l_index) ||
1728 ',' || p_attr_rec.enable_autoschedule_flag_dsp(l_index) ||
1729 ',' || p_attr_rec.scheduler_id(l_index) ||
1730 ',' || p_attr_rec.scheduler_dsp(l_index) ||
1731 ',' || p_attr_rec.enable_authorizations_flag_dsp(l_index) ||
1732 ',' || p_attr_rec.vendor_id(l_index) ||
1733 ',' || p_attr_rec.vendor_dsp(l_index) ||
1734 ',' || p_attr_rec.vendor_site_id(l_index) ||
1735 ',' || p_attr_rec.vendor_site_dsp(l_index) ||
1736 ',' || p_attr_rec.item_id(l_index) ||
1737 ',' || p_attr_rec.item_dsp(l_index) ||
1738 ',' || p_attr_rec.category_id(l_index) ||
1739 ',' || p_attr_rec.category_dsp(l_index) ||
1740 ',' || p_attr_rec.attribute_category(l_index) ||
1741 ',' || p_attr_rec.attribute1(l_index) ||
1742 ',' || p_attr_rec.attribute2(l_index) ||
1743 ',' || p_attr_rec.attribute3(l_index) ||
1744 ',' || p_attr_rec.attribute4(l_index) ||
1745 ',' || p_attr_rec.attribute5(l_index) ||
1746 ',' || p_attr_rec.attribute6(l_index) ||
1747 ',' || p_attr_rec.attribute7(l_index) ||
1748 ',' || p_attr_rec.attribute8(l_index) ||
1749 ',' || p_attr_rec.attribute9(l_index) ||
1750 ',' || p_attr_rec.attribute10(l_index) ||
1751 ',' || p_attr_rec.attribute11(l_index) ||
1752 ',' || p_attr_rec.attribute12(l_index) ||
1753 ',' || p_attr_rec.attribute13(l_index) ||
1754 ',' || p_attr_rec.attribute14(l_index) ||
1755 ',' || p_attr_rec.attribute15(l_index) ||
1756 ',' || p_attr_rec.request_id(l_index) ||
1757 ',' || p_attr_rec.program_application_id(l_index) ||
1758 ',' || p_attr_rec.program_id(l_index) ||
1759 ',' || p_attr_rec.program_update_date(l_index) ||
1760 ',' || p_attr_rec.price_update_tolerance_dsp(l_index) ||
1761 ',' || p_attr_rec.processing_lead_time_dsp(l_index) ||
1762 ',' || p_attr_rec.min_order_qty_dsp(l_index) ||
1763 ',' || p_attr_rec.fixed_lot_multiple_dsp(l_index) ||
1764 ',' || p_attr_rec.delivery_calendar_dsp(l_index) ||
1765 ',' || p_attr_rec.country_of_origin_code_dsp(l_index) ||
1766 ',' || p_attr_rec.enable_vmi_flag_dsp(l_index) ||
1767 ',' || p_attr_rec.vmi_min_qty_dsp(l_index) ||
1768 ',' || p_attr_rec.vmi_max_qty_dsp(l_index) ||
1769 ',' || p_attr_rec.enable_vmi_auto_replenish_flag(l_index) ||
1770 ',' || p_attr_rec.vmi_replenishment_approval(l_index) ||
1771 ',' || p_attr_rec.vmi_replenishment_approval_dsp(l_index) ||
1772 ',' || p_attr_rec.consigned_from_supp_flag_dsp(l_index) ||
1773 ',' || p_attr_rec.last_billing_date(l_index) ||
1774 ',' || p_attr_rec.consigned_billing_cycle_dsp(l_index) ||
1775 ',' || p_attr_rec.consume_on_aging_flag_dsp(l_index) ||
1776 ',' || p_attr_rec.aging_period_dsp(l_index) ||
1777 ',' || p_attr_rec.replenishment_method(l_index) ||
1778 ',' || p_attr_rec.replenishment_method_dsp(l_index) ||
1779 ',' || p_attr_rec.vmi_min_days_dsp(l_index) ||
1780 ',' || p_attr_rec.vmi_max_days_dsp(l_index) ||
1784 END IF;
1781 ',' || p_attr_rec.fixed_order_quantity_dsp(l_index) ||
1782 ',' || p_attr_rec.forecast_horizon_dsp(l_index));
1783 END LOOP;
1785 END log;
1786
1787 --------------------------------------------------------------------------------
1788 --Start of Comments
1789
1790 --Name: log
1791
1792 --Function:
1793 -- For logging po_asl_documents_rec records
1794
1795 --Parameters:
1796
1797 --IN:
1798 --p_doc_rec po_asl_documents_rec
1799
1800 --End of Comments
1801 --------------------------------------------------------------------------------
1802
1803 PROCEDURE log(
1804 p_doc_rec IN po_asl_documents_rec
1805 )
1806 AS
1807
1808 BEGIN
1809 IF p_doc_rec.user_key IS NOT NULL THEN
1810 FOR l_index IN 1 .. p_doc_rec.user_key.Count
1811 LOOP
1812 log('' || p_doc_rec.USER_KEY(l_index) ||
1813 ',' || p_doc_rec.PROCESS_ACTION(l_index) ||
1814 ',' || p_doc_rec.using_organization_id(l_index) ||
1815 ',' || p_doc_rec.using_organization_dsp(l_index) ||
1816 ',' || p_doc_rec.SEQUENCE_NUM(l_index) ||
1817 ',' || p_doc_rec.DOCUMENT_TYPE_CODE(l_index) ||
1818 ',' || p_doc_rec.DOCUMENT_TYPE_DSP(l_index) ||
1819 ',' || p_doc_rec.DOCUMENT_HEADER_ID(l_index) ||
1820 ',' || p_doc_rec.DOCUMENT_HEADER_DSP(l_index) ||
1821 ',' || p_doc_rec.DOCUMENT_LINE_ID(l_index) ||
1822 ',' || p_doc_rec.DOCUMENT_LINE_NUM_DSP(l_index) ||
1823 ',' || p_doc_rec.ATTRIBUTE_CATEGORY(l_index) ||
1824 ',' || p_doc_rec.ATTRIBUTE1(l_index) ||
1825 ',' || p_doc_rec.ATTRIBUTE2(l_index) ||
1826 ',' || p_doc_rec.ATTRIBUTE3(l_index) ||
1827 ',' || p_doc_rec.ATTRIBUTE4(l_index) ||
1828 ',' || p_doc_rec.ATTRIBUTE5(l_index) ||
1829 ',' || p_doc_rec.ATTRIBUTE6(l_index) ||
1830 ',' || p_doc_rec.ATTRIBUTE7(l_index) ||
1831 ',' || p_doc_rec.ATTRIBUTE8(l_index) ||
1832 ',' || p_doc_rec.ATTRIBUTE9(l_index) ||
1833 ',' || p_doc_rec.ATTRIBUTE10(l_index) ||
1834 ',' || p_doc_rec.ATTRIBUTE11(l_index) ||
1835 ',' || p_doc_rec.ATTRIBUTE12(l_index) ||
1836 ',' || p_doc_rec.ATTRIBUTE13(l_index) ||
1837 ',' || p_doc_rec.ATTRIBUTE14(l_index) ||
1838 ',' || p_doc_rec.ATTRIBUTE15(l_index) ||
1839 ',' || p_doc_rec.REQUEST_ID(l_index) ||
1840 ',' || p_doc_rec.PROGRAM_APPLICATION_ID(l_index) ||
1841 ',' || p_doc_rec.PROGRAM_ID(l_index) ||
1842 ',' || p_doc_rec.PROGRAM_UPDATE_DATE(l_index) ||
1843 ',' || p_doc_rec.ORG_ID(l_index));
1844
1845 END LOOP;
1846 END IF;
1847 END log;
1848
1849 --------------------------------------------------------------------------------
1850 --Start of Comments
1851
1852 --Name: log
1853
1854 --Function:
1855 -- For logging chv_authorizations_rec records
1856
1857 --Parameters:
1858
1859 --IN:
1860 --p_chv_rec chv_authorizations_rec
1861
1862 --End of Comments
1863 --------------------------------------------------------------------------------
1864
1865 PROCEDURE log(
1866 p_chv_rec IN chv_authorizations_rec
1867 )
1868 AS
1869
1870 BEGIN
1871 IF p_chv_rec.user_key IS NOT NULL THEN
1872 FOR l_index IN 1 .. p_chv_rec.user_key.Count
1873 LOOP
1874 log('' || p_chv_rec.USER_KEY(l_index) ||
1875 ',' || p_chv_rec.PROCESS_ACTION(l_index) ||
1876 ',' || p_chv_rec.using_organization_id(l_index) ||
1877 ',' || p_chv_rec.using_organization_dsp(l_index) ||
1878 ',' || p_chv_rec.AUTHORIZATION_CODE(l_index) ||
1879 ',' || p_chv_rec.AUTHORIZATION_CODE_DSP(l_index) ||
1880 ',' || p_chv_rec.AUTHORIZATION_SEQUENCE_DSP(l_index) ||
1881 ',' || p_chv_rec.TIMEFENCE_DAYS_DSP(l_index) ||
1882 ',' || p_chv_rec.ATTRIBUTE_CATEGORY(l_index) ||
1883 ',' || p_chv_rec.ATTRIBUTE1(l_index) ||
1884 ',' || p_chv_rec.ATTRIBUTE2(l_index) ||
1885 ',' || p_chv_rec.ATTRIBUTE3(l_index) ||
1886 ',' || p_chv_rec.ATTRIBUTE4(l_index) ||
1887 ',' || p_chv_rec.ATTRIBUTE5(l_index) ||
1888 ',' || p_chv_rec.ATTRIBUTE6(l_index) ||
1889 ',' || p_chv_rec.ATTRIBUTE7(l_index) ||
1890 ',' || p_chv_rec.ATTRIBUTE8(l_index) ||
1891 ',' || p_chv_rec.ATTRIBUTE9(l_index) ||
1892 ',' || p_chv_rec.ATTRIBUTE10(l_index) ||
1893 ',' || p_chv_rec.ATTRIBUTE11(l_index) ||
1894 ',' || p_chv_rec.ATTRIBUTE12(l_index) ||
1895 ',' || p_chv_rec.ATTRIBUTE13(l_index) ||
1896 ',' || p_chv_rec.ATTRIBUTE14(l_index) ||
1897 ',' || p_chv_rec.ATTRIBUTE15(l_index) ||
1898 ',' || p_chv_rec.REQUEST_ID(l_index) ||
1899 ',' || p_chv_rec.PROGRAM_APPLICATION_ID(l_index) ||
1900 ',' || p_chv_rec.PROGRAM_ID(l_index) ||
1901 ',' || p_chv_rec.PROGRAM_UPDATE_DATE(l_index));
1902 END LOOP;
1906 --------------------------------------------------------------------------------
1903 END IF;
1904 END log;
1905
1907 --Start of Comments
1908
1909 --Name: log
1910
1911 --Function:
1912 -- For logging po_supplier_item_capacity_rec records
1913
1914 --Parameters:
1915
1916 --IN:
1917 --p_cap_rec po_supplier_item_capacity_rec
1918
1919 --End of Comments
1920 --------------------------------------------------------------------------------
1921
1922 PROCEDURE log(
1923 p_cap_rec IN po_supplier_item_capacity_rec
1924 )
1925 AS
1926
1927 BEGIN
1928 IF p_cap_rec.user_key IS NOT NULL THEN
1929 FOR l_index IN 1 .. p_cap_rec.user_key.Count
1930 LOOP
1931 log('' || p_cap_rec.USER_KEY(l_index) ||
1932 ',' || p_cap_rec.PROCESS_ACTION(l_index) ||
1933 ',' || p_cap_rec.using_organization_id(l_index) ||
1934 ',' || p_cap_rec.using_organization_dsp(l_index) ||
1935 ',' || p_cap_rec.FROM_DATE_DSP(l_index) ||
1936 ',' || p_cap_rec.TO_DATE_DSP(l_index) ||
1937 ',' || p_cap_rec.CAPACITY_PER_DAY_DSP(l_index) ||
1938 ',' || p_cap_rec.ATTRIBUTE_CATEGORY(l_index) ||
1939 ',' || p_cap_rec.ATTRIBUTE1(l_index) ||
1940 ',' || p_cap_rec.ATTRIBUTE2(l_index) ||
1941 ',' || p_cap_rec.ATTRIBUTE3(l_index) ||
1942 ',' || p_cap_rec.ATTRIBUTE4(l_index) ||
1943 ',' || p_cap_rec.ATTRIBUTE5(l_index) ||
1944 ',' || p_cap_rec.ATTRIBUTE6(l_index) ||
1945 ',' || p_cap_rec.ATTRIBUTE7(l_index) ||
1946 ',' || p_cap_rec.ATTRIBUTE8(l_index) ||
1947 ',' || p_cap_rec.ATTRIBUTE9(l_index) ||
1948 ',' || p_cap_rec.ATTRIBUTE10(l_index) ||
1949 ',' || p_cap_rec.ATTRIBUTE11(l_index) ||
1950 ',' || p_cap_rec.ATTRIBUTE12(l_index) ||
1951 ',' || p_cap_rec.ATTRIBUTE13(l_index) ||
1952 ',' || p_cap_rec.ATTRIBUTE14(l_index) ||
1953 ',' || p_cap_rec.ATTRIBUTE15(l_index) ||
1954 ',' || p_cap_rec.REQUEST_ID(l_index) ||
1955 ',' || p_cap_rec.PROGRAM_APPLICATION_ID(l_index) ||
1956 ',' || p_cap_rec.PROGRAM_ID(l_index) ||
1957 ',' || p_cap_rec.PROGRAM_UPDATE_DATE(l_index));
1958 END LOOP;
1959 END IF;
1960 END log;
1961
1962 --------------------------------------------------------------------------------
1963 --Start of Comments
1964
1965 --Name: log
1966
1967 --Function:
1968 -- For logging po_supplier_item_tolerance_rec records
1969
1970 --Parameters:
1971
1972 --IN:
1973 --p_tol_rec po_supplier_item_tolerance_rec
1974
1975 --End of Comments
1976 --------------------------------------------------------------------------------
1977
1978 PROCEDURE log(
1979 p_tol_rec IN po_supplier_item_tolerance_rec
1980 )
1981 AS
1982
1983 BEGIN
1984 IF p_tol_rec.user_key IS NOT NULL THEN
1985 FOR l_index IN 1 .. p_tol_rec.user_key.Count
1986 LOOP
1987 log( '' || p_tol_rec.USER_KEY(l_index) ||
1988 ',' || p_tol_rec.PROCESS_ACTION(l_index) ||
1989 ',' || p_tol_rec.using_organization_id(l_index) ||
1990 ',' || p_tol_rec.using_organization_dsp(l_index) ||
1991 ',' || p_tol_rec.NUMBER_OF_DAYS_DSP(l_index) ||
1992 ',' || p_tol_rec.TOLERANCE_DSP(l_index) ||
1993 ',' || p_tol_rec.ATTRIBUTE_CATEGORY(l_index) ||
1994 ',' || p_tol_rec.ATTRIBUTE1(l_index) ||
1995 ',' || p_tol_rec.ATTRIBUTE2(l_index) ||
1996 ',' || p_tol_rec.ATTRIBUTE3(l_index) ||
1997 ',' || p_tol_rec.ATTRIBUTE4(l_index) ||
1998 ',' || p_tol_rec.ATTRIBUTE5(l_index) ||
1999 ',' || p_tol_rec.ATTRIBUTE6(l_index) ||
2000 ',' || p_tol_rec.ATTRIBUTE7(l_index) ||
2001 ',' || p_tol_rec.ATTRIBUTE8(l_index) ||
2002 ',' || p_tol_rec.ATTRIBUTE9(l_index) ||
2003 ',' || p_tol_rec.ATTRIBUTE10(l_index) ||
2004 ',' || p_tol_rec.ATTRIBUTE11(l_index) ||
2005 ',' || p_tol_rec.ATTRIBUTE12(l_index) ||
2006 ',' || p_tol_rec.ATTRIBUTE13(l_index) ||
2007 ',' || p_tol_rec.ATTRIBUTE14(l_index) ||
2008 ',' || p_tol_rec.ATTRIBUTE15(l_index) ||
2009 ',' || p_tol_rec.REQUEST_ID(l_index) ||
2010 ',' || p_tol_rec.PROGRAM_APPLICATION_ID(l_index) ||
2011 ',' || p_tol_rec.PROGRAM_ID(l_index) ||
2012 ',' || p_tol_rec.PROGRAM_UPDATE_DATE(l_index));
2013 END LOOP;
2014 END IF;
2015 END log;
2016
2017 --------------------------------------------------------------------------------
2018 --Start of Comments
2019
2020 --Name: log
2021
2022 --Function:
2023 -- For logging po_tbl_number records
2024
2025 --Parameters:
2026
2027 --IN:
2028 --tbl_number po_tbl_number
2029
2030 --End of Comments
2031 --------------------------------------------------------------------------------
2032
2033 PROCEDURE log(
2037 l_string VARCHAR2(4000);
2034 tbl_number IN po_tbl_number
2035 )
2036 AS
2038 BEGIN
2039 l_string := '';
2040 IF tbl_number IS NOT NULL THEN
2041 FOR l_index IN 1 .. tbl_number.Count
2042 LOOP
2043 l_string := l_string || tbl_number(l_index) || ',';
2044 END LOOP;
2045 END IF;
2046 Log(l_string);
2047 END log;
2048
2049 --------------------------------------------------------------------------------
2050 --Start of Comments
2051
2052 --Name: log
2053
2054 --Function:
2055 -- For logging po_tbl_varchar2000 records
2056
2057 --Parameters:
2058
2059 --IN:
2060 --tbl_varchar po_tbl_varchar2000
2061
2062 --End of Comments
2063 --------------------------------------------------------------------------------
2064
2065 PROCEDURE log(
2066 tbl_varchar IN po_tbl_varchar2000
2067 )
2068 AS
2069
2070 BEGIN
2071 IF tbl_varchar IS NOT NULL THEN
2072 FOR l_index IN 1 .. tbl_varchar.Count
2073 LOOP
2074 Log(tbl_varchar(l_index));
2075 END LOOP;
2076 END IF;
2077 END log;
2078
2079 --------------------------------------------------------------------------------
2080 --Start of Comments
2081
2082 --Name: log
2083
2084 --Function:
2085 -- For logging po_tbl_varchar30 records
2086
2087 --Parameters:
2088
2089 --IN:
2090 --tbl_varchar po_tbl_varchar30
2091
2092 --End of Comments
2093 --------------------------------------------------------------------------------
2094
2095 PROCEDURE log(
2096 tbl_varchar IN po_tbl_varchar30
2097 )
2098 AS
2099
2100 BEGIN
2101 IF tbl_varchar IS NOT NULL THEN
2102 FOR l_index IN 1 .. tbl_varchar.Count
2103 LOOP
2104 Log(tbl_varchar(l_index));
2105 END LOOP;
2106 END IF;
2107 END log;
2108
2109 END PO_ASL_API_PVT;