[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
tax_unit_id updated yet. */
CURSOR csr_assignment_action IS
SELECT aa.assignment_action_id
FROM pay_assignment_actions aa
WHERE aa.payroll_action_id = p_payroll_action_id
AND aa.assignment_id = p_assignment_id
AND aa.tax_unit_id IS NULL;
update pay_assignment_actions aa
set aa.tax_unit_id = p_tax_unit_id,
aa.serial_number = l_serial_no
where aa.assignment_action_id = l_assignment_action_id;
Notes : The criteria for selecting the people cannot be done
simply using SQL.It is done by first using a PLSQL cursor
which makes an educated guess about the people to include NB.
It will always include all the correct people even though
some may not be valid. The second step is to further check
each person found and apply further checks. If these are
passed then they are added to the list (create an assignment
action) otherwise they are discarded.
*/
function generate_people_list
(
p_report_type varchar2,
p_state varchar2,
p_trans_legal_co_id varchar2,
p_business_group_id number,
p_period_end date,
p_quarter_start date,
p_quarter_end date
) return number is
l_person_id number;
select paf.person_id person_id,
paf.assignment_id assignment_id,
fnd_number.canonical_to_number(scl.segment1) tax_unit_id,
paf.effective_start_date effective_start_date,
paf.effective_end_date effective_end_date
from per_assignments_f paf,
hr_soft_coding_keyflex scl
where paf.business_group_id = l_bus_group_id
and paf.assignment_type = 'E'
and paf.primary_flag = 'Y'
and paf.payroll_id is not null
and paf.effective_start_date <= l_end_date3
and (paf.effective_end_date >= l_end_date1
or paf.effective_end_date >= l_end_date2
or paf.effective_end_date >= l_end_date3)
and scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
and exists ( select null
from hr_organization_information hoi2
where hoi2.organization_id = paf.organization_id
and hoi2.org_information_context = 'CLASS'
and hoi2.org_information1 = 'HR_ESTAB'
and hoi2.org_information2 = 'Y')
and exists(
select null
from hr_organization_information hoi
where hoi.organization_id = paf.organization_id
and hoi.org_information_context =
'Worksite Filing')
group by paf.person_id,
paf.assignment_id,
fnd_number.canonical_to_number(scl.segment1),
paf.effective_start_date,
paf.effective_end_date
order by 1, 2, 4 asc, 5, 3;
select parameter_value
into l_chunk_size
from pay_action_parameters
where parameter_name = 'CHUNK_SIZE';
/* A payroll action has been created. So, update the status to created */
if l_payroll_action_created then
/* Update the population status of the payroll action to indicate that all
the assignment actions have been created for it. */
update pay_payroll_actions ppa
set ppa.action_population_status = 'C'
where ppa.payroll_action_id = l_payroll_action_id;
select (to_char(sysdate,'YYMMDD'))
into l_tape_creation_date
from sys.dual;
/* Request has been accepted so update payroll action with the
request details. */
update pay_payroll_actions ppa
set ppa.request_id = l_request_id
where ppa.payroll_action_id = p_payroll_action_id;
select PA.effective_date,
ltrim(substr(PA.legislative_parameters, 11,5)),
ltrim(substr(PA.legislative_parameters, 17,5)),
to_char(PA.effective_date,'YYYY'),
to_char(fnd_number.canonical_to_number(to_char(PA.effective_date,'MM'))/3),
ltrim(substr(PA.legislative_parameters, 23,5)) ,
to_char(business_group_id)
into l_effective_date,
l_report_type,
l_state,
l_reporting_year,
l_reporting_quarter,
l_trans_legal_co_id,
l_business_group_id
from pay_payroll_actions PA
where PA.payroll_action_id = p_payroll_action_id;
update pay_payroll_actions pa
set PA.action_status = 'M'
where PA.payroll_action_id = p_payroll_action_id;
update pay_assignment_actions AA
set AA.action_status = 'M'
where AA.payroll_action_id = p_payroll_action_id;
update pay_assignment_actions AA
set AA.action_status = 'C'
where AA.payroll_action_id = p_payroll_action_id;
/* Process completed successfully. Update the status of the payroll and
assignments actions. */
Pay_Mag_Utils.Update_Action_Status(l_payroll_action_id);