[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into v_count
from pay_all_payrolls_f pap,
hr_soft_coding_keyflex scl
where pap.business_group_id = (select pap2.business_group_id from pay_all_payrolls_f pap2 where pap2.payroll_id=p_payroll_id and rownum=1)
and pap.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
and scl.segment8 = p_irp5no
and to_date(v_tax_end_date,'DD-MM-YYYY') between pap.effective_start_date and pap.effective_end_date;
select to_char(min(start_date),'dd-mon-yyyy'),to_char(max(end_date),'dd-mon-yyyy')
into p_tax_start_date,p_tax_end_date
from per_time_periods
where payroll_id = p_payroll_id
and prd_information1 = p_tax_year;
/* Cursor to select all Income Sars Codes which have negative balances*/
g_default_clrno CONSTANT VARCHAR2(11) := '99999999999' ;
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
WHERE dbi.user_entity_id = arc.user_entity_id
and irp5.user_name = dbi.user_name
AND arc.context1 = p_asgn_action_id
and (( irp5.balance_sequence = 1 and
(
(irp5.code BETWEEN 3601 AND 3607)
OR
(irp5.code BETWEEN 3609 AND 3613)
or
(irp5.code BETWEEN 3615 AND 3617 ) -- 3608 and 3614 are LMPSM balance
or
(irp5.code BETWEEN 3651 AND 3667)
OR
(irp5.code BETWEEN 3701 AND 3706)
OR
(irp5.code BETWEEN 3708 AND 3717) -- 3707 and 3718 are LMPSM balances
OR
(irp5.code BETWEEN 3751 AND 3768)
or
(irp5.code BETWEEN 3801 and 3810)
or
(irp5.code BETWEEN 3851 and 3860)
or
(irp5.code BETWEEN 3813 and 3863)
OR -- 3901 to 3907 are LMPSM balances
(irp5.code = 3908)
OR
(irp5.code BETWEEN 3951 and 3957)
or
(irp5.code BETWEEN 3695 and 3699)
OR -- 4001 to 4004, 4006, 4007 are Deduction balances
(irp5.code = 4005 )
or
(irp5.code = 4018)
or
(irp5.code BETWEEN 4024 and 4025)
or
(irp5.code BETWEEN 4101 and 4103)
or
(irp5.code BETWEEN 4472 and 4474)
or
(irp5.code BETWEEN 4485 and 4487)
or
(irp5.code = 4493)
)
)
OR
( irp5.code = 4005 AND irp5.balance_sequence = 2)) --Added for Bug 8213478
group by irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) < 0 ;
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE irp5.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3909, 3915, 3920)
AND irp5.balance_sequence = 3
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_TEXT'
AND faic2.CONTEXT = 'To Be Advised'
group by irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0 ;
select sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) value
, faic2.CONTEXT Tax_Directive_Number
, irp5.code code
from pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
where arc.context1 in (select ch.assignment_action_id
from pay_assignment_actions main
, pay_assignment_actions ch
where main.assignment_action_id = p_asg_act_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.code IN (3608, 3614, 3707, 3718, 3901, 3902, 3903, 3904, 3905, 3906, 3907, 3909, 3915, 3920)
AND irp5.balance_sequence = 3
AND dbi.user_name = irp5.user_name
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_TEXT'
group BY faic2.CONTEXT
, irp5.code
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(arc.value)))) < 0;
select irp5.code ,
faic2.CONTEXT clearance_num,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE irp5.code IN (4001, 4002, 4003, 4004, 4006, 4007)
AND irp5.balance_sequence = 1
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
AND faic2.archive_item_id = arc.archive_item_id
AND ffc2.context_id = faic2.context_id
AND ffc2.context_name = 'SOURCE_NUMBER'
group by irp5.code,
faic2.CONTEXT
HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0)
OR (faic2.CONTEXT = g_default_clrno
and sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) <> 0)
--added above condition for Bug 7214056
UNION /*Added for Bug 8406456 to report negative amount in code 4030. This deduction doesnt have clearance number */
select irp5.code ,
'11111111111' clearance_num,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
WHERE irp5.code IN (4030)
AND irp5.balance_sequence = 1
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
group by irp5.code
HAVING (sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))))<0);
select irp5.code,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi
WHERE ((irp5.code IN (3810, 3813, 4005, 4024, 4025, 4474, 4485, 4486, 4493, 4030) --Added 4474 and 4493 for TYE09
AND
irp5.balance_sequence = 1
)
OR -- Added for Bug 8213478
( irp5.code = 4005 AND irp5.balance_sequence = 2
)
)
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND arc.context1 = p_asgn_action_id
group by irp5.code;
SELECT assignment_id,
max(assignment_action_id) assignment_action_id -- max assignment_action_id relates to Main Certificate
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
GROUP BY assignment_id ;
select paa.assignment_action_id
from pay_action_contexts pac,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
where paa.assignment_id = p_asgn_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q','B')
AND pac.assignment_Action_id = paa.assignment_action_id
And pac.context_value = g_default_clrno
and ffc.context_name = 'SOURCE_NUMBER'
and ffc.context_id = pac.context_id
and ppa.effective_date >= p_start_date
and ppa.effective_date <= p_end_date;
Select element_name
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prv
Where paa.assignment_action_id = p_asgn_ac_id
and prr.assignment_Action_id = paa.assignment_action_id
and pet.element_type_id = prr.element_type_id
and piv.element_type_id = pet.element_type_id
and piv.name = 'Clearance Number'
and prv.run_result_id = prr.run_result_id
and prv.input_value_id = piv.input_value_id
and prv.RESULT_VALUE = g_default_clrno
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date ;
select irp5.code,
irp5.full_balance_name bal_name,
irp5.balance_type_id bal_type_id,
trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)))) value
FROM pay_za_irp5_bal_codes irp5,
ff_archive_items arc,
ff_database_items dbi,
per_assignment_extra_info paei,
pay_assignment_actions paa
WHERE dbi.user_name in
(
'A_ANNUAL_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
'A_ASSET_PURCHASED_AT_REDUCED_VALUE_PKG_ASG_TAX_YTD',
'A_USE_OF_MOTOR_VEHICLE_PKG_ASG_TAX_YTD',
'A_RIGHT_OF_USE_OF_ASSET_PKG_ASG_TAX_YTD',
'A_MEALS_REFRESHMENTS_AND_VOUCHERS_PKG_ASG_TAX_YTD',
'A_FREE_OR_CHEAP_ACCOMMODATION_PKG_ASG_TAX_YTD',
'A_FREE_OR_CHEAP_SERVICES_PKG_ASG_TAX_YTD',
'A_LOW_OR_INTEREST_FREE_LOANS_PKG_ASG_TAX_YTD',
'A_ANNUAL_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
'A_PAYMENT_OF_EMPLOYEE_DEBT_PKG_ASG_TAX_YTD',
'A_ANNUAL_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
'A_BURSARIES_AND_SCHOLARSHIPS_PKG_ASG_TAX_YTD',
'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_ASG_TAX_YTD',
'A_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_EE_FAMILY_PKG_ASG_TAX_YTD',
'A_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_PD_BY_ER_IRO_OTHER_PKG_ASG_TAX_YTD',
'A_TAXABLE_INCOME_PKG_ASG_TAX_YTD',
'A_TAXABLE_PENSION_PKG_ASG_TAX_YTD',
'A_ANNUAL_BONUS_PKG_ASG_TAX_YTD',
'A_TAXABLE_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
'A_ANNUAL_COMMISSION_PKG_ASG_TAX_YTD',
'A_COMMISSION_PKG_ASG_TAX_YTD',
'A_ANNUAL_OVERTIME_PKG_ASG_TAX_YTD',
'A_OVERTIME_PKG_ASG_TAX_YTD',
'A_ANNUITY_FROM_RETIREMENT_FUND_PKG_ASG_TAX_YTD',
'A_PURCHASED_ANNUITY_TAXABLE_PKG_ASG_TAX_YTD',
'A_ANNUAL_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
'A_RESTRAINT_OF_TRADE_PKG_ASG_TAX_YTD',
'A_ANNUAL_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
'A_INDEPENDENT_CONTRACTOR_PAYMENTS_PKG_ASG_TAX_YTD',
'A_ANNUAL_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
'A_LABOUR_BROKER_PAYMENTS_PKG_ASG_TAX_YTD',
'A_TRAVEL_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TAXABLE_REIMBURSIVE_TRAVEL_PKG_ASG_TAX_YTD',
'A_TAXABLE_SUBSISTENCE_PKG_ASG_TAX_YTD',
'A_ENTERTAINMENT_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_PUBLIC_OFFICE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TOOL_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_COMPUTER_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TELEPHONE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_OTHER_TAXABLE_ALLOWANCE_PKG_ASG_TAX_YTD',
'A_TAXABLE_SUBSISTENCE_ALLOWANCE_FOREIGN_TRAVEL_PKG_ASG_TAX_YTD',
'A_EE_BROADBASED_SHARE_PLAN_PKG_ASG_TAX_YTD',
'A_OTHER_LUMP_SUM_TAXED_AS_ANNUAL_PAYMENT_PKG_ASG_TAX_YTD',
'A_MEDICAL_AID_PAID_ON_BEHALF_OF_EMPLOYEE_PKG_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_PKG_ASG_TAX_YTD',
'A_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD',
'A_ANNUAL_MED_COSTS_DMD_PD_BY_EE_OTHER_PKG_ASG_TAX_YTD'
)
AND irp5.user_name = dbi.user_name
AND dbi.user_entity_id = arc.user_entity_id
AND paei.assignment_id = paa.assignment_id
AND arc.context1 = p_asgn_action_id
AND arc.context1 = paa.assignment_action_id
AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis: Fixed Percentage of Specific Income
AND paei.information_type = 'ZA_SPECIFIC_INFO'
AND pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0)) <> '0';
SELECT ELEM.element_name element_name,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
from pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, per_time_periods PPTP
, per_time_periods BPTP
, pay_payroll_actions PACT
, pay_assignment_actions ASSACT
, pay_payroll_actions BACT
, pay_assignment_actions BAL_ASSACT
, pay_element_types_f ELEM
where BAL_ASSACT.assignment_action_id = p_asgn_action_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
+ decode(PPTP.year_number, 0, 0, 0)
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and BPTP.payroll_id = BACT.payroll_id
and PPTP.payroll_id = PACT.payroll_id
and nvl(BACT.date_earned,BACT.effective_date)
between BPTP.start_date and BPTP.end_date
and PACT.date_earned between PPTP.start_date and PPTP.end_date
and RR.status in ('P','PA')
AND ELEM.element_type_id = RR.element_type_id
and PPTP.prd_information1 = BPTP.prd_information1
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
AND feed.BALANCE_TYPE_ID = p_bal_typ_id
GROUP BY ELEM.element_name
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
SELECT per.employee_number empno, asgn2.assignment_number assgno,
add_months(per.date_of_birth,780) dateb--Added for TYE09
FROM pay_assignment_actions paa,
per_all_assignments_f asgn2,
per_all_people_f per,
pay_payroll_actions ppa
WHERE paa.assignment_action_id = asgn_ac_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND asgn2.assignment_id = paa.assignment_id
AND per.person_id = asgn2.person_id
AND asgn2.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 = asgn2.assignment_id
)
AND per.effective_start_date =
( select max(per2.effective_start_date)
from per_all_people_f per2
where per2.effective_start_date <= ppa.effective_date
and per2.person_id = per.person_id
);
SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
faic2.CONTEXT Tax_Directive_Number
FROM ff_archive_items arc,
ff_database_items dbi,
pay_za_irp5_bal_codes irp5,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE arc.user_entity_id = dbi.user_entity_id
and irp5.user_name = dbi.user_name
and arc.context1 in (select ch.assignment_action_id
from pay_assignment_actions main
, pay_assignment_actions ch
where main.assignment_action_id = p_asgn_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 irp5.code in (3915,4115,3920) --Modified for TYS2010 Bug 8406456
and faic2.archive_item_id = arc.archive_item_id
and ffc2.context_id = faic2.context_id
and ffc2.context_name = 'SOURCE_TEXT'
GROUP BY irp5.code,faic2.CONTEXT ;
SELECT irp5.code, sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))))) value,
faic2.CONTEXT Tax_Directive_Number
FROM ff_archive_items arc,
ff_database_items dbi,
pay_za_irp5_bal_codes irp5,
ff_archive_item_contexts faic2,
ff_contexts ffc2
WHERE arc.user_entity_id = dbi.user_entity_id
and irp5.user_name = dbi.user_name
and arc.context1 in (select ch.assignment_action_id
from pay_assignment_actions main
, pay_assignment_actions ch
where main.assignment_action_id = p_asgn_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 irp5.code in (3902,3904,3920)
and irp5.balance_sequence = 3
and faic2.archive_item_id = arc.archive_item_id
and ffc2.context_id = faic2.context_id
and ffc2.context_name = 'SOURCE_TEXT'
GROUP BY irp5.code,faic2.CONTEXT ;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
into l_tax_ytd
from ff_archive_items arc,
ff_database_items dbi
where dbi.user_name = 'A_TAX_ASG_TAX_YTD'
and arc.user_entity_id = dbi.user_entity_id
and arc.context1 = asgn.assignment_action_id;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
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 = asgn.assignment_action_id;
select pay_za_eoy_val.decimal_character_conversion(nvl(arc.value,0))
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 = asgn.assignment_action_id;
retiremnt_fund_lmpsum.delete();
SELECT tag
FROM fnd_lookup_values
WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
INSTR(USERENV('LANGUAGE'), '.') + 1)
AND language = 'US';
SELECT include_or_exclude
FROM hr_assignment_set_amendments hasa
WHERE hasa.assignment_set_id = c_asg_set_id;
select name
into l_legal_entity
from hr_all_organization_units
where organization_id=P_LEGAL_ENTITY;
select meaning
into l_cert_type
from hr_lookups
where lookup_type='ZA_TAX_CERTIFICATES' and lookup_code=P_CERT_TYPE;
select ppf.payroll_name
into l_payroll_name
from pay_payrolls_f ppf
where ppf.payroll_id=P_PAYROLL_ID
and ppf.effective_start_date =
(select max(effective_start_date)
from pay_all_payrolls_f ppf1
where ppf1.payroll_id=ppf.payroll_id
and P_TAX_YEAR_END between ppf1.effective_start_date and ppf1.effective_end_date);
select assignment_set_name into l_ass_set_name
from hr_assignment_sets
where assignment_set_id=P_ASG_SET_ID;
g_asg_set_where := ' AND ass.assignment_id NOT IN ( SELECT hasa.assignment_id FROM hr_assignment_set_amendments hasa WHERE hasa.assignment_set_id = ' || P_ASG_SET_ID || ' AND hasa.assignment_id = ass.assignment_id) ';
g_asg_set_where := ' AND ass.assignment_id IN ( SELECT hasa.assignment_id FROM hr_assignment_set_amendments hasa WHERE hasa.assignment_set_id = ' || P_ASG_SET_ID || ' AND hasa.assignment_id = ass.assignment_id) ';
ELSE -- Select all assignments assigned to the payroll associated with this assignment set
g_asg_set_where := ' AND 1= 1 ';
select substr(per.full_name,1,40)
into l_person_name
from per_all_people_f per
where person_id=P_PERSON_ID
and per.effective_start_date = ( select max(effective_start_date) from per_all_people_f per1
where per.person_id=per1.person_id
and P_TAX_YEAR_END between per1.effective_start_date and per1.effective_end_date);
select meaning
into l_test_run
from fnd_lookups
where lookup_type='YES_NO' and lookup_code=P_TEST_RUN;
select hoi.org_information1 er_trade_name, -- Employer Trading or Other Name (Code 2010)
hoi.org_information3 paye_ref_num, -- PAYE Ref Num (Code 2020)
pay_za_eoy_val.modulus_10_test(hoi.org_information3) paye_ref_num_mod,
upper(hoi.org_information12) sdl_ref_num, -- SDL Num (Code 2022)
upper(hoi.org_information6) uif_ref_num, -- UIF Ref Num (Code 2024)
hoi.org_information13 er_trade_class -- Employer Trade Classification (Code 2035)
from hr_organization_information hoi
where hoi.organization_id = p_legal_entity
and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
select hoi.org_information1 er_contact_person, -- code 2025
hoi.org_information2 er_contact_number, -- code 2026
hoi.org_information3 er_email_address, -- code 2027
hoi.org_information4 er_unit_num, -- code 2061
hoi.org_information5 er_complex, -- code 2062
hoi.org_information6 er_street_num, -- code 2063
hoi.org_information7 er_street_name_farm, -- code 2063
hoi.org_information8 er_suburb_district, -- code 2063
hoi.org_information9 er_town_city, -- code 2063
hoi.org_information10 er_postal_code -- code 2063
from hr_organization_information hoi
where hoi.organization_id = p_legal_entity
and hoi.org_information_context = 'ZA_GRE_TAX_FILE_ENTITY';
l_er_msg_tab.delete;
select translate(upper(phone_number),
'0123456789+-. ',
'0123456789') -- remove any character other than digits
from per_phones
where parent_table = 'PER_ALL_PEOPLE_F'
and parent_id = p_person_id
and phone_type = p_phone_type
and p_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY')) ;
select pea.external_account_id ext_acc_id,
pea.segment3 sars_acc_no,
-- pea.segment2 account_type,
-- pea.segment1 branch_code,
pea.segment4 acc_holder_name,
pea.segment6 acc_holder_reln,
p_payment_type account_type
from pay_external_accounts pea,
pay_personal_payment_methods_f ppm
where ppm.assignment_id = P_ASG_ID
and ppm.personal_payment_method_id = P_PAY_METHOD_ID
and ppm.external_account_id = pea.external_account_id
and p_effective_date between ppm.effective_start_date and ppm.effective_end_date;
select pea.external_account_id ext_acc_id,
pea.segment3 sars_acc_no,
pea.segment4 acc_holder_name,
pea.segment6 acc_holder_reln,
ppm.ppm_information1 account_type
from pay_personal_payment_methods_f ppm,
pay_external_accounts pea
where ppm.assignment_id = P_ASG_ID
and ppm.external_account_id = pea.external_account_id(+)
and ppm.ppm_information_category in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
and ppm.ppm_information1 in ('Y','0','7')
and p_effective_date between ppm.effective_start_date and ppm.effective_end_date;
select count(*)
into l_count
from pay_personal_payment_methods_f
where assignment_id = P_ASG_ID
and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
and ppm_information1 in ('Y','0','7')
and p_effective_date between effective_start_date and effective_end_date;
select address_line1 ee_unit_num
, address_line2 ee_complex
, address_line3 ee_street_num
, region_1 ee_street_name
, region_2 ee_suburb_district
, town_or_city ee_town_city
, postal_code ee_postal_code
from per_addresses
where person_id = p_person_id
and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
and style = p_address_style
and address_type = p_address_type;
select lei_information1 ee_unit_num
, lei_information2 ee_complex
, lei_information3 ee_street_num
, lei_information4 ee_street_name
, lei_information5 ee_suburb_district
, lei_information6 ee_town_city
, lei_information7 ee_postal_code
from hr_location_extra_info
where location_id = p_location_id
and information_type ='ZA_SARS_ADDRESS';
select nvl(region_2,'N') ee_indicator -- Postal Address same as residential address flag
, decode(region_2,'Y',null,address_line1) ee_add_line1 -- if flag = Y, then don't populate remaining postal address fields
, decode(region_2,'Y',null,address_line2) ee_add_line2
, decode(region_2,'Y',null,address_line3) ee_add_line3
, decode(region_2,'Y',null,postal_code) ee_postal_code
from per_addresses
where person_id = p_person_id
and l_effective_date between date_from and nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'))
and style = 'ZA'
and primary_flag = 'Y';
l_miss_LE_tab.delete;
l_sql :='select ass.assignment_id ,
ass.person_id ,
per.employee_number ,
per.last_name || '' ,'' || initcap(per.title) || '' '' || per.first_name ,
ass.assignment_number ,
per.last_name ,
per.first_name ,
per.middle_names ,
per.national_identifier ,
per.per_information2 ,
per.per_information10 ,
pay_za_eoy_val.check_id_dob(per.national_identifier,per.date_of_birth,''Y'') ,
per.per_information1 ,
pay_za_eoy_val.modulus_10_test(per.per_information1) ,
per.email_address ,
aei.aei_information2 ,
aei.aei_information3 ,
hr_general.decode_lookup(''ZA_PER_NATURES'',aei.aei_information4) ,
aei.aei_information7 ,
aei.aei_information13 ,
aei.aei_information14 ,
nvl(aei.aei_information7,''-1''),
ass.location_id,
ass.position_id,
ass.organization_id,
(select pap.location_id
from per_all_positions pap
where pap.position_id = ass.position_id) pos_location_id,
(select haou.location_id
from hr_all_organization_units haou
where haou.organization_id=ass.organization_id) org_location_id
from
per_assignments_f ass,
per_all_people_f per,
per_assignment_extra_info aei
where
ass.payroll_id = nvl(:1,ass.payroll_id)
and ass.business_group_id=:2
and aei.assignment_id(+)=ass.assignment_id
and (aei.information_type(+)=''ZA_SPECIFIC_INFO'' )
and nvl(aei.aei_information7,:3) = :4
and ( :5 = ''1''
OR (:6 =''2'' and aei.aei_information4 in (''01'',''02'',''03'',''11''))
)
and exists (select 1
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp
where
ppa.payroll_id=ass.payroll_id
and paa.assignment_id=ass.assignment_id
and ptp.payroll_id = ppa.payroll_id
and ptp.prd_information1 = :7
and ptp.end_date <= decode(:8,''02'', ptp.end_date, :9)
and paa.payroll_action_id=ppa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id
and ppa.action_type in (''R'', ''Q'', ''V'', ''B'', ''I'')
and paa.action_status in (''C'',''S'') --10376999
)
and (:10 between ass.effective_start_date and ass.effective_end_date
OR
(ass.effective_end_date <=:11
and ass.effective_end_date = ( select max(ass1.effective_end_date)
from per_assignments_f ass1
where ass.assignment_id = ass1.assignment_id
)))
and per.person_id = ass.person_id
and :12 between per.effective_start_date and per.effective_end_date
and per.person_id=nvl(:13,per.person_id)
and per.per_information_category=''ZA'''||g_asg_set_where||g_sort_order_clause;
select last_day(decode(P_PERIOD_RECON, '02', to_date(P_TAX_YEAR||'-02-01','yyyy-mm-dd'), '08', to_date(P_TAX_YEAR-1 ||'-08-01','yyyy-mm-dd')))
into l_period_recon_last_date
from dual;
l_ee_msg_tab.delete;
l_ee_warn_tab.delete;
select least(max(paaf.effective_end_date),p_tax_year_end)
into l_effective_date
from per_all_assignments_f paaf
where paaf.effective_start_date <= p_tax_year_end
and paaf.assignment_id = l_assignment_id;
select distinct payroll_id
from pay_all_payrolls_f papf
where business_group_id = P_BUSINESS_GROUP_ID
and exists ( select ''
from per_time_periods ptp2
where ptp2.prd_information1=l_tax_year
and ptp2.payroll_id = papf.payroll_id
);
g_xml_element_table.DELETE;
SELECT assignment_id, assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
ORDER BY assignment_id ;
SELECT per.employee_number empno, asgn2.assignment_number assgno
FROM pay_assignment_actions paa,
per_all_assignments_f asgn2,
per_all_people_f per
WHERE paa.assignment_action_id = asgn_ac_id
AND asgn2.assignment_id = paa.assignment_id
AND per.person_id = asgn2.person_id
AND asgn2.effective_start_date =
( select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.effective_start_date <= ( select max(ptp.end_date)
from per_time_periods ptp
where ptp.prd_information1 = p_tax_year
and ptp.payroll_id = asgn2.payroll_id)
and paf2.assignment_id = asgn2.assignment_id
)
AND asgn2.effective_start_date between per.effective_start_date and per.effective_end_date;
select nvl(pai.action_information3,0) SITE,
nvl(pai.action_information11,0) PAYE,
nvl(pai.action_information10,0) TAX
from pay_action_information pai
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
and pai.action_information30 = cert_num;
select count(1)
from pay_action_information pai
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and ( pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
OR
pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
)
and pai.action_information30 = cert_num
and pai.action_information3 is null
and pai.action_information2 <> '3907'
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
select 1
from pay_action_information pai
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and ( pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
OR
pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
)
and pai.action_information30 = cert_num
and pai.action_information3 is null
and pai.action_information2 = '3907'
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
select count(1)
from pay_action_information pai
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
and pai.action_information30 = cert_num
and pai.action_information3 is null
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0;
select pai.action_information30 temp_cert_num
,pai2.action_information2 cert_type --IRP5/IT3A/ITREG
from pay_action_information pai
,pay_action_information pai2
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_CONTACT_INFO'
and pai2.action_information_category ='ZATYE_EMPLOYEE_INFO'
and pai2.action_context_id=pai.action_context_id
and pai2.action_context_type=pai.action_context_type
and pai.action_information26='MAIN'
and pai2.action_information30=pai.action_information30;
select pai.action_information2 code,
trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
FROM pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
and pai.action_information30 = p_cert_num
and pai.action_information3 is null --code included in
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
order by pai.action_information2;
select pai.action_information2 code,
'To Be Advised' to_be_adv,
nvl(pai.action_information4,0) to_be_adv_val, --To Be Advised value
pai.action_information7 direct1,
nvl(pai.action_information8,0) value1,
pai.action_information9 direct2,
nvl(pai.action_information10,0) value2,
pai.action_information11 direct3,
nvl(pai.action_information12,0) value3
FROM pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
and pai.action_information30 = p_cert_num
and pai.action_information3 is null --code included in
and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
)
order by pai.action_information2;
select pai.action_information2 code,
pai.action_information5 value,
pai2.action_information18 direct1
FROM pay_action_information pai,
pay_action_information pai2
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
and pai2.action_information_category ='ZATYE_EMPLOYEE_INFO'
and pai2.action_context_id = pai.action_context_id
and pai2.action_context_type = pai.action_context_type
and pai.action_information30 = pai2.action_information30
and pai.action_information30 <> p_cert_num
and pai.action_information3 is null --code included in
and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
)
order by pai.action_information2;
select pai.action_information2 code,
trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value --code_group_value
FROM pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
and pai.action_information30 = p_cert_num
and pai.action_information3 is null --code included in
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
order by pai.action_information2;
SELECT pai.action_information2 PAYE_REF_NUM
FROM pay_action_information pai
WHERE pai.action_context_id = p_payroll_action_id
AND pai.action_context_type = 'PA'
AND pai.action_information_category = 'ZATYE_EMPLOYER_INFO';
select '1'
from pay_action_information pai
where pai.action_context_id = asgn_ac_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
and ( trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information10,0)))) <> 0
OR
trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
)
;
select '1' flag
FROM pay_action_information pai,
per_assignment_extra_info paei
where pai.action_context_id = p_asgn_action_id
and paei.assignment_id = pai.assignment_id
AND paei.AEI_INFORMATION8 <> '1' -- 'Pension Basis:1 is Fixed Percentage of Specific Income
AND paei.information_type = 'ZA_SPECIFIC_INFO'
and pai.action_information_category = 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS'
and pai.action_information30 = p_cert_num
AND pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)) <> '0'; --Gross PKG
SELECT ELEM.element_name element_name,
sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0)))))
from pay_balance_feeds_f FEED
, pay_run_result_values TARGET
, pay_run_results RR
, per_time_periods PPTP
, per_time_periods BPTP
, pay_payroll_actions PACT
, pay_assignment_actions ASSACT
, pay_payroll_actions BACT
, pay_assignment_actions BAL_ASSACT
, pay_element_types_f ELEM
, pay_balance_types PBT
where BAL_ASSACT.assignment_action_id = p_asgn_action_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
+ decode(PPTP.year_number, 0, 0, 0)
and ASSACT.payroll_action_id = PACT.payroll_action_id
and PACT.effective_date between FEED.effective_start_date
and FEED.effective_end_date
and BPTP.payroll_id = BACT.payroll_id
and PPTP.payroll_id = PACT.payroll_id
and nvl(BACT.date_earned,BACT.effective_date)
between BPTP.start_date and BPTP.end_date
and PACT.date_earned between PPTP.start_date and PPTP.end_date
and RR.status in ('P','PA')
AND ELEM.element_type_id = RR.element_type_id
and PPTP.prd_information1 = BPTP.prd_information1
and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
and ASSACT.assignment_id = BAL_ASSACT.assignment_id
AND feed.BALANCE_TYPE_ID = PBT.balance_type_id
AND PBT.balance_name in ('Taxable Package Components',
'Annual Taxable Package Components'
)
GROUP BY ELEM.element_name
HAVING sum(trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(TARGET.RESULT_VALUE,0))))) <> 0;
select pai.action_information5,
pai.action_information30 temp_cert_num,
pai2.action_information18 direct1 --Directive1
FROM pay_action_information pai,
pay_action_information pai2
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and pai.action_context_id = pai2.action_context_id
and pai.action_context_type = pai2.action_context_type
and pai.action_information30 = pai2.action_information30
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
order by pai.action_information30;
select pai.action_information2 code, --either 3901, 3920, 3921, 3915 (added 3901 for bug 11899934)
pai.action_information30 temp_cert_num
FROM pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
and pai.action_information30 = p_cert_num
and pai.action_information3 is null --code included in
and to_number(pai.action_information2) in (3901,3915,3920,3921) -- (added 3901 for bug 11899934)
and trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) <> 0
order by pai.action_information2;*/
select to_number(pai.action_information2) code --income code
,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--income value
,pai.action_information30 temp_cert_num
from pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type='AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_INCOME'
and pai.action_information3 is null
and to_number(pai.action_information2) in (3810,3813,3860,3863)
and pai.action_information30=p_cert_num
order by pai.action_information30;
select to_number(pai.action_information2) code --deduction code
,trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) value--deduction value
,pai.action_information30 temp_cert_num
from pay_action_information pai
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type='AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
and pai.action_information3 is null
and to_number(pai.action_information2) in (4005,4024,4474,4493)
and pai.action_information30=p_cert_num
order by pai.action_information30;
select pai.action_information30 temp_cert_num,
pai2.action_information18 direct1, --Directive1
nvl(pai.action_information3,0) site,
nvl(pai.action_information4,0) paye,
nvl(pai.action_information5,0) tax_ret,
nvl(pai.action_information6,0) uif,
nvl(pai.action_information7,0) sdl,
nvl(pai.action_information8,0) total
FROM pay_action_information pai,
pay_action_information pai2
where pai.action_context_id = p_asgn_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
-- Fix for bug#14128085
--and pai.action_information3 is null --code included in
and pai2.action_information2 in ('IRP5','IT3(a)')
and pai2.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and pai2.action_context_id = pai.action_context_id
and pai.action_context_type = pai2.action_context_type
and pai.action_information30 = pai2.action_information30
and (trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information3,0)))) < 0
OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information4,0)))) < 0
OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information5,0)))) < 0
OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information6,0)))) < 0
OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information7,0)))) < 0
OR trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai.action_information8,0)))) < 0
)
order by pai.action_information2;
SELECT nvl(ACTION_INFORMATION2, '0') Non_Taxable_Income,
nvl(ACTION_INFORMATION3, '0') Gross_Retire_Fund_Income,
nvl(ACTION_INFORMATION4, '0') Gross_Non_Retire_Fund_In
FROM pay_action_information
WHERE action_context_id = p_asgn_action_id
AND action_information_category = 'ZATYE_EMPLOYEE_GROSS_REMUNERATIONS' ;
SELECT pai2.action_information14
FROM pay_action_information pai
, pay_action_information pai2
WHERE pai.action_context_id = p_asgn_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
AND pai.action_information2 in ('IRP5','IT3(a)')
AND pai2.action_context_id = pai.action_context_id
AND pai2.action_context_type = pai.action_context_type
AND pai2.action_information30 = pai.action_information30
AND pai2.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
AND pai2.action_information14 is not null;
SELECT 'X'
FROM pay_action_information pai
, pay_action_information pai2
WHERE pai.action_context_id = p_asgn_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
AND pai.action_information2 in ('IRP5','IT3(a)')
AND pai.action_information3 not in ('A','B','C','N')
AND pai2.action_context_id = pai.action_context_id
AND pai2.action_context_type = pai.action_context_type
AND pai2.action_information30 = pai.action_information30
AND pai2.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
AND pai2.action_information13 is not null;
SELECT decode (sign (add_months (to_date (pai.action_information11, 'YYYYMMDD'), 780) - to_date (pai.action_information4 || '-03-01', 'yyyy-mm-dd')-1), 1
, 'B', 'A') age
, pai3.action_information13
FROM pay_action_information pai
, pay_action_information pai2
, pay_action_information pai3
WHERE pai.action_context_id = p_asgn_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
AND pai.action_information2 in ('IRP5','IT3(a)')
AND pai2.action_context_id = pai.action_context_id
AND pai2.action_context_type = pai.action_context_type
AND pai2.action_information30 = pai.action_information30
AND pai2.action_information_category = 'ZATYE_EMPLOYEE_DEDUCTIONS'
AND pai2.action_information2 = '4005'
AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
AND pai3.action_context_id = pai.action_context_id
AND pai3.action_context_type = pai.action_context_type
AND pai3.action_information30 = pai.action_information30
AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS';
SELECT 'X'
FROM pay_action_information pai
, pay_action_information pai2
, pay_action_information pai3
WHERE pai.action_context_id = p_asgn_action_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
AND pai.action_information2 in ('IRP5','IT3(a)')
AND pai2.action_context_id = pai.action_context_id
AND pai2.action_context_type = pai.action_context_type
AND pai2.action_information30 = pai.action_information30
AND pai2.action_information_category = 'ZATYE_EMPLOYEE_LUMPSUMS'
AND pai2.action_information2 in ('3901','3915','3920','3921','3922')
AND trunc(to_number(pay_za_eoy_val.decimal_character_conversion(nvl(pai2.action_information5,0)))) <> 0
AND pai3.action_context_id = pai2.action_context_id
AND pai3.action_context_type = pai2.action_context_type
AND pai3.action_information30 = pai2.action_information30
AND pai3.action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
AND pai3.action_information9 is null
AND pai3.action_information5 is null;
select legislative_parameters
into l_leg_param
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
select max(paa.action_sequence)
into l_run_action_seq
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = rec_asgn.assignment_id
and paa.action_status IN ('C','S') -- 10376999
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and ppa.action_status = 'C'
and ppa.time_period_id = ptp.time_period_id
and ptp.prd_information1 = p_tax_year;
select assignment_action_id
into l_run_ass_act_id
from pay_assignment_actions
where assignment_id = rec_asgn.assignment_id
and action_sequence = l_run_action_seq;
cross_val_t.delete;