TYPE BODY: APPS.PO_UDA_USAGE_OBJECT

Source

1 TYPE BODY po_uda_usage_object AS
2 /* $Header: PO_UDA_USAGE_OBJECT.sql 120.1 2010/07/02 08:37:14 adjain noship $ */
3 
4 /**
5 * Start of Comments
6 * Function:
7 *   Creates an empty instance of this type.
8 * Returns:
9 *   An empty instance of this type.
10 * End of Comments
11 */
12 CONSTRUCTOR FUNCTION PO_UDA_USAGE_OBJECT RETURN SELF AS RESULT
13 IS
14 BEGIN
15 RETURN;
16 END;
17 
18 /**
19  * Creates a new instance of this type, with all attributes initialized.
20  */
21 STATIC FUNCTION new_instance  (x_template_id IN NUMBER, x_usage_type IN VARCHAR2 , x_context IN EGO_COL_NAME_VALUE_PAIR_ARRAY)
22 RETURN PO_UDA_USAGE_OBJECT
23 IS
24 l_instance PO_UDA_USAGE_OBJECT;
25 BEGIN
26 l_instance := PO_UDA_USAGE_OBJECT();
27 l_instance.initialize (x_template_id , x_usage_type , x_context );
28 RETURN l_instance;
29 END new_instance;
30 
31 /**
32  * Start of Comments
33  * Pre-reqs: None.
34  * Modifies: SELF.
35  * Locks: None.
36  * Function:
37  *  Sets all of the attributes of this object to be value passed in and fire a dynamic query to get
38  *  List of  attribute Groups.
39  * End of Comments
40  */
41 MEMBER PROCEDURE initialize (x_template_id IN NUMBER, x_usage_type IN VARCHAR2 , x_context IN EGO_COL_NAME_VALUE_PAIR_ARRAY)
42 IS
43     l_dynamic_sql     VARCHAR2(2000);
44     l_strInner_sql    VARCHAR2(2000);
45     l_add_sql         VARCHAR2(2000);
46     l_context_value   VARCHAR2(100);
47     l_index NUMBER ;
48   CURSOR cur_usage_col_mappping (v_usage_type VARCHAR2 ) IS
49               SELECT APPLICATION_COLUMN_NAME, END_USER_COLUMN_NAME
50               FROM FND_DESCR_FLEX_COL_USAGE_VL
51               WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'PO_UDA_TEMPLATE_USAGES'
52               AND DESCRIPTIVE_FLEX_CONTEXT_CODE = v_usage_type  ;
53 
54 BEGIN
55   l_dynamic_sql := ' SELECT u.attribute_group_id '||
56                          ' FROM po_uda_ag_template_usages u, po_uda_ag_templates t '||
57                          ' WHERE t.template_id = u.template_id and t.template_id = '|| x_template_id ||
58                          ' AND ATTRIBUTE_CATEGORY = ' || '''' || x_usage_type || '''' ;
59 
60   l_strInner_sql := ' AND NOT EXISTS (  SELECT 1
61                               FROM po_uda_ag_template_usages tu
62                               WHERE  tu.template_id = u.template_id
63                               AND tu.ATTRIBUTE_CATEGORY = u.ATTRIBUTE_CATEGORY ';
64 
65   TEMPLATE_ID:=  x_template_id;
66   USAGE_TYPE := x_usage_type;
67   Context := x_context;
68 
69   FOR rec_usage_col_mappping IN  cur_usage_col_mappping( x_usage_type )  LOOP
70    l_index := 0;
71    l_index := x_context.FIRST;
72    WHILE (l_index <= x_context.LAST) LOOP
73       IF (x_context(l_index).NAME = rec_usage_col_mappping.END_USER_COLUMN_NAME ) THEN
74          l_context_value := x_context(l_index).Value;
75       END IF ;
76     l_index := x_context.NEXT(l_index);
77    END LOOP;
78 
79    l_add_sql := l_add_sql || ' AND ( u.' ||  rec_usage_col_mappping.APPLICATION_COLUMN_NAME || ' = ' || '''' || l_context_value || ''''
80                    || ' OR ( u.'  ||  rec_usage_col_mappping.APPLICATION_COLUMN_NAME   || ' = ''DEFAULT'' ' || l_strInner_sql
81                    || ' AND tu.' ||  rec_usage_col_mappping.APPLICATION_COLUMN_NAME || ' = ' || '''' || l_context_value || '''' || ' )))' ;
82 
83  END LOOP;
84 
85  l_dynamic_sql := l_dynamic_sql || l_add_sql ;
86  EXECUTE IMMEDIATE l_dynamic_sql BULK COLLECT  INTO  ATTR_GROUP_ID;
87 
88 END initialize ;
89 
90 END;