[Home] [Help]
PACKAGE BODY: APPS.HZ_SEED_CLASS_CAT_PKG
Source
1 PACKAGE BODY HZ_SEED_CLASS_CAT_PKG AS
2 /*$Header: ARHSECLB.pls 120.6.12020000.2 2012/11/27 12:15:25 rgokavar ship $*/
3 --Bug 15893080 - Remove direct access from all_tab_columns
4 PROCEDURE SEED_CLASS_CATEGORY
5 (p_class IN VARCHAR2,
6 p_amaf IN VARCHAR2,
7 p_ampf IN VARCHAR2,
8 p_alnof IN VARCHAR2,
9 p_user_id IN NUMBER DEFAULT 0)
10 IS
11 /* CURSOR c1(l_schema IN VARCHAR2) IS
12 SELECT 'Y'
13 FROM sys.all_tab_columns
14 WHERE table_name = 'HZ_CLASS_CATEGORIES'
15 AND column_name = 'ALLOW_LEAF_NODE_ONLY_FLAG'
16 AND owner = l_schema;
17 */
18 CURSOR c1(l_schema IN VARCHAR2) IS
19 select 'Y'
20 from
21 user_synonyms syn
22 , dba_tab_columns col
23 where syn.synonym_name = 'HZ_CLASS_CATEGORIES'
24 and col.owner = syn.table_owner
25 and col.table_name = syn.table_name
26 and col.column_name = 'ALLOW_LEAF_NODE_ONLY_FLAG';
27
28 lyn VARCHAR2(1);
29 no_data_found1 exception;
30 no_data_found2 exception;
31
32 CURSOR c2 (l_cat IN VARCHAR2) IS
33 SELECT 'Y'
34 FROM hz_class_categories
35 WHERE class_category = l_cat;
36 lyn2 VARCHAR2(1);
37
38 l_str VARCHAR2(2000);
39 ph INTEGER;
40 rt INTEGER;
41 l_allow_maf VARCHAR2(1);
42 l_allow_mpf VARCHAR2(1);
43 l_allow_lnof VARCHAR2(1);
44 l_class_cat VARCHAR2(30);
45 l_bool BOOLEAN;
46 l_status VARCHAR2(255);
47 l_schema VARCHAR2(255);
48 l_tmp VARCHAR2(2000);
49
50 BEGIN
51
52 l_allow_maf := p_amaf;
53 l_allow_mpf := p_ampf;
54 l_allow_lnof := p_alnof;
55 l_class_cat := REPLACE(p_class,'''','''''');
56 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
57
58 OPEN c1(l_schema);
59 fetch c1 INTO lyn;
60 IF c1%NOTFOUND THEN
61 lyn := 'N';
62 END IF;
63 CLOSE c1;
64
65 OPEN c2(l_class_cat);
66 FETCH c2 INTO lyn2;
67 IF c2%NOTFOUND THEN
68 lyn2 := 'N';
69 END IF;
70 CLOSE c2;
71
72
73 IF lyn = 'Y' THEN
74 IF lyn2 = 'Y' THEN
75
76 l_str :=' update HZ_CLASS_CATEGORIES set '||
77 ' ALLOW_MULTI_ASSIGN_FLAG = '''||l_allow_maf||''', '||
78 ' ALLOW_MULTI_PARENT_FLAG = '''||l_allow_mpf||''', '||
79 ' ALLOW_LEAF_NODE_ONLY_FLAG = '''||l_allow_lnof||''', '||
80 ' LAST_UPDATED_BY = '''||TO_CHAR(p_user_id)||''', '||
81 ' LAST_UPDATE_DATE = '''|| TO_CHAR(SYSDATE)||''', '||
82 ' LAST_UPDATE_LOGIN = 0 '||
83 ' where CLASS_CATEGORY = '''||l_class_cat||'''';
84 ELSE
85
86 l_str :=' insert into HZ_CLASS_CATEGORIES( '||
87 ' CLASS_CATEGORY, '||
88 ' ALLOW_MULTI_ASSIGN_FLAG, '||
89 ' ALLOW_MULTI_PARENT_FLAG, '||
90 ' ALLOW_LEAF_NODE_ONLY_FLAG, '||
91 ' DELIMITER, ' ||
92 ' FROZEN_FLAG, ' ||
93 ' LAST_UPDATED_BY, '||
94 ' LAST_UPDATE_DATE, '||
95 ' CREATED_BY, '||
96 ' CREATION_DATE, '||
97 ' LAST_UPDATE_LOGIN) values ( '||
98 ' '''||l_class_cat||''', '||
99 ' '''||l_allow_maf||''', '||
100 ' '''||l_allow_mpf||''', '||
101 ' '''||l_allow_lnof||''', '||
102 ' ''/'','||
103 ' ''N'','||
104 ' '''||TO_CHAR(p_user_id)||''', '||
105 ' '''||TO_CHAR(sysdate)||''', '||
106 ' '''||TO_CHAR(p_user_id)||''', '||
107 ' '''||TO_CHAR(sysdate)||''', '||
108 ' 0) ';
109 END IF;
110
111 ELSE
112
113 IF lyn2 = 'Y' THEN
114
115 l_str := ' update HZ_CLASS_CATEGORIES set '||
116 ' ALLOW_MULTI_ASSIGN_FLAG = '''||l_allow_maf||''', '||
117 ' ALLOW_MULTI_PARENT_FLAG = '''||l_allow_mpf||''', '||
118 ' LAST_UPDATED_BY = '''||TO_CHAR(p_user_id)||''', '||
119 ' LAST_UPDATE_DATE = '''|| TO_CHAR(SYSDATE)||''', '||
120 ' LAST_UPDATE_LOGIN = 0 '||
121 ' where CLASS_CATEGORY = '''||l_class_cat||'''';
122 ELSE
123
124 l_str :=' insert into HZ_CLASS_CATEGORIES( '||
125 ' CLASS_CATEGORY, '||
126 ' ALLOW_MULTI_ASSIGN_FLAG, '||
127 ' ALLOW_MULTI_PARENT_FLAG, '||
128 ' DELIMITER, ' ||
129 ' FROZEN_FLAG, ' ||
130 ' LAST_UPDATED_BY, '||
131 ' LAST_UPDATE_DATE, '||
132 ' CREATED_BY, '||
133 ' CREATION_DATE, '||
134 ' LAST_UPDATE_LOGIN) values ( '||
135 ' '''||l_class_cat||''', '||
136 ' '''||l_allow_maf||''', '||
137 ' '''||l_allow_mpf||''', '||
138 ' ''/'','||
139 ' ''N'','||
140 ' '''||TO_CHAR(p_user_id)||''', '||
141 ' '''||TO_CHAR(sysdate)||''', '||
142 ' '''||TO_CHAR(p_user_id)||''', '||
143 ' '''||TO_CHAR(sysdate)||''', '||
144 ' 0) ';
145 END IF;
146 END IF;
147
148 ph := dbms_sql.open_cursor;
149 dbms_sql.parse(ph,l_str,dbms_sql.native);
150 rt := dbms_sql.execute(ph);
151 dbms_sql.close_cursor(ph);
152 END;
153 --Bug 15893080 - Remove direct access from all_tab_columns
154 PROCEDURE SEED_CLASS_CATEGORY_USE
155 (p_class IN VARCHAR2,
156 p_col_name IN VARCHAR2,
157 p_awc IN VARCHAR2,
158 p_owner_tab IN VARCHAR2,
159 p_user_id IN NUMBER DEFAULT 0)
160 IS
161
162 /* CURSOR c1(l_schema IN VARCHAR2) IS
163 SELECT 'Y'
164 FROM sys.all_tab_columns
165 WHERE table_name = 'HZ_CLASS_CATEGORY_USES'
166 AND column_name = 'ADDITIONAL_WHERE_CLAUSE'
167 AND owner = l_schema;
168 */
169 CURSOR c1(l_schema IN VARCHAR2) IS
170 select 'Y'
171 from
172 user_synonyms syn
173 , dba_tab_columns col
174 where syn.synonym_name = 'HZ_CLASS_CATEGORY_USES'
175 and col.owner = syn.table_owner
176 and col.table_name = syn.table_name
177 and col.column_name = 'ADDITIONAL_WHERE_CLAUSE';
178
179 TYPE refcur IS REF CURSOR;
180 exist_cv refcur;
181 lyn2 VARCHAR2(1);
182 lyn VARCHAR2(1);
183 l_str VARCHAR2(4000);
184 ph INTEGER;
185 rt INTEGER;
186 l_awc VARCHAR2(2000);
187 l_class VARCHAR2(40);
188 l_owner_tab VARCHAR2(30);
189 l_col_name VARCHAR2(30);
190 l_bool BOOLEAN;
191 l_status VARCHAR2(255);
192 l_schema VARCHAR2(255);
193 l_tmp VARCHAR2(2000);
194
195 BEGIN
196 l_bool := fnd_installation.GET_APP_INFO('AR',l_status,l_tmp,l_schema);
197
198 OPEN c1(l_schema);
199 FETCH c1 INTO lyn;
200 IF c1%NOTFOUND THEN
201 lyn := 'N';
202 END IF;
203 CLOSE c1;
204
205 IF lyn = 'Y' THEN
206
207 l_awc := REPLACE(p_awc,'''','''''');
208 l_owner_tab := REPLACE(p_owner_tab,'''','''''');
209 l_col_name := REPLACE(p_col_name,'''','''''');
210 l_class := REPLACE(p_class,'''','''''');
211
212 OPEN exist_cv FOR
213 'SELECT ''Y'' FROM HZ_CLASS_CATEGORY_USES
214 WHERE owner_table = '''||l_owner_tab||''' AND '||
215 ' class_category = '''||l_class||'''';
216 FETCH exist_cv INTO lyn2;
217 IF exist_cv%NOTFOUND THEN
218 lyn2 := 'N';
219 END IF;
220 CLOSE exist_cv;
221
222 IF lyn2 = 'Y' THEN
223 l_str := ' UPDATE HZ_CLASS_CATEGORY_USES SET COLUMN_NAME = '''|| l_col_name ||''', '||
224 ' ADDITIONAL_WHERE_CLAUSE = '''||l_awc||''', '||
225 ' LAST_UPDATED_BY = '''||to_char(p_user_id)||''', '||
226 ' LAST_UPDATE_DATE = '''||to_char(SYSDATE)||''', '||
227 ' LAST_UPDATE_LOGIN = 0 '||
228 ' WHERE CLASS_CATEGORY = '''||l_class||''''||
229 ' AND OWNER_TABLE = '''||l_owner_tab||'''';
230 ELSE
231 l_str := ' insert into HZ_CLASS_CATEGORY_USES( CLASS_CATEGORY, OWNER_TABLE, COLUMN_NAME, '||
232 ' ADDITIONAL_WHERE_CLAUSE, LAST_UPDATED_BY, LAST_UPDATE_DATE, CREATED_BY, '||
233 ' CREATION_DATE, LAST_UPDATE_LOGIN ) values ( '||
234 ''''||l_class||''','''||l_owner_tab||''','''||l_col_name||''','''||l_awc||''','||
235 ''''||TO_CHAR(p_user_id)||''','''||TO_CHAR(sysdate)||''','''||TO_CHAR(p_user_id)||''','||
236 ''''||TO_CHAR(sysdate)||''', 0)';
237 END IF;
238
239 -- dbms_output.put_line(substr(l_str,1,255));
240 -- dbms_output.put_line(substr(l_str,256,255));
241
242 ph := dbms_sql.open_cursor;
243 dbms_sql.parse(ph,l_str,dbms_sql.native);
244 rt := dbms_sql.execute(ph);
245 dbms_sql.close_cursor(ph);
246
247 END IF;
248 END;
249 END;