[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_CONTROL_PKG
Source
1 PACKAGE BODY igs_fi_control_pkg AS
2 /* $Header: IGSSI78B.pls 120.2 2006/06/19 09:25:06 sapanigr ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_control_all%RowType;
6 new_references igs_fi_control_all%RowType;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_rec_installed IN VARCHAR2 ,
12 x_creation_date IN DATE ,
13 x_created_by IN NUMBER ,
14 x_last_update_date IN DATE ,
15 x_last_updated_by IN NUMBER ,
16 x_last_update_login IN NUMBER ,
17 x_accounting_method IN VARCHAR2 ,
18 x_set_of_books_id IN NUMBER ,
19 x_refund_dr_gl_ccid IN NUMBER ,
20 x_refund_cr_gl_ccid IN NUMBER ,
21 x_refund_dr_account_cd IN VARCHAR2 ,
22 x_refund_cr_account_cd IN VARCHAR2 ,
23 x_refund_dt_alias IN VARCHAR2 ,
24 x_fee_calc_mthd_code IN VARCHAR2 ,
25 x_planned_credits_ind IN VARCHAR2 ,
26 x_rec_gl_ccid IN NUMBER ,
27 x_cash_gl_ccid IN NUMBER ,
28 x_unapp_gl_ccid IN NUMBER ,
29 x_rec_account_cd IN VARCHAR2 ,
30 x_rev_account_cd IN VARCHAR2 ,
31 x_cash_account_cd IN VARCHAR2 ,
32 x_unapp_account_cd IN VARCHAR2,
33 x_conv_process_run_ind IN NUMBER,
34 x_currency_cd IN VARCHAR2 ,
35 x_rfnd_destination IN VARCHAR2,
36 x_ap_org_id IN NUMBER,
37 x_dflt_supplier_site_name IN VARCHAR2,
38 x_manage_accounts IN VARCHAR2,
39 x_acct_conv_flag IN VARCHAR2,
40 x_post_waiver_gl_flag IN VARCHAR2,
41 x_waiver_notify_finaid_flag IN VARCHAR2
42
43 ) AS
44 /*************************************************************
45 Created By :
46 Date Created By :
47 Purpose :
48 Know limitations, enhancements or remarks
49 Change History
50 Who When What
51 (reverse chronological order - newest change first)
52 svuppala 14-JUL-2005 Enh 3392095 - Tution Waivers build
53 Modified by adding two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
54 uudayapr 07-dec-2003 Enh#3167098 Modified the procedures BY removing the reference to prg_chg_dt_alias
55 and adding res_dt_alias.
56 shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
57 and ar_int_org_id.
58 vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build. Added column acct_conv_flag.
59 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
60 Removed references to columns interface_line_context, interface_line_attribute, term_id,
61 batch_source_id, cust_trx_type_id. Added column manage_accounts
62 smadathi 18-Feb-2002 Enh. Bug 2747329.Added new columns rfnd_destination, ap_org_id, dflt_supplier_site_name
63 smadathi 01-Nov-2002 Enh Bug 2584986. Added new column currency_cd
64 pathipat 02-OCT-2002 Added column x_conv_process_run_ind, part of Enh Bug: 2562745
65 smvk 24-Sep-2002 Added the columns rec_gl_ccid, cash_gl_ccid, unapp_gl_ccid,
66 rec_account_cd, rev_account_cd, cash_account_cd and
67 unapp_account_cd. As a part of Bug # 2564643.
68 vchappid 01-Apr-2002 Bug# 2293676, Added column planned_credits_ind
69 smvk 13-Mar-2002 Added four columns refund_cr_gl_ccid, refund_dr_gl_ccid
70 refund_dr_account_cd, refund_cr_account_cd, refund_dt_alias and
71 removed last_account_trans, last_payment_trans and
72 last_pay_term_trans w.r.t. Bug # 2144600
73 vvutukur 13-02-2002 Added a new column, ar_int_org_id for bug 2222272
74 msrinivi 17 Jul, 2001 Added a new column, set_of_books_id
75 ***************************************************************/
76 CURSOR cur_old_ref_values IS
77 SELECT *
78 FROM IGS_FI_CONTROL_ALL
79 WHERE rowid = x_rowid;
80 BEGIN
81 l_rowid := x_rowid;
82 -- Code for setting the Old and New Reference Values.
83 -- Populate Old Values.
84 OPEN cur_old_ref_values;
85 FETCH cur_old_ref_values INTO old_references;
86 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
87 CLOSE cur_old_ref_values;
88 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
89 igs_ge_msg_stack.add;
90 app_exception.raise_exception;
91 RETURN;
92 END IF;
93 Close cur_old_ref_values;
94 -- Populate New Values.
95 new_references.rec_installed := x_rec_installed;
96 new_references.accounting_method := x_accounting_method;
97 new_references.set_of_books_id := x_set_of_books_id;
98 new_references.refund_dr_gl_ccid :=x_refund_dr_gl_ccid ;
99 new_references.refund_cr_gl_ccid :=x_refund_cr_gl_ccid ;
100 new_references.refund_dr_account_cd := x_refund_dr_account_cd ;
101 new_references.refund_cr_account_cd :=x_refund_cr_account_cd ;
102 new_references.refund_dt_alias :=x_refund_dt_alias;
103 new_references.fee_calc_mthd_code :=x_fee_calc_mthd_code;
104 new_references.planned_credits_ind := x_planned_credits_ind;
105 new_references.rec_gl_ccid := x_rec_gl_ccid;
106 new_references.cash_gl_ccid := x_cash_gl_ccid;
107 new_references.unapp_gl_ccid := x_unapp_gl_ccid;
108 new_references.rec_account_cd := x_rec_account_cd;
109 new_references.rev_account_cd := x_rev_account_cd;
110 new_references.cash_account_cd := x_cash_account_cd;
111 new_references.unapp_account_cd:= x_unapp_account_cd;
112 new_references.conv_process_run_ind := x_conv_process_run_ind;
113 new_references.currency_cd := x_currency_cd;
114 new_references.rfnd_destination := x_rfnd_destination;
115 new_references.ap_org_id := x_ap_org_id;
116 new_references.dflt_supplier_site_name := x_dflt_supplier_site_name;
117 new_references.manage_accounts := x_manage_accounts;
118 new_references.acct_conv_flag := x_acct_conv_flag;
119 new_references.post_waiver_gl_flag := x_post_waiver_gl_flag;
120 new_references.waiver_notify_finaid_flag := x_waiver_notify_finaid_flag;
121
122 IF (p_action = 'UPDATE') THEN
123 new_references.creation_date := old_references.creation_date;
124 new_references.created_by := old_references.created_by;
125 ELSE
126 new_references.creation_date := x_creation_date;
127 new_references.created_by := x_created_by;
128 END IF;
129 new_references.last_update_date := x_last_update_date;
130 new_references.last_updated_by := x_last_updated_by;
131 new_references.last_update_login := x_last_update_login;
132 END set_column_values;
133
134
135 PROCEDURE check_constraints (
136 Column_Name IN VARCHAR2 ,
137 Column_Value IN VARCHAR2 ) AS
138 /*************************************************************
139 Created By :
140 Date Created By :
141 Purpose :
142 Know limitations, enhancements or remarks
143 Change History
144 Who When What
145 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
146 Added constraint for new column manage_accounts
147 smadathi 01-Nov-2002 Enh Bug 2584986. Added check constraint for new column currency_cd
148 vchappid 01-Apr-2002 Bug# 2293676, Added check constraint for the Planned Credits Ind column
149 smvk 13-Mar-2002 Added check for refund_dt_alias as one time setup field
150 sarakshi 09-Jan-2002 replaced igs_fi_gen_005.finp_get_receivables_inst
151 by new_references.accounting method in the line where comparing with 'N'
152 agairola 09-May-01 Added the check for
153 accounting method(not null)
154 (reverse chronological order - newest change first)
155 ***************************************************************/
156
157 BEGIN
158 IF column_name IS NULL THEN
159 NULL;
160 ELSIF UPPER(Column_Name) = 'ACCOUNTING_METHOD' THEN
161 new_references.accounting_method := column_value;
162 ELSIF UPPER(Column_Name) = 'CURRENCY_CD' THEN
163 new_references.currency_cd := column_value;
164 END IF;
165
166 -- Added the check for accounting method not null
167 IF UPPER(column_name) = 'ACCOUNTING_METHOD' or column_name is NULL THEN
168 IF new_references.accounting_method IS NULL AND
169 new_references.accounting_method <> 'N' THEN
170 fnd_message.set_name('IGS',
171 'IGS_FI_ACCNT_MTHD_NULL_NA');
172 igs_ge_msg_stack.add;
173 app_exception.raise_exception;
174 END IF;
175 END IF;
176
177 -- Added the check for refund_dt_alias as a one time initialisation field as Per Enh Bug # 2144600
178 IF ((NOT old_references.refund_dt_alias IS NULL) AND
179 (old_references.refund_dt_alias <> new_references.refund_dt_alias )) THEN
180 fnd_message.set_name('IGS','IGS_FI_REFUND_DT_ONE_TIME_SET');
181 igs_ge_msg_stack.add;
182 app_exception.raise_exception;
183 END IF;
184
185 -- Added the check for planned_credits_ind not null
186 IF (( new_references.planned_credits_ind IS NOT NULL) AND ( new_references.planned_credits_ind NOT IN ('Y','N'))) THEN
187 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
188 igs_ge_msg_stack.add;
189 app_exception.raise_exception;
190 END IF;
191
192
193 IF (new_references.currency_cd IS NULL)
194 THEN
195 fnd_message.set_name('IGS','IGS_PE_DATA_MANDATORY');
196 fnd_message.set_token('DATA_ELEMENT','CURRENCY_CD');
197 igs_ge_msg_stack.add;
198 app_exception.raise_exception;
199 END IF;
200
201 IF ((new_references.manage_accounts IS NOT NULL) AND (new_references.manage_accounts NOT IN ('STUDENT_FINANCE','OTHER'))
202 ) THEN
203 fnd_message.set_name('IGS','IGS_GE_INVALID_VALUE');
204 igs_ge_msg_stack.add;
205 app_exception.raise_exception;
206 END IF;
207
208 END check_constraints;
209
210 PROCEDURE check_parent_existance AS
211 /*************************************************************
212 Created By :
213 Date Created By :
214 Purpose :
215 Know limitations, enhancements or remarks
216 Change History
217 Who When What
218 uudayapr 07-dec-2004 Enh 3167098 removed the reference prg_chg_dt_alias and added res_dt_alias
219 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
220 Removed references to term_id, batch_source_id, cust_trx_type_id.
221 smadathi 18-Feb-2002 Enh. Bug 2747329.Added check parent existance for new column rfnd_destination
222 smadathi 01-Nov-2002 Enh Bug 2584986. Added check parent existance for new column currency_cd
223 smvk 24-Sep-2002 Added the check for rec_account_cd, rev_account_cd, cash_account_cd
224 and unapp_account_cd. As a part of Bug # 2564643.
225 smvk 13-Mar-2002 Added to check for refund_cr_account_cd, refund_dr_account_cd and
226 refund_dt_alias w.r.t Bug # 2144600
227 (reverse chronological order - newest change first)
228 ***************************************************************/
229 CURSOR cur_rowid4 IS
230 SELECT rowid
231 FROM fnd_currencies
232 WHERE currency_code = new_references.currency_cd;
233
234 l_rowid4 cur_rowid4%ROWTYPE;
235
236 BEGIN
237
238 IF (((old_references.accounting_method = new_references.accounting_method)) OR
239 ((new_references.accounting_method IS NULL))) THEN
240 NULL;
241 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation(
242 'IGS_FI_ACCT_METHOD',
243 new_references.accounting_method
244 )THEN
245 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
246 igs_ge_msg_stack.add;
247 app_exception.raise_exception;
248 END IF;
249
250 --Added by sarakshi,bug: 2162747
251 IF (((old_references.fee_calc_mthd_code = new_references.fee_calc_mthd_code) OR
252 (new_references.fee_calc_mthd_code IS NULL))) THEN
253 NULL;
254 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation (
255 'IGS_FI_FEE_CALC_MTHD',
256 new_references.fee_calc_mthd_code
257 ) THEN
258 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
259 igs_ge_msg_stack.add;
260 app_exception.raise_exception;
261 END IF;
262
263 -- Added for Refunds Build as per the Enhancement Bug # 2144600
264 IF (((old_references.refund_dr_account_cd = new_references.refund_dr_account_cd) OR
265 (new_references.refund_dr_account_cd IS NULL))) THEN
266 NULL;
267 ELSE
268 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.refund_dr_account_cd)) THEN
269 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
270 igs_ge_msg_stack.add;
271 app_exception.raise_exception;
272 END IF;
273 END IF;
274
275 IF (((old_references.refund_cr_account_cd= new_references.refund_cr_account_cd) OR
276 (new_references.refund_cr_account_cd IS NULL))) THEN
277 NULL;
278 ELSE
279 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.refund_cr_account_cd)) THEN
280 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
281 igs_ge_msg_stack.add;
282 app_exception.raise_exception;
283 END IF;
284 END IF;
285
286 IF ((old_references.refund_dt_alias = new_references.refund_dt_alias) OR
287 (new_references.refund_dt_alias IS NULL)) THEN
288 NULL;
289 ELSE
290 IF NOT igs_ca_da_pkg.get_pk_for_validation (
291 new_references.refund_dt_alias) THEN
292 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
293 igs_ge_msg_stack.add;
294 app_exception.raise_exception;
295 END IF;
296 END IF;
297
298 -- Added as a part of Subaccount Removal build. Enh Bug # 2564643
299 IF ((old_references.rec_account_cd = new_references.rec_account_cd) OR
300 (new_references.rec_account_cd IS NULL)) THEN
301 NULL;
302 ELSE
303 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.rec_account_cd)) THEN
304 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
305 igs_ge_msg_stack.add;
306 app_exception.raise_exception;
307 END IF;
308 END IF;
309
310 IF ((old_references.rev_account_cd = new_references.rev_account_cd) OR
311 (new_references.rev_account_cd IS NULL)) THEN
312 NULL;
313 ELSE
314 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.rev_account_cd)) THEN
315 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
316 igs_ge_msg_stack.add;
317 app_exception.raise_exception;
318 END IF;
319 END IF;
320
321 IF ((old_references.cash_account_cd = new_references.cash_account_cd) OR
322 (new_references.cash_account_cd IS NULL)) THEN
323 NULL;
324 ELSE
325 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.cash_account_cd)) THEN
326 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 END IF;
330 END IF;
331
332 IF ((old_references.unapp_account_cd = new_references.unapp_account_cd) OR
333 (new_references.unapp_account_cd IS NULL)) THEN
334 NULL;
335 ELSE
336 IF NOT(igs_fi_acc_pkg.get_pk_for_validation(new_references.unapp_account_cd)) THEN
337 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
338 igs_ge_msg_stack.add;
339 app_exception.raise_exception;
340 END IF;
341 END IF;
342
343 IF (((old_references.currency_cd = new_references.currency_cd)) OR
344 ((new_references.currency_cd IS NULL))) THEN
345 NULL;
346 ELSE
347 OPEN cur_rowid4;
348 FETCH cur_rowid4 INTO l_rowid4;
349 IF (cur_rowid4%NOTFOUND) THEN
350 CLOSE cur_rowid4;
351 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
352 igs_ge_msg_stack.add;
353 app_exception.raise_exception;
354 END IF;
355 CLOSE cur_rowid4;
356 END IF;
357
358 IF (((old_references.rfnd_destination = new_references.rfnd_destination)) OR
359 ((new_references.rfnd_destination IS NULL))) THEN
360 NULL;
361 ELSIF NOT igs_lookups_view_pkg.get_pk_for_validation(
362 'IGS_FI_REFUND_DESTINATION',
363 new_references.rfnd_destination
364 )THEN
365 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
366 igs_ge_msg_stack.add;
367 app_exception.raise_exception;
368 END IF;
369
370 END check_parent_existance;
371
372 FUNCTION get_pk_for_validation (
373 x_rec_installed IN VARCHAR2
374 ) RETURN BOOLEAN AS
375 /*************************************************************
376 Created By :
377 Date Created By :
378 Purpose :
379 Know limitations, enhancements or remarks
380 Change History
381 Who When What
382 (reverse chronological order - newest change first)
383 ***************************************************************/
384 CURSOR cur_rowid IS
385 SELECT row_id
386 FROM igs_fi_control
387 WHERE rec_installed = x_rec_installed
388 FOR UPDATE NOWAIT;
389
390 lv_rowid cur_rowid%ROWTYPE;
391
392 BEGIN
393 OPEN cur_rowid;
394 FETCH cur_rowid INTO lv_rowid;
395 IF (cur_rowid%FOUND) THEN
396 CLOSE cur_rowid;
397 RETURN(TRUE);
398 ELSE
399 CLOSE cur_rowid;
400 RETURN(FALSE);
401 END IF;
402 END get_pk_for_validation;
403
404
405 PROCEDURE before_dml (
406 p_action IN VARCHAR2,
407 x_rowid IN VARCHAR2 ,
408 x_rec_installed IN VARCHAR2 ,
409 x_creation_date IN DATE ,
410 x_created_by IN NUMBER ,
411 x_last_update_date IN DATE ,
412 x_last_updated_by IN NUMBER ,
413 x_last_update_login IN NUMBER ,
414 x_accounting_method IN VARCHAR2 ,
415 x_set_of_books_id IN NUMBER ,
416 x_refund_dr_gl_ccid IN NUMBER ,
417 x_refund_cr_gl_ccid IN NUMBER ,
418 x_refund_dr_account_cd IN VARCHAR2 ,
419 x_refund_cr_account_cd IN VARCHAR2 ,
420 x_refund_dt_alias IN VARCHAR2 ,
421 x_fee_calc_mthd_code IN VARCHAR2 ,
422 x_planned_credits_ind IN VARCHAR2 ,
423 x_rec_gl_ccid IN NUMBER ,
424 x_cash_gl_ccid IN NUMBER ,
425 x_unapp_gl_ccid IN NUMBER ,
426 x_rec_account_cd IN VARCHAR2 ,
427 x_rev_account_cd IN VARCHAR2 ,
428 x_cash_account_cd IN VARCHAR2 ,
429 x_unapp_account_cd IN VARCHAR2,
430 x_conv_process_run_ind IN NUMBER,
431 x_currency_cd IN VARCHAR2 ,
432 x_rfnd_destination IN VARCHAR2,
433 x_ap_org_id IN NUMBER,
434 x_dflt_supplier_site_name IN VARCHAR2,
435 x_manage_accounts IN VARCHAR2,
436 x_acct_conv_flag IN VARCHAR2,
437 x_post_waiver_gl_flag IN VARCHAR2,
438 x_waiver_notify_finaid_flag IN VARCHAR2
439 ) AS
440 /*************************************************************
441 Created By :
442 Date Created By :
443 Purpose :
444 Know limitations, enhancements or remarks
445 Change History
446 Who When What
447 (reverse chronological order - newest change first)
448 svuppala 14-JUL-2005 Enh 3392095 - Tution Waivers build
449 Modified by adding two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
450 uudayapr 07-dec-2003 Enh#3167098 Modified the procedures BY removing the reference to prg_chg_dt_alias
451 and adding res_dt_alias.
452 shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
453 and ar_int_org_id.
454 vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build. Added column acct_conv_flag.
455 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
456 Removed references to columns interface_line_context, interface_line_attribute, term_id,
457 batch_source_id, cust_trx_type_id. Added column manage_accounts
458 smadathi 18-Feb-2002 Enh. Bug 2747329.Added new columns rfnd_destination, ap_org_id, dflt_supplier_site_name
459 smadathi 01-Nov-2002 Enh Bug 2584986. Added new column currency_cd
460 pathipat 02-OCT-2002 Added column x_conv_process_run_ind, part of Enh Bug: 2562745
461 smvk 24-Sep-2002 Added the columns rec_gl_ccid, cash_gl_ccid, unapp_gl_ccid,
462 rec_account_cd, rev_account_cd, cash_account_cd and
463 unapp_account_cd. As a part of Bug # 2564643.
464 vchappid 01-Apr-2002 Bug# 2293676, Added column planned_credits_ind
465 smvk 13-Mar-2002 Added four columns refund_cr_gl_ccid, refund_dr_gl_ccid
466 refund_dr_account_cd, refund_cr_account_cd, refund_dt_alias and
467 removed last_account_trans, last_payment_trans and
468 last_pay_term_trans w.r.t. Bug # 2144600
469 vvutukur 13-02-2002 Added a new column, ar_int_org_id for bug 2222272
470 msrinivi 17 Jul,2001 Added 1 new column, set_of_books_id
471 ***************************************************************/
472 BEGIN
473 set_column_values (
474 p_action,
475 x_rowid,
476 x_rec_installed,
477 x_creation_date,
478 x_created_by,
479 x_last_update_date,
480 x_last_updated_by,
481 x_last_update_login,
482 x_accounting_method,
483 x_set_of_books_id,
484 x_refund_dr_gl_ccid,
485 x_refund_cr_gl_ccid,
486 x_refund_dr_account_cd,
487 x_refund_cr_account_cd,
488 x_refund_dt_alias,
489 x_fee_calc_mthd_code ,
490 x_planned_credits_ind,
491 x_rec_gl_ccid,
492 x_cash_gl_ccid,
493 x_unapp_gl_ccid ,
494 x_rec_account_cd,
495 x_rev_account_cd,
496 x_cash_account_cd,
497 x_unapp_account_cd,
498 x_conv_process_run_ind,
499 x_currency_cd,
500 x_rfnd_destination,
501 x_ap_org_id,
502 x_dflt_supplier_site_name,
503 x_manage_accounts,
504 x_acct_conv_flag,
505 x_post_waiver_gl_flag,
506 x_waiver_notify_finaid_flag
507 );
508 IF (p_action = 'INSERT') THEN
509 -- Call all the procedures related to Before Insert.
510 IF get_pk_for_validation(
511 new_references.rec_installed) THEN
512 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
513 igs_ge_msg_stack.add;
514 app_exception.raise_exception;
515 END IF;
516 check_constraints;
517 check_parent_existance;
518
519 ELSIF (p_action = 'UPDATE') THEN
520 -- Call all the procedures related to Before Update.
521 check_constraints;
522 check_parent_existance;
523 ELSIF (p_action = 'DELETE') THEN
524 -- Call all the procedures related to Before Delete.
525 Null;
526 ELSIF (p_action = 'VALIDATE_INSERT') THEN
527 -- Call all the procedures related to Before Insert.
528 IF get_pk_for_validation (
529 new_references.rec_installed) THEN
530 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
531 igs_ge_msg_stack.add;
532 app_exception.raise_exception;
533 END IF;
534 check_constraints;
535 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
536 check_constraints;
537 ELSIF (p_action = 'VALIDATE_DELETE') THEN
538 NULL;
539 END IF;
540 END before_dml;
541
542
543 PROCEDURE after_dml (
544 p_action IN VARCHAR2,
545 x_rowid IN VARCHAR2
546 ) AS
547 /*************************************************************
548 Created By :
549 Date Created By :
550 Purpose :
551 Know limitations, enhancements or remarks
552 Change History
553 Who When What
554 (reverse chronological order - newest change first)
555 ***************************************************************/
556 BEGIN
557 l_rowid := x_rowid;
558 IF (p_action = 'INSERT') THEN
559 -- Call all the procedures related to After Insert.
560 NULL;
561 ELSIF (p_action = 'UPDATE') THEN
562 -- Call all the procedures related to After Update.
563 NULL;
564 ELSIF (p_action = 'DELETE') THEN
565 -- Call all the procedures related to After Delete.
566 NULL;
567 END IF;
568 END after_dml;
569
570
571 PROCEDURE insert_row (
572 x_rowid IN OUT NOCOPY VARCHAR2,
573 x_rec_installed IN VARCHAR2,
574 x_mode IN VARCHAR2 ,
575 x_accounting_method IN VARCHAR2,
576 x_set_of_books_id IN NUMBER ,
577 x_refund_dr_gl_ccid IN NUMBER ,
578 x_refund_cr_gl_ccid IN NUMBER ,
579 x_refund_dr_account_cd IN VARCHAR2 ,
580 x_refund_cr_account_cd IN VARCHAR2 ,
581 x_refund_dt_alias IN VARCHAR2 ,
582 x_fee_calc_mthd_code IN VARCHAR2 ,
583 x_planned_credits_ind IN VARCHAR2 ,
584 x_rec_gl_ccid IN NUMBER ,
585 x_cash_gl_ccid IN NUMBER ,
586 x_unapp_gl_ccid IN NUMBER ,
587 x_rec_account_cd IN VARCHAR2 ,
588 x_rev_account_cd IN VARCHAR2 ,
589 x_cash_account_cd IN VARCHAR2 ,
590 x_unapp_account_cd IN VARCHAR2,
591 x_conv_process_run_ind IN NUMBER,
592 x_currency_cd IN VARCHAR2,
593 x_rfnd_destination IN VARCHAR2,
594 x_ap_org_id IN NUMBER,
595 x_dflt_supplier_site_name IN VARCHAR2,
596 x_manage_accounts IN VARCHAR2,
597 x_acct_conv_flag IN VARCHAR2,
598 x_post_waiver_gl_flag IN VARCHAR2,
599 x_waiver_notify_finaid_flag IN VARCHAR2
600 ) AS
601 /*************************************************************
602 Created By :
603 Date Created By :
604 Purpose :
605 Know limitations, enhancements or remarks
606 Change History
607 Who When What
608 (reverse chronological order - newest change first)
609 svuppala 14-JUL-2005 Enh 3392095 - Tution Waivers build
610 Modified by adding two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
611 uudayapr 07-dec-2003 Enh#3167098 Modified the procedures BY removing the reference to prg_chg_dt_alias
612 and adding res_dt_alias.
613 shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
614 and ar_int_org_id.
615 vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build. Added column acct_conv_flag.
616 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
617 Removed references to columns interface_line_context, interface_line_attribute, term_id,
618 batch_source_id, cust_trx_type_id. Added column manage_accounts
619 smadathi 18-Feb-2002 Enh. Bug 2747329.Added new columns rfnd_destination, ap_org_id, dflt_supplier_site_name
620 smadathi 01-Nov-2002 Enh Bug 2584986. Added new column currency_cd
621 pathipat 02-OCT-2002 Added column x_conv_process_run_ind, part of Enh Bug: 2562745
622 smvk 24-Sep-2002 Added the columns rec_gl_ccid, cash_gl_ccid, unapp_gl_ccid,
623 rec_account_cd, rev_account_cd, cash_account_cd and
624 unapp_account_cd. As a part of Bug # 2564643.
625 vchappid 01-Apr-2002 Bug# 2293676, Added column planned_credits_ind
626 smvk 13-Mar-2002 Added four columns refund_cr_gl_ccid, refund_dr_gl_ccid
627 refund_dr_account_cd, refund_cr_account_cd, refund_dt_alias and
628 removed last_account_trans, last_payment_trans and
629 last_pay_term_trans w.r.t. Bug # 2144600
630 vvutukur 13-02-2002 Added a new column, ar_int_org_id for bug 2222272
631 msrinivi 17 Jul,2001 Added a new column, set_of_books_id
632 ***************************************************************/
633 CURSOR c IS
634 SELECT ROWID
635 FROM igs_fi_control
636 WHERE rec_installed= x_rec_installed;
637
638 x_last_update_date DATE ;
639 x_last_updated_by NUMBER ;
640 x_last_update_login NUMBER ;
641
642 BEGIN
643
644 x_last_update_date := SYSDATE;
645
646 IF (x_mode = 'I') THEN
647
648 x_last_updated_by := 1;
649 x_last_update_login := 0;
650
651 ELSIF (x_mode = 'R') THEN
652
653 x_last_updated_by := FND_GLOBAL.USER_ID;
654
655 IF x_last_updated_by IS NULL THEN
656 x_last_updated_by := -1;
657 END IF;
658
659 x_last_update_login :=FND_GLOBAL.LOGIN_ID;
660
661 IF x_last_update_login IS NULL THEN
662 x_last_update_login := -1;
663 END IF;
664 ELSE
665 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
666 igs_ge_msg_stack.add;
667 app_exception.raise_exception;
668 END IF;
669
670 --msrinivi : since this table has now been made a multi org, the following is added
671 new_references.org_id := igs_ge_gen_003.get_org_id;
672
673 before_dml(
674 p_action =>'INSERT',
675 x_rowid => x_rowid,
676 x_rec_installed => x_rec_installed,
677 x_creation_date => x_last_update_date,
678 x_created_by => x_last_updated_by,
679 x_last_update_date => x_last_update_date,
680 x_last_updated_by => x_last_updated_by,
681 x_last_update_login => x_last_update_login,
682 x_accounting_method => x_accounting_method,
683 x_set_of_books_id => x_set_of_books_id,
684 x_refund_dr_gl_ccid => x_refund_dr_gl_ccid,
685 x_refund_cr_gl_ccid => x_refund_cr_gl_ccid,
686 x_refund_dr_account_cd => x_refund_dr_account_cd,
687 x_refund_cr_account_cd => x_refund_cr_account_cd,
688 x_refund_dt_alias => x_refund_dt_alias,
689 x_fee_calc_mthd_code => x_fee_calc_mthd_code ,
690 x_planned_credits_ind => x_planned_credits_ind ,
691 x_rec_gl_ccid => x_rec_gl_ccid,
692 x_cash_gl_ccid => x_cash_gl_ccid,
693 x_unapp_gl_ccid => x_unapp_gl_ccid ,
694 x_rec_account_cd => x_rec_account_cd,
695 x_rev_account_cd => x_rev_account_cd,
696 x_cash_account_cd => x_cash_account_cd,
697 x_unapp_account_cd => x_unapp_account_cd,
698 x_conv_process_run_ind => x_conv_process_run_ind,
699 x_currency_cd => x_currency_cd,
700 x_rfnd_destination => x_rfnd_destination,
701 x_ap_org_id => x_ap_org_id,
702 x_dflt_supplier_site_name => x_dflt_supplier_site_name,
703 x_manage_accounts => x_manage_accounts,
704 x_acct_conv_flag => x_acct_conv_flag,
705 x_post_waiver_gl_flag => x_post_waiver_gl_flag,
706 x_waiver_notify_finaid_flag => x_waiver_notify_finaid_flag
707 );
708
709 INSERT INTO igs_fi_control_all (
710 rec_installed,
711 creation_date,
712 created_by,
713 last_update_date,
714 last_updated_by,
715 last_update_login,
716 accounting_method,
717 set_of_books_id,
718 org_id,
719 refund_dr_gl_ccid,
720 refund_cr_gl_ccid,
721 refund_dr_account_cd,
722 refund_cr_account_cd,
723 refund_dt_alias,
724 fee_calc_mthd_code,
725 planned_credits_ind,
726 rec_gl_ccid,
727 cash_gl_ccid,
728 unapp_gl_ccid,
729 rec_account_cd,
730 rev_account_cd,
731 cash_account_cd,
732 unapp_account_cd,
733 conv_process_run_ind,
734 currency_cd,
735 rfnd_destination,
736 ap_org_id,
737 dflt_supplier_site_name,
738 manage_accounts,
739 acct_conv_flag,
740 post_waiver_gl_flag,
741 waiver_notify_finaid_flag
742 )
743 VALUES (
744 new_references.rec_installed,
745 x_last_update_date,
746 x_last_updated_by,
747 x_last_update_date,
748 x_last_updated_by,
749 x_last_update_login,
750 x_accounting_method,
751 new_references.set_of_books_id,
752 new_references.org_id,
753 new_references.refund_dr_gl_ccid,
754 new_references.refund_cr_gl_ccid,
755 new_references.refund_dr_account_cd,
756 new_references.refund_cr_account_cd,
757 new_references.refund_dt_alias,
758 new_references.fee_calc_mthd_code,
759 new_references.planned_credits_ind,
760 new_references.rec_gl_ccid,
761 new_references.cash_gl_ccid,
762 new_references.unapp_gl_ccid,
763 new_references.rec_account_cd,
764 new_references.rev_account_cd,
765 new_references.cash_account_cd,
766 new_references.unapp_account_cd,
767 new_references.conv_process_run_ind,
768 new_references.currency_cd,
769 new_references.rfnd_destination,
770 new_references.ap_org_id,
771 new_references.dflt_supplier_site_name,
772 new_references.manage_accounts,
773 new_references.acct_conv_flag,
774 new_references.post_waiver_gl_flag,
775 new_references.waiver_notify_finaid_flag
776 );
777
778 OPEN c;
779 FETCH c INTO X_ROWID;
780 IF (c%NOTFOUND) THEN
781 CLOSE c;
782 RAISE NO_DATA_FOUND;
783 END IF;
784 CLOSE c;
785
786 After_DML(
787 p_action => 'INSERT' ,
788 x_rowid => X_ROWID );
789
790 END insert_row;
791
792
793 PROCEDURE lock_row (
794 x_rowid IN VARCHAR2,
795 x_rec_installed IN VARCHAR2,
796 x_accounting_method IN VARCHAR2,
797 x_set_of_books_id IN NUMBER ,
798 x_refund_dr_gl_ccid IN NUMBER ,
799 x_refund_cr_gl_ccid IN NUMBER ,
800 x_refund_dr_account_cd IN VARCHAR2 ,
801 x_refund_cr_account_cd IN VARCHAR2 ,
802 x_refund_dt_alias IN VARCHAR2 ,
803 x_fee_calc_mthd_code IN VARCHAR2 ,
804 x_planned_credits_ind IN VARCHAR2 ,
805 x_rec_gl_ccid IN NUMBER ,
806 x_cash_gl_ccid IN NUMBER ,
807 x_unapp_gl_ccid IN NUMBER ,
808 x_rec_account_cd IN VARCHAR2 ,
809 x_rev_account_cd IN VARCHAR2 ,
810 x_cash_account_cd IN VARCHAR2 ,
811 x_unapp_account_cd IN VARCHAR2,
812 x_conv_process_run_ind IN NUMBER,
813 x_currency_cd IN VARCHAR2,
814 x_rfnd_destination IN VARCHAR2,
815 x_ap_org_id IN NUMBER,
816 x_dflt_supplier_site_name IN VARCHAR2,
817 x_manage_accounts IN VARCHAR2,
818 x_acct_conv_flag IN VARCHAR2,
819 x_post_waiver_gl_flag IN VARCHAR2,
820 x_waiver_notify_finaid_flag IN VARCHAR2
821 ) AS
822 /*************************************************************
823 Created By :
824 Date Created By :
825 Purpose :
826 Know limitations, enhancements or remarks
827 Change History
828 Who When What
829 (reverse chronological order - newest change first)
830 svuppala 14-JUL-2005 Enh 3392095 - Tution Waivers build
831 Modified by adding two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
832 uudayapr 07-dec-2003 Enh#3167098 Modified the procedures BY removing the reference to prg_chg_dt_alias
833 and adding res_dt_alias.
834 shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
835 and ar_int_org_id.
836 vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build. Added column acct_conv_flag.
837 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
838 Removed references to columns interface_line_context, interface_line_attribute, term_id,
839 batch_source_id, cust_trx_type_id. Added column manage_accounts
840 smadathi 18-Feb-2002 Enh. Bug 2747329.Added new columns rfnd_destination, ap_org_id, dflt_supplier_site_name
841 smadathi 01-Nov-2002 Enh Bug 2584986. Added new column currency_cd
842 pathipat 02-OCT-2002 Added column x_conv_process_run_ind, part of Enh Bug: 2562745
843 smvk 24-Sep-2002 Added the columns rec_gl_ccid, cash_gl_ccid, unapp_gl_ccid,
844 rec_account_cd, rev_account_cd, cash_account_cd and
845 unapp_account_cd. As a part of Bug # 2564643.
846 vchappid 01-Apr-2002 Bug# 2293676, Added column planned_credits_ind
847 smvk 13-Mar-2002 Added four columns refund_cr_gl_ccid, refund_dr_gl_ccid
848 refund_dr_account_cd, refund_cr_account_cd, refund_dt_alias and
849 removed last_account_trans, last_payment_trans and
850 last_pay_term_trans w.r.t. Bug # 2144600
851 vvutukur 13-02-2002 Added a new column, ar_int_org_id for bug 2222272
852 msrinivi 17 Jul,2001 Added 1 new column, set_of_books_id
853 vvutukur 04 Jan 2002 Added logic for when accounting method
854 is null in IF condition
855 ***************************************************************/
856 CURSOR c1 IS
857 SELECT accounting_method,
858 set_of_books_id,
859 refund_dr_gl_ccid ,
860 refund_cr_gl_ccid,
861 refund_dr_account_cd ,
862 refund_cr_account_cd,
863 refund_dt_alias ,
864 fee_calc_mthd_code,
865 planned_credits_ind,
866 rec_gl_ccid,
867 cash_gl_ccid,
868 unapp_gl_ccid,
869 rec_account_cd,
870 rev_account_cd,
871 cash_account_cd,
872 unapp_account_cd,
873 conv_process_run_ind,
874 currency_cd,
875 rfnd_destination,
876 ap_org_id,
877 dflt_supplier_site_name,
878 manage_accounts,
879 acct_conv_flag,
880 post_waiver_gl_flag,
881 waiver_notify_finaid_flag
882 FROM igs_fi_control_all
883 WHERE rowid = x_rowid
884 FOR UPDATE NOWAIT;
885
886 tlinfo c1%ROWTYPE;
887
888 BEGIN
889 OPEN c1;
890 FETCH c1 INTO tlinfo;
891 IF (c1%NOTFOUND) THEN
892 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
893 igs_ge_msg_stack.add;
894 CLOSE c1;
895 app_exception.raise_exception;
896 RETURN;
897 END IF;
898 CLOSE c1;
899
900 IF (((tlinfo.accounting_method = x_accounting_method) OR
901 (tlinfo.accounting_method IS NULL OR x_accounting_method IS NULL))
902 AND ((tlinfo.set_of_books_id = x_set_of_books_id) OR
903 (tlinfo.set_of_books_id IS NULL AND x_set_of_books_id IS NULL))
904 AND ((tlinfo.refund_dr_gl_ccid = x_refund_dr_gl_ccid ) OR
905 (tlinfo.refund_dr_gl_ccid IS NULL AND x_refund_dr_gl_ccid IS NULL))
906 AND ((tlinfo.refund_cr_gl_ccid = x_refund_cr_gl_ccid ) OR
907 (tlinfo.refund_cr_gl_ccid IS NULL AND x_refund_cr_gl_ccid IS NULL))
908 AND ((tlinfo.refund_dr_account_cd = x_refund_dr_account_cd ) OR
909 (tlinfo.refund_dr_account_cd IS NULL AND x_refund_dr_account_cd IS NULL))
910 AND ((tlinfo.refund_cr_account_cd = x_refund_cr_account_cd ) OR
911 (tlinfo.refund_cr_account_cd IS NULL AND x_refund_cr_account_cd IS NULL))
912 AND ((tlinfo.refund_dt_alias = x_refund_dt_alias ) OR
913 (tlinfo.refund_dt_alias IS NULL AND x_refund_dt_alias IS NULL))
914 AND ((tlinfo.fee_calc_mthd_code = x_fee_calc_mthd_code) OR
915 (tlinfo.fee_calc_mthd_code IS NULL AND x_fee_calc_mthd_code IS NULL))
916 AND ((tlinfo.planned_credits_ind = x_planned_credits_ind) OR
917 (tlinfo.planned_credits_ind IS NULL AND x_planned_credits_ind IS NULL))
918 AND ((tlinfo.rec_gl_ccid = x_rec_gl_ccid) OR
919 (tlinfo.rec_gl_ccid IS NULL AND x_rec_gl_ccid IS NULL))
920 AND ((tlinfo.cash_gl_ccid = x_cash_gl_ccid) OR
921 (tlinfo.cash_gl_ccid IS NULL AND x_cash_gl_ccid IS NULL))
922 AND ((tlinfo.unapp_gl_ccid = x_unapp_gl_ccid) OR
923 (tlinfo.unapp_gl_ccid IS NULL AND x_unapp_gl_ccid IS NULL))
924 AND ((tlinfo.rec_account_cd = x_rec_account_cd) OR
925 (tlinfo.rec_account_cd IS NULL AND x_rec_account_cd IS NULL))
926 AND ((tlinfo.rev_account_cd = x_rev_account_cd) OR
927 (tlinfo.rev_account_cD IS NULL AND x_rev_account_cd IS NULL))
928 AND ((tlinfo.cash_account_cd = x_cash_account_cd) OR
929 (tlinfo.cash_account_cd IS NULL AND x_cash_account_cd IS NULL))
930 AND ((tlinfo.unapp_account_cd = x_unapp_account_cd) OR
931 (tlinfo.unapp_account_cd IS NULL AND x_unapp_account_cd IS NULL))
932 AND ((tlinfo.conv_process_run_ind = x_conv_process_run_ind) OR
933 (tlinfo.conv_process_run_ind IS NULL AND x_conv_process_run_ind IS NULL))
934 AND ((tlinfo.currency_cd = x_currency_cd) OR
935 (tlinfo.currency_cd IS NULL AND x_currency_cd IS NULL))
936 AND ((tlinfo.rfnd_destination = x_rfnd_destination) OR
937 (tlinfo.rfnd_destination IS NULL AND x_rfnd_destination IS NULL))
938 AND ((tlinfo.ap_org_id = x_ap_org_id) OR
939 (tlinfo.ap_org_id IS NULL AND x_ap_org_id IS NULL))
940 AND ((tlinfo.dflt_supplier_site_name = x_dflt_supplier_site_name) OR
941 (tlinfo.dflt_supplier_site_name IS NULL AND x_dflt_supplier_site_name IS NULL))
942 AND ((tlinfo.manage_accounts = x_manage_accounts) OR
943 (tlinfo.manage_accounts IS NULL AND x_manage_accounts IS NULL))
944 AND ((tlinfo.acct_conv_flag = x_acct_conv_flag) OR
945 (tlinfo.acct_conv_flag IS NULL AND x_acct_conv_flag IS NULL))
946 AND ((tlinfo.post_waiver_gl_flag = x_post_waiver_gl_flag) OR
947 (tlinfo.post_waiver_gl_flag IS NULL AND x_post_waiver_gl_flag IS NULL))
948 AND ((tlinfo.waiver_notify_finaid_flag = x_waiver_notify_finaid_flag ) OR
949 (tlinfo.waiver_notify_finaid_flag IS NULL AND x_waiver_notify_finaid_flag IS NULL))
950 ) THEN
951 NULL;
952 ELSE
953 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
954 igs_ge_msg_stack.add;
955 app_exception.raise_exception;
956 END IF;
957 RETURN;
958 END lock_row;
959
960
961 PROCEDURE update_row (
962 x_rowid IN VARCHAR2,
963 x_rec_installed IN VARCHAR2,
964 x_mode IN VARCHAR2 ,
965 x_accounting_method IN VARCHAR2,
966 x_set_of_books_id IN NUMBER ,
967 x_refund_dr_gl_ccid IN NUMBER ,
968 x_refund_cr_gl_ccid IN NUMBER ,
969 x_refund_dr_account_cd IN VARCHAR2 ,
970 x_refund_cr_account_cd IN VARCHAR2 ,
971 x_refund_dt_alias IN VARCHAR2 ,
972 x_fee_calc_mthd_code IN VARCHAR2 ,
973 x_planned_credits_ind IN VARCHAR2 ,
974 x_rec_gl_ccid IN NUMBER ,
975 x_cash_gl_ccid IN NUMBER ,
976 x_unapp_gl_ccid IN NUMBER ,
977 x_rec_account_cd IN VARCHAR2 ,
978 x_rev_account_cd IN VARCHAR2 ,
979 x_cash_account_cd IN VARCHAR2 ,
980 x_unapp_account_cd IN VARCHAR2,
981 x_conv_process_run_ind IN NUMBER,
982 x_currency_cd IN VARCHAR2,
983 x_rfnd_destination IN VARCHAR2,
984 x_ap_org_id IN NUMBER,
985 x_dflt_supplier_site_name IN VARCHAR2,
986 x_manage_accounts IN VARCHAR2,
987 x_acct_conv_flag IN VARCHAR2,
988 x_post_waiver_gl_flag IN VARCHAR2,
989 x_waiver_notify_finaid_flag IN VARCHAR2
990 ) AS
991 /*************************************************************
992 Created By :
993 Date Created By :
994 Purpose :
995 Know limitations, enhancements or remarks
996 Change History
997 Who When What
998 (reverse chronological order - newest change first)
999 svuppala 14-JUL-2005 Enh 3392095 - Tution Waivers build
1000 Modified by adding two new columns post_waiver_gl_flag, waiver_notify_finaid_flag
1001 uudayapr 07-dec-2003 Enh#3167098 Modified the procedures BY removing the reference to prg_chg_dt_alias
1002 and adding res_dt_alias.
1003 shtatiko 27-MAY-2003 Enh# 2831582, Removed references to columns lockbox_context, lockbox_number_attribute
1004 and ar_int_org_id.
1005 vvutukur 16-May-2003 Enh#2831572.Financial Accounting Build. Added column acct_conv_flag.
1006 pathipat 14-Apr-2003 Enh. 2831569 - Commercial Receivables Interface
1007 Removed references to columns interface_line_context, interface_line_attribute, term_id,
1008 batch_source_id, cust_trx_type_id. Added column manage_accounts
1009 smadathi 18-Feb-2002 Enh. Bug 2747329.Added new columns rfnd_destination, ap_org_id, dflt_supplier_site_name
1010 smadathi 01-Nov-2002 Enh Bug 2584986. Added new column currency_cd
1011 pathipat 02-OCT-2002 Added column x_conv_process_run_ind, part of Enh Bug: 2562745
1012 smvk 24-Sep-2002 Added the columns rec_gl_ccid, cash_gl_ccid, unapp_gl_ccid,
1013 rec_account_cd, rev_account_cd, cash_account_cd and
1014 unapp_account_cd. As a part of Bug # 2564643.
1015 vchappid 01-Apr-2002 Bug# 2293676, Added column planned_credits_ind
1016 smvk 13-Mar-2002 Added four columns refund_cr_gl_ccid, refund_dr_gl_ccid
1017 refund_dr_account_cd, refund_cr_account_cd, refund_dt_alias and
1018 removed last_account_trans, last_payment_trans and
1019 last_pay_term_trans w.r.t. Bug # 2144600
1020 vvutukur 13-02-2002 Added a new column, ar_int_org_id for bug 2222272
1021 msrinivi 17 Jul,2001 Added 1 new column, set_of_books_id
1022 ***************************************************************/
1023 x_last_update_date DATE ;
1024 x_last_updated_by NUMBER ;
1025 x_last_update_login NUMBER ;
1026
1027 BEGIN
1028
1029 x_last_update_date := SYSDATE;
1030
1031 IF (x_mode = 'I') THEN
1032 x_last_updated_by := 1;
1033 x_last_update_login := 0;
1034 ELSIF (x_mode = 'R') THEN
1035 x_last_updated_by := FND_GLOBAL.USER_ID;
1036 IF x_last_updated_by IS NULL THEN
1037 x_last_updated_by := -1;
1038 END IF;
1039 x_last_update_login :=FND_GLOBAL.LOGIN_ID;
1040 IF x_last_update_login IS NULL THEN
1041 x_last_update_login := -1;
1042 END IF;
1043 ELSE
1044 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1045 igs_ge_msg_stack.add;
1046 app_exception.raise_exception;
1047 END IF;
1048
1049 before_dml(
1050 p_action =>'UPDATE',
1051 x_rowid => x_rowid,
1052 x_rec_installed => x_rec_installed,
1053 x_creation_date => x_last_update_date,
1054 x_created_by => x_last_updated_by,
1055 x_last_update_date => x_last_update_date,
1056 x_last_updated_by => x_last_updated_by,
1057 x_last_update_login => x_last_update_login,
1058 x_accounting_method => x_accounting_method,
1059 x_set_of_books_id => x_set_of_books_id,
1060 x_refund_dr_gl_ccid => x_refund_dr_gl_ccid,
1061 x_refund_cr_gl_ccid => x_refund_cr_gl_ccid,
1062 x_refund_dr_account_cd => x_refund_dr_account_cd,
1063 x_refund_cr_account_cd => x_refund_cr_account_cd,
1064 x_refund_dt_alias => x_refund_dt_alias,
1065 x_fee_calc_mthd_code => x_fee_calc_mthd_code ,
1066 x_planned_credits_ind => x_planned_credits_ind,
1067 x_rec_gl_ccid => x_rec_gl_ccid,
1068 x_cash_gl_ccid => x_cash_gl_ccid,
1069 x_unapp_gl_ccid => x_unapp_gl_ccid ,
1070 x_rec_account_cd => x_rec_account_cd,
1071 x_rev_account_cd => x_rev_account_cd,
1072 x_cash_account_cd => x_cash_account_cd,
1073 x_unapp_account_cd => x_unapp_account_cd,
1074 x_conv_process_run_ind => x_conv_process_run_ind ,
1075 x_currency_cd => x_currency_cd,
1076 x_rfnd_destination => x_rfnd_destination,
1077 x_ap_org_id => x_ap_org_id,
1078 x_dflt_supplier_site_name => x_dflt_supplier_site_name,
1079 x_manage_accounts => x_manage_accounts,
1080 x_acct_conv_flag => x_acct_conv_flag,
1081 x_post_waiver_gl_flag => x_post_waiver_gl_flag,
1082 x_waiver_notify_finaid_flag => x_waiver_notify_finaid_flag
1083 );
1084
1085 UPDATE igs_fi_control_all
1086 SET
1087 rec_installed = new_references.rec_installed,
1088 last_update_date = new_references.last_update_date,
1089 last_updated_by = new_references.last_updated_by,
1090 last_update_login = new_references.last_update_login,
1091 accounting_method = new_references.accounting_method,
1092 set_of_books_id = new_references.set_of_books_id ,
1093 refund_dr_gl_ccid = new_references.refund_dr_gl_ccid,
1094 refund_cr_gl_ccid = new_references.refund_cr_gl_ccid,
1095 refund_dr_account_cd = new_references.refund_dr_account_cd,
1096 refund_cr_account_cd = new_references.refund_cr_account_cd,
1097 refund_dt_alias = new_references.refund_dt_alias,
1098 fee_calc_mthd_code = new_references.fee_calc_mthd_code,
1099 planned_credits_ind = new_references.planned_credits_ind,
1100 rec_gl_ccid = new_references.rec_gl_ccid,
1101 cash_gl_ccid = new_references.cash_gl_ccid,
1102 unapp_gl_ccid = new_references.unapp_gl_ccid,
1103 rec_account_cd = new_references.rec_account_cd,
1104 rev_account_cd = new_references.rev_account_cd,
1105 cash_account_cd = new_references.cash_account_cd,
1106 unapp_account_cd = new_references.unapp_account_cd,
1107 conv_process_run_ind = new_references.conv_process_run_ind ,
1108 currency_cd = new_references.currency_cd,
1109 rfnd_destination = new_references.rfnd_destination,
1110 ap_org_id = new_references.ap_org_id,
1111 dflt_supplier_site_name = new_references.dflt_supplier_site_name,
1112 manage_accounts = new_references.manage_accounts,
1113 acct_conv_flag = new_references.acct_conv_flag,
1114 post_waiver_gl_flag = new_references.post_waiver_gl_flag,
1115 waiver_notify_finaid_flag = new_references.waiver_notify_finaid_flag
1116
1117 WHERE rowid = x_rowid;
1118
1119 IF (sql%NOTFOUND) THEN
1120 RAISE NO_DATA_FOUND;
1121 END IF;
1122
1123 After_DML (
1124 p_action => 'UPDATE' ,
1125 x_rowid => X_ROWID
1126 );
1127
1128 END update_row;
1129
1130 PROCEDURE delete_row (
1131 x_rowid IN VARCHAR2
1132 ) AS
1133 /*************************************************************
1134 Created By :
1135 Date Created By :
1136 Purpose :
1137 Know limitations, enhancements or remarks
1138 Change History
1139 Who When What
1140 (reverse chronological order - newest change first)
1141 ***************************************************************/
1142
1143 BEGIN
1144
1145 Before_DML (
1146 p_action => 'DELETE',
1147 x_rowid => x_rowid
1148 );
1149
1150 DELETE FROM IGS_FI_CONTROL_ALL
1151 WHERE rowid = x_rowid;
1152 IF (sql%NOTFOUND) THEN
1153 RAISE NO_DATA_FOUND;
1154 END IF;
1155
1156 After_DML (
1157 p_action => 'DELETE',
1158 x_rowid => x_rowid
1159 );
1160
1161 END delete_row;
1162
1163 PROCEDURE get_fk_igs_ca_da (
1164 x_dt_alias IN VARCHAR2
1165 ) AS
1166 /*******************************************************************************
1167 Created by : svuppala , Oracle IDC
1168 Date created: 03-Apr-2006
1169
1170 Purpose:
1171 This procedure is created as part of the bug 4025077 to add FK relation for refund_dt_alias
1172 coulmn in IGS_FI_CONTROL table with dt_alias coulmn in IGS_CA_DA table.
1173
1174 Change History: (who, when, what: NO CREATION RECORDS HERE!)
1175 Who When What
1176 *******************************************************************************/
1177 CURSOR cur_rowid IS
1178 SELECT rowid
1179 FROM IGS_FI_CONTROL_ALL
1180 WHERE ((REFUND_DT_ALIAS = x_dt_alias));
1181
1182 lv_rowid cur_rowid%RowType;
1183
1184 BEGIN
1185
1186 OPEN cur_rowid;
1187 FETCH cur_rowid INTO lv_rowid;
1188 IF (cur_rowid%FOUND) THEN
1189 CLOSE cur_rowid;
1190 fnd_message.set_name ('IGS', 'IGS_FI_CTRL_DA_FK');
1191 igs_ge_msg_stack.add;
1192 app_exception.raise_exception;
1193 RETURN;
1194 END IF;
1195 CLOSE cur_rowid;
1196
1197 END get_fk_igs_ca_da;
1198
1199 END igs_fi_control_pkg;