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