[Home] [Help]
PACKAGE BODY: APPS.OZF_CLAIM_APPROVAL_PVT
Source
1 PACKAGE BODY OZF_CLAIM_APPROVAL_PVT AS
2 /* $Header: ozfvcawb.pls 120.7.12010000.2 2008/09/02 06:01:47 kpatro ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_Claim_Approval_PVT';
5 g_file_name CONSTANT VARCHAR2(15) := 'ozfvcawb.pls';
6
7 OZF_DEBUG_HIGH_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
8 OZF_DEBUG_LOW_ON BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
9
10 --------------------------------------------------------------------------
11 function find_org_id (p_claim_id IN NUMBER)
12 return number is
13
14 l_org_id number;
15
16 cursor get_claim_org_csr(p_id in number) is
17 select org_id
18 from ozf_claims_all
19 where claim_id = p_id;
20
21 begin
22
23 open get_claim_org_csr(p_claim_id);
24 fetch get_claim_org_csr into l_org_id;
25 close get_claim_org_csr;
26
27 return l_org_id;
28
29 end find_org_id;
30 --------------------------------------------------------------------------
31 procedure set_org_ctx (p_org_id IN NUMBER) is
32
33 begin
34
35 if p_org_id is not NULL then
36 MO_GLOBAL.set_policy_context('S', p_org_id); -- R12 Enhancements
37 end if;
38
39 end set_org_ctx;
40
41 PROCEDURE make_history_data(
42 p_activity_id IN NUMBER,
43 p_requester_id IN NUMBER,
44 x_return_status OUT NOCOPY VARCHAR2
45 ) IS
46
47 l_return_status VARCHAR2(1);
48 l_msg_count NUMBER;
49 l_msg_data VARCHAR2(4000);
50 l_approval_detail_id NUMBER;
51 l_appr_seq NUMBER;
52 l_appr_type VARCHAR2(30);
53 l_obj_appr_id NUMBER;
54 l_appr_hist_rec AMS_Appr_Hist_Pvt.Appr_Hist_Rec_Type;
55
56 CURSOR c_approver(rule_id IN NUMBER) IS
57 SELECT approver_seq, approver_type, object_approver_id
58 FROM ams_approvers
59 WHERE ams_approval_detail_id = rule_id
60 AND active_flag = 'Y'
61 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,SYSDATE -1 )
62 AND TRUNC(NVL(end_date_active,SYSDATE + 1));
63
64
65 BEGIN
66 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
67
68 -- The data Deletion is necessary if Claim is Resubmitted for Approval
69 -- Processign is only required for Earning and Performance
70
71 AMS_Appr_Hist_PVT.Delete_Appr_Hist(
72 p_api_version_number => 1.0,
73 p_init_msg_list => FND_API.G_FALSE,
74 p_commit => FND_API.G_FALSE,
75 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
76 x_return_status => l_return_status,
77 x_msg_count => l_msg_count,
78 x_msg_data => l_msg_data,
79 p_object_id => p_activity_id,
80 p_object_type_code => 'CLAM',
81 p_sequence_num => null,
82 p_action_code => null,
83 p_object_version_num => null,
84 p_approval_type => 'EARNING'
85 );
86
87 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
88 x_return_status := Fnd_Api.G_RET_STS_ERROR;
89 RETURN;
90 END IF;
91
92 AMS_Appr_Hist_PVT.Delete_Appr_Hist(
93 p_api_version_number => 1.0,
94 p_init_msg_list => FND_API.G_FALSE,
95 p_commit => FND_API.G_FALSE,
96 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
97 x_return_status => l_return_status,
98 x_msg_count => l_msg_count,
99 x_msg_data => l_msg_data,
100 p_object_id => p_activity_id,
101 p_object_type_code => 'CLAM',
102 p_sequence_num => null,
103 p_action_code => null,
104 p_object_version_num => null,
105 p_approval_type => 'PERFORMANCE'
106 );
107
108 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
109 x_return_status := Fnd_Api.G_RET_STS_ERROR;
110 RETURN;
111 END IF;
112 --Populate the History Table with Approval Data for EARNING and PERFORMANCE
113 l_appr_hist_rec.object_id := p_activity_id;
114 l_appr_hist_rec.object_type_code := 'CLAM';
115 l_appr_hist_rec.sequence_num := 0;
116 l_appr_hist_rec.object_version_num := 100;
117 l_appr_hist_rec.action_code := 'OPEN';
118 l_appr_hist_rec.action_date := sysdate;
119 l_appr_hist_rec.approver_id := p_requester_id;
120 l_appr_hist_rec.note := null;
121 l_appr_hist_rec.approval_type := 'EARNING';
122 l_appr_hist_rec.approver_type := 'USER'; -- User always submits
123
124
125
126 IF OZF_Claim_Accrual_PVT.Earnings_Approval_Required(p_activity_id) = FND_API.g_true THEN
127 AMS_Gen_Approval_Pvt.Get_Approval_Rule(p_activity_id ,
128 'CLAM',
129 'EARNING',
130 null,
131 x_approval_detail_id => l_approval_detail_id,
132 x_return_status => l_return_status);
133
134 l_appr_hist_rec.approval_detail_id := l_approval_detail_id;
135
136
137 OPEN c_approver(l_approval_detail_id);
138 LOOP
139 FETCH c_approver INTO l_appr_seq, l_appr_type, l_obj_appr_id;
140 EXIT WHEN c_approver%NOTFOUND;
141
142 -- Set Record Attributes that will change for each approver
143 l_appr_hist_rec.sequence_num := l_appr_seq;
144 l_appr_hist_rec.approver_type := l_appr_type;
145 l_appr_hist_rec.approver_id := l_obj_appr_id;
146
147 AMS_Appr_Hist_PVT.Create_Appr_Hist(
148 p_api_version_number => 1.0,
149 p_init_msg_list => FND_API.G_FALSE,
150 p_commit => FND_API.G_FALSE,
151 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
152 x_return_status => l_return_status,
153 x_msg_count => l_msg_count,
154 x_msg_data => l_msg_data,
155 p_appr_hist_rec => l_appr_hist_rec
156 );
157
158 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
159 x_return_status := Fnd_Api.G_RET_STS_ERROR;
160 RETURN;
161 END IF;
162
163 END LOOP;
164 CLOSE c_approver;
165 END IF;
166
167 l_appr_hist_rec.approval_type := 'PERFORMANCE';
168 l_appr_hist_rec.sequence_num := 0;
169
170 IF OZF_Claim_Accrual_PVT.Perform_Approval_Required(p_activity_id) = FND_API.g_true THEN
171 AMS_Gen_Approval_Pvt.Get_Approval_Rule(p_activity_id ,
172 'CLAM',
173 'PERFORMANCE',
174 null,
175 x_approval_detail_id => l_approval_detail_id,
176 x_return_status => l_return_status);
177
178 l_appr_hist_rec.approval_detail_id := l_approval_detail_id;
179
180
181 OPEN c_approver(l_approval_detail_id);
182 LOOP
183 FETCH c_approver INTO l_appr_seq, l_appr_type, l_obj_appr_id;
184 EXIT WHEN c_approver%NOTFOUND;
185
186 -- Set Record Attributes that will change for each approver
187 l_appr_hist_rec.sequence_num := l_appr_seq;
188 l_appr_hist_rec.approver_type := l_appr_type;
189 l_appr_hist_rec.approver_id := l_obj_appr_id;
190
191 AMS_Appr_Hist_PVT.Create_Appr_Hist(
192 p_api_version_number => 1.0,
193 p_init_msg_list => FND_API.G_FALSE,
194 p_commit => FND_API.G_FALSE,
195 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
196 x_return_status => l_return_status,
197 x_msg_count => l_msg_count,
198 x_msg_data => l_msg_data,
199 p_appr_hist_rec => l_appr_hist_rec
200 );
201
202 IF l_return_status <> Fnd_Api.G_RET_STS_SUCCESS THEN
203 x_return_status := Fnd_Api.G_RET_STS_ERROR;
204 RETURN;
205 END IF;
206
207 END LOOP;
208 CLOSE c_approver;
209 END IF;
210 END make_history_data;
211
212 --------------------------------------------------------------------------
213 PROCEDURE get_offer_perf_req(
214 p_claim_id IN NUMBER
215 ,x_offer_perf_req OUT NOCOPY VARCHAR2
216 )
217 IS
218 l_offer_perf_tbl OZF_Claim_Accrual_PVT.offer_performance_tbl_type;
219 l_offer_name VARCHAR2(30);
220
221 CURSOR csr_offer_name(cv_offer_id IN NUMBER) IS
222 SELECT o.offer_code
223 FROM ozf_offers o
224 WHERE o.qp_list_header_id = cv_offer_id;
225
226 BEGIN
227 OZF_Claim_Accrual_PVT.Check_Offer_Performance_Tbl(
228 p_claim_id => p_claim_id
229 ,x_offer_perf_tbl => l_offer_perf_tbl
230 );
231
232 IF l_offer_perf_tbl.count > 0 THEN
233 FOR j IN l_offer_perf_tbl.FIRST..l_offer_perf_tbl.LAST LOOP
234 OPEN csr_offer_name(l_offer_perf_tbl(j).offer_id);
235 FETCH csr_offer_name INTO l_offer_name;
236 CLOSE csr_offer_name;
237
238 FND_MESSAGE.set_name('OZF', 'OZF_EARN_OFFER_PERF_NTF');
239 FND_MESSAGE.set_token('OFFER', l_offer_name, false);
240
241 x_offer_perf_req := x_offer_perf_req || FND_MESSAGE.get;
242 END LOOP;
243 END IF;
244 END get_offer_perf_req;
245
246 --------------------------------------------------------------------------
247 PROCEDURE get_offer_earn_req(
248 p_claim_id IN NUMBER
249 ,x_offer_earn_req OUT NOCOPY VARCHAR2
250 )
251 IS
252 l_offer_earn_tbl OZF_Claim_Accrual_PVT.offer_earning_tbl_type;
253 l_offer_name VARCHAR2(30);
254
255 CURSOR csr_offer_name(cv_offer_id IN NUMBER) IS
256 SELECT o.offer_code
257 FROM ozf_offers o
258 WHERE o.qp_list_header_id = cv_offer_id;
259
260 BEGIN
261 OZF_Claim_Accrual_PVT.Check_Offer_Earning_Tbl(
262 p_claim_id => p_claim_id
263 ,x_offer_earn_tbl => l_offer_earn_tbl
264 );
265
266 IF l_offer_earn_tbl.count > 0 THEN
267 FOR j IN l_offer_earn_tbl.FIRST..l_offer_earn_tbl.LAST LOOP
268 FND_MESSAGE.set_name('OZF', 'OZF_EARN_OFFER_EARN_NTF');
269 FND_MESSAGE.set_token('AMOUNT', l_offer_earn_tbl(j).acctd_amount_over, false);
270
271 OPEN csr_offer_name(l_offer_earn_tbl(j).offer_id);
272 FETCH csr_offer_name INTO l_offer_name;
273 CLOSE csr_offer_name;
274 FND_MESSAGE.set_token('OFFER', l_offer_name, false);
275
276 x_offer_earn_req := x_offer_earn_req || FND_MESSAGE.get;
277 END LOOP;
278 END IF;
279
280 END get_offer_earn_req;
281
282 --------------------------------------------------------------------------
283 -- PROCEDURE
284 -- notify_requestor_fyi
285 --
286 -- PURPOSE
287 -- Generate the FYI Document for display in messages, either text or html
288 --
289 -- IN
290 -- document_id - Item Key
291 -- display_type - either 'text/plain' or 'text/html'
292 -- document - document buffer
293 -- document_type - type of document buffer created, either 'text/plain'
294 -- or 'text/html'
295 -- OUT
296 --
297 -- USED BY
298 -- Oracle MArketing Generic Apporval
299 --
300 -- HISTORY
301 -- 04/25/2001 Prashanth Nerella CREATION
302 -- 05/29/2001 MICHELLE CHANG MODIFIED
303 --------------------------------------------------------------------------
304 PROCEDURE notify_requestor_fyi(
305 document_id IN VARCHAR2
306 ,display_type IN VARCHAR2
307 ,document IN OUT NOCOPY VARCHAR2
308 ,document_type IN OUT NOCOPY VARCHAR2
309 )
310 IS
311 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_FYI';
312 l_hyphen_pos1 NUMBER;
313 l_fyi_notification VARCHAR2(10000);
314 l_activity_type VARCHAR2(30);
315 l_item_type VARCHAR2(30);
316 l_item_key VARCHAR2(30);
317 l_approval_type VARCHAR2(30);
318 l_approver VARCHAR2(200);
319 l_note VARCHAR2(3000);
320 l_subject VARCHAR2(500);
321 l_body VARCHAR2(3500);
322 l_requester VARCHAR2(30);
323 l_requested_amt NUMBER;
324 l_claim_id NUMBER;
325 l_claim_number VARCHAR2(30);
326 l_account_number VARCHAR2(30);
327 l_account_name VARCHAR2(360);
328 l_currency VARCHAR2(80);
329 l_amount_settled NUMBER;
330 l_claim_date DATE;
331 l_due_date DATE;
332 l_claim_type VARCHAR2(30);
333 l_billto_site VARCHAR2(40);
334 l_cm_reason VARCHAR2(80);
335 l_adj_reason VARCHAR2(80);
336 l_payment_method VARCHAR2(80);
337 l_claim_source VARCHAR2(30);
338 l_offer_req VARCHAR2(2000);
339
340 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
341 SELECT c.claim_number
342 , a.account_number
343 , hp.party_name
344 , curr.name
345 , c.amount_settled
346 , c.claim_date
347 , c.due_date
348 , ct.name
349 , hc.location
350 , rlk_cm.meaning
351 , rlk_adj.meaning
352 , lk_pm.meaning
353 , 'resource_name'
354 FROM ozf_claims_all c
355 , hz_cust_accounts a
356 , hz_parties hp
357 , ozf_claim_types_all_vl ct
358 , hz_cust_site_uses_all hc
359 , ozf_reason_codes_all_b rc
360 , ar_lookups rlk_cm
361 , ar_lookups rlk_adj
362 , ozf_lookups lk_pm
363 , fnd_currencies_vl curr
364 WHERE c.claim_id = p_claim_id
365 AND c.cust_account_id = a.cust_account_id
366 AND a.party_id = hp.party_id
367 AND c.currency_code = curr.currency_code
368 AND c.claim_type_id = ct.claim_type_id
369 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
370 AND c.reason_code_id = rc.reason_code_id
371 AND c.payment_method = lk_pm.lookup_code
372 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
373 AND rc.reason_code = rlk_cm.lookup_code(+)
374 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
375 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
376 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
377
378 BEGIN
379 IF OZF_DEBUG_HIGH_ON THEN
380 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
381 END IF;
382 document_type := 'text/plain';
383 -- parse document_id for the ':' dividing item type name from item key value
384 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
385 -- release 2.5 version of this demo
386 l_hyphen_pos1 := INSTR(document_id, ':');
387 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
388 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
389
390 l_activity_type := wf_engine.getitemattrtext(
391 itemtype => l_item_type
392 ,itemkey => l_item_key
393 ,aname => 'AMS_ACTIVITY_TYPE'
394 );
395
396 l_claim_id := wf_engine.getitemattrtext(
397 itemtype => l_item_type
398 ,itemkey => l_item_key
399 ,aname => 'AMS_ACTIVITY_ID'
400 );
401
402 l_requested_amt := wf_engine.getitemattrtext(
403 itemtype => l_item_type
404 ,itemkey => l_item_key
405 ,aname => 'AMS_REQUESTED_AMOUNT'
406 );
407
408 l_note := wf_engine.getitemattrtext(
409 itemtype => l_item_type
410 ,itemkey => l_item_key
411 ,aname => 'AMS_PREV_APPROVER_NOTE'
412 );
413
414 l_approver := wf_engine.getitemattrtext(
415 itemtype => l_item_type
416 ,itemkey => l_item_key
417 ,aname => 'AMS_APPROVER_DISPLAY_NAME'
418 );
419
420 l_approval_type := wf_engine.GetItemAttrText(
421 itemtype => l_item_type
422 ,itemkey => l_item_key
423 ,aname => 'AMS_APPROVAL_TYPE'
424 );
425
426 l_note := wf_engine.getitemattrtext(
427 itemtype => l_item_type
428 ,itemkey => l_item_key
429 ,aname => 'AMS_NOTES_FROM_REQUESTOR'
430 );
431
432 OPEN c_claim_rec(l_claim_id);
433 FETCH c_claim_rec INTO l_claim_number
434 , l_account_number
435 , l_account_name
436 , l_currency
437 , l_amount_settled
438 , l_claim_date
439 , l_due_date
440 , l_claim_type
441 , l_billto_site
442 , l_cm_reason
443 , l_adj_reason
444 , l_payment_method
445 , l_claim_source;
446 CLOSE c_claim_rec;
447
448 IF l_approval_type = 'PERFORMANCE' THEN
449 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_FORD_SUBJ');
450 ELSIF l_approval_type = 'EARNING' THEN
451 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_FORD_SUBJ');
452 ELSE
453 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_FORWARD_SUBJ');
454 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
455 fnd_message.set_token('AMOUNT', l_requested_amt, false);
456 END IF;
457 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
458 --fnd_message.set_token('APPROVER', l_approver, false);
459 l_subject := fnd_message.get;
460
461 wf_engine.setitemattrtext(
462 itemtype => l_item_type
463 ,itemkey => l_item_key
464 ,aname => 'FYI_SUBJECT'
465 ,avalue => l_subject
466 );
467
468 IF l_approval_type = 'PERFORMANCE' THEN
469 get_offer_perf_req(l_claim_id, l_offer_req);
470 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_FORD_INFO');
471 fnd_message.set_token('OFFR_PERF_REQ', l_offer_req, false);
472 ELSIF l_approval_type = 'EARNING' THEN
473 get_offer_earn_req(l_claim_id, l_offer_req);
474 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_FORD_INFO');
475 fnd_message.set_token('OFFR_EARN_REQ', l_offer_req, false);
476 ELSE
477 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPROVAL_INFO');
478 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_FORWARD_INFO');
479 END IF;
480 fnd_message.set_token('APPROVER_NAME', l_approver, false);
481 fnd_message.set_token('DATE', TO_CHAR(SYSDATE, 'MM-DD-YYYY'), false);
482 fnd_message.set_token('TIME', TO_CHAR(SYSDATE, 'HH24:MI'), false);
483 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
484 fnd_message.set_token('CLAIM_TYPE', l_claim_type, false);
485 fnd_message.set_token('CLAIM_DATE', l_claim_date, false);
486 fnd_message.set_token('DUE_DATE', l_due_date, false);
487 fnd_message.set_token('CUSTOMER_NAME', l_account_name, false);
488 fnd_message.set_token('CUST_ACCT_NUMBER', l_account_number, false);
489 fnd_message.set_token('BILL_TO_SITE', l_billto_site, false);
490 fnd_message.set_token('CM_REASON', l_cm_reason, false);
491 fnd_message.set_token('ADJ_REASON', l_adj_reason, false);
492 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
493 fnd_message.set_token('AMOUNT', l_requested_amt, false);
494 fnd_message.set_token('SETTLEMENT_METHOD', l_payment_method, false);
495 fnd_message.set_token('JUSTIFICATION_NOTES', l_note, false);
496 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
497 l_body := fnd_message.get;
498 /*
499 l_note := wf_engine.getitemattrtext(
500 itemtype => l_item_type
501 ,itemkey => l_item_key
502 ,aname => 'NOTE');
503
504
505 l_forwarder :=
506 wf_engine.getitemattrtext(
507 itemtype => l_item_type
508 ,itemkey => l_item_key
509 ,aname => 'OZF_FORWARD_FROM_USERNAME');
510 */
511 -- IF (display_type = 'text/plain') THEN
512 -- l_fyi_notification := l_subject || FND_GLOBAL.LOCAL_CHR(10) || l_body;
513 l_fyi_notification := l_body;
514 document := document || l_fyi_notification;
515 document_type := 'text/plain';
516 RETURN;
517 -- END IF;
518 /*
519 IF (display_type = 'text/html') THEN
520 l_fyi_notification := l_string ||
521 FND_GLOBAL.LOCAL_CHR(10) ||
522 l_string1 ||
523 FND_GLOBAL.LOCAL_CHR(10) ||
524 l_string2;
525 document := document||l_appreq_notification;
526 document_type := 'text/html';
527 RETURN;
528 END IF;
529 */
530 EXCEPTION
531 WHEN OTHERS THEN
532 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
533 , 'NOTIFY_REQUESTOR_FYI'
534 , l_item_type
535 , l_item_key
536 );
537 RAISE;
538 END notify_requestor_fyi;
539
540
541 --------------------------------------------------------------------------
542 -- PROCEDURE
543 -- notify_approval_required
544 --
545 -- PURPOSE
546 -- Generate the Rejection Document for display in messages, either text or html
547 --
548 -- IN
549 -- document_id - Item Key
550 -- display_type - either 'text/plain' or 'text/html'
551 -- document - document buffer
552 -- document_type - type of document buffer created, either 'text/plain'
553 -- or 'text/html'
554 --
555 -- OUT
556 --
557 -- USED BY
558 -- Oracle MArketing Generic Apporval
559 --
560 -- HISTORY
561 -- 04/25/2001 Prashanth Nerella CREATION
562 -- 05/29/2001 MICHELLE CHANG MODIFIED
563 -------------------------------------------------------------------------------
564 PROCEDURE notify_approval_required(
565 document_id IN VARCHAR2
566 ,display_type IN VARCHAR2
567 ,document IN OUT NOCOPY VARCHAR2
568 ,document_type IN OUT NOCOPY VARCHAR2
569 )
570 IS
571 l_api_name VARCHAR2(100) := g_pkg_name || 'notify_approval_required';
572 l_hyphen_pos1 NUMBER;
573 l_appreq_notification VARCHAR2(10000);
574 l_activity_type VARCHAR2(30);
575 l_item_type VARCHAR2(30);
576 l_item_key VARCHAR2(30);
577 l_approval_type VARCHAR2(30);
578 l_approver VARCHAR2(200);
579 l_forwarder VARCHAR2(30);
580 l_note VARCHAR2(3000);
581 l_subject VARCHAR2(500);
582 l_body VARCHAR2(3500);
583 l_requester VARCHAR2(30);
584 l_requested_amt NUMBER;
585 l_approved_amt NUMBER;
586
587 l_claim_id NUMBER;
588 l_claim_number VARCHAR2(30);
589 l_account_number VARCHAR2(30);
590 l_account_name VARCHAR2(360);
591 l_currency VARCHAR2(80);
592 l_amount_remaining NUMBER;
593 l_claim_date DATE;
594 l_due_date DATE;
595 l_claim_type VARCHAR2(30);
596 l_billto_site VARCHAR2(40);
597 l_cm_reason VARCHAR2(80);
598 l_adj_reason VARCHAR2(80);
599 l_payment_method VARCHAR2(80);
600 l_claim_source VARCHAR2(30);
601 l_pre_approver VARCHAR2(360);
602 l_offer_req VARCHAR2(2000);
603
604 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
605 SELECT c.claim_number
606 , a.account_number
607 , hp.party_name
608 , curr.name
609 , c.amount_settled
610 , c.claim_date
611 , c.due_date
612 , ct.name
613 , hc.location
614 , rlk_cm.meaning
615 , rlk_adj.meaning
616 , lk_pm.meaning
617 , 'resource_name'
618 FROM ozf_claims_all c
619 , hz_cust_accounts a
620 , hz_parties hp
621 , ozf_claim_types_all_vl ct
622 , hz_cust_site_uses_all hc
623 , ozf_reason_codes_all_b rc
624 , ar_lookups rlk_cm
625 , ar_lookups rlk_adj
626 , ozf_lookups lk_pm
627 , fnd_currencies_vl curr
628 WHERE c.claim_id = p_claim_id
629 AND c.cust_account_id = a.cust_account_id
630 AND a.party_id = hp.party_id
631 AND c.currency_code = curr.currency_code
632 AND c.claim_type_id = ct.claim_type_id
633 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
634 AND c.reason_code_id = rc.reason_code_id
635 AND c.payment_method = lk_pm.lookup_code
636 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
637 AND rc.reason_code = rlk_cm.lookup_code(+)
638 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
639 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
640 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
641
642 BEGIN
643 IF OZF_DEBUG_HIGH_ON THEN
644 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
645 END IF;
646 document_type := 'text/plain';
647 -- parse document_id for the ':' dividing item type name from item key value
648 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
649 -- release 2.5 version of this demo
650 l_hyphen_pos1 := INSTR(document_id, ':');
651 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
652 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
653 l_activity_type := wf_engine.getitemattrtext(
654 itemtype => l_item_type
655 ,itemkey => l_item_key
656 ,aname => 'AMS_ACTIVITY_TYPE'
657 );
658
659 l_claim_id := wf_engine.getitemattrtext(
660 itemtype => l_item_type
661 ,itemkey => l_item_key
662 ,aname => 'AMS_ACTIVITY_ID'
663 );
664
665 l_requested_amt := wf_engine.getitemattrtext(
666 itemtype => l_item_type
667 ,itemkey => l_item_key
668 ,aname => 'AMS_REQUESTED_AMOUNT'
669 );
670
671 l_requester := wf_engine.getitemattrtext(
672 itemtype => l_item_type
673 ,itemkey => l_item_key
674 ,aname => 'AMS_REQUESTER'
675 );
676
677 l_approver := wf_engine.getitemattrtext(
678 itemtype => l_item_type
679 ,itemkey => l_item_key
680 ,aname => 'AMS_APPROVER_DISPLAY_NAME'
681 );
682
683 l_pre_approver := wf_engine.getitemattrtext(
684 itemtype => l_item_type
685 ,itemkey => l_item_key
686 ,aname => 'AMS_PREV_APPROVER_DISP_NAME'
687 );
688
689
690 -- start of Bugfix 5686652
691 -- If the approver is the first in line, then the requestor's comments must be shown
692 -- If the approver is not the first, previous approver's comments must be shown
693
694 -- start of Bugfix 7334602
695 l_note := wf_engine.getitemattrtext(
696 itemtype => l_item_type
697 ,itemkey => l_item_key
698 ,aname => 'AMS_NOTES_FROM_REQUESTOR'
699 );
700
701
702 -- end of Bugfix 7334602
703 -- end of Bugfix 5686652
704 /*
705 l_note := wf_engine.getitemattrtext(
706 itemtype => l_item_type
707 ,itemkey => l_item_key
708 ,aname => 'NOTE'
709 );
710 */
711
712 l_approval_type := wf_engine.GetItemAttrText(
713 itemtype => l_item_type
714 ,itemkey => l_item_key
715 ,aname => 'AMS_APPROVAL_TYPE'
716 );
717
718 OPEN c_claim_rec(l_claim_id);
719 FETCH c_claim_rec INTO l_claim_number
720 , l_account_number
721 , l_account_name
722 , l_currency
723 , l_amount_remaining
724 , l_claim_date
725 , l_due_date
726 , l_claim_type
727 , l_billto_site
728 , l_cm_reason
729 , l_adj_reason
730 , l_payment_method
731 , l_claim_source;
732 CLOSE c_claim_rec;
733
734 IF l_approval_type = 'PERFORMANCE' THEN
735 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPR_SUBJ');
736 ELSIF l_approval_type = 'EARNING' THEN
737 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPR_SUBJ');
738 ELSE
739 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPROVAL_SUBJ');
740 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
741 fnd_message.set_token('AMOUNT', l_requested_amt, false);
742 END IF;
743 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
744 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
745 l_subject := fnd_message.get;
746
747 wf_engine.setitemattrtext(
748 itemtype => l_item_type
749 ,itemkey => l_item_key
750 ,aname => 'APP_SUBJECT'
751 ,avalue => l_subject
752 );
753
754 IF l_approval_type = 'PERFORMANCE' THEN
755 get_offer_perf_req(l_claim_id, l_offer_req);
756 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPR_INFO');
757 fnd_message.set_token('OFFR_PERF_REQ', l_offer_req, false);
758 ELSIF l_approval_type = 'EARNING' THEN
759 get_offer_earn_req(l_claim_id, l_offer_req);
760 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPR_INFO');
761 fnd_message.set_token('OFFR_EARN_REQ', l_offer_req, false);
762 ELSE
763 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPROVAL_INFO');
764 END IF;
765 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
766 fnd_message.set_token('AMOUNT', l_requested_amt, false);
767 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
768 fnd_message.set_token('CLAIM_TYPE', l_claim_type, false);
769 fnd_message.set_token('CLAIM_DATE', l_claim_date, false);
770 fnd_message.set_token('DUE_DATE', l_due_date, false);
771 fnd_message.set_token('CUSTOMER_NAME', l_account_name, false);
772 fnd_message.set_token('CUST_ACCT_NUMBER', l_account_number, false);
773 fnd_message.set_token('BILL_TO_SITE', l_billto_site, false);
774 fnd_message.set_token('CM_REASON', l_cm_reason, false);
775 fnd_message.set_token('ADJ_REASON', l_adj_reason, false);
776 fnd_message.set_token('SETTLEMENT_METHOD', l_payment_method, false);
777
778 -- Lable for the token JUSTIFICATION_NOTES now reads as Comments
779
780 fnd_message.set_token('JUSTIFICATION_NOTES', l_note, false);
781 fnd_message.set_token('APPROVER_NAME', l_pre_approver, false);
782
783 -- fnd_message.set_token('DATE', TO_CHAR(SYSDATE, 'MM-DD-YYYY'), false);
784 -- fnd_message.set_token('TIME', TO_CHAR(SYSDATE, 'HH24:MI'), false);
785 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
786
787 l_body := fnd_message.get;
788
789 /*
790 -- l_note := wf_engine.getitemattrtext(
791 -- itemtype => l_item_type
792 -- ,itemkey => l_item_key
793 -- ,aname => 'NOTE');
794 --
795 --
796 -- l_forwarder :=
797 -- wf_engine.getitemattrtext(
798 -- itemtype => l_item_type
799 -- ,itemkey => l_item_key
800 -- ,aname => 'AMS_FORWARD_FROM_USERNAME');
801 */
802
803 -- IF (display_type = 'text/plain') THEN
804 -- l_appreq_notification := l_subject || FND_GLOBAL.LOCAL_CHR(10) || l_body;
805
806 l_appreq_notification := l_body;
807 document := document || l_appreq_notification;
808 document_type := 'text/plain';
809 RETURN;
810
811 -- END IF;
812
813 /*
814 -- IF (display_type = 'text/html') THEN
815 -- l_appreq_notification := l_string ||
816 -- FND_GLOBAL.LOCAL_CHR(10) ||
817 -- l_string1 ||
818 -- FND_GLOBAL.LOCAL_CHR(10) ||
819 -- l_string2;
820 -- document := document||l_appreq_notification;
821 -- document_type := 'text/html';
822 -- RETURN;
823 -- END IF;
824 */
825
826 EXCEPTION
827 WHEN OTHERS THEN
828 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
829 , 'NOTIFY_APPROVAL_REQUIRED'
830 , l_item_type
831 , l_item_key
832 );
833 RAISE;
834 END notify_approval_required;
835
836
837 --------------------------------------------------------------------------
838 -- PROCEDURE
839 -- notify_appr_req_reminder
840 --
841 -- PURPOSE
842 -- Generate the Rejection Document for display in messages, either text or html
843 --
844 -- IN
845 -- document_id - Item Key
846 -- display_type - either 'text/plain' or 'text/html'
847 -- document - document buffer
848 -- document_type - type of document buffer created, either 'text/plain'
849 -- or 'text/html'
850 -- OUT
851 --
852 -- USED BY
853 -- Oracle MArketing Generic Apporval
854 --
855 -- HISTORY
856 -- 04/25/2001 Prashanth Nerella CREATION
857 -- 05/29/2001 Michelle Chang MODIFIED
858 -------------------------------------------------------------------------------
859 PROCEDURE notify_appr_req_reminder(
860 document_id IN VARCHAR2
861 ,display_type IN VARCHAR2
862 ,document IN OUT NOCOPY VARCHAR2
863 ,document_type IN OUT NOCOPY VARCHAR2
864 )
865 IS
866 l_api_name VARCHAR2(100) := g_pkg_name || 'notify_appr_req_reminder';
867 l_hyphen_pos1 NUMBER;
868 l_apprem_notification VARCHAR2(10000);
869 l_activity_type VARCHAR2(30);
870 l_item_type VARCHAR2(30);
871 l_item_key VARCHAR2(30);
872 l_approval_type VARCHAR2(30);
873 l_approver VARCHAR2(200);
874 l_note VARCHAR2(3000);
875 l_approved_amt NUMBER;
876 l_requested_amt NUMBER;
877 l_forwarder VARCHAR2(30);
878 l_subject VARCHAR2(500);
879 l_body VARCHAR2(3000);
880 l_approver VARCHAR2(200);
881 l_requester VARCHAR2(30);
882
883 l_claim_id NUMBER;
884 l_claim_number VARCHAR2(30);
885 l_account_number VARCHAR2(30);
886 l_account_name VARCHAR2(360);
887 l_currency VARCHAR2(80);
888 l_amount_settled NUMBER;
889 l_claim_date DATE;
890 l_due_date DATE;
891 l_claim_type VARCHAR2(30);
892 l_billto_site VARCHAR2(40);
893 l_cm_reason VARCHAR2(80);
894 l_adj_reason VARCHAR2(80);
895 l_payment_method VARCHAR2(80);
896 l_claim_source VARCHAR2(30);
897 l_offer_req VARCHAR2(2000);
898
899 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
900 SELECT c.claim_number
901 , a.account_number
902 , hp.party_name
903 , curr.name
904 , c.amount_settled
905 , c.claim_date
906 , c.due_date
907 , ct.name
908 , hc.location
909 , rlk_cm.meaning
910 , rlk_adj.meaning
911 , lk_pm.meaning
912 , 'resource_name'
913 FROM ozf_claims_all c
914 , hz_cust_accounts a
915 , hz_parties hp
916 , ozf_claim_types_all_vl ct
917 , hz_cust_site_uses_all hc
918 , ozf_reason_codes_all_b rc
919 , ar_lookups rlk_cm
920 , ar_lookups rlk_adj
921 , ozf_lookups lk_pm
922 , fnd_currencies_vl curr
923 WHERE c.claim_id = p_claim_id
924 AND c.cust_account_id = a.cust_account_id
925 AND a.party_id = hp.party_id
926 AND c.currency_code = curr.currency_code
927 AND c.claim_type_id = ct.claim_type_id
928 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
929 AND c.reason_code_id = rc.reason_code_id
930 AND c.payment_method = lk_pm.lookup_code
931 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
932 AND rc.reason_code = rlk_cm.lookup_code(+)
933 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
934 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
935 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
936
937 BEGIN
938 IF OZF_DEBUG_HIGH_ON THEN
939 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
940 END IF;
941 document_type := 'text/plain';
942 -- parse document_id for the ':' dividing item type name from item key value
943 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
944 -- release 2.5 version of this demo
945 l_hyphen_pos1 := INSTR(document_id, ':');
946 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
947 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
948
949 l_activity_type := wf_engine.getitemattrtext(
950 itemtype => l_item_type
951 ,itemkey => l_item_key
952 ,aname => 'AMS_ACTIVITY_TYPE'
953 );
954
955 l_claim_id := wf_engine.getitemattrtext(
956 itemtype => l_item_type
957 ,itemkey => l_item_key
958 ,aname => 'AMS_ACTIVITY_ID'
959 );
960
961 l_requested_amt := wf_engine.getitemattrtext(
962 itemtype => l_item_type
963 ,itemkey => l_item_key
964 ,aname => 'AMS_REQUESTED_AMOUNT'
965 );
966
967 l_requester := wf_engine.getitemattrtext(
968 itemtype => l_item_type
969 ,itemkey => l_item_key
970 ,aname => 'AMS_REQUESTER'
971 );
972
973 l_approval_type := wf_engine.GetItemAttrText(
974 itemtype => l_item_type
975 ,itemkey => l_item_key
976 ,aname => 'AMS_APPROVAL_TYPE'
977 );
978
979 OPEN c_claim_rec(l_claim_id);
980 FETCH c_claim_rec INTO l_claim_number
981 , l_account_number
982 , l_account_name
983 , l_currency
984 , l_amount_settled
985 , l_claim_date
986 , l_due_date
987 , l_claim_type
988 , l_billto_site
989 , l_cm_reason
990 , l_adj_reason
991 , l_payment_method
992 , l_claim_source;
993 CLOSE c_claim_rec;
994
995 IF l_approval_type = 'PERFORMANCE' THEN
996 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_REM_SUBJ');
997 ELSIF l_approval_type = 'EARNING' THEN
998 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_REM_SUBJ');
999 ELSE
1000 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPR_REM_SUBJ');
1001 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1002 fnd_message.set_token('AMOUNT', l_requested_amt, false);
1003 END IF;
1004 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1005 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1006 l_subject := fnd_message.get;
1007
1008 wf_engine.setitemattrtext(
1009 itemtype => l_item_type
1010 ,itemkey => l_item_key
1011 ,aname => 'APP_SUBJECT'
1012 ,avalue => l_subject
1013 );
1014
1015 l_note := wf_engine.getitemattrtext(
1016 itemtype => l_item_type
1017 ,itemkey => l_item_key
1018 ,aname => 'AMS_PREV_APPROVER_NOTE'
1019 );
1020
1021 IF l_approval_type = 'PERFORMANCE' THEN
1022 get_offer_perf_req(l_claim_id, l_offer_req);
1023 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_REM_INFO');
1024 fnd_message.set_token('OFFR_PERF_REQ', l_offer_req, false);
1025 ELSIF l_approval_type = 'EARNING' THEN
1026 get_offer_earn_req(l_claim_id, l_offer_req);
1027 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_REM_INFO');
1028 fnd_message.set_token('OFFR_EARN_REQ', l_offer_req, false);
1029 ELSE
1030 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPR_REM_INFO');
1031 END IF;
1032 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1033 fnd_message.set_token('CLAIM_TYPE', l_claim_type, false);
1034 fnd_message.set_token('CLAIM_DATE', l_claim_date, false);
1035 fnd_message.set_token('DUE_DATE', l_due_date, false);
1036 fnd_message.set_token('CUSTOMER_NAME', l_account_name, false);
1037 fnd_message.set_token('CUST_ACCT_NUMBER', l_account_number, false);
1038 fnd_message.set_token('BILL_TO_SITE', l_billto_site, false);
1039 fnd_message.set_token('CM_REASON', l_cm_reason, false);
1040 fnd_message.set_token('ADJ_REASON', l_adj_reason, false);
1041 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1042 fnd_message.set_token('AMOUNT', l_requested_amt, false);
1043 fnd_message.set_token('SETTLEMENT_METHOD', l_payment_method, false);
1044 fnd_message.set_token('JUSTIFICATION_NOTES', l_note, false);
1045 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
1046 l_body := fnd_message.get;
1047 /*
1048 l_note := wf_engine.getitemattrtext(
1049 itemtype => l_item_type
1050 ,itemkey => l_item_key
1051 ,aname => 'NOTE');
1052
1053
1054 l_forwarder :=
1055 wf_engine.getitemattrtext(
1056 itemtype => l_item_type
1057 ,itemkey => l_item_key
1058 ,aname => 'OZF_FORWARD_FROM_USERNAME');
1059 */
1060 -- IF (display_type = 'text/plain') THEN
1061 -- l_apprem_notification := l_subject || FND_GLOBAL.LOCAL_CHR(10) || l_body;
1062 l_apprem_notification := l_body;
1063 document := document || l_apprem_notification;
1064 document_type := 'text/plain';
1065 RETURN;
1066 -- END IF;
1067
1068 /*
1069 IF (display_type = 'text/html') THEN
1070 l_appreq_notification := l_string ||
1071 FND_GLOBAL.LOCAL_CHR(10) ||
1072 l_string1 ||
1073 FND_GLOBAL.LOCAL_CHR(10) ||
1074 l_string2;
1075 document := document||l_appreq_notification;
1076 document_type := 'text/html';
1077 RETURN;
1078 END IF;
1079 */
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
1084 , 'NOTIFY_APPR_REQ_REMINDER'
1085 , l_item_type
1086 , l_item_key
1087 );
1088 RAISE;
1089 END notify_appr_req_reminder;
1090
1091
1092 --------------------------------------------------------------------------
1093 -- PROCEDURE
1094 -- Notify_requestor_of Approval
1095 --
1096 -- PURPOSE
1097 -- Generate the Approval Document for display in messages, either text or html
1098 --
1099 -- IN
1100 -- document_id - Item Key
1101 -- display_type - either 'text/plain' or 'text/html'
1102 -- document - document buffer
1103 -- document_type - type of document buffer created, either 'text/plain'
1104 -- or 'text/html'
1105 -- OUT
1106 --
1107 -- USED BY
1108 -- Oracle MArketing Generic Apporval
1109 --
1110 -- HISTORY
1111 -- 04/25/2001 Prashanth Nerella CREATION
1112 -- 05/30/2001 MICHELLE CHANG MODIFIED
1113 ----------------------------------------------------------------------------
1114 PROCEDURE notify_requestor_of_approval(
1115 document_id IN VARCHAR2
1116 ,display_type IN VARCHAR2
1117 ,document IN OUT NOCOPY VARCHAR2
1118 ,document_type IN OUT NOCOPY VARCHAR2
1119 )
1120 IS
1121 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_of_approval';
1122 l_hyphen_pos1 NUMBER;
1123 l_appr_notification VARCHAR2(10000);
1124 l_activity_type VARCHAR2(30);
1125 l_item_type VARCHAR2(30);
1126 l_item_key VARCHAR2(30);
1127 l_approval_type VARCHAR2(30);
1128 l_approver VARCHAR2(200);
1129 l_note VARCHAR2(3000);
1130 l_approved_amt NUMBER;
1131 l_subject VARCHAR2(500);
1132 l_body VARCHAR2(3000);
1133 l_requester VARCHAR2(30);
1134 l_requested_amt NUMBER;
1135
1136 l_claim_id NUMBER;
1137 l_claim_number VARCHAR2(30);
1138 l_account_number VARCHAR2(30);
1139 l_account_name VARCHAR2(360);
1140 l_currency VARCHAR2(80);
1141 l_amount_settled NUMBER;
1142 l_claim_date DATE;
1143 l_due_date DATE;
1144 l_claim_type VARCHAR2(30);
1145 l_billto_site VARCHAR2(40);
1146 l_cm_reason VARCHAR2(80);
1147 l_adj_reason VARCHAR2(80);
1148 l_payment_method VARCHAR2(80);
1149 l_claim_source VARCHAR2(30);
1150 l_offer_req VARCHAR2(2000);
1151
1152 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
1153 SELECT c.claim_number
1154 , a.account_number
1155 , hp.party_name
1156 , curr.name
1157 , c.amount_settled
1158 , c.claim_date
1159 , c.due_date
1160 , ct.name
1161 , hc.location
1162 , rlk_cm.meaning
1163 , rlk_adj.meaning
1164 , lk_pm.meaning
1165 , 'resource_name'
1166 FROM ozf_claims_all c
1167 , hz_cust_accounts a
1168 , hz_parties hp
1169 , ozf_claim_types_all_vl ct
1170 , hz_cust_site_uses_all hc
1171 , ozf_reason_codes_all_b rc
1172 , ar_lookups rlk_cm
1173 , ar_lookups rlk_adj
1174 , ozf_lookups lk_pm
1175 , fnd_currencies_vl curr
1176 WHERE c.claim_id = p_claim_id
1177 AND c.cust_account_id = a.cust_account_id
1178 ANd a.party_id = hp.party_id
1179 AND c.currency_code = curr.currency_code
1180 AND c.claim_type_id = ct.claim_type_id
1181 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
1182 AND c.reason_code_id = rc.reason_code_id
1183 AND c.payment_method = lk_pm.lookup_code
1184 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
1185 AND rc.reason_code = rlk_cm.lookup_code(+)
1186 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
1187 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
1188 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
1189
1190 BEGIN
1191 IF OZF_DEBUG_HIGH_ON THEN
1192 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1193 END IF;
1194 document_type := 'text/plain';
1195 -- parse document_id for the ':' dividing item type name from item key value
1196 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1197 -- release 2.5 version of this demo
1198 l_hyphen_pos1 := INSTR(document_id, ':');
1199 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1200 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1201
1202 l_activity_type := wf_engine.getitemattrtext(
1203 itemtype => l_item_type
1204 ,itemkey => l_item_key
1205 ,aname => 'AMS_ACTIVITY_TYPE'
1206 );
1207
1208 l_claim_id := wf_engine.getitemattrtext(
1209 itemtype => l_item_type
1210 ,itemkey => l_item_key
1211 ,aname => 'AMS_ACTIVITY_ID'
1212 );
1213
1214 l_approved_amt := wf_engine.getitemattrtext(
1215 itemtype => l_item_type
1216 ,itemkey => l_item_key
1217 ,aname => 'AMS_REQUESTED_AMOUNT'
1218 );
1219
1220 l_note := wf_engine.getitemattrtext(
1221 itemtype => l_item_type
1222 ,itemkey => l_item_key
1223 ,aname => 'APPROVAL_NOTE'
1224 );
1225
1226 l_approver := wf_engine.getitemattrtext(
1227 itemtype => l_item_type
1228 ,itemkey => l_item_key
1229 ,aname => 'AMS_APPROVER_DISPLAY_NAME'
1230 );
1231
1232 l_approval_type := wf_engine.GetItemAttrText(
1233 itemtype => l_item_type
1234 ,itemkey => l_item_key
1235 ,aname => 'AMS_APPROVAL_TYPE'
1236 );
1237
1238 OPEN c_claim_rec(l_claim_id);
1239 FETCH c_claim_rec INTO l_claim_number
1240 , l_account_number
1241 , l_account_name
1242 , l_currency
1243 , l_amount_settled
1244 , l_claim_date
1245 , l_due_date
1246 , l_claim_type
1247 , l_billto_site
1248 , l_cm_reason
1249 , l_adj_reason
1250 , l_payment_method
1251 , l_claim_source;
1252 CLOSE c_claim_rec;
1253
1254 IF l_approval_type = 'PERFORMANCE' THEN
1255 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPRD_SUBJ');
1256 ELSIF l_approval_type = 'EARNING' THEN
1257 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPRD_SUBJ');
1258 ELSE
1259 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPROVED_SUBJ');
1260 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1261 fnd_message.set_token('AMOUNT', l_approved_amt, false);
1262 END IF;
1263 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1264 --fnd_message.set_token('APPROVER', l_approver, false);
1265 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1266 l_subject := fnd_message.get;
1267
1268 wf_engine.setitemattrtext(
1269 itemtype => l_item_type
1270 ,itemkey => l_item_key
1271 ,aname => 'APRV_SUBJECT'
1272 ,avalue => l_subject
1273 );
1274
1275 IF l_approval_type = 'PERFORMANCE' THEN
1276 get_offer_perf_req(l_claim_id, l_offer_req);
1277 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPRD_INFO');
1278 fnd_message.set_token('OFFR_PERF_REQ', l_offer_req, false);
1279 ELSIF l_approval_type = 'EARNING' THEN
1280 get_offer_earn_req(l_claim_id, l_offer_req);
1281 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPRD_INFO');
1282 fnd_message.set_token('OFFR_EARN_REQ', l_offer_req, false);
1283 ELSE
1284 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_APPROVED_INFO');
1285 END IF;
1286 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1287 fnd_message.set_token('AMOUNT', l_approved_amt, false);
1288 fnd_message.set_token('APPROVER_NAME', l_approver, false);
1289 fnd_message.set_token('DATE', TO_CHAR(SYSDATE, 'MM-DD-YYYY'), false);
1290 fnd_message.set_token('TIME', TO_CHAR(SYSDATE, 'HH24:MI'), false);
1291 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1292 fnd_message.set_token('CLAIM_TYPE', l_claim_type, false);
1293 fnd_message.set_token('CLAIM_DATE', l_claim_date, false);
1294 fnd_message.set_token('DUE_DATE', l_due_date, false);
1295 fnd_message.set_token('CUSTOMER_NAME', l_account_name, false);
1296 fnd_message.set_token('CUST_ACCT_NUMBER', l_account_number, false);
1297 fnd_message.set_token('BILL_TO_SITE', l_billto_site, false);
1298 fnd_message.set_token('CM_REASON', l_cm_reason, false);
1299 fnd_message.set_token('ADJ_REASON', l_adj_reason, false);
1300 fnd_message.set_token('SETTLEMENT_METHOD', l_payment_method, false);
1301 fnd_message.set_token('COMMENTS_NOTES', l_note, false);
1302 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
1303 l_body := fnd_message.get;
1304 /*
1305 l_note := wf_engine.getitemattrtext(
1306 itemtype => l_item_type
1307 ,itemkey => l_item_key
1308 ,aname => 'NOTE');
1309
1310
1311 l_forwarder :=
1312 wf_engine.getitemattrtext(
1313 itemtype => l_item_type
1314 ,itemkey => l_item_key
1315 ,aname => 'OZF_FORWARD_FROM_USERNAME');
1316 */
1317 -- IF (display_type = 'text/plain') THEN
1318 -- l_appr_notification := l_subject || FND_GLOBAL.LOCAL_CHR(10) || l_body;
1319 l_appr_notification := l_body;
1320 document := document || l_appr_notification;
1321 document_type := 'text/plain';
1322 RETURN;
1323 -- END IF;
1324
1325 /*
1326 IF (display_type = 'text/html') THEN
1327 l_appreq_notification := l_string ||
1328 FND_GLOBAL.LOCAL_CHR(10) ||
1329 l_string1 ||
1330 FND_GLOBAL.LOCAL_CHR(10) ||
1331 l_string2;
1332 document := document||l_appreq_notification;
1333 document_type := 'text/html';
1334 RETURN;
1335 END IF;
1336 */
1337 EXCEPTION
1338 WHEN OTHERS THEN
1339 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
1340 , 'NOTIFY_REQUESTOR_OF_APPROVAL'
1341 , l_item_type
1342 , l_item_key
1343 );
1344 RAISE;
1345 END notify_requestor_of_approval;
1346
1347 --------------------------------------------------------------------------
1348 -- PROCEDURE
1349 -- Notify_requestor_of rejection
1350 --
1351 -- PURPOSE
1352 -- Generate the Rejection Document for display in messages, either text or html
1353 --
1354 -- IN
1355 -- document_id - Item Key
1356 -- display_type - either 'text/plain' or 'text/html'
1357 -- document - document buffer
1358 -- document_type - type of document buffer created, either 'text/plain'
1359 -- or 'text/html'
1360 -- OUT
1361 --
1362 -- USED BY
1363 -- Oracle MArketing Generic Apporval
1364 --
1365 -- HISTORY
1366 -- 04/25/2001 Prashanth Nerella CREATION
1367 -- 05/30/2001 MICHELLE CHANG MODIFIED
1368 -------------------------------------------------------------------------------
1369 PROCEDURE notify_requestor_of_rejection(
1370 document_id IN VARCHAR2
1371 ,display_type IN VARCHAR2
1372 ,document IN OUT NOCOPY VARCHAR2
1373 ,document_type IN OUT NOCOPY VARCHAR2
1374 )
1375 IS
1376 l_api_name VARCHAR2(100) := g_pkg_name || 'Notify_Requestor_of_rejection';
1377 l_hyphen_pos1 NUMBER;
1378 l_rej_notification VARCHAR2(10000);
1379 l_activity_type VARCHAR2(30);
1380 l_item_type VARCHAR2(30);
1381 l_item_key VARCHAR2(30);
1382 l_approval_type VARCHAR2(30);
1383 l_approver VARCHAR2(200);
1384 l_note VARCHAR2(3000);
1385 l_approved_amt NUMBER;
1386 l_subject VARCHAR2(500);
1387 l_body VARCHAR2(3000);
1388 l_requester VARCHAR2(30);
1389 l_requested_amt NUMBER;
1390
1391 l_claim_id NUMBER;
1392 l_claim_number VARCHAR2(30);
1393 l_account_number VARCHAR2(30);
1394 l_account_name VARCHAR2(360);
1395 l_currency VARCHAR2(80);
1396 l_amount_settled NUMBER;
1397 l_claim_date DATE;
1398 l_due_date DATE;
1399 l_claim_type VARCHAR2(30);
1400 l_billto_site VARCHAR2(40);
1401 l_cm_reason VARCHAR2(80);
1402 l_adj_reason VARCHAR2(80);
1403 l_payment_method VARCHAR2(80);
1404 l_claim_source VARCHAR2(30);
1405 l_offer_req VARCHAR2(2000);
1406
1407 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
1408 SELECT c.claim_number
1409 , a.account_number
1410 , hp.party_name
1411 , curr.name
1412 , c.amount_settled
1413 , c.claim_date
1414 , c.due_date
1415 , ct.name
1416 , hc.location
1417 , rlk_cm.meaning
1418 , rlk_adj.meaning
1419 , lk_pm.meaning
1420 , 'resource_name'
1421 FROM ozf_claims_all c
1422 , hz_cust_accounts a
1423 , hz_parties hp
1424 , ozf_claim_types_all_vl ct
1425 , hz_cust_site_uses_all hc
1426 , ozf_reason_codes_all_b rc
1427 , ar_lookups rlk_cm
1428 , ar_lookups rlk_adj
1429 , ozf_lookups lk_pm
1430 , fnd_currencies_vl curr
1431 WHERE c.claim_id = p_claim_id
1432 AND c.cust_account_id = a.cust_account_id
1433 AND a.party_id = hp.party_id
1434 AND c.currency_code = curr.currency_code
1435 AND c.claim_type_id = ct.claim_type_id
1436 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
1437 AND c.reason_code_id = rc.reason_code_id
1438 AND c.payment_method = lk_pm.lookup_code
1439 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
1440 AND rc.reason_code = rlk_cm.lookup_code(+)
1441 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
1442 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
1443 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
1444
1445 BEGIN
1446 IF OZF_DEBUG_HIGH_ON THEN
1447 ozf_utility_pvt.debug_message(l_api_name || 'Entering' || 'document id ' || document_id);
1448 END IF;
1449 document_type := 'text/plain';
1450 -- parse document_id for the ':' dividing item type name from item key value
1451 -- document_id value will take the form <ITEMTYPE>:<ITEMKEY> starting with
1452 -- release 2.5 version of this demo
1453 l_hyphen_pos1 := INSTR(document_id, ':');
1454 l_item_type := SUBSTR(document_id, 1, l_hyphen_pos1 - 1);
1455 l_item_key := SUBSTR(document_id, l_hyphen_pos1 + 1);
1456
1457 l_activity_type := wf_engine.getitemattrtext(
1458 itemtype => l_item_type
1459 ,itemkey => l_item_key
1460 ,aname => 'AMS_ACTIVITY_TYPE'
1461 );
1462
1463 l_claim_id := wf_engine.getitemattrtext(
1464 itemtype => l_item_type
1465 ,itemkey => l_item_key
1466 ,aname => 'AMS_ACTIVITY_ID'
1467 );
1468
1469 l_approved_amt := wf_engine.getitemattrtext(
1470 itemtype => l_item_type
1471 ,itemkey => l_item_key
1472 ,aname => 'AMS_AMOUNT'
1473 );
1474
1475 l_note := wf_engine.getitemattrtext(
1476 itemtype => l_item_type
1477 ,itemkey => l_item_key
1478 ,aname => 'APPROVAL_NOTE'
1479 );
1480
1481 l_approver := wf_engine.getitemattrtext(
1482 itemtype => l_item_type
1483 ,itemkey => l_item_key
1484 ,aname => 'AMS_APPROVER_DISPLAY_NAME'
1485 );
1486
1487 l_approval_type := wf_engine.GetItemAttrText(
1488 itemtype => l_item_type
1489 ,itemkey => l_item_key
1490 ,aname => 'AMS_APPROVAL_TYPE'
1491 );
1492
1493 OPEN c_claim_rec(l_claim_id);
1494 FETCH c_claim_rec INTO l_claim_number
1495 , l_account_number
1496 , l_account_name
1497 , l_currency
1498 , l_amount_settled
1499 , l_claim_date
1500 , l_due_date
1501 , l_claim_type
1502 , l_billto_site
1503 , l_cm_reason
1504 , l_adj_reason
1505 , l_payment_method
1506 , l_claim_source;
1507 CLOSE c_claim_rec;
1508
1509 IF l_approval_type = 'PERFORMANCE' THEN
1510 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_REJ_SUBJ');
1511 ELSIF l_approval_type = 'EARNING' THEN
1512 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_REJ_SUBJ');
1513 ELSE
1514 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_REJECTED_SUBJ');
1515 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1516 fnd_message.set_token('AMOUNT', l_amount_settled, false);
1517 END IF;
1518 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1519 --fnd_message.set_token('APPROVER', l_approver, false);
1520 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1521 l_subject := fnd_message.get;
1522
1523 wf_engine.setitemattrtext(
1524 itemtype => l_item_type
1525 ,itemkey => l_item_key
1526 ,aname => 'REJECT_SUBJECT'
1527 ,avalue => l_subject
1528 );
1529
1530 IF l_approval_type = 'PERFORMANCE' THEN
1531 get_offer_perf_req(l_claim_id, l_offer_req);
1532 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_PERF_REJ_INFO');
1533 fnd_message.set_token('OFFR_PERF_REQ', l_offer_req, false);
1534 ELSIF l_approval_type = 'EARNING' THEN
1535 get_offer_earn_req(l_claim_id, l_offer_req);
1536 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_EARN_REJ_INFO');
1537 fnd_message.set_token('OFFR_EARN_REQ', l_offer_req, false);
1538 ELSE
1539 fnd_message.set_name('OZF', 'OZF_CLAIM_NTF_REJECTED_INFO');
1540 END IF;
1541 fnd_message.set_token('CURRENCY_CODE', l_currency, false);
1542 fnd_message.set_token('AMOUNT', l_amount_settled, false);
1543 fnd_message.set_token('APPROVER_NAME', l_approver, false);
1544 fnd_message.set_token('DATE', TO_CHAR(SYSDATE, 'MM-DD-YYYY'), false);
1545 fnd_message.set_token('TIME', TO_CHAR(SYSDATE, 'HH24:MI'), false);
1546 fnd_message.set_token('CLAIM_NUMBER', l_claim_number, false);
1547 fnd_message.set_token('CLAIM_TYPE', l_claim_type, false);
1548 fnd_message.set_token('CLAIM_DATE', l_claim_date, false);
1549 fnd_message.set_token('DUE_DATE', l_due_date, false);
1550 fnd_message.set_token('CUSTOMER_NAME', l_account_name, false);
1551 fnd_message.set_token('CUST_ACCT_NUMBER', l_account_number, false);
1552 fnd_message.set_token('BILL_TO_SITE', l_billto_site, false);
1553 fnd_message.set_token('CM_REASON', l_cm_reason, false);
1554 fnd_message.set_token('ADJ_REASON', l_adj_reason, false);
1555 fnd_message.set_token('SETTLEMENT_METHOD', l_payment_method, false);
1556 fnd_message.set_token('JUSTIFICATION_NOTES', '', false);
1557 fnd_message.set_token('COMMENTS_NOTES', l_note, false);
1558 -- l_string1 := Substr(FND_MESSAGE.Get,1,2500);
1559 l_body := fnd_message.get;
1560 /*
1561 l_note := wf_engine.getitemattrtext(
1562 itemtype => l_item_type
1563 ,itemkey => l_item_key
1564 ,aname => 'NOTE');
1565
1566
1567 l_forwarder :=
1568 wf_engine.getitemattrtext(
1569 itemtype => l_item_type
1570 ,itemkey => l_item_key
1571 ,aname => 'AMS_FORWARD_FROM_USERNAME');
1572 */
1573 -- IF (display_type = 'text/plain') THEN
1574 -- l_rej_notification := l_subject || FND_GLOBAL.LOCAL_CHR(10) || l_body;
1575 l_rej_notification := l_body;
1576 document := document || l_rej_notification;
1577 document_type := 'text/plain';
1578 RETURN;
1579 -- END IF;
1580
1581 /*
1582 IF (display_type = 'text/html') THEN
1583 l_appreq_notification := l_string ||
1584 FND_GLOBAL.LOCAL_CHR(10) ||
1585 l_string1 ||
1586 FND_GLOBAL.LOCAL_CHR(10) ||
1587 l_string2;
1588 document := document||l_appreq_notification;
1589 document_type := 'text/html';
1590 RETURN;
1591 END IF;
1592 */
1593 EXCEPTION
1594 WHEN OTHERS THEN
1595 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
1596 , 'NOTIFY_REQUESTOR_OF_REJECTION'
1597 , l_item_type
1598 , l_item_key
1599 );
1600 RAISE;
1601 END notify_requestor_of_rejection;
1602
1603
1604 ---------------------------------------------------------------------
1605 -- PROCEDURE
1606 -- Set_claim_Activity_details
1607 --
1608 -- PURPOSE
1609 -- This Procedure will set all the item attribute details
1610 --
1611 -- IN
1612 --
1613 -- OUT
1614 --
1615 -- Used By Activities
1616 --
1617 -- NOTES
1618 --
1619 -- HISTORY
1620 -- 04/25/2001 Prashanth Nerella CREATION
1621 -- 05/30/2001 MICHELLE CHANG MODIFIED
1622 -------------------------------------------------------------------------------
1623 PROCEDURE set_claim_activity_details(
1624 itemtype IN VARCHAR2
1625 ,itemkey IN VARCHAR2
1626 ,actid IN NUMBER
1627 ,funcmode IN VARCHAR2
1628 ,resultout OUT NOCOPY VARCHAR2
1629 )
1630 IS
1631 l_activity_id NUMBER;
1632 l_activity_type VARCHAR2(30) := 'CLAM';
1633 l_approval_type VARCHAR2(30) := 'CLAIM';
1634 l_object_details AMS_GEN_APPROVAL_PVT.objrectyp;
1635 l_approval_detail_id NUMBER;
1636 l_approver_seq NUMBER;
1637 l_return_status VARCHAR2(1);
1638 l_msg_count NUMBER;
1639 l_msg_data VARCHAR2(4000);
1640 l_error_msg VARCHAR2(4000);
1641 l_orig_stat_id NUMBER;
1642 x_resource_id NUMBER;
1643 l_full_name VARCHAR2(60);
1644
1645 --l_approval_type VARCHAR2(30);
1646 l_approver VARCHAR2(200);
1647 l_note VARCHAR2(3000);
1648 l_subject VARCHAR2(500);
1649 l_requester VARCHAR2(30);
1650 l_requested_amt NUMBER;
1651 l_approved_amt NUMBER;
1652
1653 l_claim_id NUMBER;
1654 l_claim_number VARCHAR2(30);
1655 l_account_number VARCHAR2(30);
1656 l_account_name VARCHAR2(360);
1657 l_currency VARCHAR2(80);
1658 l_amount_settled NUMBER;
1659 l_claim_date DATE;
1660 l_due_date DATE;
1661 l_claim_type VARCHAR2(30);
1662 l_billto_site VARCHAR2(40);
1663 l_cm_reason VARCHAR2(80);
1664 l_adj_reason VARCHAR2(80);
1665 l_payment_method VARCHAR2(80);
1666 l_claim_source VARCHAR2(30);
1667 l_comments VARCHAR2(2000);
1668 l_object_meaning VARCHAR2(240);
1669 l_requester_id NUMBER;
1670
1671 CURSOR c_claim_obj(p_act_id IN NUMBER) IS
1672 SELECT claim_number
1673 , custom_setup_id
1674 , amount_settled
1675 , org_id
1676 , to_char(claim_type_id) -- 'CLAM' changed by slkrishn
1677 , to_char(reason_code_id) -- added by slkrishn for reason(priority)
1678 , claim_date
1679 , due_date
1680 , owner_id
1681 , currency_code
1682 FROM ozf_claims_all
1683 WHERE claim_id = p_act_id;
1684
1685 CURSOR c_claim_rec(p_claim_id IN NUMBER) IS
1686 SELECT c.claim_number
1687 , a.account_number
1688 , hp.party_name
1689 , curr.name
1690 , c.amount_settled
1691 , c.claim_date
1692 , c.due_date
1693 , ct.name
1694 , hc.location
1695 , rlk_cm.meaning
1696 , rlk_adj.meaning
1697 , lk_pm.meaning
1698 , 'resource_name'
1699 , c.comments
1700 FROM ozf_claims_all c
1701 , hz_cust_accounts a
1702 , hz_parties hp
1703 , ozf_claim_types_all_vl ct
1704 , hz_cust_site_uses_all hc
1705 , ozf_reason_codes_all_b rc
1706 , ar_lookups rlk_cm
1707 , ar_lookups rlk_adj
1708 , ozf_lookups lk_pm
1709 , fnd_currencies_vl curr
1710 WHERE c.claim_id = p_claim_id
1711 AND c.cust_account_id = a.cust_account_id
1712 AND a.party_id = hp.party_id
1713 AND c.currency_code = curr.currency_code
1714 AND c.claim_type_id = ct.claim_type_id
1715 AND c.cust_billto_acct_site_id = hc.site_use_id(+)
1716 AND c.reason_code_id = rc.reason_code_id
1717 AND c.payment_method = lk_pm.lookup_code
1718 /* [BEGIN OF FIXING BUG2474662 22-JUL-2002]: overpayment is not been selected. */
1719 --AND lk_pm.lookup_type = decode(c.claim_class, 'CLAIM', 'OZF_CLAIM_PAYMENT_METHOD', 'OZF_DEDUCTION_PAYMENT_METHOD');
1720 AND lk_pm.lookup_type = 'OZF_PAYMENT_METHOD'
1721 /* [END OF FIXING BUG2474662] */
1722 AND rc.reason_code = rlk_cm.lookup_code(+)
1723 AND rlk_cm.lookup_type(+) = 'CREDIT_MEMO_REASON'
1724 AND rc.adjustment_reason_code = rlk_adj.lookup_code(+)
1725 AND rlk_adj.lookup_type(+) = 'ADJUST_REASON';
1726
1727 BEGIN
1728 FND_MSG_PUB.initialize;
1729
1730 l_activity_id := WF_ENGINE.getitemattrnumber(
1731 itemtype => itemtype
1732 ,itemkey => itemkey
1733 ,aname => 'AMS_ACTIVITY_ID'
1734 );
1735
1736 l_approval_type := wf_engine.GetItemAttrText(
1737 itemtype => itemtype
1738 ,itemkey => itemkey
1739 ,aname => 'AMS_APPROVAL_TYPE'
1740 );
1741
1742 l_requester_id := wf_engine.getitemattrnumber(
1743 itemtype => itemtype
1744 ,itemkey => itemkey
1745 ,aname => 'AMS_REQUESTER_ID'
1746 );
1747
1748
1749
1750
1751 OPEN c_claim_obj(l_activity_id);
1752 FETCH c_claim_obj INTO l_object_details.name
1753 , l_object_details.setup_type_id
1754 , l_object_details.total_header_amount
1755 , l_object_details.org_id
1756 , l_object_details.object_type
1757 , l_object_details.priority
1758 , l_object_details.start_date
1759 , l_object_details.end_date
1760 , l_object_details.owner_id
1761 , l_object_details.currency;
1762 CLOSE c_claim_obj;
1763
1764 IF (funcmode = 'RUN') THEN
1765 BEGIN
1766 UPDATE ozf_claims_all
1767 SET appr_wf_item_key = itemkey
1768 WHERE claim_id = l_activity_id;
1769
1770 IF ( l_approval_type = 'CLAIM' ) THEN
1771 make_history_data (
1772 p_activity_id => l_activity_id,
1773 p_requester_id => l_requester_id,
1774 x_return_status => l_return_status
1775 );
1776
1777 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1778 RAISE FND_API.g_exc_unexpected_error;
1779 END IF;
1780 END IF;
1781
1782 EXCEPTION
1783 WHEN OTHERS THEN
1784 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1785 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_UPD_CLAM_ERR');
1786 FND_MSG_PUB.add;
1787 END IF;
1788 IF OZF_DEBUG_LOW_ON THEN
1789 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
1790 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
1791 FND_MSG_PUB.Add;
1792 END IF;
1793 RAISE FND_API.g_exc_unexpected_error;
1794 END;
1795
1796 AMS_GEN_APPROVAL_PVT.get_approval_details(
1797 p_activity_id => l_activity_id
1798 ,p_activity_type => l_activity_type
1799 ,p_approval_type => l_approval_type
1800 ,p_object_details => l_object_details
1801 ,x_approval_detail_id => l_approval_detail_id
1802 ,x_approver_seq => l_approver_seq
1803 ,x_return_status => l_return_status
1804 );
1805
1806 IF l_return_status = fnd_api.g_ret_sts_success THEN
1807 WF_ENGINE.setitemattrnumber(
1808 itemtype => itemtype
1809 ,itemkey => itemkey
1810 ,aname => 'AMS_APPROVAL_DETAIL_ID'
1811 ,avalue => l_approval_detail_id
1812 );
1813 WF_ENGINE.setitemattrnumber(
1814 itemtype => itemtype
1815 ,itemkey => itemkey
1816 ,aname => 'AMS_APPROVER_SEQ'
1817 ,avalue => l_approver_seq
1818 );
1819 WF_ENGINE.setitemattrnumber(
1820 itemtype => itemtype
1821 ,itemkey => itemkey
1822 ,aname => 'AMS_REQUESTED_AMOUNT'
1823 ,avalue => l_object_details.total_header_amount
1824 );
1825
1826 -- [BEGIN OF FIX BUG2352621 07/03/2002]
1827 WF_ENGINE.setitemattrtext(
1828 itemtype => itemtype
1829 ,itemkey => itemkey
1830 ,aname => 'AMS_APPROVAL_OBJECT_NAME'
1831 ,avalue => l_object_details.name
1832 );
1833
1834 l_object_meaning := OZF_UTILITY_PVT.get_lookup_meaning('AMS_SYS_ARC_QUALIFIER', 'CLAM');
1835
1836 WF_ENGINE.setitemattrtext(
1837 itemtype => itemtype
1838 ,itemkey => itemkey
1839 ,aname => 'AMS_APPROVAL_OBJECT_MEANING'
1840 ,avalue => l_object_meaning
1841 );
1842 -- [END OF FIX BUG2352621 07/03/2002]
1843
1844 -- set all subjects
1845 OPEN c_claim_rec(l_activity_id);
1846 FETCH c_claim_rec INTO l_claim_number
1847 , l_account_number
1848 , l_account_name
1849 , l_currency
1850 , l_amount_settled
1851 , l_claim_date
1852 , l_due_date
1853 , l_claim_type
1854 , l_billto_site
1855 , l_cm_reason
1856 , l_adj_reason
1857 , l_payment_method
1858 , l_claim_source
1859 , l_comments;
1860 CLOSE c_claim_rec;
1861
1862 WF_ENGINE.setitemattrtext(
1863 itemtype => itemtype
1864 ,itemkey => itemkey
1865 ,aname => 'AMS_NOTES_FROM_REQUESTOR'
1866 ,avalue => l_comments
1867 );
1868
1869 IF l_approval_type = 'PERFORMANCE' THEN
1870 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_PERF_FORD_SUBJ');
1871 ELSIF l_approval_type = 'EARNING' THEN
1872 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_EARN_FORD_SUBJ');
1873 ELSE
1874 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_FORWARD_SUBJ');
1875 FND_MESSAGE.set_token('CURRENCY_CODE', l_currency, false);
1876 FND_MESSAGE.set_token('AMOUNT', l_amount_settled, false);
1877 END IF;
1878 FND_MESSAGE.set_token('CLAIM_NUMBER', l_claim_number, false);
1879 --fnd_message.set_token('APPROVER', l_approver, false);
1880 l_subject := FND_MESSAGE.get;
1881
1882 WF_ENGINE.setitemattrtext(
1883 itemtype => itemtype
1884 ,itemkey => itemkey
1885 ,aname => 'FYI_SUBJECT'
1886 ,avalue => l_subject
1887 );
1888
1889 IF l_approval_type = 'PERFORMANCE' THEN
1890 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPR_SUBJ');
1891 ELSIF l_approval_type = 'EARNING' THEN
1892 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPR_SUBJ');
1893 ELSE
1894 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_APPROVAL_SUBJ');
1895 FND_MESSAGE.set_token('CURRENCY_CODE', l_currency, false);
1896 FND_MESSAGE.set_token('AMOUNT', l_amount_settled, false);
1897 END IF;
1898 FND_MESSAGE.set_token('CLAIM_NUMBER', l_claim_number, false);
1899 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1900 l_subject := fnd_message.get;
1901
1902 WF_ENGINE.setitemattrtext(
1903 itemtype => itemtype
1904 ,itemkey => itemkey
1905 ,aname => 'APP_SUBJECT'
1906 ,avalue => l_subject
1907 );
1908
1909 IF l_approval_type = 'PERFORMANCE' THEN
1910 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_PERF_APPRD_SUBJ');
1911 ELSIF l_approval_type = 'EARNING' THEN
1912 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_EARN_APPRD_SUBJ');
1913 ELSE
1914 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_APPROVED_SUBJ');
1915 FND_MESSAGE.set_token('CURRENCY_CODE', l_currency, false);
1916 FND_MESSAGE.set_token('AMOUNT', l_amount_settled, false);
1917 END IF;
1918 FND_MESSAGE.set_token('CLAIM_NUMBER', l_claim_number, false);
1919 --fnd_message.set_token('APPROVER', l_approver, false);
1920 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1921 l_subject := FND_MESSAGE.get;
1922
1923 WF_ENGINE.setitemattrtext(
1924 itemtype => itemtype
1925 ,itemkey => itemkey
1926 ,aname => 'APRV_SUBJECT'
1927 ,avalue => l_subject
1928 );
1929
1930 IF l_approval_type = 'PERFORMANCE' THEN
1931 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_PERF_REJ_SUBJ');
1932 ELSIF l_approval_type = 'EARNING' THEN
1933 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_EARN_REJ_SUBJ');
1934 ELSE
1935 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_NTF_REJECTED_SUBJ');
1936 FND_MESSAGE.set_token('CURRENCY_CODE', l_currency, false);
1937 FND_MESSAGE.set_token('AMOUNT', l_amount_settled, false);
1938 END IF;
1939 FND_MESSAGE.set_token('CLAIM_NUMBER', l_claim_number, false);
1940 --fnd_message.set_token('APPROVER', l_approver, false);
1941 -- l_string := Substr(FND_MESSAGE.Get,1,2500);
1942 l_subject := FND_MESSAGE.get;
1943
1944 WF_ENGINE.setitemattrtext(
1945 itemtype => itemtype
1946 ,itemkey => itemkey
1947 ,aname => 'REJECT_SUBJECT'
1948 ,avalue => l_subject
1949 );
1950
1951 resultout := 'COMPLETE:SUCCESS';
1952 ELSE
1953 FND_MSG_PUB.count_and_get(
1954 p_encoded => fnd_api.g_false
1955 ,p_count => l_msg_count
1956 ,p_data => l_msg_data
1957 );
1958
1959 AMS_GEN_APPROVAL_PVT.handle_err(
1960 p_itemtype => itemtype
1961 ,p_itemkey => itemkey
1962 ,p_msg_count => l_msg_count
1963 ,p_msg_data => l_msg_data
1964 ,p_attr_name => 'AMS_ERROR_MSG'
1965 ,x_error_msg => l_error_msg
1966 );
1967
1968 WF_CORE.context(
1969 'OZF_CLAIM_APPROVAL_PVT'
1970 ,'SET_CLAIM_ACTIVITY_DETAILS'
1971 ,itemtype
1972 ,itemkey
1973 ,actid
1974 ,l_error_msg
1975 );
1976 -- RAISE FND_API.G_EXC_ERROR;
1977 resultout := 'COMPLETE:ERROR';
1978 END IF;
1979 RETURN;
1980 END IF; -- end of RUN mode
1981
1982 --
1983 -- CANCEL mode
1984 --
1985 IF (funcmode = 'CANCEL') THEN
1986 resultout := 'COMPLETE:';
1987 RETURN;
1988 END IF;
1989
1990 --
1991 -- TIMEOUT mode
1992 --
1993 IF (funcmode = 'TIMEOUT') THEN
1994 resultout := 'COMPLETE:';
1995 RETURN;
1996 END IF;
1997 --
1998
1999 EXCEPTION
2000 WHEN fnd_api.g_exc_error THEN
2001 WF_CORE.context(
2002 'OZF_CLAIM_APPROVAL_PVT'
2003 ,'SET_CLAIM_ACTIVITY_DETAILS'
2004 ,itemtype
2005 ,itemkey
2006 ,actid
2007 ,funcmode
2008 ,l_error_msg
2009 );
2010 resultout := 'COMPLETE:ERROR';
2011 RETURN;
2012 --RAISE;
2013 WHEN OTHERS THEN
2014 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2015 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2016 FND_MSG_PUB.Add;
2017
2018 FND_MSG_PUB.count_and_get (
2019 p_encoded => fnd_api.g_false
2020 ,p_count => l_msg_count
2021 ,p_data => l_msg_data
2022 );
2023 AMS_GEN_APPROVAL_PVT.handle_err(
2024 p_itemtype => itemtype
2025 ,p_itemkey => itemkey
2026 ,p_msg_count => l_msg_count
2027 ,p_msg_data => l_msg_data
2028 ,p_attr_name => 'AMS_ERROR_MSG'
2029 ,x_error_msg => l_error_msg
2030 );
2031 WF_CORE.context(
2032 'AMS_GEN_APPROVAL_PVT'
2033 ,'SET_CLAIM_ACTIVITY_DETAILS'
2034 ,itemtype
2035 ,itemkey
2036 ,actid
2037 ,l_error_msg
2038 );
2039 resultout := 'COMPLETE:ERROR';
2040 RETURN;
2041 --RAISE;
2042 END set_claim_activity_details;
2043
2044
2045
2046 ---------------------------------------------------------------------
2047 -- PROCEDURE
2048 -- Start_Approval_Process
2049 --
2050 -- PURPOSE
2051 -- This Procedure will initiate ams gen apporval for earnings and performance.
2052 --
2053 -- IN
2054 --
2055 -- OUT
2056 --
2057 -- Used By Activities
2058 --
2059 -- NOTES
2060 --
2061 -- HISTORY
2062 -- NOV-24-2003 MCHANG CREATION
2063 -------------------------------------------------------------------------------
2064 PROCEDURE Start_Approval_Process(
2065 p_claim_id IN NUMBER
2066 ,p_orig_status_id IN NUMBER
2067 ,p_new_status_id IN NUMBER
2068 ,p_reject_status_id IN NUMBER
2069 ,p_approval_type IN VARCHAR2
2070 ,x_return_status OUT NOCOPY VARCHAR2
2071 ) IS
2072 l_api_version CONSTANT NUMBER := 1.0;
2073 l_api_name CONSTANT VARCHAR2(30) := 'Start_Approval_Process';
2074 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2075 ---
2076 l_object_version_number NUMBER;
2077 l_owner_id NUMBER;
2078 l_comments VARCHAR2(2000);
2079
2080 CURSOR claim_rec_csr(l_claim_id in number) IS
2081 SELECT object_version_number
2082 --, user_status_id
2083 , owner_id
2084 , comments
2085 FROM ozf_claims
2086 WHERE claim_id = l_claim_id;
2087
2088 BEGIN
2089 x_return_status := FND_API.g_ret_sts_success;
2090
2091 OPEN claim_rec_csr(p_claim_id);
2092 FETCH claim_rec_csr INTO l_object_version_number,
2093 --l_new_status_id,
2094 l_owner_id,
2095 l_comments;
2096 CLOSE claim_rec_csr;
2097
2098 l_object_version_number := l_object_version_number + 1;
2099
2100 BEGIN
2101 UPDATE ozf_claims_all
2102 SET OBJECT_VERSION_NUMBER = l_object_version_number
2103 WHERE claim_id = p_claim_id;
2104 EXCEPTION
2105 WHEN OTHERS THEN
2106 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2107 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_UPD_CLAM_ERR');
2108 FND_MSG_PUB.add;
2109 END IF;
2110 IF OZF_DEBUG_LOW_ON THEN
2111 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2112 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2113 FND_MSG_PUB.Add;
2114 END IF;
2115 RAISE FND_API.g_exc_unexpected_error;
2116 END;
2117
2118 ----------------------------
2119 -- Call Approval Workflow --
2120 ----------------------------
2121 -- the approval API would start claim approval process
2122 AMS_GEN_APPROVAL_PVT.StartProcess(
2123 p_activity_type => 'CLAM'
2124 ,p_activity_id => p_claim_id
2125 ,p_approval_type => p_approval_type
2126 ,p_object_version_number => l_object_version_number
2127 ,p_orig_stat_id => p_orig_status_id
2128 ,p_new_stat_id => p_new_status_id
2129 ,p_reject_stat_id => p_reject_status_id
2130 ,p_requester_userid => l_owner_id
2131 ,p_notes_from_requester => l_comments
2132 ,p_workflowprocess => 'AMSGAPP'
2133 ,p_item_type => 'AMSGAPP'
2134 );
2135
2136 EXCEPTION
2137 WHEN OTHERS THEN
2138 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2139 FND_MESSAGE.Set_Token('TEXT',l_full_name||' - '||p_approval_type||' : Error');
2140 FND_MSG_PUB.Add;
2141 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2142
2143 END Start_Approval_Process;
2144
2145
2146 ---------------------------------------------------------------------
2147 -- PROCEDURE
2148 -- update_claim_status
2149 --
2150 -- PURPOSE
2151 -- This Procedure will update the status
2152 --
2153 -- IN
2154 --
2155 -- OUT
2156 --
2157 -- Used By Activities
2158 --
2159 -- NOTES
2160 --
2161 -- HISTORY
2162 -- 04/25/2001 Prashanth Nerella CREATION
2163 -- 05/30/2001 MICHELLE CHANG MODIFIED
2164 -------------------------------------------------------------------------------
2165 PROCEDURE update_claim_status(
2166 itemtype IN VARCHAR2
2167 ,itemkey IN VARCHAR2
2168 ,actid IN NUMBER
2169 ,funcmode IN VARCHAR2
2170 ,resultout OUT NOCOPY VARCHAR2
2171 )
2172 IS
2173 l_api_version CONSTANT NUMBER := 1.0;
2174 l_api_name CONSTANT VARCHAR2(30) := 'Update_Claim_Status';
2175 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2176 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
2177 l_msg_count NUMBER;
2178 l_msg_data VARCHAR2(4000);
2179 l_error_msg VARCHAR2(4000);
2180
2181 l_claim_rec ozf_claim_pvt.claim_rec_type;
2182 l_status_code VARCHAR2(30);
2183 l_next_status_id NUMBER;
2184 l_new_status_id NUMBER;
2185 l_orig_status_id NUMBER;
2186 l_reject_status_id NUMBER;
2187 l_approved_amount NUMBER;
2188 l_update_status VARCHAR2(12);
2189 l_object_version_number NUMBER;
2190 l_org_id NUMBER;
2191 l_claim_id NUMBER;
2192 l_approver_id NUMBER;
2193 l_claim_amount_settled NUMBER;
2194
2195 l_user_id NUMBER;
2196 l_resp_id NUMBER;
2197 l_appl_id NUMBER;
2198 l_security_group_id NUMBER;
2199 l_approver_role_name VARCHAR2(30);
2200 APPROVAL_RAISE_ERROR EXCEPTION;
2201 l_approval_type VARCHAR2(30);
2202 l_payment_method VARCHAR2(30);
2203 l_approval_require VARCHAR2(1);
2204
2205 CURSOR csr_claim_obj_ver(cv_claim_id IN NUMBER) IS
2206 SELECT object_version_number
2207 FROM ozf_claims_all
2208 WHERE claim_id = cv_claim_id;
2209
2210 CURSOR csr_get_claim_amount (cv_claim_id IN NUMBER) IS
2211 SELECT amount_settled
2212 , payment_method
2213 FROM ozf_claims_all
2214 WHERE claim_id = cv_claim_id;
2215
2216
2217 BEGIN
2218 -- [BEGIN OF BUG2631497 FIXING by mchang 23-OCT-2002]
2219 -- mchang: initialized PL/SQL security context since workflow mailer desn't establish those value.
2220 l_user_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
2221 , itemkey => itemkey
2222 , aname => 'USER_ID'
2223 );
2224
2225 l_resp_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
2226 , itemkey => itemkey
2227 , aname => 'RESPONSIBILITY_ID'
2228 );
2229
2230 l_appl_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
2231 , itemkey => itemkey
2232 , aname => 'APPLICATION_ID'
2233 );
2234
2235 l_security_group_id := WF_ENGINE.getitemattrnumber( itemtype => itemtype
2236 , itemkey => itemkey
2237 , aname => 'SECURITY_GROUP_ID'
2238 );
2239
2240 -- [END OF BUG2631497 FIXING by mchang 23-OCT-2002]
2241
2242 IF funcmode = 'RUN' THEN
2243 l_update_status := wf_engine.getitemattrtext(
2244 itemtype => itemtype
2245 ,itemkey => itemkey
2246 ,aname => 'UPDATE_GEN_STATUS'
2247 );
2248
2249 IF l_update_status IN ('APPROVED', 'REJECTED') AND
2250 (l_user_id IS NULL OR l_resp_id IS NULL OR l_appl_id IS NULL) THEN
2251 l_update_status := NULL;
2252 WF_ENGINE.setitemattrnumber(
2253 itemtype => itemtype
2254 ,itemkey => itemkey
2255 ,aname => 'UPDATE_GEN_STATUS'
2256 ,avalue => l_update_status
2257 );
2258 FND_MSG_PUB.count_and_get (
2259 p_encoded => fnd_api.g_false
2260 ,p_count => l_msg_count
2261 ,p_data => l_msg_data
2262 );
2263 AMS_GEN_APPROVAL_PVT.handle_err(
2264 p_itemtype => itemtype
2265 ,p_itemkey => itemkey
2266 ,p_msg_count => l_msg_count
2267 ,p_msg_data => l_msg_data
2268 ,p_attr_name => 'AMS_ERROR_MSG'
2269 ,x_error_msg => l_error_msg
2270 );
2271 WF_CORE.context(
2272 'OZF_CLAIM_APPROVAL_PVT'
2273 ,'UPDATE_CLAIM_STATUS'
2274 ,itemtype
2275 ,itemkey
2276 ,actid
2277 ,l_error_msg
2278 );
2279 resultout := 'COMPLETE:ERROR';
2280 RETURN;
2281 ELSIF l_update_status IN ('APPROVED', 'REJECTED') THEN
2282 FND_GLOBAL.apps_initialize( user_id => l_user_id
2283 , resp_id => l_resp_id
2284 , resp_appl_id => l_appl_id
2285 --, security_group_id => l_security_group_id
2286 );
2287 END IF;
2288
2289 l_claim_id := wf_engine.getitemattrnumber(
2290 itemtype => itemtype
2291 ,itemkey => itemkey
2292 ,aname => 'AMS_ACTIVITY_ID'
2293 );
2294
2295 l_approval_type := wf_engine.GetItemAttrText(
2296 itemtype => itemtype
2297 ,itemkey => itemkey
2298 ,aname => 'AMS_APPROVAL_TYPE'
2299 );
2300
2301 l_new_status_id := wf_engine.getitemattrnumber(
2302 itemtype => itemtype
2303 ,itemkey => itemkey
2304 ,aname => 'AMS_NEW_STAT_ID'
2305 );
2306
2307 l_orig_status_id := wf_engine.getitemattrnumber(
2308 itemtype => itemtype
2309 ,itemkey => itemkey
2310 ,aname => 'AMS_ORIG_STAT_ID'
2311 );
2312
2313 l_reject_status_id := wf_engine.getitemattrnumber(
2314 itemtype => itemtype
2315 ,itemkey => itemkey
2316 ,aname => 'AMS_REJECT_STAT_ID'
2317 );
2318
2319 IF l_update_status = 'APPROVED' AND
2320 l_approval_type = 'EARNING' THEN
2321 IF OZF_Claim_Accrual_PVT.Perform_Approval_Required(l_claim_id) = FND_API.g_true THEN
2322 -- Start Performance Approval
2323 Start_Approval_Process(
2324 p_claim_id => l_claim_id
2325 ,p_orig_status_id => l_orig_status_id
2326 ,p_new_status_id => l_new_status_id
2327 ,p_reject_status_id => l_reject_status_id
2328 ,p_approval_type => 'PERFORMANCE'
2329 ,x_return_status => l_return_status
2330 );
2331 IF l_return_status = FND_API.g_ret_sts_success THEN
2332 resultout := 'COMPLETE:SUCCESS';
2333 RETURN;
2334 ELSE
2335 RAISE APPROVAL_RAISE_ERROR;
2336 END IF;
2337 ELSE
2338 OZF_CLAIM_SETTLEMENT_PVT.Claim_Approval_Required(p_claim_id => l_claim_id
2339 ,x_return_status => l_return_status
2340 ,x_msg_data => l_msg_data
2341 ,x_msg_count => l_msg_count
2342 ,x_approval_require => l_approval_require);
2343
2344 IF l_return_status = FND_API.g_ret_sts_error THEN
2345 RAISE FND_API.g_exc_error;
2346 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2347 RAISE FND_API.g_exc_unexpected_error;
2348 END IF;
2349
2350 IF l_approval_require = 'Y' THEN
2351 -- Start Performance Approval
2352 Start_Approval_Process(
2353 p_claim_id => l_claim_id
2354 ,p_orig_status_id => l_orig_status_id
2355 ,p_new_status_id => l_new_status_id
2356 ,p_reject_status_id => l_reject_status_id
2357 ,p_approval_type => 'CLAIM'
2358 ,x_return_status => l_return_status
2359 );
2360
2361
2362 IF l_return_status = FND_API.g_ret_sts_success THEN
2363 resultout := 'COMPLETE:SUCCESS';
2364 RETURN;
2365 ELSE
2366 RAISE APPROVAL_RAISE_ERROR;
2367 END IF;
2368 END IF;
2369 END IF;
2370 ELSIF l_update_status = 'APPROVED' AND
2371 l_approval_type = 'PERFORMANCE' THEN
2372 -- Start Performance Approval
2373 OZF_CLAIM_SETTLEMENT_PVT.Claim_Approval_Required(p_claim_id => l_claim_id
2374 ,x_return_status => l_return_status
2375 ,x_msg_data => l_msg_data
2376 ,x_msg_count => l_msg_count
2377 ,x_approval_require => l_approval_require);
2378
2379 IF l_return_status = FND_API.g_ret_sts_error THEN
2380 RAISE FND_API.g_exc_error;
2381 ELSIF l_return_status = FND_API.g_ret_sts_unexp_error THEN
2382 RAISE FND_API.g_exc_unexpected_error;
2383 END IF;
2384
2385 IF l_approval_require = 'Y' THEN
2386 -- Start Performance Approval
2387 Start_Approval_Process(
2388 p_claim_id => l_claim_id
2389 ,p_orig_status_id => l_orig_status_id
2390 ,p_new_status_id => l_new_status_id
2391 ,p_reject_status_id => l_reject_status_id
2392 ,p_approval_type => 'CLAIM'
2393 ,x_return_status => l_return_status
2394 );
2395
2396
2397 IF l_return_status = FND_API.g_ret_sts_success THEN
2398 resultout := 'COMPLETE:SUCCESS';
2399 RETURN;
2400 ELSE
2401 RAISE APPROVAL_RAISE_ERROR;
2402 END IF;
2403 END IF;
2404 END IF;
2405
2406
2407
2408 l_approved_amount := wf_engine.getitemattrnumber(
2409 itemtype => itemtype
2410 ,itemkey => itemkey
2411 ,aname => 'AMS_AMOUNT'
2412 );
2413
2414 l_object_version_number := wf_engine.getitemattrnumber(
2415 itemtype => itemtype
2416 ,itemkey => itemkey
2417 ,aname => 'AMS_OBJECT_VERSION_NUMBER'
2418 );
2419
2420
2421 -- get claims org_id
2422 l_org_id := find_org_id (l_claim_id);
2423
2424 -- set org_context since workflow mailer does not set the context
2425 set_org_ctx (l_org_id);
2426
2427 l_approver_id :=wf_engine.getitemattrnumber(
2428 itemtype => itemtype
2429 ,itemkey => itemkey
2430 ,aname => 'AMS_APPROVER_ID'
2431 );
2432
2433 OPEN csr_get_claim_amount (l_claim_id);
2434 FETCH csr_get_claim_amount INTO l_claim_amount_settled
2435 , l_payment_method;
2436 CLOSE csr_get_claim_amount;
2437
2438 IF l_approved_amount <> l_claim_amount_settled AND
2439 l_payment_method <> 'MASS_SETTLEMENT' THEN
2440 IF l_update_status IN ('APPROVED', 'REJECTED') THEN
2441 --set message.
2442 FND_MESSAGE.Set_Name('AMS','AMS_WF_NTF_AMOUNT_CHANGE_FYI');
2443 FND_MESSAGE.set_token('APPROVED_AMOUNT',l_approved_amount, FALSE);
2444 FND_MESSAGE.set_token('CLAIM_AMOUNT',l_claim_amount_settled, FALSE);
2445 FND_MSG_PUB.ADD;
2446 FND_MSG_PUB.count_and_get (
2447 p_encoded => fnd_api.g_false
2448 ,p_count => l_msg_count
2449 ,p_data => l_msg_data
2450 );
2451 AMS_GEN_APPROVAL_PVT.handle_err(
2452 p_itemtype => itemtype
2453 ,p_itemkey => itemkey
2454 ,p_msg_count => l_msg_count
2455 ,p_msg_data => l_msg_data
2456 ,p_attr_name => 'AMS_ERROR_MSG'
2457 ,x_error_msg => l_error_msg
2458 );
2459 WF_CORE.context(
2460 'AMS_GEN_APPROVAL_PVT'
2461 ,'Set_Activity_Details'
2462 ,itemtype
2463 ,itemkey
2464 ,actid
2465 ,l_error_msg
2466 );
2467 resultout := 'COMPLETE:ERROR';
2468 --999
2469 --Set performer of the next notification to original claim approver.
2470 l_approver_role_name := WF_ENGINE.GetItemAttrText(itemtype => itemtype
2471 ,itemkey => itemkey
2472 ,aname => 'AMS_APPROVER'
2473 );
2474
2475 WF_ENGINE.AssignActivity(itemtype => itemtype
2476 ,itemkey => itemkey
2477 ,activity => 'AMSGAPP:AMS_NTF_ERROR_REQUEST'
2478 ,performer => l_approver_role_name
2479 );
2480
2481
2482 /*
2483 WF_ENGINE.AssignActivity(itemtype => itemtype
2484 ,itemkey => itemkey
2485 ,activity => 'AMSGAPP:AMS_NTF_ERROR_REQUEST'
2486 ,performer => 'AMS_APPROVER'
2487 );
2488 */
2489
2490 ELSE
2491 resultout := 'COMPLETE:';
2492 END IF;
2493 RETURN;
2494 END IF;
2495
2496 IF l_update_status = 'APPROVED' THEN
2497 l_next_status_id := l_new_status_id;
2498
2499 l_claim_rec.approved_by := l_approver_id;
2500 l_claim_rec.approved_date := SYSDATE;
2501 l_claim_rec.object_version_number := l_object_version_number ;
2502
2503 IF OZF_DEBUG_HIGH_ON THEN
2504 ozf_utility_pvt.debug_message(l_full_name ||': update_status = ' || l_update_status);
2505 END IF;
2506
2507 ELSIF l_update_status = 'REJECTED' THEN
2508 l_next_status_id := l_reject_status_id;
2509
2510 l_claim_rec.object_version_number := l_object_version_number ;
2511
2512 ELSE
2513 l_next_status_id := OZF_UTILITY_PVT.get_default_user_status(
2514 'OZF_CLAIM_STATUS'
2515 ,'OPEN'
2516 );
2517
2518 OPEN csr_claim_obj_ver(l_claim_id);
2519 FETCH csr_claim_obj_ver INTO l_claim_rec.object_version_number;
2520 CLOSE csr_claim_obj_ver;
2521
2522 END IF;
2523
2524 l_status_code := ozf_utility_pvt.get_system_status_code(l_next_status_id);
2525 l_claim_rec.claim_id := l_claim_id;
2526 l_claim_rec.user_status_id := l_next_status_id;
2527
2528 IF OZF_DEBUG_HIGH_ON THEN
2529 ozf_utility_pvt.debug_message(l_full_name || l_status_code || l_approved_amount || l_update_status);
2530 END IF;
2531
2532 IF l_payment_method = 'MASS_SETTLEMENT' THEN
2533 IF l_update_status = 'APPROVED' THEN
2534
2535 OZF_MASS_SETTLEMENT_PVT.Start_Mass_Payment(
2536 p_group_claim_id => l_claim_rec.claim_id,
2537 x_return_status => l_return_status,
2538 x_msg_data => l_msg_data,
2539 x_msg_count => l_msg_count
2540 );
2541
2542 ELSE
2543
2544 OZF_MASS_SETTLEMENT_PVT.Reject_Mass_Payment(
2545 p_group_claim_id => l_claim_rec.claim_id,
2546 x_return_status => l_return_status,
2547 x_msg_data => l_msg_data,
2548 x_msg_count => l_msg_count
2549 );
2550
2551 END IF;
2552
2553 ELSE
2554 OZF_Claim_PVT.Update_Claim(
2555 p_api_version => l_api_version
2556 ,p_init_msg_list => FND_API.g_false
2557 ,p_commit => FND_API.g_false
2558 ,p_validation_level => FND_API.g_valid_level_full
2559 ,x_return_status => l_return_status
2560 ,x_msg_data => l_msg_data
2561 ,x_msg_count => l_msg_count
2562 ,p_claim => l_claim_rec
2563 ,p_event => 'UPDATE'
2564 ,p_mode => 'AUTO'
2565 ,x_object_version_number => l_object_version_number
2566 );
2567 END IF;
2568
2569 IF l_return_status = fnd_api.g_ret_sts_success THEN
2570 resultout := 'COMPLETE:SUCCESS';
2571 ELSE
2572 FND_MSG_PUB.count_and_get (
2573 p_encoded => fnd_api.g_false
2574 ,p_count => l_msg_count
2575 ,p_data => l_msg_data
2576 );
2577 AMS_GEN_APPROVAL_PVT.handle_err(
2578 p_itemtype => itemtype
2579 ,p_itemkey => itemkey
2580 ,p_msg_count => l_msg_count
2581 ,p_msg_data => l_msg_data
2582 ,p_attr_name => 'AMS_ERROR_MSG'
2583 ,x_error_msg => l_error_msg
2584 );
2585 WF_CORE.context(
2586 'OZF_CLAIM_APPROVAL_PVT'
2587 ,'UPDATE_CLAIM_STATUS'
2588 ,itemtype
2589 ,itemkey
2590 ,actid
2591 ,l_error_msg
2592 );
2593 resultout := 'COMPLETE:ERROR';
2594 END IF;
2595 RETURN;
2596 END IF;
2597
2598 -- CANCEL mode
2599 --
2600 IF (funcmode = 'CANCEL') THEN
2601 resultout := 'COMPLETE:';
2602 RETURN;
2603 END IF;
2604
2605 --
2606 -- TIMEOUT mode
2607 --
2608 IF (funcmode = 'TIMEOUT') THEN
2609 resultout := 'COMPLETE:';
2610 RETURN;
2611 END IF;
2612
2613 fnd_msg_pub.count_and_get(
2614 p_encoded => fnd_api.g_false
2615 ,p_count => l_msg_count
2616 ,p_data => l_msg_data
2617 );
2618
2619 IF OZF_DEBUG_HIGH_ON THEN
2620 ozf_utility_pvt.debug_message(l_full_name || ': l_return_status' || l_return_status);
2621 END IF;
2622
2623 EXCEPTION
2624 WHEN APPROVAL_RAISE_ERROR THEN
2625 wf_core.context( 'OZF_CLAIM_APPROVAL_PVT'
2626 , 'Start_Approval_Process'
2627 , itemtype
2628 , itemkey
2629 );
2630 RAISE;
2631
2632 WHEN OTHERS THEN
2633 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2634 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2635 FND_MSG_PUB.Add;
2636
2637 fnd_msg_pub.count_and_get(
2638 p_encoded => fnd_api.g_false
2639 ,p_count => l_msg_count
2640 ,p_data => l_msg_data
2641 );
2642 resultout := 'COMPLETE:ERROR';
2643 RETURN;
2644 END update_claim_status;
2645
2646
2647
2648 END OZF_Claim_Approval_PVT;