[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.4.12010000.2 2008/08/06 07:50:16 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
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',
62 'Raised validation event with key = '||to_char(l_event_key));
63 end if;
64 end loop;
65
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,
215 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.get_search_page_url',
216 sqlerrm);
217 end if;
218 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_SEARCH_PAGE_URL',
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 begin
235 l_parameter_list := wf_parameter_list_t(
236 wf_parameter_t('REQUEST_ID', to_char(p_request_id)),
237 wf_parameter_t('CARD_PROGRAM_ID', to_char(p_card_program_id)),
238 wf_parameter_t('START_DATE', to_char(p_start_date, 'YYYY/MM/DD')),
239 wf_parameter_t('END_DATE', to_char(p_end_date, 'YYYY/MM/DD'))
240 );
241
242 select ap_ccard_notification_id_s.nextval into l_event_key
243 from dual;
244
245 wf_event.raise(p_event_name => 'oracle.apps.ap.oie.creditcard.account.create',
246 p_event_key => to_char(l_event_key),
247 p_parameters => l_parameter_list);
248 if ( FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
249 fnd_log.string(fnd_log.level_event,
250 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.raise_new_cc_event',
251 'Raised new card event with key = '||to_char(l_event_key));
252 end if;
253
254
255 return l_event_key;
256 end raise_new_cc_event;
257
258
259 --
260 -- Find employee matches
261 procedure card_employee_match(itemtype in varchar2,
262 itemkey in varchar2,
263 actid in number,
264 funcmode in varchar2,
265 resultout out nocopy varchar2) is
266 l_request_id number;
267 l_card_program_id number;
268 l_match_rule varchar2(30);
269 l_stmt varchar2(200);
270
271 cursor ccard is
272 select det.card_id -- , det.name, det.employee_number, det.national_identifier
273 from ap_card_details det, ap_cards_all card
274 where det.card_id = card.card_id
275 and card.request_id = l_request_id;
276 begin
277 if ( funcmode = 'RUN' ) then
278 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID', true);
279 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID', true);
280
281 select card_emp_matching_rule into l_match_rule
282 from ap_card_programs_all
283 where card_program_id = l_card_program_id;
284
285 if l_match_rule is not null then
286 l_stmt := 'begin '||
287 l_match_rule||'.get_employee_matches(:cardId); '||
288 'end;';
289 for crec in ccard loop
290 execute immediate l_stmt using crec.card_id;
291 end loop;
292
293 end if;
294
295 resultout := 'COMPLETE:';
296 return;
297 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
298 resultout := 'COMPLETE';
299 return;
300 else
301 resultout := ' ';
302 return;
303 end if;
304 exception
305 when others then
306 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
307 fnd_log.string(fnd_log.LEVEL_UNEXPECTED,
308 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.CARD_EMPLOYEE_MATCH',
309 sqlerrm);
310 end if;
311 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'CARD_EMPLOYEE_MATCH',
312 itemtype, itemkey, to_char(actid), funcmode);
313 raise;
314 end card_employee_match;
315
316
317 --
318 -- Assigns employees to credit cards if only one employee
319 -- candidate was found - thereby activating the credit card.
320 procedure assign_emp_if_unique(itemtype in varchar2,
321 itemkey in varchar2,
322 actid in number,
323 funcmode in varchar2,
324 resultout out nocopy varchar2) is
325 l_request_id number;
326 l_card_program_id number;
327
328 cursor cemp is
329 select c.card_id, max(emp.employee_id) as employee_id
330 from ap_cards_all ca, ap_card_details c, ap_card_emp_candidates emp
331 where c.card_id = emp.card_id
332 and ca.card_id = c.card_id
333 and ca.request_id = l_request_id
334 group by c.card_id
335 having count(*) = 1;
336
337 begin
338 if ( funcmode = 'RUN' ) then
339 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID', false);
340 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID', true);
341
342 for crec in cemp loop
343 ap_web_cc_validations_pkg.assign_employee(crec.card_id, crec.employee_id);
344 end loop;
345 resultout := 'COMPLETE:';
346 return;
347 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
348 resultout := 'COMPLETE';
349 return;
350 else
351 resultout := ' ';
352 return;
353 end if;
354 exception
355 when others then
356 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
357 fnd_log.string(fnd_log.level_unexpected,
358 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.ASSIGN_EMP_IF_UNIQUE',
359 sqlerrm);
360 end if;
361 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'ASSIGN_EMP_IF_UNIQUE',
362 itemtype, itemkey, to_char(actid), funcmode);
363 raise;
364 end assign_emp_if_unique;
365
366 --
370 itemkey in varchar2,
367 -- Checks to see if new credit cards were created by
368 -- a given request id
369 procedure new_cards_exist(itemtype in varchar2,
371 actid in number,
372 funcmode in varchar2,
373 resultout out nocopy varchar2) is
374 l_request_id number;
375 l_exist number;
376 begin
377 if ( funcmode = 'RUN' ) then
378 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
379 select count(*) into l_exist from dual
380 where exists (select 1 from ap_cards_all where request_id = l_request_id);
381
382 if l_exist = 0 then
383 resultout := 'COMPLETE:F';
384 else
385 resultout := 'COMPLETE:T';
386 end if;
387 return;
388 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
389 resultout := 'COMPLETE';
390 return;
391 else
392 resultout := ' ';
393 return;
394 end if;
395 exception
396 when others then
397 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
398 fnd_log.string(fnd_log.level_unexpected,
399 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.NEW_CARDS_EXIST',
400 sqlerrm);
401 end if;
402 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'NEW_CARDS_EXIST',
403 itemtype, itemkey, to_char(actid), funcmode);
404 raise;
405 end new_cards_exist;
406
407
408 --
409 -- Checks to see if inactive credit cards were created by
410 -- a given request id
411 procedure inactive_cards_exist(itemtype in varchar2,
412 itemkey in varchar2,
413 actid in number,
414 funcmode in varchar2,
415 resultout out nocopy varchar2) is
416 l_request_id number;
417 l_exist number;
418 begin
419 if ( funcmode = 'RUN' ) then
420 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
421 select count(*) into l_exist from dual
422 where exists (select 1 from ap_cards_all where request_id = l_request_id and employee_id is null);
423
424 if l_exist = 0 then
425 resultout := 'COMPLETE:F';
426 else
427 resultout := 'COMPLETE:T';
428 end if;
429 return;
430 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
431 resultout := 'COMPLETE';
432 return;
433 else
434 resultout := ' ';
435 return;
436 end if;
437 exception
438 when others then
439 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
440 fnd_log.string(fnd_log.level_unexpected,
441 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.INACTIVE_CARDS_EXIST',
442 sqlerrm);
443 end if;
444 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'INACTIVE_CARDS_EXIST',
445 itemtype, itemkey, to_char(actid), funcmode);
446 raise;
447 end inactive_cards_exist;
448
449
450 --
451 -- Checks to see if invalid credit card trx were created by
452 -- a given request id
453 procedure invalid_cctrx_exist(itemtype in varchar2,
454 itemkey in varchar2,
455 actid in number,
456 funcmode in varchar2,
457 resultout out nocopy varchar2) is
458 l_request_id number;
459 l_card_program_id NUMBER;
460 l_start_date DATE;
461 l_end_date DATE;
462 l_exist number;
463 l_exist1 number;
464 l_exist2 number;
465 begin
466 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)
467 if ( funcmode = 'RUN' ) then
468 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
469 l_card_program_id := wf_engine.getitemattrnumber(itemtype, itemkey,'CARD_PROGRAM_ID',FALSE);
470 l_start_date := wf_engine.getitemattrdate(itemtype, itemkey,'START_DATE',FALSE);
471 l_end_date := wf_engine.getitemattrdate(itemtype, itemkey,'END_DATE',FALSE);
472 select count(*) into l_exist1 from dual
473 where exists (select 1 from ap_credit_card_trxns_all where request_id = l_request_id and validate_code <> 'Y');
474 SELECT COUNT(*) INTO l_exist2 FROM dual
475 WHERE exists (SELECT 1 FROM ap_credit_card_trxns_all
476 WHERE (l_card_program_id IS NULL OR card_program_id = l_card_program_id) -- Bug 6829024(sodash)
477 AND (l_start_date IS NULL OR transaction_date IS NULL OR transaction_date >= l_start_date)
478 AND (l_end_date IS NULL OR transaction_date IS NULL OR transaction_date <= l_end_date)
479 AND validate_code <> 'Y'
480 );
481 l_exist := l_exist1 + l_exist2;
482
483 if l_exist = 0 then
484 resultout := 'COMPLETE:F';
485 else
486 resultout := 'COMPLETE:T';
487 end if;
488 return;
489 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
490 resultout := 'COMPLETE';
491 return;
492 else
493 resultout := ' ';
494 return;
495 end if;
496 exception
497 when others then
501 sqlerrm);
498 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
499 fnd_log.string(fnd_log.level_unexpected,
500 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.INVALID_CCTRX_EXIST',
502 end if;
503 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'INVALID_CCTRX_EXIST',
504 itemtype, itemkey, to_char(actid), funcmode);
505 raise;
506 end invalid_cctrx_exist;
507
508 --
509 -- Counts the number of new credit cards that were created by
510 -- a given Request ID
511 procedure count_new_cards(itemtype in varchar2,
512 itemkey in varchar2,
513 actid in number,
514 funcmode in varchar2,
515 resultout out nocopy varchar2) is
516 l_request_id number;
517 l_count number;
518 begin
519 if ( funcmode = 'RUN' ) then
520 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',false);
521 select count(*) into l_count
522 from ap_cards_all
523 where request_id = l_request_id;
524
525 wf_engine.setitemattrnumber(itemtype,itemkey,'NEW_CARD_COUNT',l_count);
526
527 resultout := 'COMPLETE:';
528 return;
529 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
530 resultout := 'COMPLETE';
531 return;
532 else
533 resultout := ' ';
534 return;
535 end if;
536 exception
537 when others then
538 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
539 fnd_log.string(fnd_log.level_unexpected,
540 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.COUNT_NEW_CARDS',
541 sqlerrm);
542 end if;
543 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'COUNT_NEW_CARDS',
544 itemtype, itemkey, to_char(actid), funcmode);
545 raise;
546 end count_new_cards;
547
548
549 --
550 -- Returns the URL to the New Card Search page.
551 procedure get_new_card_page_url(itemtype in varchar2,
552 itemkey in varchar2,
553 actid in number,
554 funcmode in varchar2,
555 resultout out nocopy varchar2) is
556
557 l_request_id number;
558 l_card_program_id number;
559 l_start_date date;
560 l_end_date date;
561
562 url varchar2(2000);
563 begin
564 if ( funcmode = 'RUN' ) then
565 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true);
566 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',true);
567
568 url := 'JSP:/OA_HTML/OA.jsp?OAFunc=OIE_NEW_CCARD_SEARCH_FN'||
569 '&'||'restrict=Y'||
570 '&'||'pRequestId='||to_char(l_request_id)||
571 '&'||'pCardProgramId='||to_char(l_card_program_id)||
572 '&'||'NtfId=-&#NID-';
573
574 wf_engine.setitemattrtext(itemtype,itemkey,'NEW_CREDIT_CARD_URL',url);
575
576 resultout := 'COMPLETE:';
577 return;
578 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
579 resultout := 'COMPLETE';
580 return;
581 else
582 resultout := ' ';
583 return;
584 end if;
585 exception
586 when others then
587 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
588 fnd_log.string(fnd_log.level_unexpected,
589 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.get_new_card_page_url',
590 sqlerrm);
591 end if;
592 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_NEW_CARD_PAGE_URL',
593 itemtype, itemkey, to_char(actid), funcmode);
594 raise;
595 end get_new_card_page_url;
596
597 --
598 -- Returns the name of the user who initiated the workflow.
599 -- If the workflow is initiated through by a concurrent program,
600 -- the current user would be the user who initiated the
601 -- concurrent program.
602 procedure whoami(itemtype in varchar2,
603 itemkey in varchar2,
604 actid in number,
605 funcmode in varchar2,
606 resultout out nocopy varchar2) is
607 attr_name varchar2(30);
608 user_name varchar2(100);
609 l_request_id number; -- Bug 6971825
610 begin
611 if ( funcmode = 'RUN' ) then
612
613 begin
614 l_request_id := wf_engine.getitemattrnumber(itemtype,itemkey,'REQUEST_ID',true); -- Bug 6971825
615
616 select user_name into user_name
617 from fnd_user
618 where user_id in (select requested_by from FND_CONCURRENT_REQUESTS where
619 request_id = nvl(l_request_id,fnd_global.user_id));
620
621 attr_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_NAME', false);
622 wf_engine.setitemattrtext(itemtype,itemkey,attr_name,user_name);
623 exception
624 when no_data_found then
625 user_name := null;
626 end;
627
628 resultout := 'COMPLETE:';
629 return;
630 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
631 resultout := 'COMPLETE';
632 return;
633 else
637 exception
634 resultout := ' ';
635 return;
636 end if;
638 when others then
639 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
640 fnd_log.string(fnd_log.level_unexpected,
641 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.whoami',
642 sqlerrm);
643 end if;
644 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'WHOAMI',
645 itemtype, itemkey, to_char(actid), funcmode);
646 raise;
647 end whoami;
648
649 --
650 -- Returns the name of the system administrator role for
651 -- the card program.
652 procedure get_card_sysadmin(itemtype in varchar2,
653 itemkey in varchar2,
654 actid in number,
655 funcmode in varchar2,
656 resultout out nocopy varchar2) is
657 l_attr_name varchar2(30);
658 l_card_program_id number;
659 l_role_name varchar2(360);
660 l_person_id number;
661 begin
662 if ( funcmode = 'RUN' ) then
663 l_card_program_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',false);
664
665 IF(l_card_program_id IS NOT NULL) THEN
666 select sysadmin_role_name, admin_employee_id into l_role_name, l_person_id
667 from ap_card_programs_all
668 where card_program_id = l_card_program_id;
669 ELSE
670 l_role_name := NULL;
671 l_person_id := NULL;
672 END IF;
673
674 if l_role_name is null and l_person_id is not null then
675 begin
676 select name into l_role_name
677 from wf_roles
678 where orig_system = 'PER'
679 and orig_system_id = l_person_id;
680 exception
681 when no_data_found then
682 l_role_name := null;
683 when too_many_rows then
684 l_role_name := null;
685 end;
686 end if;
687
688 if l_role_name is null then
689 whoami(itemtype, itemkey, actid, funcmode, resultout);
690 return;
691 else
692 l_attr_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_NAME', false);
693 wf_engine.setitemattrtext(itemtype,itemkey,l_attr_name,l_role_name);
694
695 resultout := 'COMPLETE:';
696 return;
697 end if;
698 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
699 resultout := 'COMPLETE';
700 return;
701 else
702 resultout := ' ';
703 return;
704 end if;
705 exception
706 when others then
707 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
708 fnd_log.string(fnd_log.level_unexpected,
709 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_CARD_SYSADMIN', sqlerrm);
710 end if;
711 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_CARD_SYSADMIN',
712 itemtype, itemkey, to_char(actid), funcmode);
713 raise;
714 end get_card_sysadmin;
715
716
717
718 --
719 -- Returns the name of the card program
720 procedure get_card_program_name(itemtype in varchar2,
721 itemkey in varchar2,
722 actid in number,
723 funcmode in varchar2,
724 resultout out nocopy varchar2) is
725 l_name varchar2(80);
726 l_id number;
727 begin
728 if ( funcmode = 'RUN' ) then
729 l_id := wf_engine.getitemattrnumber(itemtype,itemkey,'CARD_PROGRAM_ID',false);
730 -- Bug 6829024(sodash)
731 if (l_id is null) then
732 wf_engine.setitemattrtext(itemtype,itemkey,'CARD_PROGRAM_NAME',null);
733 return;
734 end if;
735
736 select card_program_name into l_name
737 from ap_card_programs_all
738 where card_program_id = l_id;
739 wf_engine.setitemattrtext(itemtype,itemkey,'CARD_PROGRAM_NAME',l_name);
740
741 resultout := 'COMPLETE';
742 return;
743 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
744 resultout := 'COMPLETE';
745 return;
746 else
747 resultout := ' ';
748 return;
749 end if;
750 exception
751 when others then
752 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
753 fnd_log.string(fnd_log.level_unexpected,
754 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_CARD_PROGRAM_NAME', sqlerrm);
755 end if;
756 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_CARD_PROGRAM_NAME',
757 itemtype, itemkey, to_char(actid), funcmode);
758 raise;
759 end get_card_program_name;
760
761 --
762 -- Returns the value of RETURN_ATTRIBUTE_NAME
763 procedure get_attribute_value(itemtype in varchar2,
764 itemkey in varchar2,
765 actid in number,
766 funcmode in varchar2,
767 resultout out nocopy varchar2) is
768 l_attrname VARCHAR2(100);
769 l_attrval VARCHAR2(100);
770 begin
771 if ( funcmode = 'RUN' ) then
772 l_attrname := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'RETURN_ATTRIBUTE_NAME',false);
773 l_attrval := wf_engine.getitemattrtext(itemtype,itemkey,l_attrname,false);
777 resultout := 'COMPLETE';
774 resultout := 'COMPLETE:'||l_attrval;
775 return;
776 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
778 return;
779 else
780 resultout := ' ';
781 return;
782 end if;
783 exception
784 when others then
785 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
786 fnd_log.string(fnd_log.level_unexpected,
787 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_ATTRIBUUTE_VALUE', sqlerrm);
788 end if;
789 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_ATTRIBUTE_VALUE',
790 itemtype, itemkey, to_char(actid), funcmode);
791 raise;
792 end get_attribute_value;
793
794 --
795 -- Returns the activity value of RETURN_ATTRIBUTE_NAME
796 procedure get_act_attribute_value(itemtype in varchar2,
797 itemkey in varchar2,
798 actid in number,
799 funcmode in varchar2,
800 resultout out nocopy varchar2) is
801 l_attrname VARCHAR2(100);
802 l_attrval VARCHAR2(100);
803 begin
804 if ( funcmode = 'RUN' ) then
805 l_attrval := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'ATTRIBUTE_VALUE',FALSE);
806 resultout := 'COMPLETE:'||l_attrval;
807 return;
808 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
809 resultout := 'COMPLETE';
810 return;
811 else
812 resultout := ' ';
813 return;
814 end if;
815 exception
816 when others then
817 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
818 fnd_log.string(fnd_log.level_unexpected,
819 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_ATTRIBUUTE_VALUE', sqlerrm);
820 end if;
821 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_ATTRIBUTE_VALUE',
822 itemtype, itemkey, to_char(actid), funcmode);
823 raise;
824 end get_act_attribute_value;
825
826 procedure get_instructions(itemtype in varchar2,
827 itemkey in varchar2,
828 actid in number,
829 funcmode in varchar2,
830 resultout out nocopy varchar2) is
831 l_message_app VARCHAR2(30);
832 l_message_name varchar2(30);
833 l_message_text varchar2(240);
834 begin
835 if ( funcmode = 'RUN' ) THEN
836 l_message_app := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'MESSAGE_APP',false);
837 l_message_name := wf_engine.getactivityattrtext(itemtype,itemkey,actid,'MESSAGE_NAME',false);
838 fnd_message.set_name(l_message_app, l_message_name);
839 l_message_text := fnd_message.get;
840 wf_engine.setitemattrtext(itemtype,itemkey,'INSTRUCTIONS',l_message_text);
841 resultout := 'COMPLETE';
842 return;
843 elsif ( funcmode in ('CANCEL', 'RESPOND', 'FORWARD', 'TRANSFER', 'TIMEOUT') ) then
844 resultout := 'COMPLETE';
845 return;
846 else
847 resultout := ' ';
848 return;
849 end if;
850 exception
851 when others then
852 if ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
853 fnd_log.string(fnd_log.level_unexpected,
854 'ap.pls.AP_WEB_CC_VALIDATION_WF_PKG.GET_INSTRUCTIONS', sqlerrm);
855 end if;
856 WF_CORE.CONTEXT ('AP_WEB_CC_VALIDATION_WF_PKG', 'GET_INSTRUCTIONS',
857 itemtype, itemkey, to_char(actid), funcmode);
858 raise;
859 end get_instructions;
860
861
862
863
864 end ap_web_cc_validation_wf_pkg;