[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(peev.screen_entry_value,'NULL') ref_no
,nvl(peef.entry_information2,'NONE') -- FIPS code
,nvl(peef.entry_information1,'N') -- Medical Indicator
,ppa.effective_date payment_date
,prr.run_result_id
from pay_element_entry_values_f peev,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_results prr,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp
WHERE ppp.value = p_amount
and ppp.pre_payment_id = p_ppm_id
and ppp.assignment_action_id = pai.locking_action_id
and pai.locked_action_id = paa.assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.action_type in ('R', 'Q')
and ((paa.source_action_id is not null and ppa.run_type_id is not null) or
(paa.source_action_id is null and ppa.run_type_id is null))
and ppp.personal_payment_method_id = peef.personal_payment_method_id
and peef.assignment_id = paa.assignment_id
and ppa.date_earned between peev.effective_start_date
and peev.effective_end_date
and ppa.date_earned between peef.effective_start_date
and peef.effective_end_date
and piv_att.input_value_id = peev.input_value_id
and upper(piv_att.name) = 'ATTACHMENT NUMBER'
and ppa.effective_Date between piv_att.effective_start_date
and piv_att.effective_end_date
and peef.element_entry_id = peev.element_entry_id
and paa.assignment_Action_id = prr.assignment_Action_id
and prr.element_type_id = peef.element_type_id
order by prr.run_result_id;
select TO_CHAR(FORMULA_ID) INTO ff_formula_id
from FF_FORMULAS_F
where g_effective_date between EFFECTIVE_START_DATE and
EFFECTIVE_END_DATE
and FORMULA_NAME = p_formula_name;
update PAY_EXTERNAL_ACCOUNTS a
set a.PRENOTE_DATE = nvl(to_date(g_direct_dep_date,'YYMMDD'),
g_effective_date)
where a.PRENOTE_DATE is null
and a.EXTERNAL_ACCOUNT_ID =
( select b.EXTERNAL_ACCOUNT_ID
from PAY_PERSONAL_PAYMENT_METHODS_F b
where b.PERSONAL_PAYMENT_METHOD_ID =
g_personal_payment_method_id
and g_effective_date between b.EFFECTIVE_START_DATE
and b.EFFECTIVE_END_DATE);
/* Select all the relevent information using payroll action id */
select ppa.business_group_id,
ppa.effective_date,
to_char(ppa.overriding_dd_date,'YYMMDD'),
ppa.org_payment_method_id,
ppa.legislative_parameters,
decode(nvl(to_char(opm.defined_balance_id),'Y'),'Y','Y','N')
into g_business_group_id,
g_effective_date,
g_direct_dep_date,
g_org_payment_method_id,
g_legislative_parameters,
g_csr_org_pay_third_party
from pay_payroll_actions ppa,
pay_org_payment_methods_f opm
where ppa.payroll_action_id = g_payroll_action_id
and opm.ORG_PAYMENT_METHOD_ID = ppa.org_payment_method_id
and ppa.effective_date between opm.EFFECTIVE_START_DATE
and opm.EFFECTIVE_END_DATE;