[Home] [Help]
3:
4: g_pkg_name CONSTANT VARCHAR2(30) := 'PO_R12_CAT_UPG_VAL_PVT';
5: g_module_prefix CONSTANT VARCHAR2(100) := 'po.plsql.' || g_pkg_name || '.';
6:
7: g_debug BOOLEAN := PO_R12_CAT_UPG_DEBUG.is_logging_enabled;
8: g_err_num NUMBER := PO_R12_CAT_UPG_PVT.g_application_err_num;
9:
10: -- Value of PO_LINE_ID and REQ_TEMPLATE_LINE_NUM if they are not
11: -- required in Attr/TLP tables.
77: AND process_code = PO_R12_CAT_UPG_PVT.g_PROCESS_CODE_NEW;
78:
79: BEGIN
80: l_progress := '010';
81: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
82:
83: OPEN load_org_ids_csr(p_batch_id);
84:
85: l_progress := '020';
93: LIMIT p_batch_size;
94:
95: l_progress := '040';
96:
97: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids.COUNT='||l_interface_header_ids.COUNT); END IF;
98:
99: IF (l_interface_header_ids.COUNT > 0) THEN
100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids(1)='||l_interface_header_ids(1)); END IF;
101: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_org_ids(1)='||l_org_ids(1)); END IF;
96:
97: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids.COUNT='||l_interface_header_ids.COUNT); END IF;
98:
99: IF (l_interface_header_ids.COUNT > 0) THEN
100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids(1)='||l_interface_header_ids(1)); END IF;
101: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_org_ids(1)='||l_org_ids(1)); END IF;
102: END IF;
103:
104: l_progress := '050';
97: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids.COUNT='||l_interface_header_ids.COUNT); END IF;
98:
99: IF (l_interface_header_ids.COUNT > 0) THEN
100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_interface_header_ids(1)='||l_interface_header_ids(1)); END IF;
101: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_org_ids(1)='||l_org_ids(1)); END IF;
102: END IF;
103:
104: l_progress := '050';
105: EXIT WHEN l_interface_header_ids.COUNT = 0;
124: ))
125: RETURNING interface_header_id, org_id
126: BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
127:
128: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
129:
130: l_progress := '070';
131: -- Mark the error records
132: FOR i IN 1 .. l_err_interface_header_ids.COUNT
213: )
214: RETURNING interface_header_id, org_id
215: BULK COLLECT INTO l_err_interface_header_ids, l_err_org_ids;
216:
217: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
218:
219: l_progress := '100';
220: -- Mark the error records
221: FOR i IN 1 .. l_err_interface_header_ids.COUNT
264: END IF;
265: l_progress := '140';
266: EXCEPTION
267: WHEN g_SNAPSHOT_TOO_OLD THEN
268: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'EXCEPTION: SNAPSHOT_TOO_OLD. Now commiting and re-opening the load_org_ids_csr'); END IF;
269:
270: -- Commit and re-open the cursor
271: l_progress := '150';
272: COMMIT;
285: CLOSE load_org_ids_csr;
286: END IF;
287:
288: l_progress := '200';
289: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
290: EXCEPTION
291: WHEN OTHERS THEN
292: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
293: IF (load_org_ids_csr%ISOPEN) THEN
288: l_progress := '200';
289: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
290: EXCEPTION
291: WHEN OTHERS THEN
292: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
293: IF (load_org_ids_csr%ISOPEN) THEN
294: CLOSE load_org_ids_csr;
295: END IF;
296: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
327:
328: i NUMBER;
329: BEGIN
330: l_progress := '010';
331: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
332:
333: FOR i IN 1 .. p_headers_rec.expiration_date.COUNT
334: LOOP
335: l_progress := '020';
357: END IF;
358: END LOOP;
359:
360: l_progress := '040';
361: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
362: EXCEPTION
363: WHEN OTHERS THEN
364: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
365: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
360: l_progress := '040';
361: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
362: EXCEPTION
363: WHEN OTHERS THEN
364: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
365: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
366: END validate_expiration_date;
367:
368: --------------------------------------------------------------------------------
396:
397: i NUMBER;
398: BEGIN
399: l_progress := '010';
400: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
401:
402: FOR i IN 1 .. p_headers_rec.agent_id.COUNT
403: LOOP
404: l_progress := '020';
424: END IF;
425: END LOOP;
426:
427: l_progress := '040';
428: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
429: EXCEPTION
430: WHEN OTHERS THEN
431: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
432: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
427: l_progress := '040';
428: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
429: EXCEPTION
430: WHEN OTHERS THEN
431: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
432: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
433: END validate_buyer;
434:
435: --------------------------------------------------------------------------------
475: i NUMBER;
476: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
477: BEGIN
478: l_progress := '010';
479: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
480:
481: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.currency_code.COUNT);
482:
483: IF (p_headers_rec.currency_code.COUNT > 0) THEN
480:
481: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.currency_code.COUNT);
482:
483: IF (p_headers_rec.currency_code.COUNT > 0) THEN
484: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.currency_code(1)='||p_headers_rec.currency_code(1)); END IF;
485: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
486: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
487: END IF;
488:
481: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.currency_code.COUNT);
482:
483: IF (p_headers_rec.currency_code.COUNT > 0) THEN
484: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.currency_code(1)='||p_headers_rec.currency_code(1)); END IF;
485: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
486: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
487: END IF;
488:
489: -- ECO bug 5584556: Add new messages
482:
483: IF (p_headers_rec.currency_code.COUNT > 0) THEN
484: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.currency_code(1)='||p_headers_rec.currency_code(1)); END IF;
485: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
486: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
487: END IF;
488:
489: -- ECO bug 5584556: Add new messages
490: FOR i IN 1 .. p_headers_rec.currency_code.COUNT
536: AND sysdate BETWEEN
537: nvl(CUR.start_date_active, sysdate-1)
538: AND nvl(CUR.end_date_active, sysdate+1));
539:
540: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
541:
542: l_progress := '030';
543: -- SQL What: Transfer from session GT table to local arrays
544: -- SQL Why : It will be used to mark the error records.
547: WHERE key = p_key
548: RETURNING num1, num2, index_char1
549: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_currency_code_list;
550:
551: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
552:
553: l_progress := '040';
554: -- Mark the error records
555: FOR i IN 1 .. l_indexes.COUNT
572: );
573: END LOOP;
574:
575: l_progress := '050';
576: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
577: EXCEPTION
578: WHEN OTHERS THEN
579: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
580: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
575: l_progress := '050';
576: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
577: EXCEPTION
578: WHEN OTHERS THEN
579: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
580: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
581: END validate_currency_code;
582:
583: --------------------------------------------------------------------------------
644: NVL(FND_PROFILE.value('USSGL_OPTION'), 'N');
645: x_temp_val BOOLEAN;
646: BEGIN
647: l_progress := '010';
648: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
649:
650: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.vendor_id.COUNT);
651:
652: ----------------------------------------------------------------------------
679: OR NOT EXISTS(SELECT 1
680: FROM PO_VENDORS PV
681: WHERE p_headers_rec.vendor_id(i) = PV.vendor_id));
682:
683: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
684:
685: l_progress := '030';
686: -- SQL What: Transfer from session GT table to local arrays
687: -- SQL Why : It will be used to mark the error records.
690: WHERE key = p_key
691: RETURNING num1, num2, num3, char1
692: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
693:
694: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
695:
696: l_progress := '040';
697: -- Mark the error records
698: FOR i IN 1 .. l_indexes.COUNT
699: LOOP
700: l_index := l_indexes(i);
701: p_headers_rec.has_errors(l_index) := 'Y';
702:
703: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_id does not exist'||', vendor_id='||l_vendor_id_list(i)); END IF;
704:
705: -- Add error message into INTERFACE_ERRORS table
706: -- ICX_CAT_SUPPLIER_REQD:
707: -- "Supplier is missing."
746: AND NOT EXISTS(SELECT 1
747: FROM PO_SUPPLIERS_VAL_V PSV
748: WHERE p_headers_rec.vendor_id(i) = PSV.vendor_id);
749:
750: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
751:
752: l_progress := '060';
753: -- SQL What: Transfer from session GT table to local arrays
754: -- SQL Why : It will be used to mark the error records.
757: WHERE key = p_key
758: RETURNING num1, num2, num3, char1
759: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_id_list, l_vendor_name_list;
760:
761: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
762:
763: l_progress := '070';
764: -- Mark the error records
765: FOR i IN 1 .. l_indexes.COUNT
766: LOOP
767: l_index := l_indexes(i);
768: p_headers_rec.has_errors(l_index) := 'Y';
769:
770: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_name='||l_vendor_name_list(i)||', vendor_id='||l_vendor_id_list(i)); END IF;
771:
772: -- Add error message into INTERFACE_ERRORS table
773: -- ICX_CAT_INVALID_SUPPLIER:
774: -- "Supplier (VALUE) is inactive or invalid."
827: WHERE p_headers_rec.vendor_site_id(i) = PVSA.vendor_site_id)
828: )
829: );
830:
831: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
832:
833: l_progress := '090';
834: -- SQL What: Transfer from session GT table to local arrays
835: -- SQL Why : It will be used to mark the error records.
839: RETURNING num1, num2, num3, char1
840: BULK COLLECT INTO l_indexes, l_interface_header_ids,
841: l_vendor_site_id_list, l_vendor_site_code_list;
842:
843: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
844:
845: l_progress := '100';
846: -- Mark the error records
847: FOR i IN 1 .. l_indexes.COUNT
848: LOOP
849: l_index := l_indexes(i);
850: p_headers_rec.has_errors(l_index) := 'Y';
851:
852: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_id='||l_vendor_site_id_list(i)); END IF;
853:
854: -- Add error message into INTERFACE_ERRORS table
855: -- ICX_CAT_SUPPLIER_SITE_REQD:
856: -- "Supplier site is missing."
895: AND NOT EXISTS(SELECT 1
896: FROM PO_SUPPLIER_SITES_VAL_V PSSV
897: WHERE p_headers_rec.vendor_site_id(i) = PSSV.vendor_site_id);
898:
899: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
900:
901: l_progress := '120';
902: -- SQL What: Transfer from session GT table to local arrays
903: -- SQL Why : It will be used to mark the error records.
907: RETURNING num1, num2, num3, char1
908: BULK COLLECT INTO l_indexes, l_interface_header_ids,
909: l_vendor_site_id_list, l_vendor_site_code_list;
910:
911: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
912:
913: l_progress := '130';
914: -- Mark the error records
915: FOR i IN 1 .. l_indexes.COUNT
916: LOOP
917: l_index := l_indexes(i);
918: p_headers_rec.has_errors(l_index) := 'Y';
919:
920: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_code='||l_vendor_site_code_list(i)||', vendor_site_id='||l_vendor_site_id_list(i)); END IF;
921:
922: -- Add error message into INTERFACE_ERRORS table
923: -- ICX_CAT_INVALID_SUPPLIER_SITE:
924: -- "Supplier site (VALUE) is not an active and valid purchasing supplier site."
962: FROM PO_VENDOR_CONTACTS PVC
963: WHERE p_headers_rec.vendor_site_id(i) = PVC.vendor_site_id
964: AND p_headers_rec.vendor_contact_id(i) = PVC.vendor_contact_id);
965:
966: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
967:
968: l_progress := '150';
969: -- SQL What: Transfer from session GT table to local arrays
970: -- SQL Why : It will be used to mark the error records.
973: WHERE key = p_key
974: RETURNING num1, num2, num3
975: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_vendor_contact_id_list;
976:
977: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
978:
979: l_progress := '160';
980: -- Mark the error records
981: FOR i IN 1 .. l_indexes.COUNT
1031: IF (x_temp_val = FALSE) THEN
1032: p_headers_rec.has_errors(i) := 'Y';
1033:
1034: l_progress := '190';
1035: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: vendor_site_code='||p_headers_rec.vendor_site_code(i)||', vendor_site_id='||p_headers_rec.vendor_site_id(i)||
1036: ', vendor_name='||p_headers_rec.vendor_name(i)||', vendor_id='||p_headers_rec.vendor_id(i)); END IF;
1037:
1038: -- Add error message into INTERFACE_ERRORS table
1039: -- ICX_CAT_SUPPLIER_SITE_CCR_INV:
1056: END LOOP;
1057: END IF; -- IF (l_federal_instance = 'Y')
1058:
1059: l_progress := '200';
1060: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1061: EXCEPTION
1062: WHEN OTHERS THEN
1063: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1064: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1059: l_progress := '200';
1060: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1061: EXCEPTION
1062: WHEN OTHERS THEN
1063: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1064: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1065: END validate_vendor_info;
1066:
1067: --------------------------------------------------------------------------------
1096: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1097: l_progress VARCHAR2(3) := '000';
1098: BEGIN
1099: l_progress := '010';
1100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1101:
1102: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_location_id='||p_location_id); END IF;
1103:
1104: -- ECO bug 5584556: id-to-name conversion for new message
1098: BEGIN
1099: l_progress := '010';
1100: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1101:
1102: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_location_id='||p_location_id); END IF;
1103:
1104: -- ECO bug 5584556: id-to-name conversion for new message
1105: -- Get the category name
1106: l_progress := '020';
1112: AND language = userenv('LANG');
1113: EXCEPTION
1114: WHEN OTHERS THEN
1115: l_progress := '030';
1116: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from HR_LOCATIONS_ALL_TL: '||SQLERRM); END IF;
1117:
1118: x_location_name := p_location_id;
1119: END;
1120: -- ECO bug 5584556: End
1119: END;
1120: -- ECO bug 5584556: End
1121:
1122: l_progress := '040';
1123: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'location_name='||x_location_name); END IF;
1124:
1125: l_progress := '050';
1126: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1127: EXCEPTION
1122: l_progress := '040';
1123: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'location_name='||x_location_name); END IF;
1124:
1125: l_progress := '050';
1126: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1127: EXCEPTION
1128: WHEN OTHERS THEN
1129: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1130: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1125: l_progress := '050';
1126: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1127: EXCEPTION
1128: WHEN OTHERS THEN
1129: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1130: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1131: END get_hr_location_name_from_id;
1132:
1133: --------------------------------------------------------------------------------
1175:
1176: l_location_name HR_LOCATIONS_ALL_TL.location_code%TYPE;
1177: BEGIN
1178: l_progress := '010';
1179: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1180:
1181: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.ship_to_location_id.COUNT);
1182:
1183: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1180:
1181: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.ship_to_location_id.COUNT);
1182:
1183: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1184: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(1)='||p_headers_rec.ship_to_location_id(1)); END IF;
1185: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1186: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1187: END IF;
1188:
1181: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.ship_to_location_id.COUNT);
1182:
1183: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1184: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(1)='||p_headers_rec.ship_to_location_id(1)); END IF;
1185: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1186: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1187: END IF;
1188:
1189: l_progress := '020';
1182:
1183: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1184: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.ship_to_location_id(1)='||p_headers_rec.ship_to_location_id(1)); END IF;
1185: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1186: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1187: END IF;
1188:
1189: l_progress := '020';
1190: -- ECO bug 5584556: Start
1210: NOT EXISTS(SELECT 1
1211: FROM HR_LOCATIONS_ALL HRLA
1212: WHERE p_headers_rec.ship_to_location_id(i) = HRLA.location_id));
1213:
1214: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1215:
1216: l_progress := '030';
1217: -- SQL What: Transfer from session GT table to local arrays
1218: -- SQL Why : It will be used to mark the error records.
1221: WHERE key = p_key
1222: RETURNING num1, num2, num3
1223: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
1224:
1225: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1226:
1227: l_progress := '040';
1228: -- Mark the error records
1229: FOR i IN 1 .. l_indexes.COUNT
1275: WHERE HRL.ship_to_site_flag = 'Y'
1276: AND p_headers_rec.ship_to_location_id(i) = HRL.location_id
1277: AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
1278:
1279: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1280:
1281: l_progress := '060';
1282: -- SQL What: Transfer from session GT table to local arrays
1283: -- SQL Why : It will be used to mark the error records.
1286: WHERE key = p_key
1287: RETURNING num1, num2, num3
1288: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_to_loc_id_list;
1289:
1290: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1291:
1292: l_progress := '070';
1293: -- Mark the error records
1294: FOR i IN 1 .. l_indexes.COUNT
1320: );
1321: END LOOP;
1322:
1323: l_progress := '090';
1324: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1325: EXCEPTION
1326: WHEN OTHERS THEN
1327: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1328: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1323: l_progress := '090';
1324: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1325: EXCEPTION
1326: WHEN OTHERS THEN
1327: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1328: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1329: END validate_ship_to_location_id;
1330:
1331: --------------------------------------------------------------------------------
1373:
1374: l_location_name HR_LOCATIONS_ALL_TL.location_code%TYPE;
1375: BEGIN
1376: l_progress := '010';
1377: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1378:
1379: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.bill_to_location_id.COUNT);
1380:
1381: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1378:
1379: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.bill_to_location_id.COUNT);
1380:
1381: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1382: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.bill_to_location_id(1)='||p_headers_rec.bill_to_location_id(1)); END IF;
1383: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1384: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1385: END IF;
1386:
1379: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.bill_to_location_id.COUNT);
1380:
1381: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1382: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.bill_to_location_id(1)='||p_headers_rec.bill_to_location_id(1)); END IF;
1383: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1384: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1385: END IF;
1386:
1387: l_progress := '020';
1380:
1381: IF (p_headers_rec.currency_code.COUNT > 0) THEN
1382: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.bill_to_location_id(1)='||p_headers_rec.bill_to_location_id(1)); END IF;
1383: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1384: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1385: END IF;
1386:
1387: l_progress := '020';
1388: -- SQL What: Bulk validate bill_to_location_id not null and against HR_LOCATIONS_ALL.
1405: NOT EXISTS(SELECT 1
1406: FROM HR_LOCATIONS_ALL HRLA
1407: WHERE p_headers_rec.bill_to_location_id(i) = HRLA.location_id));
1408:
1409: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1410:
1411: l_progress := '030';
1412: -- SQL What: Transfer from session GT table to local arrays
1413: -- SQL Why : It will be used to mark the error records.
1416: WHERE key = p_key
1417: RETURNING num1, num2, num3
1418: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
1419:
1420: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1421:
1422: l_progress := '040';
1423: -- Mark the error records
1424: FOR i IN 1 .. l_indexes.COUNT
1469: WHERE HRL.bill_to_site_flag = 'Y'
1470: AND p_headers_rec.bill_to_location_id(i) = HRL.location_id
1471: AND SYSDATE < NVL(HRL.inactive_date, SYSDATE + 1));
1472:
1473: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1474:
1475: l_progress := '060';
1476: -- SQL What: Transfer from session GT table to local arrays
1477: -- SQL Why : It will be used to mark the error records.
1480: WHERE key = p_key
1481: RETURNING num1, num2, num3
1482: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_bill_to_loc_id_list;
1483:
1484: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1485:
1486: l_progress := '070';
1487: -- Mark the error records
1488: FOR i IN 1 .. l_indexes.COUNT
1514: );
1515: END LOOP;
1516:
1517: l_progress := '090';
1518: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1519: EXCEPTION
1520: WHEN OTHERS THEN
1521: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1522: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1517: l_progress := '090';
1518: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1519: EXCEPTION
1520: WHEN OTHERS THEN
1521: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1522: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1523: END validate_bill_to_location_id;
1524:
1525: --------------------------------------------------------------------------------
1557: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
1558: l_progress VARCHAR2(3) := '000';
1559: BEGIN
1560: l_progress := '010';
1561: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1562:
1563: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_po_lookup_code='||p_po_lookup_code); END IF;
1564:
1565: -- ECO bug 5584556: id-to-name conversion for new message
1559: BEGIN
1560: l_progress := '010';
1561: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1562:
1563: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_po_lookup_code='||p_po_lookup_code); END IF;
1564:
1565: -- ECO bug 5584556: id-to-name conversion for new message
1566: -- Get the lookup meaning
1567: l_progress := '020';
1575: --AND language = userenv('LANG');
1576: EXCEPTION
1577: WHEN OTHERS THEN
1578: l_progress := '030';
1579: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from PO_LOOKUP_CODES: '||SQLERRM); END IF;
1580:
1581: x_lookup_meaning := p_po_lookup_code;
1582: END;
1583: -- ECO bug 5584556: End
1582: END;
1583: -- ECO bug 5584556: End
1584:
1585: l_progress := '040';
1586: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_lookup_meaning='||x_lookup_meaning); END IF;
1587:
1588: l_progress := '050';
1589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1590: EXCEPTION
1585: l_progress := '040';
1586: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_lookup_meaning='||x_lookup_meaning); END IF;
1587:
1588: l_progress := '050';
1589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1590: EXCEPTION
1591: WHEN OTHERS THEN
1592: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1593: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1588: l_progress := '050';
1589: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1590: EXCEPTION
1591: WHEN OTHERS THEN
1592: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1593: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1594: END get_lookup_name_from_code;
1595:
1596: --------------------------------------------------------------------------------
1638:
1639: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1640: BEGIN
1641: l_progress := '010';
1642: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1643: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- FOB_LOOKUP_CODE'); END IF;
1644:
1645: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.fob.COUNT);
1646:
1639: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1640: BEGIN
1641: l_progress := '010';
1642: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1643: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- FOB_LOOKUP_CODE'); END IF;
1644:
1645: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.fob.COUNT);
1646:
1647: IF (p_headers_rec.fob.COUNT > 0) THEN
1644:
1645: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.fob.COUNT);
1646:
1647: IF (p_headers_rec.fob.COUNT > 0) THEN
1648: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(1)='||p_headers_rec.fob(1)); END IF;
1649: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1650: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1651: END IF;
1652:
1645: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.fob.COUNT);
1646:
1647: IF (p_headers_rec.fob.COUNT > 0) THEN
1648: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(1)='||p_headers_rec.fob(1)); END IF;
1649: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1650: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1651: END IF;
1652:
1653: l_progress := '020';
1646:
1647: IF (p_headers_rec.fob.COUNT > 0) THEN
1648: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.fob(1)='||p_headers_rec.fob(1)); END IF;
1649: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1650: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1651: END IF;
1652:
1653: l_progress := '020';
1654: -- SQL What: Bulk validate fob_lookup_code not null and against PO_LOOKUP_CODES.
1673: WHERE p_headers_rec.fob(i) = PLC.lookup_code
1674: AND PLC.lookup_type = 'FOB'
1675: AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
1676:
1677: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1678:
1679: l_progress := '030';
1680: -- SQL What: Transfer from session GT table to local arrays
1681: -- SQL Why : It will be used to mark the error records.
1684: WHERE key = p_key
1685: RETURNING num1, num2, char1
1686: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_fob_lookup_code_list;
1687:
1688: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1689:
1690: l_progress := '040';
1691: -- Mark the error records
1692: FOR i IN 1 .. l_indexes.COUNT
1720: );
1721: END LOOP;
1722:
1723: l_progress := '070';
1724: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1725: EXCEPTION
1726: WHEN OTHERS THEN
1727: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1728: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1723: l_progress := '070';
1724: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1725: EXCEPTION
1726: WHEN OTHERS THEN
1727: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1728: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1729: END validate_fob_lookup_code;
1730:
1731: --------------------------------------------------------------------------------
1771: i NUMBER;
1772: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1773: BEGIN
1774: l_progress := '010';
1775: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1776: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1777:
1778: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_carrier.COUNT);
1779:
1772: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
1773: BEGIN
1774: l_progress := '010';
1775: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1776: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1777:
1778: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_carrier.COUNT);
1779:
1780: IF (p_headers_rec.freight_carrier.COUNT > 0) THEN
1777:
1778: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_carrier.COUNT);
1779:
1780: IF (p_headers_rec.freight_carrier.COUNT > 0) THEN
1781: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_carrier(1)='||p_headers_rec.freight_carrier(1)); END IF;
1782: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1783: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1784: END IF;
1785:
1778: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_carrier.COUNT);
1779:
1780: IF (p_headers_rec.freight_carrier.COUNT > 0) THEN
1781: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_carrier(1)='||p_headers_rec.freight_carrier(1)); END IF;
1782: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1783: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1784: END IF;
1785:
1786: l_progress := '020';
1779:
1780: IF (p_headers_rec.freight_carrier.COUNT > 0) THEN
1781: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_carrier(1)='||p_headers_rec.freight_carrier(1)); END IF;
1782: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1783: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1784: END IF;
1785:
1786: l_progress := '020';
1787: -- SQL What: Bulk validate ship_via_lookup_code against ORG_FREIGHT.
1809: WHERE p_headers_rec.freight_carrier(i) = OFR.freight_code
1810: AND NVL(OFR.disable_date, SYSDATE + 1) > SYSDATE
1811: AND OFR.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id);
1812:
1813: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1814:
1815: l_progress := '030';
1816: -- SQL What: Transfer from session GT table to local arrays
1817: -- SQL Why : It will be used to mark the error records.
1820: WHERE key = p_key
1821: RETURNING num1, num2, char1
1822: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_ship_via_luc_list;
1823:
1824: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1825:
1826: l_progress := '040';
1827: -- Mark the error records
1828: FOR i IN 1 .. l_indexes.COUNT
1844: );
1845: END LOOP;
1846:
1847: l_progress := '050';
1848: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1849: EXCEPTION
1850: WHEN OTHERS THEN
1851: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1852: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1847: l_progress := '050';
1848: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1849: EXCEPTION
1850: WHEN OTHERS THEN
1851: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1852: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1853: END validate_ship_via_luc;
1854:
1855: --------------------------------------------------------------------------------
1897:
1898: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1899: BEGIN
1900: l_progress := '010';
1901: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1902: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1903:
1904: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_terms.COUNT);
1905:
1898: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
1899: BEGIN
1900: l_progress := '010';
1901: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
1902: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
1903:
1904: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_terms.COUNT);
1905:
1906: IF (p_headers_rec.freight_terms.COUNT > 0) THEN
1903:
1904: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_terms.COUNT);
1905:
1906: IF (p_headers_rec.freight_terms.COUNT > 0) THEN
1907: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_terms(1)='||p_headers_rec.freight_terms(1)); END IF;
1908: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1909: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1910: END IF;
1911:
1904: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.freight_terms.COUNT);
1905:
1906: IF (p_headers_rec.freight_terms.COUNT > 0) THEN
1907: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_terms(1)='||p_headers_rec.freight_terms(1)); END IF;
1908: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1909: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1910: END IF;
1911:
1912: l_progress := '020';
1905:
1906: IF (p_headers_rec.freight_terms.COUNT > 0) THEN
1907: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.freight_terms(1)='||p_headers_rec.freight_terms(1)); END IF;
1908: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
1909: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
1910: END IF;
1911:
1912: l_progress := '020';
1913: -- SQL What: Bulk validate freight_terms_lookup_code against PO_LOOKUP_CODES.
1933: WHERE p_headers_rec.freight_terms(i) = PLC.lookup_code
1934: AND PLC.lookup_type = 'FREIGHT TERMS'
1935: AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
1936:
1937: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
1938:
1939: l_progress := '030';
1940: -- SQL What: Transfer from session GT table to local arrays
1941: -- SQL Why : It will be used to mark the error records.
1944: WHERE key = p_key
1945: RETURNING num1, num2, char1
1946: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_freight_terms_list;
1947:
1948: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
1949:
1950: l_progress := '040';
1951: -- Mark the error records
1952: FOR i IN 1 .. l_indexes.COUNT
1979: );
1980: END LOOP;
1981:
1982: l_progress := '070';
1983: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1984: EXCEPTION
1985: WHEN OTHERS THEN
1986: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1987: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1982: l_progress := '070';
1983: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
1984: EXCEPTION
1985: WHEN OTHERS THEN
1986: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
1987: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
1988: END validate_freight_terms;
1989:
1990: --------------------------------------------------------------------------------
2019: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
2020: l_progress VARCHAR2(3) := '000';
2021: BEGIN
2022: l_progress := '010';
2023: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2024:
2025: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_terms_id='||p_terms_id); END IF;
2026:
2027: -- ECO bug 5584556: id-to-name conversion for new message
2021: BEGIN
2022: l_progress := '010';
2023: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2024:
2025: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_terms_id='||p_terms_id); END IF;
2026:
2027: -- ECO bug 5584556: id-to-name conversion for new message
2028: -- Get the category name
2029: l_progress := '020';
2035: AND language = userenv('LANG');
2036: EXCEPTION
2037: WHEN OTHERS THEN
2038: l_progress := '030';
2039: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from AP_TERMS_TL: '||SQLERRM); END IF;
2040:
2041: x_terms_name := p_terms_id;
2042: END;
2043: -- ECO bug 5584556: End
2042: END;
2043: -- ECO bug 5584556: End
2044:
2045: l_progress := '040';
2046: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'terms_name='||x_terms_name); END IF;
2047:
2048: l_progress := '050';
2049: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2050: EXCEPTION
2045: l_progress := '040';
2046: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'terms_name='||x_terms_name); END IF;
2047:
2048: l_progress := '050';
2049: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2050: EXCEPTION
2051: WHEN OTHERS THEN
2052: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2053: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2048: l_progress := '050';
2049: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2050: EXCEPTION
2051: WHEN OTHERS THEN
2052: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2053: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2054: END get_ap_terms_name_from_id;
2055:
2056: --------------------------------------------------------------------------------
2098:
2099: l_terms_name AP_TERMS_TL.name%TYPE;
2100: BEGIN
2101: l_progress := '010';
2102: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2104:
2105: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.terms_id.COUNT);
2106:
2099: l_terms_name AP_TERMS_TL.name%TYPE;
2100: BEGIN
2101: l_progress := '010';
2102: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2103: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2104:
2105: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.terms_id.COUNT);
2106:
2107: IF (p_headers_rec.terms_id.COUNT > 0) THEN
2104:
2105: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.terms_id.COUNT);
2106:
2107: IF (p_headers_rec.terms_id.COUNT > 0) THEN
2108: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.terms_id(1)='||p_headers_rec.terms_id(1)); END IF;
2109: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2110: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2111: END IF;
2112:
2105: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.terms_id.COUNT);
2106:
2107: IF (p_headers_rec.terms_id.COUNT > 0) THEN
2108: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.terms_id(1)='||p_headers_rec.terms_id(1)); END IF;
2109: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2110: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2111: END IF;
2112:
2113: l_progress := '020';
2106:
2107: IF (p_headers_rec.terms_id.COUNT > 0) THEN
2108: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.terms_id(1)='||p_headers_rec.terms_id(1)); END IF;
2109: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2110: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2111: END IF;
2112:
2113: l_progress := '020';
2114: -- SQL What: Bulk validate terms_id against AP_TERMS.
2135: AND sysdate BETWEEN
2136: nvl(APT.start_date_active, sysdate - 1) AND
2137: nvl(APT.end_date_active, sysdate + 1));
2138:
2139: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2140:
2141: l_progress := '030';
2142: -- SQL What: Transfer from session GT table to local arrays
2143: -- SQL Why : It will be used to mark the error records.
2146: WHERE key = p_key
2147: RETURNING num1, num2, num3
2148: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_terms_id_list;
2149:
2150: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2151:
2152: l_progress := '040';
2153: -- Mark the error records
2154: FOR i IN 1 .. l_indexes.COUNT
2181: );
2182: END LOOP;
2183:
2184: l_progress := '070';
2185: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2186: EXCEPTION
2187: WHEN OTHERS THEN
2188: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2189: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2184: l_progress := '070';
2185: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2186: EXCEPTION
2187: WHEN OTHERS THEN
2188: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2189: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2190: END validate_terms_id;
2191:
2192: --------------------------------------------------------------------------------
2234:
2235: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
2236: BEGIN
2237: l_progress := '010';
2238: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2239: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2240:
2241: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.shipping_control.COUNT);
2242:
2235: l_lookup_meaning PO_LOOKUP_CODES.displayed_field%TYPE;
2236: BEGIN
2237: l_progress := '010';
2238: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2239: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Validating a defaulted field -- SHIP_VIA_LOOKUP_CODE (freight_carrier)'); END IF;
2240:
2241: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.shipping_control.COUNT);
2242:
2243: IF (p_headers_rec.shipping_control.COUNT > 0) THEN
2240:
2241: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.shipping_control.COUNT);
2242:
2243: IF (p_headers_rec.shipping_control.COUNT > 0) THEN
2244: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.shipping_control(1)='||p_headers_rec.shipping_control(1)); END IF;
2245: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2246: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2247: END IF;
2248:
2241: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.shipping_control.COUNT);
2242:
2243: IF (p_headers_rec.shipping_control.COUNT > 0) THEN
2244: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.shipping_control(1)='||p_headers_rec.shipping_control(1)); END IF;
2245: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2246: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2247: END IF;
2248:
2249: l_progress := '020';
2242:
2243: IF (p_headers_rec.shipping_control.COUNT > 0) THEN
2244: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.shipping_control(1)='||p_headers_rec.shipping_control(1)); END IF;
2245: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2246: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2247: END IF;
2248:
2249: l_progress := '020';
2250: -- SQL What: Bulk validate shipping_control against AP_TERMS.
2270: WHERE p_headers_rec.shipping_control(i) = PLC.lookup_code
2271: AND PLC.lookup_type = 'SHIPPING CONTROL'
2272: AND SYSDATE < NVL(PLC.inactive_date, SYSDATE + 1));
2273:
2274: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2275:
2276: l_progress := '030';
2277: -- SQL What: Transfer from session GT table to local arrays
2278: -- SQL Why : It will be used to mark the error records.
2281: WHERE key = p_key
2282: RETURNING num1, num2, char1
2283: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_shipping_control_list;
2284:
2285: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2286:
2287: l_progress := '040';
2288: -- Mark the error records
2289: FOR i IN 1 .. l_indexes.COUNT
2316: );
2317: END LOOP;
2318:
2319: l_progress := '070';
2320: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2321: EXCEPTION
2322: WHEN OTHERS THEN
2323: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2324: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2319: l_progress := '070';
2320: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2321: EXCEPTION
2322: WHEN OTHERS THEN
2323: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2324: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2325: END validate_shipping_control;
2326:
2327: -- Bug 5461235: Start
2368: i NUMBER;
2369: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2370: BEGIN
2371: l_progress := '010';
2372: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2373:
2374: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.rate_type_code.COUNT);
2375:
2376: IF (p_headers_rec.rate_type_code.COUNT > 0) THEN
2373:
2374: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.rate_type_code.COUNT);
2375:
2376: IF (p_headers_rec.rate_type_code.COUNT > 0) THEN
2377: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.rate_type_code(1)='||p_headers_rec.rate_type_code(1)); END IF;
2378: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2379: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2380: END IF;
2381:
2374: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_headers_rec.rate_type_code.COUNT);
2375:
2376: IF (p_headers_rec.rate_type_code.COUNT > 0) THEN
2377: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.rate_type_code(1)='||p_headers_rec.rate_type_code(1)); END IF;
2378: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2379: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2380: END IF;
2381:
2382: -- ECO bug 5584556: added new message
2375:
2376: IF (p_headers_rec.rate_type_code.COUNT > 0) THEN
2377: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.rate_type_code(1)='||p_headers_rec.rate_type_code(1)); END IF;
2378: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.action(1)='||p_headers_rec.action(1)); END IF;
2379: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_headers_rec.has_errors(1)='||p_headers_rec.has_errors(1)); END IF;
2380: END IF;
2381:
2382: -- ECO bug 5584556: added new message
2383: -- Add a separate message for NULL value of rate_type_code
2428: SELECT 'Rate type exists'
2429: FROM GL_DAILY_CONVERSION_TYPES_V GLDCT
2430: WHERE GLDCT.conversion_type = p_headers_rec.rate_type_code(i));
2431:
2432: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2433:
2434: l_progress := '040';
2435: -- SQL What: Transfer from session GT table to local arrays
2436: -- SQL Why : It will be used to mark the error records.
2439: WHERE key = p_key
2440: RETURNING num1, num2, char1
2441: BULK COLLECT INTO l_indexes, l_interface_header_ids, l_err_rate_type_code_list;
2442:
2443: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2444:
2445: l_progress := '050';
2446: -- Mark the error records
2447: FOR i IN 1 .. l_indexes.COUNT
2463: );
2464: END LOOP;
2465:
2466: l_progress := '060';
2467: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2468: EXCEPTION
2469: WHEN OTHERS THEN
2470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2471: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2466: l_progress := '060';
2467: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2468: EXCEPTION
2469: WHEN OTHERS THEN
2470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2471: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2472: END validate_rate_type_code;
2473: -- Bug 5461235: End
2474:
2519: l_key PO_SESSION_GT.key%TYPE;
2520: i NUMBER;
2521: BEGIN
2522: l_progress := '010';
2523: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2524:
2525: -- pick a new key from temp table which will be used in all validate logic
2526: SELECT PO_SESSION_GT_S.nextval
2527: INTO l_key
2621: p_headers_rec => p_headers_rec
2622: );
2623:
2624: l_progress := '140';
2625: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2626: EXCEPTION
2627: WHEN OTHERS THEN
2628: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2629: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2624: l_progress := '140';
2625: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2626: EXCEPTION
2627: WHEN OTHERS THEN
2628: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2629: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2630: END validate_headers;
2631:
2632: --------------------------------------------------------------------------------
2674: i NUMBER;
2675: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2676: BEGIN
2677: l_progress := '010';
2678: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2679:
2680: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2681:
2682: l_progress := '020';
2713: AND MSI.outside_operation_flag = PLT.outside_operation_flag
2714: AND TRUNC(nvl(MSI.start_date_active, sysdate))<= TRUNC(sysdate)
2715: AND TRUNC(nvl(MSI.end_date_active, sysdate)) >= TRUNC(sysdate));
2716:
2717: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2718:
2719: l_progress := '030';
2720: -- SQL What: Transfer from session GT table to local arrays
2721: -- SQL Why : It will be used to mark the error records.
2725: RETURNING num1, num2, num3, num4
2726: BULK COLLECT INTO l_indexes, l_interface_header_ids,
2727: l_interface_line_ids, l_item_id_list;
2728:
2729: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2730:
2731: l_progress := '040';
2732: -- Mark the error records
2733: FOR i IN 1 .. l_indexes.COUNT
2740: -- existing PO documents for invalid items. Keep item as it is and dont
2741: -- give any exception.
2742: --p_lines_rec.has_errors(l_index) := 'Y';
2743:
2744: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring validation error in ITEM_ID='||l_item_id_list(i)); END IF;
2745:
2746: -- Add error message into INTERFACE_ERRORS table
2747: -- PO_PDOI_INVALID_ITEM_ID:
2748: -- "ITEM ID (VALUE =VALUE) is not a valid purchasable item."
2758: -- );
2759: END LOOP;
2760:
2761: l_progress := '050';
2762: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2763: EXCEPTION
2764: WHEN OTHERS THEN
2765: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2766: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2761: l_progress := '050';
2762: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2763: EXCEPTION
2764: WHEN OTHERS THEN
2765: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2766: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2767: END validate_item;
2768:
2769:
2812: i NUMBER;
2813: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2814: BEGIN
2815: l_progress := '010';
2816: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2817:
2818: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2819:
2820: l_progress := '020';
2847: AND MSI.allow_item_desc_update_flag = 'N'
2848: AND p_lines_rec.item_description(i) <> MSI.description)));
2849: --AND create_or_update_item_flag = 'N'))); its a parameter to PDOI, For catalog migration, it would always be 'N'
2850:
2851: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
2852:
2853: l_progress := '030';
2854: -- SQL What: Transfer from session GT table to local arrays
2855: -- SQL Why : It will be used to mark the error records.
2859: RETURNING num1, num2, num3, char1
2860: BULK COLLECT INTO l_indexes, l_interface_header_ids,
2861: l_interface_line_ids, l_item_description_list;
2862:
2863: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
2864:
2865: l_progress := '040';
2866: -- Mark the error records
2867: FOR i IN 1 .. l_indexes.COUNT
2868: LOOP
2869: l_index := l_indexes(i);
2870:
2871: -- Do not give an error. In this case, ignore the description that comes from iP.
2872: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring the validation error of item description for ITEM_ID='||p_lines_rec.item_id(l_index)||'. Also ignoring the item description from interface table'); END IF;
2873: p_lines_rec.item_description(l_index) := NULL;
2874:
2875: --p_lines_rec.has_errors(l_index) := 'Y';
2876:
2887: -- );
2888: END LOOP;
2889:
2890: l_progress := '050';
2891: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2892: EXCEPTION
2893: WHEN OTHERS THEN
2894: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2895: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2890: l_progress := '050';
2891: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
2892: EXCEPTION
2893: WHEN OTHERS THEN
2894: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
2895: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
2896: END validate_item_description;
2897:
2898: --------------------------------------------------------------------------------
2940: i NUMBER;
2941: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
2942: BEGIN
2943: l_progress := '010';
2944: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
2945:
2946: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2947:
2948: IF (p_lines_rec.unit_of_measure.COUNT > 0) THEN
2945:
2946: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2947:
2948: IF (p_lines_rec.unit_of_measure.COUNT > 0) THEN
2949: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.unit_of_measure(1)='||p_lines_rec.unit_of_measure(1)); END IF;
2950: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.uom_code(1)='||p_lines_rec.uom_code(1)); END IF;
2951: END IF;
2952:
2953: -- ECO bug 5584556: Add new messages
2946: l_subscript_array := PO_R12_CAT_UPG_UTL.construct_subscript_array(p_lines_rec.item_id.COUNT);
2947:
2948: IF (p_lines_rec.unit_of_measure.COUNT > 0) THEN
2949: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.unit_of_measure(1)='||p_lines_rec.unit_of_measure(1)); END IF;
2950: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_lines_rec.uom_code(1)='||p_lines_rec.uom_code(1)); END IF;
2951: END IF;
2952:
2953: -- ECO bug 5584556: Add new messages
2954: -- uom_code must not be NULL. iP populates uom_code.
2973:
2974: l_progress := '060';
2975: p_lines_rec.has_errors(i) := 'Y';
2976:
2977: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
2978:
2979: -- Add error message into INTERFACE_ERRORS table
2980: -- ECO bug 5584556: Add new messages
2981: -- ICX_CAT_UOM_CODE_REQD:
3001: p_lines_rec.unit_of_measure(i) IS NULL AND
3002: p_lines_rec.uom_code(i) IS NOT NULL) THEN
3003:
3004: l_progress := '030';
3005: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Deriving unit_of_measure for uom_code='||p_lines_rec.uom_code(i)); END IF;
3006:
3007: BEGIN
3008: SELECT unit_of_measure
3009: INTO p_lines_rec.unit_of_measure(i)
3018: p_lines_rec.unit_of_measure(i) := NULL;
3019: END;
3020:
3021: l_progress := '040';
3022: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'unit_of_measure='||p_lines_rec.unit_of_measure(i)); END IF;
3023: END IF;
3024: END LOOP;
3025:
3026: -- unit_of_measure must not be NULL
3048:
3049: l_progress := '060';
3050: p_lines_rec.has_errors(i) := 'Y';
3051:
3052: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3053:
3054: -- Add error message into INTERFACE_ERRORS table
3055: -- ECO bug 5584556: Add new messages
3056: -- ICX_CAT_INVALID_UOM_CODE:
3104: FROM MTL_UNITS_OF_MEASURE MUOM
3105: WHERE MUOM.unit_of_measure = p_lines_rec.unit_of_measure(i)
3106: AND sysdate < NVL(disable_date, sysdate+1))));
3107:
3108: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3109:
3110: l_progress := '080';
3111: -- SQL What: Transfer from session GT table to local arrays
3112: -- SQL Why : It will be used to mark the error records.
3116: RETURNING num1, num2, num3, char1, char2
3117: BULK COLLECT INTO l_indexes, l_interface_header_ids,
3118: l_interface_line_ids, l_unit_of_measure_list, l_uom_code_list;
3119:
3120: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3121:
3122: l_progress := '090';
3123: -- Mark the error records
3124: FOR i IN 1 .. l_indexes.COUNT
3125: LOOP
3126: l_index := l_indexes(i);
3127: p_lines_rec.has_errors(l_index) := 'Y';
3128:
3129: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: unit_of_measure='||l_unit_of_measure_list(i)||', uom_code='||l_uom_code_list(i)); END IF;
3130:
3131: -- Add error message into INTERFACE_ERRORS table
3132: -- ICX_CAT_INVALID_UOM_CODE:
3133: -- "The following unit of measure codes are inactive or invalid: VALUE, VALUE"
3144: );
3145: END LOOP;
3146:
3147: l_progress := '100';
3148: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3149: EXCEPTION
3150: WHEN OTHERS THEN
3151: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3152: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3147: l_progress := '100';
3148: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3149: EXCEPTION
3150: WHEN OTHERS THEN
3151: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3152: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3153: END validate_uom;
3154:
3155: --------------------------------------------------------------------------------
3199: i NUMBER;
3200: l_subscript_array PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
3201: BEGIN
3202: l_progress := '010';
3203: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3204:
3205: -- if item id is null, then item revision has to be NULL
3206: FOR i IN 1 .. p_lines_rec.item_revision.COUNT
3207: LOOP
3216: -- If this happens, ignore the revision for a non-inventory item. Dont give any exception.
3217: --p_lines_rec.has_errors(i) := 'Y';
3218: p_lines_rec.item_revision(i) := NULL;
3219:
3220: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring item_revision for interface_lien-id='||p_lines_rec.interface_line_id(i)||' because item_id is NULL'); END IF;
3221:
3222: -- Add error message into INTERFACE_ERRORS table
3223: --PO_R12_CAT_UPG_UTL.add_fatal_error(
3224: -- p_interface_header_id => p_lines_rec.interface_header_id(i),
3262: FROM MTL_ITEM_REVISIONS MIR
3263: WHERE MIR.inventory_item_id = p_lines_rec.item_id(i)
3264: AND MIR.revision = p_lines_rec.item_revision(i));
3265:
3266: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3267:
3268: l_progress := '060';
3269: -- SQL What: Transfer from session GT table to local arrays
3270: -- SQL Why : It will be used to mark the error records.
3274: RETURNING num1, num2, num3, num4, char1
3275: BULK COLLECT INTO l_indexes, l_interface_header_ids,
3276: l_interface_line_ids, l_item_id_list, l_item_revision_list;
3277:
3278: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3279:
3280: l_progress := '070';
3281: -- Mark the error records
3282: FOR i IN 1 .. l_indexes.COUNT
3284: l_index := l_indexes(i);
3285:
3286: -- Same as item ID. Keep item as it is and dont give any exception
3287: --p_lines_rec.has_errors(l_index) := 'Y';
3288: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Ignoring the validation error of Item Revision '|| l_item_revision_list(i) ||' for ITEM_ID='||l_item_id_list(i)); END IF;
3289:
3290: -- Add error message into INTERFACE_ERRORS table
3291: -- PO_PDOI_ITEM_RELATED_INFO:
3292: -- "COLUMN_NAME (VALUE=VALUE) specified is inactive or invalid for item_id (VALUE=ITEM)."
3306: -- );
3307: END LOOP;
3308:
3309: l_progress := '080';
3310: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3311: EXCEPTION
3312: WHEN OTHERS THEN
3313: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3314: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3309: l_progress := '080';
3310: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3311: EXCEPTION
3312: WHEN OTHERS THEN
3313: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3314: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3315: END validate_item_revision;
3316:
3317: --------------------------------------------------------------------------------
3346: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3347: l_progress VARCHAR2(3) := '000';
3348: BEGIN
3349: l_progress := '010';
3350: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3351:
3352: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_category_id='||p_category_id); END IF;
3353:
3354: -- ECO bug 5584556: id-to-name conversion for new message
3348: BEGIN
3349: l_progress := '010';
3350: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3351:
3352: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_category_id='||p_category_id); END IF;
3353:
3354: -- ECO bug 5584556: id-to-name conversion for new message
3355: -- Get the category name
3356: l_progress := '020';
3361: WHERE category_id = p_category_id;
3362: EXCEPTION
3363: WHEN OTHERS THEN
3364: l_progress := '030';
3365: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting name from MTL_CATEGORIES_B_KFV: '||SQLERRM); END IF;
3366:
3367: x_category_name := p_category_id;
3368: END;
3369: -- ECO bug 5584556: End
3368: END;
3369: -- ECO bug 5584556: End
3370:
3371: l_progress := '040';
3372: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'category_name='||x_category_name); END IF;
3373:
3374: l_progress := '050';
3375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3376: EXCEPTION
3371: l_progress := '040';
3372: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'category_name='||x_category_name); END IF;
3373:
3374: l_progress := '050';
3375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3376: EXCEPTION
3377: WHEN OTHERS THEN
3378: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3379: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3374: l_progress := '050';
3375: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3376: EXCEPTION
3377: WHEN OTHERS THEN
3378: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3379: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3380: END get_category_name_from_id;
3381:
3382: --------------------------------------------------------------------------------
3414: l_log_head CONSTANT VARCHAR2(100) := g_module_prefix || l_api_name;
3415: l_progress VARCHAR2(3) := '000';
3416: BEGIN
3417: l_progress := '010';
3418: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3419:
3420: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_ip_category_id='||p_ip_category_id); END IF;
3421:
3422: x_ip_catgeory_found := 'Y';
3416: BEGIN
3417: l_progress := '010';
3418: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3419:
3420: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'p_ip_category_id='||p_ip_category_id); END IF;
3421:
3422: x_ip_catgeory_found := 'Y';
3423: -- ECO bug 5584556: id-to-name conversion for new message
3424: -- Get the category name
3432: EXCEPTION
3433: WHEN OTHERS THEN
3434: -- If the shopping category does not exist, or other cases.
3435: l_progress := '030';
3436: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Exception while getting IP ctageory_name using ICX_CAT_CATEGORIES_V view: '||SQLERRM); END IF;
3437:
3438: x_ip_catgeory_found := 'N';
3439: END;
3440: -- ECO bug 5584556: End
3445: x_ip_category_name := p_ip_category_id;
3446: x_ip_catgeory_found := 'N';
3447:
3448: l_progress := '040';
3449: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Could not find IP category name'); END IF;
3450:
3451: -- Add error message into INTERFACE_ERRORS table
3452: -- ICX_CAT_SHOP_CATEG_REQD:
3453: -- "Shopping category is missing for one or more lines."
3461: );
3462: END IF;
3463:
3464: l_progress := '050';
3465: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_ip_category_name='||x_ip_category_name); END IF;
3466:
3467: l_progress := '060';
3468: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3469: EXCEPTION
3464: l_progress := '050';
3465: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'x_ip_category_name='||x_ip_category_name); END IF;
3466:
3467: l_progress := '060';
3468: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3469: EXCEPTION
3470: WHEN OTHERS THEN
3471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3472: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3467: l_progress := '060';
3468: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3469: EXCEPTION
3470: WHEN OTHERS THEN
3471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3472: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3473: END get_ip_catgeory_name;
3474:
3475: --------------------------------------------------------------------------------
3530: l_ip_category_name ICX_CAT_CATEGORIES_V.category_name%TYPE;
3531: l_ip_catgeory_found VARCHAR2(1);
3532: BEGIN
3533: l_progress := '010';
3534: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3535:
3536: -- category_id must not be NULL
3537: FOR i IN 1 .. p_lines_rec.category_id.COUNT
3538: LOOP
3557:
3558: l_progress := '030';
3559: p_lines_rec.has_errors(i) := 'Y';
3560:
3561: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||p_lines_rec.category(i)||', category_id='||p_lines_rec.category_id(i)); END IF;
3562: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3563:
3564: -- ECO bug 5584556: Get the IP category name, that will be the token in th err msg
3565: l_progress := '035';
3558: l_progress := '030';
3559: p_lines_rec.has_errors(i) := 'Y';
3560:
3561: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||p_lines_rec.category(i)||', category_id='||p_lines_rec.category_id(i)); END IF;
3562: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3563:
3564: -- ECO bug 5584556: Get the IP category name, that will be the token in th err msg
3565: l_progress := '035';
3566: get_ip_catgeory_name
3646: AND MIC.organization_id = PO_R12_CAT_UPG_PVT.g_sys.inv_org_id
3647: AND sysdate < nvl(MCS.disable_date, sysdate+1)
3648: AND MCS.enabled_flag = 'Y');
3649:
3650: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3651:
3652: l_progress := '070';
3653: -- SQL What: Transfer from session GT table to local arrays
3654: -- SQL Why : It will be used to mark the error records.
3659: BULK COLLECT INTO l_indexes, l_interface_header_ids,
3660: l_interface_line_ids, l_item_id_list,
3661: l_category_id_list, l_category_name_list;
3662:
3663: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3664:
3665: l_progress := '080';
3666: -- Mark the error records
3667: FOR i IN 1 .. l_indexes.COUNT
3677: , x_category_name => l_category_name
3678: );
3679: -- ECO bug 5584556: End
3680:
3681: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)||', item_id='||l_item_id_list(i)); END IF;
3682:
3683: -- Add error message into INTERFACE_ERRORS table
3684: -- ICX_CAT_INVALID_CATEGORY:
3685: -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3734: AND MCSVC.category_set_id = l_category_set_id
3735: AND sysdate < nvl(MCS.disable_date, sysdate+1)
3736: AND MCS.enabled_flag = 'Y');
3737:
3738: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3739:
3740: l_progress := '110';
3741: -- SQL What: Transfer from session GT table to local arrays
3742: -- SQL Why : It will be used to mark the error records.
3747: BULK COLLECT INTO l_indexes, l_interface_header_ids,
3748: l_interface_line_ids,
3749: l_category_id_list, l_category_name_list;
3750:
3751: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3752:
3753: l_progress := '120';
3754: -- Mark the error records
3755: FOR i IN 1 .. l_indexes.COUNT
3765: , x_category_name => l_category_name
3766: );
3767: -- ECO bug 5584556: End
3768:
3769: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)); END IF;
3770:
3771: -- Add error message into INTERFACE_ERRORS table
3772: -- ICX_CAT_INVALID_CATEGORY:
3773: -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3819: WHERE MCS.category_id = p_lines_rec.category_id(i)
3820: AND sysdate < nvl(MCS.disable_date, sysdate+1)
3821: AND MCS.enabled_flag = 'Y');
3822:
3823: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows insert into GT table='||SQL%rowcount); END IF;
3824:
3825: l_progress := '150';
3826: -- SQL What: Transfer from session GT table to local arrays
3827: -- SQL Why : It will be used to mark the error records.
3832: BULK COLLECT INTO l_indexes, l_interface_header_ids,
3833: l_interface_line_ids,
3834: l_category_id_list, l_category_name_list;
3835:
3836: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of rows deleted from GT table='||SQL%rowcount); END IF;
3837:
3838: l_progress := '160';
3839: -- Mark the error records
3840: FOR i IN 1 .. l_indexes.COUNT
3852: , x_category_name => l_category_name
3853: );
3854: -- ECO bug 5584556: End
3855:
3856: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'VALIDATION ERROR: category_name='||l_category_name_list(i)||', category_id='||l_category_id_list(i)); END IF;
3857:
3858: -- Add error message into INTERFACE_ERRORS table
3859: -- ICX_CAT_INVALID_CATEGORY:
3860: -- "The following purchasing categories are inactive or invalid: VALUE, VALUE"
3872: END LOOP;
3873: END IF; -- IF x_validate_flag = 'Y'
3874:
3875: l_progress := '180';
3876: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3877: EXCEPTION
3878: WHEN OTHERS THEN
3879: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3880: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3875: l_progress := '180';
3876: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3877: EXCEPTION
3878: WHEN OTHERS THEN
3879: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3880: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3881: END validate_category;
3882:
3883: --------------------------------------------------------------------------------
3915:
3916: i NUMBER;
3917: BEGIN
3918: l_progress := '010';
3919: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
3920:
3921: FOR i IN 1 .. p_lines_rec.unit_price.COUNT
3922: LOOP
3923: l_progress := '020';
3940:
3941: l_progress := '030';
3942:
3943: p_lines_rec.has_errors(i) := 'Y';
3944: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'CREATED_BY='||p_lines_rec.created_by(i)||', ACTION='||p_lines_rec.action(i)); END IF;
3945:
3946: -- Add error message into INTERFACE_ERRORS table
3947: -- PO_PDOI_LT_ZERO:
3948: -- "COLUMN_NAME (VALUE =VALUE) specified is less than zero."
3962: END IF;
3963: END LOOP;
3964:
3965: l_progress := '040';
3966: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3967: EXCEPTION
3968: WHEN OTHERS THEN
3969: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3970: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3965: l_progress := '040';
3966: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
3967: EXCEPTION
3968: WHEN OTHERS THEN
3969: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
3970: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
3971: END validate_unit_price;
3972:
3973: --------------------------------------------------------------------------------
4017: l_key PO_SESSION_GT.key%TYPE;
4018: i NUMBER;
4019: BEGIN
4020: l_progress := '010';
4021: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4022:
4023: -- pick a new key from temp table which will be used in all validate logic
4024: SELECT PO_SESSION_GT_S.nextval
4025: INTO l_key
4080: p_lines_rec => p_lines_rec
4081: );
4082:
4083: l_progress := '080';
4084: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4085: EXCEPTION
4086: WHEN OTHERS THEN
4087: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4088: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4083: l_progress := '080';
4084: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4085: EXCEPTION
4086: WHEN OTHERS THEN
4087: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4088: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4089: END validate_lines;
4090:
4091:
4138: l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4139: l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4140: BEGIN
4141: l_progress := '010';
4142: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4143:
4144: -----------------------------------------------------------------------------
4145: -- CREATE action validations for Lines
4146: -----------------------------------------------------------------------------
4160: AND POLI.action = 'ORIGINAL'
4161: RETURNING POLI.interface_line_id, POLI.interface_header_id
4162: BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4163:
4164: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Line level='||SQL%rowcount); END IF;
4165:
4166: l_progress := '050';
4167: -- Add error message into INTERFACE_ERRORS table
4168: FOR i in 1..l_err_line_ids.COUNT
4186:
4187: -----------------------------------------------------------------------------
4188: -- CREATE action validations for Attribute Values
4189: -----------------------------------------------------------------------------
4190: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_line_create='||PO_R12_CAT_UPG_PVT.g_action_line_create); END IF;
4191: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_attr_create='||PO_R12_CAT_UPG_PVT.g_action_attr_create); END IF;
4192:
4193: -- Clear the error arrays
4194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4187: -----------------------------------------------------------------------------
4188: -- CREATE action validations for Attribute Values
4189: -----------------------------------------------------------------------------
4190: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_line_create='||PO_R12_CAT_UPG_PVT.g_action_line_create); END IF;
4191: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_attr_create='||PO_R12_CAT_UPG_PVT.g_action_attr_create); END IF;
4192:
4193: -- Clear the error arrays
4194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4195: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4190: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_line_create='||PO_R12_CAT_UPG_PVT.g_action_line_create); END IF;
4191: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_attr_create='||PO_R12_CAT_UPG_PVT.g_action_attr_create); END IF;
4192:
4193: -- Clear the error arrays
4194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4195: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4196: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4197:
4198: l_err_attr_values_ids.DELETE;
4191: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'PO_R12_CAT_UPG_PVT.g_action_attr_create='||PO_R12_CAT_UPG_PVT.g_action_attr_create); END IF;
4192:
4193: -- Clear the error arrays
4194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4195: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4196: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4197:
4198: l_err_attr_values_ids.DELETE;
4199: l_err_line_ids.DELETE;
4192:
4193: -- Clear the error arrays
4194: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4195: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4196: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4197:
4198: l_err_attr_values_ids.DELETE;
4199: l_err_line_ids.DELETE;
4200: l_err_header_ids.DELETE;
4227: )
4228: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4229: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4230:
4231: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4232:
4233: l_progress := '080';
4234: -- Add error message into INTERFACE_ERRORS table
4235: FOR i in 1..l_err_attr_values_ids.COUNT
4252: END IF;
4253:
4254: ----------------------------------------------------------------------------------
4255: -- Clear the error arrays
4256: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4257: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4258: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4259:
4260: l_err_attr_values_ids.DELETE;
4253:
4254: ----------------------------------------------------------------------------------
4255: -- Clear the error arrays
4256: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4257: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4258: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4259:
4260: l_err_attr_values_ids.DELETE;
4261: l_err_line_ids.DELETE;
4254: ----------------------------------------------------------------------------------
4255: -- Clear the error arrays
4256: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4257: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4258: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4259:
4260: l_err_attr_values_ids.DELETE;
4261: l_err_line_ids.DELETE;
4262: l_err_header_ids.DELETE;
4278: )))
4279: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4280: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4281:
4282: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4283:
4284: l_progress := '072';
4285: -- Add error message into INTERFACE_ERRORS table
4286: FOR i in 1..l_err_attr_values_ids.COUNT
4295: );
4296: END LOOP;
4297:
4298: -- Clear the error arrays
4299: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4300: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4301: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4302:
4303: l_err_attr_values_ids.DELETE;
4296: END LOOP;
4297:
4298: -- Clear the error arrays
4299: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4300: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4301: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4302:
4303: l_err_attr_values_ids.DELETE;
4304: l_err_line_ids.DELETE;
4297:
4298: -- Clear the error arrays
4299: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4300: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4301: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4302:
4303: l_err_attr_values_ids.DELETE;
4304: l_err_line_ids.DELETE;
4305: l_err_header_ids.DELETE;
4305: l_err_header_ids.DELETE;
4306:
4307: l_progress := '071';
4308: -- Clear the error arrays
4309: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4310: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4311: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4312:
4313: l_err_attr_values_ids.DELETE;
4306:
4307: l_progress := '071';
4308: -- Clear the error arrays
4309: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4310: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4311: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4312:
4313: l_err_attr_values_ids.DELETE;
4314: l_err_line_ids.DELETE;
4307: l_progress := '071';
4308: -- Clear the error arrays
4309: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4310: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4311: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4312:
4313: l_err_attr_values_ids.DELETE;
4314: l_err_line_ids.DELETE;
4315: l_err_header_ids.DELETE;
4331: )))
4332: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4333: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4334:
4335: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4336:
4337: l_progress := '072';
4338: -- Add error message into INTERFACE_ERRORS table
4339: FOR i in 1..l_err_attr_values_ids.COUNT
4348: );
4349: END LOOP;
4350:
4351: -- Clear the error arrays
4352: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4353: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4354: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4355:
4356: l_err_attr_values_ids.DELETE;
4349: END LOOP;
4350:
4351: -- Clear the error arrays
4352: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4353: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4354: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4355:
4356: l_err_attr_values_ids.DELETE;
4357: l_err_line_ids.DELETE;
4350:
4351: -- Clear the error arrays
4352: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4353: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4354: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4355:
4356: l_err_attr_values_ids.DELETE;
4357: l_err_line_ids.DELETE;
4358: l_err_header_ids.DELETE;
4376: )
4377: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4378: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4379:
4380: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4381:
4382: l_progress := '074';
4383: -- Add error message into INTERFACE_ERRORS table
4384: FOR i in 1..l_err_attr_values_ids.COUNT
4393: );
4394: END LOOP;
4395:
4396: -- Clear the error arrays
4397: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4398: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4399: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4400:
4401: l_err_attr_values_ids.DELETE;
4394: END LOOP;
4395:
4396: -- Clear the error arrays
4397: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4398: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4399: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4400:
4401: l_err_attr_values_ids.DELETE;
4402: l_err_line_ids.DELETE;
4395:
4396: -- Clear the error arrays
4397: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4398: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4399: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4400:
4401: l_err_attr_values_ids.DELETE;
4402: l_err_line_ids.DELETE;
4403: l_err_header_ids.DELETE;
4423: )
4424: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4425: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4426:
4427: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at Attr level='||SQL%rowcount); END IF;
4428:
4429: l_progress := '076';
4430: -- Add error message into INTERFACE_ERRORS table
4431: FOR i in 1..l_err_attr_values_ids.COUNT
4466: -----------------------------------------------------------------------------
4467: -- CREATE action validations for Attribute Values TLP
4468: -----------------------------------------------------------------------------
4469: -- Clear the error arrays
4470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4472: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4473: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_tlp_ids.COUNT='||l_err_attr_values_tlp_ids.COUNT); END IF;
4474:
4467: -- CREATE action validations for Attribute Values TLP
4468: -----------------------------------------------------------------------------
4469: -- Clear the error arrays
4470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4472: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4473: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_tlp_ids.COUNT='||l_err_attr_values_tlp_ids.COUNT); END IF;
4474:
4475: l_err_attr_values_ids.DELETE;
4468: -----------------------------------------------------------------------------
4469: -- Clear the error arrays
4470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4472: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4473: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_tlp_ids.COUNT='||l_err_attr_values_tlp_ids.COUNT); END IF;
4474:
4475: l_err_attr_values_ids.DELETE;
4476: l_err_attr_values_tlp_ids.DELETE;
4469: -- Clear the error arrays
4470: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_header_ids.COUNT='||l_err_header_ids.COUNT); END IF;
4471: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_line_ids.COUNT='||l_err_line_ids.COUNT); END IF;
4472: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_ids.COUNT='||l_err_attr_values_ids.COUNT); END IF;
4473: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'l_err_attr_values_tlp_ids.COUNT='||l_err_attr_values_tlp_ids.COUNT); END IF;
4474:
4475: l_err_attr_values_ids.DELETE;
4476: l_err_attr_values_tlp_ids.DELETE;
4477: l_err_line_ids.DELETE;
4547: )
4548: RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
4549: BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
4550:
4551: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid CREATE action at TLP level='||SQL%rowcount); END IF;
4552:
4553: -- TODO: Similar SQL will be used to validate Artributes TLP for Req Templates
4554:
4555: l_progress := '110';
4565: p_column_name => 'INTERFACE_ATTR_VALUES_TLP_ID'
4566: );
4567: END LOOP;
4568:
4569: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4570: EXCEPTION
4571: WHEN OTHERS THEN
4572: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4573: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4568:
4569: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4570: EXCEPTION
4571: WHEN OTHERS THEN
4572: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4573: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4574: END validate_create_action;
4575:
4576: --------------------------------------------------------------------------------
4624: l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4625: l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4626: BEGIN
4627: l_progress := '010';
4628: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4629:
4630: -----------------------------------------------------------------------------
4631: -- ADD action validations for Headers
4632: -----------------------------------------------------------------------------
4644: AND POHI.action = 'ADD'
4645: RETURNING POHI.interface_header_id
4646: BULK COLLECT INTO l_err_header_ids;
4647:
4648: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ADD action at Header level='||SQL%rowcount); END IF;
4649:
4650: l_progress := '020';
4651: -- Add error message into INTERFACE_ERRORS table
4652: FOR i in 1..l_err_header_ids.COUNT
4691: WHERE POH.po_header_id = POLI.po_header_id)))
4692: RETURNING POLI.interface_line_id, POLI.interface_header_id
4693: BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4694:
4695: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ADD action at Line level='||SQL%rowcount); END IF;
4696:
4697: l_progress := '050';
4698: -- Add error message into INTERFACE_ERRORS table
4699: FOR i in 1..l_err_line_ids.COUNT
4733: AND POAI.action = 'ORIGINAL'
4734: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
4735: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
4736:
4737: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ORIGINAL action at Attr level='||SQL%rowcount); END IF;
4738:
4739: -- TODO: Similar SQL will be used to validate Artributes for Req Templates
4740:
4741: l_progress := '080';
4777: AND POATI.action = 'ORIGINAL'
4778: RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
4779: BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
4780:
4781: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid ORIGINAL action at TLP level='||SQL%rowcount); END IF;
4782:
4783: -- TODO: Similar SQL will be used to validate Artributes TLP for Req Templates
4784:
4785: l_progress := '110';
4795: p_column_name => 'ACTION'
4796: );
4797: END LOOP;
4798:
4799: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4800: EXCEPTION
4801: WHEN OTHERS THEN
4802: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4803: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4798:
4799: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
4800: EXCEPTION
4801: WHEN OTHERS THEN
4802: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
4803: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
4804: END validate_add_action;
4805:
4806: --------------------------------------------------------------------------------
4855: l_err_attr_values_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4856: l_err_attr_values_tlp_ids PO_R12_CAT_UPG_TYPES.PO_TBL_NUMBER;
4857: BEGIN
4858: l_progress := '010';
4859: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
4860:
4861: -----------------------------------------------------------------------------
4862: -- UPDATE action validations for Headers
4863: -----------------------------------------------------------------------------
4881: WHERE POH.po_header_id = POHI.po_header_id))
4882: RETURNING POHI.interface_header_id
4883: BULK COLLECT INTO l_err_header_ids;
4884:
4885: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Header level='||SQL%rowcount); END IF;
4886:
4887: l_progress := '020';
4888: -- Add error message into INTERFACE_ERRORS table
4889: FOR i in 1..l_err_header_ids.COUNT
4928: WHERE POL.po_line_id = POLI.po_line_id))
4929: RETURNING POLI.interface_line_id, POLI.interface_header_id
4930: BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
4931:
4932: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Line level='||SQL%rowcount); END IF;
4933:
4934: l_progress := '040';
4935: -- Add error message into INTERFACE_ERRORS table
4936: FOR i in 1..l_err_line_ids.COUNT
5017: )
5018: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
5019: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
5020:
5021: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at Attr level='||SQL%rowcount); END IF;
5022:
5023: l_progress := '070';
5024: -- Add error message into INTERFACE_ERRORS table
5025: FOR i in 1..l_err_attr_values_ids.COUNT
5117: )
5118: RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
5119: BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
5120:
5121: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid UPDATE action at TLP level='||SQL%rowcount); END IF;
5122:
5123: l_progress := '100';
5124: -- Add error message into INTERFACE_ERRORS table
5125: FOR i in 1..l_err_attr_values_tlp_ids.COUNT
5133: p_column_name => 'PO_LINE_ID'
5134: );
5135: END LOOP;
5136:
5137: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5138: EXCEPTION
5139: WHEN OTHERS THEN
5140: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5141: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5136:
5137: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5138: EXCEPTION
5139: WHEN OTHERS THEN
5140: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5141: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5142: END validate_update_action;
5143:
5144: --------------------------------------------------------------------------------
5193:
5194: l_base_language FND_LANGUAGES.language_code%TYPE;
5195: BEGIN
5196: l_progress := '010';
5197: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'START'); END IF;
5198:
5199: -----------------------------------------------------------------------------
5200: -- DELETE action validations for Headers
5201: -----------------------------------------------------------------------------
5220: AND POH.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
5221: RETURNING POHI.interface_header_id
5222: BULK COLLECT INTO l_err_header_ids;
5223:
5224: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Header level='||SQL%rowcount); END IF;
5225:
5226: l_progress := '020';
5227: -- Add error message into INTERFACE_ERRORS table
5228: FOR i in 1..l_err_header_ids.COUNT
5268: AND POL.created_by <> PO_R12_CAT_UPG_PVT.g_R12_UPGRADE_USER))
5269: RETURNING POLI.interface_line_id, POLI.interface_header_id
5270: BULK COLLECT INTO l_err_line_ids, l_err_header_ids;
5271:
5272: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Line level='||SQL%rowcount); END IF;
5273:
5274: l_progress := '040';
5275: -- Add error message into INTERFACE_ERRORS table
5276: FOR i in 1..l_err_line_ids.COUNT
5314: AND POAI.action = 'DELETE'
5315: RETURNING POAI.interface_attr_values_id, POAI.interface_line_id, POAI.interface_header_id
5316: BULK COLLECT INTO l_err_attr_values_ids, l_err_line_ids, l_err_header_ids;
5317:
5318: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at Attr level='||SQL%rowcount); END IF;
5319:
5320: l_progress := '070';
5321: -- Add error message into INTERFACE_ERRORS table
5322: FOR i in 1..l_err_attr_values_ids.COUNT
5442: )
5443: RETURNING POATI.interface_attr_values_tlp_id, POATI.interface_line_id, POATI.interface_header_id
5444: BULK COLLECT INTO l_err_attr_values_tlp_ids, l_err_line_ids, l_err_header_ids;
5445:
5446: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Number of invalid DELETE action at TLP level='||SQL%rowcount); END IF;
5447:
5448: l_progress := '100';
5449: -- Add error message into INTERFACE_ERRORS table
5450: FOR i in 1..l_err_attr_values_tlp_ids.COUNT
5458: p_column_name => 'PO_LINE_ID'
5459: );
5460: END LOOP;
5461:
5462: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5463: EXCEPTION
5464: WHEN OTHERS THEN
5465: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5466: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5461:
5462: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'END'); END IF;
5463: EXCEPTION
5464: WHEN OTHERS THEN
5465: IF g_debug THEN PO_R12_CAT_UPG_DEBUG.log_stmt(l_log_head,l_progress,'Unexpected exception'); END IF;
5466: RAISE_APPLICATION_ERROR(g_err_num,l_log_head||','||l_progress || ','|| SQLERRM);
5467: END validate_delete_action;
5468:
5469: