[Home] [Help]
PACKAGE BODY: APPS.PQH_AME_APPROVALS
Source
1 PACKAGE BODY pqh_ame_approvals AS
2 /* $Header: pqameapr.pkb 120.5 2005/06/23 13:37:24 nsanghal noship $ */
3 --
4
5 g_package constant varchar2(32) := ' pqh_ame_approvals.';
6
7 -- ---------------------------------------------------------------------------
8 -- ------------------- <set_route_to_user> -----------------------------------
9 -- ---------------------------------------------------------------------------
10 procedure set_route_to_user (
11 p_itemType in varchar2
12 ,p_itemKey in varchar2
13 ,p_forward_to_person_id in number
14 ,p_result out nocopy varchar2 ) Is
15 --
16 l_forward_to_username varchar2(240);
17 l_forward_to_disp_name varchar2(240);
18 l_proc constant varchar2(72):= g_package||'set_route_to_user';
19 --
20 BEGIN
21
22 hr_utility.set_location('Entering:'||l_proc, 5);
23 wf_directory.GetUserName
24 (p_orig_system => 'PER'
25 ,p_orig_system_id => p_forward_to_person_id
26 ,p_name => l_forward_to_username
27 ,p_display_name => l_forward_to_disp_name);
28
29 wf_engine.SetItemAttrText
30 (itemtype => p_itemtype
31 ,itemkey => p_itemkey
32 ,aname => 'PARAMETER10_VALUE'
33 ,avalue => p_forward_to_person_id);
34
35 if (l_forward_to_username is null) then
36 p_result := 'NO_USER';
37 else
38 wf_engine.SetItemAttrText
39 (itemtype => p_itemtype
40 ,itemkey => p_itemkey
41 ,aname => 'ROUTE_TO_USER'
42 ,avalue => l_forward_to_username);
43 end if;
44 --
45 hr_utility.set_location(' Leaving:'||l_proc, 10);
46 Exception
47 When Others Then
48 Raise;
49 END set_route_to_user;
50
51
52 -- ---------------------------------------------------------------------------
53 -- ------------------- <get_txn_type_info> -----------------------------------
54 -- ---------------------------------------------------------------------------
55 procedure get_txn_type_info(
56 p_item_type in varchar2
57 ,p_item_key in varchar2
58 ,p_txn_type out nocopy varchar2
59 ,p_txn_app_id out nocopy number ) is
60
61 l_proc constant varchar2(72):= g_package||'get_txn_type_info';
62 begin
63
64 hr_utility.set_location('Entering:'||l_proc, 5);
65 p_txn_type := wf_engine.GetItemAttrText
66 (itemtype => p_item_type,
67 itemkey => p_item_key,
68 aname => 'PARAMETER2_VALUE' );
69
70 p_txn_app_id := wf_engine.GetItemAttrText
71 (itemtype => p_item_type,
72 itemkey => p_item_key,
73 aname => 'PARAMETER3_VALUE' );
74 hr_utility.set_location(' Leaving:'||l_proc, 10);
75 Exception
76 When Others Then
77 Raise;
78 end;
79 --
80 -- ---------------------------------------------------------------------------
81 -- ------------------- <Check_Final_Approver> --------------------------------
82 -- ---------------------------------------------------------------------------
83 PROCEDURE check_final_approver (
84 p_itemType IN varchar2,
85 p_itemKey in varchar2,
86 p_actId in number,
87 p_funmode in varchar2,
88 p_result out nocopy varchar2 ) IS
89 --
90 l_forward_from_username varchar2(30);
91 l_forward_from_person_id number;
92 l_forward_to_person_id number;
93 c_next_approver_rec ame_util.approverRecord;
94 --
95 l_transaction_id number(18);
96 l_txn_type varchar2(30);
97 l_txn_app_id number(18);
98 --
99 l_proc constant varchar2(72):= g_package||'check_final_approver';
100 BEGIN
101
102 hr_utility.set_location('Entering:'||l_proc, 5);
103
104 if ( p_funmode = 'RUN' ) then
105 l_transaction_id := pqh_ss_workflow.get_transaction_id(p_itemType, p_itemKey);
106
107 l_forward_from_person_id := wf_engine.GetItemAttrText (
108 itemtype => p_itemtype,
109 itemkey => p_itemkey,
110 aname => 'PARAMETER10_VALUE' );
111
112 get_txn_type_info(
113 p_item_type => p_itemType
114 ,p_item_key => p_itemKey
115 ,p_txn_type => l_txn_type
116 ,p_txn_app_id => l_txn_app_id );
117
118 -- Status not to be updated to approved for the first time
119 -- when the forward to person is null
120 if ( l_forward_from_person_id is not null) then
121 ame_api.updateApprovalStatus2(
122 applicationIdIn => l_txn_app_id,
123 transactionIdIn => l_transaction_id,
124 transactionTypeIn => l_txn_type,
125 approvalStatusIn => ame_util.approvedStatus,
126 approverPersonIdIn => l_forward_from_person_id,
127 approverUserIdIn => null,
128 forwardeeIn => null);
129 end if;
130
131 ame_api.getNextApprover(applicationIdIn=> l_txn_app_id,
132 transactionIdIn => l_transaction_id,
133 transactionTypeIn => l_txn_type,
134 nextApproverOut => c_next_approver_rec);
135
136 if ( c_next_approver_rec.person_id is null) then
137 p_result := 'COMPLETE:T';
138 else
139 p_result := 'COMPLETE:F';
140 end if;
141 --
142 end if;
143 hr_utility.set_location(' Leaving:'||l_proc, 10);
144
145 Exception
146 When Others Then
147 -- Set error attribute and complete activity with result error
148 wf_engine.SetItemAttrText (
149 itemtype => p_itemType
150 ,itemkey => p_ItemKey
151 ,aname => 'SYSTEM_ERROR'
152 ,avalue => sqlerrm);
153
154 p_result := 'COMPLETE:E';
155 END;
156
157 -- ---------------------------------------------------------------------------
158 -- ------------------- <Approve_Reject_Elctbl_Chc> ---------------------------
159 -- ---------------------------------------------------------------------------
160 PROCEDURE approve_reject_elctbl_chc (
161 p_itemType IN varchar2,
162 p_itemKey in varchar2,
163 p_app_rej in varchar2 ) IS
164 l_elctbl_chc_id number(18);
165 l_proc constant varchar2(72):= g_package||'approve_reject_elctbl_chc';
166 l_ovn number(15);
167 BEGIN
168
169 hr_utility.set_location('Entering:'||l_proc, 5);
170 -- Get The electable choice id from WF attribute
171 l_elctbl_chc_id := wf_engine.GetItemAttrText
172 (itemtype => p_itemtype,
173 itemkey => p_itemkey,
174 aname => 'PARAMETER1_VALUE' );
175
176 -- Get the OVN to call update api
177 Select object_version_number
178 into l_ovn
179 From ben_elig_per_elctbl_chc
180 Where elig_per_elctbl_chc_id = l_elctbl_chc_id;
181
182 -- Call the API to update the status code
183 ben_elig_per_elc_chc_api.update_elig_per_elc_chc (
184 p_elig_per_elctbl_chc_id => l_elctbl_chc_id
185 ,p_approval_status_cd => p_app_rej
186 ,p_object_version_number => l_ovn
187 ,p_effective_date => sysdate);
188
189 hr_utility.set_location(' Leaving:'||l_proc, 10);
190 Exception
191 When Others Then
192 Raise;
193 END approve_reject_elctbl_chc ;
194
195 -- ---------------------------------------------------------------------------
196 -- ------------------- <Mark_Elctbl_Chc_Approved> ----------------------------
197 -- ---------------------------------------------------------------------------
198 PROCEDURE mark_elctbl_chc_approved (
199 p_itemType IN varchar2,
200 p_itemKey in varchar2,
201 p_actId in number,
202 p_funmode in varchar2,
203 p_result out nocopy varchar2 ) IS
204
205 l_elctbl_chc_id varchar2(30);
206
207 l_proc constant varchar2(72):= g_package||'mark_elctbl_chc_approved';
208 BEGIN
209
210 hr_utility.set_location('Entering:'||l_proc, 5);
211 if ( p_funmode = 'RUN' ) then
212 approve_reject_elctbl_chc (
213 p_itemType => p_itemType
214 ,p_itemKey => p_itemKey
215 ,p_app_rej => 'PQH_GSP_A');
216
217 --get electable choice id
218 end if;
219
220 p_result := 'COMPLETE:T';
221
222 hr_utility.set_location(' Leaving:'||l_proc, 10);
223 Exception
224 When Others Then
225 wf_core.context(g_package,'mark_elctbl_chc_approved',p_itemType,p_itemKey);
226 Raise;
227 END;
228
229 -- ---------------------------------------------------------------------------
230 -- ------------------- <Mark_Elctbl_Chc_Rejected> ----------------------------
231 -- ---------------------------------------------------------------------------
232 PROCEDURE mark_elctbl_chc_rejected (
233 p_itemType IN varchar2,
234 p_itemKey in varchar2,
235 p_actId in number,
236 p_funmode in varchar2,
237 p_result out nocopy varchar2 ) IS
238
239 l_elctbl_chc_id varchar2(30);
240
241 l_proc constant varchar2(72):= g_package||'mark_elctbl_chc_rejected';
242 BEGIN
243
244 hr_utility.set_location('Entering:'||l_proc, 5);
245 if ( p_funmode = 'RUN' ) then
246 approve_reject_elctbl_chc (
247 p_itemType => p_itemType
248 ,p_itemKey => p_itemKey
249 ,p_app_rej => 'PQH_GSP_R');
250 end if;
251
252 p_result := 'COMPLETE:T';
253 hr_utility.set_location(' Leaving:'||l_proc, 10);
254 Exception
255 When Others Then
256 wf_core.context(g_package,'mark_elctbl_chc_rejected',p_itemType,p_itemKey);
257 Raise;
258 END mark_elctbl_chc_rejected;
259 --
260 PROCEDURE unmark_wf_flag_for_elctbl_chc (
261 p_itemType IN varchar2,
262 p_itemKey in varchar2,
263 p_actId in number,
264 p_funmode in varchar2,
265 p_result out nocopy varchar2 ) IS
266 l_proc constant varchar2(72):= g_package||'unmark_wf_flag_for_elctbl_chc';
267 l_ovn number;
268 l_elctbl_chc_id number;
269 BEGIN
270 hr_utility.set_location('Entering:'||l_proc, 5);
271 --
272 if ( p_funmode = 'RUN' ) then
273 l_elctbl_chc_id := wf_engine.GetItemAttrText (
274 itemtype => p_itemtype,
275 itemkey => p_itemkey,
276 aname => 'PARAMETER1_VALUE' );
277 Select object_version_number
278 into l_ovn
279 From ben_elig_per_elctbl_chc
280 Where elig_per_elctbl_chc_id = l_elctbl_chc_id;
281
282 ben_elig_per_elc_chc_api.update_elig_per_elc_chc (
283 p_elig_per_elctbl_chc_id => l_elctbl_chc_id
284 ,p_in_pndg_wkflow_flag => 'N'
285 ,p_object_version_number => l_ovn
286 ,p_effective_date => sysdate);
287 end if;
288 --
289 hr_utility.set_location(' Leaving:'||l_proc, 10);
290 Exception
291 When Others Then
292 wf_core.context(g_package,'unmark_wf_flag_for_elctbl_chc',p_itemType,p_itemKey);
293 Raise;
294 END unmark_wf_flag_for_elctbl_chc;
295
296
297 -- ---------------------------------------------------------------------------
298 -- ---------------------- <Initialize_AME> -----------------------------------
299 -- ---------------------------------------------------------------------------
300 PROCEDURE initialize_ame (
301 p_itemType IN varchar2,
302 p_itemKey in varchar2,
303 p_actId in number,
304 p_funmode in varchar2,
305 p_result out nocopy varchar2 ) IS
306 --
307 l_txn_id number ;
308 l_txn_type varchar2(30);
309 l_txn_app_id number(18);
310 l_person_id number(18);
311 --
312 l_proc constant varchar2(72):= g_package||'initialize_ame';
313 l_result varchar2(30);
314 BEGIN
315 hr_utility.set_location('Entering:'||l_proc, 5);
316
317 if ( p_funmode = 'RUN' ) then
318 p_result := 'COMPLETE:T';
319 end if;
320 hr_utility.set_location(' Leaving:'||l_proc, 10);
321 Exception
322 When Others Then
323 -- Set error attribute and complete activity with result error
324 wf_engine.SetItemAttrText (
325 itemtype => p_itemType
326 ,itemkey => p_ItemKey
327 ,aname => 'SYSTEM_ERROR'
328 ,avalue => sqlerrm);
329
330 p_result := 'COMPLETE:E';
331 END;
332
333 -- ---------------------------------------------------------------------------
334 -- ------------------- <Find_Next_Approver> ----------------------------------
335 -- ---------------------------------------------------------------------------
336 PROCEDURE find_next_approver (
337 p_itemType IN varchar2,
338 p_itemKey in varchar2,
339 p_actId in number,
340 p_funmode in varchar2,
341 p_result out nocopy varchar2 ) IS
342 --
343 c_next_approver_rec ame_util.approverRecord;
344 l_forward_to_person_id number;
345 l_forward_from_username varchar2(240);
346 l_forward_from_person_id number;
347 l_forward_to_username varchar2(240);
348 l_forward_to_disp_name varchar2(240);
349 l_final_approver varchar2(10);
350 l_txn_id number ;
351 l_txn_type varchar2(30);
352 l_txn_app_id number(18);
353 l_dummy varchar2(240);
354 l_result varchar2(30);
355
356 l_proc constant varchar2(72):= g_package||'find_next_approver';
357 BEGIN
358
359 hr_utility.set_location('Entering:'||l_proc, 5);
360
361 l_txn_id := pqh_ss_workflow.get_transaction_id(p_itemType, p_itemKey);
362
363 if ( p_funmode = 'RUN' ) then
364 get_txn_type_info(
365 p_item_type => p_itemType
366 ,p_item_key => p_itemKey
367 ,p_txn_type => l_txn_type
368 ,p_txn_app_id => l_txn_app_id );
369
370 ame_api.getNextApprover(applicationIdIn=> l_txn_app_id,
371 transactionIdIn => l_txn_id,
372 transactionTypeIn => l_txn_type,
373 nextApproverOut => c_next_approver_rec);
374
375 l_forward_to_person_id := c_next_approver_rec.person_id;
376
377 if ( l_forward_to_person_id is null ) then
378 --
379 p_result := 'COMPLETE:F';
380 --
381 else
382 --
383 set_route_to_user (
384 p_itemType => p_itemType
385 ,p_itemKey => p_itemKey
386 ,p_forward_to_person_id => l_forward_to_person_id
387 ,p_result => l_result);
388
389 --
390 -- Might have to fetch previous value of route_to_user and set to
391 -- routed_by_user
392 p_result := 'COMPLETE:T';
393 --
394 end if;
395 --
396 --
397 end if;
398
399 hr_utility.set_location(' Leaving:'||l_proc, 10);
400 Exception
401 When Others Then
402 wf_engine.SetItemAttrText (
403 itemtype => p_itemType
404 ,itemkey => p_ItemKey
405 ,aname => 'SYSTEM_ERROR'
406 ,avalue => sqlerrm);
407
408 p_result := 'COMPLETE:E';
409
410 END;
411
412 END; -- Package Body PQH_AME_APPROVALS