[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_CC_VALIDATION_WF_PKG
Source
1 PACKAGE BODY AP_WEB_CC_VALIDATION_WF_PKG as
2 /* $Header: apwfvalb.pls 120.9.12020000.2 2012/07/05 14:17:08 rveliche ship $ */
3
4 --
5 -- Raises the Workflow business event
6 -- oracle.apps.ap.oie.creditcard.transaction.error
7 function raise_validation_event(p_request_id in number default null,
8 p_card_program_id in number default null,
9 p_start_date in date default null,
10 p_end_date in date default null)
11 return number is
12 l_parameter_list wf_parameter_list_t;
13 l_event_key number;
14 i number := 0;
15 --Bug 6160290: Add cursor to handle scenario when p_card_program_id is null
16 cursor fetch_card_program_id is
17 SELECT distinct card_program_id FROM ap_credit_card_trxns_all
18 WHERE (p_start_date IS NULL OR transaction_date IS NULL OR transaction_date >= p_start_date)
19 AND (p_end_date IS NULL OR transaction_date IS NULL OR transaction_date <= p_end_date)
20 AND validate_code <> 'Y'
21 AND validate_request_id = p_request_id; --Bug#14084710 Show notification only for present request id
22 begin
23 if (p_card_program_id is not null) then
24 l_parameter_list := wf_parameter_list_t(
25 wf_parameter_t('REQUEST_ID', to_char(p_request_id)),
26 wf_parameter_t('CARD_PROGRAM_ID', to_char(p_card_program_id)),
27 wf_parameter_t('START_DATE', to_char(p_start_date, 'YYYY/MM/DD')),
28 wf_parameter_t('END_DATE', to_char(p_end_date, 'YYYY/MM/DD'))
29 );
30
31 -- select ap_oie_workflow_s.nextval into l_event_key
32 select ap_ccard_notification_id_s.nextval into l_event_key
33 from dual;
34 wf_event.raise(p_event_name => 'oracle.apps.ap.oie.creditcard.transaction.error',
35 p_event_key => to_char(l_event_key),
36 p_parameters => l_parameter_list);
37
38 if ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
39 fnd_log.string(fnd_log.level_event,
40 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.raise_validation_event',
41 'Raised validation event with key = '||to_char(l_event_key));
42 end if;
43
44 else
45
46 for l_fetch_card_program_id in fetch_card_program_id loop
47 l_parameter_list := wf_parameter_list_t(
48 wf_parameter_t('REQUEST_ID', to_char(p_request_id)),
49 wf_parameter_t('CARD_PROGRAM_ID', to_char(l_fetch_card_program_id.card_program_id)),
50 wf_parameter_t('START_DATE', to_char(p_start_date, 'YYYY/MM/DD')),
51 wf_parameter_t('END_DATE', to_char(p_end_date, 'YYYY/MM/DD')));
52
53 select ap_ccard_notification_id_s.nextval into l_event_key from dual;
54
55 wf_event.raise(p_event_name => 'oracle.apps.ap.oie.creditcard.transaction.error',
56 p_event_key => to_char(l_event_key),
57 p_parameters => l_parameter_list);
58
59 if ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
60 fnd_log.string(fnd_log.level_event,
61 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.raise_validation_event',
65
62 'Raised validation event with key = '||to_char(l_event_key));
63 end if;
64 end loop;
66 end if;
67 return l_event_key;
68
69 end raise_validation_event;
70
71 /*
72 --
73 -- Counts the number of invalid credit card transactions
74 -- for the give Request ID, Card Program ID, and start/end dates
75 procedure count_invalid(itemtype in varchar2,
76 itemkey in varchar2,
77 actid in number,
78 funcmode in varchar2,
79 resultout out nocopy varchar2) is
80 l_request_id number;
81 l_card_program_id number;
82 l_start_date date;
83 l_end_date date;
84 l_validate_code varchar2(30);
85 l_total_count number;
86 l_count number;
87
88 stmt varchar2(2000);
89
90 type gen_cursor is ref cursor;
91 c gen_cursor;
92 begin
93 if ( funcmode = 'RUN' ) then
94 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true);
95 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',true);
96 l_start_date := wf_engine.getitemattrdate(itemtype,itemkey,'START_DATE',true);
97 l_end_date := wf_engine.getitemattrdate(itemtype,itemkey,'END_DATE',true);
98
99 stmt := 'select validate_code, count(*) '||
100 'from ap_credit_card_trxns_all '||
101 'where validate_code not in (''Y'', ''N'', ''UNTESTED'') ';
102 if l_request_id is null then
103 stmt := stmt || 'and :reqId is null ';
104 else
105 stmt := stmt || 'and request_id = :reqId ';
106 end if;
107 if l_card_program_id is null then
108 stmt := stmt || 'and :cardProgramId is null ';
109 else
110 stmt := stmt || 'and card_program_id = :card_program_id ';
111 end if;
112 if l_start_date is null then
113 stmt := stmt || 'and :startDate is null ';
114 else
115 stmt := stmt || 'and transaction_date >= :startDate ';
116 end if;
117 if l_end_date is null then
118 stmt := stmt || 'and :endDate is null ';
119 else
120 stmt := stmt || 'and transaction_date >= :endDate ';
121 end if;
122 stmt := stmt || 'group by validate_code ';
123
124 begin
125 l_total_count := 0;
126 open c for stmt using l_request_id, l_card_program_id, l_start_date, l_end_date;
127 loop
128 fetch c into l_validate_code, l_count;
129 exit when c%notfound;
130
131 wf_engine.setitemattrnumber(itemtype,itemkey,l_validate_code,l_count);
132 l_total_count := l_total_count + l_count;
133 end loop;
134 close c;
135
136 wf_engine.setitemattrnumber(itemtype,itemkey,'INVALID_ALL',l_total_count);
137 exception
138 when others then
139 if c%isopen then
140 close c;
141 end if;
142 raise;
143 end;
144
145
146 resultout := 'COMPLETE:';
147 return;
148 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
149 resultout := 'COMPLETE';
150 return;
151 else
152 resultout := ' ';
153 return;
154 end if;
155 exception
156 when others then
157 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
158 fnd_log.string(fnd_log.level_unexpected,
159 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.count_invalid',
160 sqlerrm);
161 end if;
162 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'COUNT_INVALID',
163 itemtype, itemkey, to_char(actid), funcmode);
164 raise;
165 end count_invalid;
166 */
167
168 --
169 -- Returns the URL for the Credit Card Transactions page
170 --
171 --
172 procedure get_search_page_url(itemtype in varchar2,
173 itemkey in varchar2,
174 actid in number,
175 funcmode in varchar2,
176 resultout out nocopy varchar2) is
177
178 l_request_id number;
179 l_card_program_id number;
180 l_start_date date;
181 l_end_date date;
182
183 url varchar2(2000);
184 begin
185 if ( funcmode = 'RUN' ) then
186 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true);
187 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',true);
188 l_start_date := wf_engine.getitemattrdate(itemtype,itemkey,'START_DATE',true);
189 l_end_date := wf_engine.getitemattrdate(itemtype,itemkey,'END_DATE',true);
190
191 url := 'JSP:/OA_HTML/OA.jsp?OAFunc=OIE_CCTRX_SEARCH_FN'||
192 '&'||'restrict=Y'||
193 '&'||'pRequestId='||to_char(l_request_id)||
194 '&'||'pCardProgramId='||to_char(l_card_program_id)||
195 '&'||'pStartDate='||to_char(l_start_date, 'YYYY/MM/DD')||
196 '&'||'pEndDate='||to_char(l_end_date, 'YYYY/MM/DD')||
197 '&'||'pValidateCode=INVALID_ALL'||
198 '&'||'NtfId=-&#NID-';
199
200 wf_engine.setitemattrtext(itemtype,itemkey,'SEARCH_PAGE_URL',url);
201
202 resultout := 'COMPLETE:';
203 return;
204 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
205 resultout := 'COMPLETE';
206 return;
207 else
208 resultout := ' ';
209 return;
210 end if;
211 exception
212 when others then
213 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
214 fnd_log.string(fnd_log.level_unexpected,
218 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_SEARCH_PAGE_URL',
215 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.get_search_page_url',
216 sqlerrm);
217 end if;
219 itemtype, itemkey, to_char(actid), funcmode);
220 raise;
221 end get_search_page_url;
222
223 --
224 -- Raises the Workflow business event
225 -- oracle.apps.ap.oie.creditcard.account.create
226 function raise_new_cc_event(p_request_id in number default null,
227 p_card_program_id in number default null,
228 p_start_date in date default null,
229 p_end_date in date default null)
230 return number is
231 l_parameter_list wf_parameter_list_t;
232 l_event_key number;
233 i number := 0;
234
235 cursor fetch_card_program_id is
236 SELECT distinct card_program_id FROM ap_cards_all WHERE request_id = p_request_id;
237
238 begin
239 if (p_card_program_id is not null and p_card_program_id <> 0) then
240 l_parameter_list := wf_parameter_list_t(
241 wf_parameter_t('REQUEST_ID', to_char(p_request_id)),
242 wf_parameter_t('CARD_PROGRAM_ID', to_char(p_card_program_id)),
243 wf_parameter_t('START_DATE', to_char(p_start_date, 'YYYY/MM/DD')),
244 wf_parameter_t('END_DATE', to_char(p_end_date, 'YYYY/MM/DD'))
245 );
246
247 select ap_ccard_notification_id_s.nextval into l_event_key from dual;
248
249 wf_event.raise(p_event_name => 'oracle.apps.ap.oie.creditcard.account.create',
250 p_event_key => to_char(l_event_key),
251 p_parameters => l_parameter_list);
252 if ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
253 fnd_log.string(fnd_log.level_event,
254 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.raise_new_cc_event',
255 'Raised new card event with key = '||to_char(l_event_key));
256 end if;
257 else
258 for l_fetch_card_program_id in fetch_card_program_id loop
259 l_parameter_list := wf_parameter_list_t(
260 wf_parameter_t('REQUEST_ID', to_char(p_request_id)),
261 wf_parameter_t('CARD_PROGRAM_ID', to_char(l_fetch_card_program_id.card_program_id)),
262 wf_parameter_t('START_DATE', to_char(p_start_date, 'YYYY/MM/DD')),
263 wf_parameter_t('END_DATE', to_char(p_end_date, 'YYYY/MM/DD'))
264 );
265
266 select ap_ccard_notification_id_s.nextval into l_event_key from dual;
267
268 wf_event.raise(p_event_name => 'oracle.apps.ap.oie.creditcard.account.create',
269 p_event_key => to_char(l_event_key),
270 p_parameters => l_parameter_list);
271 if ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
272 fnd_log.string(fnd_log.level_event,
273 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.raise_new_cc_event',
274 'Raised new card event with key = '||to_char(l_event_key));
275 end if;
276 end loop;
277 end if;
278
279 return l_event_key;
280 end raise_new_cc_event;
281
282
283 --
284 -- Find employee matches
285 procedure card_employee_match(itemtype in varchar2,
286 itemkey in varchar2,
287 actid in number,
288 funcmode in varchar2,
289 resultout out nocopy varchar2) is
290 l_request_id number;
291 l_card_program_id number;
292 l_match_rule varchar2(30);
293 l_stmt varchar2(200);
294
295 cursor ccard is
296 select det.card_id, card.card_program_id -- , det.name, det.employee_number, det.national_identifier
297 from ap_card_details det, ap_cards_all card
298 where det.card_id = card.card_id
299 and card.request_id = l_request_id;
300 begin
301 if ( funcmode = 'RUN' ) then
302 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID', true);
303 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID', true);
304
305 for crec in ccard LOOP
306 select card_emp_matching_rule into l_match_rule
307 from ap_card_programs_all
308 where card_program_id = crec.card_program_id;
309
310 if l_match_rule is not null then
311 l_stmt := 'begin '||
312 l_match_rule||'.get_employee_matches(:cardId); '||
313 'end;';
314 execute immediate l_stmt using crec.card_id;
315 end if;
316 end loop;
317
318
319 resultout := 'COMPLETE:';
320 return;
321 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
322 resultout := 'COMPLETE';
323 return;
324 else
325 resultout := ' ';
326 return;
327 end if;
328 exception
329 when others then
330 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
331 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
332 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.CARD_EMPLOYEE_MATCH',
333 sqlerrm);
334 end if;
335 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'CARD_EMPLOYEE_MATCH',
336 itemtype, itemkey, to_char(actid), funcmode);
337 raise;
338 end card_employee_match;
339
340
341 --
342 -- Assigns employees to credit cards if only one employee
343 -- candidate was found - thereby activating the credit card.
344 procedure assign_emp_if_unique(itemtype in varchar2,
345 itemkey in varchar2,
346 actid in number,
347 funcmode in varchar2,
348 resultout out nocopy varchar2) is
352 cursor cemp is
349 l_request_id number;
350 l_card_program_id number;
351
353 select c.card_id, max(emp.employee_id) as employee_id
354 from ap_cards_all ca, ap_card_details c, ap_card_emp_candidates emp
355 where c.card_id = emp.card_id
356 and ca.card_id = c.card_id
357 and ca.request_id = l_request_id
358 group by c.card_id
359 having count(*) = 1;
360
361 begin
362 if ( funcmode = 'RUN' ) then
363 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID', false);
364 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID', true);
365
366 for crec in cemp loop
367 ap_web_cc_validations_pkg.assign_employee(crec.card_id, crec.employee_id);
368 end loop;
369 resultout := 'COMPLETE:';
370 return;
371 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
372 resultout := 'COMPLETE';
373 return;
374 else
375 resultout := ' ';
376 return;
377 end if;
378 exception
379 when others then
380 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
381 fnd_log.string(fnd_log.level_unexpected,
382 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.ASSIGN_EMP_IF_UNIQUE',
383 sqlerrm);
384 end if;
385 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'ASSIGN_EMP_IF_UNIQUE',
386 itemtype, itemkey, to_char(actid), funcmode);
387 raise;
388 end assign_emp_if_unique;
389
390 --
391 -- Checks to see if new credit cards were created by
392 -- a given request id
393 procedure new_cards_exist(itemtype in varchar2,
394 itemkey in varchar2,
395 actid in number,
396 funcmode in varchar2,
397 resultout out nocopy varchar2) is
398 l_request_id number;
399 l_exist number;
400 begin
401 if ( funcmode = 'RUN' ) then
402 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
403 select count(*) into l_exist from dual
404 where exists (select 1 from ap_cards_all where request_id = l_request_id);
405
406 if l_exist = 0 then
407 resultout := 'COMPLETE:F';
408 else
409 resultout := 'COMPLETE:T';
410 end if;
411 return;
412 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
413 resultout := 'COMPLETE';
414 return;
415 else
416 resultout := ' ';
417 return;
418 end if;
419 exception
420 when others then
421 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
422 fnd_log.string(fnd_log.level_unexpected,
423 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.NEW_CARDS_EXIST',
424 sqlerrm);
425 end if;
426 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'NEW_CARDS_EXIST',
427 itemtype, itemkey, to_char(actid), funcmode);
428 raise;
429 end new_cards_exist;
430
431
432 --
433 -- Checks to see if inactive credit cards were created by
434 -- a given request id
435 procedure inactive_cards_exist(itemtype in varchar2,
436 itemkey in varchar2,
437 actid in number,
438 funcmode in varchar2,
439 resultout out nocopy varchar2) is
440 l_request_id number;
441 l_exist number;
442 begin
443 if ( funcmode = 'RUN' ) then
444 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
445 select count(*) into l_exist from dual
446 where exists (select 1 from ap_cards_all where request_id = l_request_id and employee_id is null);
447
448 if l_exist = 0 then
449 resultout := 'COMPLETE:F';
450 else
451 resultout := 'COMPLETE:T';
452 end if;
453 return;
454 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
455 resultout := 'COMPLETE';
456 return;
457 else
458 resultout := ' ';
459 return;
460 end if;
461 exception
462 when others then
463 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
464 fnd_log.string(fnd_log.level_unexpected,
465 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.INACTIVE_CARDS_EXIST',
466 sqlerrm);
467 end if;
468 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'INACTIVE_CARDS_EXIST',
469 itemtype, itemkey, to_char(actid), funcmode);
470 raise;
471 end inactive_cards_exist;
472
473
474 --
475 -- Checks to see if invalid credit card trx were created by
476 -- a given request id
477 procedure invalid_cctrx_exist(itemtype in varchar2,
478 itemkey in varchar2,
479 actid in number,
480 funcmode in varchar2,
481 resultout out nocopy varchar2) is
482 l_request_id number;
483 l_card_program_id NUMBER;
484 l_start_date DATE;
485 l_end_date DATE;
486 l_exist number;
487 l_exist1 number;
488 l_exist2 number;
489 begin
490 WF_ENGINE.SetItemAttrText(itemtype,itemkey,'OIE_INVALID_TABLE','JSP:/OA_HTML/OA.jsp?akRegionCode=InvalidCCardRN'||'&'||'akRegionApplicationId=200'||'&'||'itemKey='||itemkey||'&'||'requestId=-&#HDR_REQUEST_ID-'); -- Bug 6829024(sodash)
491 if ( funcmode = 'RUN' ) then
492 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
493 l_card_program_id := wf_engine.getitemattrnumber(itemtype, itemkey,'CARD_PROGRAM_ID',FALSE);
497 where exists (select 1 from ap_credit_card_trxns_all where request_id = l_request_id and validate_code <> 'Y');
494 l_start_date := wf_engine.getitemattrdate(itemtype, itemkey,'START_DATE',FALSE);
495 l_end_date := wf_engine.getitemattrdate(itemtype, itemkey,'END_DATE',FALSE);
496 select count(*) into l_exist1 from dual
498 SELECT COUNT(*) INTO l_exist2 FROM dual
499 WHERE exists (SELECT 1 FROM ap_credit_card_trxns_all
500 WHERE (l_card_program_id IS NULL OR card_program_id = l_card_program_id) -- Bug 6829024(sodash)
501 AND (l_start_date IS NULL OR transaction_date IS NULL OR transaction_date >= l_start_date)
502 AND (l_end_date IS NULL OR transaction_date IS NULL OR transaction_date <= l_end_date)
503 AND validate_code <> 'Y'
504 );
505 l_exist := l_exist1 + l_exist2;
506
507 if l_exist = 0 then
508 resultout := 'COMPLETE:F';
509 else
510 resultout := 'COMPLETE:T';
511 end if;
512 return;
513 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
514 resultout := 'COMPLETE';
515 return;
516 else
517 resultout := ' ';
518 return;
519 end if;
520 exception
521 when others then
522 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
523 fnd_log.string(fnd_log.level_unexpected,
524 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.INVALID_CCTRX_EXIST',
525 sqlerrm);
526 end if;
527 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'INVALID_CCTRX_EXIST',
528 itemtype, itemkey, to_char(actid), funcmode);
529 raise;
530 end invalid_cctrx_exist;
531
532 --
533 -- Counts the number of new credit cards that were created by
534 -- a given Request ID
535 procedure count_new_cards(itemtype in varchar2,
536 itemkey in varchar2,
537 actid in number,
538 funcmode in varchar2,
539 resultout out nocopy varchar2) is
540 l_request_id number;
541 l_count number;
542 begin
543 if ( funcmode = 'RUN' ) then
544 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
545 select count(*) into l_count
546 from ap_cards_all
547 where request_id = l_request_id;
548
549 wf_engine.setitemattrnumber(itemtype,itemkey,'NEW_CARD_COUNT',l_count);
550
551 resultout := 'COMPLETE:';
552 return;
553 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
554 resultout := 'COMPLETE';
555 return;
556 else
557 resultout := ' ';
558 return;
559 end if;
560 exception
561 when others then
562 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
563 fnd_log.string(fnd_log.level_unexpected,
564 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.COUNT_NEW_CARDS',
565 sqlerrm);
566 end if;
567 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'COUNT_NEW_CARDS',
568 itemtype, itemkey, to_char(actid), funcmode);
569 raise;
570 end count_new_cards;
571
572
573 --
574 -- Returns the URL to the New Card Search page.
575 procedure get_new_card_page_url(itemtype in varchar2,
576 itemkey in varchar2,
577 actid in number,
578 funcmode in varchar2,
579 resultout out nocopy varchar2) is
580
581 l_request_id number;
582 l_card_program_id number;
583 l_start_date date;
584 l_end_date date;
585
586 url varchar2(2000);
587 begin
588 if ( funcmode = 'RUN' ) then
589 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true);
590 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',true);
591
592 url := 'JSP:/OA_HTML/OA.jsp?OAFunc=OIE_NEW_CCARD_SEARCH_FN'||
593 '&'||'restrict=Y'||
594 '&'||'pRequestId='||to_char(l_request_id)||
595 '&'||'pCardProgramId='||to_char(l_card_program_id)||
596 '&'||'NtfId=-&#NID-';
597
598 wf_engine.setitemattrtext(itemtype,itemkey,'NEW_CREDIT_CARD_URL',url);
599
600 resultout := 'COMPLETE:';
601 return;
602 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
603 resultout := 'COMPLETE';
604 return;
605 else
606 resultout := ' ';
607 return;
608 end if;
609 exception
610 when others then
611 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
612 fnd_log.string(fnd_log.level_unexpected,
613 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.get_new_card_page_url',
614 sqlerrm);
615 end if;
616 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_NEW_CARD_PAGE_URL',
617 itemtype, itemkey, to_char(actid), funcmode);
618 raise;
619 end get_new_card_page_url;
620
621 --
622 -- Returns the name of the user who initiated the workflow.
623 -- If the workflow is initiated through by a concurrent program,
624 -- the current user would be the user who initiated the
625 -- concurrent program.
626 procedure whoami(itemtype in varchar2,
627 itemkey in varchar2,
628 actid in number,
629 funcmode in varchar2,
630 resultout out nocopy varchar2) is
631 attr_name varchar2(30);
632 user_name varchar2(100);
633 l_request_id number; -- Bug 6971825
634 begin
635 if ( funcmode = 'RUN' ) then
636
637 begin
641 from fnd_user
638 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true); -- Bug 6971825
639
640 select user_name into user_name
642 where user_id in (select requested_by from FND_CONCURRENT_REQUESTS where
643 request_id = nvl(l_request_id,fnd_global.user_id));
644
645 attr_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_NAME', false);
646 wf_engine.setitemattrtext(itemtype,itemkey,attr_name,user_name);
647 exception
648 when no_data_found then
649 user_name := null;
650 end;
651
652 resultout := 'COMPLETE:';
653 return;
654 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
655 resultout := 'COMPLETE';
656 return;
657 else
658 resultout := ' ';
659 return;
660 end if;
661 exception
662 when others then
663 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
664 fnd_log.string(fnd_log.level_unexpected,
665 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.whoami',
666 sqlerrm);
667 end if;
668 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'WHOAMI',
669 itemtype, itemkey, to_char(actid), funcmode);
670 raise;
671 end whoami;
672
673 --
674 -- Returns the name of the system administrator role for
675 -- the card program.
676 procedure get_card_sysadmin(itemtype in varchar2,
677 itemkey in varchar2,
678 actid in number,
679 funcmode in varchar2,
680 resultout out nocopy varchar2) is
681 l_attr_name varchar2(30);
682 l_card_program_id number;
683 l_role_name varchar2(360);
684 l_person_id number;
685 begin
686 if ( funcmode = 'RUN' ) then
687 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',false);
688
689 IF(l_card_program_id IS NOT NULL) THEN
690 select sysadmin_role_name, admin_employee_id into l_role_name, l_person_id
691 from ap_card_programs_all
692 where card_program_id = l_card_program_id;
693 ELSE
694 l_role_name := NULL;
695 l_person_id := NULL;
696 END IF;
697
698 if l_role_name is null and l_person_id is not null then
699 begin
700 select name into l_role_name
701 from wf_roles
702 where orig_system = 'PER'
703 and orig_system_id = l_person_id;
704 exception
705 when no_data_found then
706 l_role_name := null;
707 when too_many_rows then
708 l_role_name := null;
709 end;
710 end if;
711
712 if l_role_name is null then
713 whoami(itemtype, itemkey, actid, funcmode, resultout);
714 return;
715 else
716 l_attr_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_NAME', false);
717 wf_engine.setitemattrtext(itemtype,itemkey,l_attr_name,l_role_name);
718
719 resultout := 'COMPLETE:';
720 return;
721 end if;
722 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
723 resultout := 'COMPLETE';
724 return;
725 else
726 resultout := ' ';
727 return;
728 end if;
729 exception
730 when others then
731 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
732 fnd_log.string(fnd_log.level_unexpected,
733 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_CARD_SYSADMIN', sqlerrm);
734 end if;
735 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_CARD_SYSADMIN',
736 itemtype, itemkey, to_char(actid), funcmode);
737 raise;
738 end get_card_sysadmin;
739
740
741
742 --
743 -- Returns the name of the card program
744 procedure get_card_program_name(itemtype in varchar2,
745 itemkey in varchar2,
746 actid in number,
747 funcmode in varchar2,
748 resultout out nocopy varchar2) is
749 l_name varchar2(80);
750 l_id number;
751 begin
752 if ( funcmode = 'RUN' ) then
753 l_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',false);
754 -- Bug 6829024(sodash)
755 if (l_id is null) then
756 wf_engine.setitemattrtext(itemtype,itemkey,'CARD_PROGRAM_NAME',null);
757 return;
758 end if;
759
760 select card_program_name into l_name
761 from ap_card_programs_all
762 where card_program_id = l_id;
763 wf_engine.setitemattrtext(itemtype,itemkey,'CARD_PROGRAM_NAME',l_name);
764
765 resultout := 'COMPLETE';
766 return;
767 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
768 resultout := 'COMPLETE';
769 return;
770 else
771 resultout := ' ';
772 return;
773 end if;
774 exception
775 when others then
776 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
777 fnd_log.string(fnd_log.level_unexpected,
778 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_CARD_PROGRAM_NAME', sqlerrm);
779 end if;
780 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_CARD_PROGRAM_NAME',
781 itemtype, itemkey, to_char(actid), funcmode);
782 raise;
783 end get_card_program_name;
784
785 --
786 -- Returns the value of RETURN_ATTRIBUTE_NAME
787 procedure get_attribute_value(itemtype in varchar2,
788 itemkey in varchar2,
789 actid in number,
790 funcmode in varchar2,
791 resultout out nocopy varchar2) is
792 l_attrname VARCHAR2(100);
793 l_attrval VARCHAR2(100);
794 begin
795 if ( funcmode = 'RUN' ) then
796 l_attrname := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'RETURN_ATTRIBUTE_NAME',false);
797 l_attrval := wf_engine.getitemattrtext(itemtype,itemkey,l_attrname,false);
798 resultout := 'COMPLETE:'||l_attrval;
799 return;
800 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
801 resultout := 'COMPLETE';
802 return;
803 else
804 resultout := ' ';
805 return;
806 end if;
807 exception
808 when others then
809 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
810 fnd_log.string(fnd_log.level_unexpected,
811 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_ATTRIBUUTE_VALUE', sqlerrm);
812 end if;
813 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_ATTRIBUTE_VALUE',
814 itemtype, itemkey, to_char(actid), funcmode);
815 raise;
816 end get_attribute_value;
817
818 --
819 -- Returns the activity value of RETURN_ATTRIBUTE_NAME
820 procedure get_act_attribute_value(itemtype in varchar2,
821 itemkey in varchar2,
822 actid in number,
823 funcmode in varchar2,
824 resultout out nocopy varchar2) is
825 l_attrname VARCHAR2(100);
826 l_attrval VARCHAR2(100);
827 begin
828 if ( funcmode = 'RUN' ) then
829 l_attrval := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_VALUE',FALSE);
830 resultout := 'COMPLETE:'||l_attrval;
831 return;
832 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
833 resultout := 'COMPLETE';
834 return;
835 else
836 resultout := ' ';
837 return;
838 end if;
839 exception
840 when others then
841 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
842 fnd_log.string(fnd_log.level_unexpected,
843 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_ATTRIBUUTE_VALUE', sqlerrm);
844 end if;
845 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_ATTRIBUTE_VALUE',
846 itemtype, itemkey, to_char(actid), funcmode);
847 raise;
848 end get_act_attribute_value;
849
850 procedure get_instructions(itemtype in varchar2,
851 itemkey in varchar2,
852 actid in number,
853 funcmode in varchar2,
854 resultout out nocopy varchar2) is
855 l_message_app VARCHAR2(30);
856 l_message_name varchar2(30);
857 l_message_text varchar2(240);
858 begin
859 if ( funcmode = 'RUN' ) THEN
860 l_message_app := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'MESSAGE_APP',false);
861 l_message_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'MESSAGE_NAME',false);
862 fnd_message.set_name(l_message_app, l_message_name);
863 l_message_text := fnd_message.get;
864 wf_engine.setitemattrtext(itemtype,itemkey,'INSTRUCTIONS',l_message_text);
865 resultout := 'COMPLETE';
866 return;
867 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
868 resultout := 'COMPLETE';
869 return;
870 else
871 resultout := ' ';
872 return;
873 end if;
874 exception
875 when others then
876 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
877 fnd_log.string(fnd_log.level_unexpected,
878 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_INSTRUCTIONS', sqlerrm);
879 end if;
880 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_INSTRUCTIONS',
881 itemtype, itemkey, to_char(actid), funcmode);
882 raise;
883 end get_instructions;
884
885
886
887
888 end ap_web_cc_validation_wf_pkg;