[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
R Babla 10/06/2009 120.0 8512751 Updated Initial Version
R Babla 26/06/2009 120.1 8512751 Removed GSCC Errors
R Babla 26/06/2009 120.0.12010000.3 8512751 Modified the message in warning section.
Changed the archive_code to set l_cnt_paye_perm
as N if its seasonal worker with no remuneration
with EMP201 Status as Y
R Babla 01/09/2009 120.0.12010000.3 8859207 1.Changes done in cursor csr_asg_details to include
space between name
2. Changes done in the parameters passed to cursor
csr_check_asg_termination
BKeshary 07/12/2010 120.0.12010000.4 10376999 Changes to avoid impact of enabling skip
terminated asg leg rule.
A Dash 03/08/2012 120.0.12010000.6 14081001 Reverse run in a month should be reported in that month even
if it is a reversal of a payrun of a previous month.
asnell 10/12/2012 120.0.12010000.7 14543232 Time period based on actions effective date even if its a reversal
ABDASH 28/01/2013 120.0.12010000.8 16174886 Adding of legal entity parameter to ZA EMP201 Report.
ABDASH 05/02/2013 120.0.12010000.9 16174886 Adding of legal entity parameter to ZA EMP201 Report.
*/
g_package constant varchar2(30) := 'pay_za_emp201.';
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;
update pay_payroll_actions
set payroll_id = l_payroll_id
where payroll_action_id = pactid;
'select distinct ass.person_id
from per_assignments_f ass,
pay_payrolls_f ppf,
pay_payroll_actions ppa
where ppa.payroll_action_id = :payroll_action_id
and ass.business_group_id = ppa.business_group_id
and ass.assignment_type = ''E''
and ppf.payroll_id = ass.payroll_id
and ppf.payroll_id = ppa.payroll_id
order by ass.person_id';
'select distinct paf.person_id
from per_assignments_f paf,
pay_payrolls_f ppf,
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 ppf.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_id||')
order by paf.person_id';
select effective_date
from pay_payroll_actions
where payroll_action_id = pactid;
select payroll_name
from pay_all_payrolls_f
where payroll_id = p_payroll_id
and l_effective_date between effective_start_date and effective_end_date;
select haou.name
from hr_all_organization_units haou,
hr_organization_information hoi,
hr_organization_information hoi2
where haou.organization_id = p_organization_id
and hoi.organization_id = haou.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hoi2.organization_id = haou.organization_id
and hoi2.org_information_context = 'CLASS'
and hoi2.org_information1 = 'HR_LEGAL'
and hoi2.org_information2 = 'Y' ;
select ppf.person_id
, paa.assignment_id
from per_all_people_f ppf
, per_all_assignments_f paa
, pay_payroll_actions ppa
, per_periods_of_service pps
, per_assignment_extra_info aei
where ppf.person_id between p_stperson and p_endperson
and paa.person_id = ppf.person_id
and paa.business_group_id = ppa.business_group_id
and ppa.payroll_action_id = p_pactid
and aei.assignment_id = paa.assignment_id
and aei.aei_information_category = 'ZA_SPECIFIC_INFO'
and aei.aei_information7 = to_char(p_legal_entity_id)
and paa.payroll_id = nvl(p_payroll_id, paa.payroll_id)
and paa.period_of_service_id = pps.period_of_service_id
-- last person record before this month end
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
)
-- last assignment record before this month end
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
(
pps.actual_termination_date is null -- employee is not terminated
or -- (or)
(
pps.actual_termination_date is not null -- employee is terminated but
and
(
pps.actual_termination_date > p_canonical_end_date -- 1) termination is after this month end (or)
or
pps.actual_termination_date between p_canonical_start_date and p_canonical_end_date -- 2) termination is within this month (or)
or
(
pps.actual_termination_date < p_canonical_start_date -- 3) termination happened before month start (but) final_process_date is after month start
and nvl(pps.final_process_date,to_date('31-12-4712','DD-MM-YYYY')) >= p_canonical_start_date
)
)
)
)
order by 2
for update of paa.assignment_id;
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 p.employee_number
,p.title ||' '|| p.first_name ||' '|| p.last_name emp_name
,a.assignment_id
,a.period_of_service_id
,a.assignment_number
,a.payroll_id
from per_all_people_f p
, per_all_assignments_f a
, pay_assignment_actions paa
where paa.assignment_action_id = p_assactid
and a.assignment_id = paa.assignment_id
and p.person_id = a.person_id
and l_eff_date between p.effective_start_date and p.effective_end_date
and l_eff_date between a.effective_start_date and a.effective_end_date ;
select hr_general.decode_lookup('ZA_PER_NATURES',aei.aei_information4) nature,
nvl(aei.aei_information6,'N') independent_contractor,
nvl(aei.aei_information10,'N') labour_broker,
decode(aei.aei_information12,
'P','Permanent',
'N','Non-Permanent',
nvl(decode(aei.aei_information11,
'P','Permanent',
'N','Non-Permanent'),
per_za_employment_equity_pkg.get_ee_employment_type_name(p_effective_date
, p_period_of_service_id))) EMP201_status
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and aei.information_type = 'ZA_SPECIFIC_INFO' ;
select aei.aei_information1 reason_for_non_contrib
from per_assignment_extra_info aei
where aei.assignment_id = p_assignment_id
and aei.information_type = 'ZA_UIF_INFO' ;
select ppa.payroll_action_id
, ppa.payroll_id
, ptp.end_date
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa.action_status IN ('C','S') -- 10376999
and ptp.payroll_id = ppa.payroll_id
and ppa.date_earned between ptp.start_date and ptp.end_date
and ptp.pay_advice_date between trunc(l_effective_date,'Month') and l_effective_date
and paa.action_sequence = (select max(paa1.action_sequence)
from pay_assignment_actions paa1,
pay_payroll_actions ppa1,
per_time_periods ptp1
where paa1.assignment_id = p_assignment_id
and paa1.payroll_action_id = ppa1.payroll_action_id
and ppa1.action_type IN ('R', 'Q', 'V', 'B', 'I')
and paa1.action_status IN ('C','S') -- 10376999
and ptp1.payroll_id = ppa1.payroll_id
and ppa1.date_earned between ptp1.start_date and ptp1.end_date
and ptp1.pay_advice_date between trunc(l_effective_date,'Month') and l_effective_date
) ;
select decode(hoi.org_information1,'Exempt','E',nvl(aei.aei_information9,'N')) "Exemption"
from per_all_assignments_f ass
, hr_organization_information hoi
, per_assignment_extra_info aei
where ass.assignment_id = p_assignment_id
and p_effective_date between ass.effective_start_date and ass.effective_end_date
and ass.organization_id = hoi.organization_id
and hoi.org_information_context = 'ZA_NQF_SETA_INFO'
and aei.assignment_id = ass.assignment_id
and aei.information_type = 'ZA_SPECIFIC_INFO' ;
select count(ptp.end_date)
from per_time_periods ptp
where ptp.pay_advice_date =
(select tperiod.pay_advice_date
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id
)
and ptp.end_date <=
(select tperiod.end_date
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id
)
and ptp.payroll_id = p_payroll_id;
select count(ptp.end_date)
from per_time_periods PTP
where ptp.prd_information1 =
(select tperiod.prd_information1
from per_time_periods tperiod,
pay_payroll_actions paction
where paction.payroll_action_id = p_payroll_action_id
and tperiod.time_period_id = paction.time_period_id)
and ptp.payroll_id = p_payroll_id;
select global_value
from ff_globals_f
where global_name = p_global_name
and p_effective_date between effective_start_date
and effective_end_date
and legislation_code = 'ZA';
select prrv.result_value
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_element_types_f pet
, pay_input_values_f piv
, pay_run_results prr
, pay_run_result_values prrv
where ppa.payroll_action_id = p_payroll_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and pet.element_name = 'ZA_Tax'
and piv.element_type_id = pet.element_type_id
and piv.name = 'Tax Status'
and prr.element_type_id = pet.element_type_id
and prr.assignment_action_id = paa.assignment_action_id
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = piv.input_value_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 peev.screen_entry_value
from pay_element_entry_values_f peev
, pay_element_entries_f peef
, pay_element_types_f pet
, pay_input_values_f piv
where
pet.legislation_code = 'ZA'
and pet.element_name = 'ZA_Tax'
and piv.element_type_id = pet.element_type_id
and piv.name = 'Tax Status'
and peef.assignment_id = p_assignment_id
and peef.element_type_id = pet.element_type_id
and peev.element_entry_id = peef.element_entry_id
and peev.input_value_id = piv.input_value_id
and p_effective_date between pet.effective_start_date and pet.effective_end_date
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and p_effective_date between peef.effective_start_date and peef.effective_end_date
and p_effective_date between peev.effective_start_date and peev.effective_end_date ;
select decode (past.PER_SYSTEM_STATUS, 'TERM_ASSIGN','Y','N') asg_terminated
from per_all_assignments_f paa,
per_assignment_status_types past
where paa.assignment_id = p_assignment_id
and paa.assignment_status_type_id = past.assignment_status_type_id
and p_effective_date between paa.effective_start_date and paa.effective_end_date ;
select paa.assignment_action_id assignment_action_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_time_periods ptp
where paa.assignment_id = p_assignment_id
and paa.action_status IN ('C','S')
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'V'
and ppa.action_status = 'C'
and ppa.payroll_id = p_payroll_id
-- bug 14543232 use time period spanning effective_date
-- and ppa.time_period_id = ptp.time_period_id
and ptp.payroll_id = p_payroll_id
and ppa.effective_date between ptp.start_date and ptp.end_date
and ptp.end_date <= p_month_end
and ptp.end_date >= trunc(p_month_end, 'mm') -- start of the month
and ppa.effective_date <> ppa.date_earned;
select balance_type_id
,balance_name
from pay_balance_types
where legislation_code = 'ZA'
and balance_name in (
'Tax'
,'Net PAYE Taxable Income'
,'Net Taxable Income'
,'Skills Levy'
,'UIF Employee Contribution'
,'UIF Employer Contribution'
,'Total UIFable Income'
,'Gross Remuneration'
);
l_bal_name_table.delete;
l_net_bal_table.delete;
l_asg_act_id_table.delete;
l_context_lst.delete;
l_output_table.delete;
l_defined_balance_lst.delete;
select ppa.payroll_action_id
into l_pactid_archive
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 max(effective_end_date)
into l_asg_end_date
from per_all_assignments_f paf,
pay_assignment_actions paa
where effective_end_date <= l_month_end
and paa.assignment_id = paf.assignment_id
and paa.assignment_action_id = p_assactid;
select distinct pai.action_information18 payroll_id,
ppf.payroll_name payroll_name,
pai.effective_date effective_date
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
, pay_all_payrolls_f ppf
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 = 'ZA EMP201 ASSIGNMENT DETAILS'
and ppf.payroll_id = pai.action_information18
and pai.effective_date between effective_start_date and effective_end_date
order by payroll_id;
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 action_information1 l_legal_entity_id
, action_information2 l_legal_entity_name
, action_information3 l_payroll_prd
from pay_action_information
where action_context_id = p_archive_pact
and action_context_type = 'PA'
and action_information_category = 'ZA EMP201 LEGAL ENTITY DETAILS' ;
select action_information1 payroll_id
, action_information2 payroll_name
from pay_action_information
where action_context_id = p_archive_pact
and action_context_type = 'PA'
and action_information_category = 'ZA EMP201 PAYROLL DETAILS'
order by payroll_id ;
select action_information1 assignment_id,
action_information2 l_effective_date,
action_information3 employee_number,
action_information4 emp_name,
formatted_canonical(action_information5) paye_remuneration,
formatted_canonical(action_information6) tax,
formatted_canonical(action_information7) leviable_amt,
formatted_canonical(action_information8) sdl_amt,
formatted_canonical(action_information9) uif_remuneration,
formatted_canonical(action_information10) uif_amt,
action_information11 EMP201_status,
action_information12 cnt_paye_perm,
action_information13 cnt_paye_non_perm,
action_information14 raise_warning,
action_information15 asg_terminated,
action_information16 assignment_no
from pay_action_information pai
, pay_assignment_actions paa
where paa.payroll_action_id = p_archive_pact
and pai.action_context_id = paa.assignment_action_id
and pai.action_context_type = 'AAP'
and pai.action_information_category = 'ZA EMP201 ASSIGNMENT DETAILS'
and pai.action_information18 = p_payroll_id
order by employee_number,emp_name;
g_xml_element_table.DELETE;