[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SERVICE TAX DEBIT ENTRIES ARE NOT UPDATED IN THE SERVICE TAX REGISTER
Reason:Debit memo is not considered at the time of service tax enhancement
Fix:
Changes are done to include to process the debit memos.
following scenarios are considered:
1.Credit memo applied to debit memo
2.Cash Receipt applied to debit memo
3.Receip unapplication
11. 3-Mar-2010 Bug 9432780
Issue - When payment term is not "Immediate" for an AR invoice and a
credit memo is applied instead of cash receipt, service tax accounting
goes wrong for the 2nd (and later) installment.
Fix - In procedure populate_cm_app, the amount to be accounted for the CM
is calculated using ratio of (amount applied / total recoverable amount).
Total recoverable amount is calculated as sum(recoverable amount - recoverable amount),
which gives wrong results for 2nd installment and later.
So modified the cursor c_get_cmref_totrd_amt to calculate this amount as
simply sum(recoverable amount).
12 4-Apr-2010 Bo Li for Bug9305067
Replace the old attribute columns of JAI_RGM_TRX_RECORDS with the new meaningful columns
13. 27-Jul-2010 Bug 9919612
Issue: If an AR Invoice is created with just Adhoc Service Tax(with no Line Amount) then
divisor by zero error is thrown
Fix: Added a check to set a temporary line amount variable with value 1 if the Original
Line amount is zero
14. Oct 03, 2010 Bug 10148245
Description: When Receipt Application is made in detail, the Service Tax
amounts are not prorated correctly across all the Lines
The Tax proration is done on the total tax amount of all lines
Fix: Fetched Tax Applied and Total Tax of individual lines from
ar_activity_details and prorated the taxes accordingly
15. Oct 29, 2010 Bug 10230041 fixed by Eric Ma
The credit memo entries are missing in both jai_rgm_trx_records and gl_interface tables
when applying the receipt with CM and Invoice.
Added REVERSAL_FLAG LOGIC by 3 Ma for bug 10230041 on Oct-28-2010
16. Jan 04, 2011 Bug 10623735
Description:
+ When Receipt and Invoice currency are different, the Service Tax Repository
is not updated with the correct amounts
+ If there are multiple applications and unapplication, repository entries
are corrupt
Fix:
+ JAI_RGM_TRX_RECORDS is populated based on line_applied and tax_applied columns
in AR_RECEIVABLE_APPLICATIONS_ALL. In case of currency difference between Invoice and
Receipt, the Invoice currency is used to populate AR_RECEIVABLE_APPLICATIONS_ALL.
This was not considered when calculating Transaction Amount.
Multiplied by Invoice Exchange Rate if Invoice is in foreign currency or divided
by Receipt/Credit Memo exchange rate if Receipt/CM is in foreign currency.
+ If there are multiple applications and unapplication, repository entries
need to be created in the same order of application/unapplication.
Hence added order by clause on aral.receivable_application_id in cursors
c_get_cm_rec_app and c_get_rec_app
17. 14-Mar-2011 ER 11821537
Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
Service Tax Liability arises on creation of Invoice and not on Receipt of Payment from Customer
i.e. Accounting changes to Accrual basis from Cash Basis
Fix: Commented the following procedures
+ delete_non_existant_cm
+ populate_cm_app
+ populate_receipt_records
Added the following procedure
+ populate_repository - To populate Repository Entries for all eligible AR Invoice with
no accounting (Interim Liability(Dr) to Liability(Cr))
Service Tax Processing shall not run unless the From and To Dates are after the Effective Date
for Service Tax Paoint of Taxation(This is to avoid Data corruption)
18. 19-May-2011 Bug 11932841
Description: Receipt Tax amount shall be zero in case a Receipt is applied on a AR Document with only Inclusive Taxes
as Inclusive Tax is part of Line amount and there are no separate Tax Distributions.
In those cases ln_eff_cr_tax_amount will be zero as ln_receipt_tax_amt will be zero.
But the Inclusive Tax needs to hit the repository
Fix: Modified cursor cur_get_inv_tottax_amt to get only Exclusive Tax.
Calculated Repository amount separately for Inclusive and Exclusive Taxes.
19. 11-Jul-2011 Bug 11787045
Description: Credit Memo unapplication on a Receipt does not work as intended.
Fix: Receipt Application ID corresponding to the unapplication is not picked by cursor c_get_rec_app.
Added abs clause to ensure that Unapplication lines are picked even if tax amounts are negative.
20. 26-JUL-2011 By mmurtuza Description: Rolling back the changes done for bug 11787045
Porting the changes done in 12.1 branch.
Issue:
Service tax pending liability report is showing the post POT invoices which are completed
technical details:
issue is due to the jai_rgm_trx_refs.recovered_amount is not updated with the tax amount
Fix:
made a call to jai_rgm_trx_recording_pkg.update_reference to update the recovered_amoun in
populate_repository procedure
21. 24-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Compare the invoice accounting date with POT implementation date, so that it can process
transactions on cash basis as well as accrual basis.
Recover code for :
+ delete_non_existant_cm
+ populate_cm_app
+ populate_receipt_records
22. 29-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: Correct cursor get_trx_gl_date_cur, use link_to_cust_trx_line_id to get REV line accounting date.
Variable ld_st_accrual_date should be initialized correctly.
23. 30-May-2011 Xiao for POT change, reg bug#12533434.
Fixed: In procedure populate_repository, correct cursor c_ar_transactions and c_ar_ref_records to fetch.
eligible ar lines to populate into repository.
In cursor c_get_incompleted_cm, add rgtr.line_id in the group clause.
24. 08-Jun-2011 Xiao for POT change, reg bug#12631135.
Fixed: In function is_accrual_basis, jai_ar_trx_tax_lines should be used instead of table ra_customer_trx_lines_all
as for taxes lines, they will be held in jai_ar_trx_tax_lines, not in ra_customer_trx_lines_all.
25. 11-Jul-2011 Bug 11787045
Description: Credit Memo unapplication on a Receipt does not work as intended.
Fix: Receipt Application ID corresponding to the unapplication is not picked by cursor c_get_rec_app.
Added abs clause to ensure that Unapplication lines are picked even if tax amounts are negative.
26. 21-jul-2011 vkaranam for bug#12777500
Issue:
Service tax pending liability report is showing the post POT invoices which are completed
technical details:
issue is due to the jai_rgm_trx_refs.recovered_amount is not updated with the tax amount
Fix:
made a call to jai_rgm_trx_recording_pkg.update_reference to update the recovered_amoun in
populate_repository procedure
27. 22-jul-2011 mmurtuza for Rolling back the changes done for the bug 11787045.
28. 22-Aug-2011 Xiao for POT Phase III changes, reg bug#12895841
Fixed: Add logic for showing transactions for AP reversal, AP claim, AR reversal, AR claim.
29. 29-Sep-2011 Zhiwei for POT change to Open Interface, reg bug#13023443
Fixed: for Tax line from Open Interface and External and GL date >= POT date should not populate repository
30. 21-Nov-2011 Qiong for Advanced Receipts
31. 30-Nov-2011 Qinglei for Bug#13531399
Fixed: for both recoverable and non-recovable tax, when poputlate to jai_rgm_trx_refs, use tax amount
ignore the mod_cr_percentage.
32. 05-Jan-2012 Qinglei for Bug#13556198
Fixed: Receipt application/unapplication can't updated into Service Tax Repository
33. 12-Jan-2012 Qinglei for Bug#13569242
Issue: For Advance Receipt Reversal, GL INTERFACE SHOULD USE RECEIPT REVERSAL GL DATE AS ACCOUNTING DATE
Fixed: Add CURSOR cur_get_rcpt_reversal_gl_date to get receipt reversal gl date.
34. 12-Jan-2012 Qinglei for Bug#13557031
Issue: When Advance Receipt Confirmation and Reversal, wrong transacton date inserted into ra_interface_lines_all.
Fixed: When Receipt confirm, use receipt date insert into ar interface table; when receipt reversal, use reversal date
insert into ar interface table.
Rounded the amount inserted into ar interface table.
35. 12-Jan-2012 Qiong for bug#13540741 ST repository review shouldn't use transaction date for query
36. 12-Jan-2012 Qiong for bug#13555753 ST proceesing report and repository should use reversal trx date and gl date
37. 13-Jan-2012 Qinglei for Bug#13557031
Fixed: Rounded the amount insert into repository for receipt apply/unapply.
39. 20-Jan-2012 Qiong for Bug#13569249 - TST122.XB18:GL LINE DRILLDOWN FOR RECEIPT REVERSAL HAS ERROR
40. 07-Feb-2012 Qiong for Bug#13579826 - TST122.XB18:Applied AR trx with cash basis can not be updated to repository
41. 07-Feb-2012 Zhiwei Xin for Bug#13535100
Fixed: AFTER POT DATA WITH EXTERNAL EVENT MUSTN'T BE UPDATED TO REPOSITORY
42. 08-Feb-2012 Zhiwei Xin for Bug#13535519
Fixed: Populate AR transaction applied to receipts into repository for pre-POT.
43. 09-Feb-2012 Qiong for bug#13690141
Fixed: Tax amount column on ST repository UI is inaccurate
44. 22-Feb-2012 Qiong for bug#13730406
Fixed: Document number is not generated in IL cash receipts form.
45. 24-Feb-2012 Zhiwei Xin for bug #13777022
Fixed: default ln_external_flag as null.
46. 28-Feb-2012 Qinglei for bug#13741544
Fixed: Change jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
For Advance Receipt reversal transaction.
Future Dependencies For the release Of this Object:-
(Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
A datamodel change )
----------------------------------------------------------------------------------------------------------------------------------------------------
Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
Of File On Bug/Patchset
jai_rgm_process_ar_taxes_pkg_b.sql
----------------------------------------------------------------------------------------------------------------------------------------------------
115.0 4146634 IN60105D2 + Aiyer 27-Jan-2005 4146708 is the release bug
4146708 for SERVICE/CESS enhancement release
115.1 4193633 jai_cmn_rgm_recording_pkg 115.1 Aiyer 23-Feb-2005 Functional dependency due to spec change.
----------------------------------------------------------------------------------------------------------------------------------------------------
********************************************************************************************************************************************************/
AS
/*csahoo for bug#5879769...start*/
lv_service_type_code JAI_PO_LINE_LOCATIONS.service_type_code%TYPE;
SELECT rda.gl_date
FROM ra_cust_trx_line_gl_dist_all rda,
--ra_customer_trx_lines_all rla
jai_ar_trx_tax_lines rla--Modified by Xiao for bug#12631135.
WHERE rda.account_class = 'REV'
AND rda.customer_trx_line_id = rla.link_to_cust_trx_line_id
-- AND rda.customer_trx_id = rla.customer_trx_id --Commented by Xiao for bug#12631135.
AND rla.customer_trx_line_id = pn_customer_trx_line_id;
select to_date(attribute_value, 'DD/MM/YYYY')
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id
AND jrr.attribute_code = 'EFF_DATE_ST_PT'
AND jrr.attribute_type_code = 'OTHERS'
AND jrr.registration_type = 'OTHERS'
AND (NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.attribute_code IN 'INV_ORG_CLASSIFICATION'
and jrr.attribute_value <> 'ORGANIZATION'
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id)
OR
NOT EXISTS
(select '1'
from JAI_RGM_ORG_REGNS_V jrr, jai_rgm_definitions jrd
where jrr.regime_id = jrd.regime_id
AND jrd.regime_code = jai_constants.service_regime
and jrr.attribute_code IN 'SERVICE TYPE'
and jrr.attribute_value <> 'OTHER'
and jrr.organization_id = p_organization_id
and jrr.location_id = p_location_id)
);
SELECT organization_id, location_id
FROM jai_ar_trxs
WHERE customer_trx_id IN(SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id IN pn_customer_trx_line_id);*/
SELECT organization_id, location_id
FROM jai_ar_trxs
WHERE customer_trx_id IN(
SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id IN
(
select link_to_cust_trx_line_id
from jai_ar_trx_tax_lines
where customer_trx_line_id = pn_customer_trx_line_id
)
);
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,
nvl(jtc.reverse_charge_flag,'N') reverse_charge_flag --Added by Qiong for reverse charge bug#16001407
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 ,
ra_customer_trx_lines_all rctla , /*14121914*/
JAI_CMN_TAXES_ALL jtc ,
ra_cust_trx_line_gl_dist_all gd--Added by Qiong for bug13540741
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)*/
--Modified by qiong for bug13540741Changed trx.trx_date to gd.gl_date
trunc(gd.gl_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,cp_dm_type) AND -- Add by Xiao for bug#6773751 on 20-Dec-09
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
/*14121914 - Start*/
jtrxl.customer_trx_line_id = rctla.customer_trx_line_id AND
(
(rctla.accounting_rule_id is not null
AND
(rctla.autorule_complete_flag IS NULL
OR
rctla.autorule_complete_flag = 'Y')
)
OR
rctla.accounting_rule_id is null
) AND
/*14121914 - End*/
jtrxtl.tax_id = jtc.tax_id AND
--Added by Qiong for bug13540741 begin
-----------------------------------------------
gd.customer_trx_id = trx.customer_trx_id AND
gd.account_class = 'REC' AND
gd.latest_rec_flag = 'Y' AND
-----------------------------------------------
--Added by Qiong for bug13540741 end
--Commented by zhiwei for POT bug#12925963 on 20110908 begin
-----------------------------------------------------------
--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
-----------------------------------------------------------
--Commented by zhiwei for POT bug#12925963 on 20110908 begin
--Added by zhiwei for POT bug#12925963 on 20110908 begin
-----------------------------------------------------------
upper(jtc.tax_type) IN ( upper(cp_service_tax),cp_service_edu_cess ,cp_sh_service_edu_cess) AND
-----------------------------------------------------------
--Added by zhiwei for POT bug#12925963 on 20110908 end
( /**** 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*/--Commented by Qiong for bug13690141
) OR
/* Added by Xiao for bug#6773751 */
(
upper(trx_types.type) = cp_dm_type
)
) 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
)
--Commented by zhiwei for POT change Bug#13023443 on 20110929 begin
-------------------------------------------------------------------------------------------------------------------------------------------
--AND NVL(jtrxl.INTERFACE_EVENT,'####') <> 'EXTERNAL' --Added by zhiwei for Bug#12718676 service tax process should not pick up external
-------------------------------------------------------------------------------------------------------------------------------------------
--Commented by zhiwei for POT change Bug#13023443 on 20110929 end
ORDER BY
trx_types.type desc;
select 1
from JAI_AR_TRX_LINES line
where customer_trx_line_id = cn_customer_trx_line_id --rec_c_fetch_inv_cm_rec.link_to_cust_trx_line_id
and NVL(interface_flag, '###') = 'Y'
and NVL(interface_event,'###') = JAI_OPEN_API_PKG.GV_TAXABLE_EVENT_EXTERNAL;
SELECT
jcra.cash_receipt_id,
jcra.currency_code,
jcra.exchange_date ,
jcra.exchange_rate ,
jcra.org_id,
jcra.customer_id,
acra.customer_site_use_id ,
jtc.tax_type ,
jdt.doc_tax_id,
jdt.tax_id ,
jdt.tax_rate,
nvl(jtc.mod_cr_percentage,0) mod_cr_percentage ,
jdt.tax_amt tax_amount,
jdt.func_tax_amt,
acra.amount receipt_amount
FROM ar_cash_receipts_all acra,
jai_ar_cash_receipts_all jcra,
jai_cmn_document_taxes jdt ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE acra.cash_receipt_id = jcra.cash_receipt_id
AND jdt.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jdt.source_doc_id = jcra.cash_receipt_id
AND jrttv.regime_code = jai_constants.service_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash
AND jcra.CONFIRM_FLAG = 'Y'
AND jcra.organization_id = p_organization_id
AND jcra.org_id = nvl(p_org_id,jcra.org_id)
AND NOT EXISTS ( SELECT /*A ref of receipt should not exist in the reference table */
1
FROM jai_rgm_trx_refs rgtr
WHERE
rgtr.source = p_source AND
rgtr.invoice_id = acra.cash_receipt_id AND
rgtr.line_id = jdt.doc_tax_id AND
rgtr.tax_id = jdt.tax_id
);
||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 ,
-- Replaced tax_amount by func_tax_amount for Bug 7522584
p_tax_amount => rec_c_fetch_inv_cm_rec.func_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)*/ ,
--Commented by Qinglei on 30-Dec-2011 for bug#13531399
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 );
||insert the receipts into the jai_rgm_trx_refs
||using the procedure jai_cmn_rgm_recording_pkg.insert_reference
*****/
fnd_file.put_line(fnd_file.LOG,'2.2 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 ,
p_source => p_source ,
p_invoice_id => rec_c_fetch_receipt_rec.cash_receipt_id ,
p_line_id => rec_c_fetch_receipt_rec.doc_tax_id ,
p_tax_type => rec_c_fetch_receipt_rec.tax_type ,
p_tax_id => rec_c_fetch_receipt_rec.tax_id ,
p_tax_rate => rec_c_fetch_receipt_rec.tax_rate ,
p_recoverable_ptg => rec_c_fetch_receipt_rec.mod_cr_percentage ,
p_party_type => jai_constants.party_type_customer ,
p_party_id => rec_c_fetch_receipt_rec.customer_id ,
p_party_site_id => rec_c_fetch_receipt_rec.customer_site_use_id ,
p_trx_tax_amount => rec_c_fetch_receipt_rec.tax_amount ,
p_trx_currency => rec_c_fetch_receipt_rec.currency_code ,
p_curr_conv_date => rec_c_fetch_receipt_rec.exchange_date ,
p_curr_conv_rate => rec_c_fetch_receipt_rec.exchange_rate ,
p_tax_amount => rec_c_fetch_receipt_rec.func_tax_amt ,
p_recoverable_amount => 0 ,
p_recovered_amount => 0
/*
Item_line_id should be checked in jai_trx_repo_extract_pkg.get_doc_from_reference, if it is null,
can't pass it. But, no item exists in receipts.So, assign hardcode 1 to item_line_id.
Added by Qiong
*/ ,
p_item_line_id => 1,
p_item_id => NULL,
p_taxable_basis => rec_c_fetch_receipt_rec.receipt_amount ,
p_parent_reference_id => NULL ,
p_reversal_flag => NULL,
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,'2.3 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, '2.4 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,'2.5 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 */
, rgtr.line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
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
, rgtr.line_id; --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
SELECT
*
FROM
jai_rgm_trx_records
WHERE
trx_reference_context = cp_attribute_context AND --Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
trx_reference2 = cp_cm_customer_trx_id --Bo Li for Bug9305067 change attribute2 to trx_reference2
/* 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
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
trx_reference2 = cp_cm_customer_trx_id ; --Bo Li for Bug9305067 change attribute2 to trx_reference2
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
--Add by Xiao for POT change, reg bug#12533434 on 16-May-2011, begin
----------------------------------------------------------------------------------------------
IF NOT is_accrual_basis(rec_c_get_incompleted_cm.line_id) THEN
----------------------------------------------------------------------------------------------
--Add by Xiao for POT change, reg bug#12533434 on 16-May-2011, begin
/*########################################################################################################
|| 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.trx_reference2 --Bo Li for Bug9305067 change attribute2 to trx_reference2
||' 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 ,
--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
-- attribute2 to trx_reference2
----------------------------------------------------------------------------- ,
p_trx_reference_context => lv_attribute_context ,
p_trx_reference2 => 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 ,
--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
-- attribute1 to trx_reference1 and attribute2 to trx_reference2
----------------------------------------------------------------------------- , --rchandan for bug#4428980
p_trx_reference_context => lv_attribute_context ,
p_trx_reference1 => rec_c_get_cm_inv_app_rec.trx_reference1 ,
p_trx_reference2 => rec_c_get_cm_inv_app_rec.trx_reference2
---------------------------------------------------------------------------
, 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 ,
trx_types.type Trx_type, --added by Xiao for bug#6773751
-- Added for Bug 8294236 - Start
inv_trx.invoice_currency_code invoice_currency_code ,
inv_trx.exchange_rate invoice_exchange_rate ,
inv_trx.exchange_date invoice_exchange_date ,
inv_trx.exchange_rate_type invoice_exchange_rate_type
-- Added for Bug 8294236 - End
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 in( 'INV', 'DM' ) AND --lv_type -- modified by Xiao for bug#6773751
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)
)
order by aral.receivable_application_id;
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*/
, line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
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*/
, line_id --Add by Xiao for POT change, reg bug#12533434 on 16-May-2011
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),0) tot_effcm_rb_amt /*bug 9432780*/
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 ,
-- Replaced ln_amount by ln_func_amount for Bug 7522584
p_amount => ln_func_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_trx_amount /*Bug 10623735*/ ,
--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
-- attribute1 to trx_reference1 and attribute2 to trx_reference2
----------------------------------------------------------------------------- ,
p_trx_reference_context => lv_attribute_context ,
p_trx_reference1 => rec_c_get_cm_rec_app.inv_customer_trx_id ,
p_trx_reference2 => 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
--Add by Xiao for POT change, reg bug#12533434 on 16-May-2011, begin
----------------------------------------------------------------------------------------------
IF NOT is_accrual_basis(rec_c_get_refcmrec_for_upd.line_id) THEN
----------------------------------------------------------------------------------------------
--Add by Xiao for POT change, reg bug#12533434 on 16-May-2011, end
/*
|| 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 ,
-- Added ln_func_amount for Bug 7522584
p_amount => ln_func_amount ,
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_trx_amount /*10623735*/ ,
--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
-- attribute2 to trx_reference2
----------------------------------------------------------------------------- ,
p_trx_reference_context => lv_attribute_context ,
p_trx_reference2 => 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 ,
trx_types.type trx_type, --added by Xiao for bug#6773751
-- Added for Bug 8294236
trx.invoice_currency_code invoice_currency_code ,
trx.exchange_rate invoice_exchange_rate ,
trx.exchange_date invoice_exchange_date ,
trx.exchange_rate_type invoice_exchange_rate_type
-- Added for Bug 8294236
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)
,upper(jai_constants.ar_doc_type_dm)) AND /* Added ar_doc_type_dm for bug# 6773751 */
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
--Added by Qiong for bug13579826 begin
rgtr.source_trx_type in (jai_constants.trx_type_rct_app,jai_constants.trx_type_rct_rvs) AND
--Added by Qiong for bug13579826 end
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
(
/*Bug 11932841 - Tax applied can be zero if the AR Document has only inclusive Taxes. Replaced > with >= and < with <=*/
( /*Scope of recovery is possible for cash receipt application */
/*Bug 11787045 - In case a Credit Memo is applied on a Receipt and reversal of the same happens,
the Receipt Application ID is not picked up as recoverable_amount - discounted_amount is not greater than
recovered_amount. Hence added abs*/
/*Rolling back the changes done for Bug 11787045 and removing abs by mmurtuza */
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
)
)
)
order by aral.receivable_application_id;
SELECT
nvl(sum(jrttl.tax_amount),0) inv_tot_tax_amount
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_line_id = nvl(cp_inv_cus_trx_line_id, jrtl.customer_trx_line_id) AND
jrttl.customer_trx_line_id = nvl(cp_inv_tax_trx_line_id, jrttl.customer_trx_line_id) AND
jrtl.customer_trx_id = cp_inv_customer_trx_id AND
jrttl.tax_id = tax.tax_id AND
NVL(tax.inclusive_tax_flag,'N') = 'N';
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 jrtl.customer_trx_line_id = nvl(pn_inv_cus_trx_line_id, jrtl.customer_trx_line_id)
AND jrttl.customer_trx_line_id = nvl(pn_inv_tax_trx_line_id, jrttl.customer_trx_line_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
AND jrtl.customer_trx_line_id = nvl(pn_inv_cus_trx_line_id, jrtl.customer_trx_line_id);
SELECT
reference_id ,
invoice_id ,/*Bug 10148245*/
line_id ,/*Bug 10148245*/
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*/ ,
NVL(reversal_flag,'N') reversal_flag --Added by Eric Ma for bug 10230041 on Oct-28-20100
FROM
jai_rgm_trx_refs
WHERE
source = cp_source_ar AND
invoice_id = cp_customer_trx_id AND
--Commented out by Eric Ma for bug 10230041 on Oct-28-2010
-- NVL(recoverable_amount,0) - nvl(discounted_amount,0) >= nvl(recovered_amount,0) /*Modified the comparison condition to >= for Bug 6474509*/
--Added by Eric Ma for bug 10230041 on Oct-28-2010, Begin
------------------------------------------------------------------------------------------------------------------------
( ((REVERSAL_FLAG <>'Y') AND (nvl(recoverable_amount, 0) - nvl(discounted_amount, 0) >=nvl(recovered_amount, 0)))
OR
((REVERSAL_FLAG = 'Y') AND (nvl(ABS(recoverable_amount), 0) - nvl(ABS(discounted_amount), 0) >=nvl(ABS(recovered_amount), 0)))
)
------------------------------------------------------------------------------------------------------------------------
--Added by Eric Ma for bug 10230041 on Oct-28-2010, End
FOR UPDATE NOWAIT ;
SELECT 1
FROM ar_activity_details
WHERE cash_receipt_id = cp_cash_receipt_id
AND source_id = cp_receivable_appln_id;
SELECT link_to_cust_trx_line_id
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = cp_cust_trx_line_id
AND line_type = 'TAX';
SELECT sum(extended_amount)
FROM ra_customer_trx_lines_all
WHERE link_to_cust_trx_line_id = cp_cust_trx_line_id
AND line_type = 'TAX';
SELECT nvl(sum(tax), 0), nvl(sum(tax_discount), 0)
FROM ar_activity_details
WHERE customer_trx_line_id = cp_cust_trx_line_id
AND cash_receipt_id = cp_cash_receipt_id
AND source_id = cp_receivable_appln_id;
|| 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 ,
-- Added ln_func_amount for Bug 7522584
p_amount => ln_func_tax_amt ,
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_trx_amount , /*Bug 10623735*/
--Bo Li for Bug9305067 change attribute_cotext to trx_reference_context
--attribute1 to trx_reference1 and attribute2 to trx_reference2
-----------------------------------------------------------------------------
p_trx_reference_context => lv_attribute_context ,
p_trx_reference1 => rec_c_get_rec_app.customer_trx_id ,
p_trx_reference2 => 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 ' );
SELECT trx.customer_trx_id ,
trx.trx_date
FROM
ra_customer_trx_all trx ,
ra_cust_trx_types_all trx_types ,
JAI_AR_TRXS jtrx
WHERE
trx_types.cust_trx_type_id = trx.cust_trx_type_id AND
jtrx.organization_id = p_organization_id AND
trx.org_id = nvl(p_org_id,trx.org_id) AND
upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv,
jai_constants.ar_invoice_type_cm,
jai_constants.ar_doc_type_dm) AND
trx_types.org_id = trx.org_id AND
trx.customer_trx_id = jtrx.customer_trx_id AND
trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) AND
NOT EXISTS ( SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE
rgtr.source = cp_source_ar AND
rgtr.organization_id = p_organization_id AND
rgtr.source_table_name = lv_source_table AND
rgtr.source_document_id = jtrx.customer_trx_id
) AND
EXISTS ( SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE
rgtf.source = cp_source_ar AND
rgtf.invoice_id = jtrx.customer_trx_id AND
rgtf.party_type = 'C'
);*/--Commented by Zhiwei.hou for POT code port.
SELECT trx.customer_trx_id
, trx.trx_date
, gd.gl_date --Added by Qiong for bug13540741
, jattl.customer_trx_line_id
, jattl.link_to_cust_trx_line_id
, jtrx.st_inv_number --Added by Qiong for Advanced Receipts
FROM jai_ar_trx_tax_lines jattl
, ra_customer_trx_lines_all trxl
, ra_customer_trx_all trx
, ra_cust_trx_types_all trx_types
, jai_ar_trxs jtrx
, ra_cust_trx_line_gl_dist_all gd --Added by Qiong for bug13540741
WHERE jattl.link_to_cust_trx_line_id = trxl.customer_trx_line_id
AND trxl.customer_trx_id = trx.customer_trx_id
/*14121914 - Start*/
AND (
(accounting_rule_id is not null
AND
(autorule_complete_flag IS NULL
OR
autorule_complete_flag = 'Y')
)
OR
accounting_rule_id is null
)
/*14121914 - End*/
AND trx_types.cust_trx_type_id = trx.cust_trx_type_id
AND jtrx.organization_id = p_organization_id
AND trx.org_id = nvl(p_org_id,trx.org_id)
AND upper(trx_types.type) IN (jai_constants.ar_invoice_type_inv
, jai_constants.ar_invoice_type_cm
, jai_constants.ar_doc_type_dm
)
AND trx_types.org_id = trx.org_id
AND trx.customer_trx_id = jtrx.customer_trx_id
--AND trx.trx_date BETWEEN trunc(p_from_date) and trunc(p_to_date) commented by qiong for bug13540741
--Added by Qiong for bug13540741 begin
----------------------------------------------
AND gd.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
AND gd.customer_trx_id = trx.customer_trx_id
AND gd.ACCOUNT_CLASS = 'REC'
AND gd.LATEST_REC_FLAG = 'Y'
----------------------------------------------
--Added by Qiong for bug13540741 end
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = cp_source_ar
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = lv_source_table
AND rgtr.source_document_id = jattl.customer_trx_line_id)
AND EXISTS (SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = cp_source_ar
AND rgtf.line_id = jattl.customer_trx_line_id
AND rgtf.party_type = 'C');
SELECT *
FROM jai_rgm_trx_refs rgtf
WHERE line_id = cp_source_document_id AND
party_type = 'C' AND
source = cp_source_ar;
SELECT jcta.tax_type, jattl.customer_trx_line_id, trx.trx_date,
jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100 tax_amt
FROM jai_ar_trx_tax_lines jattl
, ra_customer_trx_all trx
, jai_cmn_taxes_all jcta
WHERE trx.customer_trx_id = pn_invoice_id
AND jattl.link_to_cust_trx_line_id = pn_line_number
AND jattl.tax_id = jcta.tax_id
AND nvl(jcta.mod_cr_percentage,0) > 0
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AR_REVERSAL'
AND source_table_name = 'CUSTOMER_TRX_LINE_ALL'
AND source_document_id = jattl.customer_trx_line_id);
SELECT reversal_amount, date_of_reversal
FROM jai_st_invoice_reversal
WHERE invoice_id = pn_invoice_id
AND customer_trx_line_id = pn_line_number
AND SOURCE = 'AR';
SELECT SUM(tax_amount)
FROM jai_rgm_trx_refs jrtr
WHERE jrtr.invoice_id = pn_invoice_id
AND jrtr.source = 'AR'
AND jrtr.tax_type IN (jai_constants.tax_type_service,
jai_constants.tax_type_service_edu_cess,
jai_constants.tax_type_sh_service_edu_cess);
SELECT SUM(total_amount)
FROM jai_ar_trx_lines
WHERE customer_trx_id = pn_invoice_id;
SELECT jcta.tax_type, (jattl.tax_amount * nvl(jcta.mod_cr_percentage,0)/100) tax_amt,
jattl.customer_trx_line_id, trx.trx_date
FROM jai_ar_trx_tax_lines jattl
, ra_customer_trx_all trx
, jai_cmn_taxes_all jcta
WHERE trx.customer_trx_id = pn_invoice_id
AND jattl.link_to_cust_trx_line_id = pn_line_number
AND jattl.tax_id = jcta.tax_id
AND nvl(jcta.mod_cr_percentage,0) > 0
AND jcta.tax_type IN ('Service', 'SERVICE_EDUCATION_CESS', 'SERVICE_SH_EDU_CESS')
AND EXISTS (SELECT 1
FROM jai_rgm_trx_records
WHERE SOURCE = 'AR_REVERSAL'
AND source_table_name = 'CUSTOMER_TRX_LINE_ALL'
AND source_document_id = jattl.customer_trx_line_id);
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AR'
AND jsir.tax_to_be_adjusted > 0
ORDER BY jsir.invoice_id;
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AR'
AND jsir.date_of_reversal BETWEEN p_from_date AND p_to_date;
SELECT nvl(amount_applied, 0) claim_amt, receivable_application_id, gl_date
FROM ar_receivable_applications_all araa
WHERE araa.applied_customer_trx_id = pn_invoice_id
AND araa.receivable_application_id > nvl(pn_max_payment_id, 0)
AND trunc(araa.apply_date) <= p_to_date
AND araa.status = 'APP'
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AR_CLAIM'
AND jrtr.source_document_id = araa.receivable_application_id)
AND EXISTS (SELECT 1
FROM ra_cust_trx_line_gl_dist_all rg
WHERE rg.customer_trx_id = pn_invoice_id
AND rg.customer_trx_line_id = pn_line_number
AND rg.account_class = 'REV'
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(araa.apply_date,
rg.gl_date, 'AR') = 'Y');
SELECT jcra.cash_receipt_id
, acra.receipt_date
, jcra.organization_id
, jcra.location_id
, acra.receipt_number
, jcra.gl_date
, jcra.service_type_code
, jcra.exchange_rate
, jcra.exchange_rate_type
FROM
jai_ar_cash_receipts_all jcra
, ar_cash_receipts_all acra
WHERE jcra.cash_receipt_id = acra.cash_receipt_id
AND jcra.confirm_flag = 'Y'
AND jcra.document_type = 'Service'
--AND acra.reversal_date IS NULL
AND jcra.organization_id = p_organization_id
AND jcra.org_id = nvl(p_org_id,jcra.org_id)
AND jcra.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AR'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
AND rgtr.source_trx_type = jai_constants.trx_type_adv_rcpts
AND rgtr.source_document_id = jcra.cash_receipt_id)
AND EXISTS (SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AR'
AND rgtf.invoice_id = jcra.cash_receipt_id
AND rgtf.party_type = 'C');
SELECT jdt.doc_tax_id,
jdt.tax_type,
jdt.tax_amt,
jdt.func_tax_amt
FROM jai_cmn_document_taxes jdt
, jai_ar_cash_receipts_all jacr
, jai_cmn_taxes_all jcta
, jai_regime_tax_types_v jrttv
WHERE jacr.cash_receipt_id = cn_cash_receipt_id
AND jdt.source_doc_id = jacr.cash_receipt_id
AND jdt.tax_id = jcta.tax_id
AND jcta.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.service_regime
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash ;
SELECT *
FROM jai_rgm_trx_refs rgtf
WHERE line_id = cp_source_doc_line_id AND
invoice_id = cp_source_document_id AND
party_type = 'C' AND
source = cp_source_ar;
SELECT jcra.cash_receipt_id
, acra.reversal_date
, jcra.organization_id
, jcra.location_id
, acra.receipt_number
, acrh.gl_date --Modified by Qiong for bug13555753 change from jcra.gl_date to acrh.gl_date
, jcra.service_type_code
, jcra.exchange_rate
, jcra.exchange_rate_type
FROM
jai_ar_cash_receipts_all jcra
, ar_cash_receipts_all acra
, ar_cash_receipt_history_all acrh --Added by Qiong for bug13555753
WHERE jcra.cash_receipt_id = acra.cash_receipt_id
AND jcra.confirm_flag = 'Y'
AND jcra.document_type = 'Service'
AND acra.reversal_date IS NOT NULL
AND jcra.organization_id = p_organization_id
AND jcra.org_id = nvl(p_org_id,jcra.org_id)
--AND jcra.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date) commented by qiong for bug13555753
--Added by qiong for bug13555753 begin
---------------------------------------
AND acrh.cash_receipt_id = acra.cash_receipt_id
AND acrh.status = 'REVERSED'
AND acrh.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
---------------------------------------
--Added by qiong for bug13555753 end
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AR'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AR_CASH_RECEIPTS_ALL'
AND rgtr.source_trx_type = jai_constants.trx_type_adv_rvs
--Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
--by Qinglei on 24-Dec-2012 for bug#13741544
AND rgtr.source_document_id = jcra.cash_receipt_id)
AND EXISTS (SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AR'
AND rgtf.invoice_id = jcra.cash_receipt_id
AND rgtf.party_type = 'C');
SELECT araa.receivable_application_id
, jcra.cash_receipt_id
, trx.customer_trx_id
, araa.apply_date
, araa.gl_date
, jcra.organization_id
, jcra.location_id location_id
, acra.receipt_number
, jcra.service_type_code
, acra.receipt_date
, jcra.currency_code receipt_currency_code
, jcra.exchange_date receipt_exchange_date
, jcra.exchange_rate receipt_exchange_rate
, jcra.exchange_rate_type receipt_exchange_rate_type
, trx.invoice_currency_code invoice_currency_code
, trx.exchange_rate invoice_exchange_rate
, trx.exchange_date invoice_exchange_date
, trx.exchange_rate_type invoice_exchange_rate_type
, acra.amount amount
, araa.amount_applied amount_applied
, araa.acctd_amount_applied_from amount_applied_from
--Modified by Qinglei on 05-Jan-2012 for bug#13556198
, to_number(decode(sign(araa.applied_payment_schedule_id),
-1,
null,
nvl(araa.earned_discount_taken, 0) +
nvl(araa.unearned_discount_taken, 0))) discount_amount
, acra.set_of_books_id
--Added by Qinglei on 05-Jan-2012 for bug#13556198
FROM
ar_receivable_applications_all araa
, ar_cash_receipts_all acra
, jai_ar_cash_receipts_all jcra
, ra_customer_trx_all trx
WHERE araa.applied_customer_trx_id = trx.customer_trx_id
AND araa.cash_receipt_id = acra.cash_receipt_id
AND jcra.cash_receipt_id = acra.cash_receipt_id
AND trx.complete_flag = 'Y'
AND upper(araa.application_type) = upper(jai_constants.ar_cash)
AND upper(araa.status) = upper(jai_constants.ar_status_app)
AND jcra.confirm_flag = 'Y'
AND sign (nvl(acra.amount,0)) = sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
AND jcra.document_type = 'Service'
--AND acra.reversal_date IS NULL
AND jcra.organization_id = p_organization_id
AND jcra.org_id = nvl(p_org_id,jcra.org_id)
AND araa.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)--Need confirm use creation date/apply date
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AR'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND rgtr.source_trx_type = jai_constants.trx_type_adv_app
AND rgtr.source_document_id = araa.receivable_application_id)
AND EXISTS (SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AR'
AND rgtf.invoice_id = jcra.cash_receipt_id
AND rgtf.party_type = 'C');
SELECT araa.receivable_application_id
, jcra.cash_receipt_id
, trx.customer_trx_id
, araa.apply_date
, araa.gl_date
, jcra.organization_id
, jcra.location_id
, acra.receipt_number
, jcra.service_type_code
, acra.receipt_date
, jcra.currency_code receipt_currency_code
, jcra.exchange_date receipt_exchange_date
, jcra.exchange_rate receipt_exchange_rate
, jcra.exchange_rate_type receipt_exchange_rate_type
, trx.invoice_currency_code invoice_currency_code
, trx.exchange_rate invoice_exchange_rate
, trx.exchange_date invoice_exchange_date
, trx.exchange_rate_type invoice_exchange_rate_type
, acra.amount amount
, araa.amount_applied amount_applied
, araa.acctd_amount_applied_from amount_applied_from
--Modified by Qinglei on 05-Jan-2012 for bug#13556198
, to_number(decode(sign(araa.applied_payment_schedule_id),
-1,
null,
nvl(araa.earned_discount_taken, 0) +
nvl(araa.unearned_discount_taken, 0))) discount_amount
, acra.set_of_books_id
--Added by Qinglei on 05-Jan-2012 for bug#13556198
FROM
jai_ar_cash_receipts_all jcra
, ar_cash_receipts_all acra
, ar_receivable_applications_all araa
, ra_customer_trx_all trx
WHERE araa.applied_customer_trx_id = trx.customer_trx_id
AND araa.cash_receipt_id = acra.cash_receipt_id
AND jcra.cash_receipt_id = acra.cash_receipt_id
AND trx.complete_flag = 'Y'
AND upper(araa.application_type) = upper(jai_constants.ar_cash)
--AND upper(araa.status) = upper(jai_constants.ar_status_unapp)
AND jcra.confirm_flag = 'Y'
AND sign (nvl(acra.amount,0)) <> sign(nvl(araa.amount_applied,0))-- same means apply, otherwise means unapply
AND jcra.document_type = 'Service'
--AND acra.reversal_date IS NULL
AND jcra.organization_id = p_organization_id
AND jcra.org_id = nvl(p_org_id,jcra.org_id)
AND araa.gl_date BETWEEN trunc(p_from_date) and trunc(p_to_date)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records rgtr
WHERE rgtr.source = 'AR'
AND rgtr.organization_id = p_organization_id
AND rgtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND rgtr.source_trx_type = jai_constants.trx_type_adv_unapp
AND rgtr.source_document_id = araa.receivable_application_id )
AND EXISTS (SELECT 1
FROM jai_rgm_trx_refs rgtf
WHERE rgtf.source = 'AR'
AND rgtf.invoice_id = jcra.cash_receipt_id
AND rgtf.party_type = 'C');
select
trx.organization_id,
trx.location_id,
line.service_type_code
from jai_rgm_trx_refs refs,
jai_ar_trxs trx,
jai_ar_trx_tax_lines tax,
jai_ar_trx_lines line
where refs.reference_id = cp_reference_id--r_ar_ref_records.reference_id
and refs.line_id = tax.customer_trx_line_id
and refs.invoice_id = trx.customer_trx_id
and tax.link_to_cust_trx_line_id = line.customer_trx_line_id
and line.customer_trx_id = trx.customer_trx_id;
jai_cmn_rgm_recording_pkg.insert_repository_entry (
p_repository_id => ln_repository_id ,
p_regime_id => p_regime_id ,
p_tax_type => r_ar_ref_records.tax_type ,
p_organization_type => p_organization_type ,
p_organization_id => ln_organization_id ,
p_location_id => ln_location_id ,
p_service_type_code => lv_service_type_code ,
p_source => p_source ,
p_source_trx_type => jai_constants.trx_event_inv_save ,
p_source_table_name => UPPER(jai_constants.tname_cus_trx_lines) ,
--p_source_document_id => r_ar_ref_records.invoice_id --Commented by zhiwei.hou for POT code port
p_source_document_id => r_ar_ref_records.line_id, --Modified by Xiao for POT bug#12533434 ,
p_transaction_date => r_ar_transactions.gl_date, --Changed by Qiong for bug13540741 ,
p_account_name => NULL ,
p_charge_account_id => NULL ,
p_balancing_account_id => NULL ,
p_amount => r_ar_ref_records.tax_amount ,
p_assessable_value => NULL ,
p_tax_rate => r_ar_ref_records.tax_rate ,
p_reference_id => r_ar_ref_records.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_amt ,
p_inv_organization_id => ln_organization_id ,
p_accounting_date => r_ar_transactions.gl_date, --Changed from sysdate to gl_date by Qiong for bug13540741
p_currency_code => r_ar_ref_records.trx_currency ,
p_curr_conv_date => r_ar_ref_records.curr_conv_date ,
p_curr_conv_type => NULL ,
p_curr_conv_rate => r_ar_ref_records.curr_conv_rate ,
p_trx_amount => r_ar_ref_records.trx_tax_amount ,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y' ,--Add by Xiao for pot change, reg bug#12533434
p_invoice_no => r_ar_transactions.st_inv_number --Added by Qiong for Advanced Receipts
);
jai_cmn_rgm_recording_pkg.update_reference (
p_source => p_source ,
p_reference_id => r_ar_ref_records.reference_id ,
p_recovered_amount => r_ar_ref_records.trx_tax_amount ,
p_process_flag => lv_process_flag ,
p_process_message => lv_process_message
);
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_reversal_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR_REVERSAL',
p_source_trx_type => 'REVERSAL_ACCOUNTING',
p_source_table_name => 'CUSTOMER_TRX_LINE_ALL',
p_source_document_id => lr_ar_ref_records.line_id,
p_transaction_date => ld_accounting_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => NULL,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_claim_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR_CLAIM',
p_source_trx_type => 'CLAIM_ACCOUNTING',
p_source_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
p_source_document_id => rec_claim_line.receivable_application_id,
p_transaction_date => ld_accounting_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => NULL,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_tax_amount,--lr_ar_ref_records.trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_receipt_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR',
p_source_trx_type => jai_constants.trx_type_adv_rcpts,
p_source_table_name => 'AR_CASH_RECEIPTS_ALL',
p_source_document_id => rec_c_ar_receipts.cash_receipt_id,
p_transaction_date => ld_gl_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount ,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => rec_c_ar_receipts.exchange_rate_type,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_receipt_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR',
p_source_trx_type => jai_constants.trx_type_adv_rvs,
--Modified jai_constants.trx_type_rct_rvs to jai_constants.trx_type_adv_rvs
--by Qinglei on 28-Dec-2012 for bug#13741544
p_source_table_name => 'AR_CASH_RECEIPTS_ALL',
p_source_document_id => rec_c_receipts_reversal.cash_receipt_id,
p_transaction_date => ld_gl_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount ,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => ld_accounting_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => rec_c_receipts_reversal.exchange_rate_type,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_receipt_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR',
p_source_trx_type => jai_constants.trx_type_adv_app,
p_source_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
p_source_document_id => rec_c_receipts_application.receivable_application_id,
p_transaction_date => rec_c_receipts_application.gl_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount ,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => rec_c_receipts_application.gl_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => rec_c_receipts_application.receipt_exchange_rate_type,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => p_regime_id,
p_tax_type => rec_receipt_entries.tax_type,
p_organization_type => p_organization_type,
p_organization_id => ln_organization_id,
p_location_id => ln_location_id,
p_service_type_code => lv_service_type_code,
p_source => 'AR',
p_source_trx_type => jai_constants.trx_type_adv_unapp,
p_source_table_name => 'AR_RECEIVABLE_APPLICATIONS_ALL',
p_source_document_id => rec_c_receipts_unapplication.receivable_application_id,
p_transaction_date => rec_c_receipts_unapplication.gl_date,
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_tax_amount ,
p_assessable_value => NULL,
p_tax_rate => lr_ar_ref_records.tax_rate,
p_reference_id => lr_ar_ref_records.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_amt,
p_inv_organization_id => ln_organization_id,
p_accounting_date => rec_c_receipts_unapplication.gl_date,
p_currency_code => lr_ar_ref_records.trx_currency,
p_curr_conv_date => lr_ar_ref_records.curr_conv_date,
p_curr_conv_type => rec_c_receipts_unapplication.receipt_exchange_rate_type,
p_curr_conv_rate => lr_ar_ref_records.curr_conv_rate,
p_trx_amount => ln_trx_tax_amount,
p_accntg_required_flag => jai_constants.no,
p_accrual_basis => 'Y');
SELECT jsir.*
FROM jai_st_invoice_reversal jsir
WHERE jsir.source = 'AR'
AND jsir.tax_to_be_adjusted > 0;
SELECT nvl(amount_applied, 0) claim_amt, receivable_application_id, gl_date
FROM ar_receivable_applications_all araa
WHERE araa.applied_customer_trx_id = pn_invoice_id
AND araa.apply_date <= pd_to_date
AND araa.status = 'APP'
AND araa.receivable_application_id > nvl(pn_max_payment_id, 0)
AND NOT EXISTS (SELECT 1
FROM jai_rgm_trx_records jrtr
WHERE jrtr.source_table_name = 'AR_RECEIVABLE_APPLICATIONS_ALL'
AND jrtr.source_trx_type = 'CLAIM_ACCOUNTING'
AND jrtr.source = 'AR_CLAIM'
AND jrtr.source_document_id = araa.receivable_application_id)
AND EXISTS (SELECT 1
FROM ra_cust_trx_line_gl_dist_all rg
WHERE rg.customer_trx_id = pn_invoice_id
AND rg.customer_trx_line_id = pn_line_number
AND rg.account_class = 'REV'
AND jai_st_reversal_extract_pkg.is_paid_after_reversal(araa.apply_date,
rg.gl_date, 'AR') = 'Y');
UPDATE jai_st_invoice_reversal
SET max_claim_payment_id = ln_max_payment_id
WHERE invoice_id = reversal_trxn_rec.invoice_id
AND customer_trx_line_id = reversal_trxn_rec.customer_trx_line_id
AND SOURCE = 'AR';
SELECT to_date(attribute_value, 'DD/MM/YYYY')
FROM jai_rgm_registrations
WHERE regime_id = (select regime_id
from JAI_RGM_DEFINITIONS
where regime_code = jai_constants.service_regime)
AND attribute_code = 'EFF_DATE_ST_PT'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
|| 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
*/
/*Bug 11821537 - Update repository entry. Accounting is taken care during AR Invoice Creation or Import
Hence commenting procedures delete_non_existant_cm, populate_cm_app and populate_receipt_records
Code shall be rewritten to pick AR Invoices*/
--/* --Commented comments by zhiwei.hou for POT code port.
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 ,
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||'############################## ');
SELECT *
FROM ar_cash_receipts_all
WHERE cash_receipt_id = cp_cash_receipt_id;
SELECT *
FROM
jai_ar_cash_receipts_all jcra
WHERE
jcra.cash_receipt_id = cp_cash_receipt_id ;
SELECT
jtc.tax_type ,
jdt.tax_amt tax_amount ,
jdt.func_tax_amt
FROM
jai_cmn_document_taxes jdt ,
JAI_CMN_TAXES_ALL jtc ,
jai_regime_tax_types_v jrttv
WHERE
jdt.tax_id = jtc.tax_id
AND jtc.tax_type = jrttv.tax_type
AND jdt.source_doc_id = p_acra.cash_receipt_id
AND jrttv.regime_code = jai_constants.service_regime
AND nvl(jtc.INCLUSIVE_TAX_FLAG,'N') = cp_inclusive_tax_flag
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;
SELECT
regime_id ,
attribute_value org_tan_no
FROM
JAI_RGM_ORG_REGNS_V rgm_attr_v
WHERE
rgm_attr_v.regime_code = cp_regime_code
AND rgm_attr_v.attribute_code = cp_attribute_code
AND rgm_attr_v.organization_id = cp_organization_id
AND rgm_attr_v.location_id = cp_location_id ;
SELECT
bsa.batch_source_id ,
bsa.default_inv_trx_type ,
rctt.type ,
rctt.name ,
rctt.default_term ,
rctt.gl_id_rec ,
rctt.creation_sign
FROM
ra_batch_sources_all bsa ,
ra_cust_trx_types_all rctt
WHERE
bsa.default_inv_trx_type = rctt.cust_trx_type_id
AND bsa.org_id = rctt.org_id
AND bsa.org_id = cp_org_id
AND bsa.name = cp_name ;
SELECT
hzcas.cust_acct_site_id bill_to_address_id
FROM
hz_cust_accounts hca ,
hz_cust_acct_sites_all hzcas ,
hz_cust_site_uses_all hzcsu
WHERE
hca.cust_account_id = hzcas.cust_account_id
AND hzcas.cust_acct_site_id = hzcsu.cust_acct_site_id
AND hzcsu.site_use_code = jai_constants.site_use_bill_to
AND hca.cust_account_id = cp_party_id
AND hzcsu.site_use_id = cp_party_site_id ;-- site_use_id is the party_site_id ;
SELECT
set_of_books_id
FROM
hr_operating_units
WHERE
organization_id = cp_org_id;
SELECT
jai_rgm_trx_refs_s.nextval
FROM
dual;
ln_login_id ra_interface_lines_all.LAST_UPDATE_LOGIN%TYPE;
rec_ra_interface_lines.last_updated_by := ln_user_id;
rec_ra_interface_lines.last_update_date := SYSDATE;
rec_ra_interface_lines.last_update_login := ln_login_id;
rec_ra_interface_dist.last_updated_by := ln_user_id;
rec_ra_interface_dist.last_update_date := SYSDATE;
rec_ra_interface_dist.last_update_login := ln_login_id;
|| Insert GL interface for inclusive taxes
################################################################################################################*/
FOR rec_cur_get_rcpt_taxes IN cur_get_rcpt_taxes(cp_inclusive_tax_flag => 'Y')
LOOP
IF p_document_type IN ( --jai_constants.ar_cash_tax_confirmed , /* Receipt confirmation */
--Changed by Qiong from ar_cash_tax_confirmed to trx_type_adv_rcpts for bug13569249
jai_constants.trx_type_adv_rcpts,
jai_constants.trx_type_adv_rvs /* Receipt reversal */
--Modified by Qinglei on 24-Feb-2012 for bug#13741544
) THEN
ln_rcpt_tax_amount := rec_cur_get_rcpt_taxes.func_tax_amt;
|| INSERT INTO RA_INTERFACE_LINES_ALL TABLE
################################################################################################################*/
INSERT INTO ra_interface_lines_all
(
interface_line_id,
amount,
description,
orig_system_bill_customer_id,
orig_system_bill_address_id,
set_of_books_id,
trx_date,
trx_number,
batch_source_name,
cust_trx_type_name,
line_type,
conversion_rate,
conversion_type,
interface_line_context,
interface_line_attribute2,
currency_code,
primary_salesrep_id,
tax_code,
term_id,
warehouse_id,
org_id,
quantity,
unit_selling_price,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES (
p_rila.interface_line_id,
p_rila.amount,
p_rila.description,
p_rila.orig_system_bill_customer_id,
p_rila.orig_system_bill_address_id,
p_rila.set_of_books_id,
p_rila.trx_date,
p_rila.trx_number,
p_rila.batch_source_name,
p_rila.cust_trx_type_name,
p_rila.line_type,
p_rila.conversion_rate,
p_rila.conversion_type,
p_rila.interface_line_context,
p_rila.interface_line_attribute2,
p_rila.currency_code,
p_rila.primary_salesrep_id,
p_rila.tax_code,
p_rila.term_id,
p_rila.warehouse_id,
p_rila.org_id,
p_rila.quantity,
p_rila.unit_selling_price,
p_rila.created_by,
p_rila.creation_date,
p_rila.last_updated_by,
p_rila.last_update_date,
p_rila.last_update_login
);
INSERT INTO ra_interface_distributions_all
(
interface_line_id,
interface_line_context,
interface_line_attribute2,
account_class,
amount,
code_combination_id,
acctd_amount,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
org_id
)
VALUES(
p_rida.interface_line_id,
p_rida.interface_line_context,
p_rida.interface_line_attribute2,
p_rida.account_class,
p_rida.amount,
ln_ccid_tax_type,
p_rida.acctd_amount,
p_rida.created_by,
p_rida.creation_date,
p_rida.last_updated_by,
p_rida.last_update_date,
p_rida.last_update_login,
p_rida.org_id
);
SELECT gl_date
FROM ar_cash_receipt_history_all
WHERE cash_receipt_id = p_acra.cash_receipt_id
AND status = 'REVERSED';
SELECT unapplied_ccid
FROM AR_RECEIPT_METHOD_ACCOUNTS_all
WHERE receipt_method_id = p_acra.receipt_method_id
AND remit_bank_acct_use_id = p_acra.remit_bank_acct_use_id;
SELECT 1
FROM jai_ar_cash_receipts_all jcra,
jai_cmn_document_taxes jdt,
jai_regime_tax_types_v jrttv
WHERE jcra.cash_receipt_id = cp_cash_receipt_id
AND jcra.cash_receipt_id = jdt.source_doc_id
AND jdt.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.service_regime
AND jcra.confirm_flag = jai_constants.yes
AND jdt.source_doc_type = jai_constants.ar_cash;
SELECT
sign (nvl(amount,0)) app_fr_sign
FROM
ar_cash_receipts_all
WHERE
cash_receipt_id = cp_cash_receipt_id;
SELECT
1
FROM
jai_ar_cash_receipts_all jcra ,
jai_cmn_document_taxes jdt ,
jai_regime_tax_types_v jrttv
WHERE
jcra.cash_receipt_id = cp_cash_receipt_id
AND jcra.cash_receipt_id = jdt.source_doc_id
AND jdt.tax_type = jrttv.tax_type
AND jrttv.regime_code = jai_constants.service_regime
AND jcra.confirm_flag = jai_constants.yes
AND jdt.source_doc_type = JAI_CONSTANTS.ar_cash;