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