1 PACKAGE BODY jai_cmn_utils_pkg AS
2 /* $Header: jai_cmn_utils.plb 120.13.12020000.6 2013/03/15 13:29:22 mmurtuza ship $ */
3
4 /********************************************************************************************************
5 FILENAME : ja_in_util_pkg_b.sql
6
7 Created By : ssumaith
8
9 Created Date : 29-Nov-2004
10
11 Bug : 4033992
12
13 Purpose : Check whether The India Localization functionality should be used or not.
14
15 Called from : All india Localization triggers on base APPS tables
16
17 --------------------------------------------------------------------------------------------------------
18 CHANGE HISTORY:
19 --------------------------------------------------------------------------------------------------------
20 S.No Date Author and Details
21 --------------------------------------------------------------------------------------------------------
22 1. 2004/11/29 ssumaith - bug# 4033992 - File version 115.0
23
24 created the package spec for the common utility which will be used to check
25 if India Localization funtionality is being used.
26
27 This function check_jai_exists is to be called from all India localization triggers.
28 The parameter - p_calling_object is a mandatory one and will have the name of the
29 trigger which calls the package.
30 The other parameters are optional , but one of them needs to be passed.
31 The second parameter is inventory_organization_id
32 The Third Parameter is Operating_unit
33 The fouth Parameter is Set_of_books_id
34 The fifth and sixth parameters are for future use.
35 The fifth parameter - p_value_string has the values passed seperated by colons
36 The sixth parameter - p_format_string has the corresponding labels seperated by colons,
37 which inform what each value is.
38
39 Example call to the package can be :
40
41 JA_IN_UTIL.CHECK_JAI_EXISTS(
42 P_CALLING_OBJECT => 'TRIGGER NAME' ,
43 P_ORG_ID => :New.org_id ,
44 P_Value_string => 'OM:OE_ORDER_LINES_ALL' ,
45 p_format_string => 'MODULE NAME:TABLE NAME'
46 );
47
48 2. 8-Jun-2005 File Version 116.2 jai_cmn_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
49 as required for CASE COMPLAINCE.
50
51 3. 14-Jun-2005 rchandan for bug#4428980, Version 116.3
52 Modified the object to remove literals from DML statements and CURSORS.
53 This activity is done as a part of R12 initiatives.
54
55 4. 14-Jun-2005 rchandan for bug#4428980, Version 116.4,
56 As part OF R12 Inititive Inventory conversion the OPM code IS commented
57
58 5 06-Jul-2005 rallamse for bug# PADDR Elimination
59 1. Removed the procedures ja_in_put_locator , ja_in_set_locator and ja_in_get_locator
60 from both the specification and body.
61
62 6. 12-Jul-2005 Sanjikum for Bug#4483042, Version 117.2
63 1) Added a new function validate_po_type
64
65 7. 22-Sep-2005 Ramananda for issue#76. Bug 4627086 . Version 120.2
66 Added not null columns in the insert statement of update_rg_slno procedure
67
68 8. 06-Dec-2005 rallamse for Bug#4773914, Version 120.2
69 1) Added a new function get_operating_unit
70 This function get_operating_unit returns operating unit based on
71 inventory organization id.
72
73
74 9. 27-Dec-2005 Bug 4906958. Added by Lakshmi Gopalsami Version 120.4
75 Derived the value for default LE if the value is not retrieved via
76 default BSV.
77
78 10. 26-FEB-2007 SSAWANT , File version 120.7
79 Forward porting the change in 11.5 bug 4903380 to R12 bug no 5039365 .
80 Added a function return_valid_date. This function would take varchar2 as input. If
81 this input is a date then it would return the same otherwise it would return NULL.
82 This function is currently used in JAINASST.rdf and JAINYEDE.rdf.
83
84 Dependency
85 ----------
86 Yes
87
88 11 18-MAY-2007 Bgowrava for Bug#6053352 , file version 120.8
89 Modified the date related codes to satisfy the GSCC compilance.
90
91
92 12. 09-Mar-2010 Jia for GL Drilldown ER
93 Add a new function if_IL_drilldown that is used to Enable/Disable drilldown button
94 according OFI journal source and journal categories.
95
96 13. 07-Jun-2010 Modified by Jia for bug#9736876
97 Issue: TAXES POSTED TO PLA IN FOREIGN CURRENCY
98 Fix: Modified the return value in function currency_conversion.
99
100 14. 21-Sep-2012 mmurtuza for bug 14583195
101 Issue: POAPPRV WORKFLOW NOT SHOWING UP THE VALUE OF NON RECOVERABLE TAX
102 Fix: Added call to AD_EVENT_REGISTRY_PKG.Is_Event_Done in FUNCTION check_jai_exists to check JAI tables
103
104 15. 15-Mar-2013 mmurtuza for bug 16456183
105 Issue: WHILE BOOKING THE ORDER CURRENCY CONVERSION VALIDATION IS MISSING
106 Fix: Commented use of cursor get_curr_rate and used select statement instead
107
108 *********************************************************************************************************/
109 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
110 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
111 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
112 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
113 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
114 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
115 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
116 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'JAI.PLSQL.JAI_CMN_UTILS_PKG.';
117 FUNCTION get_currency_code(p_operating_unit_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE) RETURN VARCHAR2 IS
118 /* Bug 5243532. Added by Lakshmi Gopalsami.
119 Removed the cursor c_set_of_books and c_sob_currency
120 and implemented using caching logic.
121 */
122
123 ln_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
124 lv_sob_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
125
126 /* Bug 5243532. Added by Lakshmi Gopalsami.
127 Defined local variable for implementing caching logic.
128 */
129 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
130
131 BEGIN
132 /* Bug 5243532. Added by Lakshmi Gopalsami
133 Removed the reference to cursor and implemented
134 caching logic.
135 */
136 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
137 (p_org_id => p_operating_unit_id );
138
139 ln_set_of_books_id := l_func_curr_det.ledger_id;
140 lv_sob_currency_code := l_func_curr_det.currency_code;
141
142 RETURN(lv_sob_currency_code);
143 END get_currency_code;
144
145 FUNCTION check_jai_exists(p_calling_object VARCHAR2 ,
146 p_inventory_orgn_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
147 p_org_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
148 p_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL ,
149 p_value_string VARCHAR2 DEFAULT NULL ,
150 p_format_string VARCHAR2 DEFAULT NULL
151 ) RETURN BOOLEAN
152 IS
153 /* Bug 5243532. Added by Lakshmi Gopalsami
154 Removed the cursor c_set_of_books and c_operating_unit
155 which is referring to hr_operating_units
156 and org_organization_definitions. Replaced the same with caching logic.
157 Replaced gl_sets_of_books with gl_ledgers
158 */
159 CURSOR c_sob_currency (cp_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE) IS
160 SELECT currency_code
161 FROM gl_ledgers
162 WHERE ledger_id = cp_set_of_books_id;
163
164 lv_sob_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
165 lv_calling_object VARCHAR2(50);
166 lv_message_text VARCHAR2(3000);
167
168 /* Bug 5243235. Added by Lakshmi Gopalsami
169 Defined variable for implementing caching logic.
170 */
171 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
172
173 BEGIN
174 /*
175 If the mandatory parameter p_calling_object is not passed, then returning false value so that no further processing needs
176 to be done.
177 */
178
179 IF p_calling_object IS NULL THEN
180 return(false);
181 END IF;
182
183 /*Added below if clause by mmurtuza for bug 14583195*/
184 IF (AD_EVENT_REGISTRY_PKG.Is_Event_Done( p_Owner => 'JA',p_Event_Name => 'JAI_EXISTENCE_OF_TABLES' ) = false) then
185 return (false);
186 END IF;
187
188 /*
189 set of books id is passed. Get the currency code from the gl_sets_of_books and return true or false
190 depending on the curreny code = 'INR' or not.
191 */
192 IF p_set_of_books_id IS NOT NULL THEN
193 OPEN c_sob_currency(p_set_of_books_id);
194 FETCH c_sob_currency INTO lv_sob_currency_code;
195 CLOSE c_sob_currency;
196
197 IF lv_sob_currency_code = 'INR' THEN
198 RETURN(TRUE);
199 ELSE
200 RETURN(FALSE);
201 END IF;
202 END IF;
203
204 /* Bug 5243532. Added by Lakshmi Gopalsami
205 Removed the existing code for deriving the SOB.
206 Implemented using caching logic.
207 */
208 IF p_org_id IS NOT NULL THEN
209 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
210 (p_org_id => p_org_id );
211
212 ELSIF p_inventory_orgn_id IS NOT NULL THEN
213 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
214 (p_org_id => p_inventory_orgn_id );
215 END IF;
216 lv_sob_currency_code := l_func_curr_det.currency_code;
217 IF lv_sob_currency_code = 'INR' THEN
218 RETURN(TRUE);
219 ELSE
220 RETURN(FALSE);
221 END IF;
222
223 /*
224 The final return(false) below is to trap the case where none of the parameters p_inventory_orgn_id, p_org_id
225 or p_set_of_books_id is passed.
226 */
227
228 RETURN(FALSE);
229 EXCEPTION
230 WHEN OTHERS THEN
231 RAISE_APPLICATION_ERROR(-20001,'Unexpected Error in ja_in_util.check_jai_exists, called from ' || p_calling_object || ' The error is ' || sqlerrm);
232 END check_jai_exists;
233 /*5039365 by ssawant*/
234 FUNCTION return_valid_date( p_validate_text VARCHAR2 ) RETURN DATE IS
235 ld_ret_value DATE;
236 BEGIN
237 ld_ret_value := to_date( p_validate_text,'DD/MM/RRRR');
238 return ld_ret_value;
239 EXCEPTION
240 when others then
241 return NULL;
242 END return_valid_date;
243
244 FUNCTION get_operating_unit (
245 p_calling_object VARCHAR2 ,
246 p_inventory_orgn_id ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE
247 ) RETURN NUMBER
248 IS
249 CURSOR c_operating_unit IS
250 SELECT operating_unit
251 FROM org_organization_definitions
252 WHERE organization_id = p_inventory_orgn_id;
253
254 ln_operating_unit_id ORG_ORGANIZATION_DEFINITIONS.OPERATING_UNIT%TYPE;
255
256 BEGIN
257 /*
258 || If the mandatory parameter p_calling_object is not passed, then returning
259 || false value so that no further processing needs to be done.
260 */
261 IF p_calling_object IS NULL OR p_inventory_orgn_id IS NULL THEN
262 return (-1) ;
263 END IF;
264
265 /*
266 || Based on the inventory organization info , get the associated operating unit
267 */
268 OPEN c_operating_unit;
269 FETCH c_operating_unit INTO ln_operating_unit_id;
270 CLOSE c_operating_unit;
271
272 IF ln_operating_unit_id IS NULL THEN
273 RETURN (-1);
274 ELSE
275 RETURN ( ln_operating_unit_id );
276 END IF ;
277
278 EXCEPTION
279
280 WHEN OTHERS THEN
281 RAISE_APPLICATION_ERROR ( -20001,
282 'Unexpected Error in ja_in_util.get_operating_unit, called from ' || p_calling_object || ' The error is ' || sqlerrm
283 );
284
285 END get_operating_unit ;
286
287 PROCEDURE update_rg_slno(
288 pn_organization_id IN NUMBER,
289 pn_location_id IN NUMBER,
290 pv_register_type IN VARCHAR2,
291 pn_fin_year IN NUMBER,
292 pn_txn_amt IN NUMBER,
293 pn_slno OUT NOCOPY NUMBER,
294 pn_opening_balance OUT NOCOPY NUMBER,
295 pn_closing_balance OUT NOCOPY NUMBER
296
297 ) IS
298 ln_fin_year NUMBER;
299 ln_slno NUMBER;
300 ln_closing_balance NUMBER;
301
302 /* Added by Ramananda for bug#4407165 */
303 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.update_rg_slno';
304
305
306 PROCEDURE insert_record(
307 pn_organization_id NUMBER,
308 pn_location_id NUMBER,
309 pn_current_fin_year NUMBER,
310 pv_register_type VARCHAR2)
311 IS
312 PRAGMA autonomous_transaction;
313 BEGIN
314 --Issue#76. 4627086
315 INSERT INTO JAI_CMN_RG_SLNOS
316 (organization_id, location_id, current_fin_year, register_type, slno, balance,created_by, creation_date, last_updated_by, last_update_date )
317 VALUES
318 (pn_organization_id, pn_location_id, pn_current_fin_year, pv_register_type, 0, 0, fnd_global.user_id , sysdate, fnd_global.user_id , sysdate);
319
320 COMMIT;
321
322 EXCEPTION
323 WHEN DUP_VAL_ON_INDEX THEN
324 null;
325 END insert_record;
326
327 BEGIN
328
329 UPDATE JAI_CMN_RG_SLNOS
330 SET slno = NVL(slno,0) + 1,
331 balance = NVL(balance,0) + pn_txn_amt
332 WHERE register_type = pv_register_type
333 AND organization_id = pn_organization_id
334 AND location_id = pn_location_id
335 RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
336
337 IF SQL%NOTFOUND THEN
338
339 insert_record(pn_organization_id, pn_location_id, pn_fin_year, pv_register_type);
340
341 UPDATE JAI_CMN_RG_SLNOS
342 SET slno = NVL(slno,0) + 1,
343 balance = NVL(balance,0) + pn_txn_amt
344 WHERE register_type = pv_register_type
345 AND organization_id = pn_organization_id
346 AND location_id = pn_location_id
347 RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
348
349 ELSIF ln_fin_year <> pn_fin_year THEN
350
351 UPDATE JAI_CMN_RG_SLNOS
352 SET slno = 1,
353 current_fin_year = pn_fin_year
354 WHERE register_type = pv_register_type
355 AND organization_id = pn_organization_id
356 AND location_id = pn_location_id
357 RETURNING slno, current_fin_year INTO ln_slno, ln_fin_year;
358
359 END IF;
360
361 pn_slno := ln_slno;
362 pn_opening_balance := ln_closing_balance - pn_txn_amt;
363 pn_closing_balance := ln_closing_balance;
364
365
366 /* Added by Ramananda for bug#4407165 */
367 EXCEPTION
368 WHEN OTHERS THEN
369 pn_opening_balance := null;
370 pn_closing_balance := null;
371 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
372 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
373 app_exception.raise_exception;
374
375 END update_rg_slno;
376
377
378 FUNCTION currency_conversion (c_set_of_books_id In Number,
379 c_from_currency_code In varchar2,
380 c_conversion_date in date,
381 c_conversion_type in varchar2,
382 c_conversion_rate in number) return number is
383 v_func_curr varchar2(15);
384 ret_value number;
385
386 Cursor currency_code_cur IS
387 Select currency_code from gl_sets_of_books
388 where set_of_books_id = c_set_of_books_id;
389
390 /* Added by Ramananda for bug#4407165 */
391 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.currency_conversion';
392 lv_debug VARCHAR2(1) := 'N'; /* Bug 9547863 */
393 /* The value of lv_debug can be set to 'Y' depending on whether debug is enabled */
394 /* Wrapped the print_log calls with an IF to check for lv_debug value and execute */
395
396 Begin
397 -- Bug 5148770. Added by Lakshmi Gopalsami
398
399 IF lv_debug = 'Y' THEN
400 print_log('jai_cmn_utils_pkg.currency_conversion.log',' SOB'|| c_set_of_books_id);
401 END IF;
402
403 Open currency_code_cur;
404 Fetch currency_code_cur Into v_func_curr;
405 Close currency_code_cur;
406
407 -- Bug 5148770. Added by Lakshmi Gopalsami
408
409
410 IF lv_debug = 'Y' THEN
411 print_log('jai_cmn_utils_pkg.currency_conversion.log',' Func curr '|| v_func_curr);
412 print_log('jai_cmn_utils_pkg.currency_conversion.log', 'FROM curr code '|| c_from_currency_code);
413 END IF;
414
415 If NVL(v_func_curr,'NO') = c_from_currency_code Then
416 -- Bug 5148770. Added by Lakshmi Gopalsami
417
418 IF lv_debug = 'Y' THEN
419 print_log('jai_cmn_utils_pkg.currency_conversion.log',
420 ' func curr and from curr same - return 1');
421 END IF;
422
423 ret_value := 1;
424
425 Elsif upper(c_conversion_type) = 'USER' Then
426 -- Bug 5148770. Added by Lakshmi Gopalsami
427
428 IF lv_debug = 'Y' THEN
429 print_log('jai_cmn_utils_pkg.currency_conversion.log',
430 ' User entered the rate - return '|| c_conversion_rate);
431 END IF;
432
433 ret_value := c_conversion_rate;
434
435 Else
436
437 Declare
438
439 v_frm_curr Varchar2(10) := c_from_currency_code ; -- added by Subbu, Sri on 02-NOV-2000
440
441 v_dr_type Varchar2(20); -- added by Subbu, Sri on 02-NOV-2000
442
443 -- Cursor for checking currency whether derived from Euro Derived / Euro Currency or not
444 -- added by Subbu, Sri on 02-NOV-2000
445
446 CURSOR Chk_Derived_Type_Cur IS SELECT Derive_type FROM Fnd_Currencies
447 WHERE Currency_Code in (v_frm_curr);
448 /* Bug 5148770. Added by Lakshmi Gopalsami
449 Changed the select to get the rate into cursor.
450 */
451
452 /*Commented use of cursor bug mmurtua for bug 16456183*/
453 /*CURSOR get_curr_rate(p_to_curr IN varchar2,
454 p_from_curr IN varchar2) IS
455 SELECT Conversion_Rate
456 FROM Gl_Daily_Rates
457 WHERE To_Currency = p_to_curr
458 and From_Currency = p_from_curr
459 and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
460 and Conversion_Type = c_conversion_type;*/
461
462 Begin
463
464 OPEN Chk_Derived_Type_Cur;
465 FETCH Chk_Derived_Type_Cur INTO v_dr_type;
466 CLOSE Chk_Derived_Type_Cur;
467
468 -- Bug 5148770. Added by Lakshmi Gopalsami
469
470 IF lv_debug = 'Y' THEN
471 print_log('jai_cmn_utils_pkg.currency_conversion.log',
472 ' derived type '|| v_dr_type);
473 END IF;
474
475 IF v_dr_type IS NULL THEN
476
477 -- If currency is not derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
478 /* Bug 5148770. Added by Lakshmi Gopalsami
479 Removed the select and changed the same into a cursor.
480 */
481
482 /*Commented use of cursor and used select statement bug mmurtua for bug 16456183*/
483
484 /*OPEN get_curr_rate(v_func_curr,v_frm_curr);
485 FETCH get_curr_rate INTO ret_value;
486 CLOSE get_curr_rate;*/
487
488 SELECT Conversion_Rate INTO ret_value
489 FROM Gl_Daily_Rates
490 WHERE To_Currency = v_func_curr
491 and From_Currency = v_frm_curr
492 and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
493 and Conversion_Type = c_conversion_type;
494
495 -- Bug 5148770. Added by Lakshmi Gopalsami
496
497 IF lv_debug = 'Y' THEN
498 print_log('jai_cmn_utils_pkg.currency_conversion.log',
499 ' derive type null - return value '|| ret_value);
500 END IF;
501
502 ELSE
503
504 IF v_dr_type in('EMU','EURO') THEN
505
506 -- If currency is derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
507
508 v_frm_curr := 'EUR';
509
510 /* Bug 5148770. Added by Lakshmi Gopalsami
511 Removed the select and changed the same into a cursor.
512 */
513
514 /*Commented use of cursor and used select statement bug mmurtua for bug 16456183*/
515
516 /*OPEN get_curr_rate(v_func_curr,v_frm_curr);
517 FETCH get_curr_rate INTO ret_value;
518 CLOSE get_curr_rate;*/
519
520 SELECT Conversion_Rate INTO ret_value
521 FROM Gl_Daily_Rates
522 WHERE To_Currency = v_func_curr
523 and From_Currency = v_frm_curr
524 and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
525 and Conversion_Type = c_conversion_type;
526
527 -- Bug 5148770. Added by Lakshmi Gopalsami
528
529 IF lv_debug = 'Y' THEN
530 print_log('jai_cmn_utils_pkg.currency_conversion.log',
531 ' EURO/EMU - derive type - return value '|| ret_value);
532 END IF;
533
534 END IF;
535
536 END IF;
537
538 Exception When Others Then
539 --old code ret_value := 1;
540 RAISE_APPLICATION_ERROR(-20120,'Currency Conversion Rate Not Defined In The System');
541 End;
542 End If;
543 -- Modified by Jia for bug#9736876, Begin
544 ----------------------------------------------
545 -- Return(nvl(ret_value,1)); --Commented by Jia for bug#9736876
546 Return (ret_value);
547 ----------------------------------------------
548 -- Modified by Jia for bug#9736876, End
549
550
551
552 /* Added by Ramananda for bug#4407165 */
553 EXCEPTION
554 WHEN OTHERS THEN
555 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
556 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
557 app_exception.raise_exception;
558
559 End currency_conversion;
560
561
562 PROCEDURE print_log (
563 filename VARCHAR2,
564 text_to_write VARCHAR2
565 ) IS
566 v_myfilehandle UTL_FILE.FILE_TYPE;
567 v_utl_location VARCHAR2(40) ;
568 lv_name varchar2(30);
569 BEGIN
570 lv_name := 'utl_file_dir'; --rchandan for bug#4428980
571 SELECT
572 decode(substr (value,1,instr(value,',') -1) ,
573 null ,
574 value ,
575 substr (value,1,instr(value,',') -1))
576 INTO
577 v_utl_location
578 FROM
579 v$parameter
580 WHERE
581 name = lv_name; --rchandan for bug#4428980
582
583 v_myfilehandle := utl_file.fopen(v_utl_location,filename,'A');
584
585 utl_file.put_line(v_myfilehandle,text_to_write);
586
587 utl_file.fclose(v_myfilehandle);
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 Null;
592 END print_log;
593 --rchandan for bug#4428980
594 --As part OF R12 Inititive Inventory conversion the OPM code IS commented
595 /*FUNCTION opm_uom_version(from_uom varchar2,to_uom varchar2,p_item_id number) RETURN NUMBER IS
596 f_uom_type varchar2(20);
597 t_uom_type varchar2(20);
598 f_ref_um varchar2(20);
599 t_ref_um varchar2(20);
600 l_std_factor number;
601 l_std_factor_t number;
602 l_std_factor_typ_1 number;
603 l_item_um varchar2(20);
604 x number;
605 y number;
606 z number;
607 CURSOR C_Uom_Type(p_uom_code varchar2) IS Select upper(um_type),upper(ref_um) From sy_uoms_mst
608 Where upper(um_code) = upper(p_uom_code);
609 CURSOR C_Conv_Val(p_uom_code varchar2) IS
610 Select std_factor From sy_uoms_mst
611 Where um_code = p_uom_code;
612 CURSOR C_Uom_Type_Conv(p_uom_type varchar2) IS
613 Select type_factor From ic_item_cnv
614 Where um_type = p_uom_type and
615 item_id = p_item_id;
616 CURSOR C_Item_Um(p_item_id number) IS
617 Select item_um From ic_item_mst
618 Where item_id = p_item_id;
619
620 Added by Ramananda for bug#4407165
621 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.opm_uom_version';
622
623 BEGIN
624 OPEN C_Uom_Type(from_uom);
625 FETCH C_Uom_Type INTO f_uom_type,f_ref_um;
626 CLOSE C_Uom_Type;
627 OPEN C_Uom_Type(to_uom);
628 FETCH C_Uom_Type INTO t_uom_type,t_ref_um;
629 CLOSE C_Uom_Type;
630 OPEN C_Conv_Val(from_uom);
631 FETCH C_Conv_Val INTO l_std_factor;
632 CLOSE C_Conv_Val;
633 OPEN C_Conv_Val(to_uom);
634 FETCH C_Conv_Val INTO l_std_factor_t;
635 CLOSE C_Conv_Val;
636 IF f_uom_type = t_uom_type THEN
637 IF from_uom = to_uom THEN
638 return(1);
639 ELSIF from_uom = f_ref_um THEN
640 return(1/l_std_factor_t);
641 ELSIF to_uom = t_ref_um THEN
642 return(l_std_factor);
643 ELSE
644
645 return(round((l_std_factor/l_std_factor_t),9));
646 END IF;
647 ELSE
648 OPEN C_Uom_Type_Conv(t_uom_type);
649 FETCH C_Uom_Type_Conv INTO l_std_factor_typ_1;
650 CLOSE C_Uom_Type_Conv;
651 OPEN C_Item_Um(p_item_id);
652 FETCH C_Item_Um INTO l_item_um;
653 CLOSE C_Item_Um;
654 IF l_item_um = from_uom THEN
655 IF from_uom = f_ref_um AND to_uom = t_ref_um THEN
656 return(l_std_factor_typ_1);
657 ELSIF from_uom = f_ref_um AND to_uom <> t_ref_um THEN
658 return(round((1/(l_std_factor_typ_1*l_std_factor_t)),9));
659 ELSIF to_uom = t_ref_um AND from_uom <> f_ref_um THEN
660 return(round((l_std_factor/l_std_factor_typ_1),2));
661 ELSE
662 return(round((l_std_factor/(l_std_factor_typ_1*l_std_factor_t)),9));
663 END IF;
664 ELSIF l_item_um = to_uom THEN
665 x:= jai_cmn_utils_pkg.opm_uom_version(to_uom,from_uom,p_item_id);
666 IF x = 0 THEN
667 return(x);
668 ELSE
669 return(1/x);
670 END IF;
671 ELSE
672 x := jai_cmn_utils_pkg.opm_uom_version(l_item_um,from_uom,p_item_id);
673 y := jai_cmn_utils_pkg.opm_uom_version(l_item_um,to_uom,p_item_id);
674 IF x > 0 THEN
675 z := y/x;
676 ELSE
677 z := 0;
678 END IF;
679 return(z);
680 END IF;
681 END IF;
682
683
684 -- Added by Ramananda for bug#4407165
685 EXCEPTION
686 WHEN OTHERS THEN
687 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
688 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
689 app_exception.raise_exception;
690
691 End opm_uom_version;
692
693
694 FUNCTION get_opm_assessable_value(p_item_id number,p_qty number,p_exted_price number,P_Cust_Id Number Default 0 ) RETURN NUMBER IS
695 Cursor C_Item_Dtl IS
696 Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
697 From JAI_OPM_ITM_MASTERS
698 Where item_id = p_item_id;
699
700 -- Added by Ramananda for bug#4407165
701 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.get_opm_assessable_value';
702
703 ---Added For OPM Localization By A.Raina on 22-02-2000
704 ---Code Added For Fetching the Assessable_value at the customer level
705
706 Cursor C_Price_list_id is
707 Select Pricelist_Id
708 From JAI_OPM_CUSTOMERS
709 Where Cust_id = p_cust_id ;
710
711 Cursor C_Cust_Ass_Value ( p_Pricelist_Id In Number ) is
712 Select a.Base_Price
713 From Op_Prce_Itm a ,op_prce_eff b
714 Where a.pricelist_id = b.pricelist_id
715 And a.Pricelist_Id = p_Pricelist_id
716 And a.Item_Id = p_item_id
717 And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
718
719 CURSOR C_item_Ass_Value IS
720 Select assessable_value
721 From JAI_OPM_ITM_MASTERS
722 Where item_id = p_item_id;
723
724 v_pricelist_id Number;
725 v_assessable_flag char(1) ;
726 --End Addition
727 l_assessable_val number;
728 l_excise_cal varchar2(1);
729 BEGIN
730
731 ---Added For OPM Localization By A.Raina on 22-02-2000
732 ---Code Added For Fetching the Assessable_value at the customer level
733
734 OPEN C_Price_list_id ;
735 FETCH C_Price_list_id into v_pricelist_id;
736 CLOSE C_Price_list_id ;
737
738 l_assessable_val := Null ;
739 IF v_pricelist_id is Not Null Then
740 OPEN C_Cust_Ass_Value (v_pricelist_id ) ;
741 FETCH C_Cust_Ass_Value into l_assessable_val ;
742 CLOSE C_Cust_Ass_Value ;
743 End If;
744 IF l_assessable_val Is Null Then
745 OPEN C_item_Ass_Value ;
746 FETCH C_item_Ass_Value into l_assessable_val ;
747 CLOSE C_item_Ass_Value ;
748 End If;
749
750 ---End Addition
751
752 OPEN C_Item_Dtl;
753 FETCH C_Item_Dtl INTO l_excise_cal ; -- l_assessable_val (Commented as Assessable Value is picked by other conditions now )
754 CLOSE C_Item_Dtl ;
755
756 IF NVL(l_excise_cal,'N') = 'Y' THEN
757 Return(l_assessable_val*p_qty);
758 ELSE
759 Return(p_exted_price);
760 END IF;
761
762 -- Added by Ramananda for bug#4407165
763 EXCEPTION
764 WHEN OTHERS THEN
765 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
766 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
767 app_exception.raise_exception;
768
769 END get_opm_assessable_value;*/
770
771
772 procedure get_le_info
773 (
774 p_api_version IN NUMBER ,
775 p_init_msg_list IN VARCHAR2 ,
776 p_commit IN VARCHAR2 ,
777 p_ledger_id IN NUMBER ,
778 p_bsv IN VARCHAR2 ,
779 p_org_id IN NUMBER ,
780 x_return_status OUT NOCOPY VARCHAR2 ,
781 x_msg_count OUT NOCOPY NUMBER ,
782 x_msg_data OUT NOCOPY VARCHAR2 ,
783 x_legal_entity_id OUT NOCOPY NUMBER ,
784 x_legal_entity_name OUT NOCOPY VARCHAR2
785 )
786 IS
787 ln_legal_entity_id NUMBER;
788 CURSOR c_get_le_info is
789 SELECT XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_org_id)
790 FROM dual;
791
792 /* Bug 4906958. Added by Lakshmi Gopalsami
793 Get the value of legal entity from hr_operating_units
794 if the above cursor is returning null.
795 */
796 /* Bug 5243532. Added by Lakshmi Gopalsami
797 Removed the cursor c_get_default_LE_id which
798 is referring to hr_operating_units
799 and replaced with caching logic.
800 Defined variable for implementing caching logic.
801 */
802
803 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
804
805 BEGIN
806
807 IF p_ledger_id IS NOT NULL AND p_bsv IS NOT NULL THEN
808
809
810 XLE_UTILITIES_GRP.Get_LegalEntity_LGER_BSV
811 (
812 p_api_version ,
813 p_init_msg_list ,
814 p_commit ,
815 x_return_status ,
816 x_msg_count ,
817 x_msg_data ,
818 p_ledger_id ,
819 p_bsv ,
820 x_legal_entity_id ,
821 x_legal_entity_name
822 );
823
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825
826 RETURN;
827
828 END IF;
829
830 IF p_org_id IS NOT NULL THEN
831 OPEN c_get_le_info;
832 FETCH c_get_le_info INTO ln_legal_entity_id;
833 CLOSE c_get_le_info;
834
835 /* Bug 4906958. Added by Lakshmi Gopalsami
836 If ln_legal_entity_id is null fetch the legal entity id
837 from the default_legal_context_id from hr_operating_units
838 */
839
840 /* Bug 5243532. Added by Lakshmi Gopalsami
841 Removed the cursor c_get_default_LE_id and implemented using
842 cache logic.
843 */
844
845 If nvl(ln_legal_entity_id,-1) = -1 THEN
846 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
847 (p_org_id => p_org_id);
848 ln_legal_entity_id := l_func_curr_det.legal_entity;
849
850 END IF;
851
852 x_legal_entity_id := ln_legal_entity_id;
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854 RETURN;
855 END IF;
856
857
858 x_return_status := FND_API.G_RET_STS_ERROR;
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863
864 END get_le_info;
865
866
867 FUNCTION validate_po_type(p_po_type IN VARCHAR2 DEFAULT NULL,
868 p_style_id IN NUMBER DEFAULT NULL,
869 p_po_header_id IN NUMBER DEFAULT NULL
870 ) RETURN BOOLEAN
871
872 IS
873 CURSOR c_doc_style_headers(cp_style_id NUMBER)
874 IS
875 SELECT progress_payment_flag
876 FROM po_doc_style_headers
877 WHERE style_id = cp_style_id;
878
879 CURSOR c_po_headers(cp_po_header_id NUMBER)
880 IS
881 SELECT style_id
882 FROM po_headers_all
883 WHERE po_header_id = cp_po_header_id;
884
885 r_doc_style_headers c_doc_style_headers%ROWTYPE;
886 r_po_headers c_po_headers%ROWTYPE;
887 b_process_po BOOLEAN;
888 v_style_id po_headers_all.style_id%TYPE;
889 BEGIN
890 v_style_id := p_style_id;
891
892 IF v_style_id IS NULL AND p_po_header_id IS NOT NULL THEN
893 OPEN c_po_headers(p_style_id);
894 FETCH c_po_headers INTO r_po_headers;
895 CLOSE c_po_headers;
896
897 v_style_id := r_po_headers.style_id;
898 END IF;
899
900 OPEN c_doc_style_headers(v_style_id);
901 FETCH c_doc_style_headers INTO r_doc_style_headers;
902 CLOSE c_doc_style_headers;
903
904 --if progess_payment_flag = 'Y', then it is a complex work PO
905 IF NVL(r_doc_style_headers.progress_payment_flag,'N') = 'Y' THEN
906 b_process_po := FALSE;
907 ELSE
908 b_process_po := TRUE;
909 END IF;
910
911 RETURN b_process_po;
912 END validate_po_type;
913
914
915 --==========================================================================
916 -- FUNCTION NAME:
917 --
918 -- if_IL_drilldown Public
919 --
920 -- DESCRIPTION:
921 --
922 -- This function is used to Enable/Disable drilldown buttion
923 -- according OFI journal source and journal categories.
924 --
925 -- PARAMETERS:
926 -- In: pn_je_source Identifier of journal source
927 -- pn_je_category Identifier of journal category
928 --
929 --
930 -- DESIGN REFERENCES:
931 -- FDD_R12_1_4_GL_Drilldown_V0.4.docx
932 --
933 -- CHANGE HISTORY:
934 --
935 -- 09-Mar-2010 Jia Li created
936 --==========================================================================
937 FUNCTION if_IL_drilldown (
938 pn_je_source VARCHAR2
939 , pn_je_category VARCHAR2
940 ) RETURN BOOLEAN
941 IS
942 lb_drilldown_flag BOOLEAN := FALSE;
943 lv_je_source varchar2(100);
944 lv_je_category varchar2(100);
945
946 CURSOR get_user_source IS
947 select user_je_source_name
948 from gl_je_sources gjs
949 where gjs.je_source_name = pn_je_source;
950
951 CURSOR get_user_category IS
952 select user_je_category_name
953 from gl_je_categories gjc
954 where gjc.je_category_name = pn_je_category;
955
956 BEGIN
957
958 OPEN get_user_source;
959 FETCH get_user_source INTO lv_je_source;
960 CLOSE get_user_source;
961
962 OPEN get_user_category;
963 FETCH get_user_category INTO lv_je_category;
964 CLOSE get_user_category;
965
966 IF ((lv_je_source = 'Receivables India') AND (lv_je_category = 'Register India'))
967 OR
968 ((lv_je_source = 'Projects India') AND (lv_je_category = 'Register India'))
969 OR
970 ((lv_je_source = 'Inventory India') AND (lv_je_category = 'MTL'))
971 OR
972 ((lv_je_source = 'Payables India') AND (lv_je_category IN ('Bill of Entry India','Payments')))
973 OR
974 ((lv_je_source = 'Payables') AND (lv_je_category = 'BOE'))
975 OR
976 ((lv_je_source = 'Purchasing India') AND (lv_je_category IN ('Receiving India','OSP Issue India','OSP Receipt India', 'MMT')))
977 OR
978 ((lv_je_source = 'Register India') AND (lv_je_category IN ('Inventory India','VAT India','Register India')))
979 OR
980 ((lv_je_source IN ('VAT India','Service Tax India')) AND (lv_je_category = 'Register India'))
981 OR
982 ((lv_je_source = 'India Tax Collected') AND (lv_je_category = 'Receivalbes India'))
983 THEN
984 lb_drilldown_flag := TRUE;
985 END IF;
986
987 RETURN ( lb_drilldown_flag );
988
989 END if_IL_drilldown;
990
991 PROCEDURE WRITE_FND_LOG_MSG (
992 module IN VARCHAR2,
993 message IN VARCHAR2
994 ) IS
995
996 ln_log_level NUMBER := 6;
997
998 BEGIN
999
1000 IF (ln_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1001 THEN
1002
1003 fnd_log.STRING(log_level => ln_log_level
1004 ,module => module
1005 ,message => message );
1006
1007 END IF;
1008
1009
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 Null;
1013 END WRITE_FND_LOG_MSG;
1014
1015
1016 --==========================================================================
1017 -- FUNCTION NAME:
1018 -- WRITE_FND_LOG Public
1019 --
1020 -- DESCRIPTION:
1021 -- Write FND log with given log level.
1022 --
1023 -- PARAMETERS:
1024 -- In: pn_message_level Given log level
1025 -- pv_module module name
1026 -- pv_message fnd log message contents
1027 --
1028 -- DESIGN REFERENCES:
1029 --
1030 -- CHANGE HISTORY:
1031 -- 15-JUN-2012 Chong created
1032 --==========================================================================
1033 PROCEDURE WRITE_FND_LOG(
1034 pn_message_level IN NUMBER DEFAUlt 1, --FND log level, default 1: statument level
1035 pv_module IN VARCHAR2,
1036 pv_message IN VARCHAR2
1037 ) IS
1038
1039 ln_log_level NUMBER := 6;
1040
1041 BEGIN
1042
1043 IF (pn_message_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044
1045 FND_LOG.STRING(log_level => pn_message_level
1046 ,module => pv_module
1047 ,message => pv_message );
1048 END IF;
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 Null;
1053 END WRITE_FND_LOG;
1054
1055 --==========================================================================
1056 -- FUNCTION NAME:
1057 -- IS_ACCRUAL_BASIS Public
1058 --
1059 -- DESCRIPTION:
1060 -- Compare given gl date with service tax accrual date, if given gl date
1061 -- before service tax accrual date, then return false, else return true.
1062 --
1063 -- PARAMETERS:
1064 -- In: pn_regime_id
1065 -- pn_organization_id
1066 -- pn_location_id
1067 -- pd_gl_date
1068 --
1069 -- DESIGN REFERENCES:
1070 --
1071 -- CHANGE HISTORY:
1072 -- 10-JUN-2012 Qinglei created
1073 --==========================================================================
1074 FUNCTION IS_ACCRUAL_BASIS(pn_regime_id NUMBER,
1075 pn_organization_id NUMBER,
1076 pn_location_id NUMBER,
1077 pd_gl_date DATE) RETURN BOOLEAN
1078 IS
1079 lv_api_name VARCHAR2(100) := 'IS_ACCRUAL_BASIS';
1080 lb_is_accrual_basis BOOLEAN := FALSE;
1081 ld_st_accrual_date DATE;
1082 lv_debug_info VARCHAR2(2000);
1083
1084 CURSOR c_get_st_accrual_date(p_regime_id NUMBER, p_organization_id NUMBER, p_location_id NUMBER) IS
1085 SELECT to_date(attribute_value, 'DD/MM/YYYY')
1086 FROM JAI_RGM_ORG_REGNS_V
1087 WHERE regime_id = p_regime_id
1088 AND organization_id = p_organization_id
1089 AND location_id = p_location_id
1090 AND attribute_code = 'EFF_DATE_ST_PT'
1091 AND attribute_type_code = 'OTHERS'
1092 AND registration_type = 'OTHERS'
1093 AND (NOT EXISTS
1094 (SELECT '1'
1095 FROM JAI_RGM_ORG_REGNS_V
1096 WHERE regime_id = p_regime_id
1097 AND attribute_code IN 'INV_ORG_CLASSIFICATION'
1098 AND attribute_value <> 'ORGANIZATION'
1099 AND organization_id = p_organization_id
1100 AND location_id = p_location_id)
1101 OR
1102 NOT EXISTS
1103 (SELECT '1'
1104 FROM JAI_RGM_ORG_REGNS_V
1105 WHERE regime_id = p_regime_id
1106 AND attribute_code IN 'SERVICE TYPE'
1107 AND attribute_value <> 'OTHER'
1108 AND organization_id = p_organization_id
1109 AND location_id = p_location_id)
1110 );
1111
1112 BEGIN
1113 lv_debug_info := 'pn_regime_id = '||pn_regime_id||', pn_organization_id = '||pn_organization_id ||
1114 ', pn_location_id = '|| pn_location_id || ', pd_gl_date = '|| pd_gl_date;
1115
1116 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1117 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||lv_api_name,lv_debug_info);
1118 END IF;
1119
1120 OPEN c_get_st_accrual_date(pn_regime_id,pn_organization_id,pn_location_id);
1121 FETCH c_get_st_accrual_date INTO ld_st_accrual_date;
1122 CLOSE c_get_st_accrual_date;
1123
1124 lv_debug_info := 'pd_gl_date = '|| pd_gl_date||', ld_st_accrual_date = '||ld_st_accrual_date;
1125
1126 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1127 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||lv_api_name,lv_debug_info);
1128 END IF;
1129
1130 IF pd_gl_date >= ld_st_accrual_date THEN
1131 lb_is_accrual_basis := TRUE;
1132 END IF;
1133
1134 RETURN lb_is_accrual_basis;
1135 END IS_ACCRUAL_BASIS;
1136
1137 END jai_cmn_utils_pkg;
[Home] [Help]