[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select fai.value
from ff_archive_items fai,
ff_database_items fdi
where fai.context1 = p_archive_action_id
and fai.user_entity_id=fdi.user_entity_id
and fdi.user_name=p_db_name;
select fai.value
from ff_archive_items fai,
ff_database_items fdi,
ff_archive_item_contexts fac,
ff_contexts ffc
where fai.context1 = p_asg_act_id
and fai.archive_item_id = fac.archive_item_id
and fai.user_entity_id = fdi.user_entity_id
and fdi.user_name = p_db_name
and fac.context = p_context
and fac.context_id = ffc.context_id
and ffc.context_name = p_context_name;
select distinct 'Y'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = l_archive_type
and to_char(ppa.effective_date,'YYYY') = p_reporting_year
and ppa.business_group_id = p_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and to_number(pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))= nvl(p_tax_unit_id,to_number(pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters)))
and ((p_report_mode = 'REPRINT'
and exists (select distinct 'Y' from pay_action_interlocks pai,pay_assignment_actions paa2,pay_payroll_actions ppa2
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.effective_date = ppa.effective_date /*Added for 10381064*/
and paa2.tax_unit_id = nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
and ppa2.report_type = p_report_type)
and not exists (select 1 from pay_assignment_actions paa1,pay_payroll_actions ppa1
where ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date = ppa.effective_date /*Added for 10381064*/
and paa1.assignment_id = paa.assignment_id
and paa1.assignment_action_id > paa.assignment_action_id
and nvl(paa1.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa1.legislative_parameters))
= nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
and ppa1.report_type = l_archive_type))
or (p_report_mode = 'LATEST'
and not exists (select distinct 'Y' from pay_action_interlocks pai,pay_assignment_actions paa2,pay_payroll_actions ppa2
where pai.locked_action_id = paa.assignment_action_id
and pai.locking_action_id = paa2.assignment_action_id
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.effective_date = ppa.effective_date /*Added for 10381064*/
and nvl(paa2.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa2.legislative_parameters))
= nvl(paa.tax_unit_id,pay_ca_eoy_t4_amend_arch.get_parameter(l_gre_or_pre,ppa.legislative_parameters))
and ppa2.report_type in (l_archive_type,p_report_type) )
and ppa.payroll_action_id = (select max(payroll_action_id) from pay_payroll_actions ppa1 where ppa1.payroll_action_id = ppa.payroll_action_id)));
select distinct 'Y'
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type in (l_main_report_type,l_amend_report_type)
and to_char(ppa.effective_date,'YYYY') = p_reporting_year
and ppa.business_group_id = p_business_group_id
and ppa.payroll_action_id = paa.payroll_action_id
and decode(ppa.report_type,l_amend_report_type,instr(ppa.legislative_parameters,'MODE=LATEST'),1) <> 0
and paa.tax_unit_id = nvl(p_tax_unit_id,paa.tax_unit_id)
and substr(paa.serial_number,1,2) = nvl(p_province,substr(paa.serial_number,1,2))
and not exists
(select 1
from pay_assignment_actions paa1,
pay_payroll_actions ppa1
where ppa1.report_type in (l_main_report_type,l_amend_report_type)
and ppa1.payroll_action_id = paa1.payroll_action_id
and ppa1.effective_date = ppa.effective_date
and paa1.tax_unit_id = paa.tax_unit_id
and paa1.assignment_id = paa.assignment_id
and substr(paa1.serial_number,1,2) = substr(paa.serial_number,1,2)
and substr(paa1.serial_number,l_substr_st_pos,14)||lpad(paa1.assignment_action_id,14,0) > substr(paa.serial_number,l_substr_st_pos,14)||lpad(paa.assignment_action_id,14,0)
and decode(ppa1.report_type,l_amend_report_type,instr(ppa1.legislative_parameters,'MODE=LATEST'),1) <> 0)
and ((p_report_mode = 'LATEST'
and not exists
(select 1
from pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
where pact.report_type = p_report_type
and pact.payroll_action_id = passt.payroll_action_id
and pact.effective_date = ppa.effective_date
and passt.assignment_action_id = pail.locking_action_id
and pail.locked_action_id = paa.assignment_action_id)
)
or (p_report_mode = 'REPRINT'
and exists
(select pail.locked_action_id
from pay_action_interlocks pail,
pay_payroll_actions pact,
pay_assignment_actions passt
where pact.report_type = p_report_type
and pact.payroll_action_id = passt.payroll_action_id
and pact.effective_date = ppa.effective_date
and passt.assignment_action_id = pail.locking_action_id
and pail.locked_action_id = paa.assignment_action_id
and passt.tax_unit_id = paa.tax_unit_id))
);
SELECT decode(p_report_type,'PAYCAT4AMPDF','CAEOY_T4_AMEND_PP','CAEOY_T4A_AMEND_PP')
INTO l_archive_type
FROM DUAL;
SELECT decode(p_report_type,'PAYCARL1AMPDF','CAEOY_RL1_AMEND_PP','CAEOY_RL2_AMEND_PP')
INTO l_archive_type
FROM DUAL;