[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- select balance_type_id
-- from pay_balance_types
-- where balance_name = g_sal;
-- select balance_type_id
-- from pay_balance_types
-- where balance_name = g_material;
SELECT /*+ ORDERED
INDEX(BACT_ASSACT PAY_ASSIGNMENT_ACTIONS_N1)
INDEX(PEPD PAY_ENTRY_PROCESS_DETAILS_FK2)
INDEX(BACT PAY_PAYROLL_ACTIONS_PK)
INDEX(RR PAY_RUN_RESULTS_N51)
INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(PACT PAY_PAYROLL_ACTIONS_PK)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK)
INDEX(TARGET PAY_RUN_RESULT_VALUES_N50)
INDEX(FEED PAY_BALANCE_FEEDS_F_N2) */
-- retro salary diff(adjusted amount to pay actually)
-- = retro salary to pay at target month(BACT) - retro salary paid at target month(PACT)
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_3,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,-1,0)))),0),
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_2,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,-1,0)))),0),
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_1,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,-1,0)))),0),
-- retro salary paid(actual paid retro salary at the month)(PACT)
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1))),0),
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1))),0),
nvl(sum(decode(FEED.balance_type_id,
g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1))),0),
-- retro salary materials diff
-- = retro salary to pay at target month(BACT) - retro salary paid at target month(PACT)
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_3,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,-1,0)))),0),
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_2,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,-1,0)))),0),
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_1,1,0)
+ decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,-1,0)))),0),
-- retro salary materials paid(actual paid retro salary at the month)(PACT)
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1))),0),
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1))),0),
nvl(sum(decode(FEED.balance_type_id,
g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
* FEED.scale
* decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1))),0),
-- Minimum Action Sequence of Retro assact(not Target assact)
min(decode(balance_type_id, g_sal_id, BACT.effective_date, g_material_id, BACT.effective_date)),
-- exists check for retro salary paid (paid retro salary at the month)(PACT)
nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1)),0),
nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1)),0),
nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1)),0)
INTO g_sal3_diff,
g_sal2_diff,
g_sal1_diff,
g_sal3_paid,
g_sal2_paid,
g_sal1_paid,
g_material3_diff,
g_material2_diff,
g_material1_diff,
g_material3_paid,
g_material2_paid,
g_material1_paid,
l_min_effective_date,
g_retro_paid3,
g_retro_paid2,
g_retro_paid1
-- 1) pay_element_entries_f.source_id is no index so that all query cause bad performance. reject.
-- target assact(use assignment_id)
-- target pact(use effective_date)
-- target result(use status)
-- retro entries(use creator_type,entry_type)
-- retro assact
-- retro pact
-- target result value
-- target feed(use balance_type_id)
-- 2) probably this way will perform better.
-- retro entries(use assignment_id,creator_type,entry_type)
-- target result(use status)
-- target assact
-- target pact(use effective_date)
-- retro assact
-- retro pact
-- target result value
-- target feed(use balance_type_id)
FROM pay_assignment_actions BACT_ASSACT, -- retro assact
pay_entry_process_details PEPD, -- retro entries
pay_payroll_actions BACT, -- retro pact
pay_run_results RR, -- target result
pay_assignment_actions ASSACT, -- target assact
pay_payroll_actions PACT, -- target pact
pay_element_entries_f PEE, -- retro element entries
pay_run_result_values TARGET, -- target result value
pay_balance_feeds_f FEED
WHERE BACT_ASSACT.assignment_id = p_assignment_id
and PEPD.source_asg_action_id = BACT_ASSACT.assignment_action_id
and BACT.payroll_action_id = BACT_ASSACT.payroll_action_id
and RR.source_id = PEPD.element_entry_id
and RR.status in ('P','PA')
and ASSACT.assignment_action_id = RR.assignment_action_id
and PACT.payroll_action_id = ASSACT.payroll_action_id
and PACT.effective_date
between l_start_gtr and l_end_gtr
and PEE.element_entry_id = PEPD.element_entry_id
and PACT.date_earned
between PEE.effective_start_date and PEE.effective_end_date
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (g_sal_id, g_material_id)
and PACT.effective_date
between FEED.effective_start_date and FEED.effective_end_date;
SELECT /*+ ORDERED
INDEX(BACT_ASSACT PAY_ASSIGNMENT_ACTIONS_N1)
INDEX(PEPD PAY_ENTRY_PROCESS_DETAILS_FK2)
INDEX(BACT PAY_PAYROLL_ACTIONS_PK)
INDEX(RR PAY_RUN_RESULTS_N51)
INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK)
INDEX(PACT PAY_PAYROLL_ACTIONS_PK)
INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK)
INDEX(TARGET PAY_RUN_RESULT_VALUES_N50)
INDEX(FEED PAY_BALANCE_FEEDS_F_N2) */
nvl(sum(fnd_number.canonical_to_number(target.result_value)),0)
INTO g_retro_diff_value
FROM pay_assignment_actions BACT_ASSACT,
pay_entry_process_details PEPD,
pay_payroll_actions BACT,
pay_run_results RR,
pay_assignment_actions ASSACT,
pay_payroll_actions PACT,
pay_element_entries_f PEE,
pay_run_result_values TARGET,
pay_balance_feeds_f FEED
WHERE BACT_ASSACT.assignment_id = p_assignment_id
and PEPD.source_asg_action_id = BACT_ASSACT.assignment_action_id
and BACT.payroll_action_id = BACT_ASSACT.payroll_action_id
and to_char(BACT.effective_date,'YYYYMM')=to_char(l_min_effective_date,'YYYYMM')
and RR.source_id = PEPD.element_entry_id
and RR.status in ('P','PA')
and ASSACT.assignment_action_id = RR.assignment_action_id
and PACT.payroll_action_id = ASSACT.payroll_action_id
and PACT.effective_date
between l_start_gtr and l_end_gtr
and PEE.element_entry_id = PEPD.element_entry_id
and PACT.date_earned
between PEE.effective_start_date and PEE.effective_end_date
and TARGET.run_result_id = RR.run_result_id
and nvl(TARGET.result_value,'0') <> '0'
and FEED.input_value_id = TARGET.input_value_id
and FEED.balance_type_id in (g_sal_id, g_material_id)
and PACT.effective_date
between FEED.effective_start_date and FEED.effective_end_date;
select balance_name
from pay_balance_types
where balance_type_id = g_sal_id;
select balance_name
from pay_balance_types
where balance_type_id = g_material_id;
select org_information9
from hr_organization_information
where organization_id=p_business_group_id
and org_information_context='Business Group Information';
select UE.creator_id
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_item_name
and UE.user_entity_id = DI.user_entity_id
and UE.creator_type = 'B'
and nvl(UE.business_group_id,p_business_group_id) = p_business_group_id
and nvl(UE.legislation_code,l_legislation_code) = l_legislation_code;
select paa.assignment_action_id
from pay_action_classifications pac,
pay_payroll_actions ppa,
pay_assignment_actions paa
where paa.assignment_id=p_assignment_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.effective_date
between p_effective_date_from and p_effective_date_to
and pac.action_type= ppa.action_type
and pac.classification_name='SEQUENCED'
order by paa.action_sequence desc;
select UE.creator_id
from ff_user_entities UE,
ff_database_items DI
where DI.user_name = p_item_name
and UE.user_entity_id = DI.user_entity_id
and UE.creator_type = 'B';
select effective_date
into l_effective_date
from fnd_sessions
where session_id = userenv('sessionid');