The following lines contain the word 'select', 'insert', 'update' or 'delete':
' select ppa.payroll_action_id '||
'from pay_payroll_actions ppa '||
'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 pay_za_irp5_archive_pkg.get_parameter(''CERT_TYPE'',ppa.legislative_parameters) = 1'||
' and pay_za_irp5_archive_pkg.get_parameter(''LEGAL_ENTITY'',ppa.legislative_parameters) = '||P_LEGAL_ENTITY_ID||
' and pay_za_irp5_archive_pkg.get_parameter(''TAX_YEAR'',ppa.legislative_parameters) = '||P_TAX_YEAR||
' and NVL(pay_za_irp5_archive_pkg.get_parameter(''PERIOD_RECON'',ppa.legislative_parameters), ''02'') = NVL('||P_PERIOD_RECON||',''02'')
)';
l_eff_date_sql := ' select max(paa.effective_date) '||
' from pay_payroll_actions paa'||
' where 1 = 1 '||
C_PAYROLL_ACTION_ID;
select pbg.name
into CP_BG_NAME
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id
and l_eff_date between pbg.DATE_FROM and nvl(pbg.DATE_TO,to_date('31-12-4712','DD-MM-YYYY'));
select haou.name, hoi.org_information3
into CP_LE_NAME, CP_TAX_REF
from hr_all_organization_units haou,
hr_organization_information hoi
where haou.organization_id = P_LEGAL_ENTITY_ID
and hoi.organization_id = haou.organization_id
and hoi.org_information_context = 'ZA_LEGAL_ENTITY';
/*l_cert_count_sql := 'select min(pai.action_information1),max(pai.action_information1),count(pai.action_information1)'||
' from pay_action_information pai,'||
' pay_assignment_actions paa'||
' where pai.action_context_id = paa.assignment_action_id'||
' and pai.action_context_type = ''AAP'''||
' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
C_PAYROLL_ACTION_ID;*/
l_cert_count_sql := 'select first_cert,last_cert,cert_cnt from ('||
' select first_value(pai.action_information1) over(order by substr(pai.action_information1,17)) first_cert,'||
' first_value(pai.action_information1) over(order by substr(pai.action_information1,17) DESC nulls last) last_cert,'||
' count(pai.action_information1) over() cert_cnt'||
' from pay_action_information pai,'||
' pay_assignment_actions paa'||
' where pai.action_context_id = paa.assignment_action_id'||
' and pai.action_context_type = ''AAP'''||
' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
C_PAYROLL_ACTION_ID ||
' ) where rownum < 2';
l_man_cert_count_sql := 'select count(*)'||
' from pay_action_information pai,'||
' pay_assignment_actions paa'||
' where pai.action_context_id = paa.assignment_action_id'||
' and pai.action_context_type = ''AAP'''||
' and pai.action_information_category = ''ZATYE_EMPLOYEE_INFO'''||
C_PAYROLL_ACTION_ID||
' and pai.action_information28 is not null ';