[Home] [Help]
PACKAGE BODY: APPS.FV_WF_BE_APPROVAL
Source
1 PACKAGE BODY Fv_Wf_Be_Approval AS
2 /* $Header: FVBEWFPB.pls 120.16 2011/03/02 07:29:20 yanasing ship $ */
3
4 -- ======================================================================
5 -- Variable Naming Conventions
6 -- ======================================================================
7 -- 1. Global Variables have the format "vg_<Variable_Name>"
8 -- 2. Procedure Level local variables have
9 -- the format "vl_<Variable_Name>"
10 -- 3. User Defined Exceptions have "e_<Exception_Name>"
11
12 -- ======================================================================
13 -- Global Variable Declarations
14 -- ======================================================================
15
16 g_module_name VARCHAR2(100) := 'fv.plsql.Fv_Wf_Be_Approval.';
17 vg_errbuf VARCHAR2(1000) ;
18 vg_retcode NUMBER := 0 ;
19
20 vg_itemtype wf_items.item_type%TYPE;
21 vg_itemkey wf_items.item_key%TYPE;
22
23 vg_sob_id Gl_Sets_Of_Books.set_of_books_id%TYPE;
24 vg_doc_id Fv_Be_Trx_Hdrs.doc_id%TYPE;
25 vg_to_rpr_doc_id Fv_Be_Trx_Hdrs.doc_id%TYPE;
26 vg_user_id fnd_user.user_id%TYPE;
27 vg_resp_id fnd_responsibility.responsibility_id%TYPE;
28 vg_response_note VARCHAR2(240);
29 vg_doc_type VARCHAR2(40);
30 vg_event_type VARCHAR2(40);
31 vg_calling_sequence VARCHAR2(80);
32 vg_bc_mode VARCHAR2(1);
33 vg_gl_date DATE;
34
35 e_invalid EXCEPTION;
36
37 TYPE be_trx_record IS RECORD (
38 gl_date Fv_Be_Trx_Dtls.gl_date%TYPE,
39 trx_type Fv_Be_Transaction_Types.apprn_transaction_type%TYPE,
40 trx_code Fv_Be_Trx_Dtls.sub_type%TYPE,
41 inc_dec_flag VARCHAR2(15),
42 amount Fv_Be_Trx_Dtls.amount%TYPE,
43 fund_dist Fv_Be_Trx_Dtls.budgeting_segments%TYPE );
44
45
46 -- BCPSA-BE enhancements
47
48 PROCEDURE Main(
49 errbuf OUT NOCOPY VARCHAR2,
50 retcode OUT NOCOPY NUMBER,
51 p_sob_id IN NUMBER,
52 p_submitter_id IN NUMBER,
53 p_approver_id IN NUMBER,
54 p_doc_id IN NUMBER,
55 p_note IN VARCHAR2,
56 p_to_rpr_doc_id IN NUMBER,
57 p_user_id IN NUMBER,
58 p_resp_id IN NUMBER) IS
59
60 l_module_name VARCHAR2(200) := g_module_name || 'Main';
61 vg_itemtype VARCHAR2(30);
62 vg_itemkey VARCHAR2(80);
63 vl_doc_number Fv_Be_Trx_Hdrs.doc_number%TYPE;
64 vl_revision_num Fv_Be_Trx_Hdrs.revision_num%TYPE;
65 vl_ts_id Fv_Be_Trx_Hdrs.treasury_symbol_id%TYPE;
66 vl_fund_value Fv_Be_Trx_Hdrs.fund_value%TYPE;
67 vl_fund_dist Fv_Be_Trx_Hdrs.budgeting_segments%TYPE;
68 vl_budlevel_id Fv_Be_Trx_Hdrs.budget_level_id%TYPE;
69 vl_budget_desc Fv_Budget_Levels.description%TYPE;
70 vl_treasury_symbol Fv_Treasury_symbols.treasury_symbol%TYPE;
71 vl_form_name VARCHAR2(20);
72 vl_submitter_username VARCHAR2(30);
73 vl_approver_username VARCHAR2(30);
74 vl_submitter_dispname VARCHAR2(80);
75 vl_approver_dispname VARCHAR2(80);
76 vl_item_seq NUMBER;
77 vl_orig_system VARCHAR2(14);
78 vl_user_id Fnd_User.user_id%TYPE;
79 vl_owner Fnd_User.user_name%TYPE;
80 vl_rev_total NUMBER;
81 vl_curr_code VARCHAR2(15);
82
83
84 CURSOR get_otherattr_cur IS
85 SELECT doc_number, revision_num, treasury_symbol_id,
86 fund_value, budgeting_segments, budget_level_id
87 FROM Fv_Be_Trx_Hdrs
88 WHERE doc_id = p_doc_id
89 AND set_of_books_id = p_sob_id;
90
91 CURSOR get_tsymbol_cur IS
92 SELECT treasury_symbol
93 FROM Fv_Treasury_Symbols
94 WHERE treasury_symbol_id = vl_ts_id
95 AND set_of_books_id = p_sob_id;
96
97 CURSOR get_buddesc_cur IS
98 SELECT description
99 FROM Fv_Budget_Levels
100 WHERE budget_level_id = vl_budlevel_id
101 AND set_of_books_id = p_sob_id;
102
103 CURSOR get_revtotal_csr IS
104 SELECT SUM(amount)
105 FROM Fv_Be_Trx_Dtls
106 WHERE doc_id = p_doc_id
107 AND set_of_books_id = p_sob_id
108 AND revision_num = vl_revision_num;
109
110 CURSOR get_currency_csr IS
111 SELECT currency_code
112 FROM Gl_Sets_Of_Books
113 WHERE set_of_books_id = p_sob_id;
114 BEGIN
115 -- Set the retcode
116 retcode := 0;
117
118 -- Check if any of the required parameters are null
119 IF (p_sob_id IS NULL OR p_submitter_id IS NULL
120 OR p_approver_id IS NULL OR p_doc_id IS NULL
121 OR p_user_id IS NULL OR p_resp_id IS NULL) THEN
122
123 -- Raise an error.
124 errbuf:= 'Either the SOB Id,Doc Id,Submitter Id,Approver Id,'||
125 'User Id or the Responsibility Id is null.'||
126 'Workflow process has not been started.';
127 retcode := 2;
128 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.message1',errbuf);
129
130 RETURN;
131
132 END IF;
133
134 -- Get the sequence value for the itemkey.
135 SELECT fv_be_wf_itemkey_s.NEXTVAL
136 INTO vl_item_seq
137 FROM DUAL;
138
139 -- Set the Itemtype,Itemkey
140 vg_itemtype := 'FVBEAPPR';
141 vg_itemkey := p_doc_id || '-' || vl_item_seq;
142
143 -- Get all the other attributes which are needed in the workflow process
144 OPEN get_otherattr_cur;
145 FETCH get_otherattr_cur INTO
146 vl_doc_number, vl_revision_num, vl_ts_id,
147 vl_fund_value, vl_fund_dist, vl_budlevel_id;
148 CLOSE get_otherattr_cur;
149
150 -- Get the Treasury_Symbol
151 OPEN get_tsymbol_cur;
152 FETCH get_tsymbol_cur INTO vl_treasury_symbol;
153 CLOSE get_tsymbol_cur;
154
155 -- Get the Budget Level Description
156 OPEN get_buddesc_cur;
157 FETCH get_buddesc_cur INTO vl_budget_desc;
158 CLOSE get_buddesc_cur;
159
160 -- Get the Total Amount for the document for the revision that is processed.
161 OPEN get_revtotal_csr;
162 FETCH get_revtotal_csr INTO vl_rev_total;
163 CLOSE get_revtotal_csr;
164
165 -- Get the Functional Currency
166 OPEN get_currency_csr;
167 FETCH get_currency_csr INTO vl_curr_code;
168 CLOSE get_currency_csr;
169
170 -- Append the word Distribution to vl_fund_dist
171 vl_fund_dist := 'Distribution : '||vl_fund_dist;
172
173 -- Derive the Orig_System for the submitter_id
174 Get_Orig_System(p_submitter_id,vl_orig_system,vl_user_id,vg_errbuf,vg_retcode);
175
176 IF (vg_retcode <> 0) THEN
177 RAISE e_invalid;
178 END IF;
179
180 -- Get the Submitter Username and Display Name
181 Wf_Directory.GetUserName(vl_orig_system,
182 vl_user_id,
183 vl_submitter_username,
184 vl_submitter_dispname);
185
186 -- Derive the Orig_System for the approver_id
187 Get_Orig_System(p_approver_id,vl_orig_system,vl_user_id,vg_errbuf,vg_retcode);
188 IF (vg_retcode <> 0) THEN
189 RAISE e_invalid;
190 END IF;
191
192 -- Get the Approver Username and Display Name
193 Wf_Directory.GetUserName(vl_orig_system,
194 vl_user_id,
195 vl_approver_username,
196 vl_approver_dispname);
197
198 -- Call to the Create Process
199 Wf_Engine.CreateProcess(ItemType => vg_itemtype,
200 ItemKey => vg_itemkey,
201 Process => 'FV_BEAP');
202
203 -- Set the ItemUserKey
204 Wf_Engine.SetItemUserKey(ItemType => vg_itemtype,
205 ItemKey => vg_itemkey,
206 UserKey => vl_doc_number);
207
208 -- Set all the attributes
209 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
210 ItemKey => vg_itemkey,
211 AName => 'SOB',
212 AValue => p_sob_id );
213
214 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
215 ItemKey => vg_itemkey,
216 AName => 'DOC_ID',
217 AValue => p_doc_id );
218
219 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
220 ItemKey => vg_itemkey,
221 AName => 'SUBMITTER_ID',
222 AValue => p_submitter_id );
223
224 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
225 ItemKey => vg_itemkey,
226 AName => 'APPROVER_ID',
227 AValue => p_approver_id );
228
229 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
230 ItemKey => vg_itemkey,
231 AName => 'TO_RPR_DOC_ID',
232 AValue => p_to_rpr_doc_id );
233
234 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
235 ItemKey => vg_itemkey,
236 AName => 'NOTE',
237 AValue => p_note );
238
239 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
240 ItemKey => vg_itemkey,
241 AName => 'SUBMITTER_NAME',
242 AValue => vl_submitter_username );
243
244 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
245 ItemKey => vg_itemkey,
246 AName => 'SUBMITTER_DISP_NAME',
247 AValue => vl_submitter_dispname );
248
249 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
250 ItemKey => vg_itemkey,
251 AName => 'APPROVER_NAME',
252 AValue => vl_approver_username );
253
254 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
255 ItemKey => vg_itemkey,
256 AName => 'APPROVER_DISP_NAME',
257 AValue => vl_approver_dispname );
258
259 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
260 ItemKey => vg_itemkey,
261 AName => 'DOC_NUMBER',
262 AValue => vl_doc_number );
263
264 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
265 ItemKey => vg_itemkey,
266 AName => 'REVISION_NUM',
267 AValue => vl_revision_num );
268
269 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
270 ItemKey => vg_itemkey,
271 AName => 'TREASURY_SYMBOL',
272 AValue => vl_treasury_symbol );
273
274 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
275 ItemKey => vg_itemkey,
276 AName => 'FUND_VALUE',
277 AValue => vl_fund_value );
278
279 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
280 ItemKey => vg_itemkey,
281 AName => 'FROM_DISTRIBUTION',
282 AValue => vl_fund_dist );
283
284 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
285 ItemKey => vg_itemkey,
286 AName => 'BUDGET_LEVEL_DESC',
287 AValue => vl_budget_desc );
288
289 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
290 ItemKey => vg_itemkey,
291 AName => 'RESPONSE_NOTE',
292 AValue => vg_response_note );
293
294 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
295 ItemKey => vg_itemkey,
296 AName => 'REVISION_TOTAL',
297 AValue => vl_rev_total );
298
299 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
300 ItemKey => vg_itemkey,
301 AName => 'REVISION_TOTAL_DISP',
302 AValue => TO_CHAR(vl_rev_total)||' '||
303 vl_curr_code);
304
305 Wf_Engine.SetItemAttrText(itemtype => vg_itemtype,
306 itemkey => vg_itemkey,
307 aname => 'TRANSACTION_DETAILS',
308 avalue => 'PLSQL:FV_WF_BE_APPROVAL.GET_TRX_DOC_DETAILS/'|| vg_itemtype ||':'||vg_itemkey );
309
310 -- Populate #HDR_1(revision total amount), only if header attributes
311 -- are supported.
312 IF (Wf_Core.Translate('WF_HEADER_ATTR') = 'Y') then
313 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
314 ItemKey => vg_itemkey,
315 AName => '#HDR_1',
316 AValue => TO_CHAR(vl_rev_total)||' '||
317 vl_curr_code);
318 END IF;
319
320 -- Disable the relevant attribute that should not be displayed on the
321 -- notifications, based on the budget level id
322 IF (vl_budlevel_id = 1) THEN
323 -- If it is Appropriation Document, then disable the distribution form.
324 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
325 ItemKey => vg_itemkey,
326 AName => 'DIST_FORM_DETAILS',
327 AValue => '' );
328
329 vl_form_name := 'Appropriation';
330 ELSE
331 -- If it is Distribution Document, then disable the appropriation form.
332 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
333 ItemKey => vg_itemkey,
334 AName => 'TRX_FORM_DETAILS',
335 AValue => '' );
336
337 vl_form_name := 'Distribution';
338 END IF;
339
340 -- Set the Budget Level Form Name
341 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
342 ItemKey => vg_itemkey,
343 AName => 'BUDGET_LEVEL_FORM_NAME',
344 AValue => vl_form_name );
345
346 -- Set the User Id and Responsibility Id
347 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
348 ItemKey => vg_itemkey,
349 AName => 'USER_ID',
350 AValue => p_user_id );
351
352 Wf_Engine.SetItemAttrNumber(ItemType => vg_itemtype,
353 ItemKey => vg_itemkey,
354 AName => 'RESP_ID',
355 AValue => p_resp_id );
356
357 Wf_Engine.SetItemAttrText(ItemType => vg_itemtype,
358 ItemKey => vg_itemkey,
359 AName => '#FROM_ROLE',
360 AValue => vl_submitter_username );
361
362 -- Call to set the ProcessOwner
363 Wf_Engine.SetItemOwner(itemtype => vg_itemtype,
364 itemkey => vg_itemkey,
365 owner => vl_submitter_username);
366
367 -- Call to the Start Process
368 Wf_Engine.StartProcess(ItemType => vg_itemtype,
369 ItemKey => vg_itemkey);
370
371 COMMIT;
372
373 EXCEPTION
374 WHEN e_invalid THEN
375 Wf_Core.Context('FV_WF_BE_APPROVAL','Main',vg_itemtype,vg_itemkey,vg_errbuf,vg_retcode);
376
377 errbuf := vg_errbuf;
378 retcode := vg_retcode;
379
380 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',errbuf);
381 Raise;
382
383 RETURN;
384 WHEN OTHERS THEN
385 Wf_Core.Context('FV_WF_BE_APPROVAL','Main',vg_itemtype,vg_itemkey,SQLERRM,SQLCODE);
386
387 errbuf := SQLERRM ||' -- Error in Main procedure';
388 retcode := SQLCODE;
389
390 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
391
392 Raise;
393 RETURN;
394 END Main;
395
396
397 ----------------------------------------------
398 PROCEDURE VerifyStatus(itemtype VARCHAR2,
399 itemkey VARCHAR2,
400 actid NUMBER,
401 funcmode VARCHAR2,
402 resultout IN OUT NOCOPY VARCHAR2 ) IS
403 l_module_name VARCHAR2(200) := g_module_name || 'VerifyStatus';
404 l_errbuf VARCHAR2(1024);
405
406 vl_doc_status Fv_Be_Trx_Dtls.transaction_status%TYPE;
407 vl_doc_status_desc VARCHAR2(80);
408
409 CURSOR get_transtat_cur IS
410 SELECT doc_status
411 FROM Fv_Be_Trx_Hdrs
412 WHERE set_of_books_id = vg_sob_id
413 AND doc_id = vg_doc_id;
414
415 CURSOR get_desc_cur IS
416 SELECT description
417 FROM Fv_Lookup_Codes
418 WHERE lookup_type = 'BE_DOC_STATUS'
419 AND lookup_code = vl_doc_status;
420 BEGIN
421
422 IF (funcmode = 'RUN') THEN
423 -- Call to get the sob and doc id item attributes
424 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
425 itemkey => itemkey,
426 aname => 'SOB');
427
428 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
429 itemkey => itemkey,
430 aname => 'DOC_ID');
431
432 OPEN get_transtat_cur;
433 FETCH get_transtat_cur INTO vl_doc_status;
434 CLOSE get_transtat_cur;
435
436 IF (vl_doc_status = 'IP') THEN
437 resultout := 'COMPLETE:SUCCESS';
438 ELSE
439 OPEN get_desc_cur;
440 FETCH get_desc_cur INTO vl_doc_status_desc;
441 CLOSE get_desc_cur;
442
443 -- Set the Doc Status Desctiption Attribute
444 Wf_Engine.SetItemAttrText(ItemType => itemtype,
445 ItemKey => itemkey,
446 AName => 'DOC_STATUS',
447 AValue => vl_doc_status_desc );
448 resultout := 'COMPLETE:FAILURE';
449 END IF;
450 ELSIF (funcmode = 'CANCEL') THEN
451 resultout := 'COMPLETE';
452 RETURN;
453 END IF;
454
455 EXCEPTION
456 WHEN OTHERS THEN
457 l_errbuf := SQLERRM;
458 Wf_Core.Context('FV_WF_BE_APPROVAL','VerifyStatus',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
459 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
460 Raise;
461 END VerifyStatus;
462
463
464 ----------------------------------------------
465
466 PROCEDURE CheckRPRDocId(itemtype VARCHAR2,
467 itemkey VARCHAR2,
468 actid NUMBER,
469 funcmode VARCHAR2,
470 resultout IN OUT NOCOPY VARCHAR2 ) IS
471 l_module_name VARCHAR2(200) := g_module_name || 'CheckRPRDocId';
472 l_errbuf VARCHAR2(1024);
473 BEGIN
474
475 IF (funcmode = 'RUN') THEN
476 -- Call to get the to_rpr_doc_id item attributes
477 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
478 itemkey => itemkey,
479 aname => 'TO_RPR_DOC_ID');
480
481 IF (vg_to_rpr_doc_id IS NOT NULL) THEN
482 resultout := 'COMPLETE:Y';
483 ELSE
484 resultout := 'COMPLETE:N';
485 END IF;
486 ELSIF (funcmode = 'CANCEL') THEN
487 resultout := 'COMPLETE';
488 RETURN;
489 END IF;
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 l_errbuf := SQLERRM;
494 Wf_Core.Context('FV_WF_BE_APPROVAL','CheckRPRDocId',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
495 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
496 Raise;
497 END CheckRPRDocId;
498
499
500 ----------------------------------------------
501
502 PROCEDURE GetRPRDetails(itemtype VARCHAR2,
503 itemkey VARCHAR2,
504 actid NUMBER,
505 funcmode VARCHAR2,
506 resultout IN OUT NOCOPY VARCHAR2 ) IS
507 l_module_name VARCHAR2(200) := g_module_name || 'GetRPRDetails';
508 l_errbuf VARCHAR2(1024);
509 BEGIN
510
511 IF (funcmode = 'RUN') THEN
512 -- Call to get the to_rpr_doc_id item attributes
513 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
514 itemkey => itemkey,
515 aname => 'TO_RPR_DOC_ID');
516
517 -- Set the attribute RPR_Trx_Details
518 Wf_Engine.SetItemAttrText(itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'RPR_TRX_DETAILS',
521 avalue => 'PLSQL:FV_WF_BE_APPROVAL.GET_RPR_DOC_DETAILS/'||itemtype||':'||itemkey );
522
523
524 resultout := 'COMPLETE';
525
526 ELSIF (funcmode = 'CANCEL') THEN
527 resultout := 'COMPLETE';
528 RETURN;
529 END IF;
530
531 EXCEPTION
532 WHEN OTHERS THEN
533 l_errbuf := SQLERRM;
534 Wf_Core.Context('FV_WF_BE_APPROVAL','GetRPRDetails',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
535 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
536 Raise;
537 END GetRPRDetails;
538
539
540 ----------------------------------------------
541
542 PROCEDURE ApproverPostNtf(itemtype VARCHAR2,
543 itemkey VARCHAR2,
544 actid NUMBER,
545 funcmode VARCHAR2,
546 resultout IN OUT NOCOPY VARCHAR2 ) IS
547 l_module_name VARCHAR2(200) := g_module_name || 'ApproverPostNtf';
548 l_errbuf VARCHAR2(1024);
549
550 vl_nid NUMBER;
551 vl_ntf_result VARCHAR2(30);
552
553 BEGIN
554
555 IF (funcmode = 'RESPOND') THEN
556 -- Call to get the notification_id
557 vl_nid := Wf_Engine.Context_Nid;
558
559 -- Call to get the sob_id, doc_id,to_rpr_doc_id item attributes
560 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
561 itemkey => itemkey,
562 aname => 'SOB');
563
564 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
565 itemkey => itemkey,
566 aname => 'DOC_ID');
567
568 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'TO_RPR_DOC_ID');
571
572 vg_response_note := Wf_Engine.GetItemAttrText(itemtype => itemtype,
573 itemkey => itemkey,
574 aname => 'RESPONSE_NOTE');
575
576 -- Get the notification result
577 vl_ntf_result := Wf_Notification.GetAttrText(vl_nid,'RESULT');
578
579 IF (vl_ntf_result = 'REJECTED') THEN
580
581 -- Update the status for the doc_id to Rejected
582 Update_Status(vg_sob_id,vg_doc_id,'RJ',vg_errbuf,vg_retcode);
583
584 IF (vg_retcode <> 0) THEN
585 RAISE e_invalid;
586 END IF;
587
588 IF (vg_to_rpr_doc_id IS NOT NULL) THEN
589
590 -- Update the status for the to_rpr_doc_id to Rejected
591 Update_Status(vg_sob_id,vg_to_rpr_doc_id,'RJ',vg_errbuf,vg_retcode);
592 IF (vg_retcode <> 0) THEN
593 RAISE e_invalid;
594 END IF;
595
596 END IF; /* To RPR Doc Id */
597
598 END IF; /* Reject */
599
600 resultout := Wf_Engine.Eng_Completed||':'||vl_ntf_result;
601 RETURN;
602
603 ELSIF (funcmode = 'TRANSFER') THEN
604 resultout := Wf_Engine.Eng_Null;
605 RETURN;
606
607 ELSIF (funcmode = 'FORWARD') THEN
608 resultout := Wf_Engine.Eng_Null;
609 RETURN;
610
611 ELSIF (funcmode = 'RUN') THEN
612 resultout := Wf_Engine.Eng_Null;
613 RETURN;
614
615 ELSIF (funcmode = 'CANCEL') THEN
616 resultout := Wf_Engine.Eng_Null;
617 RETURN;
618
619 END IF;
620
621 EXCEPTION
622 WHEN e_invalid THEN
623 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproverPostNtf',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
624 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
625 Raise;
626
627 WHEN OTHERS THEN
628 l_errbuf := SQLERRM;
629 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproverPostNtf',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
630 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
631 Raise;
632 END ApproverPostNtf;
633
634
635 ----------------------------------------------
636
637 PROCEDURE Update_Status(p_sob_id NUMBER,
638 p_doc_id NUMBER,
639 p_doc_status VARCHAR2,
640 errbuf OUT NOCOPY VARCHAR2,
641 retcode OUT NOCOPY NUMBER) IS
642 l_module_name VARCHAR2(200) := g_module_name || 'Update_Status';
643 l_errbuf VARCHAR2(1024);
644
645 BEGIN
646
647 -- Update the Headers table
648 UPDATE Fv_Be_Trx_Hdrs
649 SET doc_status = p_doc_status
650 WHERE doc_id = p_doc_id
651 AND set_of_books_id = p_sob_id;
652
653 -- Update the Details table
654 UPDATE Fv_Be_Trx_Dtls
655 SET transaction_status = p_doc_status
656 WHERE doc_id = p_doc_id
657 AND set_of_books_id = p_sob_id
658 AND transaction_status = 'IP';
659
660 EXCEPTION
661 WHEN OTHERS THEN
662 errbuf := SQLERRM || ' -- Error in the Update_Status Procedure';
663 retcode := SQLCODE;
664 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
665
666 RETURN;
667 END Update_Status;
668
669
670 ----------------------------------------------
671
672 PROCEDURE ApproveDoc(itemtype VARCHAR2,
673 itemkey VARCHAR2,
674 actid NUMBER,
675 funcmode VARCHAR2,
676 resultout IN OUT NOCOPY VARCHAR2 ) IS
677 l_module_name VARCHAR2(200) := g_module_name || 'ApproveDoc';
678 l_errbuf VARCHAR2(1024);
679
680 vl_packet_id NUMBER;
681 vl_approver_id Fnd_User.user_id%TYPE;
682 x_return_status VARCHAR2(1);
683 x_status_code VARCHAR2(100);
684 vg_doc_type VARCHAR2(25);
685 vg_event_type VARCHAR2(25);
686 vg_gl_date DATE;
687 vg_budget_level_id NUMBER;
688 --vg_source VARCHAR2(5);
689 vg_source VARCHAR2(25);
690
691 BEGIN
692
693 IF (funcmode = 'RUN') THEN
694 -- Call to get the sob_id, doc_id,to_rpr_doc_id item attributes
695 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
696 itemkey => itemkey,
697 aname => 'SOB');
698
699 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
700 itemkey => itemkey,
701 aname => 'DOC_ID');
702
703 vl_approver_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
704 itemkey => itemkey,
705 aname => 'APPROVER_ID');
706
707 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
708 itemkey => itemkey,
709 aname => 'TO_RPR_DOC_ID');
710
711 vg_user_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
712 itemkey => itemkey,
713 aname => 'USER_ID');
714
715 vg_resp_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
716 itemkey => itemkey,
717 aname => 'RESP_ID');
718
719
720 ------****** Code suggested by Rani Shergil *****************
721 /*
722 CURSOR fetch_doc_info IS
723 SELECT doc_id, transaction_date, budget_level_id, source
724 FROM fv_be_trx_hdrs
725 WHERE doc_id = p_doc_id
726
727 FOR doc_rec IN fetch_doc_info LOOP
728
729 IF doc_rec.source = 'RPR' then
730
731 l_doc_type := 'BE_RPR_TRANSACTIONS';
732
733 IF doc_rec.budget_level_id = 1 THEN
734
735 l_event_type := 'RPR_BA_RESERVE';
736
737 ELSE
738
739 l_event_type := 'RPR_FD_RESERVE';
740
741 END IF;
742
743 ELSE
744
745 l_doc_type := 'BE_TRANSACTIONS';
746
747 IF doc_rec.budget_level_id = 1 THEN
748
749 l_event_type := 'BA_RESERVE';
750
751 ELSE
752
753 l_event_type := 'FD_RESERVE';
754
755 END IF;
756
757 END IF;
758
759 call Main ( .........);
760
761 END LOOP;
762
763 */
764 ----------------------*/*******************************
765
766 SELECT transaction_date, budget_level_id, source
767 INTO vg_gl_date, vg_budget_level_id, vg_source
768 FROM fv_be_trx_hdrs
769 WHERE doc_id = vg_doc_id;
770
771 IF vg_source = 'RPR' then
772 vg_doc_type := 'BE_RPR_TRANSACTIONS';
773 IF vg_budget_level_id = 1 THEN
774 vg_event_type := 'RPR_BA_RESERVE';
775 ELSE
776 vg_event_type := 'RPR_FD_RESERVE';
777 END IF;
778 ELSE
779 vg_doc_type := 'BE_TRANSACTIONS';
780 IF vg_budget_level_id = 1 THEN
781 vg_event_type := 'BA_RESERVE';
782 ELSE
783 vg_event_type := 'FD_RESERVE';
784 END IF;
785 END IF;
786
787
788 --BCPSA-BE Enhancement -
789 -- Call to the Funds Reservation Process
790 Fv_Be_Fund_Pkg.Main(vg_errbuf
791 ,vg_retcode
792 ,'R'
793 ,vg_sob_id
794 ,vg_doc_id
795 ,vg_to_rpr_doc_id
796 ,vl_approver_id
797 ,vg_doc_type
798 ,vg_event_type
799 ,vg_gl_date
800 ,x_return_status
801 ,x_status_code
802 ,vg_user_id
803 ,vg_resp_id);
804
805 IF (x_return_status = 'S') THEN
806 resultout := 'COMPLETE:SUCCESS';
807 ELSE
808 -- Set the attribute for the packet id
809 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
810 itemkey => itemkey,
811 aname => 'PACKET_ID',
812 avalue => vl_packet_id );
813
814 resultout := 'COMPLETE:FAILURE';
815 END IF;
816 ELSIF (funcmode = 'CANCEL') THEN
817 resultout := 'COMPLETE';
818 RETURN;
819 END IF;
820
821 EXCEPTION
822 WHEN OTHERS THEN
823 l_errbuf := vg_errbuf||SQLERRM;
824 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproveDoc',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
825 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
826 Raise;
827 END ApproveDoc;
828
829
830 ----------------------------------------------
831
832 PROCEDURE TimeoutPostNtf(itemtype VARCHAR2,
833 itemkey VARCHAR2,
834 actid NUMBER,
835 funcmode VARCHAR2,
836 resultout IN OUT NOCOPY VARCHAR2 ) IS
837 l_module_name VARCHAR2(200) := g_module_name || 'TimeoutPostNtf';
838 l_errbuf VARCHAR2(1024);
839
840 BEGIN
841
842 IF (funcmode = 'RESPOND') THEN
843 resultout := Wf_Engine.Eng_Null;
844 RETURN;
845
846 ELSIF (funcmode = 'TRANSFER') THEN
847 resultout := Wf_Engine.Eng_Null;
848 RETURN;
849
850 ELSIF (funcmode = 'FORWARD') THEN
851 resultout := Wf_Engine.Eng_Null;
852 RETURN;
853
854 ELSIF (funcmode = 'RUN') THEN
855
856 -- Call to get the sob_id, doc_id,to_rpr_doc_id item attributes
857 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
858 itemkey => itemkey,
859 aname => 'SOB');
860
861 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
862 itemkey => itemkey,
863 aname => 'DOC_ID');
864
865 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
866 itemkey => itemkey,
867 aname => 'TO_RPR_DOC_ID');
868
869 -- Get Revision Number for doc_id
870 Get_Revision_Number(vg_sob_id,vg_doc_id,vg_errbuf,vg_retcode);
871
872 IF (vg_retcode <> 0) THEN
873 RAISE e_invalid;
874 END IF;
875
876 IF (vg_to_rpr_doc_id IS NOT NULL) THEN
877
878 -- Get Revision Number for to_rpr_doc_id
879 Get_Revision_Number(vg_sob_id,vg_to_rpr_doc_id,vg_errbuf,vg_retcode);
880
881 IF (vg_retcode <> 0) THEN
882 RAISE e_invalid;
883 END IF;
884 END IF;
885
886 resultout := Wf_Engine.Eng_Completed || ':' || Wf_Engine.Eng_Null;
887 RETURN;
888
889 ELSIF (funcmode = 'CANCEL') THEN
890 resultout := Wf_Engine.Eng_Null;
891 RETURN;
892
893 END IF;
894
895 EXCEPTION
896 WHEN e_invalid THEN
897 Wf_Core.Context('FV_WF_BE_APPROVAL','TimeoutPostNtf',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
898 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproveDoc',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
899 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
900 Raise;
901
902 WHEN OTHERS THEN
903 l_errbuf := SQLERRM;
904 Wf_Core.Context('FV_WF_BE_APPROVAL','TimeoutPostNtf',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
905 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
906 Raise;
907 END TimeoutPostNtf;
908
909
910 ----------------------------------------------
911
912 PROCEDURE Get_Revision_Number(sob_id NUMBER,
913 doc_id NUMBER,
914 errbuf OUT NOCOPY VARCHAR2,
915 retcode OUT NOCOPY NUMBER) IS
916 l_module_name VARCHAR2(200) := g_module_name || 'Get_Revision_Number';
917 l_errbuf VARCHAR2(1024);
918
919 CURSOR get_revnum_cur IS
920 SELECT MAX(revision_num)
921 FROM Fv_Be_Trx_Dtls
922 WHERE set_of_books_id = sob_id
923 AND doc_id = doc_id
924 AND transaction_status = 'IP';
925
926 vl_rev_num Fv_Be_Trx_Dtls.revision_num%TYPE;
927
928 BEGIN
929
930 -- Get the revision number for doc_id
931 OPEN get_revnum_cur;
932 FETCH get_revnum_cur INTO vl_rev_num;
933 CLOSE get_revnum_cur;
934
935 IF (vl_rev_num = 0) THEN
936 -- Update the status for the doc_id to Incomplete
937 Update_Status(sob_id,doc_id,'IN',errbuf,retcode);
938 ELSE
939 -- Update the status for the doc_id to Requires Reapproval
940 Update_Status(sob_id,doc_id,'RA',errbuf,retcode);
941 END IF;
942
943 EXCEPTION
944 WHEN OTHERS THEN
945 errbuf := SQLERRM || ' -- Error in the Get_Revision_Number Procedure';
946 retcode := SQLCODE;
947 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
948
949 RETURN;
950 END Get_Revision_Number;
951
952
953 ----------------------------------------------
954
955 PROCEDURE Get_Orig_System(p_user_id NUMBER,
956 p_orig_system OUT NOCOPY VARCHAR2,
957 p_new_user_id OUT NOCOPY NUMBER,
958 errbuf OUT NOCOPY VARCHAR2,
959 retcode OUT NOCOPY NUMBER) IS
960 l_module_name VARCHAR2(200) := g_module_name || 'Get_Orig_System';
961 l_errbuf VARCHAR2(1024);
962
963 CURSOR get_empid_cur(c_user_id Fnd_User.user_id%TYPE) IS
964 SELECT employee_id
965 FROM Fnd_User
966 WHERE user_id = c_user_id;
967
968 vl_emp_id Fnd_User.employee_id%TYPE;
969 BEGIN
970
971 OPEN get_empid_cur(p_user_id);
972 FETCH get_empid_cur INTO vl_emp_id;
973 CLOSE get_empid_cur;
974
975 IF (vl_emp_id IS NULL) THEN
976 p_new_user_id := p_user_id;
977 p_orig_system := 'FND_USR';
978 ELSE
979 p_new_user_id := vl_emp_id;
980 p_orig_system := 'PER';
981 END IF;
982
983 EXCEPTION
984 WHEN OTHERS THEN
985 errbuf := SQLERRM || ' -- Error in the Get_Orig_System Procedure';
986 retcode := SQLCODE;
987 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
988
989 RETURN;
990 END Get_Orig_System;
991
992
993 ----------------------------------------------
994
995 PROCEDURE Get_Trx_Doc_Details( document_id IN VARCHAR2,
996 display_type IN VARCHAR2,
997 document IN OUT NOCOPY VARCHAR2,
998 document_type IN OUT NOCOPY VARCHAR2) IS
999 l_module_name VARCHAR2(200) := g_module_name || 'Get_Trx_Doc_Details';
1000 l_errbuf VARCHAR2(1024);
1001 vl_document VARCHAR2(32000) := 'Get_Trx_Doc_Details';
1002 BEGIN
1003
1004 -- Derive the itemtype
1005 vg_itemtype := SUBSTR(document_id, 1, INSTR(document_id, ':') - 1);
1006
1007 -- Derive the itemkey
1008 vg_itemkey := SUBSTR(document_id, INSTR(document_id, ':') + 1);
1009
1010 -- Call to get the sob and doc id item attributes
1011 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1012 itemkey => vg_itemkey,
1013 aname => 'SOB');
1014
1015 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1016 itemkey => vg_itemkey,
1017 aname => 'DOC_ID');
1018
1019 -- Call to build the document
1020 Build_Document('N',display_type,vl_document,vg_errbuf,vg_retcode);
1021
1022 IF (vg_retcode <> 0) THEN
1023 Raise e_invalid;
1024 END IF;
1025
1026 document := vl_document;
1027
1028 EXCEPTION
1029 WHEN e_invalid THEN
1030 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_Trx_Doc_Details',vg_itemtype,vg_itemkey,vg_errbuf,vg_retcode);
1031 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
1032 Raise;
1033 WHEN OTHERS THEN
1034 l_errbuf := SQLERRM;
1035 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_Trx_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1036 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1037 Raise;
1038 END Get_Trx_Doc_Details;
1039
1040
1041 ----------------------------------------------
1042
1043 PROCEDURE Get_RPR_Doc_Details( document_id IN VARCHAR2,
1044 display_type IN VARCHAR2,
1045 document IN OUT NOCOPY VARCHAR2,
1046 document_type IN OUT NOCOPY VARCHAR2) IS
1047 l_module_name VARCHAR2(200) := g_module_name || 'Get_RPR_Doc_Details';
1048 l_errbuf VARCHAR2(1024);
1049 vl_document VARCHAR2(32000) := 'Get_RPR_Doc_Details';
1050 BEGIN
1051
1052 vg_itemtype := SUBSTR(document_id, 1, INSTR(document_id, ':') - 1);
1053
1054 vg_itemkey := SUBSTR(document_id, INSTR(document_id, ':') + 1);
1055
1056 -- Call to get the sob and doc id(in this case,it is the rpr doc id)item attributes
1057 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1058 itemkey => vg_itemkey,
1059 aname => 'SOB');
1060
1061 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1062 itemkey => vg_itemkey,
1063 aname => 'TO_RPR_DOC_ID');
1064
1065 -- Call to build the document
1066 Build_Document('Y',display_type,vl_document,vg_errbuf,vg_retcode);
1067
1068 IF (vg_retcode <> 0) THEN
1069 Raise e_invalid;
1070 END IF;
1071
1072 document := vl_document;
1073
1074 EXCEPTION
1075 WHEN e_invalid THEN
1076 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,vg_errbuf,vg_retcode);
1077 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
1078 Raise;
1079 WHEN OTHERS THEN
1080 l_errbuf := SQLERRM;
1081 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1082 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1083 Raise;
1084 END Get_RPR_Doc_Details;
1085
1086
1087 ----------------------------------------------
1088
1089 PROCEDURE Build_Document(rpr_flag VARCHAR2,
1090 disp_type VARCHAR2,
1091 doc OUT NOCOPY VARCHAR2,
1092 errbuf OUT NOCOPY VARCHAR2,
1093 retcode OUT NOCOPY NUMBER) IS
1094 l_module_name VARCHAR2(200) := g_module_name || 'Build_Document';
1095 l_errbuf VARCHAR2(1024);
1096
1097 vl_trx_record be_trx_record;
1098 NL VARCHAR2(1) := fnd_global.newline;
1099 vl_document VARCHAR2(32000) := 'Build_Document';
1100 vl_date_str VARCHAR2(32000) DEFAULT NULL;
1101
1102 CURSOR get_trx_cur IS
1103 SELECT D.gl_date,
1104 T.apprn_transaction_type ,
1105 D.sub_type ,
1106 decode(D.increase_decrease_flag,'I','Increase','Decrease') ,
1107 D.amount ,
1108 D.budgeting_segments
1109 FROM Fv_Be_Trx_Dtls D, Fv_Be_Transaction_Types T
1110 WHERE D.set_of_books_id = vg_sob_id
1111 AND D.doc_id = vg_doc_id
1112 AND D.transaction_type_id = T.be_tt_id
1113 AND D.revision_num = (SELECT MAX(revision_num)
1114 FROM Fv_Be_Trx_Hdrs
1115 WHERE doc_id = vg_doc_id
1116 AND set_of_books_id = vg_sob_id)
1117 ORDER BY D.gl_date ;
1118
1119 BEGIN
1120
1121 IF (rpr_flag = 'N') THEN
1122 vl_document := NL || NL ||'<P><B> Transaction Details </B>';
1123 ELSE
1124 vl_document := NL || NL ||'<P><B> Re-Programming Transaction Details </B>';
1125 END IF;
1126
1127 IF (disp_type = 'text/html') THEN
1128 vl_document := vl_document || ' <TABLE border=1 cellpadding=2 cellspacing=1> '
1129 || NL;
1130
1131 vl_document := vl_document ||'<TR> '||NL;
1132
1133 vl_document := vl_document ||'<TH>GL Date</TH> '||NL;
1134
1135 vl_document := vl_document ||'<TH>Transaction Type</TH> '||NL;
1136
1137 vl_document := vl_document ||'<TH>Transaction Code</TH> '||NL;
1138
1139 vl_document := vl_document ||'<TH>Inc/Dec</TH> '||NL;
1140
1141 vl_document := vl_document ||'<TH>Amount</TH> '||NL;
1142
1143 vl_document := vl_document ||'<TH>Fund Distribution</TH> '||NL;
1144
1145 vl_document := vl_document ||'</TR> '||NL;
1146
1147 OPEN get_trx_cur;
1148
1149 LOOP
1150 FETCH get_trx_cur INTO vl_trx_record;
1151
1152 EXIT WHEN get_trx_cur%NOTFOUND;
1153
1154 vl_document := vl_document ||'<TR>'||NL;
1155
1156
1157 -- vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(to_char(vl_trx_record.gl_date), ' ') ||'</TD>'||NL;
1158 /*
1159 *FOR BUG 7538261
1160 * Is modified to use TO_CHAR(datetime) with NLS_CALENDAR
1161 * parameter.
1162 **/
1163 /*
1164 Modified for bug 7713511.
1165 */
1166 -- IF FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', vg_user_id) IS
1167 -- NOT NULL THEN
1168
1169 IF ((FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 1 and
1170 FND_RELEASE.POINT_VERSION >= 1 )
1171 or (FND_RELEASE.MAJOR_VERSION = 12 and FND_RELEASE.minor_version >= 2)
1172 or (FND_RELEASE.MAJOR_VERSION > 12)) THEN
1173 /*
1174 * Execute for versions equal and above R12.1.1
1175 */
1176 IF vg_user_id IS NULL OR vg_user_id = '' THEN
1177 vg_user_id:=fnd_global.user_id;
1178 END IF;
1179 -- BUG 8974285
1180 vl_date_str := nvl(to_char(vl_trx_record.gl_date,
1181 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',
1182 vg_user_id),
1183 'NLS_CALENDAR = ''' ||
1184 NVL(FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR',
1185 vg_user_id),'GREGORIAN') || ''''),'');
1186 IF (disp_type=wf_notification.doc_html) THEN
1187 vl_date_str := '<BDO DIR="LTR">' ||
1188 vl_date_str || '</BDO>';
1189 END IF;
1190 ELSE
1191 vl_date_str := nvl(to_char(vl_trx_record.gl_date),'');
1192 END IF;
1193
1194 vl_document := vl_document ||'<TD nowrap align=CENTER>'||
1195 vl_date_str||'</TD>'||NL;
1196
1197 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.trx_type, ' ') ||'</TD>'||NL;
1198
1199 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.trx_code, ' ') ||'</TD>'||NL;
1200
1201 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.inc_dec_flag, ' ') ||'</TD>'||NL;
1202
1203 vl_document := vl_document ||'<TD nowrap align=RIGHT>'|| nvl(to_char(vl_trx_record.amount,'999,999,999,990.90'), 0) ||'</TD>'||NL;
1204
1205 vl_document := vl_document ||'<TD align=CENTER>'|| nvl(vl_trx_record.fund_dist, ' ') ||'</TD>'||NL;
1206
1207 vl_document := vl_document ||'</TR>'||NL;
1208
1209 END LOOP;
1210
1211 CLOSE get_trx_cur;
1212
1213 vl_document := vl_document ||'</TABLE> </P>'||NL;
1214
1215 doc := vl_document;
1216 END IF;
1217
1218 EXCEPTION
1219 WHEN OTHERS THEN
1220 l_errbuf := SQLERRM;
1221 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1222 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1223 Raise;
1224 END Build_Document;
1225
1226 END Fv_Wf_Be_Approval;