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