[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 distinct 'Y'
from pay_upgrade_definitions pud
,pay_upgrade_status pus
where pud.short_name = 'US_INV_DEDN_UPGRADE'
and pud.legislation_code = 'US'
and pud.upgrade_definition_id = pus.upgrade_definition_id
and pus.status = 'C'
and pus.business_group_id = g_business_group_id;
select parameter_value
from pay_action_parameters
where parameter_name = 'US_ADVANCED_WAGE_ATTACHMENT';
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_pay.run_result_id
from pay_element_entry_values_f peev,
pay_element_entry_values_f peev_pay,
pay_input_values_f piv_att,
pay_element_entries_f peef,
pay_run_results prr_att,
pay_run_results prr_pay,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp,
pay_input_values_f piv_pay,
pay_run_result_values prrv_att,
pay_run_result_values prrv_pay
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 piv_att.input_value_id = prrv_att.input_value_id
and prrv_att.result_value = peev.screen_entry_value
and piv_pay.input_value_id = peev_pay.input_value_id
and ppa.effective_date between piv_pay.effective_start_date
and piv_pay.effective_end_date
and ppa.date_earned between peev_pay.effective_start_date
and peev_pay.effective_end_date
and piv_pay.input_value_id = prrv_pay.input_value_id
and upper(piv_pay.name) = 'PAY VALUE'
and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
and peef.element_entry_id = peev.element_entry_id
and peef.element_entry_id = peev_pay.element_entry_id
and paa.assignment_Action_id = prr_att.assignment_Action_id
and prr_att.run_result_id = prrv_att.run_result_id
and paa.assignment_action_id = prr_pay.assignment_action_id
and prr_pay.run_result_id = prrv_pay.run_result_id
and prr_att.element_type_id = peef.element_type_id
and prr_pay.element_type_id = peef.element_type_id
order by prr_pay.run_result_id;
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_att.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_att,
pay_run_results prr_pay,
pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai,
pay_pre_payments ppp,
pay_input_values_f piv_pay,
pay_run_result_values prrv_att,
pay_run_result_values prrv_pay,
pay_element_types_f pet,
pay_element_types_f pet_calc,
pay_element_classifications pec
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 piv_att.input_value_id = prrv_att.input_value_id
and prrv_att.result_value = peev.screen_entry_value
and prrv_att.run_result_id = prr_att.run_result_id
and paa.assignment_Action_id = prr_att.assignment_Action_id
and prr_att.element_type_id = peef.element_type_id
and peef.element_type_id = pet.element_type_id
and pet.classification_id = pec.classification_id
and pec.classification_name = 'Involuntary Deductions'
and pec.legislation_code = 'US'
and pec.business_group_id IS NULL
and fnd_number.canonical_to_number(pet.element_information5) = pet_calc.element_type_id
and pet_calc.element_name like pet.element_name || '%Calculator'
and pet_calc.element_type_id = piv_pay.element_type_id
and NVL(ppa.date_earned, ppa.effective_date) between piv_pay.effective_start_date and piv_pay.effective_end_date
and piv_pay.input_value_id = prrv_pay.input_value_id
and upper(piv_pay.name) = 'PAY VALUE'
and fnd_number.number_to_canonical(ppp.value) = prrv_pay.result_value
and prr_pay.run_result_id = prrv_pay.run_result_id
and prr_pay.assignment_action_id = paa.assignment_action_id
and prr_pay.element_type_id = pet_calc.element_type_id
order by prr_att.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'),
ppa.payroll_id
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,
g_payroll_id
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;
SELECT pea.segment1,pea.segment3,pea.segment5,pea.segment6
FROM pay_external_accounts pea, pay_personal_payment_methods_f ppm,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppm.personal_payment_method_id = p_personal_payment_method_id
AND ppa.effective_date BETWEEN ppm.effective_start_date AND ppm.effective_end_date
AND pea.external_account_id (+) = ppm.external_account_id;
SELECT to_char(ppa.effective_date,'YYYY/MM/DD') effective_date,
to_char(ppa.overriding_dd_date,'YYYY/MM/DD') overriding_dd_date
FROM pay_payroll_actions ppa, pay_org_payment_methods_f opm, pay_assignment_actions paa
WHERE ppa.payroll_action_id = p_payroll_action_id
AND opm.org_payment_method_id = ppa.org_payment_method_id
AND opm.org_payment_method_id = p_org_payment_method_id
AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT ppp.org_payment_method_id, hou.organization_id, paa.assignment_action_id
FROM pay_pre_payments ppp, pay_org_payment_methods_f opm,
hr_organization_units hou, hr_organization_information hoi,
pay_payroll_actions ppa, pay_assignment_actions paa
WHERE opm.org_payment_method_id = ppp.org_payment_method_id
AND opm.org_payment_method_id = ppa.org_payment_method_id
AND opm.org_payment_method_id = p_org_payment_method_id
AND ppa.payroll_action_id = p_payroll_action_id
AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
AND hou.business_group_id = ppa.business_group_id
AND opm.business_group_id = ppa.business_group_id
AND hou.organization_id = hoi.organization_id
AND hoi.org_information_context = 'CLASS'
AND hoi.org_information1 = 'HR_LEGAL'
AND hoi.org_information2 = 'Y'
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.tax_unit_id = hou.organization_id
AND paa.pre_payment_id = ppp.pre_payment_id
AND EXISTS
( SELECT 1
FROM per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND ppa.effective_date BETWEEN paf.effective_start_date and paf.effective_end_date
)
ORDER BY ppp.org_payment_method_id, hou.organization_id;
SELECT flv.meaning
FROM fnd_lookup_types flt, fnd_lookup_values flv
WHERE flt.lookup_type = 'PAYMENT_TRX_CONC_IDENTS'
AND flt.application_id = (SELECT application_id FROM fnd_application
WHERE application_short_name = 'PAY')
AND flt.lookup_type = flv.lookup_type
AND flv.lookup_code = 'NACHA'
AND flv.language = 'US'
AND flv.enabled_flag = 'Y'
AND sysdate between flv.start_date_active and nvl(flv.end_date_active, sysdate);
SELECT opm.pmeth_information3, nvl(opm.pmeth_information17,
hoi.org_information4) org_information4
FROM pay_org_payment_methods_f opm, pay_payroll_actions ppa,
pay_assignment_actions paa, hr_organization_information hoi
WHERE ppa.payroll_action_id = p_payroll_action_id
AND ppa.effective_date BETWEEN opm.effective_start_date AND opm.effective_end_date
AND ppa.org_payment_method_id = opm.org_payment_method_id
AND ppa.payment_type_id = opm.payment_type_id
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_action_id = p_assignment_action_id
and paa.tax_unit_id = hoi.organization_id
and hoi.org_information_context = 'NACHA Rules';