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