[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_INACTIVE_EMP_WF_PKG
Source
1 PACKAGE BODY AP_WEB_INACTIVE_EMP_WF_PKG AS
2 /* $Header: apwinacb.pls 120.23.12000000.3 2007/04/24 20:51:57 skoukunt ship $ */
3 --
4 -- Private Variables
5 --
6 -- copied from WF_NOTIFICATION package
7 -- /fnddev/fnd/11.5/patch/115/sql/wfntfb.pls
8 --
9 table_width varchar2(6) := '"100%"';
10 table_border varchar2(3) := '"0"';
11 table_cellpadding varchar2(3) := '"3"';
12 table_cellspacing varchar2(3) := '"1"';
13 table_bgcolor varchar2(7) := '"white"';
14 th_bgcolor varchar2(9) := '"#cccc99"';
15 th_fontcolor varchar2(9) := '"#336699"';
16 th_fontface varchar2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
17 td_bgcolor varchar2(9) := '"#f7f7e7"';
18 td_fontcolor varchar2(7) := '"black"';
19 td_fontface varchar2(80) := '"Arial, Helvetica, Geneva, sans-serif"';
20
21 PROCEDURE Start_inactive_emp_process(p_card_program_id IN NUMBER,
22 p_inact_employee_id IN NUMBER,
23 p_billed_currency_code IN VARCHAR2,
24 p_total_amt_posted IN NUMBER,
25 p_cc_billed_start_date IN ccTrxn_billedDate,
26 p_cc_billed_end_date IN ccTrxn_billedDate,
27 p_wf_item_type IN wfItems_item_type,
28 p_wf_item_key IN wfItems_item_key)
29
30
31 -- Function Name: Start_inactive_emp_proces
32 -- Author: Geetha Gurram
33 -- Purpose: Assign all the attribute values for the WF Item type APCCARD and process
34 -- inform Inactive Employee Manager and initiates the Inactive Employee Workflow Process
35 -- Input: p_card_program_id
36 -- p_inact_employee_id
37 -- p_billed_currency_code
38 -- p_total_amt_posted
39 -- p_cc_billed_start_date
40 -- p_cc_billed_end_date
41 -- p_wf_item_type
42 -- p_wf_item_key -> sequence ap_ccard_notification_id_s.nextval
43 --
44 -- Output:
45 --
46 -- Assumptions:
47 --
48 -- Notes: Inactive Employee Workflow Processs
49 --
50
51 IS
52 l_process VARCHAR2(50) := 'INFORM_INACT_EMP_MANAGER';
53 l_item_type VARCHAR2(100) := p_wf_item_type;
54 l_Item_Key VARCHAR2(50) := p_wf_item_key;
55 l_inact_employee_name VARCHAR2(30);
56 l_inact_emp_display_name Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
57 l_temp_inact_emp_disp_name Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
58 l_inact_emp_org_id NUMBER;
59 l_inact_emp_org_name VARCHAR2(200);
60 l_total_dsp VARCHAR2(50);
61 l_special_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
62 l_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
63 l_note Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
64 l_resp_notes Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
65 l_resp_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
66 l_orgId NUMBER;
67 l_n_org_id NUMBER;
68 l_debug_info VARCHAR2(200);
69 l_err_name VARCHAR2(200);
70
71 l_textNameArr Wf_Engine.NameTabTyp;
72 l_textValArr Wf_Engine.TextTabTyp;
73 l_numNameArr Wf_Engine.NameTabTyp;
74 l_numValArr Wf_Engine.NumTabTyp;
75 l_dateNameArr Wf_Engine.NameTabTyp;
76 l_dateValArr Wf_Engine.DateTabTyp;
77
78 iNum NUMBER :=0;
79 iText NUMBER :=0;
80 iDate NUMBER :=0;
81
82
83 BEGIN
84
85 Begin
86
87 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start Start_inactive_emp_process');
88
89 wf_engine.CreateProcess(ItemType => l_Item_Type,
90 ItemKey => l_Item_Key,
91 process => l_process);
92
93 exception
94 when others then
95 l_err_name := wf_core.error_name;
96 if (l_err_name = 'WFENG_ITEM_UNIQUE') then
97 wf_core.clear;
98 else
99 raise;
100 end if;
101 end;
102
103 --------------------------------------------------------------
104 l_debug_info := 'Get Org_ID value ';
105 --------------------------------------------------------------
106
107 FND_PROFILE.GET('ORG_ID' , l_n_org_id );
108
109 -- ORG_ID was added later; therefore, it needs to be tested for upgrade purpose, and
110 -- is not included in the bulk update.
111 begin
112
113 WF_ENGINE.SetItemAttrNumber(l_item_type,
114 l_item_key,
115 'ORG_ID',
116 l_n_Org_ID);
117 exception
118 when others then
119 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
120 -- ORG_ID item attribute doesn't exist, need to add it
121 WF_ENGINE.AddItemAttr(l_item_type, l_item_key, 'ORG_ID');
122 WF_ENGINE.SetItemAttrNumber(l_item_type,
123 l_item_key,
124 'ORG_ID',
125 l_n_Org_ID);
126 else
127 raise;
128 end if;
129
130 end;
131
132
133 begin
134
135 --------------------------------------------------------------
136 l_debug_info := 'Set User_ID value ';
137 --------------------------------------------------------------
138 WF_ENGINE.SetItemAttrNumber(l_item_type,
139 l_item_key,
140 'USER_ID',
141 FND_PROFILE.VALUE('USER_ID'));
142
143 --------------------------------------------------------------
144 l_debug_info := 'Set Resp_ID value ';
145 --------------------------------------------------------------
146 WF_ENGINE.SetItemAttrNumber(l_item_type,
147 l_item_key,
148 'RESPONSIBILITY_ID',
149 FND_PROFILE.VALUE('RESP_ID'));
150
151 --------------------------------------------------------------
152 l_debug_info := 'Set Resp_Appl_ID value ';
153 --------------------------------------------------------------
154 WF_ENGINE.SetItemAttrNumber(l_item_type,
155 l_item_key,
156 'APPLICATION_ID',
157 FND_PROFILE.VALUE('RESP_APPL_ID'));
158
159 exception
160 when others then
161 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
162 null;
163 else
164 raise;
165 end if;
166 end;
167
168 Begin
169 ------------------------------------------------------
170 l_debug_info := 'Retrieve The Inactive Employee Info';
171 ------------------------------------------------------
172
173 select p.full_name, o.organization_id, o.name
174 into l_inact_emp_display_name, l_inact_emp_org_id, l_inact_emp_org_name
175 from per_people_f p,
176 per_assignments_f a,
177 per_assignment_status_types s,
178 per_organization_units o
179 where p.person_id = p_inact_employee_id
180 and p.person_id = a.person_id
181 and a.primary_flag = 'Y'
182 and a.assignment_status_type_id = s.assignment_status_type_id
183 and o.organization_id = a.organization_id
184 and o.business_group_id = a.business_group_id
185 and per_system_status in ('TERM_ASSIGN', 'SUSP_ASSIGN')
186 and a.assignment_type in ('E', 'C')
187 and trunc(sysdate) between p.effective_start_date and p.effective_end_date
188 and trunc(sysdate) between a.effective_start_date and a.effective_end_date;
189
190 exception
191 when others then
192 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'Start_Inactive_Emp_Process',
193 l_item_type, l_item_key, to_char(0), l_debug_info);
194 raise;
195 end;
196
197
198 Begin
199
200 l_total_dsp := to_char(p_total_amt_posted,
201 FND_CURRENCY.Get_Format_Mask(p_billed_currency_code,22));
202
203 l_total_dsp := l_total_dsp || ' ' || p_billed_currency_code;
204
205
206 -------------------------------------------------------------
207 l_debug_info := 'Set WF Inactive Employee_ID Item Attribute';
208 -------------------------------------------------------------
209 iNum := iNum + 1;
210 l_numNameArr(iNum) := 'INACT_EMPLOYEE_ID';
211 l_numValArr(iNum) := p_inact_employee_id;
212
213 -------------------------------------------------------------
214 l_debug_info := 'Set WF Inactive Employee_Org Item Attribute';
215 -------------------------------------------------------------
216 iNum := iNum + 1;
217 l_numNameArr(iNum) := 'INACT_EMPLOYEE_ORG_ID';
218 l_numValArr(iNum) := l_inact_emp_org_id;
219
220 --------------------------------------------------------------
221 l_debug_info := 'Set WF Inactive Org Name Item Attribute';
222 --------------------------------------------------------------
223 iText := iText + 1;
224 l_textNameArr(iText) := 'INACT_EMPLOYEE_ORG_NAME';
225 l_textValArr(iText) := l_inact_emp_org_name;
226
227 -- 5921835: for inactive employee WF_DIRECTORY.GetUserName would
228 -- return null as the entry in wf_users would be end dated
229 --------------------------------------------------------------
230 l_debug_info := 'Get Preparer Name Info For Inactive Employee';
231 --------------------------------------------------------------
232 WF_DIRECTORY.GetUserName('PER',
233 p_inact_employee_id,
234 l_inact_employee_name,
235 l_temp_inact_emp_disp_name);
236
237 if l_temp_inact_emp_disp_name is not null then
238 l_inact_emp_display_name := l_temp_inact_emp_disp_name;
239 end if;
240
241 --------------------------------------------------------------
242 l_debug_info := 'Set WF Inactive Employee Name Item Attribute';
243 --------------------------------------------------------------
244 iText := iText + 1;
245 l_textNameArr(iText) := 'INACT_EMP_NAME';
246 l_textValArr(iText) := l_inact_employee_name;
247
248 ----------------------------------------------------------------------
249 l_debug_info := 'Set WF Inactive Employee Display Name Item Attribute';
250 ----------------------------------------------------------------------
251 iText := iText + 1;
252 l_textNameArr(iText) := 'INACT_EMPLOYEE_DISPLAY_NAME';
253 l_textValArr(iText) := l_inact_emp_display_name;
254
255 ------------------------------------------------------
256 l_debug_info := 'Set WF Credit_Card_ID Item Attribute';
257 ------------------------------------------------------
258 iNum := iNum + 1;
259 l_numNameArr(iNum) := 'CARD_PROG_ID';
260 l_numValArr(iNum) := p_card_program_id;
261
262
263 -----------------------------------------------------------
264 l_debug_info := 'Set WF CC Trx Begin Date Item Attribute';
265 -----------------------------------------------------------
266 iDate := iDate + 1;
267 l_dateNameArr(iDate) := 'CC_TRX_BEGIN_DATE';
268 l_dateValArr(iDate) := p_cc_billed_start_date;
269
270 ------------------------------------------------------
271 l_debug_info := 'Set WF CC Trx End Date Item Attribute';
272 ------------------------------------------------------
273 iDate := iDate + 1;
274 l_dateNameArr(iDate) := 'CC_TRX_END_DATE';
275 l_dateValArr(iDate) := p_cc_billed_end_date ;
276
277
278 -------------------------------------------------------------
279 l_debug_info := 'Set WF Total Amt Posted Item Attribute';
280 -------------------------------------------------------------
281 iNum := iNum + 1;
282 l_numNameArr(iNum) := 'TOTAL_AMT_POSTED';
283 l_numValArr(iNum) := p_total_amt_posted;
284
285 ------------------------------------------------------
286 l_debug_info := 'Set WF Display Total Item Attribute';
287 -------------------------------------------------------
288 iText := iText + 1;
289 l_textNameArr(iText) := 'DISPLAY_TOTAL';
290 l_textValArr(iText) := l_total_dsp;
291
292 ---------------------------------------------------------------
293 l_debug_info := 'Set WF Currency Item Attribute';
294 ---------------------------------------------------------------
295 iText := iText + 1;
296 l_textNameArr(iText) := 'CURRENCY';
297 l_textValArr(iText) := p_billed_currency_code;
298
299 ------------------------------------------------------------
300 l_debug_info := 'Get Special Instructions from FND_MESSAGE';
301 ------------------------------------------------------------
302
303 fnd_message.set_name('SQLAP','OIE_INACT_SPEC_INSTRUCTIONS');
304 fnd_message.set_token('INACT_EMP_NAME', l_inact_emp_display_name);
305 fnd_message.set_token('INACT_EMP_ORG_NAME', l_inact_emp_org_name);
306 l_special_instructions := fnd_message.get;
307
308 -------------------------------------------------------------
309 l_debug_info := 'Set WF Special Instructions Item Attribute';
310 -------------------------------------------------------------
311 iText := iText + 1;
312 l_textNameArr(iText) := 'SPECIAL_INSTRUCTIONS';
313 l_textValArr(iText) := l_special_instructions;
314
315 ------------------------------------------------------------
316 l_debug_info := 'Get Instructions from FND_MESSAGE';
317 ------------------------------------------------------------
318
319 fnd_message.set_name('SQLAP','OIE_INACT_INSTRUCTIONS');
320 l_instructions := fnd_message.get;
321
322
323 -------------------------------------------------------------
324 l_debug_info := 'Set WF Instructions Item Attribute';
325 -------------------------------------------------------------
326 iText := iText + 1;
327 l_textNameArr(iText) := 'INSTRUCTIONS';
328 l_textValArr(iText) := l_instructions;
329
330 ------------------------------------------------------------
331 l_debug_info := 'Get Note from FND_MESSAGE';
332 ------------------------------------------------------------
333
334 fnd_message.set_name('SQLAP','OIE_INACT_NOTE');
335 l_note := fnd_message.get;
336
337 -------------------------------------------------------------
338 l_debug_info := 'Set WF Instructions Note Item Attribute';
339 -------------------------------------------------------------
340 iText := iText + 1;
341 l_textNameArr(iText) := 'INSTRUCTION_NOTE';
342 l_textValArr(iText) := l_note;
343
344 -------------------------------------------------------------------
345 l_debug_info := 'Get Responsibility Instructions from FND_MESSAGE';
346 -------------------------------------------------------------------
347
348 fnd_message.set_name('SQLAP','OIE_INACT_RESP_INSTRUCTIONS');
349 l_resp_instructions := fnd_message.get;
350
351 ---------------------------------------------------------------------
352 l_debug_info := 'Set Responsibility Instructions Note Item Attribute';
353 ---------------------------------------------------------------------
354 iText := iText + 1;
355 l_textNameArr(iText) := 'RESP_INSTRUCTIONS';
356 l_textValArr(iText) := l_resp_instructions;
357
358 -------------------------------------------------------------------
359 l_debug_info := 'Get Responsibility Note from FND_MESSAGE';
360 -------------------------------------------------------------------
361
362 fnd_message.set_name('SQLAP','OIE_INACT_RESP_NOT_REASON');
363 fnd_message.set_token('INACT_EMP_NAME', l_inact_emp_display_name);
364 l_resp_notes := fnd_message.get;
365
366 ---------------------------------------------------------------------
367 l_debug_info := 'Set Responsibility Instructions Note Item Attribute';
368 ---------------------------------------------------------------------
369 iText := iText + 1;
370 l_textNameArr(iText) := 'RESP_NOTES';
371 l_textValArr(iText) := l_resp_notes;
372
373 --------------------------------------------------------------
374 l_debug_Info := 'Get ORG_ID from FND_PROFILE';
375 --------------------------------------------------------------
376 FND_PROFILE.GET('ORG_ID' , l_orgId);
377
378 -------------------------------------------------------------
379 l_debug_info := 'Set WF ORG_ID Item Attribute';
380 -------------------------------------------------------------
381 iNum := iNum + 1;
382 l_numNameArr(iNum) := 'ORG_ID';
383 l_numValArr(iNum) := l_orgid;
384
385 ------------------------------------------------------
386 l_debug_info := 'Set CC_TRX_DETAILS_TABLE Item Attribute';
387 ------------------------------------------------------
388 iText := iText + 1;
389 l_textNameArr(iText) := 'CC_TRX_DETAILS_TABLE';
390 l_textValArr(iText) := 'plsql:AP_WEB_INACTIVE_EMP_WF_PKG.GenerateCCTrxList/'||l_item_type||':'||l_item_key;
391
392 ------------------------------------------------------
393 l_debug_info := 'Set OIE_CC_TRX_DETAILS_TABLE Item Attribute';
394 ------------------------------------------------------
395 iText := iText + 1;
396 l_textNameArr(iText) := 'OIE_CC_TRX_DETAILS_TABLE';
397 l_textValArr(iText) := 'JSP:/OA_HTML/OA.jsp?akRegionCode=InactiveEmpRN&akRegionApplicationId=200&itemKey='||l_item_key||'&orgId='||l_orgid;
398
399 -----------------------------------------------------
400 -----------------------------------------------------
401 l_debug_info := 'Set all number Attributes';
402 -----------------------------------------------------
403 WF_ENGINE.SetItemAttrNumberArray(l_item_type, l_item_key, l_numNameArr, l_numValArr);
404
405 -----------------------------------------------------
406 l_debug_info := 'Set all text Attributes';
407 -----------------------------------------------------
408 WF_ENGINE.SetItemAttrTextArray(l_item_type, l_item_key, l_textNameArr, l_textValArr);
409
410 -----------------------------------------------------
411 l_debug_info := 'Set all Date Attributes';
412 -----------------------------------------------------
413 WF_ENGINE.SetItemAttrDateArray(l_item_type, l_item_key, l_dateNameArr, l_dateValArr);
414
415
416 exception
417 when others then
418 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
419 null;
420 else
421 raise;
422 end if;
423 end;
424
425 Begin
426 wf_engine.StartProcess(ItemType => l_Item_Type,
427 ItemKey => l_Item_Key);
428 exception
429 when others then
430 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', ' Start_inactive_emp_process ',
431 l_item_type, l_item_key, to_char(0), l_debug_info);
432 raise;
433 end;
434
435 AP_WEB_UTILITIES_PKG.logProcedure('AP_INACTIVE_EMP_WF_PKG', 'end Start_inactive_emp_process');
436
437 COMMIT;
438
439 EXCEPTION
440 WHEN OTHERS THEN
441 ROLLBACK;
442 APP_EXCEPTION.RAISE_EXCEPTION;
443
444 END Start_inactive_emp_process;
445
446 FUNCTION GetInactEmpCcardTrxnCursor(
447 p_cardProgramId IN ccTrxn_cardProgID,
448 p_employeeId IN perEmp_employeeID,
449 p_billedStartDate IN ccTrxn_billedDate,
450 p_billedEndDate IN ccTrxn_billedDate,
451 p_itemkey IN wfItems_item_key,
452 p_Inact_Emp_trx_cursor OUT NOCOPY InactEmpCCTrxnCursor
453 ) RETURN BOOLEAN IS
454
455 -- Function Name: GetInactEmpCcardTrxnCursor
456 -- Author: Geetha Gurram
457 -- Purpose: Retrieves all the CC trx of the inactive employee which fall in the range
458 -- of billed start and end date
459 --
460 -- Input: p_cardProgramId
461 -- p_employeeId
462 -- p_billedStartDate
463 -- p_billedEndDate
464 --
465 -- Output: p_Inact_Emp_trx_cursor
466 --
467 -- Assumptions:
468 --
469 -- Notes: Inactive Employee Workflow Processs
470 --
471 l_debug_info VARCHAR2(200);
472 BEGIN
473
474 OPEN p_Inact_Emp_trx_cursor FOR
475
476 SELECT transaction_date,
477 merchant_name1,
478 merchant_city,
479 billed_amount,
480 billed_currency_code,
481 null invoice_num
482 FROM
483 ap_credit_card_trxns cct,
484 ap_cards ac
485 WHERE cct.card_program_id = p_cardProgramId
486 and cct.validate_code = 'Y'
487 and cct.payment_flag <> 'Y'
488 and nvl(cct.expensed_amount,0) = 0
489 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
490 and ac.card_program_id = cct.card_program_id
491 and ac.card_id = cct.card_id
492 and nvl(cct.billed_date, cct.posted_date) between
493 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
494 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
495 and ac.employee_id = p_employeeId
496 and cct.inactive_emp_wf_item_key = p_itemkey
497 and cct.report_header_id is NULL
498 union all
499 SELECT transaction_date,
500 merchant_name1,
501 merchant_city,
502 billed_amount,
503 billed_currency_code,
504 erh.invoice_num
505 FROM
506 ap_credit_card_trxns cct,
507 ap_cards ac,
508 ap_expense_report_headers erh
509 WHERE
510 cct.card_program_id = p_cardProgramId
511 and cct.validate_code = 'Y'
512 and cct.payment_flag <> 'Y'
513 and cct.expensed_amount <> 0
514 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED','DEACTIVATED')
515 and ac.card_program_id = cct.card_program_id
516 and ac.card_id = cct.card_id
517 and nvl(cct.billed_date, cct.posted_date) between
518 nvl(p_billedStartDate, nvl(cct.billed_date, cct.posted_date)-1) and
519 nvl(p_billedEndDate, nvl(cct.billed_date, cct.posted_date)+1)
520 and erh.report_header_id = cct.report_header_id
521 and AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(erh.source,erh.workflow_approved_flag,erh.report_header_id) in ('EMPAPPR','RESOLUTN','RETURNED','REJECTED','WITHDRAWN', 'SAVED')
522 and ac.employee_id = p_employeeId
523 and cct.inactive_emp_wf_item_key = p_itemkey
524 and rownum < 41
525 order by transaction_date;
526
527 RETURN TRUE;
528
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GetInactEmpCcardTrxnCursor',
532 to_char(0), to_char(0), to_char(0), l_debug_info || FND_MESSAGE.GET);
533 RETURN FALSE;
534
535 WHEN OTHERS THEN
536
537 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'GetInactEmpCcardTrxnCursor',
538 to_char(0), to_char(0), to_char(0), l_debug_info || FND_MESSAGE.GET);
539 APP_EXCEPTION.RAISE_EXCEPTION;
540 RETURN FALSE;
541
542 END GetInactEmpCcardTrxnCursor;
543
544 PROCEDURE GenerateCCTrxList(document_id IN VARCHAR2,
545 display_type IN VARCHAR2,
546 document IN OUT NOCOPY VARCHAR2,
547 document_type IN OUT NOCOPY VARCHAR2)IS
548
549 -- Function Name: GenerateCCTrxList
550 -- Author: Geetha Gurram
551 -- Purpose: Generates a CC trx list of text/HTML document type
552 --
553 -- Input: document_id
554 -- display_type
555 -- document
556 -- document_type
557 --
558 -- Output: document
559 -- document_type
560 --
561 -- Assumptions:
562 --
563 -- Notes: Inactive Employee Workflow Processs
564 --
565
566 l_colon NUMBER;
567 l_itemtype VARCHAR2(7);
568 l_itemkey VARCHAR2(15);
569 l_cardProgramId AP_WEB_DB_CCARD_PKG.ccTrxn_cardProgID;
570 l_billedStartDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
571 l_billedEndDate AP_WEB_DB_CCARD_PKG.ccTrxn_billedDate;
572 l_minimumAmount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
573 l_employeeId AP_WEB_DB_CCARD_PKG.cards_employeeID;
574 l_dateFormat VARCHAR2(30);
575 l_transaction_date AP_WEB_DB_CCARD_PKG.ccTrxn_transDate;
576 l_merchant_name1 AP_WEB_DB_CCARD_PKG.ccTrxn_merchantName1;
577 l_merchant_City AP_WEB_DB_CCARD_PKG.ccTrxn_merchantCity;
578 l_billed_amount AP_WEB_DB_CCARD_PKG.ccTrxn_billedAmount;
579 l_total_billed_amt NUMBER := 0;
580 l_billed_currency_code AP_WEB_DB_CCARD_PKG.ccTrxn_billedCurrCode;
581 l_lineInfo VARCHAR2(2000);
582 l_debugInfo VARCHAR2(1000);
583 l_orgId NUMBER;
584 l_InactEmpCCTrxn_cursor InactEmpCCTrxnCursor;
585 l_debug_info VARCHAR2(1000);
586 l_expense_report_number VARCHAR2(60);
587 l_prompts AP_WEB_UTILITIES_PKG.prompts_table;
588 l_title AK_REGIONS_VL.name%TYPE;
589 l_expense_report_status VARCHAR2(30);
590 l_displayed_status VARCHAR2(60);
591 l_total_dsp VARCHAR2(50);
592
593
594 BEGIN
595
596 l_debugInfo := 'Decode document_id';
597 l_colon := instrb(document_id, ':');
598 l_debugInfo := l_debugInfo || ' First index: ' || to_char(l_colon);
599 l_itemtype := substrb(document_id, 1, l_colon - 1);
600 l_itemkey := substrb(document_id, l_colon + 1);
601
602 l_debugInfo := 'Get org_id';
603 l_orgId := WF_ENGINE.GetItemAttrNumber(l_itemtype, l_itemKey, 'ORG_ID');
604
605 -- MOAC UPTAKE --
606 -- Should not have to initialize the org context
607 -- This is done via callbackfunction()
608 -- l_debugInfo := 'Set Org context';
609 -- fnd_client_info.set_org_context(l_orgId);
610
611 ------------------------------------------------------------
612 l_debugInfo := 'Get prompts';
613 ------------------------------------------------------------
614 AP_WEB_DISC_PKG.getPrompts(200,'AP_WEB_WF_INAC_CC_LINETABLE',l_title,l_prompts);
615
616 l_debugInfo := 'Generate header';
617 if (display_type = 'text/plain') then
618 document := '';
619 else -- html
620 document := '<table border=0 cellpadding=2>';
621 document := document || '<tr bgcolor='||th_bgcolor||'>';
622 document := document || '<th><font color='||th_fontcolor||' face='||th_fontface||'><b>' || l_prompts(1) || '</b></th>';
623 document := document || '<th><font color='||th_fontcolor||' face='||th_fontface||'><b>' || l_prompts(2) || '</b></th>';
624 document := document || '<th><font color='||th_fontcolor||' face='||th_fontface||'><b>' || l_prompts(3) || '</b></th>';
625 document := document || '<th><font color='||th_fontcolor||' face='||th_fontface||'><b>' || l_prompts(4) || '</b></th>';
626 document := document || '<th><font color='||th_fontcolor||' face='||th_fontface||'><b>' || l_prompts(5) || '</b></th></tr>';
627 end if;
628
629
630 ------------------------------------------------------
631 l_debugInfo := 'Get WF CC_TRX_BEGIN_DATE Item Attribute';
632 ------------------------------------------------------
633 l_billedStartDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'CC_TRX_BEGIN_DATE');
634
635 ------------------------------------------------------
636 l_debugInfo := 'Get WF CC_TRX_END_DATE Item Attribute';
637 ------------------------------------------------------
638 l_billedEndDate := WF_ENGINE.GetItemAttrDate(l_itemType, l_itemKey, 'CC_TRX_END_DATE');
639
640 --------------------------------------------------------------
641 l_debugInfo := 'Get WF CARD_PROG_ID Item Attribute';
642 --------------------------------------------------------------
643 l_cardProgramID := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'CARD_PROG_ID');
644
645 --------------------------------------------------------------
646 l_debugInfo := 'Get WF INACT_EMPLOYEE_ID Item Attribute';
647 --------------------------------------------------------------
648 l_employeeID := WF_ENGINE.GetItemAttrNumber(l_itemType, l_itemKey, 'INACT_EMPLOYEE_ID');
649
650 l_dateFormat := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
651
652 l_debugInfo := 'Loop over all the disputed charges';
653
654 --------------------------------------------
655 l_debug_info := 'Open Expense Lines Cursor';
656 --------------------------------------------
657 IF (GetInactEmpCcardTrxnCursor(l_cardProgramId,
658 l_employeeId, l_billedStartDate,
659 l_billedEndDate, l_itemKey , l_InactEmpCCTrxn_cursor)) THEN
660
661 LOOP
662 FETCH l_InactEmpCCTrxn_cursor
663 INTO l_transaction_date,
664 l_merchant_name1,
665 l_merchant_city,
666 l_billed_amount,
667 l_billed_currency_code,
668 l_expense_report_number;
669 EXIT WHEN l_InactEmpCCTrxn_cursor%NOTFOUND;
670
671
672 l_total_billed_amt := l_total_billed_amt + l_billed_amount;
673 l_total_dsp := to_char(l_total_billed_amt, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22));
674 l_total_dsp := l_total_dsp || ' ' || l_billed_currency_code;
675
676 WF_ENGINE.SetItemAttrText(l_itemtype,
677 l_itemkey,
678 'DISPLAY_TOTAL',
679 l_total_dsp);
680
681
682 IF (display_type = 'text/plain') THEN
683 --------------------------------------------
684 l_debugInfo := 'Format Line Info';
685 --------------------------------------------
686 l_lineInfo := to_char(l_transaction_date,l_dateFormat) || ' ' ||
687 l_merchant_name1 || ' ' || to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22));
688 -- set a new line
689 document := document || '' || l_lineInfo;
690 l_lineInfo := '';
691 ELSE -- HTML type
692 document := document || '<tr bgcolor='||th_bgcolor||'>';
693 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| to_char(l_transaction_date,l_dateFormat) || '</td>';
694 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| to_char(l_billed_amount, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)) || '</td>';
695 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| l_merchant_name1 || '</td>';
696 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| l_merchant_city || '</td>';
697 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| l_expense_report_number || '</td>';
698 END IF;
699
700
701 END LOOP;
702 --------------------------------------------
703 l_debug_info := 'Generate Total Row';
704 --------------------------------------------
705 document := document || '<tr bgcolor='||th_bgcolor||'>';
706 document := document || '<td align="right"><font color='||th_fontcolor||' face='||th_fontface||'><b>' || 'Total' || '</b></td>';
707 document := document || '<td bgcolor='||td_bgcolor||' align="left"><font color='||td_fontcolor||' face='||td_fontface||'>'|| LPAD(to_char(l_total_billed_amt, FND_CURRENCY.Get_Format_Mask(l_billed_currency_code,22)),14) || '</td>';
708 document := document || '</tr>';
709 document := document || '</table><br>';
710 END iF;
711
712 close l_InactEmpCCTrxn_cursor;
713
714 if (display_type = 'text/html') then
715 document := document || '</table>';
716 end if;
717
718 document_type := display_type;
719
720 EXCEPTION
721 WHEN OTHERS THEN
722 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'GenerateCCTrxList',
723 document_id, l_debugInfo);
724 raise;
725 END GenerateCCTrxList;
726
727 PROCEDURE FindActiveMAnager(p_item_type IN VARCHAR2,
728 p_item_key IN VARCHAR2,
729 p_actid IN NUMBER,
730 p_funmode IN VARCHAR2,
731 p_result OUT NOCOPY VARCHAR2) IS
732
733 -- Function Name: FindActiveMAnager
734 -- Author: Geetha Gurram
735 -- Purpose: Finds the Active Supervisor of the Inactive Employee
736 --
737 -- Input: p_item_type
738 -- p_item_key
739 -- p_actid
740 -- p_funmode
741 --
742 -- Output: p_result
743 --
744 -- Assumptions:
745 --
746 -- Notes: Inactive Employee Workflow Processs
747 --
748
749 l_dummy_inact_emp_mang_name VARCHAR2(240);
750 l_inact_emp_mang_num VARCHAR2(30);
751 l_inact_emp_mang_cost_center VARCHAR2(240);
752 l_inact_employee_id NUMBER;
753 l_supervisor_id NUMBER;
754 l_inact_emp_manager_id NUMBER;
755 l_inact_emp_cost_center VARCHAR2(30);
756 l_preparer_name VARCHAR2(30);
757 l_preparer_display_name VARCHAR2(80);
758 l_preparer_empl_id NUMBER;
759 l_forward_from_id NUMBER;
760 l_debug_info VARCHAR2(200);
761 l_employee_id NUMBER;
762 l_preparer_org_id NUMBER;
763 l_dummy_var BOOLEAN;
764 l_forward_from_name VARCHAR2(50);
765 l_manager_name VARCHAR2(240);
766
767 Begin
768
769
770 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start FindActiveMAnager');
771
772 IF (p_funmode = 'RUN') THEN
773
774 ------------------------------------------------------------
775 l_debug_info := 'Retrieve INACT_EMPLOYEE_ID Item Attribute';
776 ------------------------------------------------------------
777 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
778 p_item_key,
779 'INACT_EMPLOYEE_ID');
780
781 -----------------------------------------------------
782 l_debug_info := 'Get FORWARD_FROM_ID Item Attribute';
783 -----------------------------------------------------
784 l_forward_from_id := WF_ENGINE.GetItemAttrNUMBER(p_item_type,
785 p_item_key,
786 'FORWARD_FROM_ID');
787
788 -----------------------------------------------------
789 l_debug_info := 'Get PREPARER_EMPL_ID Item Attribute';
790 -----------------------------------------------------
791 l_preparer_empl_id := WF_ENGINE.GetItemAttrNUMBER(p_item_type,
792 p_item_key,
793 'PREPARER_EMPL_ID');
794
795
796 -----------------------------------------------------
797 l_debug_info := 'Get FORWARD_FROM_NAME Item Attribute';
798 -----------------------------------------------------
799
800 l_forward_from_name := WF_ENGINE.GetItemAttrText(p_item_type,
801 p_item_key,
802 'FORWARD_FROM_NAME');
803 IF l_forward_from_name is null then
804 l_preparer_empl_id := NULL;
805 end if;
806
807
808 l_employee_id := NVL(l_preparer_empl_id, l_inact_employee_id);
809
810 -----------------------------------------------------------------------------------
811 l_debug_info := 'Get the Manager Information Associated With Inactive Employee Id';
812 -----------------------------------------------------------------------------------
813
814
815 IF ((AP_WEB_DB_HR_INT_PKG.GetSupervisorInfo( l_employee_id ,l_supervisor_id, l_manager_name, l_preparer_org_id)= FALSE) OR l_supervisor_id is NULL) THEN
816 p_result := 'COMPLETE:N';
817 l_supervisor_id := NULL;
818 ELSE
819
820 WHILE (l_supervisor_id IS NOT NULL) LOOP
821
822 l_inact_emp_manager_id := l_supervisor_id;
823
824 -----------------------------------------------------------------------------------
825 l_debug_info := 'Get the Manager Cost Center Associated With Inactive Employee Id';
826 -----------------------------------------------------------------------------------
827
828 AP_WEB_UTILITIES_PKG.GetEmployeeInfo(l_dummy_inact_emp_mang_name,
829 l_inact_emp_mang_num,
830 l_inact_emp_cost_center,
831 l_inact_emp_manager_id);
832
833 IF l_dummy_inact_emp_mang_name IS NULL THEN
834 IF ((AP_WEB_DB_HR_INT_PKG.GetSupervisorInfo( l_employee_id ,l_supervisor_id, l_manager_name, l_preparer_org_id)= FALSE) OR l_supervisor_id is NULL) THEN
835 p_result := 'COMPLETE:N';
836 l_supervisor_id := NULL;
837 ELSE
838 l_inact_emp_manager_id := l_supervisor_id;
839 END IF;
840 ELSE
841 l_preparer_empl_id := l_inact_emp_manager_id;
842 l_supervisor_id := NULL;
843
844
845 ---------------------------------------------------------
846 l_debug_info := 'Set Item Attribute Preparer Org ID';
847 ---------------------------------------------------------
848 WF_ENGINE.SetItemAttrNumber(p_item_type,
849 p_item_key,
850 'PREPARER_ORG_ID',
851 l_preparer_org_id);
852
853 ---------------------------------------------------------
854 l_debug_info := 'Set Item Attribute Preparer EMPL ID';
855 ---------------------------------------------------------
856 WF_ENGINE.SetItemAttrNumber(p_item_type,
857 p_item_key,
858 'PREPARER_EMPL_ID',
859 l_PREPARER_EMPL_ID);
860 ----------------------------------------------------------
861 l_debug_info := 'Get Preparer Name Info For PREPARER_EMPL_ID';
862 ----------------------------------------------------------
863 WF_DIRECTORY.GetUserName('PER',
864 l_PREPARER_EMPL_ID,
865 l_preparer_name,
866 l_preparer_display_name);
867 ----------------------------------------------------------
868 l_debug_info := 'Set Preparer Name Info Item Attributes';
869 ----------------------------------------------------------
870 WF_ENGINE.SetItemAttrText(p_item_type,
871 p_item_key,
872 'PREPARER_NAME',
873 l_preparer_name);
874
875 ----------------------------------------------------------
876 l_debug_info := 'Set Preparer Display Name Item Attributes';
877 ----------------------------------------------------------
878
879 WF_ENGINE.SetItemAttrText(p_item_type,
880 p_item_key,
881 'PREPARER_DISPLAY_NAME',
882 l_preparer_display_name);
883
884 p_result := 'COMPLETE:Y';
885
886 END IF;
887
888 End Loop;
889
890 END IF;
891
892
893 ELSIF (p_funmode = 'CANCEL') THEN
894
895 p_result := 'COMPLETE';
896
897 END IF;
898
899 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end FindActiveMAnager');
900
901 EXCEPTION
902 WHEN OTHERS THEN
903 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'FindActiveMAnager',
904 p_item_type, p_item_key, to_char(0), l_debug_info || FND_MESSAGE.GET);
905 raise;
906 END FindActiveMAnager;
907
908 PROCEDURE SetAPRolePreparer(p_item_type IN VARCHAR2,
909 p_item_key IN VARCHAR2,
910 p_actid IN NUMBER,
911 p_funmode IN VARCHAR2,
912 p_result OUT NOCOPY VARCHAR2)
913 IS
914 -- Function Name: SetAPRolePreparer
915 -- Author: Geetha Gurram
916 -- Purpose: Sets exception AP Role Preparer Attribute
917 --
918 -- Input: p_item_type
919 -- p_item_key
920 -- p_actid
921 -- p_funmode
922 --
923 -- Output: p_result
924 --
925 -- Assumptions:
926 --
927 -- Notes: Inactive Employee Workflow Processs
928 --
929
930 l_set_preparer_to_role VARCHAR2(30);
931 l_role_display_name VARCHAR2(100);
932 l_debug_info VARCHAR2(200);
933 l_role_org_id NUMBER;
934
935 BEGIN
936 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start SetAPRolePreparer');
937
938 IF (p_funmode = 'RUN') THEN
939
940 ------------------------------------------------------------
941 l_debug_info := 'Retrieve AP_EXCEPTION_ROLE Item Attribute';
942 ------------------------------------------------------------
943
944 l_set_preparer_to_role := WF_ENGINE.GetItemAttrText(p_item_type, p_item_key, 'AP_EXCEPTION_ROLE');
945
946 ------------------------------------------------------------------
947 l_debug_info := 'Set Role Name to Preparer Name Item Attributes';
948 ------------------------------------------------------------------
949
950 WF_ENGINE.SetItemAttrText(p_item_type,
951 p_item_key,
952 'PREPARER_NAME',
953 l_set_preparer_to_role);
954
955 ---------------------------------------------------------------------------------
956 l_debug_info := 'Get Role Display Name to Preparer Display Name Item Attributes';
957 ---------------------------------------------------------------------------------
958
959 l_role_display_name := WF_DIRECTORY.GetRoleDisplayName(l_set_preparer_to_role);
960
961 -------------------------------------------------------------------------
962 l_debug_info := 'Set Role Name to Preparer Display Name Item Attributes';
963 -------------------------------------------------------------------------
964
965 WF_ENGINE.SetItemAttrText(p_item_type,
966 p_item_key,
967 'PREPARER_DISPLAY_NAME',
968 l_role_display_name);
969
970 --------------------------------------------------------------
971 l_debug_info := 'Get Org_Id for Role ';
972 --------------------------------------------------------------
973
974 /* Setting the Inactive Employee Org ID to be the Org ID of the Exception role Org ID */
975
976 l_role_org_id := WF_ENGINE.GetItemAttrNumber(p_item_type,
977 p_item_key,
978 'INACT_EMPLOYEE_ORG_ID');
979
980 -------------------------------------------------------------------------
981 l_debug_info := 'Set PREPARER_ORG_ID Item Attribute';
982 -------------------------------------------------------------------------
983
984 WF_ENGINE.SetItemAttrNumber(p_item_type,
985 p_item_key,
986 'PREPARER_ORG_ID',
987 l_role_org_id);
988
989
990 p_result := 'COMPLETE:Y';
991
992 ELSIF (p_funmode = 'CANCEL') THEN
993 p_result := 'COMPLETE';
994 END IF;
995
996 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end SetAPRolePreparer');
997
998 EXCEPTION
999 WHEN OTHERS THEN
1000 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'SetAPRolePreparer',
1001 p_item_type, p_item_key, to_char(0), l_debug_info || FND_MESSAGE.GET);
1002 raise;
1003 END SetAPRolePreparer;
1004
1005 PROCEDURE SetFromRoleForwardFrom(p_item_type IN VARCHAR2,
1006 p_item_key IN VARCHAR2,
1007 p_actid IN NUMBER,
1008 p_funmode IN VARCHAR2,
1009 p_result OUT NOCOPY VARCHAR2) IS
1010
1011 -- Function Name: SetFromRoleForwardFrom
1012 -- Author: Geetha Gurram
1013 -- Purpose: Sets Attribute value for "From Role" Forward from
1014 --
1015 -- Input: p_item_type
1016 -- p_item_key
1017 -- p_actid
1018 -- p_funmode
1019 --
1020 -- Output: p_result
1021 --
1022 -- Assumptions:
1023 --
1024 -- Notes: Inactive Employee Workflow Processs
1025
1026 l_debug_info VARCHAR2(200);
1027 BEGIN
1028
1029 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start SetFromRoleForwardFrom');
1030
1031 IF (p_funmode = 'RUN') THEN
1032 ----------------------------------------------------------------
1033 l_debug_info := 'Set #FROM_ROLE to Forward From';
1034 ----------------------------------------------------------------
1035 WF_ENGINE.SetItemAttrText(p_item_type,
1036 p_item_key,
1037 '#FROM_ROLE',
1038 WF_ENGINE.GetItemAttrText(p_item_type,
1039 p_item_key,
1040 'FORWARD_FROM_NAME'));
1041 p_result := 'COMPLETE:Y';
1042
1043 ELSIF (p_funmode = 'CANCEL') THEN
1044 p_result := 'COMPLETE';
1045 END IF;
1046
1047 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end SetFromRoleForwardFrom');
1048
1049 EXCEPTION
1050 WHEN OTHERS THEN
1051 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'SetFromRoleForwardFrom',
1052 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1053 raise;
1054 END SetFromRoleForwardFrom;
1055
1056 PROCEDURE RecordForwardFromInfo(p_item_type IN VARCHAR2,
1057 p_item_key IN VARCHAR2,
1058 p_actid IN NUMBER,
1059 p_funmode IN VARCHAR2,
1060 p_result OUT NOCOPY VARCHAR2) IS
1061
1062 -- Function Name: RecordForwardFromInfo
1063 -- Author: Geetha Gurram
1064 -- Purpose: Sets Attribute value for "From Role" Forward from
1065 --
1066 -- Input: p_item_type
1067 -- p_item_key
1068 -- p_actid
1069 -- p_funmode
1070 --
1071 -- Output: p_result
1072 --
1073 -- Assumptions:
1074 --
1075 -- Notes: Inactive Employee Workflow Processs
1076
1077 l_PREPARER_EMPL_ID NUMBER;
1078 l_preparer_name VARCHAR2(30);
1079 l_preparer_display_name VARCHAR2(80);
1080 l_debug_info VARCHAR2(200);
1081 BEGIN
1082
1083 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start RecordForwardFromInfo');
1084
1085 IF (p_funmode = 'RUN') THEN
1086
1087 ------------------------------------------------------------
1088 l_debug_info := 'Retrieve Preparer_Info Item Attributes';
1089 ------------------------------------------------------------
1090 l_PREPARER_EMPL_ID := WF_ENGINE.GetItemAttrNumber(p_item_type,
1091 p_item_key,
1092 'PREPARER_EMPL_ID');
1093
1094 l_preparer_name := WF_ENGINE.GetItemAttrText(p_item_type,
1095 p_item_key,
1096 'PREPARER_NAME');
1097
1098 l_preparer_display_name := WF_ENGINE.GetItemAttrText(p_item_type,
1099 p_item_key,
1100 'PREPARER_DISPLAY_NAME');
1101
1102 ----------------------------------------------------------------------
1103 l_debug_info := 'Set Forward_From Item Attributes With Approver Info';
1104 ----------------------------------------------------------------------
1105 WF_ENGINE.SetItemAttrNUMBER(p_item_type,
1106 p_item_key,
1107 'FORWARD_FROM_ID',
1108 l_PREPARER_EMPL_ID);
1109
1110 WF_ENGINE.SetItemAttrText(p_item_type,
1111 p_item_key,
1112 'FORWARD_FROM_NAME',
1113 l_preparer_name);
1114
1115 WF_ENGINE.SetItemAttrText(p_item_type,
1116 p_item_key,
1117 'FORWARD_FROM_DISPLAY_NAME',
1118 l_preparer_display_name);
1119
1120 p_result := 'COMPLETE:Y';
1121
1122 ELSIF (p_funmode = 'CANCEL') THEN
1123
1124 p_result := 'COMPLETE';
1125
1126 END IF;
1127
1128 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end RecordForwardFromInfo');
1129
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'RecordForwardFromInfo',
1133 p_item_type, p_item_key, to_char(p_actid), l_debug_info);
1134 raise;
1135 END RecordForwardFromInfo;
1136
1137 PROCEDURE SetMangInfoPrepNoResp(itemtype in varchar2,
1138 itemkey in varchar2,
1139 actid in number,
1140 funcmode in varchar2,
1141 resultout in out NOCOPY varchar2)IS
1142
1143 -- Function Name: SetMangInfoPrepNoResp
1144 -- Author: Geetha Gurram
1145 -- Purpose: Sets Attribute value : Note for the preparer that the notification is forwarded from preparer,
1146 -- who failed to respond
1147 --
1148 -- Input: p_item_type
1149 -- p_item_key
1150 -- p_actid
1151 -- p_funmode
1152 --
1153 -- Output: p_result
1154 --
1155 -- Assumptions:
1156 --
1157 -- Notes: Inactive Employee Workflow Processs
1158
1159 l_preparer_display_name VARCHAR2(100);
1160 l_note_mang_prep_no_resp VARCHAR2(2000);
1161 l_debug_info VARCHAR2(200);
1162
1163 BEGIN
1164
1165 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start SetMangInfoPrepNoResp');
1166
1167
1168 if (funcmode = 'RUN') then
1169
1170 l_preparer_display_name := WF_ENGINE.GetItemAttrText(itemtype,
1171 itemkey,
1172 'PREPARER_DISPLAY_NAME');
1173
1174 --------------------------------------------------------------------
1175 l_debug_info := 'Get No Response from the Preparer from FND_MESSAGE';
1176 ---------------------------------------------------------------------
1177
1178 fnd_message.set_name('SQLAP','OIE_INACT_PREPARER_NO_RESP');
1179 fnd_message.set_token('PREPARER_NAME', l_preparer_display_name);
1180 l_note_mang_prep_no_resp := fnd_message.get;
1181
1182 WF_ENGINE.SetItemAttrText(itemtype,
1183 itemkey,
1184 'NOTE_MANG_PREP_NO_RESPONSE',
1185 l_note_mang_prep_no_resp);
1186
1187 resultout := 'COMPLETE:Y';
1188
1189 ELSIF (funcmode = 'CANCEL') THEN
1190
1191 resultout := 'COMPLETE';
1192
1193 END IF;
1194
1195 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end SetMangInfoPrepNoResp');
1196
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'SetMangInfoPrepNoResp',
1200 itemtype, itemkey, to_char(actid), l_debug_info);
1201 raise;
1202 end SetMangInfoPrepNoResp;
1203
1204 PROCEDURE CheckMangSecAttr(itemtype in varchar2,
1205 itemkey in varchar2,
1206 actid in number,
1207 funcmode in varchar2,
1208 resultout in out NOCOPY varchar2)
1209 IS
1210
1211 -- Function Name: CheckMangSecAttr
1212 -- Author: Geetha Gurram
1213 -- Purpose: Checks Managers has securing Attribute to create inactive employees expense report
1214 --
1215 -- Input: itemtype
1216 -- itemkey
1217 -- actid
1218 -- funmode
1219 --
1220 -- Output: resultout Yes/No
1221 --
1222 -- Assumptions:
1223 --
1224 -- Notes: Inactive Employee Workflow Processs
1225
1226 l_preparer_Userid AP_WEB_DB_HR_INT_PKG.fndUser_userID;
1227 l_preparer_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
1228 l_number_of_emp_user NUMBER;
1229 l_check_mang_sec_attribute BOOLEAN := FALSE;
1230 l_emplist_for_webuser AP_WEB_DB_HR_INT_PKG.EmpNameCursor;
1231 l_temp_emplist_for_webuser AP_WEB_DB_HR_INT_PKG.EmpNameCursor;
1232 l_preparer_emp_id NUMBER;
1233 l_debug_info VARCHAR2(200);
1234 l_inactive_employee_id NUMBER;
1235 l_employee_id AP_WEB_DB_HR_INT_PKG.usrSecAttr_webUserID;
1236 l_employee_name AP_WEB_DB_HR_INT_PKG.empCurrent_fullName;
1237 l_preparer_web_user_id NUMBER;
1238 l_preparer_name VARCHAR2(30);
1239
1240
1241 BEGIN
1242
1243 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start CheckMangSecAttr');
1244
1245
1246 IF (funcmode = 'RUN') then
1247
1248 ------------------------------------------------------------------
1249 l_debug_info := 'Retrieve PREPARER_EMPL_ID Item Attributes';
1250 ------------------------------------------------------------------
1251 l_preparer_emp_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1252 itemkey,
1253 'PREPARER_EMPL_ID');
1254
1255 -------------------------------------------------------------------
1256 l_debug_info := 'Retrieve PREPARER_NAME Item Attributes';
1257 -------------------------------------------------------------------
1258 l_preparer_name := WF_ENGINE.GetItemAttrText(itemtype,
1259 itemkey,
1260 'PREPARER_NAME');
1261
1262
1263 -------------------------------------------------------------------
1264 l_debug_info := 'Retrieve INACT_EMPLOYEE_ID Item Attributes';
1265 -------------------------------------------------------------------
1266
1267
1268 l_inactive_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1269 itemkey,
1270 'INACT_EMPLOYEE_ID');
1271
1272
1273 ---------------------------------------------
1274 l_debug_info := 'Getting Preparer UserId';
1275 ---------------------------------------------
1276
1277 begin
1278
1279 IF (GetUserIdForEmp(l_preparer_name, l_preparer_web_user_id)
1280 = FALSE) THEN
1281 l_check_mang_sec_attribute := False;
1282 resultout := 'COMPLETE:N';
1283 END IF;
1284
1285 EXCEPTION
1286 WHEN OTHERS THEN
1287 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckMangSecAttr',
1288 itemtype, itemkey, to_char(actid), l_debug_info);
1289 raise;
1290 end;
1291
1292 ------------------------------------------------------------
1293 l_debug_info := 'Getting WebUser for preparer employee ID';
1294 ------------------------------------------------------------
1295
1296 IF ( AP_WEB_DB_HR_INT_PKG.GetAllEmpListForWebUserCursor(
1297 l_preparer_web_user_id,
1298 l_emplist_for_webuser) = TRUE ) THEN
1299 LOOP
1300 FETCH l_emplist_for_webuser INTO l_employee_id, l_employee_name;
1301 If l_employee_id = l_inactive_employee_id then
1302 l_check_mang_sec_attribute := True;
1303 end if;
1304 EXIT WHEN l_emplist_for_webuser%NOTFOUND;
1305 END LOOP;
1306 CLOSE l_emplist_for_webuser;
1307
1308 END IF;
1309
1310 IF l_check_mang_sec_attribute = True THEN
1311 resultout := 'COMPLETE:Y';
1312 ELSE
1313 resultout := 'COMPLETE:N';
1314 END IF;
1315
1316
1317 ELSIF (funcmode = 'CANCEL') THEN
1318
1319 resultout := 'COMPLETE';
1320
1321 END IF;
1322
1323
1324 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end CheckMangSecAttr');
1325
1326 EXCEPTION
1327 WHEN OTHERS THEN
1328 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckMangSecAttr',
1329 itemtype, itemkey, to_char(actid), l_debug_info);
1330 raise;
1331 END CheckMangSecAttr;
1332
1333 PROCEDURE AddSecAttrPreparer(itemtype in varchar2,
1334 itemkey in varchar2,
1335 actid in number,
1336 funcmode in varchar2,
1337 resultout in out NOCOPY varchar2)
1338 IS
1339
1340 -- Function Name: AddSecAttrPreparer
1341 -- Author: Geetha Gurram
1342 -- Purpose: Add securing Attribute to create inactive employees expense report
1343 --
1344 -- Input: itemtype
1345 -- itemkey
1346 -- actid
1347 -- funmode
1348 --
1349 -- Output: resultout
1350 --
1351 -- Assumptions:
1352 --
1353 -- Notes: Inactive Employee Workflow Processs
1354
1355 l_return_status VARCHAR2(2000);
1356 l_msg_count NUMBER;
1357 l_msg_data VARCHAR2(2000);
1358 l_preparer_emp_id NUMBER;
1359 l_inact_employee_id NUMBER;
1360 l_error VARCHAR2(2000);
1361 l_debug_info VARCHAR2(200);
1362 l_preparer_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
1363 l_preparer_web_user_id NUMBER;
1364 l_inact_employee_display_name VARCHAR2(100);
1365 l_preparer_display_name VARCHAR2(100);
1366 l_preparer_name VARCHAR2(100);
1367 l_error_preparer_name VARCHAR2(150);
1368 l_error_inact_empl_name VARCHAR2(150);
1369 l_error_instructions Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
1370 l_error_note Wf_Item_Attribute_Values.TEXT_VALUE%TYPE;
1371
1372
1373 BEGIN
1374 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start AddSecuringAttributePreparer');
1375
1376 IF (funcmode = 'RUN') then
1377
1378 ---------------------------------------------------------------
1379 l_debug_info := 'Retrieve Preparer Employee ID Item Attributes';
1380 ---------------------------------------------------------------
1381 l_preparer_emp_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1382 itemkey,
1383 'PREPARER_EMPL_ID');
1384
1385 ------------------------------------------------------------------------
1386 l_debug_info := 'Retrieve Preparer Employee Display Name Item Attributes';
1387 --------------------------------------------------------------------------
1388 l_preparer_display_name := WF_ENGINE.GetItemAttrText(itemtype,
1389 itemkey,
1390 'PREPARER_DISPLAY_NAME');
1391
1392 --------------------------------------------------------------------
1393 l_debug_info := 'Retrieve Preparer Employee Name Item Attributes';
1394 ----------------------------------------------------------------------
1395 l_preparer_name := WF_ENGINE.GetItemAttrText(itemtype,
1396 itemkey,
1397 'PREPARER_NAME');
1398
1399
1400 -------------------------------------------------------------------
1401 l_debug_info := 'Retrieve INACT_EMPLOYEE_ID Item Attributes';
1402 -------------------------------------------------------------------
1403
1404 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1405 itemkey,
1406 'INACT_EMPLOYEE_ID');
1407
1408 -----------------------------------------------------------------------
1409 l_debug_info := 'Retrieve INACT_EMPLOYEE_DISPLAY_NAME Item Attributes';
1410 -----------------------------------------------------------------------
1411
1412 l_inact_employee_display_name := WF_ENGINE.GetItemAttrText(itemtype,
1413 itemkey,
1414 'INACT_EMPLOYEE_DISPLAY_NAME');
1415 begin
1416
1417 IF ( GetUserIdForEmp(l_preparer_name, l_preparer_web_user_id)
1418 = FALSE) THEN
1419 resultout := 'COMPLETE:N';
1420 ------------------------------------------------------------------
1421 l_debug_info := 'No Preparer WEB UserId';
1422 ------------------------------------------------------------------
1423 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'AddSecAttrPreparer',
1424 itemtype, itemkey, to_char(actid), l_debug_info);
1425 ELSE
1426
1427 ---------------------------------------------------
1428 l_debug_info := 'Inserting Securing Attribute';
1429 --------------------------------------------------
1430
1431 ICX_User_Sec_Attr_PUB.Create_User_Sec_Attr (
1432 p_api_version_number => c_api_version_num,
1433 p_commit => c_commit,
1434 p_return_status => l_return_status,
1435 p_msg_count => l_msg_count,
1436 p_msg_data => l_msg_data,
1437 p_web_user_id => l_preparer_web_user_id,
1438 p_attribute_code => c_sec_attribute,
1439 p_attribute_appl_id => c_attribute_appl_id,
1440 p_varchar2_value => NULL,
1441 p_date_value => NULL,
1442 p_number_value => l_inact_employee_id,
1443 p_created_by => fnd_global.user_id,
1444 p_creation_date => SYSDATE,
1445 p_last_updated_by => fnd_global.user_id,
1446 p_last_update_date => SYSDATE,
1447 p_last_UPDATE_login => c_last_update_login);
1448
1449 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1450 format_message(l_return_status, l_msg_count, l_msg_data, l_error);
1451
1452 l_error_preparer_name := l_preparer_display_name || ' (' || l_preparer_name || ')';
1453 l_error_inact_empl_name := l_inact_employee_display_name || ' (' ||to_char(l_inact_employee_id)|| ')';
1454
1455
1456 WF_ENGINE.SetItemAttrText(itemtype,
1457 itemkey,
1458 'ERROR_MESSAGE',
1459 l_error);
1460
1461 WF_ENGINE.SetItemAttrText(itemtype,
1462 itemkey,
1463 'ERROR_ITEM_KEY',
1464 itemkey);
1465
1466 WF_ENGINE.SetItemAttrText(itemtype,
1467 itemkey,
1468 'ERROR_ITEM_TYPE',
1469 itemtype);
1470
1471 WF_ENGINE.SetItemAttrText(itemtype,
1472 itemkey,
1473 'ERROR_ACTIVITY_ID',
1474 actid);
1475
1476 WF_ENGINE.SetItemAttrText(itemtype,
1477 itemkey,
1478 'ERROR_INACT_EMPL_NAME',
1479 l_error_inact_empl_name);
1480
1481 WF_ENGINE.SetItemAttrText(itemtype,
1482 itemkey,
1483 'ERROR_PREPARER_NAME',
1484 l_error_preparer_name);
1485
1486 ------------------------------------------------------------
1487 l_debug_info := 'Get Error Instructions from FND_MESSAGE';
1488 ------------------------------------------------------------
1489
1490 fnd_message.set_name('SQLAP','OIE_INACT_ERROR_INSTRUCTIONS');
1491 l_error_instructions := fnd_message.get;
1492
1493 -------------------------------------------------------------
1494 l_debug_info := 'Set WF Error Instructions Item Attribute';
1495 -------------------------------------------------------------
1496
1497 WF_ENGINE.SetItemAttrText(itemtype,
1498 itemkey,
1499 'ERROR_INSTRUCTIONS',
1500 l_error_instructions);
1501
1502 ------------------------------------------------------------
1503 l_debug_info := 'Get Error Note from FND_MESSAGE';
1504 ------------------------------------------------------------
1505
1506 fnd_message.set_name('SQLAP','OIE_INACT_ERROR_NOTE');
1507 l_error_note := fnd_message.get;
1508
1509 -------------------------------------------------------------
1510 l_debug_info := 'Set WF Error Note Item Attribute';
1511 -------------------------------------------------------------
1512
1513 WF_ENGINE.SetItemAttrText(itemtype,
1514 itemkey,
1515 'ERROR_NOTE',
1516 l_error_note);
1517
1518 resultout := 'COMPLETE:AP_FAIL';
1519
1520 ELSE
1521 resultout := 'COMPLETE:AP_PASS';
1522
1523 END IF;
1524
1525
1526 END IF;
1527 END;
1528
1529
1530 ELSIF (funcmode = 'CANCEL') THEN
1531
1532 resultout := 'COMPLETE';
1533
1534 END IF;
1535
1536 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end AddSecAttrPreparer');
1537
1538 EXCEPTION
1539 WHEN OTHERS THEN
1540 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'AddSecAttrPreparer',
1541 itemtype, itemkey, to_char(actid), l_debug_info||l_error);
1542 raise;
1543 END AddSecAttrPreparer;
1544
1545 PROCEDURE RemoveSecAttrPreparer(itemtype in varchar2,
1546 itemkey in varchar2,
1547 actid in number,
1548 funcmode in varchar2,
1549 resultout in out NOCOPY varchar2)
1550 IS
1551
1552 -- Function Name: RemoveSecAttrPreparer
1553 -- Author: Geetha Gurram
1554 -- Purpose: Remove securing Attribute from the Preparer
1555 --
1556 -- Input: itemtype
1557 -- itemkey
1558 -- actid
1559 -- funmode
1560 --
1561 -- Output: resultout
1562 --
1563 -- Assumptions:
1564 --
1565 -- Notes: Inactive Employee Workflow Processs
1566
1567 l_return_status VARCHAR2(2000);
1568 l_msg_count NUMBER;
1569 l_msg_data VARCHAR2(2000);
1570 l_preparer_emp_id NUMBER;
1571 l_inact_employee_id NUMBER;
1572 l_error VARCHAR2(2000);
1573 l_debug_info VARCHAR2(200);
1574 l_preparer_Userid AP_WEB_DB_HR_INT_PKG.fndUser_userID;
1575 l_preparer_userIdCursor AP_WEB_DB_HR_INT_PKG.UserIdRefCursor;
1576 l_preparer_web_user_id NUMBER;
1577 l_inact_emp_web_user_id NUMBER;
1578 l_preparer_name VARCHAR2(50);
1579 l_inact_employee_name VARCHAR2(50);
1580
1581
1582 Cursor emp_sec_attr_cur(p_inact_employee_id in number,
1583 p_preparer_web_user_id in number)
1584 IS
1585 select web_user_id
1586 from ak_web_user_sec_attr_values
1587 where web_user_id = p_preparer_web_user_id
1588 and attribute_code = 'ICX_HR_PERSON_ID'
1589 and number_value = to_char(p_inact_employee_id);
1590
1591
1592 BEGIN
1593
1594 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start RemoveSecAttrPreparer');
1595
1596 IF (funcmode = 'RUN') then
1597 ------------------------------------------------------------------
1598 l_debug_info := 'Retrieve Preparer Employee ID Item Attributes';
1599 ------------------------------------------------------------------
1600 l_preparer_emp_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1601 itemkey,
1602 'PREPARER_EMPL_ID');
1603
1604 -------------------------------------------------------------------
1605 l_debug_info := 'Retrieve Inactive Employee Name Item Attributes';
1606 -------------------------------------------------------------------
1607 l_inact_employee_name := WF_ENGINE.GetItemAttrText(itemtype,
1608 itemkey,
1609 'INACT_EMP_NAME');
1610
1611 -------------------------------------------------------------------
1612 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
1613 -------------------------------------------------------------------
1614 l_preparer_name := WF_ENGINE.GetItemAttrText(itemtype,
1615 itemkey,
1616 'PREPARER_NAME');
1617
1618 -------------------------------------------------------------------
1619 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
1620 -------------------------------------------------------------------
1621
1622 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1623 itemkey,
1624 'INACT_EMPLOYEE_ID');
1625
1626 -------------------------------------------------
1627 l_debug_info := 'Getting Preparer WEB UserId';
1628 -------------------------------------------------
1629
1630 begin
1631
1632 IF (GetUserIdForEmp(l_preparer_name, l_preparer_web_user_id)
1633 = FALSE) THEN
1634 resultout := 'COMPLETE:N';
1635 ----------------------------------------------------
1636 l_debug_info := 'No Inact Empl WEB UserId';
1637 -----------------------------------------------------
1638 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'RemoveSecAttrPreparer',
1639 itemtype, itemkey, to_char(actid), l_debug_info);
1640 ELSE
1641
1642 open emp_sec_attr_cur(l_inact_employee_id,
1643 l_preparer_web_user_id);
1644 Loop
1645 fetch emp_sec_attr_cur into l_preparer_web_user_id;
1646 exit when emp_sec_attr_cur%notfound;
1647
1648 begin
1649 ---------------------------------------------------
1650 l_debug_info := 'Deleting Securing Attribute';
1651 --------------------------------------------------
1652
1653 ICX_User_Sec_Attr_PUB.Delete_User_Sec_Attr (
1654 p_api_version_number => c_api_version_num,
1655 p_commit => c_commit,
1656 p_return_status => l_return_status,
1657 p_msg_count => l_msg_count,
1658 p_msg_data => l_msg_data,
1659 p_web_user_id => l_preparer_web_user_id,
1660 p_attribute_code => c_sec_attribute,
1661 p_attribute_appl_id => c_attribute_appl_id,
1662 p_varchar2_value => NULL,
1663 p_date_value => NULL,
1664 p_number_value => l_inact_employee_id);
1665
1666 -- Bug 3320047 resultout was not set
1667 resultout := 'COMPLETE:Y';
1668
1669 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
1670 format_message(l_return_status, l_msg_count, l_msg_data, l_error);
1671 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'RemoveSecAttrPreparer',
1672 itemtype, itemkey, to_char(actid), l_error);
1673
1674 -- Bug 3320047 resultout was not set
1675 resultout := 'COMPLETE:N';
1676 ELSE
1677 resultout := 'COMPLETE:Y';
1678 END IF;
1679
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'RemoveSecAttrPreparer',
1683 itemtype, itemkey, to_char(actid), l_debug_info);
1684 raise;
1685 end;
1686
1687 end loop;
1688
1689 close emp_sec_attr_cur;
1690
1691 END IF;
1692 END;
1693
1694 ELSIF (funcmode = 'CANCEL') THEN
1695
1696 resultout := 'COMPLETE';
1697
1698 END IF;
1699
1700 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end RemoveSecAttrPreparer');
1701
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'RemoveSecAttrPreparer',
1705 itemtype, itemkey, to_char(actid), l_debug_info);
1706 raise;
1707 END RemoveSecAttrPreparer;
1708
1709 PROCEDURE Format_message(p_status IN VARCHAR2,
1710 p_msg_count IN NUMBER,
1711 p_msg_data IN VARCHAR2,
1712 p_error OUT NOCOPY VARCHAR2)
1713 IS
1714
1715 -- Function Name: Format_message
1716 -- Author: Geetha Gurram
1717 -- Purpose: Format error message
1718 --
1719 -- Input: p_status
1720 -- p_msg_count
1721 -- p_msg_data
1722 -- p_error
1723 --
1724 -- Output: resultout
1725 --
1726 -- Assumptions:
1727 --
1728 -- Notes: Inactive Employee Workflow Processs
1729
1730 l_error VARCHAR2(2000);
1731 l_debug_info VARCHAR2(200);
1732 BEGIN
1733
1734 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start format_message');
1735
1736 IF p_status = Fnd_Api.G_RET_STS_SUCCESS THEN
1737 p_error:= 'Status: Successful!';
1738 ELSIF p_status = Fnd_Api.G_RET_STS_ERROR THEN
1739 l_error := 'Status: Error! ';
1740 IF p_msg_count = 1 THEN
1741 l_error:= l_error || ' There is ' || p_msg_count || ' error:';
1742 ELSIF p_msg_count > 1 THEN
1743 l_error:= l_error || ' There are ' || p_msg_count || ' errors:';
1744 ELSE
1745 l_error:= l_error || ' error message:';
1746 END IF;
1747 p_error:= l_error || p_msg_data;
1748 END IF;
1749
1750 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end format_message');
1751
1752 END Format_message;
1753
1754 PROCEDURE CheckCCTransactionExists (itemtype in varchar2,
1755 itemkey in varchar2,
1756 actid in number,
1757 funcmode in varchar2,
1758 resultout in out NOCOPY varchar2)
1759 IS
1760
1761 -- Function Name: CheckCCTransactionExists
1762 -- Author: Geetha Gurram
1763 -- Purpose: Check if there are any more CC Transactions still exists for the inactive employee
1764 -- which have not been captured on expense report
1765 --
1766 -- Input: itemtype
1767 -- itemkey
1768 -- actid
1769 -- funmode
1770 --
1771 -- Output: resultout
1772 --
1773 -- Assumptions:
1774 --
1775 -- Notes: Inactive Employee Workflow Processs
1776
1777 l_cc_trx_exists NUMBER := 0;
1778 l_debug_info VARCHAR2(200);
1779 l_inact_employee_id NUMBER;
1780 l_credit_card_program_id NUMBER;
1781 l_cc_billed_start_date DATE;
1782 l_cc_billed_end_date DATE;
1783 l_itemkey VARCHAR2(30):= '';
1784
1785
1786 Cursor trx_exists_cur(p_credit_card_program_id in number,
1787 p_inact_employee_id in number,
1788 p_itemkey in varchar2,
1789 p_cc_billed_start_date in date,
1790 p_cc_billed_end_date in date) is
1791 select 1
1792 from dual
1793 where exists (select cct.trx_id
1794 from ap_cards_all ac,
1795 ap_credit_card_trxns cct
1796 where ac.card_program_id = p_credit_card_program_id
1797 and ac.employee_id = p_inact_employee_id
1798 and ac.card_program_id = cct.card_program_id
1799 and ac.card_id = cct.card_id
1800 and cct.validate_code = 'Y'
1801 and cct.payment_flag <> 'Y'
1802 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
1803 and cct.inactive_emp_wf_item_key = p_itemkey
1804 -- group by cct.trx_id
1805 minus
1806 (select cct.trx_id
1807 from ap_cards_all ac,
1808 ap_credit_card_trxns cct,
1809 ap_expense_report_headers erh
1810 where ac.card_program_id = p_credit_card_program_id
1811 and ac.card_program_id = cct.card_program_id
1812 and ac.card_id = cct.card_id
1813 and cct.validate_code = 'Y'
1814 and cct.payment_flag <> 'Y'
1815 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
1816 and cct.report_header_id = erh.report_header_id
1817 and erh.source <> 'NonValidatedWebExpense'
1818 and nvl(cct.billed_date, cct.posted_date) between p_cc_billed_start_date and p_cc_billed_end_date
1819 and ac.employee_id = p_inact_employee_id
1820 and cct.inactive_emp_wf_item_key = p_itemkey
1821 -- group by cct.trx_id
1822 ));
1823
1824 BEGIN
1825
1826 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start CheckCCTransactionExists');
1827
1828 l_itemkey := itemkey;
1829
1830 IF (funcmode = 'RUN') THEN
1831
1832
1833 -------------------------------------------------------------------
1834 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
1835 -------------------------------------------------------------------
1836
1837 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1838 itemkey,
1839 'INACT_EMPLOYEE_ID');
1840
1841 -------------------------------------------------------------------
1842 l_debug_info := 'Retrieve Credit Card Program ID Item Attributes';
1843 -------------------------------------------------------------------
1844
1845 l_credit_card_program_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1846 itemkey,
1847 'CARD_PROG_ID');
1848
1849 ------------------------------------------------------------
1850 l_debug_info := 'Retreive CC_TRX_BEGIN_DATE Item Attribute';
1851 ------------------------------------------------------------
1852
1853 l_cc_billed_start_date := WF_ENGINE.GetItemAttrDate(itemtype,
1854 itemkey,
1855 'CC_TRX_BEGIN_DATE');
1856
1857 ----------------------------------------------------------
1858 l_debug_info := 'Retreive Credit_END_DATE Item Attribute';
1859 ----------------------------------------------------------
1860
1861 l_cc_billed_end_date := WF_ENGINE.GetItemAttrDate(itemtype,
1862 itemkey,
1863 'CC_TRX_END_DATE');
1864
1865 Begin
1866 ----------------------------------------------------------
1867 l_debug_info := 'Is Credit Card Transactions exists ';
1868 ----------------------------------------------------------
1869 open trx_exists_cur( l_credit_card_program_id,
1870 l_inact_employee_id,
1871 l_itemkey,
1872 l_cc_billed_start_date,
1873 l_cc_billed_end_date);
1874 fetch trx_exists_cur into l_cc_trx_exists;
1875 close trx_exists_cur;
1876
1877 exception
1878 when others then
1879 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckCCTransactionExists',
1880 itemtype, itemkey, to_char(actid), l_debug_info);
1881 raise;
1882 end;
1883
1884 IF l_cc_trx_exists > 0 THEN
1885 resultout := 'COMPLETE:Y';
1886 ELSE
1887 resultout := 'COMPLETE:N';
1888 END IF;
1889
1890 ELSIF (funcmode = 'CANCEL') THEN
1891
1892 resultout := 'COMPLETE';
1893
1894 END IF;
1895
1896 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end CheckCCTransactionExists');
1897
1898 exception
1899 when others then
1900 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckCCTransactionExists',
1901 itemtype, itemkey, to_char(actid), l_debug_info);
1902 raise;
1903
1904 END CheckCCTransactionExists;
1905
1906 PROCEDURE CheckWfExistsEmpl(itemtype in varchar2,
1907 itemkey in varchar2,
1908 actid in number,
1909 funcmode in varchar2,
1910 resultout in out NOCOPY varchar2)
1911 IS
1912 -- Function Name: CheckWfExistsEmpl
1913 -- Author: Geetha Gurram
1914 -- Purpose: Check if there are any Workflow Process in active mode for the inactive employee
1915 --
1916 -- Input: itemtype
1917 -- itemkey
1918 -- actid
1919 -- funmode
1920 --
1921 -- Output: resultout
1922 --
1923 -- Assumptions:
1924 --
1925 -- Notes: Inactive Employee Workflow Processs
1926
1927 -- 3319945: Initialize l_wf_exists_status
1928 l_wf_exists_status NUMBER :=0;
1929 l_debug_info VARCHAR2(200);
1930 l_inact_employee_id NUMBER;
1931 l_credit_card_program_id NUMBER;
1932 l_itemkey VARCHAR2(30);
1933
1934 Cursor wf_exists_cur(p_credit_card_program_id in number,
1935 p_inact_employee_id in number,
1936 p_itemkey in varchar2)
1937 IS
1938 select 1
1939 from dual
1940 where exists (select cct.trx_id
1941 from ap_credit_card_trxns cct,
1942 ap_cards_all ac,
1943 --ap_card_programs_all cp,
1944 ap_expense_report_lines erl,
1945 ap_expense_report_headers erh
1946 where ac.card_program_id = p_credit_card_program_id
1947 and cct.validate_code = 'Y'
1948 and cct.payment_flag <> 'Y'
1949 and cct.inactive_emp_wf_item_key is not null
1950 and cct.inactive_emp_wf_item_key <> p_itemkey
1951 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
1952 and ac.card_program_id = cct.card_program_id
1953 and ac.card_id = cct.card_id
1954 and cct.report_header_id = erh.report_header_id(+)
1955 --and cct.trx_id = erl.credit_card_trx_id(+)
1956 and erh.report_header_id = erl.report_header_id(+)
1957 --and decode(erh.expense_status_code, null, decode(erh.workflow_approved_flag, 'S','SAVED',null, decode(erh.source, null,'UNSUBMITTED')), erh.expense_status_code)
1958 and ac.employee_id = p_inact_employee_id
1959 group by cct.trx_id);
1960
1961 BEGIN
1962
1963 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start CheckWfExistsEmpl');
1964
1965 -- 3319945: Initialize l_itemkey
1966 l_itemkey := itemkey;
1967
1968 IF (funcmode = 'RUN') THEN
1969
1970
1971 -------------------------------------------------------------------
1972 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
1973 -------------------------------------------------------------------
1974
1975 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1976 itemkey,
1977 'INACT_EMPLOYEE_ID');
1978
1979 -------------------------------------------------------------------
1980 l_debug_info := 'Retrieve Credit Card Program ID Item Attributes';
1981 -------------------------------------------------------------------
1982
1983 l_credit_card_program_id := WF_ENGINE.GetItemAttrNumber(itemtype,
1984 itemkey,
1985 'CARD_PROG_ID');
1986
1987
1988 Begin
1989
1990 ----------------------------------------------------------
1991 l_debug_info := 'Is Workflow exists ';
1992 ----------------------------------------------------------
1993
1994 open wf_exists_cur(l_credit_card_program_id,
1995 l_inact_employee_id,
1996 l_itemkey);
1997 fetch wf_exists_cur into l_wf_exists_status;
1998 close wf_exists_cur;
1999
2000
2001 exception
2002 when others then
2003 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckWfExistsEmpl',
2004 itemtype, itemkey, to_char(actid), l_debug_info);
2005 raise;
2006 end;
2007
2008 IF l_wf_exists_status > 0 THEN
2009 resultout := 'COMPLETE:Y';
2010 ELSE
2011 resultout := 'COMPLETE:N';
2012 END IF;
2013
2014 ELSIF (funcmode = 'CANCEL') THEN
2015
2016 resultout := 'COMPLETE';
2017
2018 END IF;
2019
2020 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end CheckWfExistsEmpl');
2021
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckWfExistsEmpl',
2025 itemtype, itemkey, to_char(actid), l_debug_info);
2026 raise;
2027
2028 END CheckWfExistsEmpl;
2029 ----------------------------------------------------------------------
2030 PROCEDURE CallbackFunction( p_s_item_type IN VARCHAR2,
2031 p_s_item_key IN VARCHAR2,
2032 p_n_actid IN NUMBER,
2033 p_s_command IN VARCHAR2,
2034 p_s_result OUT NOCOPY VARCHAR2)
2035 IS
2036
2037 -- Function Name: CallbackFunction
2038 -- Author: Geetha Gurram
2039 -- Purpose: Sets the session context(userid, org_id etc.,) when workflow is started or restarted
2040 --
2041 -- Input: p_s_item_type
2042 -- p_s_item_key
2043 -- actid
2044 -- funmode
2045 --
2046 -- Output: resultout
2047 --
2048 -- Assumptions:
2049 --
2050 -- Notes: Inactive Employee Workflow Processs
2051
2052 l_n_org_id Number;
2053 l_n_user_id Number;
2054 l_n_resp_id Number;
2055 l_n_resp_appl_id Number;
2056
2057 BEGIN
2058
2059 AP_WEB_UTILITIES_PKG.logProcedure('P_WEB_INACTIVE_EMP_WF_PKG', 'start CallbackFunction');
2060
2061 begin
2062
2063 l_n_org_id := WF_ENGINE.GetItemAttrNumber(p_s_item_type,
2064 p_s_item_key,
2065 'ORG_ID');
2066 exception
2067 when others then
2068 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
2069 -- ORG_ID item attribute doesn't exist, need to add it
2070 wf_engine.AddItemAttr(p_s_item_type, p_s_item_key, 'ORG_ID');
2071 -- get the org_id from header for old reports
2072 IF (AP_WEB_DB_EXPRPT_PKG.GetOrgIdByReportHeaderId(
2073 to_number(p_s_item_key),
2074 l_n_org_id) <> TRUE ) THEN
2075 l_n_org_id := NULL;
2076 END IF;
2077 WF_ENGINE.SetItemAttrNumber(p_s_item_type,
2078 p_s_item_key,
2079 'ORG_ID',
2080 l_n_org_id);
2081 else
2082 raise;
2083 end if;
2084
2085 end;
2086
2087
2088
2089 IF (p_s_command = 'SET_CTX') THEN
2090
2091 begin
2092 l_n_user_id := WF_ENGINE.GetItemAttrNumber(p_s_item_type,
2093 p_s_item_key,
2094 'USER_ID');
2095 l_n_resp_id := WF_ENGINE.GetItemAttrNumber(p_s_item_type,
2096 p_s_item_key,
2097 'RESPONSIBILITY_ID');
2098 l_n_resp_appl_id := WF_ENGINE.GetItemAttrNumber(p_s_item_type,
2099 p_s_item_key,
2100 'APPLICATION_ID');
2101 -- Set the context
2102 FND_GLOBAL.APPS_INITIALIZE( USER_ID => l_n_user_id,
2103 RESP_ID => l_n_resp_id,
2104 RESP_APPL_ID => l_n_resp_appl_id
2105 );
2106 exception
2107 when others then
2108 if (wf_core.error_name = 'WFENG_ITEM_ATTR') then
2109 null;
2110 else
2111 raise;
2112 end if;
2113 end;
2114
2115 -- Set Org context
2116 -- Needs to be after FND_GLOBAL.APPS_INITIALIZE because
2117 -- user_id, resp_id, and appl_id may be null because
2118 -- the attributes don't exist or because they are not set
2119 if (l_n_org_id is not null) then
2120 mo_global.set_policy_context(p_access_mode => 'S',
2121 p_org_id => l_n_org_id);
2122 end if;
2123
2124 ELSIF (p_s_command = 'TEST_CTX') THEN
2125 IF ((nvl(mo_global.get_access_mode, 'NULL') <> 'S') OR
2126 (nvl(mo_global.get_current_org_id, -99) <> nvl(l_n_org_id, -99)) ) THEN
2127 p_s_result := 'FALSE';
2128 ELSE
2129 p_s_result := 'TRUE';
2130 END IF;
2131
2132 END IF;
2133
2134 AP_WEB_UTILITIES_PKG.logProcedure('P_WEB_INACTIVE_EMP_WF_PKG', 'end CallbackFunction');
2135
2136 END CallbackFunction;
2137
2138 PROCEDURE IsNotifTransferred( p_item_type IN VARCHAR2,
2139 p_item_key IN VARCHAR2,
2140 p_actid IN NUMBER,
2141 p_funmode IN VARCHAR2,
2142 p_result OUT NOCOPY VARCHAR2)
2143 IS
2144 -- Function Name: IsNotifTransferred
2145 -- Author: Geetha Gurram
2146 -- Purpose: Check if Notification transfered from one preparer to another
2147 --
2148 -- Input: p_itemtype
2149 -- p_itemkey
2150 -- p_actid
2151 -- p_funmode
2152 --
2153 -- Output: p_result
2154 --
2155 -- Assumptions:
2156 --
2157 -- Notes: Inactive Employee Workflow Processs
2158
2159 l_debug_info VARCHAR2(1000);
2160 l_notificationID NUMBER;
2161 l_TransferNotificationID NUMBER;
2162 l_TransferToID NUMBER;
2163 l_Transferee VARCHAR2(80);
2164 l_TransferToName VARCHAR2(30);
2165 l_preparer_id NUMBER;
2166 l_preparer_name VARCHAR2(30);
2167 l_preparer_display_name VARCHAR2(80);
2168
2169 CURSOR c_person_id IS
2170 SELECT orig_system_id
2171 FROM wf_roles
2172 WHERE orig_system = 'PER'
2173 AND name = l_TransferToName;
2174
2175 BEGIN
2176
2177 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start IsNotifTransferred');
2178
2179 if (p_funmode IN ('TRANSFER', 'FORWARD')) then
2180 -----------------------------------------
2181 l_debug_info := 'Get the Notification ID';
2182 -----------------------------------------
2183 l_notificationID := wf_engine.context_nid;
2184
2185 -----------------------------------------
2186 l_debug_info := 'Get information on the transfer to';
2187 -----------------------------------------
2188 -- wf_engine.context_text = new responder
2189 l_Transferee := wf_engine.context_text;
2190
2191 -----------------------------------------
2192 l_debug_info := 'check for transferee received through email/web';
2193 -----------------------------------------
2194 IF (substrb(l_Transferee,1,6) = 'email:') THEN
2195 l_TransferToName := substrb(l_Transferee,7);
2196 ELSE
2197 -- response received through web or form
2198 l_TransferToName := l_Transferee;
2199 END IF;
2200
2201 -----------------------------------------
2202 l_debug_info := 'Get the transferee id';
2203 -----------------------------------------
2204 OPEN c_person_id;
2205 FETCH c_person_id into l_TransferToID;
2206 IF c_person_id%NOTFOUND THEN
2207 p_result := wf_engine.eng_completed||':'||wf_engine.eng_null;
2208 Wf_Core.Raise(wf_core.translate('NO_ROLE_FOUND'));
2209 RETURN;
2210 ELSE
2211 IF l_TransferToID IS NULL THEN
2212 p_result := wf_engine.eng_completed||':'||wf_engine.eng_null;
2213 Wf_Core.Raise(wf_core.translate('PERSON_ID_NULL'));
2214 RETURN;
2215 END IF;
2216 END IF;
2217 CLOSE c_person_id;
2218
2219 ---------------------------------------------------------------------
2220 l_debug_info := 'set the transferring Preparer info to the Preparer';
2221 ---------------------------------------------------------------------
2222 WF_ENGINE.SetItemAttrText(p_item_type,
2223 p_item_key,
2224 'PREPARER_DISPLAY_NAME',
2225 WF_ENGINE.GetItemAttrText(p_item_type,
2226 p_item_key,
2227 'PREPARER_DISPLAY_NAME'));
2228
2229 ---------------------------------------------------------------------------
2230 l_debug_info := 'set the transferring Preparer Name to the Forwarded Info';
2231 ---------------------------------------------------------------------------
2232 WF_ENGINE.SetItemAttrText(p_item_type,
2233 p_item_key,
2234 'FORWARD_FROM_NAME',
2235 WF_ENGINE.GetItemAttrText(p_item_type,
2236 p_item_key,
2237 'PREPARER_NAME'));
2238 -----------------------------------------------------------------------------------
2239 l_debug_info := 'set the transferring Preparer Display Name to the Forwarded Info';
2240 -----------------------------------------------------------------------------------
2241
2242 WF_ENGINE.SetItemAttrText(p_item_type,
2243 p_item_key,
2244 'FORWARD_FROM_DISPLAY_NAME',
2245 WF_ENGINE.GetItemAttrText(p_item_type,
2246 p_item_key,
2247 'PREPARER_DISPLAY_NAME'));
2248
2249 ----------------------------------------------------------------------
2250 l_debug_info := 'set the current Preparer info to the Transferee';
2251 ---------------------------------------------------------------------
2252 SetPersonAs(l_TransferToID,
2253 p_item_type,
2254 p_item_key,
2255 'PREPARER');
2256
2257 -----------------------------------------
2258 l_debug_info := 'set the current Preparer info in the Notification';
2259 -----------------------------------------
2260 WF_NOTIFICATION.SetAttrText(l_notificationID,
2261 'PREPARER_DISPLAY_NAME',
2262 WF_ENGINE.GetItemAttrText(p_item_type,
2263 p_item_key,
2264 'PREPARER_DISPLAY_NAME'));
2265
2266 end if;
2267
2268
2269 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end IsNotifTransferred');
2270
2271 EXCEPTION
2272 WHEN OTHERS THEN
2273 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'IsNotifTransferred',
2274 p_item_type, p_item_key, to_char(0), l_debug_info);
2275 RAISE;
2276
2277 END IsNotifTransferred;
2278
2279
2280 PROCEDURE SetPersonAs(p_preparer_id IN NUMBER,
2281 p_item_type IN VARCHAR2,
2282 p_item_key IN VARCHAR2,
2283 p_preparer_target IN VARCHAR2)
2284 IS
2285
2286 -- Function Name: SetPersonAs
2287 -- Author: Geetha Gurram
2288 -- Purpose: Set Preparer information Attributes
2289 --
2290 -- Input: p_preparer_id
2291 -- p_itemtype
2292 -- p_itemkey
2293 -- p_preparer_targe
2294 --
2295 -- Assumptions:
2296 --
2297 -- Notes: Inactive Employee Workflow Processs
2298
2299 l_preparer_name VARCHAR2(30);
2300 l_preparer_display_name VARCHAR2(150);
2301 l_debug_info VARCHAR2(200);
2302 l_preparer_org_id NUMBER;
2303
2304 BEGIN
2305
2306 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start SetPersonAs');
2307
2308 ------------------------------------------------------------
2309 l_debug_info := 'Retrieve Preparer_Name Info for Preparer_Id';
2310 ------------------------------------------------------------
2311 WF_DIRECTORY.GetUserName('PER',
2312 p_preparer_id,
2313 l_preparer_name,
2314 l_preparer_display_name);
2315
2316 IF (p_preparer_target = 'PREPARER') THEN
2317
2318 WF_ENGINE.SetItemAttrNumber(p_item_type,
2319 p_item_key,
2320 'PREPARER_EMPL_ID',
2321 p_preparer_id);
2322
2323 --------------------------------------------------------
2324 l_debug_info := 'Set Preparer_Name Info Item Attribute';
2325 --------------------------------------------------------
2326 WF_ENGINE.SetItemAttrText(p_item_type,
2327 p_item_key,
2328 'PREPARER_NAME',
2329 l_preparer_name);
2330
2331 ---------------------------------------------------------------
2332 l_debug_info := 'Set Preparer_Display_Name Info Item Attribute';
2333 ---------------------------------------------------------------
2334 WF_ENGINE.SetItemAttrText(p_item_type,
2335 p_item_key,
2336 'PREPARER_DISPLAY_NAME',
2337 l_preparer_display_name);
2338
2339 --------------------------------------------------------
2340 l_debug_info := 'Get Preperer Org Info Item Attribute';
2341 --------------------------------------------------------
2342 if (AP_WEB_DB_HR_INT_PKG.GetEmpOrgId(p_preparer_id,l_preparer_org_id) = TRUE ) then
2343
2344 --------------------------------------------------------
2345 l_debug_info := 'Set Preperer Org Info Item Attribute';
2346 --------------------------------------------------------
2347 WF_ENGINE.SetItemAttrNumber(p_item_type,
2348 p_item_key,
2349 'PREPARER_ORG_ID',
2350 l_preparer_org_id);
2351 end if;
2352
2353 ELSE
2354
2355 --------------------------------------------------------
2356 l_debug_info := 'Set Supervisor_ID Info Item Attribute';
2357 --------------------------------------------------------
2358 WF_ENGINE.SetItemAttrNumber(p_item_type,
2359 p_item_key,
2360 'PREPARER_EMPL_ID',
2361 p_preparer_id);
2362
2363 --------------------------------------------------------
2364 l_debug_info := 'Set Approver_Name Info Item Attribute';
2365 --------------------------------------------------------
2366 WF_ENGINE.SetItemAttrText(p_item_type,
2367 p_item_key,
2368 'PREPARER_NAME',
2369 l_preparer_name);
2370
2371 ----------------------------------------------------------------
2372 l_debug_info := 'Set Approver_Display_Name Info Item Attribute';
2373 ----------------------------------------------------------------
2374 WF_ENGINE.SetItemAttrText(p_item_type,
2375 p_item_key,
2376 'PREPARER_DISPLAY_NAME',
2377 l_preparer_display_name);
2378
2379
2380
2381 END IF;
2382
2383 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end SetPersonAs');
2384
2385 EXCEPTION
2386 WHEN OTHERS THEN
2387 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'SetPersonAs',
2388 p_item_type, p_item_key, null, l_debug_info);
2389 raise;
2390 END SetPersonAs;
2391
2392 PROCEDURE CheckAPApproved(itemtype in varchar2,
2393 itemkey in varchar2,
2394 actid in number,
2395 funcmode in varchar2,
2396 resultout in out NOCOPY varchar2)
2397 IS
2398
2399 -- Function Name: CheckAPApproved
2400 -- Author: Geetha Gurram
2401 -- Purpose: Check if the expense report submitted by the preparer is AP approved
2402 --
2403 -- Input: itemtype
2404 -- itemkey
2405 -- actid
2406 -- funcmode
2407 --
2408 -- Output: resultout
2409 --
2410 -- Assumptions:
2411 --
2412 -- Notes: Inactive Employee Workflow Processs
2413
2414 l_ap_approved_status VARCHAR2(50);
2415 l_debug_info VARCHAR2(2000);
2416 l_inact_employee_id NUMBER;
2417 l_credit_card_program_id NUMBER;
2418 l_ap_unapprove_exsists NUMBER := 0;
2419
2420 Cursor ap_unappr_exists_cur(p_credit_card_program_id in number,
2421 p_inact_employee_id in number,
2422 p_itemkey in varchar2)
2423 IS
2424 select erh.source
2425 from ap_expense_report_headers erh,
2426 ap_credit_card_trxns cct,
2427 ap_cards_all ac
2428 where ac.card_program_id = p_credit_card_program_id
2429 and cct.validate_code = 'Y'
2430 and cct.inactive_emp_wf_item_key is not null
2431 and cct.inactive_emp_wf_item_key = p_itemkey
2432 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
2433 and ac.card_program_id = cct.card_program_id
2434 and ac.card_id = cct.card_id
2435 and cct.report_header_id = erh.report_header_id
2436 and ac.employee_id = p_inact_employee_id;
2437
2438 BEGIN
2439
2440 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start CheckAPApproved');
2441
2442
2443 IF (funcmode = 'RUN') THEN
2444
2445
2446 -------------------------------------------------------------------
2447 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
2448 -------------------------------------------------------------------
2449
2450 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2451 itemkey,
2452 'INACT_EMPLOYEE_ID');
2453
2454 -------------------------------------------------------------------
2455 l_debug_info := 'Retrieve Credit Card Program ID Item Attributes';
2456 -------------------------------------------------------------------
2457
2458 l_credit_card_program_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2459 itemkey,
2460 'CARD_PROG_ID');
2461
2462
2463 ----------------------------------------------------------
2464 l_debug_info := 'Is Unapproved CC exists ';
2465 ----------------------------------------------------------
2466
2467 open ap_unappr_exists_cur(l_credit_card_program_id,
2468 l_inact_employee_id,
2469 itemkey);
2470 Loop
2471 fetch ap_unappr_exists_cur into l_ap_approved_status;
2472 exit when ap_unappr_exists_cur%notfound;
2473
2474 if l_ap_approved_status NOT IN ( 'CREDIT CARD', 'Both Pay') then
2475 l_ap_unapprove_exsists := l_ap_unapprove_exsists + 1;
2476 end if;
2477
2478 end loop;
2479 close ap_unappr_exists_cur;
2480
2481 IF l_ap_unapprove_exsists > 0 THEN
2482 resultout := 'COMPLETE:Y';
2483 ELSE
2484 resultout := 'COMPLETE:N';
2485 END IF;
2486
2487 ELSIF (funcmode = 'CANCEL') THEN
2488
2489 resultout := 'COMPLETE';
2490
2491 END IF;
2492
2493 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end CheckAPApproved');
2494
2495 EXCEPTION
2496 WHEN OTHERS THEN
2497 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'CheckAPApproved',
2498 itemtype, itemkey, to_char(actid), l_debug_info);
2499 raise;
2500
2501 END CheckAPApproved;
2502
2503 FUNCTION GetUserIdForEmp(
2504 p_emp_user_name IN VARCHAR2,
2505 p_user_id OUT NOCOPY NUMBER
2506 ) RETURN BOOLEAN
2507
2508 IS
2509 -- Function Name: GetUserIdForEmp
2510 -- Author: Geetha Gurram
2511 -- Purpose: Returns userid for the username
2512 --
2513 -- Input: p_emp_user_name
2514 --
2515 -- Output: p_user_id
2516 --
2517 -- Assumptions:
2518 --
2519 -- Notes: Inactive Employee Workflow Processs
2520
2521 l_debug_info VARCHAR2(200);
2522
2523 BEGIN
2524
2525 SELECT user_id
2526 INTO p_user_id
2527 FROM fnd_user
2528 WHERE user_name = p_emp_user_name;
2529
2530
2531 RETURN TRUE;
2532
2533 EXCEPTION
2534 WHEN NO_DATA_FOUND THEN
2535 Wf_Core.Context('AP_WEB_EXPENSE_WF', 'GetUserIdForEmp',
2536 to_char(0), to_char(0), to_char(0), l_debug_info || FND_MESSAGE.GET);
2537 RETURN FALSE;
2538
2539 WHEN OTHERS THEN
2540
2541 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'GetUserIdForEmp',
2542 to_char(0), to_char(0), to_char(0), l_debug_info || FND_MESSAGE.GET);
2543 APP_EXCEPTION.RAISE_EXCEPTION;
2544 RETURN FALSE;
2545
2546 END GetUserIdForEmp;
2547
2548 PROCEDURE ClearItemkeyCCTrx(itemtype in varchar2,
2549 itemkey in varchar2,
2550 actid in number,
2551 funcmode in varchar2,
2552 resultout in out NOCOPY varchar2)
2553 IS
2554
2555 -- Function Name: ClearItemkeyCCTrx
2556 -- Author: Geetha Gurram
2557 -- Purpose: Clear all the WF item key for all the Credit Card Transactions which selected when the workflow
2558 -- got initiated
2559 --
2560 -- Input: itemtype
2561 -- itemkey
2562 -- actid
2563 -- funcmode
2564 --
2565 -- Output: resultout
2566 --
2567 -- Assumptions:
2568 --
2569 -- Notes: Inactive Employee Workflow Processs
2570
2571 l_debug_info VARCHAR2(2000);
2572 l_inact_employee_id NUMBER;
2573 l_credit_card_program_id NUMBER;
2574 l_trx_id NUMBER;
2575 l_expense_status_code VARCHAR2(50);
2576
2577 Cursor cc_trx_cur(p_credit_card_program_id in number,
2578 p_inact_employee_id in number,
2579 p_itemkey in varchar2)
2580 IS
2581 select cct.trx_id
2582 from ap_credit_card_trxns cct,
2583 ap_cards_all ac
2584 where ac.card_program_id = p_credit_card_program_id
2585 and cct.validate_code = 'Y'
2586 and cct.inactive_emp_wf_item_key is not null
2587 and cct.inactive_emp_wf_item_key = p_itemkey
2588 and nvl(cct.category, 'BUSINESS') not in ( 'DISPUTED', 'DEACTIVATED')
2589 and ac.card_program_id = cct.card_program_id
2590 and ac.card_id = cct.card_id
2591 and ac.employee_id = p_inact_employee_id;
2592
2593 BEGIN
2594
2595 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'start ClearItemkeyCCTrx');
2596
2597
2598 IF (funcmode = 'RUN') THEN
2599
2600
2601 -------------------------------------------------------------------
2602 l_debug_info := 'Retrieve Inactive Employee ID Item Attributes';
2603 -------------------------------------------------------------------
2604
2605 l_inact_employee_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2606 itemkey,
2607 'INACT_EMPLOYEE_ID');
2608
2609 -------------------------------------------------------------------
2610 l_debug_info := 'Retrieve Credit Card Program ID Item Attributes';
2611 -------------------------------------------------------------------
2612
2613 l_credit_card_program_id := WF_ENGINE.GetItemAttrNumber(itemtype,
2614 itemkey,
2615 'CARD_PROG_ID');
2616
2617
2618 ----------------------------------------------------------
2619 l_debug_info := 'Is Unapproved CC exists ';
2620 ----------------------------------------------------------
2621
2622 open cc_trx_cur(l_credit_card_program_id,
2623 l_inact_employee_id,
2624 itemkey);
2625 Loop
2626 fetch cc_trx_cur into l_trx_id;
2627 exit when cc_trx_cur%notfound;
2628
2629 -- if l_expense_status_code in( 'WITHDRAWN', 'RETURNED', 'REJECTED', 'ERROR') then
2630 update ap_credit_card_trxns
2631 set inactive_emp_wf_item_key = NULL
2632 where trx_id = l_trx_id;
2633 -- end if;
2634
2635 end loop;
2636
2637 commit;
2638 close cc_trx_cur;
2639
2640 resultout := 'COMPLETE:Y';
2641
2642
2643 ELSIF (funcmode = 'CANCEL') THEN
2644
2645 resultout := 'COMPLETE';
2646
2647 END IF;
2648
2649 AP_WEB_UTILITIES_PKG.logProcedure('AP_WEB_INACTIVE_EMP_WF_PKG', 'end ClearItemkeyCCTrx');
2650
2651 EXCEPTION
2652 WHEN OTHERS THEN
2653 Wf_Core.Context('AP_WEB_INACTIVE_EMP_WF_PKG', 'ClearItemkeyCCTrx',
2654 itemtype, itemkey, to_char(actid), l_debug_info);
2655 raise;
2656
2657 END ClearItemkeyCCTrx;
2658
2659 END AP_WEB_INACTIVE_EMP_WF_PKG;