[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PURPOSE : To get the balances , to insert records into repository
CALLED FROM : jai_cmn_rgm_settlement_pkg , JAIRGMDT.fmb , JAIRGMDT.fmb
/* -------------------------------------------------------------------------------------------------------------------
1. 08-Jun-2005 File Version 116.2. Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
as required for CASE COMPLAINCE.
2. 13-Jun-2005 rchandan for bug#4428980. File Version: 116.3
Removal of SQL LITERALs is done
3. 17-Aug-2005 Ramananda for bug#4557267 (Fwd porting for the bug 4276280 ) during R12 Sanity Testing. File Version 120.2
The Settlement form was erroring out when get_details button was pressed giving a
message "cannot insert NULL into debit balances". This was happening if the last
settlement balnce amount was NULL. From now it will be taken as zero instead of NULL.
While inserting into temp table nvl check is added for ln_settled_credit_balance
and ln_settled_debit_balance.
NVL() is added to the following parameters:
p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt
p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt
4. 25-Aug-2005 Bug4568078. Added by Lakshmi Gopalsami Version 120.3
(1) Added parameter p_pla_balance in procedure
insert_records_into_temp
(2) Added pla_balance while inserting into jai_rgm_balance_t
(3) Added nvl(pla_balance,0) in cursor c_balance_cur in
procedure calculate_balances_for_io. Added cursor
c_pla_cess_balance to fetch the pla_balance and
passed the same to insert_records_into_temp
(4) Passed NULL for p_pla_balance in the call to
insert_records_into_temp in procedure
calculate_balances_for_ou.
Dependencies:(Functional+Compilation)
------------
JAIRGMDT.fmb 120.3
5 27/03/2006 Hjujjuru for Bug 5096787 , File Version 120.4
Spec changes have been made in this file as a part og Bug 5096787.
Now, the r12 Procedure/Function specs is in this file are in
sync with their corrsponding 11i counterparts
6. 19/12/2006 CSahoo for Bug 5073553, File Version 120.5
1.Changed the procedure jai_rgm_distribution_pkg.calculate_balances_for_io such that in case
the pla balance is -ve, populate the column JAI_RGM_BALANCE_T debit_amt and JAI_RGM_BALANCE_T pla_balance as 0
else (+ve value for PLA balance) let the value of o be populated into JAI_RGM_BALANCE_T debit_amt and pla_balance
would be the register pla_amt amount for the IO.
2.Added a new function f_get_io_register in both package spec and body .
7. 30/01/2007 SACSETHI FOR BUG#5631784. FILE VERSION 120.7
FORWARD PORTING BUG FROM 11I BUG 4742259
NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
Changes -
OBJECT TYPE OBJECT NAME CHANGE DESCRIPTION
--------------------------------------------------------------------------------------
PROCEDURE PUNCH_SETTLEMENT_ID ARGUMENT ADDED P_TAN_NO IS ADDED
PROCEDURE PUNCH_SETTLEMENT_ID ARGUMENT ADDED P_ITEM_CLASSIFICATION IS ADDED
PROCEDURE PUNCH_SETTLEMENT_ID CODE ADDED UPDATATION OF SETTLEMENT_ID IN TABLE
JAI_RGM_REFS_ALL FOR TCS
PROCEDURE GET_BALANCES ARGUMENT ADDED P_ITEM_CLASSIFICATIONIS ADDED
PROCEDURE GET_BALANCES CURSOR ADDED CUR_REGIME_CODE IS ADDED
PROCEDURE GET_BALANCES CODE ADDED PROCEDURE CALCULATE_RGM_BALANCES FOR TCS
PROCEDURE CALCULATE_RGM_BALANCES NEW CREATED NEWLY PROCEDURE ADDED FOR TCS
----------------------------------------------------------------------------------------------------------------
8 23/04/2007 bduvarag for the Bug#5879769, file version 120.8
Forward porting the changes done in 11i bug#5694855
9 7-June-2007 ssawant for bug 5662296
Forward porting R11 bugs 5642053 and 4346527 to R12 bug 5662296.
10. 09-June-2007 CSahoo for BUG#6109941 , FileVersion 120.11
Added the sh cess types.
11. 16-Jul-2007 CSahoo for bug#6235971, File Version 120.13
added the following and condition in the for loop
"AND a.settlement_id IS NULL".
12. 10-OCT-2008 JMEENA for bug#7445742
Modified procedure calculate_balances_for_ou
and added condition source <> 'VAT REVERSAL' in the query.
13.12-Nov-2008 Changes by nprashar for bug 6359082, Forward port the changes from 11i bug 6348081.
14. 20-Nov-2008 Changes by nprashar for bug # 7525691, FP changes of 11 i bug 7518230.
Issue : SVC TX SETTLEMENT PROCESS WITH DIFF SVC TYPES DIDN'T CREATE NETTING SERVICE JE
Fix: modified the code in the procedure insert_records_into_register. Added a variable
lv_balancing_entry. The value is set as N for settlement else it is null. Passed this
variable to the procedure insert_repository_entry
15 30-dec-2008 Vkaranam for bug#6773684,file version 120.4.12000000.8/120.14.12010000.5/120.20
Issue:
SERVICE TAX DISTRIBUTION IN PLA/RG DOES NOT RESULT IN PLA REGISTER
Reason:
Cursor 'cur_get_dist_plg_rg' is used to fetch "Service Tax Distribution in PLA/RG" setup value.
In this cursor the organization_type is given as 'OU'.But after the 'Service tax by IO' enhancement\
service tax at OU level is not supported.Due to this 'cur_get_dist_plg_rg' always return null and the code in function f_get_io_register
always return 'RG'.
Fix:
1)
Changes are done in function f_get_io_register .
1.1 Removed organization_type='OU' condition from Cursor 'cur_get_dist_plg_rg'.
1.2 Added a conditon to get the value as 'RG' ,If the setup is not done.
2)
while calling the f_get_io_register_type ,party id is passed as p_to_party_id if the transfer is "Service --Excise"
and p_from_party_id is passed if the transfer is from "Excise-- Service" .
Changes are done as per the above.
15 04-feb-2009 Vkaranam for bug#6773684,file version 120.4.12000000.9/120.14.12010000.6/120.21
Revereted back the changes done in fp bug# 7525691 as the fix is not yet tested/released.
16 18-Mar-2009 Bug 7525691 File version 120.4.12000000.10/120.14.12010000.7/120.22
Added parameter p_distribution_type when calling jai_cmn_rgm_recording_pkg.insert_repository_entry
17 21-Jul-2009 CSahoo for bug#8702609, File Version 120.4.12000000.13
Issue: ISSUES WITH SERVICE TAX DISTRIBUTION AND SETTLEMENT FORMS
Fix: modified the code in the procedure calculate_balances_for_ou. Initially the settled amount
was getting added up to the amount the to be distributed. So modified the code so that the
settled amount no more gets added up.
18 22-Jul-2009 CSahoo for bug#8289991, File Version 120.4.12000000.14
Issue: FP12.0 :7828827 SERVICE TAX CREDIT AMOUNT IS NOT CARRYING FORWARD TO NEXT SETTLEMENT
FIX: Modified the procedure calculate_balances_for_ou. Removed the logic of calculating the
credit and debit balance on the basis of the service type. Added the code to obtain
total credit and debit amount from the last settlement date to the new settlement date.
Then check the last settled credit and debit amount for each tax type. If there is a
credit carry forward then it is added to the total credit amount to be settled.
19 28-jul-2009 vumaasha for bug 8657720, reverted the change done for the bug 7445742
20 11-sep-2009 vkaranam for bug#8873924
Issue:SERVICE TAX DISTRIBUTION WITH EXCISE-SERVICE TRANSFER IS HITTING RG23A
Reason:
If the service tax distribution in RG/PLA setup has been given as "RG" ,and excise to transfer is always
hitting RG23A' register eventhough the balance is not available in that.
register_type='A' has been hardcode with setup as "RG"
hence the issue.
Fix:
If the service tax distribution in RG/PLA setup as "RG" and with "Excise-service" Transfer
,either RG23A/RG23C register will get hit based on the Register prefernces and the balance available in
the individual registers.
Changes are done in create_io_register_entry procedure to fetch the register_type based on
Register prefernces and the balance available in the RG23A/C registers for excise-service transfer.
20. 12-oct-2009 vkaranam for bug#9005474
issue:
TCS tax is geeting doubled during the settlement
Reason:
Issue is that jai_rgm_balance_tmp is popualted with double amount.
Issue is with the jai_cmn_rgm_tax_dist_pkg.CALCULATE_RGM_BALANCES procedure
JAI_RGM_ORG_REGNS_V is retreiving 2 rows for TCS type of taxes.
This will occuer only if the organization is associated with more than one location.
Fix:
Removed the JAI_RGM_ORG_REGNS_V in cursor for delta_rec in (
Added the table jai_rgm_registrations table.
21 14-oct-2009 vkaranam for bug#9005474
Added the condition jrr.organization_id=nvl(p_org_id,jrr.organization_id)
as per review comments
22 03-Dec-2009 Added by Jia for FP Bug#6174148
Issue:
Vendor_id has been updated with org_id in table JA_IN_RG23_PART_II,
this caused the vendor name to be displayed instead of org_name in rg23 Part II form.
Fix:
This was a forward port issue of the R11i Bug#6129789.
Code changes are done in insert_records_into_register procedure.
Vendor_id has been inserted with -1 * org_id to solve the above issue.
22 19-Dec-2009 Eric for bug#8333082 and bug8671217
23 10-Mar-2009 Bug 9445836
Issue - New transactions which have transaction date lying in settled period
are not considered for the next settlement.
Fix - Changed the filter condition to fetch delta records in calculate_balances_for_ou
procedure. Instead of getting the transactions with date between last settlement date
and new settlement date, we fetch all unsettled transactions with date less than
the new settlement date.
Also modified the filter for update statements in punch_settlement_id procedure.
24 18-mar-2011 vkaranam for er#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:
Added the code in calculate_balances_for_ou procedure to get credit utilization amount
and further passed the same to insert_records_into_temp procedure.
25 30-mar-2011 vkaranam for er#11821537 ( QA bug 11923714)
Issue:
STAX.18-2011.11I.QA: ERROR IN SERVICE TAX SETTLEMENT
credit utilized shall not be rounded.
Fix:
removed the rounding factor while calculating the credit_utilized column.
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 populate_all_orgs_vat procedure.
26 18-sep-2011 vkaranam for bug#12706846 ( 12996230)
Issue: Credit balance shown in the settlement screen is wrong.
Fix : changes are done in populate_all_orgs_vat procedure.
27 01-Feb-2012 amandali for bug 13534704
Issue: Distribution number not matching with invoice number in RG 23 Part II view transactions form
Fix: Added cursor c_get_src_rec to fetch distribution number from jai_rgm_dis_src_hdrs
the parameter p_excise_invoice_no in procedure call create_io_register_entry has been modified to distribution number rather
than the transfer id prefixed with Distribution
28 21-mar-2012 vkaranam for bug#13865856
Issue:Service type to be made optional for Service tax distribution.
fix:changes are done in calculate_balances_for_ou procedure.
added the nvl condition for service_type_code condition ,which are used for fetching
the balances.
29 10-AUG-2012 amandali for bug 14475128
Issue:SER. TAX DISTRIBUTION FORM DISPLAYING AMOUNT ALREADY DISTRIBUTED TO EXCISE
Fix:changes are done in calculate_balances_for_ou procedure.Modified the condition for service_type_code to handle the records with service_type_code as null
-- #
-- # Change History -
-- # 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 Files Version Author Date Remarks
Of File On Bug/Patchset Dependent On
------------------------------------------------------------------------------------------------------------------------------------------------------------------
115.1 4245365 4245089 rchandan 17/Mar/05 Changes made to implement VAT
115.2 4245365 4245089 rchandan 20/03/2005 Observations in VAT. From now when we are settling
balances the opening balance of the last settlemnt date is not considered if it was completely settled.
Only the transaction amount in the delta period is taken into consideration. If it is not settled then
the settlement balances are taken into consideration
11.22-jun-2007 kunkumar made changes for bug#6127194 file 120.11
Added package body to create_io_register_entry and
made calls to the proc from insert_into_register proc.
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
***************************************************************************************************/
PROCEDURE insert_records_into_temp(
p_request_id NUMBER ,
p_regime_id NUMBER ,
p_party_type VARCHAR2 ,
p_party_id NUMBER ,
p_location_id NUMBER ,
p_bal_date DATE ,
p_tax_type VARCHAR2 ,
p_debit_amt NUMBER ,
p_credit_amt NUMBER ,
/* Bug4568078. Added by Lakshmi Gopalsami */
p_pla_balance NUMBER default NULL,
p_service_type_code VARCHAR2 DEFAULT NULL,/*Bug 5879769 bduvarag*/
/**added p_credit_utilized for bug#11821537 by vkaranam,budget 2011 phase2*/
p_credit_utilized NUMBER default null
,p_reverse_charge_flag VARCHAR2 DEFAULT NULL --Added by Qiong for reverse charge settlement
)
is
/* Added by Ramananda for bug#4407165 */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rgm_tax_dist_pkg.insert_records_into_temp';
INSERT INTO JAI_RGM_BALANCE_T
(
REQUEST_ID ,
REGIME_ID ,
PARTY_TYPE ,
PARTY_ID ,
LOCATION_ID ,
BALANCE_DATE ,
TAX_TYPE ,
DEBIT_AMT ,
CREDIT_AMT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN ,
program_application_id,
program_id,
program_login_id,
/* Bug 4568078. Added by Lakshmi Gopalsami */
pla_balance,
service_type_code /*Bug 5879769 bduvarag*/
,credit_utilized --11821537
,reverse_charge_flag --Added by qiong for reverse charge settlement
)
VALUES
(
p_request_id ,
p_regime_id ,
p_party_type ,
p_party_id ,
p_location_id ,
p_bal_date ,
p_tax_type ,
round(p_debit_amt,ln_rounding_precision) ,
round(p_credit_amt,ln_rounding_precision) ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
fnd_profile.value('PROG_APPL_ID'),
fnd_profile.value('CONC_PROGRAM_ID'),
fnd_profile.value('CONC_LOGIN_ID'),
/* Bug 4568078. Added by Lakshmi Gopalsami */
p_pla_balance,
p_service_type_code/*Bug 5879769 bduvarag*/
, p_credit_utilized --11821537
, p_reverse_charge_flag --Added by qiong for reverse charge settlement
);
END insert_records_into_temp;
and there will be only one record inserted in jai_rgm_balances_t as per the logic
and the organization,location insertion depends on the cursor fetching
fix 12706846 : populate_all_orgs_vat will populate the credit_balances available for ALL the organizations
which are not considered in the current settlement period .
*/
--this cursor will fetch the credit balance from the organization which doesnot have transactions in the current settlement period
/*Added parameter cp_regime_id by mmurtuza for bug 12641455*/
CURSOR c_other_orgs(cp_regn_no jai_rgm_org_regns_v.attribute_value%TYPE,cp_regn_id jai_rgm_org_regns_v.registration_id%TYPE, cp_regime_id jai_rgm_org_regns_v.regime_id%type)
IS
SELECT organization_id,
location_id
FROM jai_rgm_org_regns_v
WHERE --regime_code = jai_constants.vat_regime -- commneted by mmurtuza for bug 12641455
regime_id = cp_regime_id -- added by mmurtuza for bug 12641455
AND attribute_code ='REGISTRATION_NO'
AND attribute_value = cp_regn_no
AND registration_id = cp_regn_id
AND (organization_id, location_id) NOT IN
(SELECT party_id, location_id FROM JAI_RGM_BALANCE_T where request_id=p_request_id
);
SELECT
--(credit_balance - debit_balance) cr_balance, 8671217
( sum(nvl(credit_balance,0))-sum(nvl(debit_balance,0))) cr_balance ,
tax_type
FROM jai_rgm_stl_balances stl
WHERE settlement_id =
(SELECT MAX(jstl.settlement_id)
FROM jai_rgm_stl_balances jstl,
jai_rgm_Settlements jrs
WHERE
jstl.party_id = cp_org_id /* 8671217 ,12706846*/
AND jstl.location_id = cp_location_id /* 8671217 ,12706846*/
AND jstl.settlement_id = jrs.settlement_id
and jstl.settlement_id<>p_settlement_id ---8671217 (To exclude from the current settlement)
AND jrs.regime_id = cp_regime_id
)
/*added the where clause for bug#12706846*/
and stl.party_id = cp_org_id
AND stl.location_id = cp_location_id
group by tax_type;---8671217
select '1'
FROM JAI_RGM_SETTLEMENTS stL
WHERE settlement_id =
(SELECT MAX(jstl.settlement_id)
FROM jai_rgm_stl_balances jstl,
jai_rgm_Settlements jrs
WHERE
jstl.party_id = cp_org_id /* 8671217 ,12706846*/
AND jstl.location_id = cp_location_id /* 8671217 ,12706846*/
AND jstl.settlement_id = jrs.settlement_id
and jstl.settlement_id<>p_settlement_id ---8671217 (To exclude from the current settlement)
AND jrs.regime_id = cp_regime_id
)
AND payment_amount>0;
/***credit balance will updated only for the 1st organization in the c_other_orgs loop
for the remaining orgs cr_balance is 0.
***/
/*12656570 - Credit Balance should never go below zero*/
/*
IF ln_credit_set_off = 0 AND rec_cr_bal.cr_balance > 0 THEN
ln_cr_balance:= rec_cr_bal.cr_balance - nvl(p_cramt_considered,0);
insert_records_into_temp( p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => p_org_type ,
p_party_id => rec_other_orgs.organization_id,
p_location_id => rec_other_orgs.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => rec_cr_bal.tax_type ,
p_debit_amt => 0 ,
p_credit_amt => ln_cr_balance ,--rec_cr_bal.cr_balance,
p_pla_balance => NULL,
p_service_type_code => NULL
);
insert_records_into_temp( p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => p_org_type ,
p_party_id => rec_other_orgs.organization_id,
p_location_id => rec_other_orgs.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => rec_cr_bal.tax_type ,
p_debit_amt => 0 ,
p_credit_amt => rec_cr_bal.cr_balance,
p_pla_balance => NULL,
p_service_type_code => NULL
);
||instead now the criteria should be that in the form JAIRGMDT.fmb , user should not be able to select a IO with this sum = 0.
*/
CURSOR c_balance_cur is
SELECT org_unit_id org_unit_id ,
organization_id party_id ,
location_id location_id ,
'EXCISE' tax_type ,
nvl(rg23A_balance,0) + nvl(rg23c_balance,0) Balance ,
'IO' party_type,
/* Bug 4568078. Added by LGOPALSA */
nvl(pla_balance,0) pla_balance
FROM JAI_CMN_RG_BALANCES;
SELECT SUM(balance)
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = cp_org_unit_id
AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)--rchandan for bug#4428980
AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess);--rchandan for bug#4428980
SELECT SUM(balance)
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = cp_org_unit_id
AND register_type = jai_constants.reg_pla
AND tax_type IN (jai_constants.tax_type_cvd_edu_cess,jai_constants.tax_type_exc_edu_cess) ;
SELECT SUM(balance)
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = cp_org_unit_id
AND register_type IN (jai_constants.reg_rg23a ,jai_constants.reg_rg23c)
AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess);
SELECT SUM(balance)
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = cp_org_unit_id
AND register_type = jai_constants.reg_pla
AND tax_type IN (jai_constants.tax_type_sh_cvd_edu_cess,jai_constants.tax_type_sh_exc_edu_cess) ;
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => bal_rec.party_type ,
p_party_id => bal_rec.party_id ,
p_location_id => bal_rec.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => bal_rec.tax_type ,
/* changed by CSahoo for bug 5073553.
||put the variable ln_debit_amt instead of 0
*/
p_debit_amt => ln_debit_amt ,
p_credit_amt => bal_rec.balance ,
p_pla_balance => ln_pla_balance,
/* Bug 4568078. Added by Lakshmi Gopalsami */
p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
);
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => bal_rec.party_type ,
p_party_id => bal_rec.party_id ,
p_location_id => bal_rec.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => 'EXCISE-CESS' ,
p_debit_amt => ln_debit_amt , -- Added by CSahoo, BUG#5073553
p_credit_amt => ln_cess_balance ,
p_pla_balance => ln_pla_cess_balance,
p_service_type_code => p_service_type_code/*Bug 5879769 bduvarag*/
);
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => bal_rec.party_type ,
p_party_id => bal_rec.party_id ,
p_location_id => bal_rec.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => 'EXCISE_SH_EDU_CESS' ,
p_debit_amt => ln_sh_debit_amt ,
p_credit_amt => ln_sh_cess_balance ,
p_pla_balance => ln_sh_pla_cess_balance,
p_service_type_code => p_service_type_code
);
SELECT regime_code
FROM JAI_RGM_DEFINITIONS -- ADDED BY SACSETHI ON 30-01-2007 FOR BUG 5631784
WHERE regime_id = p_regime_id;
UPDATE jai_Rgm_trx_records
SET settlement_id = p_settlement_id
/*Bug 5879769 bduvarag*/
WHERE organization_id = p_org_id
AND location_id = p_location_id
AND regime_code = lv_regime
AND trunc(transaction_date) <= p_balance_date /*bug 9445836*/
AND settlement_id IS NULL /* added by ssawant for bug 5662296*/
AND ( (NVL(p_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(p_reverse_charge_flag,'N')='Y' AND source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
) ;--Added by Qiong for reverse charge settlement
UPDATE jai_Rgm_trx_records
SET settlement_id = p_settlement_id
WHERE (organization_id,location_id) in
(SELECT organization_id,location_id
FROM JAI_RGM_ORG_REGNS_V
WHERE registration_id = p_regn_id
AND attribute_value = nvl(p_regn_no, attribute_value) -- 6835541. Added by Lakshmi Gopalsami
AND regime_code = 'VAT'
AND organization_id = nvl(p_org_id,organization_id)
AND location_id = nvl(p_location_id,location_id)
)
AND regime_code = lv_regime --added for bug#8289991
AND trunc(transaction_date) <= p_balance_date /*bug 9445836*/
AND settlement_id IS NULL;
UPDATE JAI_RGM_REFS_ALL
SET SETTLEMENT_ID = P_SETTLEMENT_ID
WHERE ORG_TAN_NO = P_TAN_NO AND
ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION AND
TRUNC(SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE AND
SETTLEMENT_ID IS NULL;
SELECT regime_code
FROM JAI_RGM_DEFINITIONS
WHERE regime_id = p_regime_id;
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE regime_primary_regno = p_regn_no
AND 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 = p_regn_no
AND jbal.party_type = p_org_type
AND jbal.party_id = p_org_id
AND nvl(jbal.location_id,-999) = nvl(p_location_id,-999)
AND jbal.settlement_id <> nvl(p_settlement_id,-999)
);
SELECT credit_amount
FROM jai_rgm_trx_records
WHERE source_trx_type = 'Invoice Payment'
-- AND service_type_code = nvl(cp_service_type_code,service_type_code)/*added nvl for bug#13865856*/
and (cp_service_type_code is null or service_type_code =cp_service_type_code) /*Commented the above and added the condition for bug 14475128 */
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 jbal.party_type = cp_org_type
AND jbal.party_id = cp_org_id
AND jbal.location_id = nvl(cp_location_id,jbal.location_id)/*5694855*/
AND jbal.tax_type = cp_tax_type
-- AND jbal.service_type_code = nvl(cp_service_type_code,jbal.service_type_code)/*added nvl for bug#13865856*/
and (cp_service_type_code is null or jbal.service_type_code =cp_service_type_code) /*Commented the above and added the condition for bug 14475128 */
);
SELECT credit_amount
FROM jai_rgm_trx_records
WHERE /*regime_primary_regno = p_regn_no*/ -- Commented, Harshita for Bug 5694855
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 jbal.party_type = cp_org_type
AND jbal.party_id = cp_org_id
AND jbal.tax_type = cp_tax_type
);
SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance,jrs.settlement_id,jrs.tax_type,jrs.location_id
FROM jai_rgm_stl_balances jrs,
(
SELECT MAX(jbal.settlement_id) settlement_id,tax_type
FROM jai_rgm_stl_balances jbal,jai_rgm_settlements jstl
WHERE jbal.settlement_id = jstl.settlement_id
AND jstl.primary_registration_no = p_regn_no
AND jbal.party_type = p_org_type
AND jbal.party_id = p_org_id
AND NVL(jbal.location_id,-999) = NVL(p_location_id,-999)
AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
GROUP BY jbal.tax_type
) sv
WHERE
jrs.settlement_id=sv.settlement_id
AND jrs.tax_type= sv.tax_type
GROUP BY jrs.settlement_id,jrs.tax_type,jrs.location_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 = p_regn_no
AND jbal.party_type = p_org_type
AND jbal.party_id = p_org_id
AND NVL(jbal.location_id,-999) = NVL(p_location_id,-999)
AND jbal.settlement_id <> p_settlement_id;
SELECT sum(credit_amount)
FROM jai_rgm_trx_records
WHERE regime_primary_regno = p_regn_no
AND source_trx_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 = 'VAT'
AND jstl.primary_registration_no = p_regn_no
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> nvl(p_settlement_id,-999) /*This clause is used to exclude the current settlement*/
);
SELECT sum(credit_balance) credit_balance,sum(debit_balance) debit_balance,jrs.settlement_id,jrs.tax_type,jrs.location_id
FROM jai_rgm_stl_balances jrs,
( SELECT MAX(jbal.settlement_id) settlement_id,tax_type
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 = 'VAT'
AND jstl.primary_registration_no = p_regn_no
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
GROUP BY jbal.tax_type) sv
WHERE
jrs.settlement_id=sv.settlement_id
AND jrs.tax_type= sv.tax_type
GROUP BY jrs.settlement_id,jrs.tax_type,jrs.location_id ;
select * from
jai_rgm_settlements
where settlement_id=cp_stl_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 = 'VAT'
AND jstl.primary_registration_no = p_regn_no
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> p_settlement_id;
SELECT
organization_id ,
location_id ,
tax_type ,
nvl(sum(debit_amount),0) debit_amt ,
nvl(sum(credit_amount),0) credit_amt
FROM
jai_rgm_trx_records
WHERE trunc(transaction_date) <= p_balance_date --changed the date condition for bug 9445836
AND settlement_id IS NULL
AND source_trx_type <> 'Invoice Payment'
AND organization_id = cp_organization_id
AND location_id = cp_location_id
AND organization_type = cp_organization_type
AND tax_type = cp_tax_type
AND regime_code = cp_regime_code
-- AND service_type_code = nvl(p_service_type_code,service_type_code)--added nvl condition for bug#13865856
and (p_service_type_code is null or service_type_code =p_service_type_code) /* Commented the above and added the condition for bug 14475128 */
GROUP BY
organization_id,
location_id ,
tax_type
ORDER BY
tax_type;
SELECT sum(debit_balance), sum(credit_balance)
FROM JAI_RGM_STL_BALANCES
WHERE party_id = cp_org_id
AND location_id = cp_location_id
AND tax_type = cp_tax_type
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 = cp_regime_id
AND party_id = cp_org_id
AND location_id = cp_location_id
AND tax_type = cp_tax_type
AND NVL(jstl.reverse_charge_flag,'N') = nvl(p_reverse_charge_flag,'N'));--Added by Qiong for reverse charge settlement
lv_temp_insert varchar2(1);--Added by Eric Ma for bug 8671217 on Dec-19-2009
SELECT attribute_value
FROM jai_rgm_org_regns_v
WHERE regime_id = cp_regime_id
AND attribute_code = 'CRPTG_UTILIZE_ST'
AND attribute_type_code = 'OTHERS'
AND registration_type = 'OTHERS';
SELECT a.organization_id ,
a.location_id ,/*Bug 5879769 bduvarag*/
a.tax_type ,
nvl(sum(a.debit_amount),0) debit_amt ,
nvl(sum(a.credit_amount),0) credit_amt
FROM jai_rgm_trx_records a
WHERE trunc(transaction_date) <= p_balance_date --date condition changed for bug 9445836
AND a.settlement_id IS NULL/*rchandan for bug#5642053*/
AND a.regime_code = lv_regime_code/*5694855*/
AND a.organization_type = p_org_type/*5694855*/
AND a.organization_id = nvl(p_org_id,a.organization_id )
AND a.location_id = p_location_id/*5694855*/
AND a.source_trx_type <> 'Invoice Payment'
AND ( (NVL(p_reverse_charge_flag,'N')='N' AND a.source_trx_type NOT IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
OR (NVL(p_reverse_charge_flag,'N')='Y' AND a.source_trx_type IN ('REVERSE_PAID_ACCOUNTING','REVERSE_AVOID_ACCOUNTING','REVERSE_APPLY_ACCOUNTING','REVERSE_UNAPPLY_ACCOUNTING'))
)
GROUP BY a.organization_id ,
a.location_id ,/*5694855*/
a.tax_type
)/*Bug 5879769 bduvarag*/
LOOP
/*
insert the tax types for every operating unit for the delta period
ie .. between the last settlement date and the date of transfer.
get the debit balance and credit balance as on the last settlement for a given operating unit and tax type
and add the value in this table.
-- API call to settlement process.
*/
--ld_trx_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(delta_rec.organization_id) + 1;/* commented by ssawant for bug 5662296*/
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => p_org_type ,
p_party_id => delta_rec.organization_id ,
p_location_id => delta_rec.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => delta_rec.tax_type ,
p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
/* Bug 4568078. Added by Lakshmi Gopalsami */
p_pla_balance => NULL,
p_service_type_code => NULL /* modified by vumaasha for bug 7606212*/
,p_credit_utilized =>ln_credit_utilized /*added p_credit_utilized for er#11821537*/
,p_reverse_charge_flag => p_reverse_charge_flag --Added by Qiong for reverse charge settlement
);
SELECT
b.regime_id ,
a.organization_id ,
a.location_id ,
a.tax_type ,
a.organization_type , /*6835541*/
nvl(sum(a.debit_amount),0) debit_amt ,
nvl(sum(a.credit_amount),0) credit_amt
FROM
jai_rgm_trx_records a, JAI_RGM_ORG_REGNS_V b
WHERE trunc(transaction_date) <= p_balance_date --date condition changed for bug 9445836
AND a.settlement_id IS NULL --added by csahoo for bug#6235971
AND b.regime_id = p_regime_id/*5694855 bduvarag*/
AND a.regime_code = lv_regime_code/*5694855 bduvarag*/
AND a.organization_id = b.organization_id
AND a.location_id = b.location_id
AND a.organization_type = b.organization_type
AND b.registration_id = p_regn_id
AND a.organization_id = nvl(p_org_id,a.organization_id )
AND a.organization_type = nvl(p_org_type,a.organization_type)
AND b.attribute_value = p_regn_no
AND a.location_id = nvl(p_location_id,a.location_id)/*rchandan for bug#6835541. Added nvl*/
AND a.source_trx_type <> lv_source_trx_type--rchandan for bug#4428980
GROUP BY a.organization_id , a.tax_type,a.location_id,b.regime_id,a.organization_type /*6835541. added organization_type*/
)
LOOP
/*
insert the tax types for every IO for the delta period
ie .. between the last settlement date and the date of transfer.
get the debit balance and credit balance as on the last settlement for a given IO , Location and tax type
and add the value in this table.
-- API call to settlement process.
*/
--ld_trx_date := jai_cmn_rgm_settlement_pkg.get_last_settlement_date(delta_rec.regime_id,delta_rec.organization_id,delta_rec.location_id) + 1; /* commented by ssawant for bug 5662296*/
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => delta_rec.organization_type,/*6835541*/
p_party_id => delta_rec.organization_id ,
p_location_id => delta_rec.location_id ,
p_bal_date => p_balance_date ,
p_tax_type => delta_rec.tax_type ,
p_debit_amt => nvl(ln_settled_debit_balance,0) + delta_rec.debit_amt , --4557267
p_credit_amt => nvl(ln_settled_credit_balance,0) + delta_rec.credit_amt, --4557267
/* Bug 4568078. Added by Lakshmi Gopalsami */
p_pla_balance => NULL
);
SELECT regime_id,
attribute_code tax_type
FROM jai_rgm_org_regns_v
WHERE organization_id = p_org_id
AND location_id = p_location_id
AND organization_type = p_org_type
AND registration_type = jai_constants.regn_type_tax_types
AND regime_code = lv_regime_code
)
LOOP
r_delta_rec := NULL;
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => p_org_type ,
p_party_id => p_org_id ,
p_location_id => p_location_id ,
p_bal_date => p_balance_date ,
p_tax_type => tax_types_rec.tax_type ,
-- modified for bug#8702609, start
p_debit_amt => nvl(r_delta_rec.debit_amt,0) ,
p_credit_amt => nvl(r_delta_rec.credit_amt,0) ,
-- bug#8702609, end
p_pla_balance => NULL ,
p_service_type_code => p_service_type_code
);
SELECT
a.organization_id ,
a.tax_type ,
nvl(sum(debit_amount),0) debit_amt ,
nvl(sum(credit_amount),0) credit_amt
FROM
/*Bug 5879769 bduvarag start*/
jai_rgm_trx_records a
WHERE trunc(transaction_date) <= p_balance_date --date condition removed for bug 9445836
AND a.settlement_id IS NULL
AND a.source_trx_type <> 'Invoice Payment'
AND a.organization_id = p_org_id
AND a.organization_type = p_org_type/*5694855*/
GROUP BY
a.organization_id,
a.tax_type
ORDER BY
a.tax_type desc
)/*Bug 5879769 bduvarag end*/
LOOP
ln_settled_debit_balance :=0;
insert_records_into_temp(
p_request_id => p_request_id ,
p_regime_id => p_regime_id ,
p_party_type => p_org_type ,/*Bug 5879769 bduvarag*/
p_party_id => delta_rec.organization_id ,
p_location_id => null ,
p_bal_date => p_balance_date ,
p_tax_type => delta_rec.tax_type ,
-- modified for bug#8702609, start
p_debit_amt => nvl(delta_rec.debit_amt,0) ,
p_credit_amt => nvl(delta_rec.credit_amt,0) ,
-- bug#8702609, end
/* Bug 4568078. Added by Lakshmi Gopalsami */
p_pla_balance => NULL
);
SELECT REGIME_CODE
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_ID = P_REGIME_ID;
SELECT SUM(credit_balance) credit_balance,
SUM(debit_balance) debit_balance,
jrs.settlement_id,
jrs.tax_type,
jrs.location_id
FROM jai_rgm_stl_balances jrs,
(SELECT MAX(jbal.settlement_id) settlement_id,
tax_type
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 = P_TAN_NO
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> p_settlement_id
GROUP BY jbal.tax_type)
sv
WHERE jrs.settlement_id = sv.settlement_id
AND jrs.tax_type = sv.tax_type
GROUP BY jrs.settlement_id,
jrs.tax_type,
jrs.location_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 = P_TAN_NO
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> p_settlement_id;
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 = P_TAN_NO
--AND source_document_type = 'Invoice Payment' --mmurtuza need to discuss
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 = P_TAN_NO
AND jbal.party_type = cp_organization_type
AND jbal.party_id = cp_organization_id
AND jbal.location_id = cp_location_id
AND jbal.settlement_id <> nvl(p_settlement_id, -999))
;
SELECT *
FROM jai_rgm_settlements
WHERE settlement_id = cp_stl_id;
SELECT DISTINCT organization_type
FROM jai_rgm_org_regns_v
WHERE regime_code = 'TCS'
AND organization_id = cp_organization_id;
SELECT SUM(DECODE(SIGN(JRT.TAX_AMT),-1,-1 * JRT.TAX_AMT,1,0)) CREDIT_AMOUNT,
SUM(DECODE(SIGN(JRT.TAX_AMT),1,JRT.TAX_AMT,-1,0)) DEBIT_AMOUNT,
DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE) TAX_TYPE,
JRR.ORGANIZATION_ID,
JRR.LOCATION_ID, --added by mmurtuza for bug 12641455
JRR.REGIME_ID
FROM JAI_RGM_REFS_ALL JRR,
JAI_RGM_TAXES JRT,
--JAI_RGM_ORG_REGNS_V JOR --commented for bug# 9005474
JAI_RGM_REGISTRATIONS JOR --added for bug#9005474
WHERE JRR.TRX_REF_ID = JRT.TRX_REF_ID
-- AND JRR.ORGANIZATION_ID = JOR.ORGANIZATION_ID commented for bug# 9005474
AND JOR.REGIME_ID = JRR.REGIME_ID
AND JOR.REGISTRATION_TYPE = 'TAX_TYPES'
AND JRT.TAX_TYPE = JOR.ATTRIBUTE_CODE
AND JRR.REGIME_ID = P_REGIME_ID
AND JRR.ORG_TAN_NO = P_TAN_NO
AND JRR.ORGANIZATION_ID=NVL(P_ORG_ID,JRR.ORGANIZATION_ID)--added for bug 9005474
AND JRR.ITEM_CLASSIFICATION = P_ITEM_CLASSIFICATION
AND JRR.SETTLEMENT_ID IS NULL
AND TRUNC(JRR.SOURCE_DOCUMENT_DATE) <= P_BALANCE_DATE
GROUP BY DECODE(JRT.TAX_TYPE,'TCS_SURCHARGE_CESS','TCS_CESS',JRT.TAX_TYPE),
JRR.ORGANIZATION_ID,JRR.REGIME_ID, JRR.LOCATION_ID --added location id by mmurtuza for bug 12641455
) LOOP
/*Start addtions by mmurtuza for bug 12641455*/
ln_settled_debit_balance := 0;
INSERT_RECORDS_INTO_TEMP(
P_REQUEST_ID => P_REQUEST_ID ,
P_REGIME_ID => P_REGIME_ID ,
P_PARTY_TYPE => 'IO' ,
P_PARTY_ID => DELTA_REC.ORGANIZATION_ID ,
P_LOCATION_ID => DELTA_REC.LOCATION_ID , --initially null was inserted. Added JRR.LOCATION_ID by mmurtuza for bug 12641455
P_BAL_DATE => P_BALANCE_DATE ,
P_TAX_TYPE => DELTA_REC.TAX_TYPE ,
P_DEBIT_AMT => nvl(ln_settled_debit_balance,0) + delta_rec.DEBIT_AMOUNT , --changed by mmurtuza for bug 12641455
P_CREDIT_AMT => nvl(ln_settled_credit_balance,0) + delta_rec.CREDIT_AMOUNT , --changed by mmurtuza for bug 12641455
P_PLA_BALANCE => NULL
);
IS SELECT REGIME_CODE
FROM JAI_RGM_DEFINITIONS
WHERE REGIME_ID = CP_REGIME_ID;
SELECT organization_id party_id ,
'OU' party_type ,
set_of_books_id
FROM
hr_operating_units
)
LOOP
IF jai_cmn_utils_pkg.check_jai_exists(P_CALLING_OBJECT => 'JAI_TAX_DISTRIB' ,
P_SET_OF_BOOKS_ID => ou_rec.set_of_books_id
) = TRUE
THEN*/
/*Bug 5879769 bduvarag end*/
calculate_balances_for_ou(p_regime_id => p_regime_id ,
p_balance_date => p_balance_date ,
p_request_id => p_request_id ,
p_org_id => p_org_id ,/*Bug 5879769 bduvarag*/
p_org_type => 'OU' ,/*Bug 5879769 bduvarag*/
p_regn_id => NULL ,
p_regn_no => NULL ,
p_settlement_id => NULL ,
p_called_from => 'DISTRIBUTE_OU' ,/*Bug 5879769 bduvarag*/
p_service_type_code => p_service_type_code /*Bug 5879769 bduvarag*/
);
( select distinct
organization_id, location_id
from JAI_RGM_ORG_REGNS_V
where organization_type = 'IO'
and regime_code = 'SERVICE'
)
LOOP
calculate_balances_for_ou(p_regime_id => p_regime_id ,
p_balance_date => p_balance_date ,
p_request_id => p_request_id ,
p_org_id => io_rec.organization_id ,
p_org_type => 'IO' ,
p_regn_id => NULL ,
p_regn_no => NULL ,
p_settlement_id => NULL ,
p_called_from => 'DISTRIBUTE_IO' ,
p_location_id => io_rec.location_id ,
p_service_type_code => p_service_type_code
);
PROCEDURE insert_records_into_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,
p_from_service_type VARCHAR2 default null, -- bduvarag for Bug 5694855
p_to_service_type VARCHAR2 default null -- bduvarag for Bug 5694855
)
is
ln_repository_id NUMBER;
SELECT modvat_pla_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_inv_orgn_id
AND location_id = cp_locn_id;
SELECT modvat_rm_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_inv_orgn_id
AND location_id = cp_locn_id;
SELECT modvat_cg_account_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_inv_orgn_id
AND location_id = cp_locn_id;
SELECT pref_rg23a, pref_rg23c
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT NVL(rg23a_balance,0) rg23a_balance ,NVL(rg23c_balance,0) rg23c_balance
FROM JAI_CMN_RG_BALANCES
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
SELECT transfer_Destination_id
FROM JAI_RGM_DIS_DES_TAXES
WHERE transfer_destination_line_id = p_reference_id;
SELECT transfer_amount
FROM JAI_RGM_DIS_DES_TAXES
WHERE transfer_destination_id = cp_transfer_dest_id
-- and tax_type in (lv_excise_cess,jai_constants.tax_type_service_edu_cess, jai_constants.tax_type_sh_service_edu_cess); --rchandan for bug#4428980
SELECT transfer_amount
FROM jai_rgm_dis_des_taxes
WHERE transfer_destination_id = cp_transfer_dest_id
and tax_type in ('SERVICE_SH_EDU_CESS','EXCISE_SH_EDU_CESS');
select transfer_number
from jai_rgm_dis_src_hdrs
where party_type = cp_party_type
and party_id = cp_party_id
and transfer_id = cp_transfer_id;
SELECT jai_rgm_dis_src_hdrs_s.nextval ,
JAI_RGM_DIS_SRC_TAXES_S.nextval ,
jai_rgm_dis_des_hdrs_s.nextval ,
JAI_RGM_DIS_DES_TAXES_S.nextval ,
JAI_RGM_DIS_TRF_NUMS_S.nextval
INTO ln_transfer_id ,
ln_transfer_source_id ,
ln_transfer_dest_id ,
ln_transfer_dest_line_id ,
lv_transfer_num
FROM dual;
INSERT INTO jai_rgm_dis_src_hdrs
(
TRANSFER_ID ,
PARTY_ID ,
PARTY_TYPE ,
LOCATION_ID ,
TRANSFER_NUMBER ,
TRANSACTION_DATE ,
SETTLEMENT_ID ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
ln_transfer_id ,
p_from_party_id ,
p_from_party_type ,
p_from_locn_id ,
lv_transfer_num ,
p_trx_date ,
p_settlement_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO JAI_RGM_DIS_SRC_TAXES
(
TRANSFER_ID ,
TRANSFER_SOURCE_ID ,
TAX_TYPE ,
DEBIT_BALANCE ,
CREDIT_BALANCE ,
TRANSFER_AMOUNT ,
PARENT_TAX_TYPE ,
PERCENT_OF_PARENT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
ln_transfer_id ,
ln_transfer_source_id ,
p_from_tax_type ,
NULL ,
NULL ,
p_to_trx_amount ,
NULL ,
NULL ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO jai_rgm_dis_des_hdrs
(
TRANSFER_ID ,
TRANSFER_SOURCE_ID ,
TRANSFER_DESTINATION_ID ,
DESTINATION_PARTY_TYPE ,
DESTINATION_PARTY_ID ,
LOCATION_ID ,
AMOUNT_TO_TRANSFER ,
TRANSFER_NUMBER ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
ln_transfer_id ,
ln_transfer_source_id ,
ln_transfer_Dest_id ,
p_to_party_type ,
p_to_party_id ,
p_to_locn_id ,
p_to_trx_amount ,
lv_transfer_num ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
INSERT INTO JAI_RGM_DIS_DES_TAXES
(
TRANSFER_SOURCE_ID ,
TRANSFER_DESTINATION_ID ,
TRANSFER_DESTINATION_LINE_ID ,
TAX_TYPE ,
DEBIT_BALANCE ,
CREDIT_BALANCE ,
TRANSFER_AMOUNT ,
CREATION_DATE ,
CREATED_BY ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_LOGIN
)
VALUES
(
ln_transfer_source_id ,
ln_transfer_Dest_id ,
ln_transfer_dest_line_id,
p_to_tax_type ,
NULL ,
NULL ,
p_to_trx_amount ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
);
jai_cmn_rgm_recording_pkg.insert_repository_entry
(
P_REPOSITORY_ID => ln_repository_id ,
P_REGIME_ID => p_regime_id ,
P_TAX_TYPE => p_from_tax_type ,
P_ORGANIZATION_TYPE => p_from_party_type ,
P_ORGANIZATION_ID => p_from_party_id ,
P_LOCATION_ID => p_from_locn_id ,
P_SOURCE => lv_source ,
P_SOURCE_TRX_TYPE => p_source_trx_type ,
P_SOURCE_TABLE_NAME => p_source_table_name,
P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
P_TRANSACTION_DATE => p_trx_date ,
P_ACCOUNT_NAME => NULL ,
P_CHARGE_ACCOUNT_ID => NULL ,
P_BALANCING_ACCOUNT_ID => NULL ,
P_AMOUNT => p_to_trx_amount ,
P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
P_ASSESSABLE_VALUE => NULL ,
P_TAX_RATE => NULL ,
P_REFERENCE_ID => ln_transfer_id ,
P_BATCH_ID => NULL ,
P_CALLED_FROM => p_called_from ,
p_process_flag => p_process_flag ,
p_process_message => p_process_message ,
P_SETTLEMENT_ID => p_settlement_id ,
p_accounting_date => p_accounting_date ,
P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
P_BALANCING_ORGN_TYPE => p_to_party_type ,
P_BALANCING_ORGN_ID => p_to_party_id ,
P_BALANCING_LOCATION_ID => p_to_locn_id ,
P_BALANCING_TAX_TYPE => p_to_tax_type ,
P_BALANCING_ACCNT_NAME =>nvl(lv_rep_register_type ,v_register_type) ,--added nvl(lv_rep_register_type for bug#6773684
P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
p_service_type_code => p_from_service_type,
p_distribution_type => lv_distribution_type /*bug 7525691*/
);
/* jai_cmn_rg_23ac_ii_pkg.insert_row(
P_REGISTER_ID => ln_register_id ,
P_INVENTORY_ITEM_ID => -999 ,
P_ORGANIZATION_ID => p_from_party_id ,
P_RECEIPT_ID => p_reference_id ,
P_RECEIPT_DATE => p_trx_date ,
P_CR_BASIC_ED => NULL,
P_CR_ADDITIONAL_ED => NULL,
P_CR_OTHER_ED => NULL,
P_DR_BASIC_ED => p_to_trx_amount ,
P_DR_ADDITIONAL_ED => NULL,
P_DR_OTHER_ED => NULL,
P_EXCISE_INVOICE_NO => NULL,
P_EXCISE_INVOICE_DATE => NULL,
P_REGISTER_TYPE => jai_constants.REGISTER_TYPE_A ,
P_REMARKS => 'DISTRIBUTION - OUT',
P_VENDOR_ID => NULL,
P_VENDOR_SITE_ID => NULL ,
P_CUSTOMER_ID => NULL,
P_CUSTOMER_SITE_ID => NULL,
P_LOCATION_ID => p_from_locn_id,
P_TRANSACTION_DATE => p_trx_date ,
P_CHARGE_ACCOUNT_ID => NULL ,
P_REGISTER_ID_PART_I => NULL ,
P_REFERENCE_NUM => p_source_doc_id,
P_ROUNDING_ID => NULL ,
P_OTHER_TAX_CREDIT => NULL,
P_OTHER_TAX_DEBIT => ln_cess_amount,
P_TRANSACTION_TYPE => 'DISTRIBUTION' ,
P_TRANSACTION_SOURCE => 'DISTRIBUTION' ,
P_CALLED_FROM => p_called_from ,
P_SIMULATE_FLAG => 'N' ,
p_process_status => p_process_flag,
P_PROCESS_MESSAGE => p_process_message
);*/
/**this check has been added to fetch the v_register_type which will be used to insert into repository
with p from tax type as excise*/
IF v_register_type = 'RG' THEN
OPEN pref_cur(p_from_party_id, p_from_locn_id);
jai_cmn_rgm_recording_pkg.insert_repository_entry
(
P_REPOSITORY_ID => ln_repository_id ,
P_REGIME_ID => p_regime_id ,
P_TAX_TYPE => p_to_tax_type ,
P_ORGANIZATION_TYPE => p_to_party_type ,
P_ORGANIZATION_ID => p_to_party_id ,
P_LOCATION_ID => p_to_locn_id ,
P_SOURCE => lv_source ,
P_SOURCE_TRX_TYPE => p_source_trx_type ,
P_SOURCE_TABLE_NAME => p_source_table_name,
P_SOURCE_DOCUMENT_ID => p_source_doc_id ,
P_TRANSACTION_DATE => p_trx_date ,
P_ACCOUNT_NAME => NULL ,
P_CHARGE_ACCOUNT_ID => NULL ,
P_BALANCING_ACCOUNT_ID => NULL ,
P_AMOUNT => p_to_trx_amount ,
P_DISCOUNTED_AMOUNT => ln_discounted_amt ,
P_ASSESSABLE_VALUE => NULL ,
P_TAX_RATE => NULL ,
P_REFERENCE_ID => ln_transfer_id ,
P_BATCH_ID => NULL ,
P_CALLED_FROM => p_called_from ,
p_process_flag => p_process_flag ,
p_process_message => p_process_message ,
P_SETTLEMENT_ID => p_settlement_id ,
p_accounting_date => p_trx_date ,
P_ACCNTG_REQUIRED_FLAG => lv_acct_req_flag ,
P_BALANCING_ORGN_TYPE => p_from_party_type ,
P_BALANCING_ORGN_ID => p_from_party_id ,
P_BALANCING_LOCATION_ID => p_from_locn_id ,
P_BALANCING_TAX_TYPE => p_from_tax_type ,
P_BALANCING_ACCNT_NAME =>lv_rep_register_type ,
--added the nvl(lv_rep_register_type for bug#6773684
P_CURRENCY_CODE => jai_constants.func_curr , -- File.Sql.35 by Brathod
p_service_type_code => p_to_service_type ,
p_distribution_type => lv_distribution_type /*bug 7525691*/
);
/* jai_cmn_rg_pla_trxs_pkg.insert_row(
p_register_id => ln_register_id,
p_tr6_challan_no => NULL,
p_tr6_challan_date => NULL,
p_cr_basic_ed => p_to_trx_amount,
p_cr_additional_ed => NULL,
p_cr_other_ed => NULL,
p_ref_document_id => p_reference_id,
p_ref_document_date => p_trx_date,
p_dr_invoice_id => NULL,
p_dr_invoice_date => NULL,
p_dr_basic_ed => NULL,
p_dr_additional_ed => NULL,
p_dr_other_ed => NULL,
p_organization_id => p_to_party_id,
p_location_id => p_to_locn_id,
p_bank_branch_id => NULL,
p_entry_date => NULL,
p_inventory_item_id => -999,
p_vendor_cust_flag => 'O',
p_vendor_id => p_from_party_id,
p_vendor_site_id => NULL,
p_excise_invoice_no => NULL,
p_remarks => 'DISTRIBUTION',
p_transaction_date => p_trx_date,
p_charge_account_id => ln_charge_account_id,
p_other_tax_credit => ln_cess_amount,
p_other_tax_debit => NULL,
p_transaction_type => 'DISTRIBUTION',
p_transaction_source => 'DISTRIBUTION',
p_called_from => p_called_from,
p_simulate_flag => 'N',
p_process_status => p_process_flag,
p_process_message => p_process_message
);*/ --Added the call to create_io_register entry by kunkumar
p_process_message := 'Error in procedure - insert_records_into_register ' || substr(sqlerrm,1,1500);
END insert_records_into_register;
PROCEDURE delete_records(p_request_id number) IS
BEGIN
DELETE FROM JAI_RGM_BALANCE_T
WHERE request_id = p_request_id;
END delete_records;
jai_cmn_rg_23ac_ii_pkg.insert_row(
p_register_id => ln_register_id ,
p_inventory_item_id => p_inventory_item_id ,
p_organization_id => p_organization_id ,
p_receipt_id => p_receipt_id ,
p_receipt_date => p_receipt_date ,
p_cr_basic_ed => p_cr_basic_ed ,
p_cr_additional_ed => p_cr_additional_ed ,
p_cr_other_ed => p_cr_other_ed ,
p_dr_basic_ed => p_dr_basic_ed ,
p_dr_additional_ed => p_dr_additional_ed ,
p_dr_other_ed => p_dr_other_ed ,
p_excise_invoice_no => p_excise_invoice_no ,
p_excise_invoice_date => p_excise_invoice_date ,
p_register_type => lv_register_type ,--jai_constants.register_type_a , bug 8873924
p_remarks => p_remarks ,
p_vendor_id => p_vendor_id ,
p_vendor_site_id => p_vendor_site_id ,
p_customer_id => p_customer_id ,
p_customer_site_id => p_customer_site_id ,
p_location_id => p_location_id ,
p_transaction_date => p_transaction_date ,
p_charge_account_id => p_charge_account_id ,
p_register_id_part_i => p_register_id_part_i ,
p_reference_num => p_reference_num ,
p_rounding_id => p_rounding_id ,
p_other_tax_credit => p_other_tax_credit ,
p_other_tax_debit => p_other_tax_debit ,
p_transaction_type => p_transaction_type ,
p_transaction_source => p_transaction_source ,
p_called_from => p_called_from ,
p_simulate_flag => p_simulate_flag ,
p_process_status => p_process_flag ,
p_process_message => p_process_message
);
jai_cmn_rg_pla_trxs_pkg.insert_row(
p_register_id => ln_register_id ,
p_tr6_challan_no => NULL ,
p_tr6_challan_date => NULL ,
p_cr_basic_ed => p_cr_basic_ed ,
p_cr_additional_ed => p_cr_additional_ed ,
p_cr_other_ed => p_cr_other_ed ,
p_ref_document_id => p_ref_document_id ,
p_ref_document_date => p_ref_document_date ,
p_dr_invoice_id => p_dr_invoice_id ,
p_dr_invoice_date => p_dr_invoice_date ,
p_dr_basic_ed => p_dr_basic_ed ,
p_dr_additional_ed => p_dr_additional_ed ,
p_dr_other_ed => p_dr_other_ed ,
p_organization_id => p_organization_id ,
p_location_id => p_location_id ,
p_bank_branch_id => p_bank_branch_id ,
p_entry_date => p_entry_date ,
p_inventory_item_id => p_inventory_item_id ,
p_vendor_cust_flag => p_vendor_cust_flag ,
p_vendor_id => p_vendor_id ,
p_vendor_site_id => p_vendor_site_id ,
p_excise_invoice_no => p_excise_invoice_no ,
p_remarks => p_remarks ,
p_transaction_date => p_transaction_date ,
p_charge_account_id => p_charge_account_id ,
p_other_tax_credit => p_other_tax_credit ,
p_other_tax_debit => p_other_tax_debit ,
p_transaction_type => p_transaction_type ,
p_transaction_source => p_transaction_source ,
p_called_from => p_called_from ,
p_simulate_flag => p_simulate_flag ,
p_process_status => p_process_flag ,
p_process_message => p_process_message
);
/* Update the cess amount in the ja_in_rg23_part_ii table*/
IF nvl(p_credit_amt,0) <> 0 OR
nvl(p_debit_amt,0) <> 0
THEN
jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
p_source_name => lv_source_register ,
p_source_id => ln_register_id ,
p_tax_type => 'EXCISE_EDUCATION_CESS' ,
debit_amt => p_debit_amt ,
credit_amt => p_credit_amt ,
p_process_flag => p_process_flag ,
p_process_msg => p_process_message
);
jai_cmn_rg_others_pkg.insert_row( p_source_type => ln_source_type ,
p_source_name => lv_source_register ,
p_source_id => ln_register_id ,
p_tax_type => 'EXCISE_SH_EDU_CESS' ,
debit_amt => p_sh_cess_debit_amt ,
credit_amt => p_sh_cess_credit_amt ,
p_process_flag => p_process_flag ,
p_process_msg => p_process_message
);
SELECT
attribute_value
FROM
jai_rgm_org_regns_v
WHERE
--organization_type = 'OU' /*commented by vkaranam for bug#6773684*/
organization_id = p_party_id
AND regime_code = 'SERVICE'
AND registration_type = 'OTHERS'
AND attribute_code = 'DIST_PLA_RG';