[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
while calling procedure - jai_ap_utils_pkg.insert_ap_inv_interface
18-Jul-2005 rchandan for bug#4487676.Version 117.2
JAI_RGM_SETTLEMENT_INVOICE_S is replaced with JAI_RGM_SETTLEMENT_S1
23-Aug-2005 Ramananda for bug#4559828. File Version 120.3
Problem:
-------
R12.FIN.A.QA.ST.2: GETTING ERROR ON PERFORMING SERVICE TAX SETTLEMENT
This error is coming inspite of GL and AP periods being open
Reason:
------
Org_id in the form is populated when authority site is selected from the
front end. When 'Process' Button is pressed, form makes a call to
jai_cmn_rgm_settlement_pkg.create_invoice passing org_id.
Presently, org_id is not passed to ap_utilities_pkg.get_open_gl_date and
ap_utilities_pkg.get_current_gl_date. This is defaulted from mo_global.GET_CURRENT_ORG_ID.
However the value is not retrieved from the same, hence the above reported error
Fix:
----
Added pn_org_id parameter while making a call to
1. ap_utilities_pkg.get_open_gl_date
2. ap_utilities_pkg.get_current_gl_date
in jai_cmn_rgm_settlement_pkg.create_invoice is modified to pass org_id, which is solving the problem.
i.e "APP-JA-460204: ORA 20001: No Open Period...after "
02-Dec-2005 Bug 4774647. Added by Lakshmi Gopalsami Version 120.4
Passed operating unit also as this parameter has been added by base.
27-Feb-2006 Bug 4929081. Added by Lakshmi Gopalsami version 120.5
(1) Moved cursor counter_cur after inserting into
ap_invoices_interface so that invoice_id condition can be used.
(2) Removed the select for count(*) and put the same in the cursor.
30-JAN-2007 Bug#5631784. Added by CSahoo File Version 120.11
Forward Porting of BUG#4742259 (TCS solution)
Changes made in the procedure create_invoice to create invoice at the
time of TCS settlement. A new cursor cur_distributions_TCS is defined to fetch
tax balances.
27-April-2007 ssawant for bug 5879769,6020629 ,File version 120.6
Forward porting of
ENH : SERVICE TAX BY INVENTORY ORGANIZATION AND SERVICE TYPE SOLUTION
from 11.5( bug no 5694855) to R12 (bug no 5879769).
forward porting of bug
ACCOUNTING ENTRY ON SETTLEMENT NOT PASSED
from 11.5( bug no 4287372) to R12 (bug no 6020629).
7-June-2007 ssawant for bug 5662296
Forward porting R11 bug 5642053 to R12 bug 5662296.
19-Sep-2007 anujsax for bug#6126142, File Version 120.16
Issue : VAT SETTLEMENT ENTRIES NOT GENERATED FOR OFFSET VALUE AT THE TIME OF PAYMENT.
The above issue was happening due to passing of SYSDATE for the accounting date
for creating AP Invoices and GL Interface.
Fix : The seettlement date has been passed as accounting date for AP Invoice and GL Interface
28-jun-2009 vumaasha for bug 8657720
Added an IF condition to consider 'VAT REVERSAL' tax type equivalent to 'VALUE ADDED TAX' during settlement.
30-sep-2009 vkaranam for bug#8974544
Fix:
Added regime_id condition in the get_last_Settlement_date(pn_regime_id.pn_or_id) procedure.
13-Dec-2009 Eric Ma for bug#7031751
Fix: FP 12.0 : INDIA LOC- SETTLEMENT ENTRIES ARE NOT GETTING GENERATED
22-Dec-2009 Eric Ma for bug#8333082,8671217
Fix: FP:8281389: VAT SETTLEMENT PAYMENT DETAILS FORM NOT SHOWING THE DATA PROPERLY
18-MAR-2011 vkaranam for bug#11821537
Description: Notification # 18/2011 proposes change in Point of Taxation for Service Tax.
Settlement UI and Process for partial Credit Utilization
i.e. Adjustment of liability w.r.t the credit will be restricted and is based on the
ptg provided in the regime registration setup.
Fix:
changes are done in transfeR_balance procedure.
added nvlcredit_utilized,NVL(r_acct_balances.credit_balance,0)) in transfer_balance procedure.
25 02-aug-2011 vkaranam for bug#12706846
Issue:
VAT Settlement Invoice in case of Settlement at registration number level
is imbalanced.
VAT Payment Invoice that got created correctly.
But when querying the Invoice in Invoice Workbench ,at Invoice Distributions
level only the debit lines are appearing and credit lines are not there.
As a result the amount in distribution and header level is not matching.
Fix:
Step 1.accounting entry at each and every organization level for setting off the liability with the recovery available
example :
Recovery(Credit_balance) Liability(Debit_balance) net_balance
IN1 20000 25000 -5000
IN2 17000 14000 +3000
Accounting entry shall be :
IN1:
Dr VAT liability 20000
Cr VAT recovery 20000
IN2:
Dr VAT liability 14000
Cr VAT recovery 14000
Step 2:
Distribute the credit balance available in one organization to the other organization which has the liability (debit balance).
Here the repository will be populated source_trx_type as "SETTLEMENT"
Also the accounting entry will be
IN1 Dr VAT liability +3000
IN2 Cr VAT Recovery +3000
Logic for the distribution of credit balance from source org to debit balance of destination org is as follows:
--fetch the net_balance>0 i.e organizations with the credit available in the DESCENDING ORDER.
--fetch the net_balance<0 i.e organizations with the debit available in the ASCENDING ORDER.
Step 3:
Invoice lines shall be generated for the organizations if
the net_balance + distribution_amount received from other organizations is < 0
Changes are done in transfer_balance,create_invoice procedure for step2 and step3.
25-aug-2011 vkaranam for bug#12706846
Transfer_balance procedure is not distributing the credit balance evenly to the liability.
example:
credit balance debit_balance
2k 2.5k
1k 3K
In the first run of credit balance loop 2k will be setoff against 2.5k
In the second run of credit balance loop 1k will be setoff against 2.5k only.
as the jai_rgm_Stl_balances are not updated after distribution
fix:
added the pl/sql table logic in the transfer_balance procude to distribute the
credit available evenly to the liability.
18-sep-2011 vkaranam for bug#12706846 / 12996230
Issue :
Clicking on the process button in settlement screen is giving the following error:
reference to uninitialized collection
fix:
Used the extend method to initialized the ln_crdt_transfer pl/sql varaible.
vkaranam for bug#12996230
issue:last settlement date shown for the regime registration level settlement is wrong.
fix:changes are done in get_last_Settlement_date function
28 21-mar-2012 vkaranam for bug#13865856
Issue:Service type to be made optional for Service tax distribution.
fix:changes are done in get_last_balance_amount procedure.
added the nvl condition for service_type_code condition ,which are used for fetching
the balances.
29. 18-Apr-2012 mmurtuza for bug 12641455
Description: FOR TCS SETTLEMENT ORGANIZATION AND LOCATION FIELD IS DISABLED IN SETTLEMENT FORM
Fix: modified create_invoice and transfer_balance procedures
--------------------------------------------------------------------------------------*/
PROCEDURE insert_into_vat_register(
p_repository_id OUT NOCOPY NUMBER ,
p_regime_id NUMBER ,
p_from_party_type VARCHAR2 ,
p_from_party_id NUMBER ,
p_from_locn_id NUMBER ,
p_from_tax_type VARCHAR2 ,
p_from_trx_amount NUMBER ,
p_to_party_type VARCHAR2 ,
p_to_party_id NUMBER ,
p_to_tax_type VARCHAR2 ,
p_to_trx_amount IN OUT NOCOPY NUMBER ,
p_to_locn_id NUMBER ,
p_called_from VARCHAR2 ,
p_trx_date DATE ,
p_acct_req VARCHAR2 ,
p_source VARCHAR2 ,
P_SOURCE_TRX_TYPE VARCHAR2 ,
P_SOURCE_TABLE_NAME VARCHAR2 ,
p_source_doc_id NUMBER ,
p_settlement_id NUMBER ,
p_reference_id NUMBER ,
p_process_flag OUT NOCOPY VARCHAR2 ,
p_process_message OUT NOCOPY VARCHAR2 ,
p_accounting_date Date
)
IS
ln_repository_id number;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => p_regime_id,
pv_tax_type => p_from_tax_type ,
pv_organization_type => p_from_party_type,
pn_organization_id => p_from_party_id,
pn_location_id => p_from_locn_id,
pv_source => lv_source,
pv_source_trx_type => p_source_trx_type ,
pv_source_table_name => p_source_table_name,
pn_source_id => p_source_doc_id ,
pd_transaction_date => p_trx_date,
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_charge_accounting_id,
pn_balancing_account_id => ln_balance_accounting_id,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => NULL,
pn_batch_id => NULL,
pn_inv_organization_id => p_from_party_id,
pv_invoice_no => NULL,
pv_called_from => p_called_from,
pv_process_flag => p_process_flag,
pv_process_message => p_process_message,
pd_invoice_date => NULL,
pn_settlement_id => p_settlement_id --added for bug#7145898 on 25-Dec-2009 by Eric Ma
);
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => p_regime_id,
pv_tax_type => p_to_tax_type ,
pv_organization_type => p_to_party_type,
pn_organization_id => p_to_party_id,
pn_location_id => p_to_locn_id,
pv_source => lv_source,
pv_source_trx_type => p_source_trx_type ,
pv_source_table_name => p_source_table_name,
pn_source_id => p_source_doc_id ,
pd_transaction_date => p_trx_date,
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_charge_accounting_id,
pn_balancing_account_id => ln_balance_accounting_id,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => NULL,
pn_batch_id => NULL,
pn_inv_organization_id => p_to_party_id,
pv_invoice_no => NULL,
pv_called_from => p_called_from,
pv_process_flag => p_process_flag,
pv_process_message => p_process_message,
pd_invoice_date => NULL,
pn_settlement_id => p_settlement_id --added for bug#7145898 on 25-Dec-2009 by Eric Ma
);
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => p_regime_id,
pv_tax_type => p_to_tax_type,
pv_organization_type => p_to_party_type,
pn_organization_id => p_to_party_id,
pn_location_id => p_to_locn_id,
pv_source => lv_source,
pv_source_trx_type => p_source_trx_type,
pv_source_table_name => p_source_table_name,
pn_source_id => p_source_doc_id,
pd_transaction_date => p_trx_date,
pv_account_name => jai_constants.liability,
pn_charge_account_id => ln_charge_accounting_id,
pn_balancing_account_id => ln_balance_accounting_id,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => -9999,
pn_batch_id => NULL,
pn_inv_organization_id => p_to_party_id,
pv_invoice_no => NULL,
pv_called_from => p_called_from,
pv_process_flag => p_process_flag,
pv_process_message => p_process_message,
pd_invoice_date => NULL,
pn_settlement_id => p_settlement_id
);
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => p_regime_id,
pv_tax_type => p_to_tax_type,
pv_organization_type => p_to_party_type,
pn_organization_id => p_to_party_id,
pn_location_id => p_to_locn_id,
pv_source => lv_source,
pv_source_trx_type => p_source_trx_type,
pv_source_table_name => p_source_table_name,
pn_source_id => p_source_doc_id,
pd_transaction_date => p_trx_date,
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_charge_accounting_id,
pn_balancing_account_id => ln_balance_accounting_id,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => -9998,
pn_batch_id => NULL,
pn_inv_organization_id => p_to_party_id,
pv_invoice_no => NULL,
pv_called_from => p_called_from,
pv_process_flag => p_process_flag,
pv_process_message => p_process_message,
pd_invoice_date => NULL,
pn_settlement_id => p_settlement_id
);
p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
end insert_into_vat_register;
SELECT NVL(debit_balance,0) - NVL(credit_balance,0) debit_balance,
party_id,
location_id,
service_type_code , /* added by ssawant for bug 5879769 */
party_type,
rowid
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
AND tax_type = lv_tax_type
AND NVL(debit_balance,0) - NVL(credit_balance,0) > 0
-- ORDER BY 1 desc; 12706846
SELECT NVL(credit_balance,0) - NVL(debit_balance,0) credit_balance,
party_id,
location_id,
service_type_code ,/* added by ssawant for bug 5879769 */
party_type,
rowid
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
AND tax_type = lv_tax_type
AND NVL(credit_balance,0) - NVL(debit_balance,0) > 0
ORDER BY 1 desc;
SELECT (NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0))*-1 debit_balance, organization_id party_id, rowid
FROM jai_rgm_trx_records
WHERE tax_type = lv_tax_type
AND organization_id = ln_party_id
AND nvl(location_id,-999) = nvl(ln_location_id,-999)
AND organization_type = lv_party_type
AND settlement_id <= pn_settlement_id
AND NVL(settled_flag,'N') <> 'Y'
AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
ORDER BY 1 desc;
SELECT NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) credit_balance, organization_id party_id, rowid
FROM jai_rgm_trx_records
WHERE tax_type = lv_tax_type
AND organization_id = ln_party_id
AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
AND organization_type = lv_party_type
AND settlement_id <= pn_settlement_id
AND NVL(settled_flag,'N') <> 'Y'
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0
ORDER BY 1 desc;
SELECT regime_id
FROM jai_rgm_settlements
WHERE settlement_id = pn_settlement_id;
select regime_code
from JAI_RGM_DEFINITIONS
where regime_id = lv_regime_id;
SELECT JAI_RGM_DIS_DES_TAXES_S.nextval
FROM DUAL;
SELECT *
FROM jai_rgm_stl_balances
WHERE NVL(debit_balance,0) >= 0
AND NVL(credit_balance,0) >= 0
AND settlement_id = pn_settlement_id;
SELECT primary_registration_no
FROM jai_rgm_settlements
WHERE settlement_id = pn_settlement_id;*/
SELECT jstl.primary_registration_no,
jbal.party_type ,
jbal.party_id ,
jbal.location_id
FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jbal.settlement_id = pn_settlement_id;
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE regime_primary_regno = lp_regn_no
AND source_trx_type = 'Invoice Payment'
AND transaction_date = ( select max(settlement_date) + 1
from jai_rgm_stl_balances a
where 2 = (select count(distinct jbal.settlement_date)
from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
where jbal.settlement_id = jstl.settlement_id
and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date));
SELECT sum(jbal.credit_balance) credit_balance,sum(jbal.debit_balance) debit_balance
FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.primary_registration_no = lp_regn_no
AND jbal.tax_type = lp_tax_type
AND jbal.party_id = lp_org_id
AND jstl.settlement_date = ( select max(settlement_date)
from jai_rgm_stl_balances a
where 2 = (select count(distinct jbal.settlement_date)
from jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
where jbal.settlement_id = jstl.settlement_id
and jstl.primary_registration_no = lp_regn_no and jbal.settlement_date >= a.settlement_date ));
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE source_trx_type = 'Invoice Payment'
AND settlement_id = ( SELECT MAX(jbal.settlement_id)
FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.primary_registration_no = cp_regn_no
AND jbal.party_type = cp_org_type
AND jbal.party_id = cp_org_id
AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
AND jbal.settlement_id <> pn_settlement_id /*This clause is used to exclude the current settlement*/
);
SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance
FROM jai_rgm_stl_balances
WHERE settlement_id = ( SELECT MAX(jbal.settlement_id)
FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.primary_registration_no = cp_regn_no
AND jbal.party_type = cp_org_type
AND jbal.party_id = cp_org_id
AND nvl(jbal.location_id,-999) = nvl(cp_location_id,-999)
AND jbal.settlement_id <> pn_settlement_id/*This clause is used to exclude the current settlement*/
)
AND tax_type = cp_tax_type;
SELECT SUM(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0)) -- Negative amount converted to positive and taken as credit amount
FROM jai_rgm_refs_all
WHERE org_tan_no = cp_regn_no
--AND source_document_type = 'Invoice Payment'
AND settlement_id =
(SELECT MAX(jbal.settlement_id)
FROM jai_rgm_stl_balances jbal,
jai_rgm_settlements jstl,
jai_rgm_definitions jrg
WHERE jbal.settlement_id = jstl.settlement_id
AND jrg.regime_id = jstl.regime_id
AND jrg.regime_code = 'TCS'
AND jstl.primary_registration_no = cp_regn_no
AND jbal.party_type = cp_organization_type
--AND jbal.party_id = cp_organization_id /*For bug 12641455. Commented organization_id and location_id condition as for TCS , these two fields are not passed*/
--AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> nvl(pn_settlement_id, -999))
;
SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0),0) credit_balance, organization_id party_id, rowid
FROM jai_rgm_refs_all
WHERE organization_id = ln_party_id
AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
AND settlement_id <= pn_settlement_id
ORDER BY 1 desc;
SELECT NVL(total_tax_amt * decode(sign(total_tax_amt), -1, 0, 1),0) debit_balance, organization_id party_id, rowid
FROM jai_rgm_refs_all
WHERE organization_id = ln_party_id
AND nvl(location_id,-999) = nvl(ln_location_id,-999)/*rchandan for Service Type FP*/
AND settlement_id <= pn_settlement_id
ORDER BY 1 desc;
/**Observation :11821537 ,settle in ,settle out repository update is not required as the service tax is by IO and for settlement
organization and location are mandatory ,need to comment the code**/
FOR I in (select distinct b.regime_id, b.settlement_date, a.tax_type
from jai_rgm_stl_balances a,
jai_rgm_settlements b
where a.settlement_id = b.settlement_id
AND a.settlement_id = pn_settlement_id)
LOOP
SELECT count(*)
INTO ln_debit_cnt
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
AND debit_balance >0;
SELECT count(*)
INTO ln_credit_cnt
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
AND credit_balance >0;
/**Observation :11821537 ,settle in ,settle out i.e distribution repository update is not required as the service tax is by IO and for settlement
organization and location are mandatory ,hence commenting the below code**/
/*
jai_cmn_rgm_tax_dist_pkg.insert_records_into_register
(p_repository_id => ln_repository_id,
p_regime_id => i.regime_id,
p_from_party_type => cur_credit.party_type,
p_from_party_id => cur_credit.party_id,
p_from_locn_id => cur_credit.location_id,
p_from_tax_type => i.tax_type,
p_from_service_type => cur_credit.service_type_code,
p_from_trx_amount => ln_transfer_amt,
p_to_party_type => cur_debit.party_type,
p_to_party_id => cur_debit.party_id,
p_to_locn_id => cur_debit.location_id,
p_to_tax_type => i.tax_type,
p_to_service_type => cur_debit.service_type_code,
p_to_trx_amount => ln_transfer_amt,
p_called_from => 'SETTLEMENT',
p_trx_date => i.settlement_date,
p_acct_req => jai_constants.yes,
p_source => 'SETTLEMENT',
p_source_trx_type => 'SETTLEMENT',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_doc_id => pn_settlement_id,
p_settlement_id => pn_settlement_id,
p_reference_id => NULL,
p_process_flag => pv_process_flag,
p_process_message => pv_process_message,
p_accounting_date => i.settlement_date);
insert_into_vat_register(p_repository_id => ln_repository_id,
p_regime_id => i.regime_id,
p_from_party_type => cur_credit.party_type,
p_from_party_id => cur_credit.party_id,
p_from_locn_id => cur_credit.location_id, --added for bug#7145898 on 25-Dec-2009 by Eric Ma
p_from_tax_type => i.tax_type,
p_from_trx_amount => ln_transfer_amt,
p_to_party_type => cur_debit.party_type,
p_to_party_id => cur_debit.party_id,
p_to_locn_id => cur_debit.location_id,
p_to_tax_type => i.tax_type,
p_to_trx_amount => ln_transfer_amt,
p_called_from => 'SETTLEMENT',
p_trx_date => i.settlement_date,
p_acct_req => jai_constants.yes,
p_source => 'SETTLEMENT',
p_source_trx_type => 'SETTLEMENT',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_doc_id => ln_dist_dtl_id,
p_settlement_id => pn_settlement_id,
p_reference_id => NULL,
p_process_flag => pv_process_flag,
p_process_message => pv_process_message,
p_accounting_date => i.settlement_date);
update jai_rgm_stl_balances
SET debit_balance = debit_balance - ln_transfer_amt
WHERE rowid = cur_debit.rowid;
update jai_rgm_stl_balances
SET credit_balance = credit_balance - ln_transfer_amt
WHERE rowid = cur_credit.rowid;
ln_crdt_transfer.delete;
FOR I in (select *
from jai_rgm_stl_balances
where settlement_id = pn_settlement_id)
LOOP
IF NVL(i.debit_balance,0) = NVL(i.credit_balance,0) THEN
UPDATE jai_rgm_trx_records
SET settled_flag = 'Y',
settled_amount = NULL
WHERE tax_type = i.tax_type
AND organization_id = i.party_id
AND nvl(location_id,-999) = nvl(i.location_id,-999)
AND organization_type = i.party_type
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
AND settlement_id <= pn_settlement_id;
SELECT count(*)
INTO ln_debit_cnt
FROM jai_rgm_trx_records
WHERE tax_type = i.tax_type
AND organization_id = i.party_id
AND nvl(location_id,-999) = nvl(i.location_id,-999)
AND organization_type = i.party_type
AND settlement_id <= pn_settlement_id
AND NVL(settled_flag,'N') <> 'Y'
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) < 0;
SELECT count(*)
INTO ln_credit_cnt
FROM jai_rgm_trx_records
WHERE tax_type = i.tax_type
AND organization_id = i.party_id
AND nvl(location_id,-999) = nvl(i.location_id,-999)
AND organization_type = i.party_type
AND settlement_id <= pn_settlement_id
AND NVL(settled_flag,'N') <> 'Y'
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0;
/*Need to discuss how to update for TCS*/
UPDATE jai_rgm_trx_records
SET settled_amount = nvl(settled_amount, 0) -ln_transfer_amt,
settled_flag = 'P'
WHERE rowid = cur_debit.rowid;
UPDATE jai_rgm_trx_records
SET settled_amount = nvl(settled_amount, 0) + ln_transfer_amt,
settled_flag = 'P'
WHERE rowid = cur_credit.rowid;
UPDATE jai_rgm_trx_records
SET settled_amount = NVL(settled_amount,0) - ln_transfer_amt,
settled_flag = 'P'
WHERE rowid = cur_debit.rowid;
UPDATE jai_rgm_trx_records
SET settled_amount = NVL(settled_amount,0) + ln_transfer_amt,
settled_flag = 'P'
WHERE rowid = cur_credit.rowid;
UPDATE jai_rgm_trx_records
SET settled_flag = 'Y',
settled_amount = debit_amount*-1
WHERE settlement_id <= pn_settlement_id
AND organization_id = i.party_id
AND organization_type = i.party_type
AND nvl(location_id,-999) = nvl(i.location_id,-999)
AND tax_type = i.tax_type
AND debit_amount > 0
AND debit_amount = settled_amount*-1
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
);
UPDATE jai_rgm_trx_records
SET settled_flag = 'Y',
settled_amount = credit_amount
WHERE settlement_id <= pn_settlement_id
AND organization_id = i.party_id
AND nvl(location_id,-999) = nvl(i.location_id,-999)
AND organization_type = i.party_type
AND tax_type = i.tax_type
AND credit_amount > 0
AND credit_amount = settled_amount
--Added by Qiong for reverse charge settlement
AND ( (NVL(pv_reverse_charge_flag,'N')='N' AND source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(pv_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
);
pn_last_updated_by IN ap_invoices_interface.last_updated_by%TYPE,
pd_last_update_date IN ap_invoices_interface.last_update_date%TYPE,
pn_last_update_login IN ap_invoices_interface.last_update_login%TYPE,
pv_system_invoice_no OUT NOCOPY jai_rgm_settlements.system_invoice_no%TYPE,
pv_process_flag OUT NOCOPY VARCHAR2,
pv_process_message OUT NOCOPY VARCHAR2)
IS
/* Bug 5243532. Added by Lakshmi Gopalsami
* (1) Removed the cursor c_functional_currency which is referring
* to hr_operating_units and implemented using caching logic.
* (2) Removed cursor cur_currency_precision as the precision
* is derived using caching logic.
*/
CURSOR for_terms_id(ven_id NUMBER,ven_site_id NUMBER) IS
SELECT terms_id,
--payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
pay_group_lookup_code
FROM po_vendor_sites_all
WHERE vendor_id = pn_vendor_id
AND vendor_site_id = pn_vendor_site_id;
SELECT terms_id,
--payment_method_lookup_code, --commented the column by Sanjikum for Bug#4482462
pay_group_lookup_code
FROM po_vendors
WHERE vendor_id = pn_vendor_id;
SELECT NVL(MAX(line_number),0)
FROM ap_invoice_lines_interface
-- bug 4929081. Added by Lakshmi Gopalsami
WHERE invoice_id = pn_invoice_id;
SELECT jai_rgm_settlements_s1.NEXTVAL --rchandan for bug#4487676. JAI_RGM_SETTLEMENT_INVOICE_S is replaced with JAI_RGM_SETTLEMENTS_S1
FROM dual;
SELECT tax_type, debit, credit, NVL(debit,0) - NVL(credit,0) balance_amount
FROM JAI_RGM_STL_BALANCES_V
WHERE settlement_id = pn_settlement_id
AND NVL(debit,0) - NVL(credit,0) > 0;
SELECT party_id ,
location_id ,
service_type_code ,
tax_type ,
sum(debit_balance) debit_balance ,
sum(credit_balance) credit_balance,
/**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
NVL(sum(debit_balance),0) - NVL(sum(nvl(credit_utilized,credit_balance)),0) balance_amount
FROM JAI_RGM_STL_BALANCES
WHERE settlement_id = pn_settlement_id
GROUP BY party_id,location_id,service_type_code,tax_type
HAVING sum(debit_balance) - sum(nvl(credit_utilized,credit_balance)) > 0 ;/**added nvl(credit_utilized for bugdget 2011 ,er#11821537*/
SELECT party_id,location_id,tax_type,
sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
FROM JAI_RGM_STL_BALANCES
WHERE settlement_id = pn_settlement_id
GROUP BY party_id,location_id,tax_type
HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
SELECT sum(nvl(trx_credit_amount,0))
FROM JAI_RGM_trX_records
WHERE settlement_id = pn_settlement_id
and source_trx_type='SETTLEMENT'
and source_table_name='JAI_RGM_SETTLEMENTS'
and nvl(trx_credit_amount,0)>0
and organization_id=cp_org_id
and location_id=cp_loc_id
and tax_type=cp_tax_type
AND account_name = 'RECOVERY';/*Bug 13788285*/
SELECT SUM(total_tax_amt * decode(sign(total_tax_amt), -1, -1, 0))
FROM jai_rgm_refs_all
WHERE settlement_id = pn_settlement_id
-- and source_document_type='SETTLEMENT'
-- and source_table_name='JAI_RGM_SETTLEMENTS' --mmurtuza need to discuss
--and nvl(trx_credit_amount,0)>0
and organization_id=cp_org_id
and location_id=cp_loc_id;
SELECT party_id,location_id,tax_type,
sum(debit_balance) debit_balance, sum(credit_balance) credit_balance,
NVL(sum(debit_balance),0) - NVL(sum(credit_balance),0) balance_amount
FROM JAI_RGM_STL_BALANCES
WHERE settlement_id = pn_settlement_id
GROUP BY party_id,location_id,tax_type;
SELECT attribute_sequence, attribute_code tax_type, RATE
FROM JAI_RGM_REGISTRATIONS
WHERE regime_id = pn_regime_id
AND registration_type = p_reg_type--rchandan for bug#4428980
ORDER BY 1 ASC;
SELECT regime_code,description
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = pn_regime_id;
SELECT party_id,location_id
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
GROUP BY party_id,location_id
HAVING sum(debit_balance) - sum(credit_balance) > 0;
SELECT 'Y'
FROM ap_invoices_interface
WHERE invoice_id = pn_invoice_id;
jai_ap_utils_pkg.insert_ap_inv_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_num => lv_invoice_num,
p_invoice_date => v_open_gl_date,
p_gl_date => v_open_gl_date,
p_vendor_id => pn_vendor_id,
p_vendor_site_id => pn_vendor_site_id,
p_invoice_amount => ROUND(pn_invoice_amount, ln_precision),
p_invoice_currency_code => lv_currency_code,
p_terms_id => for_terms_id_rec.terms_id,
p_description => 'Settlement of '||lv_regime.description||' Liability on '||pd_settlement_date||' for registration no '||pv_regsitration_no, /*4245365*/
/* Bug 5359044. Added by Lakshmi Gopalsami
* Changed the p_source from 'EXTERNAL'
* to 'INDIA TAX SETTLEMENT INVOICES'
*/
/* Bug 5373747. Added by Lakshmi Gopalsami
* As per the discussion with AP Team changing the source
* as 'INDIA TAX SETTLEMENT'
*/
p_source => 'INDIA TAX SETTLEMENT',
p_voucher_num => lv_invoice_num,
--p_payment_method_lookup_code => for_terms_id_rec.payment_method_lookup_code, --commented by Sanjikum for Bug#4482462
p_pay_group_lookup_code => for_terms_id_rec.pay_group_lookup_code,
p_org_id => pn_org_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
p_amount => ROUND(i.balance_amount,ln_precision),
p_accounting_date => v_open_gl_date,
p_description => lv_regime.description||' Liability Payment for Tax Type '||i.tax_type||' of Service Type '||i.service_type_code, /*4245365*//* added by ssawant for bug 5879769 . Added service_type_code*/
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
-- p_amount => ROUND(i.balance_amount,ln_precision),
p_amount => ROUND(ln_line_amount,ln_precision),
p_accounting_date => v_open_gl_date,
p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type, /*4245365*/
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
p_amount => ROUND(i.balance_amount,ln_precision),
p_accounting_date => v_open_gl_date,
p_description => lv_regime.description||' Liability Payment for Organization:'||i.party_id||'Location:'||i.location_id||' Tax Type: '||i.tax_type,
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
p_amount => ln_amount1,
p_accounting_date => v_open_gl_date,
p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type1,
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
p_amount => ln_amount2,
p_accounting_date => v_open_gl_date,
p_description => 'Service Tax Excess Payment for Tax Type '||lv_tax_type2,
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
jai_ap_utils_pkg.insert_ap_inv_lines_interface(
p_jai_source => 'SETTLEMENT',
p_invoice_id => ln_invoice_id,
p_invoice_line_id => ln_invoice_line_id,
p_line_number => counter_tds_dm_v,
p_line_type_lookup_code => 'ITEM',
p_amount => ln_amount,
p_accounting_date => v_open_gl_date,
p_description => 'Value Added Tax Excess Payment for Tax Type '||lv_tax_type,
p_dist_code_combination_id => ln_dist_code_combination_id,
p_created_by => pn_created_by,
p_creation_date => pd_creation_date,
p_last_updated_by => pn_last_updated_by,
p_last_update_date => pd_last_update_date,
p_last_update_login => pn_last_update_login);
SELECT MAX(jbal.settlement_date)
FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl --bug 8974544
WHERE jbal.settlement_id = jstl.settlement_id --bug 8974544
AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N')--Added by Qiong for reverse charge settlment
AND jstl.regime_id = pn_regime_id
and party_id = pn_org_id;
SELECT MAX(jbal.settlement_date)
FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.regime_id = pn_regime_id
AND party_id = pn_org_id
AND location_id = pn_location_id
AND NVL(jstl.reverse_charge_flag,'N') = NVL(pv_reverse_charge_flag,'N');--Added by Qiong for reverse charge settle
SELECT debit_balance, credit_balance
FROM JAI_RGM_STL_BALANCES
WHERE party_id = pn_org_id
AND tax_type = pv_tax_type
AND settlement_date = (SELECT MAX(settlement_date)
FROM JAI_RGM_STL_BALANCES
WHERE party_id = pn_org_id
AND tax_type = pv_tax_type);
SELECT debit_balance, credit_balance
FROM JAI_RGM_STL_BALANCES
WHERE party_id = pn_org_id
AND tax_type = pv_tax_type
AND settlement_id = (SELECT MAX(settlement_id)
FROM JAI_RGM_STL_BALANCES
WHERE party_id = pn_org_id
AND tax_type = pv_tax_type);
SELECT sum(debit_balance), sum(credit_balance) /* added sum by ssawant for bug 5879769 */
FROM JAI_RGM_STL_BALANCES
WHERE party_id = pn_org_id
AND location_id = pn_location_id
AND tax_type = pv_tax_type
-- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
AND nvl(service_type_code,'-999') = nvl(pv_service_type_code,nvl(service_type_code,'-999') ) /* added nvl for bug#13865856*/
AND settlement_id = (SELECT MAX(jbal.settlement_id)
FROM JAI_RGM_STL_BALANCES jbal,
jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.regime_id = pn_regime_id
AND party_id = pn_org_id
AND location_id = pn_location_id
-- AND nvl(service_type_code,'-999') = nvl(pv_service_type_code, '-999' ) /* added by ssawant for bug 5879769 */
AND nvl(service_type_code,'-999') = nvl(pv_service_type_code,nvl(service_type_code,'-999') ) /* added nvl for bug#13865856*/
AND tax_type = pv_tax_type);
SELECT tax_type ,
SUM(debit_balance) debit ,
SUM(credit_balance) credit,
NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) balance_amount,
party_id ,
party_type ,
location_id ,
service_type_code /* added by ssawant for bug 5879769 */
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
GROUP BY tax_type, party_type, party_id,location_id,service_type_code /* added by ssawant for bug 5879769 */
HAVING NVL(SUM(debit_balance),0) - NVL(SUM(credit_balance),0) > 0;
SELECT *
FROM jai_rgm_settlements
WHERE settlement_id = pn_settlement_id;
SELECT attribute_sequence, attribute_code tax_type, RATE
FROM JAI_RGM_REGISTRATIONS
WHERE regime_id = pn_regime_id
AND registration_type = p_reg_type --rchandan for bug#4428980
ORDER BY 1 ASC;
SELECT org_id
FROM po_vendor_sites_all
WHERE vendor_id = c_vendor_id
AND vendor_site_id = c_vendor_site_id;
SELECT regime_code,description
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = pn_regime_id;
SELECT party_id,location_id
FROM jai_rgm_stl_balances
WHERE settlement_id = pn_settlement_id
GROUP BY party_id,location_id
HAVING sum(debit_balance) - sum(credit_balance) > 0;
/*SELECT organization_id,location_id
FROM JAI_RGM_ORG_REGNS_V
WHERE regime_code = 'VAT'
AND rownum = 1; */
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => pn_regime_id,
p_tax_type => i.tax_type,
p_organization_type => jai_constants.orgn_type_io, /* added by ssawant for bug 5879769 */
p_organization_id => i.party_id,
p_location_id => i.location_id , /* added by ssawant for bug 5879769 */
p_source => jai_constants.source_settle_in,
p_source_trx_type => 'Invoice Payment',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_document_id => pn_settlement_id,
p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => i.balance_amount,
p_discounted_amount => ln_discounted_amount,
p_assessable_value => NULL,
p_tax_rate => NULL,
p_reference_id => NULL,
p_batch_id => NULL,
p_called_from => 'JAIRGMSP',
p_accntg_required_flag => jai_constants.no,
p_process_flag => pv_process_flag,
p_process_message => pv_process_message,
p_accounting_date => pd_transaction_date,
p_currency_code => jai_constants.func_curr, --File.Sql.35 Cbabu
p_service_type_code => i.service_type_code /* added by ssawant for bug 5879769 */
);
UPDATE jai_rgm_trx_records
SET settlement_id = pn_settlement_id
WHERE repository_id = ln_repository_id;
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => pn_regime_id,
pv_tax_type => i.tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => i.party_id,
pn_location_id => i.location_id,
pv_source => jai_constants.source_settle_in,
pv_source_trx_type => 'Invoice Payment',
pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
pn_source_id => pn_settlement_id,
pd_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
pv_account_name => jai_constants.recovery,
pn_charge_account_id => ln_charge_accounting_id,
pn_balancing_account_id => NULL,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => NULL,
pn_batch_id => NULL,
pn_inv_organization_id => i.party_id,
pv_invoice_no => NULL,
pv_called_from => 'JAIRGMSP',
pv_process_flag => pv_process_flag,
pv_process_message => pv_process_message,
pd_invoice_date => NULL
);
UPDATE jai_rgm_trx_records
SET settlement_id = pn_settlement_id
WHERE repository_id = ln_repository_id;
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => pn_regime_id,
p_tax_type => lv_tax_type1,
p_organization_type => jai_constants.orgn_type_ou,
p_organization_id => ln_org_id,
p_location_id => NULL,
p_source => jai_constants.source_settle_in,
p_source_trx_type => 'Invoice Payment',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_document_id => pn_settlement_id,
p_transaction_date => pd_transaction_date,/* +1 is removed by ssawant for bug 5662296 */
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_amount1,
p_discounted_amount => ln_discounted_amount,
p_assessable_value => NULL,
p_tax_rate => NULL,
p_reference_id => NULL,
p_batch_id => NULL,
p_called_from => 'JAIRGMSP',
p_accntg_required_flag => jai_constants.no,
p_process_flag => pv_process_flag,
p_process_message => pv_process_message,
p_accounting_date => pd_transaction_date
, p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
);
jai_cmn_rgm_recording_pkg.insert_repository_entry(
p_repository_id => ln_repository_id,
p_regime_id => pn_regime_id,
p_tax_type => lv_tax_type2,
p_organization_type => jai_constants.orgn_type_ou,
p_organization_id => ln_org_id,
p_location_id => NULL,
p_source => jai_constants.source_settle_in,
p_source_trx_type => 'Invoice Payment',
p_source_table_name => 'JAI_RGM_SETTLEMENTS',
p_source_document_id => pn_settlement_id,
p_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
p_account_name => NULL,
p_charge_account_id => NULL,
p_balancing_account_id => NULL,
p_amount => ln_amount2,
p_discounted_amount => ln_discounted_amount,
p_assessable_value => NULL,
p_tax_rate => NULL,
p_reference_id => NULL,
p_batch_id => NULL,
p_called_from => 'JAIRGMSP',
p_accntg_required_flag => jai_constants.no,
p_process_flag => pv_process_flag,
p_process_message => pv_process_message,
p_accounting_date => pd_transaction_date
, p_currency_code => jai_constants.func_curr --File.Sql.35 Cbabu
);
jai_cmn_rgm_recording_pkg.insert_vat_repository_entry(
pn_repository_id => ln_repository_id,
pn_regime_id => pn_regime_id,
pv_tax_type => lv_tax_type,
pv_organization_type => jai_constants.orgn_type_io,
pn_organization_id => org_io_rec.party_id,
pn_location_id => org_io_rec.location_id,
pv_source => jai_constants.source_settle_in,
pv_source_trx_type => 'Invoice Payment',
pv_source_table_name => 'JAI_RGM_SETTLEMENTS',
pn_source_id => pn_settlement_id,
pd_transaction_date => pd_transaction_date, /* +1 is removed by ssawant for bug 5662296 */
pv_account_name => NULL,
pn_charge_account_id => NULL,
pn_balancing_account_id => NULL,
pn_credit_amount => ln_credit_amount,
pn_debit_amount => ln_debit_amount,
pn_assessable_value => NULL,
pn_tax_rate => NULL,
pn_reference_id => NULL,
pn_batch_id => NULL,
pn_inv_organization_id => org_io_rec.party_id,
pv_invoice_no => NULL,
pv_called_from => 'JAIRGMSP',
pv_process_flag => pv_process_flag,
pv_process_message => pv_process_message,
pd_invoice_date => NULL
);
SELECT MAX(jbal.settlement_date)
FROM JAI_RGM_STL_BALANCES jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.regime_id = pn_regime_id
AND jstl.primary_registration_no = pn_regn_no
AND jbal.party_id = nvl(pn_organization_id,jbal.party_id)
AND jbal.location_id = nvl(pn_location_id,jbal.location_id);
select settlement_id from
(
select jstl.party_id,jstl.location_id,jstl.settlement_id from
jai_rgm_stl_balances jstl,
jai_rgm_Settlements jrs,
JAI_RGM_DEFINITIONS jr
WHERE jstl.settlement_id = jrs.settlement_id
AND jrs.regime_id = jr.regime_id
AND jr.regime_code = 'VAT'
AND jrs.primary_registration_no = pn_regn_no -- 12996230
group by jstl.party_id,jstl.location_id,jstl.settlement_id
)group by settlement_id
having count(*) >1
order by settlement_id desc;
SELECT settlement_date FROM
jai_rgm_stl_balances where settlement_id = cp_settlement_id
and rownum=1;