1 PACKAGE BODY igs_fi_cr_types_pkg AS
2 /* $Header: IGSSI88B.pls 120.1 2005/09/22 01:53:48 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_cr_types_all%ROWTYPE;
6 new_references igs_fi_cr_types_all%ROWTYPE;
7 PROCEDURE beforerowdelete;
8
9 PROCEDURE set_column_values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2,
12 x_credit_type_id IN NUMBER ,
13 x_credit_type_name IN VARCHAR2,
14 x_description IN VARCHAR2,
15 x_credit_class IN VARCHAR2,
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_effective_start_date IN DATE ,
21 x_effective_end_date IN DATE ,
22 x_refund_allowed IN VARCHAR2,
23 x_payment_priority IN NUMBER ,
24 x_attribute_category IN VARCHAR2,
25 x_attribute1 IN VARCHAR2,
26 x_attribute2 IN VARCHAR2,
27 x_attribute3 IN VARCHAR2,
28 x_attribute4 IN VARCHAR2,
29 x_attribute5 IN VARCHAR2,
30 x_attribute6 IN VARCHAR2,
31 x_attribute7 IN VARCHAR2,
32 x_attribute8 IN VARCHAR2,
33 x_attribute9 IN VARCHAR2,
34 x_attribute10 IN VARCHAR2,
35 x_attribute11 IN VARCHAR2,
36 x_attribute12 IN VARCHAR2,
37 x_attribute13 IN VARCHAR2,
38 x_attribute14 IN VARCHAR2,
39 x_attribute15 IN VARCHAR2,
40 x_attribute16 IN VARCHAR2,
41 x_attribute17 IN VARCHAR2,
42 x_attribute18 IN VARCHAR2,
43 x_attribute19 IN VARCHAR2,
44 x_attribute20 IN VARCHAR2,
45 x_title4_type_ind IN VARCHAR2,
46 x_creation_date IN DATE ,
47 x_created_by IN NUMBER ,
48 x_last_update_date IN DATE ,
49 x_last_updated_by IN NUMBER ,
50 x_last_update_login IN NUMBER ,
51 x_payment_credit_type_id IN NUMBER ,
52 x_forfeiture_gl_ccid IN NUMBER ,
53 x_forfeiture_account_cd IN VARCHAR2,
54 x_appl_hierarchy_id IN NUMBER
55 ) AS
56 /*
57 || Created By : BDEVARAK
58 || Created On : 26-APR-2001
59 || Purpose : Initialises the Old and New references for the columns of the table.
60 || Known limitations, enhancements or remarks :
61 || Change History :
62 || Who When What
63 || (reverse chronological order - newest change first)
64 || sapanigr 20-Sep-2005 Enh#4228665.Added field appl_hierarchy_id.
65 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
66 || payment_credit_type_id, forfeiture_gl_ccid
67 || and forfeiture_account_cd
68 || vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.Also removed
69 || DEFAULTing the parameters using DEFAULT keyword to avoid gscc warnings.
70 */
71
72 CURSOR cur_old_ref_values IS
73 SELECT *
74 FROM IGS_FI_CR_TYPES_ALL
75 WHERE rowid = x_rowid;
76
77 BEGIN
78
79 l_rowid := x_rowid;
80
81 -- Code for setting the Old and New Reference Values.
82 -- Populate Old Values.
83 OPEN cur_old_ref_values;
84 FETCH cur_old_ref_values INTO old_references;
85 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
86 CLOSE cur_old_ref_values;
87 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
88 igs_ge_msg_stack.add;
89 app_exception.raise_exception;
90 RETURN;
91 END IF;
92 CLOSE cur_old_ref_values;
93
94 -- Populate New Values.
95 new_references.credit_type_id := x_credit_type_id;
96 new_references.credit_type_name := x_credit_type_name;
97 new_references.description := x_description;
98 new_references.credit_class := x_credit_class;
99 new_references.dr_account_cd := x_dr_account_cd;
100 new_references.cr_account_cd := x_cr_account_cd;
101 new_references.dr_gl_ccid := x_dr_gl_ccid;
102 new_references.cr_gl_ccid := x_cr_gl_ccid;
103 new_references.effective_start_date := x_effective_start_date;
104 new_references.effective_end_date := x_effective_end_date;
105 new_references.refund_allowed := x_refund_allowed;
106 new_references.payment_priority := x_payment_priority;
107 new_references.attribute_category := x_attribute_category;
108 new_references.attribute1 := x_attribute1;
109 new_references.attribute2 := x_attribute2;
110 new_references.attribute3 := x_attribute3;
111 new_references.attribute4 := x_attribute4;
112 new_references.attribute5 := x_attribute5;
113 new_references.attribute6 := x_attribute6;
114 new_references.attribute7 := x_attribute7;
115 new_references.attribute8 := x_attribute8;
116 new_references.attribute9 := x_attribute9;
117 new_references.attribute10 := x_attribute10;
118 new_references.attribute11 := x_attribute11;
119 new_references.attribute12 := x_attribute12;
120 new_references.attribute13 := x_attribute13;
121 new_references.attribute14 := x_attribute14;
122 new_references.attribute15 := x_attribute15;
123 new_references.attribute16 := x_attribute16;
124 new_references.attribute17 := x_attribute17;
125 new_references.attribute18 := x_attribute18;
126 new_references.attribute19 := x_attribute19;
127 new_references.attribute20 := x_attribute20;
128 new_references.title4_type_ind := x_title4_type_ind;
129 new_references.payment_credit_type_id := x_payment_credit_type_id;
130 new_references.forfeiture_gl_ccid := x_forfeiture_gl_ccid;
131 new_references.forfeiture_account_cd := x_forfeiture_account_cd;
132 new_references.appl_hierarchy_id := x_appl_hierarchy_id;
133
134
135 IF (p_action = 'UPDATE') THEN
136 new_references.creation_date := old_references.creation_date;
137 new_references.created_by := old_references.created_by;
138 ELSE
139 new_references.creation_date := x_creation_date;
140 new_references.created_by := x_created_by;
141 END IF;
142
143 new_references.last_update_date := x_last_update_date;
144 new_references.last_updated_by := x_last_updated_by;
145 new_references.last_update_login := x_last_update_login;
146
147 END set_column_values;
148
149 PROCEDURE BeforeRowInsertUpdate1(
150 p_inserting IN BOOLEAN,
151 p_updating IN BOOLEAN,
152 p_deleting IN BOOLEAN
153 ) AS
154
155 --CHANGE HISTORY
156 --Who When What
157 --vvutukur 26-Aug-2003 Enh#3045007.Added Installment Payments credit class in order to restrict the creation
158 -- of credit types with overlapping dates.
159 --vvutukur 09-Dec-2002 Enh#2584741.Added cursor cur_enr_deposit and corresponding validation not to allow
160 -- the user to create two credit types with credit class 'Enrollment Deposit'.
161 -- Also modified cursors cur_online_pay,cur_nca_meaning to check dates overlapping.Check
162 -- introduced with the value specified by the user on the form instead of SYSDATE, for
163 -- online payment and negative charge adjustment credit classes.Replaced igs_lookups_view
164 -- with igs_lookup_values and selected active lookup codes only.
165 --vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id from cursors cur_online_pay,
166 -- cur_nca and from places where the cursors are used.Also removed DEFAULT
167 -- in procedure parameters list to avoid gscc warnings.
168 --vvutukur 30-jan-2002 Modified cur_online_pay and added IF condition
169 -- before opening that cursor for bug 2183291 as part of SFCR003.
170 --vvutukur 25-Jan-2002 Added code to error out NOCOPY when a Credit Type
171 -- with credit class CHGADJ already exists
172 -- for the subaccount for bug 2195715 as part of SFCR003.
173
174 CURSOR cur_overlapping_u(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE,
175 cp_credit_class igs_fi_cr_types.credit_class%TYPE
176 ) IS
177 SELECT 'x'
178 FROM igs_fi_cr_types
179 WHERE credit_class = cp_credit_class
180 AND credit_type_id <> cp_credit_type_id
181 AND (new_references.effective_start_date >= effective_start_date AND new_references.effective_start_date <= NVL(effective_end_date,new_references.effective_start_date)
182 OR ((new_references.effective_end_date >= effective_start_date OR (new_references.effective_end_date IS NULL)) AND new_references.effective_start_date <= effective_start_date));
183
184 l_var VARCHAR2(1);
185
186 BEGIN
187
188 --Validating if effective dates of credit type are not overlapping
189 IF new_references.credit_class IN ('CHGADJ','ONLINE PAYMENT','ENRDEPOSIT','INSTALLMENT_PAYMENTS') THEN
190 OPEN cur_overlapping_u(new_references.credit_type_id,new_references.credit_class);
191 FETCH cur_overlapping_u INTO l_var;
192 IF cur_overlapping_u%FOUND THEN
193 CLOSE cur_overlapping_u;
194 FND_MESSAGE.SET_NAME('IGS','IGS_FI_SUB_ACCT_CR_TYP_EXIST');
195 FND_MESSAGE.SET_TOKEN('CRCLASS',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CLASS',new_references.credit_class));
196 IGS_GE_MSG_STACK.ADD;
197 APP_EXCEPTION.RAISE_EXCEPTION;
198 END IF;
199 CLOSE cur_overlapping_u;
200 END IF;
201
202 END BeforeRowInsertUpdate1;
203
204
205 PROCEDURE check_uniqueness AS
206 /*
207 || Created By : BDEVARAK
208 || Created On : 26-APR-2001
209 || Purpose : Handles the Unique Constraint logic defined for the columns.
210 || Known limitations, enhancements or remarks :
211 || Change History :
212 || Who When What
213 || (reverse chronological order - newest change first)
214 */
215 BEGIN
216
217 IF ( get_uk_for_validation (
218 new_references.credit_type_name,
219 new_references.effective_start_date
220 )
221 ) THEN
222 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
223 igs_ge_msg_stack.add;
224 app_exception.raise_exception;
225 END IF;
226
227 END check_uniqueness;
228
229
230 PROCEDURE check_parent_existance AS
231 /*
232 || Created By : BDEVARAK
233 || Created On : 26-APR-2001
234 || Purpose : Checks for the existance of Parent records.
235 || Known limitations, enhancements or remarks :
236 || Change History :
237 || Who When What
238 || (reverse chronological order - newest change first)
239 || sapanigr 20-Sep-2005 Enh#4228665.Added check for parent in igs_fi_a_hierarchies_pkg
240 || vvutukur 16-Sep-2002 Enh#2564643.Removed code referring subaccount_id which contains call to
241 || igs_fi_subaccts_pkg.get_pk_for_validation.
242 */
243 BEGIN
244
245 IF ((old_references.cr_account_cd = new_references.cr_account_cd) OR
246 (new_references.cr_account_cd IS NULL)) THEN
247 NULL;
248 ELSE
249 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
250 new_references.cr_account_cd
251 ) THEN
252 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256 END IF;
257 IF ((old_references.dr_account_cd = new_references.dr_account_cd) OR
258 (new_references.dr_account_cd IS NULL)) THEN
259 NULL;
260 ELSE
261 IF NOT IGS_FI_ACC_PKG.Get_PK_For_Validation (
262 new_references.dr_account_cd
263 ) THEN
264 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
265 IGS_GE_MSG_STACK.ADD;
266 App_Exception.Raise_Exception;
267 END IF;
268 END IF;
269
270 -- Added by sapanigr for Enhancement 4228665.
271 IF ((old_references.appl_hierarchy_id = new_references.appl_hierarchy_id) OR
272 (new_references.appl_hierarchy_id IS NULL)) THEN
273 NULL;
274 ELSE
275 IF NOT igs_fi_a_hierarchies_pkg.Get_PK_For_Validation (
276 new_references.appl_hierarchy_id
277 ) THEN
278 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 END IF;
283
284 IF (((old_references.credit_class = new_references.credit_class)) OR
285 ((new_references.credit_class IS NULL))) THEN
289 new_references.credit_class
286 NULL;
287 ELSIF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
288 'IGS_FI_CREDIT_CLASS',
290 )THEN
291 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295
296 -- Following is added by shtatiko as part of Deposits Build, Bug# 2584741
297 -- Check if any account exists with code new_references.forfeiture_account_cd
298 IF ((old_references.forfeiture_account_cd = new_references.forfeiture_account_cd) OR
299 (new_references.forfeiture_account_cd IS NULL)) THEN
300 NULL;
301 ELSE
302 IF NOT igs_fi_acc_pkg.get_pk_for_validation (
303 x_account_cd => new_references.forfeiture_account_cd
304 ) THEN
305 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
306 IGS_GE_MSG_STACK.ADD;
307 App_Exception.Raise_Exception;
308 END IF;
309 END IF;
310
311 IF ((old_references.payment_credit_type_id = new_references.payment_credit_type_id) OR
312 (new_references.payment_credit_type_id IS NULL)) THEN
313 NULL;
314 ELSE
315 IF NOT igs_fi_cr_types_pkg.get_pk_for_validation (
316 x_credit_type_id => new_references.payment_credit_type_id
317 ) THEN
318 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321 END IF;
322 END IF;
323
324 END check_parent_existance;
325
326
327 PROCEDURE check_child_existance IS
328 /*
329 || Created By : BDEVARAK
330 || Created On : 26-APR-2001
331 || Purpose : Checks for the existance of Child records.
332 || Known limitations, enhancements or remarks :
333 || Change History :
334 || Who When What
335 || sapanigr 21-SEP-2005 Enhc 4228665 Deleted igs_fi_a_hierarchies_pkg.get_fk_igs_fi_cr_types_all
336 || (reverse chronological order - newest change first)
337 */
338 BEGIN
339
340
341
342 igs_fi_bal_ex_c_typs_pkg.get_fk_igs_fi_cr_types_all (
343 old_references.credit_type_id
344 );
345
346 igs_fi_credits_pkg.get_fk_igs_fi_cr_types_all (
347 old_references.credit_type_id
348 );
349
350 -- Added by shtatiko as part of Enh Bug# 2584741, Deposits Build
351 igs_fi_cr_types_pkg.get_fk_igs_fi_cr_types_all (
352 old_references.credit_type_id
353 );
354
355 END check_child_existance;
356
357
358 PROCEDURE GET_FK_IGS_FI_CR_TYPES_ALL(
359 x_credit_type_id IN NUMBER
360 ) AS
361 /*
362 || Created By : shtatiko
363 || Created On : 04-DEC-2002
364 || Purpose : Validates the foreign Key of the table.
365 || Known limitations, enhancements or remarks :
366 || Change History :
367 || Who When What
368 || (reverse chronological order - newest change first)
369 */
370 CURSOR cur_rowid IS
371 SELECT rowid
372 FROM IGS_FI_CR_TYPES_ALL
373 WHERE credit_type_id = x_credit_type_id;
374 lv_rowid cur_rowid%RowType;
375 BEGIN
376 Open cur_rowid;
377 Fetch cur_rowid INTO lv_rowid;
378 IF (cur_rowid%FOUND) THEN
379 Fnd_Message.Set_Name ('IGS', 'IGS_FI_CRTY_CRTY_FK');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 Close cur_rowid;
383 Return;
384 END IF;
385 Close cur_rowid;
386 END GET_FK_IGS_FI_CR_TYPES_ALL;
387
388
389 FUNCTION get_pk_for_validation (
390 x_credit_type_id IN NUMBER
391 ) RETURN BOOLEAN AS
392 /*
393 || Created By : BDEVARAK
394 || Created On : 26-APR-2001
395 || Purpose : Validates the Primary Key of the table.
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || smadathi 10-Jun-2002 Bug 2404523. The row share table lock on the table igs_fi_cr_types_all
400 || removed.
401 || (reverse chronological order - newest change first)
402 */
403 CURSOR cur_rowid IS
404 SELECT rowid
405 FROM igs_fi_cr_types_all
406 WHERE credit_type_id = x_credit_type_id ;
407
408
409 lv_rowid cur_rowid%RowType;
410
411 BEGIN
412
413 OPEN cur_rowid;
414 FETCH cur_rowid INTO lv_rowid;
415 IF (cur_rowid%FOUND) THEN
416 CLOSE cur_rowid;
417 RETURN(TRUE);
418 ELSE
419 CLOSE cur_rowid;
420 RETURN(FALSE);
421 END IF;
422
423 END get_pk_for_validation;
424
425
426 FUNCTION get_uk_for_validation (
427 x_credit_type_name IN VARCHAR2,
428 x_effective_start_date IN DATE
429 ) RETURN BOOLEAN AS
430 /*
431 || Created By : BDEVARAK
432 || Created On : 26-APR-2001
433 || Purpose : Validates the Unique Keys of the table.
434 || Known limitations, enhancements or remarks :
435 || Change History :
436 || Who When What
437 || (reverse chronological order - newest change first)
438 */
439 CURSOR cur_rowid IS
443 AND ((effective_start_date = x_effective_start_date) OR (effective_start_date IS NULL AND x_effective_start_date IS NULL))
440 SELECT rowid
441 FROM igs_fi_cr_types
442 WHERE credit_type_name = x_credit_type_name
444 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
445
446 lv_rowid cur_rowid%RowType;
447
448 BEGIN
449
450 OPEN cur_rowid;
451 FETCH cur_rowid INTO lv_rowid;
452 IF (cur_rowid%FOUND) THEN
453 CLOSE cur_rowid;
454 RETURN (true);
455 ELSE
456 CLOSE cur_rowid;
457 RETURN(FALSE);
458 END IF;
459
460 END get_uk_for_validation ;
461
462
463 PROCEDURE before_dml (
464 p_action IN VARCHAR2,
465 x_rowid IN VARCHAR2,
466 x_credit_type_id IN NUMBER ,
467 x_credit_type_name IN VARCHAR2,
468 x_description IN VARCHAR2,
469 x_credit_class IN VARCHAR2,
470 x_dr_account_cd IN VARCHAR2,
471 x_cr_account_cd IN VARCHAR2,
472 x_dr_gl_ccid IN NUMBER ,
473 x_cr_gl_ccid IN NUMBER ,
474 x_effective_start_date IN DATE ,
475 x_effective_end_date IN DATE ,
476 x_refund_allowed IN VARCHAR2,
477 x_payment_priority IN NUMBER ,
478 x_attribute_category IN VARCHAR2,
479 x_attribute1 IN VARCHAR2,
480 x_attribute2 IN VARCHAR2,
481 x_attribute3 IN VARCHAR2,
482 x_attribute4 IN VARCHAR2,
483 x_attribute5 IN VARCHAR2,
484 x_attribute6 IN VARCHAR2,
485 x_attribute7 IN VARCHAR2,
486 x_attribute8 IN VARCHAR2,
487 x_attribute9 IN VARCHAR2,
488 x_attribute10 IN VARCHAR2,
489 x_attribute11 IN VARCHAR2,
490 x_attribute12 IN VARCHAR2,
491 x_attribute13 IN VARCHAR2,
492 x_attribute14 IN VARCHAR2,
493 x_attribute15 IN VARCHAR2,
494 x_attribute16 IN VARCHAR2,
495 x_attribute17 IN VARCHAR2,
496 x_attribute18 IN VARCHAR2,
497 x_attribute19 IN VARCHAR2,
498 x_attribute20 IN VARCHAR2,
499 x_title4_type_ind IN VARCHAR2,
500 x_creation_date IN DATE ,
501 x_created_by IN NUMBER ,
502 x_last_update_date IN DATE ,
503 x_last_updated_by IN NUMBER ,
504 x_last_update_login IN NUMBER ,
505 x_payment_credit_type_id IN NUMBER ,
506 x_forfeiture_gl_ccid IN NUMBER ,
507 x_forfeiture_account_cd IN VARCHAR2,
508 x_appl_hierarchy_id IN NUMBER
509 ) AS
510 /*
511 || Created By : BDEVARAK
512 || Created On : 26-APR-2001
513 || Purpose : Initialises the columns, Checks Constraints, Calls the
514 || Trigger Handlers for the table, before any DML operation.
515 || Known limitations, enhancements or remarks :
516 || Change History :
517 || Who When What
518 || (reverse chronological order - newest change first)
519 || sapanigr 20-Sep-2005 Enh#4228665.Added field appl_hierarchy_id.
520 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
521 || payment_credit_type_id, forfeiture_gl_ccid
522 || and forfeiture_account_cd
523 || vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie., from parameters list
524 || and from call to set_column_values.Also removed DEFAULTing values
525 || in parameter list using DEFAULT keyword to avoid gscc warnings.
526 */
527 BEGIN
528
529 set_column_values (
530 p_action,
531 x_rowid,
532 x_credit_type_id,
533 x_credit_type_name,
534 x_description,
535 x_credit_class,
536 x_dr_account_cd,
537 x_cr_account_cd,
538 x_dr_gl_ccid,
539 x_cr_gl_ccid,
540 x_effective_start_date,
541 x_effective_end_date,
542 x_refund_allowed,
543 x_payment_priority,
544 x_attribute_category,
545 x_attribute1,
546 x_attribute2,
547 x_attribute3,
548 x_attribute4,
549 x_attribute5,
550 x_attribute6,
551 x_attribute7,
552 x_attribute8,
553 x_attribute9,
554 x_attribute10,
555 x_attribute11,
556 x_attribute12,
557 x_attribute13,
558 x_attribute14,
559 x_attribute15,
560 x_attribute16,
561 x_attribute17,
562 x_attribute18,
563 x_attribute19,
564 x_attribute20,
568 x_last_update_date,
565 x_title4_type_ind,
566 x_creation_date,
567 x_created_by,
569 x_last_updated_by,
570 x_last_update_login,
571 x_payment_credit_type_id,
572 x_forfeiture_gl_ccid,
573 x_forfeiture_account_cd,
574 x_appl_hierarchy_id
575 );
576
577 IF (p_action = 'INSERT') THEN
578 -- Call all the procedures related to Before Insert.
579 BeforeRowInsertUpdate1(p_inserting => FALSE,
580 p_updating => FALSE,
581 p_deleting => FALSE);
582 IF ( get_pk_for_validation(
583 new_references.credit_type_id
584 )
585 ) THEN
586 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
587 igs_ge_msg_stack.add;
588 app_exception.raise_exception;
589 END IF;
590 check_uniqueness;
591 check_parent_existance;
592 ELSIF (p_action = 'UPDATE') THEN
593 -- Call all the procedures related to Before Update.
594 BeforeRowInsertUpdate1(p_inserting => FALSE,
595 p_updating => FALSE,
596 p_deleting => FALSE);
597 check_uniqueness;
598 check_parent_existance;
599 ELSIF (p_action = 'DELETE') THEN
600 -- Call all the procedures related to Before Delete.
601 check_child_existance;
602 beforerowdelete;
603 ELSIF (p_action = 'VALIDATE_INSERT') THEN
604 -- Call all the procedures related to Before Insert.
605 BeforeRowInsertUpdate1(p_inserting => FALSE,
606 p_updating => FALSE,
607 p_deleting => FALSE);
608 IF ( get_pk_for_validation (
609 new_references.credit_type_id
610 )
611 ) THEN
612 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
613 igs_ge_msg_stack.add;
614 app_exception.raise_exception;
615 END IF;
616 check_uniqueness;
617 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
618 BeforeRowInsertUpdate1(p_inserting => FALSE,
619 p_updating => FALSE,
620 p_deleting => FALSE);
621 check_uniqueness;
622 ELSIF (p_action = 'VALIDATE_DELETE') THEN
623 check_child_existance;
624 END IF;
625
626 END before_dml;
627
628
629 PROCEDURE insert_row (
630 x_rowid IN OUT NOCOPY VARCHAR2,
631 x_credit_type_id IN OUT NOCOPY NUMBER,
632 x_credit_type_name IN VARCHAR2,
633 x_description IN VARCHAR2,
634 x_credit_class IN VARCHAR2,
635 x_dr_account_cd IN VARCHAR2,
636 x_cr_account_cd IN VARCHAR2,
637 x_dr_gl_ccid IN NUMBER,
638 x_cr_gl_ccid IN NUMBER,
639 x_effective_start_date IN DATE,
640 x_effective_end_date IN DATE,
641 x_refund_allowed IN VARCHAR2,
642 x_payment_priority IN NUMBER,
643 x_attribute_category IN VARCHAR2,
644 x_attribute1 IN VARCHAR2,
645 x_attribute2 IN VARCHAR2,
646 x_attribute3 IN VARCHAR2,
647 x_attribute4 IN VARCHAR2,
648 x_attribute5 IN VARCHAR2,
649 x_attribute6 IN VARCHAR2,
650 x_attribute7 IN VARCHAR2,
651 x_attribute8 IN VARCHAR2,
652 x_attribute9 IN VARCHAR2,
653 x_attribute10 IN VARCHAR2,
654 x_attribute11 IN VARCHAR2,
655 x_attribute12 IN VARCHAR2,
656 x_attribute13 IN VARCHAR2,
657 x_attribute14 IN VARCHAR2,
658 x_attribute15 IN VARCHAR2,
659 x_attribute16 IN VARCHAR2,
660 x_attribute17 IN VARCHAR2,
661 x_attribute18 IN VARCHAR2,
662 x_attribute19 IN VARCHAR2,
663 x_attribute20 IN VARCHAR2,
664 x_title4_type_ind IN VARCHAR2,
665 x_mode IN VARCHAR2,
666 x_payment_credit_type_id IN NUMBER,
667 x_forfeiture_gl_ccid IN NUMBER,
668 x_forfeiture_account_cd IN VARCHAR2,
669 x_appl_hierarchy_id IN NUMBER
670 ) AS
671 /*
672 || Created By : BDEVARAK
673 || Created On : 26-APR-2001
674 || Purpose : Handles the INSERT DML logic for the table.
675 || Known limitations, enhancements or remarks :
676 || Change History :
677 || Who When What
678 || (reverse chronological order - newest change first)
679 || sapanigr 20-Sep-2005 Enh#4228665.Added field appl_hierarchy_id.
680 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
684 || list and from call to before_dml and from insert statement.Also removed
681 || payment_credit_type_id, forfeiture_gl_ccid
682 || and forfeiture_account_cd
683 || vvutukur 16-Sep-2002 Enh#2564643.Removed referenes to subaccount_id.ie., from parameters
685 || DEFAULT keyword in parameter list.
686 */
687 CURSOR c IS
688 SELECT rowid
689 FROM igs_fi_cr_types_all
690 WHERE credit_type_id = x_credit_type_id;
691
692 x_last_update_date DATE;
693 x_last_updated_by NUMBER;
694 x_last_update_login NUMBER;
695
696 BEGIN
697
698 x_last_update_date := SYSDATE;
699 IF (x_mode = 'I') THEN
700 x_last_updated_by := 1;
701 x_last_update_login := 0;
702 ELSIF (x_mode = 'R') THEN
703 x_last_updated_by := fnd_global.user_id;
704 IF (x_last_updated_by IS NULL) THEN
705 x_last_updated_by := -1;
706 END IF;
707 x_last_update_login := fnd_global.login_id;
708 IF (x_last_update_login IS NULL) THEN
709 x_last_update_login := -1;
710 END IF;
711 ELSE
712 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
713 igs_ge_msg_stack.add;
714 app_exception.raise_exception;
715 END IF;
716
717 SELECT igs_fi_cr_types_s.NEXTVAL
718 INTO x_credit_type_id
719 FROM dual;
720
721 new_references.org_id := igs_ge_gen_003.get_org_id;
722
723 before_dml(
724 p_action => 'INSERT',
725 x_rowid => x_rowid,
726 x_credit_type_id => x_credit_type_id,
727 x_credit_type_name => x_credit_type_name,
728 x_description => x_description,
729 x_credit_class => x_credit_class,
730 x_dr_account_cd => x_dr_account_cd,
731 x_cr_account_cd => x_cr_account_cd,
732 x_dr_gl_ccid => x_dr_gl_ccid,
733 x_cr_gl_ccid => x_cr_gl_ccid,
734 x_effective_start_date => x_effective_start_date,
735 x_effective_end_date => x_effective_end_date,
736 x_refund_allowed => x_refund_allowed,
737 x_payment_priority => x_payment_priority,
738 x_attribute_category => x_attribute_category,
739 x_attribute1 => x_attribute1,
740 x_attribute2 => x_attribute2,
741 x_attribute3 => x_attribute3,
742 x_attribute4 => x_attribute4,
743 x_attribute5 => x_attribute5,
744 x_attribute6 => x_attribute6,
745 x_attribute7 => x_attribute7,
746 x_attribute8 => x_attribute8,
747 x_attribute9 => x_attribute9,
748 x_attribute10 => x_attribute10,
749 x_attribute11 => x_attribute11,
750 x_attribute12 => x_attribute12,
751 x_attribute13 => x_attribute13,
752 x_attribute14 => x_attribute14,
753 x_attribute15 => x_attribute15,
754 x_attribute16 => x_attribute16,
755 x_attribute17 => x_attribute17,
756 x_attribute18 => x_attribute18,
757 x_attribute19 => x_attribute19,
758 x_attribute20 => x_attribute20,
759 x_title4_type_ind => x_title4_type_ind,
760 x_creation_date => x_last_update_date,
761 x_created_by => x_last_updated_by,
762 x_last_update_date => x_last_update_date,
763 x_last_updated_by => x_last_updated_by,
764 x_last_update_login => x_last_update_login,
765 x_payment_credit_type_id => x_payment_credit_type_id,
766 x_forfeiture_gl_ccid => x_forfeiture_gl_ccid,
767 x_forfeiture_account_cd => x_forfeiture_account_cd,
768 x_appl_hierarchy_id => x_appl_hierarchy_id
769 );
770
771 INSERT INTO igs_fi_cr_types_all (
772 credit_type_id,
773 credit_type_name,
774 description,
775 credit_class,
776 dr_account_cd,
777 cr_account_cd,
778 dr_gl_ccid,
779 cr_gl_ccid,
780 effective_start_date,
781 effective_end_date,
782 refund_allowed,
783 org_id,
784 payment_priority,
785 attribute_category,
786 attribute1,
787 attribute2,
788 attribute3,
789 attribute4,
790 attribute5,
791 attribute6,
792 attribute7,
793 attribute8,
794 attribute9,
795 attribute10,
796 attribute11,
797 attribute12,
798 attribute13,
799 attribute14,
800 attribute15,
801 attribute16,
802 attribute17,
803 attribute18,
804 attribute19,
808 created_by,
805 attribute20,
806 title4_type_ind,
807 creation_date,
809 last_update_date,
810 last_updated_by,
811 last_update_login,
812 payment_credit_type_id,
813 forfeiture_gl_ccid,
814 forfeiture_acCount_cd,
815 appl_hierarchy_id
816 ) VALUES (
817 new_references.credit_type_id,
818 new_references.credit_type_name,
819 new_references.description,
820 new_references.credit_class,
821 new_references.dr_account_cd,
822 new_references.cr_account_cd,
823 new_references.dr_gl_ccid,
824 new_references.cr_gl_ccid,
825 new_references.effective_start_date,
826 new_references.effective_end_date,
827 new_references.refund_allowed,
828 new_references.org_id,
829 new_references.payment_priority,
830 new_references.attribute_category,
831 new_references.attribute1,
832 new_references.attribute2,
833 new_references.attribute3,
834 new_references.attribute4,
835 new_references.attribute5,
836 new_references.attribute6,
837 new_references.attribute7,
838 new_references.attribute8,
839 new_references.attribute9,
840 new_references.attribute10,
841 new_references.attribute11,
842 new_references.attribute12,
843 new_references.attribute13,
844 new_references.attribute14,
845 new_references.attribute15,
846 new_references.attribute16,
847 new_references.attribute17,
848 new_references.attribute18,
849 new_references.attribute19,
850 new_references.attribute20,
851 new_references.title4_type_ind,
852 x_last_update_date,
853 x_last_updated_by,
854 x_last_update_date,
855 x_last_updated_by,
856 x_last_update_login,
857 new_references.payment_credit_type_id,
858 new_references.forfeiture_gl_ccid,
859 new_references.forfeiture_account_cd,
860 new_references.appl_hierarchy_id
861 );
862
863 OPEN c;
864 FETCH c INTO x_rowid;
865 IF (c%NOTFOUND) THEN
866 CLOSE c;
867 RAISE NO_DATA_FOUND;
868 END IF;
869 CLOSE c;
870
871 END insert_row;
872
873
874 PROCEDURE lock_row (
875 x_rowid IN VARCHAR2,
876 x_credit_type_id IN NUMBER,
877 x_credit_type_name IN VARCHAR2,
878 x_description IN VARCHAR2,
879 x_credit_class IN VARCHAR2,
880 x_dr_account_cd IN VARCHAR2,
881 x_cr_account_cd IN VARCHAR2,
882 x_dr_gl_ccid IN NUMBER,
883 x_cr_gl_ccid IN NUMBER,
884 x_effective_start_date IN DATE,
885 x_effective_end_date IN DATE,
886 x_refund_allowed IN VARCHAR2,
887 x_payment_priority IN NUMBER,
888 x_attribute_category IN VARCHAR2,
889 x_attribute1 IN VARCHAR2,
890 x_attribute2 IN VARCHAR2,
891 x_attribute3 IN VARCHAR2,
892 x_attribute4 IN VARCHAR2,
893 x_attribute5 IN VARCHAR2,
894 x_attribute6 IN VARCHAR2,
895 x_attribute7 IN VARCHAR2,
896 x_attribute8 IN VARCHAR2,
897 x_attribute9 IN VARCHAR2,
898 x_attribute10 IN VARCHAR2,
899 x_attribute11 IN VARCHAR2,
900 x_attribute12 IN VARCHAR2,
901 x_attribute13 IN VARCHAR2,
902 x_attribute14 IN VARCHAR2,
903 x_attribute15 IN VARCHAR2,
904 x_attribute16 IN VARCHAR2,
905 x_attribute17 IN VARCHAR2,
906 x_attribute18 IN VARCHAR2,
907 x_attribute19 IN VARCHAR2,
908 x_attribute20 IN VARCHAR2,
909 x_title4_type_ind IN VARCHAR2,
910 x_payment_credit_type_id IN NUMBER,
911 x_forfeiture_gl_ccid IN NUMBER,
912 x_forfeiture_account_cd IN VARCHAR2,
913 x_appl_hierarchy_id IN NUMBER
914 ) AS
915 /*
916 || Created By : BDEVARAK
917 || Created On : 26-APR-2001
918 || Purpose : Handles the LOCK mechanism for the table.
919 || Known limitations, enhancements or remarks :
920 || Change History :
921 || Who When What
922 || (reverse chronological order - newest change first)
923 || sapanigr 20-Sep_2005 Enh#4228665.Added field appl_hierarchy_id.
924 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
925 || payment_credit_type_id, forfeiture_gl_ccid
926 || and forfeiture_account_cd
930 */
927 || vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie., from parameters list,
928 || from cursor c1 and from if condition.Also removed DEFAULT value
929 || for x_title4_type_ind from parameter list to avoid gscc warning.
931 CURSOR c1 IS
932 SELECT
933 credit_type_name,
934 description,
935 credit_class,
936 dr_account_cd,
937 cr_account_cd,
938 dr_gl_ccid,
939 cr_gl_ccid,
940 effective_start_date,
941 effective_end_date,
942 refund_allowed,
943 payment_priority,
944 attribute_category,
945 attribute1,
946 attribute2,
947 attribute3,
948 attribute4,
949 attribute5,
950 attribute6,
951 attribute7,
952 attribute8,
953 attribute9,
954 attribute10,
955 attribute11,
956 attribute12,
957 attribute13,
958 attribute14,
959 attribute15,
960 attribute16,
961 attribute17,
962 attribute18,
963 attribute19,
964 attribute20,
965 title4_type_ind,
966 payment_credit_type_id,
967 forfeiture_gl_ccid,
968 forfeiture_account_cd,
969 appl_hierarchy_id
970 FROM igs_fi_cr_types_all
971 WHERE rowid = x_rowid
972 FOR UPDATE NOWAIT;
973
974 tlinfo c1%ROWTYPE;
975
976 BEGIN
977
978 OPEN c1;
979 FETCH c1 INTO tlinfo;
980 IF (c1%notfound) THEN
981 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
982 igs_ge_msg_stack.add;
983 CLOSE c1;
984 app_exception.raise_exception;
985 RETURN;
986 END IF;
987 CLOSE c1;
988
989 IF (
990 (tlinfo.credit_type_name = x_credit_type_name)
991 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
992 AND (tlinfo.credit_class = x_credit_class)
993 AND ((tlinfo.dr_account_cd = x_dr_account_cd) OR ((tlinfo.dr_account_cd IS NULL) AND (X_dr_account_cd IS NULL)))
994 AND ((tlinfo.cr_account_cd = x_cr_account_cd) OR ((tlinfo.cr_account_cd IS NULL) AND (X_cr_account_cd IS NULL)))
995 AND ((tlinfo.dr_gl_ccid = x_dr_gl_ccid) OR ((tlinfo.dr_gl_ccid IS NULL) AND (X_dr_gl_ccid IS NULL)))
996 AND ((tlinfo.cr_gl_ccid = x_cr_gl_ccid) OR ((tlinfo.cr_gl_ccid IS NULL) AND (X_cr_gl_ccid IS NULL)))
997 AND ((tlinfo.effective_start_date = x_effective_start_date) OR ((tlinfo.effective_start_date IS NULL) AND (X_effective_start_date IS NULL)))
998 AND ((tlinfo.effective_end_date = x_effective_end_date) OR ((tlinfo.effective_end_date IS NULL) AND (X_effective_end_date IS NULL)))
999 AND ((tlinfo.refund_allowed = x_refund_allowed) OR ((tlinfo.refund_allowed IS NULL) AND (X_refund_allowed IS NULL)))
1000 AND ((tlinfo.payment_priority = x_payment_priority) OR ((tlinfo.payment_priority IS NULL) AND (X_payment_priority IS NULL)))
1001 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1002 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1003 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1004 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1005 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1006 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1007 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1008 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1009 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1010 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1011 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1012 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1013 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1014 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1015 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1016 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1017 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1018 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1019 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1020 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1021 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1022 AND (tlinfo.title4_type_ind = x_title4_type_ind)
1026 AND ((tlinfo.appl_hierarchy_id = x_appl_hierarchy_id) OR ((tlinfo.appl_hierarchy_id IS NULL) AND (x_appl_hierarchy_id IS NULL)))
1023 AND ((tlinfo.payment_credit_type_id = x_payment_credit_type_id) OR ((tlinfo.payment_credit_type_id IS NULL) AND (x_payment_credit_type_id IS NULL)))
1024 AND ((tlinfo.forfeiture_gl_ccid = x_forfeiture_gl_ccid) OR ((tlinfo.forfeiture_gl_ccid IS NULL) AND (x_forfeiture_gl_ccid IS NULL)))
1025 AND ((tlinfo.forfeiture_account_cd = x_forfeiture_account_cd) OR ((tlinfo.forfeiture_account_cd IS NULL) AND (x_forfeiture_account_cd IS NULL)))
1027 ) THEN
1028 NULL;
1029 ELSE
1030 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1031 igs_ge_msg_stack.add;
1032 app_exception.raise_exception;
1033 END IF;
1034
1035 RETURN;
1036
1037 END lock_row;
1038
1039
1040 PROCEDURE update_row (
1041 x_rowid IN VARCHAR2,
1042 x_credit_type_id IN NUMBER,
1043 x_credit_type_name IN VARCHAR2,
1044 x_description IN VARCHAR2,
1045 x_credit_class IN VARCHAR2,
1046 x_dr_account_cd IN VARCHAR2,
1047 x_cr_account_cd IN VARCHAR2,
1048 x_dr_gl_ccid IN NUMBER,
1049 x_cr_gl_ccid IN NUMBER,
1050 x_effective_start_date IN DATE,
1051 x_effective_end_date IN DATE,
1052 x_refund_allowed IN VARCHAR2,
1053 x_payment_priority IN NUMBER,
1054 x_attribute_category IN VARCHAR2,
1055 x_attribute1 IN VARCHAR2,
1056 x_attribute2 IN VARCHAR2,
1057 x_attribute3 IN VARCHAR2,
1058 x_attribute4 IN VARCHAR2,
1059 x_attribute5 IN VARCHAR2,
1060 x_attribute6 IN VARCHAR2,
1061 x_attribute7 IN VARCHAR2,
1062 x_attribute8 IN VARCHAR2,
1063 x_attribute9 IN VARCHAR2,
1064 x_attribute10 IN VARCHAR2,
1065 x_attribute11 IN VARCHAR2,
1066 x_attribute12 IN VARCHAR2,
1067 x_attribute13 IN VARCHAR2,
1068 x_attribute14 IN VARCHAR2,
1069 x_attribute15 IN VARCHAR2,
1070 x_attribute16 IN VARCHAR2,
1071 x_attribute17 IN VARCHAR2,
1072 x_attribute18 IN VARCHAR2,
1073 x_attribute19 IN VARCHAR2,
1074 x_attribute20 IN VARCHAR2,
1075 x_title4_type_ind IN VARCHAR2,
1076 x_mode IN VARCHAR2,
1077 x_payment_credit_type_id IN NUMBER,
1078 x_forfeiture_gl_ccid IN NUMBER,
1079 x_forfeiture_account_cd IN VARCHAR2,
1080 x_appl_hierarchy_id IN NUMBER
1081 ) AS
1082 /*
1083 || Created By : BDEVARAK
1084 || Created On : 26-APR-2001
1085 || Purpose : Handles the UPDATE DML logic for the table.
1086 || Known limitations, enhancements or remarks :
1087 || Change History :
1088 || Who When What
1089 || (reverse chronological order - newest change first)
1090 || sapanigr 20-Sep-2005 Enh#4228665.Added field appl_hierarchy_id.
1091 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
1092 || payment_credit_type_id, forfeiture_gl_ccid
1093 || and forfeiture_account_cd
1094 || vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie., from parameters list
1095 || and from call to before_dml and from update statement.Also removed
1096 || DEFAULT value for x_title4_type_ind,x_mode to avoid gscc warnings.
1097 */
1098 x_last_update_date DATE ;
1099 x_last_updated_by NUMBER;
1100 x_last_update_login NUMBER;
1101
1102 BEGIN
1103
1104 x_last_update_date := SYSDATE;
1105 IF (X_MODE = 'I') THEN
1106 x_last_updated_by := 1;
1107 x_last_update_login := 0;
1108 ELSIF (x_mode = 'R') THEN
1109 x_last_updated_by := fnd_global.user_id;
1110 IF x_last_updated_by IS NULL THEN
1111 x_last_updated_by := -1;
1112 END IF;
1113 x_last_update_login := fnd_global.login_id;
1114 IF (x_last_update_login IS NULL) THEN
1115 x_last_update_login := -1;
1116 END IF;
1117 ELSE
1118 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1119 igs_ge_msg_stack.add;
1120 app_exception.raise_exception;
1121 END IF;
1122
1123 before_dml(
1124 p_action => 'UPDATE',
1125 x_rowid => x_rowid,
1126 x_credit_type_id => x_credit_type_id,
1127 x_credit_type_name => x_credit_type_name,
1128 x_description => x_description,
1132 x_dr_gl_ccid => x_dr_gl_ccid,
1129 x_credit_class => x_credit_class,
1130 x_dr_account_cd => x_dr_account_cd,
1131 x_cr_account_cd => x_cr_account_cd,
1133 x_cr_gl_ccid => x_cr_gl_ccid,
1134 x_effective_start_date => x_effective_start_date,
1135 x_effective_end_date => x_effective_end_date,
1136 x_refund_allowed => x_refund_allowed,
1137 x_payment_priority => x_payment_priority,
1138 x_attribute_category => x_attribute_category,
1139 x_attribute1 => x_attribute1,
1140 x_attribute2 => x_attribute2,
1141 x_attribute3 => x_attribute3,
1142 x_attribute4 => x_attribute4,
1143 x_attribute5 => x_attribute5,
1144 x_attribute6 => x_attribute6,
1145 x_attribute7 => x_attribute7,
1146 x_attribute8 => x_attribute8,
1147 x_attribute9 => x_attribute9,
1148 x_attribute10 => x_attribute10,
1149 x_attribute11 => x_attribute11,
1150 x_attribute12 => x_attribute12,
1151 x_attribute13 => x_attribute13,
1152 x_attribute14 => x_attribute14,
1153 x_attribute15 => x_attribute15,
1154 x_attribute16 => x_attribute16,
1155 x_attribute17 => x_attribute17,
1156 x_attribute18 => x_attribute18,
1157 x_attribute19 => x_attribute19,
1158 x_attribute20 => x_attribute20,
1159 x_title4_type_ind => x_title4_type_ind,
1160 x_creation_date => x_last_update_date,
1161 x_created_by => x_last_updated_by,
1162 x_last_update_date => x_last_update_date,
1163 x_last_updated_by => x_last_updated_by,
1164 x_last_update_login => x_last_update_login,
1165 x_payment_credit_type_id => x_payment_credit_type_id,
1166 x_forfeiture_gl_ccid => x_forfeiture_gl_ccid,
1167 x_forfeiture_account_cd => x_forfeiture_account_cd,
1168 x_appl_hierarchy_id => x_appl_hierarchy_id
1169 );
1170
1171 UPDATE igs_fi_cr_types_all
1172 SET
1173 credit_type_name = new_references.credit_type_name,
1174 description = new_references.description,
1175 credit_class = new_references.credit_class,
1176 dr_account_cd = new_references.dr_account_cd,
1177 cr_account_cd = new_references.cr_account_cd,
1178 dr_gl_ccid = new_references.dr_gl_ccid,
1179 cr_gl_ccid = new_references.cr_gl_ccid,
1180 effective_start_date = new_references.effective_start_date,
1181 effective_end_date = new_references.effective_end_date,
1182 refund_allowed = new_references.refund_allowed,
1186 attribute2 = new_references.attribute2,
1183 payment_priority = new_references.payment_priority,
1184 attribute_category = new_references.attribute_category,
1185 attribute1 = new_references.attribute1,
1187 attribute3 = new_references.attribute3,
1188 attribute4 = new_references.attribute4,
1189 attribute5 = new_references.attribute5,
1190 attribute6 = new_references.attribute6,
1191 attribute7 = new_references.attribute7,
1192 attribute8 = new_references.attribute8,
1193 attribute9 = new_references.attribute9,
1194 attribute10 = new_references.attribute10,
1195 attribute11 = new_references.attribute11,
1196 attribute12 = new_references.attribute12,
1197 attribute13 = new_references.attribute13,
1198 attribute14 = new_references.attribute14,
1199 attribute15 = new_references.attribute15,
1200 attribute16 = new_references.attribute16,
1201 attribute17 = new_references.attribute17,
1202 attribute18 = new_references.attribute18,
1203 attribute19 = new_references.attribute19,
1204 attribute20 = new_references.attribute20,
1205 title4_type_ind = new_references.title4_type_ind,
1206 last_update_date = x_last_update_date,
1207 last_updated_by = x_last_updated_by,
1208 last_update_login = x_last_update_login,
1209 payment_credit_type_id = x_payment_credit_type_id,
1210 forfeiture_gl_ccid = x_forfeiture_gl_ccid,
1211 forfeiture_account_cd = x_forfeiture_account_cd,
1212 appl_hierarchy_id = x_appl_hierarchy_id
1213 WHERE rowid = x_rowid;
1214
1215 IF (SQL%NOTFOUND) THEN
1216 RAISE NO_DATA_FOUND;
1217 END IF;
1218
1219 END update_row;
1220
1221
1222 PROCEDURE add_row (
1223 x_rowid IN OUT NOCOPY VARCHAR2,
1224 x_credit_type_id IN OUT NOCOPY NUMBER,
1225 x_credit_type_name IN VARCHAR2,
1226 x_description IN VARCHAR2,
1227 x_credit_class IN VARCHAR2,
1228 x_dr_account_cd IN VARCHAR2,
1229 x_cr_account_cd IN VARCHAR2,
1230 x_dr_gl_ccid IN NUMBER,
1231 x_cr_gl_ccid IN NUMBER,
1232 x_effective_start_date IN DATE,
1233 x_effective_end_date IN DATE,
1234 x_refund_allowed IN VARCHAR2,
1235 x_payment_priority IN NUMBER,
1236 x_attribute_category IN VARCHAR2,
1237 x_attribute1 IN VARCHAR2,
1238 x_attribute2 IN VARCHAR2,
1239 x_attribute3 IN VARCHAR2,
1240 x_attribute4 IN VARCHAR2,
1241 x_attribute5 IN VARCHAR2,
1242 x_attribute6 IN VARCHAR2,
1243 x_attribute7 IN VARCHAR2,
1244 x_attribute8 IN VARCHAR2,
1245 x_attribute9 IN VARCHAR2,
1246 x_attribute10 IN VARCHAR2,
1247 x_attribute11 IN VARCHAR2,
1248 x_attribute12 IN VARCHAR2,
1249 x_attribute13 IN VARCHAR2,
1250 x_attribute14 IN VARCHAR2,
1251 x_attribute15 IN VARCHAR2,
1252 x_attribute16 IN VARCHAR2,
1253 x_attribute17 IN VARCHAR2,
1254 x_attribute18 IN VARCHAR2,
1255 x_attribute19 IN VARCHAR2,
1256 x_attribute20 IN VARCHAR2,
1257 x_title4_type_ind IN VARCHAR2,
1258 x_mode IN VARCHAR2,
1259 x_payment_credit_type_id IN NUMBER,
1260 x_forfeiture_gl_ccid IN NUMBER,
1261 x_forfeiture_account_cd IN VARCHAR2,
1262 x_appl_hierarchy_id IN NUMBER
1263 ) AS
1264 /*
1265 || Created By : BDEVARAK
1266 || Created On : 26-APR-2001
1267 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1268 || Known limitations, enhancements or remarks :
1269 || Change History :
1270 || Who When What
1271 || (reverse chronological order - newest change first)
1272 || sapanigr 20-Sep-2005 Enh#4228665.Added field appl_hierarchy_id.
1273 || shtatiko 03-Dec-2002 Enh Bug 2584741, Added three new columns
1274 || payment_credit_type_id, forfeiture_gl_ccid
1275 || and forfeiture_account_cd
1276 || vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,from parameters,
1277 || from calls to insert_row and update_row.Also removed DEFAULT value
1278 || for x_title4_type_ind,x_mode in parameter list to avoid gscc warnings.
1279 */
1280 CURSOR c1 IS
1284
1281 SELECT rowid
1282 FROM igs_fi_cr_types_all
1283 WHERE credit_type_id = x_credit_type_id;
1285 BEGIN
1286
1287 OPEN c1;
1288 FETCH c1 INTO x_rowid;
1289 IF (c1%NOTFOUND) THEN
1290 CLOSE c1;
1291
1292 insert_row (
1293 x_rowid,
1294 x_credit_type_id,
1295 x_credit_type_name,
1296 x_description,
1297 x_credit_class,
1298 x_dr_account_cd,
1299 x_cr_account_cd,
1300 x_dr_gl_ccid,
1301 x_cr_gl_ccid,
1302 x_effective_start_date,
1303 x_effective_end_date,
1304 x_refund_allowed,
1305 x_payment_priority,
1306 x_attribute_category,
1307 x_attribute1,
1308 x_attribute2,
1309 x_attribute3,
1310 x_attribute4,
1311 x_attribute5,
1312 x_attribute6,
1313 x_attribute7,
1314 x_attribute8,
1315 x_attribute9,
1316 x_attribute10,
1317 x_attribute11,
1318 x_attribute12,
1319 x_attribute13,
1320 x_attribute14,
1321 x_attribute15,
1322 x_attribute16,
1323 x_attribute17,
1324 x_attribute18,
1325 x_attribute19,
1326 x_attribute20,
1327 x_title4_type_ind,
1328 x_mode ,
1329 x_payment_credit_type_id,
1330 x_forfeiture_gl_ccid,
1331 x_forfeiture_account_cd,
1332 x_appl_hierarchy_id
1333 );
1334 RETURN;
1335 END IF;
1336 CLOSE c1;
1337
1338 update_row (
1339 x_rowid,
1340 x_credit_type_id,
1341 x_credit_type_name,
1342 x_description,
1343 x_credit_class,
1344 x_dr_account_cd,
1345 x_cr_account_cd,
1346 x_dr_gl_ccid,
1347 x_cr_gl_ccid,
1348 x_effective_start_date,
1349 x_effective_end_date,
1350 x_refund_allowed,
1351 x_payment_priority,
1352 x_attribute_category,
1353 x_attribute1,
1354 x_attribute2,
1355 x_attribute3,
1356 x_attribute4,
1357 x_attribute5,
1358 x_attribute6,
1359 x_attribute7,
1360 x_attribute8,
1361 x_attribute9,
1362 x_attribute10,
1363 x_attribute11,
1364 x_attribute12,
1365 x_attribute13,
1366 x_attribute14,
1367 x_attribute15,
1368 x_attribute16,
1369 x_attribute17,
1370 x_attribute18,
1371 x_attribute19,
1372 x_attribute20,
1373 x_title4_type_ind,
1374 x_mode ,
1375 x_payment_credit_type_id,
1376 x_forfeiture_gl_ccid,
1377 x_forfeiture_account_cd,
1378 x_appl_hierarchy_id
1379 );
1380
1381 END add_row;
1382
1383
1384 PROCEDURE delete_row (
1385 x_rowid IN VARCHAR2
1386 ) AS
1387 /*
1388 || Created By : BDEVARAK
1389 || Created On : 26-APR-2001
1390 || Purpose : Handles the DELETE DML logic for the table.
1391 || Known limitations, enhancements or remarks :
1392 || Change History :
1393 || Who When What
1394 || (reverse chronological order - newest change first)
1395 */
1396 BEGIN
1397
1398 before_dml (
1399 p_action => 'DELETE',
1400 x_rowid => x_rowid
1401 );
1402
1403 DELETE FROM igs_fi_cr_types_all
1404 WHERE rowid = x_rowid;
1405
1406 IF (SQL%NOTFOUND) THEN
1407 RAISE NO_DATA_FOUND;
1408 END IF;
1409
1410 END delete_row;
1411
1412 PROCEDURE beforerowdelete AS
1413 ------------------------------------------------------------------
1414 --Created by : Sanil Madathil, Oracle India
1415 --Date created: 12-Jun-2002
1416 --
1417 --Purpose: Only planned Calendar Instances are allowed for deletion
1418 --
1419 --
1420 --Known limitations/enhancements and/or remarks:
1421 --
1422 --Change History:
1423 --Who When What
1424 -------------------------------------------------------------------
1425 BEGIN
1426 -- while inserting records into credits table, an explicit lock was place on the IGS_FI_CR_TYPES table
1427 -- As a result locking contention occured when simultaneous transactions on the credit table were
1428 -- carried out. Hence the row share table lock on the IGS_FI_CR_TYPES table was removed from get_pk_for_validation.
1429 -- Through the forms, deletion of the IGS_FI_CR_TYPES records are not happening. To ensure that none of the
1430 -- other process which manipulates the records of the IGS_FI_CR_TYPES should not delete any of the records present
1431 -- in the table, explicitly the logic has been coded to prevent the deletion of records.
1432 -- done as part of bug 2404523
1433 FND_MESSAGE.SET_NAME('IGS','IGS_FI_DEL_NOT_ALLWD');
1434 IGS_GE_MSG_STACK.ADD;
1435 APP_EXCEPTION.RAISE_EXCEPTION;
1436 END beforerowdelete;
1437
1438 END igs_fi_cr_types_pkg;
[Home] [Help]