[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_CR_ACTIVITIES_PKG
Source
1 PACKAGE BODY igs_fi_cr_activities_pkg AS
2 /* $Header: IGSSI87B.pls 115.15 2003/02/17 09:01:48 pathipat ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_cr_activities%ROWTYPE;
6 new_references igs_fi_cr_activities%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_credit_activity_id IN NUMBER,
12 x_credit_id IN NUMBER,
13 x_status IN VARCHAR2,
14 x_transaction_date IN DATE,
15 x_amount IN NUMBER,
16 x_dr_account_cd IN VARCHAR2,
17 x_cr_account_cd IN VARCHAR2,
18 x_dr_gl_ccid IN NUMBER,
19 x_cr_gl_ccid IN NUMBER,
20 x_bill_id IN NUMBER,
21 x_bill_number IN VARCHAR2,
22 x_bill_date IN DATE,
23 x_posting_id IN NUMBER,
24 x_creation_date IN DATE,
25 x_created_by IN NUMBER,
26 x_last_update_date IN DATE,
27 x_last_updated_by IN NUMBER,
28 x_last_update_login IN NUMBER,
29 x_gl_date IN DATE,
30 x_gl_posted_date IN DATE,
31 x_posting_control_id IN NUMBER
32 ) AS
33 /*
34 || Created By : BDEVARAK
35 || Created On : 26-APR-2001
36 || Purpose : Initialises the Old and New references for the columns of the table.
37 || Known limitations, enhancements or remarks :
38 || Change History :
39 || Who When What
40 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
41 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
42 || (reverse chronological order - newest change first)
43 */
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM IGS_FI_CR_ACTIVITIES
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 OPEN cur_old_ref_values;
57 FETCH cur_old_ref_values INTO old_references;
58 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
59 CLOSE cur_old_ref_values;
60 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
61 igs_ge_msg_stack.add;
62 app_exception.raise_exception;
63 RETURN;
64 END IF;
65 CLOSE cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.credit_activity_id := x_credit_activity_id;
69 new_references.credit_id := x_credit_id;
70 new_references.status := x_status;
71 new_references.transaction_date := x_transaction_date;
72 new_references.amount := x_amount;
73 new_references.dr_account_cd := x_dr_account_cd;
74 new_references.cr_account_cd := x_cr_account_cd;
75 new_references.dr_gl_ccid := x_dr_gl_ccid;
76 new_references.cr_gl_ccid := x_cr_gl_ccid;
77 new_references.bill_id := x_bill_id;
78 new_references.bill_number := x_bill_number;
79 new_references.bill_date := x_bill_date;
80 new_references.posting_id := x_posting_id;
81 new_references.gl_date := TRUNC(x_gl_date);
82 new_references.gl_posted_date := x_gl_posted_date;
83 new_references.posting_control_id := x_posting_control_id;
84
85
86 IF (p_action = 'UPDATE') THEN
87 new_references.creation_date := old_references.creation_date;
88 new_references.created_by := old_references.created_by;
89 ELSE
90 new_references.creation_date := x_creation_date;
91 new_references.created_by := x_created_by;
92 END IF;
93
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END set_column_values;
99
100
101 PROCEDURE check_parent_existance AS
102 /*
103 || Created By : BDEVARAK
104 || Created On : 26-APR-2001
105 || Purpose : Checks for the existance of Parent records.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113 IF ((old_references.cr_account_cd = new_references.cr_account_cd) OR
114 (new_references.cr_account_cd IS NULL)) THEN
115 NULL;
116 ELSE
117 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
118 new_references.cr_account_cd
119 ) THEN
120 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
121 IGS_GE_MSG_STACK.ADD;
122 App_Exception.Raise_Exception;
123 END IF;
124 END IF;
125 IF ((old_references.dr_account_cd = new_references.dr_account_cd) OR
126 (new_references.dr_account_cd IS NULL)) THEN
127 NULL;
128 ELSE
129 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
130 new_references.dr_account_cd
131 ) THEN
132 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception;
135 END IF;
136 END IF;
137
138 IF (((old_references.status = new_references.status)) OR
139 ((new_references.status IS NULL))) THEN
140 NULL;
141 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
142 'IGS_FI_CREDIT_STATUS',
143 new_references.status
144 )THEN
145 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149
150 IF (((old_references.bill_id = new_references.bill_id)) OR
151 ((new_references.bill_id IS NULL))) THEN
152 NULL;
153 ELSIF NOT igs_fi_bill_pkg.get_pk_for_validation (
154 new_references.bill_id
155 ) THEN
156 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
157 igs_ge_msg_stack.add;
158 app_exception.raise_exception;
159 END IF;
160
161 IF (((old_references.credit_id = new_references.credit_id)) OR
162 ((new_references.credit_id IS NULL))) THEN
163 NULL;
164 ELSIF NOT igs_fi_credits_pkg.get_pk_for_validation (
165 new_references.credit_id
166 ) THEN
167 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
168 igs_ge_msg_stack.add;
169 app_exception.raise_exception;
170 END IF;
171
172 IF (((old_references.posting_id = new_references.posting_id)) OR
173 ((new_references.posting_id IS NULL))) THEN
174 NULL;
175 ELSIF NOT igs_fi_posting_int_pkg.get_pk_for_validation (
176 new_references.posting_id
177 ) THEN
178 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
179 igs_ge_msg_stack.add;
180 app_exception.raise_exception;
181 END IF;
182
183 END check_parent_existance;
184
185
186 PROCEDURE check_child_existance IS
187 /*
188 || Created By : BDEVARAK
189 || Created On : 26-APR-2001
190 || Purpose : Checks for the existance of Child records.
191 || Known limitations, enhancements or remarks :
192 || Change History :
193 || Who When What
194 || (reverse chronological order - newest change first)
195 || shtatiko 04-Dec-2002 Added call to igs_fi_bill_dpsts_pkg, Bug# 2584741
196 */
197 BEGIN
198
199 igs_fi_applications_pkg.get_fk_igs_fi_cr_activities (
200 old_references.credit_activity_id
201 );
202
203 igs_fi_bill_trnsctns_pkg.get_fk_igs_fi_cr_activities (
204 old_references.credit_activity_id
205 );
206
207 igs_fi_bill_dpsts_pkg.get_fk_igs_fi_cr_activities (
208 old_references.credit_activity_id
209 );
210
211 END check_child_existance;
212
213
214 FUNCTION get_pk_for_validation (
215 x_credit_activity_id IN NUMBER
216 ) RETURN BOOLEAN AS
217 /*
218 || Created By : BDEVARAK
219 || Created On : 26-APR-2001
220 || Purpose : Validates the Primary Key of the table.
221 || Known limitations, enhancements or remarks :
222 || Change History :
223 || Who When What
224 || (reverse chronological order - newest change first)
225 */
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM igs_fi_cr_activities
229 WHERE credit_activity_id = x_credit_activity_id
230 FOR UPDATE NOWAIT;
231
232 lv_rowid cur_rowid%RowType;
233
234 BEGIN
235
236 OPEN cur_rowid;
237 FETCH cur_rowid INTO lv_rowid;
238 IF (cur_rowid%FOUND) THEN
239 CLOSE cur_rowid;
240 RETURN(TRUE);
241 ELSE
242 CLOSE cur_rowid;
243 RETURN(FALSE);
244 END IF;
245
246 END get_pk_for_validation;
247
248
249 PROCEDURE get_fk_igs_fi_bill (
250 x_bill_id IN NUMBER
251 ) AS
252 /*
253 || Created By : [email protected]
254 || Created On : 23-JUL-2001
255 || Purpose : Validates the Foreign Keys for the table.
256 || Known limitations, enhancements or remarks :
257 || Change History :
258 || Who When What
259 || (reverse chronological order - newest change first)
260 */
261 CURSOR cur_rowid IS
262 SELECT rowid
263 FROM igs_fi_bill_trnsctns
264 WHERE ((bill_id = x_bill_id));
265
266 lv_rowid cur_rowid%RowType;
267
268 BEGIN
269
270 OPEN cur_rowid;
271 FETCH cur_rowid INTO lv_rowid;
272 IF (cur_rowid%FOUND) THEN
273 CLOSE cur_rowid;
274 fnd_message.set_name ('IGS', 'IGS_FI_CRAC_FBLLA_FK');
275 igs_ge_msg_stack.add;
276 app_exception.raise_exception;
277 RETURN;
278 END IF;
279 CLOSE cur_rowid;
280
281 END get_fk_igs_fi_bill;
282
283 PROCEDURE get_fk_igs_fi_credits_all (
284 x_credit_id IN NUMBER
285 ) AS
286 /*
287 || Created By : BDEVARAK
288 || Created On : 26-APR-2001
289 || Purpose : Validates the Foreign Keys for the table.
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 || (reverse chronological order - newest change first)
294 */
295 CURSOR cur_rowid IS
296 SELECT rowid
297 FROM igs_fi_cr_activities
298 WHERE ((credit_id = x_credit_id));
299
300 lv_rowid cur_rowid%RowType;
301
302 BEGIN
303
304 OPEN cur_rowid;
305 FETCH cur_rowid INTO lv_rowid;
306 IF (cur_rowid%FOUND) THEN
307 CLOSE cur_rowid;
308 fnd_message.set_name ('IGS', 'IGS_FI_CRAC_CRTY_FK');
309 igs_ge_msg_stack.add;
310 app_exception.raise_exception;
311 RETURN;
312 END IF;
313 CLOSE cur_rowid;
314
315 END get_fk_igs_fi_credits_all;
316
317
318 PROCEDURE get_fk_igs_fi_posting_int_all (
319 x_posting_id IN NUMBER
320 ) AS
321 /*
322 || Created By : BDEVARAK
323 || Created On : 26-APR-2001
324 || Purpose : Validates the Foreign Keys for the table.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 */
330 CURSOR cur_rowid IS
331 SELECT rowid
332 FROM igs_fi_cr_activities
333 WHERE ((posting_id = x_posting_id));
334
335 lv_rowid cur_rowid%RowType;
336
337 BEGIN
338
339 OPEN cur_rowid;
340 FETCH cur_rowid INTO lv_rowid;
341 IF (cur_rowid%FOUND) THEN
342 CLOSE cur_rowid;
343 fnd_message.set_name ('IGS', 'IGS_FI_CRAC_PINT_FK');
344 igs_ge_msg_stack.add;
345 app_exception.raise_exception;
346 RETURN;
347 END IF;
348 CLOSE cur_rowid;
349
350 END get_fk_igs_fi_posting_int_all;
351
352
353 PROCEDURE before_dml (
354 p_action IN VARCHAR2,
355 x_rowid IN VARCHAR2,
356 x_credit_activity_id IN NUMBER,
357 x_credit_id IN NUMBER,
358 x_status IN VARCHAR2,
359 x_transaction_date IN DATE,
360 x_amount IN NUMBER,
361 x_dr_account_cd IN VARCHAR2,
362 x_cr_account_cd IN VARCHAR2,
363 x_dr_gl_ccid IN NUMBER,
364 x_cr_gl_ccid IN NUMBER,
365 x_bill_id IN NUMBER ,
366 x_bill_number IN VARCHAR2,
367 x_bill_date IN DATE,
368 x_posting_id IN NUMBER,
369 x_creation_date IN DATE ,
370 x_created_by IN NUMBER,
371 x_last_update_date IN DATE ,
372 x_last_updated_by IN NUMBER,
373 x_last_update_login IN NUMBER,
374 x_gl_date IN DATE,
375 x_gl_posted_date IN DATE,
376 x_posting_control_id IN NUMBER
377
378 ) AS
379 /*
380 || Created By : BDEVARAK
381 || Created On : 26-APR-2001
382 || Purpose : Initialises the columns, Checks Constraints, Calls the
383 || Trigger Handlers for the table, before any DML operation.
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
388 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
389 || (reverse chronological order - newest change first)
390 */
391 BEGIN
392
393 set_column_values (
394 p_action,
395 x_rowid,
396 x_credit_activity_id,
397 x_credit_id,
398 x_status,
399 x_transaction_date,
400 x_amount,
401 x_dr_account_cd,
402 x_cr_account_cd,
403 x_dr_gl_ccid,
404 x_cr_gl_ccid,
405 x_bill_id,
406 x_bill_number,
407 x_bill_date,
408 x_posting_id,
409 x_creation_date,
410 x_created_by,
411 x_last_update_date,
412 x_last_updated_by,
413 x_last_update_login,
414 x_gl_date,
415 x_gl_posted_date,
416 x_posting_control_id
417 );
418
419 IF (p_action = 'INSERT') THEN
420 -- Call all the procedures related to Before Insert.
421 IF ( get_pk_for_validation(
422 new_references.credit_activity_id
423 )
424 ) THEN
425 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
426 igs_ge_msg_stack.add;
427 app_exception.raise_exception;
428 END IF;
429 check_parent_existance;
430 ELSIF (p_action = 'UPDATE') THEN
431 -- Call all the procedures related to Before Update.
432 check_parent_existance;
433 ELSIF (p_action = 'DELETE') THEN
434 -- Call all the procedures related to Before Delete.
435 check_child_existance;
436 ELSIF (p_action = 'VALIDATE_INSERT') THEN
437 -- Call all the procedures related to Before Insert.
438 IF ( get_pk_for_validation (
439 new_references.credit_activity_id
440 )
441 ) THEN
442 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
443 igs_ge_msg_stack.add;
444 app_exception.raise_exception;
445 END IF;
446 ELSIF (p_action = 'VALIDATE_DELETE') THEN
447 check_child_existance;
448 END IF;
449
450 END before_dml;
451
452
453 PROCEDURE insert_row (
454 x_rowid IN OUT NOCOPY VARCHAR2,
455 x_credit_activity_id IN OUT NOCOPY NUMBER,
456 x_credit_id IN NUMBER,
457 x_status IN VARCHAR2,
458 x_transaction_date IN DATE,
459 x_amount IN NUMBER,
460 x_dr_account_cd IN VARCHAR2,
461 x_cr_account_cd IN VARCHAR2,
462 x_dr_gl_ccid IN NUMBER,
463 x_cr_gl_ccid IN NUMBER,
464 x_bill_id IN NUMBER,
465 x_bill_number IN VARCHAR2,
466 x_bill_date IN DATE,
467 x_posting_id IN NUMBER,
468 x_mode IN VARCHAR2,
469 x_gl_date IN DATE,
470 x_gl_posted_date IN DATE,
471 x_posting_control_id IN NUMBER
472 ) AS
473 /*
474 || Created By : BDEVARAK
475 || Created On : 26-APR-2001
476 || Purpose : Handles the INSERT DML logic for the table.
477 || Known limitations, enhancements or remarks :
478 || Change History :
479 || Who When What
480 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
481 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
482 || (reverse chronological order - newest change first)
483 */
484 CURSOR c IS
485 SELECT rowid
486 FROM igs_fi_cr_activities
487 WHERE credit_activity_id = x_credit_activity_id;
488
489 x_last_update_date DATE;
490 x_last_updated_by NUMBER;
491 x_last_update_login NUMBER;
492 x_request_id NUMBER;
493 x_program_id NUMBER;
494 x_program_application_id NUMBER;
495 x_program_update_date DATE;
496
497 BEGIN
498
499 x_last_update_date := SYSDATE;
500 IF (x_mode = 'I') THEN
501 x_last_updated_by := 1;
502 x_last_update_login := 0;
503 ELSIF (x_mode = 'R') THEN
504 x_last_updated_by := fnd_global.user_id;
505 IF (x_last_updated_by IS NULL) THEN
506 x_last_updated_by := -1;
507 END IF;
508 x_last_update_login := fnd_global.login_id;
509 IF (x_last_update_login IS NULL) THEN
510 x_last_update_login := -1;
511 END IF;
512 x_request_id := fnd_global.conc_request_id;
513 x_program_id := fnd_global.conc_program_id;
514 x_program_application_id := fnd_global.prog_appl_id;
515
516 IF (x_request_id = -1) THEN
517 x_request_id := NULL;
518 x_program_id := NULL;
519 x_program_application_id := NULL;
520 x_program_update_date := NULL;
521 ELSE
522 x_program_update_date := SYSDATE;
523 END IF;
524 ELSE
525 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
526 igs_ge_msg_stack.add;
527 app_exception.raise_exception;
528 END IF;
529
530 SELECT igs_fi_cr_activities_s.NEXTVAL
531 INTO x_credit_activity_id
532 FROM dual;
533
534 before_dml(
535 p_action => 'INSERT',
536 x_rowid => x_rowid,
537 x_credit_activity_id => x_credit_activity_id,
538 x_credit_id => x_credit_id,
539 x_status => x_status,
540 x_transaction_date => x_transaction_date,
541 x_amount => x_amount,
542 x_dr_account_cd => x_dr_account_cd,
543 x_cr_account_cd => x_cr_account_cd,
544 x_dr_gl_ccid => x_dr_gl_ccid,
545 x_cr_gl_ccid => x_cr_gl_ccid,
546 x_bill_id => x_bill_id,
547 x_bill_number => x_bill_number,
548 x_bill_date => x_bill_date,
549 x_posting_id => x_posting_id,
550 x_creation_date => x_last_update_date,
551 x_created_by => x_last_updated_by,
552 x_last_update_date => x_last_update_date,
553 x_last_updated_by => x_last_updated_by,
554 x_last_update_login => x_last_update_login ,
555 x_gl_date => x_gl_date,
556 x_gl_posted_date => x_gl_posted_date,
557 x_posting_control_id => x_posting_control_id
558 );
559
560 INSERT INTO igs_fi_cr_activities (
561 credit_activity_id,
562 credit_id,
563 status,
564 transaction_date,
565 amount,
566 dr_account_cd,
567 cr_account_cd,
568 dr_gl_ccid,
569 cr_gl_ccid,
570 bill_id,
571 bill_number,
572 bill_date,
573 posting_id,
574 creation_date,
575 created_by,
576 last_update_date,
577 last_updated_by,
578 last_update_login,
579 request_id,
580 program_id,
581 program_application_id,
582 program_update_date ,
583 gl_date,
584 gl_posted_date,
585 posting_control_id
586 ) VALUES (
587 new_references.credit_activity_id,
588 new_references.credit_id,
589 new_references.status,
590 new_references.transaction_date,
591 new_references.amount,
592 new_references.dr_account_cd,
593 new_references.cr_account_cd,
594 new_references.dr_gl_ccid,
595 new_references.cr_gl_ccid,
596 new_references.bill_id,
597 new_references.bill_number,
598 new_references.bill_date,
599 new_references.posting_id,
600 x_last_update_date,
601 x_last_updated_by,
602 x_last_update_date,
603 x_last_updated_by,
604 x_last_update_login ,
605 x_request_id,
606 x_program_id,
607 x_program_application_id,
608 x_program_update_date ,
609 new_references.gl_date,
610 new_references.gl_posted_date,
611 new_references.posting_control_id
612 );
613
614 OPEN c;
615 FETCH c INTO x_rowid;
616 IF (c%NOTFOUND) THEN
617 CLOSE c;
618 RAISE NO_DATA_FOUND;
619 END IF;
620 CLOSE c;
621
622 END insert_row;
623
624
625 PROCEDURE lock_row (
626 x_rowid IN VARCHAR2,
627 x_credit_activity_id IN NUMBER,
628 x_credit_id IN NUMBER,
629 x_status IN VARCHAR2,
630 x_transaction_date IN DATE,
631 x_amount IN NUMBER,
632 x_dr_account_cd IN VARCHAR2,
633 x_cr_account_cd IN VARCHAR2,
634 x_dr_gl_ccid IN NUMBER,
635 x_cr_gl_ccid IN NUMBER,
636 x_bill_id IN NUMBER,
637 x_bill_number IN VARCHAR2,
638 x_bill_date IN DATE,
639 x_posting_id IN NUMBER,
640 x_gl_date IN DATE,
641 x_gl_posted_date IN DATE ,
642 x_posting_control_id IN NUMBER
643 ) AS
644 /*
645 || Created By : BDEVARAK
646 || Created On : 26-APR-2001
647 || Purpose : Handles the LOCK mechanism for the table.
648 || Known limitations, enhancements or remarks :
649 || Change History :
650 || Who When What
651 || pathipat 30-Dec-2002 Bug: 2728036 - Added TRUNC while comparing dates
652 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
653 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
654 || (reverse chronological order - newest change first)
655 */
656 CURSOR c1 IS
657 SELECT
658 credit_id,
659 status,
660 transaction_date,
661 amount,
662 dr_account_cd,
663 cr_account_cd,
664 dr_gl_ccid,
665 cr_gl_ccid,
666 bill_id,
667 bill_number,
668 bill_date,
669 posting_id ,
670 gl_date,
671 gl_posted_date,
672 posting_control_id
673 FROM igs_fi_cr_activities
674 WHERE rowid = x_rowid
675 FOR UPDATE NOWAIT;
676
677 tlinfo c1%ROWTYPE;
678
679 BEGIN
680
681 OPEN c1;
682 FETCH c1 INTO tlinfo;
683 IF (c1%notfound) THEN
684 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
685 igs_ge_msg_stack.add;
686 CLOSE c1;
687 app_exception.raise_exception;
688 RETURN;
689 END IF;
690 CLOSE c1;
691
692 IF (
693 (tlinfo.credit_id = x_credit_id)
694 AND (tlinfo.status = x_status)
695 AND (TRUNC(tlinfo.transaction_date) = TRUNC(x_transaction_date))
696 AND (tlinfo.amount = x_amount)
697 AND ((tlinfo.dr_account_cd = x_dr_account_cd) OR ((tlinfo.dr_account_cd IS NULL) AND (X_dr_account_cd IS NULL)))
698 AND ((tlinfo.cr_account_cd = x_cr_account_cd) OR ((tlinfo.cr_account_cd IS NULL) AND (X_cr_account_cd IS NULL)))
699 AND ((tlinfo.dr_gl_ccid = x_dr_gl_ccid) OR ((tlinfo.dr_gl_ccid IS NULL) AND (X_dr_gl_ccid IS NULL)))
700 AND ((tlinfo.cr_gl_ccid = x_cr_gl_ccid) OR ((tlinfo.cr_gl_ccid IS NULL) AND (X_cr_gl_ccid IS NULL)))
701 AND ((tlinfo.bill_id = x_bill_id) OR ((tlinfo.bill_id IS NULL) AND (X_bill_id IS NULL)))
702 AND ((tlinfo.bill_number = x_bill_number) OR ((tlinfo.bill_number IS NULL) AND (X_bill_number IS NULL)))
703 AND ((TRUNC(tlinfo.bill_date) = TRUNC(x_bill_date)) OR ((tlinfo.bill_date IS NULL) AND (X_bill_date IS NULL)))
704 AND ((tlinfo.posting_id = x_posting_id) OR ((tlinfo.posting_id IS NULL) AND (X_posting_id IS NULL)))
705 AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (X_gl_date IS NULL)))
706 AND ((TRUNC(tlinfo.gl_posted_date) = TRUNC(x_gl_posted_date)) OR ((tlinfo.gl_posted_date IS NULL) AND (X_gl_posted_date IS NULL)))
707 AND ((tlinfo.posting_control_id = x_posting_control_id) OR ((tlinfo.posting_control_id IS NULL) AND (X_posting_control_id IS NULL)))
708 ) THEN
709 NULL;
710 ELSE
711 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
712 igs_ge_msg_stack.add;
713 app_exception.raise_exception;
714 END IF;
715
716 RETURN;
717
718 END lock_row;
719
720
721 PROCEDURE update_row (
722 x_rowid IN VARCHAR2,
723 x_credit_activity_id IN NUMBER,
724 x_credit_id IN NUMBER,
725 x_status IN VARCHAR2,
726 x_transaction_date IN DATE,
727 x_amount IN NUMBER,
728 x_dr_account_cd IN VARCHAR2,
729 x_cr_account_cd IN VARCHAR2,
730 x_dr_gl_ccid IN NUMBER,
731 x_cr_gl_ccid IN NUMBER,
732 x_bill_id IN NUMBER,
733 x_bill_number IN VARCHAR2,
734 x_bill_date IN DATE,
735 x_posting_id IN NUMBER,
736 x_mode IN VARCHAR2,
737 x_gl_date IN DATE,
738 x_gl_posted_date IN DATE ,
739 x_posting_control_id IN NUMBER
740 ) AS
741 /*
742 || Created By : BDEVARAK
743 || Created On : 26-APR-2001
744 || Purpose : Handles the UPDATE DML logic for the table.
745 || Known limitations, enhancements or remarks :
746 || Change History :
747 || Who When What
748 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
749 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
750 || (reverse chronological order - newest change first)
751 */
752 x_last_update_date DATE ;
753 x_last_updated_by NUMBER;
754 x_last_update_login NUMBER;
755 x_request_id NUMBER;
756 x_program_id NUMBER;
757 x_program_application_id NUMBER;
758 x_program_update_date DATE;
759
760 BEGIN
761
762 x_last_update_date := SYSDATE;
763 IF (X_MODE = 'I') THEN
764 x_last_updated_by := 1;
765 x_last_update_login := 0;
766 ELSIF (x_mode = 'R') THEN
767 x_last_updated_by := fnd_global.user_id;
768 IF x_last_updated_by IS NULL THEN
769 x_last_updated_by := -1;
770 END IF;
771 x_last_update_login := fnd_global.login_id;
772 IF (x_last_update_login IS NULL) THEN
773 x_last_update_login := -1;
774 END IF;
775 ELSE
776 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
777 igs_ge_msg_stack.add;
778 app_exception.raise_exception;
779 END IF;
780
781 before_dml(
782 p_action => 'UPDATE',
783 x_rowid => x_rowid,
784 x_credit_activity_id => x_credit_activity_id,
785 x_credit_id => x_credit_id,
786 x_status => x_status,
787 x_transaction_date => x_transaction_date,
788 x_amount => x_amount,
789 x_dr_account_cd => x_dr_account_cd,
790 x_cr_account_cd => x_cr_account_cd,
791 x_dr_gl_ccid => x_dr_gl_ccid,
792 x_cr_gl_ccid => x_cr_gl_ccid,
793 x_bill_id => x_bill_id,
794 x_bill_number => x_bill_number,
795 x_bill_date => x_bill_date,
796 x_posting_id => x_posting_id,
797 x_creation_date => x_last_update_date,
798 x_created_by => x_last_updated_by,
799 x_last_update_date => x_last_update_date,
800 x_last_updated_by => x_last_updated_by,
801 x_last_update_login => x_last_update_login ,
802 x_gl_date => x_gl_date,
803 x_gl_posted_date => x_gl_posted_date,
804 x_posting_control_id => x_posting_control_id
805 );
806
807 IF (x_mode = 'R') THEN
808 x_request_id := fnd_global.conc_request_id;
809 x_program_id := fnd_global.conc_program_id;
810 x_program_application_id := fnd_global.prog_appl_id;
811 IF (x_request_id = -1) THEN
812 x_request_id := old_references.request_id;
813 x_program_id := old_references.program_id;
814 x_program_application_id := old_references.program_application_id;
815 x_program_update_date := old_references.program_update_date;
816 ELSE
817 x_program_update_date := SYSDATE;
818 END IF;
819 END IF;
820
821 UPDATE igs_fi_cr_activities
822 SET
823 credit_id = new_references.credit_id,
824 status = new_references.status,
825 transaction_date = new_references.transaction_date,
826 amount = new_references.amount,
827 dr_account_cd = new_references.dr_account_cd,
828 cr_account_cd = new_references.cr_account_cd,
829 dr_gl_ccid = new_references.dr_gl_ccid,
830 cr_gl_ccid = new_references.cr_gl_ccid,
831 bill_id = new_references.bill_id,
832 bill_number = new_references.bill_number,
833 bill_date = new_references.bill_date,
834 posting_id = new_references.posting_id,
835 last_update_date = x_last_update_date,
836 last_updated_by = x_last_updated_by,
837 last_update_login = x_last_update_login ,
838 request_id = x_request_id,
839 program_id = x_program_id,
840 program_application_id = x_program_application_id,
841 program_update_date = x_program_update_date ,
842 gl_date = new_references.gl_date,
843 gl_posted_date = new_references.gl_posted_date,
844 posting_control_id = new_references.posting_control_id
845 WHERE rowid = x_rowid;
846
847 IF (SQL%NOTFOUND) THEN
848 RAISE NO_DATA_FOUND;
849 END IF;
850
851 END update_row;
852
853
854 PROCEDURE add_row (
855 x_rowid IN OUT NOCOPY VARCHAR2,
856 x_credit_activity_id IN OUT NOCOPY NUMBER,
857 x_credit_id IN NUMBER,
858 x_status IN VARCHAR2,
859 x_transaction_date IN DATE,
860 x_amount IN NUMBER,
861 x_dr_account_cd IN VARCHAR2,
862 x_cr_account_cd IN VARCHAR2,
863 x_dr_gl_ccid IN NUMBER,
864 x_cr_gl_ccid IN NUMBER,
865 x_bill_id IN NUMBER,
866 x_bill_number IN VARCHAR2,
867 x_bill_date IN DATE,
868 x_posting_id IN NUMBER,
869 x_mode IN VARCHAR2,
870 x_gl_date IN DATE,
871 x_gl_posted_date IN DATE ,
872 x_posting_control_id IN NUMBER
873 ) AS
874 /*
875 || Created By : BDEVARAK
876 || Created On : 26-APR-2001
877 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
878 || Known limitations, enhancements or remarks :
879 || Change History :
880 || Who When What
881 || smadathi 01-Nov-2002 Enh. Bug 2584986. Added new column GL_DATE,GL_POSTED_DATE,
882 || POSTING_CONTROL_ID. Also removed all DEFAULT CLAUSES
883 || (reverse chronological order - newest change first)
884 */
885 CURSOR c1 IS
886 SELECT rowid
887 FROM igs_fi_cr_activities
888 WHERE credit_activity_id = x_credit_activity_id;
889
890 BEGIN
891
892 OPEN c1;
893 FETCH c1 INTO x_rowid;
894 IF (c1%NOTFOUND) THEN
895 CLOSE c1;
896
897 insert_row (
898 x_rowid,
899 x_credit_activity_id,
900 x_credit_id,
901 x_status,
902 x_transaction_date,
903 x_amount,
904 x_dr_account_cd,
905 x_cr_account_cd,
906 x_dr_gl_ccid,
907 x_cr_gl_ccid,
908 x_bill_id,
909 x_bill_number,
910 x_bill_date,
911 x_posting_id,
912 x_mode,
913 x_gl_date,
914 x_gl_posted_date,
915 x_posting_control_id
916 );
917 RETURN;
918 END IF;
919 CLOSE c1;
920
921 update_row (
922 x_rowid,
923 x_credit_activity_id,
924 x_credit_id,
925 x_status,
926 x_transaction_date,
927 x_amount,
928 x_dr_account_cd,
929 x_cr_account_cd,
930 x_dr_gl_ccid,
931 x_cr_gl_ccid,
932 x_bill_id,
933 x_bill_number,
934 x_bill_date,
935 x_posting_id,
936 x_mode ,
937 x_gl_date,
938 x_gl_posted_date,
939 x_posting_control_id
940 );
941
942 END add_row;
943
944
945 PROCEDURE delete_row (
946 x_rowid IN VARCHAR2
947 ) AS
948 /*
949 || Created By : BDEVARAK
950 || Created On : 26-APR-2001
951 || Purpose : Handles the DELETE DML logic for the table.
952 || Known limitations, enhancements or remarks :
953 || Change History :
954 || Who When What
955 || (reverse chronological order - newest change first)
956 */
957 BEGIN
958
959 before_dml (
960 p_action => 'DELETE',
961 x_rowid => x_rowid
962 );
963
964 DELETE FROM igs_fi_cr_activities
965 WHERE rowid = x_rowid;
966
967 IF (SQL%NOTFOUND) THEN
968 RAISE NO_DATA_FOUND;
969 END IF;
970
971 END delete_row;
972
973
974 END igs_fi_cr_activities_pkg;