[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
J.N. Louw 17-Jun-2002 115.8 2420012 Id Flex select corrected
Nirupa S 10-Dec-2002 115.10 2686708 Added NOCOPY
Nageswara Rao 24-Jan-2003 115.11 2654703 Added new function
get_uif_total_remu_sub_uif
Nageswara Rao 13-Feb-2003 115.12 2798916 Changed query in Action Creation
procedure
Nageswara Rao 14-Feb-2003 115.13 Changes to query in Action Creation
procedure to select all Employees
in a payroll run
Nageswara Rao 10-Apr-2003 115.15 2874102 changes to obsolete reason_non_contrib
code '07'
2863938 when first_name is null, archive 'XXX'
Kaladhaur P 05-Oct-2004 115.16 3869426 Modified query in Action Creation to
include future terminated employees
to Electronic UIF File
A. Mahanty 23-DEC-2004 115.18 4072410 An extra condition was added for an Employee
having a non-contribution reason of 01 and with
a value in the UIF Employee Contribution balance.
No non-contribution reason must be written to the
UIF File for such cases.
A. Mahanty 14-FEB-2004 115.19 4134166 The Monthly UIFable Limit calculation was changed.
Two cursors csr_pay_periods_per_year and
csr_pay_periods_per_month were added.The cursor
csr_uif_limit was modified.
4140343 An additional condition was added to set to zero the
balance values for an employee who has not been processed
even once in a month.All eligible employees are included
in the UIF File, even if they are not processed.
Kaladhaur P 22-Apr-2005 115.20 4306265 Modified the cursor csr_employee_data in archive_data.
Modified the parameter value passed to csr_employee_data
inorder to fetch date effective data.
Kaladhaur P 15-Sep-2005 115.20 4612798 R12 Performance Bug Fix. Tuned the query in the cursor
csr_latest_asg_action.
A. Mahanty 19-Dec-2005 115.22 4768622 R12 Performance Bug Fix. Modified the query in the
procedure archive_data.
P.Arusia 16-Jul-2008 115.23 7255839 If reason for UIF non-contribution is 007, then report
it as 07
========================================================================
*/
sql_range varchar2(4000);
select substr
(
legislative_parameters,
instr
(
legislative_parameters,
p_token
) + (length(p_token) + 1),
instr
(
legislative_parameters,
' ',
instr
(
legislative_parameters,
p_token
)
)
-
(
instr
(
legislative_parameters,
p_token
) + length(p_token)
)
),
business_group_id
from pay_payroll_actions
where payroll_action_id = p_pact_id;
select paa.assignment_action_id
from pay_assignment_actions paa
where paa.assignment_id = p_assignment_id
and paa.rowid =
(
select substr(
max(lpad(paa2.action_sequence, 15, 0) || paa2.rowid)
, -length(paa2.rowid)
)
from pay_payroll_actions ppa2
, pay_assignment_actions paa2
where paa2.assignment_id = p_assignment_id
and ppa2.payroll_action_id = paa2.payroll_action_id
and ppa2.action_type in ('R', 'Q', 'I', 'B', 'V')
and ppa2.effective_date >= (add_months(p_effective_date, -1)+1) /*g_canonical_start_date*/
and ppa2.effective_date <= p_effective_date /*g_canonical_end_date*/
group by length(paa2.rowid)
);
select def.defined_balance_id
into l_defined_bal_id
from pay_defined_balances def,
pay_balance_types pbt,
pay_balance_dimensions dim,
pay_balance_types_tl pbt_tl
where pbt_tl.balance_name = p_balance_name
and pbt_tl.language = 'US'
and pbt_tl.balance_type_id = pbt.balance_type_id
and pbt.legislation_code = 'ZA'
and dim.dimension_name = p_dimension
and dim.legislation_code = 'ZA'
and pbt.balance_type_id = def.balance_type_id
and dim.balance_dimension_id = def.balance_dimension_id
and def.legislation_code = 'ZA';
Purpose : This returns the select statement that is used to created 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
) is
-- Returns Creator Information for the specified UIF Month that has not been archived yet
cursor csr_creator_info is
select hoi.org_information1,
hoi.org_information2,
hoi.org_information3,
hoi.org_information4
from hr_organization_information hoi
, hr_all_organization_units org
where hoi.org_information_context = 'ZA_UIF_CREATOR_INFO'
and hoi.organization_id = org.organization_id
and org.organization_id = g_business_group_id;
select null
from pay_action_information pai
, pay_payroll_actions ppa
where pai.action_context_type = 'PA'
and pai.action_information_category = 'ZA UIF CREATOR DETAILS'
and pai.action_information1 = g_business_group_id
and pai.action_information2 = to_char(g_canonical_end_date, 'YYYYMM')
and ppa.payroll_action_id = pai.action_context_id
)
*/
cursor csr_archive_effective_date(pactid number) is
select effective_date
from pay_payroll_actions
where payroll_action_id = pactid;
update pay_payroll_actions
set payroll_id = l_payroll_id
where payroll_action_id = pactid;
select get_parameter('START_DATE', legislative_parameters)
into l_start_date
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('END_DATE', legislative_parameters)
into l_end_date
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('BG_ID', legislative_parameters)
into l_bg_id
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('ASG_SET_ID', legislative_parameters)
into g_asg_set_id
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('PERSON_ID', legislative_parameters)
into g_person_id
from pay_payroll_actions
where payroll_action_id = pactid;
'select distinct asg.person_id
from per_assignments_f asg,
pay_payrolls_f ppf,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and asg.business_group_id = ppa.business_group_id
and asg.assignment_type = ''E''
and ppf.payroll_id = asg.payroll_id
and ppf.payroll_id = ppa.payroll_id
order by asg.person_id';
Purpose : This returns the select statement that is used to created the
range rows for the UIF File.
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_mag
(
pactid in number,
sqlstr out nocopy varchar2
) is
sql_range varchar2(4000);
'select distinct asg.person_id
from per_assignments_f asg,
pay_payrolls_f ppf,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and asg.business_group_id = ppa.business_group_id
and asg.assignment_type = ''E''
and ppf.payroll_id = asg.payroll_id
and ppf.payroll_id = ppa.payroll_id
order by asg.person_id';
select asg.person_id person_id,
asg.assignment_id assignment_id
from per_assignments_f asg,
per_assignments_f asg3,
--pay_payrolls_f ppf, -- Bug 2608190
pay_payroll_actions ppa_arch,
per_assignment_extra_info paei,
per_periods_of_service pds -- Bug 2654703
where asg.business_group_id = ppa_arch.business_group_id
and asg.period_of_service_id = pds.period_of_service_id -- Bug 2608190
and asg3.period_of_service_id = pds.period_of_service_id -- Bug 2608190
and asg.person_id between p_stperson and p_endperson
and paei.assignment_id = asg.assignment_id
and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
-- Not an Independent Contractor
and nvl(paei.aei_information6, 'N') = 'N'
-- Nature of Person not in the following ZA_PER_NATURES Lookup Values
and paei.aei_information4 not in ('04', '05', '06', '07', '08', '09')
and ppa_arch.payroll_action_id = p_pactid
--and ppf.payroll_id = ppa_arch.payroll_id -- Bug 2608190
--and asg.payroll_id = ppf.payroll_id -- Bug 2608190
and ppa_arch.payroll_id = asg.payroll_id -- Bug 2608190
--and ppa_arch.effective_date between ppf.effective_start_date -- Bug 2608190
-- and ppf.effective_end_date -- Bug 2608190
-- Get the Assignment End Date
and asg.effective_end_date =
(
select max(asg2.effective_end_date)
from per_assignments_f asg2
, per_assignment_status_types sta
where asg2.assignment_id = asg.assignment_id
and asg2.assignment_status_type_id = sta.assignment_status_type_id
and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
)
-- Check that Assignment ends after UIF Month Start Date
and ( asg.effective_end_date >= p_canonical_start_date
or pds.final_process_date >= p_canonical_start_date ) -- Bug 2608190
-- Get the Assignment Start Date
and asg3.assignment_id = asg.assignment_id
and asg3.effective_start_date =
(
select min(asg4.effective_start_date)
from per_assignments_f asg4
, per_assignment_status_types sta
where asg4.assignment_id = asg3.assignment_id
and asg4.assignment_status_type_id = sta.assignment_status_type_id
and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN')
)
-- Check that Assignment starts before UIF Month End Date
and asg3.effective_start_date <= p_canonical_end_date
order by 2
for update of asg.assignment_id;
select ppf.person_id
,paa.assignment_id
from per_all_people_f ppf
,per_all_assignments_f paa
,per_assignment_extra_info paei
,pay_payroll_actions ppa_arch
,per_periods_of_service pps
where paa.business_group_id = ppa_arch.business_group_id
and paa.person_id = ppf.person_id
and ppf.person_id between p_stperson and p_endperson /* to select all Employees in a payroll run */
and paa.period_of_service_id = pps.period_of_service_id
and paei.assignment_id = paa.assignment_id
and ppa_arch.payroll_id = paa.payroll_id
and ppa_arch.payroll_action_id = p_pactid
and paei.aei_information_category = 'ZA_SPECIFIC_INFO'
/* Not an Independent Contractor */
and nvl(paei.aei_information6, 'N') = 'N'
/* Nature of Person not in the following ZA_PER_NATURES Lookup Values */
and paei.aei_information4 not in ('04', '05', '06', '07', '08', '09')
and ppf.effective_start_date = ( select max(effective_start_date)
from per_all_people_f ppf1
where ppf1.person_id = ppf.person_id
and ppf1.effective_start_date <= p_canonical_end_date
and ppf1.effective_end_date >= '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1))
and paa.effective_start_date = ( select max(paa1.effective_start_date)
from per_all_assignments_f paa1 where paa1.assignment_id = paa.assignment_id
and paa1.effective_start_date <= p_canonical_end_date
and paa1.effective_end_date >= '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1))
and
(
(
pps.actual_termination_date is not null
and
(
(
pps.actual_termination_date between '01-MAR-' || to_number(to_char(p_canonical_end_date ,'YYYY') - 1) and p_canonical_end_date
and
(
pps.actual_termination_date between p_canonical_start_date and p_canonical_end_date
or
(
pps.actual_termination_date < p_canonical_start_date
and nvl(pps.final_process_date,to_date('31-12-4712','DD-MM-YYYY')) >= p_canonical_start_date
and pay_za_uif_archive_pkg.get_balance_value(paa.assignment_id,'Total UIFable Income','_ASG_TAX_MTD',p_canonical_end_date) <> 0
)
)
)
or pps.actual_termination_date > p_canonical_end_date /* New Condition for Bug 3869426 */
)
)
or pps.actual_termination_date is null
)
order by 2
for update of paa.assignment_id;
select get_parameter('START_DATE', legislative_parameters)
into l_start_date
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('END_DATE', legislative_parameters)
into l_end_date
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('BG_ID', legislative_parameters)
into l_bg_id
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('ASG_SET_ID', legislative_parameters)
into l_asg_set_id
from pay_payroll_actions
where payroll_action_id = pactid;
select get_parameter('PERSON_ID', legislative_parameters)
into l_person_id
from pay_payroll_actions
where payroll_action_id = pactid;
select distinct include_or_exclude
into v_incl_sw
from hr_assignment_set_amendments
where assignment_set_id = l_asg_set_id;
select include_or_exclude
into inc_flag
from hr_assignment_set_amendments
where assignment_set_id = l_asg_set_id
and assignment_id = asgrec.assignment_id;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select per.national_identifier ID_Number,
nvl(per.per_information2, per.per_information3) Other_Number,
per.employee_number,
per.last_name,
names(per.first_name||', '||per.middle_names) First_Names,
per.date_of_birth,
pos.date_start Date_Employed_From,
pos.actual_termination_date Date_Employed_To,
pos.leaving_reason
from per_people_f per,
per_periods_of_service pos
where per.person_id = p_person_id
-- Bug 4306265: and per.effective_end_date between per.effective_start_date and p_eff_date -- Modified the condition for Bug : 3869426
and p_eff_date between per.effective_start_date and per.effective_end_date -- Bug 4306265: re-enabled /* Old Condition Before Bug : 3869426 */
and per.per_information_category = 'ZA'
and pos.person_id(+) = per.person_id
and nvl(pos.actual_termination_date(+), per.effective_end_date) = per.effective_end_date;
select flv.meaning
from fnd_lookup_types flt,
fnd_lookup_values flv
where flt.lookup_type = 'LEAV_REAS'
and flt.lookup_type = flv.lookup_type
and flv.language = 'US'
and flv.lookup_code = p_leaving_reason
and flv.enabled_flag = 'Y';
select min(asg2.effective_start_date)
from per_assignments_f asg2
, per_assignment_status_types sta
where asg2.assignment_id = p_asg_id
and asg2.assignment_status_type_id = sta.assignment_status_type_id
and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN', 'TERM_ASSIGN'); -- Added 'TERM_ASSIGN' for bug 3869426
select paei.aei_information2 Empl_Status,
paei.aei_information1 Reason_Non_Contrib
from per_assignment_extra_info paei
where paei.assignment_id = p_asg_id
and paei.aei_information_category = 'ZA_UIF_INFO';
select flv.lookup_code
from fnd_lookup_types flt,
fnd_lookup_values flv
where flt.lookup_type = 'ZA_UIF_TERMINATION_CATEGORIES'
and flt.lookup_type = flv.lookup_type
and flv.language = 'US'
and flv.meaning = p_empl_stat_value
and flv.enabled_flag = 'Y';
select paei.aei_information7 Legal_Entity
from per_assignment_extra_info paei
where paei.assignment_id = p_asg_id
and paei.aei_information_category = 'ZA_SPECIFIC_INFO';
select hoi.org_information6 Employer_UIF_Ref_No,
/* Changes as per Bug2654703 */
hoi.org_information10 Employer_email_Address,
hoi.org_information3 Employer_PAYE_Number
from hr_organization_information hoi,
hr_all_organization_units org
where org.organization_id = p_org_id
and org.organization_id = hoi.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
select pea.segment1 seg1,
pea.segment3 seg3,
pea.segment2 seg2
from pay_personal_payment_methods_f pppm,
pay_external_accounts pea,
pay_org_payment_methods_f porg,
pay_legislation_rules plr
where pppm.assignment_id = p_asg_id
and pea.external_account_id = pppm.external_account_id
and pea.id_flex_num = plr.rule_mode
and plr.LEGISLATION_CODE = 'ZA'
and plr.rule_type = 'E'
and pea.territory_code = 'ZA'
and pppm.priority =
( select min(pppm2.priority)
from pay_personal_payment_methods_f pppm2,
pay_external_accounts pea2,
pay_org_payment_methods_f porg2,
pay_legislation_rules plr2
where pppm2.assignment_id = pppm.assignment_id
and pea2.external_account_id = pppm2.external_account_id
and pea2.id_flex_num = plr2.rule_mode
and plr2.LEGISLATION_CODE = 'ZA'
and plr2.rule_type = 'E'
and pea2.territory_code = 'ZA'
and p_eff_date between pppm2.effective_start_date
and pppm2.effective_end_date
and pppm2.org_payment_method_id = porg2.org_payment_method_id
/* Exclude 3rd Party Payment Methods*/
and porg2.defined_balance_id is not null
and p_eff_date between porg2.effective_start_date
and porg2.effective_end_date
)
and p_eff_date between pppm.effective_start_date
and pppm.effective_end_date
and pppm.org_payment_method_id = porg.org_payment_method_id
/* Exclude 3rd Party Payment Methods*/
and porg.defined_balance_id is not null
and p_eff_date between porg.effective_start_date
and porg.effective_end_date;
select count(ptp.end_date)
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and ptp.end_date >= '01-MAR-'||to_char(p_eff_date,'YYYY')
and ptp.end_date < '01-MAR-'||to_number(to_char(p_eff_date,'YYYY')+1);
select count(ptp.end_date)
from per_time_periods ptp
where ptp.payroll_id = p_payroll_id
and to_char(ptp.end_date,'MMYYYY')= to_char(p_eff_date, 'MMYYYY');
select round((to_number(ffg.global_value)*l_pay_periods_per_month/l_pay_periods_per_year),2) --Bug 4134166
--select (to_number(ffg.global_value)/12)
from ff_globals_f ffg
where ffg.global_name = 'ZA_UIF_ANN_LIM'
and ffg.legislation_code = 'ZA'
and p_eff_date between ffg.effective_start_date and ffg.effective_end_date;
select ppa.effective_date,
ppa.payroll_action_id
into l_archive_effective_date,
l_pactid --Bug 4134166
from pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_action_id = p_assactid;
select paa.assignment_id
into l_asgid
from pay_assignment_actions paa
where paa.assignment_action_id = p_assactid;
select asg.person_id
, business_group_id
, asg.effective_end_date
into l_person_id
, l_business_group_id
, l_asg_eff_end_date
from per_assignments_f asg
where asg.assignment_id = l_asgid
and asg.effective_end_date =
(
select max(asg2.effective_end_date)
from per_assignments_f asg2
, per_assignment_status_types sta
where asg2.assignment_id = l_asgid --Bug 4768622
and asg2.assignment_status_type_id = sta.assignment_status_type_id
and sta.per_system_status in ('ACTIVE_ASSIGN', 'SUSP_ASSIGN') -- Bug 4306265: Removed 'TERM_ASSIGN') -- Added 'TERM_ASSIGN' for bug 3869426
);