[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select db.defined_balance_id
from pay_defined_balances db,
pay_balance_types bt,
pay_balance_dimensions bdim
where bt.balance_name = p_balance_name
and bt.legislation_code = 'FR'
and bdim.dimension_name = p_dimension_name
and bdim.legislation_code = 'FR'
and db.balance_type_id = bt.balance_type_id
and db.balance_dimension_id = bdim.balance_dimension_id;
select decode(et.element_name
,'FR_FAMILY_MAINTENANCE',10
,'FR_FAMILY_MAINTENANCE_ARREARS',15
,'FR_TAX',20
,'FR_FINE',30
,'FR_MISCELLANEOUS',40
,'FR_SEIZURE',50) PRIORITY
,max(decode(iv.name
,'Monthly Payment',rrv.result_value)) MONTHLY_PAYMENT
,max(decode(iv.name,'Amount',rrv.result_value)) AMOUNT
,max(decode(iv.name
,'Processing Order',rrv.result_value)) PROCESSING_ORDER
,et.element_name ELEMENT_NAME
,rr.source_id SOURCE_ID
,rr.run_result_id RUN_RESULT_ID
from pay_element_types_f et
,pay_run_results rr
,pay_run_result_values rrv
,pay_input_values_f iv
where et.element_name in ('FR_FAMILY_MAINTENANCE'
,'FR_FAMILY_MAINTENANCE_ARREARS'
,'FR_TAX'
,'FR_FINE'
,'FR_MISCELLANEOUS'
,'FR_SEIZURE')
and et.legislation_code = 'FR'
and et.business_group_id is null
and p_date_earned between et.effective_start_date
and et.effective_end_date
and et.element_type_id = rr.element_type_id
and rr.assignment_action_id = p_assignment_action_id
and rr.run_result_id = rrv.run_result_id
and rrv.input_value_id = iv.input_value_id
and iv.element_type_id = et.element_type_id
and p_date_earned between iv.effective_start_date
and iv.effective_end_date
and iv.name in ('Monthly Payment'
,'Amount'
,'Processing Order')
and rr.status in ('P','PA')
group by decode(et.element_name
,'FR_FAMILY_MAINTENANCE',10
,'FR_FAMILY_MAINTENANCE_ARREARS',15
,'FR_TAX',20
,'FR_FINE',30
,'FR_MISCELLANEOUS',40
,'FR_SEIZURE',50)
,et.element_name
,rr.source_id
,rr.run_result_id
order by 1, 4;
/* BUG 2481752 Cursor updated to reflect a fix by the PHQ team */
Cursor csr_no_of_dpndts is
select count(*)
from PER_CONTACT_RELATIONSHIPS PCR
, per_all_assignments_f a
, pay_assignment_actions aa
where PCR.person_id = a.person_id
and a.assignment_id = aa.assignment_id
and aa.assignment_action_id = p_assignment_action_id
and p_date_earned
between a.effective_start_date and a.effective_end_date
and PCR.DEPENDENT_FLAG = 'Y'
and ( pcr.date_start is NULL OR p_date_earned BETWEEN
pcr.date_start AND NVL(pcr.date_end, p_date_earned) )
and (pcr.date_start IS NOT NULL OR
EXISTS (SELECT person_id
FROM per_all_people_f
WHERE person_id = pcr.contact_person_id
AND p_date_earned BETWEEN
effective_start_date AND effective_end_date));
select fnd_number.canonical_to_number(cinst.value) rate,
fnd_number.canonical_to_number(cinst2.value) high_value,
fnd_number.canonical_to_number(cinst3.value) low_value
from pay_user_column_instances_f cinst
,pay_user_columns c
,pay_user_column_instances_f cinst2
,pay_user_columns c2
,pay_user_column_instances_f cinst3
,pay_user_columns c3
,pay_user_tables tab
,pay_user_rows_f r
where tab.user_table_name = 'FR_COURT_ORDER_BANDS'
and tab.user_key_units = 'N'
and c.user_table_id = tab.user_table_id
and c.legislation_code = 'FR'
and c.user_column_name = 'DEDUCTION_RATE'
and cinst.user_column_id = c.user_column_id
and l_effective_date between cinst.effective_start_date and cinst.effective_end_date
and cinst.legislation_code = 'FR'
and c2.user_table_id = tab.user_table_id
and c2.legislation_code = 'FR'
and c2.user_column_name = 'Upper Bound'
and cinst2.user_column_id = c2.user_column_id
and l_effective_date between cinst2.effective_start_date and cinst2.effective_end_date
and cinst2.legislation_code = 'FR'
and c3.user_table_id = tab.user_table_id
and c3.legislation_code = 'FR'
and c3.user_column_name = 'Lower Bound'
and cinst3.user_column_id = c3.user_column_id
and l_effective_date between cinst3.effective_start_date and cinst3.effective_end_date
and cinst3.legislation_code = 'FR'
and r.user_table_id = tab.user_table_id
and r.user_row_id = cinst.user_row_id
and r.user_row_id = cinst2.user_row_id
and r.user_row_id = cinst3.user_row_id;
select fnd_number.canonical_to_number(R.row_low_range_or_name) low_value
,fnd_number.canonical_to_number(R.row_high_range) high_value
,cinst.value rate
from pay_user_column_instances_f cinst
,pay_user_columns c
,pay_user_rows_f r
,pay_user_tables tab
where tab.user_table_name = 'FR_COURT_ORDER_BANDS'
and c.user_table_id = tab.user_table_id
and c.legislation_code = 'FR'
and c.user_column_name = 'DEDUCTION_RATE'
and cinst.user_column_id = c.user_column_id
and r.user_table_id = tab.user_table_id
and l_effective_date between r.effective_start_date and r.effective_end_date
and r.legislation_code = 'FR'
and tab.user_key_units = 'N'
and cinst.user_row_id = r.user_row_id
and l_effective_date between cinst.effective_start_date and cinst.effective_end_date
and cinst.legislation_code = 'FR';
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('SESSIONID');
Then determine how much of the net pay fits into each band selected but don't
mess the bottom value of zero
=============================================================================*/
if r.low_value = 0 then
l_low_value := 0;
l_sql_string := l_sql_string||' select '||court_order(l_index).outstanding_balance||' balance'
||','||l_index ||' l_index'
||' from dual';