[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
236: AND NVL(b.inclusive_tax_flag, 'N') = 'N'; -- Added by Jia Li for inclusive tax on 2008/01/08
237:
238: CURSOR get_so_lines_count_cur (p_line_id NUMBER) IS
239: SELECT COUNT(1)
240: FROM JAI_OM_OE_SO_LINES
241: WHERE line_id = p_line_id;
242:
243: CURSOR get_rma_lines_count_cur(p_line_id NUMBER) IS
244: SELECT COUNT(1)
310: tax_amount, line_tot_amount, shipment_line_number,
311: excise_exempt_type, excise_exempt_refno, excise_exempt_date, -- added by sriram for Bug # 2672114
312: vat_exemption_flag,vat_exemption_type,vat_exemption_date ,vat_exemption_refno,vat_assessable_value /* added by ssumaith for vat */,
313: vat_reversal_price,service_type_code
314: FROM JAI_OM_OE_SO_LINES
315: WHERE header_id = p_header_Id
316: AND line_id = p_Line_Id ;
317:
318:
318:
319: --2001/04/24 Anuradha Parthasarathy
320: CURSOR get_copy_order_count(p_header_id NUMBER) IS
321: SELECT count(1)
322: FROM JAI_OM_OE_SO_LINES
323: WHERE header_id = p_header_id;
324:
325: copy_rec get_copy_order_line%ROWTYPE;
326:
367: IS
368: SELECT
369: 'X'
370: FROM
371: JAI_OM_OE_SO_LINES
372: WHERE
373: line_id = p_line_id AND
374: header_id = p_header_id ;
375:
385: IS
386: SELECT
387: lc_flag
388: FROM
389: JAI_OM_OE_SO_LINES
390: WHERE
391: line_id = pr_new.split_from_line_id;
392:
393: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
389: JAI_OM_OE_SO_LINES
390: WHERE
391: line_id = pr_new.split_from_line_id;
392:
393: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
394:
395: -- End of bug # 3057594
396:
397: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
393: l_lc_flag JAI_OM_OE_SO_LINES.LC_FLAG%TYPE;
394:
395: -- End of bug # 3057594
396:
397: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
398:
399: r_get_copy_order_line get_copy_order_line%ROWTYPE; --bgowrava for forward porting bug#4895477
400:
401: -- code segment added by sriram - LMW ATO
402: procedure calc_price_tax_for_config_item (p_header_id Number, p_line_id number)
403: is
404: cursor c_get_line_tax_amt is
405: select line_amount , tax_amount , selling_price , assessable_value , quantity -- quantity added to the select clause Bug # 2968360
406: from JAI_OM_OE_SO_LINES
407: where header_id = pr_new.header_id
408: and shipment_schedule_line_id = pr_new.ato_line_id;
409:
410: -- the last where clause handles the case where there are multiple config items in the single order
564: OR
565: ( 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))
566: then
567:
568: update JAI_OM_OE_SO_LINES
569: set line_number = pr_new.line_number , shipment_line_number = pr_new.shipment_number
570: where line_id = pr_new.line_id;
571:
572: return;
638: Added by ssumaith - bug#3671871
639: */
640:
641: IF pv_action = jai_constants.updating AND pr_new.inventory_item_id <> pr_old.inventory_item_id THEN
642: DELETE JAI_OM_OE_SO_LINES
643: WHERE line_id = pr_new.line_id;
644:
645: DELETE JAI_OM_OE_SO_TAXES
646: WHERE line_id = pr_new.line_id;
745: FETCH Get_Copy_Order_Line INTO copy_rec;
746:
747: -- start added for bug#3223481
748: IF Get_Copy_Order_Line%NOTFOUND THEN
749: -- source order line does not exist in JAI_OM_OE_SO_LINES , should not process
750: -- this could be because of quantity being 0 / cancelled line.
751:
752: IF v_line_category_code = 'ORDER' THEN -- ABCD
753: CLOSE get_copy_order_line ;
768: */
769: IF v_source_order_category_code = 'ORDER' AND v_line_category_code = 'ORDER' THEN
770:
771: --ashish shukla 1 aug02 2489301
772: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
773: IF c_source_line_id = 0 THEN
774: /*
775: in the following insert - changes are done to the insert by ssumaith - bug#3959984
776: - inventory_item_id -> v_inventory_item_id
796:
797: --End of bug#4895477
798:
799:
800: INSERT INTO JAI_OM_OE_SO_LINES (
801: line_number, line_id, header_id, inventory_item_id,
802: unit_code, quantity, tax_category_id, ato_flag,
803: selling_price, line_amount, assessable_value, tax_amount,
804: line_tot_amount, shipment_line_number,
827: This code has been added by Arun Iyer for the fix of the bug #2798930.
828: IF the source_order categoy code is Return and line category code is ORDER then
829: 1. Check whether a corresponding record exists in the rma_entry_lines table.
830: IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
831: record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
832: IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
833: */
834:
835: OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
828: IF the source_order categoy code is Return and line category code is ORDER then
829: 1. Check whether a corresponding record exists in the rma_entry_lines table.
830: IF Yes then get the details of this record into the record group variable rec_cur_get_rma_entry_lines and check whether a
831: record with the same line_id exists in the JAI_OM_OE_SO_LINES table.
832: IF such a record is not found then then insert a record into the JAI_OM_OE_SO_LINES table.
833: */
834:
835: OPEN cur_get_rma_entry_lines (V_Source_Document_Id, V_Source_Document_Line_Id);
836: FETCH cur_get_rma_entry_lines INTO rec_cur_get_rma_entry_lines;
842:
843: FETCH cur_source_line_id_exists INTO l_exists;
844: IF cur_source_line_id_exists%NOTFOUND THEN
845:
846: INSERT INTO JAI_OM_OE_SO_LINES (
847: line_number ,
848: line_id ,
849: header_id ,
850: inventory_item_id ,
950: --Added by kunkumar for forward porting to R12
951:
952: cursor c_sales_order_cur is
953: select quantity,service_type_code
954: from JAI_OM_OE_SO_LINES
955: where line_id=v_reference_line_id;
956:
957:
958: CURSOR cur_rma_entry_line_exists ( p_line_id OE_ORDER_LINES_ALL.LINE_ID%TYPE ,
1134: IS
1135: SELECT
1136: quantity
1137: FROM
1138: JAI_OM_OE_SO_LINES
1139: WHERE
1140: line_id = pr_new.reference_line_id ;**/
1141:
1142: CURSOR c_so_tax_amount (p_tax_id JAI_CMN_TAXES_ALL.tax_id%type)
1713: FETCH get_copy_order_line INTO r_get_copy_order_line;
1714: CLOSE get_copy_order_line;
1715:
1716: /*
1717: || The variable r_get_copy_order_line has the details of the current line from JAI_OM_OE_SO_LINES table
1718: */
1719:
1720: -- added a call to the procedure ja_in_calc_taxes_ato - bgowrava for forward porting bug#4895477 so that tax recalculation can happen.
1721:
1728: NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login , r_get_copy_order_line.vat_assessable_value
1729: );
1730:
1731:
1732: update JAI_OM_OE_SO_LINES
1733: set tax_amount = NVL(r_get_copy_order_line.line_amount,0) ,
1734: line_tot_amount = line_amount + NVL(r_get_copy_order_line.line_amount,0),
1735: vat_assessable_Value = r_get_copy_order_line.vat_assessable_value
1736: where header_id = pr_new.header_id
1809: p_tax_id => rec_get_rma_tax_lines.tax_id
1810: );
1811: FETCH cur_chk_tax_lines_exists INTO l_exists;
1812: IF cur_chk_tax_lines_exists%NOTFOUND THEN
1813: -- Insert into JAI_OM_OE_SO_LINES
1814:
1815: INSERT INTO JAI_OM_OE_SO_TAXES (
1816: header_id ,
1817: line_id ,
1947: v_last_update_login
1948: );
1949:
1950: UPDATE
1951: JAI_OM_OE_SO_LINES
1952: SET
1953: assessable_value = v_assessable_value ,
1954: tax_amount = NVL(v_line_tax_amount,0) ,
1955: line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
1984: v_last_update_login
1985: );
1986:
1987: UPDATE
1988: JAI_OM_OE_SO_LINES
1989: SET
1990: assessable_value = v_assessable_value ,
1991: tax_amount = NVL(v_line_tax_amount,0) ,
1992: line_tot_amount = v_line_amount + NVL(v_line_tax_amount,0) ,
2206: -- the following select and if added by sriram
2207: -- bug # 2503978
2208:
2209: c_source_line_id :=0;
2210: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
2211: IF c_source_line_id = 0 THEN
2212: /*
2213: This code added by aiyer for the bug #3057594
2214: If the original line from which the new line has been split is lc enabled i.e lc _flag has been checked
2232: /* Start of bug # 3057594 */
2233: OPEN rec_get_lc_flag ;
2234: FETCH rec_get_lc_flag INTO l_lc_flag;
2235: CLOSE rec_get_lc_flag;
2236: INSERT INTO JAI_OM_OE_SO_LINES (
2237: line_number,
2238: line_id,
2239: header_id,
2240: SPLIT_FROM_LINE_ID,
2425: END IF;
2426: END LOOP; --FOR Rec IN So_Tax_Lines_Cur( v_header_id, pr_new.SPLIT_FROM_LINE_ID)
2427:
2428: --start bug#7523501
2429: update JAI_OM_OE_SO_LINES
2430: set tax_amount = v_header_tax_amount,
2431: line_tot_amount = v_header_tax_amount + line_amount
2432: where header_id = v_header_id
2433: and line_id = v_line_id;
2434: --end bug#7523501
2435: END IF; -- NVL(v_so_tax_lines_count,0)>0 THEN
2436: /*moved this up for bug#7523501
2437: --Added by Nagaraj.s for 3140153
2438: update JAI_OM_OE_SO_LINES
2439: set tax_amount = v_header_tax_amount,
2440: line_tot_amount = v_header_tax_amount + line_amount
2441: where header_id = v_header_id
2442: and line_id = v_line_id;
2449: /*
2450: This code has been modified by Aiyer for the fix of the bug #2979969.
2451: Issue:-
2452: If an RMA order is created having a return_context as null then the record gets
2453: inserted into the JAI_OM_OE_SO_LINES table.
2454: Even though a rma line is not having the return_context field still the line should be treated as
2455: RMA and not as a sales order line.
2456:
2457: Solution:-
2458: Added an NVL clause to the below IF statement .
2459: Now even if the Return_context is null it would be treated as = LEGACY
2460: and the v_transaction_name flag would be set to LEGACY.
2461: Due to this the record would be inserted into the JAI_OM_OE_RMA_LINES table instead of the
2462: JAI_OM_OE_SO_LINES table.
2463: */
2464: -- Start of bug #2979969
2465: IF pr_new.LINE_CATEGORY_CODE = 'RETURN' THEN
2466: -- Start of Bug # 3344454
2565: WHERE RMA_LINE_ID = V_LINE_ID;
2566: DELETE JAI_OM_OE_RMA_TAXES
2567: WHERE RMA_LINE_ID = V_LINE_ID;
2568: ELSE
2569: DELETE JAI_OM_OE_SO_LINES
2570: WHERE LINE_ID = v_line_id;
2571: DELETE JAI_OM_OE_SO_TAXES
2572: WHERE Line_ID = v_line_id;
2573: END IF;
2732: v_assessable_value -- cbabu for Bug# 2687130
2733: );
2734: ELSE /* else if of v_transaction_name = 'RMA_LEGACY_INSERT' */
2735: IF v_debug = 'Y' THEN
2736: utl_file.put_line(v_myfilehandle, ' BEFORE INSERTING RECORD INTO JAI_OM_OE_SO_LINES ');
2737: END IF;
2738: -- the following select and if added by sriram
2739: -- bug # 2503978
2740:
2738: -- the following select and if added by sriram
2739: -- bug # 2503978
2740:
2741: c_source_line_id :=0;
2742: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
2743: IF c_source_line_id = 0 THEN
2744: INSERT INTO JAI_OM_OE_SO_LINES
2745: (
2746: line_number,
2740:
2741: c_source_line_id :=0;
2742: SELECT COUNT(*) INTO c_source_line_id FROM JAI_OM_OE_SO_LINES WHERE LINE_ID = v_line_id;
2743: IF c_source_line_id = 0 THEN
2744: INSERT INTO JAI_OM_OE_SO_LINES
2745: (
2746: line_number,
2747: line_id,
2748: header_id,
2813: calc_price_tax_for_config_item(pr_new.header_id , pr_new.line_id );
2814:
2815: -- Select 'after calling calc_price_tax_for_config_item ' into v_trigg_stat from dual;
2816:
2817: update JAI_OM_OE_SO_LINES
2818: set line_amount = v_ato_line_amount,
2819: --tax_amount = v_ato_tax_amount -- Not reqired
2820: assessable_value = v_ato_assessable_value,
2821: selling_price = v_ato_selling_price
2834: -- ato_line_id gets the line_id of the model item
2835:
2836: cursor c_model_tax_Categ is
2837: select tax_category_id , inventory_item_id , line_amount
2838: from JAI_OM_OE_SO_LINES
2839: where header_id = pr_new.header_id
2840: and line_id = pr_new.ato_line_id ;
2841:
2842: v_output_tax_amount Number;
2948: NULL,NULL,NULL,NULL,pr_new.last_update_date,pr_new.last_updated_by,pr_new.last_update_login
2949: );
2950:
2951: -- update the tax amounts after doing tax recalculation .
2952: update JAI_OM_OE_SO_LINES
2953: set tax_amount = NVL(v_ato_line_amount,0) ,
2954: line_tot_amount = line_amount + NVL(v_ato_line_amount,0)
2955: where header_id = pr_new.header_id
2956: and line_id = pr_new.line_id;
3042:
3043: /* Bug 5095812. Added by Lakshmi Gopalsami */
3044: l_func_curr_det jai_plsql_cache_pkg.func_curr_details;
3045:
3046: CURSOR JAI_OM_OE_SO_LINES_cur(p_line_id NUMBER) IS
3047: SELECT quantity,
3048: --selling_price,
3049: assessable_value,
3050: --line_amount
3050: --line_amount
3051: excise_exempt_type,
3052: excise_exempt_refno,
3053: vat_reversal_price --Date 14/06/2007 by sacsethi for bug 6072461
3054: FROM JAI_OM_OE_SO_LINES
3055: WHERE line_id = p_line_id;
3056:
3057: CURSOR c_ja_in_rma_lines (p_line_id NUMBER) IS
3058: SELECT quantity,
3121:
3122: CURSOR Ato_line_info_cur IS
3123: SELECT assessable_value,
3124: quantity
3125: FROM JAI_OM_OE_SO_LINES
3126: WHERE shipment_schedule_line_id = v_line_id; /* Modified by Ramananda for removal of SQL LITERALs */
3127: -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
3128:
3129: CURSOR so_lines_count IS
3127: -- WHERE NVL(shipment_schedule_line_id,0) = v_line_id;
3128:
3129: CURSOR so_lines_count IS
3130: SELECT COUNT(*)
3131: FROM JAI_OM_OE_SO_LINES
3132: WHERE header_id = v_header_id;
3133:
3134: v_count NUMBER;
3135:
3143: -- cursor added by sriram for ato support during partial shipment - Bug # 2806274
3144:
3145: Cursor c_get_loc_record is
3146: select selling_price , assessable_value
3147: from JAI_OM_OE_SO_LINES
3148: where header_id = pr_new.header_id
3149: and line_id = pr_new.line_id ;
3150:
3151: v_loc_selling_price Number;
3150:
3151: v_loc_selling_price Number;
3152: v_loc_assessable_value Number;
3153:
3154: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3155:
3156: ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
3157:
3158: --2001/06/14 Jagdish,Gadde
3152: v_loc_assessable_value Number;
3153:
3154: ln_vat_assessable_value JAI_OM_OE_SO_LINES.VAT_ASSESSABLE_VALUE%TYPE;
3155:
3156: ln_vat_reversal_price JAI_OM_OE_SO_LINES.vat_reversal_price%TYPE; --Date 14/06/2007 by sacsethi for bug 6072461
3157:
3158: --2001/06/14 Jagdish,Gadde
3159: BEGIN
3160: pv_return_code := jai_constants.successful ;
3207: -- the following if condition " if pr_new.LINE_CATEGORY_CODE = 'RETURN' then " added by sriram
3208: -- for the fix of bug # 3181926
3209: -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
3210: -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
3211: -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
3212: -- 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
3213: -- table.
3214: -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
3215: -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
3208: -- for the fix of bug # 3181926
3209: -- When a Legacy return order is created and line saved and if quantity is changed , this trigger was throwing up
3210: -- an exception - DIVIDE BY ZERO .The reason for this is that the cursor which fetches the old quantity and old
3211: -- assessable value fetched the values from the JAI_OM_OE_SO_LINES table. For a return order , this is not relevant
3212: -- 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
3213: -- table.
3214: -- code added by sriram includes adding the if statement below , adding the elsif condition and opening the cursor
3215: -- c_ja_in_rma_lines . This cursor definition also has been added by sriram.
3216: /*bduvarag for the bug#5256498 start*/
3224: OPEN c_ja_in_rma_lines (v_line_id);
3225: FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
3226: CLOSE c_ja_in_rma_lines ;
3227: else
3228: OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
3229: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
3230: v_excise_exempt_type, -- added by sriram - bug # 2672114
3231: v_excise_exempt_refno, -- added by sriram - bug # 2672114
3232: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
3225: FETCH c_ja_in_rma_lines INTO v_old_quantity, v_old_assessable_value;
3226: CLOSE c_ja_in_rma_lines ;
3227: else
3228: OPEN JAI_OM_OE_SO_LINES_cur(v_line_id);
3229: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
3230: v_excise_exempt_type, -- added by sriram - bug # 2672114
3231: v_excise_exempt_refno, -- added by sriram - bug # 2672114
3232: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
3233: CLOSE JAI_OM_OE_SO_LINES_cur;
3229: FETCH JAI_OM_OE_SO_LINES_cur INTO v_old_quantity, v_old_assessable_value,
3230: v_excise_exempt_type, -- added by sriram - bug # 2672114
3231: v_excise_exempt_refno, -- added by sriram - bug # 2672114
3232: ln_vat_reversal_price ; --Date 14/06/2007 by sacsethi for bug 6072461
3233: CLOSE JAI_OM_OE_SO_LINES_cur;
3234: end if;
3235:
3236: --END IF; --1
3237:
3309:
3310: Functional Description:-
3311: During partial shipment, if the assessable price list setup has been removed after booking an order then
3312: during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
3313: 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).
3314:
3315: Technical Description:-
3316: Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
3317: IF
3312: during the excise duty recalculation, the assessable value (which would be found as null in the setup ) should be taken from the
3313: 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).
3314:
3315: Technical Description:-
3316: Check whether the assessable value is null in the table JAI_OM_OE_SO_LINES
3317: IF
3318: no then assign this value to the v_assessable_value variable
3319: ELSE
3320: Assign the assign the nvl(pr_new.unit_selling_price) to the v_assessable_value variable.
3334: IS
3335: SELECT
3336: assessable_value
3337: FROM
3338: JAI_OM_OE_SO_LINES
3339: WHERE
3340: line_id = v_line_id;
3341:
3342: cur_rec_get_assessable_value rec_get_assessable_value%ROWTYPE;
3413: 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 */
3414: /*
3415: || Start of bug 4566002
3416: || Code modified for bug 4566002
3417: || Added the VAT assessable value in the update to jai_om_oe_so_lines table
3418: */
3419: UPDATE jai_om_oe_so_lines
3420: SET
3421: quantity = v_quantity ,
3415: || Start of bug 4566002
3416: || Code modified for bug 4566002
3417: || Added the VAT assessable value in the update to jai_om_oe_so_lines table
3418: */
3419: UPDATE jai_om_oe_so_lines
3420: SET
3421: quantity = v_quantity ,
3422: unit_code = v_uom_code , --Added by Nagaraj.s for Bug#3402260
3423: selling_price = v_selling_price ,