[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
procedure delete_rows is
l_proc varchar2(60) := g_package||'delete_rows';
g_period_table.delete;
end delete_rows;
Procedure Insert_period( p_period_start_date in date,
p_period_end_date in date
) is
l_number_of_rows number;
l_proc varchar2(60) := g_package||'Insert_period';
end Insert_period;
select pay.pay_basis_id,
pay_basis,
PAY_ANNUALIZATION_FACTOR,
name
from per_pay_bases pay,
per_all_assignments_f asg
where pay.input_value_id = p_input_value_id
and pay.business_group_id = asg.business_group_id
and pay.pay_basis_id = asg.pay_basis_id
and asg.assignment_id = p_assignment_id
and p_period_start_date <= asg.effective_end_date
and p_effective_date >= asg.effective_start_date;
select ee.effective_start_date ee_esd,
ee.effective_end_date ee_eed,
ee.CREATOR_TYPE creator_type,
eev.screen_entry_value eev_amt,
eev.ELEMENT_ENTRY_VALUE_ID eev_id
from pay_element_entries_f ee,
pay_element_entry_values_f eev
where ee.assignment_id = p_assignment_id
and ee.element_type_id = p_element_type_id
and ee.effective_start_date < p_perd_en_dt
and ee.effective_end_date > p_perd_st_dt
and ee.element_entry_id = eev.element_entry_id
and eev.input_value_id = p_input_value_id
and eev.effective_start_date = ee.effective_start_date
and eev.effective_end_date = ee.effective_end_date
and eev.screen_entry_value is not null
order by ee.effective_start_date;
select pbb.effective_start_date,
pbb.effective_end_date,
pbb.per_bnfts_bal_id,
pbb.val,
bb.uom,
bb.nnmntry_uom
from ben_bnfts_bal_f bb,
ben_per_bnfts_bal_f pbb
where bb.bnfts_bal_id = p_input_value_id
and pbb.bnfts_bal_id = bb.bnfts_bal_id
and pbb.person_id = p_person_id
and pbb.effective_start_date between p_perd_st_dt and p_perd_en_dt
and bb.effective_start_date < p_perd_en_dt
and bb.effective_end_date > p_perd_st_dt
and pbb.val is not null;
select pr.processing_date period_start_date,
pr.processing_date period_end_date,
ba.run_amount amount ,
bt.uom,
bt.currency,
ba.balance_amount_id,
pe.input_currency_code,
pi.uom input_value_uom,
bt.displayed_name
from
per_bf_balance_types bt,
per_bf_processed_assignments pa,
per_bf_balance_amounts ba,
per_bf_payroll_runs pr,
pay_element_types_f pe,
pay_input_values_f pi
where
pa.assignment_id = p_assignment_id
and pa.payroll_run_id = pr.payroll_run_id
and bt.input_value_id = p_input_value_id
and ba.balance_type_id = bt.balance_type_id
and ba.processed_assignment_id = pa.processed_assignment_id
and pr.processing_date between p_perd_start_date and p_perd_end_date
and ba.run_amount is not null
and pi.input_value_id = bt.input_value_id(+)
and pe.element_type_id = pi.element_type_id(+)
and trunc(sysdate) between pe.EFFECTIVE_START_DATE(+) and pe.EFFECTIVE_END_DATE(+)
and trunc(sysdate) between pi.EFFECTIVE_START_DATE(+) and pi.EFFECTIVE_END_DATE(+);
SELECT ppa.effective_date,
ppa.effective_date date_earned,
paa.assignment_id,
pivf.element_type_id,
pc.input_value_id,
pc.debit_or_credit,
pec.costing_debit_or_credit,
pc.costed_value,
petf.output_currency_code,
pc.cost_id
FROM
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_costs pc,
pay_run_results prr,
pay_input_values_f pivf,
pay_element_types_f petf,
pay_element_classifications pec
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = pc.assignment_action_id
--and paa.assignment_action_id = prr.assignment_action_id
and prr.RUN_RESULT_ID = pc.run_result_id
AND NVL (pc.distributed_input_value_id, pc.input_value_id) =
pivf.input_value_id
AND pivf.input_value_id = p_input_value_id
AND petf.element_type_id = pivf.element_type_id
AND ppa.action_type IN ('C', 'S')
AND pc.balance_or_cost = 'C'
AND pec.classification_id = petf.classification_id
AND ppa.effective_date BETWEEN pivf.effective_start_date
AND pivf.effective_end_date
AND ppa.effective_date BETWEEN petf.effective_start_date
AND petf.effective_end_date
AND paa.assignment_id = p_assignment_id
AND ppa.effective_date BETWEEN p_perd_start_date AND p_perd_end_date
AND pc.costed_value IS NOT NULL;
SELECT ppa.effective_date
,prrv.RESULT_VALUE
,paaf.assignment_id
,piv.element_type_id
,prrv.input_value_id
,prr.run_result_id
,pet.output_currency_code
FROM per_all_assignments_f paaf,
pay_run_results prr,
Pay_run_result_values prrv,
pay_assignment_actions paa,
pay_payroll_actions ppa,
pay_input_values_f piv,
pay_element_types_f pet
WHERE paaf.assignment_id = paa.assignment_id
AND prr.assignment_action_id = paa.assignment_action_id
AND prr.run_result_id=prrv.run_result_id
AND paa.PAYROLL_ACTION_ID=ppa.PAYROLL_ACTION_ID
And piv.input_value_id = p_input_value_id
and piv.input_value_id=prrv.input_value_id
And pet.element_type_id = piv.element_type_id
AND ppa.action_type IN ('Q', 'R')
AND ppa.effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND paaf.assignment_id = p_assignment_id
AND ppa.effective_date BETWEEN p_perd_start_date AND p_perd_end_date;
Select * from per_all_assignments_f
where person_id = p_person_id
and p_effective_date between effective_start_date and effective_end_date
and assignment_id = p_assignment_id ;
select inv.input_value_id input_value_id,
elm.element_type_id element_type_id,
elm.processing_type processing_type,
elm.INPUT_CURRENCY_CODE inp_cur_code,
inv.uom uom , inv.lookup_type ,inv.value_set_id
from pay_input_values_f inv, pay_element_types_f elm
where elm.element_type_id = inv.element_type_id
and inv.input_value_id = p_item_key
and inv.effective_start_date <= p_effective_date
and inv.effective_end_date >= p_period_start_date
and elm.effective_start_date <= p_effective_date
and elm.effective_end_date >= p_period_start_date ;
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => p_perd_en_dt
);
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => period_end_date
);
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => p_perd_en_dt
);
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => period_end_date
);
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => p_perd_en_dt
);
Insert_period ( p_period_start_date => period_start_date,
p_period_end_date => period_end_date
);
select distinct asg.payroll_id,
asg.effective_start_date,
asg.effective_end_date,
number_per_fiscal_year,
status.pay_system_status
from per_all_assignments_f asg,
pay_payrolls_f pay ,per_time_period_types period,PER_ASSIGNMENT_STATUS_TYPES status
where assignment_id = p_assignment_id
--vkodedal = is added to fix payroll period issue 07-Sep-07
and asg.effective_start_date <= p_perd_en_dt
and asg.effective_end_date >= p_perd_st_dt
and asg.payroll_id is not null
and pay.payroll_id = asg.payroll_id
and pay.effective_end_date = (select max(effective_end_date) from pay_payrolls_f where payroll_id =
asg.payroll_id )
and period.period_type = pay.period_type
AND nvl(status.business_group_id,asg.business_group_id) = asg.business_group_id
AND status.active_flag = 'Y'
AND asg.ASSIGNMENT_STATUS_TYPE_ID = status.ASSIGNMENT_STATUS_TYPE_ID
--vkodedal 20-Nov-2009 Bug#9082203
AND status.per_system_status IN ('ACTIVE_ASSIGN', 'ACTIVE_CWK', 'SUSP_ASSIGN')
--AND status.pay_system_status = 'P'
--vkodedal 21-Apr-2009 Bug#8446898
order by effective_start_date;
Select * from PER_TIME_PERIODS_V
where payroll_id = p_pay_id
and not(start_date > p_ped)
and not(end_date < p_psd )
--vkodedal 03-Feb-2010 Bug#8446898
order by end_date;
/* cursor csr_item is select * from ben_tcs_item
where item_id = p_comp_item_id; */
delete_rows;