[Home] [Help]
1: PACKAGE BODY PO_UDA_DATA_UTIL AS
2: /* $Header: PO_UDA_DATA_UTIL.plb 120.25.12020000.9 2013/04/18 11:48:43 inagdeo ship $ */
3:
4: d_pkg_name CONSTANT varchar2(50) :=
5: PO_LOG.get_package_base('PO_UDA_DATA_UTIL');
6:
7: TYPE varchar_tbl is table of varchar2(100) index by binary_integer;
8: user_col_name_tbl varchar_tbl;
9: appl_col_name_tbl varchar_tbl;
27: BEGIN
28:
29: d_progress := 10;
30:
31: IF (PO_LOG.d_proc) THEN
32: PO_LOG.proc_begin(d_module);
33: PO_LOG.proc_begin(d_module, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
34: END IF;
35:
28:
29: d_progress := 10;
30:
31: IF (PO_LOG.d_proc) THEN
32: PO_LOG.proc_begin(d_module);
33: PO_LOG.proc_begin(d_module, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
34: END IF;
35:
36: -- Contition for Attribute Group Id
29: d_progress := 10;
30:
31: IF (PO_LOG.d_proc) THEN
32: PO_LOG.proc_begin(d_module);
33: PO_LOG.proc_begin(d_module, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
34: END IF;
35:
36: -- Contition for Attribute Group Id
37: IF p_attr_grp_or_page_type = 'ATTRIBUTE_GROUP_ID' THEN
49: AND ROWNUM < 2;
50:
51: d_progress := 30;
52:
53: IF PO_LOG.d_stmt THEN
54: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
55: END IF;
56:
57: IF l_count >= 1 THEN
50:
51: d_progress := 30;
52:
53: IF PO_LOG.d_stmt THEN
54: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
55: END IF;
56:
57: IF l_count >= 1 THEN
58:
63: WHERE TEMPLATE_ID = p_template_id
64: AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id;
65: x_found_status := 'true';
66:
67: IF PO_LOG.d_stmt THEN
68: PO_LOG.stmt(d_module, d_progress, 'x_usage_code', x_usage_code);
69: END IF;
70:
71:
64: AND ATTRIBUTE_GROUP_ID = p_attr_grp_or_page_id;
65: x_found_status := 'true';
66:
67: IF PO_LOG.d_stmt THEN
68: PO_LOG.stmt(d_module, d_progress, 'x_usage_code', x_usage_code);
69: END IF;
70:
71:
72: END IF;
76: EXCEPTION
77: WHEN OTHERS THEN
78: d_progress := 50;
79: x_found_status := 'false';
80: IF PO_LOG.d_stmt THEN
81: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
82: END IF;
83: END;
84:
77: WHEN OTHERS THEN
78: d_progress := 50;
79: x_found_status := 'false';
80: IF PO_LOG.d_stmt THEN
81: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
82: END IF;
83: END;
84:
85: -- Contition for Page Id
86: ELSIF p_attr_grp_or_page_type = 'PAGE_ID' THEN
87:
88: d_progress := 60;
89:
90: IF PO_LOG.d_stmt THEN
91: PO_LOG.stmt(d_module, d_progress, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
92: END IF;
93:
94: BEGIN
87:
88: d_progress := 60;
89:
90: IF PO_LOG.d_stmt THEN
91: PO_LOG.stmt(d_module, d_progress, 'p_attr_grp_or_page_type', p_attr_grp_or_page_type);
92: END IF;
93:
94: BEGIN
95: d_progress := 70;
103: AND t.TEMPLATE_ID = p_template_id
104: AND p.CLASSIFICATION_CODE = p_template_id || ''
105: AND ROWNUM < 2;
106:
107: IF PO_LOG.d_stmt THEN
108: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
109: END IF;
110:
111: d_progress := 80;
104: AND p.CLASSIFICATION_CODE = p_template_id || ''
105: AND ROWNUM < 2;
106:
107: IF PO_LOG.d_stmt THEN
108: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
109: END IF;
110:
111: d_progress := 80;
112:
113: IF l_count >= 1 THEN
114: x_found_status := 'true';
115: END IF;
116:
117: IF PO_LOG.d_stmt THEN
118: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
119: END IF;
120: d_progress := 90;
121: EXCEPTION
114: x_found_status := 'true';
115: END IF;
116:
117: IF PO_LOG.d_stmt THEN
118: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
119: END IF;
120: d_progress := 90;
121: EXCEPTION
122: WHEN OTHERS THEN
121: EXCEPTION
122: WHEN OTHERS THEN
123: d_progress := 100;
124: x_found_status := 'false';
125: IF PO_LOG.d_stmt THEN
126: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
127: END IF;
128: END;
129:
122: WHEN OTHERS THEN
123: d_progress := 100;
124: x_found_status := 'false';
125: IF PO_LOG.d_stmt THEN
126: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
127: END IF;
128: END;
129:
130: END IF;
130: END IF;
131:
132: d_progress := 110;
133:
134: IF (PO_LOG.d_exc) THEN
135: PO_LOG.exc(d_module, d_progress, x_found_status );
136: PO_LOG.proc_end(d_module);
137: END IF;
138:
131:
132: d_progress := 110;
133:
134: IF (PO_LOG.d_exc) THEN
135: PO_LOG.exc(d_module, d_progress, x_found_status );
136: PO_LOG.proc_end(d_module);
137: END IF;
138:
139: END CHECK_ATTR_OR_PAGE_ID_VALID;
132: d_progress := 110;
133:
134: IF (PO_LOG.d_exc) THEN
135: PO_LOG.exc(d_module, d_progress, x_found_status );
136: PO_LOG.proc_end(d_module);
137: END IF;
138:
139: END CHECK_ATTR_OR_PAGE_ID_VALID;
140:
445: --Bug9441838: Added variable to hold the sequence value
446: x_association_id NUMBER;
447: BEGIN
448:
449: IF (PO_LOG.d_proc) THEN
450: PO_LOG.proc_begin(d_module);
451: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
452: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
453: END IF;
446: x_association_id NUMBER;
447: BEGIN
448:
449: IF (PO_LOG.d_proc) THEN
450: PO_LOG.proc_begin(d_module);
451: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
452: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
453: END IF;
454:
447: BEGIN
448:
449: IF (PO_LOG.d_proc) THEN
450: PO_LOG.proc_begin(d_module);
451: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
452: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
453: END IF;
454:
455: d_progress := 10;
448:
449: IF (PO_LOG.d_proc) THEN
450: PO_LOG.proc_begin(d_module);
451: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
452: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
453: END IF;
454:
455: d_progress := 10;
456: -- Fetching the internal name and entity code for the template
459: FROM PO_UDA_AG_TEMPLATES
460: WHERE TEMPLATE_ID = p_template_id
461: AND ROWNUM = 1;
462:
463: IF PO_LOG.d_stmt THEN
464: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
465: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
466: END IF;
467:
460: WHERE TEMPLATE_ID = p_template_id
461: AND ROWNUM = 1;
462:
463: IF PO_LOG.d_stmt THEN
464: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
465: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
466: END IF;
467:
468: d_progress := 20;
461: AND ROWNUM = 1;
462:
463: IF PO_LOG.d_stmt THEN
464: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
465: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
466: END IF;
467:
468: d_progress := 20;
469: SELECT OBJECT_ID
476: FROM EGO_DATA_LEVEL_B
477: WHERE DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
478: AND ATTR_GROUP_TYPE = v_entity_type;
479:
480: IF PO_LOG.d_stmt THEN
481: PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
482: END IF;
483:
484: d_progress := 30;
477: WHERE DATA_LEVEL_NAME NOT LIKE '%ARCHIVE'
478: AND ATTR_GROUP_TYPE = v_entity_type;
479:
480: IF PO_LOG.d_stmt THEN
481: PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
482: END IF;
483:
484: d_progress := 30;
485:
508: );
509:
510: d_progress := 40;
511:
512: IF PO_LOG.d_stmt THEN
513: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
514: END IF;
515:
516:
509:
510: d_progress := 40;
511:
512: IF PO_LOG.d_stmt THEN
513: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
514: END IF;
515:
516:
517: IF (PO_LOG.d_exc) THEN
513: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
514: END IF;
515:
516:
517: IF (PO_LOG.d_exc) THEN
518: PO_LOG.exc(d_module, d_progress, p_association_id );
519: PO_LOG.proc_end(d_module);
520: END IF;
521:
514: END IF;
515:
516:
517: IF (PO_LOG.d_exc) THEN
518: PO_LOG.exc(d_module, d_progress, p_association_id );
519: PO_LOG.proc_end(d_module);
520: END IF;
521:
522: EXCEPTION
515:
516:
517: IF (PO_LOG.d_exc) THEN
518: PO_LOG.exc(d_module, d_progress, p_association_id );
519: PO_LOG.proc_end(d_module);
520: END IF;
521:
522: EXCEPTION
523: WHEN OTHERS THEN
521:
522: EXCEPTION
523: WHEN OTHERS THEN
524: d_progress := 50;
525: IF PO_LOG.d_stmt THEN
526: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
527: END IF;
528: END CREATE_ASSOCIATION_ID;
529:
522: EXCEPTION
523: WHEN OTHERS THEN
524: d_progress := 50;
525: IF PO_LOG.d_stmt THEN
526: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
527: END IF;
528: END CREATE_ASSOCIATION_ID;
529:
530: PROCEDURE GET_ASSOCIATION_ID
542: d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
543: d_progress NUMBER;
544: BEGIN
545:
546: IF (PO_LOG.d_proc) THEN
547: PO_LOG.proc_begin(d_module);
548: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
549: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
550: END IF;
543: d_progress NUMBER;
544: BEGIN
545:
546: IF (PO_LOG.d_proc) THEN
547: PO_LOG.proc_begin(d_module);
548: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
549: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
550: END IF;
551:
544: BEGIN
545:
546: IF (PO_LOG.d_proc) THEN
547: PO_LOG.proc_begin(d_module);
548: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
549: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
550: END IF;
551:
552: d_progress := 10;
545:
546: IF (PO_LOG.d_proc) THEN
547: PO_LOG.proc_begin(d_module);
548: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
549: PO_LOG.proc_begin(d_module, 'p_attribute_grp_id', p_attribute_grp_id);
550: END IF;
551:
552: d_progress := 10;
553: -- Fetching the internal name and entity code for the template
556: FROM PO_UDA_AG_TEMPLATES
557: WHERE TEMPLATE_ID = p_template_id
558: AND ROWNUM = 1;
559:
560: IF PO_LOG.d_stmt THEN
561: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
562: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
563: END IF;
564:
557: WHERE TEMPLATE_ID = p_template_id
558: AND ROWNUM = 1;
559:
560: IF PO_LOG.d_stmt THEN
561: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
562: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
563: END IF;
564:
565: d_progress := 20;
558: AND ROWNUM = 1;
559:
560: IF PO_LOG.d_stmt THEN
561: PO_LOG.stmt(d_module, d_progress, 'v_internal_name', p_template_id || '');
562: PO_LOG.stmt(d_module, d_progress, 'v_entity_type', v_entity_type);
563: END IF;
564:
565: d_progress := 20;
566: SELECT OBJECT_ID
567: INTO v_object_id
568: FROM FND_OBJECTS
569: WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
570:
571: IF PO_LOG.d_stmt THEN
572: PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
573: END IF;
574:
575: d_progress := 30;
568: FROM FND_OBJECTS
569: WHERE OBJ_NAME = g_object_dff_tl(v_entity_type).l_object_name;
570:
571: IF PO_LOG.d_stmt THEN
572: PO_LOG.stmt(d_module, d_progress, 'v_object_id', v_object_id);
573: END IF;
574:
575: d_progress := 30;
576: p_association_id := ego_ext_fwk_pub.Get_Association_Id_From_PKs(v_object_id , p_template_id || '', p_attribute_grp_id);
576: p_association_id := ego_ext_fwk_pub.Get_Association_Id_From_PKs(v_object_id , p_template_id || '', p_attribute_grp_id);
577:
578: d_progress := 40;
579:
580: IF PO_LOG.d_stmt THEN
581: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
582: END IF;
583:
584:
577:
578: d_progress := 40;
579:
580: IF PO_LOG.d_stmt THEN
581: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
582: END IF;
583:
584:
585: IF (PO_LOG.d_exc) THEN
581: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
582: END IF;
583:
584:
585: IF (PO_LOG.d_exc) THEN
586: PO_LOG.exc(d_module, d_progress, p_association_id );
587: PO_LOG.proc_end(d_module);
588: END IF;
589:
582: END IF;
583:
584:
585: IF (PO_LOG.d_exc) THEN
586: PO_LOG.exc(d_module, d_progress, p_association_id );
587: PO_LOG.proc_end(d_module);
588: END IF;
589:
590: EXCEPTION
583:
584:
585: IF (PO_LOG.d_exc) THEN
586: PO_LOG.exc(d_module, d_progress, p_association_id );
587: PO_LOG.proc_end(d_module);
588: END IF;
589:
590: EXCEPTION
591: WHEN OTHERS THEN
589:
590: EXCEPTION
591: WHEN OTHERS THEN
592: d_progress := 50;
593: IF PO_LOG.d_stmt THEN
594: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
595: END IF;
596: END GET_ASSOCIATION_ID;
597:
590: EXCEPTION
591: WHEN OTHERS THEN
592: d_progress := 50;
593: IF PO_LOG.d_stmt THEN
594: PO_LOG.stmt(d_module, d_progress, 'p_association_id', p_association_id);
595: END IF;
596: END GET_ASSOCIATION_ID;
597:
598:
609: d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
610: d_progress NUMBER;
611: BEGIN
612:
613: IF (PO_LOG.d_proc) THEN
614: PO_LOG.proc_begin(d_module);
615: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
616: PO_LOG.proc_begin(d_module, 'p_usage_code', p_usage_code);
617: END IF;
610: d_progress NUMBER;
611: BEGIN
612:
613: IF (PO_LOG.d_proc) THEN
614: PO_LOG.proc_begin(d_module);
615: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
616: PO_LOG.proc_begin(d_module, 'p_usage_code', p_usage_code);
617: END IF;
618:
611: BEGIN
612:
613: IF (PO_LOG.d_proc) THEN
614: PO_LOG.proc_begin(d_module);
615: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
616: PO_LOG.proc_begin(d_module, 'p_usage_code', p_usage_code);
617: END IF;
618:
619: d_progress := 10;
612:
613: IF (PO_LOG.d_proc) THEN
614: PO_LOG.proc_begin(d_module);
615: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
616: PO_LOG.proc_begin(d_module, 'p_usage_code', p_usage_code);
617: END IF;
618:
619: d_progress := 10;
620:
625: AND b.template_id = p_template_id
626: AND u.ATTRIBUTE_CATEGORY = p_usage_code
627: AND ROWNUM < 2;
628:
629: IF PO_LOG.d_stmt THEN
630: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
631: END IF;
632:
633: d_progress := 20;
626: AND u.ATTRIBUTE_CATEGORY = p_usage_code
627: AND ROWNUM < 2;
628:
629: IF PO_LOG.d_stmt THEN
630: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
631: END IF;
632:
633: d_progress := 20;
634:
635: IF l_count >= 1 THEN
636: x_valid_status := 'true';
637: END IF;
638:
639: IF PO_LOG.d_stmt THEN
640: PO_LOG.stmt(d_module, d_progress, 'x_valid_status', x_valid_status);
641: END IF;
642:
643:
636: x_valid_status := 'true';
637: END IF;
638:
639: IF PO_LOG.d_stmt THEN
640: PO_LOG.stmt(d_module, d_progress, 'x_valid_status', x_valid_status);
641: END IF;
642:
643:
644: d_progress := 30;
642:
643:
644: d_progress := 30;
645:
646: IF (PO_LOG.d_exc) THEN
647: PO_LOG.exc(d_module, d_progress, x_valid_status );
648: PO_LOG.proc_end(d_module);
649: END IF;
650:
643:
644: d_progress := 30;
645:
646: IF (PO_LOG.d_exc) THEN
647: PO_LOG.exc(d_module, d_progress, x_valid_status );
648: PO_LOG.proc_end(d_module);
649: END IF;
650:
651: EXCEPTION
644: d_progress := 30;
645:
646: IF (PO_LOG.d_exc) THEN
647: PO_LOG.exc(d_module, d_progress, x_valid_status );
648: PO_LOG.proc_end(d_module);
649: END IF;
650:
651: EXCEPTION
652: WHEN OTHERS THEN
652: WHEN OTHERS THEN
653: d_progress := 40;
654: x_valid_status := 'false';
655:
656: IF (PO_LOG.d_exc) THEN
657: PO_LOG.exc(d_module, d_progress, x_valid_status );
658: PO_LOG.proc_end(d_module);
659: END IF;
660:
653: d_progress := 40;
654: x_valid_status := 'false';
655:
656: IF (PO_LOG.d_exc) THEN
657: PO_LOG.exc(d_module, d_progress, x_valid_status );
658: PO_LOG.proc_end(d_module);
659: END IF;
660:
661:
654: x_valid_status := 'false';
655:
656: IF (PO_LOG.d_exc) THEN
657: PO_LOG.exc(d_module, d_progress, x_valid_status );
658: PO_LOG.proc_end(d_module);
659: END IF;
660:
661:
662:
689:
690:
691: BEGIN
692:
693: IF (PO_LOG.d_proc) THEN
694: PO_LOG.proc_begin(d_module);
695: PO_LOG.proc_begin(d_module, 'p_sql_str', p_sql_str);
696: END IF;
697:
690:
691: BEGIN
692:
693: IF (PO_LOG.d_proc) THEN
694: PO_LOG.proc_begin(d_module);
695: PO_LOG.proc_begin(d_module, 'p_sql_str', p_sql_str);
696: END IF;
697:
698: d_progress := 10;
691: BEGIN
692:
693: IF (PO_LOG.d_proc) THEN
694: PO_LOG.proc_begin(d_module);
695: PO_LOG.proc_begin(d_module, 'p_sql_str', p_sql_str);
696: END IF;
697:
698: d_progress := 10;
699:
704: x_attr_grp_ids := PO_TBL_NUMBER();
705:
706: d_progress := 20;
707:
708: IF PO_LOG.d_stmt THEN
709: PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
710: END IF;
711:
712: -- Loop to fetch the PL SQL Table of attribute groups
705:
706: d_progress := 20;
707:
708: IF PO_LOG.d_stmt THEN
709: PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
710: END IF;
711:
712: -- Loop to fetch the PL SQL Table of attribute groups
713: LOOP
717: v_count_attr_grp_ids := v_count_attr_grp_ids + 1;
718: x_attr_grp_ids.extend(1);
719: x_attr_grp_ids(v_count_attr_grp_ids) := v_attr_group_id;
720:
721: IF PO_LOG.d_stmt THEN
722: PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
723: END IF;
724:
725: ELSE
718: x_attr_grp_ids.extend(1);
719: x_attr_grp_ids(v_count_attr_grp_ids) := v_attr_group_id;
720:
721: IF PO_LOG.d_stmt THEN
722: PO_LOG.stmt(d_module, d_progress, 'v_attr_group_id', v_attr_group_id);
723: END IF;
724:
725: ELSE
726: EXIT;
731: DBMS_SQL.CLOSE_CURSOR(v_query_hdl2);
732:
733: d_progress := 30;
734:
735: IF PO_LOG.d_stmt THEN
736: PO_LOG.stmt(d_module, d_progress, 'v_count_attr_grp_ids', v_count_attr_grp_ids);
737: END IF;
738:
739: IF v_count_attr_grp_ids = 0 THEN
732:
733: d_progress := 30;
734:
735: IF PO_LOG.d_stmt THEN
736: PO_LOG.stmt(d_module, d_progress, 'v_count_attr_grp_ids', v_count_attr_grp_ids);
737: END IF;
738:
739: IF v_count_attr_grp_ids = 0 THEN
740:
740:
741: x_found_status := 'false';
742: d_progress := 40;
743:
744: IF PO_LOG.d_stmt THEN
745: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
746: END IF;
747:
748: ELSIF v_count_attr_grp_ids = 1 THEN
741: x_found_status := 'false';
742: d_progress := 40;
743:
744: IF PO_LOG.d_stmt THEN
745: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
746: END IF;
747:
748: ELSIF v_count_attr_grp_ids = 1 THEN
749:
752: x_found_status := 'true';
753:
754: d_progress := 50;
755:
756: IF PO_LOG.d_stmt THEN
757: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
758: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
759: END IF;
760:
753:
754: d_progress := 50;
755:
756: IF PO_LOG.d_stmt THEN
757: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
758: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
759: END IF;
760:
761: ELSE
754: d_progress := 50;
755:
756: IF PO_LOG.d_stmt THEN
757: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
758: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
759: END IF;
760:
761: ELSE
762:
767: v_sql_pg := v_sql_pg || ' WHERE PG1.ASSOCIATION_ID = ATTR1.ASSOCIATION_ID AND ATTR_GROUP_ID NOT IN (' || p_sql_str || '))';
768:
769: d_progress := 60;
770:
771: IF PO_LOG.d_stmt THEN
772: PO_LOG.stmt(d_module, d_progress, 'v_sql_pg', v_sql_pg);
773: END IF;
774:
775: v_query_hdl1 := DBMS_SQL.open_cursor;
768:
769: d_progress := 60;
770:
771: IF PO_LOG.d_stmt THEN
772: PO_LOG.stmt(d_module, d_progress, 'v_sql_pg', v_sql_pg);
773: END IF;
774:
775: v_query_hdl1 := DBMS_SQL.open_cursor;
776: DBMS_SQL.parse(v_query_hdl1, v_sql_pg, DBMS_SQL.native);
779: v_rows_processed := DBMS_SQL.EXECUTE(v_query_hdl1);
780:
781: d_progress := 70;
782:
783: IF PO_LOG.d_stmt THEN
784: PO_LOG.stmt(d_module, d_progress, 'v_rows_processed', v_rows_processed);
785: END IF;
786:
787: -- Loop to fetch the actual page id
780:
781: d_progress := 70;
782:
783: IF PO_LOG.d_stmt THEN
784: PO_LOG.stmt(d_module, d_progress, 'v_rows_processed', v_rows_processed);
785: END IF;
786:
787: -- Loop to fetch the actual page id
788: LOOP
804: DBMS_SQL.CLOSE_CURSOR(v_query_hdl1);
805:
806: d_progress := 80;
807:
808: IF PO_LOG.d_stmt THEN
809: PO_LOG.stmt(d_module, d_progress, 'v_count_attr_pg_ids', v_count_attr_pg_ids);
810: END IF;
811:
812: IF v_count_attr_pg_ids = 1 THEN
805:
806: d_progress := 80;
807:
808: IF PO_LOG.d_stmt THEN
809: PO_LOG.stmt(d_module, d_progress, 'v_count_attr_pg_ids', v_count_attr_pg_ids);
810: END IF;
811:
812: IF v_count_attr_pg_ids = 1 THEN
813:
816: x_found_status := 'true';
817:
818: d_progress := 90;
819:
820: IF PO_LOG.d_stmt THEN
821: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
822: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
823: END IF;
824:
817:
818: d_progress := 90;
819:
820: IF PO_LOG.d_stmt THEN
821: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
822: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
823: END IF;
824:
825: ELSE
818: d_progress := 90;
819:
820: IF PO_LOG.d_stmt THEN
821: PO_LOG.stmt(d_module, d_progress, 'x_id', x_id);
822: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
823: END IF;
824:
825: ELSE
826: x_found_status := 'false';
825: ELSE
826: x_found_status := 'false';
827: d_progress := 100;
828:
829: IF PO_LOG.d_stmt THEN
830: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
831: END IF;
832:
833: END IF;
826: x_found_status := 'false';
827: d_progress := 100;
828:
829: IF PO_LOG.d_stmt THEN
830: PO_LOG.stmt(d_module, d_progress, 'x_found_status', x_found_status);
831: END IF;
832:
833: END IF;
834:
834:
835: END IF;
836: EXCEPTION
837: WHEN OTHERS THEN
838: IF (PO_LOG.d_exc) THEN
839: d_progress := 110;
840: PO_LOG.exc(d_module, d_progress, x_found_status );
841: PO_LOG.proc_end(d_module);
842: END IF;
836: EXCEPTION
837: WHEN OTHERS THEN
838: IF (PO_LOG.d_exc) THEN
839: d_progress := 110;
840: PO_LOG.exc(d_module, d_progress, x_found_status );
841: PO_LOG.proc_end(d_module);
842: END IF;
843: END GET_ATTR_OR_PAGE_ID;
844:
837: WHEN OTHERS THEN
838: IF (PO_LOG.d_exc) THEN
839: d_progress := 110;
840: PO_LOG.exc(d_module, d_progress, x_found_status );
841: PO_LOG.proc_end(d_module);
842: END IF;
843: END GET_ATTR_OR_PAGE_ID;
844:
845: -------------------------------------------------------------------------------
951: l_dynamic_lang_cursor DYNAMIC_CUR;
952:
953: BEGIN
954: d_progress := 001;
955: IF (PO_LOG.d_proc) THEN
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
952:
953: BEGIN
954: d_progress := 001;
955: IF (PO_LOG.d_proc) THEN
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
953: BEGIN
954: d_progress := 001;
955: IF (PO_LOG.d_proc) THEN
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961: PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
954: d_progress := 001;
955: IF (PO_LOG.d_proc) THEN
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961: PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
962: END IF;
955: IF (PO_LOG.d_proc) THEN
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961: PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
962: END IF;
963:
956: PO_LOG.proc_begin(d_module);
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961: PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
962: END IF;
963:
964: IF FND_API.To_Boolean(p_commit) THEN
957: PO_LOG.proc_begin(d_module, 'from_template_id', from_template_id);
958: PO_LOG.proc_begin(d_module, 'to_template_id', to_template_id);
959: PO_LOG.proc_begin(d_module, 'copy_attribute_groups', copy_attribute_groups);
960: PO_LOG.proc_begin(d_module, 'attribute_group_table', attribute_group_table);
961: PO_LOG.proc_begin(d_module, 'p_commit', p_commit);
962: END IF;
963:
964: IF FND_API.To_Boolean(p_commit) THEN
965: SAVEPOINT Copy_Attrs_Data_SP;
974: INTO to_attr_group_type
975: FROM PO_UDA_AG_TEMPLATES
976: WHERE TEMPLATE_ID = to_template_id;
977:
978: IF PO_LOG.d_stmt THEN
979: PO_LOG.stmt(d_module,d_progress,'from_attr_group_Type',from_attr_group_Type);
980: PO_LOG.stmt(d_module,d_progress,'to_attr_group_Type',to_attr_group_Type);
981: END IF;
982:
975: FROM PO_UDA_AG_TEMPLATES
976: WHERE TEMPLATE_ID = to_template_id;
977:
978: IF PO_LOG.d_stmt THEN
979: PO_LOG.stmt(d_module,d_progress,'from_attr_group_Type',from_attr_group_Type);
980: PO_LOG.stmt(d_module,d_progress,'to_attr_group_Type',to_attr_group_Type);
981: END IF;
982:
983: l_from_object_cr := g_object_dff_tl(from_attr_group_Type);
976: WHERE TEMPLATE_ID = to_template_id;
977:
978: IF PO_LOG.d_stmt THEN
979: PO_LOG.stmt(d_module,d_progress,'from_attr_group_Type',from_attr_group_Type);
980: PO_LOG.stmt(d_module,d_progress,'to_attr_group_Type',to_attr_group_Type);
981: END IF;
982:
983: l_from_object_cr := g_object_dff_tl(from_attr_group_Type);
984: l_to_object_cr := g_object_dff_tl(to_attr_group_Type);
1022: END IF;
1023: l_index := from_pk_col_value_pairs.NEXT(l_index);
1024: END LOOP;
1025:
1026: IF PO_LOG.d_stmt THEN
1027: PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1028: PO_LOG.stmt(d_module,d_progress,'l_where_not_in_sql',l_where_not_in_sql);
1029: END IF;
1030:
1023: l_index := from_pk_col_value_pairs.NEXT(l_index);
1024: END LOOP;
1025:
1026: IF PO_LOG.d_stmt THEN
1027: PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1028: PO_LOG.stmt(d_module,d_progress,'l_where_not_in_sql',l_where_not_in_sql);
1029: END IF;
1030:
1031: d_progress := 020;
1024: END LOOP;
1025:
1026: IF PO_LOG.d_stmt THEN
1027: PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1028: PO_LOG.stmt(d_module,d_progress,'l_where_not_in_sql',l_where_not_in_sql);
1029: END IF;
1030:
1031: d_progress := 020;
1032:
1040: l_index := 0;
1041: l_index := to_pk_col_value_pairs.FIRST;
1042: WHILE (l_index <= to_pk_col_value_pairs.LAST)
1043: LOOP
1044: IF PO_LOG.d_stmt THEN
1045: PO_LOG.stmt(d_module,d_progress,'to_pk_col_value_pairs(l_index).NAME' , to_pk_col_value_pairs(l_index).NAME);
1046: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK1_COLUMN_NAME ' , l_to_object.PK1_COLUMN_NAME);
1047: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK2_COLUMN_NAME ' , l_to_object.PK2_COLUMN_NAME);
1048: END IF ;
1041: l_index := to_pk_col_value_pairs.FIRST;
1042: WHILE (l_index <= to_pk_col_value_pairs.LAST)
1043: LOOP
1044: IF PO_LOG.d_stmt THEN
1045: PO_LOG.stmt(d_module,d_progress,'to_pk_col_value_pairs(l_index).NAME' , to_pk_col_value_pairs(l_index).NAME);
1046: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK1_COLUMN_NAME ' , l_to_object.PK1_COLUMN_NAME);
1047: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK2_COLUMN_NAME ' , l_to_object.PK2_COLUMN_NAME);
1048: END IF ;
1049: IF (((l_to_object.PK1_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK1_COLUMN_NAME))) OR
1042: WHILE (l_index <= to_pk_col_value_pairs.LAST)
1043: LOOP
1044: IF PO_LOG.d_stmt THEN
1045: PO_LOG.stmt(d_module,d_progress,'to_pk_col_value_pairs(l_index).NAME' , to_pk_col_value_pairs(l_index).NAME);
1046: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK1_COLUMN_NAME ' , l_to_object.PK1_COLUMN_NAME);
1047: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK2_COLUMN_NAME ' , l_to_object.PK2_COLUMN_NAME);
1048: END IF ;
1049: IF (((l_to_object.PK1_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK1_COLUMN_NAME))) OR
1050: ((l_to_object.PK2_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK2_COLUMN_NAME))) OR
1043: LOOP
1044: IF PO_LOG.d_stmt THEN
1045: PO_LOG.stmt(d_module,d_progress,'to_pk_col_value_pairs(l_index).NAME' , to_pk_col_value_pairs(l_index).NAME);
1046: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK1_COLUMN_NAME ' , l_to_object.PK1_COLUMN_NAME);
1047: PO_LOG.stmt(d_module,d_progress,'l_to_object.PK2_COLUMN_NAME ' , l_to_object.PK2_COLUMN_NAME);
1048: END IF ;
1049: IF (((l_to_object.PK1_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK1_COLUMN_NAME))) OR
1050: ((l_to_object.PK2_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK2_COLUMN_NAME))) OR
1051: ((l_to_object.PK3_COLUMN_NAME IS NOT NULL) AND (to_pk_col_value_pairs(l_index).NAME = Upper(l_to_object.PK3_COLUMN_NAME))) OR
1065: l_index := to_pk_col_value_pairs.NEXT(l_index);
1066: END LOOP;
1067:
1068: d_progress := 020;
1069: IF PO_LOG.d_stmt THEN
1070: PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
1071: PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
1072: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1073: END IF;
1066: END LOOP;
1067:
1068: d_progress := 020;
1069: IF PO_LOG.d_stmt THEN
1070: PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
1071: PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
1072: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1073: END IF;
1074: -----------------------------------------------------------------------------
1067:
1068: d_progress := 020;
1069: IF PO_LOG.d_stmt THEN
1070: PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
1071: PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
1072: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1073: END IF;
1074: -----------------------------------------------------------------------------
1075: -- Columns which are not to be copied from Source to Destination.
1068: d_progress := 020;
1069: IF PO_LOG.d_stmt THEN
1070: PO_LOG.stmt(d_module,d_progress,'l_insert_pk_sql',l_insert_pk_sql);
1071: PO_LOG.stmt(d_module,d_progress,'l_select_pk_sql',l_select_pk_sql);
1072: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1073: END IF;
1074: -----------------------------------------------------------------------------
1075: -- Columns which are not to be copied from Source to Destination.
1076: -----------------------------------------------------------------------------
1169: l_b_table_upt_list := RTRIM(l_b_table_upt_list, ',');
1170: l_tl_table_upt_list := RTRIM(l_tl_table_upt_list, ',');
1171:
1172: d_progress := 030;
1173: IF PO_LOG.d_stmt THEN
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1170: l_tl_table_upt_list := RTRIM(l_tl_table_upt_list, ',');
1171:
1172: d_progress := 030;
1173: IF PO_LOG.d_stmt THEN
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1171:
1172: d_progress := 030;
1173: IF PO_LOG.d_stmt THEN
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1172: d_progress := 030;
1173: IF PO_LOG.d_stmt THEN
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1173: IF PO_LOG.d_stmt THEN
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181: PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1174: PO_LOG.stmt(d_module,d_progress,'l_b_table_col_names_list',l_b_table_col_names_list);
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181: PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1182: END IF;
1175: PO_LOG.stmt(d_module,d_progress,'l_s_b_table_col_names_list',l_s_b_table_col_names_list);
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181: PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1182: END IF;
1183:
1176: PO_LOG.stmt(d_module,d_progress,'l_d_b_table_col_names_list',l_d_b_table_col_names_list);
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181: PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1182: END IF;
1183:
1184: -----------------------------------------------------------------------------
1177: PO_LOG.stmt(d_module,d_progress,'l_tl_table_col_names_list',l_tl_table_col_names_list);
1178: PO_LOG.stmt(d_module,d_progress,'l_s_tl_table_col_names_list',l_s_tl_table_col_names_list);
1179: PO_LOG.stmt(d_module,d_progress,'l_d_tl_table_col_names_list',l_d_tl_table_col_names_list);
1180: PO_LOG.stmt(d_module,d_progress,'l_b_table_upt_list',l_b_table_upt_list);
1181: PO_LOG.stmt(d_module,d_progress,'l_tl_table_upt_list',l_tl_table_upt_list);
1182: END IF;
1183:
1184: -----------------------------------------------------------------------------
1185: -- Creating the Condition to Filter Records Based On the Attribute Group List.
1218: l_where_ag_sql := ' AND ATTR_GROUP_ID NOT IN ( '|| l_where_ag_sql || ' )';
1219: END IF ;
1220: END IF;
1221:
1222: IF PO_LOG.d_stmt THEN
1223: PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
1224: END IF;
1225: END IF ;
1226:
1219: END IF ;
1220: END IF;
1221:
1222: IF PO_LOG.d_stmt THEN
1223: PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
1224: END IF;
1225: END IF ;
1226:
1227: d_progress := 050;
1238: SELECT DISTINCT(DATA_LEVEL_ID)
1239: INTO l_from_data_level_id
1240: FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = from_attr_group_Type;
1241:
1242: IF PO_LOG.d_stmt THEN
1243: PO_LOG.stmt(d_module,d_progress,'From Data Level' || l_from_data_level_id);
1244: PO_LOG.stmt(d_module,d_progress,'To Data Level' || l_to_data_level_id);
1245: END IF;
1246:
1239: INTO l_from_data_level_id
1240: FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = from_attr_group_Type;
1241:
1242: IF PO_LOG.d_stmt THEN
1243: PO_LOG.stmt(d_module,d_progress,'From Data Level' || l_from_data_level_id);
1244: PO_LOG.stmt(d_module,d_progress,'To Data Level' || l_to_data_level_id);
1245: END IF;
1246:
1247: /*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
1240: FROM UDA_SETUP_METADATA_V WHERE Attr_group_type = from_attr_group_Type;
1241:
1242: IF PO_LOG.d_stmt THEN
1243: PO_LOG.stmt(d_module,d_progress,'From Data Level' || l_from_data_level_id);
1244: PO_LOG.stmt(d_module,d_progress,'To Data Level' || l_to_data_level_id);
1245: END IF;
1246:
1247: /*Bug 13892657 : Added UDA_TEMPALTE_ID in the insert statement*/
1248: l_base_table_copy_dml := 'MERGE INTO ' || l_to_object_cr.l_object_ext_b_table || ' destination' ||
1348: l_current_login_id ||
1349: ') ';
1350: d_progress := 060;
1351:
1352: IF PO_LOG.d_stmt THEN
1353: l_base_table_copy_dml_table := PO_TBL_VARCHAR4000 (SUBSTR(l_base_table_copy_dml,1 , 4000),
1354: SUBSTR(l_base_table_copy_dml,4001 , 4000),
1355: SUBSTR(l_base_table_copy_dml,8001 , 4000),
1356: SUBSTR(l_base_table_copy_dml,12001 , 4000));
1358: SUBSTR(l_tl_table_copy_dml,4001 , 4000),
1359: SUBSTR(l_tl_table_copy_dml,8001 , 4000),
1360: SUBSTR(l_tl_table_copy_dml,12001 , 4000));
1361:
1362: PO_LOG.stmt(d_module,d_progress,'l_base_table_copy_dml_table', l_base_table_copy_dml_table);
1363: PO_LOG.stmt(d_module,d_progress,'l_tl_table_copy_dml_table', l_tl_table_copy_dml_table);
1364: END IF;
1365:
1366: d_progress := 070;
1359: SUBSTR(l_tl_table_copy_dml,8001 , 4000),
1360: SUBSTR(l_tl_table_copy_dml,12001 , 4000));
1361:
1362: PO_LOG.stmt(d_module,d_progress,'l_base_table_copy_dml_table', l_base_table_copy_dml_table);
1363: PO_LOG.stmt(d_module,d_progress,'l_tl_table_copy_dml_table', l_tl_table_copy_dml_table);
1364: END IF;
1365:
1366: d_progress := 070;
1367: -----------------------------------------------------------------------------
1380: ' AND EXISTS (SELECT 1 from PO_UDA_AG_TEMPLATE_USAGES where template_id = ' || to_template_id ||
1381: ' AND attribute_group_id = ' ||
1382: '(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID)) ';
1383:
1384: IF PO_LOG.d_stmt THEN
1385: PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1386: END IF;
1387:
1388: OPEN l_dynamic_cursor FOR l_dynamic_sql;
1381: ' AND attribute_group_id = ' ||
1382: '(SELECT ' || l_to_object_cr.l_ag_mapping_column || ' FROM po_uda_mappings WHERE ' || l_from_object_cr.l_ag_mapping_column || ' = ATTR_GROUP_ID)) ';
1383:
1384: IF PO_LOG.d_stmt THEN
1385: PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1386: END IF;
1387:
1388: OPEN l_dynamic_cursor FOR l_dynamic_sql;
1389: LOOP
1395: -----------------------------------------------------------------------------
1396: -- Copy the TL Table Records only if Records Where Copied from Base Table.
1397: -----------------------------------------------------------------------------
1398: l_dynamic_Lang_sql := 'SELECT language from '|| l_from_object_cr.l_object_ext_tl_table || ' where extension_id = ' || l_copy_from_ext_id;
1399: IF PO_LOG.d_stmt THEN
1400: PO_LOG.stmt(d_module,d_progress,'l_dynamic_Lang_sql', l_dynamic_Lang_sql);
1401: END IF;
1402: -----------------------------------------------------------------------------
1403: -- Copy the all TL Table Records based on Langauge.
1396: -- Copy the TL Table Records only if Records Where Copied from Base Table.
1397: -----------------------------------------------------------------------------
1398: l_dynamic_Lang_sql := 'SELECT language from '|| l_from_object_cr.l_object_ext_tl_table || ' where extension_id = ' || l_copy_from_ext_id;
1399: IF PO_LOG.d_stmt THEN
1400: PO_LOG.stmt(d_module,d_progress,'l_dynamic_Lang_sql', l_dynamic_Lang_sql);
1401: END IF;
1402: -----------------------------------------------------------------------------
1403: -- Copy the all TL Table Records based on Langauge.
1404: -----------------------------------------------------------------------------
1422:
1423: x_return_status := FND_API.G_RET_STS_SUCCESS;
1424: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1425:
1426: IF (PO_LOG.d_proc) THEN
1427: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430: PO_LOG.proc_end(d_module);
1423: x_return_status := FND_API.G_RET_STS_SUCCESS;
1424: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1425:
1426: IF (PO_LOG.d_proc) THEN
1427: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430: PO_LOG.proc_end(d_module);
1431: END IF;
1424: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1425:
1426: IF (PO_LOG.d_proc) THEN
1427: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430: PO_LOG.proc_end(d_module);
1431: END IF;
1432:
1425:
1426: IF (PO_LOG.d_proc) THEN
1427: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430: PO_LOG.proc_end(d_module);
1431: END IF;
1432:
1433: EXCEPTION
1426: IF (PO_LOG.d_proc) THEN
1427: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1428: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1429: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1430: PO_LOG.proc_end(d_module);
1431: END IF;
1432:
1433: EXCEPTION
1434: WHEN FND_API.G_EXC_ERROR THEN
1436: IF FND_API.To_Boolean(p_commit) THEN
1437: ROLLBACK TO Copy_Attrs_Data_SP;
1438: END IF;
1439: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1440: IF (PO_LOG.d_exc) THEN
1441: PO_LOG.exc(d_module, d_progress, x_msg_data );
1442: PO_LOG.proc_end(d_module);
1443: END IF;
1444: x_return_status := FND_API.G_RET_STS_ERROR;
1437: ROLLBACK TO Copy_Attrs_Data_SP;
1438: END IF;
1439: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1440: IF (PO_LOG.d_exc) THEN
1441: PO_LOG.exc(d_module, d_progress, x_msg_data );
1442: PO_LOG.proc_end(d_module);
1443: END IF;
1444: x_return_status := FND_API.G_RET_STS_ERROR;
1445: WHEN OTHERS THEN
1438: END IF;
1439: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1440: IF (PO_LOG.d_exc) THEN
1441: PO_LOG.exc(d_module, d_progress, x_msg_data );
1442: PO_LOG.proc_end(d_module);
1443: END IF;
1444: x_return_status := FND_API.G_RET_STS_ERROR;
1445: WHEN OTHERS THEN
1446: PO_MESSAGE_S.add_exc_msg
1479:
1480: l_app_id NUMBER;
1481:
1482: BEGIN
1483: IF (PO_LOG.d_proc) THEN
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1480: l_app_id NUMBER;
1481:
1482: BEGIN
1483: IF (PO_LOG.d_proc) THEN
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1481:
1482: BEGIN
1483: IF (PO_LOG.d_proc) THEN
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489: PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1482: BEGIN
1483: IF (PO_LOG.d_proc) THEN
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489: PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1490: END IF;
1483: IF (PO_LOG.d_proc) THEN
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489: PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1490: END IF;
1491: d_progress := 001;
1484: PO_LOG.proc_begin(d_module);
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489: PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1490: END IF;
1491: d_progress := 001;
1492: -----------------------------------------------------------------------------
1485: PO_LOG.proc_begin(d_module, 'p_from_table_name', p_from_table_name);
1486: PO_LOG.proc_begin(d_module, 'p_from_cols_to_exclude_list',p_from_cols_to_exclude_list);
1487: PO_LOG.proc_begin(d_module, 'p_from_table_alias_prefix', p_from_table_alias_prefix);
1488: PO_LOG.proc_begin(d_module, 'p_to_table_name', p_to_table_name);
1489: PO_LOG.proc_begin(d_module, 'p_to_table_alias_prefix', p_to_table_alias_prefix);
1490: END IF;
1491: d_progress := 001;
1492: -----------------------------------------------------------------------------
1493: -- SQL to Fetch the Column List from SYS.ALL_TAB_COLUMNS Table.
1536: l_dynamic_sql := l_dynamic_sql||' ORDER BY COLUMN_NAME';
1537:
1538: l_in_update_mode := (p_to_table_name IS NOT NULL);
1539:
1540: IF PO_LOG.d_stmt THEN
1541: PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1542: END IF;
1543:
1544: OPEN l_dynamic_cursor FOR l_dynamic_sql
1537:
1538: l_in_update_mode := (p_to_table_name IS NOT NULL);
1539:
1540: IF PO_LOG.d_stmt THEN
1541: PO_LOG.stmt(d_module,d_progress,'l_dynamic_sql', l_dynamic_sql);
1542: END IF;
1543:
1544: OPEN l_dynamic_cursor FOR l_dynamic_sql
1545: USING p_from_table_name;
1567: END LOOP;
1568: CLOSE l_dynamic_cursor;
1569:
1570: d_progress := 020;
1571: IF (PO_LOG.d_proc) THEN
1572: PO_LOG.proc_end(d_module, 'l_table_column_names_list', l_table_column_names_list);
1573: PO_LOG.proc_end(d_module);
1574: END IF;
1575:
1568: CLOSE l_dynamic_cursor;
1569:
1570: d_progress := 020;
1571: IF (PO_LOG.d_proc) THEN
1572: PO_LOG.proc_end(d_module, 'l_table_column_names_list', l_table_column_names_list);
1573: PO_LOG.proc_end(d_module);
1574: END IF;
1575:
1576: RETURN l_table_column_names_list;
1569:
1570: d_progress := 020;
1571: IF (PO_LOG.d_proc) THEN
1572: PO_LOG.proc_end(d_module, 'l_table_column_names_list', l_table_column_names_list);
1573: PO_LOG.proc_end(d_module);
1574: END IF;
1575:
1576: RETURN l_table_column_names_list;
1577:
1640: l_Rev_num_dml VARCHAR2(1000);
1641: l_where_pk_sql VARCHAR2(2000);
1642:
1643: BEGIN
1644: IF (PO_LOG.d_proc) THEN
1645: PO_LOG.proc_begin(d_module);
1646: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647: PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1641: l_where_pk_sql VARCHAR2(2000);
1642:
1643: BEGIN
1644: IF (PO_LOG.d_proc) THEN
1645: PO_LOG.proc_begin(d_module);
1646: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647: PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1649: END IF;
1642:
1643: BEGIN
1644: IF (PO_LOG.d_proc) THEN
1645: PO_LOG.proc_begin(d_module);
1646: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647: PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1649: END IF;
1650: d_progress := 010;
1643: BEGIN
1644: IF (PO_LOG.d_proc) THEN
1645: PO_LOG.proc_begin(d_module);
1646: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647: PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1649: END IF;
1650: d_progress := 010;
1651:
1644: IF (PO_LOG.d_proc) THEN
1645: PO_LOG.proc_begin(d_module);
1646: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1647: PO_LOG.proc_begin(d_module, 'x_revision_id',x_revision_id);
1648: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1649: END IF;
1650: d_progress := 010;
1651:
1652: SELECT ENTITY_CODE
1653: INTO l_attr_group_Type
1654: FROM PO_UDA_AG_TEMPLATES
1655: WHERE TEMPLATE_ID = x_template_id;
1656:
1657: IF PO_LOG.d_stmt THEN
1658: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1659: END IF;
1660:
1661: l_object_dff_cr := g_object_dff_tl(l_attr_group_Type);
1654: FROM PO_UDA_AG_TEMPLATES
1655: WHERE TEMPLATE_ID = x_template_id;
1656:
1657: IF PO_LOG.d_stmt THEN
1658: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1659: END IF;
1660:
1661: l_object_dff_cr := g_object_dff_tl(l_attr_group_Type);
1662:
1676: FROM EGO_DATA_LEVEL_B
1677: WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1678: AND DATA_LEVEL_NAME LIKE '%ARCHIVE';
1679:
1680: IF PO_LOG.d_stmt THEN
1681: PO_LOG.stmt(d_module,d_progress,'Main_data_level_id',l_main_data_level_id);
1682: PO_LOG.stmt(d_module,d_progress,'Archive_data_level_id',l_archive_data_level.data_level_id);
1683: END IF;
1684:
1677: WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1678: AND DATA_LEVEL_NAME LIKE '%ARCHIVE';
1679:
1680: IF PO_LOG.d_stmt THEN
1681: PO_LOG.stmt(d_module,d_progress,'Main_data_level_id',l_main_data_level_id);
1682: PO_LOG.stmt(d_module,d_progress,'Archive_data_level_id',l_archive_data_level.data_level_id);
1683: END IF;
1684:
1685: d_progress := 020;
1678: AND DATA_LEVEL_NAME LIKE '%ARCHIVE';
1679:
1680: IF PO_LOG.d_stmt THEN
1681: PO_LOG.stmt(d_module,d_progress,'Main_data_level_id',l_main_data_level_id);
1682: PO_LOG.stmt(d_module,d_progress,'Archive_data_level_id',l_archive_data_level.data_level_id);
1683: END IF;
1684:
1685: d_progress := 020;
1686: l_index := 0;
1699: END IF;
1700: l_index := x_pk_col_value_pairs.NEXT(l_index);
1701: END LOOP;
1702:
1703: IF PO_LOG.d_stmt THEN
1704: PO_LOG.stmt(d_module,d_progress,'l_where_pk_sql',l_where_pk_sql);
1705: END IF;
1706:
1707: d_progress := 030;
1700: l_index := x_pk_col_value_pairs.NEXT(l_index);
1701: END LOOP;
1702:
1703: IF PO_LOG.d_stmt THEN
1704: PO_LOG.stmt(d_module,d_progress,'l_where_pk_sql',l_where_pk_sql);
1705: END IF;
1706:
1707: d_progress := 030;
1708: l_Rev_num_dml := ' SELECT count(1) from ( '||
1711: ' WHERE '|| l_where_pk_sql ||
1712: ' PK1_VALUE IS NOT NULL AND PK1_VALUE = ' || x_revision_id ||
1713: ' AND ROWNUM < 2 )';
1714:
1715: IF PO_LOG.d_stmt THEN
1716: PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
1717: END IF;
1718:
1719: EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
1712: ' PK1_VALUE IS NOT NULL AND PK1_VALUE = ' || x_revision_id ||
1713: ' AND ROWNUM < 2 )';
1714:
1715: IF PO_LOG.d_stmt THEN
1716: PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
1717: END IF;
1718:
1719: EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
1720:
1765: );
1766:
1767: d_progress := 060;
1768:
1769: IF PO_LOG.d_stmt THEN
1770: PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
1771: END IF;
1772:
1773: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1766:
1767: d_progress := 060;
1768:
1769: IF PO_LOG.d_stmt THEN
1770: PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
1771: END IF;
1772:
1773: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1774: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1776: ELSE
1777: RAISE FND_API.G_EXC_ERROR;
1778: END IF;
1779:
1780: IF (PO_LOG.d_proc) THEN
1781: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784: PO_LOG.proc_end(d_module);
1777: RAISE FND_API.G_EXC_ERROR;
1778: END IF;
1779:
1780: IF (PO_LOG.d_proc) THEN
1781: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784: PO_LOG.proc_end(d_module);
1785: END IF;
1778: END IF;
1779:
1780: IF (PO_LOG.d_proc) THEN
1781: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784: PO_LOG.proc_end(d_module);
1785: END IF;
1786: EXCEPTION
1779:
1780: IF (PO_LOG.d_proc) THEN
1781: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784: PO_LOG.proc_end(d_module);
1785: END IF;
1786: EXCEPTION
1787: WHEN FND_API.G_EXC_ERROR THEN
1780: IF (PO_LOG.d_proc) THEN
1781: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
1783: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
1784: PO_LOG.proc_end(d_module);
1785: END IF;
1786: EXCEPTION
1787: WHEN FND_API.G_EXC_ERROR THEN
1788: d_progress := 070;
1786: EXCEPTION
1787: WHEN FND_API.G_EXC_ERROR THEN
1788: d_progress := 070;
1789: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1790: IF (PO_LOG.d_exc) THEN
1791: PO_LOG.exc(d_module, d_progress, x_msg_data );
1792: PO_LOG.proc_end(d_module);
1793: END IF;
1794: x_return_status := FND_API.G_RET_STS_ERROR;
1787: WHEN FND_API.G_EXC_ERROR THEN
1788: d_progress := 070;
1789: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1790: IF (PO_LOG.d_exc) THEN
1791: PO_LOG.exc(d_module, d_progress, x_msg_data );
1792: PO_LOG.proc_end(d_module);
1793: END IF;
1794: x_return_status := FND_API.G_RET_STS_ERROR;
1795: WHEN OTHERS THEN
1788: d_progress := 070;
1789: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1790: IF (PO_LOG.d_exc) THEN
1791: PO_LOG.exc(d_module, d_progress, x_msg_data );
1792: PO_LOG.proc_end(d_module);
1793: END IF;
1794: x_return_status := FND_API.G_RET_STS_ERROR;
1795: WHEN OTHERS THEN
1796: d_progress := 080;
1876: l_get_attr_list_sql VARCHAR2(4000);
1877:
1878: BEGIN
1879: d_progress := 010;
1880: IF (PO_LOG.d_proc) THEN
1881: PO_LOG.proc_begin(d_module);
1882: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883: PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1877:
1878: BEGIN
1879: d_progress := 010;
1880: IF (PO_LOG.d_proc) THEN
1881: PO_LOG.proc_begin(d_module);
1882: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883: PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1885: END IF;
1878: BEGIN
1879: d_progress := 010;
1880: IF (PO_LOG.d_proc) THEN
1881: PO_LOG.proc_begin(d_module);
1882: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883: PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1885: END IF;
1886:
1879: d_progress := 010;
1880: IF (PO_LOG.d_proc) THEN
1881: PO_LOG.proc_begin(d_module);
1882: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883: PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1885: END IF;
1886:
1887: SELECT ENTITY_CODE
1880: IF (PO_LOG.d_proc) THEN
1881: PO_LOG.proc_begin(d_module);
1882: PO_LOG.proc_begin(d_module, 'x_template_id', x_template_id);
1883: PO_LOG.proc_begin(d_module, 'x_copy_attribute_groups',x_copy_attribute_groups);
1884: PO_LOG.proc_begin(d_module, 'x_commit', x_commit);
1885: END IF;
1886:
1887: SELECT ENTITY_CODE
1888: INTO l_attr_group_Type
1888: INTO l_attr_group_Type
1889: FROM PO_UDA_AG_TEMPLATES
1890: WHERE TEMPLATE_ID = x_template_id;
1891:
1892: IF PO_LOG.d_stmt THEN
1893: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1894: END IF;
1895:
1896: d_progress := 020;
1889: FROM PO_UDA_AG_TEMPLATES
1890: WHERE TEMPLATE_ID = x_template_id;
1891:
1892: IF PO_LOG.d_stmt THEN
1893: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
1894: END IF;
1895:
1896: d_progress := 020;
1897: l_object_dff_cr := g_object_dff_tl(l_attr_group_Type);
1906: FROM EGO_DATA_LEVEL_B
1907: WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1908: AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1909:
1910: IF PO_LOG.d_stmt THEN
1911: PO_LOG.stmt(d_module,d_progress,'Main_data_level',l_main_data_level.data_level_id);
1912: END IF;
1913:
1914: d_progress := 030;
1907: WHERE ATTR_GROUP_TYPE = l_attr_group_Type
1908: AND DATA_LEVEL_NAME NOT LIKE '%ARCHIVE';
1909:
1910: IF PO_LOG.d_stmt THEN
1911: PO_LOG.stmt(d_module,d_progress,'Main_data_level',l_main_data_level.data_level_id);
1912: END IF;
1913:
1914: d_progress := 030;
1915:
1936:
1937: l_from_where_pk_sql := SUBSTR(l_from_where_pk_sql, 1, LENGTH(l_from_where_pk_sql) - LENGTH(' AND'));
1938:
1939:
1940: IF PO_LOG.d_stmt THEN
1941: PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1942: END IF;
1943:
1944: l_index := 0;
1937: l_from_where_pk_sql := SUBSTR(l_from_where_pk_sql, 1, LENGTH(l_from_where_pk_sql) - LENGTH(' AND'));
1938:
1939:
1940: IF PO_LOG.d_stmt THEN
1941: PO_LOG.stmt(d_module,d_progress,'l_from_where_pk_sql',l_from_where_pk_sql);
1942: END IF;
1943:
1944: l_index := 0;
1945: l_index := x_to_pk_col_value_pairs.FIRST;
1959: END LOOP;
1960:
1961: l_to_where_pk_sql := SUBSTR(l_to_where_pk_sql, 1, LENGTH(l_to_where_pk_sql) - LENGTH(' AND'));
1962:
1963: IF PO_LOG.d_stmt THEN
1964: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1965: END IF;
1966:
1967: d_progress := 040;
1960:
1961: l_to_where_pk_sql := SUBSTR(l_to_where_pk_sql, 1, LENGTH(l_to_where_pk_sql) - LENGTH(' AND'));
1962:
1963: IF PO_LOG.d_stmt THEN
1964: PO_LOG.stmt(d_module,d_progress,'l_to_where_pk_sql',l_to_where_pk_sql);
1965: END IF;
1966:
1967: d_progress := 040;
1968: IF ( (x_copy_attribute_groups = 'SPECIFIC') OR ( x_copy_attribute_groups = 'EXCLUSIVE') ) THEN
1995: l_where_ag_sql := ' AND ATTR_GROUP_ID NOT IN ( '|| l_where_ag_sql || ' )';
1996: END IF ;
1997:
1998: END IF;
1999: IF PO_LOG.d_stmt THEN
2000: PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
2001: END IF;
2002: END IF ;
2003:
1996: END IF ;
1997:
1998: END IF;
1999: IF PO_LOG.d_stmt THEN
2000: PO_LOG.stmt(d_module,d_progress,'l_where_ag_sql',l_where_ag_sql);
2001: END IF;
2002: END IF ;
2003:
2004: d_progress := 050;
2010: ' WHERE '|| l_to_where_pk_sql ||
2011: l_where_ag_sql ||
2012: ' AND ROWNUM < 2 )';
2013:
2014: IF PO_LOG.d_stmt THEN
2015: PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
2016: END IF;
2017:
2018: EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
2011: l_where_ag_sql ||
2012: ' AND ROWNUM < 2 )';
2013:
2014: IF PO_LOG.d_stmt THEN
2015: PO_LOG.stmt(d_module,d_progress,'l_Rev_num_dml', l_Rev_num_dml);
2016: END IF;
2017:
2018: EXECUTE IMMEDIATE l_Rev_num_dml INTO l_count;
2019:
2044: ELSE -- bug16361247: No UDA data to copy
2045: x_return_status := FND_API.G_RET_STS_SUCCESS;
2046:
2047: d_progress := 060;
2048: IF PO_LOG.d_stmt THEN
2049: PO_LOG.stmt(d_module,d_progress,'No UDA Data to copy. Returning with Return_status : ', x_return_status);
2050: END IF;
2051:
2052: RETURN;
2045: x_return_status := FND_API.G_RET_STS_SUCCESS;
2046:
2047: d_progress := 060;
2048: IF PO_LOG.d_stmt THEN
2049: PO_LOG.stmt(d_module,d_progress,'No UDA Data to copy. Returning with Return_status : ', x_return_status);
2050: END IF;
2051:
2052: RETURN;
2053: END IF;
2077: ,x_errorcode => l_errorcode
2078: ,x_msg_count => l_msg_count
2079: ,x_msg_data => l_msg_data
2080: );
2081: IF PO_LOG.d_stmt THEN
2082: PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
2083: END IF;
2084: d_progress := 080;
2085: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2078: ,x_msg_count => l_msg_count
2079: ,x_msg_data => l_msg_data
2080: );
2081: IF PO_LOG.d_stmt THEN
2082: PO_LOG.stmt(d_module,d_progress,'ego_user_attrs_data_pvt.copy_user_attrs_data : Return_status : ', l_return_status);
2083: END IF;
2084: d_progress := 080;
2085: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2086: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2088: ELSE
2089: RAISE FND_API.G_EXC_ERROR;
2090: END IF;
2091:
2092: IF (PO_LOG.d_proc) THEN
2093: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096: PO_LOG.proc_end(d_module);
2089: RAISE FND_API.G_EXC_ERROR;
2090: END IF;
2091:
2092: IF (PO_LOG.d_proc) THEN
2093: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096: PO_LOG.proc_end(d_module);
2097: END IF;
2090: END IF;
2091:
2092: IF (PO_LOG.d_proc) THEN
2093: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096: PO_LOG.proc_end(d_module);
2097: END IF;
2098:
2091:
2092: IF (PO_LOG.d_proc) THEN
2093: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096: PO_LOG.proc_end(d_module);
2097: END IF;
2098:
2099: EXCEPTION
2092: IF (PO_LOG.d_proc) THEN
2093: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2094: PO_LOG.proc_end(d_module, 'x_msg_count', x_msg_count);
2095: PO_LOG.proc_end(d_module, 'x_msg_data', x_msg_data);
2096: PO_LOG.proc_end(d_module);
2097: END IF;
2098:
2099: EXCEPTION
2100: WHEN FND_API.G_EXC_ERROR THEN
2099: EXCEPTION
2100: WHEN FND_API.G_EXC_ERROR THEN
2101: d_progress := 090;
2102: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2103: IF (PO_LOG.d_exc) THEN
2104: PO_LOG.exc(d_module, d_progress, x_msg_data );
2105: PO_LOG.proc_end(d_module);
2106: END IF;
2107: x_return_status := FND_API.G_RET_STS_ERROR;
2100: WHEN FND_API.G_EXC_ERROR THEN
2101: d_progress := 090;
2102: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2103: IF (PO_LOG.d_exc) THEN
2104: PO_LOG.exc(d_module, d_progress, x_msg_data );
2105: PO_LOG.proc_end(d_module);
2106: END IF;
2107: x_return_status := FND_API.G_RET_STS_ERROR;
2108: WHEN OTHERS THEN
2101: d_progress := 090;
2102: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2103: IF (PO_LOG.d_exc) THEN
2104: PO_LOG.exc(d_module, d_progress, x_msg_data );
2105: PO_LOG.proc_end(d_module);
2106: END IF;
2107: x_return_status := FND_API.G_RET_STS_ERROR;
2108: WHEN OTHERS THEN
2109: d_progress := 100;
2139: x_association_id NUMBER;
2140: BEGIN
2141:
2142: d_progress := 010;
2143: IF (PO_LOG.d_proc) THEN
2144: PO_LOG.proc_begin(d_module);
2145: PO_LOG.proc_begin(d_module, 'x_mod_requisition_header_id', x_amd_requisition_header_id);
2146: PO_LOG.proc_begin(d_module, 'x_requisition_header_id',x_requisition_header_id);
2147: END IF;
2140: BEGIN
2141:
2142: d_progress := 010;
2143: IF (PO_LOG.d_proc) THEN
2144: PO_LOG.proc_begin(d_module);
2145: PO_LOG.proc_begin(d_module, 'x_mod_requisition_header_id', x_amd_requisition_header_id);
2146: PO_LOG.proc_begin(d_module, 'x_requisition_header_id',x_requisition_header_id);
2147: END IF;
2148:
2141:
2142: d_progress := 010;
2143: IF (PO_LOG.d_proc) THEN
2144: PO_LOG.proc_begin(d_module);
2145: PO_LOG.proc_begin(d_module, 'x_mod_requisition_header_id', x_amd_requisition_header_id);
2146: PO_LOG.proc_begin(d_module, 'x_requisition_header_id',x_requisition_header_id);
2147: END IF;
2148:
2149: SELECT ATTR_GROUP_ID
2142: d_progress := 010;
2143: IF (PO_LOG.d_proc) THEN
2144: PO_LOG.proc_begin(d_module);
2145: PO_LOG.proc_begin(d_module, 'x_mod_requisition_header_id', x_amd_requisition_header_id);
2146: PO_LOG.proc_begin(d_module, 'x_requisition_header_id',x_requisition_header_id);
2147: END IF;
2148:
2149: SELECT ATTR_GROUP_ID
2150: INTO l_attr_group_id
2194:
2195: END LOOP;
2196:
2197: x_return_status := FND_API.G_RET_STS_SUCCESS;
2198: IF (PO_LOG.d_proc) THEN
2199: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2200: PO_LOG.proc_end(d_module);
2201: END IF;
2202: EXCEPTION
2195: END LOOP;
2196:
2197: x_return_status := FND_API.G_RET_STS_SUCCESS;
2198: IF (PO_LOG.d_proc) THEN
2199: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2200: PO_LOG.proc_end(d_module);
2201: END IF;
2202: EXCEPTION
2203: WHEN OTHERS THEN
2196:
2197: x_return_status := FND_API.G_RET_STS_SUCCESS;
2198: IF (PO_LOG.d_proc) THEN
2199: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2200: PO_LOG.proc_end(d_module);
2201: END IF;
2202: EXCEPTION
2203: WHEN OTHERS THEN
2204: d_progress := 100;
2202: EXCEPTION
2203: WHEN OTHERS THEN
2204: d_progress := 100;
2205: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2206: IF (PO_LOG.d_exc) THEN
2207: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2208: PO_LOG.proc_end(d_module);
2209: END IF;
2210: END Merge_Amendment_Address_AG ;
2203: WHEN OTHERS THEN
2204: d_progress := 100;
2205: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2206: IF (PO_LOG.d_exc) THEN
2207: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2208: PO_LOG.proc_end(d_module);
2209: END IF;
2210: END Merge_Amendment_Address_AG ;
2211:
2204: d_progress := 100;
2205: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2206: IF (PO_LOG.d_exc) THEN
2207: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2208: PO_LOG.proc_end(d_module);
2209: END IF;
2210: END Merge_Amendment_Address_AG ;
2211:
2212: PROCEDURE Merge_Mod_Address_AG
2230: l_attr_group_id NUMBER;
2231: BEGIN
2232:
2233: d_progress := 010;
2234: IF (PO_LOG.d_proc) THEN
2235: PO_LOG.proc_begin(d_module);
2236: PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2237: PO_LOG.proc_begin(d_module, 'p_draft_id',p_draft_id);
2238: END IF;
2231: BEGIN
2232:
2233: d_progress := 010;
2234: IF (PO_LOG.d_proc) THEN
2235: PO_LOG.proc_begin(d_module);
2236: PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2237: PO_LOG.proc_begin(d_module, 'p_draft_id',p_draft_id);
2238: END IF;
2239:
2232:
2233: d_progress := 010;
2234: IF (PO_LOG.d_proc) THEN
2235: PO_LOG.proc_begin(d_module);
2236: PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2237: PO_LOG.proc_begin(d_module, 'p_draft_id',p_draft_id);
2238: END IF;
2239:
2240: SELECT ATTR_GROUP_ID
2233: d_progress := 010;
2234: IF (PO_LOG.d_proc) THEN
2235: PO_LOG.proc_begin(d_module);
2236: PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2237: PO_LOG.proc_begin(d_module, 'p_draft_id',p_draft_id);
2238: END IF;
2239:
2240: SELECT ATTR_GROUP_ID
2241: INTO l_attr_group_id
2290:
2291: END LOOP;
2292:
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: IF (PO_LOG.d_proc) THEN
2295: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2296: PO_LOG.proc_end(d_module);
2297: END IF;
2298: EXCEPTION
2291: END LOOP;
2292:
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: IF (PO_LOG.d_proc) THEN
2295: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2296: PO_LOG.proc_end(d_module);
2297: END IF;
2298: EXCEPTION
2299: WHEN OTHERS THEN
2292:
2293: x_return_status := FND_API.G_RET_STS_SUCCESS;
2294: IF (PO_LOG.d_proc) THEN
2295: PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2296: PO_LOG.proc_end(d_module);
2297: END IF;
2298: EXCEPTION
2299: WHEN OTHERS THEN
2300: d_progress := 100;
2298: EXCEPTION
2299: WHEN OTHERS THEN
2300: d_progress := 100;
2301: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302: IF (PO_LOG.d_exc) THEN
2303: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2304: PO_LOG.proc_end(d_module);
2305: END IF;
2306: END Merge_Mod_Address_AG ;
2299: WHEN OTHERS THEN
2300: d_progress := 100;
2301: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302: IF (PO_LOG.d_exc) THEN
2303: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2304: PO_LOG.proc_end(d_module);
2305: END IF;
2306: END Merge_Mod_Address_AG ;
2307:
2300: d_progress := 100;
2301: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2302: IF (PO_LOG.d_exc) THEN
2303: PO_LOG.exc(d_module, d_progress, 'Unexpected Error in Merge_Amendment_Address_AG'||SQLERRM);
2304: PO_LOG.proc_end(d_module);
2305: END IF;
2306: END Merge_Mod_Address_AG ;
2307:
2308:
2362: d_module CONSTANT VARCHAR2(100) := d_pkg_name || d_api_name;
2363: d_progress NUMBER := 0;
2364:
2365: BEGIN
2366: IF (PO_LOG.d_proc) THEN
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2363: d_progress NUMBER := 0;
2364:
2365: BEGIN
2366: IF (PO_LOG.d_proc) THEN
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2364:
2365: BEGIN
2366: IF (PO_LOG.d_proc) THEN
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372: PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2365: BEGIN
2366: IF (PO_LOG.d_proc) THEN
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372: PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2373: END IF;
2366: IF (PO_LOG.d_proc) THEN
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372: PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2373: END IF;
2374: -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2367: PO_LOG.proc_begin(d_module);
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372: PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2373: END IF;
2374: -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2375: IF p_document_style_id = 1 THEN
2368: PO_LOG.proc_begin(d_module, 'p_functional_area', p_functional_area);
2369: PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2370: PO_LOG.proc_begin(d_module, 'p_document_style_id', p_document_style_id);
2371: PO_LOG.proc_begin(d_module, 'p_document_level', p_document_level);
2372: PO_LOG.proc_begin(d_module, 'p_input_date', To_Char(p_input_date, 'mm/dd/yyyy hh24:mi:ss'));
2373: END IF;
2374: -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2375: IF p_document_style_id = 1 THEN
2376:
2373: END IF;
2374: -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2375: IF p_document_style_id = 1 THEN
2376:
2377: IF (PO_LOG.d_proc) THEN
2378: PO_LOG.proc_end(d_module);
2379: END IF;
2380:
2381: x_return_status := FND_API.G_RET_STS_ERROR;
2374: -- the standard style (style_id = 1)is used for seeding. However UDAs are not enabled for the standard style.
2375: IF p_document_style_id = 1 THEN
2376:
2377: IF (PO_LOG.d_proc) THEN
2378: PO_LOG.proc_end(d_module);
2379: END IF;
2380:
2381: x_return_status := FND_API.G_RET_STS_ERROR;
2382: x_err_msg := 'PO_UDA_TEMPLATE_NOT_FOUND';
2396: p_input_date < effective_to
2397: )
2398: AND compiled_flag = 'Y';
2399:
2400: IF (PO_LOG.d_proc) THEN
2401: PO_LOG.proc_end(d_module);
2402: END IF;
2403:
2404: x_return_status := FND_API.G_RET_STS_SUCCESS;
2397: )
2398: AND compiled_flag = 'Y';
2399:
2400: IF (PO_LOG.d_proc) THEN
2401: PO_LOG.proc_end(d_module);
2402: END IF;
2403:
2404: x_return_status := FND_API.G_RET_STS_SUCCESS;
2405: RETURN l_template_id;
2409: WHEN OTHERS THEN
2410: x_err_msg := 'PO_UDA_TEMPLATE_NOT_FOUND';
2411: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2412:
2413: IF PO_LOG.d_stmt THEN
2414: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2415: END IF;
2416:
2417: IF (PO_LOG.d_proc) THEN
2410: x_err_msg := 'PO_UDA_TEMPLATE_NOT_FOUND';
2411: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2412:
2413: IF PO_LOG.d_stmt THEN
2414: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2415: END IF;
2416:
2417: IF (PO_LOG.d_proc) THEN
2418: PO_LOG.proc_end(d_module);
2413: IF PO_LOG.d_stmt THEN
2414: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2415: END IF;
2416:
2417: IF (PO_LOG.d_proc) THEN
2418: PO_LOG.proc_end(d_module);
2419: END IF;
2420:
2421: RETURN NULL;
2414: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2415: END IF;
2416:
2417: IF (PO_LOG.d_proc) THEN
2418: PO_LOG.proc_end(d_module);
2419: END IF;
2420:
2421: RETURN NULL;
2422:
2461: AND ATTRACTDISP.EXECUTION_METHOD = p_event
2462: AND ATTRACT.CLASSIFICATION_CODE = p_template_id ;
2463:
2464: BEGIN
2465: IF (PO_LOG.d_proc) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2462: AND ATTRACT.CLASSIFICATION_CODE = p_template_id ;
2463:
2464: BEGIN
2465: IF (PO_LOG.d_proc) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470: PO_LOG.proc_begin(d_module, 'p_event', p_event);
2463:
2464: BEGIN
2465: IF (PO_LOG.d_proc) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470: PO_LOG.proc_begin(d_module, 'p_event', p_event);
2471: END IF;
2464: BEGIN
2465: IF (PO_LOG.d_proc) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470: PO_LOG.proc_begin(d_module, 'p_event', p_event);
2471: END IF;
2472:
2465: IF (PO_LOG.d_proc) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470: PO_LOG.proc_begin(d_module, 'p_event', p_event);
2471: END IF;
2472:
2473: IF(p_action_id IS NOT NULL) THEN
2466: PO_LOG.proc_begin(d_module);
2467: PO_LOG.proc_begin(d_module, 'p_Action_Id', p_Action_Id);
2468: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2469: PO_LOG.proc_begin(d_module, 'p_attribute_group_id', p_attribute_group_id);
2470: PO_LOG.proc_begin(d_module, 'p_event', p_event);
2471: END IF;
2472:
2473: IF(p_action_id IS NOT NULL) THEN
2474: l_action_id := p_action_id;
2479: END IF;
2480:
2481: IF ( l_action_id IS NOT NULL ) THEN
2482:
2483: IF PO_LOG.d_stmt THEN
2484: PO_LOG.stmt(d_module,d_progress,'Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2485: PO_LOG.stmt(d_module,d_progress,'Executing UDA Function for Action Id : '|| l_action_id );
2486: END IF;
2487:
2480:
2481: IF ( l_action_id IS NOT NULL ) THEN
2482:
2483: IF PO_LOG.d_stmt THEN
2484: PO_LOG.stmt(d_module,d_progress,'Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2485: PO_LOG.stmt(d_module,d_progress,'Executing UDA Function for Action Id : '|| l_action_id );
2486: END IF;
2487:
2488: ego_ext_fwk_pub.Execute_Function(
2481: IF ( l_action_id IS NOT NULL ) THEN
2482:
2483: IF PO_LOG.d_stmt THEN
2484: PO_LOG.stmt(d_module,d_progress,'Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2485: PO_LOG.stmt(d_module,d_progress,'Executing UDA Function for Action Id : '|| l_action_id );
2486: END IF;
2487:
2488: ego_ext_fwk_pub.Execute_Function(
2489: p_Action_Id => l_action_id
2502: x_msg_count := l_msg_count;
2503: x_msg_data := l_msg_data;
2504: ELSE
2505: x_return_status := FND_API.G_RET_STS_SUCCESS;
2506: IF PO_LOG.d_stmt THEN
2507: PO_LOG.stmt(d_module,d_progress,'No Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2508: END IF;
2509: END IF;
2510:
2503: x_msg_data := l_msg_data;
2504: ELSE
2505: x_return_status := FND_API.G_RET_STS_SUCCESS;
2506: IF PO_LOG.d_stmt THEN
2507: PO_LOG.stmt(d_module,d_progress,'No Validation Action found for the Attribute Group : '|| p_attribute_group_id );
2508: END IF;
2509: END IF;
2510:
2511: EXCEPTION
2512: WHEN OTHERS THEN
2513:
2514: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2515:
2516: IF PO_LOG.d_stmt THEN
2517: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2518: END IF;
2519:
2520: IF (PO_LOG.d_proc) THEN
2513:
2514: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2515:
2516: IF PO_LOG.d_stmt THEN
2517: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2518: END IF;
2519:
2520: IF (PO_LOG.d_proc) THEN
2521: PO_LOG.proc_end(d_module);
2516: IF PO_LOG.d_stmt THEN
2517: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2518: END IF;
2519:
2520: IF (PO_LOG.d_proc) THEN
2521: PO_LOG.proc_end(d_module);
2522: END IF;
2523:
2524: END Execute_uda_Function;
2517: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
2518: END IF;
2519:
2520: IF (PO_LOG.d_proc) THEN
2521: PO_LOG.proc_end(d_module);
2522: END IF;
2523:
2524: END Execute_uda_Function;
2525:
2562:
2563: BEGIN
2564: x_return_status := FND_API.G_RET_STS_SUCCESS;
2565: d_progress := 010;
2566: IF (PO_LOG.d_proc) THEN
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2563: BEGIN
2564: x_return_status := FND_API.G_RET_STS_SUCCESS;
2565: d_progress := 010;
2566: IF (PO_LOG.d_proc) THEN
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2564: x_return_status := FND_API.G_RET_STS_SUCCESS;
2565: d_progress := 010;
2566: IF (PO_LOG.d_proc) THEN
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2565: d_progress := 010;
2566: IF (PO_LOG.d_proc) THEN
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2566: IF (PO_LOG.d_proc) THEN
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2567: PO_LOG.proc_begin(d_module);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2568: PO_LOG.proc_begin(d_module, 'p_template_id',p_template_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2569: PO_LOG.proc_begin(d_module, 'pk1_value',pk1_value);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2570: PO_LOG.proc_begin(d_module, 'pk2_value',pk2_value);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578: PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2571: PO_LOG.proc_begin(d_module, 'pk3_value',pk3_value);
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578: PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2579: END IF;
2572: PO_LOG.proc_begin(d_module, 'pk4_value',pk4_value);
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578: PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2579: END IF;
2580:
2573: PO_LOG.proc_begin(d_module, 'pk5_value',pk5_value);
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578: PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2579: END IF;
2580:
2581: SELECT ENTITY_CODE
2574: PO_LOG.proc_begin(d_module, 'p_attr_grp_id',p_attr_grp_id);
2575: PO_LOG.proc_begin(d_module, 'p_attr_grp_int_name',p_attr_grp_int_name);
2576: PO_LOG.proc_begin(d_module, 'p_attr_id',p_attr_id);
2577: PO_LOG.proc_begin(d_module, 'p_attr_int_name',p_attr_int_name);
2578: PO_LOG.proc_begin(d_module, 'p_mode',p_mode);
2579: END IF;
2580:
2581: SELECT ENTITY_CODE
2582: INTO l_attr_group_Type
2582: INTO l_attr_group_Type
2583: FROM PO_UDA_AG_TEMPLATES
2584: WHERE TEMPLATE_ID = p_template_id;
2585:
2586: IF PO_LOG.d_stmt THEN
2587: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
2588: END IF;
2589:
2590: d_progress := 020;
2583: FROM PO_UDA_AG_TEMPLATES
2584: WHERE TEMPLATE_ID = p_template_id;
2585:
2586: IF PO_LOG.d_stmt THEN
2587: PO_LOG.stmt(d_module,d_progress,'l_attr_group_Type',l_attr_group_Type);
2588: END IF;
2589:
2590: d_progress := 020;
2591: l_ext_table_name := g_object_dff_tl(l_attr_group_Type).l_object_ext_b_table;
2618: raise ATTR_GRP_REQD;
2619: end if;
2620: exception
2621: when no_data_found then
2622: po_log.exc('At '|| d_progress ||': invalid attribute group');
2623: raise ATTR_GRP_REQD;
2624: end;
2625:
2626: IF PO_LOG.d_stmt THEN
2622: po_log.exc('At '|| d_progress ||': invalid attribute group');
2623: raise ATTR_GRP_REQD;
2624: end;
2625:
2626: IF PO_LOG.d_stmt THEN
2627: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id',l_attr_grp_id);
2628: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_name',l_attr_grp_name);
2629: END IF;
2630:
2623: raise ATTR_GRP_REQD;
2624: end;
2625:
2626: IF PO_LOG.d_stmt THEN
2627: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id',l_attr_grp_id);
2628: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_name',l_attr_grp_name);
2629: END IF;
2630:
2631: -- next hit ego_attrs_v to get the db column name
2624: end;
2625:
2626: IF PO_LOG.d_stmt THEN
2627: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_id',l_attr_grp_id);
2628: PO_LOG.stmt(d_module,d_progress,'l_attr_grp_name',l_attr_grp_name);
2629: END IF;
2630:
2631: -- next hit ego_attrs_v to get the db column name
2632: d_progress := 040;
2652: raise ATTR_REQD;
2653: end if;
2654: exception
2655: when no_data_found then
2656: po_log.exc('At '|| d_progress ||': invalid attribute');
2657: raise ATTR_REQD;
2658: end;
2659: IF PO_LOG.d_stmt THEN
2660: PO_LOG.stmt(d_module,d_progress,'l_db_col_name',l_db_col_name);
2655: when no_data_found then
2656: po_log.exc('At '|| d_progress ||': invalid attribute');
2657: raise ATTR_REQD;
2658: end;
2659: IF PO_LOG.d_stmt THEN
2660: PO_LOG.stmt(d_module,d_progress,'l_db_col_name',l_db_col_name);
2661: END IF;
2662:
2663: -- construct the sql query on the ext table
2656: po_log.exc('At '|| d_progress ||': invalid attribute');
2657: raise ATTR_REQD;
2658: end;
2659: IF PO_LOG.d_stmt THEN
2660: PO_LOG.stmt(d_module,d_progress,'l_db_col_name',l_db_col_name);
2661: END IF;
2662:
2663: -- construct the sql query on the ext table
2664: d_progress := 050;
2685: end if;
2686: if l_pk5_col_name is not null then
2687: l_sql_stmt := l_sql_stmt ||' and '||l_pk5_col_name ||' = '|| pk5_value;
2688: end if;
2689: IF PO_LOG.d_stmt THEN
2690: PO_LOG.stmt(d_module,d_progress,'l_sql_stmt',l_sql_stmt);
2691: END IF;
2692:
2693: -- execute the query to get the attribute value
2686: if l_pk5_col_name is not null then
2687: l_sql_stmt := l_sql_stmt ||' and '||l_pk5_col_name ||' = '|| pk5_value;
2688: end if;
2689: IF PO_LOG.d_stmt THEN
2690: PO_LOG.stmt(d_module,d_progress,'l_sql_stmt',l_sql_stmt);
2691: END IF;
2692:
2693: -- execute the query to get the attribute value
2694: d_progress := 060;
2692:
2693: -- execute the query to get the attribute value
2694: d_progress := 060;
2695: execute immediate l_sql_stmt into l_attr_value;
2696: IF PO_LOG.d_stmt THEN
2697: PO_LOG.stmt(d_module,d_progress,'l_attr_value',substr(l_attr_value, 1, 2000));
2698: END IF;
2699:
2700: if p_mode = 'INTERNAL_VALUE' then
2693: -- execute the query to get the attribute value
2694: d_progress := 060;
2695: execute immediate l_sql_stmt into l_attr_value;
2696: IF PO_LOG.d_stmt THEN
2697: PO_LOG.stmt(d_module,d_progress,'l_attr_value',substr(l_attr_value, 1, 2000));
2698: END IF;
2699:
2700: if p_mode = 'INTERNAL_VALUE' then
2701: return l_attr_value;
2720: );
2721:
2722: EXCEPTION
2723: WHEN ATTR_GRP_REQD then
2724: PO_LOG.exc(d_module,d_progress,'Either attribute group id or attribute group internal name must be specified');
2725: x_return_status := FND_API.G_RET_STS_ERROR;
2726: x_msg_data := 'Either attribute group id or attribute group internal name must be specified' ;
2727: return null;
2728: WHEN ATTR_REQD then
2725: x_return_status := FND_API.G_RET_STS_ERROR;
2726: x_msg_data := 'Either attribute group id or attribute group internal name must be specified' ;
2727: return null;
2728: WHEN ATTR_REQD then
2729: PO_LOG.exc(d_module,d_progress,'Either attribute id or attribute internal name must be specified');
2730: x_return_status := FND_API.G_RET_STS_ERROR;
2731: x_msg_data := 'Either attribute id or attribute internal name must be specified' ;
2732: return null;
2733: when others then
2730: x_return_status := FND_API.G_RET_STS_ERROR;
2731: x_msg_data := 'Either attribute id or attribute internal name must be specified' ;
2732: return null;
2733: when others then
2734: PO_LOG.exc(d_module,d_progress,'Unexpected error: '|| SQLERRM || 'at '||d_progress);
2735: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2736: x_msg_data := 'Unexpected error: '|| SQLERRM || 'at '||d_progress ;
2737: return null;
2738: END GET_SINGLE_ATTR_VALUE;
2784: select distinct(attribute_category) from po_uda_ag_template_usages
2785: where template_id = p_template_id;
2786:
2787: BEGIN
2788: IF (PO_LOG.d_proc) THEN
2789: PO_LOG.proc_begin(d_module);
2790: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2791: END IF;
2792:
2785: where template_id = p_template_id;
2786:
2787: BEGIN
2788: IF (PO_LOG.d_proc) THEN
2789: PO_LOG.proc_begin(d_module);
2790: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2791: END IF;
2792:
2793: l_attr_grp_ids := PO_TBL_NUMBER();
2786:
2787: BEGIN
2788: IF (PO_LOG.d_proc) THEN
2789: PO_LOG.proc_begin(d_module);
2790: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2791: END IF;
2792:
2793: l_attr_grp_ids := PO_TBL_NUMBER();
2794:
2802: END IF; */
2803:
2804: IF((p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN')
2805: OR (c_get_usages_rec.attribute_category = 'DOCUMENT_NUMBERING') ) THEN
2806: IF PO_LOG.d_stmt THEN
2807: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain and Doc Numbering AGs');
2808: END IF;
2809: ELSE
2810: l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2803:
2804: IF((p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN')
2805: OR (c_get_usages_rec.attribute_category = 'DOCUMENT_NUMBERING') ) THEN
2806: IF PO_LOG.d_stmt THEN
2807: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain and Doc Numbering AGs');
2808: END IF;
2809: ELSE
2810: l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2811: l_attr_grp_ids_list := l_po_uda_usage_object.Attr_group_Id;
2821:
2822: x_attr_grp_ids := l_attr_grp_ids;
2823: x_return_status := 'S';
2824:
2825: IF PO_LOG.d_stmt THEN
2826: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2827: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2828: END IF;
2829:
2822: x_attr_grp_ids := l_attr_grp_ids;
2823: x_return_status := 'S';
2824:
2825: IF PO_LOG.d_stmt THEN
2826: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2827: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2828: END IF;
2829:
2830: EXCEPTION
2823: x_return_status := 'S';
2824:
2825: IF PO_LOG.d_stmt THEN
2826: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2827: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2828: END IF;
2829:
2830: EXCEPTION
2831: WHEN OTHERS THEN
2830: EXCEPTION
2831: WHEN OTHERS THEN
2832: d_progress := 40;
2833: x_return_status := 'U';
2834: IF PO_LOG.d_stmt THEN
2835: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2836: END IF;
2837: END get_po_header_attr_group_ids;
2838:
2831: WHEN OTHERS THEN
2832: d_progress := 40;
2833: x_return_status := 'U';
2834: IF PO_LOG.d_stmt THEN
2835: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2836: END IF;
2837: END get_po_header_attr_group_ids;
2838:
2839: PROCEDURE get_po_line_attr_group_ids(
2863: WHERE template_id = p_template_id;
2864:
2865: BEGIN
2866:
2867: IF (PO_LOG.d_proc) THEN
2868: PO_LOG.proc_begin(d_module);
2869: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2870: END IF;
2871:
2864:
2865: BEGIN
2866:
2867: IF (PO_LOG.d_proc) THEN
2868: PO_LOG.proc_begin(d_module);
2869: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2870: END IF;
2871:
2872: l_attr_grp_ids := PO_TBL_NUMBER();
2865: BEGIN
2866:
2867: IF (PO_LOG.d_proc) THEN
2868: PO_LOG.proc_begin(d_module);
2869: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2870: END IF;
2871:
2872: l_attr_grp_ids := PO_TBL_NUMBER();
2873:
2892: EGO_COL_NAME_VALUE_PAIR_OBJ( 'IDC_TYPE' , l_clm_idc_type ));
2893: END IF;
2894:
2895: IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2896: IF PO_LOG.d_stmt THEN
2897: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2898: END IF;
2899:
2900: ELSE
2893: END IF;
2894:
2895: IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2896: IF PO_LOG.d_stmt THEN
2897: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2898: END IF;
2899:
2900: ELSE
2901: l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2911:
2912: x_attr_grp_ids := l_attr_grp_ids;
2913: x_return_status := 'S';
2914:
2915: IF PO_LOG.d_stmt THEN
2916: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2917: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2918: END IF;
2919:
2912: x_attr_grp_ids := l_attr_grp_ids;
2913: x_return_status := 'S';
2914:
2915: IF PO_LOG.d_stmt THEN
2916: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2917: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2918: END IF;
2919:
2920: EXCEPTION
2913: x_return_status := 'S';
2914:
2915: IF PO_LOG.d_stmt THEN
2916: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2917: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2918: END IF;
2919:
2920: EXCEPTION
2921: WHEN OTHERS THEN
2920: EXCEPTION
2921: WHEN OTHERS THEN
2922: d_progress := 40;
2923: x_return_status := 'U';
2924: IF PO_LOG.d_stmt THEN
2925: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2926: END IF;
2927: END get_po_line_attr_group_ids;
2928:
2921: WHEN OTHERS THEN
2922: d_progress := 40;
2923: x_return_status := 'U';
2924: IF PO_LOG.d_stmt THEN
2925: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2926: END IF;
2927: END get_po_line_attr_group_ids;
2928:
2929:
2949: SELECT DISTINCT(ATTRIBUTE_CATEGORY) FROM po_uda_ag_template_usages
2950: WHERE template_id = p_template_id;
2951:
2952: BEGIN
2953: IF (PO_LOG.d_proc) THEN
2954: PO_LOG.proc_begin(d_module);
2955: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2956: END IF;
2957:
2950: WHERE template_id = p_template_id;
2951:
2952: BEGIN
2953: IF (PO_LOG.d_proc) THEN
2954: PO_LOG.proc_begin(d_module);
2955: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2956: END IF;
2957:
2958: l_attr_grp_ids := PO_TBL_NUMBER();
2951:
2952: BEGIN
2953: IF (PO_LOG.d_proc) THEN
2954: PO_LOG.proc_begin(d_module);
2955: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
2956: END IF;
2957:
2958: l_attr_grp_ids := PO_TBL_NUMBER();
2959:
2958: l_attr_grp_ids := PO_TBL_NUMBER();
2959:
2960: FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
2961: IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2962: IF PO_LOG.d_stmt THEN
2963: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2964: END IF;
2965:
2966: ELSE
2959:
2960: FOR c_get_usages_rec IN c_get_usages(p_template_id) LOOP
2961: IF(p_is_doc_mod = FND_API.G_TRUE AND c_get_usages_rec.attribute_category = 'CHAIN') THEN
2962: IF PO_LOG.d_stmt THEN
2963: PO_LOG.stmt(d_module, d_progress, 'Ignore Chain AG Ids');
2964: END IF;
2965:
2966: ELSE
2967: l_po_uda_usage_object := po_uda_usage_object.new_instance(p_template_id,c_get_usages_rec.attribute_category,l_context );
2978:
2979: x_attr_grp_ids := l_attr_grp_ids;
2980: x_return_status := 'S';
2981:
2982: IF PO_LOG.d_stmt THEN
2983: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2984: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2985: END IF;
2986:
2979: x_attr_grp_ids := l_attr_grp_ids;
2980: x_return_status := 'S';
2981:
2982: IF PO_LOG.d_stmt THEN
2983: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2984: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2985: END IF;
2986:
2987: EXCEPTION
2980: x_return_status := 'S';
2981:
2982: IF PO_LOG.d_stmt THEN
2983: PO_LOG.stmt(d_module, d_progress, 'x_attr_grp_ids', x_attr_grp_ids);
2984: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2985: END IF;
2986:
2987: EXCEPTION
2988: WHEN OTHERS THEN
2987: EXCEPTION
2988: WHEN OTHERS THEN
2989: d_progress := 40;
2990: x_return_status := 'U';
2991: IF PO_LOG.d_stmt THEN
2992: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2993: END IF;
2994: END get_po_line_loc_attr_group_ids;
2995:
2988: WHEN OTHERS THEN
2989: d_progress := 40;
2990: x_return_status := 'U';
2991: IF PO_LOG.d_stmt THEN
2992: PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2993: END IF;
2994: END get_po_line_loc_attr_group_ids;
2995:
2996: PROCEDURE global_update_line_uda
3017:
3018:
3019: BEGIN
3020:
3021: IF (PO_LOG.d_proc) THEN
3022: PO_LOG.proc_begin(d_module);
3023: PO_LOG.proc_begin(d_module, 'po_src_line_id', po_src_line_id);
3024: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3025: END IF;
3018:
3019: BEGIN
3020:
3021: IF (PO_LOG.d_proc) THEN
3022: PO_LOG.proc_begin(d_module);
3023: PO_LOG.proc_begin(d_module, 'po_src_line_id', po_src_line_id);
3024: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3025: END IF;
3026:
3019: BEGIN
3020:
3021: IF (PO_LOG.d_proc) THEN
3022: PO_LOG.proc_begin(d_module);
3023: PO_LOG.proc_begin(d_module, 'po_src_line_id', po_src_line_id);
3024: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3025: END IF;
3026:
3027: FOR i IN 1..po_dest_line_ids_tbl.Count LOOP
3020:
3021: IF (PO_LOG.d_proc) THEN
3022: PO_LOG.proc_begin(d_module);
3023: PO_LOG.proc_begin(d_module, 'po_src_line_id', po_src_line_id);
3024: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3025: END IF;
3026:
3027: FOR i IN 1..po_dest_line_ids_tbl.Count LOOP
3028: po_dest_line_ids := po_dest_line_ids || po_dest_line_ids_tbl(i) || ',' ;
3758: d_progress := '80';
3759:
3760: EXCEPTION
3761: WHEN OTHERS THEN
3762: IF PO_LOG.d_stmt THEN
3763: PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
3764: END IF;
3765: END GLOBAL_UPDATE_LINE_UDA;
3766:
3759:
3760: EXCEPTION
3761: WHEN OTHERS THEN
3762: IF PO_LOG.d_stmt THEN
3763: PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
3764: END IF;
3765: END GLOBAL_UPDATE_LINE_UDA;
3766:
3767: PROCEDURE global_update_shipment_uda
3791:
3792:
3793: BEGIN
3794:
3795: IF (PO_LOG.d_proc) THEN
3796: PO_LOG.proc_begin(d_module);
3797: PO_LOG.proc_begin(d_module, 'po_src_line_location_id', po_src_line_location_id);
3798: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3799: END IF;
3792:
3793: BEGIN
3794:
3795: IF (PO_LOG.d_proc) THEN
3796: PO_LOG.proc_begin(d_module);
3797: PO_LOG.proc_begin(d_module, 'po_src_line_location_id', po_src_line_location_id);
3798: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3799: END IF;
3800:
3793: BEGIN
3794:
3795: IF (PO_LOG.d_proc) THEN
3796: PO_LOG.proc_begin(d_module);
3797: PO_LOG.proc_begin(d_module, 'po_src_line_location_id', po_src_line_location_id);
3798: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3799: END IF;
3800:
3801: FOR i IN 1..po_dest_line_location_ids_tbl.Count LOOP
3794:
3795: IF (PO_LOG.d_proc) THEN
3796: PO_LOG.proc_begin(d_module);
3797: PO_LOG.proc_begin(d_module, 'po_src_line_location_id', po_src_line_location_id);
3798: PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
3799: END IF;
3800:
3801: FOR i IN 1..po_dest_line_location_ids_tbl.Count LOOP
3802: po_dest_line_location_ids := po_dest_line_location_ids || po_dest_line_location_ids_tbl(i) || ',' ;
4569: d_progress := '80';
4570:
4571: EXCEPTION
4572: WHEN OTHERS THEN
4573: IF PO_LOG.d_stmt THEN
4574: PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
4575: END IF;
4576:
4577: END GLOBAL_UPDATE_SHIPMENT_UDA;
4570:
4571: EXCEPTION
4572: WHEN OTHERS THEN
4573: IF PO_LOG.d_stmt THEN
4574: PO_LOG.stmt(d_module, d_progress, 'Exception : ', SQLERRM );
4575: END IF;
4576:
4577: END GLOBAL_UPDATE_SHIPMENT_UDA;
4578:
4590:
4591: begin
4592: -- using statement level logging to log the entry because this proc will be
4593: -- executed thousands of times
4594: IF PO_LOG.d_stmt THEN
4595: PO_LOG.stmt(d_module, d_progress, 'Entering');
4596: PO_LOG.stmt(d_module, d_progress, 'p_location_id', p_location_id);
4597: PO_LOG.stmt(d_module, d_progress, 'p_address_type', p_address_type);
4598: END IF;
4591: begin
4592: -- using statement level logging to log the entry because this proc will be
4593: -- executed thousands of times
4594: IF PO_LOG.d_stmt THEN
4595: PO_LOG.stmt(d_module, d_progress, 'Entering');
4596: PO_LOG.stmt(d_module, d_progress, 'p_location_id', p_location_id);
4597: PO_LOG.stmt(d_module, d_progress, 'p_address_type', p_address_type);
4598: END IF;
4599:
4592: -- using statement level logging to log the entry because this proc will be
4593: -- executed thousands of times
4594: IF PO_LOG.d_stmt THEN
4595: PO_LOG.stmt(d_module, d_progress, 'Entering');
4596: PO_LOG.stmt(d_module, d_progress, 'p_location_id', p_location_id);
4597: PO_LOG.stmt(d_module, d_progress, 'p_address_type', p_address_type);
4598: END IF;
4599:
4600: d_progress := 10;
4593: -- executed thousands of times
4594: IF PO_LOG.d_stmt THEN
4595: PO_LOG.stmt(d_module, d_progress, 'Entering');
4596: PO_LOG.stmt(d_module, d_progress, 'p_location_id', p_location_id);
4597: PO_LOG.stmt(d_module, d_progress, 'p_address_type', p_address_type);
4598: END IF;
4599:
4600: d_progress := 10;
4601:
4624: l_sql_stmt := l_sql_stmt || ' = ''Y'' ';
4625: END IF;
4626:
4627:
4628: IF PO_LOG.d_stmt THEN
4629: PO_LOG.stmt(d_module, d_progress, 'l_sql_stmt', l_sql_stmt);
4630: END IF;
4631:
4632: d_progress := 20;
4625: END IF;
4626:
4627:
4628: IF PO_LOG.d_stmt THEN
4629: PO_LOG.stmt(d_module, d_progress, 'l_sql_stmt', l_sql_stmt);
4630: END IF;
4631:
4632: d_progress := 20;
4633: execute immediate l_sql_stmt into l_count using p_location_id;
4631:
4632: d_progress := 20;
4633: execute immediate l_sql_stmt into l_count using p_location_id;
4634:
4635: IF PO_LOG.d_stmt THEN
4636: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
4637: PO_LOG.stmt(d_module, d_progress, 'Exiting');
4638: END IF;
4639:
4632: d_progress := 20;
4633: execute immediate l_sql_stmt into l_count using p_location_id;
4634:
4635: IF PO_LOG.d_stmt THEN
4636: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
4637: PO_LOG.stmt(d_module, d_progress, 'Exiting');
4638: END IF;
4639:
4640: if l_count > 0 then
4633: execute immediate l_sql_stmt into l_count using p_location_id;
4634:
4635: IF PO_LOG.d_stmt THEN
4636: PO_LOG.stmt(d_module, d_progress, 'l_count', l_count);
4637: PO_LOG.stmt(d_module, d_progress, 'Exiting');
4638: END IF;
4639:
4640: if l_count > 0 then
4641: return 'Y';
4644: end if;
4645:
4646: exception
4647: when others then
4648: IF PO_LOG.d_stmt THEN
4649: PO_LOG.stmt(d_module, d_progress, 'Encountered exception', substr(SQLERRM, 1, 200));
4650: END IF;
4651:
4652: return 'N';
4645:
4646: exception
4647: when others then
4648: IF PO_LOG.d_stmt THEN
4649: PO_LOG.stmt(d_module, d_progress, 'Encountered exception', substr(SQLERRM, 1, 200));
4650: END IF;
4651:
4652: return 'N';
4653: end is_location_valid;
4699: l_attr_group_id NUMBER := 0;
4700: l_template_id NUMBER;
4701:
4702: BEGIN
4703: IF (PO_LOG.d_proc)
4704: THEN
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4701:
4702: BEGIN
4703: IF (PO_LOG.d_proc)
4704: THEN
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709: PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4702: BEGIN
4703: IF (PO_LOG.d_proc)
4704: THEN
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709: PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4710: END IF;
4703: IF (PO_LOG.d_proc)
4704: THEN
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709: PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4710: END IF;
4711:
4704: THEN
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709: PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4710: END IF;
4711:
4712: -- SQL What: Querying for attribute_group_id for a specific attribute category
4705: PO_LOG.proc_begin(d_module);
4706: PO_LOG.proc_begin(d_module, 'p_contract_type', p_contract_type);
4707: PO_LOG.proc_begin(d_module, 'p_idc_type', p_idc_type);
4708: PO_LOG.proc_begin(d_module, 'p_template_id', p_template_id);
4709: PO_LOG.proc_begin(d_module, 'p_attr_category', p_attr_category);
4710: END IF;
4711:
4712: -- SQL What: Querying for attribute_group_id for a specific attribute category
4713: -- SQL Why: Need to attribute_group_id to copy specific attr grp uda data
4723:
4724: x_return_status := FND_API.G_RET_STS_SUCCESS;
4725: x_attr_group_id := l_attr_group_id;
4726:
4727: IF (PO_LOG.d_proc)
4728: THEN
4729: PO_LOG.proc_begin(d_module, 'x_attr_group_id', x_attr_group_id);
4730: PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
4731: PO_LOG.proc_end(d_module);
4725: x_attr_group_id := l_attr_group_id;
4726:
4727: IF (PO_LOG.d_proc)
4728: THEN
4729: PO_LOG.proc_begin(d_module, 'x_attr_group_id', x_attr_group_id);
4730: PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
4731: PO_LOG.proc_end(d_module);
4732: END IF;
4733:
4726:
4727: IF (PO_LOG.d_proc)
4728: THEN
4729: PO_LOG.proc_begin(d_module, 'x_attr_group_id', x_attr_group_id);
4730: PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
4731: PO_LOG.proc_end(d_module);
4732: END IF;
4733:
4734: EXCEPTION
4727: IF (PO_LOG.d_proc)
4728: THEN
4729: PO_LOG.proc_begin(d_module, 'x_attr_group_id', x_attr_group_id);
4730: PO_LOG.proc_begin(d_module, 'x_return_status', x_return_status);
4731: PO_LOG.proc_end(d_module);
4732: END IF;
4733:
4734: EXCEPTION
4735: WHEN OTHERS THEN
4734: EXCEPTION
4735: WHEN OTHERS THEN
4736: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4737:
4738: IF PO_LOG.d_stmt THEN
4739: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
4740: END IF;
4741:
4742: IF (PO_LOG.d_proc) THEN
4735: WHEN OTHERS THEN
4736: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4737:
4738: IF PO_LOG.d_stmt THEN
4739: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
4740: END IF;
4741:
4742: IF (PO_LOG.d_proc) THEN
4743: PO_LOG.proc_end(d_module);
4738: IF PO_LOG.d_stmt THEN
4739: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
4740: END IF;
4741:
4742: IF (PO_LOG.d_proc) THEN
4743: PO_LOG.proc_end(d_module);
4744: END IF;
4745:
4746: END get_attr_group_id;
4739: PO_LOG.stmt(d_module, d_progress,'exception '|| SQLERRM );
4740: END IF;
4741:
4742: IF (PO_LOG.d_proc) THEN
4743: PO_LOG.proc_end(d_module);
4744: END IF;
4745:
4746: END get_attr_group_id;
4747: