1 PACKAGE BODY jai_cmn_utils_pkg AS
2 /* $Header: jai_cmn_utils.plb 120.8 2007/05/18 12:16:51 bgowrava 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 FUNCTION get_currency_code(p_operating_unit_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE) RETURN VARCHAR2 IS
93 /* Bug 5243532. Added by Lakshmi Gopalsami.
94 Removed the cursor c_set_of_books and c_sob_currency
95 and implemented using caching logic.
96 */
97
98 ln_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE;
99 lv_sob_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE ;
100
101 /* Bug 5243532. Added by Lakshmi Gopalsami.
102 Defined local variable for implementing caching logic.
103 */
104 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
105
106 BEGIN
107 /* Bug 5243532. Added by Lakshmi Gopalsami
108 Removed the reference to cursor and implemented
109 caching logic.
110 */
111 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
112 (p_org_id => p_operating_unit_id );
113
114 ln_set_of_books_id := l_func_curr_det.ledger_id;
115 lv_sob_currency_code := l_func_curr_det.currency_code;
116
117 RETURN(lv_sob_currency_code);
118 END get_currency_code;
119
120 FUNCTION check_jai_exists(p_calling_object VARCHAR2 ,
121 p_inventory_orgn_id HR_ORGANIZATION_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
122 p_org_id HR_OPERATING_UNITS.ORGANIZATION_ID%TYPE DEFAULT NULL ,
123 p_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE DEFAULT NULL ,
124 p_value_string VARCHAR2 DEFAULT NULL ,
125 p_format_string VARCHAR2 DEFAULT NULL
126 ) RETURN BOOLEAN
127 IS
128 /* Bug 5243532. Added by Lakshmi Gopalsami
129 Removed the cursor c_set_of_books and c_operating_unit
130 which is referring to hr_operating_units
131 and org_organization_definitions. Replaced the same with caching logic.
132 Replaced gl_sets_of_books with gl_ledgers
133 */
134 CURSOR c_sob_currency (cp_set_of_books_id GL_SETS_OF_BOOKS.SET_OF_BOOKS_ID%TYPE) IS
135 SELECT currency_code
136 FROM gl_ledgers
137 WHERE ledger_id = cp_set_of_books_id;
138
139 lv_sob_currency_code GL_SETS_OF_BOOKS.CURRENCY_CODE%TYPE;
140 lv_calling_object VARCHAR2(50);
141 lv_message_text VARCHAR2(3000);
142
143 /* Bug 5243235. Added by Lakshmi Gopalsami
144 Defined variable for implementing caching logic.
145 */
146 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
147
148 BEGIN
149 /*
150 If the mandatory parameter p_calling_object is not passed, then returning false value so that no further processing needs
151 to be done.
152 */
153
154 IF p_calling_object IS NULL THEN
155 return(false);
156 END IF;
157
158 /*
159 set of books id is passed. Get the currency code from the gl_sets_of_books and return true or false
160 depending on the curreny code = 'INR' or not.
161 */
162 IF p_set_of_books_id IS NOT NULL THEN
163 OPEN c_sob_currency(p_set_of_books_id);
164 FETCH c_sob_currency INTO lv_sob_currency_code;
165 CLOSE c_sob_currency;
166
167 IF lv_sob_currency_code = 'INR' THEN
168 RETURN(TRUE);
169 ELSE
170 RETURN(FALSE);
171 END IF;
172 END IF;
173
174 /* Bug 5243532. Added by Lakshmi Gopalsami
175 Removed the existing code for deriving the SOB.
176 Implemented using caching logic.
177 */
178 IF p_org_id IS NOT NULL THEN
179 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
180 (p_org_id => p_org_id );
181
182 ELSIF p_inventory_orgn_id IS NOT NULL THEN
183 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
184 (p_org_id => p_inventory_orgn_id );
185 END IF;
186 lv_sob_currency_code := l_func_curr_det.currency_code;
187 IF lv_sob_currency_code = 'INR' THEN
188 RETURN(TRUE);
189 ELSE
190 RETURN(FALSE);
191 END IF;
192
193 /*
194 The final return(false) below is to trap the case where none of the parameters p_inventory_orgn_id, p_org_id
195 or p_set_of_books_id is passed.
196 */
197
198 RETURN(FALSE);
199 EXCEPTION
200 WHEN OTHERS THEN
201 RAISE_APPLICATION_ERROR(-20001,'Unexpected Error in ja_in_util.check_jai_exists, called from ' || p_calling_object || ' The error is ' || sqlerrm);
202 END check_jai_exists;
203 /*5039365 by ssawant*/
204 FUNCTION return_valid_date( p_validate_text VARCHAR2 ) RETURN DATE IS
205 ld_ret_value DATE;
206 BEGIN
207 ld_ret_value := to_date( p_validate_text,'DD/MM/RRRR');
208 return ld_ret_value;
209 EXCEPTION
210 when others then
211 return NULL;
212 END return_valid_date;
213
214 FUNCTION get_operating_unit (
215 p_calling_object VARCHAR2 ,
216 p_inventory_orgn_id ORG_ORGANIZATION_DEFINITIONS.ORGANIZATION_ID%TYPE
217 ) RETURN NUMBER
218 IS
219 CURSOR c_operating_unit IS
220 SELECT operating_unit
221 FROM org_organization_definitions
222 WHERE organization_id = p_inventory_orgn_id;
223
224 ln_operating_unit_id ORG_ORGANIZATION_DEFINITIONS.OPERATING_UNIT%TYPE;
225
226 BEGIN
227 /*
228 || If the mandatory parameter p_calling_object is not passed, then returning
229 || false value so that no further processing needs to be done.
230 */
231 IF p_calling_object IS NULL OR p_inventory_orgn_id IS NULL THEN
232 return (-1) ;
233 END IF;
234
235 /*
236 || Based on the inventory organization info , get the associated operating unit
237 */
238 OPEN c_operating_unit;
239 FETCH c_operating_unit INTO ln_operating_unit_id;
240 CLOSE c_operating_unit;
241
242 IF ln_operating_unit_id IS NULL THEN
243 RETURN (-1);
244 ELSE
245 RETURN ( ln_operating_unit_id );
246 END IF ;
247
248 EXCEPTION
249
250 WHEN OTHERS THEN
251 RAISE_APPLICATION_ERROR ( -20001,
252 'Unexpected Error in ja_in_util.get_operating_unit, called from ' || p_calling_object || ' The error is ' || sqlerrm
253 );
254
255 END get_operating_unit ;
256
257 PROCEDURE update_rg_slno(
258 pn_organization_id IN NUMBER,
259 pn_location_id IN NUMBER,
260 pv_register_type IN VARCHAR2,
261 pn_fin_year IN NUMBER,
262 pn_txn_amt IN NUMBER,
263 pn_slno OUT NOCOPY NUMBER,
264 pn_opening_balance OUT NOCOPY NUMBER,
265 pn_closing_balance OUT NOCOPY NUMBER
266
267 ) IS
268 ln_fin_year NUMBER;
269 ln_slno NUMBER;
270 ln_closing_balance NUMBER;
271
272 /* Added by Ramananda for bug#4407165 */
273 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.update_rg_slno';
274
275
276 PROCEDURE insert_record(
277 pn_organization_id NUMBER,
278 pn_location_id NUMBER,
279 pn_current_fin_year NUMBER,
280 pv_register_type VARCHAR2)
281 IS
282 PRAGMA autonomous_transaction;
283 BEGIN
284 --Issue#76. 4627086
285 INSERT INTO JAI_CMN_RG_SLNOS
286 (organization_id, location_id, current_fin_year, register_type, slno, balance,created_by, creation_date, last_updated_by, last_update_date )
287 VALUES
288 (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);
289
290 COMMIT;
291
292 EXCEPTION
293 WHEN DUP_VAL_ON_INDEX THEN
294 null;
295 END insert_record;
296
297 BEGIN
298
299 UPDATE JAI_CMN_RG_SLNOS
300 SET slno = NVL(slno,0) + 1,
301 balance = NVL(balance,0) + pn_txn_amt
302 WHERE register_type = pv_register_type
303 AND organization_id = pn_organization_id
304 AND location_id = pn_location_id
305 RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
306
307 IF SQL%NOTFOUND THEN
308
309 insert_record(pn_organization_id, pn_location_id, pn_fin_year, pv_register_type);
310
311 UPDATE JAI_CMN_RG_SLNOS
312 SET slno = NVL(slno,0) + 1,
313 balance = NVL(balance,0) + pn_txn_amt
314 WHERE register_type = pv_register_type
315 AND organization_id = pn_organization_id
316 AND location_id = pn_location_id
317 RETURNING slno, balance, current_fin_year INTO ln_slno, ln_closing_balance, ln_fin_year;
318
319 ELSIF ln_fin_year <> pn_fin_year THEN
320
321 UPDATE JAI_CMN_RG_SLNOS
322 SET slno = 1,
323 current_fin_year = pn_fin_year
324 WHERE register_type = pv_register_type
325 AND organization_id = pn_organization_id
326 AND location_id = pn_location_id
327 RETURNING slno, current_fin_year INTO ln_slno, ln_fin_year;
328
329 END IF;
330
331 pn_slno := ln_slno;
332 pn_opening_balance := ln_closing_balance - pn_txn_amt;
333 pn_closing_balance := ln_closing_balance;
334
335
336 /* Added by Ramananda for bug#4407165 */
337 EXCEPTION
338 WHEN OTHERS THEN
339 pn_opening_balance := null;
340 pn_closing_balance := null;
341 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
342 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
343 app_exception.raise_exception;
344
345 END update_rg_slno;
346
347
348 FUNCTION currency_conversion (c_set_of_books_id In Number,
349 c_from_currency_code In varchar2,
350 c_conversion_date in date,
351 c_conversion_type in varchar2,
352 c_conversion_rate in number) return number is
353 v_func_curr varchar2(15);
354 ret_value number;
355
356 Cursor currency_code_cur IS
357 Select currency_code from gl_sets_of_books
358 where set_of_books_id = c_set_of_books_id;
359
360 /* Added by Ramananda for bug#4407165 */
361 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.currency_conversion';
362
363 Begin
364 -- Bug 5148770. Added by Lakshmi Gopalsami
365
366 print_log('jai_cmn_utils_pkg.currency_conversion.log',' SOB'|| c_set_of_books_id);
367
368 Open currency_code_cur;
369 Fetch currency_code_cur Into v_func_curr;
370 Close currency_code_cur;
371
372 -- Bug 5148770. Added by Lakshmi Gopalsami
373
374 print_log('jai_cmn_utils_pkg.currency_conversion.log',' Func curr '|| v_func_curr);
375 print_log('jai_cmn_utils_pkg.currency_conversion.log', 'FROM curr code '|| c_from_currency_code);
376
377 If NVL(v_func_curr,'NO') = c_from_currency_code Then
378 -- Bug 5148770. Added by Lakshmi Gopalsami
379 print_log('jai_cmn_utils_pkg.currency_conversion.log',
380 ' func curr and from curr same - return 1');
381
382 ret_value := 1;
383
384 Elsif upper(c_conversion_type) = 'USER' Then
385 -- Bug 5148770. Added by Lakshmi Gopalsami
386 print_log('jai_cmn_utils_pkg.currency_conversion.log',
387 ' User entered the rate - return '|| c_conversion_rate);
388 ret_value := c_conversion_rate;
389
390 Else
391
392 Declare
393
394 v_frm_curr Varchar2(10) := c_from_currency_code ; -- added by Subbu, Sri on 02-NOV-2000
395
396 v_dr_type Varchar2(20); -- added by Subbu, Sri on 02-NOV-2000
397
398 -- Cursor for checking currency whether derived from Euro Derived / Euro Currency or not
399 -- added by Subbu, Sri on 02-NOV-2000
400
401 CURSOR Chk_Derived_Type_Cur IS SELECT Derive_type FROM Fnd_Currencies
402 WHERE Currency_Code in (v_frm_curr);
403 /* Bug 5148770. Added by Lakshmi Gopalsami
404 Changed the select to get the rate into cursor.
405 */
406 CURSOR get_curr_rate(p_to_curr IN varchar2,
407 p_from_curr IN varchar2) IS
408 SELECT Conversion_Rate
409 FROM Gl_Daily_Rates
410 WHERE To_Currency = p_to_curr
411 and From_Currency = p_from_curr
412 and trunc(Conversion_Date) = trunc(nvl(c_conversion_date,sysdate))
413 and Conversion_Type = c_conversion_type;
414
415 Begin
416
417 OPEN Chk_Derived_Type_Cur;
418 FETCH Chk_Derived_Type_Cur INTO v_dr_type;
419 CLOSE Chk_Derived_Type_Cur;
420
421 -- Bug 5148770. Added by Lakshmi Gopalsami
422 print_log('jai_cmn_utils_pkg.currency_conversion.log',
423 ' derived type '|| v_dr_type);
424
425 IF v_dr_type IS NULL THEN
426
427 -- If currency is not derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
428 /* Bug 5148770. Added by Lakshmi Gopalsami
429 Removed the select and changed the same into a cursor.
430 */
431 OPEN get_curr_rate(v_func_curr,v_frm_curr);
432 FETCH get_curr_rate INTO ret_value;
433 CLOSE get_curr_rate;
434
435 -- Bug 5148770. Added by Lakshmi Gopalsami
436 print_log('jai_cmn_utils_pkg.currency_conversion.log',
437 ' derive type null - return value '|| ret_value);
438 ELSE
439
440 IF v_dr_type in('EMU','EURO') THEN
441
442 -- If currency is derived from Euro derived / Euro Currency by Subbu, Sri on 02-NOV-2000
443
444 v_frm_curr := 'EUR';
445
446 /* Bug 5148770. Added by Lakshmi Gopalsami
447 Removed the select and changed the same into a cursor.
448 */
449 OPEN get_curr_rate(v_func_curr,v_frm_curr);
450 FETCH get_curr_rate INTO ret_value;
451 CLOSE get_curr_rate;
452
453 -- Bug 5148770. Added by Lakshmi Gopalsami
454 print_log('jai_cmn_utils_pkg.currency_conversion.log',
455 ' EURO/EMU - derive type - return value '|| ret_value);
456 END IF;
457
458 END IF;
459
460 Exception When Others Then
461 --old code ret_value := 1;
462 RAISE_APPLICATION_ERROR(-20120,'Currency Conversion Rate Not Defined In The System');
463 End;
464 End If;
465 Return(nvl(ret_value,1));
466
467
468 /* Added by Ramananda for bug#4407165 */
469 EXCEPTION
470 WHEN OTHERS THEN
471 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
472 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
473 app_exception.raise_exception;
474
475 End currency_conversion;
476
477
478 PROCEDURE print_log (
479 filename VARCHAR2,
480 text_to_write VARCHAR2
481 ) IS
482 v_myfilehandle UTL_FILE.FILE_TYPE;
483 v_utl_location VARCHAR2(40) ;
484 lv_name varchar2(30);
485 BEGIN
486 lv_name := 'utl_file_dir'; --rchandan for bug#4428980
487 SELECT
488 decode(substr (value,1,instr(value,',') -1) ,
489 null ,
490 value ,
491 substr (value,1,instr(value,',') -1))
492 INTO
493 v_utl_location
494 FROM
495 v$parameter
496 WHERE
497 name = lv_name; --rchandan for bug#4428980
498
499 v_myfilehandle := utl_file.fopen(v_utl_location,filename,'A');
500
501 utl_file.put_line(v_myfilehandle,text_to_write);
502
503 utl_file.fclose(v_myfilehandle);
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 Null;
508 END print_log;
509 --rchandan for bug#4428980
510 --As part OF R12 Inititive Inventory conversion the OPM code IS commented
511 /*FUNCTION opm_uom_version(from_uom varchar2,to_uom varchar2,p_item_id number) RETURN NUMBER IS
512 f_uom_type varchar2(20);
513 t_uom_type varchar2(20);
514 f_ref_um varchar2(20);
515 t_ref_um varchar2(20);
516 l_std_factor number;
517 l_std_factor_t number;
518 l_std_factor_typ_1 number;
519 l_item_um varchar2(20);
520 x number;
521 y number;
522 z number;
523 CURSOR C_Uom_Type(p_uom_code varchar2) IS Select upper(um_type),upper(ref_um) From sy_uoms_mst
524 Where upper(um_code) = upper(p_uom_code);
525 CURSOR C_Conv_Val(p_uom_code varchar2) IS
526 Select std_factor From sy_uoms_mst
527 Where um_code = p_uom_code;
528 CURSOR C_Uom_Type_Conv(p_uom_type varchar2) IS
529 Select type_factor From ic_item_cnv
530 Where um_type = p_uom_type and
531 item_id = p_item_id;
532 CURSOR C_Item_Um(p_item_id number) IS
533 Select item_um From ic_item_mst
534 Where item_id = p_item_id;
535
536 Added by Ramananda for bug#4407165
537 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.opm_uom_version';
538
539 BEGIN
540 OPEN C_Uom_Type(from_uom);
541 FETCH C_Uom_Type INTO f_uom_type,f_ref_um;
542 CLOSE C_Uom_Type;
543 OPEN C_Uom_Type(to_uom);
544 FETCH C_Uom_Type INTO t_uom_type,t_ref_um;
545 CLOSE C_Uom_Type;
546 OPEN C_Conv_Val(from_uom);
547 FETCH C_Conv_Val INTO l_std_factor;
548 CLOSE C_Conv_Val;
549 OPEN C_Conv_Val(to_uom);
550 FETCH C_Conv_Val INTO l_std_factor_t;
551 CLOSE C_Conv_Val;
552 IF f_uom_type = t_uom_type THEN
553 IF from_uom = to_uom THEN
554 return(1);
555 ELSIF from_uom = f_ref_um THEN
556 return(1/l_std_factor_t);
557 ELSIF to_uom = t_ref_um THEN
558 return(l_std_factor);
559 ELSE
560
561 return(round((l_std_factor/l_std_factor_t),9));
562 END IF;
563 ELSE
564 OPEN C_Uom_Type_Conv(t_uom_type);
565 FETCH C_Uom_Type_Conv INTO l_std_factor_typ_1;
566 CLOSE C_Uom_Type_Conv;
567 OPEN C_Item_Um(p_item_id);
568 FETCH C_Item_Um INTO l_item_um;
569 CLOSE C_Item_Um;
570 IF l_item_um = from_uom THEN
571 IF from_uom = f_ref_um AND to_uom = t_ref_um THEN
572 return(l_std_factor_typ_1);
573 ELSIF from_uom = f_ref_um AND to_uom <> t_ref_um THEN
574 return(round((1/(l_std_factor_typ_1*l_std_factor_t)),9));
575 ELSIF to_uom = t_ref_um AND from_uom <> f_ref_um THEN
576 return(round((l_std_factor/l_std_factor_typ_1),2));
577 ELSE
578 return(round((l_std_factor/(l_std_factor_typ_1*l_std_factor_t)),9));
579 END IF;
580 ELSIF l_item_um = to_uom THEN
581 x:= jai_cmn_utils_pkg.opm_uom_version(to_uom,from_uom,p_item_id);
582 IF x = 0 THEN
583 return(x);
584 ELSE
585 return(1/x);
586 END IF;
587 ELSE
588 x := jai_cmn_utils_pkg.opm_uom_version(l_item_um,from_uom,p_item_id);
589 y := jai_cmn_utils_pkg.opm_uom_version(l_item_um,to_uom,p_item_id);
590 IF x > 0 THEN
591 z := y/x;
592 ELSE
593 z := 0;
594 END IF;
595 return(z);
596 END IF;
597 END IF;
598
599
600 -- Added by Ramananda for bug#4407165
601 EXCEPTION
602 WHEN OTHERS THEN
603 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
604 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
605 app_exception.raise_exception;
606
607 End opm_uom_version;
608
609
610 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
611 Cursor C_Item_Dtl IS
612 Select excise_calc_base -- , assessable_value (Commented as Assessable Value is picked by other conditions now )
613 From JAI_OPM_ITM_MASTERS
614 Where item_id = p_item_id;
615
616 -- Added by Ramananda for bug#4407165
617 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_utils_pkg.get_opm_assessable_value';
618
619 ---Added For OPM Localization By A.Raina on 22-02-2000
620 ---Code Added For Fetching the Assessable_value at the customer level
621
622 Cursor C_Price_list_id is
623 Select Pricelist_Id
624 From JAI_OPM_CUSTOMERS
625 Where Cust_id = p_cust_id ;
626
627 Cursor C_Cust_Ass_Value ( p_Pricelist_Id In Number ) is
628 Select a.Base_Price
629 From Op_Prce_Itm a ,op_prce_eff b
630 Where a.pricelist_id = b.pricelist_id
631 And a.Pricelist_Id = p_Pricelist_id
632 And a.Item_Id = p_item_id
633 And sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate) ;
634
635 CURSOR C_item_Ass_Value IS
636 Select assessable_value
637 From JAI_OPM_ITM_MASTERS
638 Where item_id = p_item_id;
639
640 v_pricelist_id Number;
641 v_assessable_flag char(1) ;
642 --End Addition
643 l_assessable_val number;
644 l_excise_cal varchar2(1);
645 BEGIN
646
647 ---Added For OPM Localization By A.Raina on 22-02-2000
648 ---Code Added For Fetching the Assessable_value at the customer level
649
650 OPEN C_Price_list_id ;
651 FETCH C_Price_list_id into v_pricelist_id;
652 CLOSE C_Price_list_id ;
653
654 l_assessable_val := Null ;
655 IF v_pricelist_id is Not Null Then
656 OPEN C_Cust_Ass_Value (v_pricelist_id ) ;
657 FETCH C_Cust_Ass_Value into l_assessable_val ;
658 CLOSE C_Cust_Ass_Value ;
659 End If;
660 IF l_assessable_val Is Null Then
661 OPEN C_item_Ass_Value ;
662 FETCH C_item_Ass_Value into l_assessable_val ;
663 CLOSE C_item_Ass_Value ;
664 End If;
665
666 ---End Addition
667
668 OPEN C_Item_Dtl;
669 FETCH C_Item_Dtl INTO l_excise_cal ; -- l_assessable_val (Commented as Assessable Value is picked by other conditions now )
670 CLOSE C_Item_Dtl ;
671
672 IF NVL(l_excise_cal,'N') = 'Y' THEN
673 Return(l_assessable_val*p_qty);
674 ELSE
675 Return(p_exted_price);
676 END IF;
677
678 -- Added by Ramananda for bug#4407165
679 EXCEPTION
680 WHEN OTHERS THEN
681 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
682 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
683 app_exception.raise_exception;
684
685 END get_opm_assessable_value;*/
686
687
688 procedure get_le_info
689 (
690 p_api_version IN NUMBER ,
691 p_init_msg_list IN VARCHAR2 ,
692 p_commit IN VARCHAR2 ,
693 p_ledger_id IN NUMBER ,
694 p_bsv IN VARCHAR2 ,
695 p_org_id IN NUMBER ,
696 x_return_status OUT NOCOPY VARCHAR2 ,
697 x_msg_count OUT NOCOPY NUMBER ,
698 x_msg_data OUT NOCOPY VARCHAR2 ,
699 x_legal_entity_id OUT NOCOPY NUMBER ,
700 x_legal_entity_name OUT NOCOPY VARCHAR2
701 )
702 IS
703 ln_legal_entity_id NUMBER;
704 CURSOR c_get_le_info is
705 SELECT XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(p_org_id)
706 FROM dual;
707
708 /* Bug 4906958. Added by Lakshmi Gopalsami
709 Get the value of legal entity from hr_operating_units
710 if the above cursor is returning null.
711 */
712 /* Bug 5243532. Added by Lakshmi Gopalsami
713 Removed the cursor c_get_default_LE_id which
714 is referring to hr_operating_units
715 and replaced with caching logic.
716 Defined variable for implementing caching logic.
717 */
718
719 l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
720
721 BEGIN
722
723 IF p_ledger_id IS NOT NULL AND p_bsv IS NOT NULL THEN
724
725
726 XLE_UTILITIES_GRP.Get_LegalEntity_LGER_BSV
727 (
728 p_api_version ,
729 p_init_msg_list ,
730 p_commit ,
731 x_return_status ,
732 x_msg_count ,
733 x_msg_data ,
734 p_ledger_id ,
735 p_bsv ,
736 x_legal_entity_id ,
737 x_legal_entity_name
738 );
739
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 RETURN;
743
744 END IF;
745
746 IF p_org_id IS NOT NULL THEN
747 OPEN c_get_le_info;
748 FETCH c_get_le_info INTO ln_legal_entity_id;
749 CLOSE c_get_le_info;
750
751 /* Bug 4906958. Added by Lakshmi Gopalsami
752 If ln_legal_entity_id is null fetch the legal entity id
753 from the default_legal_context_id from hr_operating_units
754 */
755
756 /* Bug 5243532. Added by Lakshmi Gopalsami
757 Removed the cursor c_get_default_LE_id and implemented using
758 cache logic.
759 */
760
761 If nvl(ln_legal_entity_id,-1) = -1 THEN
762 l_func_curr_det := jai_plsql_cache_pkg.return_sob_curr
763 (p_org_id => p_org_id);
764 ln_legal_entity_id := l_func_curr_det.legal_entity;
765
766 END IF;
767
768 x_legal_entity_id := ln_legal_entity_id;
769 x_return_status := FND_API.G_RET_STS_SUCCESS;
770 RETURN;
771 END IF;
772
773
774 x_return_status := FND_API.G_RET_STS_ERROR;
775
776 EXCEPTION
777 WHEN OTHERS THEN
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779
780 END get_le_info;
781
782
783 FUNCTION validate_po_type(p_po_type IN VARCHAR2 DEFAULT NULL,
784 p_style_id IN NUMBER DEFAULT NULL,
785 p_po_header_id IN NUMBER DEFAULT NULL
786 ) RETURN BOOLEAN
787
788 IS
789 CURSOR c_doc_style_headers(cp_style_id NUMBER)
790 IS
791 SELECT progress_payment_flag
792 FROM po_doc_style_headers
793 WHERE style_id = cp_style_id;
794
795 CURSOR c_po_headers(cp_po_header_id NUMBER)
796 IS
797 SELECT style_id
798 FROM po_headers_all
799 WHERE po_header_id = cp_po_header_id;
800
801 r_doc_style_headers c_doc_style_headers%ROWTYPE;
802 r_po_headers c_po_headers%ROWTYPE;
803 b_process_po BOOLEAN;
804 v_style_id po_headers_all.style_id%TYPE;
805 BEGIN
806 v_style_id := p_style_id;
807
808 IF v_style_id IS NULL AND p_po_header_id IS NOT NULL THEN
809 OPEN c_po_headers(p_style_id);
810 FETCH c_po_headers INTO r_po_headers;
811 CLOSE c_po_headers;
812
813 v_style_id := r_po_headers.style_id;
814 END IF;
815
816 OPEN c_doc_style_headers(v_style_id);
817 FETCH c_doc_style_headers INTO r_doc_style_headers;
818 CLOSE c_doc_style_headers;
819
820 --if progess_payment_flag = 'Y', then it is a complex work PO
821 IF NVL(r_doc_style_headers.progress_payment_flag,'N') = 'Y' THEN
822 b_process_po := FALSE;
823 ELSE
824 b_process_po := TRUE;
825 END IF;
826
827 RETURN b_process_po;
828 END validate_po_type;
829
830
831 END jai_cmn_utils_pkg;
[Home] [Help]