[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select ben_ext_rslt_dtl_s.nextval into p_val_tab.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_val_tab.ext_rslt_dtl_id
,p_val_tab.ext_rslt_id
,p_val_tab.business_group_id
,p_val_tab.ext_rcd_id
,p_val_tab.person_id
,p_val_tab.val_01
,p_val_tab.val_02
,p_val_tab.val_03
,p_val_tab.val_04
,p_val_tab.val_05
,p_val_tab.val_06
,p_val_tab.val_07
,p_val_tab.val_08
,p_val_tab.val_09
,p_val_tab.val_10
,p_val_tab.val_11
,p_val_tab.val_12
,p_val_tab.val_13
,p_val_tab.val_14
,p_val_tab.val_15
,p_val_tab.val_16
,p_val_tab.val_17
,p_val_tab.val_18
,p_val_tab.val_19
,p_val_tab.val_20
,p_val_tab.val_21
,p_val_tab.val_22
,p_val_tab.val_23
,p_val_tab.val_24
,p_val_tab.val_25
,p_val_tab.val_26
,p_val_tab.val_27
,p_val_tab.val_28
,p_val_tab.val_29
,p_val_tab.val_30
,p_val_tab.val_31
,p_val_tab.val_32
,p_val_tab.val_33
,p_val_tab.val_34
,p_val_tab.val_35
,p_val_tab.val_36
,p_val_tab.val_37
,p_val_tab.val_38
,p_val_tab.val_39
,p_val_tab.val_40
,p_val_tab.val_41
,p_val_tab.val_42
,p_val_tab.val_43
,p_val_tab.val_44
,p_val_tab.val_45
,p_val_tab.val_46
,p_val_tab.val_47
,p_val_tab.val_48
,p_val_tab.val_49
,p_val_tab.val_50
,p_val_tab.val_51
,p_val_tab.val_52
,p_val_tab.val_53
,p_val_tab.val_54
,p_val_tab.val_55
,p_val_tab.val_56
,p_val_tab.val_57
,p_val_tab.val_58
,p_val_tab.val_59
,p_val_tab.val_60
,p_val_tab.val_61
,p_val_tab.val_62
,p_val_tab.val_63
,p_val_tab.val_64
,p_val_tab.val_65
,p_val_tab.val_66
,p_val_tab.val_67
,p_val_tab.val_68
,p_val_tab.val_69
,p_val_tab.val_70
,p_val_tab.val_71
,p_val_tab.val_72
,p_val_tab.val_73
,p_val_tab.val_74
,p_val_tab.val_75
,p_val_tab.val_76
,p_val_tab.val_77
,p_val_tab.val_78
,p_val_tab.val_79
,p_val_tab.val_80
,p_val_tab.val_81
,p_val_tab.val_82
,p_val_tab.val_83
,p_val_tab.val_84
,p_val_tab.val_85
,p_val_tab.val_86
,p_val_tab.val_87
,p_val_tab.val_88
,p_val_tab.val_89
,p_val_tab.val_90
,p_val_tab.val_91
,p_val_tab.val_92
,p_val_tab.val_93
,p_val_tab.val_94
,p_val_tab.val_95
,p_val_tab.val_96
,p_val_tab.val_97
,p_val_tab.val_98
,p_val_tab.val_99
,p_val_tab.val_100
,p_val_tab.val_101
,p_val_tab.val_102
,p_val_tab.val_103
,p_val_tab.val_104
,p_val_tab.val_105
,p_val_tab.val_106
,p_val_tab.val_107
,p_val_tab.val_108
,p_val_tab.val_109
,p_val_tab.val_110
,p_val_tab.val_111
,p_val_tab.val_112
,p_val_tab.val_113
,p_val_tab.val_114
,p_val_tab.val_115
,p_val_tab.val_116
,p_val_tab.val_117
,p_val_tab.val_118
,p_val_tab.val_119
,p_val_tab.val_120
,p_val_tab.val_121
,p_val_tab.val_122
,p_val_tab.val_123
,p_val_tab.val_124
,p_val_tab.val_125
,p_val_tab.val_126
,p_val_tab.val_127
,p_val_tab.val_128
,p_val_tab.val_129
,p_val_tab.val_130
,p_val_tab.val_131
,p_val_tab.val_132
,p_val_tab.val_133
,p_val_tab.val_134
,p_val_tab.val_135
,p_val_tab.val_136
,p_val_tab.val_137
,p_val_tab.val_138
,p_val_tab.val_139
,p_val_tab.val_140
,p_val_tab.val_141
,p_val_tab.val_142
,p_val_tab.val_143
,p_val_tab.val_144
,p_val_tab.val_145
,p_val_tab.val_146
,p_val_tab.val_147
,p_val_tab.val_148
,p_val_tab.val_149
,p_val_tab.val_150
,p_val_tab.created_by
,p_val_tab.creation_date
,p_val_tab.last_update_date
,p_val_tab.last_updated_by
,p_val_tab.last_update_login
,p_val_tab.program_application_id
,p_val_tab.program_id
,p_val_tab.program_update_date
,p_val_tab.request_id
,p_val_tab.object_version_number
,p_val_tab.prmy_sort_val
,p_val_tab.scnd_sort_val
,p_val_tab.thrd_sort_val
,p_val_tab.trans_seq_num
,p_val_tab.rcrd_seq_num
);
SELECT COUNT (person_id) cnt
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id =cp_request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.val_29 ='352'
AND berd.val_30 ='825'
AND berd.val_03 ='063';
UPDATE ben_ext_rslt_dtl berd
SET berd.val_30 = NULL
WHERE berd.request_id =p_request_id
AND berd.ext_rslt_id =p_rslt_id
AND berd.val_29 ='352'
AND berd.val_30 ='825'
AND berd.val_03 ='063' ;
SELECT berd.*
FROM ben_ext_rslt_dtl berd
,ben_ext_rslt_dtl berd1
WHERE berd.request_id =cp_request_id
AND berd.request_id =berd1.request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.ext_rslt_id =berd1.ext_rslt_id
AND ( (berd.val_03 ='063'
AND berd.val_03 =berd1.val_03
AND berd.val_30 =berd1.val_29
AND berd.val_29 IN ('001','002')
AND berd.val_150 =berd1.val_01)
OR (berd.val_03 ='110'
AND berd.val_03 =berd1.val_03
AND berd.val_30 =berd1.val_30
AND berd.val_38 IN ('001','002')
AND berd.val_55 =berd1.val_01)
)
ORDER BY berd.person_id;
SELECT *
FROM ghr_pa_requests gpa
WHERE pa_request_id=cp_alt_req_id;
SELECT berd.*
FROM ben_ext_rslt_dtl berd
,ben_ext_rslt_dtl berd1
WHERE berd.request_id =cp_request_id
AND berd.request_id =berd1.request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.ext_rslt_id =berd1.ext_rslt_id
AND ( (berd.val_03 ='063'
AND berd.val_03 =berd1.val_03
AND berd.val_30 =berd1.val_29
AND berd.val_29 IN ('001','002')
AND berd.val_150 =berd1.val_01)
OR (berd.val_03 ='110'
AND berd.val_03 =berd1.val_03
AND berd.val_30 =berd1.val_30
AND berd.val_38 IN ('001','002')
AND berd.val_55 =berd1.val_01)
)
ORDER BY berd.person_id;
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id =cp_request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.val_03 ='063'
AND berd.person_id=cp_person_id
AND berd.val_29 =cp_noa
AND berd.val_01=cp_pa_request_id;
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id =cp_request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.val_03 ='110'
AND berd.person_id=cp_person_id
AND berd.val_30 =cp_noa
AND berd.val_01=cp_pa_request_id;
deleted and the only way to know that the original action
has been created before the current tranmission date is to look at
approval date.
Caveat is there could be an approval date for future action and
this may happen in a very rare case.
*/
IF TRUNC(l_get_dup_act.approval_date) < ghr_us_nfc_extracts.g_ext_start_dt THEN
DELETE from ben_ext_rslt_dtl berd
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.person_id=l_chk_dup_action.person_id
AND berd.Val_01 = l_chk_dup_action.val_150;
DELETE from ben_ext_rslt_dtl berd
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.person_id=l_person_id
AND (berd.Val_150 = l_rslt_dtl_pa(i).val_150
OR berd.val_29=l_rslt_dtl_pa(i).val_150);
DELETE from ben_ext_rslt_dtl berd
WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
l_rslt_dtl_pa.delete;
l_rslt_dtl_tmp.delete;
DELETE from ben_ext_rslt_dtl berd
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.person_id=l_person_id
AND (berd.Val_55 = l_rslt_dtl_aw(i).val_55
OR berd.val_38=l_rslt_dtl_aw(i).val_55);
DELETE from ben_ext_rslt_dtl berd
WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
l_rslt_dtl_aw.delete;
l_rslt_dtl_tmp.delete;
DELETE from ben_ext_rslt_dtl berd
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.person_id=l_person_id
AND (berd.Val_150 = l_rslt_dtl_pa(i).val_150
OR berd.val_01=l_rslt_dtl_pa(i).val_150);
DELETE from ben_ext_rslt_dtl berd
WHERE (berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id)
OR berd.val_150 =l_rslt_dtl.val_01)
AND berd.ext_rslt_dtl_id NOT IN (
l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
l_rslt_dtl_pa.delete;
l_rslt_dtl_tmp.delete;
DELETE from ben_ext_rslt_dtl berd
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.person_id=l_person_id
AND (berd.Val_55 = l_rslt_dtl_aw(i).val_55
OR berd.val_38=l_rslt_dtl_aw(i).val_55);
DELETE from ben_ext_rslt_dtl berd
WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
l_rslt_dtl_aw.delete;
l_rslt_dtl_tmp.delete;
SELECT count(err_doc_type) cnt
,gpid.err_doc_type doc_typ
FROM ghr_pa_interface_err_dtls gpid
WHERE gpid.err_doc_type IN ('063','110','347')
GROUP BY gpid.err_doc_type;
SELECT gpid.person_id
,gpid.result_dtl_id
,gpid.result_id
,gpid.ext_request_id
,gpid.pa_interface_err_dtl_id
FROM ghr_pa_interface_err_dtls gpid
WHERE gpid.err_doc_type='347'
AND NOT EXISTS
(SELECT 'Xl'
FROM ben_ext_rslt_dtl berd
WHERE berd.person_id=gpid.person_id
AND berd.val_03=gpid.err_doc_type
AND berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id);
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.person_id=cp_person_id
AND berd.ext_rslt_dtl_id = cp_result_dtl_id
AND berd.val_03='347'
AND berd.request_id=cp_request_id
AND berd.ext_rslt_id=cp_rslt_id;
SELECT gpid.person_id
,gpid.ext_request_id
,gpid.result_id
,gpid.result_dtl_id
,gpid.record_id
,gpid.pa_request_id
,gpid.err_doc_type
,gpid.err_ssn_no
,gpid.err_agency
,gpid.err_dept_code
,gpid.nat_act_1st_3_pos
,gpid.nat_act_2nd_3_pos
,gpid.alt_pa_request_id
,gpid.err_eff_dt
,gpid.err_auth_dt
,NULL request_id
,NULL alt_req_id
,NULL noa1
,NULL noa2
,NULL ext_rslt_dtl_id
,NULL ex_ssno
,NULL ex_eff_dt
,NULL ex_auth_dt
,NULL ex_agncy
,NULL dept_code
,NULL ext_doc_typ
,NULL business_group_id
,'N' identifier
,gpid.pa_interface_err_dtl_id
FROM ghr_pa_interface_err_dtls gpid
WHERE gpid.err_doc_type=cp_doc_typ
AND NOT EXISTS
(SELECT 'X'
FROM ben_ext_rslt_dtl berd
WHERE gpid.person_id=berd.person_id
AND gpid.err_doc_type=berd.val_03
AND (gpid.nat_act_1st_3_pos = berd.val_29
OR gpid.nat_act_1st_3_pos=berd.val_30 )
AND berd.ext_rcd_id=gpid.record_id
AND gpid.pa_request_id=NVL(berd.val_150,gpid.pa_request_id)
AND berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
)
UNION
SELECT gpid.person_id
,gpid.ext_request_id
,gpid.result_id
,gpid.result_dtl_id
,gpid.record_id
,gpid.pa_request_id
,gpid.err_doc_type
,gpid.err_ssn_no
,gpid.err_agency
,gpid.err_dept_code
,gpid.nat_act_1st_3_pos
,gpid.nat_act_2nd_3_pos
,gpid.alt_pa_request_id
,gpid.err_eff_dt
,gpid.err_auth_dt
,berd.request_id
,berd.val_150 alt_req_id
,berd.val_29 noa1
,berd.val_30 noa2
,berd.ext_rslt_dtl_id ext_rslt_dtl_id
,val_07 ex_ssno
,val_34 ex_eff_dt
,val_111 ex_auth_dt
,val_04 ex_agncy
,val_10 dept_code
,berd.val_03 ext_doc_typ
,berd.business_group_id
,DECODE(berd.val_29,'001','D','002','C') identifier
,gpid.pa_interface_err_dtl_id
FROM ghr_pa_interface_err_dtls gpid
,ben_ext_rslt_dtl berd
WHERE gpid.err_doc_type=cp_doc_typ
AND gpid.person_id=berd.person_id
AND gpid.err_doc_type=berd.val_03
AND berd.val_29 IN ( '001','002')
AND gpid.nat_act_1st_3_pos = berd.val_30
AND gpid.pa_request_id = berd.val_150
AND berd.ext_rcd_id=gpid.record_id
AND berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id;
SELECT gpid.person_id
,gpid.ext_request_id
,gpid.result_id
,gpid.result_dtl_id
,gpid.record_id
,gpid.pa_request_id
,gpid.err_doc_type
,gpid.err_ssn_no
,gpid.err_agency
,gpid.err_dept_code
,gpid.nat_act_1st_3_pos
,gpid.nat_act_2nd_3_pos
,gpid.alt_pa_request_id
,gpid.err_eff_dt
,gpid.err_auth_dt
,NULL request_id
,NULL alt_req_id
,NULL noa1
,NULL noa2
,NULL ext_rslt_dtl_id
,NULL ex_ssno
,NULL ex_eff_dt
,NULL ex_auth_dt
,NULL ex_agncy
,NULL dept_code
,NULL ext_doc_typ
,null business_group_id
,'N' identifier
,gpid.pa_interface_err_dtl_id
FROM ghr_pa_interface_err_dtls gpid
WHERE gpid.err_doc_type='110'
AND NOT EXISTS
(SELECT 'X'
FROM ben_ext_rslt_dtl berd
WHERE gpid.person_id=berd.person_id
AND gpid.err_doc_type=berd.val_03
AND (gpid.nat_act_1st_3_pos = berd.val_38
OR gpid.nat_act_1st_3_pos=berd.val_30 )
AND berd.ext_rcd_id=gpid.record_id
AND gpid.pa_request_id=NVL(berd.val_55,gpid.pa_request_id)
AND berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
)
UNION
SELECT gpid.person_id
,gpid.ext_request_id
,gpid.result_id
,gpid.result_dtl_id
,gpid.record_id
,gpid.pa_request_id
,gpid.err_doc_type
,gpid.err_ssn_no
,gpid.err_agency
,gpid.err_dept_code
,gpid.nat_act_1st_3_pos
,gpid.nat_act_2nd_3_pos
,gpid.alt_pa_request_id
,gpid.err_eff_dt
,gpid.err_auth_dt
,berd.request_id
,berd.val_150 alt_req_id
,berd.val_38 noa1
,berd.val_30 noa2
,berd.ext_rslt_dtl_id ext_rslt_dtl_id
,val_09 ex_ssno
,val_32 ex_eff_dt
,val_48 ex_auth_dt
,val_04 ex_agncy
,val_10 dept_code
,berd.val_03 ext_doc_typ
,berd.business_group_id
,DECODE(berd.val_38,'001','D','002','C') identifier
,gpid.pa_interface_err_dtl_id
FROM ghr_pa_interface_err_dtls gpid
,ben_ext_rslt_dtl berd
WHERE gpid.err_doc_type='110'
AND gpid.person_id=berd.person_id
AND gpid.err_doc_type=berd.val_03
AND berd.val_38 IN ( '001','002')
AND gpid.nat_act_1st_3_pos = berd.val_30
AND gpid.pa_request_id = berd.val_55
AND berd.ext_rcd_id=gpid.record_id
AND berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id;
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id = cp_request_id
AND berd.ext_rslt_dtl_id = cp_rslt_dtl_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.ext_rcd_id =cp_rcd_id
;
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id = cp_request_id
AND berd.val_02='RMK'
AND berd.val_01=cp_pa_req
AND berd.person_id=cp_person_id
;
DELETE FROM ghr_pa_interface_err_dtls perr
WHERE pa_interface_err_dtl_id = l_chk_data_in_file.pa_interface_err_dtl_id;
UPDATE ben_ext_rslt_dtl berd
SET berd.val_29 = l_chk_data_in_file.nat_act_1st_3_pos
,berd.val_30=NULL
,berd.val_150=null
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.ext_rcd_id=l_chk_data_in_file.record_id
AND berd.ext_rslt_dtl_id=l_chk_data_in_file.ext_rslt_dtl_id;
DELETE FROM ben_ext_rslt_dtl berd
WHERE berd.ext_rslt_dtl_id=l_chk_data_in_file.ext_rslt_dtl_id;
DELETE FROM ghr_pa_interface_err_dtls perr
WHERE pa_interface_err_dtl_id = l_chk_data_in_file_aw.pa_interface_err_dtl_id;
UPDATE ben_ext_rslt_dtl berd
SET berd.val_38=NULL
--, berd.val_38 = l_chk_data_in_file.nat_act_1st_3_pos
--,berd.val_30=NULL
,berd.val_55=NULL
WHERE berd.request_id=p_request_id
AND berd.ext_rslt_id=p_rslt_id
AND berd.ext_rcd_id=l_chk_data_in_file_aw.record_id
AND berd.ext_rslt_dtl_id=l_chk_data_in_file_aw.ext_rslt_dtl_id;
DELETE FROM ben_ext_rslt_dtl berd
WHERE berd.ext_rslt_dtl_id=l_chk_data_in_file_aw.ext_rslt_dtl_id;
DELETE FROM ghr_pa_interface_err_dtls perr
WHERE pa_interface_err_dtl_id = l_chk_add_in_file.pa_interface_err_dtl_id;
SELECT *
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id = cp_request_id
AND berd.ext_rslt_dtl_id=cp_result_dtl_id
AND berd.ext_rcd_id =cp_record_id;
SELECT count(*) cnt
FROM ghr_pos_interface_err_dtls gpid
WHERE gpid.susp_mast_indv='2055';
SELECT berd.val_71 position_id,
berd.val_26 f_function_cd,
gpid.susp_function_cd e_function_cd,
gpid.pos_interface_err_dtl_id
FROM ben_ext_rslt_dtl berd
,ghr_pos_interface_err_dtls gpid
WHERE TO_CHAR(gpid.position_id) = berd.val_71
AND gpid.susp_mast_indv ='2055'
AND gpid.susp_mast_indv=berd.val_02
AND berd.request_id=p_request_id
AND berd.ext_rcd_id=gpid.record_id;
SELECT *
FROM ghr_pos_interface_err_dtls gpid
WHERE NOT EXISTS (
SELECT 'X'
FROM ben_ext_rslt_dtl berd
WHERE to_char(gpid.position_id) = berd.val_71
AND gpid.susp_mast_indv ='2055'
AND gpid.susp_mast_indv=berd.val_02
AND berd.request_id=p_request_id
AND berd.ext_rcd_id=gpid.record_id)
AND gpid.susp_mast_indv ='2055';
SELECT COUNT(*) cnt
FROM ghr_pos_interface_err_dtls gpid
WHERE gpid.susp_mast_indv='2056';
SELECT berd.val_71 position_id,
berd.val_45 f_function_cd,
gpid.susp_function_cd e_function_cd,
gpid.pos_interface_err_dtl_id
FROM ben_ext_rslt_dtl berd
,ghr_pos_interface_err_dtls gpid
WHERE to_char(gpid.position_id) = berd.val_71
AND gpid.susp_mast_indv ='2056'
AND gpid.susp_mast_indv=berd.val_02
AND berd.request_id=p_request_id
AND berd.ext_rcd_id=gpid.record_id;
SELECT *
FROM ghr_pos_interface_err_dtls gpid
WHERE NOT EXISTS (
SELECT 'X'
FROM ben_ext_rslt_dtl berd
WHERE to_char(gpid.position_id) = berd.val_71
AND gpid.susp_mast_indv ='2056'
AND gpid.susp_mast_indv=berd.val_02
AND berd.request_id=p_request_id
AND berd.ext_rcd_id=gpid.record_id)
AND gpid.susp_mast_indv ='2056';
UPDATE ben_ext_rslt_dtl berd
SET berd.val_26=l_chk_mast_err.e_function_cd
WHERE berd.val_71 = to_char(l_chk_mast_err.position_id)
AND berd.request_id=p_request_id
AND berd.val_02='2055';
DELETE FROM ghr_pos_interface_err_dtls perr
WHERE pos_interface_err_dtl_id = l_chk_unpick_mast.pos_interface_err_dtl_id;
UPDATE ben_ext_rslt_dtl berd
SET berd.val_45=l_chk_ind_err.e_function_cd
WHERE berd.val_71 =TO_CHAR( l_chk_ind_err.position_id)
AND berd.request_id=p_request_id
AND berd.val_02='2056';
DELETE FROM ghr_pos_interface_err_dtls perr
WHERE pos_interface_err_dtl_id = l_chk_unpick_ind.pos_interface_err_dtl_id;
l_stmt := 'select ext_rslt_dtl_id
, ext_rslt_id
,ext_rcd_id
,val_01
,person_id'||','
||p_indicator ||','
||p_department_code||','
||p_agency_code||','
||p_poi||','
||p_ssn||','
||p_noa1||','
||p_noa2||','
||p_pay_per_num||','
||p_auth_dt||','
||p_doc_typ||','
||p_eff_dt||
' from ben_ext_rslt_dtl
where request_id = :1
and ext_rcd_id = :2
and '||p_status||' = '||'''E''' ;
INSERT INTO ghr_pa_interface_err_dtls
( pa_interface_err_dtl_id
,ext_request_id
,result_id
,result_dtl_id
,record_id
,business_group_id
,person_id
,assignment_id
,pa_request_id
,err_ssn_no
,err_agency
,err_emp_off
,err_dept_code
,err_doc_type
,err_pay_period
,err_auth_dt
,nat_act_1st_3_pos
,nat_act_2nd_3_pos
,err_eff_dt
,err_batch_no
,err_oper_code
,error_code
,error_msg
,error_element_name
,error_elem_content
)
VALUES
(
ghr_pa_interface_err_dtls_s.nextval
,p_request_id
,l_result_id
,l_result_dtl_id
,p_record_id
,null
,l_person_id
,null
,l_pa_req
,l_ssn
,l_agency_code
,l_poi
,l_department_code
,l_indicator
,l_pay_per_num
,l_auth_dt
,l_noa1
,l_noa2
,l_eff_dt
,l_doc_typ
,null
,null
,null
,null
,null
);
l_stmt := 'select ext_rslt_dtl_id
, ext_rslt_id
,ext_rcd_id
,val_71'||','
||p_indicator ||','
||p_function_code||','
||p_department_code||','
||p_agency_code||','
||p_poi||','
||p_mrn||','
||p_grade||','
||p_pos_num||','
||p_incumbant_ssn||
' from ben_ext_rslt_dtl
where request_id = :1
and ext_rcd_id = :2
and '||p_status||' = '||'''E''' ;
INSERT INTO ghr_pos_interface_err_dtls
( pos_interface_err_dtl_id
,request_id
,result_id
,result_dtl_id
,record_id
,business_group_id
,position_id
,susp_user_id
,susp_mast_indv
,susp_function_cd
,susp_dept
,susp_agcy
,susp_poi
,susp_mast_rec_num
,susp_grd
,susp_indv_pos_num
,susp_incum_ssn
,susp_oblig_ssn
,susp_pay_period
,susp_pass_num
,susp_error_msg_num
,susp_error_msg
,susp_elem_name_num
,susp_elem_name
,susp_data_field
,susp_serv_agcy
)
VALUES
(ghr_pos_interface_err_dtls_s.nextval
,p_request_id
,l_result_id
,l_result_dtl_id
,p_record_id
,null
,TO_NUMBER(l_position_id)
,null
,l_indicator
,l_function_code
,l_department_code
,l_agency_code
,l_poi
,l_mrn
,l_grade
,l_pos_num
,null
,null
,null
,null
,null
,null
,null
,null
,null
,null
);
select beder.seq_num
,bede.string_val
,ber.ext_rcd_id record_id
from ben_ext_rslt bes
,ben_ext_dfn bed
,ben_ext_file bef
,ben_ext_rcd_in_file berf
,ben_ext_rcd ber
,ben_ext_data_elmt_in_rcd beder
,ben_ext_data_elmt bede
where bes.request_id=p_request_id
and bes.ext_dfn_id=bed.ext_dfn_id
and bed.ext_file_id =bef.ext_file_id
and bef.ext_file_id=berf.ext_file_id
and ber.ext_rcd_id = berf.ext_rcd_id
/* and (ber.name like '%SHRL Master Position Data Element Record - (NFC PO)'
OR ber.name like '%SHRL Detail Position Data Element Record - (NFC PO)'
OR ber.name like 'DRL Individual Data Element Record - (NFC PO)')*/
and ber.ext_rcd_id=beder.ext_rcd_id
and bede.ext_data_elmt_id = beder.ext_data_elmt_id
and bede.string_val in ('S','Master Record Number06','2055'
,'Function Code26','Function Code46','Department Code03'
,'Agency Code04','Personnel Office Identifier05'
,'Grade07','2056','Position Number08','IncumbentSSN50')
ORDER BY ber.ext_rcd_id;
select beder.seq_num
,bede.string_val
,ber.ext_rcd_id record_id
from ben_ext_rslt bes
,ben_ext_dfn bed
,ben_ext_file bef
,ben_ext_rcd_in_file berf
,ben_ext_rcd ber
,ben_ext_data_elmt_in_rcd beder
,ben_ext_data_elmt bede
where bes.request_id=p_request_id
and bes.ext_dfn_id=bed.ext_dfn_id
and bed.ext_file_id =bef.ext_file_id
and bef.ext_file_id=berf.ext_file_id
and ber.ext_rcd_id = berf.ext_rcd_id
-- and (ber.name like '%DRL Personnel Actions [RPA] - (NFC PA)'
-- OR ber.name like '%DRL Personnel Actions Remarks [RMK] - (NFC PA)'
-- OR ber.name like 'DRL Personnel Actions Awards [AWD] - (NFC PA)'
-- OR ber.name like '%DRL Address Change Record [ADD] - (NFC PA)')
and ber.ext_rcd_id=beder.ext_rcd_id
and bede.ext_data_elmt_id = beder.ext_data_elmt_id
and bede.string_val in ('S','RPA_SSN','RPA_NFC_AGENCY','RPA_POI','RPA_PMSO_DEPT','063'
,'RPA_PAY_PERIOD_NUM','RPA_AUTH_DT','RPA_SEC_NOA_CD',
'RPA_FIRST_NOA_CD','RPA_EFF_DATE','6700','110','347','REM_SSN',
'REM_AGNCY_CD','REM_POI','REM_PAY_PER_NUM','REM_DEPT_CD','AWD_AUTH_DT'
,'AWD_PERS_EFF_DT','AWD_NAT_ACT_POS1','AWD_NAT_ACT_POS2',
'ADD_AGNCY_CD','ADD_POI','ADD_SSN','ADD_PAY_PER_NUM','ADD_DEPT_CODE'
)
ORDER BY ber.ext_rcd_id;
select beder.seq_num
,bede.string_val
,ber.ext_rcd_id record_id
from ben_ext_rslt bes
,ben_ext_dfn bed
,ben_ext_file bef
,ben_ext_rcd_in_file berf
,ben_ext_rcd ber
,ben_ext_data_elmt_in_rcd beder
,ben_ext_data_elmt bede
where bes.request_id=p_request_id
and bes.ext_dfn_id=bed.ext_dfn_id
and bed.ext_file_id =bef.ext_file_id
and bef.ext_file_id=berf.ext_file_id
and ber.ext_rcd_id = berf.ext_rcd_id
and ( ber.name like '%SHRL Detail Position Data Element Record - (NFC PO)'
OR ber.name like 'DRL Individual Data Element Record - (NFC PO)'
and ber.ext_rcd_id=beder.ext_rcd_id
and bede.ext_data_elmt_id = beder.ext_data_elmt_id
and bede.string_val in ('S','Master Record Number06','2056'
,'Function Code26','Department Code03'
,'Agency Code04','Personnel Office Identifier05'
,'Grade07','Position Number08','Incumbent SSN50');
DELETE FROM ghr_pos_interface_err_dtls;
DELETE FROM ghr_pa_interface_err_dtls;
SELECT berd.*
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id =cp_request_id
AND berd.ext_rslt_id =cp_rslt_id
AND ( (berd.val_03 ='063'
AND berd.val_29 IN ('001','002')
)
OR (berd.val_03 ='110'
AND berd.val_38 IN ('001','002')
)
)
ORDER BY berd.person_id, TO_DATE(berd.val_34,'MMDDYYYY') desc, to_number(berd.val_01) desc ; -- Bug 4923152
SELECT berd.*
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id =cp_request_id
AND berd.ext_rslt_id =cp_rslt_id
AND berd.val_01 = cp_val_01;
SELECT par.*
FROM ghr_pa_requests par
WHERE pa_request_id = c_pa_request_id;
SELECT pa_request_id
FROM ghr_pa_requests
WHERE person_id = c_person_id
AND first_noa_cancel_or_correct = 'CANCEL'
AND pa_request_id < c_canc_rpa_id
AND TRUNC(approval_date) = c_approval_date;
SELECT pa_request_id
FROM ghr_pa_requests
WHERE pa_notification_id IS NOT NULL
START WITH pa_request_id = c_pa_request_id
CONNECT BY PRIOR pa_request_id = altered_pa_request_id; -- Bug 4923152
SELECT pa_request_id
FROM ghr_pa_requests
WHERE pa_notification_id IS NOT NULL
START WITH pa_request_id = c_pa_request_id
CONNECT BY PRIOR altered_pa_request_id = pa_request_id; -- Bug 4923152
l_skip_records.delete;
-- If yes and also if extract date is greater than approval date, delete both actions.
-- Initialise Values to NULL
l_orig_pa := NULL;
l_skip_records.DELETE;
-- If Actions done on the same date, add them to delete record.
-- IF ghr_us_nfc_extracts.g_ext_start_dt >= TRUNC(l_orig_pa.approval_date) AND
IF (
(TRUNC(l_orig_pa.approval_date)
BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
)
OR
(TRUNC(l_orig_pa.effective_date)
BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
)
)
AND TRUNC(l_orig_pa.approval_date) = TRUNC(l_canc_pa.approval_date) THEN
-- If it's Appointment action, delete all old actions done between appt and cancellation.
debug_prg('IF l_second_noa_code = 100');
-- Delete child records(canc/corr) too
FOR l_child_rpas IN c_child_rpas(l_orig_pa_request_id) LOOP
l_del_rec(l_del_rec.count+1) := l_child_rpas.pa_request_id;
debug_prg('Recs to be deleted1: ' || l_child_rpas.pa_request_id);
-- Delete child records(canc/corr) too
FOR l_child_rpas IN c_child_rpas_correct(l_canc_pa_request_id) LOOP
l_del_rec(l_del_rec.count+1) := l_child_rpas.pa_request_id;
debug_prg('Recs to be deleted2: ' || l_child_rpas.pa_request_id);
debug_prg('Before update: ' || l_berd_corrected.val_29);
debug_prg('After update: ' || l_berd_corrected.val_29);
DELETE
FROM ben_ext_rslt_dtl berd
WHERE berd.request_id= p_request_id
AND berd.ext_rslt_id = p_rslt_id
AND val_01 = TO_CHAR(l_del_rec(l_recs));
DELETE
FROM ghr_pa_interface_err_dtls perr
WHERE perr.ext_request_id= p_request_id
AND perr.result_id = p_rslt_id
AND pa_request_id = l_del_rec(l_recs);