1 PACKAGE BODY JAI_OM_UTILS_PKG AS
2 /* $Header: jai_om_utils.plb 120.8.12020000.2 2013/03/29 13:47:17 mmurtuza ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_om_utils -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 13-Jun-2005 4428980 File Version: 116.3
15 Ramananda for bug#4428980. Removal of SQL LITERALs is done
16
17 28-Jul-2009 Xiao Lv for IL Advanced Pricing.
18 Add if condition control for specific release version, code as:
19 IF lv_release_name NOT LIKE '12.0%' THEN
20 Advanced Pricing code;
21 END IF;
22
23 28-aug-2009 vkaranam for bug#8844209
24 Issue:
25 IL ASSESSABLE VALUE NOT CONSIDERING CURRENCY CONVERSION FOR EXPORT SALES ORDER
26
27 Fix:
28 Added a conversion factor while calculating the assessable value.
29 Changes are done in get_oe_assessable_value.
30 Please query by bug number to see the changes.
31
32
33 *--------------------------------------------------------------------------------------*/
34
35 PROCEDURE get_ato_pricelist_value
36 (
37 NEW_LIST NUMBER,
38 UNIT_CODE NUMBER,
39 INVENTORY_ID NUMBER,
40 IL6 NUMBER,
41 NAMOUNT OUT NOCOPY NUMBER
42 )
43 IS
44
45 PRICE NUMBER;
46 SUM_TOT NUMBER;
47 NO NUMBER;
48
49 -- Changed For Migration To R11i on 17-10-2000 by A.Raina
50 -- Table "SO_LINES_ALL IS REPLACED BY "OE_ORDER_LINES_ALL"
51 -- so field "UNIT_CODE" is replace by "ORDER_QUANTITY_UOM" .
52 -- Also table "SO_PRICE_LIST_LINES" is replaced by "SO_PRICE_LIST_LINES_115"
53
54 CURSOR sel_line_id is
55 SELECT line_id,ordered_quantity,order_quantity_uom,inventory_item_id
56 FROM oe_order_lines_all
57 WHERE line_id = il6;
58
59 CURSOR sel_ato_lines(ln_id number) is select line_id,ato_line_id,ordered_quantity,inventory_itEM_ID,ORDER_QUANTITY_UOM
60 FROM oe_order_lines_all
61 WHERE ato_line_id = ln_id;
62
63 CURSOR n_lst_price(id number,unt varchar2) is /*select list_price
64 FROM so_price_list_lines_115
65 WHERE inventory_item_id = id
66 AND unit_code = unt
67 AND price_list_id = new_list; */ /*Changed cursor query for bug 16574430 */
68
69 SELECT qpll.operand list_price
70 FROM qp_list_lines qpll,
71 qp_pricing_attributes qppr
72 WHERE qppr.list_line_id = qpll.list_line_id
73 AND qpll.list_header_id = new_list
74 AND qppr.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
75 AND qppr.product_attribute = 'PRICING_ATTRIBUTE1'
76 AND qppr.product_attr_value = to_char(id)
77 and qppr.PRICING_ATTRIBUTE_CONTEXT is null
78 and qppr.PRICING_ATTRIBUTE is null
79 and qppr.EXCLUDER_FLAG = 'N'
80 AND qppr.product_uom_code = unt
81 and qpll.LIST_LINE_TYPE_CODE='PLL'
82 and qpll.pricing_phase_id=1
83 and mod(qpll.QUALIFICATION_IND,2)=0;
84
85 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_ato_pricelist_value';
86
87 BEGIN
88
89 FOR I IN SEL_LINE_ID ---------------------------------------------------------------- (1)
90 LOOP
91 FOR J IN SEL_ATO_LINES(I.LINE_ID) -------------------------------------------------(2)
92 LOOP
93 FOR K IN N_LST_PRICE(J.INVENTORY_ITEM_ID,J.ORDER_QUANTITY_UOM) ----------------(3)
94 LOOP
95 SUM_TOT := K.LIST_PRICE * J.ORDERED_QUANTITY;
96 NO := NO + J.ORDERED_QUANTITY;
97 END LOOP; -------------------------(3)
98 END LOOP; ------------------------------------------------- (2)
99
100 FOR L IN N_LST_PRICE(I.INVENTORY_ITEM_ID,I.ORDER_QUANTITY_UOM) ------------------- (4)
101 LOOP
102 SUM_TOT := L.LIST_PRICE * I.ORDERED_QUANTITY;
103 NO := NO + I.ORDERED_QUANTITY;
104 END LOOP; -----------------------------(4)
105 PRICE := SUM_TOT/NO;
106 END LOOP; ---------------------------------------------------------------- (1)
107 NAMOUNT := PRICE;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 NAMOUNT := null;
112 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
113 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
114 app_exception.raise_exception;
115 END get_ato_pricelist_value ;
116
117
118 function get_oe_assessable_value
119 (
120 p_customer_id IN NUMBER,
121 p_ship_to_site_use_id IN NUMBER,
122 p_inventory_item_id IN NUMBER,
123 p_uom_code IN VARCHAR2,
124 p_default_price IN NUMBER,
125 p_ass_value_date IN DATE, --DEFAULT SYSDATE --Added global variable gd_ass_value_date in package spec. by Ramananda for File.Sql.35
126 /* Bug 5096787. Added the following parameters */
127 p_sob_id IN NUMBER ,
128 p_curr_conv_code IN VARCHAR2 ,
129 p_conv_rate IN NUMBER
130 )
131 RETURN NUMBER IS
132
133 CURSOR address_cur ( p_ship_to_site_use_id IN NUMBER )
134 IS
135 SELECT NVL(cust_acct_site_id , 0) address_id
136 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
137 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
138 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
139
140 /*
141 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, ,Ordered date.
142 Exact Match condition
143 */
144 CURSOR c_assessable_value
145 ( p_customer_id NUMBER ,
146 p_address_id NUMBER ,
147 p_inventory_item_id NUMBER ,
148 p_uom_code VARCHAR2,
149 p_ordered_date DATE
150 )
151 IS
152 SELECT
153 b.operand list_price,
154 c.product_uom_code list_price_uom_code ,
155 qlhb.currency_code /* Added for bug#8844209 */
156
157 FROM
158 JAI_CMN_CUS_ADDRESSES a,
159 qp_list_lines b,
160 qp_pricing_attributes c ,
161 qp_list_headers_b qlhb /* Added for bug#8844209 */
162 WHERE
163 a.customer_id = p_customer_id AND
164 a.address_id = p_address_id AND
165 a.price_list_id = b.LIST_header_ID AND
166 c.list_line_id = b.list_line_id AND
167 c.product_attr_value = to_char(p_inventory_item_id) AND
168 c.product_uom_code = p_uom_code AND
169 qlhb.list_header_id = b.list_header_id AND /* Added for bug#8844209 */
170 p_ordered_date BETWEEN nvl( qlhb.start_date_active, p_ordered_date) AND
171 nvl( qlhb.end_date_active, SYSDATE) AND
172 p_ordered_date BETWEEN nvl( b.start_date_active, p_ordered_date) AND
173 nvl( b.end_date_active, SYSDATE);
174
175 /*
176 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
177 Exact Match condition
178 */
179 --Added by Nagaraj.s for Bug3700249
180 CURSOR c_assessable_value_pri_uom(
181 p_customer_id NUMBER,
182 p_address_id NUMBER,
183 p_inventory_item_id NUMBER,
184 p_ordered_date DATE
185 )
186 IS
187 SELECT
188 b.operand list_price,
189 c.product_uom_code list_price_uom_code ,
190 qlhb.currency_code /* Added for bug#8844209 */
191 FROM
192 JAI_CMN_CUS_ADDRESSES a,
193 qp_list_lines b,
194 qp_pricing_attributes c,
195 qp_list_headers_b qlhb /* Added for bug#8844209 */
196 WHERE
197 a.customer_id = p_customer_id AND
198 a.address_id = p_address_id AND
199 a.price_list_id = b.list_header_id AND
200 c.list_line_id = b.list_line_id AND
201 c.product_attr_value = to_char(p_inventory_item_id) AND
202 trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
203 qlhb.list_header_id = b.list_header_id AND /* Added for bug#8844209 */
204 nvl(qlhb.active_flag,'N') = 'Y' AND /*added for bug#8844209*/
205 primary_uom_flag ='Y'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
206 --nvl(primary_uom_flag,'N') ='Y';
207
208 CURSOR c_assessable_value_other_uom
209 (
210 p_customer_id NUMBER,
211 p_address_id NUMBER,
212 p_inventory_item_id NUMBER,
213 p_ordered_date DATE
214 )
215 IS
216 SELECT
217 b.operand list_price,
218 c.product_uom_code list_price_uom_code ,
219 qlhb.currency_code /* Added for bug#8844209 */
220 FROM
221 JAI_CMN_CUS_ADDRESSES a,
222 qp_list_lines b,
223 qp_pricing_attributes c,
224 qp_list_headers_b qlhb /* Added for bug#8844209 */
225 WHERE
226 a.customer_id = p_customer_id AND
227 a.address_id = p_address_id AND
228 a.price_list_id = b.LIST_header_ID AND
229 c.list_line_id = b.list_line_id AND
230 c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) AND
231 qlhb.list_header_id = b.list_header_id AND /* Added for bug#8844209 */
232 NVL(qlhb.end_date_active,SYSDATE) >= p_ordered_date AND
233 NVL(qlhb.active_flag,'N') = 'Y' AND /*Added for bug#8844209*/
234 (b.end_date_active is null OR b.end_date_active >= p_ordered_date); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
235 --NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
236
237 v_primary_uom_code qp_pricing_attributes.product_uom_code%type; --Added by Nagaraj.s for Bug3700249
238 v_other_uom_code qp_pricing_attributes.product_uom_code%type; --Added by Nagaraj.s for Bug3700249
239
240 v_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'N';
241 v_address_id NUMBER;
242 v_assessable_value NUMBER;
243 v_conversion_rate NUMBER;
244 v_price_list_uom_code VARCHAR2(4);
245 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_oe_assessable_value';
246
247 /* Added for bug#8844209 */
248
249 lv_assess_val_curr_code VARCHAR2(100) ;
250 ln_assess_val_conv_rate NUMBER ;
251
252 -- add by Xiao for recording down the release version on 27-Jul-2009
253 lv_release_name VARCHAR2(30);
254 lv_other_release_info VARCHAR2(30);
255 lb_result BOOLEAN := FALSE ;
256
257 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
258 ----------------------------------------------------------------------------------------------------------
259 -- Get category_set_name
260 CURSOR category_set_name_cur
261 IS
262 SELECT
263 category_set_name
264 FROM
265 mtl_default_category_sets_fk_v
266 WHERE functional_area_desc = 'Order Entry';
267
268 lv_category_set_name VARCHAR2(30);
269
270 -- Get the Excise Assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, Ordered date.
271 CURSOR cust_ass_value_category_cur
272 ( pn_party_id NUMBER
273 , pn_address_id NUMBER
274 , pn_inventory_item_id NUMBER
275 , pv_uom_code VARCHAR2
276 , pd_ordered_date DATE
277 )
278 IS
279 SELECT
280 b.operand list_price
281 , c.product_uom_code list_price_uom_code,
282 qlhb.currency_code /* Added for bug#8844209 */
283 FROM
284 jai_cmn_cus_addresses a
285 , qp_list_lines b
286 , qp_pricing_attributes c ,
287 qp_list_headers_b qlhb /* Added for bug#8844209 */
288 WHERE a.customer_id = pn_party_id
289 AND a.address_id = pn_address_id
290 AND a.price_list_id = b.list_header_id
291 AND c.list_line_id = b.list_line_id
292 AND c.product_uom_code = pv_uom_code
293 AND qlhb.list_header_id = b.list_header_id /* Added for bug#8844209 */
294 AND NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date
295 AND NVL(qlhb.active_flag,'N') = 'Y' /*Added for bug#8844209*/
296 AND pd_ordered_date BETWEEN NVL( b.start_date_active, pd_ordered_date)
297 AND NVL( b.end_date_active, SYSDATE)
298 AND EXISTS ( SELECT
299 'x'
300 FROM
301 mtl_item_categories_v d
302 WHERE d.category_set_name = lv_category_set_name
303 AND d.inventory_item_id = pn_inventory_item_id
304 AND c.product_attr_value = TO_CHAR(d.category_id)
305 );
306
307 --Get the Excise Assessable Value based on the Primary Uom, Customer Id, Address Id, inventory_item_id, Ordered date.
308 CURSOR cust_ass_value_pri_uom_cur
309 ( pn_party_id NUMBER
310 , pn_address_id NUMBER
311 , pn_inventory_item_id NUMBER
312 , pd_ordered_date DATE
313 )
314 IS
315 SELECT
316 b.operand list_price
317 , c.product_uom_code list_price_uom_code,
318 qlhb.currency_code /* Added for bug#8844209 */
319 FROM
320 jai_cmn_cus_addresses a
321 , qp_list_lines b
322 , qp_pricing_attributes c ,
323 qp_list_headers_b qlhb /* Added for bug#8844209 */
324 WHERE a.customer_id = pn_party_id
325 AND a.address_id = pn_address_id
326 AND a.price_list_id = b.list_header_id
327 AND c.list_line_id = b.list_line_id
328 AND qlhb.list_header_id = b.list_header_id /* Added for bug#8844209 */
329 AND NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date
330 AND NVL(qlhb.active_flag,'N') = 'Y'
331 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
332 AND NVL(primary_uom_flag,'N') ='Y'
333 AND EXISTS ( SELECT
334 'x'
335 FROM
336 mtl_item_categories_v d
337 WHERE d.category_set_name = lv_category_set_name
338 AND d.inventory_item_id = pn_inventory_item_id
339 AND c.product_attr_value = TO_CHAR(d.category_id)
340 );
341
342 --Get the Excise Assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
343 CURSOR cust_ass_value_other_uom_cur
344 ( pn_party_id NUMBER
345 , pn_address_id NUMBER
346 , pn_inventory_item_id NUMBER
347 , pd_ordered_date DATE
348 )
349 IS
350 SELECT
351 b.operand list_price
352 , c.product_uom_code list_price_uom_code,
353 qlhb.currency_code /* Added for bug#8844209 */
354 FROM
355 jai_cmn_cus_addresses a
356 , qp_list_lines b
357 , qp_pricing_attributes c ,
358 qp_list_headers_b qlhb /* Added for bug#8844209 */
359 WHERE a.customer_id = pn_party_id
360 AND a.address_id = pn_address_id
361 AND a.price_list_id = b.list_header_id
362 AND c.list_line_id = b.list_line_id
363 AND qlhb.list_header_id = b.list_header_id /* Added for bug#8844209 */
364 AND NVL(qlhb.end_date_active,SYSDATE) >= pd_ordered_date
365 AND NVL(qlhb.active_flag,'N') = 'Y'
366 AND TRUNC(NVL(b.end_date_active,SYSDATE)) >= TRUNC(pd_ordered_date)
367 AND EXISTS ( SELECT
368 'x'
369 FROM
370 mtl_item_categories_v d
371 WHERE d.category_set_name = lv_category_set_name
372 AND d.inventory_item_id = pn_inventory_item_id
373 AND c.product_attr_value = TO_CHAR(d.category_id)
374 );
375 ----------------------------------------------------------------------------------------------------------
376 --- Added by Jia for Advanced Pricing on 08-Jun-2009, End
377
378 BEGIN
379 v_debug := 'N';
380
381 /*----------------------------------------------------------------------------------------------------------------------------
382 Change History for File -> get_oe_assessable_value_f.sql
383 S.No. DD/MM/YY Author AND Details
384 ------------------------------------------------------------------------------------------------------------------------------
385 1 25/03/03 Vijay Shankar for Bug# 2837970, FileVersion: 615.1
386 This function is written for CRM Localization Print Quote Taxes functionality. But this function can be called
387 from anywhere in Order Management to fetch the assessable value. Required Parameter needs to be passed during
388 invocation of this procedure.
389 Basically this fetches the assessable value of an ITEM based on the Customer additional information setup
390 If assessable value is not found then it returns the value passed as P_DEFAULT_PRICE
391 This is a duplicate code for jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes procedure
392
393 2. 2004/14/07 Aiyer - bug # 3700249 File Version 115.2
394 Issue
395 The assessable value does not get calculated properly.
396
397 Solution
398 The following 5 level assessable value derivation logic has been implemented now:-
399 Each Logic will come into picture only if the preceding one does not get any value.
400 1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
401 2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
402
403 3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
404 for the Primary UOM defined in Price List.
405 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
406 as the product of the Assessable value.
407 4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
408 on a first come first serve basis.
409 5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
410 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
411 as the product of the Assessable value.
412
413 6. 08-Jun-2009 Jia Li for IL Advanced Pricing.
414 There were enhancement requests from customers to enhance the current India Localization functionality
415 on assessable values where an assessable value can be defined either based on an item or an item category.
416
417 7. 30-Jul-2009 Jia Li for Bug#8731794
418 Add Item-UOM validation for null site level.
419
420 Dependency Due to this Bug:-
421 None
422
423 Future Dependencies For the release Of this Object:-
424 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
425 A datamodel change )
426
427 ----------------------------------------------------------------------------------------------------------------------------------------------------
428 Current Version Current Bug Dependent Files Version Author Date Remarks
429 Of File On Bug/Patchset Dependent On
430 get_oe_assessable_value_f.sql
431 ----------------------------------------------------------------------------------------------------------------------------------------------------
432 115.2 3700249 IN60105D2 None -- Aiyer 14/07/2004 Row introduces to start dependency tracking
433
434 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
435
436 /******************************** Part 1 Get Customer address id ******************************/
437 OPEN address_cur(p_ship_to_site_use_id);
438 FETCH address_cur INTO v_address_id;
439 CLOSE address_cur;
440
441
442 IF v_debug = 'Y' THEN
443 fnd_file.put_line(fnd_file.log, 'v_address_id -> '||v_address_id);
444 END IF;
445
446 ----------------------------------------------------------------------------------------------------------
447 /*
448 --Assessable Value Fetching Logic is based upon the following logic now.....
449 --Each Logic will come into picture only if the preceding one does not get any value.
450 --1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
451 --1.1. Assessable Value of item cetegory is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
452
453 --2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
454 --2.1. Assessable Value of item cetegory is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
455
456 --3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
457 for the Primary UOM defined in Price List.
458 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
459 as the product of the Assessable value.
460 --3.1. Assessable Value of item cetegory and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
461 for the Primary UOM defined in Price List.
462 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
463 as the product of the Assessable value.
464
465 --4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
466 on a first come first serve basis.
467 --4.1. Assessable Value of item cetegory is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
468 on a first come first serve basis.
469
470 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
471 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
472 as the product of the Assessable value.
473 */
474 ----------------------------------------------------------------------------------------------------------
475
476 -- Add by Xiao to get release version on 24-Jul-2009
477 lb_result := fnd_release.get_release(lv_release_name, lv_other_release_info);
478
479 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
480 ----------------------------------------------------------------------------------------------------------
481 -- Get category_set_name
482 OPEN category_set_name_cur;
483 FETCH category_set_name_cur INTO lv_category_set_name;
484 CLOSE category_set_name_cur;
485
486 -- Validate if there is more than one Item-UOM combination existing in used AV list for the Item selected
487 -- in the transaction. If yes, give an exception error message to stop transaction.
488
489 -- Add condition by Xiao for specific release version for Advanced Pricing code on 24-Jul-2009
490 IF lv_release_name NOT LIKE '12.0%' THEN
491
492 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_customer_id
493 , pn_party_site_id => v_address_id
494 , pn_inventory_item_id => p_inventory_item_id
495 , pd_ordered_date => TRUNC(p_ass_value_date)
496 , pv_party_type => 'C'
497 , pn_pricing_list_id => NULL
498 );
499 END IF;
500
501 ----------------------------------------------------------------------------------------------------------
502 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
503
504
505 /********************************************* Part 2 ****************************************/
506
507 /*
508 Get the Assessable Value based on the Customer Id, Address Id, UOM Code, inventory_item_id,Ordered date
509 Exact Match condition.
510 */
511
512 -- Fetch Assessable Price List Value for the given Customer and Location Combination
513 OPEN c_assessable_value( p_customer_id, v_address_id, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date));
514 FETCH c_assessable_value INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
515 CLOSE c_assessable_value;
516
517 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
518 ----------------------------------------------------------------------------------------------------------
519
520 -- Add condition for specific release version for Advanced Pricing code
521 IF lv_release_name NOT LIKE '12.0%' THEN
522 IF v_assessable_value IS NULL
523 THEN
524 -- Fetch Excise Assessable Value of item category for the given Customer, Site, Inventory Item and UOM Combination
525 OPEN cust_ass_value_category_cur( p_customer_id
526 , v_address_id
527 , p_inventory_item_id
528 , p_uom_code
529 , TRUNC(p_ass_value_date)
530 );
531 FETCH
532 cust_ass_value_category_cur
533 INTO
534 v_assessable_value
535 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
536 CLOSE cust_ass_value_category_cur;
537 END IF; -- v_assessable_value is null for given customer/site/inventory_item_id/UOM
538 END IF; --lv_release_name NOT LIKE '12.0%'
539
540 ----------------------------------------------------------------------------------------------------------
541 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
542
543 /********************************************* Part 3 ****************************************/
544
545 /*
546 Get the Assessable Value based on the Customer Id, Null Site, UOM Code, inventory_item_id,Ordered date
547 Null Site condition.
548 */
549
550 IF v_assessable_value IS NULL THEN
551
552 IF v_debug = 'Y' THEN
553 fnd_file.put_line(fnd_file.log,' Inside IF OF v_assessable_value IS NULL ');
554 END IF;
555
556 -- Added by Jia for Bug#8731794 on 30-Jul-2009, Begin
557 ----------------------------------------------------------------------------------------------------------
558 IF lv_release_name NOT LIKE '12.0%' THEN
559
560 Jai_Avlist_Validate_Pkg.Check_AvList_Validation( pn_party_id => p_customer_id
561 , pn_party_site_id => 0
562 , pn_inventory_item_id => p_inventory_item_id
563 , pd_ordered_date => TRUNC(p_ass_value_date)
564 , pv_party_type => 'C'
565 , pn_pricing_list_id => NULL
566 );
567 END IF;
568 ----------------------------------------------------------------------------------------------------------
569 -- Added by Jia for Bug#8731794 on 30-Jul-2009, End
570
571 -- Fetch Assessable Price List Value for the
572 -- given Customer and NULL LOCATION Combination
573 OPEN c_assessable_value( p_customer_id, 0, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date) );
574 FETCH c_assessable_value INTO v_assessable_value, v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
575 CLOSE c_assessable_value;
576
577 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
578 ----------------------------------------------------------------------------------------------------------
579
580 -- Add condition by Xiao for specific release version for Advanced Pricing code on 27-Jul-2009
581 IF lv_release_name NOT LIKE '12.0%' THEN
582
583 IF v_assessable_value IS NULL
584 THEN
585 -- Fetch the VAT Assessable Value of item category
586 -- for the given Customer, null Site, Inventory Item Id, UOM and Ordered date Combination.
587 OPEN cust_ass_value_category_cur( p_customer_id
588 , 0
589 , p_inventory_item_id
590 , p_uom_code
591 , TRUNC(p_ass_value_date)
592 );
593 FETCH
594 cust_ass_value_category_cur
595 INTO
596 v_assessable_value
597 , v_price_list_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
598 CLOSE cust_ass_value_category_cur;
599 END IF; -- v_assessable_value is null for given customer/null site/inventory_item_id/UOM
600 END IF; -- lv_release_name NOT LIKE '12.0%'
601
602 ----------------------------------------------------------------------------------------------------------
603 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
604
605 END IF;
606
607 IF v_debug = 'Y' THEN
608 fnd_file.put_line(fnd_file.log, '2 v_assessable_value -> '||v_assessable_value||', v_price_list_uom_code -> '||v_price_list_uom_code);
609 END IF;
610
611 /********************************************* Part 4 ****************************************/
612
613 /*
614 Get the Assessable Value based on the Customer Id, Address id, inventory_item_id,primary_uom_code and Ordered date
615 Primary UOM condition.
616 */
617
618 --Added by Aiyer for Bug 3700249
619 IF v_assessable_value is null THEN
620
621 open c_assessable_value_pri_uom
622 (
623 p_customer_id,
624 v_address_id,
625 p_inventory_item_id,
626 trunc(p_ass_value_date)
627 );
628 fetch c_assessable_value_pri_uom into v_assessable_value,v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
629 close c_assessable_value_pri_uom;
630
631 IF v_primary_uom_code is not null THEN
632
633 inv_convert.inv_um_conversion
634 (
635 p_uom_code,
636 v_primary_uom_code,
637 p_inventory_item_id,
638 v_conversion_rate
639 );
640
641
642 IF nvl(v_conversion_rate, 0) <= 0 THEN
643 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
644 IF NVL(v_conversion_rate, 0) <= 0 THEN
645 v_conversion_rate := 0;
646 END IF;
647 END IF;
648
649 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
650
651 ELSE
652 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
653 ----------------------------------------------------------------------------------------------------------
654 -- Fetch the Excise Assessable Value of item category and Primary UOM
655 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
656
657 -- Add condition by Xiao for specific release version for Advanced Pricing code on 27-Jul-2009
658 IF lv_release_name NOT LIKE '12.0%' THEN
659
660 OPEN cust_ass_value_pri_uom_cur( p_customer_id
661 , v_address_id
662 , p_inventory_item_id
663 , TRUNC(p_ass_value_date)
664 );
665 FETCH
666 cust_ass_value_pri_uom_cur
667 INTO
668 v_assessable_value
669 , v_primary_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
670 CLOSE cust_ass_value_pri_uom_cur;
671
672 IF v_primary_uom_code IS NOT NULL
673 THEN
674 inv_convert.inv_um_conversion( p_uom_code
675 , v_primary_uom_code
676 , p_inventory_item_id
677 , v_conversion_rate
678 );
679
680 IF NVL(v_conversion_rate, 0) <= 0
681 THEN
682 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
683 IF NVL(v_conversion_rate, 0) <= 0
684 THEN
685 v_conversion_rate := 0;
686 END IF;
687 END IF;
688
689 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
690 END IF; -- v_primary_uom_code IS NOT NULL for Customer/Site/Inventory_item_id
691
692 END IF; -- lv_release_name NOT LIKE '12.0%'
693
694 IF v_assessable_value IS NULL
695 THEN
696 ----------------------------------------------------------------------------------------------------------
697 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
698
699 /* Primary uom code setup not found for the customer id, address id, inventory_item_id and ordered_date.
700 Get the assessable value for a combination of customer id, address id, inventory_item_id
701 and ordered_date. Pick up the assessable value by first come first serve basis.
702 */
703
704 OPEN c_assessable_value_other_uom
705 (
706 p_customer_id,
707 v_address_id,
708 p_inventory_item_id,
709 trunc(p_ass_value_date)
710 );
711 FETCH c_assessable_value_other_uom into v_assessable_value,v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
712 CLOSE c_assessable_value_other_uom;
713
714 IF v_other_uom_code is not null THEN
715 inv_convert.inv_um_conversion
716 (
717 p_uom_code,
718 v_other_uom_code,
719 p_inventory_item_id,
720 v_conversion_rate
721 );
722
723 IF nvl(v_conversion_rate, 0) <= 0 THEN
724
725 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
726
727 IF NVL(v_conversion_rate, 0) <= 0 THEN
728 v_conversion_rate := 0;
729 END IF;
730 END IF;
731 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
732
733 -- Added by Jia for Advanced Pricing on 08-Jun-2009, Begin
734 ----------------------------------------------------------------------------------------------------------
735 ELSE
736 -- Primary uom code setup not found for the Customer, Site, Inventory Item Id and Ordered_date.
737 -- Fetch the Excise Assessable Value of item category and other UOM
738 -- for the given Customer, Site, Inventory Item Id, Ordered date Combination.
739
740
741 -- Add condition by Xiao for specific release version for Advanced Pricing code on 27-Jul-2009
742 IF lv_release_name NOT LIKE '12.0%' THEN
743
744 OPEN cust_ass_value_other_uom_cur( p_customer_id
745 , v_address_id
746 , p_inventory_item_id
747 , TRUNC(p_ass_value_date)
748 );
749 FETCH
750 cust_ass_value_other_uom_cur
751 INTO
752 v_assessable_value
753 , v_other_uom_code,lv_assess_val_curr_code; /* Added for bug#8844209 */
754 CLOSE cust_ass_value_other_uom_cur;
755
756 IF v_other_uom_code IS NOT NULL
757 THEN
758 inv_convert.inv_um_conversion( p_uom_code
759 , v_other_uom_code
760 , p_inventory_item_id
761 , v_conversion_rate
762 );
763
764 IF NVL(v_conversion_rate, 0) <= 0
765 THEN
766 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
767 IF NVL(v_conversion_rate, 0) <= 0
768 THEN
769 v_conversion_rate := 0;
770 END IF;
771 END IF;
772
773 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
774 END IF; -- v_other_uom_code is not null for Customer/Site/Inventory_item_id
775
776 END IF; -- lv_release_name NOT LIKE '12.0%'
777
778 ----------------------------------------------------------------------------------------------------------
779 -- Added by Jia for Advanced Pricing on 08-Jun-2009, End
780
781 END IF; --end if for v_other_uom_code is not null
782 END IF; -- v_assessable_value is null, Added by Jia for Advanced Pricing on 08-Jun-2009.
783 END IF; --end if for v_primary_uom_code is not null
784 END IF; --end if for v_assessable_value
785 --Ends here..........................
786
787
788 /*
789 IF NVL(v_assessable_value,0) > 0 THEN
790
791 -- If still the Assessable Value is available
792 IF v_price_list_uom_code IS NOT NULL THEN
793
794 IF v_debug = 'Y' THEN
795 fnd_file.put_line(fnd_file.log,' BEFORE Calling Inv_Convert.inv_um_conversion 1');
796 END IF;
797
798 Inv_Convert.inv_um_conversion ( v_uom_code, v_price_list_uom_code, v_inventory_item_id, v_conversion_rate );
799 IF NVL(v_conversion_rate, 0) <= 0 THEN
800
801 IF v_debug = 'Y' THEN
802 fnd_file.put_line(fnd_file.log,' BEFORE Calling Inv_Convert.inv_um_conversion 2');
803 END IF;
804
805 Inv_Convert.inv_um_conversion(v_uom_code, v_price_list_uom_code, 0, v_conversion_rate);
806 IF NVL(v_conversion_rate, 0) <= 0 THEN
807 v_conversion_rate := 0;
808 END IF;
809
810 END IF;
811
812 END IF;
813
814 v_assessable_value := nvl(v_assessable_value,0) * v_conversion_rate;
815 -- v_assessable_value := NVL(1/v_converted_rate,0) * NVL(v_assessable_value,0) * v_conversion_rate;
816 -- v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
817
818 ELSE
819
820 IF v_debug = 'Y' THEN
821 fnd_file.put_line(fnd_file.log,' inside ELSE OF v_assessable_value IS NULL ');
822 END IF;
823
824 -- If the assessable value is not available
825 -- then pick up the Line price for Tax Calculation
826 v_assessable_value := NVL(p_default_price, 0);
827 -- v_assessable_amount := v_line_amount;
828
829 END IF; -- v_assessable_value IS NULL THEN
830 */
831
832 IF nvl(v_assessable_value,0) =0 THEN
833 IF v_debug = 'Y' THEN
834 fnd_file.put_line(fnd_file.log,' No Assessable value is defined, so default price is returning back ');
835 END IF;
836
837 v_assessable_value := NVL(p_default_price, 0);
838 ELSE
839
840 /* Added for bug#8844209 */
841 ln_assess_val_conv_rate :=jai_cmn_utils_pkg.currency_conversion (
842 p_sob_id,
843 lv_assess_val_curr_code,
844 NULL,
845 p_curr_conv_code,
846 NULL
847 );
848
849 v_assessable_value := v_assessable_value*(ln_assess_val_conv_rate/nvl(p_conv_rate,1));
850 /* end bug#8844209 */
851
852 END IF;
853
854 RETURN v_assessable_value;
855 EXCEPTION
856 WHEN OTHERS THEN
857 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
858 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
859 app_exception.raise_exception;
860
861 END get_oe_assessable_value;
862
863
864
865 procedure get_ato_assessable_value
866 (
867 NEW_ASSESS_LIST NUMBER,
868 IL6 NUMBER ,
869 NAMOUNT OUT NOCOPY NUMBER
870 )
871 IS
872 APRICE NUMBER;
873 ASUM_TOT NUMBER;
874 ANO NUMBER;
875
876 -- Changed For Migration To R11i on 17-10-2000 by A.Raina
877 -- Table "SO_LINES_ALL IS REPLACED BY "OE_ORDER_LINES_ALL"
878 -- so field "UNIT_CODE" is replace by "ORDER_QUANTITY_UOM" .
879 -- Also table "SO_PRICE_LIST_LINES" is replaced by "SO_PRICE_LIST_LINES_115"
880
881 CURSOR ASEL_LINE_ID IS
882 SELECT LINE_ID,ORDERED_QUANTITY,ORDER_QUANTITY_UOM,INVENTORY_ITEM_ID
883 FROM OE_ORDER_LINES_ALL
884 WHERE LINE_ID = IL6;
885
886 CURSOR ASEL_ATO_LINES(LN_ID NUMBER) IS
887 SELECT LINE_ID,ATO_LINE_ID,ORDERED_QUANTITY,INVENTORY_ITEM_ID,ORDER_QUANTITY_UOM
888 FROM OE_ORDER_LINES_ALL
889 WHERE ATO_LINE_ID = LN_ID;
890
891 CURSOR AN_LIST_PRICE (INVENT NUMBER,UNT NUMBER,NEW_NO NUMBER) IS
892 /*SELECT LIST_PRICE
893 FROM SO_PRICE_LIST_LINES_115
894 WHERE INVENTORY_ITEM_ID = INVENT
895 AND UNIT_CODE = UNT
896 AND PRICE_LIST_ID = NEW_NO
897 AND SYSDATE BETWEEN
898 NVL(START_DATE_ACTIVE,SYSDATE)
899 AND NVL(END_DATE_ACTIVE,SYSDATE);*/ /*Changed cursor query for bug 16574430 */
900
901 SELECT qpll.operand list_price
902 FROM qp_list_lines qpll,
903 qp_pricing_attributes qppr
904 WHERE qppr.list_line_id = qpll.list_line_id
905 AND qpll.list_header_id = new_no
906 AND qppr.PRODUCT_ATTRIBUTE_CONTEXT = 'ITEM'
907 AND qppr.product_attribute = 'PRICING_ATTRIBUTE1'
908 AND qppr.product_attr_value = to_char(invent)
909 and qppr.PRICING_ATTRIBUTE_CONTEXT is null
910 and qppr.PRICING_ATTRIBUTE is null
911 and qppr.EXCLUDER_FLAG = 'N'
912 AND qppr.product_uom_code = unt
913 AND nvl(qpll.start_date_active, sysdate -1) <= sysdate
914 AND nvl(qpll.end_date_active, sysdate + 1) >= sysdate
915 and qpll.LIST_LINE_TYPE_CODE='PLL'
916 and qpll.pricing_phase_id=1
917 and mod(qpll.QUALIFICATION_IND,2)=0;
918
919 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_ato_assessable_value';
920
921 BEGIN
922 FOR I IN ASEL_LINE_ID
923 LOOP
924 FOR J IN ASEL_ATO_LINES(I.LINE_ID)
925 LOOP
926 FOR K IN AN_LIST_PRICE(J.INVENTORY_ITEM_ID,J.ORDER_QUANTITY_UOM,NEW_ASSESS_LIST)
927 LOOP
928 ASUM_TOT := K.LIST_PRICE * J.ORDERED_QUANTITY;
929 END LOOP;
930 END LOOP;
931 FOR L IN AN_LIST_PRICE(I.INVENTORY_ITEM_ID,I.ORDER_QUANTITY_UOM,NEW_ASSESS_LIST)
932 LOOP
933 ASUM_TOT := L.LIST_PRICE * I.ORDERED_QUANTITY;
934 ANO := I.ORDERED_QUANTITY;
935 END LOOP;
936 APRICE := ASUM_TOT/ANO;
937 END LOOP;
938 NAMOUNT := APRICE;
939 EXCEPTION
940 WHEN OTHERS THEN
941 namount := null;
942 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
943 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
944 app_exception.raise_exception;
945 END get_ato_assessable_value;
946
947
948 FUNCTION validate_excise_exemption
949 (
950 p_line_id JAI_OM_OE_SO_LINES.LINE_ID%TYPE ,
951 p_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ,
952 p_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ,
953 p_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ,
954 p_error_msg OUT NOCOPY VARCHAR2
955 )
956 RETURN VARCHAR2
957 /***************************************************************************************************************************************************************
958
959 Created By : Aiyer
960
961 Created Date : 11-Feb-2004
962
963 Bug : 3436541
964
965 Purpose : This function validates the different valid combination of values that can exist between
966 JAI_OM_OE_SO_LINES.excise_exempt_type and tax types associated with the table JAI_OM_OE_SO_TAXES
967
968 A sales order In India Localization Order Management should be allowed to be shipped only when the
969 following conditions are satisfied: -
970 1. A Sales order with excise exemption types (field JAI_OM_OE_SO_LINES.excise_exempt_type) like
971 'EXCISE_EXEMPT_CERT', 'CT2','CT3' should not have Modvat Receovery type of taxes attached
972 ( table JAI_OM_OE_SO_TAXES)
973
974 2. A Sales order with excise exemption types (field JAI_OM_OE_SO_LINES.excise_exempt_type) like
975 'EXCISE_EXEMPT_CERT_OTH', 'CT2_OTH' should have modvat recovery type of tax attached
976 ( table JAI_OM_OE_SO_TAXES)
977
978 3. A sales order which does not have any excise exemptions specified (Null value for field
979 JAI_OM_OE_SO_LINES.excise_exempt_type) should not have any Modvat Recovery type of taxes
980 ( table JAI_OM_OE_SO_TAXES).
981
982 This function returns an error when any of the above conditions are not satisified.
983
984 Return Status : Returns 'EE' -> Expected Error
985 Out parameter p_error_msg populated with business logic violation message.
986
987 Returns 'UE' -> Unexpected Error
988 Out parameter p_error_msg populated with sqlerrm error message.
989
990 Returns 'S' -> Indicates Success.
991 Out parameter p_error_msg populated with Null value
992
993 Called From : 1. Procedure validate_exc_exmpt_cond (which is called from Key Exit trigger)
994 in the Sales Order India Localization Form (File Name JAINEORD.fmb ).
995
996 2. Trigger ja_in_wsh_dlry_au_rel_stat_trg (File Name ja_in_wsh_dlry_au_rel_stat_trg.sql).
997
998 Dependency Due To The Current Bug :
999 1. Form JAINEORD.fmb (618.3) and Trigger ja_in_wsh_dlry_au_rel_stat_trg.sql (618.1)
1000 call the function ->validate_excise_exemption(618.1)
1001
1002 Change History
1003 ==============
1004 Future Dependencies For the release Of this Object:-
1005 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
1006 A datamodel change )
1007
1008 ----------------------------------------------------------------------------------------------------------------------------------------------------
1009 Current Version Current Bug Dependent Files Version Author Date Remarks
1010 Of File On Bug/Patchset Dependent On
1011 validate_excise_exemption_f.sql
1012 ----------------------------------------------------------------------------------------------------------------------------------------------------
1013 618.1 3436541 IN60105D2 None -- Aiyer 11/02/2004 This object is not dependent on any object however,
1014 this object is called from Form JAINEORD.fmb and
1015 trigger ja_in_wsh_dlry_au_rel_stat_trg.sql
1016 ----------------------------------------------------------------------------------------------------------------------------------------------------
1017
1018 ***********************************************************************************************************************************************************/
1019 IS
1020 CURSOR c_chk_modvat_rectax
1021 IS
1022 SELECT
1023 '1'
1024 FROM
1025 JAI_OM_OE_SO_TAXES tl,
1026 JAI_CMN_TAXES_ALL tc
1027 WHERE
1028 tc.tax_type = jai_constants.tax_type_modvat_recovery AND /*--'Modvat Recovery' Ramananda for removal of SQL LITERALs :bug#4428980*/
1029 tc.tax_id = tl.tax_id AND
1030 tl.line_id = p_line_id ;
1031
1032 lv_exists VARCHAR2(1) ;
1033
1034 BEGIN
1035
1036 p_error_msg := NULL;
1037 OPEN c_chk_modvat_rectax;
1038 FETCH c_chk_modvat_rectax INTO lv_exists;
1039 /*
1040 Validate that EXCISE_EXEMPT_CERT, CT2,CT3 types of exemption types should not have
1041 modvat types of taxes attached because the basis of the modvat reversal will be based on
1042 the setup in the additional organization information.
1043 */
1044 IF nvl(p_excise_exempt_type,'###') IN ('EXCISE_EXEMPT_CERT_OTH', 'CT2_OTH') THEN
1045 IF c_chk_modvat_rectax%NOTFOUND THEN
1046 CLOSE c_chk_modvat_rectax;
1047 p_error_msg := 'Modvat Type of a tax must be entered for the Sales Order line with Line Number ' ||p_line_number ||
1048 ' and Shipment line Number '||p_shipment_line_number;
1049 return ('EE');
1050 END IF;
1051
1052 /*
1053 For the EXCISE_EXEMPT_CERT_OTH, CT2_OTH types of excise exemptions , modvat recovery type
1054 of tax has to be entered and the modvat recovery will be done based on the actual amount of
1055 excise tax levied in the sales order.
1056 Also sales order which does not have any excise exemptions specified should not have any Modvat Recovery type of taxes
1057 */
1058 ELSIF nvl(p_excise_exempt_type,'###') IN ('###','EXCISE_EXEMPT_CERT', 'CT2', 'CT3') THEN
1059 IF c_chk_modvat_rectax%FOUND THEN
1060 CLOSE c_chk_modvat_rectax;
1061 p_error_msg := 'Modvat Type of a tax should not be entered for the line with Line Number ' ||p_line_number ||
1062 ' and Shipment line Number '||p_shipment_line_number;
1063 return ('EE');
1064 END IF;
1065 END IF;
1066 CLOSE c_chk_modvat_rectax;
1067 p_error_msg := NULL;
1068 return ('S');
1069
1070 Exception
1071
1072 WHEN OTHERS THEN
1073 /* Handle all unexpected errors. */
1074 p_error_msg := 'Unexpected error occured in function validate_excise_exemption - '||sqlerrm;
1075 return jai_constants.unexpected_error;
1076 END validate_excise_exemption;
1077
1078 END jai_om_utils_pkg;
[Home] [Help]