[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select legislative_parameters,
start_date,
effective_date
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
select legislative_parameters,
start_date,
effective_date
from pay_payroll_actions
where payroll_action_id = cp_payroll_action_id;
sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.payroll_id = ' || ln_payroll_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id
order by paa.assignment_id';
sqlstr := 'select '||l_db_version||' distinct paa.assignment_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id
order by paa.assignment_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,
pay_org_payment_methods_f popm,
pay_pre_payments ppp,
pay_payment_types ppt
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 pai_mag.locked_action_id = ppp.assignment_action_id
and ppp.value > 0
and ppp.org_payment_method_id = popm.org_payment_method_id
and ppa_mag.effective_date between popm.effective_start_date
and popm.effective_end_date
and popm.DEFINED_BALANCE_ID is not null
and popm.payment_type_id = ppt.payment_type_id
and ppt.territory_code = 'US'
and ppt.payment_type_name = 'NACHA'
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
and ppa_mag.ORG_PAYMENT_METHOD_ID = popm.org_payment_method_id);
select 1
from dual
where not exists
(select 1
from pay_pre_payments ppp ,
pay_org_payment_methods_f popm
where ppp.assignment_action_id = cp_prepayment_action_id
and popm.ORG_PAYMENT_METHOD_ID = ppp.org_payment_method_id
and popm.defined_balance_id is not NULL );
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')
);
select /*+ ORDERED */
paa_xfr.assignment_action_id,
paa_xfr.assignment_id,
paa_xfr.tax_unit_id
from
pay_payroll_actions ppa_xfr,
pay_assignment_actions paa_xfr,
pay_action_interlocks pai_pre
where ppa_xfr.report_type = 'XFR_INTERFACE'
and ppa_xfr.report_category = 'RT'
and ppa_xfr.report_qualifier = 'FED'
/* Bug : 7558310
and cp_deposit_end_date between ppa_xfr.start_date
and ppa_xfr.effective_date */
and ppa_xfr.effective_date between cp_deposit_start_date and cp_deposit_end_date
and pay_us_payroll_utils.get_parameter('TRANSFER_CONSOLIDATION_SET_ID',
ppa_xfr.legislative_parameters)
= cp_consolidation_set_id
and paa_xfr.payroll_action_id = ppa_xfr.payroll_action_id
-- the statement below will make sure only Pre Payment Archive Actions are picked up
and substr(paa_xfr.serial_number,1,1) not in ('V', 'B')
and pai_pre.locking_Action_id = paa_xfr.assignment_action_id
and (cp_payroll_id is null
or
pay_us_payroll_utils.get_parameter('TRANSFER_PAYROLL_ID',
ppa_xfr.legislative_parameters)
= cp_payroll_id
)
and paa_xfr.assignment_id between cp_start_person and cp_end_person
and pay_us_employee_payslip_web.get_doc_eit(
'PAYSLIP','PRINT',
'ASSIGNMENT',paa_xfr.assignment_id,
cp_deposit_end_date) = 'Y'
and pay_us_deposit_advice_pkg.check_if_assignment_paid(
pai_pre.locked_action_id,
cp_deposit_start_date,
cp_deposit_end_date,
cp_consolidation_set_id) = 'Y' --Bug 3512116
and not 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 = 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_xfr.assignment_action_id
and rownum < 2
) order by paa_xfr.assignment_id desc;
select pay_assignment_actions_s.nextval
into ln_deposit_action_id
from dual;
hr_utility.trace('Inserted into paa');
update pay_assignment_Actions
set serial_number = ln_nacha_action_id
where assignment_action_id = ln_deposit_action_id;
sqlstr := 'select '||l_db_version||' paa.rowid
from hr_all_organization_units hou,
per_all_people_f ppf,
per_all_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 paf.effective_start_date =
(select max(paf1.effective_start_date)
from per_all_assignments_f paf1
where paf1.assignment_id = paf.assignment_id
and paf1.effective_start_date <= ppa.effective_date
and paf1.effective_end_date >= ppa.start_date
)
and paf.person_id = ppf.person_id
and ppa.effective_date between ppf.effective_start_date
and ppf.effective_end_date
and hou.organization_id
= nvl(paf.organization_id, paf.business_group_id)
order by hou.name,ppf.last_name,ppf.first_name
for update of paa.assignment_id';
select distinct act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa_mag.effective_date,
ppa_mag.action_type
from pay_assignment_actions act,
pay_payroll_actions ppa_dar,
pay_payroll_actions ppa_mag,
pay_org_payment_methods_f popm,
pay_all_payrolls_f ppf,
per_all_assignments_f paf2,
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 = consetid
and (( has.payroll_id is null
and nvl(ppa_mag.payroll_id,ppf.payroll_id) =
nvl(payid, 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 ppf.Payroll_id >= 0
and act.payroll_action_id = ppa_mag.payroll_action_id
and act.action_status = 'C'
and ppa_mag.action_type in ('M','P','U')
and decode(ppa_mag.action_type,'M',
ppa_mag.org_payment_method_id,
popm.org_payment_method_id) = popm.org_payment_method_id
and popm.defined_balance_id is not null
and ppa_mag.effective_date between
popm.effective_start_date and popm.effective_end_date
and hasa.assignment_set_id = has.assignment_set_id
and hasa.assignment_id = act.assignment_id
and hasa.include_or_exclude = 'I'
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 = ppf.payroll_id
and act.assignment_id between stperson and endperson
-- No run results.
and NOT EXISTS (SELECT ' '
FROM pay_pre_payments ppp,
pay_org_payment_methods_f popm
WHERE ppp.assignment_action_id = decode(act.source_action_id,NULL
,act.assignment_action_id,
act.source_action_id) --Bug 3928576.Check only for master actions.
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 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_nacha_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;
select distinct ppp.source_action_id
from pay_pre_payments ppp,
pay_personal_payment_methods_f pppm
where ppp.assignment_action_id = cp_pre_pymt_action_id
and pppm.personal_payment_method_id = ppp.personal_payment_method_id
and pppm.external_account_id is not null
and cp_effective_date between pppm.effective_start_date
and pppm.effective_end_date
and nvl(ppp.value,0) <> 0
order by ppp.source_action_id;
l_paid_actions := 'select distinct '||l_db_version||' act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa_mag.effective_date,
ppa_mag.action_type
from pay_assignment_actions act,
pay_payroll_actions ppa_dar,
pay_payroll_actions ppa_mag,
per_all_assignments_f paf2,
pay_org_payment_methods_f popm, --Bug 3009643
pay_payrolls_f pay --Bug 3343621
where ppa_dar.payroll_action_id = :pactid
and ppa_mag.consolidation_set_id +0 = '|| nvl(ln_consolidation_set_id,0) ||'
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 in (''M'',''P'',''U'')
and decode(ppa_mag.action_type,''M'',
ppa_mag.org_payment_method_id,
popm.org_payment_method_id) = popm.org_payment_method_id -- Bug 3009643
and popm.defined_balance_id is not null -- Bug 3009643
and ppa_mag.effective_date between
popm.effective_start_date and popm.effective_end_date --Bug 3009643
and nvl(ppa_mag.payroll_id,pay.payroll_id) = pay.payroll_id --Bug 3343621
and ppa_mag.effective_date between
pay.effective_start_date and pay.effective_end_date --Bug 3343621
and pay.payroll_id >= 0 --Bug 3343621
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 = pay.payroll_id
and act.assignment_id between :stperson and :endperson
and (' || NVL(ln_payroll_id,-99999) || ' = -99999
or
pay.payroll_id = ' || NVL(ln_payroll_id,-99999) ||'
)
-- No run results.
and NOT EXISTS (SELECT '' ''
FROM pay_pre_payments ppp,
pay_org_payment_methods_f popm
WHERE ppp.assignment_action_id = decode(act.source_action_id,NULL
,act.assignment_action_id,
act.source_action_id) --Bug 3928576.Check only for master actions.
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 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 DESC';
** we need to insert atleast one action for each of the rows that we
** return from the cursor (i.e. one for each assignment/pre-payment action).
**************************************************************************/
hr_utility.trace(' ln_prev_pre_pymt_action_id is'
||to_char(ln_prev_pre_pymt_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 ln_deposit_action_id
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
where assignment_action_id = ln_deposit_action_id;
update pay_assignment_Actions
set serial_number = 'M'||ln_master_action_id
where assignment_action_id = ln_deposit_action_id;
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 = 'M'||ln_master_action_id
where assignment_action_id = ln_deposit_action_id;
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);
lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
hr_assignment_set_amendments hasa
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and paa.assignment_id = paf.assignment_id
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.payroll_id = ' || ln_payroll_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id'
|| ' and paa.assignment_id = hasa.assignment_id'
|| ' and hasa.assignment_set_id = ' || ln_assignment_set_id
|| ' and hasa.include_or_exclude = ''I'''
|| ' order by paf.person_id';
lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and paa.assignment_id = paf.assignment_id
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.payroll_id = ' || ln_payroll_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id
order by paf.person_id';
lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf,
hr_assignment_set_amendments hasa
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and paa.assignment_id = paf.assignment_id
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id'
|| ' and paa.assignment_id = hasa.assignment_id'
|| ' and hasa.assignment_set_id = ' || ln_assignment_set_id
|| ' and hasa.include_or_exclude = ''I'''
|| ' order by paf.person_id';
lv_sqlstr := 'select '||l_db_version||' distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_assignments_f paf
where :payroll_action_id is not null
and ppa.action_type in (''R'',''Q'')
and paa.assignment_id = paf.assignment_id
and ppa.consolidation_set_id = ' || ln_consolidation_set_id
|| ' and ppa.effective_date between ''' || ld_deposit_start_date
|| ''' and ''' || ld_deposit_end_date
|| ''' and paa.payroll_action_id = ppa.payroll_action_id
order by paf.person_id';