[Home] [Help]
PACKAGE BODY: APPS.FV_WF_BE_APPROVAL
Source
1 PACKAGE BODY Fv_Wf_Be_Approval AS
2 /* $Header: FVBEWFPB.pls 120.11.12010000.2 2008/11/11 05:33:48 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
690 BEGIN
691
692 IF (funcmode = 'RUN') THEN
693 -- Call to get the sob_id, doc_id,to_rpr_doc_id item attributes
694 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
695 itemkey => itemkey,
696 aname => 'SOB');
697
698 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
699 itemkey => itemkey,
700 aname => 'DOC_ID');
701
702 vl_approver_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
703 itemkey => itemkey,
704 aname => 'APPROVER_ID');
705
706 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
707 itemkey => itemkey,
708 aname => 'TO_RPR_DOC_ID');
709
710 vg_user_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'USER_ID');
713
714 vg_resp_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
715 itemkey => itemkey,
716 aname => 'RESP_ID');
717
718
719 ------****** Code suggested by Rani Shergil *****************
720 /*
721 CURSOR fetch_doc_info IS
722 SELECT doc_id, transaction_date, budget_level_id, source
723 FROM fv_be_trx_hdrs
724 WHERE doc_id = p_doc_id
725
726 FOR doc_rec IN fetch_doc_info LOOP
727
728 IF doc_rec.source = 'RPR' then
729
730 l_doc_type := 'BE_RPR_TRANSACTIONS';
731
732 IF doc_rec.budget_level_id = 1 THEN
733
734 l_event_type := 'RPR_BA_RESERVE';
735
736 ELSE
737
738 l_event_type := 'RPR_FD_RESERVE';
739
740 END IF;
741
742 ELSE
743
744 l_doc_type := 'BE_TRANSACTIONS';
745
746 IF doc_rec.budget_level_id = 1 THEN
747
748 l_event_type := 'BA_RESERVE';
749
750 ELSE
751
752 l_event_type := 'FD_RESERVE';
753
754 END IF;
755
756 END IF;
757
758 call Main ( .........);
759
760 END LOOP;
761
762 */
763 ----------------------*/*******************************
764
765 SELECT transaction_date, budget_level_id, source
766 INTO vg_gl_date, vg_budget_level_id, vg_source
767 FROM fv_be_trx_hdrs
768 WHERE doc_id = vg_doc_id;
769
770 IF vg_source = 'RPR' then
771 vg_doc_type := 'BE_RPR_TRANSACTIONS';
772 IF vg_budget_level_id = 1 THEN
773 vg_event_type := 'RPR_BA_RESERVE';
774 ELSE
775 vg_event_type := 'RPR_FD_RESERVE';
776 END IF;
777 ELSE
778 vg_doc_type := 'BE_TRANSACTIONS';
779 IF vg_budget_level_id = 1 THEN
780 vg_event_type := 'BA_RESERVE';
781 ELSE
782 vg_event_type := 'FD_RESERVE';
783 END IF;
784 END IF;
785
786
787 --BCPSA-BE Enhancement -
788 -- Call to the Funds Reservation Process
789 Fv_Be_Fund_Pkg.Main(vg_errbuf
790 ,vg_retcode
791 ,'R'
792 ,vg_sob_id
793 ,vg_doc_id
794 ,vg_to_rpr_doc_id
795 ,vl_approver_id
796 ,vg_doc_type
797 ,vg_event_type
798 ,vg_gl_date
799 ,x_return_status
800 ,x_status_code
801 ,vg_user_id
802 ,vg_resp_id);
803
804 IF (x_return_status = 'S') THEN
805 resultout := 'COMPLETE:SUCCESS';
806 ELSE
807 -- Set the attribute for the packet id
808 Wf_Engine.SetItemAttrNumber(itemtype => itemtype,
809 itemkey => itemkey,
810 aname => 'PACKET_ID',
811 avalue => vl_packet_id );
812
813 resultout := 'COMPLETE:FAILURE';
814 END IF;
815 ELSIF (funcmode = 'CANCEL') THEN
816 resultout := 'COMPLETE';
817 RETURN;
818 END IF;
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 l_errbuf := vg_errbuf||SQLERRM;
823 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproveDoc',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
824 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
825 Raise;
826 END ApproveDoc;
827
828
829 ----------------------------------------------
830
831 PROCEDURE TimeoutPostNtf(itemtype VARCHAR2,
832 itemkey VARCHAR2,
833 actid NUMBER,
834 funcmode VARCHAR2,
835 resultout IN OUT NOCOPY VARCHAR2 ) IS
836 l_module_name VARCHAR2(200) := g_module_name || 'TimeoutPostNtf';
837 l_errbuf VARCHAR2(1024);
838
839 BEGIN
840
841 IF (funcmode = 'RESPOND') THEN
842 resultout := Wf_Engine.Eng_Null;
843 RETURN;
844
845 ELSIF (funcmode = 'TRANSFER') THEN
846 resultout := Wf_Engine.Eng_Null;
847 RETURN;
848
849 ELSIF (funcmode = 'FORWARD') THEN
850 resultout := Wf_Engine.Eng_Null;
851 RETURN;
852
853 ELSIF (funcmode = 'RUN') THEN
854
855 -- Call to get the sob_id, doc_id,to_rpr_doc_id item attributes
856 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
857 itemkey => itemkey,
858 aname => 'SOB');
859
860 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'DOC_ID');
863
864 vg_to_rpr_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => itemtype,
865 itemkey => itemkey,
866 aname => 'TO_RPR_DOC_ID');
867
868 -- Get Revision Number for doc_id
869 Get_Revision_Number(vg_sob_id,vg_doc_id,vg_errbuf,vg_retcode);
870
871 IF (vg_retcode <> 0) THEN
872 RAISE e_invalid;
873 END IF;
874
875 IF (vg_to_rpr_doc_id IS NOT NULL) THEN
876
877 -- Get Revision Number for to_rpr_doc_id
878 Get_Revision_Number(vg_sob_id,vg_to_rpr_doc_id,vg_errbuf,vg_retcode);
879
880 IF (vg_retcode <> 0) THEN
881 RAISE e_invalid;
882 END IF;
883 END IF;
884
885 resultout := Wf_Engine.Eng_Completed || ':' || Wf_Engine.Eng_Null;
886 RETURN;
887
888 ELSIF (funcmode = 'CANCEL') THEN
889 resultout := Wf_Engine.Eng_Null;
890 RETURN;
891
892 END IF;
893
894 EXCEPTION
895 WHEN e_invalid THEN
896 Wf_Core.Context('FV_WF_BE_APPROVAL','TimeoutPostNtf',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
897 Wf_Core.Context('FV_WF_BE_APPROVAL','ApproveDoc',itemtype,itemkey,to_char(actid),vg_errbuf,vg_retcode);
898 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
899 Raise;
900
901 WHEN OTHERS THEN
902 l_errbuf := SQLERRM;
903 Wf_Core.Context('FV_WF_BE_APPROVAL','TimeoutPostNtf',itemtype,itemkey,to_char(actid),l_errbuf,SQLCODE);
904 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
905 Raise;
906 END TimeoutPostNtf;
907
908
909 ----------------------------------------------
910
911 PROCEDURE Get_Revision_Number(sob_id NUMBER,
912 doc_id NUMBER,
913 errbuf OUT NOCOPY VARCHAR2,
914 retcode OUT NOCOPY NUMBER) IS
915 l_module_name VARCHAR2(200) := g_module_name || 'Get_Revision_Number';
916 l_errbuf VARCHAR2(1024);
917
918 CURSOR get_revnum_cur IS
919 SELECT MAX(revision_num)
920 FROM Fv_Be_Trx_Dtls
921 WHERE set_of_books_id = sob_id
922 AND doc_id = doc_id
923 AND transaction_status = 'IP';
924
925 vl_rev_num Fv_Be_Trx_Dtls.revision_num%TYPE;
926
927 BEGIN
928
929 -- Get the revision number for doc_id
930 OPEN get_revnum_cur;
931 FETCH get_revnum_cur INTO vl_rev_num;
932 CLOSE get_revnum_cur;
933
934 IF (vl_rev_num = 0) THEN
935 -- Update the status for the doc_id to Incomplete
936 Update_Status(sob_id,doc_id,'IN',errbuf,retcode);
937 ELSE
938 -- Update the status for the doc_id to Requires Reapproval
939 Update_Status(sob_id,doc_id,'RA',errbuf,retcode);
940 END IF;
941
942 EXCEPTION
943 WHEN OTHERS THEN
944 errbuf := SQLERRM || ' -- Error in the Get_Revision_Number Procedure';
945 retcode := SQLCODE;
946 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
947
948 RETURN;
949 END Get_Revision_Number;
950
951
952 ----------------------------------------------
953
954 PROCEDURE Get_Orig_System(p_user_id NUMBER,
955 p_orig_system OUT NOCOPY VARCHAR2,
956 p_new_user_id OUT NOCOPY NUMBER,
957 errbuf OUT NOCOPY VARCHAR2,
958 retcode OUT NOCOPY NUMBER) IS
959 l_module_name VARCHAR2(200) := g_module_name || 'Get_Orig_System';
960 l_errbuf VARCHAR2(1024);
961
962 CURSOR get_empid_cur(c_user_id Fnd_User.user_id%TYPE) IS
963 SELECT employee_id
964 FROM Fnd_User
965 WHERE user_id = c_user_id;
966
967 vl_emp_id Fnd_User.employee_id%TYPE;
968 BEGIN
969
970 OPEN get_empid_cur(p_user_id);
971 FETCH get_empid_cur INTO vl_emp_id;
972 CLOSE get_empid_cur;
973
974 IF (vl_emp_id IS NULL) THEN
975 p_new_user_id := p_user_id;
976 p_orig_system := 'FND_USR';
977 ELSE
978 p_new_user_id := vl_emp_id;
979 p_orig_system := 'PER';
980 END IF;
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 errbuf := SQLERRM || ' -- Error in the Get_Orig_System Procedure';
985 retcode := SQLCODE;
986 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',errbuf);
987
988 RETURN;
989 END Get_Orig_System;
990
991
992 ----------------------------------------------
993
994 PROCEDURE Get_Trx_Doc_Details( document_id IN VARCHAR2,
995 display_type IN VARCHAR2,
996 document IN OUT NOCOPY VARCHAR2,
997 document_type IN OUT NOCOPY VARCHAR2) IS
998 l_module_name VARCHAR2(200) := g_module_name || 'Get_Trx_Doc_Details';
999 l_errbuf VARCHAR2(1024);
1000 vl_document VARCHAR2(32000) := 'Get_Trx_Doc_Details';
1001 BEGIN
1002
1003 -- Derive the itemtype
1004 vg_itemtype := SUBSTR(document_id, 1, INSTR(document_id, ':') - 1);
1005
1006 -- Derive the itemkey
1007 vg_itemkey := SUBSTR(document_id, INSTR(document_id, ':') + 1);
1008
1009 -- Call to get the sob and doc id item attributes
1010 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1011 itemkey => vg_itemkey,
1012 aname => 'SOB');
1013
1014 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1015 itemkey => vg_itemkey,
1016 aname => 'DOC_ID');
1017
1018 -- Call to build the document
1019 Build_Document('N',display_type,vl_document,vg_errbuf,vg_retcode);
1020
1021 IF (vg_retcode <> 0) THEN
1022 Raise e_invalid;
1023 END IF;
1024
1025 document := vl_document;
1026
1027 EXCEPTION
1028 WHEN e_invalid THEN
1029 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_Trx_Doc_Details',vg_itemtype,vg_itemkey,vg_errbuf,vg_retcode);
1030 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
1031 Raise;
1032 WHEN OTHERS THEN
1033 l_errbuf := SQLERRM;
1034 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_Trx_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1035 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1036 Raise;
1037 END Get_Trx_Doc_Details;
1038
1039
1040 ----------------------------------------------
1041
1042 PROCEDURE Get_RPR_Doc_Details( document_id IN VARCHAR2,
1043 display_type IN VARCHAR2,
1044 document IN OUT NOCOPY VARCHAR2,
1045 document_type IN OUT NOCOPY VARCHAR2) IS
1046 l_module_name VARCHAR2(200) := g_module_name || 'Get_RPR_Doc_Details';
1047 l_errbuf VARCHAR2(1024);
1048 vl_document VARCHAR2(32000) := 'Get_RPR_Doc_Details';
1049 BEGIN
1050
1051 vg_itemtype := SUBSTR(document_id, 1, INSTR(document_id, ':') - 1);
1052
1053 vg_itemkey := SUBSTR(document_id, INSTR(document_id, ':') + 1);
1054
1055 -- Call to get the sob and doc id(in this case,it is the rpr doc id)item attributes
1056 vg_sob_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1057 itemkey => vg_itemkey,
1058 aname => 'SOB');
1059
1060 vg_doc_id := Wf_Engine.GetItemAttrNumber(itemtype => vg_itemtype,
1061 itemkey => vg_itemkey,
1062 aname => 'TO_RPR_DOC_ID');
1063
1064 -- Call to build the document
1065 Build_Document('Y',display_type,vl_document,vg_errbuf,vg_retcode);
1066
1067 IF (vg_retcode <> 0) THEN
1068 Raise e_invalid;
1069 END IF;
1070
1071 document := vl_document;
1072
1073 EXCEPTION
1074 WHEN e_invalid THEN
1075 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,vg_errbuf,vg_retcode);
1076 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.e_invalid',vg_errbuf);
1077 Raise;
1078 WHEN OTHERS THEN
1079 l_errbuf := SQLERRM;
1080 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1081 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1082 Raise;
1083 END Get_RPR_Doc_Details;
1084
1085
1086 ----------------------------------------------
1087
1088 PROCEDURE Build_Document(rpr_flag VARCHAR2,
1089 disp_type VARCHAR2,
1090 doc OUT NOCOPY VARCHAR2,
1091 errbuf OUT NOCOPY VARCHAR2,
1092 retcode OUT NOCOPY NUMBER) IS
1093 l_module_name VARCHAR2(200) := g_module_name || 'Build_Document';
1094 l_errbuf VARCHAR2(1024);
1095
1096 vl_trx_record be_trx_record;
1097 NL VARCHAR2(1) := fnd_global.newline;
1098 vl_document VARCHAR2(32000) := 'Build_Document';
1099 vl_date_str VARCHAR2(32000) DEFAULT NULL;
1100
1101 CURSOR get_trx_cur IS
1102 SELECT D.gl_date,
1103 T.apprn_transaction_type ,
1104 D.sub_type ,
1105 decode(D.increase_decrease_flag,'I','Increase','Decrease') ,
1106 D.amount ,
1107 D.budgeting_segments
1108 FROM Fv_Be_Trx_Dtls D, Fv_Be_Transaction_Types T
1109 WHERE D.set_of_books_id = vg_sob_id
1110 AND D.doc_id = vg_doc_id
1111 AND D.transaction_type_id = T.be_tt_id
1112 AND D.revision_num = (SELECT MAX(revision_num)
1113 FROM Fv_Be_Trx_Hdrs
1114 WHERE doc_id = vg_doc_id
1115 AND set_of_books_id = vg_sob_id)
1116 ORDER BY D.gl_date ;
1117
1118 BEGIN
1119
1120 IF (rpr_flag = 'N') THEN
1121 vl_document := NL || NL ||'<P><B> Transaction Details </B>';
1122 ELSE
1123 vl_document := NL || NL ||'<P><B> Re-Programming Transaction Details </B>';
1124 END IF;
1125
1126 IF (disp_type = 'text/html') THEN
1127 vl_document := vl_document || ' <TABLE border=1 cellpadding=2 cellspacing=1> '
1128 || NL;
1129
1130 vl_document := vl_document ||'<TR> '||NL;
1131
1132 vl_document := vl_document ||'<TH>GL Date</TH> '||NL;
1133
1134 vl_document := vl_document ||'<TH>Transaction Type</TH> '||NL;
1135
1136 vl_document := vl_document ||'<TH>Transaction Code</TH> '||NL;
1137
1138 vl_document := vl_document ||'<TH>Inc/Dec</TH> '||NL;
1139
1140 vl_document := vl_document ||'<TH>Amount</TH> '||NL;
1141
1142 vl_document := vl_document ||'<TH>Fund Distribution</TH> '||NL;
1143
1144 vl_document := vl_document ||'</TR> '||NL;
1145
1146 OPEN get_trx_cur;
1147
1148 LOOP
1149 FETCH get_trx_cur INTO vl_trx_record;
1150
1151 EXIT WHEN get_trx_cur%NOTFOUND;
1152
1153 vl_document := vl_document ||'<TR>'||NL;
1154
1155
1156 -- vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(to_char(vl_trx_record.gl_date), ' ') ||'</TD>'||NL;
1157 /*
1158 * * FOR BUG 7538261
1159 * * Is modified to use TO_CHAR(datetime) with NLS_CALENDAR
1160 * * parameter.
1161 * * */
1162
1163 IF FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR', vg_user_id) IS
1164 NOT NULL THEN
1165 IF vg_user_id IS NULL OR vg_user_id = '' THEN
1166 vg_user_id:=fnd_global.user_id;
1167 END IF;
1168 vl_date_str := nvl(to_char(vl_trx_record.gl_date,
1169 FND_PROFILE.VALUE_SPECIFIC('ICX_DATE_FORMAT_MASK',
1170 vg_user_id),
1171 'NLS_CALENDAR = ''' ||
1172 FND_PROFILE.VALUE_SPECIFIC('FND_FORMS_USER_CALENDAR',
1173 vg_user_id) || ''''),'');
1174
1175 IF (disp_type=wf_notification.doc_html) THEN
1176 vl_date_str := '<BDO DIR="LTR">' ||
1177 vl_date_str || '</BDO>';
1178 END IF;
1179 ELSE
1180 vl_date_str := nvl(to_char(vl_trx_record.gl_date),'');
1181 END IF;
1182
1183 vl_document := vl_document ||'<TD nowrap align=CENTER>'||
1184 vl_date_str||'</TD>'||NL;
1185
1186 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.trx_type, ' ') ||'</TD>'||NL;
1187
1188 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.trx_code, ' ') ||'</TD>'||NL;
1189
1190 vl_document := vl_document ||'<TD nowrap align=CENTER>'|| nvl(vl_trx_record.inc_dec_flag, ' ') ||'</TD>'||NL;
1191
1192 vl_document := vl_document ||'<TD nowrap align=RIGHT>'|| nvl(to_char(vl_trx_record.amount,'999,999,999,990.90'), 0) ||'</TD>'||NL;
1193
1194 vl_document := vl_document ||'<TD align=CENTER>'|| nvl(vl_trx_record.fund_dist, ' ') ||'</TD>'||NL;
1195
1196 vl_document := vl_document ||'</TR>'||NL;
1197
1198 END LOOP;
1199
1200 CLOSE get_trx_cur;
1201
1202 vl_document := vl_document ||'</TABLE> </P>'||NL;
1203
1204 doc := vl_document;
1205 END IF;
1206
1207 EXCEPTION
1208 WHEN OTHERS THEN
1209 l_errbuf := SQLERRM;
1210 Wf_Core.Context('FV_WF_BE_APPROVAL','Get_RPR_Doc_Details',vg_itemtype,vg_itemkey,l_errbuf,SQLCODE);
1211 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',l_errbuf);
1212 Raise;
1213 END Build_Document;
1214
1215 END Fv_Wf_Be_Approval;