[Home] [Help]
PACKAGE BODY: APPS.JAI_OM_RMA_PKG
Source
1 PACKAGE BODY jai_om_rma_pkg AS
2 /* $Header: jai_om_rma.plb 120.7.12010000.2 2008/09/25 06:28:48 csahoo ship $ */
3
4 procedure default_taxes_onto_line (
5 p_header_id NUMBER,
6 p_line_id NUMBER,
7 p_inventory_item_id NUMBER,
8 p_warehouse_id NUMBER,
9 p_new_reference_line_id NUMBER,
10 p_new_ref_customer_trx_line_id NUMBER,
11 p_line_number NUMBER,
12 p_old_return_context VARCHAR2,
13 pn_delivery_detail_id NUMBER,
14 pv_allow_excise_flag VARCHAR2,
15 pv_allow_sales_flag VARCHAR2,
16 pn_excise_duty_per_unit NUMBER,
17 pn_excise_duty_rate NUMBER,
18 p_old_reference_line_id NUMBER,
19 p_old_ref_customer_trx_line_id NUMBER,
20 p_old_ordered_quantity NUMBER,
21 p_old_cancelled_quantity NUMBER,
22 p_new_return_context VARCHAR2,
23 p_new_ordered_quantity NUMBER,
24 p_new_cancelled_quantity NUMBER,
25 p_uom VARCHAR2,
26 p_old_selling_price NUMBER,
27 p_new_selling_price NUMBER, -- added by sriram Bug
28 p_item_type_code VARCHAR2,
29 p_serviced_quantity NUMBER,
30 p_creation_date DATE,
31 p_created_by NUMBER,
32 p_last_update_date DATE,
33 p_last_updated_by NUMBER,
34 p_last_update_login NUMBER,
35 p_source_document_type_id NUMBER,
36 p_line_category_code OE_ORDER_LINES_ALL.LINE_CATEGORY_CODE%TYPE
37 )
38 IS
39 v_category oe_order_headers_all.order_category_code % TYPE;
40 v_order_number oe_order_headers_all.order_number % TYPE;
41 v_ord_inv_quantity oe_order_lines_all.ordered_quantity % TYPE;
42 v_old_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
43 v_new_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
44 v_shipped_quantity wsh_delivery_details.shipped_quantity % TYPE;
45 v_quantity JAI_OM_WSH_LINES_ALL.quantity % TYPE;
46 v_cor_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
47 v_tax_category_id JAI_OM_WSH_LINES_ALL.tax_category_id % TYPE;
48 v_tax_amount JAI_OM_WSH_LINES_ALL.tax_amount % TYPE;
49 v_delivery_detail_id JAI_OM_WSH_LINES_ALL.delivery_detail_id % TYPE;
50 v_excise_return_days JAI_CMN_INVENTORY_ORGS.excise_return_days % TYPE;
51 v_sales_return_days JAI_CMN_INVENTORY_ORGS.sales_return_days % TYPE;
52 v_vat_return_days JAI_CMN_INVENTORY_ORGS.vat_return_days % TYPE; -- added, Harshita for bug#4245062
53 v_excise_flag VARCHAR2(1);
54 v_sales_flag VARCHAR2(1);
55 v_vat_flag VARCHAR2(1); -- added, Harshita for bug#4245062
56 v_vat_attribute VARCHAR2(1); -- added, Harshita for bug#4245062
57 v_date_ordered DATE;
58 v_date_confirmed DATE;
59 v_paddr v$session.paddr % TYPE;
60 v_chk_form VARCHAR2(30);
61 v_round_tax NUMBER;
62 v_round_base NUMBER;
63 v_round_func NUMBER;
64 v_tax_total NUMBER;
65 v_conf NUMBER;
66 v_test_id NUMBER;
67 v_exist_flag NUMBER := 0;
68 v_excise_duty_rate JAI_OM_OE_RMA_LINES.excise_duty_rate % TYPE;
69 v_rate_per_unit JAI_OM_OE_RMA_LINES.rate_per_unit % TYPE;
70 v_excise_total NUMBER;
71 v_rate_total NUMBER;
72 v_qty_total NUMBER;
73 v_manufacturing JAI_CMN_INVENTORY_ORGS.manufacturing%type; --Added by Nagaraj.s for Bug3113027
74 v_trading JAI_CMN_INVENTORY_ORGS.trading%type; --Added by Nagaraj.s for Bug3113027
75 v_service_type_code JAI_OM_OE_SO_LINES.service_type_code%type; /* added by ssawant for bug 5879769 */
76
77 CURSOR Localized_Order_Cur(c_delivery_detail_id NUMBER) IS
78 SELECT 1
79 FROM JAI_OM_WSH_LINES_ALL
80 WHERE delivery_detail_id = c_delivery_detail_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
81
82 -- cbabu for Bug#2523313, Start
83 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER) IS
84 SELECT requested_quantity_uom FROM wsh_delivery_details
85 WHERE delivery_detail_id = p_delivery_detail_id;
86
87 v_conversion_rate NUMBER := 0;
88 v_requested_quantity_uom VARCHAR2(3);
89 v_rma_quantity_uom VARCHAR2(3); -- := p_uom; --Ramananda for File.Sql.35
90
91 -- cbabu for Bug#2523313, End
92
93
94 -- added by sriram - bug # 2798596
95
96 cursor c_sales_order_cur is
97 select quantity ,service_type_code /* added by ssawant for bug 5879769 */
98 from JAI_OM_OE_SO_LINES
99 where line_id = p_new_reference_line_id;
100
101 Cursor C_SO_TAX_AMOUNT (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type) is
102 select tax_amount
103 from JAI_OM_OE_SO_TAXES
104 where line_id = p_new_reference_line_id
105 and tax_id = p_tax_id;
106
107 v_so_tax_amount Number;
108 v_orig_ord_qty Number;
109
110
111 -- additions by sriram - bug # 2798596 -- ends here
112
113 -- added, Harshita for bug#4245062
114 Cursor c_ordered_date_cur(v_header_id number) is
115 select
116 ordered_date
117 from
118 oe_order_headers_all
119 where
120 header_id = v_header_id;
121
122 -- following cursor is used to get the ship confirm date based on the delivery detail id
123
124 cursor c_confirmed_date (p_delivery_Detail_id Number) is
125 select
126 confirm_date
127 FROM
128 wsh_delivery_details wdd,
129 wsh_delivery_assignments wda,
130 wsh_new_deliveries wnd
131 WHERE
132 wdd.delivery_detail_id = (p_delivery_Detail_id)
133 AND
134 wda.delivery_detail_id = wdd.delivery_detail_id
135 AND
136 wnd.delivery_id = wda.delivery_id;
137
138 Cursor c_hr_organizations_cur( v_organization_id number ) is
139 SELECT vat_return_days
140 FROM JAI_CMN_INVENTORY_ORGS
141 WHERE organization_id = v_organization_id
142 AND location_id = 0;
143
144 v_ordered_date date ;
145 v_confirm_date date ;
146
147
148 -- end, Harshita for bug#4245062
149 /*bduvarag for the bug# 5256498 start*/
150 cursor c_check_Vat_type_Tax_exists (cp_tax_type VARCHAR2) IS
151 SELECT 1
152 FROM jai_regime_tax_types_v
153 WHERE regime_code = jai_constants.vat_regime
154 AND tax_type = cp_tax_type;
155
156 lv_check_vat_type_exists VARCHAR2(1);
157
158 /*bduvarag for the bug#5256498 end*/
159 /* Added for DFF Elimination by Ramananda */
160
161 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rma_pkg.default_taxes_onto_line';
162
163 -------------------------------------------------------------------------------------------------
164 PROCEDURE rma_insert IS
165 BEGIN
166
167 IF v_delivery_detail_id IS NOT NULL
168 THEN
169 v_ord_inv_quantity := p_new_ordered_quantity;
170 IF v_ord_inv_quantity <> 0
171 THEN
172 FOR pick_rec IN (SELECT quantity,
173 tax_category_id
174 FROM JAI_OM_WSH_LINES_ALL
175 WHERE delivery_detail_id = v_delivery_detail_id)
176 LOOP
177 v_quantity := pick_rec.quantity;
178 v_tax_category_id := pick_rec.tax_category_id;
179 END LOOP;
180
181 IF pn_excise_duty_per_unit IS NOT NULL AND
182 pn_excise_duty_rate IS NOT NULL
183 THEN
184 v_excise_duty_rate := pn_excise_duty_rate;
185 v_rate_per_unit := pn_excise_duty_per_unit;
186 ELSE
187 FOR duty_rec IN (SELECT rgd.excise_duty_rate,
188 rgd.rate_per_unit,
189 rgd.quantity_received
190 FROM JAI_CMN_RG_23D_TRXS rgd,
191 JAI_CMN_MATCH_RECEIPTS rm
192 WHERE rm.ref_line_id = v_delivery_detail_id
193 AND rgd.register_id = rm.receipt_id)
194 LOOP
195 v_excise_total := NVL(v_excise_total, 0) + NVL(duty_rec.excise_duty_rate, 0) *
196 NVL(duty_rec.quantity_received, 0);
197 v_rate_total := NVL(v_rate_total, 0) + NVL(duty_rec.rate_per_unit, 0) *
198 NVL(duty_rec.quantity_received, 0);
199 v_qty_total := NVL(v_qty_total, 0) + NVL(duty_rec.quantity_received, 0);
200 END LOOP;
201 IF NVL(v_excise_total, 0) <> 0 AND
202 NVL(v_qty_total, 0) <> 0
203 THEN
204 v_excise_duty_rate := ROUND((v_excise_total / v_qty_total), 2);
205 END IF;
206 IF NVL(v_rate_total, 0) <> 0 AND
207 NVL(v_qty_total, 0) <> 0
208 THEN
209 v_rate_per_unit := ROUND((v_rate_total / v_qty_total), 2);
210 END IF;
211 END IF;
212 ---------------------- For inserting record in JAI_OM_OE_RMA_LINES --------------------
213
214
215 INSERT INTO JAI_OM_OE_RMA_LINES
216 (rma_line_id,
217 rma_line_number,
218 rma_header_id,
219 rma_number,
220 delivery_detail_id,
221 uom,
222 selling_price,
223 quantity,
224 tax_category_id,
225 tax_amount,
226 inventory_item_id,
227 received_flag,
228 assessable_value,
229 excise_duty_rate,
230 rate_per_unit,
231 creation_date,
232 created_by,
233 last_update_date,
234 last_updated_by,
235 last_update_login,
236 /* Added for DFF Elimination by Ramananda */
237 allow_excise_credit_flag,
238 allow_sales_credit_flag,
239 service_type_code /* added by ssawant for bug 5879769 */
240 )
241 VALUES (p_line_id,
242 p_line_number,
243 p_header_id,
244 v_order_number,
245 v_delivery_detail_id,
246 P_uom,
247 p_new_selling_price,
248 v_ord_inv_quantity,
249 v_tax_category_id,
250 NULL,
251 p_inventory_item_id,
252 NULL, --received_flag
253 NULL, --assessable value
254 v_excise_duty_rate,
255 v_rate_per_unit,
256 p_creation_date,
257 p_created_by,
258 p_last_update_date,
259 p_last_updated_by,
260 p_last_update_login,
261 pv_allow_excise_flag,
262 pv_allow_sales_flag,
263 v_service_type_code /* added by ssawant for bug 5879769 */
264 );
265
266 IF v_quantity <> 0
267 THEN
268 -- cbabu for Bug#2523313, Start
269 OPEN requested_qty_uom_cur(v_delivery_detail_id);
270 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
271 CLOSE requested_qty_uom_cur;
272
273 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
274 v_rma_quantity_uom,
275 p_inventory_item_id,
276 v_conversion_rate);
277 IF NVL(v_conversion_rate, 0) <= 0 THEN
278 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
279 v_rma_quantity_uom,
280 0,
281 v_conversion_rate);
282 IF NVL(v_conversion_rate, 0) <= 0 THEN
283 v_conversion_rate := 0;
284 END IF;
285 END IF;
286 -- cbabu for Bug#2523313, End
287 v_cor_amount := (v_ord_inv_quantity / v_quantity)*(1/v_conversion_rate); -- cbabu for Bug#2523313
288
289 END IF;
290 FOR tax_line_rec IN (SELECT tax_line_no,
291 precedence_1,
292 precedence_2,
293 precedence_3,
294 precedence_4,
295 precedence_5,
296 sptl.tax_id,
297 sptl.tax_rate,
298 sptl.qty_rate,
299 uom,
300 sptl.tax_amount,
301 nvl(jtc.rounding_factor,0) rounding_factor,/*Bug 5989740 bduvarag*//*bduvarag for the bug#6071813*/
302 -- interfaced_flag,
303 base_tax_amount,
304 func_tax_amount,
305 jtc.tax_type ,
306 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
307 precedence_7,
308 precedence_8,
309 precedence_9,
310 precedence_10
311 FROM JAI_OM_WSH_LINE_TAXES sptl,
312 JAI_CMN_TAXES_ALL jtc
313 WHERE delivery_detail_id = v_delivery_detail_id
314 AND jtc.tax_id = sptl.tax_id)
315 LOOP
316
317
318 IF tax_line_rec.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
319 THEN
320 v_round_tax := ROUND((v_cor_amount * tax_line_rec.tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
321 v_round_base := ROUND((v_cor_amount * tax_line_rec.base_tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
322 v_round_func := ROUND((v_cor_amount * tax_line_rec.func_tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
323 ELSE
324 v_round_tax := ROUND((v_cor_amount * tax_line_rec.tax_amount), 2);
325 v_round_base := ROUND((v_cor_amount * tax_line_rec.base_tax_amount), 2);
326 v_round_func := ROUND((v_cor_amount * tax_line_rec.func_tax_amount), 2);
327 END IF;
328
329
330 /*
331 code segment added by sriram - bug # 2798596
332 */
333
334 open c_sales_order_cur;
335 fetch c_sales_order_cur into v_orig_ord_qty,v_service_type_code;/* added by ssawant for bug 5879769 */
336 close c_sales_order_cur;
337
338 /*
339 code segment added by sriram - ends here - bug # 2798596
340 */
341 /*bduvarag for the bug#5256498 start*/
342 lv_check_vat_type_exists := NULL;
343
344 OPEN c_check_Vat_type_Tax_exists (tax_line_rec.tax_type);
345 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
346 CLOSE c_check_Vat_type_Tax_exists;
347 /*bduvarag for the bug#5256498 end*/
348
349 IF (tax_line_rec.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS) AND /*Bug 5989740 bduvarag*/
350 v_excise_flag = 'N') OR
351 (tax_line_rec.tax_type IN ('Sales Tax', 'CST') AND
352 v_sales_flag = 'N') OR -- added, Harshita for bug#4245062
353 /*bduvarag for the bug#5256498 start*/
354 /* (tax_line_rec.tax_type IN ('TURNOVER TAX', 'VAT', 'ENTRY TAX', 'Octrai', 'PURCHASE TAX') AND
355 v_vat_flag = 'N') */
356 ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
357 /*bduvarag for the bug#5256498 end*/
358 THEN
359 v_round_tax := 0;
360 v_round_base := 0;
361 v_round_func := 0;
362 END IF;
363
364
365 INSERT INTO JAI_OM_OE_RMA_TAXES
366 (rma_line_id,
367 delivery_detail_id,
368 tax_line_no,
369 precedence_1,
370 precedence_2,
371 precedence_3,
372 precedence_4,
373 precedence_5,
374 tax_id,
375 tax_rate,
376 qty_rate,
377 uom,
378 tax_amount,
379 base_tax_amount,
380 func_tax_amount,
381 creation_date,
382 created_by,
383 last_update_date,
384 last_updated_by,
385 last_update_login ,
386 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
387 precedence_7,
388 precedence_8,
389 precedence_9,
390 precedence_10)
391 VALUES (p_line_id,
392 v_delivery_detail_id,
393 tax_line_rec.tax_line_no,
394 tax_line_rec.precedence_1,
395 tax_line_rec.precedence_2,
396 tax_line_rec.precedence_3,
397 tax_line_rec.precedence_4,
398 tax_line_rec.precedence_5,
399 tax_line_rec.tax_id,
400 tax_line_rec.tax_rate,
401 tax_line_rec.qty_rate,
402 tax_line_rec.uom,
403 v_round_tax,
404 v_round_base,
405 v_round_func,
406 p_creation_date,
407 p_created_by,
408 p_last_update_date,
409 p_last_updated_by,
410 p_last_update_login ,
411 tax_line_rec.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
412 tax_line_rec.precedence_7,
413 tax_line_rec.precedence_8,
414 tax_line_rec.precedence_9,
415 tax_line_rec.precedence_10
416 );
417 IF tax_line_rec.tax_type <> 'TDS'
418 THEN
419 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
420 END IF;
421 END LOOP;
422 UPDATE JAI_OM_OE_RMA_LINES
423 SET tax_amount = NVL(tax_amount, 0) + v_tax_total
424 WHERE rma_line_id = p_line_id;
425 END IF;
426 END IF;
427
428 END rma_insert;
429 -----------------------------------------------------------------------------------------------
430 PROCEDURE check_dff IS
431 BEGIN
432 /*
433 IF ( (p_new_return_context IS NOT NULL) OR (p_new_return_context <> 'LEGACY') ) THEN --2001/05/08 Anuradha Parthasarathy
434 */
435 /*
436 || Added by aiyer for the bug 5401180,
437 || Removed check for LEGACY as Dff context LEGACY has been removed
438 */
439 IF (p_new_return_context IS NOT NULL) THEN
440 -- legacy condition added by Aparajita on 31-may-2002 for bug 2381492
441 IF pn_delivery_detail_id IS NULL THEN
442 RAISE_APPLICATION_ERROR(-20401,'Delivery Detail id IS NOT entered');
443 END IF;
444
445 IF p_new_reference_line_id IS NOT NULL THEN
446
447 FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
448 FROM wsh_delivery_details wdd
449 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
450 AND (wdd.inventory_item_id IN
451 (SELECT inventory_item_id
452 FROM mtl_system_items
453 WHERE base_item_id = p_inventory_item_id
454 AND bom_item_type = 4) OR
455 wdd.inventory_item_id = p_inventory_item_id)
456 AND wdd.shipped_quantity IS NOT NULL)
457 LOOP
458 v_conf := conf_rec.pic;
459 END LOOP;
460
461 ELSIF p_new_ref_customer_trx_line_id IS NOT NULL THEN
462
463 FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
464 FROM wsh_delivery_details wdd,
465 ra_customer_trx_lines_all rctla
466 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
467 AND rctla.customer_trx_line_id = p_new_ref_customer_trx_line_id
468 AND rctla.inventory_item_id = p_inventory_item_id
469 AND wdd.delivery_detail_id = TO_NUMBER(rctla.interface_line_attribute3)
470 AND (wdd.inventory_item_id IN
471 (SELECT inventory_item_id
472 FROM mtl_system_items
473 WHERE base_item_id = p_inventory_item_id
474 AND bom_item_type = 4) OR
475 wdd.inventory_item_id = p_inventory_item_id)
476 AND wdd.shipped_quantity IS NOT NULL)
477 LOOP
478 v_conf := conf_rec.pic;
479 END LOOP;
480
481 END IF; -- p_new_reference_line_id
482
483 IF v_conf IS NULL THEN
484 RAISE_APPLICATION_ERROR(-20401, 'Delivery detail id IN the DFF IS NOT matching WITH the related delivery detail id FOR the entered ORDER NUMBER');
485 END IF;
486
487 FOR hr_rec IN (SELECT excise_return_days,
488 sales_return_days,
489 vat_return_days , -- added, Harshita for bug#4245062
490 nvl(manufacturing,'N') manufacturing, --Added Manufacturing and Trading by Nagaraj.s for bug3113027
491 nvl(trading,'N') trading
492 FROM JAI_CMN_INVENTORY_ORGS
493 WHERE organization_id = p_warehouse_id
494 AND location_id = 0)
495 LOOP
496 v_excise_return_days := hr_rec.excise_return_days;
497 v_sales_return_days := hr_rec.sales_return_days;
498 v_vat_return_days := hr_rec.vat_return_days ; -- added, Harshita for bug#4245062
499 --Added Manufacturing and Trading by Nagaraj.s for bug3113027
500 v_manufacturing := hr_rec.manufacturing;
501 v_trading := hr_rec.trading;
502
503 END LOOP;
504
505 FOR date_rec IN (SELECT ordered_date
506 FROM oe_order_headers_all
507 WHERE header_id = p_header_id)
508 LOOP
509 v_date_ordered := date_rec.ordered_date;
510 END LOOP;
511 --2001/07/03 Anuradha Parthasarathy
512 ---modified the IF condition for bug#7316234
513 IF NVL(pv_allow_excise_flag, 'Y') = 'Y' AND
514 (v_excise_return_days IS NULL
515 OR
516 (v_date_ordered - v_date_confirmed) <= v_excise_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
517 THEN
518 v_excise_flag := 'Y';
519 ELSE
520 v_excise_flag := 'N';
521 END IF;
522
523 ---modified the IF condition for bug#7316234
524 IF NVL(pv_allow_sales_flag, 'Y') = 'Y' AND
525 (v_sales_return_days IS NULL
526 OR
527 (v_date_ordered - v_date_confirmed) <= v_sales_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
528 THEN
529 v_sales_flag := 'Y';
530 ELSE
531 v_sales_flag := 'N';
532 END IF;
533
534 -- added, Harshita for bug#4245062
535 ---modified the IF condition for bug#7316234
536 IF (v_vat_return_days IS NULL
537 OR
538 (v_date_ordered - v_date_confirmed) <= v_vat_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
539 THEN
540 v_vat_flag := 'Y';
541 ELSE
542 v_vat_flag := 'N';
543 END IF;
544 -- ended, Harshita for bug#4245062
545
546
547 --2001/07/03 Anuradha Parthasarathy
548
549
550 FOR pick_rec IN (SELECT SUM(wdd.shipped_quantity) qty
551 FROM wsh_delivery_details wdd
552 WHERE wdd.delivery_detail_id = v_delivery_detail_id
553 AND wdd.inventory_item_id = p_inventory_item_id)
554 LOOP
555 v_shipped_quantity := pick_rec.qty;
556 END LOOP;
557
558 IF v_shipped_quantity < v_ord_inv_quantity THEN
559 RAISE_APPLICATION_ERROR(-20401, 'RMA quantity can NOT be more than shipped quantity');
560 END IF;
561
562 END IF;
563
564 END check_dff;
565
566 -----------------------------------------------------------------------------------------------
567 PROCEDURE delete_data IS
568 BEGIN
569
570 DELETE FROM JAI_OM_OE_RMA_LINES
571 WHERE rma_line_id = p_line_id;
572 DELETE FROM JAI_OM_OE_RMA_TAXES
573 WHERE rma_line_id = p_line_id;
574 END delete_data;
575
576 BEGIN
577 v_rma_quantity_uom := p_uom; --Ramananda for File.Sql.35
578
579 ----------------------------- For picking order category from oe_order_headers_all ------------------
580 IF p_header_id IS NOT NULL
581 THEN
582 FOR order_rec IN(SELECT order_category_code,
583 order_number,
584 ROWID
585 FROM oe_order_headers_all
586 WHERE header_id = P_header_id)
587 LOOP
588 v_category := order_rec.order_category_code;
589 v_order_number := order_rec.order_number;
590 /* v_rowid := order_rec.ROWID;*/
591 END LOOP;
592 END IF;
593 -------------------------------- For updating JAI_CMN_LOCATORS_T -----------------------------------
594 -- Start of bug #3306419
595 /*
596 The following if condition has been modified by aiyer for the bug #3306419
597 Added the clause p_line_category_code = 'RETURN' so that this piece of code would always
598 execute in case of an RMA irrespective of how the return order has been created.
599 */
600
601 IF NVL(p_source_document_type_id,0) <> 2 OR
602 p_line_category_code = 'RETURN'
603 THEN
604 -- End OF Bug #3306419
605
606 IF (v_category IS NOT NULL AND v_category = 'RETURN')
607 OR (p_new_reference_line_id IS NOT NULL)
608 THEN
609
610
611 v_chk_form := NVL(v_chk_form, 'JAINRCRT');
612
613 --- For picking shipped quantity, tax amount and tax category from JAI_OM_WSH_LINES_ALL ---
614
615 FOR pick_lrec IN (SELECT wdd.delivery_detail_id,
616 wnd.confirm_date
617 FROM wsh_delivery_details wdd,
618 wsh_delivery_assignments wda,
619 wsh_new_deliveries wnd
620 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
621 AND wda.delivery_detail_id = wdd.delivery_detail_id
622 AND wnd.delivery_id = wda.delivery_id)
623 LOOP
624 v_delivery_detail_id := pick_lrec.delivery_detail_id;
625 v_date_confirmed := pick_lrec.confirm_date;
626 END LOOP;
627
628 OPEN Localized_Order_Cur(v_delivery_detail_id);
629 FETCH Localized_Order_Cur INTO v_exist_flag;
630 CLOSE Localized_Order_Cur;
631
632 IF p_item_type_code = 'STANDARD' AND
633 p_serviced_quantity IS NULL AND
634 p_new_reference_line_id IS NOT NULL AND
635 v_chk_form IS NULL AND
636 NVL(v_exist_Flag,0) <> 1
637 THEN
638 RETURN;
639 END IF;
640
641 if UPDATING then
642
643 v_old_quantity := p_old_ordered_quantity - NVL(p_old_cancelled_quantity, 0);
644 v_new_quantity := p_new_ordered_quantity - NVL(p_new_cancelled_quantity, 0);
645 IF (
646 p_old_return_context <> p_new_return_context
647 OR (NVL(p_old_selling_price,0) <> NVL(P_new_selling_price,0)) -- added by sriram
648 OR NVL(p_old_reference_line_id, -99) <> NVL(p_new_reference_line_id, -99)
649 OR NVL(p_new_ref_customer_trx_line_id, -99) <> NVL(p_old_ref_customer_trx_line_id, -99)
650 OR v_old_quantity <> v_new_quantity
651 )
652 AND
653 (
654 v_chk_form IS NOT NULL
655 OR (
656 p_item_type_code = 'STANDARD' AND
657 p_serviced_quantity IS NULL AND
658 p_new_reference_line_id IS NOT NULL AND
659 v_chk_form IS NULL
660 )
661 )
662 THEN
663
664 check_dff;
665 delete_data;
666 rma_insert;
667 ELSIF p_old_cancelled_quantity <> p_new_cancelled_quantity
668 THEN
669
670 FOR rma_rec IN (SELECT rma_line_id
671 FROM JAI_OM_OE_RMA_LINES
672 WHERE rma_line_id = p_line_id)
673 LOOP
674 v_test_id := rma_rec.rma_line_id;
675 END LOOP;
676 IF v_test_id IS NOT NULL
677 THEN
678
679 delete_data;
680 rma_insert;
681 END IF;
682 END IF;
683
684 elsif
685 inserting
686 and (
687 v_chk_form IS NOT NULL
688 OR (
689 p_item_type_code = 'STANDARD' AND
690 p_serviced_quantity IS NULL AND
691 p_new_reference_line_id IS NOT NULL
692 )
693 )
694 then
695
696 check_dff;
697 rma_insert;
698
699 end if;
700
701 end if;
702
703 end if;
704
705 EXCEPTION
706 WHEN OTHERS THEN
707 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
708 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
709 app_exception.raise_exception;
710 END default_taxes_onto_line;
711
712
713 FUNCTION cal_excise_duty
714 ( p_rma_line_id IN NUMBER,
715 p_transaction_quantity IN NUMBER
716 ) RETURN NUMBER
717 IS
718
719 v_rma_camount jai_om_oe_rma_lines.tax_amount % type;
720 v_basic_ed jai_cmn_rg_23ac_ii_trxs.cr_basic_ed % type;
721 v_additional_ed jai_cmn_rg_23ac_ii_trxs.cr_additional_ed % type;
722 v_other_ed jai_cmn_rg_23ac_ii_trxs.cr_other_ed % type;
723 v_excise number;
724 ln_rma_amt number ;
725
726
727 CURSOR c_get_quantity(p_rma_line_id IN NUMBER) IS
728 SELECT
729 NVL(quantity, 0) quantity
730 FROM JAI_OM_OE_RMA_LINES
731 WHERE rma_line_id = p_rma_line_id ;
732
733 v_rma_quantity jai_om_oe_rma_lines.quantity % type;
734
735 BEGIN
736
737 v_basic_ed := 0 ;
738 v_additional_ed := 0 ;
739 v_other_ed := 0 ;
740 v_excise := 0 ;
741
742
743 OPEN c_get_quantity(p_rma_line_id) ;
744 FETCH c_get_quantity INTO v_rma_quantity ;
745 CLOSE c_get_quantity ;
746
747 For tax_line_rec IN (SELECT rtl.tax_line_no,
748 rtl.precedence_1,
749 rtl.precedence_2,
750 rtl.precedence_3,
751 rtl.precedence_4,
752 rtl.precedence_5,
753 rtl.tax_id,
754 rtl.tax_rate,
755 rtl.qty_rate,
756 rtl.uom,
757 rtl.tax_amount,
758 jtc.tax_type,
759 NVL(jtc.mod_cr_percentage, 0) modcp,
760 NVL(jtc.rounding_factor, 0) rounding_factor ,
761 rtl.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
762 rtl.precedence_7,
763 rtl.precedence_8,
764 rtl.precedence_9,
765 rtl.precedence_10
766 FROM JAI_OM_OE_RMA_TAXES rtl,
767 JAI_CMN_TAXES_ALL jtc
768 WHERE rtl.rma_line_id = p_rma_line_id
769 AND jtc.tax_id = rtl.tax_id)
770 LOOP
771 ln_rma_amt := 0 ;
772 v_rma_camount := 0 ;
773
774 IF v_rma_quantity <> 0
775 THEN
776 v_rma_camount := ROUND(( tax_line_rec.tax_amount * p_transaction_quantity / v_rma_quantity ), tax_line_rec.rounding_factor);
777 ln_rma_amt := v_rma_camount * NVL(tax_line_rec.modcp, 0) / 100 ;
778 END IF;
779
780 IF tax_line_rec.tax_type = 'Excise'
781 THEN
782 v_basic_ed := NVL(v_basic_ed, 0) + ln_rma_amt;
783
784 ELSIF tax_line_rec.tax_type IN ('Addl. Excise', 'CVD' )
785 THEN
786 v_additional_ed := NVL(v_additional_ed, 0) + ln_rma_amt;
787
788 ELSIF tax_line_rec.tax_type = 'Other Excise'
789 THEN
790 v_other_ed := NVL(v_other_ed, 0) + ln_rma_amt;
791 END IF;
792 END LOOP ;
793
794 v_excise := NVL(v_basic_ed, 0) + NVL(v_additional_ed, 0) + NVL(v_other_ed, 0);
795
796 RETURN NVL(v_excise,0) ;
797
798 END ;
799
800
801 END jai_om_rma_pkg;