[Home] [Help]
PACKAGE BODY: APPS.IGS_UC_REF_COUNTRY_PKG
Source
1 PACKAGE BODY igs_uc_ref_country_pkg AS
2 /* $Header: IGSXI55B.pls 120.0 2006/08/21 03:42:56 jbaber noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_uc_ref_country%ROWTYPE;
6 new_references igs_uc_ref_country%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_country_code IN VARCHAR2,
12 x_description IN VARCHAR2,
13 x_type IN VARCHAR2,
14 x_imported IN VARCHAR2,
15 x_creation_date IN DATE,
16 x_created_by IN NUMBER,
17 x_last_update_date IN DATE,
18 x_last_updated_by IN NUMBER,
19 x_last_update_login IN NUMBER
20 ) AS
21 /*
22 || Created By : [email protected]
23 || Created On : 13-JUL-2006
24 || Purpose : Initialises the Old and New references for the columns of the table.
25 || Known limitations, enhancements or remarks :
26 || Change History :
27 || Who When What
28 || (reverse chronological order - newest change first)
29 */
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM igs_uc_ref_country
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 OPEN cur_old_ref_values;
43 FETCH cur_old_ref_values INTO old_references;
44 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
45 CLOSE cur_old_ref_values;
46 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
47 igs_ge_msg_stack.add;
48 app_exception.raise_exception;
49 RETURN;
50 END IF;
51 CLOSE cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.country_code := x_country_code;
55 new_references.description := x_description;
56 new_references.type := x_type;
57 new_references.imported := x_imported;
58
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70
71 END set_column_values;
72
73
74 FUNCTION get_pk_for_validation (
75 x_country_code IN VARCHAR2
76 ) RETURN BOOLEAN AS
77 /*
78 || Created By : [email protected]
79 || Created On : 13-JUL-2006
80 || Purpose : Validates the Primary Key of the table.
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 */
86 CURSOR cur_rowid IS
87 SELECT rowid
88 FROM igs_uc_ref_country
89 WHERE country_code = x_country_code
90 FOR UPDATE NOWAIT;
91
92 lv_rowid cur_rowid%RowType;
93
94 BEGIN
95
96 OPEN cur_rowid;
97 FETCH cur_rowid INTO lv_rowid;
98 IF (cur_rowid%FOUND) THEN
99 CLOSE cur_rowid;
100 RETURN(TRUE);
101 ELSE
102 CLOSE cur_rowid;
103 RETURN(FALSE);
104 END IF;
105
106 END get_pk_for_validation;
107
108
109 PROCEDURE before_dml (
110 p_action IN VARCHAR2,
111 x_rowid IN VARCHAR2,
112 x_country_code IN VARCHAR2,
113 x_description IN VARCHAR2,
114 x_type IN VARCHAR2,
115 x_imported IN VARCHAR2,
116 x_creation_date IN DATE,
117 x_created_by IN NUMBER,
118 x_last_update_date IN DATE,
119 x_last_updated_by IN NUMBER,
120 x_last_update_login IN NUMBER
121 ) AS
122 /*
123 || Created By : [email protected]
124 || Created On : 13-JUL-2006
125 || Purpose : Initialises the columns, Checks Constraints, Calls the
126 || Trigger Handlers for the table, before any DML operation.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 BEGIN
133
134 set_column_values (
135 p_action,
136 x_rowid,
137 x_country_code,
138 x_description,
139 x_type,
140 x_imported,
141 x_creation_date,
142 x_created_by,
143 x_last_update_date,
144 x_last_updated_by,
145 x_last_update_login
146 );
147
148 IF (p_action = 'INSERT') THEN
149 -- Call all the procedures related to Before Insert.
150 IF ( get_pk_for_validation(
151 new_references.country_code
152 )
153 ) THEN
154 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 END IF;
158 ELSIF (p_action = 'VALIDATE_INSERT') THEN
159 -- Call all the procedures related to Before Insert.
160 IF ( get_pk_for_validation (
161 new_references.country_code
162 )
163 ) THEN
164 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
165 igs_ge_msg_stack.add;
166 app_exception.raise_exception;
167 END IF;
168 END IF;
169
170 END before_dml;
171
172
173 PROCEDURE insert_row (
174 x_rowid IN OUT NOCOPY VARCHAR2,
175 x_country_code IN VARCHAR2,
176 x_description IN VARCHAR2,
177 x_type IN VARCHAR2,
178 x_imported IN VARCHAR2,
179 x_mode IN VARCHAR2
180 ) AS
181 /*
182 || Created By : [email protected]
183 || Created On : 13-JUL-2006
184 || Purpose : Handles the INSERT DML logic for the table.
185 || Known limitations, enhancements or remarks :
186 || Change History :
187 || Who When What
188 || (reverse chronological order - newest change first)
189 */
190
191 x_last_update_date DATE;
192 x_last_updated_by NUMBER;
193 x_last_update_login NUMBER;
194
195 BEGIN
196
197 x_last_update_date := SYSDATE;
198 IF (x_mode = 'I') THEN
199 x_last_updated_by := 1;
200 x_last_update_login := 0;
201 ELSIF (x_mode = 'R') THEN
202 x_last_updated_by := fnd_global.user_id;
203 IF (x_last_updated_by IS NULL) THEN
204 x_last_updated_by := -1;
205 END IF;
206 x_last_update_login := fnd_global.login_id;
207 IF (x_last_update_login IS NULL) THEN
208 x_last_update_login := -1;
209 END IF;
210 ELSE
211 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
212 fnd_message.set_token ('ROUTINE', 'IGS_UC_REF_COUNTRY_PKG.INSERT_ROW');
213 igs_ge_msg_stack.add;
214 app_exception.raise_exception;
215 END IF;
216
217 before_dml(
218 p_action => 'INSERT',
219 x_rowid => x_rowid,
220 x_country_code => x_country_code,
221 x_description => x_description,
222 x_type => x_type,
223 x_imported => x_imported,
224 x_creation_date => x_last_update_date,
225 x_created_by => x_last_updated_by,
226 x_last_update_date => x_last_update_date,
227 x_last_updated_by => x_last_updated_by,
228 x_last_update_login => x_last_update_login
229 );
230
231 INSERT INTO igs_uc_ref_country (
232 country_code,
233 description,
234 type,
235 imported,
236 creation_date,
237 created_by,
238 last_update_date,
239 last_updated_by,
240 last_update_login
241 ) VALUES (
242 new_references.country_code,
243 new_references.description,
244 new_references.type,
245 new_references.imported,
246 x_last_update_date,
247 x_last_updated_by,
248 x_last_update_date,
249 x_last_updated_by,
250 x_last_update_login
251 ) RETURNING ROWID INTO x_rowid;
252
253 END insert_row;
254
255
256 PROCEDURE lock_row (
257 x_rowid IN VARCHAR2,
258 x_country_code IN VARCHAR2,
259 x_description IN VARCHAR2,
260 x_type IN VARCHAR2,
261 x_imported IN VARCHAR2
262 ) AS
263 /*
264 || Created By : [email protected]
265 || Created On : 13-JUL-2006
266 || Purpose : Handles the LOCK mechanism for the table.
267 || Known limitations, enhancements or remarks :
268 || Change History :
269 || Who When What
270 || (reverse chronological order - newest change first)
271 */
272 CURSOR c1 IS
273 SELECT
274 description,
275 type,
276 imported
277 FROM igs_uc_ref_country
278 WHERE rowid = x_rowid
279 FOR UPDATE NOWAIT;
280
281 tlinfo c1%ROWTYPE;
282
283 BEGIN
284
285 OPEN c1;
286 FETCH c1 INTO tlinfo;
287 IF (c1%notfound) THEN
288 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
289 igs_ge_msg_stack.add;
290 CLOSE c1;
291 app_exception.raise_exception;
292 RETURN;
293 END IF;
294 CLOSE c1;
295
296 IF (
297 ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
298 AND ((tlinfo.type = x_type) OR ((tlinfo.type IS NULL) AND (X_type IS NULL)))
299 AND (tlinfo.imported = x_imported)
300 ) THEN
301 NULL;
302 ELSE
303 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
304 igs_ge_msg_stack.add;
305 app_exception.raise_exception;
306 END IF;
307
308 RETURN;
309
310 END lock_row;
311
312
313 PROCEDURE update_row (
314 x_rowid IN VARCHAR2,
315 x_country_code IN VARCHAR2,
316 x_description IN VARCHAR2,
317 x_type IN VARCHAR2,
318 x_imported IN VARCHAR2,
319 x_mode IN VARCHAR2
320 ) AS
321 /*
322 || Created By : [email protected]
323 || Created On : 13-JUL-2006
324 || Purpose : Handles the UPDATE DML logic for the table.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 */
330 x_last_update_date DATE ;
331 x_last_updated_by NUMBER;
332 x_last_update_login NUMBER;
333
334 BEGIN
335
336 x_last_update_date := SYSDATE;
337 IF (X_MODE = 'I') THEN
338 x_last_updated_by := 1;
339 x_last_update_login := 0;
340 ELSIF (x_mode = 'R') THEN
341 x_last_updated_by := fnd_global.user_id;
342 IF x_last_updated_by IS NULL THEN
343 x_last_updated_by := -1;
344 END IF;
345 x_last_update_login := fnd_global.login_id;
346 IF (x_last_update_login IS NULL) THEN
347 x_last_update_login := -1;
348 END IF;
349 ELSE
350 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
351 fnd_message.set_token ('ROUTINE', 'IGS_UC_REF_COUNTRY_PKG.UPDATE_ROW');
352 igs_ge_msg_stack.add;
353 app_exception.raise_exception;
354 END IF;
355
356 before_dml(
357 p_action => 'UPDATE',
358 x_rowid => x_rowid,
359 x_country_code => x_country_code,
360 x_description => x_description,
361 x_type => x_type,
362 x_imported => x_imported,
363 x_creation_date => x_last_update_date,
364 x_created_by => x_last_updated_by,
365 x_last_update_date => x_last_update_date,
366 x_last_updated_by => x_last_updated_by,
367 x_last_update_login => x_last_update_login
368 );
369
370 UPDATE igs_uc_ref_country
371 SET
372 description = new_references.description,
373 type = new_references.type,
374 imported = new_references.imported,
375 last_update_date = x_last_update_date,
376 last_updated_by = x_last_updated_by,
377 last_update_login = x_last_update_login
378 WHERE rowid = x_rowid;
379
380 IF (SQL%NOTFOUND) THEN
381 RAISE NO_DATA_FOUND;
382 END IF;
383
384 END update_row;
385
386
387 PROCEDURE add_row (
388 x_rowid IN OUT NOCOPY VARCHAR2,
389 x_country_code IN VARCHAR2,
390 x_description IN VARCHAR2,
391 x_type IN VARCHAR2,
392 x_imported IN VARCHAR2,
393 x_mode IN VARCHAR2
394 ) AS
395 /*
396 || Created By : [email protected]
397 || Created On : 13-JUL-2006
398 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
399 || Known limitations, enhancements or remarks :
400 || Change History :
401 || Who When What
402 || (reverse chronological order - newest change first)
403 */
404 CURSOR c1 IS
405 SELECT rowid
406 FROM igs_uc_ref_country
407 WHERE country_code = x_country_code;
408
409 BEGIN
410
411 OPEN c1;
412 FETCH c1 INTO x_rowid;
413 IF (c1%NOTFOUND) THEN
414 CLOSE c1;
415
416 insert_row (
417 x_rowid,
418 x_country_code,
419 x_description,
420 x_type,
421 x_imported,
422 x_mode
423 );
424 RETURN;
425 END IF;
426 CLOSE c1;
427
428 update_row (
429 x_rowid,
430 x_country_code,
431 x_description,
432 x_type,
433 x_imported,
434 x_mode
435 );
436
437 END add_row;
438
439
440 PROCEDURE delete_row (
441 x_rowid IN VARCHAR2
442 ) AS
443 /*
444 || Created By : [email protected]
445 || Created On : 13-JUL-2006
446 || Purpose : Handles the DELETE DML logic for the table.
447 || Known limitations, enhancements or remarks :
448 || Change History :
449 || Who When What
450 || (reverse chronological order - newest change first)
451 */
452 BEGIN
453
454 before_dml (
455 p_action => 'DELETE',
456 x_rowid => x_rowid
457 );
458
459 DELETE FROM igs_uc_ref_country
460 WHERE rowid = x_rowid;
461
462 IF (SQL%NOTFOUND) THEN
463 RAISE NO_DATA_FOUND;
464 END IF;
465
466 END delete_row;
467
468
469 END igs_uc_ref_country_pkg;