[Home] [Help]
107: v_once_completed_flag Varchar2(1);
108:
109: CURSOR excise_cal_cur IS
110: SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
111: FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
112: WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
113: and A.tax_id = B.tax_id
114: order by 1;
115:
271: WHERE il.customer_trx_id = v_customer_trx_id
272: AND NVL(il.interface_status , '~') <> 'P'
273: );
274:
275: DELETE JAI_AR_TRX_TAX_LINES
276: WHERE LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
277:
278: -- the followining delete from temp_lines_insert added by sriram bug # 2590650
279:
372: a.precedence_9 p_9, a.precedence_10 p_10,
373: a.tax_rate, a.tax_amount, a.uom uom_code, a.qty_rate,
374: decode(upper(b.tax_type),'EXCISE', 1, 'ADDL. EXCISE', 1, 'OTHER EXCISE', 1, 'CVD',1, 'TDS', 2, 0) tax_type_val,
375: b.tax_type
376: FROM JAI_AR_TRX_TAX_LINES a, JAI_CMN_TAXES_ALL b
377: WHERE a.customer_trx_line_id = v_prev_customer_trx_line_id
378: AND a.tax_id = b.tax_id;
379:
380:
561: FOR rec in localization_tax_info
562: LOOP
563: -- Date 23/02/2006 by sacsethi for bug 5228046
564: -- precedence 6 to 10
565: INSERT INTO JAI_AR_TRX_TAX_LINES
566: (customer_trx_line_id,
567: link_to_cust_trx_line_id,
568: tax_line_no,
569: precedence_1,
1168: FROM JAI_AR_TRX_LINES
1169: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
1170: CURSOR DUPLICATE_TAX_LINES_CUR(v_tax_id NUMBER) IS
1171: SELECT 1
1172: FROM JAI_AR_TRX_TAX_LINES
1173: WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
1174: AND tax_id = v_tax_id;
1175: v_line_count NUMBER;
1176: v_tax_line_count NUMBER;
1339: */
1340: -- bug # 3000550 sriram
1341: cursor c_cust_trx_tax_line_amt is
1342: select nvl(sum(tax_amount),0)
1343: from JAI_AR_TRX_TAX_LINES
1344: where link_to_cust_trx_line_id = v_customer_trx_line_id;
1345: -- bug # 3000550 sriram
1346: -- End of bug 3328871
1347:
1391: )
1392: ;
1393:
1394: /* Added by JMEENA for bug#6391684( FP of 6386592), Starts */
1395: CURSOR c_duplicate_tax(cp_tax_id JAI_AR_TRX_TAX_LINES.tax_id%TYPE,
1396: cp_link_cust_trx_line_id JAI_AR_TRX_TAX_LINES.link_to_cust_trx_line_id%TYPE)
1397: IS
1398: SELECT 1
1399: FROM JAI_AR_TRX_TAX_LINES
1392: ;
1393:
1394: /* Added by JMEENA for bug#6391684( FP of 6386592), Starts */
1395: CURSOR c_duplicate_tax(cp_tax_id JAI_AR_TRX_TAX_LINES.tax_id%TYPE,
1396: cp_link_cust_trx_line_id JAI_AR_TRX_TAX_LINES.link_to_cust_trx_line_id%TYPE)
1397: IS
1398: SELECT 1
1399: FROM JAI_AR_TRX_TAX_LINES
1400: WHERE tax_id = cp_tax_id
1395: CURSOR c_duplicate_tax(cp_tax_id JAI_AR_TRX_TAX_LINES.tax_id%TYPE,
1396: cp_link_cust_trx_line_id JAI_AR_TRX_TAX_LINES.link_to_cust_trx_line_id%TYPE)
1397: IS
1398: SELECT 1
1399: FROM JAI_AR_TRX_TAX_LINES
1400: WHERE tax_id = cp_tax_id
1401: AND link_to_cust_trx_line_id = cp_link_cust_trx_line_id ;
1402:
1403: ln_tax_exist NUMBER;
1527: IF v_tax_type NOT IN ('Excise','Other Excise','CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS',jai_constants.tax_type_sh_cvd_edu_cess, jai_constants.tax_type_sh_exc_edu_cess) THEN
1528: /* 'CVD_EDUCATION_CESS','EXCISE_EDUCATION_CESS' added by ssumaith - bug# 4136981*/
1529: -- Date 23/02/2006 by sacsethi for bug 5228046
1530: -- precedence 6 to 10
1531: INSERT INTO JAI_AR_TRX_TAX_LINES
1532: (
1533: tax_line_no ,
1534: customer_trx_line_id ,
1535: link_to_cust_trx_line_id ,
1583: so_taxes_rec.last_updated_by,
1584: so_taxes_rec.last_update_login
1585: );
1586:
1587: FND_FILE.PUT_LINE(FND_FILE.LOG, ' After insert into JAI_AR_TRX_TAX_LINES');
1588:
1589: v_tax_amount := NVL(v_tax_amount,0) + NVL(so_taxes_rec.tax_amount,0);
1590:
1591: FND_FILE.PUT_LINE(FND_FILE.LOG,' Tax amount: '|| v_tax_amount);
1595:
1596: -- handle for the case where the register code is not BOND_REG
1597: -- Date 23/02/2006 by sacsethi for bug 5228046
1598: -- precedence 6 to 10
1599: INSERT INTO JAI_AR_TRX_TAX_LINES
1600: (
1601: TAX_LINE_NO ,
1602: CUSTOMER_TRX_LINE_ID ,
1603: LINK_TO_CUST_TRX_LINE_ID ,
1651: so_taxes_rec.last_updated_by,
1652: so_taxes_rec.last_update_login
1653: );
1654: FND_FILE.PUT_LINE(FND_FILE.LOG,
1655: ' Else - After insert into JAI_AR_TRX_TAX_LINES');
1656: v_tax_amount := NVL(v_tax_amount,0) + NVL(so_taxes_rec.tax_amount,0);
1657: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Tax amount: '|| v_tax_amount);
1658:
1659: end if; -- for 'BOND_REG'
2073: )
2074: is
2075: select count(1)
2076: from jai_regime_tax_types_v jrttv
2077: , JAI_AR_TRX_TAX_LINES jrctt
2078: , JAI_CMN_TAXES_ALL jtc
2079: where jtc.tax_id = jrctt.tax_id
2080: and jtc.tax_type = jrttv.tax_type
2081: and regime_code = cp_regime_code
2089: /*added for bug#6498072*/
2090: Cursor get_ar_tax_amount
2091: is
2092: select sum(tax_amount)
2093: from JAI_AR_TRX_TAX_LINES
2094: Where link_to_cust_trx_line_id = v_customer_trx_line_id;
2095:
2096: ln_ar_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%type;
2097:
2092: select sum(tax_amount)
2093: from JAI_AR_TRX_TAX_LINES
2094: Where link_to_cust_trx_line_id = v_customer_trx_line_id;
2095:
2096: ln_ar_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%type;
2097:
2098: begin
2099: FND_FILE.PUT_LINE(FND_FILE.LOG,
2100: ' Inside Procedure process_bill_only_invoice');
2242: ' After insert into JAI_AR_TRXS - Bill only invoice');
2243: end if;
2244:
2245: /*
2246: insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
2247: pr_new.interface_line_attribute6 = order_line_id
2248: pr_new.interface_line_context = 'ORDER ENTRY'
2249: */
2250:
2282: because of the return statement above.
2283: */
2284: /* Date 23/02/2006 by sacsethi for bug 5228046
2285: precedence 6 to 10 */
2286: INSERT INTO JAI_AR_TRX_TAX_LINES
2287: (
2288: tax_line_no ,
2289: customer_trx_line_id ,
2290: link_to_cust_trx_line_id ,
2342: end if; /* end if for tax type not in excise .... */
2343: else /* register association is not bond register , so all taxes can flow */
2344: /* Date 23/02/2006 by sacsethi for bug 5228046
2345: precedence 6 to 10 */
2346: INSERT INTO JAI_AR_TRX_TAX_LINES
2347: (
2348: tax_line_no ,
2349: customer_trx_line_id ,
2350: link_to_cust_trx_line_id ,
2461: end if;
2462: /* Get line number after which threshold taxes needs to be defaulted */
2463: select max(tax_line_no)
2464: into ln_last_line_no
2465: from JAI_AR_TRX_TAX_LINES
2466: where link_to_cust_trx_line_id = v_customer_trx_line_id;
2467: /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
2468: select max(tax_line_no)
2469: into ln_base_line_no
2466: where link_to_cust_trx_line_id = v_customer_trx_line_id;
2467: /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
2468: select max(tax_line_no)
2469: into ln_base_line_no
2470: from JAI_AR_TRX_TAX_LINES jrctt
2471: , JAI_CMN_TAXES_ALL jtc
2472: where jrctt.link_to_cust_trx_line_id = v_customer_trx_line_id
2473: and jrctt.tax_id = jtc.tax_id
2474: and jtc.tax_type = jai_constants.tax_type_tcs;
2637: 4. 2001/05/03 Gadde,Jagdish
2638: Check added to avoid firing of trigger for Non_Indian OU.
2639: 5. 2001/06/05 Anuradha Parthasarathy
2640: Cursor added to take care of correct tax insertions
2641: into JAI_AR_TRX_TAX_LINES
2642: 6. 2001/09/13 Vijay
2643: Added condition of interface_line_context to return
2644: for Project Accounting
2645: 7. 2001/11/01 Anuradha Parthasarathy
2655: 9. 2002/04/22 SRIRAM For Bug # 2316589 . The JAI_AR_TRXS table was updated with
2656: wrong values . Instead a new update has been written using a different value to
2657: update tax amounts correctly.
2658: 10 2002/06/26 SRIRAM For Bug # 2398198 . Tax Lines were inserted multiple times in the
2659: JAI_AR_TRX_TAX_LINES , JAI_AR_TRX_LINES tables when discounts
2660: exist and tax amounts were updated doubly in JAI_AR_TRXS table, which have been
2661: solved in this bug.
2662: 11. 2002/08/17 SRIRAM .Fopr Bug # 2518534 . When there is a change in UOM in the
2663: Sales order screen , the taxes in the localization screen are not
2688:
2689: 15. 2003/06/26 Sriram - Bug # 3000550 version 616.1
2690: Tax amounts were not calculated correctly , in the JAI_AR_TRXS and JAI_AR_TRX_LINES
2691: table.This was observed when there was a split done during shipment.
2692: Data was correct in the JAI_AR_TRX_TAX_LINES table , but incorrect in the JAI_AR_TRX_LINES
2693: and JAI_AR_TRXS table.
2694: This has been fixed in this bug
2695:
2696: 16. 2003/08/22 Sriram - Bug # 3021588 version 616.2
2849: 35. 16-oCT-2007 CSahoo for bug#6498072, File Version 120.21
2850: Multipled p_quantity to the assesible value in the call to the procedure jai_rgm_thhold_proc_pkg.default_thhold_taxes
2851:
2852: 36. 18-OCT-2007 CSahoo for bug#6498072, File Version 120.22
2853: Added the cursor get_ar_tax_amount to get the total tax amount from JAI_AR_TRX_TAX_LINES table.
2854: Moved the code for Inserting into JAI_AR_TRX_LINES and updating the table JAI_AR_TRXS to the end in the procedure
2855: process_bill_only_invoice
2856:
2857: 37 11-Nov-2008 JMEENA for bug#6498345( FP 6492966 )
3216: v_tax_line_no := v_tax_line_no+1;
3217:
3218: -- Date 23/02/2006 by sacsethi for bug 5228046
3219: -- precedence 6 to 10
3220: INSERT INTO JAI_AR_TRX_TAX_LINES(
3221: tax_line_no ,
3222: customer_trx_line_id ,
3223: link_to_cust_trx_line_id ,
3224: precedence_1 ,
3292: ELSIF pr_new.interface_line_attribute4 IS NOT NULL THEN -- pr_new.interface_line_attribute1, Bug# 4392001
3293: FOR Tax_Rec IN SUPPLEMENT_TAX_LINES LOOP
3294: v_tax_line_no := v_tax_line_no+1;
3295:
3296: INSERT INTO JAI_AR_TRX_TAX_LINES(
3297: tax_line_no ,
3298: customer_trx_line_id ,
3299: link_to_cust_trx_line_id ,
3300: precedence_1 ,
3937: )
3938: then
3939: -- Date 23/02/2006 by sacsethi for bug 5228046
3940: -- precedence 6 to 10
3941: INSERT INTO JAI_AR_TRX_TAX_LINES (
3942: tax_line_no ,
3943: customer_trx_line_id ,
3944: link_to_cust_trx_line_id ,
3945: precedence_1 ,
4014: )
4015: then
4016: -- Date 23/02/2006 by sacsethi for bug 5228046
4017: -- precedence 6 to 10
4018: INSERT INTO JAI_AR_TRX_TAX_LINES(
4019: tax_line_no ,
4020: customer_trx_line_id ,
4021: link_to_cust_trx_line_id ,
4022: precedence_1 ,
4129: FETCH ja_so_lines_tax_record_check INTO v_exist_flag;
4130: CLOSE ja_so_lines_tax_record_check;
4131: /*
4132: Check if a tax line exists in ja_in_so_tax_lines_table
4133: IF yes then insert all the taxes into JAI_AR_TRX_TAX_LINES table.
4134: */
4135: IF NVL(v_exist_flag,0) = 1 THEN
4136: FOR tax_rec IN ja_so_tax_lines_info LOOP
4137: /* Added by JMEENA for bug# 6391684( FP of 6386592), Starts */
4143: IF nvl(ln_tax_exist, 0) <> 1 THEN
4144: /* Added by JMEENA for bug# 6391684 ( FP of 6386592), Ends */
4145: -- Date 23/02/2006 by sacsethi for bug 5228046
4146: -- precedence 6 to 10
4147: INSERT INTO JAI_AR_TRX_TAX_LINES(
4148: tax_line_no ,
4149: customer_trx_line_id ,
4150: link_to_cust_trx_line_id ,
4151: precedence_1 ,
4689: IS
4690: SELECT
4691: nvl(sum(tax_amount),0)
4692: FROM
4693: JAI_AR_TRX_TAX_LINES
4694: WHERE
4695: link_to_cust_trx_line_id = pr_new.customer_trx_line_id ;
4696: /*added by csahoo for bug#6407648, end*/
4697:
4804: )
4805: is
4806: select count(1)
4807: from jai_regime_tax_types_v jrttv
4808: , JAI_AR_TRX_TAX_LINES jrctt
4809: , JAI_CMN_TAXES_ALL jtc
4810: where jtc.tax_id = jrctt.tax_id
4811: and jtc.tax_type = jrttv.tax_type
4812: and regime_code = cp_regime_code
4819:
4820: Cursor get_ar_tax_amount
4821: is
4822: select sum(tax_amount)
4823: from JAI_AR_TRX_TAX_LINES
4824: Where link_to_cust_trx_line_id = v_customer_trx_line_id;
4825:
4826: ln_ar_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%type;
4827:
4822: select sum(tax_amount)
4823: from JAI_AR_TRX_TAX_LINES
4824: Where link_to_cust_trx_line_id = v_customer_trx_line_id;
4825:
4826: ln_ar_tax_amount JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%type;
4827:
4828: begin
4829: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inside Procedure process_bill_only_return');
4830: ln_hdr_exists := 0;
4966: ' After insert into JAI_AR_TRXS - Bill only invoice');
4967: end if;
4968:
4969: /*
4970: insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
4971: pr_new.interface_line_attribute6 = order_line_id
4972: pr_new.interface_line_context = 'ORDER ENTRY'
4973: */
4974:
5006: because of the return statement above.
5007: */
5008: /* Date 23/02/2006 by sacsethi for bug 5228046
5009: precedence 6 to 10 */
5010: INSERT INTO JAI_AR_TRX_TAX_LINES
5011: (
5012: tax_line_no ,
5013: customer_trx_line_id ,
5014: link_to_cust_trx_line_id ,
5066: end if; /* end if for tax type not in excise .... */
5067: else /* register association is not bond register , so all taxes can flow */
5068: /* Date 23/02/2006 by sacsethi for bug 5228046
5069: precedence 6 to 10 */
5070: INSERT INTO JAI_AR_TRX_TAX_LINES
5071: (
5072: tax_line_no ,
5073: customer_trx_line_id ,
5074: link_to_cust_trx_line_id ,
5189: end if;
5190: /* Get line number after which threshold taxes needs to be defaulted */
5191: select max(tax_line_no)
5192: into ln_last_line_no
5193: from JAI_AR_TRX_TAX_LINES
5194: where link_to_cust_trx_line_id = v_customer_trx_line_id;
5195: /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
5196: select max(tax_line_no)
5197: into ln_base_line_no
5194: where link_to_cust_trx_line_id = v_customer_trx_line_id;
5195: /* Get line number of the base tax (tax_type=TCS) for calculating the surcharge basically to set a precedence */
5196: select max(tax_line_no)
5197: into ln_base_line_no
5198: from JAI_AR_TRX_TAX_LINES jrctt
5199: , JAI_CMN_TAXES_ALL jtc
5200: where jrctt.link_to_cust_trx_line_id = v_customer_trx_line_id
5201: and jrctt.tax_id = jtc.tax_id
5202: and jtc.tax_type = jai_constants.tax_type_tcs;
5643: or
5644: (nvl(v_ont_source_code,'ORDER ENTRY') <> nvl(ltrim(rtrim(pr_new.interface_line_context)),'$$$')
5645: )
5646: then
5647: INSERT INTO JAI_AR_TRX_TAX_LINES (
5648: tax_line_no ,
5649: customer_trx_line_id ,
5650: link_to_cust_trx_line_id ,
5651: precedence_1 ,
5727: )
5728: then
5729: -- Date 23/02/2006 by sacsethi for bug 5228046
5730: -- precedence 6 to 10
5731: INSERT INTO JAI_AR_TRX_TAX_LINES
5732: (
5733: tax_line_no,
5734: customer_trx_line_id,
5735: link_to_cust_trx_line_id,
6346: WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
6347:
6348: CURSOR excise_cal_cur IS
6349: SELECT A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
6350: FROM JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
6351: WHERE link_to_cust_trx_line_id = v_customer_trx_line_id
6352: AND A.tax_id = B.tax_id
6353: ORDER BY 1;
6354:
6610: tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
6611: total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
6612: WHERE customer_trx_id = pr_old.CUSTOMER_TRX_ID;
6613:
6614: DELETE JAI_AR_TRX_TAX_LINES
6615: WHERE LINK_TO_CUST_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
6616:
6617: DELETE JAI_AR_TRX_LINES
6618: WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
7056: WHERE customer_trx_id = v_header_id;
7057:
7058: CURSOR excise_cal_cur IS
7059: select A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
7060: from JAI_AR_TRX_TAX_LINES A ,
7061: JAI_CMN_TAXES_ALL B
7062: where link_to_cust_trx_line_id = pr_old.customer_trx_line_id
7063: and A.tax_id = B.tax_id
7064: order by 1;
7392: -- WHERE A.site_use_id = NVL(p_ship_to_site_use_id,0);
7393:
7394: CURSOR excise_cal_cur IS
7395: select A.tax_id, A.tax_rate, A.tax_amount tax_amt,b.tax_type t_type
7396: from JAI_AR_TRX_TAX_LINES A , JAI_CMN_TAXES_ALL B
7397: where link_to_cust_trx_line_id = pr_old.customer_trx_line_id
7398: and A.tax_id = B.tax_id
7399: order by 1;
7400:
8027: else
8028: ln_base_tax_amount := ln_tax_amount;
8029: end if;
8030:
8031: insert into jai_ar_trx_tax_lines(
8032: tax_line_no ,
8033: customer_trx_line_id ,
8034: link_to_cust_trx_line_id ,
8035: precedence_1 ,