[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select transaction_category_id
from pqh_transaction_categories
where short_name = 'BEN_PDCPWZ';
select copy_entity_txn_id
from pqh_copy_entity_txns
where transaction_category_id = p_transaction_category_id
and context_business_group_id = 0
and display_name = 'GHR_TSP_CATCHUP_SEED_PROGRAM_DESIGN';
Cursor update_program_status is
select * from ben_pgm_f
where name = 'Federal Thrift Savings Plan (TSP) Catch Up Contributions'
and business_group_id = p_target_business_group_id;
For i in update_program_status Loop
ben_Program_api.update_program(
p_pgm_id => i.pgm_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_pgm_stat_cd => 'A'
,p_object_version_number => i.object_version_number
,p_effective_date => i.effective_start_date
,p_datetrack_mode => 'CORRECTION'
);
select 'Y' tsp_exists from ben_pgm_f
where name = 'Federal Thrift Savings Plan (TSP)'
and business_group_id = p_target_business_group_id
and pgm_stat_cd = 'A';
select 'Y' from ben_pgm_f
where name = 'Federal Thrift Savings Plan (TSP) Catch Up Contributions'
and business_group_id = p_target_business_group_id;
select
e.assignment_id assignment_id,
decode(name,'Catch Up Amount','Amount', name) Name,
screen_entry_value,
e.effective_start_date,
g.person_id
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f,
per_assignments_f g
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and a.element_type_id = e.element_type_id
and g.business_group_id = c_business_group_id
and e.effective_end_date = hr_api.g_eot
--and trunc(sysdate) between e.effective_start_date and e.effective_End_date
and trunc(e.effective_start_date) between f.effective_start_date and f.effective_End_date
and g.assignment_id = e.assignment_id
and trunc(e.effective_start_date) between g.effective_start_date and g.effective_end_date
and UPPER(a.element_name) = c_element_name
and ghr_general.return_number(screen_entry_value) > 0
order by 1, 2 desc;
Procedure update_alternate_check_date(p_errbuf OUT NOCOPY Varchar2
,p_retcode OUT NOCOPY Number
,p_payroll_id in Number
,p_date_start in Varchar2
,p_date_to in Varchar2
,p_chk_offset in Number) IS
l_proc varchar2(100):= 'Update_Alternate_Chcek_Date';
select ROWID
from per_time_periods
where payroll_id = p_payroll_id
--and start_date between nvl(p_date_start,hr_api.g_sot) and nvl(p_date_to,hr_api.g_eot);
select * from per_time_periods where rowid = l_row_id;
per_time_periods_pkg.update_row
(X_Rowid => l_row_id,
X_Time_Period_Id => l_tprec.time_period_id,
X_Payroll_Id => l_tprec.payroll_id ,
X_End_Date => l_tprec.end_date,
X_Period_Name => l_tprec.period_name,
X_Period_Num => l_tprec.period_Num,
X_Period_Type => l_tprec.period_type,
X_Start_Date => l_tprec.start_date,
X_Cut_Off_Date => l_tprec.cut_off_date,
X_Default_Dd_Date => l_tprec.default_dd_date,
X_Description => l_tprec.description,
X_Pay_Advice_Date => l_tprec.pay_advice_date,
X_Period_Set_Name => l_tprec.period_set_name,
X_Period_Year => l_tprec.period_year,
X_Proc_Period_Type => l_tprec.proc_period_type,
X_Quarter_Num => l_tprec.quarter_num,
X_Quickpay_Display_Number => l_tprec.quickpay_display_number,
X_Regular_Payment_Date => (l_tprec.end_date + p_chk_offset),
X_Run_Display_Number => l_tprec.run_display_number,
X_Status => l_tprec.status,
X_Year_Number => l_tprec.year_number,
X_Attribute_Category => l_tprec.attribute_category,
X_Attribute1 => l_tprec.attribute1,
X_Attribute2 => l_tprec.attribute2,
X_Attribute3 => l_tprec.attribute3,
X_Attribute4 => l_tprec.attribute4,
X_Attribute5 => l_tprec.attribute5,
X_Attribute6 => l_tprec.attribute6,
X_Attribute7 => l_tprec.attribute7,
X_Attribute8 => l_tprec.attribute8,
X_Attribute9 => l_tprec.attribute9,
X_Attribute10 => l_tprec.attribute10,
X_Attribute11 => l_tprec.attribute11,
X_Attribute12 => l_tprec.attribute12,
X_Attribute13 => l_tprec.attribute13,
X_Attribute14 => l_tprec.attribute14,
X_Attribute15 => l_tprec.attribute15,
X_Attribute16 => l_tprec.attribute16,
X_Attribute17 => l_tprec.attribute17,
X_Attribute18 => l_tprec.attribute18,
X_Attribute19 => l_tprec.attribute19,
X_Attribute20 => l_tprec.attribute20,
X_Prd_Information_Category => l_tprec.prd_Information_Category,
X_Prd_Information1 => l_tprec.Prd_Information1,
X_Prd_Information2 => l_tprec.Prd_Information2,
X_Prd_Information3 => l_tprec.Prd_Information3,
X_Prd_Information4 => l_tprec.Prd_Information4,
X_Prd_Information5 => l_tprec.Prd_Information5,
X_Prd_Information6 => l_tprec.Prd_Information6,
X_Prd_Information7 => l_tprec.Prd_Information7,
X_Prd_Information8 => l_tprec.Prd_Information8,
X_Prd_Information9 => l_tprec.Prd_Information9,
X_Prd_Information10 => l_tprec.Prd_Information10,
X_Prd_Information11 => l_tprec.Prd_Information11,
X_Prd_Information12 => l_tprec.Prd_Information12,
X_Prd_Information13 => l_tprec.Prd_Information13,
X_Prd_Information14 => l_tprec.Prd_Information14,
X_Prd_Information15 => l_tprec.Prd_Information15,
X_Prd_Information16 => l_tprec.Prd_Information16,
X_Prd_Information17 => l_tprec.Prd_Information17,
X_Prd_Information18 => l_tprec.Prd_Information18,
X_Prd_Information19 => l_tprec.Prd_Information19,
X_Prd_Information20 => l_tprec.Prd_Information20,
X_Prd_Information21 => l_tprec.Prd_Information21,
X_Prd_Information22 => l_tprec.Prd_Information22,
X_Prd_Information23 => l_tprec.Prd_Information23,
X_Prd_Information24 => l_tprec.Prd_Information24,
X_Prd_Information25 => l_tprec.Prd_Information25,
X_Prd_Information26 => l_tprec.Prd_Information26,
X_Prd_Information27 => l_tprec.Prd_Information27,
X_Prd_Information28 => l_tprec.Prd_Information28,
X_Prd_Information29 => l_tprec.Prd_Information29,
X_Prd_Information30 => l_tprec.Prd_Information30,
X_Payslip_view_Date => l_tprec.payslip_view_date
);
End update_alternate_check_date;