[Home] [Help]
PACKAGE BODY: APPS.GCS_CATEGORIES_PKG
Source
1 PACKAGE BODY GCS_CATEGORIES_PKG AS
2 /* $Header: gcscategoryb.pls 120.3 2006/06/29 15:05:56 hakumar noship $ */
3
4
5
6 PROCEDURE Insert_Row( row_id IN OUT NOCOPY VARCHAR2,
7 category_code VARCHAR2,
8 category_number NUMBER,
9 net_to_re_flag VARCHAR2,
10 target_entity_code VARCHAR2,
11 category_type_code VARCHAR2,
12 associated_object_id NUMBER,
13 org_output_code VARCHAR2,
14 support_multi_parents_flag VARCHAR2,
15 enabled_flag VARCHAR2,
16 specific_intercompany_id NUMBER,
17 category_name VARCHAR2,
18 description VARCHAR2,
19 creation_date DATE,
20 created_by NUMBER,
21 last_update_date DATE,
22 last_updated_by NUMBER,
23 last_update_login NUMBER,
24 object_version_number NUMBER) IS
25 CURSOR catg_row IS
26 SELECT rowid
27 FROM gcs_categories_b cb
28 WHERE cb.category_code= insert_row.category_code;
29
30 BEGIN
31 IF category_code IS NULL THEN
32 raise no_data_found;
33 END IF;
34
35 INSERT INTO gcs_categories_b
36 (category_code,
37 category_number,
38 net_to_re_flag,
39 target_entity_code,
40 category_type_code,
41 associated_object_id,
42 org_output_code,
43 support_multi_parents_flag,
44 enabled_flag,
45 specific_intercompany_id,
46 creation_date,
47 created_by,
48 last_update_date,
49 last_updated_by,
50 last_update_login,
51 object_version_number)
52 SELECT
53 insert_row.category_code,
54 insert_row.category_number,
55 insert_row.net_to_re_flag,
56 insert_row.target_entity_code,
57 insert_row.category_type_code,
58 insert_row.associated_object_id,
59 insert_row.org_output_code,
60 insert_row.support_multi_parents_flag,
61 insert_row.enabled_flag,
62 insert_row.specific_intercompany_id,
63 insert_row.creation_date,
64 insert_row.created_by,
65 insert_row.last_update_date,
66 insert_row.last_updated_by,
67 insert_row.last_update_login,
68 insert_row.object_version_number
69 FROM dual
70 WHERE NOT EXISTS
71 (SELECT 1
72 FROM gcs_categories_b cb
73 WHERE cb.category_code= insert_row.category_code);
74
75 -- Bugfix 5158937 : Inserted rows for the other installed languages on the env.
76 INSERT INTO gcs_categories_tl(
77 category_code,
78 language,
79 source_lang,
80 category_name,
81 description,
82 last_update_date,
83 last_updated_by,
84 last_update_login,
85 creation_date,
86 created_by)
87 -- Bugfix 5353211 : Qualify API variables with the API name, so that the values passed to the API are utilized
88 SELECT
89 insert_row.category_code,
90 L.language_code,
91 userenv('LANG'),
92 insert_row.category_name,
93 insert_row.description,
94 insert_row.last_update_date,
95 insert_row.last_updated_by,
96 insert_row.last_update_login,
97 insert_row.creation_date,
98 insert_row.created_by
99 FROM FND_LANGUAGES L
100 WHERE L.INSTALLED_FLAG in ('I', 'B')
101 AND NOT EXISTS
102 (SELECT NULL
103 FROM gcs_categories_tl ctl
104 WHERE ctl.category_code = insert_row.category_code
105 AND ctl.LANGUAGE = L.LANGUAGE_CODE);
106
107 OPEN catg_row;
108 FETCH catg_row INTO row_id;
109 IF catg_row%NOTFOUND THEN
110 CLOSE catg_row;
111 raise no_data_found;
112 END IF;
113 CLOSE catg_row;
114
115 END Insert_Row;
116
117 PROCEDURE Update_Row( row_id IN OUT NOCOPY VARCHAR2,
118 category_code VARCHAR2,
119 category_number NUMBER,
120 net_to_re_flag VARCHAR2,
121 target_entity_code VARCHAR2,
122 category_type_code VARCHAR2,
123 associated_object_id NUMBER,
124 org_output_code VARCHAR2,
125 support_multi_parents_flag VARCHAR2,
126 enabled_flag VARCHAR2,
127 specific_intercompany_id NUMBER,
128 category_name VARCHAR2,
129 description VARCHAR2,
130 creation_date DATE,
131 created_by NUMBER,
132 last_update_date DATE,
133 last_updated_by NUMBER,
134 last_update_login NUMBER,
135 object_version_number NUMBER) IS
136 BEGIN
137 UPDATE gcs_categories_b cb
138 SET category_number = update_row.category_number,
139 net_to_re_flag = update_row.net_to_re_flag,
140 target_entity_code = update_row.target_entity_code,
141 category_type_code = update_row.category_type_code,
142 associated_object_id = update_row.associated_object_id,
143 org_output_code = update_row.org_output_code,
144 support_multi_parents_flag = update_row.support_multi_parents_flag,
145 enabled_flag = update_row.enabled_flag,
146 specific_intercompany_id = update_row.specific_intercompany_id,
147 last_update_date = update_row.last_update_date,
148 last_updated_by = update_row.last_updated_by,
149 last_update_login = update_row.last_update_login,
150 object_version_number = update_row.object_version_number
151 WHERE cb.category_code = update_row.category_code;
152
153 IF SQL%NOTFOUND THEN
154 raise no_data_found;
155 END IF;
156
157 -- Bugfix 5158937 : Inserted rows for the other installed languages on the env.
158 INSERT INTO gcs_categories_tl(
159 category_code,
160 language,
161 source_lang,
162 category_name,
163 description,
164 last_update_date,
165 last_updated_by,
166 last_update_login,
167 creation_date,
168 created_by)
169 -- Bugfix 5353211 : Qualify API variables with the API name, so that the values passed to the API are utilized
170 SELECT
171 update_row.category_code,
172 L.language_code,
173 userenv('LANG'),
174 update_row.category_name,
175 update_row.description,
176 update_row.last_update_date,
177 update_row.last_updated_by,
178 update_row.last_update_login,
179 update_row.creation_date,
180 update_row.created_by
181 FROM FND_LANGUAGES L
182 WHERE L.INSTALLED_FLAG in ('I', 'B')
183 AND NOT EXISTS
184 (SELECT NULL
185 FROM gcs_categories_tl ctl
186 WHERE ctl.category_code = update_row.category_code
187 AND ctl.LANGUAGE = L.LANGUAGE_CODE);
188
189
190 UPDATE gcs_categories_tl ctl
191 SET category_name = update_row.category_name,
192 description = update_row.description,
193 last_update_date = update_row.last_update_date,
194 last_updated_by = update_row.last_updated_by,
195 last_update_login = update_row.last_update_login
196 WHERE ctl.category_code = update_row.category_code
197 AND ctl.language = userenv('LANG');
198
199 IF SQL%NOTFOUND THEN
200 raise no_data_found;
201 END IF;
202 END Update_Row;
203
204
205
206 PROCEDURE Load_Row( category_code VARCHAR2,
207 owner VARCHAR2,
208 last_update_date VARCHAR2,
209 custom_mode VARCHAR2,
210 category_number NUMBER,
211 net_to_re_flag VARCHAR2,
212 target_entity_code VARCHAR2,
213 category_type_code VARCHAR2,
214 associated_object_id NUMBER,
215 org_output_code VARCHAR2,
216 support_multi_parents_flag VARCHAR2,
217 enabled_flag VARCHAR2,
218 specific_intercompany_id NUMBER,
219 category_name VARCHAR2,
220 description VARCHAR2,
221 object_version_number NUMBER) IS
222
223 row_id VARCHAR2(64);
224 f_luby NUMBER; -- category owner in file
225 f_ludate DATE; -- category update date in file
226 db_luby NUMBER; -- category owner in db
227 db_ludate DATE; -- category update date in db
228
229 f_start_date DATE; -- start date in file
230 BEGIN
231 -- Get last updated information from the loader data file
232 f_luby := fnd_load_util.owner_id(owner);
233 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
234
235 BEGIN
236 SELECT cb.last_updated_by, cb.last_update_date
237 INTO db_luby, db_ludate
238 FROM GCS_CATEGORIES_B cb
239 WHERE cb.category_code = load_row.category_code;
240
241 -- Test for customization information
242 IF fnd_load_util.upload_test( f_luby, f_ludate, db_luby, db_ludate,
243 custom_mode) THEN
244
245 update_row( row_id => row_id,
246 category_code => load_row.CATEGORY_CODE,
247 category_number => load_row.CATEGORY_NUMBER,
248 net_to_re_flag => load_row.NET_TO_RE_FLAG,
249 target_entity_code => load_row.TARGET_ENTITY_CODE,
250 category_type_code => load_row.CATEGORY_TYPE_CODE,
251 associated_object_id => load_row.ASSOCIATED_OBJECT_ID,
252 org_output_code => load_row.ORG_OUTPUT_CODE,
253 support_multi_parents_flag => load_row.SUPPORT_MULTI_PARENTS_FLAG,
254 enabled_flag => load_row.ENABLED_FLAG,
255 specific_intercompany_id => load_row.SPECIFIC_INTERCOMPANY_ID,
256 category_name => load_row.CATEGORY_NAME,
257 description => load_row.DESCRIPTION,
258 creation_date => f_ludate,
259 created_by => f_luby,
260 last_update_date => f_ludate,
261 last_updated_by => f_luby,
262 last_update_login => 0,
263 object_version_number => load_row.OBJECT_VERSION_NUMBER);
264 END IF;
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 insert_row( row_id => row_id,
268 category_code => load_row.CATEGORY_CODE,
269 category_number => load_row.CATEGORY_NUMBER,
270 net_to_re_flag => load_row.NET_TO_RE_FLAG,
271 target_entity_code => load_row.TARGET_ENTITY_CODE,
272 category_type_code => load_row.CATEGORY_TYPE_CODE,
273 associated_object_id => load_row.ASSOCIATED_OBJECT_ID,
274 org_output_code => load_row.ORG_OUTPUT_CODE,
275 support_multi_parents_flag => load_row.SUPPORT_MULTI_PARENTS_FLAG,
276 enabled_flag => load_row.ENABLED_FLAG,
277 specific_intercompany_id => load_row.SPECIFIC_INTERCOMPANY_ID,
278 category_name => load_row.CATEGORY_NAME,
279 description => load_row.DESCRIPTION,
280 creation_date => f_ludate,
281 created_by => f_luby,
282 last_update_date => f_ludate,
283 last_updated_by => f_luby,
284 last_update_login => 0,
285 object_version_number => load_row.OBJECT_VERSION_NUMBER);
286 END;
287
288 END Load_Row;
289
290
291 PROCEDURE Translate_Row( category_code VARCHAR2,
292 owner VARCHAR2,
293 last_update_date VARCHAR2,
294 custom_mode VARCHAR2,
295 category_name VARCHAR2,
296 description VARCHAR2) IS
297
298 f_luby NUMBER; -- category owner in file
299 f_ludate DATE; -- category update date in file
300 db_luby NUMBER; -- category owner in db
301 db_ludate DATE; -- category update date in db
302 BEGIN
303 -- Get last updated information from the loader data file
304 f_luby := fnd_load_util.owner_id(owner);
305 f_ludate := nvl(to_date(last_update_date, 'YYYY/MM/DD'), sysdate);
306
307 BEGIN
308 SELECT ctl.last_updated_by, ctl.last_update_date
309 INTO db_luby, db_ludate
310 FROM GCS_CATEGORIES_TL ctl
311 WHERE ctl.category_code = translate_row.category_code
312 AND ctl.language = userenv('LANG');
313
314 -- Test for customization information
315 IF fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate,
316 custom_mode) THEN
317 UPDATE gcs_categories_tl ctl
318 SET category_name = translate_row.category_name,
319 description = translate_row.description,
320 source_lang = userenv('LANG'),
321 last_update_date = f_ludate,
322 last_updated_by = f_luby,
323 last_update_login = 0
324 WHERE ctl.category_code = translate_row.category_code
325 AND userenv('LANG') IN (ctl.language, ctl.source_lang);
326 END IF;
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 null;
330 END;
331 END Translate_Row;
332
333
334
335
336 PROCEDURE ADD_LANGUAGE
337 IS
338 BEGIN
339 INSERT /*+ append parallel(tt) */ INTO
340 GCS_CATEGORIES_TL tt
341 (
342 CATEGORY_CODE ,
343 CATEGORY_NAME ,
344 DESCRIPTION ,
345 CREATION_DATE ,
346 CREATED_BY ,
347 LAST_UPDATED_BY ,
348 LAST_UPDATE_DATE ,
349 LAST_UPDATE_LOGIN ,
350 LANGUAGE ,
351 SOURCE_LANG
352 )
353
354 SELECT /*+ parallel(v) parallel(t) use_nl(t) */
355 v.*
356 FROM
357 ( SELECT /*+ no_merge ordered parellel(b) */
358 B.CATEGORY_CODE ,
359 B.CATEGORY_NAME ,
360 B.DESCRIPTION ,
361 B.CREATION_DATE ,
362 B.CREATED_BY ,
363 B.LAST_UPDATED_BY ,
364 B.LAST_UPDATE_DATE ,
365 B.LAST_UPDATE_LOGIN ,
366 L.LANGUAGE_CODE ,
367 B.SOURCE_LANG
368
369 FROM GCS_CATEGORIES_TL B,
370 FND_LANGUAGES L
371 WHERE L.INSTALLED_FLAG in ('I', 'B')
372 AND B.LANGUAGE = userenv('LANG')
373 ) v,
374 GCS_CATEGORIES_TL T
375 WHERE T.CATEGORY_CODE(+) = v.CATEGORY_CODE
376 AND T.LANGUAGE(+) = v.LANGUAGE_CODE
377 AND T.CATEGORY_CODE IS NULL;
378
379 END ADD_LANGUAGE;
380
381
382
383 BEGIN
384
385 SELECT category_code,
386 category_number,
387 net_to_re_flag,
388 target_entity_code,
389 support_multi_parents_flag
390 BULK COLLECT INTO g_oper_category_info
391 FROM gcs_categories_b
392 WHERE target_entity_code = 'CHILD'
393 --Bugfix : 4209435
394 AND enabled_flag = 'Y'
395 AND category_type_code IN ('CONSOLIDATION_RULE', 'ELIMINATION_RULE')
396 ORDER BY category_number;
397
398 SELECT category_code,
399 category_number,
400 net_to_re_flag,
401 target_entity_code,
402 support_multi_parents_flag
403 BULK COLLECT INTO g_cons_category_info
404 FROM gcs_categories_b
405 WHERE target_entity_code IN ('ELIMINATION', 'PARENT')
406 --Bugfix : 4209435
407 AND enabled_flag = 'Y'
408 AND category_type_code IN ('CONSOLIDATION_RULE', 'ELIMINATION_RULE')
409 ORDER BY category_number;
410
411 END GCS_CATEGORIES_PKG;