[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ ORDERED
USE_NL(PAA, PPA)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA) */
ppa.business_group_id
from pay_assignment_actions paa,
pay_payroll_actions ppa
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select /*+ INDEX(PER_ASSIGNMENTS_F_PK PA) */
pa.business_group_id
from per_assignments_f pa
where pa.assignment_id = p_assignment_id
and p_effective_date
between pa.effective_start_date and pa.effective_end_date;
select pbg.legislation_code
from per_business_groups pbg
where pbg.business_group_id = p_business_group_id;
select /*+ ORDERED
USE_NL(PBT, PDB, PBD)
INDEX(PAY_BALANCE_TYPES_UK2 PBT)
INDEX(PAY_DEFINED_BALANCES_UK2 PDB)
INDEX(PAY_BALANCE_DIMENSIONS_PK PBD) */
pdb.defined_balance_id
from pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_dimensions pbd
where pbt.balance_name = p_balance_name
and nvl(pbt.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pbt.legislation_code,l_legislation_code) = l_legislation_code
and pbd.dimension_name = p_dimension_name
and nvl(pbd.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pbd.legislation_code,l_legislation_code) = l_legislation_code
and pdb.balance_type_id = pbt.balance_type_id
and pdb.balance_dimension_id = pbd.balance_dimension_id;
select /*+ INDEX(PAY_BALANCE_TYPES_UK2 PBT) */
pbt.balance_type_id
from pay_balance_types pbt
where pbt.balance_name = p_balance_name
and nvl(pbt.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pbt.legislation_code,p_legislation_code) = p_legislation_code;
SELECT /*+ ORDERED
USE_NL(ASSACT, PACT, FEED, RR, TARGET)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK ASSACT)
INDEX(PAY_PAYROLL_ACTIONS_PK PACT)
INDEX(PAY_BALANCE_FEEDS_F_FK1 FEED)
INDEX(PAY_RUN_RESULTS_N50 RR)
INDEX(PAY_RUN_RESULT_VALUES_PK TARGET) */
nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
FROM pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_balance_feeds_f FEED,
pay_run_results RR,
pay_run_result_values TARGET
where ASSACT.assignment_action_id = p_assignment_action_id
and PACT.payroll_action_id = ASSACT.payroll_action_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id = p_balance_type_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date;
SELECT /*+ ORDERED
USE_NL(ASSACT, PACT, FEED, RR, TARGET)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK ASSACT)
INDEX(PAY_PAYROLL_ACTIONS_PK PACT)
INDEX(PAY_BALANCE_FEEDS_F_FK1 FEED)
INDEX(PAY_RUN_RESULTS_N50 RR)
INDEX(PAY_RUN_RESULT_VALUES_PK TARGET) */
nvl(sum(fnd_number.canonical_to_number(TARGET.result_value) * FEED.scale),0)
FROM
pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_balance_feeds_f FEED,
pay_run_results RR,
pay_run_result_values TARGET
where ASSACT.assignment_action_id = p_assignment_action_id
and PACT.payroll_action_id = ASSACT.payroll_action_id
and RR.assignment_action_id = ASSACT.assignment_action_id
and RR.status in ('P','PA')
and TARGET.run_result_id = RR.run_result_id
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id = p_balance_type_id
and PACT.effective_date between
FEED.effective_start_date and FEED.effective_end_date;
select parameter_value
into g_low_volume
from pay_action_parameters
where parameter_name = 'LOW_VOLUME';
select /*+ ORDERED
USE_NL(PAA, PPA, PRR, PRRV)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
INDEX(PAY_RUN_RESULTS_N50 PRR)
INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
sum(fnd_number.canonical_to_number(prrv.result_value))
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id + 0 = p_element_type_id
and prr.status in ('P','PA')
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = p_input_value_id;
select /*+ ORDERED
USE_NL(PAA, PPA, PRR, PRRV)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
INDEX(PAY_RUN_RESULTS_N50 PRR)
INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
min(prrv.result_value)
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id + 0 = p_element_type_id
and prr.status in ('P','PA')
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = p_input_value_id;
select /*+ ORDERED
USE_NL(PAA, PPA, PRR, PRRV)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
INDEX(PAY_RUN_RESULTS_N50 PRR)
INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
min(fnd_number.canonical_to_number(prrv.result_value))
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id + 0 = p_element_type_id
and prr.status in ('P','PA')
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = p_input_value_id;
select /*+ ORDERED
USE_NL(PAA, PPA, PRR, PRRV)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA)
INDEX(PAY_RUN_RESULTS_N50 PRR)
INDEX(PAY_RUN_RESULT_VALUES_PK PRRV) */
min(fnd_date.canonical_to_date(prrv.result_value))
from pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv
where paa.assignment_action_id = p_assignment_action_id
and ppa.payroll_action_id = paa.payroll_action_id
and prr.assignment_action_id = paa.assignment_action_id
and prr.element_type_id + 0 = p_element_type_id
and prr.status in ('P','PA')
and prrv.run_result_id = prr.run_result_id
and prrv.input_value_id = p_input_value_id;
select /*+ ORDERED
USE_NL(PIV, PLIV, PEE, PEEV)
INDEX(PAY_INPUT_VALUES_F_PK PIV)
INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
min( decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
'N',peev.screen_entry_value))
from pay_input_values_f piv,
pay_link_input_values_f pliv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE piv.input_value_id = p_input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and pliv.input_value_id = piv.input_value_id
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type,'E') = 'E'
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and peev.input_value_id = piv.input_value_id;
select /*+ ORDERED
USE_NL(PIV, PLIV, PEE, PEEV)
INDEX(PAY_INPUT_VALUES_F_PK PIV)
INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
min(fnd_number.canonical_to_number(decode(decode(substr(piv.uom,1,1),'M','N','N','N','I','N','H','N',null),'N',
decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
'N',peev.screen_entry_value),null)))
from pay_input_values_f piv,
pay_link_input_values_f pliv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE piv.input_value_id = p_input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and pliv.input_value_id = piv.input_value_id
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type,'E') = 'E'
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and peev.input_value_id = piv.input_value_id;
select /*+ ORDERED
USE_NL(PIV, PLIV, PEE, PEEV)
INDEX(PAY_INPUT_VALUES_F_PK PIV)
INDEX(PAY_LINK_INPUT_VALUES_F_N2 PLIV)
INDEX(PAY_ELEMENT_ENTRIES_F_N51 PEE)
INDEX(PAY_ELEMENT_ENTRY_VALUES_F_N50 PEEV) */
min (fnd_date.canonical_to_date(decode(substr(piv.uom,1,1),'D',
decode(piv.hot_default_flag, 'Y',nvl(peev.screen_entry_value,nvl(pliv.default_value,piv.default_value)),
'N',peev.screen_entry_value),null)))
from pay_input_values_f piv,
pay_link_input_values_f pliv,
pay_element_entries_f pee,
pay_element_entry_values_f peev
WHERE piv.input_value_id = p_input_value_id
and p_effective_date
between piv.effective_start_date and piv.effective_end_date
and pliv.input_value_id = piv.input_value_id
and p_effective_date
between pliv.effective_start_date and pliv.effective_end_date
and pee.element_link_id = pliv.element_link_id
and pee.assignment_id = p_assignment_id
and nvl(pee.entry_type,'E') = 'E'
and p_effective_date
between pee.effective_start_date and pee.effective_end_date
and peev.element_entry_id = pee.element_entry_id
and peev.effective_start_date = pee.effective_start_date
and peev.effective_end_date = pee.effective_end_date
and peev.input_value_id = piv.input_value_id;
select /*+ INDEX(PAY_ELEMENT_TYPES_F_UK2 PET) */
min(pet.element_type_id)
from pay_element_types_f pet
where pet.element_name = p_element_name
and nvl(pet.business_group_id,p_business_group_id) = p_business_group_id
and nvl(pet.legislation_code,p_legislation_code) = p_legislation_code;
select /*+ INDEX(PAY_INPUT_VALUES_F_UK2 PIV) */
min(piv.input_value_id)
from pay_input_values_f piv
where piv.element_type_id = p_element_type_id
and piv.name=p_input_value_name;
select piv.input_value_id
from pay_input_values_f piv
where piv.element_type_id = l_element_type_id
and piv.display_sequence = p_input_value_disp_seq
/* Validate if input value is owned as JP legislation code */
and piv.legislation_code = decode(p_legislation_code,'JP',p_legislation_code,null)
/* Validate if there are another input value of same display sequence */
and not exists(
select null
from pay_input_values_f piv2
where piv2.element_type_id = piv.element_type_id
and piv2.display_sequence = piv.display_sequence
and piv2.input_value_id <> piv.input_value_id);
SELECT SAVE_RUN_BALANCE
INTO l_save_run_balance
FROM PAY_DEFINED_BALANCES
WHERE BALANCE_TYPE_ID = P_BALANCE_TYPE_ID
AND nvl(BUSINESS_GROUP_ID,p_business_group_id) = p_business_group_id
AND nvl(LEGISLATION_CODE,p_legislation_code) = p_legislation_code;
SELECT BALANCE_NAME
FROM PAY_BALANCE_TYPES
WHERE nvl(BUSINESS_GROUP_ID,p_business_group_id) = p_business_group_id
AND nvl(LEGISLATION_CODE,p_legislation_code) = p_legislation_code
AND BALANCE_TYPE_ID = p_balance_type_id;
SELECT /*+ ORDERED
USE_NL(PAA, PPA)
INDEX(PAY_ASSIGNMENT_ACTIONS_PK PAA)
INDEX(PAY_PAYROLL_ACTIONS_PK PPA) */
PAA.ASSIGNMENT_ID,
PPA.EFFECTIVE_DATE
FROM PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
WHERE PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = p_assignment_action_id;