[Home] [Help]
PACKAGE BODY: APPS.PQH_PROCESS_TENURE_STATUS
Source
1 PACKAGE BODY pqh_process_tenure_status AS
2 /* $Header: pqhusten.pkb 120.0 2005/05/29 02:08:35 appldev noship $*/
3
4 PROCEDURE rollback_transaction(
5 itemType IN VARCHAR2,
6 itemKey IN VARCHAR2,
7 result OUT NOCOPY VARCHAR2) IS
8 BEGIN
9 --
10 savepoint rollback_transaction;
11 --
12 wf_engine.setItemAttrNumber (
13 itemType => itemType,
14 itemKey => itemKey,
15 aname => 'TRANSACTION_ID',
16 avalue => null );
17 --
18 --
19 hr_transaction_ss.rollback_transaction (
20 itemType => itemType,
21 itemKey => itemKey,
22 actid => 0,
23 funmode => 'RUN',
24 result => result );
25 --
26 --
27 result := 'SUCCESS';
28 --
29 --
30 EXCEPTION
31 --
32 WHEN Others THEN
33 rollback to rollback_transaction;
34 result := 'FAILURE';
35 --
36 END;
37
38 --
39 --
40 FUNCTION get_tenure_status (
41 p_transaction_step_id in varchar2 ) RETURN ref_cursor IS
42 csr ref_cursor;
43 BEGIN
44 OPEN csr FOR
45 select
46 tenure.person_id,
47 'PQH_TENURE_STATUS' pei_information_category,
48 tenure.pei_information1,
49 fnd_date.canonical_to_date(tenure.pei_information2) pei_information2,
50 fnd_date.canonical_to_date(tenure.pei_information3) pei_information3,
51 fnd_date.canonical_to_date(tenure.pei_information4) pei_information4,
52 tenure.pei_information5,
53 to_number(tenure.person_extra_info_id ) person_extra_info_id,
54 to_number(tenure.object_version_number) object_version_number,
55 hl.meaning Tenure_desc,
56 tenure.pei_information6
57 from (
58 Select max(person_id) person_id ,
59 max(pei_information1) pei_information1,
60 max(person_extra_info_id) person_extra_info_id,
61 max(object_version_number) object_version_number,
62 max(pei_information2) pei_information2,
63 max(pei_information3) pei_information3,
64 max(pei_information4) pei_information4,
65 max(pei_information5) pei_information5,
66 max(pei_information6) pei_information6
67 from (
68 SELECT decode(a.name, 'P_PERSON_ID' , a.varchar2_value, null) person_id,
69 decode(a.name, 'P_TENURE_STATUS' , a.varchar2_value,null) pei_information1,
70 decode(a.name, 'P_DATE_DETERMINE' , a.varchar2_value,null) pei_information2,
71 decode(a.name, 'P_PROJECTED_TENURE_DATE', a.varchar2_value ,null) pei_information3,
72 decode(a.name, 'P_ADJUSTED_DATE' , a.varchar2_value ,null) pei_information4,
73 decode(a.name, 'P_REASON_ADJUSTED' , a.varchar2_value ,null) pei_information5,
74 decode(a.name, 'P_PERSON_EXTRA_INFO_ID' , a.varchar2_value ,null) person_extra_info_id,
75 decode(a.name, 'P_OBJECT_VERSION_NUMBER', a.varchar2_value ,null) object_version_number,
76 decode(a.name, 'P_SUBJECT_TO_QUOTA' , a.varchar2_value ,null) pei_information6
77 FROM hr_api_transaction_steps s,
78 hr_api_transaction_values a
79 WHERE s.transaction_step_id = a.transaction_step_id
80 and s.transaction_step_id = p_transaction_step_id
81 AND s.api_name = 'PQH_PROCESS_TENURE_STATUS.PROCESS_API'
82 )
83 ) tenure ,
84 hr_lookups hl
85 where hl.lookup_type = 'PQH_TENURE_STATUS'
86 AND hl.lookup_code = tenure.pei_information1;
87
88 RETURN csr;
89 END get_tenure_status;
90
91 --
92 --
93
94 PROCEDURE get_tenure_details (
95 x_transaction_step_id in varchar2
96 ,x_pei_information1 out nocopy varchar2
97 ,x_pei_information2 out nocopy varchar2
98 ,x_pei_information3 out nocopy varchar2
99 ,x_pei_information4 out nocopy varchar2
100 ,x_pei_information5 out nocopy varchar2
101 ,x_pei_information6 out nocopy varchar2
102 ,x_person_extra_info_id out nocopy varchar2 ) IS
103
104 l_transaction_step_id number;
105 l_api_name hr_api_transaction_steps.api_name%TYPE;
106
107 BEGIN
108 hr_utility.set_location('Entering: PQH_PROCESS_TENURE.get_tenure_details',5);
109 --
110 l_transaction_step_id := to_number(x_transaction_step_id);
111 --
112
113 if l_transaction_step_id is null then
114 return;
115 end if;
116 --
117
118 x_pei_information1 :=
119 hr_transaction_api.get_varchar2_value
120 (p_transaction_step_id => l_transaction_step_id
121 ,p_name => 'P_TENURE_STATUS');
122 --
123 x_pei_information2 :=
124 REPLACE(SUBSTR(
125 hr_transaction_api.get_varchar2_value
126 (p_transaction_step_id => l_transaction_step_id
127 ,p_name => 'P_DATE_DETERMINE')
128 ,1,10),'/','-');
129 --
130 x_pei_information3 :=
131 REPLACE(SUBSTR(
132 hr_transaction_api.get_varchar2_value
133 (p_transaction_step_id => l_transaction_step_id
134 ,p_name => 'P_PROJECTED_TENURE_DATE')
135 ,1,10),'/','-');
136 --
137 x_pei_information4 :=
138 REPLACE(SUBSTR(
139 hr_transaction_api.get_varchar2_value
140 (p_transaction_step_id => l_transaction_step_id
141 ,p_name => 'P_ADJUSTED_DATE')
142 ,1,10),'/','-');
143 --
144 x_pei_information5 :=
145 hr_transaction_api.get_varchar2_value
146 (p_transaction_step_id => l_transaction_step_id
147 ,p_name => 'P_REASON_ADJUSTED');
148 --
149 x_pei_information6 :=
150 hr_transaction_api.get_varchar2_value
151 (p_transaction_step_id => l_transaction_step_id
152 ,p_name => 'P_SUBJECT_TO_QUOTA');
153 --
154 x_person_extra_info_id :=
155 hr_transaction_api.get_varchar2_value
156 (p_transaction_step_id => l_transaction_step_id
157 ,p_name => 'P_PERSON_EXTRA_INFO_ID');
158 --
159 hr_utility.set_location('Leaving: PQH_PROCESS_TENURE.get_tenure_details',10);
160 EXCEPTION
161 WHEN hr_utility.hr_error THEN
162 hr_utility.raise_error;
163 WHEN OTHERS THEN
164 x_pei_information1 := null;
165 x_pei_information2 := null;
166 x_pei_information3 := null;
167 x_pei_information4 := null;
168 x_pei_information5 := null;
169 x_pei_information6 := null;
170 x_person_extra_info_id := null;
171 RAISE; -- Raise error here relevant to the new tech stack.
172 END get_tenure_details;
173
174 PROCEDURE set_tenure_details (
175 x_login_person_id IN NUMBER,
176 x_person_id IN NUMBER,
177 x_item_type IN VARCHAR2,
178 x_item_key IN NUMBER,
179 x_activity_id IN NUMBER,
180 x_object_version_number IN NUMBER,
181 x_person_extra_info_id IN NUMBER,
182 x_pei_information1 IN VARCHAR2,
183 x_pei_information2 IN VARCHAR2,
184 x_pei_information3 IN VARCHAR2,
185 x_pei_information4 IN VARCHAR2,
186 x_pei_information5 IN VARCHAR2,
187 x_pei_information6 IN VARCHAR2 ) IS
188
189 l_transaction_id number;
190 l_trans_tbl hr_transaction_ss.transaction_table;
191 l_count number;
192 l_transaction_step_id number;
193 l_api_name constant hr_api_transaction_steps.api_name%TYPE := 'PQH_PROCESS_TENURE_STATUS.PROCESS_API';
194 l_result varchar2(100);
195 l_trns_object_version_number number;
196 l_review_proc_call VARCHAR2(30);
197 l_effective_date DATE ;
198
199 BEGIN
200 hr_utility.set_location('Entering: PQH_PROCESS_TENURE.set_tenure_details',5);
201 l_review_proc_call := 'PqhTenureStatusReview';
202 l_effective_date := SYSDATE;
203 --
204 hr_transaction_api.get_transaction_step_info
205 (p_item_type => x_item_type
206 ,p_item_key => x_item_key
207 ,p_activity_id => x_activity_id
208 ,p_transaction_step_id => l_transaction_step_id
209 ,p_object_version_number => l_trns_object_version_number);
210 --
211 l_count:=1;
212 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
213 l_trans_tbl(l_count).param_value := x_object_version_number;
214 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
215 --
216 l_count:=l_count+1;
217 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
218 l_trans_tbl(l_count).param_value := l_review_proc_call;
219 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
220 --
221 l_count:=l_count+1;
222 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
223 l_trans_tbl(l_count).param_value := x_activity_id;
224 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
225 --
226 l_count:=l_count+1;
227 l_trans_tbl(l_count).param_name := 'P_TENURE_STATUS';
228 l_trans_tbl(l_count).param_value := x_pei_information1;
229 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
230 --
231 l_count:=l_count+1;
232 l_trans_tbl(l_count).param_name := 'P_DATE_DETERMINE';
233 l_trans_tbl(l_count).param_value := x_pei_information2;
234 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
235 --
236 l_count:=l_count+1;
237 l_trans_tbl(l_count).param_name := 'P_PROJECTED_TENURE_DATE';
238 l_trans_tbl(l_count).param_value := x_pei_information3;
239 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
240 --
241 l_count:=l_count+1;
242 l_trans_tbl(l_count).param_name := 'P_ADJUSTED_DATE';
243 l_trans_tbl(l_count).param_value := x_pei_information4;
244 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
245 --
246 l_count:=l_count+1;
247 l_trans_tbl(l_count).param_name := 'P_REASON_ADJUSTED';
248 l_trans_tbl(l_count).param_value := x_pei_information5;
249 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
250 --
251 l_count:=l_count+1;
252 l_trans_tbl(l_count).param_name := 'P_SUBJECT_TO_QUOTA';
253 l_trans_tbl(l_count).param_value := x_pei_information6;
254 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
255 --
256 l_count:=l_count+1;
257 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
258 l_trans_tbl(l_count).param_value := x_person_id;
259 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
260 --
261 l_count:=l_count+1;
262 l_trans_tbl(l_count).param_name := 'P_PERSON_EXTRA_INFO_ID';
263 l_trans_tbl(l_count).param_value := x_person_extra_info_id;
264 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
265
266 hr_transaction_ss.save_transaction_step
267 (p_item_type => x_item_type
268 ,p_item_key => x_item_key
269 ,p_actid => x_activity_id
270 ,p_login_person_id => x_login_person_id
271 ,p_transaction_step_id => l_transaction_step_id
272 ,p_api_name => l_api_name
273 ,p_transaction_data => l_trans_tbl );
274 hr_utility.set_location('Leaving: PQH_PROCESS_TENURE.set_tenure_details',10);
275 commit;
276 EXCEPTION
277 WHEN hr_utility.hr_error THEN
278 hr_utility.raise_error;
279 WHEN OTHERS THEN
280 RAISE; -- Raise error here relevant to the new tech stack.
281 END set_tenure_details;
282 --
283 --
284 PROCEDURE process_api (
285 p_validate IN BOOLEAN DEFAULT FALSE,
286 p_transaction_step_id IN NUMBER ) IS
287 --
288 --
289 l_person_id NUMBER;
290 l_person_extra_info_id NUMBER;
291 l_ovn NUMBER;
292 --
293 --
294 l_pei_information1 VARCHAR2(255);
295 l_pei_information2 VARCHAR2(255);
296 l_pei_information3 VARCHAR2(255);
297 l_pei_information4 VARCHAR2(255);
298 l_pei_information5 VARCHAR2(255);
299 l_pei_information6 VARCHAR2(255);
300 --
301 --
302 BEGIN
303 hr_utility.set_location('Entering: PQH_PROCESS_TENURE.process_api',5);
304 --
305 savepoint process_tenure_details;
306 --
307 get_tenure_details (
308 x_transaction_step_id => p_transaction_step_id
309 ,x_pei_information1 => l_pei_information1
310 ,x_pei_information2 => l_pei_information2
311 ,x_pei_information3 => l_pei_information3
312 ,x_pei_information4 => l_pei_information4
313 ,x_pei_information5 => l_pei_information5
314 ,x_pei_information6 => l_pei_information6
315 ,x_person_extra_info_id => l_person_extra_info_id);
316
317 l_person_id := hr_transaction_api.get_varchar2_value (
318 p_transaction_step_id => p_transaction_step_id,
319 p_name => 'P_PERSON_ID');
320 --
321 --
322 l_ovn := hr_transaction_api.get_varchar2_value (
323 p_transaction_step_id => p_transaction_step_id,
324 p_name => 'P_OBJECT_VERSION_NUMBER');
325 --
326
327 IF l_pei_information2 IS NOT NULL THEN
328 l_pei_information2 := TO_CHAR(TO_DATE(l_pei_information2,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
329 END IF;
330
331 IF l_pei_information3 IS NOT NULL THEN
332 l_pei_information3 := TO_CHAR(TO_DATE(l_pei_information3,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
333 END IF;
334
335 IF l_pei_information4 IS NOT NULL THEN
336 l_pei_information4 := TO_CHAR(TO_DATE(l_pei_information4,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
337 END IF;
338
339
340 IF l_person_extra_info_id IS NOT NULL THEN
341 --
342 HR_PERSON_EXTRA_INFO_API.update_person_extra_info (
343 p_person_extra_info_id => l_person_extra_info_id,
344 p_pei_information1 => l_pei_information1 ,
345 p_pei_information2 => l_pei_information2 ,
346 p_pei_information3 => l_pei_information3 ,
347 p_pei_information4 => l_pei_information4 ,
348 p_pei_information5 => l_pei_information5 ,
349 p_pei_information6 => l_pei_information6 ,
350 p_object_version_number => l_ovn );
351 --
352 ELSE
353 --
354 HR_PERSON_EXTRA_INFO_API.create_person_extra_info (
355 p_information_type => 'PQH_TENURE_STATUS',
356 p_pei_information_category => 'PQH_TENURE_STATUS',
357 p_person_id => l_person_id ,
358 p_pei_information1 => l_pei_information1 ,
359 p_pei_information2 => l_pei_information2 ,
360 p_pei_information3 => l_pei_information3 ,
361 p_pei_information4 => l_pei_information4 ,
362 p_pei_information5 => l_pei_information5 ,
363 p_pei_information6 => l_pei_information6 ,
364 p_person_extra_info_id => l_person_extra_info_id,
365 p_object_version_number => l_ovn );
366 --
367 END IF;
368 --
369 -- ns 5/19/2005: BUG 4381336: commenting commit as it is called while
370 -- resurrecting the transaction (via update action link), it is then
371 -- attempted to rollback which would fail if committed here.
372 -- commit;
373 --
374 hr_utility.set_location('Leaving: PQH_PROCESS_TENURE.process_api',10);
375 EXCEPTION
376 WHEN hr_utility.hr_error THEN
377 ROLLBACK TO process_tenure_details;
378 RAISE;
379 WHEN OTHERS THEN
380 RAISE; -- Raise error here relevant to the new tech stack.
381 END process_api;
382
383 PROCEDURE self_or_subordinate (
384 itemtype IN VARCHAR2,
385 itemkey IN VARCHAR2,
386 actid IN NUMBER,
387 funcmode IN VARCHAR2,
388 resultout IN OUT NOCOPY VARCHAR2) IS
389 --
390 nval1 number;
391 nval2 number;
392 l_resultout varchar2(200);
393 --
394 BEGIN
395 l_resultout := resultout;
396 --
397 nval1 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE1');
398 nval2 := Wf_Engine.GetActivityAttrNumber(itemtype,itemkey,actid, 'VALUE2');
399
400 IF nval1 = nval2 THEN
401 resultout := 'SELF';
402 ELSE
403 resultout := 'SUBORDINATE';
404 END IF;
405 EXCEPTION
406 WHEN OTHERS THEN
407 resultout := l_resultout;
408 RAISE;
409 --
410 END self_or_subordinate;
411 --
412
413 END pqh_process_tenure_status;