[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select per.employee_number,
paa.assignment_action_id ASS_ACTION_ID,
ppa.effective_date CF_EFF_DATE,
per.national_identifier,
per.per_information1,
per.per_information2,
per.last_name,
per.first_name,
ass.assignment_number,
per.first_name || ' ' ||
substr(per.middle_names, 1, (replace(instr(per.middle_names, ',', 1), 0, 250) - 1)) FIRST_NAMES,
to_char(per.date_of_birth, 'YYYYMMDD') DATE_OF_BIRTH,
substr(per.first_name, 1, 1) || nvl(substr(per.middle_names, 1, 1), '')
|| substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 1), 0, 250) + 1), 1)
|| substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 2), 0, 250) + 1), 1)
|| substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 3), 0, 250) + 1), 1)
|| substr(per.middle_names, (replace(instr(per.middle_names, ',', 1, 4), 0, 250) + 1), 1) INITIALS,
per.middle_names,
adr.address_line1,
adr.address_line2,
adr.address_line3,
adr.town_or_city,
adr.postal_code,
ass.assignment_id,
ass.location_id,
ass.payroll_id,
ass.effective_start_date,
ass.effective_end_date,
aei.aei_information4,
aei.aei_information7,
aei.aei_information6,
aei.aei_information3,
nvl(aei.aei_information8, nvl(scl.segment10, '1')) AEI_INFORMATION8,
decode(aei.aei_information2, null, per.last_name, aei.aei_information2) AEI_INFORMATION2,
hrl.location_code,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city CITY,
hrl.postal_code POSTCODE,
org.name ORG_NAME,
hoi.organization_id,
hoi.org_information1,
hoi.org_information3,
hoi.org_information4,
nvl(fcl.meaning, 'A') NATURE,
paa.serial_number
from hr_all_organization_units org,
per_all_people_f per,
per_addresses adr,
per_all_assignments_f ass,
per_assignment_extra_info aei,
hr_soft_coding_keyflex scl,
pay_all_payrolls_f ppf,
hr_locations hrl,
hr_organization_information hoi,
hr_all_organization_units hou,
fnd_common_lookups fcl,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = substr(P_PAYROLL_ACTION_ID, 28)
and ppa.action_type = 'X'
and ppa.action_status = 'C'
and ppa.report_type = 'ZA_IRP5'
and paa.payroll_action_id = ppa.payroll_action_id
and nvl(substr(paa.serial_number, 1, 1), '1') in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0', '&')
and ass.assignment_id = paa.assignment_id
and (ass.assignment_id = P_EMPLOYEE or P_EMPLOYEE is null)
and ass.effective_start_date =
(
select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.effective_start_date <= ppa.effective_date
and paf2.assignment_id = paa.assignment_id
)
and per.person_id = ass.person_id
and per.effective_start_date =
(
select max(per2.effective_start_date)
from per_people_f per2
where per2.effective_start_date <= ppa.effective_date
and per2.person_id = ass.person_id
)
and ppf.payroll_id = ass.payroll_id
and ppf.effective_start_date =
(
select max(ppf2.effective_start_date)
from pay_all_payrolls_f ppf2
where ppf2.effective_start_date <= ppa.effective_date
and ppf2.payroll_id = ass.payroll_id
)
and scl.soft_coding_keyflex_id (+) = ppf.soft_coding_keyflex_id
and per.person_id = adr.person_id (+)
and adr.style (+) = 'ZA'
and adr.primary_flag (+) = 'Y'
and org.organization_id (+) = ass.organization_id
and ass.assignment_id = aei.assignment_id (+)
and aei.aei_information7 = hou.organization_id
and hou.organization_id = hoi.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hrl.location_id (+) = hou.location_id
and hrl.style (+) = 'ZA'
and fcl.lookup_type (+) = 'ZA_PER_NATURES'
and fcl.lookup_code (+) = aei.aei_information4
and fcl.application_id (+) = 800;
select pay_za_irp5_temp_s.nextval
into l_irp5_id
from dual;
insert into pay_za_irp5_temp
values
(
l_irp5_id,
v_main.employee_number,
v_main.ass_action_id,
v_main.cf_eff_date,
v_main.national_identifier,
v_main.per_information1,
v_main.per_information2,
v_main.last_name,
v_main.first_name,
v_main.assignment_number,
v_main.first_names,
v_main.date_of_birth,
v_main.initials,
v_main.middle_names,
v_main.address_line1,
v_main.address_line2,
v_main.address_line3,
v_main.town_or_city,
v_main.postal_code,
v_main.assignment_id,
v_main.location_id,
v_main.payroll_id,
v_main.effective_start_date,
v_main.effective_end_date,
v_main.aei_information4,
v_main.aei_information7,
v_main.aei_information6,
v_main.aei_information3,
v_main.aei_information8,
v_main.aei_information2,
v_main.location_code,
v_main.address_line_1,
v_main.address_line_2,
v_main.address_line_3,
v_main.city,
v_main.postcode,
v_main.org_name,
v_main.organization_id,
v_main.org_information1,
v_main.org_information3,
v_main.org_information4,
v_main.nature,
v_main.serial_number
);
Select decode(count(*), 0 ,'Y', 'N')
into l_lump_sum_indicator
From pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch
where paa_arch.assignment_action_id = p_assignment_action_id
and ppa_arch.payroll_action_id = paa_arch.payroll_action_id
and paa_arch.assignment_action_id =
(
select max(paa.assignment_action_id)
from pay_assignment_actions paa
where paa.payroll_action_id = ppa_arch.payroll_action_id
and paa.assignment_id = paa_arch.assignment_id
) ;
select fnd_number.canonical_to_number(arc.value)
into l_site
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_SITE_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = p_assignment_action_id;
select fnd_number.canonical_to_number(arc.value)
into l_paye
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_PAYE_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = p_assignment_action_id;
select arc.value
into l_voluntary_tax
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_VOLUNTARY_TAX_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = p_assignment_action_id;
select sum(arc.value)
into l_total
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name IN ('A_TAX_ON_LUMP_SUMS_ASG_LMPSM_TAX_YTD',
'A_TAX_ON_RETIREMENT_FUND_LUMP_SUMS_ASG_LMPSM_TAX_YTD')
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = p_assignment_action_id;
select sum(trunc(to_number(arc.value))) value
into l_sum
from -- pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
where arc.context1 = p_assignment_action_id
and
(
arc.value is not null
or
(
arc.value is not null
and to_number(arc.value) <> 0
)
)
and dbi.user_entity_id = arc.user_entity_id
-- and irp5.user_name = dbi.user_name
and dbi.user_name in
(
'A_GROSS_REMUNERATION_ASG_TAX_YTD',
'A_GROSS_NON_TAXABLE_INCOME_ASG_TAX_YTD',
'A_ARREAR_PROVIDENT_FUND_ASG_TAX_YTD',
'A_ARREAR_RETIREMENT_ANNUITY_ASG_TAX_YTD',
'A_CURRENT_PROVIDENT_FUND_ASG_TAX_YTD',
'A_CURRENT_RETIREMENT_ANNUITY_ASG_TAX_YTD',
'A_MEDICAL_AID_CONTRIBUTION_ASG_TAX_YTD'
);
select sum(trunc(to_number(arc.value))) value
into l_lmpsm_sum
from -- pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
where arc.context1 in (select ch.assignment_action_id
from pay_assignment_actions main
, pay_assignment_actions ch
where main.assignment_action_id = p_assignment_action_id
and ch.payroll_action_id = main.payroll_action_id
and ch.assignment_action_id < main.assignment_action_id
AND ch.assignment_id = main.assignment_id)
and
(
arc.value is not null
or
(
arc.value is not null
and arc.value <> 0
)
)
and dbi.user_entity_id = arc.user_entity_id
-- and irp5.user_name = dbi.user_name
and dbi.user_name in
(
'A_EXECUTIVE_EQUITY_SHARES_NRFI_ASG_LMPSM_TAX_YTD'
,'A_EXECUTIVE_EQUITY_SHARES_RFI_ASG_LMPSM_TAX_YTD'
,'A_OTHER_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_OTHER_RETIREMENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RESIGNATION_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RESIGNATION_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_OR_RETRENCHMENT_GRATUITIES_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_SHARE_OPTIONS_EXERCISED_NRFI_ASG_LMPSM_TAX_YTD'
,'A_SHARE_OPTIONS_EXERCISED_RFI_ASG_LMPSM_TAX_YTD'
,'A_SPECIAL_REMUNERATION_ASG_LMPSM_TAX_YTD'
,'A_TAXABLE_ARBITRATION_AWARD_NRFI_ASG_LMPSM_TAX_YTD'
,'A_TAXABLE_ARBITRATION_AWARD_RFI_ASG_LMPSM_TAX_YTD'
,'A_SURPLUS_APPORTIONMENT_ASG_LMPSM_TAX_YTD'
,'A_UNCLAIMED_BENEFITS_ASG_LMPSM_TAX_YTD'
,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_RFI_ASG_LMPSM_TAX_YTD'
,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_NRFI_ASG_LMPSM_TAX_YTD'
);
select sum(trunc(to_number(arc.value))) value
into l_sum
from ff_archive_items arc,
ff_database_items dbi
where arc.context1 = p_assignment_action_id
and dbi.user_name IN (
'A_ANNUAL_PENSION_FUND_ASG_CLRNO_TAX_YTD'
,'A_CURRENT_PENSION_FUND_ASG_CLRNO_TAX_YTD'
,'A_ANNUAL_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
,'A_ARREAR_PENSION_FUND_ASG_CLRNO_TAX_YTD'
,'A_ANNUAL_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
,'A_CURRENT_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
,'A_ARREAR_PROVIDENT_FUND_ASG_CLRNO_TAX_YTD'
,'A_ANNUAL_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
,'A_CURRENT_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
,'A_ANNUAL_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
,'A_ARREAR_RETIREMENT_ANNUITY_ASG_CLRNO_TAX_YTD'
,'A_ANNUAL_EE_INCOME_PROTECTION_POLICY_CONTRIBUTIONS_ASG_TAX_YTD'
,'A_EE_INCOME_PROTECTION_POLICY_CONTRIBUTIONS_ASG_TAX_YTD'
,'A_MEDICAL_AID_CONTRIBUTION_ASG_TAX_YTD'
,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_RFI_ASG_TAX_YTD' -- added on 22-May-2007
,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_NRFI_ASG_TAX_YTD'
,'A_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD'
,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_RFI_ASG_TAX_YTD'
,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_NRFI_ASG_TAX_YTD'
,'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_EE_FAMILY_PKG_ASG_TAX_YTD'
,'A_MEDICAL_CONTRIBUTIONS_ABATEMENT_ASG_TAX_YTD'
,'A_ANNUAL_MEDICAL_CONTRIBUTIONS_ABATEMENT_ASG_TAX_YTD'
)
and dbi.user_entity_id = arc.user_entity_id;
select sum(trunc(to_number(arc.value))) value
into l_sum
from -- pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
where arc.context1 = p_assignment_action_id
and
(
arc.value is not null
or
(
arc.value is not null
and arc.value <> 0
)
)
and dbi.user_entity_id = arc.user_entity_id
-- and irp5.user_name = dbi.user_name
and dbi.user_name in
(
'A_EXECUTIVE_EQUITY_SHARES_NRFI_ASG_LMPSM_TAX_YTD'
,'A_EXECUTIVE_EQUITY_SHARES_RFI_ASG_LMPSM_TAX_YTD'
,'A_OTHER_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_OTHER_RETIREMENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RESIGNATION_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RESIGNATION_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_OR_RETRENCHMENT_GRATUITIES_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_PENSION_AND_RAF_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_RETIREMENT_PROVIDENT_LUMP_SUMS_ASG_LMPSM_TAX_YTD'
,'A_SHARE_OPTIONS_EXERCISED_NRFI_ASG_LMPSM_TAX_YTD'
,'A_SHARE_OPTIONS_EXERCISED_RFI_ASG_LMPSM_TAX_YTD'
,'A_SPECIAL_REMUNERATION_ASG_LMPSM_TAX_YTD'
,'A_TAXABLE_ARBITRATION_AWARD_NRFI_ASG_LMPSM_TAX_YTD'
,'A_TAXABLE_ARBITRATION_AWARD_RFI_ASG_LMPSM_TAX_YTD'
,'A_SURPLUS_APPORTIONMENT_ASG_LMPSM_TAX_YTD'
,'A_UNCLAIMED_BENEFITS_ASG_LMPSM_TAX_YTD'
,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_RFI_ASG_LMPSM_TAX_YTD'
,'A_RETIRE_PEN_RAF_PROV_FUND_BEN_ON_RET_OR_DEATH_NRFI_ASG_LMPSM_TAX_YTD'
);