1 PACKAGE BODY igs_fi_gen_gl AS
2 /* $Header: IGSFI75B.pls 120.1 2006/05/12 01:49:04 abshriva noship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL spec for package: IGS_FI_GEN_GL |
11 | |
12 | NOTES |
13 |New Package created for generic |
14 | procedures and functions |
15 | as per GL Interface TD. (Bug 2584986)|
16 | HISTORY |
17 | Who When What |
18 | abshriva 12-MAY-2006 Added new function get_formatted_amount
19 | to return formatted amount |
20 | vvutukur 12-Jan-2004 Bug#3348787.Modified finp_get_cur. |
21 | schodava 29-Sep-2003 Bug # 3112084 - Modified procedure |
22 | finp_get_cur |
23 | agairola 26-Nov-2002 Removed the procedures for the |
24 | derivation of the Journal Categories |
25 | SYKRISHN 05-NOV/2002 New Package created for generic |
26 | procedures and functions |
27 | as per GL Interface TD. |
28 *=======================================================================*/
29 g_old_reference fnd_currencies%ROWTYPE;
30
31
32 PROCEDURE finp_get_cur ( p_v_currency_cd OUT NOCOPY VARCHAR2,
33 p_v_curr_desc OUT NOCOPY VARCHAR2,
34 p_v_message_name OUT NOCOPY VARCHAR2) AS
35 /******************************************************************
36 Created By : SYKRISHN
37 Date Created By : 05/NOV-2002
38 Purpose : Procedure to get the local functional currency details
39 Returns message name if error occurs
40 Known limitations,
41 enhancements,
42 remarks :
43 Change History
44 Who When What
45 vvutukur 12-Jan-2004 Bug#3348787.Modified cursor c_curr_desc to put filter on language
46 while selecting currency from fnd_currencies_tl.
47 schodava 29-Sep-2003 Bug # 3112084 - Modified cursor cur_ctrl, to
48 fetch the currency name from fnd_currencies_tl
49 ******************************************************************/
50
51 -- get the currency code
52 CURSOR cur_ctrl IS
53 SELECT currency_cd
54 FROM igs_fi_control_all;
55
56 -- Get the name of the currency
57 CURSOR c_curr_desc(cp_currency_cd IN igs_fi_control_all.currency_cd%TYPE
58 ) IS
59 SELECT name
60 FROM fnd_currencies_tl
61 WHERE currency_code = cp_currency_cd
62 AND language = USERENV('LANG');
63
64 BEGIN
65 p_v_message_name := NULL;
66 OPEN cur_ctrl;
67 FETCH cur_ctrl INTO p_v_currency_cd;
68
69 IF cur_ctrl%NOTFOUND THEN
70 CLOSE cur_ctrl;
71 p_v_currency_cd := NULL;
72 p_v_curr_desc := NULL;
73 p_v_message_name := 'IGS_FI_SYSTEM_OPT_SETUP';
74 RETURN;
75 END IF;
76
77 OPEN c_curr_desc(p_v_currency_cd);
78 FETCH c_curr_desc INTO p_v_curr_desc;
79 CLOSE c_curr_desc;
80
81 CLOSE cur_ctrl;
82 END finp_get_cur;
83
84
85 FUNCTION finp_ss_get_cur RETURN VARCHAR2 AS
86 /******************************************************************
87 Created By : SYKRISHN
88 Date Created By : 05/NOV-2002
89 Purpose : Function to only return currency_cd (to be used in SS)
90
91 Known limitations,
92 enhancements,
93 remarks :
94 Change History
95 Who When What
96 ******************************************************************/
97
98 l_v_currency_cd igs_fi_control_v.currency_cd%TYPE;
99 l_v_currency_desc igs_fi_control_v.name%TYPE;
100 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
101
102 BEGIN
103
104 --Invoke procedure to get currency
105 igs_fi_gen_gl.finp_get_cur(l_v_currency_cd,
106 l_v_currency_desc,
107 l_v_message_name);
108
109 -- If error then return null
110 IF l_v_message_name IS NOT NULL THEN
111 RETURN NULL;
112 ELSE
113 RETURN l_v_currency_cd;
114 END IF;
115
116 END finp_ss_get_cur;
117
118
119
120 FUNCTION check_unposted_txns_exist (p_d_start_date IN DATE,
121 p_d_end_date IN DATE,
122 p_v_accounting_mthd IN VARCHAR2) RETURN BOOLEAN AS
123 /******************************************************************
124 Created By : SYKRISHN
125 Date Created By : 05/NOV-2002
126 Purpose : Function to check for any unposted transactions
127 This function returns Boolean. TRUE if
128 Unposted Transaction exists and FALSE if not.
129
130 Known limitations,
131 enhancements,
132 remarks :
133 Change History
134 Who When What
135 ******************************************************************/
136
137
138 --Cursor to validate the accounting method param
139 CURSOR cur_acct_mth(cp_v_accounting_mthd IN igs_fi_control.accounting_method%TYPE) IS
140 SELECT lookup_code
141 FROM igs_lookup_values
142 WHERE lookup_code = cp_v_accounting_mthd
143 AND lookup_type = 'IGS_FI_ACCT_METHOD'
144 AND enabled_flag = 'Y'
145 AND trunc(sysdate) BETWEEN trunc(NVL(start_date_active, SYSDATE)) AND trunc(NVL(end_date_active, SYSDATE));
146
147
148 --Cursor to check unposted transactions in credit activities table.
149
150 CURSOR cur_credit_activities(cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
151 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
152
153 SELECT 'Y'
154 FROM igs_fi_cr_activities crac
155 WHERE crac.gl_date IS NOT NULL
156 AND TRUNC(crac.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
157 AND crac.posting_id IS NULL
158 AND crac.posting_control_id IS NULL;
159
160
161
162 --Cursor to check unposted transactions in applications table.
163
164 CURSOR cur_appl(cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
165 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
166
167 SELECT 'Y'
168 FROM igs_fi_applications appl
169 WHERE appl.gl_date IS NOT NULL
170 AND TRUNC(appl.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
171 AND appl.posting_id IS NULL
172 AND appl.posting_control_id IS NULL;
173
174
175
176 --Cursor to check unposted transactions in adm applications fees table. (only posting control id need to be checked)
177 CURSOR cur_adm_fee (cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
178 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
179 SELECT 'Y'
180 FROM igs_ad_app_req adm
181 WHERE adm.gl_date IS NOT NULL
182 AND TRUNC(adm.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
183 AND adm.posting_control_id IS NULL;
184
185
186 --Cursor to check unposted transactions in charges table.
187 CURSOR cur_inv (cp_d_gl_date_start IN igs_fi_posting_int_all.accounting_date%TYPE,
188 cp_d_gl_date_end IN igs_fi_posting_int_all.accounting_date%TYPE ) IS
189 SELECT 'Y'
190 FROM igs_fi_invln_int_all invln
191 WHERE invln.gl_date IS NOT NULL
192 AND TRUNC(invln.gl_date) BETWEEN TRUNC(cp_d_gl_date_start) AND TRUNC(cp_d_gl_date_end)
193 AND invln.posting_id IS NULL
194 AND invln.posting_control_id IS NULL;
195
196
197 l_v_accounting_method igs_fi_control.accounting_method%TYPE;
198 l_v_exist VARCHAR2(1);
199
200 BEGIN
201
202 -- Sanity Validation of the input parameters to raise exception
203 -- We know that this is a function invoked by igsfi071- hence the validation to raise exception only
204 IF (p_d_start_date IS NULL) OR (p_d_end_date IS NULL) THEN
205 app_exception.raise_exception;
206 END IF;
207
208 IF TRUNC(p_d_start_date) > TRUNC(p_d_end_date) THEN
209 app_exception.raise_exception;
210 END IF;
211
212 OPEN cur_acct_mth(p_v_accounting_mthd);
213 FETCH cur_acct_mth INTO l_v_accounting_method;
214 IF cur_acct_mth%NOTFOUND THEN
215 CLOSE cur_acct_mth;
216 app_exception.raise_exception;
217 END IF;
218 CLOSE cur_acct_mth;
219 -- Sanity Validation of the input parameters to raise exception
220
221 --If the value of the parameter P_V_ACCOUNTING_MTHD is CASH or ACCRUAL then existence of unposted transactions needs to be checked in
222 --the credit activities, the applications table and admission application fees table.
223
224 --
225 OPEN cur_credit_activities(cp_d_gl_date_start => p_d_start_date,
226 cp_d_gl_date_end => p_d_end_date);
227 FETCH cur_credit_activities INTO l_v_exist;
228 IF cur_credit_activities%FOUND THEN
229 CLOSE cur_credit_activities;
230 RETURN TRUE;
231 END IF;
232 CLOSE cur_credit_activities;
233 --
234
235 OPEN cur_appl(cp_d_gl_date_start => p_d_start_date,
236 cp_d_gl_date_end => p_d_end_date);
237 FETCH cur_appl INTO l_v_exist;
238 IF cur_appl%FOUND THEN
239 CLOSE cur_appl;
240 RETURN TRUE;
241 END IF;
242 CLOSE cur_appl;
243 --
244 OPEN cur_adm_fee(cp_d_gl_date_start => p_d_start_date,
245 cp_d_gl_date_end => p_d_end_date);
246 FETCH cur_adm_fee INTO l_v_exist;
247 IF cur_adm_fee%FOUND THEN
248 CLOSE cur_adm_fee;
249 RETURN TRUE;
250 END IF;
251 CLOSE cur_adm_fee;
252
253 --This check needs to happen only when accounting method is ACCRUAL
254
255 IF p_v_accounting_mthd = 'ACCRUAL' THEN
256 OPEN cur_inv(cp_d_gl_date_start => p_d_start_date,
257 cp_d_gl_date_end => p_d_end_date);
258 FETCH cur_inv INTO l_v_exist;
259 IF cur_inv%FOUND THEN
260 CLOSE cur_inv;
261 RETURN TRUE;
262 END IF;
263 CLOSE cur_inv;
264 END IF;
265
266 -- If no unposted transactions exist ina any of the above, then return FALSE to denote than no unposted txns exist at all.
267
268 RETURN FALSE;
269
270
271 END check_unposted_txns_exist;
272
273 PROCEDURE get_period_status_for_date(p_d_date IN DATE,
274 p_v_closing_status OUT NOCOPY VARCHAR2,
275 p_v_message_name OUT NOCOPY VARCHAR2) AS
276 /******************************************************************
277 Created By : SYKRISHN
278 Date Created By : 05/NOV-2002
279 Purpose : Procedure to get the period's closing status in which a passed date belong to
280
281 Known limitations,
282 enhancements,
283 remarks :
284 Change History
285 Who When What
286 ******************************************************************/
287
288
289
290
291 --Cursor to Derive the Set Of books defined in the system options form- Use the Set of Books ID derived (SET_OF_BOOKS_ID) to
292 --access the view IGS_FI_GL_PERIODS_V to get the CLOSING_STATUS of the period in which the passed P_D_DATE belongs to.
293 CURSOR cur_closing_status IS
294 SELECT closing_status
295 FROM igs_fi_gl_periods_v
296 WHERE TRUNC(p_d_date) BETWEEN TRUNC(start_date) AND TRUNC(end_date)
297 AND set_of_books_id = igs_fi_gen_007.get_sob_id;
298
299 BEGIN
300 p_v_message_name := NULL;
301 p_v_closing_status := NULL;
302
303 -- Validate if p_d_date passed is null
304 IF (p_d_date IS NULL) THEN
305 p_v_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER' ;
306 RETURN;
307 END IF;
308
309 --If the passed P_D_DATE is Current System Date , then return P_V_CLOSING_STATUS = O and return from procedure. No validation required when the date passed is SYSDATE.
310 --OR If the Oracle Financials Installed is derived as Y, then proceed with remaining steps . If the value is N, then return P_V_CLOSING_STATUS = O and return from procedure.
311 --Derive the value of Oracle Financials Installed value defined in the system options
312
313 IF (TRUNC(p_d_date) = TRUNC(SYSDATE)) OR (igs_fi_gen_005.finp_get_receivables_inst = 'N') THEN
314 p_v_closing_status := 'O';
315 RETURN;
316 END IF;
317
318 --Derive the Set Of books defined in the system options form- Use the Set of Books ID derived (SET_OF_BOOKS_ID) to
319 --access the view IGS_FI_GL_PERIODS_V to get the CLOSING_STATUS of the period in which the passed P_D_DATE belongs to.
320 OPEN cur_closing_status;
321 FETCH cur_closing_status INTO p_v_closing_status;
322 IF cur_closing_status%NOTFOUND THEN
323 CLOSE cur_closing_status;
324 p_v_closing_status := NULL;
325 p_v_message_name := 'IGS_FI_GL_DT_NT_IN_PER';
326 RETURN;
327 END IF;
328 p_v_message_name := NULL;
329 -- Return the appropriate closing status derived.
330 CLOSE cur_closing_status;
331
332
333 END get_period_status_for_date;
334
335
336
337 FUNCTION check_gl_dt_appl_not_valid (p_d_gl_date IN DATE,
338 p_n_invoice_id IN NUMBER,
339 p_n_credit_id IN NUMBER) RETURN BOOLEAN AS
340 /******************************************************************
341 Created By : SYKRISHN
342 Date Created By : 05/NOV-2002
343 Purpose : Function to check validity of GL date for applications
344 This function returns TRUE if the GL Date passed is NOT Valid. Else FALSE
345
346 Known limitations,
347 enhancements,
348 remarks :
349 Change History
350 Who When What
351 ******************************************************************/
352
353
354 CURSOR cur_inv (cp_n_invoice_id IN igs_fi_inv_int.invoice_id%TYPE) IS
355 SELECT gl_date
356 FROM igs_fi_invln_int_all
357 WHERE gl_date IS NOT NULL
358 AND invoice_id = cp_n_invoice_id;
359
360
361 CURSOR cur_credit (cp_n_credit_id IN igs_fi_credits.credit_id%TYPE) IS
362 SELECT gl_date
363 FROM igs_fi_credits_all
364 WHERE gl_date IS NOT NULL
365 AND credit_id = cp_n_credit_id;
366
367 l_d_chg_gl_date igs_fi_invln_int_all.gl_date%TYPE := NULL;
368 l_d_crd_gl_date igs_fi_credits_all.gl_date%TYPE := NULL;
369
370 BEGIN
371
372 -- Sanity Validation of the input parameters to raise exception
373 -- We know that this is a function invoked by applications form - hence the validation to raise exception only and not give proper error messages.
374 IF (p_d_gl_date IS NULL) OR (p_n_invoice_id IS NULL) OR (p_n_credit_id IS NULL) THEN
375 app_exception.raise_exception;
376 END IF;
377 -- Sanity Validation of the input parameters to raise exception
378
379 OPEN cur_inv (p_n_invoice_id);
380 FETCH cur_inv INTO l_d_chg_gl_date;
381 IF cur_inv%NOTFOUND THEN
382 CLOSE cur_inv;
383 RETURN FALSE;
384 END IF;
385 CLOSE cur_inv;
386
387
388 OPEN cur_credit (p_n_credit_id);
389 FETCH cur_credit INTO l_d_crd_gl_date;
390 IF cur_credit%NOTFOUND THEN
391 CLOSE cur_credit;
392 RETURN FALSE;
393 END IF;
394 CLOSE cur_credit;
395
396
397 --If the passed P_D_GL_DATE is earlier than CHG_GL_DATE or CRD_GL_DATE selected above then return TRUE else FALSE.
398 IF (TRUNC(p_d_gl_date) < TRUNC(l_d_chg_gl_date)) OR (TRUNC(p_d_gl_date) < TRUNC(l_d_crd_gl_date)) THEN
399 RETURN TRUE;
400 END IF;
401
402 -- Else return FALSE
403 RETURN FALSE;
404
405
406 END check_gl_dt_appl_not_valid;
407
408 FUNCTION check_neg_chgadj_exists (p_n_invoice_id IN NUMBER) RETURN BOOLEAN AS
409
410 /******************************************************************
411 Created By : SYKRISHN
412 Date Created By : 05/NOV-2002
413 Purpose : Function to check if negative charge adjustment exist for the charge id (invoice_id) passed
414 This function returns BOOLEAN. Returns TRUE if negative charge adjustment exists and FALSE otherwise.
415
416 Known limitations,
417 enhancements,
418 remarks :
419 Change History
420 Who When What
421 ******************************************************************/
422
423 -- Below 2 Cursors to check if negative charge adjustment exists in applications table for the invoice_id
424
425 CURSOR cur_app_crd (cp_n_invoice_id IN igs_fi_inv_int.invoice_id%TYPE) IS
426 SELECT credit_id
427 FROM igs_fi_applications
428 WHERE invoice_id = cp_n_invoice_id
429 AND application_type = 'APP';
430
431
432 CURSOR cur_neg (cp_n_credit_id IN igs_fi_credits.credit_id%TYPE) IS
433 SELECT 'Y'
434 FROM igs_fi_credits_all crd,
435 igs_fi_cr_types_all crtype
436 WHERE crd.credit_id = cp_n_credit_id
437 AND crd.credit_type_id = crtype.credit_type_id
438 AND crtype.credit_class = 'CHGADJ';
439
440 l_v_neg_exist VARCHAR2(1);
441
442 BEGIN
443 -- Sanity Validation of the input parameters to raise exception
444 -- We know that this is a function invoked internally - hence the validation to raise exception only and not give proper error messages.
445 IF (p_n_invoice_id IS NULL) THEN
446 app_exception.raise_exception;
447 END IF;
448 -- Sanity Validation of the input parameters to raise exception
449
450 -- Loop across the applied credit ids to check if any of them is a neg chag adjustment.
451 FOR app_crd_rec IN cur_app_crd (p_n_invoice_id) LOOP
452 OPEN cur_neg (app_crd_rec.credit_id);
453 FETCH cur_neg INTO l_v_neg_exist;
454 IF cur_neg%FOUND THEN
455 CLOSE cur_neg;
456 RETURN TRUE;
457 END IF;
458 CLOSE cur_neg;
459 END LOOP;
460 -- Return FALSE if no neg charge adj rows found.
461 RETURN FALSE;
462
463 END check_neg_chgadj_exists;
464
465 FUNCTION get_lkp_meaning (p_v_lookup_type IN igs_lookup_values.lookup_type%TYPE ,
466 p_v_lookup_code IN igs_lookup_values.lookup_code%TYPE ) RETURN VARCHAR2 IS
467 /******************************************************************
468 Created By : sykrishn
469 Date Created By : 11-NOV-2002
470 Purpose : Function Returns the meaning for the given lookup code
471 Retuns NULL if not found.
472
473 Known limitations,
474 enhancements,
475 remarks :
476 Change History
477 Who When What
478 ******************************************************************/
479
480 CURSOR cur_lkp_meaning( cp_v_lookup_type IN igs_lookup_values.lookup_type%TYPE ,
481 cp_v_lookup_code IN igs_lookup_values.lookup_code%TYPE )
482 IS
483 SELECT meaning
484 FROM igs_lookup_values
485 WHERE lookup_code = cp_v_lookup_code
486 AND lookup_type = cp_v_lookup_type;
487
488 l_v_meaning igs_lookup_values.meaning%TYPE;
489
490 BEGIN
491 IF p_v_lookup_code IS NULL THEN
492 RETURN NULL;
493 ELSE
494 OPEN cur_lkp_meaning(p_v_lookup_type,p_v_lookup_code);
495 FETCH cur_lkp_meaning INTO l_v_meaning;
496 IF cur_lkp_meaning%NOTFOUND THEN
497 CLOSE cur_lkp_meaning;
498 RETURN NULL;
499 END IF;
500 CLOSE cur_lkp_meaning;
501 END IF ;
502
503 RETURN l_v_meaning;
504
505
506 END get_lkp_meaning;
507
508 FUNCTION get_formatted_amount ( p_n_amount IN NUMBER) RETURN NUMBER
509 AS
510 ------------------------------------------------------------------
511 --Created by : Sanil Madathil, Oracle IDC
512 --Date created: 28 April 2006
513 --
514 -- Purpose: : Public procedure for amount precision
515 -- Invoked :
516 -- Function : Public function to return the formatted
517 -- value of the input amount based on the currency
518 -- precision
519 -- Parameters : p_n_amount : IN parameter. Required.
520 --
521 --
522 --Known limitations/enhancements and/or remarks:
523 --
524 --Change History:
525 --Who When What
526 ------------------------------------------------------------------
527 l_n_amount NUMBER;
528
529 BEGIN
530 l_n_amount := p_n_amount;
531
532 -- if the global value have not been initialized
533 IF g_old_reference.currency_code IS NULL THEN
534 -- Get the default functional currency set up in the System Options form
535 g_old_reference.currency_code := igs_fi_gen_gl.finp_ss_get_cur;
536 -- If no default functional currency set up in the System Options form
537 -- return the amount value without any precision formatting
538 IF g_old_reference.currency_code IS NULL THEN
539 RETURN l_n_amount;
540 END IF;
541
542 -- invoke the generic fnd_currency.get_info to get the precision information
543 -- for the functional currency. This call out would be made once per session
544 fnd_currency.get_info(
545 currency_code => g_old_reference.currency_code ,
546 precision => g_old_reference.precision ,
547 ext_precision => g_old_reference.extended_precision ,
548 min_acct_unit => g_old_reference.minimum_accountable_unit
549 );
550 END IF;
551
552 -- if minimum_accountable_unit holds a value
553 IF g_old_reference.minimum_accountable_unit IS NOT NULL
554 THEN
555 RETURN( ROUND(l_n_amount/g_old_reference.minimum_accountable_unit)* g_old_reference.minimum_accountable_unit);
556 END IF;
557
558 RETURN( ROUND( l_n_amount,g_old_reference.precision ));
559
560 END get_formatted_amount;
561
562
563 END igs_fi_gen_gl;
[Home] [Help]