1 Package Body GHR_SS_ASG_EIT as
2 /* $Header: ghrasgei.pkb 120.0.12020000.3 2013/02/12 07:10:24 vmididho noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- |-----------------------< INS_PA_HISTORY_PROC >-------------------------|
6 -- ----------------------------------------------------------------------------
7 -- Description:
8 -- Procedure to insert into GHR_PA_HISTORY after updating Assignment_Extra_Info
9 -- from self service
10 --
11 -- Pre Conditions:
12 --
13 --
14 -- In Parameters:
15 -- assignment_extra_info_id, assignment_id, information_type, row_id
16 --
17 -- Post Success:
18 -- Processing continues.
19 --
20 -- Post Failure:
21 -- An application error will be raised and processing is terminated.
22 --
23 -- Access Status:
24 -- Internal Table Handler Use Only.
25 --
26 --
27
28 Procedure INS_PA_HISTORY_PROC (p_assignment_extra_info_id in number
29 ,p_assignment_id in number
30 ,p_information_type in varchar2) IS
31
32 l_proc varchar2(30) := 'history_ins_process';
33 l_session_var ghr_history_api.g_session_var_type;
34 v_is_ghr_setup_avl varchar2(10);
35 r_setup_cnt number;
36 l_row_touched boolean;
37 indx binary_integer;
38 l_row_id rowid;
39
40
41 CURSOR c_fed_setup_avl IS
42 select count(1)
43 from hr_organization_information oi
44 where oi.org_information_context = 'GHR_US_ORG_INFORMATION'
45 and oi.org_information1 is not null
46 and oi.org_information2 is not null
47 and oi.org_information3 is not null
48 and oi.org_information4 is not null
49 and oi.org_information5 is not null;
50
51 CURSOR get_row_id IS
52 select rowid
53 from PER_ASSIGNMENT_EXTRA_INFO
54 where assignment_extra_info_id = p_assignment_extra_info_id;
55
56 BEGIN
57 hr_utility.set_location('Entering'||l_proc,100);
58
59 v_is_ghr_setup_avl := 'FALSE';
60 OPEN c_fed_setup_avl;
61 FETCH c_fed_setup_avl into r_setup_cnt;
62 IF NVL(r_setup_cnt,0) > 0 THEN
63 v_is_ghr_setup_avl := 'TRUE';
64 END IF;
65 CLOSE c_fed_setup_avl;
66
67 IF v_is_ghr_setup_avl = 'TRUE' THEN
68 ghr_history_api.get_g_session_var(l_session_var);
69
70 IF l_session_var.program_name is null and p_information_type = 'GHR_US_ASG_TELEWORK_INFO' THEN
71
72 l_session_var.assignment_id := p_assignment_id;
73 ghr_history_api.get_session_date( l_session_var.date_effective);
74 ghr_history_api.set_g_session_var(l_session_var);
75
76 validate_telework_det(p_assignment_extra_info_id);
77
78 open get_row_id;
79 fetch get_row_id into l_row_id;
80 close get_row_id;
81
82 l_row_touched := ghr_history_api.row_already_touched(l_row_id);
83 IF l_row_touched then
84 hr_utility.set_location(l_proc, 15);
85 null;
86 else
87 indx := ghr_history_api.add_row_operation_info_rg(p_table_name => 'PER_ASSIGNMENT_EXTRA_INFO',
88 p_table_pk_id => p_assignment_extra_info_id,
89 p_operation => 'insert',
90 p_row_id => l_row_id);
91 end if;
92
93 ghr_history_api.post_update_process;
94 ghr_history_api.reinit_g_session_var;
95 END IF;
96 END IF;
97
98 END INS_PA_HISTORY_PROC;
99
100 --
101 -- ----------------------------------------------------------------------------
102 -- |-----------------------< UPD_PA_HISTORY_PROC >-------------------------|
103 -- ----------------------------------------------------------------------------
104 -- Description:
105 -- Procedure to insert into GHR_PA_HISTORY after updating Assignment_Extra_Info
106 -- from self service
107 --
108 -- Pre Conditions:
109 --
110 --
111 -- In Parameters:
112 -- assignment_extra_info_id, assignment_id, information_type, row_id
113 --
114 -- Post Success:
115 -- Processing continues.
116 --
117 -- Post Failure:
118 -- An application error will be raised and processing is terminated.
119 --
120 -- Access Status:
121 -- Internal Table Handler Use Only.
122 --
123 --
124
125 Procedure UPD_PA_HISTORY_PROC (p_assignment_extra_info_id in number
126 ,p_assignment_id in number
127 ,p_information_type in varchar2) IS
128
129 l_proc varchar2(30) := 'history_ins_process';
130 l_session_var ghr_history_api.g_session_var_type;
131 v_is_ghr_setup_avl varchar2(10);
132 r_setup_cnt number;
133 l_row_touched boolean;
134 indx binary_integer;
135 l_row_id rowid;
136
137 CURSOR c_fed_setup_avl IS
138 select count(1)
139 from hr_organization_information oi
140 where oi.org_information_context = 'GHR_US_ORG_INFORMATION'
141 and oi.org_information1 is not null
142 and oi.org_information2 is not null
143 and oi.org_information3 is not null
144 and oi.org_information4 is not null
145 and oi.org_information5 is not null;
146
147 CURSOR get_row_id IS
148 select rowid
149 from PER_ASSIGNMENT_EXTRA_INFO
150 where assignment_extra_info_id = p_assignment_extra_info_id;
151
152 BEGIN
153 hr_utility.set_location('Entering'||l_proc,100);
154
155 v_is_ghr_setup_avl := 'FALSE';
156 OPEN c_fed_setup_avl;
157 FETCH c_fed_setup_avl into r_setup_cnt;
158 IF NVL(r_setup_cnt,0) > 0 THEN
159 v_is_ghr_setup_avl := 'TRUE';
160 END IF;
161 CLOSE c_fed_setup_avl;
162
163 IF v_is_ghr_setup_avl = 'TRUE' THEN
164 ghr_history_api.get_g_session_var(l_session_var);
165
166 IF l_session_var.program_name is null and p_information_type = 'GHR_US_ASG_TELEWORK_INFO' THEN
167
168 l_session_var.assignment_id := p_assignment_id;
169 ghr_history_api.get_session_date( l_session_var.date_effective);
170 ghr_history_api.set_g_session_var(l_session_var);
171
172 validate_telework_det(p_assignment_extra_info_id);
173
174 open get_row_id;
175 fetch get_row_id into l_row_id;
176 close get_row_id;
177
178 l_row_touched := ghr_history_api.row_already_touched(l_row_id);
179
180 IF l_row_touched then
181 hr_utility.set_location(l_proc, 15);
182 null;
183 else
184 indx := ghr_history_api.add_row_operation_info_rg(p_table_name => 'PER_ASSIGNMENT_EXTRA_INFO',
185 p_table_pk_id => p_assignment_extra_info_id,
186 p_operation => 'update',
187 p_row_id => l_row_id);
188 end if;
189
190 ghr_history_api.post_update_process;
191 ghr_history_api.reinit_g_session_var;
192 END IF;
193 END IF;
194
195 END UPD_PA_HISTORY_PROC;
196
197
198 PROCEDURE VALIDATE_TELEWORK_DET(p_assignment_extra_info_id in NUMBER) IS
199
200 CURSOR c_tel_asg_info
201 IS
202 SELECT aei_information9,
203 aei_information5,
204 aei_information1,
205 aei_information2,
206 aei_information3,
207 aei_information6
208 FROM PER_ASSIGNMENT_EXTRA_INFO
209 WHERE assignment_extra_info_id = p_assignment_extra_info_id;
210
211 BEGIN
212
213 FOR get_tel_info in c_tel_asg_info
214 LOOP
215 IF get_tel_info.aei_information9 IS NOT NULL AND get_tel_info.aei_information5 IS NOT NULL AND
216 fnd_date.canonical_to_date(get_tel_info.aei_information9) >= fnd_date.canonical_to_date(get_tel_info.aei_information5) THEN
217 hr_utility.set_message(8301, 'GHR_37613_ALL_PROCEDURE_FAIL');
218 hr_utility.raise_error;
219 END IF;
220
221 IF get_tel_info.aei_information1 ='4' AND get_tel_info.aei_information2 IS NULL AND get_tel_info.aei_information3 IS NULL THEN
222 hr_utility.set_message(8301,'GHR_37614_ALL_PROCEDURE_FAIL');
223 hr_utility.raise_error;
224 END IF;
225
226 IF get_tel_info.aei_information6 IS NOT NULL AND
227 fnd_date.canonical_to_date(NVL(get_tel_info.aei_information5,get_tel_info.aei_information6))
228 >= fnd_date.canonical_to_date(get_tel_info.aei_information6) THEN
229 hr_utility.set_message(8301, 'GHR_37699_ALL_PROCEDURE_FAIL');
230 hr_utility.set_message_token('ITEM','Telework Agreement');
231 hr_utility.raise_error;
232 END IF;
233 END LOOP;
234
235 END VALIDATE_TELEWORK_DET;
236
237
238
239 --
240 --
241 end GHR_SS_ASG_EIT;
[Home] [Help]