[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.4 2010/05/10 09:59:34 haoyang 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 -- added by Allen Yang for bug 9666476 28-apr-2010, begin
78 lv_shippable_flag VARCHAR2(1);
79
80 CURSOR c_get_shippable_flag(c_inv_item_id NUMBER, c_organization_id NUMBER)
81 IS
82 SELECT SHIPPABLE_ITEM_FLAG
83 FROM MTL_SYSTEM_ITEMS
84 WHERE INVENTORY_ITEM_ID = c_inv_item_id
85 AND ORGANIZATION_ID = c_organization_id;
86 -- added by Allen Yang for bug 9666476 28-apr-2010, end
87
88 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
89 --CURSOR Localized_Order_Cur(c_delivery_detail_id NUMBER) IS
90 -- SELECT 1
91 -- FROM JAI_OM_WSH_LINES_ALL
92 -- WHERE delivery_detail_id = c_delivery_detail_id; /* Modified by Ramananda for removal of SQL LITERALs :bug#4428980*/
93 CURSOR Localized_Order_Cur(c_delivery_detail_id NUMBER, c_order_line_id NUMBER) IS
94 SELECT 1
95 FROM JAI_OM_WSH_LINES_ALL
96 WHERE delivery_detail_id = c_delivery_detail_id
97 OR (SHIPPABLE_FLAG = 'N' AND ORDER_LINE_ID = c_order_line_id);
98 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
99
100 -- cbabu for Bug#2523313, Start
101 CURSOR requested_qty_uom_cur(p_delivery_detail_id NUMBER) IS
102 SELECT requested_quantity_uom FROM wsh_delivery_details
103 WHERE delivery_detail_id = p_delivery_detail_id;
104
105 v_conversion_rate NUMBER := 0;
106 v_requested_quantity_uom VARCHAR2(3);
107 v_rma_quantity_uom VARCHAR2(3); -- := p_uom; --Ramananda for File.Sql.35
108
109 -- cbabu for Bug#2523313, End
110
111
112 -- added by sriram - bug # 2798596
113
114 cursor c_sales_order_cur is
115 select quantity ,service_type_code /* added by ssawant for bug 5879769 */
116 from JAI_OM_OE_SO_LINES
117 where line_id = p_new_reference_line_id;
118
119 Cursor C_SO_TAX_AMOUNT (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type) is
120 select tax_amount
121 from JAI_OM_OE_SO_TAXES
122 where line_id = p_new_reference_line_id
123 and tax_id = p_tax_id;
124
125 v_so_tax_amount Number;
126 v_orig_ord_qty Number;
127
128
129 -- additions by sriram - bug # 2798596 -- ends here
130
131 -- added, Harshita for bug#4245062
132 Cursor c_ordered_date_cur(v_header_id number) is
133 select
134 ordered_date
135 from
136 oe_order_headers_all
137 where
138 header_id = v_header_id;
139
140 -- following cursor is used to get the ship confirm date based on the delivery detail id
141
142 cursor c_confirmed_date (p_delivery_Detail_id Number) is
143 select
144 confirm_date
145 FROM
146 wsh_delivery_details wdd,
147 wsh_delivery_assignments wda,
148 wsh_new_deliveries wnd
149 WHERE
150 wdd.delivery_detail_id = (p_delivery_Detail_id)
151 AND
152 wda.delivery_detail_id = wdd.delivery_detail_id
153 AND
154 wnd.delivery_id = wda.delivery_id;
155
156 Cursor c_hr_organizations_cur( v_organization_id number ) is
157 SELECT vat_return_days
158 FROM JAI_CMN_INVENTORY_ORGS
159 WHERE organization_id = v_organization_id
160 AND location_id = 0;
161
162 v_ordered_date date ;
163 v_confirm_date date ;
164
165
166 -- end, Harshita for bug#4245062
167 /*bduvarag for the bug# 5256498 start*/
168 cursor c_check_Vat_type_Tax_exists (cp_tax_type VARCHAR2) IS
169 SELECT 1
170 FROM jai_regime_tax_types_v
171 WHERE regime_code = jai_constants.vat_regime
172 AND tax_type = cp_tax_type;
173
174 lv_check_vat_type_exists VARCHAR2(1);
175
176 /*bduvarag for the bug#5256498 end*/
177 /* Added for DFF Elimination by Ramananda */
178
179 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_om_rma_pkg.default_taxes_onto_line';
180
181 -------------------------------------------------------------------------------------------------
182 PROCEDURE rma_insert IS
183 BEGIN
184
185 IF v_delivery_detail_id IS NOT NULL
186 THEN
187 v_ord_inv_quantity := p_new_ordered_quantity;
188 IF v_ord_inv_quantity <> 0
189 THEN
190 FOR pick_rec IN (SELECT quantity,
191 tax_category_id
192 FROM JAI_OM_WSH_LINES_ALL
193 WHERE delivery_detail_id = v_delivery_detail_id)
194 LOOP
195 v_quantity := pick_rec.quantity;
196 v_tax_category_id := pick_rec.tax_category_id;
197 END LOOP;
198
199 IF pn_excise_duty_per_unit IS NOT NULL AND
200 pn_excise_duty_rate IS NOT NULL
201 THEN
202 v_excise_duty_rate := pn_excise_duty_rate;
203 v_rate_per_unit := pn_excise_duty_per_unit;
204 ELSE
205 FOR duty_rec IN (SELECT rgd.excise_duty_rate,
206 rgd.rate_per_unit,
207 rgd.quantity_received
208 FROM JAI_CMN_RG_23D_TRXS rgd,
209 JAI_CMN_MATCH_RECEIPTS rm
210 WHERE rm.ref_line_id = v_delivery_detail_id
211 AND rgd.register_id = rm.receipt_id)
212 LOOP
213 v_excise_total := NVL(v_excise_total, 0) + NVL(duty_rec.excise_duty_rate, 0) *
214 NVL(duty_rec.quantity_received, 0);
215 v_rate_total := NVL(v_rate_total, 0) + NVL(duty_rec.rate_per_unit, 0) *
216 NVL(duty_rec.quantity_received, 0);
217 v_qty_total := NVL(v_qty_total, 0) + NVL(duty_rec.quantity_received, 0);
218 END LOOP;
219 IF NVL(v_excise_total, 0) <> 0 AND
220 NVL(v_qty_total, 0) <> 0
221 THEN
222 v_excise_duty_rate := ROUND((v_excise_total / v_qty_total), 2);
223 END IF;
224 IF NVL(v_rate_total, 0) <> 0 AND
225 NVL(v_qty_total, 0) <> 0
226 THEN
227 v_rate_per_unit := ROUND((v_rate_total / v_qty_total), 2);
228 END IF;
229 END IF;
230 ---------------------- For inserting record in JAI_OM_OE_RMA_LINES --------------------
231
232
233 INSERT INTO JAI_OM_OE_RMA_LINES
234 (rma_line_id,
235 rma_line_number,
236 rma_header_id,
237 rma_number,
238 delivery_detail_id,
239 uom,
240 selling_price,
241 quantity,
242 tax_category_id,
243 tax_amount,
244 inventory_item_id,
245 received_flag,
246 assessable_value,
247 excise_duty_rate,
248 rate_per_unit,
249 creation_date,
250 created_by,
251 last_update_date,
252 last_updated_by,
253 last_update_login,
254 /* Added for DFF Elimination by Ramananda */
255 allow_excise_credit_flag,
256 allow_sales_credit_flag,
257 service_type_code /* added by ssawant for bug 5879769 */
258 )
259 VALUES (p_line_id,
260 p_line_number,
261 p_header_id,
262 v_order_number,
263 v_delivery_detail_id,
264 P_uom,
265 p_new_selling_price,
266 v_ord_inv_quantity,
267 v_tax_category_id,
268 NULL,
269 p_inventory_item_id,
270 NULL, --received_flag
271 NULL, --assessable value
272 v_excise_duty_rate,
273 v_rate_per_unit,
274 p_creation_date,
275 p_created_by,
276 p_last_update_date,
277 p_last_updated_by,
278 p_last_update_login,
279 pv_allow_excise_flag,
280 pv_allow_sales_flag,
281 v_service_type_code /* added by ssawant for bug 5879769 */
282 );
283
284 IF v_quantity <> 0
285 THEN
286 -- cbabu for Bug#2523313, Start
287 OPEN requested_qty_uom_cur(v_delivery_detail_id);
288 FETCH requested_qty_uom_cur INTO v_requested_quantity_uom;
289 CLOSE requested_qty_uom_cur;
290
291 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
292 v_rma_quantity_uom,
293 p_inventory_item_id,
294 v_conversion_rate);
295 IF NVL(v_conversion_rate, 0) <= 0 THEN
296 INV_CONVERT.inv_um_conversion(v_requested_quantity_uom,
297 v_rma_quantity_uom,
298 0,
299 v_conversion_rate);
300 IF NVL(v_conversion_rate, 0) <= 0 THEN
301 v_conversion_rate := 0;
302 END IF;
303 END IF;
304 -- cbabu for Bug#2523313, End
305 v_cor_amount := (v_ord_inv_quantity / v_quantity)*(1/v_conversion_rate); -- cbabu for Bug#2523313
306
307 END IF;
308 FOR tax_line_rec IN (SELECT tax_line_no,
309 precedence_1,
310 precedence_2,
311 precedence_3,
312 precedence_4,
313 precedence_5,
314 sptl.tax_id,
315 sptl.tax_rate,
316 sptl.qty_rate,
317 uom,
318 sptl.tax_amount,
319 nvl(jtc.rounding_factor,0) rounding_factor,/*Bug 5989740 bduvarag*//*bduvarag for the bug#6071813*/
320 -- interfaced_flag,
321 base_tax_amount,
322 func_tax_amount,
323 jtc.tax_type ,
324 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
325 precedence_7,
326 precedence_8,
327 precedence_9,
328 precedence_10
329 FROM JAI_OM_WSH_LINE_TAXES sptl,
330 JAI_CMN_TAXES_ALL jtc
331 WHERE delivery_detail_id = v_delivery_detail_id
332 AND jtc.tax_id = sptl.tax_id)
333 LOOP
334
335
336 IF tax_line_rec.tax_type IN ('Excise', 'Addl. Excise', 'Other Excise', 'TDS', 'CVD')
337 THEN
338 v_round_tax := ROUND((v_cor_amount * tax_line_rec.tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
339 v_round_base := ROUND((v_cor_amount * tax_line_rec.base_tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
340 v_round_func := ROUND((v_cor_amount * tax_line_rec.func_tax_amount),tax_line_rec.rounding_factor);/*Bug 5989740 bduvarag*/
341 ELSE
342 v_round_tax := ROUND((v_cor_amount * tax_line_rec.tax_amount), 2);
343 v_round_base := ROUND((v_cor_amount * tax_line_rec.base_tax_amount), 2);
344 v_round_func := ROUND((v_cor_amount * tax_line_rec.func_tax_amount), 2);
345 END IF;
346
347
348 /*
349 code segment added by sriram - bug # 2798596
350 */
351
352 open c_sales_order_cur;
353 fetch c_sales_order_cur into v_orig_ord_qty,v_service_type_code;/* added by ssawant for bug 5879769 */
354 close c_sales_order_cur;
355
356 /*
357 code segment added by sriram - ends here - bug # 2798596
358 */
359 /*bduvarag for the bug#5256498 start*/
360 lv_check_vat_type_exists := NULL;
361
362 OPEN c_check_Vat_type_Tax_exists (tax_line_rec.tax_type);
363 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
364 CLOSE c_check_Vat_type_Tax_exists;
365 /*bduvarag for the bug#5256498 end*/
366
367 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*/
368 v_excise_flag = 'N') OR
369 (tax_line_rec.tax_type IN ('Sales Tax', 'CST') AND
370 v_sales_flag = 'N') OR -- added, Harshita for bug#4245062
371 /*bduvarag for the bug#5256498 start*/
372 /* (tax_line_rec.tax_type IN ('TURNOVER TAX', 'VAT', 'ENTRY TAX', 'Octrai', 'PURCHASE TAX') AND
373 v_vat_flag = 'N') */
374 ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
375 /*bduvarag for the bug#5256498 end*/
376 THEN
377 v_round_tax := 0;
378 v_round_base := 0;
379 v_round_func := 0;
380 END IF;
381
382
383 INSERT INTO JAI_OM_OE_RMA_TAXES
384 (rma_line_id,
385 delivery_detail_id,
386 tax_line_no,
387 precedence_1,
388 precedence_2,
389 precedence_3,
390 precedence_4,
391 precedence_5,
392 tax_id,
393 tax_rate,
394 qty_rate,
395 uom,
396 tax_amount,
397 base_tax_amount,
398 func_tax_amount,
399 creation_date,
400 created_by,
401 last_update_date,
402 last_updated_by,
403 last_update_login ,
404 precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
405 precedence_7,
406 precedence_8,
407 precedence_9,
408 precedence_10)
409 VALUES (p_line_id,
410 v_delivery_detail_id,
411 tax_line_rec.tax_line_no,
412 tax_line_rec.precedence_1,
413 tax_line_rec.precedence_2,
414 tax_line_rec.precedence_3,
415 tax_line_rec.precedence_4,
416 tax_line_rec.precedence_5,
417 tax_line_rec.tax_id,
418 tax_line_rec.tax_rate,
419 tax_line_rec.qty_rate,
420 tax_line_rec.uom,
421 v_round_tax,
422 v_round_base,
423 v_round_func,
424 p_creation_date,
425 p_created_by,
426 p_last_update_date,
427 p_last_updated_by,
428 p_last_update_login ,
429 tax_line_rec.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
430 tax_line_rec.precedence_7,
431 tax_line_rec.precedence_8,
432 tax_line_rec.precedence_9,
433 tax_line_rec.precedence_10
434 );
435 IF tax_line_rec.tax_type <> 'TDS'
436 THEN
437 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
438 END IF;
439 END LOOP;
440 UPDATE JAI_OM_OE_RMA_LINES
441 SET tax_amount = NVL(tax_amount, 0) + v_tax_total
442 WHERE rma_line_id = p_line_id;
443 END IF;
444
445 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
446 --END IF;
447 -- need to do RMA insert for non-shippable lines (whose delivery_detail_id IS NULL)
448 ELSIF NVL(lv_shippable_flag, 'Y') = 'N'
449 THEN
450 -- added by Allen Yang for bug 9691880 10-May-2010, begin
451 FOR hr_rec IN (SELECT vat_return_days
452 FROM JAI_CMN_INVENTORY_ORGS
453 WHERE organization_id = p_warehouse_id
454 AND location_id = 0)
455 LOOP
456 v_vat_return_days := hr_rec.vat_return_days ;
457 END LOOP; -- hr_rec IN ......
458
459 FOR date_rec IN (SELECT ordered_date
460 FROM oe_order_headers_all
461 WHERE header_id = p_header_id)
462 LOOP
463 v_date_ordered := date_rec.ordered_date;
464 END LOOP; -- date_rec IN ......
465
466 -- added by Allen Yang for bug 9691880 10-May-2010, end
467
468 IF p_new_ordered_quantity <> 0
469 THEN
470 FOR pick_rec IN (SELECT tax_category_id
471 -- added by Allen Yang for bug 9691880 10-May-2010, begin
472 ,creation_date
473 -- added by Allen Yang for bug 9691880 10-May-2010, end
474 FROM JAI_OM_WSH_LINES_ALL
475 WHERE shippable_flag = 'N'
476 AND order_line_id = p_new_reference_line_id)
477 LOOP
478 v_tax_category_id := pick_rec.tax_category_id;
479 -- added by Allen Yang for bug 9691880 10-May-2010, begin
480 v_date_confirmed := pick_rec.creation_date;
481 -- added by Allen Yang for bug 9691880 10-May-2010, end
482 END LOOP; -- pick_rec IN ......
483
484 -- added by Allen Yang for bug 9691880 10-May-2010, begin
485 IF (v_vat_return_days IS NULL
486 OR
487 (v_date_ordered - v_date_confirmed) <= v_vat_return_days)
488 THEN
489 v_vat_flag := 'Y';
490 ELSE
491 v_vat_flag := 'N';
492 END IF; -- v_vat_return_days IS NULL OR ......
493 -- added by Allen Yang for bug 9691880 10-May-2010, end
494
495 ---------------------- For inserting record in JAI_OM_OE_RMA_LINES --------------------
496 INSERT INTO JAI_OM_OE_RMA_LINES
497 (rma_line_id,
498 rma_line_number,
499 rma_header_id,
500 rma_number,
501 delivery_detail_id,
502 uom,
503 selling_price,
504 quantity,
505 tax_category_id,
506 tax_amount,
507 inventory_item_id,
508 received_flag,
509 assessable_value,
510 excise_duty_rate,
511 rate_per_unit,
512 creation_date,
513 created_by,
514 last_update_date,
515 last_updated_by,
516 last_update_login,
517 allow_excise_credit_flag,
518 allow_sales_credit_flag,
519 service_type_code
520 )
521 VALUES (p_line_id,
522 p_line_number,
523 p_header_id,
524 v_order_number,
525 NULL, -- delivery_detail_id
526 P_uom,
527 p_new_selling_price,
528 p_new_ordered_quantity,
529 v_tax_category_id,
530 NULL, -- tax_amount
531 p_inventory_item_id,
532 NULL, --received_flag
533 NULL, --assessable value
534 NULL, -- excise_duty_rate
535 NULL, -- rate_per_unit,
536 p_creation_date,
537 p_created_by,
538 p_last_update_date,
539 p_last_updated_by,
540 p_last_update_login,
541 pv_allow_excise_flag,
542 pv_allow_sales_flag,
543 v_service_type_code
544 );
545
546 FOR tax_line_rec IN (SELECT tax_line_no,
547 precedence_1,
548 precedence_2,
549 precedence_3,
550 precedence_4,
551 precedence_5,
552 sptl.tax_id,
553 sptl.tax_rate,
554 sptl.qty_rate,
555 uom,
556 sptl.tax_amount,
557 nvl(jtc.rounding_factor,0) rounding_factor,
558 base_tax_amount,
559 func_tax_amount,
560 jtc.tax_type ,
561 precedence_6,
562 precedence_7,
563 precedence_8,
564 precedence_9,
565 precedence_10
566 FROM JAI_OM_WSH_LINE_TAXES sptl,
567 JAI_CMN_TAXES_ALL jtc
568 WHERE order_line_id = p_new_reference_line_id
569 AND jtc.tax_id = sptl.tax_id)
570 LOOP
571 -- added by Allen Yang for bug 9691880 10-May-2010, begin
572 lv_check_vat_type_exists := NULL;
573
574 OPEN c_check_Vat_type_Tax_exists (tax_line_rec.tax_type);
575 FETCH c_check_Vat_type_Tax_exists INTO lv_check_vat_type_exists;
576 CLOSE c_check_Vat_type_Tax_exists;
577
578 IF ( lv_check_vat_type_exists = 1 AND v_vat_flag = 'N')
579 THEN
580 v_round_tax := 0;
581 v_round_base := 0;
582 v_round_func := 0;
583 ELSE
584 v_round_tax := tax_line_rec.tax_amount;
585 v_round_base := tax_line_rec.base_tax_amount;
586 v_round_func := tax_line_rec.func_tax_amount;
587 END IF; -- lv_check_vat_type_exists = 1 AND v_vat_flag = 'N'
588 -- added by Allen Yang for bug 9691880 10-May-2010, end
589
590 open c_sales_order_cur;
591 fetch c_sales_order_cur into v_orig_ord_qty,v_service_type_code;
592 close c_sales_order_cur;
593
594 INSERT INTO JAI_OM_OE_RMA_TAXES
595 (rma_line_id,
596 delivery_detail_id,
597 tax_line_no,
598 precedence_1,
599 precedence_2,
600 precedence_3,
601 precedence_4,
602 precedence_5,
603 tax_id,
604 tax_rate,
605 qty_rate,
606 uom,
607 tax_amount,
608 base_tax_amount,
609 func_tax_amount,
610 creation_date,
611 created_by,
612 last_update_date,
613 last_updated_by,
614 last_update_login ,
615 precedence_6,
616 precedence_7,
617 precedence_8,
618 precedence_9,
619 precedence_10)
620 VALUES (p_line_id,
621 NULL, -- delivery_detail_id
622 tax_line_rec.tax_line_no,
623 tax_line_rec.precedence_1,
624 tax_line_rec.precedence_2,
625 tax_line_rec.precedence_3,
626 tax_line_rec.precedence_4,
627 tax_line_rec.precedence_5,
628 tax_line_rec.tax_id,
629 tax_line_rec.tax_rate,
630 tax_line_rec.qty_rate,
631 tax_line_rec.uom,
632 -- modified by Allen Yang for bug 9691880 10-May-2010, begin
633 v_round_tax, --tax_line_rec.tax_amount,
634 v_round_base, --tax_line_rec.base_tax_amount,
635 v_round_func, --tax_line_rec.func_tax_amount,
636 -- modified by Allen Yang for bug 9691880 10-May-2010, end
637 p_creation_date,
638 p_created_by,
639 p_last_update_date,
640 p_last_updated_by,
641 p_last_update_login ,
642 tax_line_rec.precedence_6,
643 tax_line_rec.precedence_7,
644 tax_line_rec.precedence_8,
645 tax_line_rec.precedence_9,
646 tax_line_rec.precedence_10
647 );
648 IF tax_line_rec.tax_type <> 'TDS'
649 THEN
650 -- modified by Allen Yang for bug 9691880 10-May-2010, begin
651 --v_tax_total := NVL(v_tax_total, 0) + tax_line_rec.tax_amount;
652 v_tax_total := NVL(v_tax_total, 0) + v_round_tax;
653 -- modified by Allen Yang for bug 9691880 10-May-2010, end
654 END IF; --tax_line_rec.tax_type <> 'TDS'
655 END LOOP; --tax_line_rec IN (SELECT tax_line_no ......
656
657 UPDATE JAI_OM_OE_RMA_LINES
658 SET tax_amount = NVL(tax_amount, 0) + v_tax_total
659 WHERE rma_line_id = p_line_id;
660 END IF; -- p_new_ordered_quantity <> 0
661 END IF; -- v_delivery_detail_id IS NOT NULL
662 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
663
664 END rma_insert;
665 -----------------------------------------------------------------------------------------------
666 PROCEDURE check_dff IS
667 BEGIN
668 /*
669 IF ( (p_new_return_context IS NOT NULL) OR (p_new_return_context <> 'LEGACY') ) THEN --2001/05/08 Anuradha Parthasarathy
670 */
671 /*
672 || Added by aiyer for the bug 5401180,
673 || Removed check for LEGACY as Dff context LEGACY has been removed
674 */
675 IF (p_new_return_context IS NOT NULL) THEN
676 -- legacy condition added by Aparajita on 31-may-2002 for bug 2381492
677 IF pn_delivery_detail_id IS NULL THEN
678 RAISE_APPLICATION_ERROR(-20401,'Delivery Detail id IS NOT entered');
679 END IF;
680
681 IF p_new_reference_line_id IS NOT NULL THEN
682
683 FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
684 FROM wsh_delivery_details wdd
685 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
686 AND (wdd.inventory_item_id IN
687 (SELECT inventory_item_id
688 FROM mtl_system_items
689 WHERE base_item_id = p_inventory_item_id
690 AND bom_item_type = 4) OR
691 wdd.inventory_item_id = p_inventory_item_id)
692 AND wdd.shipped_quantity IS NOT NULL)
693 LOOP
694 v_conf := conf_rec.pic;
695 END LOOP;
696
697 ELSIF p_new_ref_customer_trx_line_id IS NOT NULL THEN
698
699 FOR conf_rec IN (SELECT NVL(wdd.delivery_detail_id, 0) pic
700 FROM wsh_delivery_details wdd,
701 ra_customer_trx_lines_all rctla
702 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
703 AND rctla.customer_trx_line_id = p_new_ref_customer_trx_line_id
704 AND rctla.inventory_item_id = p_inventory_item_id
705 AND wdd.delivery_detail_id = TO_NUMBER(rctla.interface_line_attribute3)
706 AND (wdd.inventory_item_id IN
707 (SELECT inventory_item_id
708 FROM mtl_system_items
709 WHERE base_item_id = p_inventory_item_id
710 AND bom_item_type = 4) OR
711 wdd.inventory_item_id = p_inventory_item_id)
712 AND wdd.shipped_quantity IS NOT NULL)
713 LOOP
714 v_conf := conf_rec.pic;
715 END LOOP;
716
717 END IF; -- p_new_reference_line_id
718
719 IF v_conf IS NULL THEN
720 RAISE_APPLICATION_ERROR(-20401, 'Delivery detail id IN the DFF IS NOT matching WITH the related delivery detail id FOR the entered ORDER NUMBER');
721 END IF;
722
723 FOR hr_rec IN (SELECT excise_return_days,
724 sales_return_days,
725 vat_return_days , -- added, Harshita for bug#4245062
726 nvl(manufacturing,'N') manufacturing, --Added Manufacturing and Trading by Nagaraj.s for bug3113027
727 nvl(trading,'N') trading
728 FROM JAI_CMN_INVENTORY_ORGS
729 WHERE organization_id = p_warehouse_id
730 AND location_id = 0)
731 LOOP
732 v_excise_return_days := hr_rec.excise_return_days;
733 v_sales_return_days := hr_rec.sales_return_days;
734 v_vat_return_days := hr_rec.vat_return_days ; -- added, Harshita for bug#4245062
735 --Added Manufacturing and Trading by Nagaraj.s for bug3113027
736 v_manufacturing := hr_rec.manufacturing;
737 v_trading := hr_rec.trading;
738
739 END LOOP;
740
741 FOR date_rec IN (SELECT ordered_date
742 FROM oe_order_headers_all
743 WHERE header_id = p_header_id)
744 LOOP
745 v_date_ordered := date_rec.ordered_date;
746 END LOOP;
747 --2001/07/03 Anuradha Parthasarathy
748 ---modified the IF condition for bug#7316234
749 IF NVL(pv_allow_excise_flag, 'Y') = 'Y' AND
750 (v_excise_return_days IS NULL
751 OR
752 (v_date_ordered - v_date_confirmed) <= v_excise_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
753 THEN
754 v_excise_flag := 'Y';
755 ELSE
756 v_excise_flag := 'N';
757 END IF;
758
759 ---modified the IF condition for bug#7316234
760 IF NVL(pv_allow_sales_flag, 'Y') = 'Y' AND
761 (v_sales_return_days IS NULL
762 OR
763 (v_date_ordered - v_date_confirmed) <= v_sales_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
764 THEN
765 v_sales_flag := 'Y';
766 ELSE
767 v_sales_flag := 'N';
768 END IF;
769
770 -- added, Harshita for bug#4245062
771 ---modified the IF condition for bug#7316234
772 IF (v_vat_return_days IS NULL
773 OR
774 (v_date_ordered - v_date_confirmed) <= v_vat_return_days) -- 0 replaced with 180 by sriram in the nvl comparison. - bug # 2993645
775 THEN
776 v_vat_flag := 'Y';
777 ELSE
778 v_vat_flag := 'N';
779 END IF;
780 -- ended, Harshita for bug#4245062
781
782
783 --2001/07/03 Anuradha Parthasarathy
784
785
786 FOR pick_rec IN (SELECT SUM(wdd.shipped_quantity) qty
787 FROM wsh_delivery_details wdd
788 WHERE wdd.delivery_detail_id = v_delivery_detail_id
789 AND wdd.inventory_item_id = p_inventory_item_id)
790 LOOP
791 v_shipped_quantity := pick_rec.qty;
792 END LOOP;
793
794 IF v_shipped_quantity < v_ord_inv_quantity THEN
795 RAISE_APPLICATION_ERROR(-20401, 'RMA quantity can NOT be more than shipped quantity');
796 END IF;
797
798 END IF;
799
800 END check_dff;
801
802 -----------------------------------------------------------------------------------------------
803 PROCEDURE delete_data IS
804 BEGIN
805
806 DELETE FROM JAI_OM_OE_RMA_LINES
807 WHERE rma_line_id = p_line_id;
808 DELETE FROM JAI_OM_OE_RMA_TAXES
809 WHERE rma_line_id = p_line_id;
810 END delete_data;
811
812 BEGIN
813 v_rma_quantity_uom := p_uom; --Ramananda for File.Sql.35
814
815 ----------------------------- For picking order category from oe_order_headers_all ------------------
816 IF p_header_id IS NOT NULL
817 THEN
818 FOR order_rec IN(SELECT order_category_code,
819 order_number,
820 ROWID
821 FROM oe_order_headers_all
822 WHERE header_id = P_header_id)
823 LOOP
824 v_category := order_rec.order_category_code;
825 v_order_number := order_rec.order_number;
826 /* v_rowid := order_rec.ROWID;*/
827 END LOOP;
828 END IF;
829 -------------------------------- For updating JAI_CMN_LOCATORS_T -----------------------------------
830 -- Start of bug #3306419
831 /*
832 The following if condition has been modified by aiyer for the bug #3306419
833 Added the clause p_line_category_code = 'RETURN' so that this piece of code would always
834 execute in case of an RMA irrespective of how the return order has been created.
835 */
836
837 IF NVL(p_source_document_type_id,0) <> 2 OR
838 p_line_category_code = 'RETURN'
839 THEN
840 -- End OF Bug #3306419
841
842 IF (v_category IS NOT NULL AND v_category = 'RETURN')
843 OR (p_new_reference_line_id IS NOT NULL)
844 THEN
845
846
847 v_chk_form := NVL(v_chk_form, 'JAINRCRT');
848
849 --- For picking shipped quantity, tax amount and tax category from JAI_OM_WSH_LINES_ALL ---
850
851 FOR pick_lrec IN (SELECT wdd.delivery_detail_id,
852 wnd.confirm_date
853 FROM wsh_delivery_details wdd,
854 wsh_delivery_assignments wda,
855 wsh_new_deliveries wnd
856 WHERE wdd.delivery_detail_id = TO_NUMBER(pn_delivery_detail_id)
857 AND wda.delivery_detail_id = wdd.delivery_detail_id
858 AND wnd.delivery_id = wda.delivery_id)
859 LOOP
860 v_delivery_detail_id := pick_lrec.delivery_detail_id;
861 v_date_confirmed := pick_lrec.confirm_date;
862 END LOOP;
863
864 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
865 --OPEN Localized_Order_Cur(v_delivery_detail_id);
866 OPEN Localized_Order_Cur(v_delivery_detail_id, p_new_reference_line_id);
867 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
868 FETCH Localized_Order_Cur INTO v_exist_flag;
869 CLOSE Localized_Order_Cur;
870
871 -- added by Allen Yang for bug 9666476 28-apr-2010, begin
872 OPEN c_get_shippable_flag(p_inventory_item_id, p_warehouse_id);
873 FETCH c_get_shippable_flag INTO lv_shippable_flag;
874 CLOSE c_get_shippable_flag;
875 -- added by Allen Yang for bug 9666476 28-apr-2010, end
876
877 IF p_item_type_code = 'STANDARD' AND
878 p_serviced_quantity IS NULL AND
879 p_new_reference_line_id IS NOT NULL AND
880 v_chk_form IS NULL AND
881 NVL(v_exist_Flag,0) <> 1
882 THEN
883 RETURN;
884 END IF;
885
886 if UPDATING then
887
888 v_old_quantity := p_old_ordered_quantity - NVL(p_old_cancelled_quantity, 0);
889 v_new_quantity := p_new_ordered_quantity - NVL(p_new_cancelled_quantity, 0);
890 IF (
891 p_old_return_context <> p_new_return_context
892 OR (NVL(p_old_selling_price,0) <> NVL(P_new_selling_price,0)) -- added by sriram
893 OR NVL(p_old_reference_line_id, -99) <> NVL(p_new_reference_line_id, -99)
894 OR NVL(p_new_ref_customer_trx_line_id, -99) <> NVL(p_old_ref_customer_trx_line_id, -99)
895 OR v_old_quantity <> v_new_quantity
896 )
897 AND
898 (
899 v_chk_form IS NOT NULL
900 OR (
901 p_item_type_code = 'STANDARD' AND
902 p_serviced_quantity IS NULL AND
903 p_new_reference_line_id IS NOT NULL AND
904 v_chk_form IS NULL
905 )
906 )
907 THEN
908 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
909 --check_dff;
910 IF NVL(lv_shippable_flag, 'Y') <> 'N'
911 THEN
912 check_dff;
913 END IF;
914 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
915 delete_data;
916 rma_insert;
917 ELSIF p_old_cancelled_quantity <> p_new_cancelled_quantity
918 THEN
919
920 FOR rma_rec IN (SELECT rma_line_id
921 FROM JAI_OM_OE_RMA_LINES
922 WHERE rma_line_id = p_line_id)
923 LOOP
924 v_test_id := rma_rec.rma_line_id;
925 END LOOP;
926 IF v_test_id IS NOT NULL
927 THEN
928
929 delete_data;
930 rma_insert;
931 END IF;
932 END IF;
933
934 elsif
935 inserting
936 and (
937 v_chk_form IS NOT NULL
938 OR (
939 p_item_type_code = 'STANDARD' AND
940 p_serviced_quantity IS NULL AND
941 p_new_reference_line_id IS NOT NULL
942 )
943 )
944 then
945 -- modified by Allen Yang for bug 9666476 28-apr-2010, begin
946 --check_dff;
947 IF NVL(lv_shippable_flag, 'Y') <> 'N'
948 THEN
949 check_dff;
950 END IF;
951 -- modified by Allen Yang for bug 9666476 28-apr-2010, end
952 rma_insert;
953
954 end if;
955
956 end if;
957
958 end if;
959
960 EXCEPTION
961 WHEN OTHERS THEN
962 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
963 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
964 app_exception.raise_exception;
965 END default_taxes_onto_line;
966
967
968 FUNCTION cal_excise_duty
969 ( p_rma_line_id IN NUMBER,
970 p_transaction_quantity IN NUMBER
971 ) RETURN NUMBER
972 IS
973
974 v_rma_camount jai_om_oe_rma_lines.tax_amount % type;
975 v_basic_ed jai_cmn_rg_23ac_ii_trxs.cr_basic_ed % type;
976 v_additional_ed jai_cmn_rg_23ac_ii_trxs.cr_additional_ed % type;
977 v_other_ed jai_cmn_rg_23ac_ii_trxs.cr_other_ed % type;
978 v_excise number;
979 ln_rma_amt number ;
980
981
982 CURSOR c_get_quantity(p_rma_line_id IN NUMBER) IS
983 SELECT
984 NVL(quantity, 0) quantity
985 FROM JAI_OM_OE_RMA_LINES
986 WHERE rma_line_id = p_rma_line_id ;
987
988 v_rma_quantity jai_om_oe_rma_lines.quantity % type;
989
990 BEGIN
991
992 v_basic_ed := 0 ;
993 v_additional_ed := 0 ;
994 v_other_ed := 0 ;
995 v_excise := 0 ;
996
997
998 OPEN c_get_quantity(p_rma_line_id) ;
999 FETCH c_get_quantity INTO v_rma_quantity ;
1000 CLOSE c_get_quantity ;
1001
1002 For tax_line_rec IN (SELECT rtl.tax_line_no,
1003 rtl.precedence_1,
1004 rtl.precedence_2,
1005 rtl.precedence_3,
1006 rtl.precedence_4,
1007 rtl.precedence_5,
1008 rtl.tax_id,
1009 rtl.tax_rate,
1010 rtl.qty_rate,
1011 rtl.uom,
1012 rtl.tax_amount,
1013 jtc.tax_type,
1014 NVL(jtc.mod_cr_percentage, 0) modcp,
1015 NVL(jtc.rounding_factor, 0) rounding_factor ,
1016 rtl.precedence_6, -- Date 31/10/2006 Bug 5228046 added by SACSETHI ( added column from Precedence 6 to 10 )
1017 rtl.precedence_7,
1018 rtl.precedence_8,
1019 rtl.precedence_9,
1020 rtl.precedence_10
1021 FROM JAI_OM_OE_RMA_TAXES rtl,
1022 JAI_CMN_TAXES_ALL jtc
1023 WHERE rtl.rma_line_id = p_rma_line_id
1024 AND jtc.tax_id = rtl.tax_id)
1025 LOOP
1026 ln_rma_amt := 0 ;
1027 v_rma_camount := 0 ;
1028
1029 IF v_rma_quantity <> 0
1030 THEN
1031 v_rma_camount := ROUND(( tax_line_rec.tax_amount * p_transaction_quantity / v_rma_quantity ), tax_line_rec.rounding_factor);
1032 ln_rma_amt := v_rma_camount * NVL(tax_line_rec.modcp, 0) / 100 ;
1033 END IF;
1034
1035 IF tax_line_rec.tax_type = 'Excise'
1036 THEN
1037 v_basic_ed := NVL(v_basic_ed, 0) + ln_rma_amt;
1038
1039 ELSIF tax_line_rec.tax_type IN ('Addl. Excise', 'CVD' )
1040 THEN
1041 v_additional_ed := NVL(v_additional_ed, 0) + ln_rma_amt;
1042
1043 ELSIF tax_line_rec.tax_type = 'Other Excise'
1044 THEN
1045 v_other_ed := NVL(v_other_ed, 0) + ln_rma_amt;
1046 END IF;
1047 END LOOP ;
1048
1049 v_excise := NVL(v_basic_ed, 0) + NVL(v_additional_ed, 0) + NVL(v_other_ed, 0);
1050
1051 RETURN NVL(v_excise,0) ;
1052
1053 END ;
1054
1055
1056 END jai_om_rma_pkg;