[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_SETTLEMENT_PVT
Source
1 PACKAGE BODY OZF_AR_SETTLEMENT_PVT AS
2 /* $Header: ozfvcapb.pls 120.11.12010000.4 2010/03/22 04:56:38 kpatro ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_AR_SETTLEMENT_PVT';
5 G_ITEMTYPE CONSTANT varchar2(30) := 'OZF_CSTL';
6 ERROR EXCEPTION;
7
8 --------------------------------------------------------------------------
9 PROCEDURE set_org_context(p_item_type IN VARCHAR2,
10 p_item_key IN VARCHAR2,
11 p_activity_id IN VARCHAR2,
12 p_command IN VARCHAR2,
13 p_resultout IN OUT NOCOPY VARCHAR2)
14 IS
15
16 CURSOR csr_claim IS
17 SELECT org_id
18 FROM ozf_claims_all
19 WHERE cstl_wf_item_key = p_item_key;
20
21 l_org_id NUMBER;
22
23 BEGIN
24
25 OPEN csr_claim;
26 FETCH csr_claim INTO l_org_id;
27 CLOSE csr_claim;
28
29 IF p_command = 'SET_CTX' THEN
30 mo_global.init('OZF');
31 mo_global.set_policy_context( p_access_mode => 'S', p_org_id => l_org_id);
32 RETURN;
33 END IF;
34
35 IF p_command = 'TEST_CTX' THEN
36 IF (NVL(mo_global.get_access_mode,'NULL') <> 'S') OR
37 (NVL(mo_global.get_current_org_id,-99) <> l_org_id)
38 THEN
39 p_resultout := 'FALSE';
40 RETURN;
41 ELSE
42 p_resultout := 'TRUE';
43 RETURN;
44 END IF;
45 END IF;
46
47 EXCEPTION
48 WHEN OTHERS THEN
49 WF_CORE.CONTEXT ('OZF_AR_SETTLEMENT_PVT',
50 'set_org_context',
51 p_item_type,
52 p_item_key,
53 p_activity_id,
54 p_command);
55 RAISE;
56 END set_org_context;
57
58 ---------------------------------------------------------------------------------
59 -- PROCEDURE
60 -- Get_User_Role
61 --
62 -- PURPOSE
63 -- This Procedure will be return the User role for the userid sent Called By
64 --
65 -- NOTES
66 --
67 ---------------------------------------------------------------------------------
68 PROCEDURE Get_User_Role(
69 p_user_id IN NUMBER,
70 x_role_name OUT NOCOPY VARCHAR2,
71 x_role_display_name OUT NOCOPY VARCHAR2
72 )
73 IS
74 -- Bug4904064: Replaced view jtf_rs_res_emp_vl with direct tables
75 -- Modified for Bugfix 5199354, to reduce shared memory usage further
76 CURSOR c_resource IS
77 select ppl.person_id employee_id
78 from jtf_rs_resource_extns rsc , per_people_f ppl
79 where rsc.category = 'EMPLOYEE' and ppl.person_id = rsc.source_id
80 and trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
81 and rsc.resource_id = p_user_id;
82
83 l_person_id NUMBER;
84
85 BEGIN
86 OPEN c_resource ;
87 FETCH c_resource INTO l_person_id ;
88 IF c_resource%NOTFOUND THEN
89 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
90 FND_MESSAGE.Set_Token('TEXT', sqlerrm );
91 FND_MSG_PUB.Add;
92 END IF;
93 CLOSE c_resource ;
94 -- Pass the Employee ID to get the Role
95 WF_DIRECTORY.getrolename(
96 p_orig_system => 'PER',
97 p_orig_system_id => l_person_id ,
98 p_name => x_role_name,
99 p_display_name => x_role_display_name
100 );
101 END Get_User_Role;
102
103
104 --------------------------------------------------------------------------------
105 -- PROCEDURE
106 -- Set_Reminder
107 --
108 -- Workflow cover:
109 -- IN
110 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
111 -- itemkey - A string generated from the application object's primary key.
112 -- actid - The function activity(instance id).
113 -- funcmode - Run/Cancel/Timeout
114 -- OUT
115 -- Resultout - 'COMPLETE:ERROR'
116 -- - 'COMPLETE:'
117 --
118 -- USED BY ACTIVITIES
119 -- <ITEM_TYPE> <ACTIVITY>
120 -- OZF_AR_SETTLEMENT_PVT OZF_SET_REMINDER
121 --
122 -- HISTORY
123 -- 04/05/2001 MCHANG CREATION.
124 --------------------------------------------------------------------------------
125 PROCEDURE Set_Reminder(
126 itemtype IN VARCHAR2,
127 itemkey IN VARCHAR2,
128 actid IN NUMBER,
129 funcmode IN VARCHAR2,
130 resultout OUT NOCOPY VARCHAR2
131 )
132 IS
133
134 BEGIN
135 ---- RUN mode ----
136 IF (funcmode = 'RUN') THEN
137 resultout := 'COMPLETE:';
138 RETURN;
139 END IF;
140 -- end RUN mode
141
142 ---- CANCEL mode -----
143 IF (funcmode = 'CANCEL') THEN
144 resultout := 'COMPLETE:';
145 RETURN;
146 END IF;
147 --
148 ---- TIMEOUT mode ----
149 IF (funcmode = 'TIMEOUT') THEN
150 resultout := 'COMPLETE:';
151 RETURN;
152 END IF;
153 --
154 EXCEPTION
155 WHEN OTHERS THEN
156 WF_CORE.context(
157 'OZF_AR_SETTLEMENT_PVT',
158 'Set_Reminder',
159 itemtype,
160 itemkey,
161 to_char(actid),
162 funcmode
163 );
164 RAISE;
165 END Set_Reminder;
166
167
168 --------------------------------------------------------------------------------
169 -- PROCEDURE
170 -- Incomplete_Claim
171 --
172 -- Workflow cover:
173 -- IN
174 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
175 -- itemkey - A string generated from the application object's primary key.
176 -- actid - The function activity(instance id).
177 -- funcmode - Run/Cancel/Timeout
178 -- OUT
179 -- Resultout - 'COMPLETE:ERROR'
180 -- - 'COMPLETE:'
181 --
182 -- USED BY ACTIVITIES
183 -- <ITEM_TYPE> <ACTIVITY>
184 -- OZF_AR_SETTLEMENT_PVT OZF_INCOMPLETE_CLAIM
185 --
186 -- HISTORY
187 -- 04/05/2001 MCHANG CREATION.
188 --------------------------------------------------------------------------------
189 PROCEDURE Incomplete_Claim(
190 itemtype IN VARCHAR2,
191 itemkey IN VARCHAR2,
192 actid IN NUMBER,
193 funcmode IN VARCHAR2,
194 resultout OUT NOCOPY VARCHAR2
195 )
196 IS
197 l_return_status VARCHAR2(1);
198 l_msg_data VARCHAR2(400);
199 l_msg_count NUMBER;
200
201 CURSOR csr_claim_version(cv_claim_id IN NUMBER) IS
202 SELECT object_version_number
203 , status_code
204 , org_id
205 FROM ozf_claims_all
206 WHERE claim_id = cv_claim_id;
207
208 l_claim_id NUMBER;
209 l_object_version NUMBER;
210 l_org_id NUMBER;
211 l_status_code VARCHAR2(30);
212 l_user_status_id NUMBER;
213 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
214 l_error_msg VARCHAR2(4000);
215
216 BEGIN
217 SAVEPOINT Inccmplete_Claim;
218 ---- RUN mode ----
219 IF (funcmode = 'RUN') THEN
220 -- get claim_id
221 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
222 itemkey => itemkey,
223 aname => 'OZF_CLAIM_ID'
224 );
225
226 OPEN csr_claim_version(l_claim_id);
227 FETCH csr_claim_version INTO l_object_version
228 , l_status_code
229 , l_org_id;
230 CLOSE csr_claim_version;
231
232 -- set org_context since workflow mailer does not set the context
233 --Set_Org_Ctx (l_org_id);
234
235 l_claim_rec.claim_id := l_claim_id;
236 l_claim_rec.object_version_number := l_object_version;
237 l_claim_rec.payment_status := 'INCOMPLETE';
238
239 OZF_CLAIM_PVT.Update_Claim (
240 p_api_version => 1.0
241 ,p_init_msg_list => FND_API.g_false
242 ,p_commit => FND_API.g_false
243 ,p_validation_level => FND_API.g_valid_level_full
244
245 ,x_return_status => l_return_status
246 ,x_msg_data => l_msg_data
247 ,x_msg_count => l_msg_count
248 ,p_claim => l_claim_rec
249 ,p_event => 'UPDATE'
250 ,p_mode => 'AUTO'
251 ,x_object_version_number => l_object_version
252 );
253 IF l_return_status <> FND_API.g_ret_sts_success THEN
254 RAISE ERROR;
255 END IF;
256
257 resultout := 'COMPLETE:';
258 RETURN;
259 END IF;
260 -- end RUN mode
261
262 ---- CANCEL mode -----
263 IF (funcmode = 'CANCEL') THEN
264 resultout := 'COMPLETE:';
265 RETURN;
266 END IF;
267 --
268 ---- TIMEOUT mode ----
269 IF (funcmode = 'TIMEOUT') THEN
270 resultout := 'COMPLETE:';
271 return;
272 END IF;
273 --
274 EXCEPTION
275 WHEN ERROR THEN
276 ROLLBACK TO Inccmplete_Claim;
277 FND_MSG_PUB.count_and_get (
278 p_encoded => FND_API.g_false
279 ,p_count => l_msg_count
280 ,p_data => l_msg_data
281 );
282 Handle_Error(
283 p_itemtype => itemtype
284 ,p_itemkey => itemkey
285 ,p_msg_count => l_msg_count
286 ,p_msg_data => l_msg_data
287 ,p_process_name => 'Incomplete_Creation'
288 ,x_error_msg => l_error_msg
289 );
290 WF_CORE.context(
291 'OZF_AR_SETTLEMENT_PVT'
292 ,'Incomplete_Creation'
293 ,itemtype
294 ,itemkey
295 ,actid
296 ,l_error_msg
297 );
298 RAISE;
299 WHEN OTHERS THEN
300 WF_CORE.context(
301 'OZF_AR_SETTLEMENT_PVT',
302 'Incomplete_Creation',
303 itemtype,
304 itemkey,
305 to_char(actid),
306 funcmode
307 );
308 RAISE;
309 END Incomplete_Claim;
310
311
312 --------------------------------------------------------------------------------
313 -- PROCEDURE
314 -- Prepare_Instructions
315 --
316 -- Workflow cover:
317 -- IN
318 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
319 -- itemkey - A string generated from the application object's primary key.
320 -- actid - The function activity(instance id).
321 -- funcmode - Run/Cancel/Timeout
322 -- OUT
323 -- Resultout - 'COMPLETE:ERROR'
324 -- - 'COMPLETE:'
325 --
326 -- USED BY ACTIVITIES
327 -- <ITEM_TYPE> <ACTIVITY>
328 -- OZF_AR_SETTLEMENT_PVT OZF_RECEIVABLE_INSTRUCTION
329 --
330 -- HISTORY
331 -- 04/05/2001 MCHANG CREATION.
332 -- 10/30/2001 MCHNAG Hand 'CANCELLED' and 'REJECTED' deduction :
333 -- get OZF_NEXT_STATUS, if it's other then 'CLOSED', return 'N'.
334 --------------------------------------------------------------------------------
335 PROCEDURE Prepare_Instructions(
336 itemtype IN VARCHAR2,
337 itemkey IN VARCHAR2,
338 actid IN NUMBER,
339 funcmode IN VARCHAR2,
340 resultout OUT NOCOPY VARCHAR2
341 )
342 IS
343
344 l_next_status VARCHAR2(30);
345
346 l_return_status VARCHAR2(1);
347 l_msg_data VARCHAR2(4000);
348 l_msg_count NUMBER;
349
350 l_claim_id NUMBER;
351 l_root_claim_id NUMBER;
352 l_payment_method VARCHAR2(30);
353 l_inv_org_id NUMBER;
354 l_vendor_id NUMBER;
355 l_vendor_site_id NUMBER;
356 l_vendor_name VARCHAR2(80);
357 l_vendor_site VARCHAR2(15);
358 l_vendor_info_str VARCHAR2(200) := NULL;
359
360 CURSOR csr_claim(cv_claim_id IN NUMBER) IS
361 SELECT root_claim_id
362 , payment_method
363 , vendor_id
364 , vendor_site_id
365 FROM ozf_claims_all
366 WHERE claim_id = cv_claim_id;
367
368 /* == Stuff used for Getting Split info ==== */
369
370 CURSOR csr_is_split( cv_claim_id IN NUMBER ) IS
371 SELECT COUNT(claim_id)
372 FROM ozf_claims
373 WHERE root_claim_id = cv_claim_id ;
374
375 CURSOR csr_get_split_details (cv_claim_id IN NUMBER ) IS
376 SELECT c.claim_number,
377 lk.meaning,
378 c.amount,
379 c.amount_remaining,
380 c.amount_settled
381 FROM ozf_claims c
382 , ozf_lookups lk
383 WHERE root_claim_id = cv_claim_id
384 AND c.status_code = lk.lookup_code
385 AND lk.lookup_type = 'OZF_CLAIM_STATUS'
386 ORDER BY claim_number;
387
388 TYPE l_split_rec is RECORD ( claim_number VARCHAR2(30)
389 , status VARCHAR2(30)
390 , amount NUMBER
391 , amount_remaining NUMBER
392 , amount_settled NUMBER
393 );
394
395 TYPE split_tbl_type IS TABLE OF l_split_rec;
396 l_split_tbl split_tbl_type;
397
398 l_split_count NUMBER;
399 l_rec_count NUMBER := 1;
400 l_split_msg VARCHAR2(2000);
401 l_split_str VARCHAR2(2000);
402
403 /* =========================================== */
404
405 /* ===== Getting Claim Line Detail info ====== */
406
407 CURSOR csr_claim_lines(cv_claim_id IN NUMBER) IS
408 SELECT ln.claim_line_id
409 , ln.line_number
410 , ln.item_id
411 , ln.quantity_uom
412 , ln.quantity
413 , ln.rate
414 , ln.claim_currency_amount
415 , ln.tax_code
416 , ln.earnings_associated_flag
417 , ln.org_id
418 FROM ozf_claim_lines ln
419 WHERE ln.claim_id = cv_claim_id;
420
421 TYPE line_detail_rec is RECORD ( claim_line_id NUMBER
422 , line_number NUMBER
423 , item_id NUMBER
424 , product VARCHAR2(40)
425 , uom_code VARCHAR2(3)
426 , uom_name VARCHAR2(25)
427 , quantity NUMBER
428 , rate NUMBER
429 , amount NUMBER
430 , type VARCHAR2(20)
431 , name VARCHAR2(30)
432 , tax_code VARCHAR2(50)
433 , tax_name VARCHAR2(50)
434 , gl_code_id VARCHAR2(30)
435 , gl_code VARCHAR2(50)
436 , earnings_flag VARCHAR2(1)
437 , org_id NUMBER
438 );
439 TYPE line_detail_tbl_type IS TABLE OF line_detail_rec
440 INDEX BY BINARY_INTEGER;
441 l_line_detail_tbl line_detail_tbl_type;
442 l_line_counter NUMBER := 1;
443 l_line_detail_str VARCHAR2(2000);
444 l_line_detail_msg VARCHAR2(2000);
445 l_cc_id_tbl OZF_GL_INTERFACE_PVT.CC_ID_TBL;
446
447 CURSOR csr_gl_code(cv_gl_code_id IN NUMBER) IS
448 SELECT padded_concatenated_segments
449 FROM gl_code_combinations_kfv
450 WHERE code_combination_id = cv_gl_code_id;
451
452 CURSOR csr_line_trx_info(cv_claim_line_id IN NUMBER) IS
453 SELECT lk.meaning
454 , trx.trx_number
455 FROM ozf_claim_lines ln
456 , ra_customer_trx trx
457 , ozf_lookups lk
458 WHERE ln.claim_line_id = cv_claim_line_id
459 AND ln.source_object_class = lk.lookup_code
460 AND lk.lookup_type = 'OZF_OBJECT_CLASS'
461 AND ln.source_object_id = trx.customer_trx_id;
462
463 CURSOR csr_line_product_name(cv_item_id IN NUMBER, cv_org_id IN NUMBER) IS
464 SELECT concatenated_segments
465 FROM mtl_system_items_vl
466 WHERE inventory_item_id = cv_item_id
467 AND organization_id = cv_org_id;
468
469 CURSOR csr_line_uom_name(cv_uom_code IN VARCHAR2) IS
470 SELECT unit_of_measure
471 FROM mtl_units_of_measure
472 WHERE uom_code = cv_uom_code;
473
474
475 /* =========================================== */
476 CURSOR csr_vendor_name(cv_vendor_id IN NUMBER) IS
477 SELECT vendor_name
478 FROM po_vendors
479 WHERE vendor_id = cv_vendor_id;
480
481 CURSOR csr_vendor_site(cv_vendor_site_id IN NUMBER) IS
482 SELECT vendor_site_code
483 FROM po_vendor_sites
484 WHERE vendor_site_id = cv_vendor_site_id;
485
486
487
488 -- [BEGIN OF BUG 3768539 FIXING]
489 l_rec_role_name VARCHAR2(320);
490 l_next_status_meaning VARCHAR2(60);
491 l_csetl_err_msg VARCHAR2(2000);
492 -- [END OF BUG 3768539 FIXING]
493
494 l_error_msg VARCHAR2(4000);
495 BEGIN
496
497 l_next_status := WF_ENGINE.GetItemAttrText(itemtype => itemtype,
498 itemkey => itemkey,
499 aname => 'OZF_NEXT_STATUS'
500 );
501
502 l_claim_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
503 itemkey => itemkey,
504 aname => 'OZF_CLAIM_ID'
505 );
506
507
508 -- [BEGIN OF BUG 3768539 FIXING]
509 l_rec_role_name := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
510 itemkey => itemkey,
511 aname => 'OZF_RECEIVABLE_ROLE'
512 );
513
514 -- Bug4042671: Read role from profile to allow role to be org specific.
515 IF l_rec_role_name IS NULL THEN
516 l_rec_role_name := FND_PROFILE.value('OZF_CLAIM_CSTL_WF_ROLE');
517 IF l_rec_role_name IS NOT NULL THEN
518 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
519 itemkey => itemkey,
520 aname => 'OZF_RECEIVABLE_ROLE',
521 avalue => l_rec_role_name
522 );
523 END IF;
524 END IF;
525
526 IF l_rec_role_name IS NULL THEN
527 l_next_status := 'OPEN';
528 l_next_status_meaning := OZF_UTILITY_PVT.get_lookup_meaning (
529 p_lookup_type => 'OZF_CLAIM_STATUS',
530 p_lookup_code => 'OPEN'
531 );
532
533
534 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
535 itemkey => itemkey,
536 aname => 'OZF_NEXT_STATUS_MEANING',
537 avalue => l_next_status_meaning
538 );
539
540 FND_MESSAGE.set_name('OZF', 'OZF_SETL_WF_REC_ROLE_ERR');
541 l_csetl_err_msg := FND_MESSAGE.get;
542
543 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
544 itemkey => itemkey,
545 aname => 'OZF_CSETL_ERR_MSG',
546 avalue => l_csetl_err_msg
547 );
548 END IF;
549 -- [END OF BUG 3768539 FIXING]
550
551
552 ---- RUN mode ----
553 IF (funcmode = 'RUN') THEN
554 IF l_next_status = 'CLOSED' THEN
555 OPEN csr_claim(l_claim_id);
556 FETCH csr_claim INTO l_root_claim_id
557 , l_payment_method
558 , l_vendor_id
559 , l_vendor_site_id;
560 CLOSE csr_claim;
561
562
563 /* === Fetch Claim Split Information ======= */
564
565 OPEN csr_is_split(l_root_claim_id);
566 FETCH csr_is_split into l_split_count ;
567 CLOSE csr_is_split;
568
569 l_split_tbl := split_tbl_type();
570
571 IF l_split_count > 1 then
572
573 FOR i IN csr_get_split_details(l_root_claim_id)
574 LOOP
575 l_split_tbl.EXTEND;
576 l_split_tbl(l_rec_count).claim_number := i.claim_number;
577 l_split_tbl(l_rec_count).status := i.meaning;
578 l_split_tbl(l_rec_count).amount := i.amount;
579 l_split_tbl(l_rec_count).amount_remaining := i.amount_remaining;
580 l_split_tbl(l_rec_count).amount_settled := i.amount_settled;
581
582 l_rec_count := l_rec_count+1;
583 END LOOP;
584
585 FOR i IN 1..l_split_tbl.COUNT
586 LOOP
587 /*
588 l_split_str := l_split_str || ', ('
589 || RPAD(l_split_tbl(i).claim_number,30,' ') || ' , '
590 || TO_CHAR(l_split_tbl(i).amount_remaining)
591 || ')' ;
592
593 -- || fnd_global.local_chr(10) ;
594 */
595 l_split_msg := SUBSTR(l_split_msg||'('
596 || RPAD(l_split_tbl(i).claim_number,30,' ') || ' , '
597 || RPAD(l_split_tbl(i).status,30,' ') || ' , '
598 || TO_CHAR(l_split_tbl(i).amount) || ' , '
599 || TO_CHAR(l_split_tbl(i).amount_remaining) || ' , '
600 || TO_CHAR(l_split_tbl(i).amount_settled)
601 || ')'
602 || FND_GLOBAL.local_chr(10), 1, 2000);
603 END LOOP;
604 END IF;
605
606 FND_MESSAGE.set_name('OZF', 'OZF_SETL_WF_SPLT_DETLS');
607 FND_MESSAGE.set_token('OZF_SETL_WF_SPLT_DETLS', l_split_msg, false);
608 l_split_str := FND_MESSAGE.get;
609
610 /* ========================================= */
611
612 /* == Set Vendor Information in case of Contra Charge settlement == */
613 IF l_payment_method = 'CONTRA_CHARGE' THEN
614 OPEN csr_vendor_name(l_vendor_id);
615 FETCH csr_vendor_name INTO l_vendor_name;
616 CLOSE csr_vendor_name;
617
618 OPEN csr_vendor_site(l_vendor_site_id);
619 FETCH csr_vendor_site INTO l_vendor_site;
620 CLOSE csr_vendor_site;
621
622 FND_MESSAGE.set_name('OZF', 'OZF_SETL_WF_VENDOR_INFO');
623 FND_MESSAGE.set_token('VENDOR_NAME', l_vendor_name, false);
624 FND_MESSAGE.set_token('VENDOR_SITE', l_vendor_site, false);
625 l_vendor_info_str := FND_MESSAGE.get;
626 END IF;
627
628 /* ========================================= */
629
630 /* === Fetch Claim Lines Detail Information ======= */
631 OPEN csr_claim_lines(l_claim_id);
632 LOOP
633 FETCH csr_claim_lines into l_line_detail_tbl(l_line_counter).claim_line_id
634 , l_line_detail_tbl(l_line_counter).line_number
635 , l_line_detail_tbl(l_line_counter).item_id
636 , l_line_detail_tbl(l_line_counter).uom_code
637 , l_line_detail_tbl(l_line_counter).quantity
638 , l_line_detail_tbl(l_line_counter).rate
639 , l_line_detail_tbl(l_line_counter).amount
640 , l_line_detail_tbl(l_line_counter).tax_code
641 , l_line_detail_tbl(l_line_counter).earnings_flag
642 , l_line_detail_tbl(l_line_counter).org_id;
643 EXIT WHEN csr_claim_lines%NOTFOUND;
644 l_line_counter := l_line_counter + 1;
645 END LOOP;
646 CLOSE csr_claim_lines;
647
648
649 IF l_line_counter > 1 THEN
650 FOR i IN 1..l_line_detail_tbl.count LOOP
651 IF l_line_detail_tbl(i).earnings_flag = 'T' THEN
652 OZF_GL_INTERFACE_PVT.Get_GL_Account(
653 p_api_version => 1.0
654 ,p_init_msg_list => FND_API.g_false
655 ,p_commit => FND_API.g_false
656 ,p_validation_level => FND_API.g_valid_level_full
657 ,x_return_status => l_return_status
658 ,x_msg_data => l_msg_data
659 ,x_msg_count => l_msg_count
660 ,p_source_id => l_line_detail_tbl(i).claim_line_id
661 ,p_source_table => 'OZF_CLAIM_LINES_ALL'
662 ,p_account_type => 'REC_CLEARING'
663 ,x_cc_id_tbl => l_cc_id_tbl
664 );
665 IF l_return_status <> FND_API.g_ret_sts_success THEN
666 RAISE ERROR;
667 END IF;
668
669 IF l_cc_id_tbl.EXISTS(1) THEN
670 OPEN csr_gl_code(l_cc_id_tbl(1).code_combination_id);
671 FETCH csr_gl_code INTO l_line_detail_tbl(i).gl_code;
672 CLOSE csr_gl_code;
673 END IF;
674 END IF;
675
676 IF l_line_detail_tbl(i).item_id IS NOT NULL THEN
677 l_inv_org_id := FND_PROFILE.value('OZF_ITEM_ORGANIZATION_ID');
678 OPEN csr_line_product_name(l_line_detail_tbl(i).item_id, l_inv_org_id);
679 FETCH csr_line_product_name INTO l_line_detail_tbl(i).product;
680 CLOSE csr_line_product_name;
681 ENd IF;
682
683 IF l_line_detail_tbl(i).uom_code IS NOT NULL THEN
684 OPEN csr_line_uom_name(l_line_detail_tbl(i).uom_code);
685 FETCH csr_line_uom_name INTO l_line_detail_tbl(i).uom_name;
686 CLOSE csr_line_uom_name;
687 END IF;
688
689
690 OPEN csr_line_trx_info(l_line_detail_tbl(i).claim_line_id);
691 FETCH csr_line_trx_info INTO l_line_detail_tbl(i).type
692 , l_line_detail_tbl(i).name;
693 CLOSE csr_line_trx_info;
694
695 l_line_detail_msg := SUBSTR(l_line_detail_msg||'('||
696 TO_CHAR(l_line_detail_tbl(i).line_number)||FND_GLOBAL.local_chr(9)||' , '||
697 NVL(l_line_detail_tbl(i).type, ' ')||FND_GLOBAL.local_chr(9)||' , '||
698 NVL(l_line_detail_tbl(i).name, ' ')||FND_GLOBAL.local_chr(9)||' , '||
699 NVL(l_line_detail_tbl(i).product, ' ')||FND_GLOBAL.local_chr(9)||' , '||
700 NVL(l_line_detail_tbl(i).uom_name, ' ')||FND_GLOBAL.local_chr(9)||' , '||
701 NVL(TO_CHAR(l_line_detail_tbl(i).quantity), ' ')||FND_GLOBAL.local_chr(9)||' , '||
702 NVL(TO_CHAR(l_line_detail_tbl(i).rate), ' ')||FND_GLOBAL.local_chr(9)||' , '||
703 NVL(TO_CHAR(l_line_detail_tbl(i).amount), ' ')||FND_GLOBAL.local_chr(9)||' , '||
704 NVL(l_line_detail_tbl(i).tax_code, ' ')||FND_GLOBAL.local_chr(9)||' , '||
705 NVL(l_line_detail_tbl(i).gl_code, ' ')||FND_GLOBAL.local_chr(9)||' , '||
706 NVL(l_line_detail_tbl(i).earnings_flag, ' ')||FND_GLOBAL.local_chr(9)||')'||
707 FND_GLOBAL.local_chr(10), 1, 2000);
708 END LOOP;
709 END IF;
710
711 FND_MESSAGE.set_name('OZF', 'OZF_SETL_WF_LINE_DETLS');
712 FND_MESSAGE.set_token('OZF_SETL_WF_LINE_DETLS', l_line_detail_msg, false);
713 l_line_detail_str := FND_MESSAGE.get;
714 /* ========================================= */
715
716 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
717 itemkey => itemkey,
718 aname => 'OZF_SPLIT_DETAILS',
719 avalue => l_split_str
720 );
721 --
722
723 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
724 itemkey => itemkey,
725 aname => 'OZF_LINE_DETAILS',
726 avalue => l_line_detail_str
727 );
728
729 WF_ENGINE.SetItemAttrDocument(itemtype => itemtype,
730 itemkey => itemkey,
731 aname => 'OZF_LINE_DETAILS',
732 documentid => l_line_detail_str
733 );
734
735 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
736 itemkey => itemkey,
737 aname => 'OZF_VENDOR_INFO',
738 avalue => l_vendor_info_str
739 );
740 resultout := 'COMPLETE:Y';
741 RETURN;
742 ELSE
743 resultout := 'COMPLETE:N';
744 RETURN;
745 END IF;
746 END IF;
747 -- end RUN mode
748
749 ---- CANCEL mode -----
750 IF (funcmode = 'CANCEL') THEN
751 resultout := 'COMPLETE:';
752 RETURN;
753 END IF;
754 --
755 ---- TIMEOUT mode ----
756 IF (funcmode = 'TIMEOUT') THEN
757 resultout := 'COMPLETE:';
758 return;
759 END IF;
760 --
761 EXCEPTION
762 WHEN ERROR THEN
763 FND_MSG_PUB.count_and_get (
764 p_encoded => FND_API.g_false
765 ,p_count => l_msg_count
766 ,p_data => l_msg_data
767 );
768 Handle_Error(
769 p_itemtype => itemtype
770 ,p_itemkey => itemkey
771 ,p_msg_count => l_msg_count
772 ,p_msg_data => l_msg_data
773 ,p_process_name => 'PREPARE_INSTRUCTIONS'
774 ,x_error_msg => l_error_msg
775 );
776 WF_CORE.context(
777 'OZF_AR_SETTLEMENT_PVT'
778 ,'Prepare_Instructions'
779 ,itemtype
780 ,itemkey
781 ,actid
782 ,l_error_msg
783 );
784 RAISE;
785 WHEN OTHERS THEN
786 IF (csr_claim_lines%ISOPEN) THEN
787 CLOSE csr_claim_lines;
788 END IF;
789 IF (csr_gl_code%ISOPEN) THEN
790 CLOSE csr_gl_code;
791 END IF;
792 WF_CORE.context(
793 'OZF_AR_SETTLEMENT_PVT',
794 'Prepare_Instructions',
795 itemtype,
796 itemkey,
797 to_char(actid),
798 funcmode
799 );
800 RAISE;
801 END Prepare_Instructions;
802
803
804 --------------------------------------------------------------------------------
805 -- PROCEDURE
806 -- Update_Docs
807 --
808 -- Workflow cover:
809 -- IN
810 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
811 -- itemkey - A string generated from the application object's primary key.
812 -- actid - The function activity(instance id).
813 -- funcmode - Run/Cancel/Timeout
814 -- OUT
815 -- Resultout - 'COMPLETE:ERROR'
816 -- - 'COMPLETE:'
817 --
818 -- USED BY ACTIVITIES
819 -- <ITEM_TYPE> <ACTIVITY>
820 -- OZF_AR_SETTLEMENT_PVT OZF_UPDATE_DOCS
821 --
822 -- HISTORY
823 -- 04/05/2001 MCHANG CREATION.
824 -- 03/04/2002 MCHANG Updated to populate Wriet_off number.(see BUG#2226663)
825 -- 15-Jul-05 Sahana R12: AP-AR Netting Changes
826 --------------------------------------------------------------------------------
827 PROCEDURE Update_Docs(
828 itemtype IN VARCHAR2,
829 itemkey IN VARCHAR2,
830 actid IN NUMBER,
831 funcmode IN VARCHAR2,
832 resultout OUT NOCOPY VARCHAR2
833 )
834 IS
835 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
836 l_msg_data VARCHAR2(400);
837 l_msg_count NUMBER;
838
839 -- Cursor to get claim payment_method
840 CURSOR csr_get_settle_method(cv_claim_id IN NUMBER) IS
841 SELECT payment_method
842 , org_id
843 FROM ozf_claims_all
844 WHERE claim_id = cv_claim_id;
845
846
847 TYPE trx_number_type IS VARRAY(5) OF VARCHAR2(30);
848 l_trx_number trx_number_type;
849
850 CURSOR csr_cm_trx_data(cv_trx_number IN VARCHAR2) IS
851 SELECT distinct customer_trx_id
852 FROM ar_payment_schedules
853 WHERE trx_number = cv_trx_number
854 AND class = 'CM';
855
856 CURSOR csr_dm_trx_data(cv_trx_number IN VARCHAR2) IS
857 SELECT distinct customer_trx_id
858 FROM ar_payment_schedules
859 WHERE trx_number = cv_trx_number
860 AND class in ( 'DM', 'CB', 'INV');
861
862 CURSOR csr_cb_trx_data(cv_trx_number IN VARCHAR2) IS
863 SELECT distinct customer_trx_id
864 FROM ar_payment_schedules
865 WHERE trx_number = cv_trx_number;
866
867 -- Cursor to verify Write_Off number
868 CURSOR csr_write_off_trx(cv_trx_number IN VARCHAR2) IS
869 SELECT adjustment_id
870 FROM ar_adjustments
871 WHERE adjustment_number = cv_trx_number;
872
873 l_trx_id NUMBER := NULL;
874 l_trx_number_name VARCHAR2(30);
875 l_err_trx_number_name VARCHAR2(30);
876 l_trx_number_invalid VARCHAR2(1) := 'N';
877 l_trx_number_all_null VARCHAR2(1) := 'Y';
878 l_trx_number_error VARCHAR2(1) := FND_API.g_false;
879
880 l_claim_id NUMBER;
881 l_org_id NUMBER;
882 l_settlement_method VARCHAR(15);
883 l_invoice_id NUMBER := NULL;
884 l_receipt_id NUMBER := NULL;
885 l_settle_amount NUMBER;
886 l_settlement_doc_id NUMBER;
887 l_settlement_doc_rec OZF_Settlement_Doc_PVT.settlement_doc_rec_type;
888 l_settlement_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
889 l_err_msg VARCHAR2(2000);
890 l_do_fetch VARCHAR2(1) := 'N';
891 l_is_overpayment BOOLEAN := FALSE;
892 l_trx_attribute_name VARCHAR2(30);
893
894 --modified for Bugfix 5199354
895 CURSOR csr_ar_settlement(cv_trx_id IN NUMBER) IS
896 select pay.customer_trx_id --"settlement_id"
897 , pay.cust_trx_type_id --"settlement_type_id"
898 , pay.trx_number --"settlement_number"
899 , pay.trx_date --"settlement_date"
900 , sum(pay.amount_due_original) --"settlement_amount"
901 , pay.status
902 from ar_payment_schedules pay
903 where pay.customer_trx_id = cv_trx_id
904 group by pay.customer_trx_id, pay.cust_trx_type_id,pay.trx_number,
905 pay.trx_date , pay.status;
906
907 CURSOR csr_ar_settle_ded_writeoff(cv_trx_id IN NUMBER) IS
908 SELECT adj.adjustment_id --"settlement_id"
909 , adj.receivables_trx_id --"settlement_type_id"
910 , adj.adjustment_number --"settlement_number"
911 , adj.apply_date --"settlement_date"
912 , adj.amount --"settlement_amount"
913 , pay.status --"status_code"
914 FROM ar_adjustments adj
915 , ar_payment_schedules pay
916 WHERE adj.payment_schedule_id = pay.payment_schedule_id
917 AND adj.adjustment_id = cv_trx_id;
918
919 CURSOR csr_ar_settle_rec_writeoff(cv_cash_receipt_id IN NUMBER) IS
920 SELECT rec.receivable_application_id --"settlement_id"
921 , pay.payment_schedule_id --"settlement_type_id"
922 , NULL --"settlement_number"
923 , rec.apply_date --"settlement_date"
924 , rec.amount_applied --"settlement_amount"
925 , pay.status --"status_code"
926 FROM ar_receivable_applications rec
927 , ar_payment_schedules pay
928 WHERE rec.cash_receipt_id = cv_cash_receipt_id
929 AND rec.applied_payment_schedule_id = -3
930 AND rec.applied_payment_schedule_id = pay.payment_schedule_id;
931
932 CURSOR csr_del_settle_doc(cv_claim_id IN NUMBER) IS
933 SELECT settlement_doc_id
934 , object_version_number
935 FROM ozf_settlement_docs
936 WHERE claim_id = cv_claim_id;
937
938 BEGIN
939 SAVEPOINT Update_Docs;
940 ---- RUN mode ----
941 IF (funcmode = 'RUN') THEN
942 l_claim_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
943 itemkey => itemkey,
944 aname => 'OZF_CLAIM_ID'
945 );
946 l_settle_amount := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
947 itemkey => itemkey,
948 aname => 'OZF_AMOUNT_SETTLED'
949 );
950
951 l_receipt_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
952 itemkey => itemkey,
953 aname => 'OZF_RECEIPT_ID'
954 );
955 l_invoice_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
956 itemkey => itemkey,
957 aname => 'OZF_INVOICE_ID'
958 );
959 OPEN csr_get_settle_method(l_claim_id);
960 FETCH csr_get_settle_method INTO l_settlement_method
961 , l_org_id;
962 CLOSE csr_get_settle_method;
963
964 -- set org_context since workflow mailer does not set the context
965 --Set_Org_Ctx (l_org_id);
966
967
968 IF l_settle_amount < 0 THEN
969 l_is_overpayment := TRUE;
970 END IF;
971
972 l_trx_number := trx_number_type();
973
974
975 /*------------------------*
976 | Get Transaction Number
977 | Reset Error Transaction Number Array
978 *------------------------*/
979
980 IF l_settlement_method = 'WRITE_OFF' AND
981 (l_invoice_id IS NULL OR l_is_overpayment) THEN
982 -- If it's a overpayment-receipt write_off,
983 -- workflow don't expect a transaction number entered by user.
984 l_trx_number.extend;
985 l_trx_number(1) := 'Receipt Write-off';
986 l_trx_number.extend;
987 l_trx_number(2) := NULL;
988 l_trx_number.extend;
989 l_trx_number(3) := NULL;
990 l_trx_number.extend;
991 l_trx_number(4) := NULL;
992 l_trx_number.extend;
993 l_trx_number(5) := NULL;
994 l_trx_number_all_null := 'N';
995 ELSE
996 -- get OZF_TRX_NUMBER_i
997 FOR i IN 1..5 LOOP
998 l_trx_number_name := 'OZF_TRX_NUMBER_'||i;
999 l_trx_number.extend;
1000 l_trx_number(i) := WF_ENGINE.GetItemAttrText( itemtype => itemtype
1001 , itemkey => itemkey
1002 , aname => l_trx_number_name
1003 );
1004
1005 l_trx_number(i) := LTRIM(RTRIM(l_trx_number(i)));
1006
1007 FOR j IN 1..i LOOP
1008 IF i <> 1 AND l_trx_number(i) = l_trx_number(j) THEN
1009 l_trx_number(i) := NULL;
1010 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1011 , itemkey => itemkey
1012 , aname => l_trx_number_name
1013 , avalue => NULL
1014 );
1015 END IF;
1016 END LOOP;
1017
1018 IF l_trx_number(i) IS NOT NULL THEN
1019 l_trx_number_all_null := 'N';
1020 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1021 , itemkey => itemkey
1022 , aname => l_trx_number_name
1023 , avalue => l_trx_number(i)
1024 );
1025 END IF;
1026
1027 l_err_trx_number_name := 'OZF_ERROR_TRX_NUMBER_'||i;
1028 WF_ENGINE.SetItemAttrText( itemtype => itemtype
1029 , itemkey => itemkey
1030 , aname => l_err_trx_number_name
1031 , avalue => NULL
1032 );
1033 END LOOP;
1034
1035 IF l_trx_number_all_null = 'Y' THEN
1036 resultout := 'COMPLETE:ERROR';
1037 RETURN;
1038 END IF;
1039 END IF;
1040
1041 /*---------------------------*
1042 | Verify Transaction Number
1043 *---------------------------*/
1044
1045 FOR i IN 1..5 LOOP
1046 IF l_trx_number(i) IS NOT NULL THEN
1047 l_do_fetch := 'N';
1048 l_trx_number_invalid := 'N';
1049
1050 IF l_settlement_method = 'CONTRA_CHARGE' THEN -- R12
1051 l_do_fetch := 'Y';
1052 ELSIF l_settlement_method = 'WRITE_OFF' THEN
1053
1054 IF l_invoice_id IS NULL OR l_is_overpayment THEN
1055 -- Overpayment - Receipt Write-Off: payment_schedule_id for Receipt Write-off is -3
1056 l_do_fetch := 'Y';
1057 ELSE
1058 -- Deduction Write-Off: AR will issue a write_off number.
1059 OPEN csr_write_off_trx(l_trx_number(i));
1060 FETCH csr_write_off_trx INTO l_trx_id;
1061 IF csr_write_off_trx%NOTFOUND THEN
1062 l_do_fetch := 'N';
1063 ELSE
1064 l_do_fetch := 'Y';
1065 END IF;
1066 CLOSE csr_write_off_trx;
1067 END IF;
1068 ELSE
1069 -- Other settlement methods
1070 IF l_settlement_method ='CHARGEBACK' THEN
1071 OPEN csr_cb_trx_data(l_trx_number(i));
1072 FETCH csr_cb_trx_data INTO l_trx_id;
1073 IF csr_cb_trx_data%NOTFOUND THEN
1074 l_do_fetch := 'N';
1075 ELSE
1076 l_do_fetch := 'Y';
1077 END IF;
1078 CLOSE csr_cb_trx_data;
1079
1080 ELSIF l_is_overpayment THEN
1081 OPEN csr_dm_trx_data(l_trx_number(i));
1082 FETCH csr_dm_trx_data INTO l_trx_id;
1083 IF csr_dm_trx_data%NOTFOUND THEN
1084 l_do_fetch := 'N';
1085 ELSE
1086 l_do_fetch := 'Y';
1087 END IF;
1088 CLOSE csr_dm_trx_data;
1089 ELSE
1090 OPEN csr_cm_trx_data(l_trx_number(i));
1091 FETCH csr_cm_trx_data INTO l_trx_id;
1092 IF csr_cm_trx_data%NOTFOUND THEN
1093 l_do_fetch := 'N';
1094 ELSE
1095 l_do_fetch := 'Y';
1096 END IF;
1097 CLOSE csr_cm_trx_data;
1098 END IF;
1099 END IF;
1100
1101 IF l_do_fetch = 'Y' THEN
1102 /*---------------------------*
1103 | Populate Settlement Docs
1104 *---------------------------*/
1105 IF l_settlement_method = 'CONTRA_CHARGE' THEN
1106 l_settlement_doc_rec.settlement_number := l_trx_number(i);
1107 ELSIF l_settlement_method = 'WRITE_OFF' THEN
1108 IF l_invoice_id IS NULL AND
1109 l_is_overpayment THEN
1110 -- Overpayment: Receipt Write-Off
1111 OPEN csr_ar_settle_rec_writeoff(l_receipt_id);
1112 FETCH csr_ar_settle_rec_writeoff INTO
1113 l_settlement_doc_rec.settlement_id
1114 , l_settlement_doc_rec.settlement_type_id
1115 , l_settlement_doc_rec.settlement_number
1116 , l_settlement_doc_rec.settlement_date
1117 , l_settlement_doc_rec.settlement_amount
1118 , l_settlement_doc_rec.status_code;
1119 CLOSE csr_ar_settle_rec_writeoff;
1120 ELSE
1121 -- Deduction Write-Off
1122 OPEN csr_ar_settle_ded_writeoff(l_trx_id);
1123 FETCH csr_ar_settle_ded_writeoff INTO
1124 l_settlement_doc_rec.settlement_id
1125 , l_settlement_doc_rec.settlement_type_id
1126 , l_settlement_doc_rec.settlement_number
1127 , l_settlement_doc_rec.settlement_date
1128 , l_settlement_doc_rec.settlement_amount
1129 , l_settlement_doc_rec.status_code;
1130 CLOSE csr_ar_settle_ded_writeoff;
1131 END IF;
1132 ELSE
1133 -- Other settlement method
1134 OPEN csr_ar_settlement(l_trx_id);
1135 FETCH csr_ar_settlement INTO
1136 l_settlement_doc_rec.settlement_id
1137 , l_settlement_doc_rec.settlement_type_id
1138 , l_settlement_doc_rec.settlement_number
1139 , l_settlement_doc_rec.settlement_date
1140 , l_settlement_doc_rec.settlement_amount
1141 , l_settlement_doc_rec.status_code;
1142 CLOSE csr_ar_settlement;
1143 END IF;
1144
1145 l_settlement_doc_rec.payment_method := l_settlement_method;
1146 l_settlement_doc_rec.claim_id := l_claim_id;
1147
1148 -- create ozf_setttlement_docs_all
1149 BEGIN
1150 OZF_Settlement_Doc_PVT.Create_Settlement_Doc(
1151 p_api_version_number => 1.0,
1152 p_init_msg_list => FND_API.g_false,
1153 p_commit => FND_API.g_false,
1154 p_validation_level => FND_API.g_valid_level_full,
1155 x_return_status => l_return_status,
1156 x_msg_count => l_msg_count,
1157 x_msg_data => l_msg_data,
1158 p_settlement_doc_rec => l_settlement_doc_rec,
1159 x_settlement_doc_id => l_settlement_doc_id
1160 );
1161
1162 IF l_return_status <> FND_API.g_ret_sts_success THEN
1163 l_trx_number_invalid := 'Y';
1164 l_err_msg := l_msg_count||' '||l_msg_data;
1165 END IF;
1166 EXCEPTION
1167 WHEN OTHERS THEN
1168 WF_CORE.context( 'OZF_AR_SETTLEMENT_PVT',
1169 'Update_Docs'||'-'||'Update_Settlement_Docs',
1170 itemtype,
1171 itemkey,
1172 to_char(actid),
1173 funcmode
1174 );
1175 l_err_msg := SQLERRM;
1176 RAISE;
1177 END; -- begin-exception-end
1178 END IF; -- end-if l_do_fetch
1179
1180 IF l_trx_number_invalid = 'Y' OR l_do_fetch = 'N' THEN
1181 l_trx_number_error := FND_API.g_true;
1182 l_err_trx_number_name := 'OZF_ERROR_TRX_NUMBER_'||i;
1183 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1184 itemkey => itemkey,
1185 aname => l_err_trx_number_name,
1186 avalue => l_trx_number(i)
1187 );
1188 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
1189 itemkey => itemkey,
1190 aname => 'OZF_RECEIVABLE_NOTES',
1191 avalue => l_err_msg
1192 );
1193 END IF;
1194
1195 END IF; -- end-if l_trx_num not null
1196 END LOOP; -- end-if 1..5 loop
1197
1198 IF l_trx_number_error = FND_API.g_true THEN
1199 resultout := 'COMPLETE:ERROR';
1200 ELSE
1201 resultout := 'COMPLETE:SUCCESS';
1202 END IF;
1203
1204 RETURN;
1205
1206 END IF;
1207 -- end RUN mode
1208
1209 ---- CANCEL mode -----
1210 IF (funcmode = 'CANCEL') THEN
1211 OPEN csr_del_settle_doc(l_claim_id);
1212 FETCH csr_del_settle_doc INTO l_settlement_doc_rec.settlement_doc_id
1213 , l_settlement_doc_rec.object_version_number;
1214 CLOSE csr_del_settle_doc;
1215
1216 BEGIN
1217 OZF_Settlement_Doc_PVT.Delete_Settlement_Doc(
1218 p_api_version_number => 1.0,
1219 p_init_msg_list => FND_API.g_false,
1220 p_commit => FND_API.g_false,
1221 p_validation_level => FND_API.g_valid_level_full,
1222 x_return_status => l_return_status,
1223 x_msg_count => l_msg_count,
1224 x_msg_data => l_msg_data,
1225 p_settlement_doc_id => l_settlement_doc_rec.settlement_doc_id,
1226 p_object_version_number => l_settlement_doc_rec.object_version_number
1227 );
1228 IF l_return_status <> FND_API.g_ret_sts_success THEN
1229 resultout := 'COMPLETE:ERROR';
1230 RETURN;
1231 END IF;
1232 EXCEPTION
1233 WHEN OTHERS THEN
1234 WF_CORE.context( 'OZF_AR_SETTLEMENT_PVT',
1235 'Update_Docs'||'-'||'Update_Claim',
1236 itemtype,
1237 itemkey,
1238 to_char(actid),
1239 funcmode
1240 );
1241 RAISE;
1242 END;
1243
1244 resultout := 'COMPLETE:';
1245 RETURN;
1246 END IF;
1247 --
1248 ---- TIMEOUT mode ----
1249 IF (funcmode = 'TIMEOUT') THEN
1250 resultout := 'COMPLETE:';
1251 return;
1252 END IF;
1253 --
1254 EXCEPTION
1255 WHEN OTHERS THEN
1256 ROLLBACK TO Update_Docs;
1257 WF_CORE.context(
1258 'OZF_AR_SETTLEMENT_PVT',
1259 'Update_Docs',
1260 itemtype,
1261 itemkey,
1262 to_char(actid),
1263 funcmode
1264 );
1265 RAISE;
1266 END Update_Docs;
1267
1268 --------------------------------------------------------------------------------
1269 -- PROCEDURE
1270 -- Create_Settle_Doc
1271 --
1272 -- Workflow cover:
1273 -- IN
1274 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1275 -- itemkey - A string generated from the application object's primary key.
1276 -- actid - The function activity(instance id).
1277 -- funcmode - Run/Cancel/Timeout
1278 -- OUT
1279 -- Resultout - 'COMPLETE:ERROR'
1280 -- - 'COMPLETE:SUCCESS'
1281 --
1282 -- USED BY ACTIVITIES
1283 -- <ITEM_TYPE> <ACTIVITY>
1284 -- OZF_AR_SETTLEMENT_PVT OZF_CREATE_SETTLE_DOC
1285 --
1286 -- HISTORY
1287 --------------------------------------------------------------------------------
1288 PROCEDURE Create_Settle_Doc(
1289 itemtype IN VARCHAR2,
1290 itemkey IN VARCHAR2,
1291 actid IN NUMBER,
1292 funcmode IN VARCHAR2,
1293 resultout OUT NOCOPY VARCHAR2
1294 )
1295 IS
1296 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
1297 l_msg_data VARCHAR2(400);
1298 l_msg_count NUMBER;
1299
1300 l_trx_id NUMBER := NULL;
1301 l_trx_number_name VARCHAR2(30);
1302 l_err_trx_number_name VARCHAR2(30);
1303 l_trx_number_invalid VARCHAR2(1) := 'N';
1304 l_trx_number_all_null VARCHAR2(1) := 'Y';
1305 l_trx_number_error VARCHAR2(1) := FND_API.g_false;
1306
1307 l_claim_id NUMBER;
1308 l_org_id NUMBER;
1309 l_settle_amount NUMBER;
1310 l_settlement_doc_id NUMBER;
1311 l_settlement_method VARCHAR(15);
1312 l_settlement_doc_rec OZF_Settlement_Doc_PVT.settlement_doc_rec_type;
1313 l_settlement_doc_tbl OZF_Settlement_Doc_PVT.settlement_doc_tbl_type;
1314 l_err_msg VARCHAR2(2000);
1315 l_do_fetch VARCHAR2(1) := 'N';
1316 l_is_overpayment BOOLEAN := FALSE;
1317 l_trx_attribute_name VARCHAR2(30);
1318
1319 CURSOR csr_get_org(cv_claim_id IN NUMBER) IS
1320 SELECT org_id
1321 FROM ozf_claims_all
1322 WHERE claim_id = cv_claim_id;
1323
1324 CURSOR csr_del_settle_doc(cv_claim_id IN NUMBER) IS
1325 SELECT settlement_doc_id
1326 , object_version_number
1327 FROM ozf_settlement_docs
1328 WHERE claim_id = cv_claim_id;
1329
1330
1331 BEGIN
1332 SAVEPOINT Create_Settle_Doc;
1333 ---- RUN mode ----
1334 IF (funcmode = 'RUN') THEN
1335
1336 l_claim_id := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
1337 itemkey => itemkey,
1338 aname => 'OZF_CLAIM_ID'
1339 );
1340 l_settlement_method := WF_ENGINE.GetItemAttrText(
1341 itemtype => itemtype,
1342 itemkey => itemkey,
1343 aname => 'OZF_PAYMENT_METHOD_CODE'
1344 );
1345 l_settle_amount := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
1346 itemkey => itemkey,
1347 aname => 'OZF_AMOUNT_SETTLED'
1348 );
1349 OPEN csr_get_org(l_claim_id);
1350 FETCH csr_get_org INTO l_org_id;
1351 CLOSE csr_get_org;
1352
1353 -- set org_context since workflow mailer does not set the context
1354 --Set_Org_Ctx (l_org_id);
1355
1356 FOR i IN 1..1 LOOP
1357 l_trx_attribute_name := 'OZF_TRX_ID_'||i;
1358 l_settlement_doc_tbl(i).settlement_id := WF_ENGINE.GetItemAttrNumber(
1359 itemtype => itemtype
1360 , itemkey => itemkey
1361 , aname => l_trx_attribute_name
1362 );
1363
1364 l_trx_attribute_name := 'OZF_TRX_TYPE_'||i;
1365 l_settlement_doc_tbl(i).settlement_type := WF_ENGINE.GetItemAttrText(
1366 itemtype => itemtype
1367 , itemkey => itemkey
1368 , aname => l_trx_attribute_name
1369 );
1370 l_settlement_doc_tbl(i).settlement_type := LTRIM(RTRIM(l_settlement_doc_tbl(i).settlement_type));
1371
1372 l_trx_attribute_name := 'OZF_TRX_NUMBER_'||i;
1373 l_settlement_doc_tbl(i).settlement_number := WF_ENGINE.GetItemAttrText(
1374 itemtype => itemtype
1375 , itemkey => itemkey
1376 , aname => l_trx_attribute_name
1377 );
1378 l_settlement_doc_tbl(i).settlement_number := LTRIM(RTRIM(l_settlement_doc_tbl(i).settlement_number));
1379
1380 IF l_settlement_doc_tbl(i).settlement_number IS NOT NULL THEN
1381 l_trx_number_all_null := 'N';
1382 END IF;
1383
1384 l_trx_attribute_name := 'OZF_TRX_DATE_'||i;
1385 l_settlement_doc_tbl(i).settlement_DATE := WF_ENGINE.GetItemAttrDate(
1386 itemtype => itemtype
1387 , itemkey => itemkey
1388 , aname => l_trx_attribute_name
1389 );
1390
1391 l_trx_attribute_name := 'OZF_TRX_AMOUNT_'||i;
1392 l_settlement_doc_tbl(i).settlement_amount := WF_ENGINE.GetItemAttrNumber(
1393 itemtype => itemtype
1394 , itemkey => itemkey
1395 , aname => l_trx_attribute_name
1396 );
1397
1398 l_trx_attribute_name := 'OZF_TRX_STATUS_'||i;
1399 l_settlement_doc_tbl(i).status_code := WF_ENGINE.GetItemAttrText(
1400 itemtype => itemtype
1401 , itemkey => itemkey
1402 , aname => l_trx_attribute_name
1403 );
1404 l_settlement_doc_tbl(i).status_code := LTRIM(RTRIM(l_settlement_doc_tbl(i).status_code));
1405
1406 END LOOP;
1407
1408 IF l_trx_number_all_null = 'Y' THEN
1409 resultout := 'COMPLETE:ERROR';
1410 RETURN;
1411 END IF;
1412
1413 FOR i IN 1..1 LOOP
1414 IF l_settlement_doc_tbl(i).settlement_number IS NOT NULL THEN
1415 l_settlement_doc_tbl(i).payment_method := l_settlement_method;
1416 l_settlement_doc_tbl(i).claim_id := l_claim_id;
1417
1418 -- create ozf_setttlement_docs_all
1419 BEGIN
1420 OZF_Settlement_Doc_PVT.Create_Settlement_Doc(
1421 p_api_version_number => 1.0,
1422 p_init_msg_list => FND_API.g_false,
1423 p_commit => FND_API.g_false,
1424 p_validation_level => FND_API.g_valid_level_full,
1425 x_return_status => l_return_status,
1426 x_msg_count => l_msg_count,
1427 x_msg_data => l_msg_data,
1428 p_settlement_doc_rec => l_settlement_doc_tbl(i),
1429 x_settlement_doc_id => l_settlement_doc_id
1430 );
1431 IF l_return_status <> FND_API.g_ret_sts_success THEN
1432 resultout := 'COMPLETE:ERROR';
1433 RETURN;
1434 END IF;
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 WF_CORE.context( 'OZF_AR_SETTLEMENT_PVT',
1438 'Create_Settle_Doc'||'-'||'Create_Settlement_Doc',
1439 itemtype,
1440 itemkey,
1441 to_char(actid),
1442 funcmode
1443 );
1444 l_err_msg := SQLERRM;
1445 RAISE;
1446 END;
1447 END IF;
1448 END LOOP;
1449 resultout := 'COMPLETE:SUCCESS';
1450 RETURN;
1451 END IF;
1452
1453 ---- CANCEL mode -----
1454 IF (funcmode = 'CANCEL') THEN
1455 OPEN csr_del_settle_doc(l_claim_id);
1456 FETCH csr_del_settle_doc INTO l_settlement_doc_rec.settlement_doc_id
1457 , l_settlement_doc_rec.object_version_number;
1458 CLOSE csr_del_settle_doc;
1459
1460 BEGIN
1461 OZF_Settlement_Doc_PVT.Delete_Settlement_Doc(
1462 p_api_version_number => 1.0,
1463 p_init_msg_list => FND_API.g_false,
1464 p_commit => FND_API.g_false,
1465 p_validation_level => FND_API.g_valid_level_full,
1466 x_return_status => l_return_status,
1467 x_msg_count => l_msg_count,
1468 x_msg_data => l_msg_data,
1469 p_settlement_doc_id => l_settlement_doc_rec.settlement_doc_id,
1470 p_object_version_number => l_settlement_doc_rec.object_version_number
1471 );
1472 EXCEPTION
1473 WHEN OTHERS THEN
1474 WF_CORE.context( 'OZF_AR_SETTLEMENT_PVT',
1475 'Create_Settle_Doc'||'-'||'Delete_Settlement_Doc',
1476 itemtype,
1477 itemkey,
1478 to_char(actid),
1479 funcmode
1480 );
1481 RAISE;
1482 END;
1483
1484 resultout := 'COMPLETE:';
1485 RETURN;
1486 END IF;
1487 --
1488 ---- TIMEOUT mode ----
1489 IF (funcmode = 'TIMEOUT') THEN
1490 resultout := 'COMPLETE:';
1491 return;
1492 END IF;
1493 --
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496 ROLLBACK TO Create_Settle_Doc;
1497 WF_CORE.context(
1498 'OZF_AR_SETTLEMENT_PVT',
1499 'Create_Settle_Doc',
1500 itemtype,
1501 itemkey,
1502 to_char(actid),
1503 funcmode
1504 );
1505 RAISE;
1506 END Create_Settle_Doc;
1507
1508
1509 --------------------------------------------------------------------------------
1510 -- PROCEDURE
1511 -- Close_Claim
1512 --
1513 -- Workflow cover:
1514 -- IN
1515 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1516 -- itemkey - A string generated from the application object's primary key.
1517 -- actid - The function activity(instance id).
1518 -- funcmode - Run/Cancel/Timeout
1519 -- OUT
1520 -- Resultout - 'COMPLETE:ERROR'
1521 -- - 'COMPLETE:'
1522 --
1523 -- USED BY ACTIVITIES
1524 -- <ITEM_TYPE> <ACTIVITY>
1525 -- OZF_AR_SETTLEMENT_PVT OZF_CLOSE_CLAIM
1526 --
1527 -- HISTORY
1528 -- 04/05/2001 MCHANG CREATION.
1529 -- 11/15/2001 MCHANG Call OZF_SETTLEMENT_DOC_PVT.Update_Claim_From_Settlement()
1530 -- to update claim status to CLOSED.
1531 --------------------------------------------------------------------------------
1532 PROCEDURE Close_Claim (
1533 itemtype IN VARCHAR2,
1534 itemkey IN VARCHAR2,
1535 actid IN NUMBER,
1536 funcmode IN VARCHAR2,
1537 resultout OUT NOCOPY VARCHAR2
1538 )
1539 IS
1540 l_return_status VARCHAR2(1);
1541 l_msg_data VARCHAR2(400);
1542 l_msg_count NUMBER;
1543
1544 CURSOR csr_claim_version(cv_claim_id IN NUMBER) IS
1545 SELECT object_version_number
1546 , org_id
1547 FROM ozf_claims_all
1548 WHERE claim_id = cv_claim_id;
1549
1550 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
1551 SELECT user_status_id
1552 FROM ams_user_statuses_vl
1553 WHERE system_status_type = 'OZF_CLAIM_STATUS'
1554 AND system_status_code = cv_status_code;
1555
1556 l_user_status_id NUMBER;
1557 l_status_code VARCHAR2(30);
1558 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
1559 l_claim_id NUMBER;
1560 l_settle_amount NUMBER;
1561
1562 l_error_msg VARCHAR2(4000);
1563
1564 BEGIN
1565 ---- RUN mode ----
1566 IF (funcmode = 'RUN') THEN
1567 -- get claim_id
1568 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
1569 itemkey => itemkey,
1570 aname => 'OZF_CLAIM_ID'
1571 );
1572 -- get claim next status to update
1573 l_status_code := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
1574 itemkey => itemkey,
1575 aname => 'OZF_NEXT_STATUS'
1576 );
1577
1578 l_settle_amount := WF_ENGINE.GetItemAttrNumber(itemtype => itemtype,
1579 itemkey => itemkey,
1580 aname => 'OZF_AMOUNT_SETTLED'
1581 );
1582
1583 OZF_AR_PAYMENT_PVT.Query_Claim(
1584 p_claim_id => l_claim_id
1585 ,x_claim_rec => l_claim_rec
1586 ,x_return_status => l_return_status
1587 );
1588 IF l_return_status <> FND_API.g_ret_sts_success THEN
1589 RAISE ERROR;
1590 END IF;
1591
1592 -- set org_context since workflow mailer does not set the context
1593 --Set_Org_Ctx (l_claim_rec.org_id);
1594 OZF_Settlement_Doc_PVT.Update_Claim_From_Settlement(
1595 p_api_version_number => 1.0
1596 ,p_init_msg_list => FND_API.g_false
1597 ,p_commit => FND_API.g_false
1598 ,p_validation_level => FND_API.g_valid_level_full
1599
1600 ,x_return_status => l_return_status
1601 ,x_msg_count => l_msg_count
1602 ,x_msg_data => l_msg_data
1603
1604 ,p_claim_id => l_claim_rec.claim_id
1605 ,p_object_version_number => l_claim_rec.object_version_number
1606 ,p_status_code => l_status_code
1607 ,p_payment_status => 'PAID'
1608 );
1609 IF l_return_status <> FND_API.g_ret_sts_success THEN
1610 RAISE ERROR;
1611 END IF;
1612
1613 IF l_claim_rec.claim_class = 'DEDUCTION' AND
1614 l_claim_rec.source_object_id IS NOT NULL THEN
1615 OZF_AR_PAYMENT_PVT.update_dispute_amount(
1616 p_claim_rec => l_claim_rec
1617 ,p_dispute_amount => l_settle_amount
1618 ,x_return_status => l_return_status
1619 ,x_msg_data => l_msg_data
1620 ,x_msg_count => l_msg_count
1621 );
1622 IF l_return_status <> FND_API.g_ret_sts_success THEN
1623 RAISE ERROR;
1624 END IF;
1625 END IF;
1626
1627 IF l_claim_rec.claim_class IN ( 'DEDUCTION' , 'OVERPAYMENT') THEN
1628 OZF_AR_PAYMENT_PVT.Unapply_Claim_Investigation(
1629 p_claim_rec => l_claim_rec
1630 ,p_reapply_amount => 0
1631 ,x_return_status => l_return_status
1632 ,x_msg_data => l_msg_data
1633 ,x_msg_count => l_msg_count
1634 );
1635 IF l_return_status <> FND_API.g_ret_sts_success THEN
1636 RAISE ERROR;
1637 END IF;
1638 END IF;
1639
1640 resultout := 'COMPLETE:SUCCESS';
1641 RETURN;
1642 END IF;
1643 -- end RUN mode
1644
1645 ---- CANCEL mode -----
1646 IF (funcmode = 'CANCEL') THEN
1647 --resultout := 'COMPLETE:';
1648 RETURN;
1649 END IF;
1650 --
1651 ---- TIMEOUT mode ----
1652 IF (funcmode = 'TIMEOUT') THEN
1653 --resultout := 'COMPLETE:';
1654 return;
1655 END IF;
1656 --
1657 EXCEPTION
1658 WHEN ERROR THEN
1659 FND_MSG_PUB.count_and_get (
1660 p_encoded => FND_API.g_false
1661 ,p_count => l_msg_count
1662 ,p_data => l_msg_data
1663 );
1664 Handle_Error(
1665 p_itemtype => itemtype
1666 ,p_itemkey => itemkey
1667 ,p_msg_count => l_msg_count
1668 ,p_msg_data => l_msg_data
1669 ,p_process_name => 'CLOSE_CLAIM'
1670 ,x_error_msg => l_error_msg
1671 );
1672 WF_CORE.context(
1673 'OZF_AR_SETTLEMENT_PVT'
1674 ,'CLOSE_CLAIM'
1675 ,itemtype
1676 ,itemkey
1677 ,actid
1678 ,l_error_msg
1679 );
1680 resultout := 'COMPLETE:ERROR';
1681 RAISE;
1682
1683 WHEN OTHERS THEN
1684 WF_CORE.context(
1685 'OZF_AR_SETTLEMENT_PVT',
1686 'Close_Claim',
1687 itemtype,
1688 itemkey,
1689 to_char(actid),
1690 funcmode
1691 );
1692 RAISE;
1693 END Close_Claim;
1694
1695
1696 ------------------------------------------------------------------------------
1697 -- PROCEDURE
1698 -- Reset_Status
1699 --
1700 -- Workflow cover:
1701 -- IN
1702 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
1703 -- itemkey - A string generated from the application object's primary key.
1704 -- actid - The function activity(instance id).
1705 -- funcmode - Run/Cancel/Timeout
1706 -- OUT
1707 -- Resultout - 'COMPLETE:'
1708 --
1709 -- USED BY ACTIVITIES
1710 -- <ITEM_TYPE> <ACTIVITY>
1711 -- OZF_AR_SETTLEMENT_PVT OZF_RESET_STATUS
1712 --
1713 -- HISTORY
1714 -- 04/05/2001 MCHANG CREATION.
1715 --------------------------------------------------------------------------------
1716 PROCEDURE Reset_Status(
1717 itemtype IN VARCHAR2,
1718 itemkey IN VARCHAR2,
1719 actid IN NUMBER,
1720 funcmode IN VARCHAR2,
1721 resultout OUT NOCOPY VARCHAR2
1722 )
1723 IS
1724 l_return_status VARCHAR2(1);
1725 l_msg_data VARCHAR2(400);
1726 l_msg_count NUMBER;
1727
1728 CURSOR csr_claim_version(cv_claim_id IN NUMBER) IS
1729 SELECT object_version_number
1730 , org_id
1731 FROM ozf_claims_all
1732 WHERE claim_id = cv_claim_id;
1733
1734 CURSOR csr_user_status_id(cv_status_code IN VARCHAR2) IS
1735 SELECT user_status_id
1736 FROM ams_user_statuses_vl
1737 WHERE system_status_type = 'OZF_CLAIM_STATUS'
1738 AND system_status_code = cv_status_code;
1739
1740 l_claim_id NUMBER;
1741 l_object_version NUMBER;
1742 l_org_id NUMBER;
1743 l_user_status_id NUMBER;
1744 l_result_status VARCHAR2(15) := 'OPEN';
1745 l_claim_rec OZF_CLAIM_PVT.claim_rec_type;
1746
1747 l_error_msg VARCHAR2(4000);
1748
1749 BEGIN
1750 SAVEPOINT Reset_Status;
1751 ---- RUN mode ----
1752 IF (funcmode = 'RUN') THEN
1753 -- get claim_id
1754 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
1755 itemkey => itemkey,
1756 aname => 'OZF_CLAIM_ID'
1757 );
1758
1759 OPEN csr_claim_version(l_claim_id);
1760 FETCH csr_claim_version INTO l_object_version
1761 , l_org_id;
1762 CLOSE csr_claim_version;
1763
1764 -- set org_context since workflow mailer does not set the context
1765 --Set_Org_Ctx (l_org_id);
1766
1767 OPEN csr_user_status_id(l_result_status);
1768 FETCH csr_user_status_id INTO l_user_status_id;
1769 CLOSE csr_user_status_id;
1770
1771 l_claim_rec.claim_id := l_claim_id;
1772 l_claim_rec.object_version_number := l_object_version;
1773 --l_claim_rec.payment_status := 'PENDING';
1774 l_claim_rec.payment_status := NULL;
1775 l_claim_rec.user_status_id := l_user_status_id;
1776 l_claim_rec.status_code := l_result_status;
1777
1778 OZF_CLAIM_PVT.Update_Claim (
1779 p_api_version => 1.0
1780 ,p_init_msg_list => FND_API.g_false
1781 ,p_commit => FND_API.g_false
1782 ,p_validation_level => FND_API.g_valid_level_full
1783
1784 ,x_return_status => l_return_status
1785 ,x_msg_data => l_msg_data
1786 ,x_msg_count => l_msg_count
1787 ,p_claim => l_claim_rec
1788 ,p_event => 'UPDATE'
1789 ,p_mode => 'AUTO'
1790 ,x_object_version_number => l_object_version
1791 );
1792 IF l_return_status <> FND_API.g_ret_sts_success THEN
1793 RAISE ERROR;
1794 END IF;
1795
1796 --resultout := 'COMPLETE:';
1797 RETURN;
1798 END IF;
1799 -- end RUN mode
1800
1801 ---- CANCEL mode -----
1802 IF (funcmode = 'CANCEL') THEN
1803 --resultout := 'COMPLETE:';
1804 RETURN;
1805 END IF;
1806 --
1807 ---- TIMEOUT mode ----
1808 IF (funcmode = 'TIMEOUT') THEN
1809 --resultout := 'COMPLETE:';
1810 return;
1811 END IF;
1812 --
1813 EXCEPTION
1814 WHEN ERROR THEN
1815 ROLLBACK TO Reset_Status;
1816 FND_MSG_PUB.count_and_get (
1817 p_encoded => FND_API.g_false
1818 ,p_count => l_msg_count
1819 ,p_data => l_msg_data
1820 );
1821 Handle_Error(
1822 p_itemtype => itemtype
1823 ,p_itemkey => itemkey
1824 ,p_msg_count => l_msg_count
1825 ,p_msg_data => l_msg_data
1826 ,p_process_name => 'RESET_STATUS'
1827 ,x_error_msg => l_error_msg
1828 );
1829 WF_CORE.context(
1830 'OZF_AR_SETTLEMENT_PVT'
1831 ,'Reset_Status'
1832 ,itemtype
1833 ,itemkey
1834 ,actid
1835 ,l_error_msg
1836 );
1837 RAISE;
1838 WHEN OTHERS THEN
1839 ROLLBACK TO Reset_Status;
1840 WF_CORE.context(
1841 'OZF_AR_SETTLEMENT_PVT',
1842 'Reset_Status',
1843 itemtype,
1844 itemkey,
1845 to_char(actid),
1846 funcmode
1847 );
1848 RAISE;
1849 END Reset_Status;
1850
1851
1852 ------------------------------------------------------------------------------
1853 -- PROCEDURE
1854 -- Start_Settlement
1855 --
1856 -- IN
1857 -- p_claim_id - claim_id
1858 -- p_prev_status - previous_status
1859 -- p_curr_status - current_status
1860 -- p_next_status - next_status
1861 --
1862 -- OUT
1863 --
1864 -- HISTORY
1865 -- 04/05/2001 MCHANG CREATION.
1866 --------------------------------------------------------------------------------
1867 PROCEDURE Start_Settlement(
1868 p_claim_id IN NUMBER,
1869 p_prev_status IN VARCHAR2,
1870 p_curr_status IN VARCHAR2,
1871 p_next_status IN VARCHAR2,
1872 p_promotional_claim IN VARCHAR2 := 'N',
1873 p_process IN VARCHAR2 := 'OZF_CLAIM_GENERIC_SETTLEMENT'
1874 )
1875 IS
1876 l_api_name CONSTANT VARCHAR2(30) := 'Start_Settlement';
1877
1878 l_itemtype VARCHAR2(30) := G_ITEMTYPE;
1879 l_itemkey VARCHAR2(80);
1880 l_itemuserkey VARCHAR2(80);
1881 l_process VARCHAR2(80) := p_process;
1882 l_return_status VARCHAR2(1);
1883 l_msg_data VARCHAR2(1000);
1884 l_msg_count NUMBER;
1885
1886 CURSOR csr_claim_rec(cv_claim_id IN NUMBER) IS
1887 SELECT object_version_number
1888 , claim_number
1889 , claim_class
1890 , amount_settled
1891 , currency_code
1892 , receipt_id
1893 , receipt_number
1894 , source_object_id
1895 , source_object_number
1896 , payment_method
1897 , gl_date
1898 , effective_date
1899 , cust_account_id
1900 , cust_billto_acct_site_id
1901 , cust_shipto_acct_site_id
1902 , owner_id
1903 , org_id
1904 , claim_type_id
1905 , claim_date
1906 , due_date
1907 , reason_code_id
1908 , comments
1909 , root_claim_id
1910 , sales_rep_id
1911 , vendor_id
1912 , vendor_site_id
1913 FROM ozf_claims_all
1914 WHERE claim_id = cv_claim_id;
1915
1916 CURSOR csr_owner(cv_owner_id IN NUMBER) IS
1917 SELECT fnd.user_name
1918 FROM fnd_user fnd
1919 , ams_jtf_rs_emp_v rs
1920 WHERE rs.user_id = fnd.user_id
1921 AND rs.resource_id = cv_owner_id;
1922
1923 CURSOR csr_site(cv_site_id IN NUMBER) IS
1924 SELECT location
1925 FROM hz_cust_site_uses
1926 WHERE site_use_id = cv_site_id;
1927
1928 CURSOR csr_cust_name(cv_cust_account_id IN NUMBER) IS
1929 SELECT p.party_name,
1930 a.account_number
1931 FROM hz_cust_accounts a
1932 , hz_parties p
1933 WHERE a.party_id = p.party_id
1934 AND a.cust_account_id = cv_cust_account_id;
1935
1936 CURSOR csr_org_name(cv_org_id IN NUMBER) IS
1937 SELECT name
1938 FROM hr_all_organization_units
1939 WHERE organization_id = cv_org_id ;
1940
1941 CURSOR csr_claim_type (cv_claim_type_id IN NUMBER) IS
1942 SELECT name
1943 , cm_trx_type_id
1944 , dm_trx_type_id
1945 , cb_trx_type_id
1946 FROM ozf_claim_types_vl
1947 WHERE claim_type_id = cv_claim_type_id;
1948
1949 CURSOR csr_trx_type(cv_trx_type_id IN NUMBER) IS
1950 SELECT name
1951 FROM ra_cust_trx_types
1952 WHERE cust_trx_type_id = cv_trx_type_id;
1953
1954 -- AND transaction_type = typ.cust_trx_type_id (+) ;
1955 /*
1956 CURSOR csr_reason (cv_reason_code_id IN NUMBER ) IS
1957 SELECT name
1958 FROM ozf_reason_codes_all_vl
1959 WHERE reason_code_id = cv_reason_code_id ;
1960 */
1961 CURSOR csr_pmt_method (cv_payment_method IN VARCHAR2) IS
1962 SELECT meaning
1963 FROM ozf_lookups
1964 WHERE lookup_type = 'OZF_PAYMENT_METHOD'
1965 AND lookup_code = cv_payment_method ;
1966
1967 CURSOR csr_salesrep_name(cv_salesrep_id IN NUMBER, cv_org_id IN NUMBER) IS
1968 SELECT nvl(s.name, r.source_name)
1969 FROM jtf_rs_salesreps s
1970 , jtf_rs_resource_extns r
1971 WHERE s.resource_id = r.resource_id
1972 AND s.salesrep_id = cv_salesrep_id
1973 AND s.org_id = cv_org_id;
1974
1975 CURSOR csr_status_meaning(cv_status_code IN VARCHAR2) IS
1976 SELECT meaning
1977 FROM ozf_lookups
1978 WHERE lookup_type = 'OZF_CLAIM_STATUS'
1979 AND lookup_code = cv_status_code;
1980
1981 CURSOR csr_ar_cm_reason_code(cv_reason_code_id IN NUMBER) IS
1982 SELECT ar.meaning
1983 FROM ar_lookups ar
1984 , ozf_reason_codes_vl rs
1985 WHERE rs.reason_code_id = cv_reason_code_id
1986 AND ar.lookup_type = 'CREDIT_MEMO_REASON'
1987 AND rs.reason_code = ar.lookup_code;
1988
1989 CURSOR csr_ar_adj_reason_code(cv_reason_code_id IN NUMBER) IS
1990 SELECT ar.meaning
1991 FROM ar_lookups ar
1992 , ozf_reason_codes_vl rs
1993 WHERE rs.reason_code_id = cv_reason_code_id
1994 AND ar.lookup_type = 'ADJUST_REASON'
1995 AND rs.adjustment_reason_code = ar.lookup_code;
1996
1997 CURSOR csr_setl_doc(cv_claim_id IN NUMBER) IS
1998 SELECT payment_method
1999 , settlement_amount
2000 FROM ozf_settlement_docs
2001 WHERE claim_id = cv_claim_id
2002 AND payment_status = 'PENDING_PAID';
2003
2004 l_claim_rec csr_claim_rec%ROWTYPE;
2005 l_owner VARCHAR2(100);
2006 l_owner_name VARCHAR2(100);
2007 l_billto_site VARCHAR2(40);
2008 l_shipto_site VARCHAR2(40);
2009 l_cust_name VARCHAR2(240);
2010 l_salesrep_name VARCHAR2(240);
2011
2012 l_org_name VARCHAR2(240);
2013 l_claim_type VARCHAR2(30);
2014 l_trx_type VARCHAR2(20);
2015 --l_reason VARCHAR2(80);
2016 l_account_number VARCHAR2(30);
2017 l_payment_method VARCHAR2(80);
2018 l_payment_method_code VARCHAR2(30);
2019 l_next_status_meaning VARCHAR2(80);
2020 l_ar_reason_code VARCHAR2(80);
2021 l_cm_trx_type_id NUMBER;
2022 l_dm_trx_type_id NUMBER;
2023 l_cb_trx_type_id NUMBER;
2024 l_amount_settled NUMBER;
2025
2026 BEGIN
2027
2028 OPEN csr_claim_rec(p_claim_id);
2029 FETCH csr_claim_rec INTO l_claim_rec;
2030 IF csr_claim_rec%NOTFOUND THEN
2031 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2032 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
2033 FND_MSG_PUB.add;
2034 END IF;
2035 CLOSE csr_claim_rec;
2036 RAISE FND_API.G_EXC_ERROR;
2037 END IF;
2038 CLOSE csr_claim_rec;
2039
2040 -- set org_context since workflow mailer does not set the context
2041 --Set_Org_Ctx (l_claim_rec.org_id);
2042
2043 OPEN csr_site(l_claim_rec.cust_billto_acct_site_id);
2044 FETCH csr_site INTO l_billto_site;
2045 CLOSE csr_site;
2046
2047 OPEN csr_site(l_claim_rec.cust_shipto_acct_site_id);
2048 FETCH csr_site INTO l_shipto_site;
2049 CLOSE csr_site;
2050
2051 OPEN csr_cust_name(l_claim_rec.cust_account_id);
2052 FETCH csr_cust_name INTO l_cust_name, l_account_number;
2053 CLOSE csr_cust_name;
2054
2055 OPEN csr_org_name(l_claim_rec.org_id);
2056 FETCH csr_org_name INTO l_org_name ;
2057 CLOSE csr_org_name;
2058
2059 /*
2060 -- Transaction type should not be passed for Regular Credit Memos and should be
2061 -- passed only for On Account Credit Memos in the settlement notification.
2062 IF l_claim_rec.payment_method <> 'CREDIT_MEMO' THEN
2063 l_trx_type := NULL;
2064 END IF;
2065 */
2066 OPEN csr_claim_type(l_claim_rec.claim_type_id) ;
2067 FETCH csr_claim_type INTO l_claim_type
2068 , l_cm_trx_type_id
2069 , l_dm_trx_type_id
2070 , l_cb_trx_type_id;
2071 CLOSE csr_claim_type;
2072
2073 IF l_claim_rec.payment_method IN ('REG_CREDIT_MEMO', 'CREDIT_MEMO') THEN
2074 OPEN csr_trx_type(l_cm_trx_type_id);
2075 FETCH csr_trx_type INTO l_trx_type;
2076 CLOSE csr_trx_type;
2077 ELSIF l_claim_rec.payment_method = 'DEBIT_MEMO' THEN
2078 OPEN csr_trx_type(l_dm_trx_type_id);
2079 FETCH csr_trx_type INTO l_trx_type;
2080 CLOSE csr_trx_type;
2081 ELSIF l_claim_rec.payment_method = 'CHARGEBACK' THEN
2082 OPEN csr_trx_type(l_cb_trx_type_id);
2083 FETCH csr_trx_type INTO l_trx_type;
2084 CLOSE csr_trx_type;
2085 END IF;
2086
2087 /*
2088 OPEN csr_reason(l_claim_rec.reason_code_id);
2089 FETCH csr_reason INTO l_reason ;
2090 CLOSE csr_reason ;
2091 */
2092
2093 OPEN csr_pmt_method(l_claim_rec.payment_method);
2094 FETCH csr_pmt_method INTO l_payment_method;
2095 CLOSE csr_pmt_method;
2096
2097 OPEN csr_salesrep_name(l_claim_rec.sales_rep_id, l_claim_rec.org_id);
2098 FETCH csr_salesrep_name INTO l_salesrep_name;
2099 CLOSE csr_salesrep_name;
2100
2101 OPEN csr_owner(l_claim_rec.owner_id);
2102 FETCH csr_owner INTO l_owner_name;
2103 CLOSE csr_owner;
2104
2105 OPEN csr_status_meaning(p_next_status);
2106 FETCH csr_status_meaning INTO l_next_status_meaning;
2107 CLOSE csr_status_meaning;
2108
2109 IF l_claim_rec.payment_method IN ('CREDIT_MEMO', 'REG_CREDIT_MEMO') THEN
2110 OPEN csr_ar_cm_reason_code(l_claim_rec.reason_code_id);
2111 FETCH csr_ar_cm_reason_code INTO l_ar_reason_code;
2112 CLOSE csr_ar_cm_reason_code;
2113 ELSIF l_claim_rec.payment_method IN ('WRITE_OFF', 'CHARGEBACK') THEN
2114 OPEN csr_ar_adj_reason_code(l_claim_rec.reason_code_id);
2115 FETCH csr_ar_adj_reason_code INTO l_ar_reason_code;
2116 CLOSE csr_ar_adj_reason_code;
2117 END IF;
2118
2119
2120 -- set the itemkey and itemuserkey
2121 l_itemkey := p_claim_id||'_'||l_claim_rec.object_version_number;
2122 l_itemuserkey := l_claim_rec.claim_number||'_'||
2123 l_claim_rec.receipt_number||'_'||
2124 l_claim_rec.object_version_number;
2125
2126 -- creaye a new process
2127 WF_ENGINE.CreateProcess( itemType => l_itemtype,
2128 itemKey => l_itemkey,
2129 process => l_process
2130 );
2131 --
2132 -- set the user key for process
2133 WF_ENGINE.SetItemUserKey( itemType => l_itemtype,
2134 itemKey => l_itemkey,
2135 userKey => l_itemuserkey
2136 );
2137 --
2138
2139 Get_User_Role(
2140 p_user_id => l_claim_rec.owner_id
2141 ,x_role_name => l_owner
2142 ,x_role_display_name => l_owner_name
2143 );
2144 -- set the process owner (l_claim_owner)
2145 WF_ENGINE.SetItemOwner( itemtype => l_itemtype,
2146 itemkey => l_itemkey,
2147 owner => l_owner
2148 );
2149 --
2150
2151 ----------------- Set Attributes ---------------------------
2152 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2153 itemkey => l_itemkey,
2154 aname => 'OZF_CLAIM_ID',
2155 avalue => p_claim_id
2156 );
2157 --
2158 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2159 itemkey => l_itemkey,
2160 aname => 'OZF_CLAIM_NUMBER',
2161 avalue => l_claim_rec.claim_number
2162 );
2163 --
2164 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2165 itemkey => l_itemkey,
2166 aname => 'OZF_CLAIM_CLASS',
2167 avalue => l_claim_rec.claim_class
2168 );
2169 --
2170 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2171 itemkey => l_itemkey,
2172 aname => 'OZF_AMOUNT_SETTLED',
2173 avalue => l_claim_rec.amount_settled
2174 );
2175 --
2176 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2177 itemkey => l_itemkey,
2178 aname => 'OZF_CURRENCY_CODE',
2179 avalue => l_claim_rec.currency_code
2180 );
2181 --
2182 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2183 itemkey => l_itemkey,
2184 aname => 'OZF_RECEIPT_ID',
2185 avalue => l_claim_rec.receipt_id
2186 );
2187 --
2188 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2189 itemkey => l_itemkey,
2190 aname => 'OZF_RECEIPT_NUMBER',
2191 avalue => l_claim_rec.receipt_number
2192 );
2193 --
2194 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2195 itemkey => l_itemkey,
2196 aname => 'OZF_INVOICE_ID',
2197 avalue => l_claim_rec.source_object_id
2198 );
2199 --
2200 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2201 itemkey => l_itemkey,
2202 aname => 'OZF_INVOICE_NUMBER',
2203 avalue => l_claim_rec.source_object_number
2204 );
2205 --
2206 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2207 itemkey => l_itemkey,
2208 aname => 'OZF_PAYMENT_METHOD',
2209 avalue => l_payment_method
2210 );
2211 --
2212 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2213 itemkey => l_itemkey,
2214 aname => 'OZF_PAYMENT_METHOD_CODE',
2215 avalue => l_claim_rec.payment_method
2216 );
2217 --
2218 WF_ENGINE.SetItemAttrDate( itemtype => l_itemtype,
2219 itemkey => l_itemkey,
2220 aname => 'OZF_GL_DATE',
2221 avalue => l_claim_rec.gl_date
2222 );
2223 --
2224 WF_ENGINE.SetItemAttrDate( itemtype => l_itemtype,
2225 itemkey => l_itemkey,
2226 aname => 'OZF_EFFECTIVE_DATE',
2227 avalue => l_claim_rec.effective_date
2228 );
2229 --
2230 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2231 itemkey => l_itemkey,
2232 aname => 'OZF_CUST_ACCOUNT_ID',
2233 avalue => l_claim_rec.cust_account_id
2234 );
2235 --
2236 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2237 itemkey => l_itemkey,
2238 aname => 'OZF_ACCOUNT_NAME',
2239 avalue => l_cust_name
2240 );
2241 --
2242 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2243 itemkey => l_itemkey,
2244 aname => 'OZF_BILLTO_ACCT_SITE_ID',
2245 avalue => l_claim_rec.cust_billto_acct_site_id
2246 );
2247 --
2248 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2249 itemkey => l_itemkey,
2250 aname => 'OZF_BILLTO_SITE',
2251 avalue => l_billto_site
2252 );
2253 --
2254 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2255 itemkey => l_itemkey,
2256 aname => 'OZF_SHIPTO_ACCT_SITE_ID',
2257 avalue => l_claim_rec.cust_shipto_acct_site_id
2258 );
2259 --
2260 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2261 itemkey => l_itemkey,
2262 aname => 'OZF_SHIPTO_SITE',
2263 avalue => l_shipto_site
2264 );
2265 --
2266 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2267 itemkey => l_itemkey,
2268 aname => 'OZF_CLAIM_OWNER_ID',
2269 avalue => l_claim_rec.owner_id
2270 );
2271 --
2272 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
2273 itemkey => l_itemkey,
2274 aname => 'OZF_CLAIM_OWNER',
2275 avalue => l_owner
2276 );
2277 --
2278 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2279 itemkey => l_itemkey,
2280 aname => 'OZF_PREVIOUS_STATUS',
2281 avalue => p_prev_status
2282 );
2283 --
2284 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2285 itemkey => l_itemkey,
2286 aname => 'OZF_CURRENT_STATUS',
2287 avalue => p_curr_status
2288 );
2289 --
2290 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2291 itemkey => l_itemkey,
2292 aname => 'OZF_NEXT_STATUS',
2293 avalue => p_next_status
2294 );
2295
2296 -- New attr added 12-Aug
2297
2298 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2299 itemkey => l_itemkey,
2300 aname => 'OZF_ORG_NAME',
2301 avalue => l_org_name
2302 );
2303 --
2304 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2305 itemkey => l_itemkey,
2306 aname => 'OZF_CLAIM_TYPE',
2307 avalue => l_claim_type
2308 );
2309 --
2310 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2311 itemkey => l_itemkey,
2312 aname => 'OZF_CLAIM_DATE',
2313 avalue => l_claim_rec.claim_date
2314 );
2315 --
2316 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2317 itemkey => l_itemkey,
2318 aname => 'OZF_DUE_DATE',
2319 avalue => l_claim_rec.due_date
2320 );
2321 --
2322 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2323 itemkey => l_itemkey,
2324 aname => 'OZF_ACCOUNT_NUMBER',
2325 avalue => l_account_number
2326 );
2327 --
2328 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2329 itemkey => l_itemkey,
2330 aname => 'OZF_REASON',
2331 avalue => l_ar_reason_code
2332 );
2333 --
2334 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2335 itemkey => l_itemkey,
2336 aname => 'OZF_TRX_TYPE',
2337 avalue => l_trx_type
2338 );
2339 --
2340 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2341 itemkey => l_itemkey,
2342 aname => 'OZF_COMMENTS',
2343 avalue => l_claim_rec.comments
2344 );
2345 --
2346 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2347 itemkey => l_itemkey,
2348 aname => 'OZF_SALESREP_ID',
2349 avalue => l_claim_rec.sales_rep_id
2350 );
2351 --
2352 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2353 itemkey => l_itemkey,
2354 aname => 'OZF_SALESREP_NAME',
2355 avalue => l_salesrep_name
2356 );
2357 --
2358 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2359 itemkey => l_itemkey,
2360 aname => 'OZF_CLAIM_OWNER_NAME',
2361 avalue => l_owner_name
2362 );
2363 --
2364 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2365 itemkey => l_itemkey,
2366 aname => 'OZF_NEXT_STATUS_MEANING',
2367 avalue => l_next_status_meaning
2368 );
2369 --
2370 IF p_promotional_claim = 'Y' THEN
2371 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2372 itemkey => l_itemkey,
2373 aname => 'OZF_PROMO_CLAIM_FLAG',
2374 avalue => 'Y'
2375 );
2376 END IF;
2377
2378 --
2379 IF l_claim_rec.payment_method = 'MASS_SETTLEMENT' THEN
2380 OPEN csr_setl_doc(p_claim_id);
2381 FETCH csr_setl_doc INTO l_payment_method_code
2382 , l_amount_settled;
2383 CLOSE csr_setl_doc;
2384 l_amount_settled := l_amount_settled * -1;
2385
2386 OPEN csr_pmt_method(l_payment_method_code);
2387 FETCH csr_pmt_method INTO l_payment_method;
2388 CLOSE csr_pmt_method;
2389
2390 --
2391 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2392 itemkey => l_itemkey,
2393 aname => 'OZF_PAYMENT_METHOD',
2394 avalue => l_payment_method
2395 );
2396 --
2397 WF_ENGINE.SetItemAttrText( itemtype => l_itemtype,
2398 itemkey => l_itemkey,
2399 aname => 'OZF_PAYMENT_METHOD_CODE',
2400 avalue => l_payment_method_code
2401 );
2402 --
2403 WF_ENGINE.SetItemAttrNumber( itemtype => l_itemtype,
2404 itemkey => l_itemkey,
2405 aname => 'OZF_AMOUNT_SETTLED',
2406 avalue => l_amount_settled
2407 );
2408 END IF;
2409
2410
2411 --
2412 -- set AR Role
2413 -- Get more details for the current activity
2414 /*
2415 BEGIN
2416 Get_AR_Role(
2417 p_activity_id => ,
2418 x_return_status => l_return_status
2419 );
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2423 FND_MESSAGE.Set_Token('TEXT', sqlerrm || l_budget_type);
2424 FND_MSG_PUB.Add;
2425 RAISE FND_API.G_EXC_ERROR;
2426 END;
2427
2428 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2429 RAISE FND_API.G_EXC_ERROR;
2430 END IF;
2431 */
2432 /*
2433 WF_ENGINE.SetItemAttrText(itemtype => l_itemtype,
2434 itemkey => l_itemkey,
2435 aname => 'OZF_RECEIVABLE_DEPT',
2436 avalue =>
2437 );
2438 */
2439
2440 --kdass - Bug 9137090 - commented since this debug message introduced junk character in the out param l_msg_data
2441 --OZF_Utility_PVT.debug_message('Start == Item Type:'||l_itemtype||'-- Item key:'||l_itemkey);
2442 BEGIN
2443 UPDATE ozf_claims_all
2444 SET cstl_wf_item_key = l_itemkey
2445 WHERE claim_id = p_claim_id;
2446 EXCEPTION
2447 WHEN OTHERS THEN
2448 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2449 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_UPD_CLAM_ERR');
2450 FND_MSG_PUB.add;
2451 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2452 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2453 FND_MSG_PUB.Add;
2454 END IF;
2455 END;
2456 -------------- start the process ---------------------
2457 WF_ENGINE.StartProcess( itemtype => l_itemtype,
2458 itemkey => l_itemkey
2459 );
2460 --
2461
2462 EXCEPTION
2463 WHEN FND_API.G_EXC_ERROR THEN
2464 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2465 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2466 FND_MESSAGE.Set_Token('TEXT',G_PKG_NAME||'.'||l_api_name||': Error');
2467 FND_MSG_PUB.Add;
2468 END IF;
2469 RAISE;
2470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2471 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2472 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2473 FND_MESSAGE.Set_Token('TEXT',G_PKG_NAME||'.'||l_api_name||': Error');
2474 FND_MSG_PUB.Add;
2475 END IF;
2476 RAISE;
2477 WHEN OTHERS THEN
2478 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2479 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2480 END IF;
2481 RAISE;
2482 END Start_Settlement;
2483
2484 ------------------------------------------------------------------------------
2485 -- PROCEDURE
2486 -- Check_Promo_Claim
2487 --
2488 -- Workflow cover:
2489 -- IN
2490 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2491 -- itemkey - A string generated from the application object's primary key.
2492 -- actid - The function activity(instance id).
2493 -- funcmode - Run/Cancel/Timeout
2494 -- OUT
2495 -- Resultout - 'COMPLETE:YES'
2496 -- - 'COMPLETE:NO'
2497 --
2498 -- USED BY ACTIVITIES
2499 -- <ITEM_TYPE> <ACTIVITY>
2500 -- OZF_AR_SETTLEMENT_PVT OZF_CHECK_PROMO_CLAIM
2501 ---------------------------------------------------------------------------------
2502 PROCEDURE Check_Promo_Claim(
2503 itemtype IN VARCHAR2,
2504 itemkey IN VARCHAR2,
2505 actid IN NUMBER,
2506 funcmode IN VARCHAR2,
2507 resultout OUT NOCOPY VARCHAR2
2508 )
2509 IS
2510 l_promo_flag VARCHAR2(3);
2511
2512 -- Bug4308173
2513 CURSOR claim_gl_posting_csr(p_id in number) IS
2514 SELECT osp.post_to_gl
2515 FROM ozf_sys_parameters_all osp
2516 , ozf_claims_all oc
2517 WHERE osp.org_id = oc.org_id
2518 AND oc.claim_id = p_id;
2519 l_post_to_gl VARCHAR2(1);
2520 l_claim_id NUMBER;
2521
2522 BEGIN
2523 ---- RUN mode ----
2524 IF (funcmode = 'RUN') THEN
2525 -- get claim_id
2526 l_promo_flag := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2527 itemkey => itemkey,
2528 aname => 'OZF_PROMO_CLAIM_FLAG'
2529 );
2530
2531 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2532 itemkey => itemkey,
2533 aname => 'OZF_CLAIM_ID'
2534 );
2535
2536 OPEN claim_gl_posting_csr(l_claim_id);
2537 FETCH claim_gl_posting_csr INTO l_post_to_gl;
2538 CLOSE claim_gl_posting_csr;
2539
2540 IF l_promo_flag = 'Y' AND NVL(l_post_to_gl,'F') = 'T' THEN
2541 resultout := 'COMPLETE:Y';
2542 ELSE
2543 resultout := 'COMPLETE:N';
2544 END IF;
2545
2546 RETURN;
2547 END IF;
2548 -- end RUN mode
2549
2550 ---- CANCEL mode -----
2551 IF (funcmode = 'CANCEL') THEN
2552 resultout := 'COMPLETE:';
2553 RETURN;
2554 END IF;
2555 --
2556 ---- TIMEOUT mode ----
2557 IF (funcmode = 'TIMEOUT') THEN
2558 resultout := 'COMPLETE:';
2559 return;
2560 END IF;
2561 --
2562 EXCEPTION
2563 WHEN OTHERS THEN
2564 WF_CORE.context(
2565 'OZF_AR_SETTLEMENT_PVT',
2566 'Check_Promo_Claim',
2567 itemtype,
2568 itemkey,
2569 to_char(actid),
2570 funcmode
2571 );
2572 RAISE;
2573 END Check_Promo_Claim;
2574
2575
2576 ------------------------------------------------------------------------------
2577 -- PROCEDURE
2578 -- Create_GL_Entries
2579 --
2580 -- Workflow cover:
2581 -- IN
2582 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2583 -- itemkey - A string generated from the application object's primary key.
2584 -- actid - The function activity(instance id).
2585 -- funcmode - Run/Cancel/Timeout
2586 -- OUT
2587 -- Resultout - 'COMPLETE:SUCCESS'
2588 -- - 'COMPLETE:ERROR'
2589 --
2590 -- USED BY ACTIVITIES
2591 -- <ITEM_TYPE> <ACTIVITY>
2592 -- OZF_AR_SETTLEMENT_PVT OZF_CREATE_GL_ENTRIES
2593 ---------------------------------------------------------------------------------
2594 PROCEDURE Create_GL_Entries(
2595 itemtype IN VARCHAR2,
2596 itemkey IN VARCHAR2,
2597 actid IN NUMBER,
2598 funcmode IN VARCHAR2,
2599 resultout OUT NOCOPY VARCHAR2
2600 )
2601 IS
2602 l_return_status VARCHAR2(1);
2603 l_msg_data VARCHAR2(400);
2604 l_msg_count NUMBER;
2605 l_api_version CONSTANT NUMBER := 1.0;
2606 l_event_id NUMBER;
2607 l_ccid NUMBER;
2608
2609 l_claim_id NUMBER;
2610 l_claim_class VARCHAR2(30);
2611 l_payment_method VARCHAR2(30);
2612
2613 l_error_msg VARCHAR2(4000);
2614
2615 BEGIN
2616
2617 SAVEPOINT Create_GL_Entries;
2618 ---- RUN mode ----
2619 IF (funcmode = 'RUN') THEN
2620 -- get claim_id
2621 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2622 itemkey => itemkey,
2623 aname => 'OZF_CLAIM_ID'
2624 );
2625
2626 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2627 itemkey => itemkey,
2628 aname => 'OZF_PAYMENT_METHOD_CODE'
2629 );
2630
2631 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2632 itemkey => itemkey,
2633 aname => 'OZF_CLAIM_CLASS'
2634 );
2635 -- ER#9382547 ChRM-SLA Uptake: Removed the Event_ID, x_clear_code_combination_id
2636 -- Out Parameter and claim_class as IN Parameter
2637 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL(
2638 p_api_version => 1.0,
2639 x_return_status => l_return_status,
2640 x_msg_data => l_msg_data,
2641 x_msg_count => l_msg_count,
2642 p_claim_id => l_claim_id,
2643 p_settlement_method => l_payment_method
2644 );
2645 IF l_return_status <> FND_API.g_ret_sts_success THEN
2646 RAISE ERROR;
2647 ELSE
2648 resultout := 'COMPLETE:SUCCESS';
2649 END IF;
2650 RETURN;
2651 END IF;
2652
2653 -- end RUN mode
2654
2655 ---- CANCEL mode -----
2656 IF (funcmode = 'CANCEL') THEN
2657 --resultout := 'COMPLETE:';
2658 RETURN;
2659 END IF;
2660 --
2661 ---- TIMEOUT mode ----
2662 IF (funcmode = 'TIMEOUT') THEN
2663 --resultout := 'COMPLETE:';
2664 return;
2665 END IF;
2666 --
2667 EXCEPTION
2668 WHEN ERROR THEN
2669 ROLLBACK TO Create_GL_Entries;
2670 FND_MSG_PUB.count_and_get (
2671 p_encoded => FND_API.g_false
2672 ,p_count => l_msg_count
2673 ,p_data => l_msg_data
2674 );
2675 Handle_Error(
2676 p_itemtype => itemtype
2677 ,p_itemkey => itemkey
2678 ,p_msg_count => l_msg_count
2679 ,p_msg_data => l_msg_data
2680 ,p_process_name => 'Create_GL_Entrie'
2681 ,x_error_msg => l_error_msg
2682 );
2683 WF_CORE.context(
2684 'OZF_AR_SETTLEMENT_PVT'
2685 ,'Create_GL_Entries'
2686 ,itemtype
2687 ,itemkey
2688 ,actid
2689 ,l_error_msg
2690 );
2691 resultout := 'COMPLETE:ERROR';
2692 RETURN;
2693
2694 WHEN OTHERS THEN
2695 ROLLBACK TO Create_GL_Entries;
2696 WF_CORE.context(
2697 'OZF_AR_SETTLEMENT_PVT',
2698 'Create_GL_Entries',
2699 itemtype,
2700 itemkey,
2701 to_char(actid),
2702 funcmode
2703 );
2704 RAISE;
2705 END Create_GL_Entries;
2706
2707
2708 ------------------------------------------------------------------------------
2709 -- PROCEDURE
2710 -- Revert_GL_Entries
2711 --
2712 -- Workflow cover:
2713 -- IN
2714 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2715 -- itemkey - A string generated from the application object's primary key.
2716 -- actid - The function activity(instance id).
2717 -- funcmode - Run/Cancel/Timeout
2718 -- OUT
2719 -- Resultout - 'COMPLETE:SUCCESS'
2720 -- - 'COMPLETE:ERROR'
2721 --
2722 ---------------------------------------------------------------------------------
2723 PROCEDURE Revert_GL_Entries(
2724 itemtype IN VARCHAR2,
2725 itemkey IN VARCHAR2,
2726 actid IN NUMBER,
2727 funcmode IN VARCHAR2,
2728 resultout OUT NOCOPY VARCHAR2
2729 )
2730 IS
2731 l_return_status VARCHAR2(1);
2732 l_msg_data VARCHAR2(400);
2733 l_msg_count NUMBER;
2734 l_api_version CONSTANT NUMBER := 1.0;
2735 l_event_id NUMBER;
2736 l_ccid NUMBER;
2737
2738 l_claim_id NUMBER;
2739 l_error_msg VARCHAR2(4000);
2740
2741 BEGIN
2742 SAVEPOINT Revert_GL_Entries;
2743 ---- RUN mode ----
2744 IF (funcmode = 'RUN') THEN
2745 -- get claim_id
2746 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2747 itemkey => itemkey,
2748 aname => 'OZF_CLAIM_ID'
2749 );
2750
2751
2752 /*OZF_GL_INTERFACE_PVT.Revert_GL_Entry (
2753 p_api_version => 1.0,
2754 x_return_status => l_return_status,
2755 x_msg_data => l_msg_data,
2756 x_msg_count => l_msg_count,
2757 p_claim_id => l_claim_id
2758 );
2759 */
2760
2761 --ER#9382547 ChRM-SLA Uptake
2762 -- For Reverting the GL entry,need to pass the
2763 -- reversal event type for the corresponding claim_id
2764 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL (
2765 p_api_version => 1.0
2766 ,p_init_msg_list =>FND_API.G_FALSE
2767 ,p_commit => FND_API.G_FALSE
2768 ,p_validation_level =>FND_API.G_VALID_LEVEL_FULL
2769 ,x_return_status =>l_return_status
2770 ,x_msg_data =>l_msg_data
2771 ,x_msg_count =>l_msg_count
2772 ,p_claim_id => l_claim_id
2773 ,p_settlement_method => 'CLAIM_SETTLEMENT_REVERSAL'
2774 );
2775
2776 IF l_return_status <> FND_API.g_ret_sts_success THEN
2777 RAISE ERROR;
2778 END IF;
2779 RETURN;
2780 END IF;
2781 -- end RUN mode
2782
2783 ---- CANCEL mode -----
2784 IF (funcmode = 'CANCEL') THEN
2785 --resultout := 'COMPLETE:';
2786 RETURN;
2787 END IF;
2788 --
2789 ---- TIMEOUT mode ----
2790 IF (funcmode = 'TIMEOUT') THEN
2791 --resultout := 'COMPLETE:';
2792 return;
2793 END IF;
2794 --
2795 EXCEPTION
2796 WHEN ERROR THEN
2797 ROLLBACK TO Revert_GL_Entries;
2798 FND_MSG_PUB.count_and_get (
2799 p_encoded => FND_API.g_false
2800 ,p_count => l_msg_count
2801 ,p_data => l_msg_data
2802 );
2803 Handle_Error(
2804 p_itemtype => itemtype
2805 ,p_itemkey => itemkey
2806 ,p_msg_count => l_msg_count
2807 ,p_msg_data => l_msg_data
2808 ,p_process_name => 'Revert_GL_Entries'
2809 ,x_error_msg => l_error_msg
2810 );
2811 WF_CORE.context(
2812 'OZF_AR_SETTLEMENT_PVT'
2813 ,'Revert_GL_Entries'
2814 ,itemtype
2815 ,itemkey
2816 ,actid
2817 ,l_error_msg
2818 );
2819 RAISE;
2820
2821 WHEN OTHERS THEN
2822 ROLLBACK TO Revert_GL_Entries;
2823 WF_CORE.context(
2824 'OZF_AR_SETTLEMENT_PVT',
2825 'Revert_GL_Entries',
2826 itemtype,
2827 itemkey,
2828 to_char(actid),
2829 funcmode
2830 );
2831 RAISE;
2832 END Revert_GL_Entries;
2833
2834
2835 ------------------------------------------------------------------------------
2836 -- PROCEDURE
2837 -- Create_Payment
2838 --
2839 -- Workflow cover:
2840 -- IN
2841 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2842 -- itemkey - A string generated from the application object's primary key.
2843 -- actid - The function activity(instance id).
2844 -- funcmode - Run/Cancel/Timeout
2845 -- OUT
2846 -- Resultout - 'COMPLETE:SUCCESS'
2847 -- - 'COMPLETE:ERROR'
2848 --
2849 -- USED BY ACTIVITIES
2850 -- <ITEM_TYPE> <ACTIVITY>
2851 -- OZF_AR_SETTLEMENT_PVT OZF_CREATE_PAYMENT
2852 ---------------------------------------------------------------------------------
2853 PROCEDURE Create_Payment(
2854 itemtype IN VARCHAR2,
2855 itemkey IN VARCHAR2,
2856 actid IN NUMBER,
2857 funcmode IN VARCHAR2,
2858 resultout OUT NOCOPY VARCHAR2
2859 )
2860 IS
2861 l_return_status VARCHAR2(1);
2862 l_msg_data VARCHAR2(4000);
2863 l_msg_count NUMBER;
2864
2865 l_api_version CONSTANT NUMBER := 1.0;
2866 l_api_name CONSTANT VARCHAR2(30) := 'Create_Payment_for_Settlement';
2867 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2868 l_claim_id NUMBER;
2869 l_object_version NUMBER;
2870 l_org_id NUMBER;
2871 l_error_msg VARCHAR2(4000);
2872 l_payment_method VARCHAR2(30);
2873
2874 CURSOR csr_claim_version(cv_claim_id IN NUMBER) IS
2875 SELECT object_version_number
2876 , org_id
2877 FROM ozf_claims_all
2878 WHERE claim_id = cv_claim_id;
2879
2880 CURSOR csr_claim_settle(cv_claim_id IN NUMBER) IS
2881 SELECT claim_number
2882 , settled_date
2883 , vendor_id
2884 , vendor_site_id
2885 , amount_settled
2886 , currency_code
2887 , exchange_rate
2888 , exchange_rate_type
2889 , exchange_rate_date
2890 , payment_method
2891 , set_of_books_id
2892 , gl_date
2893 , claim_class
2894 , payment_reference_id
2895 FROM ozf_claims
2896 WHERE claim_id = cv_claim_id;
2897
2898
2899 l_claim_settle csr_claim_settle%ROWTYPE;
2900 l_payment_term NUMBER;
2901 l_settlement_doc_rec OZF_SETTLEMENT_DOC_PVT.settlement_doc_rec_type;
2902 l_automate_settlement VARCHAR2(1);
2903 BEGIN
2904 SAVEPOINT create_payment;
2905 ---- RUN mode ----
2906 IF (funcmode = 'RUN') THEN
2907 -- get claim_id
2908 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2909 itemkey => itemkey,
2910 aname => 'OZF_CLAIM_ID'
2911 );
2912
2913 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2914 itemkey => itemkey,
2915 aname => 'OZF_PAYMENT_METHOD_CODE'
2916 );
2917
2918 OPEN csr_claim_version(l_claim_id);
2919 FETCH csr_claim_version INTO l_object_version
2920 , l_org_id;
2921 CLOSE csr_claim_version;
2922
2923
2924 OPEN csr_claim_settle(l_claim_id);
2925 FETCH csr_claim_settle INTO l_claim_settle;
2926 CLOSE csr_claim_settle;
2927
2928
2929 --------------------------- CHECK -----------------------------
2930 IF l_claim_settle.payment_method in ( 'CHECK','EFT','WIRE', 'AP_DEBIT','AP_DEFAULT') THEN
2931 -- create AP invoice
2932 OZF_AP_INTERFACE_PVT.Create_AP_Invoice (
2933 p_api_version => l_api_version
2934 ,p_init_msg_list => FND_API.g_false
2935 ,p_commit => FND_API.g_false
2936 ,p_validation_level => FND_API.g_valid_level_full
2937 ,x_return_status => l_return_status
2938 ,x_msg_data => l_msg_data
2939 ,x_msg_count => l_msg_count
2940 ,p_claim_id => l_claim_id
2941 );
2942 IF l_return_status <> FND_API.g_ret_sts_success THEN
2943 RAISE ERROR;
2944 ELSE
2945 resultout := 'COMPLETE:SUCCESS';
2946 END IF;
2947
2948 ----------------------- CREDIT_MEMO -----------------------------
2949 ELSIF l_claim_settle.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO') THEN
2950 OZF_AR_PAYMENT_PVT.Create_AR_Payment(
2951 p_api_version => l_api_version
2952 ,p_init_msg_list => FND_API.g_false
2953 ,p_commit => FND_API.g_false
2954 ,p_validation_level => FND_API.g_valid_level_full
2955 ,x_return_status => l_return_status
2956 ,x_msg_data => l_msg_data
2957 ,x_msg_count => l_msg_count
2958 ,p_claim_id => l_claim_id
2959 );
2960 IF l_return_status <> FND_API.g_ret_sts_success THEN
2961 RAISE ERROR;
2962 ELSE
2963 resultout := 'COMPLETE:SUCCESS';
2964 END IF;
2965 ELSE
2966 resultout := 'COMPLETE:ERROR';
2967 END IF;
2968 RETURN;
2969 END IF;
2970 -- end RUN mode
2971
2972
2973
2974 ---- CANCEL mode -----
2975 IF (funcmode = 'CANCEL') THEN
2976 --resultout := 'COMPLETE:';
2977 RETURN;
2978 END IF;
2979 --
2980 ---- TIMEOUT mode ----
2981 IF (funcmode = 'TIMEOUT') THEN
2982 --resultout := 'COMPLETE:';
2983 return;
2984 END IF;
2985 --
2986 EXCEPTION
2987 WHEN ERROR THEN
2988 ROLLBACK TO create_payment;
2989 FND_MSG_PUB.count_and_get (
2990 p_encoded => FND_API.g_false
2991 ,p_count => l_msg_count
2992 ,p_data => l_msg_data
2993 );
2994 Handle_Error(
2995 p_itemtype => itemtype
2996 ,p_itemkey => itemkey
2997 ,p_msg_count => l_msg_count
2998 ,p_msg_data => l_msg_data
2999 ,p_process_name => 'CREATE_PAYMENT'
3000 ,x_error_msg => l_error_msg
3001 );
3002 WF_CORE.context(
3003 'OZF_AR_SETTLEMENT_PVT'
3004 ,'CREATE_PAYMENT'
3005 ,itemtype
3006 ,itemkey
3007 ,actid
3008 ,l_error_msg
3009 );
3010 resultout := 'COMPLETE:ERROR';
3011 RETURN;
3012 WHEN OTHERS THEN
3013 ROLLBACK TO create_payment;
3014 WF_CORE.context(
3015 'OZF_AR_SETTLEMENT_PVT',
3016 'Create_Payment',
3017 itemtype,
3018 itemkey,
3019 to_char(actid),
3020 funcmode
3021 );
3022 RAISE;
3023 END Create_Payment;
3024
3025
3026 ------------------------------------------------------------------------------
3027 -- PROCEDURE
3028 -- Check_Claim_Class
3029 --
3030 -- Workflow cover:
3031 -- IN
3032 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
3033 -- itemkey - A string generated from the application object's primary key.
3034 -- actid - The function activity(instance id).
3035 -- funcmode - Run/Cancel/Timeout
3036 -- OUT
3037 -- Resultout - 'COMPLETE:MANUAL'
3038 -- - 'COMPLETE:AUTO'
3039 -- - 'COMPLETE:END'
3040 --
3041 -- USED BY ACTIVITIES
3042 -- <ITEM_TYPE> <ACTIVITY>
3043 -- OZF_AR_SETTLEMENT_PVT CHECK_CLAIM_CLASS
3044 ---------------------------------------------------------------------------------
3045 PROCEDURE Check_Claim_Class(
3046 itemtype IN VARCHAR2,
3047 itemkey IN VARCHAR2,
3048 actid IN NUMBER,
3049 funcmode IN VARCHAR2,
3050 resultout OUT NOCOPY VARCHAR2
3051 )
3052 IS
3053 l_claim_class VARCHAR2(30);
3054
3055 BEGIN
3056 ---- RUN mode ----
3057 IF (funcmode = 'RUN') THEN
3058 -- get claim_id
3059 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3060 itemkey => itemkey,
3061 aname => 'OZF_CLAIM_CLASS'
3062 );
3063 IF l_claim_class = 'CLAIM' THEN
3064 resultout := 'COMPLETE:CLAIM';
3065 ELSE
3066 resultout := 'COMPLETE:DEDUCTION';
3067 END IF;
3068 RETURN;
3069 END IF;
3070 -- end RUN mode
3071
3072 ---- CANCEL mode -----
3073 IF (funcmode = 'CANCEL') THEN
3074 --resultout := 'COMPLETE:';
3075 RETURN;
3076 END IF;
3077 --
3078 ---- TIMEOUT mode ----
3079 IF (funcmode = 'TIMEOUT') THEN
3080 --resultout := 'COMPLETE:';
3081 return;
3082 END IF;
3083 --
3084 EXCEPTION
3085 WHEN OTHERS THEN
3086 WF_CORE.context(
3087 'OZF_AR_SETTLEMENT_PVT',
3088 'Check_Claim_Class',
3089 itemtype,
3090 itemkey,
3091 to_char(actid),
3092 funcmode
3093 );
3094 RAISE;
3095 END Check_Claim_Class;
3096
3097
3098
3099 PROCEDURE Handle_Error(
3100 p_itemtype IN VARCHAR2,
3101 p_itemkey IN VARCHAR2,
3102 p_msg_count IN NUMBER,
3103 p_msg_data IN VARCHAR2,
3104 p_process_name IN VARCHAR2,
3105 x_error_msg OUT NOCOPY VARCHAR2
3106 )
3107 IS
3108 l_msg_count NUMBER ;
3109 l_msg_data VARCHAR2(2000);
3110 l_final_msg VARCHAR2(4000);
3111 l_msg_index NUMBER ;
3112 l_err_subj VARCHAR2(240);
3113 l_claim_number VARCHAR2(30);
3114
3115
3116 BEGIN
3117 l_claim_number := WF_ENGINE.GetItemAttrText(
3118 itemtype => p_itemtype,
3119 itemkey => p_itemkey,
3120 aname => 'OZF_CLAIM_NUMBER'
3121 );
3122
3123 FND_MESSAGE.set_name ('OZF', 'OZF_SETL_WF_NTF_ERR');
3124 FND_MESSAGE.set_token ('CLAIM', l_claim_number, FALSE);
3125
3126 l_err_subj := SUBSTR(FND_MESSAGE.get, 1, 240);
3127
3128 Wf_Engine.SetItemAttrText(
3129 itemtype => p_itemtype,
3130 itemkey => p_itemkey ,
3131 aname => 'OZF_CSETL_ERR_SUBJ',
3132 avalue => l_err_subj );
3133
3134 FOR i IN 1..p_msg_count LOOP
3135 FND_MSG_PUB.get(
3136 p_msg_index => i,
3137 p_encoded => FND_API.g_false,
3138 p_data => l_msg_data,
3139 p_msg_index_out => l_msg_index
3140 );
3141 l_final_msg := l_final_msg ||
3142 l_msg_index ||
3143 ': ' ||
3144 l_msg_data ||
3145 fnd_global.local_chr(10);
3146 END LOOP ;
3147
3148 x_error_msg := l_final_msg;
3149
3150 WF_ENGINE.SetItemAttrText(
3151 itemtype => p_itemtype,
3152 itemkey => p_itemkey ,
3153 aname => 'OZF_CSETL_ERR_MSG',
3154 avalue => l_final_msg
3155 );
3156 END Handle_Error;
3157
3158
3159 PROCEDURE Check_Payment_Method(
3160 itemtype IN VARCHAR2,
3161 itemkey IN VARCHAR2,
3162 actid IN NUMBER,
3163 funcmode IN VARCHAR2,
3164 resultout OUT NOCOPY VARCHAR2
3165 )
3166 IS
3167 l_payment_method VARCHAR2(15);
3168
3169 BEGIN
3170 ---- RUN mode ----
3171 IF (funcmode = 'RUN') THEN
3172 -- get claim_id
3173 l_payment_method := WF_ENGINE.GetItemAttrText(
3174 itemtype => itemtype,
3175 itemkey => itemkey,
3176 aname => 'OZF_PAYMENT_METHOD_CODE'
3177 );
3178
3179 IF l_payment_method IN ( 'CHECK'
3180 , 'CONTRA_CHARGE'
3181 , 'CREDIT_MEMO'
3182 , 'REG_CREDIT_MEMO'
3183 , 'CHARGEBACK'
3184 , 'WRITE_OFF'
3185 , 'DEBIT_MEMO'
3186 , 'ON_ACCT_CREDIT'
3187 , 'RMA'
3188 , 'EFT'
3189 , 'WIRE'
3190 , 'AP_DEBIT'
3191 , 'AP_DEFAULT'
3192 ) THEN
3193 WF_ENGINE.SetItemAttrText(
3194 itemtype => itemtype,
3195 itemkey => itemkey,
3196 aname => 'OZF_CSETL_TYPE',
3197 avalue => 'SEEDED'
3198 );
3199 resultout := 'COMPLETE:Y';
3200 ELSE
3201 WF_ENGINE.SetItemAttrText(
3202 itemtype => itemtype,
3203 itemkey => itemkey,
3204 aname => 'OZF_CSETL_TYPE',
3205 avalue => 'ADHOC'
3206 );
3207 resultout := 'COMPLETE:N';
3208 END IF;
3209 RETURN;
3210 END IF;
3211 -- end RUN mode
3212
3213 ---- CANCEL mode -----
3214 IF (funcmode = 'CANCEL') THEN
3215 --resultout := 'COMPLETE:';
3216 RETURN;
3217 END IF;
3218 --
3219 ---- TIMEOUT mode ----
3220 IF (funcmode = 'TIMEOUT') THEN
3221 --resultout := 'COMPLETE:';
3222 return;
3223 END IF;
3224 --
3225 EXCEPTION
3226 WHEN OTHERS THEN
3227 WF_CORE.context(
3228 'OZF_AR_SETTLEMENT_PVT',
3229 'CHECK_PAYMENT_METHOD',
3230 itemtype,
3231 itemkey,
3232 to_char(actid),
3233 funcmode
3234 );
3235 RAISE;
3236 END Check_Payment_Method;
3237
3238
3239 PROCEDURE Prepare_Docs(
3240 itemtype IN VARCHAR2,
3241 itemkey IN VARCHAR2,
3242 actid IN NUMBER,
3243 funcmode IN VARCHAR2,
3244 resultout OUT NOCOPY VARCHAR2
3245 )
3246 IS
3247 l_next_status VARCHAR2(30);
3248
3249 BEGIN
3250
3251 ---- RUN mode ----
3252 IF (funcmode = 'RUN') THEN
3253 -- Transaction Identifier
3254 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
3255 itemkey => itemkey,
3256 aname => 'OZF_TRX_ID_1',
3257 avalue => 1001
3258 );
3259 -- Transaction Type
3260 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3261 itemkey => itemkey,
3262 aname => 'OZF_TRX_TYPE_1',
3263 avalue => 'CM'
3264 );
3265 -- Transaction Number
3266 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3267 itemkey => itemkey,
3268 aname => 'OZF_TRX_NUMBER_1',
3269 avalue => 'TST-1001'
3270 );
3271 -- Transaction Date
3272 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
3273 itemkey => itemkey,
3274 aname => 'OZF_TRX_DATE_1',
3275 avalue => SYSDATE
3276 );
3277 -- Transaction Amount
3278 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
3279 itemkey => itemkey,
3280 aname => 'OZF_TRX_AMOUNT_1',
3281 avalue => 123456
3282 );
3283 -- Transaction Status
3284 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3285 itemkey => itemkey,
3286 aname => 'OZF_TRX_STATUS_1',
3287 avalue => 'PAID'
3288 );
3289
3290 resultout := 'COMPLETE:';
3291 RETURN;
3292 END IF;
3293 -- end RUN mode
3294
3295 ---- CANCEL mode -----
3296 IF (funcmode = 'CANCEL') THEN
3297 resultout := 'COMPLETE:';
3298 RETURN;
3299 END IF;
3300 --
3301 ---- TIMEOUT mode ----
3302 IF (funcmode = 'TIMEOUT') THEN
3303 resultout := 'COMPLETE:';
3304 return;
3305 END IF;
3306 --
3307 EXCEPTION
3308 WHEN OTHERS THEN
3309 WF_CORE.context(
3310 'OZF_AR_SETTLEMENT_PVT',
3311 'Prepare_Docs',
3312 itemtype,
3313 itemkey,
3314 to_char(actid),
3315 funcmode
3316 );
3317 RAISE;
3318 END Prepare_Docs;
3319
3320
3321 ------------------------------------------------------------------------------
3322 -- PROCEDURE
3323 -- Check_Auto_Setl_Process
3324 --
3325 -- Workflow cover:
3326 -- IN
3327 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
3328 -- itemkey - A string generated from the application object's primary key.
3329 -- actid - The function activity(instance id).
3330 -- funcmode - Run/Cancel/Timeout
3331 -- OUT
3332 -- Resultout - 'COMPLETE:YES'
3333 -- - 'COMPLETE:NO'
3334 --
3335 -- USED BY ACTIVITIES
3336 -- <ITEM_TYPE> <ACTIVITY>
3337 -- OZF_AR_SETTLEMENT_PVT OZF_CHECK_PROMO_CLAIM
3338 ---------------------------------------------------------------------------------
3339 PROCEDURE Check_Auto_Setl_Process(
3340 itemtype IN VARCHAR2,
3341 itemkey IN VARCHAR2,
3342 actid IN NUMBER,
3343 funcmode IN VARCHAR2,
3344 resultout OUT NOCOPY VARCHAR2
3345 )
3346 IS
3347 l_automate_settlement VARCHAR2(1);
3348 l_claim_class VARCHAR2(30);
3349 l_payment_method VARCHAR2(30);
3350
3351 BEGIN
3352 ---- RUN mode ----
3353 IF (funcmode = 'RUN') THEN
3354 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3355 itemkey => itemkey,
3356 aname => 'OZF_CLAIM_CLASS'
3357 );
3358
3359 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3360 itemkey => itemkey,
3361 aname => 'OZF_PAYMENT_METHOD_CODE'
3362 );
3363
3364
3365 -- Modified for Bug4241187: Support for additional methods.
3366 IF l_payment_method = 'CONTRA_CHARGE' THEN
3367 resultout := 'COMPLETE:N';
3368 ELSIF l_payment_method in ( 'RMA', 'REG_CREDIT_MEMO') THEN
3369 resultout := 'COMPLETE:N';
3370 -- Bug4308173: This check always evaluates to N
3371 -- For RMA, WF is launched only when automate settlement is N
3372 -- For REG_CREDIT_MEMO, WF is launched when automate settlement is N or
3373 -- when settlement should be done by receivable role.
3374 ELSIF l_payment_method in ( 'CHECK','EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
3375 resultout := 'COMPLETE:Y';
3376 ELSIF l_payment_method in ('CREDIT_MEMO','DEBIT_MEMO' ) THEN
3377 l_automate_settlement := NVL(FND_PROFILE.value('OZF_CLAIM_USE_AR_AUTOMATION'), 'Y');
3378 IF l_automate_settlement = 'Y' THEN
3379 resultout := 'COMPLETE:Y';
3380 ELSE
3381 resultout := 'COMPLETE:N';
3382 END IF;
3383 ELSE
3384 resultout := 'COMPLETE:N';
3385 END IF;
3386
3387 RETURN;
3388 END IF; -- end RUN mode
3389
3390 ---- CANCEL mode -----
3391 IF (funcmode = 'CANCEL') THEN
3392 resultout := 'COMPLETE:';
3393 RETURN;
3394 END IF;
3395 --
3396 ---- TIMEOUT mode ----
3397 IF (funcmode = 'TIMEOUT') THEN
3398 resultout := 'COMPLETE:';
3399 return;
3400 END IF;
3401 --
3402 EXCEPTION
3403 WHEN OTHERS THEN
3404 WF_CORE.context(
3405 'OZF_AR_SETTLEMENT_PVT',
3406 'Check_Auto_Setl_Process',
3407 itemtype,
3408 itemkey,
3409 to_char(actid),
3410 funcmode
3411 );
3412 RAISE;
3413 END Check_Auto_Setl_Process;
3414
3415 END OZF_AR_SETTLEMENT_PVT;