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