[Home] [Help]
87:
88: cursor c_get_rec(cn_customer_trx_id number)
89: is
90: select *
91: from jai_ar_trxs
92: where customer_trx_id = cn_customer_trx_id;
93:
94: t_rec_new jai_ar_trxs%rowtype;
95: t_rec_old jai_ar_trxs%rowtype;
90: select *
91: from jai_ar_trxs
92: where customer_trx_id = cn_customer_trx_id;
93:
94: t_rec_new jai_ar_trxs%rowtype;
95: t_rec_old jai_ar_trxs%rowtype;
96:
97: CURSOR SO_PICKING_HDR_INFO IS
98: SELECT A.organization_id, A.location_id
91: from jai_ar_trxs
92: where customer_trx_id = cn_customer_trx_id;
93:
94: t_rec_new jai_ar_trxs%rowtype;
95: t_rec_old jai_ar_trxs%rowtype;
96:
97: CURSOR SO_PICKING_HDR_INFO IS
98: SELECT A.organization_id, A.location_id
99: FROM JAI_OM_WSH_LINES_ALL A,WSH_NEW_DELIVERIES B
198: AND jowlt.order_line_id = TO_NUMBER(v_interface_line_attribute6);
199:
200: CURSOR DUPLICATE_HDR_CUR IS
201: SELECT 1
202: FROM JAI_AR_TRXS
203: WHERE customer_trx_id = pr_new.customer_trx_id;
204:
205: CURSOR CREATED_FROM_CUR IS
206: SELECT created_from, trx_number, batch_source_id, set_of_books_id, primary_salesrep_id,
402: lv_vat_exemption_type JAI_AR_TRX_LINES.vat_exemption_type%TYPE ;
403: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
404: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
405: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE;
406: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
407: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
408: v_service_type VARCHAR2(30);
409: v_line_count NUMBER;
410: ln_tax_exist NUMBER;
403: lv_vat_exemption_date JAI_AR_TRX_LINES.vat_exemption_date%TYPE ;
404: lv_vat_exemption_refno JAI_AR_TRX_LINES.vat_exemption_refno%TYPE ;
405: ln_vat_assessable_value JAI_AR_TRX_LINES.vat_assessable_value%TYPE;
406: ln_vat_invoice_no JAI_AR_TRXS.vat_invoice_no %TYPE ;
407: ln_vat_invoice_date JAI_AR_TRXS.vat_invoice_date%TYPE;
408: v_service_type VARCHAR2(30);
409: v_line_count NUMBER;
410: ln_tax_exist NUMBER;
411: v_calc_tax_amount NUMBER;
428: procedure process_bill_only_invoice
429: is
430: cursor c_check_hdr_exists is
431: select 1
432: from JAI_AR_TRXS
433: where customer_trx_id = pr_new.customer_trx_id;
434:
435:
436: cursor c_trx_cur is
561: Validations to handle
562: ---------------------
563: a) in case of discounts only one line should have taxes
564: b) in case of bond register excise and excise cess taxes should not flow into AR
565: c) only one record should be inserted in the JAI_AR_TRXS table.
566: */
567:
568: ln_hdr_exists := 0;
569: open c_check_hdr_exists;
579: );
580: end if;
581: if nvl(ln_hdr_exists,0) = 0 then
582: /*
583: no record exists in the JAI_AR_TRXS table for the customer trx id ,
584: hence insert a record into the table.
585: */
586:
587: open c_trx_cur;
620:
621: \*
622: if the default location setup is not done , log this as a message in the JAI_CMN_ERRORS_T table
623: do not stop the processing.
624: We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
625: *\
626: lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
627: lv_err_msg := 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id ;
628: lv_addl_msg := 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id ;
650: end if;*/
651:
652: ln_legal_entity_id := get_legal_entity_id(v_header_id) ;
653:
654: insert into JAI_AR_TRXS -- bill only invoice
655: (
656: CUSTOMER_TRX_ID ,
657: ORGANIZATION_ID ,
658: LOCATION_ID ,
711: NULL ,
712: ln_legal_entity_id
713: );
714:
715: --FND_FILE.PUT_LINE(FND_FILE.LOG,' After insert into JAI_AR_TRXS - Bill only invoice');
716: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
717: FND_LOG.STRING(G_LEVEL_PROCEDURE,
718: G_MODULE_NAME || l_api_name || '.IN',
719: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
715: --FND_FILE.PUT_LINE(FND_FILE.LOG,' After insert into JAI_AR_TRXS - Bill only invoice');
716: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
717: FND_LOG.STRING(G_LEVEL_PROCEDURE,
718: G_MODULE_NAME || l_api_name || '.IN',
719: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
720: );
721: end if;
722: end if;
723:
1150: ---------------------------------------------------------
1151: --Added by Zhiwei for JAI Trigger elimination end
1152:
1153:
1154: update JAI_AR_TRXS
1155: set tax_amount = ln_tax_amount ,
1156: line_amount = ln_line_amount,
1157: total_amount = ln_line_amount + ln_tax_amount
1158: ,vat_invoice_no = lv_vat_invoice_no
1167: if(t_rec_new.once_completed_flag = 'Y')then
1168:
1169: lv_action := jai_constants.updating ;
1170:
1171: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
1172: pr_old => t_rec_old ,
1173: pr_new => t_rec_new ,
1174: pv_action => lv_action ,
1175: pv_return_code => lv_return_code ,
1565: ---------------------------------------------------------
1566: --Added by Zhiwei for JAI Trigger elimination end
1567:
1568:
1569: update JAI_AR_TRXS
1570: set organization_id = nvl(organization_id, v_organization_id)
1571: , location_id = nvl(location_id, v_location_id)
1572: , last_update_date = sysdate
1573: where customer_trx_id = v_header_id;
1581: if(t_rec_new.once_completed_flag = 'Y')then
1582:
1583: lv_action := jai_constants.updating ;
1584:
1585: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
1586: pr_old => t_rec_old ,
1587: pr_new => t_rec_new ,
1588: pv_action => lv_action ,
1589: pv_return_code => lv_return_code ,
1667: end if;
1668: END IF ;
1669:
1670:
1671: Insert into JAI_AR_TRXS
1672: (
1673: CUSTOMER_TRX_ID ,
1674: ORGANIZATION_ID ,
1675: LOCATION_ID ,
1724: );
1725: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
1726: FND_LOG.STRING(G_LEVEL_PROCEDURE,
1727: G_MODULE_NAME || l_api_name || '.IN',
1728: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS '
1729: );
1730: end if;
1731: --FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
1732:
1727: G_MODULE_NAME || l_api_name || '.IN',
1728: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS '
1729: );
1730: end if;
1731: --FND_FILE.PUT_LINE(FND_FILE.LOG,'After insert into JAI_AR_TRXS ');
1732:
1733: end if;
1734:
1735:
2051: close c_ont_source_code;
2052: v_ont_source_code := ltrim(rtrim(v_ont_source_code));
2053:
2054:
2055: INSERT INTO JAI_AR_TRXS
2056: (Customer_Trx_ID, Organization_ID, Location_ID, Trx_Number,
2057: Update_RG_Flag,UPDATE_RG23D_FLAG, Once_Completed_Flag, Batch_Source_ID, Set_Of_Books_ID,
2058: Primary_Salesrep_ID, Invoice_Currency_Code, Exchange_Rate_Type,
2059: Exchange_Date, Exchange_Rate,
2831: RAISE le_error;
2832: END IF;
2833: END IF;
2834: --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
2835: DELETE JAI_AR_TRXS
2836: WHERE customer_trx_id = v_old_customer_trx_id;
2837: END IF;
2838: END IF; /* END IF OF IF NVL(v_line_count,0) <> 1 */
2839:
2905:
2906: -- instead of the previous if condition because , it may fail in circumstances
2907: -- where line ordering rules are set up in autoinvoice options.
2908:
2909: UPDATE JAI_AR_TRXS
2910: SET line_amount = round(NVL(line_amount,0) + NVL(v_line_amount,0),2),
2911: tax_amount = round(NVL(tax_amount,0) + NVL(v_so_tax_amount,0),2),
2912: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_so_tax_amount,0),2),
2913: once_completed_flag = NVL(v_once_completed_flag,'N'),
2915: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date
2916: WHERE customer_trx_id = v_header_id;
2917: ELSE
2918:
2919: UPDATE JAI_AR_TRXS
2920: SET line_amount = round(NVL(line_amount, 0 ) + NVL(v_line_amount,0),2),
2921: total_amount = round(NVL(total_amount,0) + NVL(v_line_amount,0),2),
2922: once_completed_flag = NVL(v_once_completed_flag,'N'),
2923: vat_invoice_no = ln_vat_invoice_no, vat_invoice_date = ln_vat_invoice_date
2933: if(t_rec_new.once_completed_flag = 'Y')then
2934:
2935: lv_action := jai_constants.updating ;
2936:
2937: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
2938: pr_old => t_rec_old ,
2939: pr_new => t_rec_new ,
2940: pv_action => lv_action ,
2941: pv_return_code => lv_return_code ,
2982: WHERE customer_trx_id = pr_new.customer_trx_id;
2983:
2984: CURSOR DUPLICATE_HDR_CUR IS
2985: SELECT 1
2986: FROM JAI_AR_TRXS
2987: WHERE customer_trx_id = pr_new.customer_trx_id;
2988:
2989: CURSOR SUPPLEMENT_LINES_CHECK_CNSLDT IS
2990: SELECT 1
3006:
3007: CURSOR JA_IN_RA_CUSTOMER_TRX_INFOCNSL IS
3008: SELECT A.organization_id,
3009: A.Location_id
3010: FROM JAI_AR_TRXS A,
3011: JAI_AR_SUP_HDRS_ALL B,
3012: RA_CUSTOMER_TRX_ALL C
3013: WHERE pr_new.customer_trx_id = c.customer_trx_id
3014: AND c.trx_number = TO_CHAR(B.SUPPLEMENTARY_NUM)
3016:
3017: CURSOR JA_IN_RA_CUSTOMER_TRX_INFO IS
3018: SELECT organization_id,
3019: Location_id
3020: FROM JAI_AR_TRXS
3021: WHERE customer_trx_id = TO_NUMBER(pr_new.interface_line_attribute4);
3022:
3023:
3024: CURSOR CREATED_FROM_CUR IS
3179:
3180: cursor c_get_rec(cn_customer_trx_id number)
3181: is
3182: select *
3183: from jai_ar_trxs
3184: where customer_trx_id = cn_customer_trx_id;
3185:
3186: t_rec_new jai_ar_trxs%rowtype;
3187: t_rec_old jai_ar_trxs%rowtype;
3182: select *
3183: from jai_ar_trxs
3184: where customer_trx_id = cn_customer_trx_id;
3185:
3186: t_rec_new jai_ar_trxs%rowtype;
3187: t_rec_old jai_ar_trxs%rowtype;
3188:
3189: CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_batch_source_id IN NUMBER) IS
3190: SELECT register_code
3183: from jai_ar_trxs
3184: where customer_trx_id = cn_customer_trx_id;
3185:
3186: t_rec_new jai_ar_trxs%rowtype;
3187: t_rec_old jai_ar_trxs%rowtype;
3188:
3189: CURSOR register_code_cur(p_org_id IN NUMBER, p_loc_id IN NUMBER, p_batch_source_id IN NUMBER) IS
3190: SELECT register_code
3191: FROM JAI_OM_OE_BOND_REG_HDRS
3428: FETCH ja_in_ra_customer_trx_info INTO v_organization_id, v_location_id;
3429: CLOSE ja_in_ra_customer_trx_info;
3430: END IF;
3431:
3432: INSERT INTO JAI_AR_TRXS ( -- supplement
3433: customer_trx_id ,
3434: organization_id ,
3435: location_id ,
3436: trx_number ,
3474: v_last_update_login ,
3475: ln_legal_entity_id ,
3476: v_created_from --zhiwei
3477: );
3478: --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inside x <> 1: After insert into JAI_AR_TRXS');
3479: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
3480: FND_LOG.STRING(G_LEVEL_PROCEDURE,
3481: G_MODULE_NAME || l_api_name || '.IN',
3482: G_PKG_NAME || ': ' || l_api_name || '().'||' Inside x <> 1: After insert into JAI_AR_TRXS '
3478: --FND_FILE.PUT_LINE(FND_FILE.LOG, ' Inside x <> 1: After insert into JAI_AR_TRXS');
3479: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
3480: FND_LOG.STRING(G_LEVEL_PROCEDURE,
3481: G_MODULE_NAME || l_api_name || '.IN',
3482: G_PKG_NAME || ': ' || l_api_name || '().'||' Inside x <> 1: After insert into JAI_AR_TRXS '
3483: );
3484: end if;
3485: END IF;
3486:
3824: ---------------------------------------------------------
3825: --Added by Zhiwei for JAI Trigger elimination end
3826:
3827:
3828: UPDATE JAI_AR_TRXS
3829: SET
3830: line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
3831: once_completed_flag = NVL(v_once_completed_flag,'N')
3832: WHERE
3841: if(t_rec_new.once_completed_flag = 'Y')then
3842:
3843: lv_action := jai_constants.updating ;
3844:
3845: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
3846: pr_old => t_rec_old ,
3847: pr_new => t_rec_new ,
3848: pv_action => lv_action ,
3849: pv_return_code => lv_return_code ,
4367: AND rownum=1 ;
4368:
4369: CURSOR ONCE_COMPLETE_FLAG_CUR IS
4370: SELECT once_completed_flag
4371: FROM JAI_AR_TRXS
4372: WHERE customer_trx_id = v_header_id;
4373:
4374: v_trans_type VARCHAR2(30);
4375:
4471: ---------------------------------------------------------
4472: cursor c_get_rec(cn_customer_trx_id number)
4473: is
4474: select *
4475: from jai_ar_trxs
4476: where customer_trx_id = cn_customer_trx_id;
4477:
4478: t_rec_new jai_ar_trxs%rowtype;
4479: t_rec_old jai_ar_trxs%rowtype;
4474: select *
4475: from jai_ar_trxs
4476: where customer_trx_id = cn_customer_trx_id;
4477:
4478: t_rec_new jai_ar_trxs%rowtype;
4479: t_rec_old jai_ar_trxs%rowtype;
4480:
4481: lv_action VARCHAR2(20) ;
4482: lv_return_message VARCHAR2(2000);
4475: from jai_ar_trxs
4476: where customer_trx_id = cn_customer_trx_id;
4477:
4478: t_rec_new jai_ar_trxs%rowtype;
4479: t_rec_old jai_ar_trxs%rowtype;
4480:
4481: lv_action VARCHAR2(20) ;
4482: lv_return_message VARCHAR2(2000);
4483: lv_return_code VARCHAR2(100) ;
4887:
4888:
4889: CURSOR DUPLICATE_HDR_CUR IS
4890: SELECT 1
4891: FROM JAI_AR_TRXS
4892: WHERE customer_trx_id = v_header_id;
4893:
4894:
4895: /* Cursor To Check Whether Interfaced Record Exist in JAI_OM_WSH_LINES_ALL */
4991: ---------------------------------------------------------
4992: cursor c_get_rec(cn_customer_trx_id number)
4993: is
4994: select *
4995: from jai_ar_trxs
4996: where customer_trx_id = cn_customer_trx_id;
4997:
4998: t_rec_new jai_ar_trxs%rowtype;
4999: t_rec_old jai_ar_trxs%rowtype;
4994: select *
4995: from jai_ar_trxs
4996: where customer_trx_id = cn_customer_trx_id;
4997:
4998: t_rec_new jai_ar_trxs%rowtype;
4999: t_rec_old jai_ar_trxs%rowtype;
5000:
5001: lv_action VARCHAR2(20) ;
5002: lv_return_message VARCHAR2(2000);
4995: from jai_ar_trxs
4996: where customer_trx_id = cn_customer_trx_id;
4997:
4998: t_rec_new jai_ar_trxs%rowtype;
4999: t_rec_old jai_ar_trxs%rowtype;
5000:
5001: lv_action VARCHAR2(20) ;
5002: lv_return_message VARCHAR2(2000);
5003: lv_return_code VARCHAR2(100) ;
5020:
5021: procedure process_bill_only_return is
5022: cursor c_check_hdr_exists is
5023: select 1
5024: from JAI_AR_TRXS
5025: where customer_trx_id = pr_new.customer_trx_id;
5026:
5027: cursor c_trx_cur is
5028: select trx_number ,
5137: Validations to handle
5138: ---------------------
5139: a) in case of discounts only one line should have taxes
5140: b) in case of bond register excise and excise cess taxes should not flow into AR
5141: c) only one record should be inserted in the JAI_AR_TRXS table.
5142: */
5143:
5144: ln_hdr_exists := 0;
5145: open c_check_hdr_exists;
5157: --FND_FILE.PUT_LINE(FND_FILE.LOG,' Header exists '|| ln_hdr_exists);
5158:
5159: if nvl(ln_hdr_exists,0) = 0 then
5160: /*
5161: no record exists in the JAI_AR_TRXS table for the customer trx id ,
5162: hence insert a record into the table.
5163: */
5164:
5165: open c_trx_cur;
5198:
5199: \*
5200: if the default location setup is not done , log this as a message in the JAI_CMN_ERRORS_T table
5201: do not stop the processing.
5202: We could still update the organization and location id in the JAI_AR_TRXS table later on (as a datafix)
5203: *\
5204: lv_appl_src := 'JA_IN_OE_AR_LINES_INSERT_TRG' ;
5205: lv_err_msg := 'Default Location is not setup for Inventory Organization ' || pr_new.warehouse_id ;
5206: lv_addl_msg := 'Please setup the Default Location in Organization Additional Information Screen for Trx id : ' || pr_new.customer_trx_id ;
5227: fnd_global.user_id,
5228: sysdate
5229: );
5230: end if;*/
5231: insert into JAI_AR_TRXS -- bill only invoice
5232: (
5233: CUSTOMER_TRX_ID ,
5234: ORGANIZATION_ID ,
5235: LOCATION_ID ,
5283: 'N' ,
5284: NULL ,
5285: ln_legal_entity_id /* rallamse bug#4448789 */
5286: );
5287: --FND_FILE.PUT_LINE(FND_FILE.LOG, ' After insert into JAI_AR_TRXS - Bill only invoice');
5288: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
5289: FND_LOG.STRING(G_LEVEL_PROCEDURE,
5290: G_MODULE_NAME || l_api_name || '.IN',
5291: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
5287: --FND_FILE.PUT_LINE(FND_FILE.LOG, ' After insert into JAI_AR_TRXS - Bill only invoice');
5288: if (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) then
5289: FND_LOG.STRING(G_LEVEL_PROCEDURE,
5290: G_MODULE_NAME || l_api_name || '.IN',
5291: G_PKG_NAME || ': ' || l_api_name || '().'||' After insert into JAI_AR_TRXS - Bill only invoice'
5292: );
5293: end if;
5294: end if;
5295:
5682: ---------------------------------------------------------
5683: --Added by Zhiwei for JAI Trigger elimination end
5684:
5685:
5686: update JAI_AR_TRXS
5687: set tax_amount = nvl(ln_tax_amount,0) ,
5688: line_amount = nvl(ln_line_amount,0),
5689: total_amount = nvl(ln_line_amount,0) + nvl(ln_tax_amount,0)
5690: where customer_trx_id = pr_new.customer_trx_id;
5698: if(t_rec_new.once_completed_flag = 'Y')then
5699:
5700: lv_action := jai_constants.updating ;
5701:
5702: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
5703: pr_old => t_rec_old ,
5704: pr_new => t_rec_new ,
5705: pv_action => lv_action ,
5706: pv_return_code => lv_return_code ,
5869: or
5870: (nvl(v_ont_source_code,'ORDER ENTRY') <> nvl(ltrim(rtrim(pr_new.interface_line_context)),'$$$')
5871: )
5872: then
5873: INSERT INTO JAI_AR_TRXS
5874: (
5875: Customer_Trx_ID,
5876: Organization_ID,
5877: Location_ID,
6158: END IF;
6159: END IF;
6160: --Added by Qinglei for JAI Trigger Elimination 11-May-2012 end
6161:
6162: DELETE JAI_AR_TRXS
6163: WHERE customer_trx_id = v_old_customer_trx_id;
6164: END IF;
6165: END IF;
6166:
6172: ---------------------------------------------------------
6173: --Added by Zhiwei for JAI Trigger elimination end
6174:
6175:
6176: UPDATE JAI_AR_TRXS
6177: SET line_amount = NVL(line_amount, 0 ) + NVL(v_line_amount,0),
6178: tax_amount = NVL(tax_amount,0) + NVL(v_excise_amount,0),
6179: total_amount = NVL(total_amount,0) + NVL(v_line_amount,0) + NVL(v_excise_amount,0)
6180: WHERE customer_trx_id = v_header_id;
6189: if(t_rec_new.once_completed_flag = 'Y')then
6190:
6191: lv_action := jai_constants.updating ;
6192:
6193: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
6194: pr_old => t_rec_old ,
6195: pr_new => t_rec_new ,
6196: pv_action => lv_action ,
6197: pv_return_code => lv_return_code ,
6264: r_ra_customer_trx c_ra_customer_trx%rowtype;
6265:
6266: cursor c_jai_ra_customer_trx is
6267: select customer_trx_id
6268: from jai_ar_trxs
6269: where customer_trx_id = r_new.customer_trx_id;
6270: r_jai_ra_customer_trx c_jai_ra_customer_trx%rowtype;
6271:
6272: cursor c_jai_tax_line(pn_tax_line_no number) is
6333: ---------------------------------------------------------
6334: cursor c_get_rec(cn_customer_trx_id number)
6335: is
6336: select *
6337: from jai_ar_trxs
6338: where customer_trx_id = cn_customer_trx_id;
6339:
6340: t_rec_new jai_ar_trxs%rowtype;
6341: t_rec_old jai_ar_trxs%rowtype;
6336: select *
6337: from jai_ar_trxs
6338: where customer_trx_id = cn_customer_trx_id;
6339:
6340: t_rec_new jai_ar_trxs%rowtype;
6341: t_rec_old jai_ar_trxs%rowtype;
6342:
6343: lv_action VARCHAR2(20) ;
6344: lv_return_message VARCHAR2(2000);
6337: from jai_ar_trxs
6338: where customer_trx_id = cn_customer_trx_id;
6339:
6340: t_rec_new jai_ar_trxs%rowtype;
6341: t_rec_old jai_ar_trxs%rowtype;
6342:
6343: lv_action VARCHAR2(20) ;
6344: lv_return_message VARCHAR2(2000);
6345: lv_return_code VARCHAR2(100) ;
6410: fetch c_jai_ra_customer_trx into r_jai_ra_customer_trx;
6411: close c_jai_ra_customer_trx;
6412:
6413: if r_jai_ra_customer_trx.customer_trx_id is null then
6414: /* insert into jai_ar_trxs */
6415: insert into jai_ar_trxs(
6416: customer_trx_id
6417: , organization_id
6418: , location_id
6411: close c_jai_ra_customer_trx;
6412:
6413: if r_jai_ra_customer_trx.customer_trx_id is null then
6414: /* insert into jai_ar_trxs */
6415: insert into jai_ar_trxs(
6416: customer_trx_id
6417: , organization_id
6418: , location_id
6419: , trx_number
6645: ---------------------------------------------------------
6646: --Added by Zhiwei for JAI Trigger elimination end
6647:
6648:
6649: update jai_ar_trxs
6650: set tax_amount = nvl(tax_amount,0) + round( nvl(ln_line_tax_amt,0), 2)
6651: , line_amount = nvl(line_amount,0) + round( nvl(ln_line_amount,0), 2)
6652: , total_amount = nvl(total_amount,0) + round(nvl(ln_line_amount,0) + nvl(ln_line_tax_amt,0), 2)
6653: , last_updated_by = r_new.last_updated_by
6664: if(t_rec_new.once_completed_flag = 'Y')then
6665:
6666: lv_action := jai_constants.updating ;
6667:
6668: JAI_AR_TRXS_PKG.UPDATE_EXCISE_REGISTERS(
6669: pr_old => t_rec_old ,
6670: pr_new => t_rec_new ,
6671: pv_action => lv_action ,
6672: pv_return_code => lv_return_code ,
6740: ---------------------------------------------------------
6741: cursor c_get_rec(cn_customer_trx_id number)
6742: is
6743: select *
6744: from jai_ar_trxs
6745: where customer_trx_id = cn_customer_trx_id;
6746:
6747: t_rec_new jai_ar_trxs%rowtype;
6748: t_rec_old jai_ar_trxs%rowtype;
6743: select *
6744: from jai_ar_trxs
6745: where customer_trx_id = cn_customer_trx_id;
6746:
6747: t_rec_new jai_ar_trxs%rowtype;
6748: t_rec_old jai_ar_trxs%rowtype;
6749:
6750: lv_action VARCHAR2(20) ;
6751: lv_return_message VARCHAR2(2000);
6744: from jai_ar_trxs
6745: where customer_trx_id = cn_customer_trx_id;
6746:
6747: t_rec_new jai_ar_trxs%rowtype;
6748: t_rec_old jai_ar_trxs%rowtype;
6749:
6750: lv_action VARCHAR2(20) ;
6751: lv_return_message VARCHAR2(2000);
6752: lv_return_code VARCHAR2(100) ;