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