[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update for get TCS financial year from TCS regime setup.
---------------------------------------------------------------------------- */
/*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
/** Package level variables used in debug package*/
lv_object_name jai_cmn_debug_contexts.log_context%type default 'TCS.JAI_RGM_THHOLD_PROC_PKG';
SELECT jrr.ATTRIBUTE_VALUE
FROM JAI_RGM_DEFINITIONS jrd,
JAI_RGM_REGISTRATIONS jrr
WHERE jrd.REGIME_ID = jrr.REGIME_ID
AND jrr.ATTRIBUTE_CODE = cp_attr_code
AND jrd.REGIME_CODE = cp_rgm_code;
SELECT a.PERIOD_YEAR
FROM GL_PERIODS a
, gl_period_sets b
WHERE a.PERIOD_SET_NAME = b.period_set_name
AND b.period_set_id = cp_calendar_id
AND cp_accounting_date BETWEEN a.START_DATE AND a.END_DATE;
select hdr.regime_id
,hdr.threshold_slab_id
,dtls.threshold_dtl_id
,dtls.threshold_base_amt
,dtls.item_classification
,nvl(dtls.manual_surcharge_amt,0) manual_surcharge_amt
,nvl(dtls.system_surcharge_amt,0) system_surcharge_amt
,nvl(dtls.system_surcharge_cess_amt,0) system_surcharge_cess_amt
,nvl(dtls.system_surcharge_sh_cess_amt,0) system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
from jai_rgm_threshold_dtls dtls
,jai_rgm_thresholds hdr
where hdr.threshold_id = p_threshold_id
and dtls.threshold_id = hdr.threshold_id ;
select currency_code
from gl_sets_of_books gsb
, hr_operating_units hou
where gsb.set_of_books_id = hou.set_of_books_id
and hou.organization_id = cp_org_id;
select *
from jai_rgm_refs_all
where ( (cp_transaction_id is not null and transaction_id = cp_transaction_id)
or (cp_trx_ref_id is not null and trx_ref_id = cp_trx_ref_id)
);
select tax_id
,tax_type
,tax_rate
from jai_rgm_taxes
where trx_ref_id = ( select max(trx_ref_id)
from jai_rgm_refs_all
where source_document_id = jai_constants.tcs_surcharge_id
and org_tan_no = cp_org_tan_no
and fin_year = cp_fin_year
and party_id = cp_party_id
and item_classification = cp_item_classification
and party_type = jai_constants.party_type_customer
);
SELECT
jai_rgm_refs_all_s2.nextval
FROM
dual;
select tax.tax_rate
, tax.rounding_factor
, tax.tax_id
, cat.line_no
from JAI_CMN_TAXES_ALL tax
,JAI_CMN_TAX_CTG_LINES cat
where cat.tax_id = tax.tax_id
and cat.tax_category_id = p_threshold_tax_cat_id
and tax.tax_type = cp_tax_type
and cat.precedence_1 = nvl(cp_line_no, cat.precedence_1); -- This will check that for CESS precedence should be of surcharge
goto update_and_return;
|| Insert repository reference for the document to be generated
*/
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_REFERENCES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
jai_ar_tcs_rep_pkg.insert_repository_references
( p_regime_id => r_thhold_info.regime_id
, p_transaction_id => ln_transaction_id
, p_source_ref_document_id => r_ref_dtls.source_ref_document_id
, p_source_ref_document_type => r_ref_dtls.source_ref_document_type
, p_parent_transaction_id => r_ref_dtls.parent_transaction_id
, p_org_tan_no => r_ref_dtls.org_tan_no
, p_document_id => jai_constants.tcs_surcharge_id
, p_document_type => jai_constants.tcs_event_surcharge
, p_document_line_id => jai_constants.tcs_surcharge_id
, p_document_date => r_ref_dtls.source_document_date
, p_table_name => jai_constants.jai_rgm_thresholds
, p_line_amount => (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt) --Bgowrava for forward porting bug#5989740
, p_document_amount => (ln_surcharge_doc_amt + ln_surcharge_doc_cess_amt + ln_surcharge_doc_sh_cess_amt) --Bgowrava for forward porting bug#5989740
, p_org_id => r_ref_dtls.org_id
, p_organization_id => r_ref_dtls.organization_id
, p_party_id => r_ref_dtls.party_id
, p_party_site_id => r_ref_dtls.party_site_id
, p_item_classification => r_thhold_info.item_classification
, p_trx_ref_id => ln_trx_ref_id
, p_process_flag => p_process_flag
, p_process_message => p_process_message
);
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_REFERENCES',jai_cmn_debug_contexts_pkg.summary);
|| Insert repository taxes
*/
/*
Insert surcharge type of tax
*/
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Surcharge Tax: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
jai_ar_tcs_rep_pkg.insert_repository_taxes
(
p_trx_ref_id => ln_trx_ref_id
, p_tax_id => ln_surcharge_tax_id
, p_tax_type => jai_constants.tax_type_tcs_surcharge
, p_tax_rate => ln_surcharge_tax_rate
, p_tax_amount => ln_surcharge_doc_amt
, p_func_tax_amount => ln_surcharge_doc_amt /* Functional currency is same as trx currency */
, p_currency_code => ln_currency_code
, p_process_flag => p_process_flag
, p_process_message => p_process_message
);
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Tax: JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);*/ --commented by bgowrava for bug#5631784
/* Insert surcharge cess type of tax */
jai_ar_tcs_rep_pkg.insert_repository_taxes
(
p_trx_ref_id => ln_trx_ref_id
, p_tax_id => ln_surcharge_cess_tax_id
, p_tax_type => jai_constants.tax_type_tcs_cess
, p_tax_rate => ln_surcharge_cess_tax_rate
, p_tax_amount => ln_surcharge_doc_cess_amt
, p_func_tax_amount => ln_surcharge_doc_cess_amt /* Functional currency is same as trx currency */
, p_currency_code => ln_currency_code
, p_process_flag => p_process_flag
, p_process_message => p_process_message
);
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before Secondary and higher Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
/* Insert surcharge cess type of tax */
jai_ar_tcs_rep_pkg.insert_repository_taxes
(
p_trx_ref_id => ln_trx_ref_id
, p_tax_id => ln_surcharge_cess_tax_id
, p_tax_type => jai_constants.tax_type_sh_tcs_edu_cess
, p_tax_rate => ln_surcharge_cess_tax_rate
, p_tax_amount => ln_surcharge_doc_sh_cess_amt
, p_func_tax_amount => ln_surcharge_doc_sh_cess_amt /* Functional currency is same as trx currency */
, p_currency_code => ln_currency_code
, p_process_flag => p_process_flag
, p_process_message => p_process_message
);
jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'After Surcharge Cess : JAI_AR_TCS_REP_PKG.INSERT_REPOSITORY_TAXES',jai_cmn_debug_contexts_pkg.summary);
|| Get Repository Reference for consolidated document record inserted above
*/
open c_get_ref_dtls (cp_trx_ref_id => ln_trx_ref_id) ;
<>
/** Update System surcharge/cess amouts */
update jai_rgm_threshold_dtls
set system_surcharge_amt = system_surcharge_amt + ln_surcharge_doc_amt
, system_surcharge_cess_amt = system_surcharge_cess_amt + ln_surcharge_doc_cess_amt
, system_surcharge_sh_cess_amt = system_surcharge_sh_cess_amt + ln_surcharge_doc_sh_cess_amt --Bgowrava for forward porting bug#5989740
, last_update_date = sysdate
, last_updated_by = ln_user_id
, last_update_login = ln_login_id
where threshold_dtl_id = r_thhold_info.threshold_dtl_id;
select
attribute_value org_tan_no
from
JAI_RGM_ORG_REGNS_V rgm_attr_v
where
rgm_attr_v.regime_id = cp_regime_id
and rgm_attr_v.attribute_code = cp_attribute_code
and rgm_attr_v.attribute_type_code = cp_attribute_type_code
and rgm_attr_v.organization_id = cp_organization_id;
select
thslbs.threshold_slab_id
from
jai_ap_tds_thhold_slabs thslbs
,jai_ap_tds_thhold_types thtyps
,jai_ap_tds_thhold_hdrs thhdrs
,jai_rgm_thresholds rgmths
where
thslbs.threshold_type_id = thtyps.threshold_type_id
and thtyps.threshold_hdr_id = thhdrs.threshold_hdr_id
and thhdrs.regime_id = rgmths.regime_id
and thtyps.threshold_type = jai_constants.thhold_typ_cumulative
and rgmths.fin_year = cp_fin_year
and rgmths.party_id = cp_party_id
and rgmths.org_tan_no = cp_org_tan_no
and rgmths.party_type = cp_party_type
and thhdrs.customer_type_lookup_code = cp_customer_type_lkup_code
and trunc(p_source_trx_date) between thtyps.from_date
and nvl(thtyps.to_date, trunc(p_source_trx_date))
and nvl(rgmths.total_threshold_amt,0) between thslbs.from_amount
and nvl(thslbs.to_amount,nvl(rgmths.total_threshold_amt,0));
select threshold_slab_id
from jai_rgm_thresholds
where fin_year = cp_fin_year
and org_tan_no = cp_org_tan_no
and party_type = cp_party_type
and party_id = cp_party_id
and regime_id = p_regime_id;
select tcs_customer_type
from JAI_CMN_CUS_ADDRESSES
where customer_id = p_party_id
AND tcs_customer_type IS NOT NULL; --added the AND condition for bug#6156619
/** This is an internal call. Hence fetch the applicable slab id for amount updated in the jai_rgm_thresholds */
/**
Assumption: To fetch slab from the setup transaction date is mandatory
*/
if p_source_trx_date is null then
p_process_flag := jai_constants.expected_error;
select
thtaxes.tax_category_id
from
jai_ap_tds_thhold_taxes thtaxes
where
thtaxes.threshold_slab_id = p_threshold_slab_id
and operating_unit_id = p_org_id;
select 1
from JAI_OM_WSH_LINE_TAXES line, JAI_CMN_TAX_CTG_LINES cat
where line.tax_id = cat.tax_id
and line.delivery_detail_id = p_source_trx_line_id
and cat.tax_category_id = p_threshold_tax_cat_id;
select 1
from JAI_AR_TRX_TAX_LINES line, JAI_CMN_TAX_CTG_LINES cat
where line.tax_id = cat.tax_id
and line.link_to_cust_trx_line_id = p_source_trx_line_id
and cat.tax_category_id = p_threshold_tax_cat_id;
insert into JAI_OM_WSH_LINE_TAXES
( picking_line_id
, tax_line_no
, precedence_1
, precedence_2
, precedence_3
, precedence_4
, precedence_5
, tax_id
, tax_rate
, qty_rate
, uom
, tax_amount
, func_tax_amount
, base_tax_amount
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, delivery_detail_id
, precedence_6
, precedence_7
, precedence_8
, precedence_9
, precedence_10
)
values ( null
, r_taxes.lno
, r_taxes.p_1
, r_taxes.p_2
, r_taxes.p_3
, r_taxes.p_4
, r_taxes.p_5
, r_taxes.tax_id
, r_taxes.tax_rate
, null
, r_taxes.tax_amount
, r_taxes.uom_code
, null
, null
, sysdate
, ln_user_id
, sysdate
, ln_user_id
, ln_login_id
, p_source_trx_line_id
, r_taxes.p_6
, r_taxes.p_7
, r_taxes.p_8
, r_taxes.p_9
, r_taxes.p_10
);
insert into JAI_AR_TRX_TAX_LINES
(
tax_line_no
,customer_trx_line_id
,link_to_cust_trx_line_id
,precedence_1
,precedence_2
,precedence_3
,precedence_4
,precedence_5
,tax_id
,tax_rate
,qty_rate
,uom
,tax_amount
,invoice_class
,func_tax_amount
,base_tax_amount
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,precedence_6
,precedence_7
,precedence_8
,precedence_9
,precedence_10
)
values (
r_taxes.lno --tax_line_no
, ra_customer_trx_lines_s.nextval --customer_trx_line_id
, p_source_trx_line_id --link_to_cust_trx_line_id
, r_taxes.p_1 --precedence_1
, r_taxes.p_2 --precedence_2
, r_taxes.p_3 --precedence_3
, r_taxes.p_4 --precedence_4
, r_taxes.p_5 --precedence_5
, r_taxes.tax_id --tax_id
, r_taxes.tax_rate --tax_rate
, null --qty_rate
, r_taxes.uom_code --uom
, null --tax_amount
, null --invoice_class
, null --func_tax_amount
, null --base_tax_amount
, sysdate --creation_date
, ln_user_id --created_by
, sysdate --last_update_date
, ln_user_id --last_updated_by
, ln_login_id --last_update_login
, r_taxes.p_6 --precedence_6
, r_taxes.p_7 --precedence_7
, r_taxes.p_8 --precedence_8
, r_taxes.p_9 --precedence_9
, r_taxes.p_10 --precedence_10
);
/** Call ja_in_calc_prec_taxes procedure in recalculate taxes mode to recalculate taxes and update the related table */
ln_tax_amount := p_base_tax_amt ;
, p_last_update_date => sysdate
, p_last_updated_by => ln_user_id
, p_last_update_login => ln_login_id
, p_vat_assessable_value => p_vat_assessable_value
, p_thhold_cat_base_tax_typ=> jai_constants.tax_type_tcs
, p_threshold_tax_cat_id => p_threshold_tax_cat_id
, p_source_trx_type => p_source_event
, p_action => jai_constants.recalculate_taxes
-- Commented by Chong for revert GST changes 2012/10/19
--, pn_gst_assessable_value => pn_gst_assessable_value -- Added by Jia for GST Bug#10091373 on 2010/09/10
);
select regime_id
, org_tan_no
, party_id
, party_type
, fin_year
, item_classification
, source_document_type
, sum(nvl(source_document_amt,0)) source_document_amt
, source_document_date
, org_id
from jai_rgm_refs_all trxref
where trxref.transaction_id = p_transaction_id
group by
regime_id
, org_tan_no
, party_id
, party_type
, fin_year
, item_classification
, source_document_type
, source_document_date
, org_id;
select threshold_id
, threshold_slab_id
from jai_rgm_thresholds
where regime_id = cp_regime_id
and org_tan_no = cp_org_tan_no
and party_id = cp_party_id
and party_type = cp_party_type
and fin_year = cp_fin_year;
select threshold_dtl_id
, invoice_amt
, cash_receipt_amt
, application_amt
, unapplication_amt
, reversal_amt
, threshold_base_amt
, manual_surcharge_amt
, system_surcharge_amt
, system_surcharge_cess_amt
, system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
from jai_rgm_threshold_dtls
where threshold_id = cp_threshold_id
and item_classification = cp_item_classification;
select pan_no
from JAI_CMN_CUS_ADDRESSES
where customer_id = cp_customer_id
and confirm_pan = jai_constants.yes;
select sum(rtax.func_tax_amt)
from jai_rgm_taxes rtax
, jai_rgm_refs_all refs
where rtax.trx_ref_id = refs.trx_ref_id
and refs.transaction_id = p_transaction_id
and rtax.tax_type = cp_base_tax_type;
select sum(rtax.func_tax_amt)
from jai_rgm_taxes rtax
, jai_rgm_refs_all refs
where rtax.trx_ref_id = refs.trx_ref_id
and rtax.tax_type = cp_tax_type
and refs.transaction_id = p_transaction_id
and rtax.tax_modified_by = nvl(cp_tax_modified_by,rtax.tax_modified_by);
select sum(rtax.func_tax_amt)
from jai_rgm_taxes rtax
, jai_rgm_refs_all refs
, JAI_CMN_TAX_CTG_LINES srch
, JAI_CMN_TAX_CTG_LINES srchcess
, JAI_CMN_TAXES_ALL tax
where rtax.trx_ref_id = refs.trx_ref_id
and rtax.tax_type = jai_constants.tax_type_tcs_cess
and srch.tax_category_id = cp_thhold_tax_cat_id
and srchcess.tax_category_id = srch.tax_category_id
and srchcess.precedence_1 = srch.line_no
and srchcess.tax_id = rtax.tax_id
and srch.tax_id = tax.tax_id
and tax.tax_type = jai_constants.tax_type_tcs_surcharge
and refs.transaction_id = p_transaction_id;
select sum(rtax.func_tax_amt)
from jai_rgm_taxes rtax
, jai_rgm_refs_all refs
, JAI_CMN_TAX_CTG_LINES srch
, JAI_CMN_TAX_CTG_LINES srchcess
, JAI_CMN_TAXES_ALL tax
where rtax.trx_ref_id = refs.trx_ref_id
and rtax.tax_type = jai_constants.tax_type_sh_tcs_edu_cess
and srch.tax_category_id = cp_thhold_tax_cat_id
and srchcess.tax_category_id = srch.tax_category_id
and srchcess.precedence_1 = srch.line_no
and srchcess.tax_id = rtax.tax_id
and srch.tax_id = tax.tax_id
and tax.tax_type = jai_constants.tax_type_tcs_surcharge
and refs.transaction_id = p_transaction_id;
lr_hdr_record.last_update_date := sysdate ;
lr_hdr_record.last_updated_by := ln_user_id ;
lr_hdr_record.last_update_login := ln_login_id ;
, 'Before insert into jai_rgm_thresholds' || chr(10)
||'lr_hdr_record.threshold_id ='||lr_hdr_record.threshold_id || chr(10)
||'lr_hdr_record.regime_id ='||lr_hdr_record.regime_id || chr(10)
||'lr_hdr_record.org_tan_no ='||lr_hdr_record.org_tan_no || chr(10)
||'lr_hdr_record.party_id ='||lr_hdr_record.party_id || chr(10)
||'lr_hdr_record.party_type ='||lr_hdr_record.party_type || chr(10)
||'lr_hdr_record.party_pan_no ='||lr_hdr_record.party_pan_no || chr(10)
||'lr_hdr_record.threshold_slab_id ='||lr_hdr_record.threshold_slab_id || chr(10)
||'lr_hdr_record.fin_year ='||lr_hdr_record.fin_year || chr(10)
||'lr_hdr_record.total_threshold_amt ='||lr_hdr_record.total_threshold_amt || chr(10)
||'lr_hdr_record.total_threshold_base_amt ='||lr_hdr_record.total_threshold_base_amt || chr(10)
||'lr_hdr_record.creation_date ='||lr_hdr_record.creation_date || chr(10)
||'lr_hdr_record.created_by ='||lr_hdr_record.created_by || chr(10)
||'lr_hdr_record.last_update_date ='||lr_hdr_record.last_update_date || chr(10)
||'lr_hdr_record.last_updated_by ='||lr_hdr_record.last_updated_by || chr(10)
||'lr_hdr_record.last_update_login ='||lr_hdr_record.last_update_login || chr(10)
);*/ --commented by bgowrava for bug#5631784
insert_threshold_hdr ( p_record => lr_hdr_record
, p_threshold_id => ln_threshold_id
, p_row_id => lx_row_id
);
||Part -2 :- CREATE OR UPDATE TABLE JAI_RGM_THRESHOLD_DTLS
***************************************************************************************/
/**
Assumption: When control comes here ln_threshold_id should NOT BE NULL.
*/
if ln_threshold_id is null then
p_process_flag := jai_constants.expected_error;
lr_dtl_record.last_update_date := sysdate;
lr_dtl_record.last_updated_by := ln_user_id;
lr_dtl_record.last_update_login := ln_login_id;
insert_threshold_dtl ( p_record => lr_dtl_record
, p_threshold_dtl_id => ln_threshold_dtl_id
, p_row_id => lx_row_id
);
else /** Threshold detils record already exists. Hence update the existing record to increment
per item classificaton amounts */
/* jai_cmn_debug_contexts_pkg.print (ln_reg_id
,'Updating JAI_RGM_THRESHOLD_DTLS' || chr(10)
||'invoice_amt ='||lr_dtl_record.invoice_amt || chr(10)
||'cash_receipt_amt ='||lr_dtl_record.cash_receipt_amt || chr(10)
||'application_amt ='||lr_dtl_record.application_amt || chr(10)
||'unapplication_amt ='||lr_dtl_record.unapplication_amt || chr(10)
||'reversal_amt ='||lr_dtl_record.reversal_amt || chr(10)
||'manual_surcharge_amt ='||lr_dtl_record.manual_surcharge_amt || chr(10)
||'system_surcharge_amt ='||lr_dtl_record.system_surcharge_amt || chr(10)
||'system_surcharge_cess_amt ='||lr_dtl_record.system_surcharge_cess_amt || chr(10)
||'threshold_base_amt ='||lr_dtl_record.threshold_base_amt || chr(10)
||'last_update_date ='||sysdate || chr(10)
||'last_updated_by ='||ln_user_id || chr(10)
||'last_update_login ='||ln_login_id || chr(10)
||'last_update_login ='||ln_threshold_dtl_id
); */ --commented by bgowrava for bug#5631784
update jai_rgm_threshold_dtls
set invoice_amt = lr_dtl_record.invoice_amt
, cash_receipt_amt = lr_dtl_record.cash_receipt_amt
, application_amt = lr_dtl_record.application_amt
, unapplication_amt = lr_dtl_record.unapplication_amt
, reversal_amt = lr_dtl_record.reversal_amt
, manual_surcharge_amt = lr_dtl_record.manual_surcharge_amt
, system_surcharge_amt = lr_dtl_record.system_surcharge_amt
, system_surcharge_cess_amt = lr_dtl_record.system_surcharge_cess_amt
, system_surcharge_sh_cess_amt = lr_dtl_record.system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
, threshold_base_amt = lr_dtl_record.threshold_base_amt
, last_update_date = sysdate
, last_updated_by = ln_user_id
, last_update_login = ln_login_id
where threshold_dtl_id = lr_dtl_record.threshold_dtl_id;
|| This is the last line of the document, so update the summary amounts maintained by header table.
|| As repository package is always making a call at document level (not at line level), this flag will
|| always be yes
*/
/* jai_cmn_debug_contexts_pkg.print ( ln_reg_id, 'Before : sync_threshold_header'); */ --commented by bgowrava for bug#5631784
/** Update jai_rgm_refs_all to punch the threshold_slab_id against all the transaction lines */
if ln_new_thhold_slab_id is not null then
update jai_rgm_refs_all
set threshold_slab_id = ln_new_thhold_slab_id
where transaction_id = p_transaction_id;
procedure insert_threshold_dtl ( p_record in jai_rgm_threshold_dtls%rowtype
, p_threshold_dtl_id out nocopy jai_rgm_threshold_dtls.threshold_dtl_id%type
, p_row_id out nocopy rowid
)
is
begin
if p_record.threshold_dtl_id is null then
select jai_rgm_thresholds_s.nextval
into p_threshold_dtl_id
from dual;
insert into jai_rgm_threshold_dtls
( threshold_dtl_id
, threshold_id
, item_classification
, invoice_amt
, cash_receipt_amt
, application_amt
, unapplication_amt
, reversal_amt
, manual_surcharge_amt
, system_surcharge_amt
, system_surcharge_cess_amt
, system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
, threshold_base_amt
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values
(
p_threshold_dtl_id
, p_record.threshold_id
, p_record.item_classification
, p_record.invoice_amt
, p_record.cash_receipt_amt
, p_record.application_amt
, p_record.unapplication_amt
, p_record.reversal_amt
, p_record.manual_surcharge_amt
, p_record.system_surcharge_amt
, p_record.system_surcharge_cess_amt
, p_record.system_surcharge_sh_cess_amt --Bgowrava for forward porting bug#5989740
, p_record.threshold_base_amt
, p_record.creation_date
, p_record.created_by
, p_record.last_update_date
, p_record.last_updated_by
, p_record.last_update_login
)
returning rowid
,threshold_dtl_id
into p_row_id
,p_threshold_dtl_id ;
end insert_threshold_dtl;
procedure insert_threshold_hdr ( p_record in jai_rgm_thresholds%rowtype
, p_threshold_id out nocopy jai_rgm_thresholds.threshold_id%type
, p_row_id out nocopy rowid
)
is
begin
if p_record.threshold_id is null then
select jai_rgm_thresholds_s.nextval
into p_threshold_id
from dual;
insert into jai_rgm_thresholds
( threshold_id
, regime_id
, org_tan_no
, party_id
, party_type
, party_pan_no
, threshold_slab_id
, fin_year
, total_threshold_amt
, total_threshold_base_amt
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
)
values
( p_threshold_id
, p_record.regime_id
, p_record.org_tan_no
, p_record.party_id
, p_record.party_type
, p_record.party_pan_no
, p_record.threshold_slab_id
, p_record.fin_year
, p_record.total_threshold_amt
, p_record.total_threshold_base_amt
, p_record.creation_date
, p_record.created_by
, p_record.last_update_date
, p_record.last_updated_by
, p_record.last_update_login
)
returning rowid
,threshold_id
into p_row_id
,p_threshold_id ;
end insert_threshold_hdr;
select sum ( nvl(invoice_amt ,0)
+ nvl(cash_receipt_amt ,0)
+ nvl(application_amt ,0)
+ nvl(unapplication_amt ,0)
+ nvl(reversal_amt ,0)
) total_threshold_amt
,sum ( nvl(threshold_base_amt ,0)
) total_threshold_base_amt
from jai_rgm_threshold_dtls
where threshold_id = p_threshold_id;
select regime_id
,org_tan_no
,party_type
,party_id
,fin_year
,threshold_slab_id
from jai_rgm_thresholds
where threshold_id = p_threshold_id;
update jai_rgm_thresholds
set total_threshold_amt = ln_total_thhold_amt
,total_threshold_base_amt = ln_total_thhold_base_amt
,last_updated_by = ln_user_id
,last_update_date = sysdate
,last_update_login = ln_login_id
where threshold_id = p_threshold_id;
/** Slab is changed. Hence update threshold slab in the jai_rgm_thresholds */
update jai_rgm_thresholds
set threshold_slab_id = ln_new_thhold_slab_id
,last_update_date = sysdate
,last_updated_by = ln_user_id
,last_update_login = ln_login_id
where threshold_id = p_threshold_id;