[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.
22-Sep-2011 Pkoduri 115.24 bug#12377778 - Added the order by clause to the range cursor,
for the scenario where we run deposit advice with
Assignment Set.
This is to ensure that all the employees in that range
are picked up - because later we process only the emp
between the start and end assignments returned by range cursor.
05-MAR-2012 sgotlasw 115.25 " CANADIAN DEPOSIT ADVICE (XML) ENHANCEMENT "
Added following methods and set of new variables.
qualifying_proc - This method is invoked from
action creation code of generic package,
PAY_GENERIC_UPGRADE.
This qualifying procedure name is mentioned
PAY_REPORT_GROUPS table for 'PAYSLIP_REPORT_CA'
check_if_qualified_for_CA
08-MAR-2012 sgotlasw 115.26 Commented out condition in csr_asg, csr_inc_asg.
12-APR-2012 sgotlasw 115.27 Modified cursor in archive action creation code to pick
all the employees when 'Canadian Deposit Advice' is run
for particular date range.
11-MAY-2012 sgotlasw 115.28 Modified 'c_direct_deposit_run' cursor to check if the
payment is voided.
01-MAR-2013 sgotlasw 115.29 Created 'c_non_void_pymts' cursor to check if any of
non void payments exists for given archive action id
before creating assignment_actions.
--
--
*/
---------------------------------- 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
order by paf.person_id '; --12377778
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
/* Bug 14061942 : Added to check if payment is voided. */
and not exists
(
select 1
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = paa_mag.assignment_action_id
and pai.locking_Action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'D'
)
);
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 */
/* Bug 13934170 : Modified above date range condition as follows */
and ppa_pyarch.effective_date between cp_deposit_start_date
and cp_deposit_end_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 1
from dual
where exists
(select 1
from PAY_EMP_NET_DIST_ACTION_INFO_V net_pay,
pay_assignment_actions paa_mag,
pay_payroll_actions ppa_mag
where net_pay.action_context_id = cp_action_id
and net_pay.pre_payment_id = paa_mag.pre_payment_id
and ppa_mag.payroll_action_id = paa_mag.payroll_action_id
and ppa_mag.action_type = 'M'
and paa_mag.action_status = 'C'
and net_pay.pre_payment_id is not null
and not exists
(
select 1
from pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
where pai.locked_action_id = paa_mag.assignment_action_id
and pai.locking_Action_id = paa.assignment_action_id
and paa.payroll_action_id = ppa.payroll_action_id
and ppa.action_type = 'D'
)
);
select pay_assignment_actions_s.nextval
into ln_deposit_action_id
from dual;
-- insert the action record.
hr_nonrun_asact.insact(ln_deposit_action_id,
ln_assignment_id,
pactid, chunk, ln_tax_unit_id);
hr_utility.trace('Inserted into paa, New Asg_act_id:'||to_char(ln_deposit_action_id));
-- insert an interlock to this action.
hr_nonrun_asact.insint(ln_deposit_action_id, ln_dd_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;
SELECT /* 'Y' */
distinct paa.assignment_id
,pai.action_context_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_lookups hrl
,pay_action_information pai
,per_time_periods ptp
WHERE /* paa.assignment_id = c_assignment_id */
ppa.effective_Date BETWEEN c_start_date
AND c_end_date
AND ppa.report_type = hrl.meaning
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = c_legislation_code
AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
= NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
--
--
AND ppa.payroll_action_id = paa.payroll_action_id
-- AND paa.source_action_id IS NULL --RLN P1 8941027
--
--
AND pai.assignment_id = paa.assignment_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMPLOYEE DETAILS'
AND pai.action_context_id = paa.assignment_action_id
AND ptp.time_period_id = pai.ACTION_INFORMATION16;
SELECT /* 'Y' */
distinct paa.assignment_id
,pai.action_context_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,hr_lookups hrl
,hr_assignment_set_amendments hasa
,pay_action_information pai
,per_time_periods ptp
WHERE ppa.effective_Date BETWEEN c_start_date
AND c_end_date
AND ppa.report_type = hrl.meaning
AND hrl.lookup_type = 'PAYSLIP_REPORT_TYPES'
AND hrl.lookup_code = c_legislation_code
AND NVL(c_payroll_id,NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1))
= NVL(pay_payslip_report.get_parameter(ppa.legislative_parameters,c_pa_token),-1)
AND c_consolidation_set_id = pay_payslip_report.get_parameter(ppa.legislative_parameters,c_cs_token)
AND ppa.payroll_action_id = paa.payroll_action_id
--AND paa.source_action_id IS NULL --RLN P1 894102
AND paa.assignment_id = hasa.assignment_id
AND hasa.assignment_set_id = c_assignment_set_id
AND hasa.include_or_exclude = 'I'
AND pai.assignment_id = paa.assignment_id
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'EMPLOYEE DETAILS'
AND pai.action_context_id = paa.assignment_action_id
AND ptp.time_period_id = pai.ACTION_INFORMATION16;
SELECT include_or_exclude
FROM hr_assignment_set_amendments
WHERE assignment_set_id = c_assignment_set_id
AND assignment_id = nvl(c_assignment_id,assignment_id);
select paa.assignment_action_id
from pay_action_interlocks paci,
pay_assignment_actions paa,
pay_payroll_actions ppa
where paci.locking_action_id = cp_assignment_action_id
and paa.assignment_action_id = paci.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('P', 'U');
select paa_pre.assignment_action_id
from pay_action_interlocks pai_run,
pay_action_interlocks pai_pre,
pay_assignment_actions paa_pre,
pay_payroll_actions ppa_pre
where pai_run.locking_action_id = cp_assignment_action_id
and pai_pre.locked_action_id = pai_run.locked_action_id
and paa_pre.assignment_Action_id = pai_pre.locking_action_id
and ppa_pre.payroll_action_id = paa_pre.payroll_action_id
and ppa_pre.action_type in ('P', 'U');
SELECT '1'
INTO lv_rev_run_exists
FROM dual
where exists
(Select /*+ ORDERED */ 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 = ln_prepay_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')
);