[Home] [Help]
PACKAGE BODY: APPS.PQH_PROCESS_ACADEMIC_RANK
Source
1 PACKAGE BODY pqh_process_academic_rank AS
2 /* $Header: pqhusark.pkb 120.3 2006/12/05 07:41:24 rpasumar noship $*/
3
4 --
5 --
6 FUNCTION get_academic_rank (
7 p_transaction_step_id in varchar2 ) RETURN ref_cursor IS
8
9 csr REF_CURSOR;
10
11 BEGIN
12 OPEN csr FOR
13 select
14 rank.pei_information1,
15 fnd_date.canonical_to_date(rank.pei_information2) pei_information2,
16 fnd_date.canonical_to_date(rank.pei_information3) pei_information3,
17 rank.pei_information4 pei_information4,
18 fnd_date.canonical_to_date(rank.pei_information5) pei_information5,
19 to_number(rank.person_extra_info_id ) person_extra_info_id,
20 to_number(rank.object_version_number) object_version_number
21 from (
22 Select
23 max(pei_information1) pei_information1,
24 max(pei_information2) pei_information2,
25 max(pei_information3) pei_information3,
26 max(pei_information4) pei_information4,
27 max(pei_information5) pei_information5,
28 max(person_extra_info_id) person_extra_info_id,
29 max(object_version_number) object_version_number
30 from (
31 SELECT
32 decode(a.name, 'P_ACADEMIC_RANK' , a.varchar2_value,null) pei_information1,
33 decode(a.name, 'P_EFFECTIVE_START_DATE' , a.varchar2_value,null) pei_information2,
34 decode(a.name, 'P_EFFECTIVE_END_DATE' , a.varchar2_value ,null) pei_information3,
35 decode(a.name, 'P_NEXT_ACADEMIC_RANK' , a.varchar2_value ,null) pei_information4,
36 decode(a.name, 'P_PROJECTED_DATE' , a.varchar2_value ,null) pei_information5,
37 decode(a.name, 'P_PERSON_EXTRA_INFO_ID' , a.varchar2_value ,null) person_extra_info_id,
38 -- Bug# 5415237
39 -- Replaced a.varchar2_value with a.number_value.
40 decode(a.name, 'P_OBJECT_VERSION_NUMBER', a.number_value ,null) object_version_number
41 FROM hr_api_transaction_steps s,
42 hr_api_transaction_values a
43 WHERE s.transaction_step_id = a.transaction_step_id
44 and s.transaction_step_id = p_transaction_step_id
45 AND s.api_name = 'PQH_PROCESS_ACADEMIC_RANK.PROCESS_API'
46 )
47 ) rank ;
48
49 RETURN csr;
50
51 END;
52
53 --
54 --
55
56 PROCEDURE get_academic_rank_details (
57 x_transaction_step_id in varchar2
58 ,x_pei_information1 out nocopy varchar2
59 ,x_pei_information2 out nocopy varchar2
60 ,x_pei_information3 out nocopy varchar2
61 ,x_pei_information4 out nocopy varchar2
62 ,x_pei_information5 out nocopy varchar2
63 ,x_person_extra_info_id out nocopy varchar2 ) IS
64
65 l_transaction_step_id number;
66 l_api_name hr_api_transaction_steps.api_name%TYPE;
67
68 BEGIN
69 hr_utility.set_location('Entering: PQH_PROCESS_ACADEMIC_RANK.get_academic_rank_details',5);
70 --
71 l_transaction_step_id := to_number(x_transaction_step_id);
72 --
73
74 if l_transaction_step_id is null then
75 return;
76 end if;
77 --
78
79 x_pei_information1 :=
80 hr_transaction_api.get_varchar2_value
81 (p_transaction_step_id => l_transaction_step_id
82 ,p_name => 'P_ACADEMIC_RANK');
83 --
84 x_pei_information2 :=
85 REPLACE(SUBSTR(
86 hr_transaction_api.get_varchar2_value
87 (p_transaction_step_id => l_transaction_step_id
88 ,p_name => 'P_EFFECTIVE_START_DATE')
89 ,1,10),'/','-');
90 --
91 x_pei_information3 :=
92 REPLACE(SUBSTR(
93 hr_transaction_api.get_varchar2_value
94 (p_transaction_step_id => l_transaction_step_id
95 ,p_name => 'P_EFFECTIVE_END_DATE')
96 ,1,10),'/','-');
97 --
98 x_pei_information4 :=
99 hr_transaction_api.get_varchar2_value
100 (p_transaction_step_id => l_transaction_step_id
101 ,p_name => 'P_NEXT_ACADEMIC_RANK');
102 --
103 x_pei_information5 :=
104 REPLACE(SUBSTR(
105 hr_transaction_api.get_varchar2_value
106 (p_transaction_step_id => l_transaction_step_id
107 ,p_name => 'P_PROJECTED_DATE')
108 ,1,10),'/','-');
109 --
110 x_person_extra_info_id :=
111 hr_transaction_api.get_varchar2_value
112 (p_transaction_step_id => l_transaction_step_id
113 ,p_name => 'P_PERSON_EXTRA_INFO_ID');
114 --
115 hr_utility.set_location('Leaving: PQH_PROCESS_ACADEMIC_RANK.get_academic_rank_details',5);
116 EXCEPTION
117 WHEN hr_utility.hr_error THEN
118 hr_utility.raise_error;
119 WHEN OTHERS THEN
120 x_pei_information1 := null;
121 x_pei_information2 := null;
122 x_pei_information3 := null;
123 x_pei_information4 := null;
124 x_pei_information5 := null;
125 x_person_extra_info_id := null;
126 RAISE; -- Raise error here relevant to the new tech stack.
127
128 END get_academic_rank_details;
129
130 PROCEDURE set_academic_rank_details (
131 x_login_person_id IN NUMBER,
132 x_person_id IN NUMBER,
133 x_item_type IN VARCHAR2,
134 x_item_key IN NUMBER,
135 x_activity_id IN NUMBER,
136 x_object_version_number IN NUMBER,
137 x_person_extra_info_id IN NUMBER,
138 x_pei_information1 IN VARCHAR2,
139 x_pei_information2 IN VARCHAR2,
140 x_pei_information3 IN VARCHAR2,
141 x_pei_information4 IN VARCHAR2,
142 x_pei_information5 IN VARCHAR2 ) IS
143
144 l_transaction_id number;
145 l_trans_tbl hr_transaction_ss.transaction_table;
146 l_count number;
147 l_transaction_step_id number;
148 l_api_name constant hr_api_transaction_steps.api_name%TYPE := 'PQH_PROCESS_ACADEMIC_RANK.PROCESS_API';
149 l_result varchar2(100);
150 l_trns_object_version_number number;
151 l_review_proc_call VARCHAR2(30);
152 l_effective_date DATE ;
153
154 BEGIN
155 hr_utility.set_location('Entering: PQH_PROCESS_ACADEMIC_RANK.set_academic_rank_details',5);
156 l_review_proc_call := 'PqhAcademicRankReview';
157 l_effective_date := SYSDATE;
158 --
159 hr_transaction_api.get_transaction_step_info
160 (p_item_type => x_item_type
161 ,p_item_key => x_item_key
162 ,p_activity_id => x_activity_id
163 ,p_transaction_step_id => l_transaction_step_id
164 ,p_object_version_number => l_trns_object_version_number);
165 --
166 l_count:=1;
167 l_trans_tbl(l_count).param_name := 'P_OBJECT_VERSION_NUMBER';
168 l_trans_tbl(l_count).param_value := x_object_version_number;
169 l_trans_tbl(l_count).param_data_type := 'NUMBER';
170 --
171 l_count:=l_count+1;
172 l_trans_tbl(l_count).param_name := 'P_REVIEW_PROC_CALL';
173 l_trans_tbl(l_count).param_value := l_review_proc_call;
174 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
175 --
176 l_count:=l_count+1;
177 l_trans_tbl(l_count).param_name := 'P_REVIEW_ACTID';
178 l_trans_tbl(l_count).param_value := x_activity_id;
179 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
180 --
181 l_count:=l_count+1;
182 l_trans_tbl(l_count).param_name := 'P_ACADEMIC_RANK';
183 l_trans_tbl(l_count).param_value := x_pei_information1;
184 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
185 --
186 l_count:=l_count+1;
187 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_START_DATE';
188 l_trans_tbl(l_count).param_value := x_pei_information2;
189 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
190 --
191 l_count:=l_count+1;
192 l_trans_tbl(l_count).param_name := 'P_EFFECTIVE_END_DATE';
193 l_trans_tbl(l_count).param_value := x_pei_information3;
194 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
195 --
196 l_count:=l_count+1;
197 l_trans_tbl(l_count).param_name := 'P_NEXT_ACADEMIC_RANK';
198 l_trans_tbl(l_count).param_value := x_pei_information4;
199 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
200 --
201 l_count:=l_count+1;
202 l_trans_tbl(l_count).param_name := 'P_PROJECTED_DATE';
203 l_trans_tbl(l_count).param_value := x_pei_information5;
204 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
205 --
206 l_count:=l_count+1;
207 l_trans_tbl(l_count).param_name := 'P_PERSON_ID';
208 l_trans_tbl(l_count).param_value := x_person_id;
209 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
210 --
211 l_count:=l_count+1;
212 l_trans_tbl(l_count).param_name := 'P_PERSON_EXTRA_INFO_ID';
213 l_trans_tbl(l_count).param_value := x_person_extra_info_id;
214 l_trans_tbl(l_count).param_data_type := 'VARCHAR2';
215
216 hr_transaction_ss.save_transaction_step
217 (p_item_type => x_item_type
218 ,p_item_key => x_item_key
219 ,p_actid => x_activity_id
220 ,p_login_person_id => x_login_person_id
221 ,p_transaction_step_id => l_transaction_step_id
222 ,p_api_name => l_api_name
223 ,p_transaction_data => l_trans_tbl );
224
225 hr_utility.set_location('Leaving: PQH_PROCESS_ACADEMIC_RANK.set_academic_rank_details',5);
226 commit;
227 EXCEPTION
228 WHEN hr_utility.hr_error THEN
229 hr_utility.raise_error;
230 WHEN OTHERS THEN
231 RAISE; -- Raise error here relevant to the new tech stack.
232 END set_academic_rank_details;
233 --
234 --
235 PROCEDURE process_api (
236 p_validate IN BOOLEAN DEFAULT FALSE,
237 p_transaction_step_id IN NUMBER,
238 p_effective_date IN VARCHAR2 DEFAULT NULL ) IS
239 --
240 l_person_id NUMBER;
241 l_person_extra_info_id NUMBER;
242 l_ovn NUMBER;
243
244 l_pei_information1 VARCHAR2(255);
245 l_pei_information2 VARCHAR2(255);
246 l_pei_information3 VARCHAR2(255);
247 l_pei_information4 VARCHAR2(255);
248 l_pei_information5 VARCHAR2(255);
249
250 BEGIN
251 hr_utility.set_location('Entering: PQH_PROCESS_ACADEMIC_RANK.process_api',5);
252 --
253 savepoint process_academic_rank_details;
254 --
255
256 get_academic_rank_details (
257 x_transaction_step_id => p_transaction_step_id
258 ,x_pei_information1 => l_pei_information1
259 ,x_pei_information2 => l_pei_information2
260 ,x_pei_information3 => l_pei_information3
261 ,x_pei_information4 => l_pei_information4
262 ,x_pei_information5 => l_pei_information5
263 ,x_person_extra_info_id => l_person_extra_info_id);
264
265 l_person_id := hr_transaction_api.get_varchar2_value (
266 p_transaction_step_id => p_transaction_step_id,
267 p_name => 'P_PERSON_ID');
268 --
269 l_ovn := hr_transaction_api.get_number_value (
270 p_transaction_step_id => p_transaction_step_id,
271 p_name => 'P_OBJECT_VERSION_NUMBER');
272 --
273 IF l_pei_information2 IS NOT NULL THEN
274 l_pei_information2 := TO_CHAR(TO_DATE(l_pei_information2,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
275 END IF;
276
277 IF l_pei_information3 IS NOT NULL THEN
278 l_pei_information3 := TO_CHAR(TO_DATE(l_pei_information3,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
279 END IF;
280
281 IF l_pei_information5 IS NOT NULL THEN
282 l_pei_information5 := TO_CHAR(TO_DATE(l_pei_information5,'RRRR-MM-DD'),'RRRR/MM/DD HH24:MI:SS');
283 END IF;
284
285 IF l_person_extra_info_id IS NOT NULL THEN
286
287 HR_PERSON_EXTRA_INFO_API.update_person_extra_info (
288 p_person_extra_info_id => l_person_extra_info_id,
289 p_pei_information_category => 'PQH_ACADEMIC_RANK',
290 p_pei_information1 => l_pei_information1 ,
291 p_pei_information2 => l_pei_information2 ,
292 p_pei_information3 => l_pei_information3 ,
293 p_pei_information4 => l_pei_information4 ,
294 p_pei_information5 => l_pei_information5 ,
295 p_object_version_number => l_ovn );
296 --
297 ELSE
298 --
299 HR_PERSON_EXTRA_INFO_API.create_person_extra_info (
300 p_information_type => 'PQH_ACADEMIC_RANK',
301 p_pei_information_category => 'PQH_ACADEMIC_RANK',
302 p_person_id => l_person_id ,
303 p_pei_information1 => l_pei_information1 ,
304 p_pei_information2 => l_pei_information2 ,
305 p_pei_information3 => l_pei_information3 ,
306 p_pei_information4 => l_pei_information4 ,
307 p_pei_information5 => l_pei_information5 ,
308 p_person_extra_info_id => l_person_extra_info_id,
309 p_object_version_number => l_ovn );
310 --
311 END IF;
312 --
313 hr_utility.set_location('Leaving: PQH_PROCESS_ACADEMIC_RANK.process_api',5);
314 --
315 -- ns 5/19/2005: BUG 4381336: commenting commit as it is called while
316 -- resurrecting the transaction (via update action link), it is then
317 -- attempted to rollback which would fail if committed here.
318 -- commit;
319 --
320 EXCEPTION
321 WHEN hr_utility.hr_error THEN
322 ROLLBACK TO process_academic_rank_details;
323 RAISE;
324 WHEN OTHERS THEN
325 RAISE; -- Raise error here relevant to the new tech stack.
326 END process_api;
327 --
328 END;