[Home] [Help]
100: So modified the cursor c_get_cmref_totrd_amt to calculate this amount as
101: simply sum(recoverable amount).
102:
103: 12 4-Apr-2010 Bo Li for Bug9305067
104: Replace the old attribute columns of JAI_RGM_TRX_RECORDS with the new meaningful columns
105:
106: 13. 27-Jul-2010 Bug 9919612
107: Issue: If an AR Invoice is created with just Adhoc Service Tax(with no Line Amount) then
108: divisor by zero error is thrown
117: ar_activity_details and prorated the taxes accordingly
118:
119: 15. Oct 29, 2010 Bug 10230041 fixed by Eric Ma
120:
121: The credit memo entries are missing in both jai_rgm_trx_records and gl_interface tables
122: when applying the receipt with CM and Invoice.
123: Added REVERSAL_FLAG LOGIC by 3 Ma for bug 10230041 on Oct-28-2010
124:
125: 16. Jan 04, 2011 Bug 10623735
128: is not updated with the correct amounts
129: + If there are multiple applications and unapplication, repository entries
130: are corrupt
131: Fix:
132: + JAI_RGM_TRX_RECORDS is populated based on line_applied and tax_applied columns
133: in AR_RECEIVABLE_APPLICATIONS_ALL. In case of currency difference between Invoice and
134: Receipt, the Invoice currency is used to populate AR_RECEIVABLE_APPLICATIONS_ALL.
135: This was not considered when calculating Transaction Amount.
136: Multiplied by Invoice Exchange Rate if Invoice is in foreign currency or divided
1192: ,p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
1193: IS
1194:
1195: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1196: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1197: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1198: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1199: ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1200: ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1193: IS
1194:
1195: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1196: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1197: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1198: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1199: ln_err_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1200: ln_set_save_point JAI_RGM_TRX_REFS.INVOICE_ID%TYPE ;
1201: lv_process_flag VARCHAR2(2) ;
1201: lv_process_flag VARCHAR2(2) ;
1202: lv_process_message VARCHAR2(1996) ;
1203: lv_source_trx_type VARCHAR2(50) ;
1204: ln_uncommitted_transactions NUMBER(10) := 0 ;
1205: lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ; --rchandan for bug#4428980
1206: lv_source_table jai_rgm_trx_records.source_table_name%TYPE ; --rchandan for bug#4428980
1207:
1208: /* Added by Ramananda for bug#4407165 */
1209: lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_rgm_processing_pkg.delete_non_existant_cm';
1202: lv_process_message VARCHAR2(1996) ;
1203: lv_source_trx_type VARCHAR2(50) ;
1204: ln_uncommitted_transactions NUMBER(10) := 0 ;
1205: lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ; --rchandan for bug#4428980
1206: lv_source_table jai_rgm_trx_records.source_table_name%TYPE ; --rchandan for bug#4428980
1207:
1208: /* Added by Ramananda for bug#4407165 */
1209: lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_rgm_processing_pkg.delete_non_existant_cm';
1210:
1244: /*
1245: || Get all the data of the incompleted CM from the
1246: || repository, so that the same record with an exactly opposite amount can be passed. This would be the CM-CM-REV record
1247: */
1248: CURSOR c_get_cm_cm_app_rec ( /*cp_cm_reference_id JAI_RGM_TRX_RECORDS.REFERENCE_ID%TYPE ,*/
1249: cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE,
1250: cp_attribute_context jai_rgm_trx_records.trx_reference_context%TYPE --rchandan for bug#4428980 --Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
1251: )
1252: IS
1246: || repository, so that the same record with an exactly opposite amount can be passed. This would be the CM-CM-REV record
1247: */
1248: CURSOR c_get_cm_cm_app_rec ( /*cp_cm_reference_id JAI_RGM_TRX_RECORDS.REFERENCE_ID%TYPE ,*/
1249: cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE,
1250: cp_attribute_context jai_rgm_trx_records.trx_reference_context%TYPE --rchandan for bug#4428980 --Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
1251: )
1252: IS
1253: SELECT
1254: *
1252: IS
1253: SELECT
1254: *
1255: FROM
1256: jai_rgm_trx_records
1257: WHERE
1258: trx_reference_context = cp_attribute_context AND --Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
1259: trx_reference2 = cp_cm_customer_trx_id --Bo Li for Bug9305067 change attribute2 to trx_reference2
1260: /* AND reference_id = cp_cm_reference_id */;
1276: || Get all the data of the CM applied to invoices (i.e CM-INV-APP) from the repository.
1277: || So that the same record with an exactly opposite amount can be passed. This would be the new CM-INV-REV record
1278: */
1279: CURSOR c_get_cm_inv_app_rec ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE
1280: -- cp_attribute_context jai_rgm_trx_records.attribute_context%TYPE ) --Commented by Xiao for bug#6773751
1281: )
1282: IS
1283: SELECT
1284: *
1282: IS
1283: SELECT
1284: *
1285: FROM
1286: jai_rgm_trx_records
1287: WHERE
1288: --attribute_context = cp_attribute_context AND
1289: trx_reference_context in ('CM-INV-APP','CM-DM-APP') AND --added CM-DM-APP by Xiao for bug#6773751--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
1290: trx_reference2 = cp_cm_customer_trx_id ; --Bo Li for Bug9305067 change attribute2 to trx_reference2
1554: --end added by Xiao for bug#6773751
1555:
1556:
1557: --lv_attribute_context := 'CM-INV-REV'; --rchandan for bug#4428980 -- commented by Xiao for bug#6773751
1558: lv_source_table := 'JAI_RGM_TRX_RECORDS'; --rchandan for bug#4428980
1559: jai_cmn_rgm_recording_pkg.insert_repository_entry (
1560: p_repository_id => ln_repository_id ,
1561: p_regime_id => p_regime_id ,
1562: p_tax_type => rec_c_get_cm_inv_app_rec.tax_type ,
1693:
1694: ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
1695: ln_discounted_amount JAI_RGM_TRX_REFS.DISCOUNTED_AMOUNT%TYPE ;
1696: ln_cm_ref_upd JAI_RGM_TRX_REFS.RECOVERED_AMOUNT%TYPE ;
1697: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
1698: lv_source_trx_type VARCHAR2(50) ;
1699: ln_eff_cm_tottax_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1700: ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1701: ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1700: ln_tot_effcm_rb_amt JAI_RGM_TRX_REFS.RECOVERABLE_AMOUNT%TYPE ;
1701: ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
1702: ln_sign_of_credit_memo NUMBER ;
1703: ln_cm_ref_ratio NUMBER ;
1704: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1705: -- Added ln_func_amount for Bug 7522584
1706: ln_func_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1707: ln_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE := -1 ;
1708: lv_process_flag VARCHAR2(2) ;
1702: ln_sign_of_credit_memo NUMBER ;
1703: ln_cm_ref_ratio NUMBER ;
1704: ln_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1705: -- Added ln_func_amount for Bug 7522584
1706: ln_func_amount JAI_RGM_TRX_RECORDS.DEBIT_AMOUNT%TYPE ;
1707: ln_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE := -1 ;
1708: lv_process_flag VARCHAR2(2) ;
1709: lv_process_message VARCHAR2(1996) ;
1710: ln_uncommitted_transactions NUMBER(10) := 0 ;
1711: lv_service_type_code JAI_AR_TRX_LINES.service_type_code%TYPE ;/*5879769*/
1712: lv_application_type ar_receivable_applications_all.application_type%TYPE ;--rchandan for bug#4428980
1713: lv_status ar_receivable_applications_all.status%TYPE ;--rchandan for bug#4428980
1714: lv_type ra_cust_trx_types_all.type%TYPE ;--rchandan for bug#4428980
1715: lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AR_RECEIVABLE_APPLICATIONS_ALL';--rchandan for bug#4428980
1716: lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.POPULATE_CM_APP';--rchandan for bug#4428980
1717: lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ;--rchandan for bug#4428980
1718:
1719: ln_total_tax_applied NUMBER; --added by walton for inclusive tax 29-Nov-07
1713: lv_status ar_receivable_applications_all.status%TYPE ;--rchandan for bug#4428980
1714: lv_type ra_cust_trx_types_all.type%TYPE ;--rchandan for bug#4428980
1715: lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AR_RECEIVABLE_APPLICATIONS_ALL';--rchandan for bug#4428980
1716: lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.POPULATE_CM_APP';--rchandan for bug#4428980
1717: lv_attribute_context jai_rgm_trx_records.attribute_context%TYPE ;--rchandan for bug#4428980
1718:
1719: ln_total_tax_applied NUMBER; --added by walton for inclusive tax 29-Nov-07
1720: ln_line_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1721: ln_inclusive_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
1728: /*
1729: || Get the credit memo applications to invoices and the total invoice tax amount .
1730: || Consider only those credit memo applications which follow the following conditions:-
1731: || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
1732: || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
1733: || 3. Consider cash receipt application against an invoice only
1734: || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount - discounted_amount in references table
1735: || 5. Processes DebitMemo for bug#6773751, add by Xiao on 20-Dec-09.
1736: */
1786: inv_jtrx.organization_id = p_organization_id AND/*5879769*/
1787: NOT EXISTS ( SELECT /*A credit memo application does not exist in repository */
1788: 1
1789: FROM
1790: jai_rgm_trx_records rgtr
1791: WHERE
1792: rgtr.source = cp_source_ar AND
1793: rgtr.organization_id = p_organization_id AND -- Date 05/06/2007 by sacsethi for bug 6109941
1794: rgtr.source_table_name = lv_source_table AND--rchandan for bug#4428980
2032:
2033: /*
2034: || If the CM tax amount is +ve then it is CREDIT MEMO REVERSAL/UNAPPLICATION
2035: || IF the CM tax receipt amount is -ve then it is CREDIT MEMO APPLICATION
2036: || This info would go into the source_trx_type in the repository table jai_rgm_trx_records .
2037: || This would help distinctly identify a CM application, CREDIT MEMO reversal
2038: || IF sign = 0 i.e CM amount = 0 then skip the cm application and proceed to the next
2039: */
2040:
2113: /* ln_amount := abs(ln_eff_cm_tax_amount) * ln_sign_of_credit_memo ; */
2114: ln_amount := abs(ln_eff_cm_tax_amount) ;
2115: /*Bug 10623735
2116: If Invoice and Credit Memo currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
2117: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
2118: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Credit Memo Currency for CM Application Event.
2119: Hence multiplied by Invoice Exchange Rate if Invoice is in foreign currency and divided
2120: by CM Exchange Rate if CM is in foreign currency
2121: */
2114: ln_amount := abs(ln_eff_cm_tax_amount) ;
2115: /*Bug 10623735
2116: If Invoice and Credit Memo currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
2117: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
2118: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Credit Memo Currency for CM Application Event.
2119: Hence multiplied by Invoice Exchange Rate if Invoice is in foreign currency and divided
2120: by CM Exchange Rate if CM is in foreign currency
2121: */
2122: IF (rec_c_get_cm_rec_app.invoice_currency_code <> 'INR'
2563:
2564: ln_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE ;
2565: ln_eff_cr_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
2566: ln_eff_cr_disc_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_EDISCOUNTED%TYPE ;
2567: ln_repository_id JAI_RGM_TRX_RECORDS.REPOSITORY_ID%TYPE ;
2568: ln_inv_tot_tax_amt JAI_AR_TRX_TAX_LINES.TAX_AMOUNT%TYPE ;
2569: -- Added for Bug 7522584
2570: ln_func_tax_amt NUMBER ;
2571: lv_process_flag VARCHAR2(2) ;
2574: lv_attribute_context VARCHAR2(50) ;
2575: ln_uncommitted_transactions NUMBER(10) := 0 ;
2576: lv_service_type_code JAI_AR_TRX_LINES.service_type_code%TYPE ;/*5879769*/
2577: ln_location_id NUMBER(15);/*5879769*/
2578: lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'AR_RECEIVABLE_APPLICATIONS_ALL';--rchandan for bug#4428980
2579: lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.POPULATE_RECEIPT_RECORDS';--rchandan for bug#4428980
2580:
2581: ln_total_tax_applied NUMBER; --added by walton for inclusive tax 29-Nov-07
2582: ln_line_total_amt NUMBER; --added by walton for inclusive tax 29-Nov-07
2589: /*
2590: || Get the cash receipt, Invoice and Total Effective Invoice Recoverable Amount for them.
2591: || Consider only those cash receipts which follow the conditions as given below:-
2592: || 1. Invoice tax line reference exists in the jai_rgm_trx_refs table for the invoice against which the cash receipt is being applied
2593: || 2. The cash receipt tax line does not already exist in the repository i.e jai_rgm_trx_records.
2594: || 3. Consider cash receipt application against an invoice only
2595: || 4. Invoice tax line has not been fully recovered i.e recovered_amount < recoverable_amount in references table
2596: || 5.Consider Cash Receipt application for DM ,bug #6773751
2597: */
2647: trx.customer_trx_id = jtrx.customer_trx_id AND
2648: NOT EXISTS ( SELECT /*A receipt application does not exist in repository */
2649: 1
2650: FROM
2651: jai_rgm_trx_records rgtr
2652: WHERE
2653: rgtr.source = cp_source_ar AND
2654: rgtr.organization_id = p_organization_id AND/*5879769*/
2655: rgtr.source_table_name = lv_source_table AND
2963:
2964: /*
2965: || If the cash receipt tax amount is -ve then it is RECEIPT REVERSAL/UNAPPLICATION
2966: || IF the cash receipt tax receipt amount is +ve then it is RECEIPT APPLICATION
2967: || This info would go into the source_trx_type in the repository table jai_rgm_trx_records .
2968: || This would help distinctly identify a receipt application, receipt reversal and a credit memo
2969: || IF sign = 0 i.e cash receipt amount = 0 then proceed exit the current loop and proceed with the next receivable application.
2970: */
2971: IF rec_c_get_rec_app.sign_of_cash_receipt > 0 or ln_total_tax_applied >0 THEN --modified by walton for inclusive tax 29-Nov-07
3185:
3186: fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
3187: /*Bug 10623735
3188: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
3189: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
3190: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
3191: Hence multiplied by Invoice Exchange Rate if Invoice is in foreign currency and divided
3192: by Receipt Exchange Rate if Receipt is in foreign currency
3193: */
3186: fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
3187: /*Bug 10623735
3188: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
3189: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
3190: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
3191: Hence multiplied by Invoice Exchange Rate if Invoice is in foreign currency and divided
3192: by Receipt Exchange Rate if Receipt is in foreign currency
3193: */
3194: IF (rec_c_get_rec_app.invoice_currency_code <> 'INR'
3391: p_process_message OUT NOCOPY VARCHAR2 ,
3392: p_organization_id IN JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
3393: IS
3394: /*Variables*/
3395: lv_source_table CONSTANT jai_rgm_trx_records.source_table_name%TYPE := 'CUSTOMER_TRX_LINE_ALL';
3396: lv_called_from CONSTANT varchar2(100) := 'jai_ar_rgm_processing_pkg.populate_repository';
3397: ln_repository_id NUMBER;
3398: ln_discounted_amt NUMBER := 0;
3399:
3417: trx_types.org_id = trx.org_id AND
3418: trx.customer_trx_id = jtrx.customer_trx_id AND
3419: trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
3420: NOT EXISTS ( SELECT 1
3421: FROM jai_rgm_trx_records rgtr
3422: WHERE
3423: rgtr.source = cp_source_ar AND
3424: rgtr.organization_id = p_organization_id AND
3425: rgtr.source_table_name = lv_source_table AND
3482: ----------------------------------------------
3483: --Added by Qiong for bug13540741 end
3484:
3485: AND NOT EXISTS (SELECT 1
3486: FROM jai_rgm_trx_records rgtr
3487: WHERE rgtr.source = cp_source_ar
3488: AND rgtr.organization_id = p_organization_id
3489: AND rgtr.source_table_name = lv_source_table
3490: AND rgtr.source_document_id = jattl.customer_trx_line_id)
3518: AND jattl.tax_id = jcta.tax_id
3519: AND nvl(jcta.mod_cr_percentage,0) > 0
3520: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
3521: AND NOT EXISTS (SELECT 1
3522: FROM jai_rgm_trx_records
3523: WHERE SOURCE = 'AR_REVERSAL'
3524: AND source_table_name = 'CUSTOMER_TRX_LINE_ALL'
3525: AND source_document_id = jattl.customer_trx_line_id);
3526:
3556: AND jattl.tax_id = jcta.tax_id
3557: AND nvl(jcta.mod_cr_percentage,0) > 0
3558: AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
3559: AND EXISTS (SELECT 1
3560: FROM jai_rgm_trx_records
3561: WHERE SOURCE = 'AR_REVERSAL'
3562: AND source_table_name = 'CUSTOMER_TRX_LINE_ALL'
3563: AND source_document_id = jattl.customer_trx_line_id);
3564:
3582: AND araa.receivable_application_id > nvl(pn_max_payment_id, 0)
3583: AND trunc(araa.apply_date) <= p_to_date
3584: AND araa.status = 'APP'
3585: AND NOT EXISTS (SELECT 1
3586: FROM jai_rgm_trx_records jrtr
3587: WHERE jrtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
3588: AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
3589: AND jrtr.source = 'AR_CLAIM'
3590: AND jrtr.source_document_id = araa.receivable_application_id)
3618: AND jcra.organization_id = p_organization_id
3619: AND jcra.org_id = nvl(p_org_id,jcra.org_id)
3620: AND jcra.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
3621: AND NOT EXISTS (SELECT 1
3622: FROM jai_rgm_trx_records rgtr
3623: WHERE rgtr.source = 'AR'
3624: AND rgtr.organization_id = p_organization_id
3625: AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
3626: AND rgtr.source_trx_type = jai_constants.trx_type_adv_rcpts
3685: AND acrh.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
3686: ---------------------------------------
3687: --Added by qiong for bug13555753 end
3688: AND NOT EXISTS (SELECT 1
3689: FROM jai_rgm_trx_records rgtr
3690: WHERE rgtr.source = 'AR'
3691: AND rgtr.organization_id = p_organization_id
3692: AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
3693: AND rgtr.source_trx_type = jai_constants.trx_type_adv_rvs
3749: AND jcra.organization_id = p_organization_id
3750: AND jcra.org_id = nvl(p_org_id,jcra.org_id)
3751: AND araa.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)--Need confirm use creation date/apply date
3752: AND NOT EXISTS (SELECT 1
3753: FROM jai_rgm_trx_records rgtr
3754: WHERE rgtr.source = 'AR'
3755: AND rgtr.organization_id = p_organization_id
3756: AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
3757: AND rgtr.source_trx_type = jai_constants.trx_type_adv_app
3811: AND jcra.organization_id = p_organization_id
3812: AND jcra.org_id = nvl(p_org_id,jcra.org_id)
3813: AND araa.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
3814: AND NOT EXISTS (SELECT 1
3815: FROM jai_rgm_trx_records rgtr
3816: WHERE rgtr.source = 'AR'
3817: AND rgtr.organization_id = p_organization_id
3818: AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
3819: AND rgtr.source_trx_type = jai_constants.trx_type_adv_unapp
4352: CLOSE c_rcpt_ref_records;
4353:
4354: /*
4355: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
4356: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
4357: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
4358: */
4359: ln_tax_amount := -ROUND(ABS(rec_receipt_entries.func_tax_amt
4360: * (rec_c_receipts_application.AMOUNT_APPLIED_FROM/
4353:
4354: /*
4355: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
4356: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
4357: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
4358: */
4359: ln_tax_amount := -ROUND(ABS(rec_receipt_entries.func_tax_amt
4360: * (rec_c_receipts_application.AMOUNT_APPLIED_FROM/
4361: (rec_c_receipts_application.AMOUNT * NVL(ln_converted_rate,1)))),
4457: CLOSE c_rcpt_ref_records;
4458:
4459: /*
4460: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
4461: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
4462: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
4463: */
4464: ln_tax_amount := ROUND(ABS(rec_receipt_entries.func_tax_amt
4465: * (rec_c_receipts_unapplication.AMOUNT_APPLIED_FROM/
4458:
4459: /*
4460: If Invoice and Receipt currency are different, data in AR_RECEIVABLE_APPLICATIONS_ALL is populated
4461: in Invoice Currency. Hence the same cannot be passed as Transaction Amount in JAI_RGM_TRX_RECORDS
4462: Transaction Amount Data in JAI_RGM_TRX_RECORDS must be in Receipt Currency for Receipt Application Event.
4463: */
4464: ln_tax_amount := ROUND(ABS(rec_receipt_entries.func_tax_amt
4465: * (rec_c_receipts_unapplication.AMOUNT_APPLIED_FROM/
4466: (rec_c_receipts_unapplication.AMOUNT * NVL(ln_converted_rate,1)))),
4566: AND araa.apply_date <= pd_to_date
4567: AND araa.status = 'APP'
4568: AND araa.receivable_application_id > nvl(pn_max_payment_id, 0)
4569: AND NOT EXISTS (SELECT 1
4570: FROM jai_rgm_trx_records jrtr
4571: WHERE jrtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
4572: AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
4573: AND jrtr.source = 'AR_CLAIM'
4574: AND jrtr.source_document_id = araa.receivable_application_id)