[Home] [Help]
114: order by 1;
115:
116: CURSOR old_line_amount_cur IS
117: SELECT line_amount ,tax_amount --added tax_amount for bug#7539258
118: FROM JAI_AR_TRX_LINES
119: WHERE CUSTOMER_TRX_ID = v_customer_trx_id AND
120: CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
121:
122: CURSOR ONCE_COMPLETE_FLAG_CUR IS
282: AND
283: LINK_TO_CUST_TRX_LINE_ID = v_customer_trx_line_id;
284:
285:
286: DELETE JAI_AR_TRX_LINES
287: WHERE CUSTOMER_TRX_ID = v_customer_trx_id AND
288: CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
289: /* Added an exception block by Ramananda for bug#4570303 */
290: EXCEPTION
357: WHERE CUSTOMER_TRX_LINE_ID = v_prev_customer_trx_line_id;
358:
359: Cursor localization_line_info IS
360: Select assessable_value, tax_category_id, service_type_code --service_type_code added by csahoo for bug#5879769
361: From JAI_AR_TRX_LINES
362: Where customer_trx_line_id = v_prev_customer_trx_line_id;
363:
364:
365: -- Date 23/02/2006 by sacsethi for bug 5228046
506: Open localization_line_info;
507: Fetch localization_line_info into v_tax_category_id, v_price_list, lv_service_type_code; -- lv_service_type_code added by csahoo for bug#5879769
508: Close localization_line_info;
509:
510: INSERT INTO JAI_AR_TRX_LINES
511: (customer_trx_line_id,
512: line_number,
513: customer_trx_id,
514: description,
622: Set total_amount = nvl(total_amount,0) + nvl(pr_new.extended_amount,0),
623: tax_amount = nvl(tax_amount,0) + nvl(pr_new.extended_amount,0)
624: Where Customer_Trx_Id = v_header_id;
625: END IF;
626: Update JAI_AR_TRX_LINES
627: Set total_amount = nvl(total_amount,0) + nvl(pr_new.extended_amount,0),
628: tax_amount = nvl(tax_amount,0) + nvl(pr_new.extended_amount,0)
629: Where Customer_Trx_Id = v_header_id
630: and Customer_Trx_Line_Id = v_link_to_cust_id;
710: v_so_tax_amount NUMBER;
711: vsqlerrm VARCHAR2(240);
712: v_qty NUMBER; -- added by sriram
713: -- added, Harshita for bug#4245062
714: lv_vat_exemption_flag JAI_AR_TRX_LINES.vat_exemption_flag%TYPE ;
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
711: vsqlerrm VARCHAR2(240);
712: v_qty NUMBER; -- added by sriram
713: -- added, Harshita for bug#4245062
714: lv_vat_exemption_flag JAI_AR_TRX_LINES.vat_exemption_flag%TYPE ;
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
719: -- ln_gst_assessable_value JAI_AR_TRX_LINES.gst_assessable_value%TYPE ;--Added by Xiao Lv for bug 10043656 16470035
712: v_qty NUMBER; -- added by sriram
713: -- added, Harshita for bug#4245062
714: lv_vat_exemption_flag JAI_AR_TRX_LINES.vat_exemption_flag%TYPE ;
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
719: -- ln_gst_assessable_value JAI_AR_TRX_LINES.gst_assessable_value%TYPE ;--Added by Xiao Lv for bug 10043656 16470035
720: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
713: -- added, Harshita for bug#4245062
714: lv_vat_exemption_flag JAI_AR_TRX_LINES.vat_exemption_flag%TYPE ;
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
719: -- ln_gst_assessable_value JAI_AR_TRX_LINES.gst_assessable_value%TYPE ;--Added by Xiao Lv for bug 10043656 16470035
720: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
721: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
714: lv_vat_exemption_flag JAI_AR_TRX_LINES.vat_exemption_flag%TYPE ;
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
719: -- ln_gst_assessable_value JAI_AR_TRX_LINES.gst_assessable_value%TYPE ;--Added by Xiao Lv for bug 10043656 16470035
720: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
721: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
722: -- ended, Harshita for bug#4245062
715: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
716: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
717: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
718: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
719: -- ln_gst_assessable_value JAI_AR_TRX_LINES.gst_assessable_value%TYPE ;--Added by Xiao Lv for bug 10043656 16470035
720: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
721: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
722: -- ended, Harshita for bug#4245062
723:
729: v_service_type VARCHAR2(30); -- added by csahoo for bug#5879769
730:
731: --Added by Bo Li for bug#10091373 Begin
732: -------------------------------------------
733: --lv_gst_invoice_no JAI_AR_TRX_LINES.gst_invoice_no %TYPE ;16470035
734: --ld_gst_invoice_date JAI_AR_TRX_LINES.gst_invoice_date %TYPE ;16470035
735: --------------------------------------------
736: --Added by Bo Li for bug#10091373 Begin
737:
730:
731: --Added by Bo Li for bug#10091373 Begin
732: -------------------------------------------
733: --lv_gst_invoice_no JAI_AR_TRX_LINES.gst_invoice_no %TYPE ;16470035
734: --ld_gst_invoice_date JAI_AR_TRX_LINES.gst_invoice_date %TYPE ;16470035
735: --------------------------------------------
736: --Added by Bo Li for bug#10091373 Begin
737:
738: -- Date 26-feb-2006 added by sacsethi for bug 5631784
1159:
1160: --ADDED TO CHECK DUPLICATE ENTRY FOR LINES 01-DEC-99 GAURAV.
1161: CURSOR DUPLICATE_LINES_CUR IS
1162: SELECT 1
1163: FROM JAI_AR_TRX_LINES
1164: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
1165:
1166: CURSOR OLD_CUSTOMER_TRX_ID_CUR IS
1167: SELECT Customer_Trx_Id
1164: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
1165:
1166: CURSOR OLD_CUSTOMER_TRX_ID_CUR IS
1167: SELECT Customer_Trx_Id
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
1362: v_ato_line_amount Number;
1363: v_ato_tax_amount Number;
1364: v_ato_total_amount Number;
1365: -- v_ar3_form_no Number;
1366: v_ar3_form_no JAI_AR_TRX_LINES.ar3_form_no%TYPE; -- Vijay shankar for Bug # 3181892
1367: v_ar3_form_date Date;
1368:
1369: /*
1370: start additions by sriram - bug# 3607101
1804: NVL(r_add.SHIP_TO_SITE_USE_ID, r_add.BILL_TO_SITE_USE_ID),'C');
1805:
1806: for so_line_rec in c_get_om_lines(v_config_line_id)
1807: Loop
1808: Insert into JAI_AR_TRX_LINES
1809: (
1810: customer_trx_line_id ,
1811: customer_trx_id ,
1812: line_number ,
1879: );
1880:
1881: FND_FILE.PUT_LINE(FND_FILE.LOG,' Cursor . c_get_om_lines ');
1882: FND_FILE.PUT_LINE(FND_FILE.LOG,
1883: ' Inserted jai_ar_trx_lines for TRX LINE ID: '||v_customer_trx_line_id);
1884: FND_FILE.PUT_LINE(FND_FILE.LOG,' Trx id: ' || v_header_id);
1885:
1886: v_ato_line_amount := so_line_rec.line_amount;
1887: v_ato_tax_amount := NVL(v_ato_tax_amount,0) + NVL(v_tax_amount,0);
2030: WHERE line_id = TO_NUMBER(v_interface_line_attribute6);
2031:
2032: cursor c_get_amounts is
2033: select sum(tax_amount) tax_amt , sum(line_amount) line_amt
2034: from JAI_AR_TRX_LINES
2035: where customer_trx_id = pr_new.customer_trx_id;
2036:
2037: cursor c_default_location(CP_ORGANIZATION_ID number) is
2038: select DEFAULT_LOCATION_BILL_ONLY
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:
2516: /*added by csahoo for bug#6498072, end*/
2517:
2518: /*moved the following code here for bug# 6498072*/
2519:
2520: INSERT INTO JAI_AR_TRX_LINES (
2521: customer_trx_line_id ,
2522: line_number ,
2523: customer_trx_id ,
2524: description ,
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
2678: For a Invoice line imported from OM , the interface_line_attribute11 will have a value 0 or Null ,
2679: whereas for a discount line , the interface_line_attribute11 will have a value which maps to the price_adjustment_id.
2680:
2681: 13. 2003/02/07 Vijay Shankar - Bug # 2779990 - Version is 615.4
2682: When bond transaction invoice is created, then tax_amounts populated in JAI_AR_TRXS and JAI_AR_TRX_LINES is wrong.
2683: this is rectified by writing a new cursor name c_tax_amount
2684:
2685: 14. 2003/03/17 Sriram LMW ATO Issue - Bug #2806274 Version 615.5
2686: Created an internal procedure Process_Taxes_for_ATO_Order to pull taxes from OM to Ar for ATO Orders.
2686: Created an internal procedure Process_Taxes_for_ATO_Order to pull taxes from OM to Ar for ATO Orders.
2687: The idea was to import the taxes for the 'Model' Item based on the config item.
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
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
2708: This is causing the unique constraint violation error. This has been resolved by making a change to ensure that
2709: entries in the ST forms table needs to go only if the St form Type is not null and tax type is 'Sales Tax' or 'CST'.
2710:
2711: 18 2003/10/10 Vijay shankar - Bug # 3181892, Version 616.4
2712: AR3 form number and date values are not populated into JAI_AR_TRX_LINES table which is resolved with this fix
2713:
2714: 19 2003/12/30 Aiyer - Bug #3328871, Version 618.1
2715:
2716: Issue: -
2715:
2716: Issue: -
2717: Create an order such that it does not have any associated taxes.
2718: Ship this order and run the auto invoice program.
2719: It is found that no records are inserted in the table JAI_AR_TRX_LINES.
2720:
2721: Solution: -
2722: This was happening because the code was assuming that a line cannot exist without any taxes.
2723: So in such a case the control was made to return back without inserting a line in JAI_AR_TRX_LINES
2719: It is found that no records are inserted in the table JAI_AR_TRX_LINES.
2720:
2721: Solution: -
2722: This was happening because the code was assuming that a line cannot exist without any taxes.
2723: So in such a case the control was made to return back without inserting a line in JAI_AR_TRX_LINES
2724: table.
2725:
2726: Fix Details:-
2727: Added a if clause in the code. Placed the return statement between the if clause.
2730: IF yes then do not retunr, if not found then return.
2731: Also added a nvl clause in the cursor c_cust_trx_tax_line_amt. so that a value of zero would be returned
2732: even if the where clause failed to fetch a record.
2733: This would take care that tax amount would be inserted as 0 and total amount would also be computed properly
2734: in table JAI_AR_TRX_LINES.
2735: This issue was reported thorugh the bug 3344492.
2736: Also indented the whole code and added additional comments wereever necessary.
2737: Removed the commented code wherever applicable.
2738:
2739: Dependency Introduced Due To This Bug:-
2740: None
2741: 20 2004/02/16 RBASKER - Bug 3357587, Version 618.2
2742: When SO UOM is different from that of the primary UOM,
2743: JAI_AR_TRX_LINES is updated with wrong assessable value
2744: Added a cursor C_JA_SO_LINES_ASSESSABLE_VAL to fetch the correct assessable_value
2745: from JAI_OM_OE_SO_LINES instead of JAI_OM_WSH_LINES_ALL table.
2746:
2747: 21. 2004/05/05 ssumaith - bug# 3607101 - Version 619.1
2782:
2783: 26. 17-Mar-2005 hjujjuru - bug #4245062 File version 115.6
2784: The columns vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno,
2785: and vat_assessable_value have been added in JAI_OM_OE_SO_LINES, JAI_OM_WSH_LINES_ALL
2786: and JAI_AR_TRX_LINES.
2787: Additional fields vat_invoice_no and vat_invoice_date have been added into JAI_OM_WSH_LINES_ALL
2788: and JAI_AR_TRXS.
2789: The trigger has been updated to ensure that any data flowing into JAI_AR_TRX_LINES and
2790: JAI_AR_TRXS also includes the Vat information that comes down from either JAI_OM_OE_SO_LINES
2785: and vat_assessable_value have been added in JAI_OM_OE_SO_LINES, JAI_OM_WSH_LINES_ALL
2786: and JAI_AR_TRX_LINES.
2787: Additional fields vat_invoice_no and vat_invoice_date have been added into JAI_OM_WSH_LINES_ALL
2788: and JAI_AR_TRXS.
2789: The trigger has been updated to ensure that any data flowing into JAI_AR_TRX_LINES and
2790: JAI_AR_TRXS also includes the Vat information that comes down from either JAI_OM_OE_SO_LINES
2791: or JAI_OM_WSH_LINES_ALL .
2792:
2793: Base bug - #4245089
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 )
2858: Issue: AR Autoinvoice completes in Error: ORA-20130: ORGANIZATION CANNOT BE NULL
2863: Fix: Added logic to extract organization and location for OPTION items
2864:
2865: 38. 22-Nov-2008 JMEENA for bug#6391684( FP of 6061010 and 6386592)
2866: 1.Issue: INDIA LOCAL-CTO ITEM-EXCISE INV,PLA REG NOT UPDATED, AR TAXES NOT GENERATED
2867: Fix: When a model line is being inserted into table JAI_AR_TRX_LINES,
2868: trigger JAI_AR_RCTA_ARIUD_T1 is invoked for MODEL item. By this
2869: time, a record is available in table JAI_AR_TRXS but without
2870: organization and location values. Hence, added the code to update the organization_id
2871: and location_id in the JAI_AR_TRXS table
2871: and location_id in the JAI_AR_TRXS table
2872:
2873: 2. Issue: AUTOINVOICE FOR CERTAIN CTO SALES ORDERS GOING INTO ERRORS
2874: Reason: Code to insert an header record into table JAI_AR_TRXS was present after
2875: insertion of lines in table JAI_AR_TRX_LINES. Trigger
2876: JAI_AR_RCTA_ARIUD_T1 was fired before the header record is present in
2877: IL header table JAI_AR_TRXS. Hence the AutoInvoice goes into error.
2878: Fix: Modified the code in internal procedure process_taxes_for_ato_order of
2879: proceudre JAI_AR_RCTLA_TRIGGER_PKG.ARI_T2.
2887: from so_ato_picking_hdr_info_1
2888:
2889: 40 30-APR-2009 JMEENA for bug#8466638
2890: Issue: Service type is not flowing OM to AR
2891: Fix: Added column Service_type_code and its value in the insert of JAI_AR_TRX_LINES.
2892:
2893: 41. 04-JUN-2009 JMEENA for bug#5641896 (FP of 5639516 )
2894: Issue: AutoInvoice Import Program (RAXTRX) is running slow.
2895: Fix: Removed to_char() from to_char(line_id) in so_rma_hdr_info cursor definition.
3405:
3406: v_service_type:=get_service_type( NVL(r_add.SHIP_TO_CUSTOMER_ID ,r_add.BILL_TO_CUSTOMER_ID) ,
3407: NVL(r_add.SHIP_TO_SITE_USE_ID, r_add.BILL_TO_SITE_USE_ID),'C');
3408:
3409: INSERT INTO JAI_AR_TRX_LINES (
3410: customer_trx_line_id ,
3411: line_number ,
3412: customer_trx_id ,
3413: description ,
3636: END LOOP;
3637: END IF;
3638: END IF;
3639:
3640: UPDATE JAI_AR_TRX_LINES
3641: SET payment_register = v_reg_type,
3642: excise_invoice_no = v_exc_invoice_no,
3643: excise_invoice_date = trunc(sysdate)
3644: WHERE
4278: fetch JA_SO_LINES_INFO into rec_so_lines;
4279: close JA_SO_LINES_INFO;
4280: --End bug#8466638
4281:
4282: INSERT INTO JAI_AR_TRX_LINES (
4283: customer_trx_line_id ,
4284: line_number ,
4285: customer_trx_id ,
4286: description ,
4378: --DELETE JAI_AR_TRX_INS_LINES_T
4379: --WHERE customer_trx_line_id = v_customer_trx_line_id;
4380:
4381: IF v_old_customer_trx_id <> v_header_id THEN
4382: UPDATE JAI_AR_TRX_LINES
4383: SET customer_trx_id = v_header_id
4384: WHERE customer_trx_line_id = v_customer_trx_line_id;
4385: DELETE JAI_AR_TRXS
4386: WHERE customer_trx_id = v_old_customer_trx_id;
4540: v_line_count NUMBER := 0; -- added on 14-jun-01 by subbu
4541: ln_legal_entity_id NUMBER ; /* rallamse bug#4448789 */
4542: v_service_type VARCHAR2(30); -- added by csahoo for bug#5879769
4543:
4544: -- cusrsor added to check duplication in JAI_AR_TRX_LINES for avoiding
4545: -- unique constraint violation
4546:
4547: CURSOR DUPLICATE_LINES_CUR IS
4548: SELECT 1
4545: -- unique constraint violation
4546:
4547: CURSOR DUPLICATE_LINES_CUR IS
4548: SELECT 1
4549: FROM JAI_AR_TRX_LINES
4550: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
4551:
4552: CURSOR OLD_CUSTOMER_TRX_ID_CUR IS
4553: SELECT Customer_Trx_Id
4550: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
4551:
4552: CURSOR OLD_CUSTOMER_TRX_ID_CUR IS
4553: SELECT Customer_Trx_Id
4554: FROM JAI_AR_TRX_LINES
4555: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
4556:
4557: CURSOR CREATED_FROM_CUR IS
4558: SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
4762: ORDER BY tax_line_no;
4763:
4764: cursor c_get_amounts is
4765: select sum(tax_amount) tax_amt , sum(line_amount) line_amt
4766: from JAI_AR_TRX_LINES
4767: where customer_trx_id = pr_new.customer_trx_id;
4768:
4769: cursor c_default_location(CP_ORGANIZATION_ID number) is
4770: select DEFAULT_LOCATION_BILL_ONLY
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:
5244: /*added by csahoo for bug#6498072, end*/
5245:
5246: /*moved the following code here for bug# 6498072*/
5247:
5248: INSERT INTO JAI_AR_TRX_LINES (
5249: customer_trx_line_id,
5250: line_number,
5251: customer_trx_id,
5252: description,
5407: The legal_entity_id is derived from CREATED_FROM_CUR cursor based on ra_customer_trx_all.
5408:
5409: 14. 24-Aug-2005 Ramananda for bug #4567935 (115 bug 4404898, 4395450). File version 120.2
5410: Issue:-
5411: 1. The following type of taxes should not be inserted into JAI_AR_TRX_LINES when Order type is BOND register:-
5412: 'EXCISE', 'OTHER EXCISE', 'CVD_EDUCATION_CESS', 'EXCISE_EDUCATION_CESS'
5413:
5414: 2. Trigger currently processing Credit memo invoices with interface line context as LEGACY.This needs to be stopped
5415:
5413:
5414: 2. Trigger currently processing Credit memo invoices with interface line context as LEGACY.This needs to be stopped
5415:
5416: Fix:-
5417: 1. Before a insert in the table JAI_AR_TRX_LINES, added a IF statement to bypass the insert into JAI_AR_TRX_LINES if
5418: the register code is 'BOND_REG' and any of the above stated taxes are present.
5419: 2. Added the check to RETURN from the trigger if interface line context = 'LEGACY'
5420:
5421: Dependency due to this bug :-
5832: v_tax_amount :=0;
5833: END IF;
5834:
5835:
5836: INSERT INTO JAI_AR_TRX_LINES
5837: (customer_trx_line_id,
5838: line_number,
5839: customer_trx_id,
5840: description,
5885: DELETE JAI_AR_TRX_INS_LINES_T
5886: WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
5887:
5888: IF v_old_customer_trx_id <> v_header_id THEN
5889: UPDATE JAI_AR_TRX_LINES
5890: SET Customer_Trx_Id = v_header_id
5891: WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
5892:
5893: DELETE JAI_AR_TRXS
6006: WHERE customer_trx_id = v_header_id;
6007:
6008: CURSOR DUPLICATE_LINES_CUR IS
6009: SELECT 1
6010: FROM JAI_AR_TRX_LINES
6011: WHERE customer_trx_LINE_id = v_customer_trx_line_id;
6012:
6013:
6014: v_trans_type VARCHAR2(30);
6134: END IF;
6135:
6136: IF NVL(ln_line_exist,0) <> 1 THEN
6137:
6138: INSERT INTO JAI_AR_TRX_LINES
6139: (customer_trx_line_id,
6140: line_number,
6141: customer_trx_id,
6142: description,
6397: WHERE customer_trx_id = v_header_id;
6398:
6399: CURSOR old_line_amount_cur IS
6400: SELECT line_amount
6401: FROM JAI_AR_TRX_LINES
6402: WHERE CUSTOMER_TRX_ID = pr_old.CUSTOMER_TRX_ID
6403: AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
6404:
6405: CURSOR gl_date_cur IS
6414: Where a.cust_trx_type_id = b.cust_trx_type_id
6415: And b.customer_trx_id = v_header_id
6416: And NVL(a.org_id,0) = NVL(pr_new.org_id,0);
6417:
6418: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
6419: --ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;--Added by Xiao Lv for bug 10091373
6420:
6421: /* Added by Ramananda as a part of removal of SQL LITERALs , start */
6422: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type ;
6415: And b.customer_trx_id = v_header_id
6416: And NVL(a.org_id,0) = NVL(pr_new.org_id,0);
6417:
6418: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
6419: --ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;--Added by Xiao Lv for bug 10091373
6420:
6421: /* Added by Ramananda as a part of removal of SQL LITERALs , start */
6422: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type ;
6423: lv_add_err JAI_CMN_ERRORS_T.additional_error_mesg%type ;
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
6619: AND CUSTOMER_TRX_LINE_ID = pr_old.CUSTOMER_TRX_LINE_ID;
6620: END IF;
6621:
6893:
6894: Open Gl_Date_Cur;
6895: Fetch Gl_Date_Cur Into v_gl_date;
6896: Close Gl_Date_Cur;
6897: INSERT INTO JAI_AR_TRX_LINES(
6898: customer_trx_line_id,
6899: line_number,
6900: customer_trx_id,
6901: description,
7278: last_updated_by = v_last_updated_by,
7279: last_update_login = v_last_update_login
7280: WHERE customer_trx_id = v_header_id;
7281:
7282: UPDATE JAI_AR_TRX_LINES
7283: SET description = pr_new.description,
7284: inventory_item_id = pr_new.inventory_item_id,
7285: unit_code = pr_new.uom_code,
7286: quantity = pr_new.quantity_credited,
7440: Removed the reference to cursor set_of_books_cur
7441: as the SOB will never be null in base table.
7442: */
7443:
7444: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
7445: --ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;--Added by Xiao Lv for bug 10091373
7446: ld_trx_date DATE;
7447:
7448: BEGIN
7441: as the SOB will never be null in base table.
7442: */
7443:
7444: ln_vat_assessable_value JAI_AR_TRX_LINES.VAT_ASSESSABLE_VALUE%TYPE;
7445: --ln_gst_assessable_value JAI_AR_TRX_LINES.GST_ASSESSABLE_VALUE%TYPE;--Added by Xiao Lv for bug 10091373
7446: ld_trx_date DATE;
7447:
7448: BEGIN
7449: pv_return_code := jai_constants.successful ;
7522:
7523: 11. 18-Nov-2008 JMEENA for bug#6414523
7524: Issue: When selling price is changed, Excise Assessable Value should be updated correctly
7525: Reason: Excise Assessable value is derived but not updated in the table ja_in_ra_customer_trx_lines
7526: Fix: Modifed procedure ARU_T2. Added column assessable_value in the update statement of table JAI_AR_TRX_LINES.
7527:
7528: 12. 20-JUL-2009 JMEENA For bug#8441899
7529: Added column vat_assessable_value in the update of table JAI_AR_TRX_LINES to update the New VAT Assessable
7530: value in the table if Quantity or UOM is changed.
7525: Reason: Excise Assessable value is derived but not updated in the table ja_in_ra_customer_trx_lines
7526: Fix: Modifed procedure ARU_T2. Added column assessable_value in the update statement of table JAI_AR_TRX_LINES.
7527:
7528: 12. 20-JUL-2009 JMEENA For bug#8441899
7529: Added column vat_assessable_value in the update of table JAI_AR_TRX_LINES to update the New VAT Assessable
7530: value in the table if Quantity or UOM is changed.
7531:
7532: Future Dependencies For the release Of this Object:-
7533: (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
7793: last_updated_by = v_last_updated_by,
7794: last_update_login = v_last_update_login
7795: WHERE customer_trx_id = v_header_id;
7796:
7797: UPDATE JAI_AR_TRX_LINES
7798: SET description = pr_new.description,
7799: inventory_item_id = pr_new.inventory_item_id,
7800: unit_code = pr_new.uom_code,
7801: quantity = v_new_quantity,
7847: AND b.customer_trx_id = r_new.customer_trx_id
7848: AND NVL(A.org_id,0) = NVL(r_new.org_id,0);
7849:
7850: r_trx_type c_transaction_type%rowtype; --Bug 6369471
7851: ln_quantity jai_ar_trx_lines.quantity%type; -- Bug 6369471
7852:
7853:
7854: cursor c_ra_customer_trx is
7855: select trx_number, trx_date, batch_source_id, set_of_books_id
7867: r_jai_ra_customer_trx c_jai_ra_customer_trx%rowtype;
7868:
7869: cursor c_jai_ra_customer_trx_line is
7870: select customer_trx_line_id
7871: from jai_ar_trx_lines
7872: where customer_trx_line_id = r_new.customer_trx_line_id;
7873: r_jai_ra_customer_trx_line c_jai_ra_customer_trx_line%rowtype;
7874:
7875: cursor c_get_project_id(cp_project_code in varchar2) is
8085: ln_line_tax_amt := nvl(ln_line_tax_amt,0) + ln_tax_amount;
8086:
8087: end loop;
8088:
8089: /* insert into jai_ar_trx_lines */
8090: open c_jai_ra_customer_trx_line;
8091: fetch c_jai_ra_customer_trx_line into r_jai_ra_customer_trx_line;
8092: close c_jai_ra_customer_trx_line;
8093:
8091: fetch c_jai_ra_customer_trx_line into r_jai_ra_customer_trx_line;
8092: close c_jai_ra_customer_trx_line;
8093:
8094: if r_jai_ra_customer_trx_line.customer_trx_line_id is null then
8095: INSERT INTO jai_ar_trx_lines (
8096: customer_trx_line_id ,
8097: line_number ,
8098: customer_trx_id ,
8099: description ,