[Home] [Help]
PACKAGE BODY: APPS.PA_AUTOALLOC_WF_PKG
Source
1 PACKAGE BODY PA_AUTOALLOC_WF_PKG AS
2 /* $Header: PAXWFALB.pls 120.4 2006/05/31 23:01:06 skannoji noship $ */
3 ----------------------------------------------------------------------------
4 -- This procedure is called from GL workflow activity to launch PA workflow
5
6 PROCEDURE Launch_PA_WF ( p_item_type IN VARCHAR2,
7 p_item_key IN VARCHAR2,
8 p_actid IN NUMBER,
9 p_funcmode IN VARCHAR2,
10 p_result OUT NOCOPY VARCHAR2)
11 IS
12 v_err_stack varchar2(2000);
13 v_debug_file_dir varchar2(255);
14 v_user_id Number;
15 v_org_id Number;
16 v_resp_id Number;
17 v_resp_appl_id Number;
18 v_lang varchar2(30);
19 v_debug_mode varchar2(1);
20 Begin
21 If ( p_funcmode = 'RUN' ) THEN
22 v_Err_Stack := 'PA_AUTOALLOC_WF_PKG.Launch_PA_WF';
23 G_Err_Stage := 'Starting Launch_PA_WF';
24 /* dbms_output.put_line(G_Err_Stage); */
25
26 -- generate PA workflow item key
27 select pa_workflow_itemkey_s.nextval
28 into PA_AUTOALLOC_WF_PKG.PA_item_key
29 from dual;
30
31 /* dbms_output.put_line('Item Key is '||PA_item_key); */
32 wf_engine.CreateProcess( itemtype => PA_item_type,
33 itemkey => PA_item_key,
34 process => 'PA_AUTO_ALLOC_PROCESS');
35 /* dbms_output.put_line('Created Process'); */
36 Init_PA_WF_Stack (PA_Item_Type,PA_Item_Key,v_Err_Stack);
37
38 /*Bug:920470. set user_id, resp_id etc. before calling fnd_profile.get()*/
39 v_user_id := WF_ENGINE.GetItemAttrNumber
40 (PA_item_type,
41 PA_item_key,
42 'USER_ID');
43
44 v_org_id := WF_ENGINE.GetItemAttrNumber
45 (PA_item_type,
46 PA_item_key,
47 'ORG_ID');
48
49 v_resp_id := WF_ENGINE.GetItemAttrNumber
50 (PA_item_type,
51 PA_item_key,
52 'RESP_ID');
53
54 v_resp_appl_id := WF_ENGINE.GetItemAttrNumber
55 (PA_item_type,
56 PA_item_key,
57 'RESP_APPL_ID');
58
59 v_lang := WF_ENGINE.GetItemAttrText
60 (PA_item_type,
61 PA_item_key,
62 'LANG');
63
64 FND_PROFILE.put('LANG', v_lang);
65
66 -- Fix for bug : 4640479
67 -- FND_PROFILE.put('ORG_ID', v_org_id);
68 MO_GLOBAL.set_policy_context('S',v_org_id);
69 FND_PROFILE.put('USER_ID', v_user_id );
70 FND_PROFILE.put('RESP_ID', v_resp_id);
71 FND_PROFILE.put('RESP_APPL_ID', v_resp_appl_id);
72 /*end bug920470*/
73
74 /*Bug 931037.set workflow attribute DEBUG_MODE,then get the attribute value from
75 function DebugFlag */
76 FND_PROFILE.GET('PA_DEBUG_MODE',v_debug_mode);
77 Wf_Engine.SetItemAttrText( itemtype => PA_item_type,
78 itemkey => PA_item_key,
79 aname => 'DEBUG_MODE',
80 avalue => v_debug_mode);
81 /*End bug 931037*/
82
83 /* Set attribute DEBUG_FILE_DIR and initialize debug only if
84 Debug Flag is on */
85 if DebugFlag then
86 v_debug_file_dir := GetDebugLogDir; -- Added for bug 5218394
87 -- Commented out for bug 5218394 v_debug_file_dir := NVL(FND_PROFILE.VALUE('PA_DEBUG_LOG_DIRECTORY'),'/sqlcom/log');
88
89 wf_engine.SetItemAttrText( itemtype => PA_item_type,
90 itemkey => PA_item_key,
91 aname => 'DEBUG_FILE_DIR',
92 avalue => v_debug_file_dir);
93 /* dbms_output.put_line('Debug File Dir is '||v_debug_file_dir); */
94
95 /** Initialize PA debug File **/
96 G_Err_Stage := 'Call Initialize Debug';
97 /* dbms_output.put_line(G_Err_Stage); */
98 initialize_debug;
99 G_Err_Stage := 'After Initialize Debug';
100 /* dbms_output.put_line(G_Err_Stage); */
101 end if;
102
103 /* Write to GL debug file */
104 If(GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg_flag) then
105 GL_AUTO_ALLOC_WF_PKG.initialize_debug;
106
107 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('*****************************************');
108 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('Launching PA Autoallocation Workflow');
109 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('PA Workflow Launch Date and Time: '||to_char(sysdate,'DD-MON-YYYY,HH24:MI:SS'));
110 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('Generated PA item Key is: '||PA_item_Key);
111 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('PA debug directory is: '||v_debug_file_dir);
112 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('PA debug file is: '||G_FILE);
113 GL_AUTO_ALLOC_WF_PKG.diagn_debug_msg('*****************************************');
114 End If;
115
116 /** Set GL itemtype and itemkey as PA attributes **/
117 Wf_Engine.SetItemAttrText( itemtype => PA_item_type,
118 itemkey => PA_item_key,
119 aname => 'GL_ITEM_TYPE',
120 avalue => p_item_type);
121
122 Wf_Engine.SetItemAttrText( itemtype => PA_item_type,
123 itemkey => PA_item_key,
124 aname => 'GL_ITEM_KEY',
125 avalue => p_item_key);
126
127 Wf_Engine.SetItemAttrText( itemtype => PA_item_type,
128 itemkey => PA_item_key,
129 aname => 'WF_LAUNCHED_FROM',
130 avalue => 'GL');
131
132 WriteDebugMsg('*****************************************');
133 WriteDebugMsg('Launching PA Auto Allocation Workflow');
134 WriteDebugMsg('*****************************************');
135 WriteDebugMsg('PA Workflow Launch Date and Time: '||
136 to_char(sysdate,'DD-MON-YYYY,HH24:MI:SS'));
137 WriteDebugMsg(v_Err_Stack);
138 WriteDebugMsg('Generated PA Item Key = ' ||PA_item_key);
139 WriteDebugMsg('Attribute DEBUG_FILE_DIR = '||v_debug_file_dir);
140 WriteDebugMsg('Attribute GL_ITEM_TYPE = '||p_item_type);
141 WriteDebugMsg('Attribute GL_ITEM_KEY = '||p_item_key);
142
143 G_Err_Stage:= 'Creating Process PA_AUTO_ALLOC_PROCESS';
144 WriteDebugMsg(G_ERR_STAGE);
145 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
146
147 G_Err_Stage:= 'Initialize PA WorkFlow Item Attributes ';
148 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
149 WriteDebugMsg(G_ERR_STAGE);
150
151 --initialize pl/sql and pa wf item attributes
152 initialize_pa_wf;
153
154 G_Err_Stage := 'Process PA_AUTO_ALLOC_PROCESS starting';
155 WriteDebugMsg(G_ERR_STAGE);
156 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
157
158 Wf_engine.StartProcess( itemtype => PA_item_type,
159 itemkey => PA_item_key );
160
161 wf_engine.SetItemAttrText( itemtype => p_item_type,
162 itemkey => p_item_key,
163 aname => 'PA_WF_STATUS',
164 avalue => NULL );
165 p_result := 'COMPLETE:PASS';
166
167 G_Err_Stage:= 'End of the API: Launch_PA_WF ';
168 Set_PA_WF_Stage(PA_Item_Type,PA_Item_key,G_Err_Stage);
169 WriteDebugMsg(G_ERR_STAGE);
170
171 ElsIf ( p_funcmode = 'CANCEL' ) THEN
172 NULL;
173 End If;
174
175 EXCEPTION
176 WHEN NO_DATA_FOUND then
177 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
178 'Launch_PA_WF', PA_item_type, PA_item_key,
179 'Item Type could not be set: sequence does not exist');
180 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
181 WriteDebugMsg('*************** Error Encountered **************');
182 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
183 wf_engine.SetItemAttrText( itemtype => p_item_type,
184 itemkey => p_item_key,
185 aname => 'PA_WF_STATUS',
186 avalue => 'FAIL' );
187 p_result := 'COMPLETE:FAIL';
188
189 Raise;
190 WHEN OTHERS THEN
191 Wf_Core.Context( 'PA_AUTOALLOC_WF_PKG','Launch_PA_WF', G_Err_Stage,
192 PA_item_type, PA_item_key);
193 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
194 WriteDebugMsg('*************** Error Encountered **************');
195 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
196 wf_engine.SetItemAttrText( itemtype => p_item_type,
197 itemkey => p_item_key,
198 aname => 'PA_WF_STATUS',
199 avalue => 'FAIL' );
200 p_result := 'COMPLETE:FAIL';
201 Raise;
202
203 End Launch_PA_WF;
204 --------------------------------------------------------------------------------------
205 PROCEDURE initialize_pa_wf
206 IS
207
208 -- Local variables
209 v_err_stack Varchar2(2000);
210 v_gl_item_type Varchar2(10);
211 v_gl_item_key Varchar2(40);
212 v_ALLOCATION_SET_NAME Varchar2(40);
213 v_SET_REQ_ID Number;
214 v_OWNER Varchar2(100);
215 v_SET_OF_BOOKS_ID Number;
216 v_GL_PERIOD_NAME Varchar2(15);
217 v_PA_PERIOD_NAME Varchar2(15);
218 v_EXPENDITURE_ITEM_DATE Date;
219 v_LAST_UPDATE_LOGIN Number;
220 v_CREATED_BY Number;
221 v_LAST_UPDATED_BY Number;
222 v_FUNC_CURR Varchar2(15);
223 v_monitor_url VARCHAR2(500);
224 v_rollback_allowed VARCHAR2(1);
225 v_resp_id NUMBER;
226 v_user_id NUMBER;
227 v_org_id NUMBER;
228 v_resp_appl_id NUMBER;
229 v_operating_mode Varchar2(2);
230 v_batch_id Number;
231 /* Changing the length of v_batch_name from varchar2(40) to varchar2(60)
232 for bug# 1721283 since PA_ALLOC_RULES_ALL.RULE_NAME
233 is now length 60 */
234 v_batch_name Varchar2(60);
235 v_batch_type_code Varchar2(1);
236 v_allocation_method_code Varchar2(1);
237 v_step_number Number;
238 v_lang fnd_profile_option_values.profile_option_value%TYPE;
239 v_value fnd_profile_option_values.profile_option_value%TYPE;
240 v_launched_from VARCHAR2(10);
241
242 Begin
243
244 G_Err_Stage := 'Entering initialize_pa_wf';
245 /* dbms_output.put_line (G_Err_Stage); */
246 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Initialize_PA_WF');
247 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
248 PA_item_key,
249 'WF_STACK');
250 WriteDebugMsg(v_err_stack);
251 G_Err_Stage := 'Get GL Item Attributes';
252 WriteDebugMsg(G_Err_Stage);
253 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
254
255 -- Get all the GL item attributes
256 v_gl_item_type := wf_engine.GetItemAttrText(itemtype =>PA_item_type,
257 itemkey => PA_item_key,
258 aname => 'GL_ITEM_TYPE');
259
260 v_gl_item_key := wf_engine.GetItemAttrText(itemtype =>PA_item_type,
261 itemkey =>PA_item_key,
262 aname => 'GL_ITEM_KEY');
263
264 v_ALLOCATION_SET_NAME := wf_engine.GetItemAttrText
265 ( itemtype => v_gl_item_type,
266 itemkey => v_gl_item_key,
267 aname => 'SET_NAME');
268
269 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
270 itemkey => v_gl_item_key,
271 aname => 'SET_REQ_ID');
272
273 v_USER_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
274 itemkey => v_gl_item_key,
275 aname => 'USER_ID');
276
277 v_ORG_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
278 itemkey => v_gl_item_key,
279 aname => 'ORG_ID');
280
281 v_RESP_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
282 itemkey => v_gl_item_key,
283 aname => 'RESP_ID');
284
285 v_RESP_APPL_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
286 itemkey => v_gl_item_key,
287 aname => 'RESP_APPL_ID');
288
289 v_LANG := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
290 itemkey => v_gl_item_key,
291 aname => 'LANG');
292
293 v_ROLLBACK_ALLOWED := WF_ENGINE.GetItemAttrText
294 (itemtype => v_gl_item_type,
295 itemkey => v_gl_item_key,
296 aname => 'ROLLBACK_ALLOWED');
297
298 v_OWNER := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
299 itemkey => v_gl_item_key,
300 aname => 'STEP_CONTACT');
301
302 v_EXPENDITURE_ITEM_DATE := WF_ENGINE.GetItemAttrDate
303 (itemtype => v_gl_item_type,
304 itemkey => v_gl_item_key,
305 aname => 'EXPENDITURE_ITEM_DATE');
306
307 v_GL_PERIOD_NAME := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
308 itemkey => v_gl_item_key,
309 aname => 'GL_PERIOD_NAME');
310
311 v_PA_PERIOD_NAME := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
312 itemkey => v_gl_item_key,
313 aname => 'PA_PERIOD_NAME');
314
315 v_CREATED_BY := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
316 itemkey => v_gl_item_key,
317 aname => 'CREATED_BY');
318
319 v_LAST_UPDATED_BY := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
320 itemkey => v_gl_item_key,
321 aname => 'LAST_UPDATED_BY');
322
323 v_LAST_UPDATE_LOGIN := WF_ENGINE.GetItemAttrNumber
324 (itemtype => v_gl_item_type,
325 itemkey => v_gl_item_key,
326 aname => 'LAST_UPDATE_LOGIN');
327
328 v_OPERATING_MODE := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
329 itemkey => v_gl_item_key,
330 aname => 'OPERATING_MODE');
331
332 v_STEP_NUMBER := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
333 itemkey => v_gl_item_key,
334 aname => 'STEP_NUMBER');
335
336 v_BATCH_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
337 itemkey => v_gl_item_key,
338 aname => 'BATCH_ID');
339
340 v_BATCH_NAME := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
341 itemkey => v_gl_item_key,
342 aname => 'BATCH_NAME');
343
344 v_BATCH_TYPE_CODE := WF_ENGINE.GetItemAttrText(itemtype => v_gl_item_type,
345 itemkey => v_gl_item_key,
346 aname => 'BATCH_TYPE_CODE');
347
348 v_ALLOCATION_METHOD_CODE := WF_ENGINE.GetItemAttrText
349 (itemtype => v_gl_item_type,
350 itemkey => v_gl_item_key,
351 aname => 'ALLOCATION_METHOD_CODE');
352
353 v_SET_OF_BOOKS_ID := WF_ENGINE.GetItemAttrNumber(itemtype => v_gl_item_type,
354 itemkey => v_gl_item_key,
355 aname => 'SET_OF_BOOKS_ID');
356
357 -- Set PA item attributes based on GL item attributes
358 G_Err_Stage:= 'Set PA Item Attributes';
359 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
360 WriteDebugMsg(G_Err_Stage);
361
362 -- Set item user key
363 wf_engine.SetItemUserKey( itemtype => PA_item_type,
364 itemkey => PA_item_key,
365 userkey => v_allocation_set_name );
366
367 wf_engine.SetItemAttrText(itemtype => PA_item_type,
368 itemkey => PA_item_key,
369 aname => 'SET_NAME',
370 avalue => v_allocation_set_name );
371 WriteDebugMsg('Attribute Allocation Set_Name = '||v_allocation_set_name);
372
373 begin
374 v_monitor_url :=
375 wf_monitor.GetDiagramUrl(wf_core.translate('WF_WEB_AGENT'),
376 PA_item_type, PA_item_key,'YES');
377 exception
378 when others then
379 v_monitor_url := 'Invalid URL';
380 end;
381
382 wf_engine.SetItemAttrText( itemtype => PA_item_type,
383 itemkey => PA_item_key,
384 aname => 'MONITOR_URL',
385 avalue => v_monitor_url);
386
387 WriteDebugMsg('Attribute MONITOR_URL = '||v_monitor_url);
388
389 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
390 itemkey => PA_item_key,
391 aname => 'SET_REQ_ID',
392 avalue => v_set_req_id );
393 WriteDebugMsg('Attribute Set_Req_ID is = '||to_char(v_set_req_id));
394
395 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
396 itemkey => PA_item_key,
397 aname => 'PA_ITEM_KEY',
398 avalue => PA_item_key );
399 WriteDebugMsg('Attribute PA_Item_Key is = '||PA_ITEM_KEY);
400
401 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
402 itemkey => PA_item_key,
403 aname => 'USER_ID',
404 avalue => v_user_id );
405 WriteDebugMsg('Attribute User_ID is = '||to_char(v_user_id));
406
407 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
408 itemkey => PA_item_key,
409 aname => 'ORG_ID',
410 avalue => v_org_id );
411 WriteDebugMsg('Attribute Org_ID is = '||to_char(v_org_id));
412
413 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
414 itemkey => PA_item_key,
415 aname => 'RESP_ID',
416 avalue => v_resp_id );
417 WriteDebugMsg('Attribute Resp_ID is = '||to_char(v_resp_id));
418
419 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
420 itemkey => PA_item_key,
421 aname => 'RESP_APPL_ID',
422 avalue => v_resp_appl_id );
423 WriteDebugMsg('Attribute Resp_Appl_ID is = '||to_char(v_resp_appl_id));
424
425 wf_engine.SetItemAttrText( itemtype => PA_item_type,
426 itemkey => PA_item_key,
427 aname => 'LANG',
428 avalue => v_lang );
429
430 WriteDebugMsg('Lang =' ||v_lang);
431
432 wf_engine.SetItemAttrText( itemtype => PA_item_type,
433 itemkey => PA_item_key,
434 aname => 'ROLLBACK_ALLOWED',
435 avalue => v_rollback_allowed);
436 WriteDebugMsg('Attribute Roolback_Allowed = ' ||v_rollback_allowed);
437
438 wf_engine.SetItemAttrText( itemtype => PA_item_type,
439 itemkey => PA_item_key,
440 aname => 'MONITOR_URL',
441 avalue => v_monitor_url );
442 WriteDebugMsg('Attribute Monitor_URL = ' ||v_monitor_url);
443
444 wf_engine.SetItemAttrText( itemtype => PA_item_type,
445 itemkey => PA_item_key,
446 aname => 'STEP_CONTACT',
447 avalue => v_OWNER);
448 WriteDebugMsg('Attribute Step_Contact = ' ||v_owner);
449
450 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
451 itemkey => PA_item_key,
452 aname => 'SET_OF_BOOKS_ID',
453 avalue => v_SET_OF_BOOKS_ID);
454 WriteDebugMsg('Attribute SET_OF_BOOKS_ID = ' ||to_char(v_SET_OF_BOOKS_ID));
455
456 wf_engine.SetItemAttrDate( itemtype => PA_item_type,
457 itemkey => PA_item_key,
458 aname => 'EXPENDITURE_ITEM_DATE',
459 avalue => v_EXPENDITURE_ITEM_DATE);
460 WriteDebugMsg('Attribute EXPENDITURE_ITEM_DATE = ' ||
461 to_char(v_EXPENDITURE_ITEM_DATE));
462
463 wf_engine.SetItemAttrText( itemtype => PA_item_type,
464 itemkey => PA_item_key,
465 aname => 'GL_PERIOD_NAME',
466 avalue => v_GL_PERIOD_NAME);
467 WriteDebugMsg('Attribute GL_PERIOD_NAME = ' ||v_GL_PERIOD_NAME);
468
469 wf_engine.SetItemAttrText( itemtype => PA_item_type,
470 itemkey => PA_item_key,
471 aname => 'PA_PERIOD_NAME',
472 avalue => v_PA_PERIOD_NAME);
473 WriteDebugMsg('Attribute PA_PERIOD_NAME = ' ||v_PA_PERIOD_NAME);
474
475 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
476 itemkey => PA_item_key,
477 aname => 'CREATED_BY',
478 avalue => v_CREATED_BY);
479 WriteDebugMsg('Attribute CREATED_BY = ' ||to_char(v_CREATED_BY));
480
481 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
482 itemkey => PA_item_key,
483 aname => 'LAST_UPDATED_BY',
484 avalue => v_LAST_UPDATED_BY);
485 WriteDebugMsg('Attribute LAST_UPDATED_BY = ' ||to_char(v_LAST_UPDATED_BY));
486
487 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
488 itemkey => PA_item_key,
489 aname => 'LAST_UPDATE_LOGIN',
490 avalue => v_LAST_UPDATE_LOGIN);
491 WriteDebugMsg('Attribute LAST_UPDATE_LOGIN = '
492 ||to_char(v_LAST_UPDATE_LOGIN));
493 wf_engine.SetItemAttrText( itemtype => PA_item_type,
494 itemkey => PA_item_key,
495 aname => 'OPERATING_MODE',
496 avalue => v_operating_mode);
497 WriteDebugMsg('Attribute Operating Mode = ' ||v_operating_mode);
498
499 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
500 itemkey => PA_item_key,
501 aname => 'STEP_NUMBER',
502 avalue => v_step_number);
503 WriteDebugMsg('Attribute Step Number = ' ||to_char(v_step_number));
504
505 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
506 itemkey => PA_item_key,
507 aname => 'BATCH_ID',
508 avalue => v_batch_id);
509 WriteDebugMsg('Attribute Batch ID = ' ||to_char(v_batch_id));
510
511 wf_engine.SetItemAttrText( itemtype => PA_item_type,
512 itemkey => PA_item_key,
513 aname => 'BATCH_NAME',
514 avalue => v_batch_name);
515 WriteDebugMsg('Attribute Batch Name = ' ||v_batch_name);
516
517 wf_engine.SetItemAttrText( itemtype => PA_item_type,
518 itemkey => PA_item_key,
519 aname => 'BATCH_TYPE_CODE',
520 avalue => v_batch_type_code);
521 WriteDebugMsg('Attribute Batch Type Code = ' ||v_batch_type_code);
522
523 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
524 itemkey => PA_item_key,
525 aname => 'SET_OF_BOOKS_ID',
526 avalue => v_set_of_books_id);
527 WriteDebugMsg('Attribute Set Of Books ID = ' ||to_char(v_set_of_books_id));
528
529 WF_Engine.SetItemAttrText(itemtype => PA_item_type,
530 itemkey => PA_item_key,
531 aname => 'DEFINITION_FORM',
532 avalue => NULL);
533
534 G_Err_Stage := 'Set PA specific Item Attributes';
535 WriteDebugMsg(G_Err_Stage);
536 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
537
538 -- Set PA specific item attributes
539 wf_engine.SetItemAttrText( itemtype => PA_item_type,
540 itemkey => PA_item_key,
541 aname => 'PARENT_PROCESS',
542 avalue => 'GL');
543 WriteDebugMsg('Attribute PARENT_PROCESS = GL');
544
545 wf_engine.SetItemAttrNumber( itemtype => PA_item_type,
546 itemkey => PA_item_key,
547 aname => 'REVERSED_STEP_NUMBER',
548 avalue => 0);
549 WriteDebugMsg('Attribute Reversed_Step_Number = 0 ');
550
551 wf_engine.SetItemAttrText( itemtype => PA_item_type,
552 itemkey => PA_item_key,
553 aname => 'EXPENDITURE_GROUP',
554 avalue => 'NONE');
555 WriteDebugMsg('Attribute EXPENDITURE_GROUP = NONE ');
556
557 v_launched_from := WF_ENGINE.GetItemAttrText(itemtype =>PA_item_type,
558 itemkey => PA_item_key,
559 aname => 'WF_LAUNCHED_FROM');
560 IF v_launched_from = 'GL' THEN
561 wf_engine.SetItemAttrText( itemtype => PA_item_type,
562 itemkey => PA_item_key,
563 aname => 'GL_BLOCK_ACTIVITY',
564 avalue => 'PA_CHECK_WF');
565 WriteDebugMsg('Attribute GL Block Activity = '||'PA_CHECK_WF');
566 END IF;
567
568 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
569 EXCEPTION
570 WHEN OTHERS THEN
571 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
572 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
573 'initialize_pa_wf', PA_item_type, PA_item_key);
574 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
575 WriteDebugMsg ('************ Error Encountered **************');
576 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
577 Raise;
578 End initialize_pa_wf;
579 --------------------------------------------------------------------------------------
580 /** This function submits Concurrent Process to generate Project Allocation Transactions.This is called from PA Step down Allocation Work Flow **/
581
582 PROCEDURE Submit_Alloc_Process( p_item_type IN VARCHAR2,
583 p_item_key IN VARCHAR2,
584 p_actid IN NUMBER,
585 p_funcmode IN VARCHAR2,
586 p_result OUT NOCOPY VARCHAR2) Is
587
588 v_request_id NUMBER;
589 v_parent_process VARCHAR2(30);
590 v_allocation_run_id NUMBER;
591 v_rule_id NUMBER;
592 v_pa_period_name VARCHAR2(20);
593 v_gl_period_name VARCHAR2(20);
594 v_expnd_item_date DATE;
595 v_step_number NUMBER;
596 l_allocation_method_code Varchar2(1);
597 v_set_req_id Number;
598 v_err_stack Varchar2(2000);
599 vc_expnd_item_date Varchar2(20);
600
601 Begin
602 If ( p_funcmode = 'RUN' ) THEN
603
604 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
605
606 Set_PA_WF_Stack(p_item_type,p_item_key,'Submit_Alloc_Process');
607 v_err_stack := WF_ENGINE.GetItemAttrText( p_item_type,
608 p_item_key,
609 'WF_STACK');
610 WriteDebugMsg(v_err_stack);
611 WriteDebugMsg('Activity ID is'||to_char(p_actid));
612
613
614 v_parent_process := WF_ENGINE.GetItemAttrText ( p_item_type
615 ,p_item_key
616 ,'PARENT_PROCESS');
617 WriteDebugMsg('Attribute Parent Process = '||v_parent_process);
618
619 /* Set attributes Concurrent Program Code and Name */
620
621 WF_ENGINE.SetItemAttrText( p_item_type,
622 p_item_key,
623 'CONC_PRG_CODE',
624 'PAXALGAT');
625 WriteDebugMsg('Attribute CONC_PRG_CODE = '||'PAXALGAT');
626
627 /* Get Parameters needed to submit Allocation Generation Process */
628
629 v_rule_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
630 p_item_key,
631 'BATCH_ID');
632 WriteDebugMsg('Attribute Batch ID = '||to_char(v_rule_id));
633
634 v_expnd_item_date := WF_ENGINE.GetItemAttrDate(p_item_type,
635 p_item_key,
636 'EXPENDITURE_ITEM_DATE');
637 WriteDebugMsg('Attribute EXPENDITURE_ITEM_DATE = '
638 ||to_char(v_expnd_item_date));
639
640 v_pa_period_name := WF_ENGINE.GetItemAttrText( p_item_type,
641 p_item_key,
642 'PA_PERIOD_NAME');
643 WriteDebugMsg('Attribute PA_PERIOD_NAME = '||v_pa_period_name);
644
645 v_gl_period_name := WF_ENGINE.GetItemAttrText( p_item_type,
646 p_item_key,
647 'GL_PERIOD_NAME');
648 WriteDebugMsg('Attribute GL_PERIOD_NAME = '||v_gl_period_name);
649
650 G_Err_Stage := 'Submit Generate Allocation Transactions Concurrent Process';
651 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
652 WriteDebugMsg(G_Err_Stage);
653
654 IF v_parent_process in ( 'GL','Concurrent Process Error') THEN
655
656 /** First time submission and restart after concurrent process
657 errors out as no draft is generated **/
658
659 vc_expnd_item_date :=fnd_date.date_to_canonical(v_expnd_item_date);
660 WriteDebugMsg('Expenditure Item Date in char format:='||
661 vc_expnd_item_date);
662
663 v_request_id := Submit_Conc_Process('PAXALGAT'
664 ,p_arg1 => v_rule_id
665 ,p_arg2 => vc_expnd_item_date
666 ,p_arg3 => v_pa_period_name
667 ,p_arg4 => v_gl_period_name
668 );
669 ELSIF v_parent_process = 'Concurrent Process Exception' THEN
670
671 /** Generate Allocation process has generated draft having status
672 'DF' or 'IP' so Delete Draft before restart**/
673
674 v_allocation_run_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
675 p_item_key,
676 'ALLOCATION_RUN_ID');
677 G_Err_Stage:='Delete Draft before submitting Allocation Concurrent Process';
678 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
679 WriteDebugMsg(G_Err_Stage);
680
681 PA_ALLOC_RUN.Delete_ALLOC_TXNS (v_rule_id,
682 v_allocation_run_id);
683
684 vc_expnd_item_date :=fnd_date.date_to_canonical(v_expnd_item_date);
685 WriteDebugMsg('Expenditure Item Date in char format:='
686 ||vc_expnd_item_date);
687
688 v_request_id := Submit_Conc_Process('PAXALGAT'
689 ,p_arg1 => v_rule_id
690 ,p_arg2 => vc_expnd_item_date
691 ,p_arg3 => v_pa_period_name
692 ,p_arg4 => v_gl_period_name
693 );
694 END IF;
695
696 WF_ENGINE.SetItemAttrText( p_item_type,
697 p_item_key,
698 'PARENT_PROCESS',
699 'Generate Allocation');
700
701 G_Err_Stage:='End Of API: Submit_Alloc_Process';
702 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
703 WriteDebugMsg(G_Err_Stage);
704
705 Reset_PA_WF_Stack(p_item_type,p_item_key);
706 p_result := 'COMPLETE';
707 ElsIf ( p_funcmode = 'CANCEL' ) THEN
708 NULL;
709 End If;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 Reset_PA_WF_Stack(p_item_type,p_item_key);
714 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'SUBMIT_ALLOC_PROCESS',
715 p_item_type, p_item_key);
716 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
717 WriteDebugMsg('*********** Error Encountered ***********');
718 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
719 -- set status code to unexpected fatal error
720
721 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
722 itemkey => p_item_key,
723 aname => 'SET_REQ_ID');
724 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
725 ,v_step_number
726 ,'UFE'
727 );
728 Raise;
729
730 END Submit_Alloc_Process;
731 --------------------------------------------------------------------------------
732
733 /** This function submits Concurrent Process to Update Project Summary Amounts.This is called from PA Step down Allocation Work Flow **/
734
735 PROCEDURE Submit_Conc_Sum( p_item_type IN VARCHAR2,
736 p_item_key IN VARCHAR2,
737 p_actid IN NUMBER,
738 p_funcmode IN VARCHAR2,
739 p_result OUT NOCOPY VARCHAR2) Is
740
741 v_request_id NUMBER;
742 v_parent_process VARCHAR2(30);
743 v_allocation_run_id NUMBER;
744 v_rule_id NUMBER;
745 v_pa_period_name VARCHAR2(20);
746 v_gl_period_name VARCHAR2(20);
747 v_expenditure_item_date DATE;
748 v_step_number NUMBER;
749 l_allocation_method_code Varchar2(1);
750 v_batch_id Number;
751 v_set_req_id Number;
752 v_debug_mode Varchar2(30);
753 v_err_stack Varchar2(2000);
754 v_operating_mode Varchar2(2);
755
756 Begin
757 If ( p_funcmode = 'RUN' ) THEN
758 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
759
760 Set_PA_WF_Stack(p_item_type,p_item_key,'Submit_Conc_Sum');
761 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
762 PA_item_key,
763 'WF_STACK');
764 WriteDebugMsg(v_err_stack);
765
766 v_parent_process := WF_ENGINE.GetItemAttrText ( p_item_type
767 ,p_item_key
768 ,'PARENT_PROCESS');
769
770 v_set_req_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
771 p_item_key,
772 'SET_REQ_ID');
773
774 v_step_number := WF_ENGINE.GetItemAttrNumber( p_item_type,
775 p_item_key,
776 'STEP_NUMBER');
777
778 v_allocation_run_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
779 p_item_key,
780 'ALLOCATION_RUN_ID');
781
782 v_operating_mode := WF_ENGINE.GetItemAttrText(p_item_type,
783 p_item_key,
784 'OPERATING_MODE');
785
786 /* Set attributes Concurrent Program Code and Name */
787
788 WF_ENGINE.SetItemAttrText( p_item_type,
789 p_item_key,
790 'CONC_PRG_CODE',
791 'PAXACMPT');
792 WriteDebugMsg('Attribute CONC_PRG_CODE = PAXACMPT');
793
794 /* Get Parameters needed to submit the concurrent Process */
795 FND_PROFILE.GET('PA_DEBUG_MODE',v_debug_mode);
796 WriteDebugMsg('Debug Mode value = '||v_debug_mode);
797
798 /** Set default values to all required parameters **/
799 v_request_id := Submit_Conc_Process(p_prog_code => 'PAXACMPT'
800 ,p_arg4 => 'Y'
801 ,p_arg6 => 'Y'
802 ,p_arg7 => 'Y'
803 ,p_arg9 => 'Y'
804 ,p_arg10 => v_debug_mode
805 ,p_arg11 => 'AUTO_ALLOCATION'
806 ,p_arg12 => v_allocation_run_id
807 ,p_arg13 => 'Y'
808 ,p_arg15 => 'I'
809 );
810
811 G_Err_Stage := 'End of API: Submit_Conc_Sum';
812 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
813 WriteDebugMsg(G_Err_Stage);
814 Reset_PA_WF_Stack(p_item_type,p_item_key);
815
816 p_result := 'COMPLETE';
817
818 ElsIf ( p_funcmode = 'CANCEL' ) THEN
819 NULL;
820 End If;
821
822 EXCEPTION
823 WHEN OTHERS THEN
824 Reset_PA_WF_Stack(p_item_type,p_item_key);
825 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'SUBMIT_CONC_SUM',
826 PA_item_type, PA_item_key);
827 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
828 WriteDebugMsg('********** Error Encountered *********');
829 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
830 -- set status code to unexpected fatal error
831 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
832 itemkey => p_item_key,
833 aname => 'SET_REQ_ID');
834 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
835 ,v_step_number
836 ,'UFE'
837 );
838 Raise;
839
840 END Submit_Conc_Sum;
841
842 --------------------------------------------------------------------------------
843
844 /** This function calls an API for Allocation Run Reversal.This is part of Rollback Process. This is called from PA Step down Allocation Work Flow **/
845
846 PROCEDURE Submit_Conc_AllocRev( p_item_type IN VARCHAR2,
847 p_item_key IN VARCHAR2,
848 p_actid IN NUMBER,
849 p_funcmode IN VARCHAR2,
850 p_result OUT NOCOPY VARCHAR2) Is
851
852 v_request_id NUMBER;
853 v_parent_process VARCHAR2(30);
854 v_allocation_run_id NUMBER;
855 v_rule_id NUMBER;
856 v_pa_period_name VARCHAR2(20);
857 v_gl_period_name VARCHAR2(20);
858 v_expenditure_item_date DATE;
859 v_step_number NUMBER;
860 l_allocation_method_code Varchar2(1);
861 v_batch_id Number;
862 v_set_req_id Number;
863 v_rev_tgt_exp_group Varchar2(50);
864 v_rev_off_exp_group Varchar2(50);
865 v_target_exp_group Varchar2(50);
866 v_offset_exp_group Varchar2(50);
867 v_run_status Varchar2(2);
868 v_org_id Number;
869 v_operating_mode Varchar2(2);
870 v_status_code Varchar2(15);
871 v_message_name Varchar2(150);
872 v_retcode Number;
873 v_errbuf Varchar2(30);
874 v_prog_code Varchar2(30);
875 v_rollback_allowed Varchar2(2);
876
877 Begin
878 If ( p_funcmode = 'RUN' ) THEN
879
880 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
881 WriteDebugMsg('Started Submit_Conc_AllocRev');
882
883 v_parent_process := WF_ENGINE.GetItemAttrText ( p_item_type
884 ,p_item_key
885 ,'PARENT_PROCESS');
886
887 v_set_req_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
888 p_item_key,
889 'SET_REQ_ID');
890
891 v_step_number := WF_ENGINE.GetItemAttrNumber( p_item_type,
892 p_item_key,
893 'STEP_NUMBER');
894
895 v_operating_mode := WF_ENGINE.GetItemAttrText(p_item_type,
896 p_item_key,
897 'OPERATING_MODE');
898
899 v_allocation_run_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
900 p_item_key,
901 'ALLOCATION_RUN_ID');
902
903 v_rule_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
904 p_item_key,
905 'BATCH_ID');
906
907 /* Set attributes Program Code and Name */
908
909 WF_ENGINE.SetItemAttrText( p_item_type,
910 p_item_key,
911 'CONC_PRG_CODE',
912 'PAXALGAT');
913
914 select run_status,target_exp_group,offset_exp_group
915 into v_run_status,v_target_exp_group,v_offset_exp_group
916 from pa_alloc_runs_all
917 where run_id = v_allocation_run_id;
918
919 if v_run_status in ('DF','DS','RF','IP') then
920 PA_ALLOC_RUN.Delete_ALLOC_TXNS (v_rule_id,
921 v_allocation_run_id);
922 elsif v_run_status = 'RV' then
923 p_result := 'COMPLETE:PASS';
924 else
925 /* Set the reversed exp group values */
926 v_rev_tgt_exp_group := Substr(v_target_exp_group,1,30)||'!'||
927 substr(p_item_key,1,19);
928 v_rev_off_exp_group := Substr(v_offset_exp_group,1,30)||'!'||
929 substr(p_item_key,1,19);
930 G_Err_Stage:= 'Calling Reverse_alloc_txns';
931 WriteDebugMsg(G_Err_Stage);
932
933 PA_ALLOC_RUN.Reverse_alloc_txns( v_rule_id
934 ,v_allocation_run_id
935 ,v_rev_tgt_exp_group
936 ,v_rev_off_exp_group
937 ,v_retcode
938 ,v_errbuf
939 );
940 if v_retcode = -1 then
941 /** ??? what will be the v_prog_code **/
942 Get_Status_and_Message(v_Prog_Code
943 ,'EXCEPTION'
944 ,v_rollback_allowed
945 ,v_status_code
946 ,v_message_name);
947
948 -- Program Error
949 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
950 ,v_step_number
951 ,v_status_code
952 );
953
954 wf_engine.SetItemAttrText(itemtype => PA_item_type,
955 itemkey => PA_item_key,
956 aname => 'MESSAGE_NAME',
957 avalue => v_message_name);
958
959 WriteDebugMsg('Attribute Message Name = '||v_message_name);
960 p_result := 'COMPLETE:FAIL';
961 Else
962 p_result := 'COMPLETE:PASS';
963 End If;
964
965 end if;
966
967 ElsIf ( p_funcmode = 'CANCEL' ) THEN
968 NULL;
969 End If;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'SUBMIT_CONC_ALLOCREV', PA_item_type, PA_item_key);
974 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
975 WriteDebugMsg('************* Error Encountered ************');
976 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
977 -- set status code to unexpected fatal error
978 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
979 itemkey => p_item_key,
980 aname => 'SET_REQ_ID');
981 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
982 ,v_step_number
983 ,'UFE'
984 );
985 Raise;
986
987 END Submit_Conc_AllocRev;
988 --------------------------------------------------------------------------------
989
990 PROCEDURE Check_Exp_Groups( p_item_type IN VARCHAR2,
991 p_item_key IN VARCHAR2,
992 p_actid IN NUMBER,
993 p_funcmode IN VARCHAR2,
994 p_result OUT NOCOPY VARCHAR2)
995 IS
996
997 v_allocation_run_id NUMBER;
998 v_batch_id NUMBER;
999 v_org_id NUMBER;
1000 v_parent_process VARCHAR2(30);
1001 v_err_stack VARCHAR2(2000);
1002 v_target_exp_group VARCHAR2(50);
1003 v_offset_exp_group VARCHAR2(50);
1004 v_operating_mode VARCHAR2(2);
1005 v_expenditure_group Varchar2(50);
1006 v_step_number Number;
1007 v_set_req_id Number;
1008
1009 BEGIN
1010
1011 If ( p_funcmode = 'RUN' ) THEN
1012
1013 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
1014 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Check_Exp_Groups');
1015 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1016 PA_item_key,
1017 'WF_STACK');
1018 WriteDebugMsg(v_err_stack);
1019
1020 v_step_number := WF_ENGINE.GetItemAttrNumber
1021 (p_item_type,
1022 p_item_key,
1023 'STEP_NUMBER');
1024
1025 v_org_id := WF_ENGINE.GetItemAttrNumber
1026 (p_item_type,
1027 p_item_key,
1028 'ORG_ID');
1029
1030 v_batch_id := WF_ENGINE.GetItemAttrNumber
1031 (p_item_type,
1032 p_item_key,
1033 'BATCH_ID');
1034 v_allocation_run_id := WF_ENGINE.GetItemAttrNumber
1035 (p_item_type,
1036 p_item_key,
1037 'ALLOCATION_RUN_ID');
1038
1039 v_parent_process := WF_ENGINE.GetItemAttrText
1040 (p_item_type,
1041 p_item_key,
1042 'PARENT_PROCESS');
1043
1044 v_operating_mode := WF_ENGINE.GetItemAttrText
1045 (p_item_type,
1046 p_item_key,
1047 'OPERATING_MODE');
1048
1049 v_expenditure_group := WF_ENGINE.GetItemAttrText
1050 (p_item_type,
1051 p_item_key,
1052 'EXPENDITURE_GROUP');
1053 WriteDebugMsg('The attribute Expenditure Group is: '||v_expenditure_group);
1054
1055 /** Select target_exp_group and offset_exp_group in NORMAL mode and
1056 rev_target_exp_group and rev_offset_exp_group in ROLLBACK mode **/
1057
1058 select decode (v_operating_mode,'N',target_exp_group,
1059 rev_target_exp_group),
1060 decode (v_operating_mode, 'N',offset_exp_group,
1061 rev_offset_exp_group)
1062 into v_target_exp_group,v_offset_exp_group
1063 from pa_alloc_runs_all
1064 where run_id = v_allocation_run_id;
1065
1066 WriteDebugMsg('Target Expenditure Group : '||v_target_exp_group);
1067 WriteDebugMsg('Offset Expenditure Group : '||v_offset_exp_group);
1068
1069 /** Initial value of expenditure group is NONE. To check if all the
1070 expenditure group has been costed or not first time item attribute
1071 EXPENDITURE_GROUP value is set to target_exp_group. Once that is
1072 processed the attribute value is set to offset_exp_group **/
1073
1074 IF v_expenditure_group = 'NONE' THEN
1075 wf_engine.SetItemAttrText(
1076 itemtype => p_item_type,
1077 itemkey => p_item_key,
1078 aname => 'EXPENDITURE_GROUP',
1079 avalue => v_target_exp_group);
1080 WriteDebugMsg('Setting the attribute Expenditure_Group : '||v_target_exp_group);
1081 p_result := 'COMPLETE:Y';
1082 ELSIF (v_expenditure_group = v_target_exp_group) AND
1083 (v_expenditure_group <> v_offset_exp_group) THEN
1084 wf_engine.SetItemAttrText(
1085 itemtype => p_item_type,
1086 itemkey => p_item_key,
1087 aname => 'EXPENDITURE_GROUP',
1088 avalue => v_offset_exp_group);
1089 WriteDebugMsg('Setting the attribute Expenditure Group : '||v_offset_exp_group);
1090 p_result := 'COMPLETE:Y';
1091 ELSE
1092 p_result := 'COMPLETE:N';
1093 END IF;
1094 Reset_PA_WF_Stack(p_item_type,p_item_key);
1095
1096 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1097 NULL;
1098 End If;
1099
1100 EXCEPTION
1101
1102 WHEN NO_DATA_FOUND THEN
1103 Reset_PA_WF_Stack(p_item_type,p_item_key);
1104 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Exp_Groups', PA_item_type, PA_item_key);
1105 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
1106 WriteDebugMsg('**************** Error Encountered ***************');
1107 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
1108 -- set status code to unexpected fatal error
1109 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1110 itemkey => p_item_key,
1111 aname => 'SET_REQ_ID');
1112 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1113 ,v_step_number
1114 ,'UFE'
1115 );
1116 Raise;
1117
1118 WHEN OTHERS THEN
1119 Reset_PA_WF_Stack(p_item_type,p_item_key);
1120 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Exp_Groups', PA_item_type, PA_item_key);
1121 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
1122 WriteDebugMsg('**************** Error Encountered ***************');
1123 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
1124 -- set status code to unexpected fatal error
1125 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1126 itemkey => p_item_key,
1127 aname => 'SET_REQ_ID');
1128 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1129 ,v_step_number
1130 ,'UFE'
1131 );
1132 Raise;
1133
1134 END Check_Exp_Groups;
1135 -------------------------------------------------------------------------------
1136 /** This function submits Concurrent Process to Distribute Cost.This is called from PA Step down Allocation Work Flow **/
1137
1138 PROCEDURE Submit_Conc_Process_Dist( p_item_type IN VARCHAR2,
1139 p_item_key IN VARCHAR2,
1140 p_actid IN NUMBER,
1141 p_funcmode IN VARCHAR2,
1142 p_result OUT NOCOPY VARCHAR2)
1143 IS
1144
1145 v_err_stack VARCHAR2(2000);
1146 v_request_id NUMBER;
1147 v_parent_process VARCHAR2(30);
1148 v_allocation_run_id NUMBER;
1149 v_rule_id NUMBER;
1150 v_pa_period_name VARCHAR2(20);
1151 v_gl_period_name VARCHAR2(20);
1152 v_expenditure_item_date DATE;
1153 v_allocation_run_id NUMBER;
1154 v_step_number NUMBER;
1155 l_allocation_method_code Varchar2(1);
1156 v_batch_id Number;
1157 v_set_req_id Number;
1158 v_expenditure_group Varchar2(50);
1159 v_debug_mode Varchar2(30);
1160 v_operating_mode Varchar2(2);
1161
1162 Begin
1163 If ( p_funcmode = 'RUN' ) THEN
1164
1165 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
1166 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Submit_Conc_Process_Dist');
1167 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1168 PA_item_key,
1169 'WF_STACK');
1170 WriteDebugMsg(v_err_stack);
1171
1172 v_step_number := WF_ENGINE.GetItemAttrNumber(p_item_type,
1173 p_item_key,
1174 'STEP_NUMBER');
1175
1176 /** Get Attribute values to be passed as parameters **/
1177
1178 v_parent_process := WF_ENGINE.GetItemAttrText ( p_item_type
1179 ,p_item_key
1180 ,'PARENT_PROCESS');
1181
1182 v_set_req_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
1183 p_item_key,
1184 'SET_REQ_ID');
1185
1186 v_operating_mode := WF_ENGINE.GetItemAttrText( p_item_type,
1187 p_item_key,
1188 'OPERATING_MODE');
1189
1190 /* Set attributes Concurrent Program Code and Name */
1191
1192 WF_ENGINE.SetItemAttrText( p_item_type,
1193 p_item_key,
1194 'CONC_PRG_CODE',
1195 'PASDUC');
1196 WriteDebugMsg('The Attribute CONC_PRG_CODE value is: PASDUC ');
1197
1198 /* Get Parameters needed to submit Distribute Usage and Misc Costs*/
1199 v_expenditure_group := WF_ENGINE.GetItemAttrText( p_item_type,
1200 p_item_key,
1201 'EXPENDITURE_GROUP');
1202 WriteDebugMsg('The attribute EXPENDITURE_GROUP is '||v_expenditure_group);
1203
1204 FND_PROFILE.GET('PA_DEBUG_MODE',v_debug_mode);
1205 Writedebugmsg('Debug Mode = '||v_debug_mode);
1206
1207 /* Bug 2497324 change p_arg9 to p_arg11 as two parameters added in the process */
1208 v_request_id := Submit_Conc_Process(
1209 p_prog_code => 'PASDUC'
1210 ,p_arg1 => v_expenditure_group
1211 ,p_arg11 => v_debug_mode
1212 );
1213
1214 G_Err_Stage := 'End of API: Submit_Conc_Process_Dist';
1215
1216 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
1217 WriteDebugMsg(G_Err_Stage);
1218 Reset_PA_WF_Stack(p_item_type,p_item_key);
1219
1220 p_result := 'COMPLETE';
1221
1222 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1223 NULL;
1224 End If;
1225
1226 EXCEPTION
1227 WHEN OTHERS THEN
1228 Reset_PA_WF_Stack(p_item_type,p_item_key);
1229 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Submit_Conc_Process_Dist', PA_item_type, PA_item_key);
1230 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
1231 WriteDebugMsg('*********** Error Encountered ***********');
1232 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
1233 -- set status code to unexpected fatal error
1234 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1235 itemkey => p_item_key,
1236 aname => 'SET_REQ_ID');
1237 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1238 ,v_step_number
1239 ,'UFE'
1240 );
1241 Raise;
1242 END Submit_Conc_Process_Dist;
1243
1244 --------------------------------------------------------------------------------
1245 /** This function submits a concurrent request and returns the request id **/
1246
1247 Function Submit_Conc_Process(
1248 p_prog_code IN VARCHAR2
1249 ,p_arg1 IN VARCHAR2 DEFAULT NULL
1250 ,p_arg2 IN VARCHAR2 DEFAULT NULL
1251 ,p_arg3 IN VARCHAR2 DEFAULT NULL
1252 ,p_arg4 IN VARCHAR2 DEFAULT NULL
1253 ,p_arg5 IN VARCHAR2 DEFAULT NULL
1254 ,p_arg6 IN VARCHAR2 DEFAULT NULL
1255 ,p_arg7 IN VARCHAR2 DEFAULT NULL
1256 ,p_arg8 IN VARCHAR2 DEFAULT NULL
1257 ,p_arg9 IN VARCHAR2 DEFAULT NULL
1258 ,p_arg10 IN VARCHAR2 DEFAULT NULL
1259 ,p_arg11 IN VARCHAR2 DEFAULT NULL
1260 ,p_arg12 IN VARCHAR2 DEFAULT NULL
1261 ,p_arg13 IN VARCHAR2 DEFAULT NULL
1262 ,p_arg14 IN VARCHAR2 DEFAULT NULL
1263 ,p_arg15 IN VARCHAR2 DEFAULT NULL)
1264
1265 Return Number Is
1266
1267 v_userenv_lang Varchar2(10);
1268 v_conc_prg_name Varchar2(240);
1269 v_user_id Number;
1270 v_org_id Number;
1271 v_resp_id Number;
1272 v_resp_appl_id Number;
1273 v_created_by Number;
1274 v_last_updated_by Number;
1275 v_last_update_login Number;
1276 v_lang Varchar2(30);
1277 v_rollback_allowed Varchar2(1);
1278 v_request_id NUMBER;
1279 v_err_stack Varchar2(2000);
1280 v_step_number NUMBER;
1281 v_set_req_id Number;
1282 v_operating_mode Varchar2(2);
1283
1284 v_status_code Varchar2(15);
1285 v_message_Name Varchar2(150);
1286 x_org_id NUMBER := mo_global.get_current_org_id ;
1287
1288 -- Needs Modification
1289 Cursor conc_prog_name_C IS
1290 Select tl.USER_CONCURRENT_PROGRAM_NAME
1291 From fnd_concurrent_programs_tl tl,
1292 fnd_concurrent_programs cp
1293 Where cp.APPLICATION_ID = 275
1294 AND cp.CONCURRENT_PROGRAM_NAME = p_prog_code
1295 AND tl.CONCURRENT_PROGRAM_ID = cp.concurrent_program_id
1296 AND tl.APPLICATION_ID = 275
1297 AND tl.LANGUAGE = NVL(v_userenv_lang,'US');
1298
1299 Begin
1300 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Submit_Conc_Process');
1301 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1302 PA_item_key,
1303 'WF_STACK');
1304 WriteDebugMsg(v_err_stack);
1305
1306 WriteDebugMsg('p_arg1='||p_arg1);
1307 WriteDebugMsg('p_arg2='||p_arg2);
1308 WriteDebugMsg('p_arg3='||p_arg3);
1309 WriteDebugMsg('p_arg4='||p_arg4);
1310 WriteDebugMsg('p_arg5='||p_arg5);
1311 WriteDebugMsg('p_arg6='||p_arg6);
1312 WriteDebugMsg('p_arg7='||p_arg7);
1313 WriteDebugMsg('p_arg8='||p_arg8);
1314 WriteDebugMsg('p_arg9='||p_arg9);
1315 WriteDebugMsg('p_arg10='||p_arg10);
1316 WriteDebugMsg('p_arg11='||p_arg11);
1317 WriteDebugMsg('p_arg12='||p_arg12);
1318 WriteDebugMsg('p_arg13='||p_arg13);
1319 WriteDebugMsg('p_arg14='||p_arg14);
1320 WriteDebugMsg('p_arg15='||p_arg15);
1321 WriteDebugMsg('Prog_Code='||p_prog_code);
1322
1323 v_user_id := WF_ENGINE.GetItemAttrNumber
1324 (PA_item_type,
1325 PA_item_key,
1326 'USER_ID');
1327
1328 v_org_id := WF_ENGINE.GetItemAttrNumber
1329 (PA_item_type,
1330 PA_item_key,
1331 'ORG_ID');
1332
1333 v_resp_id := WF_ENGINE.GetItemAttrNumber
1334 (PA_item_type,
1335 PA_item_key,
1336 'RESP_ID');
1337
1338 v_resp_appl_id := WF_ENGINE.GetItemAttrNumber
1339 (PA_item_type,
1340 PA_item_key,
1341 'RESP_APPL_ID');
1342
1343 v_lang := WF_ENGINE.GetItemAttrText
1344 (PA_item_type,
1345 PA_item_key,
1346 'LANG');
1347
1348 v_set_req_id := WF_ENGINE.GetItemAttrNumber( PA_item_type,
1349 PA_item_key,
1350 'SET_REQ_ID');
1351
1352 v_step_number := WF_ENGINE.GetItemAttrNumber( PA_item_type,
1353 PA_item_key,
1354 'STEP_NUMBER');
1355
1356 v_operating_mode := WF_ENGINE.GetItemAttrText(PA_item_type,
1357 PA_item_key,
1358 'OPERATING_MODE');
1359 v_created_by := WF_ENGINE.GetItemAttrNumber
1360 (PA_item_type,
1361 PA_item_key,
1362 'CREATED_BY');
1363
1364 v_last_updated_by := WF_ENGINE.GetItemAttrNumber
1365 (PA_item_type,
1366 PA_item_key,
1367 'LAST_UPDATED_BY');
1368
1369 v_last_update_login := WF_ENGINE.GetItemAttrNumber
1370 (PA_item_type,
1371 PA_item_key,
1372 'LAST_UPDATE_LOGIN');
1373
1374 FND_PROFILE.put('LANG', v_lang);
1375 -- Fix for bug : 4640479
1376 -- FND_PROFILE.put('ORG_ID', v_org_id);
1377 MO_GLOBAL.set_policy_context('S',v_org_id);
1378 FND_PROFILE.put('USER_ID', v_user_id );
1379 FND_PROFILE.put('RESP_ID', v_resp_id);
1380 FND_PROFILE.put('RESP_APPL_ID', v_resp_appl_id);
1381
1382 FND_PROFILE.GET('LANG', v_userenv_lang);
1383 WriteDebugMsg('User Env. Lang = '||v_userenv_lang);
1384
1385 Open conc_prog_name_C;
1386 Fetch conc_prog_name_C into v_conc_prg_name;
1387 Close conc_prog_name_C;
1388
1389 wf_engine.SetItemAttrText(
1390 itemtype => PA_item_type,
1391 itemkey => PA_item_key,
1392 aname => 'CONC_PRG_NAME',
1393 avalue => v_conc_prg_name );
1394 WriteDebugMsg('Attribute CONC_PRG_NAME = '||v_conc_prg_name);
1395
1396 IF p_prog_code = 'PAXALGAT' THEN
1397 G_Err_Stage := 'Calling PA_GL_AUTOALLOC_PKG.Submit_Alloc_Request';
1398 WriteDebugMsg(G_Err_Stage);
1399 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
1400
1401 v_request_id := PA_GL_AUTOALLOC_PKG.Submit_Alloc_Request(
1402 p_rule_id => p_arg1,
1403 p_expnd_item_date =>
1404 fnd_date.canonical_to_date(p_arg2),
1405 p_pa_period => p_arg3,
1406 p_gl_period => p_arg4);
1407 ELSIF p_prog_code = 'PASDUC' then
1408 G_Err_Stage := 'Submit Request for Concurrent Program'||'PASDUC';
1409 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
1410 WriteDebugMsg(G_Err_Stage);
1411
1412 fnd_global.apps_initialize(v_user_id, v_resp_id, v_resp_appl_id); /* Bug#3485255 */
1413
1414 /* Bug 2497324 added parameters p_arg10,p_arg11 as two parameters added in the process */
1415 -- MOAC changes
1416 fnd_request.set_org_id (x_org_id);
1417 v_request_id :=
1418 FND_REQUEST.SUBMIT_REQUEST(
1419 'PA',
1420 'PASDUC',
1421 '',
1422 '',
1423 FALSE,
1424 p_arg1,
1425 p_arg2,
1426 p_arg3,
1427 p_arg4,
1428 p_arg5,
1429 p_arg6,
1430 p_arg7,
1431 p_arg8,
1432 p_arg9,
1433 p_arg10,
1434 p_arg11);
1435
1436 ELSE
1437 G_Err_Stage := 'Submit Request for Concurrent Program'||p_prog_code;
1438 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
1439 WriteDebugMsg(G_Err_Stage);
1440
1441 /** testing
1442 IF p_prog_code = 'PAXALRAT' then
1443 DBMS_LOCK.sleep(180);
1444 End If; **/
1445
1446 -- MOAC changes
1447 fnd_request.set_org_id (x_org_id);
1448 v_request_id :=
1449 FND_REQUEST.SUBMIT_REQUEST(
1450 'PA',
1451 p_prog_code,
1452 '',
1453 '',
1454 FALSE,
1455 p_arg1,
1456 p_arg2,
1457 p_arg3,
1458 p_arg4,
1459 p_arg5,
1460 p_arg6,
1461 p_arg7,
1462 p_arg8,
1463 p_arg9,
1464 p_arg10,
1465 p_arg11,
1466 p_arg12,
1467 p_arg13,
1468 p_arg14,
1469 p_arg15,
1470 '','','','','','','',
1471 '','','','','','','','','','','','','','','',
1472 '','','','','','','','','','','','','','','',
1473 '','','','','','','','','','','','','','','',
1474 '','','','','','','','','','','','','','','',
1475 '','','','','','','','','','','','','','','',
1476 '','','');
1477 END IF;
1478
1479 IF v_request_id <> 0 THEN
1480 WriteDebugMsg('Inserting req id = '||to_char(v_request_id)||
1481 ' into histroy detail');
1482
1483 GL_AUTO_ALLOC_WF_PKG.INSERT_BATCH_HIST_DET(
1484 p_REQUEST_ID => v_request_id
1485 ,p_PARENT_REQUEST_ID => v_set_req_id
1486 ,p_STEP_NUMBER => v_step_number
1487 ,p_PROGRAM_NAME_CODE => p_prog_code
1488 ,p_RUN_MODE => v_operating_mode
1489 ,p_allocation_type => 'PA'
1490 ,p_created_by => v_created_by
1491 ,p_last_updated_by => v_last_updated_by
1492 ,p_last_update_login => v_last_update_login);
1493
1494 /* Get Appropriate Status Code */
1495 Get_Status_and_Message(p_prog_Code
1496 ,'PENDING'
1497 ,v_rollback_allowed
1498 ,v_status_code
1499 ,v_message_name);
1500 --Generation pending status
1501 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1502 ,v_step_number
1503 ,v_status_code
1504 );
1505 End If;
1506
1507 wf_engine.SetItemAttrNumber(
1508 itemtype => PA_item_type,
1509 itemkey => PA_item_key,
1510 aname => 'CONC_REQUEST_ID',
1511 avalue => v_Request_ID );
1512 WriteDebugMsg('Attribute CONC_REQUEST_ID = '||v_request_id);
1513
1514 G_Err_Stage := 'End Of API Submit_Conc_Process';
1515 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
1516 WriteDebugMsg(G_Err_Stage);
1517 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
1518
1519 Return v_request_id;
1520
1521 END Submit_Conc_Process;
1522 --------------------------------------------------------------------------------
1523 /** This procedure deletes an allocation run given a rule_id and a run_id**/
1524
1525 Procedure Delete_Alloc_Run( p_item_type IN VARCHAR2,
1526 p_item_key IN VARCHAR2,
1527 p_actid IN NUMBER,
1528 p_funcmode IN VARCHAR2,
1529 p_result OUT NOCOPY VARCHAR2) IS
1530
1531 v_rule_id Number;
1532 v_allocation_run_id Number;
1533 v_step_number Number;
1534 v_err_stack Varchar2(2000);
1535 v_set_req_id Number;
1536
1537 Begin
1538
1539 IF p_funcmode = 'RUN' THEN
1540
1541 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
1542 Set_PA_WF_Stack(p_item_type,p_item_key,'Delete_Alloc_Run');
1543 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1544 PA_item_key,
1545 'WF_STACK');
1546 WriteDebugMsg(v_err_stack);
1547
1548
1549 /** Get rule_id, run_id from WF Attributes **/
1550
1551 v_allocation_run_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
1552 p_item_key,
1553 'ALLOCATION_RUN_ID');
1554
1555 v_rule_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
1556 p_item_key,
1557 'BATCH_ID');
1558
1559 PA_ALLOC_RUN.Delete_ALLOC_TXNS (v_rule_id,
1560 v_allocation_run_id);
1561
1562 Reset_PA_WF_Stack(p_item_type,p_item_key);
1563 p_result := 'COMPLETE';
1564
1565 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1566 NULL;
1567 End If;
1568
1569 Exception
1570
1571 WHEN OTHERS THEN
1572 Reset_PA_WF_Stack(p_item_type,p_item_key);
1573 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'DELETE_ALLOC_RUN', p_item_type, p_item_key);
1574 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
1575 WriteDebugMsg('*********** Error Encountered ***********');
1576 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
1577 -- set status code to unexpected fatal error
1578 v_step_number := WF_ENGINE.GetItemAttrNumber( p_item_type,
1579 p_item_key,
1580 'STEP_NUMBER');
1581 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1582 itemkey => p_item_key,
1583 aname => 'SET_REQ_ID');
1584 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1585 ,v_step_number
1586 ,'UFE'
1587 );
1588 Raise;
1589
1590 End Delete_Alloc_Run;
1591 ------------------------------------------------------------------------------
1592 /** This is the function for checking the completion status of the concurrent process **/
1593 Procedure Check_Process_Status( p_item_type IN VARCHAR2,
1594 p_item_key IN VARCHAR2,
1595 p_actid IN NUMBER,
1596 p_funcmode IN VARCHAR2,
1597 p_result OUT NOCOPY VARCHAR2) Is
1598
1599 v_request_id Number;
1600 p_phase Varchar2(30);
1601 p_status Varchar2(30);
1602 p_dev_phase Varchar2(30);
1603 p_dev_status Varchar2(30);
1604 p_message Varchar2(240) ;
1605 v_call_status Boolean;
1606 v_step_number Number;
1607 v_conc_prg_code Varchar2(15);
1608 v_status_code Varchar2(15);
1609 v_message_Name Varchar2(150);
1610 v_err_stack VARCHAR2(2000);
1611 v_rollback_allowed Varchar2(1);
1612 v_set_req_id Number;
1613 v_user_id Number;
1614 v_org_id Number;
1615 v_resp_id Number;
1616 v_resp_appl_id Number;
1617 v_lang varchar2(30);
1618
1619 Begin
1620 If ( p_funcmode = 'RUN' ) THEN
1621 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
1622
1623 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Check_Process_Status');
1624 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1625 PA_item_key,
1626 'WF_STACK');
1627 WriteDebugMsg(v_err_stack);
1628
1629 v_step_number := WF_ENGINE.GetItemAttrNumber
1630 (PA_item_type,
1631 PA_item_key,
1632 'STEP_NUMBER');
1633 WriteDebugMsg('Attribute STEP_NUMBER = '||to_char(v_step_number));
1634
1635 v_conc_prg_code := WF_ENGINE.GetItemAttrText
1636 (PA_item_type,
1637 PA_item_key,
1638 'CONC_PRG_CODE');
1639 WriteDebugMsg('Attribute CONC_PRG_CODE = '||v_conc_prg_code);
1640
1641 v_rollback_allowed := WF_ENGINE.GetItemAttrText(
1642 PA_item_type,
1643 PA_item_key,
1644 'ROLLBACK_ALLOWED');
1645 WriteDebugMsg('Attribute ROLLBACK_ALLOWED = '||v_rollback_allowed);
1646
1647 v_request_id := WF_ENGINE.GetItemAttrNumber
1648 (PA_item_type,
1649 PA_item_key,
1650 'CONC_REQUEST_ID');
1651
1652 WriteDebugMsg('Attribute CONC_REQUEST_ID = '||to_char(v_request_id));
1653
1654 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1655 itemkey => p_item_key,
1656 aname => 'SET_REQ_ID');
1657
1658 IF v_request_id = 0 THEN /* Concurrent Program could not be submitted */
1659 WF_ENGINE.SetItemAttrText(p_item_type,
1660 p_item_key,
1661 'PARENT_PROCESS',
1662 'Concurrent Process Error');
1663
1664 WriteDebugMsg('Sending Notification.Fatal Error: Conc program not submitted');
1665 Get_Status_and_Message(v_conc_prg_Code
1666 ,'ZEROERROR'
1667 ,v_rollback_allowed
1668 ,v_status_code
1669 ,v_message_name);
1670
1671 wf_engine.SetItemAttrText(itemtype => PA_item_type,
1672 itemkey => PA_item_key,
1673 aname => 'MESSAGE_NAME',
1674 avalue => v_message_name );
1675 WriteDebugMsg('Message_name = '||v_message_Name);
1676
1677 -- Program Error
1678 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1679 ,v_step_number
1680 ,v_status_code
1681 );
1682
1683 Reset_PA_WF_Stack(p_item_type,p_item_key);
1684 p_result := 'COMPLETE:FAIL';
1685 return;
1686
1687 END IF;/* Request ID =0 */
1688
1689 v_user_id := WF_ENGINE.GetItemAttrNumber
1690 (PA_item_type,
1691 PA_item_key,
1692 'USER_ID');
1693
1694 v_org_id := WF_ENGINE.GetItemAttrNumber
1695 (PA_item_type,
1696 PA_item_key,
1697 'ORG_ID');
1698
1699 v_resp_id := WF_ENGINE.GetItemAttrNumber
1700 (PA_item_type,
1701 PA_item_key,
1702 'RESP_ID');
1703
1704 v_resp_appl_id := WF_ENGINE.GetItemAttrNumber
1705 (PA_item_type,
1706 PA_item_key,
1707 'RESP_APPL_ID');
1708 v_lang := WF_ENGINE.GetItemAttrText
1709 (PA_item_type,
1710 PA_item_key,
1711 'LANG');
1712
1713 FND_PROFILE.put('LANG', v_lang);
1714 -- Fix for bug : 4640479
1715 -- FND_PROFILE.put('ORG_ID', v_org_id);
1716 MO_GLOBAL.set_policy_context('S',v_org_id);
1717 FND_PROFILE.put('USER_ID', v_user_id );
1718 FND_PROFILE.put('RESP_ID', v_resp_id);
1719 FND_PROFILE.put('RESP_APPL_ID', v_resp_appl_id);
1720
1721 v_call_status :=
1722 Fnd_Concurrent.Wait_For_Request(
1723 request_id => v_request_id
1724 ,Interval => 30
1725 ,Max_wait => 360000
1726 ,phase => p_phase
1727 ,status => p_status
1728 ,dev_phase => p_dev_phase
1729 ,dev_status => p_dev_status
1730 ,message => p_message );
1731
1732 WriteDebugMsg('Phase = '||p_dev_phase);
1733 WriteDebugMsg('Status = '||p_dev_status);
1734
1735 if not (v_call_status) THEN
1736 WriteDebugMsg('Wait for request return message = '||p_message);
1737 end if;
1738
1739 If p_dev_phase = 'COMPLETE' AND
1740 p_dev_status In ('NORMAL') Then
1741 WriteDebugMsg('Completed concurrent program = '||
1742 to_char(v_request_id) );
1743
1744 -- set status code program completed
1745 Get_Status_and_Message(v_conc_prg_code
1746 ,'COMPLETE'
1747 ,v_rollback_allowed
1748 ,v_status_code
1749 ,v_message_name);
1750 WriteDebugMsg('Status Code Value = '||v_status_code);
1751
1752 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1753 ,v_step_number
1754 ,v_status_code
1755 );
1756
1757 WriteDebugMsg('Updated Status Code Value = '||v_status_code);
1758
1759 p_result := 'COMPLETE:PASS';
1760 Reset_PA_WF_Stack(p_item_type,p_item_key);
1761 return;
1762 Else
1763 WriteDebugMsg('Sending Notification. Concurrent program not completed'
1764 ||'Request ID is = '|| to_char(v_request_id) );
1765 WriteDebugMsg('Phase = '||p_dev_phase||' Status ='||p_dev_status);
1766 WF_Engine.SetItemAttrText (p_item_type,
1767 p_item_key,
1768 'PARENT_PROCESS',
1769 'Concurrent Process Error');
1770 Get_Status_and_Message(v_conc_prg_code
1771 ,'ERROR'
1772 ,v_rollback_allowed
1773 ,v_status_code
1774 ,v_message_name);
1775
1776 -- Program Error
1777 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1778 ,v_step_number
1779 ,v_status_code
1780 );
1781
1782 WriteDebugMsg('Updated Status Code Value = '||v_status_code);
1783
1784 wf_engine.SetItemAttrText(itemtype => PA_item_type,
1785 itemkey => PA_item_key,
1786 aname => 'MESSAGE_NAME',
1787 avalue => v_message_name );
1788
1789 WriteDebugMsg('Message_name = '||v_message_Name);
1790
1791 Reset_PA_WF_Stack(p_item_type,p_item_key);
1792 p_result := 'COMPLETE:FAIL';
1793 return;
1794 End If;
1795 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1796 NULL;
1797 End If;
1798
1799 EXCEPTION
1800 When Others Then
1801 Reset_PA_WF_Stack(p_item_type,p_item_key);
1802 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Process_Status',
1803 p_item_type, p_item_key);
1804 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
1805 WriteDebugMsg('*********** Error Encountered **********');
1806 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
1807 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1808 itemkey => p_item_key,
1809 aname => 'SET_REQ_ID');
1810 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1811 ,v_step_number
1812 ,'UFE'
1813 );
1814 Raise;
1815
1816 End Check_Process_Status ;
1817 --------------------------------------------------------------------------------
1818 /** This is the function for checking the exceptions in the allocation run process **/
1819
1820 Procedure Check_Alloc_Run_Status( p_item_type IN VARCHAR2,
1821 p_item_key IN VARCHAR2,
1822 p_actid IN NUMBER,
1823 p_funcmode IN VARCHAR2,
1824 p_result OUT NOCOPY VARCHAR2) Is
1825
1826 v_conc_request_id Number;
1827 p_phase Varchar2(30);
1828 p_status Varchar2(30);
1829 p_dev_phase Varchar2(30);
1830 p_dev_status Varchar2(30);
1831 p_message Varchar2(240) ;
1832 v_call_status Boolean;
1833 v_step_number Number;
1834 v_conc_prg_code Varchar2(15);
1835 v_rollback_allowed Varchar2(1);
1836 v_parent_process Varchar2(30);
1837 v_result Varchar2(10);
1838 v_run_id Number;
1839 v_org_id Number;
1840 v_batch_id Number;
1841 v_err_stack Varchar2(2000);
1842 v_run_status Varchar2(2);
1843 v_set_req_id Number;
1844 v_message_Name Varchar2(150);
1845 v_status_code Varchar2(15);
1846 v_return_code Number;
1847
1848 Begin
1849 If ( p_funcmode = 'RUN' ) THEN
1850 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
1851
1852 Set_PA_WF_Stack(p_item_type,p_item_key,'Check_Alloc_Run_Status');
1853 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
1854 PA_item_key,
1855 'WF_STACK');
1856 WriteDebugMsg(v_err_stack);
1857
1858 v_rollback_allowed := WF_ENGINE.GetItemAttrText(
1859 PA_item_type,
1860 PA_item_key,
1861 'ROLLBACK_ALLOWED');
1862 v_parent_process := WF_ENGINE.GetItemAttrText
1863 (p_item_type,
1864 p_item_key,
1865 'PARENT_PROCESS');
1866 WriteDebugMsg('Attribute Parent_Process = '||v_parent_process);
1867
1868 v_batch_id := WF_ENGINE.GetItemAttrNumber
1869 (p_item_type,
1870 p_item_key,
1871 'BATCH_ID');
1872 v_org_id := WF_ENGINE.GetItemAttrNumber
1873 (p_item_type,
1874 p_item_key,
1875 'ORG_ID');
1876 v_conc_request_id := WF_ENGINE.GetItemAttrNumber
1877 (p_item_type,
1878 p_item_key,
1879 'CONC_REQUEST_ID');
1880 v_step_number := WF_ENGINE.GetItemAttrNumber
1881 (p_item_type,
1882 p_item_key,
1883 'STEP_NUMBER');
1884 v_conc_prg_code := WF_ENGINE.GetItemAttrText
1885 (p_item_type,
1886 p_item_key,
1887 'CONC_PRG_CODE');
1888
1889 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
1890 itemkey => p_item_key,
1891 aname => 'SET_REQ_ID');
1892
1893 IF v_parent_process = 'Generate Allocation' THEN
1894 begin
1895 select run_status,run_id
1896 into v_run_status,v_run_id
1897 from PA_ALLOC_RUNS_ALL
1898 where rule_id = v_batch_id
1899 and draft_request_id = v_conc_request_id;
1900
1901 WriteDebugMsg('Run Status = '||v_run_status);
1902 WriteDebugMsg('Run ID = '||to_char(v_run_id));
1903
1904 WF_Engine.SetItemAttrNumber (p_item_type,
1905 p_item_key,
1906 'ALLOCATION_RUN_ID',
1907 v_run_id);
1908
1909 WriteDebugMsg('Calling GL API to update PA_ALLOCATION_RUN_ID');
1910 GL_PA_AUTOALLOC_PKG.upd_gl_autoalloc_batch_hist
1911 (p_request_id => v_set_req_id,
1912 p_step_number => v_step_number,
1913 p_pa_allocation_run_id => v_run_id,
1914 p_return_code => v_return_code);
1915
1916 IF v_return_code = -1 THEN
1917 WriteDebugMsg('Run ID could not be updated for request ID: '
1918 ||to_char(v_conc_request_id)||' and step number: '||
1919 to_char(v_step_number));
1920 ELSE
1921
1922 WriteDebugMsg('GL_AUTO_ALLOC_BAT_HIST_DET updated with RUN_ID');
1923
1924 END IF;
1925
1926 IF v_run_status in ('DF','IP') THEN
1927 WF_Engine.SetItemAttrText(p_item_type,
1928 p_item_key,
1929 'PARENT_PROCESS',
1930 'Concurrent Process Exception');
1931 WriteDebugMsg('Setting attribute PARENT_PROCESS value = '||
1932 'Concurrent Process Exception');
1933
1934 Get_Status_and_Message(v_conc_prg_code
1935 ,'EXCEPTION'
1936 ,v_rollback_allowed
1937 ,v_status_code
1938 ,v_message_name);
1939 -- Program Error
1940 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1941 ,v_step_number
1942 ,v_status_code
1943 );
1944 wf_engine.SetItemAttrText(itemtype => PA_item_type,
1945 itemkey => PA_item_key,
1946 aname => 'MESSAGE_NAME',
1947 avalue => v_message_name );
1948
1949 WriteDebugMsg('Message_name = '||v_message_Name);
1950
1951 p_result := 'COMPLETE:FAIL';
1952 ELSE
1953 p_result := 'COMPLETE:PASS';
1954 END IF;
1955
1956 exception
1957 when no_data_found then
1958 WF_Engine.SetItemAttrText(p_item_type,
1959 p_item_key,
1960 'PARENT_PROCESS',
1961 'Concurrent Process Exception');
1962 WriteDebugMsg('Setting attribute PARENT_PROCESS value = '||
1963 'Concurrent Process Exception');
1964 Get_Status_and_Message(v_conc_prg_code
1965 ,'EXCEPTION'
1966 ,v_rollback_allowed
1967 ,v_status_code
1968 ,v_message_name);
1969 -- Program Error
1970 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
1971 ,v_step_number
1972 ,v_status_code
1973 );
1974 wf_engine.SetItemAttrText(itemtype => PA_item_type,
1975 itemkey => PA_item_key,
1976 aname => 'MESSAGE_NAME',
1977 avalue => v_message_name );
1978
1979 p_result := 'COMPLETE:FAIL';
1980 end;/* Generate Allocation*/
1981
1982 ELSE /* Release Allocation Process */
1983 begin
1984 v_run_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
1985 p_item_key,
1986 'ALLOCATION_RUN_ID');
1987 WriteDebugMsg('Checking Release Status');
1988 select run_status
1989 into v_run_status
1990 from PA_ALLOC_RUNS_ALL
1991 where rule_id = v_batch_id
1992 and release_request_id = v_conc_request_id;
1993
1994 WriteDebugMsg('Run Status = '||v_run_status);
1995
1996 IF (v_run_status = 'RF' or v_run_status = 'IP') THEN
1997 WF_Engine.SetItemAttrText(p_item_type,
1998 p_item_key,
1999 'PARENT_PROCESS',
2000 'Concurrent Process Exception');
2001 Get_Status_and_Message(v_conc_prg_code
2002 ,'EXCEPTION'
2003 ,v_rollback_allowed
2004 ,v_status_code
2005 ,v_message_name);
2006 -- Program Error
2007 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2008 ,v_step_number
2009 ,v_status_code
2010 );
2011 wf_engine.SetItemAttrText(itemtype => PA_item_type,
2012 itemkey => PA_item_key,
2013 aname => 'MESSAGE_NAME',
2014 avalue => v_message_name );
2015
2016 WriteDebugMsg('Message_name = '||v_message_Name);
2017 p_result := 'COMPLETE:FAIL';
2018 ELSE
2019 p_result := 'COMPLETE:PASS';
2020 END IF;
2021 exception
2022 when no_data_found then
2023 WF_ENGINE.SetItemAttrText(p_item_type,
2024 p_item_key,
2025 'PARENT_PROCESS',
2026 'Concurrent Process Exception');
2027 WriteDebugMsg('Setting attribute PARENT_PROCESS value = '||
2028 'Concurrent Process Exception');
2029 Get_Status_and_Message(v_conc_prg_code
2030 ,'EXCEPTION'
2031 ,v_rollback_allowed
2032 ,v_status_code
2033 ,v_message_name);
2034 -- Program Error
2035 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2036 ,v_step_number
2037 ,v_status_code
2038 );
2039 wf_engine.SetItemAttrText(itemtype => PA_item_type,
2040 itemkey => PA_item_key,
2041 aname => 'MESSAGE_NAME',
2042 avalue => v_message_name );
2043 p_result := 'COMPLETE:FAIL';
2044 end;/* Release Allocation */
2045 END IF; /* Parent Process */
2046 G_Err_Stage := 'End Of API: Check_Alloc_Run_Status';
2047 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2048 WriteDebugMsg(G_Err_Stage);
2049 Reset_PA_WF_Stack(p_item_type,p_item_key);
2050 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2051 NULL;
2052 END IF;/* funcmode = run */
2053
2054 EXCEPTION
2055 When Others Then
2056 Reset_PA_WF_Stack(p_item_type,p_item_key);
2057 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Alloc_Run_Status', p_item_type, p_item_key);
2058 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2059 WriteDebugMsg('************Error Encountered***********');
2060 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
2061 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2062 itemkey => p_item_key,
2063 aname => 'SET_REQ_ID');
2064 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2065 ,v_step_number
2066 ,'UFE'
2067 );
2068 Raise;
2069 END Check_Alloc_Run_Status;
2070
2071 -----------------------------------------------------------------------------------
2072 /** This procedure checks if allocation run is released or not.**/
2073
2074 PROCEDURE Check_Alloc_Release( p_item_type IN VARCHAR2,
2075 p_item_key IN VARCHAR2,
2076 p_actid IN NUMBER,
2077 p_funcmode IN VARCHAR2,
2078 p_result OUT NOCOPY VARCHAR2) Is
2079
2080 v_org_id NUMBER;
2081 v_batch_id NUMBER;
2082 v_allocation_run_id NUMBER;
2083 v_result VARCHAR2(10);
2084 v_err_stack Varchar2(2000);
2085 v_step_number Number;
2086 v_definition_form Varchar2(500);
2087 v_set_req_id Number;
2088
2089 BEGIN
2090 If ( p_funcmode = 'RUN' ) THEN
2091
2092 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
2093 Set_PA_WF_Stack(p_item_type,p_item_key,'Check_Alloc_Release');
2094 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2095 PA_item_key,
2096 'WF_STACK');
2097 WriteDebugMsg(v_err_stack);
2098
2099 v_org_id := WF_Engine.GetItemAttrNumber (p_item_type,
2100 p_item_key,
2101 'ORG_ID');
2102 v_batch_id := WF_Engine.GetItemAttrNumber (p_item_type,
2103 p_item_key,
2104 'BATCH_ID');
2105 v_allocation_run_id := WF_Engine.GetItemAttrNumber ( p_item_type,
2106 p_item_key,
2107 'ALLOCATION_RUN_ID');
2108 v_step_number := WF_Engine.GetItemAttrNumber (p_item_type,
2109 p_item_key,
2110 'STEP_NUMBER');
2111 select 'RELEASED'
2112 into v_result
2113 from PA_ALLOC_RUNS_ALL
2114 where run_id = v_allocation_run_id
2115 and run_status = 'RS';
2116
2117 p_result := 'COMPLETE:'||v_result;
2118
2119 G_Err_Stage := 'End of API: Check_Alloc_Release';
2120 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2121 WriteDebugMsg(G_Err_Stage);
2122 Reset_PA_WF_Stack(p_item_type,p_item_key);
2123
2124 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2125 NULL;
2126 End If;
2127
2128 EXCEPTION
2129 when no_data_found then
2130 Reset_PA_WF_Stack(p_item_type,p_item_key);
2131 v_result := 'DRAFT';
2132 /** Set The definition form to Review Allocation Run Form */
2133 v_definition_form := 'PA_PAXALRAR: CHAR_RUN_ID='||
2134 to_char(V_ALLOCATION_RUN_ID);
2135
2136
2137 WF_Engine.SetItemAttrText(itemtype => p_item_type,
2138 itemkey => p_item_key,
2139 aname => 'DEFINITION_FORM',
2140 avalue => v_definition_form);
2141
2142 p_result := 'COMPLETE:'||v_result;
2143 When Others Then
2144 Reset_PA_WF_Stack(p_item_type,p_item_key);
2145 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Alloc_Release', p_item_type, p_item_key);
2146 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2147 WriteDebugMsg('************Error Encountered***********');
2148 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
2149 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2150 itemkey => p_item_key,
2151 aname => 'SET_REQ_ID');
2152 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2153 ,v_step_number
2154 ,'UFE'
2155 );
2156 Raise;
2157
2158 END Check_Alloc_Release;
2159 --------------------------------------------------------------------------------
2160 /** This is the function for checking the exceptions in the distribute cost process **/
2161
2162 Procedure Check_Costing_Process( p_item_type IN VARCHAR2,
2163 p_item_key IN VARCHAR2,
2164 p_actid IN NUMBER,
2165 p_funcmode IN VARCHAR2,
2166 p_result OUT NOCOPY VARCHAR2) Is
2167
2168 p_message Varchar2(240) ;
2169 v_status_code Varchar2(15);
2170 v_message_Name Varchar2(150);
2171 v_result Varchar2(10);
2172 v_run_id Number;
2173 v_org_id Number;
2174 v_conc_prg_code Varchar2(15);
2175 v_rollback_allowed Varchar2(1);
2176 v_err_stack VARCHAR2(2000);
2177 v_expenditure_group Varchar2(50);
2178 v_step_number Number;
2179 v_set_req_id Number;
2180
2181 Begin
2182 If ( p_funcmode = 'RUN' ) THEN
2183
2184 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
2185 Set_PA_WF_Stack(p_item_type,p_item_key,'Check_Costing_Process');
2186 v_err_stack := WF_ENGINE.GetItemAttrText( p_item_type,
2187 p_item_key,
2188 'WF_STACK');
2189 WriteDebugMsg(v_err_stack);
2190
2191 v_set_req_id := WF_ENGINE.GetItemAttrNumber
2192 (p_item_type,
2193 p_item_key,
2194 'SET_REQ_ID');
2195
2196 v_step_number := WF_ENGINE.GetItemAttrNumber
2197 (p_item_type,
2198 p_item_key,
2199 'STEP_NUMBER');
2200
2201 v_org_id := WF_ENGINE.GetItemAttrNumber
2202 (p_item_type,
2203 p_item_key,
2204 'ORG_ID');
2205 v_expenditure_group := WF_ENGINE.GetItemAttrText
2206 (p_item_type,
2207 p_item_key,
2208 'EXPENDITURE_GROUP');
2209 v_conc_prg_code := WF_ENGINE.GetItemAttrText
2210 (p_item_type,
2211 p_item_key,
2212 'CONC_PRG_CODE');
2213 v_rollback_allowed:= WF_ENGINE.GetItemAttrText
2214 (p_item_type,
2215 p_item_key,
2216 'ROLLBACK_ALLOWED');
2217
2218 select 'FAIL'
2219 into v_result
2220 From dual
2221 where exists (select 'Y'
2222 from PA_Expenditure_Items_All EI,
2223 PA_Expenditures_All ES
2224 where ES.expenditure_group = v_expenditure_group
2225 and EI.expenditure_item_id = ES.expenditure_id
2226 and EI.cost_distributed_flag||'' = 'N'
2227 );
2228
2229 Get_Status_and_Message(v_conc_prg_Code
2230 ,'EXCEPTION'
2231 ,v_rollback_allowed
2232 ,v_status_code
2233 ,v_message_name);
2234 -- Program Error
2235 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2236 ,v_step_number
2237 ,v_status_code
2238 );
2239 wf_engine.SetItemAttrText(itemtype => PA_item_type,
2240 itemkey => PA_item_key,
2241 aname => 'MESSAGE_NAME',
2242 avalue => v_message_name );
2243
2244 G_Err_Stage := 'The expenditure Group '||v_expenditure_group||
2245 'is yet to be fully costed';
2246 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2247 WriteDebugMsg(G_Err_Stage);
2248
2249 G_Err_Stage := 'End of API: Check_Costing_Process';
2250 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2251 WriteDebugMsg(G_Err_Stage);
2252 Reset_PA_WF_Stack(p_item_type,p_item_key);
2253
2254 p_result := 'COMPLETE:FAIL';
2255
2256 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2257 NULL;
2258 End If;
2259
2260 Exception
2261 When no_data_found then
2262 Reset_PA_WF_Stack(p_item_type,p_item_key);
2263 p_result := 'COMPLETE:PASS';
2264
2265 When Others Then
2266 Reset_PA_WF_Stack(p_item_type,p_item_key);
2267 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Costing_Process', p_item_type, p_item_key);
2268 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2269 WriteDebugMsg('********** Encountered Errors ***********');
2270 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
2271 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2272 ,v_step_number
2273 ,'UFE'
2274 );
2275
2276 END Check_Costing_Process;
2277 --------------------------------------------------------------------------------
2278 /** This is the function for checking the exceptions in the Summarization process **/
2279
2280 Procedure Check_Summary_Process( p_item_type IN VARCHAR2,
2281 p_item_key IN VARCHAR2,
2282 p_actid IN NUMBER,
2283 p_funcmode IN VARCHAR2,
2284 p_result OUT NOCOPY VARCHAR2) Is
2285
2286 p_message Varchar2(240) ;
2287 v_status_code Varchar2(15);
2288 v_rollback_allowed Varchar2(1);
2289 v_message_Name Varchar2(150);
2290 v_result Varchar2(5);
2291 v_run_id Number;
2292 v_org_id Number;
2293 v_err_stack VARCHAR2(2000);
2294 v_request_id Number;
2295 v_step_number Number;
2296 v_set_req_id Number;
2297
2298 Begin
2299 If ( p_funcmode = 'RUN' ) THEN
2300 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
2301
2302 Set_PA_WF_Stack(p_item_type,p_item_key,'Check_Summary_Process');
2303 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2304 PA_item_key,
2305 'WF_STACK');
2306 WriteDebugMsg(v_err_stack);
2307
2308 v_org_id := WF_ENGINE.GetItemAttrNumber
2309 (p_item_type,
2310 p_item_key,
2311 'ORG_ID');
2312 v_set_req_id := WF_ENGINE.GetItemAttrNumber
2313 (p_item_type,
2314 p_item_key,
2315 'SET_REQ_ID');
2316 v_step_number := WF_ENGINE.GetItemAttrNumber
2317 (p_item_type,
2318 p_item_key,
2319 'STEP_NUMBER');
2320 v_request_id := WF_ENGINE.GetItemAttrNumber
2321 (p_item_type,
2322 p_item_key,
2323 'CONC_REQUEST_ID');
2324
2325 v_result := Check_Summarization_status (v_request_id);
2326 IF v_result = 'FAIL' THEN
2327 /* set parent process */
2328 WF_Engine.SetItemAttrText(itemtype => p_item_type,
2329 itemkey => p_item_key,
2330 aname => 'PARENT_PROCESS',
2331 avalue => 'Concurrent Process Exception');
2332
2333 Get_Status_and_Message('PAXACMPT'
2334 ,'EXCEPTION'
2335 ,v_rollback_allowed
2336 ,v_status_code
2337 ,v_message_name);
2338
2339 wf_engine.SetItemAttrText(itemtype => PA_item_type,
2340 itemkey => PA_item_key,
2341 aname => 'MESSAGE_NAME',
2342 avalue => v_message_name );
2343 -- Program Error
2344 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2345 ,v_step_number
2346 ,v_status_code
2347 );
2348
2349 G_Err_Stage := 'Summarization Process has exceptions';
2350 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2351 WriteDebugMsg(G_Err_Stage);
2352
2353 p_result := 'COMPLETE:FAIL';
2354 ELSE
2355 G_Err_Stage := 'Summarization Process is successful';
2356 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2357 WriteDebugMsg(G_Err_Stage);
2358 p_result := 'COMPLETE:PASS';
2359 END IF;
2360
2361 G_Err_Stage := 'End of API: Check_Summary_Process';
2362 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2363 WriteDebugMsg(G_Err_Stage);
2364 Reset_PA_WF_Stack(p_item_type,p_item_key);
2365
2366 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2367 NULL;
2368 End If;
2369
2370 EXCEPTION
2371 When Others Then
2372 Reset_PA_WF_Stack(p_item_type,p_item_key);
2373 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Check_Summary_Process',
2374 p_item_type, p_item_key);
2375 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2376 WriteDebugMsg('************* Error Encountered **********');
2377 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
2378 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2379 itemkey => p_item_key,
2380 aname => 'SET_REQ_ID');
2381 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2382 ,v_step_number
2383 ,'UFE'
2384 );
2385
2386 END Check_Summary_Process;
2387 --------------------------------------------------------------------------------
2388 /*** This function checks if the request submitted for summarization
2389 has any exceptions. Returns 'FAIL' if an exception occured,
2390 'PASS' otherwise ***/
2391
2392 Function Check_Summarization_Status( p_request_id IN Number)
2393 Return Varchar2
2394 IS
2395 v_summarization_status VARCHAR2(5);
2396 v_err_stack Varchar2(2000);
2397
2398 BEGIN
2399
2400 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Check_Summarization_Status');
2401 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2402 PA_item_key,
2403 'WF_STACK');
2404 WriteDebugMsg(v_err_stack);
2405
2406 select 'FAIL'
2407 into v_summarization_status
2408 from dual
2409 where exists
2410 (select 'Exception'
2411 from pa_projects_for_accum
2412 where request_id = p_request_id
2413 and exception_flag = 'Y');
2414
2415 return v_summarization_status;
2416
2417 G_Err_Stage := 'End of API: Check_Summarization_Status';
2418 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
2419 WriteDebugMsg(G_Err_Stage);
2420 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
2421
2422 Exception
2423 when no_data_found then
2424 G_Err_Stage := 'End of API: Check_Summarization_Status';
2425 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
2426 WriteDebugMsg(G_Err_Stage);
2427 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
2428 return 'PASS';
2429
2430
2431 END Check_Summarization_Status;
2432 -------------------------------------------------------------------------------
2433 /** This function submits Concurrent Process to release Project Allocation Transactions.This is called from PA Step down Allocation Work Flow **/
2434
2435 PROCEDURE Submit_Conc_AllocRls( p_item_type IN VARCHAR2,
2436 p_item_key IN VARCHAR2,
2437 p_actid IN NUMBER,
2438 p_funcmode IN VARCHAR2,
2439 p_result OUT NOCOPY VARCHAR2) Is
2440
2441 v_request_id NUMBER;
2442 v_parent_process VARCHAR2(30);
2443 v_allocation_run_id NUMBER;
2444 v_rule_id NUMBER;
2445 v_run_period VARCHAR2(20);
2446 v_expenditure_item_date DATE;
2447 v_allocation_run_id NUMBER;
2448
2449 v_step_number NUMBER;
2450 l_allocation_method_code Varchar2(1);
2451 l_usage_code Varchar2(1);
2452 l_sob_id Number;
2453 l_period_name Varchar2(15);
2454 l_journal_effective_date Date;
2455 l_calc_effective_date Date;
2456 l_batch_id Number;
2457 v_set_req_id Number;
2458 v_err_stack Varchar2(2000);
2459 v_operating_mode VARCHAR2(2);
2460
2461 Begin
2462 If ( p_funcmode = 'RUN' ) THEN
2463 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
2464 Set_PA_WF_Stack(p_item_type,p_item_key,'Submit_Conc_AllocRls');
2465 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2466 PA_item_key,
2467 'WF_STACK');
2468 WriteDebugMsg(v_err_stack);
2469
2470 v_parent_process := WF_ENGINE.GetItemAttrText ( p_item_type
2471 ,p_item_key
2472 ,'PARENT_PROCESS');
2473
2474 v_set_req_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
2475 p_item_key,
2476 'SET_REQ_ID');
2477
2478 v_operating_mode := WF_ENGINE.GetItemAttrText( p_item_type,
2479 p_item_key,
2480 'OPERATING_MODE');
2481
2482 /* Set attributes Concurrent Program Code and Name */
2483
2484 WF_ENGINE.SetItemAttrText( p_item_type,
2485 p_item_key,
2486 'CONC_PRG_CODE',
2487 'PAXALRAT');
2488 WriteDebugMsg('Attribute CONC_PRG_CODE = PAXALRAT');
2489
2490 /* Get Parameters needed to submit Allocation Release Process */
2491
2492 v_rule_id := WF_ENGINE.GetItemAttrNumber( p_item_type,
2493 p_item_key,
2494 'BATCH_ID');
2495
2496 v_request_id := Submit_Conc_Process
2497 ('PAXALRAT',
2498 p_arg1 => to_char(v_rule_id),
2499 p_arg2 => 'R'
2500 );
2501
2502 WF_ENGINE.SetItemAttrText( p_item_type,
2503 p_item_key,
2504 'PARENT_PROCESS',
2505 'Release Allocation');
2506
2507 G_Err_Stage := 'End of API: Submit_Conc_AllocRls';
2508 Set_PA_WF_Stage(p_item_type,p_item_key,G_Err_Stage);
2509 WriteDebugMsg(G_Err_Stage);
2510 Reset_PA_WF_Stack(p_item_type,p_item_key);
2511
2512 p_result := 'COMPLETE';
2513 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2514 NULL;
2515 End If;
2516
2517 EXCEPTION
2518 WHEN OTHERS THEN
2519 Reset_PA_WF_Stack(p_item_type,p_item_key);
2520 Wf_Core.Context('PA_AUTOALLOC_WF_PKG', 'Submit_Conc_AllocRls', p_item_type, p_item_key);
2521 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2522 WriteDebugMsg(G_err_msg ||'*'||G_err_stack);
2523 WriteDebugMsg('************ Error Encountered *************');
2524 -- set status code to unexpected fatal error
2525 v_SET_REQ_ID := WF_ENGINE.GetItemAttrNumber(itemtype => p_item_type,
2526 itemkey => p_item_key,
2527 aname => 'SET_REQ_ID');
2528 GL_AUTO_ALLOC_WF_PKG.Update_Status(v_set_req_id
2529 ,v_step_number
2530 ,'UFE'
2531 );
2532 Raise;
2533 END Submit_Conc_AllocRls;
2534 --------------------------------------------------------------------------------
2535 /**This procedure sets a GL attribute value based on the result of PA Step Down
2536 Allocation Process and issues a complete activity for the block. **/
2537
2538 PROCEDURE Set_PA_WF_Status( p_item_type IN VARCHAR2,
2539 p_item_key IN VARCHAR2,
2540 p_actid IN NUMBER,
2541 p_funcmode IN VARCHAR2,
2542 p_result OUT NOCOPY VARCHAR2)
2543 IS
2544 v_gl_item_type Varchar2(10);
2545 v_gl_item_key Varchar2(40);
2546 v_gl_block_activity VARCHAR2(15);
2547 v_result VARCHAR2(30);
2548 v_rollback_allowed VARCHAR2(1);
2549 v_err_stack VARCHAR2(2000);
2550
2551 BEGIN
2552 If ( p_funcmode = 'RUN' ) THEN
2553 PA_AUTOALLOC_WF_PKG.PA_item_key := p_item_key;
2554 Set_PA_WF_Stack(p_item_type,p_item_key,'Set_PA_WF_STATUS');
2555 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2556 PA_item_key,
2557 'WF_STACK');
2558 WriteDebugMsg(v_err_stack);
2559
2560 v_gl_item_type := WF_ENGINE.GetItemAttrText( p_item_type,
2561 p_item_key,
2562 'GL_ITEM_TYPE');
2563 v_gl_item_key := WF_ENGINE.GetItemAttrText( p_item_type,
2564 p_item_key,
2565 'GL_ITEM_KEY');
2566 v_rollback_allowed := WF_ENGINE.GetItemAttrText(p_item_type,
2567 p_item_key,
2568 'ROLLBACK_ALLOWED');
2569
2570 v_result := WF_ENGINE.GetActivityAttrText( p_item_type,
2571 p_item_key,
2572 p_actid,
2573 'RESULT_CODE');
2574 G_Err_Stage := 'PA Workflow Result Code is = '||v_result;
2575 WriteDebugMsg(G_Err_Stage);
2576
2577 /** if rollback is not allowed then PA Process result cannot be ROLLBACK -
2578
2579 /** if rollback is not allowed then PA Process result cannot be ROLLBACK -
2580 ROLLBACK is overwritten with FAIL. **/
2581
2582 if v_rollback_allowed = 'N' then
2583 if v_result = 'ROLLBACK' then
2584 v_result := 'FAIL';
2585 end if;
2586 end if;
2587
2588 WF_ENGINE.SetItemAttrText( v_gl_item_type,
2589 v_gl_item_key,
2590 'PA_WF_STATUS',
2591 v_result);
2592 WriteDebugMsg('GL Attribute PA_WF_STATUS = '||v_result);
2593
2594 v_gl_block_activity := WF_ENGINE.GetItemAttrText(p_item_type,
2595 p_item_key,
2596 'GL_BLOCK_ACTIVITY');
2597 WF_ENGINE.CompleteActivity( v_gl_item_type,
2598 v_gl_item_key,
2599 v_gl_block_activity,
2600 v_result);
2601 p_result := 'COMPLETE';
2602
2603 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2604 NULL;
2605 End If;
2606
2607 END SET_PA_WF_Status;
2608 --------------------------------------------------------------------------------
2609
2610 Procedure Get_Status_and_Message(
2611 p_conc_prg_code IN Varchar2
2612 ,p_ptype IN Varchar2
2613 ,p_rollback_allowed IN Varchar2
2614 ,x_status_code OUT NOCOPY Varchar2
2615 ,x_message_name OUT NOCOPY Varchar2 ) Is
2616
2617 v_operating_mode Varchar2(2);
2618 v_err_stack Varchar2(2000);
2619 v_conc_Request_id Number;
2620 v_definition_form VARCHAR2(500);
2621 v_rule_id Number;
2622
2623 Begin
2624 Set_PA_WF_Stack(PA_item_type,PA_item_key,'Get_Status_And_Message');
2625 v_err_stack := WF_ENGINE.GetItemAttrText( PA_item_type,
2626 PA_item_key,
2627 'WF_STACK');
2628 WriteDebugMsg(v_err_stack);
2629
2630 v_operating_mode := WF_ENGINE.GetItemAttrText
2631 (PA_item_type,
2632 PA_item_key,
2633 'OPERATING_MODE');
2634 WriteDebugMsg('Attribute Operating_Mode = '||v_operating_mode);
2635
2636 WriteDebugMsg('Get_Status_and_Message: Prg_Code = '||p_Conc_Prg_Code);
2637
2638 If p_ptype = 'COMPLETE' Then
2639 If v_operating_mode = 'R' Then --Rollback mode
2640 If p_conc_prg_code = 'PAXALGAT' Then
2641 /** ?????Reversal is not a Concurrent Request. Which program Code to use ? Status Code could be set at the place where complete activity is processed ????**/
2642 --Rollback:Allocation Reversal Completed
2643 x_status_code := 'RALPC';
2644 Elsif p_conc_prg_code = 'PASDUC' Then
2645 --Rollback: Distribute Usage and Miscellaneous Cost Process Completed
2646 x_status_code := 'RDCPC';
2647 Elsif p_conc_prg_code = 'PAXACMPT' Then
2648 --Rollback: Update Project Summary Amounts Completed
2649 x_status_code := 'RUPPC';
2650 End If;
2651 Else -- Normal mode
2652 If p_conc_prg_code = 'PAXALGAT' Then
2653 --Generate Allocation Transactions Process Completed
2654 x_status_code := 'ALPC';
2655 Elsif p_conc_prg_code = 'PAXALRAT' Then
2656 --Release Allocation Transaction Process Completed
2657 x_status_code := 'RLALPC';
2658 Elsif p_conc_prg_code = 'PASDUC' Then
2659 --Distribute Usage and Miscellaneous Cost Process Completed
2660 x_status_code := 'DCPC';
2661 Elsif p_conc_prg_code = 'PAXACMPT' Then
2662 --Update Project Summary Amounts Process Completed
2663 x_status_code := 'UPPC';
2664 End If;
2665 End If;
2666 ElsIf p_ptype = 'PENDING' Then
2667 If v_operating_mode = 'R' Then --Rollback mode
2668 If p_conc_prg_code = 'PASDUC' Then
2669 --Rollback: Distribute Usage and Miscellaneous Cost Process Pending
2670 x_status_code := 'RDCPP';
2671 Elsif p_conc_prg_code = 'PAXACMPT' Then
2672 --Rollback: Update Project Summary Amounts Pending
2673 x_status_code := 'RUPPP';
2674 End If;
2675 Else -- Normal mode
2676 If p_conc_prg_code = 'PAXALGAT' Then
2677 --Generate Allocation Transactions Process Pending
2678 x_status_code := 'ALPP';
2679 Elsif p_conc_prg_code = 'PAXALRAT' Then
2680 --Release Allocation Transaction Process Pending
2681 x_status_code := 'RLALPP';
2682 Elsif p_conc_prg_code = 'PASDUC' Then
2683 --Distribute Usage and Miscellaneous Cost Process Pending
2684 x_status_code := 'DCPP';
2685 Elsif p_conc_prg_code = 'PAXACMPT' Then
2686 --Update Project Summary Amounts Process Pending
2687 x_status_code := 'UPPP';
2688 End If;
2689 End If;
2690 ElsIf p_ptype = 'ZEROERROR' Then
2691 /** Concurrent Process returned Request ID = 0 **/
2692
2693 /** Set the Message **/
2694 If p_conc_prg_code = 'PAXALGAT' Then
2695 x_message_name := 'PASDALOC:REQUEST_NOT_SUBMITTED_NRB';
2696 else
2697 if p_rollback_allowed = 'Y' then
2698 x_message_name := 'PASDALOC:REQUEST_NOT_SUBMITTED';
2699 else
2700 x_message_name := 'PASDALOC:REQUEST_NOT_SUBMITTED_NRB';
2701 end if;
2702 end if;
2703
2704 If v_operating_mode = 'R' Then --Rollback mode
2705 If p_conc_prg_code = 'PASDUC' Then
2706 --Rollback: Distribute Usage and Miscellaneous Cost Process Failed
2707 x_status_code := 'RDCPF';
2708 Elsif p_conc_prg_code = 'PAXACMPT' Then
2709 --Rollback: Update Project Summary Amounts Process Failed
2710 x_status_code := 'RUPPF';
2711 End If;
2712 Else -- Normal mode
2713 If p_conc_prg_code = 'PAXALGAT' Then
2714 --Generate Allocation Transactions Process Failed
2715 x_status_code := 'ALPF';
2716 Elsif p_conc_prg_code = 'PAXALRAT' Then
2717 --Release Allocation Transactions Process Failed
2718 x_status_code := 'RLALPF';
2719 Elsif p_conc_prg_code = 'PASDUC' Then
2720 --Distribute Usage and Miscellaneous Cost Process Failed
2721 x_status_code := 'DCPF';
2722 Elsif p_conc_prg_code = 'PAXACMPT' Then
2723 --Update Project Summary Amounts Process Completed
2724 x_status_code := 'UPPF';
2725 END IF;
2726 End If;
2727
2728 ElsIf p_ptype = 'ERROR' Then
2729
2730 v_conc_request_id := wf_engine.GetItemAttrNumber
2731 (itemtype => PA_item_type,
2732 itemkey => PA_item_key,
2733 aname => 'CONC_REQUEST_ID');
2734 /** Set the definition form attribute **/
2735
2736 v_definition_form := 'PAREQVIEW: DODT_REQ_ID='|| to_char(v_conc_request_id);
2737
2738
2739 WF_Engine.SetItemAttrText(itemtype => PA_item_type,
2740 itemkey => PA_item_key,
2741 aname => 'DEFINITION_FORM',
2742 avalue => v_definition_form);
2743
2744 If v_operating_mode = 'R' Then --Rollback mode
2745 If p_conc_prg_code = 'PASDUC' Then
2746 --Rollback: Distribute Usage and Miscellaneous Cost Process Failed
2747 x_status_code := 'RDCPF';
2748 x_message_name := 'PASDALOC:DIST_COST_PRG_FAILED';
2749 Elsif p_conc_prg_code = 'PAXACMPT' Then
2750 --Rollback: Update Project Summary Amounts Process Failed
2751 x_status_code := 'RUPPF';
2752 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_PRG_FAILED';
2753 End If;
2754 Else -- Normal mode
2755 If p_conc_prg_code = 'PAXALGAT' Then
2756 --Generate Allocation Transactions Process Failed
2757 x_status_code := 'ALPF';
2758 x_message_name := 'PASDALOC:GEN_ALLOC_PRG_FAILED';
2759 Elsif p_conc_prg_code = 'PAXALRAT' Then
2760 --Release Allocation Transactions Process Failed
2761 x_status_code := 'RLALPF';
2762 If p_rollback_allowed = 'Y' Then
2763 x_message_name := 'PASDALOC:RLS_ALLOC_PRG_FAILED';
2764 else
2765 x_message_name := 'PASDALOC:RLS_ALLOC_PRG_FAILED_NRB';
2766 End If;
2767 Elsif p_conc_prg_code = 'PASDUC' Then
2768 --Distribute Usage and Miscellaneous Cost Process Failed
2769 x_status_code := 'DCPF';
2770 If p_rollback_allowed = 'Y' Then
2771 x_message_name := 'PASDALOC:DIST_COST_PRG_FAILED';
2772 Else
2773 x_message_name := 'PASDALOC:DIST_COST_PRG_FAILED_NRB';
2774 End If;
2775 Elsif p_conc_prg_code = 'PAXACMPT' Then
2776 --Update Project Summary Amounts Process Completed
2777 x_status_code := 'UPPF';
2778 If p_rollback_allowed = 'Y' Then
2779 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_PRG_FAILED';
2780 Else
2781 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_PRG_FAILED_NRB';
2782 End If;
2783 END IF;
2784 End If;
2785 ElsIf p_ptype = 'EXCEPTION' Then
2786 v_conc_request_id := wf_engine.GetItemAttrNumber
2787 (itemtype => PA_item_type,
2788 itemkey => PA_item_key,
2789 aname => 'CONC_REQUEST_ID');
2790
2791 /* Set the view definition form sent with notification */
2792
2793 v_definition_form := 'PAREQVIEW: DODT_REQ_ID='|| to_char(v_conc_request_id);
2794
2795 WF_Engine.SetItemAttrText(itemtype => PA_item_type,
2796 itemkey => PA_item_key,
2797 aname => 'DEFINITION_FORM',
2798 avalue => v_definition_form);
2799
2800 If v_operating_mode = 'R' Then --Rollback mode
2801 If p_conc_prg_code = 'PASDUC' Then
2802 --Rollback: Distribute Usage and Miscellaneous Cost Process raised exceptions
2803 x_status_code := 'RDCPE';
2804 x_message_name := 'PASDALOC:DIST_COST_PRG_EXCEPT';
2805 Elsif p_conc_prg_code = 'PAXACMPT' Then
2806 --Rollback: Update Project Summary Amounts Process Completed
2807 x_status_code := 'RUPPE';
2808 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_EXCEPT';
2809 End If;
2810 Else -- Normal mode
2811 If p_conc_prg_code = 'PAXALGAT' Then
2812 --Generate Allocation Transactions Process raised exceptions
2813 x_status_code := 'ALPE';
2814 If p_rollback_allowed = 'Y' Then
2815 x_message_name := 'PASDALOC:GEN_ALLOC_PRG_EXCEPT';
2816 Else
2817 x_message_name := 'PASDALOC:GEN_ALLOC_PRG_EXCEPT_NRB';
2818 End If;
2819
2820 Elsif p_conc_prg_code = 'PAXALRAT' Then
2821 --Release Allocation Transactions Process raised exceptions
2822 x_status_code := 'RLALPE';
2823 If p_rollback_allowed = 'Y' Then
2824 x_message_name := 'PASDALOC:RLS_ALLOC_PRG_EXCEPT';
2825 Else
2826 x_message_name := 'PASDALOC:RLS_ALLOC_PRG_EXCEPT_NRB';
2827 End If;
2828 Elsif p_conc_prg_code = 'PASDUC' Then
2829 --Distribute Usage and Miscellaneous Cost Process raised exceptions
2830 x_status_code := 'DCPE';
2831 If p_rollback_allowed = 'Y' Then
2832 x_message_name := 'PASDALOC:DIST_COST_PRG_EXCEPT';
2833 Else
2834 x_message_name := 'PASDALOC:DIST_COST_PRG_EXCEPT_NRB';
2835 End If;
2836 Elsif p_conc_prg_code = 'PAXACMPT' Then
2837 --Update Project Summary Amounts Process raised exceptions
2838 x_status_code := 'UPPE';
2839 If p_rollback_allowed = 'Y' Then
2840 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_PRG_EXCEPT';
2841 Else
2842 x_message_name := 'PASDALOC:UPDT_PROJ_SUM_PRG_EXCEPT_NRB';
2843 End If;
2844 END IF;
2845 End If;
2846 End If;
2847
2848 WriteDebugMsg('Message_name = '||x_message_name||' Status_Code = '||x_status_code);
2849 G_Err_Stage := 'End of API: Get_Status_And_Message';
2850 Set_PA_WF_Stage(PA_item_type,PA_item_key,G_Err_Stage);
2851 WriteDebugMsg(G_Err_Stage);
2852 Reset_PA_WF_Stack(PA_item_type,PA_item_key);
2853 return;
2854
2855 End Get_Status_and_Message;
2856 --------------------------------------------------------------------------------
2857 -- ****************************************************************************
2858 -- This procedure opens debug file for appending
2859 -- ****************************************************************************
2860
2861 Procedure initialize_debug IS
2862
2863 v_err_stack VARCHAR2(2000);
2864 Begin
2865 v_err_stack := 'Initialize_Debug';
2866 Set_PA_WF_Stack(PA_Item_Type,PA_Item_Key,v_err_stack);
2867 G_FILE := 'PA'||PA_item_key ||'.dbg';
2868 G_Err_Stage := 'Debug File IS '||G_FILE;
2869 If utl_file.Is_Open(G_FILE_PTR) Then
2870 utl_file.fclose(G_FILE_PTR);
2871 End If;
2872 G_DIR := wf_engine.GetItemAttrText( itemtype => PA_item_type,
2873 itemkey => PA_item_key,
2874 aname => 'DEBUG_FILE_DIR');
2875 G_FILE_PTR := utl_file.fopen(G_DIR,G_FILE,'a');
2876 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2877 G_Err_Stage := 'Exitting Initialize_Debug';
2878 --WriteDebugMsg(G_Err_Stage);
2879
2880 Exception
2881
2882 WHEN UTL_FILE.INVALID_PATH THEN
2883 v_err_stack := wf_engine.GetItemAttrText
2884 ( itemtype => PA_item_type,
2885 itemkey => PA_item_key,
2886 aname => 'WF_STACK');
2887
2888 raise_application_error(-20020,'INVALID PATH exception from UTL_FILE !!'
2889 || v_err_stack||'-'||G_Err_Stage);
2890 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2891
2892 WHEN UTL_FILE.INVALID_MODE THEN
2893 v_err_stack := wf_engine.GetItemAttrText
2894 ( itemtype => PA_item_type,
2895 itemkey => PA_item_key,
2896 aname => 'WF_STACK');
2897 raise_application_error(-20020,'INVALID MODE exception from UTL_FILE !!'
2898 || v_Err_Stack ||' - '||G_Err_Stage);
2899 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2900
2901 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
2902 v_err_stack := wf_engine.GetItemAttrText
2903 ( itemtype => PA_item_type,
2904 itemkey => PA_item_key,
2905 aname => 'WF_STACK');
2906 raise_application_error(-20020,'INVALID FILEHANDLE exception from UTL_FIL
2907 E !!'
2908 || G_Err_Stack ||' - '||G_Err_Stage);
2909 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2910
2911 WHEN UTL_FILE.INVALID_OPERATION THEN
2912 v_err_stack := wf_engine.GetItemAttrText
2913 ( itemtype => PA_item_type,
2914 itemkey => PA_item_key,
2915 aname => 'WF_STACK');
2916 raise_application_error(-20020,'INVALID OPERATION exception from UTL_FILE
2917 !!'
2918 || v_Err_Stack ||' - '||G_Err_Stage);
2919 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2920
2921 WHEN UTL_FILE.READ_ERROR THEN
2922 v_err_stack := wf_engine.GetItemAttrText
2923 ( itemtype => PA_item_type,
2924 itemkey => PA_item_key,
2925 aname => 'WF_STACK');
2926 raise_application_error(-20020,'READ ERROR exception from UTL_FILE !!'
2927 || v_Err_Stack ||' - '||G_Err_Stage);
2928 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2929
2930 WHEN UTL_FILE.WRITE_ERROR THEN
2931 v_err_stack := wf_engine.GetItemAttrText
2932 ( itemtype => PA_item_type,
2933 itemkey => PA_item_key,
2934 aname => 'WF_STACK');
2935 raise_application_error(-20020,'WRITE ERROR exception from UTL_FILE !!'
2936 || v_Err_Stack ||' - '||G_Err_Stage);
2937 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2938
2939 WHEN UTL_FILE.INTERNAL_ERROR THEN
2940 v_err_stack := wf_engine.GetItemAttrText
2941 ( itemtype => PA_item_type,
2942 itemkey => PA_item_key,
2943 aname => 'WF_STACK');
2944 raise_application_error(-20020,'INTERNAL ERROR exception from UTL_FILE !!
2945 '
2946 || v_Err_Stack ||' - '||G_Err_Stage);
2947 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2948
2949 When Others Then
2950 v_err_stack := wf_engine.GetItemAttrText
2951 ( itemtype => PA_item_type,
2952 itemkey => PA_item_key,
2953 aname => 'WF_STACK');
2954 /* dbms_output.put_line('In when-others of initialize_debug'); */
2955 /* dbms_output.put_line(SQLERRM); */
2956 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
2957 'initialize_debug', 'PASDALOC', PA_item_key);
2958 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
2959 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2960 Raise;
2961 End initialize_debug;
2962 -------------------------------------------------------------------------------------
2963 -- This procedure writes debug messages to a file
2964
2965 Procedure WriteDebugMsg(debug_message in Varchar2) Is
2966 v_err_stack Varchar2(2000);
2967
2968 Begin
2969 v_err_stack := 'WriteDebugMsg';
2970 Set_PA_WF_Stack (PA_Item_Type,PA_Item_Key,v_err_stack);
2971
2972 If (DebugFlag) then
2973 If debug_message is not null then
2974 If NOT utl_file.Is_Open(G_FILE_PTR) OR
2975 G_FILE <> 'PA'||PA_item_key ||'.dbg' OR
2976 G_FILE IS NULL Then
2977 G_Err_Stage := 'Calling Initialize Debug from WriteDebugMsg';
2978 /* dbms_output.put_line(G_Err_Stage); */
2979 initialize_debug;
2980 End If;
2981 utl_file.put_line(G_FILE_PTR, debug_message);
2982 utl_file.fflush(G_FILE_PTR);
2983 End if;
2984 End If;
2985 Reset_PA_WF_Stack (PA_Item_Type,PA_Item_Key);
2986
2987 Exception
2988
2989 WHEN UTL_FILE.INVALID_PATH THEN
2990 v_err_stack := wf_engine.GetItemAttrText
2991 ( itemtype => PA_item_type,
2992 itemkey => PA_item_key,
2993 aname => 'WF_STACK');
2994
2995 raise_application_error(-20020,'INVALID PATH exception from UTL_FILE !!'
2996 || v_err_stack||'-'||G_Err_Stage);
2997 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
2998
2999 WHEN UTL_FILE.INVALID_MODE THEN
3000 v_err_stack := wf_engine.GetItemAttrText
3001 ( itemtype => PA_item_type,
3002 itemkey => PA_item_key,
3003 aname => 'WF_STACK');
3004 raise_application_error(-20020,'INVALID MODE exception from UTL_FILE !!'
3005 || v_Err_Stack ||' - '||G_Err_Stage);
3006 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3007
3008 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3009 v_err_stack := wf_engine.GetItemAttrText
3010 ( itemtype => PA_item_type,
3011 itemkey => PA_item_key,
3012 aname => 'WF_STACK');
3013 raise_application_error(-20020,
3014 'INVALID FILEHANDLE exception from UTL_FIL E !!'
3015 || v_Err_Stack ||' - '||G_Err_Stage);
3016 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3017
3018 WHEN UTL_FILE.INVALID_OPERATION THEN
3019 v_err_stack := wf_engine.GetItemAttrText
3020 ( itemtype => PA_item_type,
3021 itemkey => PA_item_key,
3022 aname => 'WF_STACK');
3023 raise_application_error(-20020,
3024 'INVALID OPERATION exception from UTL_FILE !!'
3025 || v_Err_Stack ||' - '||G_Err_Stage);
3026 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3027
3028 WHEN UTL_FILE.READ_ERROR THEN
3029 v_err_stack := wf_engine.GetItemAttrText
3030 ( itemtype => PA_item_type,
3031 itemkey => PA_item_key,
3032 aname => 'WF_STACK');
3033 raise_application_error(-20020,'READ ERROR exception from UTL_FILE !!'
3034 || v_Err_Stack ||' - '||G_Err_Stage);
3035 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3036
3037 WHEN UTL_FILE.WRITE_ERROR THEN
3038 v_err_stack := wf_engine.GetItemAttrText
3039 ( itemtype => PA_item_type,
3040 itemkey => PA_item_key,
3041 aname => 'WF_STACK');
3042 raise_application_error(-20020,'WRITE ERROR exception from UTL_FILE !!'
3043 || v_Err_Stack ||' - '||G_Err_Stage);
3044 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3045
3046 WHEN UTL_FILE.INTERNAL_ERROR THEN
3047 v_err_stack := wf_engine.GetItemAttrText
3048 ( itemtype => PA_item_type,
3049 itemkey => PA_item_key,
3050 aname => 'WF_STACK');
3051 raise_application_error(-20020,
3052 'INTERNAL ERROR exception from UTL_FILE !! '
3053 || v_Err_Stack ||' - '||G_Err_Stage);
3054 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3055
3056 When Others Then
3057 v_err_stack := wf_engine.GetItemAttrText
3058 ( itemtype => PA_item_type,
3059 itemkey => PA_item_key,
3060 aname => 'WF_STACK');
3061 /* dbms_output.put_line(SQLERRM); */
3062 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
3063 'WriteDebugMsg', 'PASDALOC', PA_item_key,v_err_stack);
3064 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
3065 Reset_PA_WF_Stack(PA_Item_Type,PA_Item_Key);
3066 Raise;
3067
3068 End WriteDebugMsg;
3069 --------------------------------------------------------------------------------
3070 /* This procedure initializes WF_STACK with the argument passed
3071 to it */
3072 Procedure Init_PA_WF_STACK (p_item_type In Varchar2,
3073 p_item_key In Varchar2,
3074 p_err_stack In Varchar2)
3075 IS
3076 Begin
3077
3078 G_Err_Stage := 'Inside Init_PA_WF_STACK';
3079
3080 /* Append the stack with the new string */
3081 WF_Engine.SetItemAttrText(itemtype => p_item_type,
3082 itemkey => p_item_key,
3083 aname => 'WF_STACK',
3084 avalue => p_err_stack);
3085 Exception
3086
3087 WHEN OTHERS THEN
3088 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
3089 'Init_PA_WF_Stack', 'PASDALOC', PA_item_key,G_Err_Stage);
3090 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
3091 Raise;
3092
3093
3094 End Init_PA_WF_Stack;
3095 --------------------------------------------------------------------------------
3096 /* This procedure sets WF_STACK attribute with an argument */
3097
3098 Procedure Set_PA_WF_STACK (p_item_type In Varchar2,
3099 p_item_key In Varchar2,
3100 p_err_stack In Varchar2)
3101 IS
3102 v_err_stack Varchar2(2000);
3103 Begin
3104 /* Get the previous value of the attribute */
3105 v_err_stack := WF_Engine.GetItemAttrText(p_item_type,
3106 p_item_key,
3107 'WF_STACK');
3108
3109 /* Append the stack with the new string */
3110 WF_Engine.SetItemAttrText(itemtype => p_item_type,
3111 itemkey => p_item_key,
3112 aname => 'WF_STACK',
3113 avalue => v_err_stack||'=>'||p_err_stack);
3114
3115 Exception
3116
3117 WHEN OTHERS THEN
3118 Wf_Core.Context('PA_AUTOALLOC_WF_PKG',
3119 'Set_PA_WF_Stack', 'PASDALOC', PA_item_key,G_Err_Stage);
3120 Wf_Core.Get_Error(G_err_name,G_err_msg,G_err_stack);
3121 Raise;
3122
3123 End Set_PA_WF_Stack;
3124 --------------------------------------------------------------------------------
3125 /* This procedure resets WF_STACK attribute.It just removes last string from the stack */
3126
3127 Procedure Reset_PA_WF_STACK (p_item_type In Varchar2,
3128 p_item_key In Varchar2)
3129 IS
3130 v_err_stack Varchar2(2000);
3131 v_reset_stack Varchar2(2000);
3132 Begin
3133 /* Get the previous value of the attribute */
3134 v_err_stack := WF_Engine.GetItemAttrText(p_item_type,
3135 p_item_key,
3136 'WF_STACK');
3137 v_reset_stack := Substr(v_err_stack,1,
3138 instr(v_err_stack,'=>',-1,1)-1);
3139
3140 /* Remove the most recently entered string from the stack */
3141 WF_Engine.SetItemAttrText(itemtype => p_item_type,
3142 itemkey => p_item_key,
3143 aname => 'WF_STACK',
3144 avalue => v_reset_stack);
3145
3146 End Reset_PA_WF_Stack;
3147 --------------------------------------------------------------------------------
3148 /* Set the WF_STAGE attribute with an argument */
3149 Procedure Set_PA_WF_Stage (p_item_type In Varchar2,
3150 p_item_key In Varchar2,
3151 p_err_stage In Varchar2)
3152 IS
3153 v_err_stage Varchar2(2000);
3154 Begin
3155
3156 WF_Engine.SetItemAttrText(itemtype => p_item_type,
3157 itemkey => p_item_key,
3158 aname => 'WF_STAGE',
3159 avalue => p_err_stage);
3160
3161 End Set_PA_WF_Stage;
3162 --------------------------------------------------------------------------------
3163 Function DebugFlag
3164 Return BOOLEAN
3165 IS
3166 v_debug_mode Varchar2(2);
3167 BEGIN
3168 v_debug_mode := wf_engine.GetItemAttrText
3169 ( itemtype => PA_item_type,
3170 itemkey => PA_item_key,
3171 aname => 'DEBUG_MODE');
3172
3173 IF v_debug_mode = 'Y' THEN
3174 Return TRUE;
3175 ELSE
3176 Return FALSE;
3177 END IF;
3178
3179 End DebugFlag;
3180 -------------------------------------------------------------------------------
3181
3182 -- Created this function for bug 5218394. This function will reterieve the debug
3183 -- directory location using utl_log_dir
3184 --------------------------------------------------------------------------------
3185 Function GetDebugLogDir
3186 Return VARCHAR2
3187 IS
3188
3189 TEMP_UTL VARCHAR2(512);
3190 TEMP_DIR VARCHAR2(255);
3191
3192 BEGIN
3193 -- use first entry of utl_file_dir as the TEMP_DIR
3194 -- if there is no entry then do not even construct file names
3195 SELECT TRANSLATE(LTRIM(value),',',' ')
3196 INTO TEMP_UTL
3197 FROM v$parameter
3198 WHERE name = 'utl_file_dir';
3199
3200 IF (INSTR(TEMP_UTL,' ') > 0 AND TEMP_UTL IS NOT NULL) THEN
3201 SELECT SUBSTRB(TEMP_UTL, 1, INSTR(TEMP_UTL,' ') - 1)
3202 INTO TEMP_DIR
3203 FROM dual ;
3204 ELSIF (TEMP_UTL IS NOT NULL) THEN
3205 TEMP_DIR := TEMP_UTL;
3206 END IF;
3207
3208 IF (TEMP_UTL IS NULL or TEMP_DIR IS NULL ) THEN
3209 TEMP_DIR := '/sqlcom/log';
3210 END IF;
3211
3212 RETURN TEMP_DIR;
3213 End GetDebugLogDir;
3214 -------------------------------------------------------------------------------
3215 End PA_AUTOALLOC_WF_PKG;
3216