[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Purpose : range_cursor to select personids for format37
******************************************************************/
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_year number ;
select to_number(pay_mx_isr_format37.get_parameter('Year',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('LEGAL_EMPLOYER_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('ORG_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('LOC_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('PER_ID',ppa.legislative_parameters)),
pay_mx_isr_format37.get_parameter('CURP',ppa.legislative_parameters),
to_number(pay_mx_isr_format37.get_parameter('ASG_SET',ppa.legislative_parameters)),
ppa.effective_date
into l_year,
l_legal_employer_id,
l_org_id,
l_loc_id,
l_per_id,
l_curp,
l_asg_set_id,
l_effective_date
from pay_payroll_actions ppa /* PYUGEN payroll action id */
where ppa.payroll_action_id = pactid ;
sqlstr := 'select distinct to_number(paa.serial_number)
from pay_payroll_actions ppa,
pay_assignment_actions paa
where ppa.report_type = ''MX_YREND_ARCHIVE''
and ppa.action_status = ''C''
and pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa.legislative_parameters) = ' || l_legal_employer_id ||
' and to_number(to_char(ppa.effective_date,''YYYY'')) = ' || l_year ||
' and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status =''C''
and :payroll_action_id is not null
and NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| l_legal_employer_id ||
' AND to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || l_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa.assignment_action_id )
order by to_number(paa.serial_number) ';
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = p_report_type
and report_qualifier = p_report_qualifier
and report_category = p_report_category
and p_effective_date between
effective_start_date and effective_end_date;
/* when no selection is entered */
if((p_loc_id is null ) and
(p_org_id is null ) and
(p_per_id is null ) and
(p_curp is null ) and
(p_asg_set_id is null )) then
hr_utility.set_location(l_procedure_name, 5);
dbg('Selection criteria is Null') ;
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai,
pay_population_ranges ppr
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND ppr.person_id = to_number(paa_arch.serial_number)
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND to_number(paa_arch.serial_number) between ' || stperson || ' and ' || endperson || '
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
end if; /* End of when no selection is entered */
dbg('Selection criteria is Location') ;
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai,
pay_population_ranges ppr
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || p_effective_date || ''')
between paf.effective_start_date and paf.effective_end_date
AND paf.location_id = ' || p_loc_id || '
AND paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || p_effective_date || ''')
between paf.effective_start_date and paf.effective_end_date
AND paf.location_id = ' || p_loc_id || '
AND paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
dbg('Selection criteria is Organization') ;
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai,
pay_population_ranges ppr
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || p_effective_date || ''')
between paf.effective_start_date and paf.effective_end_date
AND paf.organization_id = ' || p_org_id || '
AND paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || p_effective_date || ''')
between paf.effective_start_date and paf.effective_end_date
AND paf.location_id = ' || p_org_id || '
AND paf.effective_start_date =
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
dbg('Selection criteria is either Employee Name or CURP') ;
c_actions_sql := 'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND paf.PERSON_ID = ' || p_per_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
order by paa_arch.serial_number ';
dbg('Selection criteria is Assignment set') ;
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai,
pay_population_ranges ppr
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'')
order by paa_arch.serial_number ';
'SELECT paa_arch.serial_number,
pai.effective_date,
paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id
FROM per_assignments_f paf,
pay_payroll_actions ppa_arch,
pay_assignment_actions paa_arch,
pay_action_information pai
WHERE ppa_arch.report_type=''MX_YREND_ARCHIVE''
AND to_number(pay_mx_isr_format37.get_parameter(''TRANSFER_LEGAL_EMPLOYER'',ppa_arch.legislative_parameters)) = '
|| p_legal_employer_id ||
' and to_number(to_char(ppa_arch.effective_date,''YYYY'')) = ' || p_year ||
' AND ppa_arch.action_status =''C''
AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND paa_arch.action_status =''C''
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_effective_date || ''')
AND paf.effective_end_date >= ppa_arch.start_date
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and pai.action_information_category = ''MX YREND EE DETAILS''
and pai.action_context_id = paa_arch.assignment_action_id
AND NOT EXISTS(
SELECT ''x''
FROM pay_payroll_actions ppa1,
pay_assignment_actions paa1,
pay_action_interlocks palock
WHERE paa1.payroll_action_id = ppa1.payroll_action_id
AND ppa1.report_type = ''ISR_TAX_FORMAT37''
AND ppa1.report_qualifier = ''DEFAULT''
AND ppa1.report_category = ''REPORT''
AND paa1.action_status = ''C''
AND to_number(pay_mx_isr_format37.get_parameter(''LEGAL_EMPLOYER_ID'',ppa1.legislative_parameters)) ='
|| p_legal_employer_id ||
' and to_number(to_char(ppa1.effective_date,''YYYY'')) = ' || p_year ||
' AND palock.locking_action_id = paa1.assignment_action_id
and palock.locked_action_id = paa_arch.assignment_action_id )
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'')
order by paa_arch.serial_number ';
dbg('Inserting action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
dbg('Before Inserting action interlock record');
dbg('After Inserting action interlock record');
select to_number(pay_mx_isr_format37.get_parameter('Year',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('LEGAL_EMPLOYER_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('ORG_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('LOC_ID',ppa.legislative_parameters)),
to_number(pay_mx_isr_format37.get_parameter('PER_ID',ppa.legislative_parameters)),
pay_mx_isr_format37.get_parameter('CURP',ppa.legislative_parameters),
to_number(pay_mx_isr_format37.get_parameter('ASG_SET',ppa.legislative_parameters)),
ppa.effective_date,
ppa.report_type,
ppa.report_category,
ppa.report_qualifier
into l_year,
l_legal_employer_id,
l_org_id,
l_loc_id,
l_per_id,
l_curp,
l_asg_set_id,
l_effective_date,
l_report_type,
l_report_category,
l_report_qualifier
from pay_payroll_actions ppa /* PYUGEN payroll action id */
where ppa.payroll_action_id = pactid ;
selection citeria only one value can be entered so in case l_curp is not
null then it is safe to assume l_per_id is null */
if l_curp is not null then
l_per_id := l_curp;