[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM dual;
is select min(ppe.effective_date)
from pay_process_events ppe
where trunc(ppe.creation_date) between p_lapp_date and p_end_date
and ppe.assignment_id = p_assignment_id
and ppe.business_group_id = p_business_group_id
and ppe.effective_date >= ben_ext_thread.g_effective_start_date
and exists (select pde.event_group_id
from pay_datetracked_events pde,
pay_event_updates peu
where pde.event_group_id in (select becv.val_1
from ben_ext_crit_val becv,
ben_ext_crit_typ bect,
ben_ext_dfn bed
where becv.ext_crit_typ_id = bect.ext_crit_typ_id
and bect.ext_crit_prfl_id = bed.ext_crit_prfl_id
and bed.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
and bect.crit_typ_cd = 'CPE')
and ppe.event_update_id = peu.event_update_id
and peu.dated_table_id = pde.dated_table_id);
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND ROWNUM < 2;
SELECT ext_rslt_id
FROM ben_ext_rslt
WHERE request_id = p_request_id
AND business_group_id = p_business_group_id;
,p_parent_selected IN VARCHAR2 DEFAULT NULL
,p_ext_bdi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_adi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_sehi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_sahi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_ehi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_ahi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_bhi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_wps_rslt_id IN NUMBER DEFAULT NULL
,p_ext_pthi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_sthi_rslt_id IN NUMBER DEFAULT NULL
,p_ext_sthai_rslt_id IN NUMBER DEFAULT NULL
,p_business_group_id IN NUMBER
,p_year_end_close IN VARCHAR2 DEFAULT 'N' -- For Nuvos Changes
) IS
CURSOR csr_get_ttl_rslt(c_ext_rslt_id IN Number) IS
SELECT val_01,val_02,val_03,val_04,val_05
FROM ben_ext_rslt_dtl dtl
,ben_ext_rcd rcd
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = 'T';
SELECT req.request_id req_id
,bba.pl_id rslt_id
,bba.pgm_id ext_dfn_id
,argument3 Execution_Mode
,argument5 Extract_Type
,argument6 Interface_File
,argument8 Extract_eff_date --- For Nuvos
,request_date req_date
FROM fnd_concurrent_requests req, ben_benefit_actions bba
WHERE parent_request_id = c_get_rsltid
AND bba.request_id = req.request_id
AND bba.business_group_id = p_business_group_id;
SELECT argument8 Extract_eff_date --- For Nuvos
FROM fnd_concurrent_requests req ,ben_ext_rslt ben
where req.request_id = ben.request_id
and ben.ext_rslt_id = c_ext_rslt_id
and ben.business_group_id = p_business_group_id;
SELECT val_01 payhcnt,
nvl(val_03,0) + nvl(val_04,0) pearntot,
nvl(val_02,0) + nvl(val_05,0) + nvl(val_06,0) + nvl(val_07,0) + nvl(val_08,0) + nvl(val_09,0) + nvl(val_10,0) + nvl(val_11,0) + nvl(val_12,0) pdedstot
FROM ben_ext_rslt_dtl dtl
,ben_ext_rcd rcd
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND dtl.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = 'T';
SELECT organization_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND c_basic_date BETWEEN effective_start_date
AND effective_end_date;
SELECT org_information1
FROM hr_organization_information
WHERE organization_id = c_organization_id
AND org_information_context = 'PQP_GB_PENSERV_REPORTING_INFO';
SELECT ext_dfn_id
FROM BEN_EXT_DFN
WHERE name = 'PQP GB PenServer Periodic Changes Interface - Basic Data'
AND legislation_code ='GB';
SELECT max(effective_end_date)
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id;
SELECT assignment_number
FROM per_all_assignments_f
WHERE assignment_id = g_assignment_id
AND g_effective_date BETWEEN effective_start_date
AND effective_end_date;
select person_action_id
from ben_person_actions bpa
Where bpa.benefit_action_id = c_benefit_action_id
and EXISTS
( SELECT ers.PERSON_ID
FROM BEN_EXT_RSLT_ERR ers
WHERE ers.person_id = bpa.person_id
AND ers.EXT_RSLT_ID= c_ext_rslt_id
AND typ_cd = 'E');
Update ben_person_actions bpa
Set bpa.action_status_cd = 'U'
Where bpa.benefit_action_id = l_ben_params.benefit_action_id -- 3629 -- p_benefit_action_id
and bpa.person_id -- = p_person_id;
IN ( SELECT PERSON_ID
FROM BEN_EXT_RSLT_ERR
WHERE EXT_RSLT_ID= Ben_Ext_Thread.g_ext_rslt_id -- 2891 -- c_ext_rslt_id
AND typ_cd = 'E');
Update ben_batch_ranges bbr
set bbr.range_status_cd = 'E'
Where bbr.benefit_action_id = l_ben_params.benefit_action_id
AND EXISTS(
Select 1 -- distinct(bere.person_id)
From ben_person_actions bpa, BEN_EXT_RSLT_ERR bere
Where bpa.benefit_action_id = l_ben_params.benefit_action_id
AND bbr.benefit_action_id = bpa.benefit_action_id
AND (bpa.person_action_id Between
bbr.starting_person_action_id And bbr.ending_person_action_id)
And bpa.person_id = bere.person_id
AND bere.EXT_RSLT_ID= Ben_Ext_Thread.g_ext_rslt_id
AND bere.typ_cd = 'E');
SELECT distinct(ers.PERSON_ID)
FROM BEN_EXT_RSLT_ERR ers
WHERE ers.EXT_RSLT_ID = p_ext_rslt_id
AND typ_cd = 'E';
SELECT bbr.range_id
FROM ben_batch_ranges bbr
WHERE bbr.benefit_action_id = p_benefit_action_id
AND p_person_action_id Between
bbr.starting_person_action_id And bbr.ending_person_action_id;
Update ben_person_actions bpa
Set bpa.action_status_cd = 'U'
Where bpa.benefit_action_id = l_ben_params.benefit_action_id
and bpa.person_id = l_person_collection(i)
RETURNING person_action_id BULK COLLECT INTO l_per_action_id_collection;
Update ben_batch_ranges bbr
set bbr.range_status_cd = 'E'
Where bbr.range_id = l_RangeID_collection(l_itr);
Select output_name
from ben_ext_dfn
where ext_dfn_id = ben_ext_thread.g_ext_dfn_id;
update ben_ext_rslt
SET output_name = l_file_name
WHERE business_group_id = l_business_group_id
AND ext_rslt_id = ben_ext_thread.g_ext_rslt_id;
g_errors.DELETE(l_index);
g_warnings.DELETE(l_index);
/*l_query := 'select '||g_asg_membership_col||'
from per_all_assignments_f '||
'where business_group_id = '||p_business_group_id||' '||
'and assignment_id = '||p_assignment_id||' '||
'and ASS_ATTRIBUTE_CATEGORY = '||''''||g_asg_membership_context||''''||
'and to_date('||''''||TO_CHAR(p_effective_date,'dd/mm/yyyy')||''''||
',''dd/mm/yyyy'')'||' between effective_start_date '||
'and effective_end_date';*/
/* l_query := 'select '||g_asg_membership_col||' '||
'from per_all_assignments_f '||' '||
'where business_group_id = '||p_business_group_id||' '||
'and assignment_id = '||p_assignment_id||' ';
l_query := 'select '||g_asg_membership_col||' '||'from per_all_assignments_f '||' '||
'where business_group_id = :p_business_group_id '||
'and assignment_id = :p_assignment_id '||
'and :p_effective_date between effective_start_date and effective_end_date ';
ELSIF l_curr_event_dtl_rec.update_type <> l_next_event_dtl_rec.update_type THEN
l_flag := 'N';
SELECT MAX(eff_dt)
FROM ben_ext_rslt
WHERE ext_dfn_id = ben_ext_thread.g_ext_dfn_id
AND business_group_id = g_business_group_id
AND ext_stat_cd = 'A';
ELSIF l_prev_event_dtl_rec.update_type <> g_prev_event_dtl_rec.update_type THEN
l_flag := 'N';
l_chg_type := ben_ext_person.g_chg_update_type;
debug('Raise an error as the delete event could not be reported',30);
debug('Delete date should be reported in the current row: '||g_min_effective_date(g_assignment_id),20);
SELECT table_name
FROM pay_dated_tables
WHERE dated_table_id = p_dated_table_id;
SELECT asg.person_id person_id
,asg.assignment_id assignment_id
,asg.business_group_id business_group_id
,asg.effective_start_date start_date
,asg.effective_end_date effective_end_date
,asg.creation_date creation_date
,asg.assignment_status_type_id status_type_id
,' ' status_type
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_assignment_id
AND (( p_effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date )
OR
( asg.effective_start_date = p_effective_date + 1 ) -- modified for 115.68
)
ORDER BY asg.effective_start_date ASC; -- effective first then future rows
SELECT DECODE(pay_system_status,'D','DO NOT PROCESS','P','PROCESS')
,per_system_status
FROM per_assignment_status_types
WHERE ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
AND primary_flag = 'P';
SELECT DECODE(pay_system_status,'D','DO NOT PROCESS','P','PROCESS')
,per_system_status
FROM per_assignment_status_types
WHERE ASSIGNMENT_STATUS_TYPE_ID = p_assignment_status_type_id
AND primary_flag = 'P';
select retro_summ_ele_id
from pay_element_types_f
where element_type_id = p_element_type_id
and rownum=1;
select retro_summ_ele_id
from pay_element_types_f
where element_type_id = p_element_type_id
and rownum=1;
SELECT element_entry_id
FROM PAY_ELEMENT_ENTRY_VALUES_F
WHERE element_entry_value_id =p_element_entry_value_id
AND ROWNUM=1;
l_update_type VARCHAR2(5);
l_chg_type := g_pay_proc_evt_tab(l_index).update_type;
l_update_type VARCHAR2(10);
l_update_type := g_pay_proc_evt_tab(l_index).update_type;
OR l_update_type = 'E') THEN
IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
(
p_assignment_id => g_assignment_id
,p_effective_date => l_chg_date
,p_leaver_date => l_leaver_date
) = 'Y' THEN
-- set today as salary end date.
debug('l_leaver_date: '||l_leaver_date,20);
l_update_type VARCHAR2(10);
l_update_type := g_pay_proc_evt_tab(l_index).update_type;
ELSIF l_update_type = 'I' THEN
debug('Returning Y , Insert event on assignments ',40);
l_chg_type := g_pay_proc_evt_tab(l_index).update_type;
l_chg_type := ben_ext_person.g_chg_update_type;
Select max(effective_end_date)
From per_assignment_budget_values_f
Where assignment_budget_value_id = p_assignment_budget_value_id;
l_update_type VARCHAR2(10);
l_update_type := g_pay_proc_evt_tab(l_rev_term_index).update_type;
debug('l_update_type:'||l_update_type);
AND l_update_type = 'C'
THEN
debug('Found Reverse Term event');
AND l_update_type = 'E'
THEN
l_ele_end_date := NULL;
AND l_update_type = 'C'
THEN
l_abv_end_date := NULL;
l_update_type VARCHAR2(10);
l_update_type := g_pay_proc_evt_tab(l_index).update_type;
l_update_type := g_pay_proc_evt_tab(l_index).update_type;
' l_update_type: '||l_update_type||' l_chg_column_name: '||l_chg_column_name);
OR l_update_type = 'E') THEN
IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
(
p_assignment_id => g_assignment_id
,p_effective_date => l_chg_date
,p_leaver_date => l_leaver_date
) = 'Y' THEN
-- set today as salary end date.
debug('l_leaver_date: '||l_leaver_date,20);
AND l_update_type = 'E'
THEN
--Bug 7611963:Chk if ele end date is valid
l_ele_end_date := NULL;
AND (l_chg_column_name IN ('GRADE_ID','NORMAL_HOURS') OR l_update_type = 'I'))
OR (l_chg_table = 'PQP_ASSIGNMENT_ATTRIBUTES_F'
AND (l_chg_column_name = 'CONTRACT_TYPE' OR l_update_type = 'I'))
OR (l_chg_table = 'PAY_ELEMENT_ENTRY_VALUES_F') THEN
l_other_event_exist := 'Y';
AND (l_chg_column_name = 'VALUE' OR l_update_type = 'I' )) THEN
l_chg_surrogate_key := ben_ext_person.g_chg_surrogate_key;
l_update_type := g_pay_proc_evt_tab(l_index).update_type;
OR l_update_type = 'E') THEN
IF PQP_GB_PSI_FUNCTIONS.chk_is_employee_a_leaver
(
p_assignment_id => g_assignment_id
,p_effective_date => l_chg_date
,p_leaver_date => l_leaver_date
) = 'Y' THEN
-- set today as salary end date.
debug('l_leaver_date: '||l_leaver_date,20);
SELECT trim(nvl(pcv_information3,'NULLCOLUMN'))
FROM pqp_configuration_values
WHERE pcv_information_category='PQP_GB_PENSERVER_ELIGBLTY_CONF'
AND business_group_id = c_business_group_id;
SELECT PCV_INFORMATION1, PCV_INFORMATION2, PCV_INFORMATION3, PCV_INFORMATION4
FROM pqp_configuration_values
WHERE pcv_information_category like 'PQP_GB_PENSERVER_UNIGRD_MAP'
AND business_group_id = c_business_group_id;
l_chg_type := ben_ext_person.g_chg_update_type;
l_uni_grade_old_val_query := 'select '||l_people_group_column||' '||
'from pay_people_groups'||' '||
'where PEOPLE_GROUP_ID = '||l_old_value;
l_uni_grade_new_val_query := 'select '||l_people_group_column||' '||
'from pay_people_groups'||' '||
'where PEOPLE_GROUP_ID = '||l_new_value;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name = p_element_name
AND rownum=1;
SELECT pei.eei_information1 from_time_dimension
,pei.eei_information2 pay_source_value
,pei.eei_information3 qualifier
,pei.eei_information4 fte
,pei.eei_information5 termtime
,pei.eei_information7 calc_type
,pei.eei_information8 calc_value
,pei.eei_information9 input_value
,NVL(pei.eei_information10
,decode(pei.eei_information2,'IV','Y','N')) link_to_assign
,NVL(pei.eei_information12,'Y') term_time_yes_no -- ! be careful
,NVL(pei.eei_information13,'N') sum_multiple_entries_yn
,NVL(pei.eei_information14,'N') lookup_input_values_yn
,pei.eei_information16 column_name_source_type
,pei.eei_information17 column_name_source_name
,pei.eei_information18 row_name_source_type
,pei.eei_information19 row_name_source_name
FROM -- pay_element_types_f pet
pay_element_type_extra_info pei
WHERE pei.element_type_id = p_element_type_id
AND pei.information_type = 'PQP_UK_ELEMENT_ATTRIBUTION';
SELECT value
FROM PER_ASSIGNMENT_BUDGET_VALUES_F
WHERE assignment_id = p_assignment_id
AND UNIT = 'FTE'
AND p_effective_date between effective_start_date
AND effective_end_date;