[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
max(decode(BTYPE.balance_name,'NI A Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Employee Notional',
DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Employee',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Employee Notional',
DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Employer',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI A EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI B EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI J EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI L EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Able LEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Able UEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Able ET',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Able AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S EE AUEL',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S CO Able',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI C Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI E Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI G Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI S Ers Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI D Ees Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'NI F Ees Rebate',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'Employee COMP Min Payment',DEFBAL.Defined_balance_id))
,max(decode(BTYPE.balance_name,'Employer COMP Min Payment',DEFBAL.Defined_balance_id))
into
g_ni_a_employee_defbal ,g_ni_b_employee_defbal ,g_ni_c_employee_defbal ,g_ni_d_employee_defbal ,g_ni_e_employee_defbal
,g_ni_f_employee_defbal ,g_ni_g_employee_defbal ,g_ni_j_employee_defbal, g_ni_l_employee_defbal, g_ni_l_employee_not_defbal
,g_ni_s_employee_defbal ,g_ni_s_employee_not_defbal
,g_ni_a_employer_defbal ,g_ni_b_employer_defbal ,g_ni_c_employer_defbal ,g_ni_d_employer_defbal ,g_ni_e_employer_defbal
,g_ni_f_employer_defbal ,g_ni_g_employer_defbal, g_ni_j_employer_defbal, g_ni_l_employer_defbal ,g_ni_s_employer_defbal
,g_ni_a_able_defbal ,g_ni_a_able_lel_defbal, g_ni_a_able_uel_defbal, g_ni_a_able_et_defbal, g_ni_a_able_auel_defbal,
g_ni_a_ee_auel_defbal
,g_ni_b_able_defbal ,g_ni_b_able_lel_defbal, g_ni_b_able_uel_defbal, g_ni_b_able_et_defbal, g_ni_b_able_auel_defbal,
g_ni_b_ee_auel_defbal
,g_ni_c_able_defbal ,g_ni_c_able_lel_defbal, g_ni_c_able_uel_defbal, g_ni_c_able_et_defbal, g_ni_c_able_auel_defbal,
g_ni_c_ee_auel_defbal
,g_ni_d_able_defbal ,g_ni_d_able_lel_defbal, g_ni_d_able_uel_defbal, g_ni_d_able_et_defbal, g_ni_d_able_auel_defbal,
g_ni_d_ee_auel_defbal
,g_ni_e_able_defbal ,g_ni_e_able_lel_defbal, g_ni_e_able_uel_defbal, g_ni_e_able_et_defbal, g_ni_e_able_auel_defbal,
g_ni_e_ee_auel_defbal
,g_ni_f_able_defbal ,g_ni_f_able_lel_defbal, g_ni_f_able_uel_defbal, g_ni_f_able_et_defbal, g_ni_f_able_auel_defbal,
g_ni_f_ee_auel_defbal
,g_ni_g_able_defbal ,g_ni_g_able_lel_defbal, g_ni_g_able_uel_defbal, g_ni_g_able_et_defbal, g_ni_g_able_auel_defbal,
g_ni_g_ee_auel_defbal
,g_ni_j_able_defbal ,g_ni_j_able_lel_defbal, g_ni_j_able_uel_defbal, g_ni_j_able_et_defbal, g_ni_j_able_auel_defbal,
g_ni_j_ee_auel_defbal
,g_ni_l_able_defbal ,g_ni_l_able_lel_defbal, g_ni_l_able_uel_defbal, g_ni_l_able_et_defbal, g_ni_l_able_auel_defbal,
g_ni_l_ee_auel_defbal
,g_ni_s_able_defbal ,g_ni_s_able_lel_defbal, g_ni_s_able_uel_defbal, g_ni_s_able_et_defbal, g_ni_s_able_auel_defbal,
g_ni_s_ee_auel_defbal
,g_ni_c_co_able_defbal ,g_ni_d_co_able_defbal ,g_ni_e_co_able_defbal
,g_ni_f_co_able_defbal ,g_ni_g_co_able_defbal ,g_ni_s_co_able_defbal
,g_ni_c_ers_rebate_defbal ,g_ni_d_ers_rebate_defbal ,g_ni_e_ers_rebate_defbal
,g_ni_f_ers_rebate_defbal ,g_ni_g_ers_rebate_defbal ,g_ni_s_ers_rebate_defbal
,g_ni_d_ees_rebate_defbal ,g_ni_f_ees_rebate_defbal , g_comp_min_ees_defbal,
g_comp_min_ers_defbal
from
pay_balance_types BTYPE,
pay_defined_balances DEFBAL,
pay_balance_dimensions DIM
where DEFBAL.balance_dimension_id = DIM.balance_dimension_id
and DIM.database_item_suffix = p_database_item_suffix
and DIM.legislation_code = 'GB'
and DEFBAL.legislation_code = 'GB'
and defbal.balance_type_id = btype.balance_type_id
and BTYPE.legislation_code = 'GB';
p_balance_list.delete;
select balance_type_id
into g_ni_able_id
from pay_balance_types
where balance_name = 'NIable Pay';
select element_type_id
into g_ni_element_type_id
from pay_element_types_f
where element_name = 'NI'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into g_cat_input_id
from pay_input_values_f
where name = 'Category'
and element_type_id = g_ni_element_type_id
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into g_pen_input_id
from pay_input_values_f
where name = 'Pension'
and element_type_id = g_ni_element_type_id
and sysdate between effective_start_date
and effective_end_date;
select BACT.effective_date, BAL_ASSACT.assignment_id
into l_bact_effective_date, l_assignment_id
from pay_payroll_actions BACT,
pay_assignment_actions BAL_ASSACT
where BAL_ASSACT.assignment_action_id = p_assignment_action_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id;
select /*+ ORDERED INDEX(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK,
BACT PAY_PAYROLL_ACTIONS_PK,
BPTP PER_TIME_PERIODS_PK,
START_ASS PER_ASSIGNMENTS_F_PK,
ASS PER_ASSIGNMENTS_F_N12,
ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
PACT PAY_PAYROLL_ACTIONS_PK,
PPTP PER_TIME_PERIODS_PK ,
RR PAY_RUN_RESULTS_N50,
TARGET PAY_RUN_RESULT_VALUES_PK,
FEED PAY_BALANCE_FEEDS_F_UK2 )
USE_NL(BAL_ASSACT,BACT,BPTP,START_ASS,ASS,ASSACT,PACT,PPTP,RR,TARGET,FEED) +*/
nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'AN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'AA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'BN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'BA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'CN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'CC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'DC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'EC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'FM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'GM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'JN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'JA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'LC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'SM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
into g_st_ni_a_able , g_st_ni_ap_able , g_st_ni_b_able ,
g_st_ni_bp_able, g_st_ni_c_able , g_st_ni_co_able ,
g_st_ni_d_able , g_st_ni_e_able , g_st_ni_f_able ,
g_st_ni_g_able , g_st_ni_j_able, g_st_ni_jp_able,
g_st_ni_l_able, g_st_ni_s_able
from
pay_assignment_actions BAL_ASSACT
,pay_payroll_actions BACT
,per_time_periods BPTP
,per_all_assignments_f START_ASS
,per_all_assignments_f ASS
,pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,per_time_periods PPTP
,pay_run_results RR
,pay_run_result_values TARGET
,pay_balance_feeds_f FEED
where BAL_ASSACT.assignment_action_id = p_assignment_action_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = g_ni_able_id + decode(RR.run_result_id, null, 0, 0)
and nvl(TARGET.result_value,'0') <> '0'
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 BPTP.time_period_id = BACT.time_period_id
and PPTP.time_period_id = PACT.time_period_id
and START_ASS.assignment_id = BAL_ASSACT.assignment_id
and ASS.person_id = START_ASS.person_id /* person level not pos */
and ASSACT.assignment_id = ASS.assignment_id
and BACT.effective_date between
START_ASS.effective_start_date and START_ASS.effective_end_date
and PACT.effective_date between
ASS.effective_start_date and ASS.effective_end_date
and PACT.effective_date >=
/* find the latest td payroll transfer date - compare each of the */
/* assignment rows with its predecessor looking for the payroll */
/* that had a different tax district at that date */
( select /*+ ORDERED INDEX (NASS PER_ASSIGNMENTS_F_PK,
PASS PER_ASSIGNMENTS_F_PK,
ROLL PAY_PAYROLLS_F_PK,
FLEX HR_SOFT_CODING_KEYFLEX_PK,
PROLL PAY_PAYROLLS_F_PK,
PFLEX HR_SOFT_CODING_KEYFLEX_PK)
USE_NL(NASS,PASS,ROLL,FLEX,PROLL,PFLEX) +*/
nvl(max(NASS.effective_start_date), to_date('01-01-0001','DD-MM-YYYY'))
from per_all_assignments_f NASS
,per_all_assignments_f PASS
,pay_all_payrolls_f ROLL
,hr_soft_coding_keyflex FLEX
,pay_all_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where NASS.assignment_id = ASS.assignment_id
and ROLL.payroll_id = NASS.payroll_id
and NASS.effective_start_date between
ROLL.effective_start_date and ROLL.effective_end_date
and ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and NASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (NASS.effective_start_date - 1)
and NASS.effective_start_date <= BACT.effective_date
and PROLL.payroll_id = PASS.payroll_id
and NASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and NASS.payroll_id <> PASS.payroll_id
and FLEX.segment1 <> PFLEX.segment1
)
and exists ( select null from
/* check that the current assignment tax districts match */
pay_all_payrolls_f BROLL
,hr_soft_coding_keyflex BFLEX
,pay_all_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where BACT.payroll_id = BROLL.payroll_id
and PACT.payroll_id = PROLL.payroll_id
and BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
and PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
and BACT.effective_date between
BROLL.effective_start_date and BROLL.effective_end_date
and BACT.effective_date between
PROLL.effective_start_date and PROLL.effective_end_date
and BFLEX.segment1 = PFLEX.segment1
)
and PPTP.regular_payment_date >= l_stat_period_start
and PACT.effective_date >= l_start_director
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence;
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select to_date('06-04-' || to_char( to_number(
to_char( l_effective_date,'YYYY'))
+ decode(sign( l_effective_date - to_date('06-04-'
|| to_char(l_effective_date,'YYYY'),'DD-MM-YYYY')),
-1,-1,0)),'DD-MM-YYYY') finyear
into l_tax_year_start
from dual;
select nvl(min(p.effective_start_date)
,to_date('31-12-4712','dd-mm-yyyy'))
into l_start_of_director_date
from per_people_f p,
per_assignments_f ASS
where p.per_information2 = 'Y'
and ASS.assignment_id = p_assignment_id
and l_effective_date between
ASS.effective_start_date and ASS.effective_end_date
and ASS.person_id = P.person_id
and P.effective_start_date <= l_effective_date
and p.effective_end_date >= l_tax_year_start ;
select 52 - greatest(0,least(52,trunc(( l_start_of_director_date
- l_tax_year_start)/7)))
into l_weeks
from dual;
select nvl(max(1),0) into l_valid
from pay_user_column_instances CINST
, pay_user_columns C
, pay_user_rows R
, pay_user_tables TAB
where TAB.user_table_name = p_user_table
and C.user_column_name = p_user_column
and fnd_number.canonical_to_number(CINST.value)
= fnd_number.canonical_to_number(p_user_value)
and C.user_table_id = TAB.user_table_id
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id;
select fnd_number.canonical_to_number(CINST.value) into l_value from
pay_user_column_instances CINST
, pay_user_column_instances LABEL
, pay_user_columns CLABEL
, pay_user_columns C
, pay_user_rows R
, pay_user_tables TAB
where TAB.user_table_name = p_user_table
and C.user_column_name = p_user_column
and C.user_table_id = TAB.user_table_id
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and LABEL.value = p_label
and CLABEL.user_column_name = 'LABEL'
and CLABEL.user_column_id = LABEL.user_column_id
and CLABEL.user_table_id = TAB.user_table_id
and LABEL.user_row_id = R.user_row_id;
select decode(substr(p_high_or_low,1,1),
'H',fnd_number.canonical_to_number(R.ROW_HIGH_RANGE),
fnd_number.canonical_to_number(R.row_low_range_or_name))
into l_value from
pay_user_column_instances LABEL
, pay_user_columns CLABEL
, pay_user_rows R
, pay_user_tables TAB
where TAB.user_table_name = p_user_table
and R.user_table_id = TAB.user_table_id
and TAB.user_key_units = 'N'
and LABEL.value = p_label
and CLABEL.user_column_name = 'LABEL'
and CLABEL.user_column_id = LABEL.user_column_id
and CLABEL.user_table_id = TAB.user_table_id
and LABEL.user_row_id = R.user_row_id;
select min(fnd_number.canonical_to_number(CINST.value))
into l_value from
pay_user_column_instances CINST
, pay_user_column_instances LABEL
, pay_user_columns CLABEL
, pay_user_columns C
, pay_user_rows R
, pay_user_tables TAB
where upper(TAB.user_table_name) = 'NI_ERS_WEEKLY'
and C.user_column_name = 'C_ERS_RATE_CO'
and C.user_table_id = TAB.user_table_id
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and fnd_number.canonical_to_number(LABEL.value)
= fnd_number.canonical_to_number(p_ci_rate)
and CLABEL.user_column_name = 'C_ERS_RATE_CI'
and CLABEL.user_column_id = LABEL.user_column_id
and CLABEL.user_table_id = TAB.user_table_id
and LABEL.user_row_id = R.user_row_id;
select min(fnd_number.canonical_to_number(CINST.value))
into l_value from
pay_user_column_instances CINST
, pay_user_column_instances LABEL
, pay_user_columns CLABEL
, pay_user_columns C
, pay_user_rows R
, pay_user_tables TAB
where upper(TAB.user_table_name) = 'NI_ERS_WEEKLY'
and C.user_column_name = 'C_ERS_RATE_CM'
and C.user_table_id = TAB.user_table_id
and CINST.user_column_id = C.user_column_id
and R.user_table_id = TAB.user_table_id
and TAB.user_key_units = 'N'
and CINST.user_row_id = R.user_row_id
and fnd_number.canonical_to_number(LABEL.value) = fnd_number.canonical_to_number(p_ci_rate)
and CLABEL.user_column_name = 'C_ERS_RATE_CI'
and CLABEL.user_column_id = LABEL.user_column_id
and CLABEL.user_table_id = TAB.user_table_id
and LABEL.user_row_id = R.user_row_id;
select act.assignment_id,
ptp.regular_payment_date,
ptp.period_type
into l_assignment_id,
l_effective_date,
l_payroll_period_type
from pay_assignment_actions act,
pay_payroll_actions pact,
per_time_periods ptp
where act.assignment_action_id = p_assignment_action_id
and pact.payroll_action_id = act.payroll_action_id
and ptp.time_period_id = pact.time_period_id;
select element_type_id
into l_element_type_id
from pay_element_types_f
where element_name = 'NI'
and l_effective_date between
effective_start_date and effective_end_date;
select input_value_id
into l_period_type_id
from pay_input_values_f
where name = 'Priority Period Type'
and element_type_id = l_element_type_id
and l_effective_date between
effective_start_date and effective_end_date;
select ENT_PT.screen_entry_value
into l_period_type
from pay_element_entry_values_f ENT_PT
,pay_element_entries_f ENT
,pay_element_links_f EL
where ENT_PT.input_value_id + 0 = l_period_type_id
and ENT_PT.element_entry_id = ENT.element_entry_id
and ENT.assignment_id = l_assignment_id
and EL.element_type_id = l_element_type_id
and EL.element_link_id = ENT.element_link_id
and l_effective_date between
EL.effective_start_date and EL.effective_end_date
and l_effective_date between
ENT_PT.effective_start_date and ENT_PT.effective_end_date
and l_effective_date between
ENT.effective_start_date and ENT.effective_end_date;
select ptpt.NUMBER_PER_FISCAL_YEAR
into l_freq
from
per_time_period_types ptpt
where ptpt.period_type = nvl(l_period_type,l_payroll_period_type);
select person_id
into l_person_id
from per_assignments
where assignment_id = p_assignment_id;
select count(1) into l_count
from per_assignments a
where a.person_id = l_person_id
and a.payroll_id is not null;
select count(1) into l_count
from per_assignments_f a, per_assignment_status_types st
where st.assignment_status_type_id = a.assignment_status_type_id
and p_date between a.effective_start_date and a.effective_end_date
and a.payroll_id = p_payroll_id
and st.pay_system_status = 'P';
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');
select FLEX.segment1,a.person_id
into l_tax_reference, l_person_id
from pay_payrolls_f p,
per_assignments_f a,
hr_soft_coding_keyflex FLEX
where a.assignment_id = p_assignment_id
and a.payroll_id = p.payroll_id
and l_effective_date
between a.effective_start_date and a.effective_end_date
and l_effective_date
between p.effective_start_date and p.effective_end_date
and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id;
select count(distinct ptpt.NUMBER_PER_FISCAL_YEAR) into l_count
from pay_payrolls_f p,
per_assignments_f a,
hr_soft_coding_keyflex FLEX,
per_time_period_types ptpt
where a.person_id = l_person_id
and a.assignment_type = 'E'
and a.payroll_id = p.payroll_id
and nvl(hr_gbnidir.element_entry_value(
a.assignment_id, l_effective_date,
'NI','Priority Period Type') ,p.period_type)
= ptpt.period_type
and l_effective_date
between a.effective_start_date and a.effective_end_date
and l_effective_date
between p.effective_start_date and p.effective_end_date
and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id
and FLEX.segment1 = l_tax_reference;
select assignment_id,effective_date
into l_assignment_id,l_effective_date
from pay_assignment_actions BASSACT,
pay_payroll_actions BACT
where BASSACT.assignment_action_id = p_assignment_action_id
and BACT.payroll_action_id = BASSACT.payroll_action_id;
select
/*+
ordered
use_nl(a p flex)
index(a per_assignments_f_pk)
index(p pay_payrolls_f_pk)
index(flex hr_soft_coding_keyflex_pk)
*/
FLEX.segment1,a.person_id into l_tax_reference, l_person_id
from per_all_assignments_f a,
pay_all_payrolls_f p,
hr_soft_coding_keyflex FLEX
where a.assignment_id = l_assignment_id
and a.payroll_id = p.payroll_id
and l_effective_date between a.effective_start_date and a.effective_end_date
and l_effective_date between p.effective_start_date and p.effective_end_date
and FLEX.soft_coding_keyflex_id(+) = p.soft_coding_keyflex_id;
select
/*+
ordered
use_nl(a p flex ptpt)
index(a per_assignments_f_n12)
index(p pay_payrolls_f_pk)
index(flex hr_soft_coding_keyflex_pk)
index(ptpt per_time_period_types_pk)
*/
min(ptpt.NUMBER_PER_FISCAL_YEAR) into l_freq
from per_all_assignments_f a,
pay_all_payrolls_f p,
hr_soft_coding_keyflex FLEX,
per_time_period_types ptpt
where a.person_id = l_person_id
and a.payroll_id = p.payroll_id
and nvl(hr_gbnidir.element_entry_value(a.assignment_id,
l_effective_date,'NI','Priority Period Type'),
p.period_type) = ptpt.period_type
and l_effective_date between a.effective_start_date and a.effective_end_date
and l_effective_date between p.effective_start_date and p.effective_end_date
and FLEX.soft_coding_keyflex_id = p.soft_coding_keyflex_id
and FLEX.segment1 = l_tax_reference;
SELECT type_tl.element_type_id
INTO l_element_type_id
FROM pay_element_types_f_tl type_tl
WHERE EXISTS
(SELECT 1
FROM pay_element_types_f type
WHERE type.element_type_id = type_tl.element_type_id
AND p_effective_date BETWEEN
type.effective_start_date AND type.effective_end_date)
AND type_tl.language = USERENV ('LANG')
AND type_tl.element_name = p_element_name;
select iv.input_value_id
into l_input_id
from pay_input_values_f_tl IV_TL,
pay_input_values_f IV
where iv_tl.input_value_id = iv.input_value_id
and userenv('LANG') = iv_tl.language
and iv_tl.name = p_input_name
and iv.element_type_id = l_element_type_id
and p_effective_date between
iv.effective_start_date and iv.effective_end_date;
select max(ENT_PT.screen_entry_value)
into l_value
from pay_element_entry_values_f ENT_PT
,pay_element_entries_f ENT
,pay_element_links_f EL
where ENT_PT.input_value_id + 0 = l_input_id
and ENT_PT.element_entry_id = ENT.element_entry_id
and ENT.assignment_id = p_assignment_id
and EL.element_type_id = l_element_type_id
and EL.element_link_id = ENT.element_link_id
and p_effective_date between
EL.effective_start_date and EL.effective_end_date
and p_effective_date between
ENT_PT.effective_start_date and ENT_PT.effective_end_date
and p_effective_date between
ENT.effective_start_date and ENT.effective_end_date;
select element_type_id
into l_element_type_id
from pay_element_types_f
where element_name = 'NI'
and p_effective_date between
effective_start_date and effective_end_date;
select input_value_id
into l_cat_input_id
from pay_input_values_f
where name = 'Category'
and element_type_id = l_element_type_id
and p_effective_date between
effective_start_date and effective_end_date;
select input_value_id
into l_pen_input_id
from pay_input_values_f
where name = 'Pension'
and element_type_id = l_element_type_id
and p_effective_date between
effective_start_date and effective_end_date;
select max(decode(ENT_PT.input_value_id,l_cat_input_id,ENT_PT.screen_entry_value,null)),
max(decode(ENT_PT.input_value_id,l_pen_input_id,ENT_PT.screen_entry_value,null))
into l_category, l_pension
from pay_element_entry_values_f ENT_PT
,pay_element_entries_f ENT
,pay_element_links_f EL
where ENT_PT.element_entry_id = ENT.element_entry_id
and ENT.assignment_id = p_assignment_id
and EL.element_type_id = l_element_type_id
and EL.element_link_id = ENT.element_link_id
and p_effective_date between
EL.effective_start_date and EL.effective_end_date
and p_effective_date between
ENT.effective_start_date and ENT.effective_end_date
and ENT.effective_start_date = ENT_PT.effective_start_date
and ENT.effective_end_date = ENT_PT.effective_end_date;
select balance_type_id
into g_ni_able_id
from pay_balance_types
where balance_name = 'NIable Pay';
select element_type_id
into g_ni_element_type_id
from pay_element_types_f
where element_name = 'NI'
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into g_cat_input_id
from pay_input_values_f
where name = 'Category'
and element_type_id = g_ni_element_type_id
and sysdate between effective_start_date
and effective_end_date;
select input_value_id
into g_pen_input_id
from pay_input_values_f
where name = 'Pension'
and element_type_id = g_ni_element_type_id
and sysdate between effective_start_date
and effective_end_date;
/* OF SELECTION FROM BALANCE TABLES. */
select /*+ ORDERED INDEX(BAL_ASSACT PAY_ASSIGNMENT_ACTIONS_PK,
BACT PAY_PAYROLL_ACTIONS_PK,
BPTP PER_TIME_PERIODS_PK,
START_ASS PER_ASSIGNMENTS_F_PK,
ASS PER_ASSIGNMENTS_N4,
ASSACT PAY_ASSIGNMENT_ACTIONS_N51,
PACT PAY_PAYROLL_ACTIONS_PK,
PPTP PER_TIME_PERIODS_PK ,
RR PAY_RUN_RESULTS_N50,
TARGET PAY_RUN_RESULT_VALUES_PK,
FEED PAY_BALANCE_FEEDS_F_UK2)
USE_NL(BAL_ASSACT,BACT,BPTP,START_ASS,ASS,ASSACT,PACT,PPTP,RR,TARGET,FEED) +*/
nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'AN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'AA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'BN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'BA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'CN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'CC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'DC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'EC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'FM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'GM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'JN',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'JA',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'LC',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
,nvl(sum(decode(hr_gbnidir.NI_ELEMENT_ENTRY_VALUE(ASSACT.assignment_id, PACT.effective_date),
'SM',fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale,0)),0)
into g_st_ni_a_able , g_st_ni_ap_able, g_st_ni_b_able ,
g_st_ni_bp_able, g_st_ni_c_able , g_st_ni_co_able ,
g_st_ni_d_able , g_st_ni_e_able , g_st_ni_f_able ,
g_st_ni_g_able , g_st_ni_j_able, g_st_ni_jp_able,
g_st_ni_l_able, g_st_ni_s_able
from
pay_assignment_actions BAL_ASSACT
,pay_payroll_actions BACT
,per_time_periods BPTP
,per_all_assignments_f START_ASS
,per_all_assignments_f ASS
,pay_assignment_actions ASSACT
,pay_payroll_actions PACT
,per_time_periods PPTP
,pay_run_results RR
,pay_run_result_values TARGET
,pay_balance_feeds_f FEED
where BAL_ASSACT.assignment_action_id = p_assignment_action_id
and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
and FEED.balance_type_id = g_ni_able_id + decode(RR.run_result_id, null, 0, 0)
and nvl(TARGET.result_value,'0') <> '0'
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 BPTP.time_period_id = BACT.time_period_id
and PPTP.time_period_id = PACT.time_period_id
and START_ASS.assignment_id = BAL_ASSACT.assignment_id
and ASS.period_of_service_id = START_ASS.period_of_service_id
and ASSACT.assignment_id = ASS.assignment_id
and BACT.effective_date between
START_ASS.effective_start_date and START_ASS.effective_end_date
and PACT.effective_date between
ASS.effective_start_date and ASS.effective_end_date
and PACT.effective_date >=
/* find the latest td payroll transfer date - compare each of the */
/* assignment rows with its predecessor looking for the payroll */
/* that had a different tax district at that date */
( select /*+ ORDERED INDEX (NASS PER_ASSIGNMENTS_F_PK,
PASS PER_ASSIGNMENTS_F_PK,
ROLL PAY_PAYROLLS_F_PK,
FLEX HR_SOFT_CODING_KEYFLEX_PK,
PROLL PAY_PAYROLLS_F_PK,
PFLEX HR_SOFT_CODING_KEYFLEX_PK)
USE_NL(NASS,PASS,ROLL,FLEX,PROLL,PFLEX) +*/
nvl(max(NASS.effective_start_date), to_date('01-01-0001','DD-MM-YYYY'))
from per_all_assignments_f NASS
,per_all_assignments_f PASS
,pay_all_payrolls_f ROLL
,hr_soft_coding_keyflex FLEX
,pay_all_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where NASS.assignment_id = ASS.assignment_id
and ROLL.payroll_id = NASS.payroll_id
and NASS.effective_start_date between
ROLL.effective_start_date and ROLL.effective_end_date
and ROLL.soft_coding_keyflex_id = FLEX.soft_coding_keyflex_id
and NASS.assignment_id = PASS.assignment_id
and PASS.effective_end_date = (NASS.effective_start_date - 1)
and NASS.effective_start_date <= BACT.effective_date
and PROLL.payroll_id = PASS.payroll_id
and NASS.effective_start_date between
PROLL.effective_start_date and PROLL.effective_end_date
and PROLL.soft_coding_keyflex_id = PFLEX.soft_coding_keyflex_id
and NASS.payroll_id <> PASS.payroll_id
and FLEX.segment1 <> PFLEX.segment1
)
and exists ( select null from
/* check that the current assignment tax districts match */
pay_all_payrolls_f BROLL
,hr_soft_coding_keyflex BFLEX
,pay_all_payrolls_f PROLL
,hr_soft_coding_keyflex PFLEX
where BACT.payroll_id = BROLL.payroll_id
and PACT.payroll_id = PROLL.payroll_id
and BFLEX.soft_coding_keyflex_id = BROLL.soft_coding_keyflex_id
and PFLEX.soft_coding_keyflex_id = PROLL.soft_coding_keyflex_id
and BACT.effective_date between
BROLL.effective_start_date and BROLL.effective_end_date
and BACT.effective_date between
PROLL.effective_start_date and PROLL.effective_end_date
and BFLEX.segment1 = PFLEX.segment1
)
and PPTP.regular_payment_date >= l_stat_period_start
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence;
select ptp.regular_payment_date,
ptp.period_type,
ptp.start_date,
ptp.end_date
from per_assignments_f paf,
per_time_periods ptp
where paf.assignment_id = c_assignment_id
and ptp.payroll_id = paf.payroll_id
and c_effective_date between
ptp.start_date and ptp.end_date
and c_effective_date between
paf.effective_start_date and paf.effective_end_date;
select pet.element_type_id,
piv.input_value_id
from pay_input_values_f piv,
pay_element_types_f pet
where pet.element_name = 'NI'
and c_effective_date between
pet.effective_start_date and pet.effective_end_date
and piv.element_type_id = pet.element_type_id
and piv.name = 'Priority Period Type'
and c_effective_date between
piv.effective_start_date and piv.effective_end_date;
select ENT_PT.screen_entry_value
from pay_element_entry_values_f ENT_PT
,pay_element_entries_f ENT
,pay_element_links_f EL
where ENT_PT.input_value_id + 0 = c_period_type_id
and ENT_PT.element_entry_id = ENT.element_entry_id
and ENT.assignment_id = c_assignment_id
and EL.element_type_id = c_element_type_id
and EL.element_link_id = ENT.element_link_id
and c_effective_date between
EL.effective_start_date and EL.effective_end_date
and c_effective_date between
ENT_PT.effective_start_date and ENT_PT.effective_end_date
and c_effective_date between
ENT.effective_start_date and ENT.effective_end_date;
select ptpt.number_per_fiscal_year
from per_time_period_types ptpt
where ptpt.period_type = nvl(c_asg_period_type, c_payroll_period_type);