[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_BATCH_PKG
Source
1 PACKAGE BODY igf_sl_dl_batch_pkg AS
2 /* $Header: IGFLI13B.pls 115.7 2002/11/28 14:24:26 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sl_dl_batch_all%ROWTYPE;
6 new_references igf_sl_dl_batch_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_dbth_id IN NUMBER DEFAULT NULL,
12 x_batch_id IN VARCHAR2 DEFAULT NULL,
13 x_message_class IN VARCHAR2 DEFAULT NULL,
14 x_bth_creation_date IN DATE DEFAULT NULL,
15 x_batch_rej_code IN VARCHAR2 DEFAULT NULL,
16 x_end_date IN DATE DEFAULT NULL,
17 x_batch_type IN VARCHAR2 DEFAULT NULL,
18 x_send_resp IN VARCHAR2 DEFAULT NULL,
19 x_status IN VARCHAR2 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 : venagara
28 || Created On : 02-NOV-2000
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_SL_DL_BATCH_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.dbth_id := x_dbth_id;
60 new_references.batch_id := x_batch_id;
61 new_references.message_class := x_message_class;
62 new_references.bth_creation_date := x_bth_creation_date;
63 new_references.batch_rej_code := x_batch_rej_code;
64 new_references.end_date := x_end_date;
65 new_references.batch_type := x_batch_type;
66 new_references.send_resp := x_send_resp;
67 new_references.status := x_status;
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 PROCEDURE check_child_existance IS
85 /*
86 || Created By : venagara
87 || Created On : 02-NOV-2000
88 || Purpose : Checks for the existance of Child records.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 igf_sl_dl_chg_resp_pkg.get_fk_igf_sl_dl_batch (
97 old_references.dbth_id
98 );
99
100 igf_sl_dl_chg_send_pkg.get_fk_igf_sl_dl_batch (
101 old_references.dbth_id
102 );
103
104 igf_sl_dl_lor_crresp_pkg.get_fk_igf_sl_dl_batch (
105 old_references.dbth_id
106 );
107
108 igf_sl_dl_lor_resp_pkg.get_fk_igf_sl_dl_batch (
109 old_references.dbth_id
110 );
111
112 igf_sl_dl_pnote_resp_pkg.get_fk_igf_sl_dl_batch (
113 old_references.dbth_id
114 );
115
116
117 END check_child_existance;
118
119
120 FUNCTION get_pk_for_validation (
121 x_dbth_id IN NUMBER
122 ) RETURN BOOLEAN AS
123 /*
124 || Created By : venagara
125 || Created On : 02-NOV-2000
126 || Purpose : Validates the Primary Key of the table.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 CURSOR cur_rowid IS
133 SELECT rowid
134 FROM igf_sl_dl_batch_all
135 WHERE dbth_id = x_dbth_id
136 FOR UPDATE NOWAIT;
137
138 lv_rowid cur_rowid%RowType;
139
140 BEGIN
141
142 OPEN cur_rowid;
143 FETCH cur_rowid INTO lv_rowid;
144 IF (cur_rowid%FOUND) THEN
145 CLOSE cur_rowid;
146 RETURN(TRUE);
147 ELSE
148 CLOSE cur_rowid;
149 RETURN(FALSE);
150 END IF;
151
152 END get_pk_for_validation;
153
154
155 PROCEDURE before_dml (
156 p_action IN VARCHAR2,
157 x_rowid IN VARCHAR2 DEFAULT NULL,
158 x_dbth_id IN NUMBER DEFAULT NULL,
159 x_batch_id IN VARCHAR2 DEFAULT NULL,
160 x_message_class IN VARCHAR2 DEFAULT NULL,
161 x_bth_creation_date IN DATE DEFAULT NULL,
162 x_batch_rej_code IN VARCHAR2 DEFAULT NULL,
163 x_end_date IN DATE DEFAULT NULL,
164 x_batch_type IN VARCHAR2 DEFAULT NULL,
165 x_send_resp IN VARCHAR2 DEFAULT NULL,
166 x_status IN VARCHAR2 DEFAULT NULL,
167 x_creation_date IN DATE DEFAULT NULL,
168 x_created_by IN NUMBER DEFAULT NULL,
169 x_last_update_date IN DATE DEFAULT NULL,
170 x_last_updated_by IN NUMBER DEFAULT NULL,
171 x_last_update_login IN NUMBER DEFAULT NULL
172 ) AS
173 /*
174 || Created By : venagara
175 || Created On : 02-NOV-2000
176 || Purpose : Initialises the columns, Checks Constraints, Calls the
177 || Trigger Handlers for the table, before any DML operation.
178 || Known limitations, enhancements or remarks :
179 || Change History :
180 || Who When What
181 || (reverse chronological order - newest change first)
182 */
183 BEGIN
184
185 set_column_values (
186 p_action,
187 x_rowid,
188 x_dbth_id,
189 x_batch_id,
190 x_message_class,
191 x_bth_creation_date,
192 x_batch_rej_code,
193 x_end_date,
194 x_batch_type,
195 x_send_resp,
196 x_status,
197 x_creation_date,
198 x_created_by,
199 x_last_update_date,
200 x_last_updated_by,
201 x_last_update_login
202 );
203
204 IF (p_action = 'INSERT') THEN
205 -- Call all the procedures related to Before Insert.
206 IF ( get_pk_for_validation(
207 new_references.dbth_id
208 )
209 ) THEN
210 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
211 igs_ge_msg_stack.add;
212 app_exception.raise_exception;
213 END IF;
214 ELSIF (p_action = 'DELETE') THEN
215 -- Call all the procedures related to Before Delete.
216 check_child_existance;
217 ELSIF (p_action = 'VALIDATE_INSERT') THEN
218 -- Call all the procedures related to Before Insert.
219 IF ( get_pk_for_validation (
220 new_references.dbth_id
221 )
222 ) THEN
223 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END IF;
227 ELSIF (p_action = 'VALIDATE_DELETE') THEN
228 check_child_existance;
229 END IF;
230
231 END before_dml;
232
233
234 PROCEDURE insert_row (
235 x_rowid IN OUT NOCOPY VARCHAR2,
236 x_dbth_id IN OUT NOCOPY NUMBER,
237 x_batch_id IN VARCHAR2,
238 x_message_class IN VARCHAR2,
239 x_bth_creation_date IN DATE,
240 x_batch_rej_code IN VARCHAR2,
241 x_end_date IN DATE,
242 x_batch_type IN VARCHAR2,
243 x_send_resp IN VARCHAR2,
244 x_status IN VARCHAR2,
245 x_mode IN VARCHAR2 DEFAULT 'R'
246 ) AS
247 /*
248 || Created By : venagara
249 || Created On : 02-NOV-2000
250 || Purpose : Handles the INSERT DML logic for the table.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 CURSOR c IS
257 SELECT rowid
258 FROM igf_sl_dl_batch_all
259 WHERE dbth_id = x_dbth_id;
260
261 x_last_update_date DATE;
262 x_last_updated_by NUMBER;
263 x_last_update_login NUMBER;
264 x_request_id NUMBER;
265 x_program_id NUMBER;
266 x_program_application_id NUMBER;
267 x_program_update_date DATE;
268 l_org_id igf_sl_dl_batch_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
269
270 BEGIN
271
272 x_last_update_date := SYSDATE;
273 IF (x_mode = 'I') THEN
274 x_last_updated_by := 1;
275 x_last_update_login := 0;
276 ELSIF (x_mode = 'R') THEN
277 x_last_updated_by := fnd_global.user_id;
278 IF (x_last_updated_by IS NULL) THEN
279 x_last_updated_by := -1;
280 END IF;
281 x_last_update_login := fnd_global.login_id;
282 IF (x_last_update_login IS NULL) THEN
283 x_last_update_login := -1;
284 END IF;
285 x_request_id := fnd_global.conc_request_id;
286 x_program_id := fnd_global.conc_program_id;
287 x_program_application_id := fnd_global.prog_appl_id;
288
289 IF (x_request_id = -1) THEN
290 x_request_id := NULL;
291 x_program_id := NULL;
292 x_program_application_id := NULL;
293 x_program_update_date := NULL;
294 ELSE
295 x_program_update_date := SYSDATE;
296 END IF;
297 ELSE
298 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
299 igs_ge_msg_stack.add;
300 app_exception.raise_exception;
301 END IF;
302
303 SELECT igf_sl_dl_batch_s.NEXTVAL
304 INTO x_dbth_id
305 FROM dual;
306
307 before_dml(
308 p_action => 'INSERT',
309 x_rowid => x_rowid,
310 x_dbth_id => x_dbth_id,
311 x_batch_id => x_batch_id,
312 x_message_class => x_message_class,
313 x_bth_creation_date => x_bth_creation_date,
314 x_batch_rej_code => x_batch_rej_code,
315 x_end_date => x_end_date,
316 x_batch_type => x_batch_type,
317 x_send_resp => x_send_resp,
318 x_status => x_status,
319 x_creation_date => x_last_update_date,
320 x_created_by => x_last_updated_by,
321 x_last_update_date => x_last_update_date,
322 x_last_updated_by => x_last_updated_by,
323 x_last_update_login => x_last_update_login
324 );
325
326 INSERT INTO igf_sl_dl_batch_all (
327 dbth_id,
328 batch_id,
329 message_class,
330 bth_creation_date,
331 batch_rej_code,
332 end_date,
333 batch_type,
334 send_resp,
335 status,
336 creation_date,
337 created_by,
338 last_update_date,
339 last_updated_by,
340 last_update_login,
341 request_id,
342 program_id,
343 program_application_id,
344 program_update_date,
345 org_id
346 ) VALUES (
347 new_references.dbth_id,
348 new_references.batch_id,
349 new_references.message_class,
350 new_references.bth_creation_date,
351 new_references.batch_rej_code,
352 new_references.end_date,
353 new_references.batch_type,
354 new_references.send_resp,
355 new_references.status,
356 x_last_update_date,
357 x_last_updated_by,
358 x_last_update_date,
359 x_last_updated_by,
360 x_last_update_login ,
361 x_request_id,
362 x_program_id,
363 x_program_application_id,
364 x_program_update_date ,
365 l_org_id
366 );
367
368 OPEN c;
372 RAISE NO_DATA_FOUND;
369 FETCH c INTO x_rowid;
370 IF (c%NOTFOUND) THEN
371 CLOSE c;
373 END IF;
374 CLOSE c;
375
376 END insert_row;
377
378
379 PROCEDURE lock_row (
380 x_rowid IN VARCHAR2,
381 x_dbth_id IN NUMBER,
382 x_batch_id IN VARCHAR2,
383 x_message_class IN VARCHAR2,
384 x_bth_creation_date IN DATE,
385 x_batch_rej_code IN VARCHAR2,
386 x_end_date IN DATE,
387 x_batch_type IN VARCHAR2,
388 x_send_resp IN VARCHAR2,
389 x_status IN VARCHAR2
390 ) AS
391 /*
392 || Created By : venagara
393 || Created On : 02-NOV-2000
394 || Purpose : Handles the LOCK mechanism for the table.
395 || Known limitations, enhancements or remarks :
396 || Change History :
397 || Who When What
398 || (reverse chronological order - newest change first)
399 */
400 CURSOR c1 IS
401 SELECT
402 batch_id,
403 message_class,
404 bth_creation_date,
405 batch_rej_code,
406 end_date,
407 batch_type,
408 send_resp,
409 status,
410 org_id
411 FROM igf_sl_dl_batch_all
412 WHERE rowid = x_rowid
413 FOR UPDATE NOWAIT;
414
415 tlinfo c1%ROWTYPE;
416
417 BEGIN
418
419 OPEN c1;
420 FETCH c1 INTO tlinfo;
421 IF (c1%notfound) THEN
422 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
423 igs_ge_msg_stack.add;
424 CLOSE c1;
425 app_exception.raise_exception;
426 RETURN;
427 END IF;
428 CLOSE c1;
429
430 IF (
431 (tlinfo.batch_id = x_batch_id)
432 AND (tlinfo.message_class = x_message_class)
433 AND (tlinfo.bth_creation_date = x_bth_creation_date)
434 AND ((tlinfo.batch_rej_code = x_batch_rej_code) OR ((tlinfo.batch_rej_code IS NULL) AND (X_batch_rej_code IS NULL)))
435 AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
436 AND ((tlinfo.batch_type = x_batch_type) OR ((tlinfo.batch_type IS NULL) AND (X_batch_type IS NULL)))
437 AND ((tlinfo.send_resp = x_send_resp) OR ((tlinfo.send_resp IS NULL) AND (X_send_resp IS NULL)))
438 AND (tlinfo.status = x_status)
439 ) THEN
440 NULL;
441 ELSE
442 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
443 igs_ge_msg_stack.add;
444 app_exception.raise_exception;
445 END IF;
446
447 RETURN;
448
449 END lock_row;
450
451
452 PROCEDURE update_row (
453 x_rowid IN VARCHAR2,
454 x_dbth_id IN NUMBER,
455 x_batch_id IN VARCHAR2,
456 x_message_class IN VARCHAR2,
457 x_bth_creation_date IN DATE,
458 x_batch_rej_code IN VARCHAR2,
459 x_end_date IN DATE,
460 x_batch_type IN VARCHAR2,
461 x_send_resp IN VARCHAR2,
462 x_status IN VARCHAR2,
463 x_mode IN VARCHAR2 DEFAULT 'R'
464 ) AS
465 /*
466 || Created By : venagara
467 || Created On : 02-NOV-2000
468 || Purpose : Handles the UPDATE DML logic for the table.
469 || Known limitations, enhancements or remarks :
470 || Change History :
471 || Who When What
472 || (reverse chronological order - newest change first)
473 */
474 x_last_update_date DATE ;
475 x_last_updated_by NUMBER;
476 x_last_update_login NUMBER;
477 x_request_id NUMBER;
478 x_program_id NUMBER;
479 x_program_application_id NUMBER;
480 x_program_update_date DATE;
481
482 BEGIN
483
484 x_last_update_date := SYSDATE;
485 IF (X_MODE = 'I') THEN
486 x_last_updated_by := 1;
487 x_last_update_login := 0;
488 ELSIF (x_mode = 'R') THEN
489 x_last_updated_by := fnd_global.user_id;
490 IF x_last_updated_by IS NULL THEN
491 x_last_updated_by := -1;
492 END IF;
493 x_last_update_login := fnd_global.login_id;
494 IF (x_last_update_login IS NULL) THEN
495 x_last_update_login := -1;
496 END IF;
497 ELSE
498 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
499 igs_ge_msg_stack.add;
500 app_exception.raise_exception;
501 END IF;
502
503 before_dml(
504 p_action => 'UPDATE',
505 x_rowid => x_rowid,
506 x_dbth_id => x_dbth_id,
507 x_batch_id => x_batch_id,
511 x_end_date => x_end_date,
508 x_message_class => x_message_class,
509 x_bth_creation_date => x_bth_creation_date,
510 x_batch_rej_code => x_batch_rej_code,
512 x_batch_type => x_batch_type,
513 x_send_resp => x_send_resp,
514 x_status => x_status,
515 x_creation_date => x_last_update_date,
516 x_created_by => x_last_updated_by,
517 x_last_update_date => x_last_update_date,
518 x_last_updated_by => x_last_updated_by,
519 x_last_update_login => x_last_update_login
520 );
521
522 IF (x_mode = 'R') THEN
523 x_request_id := fnd_global.conc_request_id;
524 x_program_id := fnd_global.conc_program_id;
525 x_program_application_id := fnd_global.prog_appl_id;
526 IF (x_request_id = -1) THEN
527 x_request_id := old_references.request_id;
528 x_program_id := old_references.program_id;
529 x_program_application_id := old_references.program_application_id;
530 x_program_update_date := old_references.program_update_date;
531 ELSE
532 x_program_update_date := SYSDATE;
533 END IF;
534 END IF;
535
536 UPDATE igf_sl_dl_batch_all
537 SET
538 batch_id = new_references.batch_id,
539 message_class = new_references.message_class,
540 bth_creation_date = new_references.bth_creation_date,
541 batch_rej_code = new_references.batch_rej_code,
542 end_date = new_references.end_date,
543 batch_type = new_references.batch_type,
544 send_resp = new_references.send_resp,
545 status = new_references.status,
546 last_update_date = x_last_update_date,
547 last_updated_by = x_last_updated_by,
548 last_update_login = x_last_update_login ,
549 request_id = x_request_id,
550 program_id = x_program_id,
551 program_application_id = x_program_application_id,
552 program_update_date = x_program_update_date
553 WHERE rowid = x_rowid;
554
555 IF (SQL%NOTFOUND) THEN
556 RAISE NO_DATA_FOUND;
557 END IF;
558
559 END update_row;
560
561
562 PROCEDURE add_row (
563 x_rowid IN OUT NOCOPY VARCHAR2,
564 x_dbth_id IN OUT NOCOPY NUMBER,
565 x_batch_id IN VARCHAR2,
566 x_message_class IN VARCHAR2,
567 x_bth_creation_date IN DATE,
568 x_batch_rej_code IN VARCHAR2,
569 x_end_date IN DATE,
570 x_batch_type IN VARCHAR2,
571 x_send_resp IN VARCHAR2,
572 x_status IN VARCHAR2,
573 x_mode IN VARCHAR2 DEFAULT 'R'
574 ) AS
575 /*
576 || Created By : venagara
577 || Created On : 02-NOV-2000
578 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
579 || Known limitations, enhancements or remarks :
580 || Change History :
584 CURSOR c1 IS
581 || Who When What
582 || (reverse chronological order - newest change first)
583 */
585 SELECT rowid
586 FROM igf_sl_dl_batch_all
587 WHERE dbth_id = x_dbth_id;
588
589 BEGIN
590
591 OPEN c1;
592 FETCH c1 INTO x_rowid;
593 IF (c1%NOTFOUND) THEN
594 CLOSE c1;
595
596 insert_row (
597 x_rowid,
598 x_dbth_id,
599 x_batch_id,
600 x_message_class,
601 x_bth_creation_date,
602 x_batch_rej_code,
603 x_end_date,
604 x_batch_type,
605 x_send_resp,
606 x_status,
607 x_mode
608 );
609 RETURN;
610 END IF;
611 CLOSE c1;
612
613 update_row (
614 x_rowid,
615 x_dbth_id,
616 x_batch_id,
617 x_message_class,
618 x_bth_creation_date,
619 x_batch_rej_code,
620 x_end_date,
621 x_batch_type,
622 x_send_resp,
623 x_status,
624 x_mode
625 );
626
627 END add_row;
628
629
630 PROCEDURE delete_row (
631 x_rowid IN VARCHAR2
632 ) AS
633 /*
634 || Created By : venagara
635 || Created On : 02-NOV-2000
636 || Purpose : Handles the DELETE DML logic for the table.
637 || Known limitations, enhancements or remarks :
638 || Change History :
639 || Who When What
640 || (reverse chronological order - newest change first)
641 */
642 BEGIN
643
644 before_dml (
645 p_action => 'DELETE',
646 x_rowid => x_rowid
647 );
648
649 DELETE FROM igf_sl_dl_batch_all
650 WHERE rowid = x_rowid;
651
652 IF (SQL%NOTFOUND) THEN
653 RAISE NO_DATA_FOUND;
654 END IF;
655
656 END delete_row;
657
658
659 END igf_sl_dl_batch_pkg;