[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure insert_archive_row(
p_assactid in number
, p_tab_rec_data in action_info_table
) ;
2) This returns the select statement that is used to create the
range rows.
Arguments :
Notes : The range cursor determines which people should be processed.
The normal practice is to include everyone, and then limit
the list during the assignment action creation.
--------------------------------------------------------------------------*/
procedure range_cursor (pactid in number,
sqlstr out nocopy varchar2) as
-- Get 'ZA Tax Information' (Context ZA_LEGAL_ENTITY)
cursor csr_tax_info (p_legal_entity_org hr_all_organization_units.organization_id%type)is
select substr(hoi.org_information1, 1, 90) er_trade_name, -- Employer Trading or Other Name (Code 2010)
hoi.org_information3 paye_ref_num, -- PAYE Ref Num (Code 2020)
upper(hoi.org_information6) uif_ref_num, -- UIF Ref Num (Code 2024)
upper(hoi.org_information12) sdl_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_org
and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
select (substr(hoi.org_information1, 1, 30)) er_contact_person, -- code 2025
hoi.org_information2 er_contact_number, -- code 2026
hoi.org_information3 er_email_address, -- code 2027
substr(hoi.org_information4,1,5) unit_number, -- Address : Unit Number (Code 2061)
substr(hoi.org_information5,1,25) complex, -- Address : Complex (Code 2062)
substr(hoi.org_information6,1,5) street_number, -- Address : Street Number (Code 2063)
substr(hoi.org_information7,1,25) street_farm, -- Address : Street/Name of Farm (Code 2064)
substr(hoi.org_information8,1,34) suburb_district, -- Address : Suburb/District (Code 2065)
substr(hoi.org_information9,1,23) town_city, -- Address : Town/Cuty (Code 2066)
substr(hoi.org_information10,1,4) postal_code -- Address : Postal Code (Code 2080)
from hr_organization_information hoi
where hoi.organization_id = p_legal_entity_org
and hoi.org_information_context = 'ZA_GRE_TAX_FILE_ENTITY';
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
'SELECT distinct paf.person_id
FROM per_assignments_f paf,
pay_payrolls_f PPY,
pay_payroll_actions PPA,
per_assignment_extra_info AEI
WHERE PPA.payroll_action_id = :payroll_action_id
AND paf.business_group_id = PPA.business_group_id
AND paf.assignment_type = ''E''
AND PPY.payroll_id = paf.payroll_id
AND AEI.assignment_id = paf.assignment_id
AND AEI.aei_information_category = ''ZA_SPECIFIC_INFO''
AND AEI.aei_information7 = to_char('||l_legal_entity_org||')';
' AND exists (select 1
from hr_assignment_sets has1
where has1.assignment_set_id = '||l_asg_set_id||'
and has1.business_group_id = paf.business_group_id
and nvl(has1.payroll_id,paf.payroll_id) = paf.payroll_id
and (not exists (select 1
from hr_assignment_set_amendments hasa1
where hasa1.assignment_set_id = has1.assignment_set_id)
or exists (select 1
from hr_assignment_set_amendments hasa2
where hasa2.assignment_set_id = has1.assignment_set_id
and hasa2.assignment_id = paf.assignment_id
and nvl(hasa2.include_or_exclude,''I'') = ''I'')
or (not exists (select 1
from hr_assignment_set_amendments hasa3
where hasa3.assignment_set_id = has1.assignment_set_id
and hasa3.assignment_id = paf.assignment_id
and nvl(hasa3.include_or_exclude,''I'') = ''E'')
and not exists (select 1
from hr_assignment_set_amendments hasa4
where hasa4.assignment_set_id = has1.assignment_set_id
and nvl(hasa4.include_or_exclude,''I'') = ''I'')
)
)
)';
select get_parameter('TAX_YEAR', legislative_parameters)
into l_tax_year
from pay_payroll_actions
where payroll_action_id = p_payroll_action_id;
g_code_list.delete(3615);
SELECT /*+ INDEX(asg PER_ASSIGNMENTS_F_N12) */
/* we used the above hint to always ensure that the use the person_id
index on per_assignments_f, otherwise, it is feasible the CBO may decide to
choose the N7 (payroll_id) index due to it being a bind */
asg.person_id person_id
, asg.assignment_id assignment_id
FROM
per_all_assignments_f asg
, pay_payroll_actions ppa_arch
, per_assignment_extra_info aei
WHERE
ppa_arch.payroll_action_id = pactid
AND asg.business_group_id + 0 = ppa_arch.business_group_id
AND asg.person_id BETWEEN stperson AND endperson
AND aei.assignment_id = asg.assignment_id
AND aei.aei_information7 = to_char(p_legal_entity)
AND asg.payroll_id is not null
AND asg.payroll_id = nvl(p_payroll_id,asg.payroll_id)
AND
( ppa_arch.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
OR
( asg.effective_end_date <= ppa_arch.effective_date
AND asg.effective_end_date =
( SELECT MAX(asg2.effective_end_date)
FROM per_all_assignments_f asg2
WHERE asg2.assignment_id = asg.assignment_id
)
)
)
-- for ITREG batch, pick up only persons with Nature A/B/C/N
AND (p_itreg_batch = 'N'
or
(p_itreg_batch = 'Y' and aei.aei_information4 in ('01','02','03','11'))
)
AND EXISTS (SELECT /*+ ORDERED */
/* the ordered hint will force the paa table to be joined to first */
NULL
FROM pay_assignment_actions paa
, pay_payroll_actions ppa
, per_time_periods ptp
WHERE paa.assignment_id = asg.assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
AND ptp.time_period_id = ppa.time_period_id
AND ptp.prd_information1 = p_tax_year
AND ptp.end_date <= decode(p_period_recon, '02', ptp.end_date, p_period_recon_last_date)
AND paa.action_status IN ('C','S') --10376999
AND ppa.action_status = 'C'
)
order by 1, 2;
select legislative_parameters,payroll_id
into leg_param,l_ppa_payroll_id
from pay_payroll_actions
where payroll_action_id = pactid;
select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
into l_period_recon_last_date
from dual;
update pay_payroll_actions
set payroll_id = l_payroll_id
where payroll_action_id = pactid;
select distinct include_or_exclude
into v_incl_sw
from hr_assignment_set_amendments
where assignment_set_id = asg_set_id;
select include_or_exclude
into inc_flag
from hr_assignment_set_amendments
where assignment_set_id = asg_set_id
and assignment_id = asgrec.assignment_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select distinct prrv.result_value directive_number
from pay_payroll_actions ppa
, per_time_periods ptp
, pay_assignment_actions paa
, pay_run_results prr
, pay_run_result_values prrv
, pay_element_types_f peef
, pay_input_values_f piv
WHERE 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
and ptp.end_date <= decode(p_period_recon, '02',ptp.end_date , p_period_recon_last_date) -- 9877034 fix
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = p_ass_id
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and peef.element_type_id = prr.element_type_id
and piv.input_value_id = prrv.input_value_id
and piv.name = 'Tax Directive Number'
and peef.element_name <> 'ZA_Tax'
and ppa.effective_date between peef.effective_start_date and peef.effective_end_date
and ppa.effective_date between piv.effective_start_date and piv.effective_end_date;
select max(peevf.screen_entry_value)
from pay_element_entry_values_f peevf,
pay_element_entries_f peef,
pay_link_input_values_f plivf,
pay_input_values_f pivf,
pay_element_types_f petf,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_action_id = p_run_assact_id
and ppa.payroll_action_id = paa.payroll_action_id
and petf.element_name = 'ZA_Tax'
and petf.legislation_code = 'ZA'
and petf.business_group_id is null
and ppa.effective_date between petf.effective_start_date
and petf.effective_end_date
and pivf.element_type_id = petf.element_type_id
and pivf.name = p_input_value_name
and ppa.effective_date between pivf.effective_start_date
and pivf.effective_end_date
and plivf.input_value_id = pivf.input_value_id
and ppa.effective_date between plivf.effective_start_date
and plivf.effective_end_date
and peef.element_link_id = plivf.element_link_id
and peef.assignment_id = paa.assignment_id
and peevf.element_entry_id = peef.element_entry_id
and peef.effective_start_date =
(
select max(peef2.effective_start_date)
from pay_element_entries_f peef2
where peef2.effective_start_date <= ppa.effective_date
and peef2.element_link_id = plivf.element_link_id
and peef2.assignment_id = paa.assignment_id
)
and peevf.input_value_id = pivf.input_value_id
and peevf.effective_start_date = peef.effective_start_date
and peevf.effective_end_date = peef.effective_end_date;
select global_value
from ff_globals_f
where legislation_code = 'ZA'
and global_name = 'ZA_COMP_DEATH_EMP_AMT'
and p_effective_date between effective_start_date and effective_end_date;
select ppa.legislative_parameters
, paa.assignment_id
into l_leg_param
, l_assignment_id
from pay_payroll_actions ppa
, pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and ppa.payroll_action_id = paa.payroll_action_id;
select max(paaf.person_id)
into l_person_id
from per_all_assignments_f paaf
where paaf.assignment_id = l_assignment_id;
g_code_list.delete(3615);
select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
into l_period_recon_last_date
from dual;
SELECT max (paaf.effective_start_date)
INTO l_asg_end_date
FROM per_assignment_status_types past
,per_all_assignments_f paaf
WHERE paaf.assignment_id = l_assignment_id
AND paaf.assignment_status_type_id = past.assignment_status_type_id
AND past.per_system_status IN ('TERM_ASSIGN','END');
SELECT max (nvl(actual_termination_date,TO_DATE('31/12/4712','DD/MM/YYYY')))
-- fix for bug 12992314 ends
INTO l_asg_end_date
FROM per_periods_of_service
WHERE person_id = l_person_id
-- fix for bug 12992314 starts
AND DATE_START <= last_day(to_date('01-02-'||l_tax_year,'DD-MM-YYYY'));
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 = l_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 = l_tax_year
and ptp.end_date <= decode(l_period_recon, '02', ptp.end_date, l_period_recon_last_date); -- 9877034 fix
select assignment_action_id
into l_run_assact_id
from pay_assignment_actions
where assignment_id = l_assignment_id
and action_sequence = l_run_action_seq;
select decode (sign (add_months (to_date (l_archive_tab(0).act_info11, 'YYYYMMDD'), 780)
- to_date (l_archive_tab(0).act_info4 || '-03-01', 'yyyy-mm-dd')-1), 1
, 'B', 'A')
into l_65Years_age
from dual;
t_final_arch.delete;
t_final_arch.delete;
insert_archive_row(p_assactid, l_archive_tab);
select pai.action_information30
, pai.action_context_id
, pai.action_context_type
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
where ppa.payroll_action_id = pactid
and ppa.action_status = 'C'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and action_information1 is null
order by pai.action_information30 ;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = pactid;
update pay_action_information
set action_information1 = l_itreg_cert_num
where action_context_type = emprec.action_context_type
and action_context_id = emprec.action_context_id
and action_information30 = emprec.action_information30 ;
select code,
decode(code,
'3601','INCOME','3602','INCOME','3603','INCOME','3604','INCOME',
'3605','INCOME','3606','INCOME','3607','INCOME','3609','INCOME',
'3610','INCOME','3611','INCOME','3612','INCOME','3613','INCOME',
'3615','INCOME','3616','INCOME','3617','INCOME',
'3701','INCOME','3702','INCOME','3703','INCOME','3704','INCOME',
'3705','INCOME','3706','INCOME','3708','INCOME','3709','INCOME',
'3710','INCOME','3711','INCOME','3712','INCOME','3713','INCOME',
'3714','INCOME','3715','INCOME','3716','INCOME','3717','INCOME',
'3801','INCOME','3802','INCOME','3803','INCOME','3804','INCOME',
'3805','INCOME','3806','INCOME','3807','INCOME','3808','INCOME',
'3809','INCOME','3810','INCOME','3813','INCOME','3815','INCOME',
'3907','INCOME','3908','INCOME',
'3608','LUMPSUM','3614','LUMPSUM','3707','LUMPSUM','3718','LUMPSUM',
'3901','LUMPSUM','3906','LUMPSUM','3907','LUMPSUM','3909','LUMPSUM',
'3915','LUMPSUM','3920','LUMPSUM','3921','LUMPSUM', '3922', 'LUMPSUM',
'4001','DEDUCTION','4002','DEDUCTION','4003','DEDUCTION','4004','DEDUCTION',
'4005','DEDUCTION','4006','DEDUCTION','4007','DEDUCTION','4018','DEDUCTION',
'4024','DEDUCTION','4026','DEDUCTION','4030','DEDUCTION','4474','DEDUCTION',
'4493','DEDUCTION',
'4101','SITE','4102','PAYE','4115','PAYE_RET_LMPSM','4116','MED_TAX_CR') code_type,
sub_type(code,user_name,balance_sequence) code_sub_type,
full_balance_name,
balance_type_id,
balance_sequence
from pay_za_irp5_bal_codes
where ( code in (3601,3602,3603,3604,3605,3606,3607,3609,3610,3611,3612,3613,3615,3616,3617,
3701,3702,3703,3704,3705,3706,3708,3709,3710,3711,3712,3713,3714,3715,3716,3717,
3801,3802,3803,3804,3805,3806,3807,3808,3809,3810,3813,3908,
4001,4002,4003,4004,4005,4006,4007,4018,4024,4026,4030,4474,4493,
4101,3815)
and balance_sequence = 1
)
or
(code = 4005 and balance_sequence = 2)
or
(code = 3907 and balance_sequence = 1 and full_balance_name <> 'Other Lump Sums')
or
(code = 4102 and balance_sequence = 1 and full_balance_name <> 'Tax on Lump Sums')
or
(code = 4116 and balance_sequence = 1 and full_balance_name <> 'Medical Tax Credit Used on Lump Sums')
or
( code in (3608,3614,3707,3718,3901,3906,3907,3909,3915,3920,3921,3922,4102,4115,4116)
and balance_sequence = 3
)
order by code asc, balance_sequence desc;
select aei.assignment_id
, substr(aei.AEI_INFORMATION2,1,120) trade_name
, hr_general.decode_lookup('ZA_PER_NATURES',aei.AEI_INFORMATION4) nature
, paa.payroll_action_id
, aei.aei_information6 independent_contractor
, aei.aei_information10 labour_broker
, aei.aei_information15 foreign_income
, aei.aei_information13 payment_type
, aei.aei_information14 personal_pay_method_id
from per_assignment_extra_info aei
, pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and aei.assignment_id = paa.assignment_id
and aei.aei_information_category = 'ZA_SPECIFIC_INFO';
select ppf.person_id
, substr(ltrim(rtrim(ppf.last_name)),1,120) last_name
, ppf.first_name || ',' || ppf.middle_names first_two_names
, ppf.national_identifier id_number
, ppf.per_information2 passport_number
, ppf.per_information10 country_of_passport_issue
, to_char(ppf.date_of_birth,'YYYYMMDD') date_of_birth
, ppf.per_information1 income_tax_ref_num
, ppf.employee_number
, ppf.email_address
--, a.location_id
, nvl(a.location_id, nvl(pap.location_id, haou.location_id)) location_id
from per_all_people_f ppf
, per_all_assignments_f a
, per_all_positions pap
, hr_all_organization_units haou
where a.assignment_id = p_assignment_id
and ppf.person_id = a.person_id
and l_effective_date between a.effective_start_date and a.effective_end_date
and l_effective_date between ppf.effective_start_date and ppf.effective_end_date
and pap.position_id (+) = a.position_id
and haou.organization_id = a.organization_id ;
select address_line1
, address_line2
, address_line3
, region_1
, region_2
, town_or_city
, 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 location_code
from hr_locations
where location_id = p_location_id;
select decode(region_2,'Y','X',null) -- Postal Address same as residential address flag
, decode(region_2,'Y',null,address_line1) -- if flag = Y, then don't populate remaining postal address fields
, decode(region_2,'Y',null,address_line2)
, decode(region_2,'Y',null,address_line3)
, decode(region_2,'Y',null,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';
select personal_payment_method_id personal_pay_method_id
, ppm_information1 account_type
from pay_personal_payment_methods_f
where assignment_id = p_assignment_id
and PPM_INFORMATION_CATEGORY in ('ZA_ACB','ZA_CHEQUE','ZA_CREDIT TRANSFER','ZA_MANUAL PAYMENT')
and ppm_information1 in ('Y','0','7')
and l_effective_date between effective_start_date and effective_end_date;
select pea.segment2 account_type -- account_type
, pea.segment3 account_number -- account number
, pea.segment1 branch_code -- bank branch code
, trim(translate(trim(bnk.bank_name),
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz- ,''0123456789~%^&*<>{}[]"\/?@&$!#+=;:().',
select ppa.legislative_parameters
, paa.assignment_id
into l_leg_param
, l_assignment_id
from pay_payroll_actions ppa
, pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and ppa.payroll_action_id = paa.payroll_action_id ;
select last_day(decode(l_period_recon, '02', to_date(l_tax_year||'-02-01','yyyy-mm-dd'), '08', to_date(l_tax_year-1 ||'-08-01','yyyy-mm-dd')))
into l_period_recon_last_date
from dual;
select least(p_effective_date,max(effective_end_date))
into l_effective_date
from per_all_assignments_f
where assignment_id = l_assignment_id;
select max(paa.action_sequence)
into l_max_act_seq
from pay_assignment_actions paa
, pay_payroll_actions ppa
, per_time_periods ptp
where paa.assignment_id = l_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type in ('R', 'Q', 'V', 'B', 'I')
and ptp.time_period_id = ppa.time_period_id
and ptp.prd_information1 = l_tax_year
and ptp.end_date <= decode(l_period_recon, '02',ptp.end_date, l_period_recon_last_date) -- 9877034 fix
and paa.action_status IN ('C','S')
and ppa.action_status = 'C';
select ppa.payroll_id, paa.assignment_action_id, ptp.start_date
into l_payroll_id, l_run_assact_id, p_lumpsum_date
from pay_payroll_actions ppa
, pay_assignment_actions paa
, per_time_periods ptp
where paa.assignment_id = l_assignment_id
and paa.action_sequence = l_max_act_seq
and ppa.payroll_action_id = paa.payroll_action_id
and ptp.time_period_id = ppa.time_period_id ;
select min(start_date), max(end_date)
into l_tax_year_start_date, l_tax_year_end_date
from per_time_periods
where payroll_id = l_payroll_id
and prd_information1 = l_tax_year;
select count(start_date)
into l_total_tax_periods
from per_time_periods
where payroll_id = l_payroll_id
and prd_information1 = l_tax_year;
select min(start_date), max(end_date)
into l_tax_year_start_date, l_tax_year_end_date
from per_time_periods
where payroll_id = l_payroll_id
and prd_information1 = l_tax_year
and end_date <= l_period_recon_last_date;
select count(start_date)
into l_total_tax_periods
from per_time_periods
where payroll_id = l_payroll_id
and prd_information1 = l_tax_year
and end_date <= l_period_recon_last_date;
select nvl(min(paaf.effective_start_date), fnd_date.canonical_to_date('1001/01/01 00:00:00'))
, nvl(max(paaf.effective_end_date), fnd_date.canonical_to_date('4712/12/31 00:00:00'))
into l_asg_hire_date
, l_asg_term_date
from per_assignment_status_types past,
per_all_assignments_f paaf
where paaf.assignment_id = l_assignment_id
and paaf.effective_start_date <= l_tax_year_end_date
and paaf.assignment_status_type_id = past.assignment_status_type_id
and past.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
select trim(to_char(trunc( (l_date_employed_to - l_date_employed_from + 1)/
(( l_tax_year_end_date - l_tax_year_start_date +1)/l_total_tax_periods)
,4),'990D9999'))
into l_pay_periods_worked
from dual;
l_context_lst.delete;
l_output_table.delete;
l_context_lst.delete;
t_inc_code.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 pdb.defined_balance_id
from pay_balance_dimensions pbd
, pay_defined_balances pdb
where pbd.dimension_name = p_dim_name
and pbd.legislation_code = 'ZA'
and pdb.balance_type_id = p_bal_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select balance_type_id
from pay_balance_types
where balance_name = p_bal_name
and legislation_code = 'ZA';
select pay_balance_pkg.get_value(p_def_bal_id, --p_def_bal_id
p_asg_act_id, --assignment_action_id
null,
null,
null,
null,
null,
null,
null,
'TRUE')
from dual;
procedure insert_archive_row(p_assactid in number,
p_tab_rec_data in action_info_table) is
l_proc constant varchar2(50):= g_package||'insert_archive_row';
end insert_archive_row;