[Home] [Help]
PACKAGE BODY: APPS.PAY_ASSG_COST_WF
Source
1 package body PAY_ASSG_COST_WF as
2 /* $Header: pyacoswf.pkb 120.1.12010000.7 2009/02/04 06:07:19 pgongada noship $ */
3 --
4 -- Package Variables
5 --
6 g_package varchar2(33) := ' PAY_ASSG_COST_WF.';
7 g_debug boolean := hr_utility.debug_enabled;
8 g_approver ame_util.approversTable2;
9 /*Used to get the transaction id pertaining to the specified
10 item type and item key. If the transaction id does not exists
11 then it will create starting a transaction.*/
12 FUNCTION GET_TRANSACTION_ID(
13 itemtype in varchar2,
14 itemkey in varchar2,
15 actid in number,
16 funcmode in varchar2) RETURN NUMBER IS
17
18 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
19 l_result varchar2(100);
20 l_performer_id number(10);
21 l_proc varchar2(100) := g_package||'GET_TRANSACTION_ID';
22 BEGIN
23 hr_utility.set_location('Entering ... '||l_proc,10);
24 l_transaction_id := wf_engine.GetItemAttrNumber(
25 itemtype => itemtype
26 ,itemkey => itemkey
27 ,aname => 'TRANSACTION_ID'
28 ,ignore_notfound => false
29 );
30 /*If no transaction exists, start a transaction.*/
31 IF L_TRANSACTION_ID IS NULL THEN
32 hr_utility.set_location(l_proc || '....Step1',20);
33 l_performer_id := wf_engine.GetItemAttrNumber(itemtype, itemkey, 'PERFORMER_PERSON_ID');
34
35 hr_utility.set_location(l_proc , 30);
36
37 hr_transaction_ss.start_transaction
38 (itemtype => itemtype
39 ,itemkey => itemkey
40 ,actid => actid
41 ,funmode => 'RUN'
42 ,p_login_person_id => l_performer_id
43 ,result => l_result);
44 hr_utility.set_location(l_proc, 40);
45
46 /*Gett the newly created transaction and set as workflow attribute*/
47 l_transaction_id:= hr_transaction_ss.get_transaction_id
48 (p_item_type => itemtype
49 ,p_item_key => itemkey);
50 wf_engine.SetItemAttrNumber(itemtype,itemkey,'TRANSACTION_ID',l_transaction_id);
51 hr_utility.set_location(l_proc ,50);
52
53 END IF;
54 hr_utility.set_location('Leaving....'||l_proc ,60);
55 RETURN l_transaction_id;
56
57 END GET_TRANSACTION_ID;
58 procedure check_approvers_exist
59 (itemtype in varchar2,
60 itemkey in varchar2,
61 actid in number,
62 funcmode in varchar2,
63 resultout out nocopy varchar2)
64 is
65 all_approvers ame_util.approversTable2;
66 next_approver ame_util.approversTable2;
67 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
68 l_apprvl_process_complete varchar2(1000);
69 l_proc varchar2(72) := g_package||'check_approvers_exist';
70 l_flagApproversAsNotified varchar2(10);
71 l_role_name varchar2(240);
72 l_role_display_name varchar2(240);
73
74 l_wf_admin ame_util.approverRecord2;
75 l_admin_name varchar2(240);
76 l_admin_display_name varchar2(240);
77 begin
78 hr_utility.set_location('Entering:'|| l_proc, 10);
79 /*Get the transaction id*/
80 l_transaction_id := get_transaction_id(
81 itemtype => itemtype
82 ,itemkey => itemkey
83 ,actid => actid
84 ,funcmode => funcmode
85 );
86 /*Find out any approvers are there. If there are no approvers then
87 the following call raise exception. Catch that exception and set the
88 resultout to 'F'. The WF/AME Admin gets the notification of no approver.
89 Once he setup the approver then the notification would go to the approver
90 as expected. We are not supposed to reject any Workflow in the absense of
91 approver.*/
92
93 hr_utility.set_location(l_proc, 20);
94 resultout := ame_util.booleanTrue;
95 begin
96 ame_api2.getNextApprovers4(
97 applicationIdIn => 801,
98 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
99 transactionIdIn => l_transaction_id,
100 approvalProcessCompleteYNOut => l_apprvl_process_complete,
101 nextApproversOut => g_approver);
102 if (l_apprvl_process_complete = ame_util.booleanTrue) then
103 resultout := 'F';
104 else
105 resultout := 'T';
106 end if;
107 EXCEPTION
108 /*This is to catch the exception when there is no approver etc.
109 Once the exception raises, then the AME/workflow admin will get
110 the notification with the exception message raised.*/
111 when OTHERS then
112 wf_engine.setItemAttrText(itemtype, itemkey,'AME_EXCEPTION',SQLERRM(SQLCODE));
113 resultout:='F';
114 end;
115 if resultout <> 'F' and l_apprvl_process_complete <> ame_util.booleanTrue then
116 /*First clear all approvers, then get the approver again. This is to by-pass
117 NO_DATA_FOUND exception. This situation occurs when there is no approver
118 and resume the workflow after setting the approver.*/
119 ame_api2.clearAllApprovals(
120 APPLICATIONIDIN => 801,
121 TRANSACTIONTYPEIN => 'PAY_ASSIGNMENT_COSTING',
122 TRANSACTIONIDIN => l_transaction_id);
123 /*Now get the approver again*/
124 ame_api2.getNextApprovers4(
125 applicationIdIn => 801,
126 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
127 transactionIdIn => l_transaction_id,
128 approvalProcessCompleteYNOut => l_apprvl_process_complete,
129 nextApproversOut => g_approver);
130 if (l_apprvl_process_complete <> ame_util.booleanTrue) then
131 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME',g_approver(1).name);
132 wf_directory.GetRoleName(p_orig_system => NVL(g_approver(1).orig_system,'PER')
133 ,p_orig_system_id => g_approver(1).orig_system_id
134 ,p_name => l_role_name
135 ,p_display_name => l_role_display_name);
136 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_NAME',l_role_display_name);
137 resultout:='T';
138 end if;
139 end if;
140 EXCEPTION
141 when OTHERS then
142 resultout:='F';
143 end check_approvers_exist;
144
145
146 -- ------------------------------------------------------------------------
147 -- |-------------------------< GET_NEXT_APPROVER >-------------------------|
148 -- ------------------------------------------------------------------------
149 procedure get_next_approver
150 (itemtype in varchar2,
151 itemkey in varchar2,
152 actid in number,
153 funcmode in varchar2,
154 resultout out nocopy varchar2) is
155
156 l_current_approver ame_util.approversTable2 := g_approver;
157 l_next_approver ame_util.approversTable2;
158
159 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
160 l_apprvl_process_complete varchar2(1000);
161 l_proc varchar2(72);
162 l_flagApproversAsNotified varchar2(10);
163 l_role_name varchar2(240);
164 l_role_display_name varchar2(240);
165
166 begin
167 hr_utility.set_location('Entering ... '||l_proc, 10);
168 /*Get the transaction id*/
169 l_transaction_id := get_transaction_id(
170 itemtype => itemtype
171 ,itemkey => itemkey
172 ,actid => actid
173 ,funcmode => funcmode
174 );
175
176 /*Update the status of the previous approver to true as the control
177 comes to this place once the approver approves it.*/
178
179 hr_utility.set_location(l_proc,20);
180 ame_api2.updateApprovalStatus2(
181 applicationIdIn =>801,
182 transactionTypeIn =>'PAY_ASSIGNMENT_COSTING',
183 transactionIdIn =>l_transaction_id,
184 approvalStatusIn => ame_util.approvedStatus,
185 approverNameIn => wf_engine.getItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME'),
186 updateItemIn => true);
187
188 /*Now get the next approver.*/
189 hr_utility.set_location(l_proc,30);
190 ame_api2.getNextApprovers4(
191 applicationIdIn => 801,
192 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
193 transactionIdIn => l_transaction_id,
194 approvalProcessCompleteYNOut => l_apprvl_process_complete,
195 nextApproversOut => g_approver);
196
197 /*If there are approvers, then send notifications to the approver(s) by setting
198 the attribute APPROVER_LOGIN_NAME. Workflow sends notifications to the approvers
199 If there are no approvers then we need to save the data. Once the resultout
200 is set to 'F' workflow take care of saving the data by calling underlaying
201 APIs*/
202 hr_utility.set_location(l_proc, 40);
203 if l_apprvl_process_complete = ame_util.booleanFalse then
204 /*Set the previous approver attributes.*/
205 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_LOGIN_NAME',
206 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_LOGIN_NAME'));
207 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_NAME',
208 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_NAME'));
209
210 /*Set the current approver attributes.*/
211 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_LOGIN_NAME',g_approver(1).name);
212 /*Get the approver name from wf_roles.*/
213 wf_directory.GetRoleName(p_orig_system => NVL(g_approver(1).orig_system,'PER')
214 ,p_orig_system_id => g_approver(1).orig_system_id
215 ,p_name => l_role_name
216 ,p_display_name => l_role_display_name);
217 wf_engine.setItemAttrText(itemtype, itemkey,'APPROVER_NAME',l_role_display_name);
218
219 resultout := 'T';
220 else
221 resultout := 'F';
222 end if;
223 hr_utility.set_location('Leaving.....'||l_proc,50);
224 EXCEPTION
225 WHEN OTHERS THEN
226 hr_utility.set_location('An EXCEPTION occured '||SQLERRM(SQLCODE),60);
227 hr_utility.set_location('Leaving ..'||l_proc,1000);
228 RAISE;
229 end get_next_approver;
230 -- ------------------------------------------------------------------------
231 -- |--------------------------< START_WF_PROCESS>-------------------------|
232 -- ------------------------------------------------------------------------
233 procedure START_WF_PROCESS (P_PERSON_ID IN NUMBER
234 ,P_ASSIGNMENT_ID IN NUMBER
235 ,P_ITEM_KEY IN VARCHAR2
236 ,P_PERFORMER_LOGIN_NAME IN VARCHAR2
237 ,P_PERFORMER_ID in number
238 ,P_EFFECTIVE_DATE IN DATE
239 ) is
240
241 l_item_type varchar2(8) := 'PYASGWF';
242 l_process varchar2(30) := 'PYASGWF_PROCESS';
243 l_employee_name varchar2(240) := null;
244 l_requestor_name varchar2(240);
245 l_login_name varchar2(240);
246 l_proc varchar2(72) ;
247 --
248 l_user_key varchar2(240) := p_Item_Key;
249 --
250 l_wf_admin ame_util.approverRecord2;
251 l_admin_name varchar2(240);
252 l_admin_display_name varchar2(240);
253
254 CURSOR csr_person_name is
255 select FULL_NAME
256 from per_all_people_f
257 where person_id = p_person_id
258 and trunc(sysdate) between effective_start_date and effective_end_date;
259 BEGIN
260 hr_utility.set_location('Entering....'||l_proc,10);
261 /*Create the workflow process.*/
262 wf_engine.CreateProcess (ItemType => l_Item_Type
263 ,ItemKey => p_Item_Key
264 ,process => l_process
265 ,User_Key => l_user_key
266 ,Owner_Role => 'COREPAY'
267 );
268 /*Get the person name whose values are getting changed.*/
269 open csr_person_name;
270 fetch csr_person_name into l_employee_name;
271 close csr_person_name;
272
273 /*Get the requestor details from AME.*/
274 wf_directory.GetRoleName(p_orig_system => 'PER'
275 ,p_orig_system_id => p_performer_id
276 ,p_name => l_login_name
277 ,p_display_name => l_requestor_name);
278
279 --
280 /*Set Workflow attributes.*/
281 wf_engine.setItemAttrNumber(l_item_type, p_item_key, 'CURRENT_PERSON_ID', P_PERSON_ID);
282 wf_engine.setItemAttrNumber(l_item_type, p_item_key, 'CURRENT_ASSIGNMENT_ID',P_ASSIGNMENT_ID);
283 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PROCESS_NAME','PYASGWF_PROCESS');
284 wf_engine.setItemAttrDate(l_item_type, p_item_key,'CURRENT_EFFECTIVE_DATE',SYSDATE);
285 wf_engine.setItemAttrNumber(l_item_type, p_item_key,'PERFORMER_PERSON_ID',P_PERFORMER_ID);
286 wf_engine.setItemAttrDate(l_item_type, p_item_key,'EFFECTIVE_DATE',P_EFFECTIVE_DATE);
287 wf_engine.setItemAttrText(l_item_type, p_item_key, 'EMP_NAME', l_employee_name);
288 wf_engine.setItemAttrText(l_item_type, p_item_key, 'REQUESTOR_LOGIN_NAME', P_PERFORMER_LOGIN_NAME);
289 wf_engine.setItemAttrText(l_item_type, p_item_key, 'REQUESTOR_NAME', l_requestor_name);
290
291 /*As the control comes here only for the first time. At this time only requestor would be there.
292 So for the time being we are setting the previous approver details as the requestor.*/
293 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PREVIOUS_APPROVER_LOGIN_NAME', P_PERFORMER_LOGIN_NAME);
294 wf_engine.setItemAttrText(l_item_type, p_item_key, 'PREVIOUS_APPROVER_NAME', l_requestor_name);
295
296 /*Get the AME/workflow admin details and populate the attributes.*/
297 ame_api2.getAdminApprover(applicationIdIn => 801,
298 transactionTypeIn => 'PAY_ASSIGNMENT_COSTING',
299 adminApproverOut => l_wf_admin);
300 wf_engine.setItemAttrText(l_item_type, p_item_key,'WF_ADMIN_LOGIN_NAME',l_wf_admin.name);
301 wf_directory.GetRoleName(p_orig_system => NVL(l_wf_admin.orig_system,'PER')
302 ,p_orig_system_id => l_wf_admin.orig_system_id
303 ,p_name => l_admin_name
304 ,p_display_name => l_admin_display_name);
305 wf_engine.setItemAttrText(l_item_type, p_item_key,'WF_ADMIN_NAME',l_admin_display_name);
306
307 /*Start the created workflow process.*/
308 wf_engine.StartProcess (itemtype => l_Item_Type
309 ,itemkey => p_Item_Key
310 );
311 END START_WF_PROCESS;
312 -- ------------------------------------------------------------------------
313 -- |--------------------------< APPROVE_PROCESS >-------------------------|
314 -- ------------------------------------------------------------------------
315 PROCEDURE APPROVE_PROCESS(
316 itemtype in varchar2
317 ,itemkey in varchar2
318 ,actid in number
319 ,funmode in varchar2
320 ,result out nocopy varchar2) is
321 l_transaction_id HR_API_TRANSACTIONS.TRANSACTION_ID%TYPE;
322 l_transaction_step_id HR_UTIL_WEB.G_VARCHAR2_TAB_TYPE;
323 l_api_name HR_UTIL_WEB.G_VARCHAR2_TAB_TYPE;
324 l_row number;
325 l_proc varchar2(255) := g_package||'APPROVE_PROCESS';
326 l_api_to_call varchar2(1000);
327 l_actid WF_ITEM_ACTIVITY_STATUSES.process_activity%TYPE;
328 l_effective_date DATE;
329 begin
330
331 /*Set the previous approver attributes.*/
332 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_LOGIN_NAME',
333 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_LOGIN_NAME'));
334 wf_engine.setItemAttrText(itemtype, itemkey,'PREVIOUS_APPROVER_NAME',
335 wf_engine.getItemAttrText(itemtype, itemkey, 'APPROVER_NAME'));
336
337 /* Get the transaction step id pertaining to the work flow process
338 identified by the itemkey, itemkey and actid.*/
339 hr_transaction_api.get_transaction_step_info(p_item_type => itemtype
340 ,p_item_key => itemkey
341 ,p_activity_id => 0 --l_actid
342 ,p_transaction_step_id => l_transaction_step_id
343 ,p_api_name => l_api_name
344 ,p_rows => l_row);
345 /*l_row represents number of steps pertaining to itemtype, itemkey and actid.
346 But in our case it's always one. So we assume it as one always.*/
347 /*Call the API to save the data into base tables.*/
348 for i in 0..l_row-1 loop
349 /*Construct the API call*/
350 l_api_to_call := 'Begin ';
351 l_api_to_call := l_api_to_call || l_api_name(i)||'(';
352 l_api_to_call := l_api_to_call || 'p_transaction_step_id => '||to_number(l_transaction_step_id(i));
353 l_api_to_call := l_api_to_call || ' ); end;';
354
355 /*Call the API*/
356 EXECUTE IMMEDIATE (l_api_to_call);
357
358 end loop;
359 commit;
360 end APPROVE_PROCESS;
361 -- ------------------------------------------------------------------------
362 -- |--------------------------< REJECT_PROCESS >--------------------------|
363 -- ------------------------------------------------------------------------
364 PROCEDURE REJECT_PROCESS(
365 itemtype in varchar2
366 ,itemkey in varchar2
367 ,actid in number
368 ,funmode in varchar2
369 ,result out nocopy varchar2) IS
370 l_proc varchar2(240) := g_package||'REJECT_PROCESS';
371 l_result varchar2(20);
372 BEGIN
373 if g_debug then
374 hr_utility.set_location('Entering ..'||l_proc,10);
375 end if;
376
377 hr_transaction_ss.rollback_transaction(
378 itemtype => itemtype
379 ,itemkey => itemkey
380 ,actid => actid
381 ,funmode => funmode
382 ,result => l_result);
383 if (l_result = 'SUCCESS') then
384 hr_utility.set_location('Transaction deleted successfully ',20);
385 else
386 hr_utility.set_location('Error in deleting Transaction ',30);
387 end if;
388 if g_debug then
389 hr_utility.set_location('Leaving ..'||l_proc,1000);
390 end if;
391 EXCEPTION
392 WHEN OTHERS THEN
393 hr_utility.set_location('An EXCEPTION occured',40);
394 hr_utility.set_location('Leaving ..'||l_proc,1000);
395 RAISE;
396 END REJECT_PROCESS;
397
398 end PAY_ASSG_COST_WF;