[Home] [Help]
52:
53: DELETE FROM JAI_OM_OE_RMA_TAXES
54: WHERE rma_line_id = v_line_id;
55: ELSE
56: DELETE JAI_OM_OE_SO_LINES
57: WHERE header_id = v_header_id
58: AND line_id = v_line_id;
59:
60: DELETE JAI_OM_OE_SO_TAXES
255: AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
256:
257: CURSOR get_so_lines_count_cur (p_line_id NUMBER) IS
258: SELECT COUNT(1)
259: FROM JAI_OM_OE_SO_LINES
260: WHERE line_id = p_line_id;
261:
262: CURSOR get_rma_lines_count_cur(p_line_id NUMBER) IS
263: SELECT COUNT(1)
330: excise_exempt_type, excise_exempt_refno, excise_exempt_date, -- added by sriram for Bug # 2672114
331: vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value /* added by ssumaith for vat */,
332: /*gst_assessable_value,*/--added by peng.zheng for bug 10043656
333: vat_reversal_price,service_type_code
334: FROM JAI_OM_OE_SO_LINES
335: WHERE header_id = p_header_Id
336: AND line_id = p_Line_Id ;
337:
338:
338:
339: --2001/04/24 Anuradha Parthasarathy
340: CURSOR get_copy_order_count(p_header_id NUMBER) IS
341: SELECT count(1)
342: FROM JAI_OM_OE_SO_LINES
343: WHERE header_id = p_header_id;
344:
345: copy_rec get_copy_order_line%ROWTYPE;
346:
387: IS
388: SELECT
389: 'X'
390: FROM
391: JAI_OM_OE_SO_LINES
392: WHERE
393: line_id = p_line_id AND
394: header_id = p_header_id ;
395:
405: IS
406: SELECT
407: lc_flag
408: FROM
409: JAI_OM_OE_SO_LINES
410: WHERE
411: line_id = pr_new.split_from_line_id;
412:
413: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
409: JAI_OM_OE_SO_LINES
410: WHERE
411: line_id = pr_new.split_from_line_id;
412:
413: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
414:
415: -- End of bug # 3057594
416:
417: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
413: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
414:
415: -- End of bug # 3057594
416:
417: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
418: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.gst_ASSESSABLE_VALUE%TYPE;*/
419:
420: r_get_copy_order_line get_copy_order_line%ROWTYPE; --bgowrava for forward porting bug#4895477
421:
414:
415: -- End of bug # 3057594
416:
417: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
418: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.gst_ASSESSABLE_VALUE%TYPE;*/
419:
420: r_get_copy_order_line get_copy_order_line%ROWTYPE; --bgowrava for forward porting bug#4895477
421:
422: --added by peng.zheng for bug 10043656, begins
437: cursor c_get_line_tax_amt is
438: select line_amount , tax_amount , selling_price , assessable_value , quantity, -- quantity added to the select clause Bug # 2968360
439: vat_assessable_value -- added for bug#8924003
440: /*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
441: from JAI_OM_OE_SO_LINES
442: where header_id = pr_new.header_id
443: and shipment_schedule_line_id = pr_new.ato_line_id;
444:
445: -- the last where clause handles the case where there are multiple config items in the single order
594: 3). added logic to validate VAT Return days for Order copying from 'Standard' / 'Mixed'
595: order type to 'Return' line type.
596: 20. 13-MAY-2010 vkaranam for bug#9436523
597: issue:
598: ORA-20001: APP--20110: Taxes are not matching in JAI_OM_OE_SO_LINES and
599: JA_IN_SO_TAX_LINE FOR LINE_ID 4656 while shipping the splitted line.
600: Reason:
601: issue is happening with order having inclusive taxes.
602: inclusive tax amount is updated in jai_om_oe_so_lines.tax_amount.
598: ORA-20001: APP--20110: Taxes are not matching in JAI_OM_OE_SO_LINES and
599: JA_IN_SO_TAX_LINE FOR LINE_ID 4656 while shipping the splitted line.
600: Reason:
601: issue is happening with order having inclusive taxes.
602: inclusive tax amount is updated in jai_om_oe_so_lines.tax_amount.
603: ideally jai_om_oe_so_lines.tax_amount shall be 0 for inclusive taxes.
604:
605: Fix:
606:
599: JA_IN_SO_TAX_LINE FOR LINE_ID 4656 while shipping the splitted line.
600: Reason:
601: issue is happening with order having inclusive taxes.
602: inclusive tax amount is updated in jai_om_oe_so_lines.tax_amount.
603: ideally jai_om_oe_so_lines.tax_amount shall be 0 for inclusive taxes.
604:
605: Fix:
606:
607: updated jai_om_oe_so_lines.tax_amount as the tax amount for exclusive taxes.
603: ideally jai_om_oe_so_lines.tax_amount shall be 0 for inclusive taxes.
604:
605: Fix:
606:
607: updated jai_om_oe_so_lines.tax_amount as the tax amount for exclusive taxes.
608: 21. 27-May-2010 Allen Yang for bug #9722577
609: Issue: variable v_converted_rate is used without initialization for func_tax_amount
610: calculation when copying from Order to Order.
611: Fix: added logic to initialize v_converted_rate
622:
623: 23. 10-Sep-2010 Jia for GST Bug#10043656.
624: 24. 13-Sep-2012 Bug 11774053
625: Issue: IMPROPER ERROR WHEN SALES ORDER IS SAVED WITH OUT QUANTITY
626: Fix: Column 'quantity' is not nullable in both JAI_OM_OE_SO_LINES and JAI_OM_OE_RMA_LINES.
627: So add a quantity check before inserting lines into JAI table.
628:
629: 17. 03-Oct-2012 mmurtuza for bug 14675557
630: Issue: ASSESSABLE AMOUNT IS SHOWING WRONG VALUE FOR A COPIED ORDER
697: OR
698: ( pv_action = jai_constants.updating and ( (nvl(pr_old.shipment_number,-9999) <> nvl(pr_new.shipment_number,-9998)) and pr_new.shipment_number is not null))
699: then
700:
701: update JAI_OM_OE_SO_LINES
702: set line_number = pr_new.line_number , shipment_line_number = pr_new.shipment_number
703: where line_id = pr_new.line_id;
704:
705: return;
771: Added by ssumaith - bug#3671871
772: */
773:
774: IF pv_action = jai_constants.updating AND pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
775: DELETE JAI_OM_OE_SO_LINES
776: WHERE line_id = pr_new.line_id;
777:
778: DELETE JAI_OM_OE_SO_TAXES
779: WHERE line_id = pr_new.line_id;
878: FETCH Get_Copy_Order_Line INTO copy_rec;
879:
880: -- start added for bug#3223481
881: IF Get_Copy_Order_Line%NOTFOUND THEN
882: -- source order line does not exist in JAI_OM_OE_SO_LINES , should not process
883: -- this could be because of quantity being 0 / cancelled line.
884:
885: IF v_line_category_code = 'ORDER' THEN -- ABCD
886: CLOSE get_copy_order_line ;
901: */
902: IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
903:
904: --ashish shukla 1 aug02 2489301
905: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
906: IF c_source_line_id = 0 THEN
907: /*
908: in the following insert - changes are done to the insert by ssumaith - bug#3959984
909: - inventory_item_id -> v_inventory_item_id
973: ln_gst_assessable_value := nvl(ln_gst_assessable_value,0) * v_line_quantity;*/
974: --added by peng.zheng for bug 10043656, ends
975:
976:
977: INSERT INTO JAI_OM_OE_SO_LINES (
978: line_number, line_id, header_id, inventory_item_id,
979: unit_code, quantity, tax_category_id, ato_flag,
980: selling_price, line_amount, assessable_value, tax_amount,
981: line_tot_amount, shipment_line_number,
1008: This code has been added by Arun Iyer for the fix of the bug #2798930.
1009: IF the source_order categoy code is Return and line category code is ORDER then
1010: 1. Check whether a corresponding record exists in the rma_entry_lines table.
1011: IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
1012: record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
1013: IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
1014: */
1015:
1016: OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
1009: IF the source_order categoy code is Return and line category code is ORDER then
1010: 1. Check whether a corresponding record exists in the rma_entry_lines table.
1011: IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
1012: record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
1013: IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
1014: */
1015:
1016: OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
1017: FETCH cur_get_rma_entry_lines INTO rec_cur_get_rma_entry_lines;
1023:
1024: FETCH cur_source_line_id_exists INTO l_exists;
1025: IF cur_source_line_id_exists%NOTFOUND THEN
1026:
1027: INSERT INTO JAI_OM_OE_SO_LINES (
1028: line_number ,
1029: line_id ,
1030: header_id ,
1031: inventory_item_id ,
1131: --Added by kunkumar for forward porting to R12
1132:
1133: cursor c_sales_order_cur is
1134: select quantity,service_type_code
1135: from JAI_OM_OE_SO_LINES
1136: where line_id=v_reference_line_id;
1137:
1138:
1139: CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
1330: IS
1331: SELECT
1332: quantity
1333: FROM
1334: JAI_OM_OE_SO_LINES
1335: WHERE
1336: line_id = pr_new.reference_line_id ;**/
1337:
1338: CURSOR c_so_tax_amount (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type)
2051: pl.tax_category_id ;
2052:
2053: cursor c_sales_order_cur is
2054: select quantity,service_type_code
2055: from JAI_OM_OE_SO_LINES
2056: where line_id=v_reference_line_id;
2057:
2058:
2059: CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
2817: FETCH get_copy_order_line INTO r_get_copy_order_line;
2818: CLOSE get_copy_order_line;
2819:
2820: /*
2821: || The variable r_get_copy_order_line has the details of the current line from JAI_OM_OE_SO_LINES table
2822: */
2823:
2824: -- added a call to the procedure ja_in_calc_taxes_ato - bgowrava for forward porting bug#4895477 so that tax recalculation can happen.
2825:
2846: /*,pn_gst_assessable_Value => r_get_copy_order_line.gst_assessable_value */--Added by zhiwei for bug10043656 GST enhancement 2010/09/14
2847: );
2848:
2849:
2850: update JAI_OM_OE_SO_LINES
2851: set tax_amount = NVL(r_get_copy_order_line.line_amount,0) ,
2852: line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,0),
2853: assessable_value = v_assessable_value, /*Added v_assessable_value by mmurtuza for bug 14675557*/
2854: vat_assessable_Value = ln_vat_assessable_value /*r_get_copy_order_line.vat_assessable_value*/ /*changed to ln_vat_assessable_value by mmurtuza for bug 14675557*/
2929: p_tax_id => rec_get_rma_tax_lines.tax_id
2930: );
2931: FETCH cur_chk_tax_lines_exists INTO l_exists;
2932: IF cur_chk_tax_lines_exists%NOTFOUND THEN
2933: -- Insert into JAI_OM_OE_SO_LINES
2934:
2935: INSERT INTO JAI_OM_OE_SO_TAXES (
2936: header_id ,
2937: line_id ,
3083: /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3084: );
3085:
3086: UPDATE
3087: JAI_OM_OE_SO_LINES
3088: SET
3089: assessable_value = v_assessable_value ,
3090: tax_amount = NVL(v_line_tax_amount,0) ,
3091: line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
3121: /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
3122: );
3123:
3124: UPDATE
3125: JAI_OM_OE_SO_LINES
3126: SET
3127: assessable_value = v_assessable_value ,
3128: tax_amount = NVL(v_line_tax_amount,0) ,
3129: line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
3343: -- the following select and if added by sriram
3344: -- bug # 2503978
3345:
3346: c_source_line_id :=0;
3347: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
3348: IF c_source_line_id = 0 THEN
3349: /*
3350: This code added by aiyer for the bug #3057594
3351: If the original line from which the new line has been split is lc enabled i.e lc _flag has been checked
3370: /* Start of bug # 3057594 */
3371: OPEN rec_get_lc_flag ;
3372: FETCH rec_get_lc_flag INTO l_lc_flag;
3373: CLOSE rec_get_lc_flag;
3374: INSERT INTO JAI_OM_OE_SO_LINES (
3375: line_number,
3376: line_id,
3377: header_id,
3378: SPLIT_FROM_LINE_ID,
3575: IF v_debug = 'Y' THEN
3576: utl_file.put_line(v_myfilehandle,'** v_header_tax_amount '||v_header_tax_amount);
3577: END IF;
3578: --end additions for bug#9436523
3579: update JAI_OM_OE_SO_LINES
3580: set tax_amount = nvl(v_header_tax_amount,0),
3581: line_tot_amount = nvl(v_header_tax_amount,0) + nvl(line_amount,0)
3582: where header_id = v_header_id
3583: and line_id = v_line_id;
3584: --end bug#7523501
3585: END IF; -- NVL(v_so_tax_lines_count,0)>0 THEN
3586: /*moved this up for bug#7523501
3587: --Added by Nagaraj.s for 3140153
3588: update JAI_OM_OE_SO_LINES
3589: set tax_amount = v_header_tax_amount,
3590: line_tot_amount = v_header_tax_amount + line_amount
3591: where header_id = v_header_id
3592: and line_id = v_line_id;
3599: /*
3600: This code has been modified by Aiyer for the fix of the bug #2979969.
3601: Issue:-
3602: If an RMA order is created having a return_context as null then the record gets
3603: inserted into the JAI_OM_OE_SO_LINES table.
3604: Even though a rma line is not having the return_context field still the line should be treated as
3605: RMA and not as a sales order line.
3606:
3607: Solution:-
3608: Added an NVL clause to the below IF statement .
3609: Now even if the Return_context is null it would be treated as = LEGACY
3610: and the v_transaction_name flag would be set to LEGACY.
3611: Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
3612: JAI_OM_OE_SO_LINES table.
3613: */
3614: -- Start of bug #2979969
3615: IF pr_new.LINE_CATEGORY_CODE = 'RETURN' THEN
3616: -- Start of Bug # 3344454
3730: WHERE RMA_LINE_ID = V_LINE_ID;
3731: DELETE JAI_OM_OE_RMA_TAXES
3732: WHERE RMA_LINE_ID = V_LINE_ID;
3733: ELSE
3734: DELETE JAI_OM_OE_SO_LINES
3735: WHERE LINE_ID = v_line_id;
3736: DELETE JAI_OM_OE_SO_TAXES
3737: WHERE Line_ID = v_line_id;
3738: END IF;
3944: v_assessable_value -- cbabu for Bug# 2687130
3945: );
3946: ELSE /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
3947: IF v_debug = 'Y' THEN
3948: utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
3949: END IF;
3950: -- the following select and if added by sriram
3951: -- bug # 2503978
3952:
3950: -- the following select and if added by sriram
3951: -- bug # 2503978
3952:
3953: c_source_line_id :=0;
3954: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
3955: IF c_source_line_id = 0 THEN
3956: INSERT INTO JAI_OM_OE_SO_LINES
3957: (
3958: line_number,
3952:
3953: c_source_line_id :=0;
3954: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
3955: IF c_source_line_id = 0 THEN
3956: INSERT INTO JAI_OM_OE_SO_LINES
3957: (
3958: line_number,
3959: line_id,
3960: header_id,
4027: calc_price_tax_for_config_item(pr_new.header_id , pr_new.line_id );
4028:
4029: -- Select 'after calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
4030:
4031: update JAI_OM_OE_SO_LINES
4032: set line_amount = v_ato_line_amount,
4033: --tax_amount = v_ato_tax_amount -- Not reqired
4034: assessable_value = v_ato_assessable_value,
4035: vat_assessable_value = v_ato_vat_assessable_value, --added for bug#8924003
4050: -- ato_line_id gets the line_id of the model item
4051:
4052: cursor c_model_tax_Categ is
4053: select tax_category_id , inventory_item_id , line_amount
4054: from JAI_OM_OE_SO_LINES
4055: where header_id = pr_new.header_id
4056: and line_id = pr_new.ato_line_id ;
4057:
4058: v_output_tax_amount Number;
4167: /*,v_ato_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4168: );
4169:
4170: -- update the tax amounts after doing tax recalculation .
4171: update JAI_OM_OE_SO_LINES
4172: set tax_amount = NVL(v_ato_line_amount,0) ,
4173: line_tot_amount = line_amount + NVL(v_ato_line_amount,0)
4174: where header_id = pr_new.header_id
4175: and line_id = pr_new.line_id;
4262: ln_customer_id NUMBER;
4263: ln_ship_to_org_id NUMBER;
4264: ln_fulfilled_quantity NUMBER;
4265:
4266: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
4267: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4268:
4269: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
4270: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
4263: ln_ship_to_org_id NUMBER;
4264: ln_fulfilled_quantity NUMBER;
4265:
4266: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
4267: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4268:
4269: lv_vat_exemption_flag JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_FLAG%TYPE;
4270: lv_vat_exemption_type JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_TYPE%TYPE;
4271: ld_vat_exemption_date JAI_OM_WSH_LINES_ALL.VAT_EXEMPTION_DATE%TYPE;
4340: , vat_exemption_type
4341: , vat_exemption_date
4342: , vat_exemption_refno
4343: /*, NVL(gst_assessable_value, 0)*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
4344: FROM JAI_OM_OE_SO_LINES
4345: WHERE Line_id = ln_order_line_id;
4346:
4347: CURSOR Get_Tot_Tax_Amount_Cur IS
4348: SELECT SUM(jowlt.Tax_Amount)
4944:
4945: /* Bug 5095812. Added by Lakshmi Gopalsami */
4946: l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
4947:
4948: CURSOR JAI_OM_OE_SO_LINES_cur(p_line_id NUMBER) IS
4949: SELECT quantity,
4950: --selling_price,
4951: assessable_value,
4952: --line_amount
4952: --line_amount
4953: excise_exempt_type,
4954: excise_exempt_refno,
4955: vat_reversal_price --Date 14/06/2007 by sacsethi for bug 6072461
4956: FROM JAI_OM_OE_SO_LINES
4957: WHERE line_id = p_line_id;
4958:
4959: CURSOR c_ja_in_rma_lines (p_line_id NUMBER) IS
4960: SELECT quantity,
5023:
5024: CURSOR Ato_line_info_cur IS
5025: SELECT assessable_value,
5026: quantity
5027: FROM JAI_OM_OE_SO_LINES
5028: WHERE shipment_schedule_line_id = v_line_id; /* Modified by Ramananda for removal of SQL LITERALs */
5029: -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
5030:
5031: CURSOR so_lines_count IS
5029: -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
5030:
5031: CURSOR so_lines_count IS
5032: SELECT COUNT(*)
5033: FROM JAI_OM_OE_SO_LINES
5034: WHERE header_id = v_header_id;
5035:
5036: v_count NUMBER;
5037:
5045: -- cursor added by sriram for ato support during partial shipment - Bug # 2806274
5046:
5047: Cursor c_get_loc_record is
5048: select selling_price , assessable_value
5049: from JAI_OM_OE_SO_LINES
5050: where header_id = pr_new.header_id
5051: and line_id = pr_new.line_id ;
5052:
5053: v_loc_selling_price Number;
5052:
5053: v_loc_selling_price Number;
5054: v_loc_assessable_value Number;
5055:
5056: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
5057: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/--added by peng.zheng for bug 10043656
5058:
5059: ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
5060: --added for bug#9067808,start
5053: v_loc_selling_price Number;
5054: v_loc_assessable_value Number;
5055:
5056: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
5057: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/--added by peng.zheng for bug 10043656
5058:
5059: ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
5060: --added for bug#9067808,start
5061: ln_ordered_qty NUMBER ;
5055:
5056: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
5057: /*ln_gst_assessable_value JAI_OM_OE_SO_LINES.GST_ASSESSABLE_VALUE%TYPE;*/--added by peng.zheng for bug 10043656
5058:
5059: ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
5060: --added for bug#9067808,start
5061: ln_ordered_qty NUMBER ;
5062:
5063: PROCEDURE calc_price_tax_for_config_item (p_header_id number, p_line_id number)
5065: CURSOR c_get_line_tax_amt is
5066: SELECT line_amount, tax_amount, selling_price,
5067: assessable_value, decode(quantity,0,1,quantity) quantity, vat_assessable_value
5068: /*,gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5069: FROM JAI_OM_OE_SO_LINES
5070: WHERE header_id = p_header_id
5071: AND shipment_schedule_line_id = pr_new.ato_line_id
5072: AND line_id <> p_line_id ;
5073: BEGIN
5183: -- the following if condition " if pr_new.LINE_CATEGORY_CODE = 'RETURN' then " added by sriram
5184: -- for the fix of bug # 3181926
5185: -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
5186: -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
5187: -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
5188: -- as the JAI_OM_OE_SO_LINES has no records for a return order and instead records are present in the JAI_OM_OE_RMA_LINES
5189: -- table.
5190: -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
5191: -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
5184: -- for the fix of bug # 3181926
5185: -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
5186: -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
5187: -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
5188: -- as the JAI_OM_OE_SO_LINES has no records for a return order and instead records are present in the JAI_OM_OE_RMA_LINES
5189: -- table.
5190: -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
5191: -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
5192: /*bduvarag for the bug#5256498 start*/
5200: OPEN c_ja_in_rma_lines (v_line_id);
5201: FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
5202: CLOSE c_ja_in_rma_lines ;
5203: else
5204: OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
5205: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
5206: v_excise_exempt_type, -- added by sriram - bug # 2672114
5207: v_excise_exempt_refno, -- added by sriram - bug # 2672114
5208: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
5201: FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
5202: CLOSE c_ja_in_rma_lines ;
5203: else
5204: OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
5205: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
5206: v_excise_exempt_type, -- added by sriram - bug # 2672114
5207: v_excise_exempt_refno, -- added by sriram - bug # 2672114
5208: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
5209: CLOSE JAI_OM_OE_SO_LINES_cur;
5205: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
5206: v_excise_exempt_type, -- added by sriram - bug # 2672114
5207: v_excise_exempt_refno, -- added by sriram - bug # 2672114
5208: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
5209: CLOSE JAI_OM_OE_SO_LINES_cur;
5210: end if;
5211:
5212: --END IF; --1
5213:
5301:
5302: Functional Description:-
5303: During partial shipment, if the assessable price list setup has been removed after booking an order then
5304: during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
5305: JAI_OM_OE_SO_LINES table and if this too happens to be null then only should it be assigned as nvl(pr_new.unit_selling_price).
5306:
5307: Technical Description:-
5308: Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
5309: IF
5304: during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
5305: JAI_OM_OE_SO_LINES table and if this too happens to be null then only should it be assigned as nvl(pr_new.unit_selling_price).
5306:
5307: Technical Description:-
5308: Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
5309: IF
5310: no then assign this value to the v_assessable_value variable
5311: ELSE
5312: Assign the assign the nvl(pr_new.unit_selling_price) to the v_assessable_value variable.
5326: IS
5327: SELECT
5328: assessable_value
5329: FROM
5330: JAI_OM_OE_SO_LINES
5331: WHERE
5332: line_id = v_line_id;
5333:
5334: cur_rec_get_assessable_value rec_get_assessable_value%ROWTYPE;
5414: IF pr_new.line_category_code = 'ORDER' THEN /*added by aiyer for the bug 5401180. Replaced nvl(pr_new.return_context,'XXX') <> 'LEGACY' with this */
5415: /*
5416: || Start of bug 4566002
5417: || Code modified for bug 4566002
5418: || Added the VAT assessable value in the update to jai_om_oe_so_lines table
5419: */
5420: UPDATE jai_om_oe_so_lines
5421: SET
5422: quantity = v_quantity ,
5416: || Start of bug 4566002
5417: || Code modified for bug 4566002
5418: || Added the VAT assessable value in the update to jai_om_oe_so_lines table
5419: */
5420: UPDATE jai_om_oe_so_lines
5421: SET
5422: quantity = v_quantity ,
5423: unit_code = v_uom_code , --Added by Nagaraj.s for Bug#3402260
5424: selling_price = v_selling_price ,
5469: ln_vat_reversal_price
5470: /*,ln_gst_assessable_value*/ --Added by zhiwei for bug10043656 GST enhancement 2010/09/14
5471: );
5472:
5473: UPDATE JAI_OM_OE_SO_LINES
5474: SET quantity = v_quantity,
5475: unit_code = v_uom_code, --Added by Nagaraj.s for Bug#3402260
5476: selling_price = v_selling_price,
5477: assessable_value = nvl(v_assessable_value,v_selling_price),