[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT context_id
FROM ff_contexts
WHERE context_name = p_context_name;
SELECT distinct /*+ INDEX(prr PAY_RUN_RESULTS_N50,PAY_RUN_RESULTS_N1) */
et.element_type_id
,et.element_name
,iv.input_value_id
,iv.name input_value_name
,rr.run_result_id
,aa.assignment_action_id
,aa.assignment_id
,nvl(prrv.result_value,'Unknown') result_value
FROM pay_element_types_f et
,pay_input_values_f iv
,pay_run_results rr
,per_assignments_f paf
,pay_assignment_actions aa
,pay_run_result_values prrv
WHERE paf.business_group_id = p_business_group_id
AND paf.assignment_id = aa.assignment_id
AND et.element_name IN ('Court Order','Court Order NTPP')
AND et.legislation_code = 'GB'
AND iv.element_type_id = et.element_type_id
AND iv.name = 'Reference'
AND iv.legislation_code = 'GB'
AND rr.element_type_id = et.element_type_id
AND aa.assignment_action_id = rr.assignment_action_id
AND prrv.run_result_id = rr.run_result_id
AND prrv.input_value_id = iv.input_value_id
AND NOT EXISTS (SELECT NULL
FROM pay_action_contexts pac
WHERE pac.assignment_id = aa.assignment_id
AND pac.assignment_action_id = aa.assignment_action_id
AND pac.context_id = l_context_id
AND pac.context_value = nvl(prrv.result_value, 'Unknown'));
INSERT INTO pay_action_contexts
(assignment_action_id
,assignment_id
,context_id
,context_value)
(
select
l_result_rec.assignment_action_id
,l_result_rec.assignment_id
,l_context_id
,l_result_rec.result_value
from dual
where NOT EXISTS (SELECT NULL
FROM pay_action_contexts pac
WHERE pac.assignment_id = l_result_rec.assignment_id
AND pac.assignment_action_id = l_result_rec.assignment_action_id
AND pac.context_id = l_context_id
AND pac.context_value = nvl(l_result_rec.result_value, 'Unknown')));
UPDATE pay_run_result_values prrv
SET prrv.result_value = 'Unknown'
WHERE prrv.input_value_id = l_result_rec.input_value_id
AND prrv.run_result_id = l_result_rec.run_result_id
AND prrv.result_value is null;
SELECT name
FROM per_business_groups
WHERE business_group_id =p_bg_id;
SELECT to_date(SYSDATE,'DD-MM-YYYY')
FROM dual;
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbd.dimension_name = '_PER_CO_TD_REF_ITD'
AND pbd.business_group_id IS NULL
AND pbd.legislation_code = 'GB'
AND pbt.balance_name = 'Court Order'
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'GB'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.business_group_id IS NULL
AND pdb.legislation_code = 'GB';
SELECT distinct ppf.full_name ,
ppf.person_id,
ppf.national_identifier
FROM per_people_f ppf
,pay_payrolls_f pf
,per_assignments_f paf
WHERE ppf.business_group_id = p_bg_id
AND pf.payroll_id = paf.payroll_id
AND paf.person_id = ppf.person_id
ORDER by ppf.full_name;
SELECT distinct pf.payroll_name
FROM per_people_f ppf
,pay_payrolls_f pf
,per_assignments_f paf
WHERE ppf.business_group_id = p_bg_id
AND pf.payroll_id = paf.payroll_id
AND paf.person_id = ppf.person_id
AND ppf.person_id = p_person_id;
SELECT max(paa.assignment_action_id) assignment_action_id,
pac.context_value context_value,
nvl(pay_balance_pkg.get_value(p_defined_balance_id, max(pac.assignment_action_id), null,null, fc.context_id, pac.context_value,null,null),0) paid_value
FROM per_assignments_f paf,
pay_assignment_actions paa,
pay_action_contexts pac,
ff_contexts fc,
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv
,pay_input_values_f piv1
,pay_run_results prr1
,pay_run_result_values prrv1
WHERE paf.assignment_id = paa.assignment_id
AND paf.person_id = p_person_id
AND fc.context_id = pac.context_id
AND fc.context_name = 'SOURCE_TEXT'
AND paa.assignment_id = pac.assignment_id
AND paa.action_status = 'C'
AND pet.element_name IN ('Court Order','Court Order NTPP')
AND pet.legislation_code = 'GB'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Reference'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND prr.assignment_action_id = paa.assignment_action_id
AND piv.legislation_code = 'GB'
AND paa.assignment_action_id = prr1.assignment_action_id
AND prrv1.run_result_id = prr1.run_result_id
AND prr1.element_type_id = pet.element_type_id
AND prr.run_result_id = prr1.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND prrv1.result_value in ('CTO','CCAEO')
AND piv1.name = 'Type'
AND piv1.legislation_code = 'GB'
AND pac.context_value not in ('Unknown')
group by fc.context_id, pac.context_value;
SELECT nvl(PRRV.result_value ,'0') result_value,
nvl((prrv.result_value - p_paid_value),'0') overpaid
FROM
pay_element_types_f pet,
pay_input_values_f piv,
pay_run_results prr,
pay_run_result_values prrv,
pay_input_values_f piv1,
pay_run_results prr1,
pay_run_result_values prrv1
WHERE pet.element_name IN ('Court Order','Court Order NTPP')
AND pet.legislation_code = 'GB'
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Initial Debt'
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = piv.input_value_id
AND prr.assignment_action_id in (p_assignment_action_id)
AND prrv.result_value is not null
AND prr1.assignment_action_id in (p_assignment_action_id)
AND prrv1.run_result_id = prr1.run_result_id
AND prr1.element_type_id = pet.element_type_id
AND prr.run_result_id = prr1.run_result_id
AND prrv1.input_value_id = piv1.input_value_id
AND prrv1.result_value in (p_context_value)
AND piv1.name = 'Reference'
AND piv1.legislation_code = 'GB';