[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gpr.person_id
,gpr.employee_assignment_id
,gpr.effective_date
,gpr.last_update_date
,gpr.pa_request_id
,gpr.pa_notification_id
,gpr.first_noa_code
,gpr.second_noa_code
FROM ghr_pa_requests gpr
WHERE
(TRUNC(gpr.effective_date) between c_ext_start_date
and c_ext_end_date
or
/* TRUNC(gpr.last_update_date) between c_ext_start_date
and c_ext_end_date */
TRUNC(gpr.approval_date) between c_ext_start_date
and c_ext_end_date
)
AND TRUNC(gpr.effective_date) <= c_ext_end_date
AND gpr.person_id = c_person_id
AND(gpr.first_noa_code NOT IN (825,840,841,842,843,844,
845,846,847,848,878,879)
--or
AND
(--gpr.second_noa_code IS NOT NULL AND -- Bug 5031363
NVL(gpr.second_noa_code,-1) NOT IN (825,840,841,842,843,844,
845,846,847,848,878,879))
)
AND gpr.pa_notification_id IS NOT NULL
ORDER BY effective_date;
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
AND rin.ext_file_id = dfn.ext_file_id
AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd; --S- Sub Header D=Detail,H=Header,F=Footer
SELECT bba.request_id
FROM ben_benefit_actions bba
WHERE bba.pl_id = c_ext_rslt_id
AND bba.pgm_id = c_ext_dfn_id
AND bba.business_group_id = c_business_group_id;
SELECT gpr.person_id
,gpr.employee_assignment_id
,gpr.effective_date
,gpr.last_update_date
,gpr.pa_request_id
,gpr.pa_notification_id
,gpr.first_noa_code
,gpr.second_noa_code
FROM ghr_pa_requests gpr
WHERE(TRUNC(gpr.effective_date) between c_ext_start_date
and c_ext_end_date
or
/* TRUNC(gpr.last_update_date) between c_ext_start_date
and c_ext_end_date */
TRUNC(gpr.approval_date) between c_ext_start_date
and c_ext_end_date
)
AND TRUNC(gpr.effective_date) <= c_ext_end_date
AND(gpr.first_noa_code in (825,840,841,842,843,844,
845,846,847,848,878,879)
or
(gpr.second_noa_code is not null and
gpr.second_noa_code in (825,840,841,842,843,844,
845,846,847,848,878,879)
)
)
AND gpr.pa_notification_id is not null
AND gpr.person_id = c_person_id
AND gpr.employee_assignment_id = c_assignment_id;
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = c_person_id
AND (gph.primary_flag='Y' OR gph.address_type = 'M') -- Bug 5037078
AND gph.business_group_id = g_business_group_id
AND gph.effective_date <= c_ext_end_date
ORDER by gph.pa_history_id desc;
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = c_person_id
AND (gph.primary_flag='Y' OR gph.address_type = 'M') -- Bug 5037078
AND gph.business_group_id = g_business_group_id
AND(TRUNC(gph.effective_date) between c_ext_start_date
and c_ext_end_date
or
TRUNC(gph.process_date) between c_ext_start_date
and c_ext_end_date
)
AND TRUNC(gph.effective_date) <= c_ext_end_date
ORDER by gph.pa_history_id desc;
SELECT gpr.remark_id
,gpr.pa_request_id
,gpr.pa_remark_id
,code
FROM ghr_pa_remarks gpr
,ghr_remarks gr
WHERE pa_request_id = c_request_id
AND gr.remark_id=gpr.remark_id
AND c_effective_date between gr.date_from
and NVL(gr.date_to,to_date('12/31/4712','MM/DD/YYYY'))
ORDER BY remark_id;
SELECT gpr.*
FROM ghr_pa_requests gpr
WHERE pa_request_id = c_pa_request_id;
SELECT rcd.ext_rcd_id
FROM ben_ext_rcd rcd
,ben_ext_rcd_in_file rin
,ben_ext_dfn dfn
WHERE dfn.ext_dfn_id = Ben_Ext_Thread.g_ext_dfn_id -- The extract executing currently
AND rin.ext_file_id = dfn.ext_file_id
AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
AND rin.ext_rcd_id = rcd.ext_rcd_id
AND rcd.rcd_type_cd = c_rcd_type_cd; -- D=Detail,H=Header,F=Footer
SELECT *
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = c_ext_rslt_id
AND dtl.person_id = c_person_id
AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
SELECT ben.pgm_id
,ben.pl_id
,ben.benefit_action_id
,ben.business_group_id
,ben.process_date
,ben.request_id
FROM ben_benefit_actions ben
WHERE ben.pl_id = c_ext_rslt_id
AND ben.pgm_id = c_ext_dfn_id
AND ben.business_group_id = c_business_group_id;
SELECT *
FROM ghr_pa_request_extra_info gpre
WHERE gpre.pa_request_id=cp_rpa_req_id
AND information_type=cp_info_type;
SELECT ptp.period_num
FROM per_time_periods ptp
WHERE ptp.payroll_id=p_payroll_id
AND cp_effective_date BETWEEN ptp.start_date
AND ptp.end_date;
SELECT ptp.period_num
,ptp.start_date start_date
,ptp.end_date end_date
,paa.position_id
FROM per_time_periods ptp
,per_all_assignments_f paa
WHERE paa.assignment_id = cp_assignment_id
AND paa.business_group_id = cp_business_group_id
AND cp_effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND paa.payroll_id =ptp.payroll_id
AND cp_effective_date BETWEEN ptp.start_date
AND ptp.end_date;
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = cp_person_id
AND gph.primary_flag='Y'
AND gph.business_group_id = g_business_group_id
AND gph.effective_date <= cp_ext_end_date
ORDER BY gph.effective_date desc,gph.pa_history_id desc;
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = cp_person_id
AND gph.address_type='M'
AND gph.business_group_id = g_business_group_id
AND gph.effective_date <= cp_ext_end_date
ORDER by gph.effective_date desc,gph.pa_history_id desc;
SELECT SUBSTR(duty_station_code,1,2) state_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE '%0000000' AND
(
(substr(duty_station_code,1,1)
IN ('1','2','3','4','5','6','7','8','9','0')
AND substr (duty_station_code,2,1)
IN ('1','2','3','4','5','6','7','8','9','0'))
OR substr(duty_station_code,1,2)
IN ('AQ','GQ','RM','PS','VQ','FM','CQ','RQ') )
AND trunc(cp_effective_date)
BETWEEN effective_Start_date AND effective_end_date
AND EXISTS (SELECT 'X' FROM hr_lookups hrl WHERE lookup_type='US_STATE'
AND UPPER(hrl.MEANING)=dut.name
AND hrl.lookup_code=cp_state);
SELECT SUBSTR(duty_station_code,7,3) county_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code like cp_state_code || '0000%'
AND substr(duty_station_code,7,3) <> '000'
AND cp_effective_date BETWEEN
effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_county_name);*/
SELECT dut.duty_station_code county_code
FROM ghr_duty_stations_f dut
WHERE
dut.duty_station_code IN (
SELECT SUBSTR(dut1.duty_station_code,0,2)||'0000'||SUBSTR(dut1.duty_station_code,7,3)
FROM ghr_duty_stations_f dut1,ghr_duty_stations_f dut2
WHERE dut1.name= UPPER(cp_city_name) AND dut2.duty_station_code =cp_state_code||'0000000'
AND SUBSTR(dut1.duty_station_code,0,2) = SUBSTR(dut2.duty_station_code,0,2)
AND substr(dut2.duty_station_code,3,7) = '0000000'
AND cp_effective_date BETWEEN dut1.effective_start_date
AND dut1.effective_end_date
AND cp_effective_date BETWEEN dut2.effective_start_date
AND dut2.effective_end_date
)
AND cp_effective_date BETWEEN dut.effective_start_date
AND dut.effective_end_date;
SELECT substr(duty_station_code,3,4) city_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE
cp_state_code || '%' || cp_county_code
AND substr(duty_station_code,3,4) <> '0000'
and trunc(cp_effective_date)
BETWEEN effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_city_name);
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = cp_person_id
AND gph.primary_flag='Y'
AND gph.business_group_id = g_business_group_id
AND gph.effective_date <= cp_ext_end_date
ORDER by gph.effective_date desc,gph.pa_history_id desc;
SELECT gph.*
FROM ghr_addresses_h_v gph
WHERE gph.person_id = cp_person_id
AND gph.address_type='M'
AND gph.business_group_id = g_business_group_id
AND gph.effective_date <= cp_ext_end_date
ORDER by gph.effective_date desc,gph.pa_history_id desc;
SELECT SUBSTR(duty_station_code,1,2) state_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE '%0000000' AND
(
(substr(duty_station_code,1,1)
IN ('1','2','3','4','5','6','7','8','9','0')
AND substr (duty_station_code,2,1)
IN ('1','2','3','4','5','6','7','8','9','0'))
OR substr(duty_station_code,1,2)
IN ('AQ','GQ','RM','PS','VQ','FM','CQ','RQ') )
AND trunc(cp_effective_date)
BETWEEN effective_Start_date AND effective_end_date
AND EXISTS (SELECT 'X' FROM hr_lookups hrl WHERE lookup_type='US_STATE'
AND UPPER(hrl.MEANING)=dut.name
AND hrl.lookup_code=cp_state);
SELECT SUBSTR(duty_station_code,7,3) county_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code like cp_state_code || '0000%'
AND substr(duty_station_code,7,3) <> '000'
AND cp_effective_date BETWEEN
effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_county_name);*/
SELECT dut.duty_station_code county_code
FROM ghr_duty_stations_f dut
WHERE
dut.duty_station_code IN (
SELECT SUBSTR(dut1.duty_station_code,0,2)||'0000'||SUBSTR(dut1.duty_station_code,7,3)
FROM ghr_duty_stations_f dut1,ghr_duty_stations_f dut2
WHERE dut1.name= UPPER(cp_city_name) AND dut2.duty_station_code =cp_state_code||'0000000'
AND SUBSTR(dut1.duty_station_code,0,2) = SUBSTR(dut2.duty_station_code,0,2)
AND substr(dut2.duty_station_code,3,7) = '0000000'
AND cp_effective_date BETWEEN dut1.effective_start_date
AND dut1.effective_end_date
AND cp_effective_date BETWEEN dut2.effective_start_date
AND dut2.effective_end_date
)
AND cp_effective_date BETWEEN dut.effective_start_date
AND dut.effective_end_date;
SELECT substr(duty_station_code,3,4) city_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE
cp_state_code || '%' || cp_county_code
AND substr(duty_station_code,3,4) <> '0000'
and trunc(cp_effective_date)
BETWEEN effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_city_name);
SELECT position_id
FROM per_all_assignments_f paa
WHERE paa.assignment_id=p_assignment_id
AND paa.business_group_id=p_business_group_id
AND p_effective_date BETWEEN paa.effective_start_date
and paa.effective_end_date;
SELECT national_identifier
FROM per_all_people_f ppf
WHERE person_id=cp_person_id
AND ppf.business_group_id=p_business_group_id
AND p_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date;
SELECT pdf.segment3 NFC_Agency_Code,
pdf.segment4 POI,
pdf.segment7 Grade
FROM hr_all_positions_f pos, per_position_definitions pdf
WHERE pos.position_definition_id = pdf.position_definition_id
AND pos.position_id = cp_position_id
AND cp_effective_date between pos.effective_start_date
AND pos.effective_end_date
AND pos.business_group_id=cp_business_group_id;
SELECT SUBSTR(duty_station_code,1,2) state_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE '%0000000' AND
(
(substr(duty_station_code,1,1)
IN ('1','2','3','4','5','6','7','8','9','0')
AND substr (duty_station_code,2,1)
IN ('1','2','3','4','5','6','7','8','9','0'))
OR substr(duty_station_code,1,2)
IN ('AQ','GQ','RM','PS','VQ','FM','CQ','RQ') )
AND trunc(cp_effective_date)
BETWEEN effective_Start_date AND effective_end_date
AND EXISTS (SELECT 'X' FROM hr_lookups hrl WHERE lookup_type='US_STATE'
AND UPPER(hrl.MEANING)=dut.name
AND hrl.lookup_code=cp_state);
SELECT SUBSTR(duty_station_code,7,3) county_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code like cp_state_code || '0000%'
AND substr(duty_station_code,7,3) <> '000'
AND cp_effective_date BETWEEN
effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_county_name);*/
SELECT dut.duty_station_code county_code
FROM ghr_duty_stations_f dut
WHERE
dut.duty_station_code IN (
SELECT SUBSTR(dut1.duty_station_code,0,2)||'0000'||SUBSTR(dut1.duty_station_code,7,3)
FROM ghr_duty_stations_f dut1,ghr_duty_stations_f dut2
WHERE dut1.name= UPPER(cp_city_name) AND dut2.duty_station_code =cp_state_code||'0000000'
AND SUBSTR(dut1.duty_station_code,0,2) = SUBSTR(dut2.duty_station_code,0,2)
AND substr(dut2.duty_station_code,3,7) = '0000000'
AND cp_effective_date BETWEEN dut1.effective_start_date
AND dut1.effective_end_date
AND cp_effective_date BETWEEN dut2.effective_start_date
AND dut2.effective_end_date
)
AND cp_effective_date BETWEEN dut.effective_start_date
AND dut.effective_end_date;
SELECT substr(duty_station_code,3,4) city_code
FROM ghr_duty_stations_f dut
WHERE duty_station_code LIKE
cp_state_code || '%' || cp_county_code
AND substr(duty_station_code,3,4) <> '0000'
and trunc(cp_effective_date)
BETWEEN effective_Start_date and effective_end_Date
AND dut.name=UPPER(cp_city_name);
SELECT dut.name
FROM ghr_duty_stations_f dut
WHERE substr(duty_station_code,3,4) = cp_city_code
AND substr(duty_station_code,0,2)= cp_state_code
AND cp_date BETWEEN effective_Start_date
AND effective_end_Date;
SELECT pus.state_abbrev name
FROM pay_us_states pus
WHERE UPPER(pus.state_name)=
(SELECT dut.name
FROM ghr_duty_stations_f dut
WHERE duty_station_code like '%0000000' AND
(
(SUBSTR(duty_station_code,1,1) in ('1','2','3','4','5','6','7','8','9','0')
AND SUBSTR (duty_station_code,2,1) in ('1','2','3','4','5','6','7','8','9','0'))
OR SUBSTR(duty_station_code,1,2) in ('AQ','GQ','RM','PS','VQ','FM','CQ') )
AND cp_date BETWEEN effective_Start_date and effective_end_Date
AND SUBSTR(duty_station_code,0,2)= cp_state_code);
SELECT *
FROM ghr_pa_request_extra_info gpre
--WHERE gpre.request_id = cp_request_id Bug 4641232 Sundar
WHERE gpre.pa_request_id = cp_request_id
AND gpre.information_type = cp_information_type;
SELECT *
FROM per_addresses pa
WHERE pa.person_id = cp_person_id
AND pa.business_group_id=cp_business_group_id
AND cp_effective_date BETWEEN pa.date_from
AND NVL(pa.date_to,TO_DATE('12/31/4712','MM/DD/YYYY'))
AND pa.primary_flag='Y';
SELECT pa_request_id, (effective_date+rownum)-1 auth_date
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date ASC;
SELECT pa_request_id, (effective_date+rownum)-1 auth_date, noa_code
FROM
(
SELECT pa_request_id, effective_date, last_update_date,first_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
UNION ALL
SELECT pa_request_id, effective_date, last_update_date,second_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code = '317'
AND second_noa_code = '825'
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date
) par;
SELECT COUNT(*) auth_date
FROM
(
SELECT pa_request_id, effective_date, last_update_date,first_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
UNION ALL
SELECT pa_request_id, effective_date, last_update_date,second_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code = '317'
AND second_noa_code = '825'
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date
) par;
SELECT ppf.sex
FROM per_all_people_f ppf
WHERE p_effective_date BETWEEN
ppf.effective_start_date
AND ppf.effective_end_date
AND ppf.business_group_id=p_business_group_id
AND ppf.person_id =p_person_id;
SELECT *
FROM per_assignment_extra_info paei
WHERE paei.assignment_id= cp_assignment_id
AND paei.information_type=cp_info_type;
SELECT hap.information6 mrn
FROM hr_all_positions_f hap
WHERE hap.position_id =cp_position_id
AND cp_effective_date BETWEEN hap.effective_start_date
AND hap.effective_end_date
AND hap.business_group_id =cp_business_group_id;
SELECT pdf.segment3 NFC_Agency_Code,
pdf.segment4 Personnel_Office_ID,
pdf.segment7 Grade
FROM hr_all_positions_f pos, per_position_definitions pdf
WHERE pos.position_definition_id = pdf.position_definition_id
AND pos.position_id = cp_position_id
AND cp_effective_date between pos.effective_start_date and pos.effective_end_date;
SELECT pa_request_id, (effective_date+rownum)-1 auth_date
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date ASC;*/
SELECT pa_request_id, (effective_date+rownum)-1 auth_date, noa_code
FROM
(
SELECT pa_request_id, effective_date, last_update_date,first_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
UNION ALL
SELECT pa_request_id, effective_date, last_update_date,second_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code = '317'
AND second_noa_code = '825'
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date
) par;
SELECT COUNT(*) auth_date
FROM
(
SELECT pa_request_id, effective_date, last_update_date,first_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code NOT IN ('001','002')
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
UNION ALL
SELECT pa_request_id, effective_date, last_update_date,second_noa_code noa_code
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_code = '317'
AND second_noa_code = '825'
AND pa_notification_id IS NOT NULL
AND effective_date = c_effective_date
ORDER BY last_update_date
) par;
select eir.seq_num
,elm.string_val
from ben_ext_data_elmt elm,
ben_ext_data_elmt_in_rcd eir
where elm.ext_data_elmt_id = eir.ext_data_elmt_id
and elm.data_elmt_typ_cd = 'R'
and elm.string_val in ('RPA_REQ_ID', 'RPA_AWARD_ID',
'RPA_REMARK_ID','RPA_ADD_ID')
and eir.ext_rcd_id = c_ext_rcd_id;
select count(*) from ben_ext_rslt_err
where ext_rslt_id = ben_extract.g_ext_rslt_id
and typ_cd <> 'W';
select *
from ben_person_actions bpa
where bpa.benefit_action_id = c_benefit_action_id
and bpa.person_id = c_person_id;
select 'x'
from ben_batch_ranges
where benefit_action_id = c_benefit_action_id
and c_person_action_id between starting_person_action_id
and ending_person_action_id;
update ben_person_actions bpa
set bpa.action_status_cd = 'U'
where bpa.benefit_action_id = p_benefit_action_id
and bpa.person_id = p_person_id;
update ben_batch_ranges bbr
set bbr.range_status_cd = 'E'
where bbr.benefit_action_id = p_benefit_action_id
and l_ben_per.person_action_id
between bbr.starting_person_action_id
and bbr.ending_person_action_id;
select err.person_id
,err.typ_cd
,err.ext_rslt_id
from ben_ext_rslt_err err
where err.business_group_id = c_bg_id
and err.typ_cd = 'E'
and err.ext_rslt_id = c_ext_rslt_id;
SELECT Count(dtl.ext_rslt_dtl_id)
FROM ben_ext_rslt_dtl dtl
WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND dtl.ext_rcd_id NOT IN(c_ext_rcd_id);
delete
from ben_ext_rslt_dtl
where ext_rcd_id = csr_rcd_rec.ext_rcd_id
and ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
and business_group_id = p_business_group_id;
delete
from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
and dtl.person_id = err_rec.person_id
and dtl.business_group_id = p_business_group_id;
UPDATE ben_ext_rslt_dtl set val_06 = LPAD(l_rc,8,'0')
WHERE ext_rcd_id = csr_header_rcd_id.ext_rcd_id
AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
AND business_group_id= p_business_group_id;
select description
from ghr_pa_remarks
where pa_remark_id = c_pa_remark_id;*/
SELECT gpr.remark_id
,gpr.description
,gr.code
FROM ghr_pa_remarks gpr
,ghr_remarks gr
WHERE gpr.pa_remark_id = c_pa_remark_id
AND pa_request_id = c_request_id
AND gr.remark_id=gpr.remark_id
AND c_effective_date between gr.date_from
and NVL(gr.date_to,to_date('12/31/4712','MM/DD/YYYY'))
ORDER BY remark_id;
l_sel_stmt := 'select '||p_col_name ||
' from ben_ext_rslt_dtl
where ext_rslt_id = :1
and ext_rslt_dtl_id = :2
and ext_rcd_id = :3
and person_id = :4';
procedure Update_Record_Values
(p_ext_rcd_id in ben_ext_rcd.ext_rcd_id%type
,p_ext_data_element_name in ben_ext_data_elmt.name%type
,p_data_element_value in ben_ext_rslt_dtl.val_01%type
,p_data_ele_seqnum in number
,p_ext_dtl_rec in out nocopy ben_ext_rslt_dtl%rowtype
) is
cursor csr_seqnum (c_ext_rcd_id in ben_ext_rcd.ext_rcd_id%type
,c_ext_data_element_name in ben_ext_data_elmt.name%type
) is
select der.ext_data_elmt_id,
der.seq_num,
ede.name
from ben_ext_data_elmt_in_rcd der
,ben_ext_data_elmt ede
where der.ext_rcd_id = c_ext_rcd_id
and ede.ext_data_elmt_id = der.ext_data_elmt_id
and ede.name like '%'|| c_ext_data_element_name
order by seq_num;
l_proc_name varchar2(150):= g_proc_name||'Update_Record_Values';
end Update_Record_Values;
select xer.seq_num,
xer.sprs_cd,
xer.ext_data_elmt_in_rcd_id,
xdm.name
from ben_ext_data_elmt_in_rcd xer,
ben_ext_data_elmt xdm
where ext_rcd_id = p_ext_rcd_id
and xer.sprs_cd is not null
and xer.ext_data_elmt_id = xdm.ext_data_elmt_id ;
select xwc.oper_cd,
xwc.val,
xwc.and_or_cd,
xer.seq_num
from ben_ext_where_clause xwc,
ben_ext_data_elmt_in_rcd xer
where xwc.ext_data_elmt_in_rcd_id = p_ext_data_elmt_in_rcd_id
and xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
order by xwc.seq_num;
select xwc.oper_cd,
xwc.val,
xwc.and_or_cd,
xer.seq_num,
xrc.name,
Substr(xel.frmt_mask_cd,1,1) xel_frmt_mask_cd,
xel.data_elmt_typ_cd,
xel.data_elmt_rl,
xel.ext_fld_id,
fld.frmt_mask_typ_cd
from ben_ext_where_clause xwc,
ben_ext_data_elmt_in_rcd xer,
ben_ext_rcd xrc,
ben_ext_data_elmt xel,
ben_ext_fld fld
where xwc.ext_rcd_in_file_id = p_ext_rcd_in_file_id
and xwc.cond_ext_data_elmt_in_rcd_id = xer.ext_data_elmt_in_rcd_id
and xer.ext_rcd_id = xrc.ext_rcd_id
and xel.ext_data_elmt_id = xer.ext_data_elmt_id
and xel.ext_fld_id = fld.ext_fld_id(+)
order by xwc.seq_num;
select ben_ext_rslt_dtl_s.nextval into p_dtl_rec.ext_rslt_dtl_id from dual;
insert into ben_ext_rslt_dtl
(ext_rslt_dtl_id
,ext_rslt_id
,business_group_id
,ext_rcd_id
,person_id
,val_01
,val_02
,val_03
,val_04
,val_05
,val_06
,val_07
,val_08
,val_09
,val_10
,val_11
,val_12
,val_13
,val_14
,val_15
,val_16
,val_17
,val_19
,val_18
,val_20
,val_21
,val_22
,val_23
,val_24
,val_25
,val_26
,val_27
,val_28
,val_29
,val_30
,val_31
,val_32
,val_33
,val_34
,val_35
,val_36
,val_37
,val_38
,val_39
,val_40
,val_41
,val_42
,val_43
,val_44
,val_45
,val_46
,val_47
,val_48
,val_49
,val_50
,val_51
,val_52
,val_53
,val_54
,val_55
,val_56
,val_57
,val_58
,val_59
,val_60
,val_61
,val_62
,val_63
,val_64
,val_65
,val_66
,val_67
,val_68
,val_69
,val_70
,val_71
,val_72
,val_73
,val_74
,val_75
,val_76
,val_77
,val_78
,val_79
,val_80
,val_81
,val_82
,val_83
,val_84
,val_85
,val_86
,val_87
,val_88
,val_89
,val_90
,val_91
,val_92
,val_93
,val_94
,val_95
,val_96
,val_97
,val_98
,val_99
,val_100
,val_101
,val_102
,val_103
,val_104
,val_105
,val_106
,val_107
,val_108
,val_109
,val_110
,val_111
,val_112
,val_113
,val_114
,val_115
,val_116
,val_117
,val_118
,val_119
,val_120
,val_121
,val_122
,val_123
,val_124
,val_125
,val_126
,val_127
,val_128
,val_129
,val_130
,val_131
,val_132
,val_133
,val_134
,val_135
,val_136
,val_137
,val_138
,val_139
,val_140
,val_141
,val_142
,val_143
,val_144
,val_145
,val_146
,val_147
,val_148
,val_149
,val_150
,created_by
,creation_date
,last_update_date
,last_updated_by
,last_update_login
,program_application_id
,program_id
,program_update_date
,request_id
,object_version_number
,prmy_sort_val
,scnd_sort_val
,thrd_sort_val
,trans_seq_num
,rcrd_seq_num
)
values
(p_dtl_rec.ext_rslt_dtl_id
,p_dtl_rec.ext_rslt_id
,p_dtl_rec.business_group_id
,p_dtl_rec.ext_rcd_id
,p_dtl_rec.person_id
,p_val_tab(1)
,p_val_tab(2)
,p_val_tab(3)
,p_val_tab(4)
,p_val_tab(5)
,p_val_tab(6)
,p_val_tab(7)
,p_val_tab(8)
,p_val_tab(9)
,p_val_tab(10)
,p_val_tab(11)
,p_val_tab(12)
,p_val_tab(13)
,p_val_tab(14)
,p_val_tab(15)
,p_val_tab(16)
,p_val_tab(17)
,p_val_tab(19)
,p_val_tab(18)
,p_val_tab(20)
,p_val_tab(21)
,p_val_tab(22)
,p_val_tab(23)
,p_val_tab(24)
,p_val_tab(25)
,p_val_tab(26)
,p_val_tab(27)
,p_val_tab(28)
,p_val_tab(29)
,p_val_tab(30)
,p_val_tab(31)
,p_val_tab(32)
,p_val_tab(33)
,p_val_tab(34)
,p_val_tab(35)
,p_val_tab(36)
,p_val_tab(37)
,p_val_tab(38)
,p_val_tab(39)
,p_val_tab(40)
,p_val_tab(41)
,p_val_tab(42)
,p_val_tab(43)
,p_val_tab(44)
,p_val_tab(45)
,p_val_tab(46)
,p_val_tab(47)
,p_val_tab(48)
,p_val_tab(49)
,p_val_tab(50)
,p_val_tab(51)
,p_val_tab(52)
,p_val_tab(53)
,p_val_tab(54)
,p_val_tab(55)
,p_val_tab(56)
,p_val_tab(57)
,p_val_tab(58)
,p_val_tab(59)
,p_val_tab(60)
,p_val_tab(61)
,p_val_tab(62)
,p_val_tab(63)
,p_val_tab(64)
,p_val_tab(65)
,p_val_tab(66)
,p_val_tab(67)
,p_val_tab(68)
,p_val_tab(69)
,p_val_tab(70)
,p_val_tab(71)
,p_val_tab(72)
,p_val_tab(73)
,p_val_tab(74)
,p_val_tab(75)
,p_val_tab(76)
,p_val_tab(77)
,p_val_tab(78)
,p_val_tab(79)
,p_val_tab(80)
,p_val_tab(81)
,p_val_tab(82)
,p_val_tab(83)
,p_val_tab(84)
,p_val_tab(85)
,p_val_tab(86)
,p_val_tab(87)
,p_val_tab(88)
,p_val_tab(89)
,p_val_tab(90)
,p_val_tab(91)
,p_val_tab(92)
,p_val_tab(93)
,p_val_tab(94)
,p_val_tab(95)
,p_val_tab(96)
,p_val_tab(97)
,p_val_tab(98)
,p_val_tab(99)
,p_val_tab(100)
,p_val_tab(101)
,p_val_tab(102)
,p_val_tab(103)
,p_val_tab(104)
,p_val_tab(105)
,p_val_tab(106)
,p_val_tab(107)
,p_val_tab(108)
,p_val_tab(109)
,p_val_tab(110)
,p_val_tab(111)
,p_val_tab(112)
,p_val_tab(113)
,p_val_tab(114)
,p_val_tab(115)
,p_val_tab(116)
,p_val_tab(117)
,p_val_tab(118)
,p_val_tab(119)
,p_val_tab(120)
,p_val_tab(121)
,p_val_tab(122)
,p_val_tab(123)
,p_val_tab(124)
,p_val_tab(125)
,p_val_tab(126)
,p_val_tab(127)
,p_val_tab(128)
,p_val_tab(129)
,p_val_tab(130)
,p_val_tab(131)
,p_val_tab(132)
,p_val_tab(133)
,p_val_tab(134)
,p_val_tab(135)
,p_val_tab(136)
,p_val_tab(137)
,p_val_tab(138)
,p_val_tab(139)
,p_val_tab(140)
,p_val_tab(141)
,p_val_tab(142)
,p_val_tab(143)
,p_val_tab(144)
,p_val_tab(145)
,p_val_tab(146)
,p_val_tab(147)
,p_val_tab(148)
,p_val_tab(149)
,p_val_tab(150)
,p_dtl_rec.created_by
,p_dtl_rec.creation_date
,p_dtl_rec.last_update_date
,p_dtl_rec.last_updated_by
,p_dtl_rec.last_update_login
,p_dtl_rec.program_application_id
,p_dtl_rec.program_id
,p_dtl_rec.program_update_date
,p_dtl_rec.request_id
,p_dtl_rec.object_version_number
,p_dtl_rec.prmy_sort_val
,p_dtl_rec.scnd_sort_val
,p_dtl_rec.thrd_sort_val
,p_dtl_rec.trans_seq_num
,p_dtl_rec.rcrd_seq_num
);
update ben_ext_rslt_dtl
set val_01 = p_val_tab(1)
,val_02 = p_val_tab(2)
,val_03 = p_val_tab(3)
,val_04 = p_val_tab(4)
,val_05 = p_val_tab(5)
,val_06 = p_val_tab(6)
,val_07 = p_val_tab(7)
,val_08 = p_val_tab(8)
,val_09 = p_val_tab(9)
,val_10 = p_val_tab(10)
,val_11 = p_val_tab(11)
,val_12 = p_val_tab(12)
,val_13 = p_val_tab(13)
,val_14 = p_val_tab(14)
,val_15 = p_val_tab(15)
,val_16 = p_val_tab(16)
,val_17 = p_val_tab(17)
,val_19 = p_val_tab(19)
,val_18 = p_val_tab(18)
,val_20 = p_val_tab(20)
,val_21 = p_val_tab(21)
,val_22 = p_val_tab(22)
,val_23 = p_val_tab(23)
,val_24 = p_val_tab(24)
,val_25 = p_val_tab(25)
,val_26 = p_val_tab(26)
,val_27 = p_val_tab(27)
,val_28 = p_val_tab(28)
,val_29 = p_val_tab(29)
,val_30 = p_val_tab(30)
,val_31 = p_val_tab(31)
,val_32 = p_val_tab(32)
,val_33 = p_val_tab(33)
,val_34 = p_val_tab(34)
,val_35 = p_val_tab(35)
,val_36 = p_val_tab(36)
,val_37 = p_val_tab(37)
,val_38 = p_val_tab(38)
,val_39 = p_val_tab(39)
,val_40 = p_val_tab(40)
,val_41 = p_val_tab(41)
,val_42 = p_val_tab(42)
,val_43 = p_val_tab(43)
,val_44 = p_val_tab(44)
,val_45 = p_val_tab(45)
,val_46 = p_val_tab(46)
,val_47 = p_val_tab(47)
,val_48 = p_val_tab(48)
,val_49 = p_val_tab(49)
,val_50 = p_val_tab(50)
,val_51 = p_val_tab(51)
,val_52 = p_val_tab(52)
,val_53 = p_val_tab(53)
,val_54 = p_val_tab(54)
,val_55 = p_val_tab(55)
,val_56 = p_val_tab(56)
,val_57 = p_val_tab(57)
,val_58 = p_val_tab(58)
,val_59 = p_val_tab(59)
,val_60 = p_val_tab(60)
,val_61 = p_val_tab(61)
,val_62 = p_val_tab(62)
,val_63 = p_val_tab(63)
,val_64 = p_val_tab(64)
,val_65 = p_val_tab(65)
,val_66 = p_val_tab(66)
,val_67 = p_val_tab(67)
,val_68 = p_val_tab(68)
,val_69 = p_val_tab(69)
,val_70 = p_val_tab(70)
,val_71 = p_val_tab(71)
,val_72 = p_val_tab(72)
,val_73 = p_val_tab(73)
,val_74 = p_val_tab(74)
,val_75 = p_val_tab(75)
,val_76 = p_val_tab(76)
,val_77 = p_val_tab(77)
,val_78 = p_val_tab(78)
,val_79 = p_val_tab(79)
,val_80 = p_val_tab(80)
,val_81 = p_val_tab(81)
,val_82 = p_val_tab(82)
,val_83 = p_val_tab(83)
,val_84 = p_val_tab(84)
,val_85 = p_val_tab(85)
,val_86 = p_val_tab(86)
,val_87 = p_val_tab(87)
,val_88 = p_val_tab(88)
,val_89 = p_val_tab(89)
,val_90 = p_val_tab(90)
,val_91 = p_val_tab(91)
,val_92 = p_val_tab(92)
,val_93 = p_val_tab(93)
,val_94 = p_val_tab(94)
,val_95 = p_val_tab(95)
,val_96 = p_val_tab(96)
,val_97 = p_val_tab(97)
,val_98 = p_val_tab(98)
,val_99 = p_val_tab(99)
,val_100 = p_val_tab(100)
,val_101 = p_val_tab(101)
,val_102 = p_val_tab(102)
,val_103 = p_val_tab(103)
,val_104 = p_val_tab(104)
,val_105 = p_val_tab(105)
,val_106 = p_val_tab(106)
,val_107 = p_val_tab(107)
,val_108 = p_val_tab(108)
,val_109 = p_val_tab(109)
,val_110 = p_val_tab(110)
,val_111 = p_val_tab(111)
,val_112 = p_val_tab(112)
,val_113 = p_val_tab(113)
,val_114 = p_val_tab(114)
,val_115 = p_val_tab(115)
,val_116 = p_val_tab(116)
,val_117 = p_val_tab(117)
,val_118 = p_val_tab(118)
,val_119 = p_val_tab(119)
,val_120 = p_val_tab(120)
,val_121 = p_val_tab(121)
,val_122 = p_val_tab(122)
,val_123 = p_val_tab(123)
,val_124 = p_val_tab(124)
,val_125 = p_val_tab(125)
,val_126 = p_val_tab(126)
,val_127 = p_val_tab(127)
,val_128 = p_val_tab(128)
,val_129 = p_val_tab(129)
,val_130 = p_val_tab(130)
,val_131 = p_val_tab(131)
,val_132 = p_val_tab(132)
,val_133 = p_val_tab(133)
,val_134 = p_val_tab(134)
,val_135 = p_val_tab(135)
,val_136 = p_val_tab(136)
,val_137 = p_val_tab(137)
,val_138 = p_val_tab(138)
,val_139 = p_val_tab(139)
,val_140 = p_val_tab(140)
,val_141 = p_val_tab(141)
,val_142 = p_val_tab(142)
,val_143 = p_val_tab(143)
,val_144 = p_val_tab(144)
,val_145 = p_val_tab(145)
,val_146 = p_val_tab(146)
,val_147 = p_val_tab(147)
,val_148 = p_val_tab(148)
,val_149 = p_val_tab(149)
,val_150 = p_val_tab(150)
,object_version_number = p_dtl_rec.object_version_number
,thrd_sort_val = p_dtl_rec.thrd_sort_val
where ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
select a.ext_data_elmt_in_rcd_id
,a.seq_num
,a.sprs_cd
,a.strt_pos
,a.dlmtr_val
,a.rqd_flag
,b.ext_data_elmt_id
,b.data_elmt_typ_cd
,b.data_elmt_rl
,b.name
,Hr_General.decode_lookup('BEN_EXT_FRMT_MASK',
b.frmt_mask_cd) frmt_mask_cd
,b.frmt_mask_cd frmt_mask_lookup_cd
,b.string_val
,b.dflt_val
,b.max_length_num
,b.just_cd
from ben_ext_data_elmt b,
ben_ext_data_elmt_in_rcd a
where a.ext_data_elmt_id = b.ext_data_elmt_id
and b.data_elmt_typ_cd = 'R'
and a.ext_rcd_id = c_ext_rcd_id
order by a.seq_num;
select formula_type_id
from ff_formulas_f
where formula_id = c_formula_type_id
and c_effective_date between effective_start_date
and effective_end_date;
select rif.ext_rcd_in_file_id
,rif.any_or_all_cd
,rif.seq_num
,rif.sprs_cd
,rif.rqd_flag
from ben_ext_rcd_in_file rif
,ben_ext_dfn dfn
where rif.ext_file_id = dfn.ext_file_id
and rif.ext_rcd_id = c_rcd_id
and dfn.ext_dfn_id = c_ext_dfn_id;
Update_Record_Values
(p_ext_rcd_id => p_ext_rcd_id
,p_ext_data_element_name => null
,p_data_element_value => l_ff_value
,p_data_ele_seqnum => i.seq_num
,p_ext_dtl_rec => p_rslt_rec);
delete from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
g_pa_req(i).last_update_date := g_rpa_rec(j).last_update_date;
delete from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
delete from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
g_aw_req(i).last_update_date := g_awd_rec(j).last_update_date;
delete from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_dtl_id = l_rslt_dtl_rec.ext_rslt_dtl_id;
g_pa_req(i).last_update_date := g_rpa_rec(j).last_update_date;
g_pa_req(i).last_update_date := g_rpa_rec(j).last_update_date;
g_aw_req(i).last_update_date := g_awd_rec(j).last_update_date;
g_aw_req(i).last_update_date := g_awd_rec(j).last_update_date;
delete
from ben_ext_rslt_dtl dtl
where dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
and dtl.ext_rcd_id = rcd_rec.ext_rcd_id
and dtl.business_group_id = g_business_group_id;
select paf.person_id
,paf.assignment_type
from per_all_assignments_f paf
where paf.assignment_id = c_assignment_id
and paf.business_group_id = c_business_group_id
and c_effective_date between paf.effective_start_date
and paf.effective_end_date;
SELECT argument7, --Tranmission Type
argument8, -- Date Criteria
argument12, -- From Date
argument13, -- To Date
argument14, -- Agency Code
argument15, -- Personnel Office Id
argument16, -- Transmission Indicator
argument17, -- Signon Identification
argument18, -- User_ID
argument19, -- dept Code
argument20, -- Payroll_id
argument21 -- Notify
FROM fnd_concurrent_requests
WHERE request_id = c_req_id;
g_pa_req.delete; g_aw_req.delete;
g_rpa_rec.delete; g_awd_rec.delete;
g_address_rec.delete;
g_pa_req_remark.delete;
g_rpa_attr.delete;
g_rpa_awd_attr.delete;
g_pa_req(i).last_update_date := rpa_rec.last_update_date;
g_aw_req(i).last_update_date := awd_rec.last_update_date;
SELECT period_num
FROM per_time_periods
WHERE payroll_id = c_payroll_id
AND c_effective_date BETWEEN start_date
AND end_date;
SELECT bba.request_id
FROM ben_benefit_actions bba
WHERE bba.pl_id = c_ext_rslt_id
AND bba.pgm_id = c_ext_dfn_id ;