[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_tmps debit_amt and jai_rgm_balance_tmps pla_balance as 0
else (+ve value for PLA balance) let the value of o be populated into jai_rgm_balance_tmps 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.
-- #
-- # 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*/
)
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*/
)
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*/
);
END insert_records_into_temp;
||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) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,organization_id,location_id),g_start_date) AND p_balance_date
AND settlement_id IS NULL; /* added by ssawant for bug 5662296*/
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 trunc(transaction_date) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,organization_id,location_id),g_start_date) AND p_balance_date
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 service_type_code = cp_service_type_code /*Added by nprashar for bug # 6359082*/
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) /*This clause is used to exclude the current settlement*/
AND jbal.service_type_code = cp_service_type_code); /*Added by nprashar for bug # 6359082*/
SELECT credit_amount
FROM jai_rgm_trx_records
WHERE source_trx_type = 'Invoice Payment'
AND service_type_code = cp_service_type_code
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 = cp_service_type_code
);
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
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 = 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.service_type_code = NVL(cp_service_type_code,jbal.service_type_code) /*Added by nprashar for bug #6359082*/
AND jbal.settlement_id <> p_settlement_id/*This clause is used to exclude the current settlement*/
)
GROUP BY tax_type;
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
FROM jai_rgm_stl_balances
WHERE 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 <> p_settlement_id/*This clause is used to exclude the current settlement*/
)
GROUP BY tax_type;
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) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(cp_regime_id,cp_organization_id,cp_location_id),g_start_date) and p_balance_date
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 = p_service_type_code
GROUP BY
organization_id,
location_id ,
tax_type
ORDER BY
tax_type;
SELECT a.organization_id ,
a.location_id ,/*Bug 5879769 bduvarag*/
a.service_type_code ,/*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) BETWEEN nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,a.organization_id,a.location_id),g_start_date)
/*rchandan for bug#5003538*//*rchandan for bug#5642053. Removed +1 from last settlement date*/
AND p_balance_date /*5694855.location_id is also passed to get_last_settlement_date*/
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'
GROUP BY a.organization_id ,
a.location_id ,/*5694855*/
a.tax_type ,
a.service_type_code /*5694855*/
)/*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 => delta_rec.service_type_code/*Bug 5879769 bduvarag*/
);
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) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(b.regime_id,a.organization_id,a.location_id) ,g_start_date) and p_balance_date /*+ 1 removed by ssawant for bug 5662296*/
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
AND a.tax_type <> 'VAT REVERSAL' --Added by JMEENA for bug#7445742
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 ,
p_debit_amt => nvl(ln_settled_debit_balance,0) + nvl(r_delta_rec.debit_amt,0) ,
p_credit_amt => nvl(ln_settled_credit_balance,0) + nvl(r_delta_rec.credit_amt,0) ,
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) between nvl(jai_cmn_rgm_settlement_pkg.get_last_settlement_date(p_regime_id,a.organization_id),g_start_date) and p_balance_date
/*rchandan for bug#5003538*//*rchandan for bug#5642053. Removed +1 from last settlement date*/
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 ,
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 ,
/* 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(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.REGIME_ID
FROM JAI_RGM_REFS_ALL JRR,
JAI_RGM_TAXES JRT,
JAI_RGM_ORG_REGNS_V JOR
WHERE JRR.TRX_REF_ID = JRT.TRX_REF_ID
AND JRR.ORGANIZATION_ID = JOR.ORGANIZATION_ID
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.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
) LOOP
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 => NULL ,
P_BAL_DATE => P_BALANCE_DATE ,
P_TAX_TYPE => DELTA_REC.TAX_TYPE ,
P_DEBIT_AMT => DELTA_REC.DEBIT_AMOUNT ,
P_CREDIT_AMT => DELTA_REC.CREDIT_AMOUNT ,
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 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 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
/*
, /*Bug 5879769 bduvarag
p_balancing_entry => lv_balancing_entry --added by nprashar for bug # 7525691
*//*commented for bug#6773684*/
);
/* 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
);*/
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 => nvl(lv_rep_register_type,v_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
/*, /*Bug 5879769 bduvarag
p_balancing_entry => lv_balancing_entry --added by nprashar for bug # 7525691
*//*commented for bug#6773684*/
);
/* 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 => jai_constants.register_type_a ,
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';