1 PACKAGE BODY JAI_OM_UTILS_PKG AS
2 /* $Header: jai_om_utils.plb 120.2 2006/03/27 14:24:57 hjujjuru 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 *--------------------------------------------------------------------------------------*/
18
19 PROCEDURE get_ato_pricelist_value
20 (
21 NEW_LIST NUMBER,
22 UNIT_CODE NUMBER,
23 INVENTORY_ID NUMBER,
24 IL6 NUMBER,
25 NAMOUNT OUT NOCOPY NUMBER
26 )
27 IS
28
29 PRICE NUMBER;
30 SUM_TOT NUMBER;
31 NO NUMBER;
32
33 -- Changed For Migration To R11i on 17-10-2000 by A.Raina
34 -- Table "SO_LINES_ALL IS REPLACED BY "OE_ORDER_LINES_ALL"
35 -- so field "UNIT_CODE" is replace by "ORDER_QUANTITY_UOM" .
36 -- Also table "SO_PRICE_LIST_LINES" is replaced by "SO_PRICE_LIST_LINES_115"
37
38 CURSOR sel_line_id is
39 SELECT line_id,ordered_quantity,order_quantity_uom,inventory_item_id
40 FROM oe_order_lines_all
41 WHERE line_id = il6;
42
43 CURSOR sel_ato_lines(ln_id number) is select line_id,ato_line_id,ordered_quantity,inventory_itEM_ID,ORDER_QUANTITY_UOM
44 FROM oe_order_lines_all
45 WHERE ato_line_id = ln_id;
46
47 CURSOR n_lst_price(id number,unt varchar2) is select list_price
48 FROM so_price_list_lines_115
49 WHERE inventory_item_id = id
50 AND unit_code = unt
51 AND price_list_id = new_list;
52
53 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_ato_pricelist_value';
54
55 BEGIN
56
57 FOR I IN SEL_LINE_ID ---------------------------------------------------------------- (1)
58 LOOP
59 FOR J IN SEL_ATO_LINES(I.LINE_ID) -------------------------------------------------(2)
60 LOOP
61 FOR K IN N_LST_PRICE(J.INVENTORY_ITEM_ID,J.ORDER_QUANTITY_UOM) ----------------(3)
62 LOOP
63 SUM_TOT := K.LIST_PRICE * J.ORDERED_QUANTITY;
64 NO := NO + J.ORDERED_QUANTITY;
65 END LOOP; -------------------------(3)
66 END LOOP; ------------------------------------------------- (2)
67
68 FOR L IN N_LST_PRICE(I.INVENTORY_ITEM_ID,I.ORDER_QUANTITY_UOM) ------------------- (4)
69 LOOP
70 SUM_TOT := L.LIST_PRICE * I.ORDERED_QUANTITY;
71 NO := NO + I.ORDERED_QUANTITY;
72 END LOOP; -----------------------------(4)
73 PRICE := SUM_TOT/NO;
74 END LOOP; ---------------------------------------------------------------- (1)
75 NAMOUNT := PRICE;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 NAMOUNT := null;
80 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
81 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
82 app_exception.raise_exception;
83 END get_ato_pricelist_value ;
84
85
86 function get_oe_assessable_value
87 (
88 p_customer_id IN NUMBER,
89 p_ship_to_site_use_id IN NUMBER,
90 p_inventory_item_id IN NUMBER,
91 p_uom_code IN VARCHAR2,
92 p_default_price IN NUMBER,
93 p_ass_value_date IN DATE, --DEFAULT SYSDATE --Added global variable gd_ass_value_date in package spec. by Ramananda for File.Sql.35
94 /* Bug 5096787. Added the following parameters */
95 p_sob_id IN NUMBER ,
96 p_curr_conv_code IN VARCHAR2 ,
97 p_conv_rate IN NUMBER
98 )
99 RETURN NUMBER IS
100
101 CURSOR address_cur ( p_ship_to_site_use_id IN NUMBER )
102 IS
103 SELECT NVL(cust_acct_site_id , 0) address_id
104 FROM hz_cust_site_uses_all A -- Removed ra_site_uses_all for Bug# 4434287
105 WHERE A.site_use_id = p_ship_to_site_use_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
106 --WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
107
108 /*
109 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, uom code, ,Ordered date.
110 Exact Match condition
111 */
112 CURSOR c_assessable_value
113 ( p_customer_id NUMBER ,
114 p_address_id NUMBER ,
115 p_inventory_item_id NUMBER ,
116 p_uom_code VARCHAR2,
117 p_ordered_date DATE
118 )
119 IS
120 SELECT
121 b.operand list_price,
122 c.product_uom_code list_price_uom_code
123 FROM
124 JAI_CMN_CUS_ADDRESSES a,
125 qp_list_lines b,
126 qp_pricing_attributes c
127 WHERE
128 a.customer_id = p_customer_id AND
129 a.address_id = p_address_id AND
130 a.price_list_id = b.LIST_header_ID AND
131 c.list_line_id = b.list_line_id AND
132 c.product_attr_value = to_char(p_inventory_item_id) AND
133 c.product_uom_code = p_uom_code AND
134 p_ordered_date BETWEEN nvl( start_date_active, p_ordered_date) AND
135 nvl( end_date_active, SYSDATE);
136
137 /*
138 Get the assessable Value based on the Customer Id, Address Id, inventory_item_id, Ordered date.
139 Exact Match condition
140 */
141 --Added by Nagaraj.s for Bug3700249
142 CURSOR c_assessable_value_pri_uom(
143 p_customer_id NUMBER,
144 p_address_id NUMBER,
145 p_inventory_item_id NUMBER,
146 p_ordered_date DATE
147 )
148 IS
149 SELECT
150 b.operand list_price,
151 c.product_uom_code list_price_uom_code
152 FROM
153 JAI_CMN_CUS_ADDRESSES a,
154 qp_list_lines b,
155 qp_pricing_attributes c
156 WHERE
157 a.customer_id = p_customer_id AND
158 a.address_id = p_address_id AND
159 a.price_list_id = b.list_header_id AND
160 c.list_line_id = b.list_line_id AND
161 c.product_attr_value = to_char(p_inventory_item_id) AND
162 trunc(nvl(b.end_date_active,sysdate)) >= trunc(p_ordered_date) AND
163 primary_uom_flag ='Y'; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
164 --nvl(primary_uom_flag,'N') ='Y';
165
166 CURSOR c_assessable_value_other_uom
167 (
168 p_customer_id NUMBER,
169 p_address_id NUMBER,
170 p_inventory_item_id NUMBER,
171 p_ordered_date DATE
172 )
173 IS
174 SELECT
175 b.operand list_price,
176 c.product_uom_code list_price_uom_code
177 FROM
178 JAI_CMN_CUS_ADDRESSES a,
179 qp_list_lines b,
180 qp_pricing_attributes c
181 WHERE
182 a.customer_id = p_customer_id AND
183 a.address_id = p_address_id AND
184 a.price_list_id = b.LIST_header_ID AND
185 c.list_line_id = b.list_line_id AND
186 c.PRODUCT_ATTR_VALUE = TO_CHAR(p_inventory_item_id) AND
187 (b.end_date_active is null OR b.end_date_active >= p_ordered_date); /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
188 --NVL(b.end_date_active,SYSDATE) >= p_ordered_date;
189
190 v_primary_uom_code qp_pricing_attributes.product_uom_code%type; --Added by Nagaraj.s for Bug3700249
191 v_other_uom_code qp_pricing_attributes.product_uom_code%type; --Added by Nagaraj.s for Bug3700249
192
193 v_debug VARCHAR2(1); --File.Sql.35 Cbabu := 'N';
194 v_address_id NUMBER;
195 v_assessable_value NUMBER;
196 v_conversion_rate NUMBER;
197 v_price_list_uom_code VARCHAR2(4);
198 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_oe_assessable_value';
199
200 BEGIN
201 v_debug := 'N';
202
203 /*----------------------------------------------------------------------------------------------------------------------------
204 Change History for File -> get_oe_assessable_value_f.sql
205 S.No. DD/MM/YY Author AND Details
206 ------------------------------------------------------------------------------------------------------------------------------
207 1 25/03/03 Vijay Shankar for Bug# 2837970, FileVersion: 615.1
208 This function is written for CRM Localization Print Quote Taxes functionality. But this function can be called
209 from anywhere in Order Management to fetch the assessable value. Required Parameter needs to be passed during
210 invocation of this procedure.
211 Basically this fetches the assessable value of an ITEM based on the Customer additional information setup
212 If assessable value is not found then it returns the value passed as P_DEFAULT_PRICE
213 This is a duplicate code for jai_cmn_tax_defaultation_pkg.ja_in_cust_default_taxes procedure
214
215 2. 2004/14/07 Aiyer - bug # 3700249 File Version 115.2
216 Issue
217 The assessable value does not get calculated properly.
218
219 Solution
220 The following 5 level assessable value derivation logic has been implemented now:-
221 Each Logic will come into picture only if the preceding one does not get any value.
222 1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
223 2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
224
225 3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
226 for the Primary UOM defined in Price List.
227 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
228 as the product of the Assessable value.
229 4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
230 on a first come first serve basis.
231 5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
232 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
233 as the product of the Assessable value.
234
235 Dependency Due to this Bug:-
236 None
237
238 Future Dependencies For the release Of this Object:-
239 (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/
240 A datamodel change )
241
242 ----------------------------------------------------------------------------------------------------------------------------------------------------
243 Current Version Current Bug Dependent Files Version Author Date Remarks
244 Of File On Bug/Patchset Dependent On
245 get_oe_assessable_value_f.sql
246 ----------------------------------------------------------------------------------------------------------------------------------------------------
247 115.2 3700249 IN60105D2 None -- Aiyer 14/07/2004 Row introduces to start dependency tracking
248
249 ----------------------------------------------------------------------------------------------------------------------------------------------------*/
250
251 /******************************** Part 1 Get Customer address id ******************************/
252 OPEN address_cur(p_ship_to_site_use_id);
253 FETCH address_cur INTO v_address_id;
254 CLOSE address_cur;
255
256
257 IF v_debug = 'Y' THEN
258 fnd_file.put_line(fnd_file.log, 'v_address_id -> '||v_address_id);
259 END IF;
260
261
262 ----------------------------------------------------------------------------------------------------------
263 /*
264 --Assessable Value Fetching Logic is based upon the following logic now.....
265 --Each Logic will come into picture only if the preceding one does not get any value.
266 --1. Assessable Value is picked up for the Customer Id, Address Id, UOM Code, inventory_item_id,Assessable value date
267 --2. Assessable Value is picked up for the Customer Id, Null Site, UOM Code, Assessable value date
268
269 --3. Assessable Value and Primary UOM is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
270 for the Primary UOM defined in Price List.
271 Then Inv_convert.Inv_um_conversion is called and the UOM rate is calculated and is included
272 as the product of the Assessable value.
273 --4. Assessable Value is picked up for the Customer Id, Address Id, inventory_item_id, Assessable value date
274 on a first come first serve basis.
275 --5. If all the above are not found then the initial logic of picking up the Assessable value is followed (Unit selling price)
276 and then inv_convert.inv_um_conversion is called and the UOM rate is calculated and is included
277 as the product of the Assessable value.
278 */
279 ----------------------------------------------------------------------------------------------------------
280
281 /********************************************* Part 2 ****************************************/
282
283 /*
284 Get the Assessable Value based on the Customer Id, Address Id, UOM Code, inventory_item_id,Ordered date
285 Exact Match condition.
286 */
287
288 -- Fetch Assessable Price List Value for the given Customer and Location Combination
289 OPEN c_assessable_value( p_customer_id, v_address_id, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date));
290 FETCH c_assessable_value INTO v_assessable_value, v_price_list_uom_code;
291 CLOSE c_assessable_value;
292
293 /********************************************* Part 3 ****************************************/
294
295 /*
296 Get the Assessable Value based on the Customer Id, Null Site, UOM Code, inventory_item_id,Ordered date
297 Null Site condition.
298 */
299
300 IF v_assessable_value IS NULL THEN
301
302 IF v_debug = 'Y' THEN
303 fnd_file.put_line(fnd_file.log,' Inside IF OF v_assessable_value IS NULL ');
304 END IF;
305
306 -- Fetch Assessable Price List Value for the
307 -- given Customer and NULL LOCATION Combination
308 OPEN c_assessable_value( p_customer_id, 0, p_inventory_item_id, p_uom_code, trunc(p_ass_value_date) );
309 FETCH c_assessable_value INTO v_assessable_value, v_price_list_uom_code;
310 CLOSE c_assessable_value;
311
312 END IF;
313
314 IF v_debug = 'Y' THEN
315 fnd_file.put_line(fnd_file.log, '2 v_assessable_value -> '||v_assessable_value||', v_price_list_uom_code -> '||v_price_list_uom_code);
316 END IF;
317
318 /********************************************* Part 4 ****************************************/
319
320 /*
321 Get the Assessable Value based on the Customer Id, Address id, inventory_item_id,primary_uom_code and Ordered date
322 Primary UOM condition.
323 */
324
325 --Added by Aiyer for Bug 3700249
326 IF v_assessable_value is null THEN
327
328 open c_assessable_value_pri_uom
329 (
330 p_customer_id,
331 v_address_id,
332 p_inventory_item_id,
333 trunc(p_ass_value_date)
334 );
335 fetch c_assessable_value_pri_uom into v_assessable_value,v_primary_uom_code;
336 close c_assessable_value_pri_uom;
337
338 IF v_primary_uom_code is not null THEN
339
340 inv_convert.inv_um_conversion
341 (
342 p_uom_code,
343 v_primary_uom_code,
344 p_inventory_item_id,
345 v_conversion_rate
346 );
347
348
349 IF nvl(v_conversion_rate, 0) <= 0 THEN
350 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
351 IF NVL(v_conversion_rate, 0) <= 0 THEN
352 v_conversion_rate := 0;
353 END IF;
354 END IF;
355
356 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
357
358 ELSE
359 /* Primary uom code setup not found for the customer id, address id, inventory_item_id and ordered_date.
360 Get the assessable value for a combination of customer id, address id, inventory_item_id
361 and ordered_date. Pick up the assessable value by first come first serve basis.
362 */
363
364 OPEN c_assessable_value_other_uom
365 (
366 p_customer_id,
367 v_address_id,
368 p_inventory_item_id,
369 trunc(p_ass_value_date)
370 );
371 FETCH c_assessable_value_other_uom into v_assessable_value,v_other_uom_code;
372 CLOSE c_assessable_value_other_uom;
373
374 IF v_other_uom_code is not null THEN
375 inv_convert.inv_um_conversion
376 (
377 p_uom_code,
378 v_other_uom_code,
379 p_inventory_item_id,
380 v_conversion_rate
381 );
382
383 IF nvl(v_conversion_rate, 0) <= 0 THEN
384
385 Inv_Convert.inv_um_conversion( p_uom_code, v_primary_uom_code, 0, v_conversion_rate );
386
387 IF NVL(v_conversion_rate, 0) <= 0 THEN
388 v_conversion_rate := 0;
389 END IF;
390 END IF;
391 v_assessable_value := NVL(v_assessable_value,0) * v_conversion_rate;
392
393 END IF; --end if for v_other_uom_code is not null
394 END IF; --end if for v_primary_uom_code is not null
395 END IF; --end if for v_assessable_value
396 --Ends here..........................
397
398
399 /*
400 IF NVL(v_assessable_value,0) > 0 THEN
401
402 -- If still the Assessable Value is available
403 IF v_price_list_uom_code IS NOT NULL THEN
404
405 IF v_debug = 'Y' THEN
406 fnd_file.put_line(fnd_file.log,' BEFORE Calling Inv_Convert.inv_um_conversion 1');
407 END IF;
408
409 Inv_Convert.inv_um_conversion ( v_uom_code, v_price_list_uom_code, v_inventory_item_id, v_conversion_rate );
410 IF NVL(v_conversion_rate, 0) <= 0 THEN
411
412 IF v_debug = 'Y' THEN
413 fnd_file.put_line(fnd_file.log,' BEFORE Calling Inv_Convert.inv_um_conversion 2');
414 END IF;
415
416 Inv_Convert.inv_um_conversion(v_uom_code, v_price_list_uom_code, 0, v_conversion_rate);
417 IF NVL(v_conversion_rate, 0) <= 0 THEN
418 v_conversion_rate := 0;
419 END IF;
420
421 END IF;
422
423 END IF;
424
425 v_assessable_value := nvl(v_assessable_value,0) * v_conversion_rate;
426 -- v_assessable_value := NVL(1/v_converted_rate,0) * NVL(v_assessable_value,0) * v_conversion_rate;
427 -- v_assessable_amount := NVL(v_assessable_value,0) * v_line_quantity;
428
429 ELSE
430
431 IF v_debug = 'Y' THEN
432 fnd_file.put_line(fnd_file.log,' inside ELSE OF v_assessable_value IS NULL ');
433 END IF;
434
435 -- If the assessable value is not available
436 -- then pick up the Line price for Tax Calculation
437 v_assessable_value := NVL(p_default_price, 0);
438 -- v_assessable_amount := v_line_amount;
439
440 END IF; -- v_assessable_value IS NULL THEN
441 */
442
443 IF nvl(v_assessable_value,0) =0 THEN
444 IF v_debug = 'Y' THEN
445 fnd_file.put_line(fnd_file.log,' No Assessable value is defined, so default price is returning back ');
446 END IF;
447
448 v_assessable_value := NVL(p_default_price, 0);
449 END IF;
450
451 RETURN v_assessable_value;
452 EXCEPTION
453 WHEN OTHERS THEN
454 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
455 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
456 app_exception.raise_exception;
457
458 END get_oe_assessable_value;
459
460
461 procedure get_ato_assessable_value
462 (
463 NEW_ASSESS_LIST NUMBER,
464 IL6 NUMBER ,
465 NAMOUNT OUT NOCOPY NUMBER
466 )
467 IS
468 APRICE NUMBER;
469 ASUM_TOT NUMBER;
470 ANO NUMBER;
471
472 -- Changed For Migration To R11i on 17-10-2000 by A.Raina
473 -- Table "SO_LINES_ALL IS REPLACED BY "OE_ORDER_LINES_ALL"
474 -- so field "UNIT_CODE" is replace by "ORDER_QUANTITY_UOM" .
475 -- Also table "SO_PRICE_LIST_LINES" is replaced by "SO_PRICE_LIST_LINES_115"
476
477 CURSOR ASEL_LINE_ID IS
478 SELECT LINE_ID,ORDERED_QUANTITY,ORDER_QUANTITY_UOM,INVENTORY_ITEM_ID
479 FROM OE_ORDER_LINES_ALL
480 WHERE LINE_ID = IL6;
481
482 CURSOR ASEL_ATO_LINES(LN_ID NUMBER) IS
483 SELECT LINE_ID,ATO_LINE_ID,ORDERED_QUANTITY,INVENTORY_ITEM_ID,ORDER_QUANTITY_UOM
484 FROM OE_ORDER_LINES_ALL
485 WHERE ATO_LINE_ID = LN_ID;
486
487 CURSOR AN_LIST_PRICE (INVENT NUMBER,UNT NUMBER,NEW_NO NUMBER) IS
488 SELECT LIST_PRICE
489 FROM SO_PRICE_LIST_LINES_115
490 WHERE INVENTORY_ITEM_ID = INVENT
491 AND UNIT_CODE = UNT
492 AND PRICE_LIST_ID = NEW_NO
493 AND SYSDATE BETWEEN
494 NVL(START_DATE_ACTIVE,SYSDATE)
495 AND NVL(END_DATE_ACTIVE,SYSDATE);
496
497 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_utils_pkg.get_ato_assessable_value';
498
499 BEGIN
500 FOR I IN ASEL_LINE_ID
501 LOOP
502 FOR J IN ASEL_ATO_LINES(I.LINE_ID)
503 LOOP
504 FOR K IN AN_LIST_PRICE(J.INVENTORY_ITEM_ID,J.ORDER_QUANTITY_UOM,NEW_ASSESS_LIST)
505 LOOP
506 ASUM_TOT := K.LIST_PRICE * J.ORDERED_QUANTITY;
507 END LOOP;
508 END LOOP;
509 FOR L IN AN_LIST_PRICE(I.INVENTORY_ITEM_ID,I.ORDER_QUANTITY_UOM,NEW_ASSESS_LIST)
510 LOOP
511 ASUM_TOT := L.LIST_PRICE * I.ORDERED_QUANTITY;
512 ANO := I.ORDERED_QUANTITY;
513 END LOOP;
514 APRICE := ASUM_TOT/ANO;
515 END LOOP;
516 NAMOUNT := APRICE;
517 EXCEPTION
518 WHEN OTHERS THEN
519 namount := null;
520 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
521 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
522 app_exception.raise_exception;
523 END get_ato_assessable_value;
524
525
526 FUNCTION validate_excise_exemption
527 (
528 p_line_id JAI_OM_OE_SO_LINES.LINE_ID%TYPE ,
529 p_excise_exempt_type JAI_OM_OE_SO_LINES.EXCISE_EXEMPT_TYPE%TYPE ,
530 p_line_number JAI_OM_OE_SO_LINES.LINE_NUMBER%TYPE ,
531 p_shipment_line_number JAI_OM_OE_SO_LINES.SHIPMENT_LINE_NUMBER%TYPE ,
532 p_error_msg OUT NOCOPY VARCHAR2
533 )
534 RETURN VARCHAR2
535 /***************************************************************************************************************************************************************
536
537 Created By : Aiyer
538
539 Created Date : 11-Feb-2004
540
541 Bug : 3436541
542
543 Purpose : This function validates the different valid combination of values that can exist between
544 JAI_OM_OE_SO_LINES.excise_exempt_type and tax types associated with the table JAI_OM_OE_SO_TAXES
545
546 A sales order In India Localization Order Management should be allowed to be shipped only when the
547 following conditions are satisfied: -
548 1. A Sales order with excise exemption types (field JAI_OM_OE_SO_LINES.excise_exempt_type) like
549 'EXCISE_EXEMPT_CERT', 'CT2','CT3' should not have Modvat Receovery type of taxes attached
550 ( table JAI_OM_OE_SO_TAXES)
551
552 2. A Sales order with excise exemption types (field JAI_OM_OE_SO_LINES.excise_exempt_type) like
553 'EXCISE_EXEMPT_CERT_OTH', 'CT2_OTH' should have modvat recovery type of tax attached
554 ( table JAI_OM_OE_SO_TAXES)
555
556 3. A sales order which does not have any excise exemptions specified (Null value for field
557 JAI_OM_OE_SO_LINES.excise_exempt_type) should not have any Modvat Recovery type of taxes
558 ( table JAI_OM_OE_SO_TAXES).
559
560 This function returns an error when any of the above conditions are not satisified.
561
562 Return Status : Returns 'EE' -> Expected Error
563 Out parameter p_error_msg populated with business logic violation message.
564
565 Returns 'UE' -> Unexpected Error
566 Out parameter p_error_msg populated with sqlerrm error message.
567
568 Returns 'S' -> Indicates Success.
569 Out parameter p_error_msg populated with Null value
570
571 Called From : 1. Procedure validate_exc_exmpt_cond (which is called from Key Exit trigger)
572 in the Sales Order India Localization Form (File Name JAINEORD.fmb ).
573
574 2. Trigger ja_in_wsh_dlry_au_rel_stat_trg (File Name ja_in_wsh_dlry_au_rel_stat_trg.sql).
575
576 Dependency Due To The Current Bug :
577 1. Form JAINEORD.fmb (618.3) and Trigger ja_in_wsh_dlry_au_rel_stat_trg.sql (618.1)
578 call the function ->validate_excise_exemption(618.1)
579
580 Change History
581 ==============
582 Future Dependencies For the release Of this Object:-
583 (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/
584 A datamodel change )
585
586 ----------------------------------------------------------------------------------------------------------------------------------------------------
587 Current Version Current Bug Dependent Files Version Author Date Remarks
588 Of File On Bug/Patchset Dependent On
589 validate_excise_exemption_f.sql
590 ----------------------------------------------------------------------------------------------------------------------------------------------------
591 618.1 3436541 IN60105D2 None -- Aiyer 11/02/2004 This object is not dependent on any object however,
592 this object is called from Form JAINEORD.fmb and
593 trigger ja_in_wsh_dlry_au_rel_stat_trg.sql
594 ----------------------------------------------------------------------------------------------------------------------------------------------------
595
596 ***********************************************************************************************************************************************************/
597 IS
598 CURSOR c_chk_modvat_rectax
599 IS
600 SELECT
601 '1'
602 FROM
603 JAI_OM_OE_SO_TAXES tl,
604 JAI_CMN_TAXES_ALL tc
605 WHERE
606 tc.tax_type = jai_constants.tax_type_modvat_recovery AND /*--'Modvat Recovery' Ramananda for removal of SQL LITERALs :bug#4428980*/
607 tc.tax_id = tl.tax_id AND
608 tl.line_id = p_line_id ;
609
610 lv_exists VARCHAR2(1) ;
611
612 BEGIN
613
614 p_error_msg := NULL;
615 OPEN c_chk_modvat_rectax;
616 FETCH c_chk_modvat_rectax INTO lv_exists;
617 /*
618 Validate that EXCISE_EXEMPT_CERT, CT2,CT3 types of exemption types should not have
619 modvat types of taxes attached because the basis of the modvat reversal will be based on
620 the setup in the additional organization information.
621 */
622 IF nvl(p_excise_exempt_type,'###') IN ('EXCISE_EXEMPT_CERT_OTH', 'CT2_OTH') THEN
623 IF c_chk_modvat_rectax%NOTFOUND THEN
624 CLOSE c_chk_modvat_rectax;
625 p_error_msg := 'Modvat Type of a tax must be entered for the Sales Order line with Line Number ' ||p_line_number ||
626 ' and Shipment line Number '||p_shipment_line_number;
627 return ('EE');
628 END IF;
629
630 /*
631 For the EXCISE_EXEMPT_CERT_OTH, CT2_OTH types of excise exemptions , modvat recovery type
632 of tax has to be entered and the modvat recovery will be done based on the actual amount of
633 excise tax levied in the sales order.
634 Also sales order which does not have any excise exemptions specified should not have any Modvat Recovery type of taxes
635 */
636 ELSIF nvl(p_excise_exempt_type,'###') IN ('###','EXCISE_EXEMPT_CERT', 'CT2', 'CT3') THEN
637 IF c_chk_modvat_rectax%FOUND THEN
638 CLOSE c_chk_modvat_rectax;
639 p_error_msg := 'Modvat Type of a tax should not be entered for the line with Line Number ' ||p_line_number ||
640 ' and Shipment line Number '||p_shipment_line_number;
641 return ('EE');
642 END IF;
643 END IF;
644 CLOSE c_chk_modvat_rectax;
645 p_error_msg := NULL;
646 return ('S');
647
648 Exception
649
650 WHEN OTHERS THEN
651 /* Handle all unexpected errors. */
652 p_error_msg := 'Unexpected error occured in function validate_excise_exemption - '||sqlerrm;
653 return jai_constants.unexpected_error;
654 END validate_excise_exemption;
655
656 END jai_om_utils_pkg;
[Home] [Help]