[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
regime_id
FROM
JAI_RGM_DEFINITIONS
WHERE
regime_code = p_regime_code;
SELECT
meaning
FROM
fnd_lookup_values
WHERE
lookup_type = cp_lookup_type AND
lookup_code = cp_lookup_code;
SELECT
1
FROM
JAI_RGM_REGISTRATIONS
WHERE
regime_id = cp_regime_id AND
upper(registration_type) = upper(jai_constants.regn_type_tax_types) AND
upper(attribute_code) = upper(cp_tax_type);
Called From : jai_cmn_rgm_recording_pkg.insert_reference
Change History: -
=================
1 20-Feb-2005 aiyer - Bug # 4193633 - File Version# 115.1
Issue
The tax earned and unearned discount are not getting apportioned properly of service type of taxes and hence the India - Service Tax concurrent
ends up in a warning for records with these issues
Reason:-
In case of invoices having Service taxes and other type of taxes, the tax earned and unearned discounts should be approtioned across all the type of taxes
(Both Service and Non Service).
This apportionment logic was not present initially. This needs to be added
Fix: -
Modified the procedure. Did the following :-
1. Added a extra parameter p_total_disc_amount to the procedure.
2. used this parameter to apportion the tax earned discount amount and tax unearned discount amount
Dependency Due To This Bug:
Dependency exists due to specification change of the current procedure.
Always sent the following packages together:-
1. jai_rgm_process_ar_taxes_pkg_s.sql (115.1)
2. jai_rgm_process_ar_taxes_pkg_b.sql (115.1)
3. jai_rgm_trx_recording_pkg_s.sql version (115.1)
4. jai_rgm_trx_recording_pkg_b.sql version (115.1)
2. 17/04/2007 Bgowrava for forward porting bug#5989740, 11i BUG#5907436. File Version 120.2
ENH: Handling Secondary and Higher Education Cess
Added a input paramter cp_sh_service_edu_cess to the cursor c_fetch_inv_cm_rec.
3. 05/06/2007 sacsethi for bug 6109941
R12RUP03-ST1: CODE REVIEW COMMENTS FOR ENHANCEMENTS
Some code was found which missed during fp of bug 5879769
*****************************************************************************************************************************************************************/
IS
CURSOR cur_get_receivable_app
IS
SELECT
nvl(tax_ediscounted,0) tax_ediscounted ,
nvl(tax_uediscounted,0) tax_uediscounted
FROM
ar_receivable_applications_all
WHERE
receivable_application_id = p_receivable_application_id AND
org_id = p_org_id;
SELECT
code_combination_id
FROM
ar_receivables_trx_all rtrx,
ar_lookups lkup
WHERE
rtrx.type = lkup.lookup_code AND
lkup.lookup_code = cp_type AND
lkup.lookup_type = p_lookup_type AND --rchandan for bug#4428980
org_id = p_org_id AND
status = p_status; --rchandan for bug#4428980
SELECT
trx.customer_trx_id ,
trx.invoice_currency_code ,
trx.exchange_date ,
trx.exchange_rate ,
trx.org_id ,
trx.cust_trx_type_id ,
trx.previous_customer_trx_id ,
nvl(trx.bill_to_customer_id,trx.ship_to_customer_id) customer_id ,
nvl(trx.bill_to_site_use_id,trx.ship_to_site_use_id) customer_site_id ,
jtc.tax_type tax_type ,
nvl(decode(upper(trx_types.type),cp_cm_type,'Y','N'),'N') reversal_flag ,
jtrxl.inventory_item_id ,
jtrxtl.customer_trx_line_id ,
jtrxtl.tax_id ,
jtrxtl.tax_rate ,
jtrxtl.tax_amount ,
jtrxtl.func_tax_amount ,
jtrxtl.base_tax_amount ,
decode(upper(trx_types.type),cp_invoice_type,nvl(jtc.mod_cr_percentage,0),100) mod_cr_percentage ,
jtrxtl.link_to_cust_trx_line_id
FROM
ra_customer_trx_all trx ,
JAI_AR_TRXS jtrx ,
ra_cust_trx_types_all trx_types ,
JAI_AR_TRX_LINES jtrxl ,
JAI_AR_TRX_TAX_LINES jtrxtl ,
JAI_CMN_TAXES_ALL jtc
WHERE
trx.org_id = nvl(p_org_id,trx.org_id) AND
trx.complete_flag ='Y' AND
trx.customer_trx_id = jtrx.customer_trx_id AND
jtrx.organization_id = p_organization_id AND/*5879769*/
/* nvl(jtrx.tax_amount,0) <> 0 AND *//*Safeguard against invoice tax amount being null or zero and service type of taxes still existing at tax level */
/*trunc(trx.creation_date)*/ trunc(trx.trx_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND /*Commented by nprashar for bug # 6997453*/
upper(trx_types.type) IN (cp_invoice_type,cp_cm_type) AND
trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
trx_types.org_id = trx.org_id AND
jtrx.customer_trx_id = jtrxl.customer_trx_id AND
jtrxl.customer_trx_line_id = jtrxtl.link_to_cust_trx_line_id AND
jtrxtl.tax_id = jtc.tax_id AND
upper(jtc.tax_type) IN ( cp_service_tax,cp_service_edu_cess ,cp_sh_service_edu_cess) AND -- cp_sh_service_edu_cess Bgowrava for forward porting bug#5989740 AND
( /**** Check that in case of INV mod_Cr_percentage should be > 0 and no check in case of CM ****/
(
upper(trx_types.type) = cp_cm_type
) OR
(
upper(trx_types.type) = cp_invoice_type AND
nvl(jtc.mod_cr_percentage,0) > 0
)
) AND
NOT EXISTS ( SELECT /*A ref of invoice/cm should not exist in the reference table */
1
FROM jai_rgm_trx_refs rgtr
WHERE
rgtr.source = p_source AND
rgtr.invoice_id = trx.customer_trx_id AND
rgtr.line_id = jtrxtl.customer_trx_line_id AND
rgtr.item_line_id = jtrxtl.link_to_cust_trx_line_id AND
rgtr.tax_id = jtrxtl.tax_id
)
ORDER BY
trx_types.type desc;
||insert the invoices and credit memo's into the jai_rgm_trx_refs
||using the procedure jai_cmn_rgm_recording_pkg.insert_reference
*****/
fnd_file.put_line(fnd_file.LOG,'3 before call to procedure jai_cmn_rgm_recording_pkg.insert_reference ');
jai_cmn_rgm_recording_pkg.insert_reference (
p_reference_id => ln_reference_id ,
p_organization_id => p_organization_id ,/*5879769*/
p_source => p_source ,
p_invoice_id => rec_c_fetch_inv_cm_rec.customer_trx_id ,
p_line_id => rec_c_fetch_inv_cm_rec.customer_trx_line_id ,
p_tax_type => rec_c_fetch_inv_cm_rec.tax_type ,
p_tax_id => rec_c_fetch_inv_cm_rec.tax_id ,
p_tax_rate => rec_c_fetch_inv_cm_rec.tax_rate ,
p_recoverable_ptg => rec_c_fetch_inv_cm_rec.mod_cr_percentage ,
p_party_type => jai_constants.party_type_customer ,
p_party_id => rec_c_fetch_inv_cm_rec.customer_id ,
p_party_site_id => rec_c_fetch_inv_cm_rec.customer_site_id ,
p_trx_tax_amount => rec_c_fetch_inv_cm_rec.tax_amount ,
p_trx_currency => rec_c_fetch_inv_cm_rec.invoice_currency_code ,
p_curr_conv_date => rec_c_fetch_inv_cm_rec.exchange_date ,
p_curr_conv_rate => rec_c_fetch_inv_cm_rec.exchange_rate ,
p_tax_amount => rec_c_fetch_inv_cm_rec.tax_amount * (rec_c_fetch_inv_cm_rec.mod_cr_percentage/100) ,
p_recoverable_amount => rec_c_fetch_inv_cm_rec.tax_amount * (rec_c_fetch_inv_cm_rec.mod_cr_percentage/100) ,
p_recovered_amount => 0 ,
p_item_line_id => rec_c_fetch_inv_cm_rec.link_to_cust_trx_line_id ,
p_item_id => rec_c_fetch_inv_cm_rec.inventory_item_id ,
p_taxable_basis => rec_c_fetch_inv_cm_rec.base_tax_amount ,
p_parent_reference_id => NULL ,
p_reversal_flag => rec_c_fetch_inv_cm_rec.reversal_flag ,
p_batch_id => p_batch_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line(fnd_file.LOG,'4 returned from procedure jai_cmn_rgm_recording_pkg.insert_reference, lv_process_flag - '||lv_process_flag
||'lv_process_message - '||lv_process_message);
fnd_file.put_line( fnd_file.log, '5 error in call to jai_cmn_rgm_recording_pkg.insert_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,'6 inserted record in jai_rgm_trx_refs with reference_id '||ln_reference_id );
procedure delete_non_existant_cm ( p_regime_id IN JAI_RGM_DEFINITIONS.REGIME_ID%TYPE ,
p_organization_type IN JAI_RGM_PARTIES.ORGANIZATION_TYPE%TYPE ,
p_from_date IN DATE ,
p_to_date IN DATE ,
p_org_id IN RA_CUSTOMER_TRX_ALL.ORG_ID%TYPE ,
p_source IN varchar2, --File.Sql.35 Cbabu jai_constants.SOURCE_AR%TYPE ,
p_batch_id IN JAI_RGM_TRX_REFS.BATCH_ID%TYPE ,
p_process_flag OUT NOCOPY VARCHAR2 ,
p_process_message OUT NOCOPY VARCHAR2
,p_organization_id JAI_RGM_PARTIES.ORGANIZATION_ID%TYPE DEFAULT NULL)
IS
ln_eff_cm_tax_amount AR_RECEIVABLE_APPLICATIONS_ALL.TAX_APPLIED%TYPE ;
lv_object_name CONSTANT VARCHAR2(61) := 'jai_ar_rgm_processing_pkg.delete_non_existant_cm';
|| Get all the cm's which exist in the reference table jai_rgm_trx_refs and have been incompleted/incompleted and deleted from AR base table
|| IF a CM has been incompleted then it would exist with complete_flag = 'N' . if a CM has been incompleted and deleted then it would not exist
|| in the ra_customer_trx_all table.
*/
CURSOR c_get_incompleted_cm
IS
SELECT
rgtr.invoice_id cm_customer_trx_id /*,
rgtr.reference_id cm_reference_id */
FROM
jai_rgm_trx_refs rgtr
WHERE
rgtr.source = p_source AND
rgtr.organization_id = p_organization_id AND /*5879769*/
rgtr.reversal_flag = 'Y' AND
nvl(rgtr.recovered_amount,0) <> 0 AND
NOT EXISTS ( SELECT
1
FROM
ra_customer_trx_all trx ,
ra_cust_trx_types_all trx_types
WHERE
trx.customer_trx_id = rgtr.invoice_id AND
trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
trx_types.org_id = trx.org_id AND
upper(trx_types.type) = upper(jai_constants.ar_invoice_type_cm) AND
trx.complete_flag = 'Y'
)
GROUP BY
rgtr.invoice_id;
SELECT
*
FROM
jai_rgm_trx_records
WHERE
attribute_context = cp_attribute_context AND
attribute2 = cp_cm_customer_trx_id /* AND
reference_id = cp_cm_reference_id */;
|| Update all the credit memo reference records.
*/
CURSOR cur_upd_cm_ref ( cp_cm_customer_trx_id JAI_RGM_TRX_REFS.INVOICE_ID%TYPE )
IS
SELECT
*
FROM
jai_rgm_trx_refs
WHERE
invoice_id = cp_cm_customer_trx_id AND
nvl(recovered_amount,0) <> 0 ;
SELECT
*
FROM
jai_rgm_trx_records
WHERE
attribute_context = cp_attribute_context AND
attribute2 = cp_cm_customer_trx_id ;
fnd_file.put_line(fnd_file.LOG,'delete_non_existant_cm p_org_id:'||p_org_id );
|| Update all the credit memo from reference, reset recovered_amount = 0
|| Insert repository records ('CM-CM-APP') corresponding to the above effect
*/
FOR rec_c_get_incompleted_cm IN c_get_incompleted_cm
LOOP
/*########################################################################################################
|| SET SAVE POINT POINT FOR EACH CM_CUSTOMER_TRX_ID RECORD
########################################################################################################*/
fnd_file.put_line(fnd_file.LOG,' ********************1 PROCESSING REC_C_GET_INCOMPLETED_CM.CM_CUSTOMER_TRX_ID -> '||rec_c_get_incompleted_cm.cm_customer_trx_id
||' ******************** ');
|| PASS CM-CM-REV RECORD ENTRIES IN REPOSITORY AND UPDATE THE CM REFERENCES
|| Insert Credit Memo repository entries to the effect of the CM incompletion.
|| This record would be exactly opposite of the earlier CM creation repository record
########################################################################################################*/
IF nvl(ln_err_cm_customer_trx_id,-1) <> nvl(rec_c_get_incompleted_cm.cm_customer_trx_id,-1) THEN
FOR rec_c_get_cm_cm_app_rec IN c_get_cm_cm_app_rec ( /*cp_cm_reference_id => rec_c_get_incompleted_cm.cm_reference_id ,*/
cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id,
cp_attribute_context => 'CM-CM-APP'
) --rchandan for bug#4428980
LOOP
/*
|| Insert a record into the repository corresponding to the 'CM-CM-REV'
*/
fnd_file.put_line(fnd_file.LOG,' 3 Passing CM-CM-REV record, for the CM-CM-APP with rec_c_get_cm_cm_app_rec.cm_customer_trx_id -> '||rec_c_get_cm_cm_app_rec.attribute2
||' and reference_id -> '||rec_c_get_cm_cm_app_rec.reference_id
||',repository_id -> '|| rec_c_get_cm_cm_app_rec.repository_id);
fnd_file.put_line(fnd_file.LOG,' 3.1 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry original amount -> '||nvl(rec_c_get_cm_cm_app_rec.debit_amount,rec_c_get_cm_cm_app_rec.credit_amount)
||', reversal entry amount -> '||ln_amount );
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => rec_c_get_cm_cm_app_rec.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => p_organization_id ,/*5879769*/
p_location_id => ln_location_id ,/*5879769*/
p_service_type_code => lv_service_type_code ,/*5879769*/
p_source => p_source ,
p_source_trx_type => lv_source_trx_type ,
p_source_table_name => UPPER(jai_constants.repository_name) ,
p_source_document_id => rec_c_get_cm_cm_app_rec.repository_id ,
p_transaction_date => rec_c_get_cm_cm_app_rec.creation_date ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => ln_amount ,
p_assessable_value => NULL ,
p_tax_rate => rec_c_get_cm_cm_app_rec.tax_rate ,
p_reference_id => rec_c_get_cm_cm_app_rec.reference_id ,
p_batch_id => p_batch_id ,
p_called_from => lv_object_name , --rchandan for bug#4428980
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message ,
p_discounted_amount => ln_discounted_amount ,
p_inv_organization_id => rec_c_get_cm_cm_app_rec.inv_organization_id ,
p_accounting_date => sysdate ,
p_currency_code => rec_c_get_cm_cm_app_rec.trx_currency ,
p_curr_conv_date => rec_c_get_cm_cm_app_rec.curr_conv_date ,
p_curr_conv_type => NULL ,
p_curr_conv_rate => rec_c_get_cm_cm_app_rec.curr_conv_rate ,
p_trx_amount => ln_amount ,
p_attribute_context => lv_attribute_context ,
p_attribute2 => rec_c_get_incompleted_cm.cm_customer_trx_id
, p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.LOG,' 4 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line( fnd_file.log, '5 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message ||'cm_customer_trx_id - '||ln_err_cm_customer_trx_id);
|| Update the Credit Reference and set Recovered Amount to 0 as this credit memo has been incompleted
########################################################################################################*/
FOR rec_cur_upd_cm_ref IN cur_upd_cm_ref (cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id)
LOOP
fnd_file.put_line(fnd_file.LOG,' 6 before call to jai_cmn_rgm_recording_pkg.update_reference for updating CM reference to 0-> '||rec_c_get_incompleted_cm.cm_customer_trx_id
||', reference_id -> '||rec_cur_upd_cm_ref.reference_id );
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => rec_cur_upd_cm_ref.reference_id ,
p_recovered_amount => rec_cur_upd_cm_ref.recovered_amount * (-1) ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line( fnd_file.log, '7 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,' 8 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating CM reference' );
|| PASS CM-INV-REV ENTRIES , UPDATE INV REFERENCES
|| Reverse CM application to invoices into repository entries to the effect of the CM incompletion.
|| This record would be exactly opposite of the earlier CM creation repository record
########################################################################################################*/
IF nvl(ln_err_cm_customer_trx_id,-1) <> nvl(rec_c_get_incompleted_cm.cm_customer_trx_id,-1) THEN
FOR rec_c_get_cm_inv_app_rec IN c_get_cm_inv_app_rec ( cp_cm_customer_trx_id => rec_c_get_incompleted_cm.cm_customer_trx_id,
cp_attribute_context => 'CM-INV-APP' )
LOOP
fnd_file.put_line(fnd_file.LOG,' 9 Passing CM-INV-REV record, for the CM-INV-APP with rec_c_get_cm_inv_app_rec.inv_customer_trx_id -> '||rec_c_get_cm_inv_app_rec.attribute1
||', rec_c_get_cm_inv_app_rec.cm_customer_trx_id -> '||rec_c_get_cm_inv_app_rec.attribute2
||' reference_id -> '||rec_c_get_cm_inv_app_rec.reference_id
||', repository_id -> '|| rec_c_get_cm_inv_app_rec.repository_id);
|| Insert a record into the repository corresponding to the 'CM-CM-REV'
*/
fnd_file.put_line(fnd_file.LOG,' 9.1 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line(fnd_file.LOG,' 10 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry original amount -> '||nvl(rec_c_get_cm_inv_app_rec.debit_amount,rec_c_get_cm_inv_app_rec.credit_amount)
||', reversal entry amount -> '||ln_amount );
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => rec_c_get_cm_inv_app_rec.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => ln_organization_id ,/*5879769*/
p_location_id => ln_location_id ,/*5879769*/
p_service_type_code => lv_service_type_code ,/*5879769*/
p_source => p_source ,
p_source_trx_type => lv_source_trx_type ,
p_source_table_name => lv_source_table , --rchandan for bug#4428980
p_source_document_id => rec_c_get_cm_inv_app_rec.repository_id ,
p_transaction_date => rec_c_get_cm_inv_app_rec.creation_date ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => ln_amount ,
p_assessable_value => NULL ,
p_tax_rate => rec_c_get_cm_inv_app_rec.tax_rate ,
p_reference_id => rec_c_get_cm_inv_app_rec.reference_id ,
p_batch_id => p_batch_id ,
p_called_from => lv_object_name ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message ,
p_discounted_amount => ln_discounted_amount ,
p_inv_organization_id => rec_c_get_cm_inv_app_rec.inv_organization_id ,
p_accounting_date => sysdate ,
p_currency_code => rec_c_get_cm_inv_app_rec.trx_currency ,
p_curr_conv_date => rec_c_get_cm_inv_app_rec.curr_conv_date ,
p_curr_conv_type => NULL ,
p_curr_conv_rate => rec_c_get_cm_inv_app_rec.curr_conv_rate ,
p_trx_amount => ln_amount , --rchandan for bug#4428980
p_attribute_context => lv_attribute_context ,
p_attribute1 => rec_c_get_cm_inv_app_rec.attribute1 ,
p_attribute2 => rec_c_get_cm_inv_app_rec.attribute2
, p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.LOG,' 10.1 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line( fnd_file.log, '11 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
|| Update the Recovered Amount of the Invoice Reference against the CM application
########################################################################################################*/
fnd_file.put_line(fnd_file.LOG,' 12 before call to jai_cmn_rgm_recording_pkg.update_reference for updating INV reference_id - '||rec_c_get_cm_inv_app_rec.reference_id
||', amount to be adjusted from recovered_Amount -> '||nvl(rec_c_get_cm_inv_app_rec.debit_amount,rec_c_get_cm_inv_app_rec.credit_amount) * (-1)
);
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => rec_c_get_cm_inv_app_rec.reference_id ,
p_recovered_amount => ln_amount ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line( fnd_file.log, '13 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,' 14 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating invoice reference' );
END delete_non_existant_cm;
SELECT
aral.customer_trx_id cm_customer_trx_id ,
aral.applied_customer_trx_id inv_customer_trx_id ,
aral.receivable_application_id ,
aral.gl_date ,
nvl(aral.tax_applied,0) cm_app_tax_amt ,
nvl(aral.line_applied,0) cm_app_line_amt , --added by walton for inclusive tax 29-Nov-07
nvl(aral.amount_applied,0) cm_app_amount ,
cm_trx.trx_date cm_transaction_date ,
cm_trx.invoice_currency_code cm_currency_code ,
cm_trx.exchange_date cm_exchange_date ,
cm_trx.exchange_rate cm_exchange_rate ,
cm_trx.exchange_rate_type cm_exchange_rate_type ,
inv_jtrx.organization_id inv_invn_organization_id ,
cm_jtrx.organization_id cm_invn_organization_id
FROM
ar_receivable_applications_all aral ,
ra_customer_trx_all cm_trx ,
ra_customer_trx_all inv_trx ,
ra_cust_trx_types_all trx_types ,
JAI_AR_TRXS inv_jtrx ,
JAI_AR_TRXS cm_jtrx
WHERE
aral.customer_trx_id = cm_trx.customer_trx_id AND
cm_trx.customer_trx_id = cm_jtrx.customer_trx_id AND
aral.applied_customer_trx_id = inv_trx.customer_trx_id AND
trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
aral.application_type = lv_application_type AND--rchandan for bug#4428980
aral.status = lv_status AND--rchandan for bug#4428980
/*nvl(aral.tax_applied,0) <> 0 AND*/--Commented by walton for inclusive tax 29-Nov-07
inv_trx.org_id = nvl(p_org_id,inv_trx.org_id) AND
cm_trx.org_id = nvl(p_org_id,cm_trx.org_id) AND
inv_trx.complete_flag = 'Y' AND
cm_trx.complete_flag = 'Y' AND
trx_types.cust_trx_type_id = inv_trx.cust_trx_type_id AND
trx_types.type = lv_type AND--rchandan for bug#4428980
trx_types.org_id = inv_trx.org_id AND
inv_trx.customer_trx_id = inv_jtrx.customer_trx_id AND
inv_jtrx.organization_id = p_organization_id AND/*5879769*/
NOT EXISTS ( SELECT /*A credit memo application does not exist in repository */
1
FROM
jai_rgm_trx_records rgtr
WHERE
rgtr.source = cp_source_ar AND
rgtr.organization_id = p_organization_id AND -- Date 05/06/2007 by sacsethi for bug 6109941
rgtr.source_table_name = lv_source_table AND--rchandan for bug#4428980
rgtr.source_document_id = aral.receivable_application_id
) AND
EXISTS (
SELECT /* A credit memo exists in the reference table with total recoverable amount <> recovered amount*/
1
FROM
jai_rgm_trx_refs rgtf
WHERE
rgtf.source = cp_source_ar AND
rgtf.invoice_id = aral.customer_trx_id AND
nvl(rgtf.recoverable_amount,0) <> nvl(rgtf.recovered_amount,0)
) AND
EXISTS (
SELECT /* A invoice exists in the reference table with total recoverable amount > recovered amount*/
1
FROM
jai_rgm_trx_refs rgtf
WHERE
rgtf.source = cp_source_ar AND
rgtf.invoice_id = aral.applied_customer_trx_id AND
nvl(rgtf.recoverable_amount,0) - nvl(discounted_amount,0) > nvl(rgtf.recovered_amount,0)
) ;
SELECT
nvl(sum(jrttl.tax_amount),0) inv_tot_tax_amount
FROM
JAI_AR_TRX_LINES jrtl ,
JAI_AR_TRX_TAX_LINES jrttl
WHERE
jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
jrtl.customer_trx_id = cp_inv_customer_trx_id ;
SELECT
sign(nvl(sum(jrttl.tax_amount),0)) sign_of_credit_memo
FROM
JAI_AR_TRX_LINES jrtl ,
JAI_AR_TRX_TAX_LINES jrttl
WHERE
jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
jrtl.customer_trx_id = cp_cm_customer_trx_id;
SELECT
nvl(sum(jrttl.tax_amount),0) inv_tot_inclusive_tax_amt
FROM
JAI_AR_TRX_LINES jrtl
, JAI_AR_TRX_TAX_LINES jrttl
, jai_cmn_taxes_all tax
WHERE jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id
AND jrtl.customer_trx_id = pn_cm_customer_trx_id
AND jrttl.tax_id = tax.tax_id
AND NVL(tax.inclusive_tax_flag,'N') = 'Y' ;
SELECT
nvl(sum(jrtl.line_amount),0) inv_tot_line_amt
FROM
JAI_AR_TRX_LINES jrtl
WHERE jrtl.customer_trx_id = pn_cm_customer_trx_id;
SELECT
reference_id ,
tax_type ,
tax_rate ,
nvl(recoverable_amount,0) - nvl(discounted_amount,0) recoverable_amount ,
nvl(recovered_amount,0) recovered_amount ,
recoverable_ptg ,
item_line_id /*5879769*/
FROM
jai_rgm_trx_refs
WHERE
source = cp_source_ar AND
invoice_id = cp_inv_customer_trx_id AND
nvl(recoverable_amount,0) - nvl(discounted_amount,0) > nvl(recovered_amount,0)
FOR UPDATE NOWAIT ;
SELECT
reference_id ,
tax_type ,
tax_rate ,
nvl(recoverable_amount,0) recoverable_amount ,
nvl(recovered_amount,0) recovered_amount ,
item_line_id /*5879769*/
FROM
jai_rgm_trx_refs
WHERE
source = cp_source_ar AND
invoice_id = cp_cm_customer_trx_id AND
nvl(recoverable_amount,0) <> nvl(recovered_amount,0)
FOR UPDATE NOWAIT ;
SELECT
nvl(sum(recoverable_amount - recovered_amount),0) tot_effcm_rb_amt
FROM
jai_rgm_trx_refs
WHERE
source = cp_source_ar AND
invoice_id = cp_cm_customer_trx_id ;
|| Insert the effective Credit Memo tax amount into the repository
########################################################################################################*/
/*
|| Make an entry into the repository with the apportioned Credit Memo Tax amount to be applied against a reference invoice
*/
/* ln_amount := abs(ln_eff_cm_tax_amount) * ln_sign_of_credit_memo ; */
fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => rec_c_get_refinvrec_for_upd.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => ln_organization_id ,/*5879769*/
p_location_id => ln_location_id ,/*5879769*/
p_service_type_code => lv_service_type_code ,/*5879769*/
p_source => p_source ,
p_source_trx_type => lv_source_trx_type ,
p_source_table_name => lv_source_table ,
p_source_document_id => rec_c_get_cm_rec_app.receivable_application_id ,
p_transaction_date => rec_c_get_cm_rec_app.cm_transaction_date ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => ln_amount ,
p_assessable_value => NULL ,
p_tax_rate => rec_c_get_refinvrec_for_upd.tax_rate ,
p_reference_id => rec_c_get_refinvrec_for_upd.reference_id ,
p_batch_id => p_batch_id ,
p_called_from => lv_called_from ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message ,
p_discounted_amount => ln_discounted_amount ,
p_inv_organization_id => ln_organization_id ,/*5879769*/
p_accounting_date => rec_c_get_cm_rec_app.gl_date ,
p_currency_code => rec_c_get_cm_rec_app.cm_currency_code ,
p_curr_conv_date => rec_c_get_cm_rec_app.cm_exchange_date ,
p_curr_conv_type => rec_c_get_cm_rec_app.cm_exchange_rate_type ,
p_curr_conv_rate => rec_c_get_cm_rec_app.cm_exchange_rate ,
p_trx_amount => ln_amount ,
p_attribute_context => lv_attribute_context ,
p_attribute1 => rec_c_get_cm_rec_app.inv_customer_trx_id ,
p_attribute2 => rec_c_get_cm_rec_app.cm_customer_trx_id
, p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line( fnd_file.log, '16 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
|| Update the Invoice Reference Recovered Amount with the effective Credit Memo tax amount
########################################################################################################*/
fnd_file.put_line(fnd_file.LOG,' 11 before call to jai_cmn_rgm_recording_pkg.update_reference for updating invoice reference' );
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => rec_c_get_refinvrec_for_upd.reference_id ,
p_recovered_amount => ln_amount ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line( fnd_file.log, '12 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,' 13 Returned from jai_cmn_rgm_recording_pkg.update_reference after updating invoice reference' );
|| INSERT CM-CM-APP ENTRIES IN REPOSITORY AND UPDATE THE CREDIT MEMO REFERENCE RECORDS
########################################################################################################*/
IF nvl(ln_receivable_application_id ,-1) <> rec_c_get_cm_rec_app.receivable_application_id THEN
/*
|| Get the total effective recoverable credit memo amount from the reference table - CM record
*/
OPEN c_get_cmref_totrd_amt ( cp_source_ar => p_source ,
cp_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
);
|| Update the credit memo reference lines
*/
FOR rec_c_get_refcmrec_for_upd IN c_get_refcmrec_for_upd ( cp_source_ar => p_source ,
cp_cm_customer_trx_id => rec_c_get_cm_rec_app.cm_customer_trx_id
)
LOOP
/*
|| Initialize the variable ln_cm_ref_upd
*/
ln_cm_ref_upd := null;
|| Insert the effective Credit Memo tax amount into the repository
########################################################################################################*/
/*
|| Make an entry into the repository with the apportioned Credit Memo Tax amount to be applied against a reference Credit Memo
*/
/*csahoo for bug#5879769...start*/
ln_organization_id := NULL;
fnd_file.put_line(fnd_file.LOG,' 23 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => rec_c_get_refcmrec_for_upd.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => ln_organization_id ,/*5879769*/
p_location_id => ln_location_id ,/*5879769*/
p_service_type_code => lv_service_type_code ,/*5879769*/
p_source => p_source ,
p_source_trx_type => lv_source_trx_type ,
p_source_table_name => lv_source_table ,
p_source_document_id => rec_c_get_cm_rec_app.receivable_application_id ,
p_transaction_date => rec_c_get_cm_rec_app.cm_transaction_date ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => ln_cm_ref_upd ,
p_assessable_value => NULL ,
p_tax_rate => rec_c_get_refcmrec_for_upd.tax_rate ,
p_reference_id => rec_c_get_refcmrec_for_upd.reference_id ,
p_batch_id => p_batch_id ,
p_called_from => lv_called_from ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message ,
p_discounted_amount => ln_discounted_amount ,
p_inv_organization_id => ln_organization_id ,/*5879769*/
p_accounting_date => rec_c_get_cm_rec_app.gl_date ,
p_currency_code => rec_c_get_cm_rec_app.cm_currency_code ,
p_curr_conv_date => rec_c_get_cm_rec_app.cm_exchange_date ,
p_curr_conv_type => rec_c_get_cm_rec_app.cm_exchange_rate_type ,
p_curr_conv_rate => rec_c_get_cm_rec_app.cm_exchange_rate ,
p_trx_amount => ln_cm_ref_upd ,
p_attribute_context => lv_attribute_context ,
p_attribute2 => rec_c_get_cm_rec_app.cm_customer_trx_id
, p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.LOG,' 24 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line( fnd_file.log, '25 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,' 20 before call to jai_cmn_rgm_recording_pkg.update_reference for credit memo references '
||' ,abs(rec_c_get_refcmrec_for_upd.recoverable_amount) -> '||abs(rec_c_get_refcmrec_for_upd.recoverable_amount)
||' ,recovered amount i.e ln_cm_ref_upd -> '||ln_cm_ref_upd
);
|| Update the cm reference line with the amount in ln_cm_ref_upd
*/
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => rec_c_get_refcmrec_for_upd.reference_id ,
p_recovered_amount => ln_cm_ref_upd ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line(fnd_file.LOG,' 21 Returned from jai_cmn_rgm_recording_pkg.update_reference for credit memo references' );
fnd_file.put_line( fnd_file.log, '22 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
END LOOP; /* End of Update Credit Memo references */
SELECT
trx.customer_trx_id ,
acrl.cash_receipt_id ,
aral.receivable_application_id ,
aral.gl_date ,
sign(nvl(aral.tax_applied,0)) sign_of_cash_receipt ,
sign(nvl(tax_uediscounted,0) + nvl(tax_ediscounted,0)) sign_of_cr_disc ,
nvl(aral.tax_applied,0) cash_rcpt_tax_amt ,
nvl(tax_uediscounted,0) + nvl(tax_ediscounted,0) cr_tax_disc_amt ,
nvl(aral.amount_applied,0) receipt_amount ,
nvl(aral.line_applied,0) cash_rcpt_line_amt , --added by walton for inclusive tax 29-Nov-07
acrl.receipt_date ,
acrl.currency_code receipt_currency_code ,
acrl.exchange_date receipt_exchange_date ,
acrl.exchange_rate receipt_exchange_rate ,
acrl.exchange_rate_type receipt_exchange_rate_type ,
jtrx.organization_id inv_organization_id
FROM
ar_receivable_applications_all aral ,
ar_cash_receipts_all acrl ,
ra_customer_trx_all trx ,
ra_cust_trx_types_all trx_types ,
JAI_AR_TRXS jtrx
WHERE
aral.cash_receipt_id = acrl.cash_receipt_id AND
aral.applied_customer_trx_id = trx.customer_trx_id AND
trunc(aral.creation_date) BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
upper(aral.application_type) = upper(jai_constants.ar_cash) AND
upper(aral.status) = upper(jai_constants.ar_status_app) AND
jtrx.organization_id =p_organization_id AND --Added by kunkumar for forward porting to R12
/*nvl(aral.tax_applied,0) <> 0 AND*/--Modified by walton for inclusive tax 29-Nov-07
trx.org_id = nvl(p_org_id,trx.org_id) AND
trx.complete_flag = 'Y' AND
trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
upper(trx_types.type) IN (upper(jai_constants.ar_invoice_type_inv),upper(jai_constants.ar_invoice_type_cm)) AND
trx_types.org_id = trx.org_id AND
trx.customer_trx_id = jtrx.customer_trx_id AND
NOT EXISTS ( SELECT /*A receipt application does not exist in repository */
1
FROM
jai_rgm_trx_records rgtr
WHERE
rgtr.source = cp_source_ar AND
rgtr.organization_id = p_organization_id AND/*5879769*/
rgtr.source_table_name = lv_source_table AND
rgtr.source_document_id = aral.receivable_application_id
) AND
EXISTS (
SELECT /* A invoice exists in the reference table with total recoverable amount - discounted_amount > recovered amount*/
1
FROM
jai_rgm_trx_refs rgtf
WHERE
rgtf.source = jai_constants.SOURCE_AR AND
rgtf.invoice_id = aral.applied_customer_trx_id AND
(
( /*Scope of recovery is possible for cash receipt application */
nvl(rgtf.recoverable_amount,0) - nvl(rgtf.discounted_amount,0) > nvl(rgtf.recovered_amount,0) AND
nvl(aral.tax_applied,0) > 0
) OR
( /* As it is a case of cash receipt reversal hence do not check for recovery. */
nvl(aral.tax_applied,0) < 0
)
)
);
SELECT
nvl(sum(jrttl.tax_amount),0) inv_tot_tax_amount
FROM
JAI_AR_TRX_LINES jrtl ,
JAI_AR_TRX_TAX_LINES jrttl
WHERE
jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id AND
jrtl.customer_trx_id = cp_inv_customer_trx_id ;
SELECT
nvl(sum(jrttl.tax_amount),0) inv_tot_inclusive_tax_amt
FROM
JAI_AR_TRX_LINES jrtl
, JAI_AR_TRX_TAX_LINES jrttl
, jai_cmn_taxes_all tax
WHERE jrtl.customer_trx_line_id = jrttl.link_to_cust_trx_line_id
AND jrtl.customer_trx_id = pn_inv_customer_trx_id
AND jrttl.tax_id = tax.tax_id
AND NVL(tax.inclusive_tax_flag,'N') = 'Y' ;
SELECT
nvl(sum(jrtl.line_amount),0) inv_tot_line_amt
FROM
JAI_AR_TRX_LINES jrtl
WHERE jrtl.customer_trx_id = pn_inv_customer_trx_id;
SELECT
reference_id ,
tax_type ,
tax_rate ,
nvl(discounted_amount,0) discounted_amount ,
nvl(recoverable_amount,0) - nvl(discounted_amount,0) recoverable_amount ,
nvl(recovered_amount,0) recovered_amount ,
item_line_id /*5879769*/
FROM
jai_rgm_trx_refs
WHERE
source = cp_source_ar AND
invoice_id = cp_customer_trx_id AND
nvl(recoverable_amount,0) - nvl(discounted_amount,0) >= nvl(recovered_amount,0) /*Modified the comparison condition to >= for Bug 6474509*/
FOR UPDATE NOWAIT ;
|| Insert the effective cash receipt tax amount into the repository
########################################################################################################*/
/*
|| Make an entry into the repository with the apportioned Cash Receipt Tax amount
*/
/*csahoo for bug#5879769...start*/
ln_organization_id := NULL;
fnd_file.put_line(fnd_file.LOG,' 14 before call to jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => rec_c_get_refrec_for_upd.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => ln_organization_id ,/*5879769*/
p_location_id => ln_location_id ,/*5879769*/
p_service_type_code => lv_service_type_code ,/*5879769*/
p_source => p_source ,
p_source_trx_type => lv_source_trx_type ,
p_source_table_name => lv_source_table ,
p_source_document_id => rec_c_get_rec_app.receivable_application_id ,
p_transaction_date => rec_c_get_rec_app.receipt_date ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => ln_eff_cr_tax_amount ,
p_assessable_value => NULL ,
p_tax_rate => rec_c_get_refrec_for_upd.tax_rate ,
p_reference_id => rec_c_get_refrec_for_upd.reference_id ,
p_batch_id => p_batch_id ,
p_called_from => lv_called_from ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message ,
p_discounted_amount => ln_eff_cr_disc_amount ,
p_inv_organization_id => ln_organization_id ,/*5879769*/
p_accounting_date => rec_c_get_rec_app.gl_date ,
p_currency_code => rec_c_get_rec_app.receipt_currency_code ,
p_curr_conv_date => rec_c_get_rec_app.receipt_exchange_date ,
p_curr_conv_type => rec_c_get_rec_app.receipt_exchange_rate_type ,
p_curr_conv_rate => rec_c_get_rec_app.receipt_exchange_rate ,
p_trx_amount => ln_eff_cr_tax_amount ,
p_attribute_context => lv_attribute_context ,
p_attribute1 => rec_c_get_rec_app.customer_trx_id ,
p_attribute2 => rec_c_get_rec_app.cash_receipt_id
, p_accntg_required_flag => jai_constants.yes --File.Sql.35 Cbabu
);
fnd_file.put_line(fnd_file.LOG,' 15 Returned from jai_cmn_rgm_recording_pkg.insert_repository_entry ' );
fnd_file.put_line( fnd_file.log, '16 error in call to jai_cmn_rgm_recording_pkg.insert_repository_entry - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
|| update the effective cash receipt tax amount into the reference table
########################################################################################################*/
fnd_file.put_line(fnd_file.LOG,' 12 before call to jai_cmn_rgm_recording_pkg.update_reference ' );
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => rec_c_get_refrec_for_upd.reference_id ,
p_recovered_amount => ln_eff_cr_tax_amount ,
p_discounted_amount => ln_eff_cr_disc_amount ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line( fnd_file.log, '12.1 error in call to jai_cmn_rgm_recording_pkg.update_reference - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,' 13 Returned from jai_cmn_rgm_recording_pkg.update_reference ' );
|| DELETE NON INCOMPLETE/NON-EXISTING CREDIT MEMO'S
########################################################################################################*/
/*
|| Reverse all those AR Credit Memo's which have been incompleted/incompleted
|| and deleted from base ar tables
*/
fnd_file.put_line(fnd_file.LOG,'############################## 6 BEFORE CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM ############################## ');
delete_non_existant_cm ( p_regime_id => p_regime_id ,
p_organization_type => p_organization_type ,
p_organization_id => p_organization_id ,/*5879769*/
p_from_date => p_from_date ,
p_to_date => p_to_date ,
p_org_id => p_org_id ,
p_source => lv_source_ar ,
p_batch_id => p_batch_id ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
fnd_file.put_line( fnd_file.log, '7 ERROR IN CALL TO jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM - lv_process_flag '||lv_process_flag
||', lv_process_message'||lv_process_message);
fnd_file.put_line(fnd_file.LOG,'############################## 8 RETURNED FROM jai_ar_rgm_processing_pkg.DELETE_NON_EXISTANT_CM'||'lv_process_flag - '||lv_process_flag||
' lv_process_message- '||lv_process_message||'############################## ');