[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Deposit Advice. Added distinct in select
clause and assignment_action_id in order by
clause in c_action cursor to get uniq deposit
advice.
22-Jul-2003 vpandya 115.16 Bug 3046204: Changed action_creation for live
Deposit Advice to print zero net pay deposit
advice.
24-Mar-2004 ssattini 115.17 Bug 3331023: 11510 changes done in
range_cursor and action_creation procedures
by removing rule hint and tuning them. Still
changes need to be done for archive_action_
creation procedure.
27-Jul-2004 ssattini 115.20 Bug 3438254: 11510 Performance changes done.
Changed the cursors and logic in
archive_action_creation procedure, also
added get_payroll_action procedure and
check_if_assignment_paid function. Tuned
c_actions_asg_set cursor in action_creation
procedure. Used the get_payroll_action
in range_cursor, action_creation procedures.
Added assignment_set validation logic for
c_actions_zero_pay records in
action_creation procedure.
15-Mar-2005 ssouresr 115.21 The condition that the consolidation set
should be linked to a payroll has been
removed from the range cursor and the
action creation functions
27-Apr-2005 sackumar 115.22 Bug 3800169. Modification in the logic of
action_creation_procedure. Merge the Zero pay
cursor in the c_action and c_actions_asg_set
cursor and introduce a flag_variable for zero pay
in the cursor fetch loop.
16-JUN-2005 mmukherj Removed the changes mentioned in 115.22.
in 115.22 the changes has been done by
merging the two cursors c_actions_zero_pay
and c_actions_asg_set. So what was happening
is that the cursoe c_actions_asg_set was
being called only if the assignment_set has
been passed , so the zero pay actions was not
checked if the Deposit Advice was not run
with assignment set. That was not the intention
of this fix.
--
--
*/
---------------------------------- get_payroll_action -------------------
/**********************************************************************
** PROCEDURE : get_payroll_action
** Description: Bug 3438254
** This procedure returns the details for payroll action for
** deposit advice. This is called in the range cursor,
** action_creation and archive_action_creation procedures.
**********************************************************************/
PROCEDURE get_payroll_action(p_payroll_action_id in number
,p_deposit_start_date out nocopy date
,p_deposit_end_date out nocopy date
,p_assignment_set_id out nocopy number
,p_payroll_id out nocopy number
,p_consolidation_set_id out nocopy number
)
IS
cursor c_get_payroll_action
(cp_payroll_action_id in number) is
select legislative_parameters,
start_date,
effective_date
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
sqlstr := 'select
distinct paf.person_id
from
hr_assignment_set_amendments hasa,
per_all_assignments_f paf,
pay_payroll_actions ppa
where
ppa.payroll_action_id = :PACTID and
hasa.assignment_set_id = ' || to_char(l_asg_set_id) ||
' and hasa.assignment_id = paf.assignment_id and
ppa.effective_date between
paf.effective_start_date and
paf.effective_end_date';
sqlstr := 'select '||l_db_version||' distinct pos.person_id
from pay_assignment_actions act,
per_all_assignments_f asg,
per_periods_of_service pos,
pay_payroll_actions pa2,
pay_payroll_actions pa1,
pay_all_payrolls_f ppf
where pa1.payroll_action_id = :payroll_action_id
and ppf.payroll_id = pycadar_pkg.get_parameter(''PAYROLL_ID'',
pa1.legislative_parameters)
and pa2.consolidation_set_id =
pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
pa1.legislative_parameters)
and pa2.payroll_id = ppf.payroll_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and pa2.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and pa2.payroll_action_id= act.payroll_action_id
and asg.assignment_id = act.assignment_id
and pa2.effective_date between
asg.effective_start_date and asg.effective_end_date
and pos.period_of_service_id = asg.period_of_service_id
order by pos.person_id';
sqlstr := 'select '||l_db_version||' distinct pos.person_id
from pay_assignment_actions act,
per_all_assignments_f asg,
per_periods_of_service pos,
pay_payroll_actions pa2,
pay_payroll_actions pa1,
pay_all_payrolls_f ppf
where pa1.payroll_action_id = :payroll_action_id
and pa2.consolidation_set_id =
pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
pa1.legislative_parameters)
and pa2.payroll_id = ppf.payroll_id
and pa2.effective_date between
pa1.start_date and pa1.effective_date
and pa2.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and act.payroll_action_id = pa2.payroll_action_id
and asg.assignment_id = act.assignment_id
and pa2.effective_date between
asg.effective_start_date and asg.effective_end_date
and pos.period_of_service_id = asg.period_of_service_id
order by pos.person_id';
select 1
from dual
where exists
(select 1
from pay_action_interlocks pai_mag,
pay_assignment_actions paa_mag,
pay_payroll_actions ppa_mag
where pai_mag.locked_action_id = cp_prepayment_action_id
and pai_mag.locking_Action_id = paa_mag.assignment_action_id
and paa_mag.payroll_action_id = ppa_mag.payroll_action_id
and ppa_mag.action_type = 'M'
and ppa_mag.effective_date between cp_deposit_start_date
and cp_deposit_end_date
and ppa_mag.consolidation_set_id +0 = cp_consolidation_set_id
);
select 1
from dual
where not exists
(select 1
from pay_pre_payments ppp
where ppp.assignment_action_id = cp_prepayment_action_id
);
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_all_assignments_f paf1,
per_periods_of_service pos,
pay_payroll_actions ppa_dar,
pay_payroll_actions ppa_mag_pmts,
pay_all_payrolls_f ppf
where ( ppa_dar.payroll_action_id = pactid
and ppa_mag_pmts.consolidation_set_id =
pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',
ppa_dar.legislative_parameters)
and ppa_mag_pmts.payroll_id = ppf.payroll_id
and ppa_mag_pmts.effective_date between ppa_dar.start_date
and ppa_dar.effective_date
and ppa_mag_pmts.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and act.payroll_action_id = ppa_mag_pmts.payroll_action_id
and act.action_status = 'C'
and ppa_mag_pmts.action_type in ('P', 'U')
and paf1.assignment_id = act.assignment_id
and ppa_mag_pmts.effective_date between
paf1.effective_start_date and paf1.effective_end_date
and pos.period_of_service_id = paf1.period_of_service_id
and pos.person_id between stperson and endperson
and (paf1.payroll_id =
pycadar_pkg.get_parameter('PAYROLL_ID',
ppa_dar.legislative_parameters)
or pycadar_pkg.get_parameter('PAYROLL_ID',
ppa_dar.legislative_parameters)
is null)
-- No run results.
AND NOT EXISTS (SELECT ' '
FROM pay_pre_payments ppp,
pay_org_payment_methods_f popm
WHERE ppp.assignment_action_id = act.assignment_action_id
and ppp.org_payment_method_id = popm.org_payment_method_id
and popm.defined_balance_id IS NOT NULL)
-- and is not a reversal.
AND NOT EXISTS
( Select ' '
from pay_action_interlocks int2,
pay_action_interlocks int4,
pay_assignment_actions paa4,
pay_payroll_actions ppa_run, --- RUN
pay_payroll_actions pact4, --- Reversal
pay_assignment_actions paa_run --- RUN
where int2.locking_action_id = act.assignment_action_id -- prepayment action
and int2.locked_action_id = paa_run.assignment_action_id
and paa_run.payroll_action_id = ppa_run.payroll_action_id
and ppa_run.action_type in ('R', 'Q')
and paa_run.assignment_action_id = int4.locked_action_id
and int4.locking_action_id = paa4.assignment_action_id
and pact4.payroll_action_id = paa4.payroll_action_id
and pact4.action_type = 'V'
)
)
order by pos.person_id, act.assignment_id DESC;
select act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_all_assignments_f paf1,
per_all_assignments_f paf2,
per_periods_of_service pos,
pay_payroll_actions ppa_dar,
pay_payroll_actions ppa_mag,
pay_all_payrolls_f ppf,
hr_assignment_sets has,
hr_assignment_set_amendments hasa
where ppa_dar.payroll_action_id = pactid
and has.assignment_set_id = p_assignment_set_id
and ppa_mag.effective_date between
ppa_dar.start_date and ppa_dar.effective_date
and ppa_mag.consolidation_set_id =
pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',ppa_dar.legislative_parameters)
and (( has.payroll_id is null
and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
nvl(pycadar_pkg.get_parameter('PAYROLL_ID',ppa_dar.legislative_parameters),
nvl(ppa_mag.payroll_id,ppf.payroll_id))
) or
nvl(ppa_mag.payroll_id,has.payroll_id) = has.payroll_id
)
and ppa_mag.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and act.payroll_action_id = ppa_mag.payroll_action_id
and act.action_status = 'C'
and ppa_mag.action_type = 'M'
and hasa.assignment_set_id = has.assignment_set_id
and hasa.assignment_id = act.assignment_id
and hasa.include_or_exclude = 'I'
and paf1.assignment_id = act.assignment_id
and ppa_mag.effective_date between
paf1.effective_start_date and paf1.effective_end_date
and paf2.assignment_id = act.assignment_id
and ppa_dar.effective_date between
paf2.effective_start_date and paf2.effective_end_date
and paf2.payroll_id + 0 = paf1.payroll_id + 0
and pos.period_of_service_id = paf1.period_of_service_id
and pos.person_id between stperson and endperson
and (paf1.payroll_id = ppa_dar.payroll_id or ppa_dar.payroll_id is null)
and not exists
( select ''
from pay_action_interlocks int2,
pay_action_interlocks int3,
pay_assignment_actions paa4,
pay_payroll_actions ppa_run, --- RUN
pay_payroll_actions pact4, --- Reversal
pay_assignment_actions paa_run, --- RUN
pay_assignment_actions paa_pp --- PREPAY
where int3.locked_action_id = act.assignment_action_id
and int3.locking_action_id = paa_pp.assignment_action_id
and int2.locked_action_id = paa_pp.assignment_action_id
and int2.locking_action_id = paa_run.assignment_action_id
and paa_run.payroll_action_id = ppa_run.payroll_action_id
and ppa_run.action_type in ('R', 'Q')
and paa_run.assignment_action_id = int3.locked_action_id
and int3.locking_action_id = paa4.assignment_action_id
and pact4.payroll_action_id = paa4.payroll_action_id
and pact4.action_type = 'V'
)
order by act.assignment_id;
select locked_action_id
from pay_action_interlocks pai
where pai.locking_action_id = cp_dd_action_id; --Direct Deposit dd
select distinct ppp.source_action_id
from pay_pre_payments ppp
where ppp.assignment_action_id = cp_pre_pymt_action_id
order by ppp.source_action_id;
select assignment_action_id
from pay_action_interlocks pai,
pay_assignment_actions paa
where pai.locking_action_id = cp_pre_pymt_action_id
and paa.assignment_Action_id = pai.locked_action_id
and paa.run_type_id is null
order by action_sequence desc;
l_actions := 'select '||l_db_version||' distinct act.assignment_action_id,
act.assignment_id,
act.tax_unit_id
from pay_assignment_actions act,
per_all_assignments_f paf1,
per_all_assignments_f paf2,
per_periods_of_service pos,
pay_payroll_actions ppa_dar,
pay_payroll_actions ppa_mag,
pay_all_payrolls_f ppf
where ppa_dar.payroll_action_id = :pactid
and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
NVL(pycadar_pkg.get_parameter(''PAYROLL_ID'',
ppa_dar.legislative_parameters),
nvl(ppa_mag.payroll_id,ppf.payroll_id))
and nvl(ppa_mag.payroll_id,ppf.payroll_id) = ppf.payroll_id
and ppa_mag.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and nvl(ppf.multi_assignments_flag,''N'') = ''N''
and ppa_mag.consolidation_set_id + 0 =
pycadar_pkg.get_parameter(''CONSOLIDATION_SET_ID'',
ppa_dar.legislative_parameters)
and ppa_mag.effective_date between
ppa_dar.start_date and ppa_dar.effective_date
and act.payroll_action_id = ppa_mag.payroll_action_id
and act.action_status = ''C''
and ppa_mag.action_type = ''M''
and paf1.assignment_id = act.assignment_id
and ppa_mag.effective_date between
paf1.effective_start_date and paf1.effective_end_date
and paf2.assignment_id = act.assignment_id
and ppa_dar.effective_date between
paf2.effective_start_date and paf2.effective_end_date
and paf2.payroll_id + 0 = paf1.payroll_id + 0
and pos.period_of_service_id = paf1.period_of_service_id
and pos.person_id between :stperson and :endperson
and (( paf1.payroll_id = pycadar_pkg.get_parameter(''PAYROLL_ID'',
ppa_dar.legislative_parameters) )
or
( pycadar_pkg.get_parameter(''PAYROLL_ID'',
ppa_dar.legislative_parameters) is null )
)
and not exists
(
Select ''''
from pay_action_interlocks int2,
pay_action_interlocks int3,
pay_assignment_actions paa4,
pay_payroll_actions ppa_run, --- RUN
pay_payroll_actions pact4, --- Reversal
pay_assignment_actions paa_run, --- RUN
pay_assignment_actions paa_pp --- PREPAY
where int3.locked_action_id = act.assignment_action_id
and int3.locking_action_id = paa_pp.assignment_action_id
and int2.locked_action_id = paa_pp.assignment_action_id
and int2.locking_action_id = paa_run.assignment_action_id
and paa_run.payroll_action_id = ppa_run.payroll_action_id
and ppa_run.action_type in (''R'', ''Q'')
and paa_run.assignment_action_id = int3.locked_action_id
and int3.locking_action_id = paa4.assignment_action_id
and pact4.payroll_action_id = paa4.payroll_action_id
and pact4.action_type = ''V''
)
order by act.assignment_id, act.assignment_action_id';
** we need to insert one action for each of the rows that we
** return from the cursor (i.e. one for each
** assignment/pre-payment source).
**************************************************************/
hr_utility.trace(' ln_prev_source_action_id is'
||to_char(ln_prev_source_action_id));
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
hr_utility.trace('Inserted into paa');
hr_utility.trace('Inserted into interlock');
hr_utility.trace('serial number updated if loop ');
update pay_assignment_Actions
set serial_number = 'P'||ln_source_action_id
--set serial_number = ln_source_action_id
where assignment_action_id = lockingactid;
update pay_assignment_Actions
set serial_number = 'M'||ln_master_action_id
--set serial_number = ln_master_action_id
where assignment_action_id = lockingactid;
select pay_assignment_actions_s.nextval
into ln_deposit_action_id
from dual;
hr_utility.trace(' NZ Inserted into paa');
update pay_assignment_Actions
set serial_number = 'Z'||ln_direct_dep_act_id
where assignment_action_id = ln_deposit_action_id;
select paa_pyarch.assignment_action_id,
paa_pyarch.assignment_id,
paa_pyarch.tax_unit_id
from pay_payroll_actions ppa_pyarch,
pay_assignment_actions paa_pyarch,
per_assignments_f paf,
pay_action_interlocks pai_pre
where ppa_pyarch.report_type = 'PY_ARCHIVER'
and ppa_pyarch.report_category = 'RT'
and ppa_pyarch.report_qualifier = 'PYCAPYAR'
and cp_deposit_end_date between ppa_pyarch.start_date
and ppa_pyarch.effective_date
and cp_deposit_end_date between paf.effective_start_date
and paf.effective_end_date
and pycadar_pkg.get_parameter('CONSOLIDATION_SET_ID',
ppa_pyarch.legislative_parameters)
= cp_consolidation_set_id
and paa_pyarch.payroll_action_id = ppa_pyarch.payroll_action_id
-- the statement below will make sure only Pre Payment Archive
-- Actions are picked up
and substr(paa_pyarch.serial_number,1,1) not in ('V', 'B')
and paa_pyarch.assignment_id = paf.assignment_id
and ppa_pyarch.effective_date between paf.effective_start_date
and paf.effective_end_date
and pai_pre.locking_Action_id = paa_pyarch.assignment_action_id
and (cp_payroll_id is null
or
pycadar_pkg.get_parameter('PAYROLL_ID',
ppa_pyarch.legislative_parameters)
= cp_payroll_id
)
and paf.person_id between cp_start_person and cp_end_person
and pay_us_employee_payslip_web.get_doc_eit(
'PAYSLIP','PRINT',
'ASSIGNMENT',paf.assignment_id,
cp_deposit_end_date
) = 'Y'
and pycadar_pkg.check_if_assignment_paid(
pai_pre.locked_action_id,
cp_deposit_start_date,
cp_deposit_end_date,
cp_consolidation_set_id) = 'Y'
and not exists
(Select 1
from pay_action_interlocks pai_run, --Pre > Run
pay_action_interlocks pai_rev, --Run > Rev
pay_assignment_actions paa_rev, --Rev
pay_payroll_actions ppa_rev --Rev
where pai_run.locking_action_id = pai_pre.locked_action_id
and pai_rev.locked_action_id = pai_run.locked_action_id
and paa_rev.assignment_action_id = pai_run.locking_action_id
and ppa_rev.payroll_action_id = paa_rev.payroll_action_id
and ppa_rev.action_type in ('V')
)
and exists (select 1
from pay_action_information pai
where pai.action_context_id = paa_pyarch.assignment_action_id)
order by paf.person_id, paf.assignment_id desc;
select pay_assignment_actions_s.nextval
into ln_deposit_action_id
from dual;
hr_utility.trace('Inserted into paa, New Asg_act_id:'||to_char(ln_deposit_action_id));
update pay_assignment_Actions
set serial_number = ln_dd_action_id
where assignment_action_id = ln_deposit_action_id;
procname in varchar2, /* name of the select statement to use */
sqlstr in out nocopy varchar2, /* string holding the sql statement */
len out nocopy number /* length of the sql string */
) is
begin
-- go through each of the sql sub strings and see if
-- they are needed.
sqlstr := 'select paa.rowid
from hr_organization_units hou,
per_people_f ppf,
per_assignments_f paf,
pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.payroll_action_id = :pactid
and paa.payroll_action_id = ppa.payroll_action_id
and paa.assignment_id = paf.assignment_id
and ppa.effective_date between
paf.effective_start_date and paf.effective_end_date
and paf.person_id = ppf.person_id
and ppa.effective_date between
ppf.effective_start_date and ppf.effective_end_date
and paf.organization_id = hou.organization_id
order by hou.name,ppf.last_name,ppf.first_name
for update of paf.assignment_id';
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code;
select 1 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( ( p_person_language is null and language = 'US' ) or
( p_person_language is not null and language = p_person_language ) )
union all
select 2 ord, meaning
from fnd_lookup_values
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and ( language = 'US' and p_person_language is not null
and language <> p_person_language )
order by 1;