[Home] [Help]
PACKAGE BODY: APPS.MTL_MVT_STATS_RULE_SETS_PKG
Source
1 PACKAGE BODY MTL_MVT_STATS_RULE_SETS_PKG AS
2 -- $Header: INVGVRSB.pls 115.1 2002/12/03 22:09:25 vma ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998,1999 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVGVRSB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to create procedure for inserting rows, updating |
13 --| rows, locking rows and deleting rows on tables |
14 --| MTL_MVT_STATS_RULE_SETS and MTL_MVT_STATS_RULE_SETS_TL |
15 --| |
16 --| PROCEDURE LIST |
17 --| PROCEDURE Insert_Row |
18 --| PROCEDURE Update_Row |
19 --| PROCEDURE Lock_Row |
20 --| PROCEDURE Delete_Row |
21 --| PROCEDURE Add_Language |
22 --| PROCEDURE Translate_Row |
23 --| PROCEDURE Load_Row |
24 --| |
25 --| HISTORY |
26 --| 07/13/00 Komal Saini Created |
27 --| 11/26/02 Vivian Ma Added NOCOPY to x_rowid of Insert_Row to |
28 --| comply with new PL/SQL standard for better |
29 --| performance |
30 --| |
31 --+======================================================================*/
32
33 --==================
34 --CONSTANTS
35 --==================
36 G_PKG_NAME CONSTANT VARCHAR2(30) := 'MTL_MVT_STATS_RULE_SETS_PKG';
37
38 --==================
39 --PUBLIC PROCEDURE
40 --==================
41 --=========================================================================
42 --PRECEDURE : Insert_Row Public
43 --PARAMETERS: see below
44 --COMMENT : table handler for inserting data into table mtl_mvt_stats_rule_sets
45 -- _b and table mtl_mvt_stats_rule_sets_tl
46 --==========================================================================
47 PROCEDURE Insert_Row
48 ( x_rowid IN OUT NOCOPY VARCHAR2
49 , p_rule_set_code IN VARCHAR2
50 , p_rule_set_display_name IN VARCHAR2
51 , p_rule_set_description IN VARCHAR2
52 , p_rule_set_type IN VARCHAR2
53 , p_category_set_id IN NUMBER
54 , p_seeded_flag IN VARCHAR2
55 , p_creation_date IN DATE
56 , p_created_by IN NUMBER
57 , p_last_update_date IN DATE
58 , p_last_updated_by IN NUMBER
59 , p_last_update_login IN NUMBER
60 )
61 IS
62 CURSOR c IS
63 SELECT
64 rowid
65 FROM
66 MTL_MVT_STATS_RULE_SETS_B
67 WHERE Rule_Set_code = p_rule_set_code;
68 BEGIN
69 INSERT INTO MTL_MVT_STATS_RULE_SETS_B
70 ( Rule_Set_code
71 , Rule_set_type
72 , Seeded_Flag
73 , Category_Set_Id
74 , creation_date
75 , created_by
76 , last_update_date
77 , last_updated_by
78 , last_update_login
79 )
80 VALUES
81 ( p_rule_set_code
82 , p_Rule_set_type
83 , p_seeded_flag
84 , p_category_set_id
85 , p_creation_date
86 , p_created_by
87 , p_last_update_date
88 , p_last_updated_by
89 , p_last_update_login
90 );
91
92 INSERT INTO MTL_MVT_STATS_RULE_SETS_TL
93 ( Rule_Set_code
94 , Rule_Set_display_name
95 , Rule_Set_description
96 , created_by
97 , creation_date
98 , last_updated_by
99 , last_update_date
100 , last_update_login
101 , language
102 , source_lang
103 )
104 SELECT
105 p_rule_set_code
106 , p_rule_set_display_name
107 , p_rule_set_description
108 , p_created_by
109 , p_creation_date
110 , p_last_updated_by
111 , p_last_update_date
112 , p_last_update_login
113 , L.language_code
114 , USERENV('LANG')
115 FROM
116 FND_LANGUAGES L
117 WHERE L.installed_flag IN ('I', 'B')
118 AND NOT EXISTS
119 (SELECT
120 NULL
121 FROM
122 MTL_MVT_STATS_RULE_SETS_TL T
123 WHERE T.Rule_Set_code = p_rule_set_code
124 AND T.language = L.language_code);
125
126 OPEN c;
127 FETCH c INTO x_rowid;
128 IF (c%NOTFOUND)
129 THEN
130 CLOSE c;
131 RAISE NO_DATA_FOUND;
132 END IF;
133 CLOSE c;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
138 THEN
139 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Insert_Row');
140 END IF;
141 RAISE;
142
143 END INSERT_ROW;
144
145 --=========================================================================
146 --PRECEDURE : Lock_Row Public
147 --PARAMETERS: see below
148 --COMMENT : table handler for locking table mtl_mvt_stats_rule_sets_b and
149 -- table mtl_mvt_stats_rule_sets_tl
150 --EXCEPTION : record_changed;
151 --==========================================================================
152 PROCEDURE Lock_Row
153 ( p_rule_set_code IN VARCHAR2
154 , p_rule_set_display_name IN VARCHAR2
155 , p_rule_set_description IN VARCHAR2
156 )
157 IS
158 CURSOR c IS
159 SELECT *
160 FROM
161 MTL_MVT_STATS_RULE_SETS_B
162 WHERE Rule_Set_code = p_rule_set_code
163 FOR UPDATE OF Rule_Set_code NOWAIT;
164 recinfo c%ROWTYPE;
165
166 CURSOR c1 IS
167 SELECT
168 Rule_Set_display_name
169 , Rule_Set_description
170 , decode(language, USERENV('LANG'), 'Y', 'N') baselang
171 FROM
172 MTL_MVT_STATS_RULE_SETS_TL
173 WHERE Rule_Set_code = p_rule_set_code
174 FOR UPDATE OF Rule_Set_code NOWAIT;
175
176 record_changed EXCEPTION;
177
178 BEGIN
179 OPEN c;
180 FETCH C INTO recinfo;
181 IF (c%NOTFOUND)
182 THEN
183 CLOSE c;
184 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
185 APP_EXCEPTION.Raise_Exception;
186 END IF;
187 CLOSE c;
188
189 FOR tlinfo IN c1
190 LOOP
191 IF (tlinfo.baselang = 'Y')
192 THEN
193 IF NOT ( (tlinfo.Rule_Set_display_name = p_rule_set_display_name )
194 AND (tlinfo.Rule_Set_description = p_rule_set_description) )
195 THEN
196 RAISE record_changed;
197 END IF;
198 END IF;
199 END LOOP;
200 RETURN;
201
202 EXCEPTION
203 WHEN record_changed THEN
204 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
205 APP_EXCEPTION.raise_exception;
206 WHEN OTHERS THEN
207 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
208 THEN
209 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Insert_Row');
210 END IF;
211 RAISE;
212
213 END LOCK_ROW;
214
215 --=========================================================================
216 --PRECEDURE : Update_Row Public
217 --PARAMETERS: see below
218 --COMMENT : table handler for updating data of table mtl_mvt_stats_rule_sets
219 -- _b and table mtl_mvt_stats_rule_sets_tl
220 --==========================================================================
221 PROCEDURE Update_Row
222 ( p_rule_set_code IN VARCHAR2
223 , p_rule_set_display_name IN VARCHAR2
224 , p_rule_set_description IN VARCHAR2
225 , p_rule_set_type IN VARCHAR2
226 , p_category_set_id IN NUMBER
227 , p_last_update_date IN DATE
228 , p_last_updated_by IN NUMBER
229 , p_last_update_login IN NUMBER
230 )
231 IS
232 BEGIN
233 UPDATE MTL_MVT_STATS_RULE_SETS_B
234 SET
235 last_update_date = p_last_update_date
236 , last_updated_by = p_last_updated_by
237 , last_update_login = p_last_update_login
238 , Rule_Set_type = p_rule_set_type
239 , Category_Set_Id = p_category_set_id
240 WHERE Rule_Set_code = p_rule_set_code;
241
242 IF (SQL%NOTFOUND)
243 THEN
244 RAISE NO_DATA_FOUND;
245 END IF;
246
247 UPDATE MTL_MVT_STATS_RULE_SETS_TL
248 SET
249 Rule_Set_display_name = p_rule_set_display_name
250 , Rule_Set_description = p_rule_set_description
251 , last_update_date = p_last_update_date
252 , last_updated_by = p_last_updated_by
253 , last_update_login = p_last_update_login
254 , source_lang = USERENV('LANG')
255 WHERE Rule_Set_code = p_rule_set_code
256 AND USERENV('LANG') IN (language, source_lang);
257
258 IF (SQL%NOTFOUND)
259 THEN
260 RAISE NO_DATA_FOUND;
261 END IF;
262
263 EXCEPTION
264 WHEN OTHERS THEN
265 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
266 THEN
267 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Update_Row');
268 END IF;
269 RAISE;
270
271 END Update_Row;
272
273 --=========================================================================
274 --PRECEDURE : Delete_Row Public
275 --PARAMETERS: see below
276 --COMMENT : table handler for deleting data from table mtl_mvt_stats_rule_sets
277 -- _b and table mtl_mvt_stats_rule_sets_tl
278 --==========================================================================
279 PROCEDURE Delete_Row
280 ( p_rule_set_code IN VARCHAR2
281 )
282 IS
283 BEGIN
284 DELETE FROM MTL_MVT_STATS_RULE_SETS_TL
285 WHERE Rule_Set_code = p_rule_set_code;
286
287 IF (SQL%NOTFOUND)
288 THEN
289 RAISE NO_DATA_FOUND;
290 END IF;
291
292 DELETE FROM MTL_MVT_STATS_RULE_SETS_B
293 WHERE Rule_Set_code = p_rule_set_code;
294
295 IF (SQL%NOTFOUND)
296 THEN
297 RAISE NO_DATA_FOUND;
298 END IF;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
303 THEN
304 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Delete_Row');
305 END IF;
306 RAISE;
307
308 END DELETE_ROW;
309
310 --=========================================================================
311 --PRECEDURE : Add_Language Public
312 --PARAMETERS: none
313 --COMMENT : called by NLADD script whenever a new language is added or
314 -- after any other operation
315 --==========================================================================
316 PROCEDURE Add_Language
317 IS
318 BEGIN
319 DELETE FROM MTL_MVT_STATS_RULE_SETS_TL T
320 WHERE NOT EXISTS
321 (SELECT
322 NULL
323 FROM
324 MTL_MVT_STATS_RULE_SETS_B B
325 WHERE B.Rule_Set_code = T.Rule_Set_code
326 );
327
328 UPDATE MTL_MVT_STATS_RULE_SETS_TL T
329 SET (Rule_Set_display_name
330 ,Rule_Set_description ) =
331 (SELECT
332 B.Rule_Set_display_name
333 , B.Rule_Set_description
334 FROM
335 MTL_MVT_STATS_RULE_SETS_TL B
336 WHERE B.Rule_Set_code = T.Rule_Set_code
337 AND B.language = T.source_lang)
338 WHERE (T.Rule_Set_code
339 , T.language
340 )IN (SELECT
341 SUBT.Rule_Set_code
342 , SUBT.language
343 FROM
344 MTL_MVT_STATS_RULE_SETS_TL SUBB
345 , MTL_MVT_STATS_RULE_SETS_TL SUBT
346 WHERE SUBB.Rule_Set_code = SUBT.Rule_Set_code
347 AND SUBB.language = SUBT.source_lang
348 AND (SUBB.Rule_Set_display_name <> SUBT.Rule_Set_display_name
349 OR SUBB.Rule_Set_description <> SUBT.Rule_Set_description));
350
351 INSERT INTO MTL_MVT_STATS_RULE_SETS_TL
352 ( Rule_Set_code
353 , Rule_Set_display_name
354 , Rule_Set_description
355 , created_by
356 , creation_date
357 , last_updated_by
358 , last_update_date
359 , last_update_login
360 , language
361 , source_lang
362 )
363 SELECT
364 B.Rule_Set_code
365 , B.Rule_Set_display_name
366 , B.Rule_Set_description
367 , B.created_by
368 , B.creation_date
369 , B.last_updated_by
370 , B.last_update_date
371 , B.last_update_login
372 , L.language_CODE
373 , B.source_lang
374 FROM
375 MTL_MVT_STATS_RULE_SETS_TL B
376 , FND_LANGUAGES L
377 WHERE L.INSTALLED_FLAG IN ('I', 'B')
378 AND B.language = USERENV('LANG')
379 AND NOT EXISTS
380 (SELECT
381 NULL
382 FROM
383 MTL_MVT_STATS_RULE_SETS_TL T
384 WHERE T.Rule_Set_code = B.Rule_Set_code
385 AND T.language = L.language_CODE);
386
387 EXCEPTION
388 WHEN OTHERS THEN
389 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
390 THEN
391 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Add_Language');
392 END IF;
393 RAISE;
394
395 END Add_Language;
396
397 --========================================================================
398 -- PROCEDURE : Translate_Row PUBLIC
399 -- PARAMETERS: p_rule_set_code rule sets code (develper's key)
400 -- p_rule_set_display_name rule sets name
401 -- p_rule_set_description description
402 -- p_owner user owning the row (SEED or other)
403 -- COMMENT : used to upload seed data in NLS mode
404 --========================================================================
405 PROCEDURE Translate_Row
406 ( p_rule_set_code IN VARCHAR2
407 , p_rule_set_display_name IN VARCHAR2
408 , p_rule_set_description IN VARCHAR2
409 , p_owner IN VARCHAR2
410 )
411 IS
412 BEGIN
413
414 UPDATE mtl_mvt_stats_rule_sets_tl
415 SET Rule_Set_display_name = p_rule_set_display_name
416 , Rule_Set_description = p_rule_set_description
417 , last_update_date = SYSDATE
418 , last_updated_by = DECODE(p_owner, 'SEED', 1, 0)
419 , last_update_login = 0
420 , source_lang = userenv('LANG')
421 WHERE Rule_Set_code = p_rule_set_code
422 AND userenv('LANG') IN (language, source_lang);
423
424 EXCEPTION
425 WHEN OTHERS THEN
426 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
427 THEN
428 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Translate_Row');
429 END IF;
430 RAISE;
431
432 END Translate_Row;
433
434 --========================================================================
435 -- PRECEDURE : Load_Row PUBLIC
436 -- PARAMETERS: p_rule_set_code rule sets code (develper's key)
437 -- p_owner user owning the row (SEED or other)
438 -- p_rule_set_display_name rule sets name
442 PROCEDURE Load_Row
439 -- p_rule_set_description description
440 -- COMMENT : used to upload seed data in MLS mode
441 --========================================================================
443 ( p_rule_set_code IN VARCHAR2
444 , p_owner IN VARCHAR2
445 , p_rule_set_display_name IN VARCHAR2
446 , p_rule_set_description IN VARCHAR2
447 , p_Rule_set_type IN VARCHAR2
448 , p_category_set_id IN NUMBER
449 , p_seeded_flag IN VARCHAR2
450 )
451 IS
452
453 l_row_id VARCHAR2(20);
454 l_user_id NUMBER;
455
456 BEGIN
457
458 -- assign user ID
459 IF (p_owner = 'SEED')
460 THEN
461 l_user_id := 1;
462 ELSE
463 l_user_id := 0;
464 END IF;
465
466 BEGIN
467 -- update row if present
468 Update_Row
469 ( p_rule_set_code => p_rule_set_code
470 , p_rule_set_display_name => p_rule_set_display_name
471 , p_rule_set_description => p_rule_set_description
472 , p_rule_set_type => p_rule_set_type
473 , p_category_set_id => p_category_set_id
474 , p_last_update_date => SYSDATE
475 , p_last_updated_by => l_user_id
476 , p_last_update_login => 0
477 );
478 EXCEPTION
479 WHEN NO_DATA_FOUND THEN
480 -- insert row
481 Insert_Row
482 ( x_rowid => l_row_id
483 , p_rule_set_code => p_rule_set_code
484 , p_rule_set_display_name => p_rule_set_display_name
485 , p_rule_set_description => p_rule_set_description
486 , p_rule_set_type => p_rule_set_type
487 , p_category_set_id => p_category_set_id
488 , p_seeded_flag => p_seeded_flag
489 , p_creation_date => SYSDATE
490 , p_created_by => l_user_id
491 , p_last_update_date => SYSDATE
492 , p_last_updated_by => l_user_id
493 , p_last_update_login => 0
494 );
495 END;
496
497 EXCEPTION
498 WHEN OTHERS THEN
499 IF FND_MSG_PUB.Check_Msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
500 THEN
501 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'Load_Row');
502 END IF;
503 RAISE;
504
505 END Load_Row;
506
507
508 END MTL_MVT_STATS_RULE_SETS_PKG;