[Home] [Help]
PACKAGE BODY: APPS.GL_AUTO_ALLOC_WF_PKG
Source
1 PACKAGE BODY GL_AUTO_ALLOC_WF_PKG AS
2 /* $Header: glwfalcb.pls 120.15.12010000.2 2009/01/30 08:46:12 akhanapu ship $ */
3
4
5
6 PROCEDURE Start_AutoAllocation_Workflow( p_request_Id IN NUMBER) IS
7 Begin
8 -- generate item key
9 GL_AUTO_ALLOC_WF_PKG.p_item_key := to_char(p_request_Id);
10 If (diagn_debug_msg_flag) AND
11 G_DIR is NOT NULL then
12 initialize_debug;
13 -- dbms_output.put_line('Log Directory:='||G_DIR);
14 -- dbms_output.put_line('Log File:='||G_FILE);
15 End If;
16
17 Create_And_Start_Wf(p_request_Id);
18
19 EXCEPTION
20 WHEN OTHERS THEN
21 Wf_Core.Context('GL_AUTO_ALLOCATION_WF_PKG',
22 'Start_AutoAllocation_Workflow', p_item_type, p_item_key);
23 Wf_Core.Get_Error(err_name,err_msg,err_stack);
24 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
25 diagn_debug_msg('Start_AutoAllocation_Workflow: ' || err_msg ||'*'||err_stack);
26 END IF;
27 Raise;
28
29 End Start_AutoAllocation_Workflow;
30
31
32 PROCEDURE Create_And_Start_Wf( p_request_Id IN NUMBER
33 ) IS
34
35 -- Local variables
36 l_ALLOCATION_SET_ID Number;
37 l_ALLOCATION_SET_NAME Varchar2(40);
38 l_OWNER Varchar2(100);
39 l_ACCESS_SET_ID Number;
40 l_LEDGER_ID Number;
41 l_LEDGER_CURRENCY Varchar2(15);
42 l_PERIOD_NAME Varchar2(15);
43 l_BUDGET_VERSION_ID Number;
44 l_BALANCING_SEGMENT_VALUE Varchar2(25);
45 l_JOURNAL_EFFECTIVE_DATE Date;
46 l_CALCULATION_EFFECTIVE_DATE Date;
47 l_USAGE_CODE Varchar2(1);
48 l_GL_PERIOD_NAME Varchar2(15);
49 l_PA_PERIOD_NAME Varchar2(15);
50 l_EXPENDITURE_ITEM_DATE Date;
51 l_LAST_UPDATE_LOGIN Number;
52 l_CREATED_BY Number;
53 l_LAST_UPDATED_BY Number;
54 l_CHART_OF_ACCOUNTS_ID NUMBER;
55 l_monitor_url VARCHAR2(500);
56 l_rollback_allowed VARCHAR2(1);
57 l_resp_id NUMBER;
58 l_user_id NUMBER;
59 l_org_id NUMBER;
60 l_resp_appl_id NUMBER;
61 l_business_group_id NUMBER;
62 l_gl_allow_preparer_approval Varchar2(30);
63 l_continue_next_step Varchar2(1);
64 l_value fnd_profile_option_values.profile_option_value%TYPE;
65 l_WorkFlow_Launch Boolean;
66 fnd_user_name Varchar2(100);
67 Cursor c_set_name IS
68 Select
69 ALLOCATION_SET_ID
70 ,ALLOCATION_SET_NAME
71 ,OWNER
72 ,ACCESS_SET_ID
73 ,LEDGER_ID
74 ,LEDGER_CURRENCY
75 ,PERIOD_NAME
76 ,BUDGET_VERSION_ID
77 ,BALANCING_SEGMENT_VALUE
78 ,JOURNAL_EFFECTIVE_DATE
79 ,CALCULATION_EFFECTIVE_DATE
80 ,USAGE_CODE
81 ,GL_PERIOD_NAME
82 ,PA_PERIOD_NAME
83 ,EXPENDITURE_ITEM_DATE
84 ,CREATED_BY
85 ,LAST_UPDATED_BY
86 ,LAST_UPDATE_LOGIN
87 ,ORG_ID
88 From GL_AUTO_ALLOC_SET_HISTORY
89 Where REQUEST_ID = p_request_Id;
90
91
92 Cursor f_user_name IS
93 SELECT user_name
94 FROM fnd_user
95 WHERE user_id = l_user_id;
96
97 Cursor coa_id IS
98 SELECT chart_of_accounts_id
99 FROM GL_ACCESS_SETS
100 WHERE access_set_id = l_access_set_id;
101
102 Begin
103 Open c_set_name;
104 Fetch c_set_name into
105 l_ALLOCATION_SET_ID
106 ,l_ALLOCATION_SET_NAME
107 ,l_OWNER
108 ,l_ACCESS_SET_ID
109 ,l_LEDGER_ID
110 ,l_LEDGER_CURRENCY
111 ,l_PERIOD_NAME
112 ,l_BUDGET_VERSION_ID
113 ,l_BALANCING_SEGMENT_VALUE
114 ,l_JOURNAL_EFFECTIVE_DATE
115 ,l_CALCULATION_EFFECTIVE_DATE
116 ,l_USAGE_CODE
117 ,l_GL_PERIOD_NAME
118 ,l_PA_PERIOD_NAME
119 ,l_EXPENDITURE_ITEM_DATE
120 ,l_CREATED_BY
121 ,l_LAST_UPDATED_BY
122 ,l_LAST_UPDATE_LOGIN
123 ,l_org_id;
124
125 Close c_set_name;
126
127
128 If l_allocation_set_id IS NULL Then
129 l_WorkFlow_Launch := FALSE;
130 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
131 diagn_debug_msg('Create_And_Start_Wf: ' || 'Fatal error:No Allocation set='||to_char(p_request_Id));
132 END IF;
133 End If;
134
135
136 If contain_Projects(p_item_key) Then
137 If Not GL_PA_AUTOALLOC_PKG.valid_run_period(
138 l_ALLOCATION_SET_ID
139 ,l_PA_PERIOD_NAME
140 ,l_GL_PERIOD_NAME) Then
141 l_WorkFlow_Launch := FALSE;
142 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
143 diagn_debug_msg('Create_And_Start_Wf: ' || 'Workflow not started as required PA or GL period is not specified');
144 END IF;
145 Else
146 l_WorkFlow_Launch := TRUE;
147 End If;
148
149 l_rollback_allowed := 'N' ;
150 Else
151 l_WorkFlow_Launch := TRUE;
152 FND_PROFILE.GET('GL_AUTO_ALLOC_ROLLBACK_ALLOWED', l_value);
153 If l_value = 'Y' Then
154 l_rollback_allowed := 'Y';
155 Else
156 l_rollback_allowed := 'N';
157 End If;
158
159 End If;
160
161 If (l_WorkFlow_Launch ) Then
162 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
163 diagn_debug_msg('Create_And_Start_Wf: ' || 'Executing Start_Approval_Workflow for request_id '||
164 to_char(p_request_Id));
165 END IF;
166 wf_engine.CreateProcess( itemtype => p_item_type,
167 itemkey => p_item_key,
168 process => 'GL_SD_ALLOCATION_PROCESS' );
169
170 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
171 diagn_debug_msg('Create_And_Start_Wf: ' || 'Process for GL_SD_ALLOCATION_PROCESS created');
172 END IF;
173
174 OPEN coa_id;
175 Fetch coa_id into l_chart_of_accounts_id;
176 CLOSE coa_id;
177
178 -- Set item user key
179 wf_engine.SetItemUserKey( itemtype => p_item_type,
180 itemkey => p_item_key,
181 userkey => l_allocation_set_name );
182
183
184 wf_engine.SetItemAttrText(itemtype => p_item_type,
185 itemkey => p_item_key,
186 aname => 'SET_NAME',
187 avalue => l_allocation_set_name );
188
189 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
190 itemkey => p_item_key,
191 aname => 'SET_REQ_ID',
192 avalue => p_request_Id );
193
194 FND_PROFILE.GET('USER_ID', l_user_id);
195 if(l_org_id is null) then
196 FND_PROFILE.GET('ORG_ID', l_org_id);
197 end if;
198 FND_PROFILE.GET('RESP_ID', l_resp_id);
199 FND_PROFILE.GET('RESP_APPL_ID', l_resp_appl_id);
200 FND_PROFILE.GET('PER_BUSINESS_GROUP_ID', l_business_group_id);
201 FND_PROFILE.GET('GL_ALLOW_PREPARER_APPROVAL', l_gl_allow_preparer_approval);
202
203 -- Get AOL user name
204 Open f_user_name;
205 fetch f_user_name into fnd_user_name;
206 close f_user_name;
207
208 -- Set the process owner
209 wf_engine.SetItemOwner( itemtype => p_item_type,
210 itemkey => p_item_key,
211 owner => fnd_user_name );
212
213 FND_PROFILE.GET('GL_JRNL_REVW_REQUIRED', l_value);
214 wf_engine.SetItemAttrText( itemtype => p_item_type,
215 itemkey => p_item_key,
216 aname => 'GL_JRNL_REVW_REQUIRED',
217 avalue => l_value );
218
219 -- Bug 2043415
220 FND_PROFILE.GET('GL_AUTO_ALLOC_CONTINUE_NEXT_STEP', l_value);
221
222 If l_value = 'Y' Then
223 l_continue_next_step := 'Y';
224 Else
225 l_continue_next_step := 'N';
226 End If;
227
228 wf_engine.SetItemAttrText( itemtype => p_item_type,
229 itemkey => p_item_key,
230 aname => 'CONTINUE_NEXT_STEP',
231 avalue => l_continue_next_step);
232
233 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
234 itemkey => p_item_key,
235 aname => 'USER_ID',
236 avalue => l_user_id );
237
238 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
239 itemkey => p_item_key,
240 aname => 'ORG_ID',
241 avalue => l_org_id );
242
243 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
244 itemkey => p_item_key,
245 aname => 'RESP_ID',
246 avalue => l_resp_id );
247
248 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
249 itemkey => p_item_key,
250 aname => 'BUSINESS_GROUP_ID',
251 avalue => l_business_group_id );
252
253 wf_engine.SetItemAttrText( itemtype => p_item_type,
254 itemkey => p_item_key,
255 aname => 'GL_ALLOW_PREPARER_APPROVAL',
256 avalue => l_gl_allow_preparer_approval );
257
258 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
259 itemkey => p_item_key,
260 aname => 'RESP_APPL_ID',
261 avalue => l_resp_appl_id );
262
263
264 -- Get the monitor URL
265 begin
266 l_monitor_url :=
267 wf_monitor.GetUrl(wf_core.translate('WF_WEB_AGENT'),
268 p_item_type, p_item_key,'YES');
269 Exception
270 When others then
271 l_monitor_url := 'Invalid URL';
272 end;
273 wf_engine.SetItemAttrText( itemtype => p_item_type,
274 itemkey => p_item_key,
275 aname => 'MONITOR_URL',
276 avalue => l_monitor_url);
277 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
278 diagn_debug_msg('Create_And_Start_Wf: ' || 'm_url='||l_monitor_url);
279 END IF;
280 UPDATE GL_AUTO_ALLOC_SET_HISTORY
281 SET MONITOR_URL = l_monitor_url
282 Where Request_Id = to_number(p_item_key);
283
284
285 wf_engine.SetItemAttrText( itemtype => p_item_type,
286 itemkey => p_item_key,
287 aname => 'ROLLBACK_ALLOWED',
288 avalue => l_rollback_allowed);
289
290 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
291 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute Roolback_Allowed = ' ||l_rollback_allowed);
292 END IF;
293
294 --wf_engine.SetItemAttrText( itemtype => p_item_type,
295 -- itemkey => p_item_key,
296 -- aname => 'BUD_CONTROL_FLAG',
297 -- avalue => l_ENABLE_BUDGETARY_CON_FLAG);
298
299 --wf_engine.SetItemAttrText( itemtype => p_item_type,
300 -- itemkey => p_item_key,
301 -- aname => 'AUTOMATIC_TAX_FLAG',
302 -- avalue => l_ENABLE_AUTOMATIC_TAX_FLAG);
303
304 --wf_engine.SetItemAttrNumber( itemtype => p_item_type,
305 -- itemkey => p_item_key,
306 -- aname => 'LATEST_ENCUMBRANCE_YEAR',
307 -- avalue => l_LATEST_ENCUMBRANCE_YEAR);
308
309
310 wf_engine.SetItemAttrText( itemtype => p_item_type,
311 itemkey => p_item_key,
312 aname => 'STEP_CONTACT',
313 avalue => l_OWNER);
314
315 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
316 itemkey => p_item_key,
317 aname => 'LEDGER_ID',
318 avalue => l_LEDGER_ID);
319
320 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
321 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute LEDGER_ID = ' || to_char(l_LEDGER_ID));
322 END IF;
323
324 wf_engine.SetItemAttrText( itemtype => p_item_type,
325 itemkey => p_item_key,
326 aname => 'LEDGER_CURRENCY',
327 avalue => l_LEDGER_CURRENCY);
328
329 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
330 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute LEDGER_CURRENCY = ' || l_LEDGER_CURRENCY);
331 END IF;
332
333 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
334 itemkey => p_item_key,
335 aname => 'ACCESS_SET_ID',
336 avalue => l_ACCESS_SET_ID);
337
338 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
339 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute ACCESS_SET_ID = ' || to_char(l_ACCESS_SET_ID));
340 END IF;
341
342 wf_engine.SetItemAttrText( itemtype => p_item_type,
343 itemkey => p_item_key,
344 aname => 'BALANCING_SEGMENT_VALUE',
345 avalue => l_BALANCING_SEGMENT_VALUE);
346
347 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
348 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute BALANCING_SEGMENT_VALUE = ' || l_BALANCING_SEGMENT_VALUE);
349 END IF;
350
351 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
352 itemkey => p_item_key,
353 aname => 'CHART_OF_ACCOUNTS_ID',
354 avalue => l_CHART_OF_ACCOUNTS_ID);
355
356 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
357 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute CHART_OF_ACCOUNTS_ID = ' || to_char(l_CHART_OF_ACCOUNTS_ID));
358 END IF;
359
360 wf_engine.SetItemAttrText( itemtype => p_item_type,
361 itemkey => p_item_key,
362 aname => 'PERIOD_NAME',
363 avalue => l_PERIOD_NAME);
364 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
365 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute PERIOD_NAME = ' ||l_PERIOD_NAME);
366 END IF;
367
368 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
369 itemkey => p_item_key,
370 aname => 'BUDGET_VERSION_ID',
371 avalue => l_BUDGET_VERSION_ID);
372
373 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
374 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute BUDGET_VERSION_ID = ' ||
375 to_char(l_BUDGET_VERSION_ID));
376 END IF;
377
378 wf_engine.SetItemAttrDate( itemtype => p_item_type,
379 itemkey => p_item_key,
380 aname => 'JOURNAL_EFFECTIVE_DATE',
381 avalue => l_JOURNAL_EFFECTIVE_DATE);
382 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
383 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute JOURNAL_EFFECTIVE_DATE = ' ||
384 to_char(l_JOURNAL_EFFECTIVE_DATE));
385 END IF;
386
387 wf_engine.SetItemAttrDate( itemtype => p_item_type,
388 itemkey => p_item_key,
389 aname => 'CALCULATION_EFFECTIVE_DATE',
390 avalue => l_CALCULATION_EFFECTIVE_DATE);
391 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
392 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute CALCULATION_EFFECTIVE_DATE = ' ||
393 to_char(l_CALCULATION_EFFECTIVE_DATE));
394 END IF;
395
396 wf_engine.SetItemAttrDate( itemtype => p_item_type,
397 itemkey => p_item_key,
398 aname => 'EXPENDITURE_ITEM_DATE',
399 avalue => l_EXPENDITURE_ITEM_DATE);
400 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
401 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute EXPENDITURE_ITEM_DATE = ' ||
402 to_char(l_EXPENDITURE_ITEM_DATE));
403 END IF;
404
405 wf_engine.SetItemAttrText( itemtype => p_item_type,
406 itemkey => p_item_key,
407 aname => 'USAGE_CODE',
408 avalue => l_USAGE_CODE);
409 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
410 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute USAGE_CODE = ' ||l_USAGE_CODE);
411 END IF;
412
413 wf_engine.SetItemAttrText( itemtype => p_item_type,
414 itemkey => p_item_key,
415 aname => 'GL_PERIOD_NAME',
416 avalue => l_GL_PERIOD_NAME);
417 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
418 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute GL_PERIOD_NAME = ' ||l_GL_PERIOD_NAME);
419 END IF;
420
421
422 wf_engine.SetItemAttrText( itemtype => p_item_type,
423 itemkey => p_item_key,
424 aname => 'PA_PERIOD_NAME',
425 avalue => l_PA_PERIOD_NAME);
426 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
427 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute PA_PERIOD_NAME = ' ||l_PA_PERIOD_NAME);
428 END IF;
429
430 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
431 itemkey => p_item_key,
432 aname => 'CREATED_BY',
433 avalue => l_CREATED_BY);
434 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
435 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute CREATED_BY = ' ||to_char(l_CREATED_BY));
436 END IF;
437
438 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
439 itemkey => p_item_key,
440 aname => 'LAST_UPDATED_BY',
441 avalue => l_LAST_UPDATED_BY);
442 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
443 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute CREATED_BY = ' ||to_char(l_LAST_UPDATED_BY));
444 END IF;
445
446
447 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
448 itemkey => p_item_key,
449 aname => 'LAST_UPDATE_LOGIN',
450 avalue => l_LAST_UPDATE_LOGIN);
451 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
452 diagn_debug_msg('Create_And_Start_Wf: ' || 'Attribute LAST_UPDATE_LOGIN = ' ||to_char(l_LAST_UPDATE_LOGIN));
453 diagn_debug_msg('Create_And_Start_Wf: ' || 'Process GL_SD_ALLOCATION_PROCESS starting');
454 END IF;
455 wf_engine.StartProcess( itemtype => p_item_type,
456 itemkey => p_item_key );
457
458
459 End If;
460 EXCEPTION
461 WHEN OTHERS THEN
462 Wf_Core.Context('GL_AUTO_ALLOCATION_WF_PKG',
463 'set_wf_variables', p_item_type, p_item_key);
464 Wf_Core.Get_Error(err_name,err_msg,err_stack);
465 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
466 diagn_debug_msg('Create_And_Start_Wf: ' || err_msg ||'*'||err_stack);
467 END IF;
468 Raise;
469 End Create_And_Start_Wf;
470
471 procedure Next_Step_Type(p_item_type IN VARCHAR2,
472 p_item_key IN VARCHAR2,
473 p_actid IN NUMBER,
474 p_funcmode IN VARCHAR2,
475 p_result OUT NOCOPY VARCHAR2) Is
476
477 f_step_number NUMBER;
478 f_batch_id NUMBER;
479 f_batch_type_code VARCHAR2(1);
480 f_allocation_method_code VARCHAR2(1);
481 f_owner VARCHAR2(100);
482 f_batch_name VARCHAR2(100);
483
484
485 Cursor step_detail ( l_step_number NUMBER) IS
486 Select
487 STEP_NUMBER
488 , BATCH_ID
489 , BATCH_TYPE_CODE
490 , ALLOCATION_METHOD_CODE
491 , OWNER
492 From GL_AUTO_ALLOC_BATCH_HISTORY
493 Where REQUEST_ID = to_number(p_item_key)
494 AND Step_number > l_step_number
495 Order by Step_number ASC;
496
497
498 l_step Number;
499 l_gen_batch_id Number;
500 l_batch_id Number;
501 l_status Varchar2(1);
502 l_batch_type_code Varchar2(2);
503 l_fail_flag Varchar2(1) := 'N';
504 l_batch_generated Varchar2(1) := 'N';
505 l_fail_batches Varchar2(2000);
506
507 Cursor get_je_batch_status_C IS
508 Select jb.status, jb.je_batch_id
509 FROM GL_JE_BATCHES jb,
510 GL_AUTO_ALLOC_BATCH_HISTORY bh
511 WHERE bh.request_id = to_number(p_item_key)
512 AND bh.step_number = l_step
513 AND jb.je_batch_id = bh.generated_je_batch_id;
514
515 Begin
516
517 If p_funcmode = 'RUN' THEN
518 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
519 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
520 diagn_debug_msg('Executing: Next_Step_Type');
521 END IF;
522 l_step := WF_ENGINE.GetItemAttrNumber
523 (p_item_type,
524 p_item_key,
525 'STEP_NUMBER');
526
527 -- verify that previous step is completed
528 If l_step Is Not Null AND
529 l_step <> 0 Then
530 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
531 diagn_debug_msg('Next_Step_Type: ' || 'Previous step = '||To_Char(l_step));
532 END IF;
533 l_batch_id := WF_ENGINE.GetItemAttrNumber
534 (p_item_type,
535 p_item_key,
536 'BATCH_ID');
537
538 l_batch_type_code := WF_ENGINE.GetItemAttrText
539 (p_item_type,
540 p_item_key,
541 'BATCH_TYPE_CODE');
542
543 --Verify that batch is posted i.e. present step is completed before fetching next step
544 OPEN get_je_batch_status_C;
545 LOOP
546 fetch get_je_batch_status_C into l_status, l_gen_batch_id;
547 EXIT WHEN get_je_batch_status_C%NOTFOUND;
548
549 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
550 diagn_debug_msg('Next_Step_Type: ' || 'Gen_Batch_Id = '||to_char(l_gen_batch_id)|| ' Code = '||l_batch_type_code);
551 END IF;
552
553 IF l_gen_batch_id IS NOT NULL THEN
554
555 l_batch_generated := 'Y';
556
557 If l_status = 'P' Then
558 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
559 diagn_debug_msg('Next_Step_Type: ' || 'Batch ' ||to_char(l_gen_batch_id) ||' is posted');
560 END IF;
561 ELSE
562 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
563 diagn_debug_msg('Next_Step_Type: ' || 'Batch ' ||to_char(l_gen_batch_id) ||' is not posted');
564 END IF;
565 l_fail_flag := 'Y';
566 l_fail_batches := l_fail_batches || '*' || to_char(l_gen_batch_id);
567 END IF;
568 END IF;
569
570 END LOOP;
571 close get_je_batch_status_C;
572
573
574 IF l_batch_generated = 'N' THEN
575 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
576 diagn_debug_msg('Next_Step_Type: ' || 'Allocation Batch '||to_char(l_batch_id)||' is not generated');
577 END IF;
578 ELSE
579 IF l_fail_flag = 'N' THEN
580 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
581 diagn_debug_msg('Next_Step_Type: ' || 'Batch is posted. Mark step as complete');
582 END IF;
583 Update GL_AUTO_ALLOC_BATCH_HISTORY
584 Set COMPLETE_FLAG = 'Y'
585 Where REQUEST_ID = to_number(p_item_key)
586 And STEP_NUMBER = l_step;
587 --And BATCH_ID = l_batch_id
588 --And BATCH_TYPE_CODE = l_batch_type_code;
589
590 If SQL%FOUND Then
591 diagn_debug_msg('Rows updated='||to_char(SQL%ROWCOUNT));
592 Else
593 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
594 diagn_debug_msg('Next_Step_Type: ' || 'No update any row for complete flag = Y');
595 END IF;
596 End If;
597 ELSE
598 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
599 diagn_debug_msg('Next_Step_Type: ' || 'Batches= '|| l_fail_batches || ' generated but not posted');
600 END IF;
601 END IF;
602
603 END IF;
604 End If; /* l_step is not null or 0 */
605
606 Open step_detail(l_step);
607
608 Fetch step_detail
609 Into
610 f_step_number
611 , f_batch_id
612 , f_batch_type_code
613 , f_allocation_method_code
614 , f_owner;
615
616 Close step_detail;
617 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
618 diagn_debug_msg('Next_Step_Type: ' || '***********************************************');
619 diagn_debug_msg('Next_Step_Type: ' || 'FETCHED NEXT STEP = '||to_char(f_step_number)||
620 ' Batch_Type_Code = '||f_batch_type_code);
621 diagn_debug_msg('Next_Step_Type: ' || '***********************************************');
622 END IF;
623 wf_engine.SetItemAttrNumber(
624 itemtype => p_item_type,
625 ITEMkey => p_item_key,
626 aname => 'STEP_NUMBER',
627 avalue => f_step_number );
628
629 wf_engine.SetItemAttrtext(
630 itemtype => p_item_type,
631 itemkey => p_item_key,
632 aname => 'BATCH_TYPE_CODE',
633 avalue => f_batch_type_code );
634
635 wf_engine.SetItemAttrNumber(
636 itemtype => p_item_type,
637 itemkey => p_item_key,
638 aname => 'BATCH_ID',
639 avalue => f_batch_id );
640
641 wf_engine.SetItemAttrtext(
642 itemtype => p_item_type,
643 itemkey => p_item_key,
644 aname => 'ALLOCATION_METHOD_CODE',
645 avalue => f_allocation_method_code );
646
647 wf_engine.SetItemAttrtext(
648 itemtype => p_item_type,
649 itemkey => p_item_key,
650 aname => 'STEP_CONTACT',
651 avalue => f_owner );
652
653 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
654 diagn_debug_msg('Next_Step_Type: ' || 'Step Contact = ' ||f_owner);
655 END IF;
656 If f_batch_id Is Not Null Then
657 f_batch_name := gl_auto_alloc_vw_pkg.Get_Batch_Name(
658 BATCH_TYPE_CODE => f_batch_type_code
659 ,BATCH_ID => f_batch_id
660 );
661 Else
662 f_batch_name := NULL;
663 End If;
664
665 wf_engine.SetItemAttrtext(
666 itemtype => p_item_type,
667 itemkey => p_item_key,
668 aname => 'BATCH_NAME',
669 avalue => f_batch_name );
670
671 If f_step_number IS NULL Then
672 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
673 diagn_debug_msg('Next_Step_Type: ' || 'Autoallocation completed sucessfully.');
674 END IF;
675 p_result := 'COMPLETE:COMPLETE';
676 Elsif f_batch_type_code in ('A', 'B', 'E', 'R') then
677 p_result := 'COMPLETE:GL';
678 Elsif f_batch_type_code = 'P' then
679 p_result := 'COMPLETE:PA';
680 End If;
681 return;
682 ElsIf ( p_funcmode = 'CANCEL' ) THEN
683 NULL;
684 End If;
685
686 EXCEPTION
687 WHEN OTHERS THEN
688 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Next_Step_Type', p_item_type, p_item_key);
689 Wf_Core.Get_Error(err_name,err_msg,err_stack);
690 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
691 diagn_debug_msg('Next_Step_Type: ' || err_msg ||'*'||err_stack);
692 END IF;
693 Update_Status(to_number(p_item_key)
694 ,f_step_number
695 ,'UFE'
696 );
697 Raise;
698 End Next_Step_Type;
699
700 procedure Find_Je_Batch_Type(p_item_type IN VARCHAR2,
701 p_item_key IN VARCHAR2,
702 p_actid IN NUMBER,
703 p_funcmode IN VARCHAR2,
704 p_result OUT NOCOPY VARCHAR2) Is
705 l_Batch_Type_Code VARCHAR2(1);
706 l_definition_form Varchar2(500) := NULL;
707 l_batch_id Number;
708 l_step_number Number;
709
710 Begin
711 If ( p_funcmode = 'RUN' ) THEN
712 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
713 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
714 diagn_debug_msg('Started Find_Je_Batch_Type');
715 END IF;
716
717 l_Batch_Type_Code := WF_ENGINE.GetItemAttrText
718 (p_item_type,
719 p_item_key,
720 'BATCH_TYPE_CODE');
721 l_batch_id := WF_ENGINE.GetItemAttrNumber
722 (p_item_type,
723 p_item_key,
724 'BATCH_ID');
725 l_step_number := WF_ENGINE.GetItemAttrNumber
726 (p_item_type,
727 p_item_key,
728 'STEP_NUMBER');
729
730 l_definition_form := NULL;
731
732 If l_Batch_Type_Code in ('A','B','E') then
733 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
734 diagn_debug_msg('Find_Je_Batch_Type: ' || 'Batch type = MassAllocations');
735 END IF;
736 If l_Batch_Type_Code In( 'A','E') Then
737 l_definition_form := 'GLXMADEF_A: ALLOC_BATCH_ID='|| to_char(l_batch_id);
738 ElsIf l_Batch_Type_Code = 'B' Then
739 l_definition_form := 'GLXMADEF_B: ALLOC_BATCH_ID='|| to_char(l_batch_id);
740 End If;
741 p_result := 'COMPLETE:MA';
742 Elsif l_Batch_Type_Code = 'R' Then
743 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
744 diagn_debug_msg('Find_Je_Batch_Type: ' || 'Batch Type = Recurring');
745 END IF;
746 l_definition_form := 'GLXRJDEF_A: PARM_BATCH_ID='|| to_char(l_batch_id);
747 p_result := 'COMPLETE:R';
748 Else
749 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
750 diagn_debug_msg('Find_Je_Batch_Type: ' || 'Incorrect batch type = '||l_Batch_Type_Code);
751 END IF;
752 End if;
753
754 wf_engine.SetItemAttrText
755 ( itemtype => p_item_type,
756 itemkey => p_item_key,
757 aname => 'DEFINITION_FORM',
758 avalue => l_definition_form );
759
760 ElsIf ( p_funcmode = 'CANCEL' ) THEN
761 NULL;
762 End If;
763
764 EXCEPTION
765 WHEN OTHERS THEN
766 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Find_Je_Batch_Type', p_item_type, p_item_key);
767 Wf_Core.Get_Error(err_name,err_msg,err_stack);
768 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
769 diagn_debug_msg('Find_Je_Batch_Type: ' || err_msg ||'*'||err_stack);
770 END IF;
771 Update_Status(to_number(p_item_key)
772 ,l_step_number
773 ,'UFE'
774 );
775
776 Raise;
777 End Find_Je_Batch_Type;
778
779 Procedure Is_Review_Required(p_item_type IN VARCHAR2,
780 p_item_key IN VARCHAR2,
781 p_actid IN NUMBER,
782 p_funcmode IN VARCHAR2,
783 p_result OUT NOCOPY VARCHAR2) Is
784
785 l_batch_review_required fnd_profile_option_values.profile_option_value%TYPE;
786 l_step_number Number;
787 l_rollback_allowed Varchar2(1);
788 l_message_Name Varchar2(150);
789
790 Begin
791 If ( p_funcmode = 'RUN' ) THEN
792 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
793 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
794 diagn_debug_msg('Started Is_Review_Required');
795 END IF;
796 l_step_number := WF_ENGINE.GetItemAttrNumber
797 (p_item_type,
798 p_item_key,
799 'STEP_NUMBER');
800
801 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
802 p_item_type,
803 p_item_key,
804 'ROLLBACK_ALLOWED');
805
806 l_batch_review_required := WF_ENGINE.GetItemAttrText(
807 p_item_type,
808 p_item_key,
809 'GL_JRNL_REVW_REQUIRED');
810
811
812 If l_batch_review_required = 'Y' Then
813 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
814 diagn_debug_msg('Is_Review_Required: ' || 'Review required before posting. Sending Notification');
815 END IF;
816 If l_rollback_allowed = 'Y' Then
817 l_message_name := 'GLALLOC:BATCH_REVIEW_REQUIRED';
818 Else
819 l_message_name := 'GLALLOC:BATCH_REVIEW_REQUIRED_NRB';
820 End If;
821
822 wf_engine.SetItemAttrText(itemtype => p_item_type,
823 itemkey => p_item_key,
824 aname => 'MESSAGE_NAME',
825 avalue => l_message_Name );
826
827 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
828 diagn_debug_msg('Is_Review_Required: ' || 'Message_name = '||l_message_Name);
829 END IF;
830
831 Update_Status(to_number(p_item_key)
832 ,l_step_number
833 ,'JRP'
834 );
835 p_result := 'COMPLETE:Y';
836 Else
837 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
838 diagn_debug_msg('Is_Review_Required: ' || 'Review required before posting:= No');
839 END IF;
840 -- making sure that status is generation complete
841 Update_Status(to_number(p_item_key)
842 ,l_step_number
843 ,'GC'
844 );
845 p_result := 'COMPLETE:N';
846 End If;
847 ElsIf ( p_funcmode = 'CANCEL' ) THEN
848 NULL;
849 End If;
850 EXCEPTION
851 WHEN OTHERS THEN
852 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Review_Required', p_item_type, p_item_key);
853 Wf_Core.Get_Error(err_name,err_msg,err_stack);
854 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
855 diagn_debug_msg('Is_Review_Required: ' || err_msg ||'*'||err_stack);
856 END IF;
857 Update_Status(to_number(p_item_key)
858 ,l_step_number
859 ,'UFE'
860 );
861
862 Raise;
863 End Is_Review_Required;
864
865 Procedure Is_Approval_Required(p_item_type IN VARCHAR2,
866 p_item_key IN VARCHAR2,
867 p_actid IN NUMBER,
868 p_funcmode IN VARCHAR2,
869 p_result OUT NOCOPY VARCHAR2) Is
870 l_gen_batch_id Number;
871 l_step_number Number;
872 l_approval_code Varchar2(1);
873 l_approval_flag Varchar2(1) := 'N';
874 l_approval_batch Varchar2(2000);
875
876 Cursor jrnl_approval_required_C IS
877 Select JB.APPROVAL_STATUS_CODE,JB.JE_BATCH_ID
878 FROM GL_JE_BATCHES jb,
879 GL_AUTO_ALLOC_BATCH_HISTORY bh
880 WHERE bh.request_id = to_number(p_item_key)
881 AND bh.step_number = l_step_number
882 AND jb.je_batch_id = bh.generated_je_batch_id;
883
884 Begin
885 If ( p_funcmode = 'RUN' ) THEN
886 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
887 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
888 diagn_debug_msg('Started Is_Approval_Required');
889 END IF;
890 --l_gen_batch_id := WF_ENGINE.GetItemAttrNumber
891 -- (p_item_type,
892 -- p_item_key,
893 -- 'GEN_BATCH_ID');
894 l_step_number := WF_ENGINE.GetItemAttrNumber
895 (p_item_type,
896 p_item_key,
897 'STEP_NUMBER');
898
899 OPEN jrnl_approval_required_C;
900 LOOP
901 fetch jrnl_approval_required_C into l_approval_code,l_gen_batch_id;
902 EXIT WHEN jrnl_approval_required_C%NOTFOUND;
903
904 If l_approval_code <> 'Z' Then
905 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
906 diagn_debug_msg('Is_Approval_Required: ' || 'Journal approval require for '||to_char(l_gen_batch_id));
907 END IF;
908 l_approval_flag := 'Y';
909 l_approval_batch := l_approval_batch || '*'|| to_char(l_gen_batch_id);
910 Else
911 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
912 diagn_debug_msg('Is_Approval_Required: ' || 'Journal approval not require for '||to_char(l_gen_batch_id));
913 END IF;
914 End If;
915 END LOOP;
916 CLOSE jrnl_approval_required_C;
917
918 IF l_approval_flag = 'Y' THEN
919 -- some batches require journal approval
920 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
921 diagn_debug_msg('Is_Approval_Required: ' || 'Journal approval required batches = '|| l_approval_batch);
922 END IF;
923 --Set status as journal approval pending
924 Update_Status(to_number(p_item_key)
925 ,l_step_number
926 ,'JAP'
927 );
928 p_result := 'COMPLETE:Y';
929 return;
930 ELSE
931 --making sure that status is generation completed.
932 Update_Status(to_number(p_item_key)
933 ,l_step_number
934 ,'GC'
935 );
936 p_result := 'COMPLETE:N';
937 return;
938 END IF;
939
940 ElsIf ( p_funcmode = 'CANCEL' ) THEN
941 NULL;
942 End If;
943 EXCEPTION
944 WHEN OTHERS THEN
945 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Approval_Required', p_item_type, p_item_key);
946 Wf_Core.Get_Error(err_name,err_msg,err_stack);
947 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
948 diagn_debug_msg('Is_Approval_Required: ' || err_msg ||'*'||err_stack);
949 END IF;
950 -- set status code to unexpected fatal error
951 Update_Status(to_number(p_item_key)
952 ,l_step_number
953 ,'UFE'
954 );
955
956 Raise;
957 End Is_Approval_Required;
958
959 Procedure Launch_JE_Approval(p_item_type IN VARCHAR2,
960 p_item_key IN VARCHAR2,
961 p_actid IN NUMBER,
962 p_funcmode IN VARCHAR2,
963 p_result OUT NOCOPY VARCHAR2) Is
964 l_gen_batch_id Number;
965 l_gen_batch_name Varchar2(100);
966 l_approval_code Varchar2(1);
967 l_user_id Number;
968 l_resp_id Number;
969 l_step_number Number;
970 l_org_id NUMBER;
971 l_resp_appl_id NUMBER;
972 l_business_group_id NUMBER;
973 l_gl_allow_preparer_approval Varchar2(30);
974
975 Cursor jrnl_approval_status_C IS
976 Select JB.APPROVAL_STATUS_CODE,JB.NAME,JB.JE_BATCH_ID
977 FROM GL_JE_BATCHES jb,
978 GL_AUTO_ALLOC_BATCH_HISTORY bh
979 WHERE bh.request_id = to_number(p_item_key)
980 AND bh.step_number = l_step_number
981 AND jb.je_batch_id = bh.generated_je_batch_id;
982
983 Begin
984 If ( p_funcmode = 'RUN' ) THEN
985 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
986 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
987 diagn_debug_msg('Started Launch_JE_Approval');
988 END IF;
989
990 l_step_number := WF_ENGINE.GetItemAttrNumber
991 (p_item_type,
992 p_item_key,
993 'STEP_NUMBER');
994
995 l_user_id := WF_ENGINE.GetItemAttrNumber
996 (p_item_type,
997 p_item_key,
998 'USER_ID');
999
1000 l_resp_id := WF_ENGINE.GetItemAttrNumber
1001 (p_item_type,
1002 p_item_key,
1003 'RESP_ID');
1004
1005 l_org_id := WF_ENGINE.GetItemAttrNumber
1006 (p_item_type,
1007 p_item_key,
1008 'ORG_ID');
1009
1010
1011 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
1012 (p_item_type,
1013 p_item_key,
1014 'RESP_APPL_ID');
1015
1016 l_business_group_id := WF_ENGINE.GetItemAttrNumber
1017 (p_item_type,
1018 p_item_key,
1019 'BUSINESS_GROUP_ID');
1020
1021 l_gl_allow_preparer_approval := WF_ENGINE.GetItemAttrText
1022 (p_item_type,
1023 p_item_key,
1024 'GL_ALLOW_PREPARER_APPROVAL');
1025
1026
1027 FND_PROFILE.put('ORG_ID', l_org_id);
1028 FND_PROFILE.put('USER_ID', l_user_id );
1029 FND_PROFILE.put('RESP_ID', l_resp_id);
1030 FND_PROFILE.put('RESP_APPL_ID', l_resp_appl_id);
1031 FND_PROFILE.put('PER_BUSINESS_GROUP_ID', l_business_group_id);
1032 FND_PROFILE.put('GL_ALLOW_PREPARER_APPROVAL', l_gl_allow_preparer_approval);
1033
1034 OPEN jrnl_approval_status_C;
1035 LOOP
1036 fetch jrnl_approval_status_C into l_approval_code, l_gen_batch_name,l_gen_batch_id;
1037 EXIT WHEN jrnl_approval_status_C%NOTFOUND;
1038
1039 If l_approval_code In ( 'Z','A','I') Then
1040 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1041 diagn_debug_msg('Launch_JE_Approval: ' || 'Journal already approved. Journal approval not launched');
1042 END IF;
1043 Elsif l_approval_code = 'R' Then
1044 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1045 diagn_debug_msg('Launch_JE_Approval: ' || 'Launching journal approval process');
1046 END IF;
1047 GL_WF_JE_APPROVAL_PKG.start_approval_workflow
1048 ( p_je_batch_id => l_gen_batch_id
1049 ,p_preparer_fnd_user_id => l_user_id
1050 ,p_preparer_resp_id => l_resp_id
1051 ,p_je_batch_name => l_gen_batch_name
1052 );
1053 End If;
1054 END LOOP;
1055 CLOSE jrnl_approval_status_C;
1056
1057 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1058 NULL;
1059 End If;
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Launch_JE_Approval', p_item_type, p_item_key);
1063 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1064 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1065 diagn_debug_msg('Launch_JE_Approval: ' || err_msg ||'*'||err_stack);
1066 END IF;
1067 Update_Status(to_number(p_item_key)
1068 ,l_step_number
1069 ,'UFE'
1070 );
1071 Raise;
1072 End Launch_JE_Approval;
1073
1074 Procedure Is_Batch_Approved(p_item_type IN VARCHAR2,
1075 p_item_key IN VARCHAR2,
1076 p_actid IN NUMBER,
1077 p_funcmode IN VARCHAR2,
1078 p_result OUT NOCOPY VARCHAR2) Is
1079
1080 l_gen_batch_id Number;
1081 l_step_number Number;
1082 l_approval_code Varchar2(1);
1083 l_rollback_allowed Varchar2(1);
1084 l_message_Name Varchar2(150);
1085 l_fail_flag Varchar2(1);
1086 l_fail_batches Varchar2(2000);
1087
1088 Cursor jrnl_approval_status_C IS
1089 Select JB.APPROVAL_STATUS_CODE,JB.JE_BATCH_ID
1090 FROM GL_JE_BATCHES jb,
1091 GL_AUTO_ALLOC_BATCH_HISTORY bh
1092 WHERE bh.request_id = to_number(p_item_key)
1093 AND bh.step_number = l_step_number
1094 AND jb.je_batch_id = bh.generated_je_batch_id;
1095
1096 Begin
1097 If ( p_funcmode = 'RUN' ) THEN
1098 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1099 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1100 diagn_debug_msg('Started Is_Batch_Approved');
1101 END IF;
1102
1103 l_step_number := WF_ENGINE.GetItemAttrNumber
1104 (p_item_type,
1105 p_item_key,
1106 'STEP_NUMBER');
1107 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
1108 p_item_type,
1109 p_item_key,
1110 'ROLLBACK_ALLOWED');
1111
1112
1113 OPEN jrnl_approval_status_C;
1114 LOOP
1115 FETCH jrnl_approval_status_C into l_approval_code, l_gen_batch_id;
1116 EXIT WHEN jrnl_approval_status_C%NOTFOUND;
1117
1118 If l_approval_code in ('Z' , 'A') Then
1119 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1120 diagn_debug_msg('Is_Batch_Approved: ' || 'Journal batch '||to_char(l_gen_batch_id)||' is approved');
1121 END IF;
1122 Else
1123 -- If l_approval_code in ('I','R') Then
1124 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1125 diagn_debug_msg('Is_Batch_Approved: ' || 'Journal batch '||to_char(l_gen_batch_id)||
1126 ' not yet approved. Sending Notification');
1127 END IF;
1128 l_fail_flag := 'Y';
1129 l_fail_batches := l_fail_batches || '*' || to_char(l_gen_batch_id) || 'approval failed';
1130 END IF;
1131 END LOOP;
1132 CLOSE jrnl_approval_status_C;
1133
1134 if l_fail_flag = 'Y' THEN
1135 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1136 diagn_debug_msg('Is_Batch_Approved: ' || 'Failed batches = '|| l_fail_batches|| 'Sending Notification');
1137 END IF;
1138
1139 If l_rollback_allowed = 'Y' Then
1140 l_message_name := 'GLALLOC:JOURNAL_APPROVAL_REQUIRED';
1141 Else
1142 l_message_name := 'GLALLOC:JOURNAL_APPROVAL_REQUIRED_NRB';
1143 End If;
1144
1145 wf_engine.SetItemAttrText(itemtype => p_item_type,
1146 itemkey => p_item_key,
1147 aname => 'MESSAGE_NAME',
1148 avalue => l_message_Name );
1149
1150 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1151 diagn_debug_msg('Is_Batch_Approved: ' || 'Message_name = '||l_message_Name);
1152 END IF;
1153 -- status Journal Approval Pending
1154 Update_Status(to_number(p_item_key)
1155 ,l_step_number
1156 ,'JAP'
1157 );
1158 p_result := 'COMPLETE:N';
1159 return;
1160 ELSE
1161 -- make sure that status is generation complete
1162 Update_Status(to_number(p_item_key)
1163 ,l_step_number
1164 ,'GC'
1165 );
1166 p_result := 'COMPLETE:Y';
1167 return;
1168 End If;
1169
1170 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1171 NULL;
1172 End If;
1173 EXCEPTION
1174 WHEN OTHERS THEN
1175 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Approval_Required', p_item_type, p_item_key);
1176 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1177 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1178 diagn_debug_msg('Is_Batch_Approved: ' || err_msg ||'*'||err_stack);
1179 END IF;
1180 Update_Status(to_number(p_item_key)
1181 ,l_step_number
1182 ,'UFE'
1183 );
1184
1185 Raise;
1186 End Is_Batch_Approved;
1187
1188
1189 Procedure Is_Batch_Generated(p_item_type IN VARCHAR2,
1190 p_item_key IN VARCHAR2,
1191 p_actid IN NUMBER,
1192 p_funcmode IN VARCHAR2,
1193 p_result OUT NOCOPY VARCHAR2) Is
1194
1195 l_batch_id Number;
1196 l_access_set_id Number;
1197 l_step_number Number;
1198 l_batch_type_code Varchar2(1);
1199 l_rollback_allowed Varchar2(1);
1200 l_message_Name Varchar2(150);
1201 l_generated_je_batch_id NUMBER := NULL;
1202 l_generated_batch_name Varchar2(100) := NULL;
1203 l_enter_journals VARCHAR2(500);
1204 l_generated_flag VARCHAR2(1) := 'N';
1205
1206 Cursor get_gen_batch_id_C Is
1207 Select A.GENERATED_JE_BATCH_ID,
1208 JEB.Name
1209 From GL_JE_BATCHES JEB
1210 ,GL_AUTO_ALLOC_BATCH_HISTORY A
1211 Where JEB.JE_BATCH_ID = A.GENERATED_JE_BATCH_ID
1212 AND A.REQUEST_ID = to_number(p_item_key)
1213 AND A.STEP_NUMBER = l_step_number;
1214
1215 Begin
1216 If ( p_funcmode = 'RUN' ) THEN
1217 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1218 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1219 diagn_debug_msg('Started Is_Batch_Generated');
1220 END IF;
1221
1222 l_step_number := WF_ENGINE.GetItemAttrNumber
1223 (p_item_type,
1224 p_item_key,
1225 'STEP_NUMBER');
1226
1227 l_access_set_id := WF_ENGINE.GetItemAttrNumber
1228 (p_item_type,
1229 p_item_key,
1230 'ACCESS_SET_ID');
1231
1232
1233 l_batch_id := WF_ENGINE.GetItemAttrNumber
1234 (p_item_type,
1235 p_item_key,
1236 'BATCH_ID');
1237
1238 l_batch_type_code := WF_ENGINE.GetItemAttrText(
1239 p_item_type,
1240 p_item_key,
1241 'BATCH_TYPE_CODE');
1242
1243
1244 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
1245 p_item_type,
1246 p_item_key,
1247 'ROLLBACK_ALLOWED');
1248
1249 Open get_gen_batch_id_C;
1250 LOOP
1251 Fetch get_gen_batch_id_C into
1252 l_generated_je_batch_id,
1253 l_generated_batch_name;
1254 EXIT WHEN get_gen_batch_id_C%NOTFOUND;
1255
1256 If (l_batch_type_code = 'E') THEN
1257 l_enter_journals := 'GLXJEENT_E:autoquery_level=' || '"' || 'BATCH' || '"' ||
1258 ' autoquery_coordination=' || '"' || 'INITIAL' || '"' ||
1259 ' autoquery_criteria=' || to_char(l_generated_je_batch_id) ||
1260 ' autoquery_access_set_id=' || to_char(l_access_set_id);
1261 ELSE
1262 l_enter_journals := 'GLXJEENT_A:autoquery_level=' || '"' || 'BATCH' ||
1263 '"' || ' autoquery_coordination=' || '"' || 'INITIAL' ||
1264 '"' || ' autoquery_criteria=' || to_char(l_generated_je_batch_id) ||
1265 ' autoquery_access_id=' || to_char(l_access_set_id);
1266 END IF;
1267
1268 wf_engine.SetItemAttrText ( itemtype => p_item_type,
1269 itemkey => p_item_key,
1270 aname => 'ENTER_JOURNALS_FORM',
1271 avalue => l_enter_journals );
1272
1273 If l_generated_je_batch_id IS NOT NULL Then
1274 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1275 diagn_debug_msg('Is_Batch_Generated: ' || 'Generated batch='||To_char(l_generated_je_batch_id)||' found');
1276 END IF;
1277 l_generated_flag := 'Y';
1278 Else
1279 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1280 diagn_debug_msg('Is_Batch_Generated: ' || 'Batch not Generated or Not found in GL_JE_BATCHES. Sending Notification');
1281 END IF;
1282 End IF;
1283 END LOOP;
1284 CLOSE get_gen_batch_id_C;
1285
1286 IF l_generated_flag = 'N' THEN
1287 --If l_rollback_allowed = 'Y' Then
1288 -- l_message_name := 'GLALLOC:NO_BATCH_GENERATED';
1289 --Else
1290 -- l_message_name := 'GLALLOC:NO_BATCH_GENERATED_NRB';
1291 --End If;
1292
1293 --wf_engine.SetItemAttrText(itemtype => p_item_type,
1294 -- itemkey => p_item_key,
1295 -- aname => 'MESSAGE_NAME',
1296 -- avalue => l_message_Name );
1297
1298 --IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1299 -- diagn_debug_msg('Is_Batch_Generated: ' || 'Message_name = '||l_message_Name);
1300 --END IF;
1301 -- set status code to Batch Not Generated
1302 --Update_Status(to_number(p_item_key)
1303 -- ,l_step_number
1304 -- ,'BNG'
1305 -- );
1306 p_result := 'COMPLETE:N';
1307 return;
1308 ELSE
1309 Update_Status(to_number(p_item_key)
1310 ,l_step_number
1311 ,'GC'
1312 );
1313 p_result := 'COMPLETE:Y';
1314 return;
1315 End IF;
1316
1317 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1318 NULL;
1319 End If;
1320 EXCEPTION
1321 WHEN OTHERS THEN
1322 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Batch_Generated', p_item_type, p_item_key);
1323 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1324 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1325 diagn_debug_msg('Is_Batch_Generated: ' || err_msg ||'*'||err_stack);
1326 END IF;
1327 -- set status code to unexpected fatal error
1328 Update_Status(to_number(p_item_key)
1329 ,l_step_number
1330 ,'UFE'
1331 );
1332 Raise;
1333 End Is_Batch_Generated;
1334
1335 Procedure Select_And_Validate_Batch(p_item_type IN VARCHAR2,
1336 p_item_key IN VARCHAR2,
1337 p_actid IN NUMBER,
1338 p_funcmode IN VARCHAR2,
1339 p_result OUT NOCOPY VARCHAR2) Is
1340
1341 l_result Varchar2(1);
1342 l_error_msg Varchar2(2000);
1343 l_gen_batch_id Number;
1344 l_step_number Number;
1345 l_rollback_allowed Varchar2(1);
1346 l_message_Name Varchar2(150);
1347 l_batches_not_valid Varchar2(32000) := NULL;
1348
1349 CURSOR get_all_batches_C IS
1350 SELECT jb.je_batch_id
1351 FROM GL_JE_BATCHES jb,
1352 GL_AUTO_ALLOC_BATCH_HISTORY bh
1353 WHERE bh.request_id = to_number(p_item_key)
1354 AND bh.step_number = l_step_number
1355 AND jb.je_batch_id = bh.generated_je_batch_id
1356 AND jb.status NOT IN ('P','I','S');
1357
1358 Begin
1359 If ( p_funcmode = 'RUN' ) THEN
1360 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1361 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1362 diagn_debug_msg('Started Select_And_Validate_Batch');
1363 END IF;
1364
1365 l_step_number := WF_ENGINE.GetItemAttrNumber
1366 (p_item_type,
1367 p_item_key,
1368 'STEP_NUMBER');
1369 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
1370 p_item_type,
1371 p_item_key,
1372 'ROLLBACK_ALLOWED');
1373 OPEN get_all_batches_C;
1374 LOOP
1375 FETCH get_all_batches_C into l_gen_batch_id;
1376 EXIT WHEN get_all_batches_C%NOTFOUND;
1377
1378 Is_JE_Valid_For_Posting
1379 ( itemtype => p_item_type
1380 ,itemkey => p_item_key
1381 ,l_je_batch_id => l_gen_batch_id
1382 ,l_invalid_error => l_error_msg
1383 ,result => l_result
1384 );
1385 If l_result In ('Y', 'P') Then
1386 If l_result = 'P' Then
1387 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1388 diagn_debug_msg('Select_And_Validate_Batch: ' || 'Batch '||to_char(l_gen_batch_id)||' is already posted');
1389 END IF;
1390 Else
1391 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1392 diagn_debug_msg('Select_And_Validate_Batch: ' || 'Batch '||to_char(l_gen_batch_id)||' is valid for posting');
1393 END IF;
1394 End If;
1395 Else
1396 l_batches_not_valid := l_batches_not_valid ||'Batch_Id='||
1397 to_char(l_gen_batch_id)||'*'||substrb(l_error_msg,1,80);
1398 End If;
1399 END LOOP;
1400 CLOSE get_all_batches_C;
1401
1402 IF l_batches_not_valid IS NULL THEN
1403 -- making sure that status is generation complete
1404 Update_Status(to_number(p_item_key)
1405 ,l_step_number
1406 ,'GC'
1407 );
1408
1409 p_result := 'COMPLETE:PASS';
1410 return;
1411 ELSE
1412 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1413 diagn_debug_msg('Select_And_Validate_Batch: ' || 'Sending Notification. '||l_error_msg);
1414 END IF;
1415 -- status Batch Not postable
1416
1417 If l_rollback_allowed = 'Y' Then
1418 l_message_name := 'GLALLOC:GEN_BATCH_NOT_POSTABLE';
1419 Else
1420 l_message_name := 'GLALLOC:GEN_BATCH_NOT_POSTABLE_NRB';
1421 End If;
1422
1423 wf_engine.SetItemAttrText(itemtype => p_item_type,
1424 itemkey => p_item_key,
1425 aname => 'MESSAGE_NAME',
1426 avalue => l_message_Name );
1427
1428 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1429 diagn_debug_msg('Select_And_Validate_Batch: ' || 'Message_name = '||l_message_Name);
1430 END IF;
1431
1432 l_batches_not_valid := substrb(l_batches_not_valid,1,2000);
1433
1434 wf_engine.SetItemAttrText(
1435 itemtype => p_item_type,
1436 itemkey => p_item_key,
1437 aname => 'ERROR_BATCHES',
1438 avalue => l_batches_not_valid );
1439
1440 -- Rollback batch not postable
1441 Update_Status(to_number(p_item_key)
1442 ,l_step_number
1443 ,'BNP'
1444 );
1445
1446 p_result := 'COMPLETE:FAIL';
1447 return;
1448 End If;
1449 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1450 NULL;
1451 End If;
1452 EXCEPTION
1453 WHEN OTHERS THEN
1454 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Select_And_Validate_Batch', p_item_type, p_item_key);
1455 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1456 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1457 diagn_debug_msg('Select_And_Validate_Batch: ' || err_msg ||'*'||err_stack);
1458 END IF;
1459 -- set status code to unexpected fatal error
1460 Update_Status(to_number(p_item_key)
1461 ,l_step_number
1462 ,'UFE'
1463 );
1464 Raise;
1465 End Select_And_Validate_Batch;
1466
1467 Procedure Is_Batch_Posted(p_item_type IN VARCHAR2,
1468 p_item_key IN VARCHAR2,
1469 p_actid IN NUMBER,
1470 p_funcmode IN VARCHAR2,
1471 p_result OUT NOCOPY VARCHAR2) Is
1472
1473
1474 l_gen_batch_id Number;
1475 l_step_number Number;
1476 l_status Varchar2(1);
1477 l_rollback_allowed Varchar2(1);
1478 l_message_Name Varchar2(150);
1479 l_fail_flag Varchar2(1) := 'N';
1480 l_fail_batches Varchar2(150);
1481
1482 Cursor check_JE_batch_status_C IS
1483 Select jb.je_batch_id, jb.status
1484 From GL_JE_BATCHES jb,
1485 GL_AUTO_ALLOC_BATCH_HISTORY bh
1486 Where bh.request_id = to_number(p_item_key)
1487 And bh.step_number = l_step_number
1488 And jb.je_batch_id = bh.generated_je_batch_id;
1489
1490 Begin
1491 If ( p_funcmode = 'RUN' ) THEN
1492 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1493 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1494 diagn_debug_msg('Started Is_Batch_Posted');
1495 END IF;
1496
1497 l_step_number := WF_ENGINE.GetItemAttrNumber
1498 (p_item_type,
1499 p_item_key,
1500 'STEP_NUMBER');
1501 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
1502 p_item_type,
1503 p_item_key,
1504 'ROLLBACK_ALLOWED');
1505
1506 open check_JE_batch_status_C;
1507 LOOP
1508 fetch check_JE_batch_status_C into l_gen_batch_id,l_status;
1509 EXIT WHEN check_JE_batch_status_C%NOTFOUND;
1510
1511 If l_status In ('P') Then
1512 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1513 diagn_debug_msg('Is_Batch_Posted: ' || 'Batch '||to_char(l_gen_batch_id)||' is posted successfully');
1514 END IF;
1515 Else
1516 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1517 diagn_debug_msg('Is_Batch_Posted: ' || 'Sending Notification. Batch '||to_char(l_gen_batch_id)|| ' posting failed. Status= '||l_status);
1518 END IF;
1519 l_fail_flag := 'Y';
1520 l_fail_batches := l_fail_batches || '*' || to_char(l_gen_batch_id);
1521 End if;
1522 END LOOP;
1523 CLOSE check_JE_batch_status_C;
1524
1525 IF l_fail_flag = 'Y' Then
1526 -- Some batches are not posted
1527 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1528 diagn_debug_msg('Is_Batch_Posted: ' || 'Failed batches = ' || l_fail_batches || 'Sending Notification ');
1529 END IF;
1530
1531 If l_rollback_allowed = 'Y' Then
1532 l_message_name := 'GLALLOC:GEN_BATCH_NOT_POSTED';
1533 Else
1534 l_message_name := 'GLALLOC:GEN_BATCH_NOT_POSTED_NRB';
1535 End If;
1536
1537 wf_engine.SetItemAttrText(itemtype => p_item_type,
1538 itemkey => p_item_key,
1539 aname => 'MESSAGE_NAME',
1540 avalue => l_message_Name );
1541 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1542 diagn_debug_msg('Is_Batch_Posted: ' || 'Message_name = '||l_message_Name);
1543 END IF;
1544
1545 --journal batch not posted status
1546 Update_Status(to_number(p_item_key)
1547 ,l_step_number
1548 ,'JBNP'
1549 );
1550 p_result := 'COMPLETE:N';
1551 ELSE
1552 Update_Status(to_number(p_item_key)
1553 ,l_step_number
1554 ,'PC'
1555 );
1556
1557 p_result := 'COMPLETE:Y';
1558 END IF;
1559
1560 ELSIf ( p_funcmode = 'CANCEL' ) THEN
1561 NULL;
1562 End If;
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Batch_Posted', p_item_type, p_item_key);
1566 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1567 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1568 diagn_debug_msg('Is_Batch_Posted: ' || err_msg ||'*'||err_stack);
1569 END IF;
1570 Update_Status(to_number(p_item_key)
1571 ,l_step_number
1572 ,'UFE'
1573 );
1574 Raise;
1575 End Is_Batch_Posted;
1576
1577 Procedure Delete_Batch(p_item_type IN VARCHAR2,
1578 p_item_key IN VARCHAR2,
1579 p_actid IN NUMBER,
1580 p_funcmode IN VARCHAR2,
1581 p_result OUT NOCOPY VARCHAR2) Is
1582
1583 l_step_number Number;
1584 l_batch_id Number;
1585 l_batch_type_code Varchar2(1);
1586 l_gen_batch_Id Number;
1587 l_gen_batch_name Varchar2(100);
1588 l_complete_flag Varchar2(1);
1589 l_status Varchar2(1);
1590
1591 Cursor Verify_Delete_C IS
1592 Select
1593 H.Step_Number
1594 ,H.Batch_Id
1595 ,H.BATCH_TYPE_CODE
1596 ,H.GENERATED_JE_BATCH_ID
1597 ,H.COMPLETE_FLAG
1598 ,JEB.Name
1599 ,JEB.Status
1600 From GL_JE_BATCHES JEB
1601 ,GL_AUTO_ALLOC_BATCH_HISTORY H
1602 Where
1603 JEB.JE_BATCH_ID = H.GENERATED_JE_BATCH_ID
1604 AND H.REQUEST_ID = to_number(p_item_key)
1605 AND H.GENERATED_JE_BATCH_ID IS Not Null
1606 AND JEB.Status <> 'P'
1607 Order By H.STEP_NUMBER Desc;
1608
1609 Cursor Set_Status_C IS
1610 Select step_number
1611 From GL_AUTO_ALLOC_BATCH_HISTORY
1612 Where
1613 REQUEST_ID= to_number(p_item_key)
1614 AND GENERATED_JE_BATCH_ID IS NULL
1615 AND STATUS_CODE <> 'NS';
1616
1617 Begin
1618 If ( p_funcmode = 'RUN' ) THEN
1619 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1620 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1621 diagn_debug_msg('Rollback:Started Delete_Batch');
1622 END IF;
1623
1624 Open Verify_Delete_C;
1625 LOOP
1626 Fetch Verify_Delete_C Into
1627 l_step_number
1628 ,l_batch_id
1629 ,l_batch_type_code
1630 ,l_gen_batch_Id
1631 ,l_complete_flag
1632 ,l_gen_batch_name
1633 ,l_status;
1634
1635 If Verify_Delete_C%NOTFOUND Then
1636 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1637 diagn_debug_msg('Delete_Batch: ' || 'Rollback:No Batch to delete');
1638 END IF;
1639 open Set_Status_C;
1640 fetch Set_Status_C into l_step_number;
1641 If Set_Status_C%FOUND Then
1642 --set status to Rollback Not Required
1643 Update_Status(to_number(p_item_key)
1644 ,l_step_number
1645 ,'RNR'
1646 );
1647 End If;
1648 close Set_Status_C;
1649 exit;
1650 Else
1651 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1652 diagn_debug_msg('Delete_Batch: ' || 'Rollback:Deleting Batch= '||to_char(l_gen_batch_Id));
1653 END IF;
1654 -- Delete all of the lines in that batch
1655 DELETE gl_je_lines
1656 WHERE je_header_id IN (SELECT je_header_id
1657 FROM gl_je_headers
1658 WHERE je_batch_id = l_gen_batch_Id);
1659
1660 -- Delete all of the headers in that batch
1661 DELETE gl_je_headers
1662 WHERE je_batch_id = l_gen_batch_Id;
1663
1664 -- Delete gl_je_batch
1665 DELETE gl_je_batches
1666 WHERE je_batch_id = l_gen_batch_Id;
1667
1668 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1669 diagn_debug_msg('Delete_Batch: ' || 'Rollback:Batch deleted');
1670 END IF;
1671 --set status to Rollback Completed for this step
1672 Update_Status(to_number(p_item_key)
1673 ,l_step_number
1674 ,'RC'
1675 );
1676 End IF;
1677 END LOOP;
1678 Close Verify_Delete_C;
1679 --set status to rollback pending for all step for which
1680 -- where status is NOT [Not started, Rollback Not Required, Rollback Completed]
1681 Update_Status(to_number(p_item_key)
1682 ,-1
1683 ,'RP'
1684 );
1685
1686 Return;
1687 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1688 NULL;
1689 End If;
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Delete_Batch', p_item_type, p_item_key);
1693 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1694 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1695 diagn_debug_msg('Delete_Batch: ' || err_msg ||'*'||err_stack);
1696 END IF;
1697 Update_Status(to_number(p_item_key)
1698 ,l_step_number
1699 ,'RUFE'
1700 );
1701 Raise;
1702 End Delete_Batch;
1703
1704 Procedure Are_More_JE_Reverse(p_item_type IN VARCHAR2,
1705 p_item_key IN VARCHAR2,
1706 p_actid IN NUMBER,
1707 p_funcmode IN VARCHAR2,
1708 p_result OUT NOCOPY VARCHAR2) Is
1709
1710 l_step_number Number;
1711 l_gen_batch_id Number;
1712 l_je_header_id Number;
1713 f_je_header_id Number;
1714
1715 Cursor Get_Batches_C IS
1716 Select
1717 Step_Number
1718 ,GENERATED_JE_BATCH_ID
1719 From GL_AUTO_ALLOC_BATCH_HISTORY
1720 WHERE REQUEST_ID = to_number(p_item_key)
1721 And COMPLETE_FLAG = 'Y'
1722 And Nvl(ALL_HEADERS_REVERSED,'N') <> 'Y'
1723 And STEP_NUMBER <= l_step_number
1724 Order by STEP_NUMBER desc;
1725
1726 Cursor Get_Headers_C IS
1727 Select JE.JE_HEADER_ID
1728 FROM GL_JE_HEADERS JE
1729 WHERE JE.JE_BATCH_ID = l_gen_batch_id
1730 AND NOT EXISTS ( SELECT RB.JE_HEADER_ID
1731 FROM GL_AUTO_ALLOC_REV_BATCHES RB
1732 WHERE RB.PARENT_REQUEST_ID = to_number(p_item_key)
1733 AND RB.JE_HEADER_ID = JE.JE_HEADER_ID);
1734
1735 Begin
1736 If ( p_funcmode = 'RUN' ) THEN
1737 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1738 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1739 diagn_debug_msg('Rollback:Started Are_More_JE_Reverse');
1740 END IF;
1741 l_step_number := WF_ENGINE.GetItemAttrNumber
1742 (p_item_type,
1743 p_item_key,
1744 'STEP_NUMBER');
1745
1746
1747 l_gen_batch_id := WF_ENGINE.GetItemAttrNumber
1748 (p_item_type,
1749 p_item_key,
1750 'GEN_BATCH_ID');
1751
1752 IF l_gen_batch_id IS NOT NULL THEN
1753 Open Get_Headers_C;
1754 Fetch Get_Headers_C Into f_je_header_id;
1755 Close Get_Headers_C;
1756
1757 If f_je_header_id IS NOT NULL Then
1758 WF_ENGINE.SetItemAttrNumber
1759 (p_item_type,
1760 p_item_key,
1761 'JE_HEADER_ID'
1762 ,f_je_header_id);
1763
1764 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1765 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Rollback:header = '||to_char(f_je_header_id)||' to be reversed');
1766 END IF;
1767
1768 --set status to rollback reversal pending for this step
1769 Update_Status(to_number(p_item_key)
1770 ,l_step_number
1771 ,'RRP');
1772
1773 p_result := 'COMPLETE:Y';
1774 return;
1775 Else
1776 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1777 diagn_debug_msg('Are_More_JE_Reverse: ' || '*********************************************************');
1778 diagn_debug_msg('Are_More_JE_Reverse: ' || 'ROLLBACK: All HEADERS ARE REVERSED FOR STEP '||to_char(l_step_number)|| ' GEN BATCH '||to_char(l_gen_batch_id));
1779 diagn_debug_msg('Are_More_JE_Reverse: ' || '*********************************************************');
1780 END IF;
1781 UPDATE GL_AUTO_ALLOC_BATCH_HISTORY
1782 Set ALL_HEADERS_REVERSED = 'Y'
1783 WHERE REQUEST_ID = to_number(p_item_key)
1784 And GENERATED_JE_BATCH_ID = l_gen_batch_id;
1785 diagn_debug_msg('Rows updated = '||to_char(SQL%ROWCOUNT));
1786
1787 --set status to rollback reversal completed for this step
1788 Update_Status(to_number(p_item_key)
1789 ,l_step_number
1790 ,'RRC');
1791
1792 End If; /* f_je_header_id is not null */
1793
1794 Loop
1795 l_step_number := WF_ENGINE.GetItemAttrNumber
1796 (p_item_type,
1797 p_item_key,
1798 'STEP_NUMBER');
1799
1800 l_gen_batch_id := WF_ENGINE.GetItemAttrNumber
1801 (p_item_type,
1802 p_item_key,
1803 'GEN_BATCH_ID');
1804
1805 Open Get_Batches_C;
1806 Fetch Get_Batches_C into l_step_number, l_gen_batch_id;
1807
1808 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
1809 itemkey => p_item_key,
1810 aname => 'GEN_BATCH_ID',
1811 avalue => l_gen_batch_id );
1812
1813 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
1814 itemkey => p_item_key,
1815 aname => 'STEP_NUMBER',
1816 avalue => l_step_number );
1817
1818 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1819 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Step Number = '||to_char(l_step_number)||
1820 ' Gen Batch id = '||to_char(l_gen_batch_id));
1821 END IF;
1822
1823 If Get_Batches_C%NOTFOUND Then
1824 --no more batches to be reversed
1825 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1826 diagn_debug_msg('Are_More_JE_Reverse: ' || '*************************************');
1827 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Rollback: No more batches to reverse');
1828 diagn_debug_msg('Are_More_JE_Reverse: ' || '*************************************');
1829 END IF;
1830 Close Get_Batches_C;
1831 exit;
1832 Else
1833 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1834 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Rollback:get header id for batch '||to_char(l_gen_batch_id));
1835 END IF;
1836 Open Get_Headers_C;
1837 Fetch Get_Headers_C Into f_je_header_id;
1838 Close Get_Headers_C;
1839 If f_je_header_id IS NOT NULL Then
1840 WF_ENGINE.SetItemAttrNumber
1841 (p_item_type,
1842 p_item_key,
1843 'JE_HEADER_ID'
1844 ,f_je_header_id);
1845 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1846 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Rollback: Header '||to_char(f_je_header_id)||
1847 ' More Headers to reverse');
1848 END IF;
1849 Close Get_Batches_C;
1850 --set status to rollback reversal pending for this step
1851 Update_Status(to_number(p_item_key)
1852 ,l_step_number
1853 ,'RRP');
1854
1855 p_result := 'COMPLETE:Y';
1856 return;
1857 Else
1858 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1859 diagn_debug_msg('Are_More_JE_Reverse: ' || '*********************************************************');
1860 diagn_debug_msg('Are_More_JE_Reverse: ' || '*ROLLBACK: All HEADERS ARE REVERSED FOR STEP '||
1861 to_char(l_step_number)|| ' GEN BATCH '||to_char(l_gen_batch_id));
1862 diagn_debug_msg('Are_More_JE_Reverse: ' || '*********************************************************');
1863 END IF;
1864 UPDATE GL_AUTO_ALLOC_BATCH_HISTORY
1865 Set ALL_HEADERS_REVERSED = 'Y'
1866 WHERE REQUEST_ID = to_number(p_item_key)
1867 And GENERATED_JE_BATCH_ID = l_gen_batch_id;
1868 diagn_debug_msg('Rollback:Rows updated = '||to_char(SQL%ROWCOUNT));
1869 Close Get_Batches_C;
1870 --set status to rollback reversal completed for this step
1871 Update_Status(to_number(p_item_key)
1872 ,l_step_number
1873 ,'RRC');
1874
1875 End If;
1876 End if;
1877 End Loop;
1878 Else
1879 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1880 diagn_debug_msg('Are_More_JE_Reverse: ' || 'Reversal Completed');
1881 END IF;
1882 End If;
1883
1884 -- set status to rollback reversal completed for all step where status is NOT
1885 -- Not started, Rollback Not Required, Rollback Completed
1886 Update_Status(to_number(p_item_key)
1887 ,-1
1888 ,'RRC');
1889
1890 p_result := 'COMPLETE:N';
1891 return;
1892 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1893 NULL;
1894 End If;
1895 EXCEPTION
1896 WHEN NO_DATA_FOUND Then
1897 Null;
1898 WHEN OTHERS THEN
1899 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Are_More_JE_Reverse', p_item_type, p_item_key);
1900 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1901 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1902 diagn_debug_msg('Are_More_JE_Reverse: ' || err_msg ||'*'||err_stack);
1903 END IF;
1904 Update_Status(to_number(p_item_key)
1905 ,l_step_number
1906 ,'RUFE'
1907 );
1908
1909 Raise;
1910 End Are_More_JE_Reverse;
1911
1912 Procedure Is_Posting_Required(p_item_type IN VARCHAR2,
1913 p_item_key IN VARCHAR2,
1914 p_actid IN NUMBER,
1915 p_funcmode IN VARCHAR2,
1916 p_result OUT NOCOPY VARCHAR2) Is
1917
1918 l_reversal_je_header_id NUMBER;
1919
1920
1921 Cursor verify_rev_batch_C IS
1922 Select R.REVERSAL_JE_HEADER_ID
1923 From GL_JE_BATCHES JEB
1924 ,GL_AUTO_ALLOC_REV_BATCHES R
1925 Where JEB.JE_BATCH_ID = R.REVERSAL_JE_BATCH_ID
1926 AND JEB.STATUS Not In ('P','I')
1927 AND R.PARENT_REQUEST_ID = to_number(p_item_key);
1928
1929 Begin
1930 If ( p_funcmode = 'RUN' ) THEN
1931 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
1932 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1933 diagn_debug_msg('Started Is_Posting_Required');
1934 END IF;
1935 Open verify_rev_batch_C;
1936 Fetch verify_rev_batch_C into l_reversal_je_header_id;
1937 If verify_rev_batch_C%FOUND Then
1938 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1939 diagn_debug_msg('Is_Posting_Required: ' || 'Rollback:Posting is required for reverse batch(es)');
1940 END IF;
1941 Close verify_rev_batch_C;
1942 --set status to rollback posting pending for all step for which
1943 -- where status is NOT Not started, Rollback Not Required, Rollback Completed
1944
1945 Update_Status(to_number(p_item_key)
1946 ,-1
1947 ,'RPP');
1948
1949 p_result := 'COMPLETE:Y';
1950 return;
1951 Else
1952 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1953 diagn_debug_msg('Is_Posting_Required: ' || 'Rollback Completed: Posting not required ');
1954 END IF;
1955 --set status to rollback completed for all step for which
1956 -- where status is NOT Not started, Rollback Not Required, Rollback Completed
1957
1958 Update_Status(to_number(p_item_key)
1959 ,-1
1960 ,'RC');
1961
1962
1963 p_result := 'COMPLETE:N';
1964 Close verify_rev_batch_C;
1965 return;
1966 End If;
1967 ElsIf ( p_funcmode = 'CANCEL' ) THEN
1968 NULL;
1969 End If;
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Posting_Required', p_item_type, p_item_key);
1973 Wf_Core.Get_Error(err_name,err_msg,err_stack);
1974 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
1975 diagn_debug_msg('Is_Posting_Required: ' || err_msg ||'*'||err_stack);
1976 END IF;
1977 Raise;
1978 End Is_Posting_Required ;
1979
1980
1981 Procedure Is_Jrnl_Reversed(p_item_type IN VARCHAR2,
1982 p_item_key IN VARCHAR2,
1983 p_actid IN NUMBER,
1984 p_funcmode IN VARCHAR2,
1985 p_result OUT NOCOPY VARCHAR2) Is
1986
1987 l_je_header_id Number;
1988 l_step_number Number;
1989 l_rev_header_id Number;
1990 l_status Varchar2(1);
1991 l_message_Name Varchar2(150);
1992
1993
1994 Cursor verify_reversal_C IS
1995 Select
1996 Accrual_rev_status
1997 ,Accrual_rev_je_header_id
1998 From GL_JE_HEADERS
1999 Where JE_HEADER_ID = l_je_header_id;
2000
2001 Begin
2002 If ( p_funcmode = 'RUN' ) THEN
2003 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2004 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2005 diagn_debug_msg('Started Is_Jrnl_Reversed');
2006 END IF;
2007 l_je_header_id := WF_ENGINE.GetItemAttrNumber
2008 (p_item_type,
2009 p_item_key,
2010 'JE_HEADER_ID');
2011 l_step_number := WF_ENGINE.GetItemAttrNumber
2012 (p_item_type,
2013 p_item_key,
2014 'STEP_NUMBER');
2015
2016 If l_je_header_id is Not Null Then
2017 open verify_reversal_C;
2018 fetch verify_reversal_C into l_status,l_rev_header_id;
2019 close verify_reversal_C;
2020 If l_status = 'R' then
2021 p_result := 'COMPLETE:Y';
2022 return;
2023 Else
2024 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2025 diagn_debug_msg('Is_Jrnl_Reversed: ' || 'Sending Notification. Journal not reversed');
2026 END IF;
2027
2028 l_message_Name := 'GLALLOC:JRNL_NOT_REVERSED';
2029 wf_engine.SetItemAttrText(itemtype => p_item_type,
2030 itemkey => p_item_key,
2031 aname => 'MESSAGE_NAME',
2032 avalue => l_message_Name );
2033
2034 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2035 diagn_debug_msg('Is_Jrnl_Reversed: ' || 'Message_name = '||l_message_Name);
2036 END IF;
2037 --step status to Rollback Journal Not Reversed for this step
2038 Update_Status(to_number(p_item_key)
2039 ,l_step_number
2040 ,'RJNR');
2041
2042 p_result := 'COMPLETE:N';
2043 return;
2044 End If;
2045 Else
2046 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2047 diagn_debug_msg('Is_Jrnl_Reversed: ' || 'Je_Header_Id is null');
2048 END IF;
2049 End If;
2050
2051 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2052 NULL;
2053 End If;
2054 EXCEPTION
2055 WHEN OTHERS THEN
2056 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Jrnl_Reversed', p_item_type, p_item_key);
2057 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2058 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2059 diagn_debug_msg('Is_Jrnl_Reversed: ' || err_msg ||'*'||err_stack);
2060 END IF;
2061 Update_Status(to_number(p_item_key)
2062 ,l_step_number
2063 ,'RUFE'
2064 );
2065
2066 Raise;
2067 End Is_Jrnl_Reversed ;
2068
2069 Procedure Select_And_Validate_AllBatches(p_item_type IN VARCHAR2,
2070 p_item_key IN VARCHAR2,
2071 p_actid IN NUMBER,
2072 p_funcmode IN VARCHAR2,
2073 p_result OUT NOCOPY VARCHAR2) Is
2074
2075 l_reversal_je_batch_id NUMBER;
2076 l_step_number NUMBER;
2077 l_batches_not_valid VARCHAR2(32000) := NULL;
2078 l_error_msg Varchar2(2000);
2079 l_result Varchar2(2);
2080 l_message_Name Varchar2(150);
2081
2082 Cursor get_all_rev_batches_C IS
2083 Select
2084 GLAARV.REVERSAL_JE_BATCH_ID
2085 ,BH.step_Number
2086 FROM GL_JE_BATCHES GLB
2087 ,GL_AUTO_ALLOC_BATCH_HISTORY BH
2088 ,GL_AUTO_ALLOC_REV_BATCHES GLAARV
2089 Where GLB.JE_BATCH_ID = GLAARV.REVERSAL_JE_BATCH_ID
2090 AND GLAARV.JE_BATCH_ID = BH.GENERATED_JE_BATCH_ID
2091 AND GLB.STATUS NOT In ('P','I','S')
2092 AND BH.REQUEST_ID = to_number(p_item_key)
2093 AND GLAARV.PARENT_REQUEST_ID = to_number(p_item_key);
2094
2095 Begin
2096 If ( p_funcmode = 'RUN' ) THEN
2097 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2098 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2099 diagn_debug_msg('Rollback:Started Select_And_Validate_AllBatches');
2100 END IF;
2101 Open get_all_rev_batches_C;
2102 LOOP
2103 FETCH get_all_rev_batches_C into
2104 l_reversal_je_batch_id
2105 ,l_step_number;
2106 Exit WHEN get_all_rev_batches_C%NOTFOUND;
2107 If l_reversal_je_batch_id IS NOT NULL Then
2108 Is_JE_Valid_For_Posting
2109 ( itemtype => p_item_type
2110 ,itemkey => p_item_key
2111 ,l_je_batch_id => l_reversal_je_batch_id
2112 ,l_invalid_error => l_error_msg
2113 ,result => l_result
2114 );
2115 If l_result = 'Y' Then
2116 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2117 diagn_debug_msg('Select_And_Validate_AllBatches: ' || 'Rollback: '||to_char(l_reversal_je_batch_id)||
2118 ' Batch is valid for posting');
2119 END IF;
2120 ElsIf l_result = 'P' Then
2121 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2122 diagn_debug_msg('Select_And_Validate_AllBatches: ' || 'Rollback:Batch '||to_char(l_reversal_je_batch_id)||
2123 ' is already posted');
2124 END IF;
2125 Else
2126 l_batches_not_valid := l_batches_not_valid ||'Batch_Id='||
2127 to_char(l_reversal_je_batch_id)||'*'||substrb(l_error_msg,1,80);
2128 -- Rollback batch not postable
2129 Update_Status(to_number(p_item_key)
2130 ,l_step_number
2131 ,'RBNP');
2132
2133 End If;
2134 End If;
2135 End Loop;
2136 If l_batches_not_valid IS NULL Then
2137 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2138 diagn_debug_msg('Select_And_Validate_AllBatches: ' || '*************************************');
2139 diagn_debug_msg('Select_And_Validate_AllBatches: ' || 'Rollback:All reverse batches valid for posting');
2140 diagn_debug_msg('Select_And_Validate_AllBatches: ' || '*************************************');
2141 END IF;
2142
2143 --set status to rollback posting pending for all step for which
2144 -- where status is NOT [Not started, Rollback Not Required, Rollback Completed]
2145 Update_Status(to_number(p_item_key)
2146 ,-1
2147 ,'RPP');
2148 p_result := 'COMPLETE:PASS';
2149 return;
2150 Else
2151 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2152 diagn_debug_msg('Select_And_Validate_AllBatches: ' || 'Rollback:Batches not valid for posting = '||l_batches_not_valid||
2153 ' Sending Notification' );
2154 END IF;
2155 l_message_name := 'GLALLOC:JE_BATCHES_NOT_POSTABLE';
2156 wf_engine.SetItemAttrText(itemtype => p_item_type,
2157 itemkey => p_item_key,
2158 aname => 'MESSAGE_NAME',
2159 avalue => l_message_Name );
2160 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2161 diagn_debug_msg('Select_And_Validate_AllBatches: ' || 'Message_name = '||l_message_Name);
2162 END IF;
2163
2164 --max length for text variable in WF is 2000
2165 l_batches_not_valid := substrb(l_batches_not_valid,1,2000);
2166 wf_engine.SetItemAttrText(itemtype => p_item_type,
2167 itemkey => p_item_key,
2168 aname => 'ERROR_BATCHES',
2169 avalue => l_batches_not_valid );
2170 p_result := 'COMPLETE:FAIL';
2171 return;
2172 End If;
2173 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2174 NULL;
2175 End If;
2176 EXCEPTION
2177 WHEN OTHERS THEN
2178 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Select_And_Validate_AllBatches', p_item_type, p_item_key);
2179 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2180 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2181 diagn_debug_msg('Select_And_Validate_AllBatches: ' || err_msg ||'*'||err_stack);
2182 END IF;
2183 Update_Status(to_number(p_item_key)
2184 ,l_step_number
2185 ,'RUFE'
2186 );
2187
2188 Raise;
2189 End Select_And_Validate_AllBatches ;
2190
2191 Procedure Are_All_Batches_Posted(p_item_type IN VARCHAR2,
2192 p_item_key IN VARCHAR2,
2193 p_actid IN NUMBER,
2194 p_funcmode IN VARCHAR2,
2195 p_result OUT NOCOPY VARCHAR2) Is
2196
2197 l_status Varchar2(1);
2198 l_fail_flag Varchar2(1) := 'N';
2199 l_je_batch_id Number;
2200 l_fail_batches Varchar2(2000);
2201 l_step_number Number;
2202 l_message_Name Varchar2(150);
2203
2204 Cursor check_JE_batch_status_C IS
2205 Select JEB.Status
2206 ,JEB.JE_Batch_Id
2207 ,BH.Step_Number
2208 From GL_JE_BATCHES JEB
2209 ,GL_AUTO_ALLOC_BATCH_HISTORY BH
2210 ,GL_AUTO_ALLOC_REV_BATCHES RB
2211 Where JEB.JE_BATCH_ID = RB.REVERSAL_JE_BATCH_ID
2212 And RB.JE_BATCH_ID = BH.GENERATED_JE_BATCH_ID
2213 AND BH.REQUEST_ID = to_number(p_item_key)
2214 And RB.PARENT_REQUEST_ID = to_number(p_item_key);
2215
2216 Begin
2217 If ( p_funcmode = 'RUN' ) THEN
2218 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2219 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2220 diagn_debug_msg('Rollback:Started Are_All_Batches_Posted');
2221 END IF;
2222 open check_JE_batch_status_C;
2223 Loop
2224 fetch check_JE_batch_status_C into l_status,l_je_batch_id,l_step_number;
2225 Exit When check_JE_batch_status_C%NOTFOUND;
2226 If l_status In ('P') Then
2227 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2228 diagn_debug_msg('Are_All_Batches_Posted: ' || 'Rollback:Batch '||to_char(l_je_batch_id)||' is posted successfully');
2229 END IF;
2230 Else
2231 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2232 diagn_debug_msg('Are_All_Batches_Posted: ' || 'Rollback:Batch '||to_char(l_je_batch_id)||' posting failed ');
2233 END IF;
2234 l_fail_flag := 'Y';
2235 l_fail_batches := l_fail_batches ||'*'||to_char(l_je_batch_id);
2236 --here set status to Rollback Posting Failed for this step
2237 Update_status(
2238 to_number(p_item_key)
2239 ,l_step_number
2240 ,'RJBNP');
2241
2242 End If;
2243 End Loop;
2244
2245 If l_fail_flag = 'Y' Then
2246 -- some batches are not posted
2247 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2248 diagn_debug_msg('Are_All_Batches_Posted: ' || 'Rollback:Failed batches = '||l_fail_batches||' Sending Notification ');
2249 END IF;
2250 l_message_name := 'GLALLOC:GEN_BATCHES_NOT_POSTED';
2251 wf_engine.SetItemAttrText(itemtype => p_item_type,
2252 itemkey => p_item_key,
2253 aname => 'MESSAGE_NAME',
2254 avalue => l_message_Name );
2255 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2256 diagn_debug_msg('Are_All_Batches_Posted: ' || 'Message_name = '||l_message_Name);
2257 END IF;
2258
2259 p_result := 'COMPLETE:N';
2260 return;
2261 Else
2262 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2263 diagn_debug_msg('Are_All_Batches_Posted: ' || '*************************************************************');
2264 diagn_debug_msg('Are_All_Batches_Posted: ' || 'Rollback Completed: All reversed batches posted successfully');
2265 diagn_debug_msg('Are_All_Batches_Posted: ' || '*************************************************************');
2266 END IF;
2267 -- set status to rollback completed for all step for which
2268 -- where status is NOT [Not started, Rollback Not Required, Rollback Completed]
2269 Update_Status(to_number(p_item_key)
2270 ,-1
2271 ,'RC');
2272 p_result := 'COMPLETE:Y';
2273 return;
2274 End If;
2275
2276 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2277 NULL;
2278 End If;
2279 EXCEPTION
2280 WHEN OTHERS THEN
2281 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Are_All_Batches_Posted', p_item_type, p_item_key);
2282 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2283 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2284 diagn_debug_msg('Are_All_Batches_Posted: ' || err_msg ||'*'||err_stack);
2285 END IF;
2286 Update_Status(to_number(p_item_key)
2287 ,l_step_number
2288 ,'RUFE'
2289 );
2290
2291 Raise;
2292 End Are_All_Batches_Posted ;
2293
2294
2295 Procedure Is_Rollback_Allowed(p_item_type IN VARCHAR2,
2296 p_item_key IN VARCHAR2,
2297 p_actid IN NUMBER,
2298 p_funcmode IN VARCHAR2,
2299 p_result OUT NOCOPY VARCHAR2) Is
2300
2301 l_rollback_allowed Varchar2(1);
2302 l_message_Name Varchar2(150);
2303
2304 Begin
2305 If ( p_funcmode = 'RUN' ) THEN
2306 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2307 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2308 diagn_debug_msg('Started Is_Rollback_Allowed');
2309 END IF;
2310 l_Rollback_Allowed := WF_ENGINE.GetItemAttrText
2311 (p_item_type,
2312 p_item_key,
2313 'ROLLBACK_ALLOWED');
2314
2315 If l_Rollback_Allowed = 'N' Then
2316 p_result := 'COMPLETE:N';
2317 Else
2318 p_result := 'COMPLETE:Y';
2319 End If;
2320 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2321 diagn_debug_msg('Is_Rollback_Allowed: ' || 'rollback Allowed = '||l_Rollback_Allowed);
2322 END IF;
2323 return;
2324 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2325 NULL;
2326 End If;
2327 EXCEPTION
2328 WHEN OTHERS THEN
2329 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Is_Rollback_Allowed', p_item_type, p_item_key);
2330 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2331 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2332 diagn_debug_msg('Is_Rollback_Allowed: ' || err_msg ||'*'||err_stack);
2333 END IF;
2334 Raise;
2335 End Is_Rollback_Allowed ;
2336
2337 Procedure SUBMIT_MA_PROGRAM(p_item_type IN VARCHAR2,
2338 p_item_key IN VARCHAR2,
2339 p_actid IN NUMBER,
2340 p_funcmode IN VARCHAR2,
2341 p_result OUT NOCOPY VARCHAR2) Is
2342
2343 submit_request_id NUMBER;
2344 l_step_number NUMBER;
2345 t_allocation_method_code Varchar2(1);
2346 l_allocation_method_code Varchar2(1);
2347 l_usage_code Varchar2(1);
2348 l_access_set_id Number;
2349 l_ledger_id Number;
2350 l_ledger_currency Varchar2(15);
2351 l_balancing_segment_value Varchar2(25);
2352 l_period_name Varchar2(15);
2353 l_journal_effective_date Date;
2354 l_calc_effective_date Date;
2355 l_batch_id Number;
2356 l_parent_req_id Number := to_number(p_item_key);
2357 l_usage_num Number;
2358
2359 Begin
2360 If ( p_funcmode = 'RUN' ) THEN
2361 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2362 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2363 diagn_debug_msg('Started SUBMIT_MA_PROGRAM');
2364 END IF;
2365
2366 l_step_number := WF_ENGINE.GetItemAttrNumber
2367 (p_item_type,
2368 p_item_key,
2369 'STEP_NUMBER');
2370
2371 l_usage_code := WF_ENGINE.GetItemAttrText
2372 (p_item_type,
2373 p_item_key,
2374 'USAGE_CODE');
2375
2376 If l_usage_code = 'Y' Then
2377 l_usage_num := 1;
2378 Else
2379 l_usage_num := 0;
2380 End if;
2381
2382 t_allocation_method_code := WF_ENGINE.GetItemAttrText
2383 (p_item_type,
2384 p_item_key,
2385 'ALLOCATION_METHOD_CODE');
2386
2387 If t_allocation_method_code = 'I' Then
2388 l_allocation_method_code := 'Y' ;
2389 Else
2390 l_allocation_method_code := 'N';
2391 End If;
2392
2393 l_access_set_id := WF_ENGINE.GetItemAttrNumber
2394 (p_item_type,
2395 p_item_key,
2396 'ACCESS_SET_ID');
2397
2398 l_ledger_id := WF_ENGINE.GetItemAttrNumber
2399 (p_item_type,
2400 p_item_key,
2401 'LEDGER_ID');
2402
2403 l_ledger_currency := WF_ENGINE.GetItemAttrText
2404 (p_item_type,
2405 p_item_key,
2406 'LEDGER_CURRENCY');
2407
2408 l_balancing_segment_value := WF_ENGINE.GetItemAttrText
2409 (p_item_type,
2410 p_item_key,
2411 'BALANCING_SEGMENT_VALUE');
2412
2413 l_batch_id := WF_ENGINE.GetItemAttrNumber
2414 (p_item_type,
2415 p_item_key,
2416 'BATCH_ID');
2417
2418 l_period_name := WF_ENGINE.GetItemAttrText
2419 (p_item_type,
2420 p_item_key,
2421 'PERIOD_NAME');
2422 l_journal_effective_date := WF_ENGINE.GetItemAttrDate
2423 (p_item_type,
2424 p_item_key,
2425 'JOURNAL_EFFECTIVE_DATE');
2426 l_calc_effective_date := WF_ENGINE.GetItemAttrDate
2427 (p_item_type,
2428 p_item_key,
2429 'CALCULATION_EFFECTIVE_DATE');
2430
2431 Submit_Request( l_parent_req_id
2432 ,l_step_number
2433 ,'GLAMAS'
2434 ,'C'
2435 ,to_char(l_access_set_id)
2436 ,l_allocation_method_code
2437 ,to_char(l_usage_num)
2438 ,to_char(l_ledger_id)
2439 ,l_ledger_currency
2440 ,l_balancing_segment_value
2441 ,to_char(l_batch_id)
2442 ,l_period_name
2443 ,to_char(l_journal_effective_date,'YYYY/MM/DD HH24:MI:SS')
2444 ,to_char(l_calc_effective_date,'YYYY/MM/DD HH24:MI:SS')
2445 ,chr(0)
2446 ,submit_request_id);
2447
2448 If submit_request_id = 0 Then
2449 p_result := 'COMPLETE:FAIL';
2450 Else
2451 --Generation pending status
2452 Update_Status(to_number(p_item_key)
2453 ,l_step_number
2454 ,'GP'
2455 );
2456 p_result := 'COMPLETE:PASS';
2457 End If;
2458 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2459 NULL;
2460 End If;
2461
2462 EXCEPTION
2463 WHEN OTHERS THEN
2464 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'SUBMIT_MA_PROGRAM', p_item_type, p_item_key);
2465 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2466 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2467 diagn_debug_msg('SUBMIT_MA_PROGRAM: ' || err_msg ||'*'||err_stack);
2468 END IF;
2469 -- set status code to unexpected fatal error
2470 Update_Status(to_number(p_item_key)
2471 ,l_step_number
2472 ,'UFE'
2473 );
2474 Raise;
2475 End SUBMIT_MA_PROGRAM ;
2476
2477
2478 Procedure SUBMIT_POSTING_PROGRAM(p_item_type IN VARCHAR2,
2479 p_item_key IN VARCHAR2,
2480 p_actid IN NUMBER,
2481 p_funcmode IN VARCHAR2,
2482 p_result OUT NOCOPY VARCHAR2) Is
2483
2484 submit_request_id NUMBER;
2485 l_operating_mode Varchar2(1);
2486 l_coa_id Number;
2487 l_posting_run_id Number;
2488 l_parent_req_id Number := to_number(p_item_key);
2489 l_step_number Number;
2490 l_gen_batch_id Number;
2491 l_batch_request_id Number;
2492 l_access_set_id Number;
2493 l_post_flag VARCHAR2(1) := 'Y';
2494
2495 CURSOR get_request_id_c IS
2496 SELECT jb.request_id,bh.generated_je_batch_id
2497 FROM GL_JE_BATCHES jb,
2498 GL_AUTO_ALLOC_BATCH_HISTORY bh
2499 WHERE bh.request_id = to_number(p_item_key)
2500 AND bh.step_number = l_step_number
2501 AND jb.je_batch_id = bh.generated_je_batch_id;
2502
2503 Begin
2504 If ( p_funcmode = 'RUN' ) THEN
2505 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2506 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2507 diagn_debug_msg('Started SUBMIT_POSTING_PROGRAM');
2508 END IF;
2509
2510 l_operating_mode := wf_engine.GetItemAttrText
2511 ( itemtype => p_item_type,
2512 itemkey => p_item_key,
2513 aname => 'OPERATING_MODE');
2514
2515 l_coa_id := WF_ENGINE.GetItemAttrNumber
2516 (p_item_type,
2517 p_item_key,
2518 'CHART_OF_ACCOUNTS_ID');
2519
2520 l_access_set_id := WF_ENGINE.GetItemAttrNumber
2521 (p_item_type,
2522 p_item_key,
2523 'ACCESS_SET_ID');
2524
2525 If l_operating_mode = 'R' then
2526 l_step_number := -1;
2527 Else
2528 l_step_number := WF_ENGINE.GetItemAttrNumber
2529 (p_item_type,
2530 p_item_key,
2531 'STEP_NUMBER');
2532 End If;
2533
2534 -- Bug fix 1887834
2535 -- Before submit the batch for posting, check and see if it has
2536 -- already been submitted before. This check will be performed
2537 -- on all operating_mode except R.
2538 -- If the batch has been submitted for posting before, just put
2539 -- the request_id into the workflow process and not submit
2540 -- another posting run here.
2541 IF (l_operating_mode <> 'R') THEN
2542 OPEN get_request_id_c;
2543 LOOP
2544 FETCH get_request_id_c into l_batch_request_id,l_gen_batch_id;
2545 EXIT WHEN get_request_id_C%NOTFOUND;
2546
2547 IF(l_batch_request_id IS NOT NULL) THEN
2548 -- put request_id into workflow process, then return.
2549 -- in this way, the deferred thread will late pick this
2550 -- up and check for the posting status.
2551 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2552 diagn_debug_msg('SUBMIT_POSTING_PROGRAM: ' || 'Inserting req id = '||to_char(l_batch_request_id)||
2553 ' into histroy detail');
2554 END IF;
2555
2556 wf_engine.SetItemAttrText(
2557 itemtype => p_item_type,
2558 itemkey => p_item_key,
2559 aname => 'CONC_PRG_CODE',
2560 avalue => 'GLPPOS');
2561
2562 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
2563 itemkey => p_item_key,
2564 aname => 'CONC_REQUEST_ID',
2565 avalue => l_batch_request_id );
2566
2567 INSERT_BATCH_HIST_DET(
2568 p_REQUEST_ID => l_batch_request_id
2569 ,p_PARENT_REQUEST_ID => l_parent_req_id
2570 ,p_STEP_NUMBER => l_step_number
2571 ,p_PROGRAM_NAME_CODE => 'GLPPOS'
2572 ,p_RUN_MODE => l_operating_mode);
2573 ELSE
2574 l_post_flag := 'N';
2575 END IF;
2576 END LOOP;
2577
2578 CLOSE get_request_id_c;
2579
2580 IF l_post_flag = 'Y' THEN
2581 return;
2582 END IF;
2583
2584 END IF;
2585
2586 l_posting_run_id := gl_je_batches_post_pkg.get_unique_id;
2587
2588 Submit_Request( l_parent_req_id
2589 ,l_step_number
2590 ,'GLPPOS'
2591 , to_char(-99)
2592 , to_char(l_access_set_id)
2593 , to_char(l_coa_id)
2594 , To_Char(l_posting_run_id)
2595 , chr(0)
2596 , NULL
2597 , NULL
2598 , NULL
2599 , NULL
2600 , NULL
2601 , NULL
2602 , NULL
2603 , submit_request_id);
2604
2605 If submit_request_id = 0 Then
2606 p_result := 'COMPLETE:FAIL';
2607 Else
2608 If l_operating_mode = 'R' Then
2609 Update GL_JE_BATCHES
2610 Set Posting_Run_Id = l_posting_run_id
2611 ,Status = 'S'
2612 Where JE_BATCH_ID in ( Select GLAARV.REVERSAL_JE_BATCH_ID
2613 From GL_JE_BATCHES GLB
2614 ,GL_AUTO_ALLOC_REV_BATCHES GLAARV
2615 Where GLB.JE_BATCH_ID = GLAARV.REVERSAL_JE_BATCH_ID
2616 AND GLB.STATUS NOT In ('P','I','S')
2617 AND GLAARV.PARENT_REQUEST_ID = to_number(p_item_key) );
2618
2619 --set status to rollback posting pending for all step for which
2620 -- where status is NOT
2621 --[Not started, Rollback Not Required, Rollback Completed]
2622
2623 Update_Status(to_number(p_item_key)
2624 ,-1
2625 ,'RPP'
2626 );
2627 Else
2628 Update GL_JE_BATCHES
2629 Set Posting_Run_Id = l_posting_run_id,
2630 Status = 'S'
2631 Where JE_BATCH_ID IN
2632 ( SELECT bh.generated_je_batch_id
2633 FROM GL_AUTO_ALLOC_BATCH_HISTORY bh,
2634 GL_JE_BATCHES jb
2635 WHERE bh.request_id = to_number(p_item_key)
2636 AND bh.step_number = l_step_number
2637 AND jb.je_batch_id = bh.generated_je_batch_id
2638 AND jb.status NOT IN ('P','I','S')
2639
2640 );
2641
2642 --Posting Pending
2643 Update_Status(to_number(p_item_key)
2644 ,l_step_number
2645 ,'PP'
2646 );
2647 End If;
2648 p_result := 'COMPLETE:PASS';
2649
2650 End If ; /* submit_request_id <> 0 */
2651 ElsIf ( p_funcmode = 'CANCEL' ) THEN
2652 NULL;
2653 End If;
2654 EXCEPTION
2655 WHEN OTHERS THEN
2656 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'SUBMIT_POSTING_PROGRAM', p_item_type, p_item_key);
2657 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2658 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2659 diagn_debug_msg('SUBMIT_POSTING_PROGRAM: ' || err_msg ||'*'||err_stack);
2660 END IF;
2661 Update_Status(to_number(p_item_key)
2662 ,l_step_number
2663 ,'UFE'
2664 );
2665 Raise;
2666 End SUBMIT_POSTING_PROGRAM ;
2667
2668 PROCEDURE Is_je_valid_for_posting(itemtype IN VARCHAR2,
2669 itemkey IN VARCHAR2,
2670 l_je_batch_id IN Number,
2671 l_invalid_error OUT NOCOPY VARCHAR2,
2672 result OUT NOCOPY VARCHAR2 ) IS
2673
2674 l_gen_batch_id NUMBER;
2675 l_batch_name VARCHAR2(100);
2676 l_untaxed_cursor VARCHAR2(20);
2677 l_balance_type VARCHAR2(1);
2678 l_automatic_tax_flag VARCHAR2(1);
2679 l_budgetary_status VARCHAR2(1);
2680 l_control_total NUMBER;
2681 l_running_total_dr NUMBER;
2682 l_running_total_cr NUMBER;
2683 l_ledger_id NUMBER;
2684 l_period_name VARCHAR2(15);
2685 l_period_status VARCHAR2(1);
2686 l_start_date DATE;
2687 l_end_date DATE;
2688 l_period_num NUMBER;
2689 l_period_year NUMBER;
2690 l_latest_encumbrance_year NUMBER;
2691 l_budget_version_id NUMBER;
2692 l_status VARCHAR2(1);
2693 l_operating_mode VARCHAR2(1);
2694 l_approval_status_code VARCHAR2(1);
2695 c_je_batch_id VARCHAR2(240) := to_char(l_je_batch_id);
2696
2697 CURSOR check_untaxed IS
2698 SELECT 'untaxed journals'
2699 FROM DUAL
2700 WHERE EXISTS
2701 (SELECT 'UNTAXED'
2702 FROM GL_JE_HEADERS JEH,
2703 GL_LEDGERS LGR
2704 WHERE JEH.je_batch_id = l_je_batch_id
2705 AND JEH.tax_status_code = 'R'
2706 AND JEH.currency_code <> 'STAT'
2707 AND JEH.je_source = 'Manual'
2708 AND LGR.ledger_id = JEH.ledger_id
2709 AND LGR.ledger_category_code <> 'NONE'
2710 AND LGR.enable_automatic_tax_flag = 'Y');
2711
2712 Cursor get_je_batch_attributes_C IS
2713 Select
2714 NAME
2715 ,CONTROL_TOTAL
2716 ,RUNNING_TOTAL_DR
2717 ,RUNNING_TOTAL_CR
2718 ,DEFAULT_PERIOD_NAME
2719 ,ACTUAL_FLAG
2720 ,BUDGETARY_CONTROL_STATUS
2721 ,STATUS
2722 ,APPROVAL_STATUS_CODE
2723 From GL_JE_BATCHES
2724 Where JE_BATCH_ID = l_je_batch_id;
2725
2726
2727 CURSOR get_je_header_attributes_C IS
2728 SELECT
2729 jh.LEDGER_ID,
2730 jh.BUDGET_VERSION_ID,
2731 lgr.LATEST_ENCUMBRANCE_YEAR
2732 FROM GL_JE_HEADERS jh, GL_LEDGERS lgr
2733 WHERE jh.JE_BATCH_ID = l_je_batch_id
2734 AND lgr.LEDGER_ID = jh.LEDGER_ID
2735 AND lgr.LEDGER_CATEGORY_CODE <> 'NONE';
2736
2737 Begin
2738
2739 Open get_je_batch_attributes_C;
2740 Fetch get_je_batch_attributes_C into
2741 l_batch_name
2742 ,l_control_total
2743 ,l_running_total_dr
2744 ,l_running_total_cr
2745 ,l_period_name
2746 ,l_balance_type
2747 ,l_budgetary_status
2748 ,l_status
2749 ,l_approval_status_code;
2750
2751 If get_je_batch_attributes_C%NOTFOUND Then
2752 Close get_je_batch_attributes_C;
2753 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2754 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Generated JE batch not found '||c_je_batch_id);
2755 END IF;
2756 FND_MESSAGE.Set_Name('SQLGL', 'GL_JE_BATCH_NOT_FOUND');
2757 FND_MESSAGE.Set_Token('BATCH',l_batch_name);
2758 l_invalid_error := FND_MESSAGE.Get;
2759 result := 'N';
2760 return;
2761 Elsif l_approval_status_code = 'R' Then
2762 l_operating_mode := wf_engine.GetItemAttrText
2763 ( itemtype => p_item_type,
2764 itemkey => p_item_key,
2765 aname => 'OPERATING_MODE');
2766 If l_operating_mode = 'R' Then
2767 --during rollback, we don't require approval
2768 Update GL_JE_BATCHES
2769 Set approval_status_code = 'Z'
2770 Where je_batch_id = l_je_batch_id;
2771
2772 result := 'Y';
2773 Else
2774 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2775 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Batch approval require before posting');
2776 END IF;
2777 FND_MESSAGE.Set_Name('SQLGL', 'GL_JE_BATCH_APPROVAL_REQ');
2778 FND_MESSAGE.Set_Token('BATCH',l_batch_name);
2779 l_invalid_error := FND_MESSAGE.Get;
2780 result := 'N';
2781 return;
2782 End If;
2783 Elsif l_approval_status_code = 'I' Then
2784 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2785 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Batch '||To_char(l_je_batch_id)||
2786 ' approval launched but batch is not yet approved');
2787 END IF;
2788 FND_MESSAGE.Set_Name('SQLGL', 'GL_APPROVAL_NOT_COMPLETE');
2789 FND_MESSAGE.Set_Token('BATCH',l_batch_name);
2790 l_invalid_error := FND_MESSAGE.Get;
2791 result := 'N';
2792 return;
2793 ElsIf l_status = 'P' then
2794 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2795 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Batch already posted '||to_char(l_je_batch_id));
2796 END IF;
2797 result := 'P';
2798 return;
2799 ElsIf l_budgetary_status = 'I' then
2800 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2801 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Batch '||To_char(l_je_batch_id)||
2802 ' fund checker is in process');
2803 END IF;
2804 FND_MESSAGE.Set_Name('SQLGL', 'GL_FUND_CHECK_IN_PROCESS');
2805 FND_MESSAGE.Set_Token('BATCH',l_batch_name);
2806 l_invalid_error := FND_MESSAGE.Get;
2807 result := 'N';
2808 return;
2809 End If;
2810
2811 -- Check whether the batch contains untaxed journals or unreserved
2812 -- funds.
2813 IF l_balance_type = 'A' THEN
2814 OPEN check_untaxed ;
2815 FETCH check_untaxed INTO l_untaxed_cursor;
2816
2817 IF check_untaxed%FOUND THEN
2818 CLOSE check_untaxed;
2819 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_UNTAXED');
2820 l_invalid_error := FND_MESSAGE.Get;
2821 result := 'N';
2822 return;
2823 ELSE
2824 CLOSE check_untaxed;
2825 END IF;
2826 END IF;
2827
2828
2829 -- Check for the postability of the batch
2830 OPEN get_je_header_attributes_C;
2831 LOOP
2832 FETCH get_je_header_attributes_C into l_ledger_id, l_budget_version_id, l_latest_encumbrance_year;
2833 EXIT when get_je_header_attributes_C%NOTFOUND;
2834
2835 -- Get the period year
2836 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2837 diagn_debug_msg('Is_je_valid_for_posting: ' || 'Ledger = '||to_char(l_ledger_id)||' Period = '||l_period_name );
2838 END IF;
2839 GL_PERIOD_STATUSES_PKG.select_columns(
2840 101,
2841 l_ledger_id,
2842 l_period_name,
2843 l_period_status,
2844 l_start_date,
2845 l_end_date,
2846 l_period_num,
2847 l_period_year);
2848
2849 IF ( l_balance_type = 'A') THEN
2850 -- Check that the batch period is open for actual batches
2851 BEGIN
2852 IF (nvl(l_period_status,'X') NOT IN ('O', 'F')) THEN
2853 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_PERIOD_NOT_OPEN');
2854 l_invalid_error := FND_MESSAGE.Get;
2855 result := 'N';
2856 return;
2857 END IF;
2858 END;
2859
2860 ELSIF (l_balance_type = 'B') THEN
2861 -- Check that the budget is valid, budget year is open and
2862 -- the period is within the valid range of periods.
2863 DECLARE
2864 CURSOR chk_budgets IS
2865 SELECT max(decode(bud.status, 'I', 1, 'F', 1, 0)),
2866 max(l_period_year - bud.latest_opened_year)
2867 FROM GL_BUDGET_VERSIONS BV,
2868 GL_BUDGETS BUD
2869 WHERE BV.budget_version_id = l_budget_version_id
2870 AND BUD.budget_type = BV.budget_type
2871 AND BUD.budget_name = BV.budget_name;
2872
2873 frozen_budget NUMBER;
2874 year_violation NUMBER;
2875 BEGIN
2876 OPEN chk_budgets;
2877 FETCH chk_budgets INTO frozen_budget,
2878 year_violation;
2879 CLOSE chk_budgets;
2880
2881 IF (frozen_budget = 1) THEN
2882 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_FROZEN_BUDGET');
2883 l_invalid_error := FND_MESSAGE.Get;
2884 result := 'N';
2885 return;
2886 ELSIF (year_violation = 1) THEN
2887 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_BUDGET_PERIOD');
2888 l_invalid_error := FND_MESSAGE.Get;
2889 result := 'N';
2890 return;
2891 END IF;
2892 END ;
2893
2894 ELSE
2895 -- Make sure that for encumbrance batches, the
2896 -- batch is within an open encumbrance year
2897 IF (l_period_year > l_latest_encumbrance_year) THEN
2898 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_ENC_YEAR');
2899 l_invalid_error := FND_MESSAGE.Get;
2900 result := 'N';
2901 return;
2902 END IF;
2903
2904 END IF;
2905
2906 END LOOP;
2907 CLOSE get_je_header_attributes_C;
2908
2909 -- Make sure the control total matches the
2910 -- running totals
2911 IF ( (l_control_total IS NULL)
2912 OR ( (l_balance_type IN ('A', 'E'))
2913 AND (l_running_total_dr = l_control_total))
2914 OR ( (l_balance_type = 'B')
2915 AND (greatest(l_running_total_cr, l_running_total_dr)
2916 = l_control_total))
2917
2918 ) THEN
2919 null;
2920 ELSE
2921 FND_MESSAGE.Set_Name('SQLGL', 'GL_WF_INVALID_CONTROL_TOTAL');
2922 l_invalid_error := FND_MESSAGE.Get;
2923 result := 'N';
2924 return;
2925 END IF;
2926
2927 -- If the batch passes all the above checks, then its valid.
2928 result := 'Y';
2929
2930 EXCEPTION
2931 WHEN OTHERS THEN
2932 Wf_Core.Context('GL_WF_JE_APPROVAL_PKG', 'is_je_valid_for_posting', itemtype, itemkey);
2933 Wf_Core.Get_Error(err_name,err_msg,err_stack);
2934 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2935 diagn_debug_msg('Is_je_valid_for_posting: ' || err_msg ||'*'||err_stack);
2936 END IF;
2937 Raise;
2938 END is_je_valid_for_posting;
2939
2940
2941 Procedure SUBMIT_RJE_PROGRAM(p_item_type IN VARCHAR2,
2942 p_item_key IN VARCHAR2,
2943 p_actid IN NUMBER,
2944 p_funcmode IN VARCHAR2,
2945 p_result OUT NOCOPY VARCHAR2) Is
2946
2947 submit_request_id NUMBER;
2948 l_usage_code Varchar2(1);
2949 l_access_set_id Number;
2950 l_batch_id Number;
2951 l_step_number Number;
2952 l_period_name Varchar2(15);
2953 l_journal_effective_date Date;
2954 l_calc_effective_date Date;
2955 l_budget_version_id Number;
2956 l_parent_req_id Number := to_number(p_item_key);
2957 Begin
2958 If ( p_funcmode = 'RUN' ) THEN
2959 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
2960 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
2961 diagn_debug_msg('Started SUBMIT_RJE_PROGRAM');
2962 END IF;
2963
2964 l_usage_code := WF_ENGINE.GetItemAttrText
2965 (p_item_type,
2966 p_item_key,
2967 'USAGE_CODE');
2968 l_access_set_id := WF_ENGINE.GetItemAttrNumber
2969 (p_item_type,
2970 p_item_key,
2971 'ACCESS_SET_ID');
2972 l_batch_id := WF_ENGINE.GetItemAttrNumber
2973 (p_item_type,
2974 p_item_key,
2975 'BATCH_ID');
2976 l_step_number := WF_ENGINE.GetItemAttrNumber
2977 (p_item_type,
2978 p_item_key,
2979 'STEP_NUMBER');
2980 l_period_name := WF_ENGINE.GetItemAttrText
2981 (p_item_type,
2982 p_item_key,
2983 'PERIOD_NAME');
2984 l_journal_effective_date := WF_ENGINE.GetItemAttrDate
2985 (p_item_type,
2986 p_item_key,
2987 'JOURNAL_EFFECTIVE_DATE');
2988 l_calc_effective_date := WF_ENGINE.GetItemAttrDate
2989 (p_item_type,
2990 p_item_key,
2991 'CALCULATION_EFFECTIVE_DATE');
2992
2993 l_budget_version_id := wf_engine.getItemAttrNumber
2994 ( p_item_type,
2995 p_item_key,
2996 'BUDGET_VERSION_ID');
2997 Submit_Request( l_parent_req_id
2998 ,l_step_number
2999 , 'GLPRJE'
3000 , to_char(l_batch_id)
3001 , l_period_name
3002 , to_char(l_access_set_id)
3003 , to_char(l_budget_version_id)
3004 , to_char(l_calc_effective_date,'YYYY/MM/DD')
3005 , to_char(l_journal_effective_date,'YYYY/MM/DD')
3006 , l_usage_code
3007 , chr(0)
3008 , NULL
3009 , NULL
3010 , NULL
3011 , NULL
3012 , submit_request_id);
3013
3014 If submit_request_id = 0 Then
3015 p_result := 'COMPLETE:FAIL';
3016 Return;
3017 Else
3018 --Generation pending status
3019 Update_Status(to_number(p_item_key)
3020 ,l_step_number
3021 ,'GP'
3022 );
3023 p_result := 'COMPLETE:PASS';
3024 Return;
3025 End If;
3026 ElsIf ( p_funcmode = 'CANCEL' ) THEN
3027 NULL;
3028 End If;
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'SUBMIT_RJE_PROGRAM', p_item_type, p_item_key);
3032 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3033 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3034 diagn_debug_msg('SUBMIT_RJE_PROGRAM: ' || err_msg ||'*'||err_stack);
3035 END IF;
3036 -- set status code to unexpected fatal error
3037 Update_Status(to_number(p_item_key)
3038 ,l_step_number
3039 ,'UFE'
3040 );
3041
3042 Raise;
3043 End SUBMIT_RJE_PROGRAM ;
3044
3045 Procedure SUBMIT_REV_PROGRAM(p_item_type IN VARCHAR2,
3046 p_item_key IN VARCHAR2,
3047 p_actid IN NUMBER,
3048 p_funcmode IN VARCHAR2,
3049 p_result OUT NOCOPY VARCHAR2) Is
3050
3051 submit_request_id NUMBER;
3052 l_step_number Number;
3053 reversal_req_id Number;
3054 l_je_header_id Number;
3055 l_access_set_id Number;
3056 l_period_name Varchar2(15);
3057 l_parent_req_id Number := to_number(p_item_key);
3058 Begin
3059 If ( p_funcmode = 'RUN' ) THEN
3060 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
3061 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3062 diagn_debug_msg('Started SUBMIT_REV_PROGRAM');
3063 END IF;
3064
3065 l_access_set_id := WF_ENGINE.GetItemAttrNumber
3066 (p_item_type,
3067 p_item_key,
3068 'ACCESS_SET_ID');
3069
3070 l_je_header_id := WF_ENGINE.GetItemAttrNumber
3071 (p_item_type,
3072 p_item_key,
3073 'JE_HEADER_ID');
3074 l_step_number := WF_ENGINE.GetItemAttrNumber
3075 (p_item_type,
3076 p_item_key,
3077 'STEP_NUMBER');
3078
3079 Submit_Request( l_parent_req_id
3080 ,l_step_number
3081 ,'GLPREV'
3082 , to_char(l_access_set_id)
3083 , to_char(l_je_header_id)
3084 , chr(0)
3085 , NULL
3086 , NULL
3087 , NULL
3088 , NULL
3089 , NULL
3090 , NULL
3091 , NULL
3092 , NULL
3093 , NULL
3094 , submit_request_id);
3095
3096 If submit_request_id = 0 Then
3097 p_result := 'COMPLETE:FAIL';
3098 Return;
3099 Else
3100 l_period_name := WF_ENGINE.GetItemAttrText
3101 (p_item_type,
3102 p_item_key,
3103 'PERIOD_NAME');
3104
3105 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3106 diagn_debug_msg('SUBMIT_REV_PROGRAM: ' || 'Updating accrual_rev_period_name = '||l_period_name);
3107 END IF;
3108
3109 Update GL_JE_HEADERS
3110 Set accrual_rev_flag = 'Y'
3111 , accrual_rev_period_name = l_period_name
3112 , accrual_rev_effective_date = decode(actual_flag,'A',
3113 default_effective_date,accrual_rev_effective_date)
3114 Where je_header_id = l_je_header_id;
3115
3116 --Rollback reversal pending status for this step
3117 Update_Status(to_number(p_item_key)
3118 ,l_step_number
3119 ,'RRP'
3120 );
3121 p_result := 'COMPLETE:PASS';
3122 Return;
3123 End If;
3124 ElsIf ( p_funcmode = 'CANCEL' ) THEN
3125 NULL;
3126 End If;
3127 EXCEPTION
3128 WHEN OTHERS THEN
3129 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'SUBMIT_REV_PROGRAM', p_item_type, p_item_key);
3130 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3131 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3132 diagn_debug_msg('SUBMIT_REV_PROGRAM: ' || err_msg ||'*'||err_stack);
3133 END IF;
3134 -- set status code to unexpected fatal error
3135 Update_Status(to_number(p_item_key)
3136 ,l_step_number
3137 ,'RUFE'
3138 );
3139 Raise;
3140 End SUBMIT_REV_PROGRAM ;
3141
3142 Procedure WAITING_TO_COMPLETE(p_item_type IN VARCHAR2,
3143 p_item_key IN VARCHAR2,
3144 p_actid IN NUMBER,
3145 p_funcmode IN VARCHAR2,
3146 p_result OUT NOCOPY VARCHAR2) Is
3147
3148 l_request_id Number;
3149 p_phase Varchar2(80);
3150 p_status Varchar2(80);
3151 p_dev_phase Varchar2(80);
3152 p_dev_status Varchar2(80);
3153 p_message Varchar2(240) ;
3154 l_call_status Boolean;
3155 l_step_number Number;
3156 l_conc_prg_code Varchar2(15);
3157 l_status_code Varchar2(15);
3158 l_rollback_allowed Varchar2(1);
3159 l_message_Name Varchar2(150);
3160
3161 l_user_id Number;
3162 l_org_id Number;
3163 l_resp_id Number;
3164 l_resp_appl_id Number;
3165
3166 l_profile_value Number;
3167
3168 l_userenv_lang VARCHAR2(50);
3169 l_client_info Varchar2(240);
3170 l_wait_error Varchar2(240);
3171
3172 Begin
3173 If ( p_funcmode = 'RUN' ) THEN
3174 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
3175 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3176 diagn_debug_msg('Started WAITING_TO_COMPLETE');
3177 END IF;
3178
3179 l_request_id := WF_ENGINE.GetItemAttrNumber
3180 (p_item_type,
3181 p_item_key,
3182 'CONC_REQUEST_ID');
3183 l_step_number := WF_ENGINE.GetItemAttrNumber
3184 (p_item_type,
3185 p_item_key,
3186 'STEP_NUMBER');
3187
3188 l_conc_prg_code := WF_ENGINE.GetItemAttrText
3189 (p_item_type,
3190 p_item_key,
3191 'CONC_PRG_CODE');
3192 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3193 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'l_conc_prg_code = '||l_conc_prg_code);
3194 END IF;
3195 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
3196 p_item_type,
3197 p_item_key,
3198 'ROLLBACK_ALLOWED');
3199
3200 l_user_id := WF_ENGINE.GetItemAttrNumber
3201 (p_item_type,
3202 p_item_key,
3203 'USER_ID');
3204
3205 l_org_id := WF_ENGINE.GetItemAttrNumber
3206 (p_item_type,
3207 p_item_key,
3208 'ORG_ID');
3209
3210 l_resp_id := WF_ENGINE.GetItemAttrNumber
3211 (p_item_type,
3212 p_item_key,
3213 'RESP_ID');
3214
3215 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
3216 (p_item_type,
3217 p_item_key,
3218 'RESP_APPL_ID');
3219
3220 select userenv('LANG') into l_userenv_lang from dual;
3221 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3222 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'LANG='||l_userenv_lang);
3223 END IF;
3224 select userenv('CLIENT_INFO') into l_client_info from dual;
3225 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3226 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'CLINT_INFO='||l_client_info);
3227 END IF;
3228
3229 FND_PROFILE.put('ORG_ID', l_org_id);
3230 FND_PROFILE.put('USER_ID', l_user_id );
3231 FND_PROFILE.put('RESP_ID', l_resp_id);
3232 FND_PROFILE.put('RESP_APPL_ID', l_resp_appl_id);
3233
3234 FND_PROFILE.get('STEP_DOWN_INTERVAL',l_profile_value);
3235
3236 IF (l_profile_value is NULL) THEN
3237 l_profile_value := 30;
3238 END IF;
3239
3240 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3241 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Con Request Id = '||to_char(l_request_id));
3242 END IF;
3243 l_call_status :=
3244 Fnd_Concurrent.Wait_For_Request(
3245 request_id => l_request_id
3246 ,Interval => l_profile_value
3247 ,Max_wait => 360000
3248 ,phase => p_phase
3249 ,status => p_status
3250 ,dev_phase => p_dev_phase
3251 ,dev_status => p_dev_status
3252 ,message => p_message );
3253 If p_dev_phase = 'COMPLETE' AND
3254 p_dev_status In ('NORMAL','WARNING' ) Then
3255 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3256 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Completed concurrent program = '||to_char(l_request_id) );
3257 END IF;
3258
3259 -- set status code program completed
3260 Get_Status_and_Message(l_conc_prg_code
3261 ,'COMPLETE'
3262 ,l_rollback_allowed
3263 ,l_status_code
3264 ,l_message_name);
3265
3266 Update_Status(to_number(p_item_key)
3267 ,l_step_number
3268 ,l_status_code
3269 );
3270
3271 p_result := 'COMPLETE:PASS';
3272 return;
3273 Else
3274 If NOT (l_call_status ) Then
3275 l_wait_error := fnd_message.get;
3276 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3277 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Wait failure message='||l_wait_error);
3278 END IF;
3279 End If;
3280 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3281 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Sending Notification. Concurrent program not completed = '||
3282 to_char(l_request_id) );
3283 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'message='||p_message||'Dev_Phase = '
3284 ||p_dev_phase||' Dev_Status ='||p_dev_status);
3285 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Phase = '||p_phase||' Status ='||p_status);
3286 END IF;
3287 Get_Status_and_Message(l_conc_prg_code
3288 ,'ERROR'
3289 ,l_rollback_allowed
3290 ,l_status_code
3291 ,l_message_name);
3292
3293 -- Program Error
3294 Update_Status(to_number(p_item_key)
3295 ,l_step_number
3296 ,l_status_code
3297 );
3298
3299 wf_engine.SetItemAttrText(itemtype => p_item_type,
3300 itemkey => p_item_key,
3301 aname => 'MESSAGE_NAME',
3302 avalue => l_message_name );
3303
3304 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3305 diagn_debug_msg('WAITING_TO_COMPLETE: ' || 'Message_name = '||l_message_Name);
3306 END IF;
3307
3308 p_result := 'COMPLETE:FAIL';
3309 return;
3310 End If;
3311 ElsIf ( p_funcmode = 'CANCEL' ) THEN
3312 NULL;
3313 End If;
3314 EXCEPTION
3315 When Others Then
3316 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'WAITING_TO_COMPLETE', p_item_type, p_item_key);
3317 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3318 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3319 diagn_debug_msg('WAITING_TO_COMPLETE: ' || err_msg ||'*'||err_stack);
3320 END IF;
3321 Update_Status(to_number(p_item_key)
3322 ,l_step_number
3323 ,'UFE'
3324 );
3325
3326 Raise;
3327
3328 End WAITING_TO_COMPLETE ;
3329
3330 Procedure VAL_SET_FOR_ROLLBACK(p_item_type IN VARCHAR2,
3331 p_item_key IN VARCHAR2,
3332 p_actid IN NUMBER,
3333 p_funcmode IN VARCHAR2,
3334 p_result OUT NOCOPY VARCHAR2) Is
3335
3336 l_step_number Number;
3337 l_batch_id Number;
3338 l_batch_type_code Varchar2(1);
3339 l_gen_Batch_Id Number;
3340 l_complete_flag Varchar2(1);
3341 no_rows Varchar2(1) := 'Y';
3342 l_status Varchar2(1);
3343 gen_but_not_posted BOOLEAN := FALSE;
3344 l_gen_batch_name Varchar2(100);
3345
3346 Cursor Validate_Steps_C IS
3347 Select
3348 H.Step_Number
3349 ,H.Batch_Id
3350 ,H.BATCH_TYPE_CODE
3351 ,H.GENERATED_JE_BATCH_ID
3352 ,H.COMPLETE_FLAG
3353 ,JEB.Status
3354 From GL_JE_BATCHES JEB
3355 ,GL_AUTO_ALLOC_BATCH_HISTORY H
3356 Where
3357 JEB.JE_BATCH_ID = H.GENERATED_JE_BATCH_ID
3358 AND H.REQUEST_ID = to_number(p_item_key)
3359 Order By H.STEP_NUMBER Desc;
3360
3361 Cursor Set_Rollback_Context_C IS
3362 Select
3363 H.Step_Number
3364 ,H.Batch_Id
3365 ,H.BATCH_TYPE_CODE
3366 ,H.GENERATED_JE_BATCH_ID
3367 ,JEB.Name
3368 From GL_JE_BATCHES JEB
3369 ,GL_AUTO_ALLOC_BATCH_HISTORY H
3370 Where JEB.JE_BATCH_ID = H.GENERATED_JE_BATCH_ID
3371 AND H.REQUEST_ID = to_number(p_item_key)
3372 AND H.COMPLETE_FLAG = 'Y'
3373 Order By H.STEP_NUMBER Desc;
3374
3375 Begin
3376 If ( p_funcmode = 'RUN' ) THEN
3377 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
3378 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3379 diagn_debug_msg('***************************************');
3380 diagn_debug_msg('Rollback Started VAL_SET_FOR_ROLLBACK');
3381 diagn_debug_msg('***************************************');
3382 END IF;
3383 wf_engine.SetItemAttrText( itemtype => p_item_type,
3384 itemkey => p_item_key,
3385 aname => 'OPERATING_MODE',
3386 avalue => 'R');
3387 Open Validate_Steps_C;
3388 LOOP
3389 Fetch Validate_Steps_C Into
3390 l_step_number
3391 ,l_batch_id
3392 ,l_batch_type_code
3393 ,l_gen_batch_Id
3394 ,l_complete_flag
3395 ,l_status;
3396
3397 If Validate_Steps_C%NOTFOUND Then
3398 If No_Rows = 'Y' Then
3399 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3400 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'No batch is generated. Rollback is not necessary');
3401 END IF;
3402 End If;
3403 Close Validate_Steps_C;
3404 Exit;
3405 Else
3406 No_Rows := 'N';
3407 If l_status = 'P' Then
3408 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3409 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Batch was posted');
3410 END IF;
3411 If l_complete_flag <> 'Y' OR
3412 l_complete_flag IS NULL Then
3413 --shouldn't happen but still not a fatal error
3414 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3415 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Batch is posted.Complete flag=N .Marking step as completed');
3416 END IF;
3417 Update GL_AUTO_ALLOC_BATCH_HISTORY
3418 Set COMPLETE_FLAG = 'Y'
3419 Where REQUEST_ID = to_number(p_item_key)
3420 And STEP_NUMBER = l_step_number;
3421 --And BATCH_ID = l_batch_id
3422 --And BATCH_TYPE_CODE = l_batch_type_code;
3423 End IF;
3424 Else
3425 If NOT gen_but_not_posted Then
3426 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3427 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Found first batch generated but not posted');
3428 END IF;
3429 gen_but_not_posted := TRUE;
3430 Else
3431 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3432 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || ' More then one step is generated but not posted');
3433 END IF;
3434 End If;
3435 End If; /*l_Status*/
3436 End IF; /*Validate_Steps_C*/
3437 End LOOP;
3438
3439 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3440 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Verified all steps.');
3441 END IF;
3442 Open Set_Rollback_Context_C;
3443 Fetch Set_Rollback_Context_C into
3444 l_step_number
3445 ,l_batch_id
3446 ,l_batch_type_code
3447 ,l_gen_batch_id
3448 ,l_gen_batch_name;
3449
3450 If Set_Rollback_Context_C%NOTFOUND Then
3451 l_batch_id := NULL;
3452 l_batch_type_code := NULL;
3453 l_gen_batch_id := NULL;
3454 l_gen_batch_name := NULL;
3455 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3456 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'No step for reversal');
3457 END IF;
3458 End If;
3459
3460 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3461 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Setting up context now');
3462 END IF;
3463 wf_engine.SetItemAttrNumber(
3464 itemtype => p_item_type,
3465 itemkey => p_item_key,
3466 aname => 'BATCH_ID',
3467 avalue => l_batch_id );
3468
3469 wf_engine.SetItemAttrtext(
3470 itemtype => p_item_type,
3471 itemkey => p_item_key,
3472 aname => 'BATCH_TYPE_CODE',
3473 avalue => l_batch_type_code );
3474
3475 wf_engine.SetItemAttrNumber(
3476 itemtype => p_item_type,
3477 ITEMkey => p_item_key,
3478 aname => 'STEP_NUMBER',
3479 avalue => l_step_number );
3480
3481 wf_engine.SetItemAttrNumber(
3482 itemtype => p_item_type,
3483 itemkey => p_item_key,
3484 aname => 'GEN_BATCH_ID',
3485 avalue => l_gen_batch_id );
3486
3487 wf_engine.SetItemAttrtext(
3488 itemtype => p_item_type,
3489 itemkey => p_item_key,
3490 aname => 'GEN_BATCH_NAME',
3491 avalue => l_gen_batch_name );
3492
3493 Close Set_Rollback_Context_C;
3494 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3495 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Rollback: Set is validated for rollback');
3496 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || 'Rollback Context: Step = '||To_char(l_step_number)||
3497 ' Batch_id = '||To_char(l_batch_id)||
3498 ' Gen_Batch_Id = '||To_char(l_Gen_Batch_Id));
3499 END IF;
3500
3501 Return;
3502 ElsIf ( p_funcmode = 'CANCEL' ) THEN
3503 NULL;
3504 End If;
3505 EXCEPTION
3506 WHEN OTHERS THEN
3507 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'VAL_SET_FOR_ROLLBACK', p_item_type, p_item_key);
3508 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3509 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3510 diagn_debug_msg('VAL_SET_FOR_ROLLBACK: ' || err_msg ||'*'||err_stack);
3511 END IF;
3512 Update_Status(to_number(p_item_key)
3513 ,l_step_number
3514 ,'RUFE'
3515 );
3516
3517 Raise;
3518 End VAL_SET_FOR_ROLLBACK ;
3519
3520 Procedure End_Fail(p_item_type IN VARCHAR2,
3521 p_item_key IN VARCHAR2,
3522 p_actid IN NUMBER,
3523 p_funcmode IN VARCHAR2,
3524 p_result OUT NOCOPY VARCHAR2) Is
3525
3526 l_step_number Number;
3527 l_operating_mode Varchar2(1);
3528
3529 Begin
3530 If ( p_funcmode = 'RUN' ) THEN
3531 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
3532 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3533 diagn_debug_msg('Entering end_fail');
3534 END IF;
3535 l_operating_mode := WF_ENGINE.GetItemAttrText
3536 (p_item_type,
3537 p_item_key,
3538 'OPERATING_MODE');
3539 l_step_number := WF_ENGINE.GetItemAttrNumber
3540 (p_item_type,
3541 p_item_key,
3542 'STEP_NUMBER');
3543
3544 If l_operating_mode = 'R' Then
3545 Update_Status(to_number(p_item_key)
3546 ,l_step_number
3547 ,'RST'
3548 );
3549 Else
3550 Update_Status(to_number(p_item_key)
3551 ,l_step_number
3552 ,'ST'
3553 );
3554 End If;
3555
3556 Return;
3557 ElsIf ( p_funcmode = 'CANCEL' ) THEN
3558 NULL;
3559 End If;
3560 EXCEPTION
3561 WHEN OTHERS THEN
3562 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'END_FAIL', p_item_type, p_item_key);
3563 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3564 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3565 diagn_debug_msg('End_Fail: ' || err_msg ||'*'||err_stack);
3566 END IF;
3567 Raise;
3568 End End_Fail ;
3569
3570 procedure Selector_Func (p_item_type IN VARCHAR2,
3571 p_item_key IN VARCHAR2,
3572 p_actid IN NUMBER,
3573 p_funcmode IN VARCHAR2,
3574 p_result OUT NOCOPY VARCHAR2) IS
3575 Begin
3576 If ( p_funcmode = 'RUN' ) THEN
3577 NULL;
3578 ElsIf ( p_funcmode = 'SET_CTX') Then
3579 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
3580 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3581 diagn_debug_msg('Entering Selector_Func');
3582 END IF;
3583 -- If diagn_debug_msg_flag Then
3584 -- DBMS_SESSION.SET_SQL_TRACE(TRUE);
3585 -- End If;
3586 End If;
3587
3588 EXCEPTION
3589 WHEN OTHERS THEN
3590 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'SELECTOR_FUNC', p_item_type, p_item_key);
3591 Wf_Core.Get_Error(err_name,err_msg,err_stack);
3592 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3593 diagn_debug_msg('Selector_Func: ' || err_msg ||'*'||err_stack);
3594 END IF;
3595 Raise;
3596 End Selector_Func ;
3597
3598
3599
3600 Procedure INSERT_BATCH_HIST_DET(
3601 p_request_id IN NUMBER
3602 ,p_parent_request_id IN NUMBER
3603 ,p_step_number IN NUMBER
3604 ,p_program_name_code IN VARCHAR2
3605 ,p_run_mode IN VARCHAR2
3606 ,p_allocation_type IN VARCHAR2 DEFAULT 'GL'
3607 ,p_created_by IN NUMBER DEFAULT -1
3608 ,p_last_updated_by IN NUMBER DEFAULT -1
3609 ,p_last_update_login IN NUMBER DEFAULT -1
3610 ) IS
3611
3612 l_CREATED_BY NUMBER;
3613 l_LAST_UPDATED_BY NUMBER;
3614 l_LAST_UPDATE_LOGIN NUMBER;
3615
3616 f_step_number NUMBER;
3617
3618 Cursor get_steps_C IS
3619 Select step_number
3620 From GL_AUTO_ALLOC_BATCH_HISTORY
3621 WHERE request_Id = p_PARENT_REQUEST_ID
3622 AND Status_Code Not In ( 'NS','RNR','RC');
3623
3624 Begin
3625 If p_allocation_type = 'GL' Then
3626 get_standard_who(l_CREATED_BY
3627 ,l_LAST_UPDATED_BY
3628 ,l_LAST_UPDATE_LOGIN
3629 );
3630 Else
3631 l_created_by := p_created_by;
3632 l_last_updated_by := p_last_updated_by;
3633 l_last_update_login := p_last_update_login ;
3634 End If;
3635
3636 If p_STEP_NUMBER <> -1 AND p_STEP_NUMBER IS NOT NULL Then
3637 Insert Into GL_AUTO_ALLOC_BAT_HIST_DET
3638 (REQUEST_ID
3639 ,PARENT_REQUEST_ID
3640 ,STEP_NUMBER
3641 ,PROGRAM_NAME_CODE
3642 ,LAST_UPDATE_DATE
3643 ,LAST_UPDATED_BY
3644 ,LAST_UPDATE_LOGIN
3645 ,CREATION_DATE
3646 ,CREATED_BY
3647 ,STATUS_CODE
3648 ,RUN_MODE
3649 )
3650 Values
3651 (p_REQUEST_ID
3652 ,p_PARENT_REQUEST_ID
3653 ,p_STEP_NUMBER
3654 ,p_PROGRAM_NAME_CODE
3655 ,sysdate
3656 ,l_LAST_UPDATED_BY
3657 ,l_LAST_UPDATE_LOGIN
3658 ,sysdate
3659 ,l_CREATED_BY
3660 ,NULL
3661 ,p_RUN_MODE);
3662 ElsIf p_STEP_NUMBER = -1 Then
3663 -- this request id need to be inserted for each step
3664 -- This happenns for rollback posting process only
3665 Open get_steps_C;
3666 LOOP
3667 Fetch get_steps_C into f_step_number;
3668 EXIT WHEN get_steps_C%NOTFOUND;
3669 Insert Into GL_AUTO_ALLOC_BAT_HIST_DET
3670 (REQUEST_ID
3671 ,PARENT_REQUEST_ID
3672 ,STEP_NUMBER
3673 ,PROGRAM_NAME_CODE
3674 ,LAST_UPDATE_DATE
3675 ,LAST_UPDATED_BY
3676 ,LAST_UPDATE_LOGIN
3677 ,CREATION_DATE
3678 ,CREATED_BY
3679 ,STATUS_CODE
3680 ,RUN_MODE
3681 )
3682 Values
3683 (p_REQUEST_ID
3684 ,p_PARENT_REQUEST_ID
3685 ,f_step_number
3686 ,p_PROGRAM_NAME_CODE
3687 ,sysdate
3688 ,l_LAST_UPDATED_BY
3689 ,l_LAST_UPDATE_LOGIN
3690 ,sysdate
3691 ,l_CREATED_BY
3692 ,NULL
3693 ,p_RUN_MODE);
3694 End Loop;
3695 End If;
3696 End INSERT_BATCH_HIST_DET ;
3697
3698 Procedure get_standard_who (
3699 l_CREATED_BY OUT NOCOPY NUMBER
3700 ,l_LAST_UPDATED_BY OUT NOCOPY NUMBER
3701 ,l_LAST_UPDATE_LOGIN OUT NOCOPY NUMBER ) IS
3702 Begin
3703 l_CREATED_BY:= WF_ENGINE.GetItemAttrNumber
3704 (p_item_type,
3705 p_item_key,
3706 'CREATED_BY');
3707
3708 l_LAST_UPDATED_BY:= WF_ENGINE.GetItemAttrNumber
3709 (p_item_type,
3710 p_item_key,
3711 'LAST_UPDATED_BY');
3712
3713 l_LAST_UPDATE_LOGIN:= WF_ENGINE.GetItemAttrNumber
3714 (p_item_type,
3715 p_item_key,
3716 'LAST_UPDATE_LOGIN');
3717
3718 End get_standard_who;
3719
3720 Function Contain_Projects(
3721 X_Request_Id IN NUMBER
3722 ) RETURN BOOLEAN IS
3723
3724 l_batch_type VARCHAR2(1);
3725 l_Contain_Project BOOLEAN;
3726 CURSOR get_type_h IS
3727 SELECT batch_type_code
3728 FROM gl_auto_alloc_batch_history
3729 WHERE Request_Id = X_Request_Id;
3730 BEGIN
3731 l_Contain_Project := FALSE;
3732
3733 OPEN get_type_h;
3734
3735 LOOP
3736 FETCH get_type_h INTO l_batch_type;
3737 EXIT WHEN get_type_h%NOTFOUND;
3738
3739 IF (l_batch_type = 'P') THEN
3740 l_Contain_Project := TRUE;
3741 END IF;
3742
3743 END LOOP;
3744 CLOSE get_type_h;
3745 return(l_Contain_Project);
3746
3747 END Contain_Projects;
3748
3749 Procedure Update_Status(
3750 l_request_id IN Number
3751 ,l_step_number IN Number
3752 ,l_status_code IN Varchar2 ) IS
3753
3754 f_step_number NUMBER;
3755 f_status_code Varchar2(30);
3756
3757 Cursor status_code_C IS
3758 Select
3759 STATUS_CODE
3760 ,STEP_NUMBER
3761 FROM GL_AUTO_ALLOC_BATCH_HISTORY
3762 WHERE REQUEST_ID = l_request_id
3763 AND ( STEP_NUMBER = l_step_number OR
3764 -1 = l_step_number) ;
3765
3766 Begin
3767 Open status_code_C;
3768 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3769 diagn_debug_msg('Update_Status: ' || 'Status_code = '||l_status_code||
3770 ' Step_number = '||to_char(l_step_number));
3771 END IF;
3772 If l_step_number = -1 Then
3773 LOOP
3774 Fetch status_code_C into
3775 f_status_code
3776 ,f_step_number;
3777 Exit WHEN status_code_C%NOTFOUND;
3778 If f_status_code <> l_status_code AND
3779 f_status_code not in ('RC','NS','RNR') Then
3780 UPDATE GL_AUTO_ALLOC_BATCH_HISTORY
3781 SET STATUS_CODE = l_status_code
3782 WHERE REQUEST_ID = l_request_id
3783 AND STEP_NUMBER = f_step_number;
3784 End If;
3785 End Loop;
3786
3787 Else
3788 Fetch status_code_C into
3789 f_status_code
3790 ,f_step_number;
3791 If f_status_code <> l_status_code Or
3792 f_status_code IS NULL Then
3793 UPDATE GL_AUTO_ALLOC_BATCH_HISTORY
3794 SET STATUS_CODE = l_status_code
3795 WHERE REQUEST_ID = l_request_id
3796 AND STEP_NUMBER = l_step_number;
3797 End If;
3798
3799 End If;
3800 close status_code_C;
3801 End Update_Status;
3802
3803 Procedure Get_Status_and_Message(
3804 conc_prg_code IN Varchar2
3805 ,ptype IN Varchar2
3806 ,rollback_allowed IN Varchar2
3807 ,status_code OUT NOCOPY Varchar2
3808 ,message_name OUT NOCOPY Varchar2 ) Is
3809
3810 l_operating_mode Varchar2(2);
3811
3812 Begin
3813 l_operating_mode := WF_ENGINE.GetItemAttrText
3814 (p_item_type,
3815 p_item_key,
3816 'OPERATING_MODE');
3817 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3818 diagn_debug_msg('Get_Status_and_Message: Prg_Code = '||
3819 Conc_Prg_Code||' Type = '||ptype);
3820 END IF;
3821 If ptype = 'COMPLETE' Then
3822 If l_operating_mode = 'R' Then --Rollback mode
3823 If conc_prg_code = 'GLPPOS' Then
3824 --Rollback:Posting Program Completed
3825 status_code := 'RPPC';
3826 Elsif conc_prg_code = 'GLPREV' Then
3827 --Rollback: Reversal Program Completed
3828 status_code := 'RRPC';
3829 End If;
3830 Else -- Normal mode
3831 IF conc_prg_code In ( 'GLAMAS','GLPRJE') Then
3832 --Generation program completed
3833 status_code := 'GPC';
3834 Elsif conc_prg_code = 'GLPPOS' Then
3835 --Posting completed
3836 status_code := 'PPC';
3837 End If;
3838 End If;
3839 ElsIf ptype = 'ERROR' Then
3840 If l_operating_mode = 'R' Then --Rollback mode
3841 If conc_prg_code = 'GLPPOS' Then
3842 --Rollback:Posting Program Failed
3843 status_code := 'RPF';
3844 message_name := 'GLALLOC:POSTING_PRG_FAILED';
3845 Elsif conc_prg_code = 'GLPREV' Then
3846 --Rollback: Reversal Program Completed
3847 status_code := 'RRF';
3848 message_name := 'GLALLOC:REV_JE_PRG_FAILED';
3849 End If;
3850 Else -- Normal mode
3851 If conc_prg_code In ( 'GLAMAS','GLPRJE') Then
3852 --Generation program completed
3853 status_code := 'GF';
3854 If rollback_allowed = 'Y' Then
3855 message_name := 'GLALLOC:MA_PRG_FAILED';
3856 Else
3857 message_name := 'GLALLOC:MA_PRG_FAILED_NRB';
3858 End If;
3859 Elsif conc_prg_code = 'GLPPOS' Then
3860 --Posting completed
3861 status_code := 'PF';
3862 If rollback_allowed = 'Y' Then
3863 message_name := 'GLALLOC:POSTING_PRG_FAILED';
3864 Else
3865 message_name := 'GLALLOC:POSTING_PRG_FAILED_NRB';
3866 End If;
3867 End If;
3868 End If;
3869 End If;
3870 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3871 diagn_debug_msg('Get_Status_and_Message: ' || 'Message_name = '||message_name||' Status_Code = '||status_code);
3872 END IF;
3873 return;
3874 End Get_Status_and_Message;
3875
3876 Procedure Submit_Request(
3877 p_parent_request_id IN NUMBER
3878 ,p_step_number IN NUMBER
3879 ,Prog_Code IN VARCHAR2
3880 ,p_attribute1 IN VARCHAR2 DEFAULT NULL
3881 ,p_attribute2 IN VARCHAR2 DEFAULT NULL
3882 ,p_attribute3 IN VARCHAR2 DEFAULT NULL
3883 ,p_attribute4 IN VARCHAR2 DEFAULT NULL
3884 ,p_attribute5 IN VARCHAR2 DEFAULT NULL
3885 ,p_attribute6 IN VARCHAR2 DEFAULT NULL
3886 ,p_attribute7 IN VARCHAR2 DEFAULT NULL
3887 ,p_attribute8 IN VARCHAR2 DEFAULT NULL
3888 ,p_attribute9 IN VARCHAR2 DEFAULT NULL
3889 ,p_attribute10 IN VARCHAR2 DEFAULT NULL
3890 ,p_attribute11 IN VARCHAR2 DEFAULT NULL
3891 ,p_attribute12 IN VARCHAR2 DEFAULT NULL
3892 ,p_sub_req_id OUT NOCOPY NUMBER) Is
3893 l_parent_req_id Number := p_parent_request_id;
3894 l_operating_mode Varchar2(1);
3895 l_userenv_lang Varchar2(10);
3896 l_client_info Varchar2(240);
3897 l_conc_prg_name Varchar2(240);
3898 l_user_id Number;
3899 l_org_id Number;
3900 l_resp_id Number;
3901 l_resp_appl_id Number;
3902 l_status_code Varchar2(15);
3903 l_rollback_allowed Varchar2(1);
3904 l_message_Name Varchar2(150);
3905 l_submit_request_id NUMBER;
3906 Cursor conc_prog_name_C IS
3907 Select USER_CONCURRENT_PROGRAM_NAME
3908 From fnd_concurrent_programs_vl
3909 Where APPLICATION_ID = 101
3910 AND CONCURRENT_PROGRAM_NAME = Prog_Code;
3911 Begin
3912 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3913 diagn_debug_msg('Inside SUBMIT_REQUEST');
3914 diagn_debug_msg('Submit_Request: ' || 'p_attribute1='||p_attribute1);
3915 diagn_debug_msg('Submit_Request: ' || 'p_attribute2='||p_attribute2);
3916 diagn_debug_msg('Submit_Request: ' || 'p_attribute3='||p_attribute3);
3917 diagn_debug_msg('Submit_Request: ' || 'p_attribute4='||p_attribute4);
3918 diagn_debug_msg('Submit_Request: ' || 'p_attribute5='||p_attribute5);
3919 diagn_debug_msg('Submit_Request: ' || 'p_attribute6='||p_attribute6);
3920 diagn_debug_msg('Submit_Request: ' || 'p_attribute7='||p_attribute7);
3921 diagn_debug_msg('Submit_Request: ' || 'p_attribute8='||p_attribute8);
3922 diagn_debug_msg('Submit_Request: ' || 'p_attribute9='||p_attribute9);
3923 diagn_debug_msg('Submit_Request: ' || 'p_attribute10='||p_attribute10);
3924 diagn_debug_msg('Submit_Request: ' || 'p_attribute11='||p_attribute11);
3925 diagn_debug_msg('Submit_Request: ' || 'p_attribute12='||p_attribute12);
3926 diagn_debug_msg('Submit_Request: ' || 'Prog_Code='||Prog_Code);
3927 END IF;
3928
3929 l_operating_mode := wf_engine.GetItemAttrText
3930 ( itemtype => p_item_type,
3931 itemkey => p_item_key,
3932 aname => 'OPERATING_MODE');
3933
3934
3935 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
3936 p_item_type,
3937 p_item_key,
3938 'ROLLBACK_ALLOWED');
3939
3940 l_user_id := WF_ENGINE.GetItemAttrNumber
3941 (p_item_type,
3942 p_item_key,
3943 'USER_ID');
3944
3945 l_org_id := WF_ENGINE.GetItemAttrNumber
3946 (p_item_type,
3947 p_item_key,
3948 'ORG_ID');
3949
3950 l_resp_id := WF_ENGINE.GetItemAttrNumber
3951 (p_item_type,
3952 p_item_key,
3953 'RESP_ID');
3954
3955 l_resp_appl_id := WF_ENGINE.GetItemAttrNumber
3956 (p_item_type,
3957 p_item_key,
3958 'RESP_APPL_ID');
3959
3960 --Bug fix 1971413
3961 FND_GLOBAL.Apps_Initialize(l_user_id, l_resp_id, l_resp_appl_id);
3962
3963 FND_PROFILE.put('ORG_ID', l_org_id);
3964 FND_PROFILE.put('USER_ID', l_user_id );
3965 FND_PROFILE.put('RESP_ID', l_resp_id);
3966 FND_PROFILE.put('RESP_APPL_ID', l_resp_appl_id);
3967
3968 select userenv('LANG') into l_userenv_lang from dual;
3969 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3970 diagn_debug_msg('Submit_Request: ' || 'LANG='||l_userenv_lang);
3971 END IF;
3972 select userenv('CLIENT_INFO') into l_client_info from dual;
3973 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
3974 diagn_debug_msg('Submit_Request: ' || 'CLINT_INFO='||l_client_info);
3975 END IF;
3976
3977 l_submit_request_id :=
3978 FND_REQUEST.SUBMIT_REQUEST(
3979 'SQLGL',
3980 Prog_Code,
3981 '',
3982 '',
3983 FALSE,
3984 p_attribute1,
3985 p_attribute2,
3986 p_attribute3,
3987 p_attribute4,
3988 p_attribute5,
3989 p_attribute6,
3990 p_attribute7,
3991 p_attribute8,
3992 p_attribute9,
3993 p_attribute10,
3994 p_attribute11,
3995 p_attribute12,
3996 '','','','','','','','','','',
3997 '','','','','','','','','','','','','','','',
3998 '','','','','','','','','','','','','','','',
3999 '','','','','','','','','','','','','','','',
4000 '','','','','','','','','','','','','','','',
4001 '','','','','','','','','','','','','','','',
4002 '','','');
4003
4004 wf_engine.SetItemAttrText(
4005 itemtype => p_item_type,
4006 itemkey => p_item_key,
4007 aname => 'CONC_PRG_CODE',
4008 avalue => Prog_Code );
4009
4010 select nvl(userenv('LANG'),'US') into l_userenv_lang from dual;
4011 Open conc_prog_name_C;
4012 Fetch conc_prog_name_C into l_conc_prg_name;
4013 Close conc_prog_name_C;
4014
4015 wf_engine.SetItemAttrText(
4016 itemtype => p_item_type,
4017 itemkey => p_item_key,
4018 aname => 'CONC_PRG_NAME',
4019 avalue => l_conc_prg_name );
4020
4021
4022 If l_submit_request_id = 0 Then
4023 -- Request Submission failed
4024 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4025 diagn_debug_msg('Submit_Request: ' || 'Sending Notification.Fatal Error: Conc program not submitted');
4026 END IF;
4027 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
4028 itemkey => p_item_key,
4029 aname => 'CONC_REQUEST_ID',
4030 avalue => -1 );
4031
4032 Get_Status_and_Message(Prog_Code
4033 ,'ERROR'
4034 ,l_rollback_allowed
4035 ,l_status_code
4036 ,l_message_name);
4037
4038 -- Program Error
4039 Update_Status(to_number(p_item_key)
4040 ,p_step_number
4041 ,l_status_code
4042 );
4043
4044 wf_engine.SetItemAttrText(itemtype => p_item_type,
4045 itemkey => p_item_key,
4046 aname => 'MESSAGE_NAME',
4047 avalue => l_message_name );
4048 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4049 diagn_debug_msg('Submit_Request: ' || 'Message_name = '||l_message_Name);
4050 END IF;
4051 p_sub_req_id := 0;
4052
4053 Else /* l_submit_request_id <> 0 */
4054 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4055 diagn_debug_msg('Inserting req id = '||to_char(l_submit_request_id)||
4056 ' into histroy detail');
4057 END IF;
4058 wf_engine.SetItemAttrNumber( itemtype => p_item_type,
4059 itemkey => p_item_key,
4060 aname => 'CONC_REQUEST_ID',
4061 avalue => l_submit_request_id );
4062
4063 INSERT_BATCH_HIST_DET(
4064 p_REQUEST_ID => l_submit_request_id
4065 ,p_PARENT_REQUEST_ID => l_parent_req_id
4066 ,p_STEP_NUMBER => p_step_number
4067 ,p_PROGRAM_NAME_CODE => Prog_Code
4068 ,p_RUN_MODE => l_operating_mode);
4069
4070 p_sub_req_id := l_submit_request_id;
4071 End If;
4072 End Submit_Request;
4073
4074 -- ****************************************************************************
4075 -- Private procedure: Display diagnostic message
4076 -- ****************************************************************************
4077
4078 Procedure initialize_debug IS
4079 Begin
4080 G_FILE := GL_AUTO_ALLOC_WF_PKG.p_item_key ||'.dbg';
4081 If utl_file.Is_Open(G_FILE_PTR) Then
4082 utl_file.fclose(G_FILE_PTR);
4083 End If;
4084 G_FILE_PTR := utl_file.fopen(G_DIR, G_FILE, 'a');
4085 Exception
4086 When Others Then
4087 Wf_Core.Context('GL_AUTO_ALLOCATION_WF_PKG',
4088 'initialize_debug', 'GLALLOC', p_item_key);
4089 Wf_Core.Get_Error(err_name,err_msg,err_stack);
4090 diagn_debug_msg_flag := FALSE;
4091 --Raise;
4092 End initialize_debug;
4093
4094 Procedure diagn_debug_msg(debug_message in Varchar2) Is
4095 Begin
4096 If (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) then
4097 If debug_message is not null then
4098 If NOT utl_file.Is_Open(G_FILE_PTR) OR
4099 G_FILE <> GL_AUTO_ALLOC_WF_PKG.p_item_key ||'.dbg' OR
4100 G_FILE IS NULL Then
4101 initialize_debug;
4102 End If;
4103 utl_file.put_line(G_FILE_PTR, debug_message);
4104 utl_file.fflush(G_FILE_PTR);
4105 End if;
4106 End If;
4107 Exception
4108 WHEN UTL_FILE.INVALID_PATH OR
4109 UTL_FILE.WRITE_ERROR OR
4110 UTL_FILE.INVALID_FILEHANDLE THEN
4111 diagn_debug_msg_flag := FALSE;
4112 null;
4113 WHEN OTHERS THEN
4114 Wf_Core.Context('GL_AUTO_ALLOCATION_WF_PKG',
4115 'diagn_debug_msg', 'GLALLOC', p_item_key);
4116 diagn_debug_msg_flag := FALSE;
4117 null;
4118 End diagn_debug_msg;
4119
4120 Procedure Continue_Next_Step(p_item_type IN VARCHAR2,
4121 p_item_key IN VARCHAR2,
4122 p_actid IN NUMBER,
4123 p_funcmode IN VARCHAR2,
4124 p_result OUT NOCOPY VARCHAR2) Is
4125 l_continue_next_step Varchar2(1);
4126 l_step_number Number;
4127 l_rollback_allowed Varchar2(1);
4128 l_message_Name Varchar2(150);
4129
4130 Begin
4131 If ( p_funcmode = 'RUN' ) THEN
4132 GL_AUTO_ALLOC_WF_PKG.p_item_key := p_item_key;
4133
4134 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4135 diagn_debug_msg('Started Continue_Next_Step');
4136 END IF;
4137
4138 l_step_number := WF_ENGINE.GetItemAttrNumber
4139 (p_item_type,
4140 p_item_key,
4141 'STEP_NUMBER');
4142
4143 l_rollback_allowed := WF_ENGINE.GetItemAttrText(
4144 p_item_type,
4145 p_item_key,
4146 'ROLLBACK_ALLOWED');
4147
4148 l_continue_next_step := WF_ENGINE.GetItemAttrText(
4149 p_item_type,
4150 p_item_key,
4151 'CONTINUE_NEXT_STEP');
4152
4153 If l_continue_next_step = 'Y' Then
4154 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4155 diagn_debug_msg('Continue_Next_Step: ' || 'Continue to process next step');
4156 END IF;
4157 -- set status code to Batch Not Generated
4158 Update_Status(to_number(p_item_key)
4159 ,l_step_number
4160 ,'BNG'
4161 );
4162 p_result := 'COMPLETE:Y';
4163 Else
4164 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4165 diagn_debug_msg('Continue_Next_Step: ' || 'Stop to process next step. Sending notification');
4166 END IF;
4167
4168 If l_rollback_allowed = 'Y' Then
4169 l_message_name := 'GLALLOC:NO_BATCH_GENERATED';
4170 Else
4171 l_message_name := 'GLALLOC:NO_BATCH_GENERATED_NRB';
4172 End If;
4173
4174 wf_engine.SetItemAttrText(itemtype => p_item_type,
4175 itemkey => p_item_key,
4176 aname => 'MESSAGE_NAME',
4177 avalue => l_message_Name );
4178
4179 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4180 diagn_debug_msg('Continue_Next_Step: ' || 'Message_name = '||l_message_Name);
4181 END IF;
4182 -- set status code to Batch Not Generated
4183 Update_Status(to_number(p_item_key)
4184 ,l_step_number
4185 ,'BNG'
4186 );
4187 p_result := 'COMPLETE:N';
4188 return;
4189 END IF;
4190 ElsIf ( p_funcmode = 'CANCEL' ) THEN
4191 NULL;
4192 End If;
4193 EXCEPTION
4194 WHEN OTHERS THEN
4195 Wf_Core.Context('GL_AUTO_ALLOC_WF_PKG', 'Continue_Next_Step', p_item_type, p_item_key);
4196 Wf_Core.Get_Error(err_name,err_msg,err_stack);
4197 IF (diagn_debug_msg_flag) AND (G_DIR is NOT NULL ) THEN
4198 diagn_debug_msg('Continue_Next_Step: ' || err_msg ||'*'||err_stack);
4199 END IF;
4200 Update_Status(to_number(p_item_key)
4201 ,l_step_number
4202 ,'UFE'
4203 );
4204
4205 Raise;
4206 End Continue_Next_Step;
4207
4208
4209 End GL_AUTO_ALLOC_WF_PKG;