[Home] [Help]
120: CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
121:
122: CURSOR ONCE_COMPLETE_FLAG_CUR IS
123: SELECT once_completed_flag
124: FROM JAI_AR_TRXS
125: WHERE customer_trx_id = v_customer_trx_id;
126:
127: -- following segment uncommented by sriram - bug # 2618503
128: v_trans_type Varchar2(30);
176: S.No. Description
177:
178: 1 Sriram - Bug # 2590650 - Added delete statements to delete from the
179: JAI_AR_TRX_INS_LINES_T for customer trx id and trx line id and also the
180: JAI_AR_TRXS table
181:
182: 2 Sriram - Bug # 2618503
183: If the line being deleted corresponds to a return order or a sales order then
184: delete the lines related to localization ar tables.
253: CLOSE old_line_amount_cur;
254:
255:
256: --start additions for bug#7539258
257: UPDATE JAI_AR_TRXS
258: SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
259: tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
260: total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
261: WHERE customer_trx_id = v_customer_trx_id;
262:
263: --end additions for bug#7539258
264:
265: DELETE FROM
266: JAI_AR_TRXS trx
267: WHERE customer_trx_id = v_customer_trx_id
268: AND EXISTS
269: (SELECT 1
270: FROM ra_interface_lines_all il
384: WHERE customer_trx_id = v_header_id;
385:
386: Cursor localization_header_info_cur IS
387: SELECT organization_id
388: FROM JAI_AR_TRXS
389: WHERE customer_trx_id = v_header_id;
390:
391: /* Bug 5243532. Added by Lakshmi Gopalsami
392: Removed the cursors Fetch_Book_Id_cur and set_of_books_cur
552: v_last_updated_by,
553: v_last_update_login,
554: lv_service_type_code -- added by csahoo for bug#5879769
555: );
556: Update JAI_AR_TRXS
557: Set line_amount = nvl(line_amount,0) + nvl(v_line_amount,0)
558: Where Customer_Trx_Id = v_header_id;
559:
560: ELSIF v_line_type in ('FREIGHT','TAX') THEN
617: v_last_update_login
618: );
619:
620: IF rec.tax_type in ('Excise','Addl. Excise','Other Excise') THEN
621: Update JAI_AR_TRXS
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;
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:
724: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%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:
724: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type;
725: lv_err_msg JAI_CMN_ERRORS_T.error_message%type;
829:
830: CURSOR JA_IN_RA_CUSTOMER_TRX_INFO IS
831: SELECT organization_id,
832: Location_id
833: FROM JAI_AR_TRXS
834: WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4); -- Replaced pr_new.interface_line_attribute1 , Bug 4392001
835: CURSOR JA_IN_RA_CUSTOMER_TRX_INFOCNSL IS
836: SELECT A.organization_id,
837: A.Location_id
834: WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4); -- Replaced pr_new.interface_line_attribute1 , Bug 4392001
835: CURSOR JA_IN_RA_CUSTOMER_TRX_INFOCNSL IS
836: SELECT A.organization_id,
837: A.Location_id
838: FROM JAI_AR_TRXS A,
839: JAI_AR_SUP_HDRS_ALL B,
840: RA_CUSTOMER_TRX_ALL C
841: WHERE pr_new.customer_trx_id = c.customer_trx_id
842: AND c.trx_number = TO_CHAR(B.SUPPLEMENTARY_NUM)
1021: t.stform_type ;
1022:
1023: CURSOR DUPLICATE_HDR_CUR IS
1024: SELECT 1
1025: FROM JAI_AR_TRXS
1026: WHERE customer_trx_id = v_header_id;
1027:
1028: CURSOR ONCE_COMPLETE_FLAG_CUR IS
1029: SELECT once_completed_flag
1026: WHERE customer_trx_id = v_header_id;
1027:
1028: CURSOR ONCE_COMPLETE_FLAG_CUR IS
1029: SELECT once_completed_flag
1030: FROM JAI_AR_TRXS
1031: WHERE customer_trx_id = v_header_id;
1032: /* Cursor To Check Whether Interfaced Record Exist in JAI_OM_WSH_LINES_ALL */
1033:
1034: CURSOR SO_PICKING_RECORD_CHECK IS
1692: FND_FILE.PUT_LINE(FND_FILE.LOG,'ATO: 2. v_organization_id , v_location_id: ' || v_organization_id || ' , ' || v_location_id );
1693:
1694: END IF ;
1695:
1696: update JAI_AR_TRXS
1697: set organization_id = nvl(organization_id, v_organization_id)
1698: , location_id = nvl(location_id, v_location_id)
1699: , last_update_date = sysdate
1700: where customer_trx_id = v_header_id;
1734: FND_FILE.PUT_LINE(FND_FILE.LOG,' ATO: Loc id: '|| v_location_id);
1735: END IF ;
1736:
1737:
1738: Insert into JAI_AR_TRXS
1739: (
1740: CUSTOMER_TRX_ID ,
1741: ORGANIZATION_ID ,
1742: LOCATION_ID ,
1789: 'Y',
1790: ln_legal_entity_id /* rallamse bug#4448789 */
1791: );
1792:
1793: FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
1794: /* Added by JMEENA for bug# 6391684( FP of 6386592), Ends */
1795: end if;
1796: /* JMEENA for bug#6391684( FP of 6061010), Ends */
1797:
1890: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Ato tot amount: '||v_ato_total_amount);
1891: end loop;
1892:
1893:
1894: -- creation of record in JAI_AR_TRXS
1895: /* Commented for bug# 6391684 (Fp of 6386592), Starts */
1896: /*OPEN CREATED_FROM_CUR;
1897: FETCH CREATED_FROM_CUR INTO v_created_from, v_trx_number, v_batch_source_id, v_books_id,
1898: v_salesrep_id, c_from_currency_code, c_conversion_type,
1917: FND_FILE.PUT_LINE(FND_FILE.LOG,' Loc id: '||v_location_id);
1918:
1919: ln_legal_entity_id := get_legal_entity_id ;
1920:
1921: Insert into JAI_AR_TRXS
1922: (
1923: CUSTOMER_TRX_ID ,
1924: ORGANIZATION_ID ,
1925: LOCATION_ID ,
1972: 'Y',
1973: ln_legal_entity_id -- rallamse bug#4448789
1974: );
1975:
1976: FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
1977: */ --End bug#6391684( FP of 6386592)
1978: FND_FILE.PUT_LINE(FND_FILE.LOG,
1979: 'End of Procedure Process_Taxes_for_ATO_Order');
1980:
1989: Start additions by ssumaith for bug# 4136981*/
1990: procedure process_bill_only_invoice is
1991: cursor c_check_hdr_exists is
1992: select 1
1993: from JAI_AR_TRXS
1994: where customer_trx_id = pr_new.customer_trx_id;
1995:
1996:
1997: cursor c_trx_cur is
2107: Validations to handle
2108: ---------------------
2109: a) in case of discounts only one line should have taxes
2110: b) in case of bond register excise and excise cess taxes should not flow into AR
2111: c) only one record should be inserted in the JAI_AR_TRXS table.
2112: */
2113:
2114: ln_hdr_exists := 0;
2115: open c_check_hdr_exists;
2119: FND_FILE.PUT_LINE(FND_FILE.LOG,' Header exists '|| ln_hdr_exists);
2120:
2121: if nvl(ln_hdr_exists,0) = 0 then
2122: /*
2123: no record exists in the JAI_AR_TRXS table for the customer trx id ,
2124: hence insert a record into the table.
2125: */
2126:
2127: open c_trx_cur;
2148:
2149: /*
2150: if the default location setup is not done , log this as a message in the JAI_CMN_ERRORS_T table
2151: do not stop the processing.
2152: We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
2153: */
2154: lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
2155: lv_err_msg := 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id ;
2156: lv_addl_msg := 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id ;
2180: end if;
2181:
2182: ln_legal_entity_id := get_legal_entity_id ;
2183:
2184: insert into JAI_AR_TRXS -- bill only invoice
2185: (
2186: CUSTOMER_TRX_ID ,
2187: ORGANIZATION_ID ,
2188: LOCATION_ID ,
2238: ln_legal_entity_id /* rallamse bug#4448789 */
2239: );
2240:
2241: FND_FILE.PUT_LINE(FND_FILE.LOG,
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.
2598: FND_FILE.PUT_LINE(FND_FILE.LOG,' Cursor c_get_amounts');
2599: FND_FILE.PUT_LINE(FND_FILE.LOG,' Tax amount '|| ln_tax_amount);
2600: FND_FILE.PUT_LINE(FND_FILE.LOG,' Line amount '|| ln_line_amount);
2601:
2602: update JAI_AR_TRXS
2603: set tax_amount = ln_tax_amount ,
2604: line_amount = ln_line_amount,
2605: total_amount = ln_line_amount + ln_tax_amount
2606: where customer_trx_id = pr_new.customer_trx_id;
2651: when the manual delivery is made and when the delivery name is alphanumeric.
2652: When the order created is assigned to this delivery and the ship confimation is
2653: done ,then the autoinvoice import program is running into error because the
2654: the validation is failing for delivery_id and interface_line_attribute3
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
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
2664: flowing correctly.
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
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
2697:
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
2791: or JAI_OM_WSH_LINES_ALL .
2792:
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
2794:
2819: 29 10-Jun-2005 File Version: 116.3
2820: Removal of SQL LITERALs is done
2821:
2822: 30 10-Jun-2005 rallamse bug#4448789 116.3
2823: Added legal_entity_id for table JAI_AR_TRXS in insert statement
2824:
2825: 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
2826: GL Sources and GL Categories got changed. Refer bug for the details
2827:
2830:
2831: 32 26-Jul-2005 rallamse for bug#4510143 File Version 120.2
2832: Changed the legal_entity_id to get from function get_legal_entity_id.
2833: Replaced legal_entity_id with ln_legal_entity_id( holds value of get_legal_entity_id )
2834: for table JAI_AR_TRXS in insert statement.
2835:
2836: 33. 24-Aug-2005 Ramananda for bug #4567935 (115 bug 4404898). File version 120.2
2837: Issue:-
2838: 1. Trigger currently processes invoices with interface line context as LEGACY.This needs to be stopped
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
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
2872:
2873: 2. Issue: AUTOINVOICE FOR CERTAIN CTO SALES ORDERS GOING INTO ERRORS
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
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
2870: organization and location values. Hence, added the code to update the organization_id
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
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.
2880: Moved the insertion code into table JAI_AR_TRXS before
2881: the insertion of lines into IL taxes table.
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.
2880: Moved the insertion code into table JAI_AR_TRXS before
2881: the insertion of lines into IL taxes table.
2882:
2883: 39. 08-DEC-2008 JMEENA For Bug#5684033
2884: Issue: AUTOINVOICE GOES IN ERROR FOR PTO ORDERS
3144: FETCH ja_in_ra_customer_trx_info INTO v_organization_id, v_location_id;
3145: CLOSE ja_in_ra_customer_trx_info;
3146: END IF;
3147:
3148: INSERT INTO JAI_AR_TRXS ( -- supplement
3149: customer_trx_id ,
3150: organization_id ,
3151: location_id ,
3152: trx_number ,
3188: v_last_updated_by ,
3189: v_last_update_login ,
3190: ln_legal_entity_id /* rallamse bug#4448789 */
3191: );
3192: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inside x <> 1: After insert into JAI_AR_TRXS');
3193: END IF;
3194:
3195: IF pr_new.interface_line_attribute4 IS NULL THEN -- pr_new.interface_line_attribute1, Bug# 4392001
3196: OPEN supplement_tax_lines_check_cns;
3450: v_last_update_login,
3451: v_service_type --Added by JMEENA for bug#8466638
3452: );
3453:
3454: UPDATE JAI_AR_TRXS
3455: SET
3456: line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
3457: once_completed_flag = NVL(v_once_completed_flag,'N')
3458: WHERE
3866: /*
3867: ends here -- bug# 3607101
3868: */
3869:
3870: INSERT INTO JAI_AR_TRXS
3871: (Customer_Trx_ID, Organization_ID, Location_ID, Trx_Number,
3872: Update_RG_Flag,UPDATE_RG23D_FLAG, Once_Completed_Flag, Batch_Source_ID, Set_Of_Books_ID,
3873: Primary_Salesrep_ID, Invoice_Currency_Code, Exchange_Rate_Type,
3874: Exchange_Date, Exchange_Rate,
3903:
3904: FOR tax_rec IN so_picking_tax_lines_info LOOP
3905: /*IF ln_first_time = 0 THEN
3906: ln_first_time := ln_first_time + 1;
3907: UPDATE JAI_AR_TRXS
3908: SET vat_invoice_no = tax_rec.vat_invoice_no,
3909: vat_invoice_date = tax_rec.vat_invoice_date
3910: WHERE customer_trx_id = pr_new.customer_trx_id;
3911: END IF;*/
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;
4387: END IF;
4388: END IF; /* END IF OF IF NVL(v_line_count,0) <> 1 */
4389:
4425: -- Above if condition added by sriram Bug # 2742849
4426: -- instead of the previous if condition because , it may fail in circumstances
4427: -- where line ordering rules are set up in autoinvoice options.
4428:
4429: UPDATE JAI_AR_TRXS
4430: SET line_amount = round(NVL(line_amount,0) + NVL(v_line_amount,0),2),
4431: tax_amount = round(NVL(tax_amount,0) + NVL(v_so_tax_amount,0),2),
4432: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_so_tax_amount,0),2),
4433: once_completed_flag = NVL(v_once_completed_flag,'N'),
4434: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
4435: -- , gst_invoice_no = lv_gst_invoice_no, gst_invoice_date = ld_gst_invoice_date -- Added by Bo Li for bug#10091373
4436: WHERE customer_trx_id = v_header_id;
4437: ELSE
4438: UPDATE JAI_AR_TRXS
4439: SET line_amount = round(NVL(line_amount, 0 ) + NVL(v_line_amount,0),2),
4440: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0),2),
4441: once_completed_flag = NVL(v_once_completed_flag,'N'),
4442: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
4616:
4617:
4618: CURSOR DUPLICATE_HDR_CUR IS
4619: SELECT 1
4620: FROM JAI_AR_TRXS
4621: WHERE customer_trx_id = v_header_id;
4622:
4623:
4624: /* Cursor To Check Whether Interfaced Record Exist in JAI_OM_WSH_LINES_ALL */
4725:
4726: procedure process_bill_only_return is
4727: cursor c_check_hdr_exists is
4728: select 1
4729: from JAI_AR_TRXS
4730: where customer_trx_id = pr_new.customer_trx_id;
4731:
4732: cursor c_trx_cur is
4733: select trx_number ,
4835: Validations to handle
4836: ---------------------
4837: a) in case of discounts only one line should have taxes
4838: b) in case of bond register excise and excise cess taxes should not flow into AR
4839: c) only one record should be inserted in the JAI_AR_TRXS table.
4840: */
4841:
4842: ln_hdr_exists := 0;
4843: open c_check_hdr_exists;
4847: FND_FILE.PUT_LINE(FND_FILE.LOG,' Header exists '|| ln_hdr_exists);
4848:
4849: if nvl(ln_hdr_exists,0) = 0 then
4850: /*
4851: no record exists in the JAI_AR_TRXS table for the customer trx id ,
4852: hence insert a record into the table.
4853: */
4854:
4855: open c_trx_cur;
4876:
4877: /*
4878: if the default location setup is not done , log this as a message in the JAI_CMN_ERRORS_T table
4879: do not stop the processing.
4880: We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
4881: */
4882: lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
4883: lv_err_msg := 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id ;
4884: lv_addl_msg := 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id ;
4905: fnd_global.user_id,
4906: sysdate
4907: );
4908: end if;
4909: insert into JAI_AR_TRXS -- bill only invoice
4910: (
4911: CUSTOMER_TRX_ID ,
4912: ORGANIZATION_ID ,
4913: LOCATION_ID ,
4962: NULL ,
4963: ln_legal_entity_id /* rallamse bug#4448789 */
4964: );
4965: FND_FILE.PUT_LINE(FND_FILE.LOG,
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.
5297: FND_FILE.PUT_LINE(FND_FILE.LOG,' Cursor c_get_amounts');
5298: FND_FILE.PUT_LINE(FND_FILE.LOG,' Tax amount '|| ln_tax_amount);
5299: FND_FILE.PUT_LINE(FND_FILE.LOG,' Line amount '|| ln_line_amount);
5300:
5301: update JAI_AR_TRXS
5302: set tax_amount = nvl(ln_tax_amount,0) ,
5303: line_amount = nvl(ln_line_amount,0),
5304: total_amount = nvl(ln_line_amount,0) + nvl(ln_tax_amount,0)
5305: where customer_trx_id = pr_new.customer_trx_id;
5358: 6. 2003/01/18 Sriram - Bug # 2755890 - File Version 615.3
5359: When a credit memo created in AR because of a RMA Transaction is queried
5360: in the localization AR screen , it was causing no reords to be retreived.
5361: It is because , organization id and location id are not getting populated
5362: in the JAI_AR_TRXS table. This was because a cursor fetch was done incorrectly. The
5363: cursor definition has been corrected .
5364:
5365: For RMA without reference , organization and location has to be fetched based on the subinventory
5366: chosen in the receiving transaction in Purchasing responsibility. Hence a new cursor is written
5367: to fetch the organization id , location id based on the subinventory name from the JAI_INV_SUBINV_DTLS
5368: table based on the subinventory name in the rcv transactions table.
5369:
5370: 7. 2003/04/18 Sriram - Bug # 2905912 - File Version 615.4
5371: For a RMA Transaction , the tax amount and total amount columns in the JAI_AR_TRXS
5372: table were incorrect. This has been corrected in this fix.
5373:
5374: 8. 2004/06/09 ssumaith - bug# 3680721 File Version 115.1
5375:
5400: 11. 10-Jun-2005 File Version: 116.2
5401: Removal of SQL LITERALs is done
5402:
5403: 12. 10-Jun-2005 rallamse bug#4448789 116.3
5404: Added legal_entity_id for table JAI_AR_TRXS in insert statement
5405:
5406: 13. 26-07-2005 rallamse bug#4510143 120.2
5407: The legal_entity_id is derived from CREATED_FROM_CUR cursor based on ra_customer_trx_all.
5408:
5548: or
5549: (nvl(v_ont_source_code,'ORDER ENTRY') <> nvl(ltrim(rtrim(pr_new.interface_line_context)),'$$$') -- added by sriram - bug#3680721
5550: )
5551: then
5552: INSERT INTO JAI_AR_TRXS
5553: (
5554: Customer_Trx_ID,
5555: Organization_ID,
5556: Location_ID,
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
5894: WHERE customer_trx_id = v_old_customer_trx_id;
5895: END IF;
5896: END IF; -- end if for the modification by subbu on 14-jun-01
5897: --start additions for bug#10156485
5921:
5922: END IF;
5923: --end additions for bug#10156485
5924:
5925: UPDATE JAI_AR_TRXS
5926: SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
5927: tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
5928: total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
5929: WHERE customer_trx_id = v_header_id;
6001: WHERE customer_trx_id = v_header_id;
6002:
6003: CURSOR DUPLICATE_HDR_CUR IS
6004: SELECT 1
6005: FROM JAI_AR_TRXS
6006: WHERE customer_trx_id = v_header_id;
6007:
6008: CURSOR DUPLICATE_LINES_CUR IS
6009: SELECT 1
6079: FETCH get_loc_cur
6080: INTO v_location_id;
6081: CLOSE get_loc_cur;
6082:
6083: INSERT INTO JAI_AR_TRXS
6084: (
6085: Customer_Trx_ID,
6086: Organization_ID,
6087: Location_ID,
6181: );
6182:
6183: END IF;
6184:
6185: UPDATE JAI_AR_TRXS
6186: SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
6187: tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
6188: total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
6189: WHERE customer_trx_id = v_header_id;
6375: FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
6376:
6377: CURSOR organization_cur IS
6378: SELECT A.organization_id, location_id--Added by Xiao Lv for bug 10091373
6379: FROM JAI_AR_TRXS A, RA_CUSTOMER_TRX_ALL B
6380: WHERE A.trx_number = B.recurred_from_trx_number AND B.customer_trx_id = v_header_id;
6381:
6382: CURSOR CREATED_FROM_CUR IS
6383: SELECT created_from , trx_date
6387: ld_trx_date ra_customer_trx_all.trx_Date%type;
6388:
6389: CURSOR ONCE_COMPLETE_FLAG_CUR IS
6390: SELECT once_completed_flag
6391: FROM JAI_AR_TRXS
6392: WHERE customer_trx_id = v_header_id;
6393:
6394: CURSOR ROW_ID_CUR IS
6395: SELECT rowid
6604: v_old_tax_tot := nvl(v_excise,0) + nvl(v_other,0) + nvl(v_additional,0);
6605: OPEN old_line_amount_cur;
6606: FETCH old_line_amount_cur INTO v_old_line_amount;
6607: CLOSE old_line_amount_cur;
6608: UPDATE JAI_AR_TRXS
6609: SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
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;
6875: END LOOP;
6876: v_tax_tot := nvl(v_excise,0) + nvl(v_other,0) + nvl(v_additional,0);
6877: v_tot_amt := nvl(v_line_amount,0) + nvl(v_total_tax_amount,0);
6878:
6879: UPDATE JAI_AR_TRXS
6880: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0),
6881: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) ,
6882: tax_amount = NVL(tax_amount,0) + NVL(v_total_tax_amount,0)
6883: WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
6879: UPDATE JAI_AR_TRXS
6880: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0),
6881: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) ,
6882: tax_amount = NVL(tax_amount,0) + NVL(v_total_tax_amount,0)
6883: WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
6884:
6885: /*added by csahoo for bug#5879769*/
6886: OPEN c_get_address_details(v_header_id);
6887: FETCH c_get_address_details into r_add;
7069:
7070:
7071: CURSOR organization_cur IS
7072: SELECT organization_id
7073: FROM JAI_AR_TRXS
7074: WHERE trx_number = (
7075: SELECT recurred_from_trx_number
7076: FROM RA_CUSTOMER_TRX_ALL
7077: WHERE customer_trx_id = v_header_id
7267: END LOOP;
7268: v_tax_tot := v_excise + v_other + v_additional;
7269: v_tot_amt := v_line_amount + v_tax_tot;
7270:
7271: UPDATE JAI_AR_TRXS
7272: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
7273: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
7274: tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
7275: creation_date = v_creation_date,
7403: FROM JAI_AR_TRX_APPS_RELS_T;/*altered by rchandan for paddr limination*/
7404:
7405: CURSOR organization_cur IS
7406: SELECT organization_id, location_id--Added by Xiao Lv for bug 10091373
7407: FROM JAI_AR_TRXS
7408: WHERE trx_number = (
7409: SELECT recurred_from_trx_number
7410: FROM RA_CUSTOMER_TRX_ALL
7411: WHERE customer_trx_id = v_header_id
7421: WHERE customer_trx_id = v_header_id;*//*commented by rchandan for bug#4479131*/
7422:
7423: CURSOR ONCE_COMPLETE_FLAG_CUR IS
7424: SELECT once_completed_flag
7425: FROM JAI_AR_TRXS
7426: WHERE customer_trx_id = v_header_id;
7427:
7428:
7429: Cursor transaction_type_cur IS
7782:
7783: v_tax_tot := v_excise + v_other + v_additional;
7784: v_tot_amt := v_line_amount + v_tax_tot;
7785:
7786: UPDATE JAI_AR_TRXS
7787: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
7788: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
7789: tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
7790: creation_date = v_creation_date,
7861: r_ra_customer_trx c_ra_customer_trx%rowtype;
7862:
7863: cursor c_jai_ra_customer_trx is
7864: select customer_trx_id
7865: from jai_ar_trxs
7866: where customer_trx_id = r_new.customer_trx_id;
7867: r_jai_ra_customer_trx c_jai_ra_customer_trx%rowtype;
7868:
7869: cursor c_jai_ra_customer_trx_line is
7965: fetch c_jai_ra_customer_trx into r_jai_ra_customer_trx;
7966: close c_jai_ra_customer_trx;
7967:
7968: if r_jai_ra_customer_trx.customer_trx_id is null then
7969: /* insert into jai_ar_trxs */
7970: insert into jai_ar_trxs(
7971: customer_trx_id
7972: , organization_id
7973: , location_id
7966: close c_jai_ra_customer_trx;
7967:
7968: if r_jai_ra_customer_trx.customer_trx_id is null then
7969: /* insert into jai_ar_trxs */
7970: insert into jai_ar_trxs(
7971: customer_trx_id
7972: , organization_id
7973: , location_id
7974: , trx_number
8163: null , -- ln_vat_assessable_value
8164: r_jai_pa_draft_inv_line.service_type_code
8165: );
8166:
8167: update jai_ar_trxs
8168: set tax_amount = nvl(tax_amount,0) + round( nvl(ln_line_tax_amt,0), 2)
8169: , line_amount = nvl(line_amount,0) + round( nvl(ln_line_amount,0), 2)
8170: , total_amount = nvl(total_amount,0) + round(nvl(ln_line_amount,0) + nvl(ln_line_tax_amt,0), 2)
8171: , last_updated_by = r_new.last_updated_by