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