[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
14-OCT-2008 JMEENA for bug#7445742
Incorporate the changes of bug#6835541
--------------------------------------------------------------------------------------*/
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
);
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
);
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;
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
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'
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;
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;
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,/* added by ssawant for bug 5879769 */
p_from_tax_type => i.tax_type,
p_from_service_type => cur_credit.service_type_code,/* added by ssawant for bug 5879769 */
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,/* added by ssawant for bug 5879769 */
p_to_tax_type => i.tax_type,
p_to_service_type => cur_debit.service_type_code,/* added by ssawant for bug 5879769 */
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 => NULL,
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;
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
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'
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'
AND NVL(credit_amount,0) - NVL(debit_amount,0) - NVL(settled_amount,0) > 0;
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;
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;
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,
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,service_type_code,tax_type
HAVING sum(debit_balance) - sum(credit_balance) > 0 ;
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 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_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(settlement_date)
FROM JAI_RGM_STL_BALANCES
WHERE 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;
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 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 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);