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