[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
, pay_balance_dimensions pbd
, pay_defined_balances pdb
WHERE pbt.balance_name = p_bal_name
AND pbd.dimension_name = p_dim_name
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbd.legislation_code = 'ZA'
AND pbt.legislation_code = 'ZA';
select distinct dirnum from (
SELECT process.result_value dirnum
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, per_time_periods ptp
, per_time_periods ptp_all
, pay_assignment_actions paa_all
, pay_payroll_actions ppa_all
, pay_run_results prr_ls
, pay_run_result_values target
, pay_balance_feeds_f feed
, pay_run_result_values process
, pay_input_values_f process_iv
, pay_input_values_f piv
WHERE paa.assignment_action_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id
AND ptp_all.prd_information1 = ptp.prd_information1
AND ppa_all.time_period_id = ptp_all.time_period_id
AND paa_all.payroll_action_id = ppa_all.payroll_action_id
AND paa_all.assignment_id = paa.assignment_id
AND paa_all.action_sequence <= paa.action_sequence
AND prr_ls.assignment_action_id = paa_all.assignment_action_id
AND prr_ls.status IN ('P', 'PA')
AND target.run_result_id = prr_ls.run_result_id
AND feed.input_value_id = target.input_value_id
AND ppa_all.effective_date
BETWEEN feed.effective_start_date
AND feed.effective_end_date
AND process.run_result_id = prr_ls.run_result_id
AND process.input_value_id = process_iv.input_value_id
AND process_iv.name = 'Tax Directive Number'
AND ppa_all.effective_date BETWEEN process_iv.effective_start_date AND process_iv.effective_end_date
AND piv.input_value_id = target.input_value_id
AND ptp.pay_advice_date = ptp_all.pay_advice_date
AND nvl(process.result_value,'To Be Advised') <> 'To Be Advised'
ORDER BY paa_all.action_sequence , process.result_value);
trc_LSDirMTDTab.delete;
trc_LSNetTxbIncTab.delete;
trc_LSSklDirNumTab.delete;
SELECT trunc(dbi_SES_DTE,'Month')
INTO nti_CurMthStrtDte
FROM dual;
SELECT last_day(dbi_SES_DTE)
INTO nti_CurMthEndDte
FROM dual;
SELECT pbt.balance_type_id
INTO l_BalTypId
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_BalNme
AND pbt.legislation_code = 'ZA';
SELECT pbt.balance_type_id
INTO l_BalTypId
FROM pay_balance_types pbt
WHERE pbt.balance_name = p_BalNme;
SELECT /*+ use_nl ( ptp_all ppa_all feed)*/ process.result_value dirnum
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, per_time_periods ptp
, per_time_periods ptp_all
, pay_assignment_actions paa_all
, pay_payroll_actions ppa_all
, pay_run_results prr_ls
, pay_run_result_values target
, pay_balance_feeds_f feed
, pay_run_result_values process
, pay_input_values_f process_iv
, pay_balance_types pbt
, pay_element_types_f pet
, pay_input_values_f piv
WHERE paa.assignment_action_id = p_ass_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id
AND ptp_all.prd_information1 = ptp.prd_information1
AND ppa_all.time_period_id = ptp_all.time_period_id
AND paa_all.payroll_action_id = ppa_all.payroll_action_id
AND paa_all.assignment_id = paa.assignment_id
AND paa_all.action_sequence <= paa.action_sequence
AND prr_ls.assignment_action_id = paa_all.assignment_action_id
AND prr_ls.status IN ('P', 'PA')
AND target.run_result_id = prr_ls.run_result_id
AND feed.input_value_id = target.input_value_id
AND ppa_all.effective_date
BETWEEN feed.effective_start_date
AND feed.effective_end_date
AND process.run_result_id = prr_ls.run_result_id
AND process.input_value_id = process_iv.input_value_id
AND process_iv.name = 'Tax Directive Number'
AND ppa_all.effective_date
BETWEEN process_iv.effective_start_date
AND process_iv.effective_end_date
AND feed.balance_type_id = pbt.balance_type_id
AND pbt.balance_name = 'Tax on Lump Sums'
AND pet.element_name NOT LIKE 'Medical Tax Credit Used on LMPSM%'
AND pet.element_type_id = piv.element_type_id
AND ppa_all.effective_date
BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND piv.input_value_id = target.input_value_id
ORDER BY paa_all.action_sequence , process.result_value;
trc_LSDirTab.delete;
select paei.AEI_INFORMATION8
into trc_PenBasis
from per_assignment_extra_info paei
where paei.assignment_id = con_ASG_ID
and paei.information_type = 'ZA_SPECIFIC_INFO';
Select count(*)
into l_lmsm_proc_cnt
from
pay_balance_feeds_f FEED,
pay_run_result_values TARGET,
pay_run_results RR,
pay_payroll_actions PACT,
pay_assignment_actions ASSACT,
pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT
where FEED.BALANCE_TYPE_ID = (
select BALANCE_TYPE_ID
from pay_balance_types
where balance_name= 'Tax on Lump Sums'
and legislation_code = 'ZA') -- Balance_type_id for Tax on Lump Sums
and BAL_ASSACT.assignment_action_id = con_ASG_ACT_ID
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and RR.status in ('P','PA')
and PACT.time_period_id = BACT.time_period_id
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
Select count(*)
into l_ret_lmsm_proc_cnt
from
pay_balance_feeds_f FEED,
pay_run_result_values TARGET,
pay_run_results RR,
pay_payroll_actions PACT,
pay_assignment_actions ASSACT,
pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT
where FEED.BALANCE_TYPE_ID = (
select BALANCE_TYPE_ID
from pay_balance_types
where balance_name= 'Tax on Retirement Fund Lump Sums'
and legislation_code = 'ZA') -- Balance_type_id for Tax on Retirement Fund Lump Sums
and BAL_ASSACT.assignment_action_id = con_ASG_ACT_ID
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.input_value_id = TARGET.input_value_id
and TARGET.run_result_id = RR.run_result_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and RR.status in ('P','PA')
and PACT.time_period_id = BACT.time_period_id
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id;