[Home] [Help]
101: CUSTOMER_TRX_LINE_ID = v_customer_trx_line_id;
102:
103: CURSOR ONCE_COMPLETE_FLAG_CUR IS
104: SELECT once_completed_flag
105: FROM JAI_AR_TRXS
106: WHERE customer_trx_id = v_customer_trx_id;
107:
108: -- following segment uncommented by sriram - bug # 2618503
109: v_trans_type Varchar2(30);
157: S.No. Description
158:
159: 1 Sriram - Bug # 2590650 - Added delete statements to delete from the
160: JAI_AR_TRX_INS_LINES_T for customer trx id and trx line id and also the
161: JAI_AR_TRXS table
162:
163: 2 Sriram - Bug # 2618503
164: If the line being deleted corresponds to a return order or a sales order then
165: delete the lines related to localization ar tables.
227: CLOSE old_line_amount_cur;
228:
229:
230: --start additions for bug#7539258
231: UPDATE JAI_AR_TRXS
232: SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
233: tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
234: total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
235: WHERE customer_trx_id = v_customer_trx_id;
236:
237: --end additions for bug#7539258
238:
239: DELETE FROM
240: JAI_AR_TRXS trx
241: WHERE customer_trx_id = v_customer_trx_id
242: AND EXISTS
243: (SELECT 1
244: FROM ra_interface_lines_all il
358: WHERE customer_trx_id = v_header_id;
359:
360: Cursor localization_header_info_cur IS
361: SELECT organization_id
362: FROM JAI_AR_TRXS
363: WHERE customer_trx_id = v_header_id;
364:
365: /* Bug 5243532. Added by Lakshmi Gopalsami
366: Removed the cursors Fetch_Book_Id_cur and set_of_books_cur
526: v_last_updated_by,
527: v_last_update_login,
528: lv_service_type_code -- added by csahoo for bug#5879769
529: );
530: Update JAI_AR_TRXS
531: Set line_amount = nvl(line_amount,0) + nvl(v_line_amount,0)
532: Where Customer_Trx_Id = v_header_id;
533:
534: ELSIF v_line_type in ('FREIGHT','TAX') THEN
591: v_last_update_login
592: );
593:
594: IF rec.tax_type in ('Excise','Addl. Excise','Other Excise') THEN
595: Update JAI_AR_TRXS
596: Set total_amount = nvl(total_amount,0) + nvl(pr_new.extended_amount,0),
597: tax_amount = nvl(tax_amount,0) + nvl(pr_new.extended_amount,0)
598: Where Customer_Trx_Id = v_header_id;
599: END IF;
686: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
687: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
688: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
689: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
690: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
691: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
692: -- ended, Harshita for bug#4245062
693:
694: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type;
687: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
688: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
689: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE ;
690: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
691: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
692: -- ended, Harshita for bug#4245062
693:
694: lv_appl_src JAI_CMN_ERRORS_T.APPLICATION_SOURCE%type;
695: lv_err_msg JAI_CMN_ERRORS_T.error_message%type;
773:
774: CURSOR JA_IN_RA_CUSTOMER_TRX_INFO IS
775: SELECT organization_id,
776: Location_id
777: FROM JAI_AR_TRXS
778: WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4); -- Replaced pr_new.interface_line_attribute1 , Bug 4392001
779: CURSOR JA_IN_RA_CUSTOMER_TRX_INFOCNSL IS
780: SELECT A.organization_id,
781: A.Location_id
778: WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4); -- Replaced pr_new.interface_line_attribute1 , Bug 4392001
779: CURSOR JA_IN_RA_CUSTOMER_TRX_INFOCNSL IS
780: SELECT A.organization_id,
781: A.Location_id
782: FROM JAI_AR_TRXS A,
783: JAI_AR_SUP_HDRS_ALL B,
784: RA_CUSTOMER_TRX_ALL C
785: WHERE pr_new.customer_trx_id = c.customer_trx_id
786: AND c.trx_number = TO_CHAR(B.SUPPLEMENTARY_NUM)
953: t.stform_type ;
954:
955: CURSOR DUPLICATE_HDR_CUR IS
956: SELECT 1
957: FROM JAI_AR_TRXS
958: WHERE customer_trx_id = v_header_id;
959:
960: CURSOR ONCE_COMPLETE_FLAG_CUR IS
961: SELECT once_completed_flag
958: WHERE customer_trx_id = v_header_id;
959:
960: CURSOR ONCE_COMPLETE_FLAG_CUR IS
961: SELECT once_completed_flag
962: FROM JAI_AR_TRXS
963: WHERE customer_trx_id = v_header_id;
964: /* Cursor To Check Whether Interfaced Record Exist in JAI_OM_WSH_LINES_ALL */
965:
966: CURSOR SO_PICKING_RECORD_CHECK IS
1681: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Ato tot amount: '||v_ato_total_amount);
1682: end loop;
1683:
1684:
1685: -- creation of record in JAI_AR_TRXS
1686:
1687: OPEN CREATED_FROM_CUR;
1688: FETCH CREATED_FROM_CUR INTO v_created_from, v_trx_number, v_batch_source_id, v_books_id,
1689: v_salesrep_id, c_from_currency_code, c_conversion_type,
1708: FND_FILE.PUT_LINE(FND_FILE.LOG,' Loc id: '||v_location_id);
1709:
1710: ln_legal_entity_id := get_legal_entity_id ;
1711:
1712: Insert into JAI_AR_TRXS
1713: (
1714: CUSTOMER_TRX_ID ,
1715: ORGANIZATION_ID ,
1716: LOCATION_ID ,
1763: 'Y',
1764: ln_legal_entity_id /* rallamse bug#4448789 */
1765: );
1766:
1767: FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
1768: FND_FILE.PUT_LINE(FND_FILE.LOG,
1769: 'End of Procedure Process_Taxes_for_ATO_Order');
1770:
1771: end if; -- end if for IF NVL(pr_new.Interface_line_attribute11,'0') ='0'
1779: Start additions by ssumaith for bug# 4136981*/
1780: procedure process_bill_only_invoice is
1781: cursor c_check_hdr_exists is
1782: select 1
1783: from JAI_AR_TRXS
1784: where customer_trx_id = pr_new.customer_trx_id;
1785:
1786:
1787: cursor c_trx_cur is
1897: Validations to handle
1898: ---------------------
1899: a) in case of discounts only one line should have taxes
1900: b) in case of bond register excise and excise cess taxes should not flow into AR
1901: c) only one record should be inserted in the JAI_AR_TRXS table.
1902: */
1903:
1904: ln_hdr_exists := 0;
1905: open c_check_hdr_exists;
1909: FND_FILE.PUT_LINE(FND_FILE.LOG,' Header exists '|| ln_hdr_exists);
1910:
1911: if nvl(ln_hdr_exists,0) = 0 then
1912: /*
1913: no record exists in the JAI_AR_TRXS table for the customer trx id ,
1914: hence insert a record into the table.
1915: */
1916:
1917: open c_trx_cur;
1938:
1939: /*
1940: if the default location setup is not done , log this as a message in the JAI_CMN_ERRORS_T table
1941: do not stop the processing.
1942: We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
1943: */
1944: lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
1945: lv_err_msg := 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id ;
1946: lv_addl_msg := 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id ;
1970: end if;
1971:
1972: ln_legal_entity_id := get_legal_entity_id ;
1973:
1974: insert into JAI_AR_TRXS -- bill only invoice
1975: (
1976: CUSTOMER_TRX_ID ,
1977: ORGANIZATION_ID ,
1978: LOCATION_ID ,
2028: ln_legal_entity_id /* rallamse bug#4448789 */
2029: );
2030:
2031: FND_FILE.PUT_LINE(FND_FILE.LOG,
2032: ' After insert into JAI_AR_TRXS - Bill only invoice');
2033: end if;
2034:
2035: /*
2036: insert into the JAI_AR_TRX_TAX_LINES table and then insert into the JAI_AR_TRX_LINES table.
2388: FND_FILE.PUT_LINE(FND_FILE.LOG,' Cursor c_get_amounts');
2389: FND_FILE.PUT_LINE(FND_FILE.LOG,' Tax amount '|| ln_tax_amount);
2390: FND_FILE.PUT_LINE(FND_FILE.LOG,' Line amount '|| ln_line_amount);
2391:
2392: update JAI_AR_TRXS
2393: set tax_amount = ln_tax_amount ,
2394: line_amount = ln_line_amount,
2395: total_amount = ln_line_amount + ln_tax_amount
2396: where customer_trx_id = pr_new.customer_trx_id;
2441: when the manual delivery is made and when the delivery name is alphanumeric.
2442: When the order created is assigned to this delivery and the ship confimation is
2443: done ,then the autoinvoice import program is running into error because the
2444: the validation is failing for delivery_id and interface_line_attribute3
2445: 9. 2002/04/22 SRIRAM For Bug # 2316589 . The JAI_AR_TRXS table was updated with
2446: wrong values . Instead a new update has been written using a different value to
2447: update tax amounts correctly.
2448: 10 2002/06/26 SRIRAM For Bug # 2398198 . Tax Lines were inserted multiple times in the
2449: JAI_AR_TRX_TAX_LINES , JAI_AR_TRX_LINES tables when discounts
2446: wrong values . Instead a new update has been written using a different value to
2447: update tax amounts correctly.
2448: 10 2002/06/26 SRIRAM For Bug # 2398198 . Tax Lines were inserted multiple times in the
2449: JAI_AR_TRX_TAX_LINES , JAI_AR_TRX_LINES tables when discounts
2450: exist and tax amounts were updated doubly in JAI_AR_TRXS table, which have been
2451: solved in this bug.
2452: 11. 2002/08/17 SRIRAM .Fopr Bug # 2518534 . When there is a change in UOM in the
2453: Sales order screen , the taxes in the localization screen are not
2454: flowing correctly.
2468: For a Invoice line imported from OM , the interface_line_attribute11 will have a value 0 or Null ,
2469: whereas for a discount line , the interface_line_attribute11 will have a value which maps to the price_adjustment_id.
2470:
2471: 13. 2003/02/07 Vijay Shankar - Bug # 2779990 - Version is 615.4
2472: When bond transaction invoice is created, then tax_amounts populated in JAI_AR_TRXS and JAI_AR_TRX_LINES is wrong.
2473: this is rectified by writing a new cursor name c_tax_amount
2474:
2475: 14. 2003/03/17 Sriram LMW ATO Issue - Bug #2806274 Version 615.5
2476: Created an internal procedure Process_Taxes_for_ATO_Order to pull taxes from OM to Ar for ATO Orders.
2476: Created an internal procedure Process_Taxes_for_ATO_Order to pull taxes from OM to Ar for ATO Orders.
2477: The idea was to import the taxes for the 'Model' Item based on the config item.
2478:
2479: 15. 2003/06/26 Sriram - Bug # 3000550 version 616.1
2480: Tax amounts were not calculated correctly , in the JAI_AR_TRXS and JAI_AR_TRX_LINES
2481: table.This was observed when there was a split done during shipment.
2482: Data was correct in the JAI_AR_TRX_TAX_LINES table , but incorrect in the JAI_AR_TRX_LINES
2483: and JAI_AR_TRXS table.
2484: This has been fixed in this bug
2479: 15. 2003/06/26 Sriram - Bug # 3000550 version 616.1
2480: Tax amounts were not calculated correctly , in the JAI_AR_TRXS and JAI_AR_TRX_LINES
2481: table.This was observed when there was a split done during shipment.
2482: Data was correct in the JAI_AR_TRX_TAX_LINES table , but incorrect in the JAI_AR_TRX_LINES
2483: and JAI_AR_TRXS table.
2484: This has been fixed in this bug
2485:
2486: 16. 2003/08/22 Sriram - Bug # 3021588 version 616.2
2487:
2574: The columns vat_exemption_flag, vat_exemption_type, vat_exemption_date, vat_exemption_refno,
2575: and vat_assessable_value have been added in JAI_OM_OE_SO_LINES, JAI_OM_WSH_LINES_ALL
2576: and JAI_AR_TRX_LINES.
2577: Additional fields vat_invoice_no and vat_invoice_date have been added into JAI_OM_WSH_LINES_ALL
2578: and JAI_AR_TRXS.
2579: The trigger has been updated to ensure that any data flowing into JAI_AR_TRX_LINES and
2580: JAI_AR_TRXS also includes the Vat information that comes down from either JAI_OM_OE_SO_LINES
2581: or JAI_OM_WSH_LINES_ALL .
2582:
2576: and JAI_AR_TRX_LINES.
2577: Additional fields vat_invoice_no and vat_invoice_date have been added into JAI_OM_WSH_LINES_ALL
2578: and JAI_AR_TRXS.
2579: The trigger has been updated to ensure that any data flowing into JAI_AR_TRX_LINES and
2580: JAI_AR_TRXS also includes the Vat information that comes down from either JAI_OM_OE_SO_LINES
2581: or JAI_OM_WSH_LINES_ALL .
2582:
2583: Base bug - #4245089
2584:
2609: 29 10-Jun-2005 File Version: 116.3
2610: Removal of SQL LITERALs is done
2611:
2612: 30 10-Jun-2005 rallamse bug#4448789 116.3
2613: Added legal_entity_id for table JAI_AR_TRXS in insert statement
2614:
2615: 06-Jul-2005 Ramananda for bug#4477004. File Version: 116.4
2616: GL Sources and GL Categories got changed. Refer bug for the details
2617:
2620:
2621: 32 26-Jul-2005 rallamse for bug#4510143 File Version 120.2
2622: Changed the legal_entity_id to get from function get_legal_entity_id.
2623: Replaced legal_entity_id with ln_legal_entity_id( holds value of get_legal_entity_id )
2624: for table JAI_AR_TRXS in insert statement.
2625:
2626: 33. 24-Aug-2005 Ramananda for bug #4567935 (115 bug 4404898). File version 120.2
2627: Issue:-
2628: 1. Trigger currently processes invoices with interface line context as LEGACY.This needs to be stopped
2640: Multipled p_quantity to the assesible value in the call to the procedure jai_rgm_thhold_proc_pkg.default_thhold_taxes
2641:
2642: 36. 18-OCT-2007 CSahoo for bug#6498072, File Version 120.22
2643: Added the cursor get_ar_tax_amount to get the total tax amount from JAI_AR_TRX_TAX_LINES table.
2644: Moved the code for Inserting into JAI_AR_TRX_LINES and updating the table JAI_AR_TRXS to the end in the procedure
2645: process_bill_only_invoice
2646:
2647: 37 11-Nov-2008 JMEENA for bug#6498345( FP 6492966 )
2648: Issue: AR Autoinvoice completes in Error: ORA-20130: ORGANIZATION CANNOT BE NULL
2885: FETCH ja_in_ra_customer_trx_info INTO v_organization_id, v_location_id;
2886: CLOSE ja_in_ra_customer_trx_info;
2887: END IF;
2888:
2889: INSERT INTO JAI_AR_TRXS ( -- supplement
2890: customer_trx_id ,
2891: organization_id ,
2892: location_id ,
2893: trx_number ,
2929: v_last_updated_by ,
2930: v_last_update_login ,
2931: ln_legal_entity_id /* rallamse bug#4448789 */
2932: );
2933: FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inside x <> 1: After insert into JAI_AR_TRXS');
2934: END IF;
2935:
2936: IF pr_new.interface_line_attribute4 IS NULL THEN -- pr_new.interface_line_attribute1, Bug# 4392001
2937: OPEN supplement_tax_lines_check_cns;
3189: v_last_updated_by ,
3190: v_last_update_login
3191: );
3192:
3193: UPDATE JAI_AR_TRXS
3194: SET
3195: line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
3196: once_completed_flag = NVL(v_once_completed_flag,'N')
3197: WHERE
3581: /*
3582: ends here -- bug# 3607101
3583: */
3584:
3585: INSERT INTO JAI_AR_TRXS
3586: (Customer_Trx_ID, Organization_ID, Location_ID, Trx_Number,
3587: Update_RG_Flag,UPDATE_RG23D_FLAG, Once_Completed_Flag, Batch_Source_ID, Set_Of_Books_ID,
3588: Primary_Salesrep_ID, Invoice_Currency_Code, Exchange_Rate_Type,
3589: Exchange_Date, Exchange_Rate,
3618:
3619: FOR tax_rec IN so_picking_tax_lines_info LOOP
3620: /*IF ln_first_time = 0 THEN
3621: ln_first_time := ln_first_time + 1;
3622: UPDATE JAI_AR_TRXS
3623: SET vat_invoice_no = tax_rec.vat_invoice_no,
3624: vat_invoice_date = tax_rec.vat_invoice_date
3625: WHERE customer_trx_id = pr_new.customer_trx_id;
3626: END IF;*/
4052: IF v_old_customer_trx_id <> v_header_id THEN
4053: UPDATE JAI_AR_TRX_LINES
4054: SET customer_trx_id = v_header_id
4055: WHERE customer_trx_line_id = v_customer_trx_line_id;
4056: DELETE JAI_AR_TRXS
4057: WHERE customer_trx_id = v_old_customer_trx_id;
4058: END IF;
4059: END IF; /* END IF OF IF NVL(v_line_count,0) <> 1 */
4060:
4096: -- Above if condition added by sriram Bug # 2742849
4097: -- instead of the previous if condition because , it may fail in circumstances
4098: -- where line ordering rules are set up in autoinvoice options.
4099:
4100: UPDATE JAI_AR_TRXS
4101: SET line_amount = round(NVL(line_amount,0) + NVL(v_line_amount,0),2),
4102: tax_amount = round(NVL(tax_amount,0) + NVL(v_so_tax_amount,0),2),
4103: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_so_tax_amount,0),2),
4104: once_completed_flag = NVL(v_once_completed_flag,'N'),
4104: once_completed_flag = NVL(v_once_completed_flag,'N'),
4105: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
4106: WHERE customer_trx_id = v_header_id;
4107: ELSE
4108: UPDATE JAI_AR_TRXS
4109: SET line_amount = round(NVL(line_amount, 0 ) + NVL(v_line_amount,0),2),
4110: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0),2),
4111: once_completed_flag = NVL(v_once_completed_flag,'N'),
4112: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date -- added, Harshita for bug#4245062
4285:
4286:
4287: CURSOR DUPLICATE_HDR_CUR IS
4288: SELECT 1
4289: FROM JAI_AR_TRXS
4290: WHERE customer_trx_id = v_header_id;
4291:
4292:
4293: /* Cursor To Check Whether Interfaced Record Exist in JAI_OM_WSH_LINES_ALL */
4404: 6. 2003/01/18 Sriram - Bug # 2755890 - File Version 615.3
4405: When a credit memo created in AR because of a RMA Transaction is queried
4406: in the localization AR screen , it was causing no reords to be retreived.
4407: It is because , organization id and location id are not getting populated
4408: in the JAI_AR_TRXS table. This was because a cursor fetch was done incorrectly. The
4409: cursor definition has been corrected .
4410:
4411: For RMA without reference , organization and location has to be fetched based on the subinventory
4412: chosen in the receiving transaction in Purchasing responsibility. Hence a new cursor is written
4413: to fetch the organization id , location id based on the subinventory name from the JAI_INV_SUBINV_DTLS
4414: table based on the subinventory name in the rcv transactions table.
4415:
4416: 7. 2003/04/18 Sriram - Bug # 2905912 - File Version 615.4
4417: For a RMA Transaction , the tax amount and total amount columns in the JAI_AR_TRXS
4418: table were incorrect. This has been corrected in this fix.
4419:
4420: 8. 2004/06/09 ssumaith - bug# 3680721 File Version 115.1
4421:
4446: 11. 10-Jun-2005 File Version: 116.2
4447: Removal of SQL LITERALs is done
4448:
4449: 12. 10-Jun-2005 rallamse bug#4448789 116.3
4450: Added legal_entity_id for table JAI_AR_TRXS in insert statement
4451:
4452: 13. 26-07-2005 rallamse bug#4510143 120.2
4453: The legal_entity_id is derived from CREATED_FROM_CUR cursor based on ra_customer_trx_all.
4454:
4571: or
4572: (nvl(v_ont_source_code,'ORDER ENTRY') <> nvl(ltrim(rtrim(pr_new.interface_line_context)),'$$$') -- added by sriram - bug#3680721
4573: )
4574: then
4575: INSERT INTO JAI_AR_TRXS
4576: (
4577: Customer_Trx_ID,
4578: Organization_ID,
4579: Location_ID,
4815: UPDATE JAI_AR_TRX_LINES
4816: SET Customer_Trx_Id = v_header_id
4817: WHERE Customer_Trx_Line_Id = v_customer_trx_line_id;
4818:
4819: DELETE JAI_AR_TRXS
4820: WHERE customer_trx_id = v_old_customer_trx_id;
4821: END IF;
4822: END IF; -- end if for the modification by subbu on 14-jun-01
4823:
4820: WHERE customer_trx_id = v_old_customer_trx_id;
4821: END IF;
4822: END IF; -- end if for the modification by subbu on 14-jun-01
4823:
4824: UPDATE JAI_AR_TRXS
4825: SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
4826: tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
4827: total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
4828: WHERE customer_trx_id = v_header_id;
4995: FROM JAI_AR_TRX_APPS_RELS_T ;/*altered by rchandan for bug#4479131*/
4996:
4997: CURSOR organization_cur IS
4998: SELECT A.organization_id
4999: FROM JAI_AR_TRXS A, RA_CUSTOMER_TRX_ALL B
5000: WHERE A.trx_number = B.recurred_from_trx_number AND B.customer_trx_id = v_header_id;
5001:
5002: CURSOR CREATED_FROM_CUR IS
5003: SELECT created_from , trx_date
5007: ld_trx_date ra_customer_trx_all.trx_Date%type;
5008:
5009: CURSOR ONCE_COMPLETE_FLAG_CUR IS
5010: SELECT once_completed_flag
5011: FROM JAI_AR_TRXS
5012: WHERE customer_trx_id = v_header_id;
5013:
5014: CURSOR ROW_ID_CUR IS
5015: SELECT rowid
5221: v_old_tax_tot := nvl(v_excise,0) + nvl(v_other,0) + nvl(v_additional,0);
5222: OPEN old_line_amount_cur;
5223: FETCH old_line_amount_cur INTO v_old_line_amount;
5224: CLOSE old_line_amount_cur;
5225: UPDATE JAI_AR_TRXS
5226: SET line_amount = nvl(line_amount,0) - nvl(v_old_line_amount,0),
5227: tax_amount = nvl(tax_amount,0) - nvl(v_old_tax_tot,0),
5228: total_amount = nvl(total_amount,0) - (nvl(v_old_line_amount,0) + nvl(v_old_tax_tot,0))
5229: WHERE customer_trx_id = pr_old.CUSTOMER_TRX_ID;
5421: END LOOP;
5422: v_tax_tot := nvl(v_excise,0) + nvl(v_other,0) + nvl(v_additional,0);
5423: v_tot_amt := nvl(v_line_amount,0) + nvl(v_total_tax_amount,0);
5424:
5425: UPDATE JAI_AR_TRXS
5426: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0),
5427: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) ,
5428: tax_amount = NVL(tax_amount,0) + NVL(v_total_tax_amount,0)
5429: WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
5425: UPDATE JAI_AR_TRXS
5426: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0),
5427: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) ,
5428: tax_amount = NVL(tax_amount,0) + NVL(v_total_tax_amount,0)
5429: WHERE JAI_AR_TRXS.customer_trx_id = v_header_id;
5430:
5431: /*added by csahoo for bug#5879769*/
5432: OPEN c_get_address_details(v_header_id);
5433: FETCH c_get_address_details into r_add;
5613:
5614:
5615: CURSOR organization_cur IS
5616: SELECT organization_id
5617: FROM JAI_AR_TRXS
5618: WHERE trx_number = (
5619: SELECT recurred_from_trx_number
5620: FROM RA_CUSTOMER_TRX_ALL
5621: WHERE customer_trx_id = v_header_id
5811: END LOOP;
5812: v_tax_tot := v_excise + v_other + v_additional;
5813: v_tot_amt := v_line_amount + v_tax_tot;
5814:
5815: UPDATE JAI_AR_TRXS
5816: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
5817: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
5818: tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
5819: creation_date = v_creation_date,
5945: FROM JAI_AR_TRX_APPS_RELS_T;/*altered by rchandan for paddr limination*/
5946:
5947: CURSOR organization_cur IS
5948: SELECT organization_id
5949: FROM JAI_AR_TRXS
5950: WHERE trx_number = (
5951: SELECT recurred_from_trx_number
5952: FROM RA_CUSTOMER_TRX_ALL
5953: WHERE customer_trx_id = v_header_id
5963: WHERE customer_trx_id = v_header_id;*//*commented by rchandan for bug#4479131*/
5964:
5965: CURSOR ONCE_COMPLETE_FLAG_CUR IS
5966: SELECT once_completed_flag
5967: FROM JAI_AR_TRXS
5968: WHERE customer_trx_id = v_header_id;
5969:
5970:
5971: Cursor transaction_type_cur IS
6287:
6288: v_tax_tot := v_excise + v_other + v_additional;
6289: v_tot_amt := v_line_amount + v_tax_tot;
6290:
6291: UPDATE JAI_AR_TRXS
6292: SET line_amount = NVL(line_amount,0) + NVL(v_line_amount,0) - NVL(v_old_amount,0),
6293: total_amount = NVL(total_amount,0)+ NVL(v_tot_amt,0) - nvl(v_old_amount,0) - NVL(v_old_tax_tot,0),
6294: tax_amount = NVL(tax_amount,0) + NVL(v_tax_tot,0) - NVL(v_old_tax_tot,0),
6295: creation_date = v_creation_date,
6364: r_ra_customer_trx c_ra_customer_trx%rowtype;
6365:
6366: cursor c_jai_ra_customer_trx is
6367: select customer_trx_id
6368: from jai_ar_trxs
6369: where customer_trx_id = r_new.customer_trx_id;
6370: r_jai_ra_customer_trx c_jai_ra_customer_trx%rowtype;
6371:
6372: cursor c_jai_ra_customer_trx_line is
6468: fetch c_jai_ra_customer_trx into r_jai_ra_customer_trx;
6469: close c_jai_ra_customer_trx;
6470:
6471: if r_jai_ra_customer_trx.customer_trx_id is null then
6472: /* insert into jai_ar_trxs */
6473: insert into jai_ar_trxs(
6474: customer_trx_id
6475: , organization_id
6476: , location_id
6469: close c_jai_ra_customer_trx;
6470:
6471: if r_jai_ra_customer_trx.customer_trx_id is null then
6472: /* insert into jai_ar_trxs */
6473: insert into jai_ar_trxs(
6474: customer_trx_id
6475: , organization_id
6476: , location_id
6477: , trx_number
6666: null , -- ln_vat_assessable_value
6667: r_jai_pa_draft_inv_line.service_type_code
6668: );
6669:
6670: update jai_ar_trxs
6671: set tax_amount = nvl(tax_amount,0) + round( nvl(ln_line_tax_amt,0), 2)
6672: , line_amount = nvl(line_amount,0) + round( nvl(ln_line_amount,0), 2)
6673: , total_amount = nvl(total_amount,0) + round(nvl(ln_line_amount,0) + nvl(ln_line_tax_amt,0), 2)
6674: , last_updated_by = r_new.last_updated_by