[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lpad(segment8,6,'0')
FROM hr_soft_coding_keyflex kff, pay_all_payrolls_f ppf
WHERE ppf.soft_coding_keyflex_id = kff.soft_coding_keyflex_id
AND ppf.payroll_id = p_payroll_id
AND g_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date;
select pai_1.action_information1 cert_num
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai_1
, pay_action_information pai_2
, hr_organization_information hoi
where ppa.business_group_id = P_BUSINESS_GROUP_ID
and ppa.action_type='X'
and ppa.report_type='ZA_TYE'
and ppa.action_status='C'
and get_parameter('TAX_YEAR', ppa.legislative_parameters) = p_year
and get_parameter('LEGAL_ENTITY', ppa.legislative_parameters) = hoi.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hoi.org_information3 = p_paye_ref
and ppa.payroll_action_id <> p_archive_pact
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = p_assignment_id
and pai_1.action_context_type = 'AAP'
and pai_1.action_context_id = paa.assignment_action_id
and pai_1.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and pai_2.action_context_type = 'AAP'
and pai_2.action_context_id = paa.assignment_action_id
and pai_2.action_information_category = 'ZATYE_EMPLOYEE_CONTACT_INFO'
and pai_1.action_information30 = pai_2.action_information30
and pai_1.action_information1 like p_paye_ref || p_year || p_period || '%'
and pai_2.action_information1 like p_paye_ref || p_year || p_period || '%'
and pai_1.action_information28 is NULL -- last active certificate number
-- matching certificate type
AND ( (p_cert_type = 'MAIN' AND pai_2.action_information26 = 'MAIN')
or
(p_cert_type = 'LMPSM' AND pai_2.action_information26 = 'LMPSM' AND p_directive_num = pai_1.action_information18)
)
;
select max(substr(pai.action_information1,23,8)) max_serial_num
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai
, hr_organization_information hoi
where ppa.business_group_id = P_BUSINESS_GROUP_ID
and ppa.action_type='X'
and ppa.report_type='ZA_TYE'
and ppa.action_status='C'
and get_parameter('TAX_YEAR', ppa.legislative_parameters) = p_year
and get_parameter('LEGAL_ENTITY', ppa.legislative_parameters) = hoi.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hoi.org_information3 = p_paye_ref
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and pai.action_context_type= 'AAP'
and pai.action_context_id = paa.assignment_action_id
and pai.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and pai.action_information1 like p_paye_ref || p_year || '__' || p_payroll_ref || '%'
and ( pai.action_information28 is null or pai.action_information28 = 'O' ); -- do not consider M/OM
select distinct paa.assignment_action_id prev_assact
from pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_information pai_1
, hr_organization_information hoi
where ppa.business_group_id = P_BUSINESS_GROUP_ID
and ppa.action_type='X'
and ppa.report_type='ZA_TYE'
and ppa.action_status='C'
and get_parameter('TAX_YEAR', ppa.legislative_parameters) = P_TAX_YEAR
and get_parameter('LEGAL_ENTITY', ppa.legislative_parameters) = hoi.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY'
and hoi.org_information3 = p_paye_ref
and ppa.payroll_action_id <> p_archive_pact
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.assignment_id = p_assignment_id
and pai_1.action_context_type = 'AAP'
and pai_1.action_context_id = paa.assignment_action_id
and pai_1.action_information_category = 'ZATYE_EMPLOYEE_INFO'
and pai_1.action_information1 like p_paye_ref || P_TAX_YEAR || p_period || '%'
and pai_1.action_information28 is null; -- not 'O'/'M'/'OM'
update pay_action_information
set action_information28 = 'O'
where action_context_type = 'AAP'
and action_context_id = rec_prev_cert.prev_assact
and action_information28 is null;
select lpad(org_information3,10,'0')
from hr_organization_information
where organization_id = p_legal_entity_id
and org_information_context = 'ZA_LEGAL_ENTITY';
l_sql := 'select pai.assignment_id,
paa.assignment_action_id p_archive_assacct,
pai.action_information1 CERTIFICATE_NUMBER,
pai.action_information2 CERTIFICATE_TYPE,
pai.action_information18 DIRECTIVE_NUMBER1,
pai.action_information30 TEMP_CERTIFICATE_NUMBER
from pay_action_information pai,
pay_action_information pai2,
pay_assignment_actions paa,
hr_all_organization_units org,
per_all_assignments_f ass,
pay_payroll_actions ppa
where pai.action_context_id = paa.assignment_action_id
and paa.payroll_action_id = :1
and pai.action_context_type = ''AAP''
and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO''
and pai2.action_context_id = paa.assignment_action_id
and pai2.action_context_type = ''AAP''
and pai2.action_information_category = ''ZATYE_EMPLOYEE_CONTACT_INFO''
and pai.action_context_id = pai2.action_context_id
and pai.action_information30 = pai2.action_information30
and ( pai.action_information28 is null
or
pai.action_information28 not in (''M'',''OM'')
)
and pai.Action_Information2 = :2
and paa.ASSIGNMENT_ID = ass.ASSIGNMENT_ID
and paa.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
and (select least(ppa.EFFECTIVE_DATE, (select max(effective_end_date)
from per_all_assignments_f paaf
where paaf.assignment_id = ass.assignment_id))
from dual)
between ass.EFFECTIVE_START_DATE and ass.EFFECTIVE_END_DATE
and ass.organization_id = org.organization_id (+)
and paa.ASSIGNMENT_ID = NVL(:3, paa.ASSIGNMENT_ID) order by '|| C_SORT_ORDER;
select legislative_parameters
into leg_param
from pay_payroll_actions
where payroll_action_id = l_archive_pact;
select action_information12
into l_terminated_bef_sep
from pay_action_information
where action_context_type = 'AAP'
and action_context_id = l_employee_info.p_archive_assacct
and action_information_category = 'ZATYE_EMPLOYEE_TAX_AND_REASONS'
and action_information30 = l_employee_info.TEMP_CERTIFICATE_NUMBER;
select least(g_effective_date,max(effective_end_date))
into l_effective_date
from per_all_assignments_f
where assignment_id = l_asg_id;
select payroll_id
into l_payroll_id
from per_all_assignments_f
where assignment_id = l_asg_id
and l_effective_date between effective_start_date and effective_end_date ;
select action_information26
into l_cert_type
from pay_action_information
where action_context_type = 'AAP'
and action_context_id = l_employee_info.p_archive_assacct
and action_information_category = 'ZATYE_EMPLOYEE_CONTACT_INFO'
and action_information30 = l_employee_info.TEMP_CERTIFICATE_NUMBER;
update pay_action_information
set action_information1 = l_new_cert_num
where action_context_type = 'AAP'
and action_context_id = l_employee_info.p_archive_assacct
and action_information30 = l_employee_info.TEMP_CERTIFICATE_NUMBER;