[Home] [Help]
30: xIndexTS := 'TABLESPACE ' || gIndexTS ||
31: ' STORAGE (INITIAL 160K NEXT 160K PCTINCREASE 0)';
32: END IF;
33:
34: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
35: 'CREATE TABLE imtl_categories_kfv');
36: EXECUTE IMMEDIATE
37: 'CREATE TABLE imtl_categories_kfv( ' ||
38: ' category_id NUMBER, ' ||
47: 'CREATE INDEX imtl_categories_kfv_i1 ON ' ||
48: ' imtl_categories_kfv(category_id) ' || xIndexTS;
49:
50: xErrLoc:= 100;
51: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
52: 'CREATE TABLE imtl_category_set_valid_cats');
53: EXECUTE IMMEDIATE
54: 'CREATE TABLE imtl_category_set_valid_cats( ' ||
55: ' category_id NUMBER, ' ||
59: 'CREATE INDEX imtl_category_set_vcats_i1 ON ' ||
60: ' imtl_category_set_valid_cats(category_id) ' || xIndexTS;
61:
62: xErrLoc:= 120;
63: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
64: 'CREATE TABLE imtl_categories_tl');
65: EXECUTE IMMEDIATE
66: 'CREATE TABLE imtl_categories_tl( ' ||
67: ' category_id NUMBER, ' ||
73: 'CREATE INDEX imtl_categories_tl_i1 ON ' ||
74: ' imtl_categories_tl(category_id, language) ' || xIndexTS;
75:
76: xErrLoc:= 140;
77: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
78: 'CREATE TABLE ipo_reqexpress_headers_all');
79: EXECUTE IMMEDIATE
80: 'CREATE TABLE ipo_reqexpress_headers_all( ' ||
81: ' org_id NUMBER, ' ||
87: 'CREATE INDEX ipo_reqexpress_headers_i1 ON ' ||
88: ' ipo_reqexpress_headers_all(org_id, express_name) ' || xIndexTS;
89:
90: xErrLoc:= 160;
91: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
92: 'CREATE TABLE ipo_reqexpress_lines_all');
93: EXECUTE IMMEDIATE
94: 'CREATE TABLE ipo_reqexpress_lines_all( ' ||
95: ' org_id NUMBER, ' ||
120: 'CREATE INDEX ipo_reqexpress_lines_i1 ON ' ||
121: ' ipo_reqexpress_lines_all(org_id, express_name, sequence_num) ' || xIndexTS;
122:
123: xErrLoc:= 180;
124: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
125: 'CREATE TABLE ipo_headers_all');
126: EXECUTE IMMEDIATE
127: 'CREATE TABLE ipo_headers_all( ' ||
128: ' po_header_id NUMBER, ' ||
159: -- FPJ FPSL Extractor Changes
160: -- Add 3 columns for Amount, Allow Price Override Flag and
161: -- Not to Exceed Price
162: xErrLoc:= 200;
163: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
164: 'CREATE TABLE ipo_lines_all');
165: EXECUTE IMMEDIATE
166: 'CREATE TABLE ipo_lines_all( ' ||
167: ' po_header_id NUMBER, ' ||
193: 'CREATE INDEX ipo_lines_all_i2 ON ' ||
194: ' ipo_lines_all(po_line_id) ' || xIndexTS;
195:
196: xErrLoc:= 220;
197: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
198: 'CREATE TABLE ipo_line_locations_all');
199: EXECUTE IMMEDIATE
200: 'CREATE TABLE ipo_line_locations_all( ' ||
201: ' line_location_id NUMBER, ' ||
210: 'CREATE INDEX ipo_line_locations_all_i2 ON ' ||
211: ' ipo_line_locations_all(po_line_id) ' || xIndexTS;
212:
213: xErrLoc:= 240;
214: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
215: 'CREATE TABLE ipo_quotation_approvals_all');
216: EXECUTE IMMEDIATE
217: 'CREATE TABLE ipo_quotation_approvals_all( ' ||
218: ' line_location_id NUMBER, ' ||
230: --No need of using po_line_types which is view on po_line_types_b and
231: --po_line_types_tl
232: -- Add 2 columns for order_type_lookup_code and purchase_basis
233: xErrLoc:= 260;
234: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
235: 'CREATE TABLE ipo_line_types_b');
236: EXECUTE IMMEDIATE
237: 'CREATE TABLE ipo_line_types_b( ' ||
238: ' line_type_id NUMBER, ' ||
244: 'CREATE INDEX ipo_line_types_b_i1 ON ' ||
245: ' ipo_line_types_b(line_type_id) ' || xIndexTS;
246:
247: xErrLoc:= 280;
248: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
249: 'CREATE TABLE ipo_ga_org_assignments');
250: EXECUTE IMMEDIATE
251: 'CREATE TABLE ipo_ga_org_assignments( ' ||
252: ' po_header_id NUMBER, ' ||
259: 'CREATE INDEX ipo_ga_org_assignments_i1 ON ' ||
260: ' ipo_ga_org_assignments(po_header_id) ' || xIndexTS;
261:
262: xErrLoc:= 300;
263: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
264: 'CREATE TABLE ipo_asl_attributes');
265: EXECUTE IMMEDIATE
266: 'CREATE TABLE ipo_asl_attributes( ' ||
267: ' asl_id NUMBER, ' ||
271: 'CREATE INDEX ipo_asl_attributes_i1 ON ' ||
272: ' ipo_asl_attributes(asl_id) ' || xIndexTS;
273:
274: xErrLoc:= 320;
275: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
276: 'CREATE TABLE ipo_approved_supplier_list');
277: EXECUTE IMMEDIATE
278: 'CREATE TABLE ipo_approved_supplier_list( ' ||
279: ' asl_id NUMBER, ' ||
291: 'CREATE INDEX ipo_asl_i1 ON ' ||
292: ' ipo_approved_supplier_list(asl_id) ' || xIndexTS;
293:
294: xErrLoc:= 340;
295: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
296: 'CREATE TABLE ipo_asl_status_rules');
297: EXECUTE IMMEDIATE
298: 'CREATE TABLE ipo_asl_status_rules( ' ||
299: ' status_id NUMBER, ' ||
304: 'CREATE INDEX ipo_asl_status_rules_i1 ON ' ||
305: ' ipo_asl_status_rules(status_id) ' || xIndexTS;
306:
307: xErrLoc:= 360;
308: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
309: 'CREATE TABLE ipo_vendors');
310: EXECUTE IMMEDIATE
311: 'CREATE TABLE ipo_vendors( ' ||
312: ' vendor_id NUMBER, ' ||
320: 'CREATE INDEX ipo_vendors_i2 ON ' ||
321: ' ipo_vendors(vendor_name) ' || xIndexTS;
322:
323: xErrLoc:= 380;
324: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
325: 'CREATE TABLE ipo_vendor_sites_all');
326: EXECUTE IMMEDIATE
327: 'CREATE TABLE ipo_vendor_sites_all( ' ||
328: ' vendor_site_id NUMBER, ' ||
337: 'CREATE INDEX ipo_vendor_sites_all_i2 ON ' ||
338: ' ipo_vendor_sites_all(vendor_site_code) ' || xIndexTS;
339:
340: xErrLoc:= 400;
341: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
342: 'CREATE TABLE imtl_system_items_kfv');
343: EXECUTE IMMEDIATE
344: 'CREATE TABLE imtl_system_items_kfv( ' ||
345: ' inventory_item_id NUMBER, ' ||
360: ' imtl_system_items_kfv(inventory_item_id, '||
361: ' organization_id) ' || xIndexTS;
362:
363: xErrLoc:= 420;
364: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
365: 'CREATE TABLE imtl_system_items_tl');
366: EXECUTE IMMEDIATE
367: 'CREATE TABLE imtl_system_items_tl( ' ||
368: ' inventory_item_id NUMBER, ' ||
376: ' imtl_system_items_tl(inventory_item_id, '||
377: ' organization_id, language) ' || xIndexTS;
378:
379: xErrLoc:= 440;
380: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
381: 'CREATE TABLE imtl_item_categories');
382: EXECUTE IMMEDIATE
383: 'CREATE TABLE imtl_item_categories( ' ||
384: ' inventory_item_id NUMBER, ' ||
391: ' imtl_item_categories(inventory_item_id, category_id, '||
392: ' organization_id) ' || xIndexTS;
393:
394: xErrLoc:= 460;
395: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
396: 'CREATE TABLE ifinancials_system_params_all');
397: EXECUTE IMMEDIATE
398: 'CREATE TABLE ifinancials_system_params_all( ' ||
399: ' org_id NUMBER, ' ||
400: ' inventory_organization_id NUMBER, ' ||
401: ' set_of_books_id NUMBER) ' || xTableTS;
402:
403: xErrLoc:= 480;
404: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
405: 'CREATE TABLE ipo_system_parameters_all');
406: EXECUTE IMMEDIATE
407: 'CREATE TABLE ipo_system_parameters_all( ' ||
408: ' org_id NUMBER, ' ||
409: ' default_rate_type VARCHAR2(25), ' ||
410: ' last_update_date DATE) ' || xTableTS; -- Bug# 2945205 : pcreddy
411:
412: xErrLoc:= 500;
413: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
414: 'CREATE TABLE igl_sets_of_books');
415: EXECUTE IMMEDIATE
416: 'CREATE TABLE igl_sets_of_books( ' ||
417: ' set_of_books_id NUMBER, ' ||
420: xErrLoc:= 600;
421: EXCEPTION
422: WHEN OTHERS THEN
423: ROLLBACK;
424: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTables-'||
425: xErrLoc||' '||SQLERRM);
426: raise ICX_POR_EXT_UTL.gException;
427: END createTables;
428:
422: WHEN OTHERS THEN
423: ROLLBACK;
424: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTables-'||
425: xErrLoc||' '||SQLERRM);
426: raise ICX_POR_EXT_UTL.gException;
427: END createTables;
428:
429: PROCEDURE setCommitSize(pCommitSize NUMBER)
430: IS
458: xErrLoc:= 50;
459: gTestMode := 'Y';
460:
461: xErrLoc:= 80;
462: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Prepare...');
463:
464: xErrLoc:= 100;
465: IF NVL(pCreateTables, 'Y') = 'Y' THEN
466: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
462: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL, 'Prepare...');
463:
464: xErrLoc:= 100;
465: IF NVL(pCreateTables, 'Y') = 'Y' THEN
466: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
467: 'Prepare: Create testing tables');
468: createTables;
469: END IF;
470:
488: xErrLoc:= 300;
489: EXCEPTION
490: WHEN OTHERS THEN
491: ROLLBACK;
492: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.prepare-'||
493: xErrLoc||' '||SQLERRM);
494: raise ICX_POR_EXT_UTL.gException;
495: END prepare;
496:
490: WHEN OTHERS THEN
491: ROLLBACK;
492: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.prepare-'||
493: xErrLoc||' '||SQLERRM);
494: raise ICX_POR_EXT_UTL.gException;
495: END prepare;
496:
497: --------------------------------------------------------------
498: -- Test Cleanup Procedures --
503: xErrLoc PLS_INTEGER:= 100;
504:
505: BEGIN
506: xErrLoc:= 100;
507: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
508: 'DROP TABLE imtl_categories_kfv');
509: EXECUTE IMMEDIATE
510: 'DROP TABLE imtl_categories_kfv';
511:
509: EXECUTE IMMEDIATE
510: 'DROP TABLE imtl_categories_kfv';
511:
512: xErrLoc:= 120;
513: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
514: 'DROP TABLE imtl_category_set_valid_cats');
515: EXECUTE IMMEDIATE
516: 'DROP TABLE imtl_category_set_valid_cats';
517:
515: EXECUTE IMMEDIATE
516: 'DROP TABLE imtl_category_set_valid_cats';
517:
518: xErrLoc:= 140;
519: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
520: 'DROP TABLE imtl_categories_tl');
521: EXECUTE IMMEDIATE
522: 'DROP TABLE imtl_categories_tl';
523:
521: EXECUTE IMMEDIATE
522: 'DROP TABLE imtl_categories_tl';
523:
524: xErrLoc:= 160;
525: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
526: 'DROP TABLE ipo_reqexpress_headers_all');
527: EXECUTE IMMEDIATE
528: 'DROP TABLE ipo_reqexpress_headers_all';
529:
527: EXECUTE IMMEDIATE
528: 'DROP TABLE ipo_reqexpress_headers_all';
529:
530: xErrLoc:= 180;
531: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
532: 'DROP TABLE ipo_reqexpress_lines_all');
533: EXECUTE IMMEDIATE
534: 'DROP TABLE ipo_reqexpress_lines_all';
535:
533: EXECUTE IMMEDIATE
534: 'DROP TABLE ipo_reqexpress_lines_all';
535:
536: xErrLoc:= 200;
537: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
538: 'DROP TABLE ipo_headers_all');
539: EXECUTE IMMEDIATE
540: 'DROP TABLE ipo_headers_all';
541:
539: EXECUTE IMMEDIATE
540: 'DROP TABLE ipo_headers_all';
541:
542: xErrLoc:= 220;
543: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
544: 'DROP TABLE ipo_lines_all');
545: EXECUTE IMMEDIATE
546: 'DROP TABLE ipo_lines_all';
547:
545: EXECUTE IMMEDIATE
546: 'DROP TABLE ipo_lines_all';
547:
548: xErrLoc:= 240;
549: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
550: 'DROP TABLE ipo_line_locations_all');
551: EXECUTE IMMEDIATE
552: 'DROP TABLE ipo_line_locations_all';
553:
551: EXECUTE IMMEDIATE
552: 'DROP TABLE ipo_line_locations_all';
553:
554: xErrLoc:= 260;
555: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
556: 'DROP TABLE ipo_quotation_approvals_all');
557: EXECUTE IMMEDIATE
558: 'DROP TABLE ipo_quotation_approvals_all';
559:
557: EXECUTE IMMEDIATE
558: 'DROP TABLE ipo_quotation_approvals_all';
559:
560: xErrLoc:= 280;
561: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
562: 'DROP TABLE ipo_line_types_b');
563: EXECUTE IMMEDIATE
564: 'DROP TABLE ipo_line_types_b';
565: xErrLoc:= 300;
563: EXECUTE IMMEDIATE
564: 'DROP TABLE ipo_line_types_b';
565: xErrLoc:= 300;
566:
567: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
568: 'DROP TABLE ipo_ga_org_assignments');
569: EXECUTE IMMEDIATE
570: 'DROP TABLE ipo_ga_org_assignments';
571:
569: EXECUTE IMMEDIATE
570: 'DROP TABLE ipo_ga_org_assignments';
571:
572: xErrLoc:= 320;
573: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
574: 'DROP TABLE ipo_asl_attributes');
575: EXECUTE IMMEDIATE
576: 'DROP TABLE ipo_asl_attributes';
577:
575: EXECUTE IMMEDIATE
576: 'DROP TABLE ipo_asl_attributes';
577:
578: xErrLoc:= 340;
579: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
580: 'DROP TABLE ipo_approved_supplier_list');
581: EXECUTE IMMEDIATE
582: 'DROP TABLE ipo_approved_supplier_list';
583:
581: EXECUTE IMMEDIATE
582: 'DROP TABLE ipo_approved_supplier_list';
583:
584: xErrLoc:= 360;
585: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
586: 'DROP TABLE ipo_asl_status_rules');
587: EXECUTE IMMEDIATE
588: 'DROP TABLE ipo_asl_status_rules';
589:
587: EXECUTE IMMEDIATE
588: 'DROP TABLE ipo_asl_status_rules';
589:
590: xErrLoc:= 380;
591: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
592: 'DROP TABLE ipo_vendors');
593: EXECUTE IMMEDIATE
594: 'DROP TABLE ipo_vendors';
595:
593: EXECUTE IMMEDIATE
594: 'DROP TABLE ipo_vendors';
595:
596: xErrLoc:= 400;
597: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
598: 'DROP TABLE ipo_vendor_sites_all');
599: EXECUTE IMMEDIATE
600: 'DROP TABLE ipo_vendor_sites_all';
601:
599: EXECUTE IMMEDIATE
600: 'DROP TABLE ipo_vendor_sites_all';
601:
602: xErrLoc:= 420;
603: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
604: 'DROP TABLE imtl_system_items_kfv');
605: EXECUTE IMMEDIATE
606: 'DROP TABLE imtl_system_items_kfv';
607:
605: EXECUTE IMMEDIATE
606: 'DROP TABLE imtl_system_items_kfv';
607:
608: xErrLoc:= 440;
609: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
610: 'DROP TABLE imtl_system_items_tl');
611: EXECUTE IMMEDIATE
612: 'DROP TABLE imtl_system_items_tl';
613:
611: EXECUTE IMMEDIATE
612: 'DROP TABLE imtl_system_items_tl';
613:
614: xErrLoc:= 460;
615: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
616: 'DROP TABLE imtl_item_categories');
617: EXECUTE IMMEDIATE
618: 'DROP TABLE imtl_item_categories';
619:
617: EXECUTE IMMEDIATE
618: 'DROP TABLE imtl_item_categories';
619:
620: xErrLoc:= 480;
621: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
622: 'DROP TABLE ifinancials_system_params_all');
623: EXECUTE IMMEDIATE
624: 'DROP TABLE ifinancials_system_params_all';
625:
623: EXECUTE IMMEDIATE
624: 'DROP TABLE ifinancials_system_params_all';
625:
626: xErrLoc:= 500;
627: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
628: 'DROP TABLE ipo_system_parameters_all');
629: EXECUTE IMMEDIATE
630: 'DROP TABLE ipo_system_parameters_all';
631:
629: EXECUTE IMMEDIATE
630: 'DROP TABLE ipo_system_parameters_all';
631:
632: xErrLoc:= 520;
633: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
634: 'DROP TABLE igl_sets_of_books');
635: EXECUTE IMMEDIATE
636: 'DROP TABLE igl_sets_of_books';
637:
638: xErrLoc:= 600;
639: EXCEPTION
640: WHEN OTHERS THEN
641: ROLLBACK;
642: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.dropTables-'||
643: xErrLoc||' '||SQLERRM);
644: raise ICX_POR_EXT_UTL.gException;
645: END dropTables;
646:
640: WHEN OTHERS THEN
641: ROLLBACK;
642: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.dropTables-'||
643: xErrLoc||' '||SQLERRM);
644: raise ICX_POR_EXT_UTL.gException;
645: END dropTables;
646:
647: -- Clean up data for unit test
648: PROCEDURE cleanupData
654: xRowCount PLS_INTEGER := 0;
655:
656: BEGIN
657: xErrLoc:= 100;
658: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
659: 'Delete test data from icx_cat_categories_tl');
660: xErrLoc:= 120;
661: OPEN cTestRows FOR
662: SELECT ROWID FROM icx_cat_categories_tl
674: FORALL i IN 1..xRowIds.COUNT
675: DELETE icx_cat_categories_tl
676: WHERE rowid = xRowIds(i);
677: COMMIT;
678: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
679: 'Processed records: ' || xRowCount);
680: END LOOP;
681: CLOSE cTestRows;
682:
680: END LOOP;
681: CLOSE cTestRows;
682:
683: xErrLoc:= 200;
684: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
685: 'Delete test data from icx_por_category_data_sources');
686: xErrLoc:= 220;
687: xRowCount := 0;
688: OPEN cTestRows FOR
701: FORALL i IN 1..xRowIds.COUNT
702: DELETE icx_por_category_data_sources
703: WHERE rowid = xRowIds(i);
704: COMMIT;
705: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
706: 'Processed records: ' || xRowCount);
707: END LOOP;
708: CLOSE cTestRows;
709:
707: END LOOP;
708: CLOSE cTestRows;
709:
710: xErrLoc:= 300;
711: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
712: 'Delete test data from icx_por_category_order_map');
713: xErrLoc:= 320;
714: xRowCount := 0;
715: OPEN cTestRows FOR
728: FORALL i IN 1..xRowIds.COUNT
729: DELETE icx_por_category_order_map
730: WHERE rowid = xRowIds(i);
731: COMMIT;
732: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
733: 'Processed records: ' || xRowCount);
734: END LOOP;
735: CLOSE cTestRows;
736:
734: END LOOP;
735: CLOSE cTestRows;
736:
737: xErrLoc:= 400;
738: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
739: 'Delete test data from icx_cat_items_b');
740: xErrLoc:= 420;
741: xRowCount := 0;
742: OPEN cTestRows FOR
755: FORALL i IN 1..xRowIds.COUNT
756: DELETE icx_cat_items_b
757: WHERE rowid = xRowIds(i);
758: COMMIT;
759: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
760: 'Processed records: ' || xRowCount);
761: END LOOP;
762: CLOSE cTestRows;
763:
761: END LOOP;
762: CLOSE cTestRows;
763:
764: xErrLoc:= 500;
765: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
766: 'Delete test data from icx_cat_items_tlp');
767: xErrLoc:= 520;
768: xRowCount := 0;
769: OPEN cTestRows FOR
782: FORALL i IN 1..xRowIds.COUNT
783: DELETE icx_cat_items_tlp
784: WHERE rowid = xRowIds(i);
785: COMMIT;
786: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
787: 'Processed records: ' || xRowCount);
788: END LOOP;
789: CLOSE cTestRows;
790:
788: END LOOP;
789: CLOSE cTestRows;
790:
791: xErrLoc:= 600;
792: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
793: 'Delete test data from icx_cat_category_items');
794: xErrLoc:= 620;
795: xRowCount := 0;
796: OPEN cTestRows FOR
809: FORALL i IN 1..xRowIds.COUNT
810: DELETE icx_cat_category_items
811: WHERE rowid = xRowIds(i);
812: COMMIT;
813: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
814: 'Processed records: ' || xRowCount);
815: END LOOP;
816: CLOSE cTestRows;
817:
815: END LOOP;
816: CLOSE cTestRows;
817:
818: xErrLoc:= 700;
819: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
820: 'Delete test data from icx_cat_ext_items_tlp');
821: xErrLoc:= 720;
822: xRowCount := 0;
823: OPEN cTestRows FOR
836: FORALL i IN 1..xRowIds.COUNT
837: DELETE icx_cat_ext_items_tlp
838: WHERE rowid = xRowIds(i);
839: COMMIT;
840: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
841: 'Processed records: ' || xRowCount);
842: END LOOP;
843: CLOSE cTestRows;
844:
842: END LOOP;
843: CLOSE cTestRows;
844:
845: xErrLoc:= 800;
846: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
847: 'Delete test data from icx_cat_item_prices');
848: xErrLoc:= 820;
849: xRowCount := 0;
850: OPEN cTestRows FOR
863: FORALL i IN 1..xRowIds.COUNT
864: DELETE icx_cat_item_prices
865: WHERE rowid = xRowIds(i);
866: COMMIT;
867: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.INFO_LEVEL,
868: 'Processed records: ' || xRowCount);
869: END LOOP;
870: CLOSE cTestRows;
871:
876: IF (cTestRows%ISOPEN) THEN
877: CLOSE cTestRows;
878: END IF;
879: ROLLBACK;
880: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanupData-'||
881: xErrLoc||' '||SQLERRM);
882: raise ICX_POR_EXT_UTL.gException;
883: END cleanupData;
884:
878: END IF;
879: ROLLBACK;
880: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanupData-'||
881: xErrLoc||' '||SQLERRM);
882: raise ICX_POR_EXT_UTL.gException;
883: END cleanupData;
884:
885: -- Cleanup unit testing
886: PROCEDURE cleanup
889: xReturnErr varchar2(2000);
890:
891: BEGIN
892: xErrLoc:= 100;
893: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
894: 'Drop tables');
895: dropTables;
896: xErrLoc:= 200;
897: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
893: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
894: 'Drop tables');
895: dropTables;
896: xErrLoc:= 200;
897: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.MUST_LEVEL,
898: 'Clean up data for unit test');
899: cleanupData;
900:
901: EXCEPTION
900:
901: EXCEPTION
902: WHEN OTHERS THEN
903: ROLLBACK;
904: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanup-'||
905: xErrLoc||' '||SQLERRM);
906: raise ICX_POR_EXT_UTL.gException;
907: END cleanup;
908:
902: WHEN OTHERS THEN
903: ROLLBACK;
904: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.cleanup-'||
905: xErrLoc||' '||SQLERRM);
906: raise ICX_POR_EXT_UTL.gException;
907: END cleanup;
908:
909: --------------------------------------------------------------
910: -- Classification Test Utilities --
921: xErrLoc PLS_INTEGER:= 100;
922:
923: BEGIN
924: xErrLoc:= 50;
925: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
926: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
927: 'createCategory(p_category_id: ' || p_category_id ||
928: ', p_concatenated_segments: ' || p_concatenated_segments ||
929: ', p_description: ' || p_description ||
922:
923: BEGIN
924: xErrLoc:= 50;
925: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
926: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
927: 'createCategory(p_category_id: ' || p_category_id ||
928: ', p_concatenated_segments: ' || p_concatenated_segments ||
929: ', p_description: ' || p_description ||
930: ', p_web_status: ' || p_web_status ||
991:
992: EXCEPTION
993: WHEN OTHERS THEN
994: ROLLBACK;
995: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createCategory-'||
996: xErrLoc||' '||SQLERRM);
997: raise ICX_POR_EXT_UTL.gException;
998: END createCategory;
999:
993: WHEN OTHERS THEN
994: ROLLBACK;
995: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createCategory-'||
996: xErrLoc||' '||SQLERRM);
997: raise ICX_POR_EXT_UTL.gException;
998: END createCategory;
999:
1000: -- Update a category
1001: PROCEDURE updateCategory(p_category_id IN NUMBER,
1009: xErrLoc PLS_INTEGER:= 100;
1010:
1011: BEGIN
1012: xErrLoc:= 50;
1013: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1014: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1015: 'updateCategory(p_category_id: ' || p_category_id ||
1016: ', p_concatenated_segments: ' || p_concatenated_segments ||
1017: ', p_description: ' || p_description ||
1010:
1011: BEGIN
1012: xErrLoc:= 50;
1013: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1014: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1015: 'updateCategory(p_category_id: ' || p_category_id ||
1016: ', p_concatenated_segments: ' || p_concatenated_segments ||
1017: ', p_description: ' || p_description ||
1018: ', p_web_status: ' || p_web_status ||
1060:
1061: EXCEPTION
1062: WHEN OTHERS THEN
1063: ROLLBACK;
1064: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateCategory-'||
1065: xErrLoc||' '||SQLERRM);
1066: raise ICX_POR_EXT_UTL.gException;
1067: END updateCategory;
1068:
1062: WHEN OTHERS THEN
1063: ROLLBACK;
1064: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateCategory-'||
1065: xErrLoc||' '||SQLERRM);
1066: raise ICX_POR_EXT_UTL.gException;
1067: END updateCategory;
1068:
1069: -- Translate a category
1070: PROCEDURE translateCategory(p_category_id IN NUMBER,
1075: xExist PLS_INTEGER:= 0;
1076:
1077: BEGIN
1078: xErrLoc:= 50;
1079: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1080: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1081: 'translateCategory(p_category_id: ' || p_category_id ||
1082: ', p_description: ' || p_description ||
1083: ', p_language: ' || p_language || ')');
1076:
1077: BEGIN
1078: xErrLoc:= 50;
1079: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1080: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1081: 'translateCategory(p_category_id: ' || p_category_id ||
1082: ', p_description: ' || p_description ||
1083: ', p_language: ' || p_language || ')');
1084: END IF;
1093: WHERE installed_flag = 'I'
1094: AND language_code = p_language);
1095: EXCEPTION
1096: WHEN NO_DATA_FOUND THEN
1097: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1098: 'Not an installed language: ' || p_language);
1099: RETURN;
1100: END;
1101:
1134:
1135: EXCEPTION
1136: when others then
1137: ROLLBACK;
1138: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateCategory-'||
1139: xErrLoc||' '||SQLERRM);
1140: raise ICX_POR_EXT_UTL.gException;
1141: END translateCategory;
1142:
1136: when others then
1137: ROLLBACK;
1138: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateCategory-'||
1139: xErrLoc||' '||SQLERRM);
1140: raise ICX_POR_EXT_UTL.gException;
1141: END translateCategory;
1142:
1143: -- Create a template header
1144: PROCEDURE createTemplateHeader(p_org_id IN NUMBER,
1149: xErrLoc PLS_INTEGER:= 100;
1150:
1151: BEGIN
1152: xErrLoc:= 50;
1153: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1154: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1155: 'createTemplateHeader(p_org_id: ' || p_org_id ||
1156: ', p_express_name: ' || p_express_name ||
1157: ', p_type_lookup_code: ' || p_type_lookup_code ||
1150:
1151: BEGIN
1152: xErrLoc:= 50;
1153: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1154: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1155: 'createTemplateHeader(p_org_id: ' || p_org_id ||
1156: ', p_express_name: ' || p_express_name ||
1157: ', p_type_lookup_code: ' || p_type_lookup_code ||
1158: ', p_inactive_date: ' || p_inactive_date || ')');
1180:
1181: EXCEPTION
1182: WHEN OTHERS THEN
1183: ROLLBACK;
1184: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateHeader-'||
1185: xErrLoc||' '||SQLERRM);
1186: raise ICX_POR_EXT_UTL.gException;
1187: END createTemplateHeader;
1188:
1182: WHEN OTHERS THEN
1183: ROLLBACK;
1184: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateHeader-'||
1185: xErrLoc||' '||SQLERRM);
1186: raise ICX_POR_EXT_UTL.gException;
1187: END createTemplateHeader;
1188:
1189: -- Update a template header
1190: PROCEDURE updateTemplateHeader(p_org_id IN NUMBER,
1194: xErrLoc PLS_INTEGER:= 100;
1195:
1196: BEGIN
1197: xErrLoc:= 50;
1198: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1199: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1200: 'updateTemplateHeader(p_org_id: ' || p_org_id ||
1201: ', p_express_name: ' || p_express_name ||
1202: ', p_inactive_date: ' || p_inactive_date || ')');
1195:
1196: BEGIN
1197: xErrLoc:= 50;
1198: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1199: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1200: 'updateTemplateHeader(p_org_id: ' || p_org_id ||
1201: ', p_express_name: ' || p_express_name ||
1202: ', p_inactive_date: ' || p_inactive_date || ')');
1203: END IF;
1216:
1217: EXCEPTION
1218: WHEN OTHERS THEN
1219: ROLLBACK;
1220: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateHeader-'||
1221: xErrLoc||' '||SQLERRM);
1222: raise ICX_POR_EXT_UTL.gException;
1223: END updateTemplateHeader;
1224:
1218: WHEN OTHERS THEN
1219: ROLLBACK;
1220: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateHeader-'||
1221: xErrLoc||' '||SQLERRM);
1222: raise ICX_POR_EXT_UTL.gException;
1223: END updateTemplateHeader;
1224:
1225: --------------------------------------------------------------
1226: -- Classification Test Result Checking --
1239: FROM icx_cat_categories_tl
1240: WHERE key = p_category_key
1241: AND category_name = p_category_name
1242: AND ROWNUM = 1;
1243: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1244: 'Category[Key: ' || p_category_key || ', Name: ' ||
1245: p_category_name || '] exists in ICX_CAT_CATEGORIES_TL');
1246:
1247: xErrLoc:= 140;
1250: FROM icx_por_category_data_sources
1251: WHERE category_key = p_category_key
1252: AND external_source_key = p_category_key
1253: AND ROWNUM = 1;
1254: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1255: 'Category[Key: ' || p_category_key || ', Name: ' ||
1256: p_category_name || '] exists in ICX_POR_CATEGORY_DATA_SOURCES');
1257:
1258: xErrLoc:= 180;
1261: INTO xResult
1262: FROM icx_por_category_order_map
1263: WHERE external_source_key = p_category_key
1264: AND ROWNUM = 1;
1265: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1266: 'Category[Key: ' || p_category_key || ', Name: ' ||
1267: p_category_name || '] exists in ICX_POR_CATEGORY_ORDER_MAP');
1268: END IF;
1269:
1271: RETURN TRUE;
1272: EXCEPTION
1273: when NO_DATA_FOUND then
1274: xResult:= 0;
1275: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1276: 'Category[Key: ' || p_category_key || ', Name: ' ||
1277: p_category_name || '] does not exist');
1278: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1279: 'Category[Key: ' || p_category_key || ', Name: ' ||
1274: xResult:= 0;
1275: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1276: 'Category[Key: ' || p_category_key || ', Name: ' ||
1277: p_category_name || '] does not exist');
1278: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1279: 'Category[Key: ' || p_category_key || ', Name: ' ||
1280: p_category_name || '] does not exist');
1281: RETURN FALSE;
1282: WHEN OTHERS THEN
1279: 'Category[Key: ' || p_category_key || ', Name: ' ||
1280: p_category_name || '] does not exist');
1281: RETURN FALSE;
1282: WHEN OTHERS THEN
1283: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategory-'||
1284: xErrLoc||' '||SQLERRM);
1285: raise ICX_POR_EXT_UTL.gException;
1286: END existCategory;
1287:
1281: RETURN FALSE;
1282: WHEN OTHERS THEN
1283: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategory-'||
1284: xErrLoc||' '||SQLERRM);
1285: raise ICX_POR_EXT_UTL.gException;
1286: END existCategory;
1287:
1288: FUNCTION notExistCategory(p_category_key IN VARCHAR2)
1289: RETURN BOOLEAN
1296: INTO xResult
1297: FROM icx_cat_categories_tl
1298: WHERE key = p_category_key
1299: AND ROWNUM = 1;
1300: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1301: 'Category[Key: ' || p_category_key || '] exists in ICX_CAT_CATEGORIES_TL');
1302: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1303: 'Category[Key: ' || p_category_key || '] exist');
1304:
1298: WHERE key = p_category_key
1299: AND ROWNUM = 1;
1300: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1301: 'Category[Key: ' || p_category_key || '] exists in ICX_CAT_CATEGORIES_TL');
1302: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1303: 'Category[Key: ' || p_category_key || '] exist');
1304:
1305: xErrLoc:= 200;
1306: RETURN FALSE;
1306: RETURN FALSE;
1307: EXCEPTION
1308: when NO_DATA_FOUND then
1309: xResult:= 1;
1310: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1311: 'Category[Key: ' || p_category_key || '] does not exists');
1312: RETURN TRUE;
1313: WHEN OTHERS THEN
1314: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCategory-'||
1310: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1311: 'Category[Key: ' || p_category_key || '] does not exists');
1312: RETURN TRUE;
1313: WHEN OTHERS THEN
1314: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCategory-'||
1315: xErrLoc||' '||SQLERRM);
1316: raise ICX_POR_EXT_UTL.gException;
1317: END notExistCategory;
1318:
1312: RETURN TRUE;
1313: WHEN OTHERS THEN
1314: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCategory-'||
1315: xErrLoc||' '||SQLERRM);
1316: raise ICX_POR_EXT_UTL.gException;
1317: END notExistCategory;
1318:
1319: FUNCTION existCategoryTL(p_category_key IN VARCHAR2,
1320: p_category_name IN VARCHAR2,
1331: WHERE key = p_category_key
1332: AND category_name = p_category_name
1333: AND language = p_language
1334: AND ROWNUM = 1;
1335: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1336: 'Category[Key: ' || p_category_key || ', Name: ' ||
1337: p_category_name || ', Language: ' || p_language ||
1338: '] exists in ICX_CAT_CATEGORIES_TL');
1339:
1341: RETURN TRUE;
1342: EXCEPTION
1343: when NO_DATA_FOUND then
1344: xResult:= 0;
1345: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1346: 'Category[Key: ' || p_category_key || ', Name: ' ||
1347: p_category_name || ', Language: ' || p_language ||
1348: '] does not exist');
1349: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1345: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
1346: 'Category[Key: ' || p_category_key || ', Name: ' ||
1347: p_category_name || ', Language: ' || p_language ||
1348: '] does not exist');
1349: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1350: 'Category[Key: ' || p_category_key || ', Name: ' ||
1351: p_category_name || ', Language: ' || p_language ||
1352: '] does not exist');
1353: RETURN FALSE;
1351: p_category_name || ', Language: ' || p_language ||
1352: '] does not exist');
1353: RETURN FALSE;
1354: WHEN OTHERS THEN
1355: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategoryTL-'||
1356: xErrLoc||' '||SQLERRM);
1357: raise ICX_POR_EXT_UTL.gException;
1358: END existCategoryTL;
1359:
1353: RETURN FALSE;
1354: WHEN OTHERS THEN
1355: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCategoryTL-'||
1356: xErrLoc||' '||SQLERRM);
1357: raise ICX_POR_EXT_UTL.gException;
1358: END existCategoryTL;
1359:
1360: --------------------------------------------------------------
1361: -- Item Test Utilities --
1367: xErrLoc PLS_INTEGER:= 100;
1368:
1369: BEGIN
1370: xErrLoc:= 50;
1371: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1372: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1373: 'createGSB(p_set_of_books_id: ' || p_set_of_books_id ||
1374: ', p_currency_code: ' || p_currency_code || ')');
1375: END IF;
1368:
1369: BEGIN
1370: xErrLoc:= 50;
1371: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1372: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1373: 'createGSB(p_set_of_books_id: ' || p_set_of_books_id ||
1374: ', p_currency_code: ' || p_currency_code || ')');
1375: END IF;
1376:
1388: COMMIT;
1389: EXCEPTION
1390: WHEN OTHERS THEN
1391: ROLLBACK;
1392: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGSB-'||
1393: xErrLoc||' '||SQLERRM);
1394: raise ICX_POR_EXT_UTL.gException;
1395: END createGSB;
1396:
1390: WHEN OTHERS THEN
1391: ROLLBACK;
1392: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGSB-'||
1393: xErrLoc||' '||SQLERRM);
1394: raise ICX_POR_EXT_UTL.gException;
1395: END createGSB;
1396:
1397: -- Create financial system parameters
1398: PROCEDURE createFSP(p_org_id IN NUMBER,
1402: xErrLoc PLS_INTEGER:= 100;
1403:
1404: BEGIN
1405: xErrLoc:= 50;
1406: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1407: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1408: 'createFSP(p_org_id: ' || p_org_id ||
1409: ', p_inventory_organization_id: ' || p_inventory_organization_id ||
1410: ', p_set_of_books_id: ' || p_set_of_books_id || ')');
1403:
1404: BEGIN
1405: xErrLoc:= 50;
1406: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1407: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1408: 'createFSP(p_org_id: ' || p_org_id ||
1409: ', p_inventory_organization_id: ' || p_inventory_organization_id ||
1410: ', p_set_of_books_id: ' || p_set_of_books_id || ')');
1411: END IF;
1438: COMMIT;
1439: EXCEPTION
1440: WHEN OTHERS THEN
1441: ROLLBACK;
1442: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createFSP-'||
1443: xErrLoc||' '||SQLERRM);
1444: raise ICX_POR_EXT_UTL.gException;
1445: END createFSP;
1446:
1440: WHEN OTHERS THEN
1441: ROLLBACK;
1442: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createFSP-'||
1443: xErrLoc||' '||SQLERRM);
1444: raise ICX_POR_EXT_UTL.gException;
1445: END createFSP;
1446:
1447: -- Create an item
1448: PROCEDURE createItem(p_inventory_item_id IN NUMBER,
1463: xErrLoc PLS_INTEGER:= 100;
1464:
1465: BEGIN
1466: xErrLoc:= 50;
1467: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1468: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1469: 'createItem(p_inventory_item_id: ' || p_inventory_item_id ||
1470: ', p_organization_id: ' || p_organization_id ||
1471: ', p_concatenated_segments: ' || p_concatenated_segments ||
1464:
1465: BEGIN
1466: xErrLoc:= 50;
1467: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1468: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1469: 'createItem(p_inventory_item_id: ' || p_inventory_item_id ||
1470: ', p_organization_id: ' || p_organization_id ||
1471: ', p_concatenated_segments: ' || p_concatenated_segments ||
1472: ', p_purchasing_enabled_flag: ' || p_purchasing_enabled_flag ||
1560:
1561: EXCEPTION
1562: WHEN OTHERS THEN
1563: ROLLBACK;
1564: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createItem-'||
1565: xErrLoc||' '||SQLERRM);
1566: raise ICX_POR_EXT_UTL.gException;
1567: END createItem;
1568:
1562: WHEN OTHERS THEN
1563: ROLLBACK;
1564: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createItem-'||
1565: xErrLoc||' '||SQLERRM);
1566: raise ICX_POR_EXT_UTL.gException;
1567: END createItem;
1568:
1569: -- Update an item
1570: PROCEDURE updateItem(p_inventory_item_id IN NUMBER,
1585: xErrLoc PLS_INTEGER:= 100;
1586:
1587: BEGIN
1588: xErrLoc:= 50;
1589: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1590: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1591: 'updateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1592: ', p_organization_id: ' || p_organization_id ||
1593: ', p_concatenated_segments: ' || p_concatenated_segments ||
1586:
1587: BEGIN
1588: xErrLoc:= 50;
1589: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1590: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1591: 'updateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1592: ', p_organization_id: ' || p_organization_id ||
1593: ', p_concatenated_segments: ' || p_concatenated_segments ||
1594: ', p_purchasing_enabled_flag: ' || p_purchasing_enabled_flag ||
1669:
1670: EXCEPTION
1671: WHEN OTHERS THEN
1672: ROLLBACK;
1673: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateItem-'||
1674: xErrLoc||' '||SQLERRM);
1675: raise ICX_POR_EXT_UTL.gException;
1676: END updateItem;
1677:
1671: WHEN OTHERS THEN
1672: ROLLBACK;
1673: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateItem-'||
1674: xErrLoc||' '||SQLERRM);
1675: raise ICX_POR_EXT_UTL.gException;
1676: END updateItem;
1677:
1678: -- Translate an item
1679: PROCEDURE translateItem(p_inventory_item_id IN NUMBER,
1685: xExist PLS_INTEGER:= 0;
1686:
1687: BEGIN
1688: xErrLoc:= 50;
1689: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1690: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1691: 'translateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1692: ', p_organization_id: ' || p_organization_id ||
1693: ', p_description: ' || p_description ||
1686:
1687: BEGIN
1688: xErrLoc:= 50;
1689: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1690: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1691: 'translateItem(p_inventory_item_id: ' || p_inventory_item_id ||
1692: ', p_organization_id: ' || p_organization_id ||
1693: ', p_description: ' || p_description ||
1694: ', p_language: ' || p_language || ')');
1704: WHERE installed_flag = 'I'
1705: AND language_code = p_language);
1706: EXCEPTION
1707: WHEN NO_DATA_FOUND THEN
1708: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
1709: 'Not an installed language: ' || p_language);
1710: RETURN;
1711: END;
1712:
1752:
1753: EXCEPTION
1754: when others then
1755: ROLLBACK;
1756: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateItem-'||
1757: xErrLoc||' '||SQLERRM);
1758: raise ICX_POR_EXT_UTL.gException;
1759: END translateItem;
1760:
1754: when others then
1755: ROLLBACK;
1756: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.translateItem-'||
1757: xErrLoc||' '||SQLERRM);
1758: raise ICX_POR_EXT_UTL.gException;
1759: END translateItem;
1760:
1761: -- Delete an item
1762: PROCEDURE deleteItem(p_inventory_item_id IN NUMBER,
1765: xErrLoc PLS_INTEGER:= 100;
1766:
1767: BEGIN
1768: xErrLoc:= 50;
1769: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1770: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1771: 'deleteItem(p_inventory_item_id: ' || p_inventory_item_id ||
1772: ', p_organization_id: ' || p_organization_id || ')');
1773: END IF;
1766:
1767: BEGIN
1768: xErrLoc:= 50;
1769: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1770: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1771: 'deleteItem(p_inventory_item_id: ' || p_inventory_item_id ||
1772: ', p_organization_id: ' || p_organization_id || ')');
1773: END IF;
1774:
1798:
1799: EXCEPTION
1800: WHEN OTHERS THEN
1801: ROLLBACK;
1802: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.deleteItem-'||
1803: xErrLoc||' '||SQLERRM);
1804: raise ICX_POR_EXT_UTL.gException;
1805: END deleteItem;
1806:
1800: WHEN OTHERS THEN
1801: ROLLBACK;
1802: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.deleteItem-'||
1803: xErrLoc||' '||SQLERRM);
1804: raise ICX_POR_EXT_UTL.gException;
1805: END deleteItem;
1806:
1807: -- Create a vendor
1808: PROCEDURE createVendor(p_vendor_id IN NUMBER,
1811: xErrLoc PLS_INTEGER:= 100;
1812:
1813: BEGIN
1814: xErrLoc:= 50;
1815: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1816: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1817: 'createVendor(p_vendor_id: ' || p_vendor_id ||
1818: ', p_vendor_name: ' || p_vendor_name || ')');
1819: END IF;
1812:
1813: BEGIN
1814: xErrLoc:= 50;
1815: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1816: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1817: 'createVendor(p_vendor_id: ' || p_vendor_id ||
1818: ', p_vendor_name: ' || p_vendor_name || ')');
1819: END IF;
1820:
1834: COMMIT;
1835: EXCEPTION
1836: WHEN OTHERS THEN
1837: ROLLBACK;
1838: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendor-'||
1839: xErrLoc||' '||SQLERRM);
1840: raise ICX_POR_EXT_UTL.gException;
1841: END createVendor;
1842:
1836: WHEN OTHERS THEN
1837: ROLLBACK;
1838: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendor-'||
1839: xErrLoc||' '||SQLERRM);
1840: raise ICX_POR_EXT_UTL.gException;
1841: END createVendor;
1842:
1843: -- Update a vendor
1844: PROCEDURE updateVendor(p_vendor_id IN NUMBER,
1847: xErrLoc PLS_INTEGER:= 100;
1848:
1849: BEGIN
1850: xErrLoc:= 50;
1851: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1852: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1853: 'updateVendor(p_vendor_id: ' || p_vendor_id ||
1854: ', p_vendor_name: ' || p_vendor_name || ')');
1855: END IF;
1848:
1849: BEGIN
1850: xErrLoc:= 50;
1851: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1852: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1853: 'updateVendor(p_vendor_id: ' || p_vendor_id ||
1854: ', p_vendor_name: ' || p_vendor_name || ')');
1855: END IF;
1856:
1867:
1868: EXCEPTION
1869: WHEN OTHERS THEN
1870: ROLLBACK;
1871: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendor-'||
1872: xErrLoc||' '||SQLERRM);
1873: raise ICX_POR_EXT_UTL.gException;
1874: END updateVendor;
1875:
1869: WHEN OTHERS THEN
1870: ROLLBACK;
1871: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendor-'||
1872: xErrLoc||' '||SQLERRM);
1873: raise ICX_POR_EXT_UTL.gException;
1874: END updateVendor;
1875:
1876: -- Create a vendor site
1877: PROCEDURE createVendorSite(p_vendor_site_id IN NUMBER,
1881: xErrLoc PLS_INTEGER:= 100;
1882:
1883: BEGIN
1884: xErrLoc:= 50;
1885: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1886: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1887: 'createVendorSite(p_vendor_site_id: ' || p_vendor_site_id ||
1888: ', p_vendor_site_code: ' || p_vendor_site_code ||
1889: ', p_purchasing_site_flag: ' || p_purchasing_site_flag || ')');
1882:
1883: BEGIN
1884: xErrLoc:= 50;
1885: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1886: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1887: 'createVendorSite(p_vendor_site_id: ' || p_vendor_site_id ||
1888: ', p_vendor_site_code: ' || p_vendor_site_code ||
1889: ', p_purchasing_site_flag: ' || p_purchasing_site_flag || ')');
1890: END IF;
1909: COMMIT;
1910: EXCEPTION
1911: WHEN OTHERS THEN
1912: ROLLBACK;
1913: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendorSite-'||
1914: xErrLoc||' '||SQLERRM);
1915: raise ICX_POR_EXT_UTL.gException;
1916: END createVendorSite;
1917:
1911: WHEN OTHERS THEN
1912: ROLLBACK;
1913: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createVendorSite-'||
1914: xErrLoc||' '||SQLERRM);
1915: raise ICX_POR_EXT_UTL.gException;
1916: END createVendorSite;
1917:
1918: -- Update a vendor site
1919: PROCEDURE updateVendorSite(p_vendor_site_id IN NUMBER,
1923: xErrLoc PLS_INTEGER:= 100;
1924:
1925: BEGIN
1926: xErrLoc:= 50;
1927: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1928: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1929: 'createVendor(p_vendor_site_id: ' || p_vendor_site_id ||
1930: ', p_purchasing_site_flag: ' || p_purchasing_site_flag ||
1931: ', p_inactive_date: ' || p_inactive_date || ')');
1924:
1925: BEGIN
1926: xErrLoc:= 50;
1927: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1928: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1929: 'createVendor(p_vendor_site_id: ' || p_vendor_site_id ||
1930: ', p_purchasing_site_flag: ' || p_purchasing_site_flag ||
1931: ', p_inactive_date: ' || p_inactive_date || ')');
1932: END IF;
1948: COMMIT;
1949: EXCEPTION
1950: WHEN OTHERS THEN
1951: ROLLBACK;
1952: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendorSite-'||
1953: xErrLoc||' '||SQLERRM);
1954: raise ICX_POR_EXT_UTL.gException;
1955: END updateVendorSite;
1956:
1950: WHEN OTHERS THEN
1951: ROLLBACK;
1952: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateVendorSite-'||
1953: xErrLoc||' '||SQLERRM);
1954: raise ICX_POR_EXT_UTL.gException;
1955: END updateVendorSite;
1956:
1957: -- Create an ASL
1958: PROCEDURE createASL(p_asl_id IN NUMBER,
1970: xErrLoc PLS_INTEGER:= 100;
1971:
1972: BEGIN
1973: xErrLoc:= 50;
1974: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1975: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1976: 'createASL(p_asl_id: ' || p_asl_id ||
1977: ', p_asl_status_id: ' || p_asl_status_id ||
1978: ', p_owning_organization_id: ' || p_owning_organization_id ||
1971:
1972: BEGIN
1973: xErrLoc:= 50;
1974: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
1975: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
1976: 'createASL(p_asl_id: ' || p_asl_id ||
1977: ', p_asl_status_id: ' || p_asl_status_id ||
1978: ', p_owning_organization_id: ' || p_owning_organization_id ||
1979: ', p_item_id: ' || p_item_id ||
2048:
2049: EXCEPTION
2050: WHEN OTHERS THEN
2051: ROLLBACK;
2052: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createASL-'||
2053: xErrLoc||' '||SQLERRM);
2054: raise ICX_POR_EXT_UTL.gException;
2055: END createASL;
2056:
2050: WHEN OTHERS THEN
2051: ROLLBACK;
2052: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createASL-'||
2053: xErrLoc||' '||SQLERRM);
2054: raise ICX_POR_EXT_UTL.gException;
2055: END createASL;
2056:
2057: -- Update an ASL
2058: PROCEDURE updateASL(p_asl_id IN NUMBER,
2066: xErrLoc PLS_INTEGER:= 100;
2067:
2068: BEGIN
2069: xErrLoc:= 50;
2070: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2071: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2072: 'updateASL(p_asl_id: ' || p_asl_id ||
2073: ', p_asl_status_id: ' || p_asl_status_id ||
2074: ', p_vendor_site_id: ' || p_vendor_site_id ||
2067:
2068: BEGIN
2069: xErrLoc:= 50;
2070: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2071: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2072: 'updateASL(p_asl_id: ' || p_asl_id ||
2073: ', p_asl_status_id: ' || p_asl_status_id ||
2074: ', p_vendor_site_id: ' || p_vendor_site_id ||
2075: ', p_primary_vendor_item: ' || p_primary_vendor_item ||
2119:
2120: EXCEPTION
2121: WHEN OTHERS THEN
2122: ROLLBACK;
2123: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateASL-'||
2124: xErrLoc||' '||SQLERRM);
2125: raise ICX_POR_EXT_UTL.gException;
2126: END updateASL;
2127:
2121: WHEN OTHERS THEN
2122: ROLLBACK;
2123: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateASL-'||
2124: xErrLoc||' '||SQLERRM);
2125: raise ICX_POR_EXT_UTL.gException;
2126: END updateASL;
2127:
2128: -- Create a template line
2129: PROCEDURE createTemplateLine(p_org_id IN NUMBER,
2144: xErrLoc PLS_INTEGER:= 100;
2145:
2146: BEGIN
2147: xErrLoc:= 50;
2148: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2149: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2150: 'createTemplateLine(p_org_id: ' || p_org_id ||
2151: ', p_express_name: ' || p_express_name ||
2152: ', p_sequence_num: ' || p_sequence_num ||
2145:
2146: BEGIN
2147: xErrLoc:= 50;
2148: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2149: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2150: 'createTemplateLine(p_org_id: ' || p_org_id ||
2151: ', p_express_name: ' || p_express_name ||
2152: ', p_sequence_num: ' || p_sequence_num ||
2153: ', p_source_type_code: ' || p_source_type_code ||
2214:
2215: EXCEPTION
2216: WHEN OTHERS THEN
2217: ROLLBACK;
2218: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2219: xErrLoc||' '||SQLERRM);
2220: raise ICX_POR_EXT_UTL.gException;
2221: END createTemplateLine;
2222:
2216: WHEN OTHERS THEN
2217: ROLLBACK;
2218: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2219: xErrLoc||' '||SQLERRM);
2220: raise ICX_POR_EXT_UTL.gException;
2221: END createTemplateLine;
2222:
2223: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
2224: -- Overloaded createTemplateLine Procedure to create a template line accepting Suggested Quantity
2242: xErrLoc PLS_INTEGER:= 100;
2243:
2244: BEGIN
2245: xErrLoc:= 50;
2246: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2247: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2248: 'createTemplateLine(p_org_id: ' || p_org_id ||
2249: ', p_express_name: ' || p_express_name ||
2250: ', p_sequence_num: ' || p_sequence_num ||
2243:
2244: BEGIN
2245: xErrLoc:= 50;
2246: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2247: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2248: 'createTemplateLine(p_org_id: ' || p_org_id ||
2249: ', p_express_name: ' || p_express_name ||
2250: ', p_sequence_num: ' || p_sequence_num ||
2251: ', p_source_type_code: ' || p_source_type_code ||
2316:
2317: EXCEPTION
2318: WHEN OTHERS THEN
2319: ROLLBACK;
2320: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2321: xErrLoc||' '||SQLERRM);
2322: raise ICX_POR_EXT_UTL.gException;
2323: END createTemplateLine;
2324:
2318: WHEN OTHERS THEN
2319: ROLLBACK;
2320: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createTemplateLine-'||
2321: xErrLoc||' '||SQLERRM);
2322: raise ICX_POR_EXT_UTL.gException;
2323: END createTemplateLine;
2324:
2325: -- Update a template line
2326: PROCEDURE updateTemplateLine(p_org_id IN NUMBER,
2339: xErrLoc PLS_INTEGER:= 100;
2340:
2341: BEGIN
2342: xErrLoc:= 50;
2343: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2344: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2345: 'updateTemplateLine(p_org_id: ' || p_org_id ||
2346: ', p_express_name: ' || p_express_name ||
2347: ', p_sequence_num: ' || p_sequence_num ||
2340:
2341: BEGIN
2342: xErrLoc:= 50;
2343: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2344: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2345: 'updateTemplateLine(p_org_id: ' || p_org_id ||
2346: ', p_express_name: ' || p_express_name ||
2347: ', p_sequence_num: ' || p_sequence_num ||
2348: ', p_po_header_id: ' || p_po_header_id ||
2396:
2397: EXCEPTION
2398: WHEN OTHERS THEN
2399: ROLLBACK;
2400: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateLine-'||
2401: xErrLoc||' '||SQLERRM);
2402: raise ICX_POR_EXT_UTL.gException;
2403: END updateTemplateLine;
2404:
2398: WHEN OTHERS THEN
2399: ROLLBACK;
2400: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateTemplateLine-'||
2401: xErrLoc||' '||SQLERRM);
2402: raise ICX_POR_EXT_UTL.gException;
2403: END updateTemplateLine;
2404:
2405: -- Create a contract header
2406: PROCEDURE createContractHeader(p_po_header_id IN NUMBER,
2426: xErrLoc PLS_INTEGER:= 100;
2427:
2428: BEGIN
2429: xErrLoc:= 50;
2430: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2431: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2432: 'createContractHeader(p_po_header_id: ' || p_po_header_id ||
2433: ', p_org_id: ' || p_org_id ||
2434: ', p_segment1: ' || p_segment1 ||
2427:
2428: BEGIN
2429: xErrLoc:= 50;
2430: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2431: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2432: 'createContractHeader(p_po_header_id: ' || p_po_header_id ||
2433: ', p_org_id: ' || p_org_id ||
2434: ', p_segment1: ' || p_segment1 ||
2435: ', p_type_lookup_code: ' || p_type_lookup_code ||
2509:
2510: EXCEPTION
2511: WHEN OTHERS THEN
2512: ROLLBACK;
2513: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractHeader-'||
2514: xErrLoc||' '||SQLERRM);
2515: raise ICX_POR_EXT_UTL.gException;
2516: END createContractHeader;
2517:
2511: WHEN OTHERS THEN
2512: ROLLBACK;
2513: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractHeader-'||
2514: xErrLoc||' '||SQLERRM);
2515: raise ICX_POR_EXT_UTL.gException;
2516: END createContractHeader;
2517:
2518: -- Create a contract line
2519: PROCEDURE createContractLine(p_po_header_id IN NUMBER,
2537: xErrLoc PLS_INTEGER:= 100;
2538:
2539: BEGIN
2540: xErrLoc:= 50;
2541: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2542: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2543: 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2544: ', p_po_line_id: ' || p_po_line_id ||
2545: ', p_org_id: ' || p_org_id ||
2538:
2539: BEGIN
2540: xErrLoc:= 50;
2541: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2542: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2543: 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2544: ', p_po_line_id: ' || p_po_line_id ||
2545: ', p_org_id: ' || p_org_id ||
2546: ', p_line_num: ' || p_line_num ||
2624:
2625: EXCEPTION
2626: WHEN OTHERS THEN
2627: ROLLBACK;
2628: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2629: xErrLoc||' '||SQLERRM);
2630: raise ICX_POR_EXT_UTL.gException;
2631: END createContractLine;
2632:
2626: WHEN OTHERS THEN
2627: ROLLBACK;
2628: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2629: xErrLoc||' '||SQLERRM);
2630: raise ICX_POR_EXT_UTL.gException;
2631: END createContractLine;
2632:
2633: -- FPJ FPSL Extractor Changes
2634: -- Add 5 parameters for Amount, Allow Price Override Flag,
2660: xErrLoc PLS_INTEGER:= 100;
2661:
2662: BEGIN
2663: xErrLoc:= 50;
2664: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2665: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2666: 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2667: ', p_po_line_id: ' || p_po_line_id ||
2668: ', p_org_id: ' || p_org_id ||
2661:
2662: BEGIN
2663: xErrLoc:= 50;
2664: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2665: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2666: 'createContractLine(p_po_header_id: ' || p_po_header_id ||
2667: ', p_po_line_id: ' || p_po_line_id ||
2668: ', p_org_id: ' || p_org_id ||
2669: ', p_line_num: ' || p_line_num ||
2764:
2765: EXCEPTION
2766: WHEN OTHERS THEN
2767: ROLLBACK;
2768: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2769: xErrLoc||' '||SQLERRM);
2770: raise ICX_POR_EXT_UTL.gException;
2771: END createContractLine;
2772:
2766: WHEN OTHERS THEN
2767: ROLLBACK;
2768: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createContractLine-'||
2769: xErrLoc||' '||SQLERRM);
2770: raise ICX_POR_EXT_UTL.gException;
2771: END createContractLine;
2772:
2773: -- Update a contract header
2774: PROCEDURE updateContractHeader(p_po_header_id IN NUMBER,
2788: xErrLoc PLS_INTEGER:= 100;
2789:
2790: BEGIN
2791: xErrLoc:= 50;
2792: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2793: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2794: 'updateContractHeader(p_po_header_id: ' || p_po_header_id ||
2795: ', p_rate: ' || p_rate ||
2796: ', p_currency_code: ' || p_currency_code ||
2789:
2790: BEGIN
2791: xErrLoc:= 50;
2792: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2793: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2794: 'updateContractHeader(p_po_header_id: ' || p_po_header_id ||
2795: ', p_rate: ' || p_rate ||
2796: ', p_currency_code: ' || p_currency_code ||
2797: ', p_vendor_site_id: ' || p_vendor_site_id ||
2854:
2855: EXCEPTION
2856: WHEN OTHERS THEN
2857: ROLLBACK;
2858: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractHeader-'||
2859: xErrLoc||' '||SQLERRM);
2860: raise ICX_POR_EXT_UTL.gException;
2861: END updateContractHeader;
2862:
2856: WHEN OTHERS THEN
2857: ROLLBACK;
2858: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractHeader-'||
2859: xErrLoc||' '||SQLERRM);
2860: raise ICX_POR_EXT_UTL.gException;
2861: END updateContractHeader;
2862:
2863: -- Update a contract line
2864: PROCEDURE updateContractLine(p_po_line_id IN NUMBER,
2879: xErrLoc PLS_INTEGER:= 100;
2880:
2881: BEGIN
2882: xErrLoc:= 50;
2883: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2884: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2885: 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2886: ', p_item_description: ' || p_item_description ||
2887: ', p_vendor_product_num: ' || p_vendor_product_num ||
2880:
2881: BEGIN
2882: xErrLoc:= 50;
2883: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2884: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2885: 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2886: ', p_item_description: ' || p_item_description ||
2887: ', p_vendor_product_num: ' || p_vendor_product_num ||
2888: ', p_line_type_id: ' || p_line_type_id ||
2946:
2947: EXCEPTION
2948: WHEN OTHERS THEN
2949: ROLLBACK;
2950: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
2951: xErrLoc||' '||SQLERRM);
2952: raise ICX_POR_EXT_UTL.gException;
2953: END updateContractLine;
2954:
2948: WHEN OTHERS THEN
2949: ROLLBACK;
2950: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
2951: xErrLoc||' '||SQLERRM);
2952: raise ICX_POR_EXT_UTL.gException;
2953: END updateContractLine;
2954:
2955: -- Update a contract line
2956: -- FPJ FPSL Extractor Changes
2976: xErrLoc PLS_INTEGER:= 100;
2977:
2978: BEGIN
2979: xErrLoc:= 50;
2980: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2981: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2982: 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2983: ', p_item_description: ' || p_item_description ||
2984: ', p_vendor_product_num: ' || p_vendor_product_num ||
2977:
2978: BEGIN
2979: xErrLoc:= 50;
2980: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
2981: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
2982: 'updateContractLine(p_po_line_id: ' || p_po_line_id ||
2983: ', p_item_description: ' || p_item_description ||
2984: ', p_vendor_product_num: ' || p_vendor_product_num ||
2985: ', p_line_type_id: ' || p_line_type_id ||
3055:
3056: EXCEPTION
3057: WHEN OTHERS THEN
3058: ROLLBACK;
3059: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
3060: xErrLoc||' '||SQLERRM);
3061: raise ICX_POR_EXT_UTL.gException;
3062: END updateContractLine;
3063:
3057: WHEN OTHERS THEN
3058: ROLLBACK;
3059: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateContractLine-'||
3060: xErrLoc||' '||SQLERRM);
3061: raise ICX_POR_EXT_UTL.gException;
3062: END updateContractLine;
3063:
3064: -- Create a quotation line location
3065: PROCEDURE createQuoteLL(p_line_location_id IN NUMBER,
3073: xErrLoc PLS_INTEGER:= 100;
3074:
3075: BEGIN
3076: xErrLoc:= 50;
3077: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3078: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3079: 'createQuoteLL(p_line_location_id: ' || p_line_location_id ||
3080: ', p_po_line_id: ' || p_po_line_id ||
3081: ', p_start_date: ' || p_start_date ||
3074:
3075: BEGIN
3076: xErrLoc:= 50;
3077: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3078: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3079: 'createQuoteLL(p_line_location_id: ' || p_line_location_id ||
3080: ', p_po_line_id: ' || p_po_line_id ||
3081: ', p_start_date: ' || p_start_date ||
3082: ', p_end_date: ' || p_end_date ||
3124:
3125: EXCEPTION
3126: WHEN OTHERS THEN
3127: ROLLBACK;
3128: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createQuoteLL-'||
3129: xErrLoc||' '||SQLERRM);
3130: raise ICX_POR_EXT_UTL.gException;
3131: END createQuoteLL;
3132:
3126: WHEN OTHERS THEN
3127: ROLLBACK;
3128: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createQuoteLL-'||
3129: xErrLoc||' '||SQLERRM);
3130: raise ICX_POR_EXT_UTL.gException;
3131: END createQuoteLL;
3132:
3133: -- Update a quotation line location
3134: PROCEDURE updateQuoteLL(p_line_location_id IN NUMBER,
3141: xErrLoc PLS_INTEGER:= 100;
3142:
3143: BEGIN
3144: xErrLoc:= 50;
3145: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3146: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3147: 'updateQuoteLL(p_line_location_id: ' || p_line_location_id ||
3148: ', p_start_date: ' || p_start_date ||
3149: ', p_end_date: ' || p_end_date ||
3142:
3143: BEGIN
3144: xErrLoc:= 50;
3145: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3146: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3147: 'updateQuoteLL(p_line_location_id: ' || p_line_location_id ||
3148: ', p_start_date: ' || p_start_date ||
3149: ', p_end_date: ' || p_end_date ||
3150: ', p_approval_type: ' || p_approval_type ||
3187:
3188: EXCEPTION
3189: WHEN OTHERS THEN
3190: ROLLBACK;
3191: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateQuoteLL-'||
3192: xErrLoc||' '||SQLERRM);
3193: raise ICX_POR_EXT_UTL.gException;
3194: END updateQuoteLL;
3195:
3189: WHEN OTHERS THEN
3190: ROLLBACK;
3191: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateQuoteLL-'||
3192: xErrLoc||' '||SQLERRM);
3193: raise ICX_POR_EXT_UTL.gException;
3194: END updateQuoteLL;
3195:
3196: -- Create a global agreement assignment
3197: PROCEDURE createGlobalA(p_po_header_id IN NUMBER,
3203: xErrLoc PLS_INTEGER:= 100;
3204:
3205: BEGIN
3206: xErrLoc:= 50;
3207: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3208: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3209: 'createGlobalA(p_po_header_id: ' || p_po_header_id ||
3210: ', p_organization_id: ' || p_organization_id ||
3211: ', p_enabled_flag: ' || p_enabled_flag ||
3204:
3205: BEGIN
3206: xErrLoc:= 50;
3207: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3208: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3209: 'createGlobalA(p_po_header_id: ' || p_po_header_id ||
3210: ', p_organization_id: ' || p_organization_id ||
3211: ', p_enabled_flag: ' || p_enabled_flag ||
3212: ', p_vendor_site_id: ' || p_vendor_site_id ||
3238:
3239: EXCEPTION
3240: WHEN OTHERS THEN
3241: ROLLBACK;
3242: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGlobalA-'||
3243: xErrLoc||' '||SQLERRM);
3244: raise ICX_POR_EXT_UTL.gException;
3245: END createGlobalA;
3246:
3240: WHEN OTHERS THEN
3241: ROLLBACK;
3242: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.createGlobalA-'||
3243: xErrLoc||' '||SQLERRM);
3244: raise ICX_POR_EXT_UTL.gException;
3245: END createGlobalA;
3246:
3247: -- Update a global agreement assignment
3248: PROCEDURE updateGlobalA(p_po_header_id IN NUMBER,
3254: xErrLoc PLS_INTEGER:= 100;
3255:
3256: BEGIN
3257: xErrLoc:= 50;
3258: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3259: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3260: 'updateGlobalA(p_po_header_id: ' || p_po_header_id ||
3261: ', p_organization_id: ' || p_organization_id ||
3262: ', p_enabled_flag: ' || p_enabled_flag ||
3255:
3256: BEGIN
3257: xErrLoc:= 50;
3258: IF ICX_POR_EXT_UTL.gDebugLevel >= ICX_POR_EXT_UTL.DEBUG_LEVEL THEN
3259: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.DEBUG_LEVEL,
3260: 'updateGlobalA(p_po_header_id: ' || p_po_header_id ||
3261: ', p_organization_id: ' || p_organization_id ||
3262: ', p_enabled_flag: ' || p_enabled_flag ||
3263: ', p_vendor_site_id: ' || p_vendor_site_id ||
3286:
3287: EXCEPTION
3288: WHEN OTHERS THEN
3289: ROLLBACK;
3290: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateGlobalA-'||
3291: xErrLoc||' '||SQLERRM);
3292: raise ICX_POR_EXT_UTL.gException;
3293: END updateGlobalA;
3294:
3288: WHEN OTHERS THEN
3289: ROLLBACK;
3290: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.updateGlobalA-'||
3291: xErrLoc||' '||SQLERRM);
3292: raise ICX_POR_EXT_UTL.gException;
3293: END updateGlobalA;
3294:
3295: --------------------------------------------------------------
3296: -- Item Test Result Checking --
3339: FROM icx_cat_item_prices p
3340: WHERE p.rt_item_id = i.rt_item_id
3341: AND p.search_type = xSearchType);
3342:
3343: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3344: 'ItemsB[ORG_ID: ' || p_org_id ||
3345: ', SUPPLIER_ID: ' || p_supplier_id ||
3346: ', SUPPLIER: ' || p_supplier ||
3347: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3355: RETURN TRUE;
3356: EXCEPTION
3357: when NO_DATA_FOUND then
3358: xResult:= 0;
3359: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3360: 'ItemsB[ORG_ID: ' || p_org_id ||
3361: ', SUPPLIER_ID: ' || p_supplier_id ||
3362: ', SUPPLIER: ' || p_supplier ||
3363: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3365: ', INTERNAL_ITEM_NUM: ' || p_internal_item_num ||
3366: ', EXTRACTOR_UPDATED_FLAG: ' || p_extractor_updated_flag ||
3367: ', INTERNAL_FLAG: ' || p_internal_flag ||
3368: '] does not exist in ICX_CAT_ITEMS_B');
3369: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3370: 'ItemsB[ORG_ID: ' || p_org_id ||
3371: ', SUPPLIER_ID: ' || p_supplier_id ||
3372: ', SUPPLIER: ' || p_supplier ||
3373: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3377: ', INTERNAL_FLAG: ' || p_internal_flag ||
3378: '] does not exist in ICX_CAT_ITEMS_B');
3379: RETURN FALSE;
3380: WHEN OTHERS THEN
3381: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsB-'||
3382: xErrLoc||' '||SQLERRM);
3383: raise ICX_POR_EXT_UTL.gException;
3384: END existItemsB;
3385:
3379: RETURN FALSE;
3380: WHEN OTHERS THEN
3381: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsB-'||
3382: xErrLoc||' '||SQLERRM);
3383: raise ICX_POR_EXT_UTL.gException;
3384: END existItemsB;
3385:
3386: FUNCTION notExistItemsB(p_org_id IN NUMBER,
3387: p_supplier_id IN NUMBER,
3418: FROM icx_cat_item_prices p
3419: WHERE p.rt_item_id = i.rt_item_id
3420: AND p.search_type = xSearchType);
3421:
3422: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3423: 'ItemsB[ORG_ID: ' || p_org_id ||
3424: ', SUPPLIER_ID: ' || p_supplier_id ||
3425: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3426: ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3425: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3426: ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3427: ', INTERNAL_FLAG: ' || p_internal_flag ||
3428: '] exists in ICX_CAT_ITEMS_B');
3429: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3430: 'ItemsB[ORG_ID: ' || p_org_id ||
3431: ', SUPPLIER_ID: ' || p_supplier_id ||
3432: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3433: ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3438: RETURN FALSE;
3439: EXCEPTION
3440: when NO_DATA_FOUND then
3441: xResult:= 1;
3442: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3443: 'ItemsB[ORG_ID: ' || p_org_id ||
3444: ', SUPPLIER_ID: ' || p_supplier_id ||
3445: ', SUPPLIER_PART_NUM: ' || p_supplier_part_num ||
3446: ', INTERNAL_ITEM_ID: ' || p_internal_item_id ||
3447: ', INTERNAL_FLAG: ' || p_internal_flag ||
3448: '] does not exist in ICX_CAT_ITEMS_B');
3449: RETURN TRUE;
3450: WHEN OTHERS THEN
3451: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsB-'||
3452: xErrLoc||' '||SQLERRM);
3453: raise ICX_POR_EXT_UTL.gException;
3454: END notExistItemsB;
3455:
3449: RETURN TRUE;
3450: WHEN OTHERS THEN
3451: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsB-'||
3452: xErrLoc||' '||SQLERRM);
3453: raise ICX_POR_EXT_UTL.gException;
3454: END notExistItemsB;
3455:
3456: FUNCTION existItemsTLP(p_rt_item_id IN NUMBER,
3457: p_language IN VARCHAR2,
3501: AND (picture_url IS NULL AND p_picture_url IS NULL OR
3502: picture_url = p_picture_url);
3503: */
3504:
3505: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3506: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3507: ', LANGUAGE: ' || p_language ||
3508: ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3509: ', SEARCH_TYPE: ' || p_search_type ||
3523: RETURN TRUE;
3524: EXCEPTION
3525: when NO_DATA_FOUND then
3526: xResult:= 0;
3527: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3528: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3529: ', LANGUAGE: ' || p_language ||
3530: ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3531: ', SEARCH_TYPE: ' || p_search_type ||
3539: ', DESCRIPTION: ' || p_description ||
3540: ', PICTURE: ' || p_picture ||
3541: ', PICTURE_URL: ' || p_picture_url ||
3542: '] does not exist in ICX_CAT_ITEMS_TLP');
3543: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3544: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3545: ', LANGUAGE: ' || p_language ||
3546: ', ITEM_SOURCE_TYPE: ' || p_item_source_type ||
3547: ', SEARCH_TYPE: ' || p_search_type ||
3557: ', PICTURE_URL: ' || p_picture_url ||
3558: '] does not exist in ICX_CAT_ITEMS_TLP');
3559: RETURN FALSE;
3560: WHEN OTHERS THEN
3561: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsTLP-'||
3562: xErrLoc||' '||SQLERRM);
3563: raise ICX_POR_EXT_UTL.gException;
3564: END existItemsTLP;
3565:
3559: RETURN FALSE;
3560: WHEN OTHERS THEN
3561: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemsTLP-'||
3562: xErrLoc||' '||SQLERRM);
3563: raise ICX_POR_EXT_UTL.gException;
3564: END existItemsTLP;
3565:
3566: FUNCTION notExistItemsTLP(p_rt_item_id IN NUMBER,
3567: p_language IN VARCHAR2)
3576: FROM icx_cat_items_tlp
3577: WHERE rt_item_id = p_rt_item_id
3578: AND language = p_language;
3579:
3580: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3581: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3582: ', LANGUAGE: ' || p_language ||
3583: '] does not exist in ICX_CAT_ITEMS_TLP');
3584: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3580: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3581: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3582: ', LANGUAGE: ' || p_language ||
3583: '] does not exist in ICX_CAT_ITEMS_TLP');
3584: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3585: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3586: ', LANGUAGE: ' || p_language ||
3587: '] does not exist in ICX_CAT_ITEMS_TLP');
3588:
3590: RETURN FALSE;
3591: EXCEPTION
3592: when NO_DATA_FOUND then
3593: xResult:= 0;
3594: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3595: 'ItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3596: ', LANGUAGE: ' || p_language ||
3597: '] exists in ICX_CAT_ITEMS_TLP');
3598: RETURN TRUE;
3596: ', LANGUAGE: ' || p_language ||
3597: '] exists in ICX_CAT_ITEMS_TLP');
3598: RETURN TRUE;
3599: WHEN OTHERS THEN
3600: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsTLP-'||
3601: xErrLoc||' '||SQLERRM);
3602: raise ICX_POR_EXT_UTL.gException;
3603: END notExistItemsTLP;
3604:
3598: RETURN TRUE;
3599: WHEN OTHERS THEN
3600: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemsTLP-'||
3601: xErrLoc||' '||SQLERRM);
3602: raise ICX_POR_EXT_UTL.gException;
3603: END notExistItemsTLP;
3604:
3605: FUNCTION existCateoryItems(p_rt_item_id IN NUMBER,
3606: p_rt_category_id IN NUMBER)
3615: FROM icx_cat_category_items
3616: WHERE rt_item_id = p_rt_item_id
3617: AND rt_category_id = p_rt_category_id;
3618:
3619: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3620: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3621: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3622: '] exists in ICX_CAT_CATEGORY_ITEMS');
3623:
3625: RETURN TRUE;
3626: EXCEPTION
3627: when NO_DATA_FOUND then
3628: xResult:= 0;
3629: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3630: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3631: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3632: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3633: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3629: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3630: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3631: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3632: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3633: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3634: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3635: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3636: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3637: RETURN FALSE;
3635: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3636: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3637: RETURN FALSE;
3638: WHEN OTHERS THEN
3639: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCateoryItems-'||
3640: xErrLoc||' '||SQLERRM);
3641: raise ICX_POR_EXT_UTL.gException;
3642: END existCateoryItems;
3643:
3637: RETURN FALSE;
3638: WHEN OTHERS THEN
3639: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existCateoryItems-'||
3640: xErrLoc||' '||SQLERRM);
3641: raise ICX_POR_EXT_UTL.gException;
3642: END existCateoryItems;
3643:
3644: FUNCTION notExistCateoryItems(p_rt_item_id IN NUMBER,
3645: p_rt_category_id IN NUMBER)
3654: FROM icx_cat_category_items
3655: WHERE rt_item_id = p_rt_item_id
3656: AND rt_category_id = p_rt_category_id;
3657:
3658: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3659: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3660: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3661: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3662: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3658: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3659: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3660: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3661: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3662: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3663: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3664: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3665: '] does not exist in ICX_CAT_CATEGORY_ITEMS');
3666:
3668: RETURN FALSE;
3669: EXCEPTION
3670: when NO_DATA_FOUND then
3671: xResult:= 0;
3672: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3673: 'CategoryItems[RT_ITEM_ID: ' || p_rt_item_id ||
3674: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3675: '] exists in ICX_CAT_CATEGORY_ITEMS');
3676: RETURN TRUE;
3674: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3675: '] exists in ICX_CAT_CATEGORY_ITEMS');
3676: RETURN TRUE;
3677: WHEN OTHERS THEN
3678: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCateoryItems-'||
3679: xErrLoc||' '||SQLERRM);
3680: raise ICX_POR_EXT_UTL.gException;
3681: END notExistCateoryItems;
3682:
3676: RETURN TRUE;
3677: WHEN OTHERS THEN
3678: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistCateoryItems-'||
3679: xErrLoc||' '||SQLERRM);
3680: raise ICX_POR_EXT_UTL.gException;
3681: END notExistCateoryItems;
3682:
3683: FUNCTION existExtItemsTLP(p_rt_item_id IN NUMBER,
3684: p_rt_category_id IN NUMBER)
3694: WHERE rt_item_id = p_rt_item_id
3695: AND rt_category_id = p_rt_category_id
3696: AND ROWNUM = 1;
3697:
3698: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3699: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3700: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3701: '] exists in ICX_CAT_EXT_ITEMS_TLP');
3702:
3704: RETURN TRUE;
3705: EXCEPTION
3706: when NO_DATA_FOUND then
3707: xResult:= 0;
3708: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3709: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3710: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3711: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3712: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3708: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3709: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3710: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3711: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3712: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3713: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3714: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3715: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3716: RETURN FALSE;
3714: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3715: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3716: RETURN FALSE;
3717: WHEN OTHERS THEN
3718: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existExtItemsTLP-'||
3719: xErrLoc||' '||SQLERRM);
3720: raise ICX_POR_EXT_UTL.gException;
3721: END existExtItemsTLP;
3722:
3716: RETURN FALSE;
3717: WHEN OTHERS THEN
3718: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existExtItemsTLP-'||
3719: xErrLoc||' '||SQLERRM);
3720: raise ICX_POR_EXT_UTL.gException;
3721: END existExtItemsTLP;
3722:
3723: FUNCTION notExistExtItemsTLP(p_rt_item_id IN NUMBER,
3724: p_rt_category_id IN NUMBER)
3734: WHERE rt_item_id = p_rt_item_id
3735: AND rt_category_id = p_rt_category_id
3736: AND ROWNUM = 1;
3737:
3738: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3739: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3740: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3741: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3742: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3738: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3739: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3740: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3741: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3742: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3743: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3744: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3745: '] does not exist in ICX_CAT_EXT_ITEMS_TLP');
3746:
3748: RETURN FALSE;
3749: EXCEPTION
3750: when NO_DATA_FOUND then
3751: xResult:= 0;
3752: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3753: 'ExtItemsTLP[RT_ITEM_ID: ' || p_rt_item_id ||
3754: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3755: '] exists in ICX_CAT_EXT_ITEMS_TLP');
3756: RETURN TRUE;
3754: ', RT_CATEGORY_ID: ' || p_rt_category_id ||
3755: '] exists in ICX_CAT_EXT_ITEMS_TLP');
3756: RETURN TRUE;
3757: WHEN OTHERS THEN
3758: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistExtItemsTLP-'||
3759: xErrLoc||' '||SQLERRM);
3760: raise ICX_POR_EXT_UTL.gException;
3761: END notExistExtItemsTLP;
3762:
3756: RETURN TRUE;
3757: WHEN OTHERS THEN
3758: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistExtItemsTLP-'||
3759: xErrLoc||' '||SQLERRM);
3760: raise ICX_POR_EXT_UTL.gException;
3761: END notExistExtItemsTLP;
3762:
3763: FUNCTION existItemPrices(p_rt_item_id IN NUMBER,
3764: p_org_id IN VARCHAR2,
3829: xResult:= 0;
3830: END;
3831:
3832: IF xResult = 1 THEN
3833: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3834: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3835: ', ORG_ID: ' || p_org_id ||
3836: ', PRICE_TYPE: ' || p_price_type ||
3837: ', ACTIVE_FLAG: ' || p_active_flag ||
3855:
3856: xErrLoc:= 200;
3857: RETURN TRUE;
3858: ELSE
3859: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3860: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3861: ', ORG_ID: ' || p_org_id ||
3862: ', PRICE_TYPE: ' || p_price_type ||
3863: ', ACTIVE_FLAG: ' || p_active_flag ||
3877: ', CONTRACT_NUM: ' || p_contract_num ||
3878: ', CONTRACT_LINE_NUM: ' || p_contract_line_num ||
3879: ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
3880: '] does not exist in ICX_CAT_ITEM_PRICES');
3881: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3882: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3883: ', ORG_ID: ' || p_org_id ||
3884: ', PRICE_TYPE: ' || p_price_type ||
3885: ', ACTIVE_FLAG: ' || p_active_flag ||
3903: RETURN FALSE;
3904: END IF;
3905: EXCEPTION
3906: WHEN OTHERS THEN
3907: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
3908: xErrLoc||' '||SQLERRM);
3909: raise ICX_POR_EXT_UTL.gException;
3910: END existItemPrices;
3911:
3905: EXCEPTION
3906: WHEN OTHERS THEN
3907: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
3908: xErrLoc||' '||SQLERRM);
3909: raise ICX_POR_EXT_UTL.gException;
3910: END existItemPrices;
3911:
3912: FUNCTION notExistItemPrices(p_rt_item_id IN NUMBER,
3913: p_org_id IN VARCHAR2,
3938: AND template_id = p_template_id
3939: AND template_line_id = p_template_line_id
3940: AND inventory_item_id = p_inventory_item_id;
3941:
3942: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3943: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3944: ', ORG_ID: ' || p_org_id ||
3945: ', PRICE_TYPE: ' || p_price_type ||
3946: ', ACTIVE_FLAG: ' || p_active_flag ||
3950: ', TEMPLATE_ID: ' || p_template_id ||
3951: ', TEMPLATE_LINE_ID: ' || p_template_line_id ||
3952: ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3953: '] exists in ICX_CAT_ITEM_PRICES');
3954: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
3955: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3956: ', ORG_ID: ' || p_org_id ||
3957: ', PRICE_TYPE: ' || p_price_type ||
3958: ', ACTIVE_FLAG: ' || p_active_flag ||
3968: RETURN FALSE;
3969: EXCEPTION
3970: when NO_DATA_FOUND then
3971: xResult:= 0;
3972: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
3973: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
3974: ', ORG_ID: ' || p_org_id ||
3975: ', PRICE_TYPE: ' || p_price_type ||
3976: ', ACTIVE_FLAG: ' || p_active_flag ||
3982: ', INVENTORY_ITEM_ID: ' || p_inventory_item_id ||
3983: '] does not exist in ICX_CAT_ITEM_PRICES');
3984: RETURN TRUE;
3985: WHEN OTHERS THEN
3986: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemPrices-'||
3987: xErrLoc||' '||SQLERRM);
3988: raise ICX_POR_EXT_UTL.gException;
3989: END notExistItemPrices;
3990:
3984: RETURN TRUE;
3985: WHEN OTHERS THEN
3986: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.notExistItemPrices-'||
3987: xErrLoc||' '||SQLERRM);
3988: raise ICX_POR_EXT_UTL.gException;
3989: END notExistItemPrices;
3990:
3991: -- FPJ Bug# 3007068 sosingha: Extractor Changes For Kit Support Project
3992: -- Add new function checkSuggestedQuantity to check if the suggested_quantity extracted matches
4034: xResult:= 0;
4035: END;
4036:
4037: IF xResult = 1 THEN
4038: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
4039: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4040: ', ORG_ID: ' || p_org_id ||
4041: ', PRICE_TYPE: ' || p_price_type ||
4042: ', ACTIVE_FLAG: ' || p_active_flag ||
4050:
4051: xErrLoc:= 200;
4052: RETURN TRUE;
4053: ELSE
4054: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ANLYS_LEVEL,
4055: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4056: ', ORG_ID: ' || p_org_id ||
4057: ', PRICE_TYPE: ' || p_price_type ||
4058: ', ACTIVE_FLAG: ' || p_active_flag ||
4062: ', MTL_CATEGORY_ID: ' || p_mtl_category_id ||
4063: ', SUGGESTED_QUANTITY: ' || p_suggested_quantity ||
4064: ', LOCAL_RT_ITEM_ID: ' || p_local_rt_item_id ||
4065: '] does not exist in ICX_CAT_ITEM_PRICES');
4066: ICX_POR_EXT_UTL.debug(ICX_POR_EXT_UTL.ERROR_LEVEL,
4067: 'ItemPrices[RT_ITEM_ID: ' || p_rt_item_id ||
4068: ', ORG_ID: ' || p_org_id ||
4069: ', PRICE_TYPE: ' || p_price_type ||
4070: ', ACTIVE_FLAG: ' || p_active_flag ||
4078: RETURN FALSE;
4079: END IF;
4080: EXCEPTION
4081: WHEN OTHERS THEN
4082: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
4083: xErrLoc||' '||SQLERRM);
4084: raise ICX_POR_EXT_UTL.gException;
4085: END checkSuggestedQuantity;
4086:
4080: EXCEPTION
4081: WHEN OTHERS THEN
4082: ICX_POR_EXT_UTL.pushError('ICX_POR_EXT_TEST.existItemPrices-'||
4083: xErrLoc||' '||SQLERRM);
4084: raise ICX_POR_EXT_UTL.gException;
4085: END checkSuggestedQuantity;
4086:
4087: END ICX_POR_EXT_TEST;