[Home] [Help]
PACKAGE BODY: APPS.OZF_AR_SETTLEMENT_PVT
Source
1 PACKAGE BODY OZF_AR_SETTLEMENT_PVT AS
2 /* $Header: ozfvcapb.pls 120.11 2006/05/15 01:10:25 azahmed 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 OZF_Utility_PVT.debug_message('Start == Item Type:'||l_itemtype||'-- Item key:'||l_itemkey);
2441 BEGIN
2442 UPDATE ozf_claims_all
2443 SET cstl_wf_item_key = l_itemkey
2444 WHERE claim_id = p_claim_id;
2445 EXCEPTION
2446 WHEN OTHERS THEN
2447 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2448 FND_MESSAGE.set_name('OZF', 'OZF_CLAIM_UPD_CLAM_ERR');
2449 FND_MSG_PUB.add;
2450 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2451 FND_MESSAGE.Set_Token('TEXT',sqlerrm);
2452 FND_MSG_PUB.Add;
2453 END IF;
2454 END;
2455 -------------- start the process ---------------------
2456 WF_ENGINE.StartProcess( itemtype => l_itemtype,
2457 itemkey => l_itemkey
2458 );
2459 --
2460
2461 EXCEPTION
2462 WHEN FND_API.G_EXC_ERROR THEN
2463 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2464 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2465 FND_MESSAGE.Set_Token('TEXT',G_PKG_NAME||'.'||l_api_name||': Error');
2466 FND_MSG_PUB.Add;
2467 END IF;
2468 RAISE;
2469 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2470 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2471 FND_MESSAGE.Set_Name('OZF','OZF_API_DEBUG_MESSAGE');
2472 FND_MESSAGE.Set_Token('TEXT',G_PKG_NAME||'.'||l_api_name||': Error');
2473 FND_MSG_PUB.Add;
2474 END IF;
2475 RAISE;
2476 WHEN OTHERS THEN
2477 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2478 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2479 END IF;
2480 RAISE;
2481 END Start_Settlement;
2482
2483 ------------------------------------------------------------------------------
2484 -- PROCEDURE
2485 -- Check_Promo_Claim
2486 --
2487 -- Workflow cover:
2488 -- IN
2489 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2490 -- itemkey - A string generated from the application object's primary key.
2491 -- actid - The function activity(instance id).
2492 -- funcmode - Run/Cancel/Timeout
2493 -- OUT
2494 -- Resultout - 'COMPLETE:YES'
2495 -- - 'COMPLETE:NO'
2496 --
2497 -- USED BY ACTIVITIES
2498 -- <ITEM_TYPE> <ACTIVITY>
2499 -- OZF_AR_SETTLEMENT_PVT OZF_CHECK_PROMO_CLAIM
2500 ---------------------------------------------------------------------------------
2501 PROCEDURE Check_Promo_Claim(
2502 itemtype IN VARCHAR2,
2503 itemkey IN VARCHAR2,
2504 actid IN NUMBER,
2505 funcmode IN VARCHAR2,
2506 resultout OUT NOCOPY VARCHAR2
2507 )
2508 IS
2509 l_promo_flag VARCHAR2(3);
2510
2511 -- Bug4308173
2512 CURSOR claim_gl_posting_csr(p_id in number) IS
2513 SELECT osp.post_to_gl
2514 FROM ozf_sys_parameters_all osp
2515 , ozf_claims_all oc
2516 WHERE osp.org_id = oc.org_id
2517 AND oc.claim_id = p_id;
2518 l_post_to_gl VARCHAR2(1);
2519 l_claim_id NUMBER;
2520
2521 BEGIN
2522 ---- RUN mode ----
2523 IF (funcmode = 'RUN') THEN
2524 -- get claim_id
2525 l_promo_flag := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2526 itemkey => itemkey,
2527 aname => 'OZF_PROMO_CLAIM_FLAG'
2528 );
2529
2530 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2531 itemkey => itemkey,
2532 aname => 'OZF_CLAIM_ID'
2533 );
2534
2535 OPEN claim_gl_posting_csr(l_claim_id);
2536 FETCH claim_gl_posting_csr INTO l_post_to_gl;
2537 CLOSE claim_gl_posting_csr;
2538
2539 IF l_promo_flag = 'Y' AND NVL(l_post_to_gl,'F') = 'T' THEN
2540 resultout := 'COMPLETE:Y';
2541 ELSE
2542 resultout := 'COMPLETE:N';
2543 END IF;
2544
2545 RETURN;
2546 END IF;
2547 -- end RUN mode
2548
2549 ---- CANCEL mode -----
2550 IF (funcmode = 'CANCEL') THEN
2551 resultout := 'COMPLETE:';
2552 RETURN;
2553 END IF;
2554 --
2555 ---- TIMEOUT mode ----
2556 IF (funcmode = 'TIMEOUT') THEN
2557 resultout := 'COMPLETE:';
2558 return;
2559 END IF;
2560 --
2561 EXCEPTION
2562 WHEN OTHERS THEN
2563 WF_CORE.context(
2564 'OZF_AR_SETTLEMENT_PVT',
2565 'Check_Promo_Claim',
2566 itemtype,
2567 itemkey,
2568 to_char(actid),
2569 funcmode
2570 );
2571 RAISE;
2572 END Check_Promo_Claim;
2573
2574
2575 ------------------------------------------------------------------------------
2576 -- PROCEDURE
2577 -- Create_GL_Entries
2578 --
2579 -- Workflow cover:
2580 -- IN
2581 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2582 -- itemkey - A string generated from the application object's primary key.
2583 -- actid - The function activity(instance id).
2584 -- funcmode - Run/Cancel/Timeout
2585 -- OUT
2586 -- Resultout - 'COMPLETE:SUCCESS'
2587 -- - 'COMPLETE:ERROR'
2588 --
2589 -- USED BY ACTIVITIES
2590 -- <ITEM_TYPE> <ACTIVITY>
2591 -- OZF_AR_SETTLEMENT_PVT OZF_CREATE_GL_ENTRIES
2592 ---------------------------------------------------------------------------------
2593 PROCEDURE Create_GL_Entries(
2594 itemtype IN VARCHAR2,
2595 itemkey IN VARCHAR2,
2596 actid IN NUMBER,
2597 funcmode IN VARCHAR2,
2598 resultout OUT NOCOPY VARCHAR2
2599 )
2600 IS
2601 l_return_status VARCHAR2(1);
2602 l_msg_data VARCHAR2(400);
2603 l_msg_count NUMBER;
2604 l_api_version CONSTANT NUMBER := 1.0;
2605 l_event_id NUMBER;
2606 l_ccid NUMBER;
2607
2608 l_claim_id NUMBER;
2609 l_claim_class VARCHAR2(30);
2610 l_payment_method VARCHAR2(30);
2611
2612 l_error_msg VARCHAR2(4000);
2613
2614 BEGIN
2615
2616 SAVEPOINT Create_GL_Entries;
2617 ---- RUN mode ----
2618 IF (funcmode = 'RUN') THEN
2619 -- get claim_id
2620 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2621 itemkey => itemkey,
2622 aname => 'OZF_CLAIM_ID'
2623 );
2624
2625 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2626 itemkey => itemkey,
2627 aname => 'OZF_PAYMENT_METHOD_CODE'
2628 );
2629
2630 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2631 itemkey => itemkey,
2632 aname => 'OZF_CLAIM_CLASS'
2633 );
2634
2635 OZF_GL_INTERFACE_PVT.Post_Claim_To_GL(
2636 p_api_version => 1.0,
2637 x_return_status => l_return_status,
2638 x_msg_data => l_msg_data,
2639 x_msg_count => l_msg_count,
2640 p_claim_id => l_claim_id,
2641 p_claim_class => l_claim_class,
2642 p_settlement_method => l_payment_method,
2643 x_event_id => l_event_id,
2644 x_clear_code_combination_id => l_ccid
2645 );
2646 IF l_return_status <> FND_API.g_ret_sts_success THEN
2647 RAISE ERROR;
2648 ELSE
2649 resultout := 'COMPLETE:SUCCESS';
2650 END IF;
2651 RETURN;
2652 END IF;
2653
2654 -- end RUN mode
2655
2656 ---- CANCEL mode -----
2657 IF (funcmode = 'CANCEL') THEN
2658 --resultout := 'COMPLETE:';
2659 RETURN;
2660 END IF;
2661 --
2662 ---- TIMEOUT mode ----
2663 IF (funcmode = 'TIMEOUT') THEN
2664 --resultout := 'COMPLETE:';
2665 return;
2666 END IF;
2667 --
2668 EXCEPTION
2669 WHEN ERROR THEN
2670 ROLLBACK TO Create_GL_Entries;
2671 FND_MSG_PUB.count_and_get (
2672 p_encoded => FND_API.g_false
2673 ,p_count => l_msg_count
2674 ,p_data => l_msg_data
2675 );
2676 Handle_Error(
2677 p_itemtype => itemtype
2678 ,p_itemkey => itemkey
2679 ,p_msg_count => l_msg_count
2680 ,p_msg_data => l_msg_data
2681 ,p_process_name => 'Create_GL_Entrie'
2682 ,x_error_msg => l_error_msg
2683 );
2684 WF_CORE.context(
2685 'OZF_AR_SETTLEMENT_PVT'
2686 ,'Create_GL_Entries'
2687 ,itemtype
2688 ,itemkey
2689 ,actid
2690 ,l_error_msg
2691 );
2692 resultout := 'COMPLETE:ERROR';
2693 RETURN;
2694
2695 WHEN OTHERS THEN
2696 ROLLBACK TO Create_GL_Entries;
2697 WF_CORE.context(
2698 'OZF_AR_SETTLEMENT_PVT',
2699 'Create_GL_Entries',
2700 itemtype,
2701 itemkey,
2702 to_char(actid),
2703 funcmode
2704 );
2705 RAISE;
2706 END Create_GL_Entries;
2707
2708
2709 ------------------------------------------------------------------------------
2710 -- PROCEDURE
2711 -- Revert_GL_Entries
2712 --
2713 -- Workflow cover:
2714 -- IN
2715 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2716 -- itemkey - A string generated from the application object's primary key.
2717 -- actid - The function activity(instance id).
2718 -- funcmode - Run/Cancel/Timeout
2719 -- OUT
2720 -- Resultout - 'COMPLETE:SUCCESS'
2721 -- - 'COMPLETE:ERROR'
2722 --
2723 ---------------------------------------------------------------------------------
2724 PROCEDURE Revert_GL_Entries(
2725 itemtype IN VARCHAR2,
2726 itemkey IN VARCHAR2,
2727 actid IN NUMBER,
2728 funcmode IN VARCHAR2,
2729 resultout OUT NOCOPY VARCHAR2
2730 )
2731 IS
2732 l_return_status VARCHAR2(1);
2733 l_msg_data VARCHAR2(400);
2734 l_msg_count NUMBER;
2735 l_api_version CONSTANT NUMBER := 1.0;
2736 l_event_id NUMBER;
2737 l_ccid NUMBER;
2738
2739 l_claim_id NUMBER;
2740 l_error_msg VARCHAR2(4000);
2741
2742 BEGIN
2743 SAVEPOINT Revert_GL_Entries;
2744 ---- RUN mode ----
2745 IF (funcmode = 'RUN') THEN
2746 -- get claim_id
2747 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2748 itemkey => itemkey,
2749 aname => 'OZF_CLAIM_ID'
2750 );
2751
2752
2753 OZF_GL_INTERFACE_PVT.Revert_GL_Entry (
2754 p_api_version => 1.0,
2755 x_return_status => l_return_status,
2756 x_msg_data => l_msg_data,
2757 x_msg_count => l_msg_count,
2758 p_claim_id => l_claim_id
2759 );
2760 IF l_return_status <> FND_API.g_ret_sts_success THEN
2761 RAISE ERROR;
2762 END IF;
2763 RETURN;
2764 END IF;
2765 -- end RUN mode
2766
2767 ---- CANCEL mode -----
2768 IF (funcmode = 'CANCEL') THEN
2769 --resultout := 'COMPLETE:';
2770 RETURN;
2771 END IF;
2772 --
2773 ---- TIMEOUT mode ----
2774 IF (funcmode = 'TIMEOUT') THEN
2775 --resultout := 'COMPLETE:';
2776 return;
2777 END IF;
2778 --
2779 EXCEPTION
2780 WHEN ERROR THEN
2781 ROLLBACK TO Revert_GL_Entries;
2782 FND_MSG_PUB.count_and_get (
2783 p_encoded => FND_API.g_false
2784 ,p_count => l_msg_count
2785 ,p_data => l_msg_data
2786 );
2787 Handle_Error(
2788 p_itemtype => itemtype
2789 ,p_itemkey => itemkey
2790 ,p_msg_count => l_msg_count
2791 ,p_msg_data => l_msg_data
2792 ,p_process_name => 'Revert_GL_Entries'
2793 ,x_error_msg => l_error_msg
2794 );
2795 WF_CORE.context(
2796 'OZF_AR_SETTLEMENT_PVT'
2797 ,'Revert_GL_Entries'
2798 ,itemtype
2799 ,itemkey
2800 ,actid
2801 ,l_error_msg
2802 );
2803 RAISE;
2804
2805 WHEN OTHERS THEN
2806 ROLLBACK TO Revert_GL_Entries;
2807 WF_CORE.context(
2808 'OZF_AR_SETTLEMENT_PVT',
2809 'Revert_GL_Entries',
2810 itemtype,
2811 itemkey,
2812 to_char(actid),
2813 funcmode
2814 );
2815 RAISE;
2816 END Revert_GL_Entries;
2817
2818
2819 ------------------------------------------------------------------------------
2820 -- PROCEDURE
2821 -- Create_Payment
2822 --
2823 -- Workflow cover:
2824 -- IN
2825 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
2826 -- itemkey - A string generated from the application object's primary key.
2827 -- actid - The function activity(instance id).
2828 -- funcmode - Run/Cancel/Timeout
2829 -- OUT
2830 -- Resultout - 'COMPLETE:SUCCESS'
2831 -- - 'COMPLETE:ERROR'
2832 --
2833 -- USED BY ACTIVITIES
2834 -- <ITEM_TYPE> <ACTIVITY>
2835 -- OZF_AR_SETTLEMENT_PVT OZF_CREATE_PAYMENT
2836 ---------------------------------------------------------------------------------
2837 PROCEDURE Create_Payment(
2838 itemtype IN VARCHAR2,
2839 itemkey IN VARCHAR2,
2840 actid IN NUMBER,
2841 funcmode IN VARCHAR2,
2842 resultout OUT NOCOPY VARCHAR2
2843 )
2844 IS
2845 l_return_status VARCHAR2(1);
2846 l_msg_data VARCHAR2(4000);
2847 l_msg_count NUMBER;
2848
2849 l_api_version CONSTANT NUMBER := 1.0;
2850 l_api_name CONSTANT VARCHAR2(30) := 'Create_Payment_for_Settlement';
2851 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2852 l_claim_id NUMBER;
2853 l_object_version NUMBER;
2854 l_org_id NUMBER;
2855 l_error_msg VARCHAR2(4000);
2856 l_payment_method VARCHAR2(30);
2857
2858 CURSOR csr_claim_version(cv_claim_id IN NUMBER) IS
2859 SELECT object_version_number
2860 , org_id
2861 FROM ozf_claims_all
2862 WHERE claim_id = cv_claim_id;
2863
2864 CURSOR csr_claim_settle(cv_claim_id IN NUMBER) IS
2865 SELECT claim_number
2866 , settled_date
2867 , vendor_id
2868 , vendor_site_id
2869 , amount_settled
2870 , currency_code
2871 , exchange_rate
2872 , exchange_rate_type
2873 , exchange_rate_date
2874 , payment_method
2875 , set_of_books_id
2876 , gl_date
2877 , claim_class
2878 , payment_reference_id
2879 FROM ozf_claims
2880 WHERE claim_id = cv_claim_id;
2881
2882
2883 l_claim_settle csr_claim_settle%ROWTYPE;
2884 l_payment_term NUMBER;
2885 l_settlement_doc_rec OZF_SETTLEMENT_DOC_PVT.settlement_doc_rec_type;
2886 l_automate_settlement VARCHAR2(1);
2887 BEGIN
2888 SAVEPOINT create_payment;
2889 ---- RUN mode ----
2890 IF (funcmode = 'RUN') THEN
2891 -- get claim_id
2892 l_claim_id := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2893 itemkey => itemkey,
2894 aname => 'OZF_CLAIM_ID'
2895 );
2896
2897 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
2898 itemkey => itemkey,
2899 aname => 'OZF_PAYMENT_METHOD_CODE'
2900 );
2901
2902 OPEN csr_claim_version(l_claim_id);
2903 FETCH csr_claim_version INTO l_object_version
2904 , l_org_id;
2905 CLOSE csr_claim_version;
2906
2907
2908 OPEN csr_claim_settle(l_claim_id);
2909 FETCH csr_claim_settle INTO l_claim_settle;
2910 CLOSE csr_claim_settle;
2911
2912
2913 --------------------------- CHECK -----------------------------
2914 IF l_claim_settle.payment_method in ( 'CHECK','EFT','WIRE', 'AP_DEBIT','AP_DEFAULT') THEN
2915 -- create AP invoice
2916 OZF_AP_INTERFACE_PVT.Create_AP_Invoice (
2917 p_api_version => l_api_version
2918 ,p_init_msg_list => FND_API.g_false
2919 ,p_commit => FND_API.g_false
2920 ,p_validation_level => FND_API.g_valid_level_full
2921 ,x_return_status => l_return_status
2922 ,x_msg_data => l_msg_data
2923 ,x_msg_count => l_msg_count
2924 ,p_claim_id => l_claim_id
2925 );
2926 IF l_return_status <> FND_API.g_ret_sts_success THEN
2927 RAISE ERROR;
2928 ELSE
2929 resultout := 'COMPLETE:SUCCESS';
2930 END IF;
2931
2932 ----------------------- CREDIT_MEMO -----------------------------
2933 ELSIF l_claim_settle.payment_method IN ('CREDIT_MEMO', 'DEBIT_MEMO') THEN
2934 OZF_AR_PAYMENT_PVT.Create_AR_Payment(
2935 p_api_version => l_api_version
2936 ,p_init_msg_list => FND_API.g_false
2937 ,p_commit => FND_API.g_false
2938 ,p_validation_level => FND_API.g_valid_level_full
2939 ,x_return_status => l_return_status
2940 ,x_msg_data => l_msg_data
2941 ,x_msg_count => l_msg_count
2942 ,p_claim_id => l_claim_id
2943 );
2944 IF l_return_status <> FND_API.g_ret_sts_success THEN
2945 RAISE ERROR;
2946 ELSE
2947 resultout := 'COMPLETE:SUCCESS';
2948 END IF;
2949 ELSE
2950 resultout := 'COMPLETE:ERROR';
2951 END IF;
2952 RETURN;
2953 END IF;
2954 -- end RUN mode
2955
2956
2957
2958 ---- CANCEL mode -----
2959 IF (funcmode = 'CANCEL') THEN
2960 --resultout := 'COMPLETE:';
2961 RETURN;
2962 END IF;
2963 --
2964 ---- TIMEOUT mode ----
2965 IF (funcmode = 'TIMEOUT') THEN
2966 --resultout := 'COMPLETE:';
2967 return;
2968 END IF;
2969 --
2970 EXCEPTION
2971 WHEN ERROR THEN
2972 ROLLBACK TO create_payment;
2973 FND_MSG_PUB.count_and_get (
2974 p_encoded => FND_API.g_false
2975 ,p_count => l_msg_count
2976 ,p_data => l_msg_data
2977 );
2978 Handle_Error(
2979 p_itemtype => itemtype
2980 ,p_itemkey => itemkey
2981 ,p_msg_count => l_msg_count
2982 ,p_msg_data => l_msg_data
2983 ,p_process_name => 'CREATE_PAYMENT'
2984 ,x_error_msg => l_error_msg
2985 );
2986 WF_CORE.context(
2987 'OZF_AR_SETTLEMENT_PVT'
2988 ,'CREATE_PAYMENT'
2989 ,itemtype
2990 ,itemkey
2991 ,actid
2992 ,l_error_msg
2993 );
2994 resultout := 'COMPLETE:ERROR';
2995 RETURN;
2996 WHEN OTHERS THEN
2997 ROLLBACK TO create_payment;
2998 WF_CORE.context(
2999 'OZF_AR_SETTLEMENT_PVT',
3000 'Create_Payment',
3001 itemtype,
3002 itemkey,
3003 to_char(actid),
3004 funcmode
3005 );
3006 RAISE;
3007 END Create_Payment;
3008
3009
3010 ------------------------------------------------------------------------------
3011 -- PROCEDURE
3012 -- Check_Claim_Class
3013 --
3014 -- Workflow cover:
3015 -- IN
3016 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
3017 -- itemkey - A string generated from the application object's primary key.
3018 -- actid - The function activity(instance id).
3019 -- funcmode - Run/Cancel/Timeout
3020 -- OUT
3021 -- Resultout - 'COMPLETE:MANUAL'
3022 -- - 'COMPLETE:AUTO'
3023 -- - 'COMPLETE:END'
3024 --
3025 -- USED BY ACTIVITIES
3026 -- <ITEM_TYPE> <ACTIVITY>
3027 -- OZF_AR_SETTLEMENT_PVT CHECK_CLAIM_CLASS
3028 ---------------------------------------------------------------------------------
3029 PROCEDURE Check_Claim_Class(
3030 itemtype IN VARCHAR2,
3031 itemkey IN VARCHAR2,
3032 actid IN NUMBER,
3033 funcmode IN VARCHAR2,
3034 resultout OUT NOCOPY VARCHAR2
3035 )
3036 IS
3037 l_claim_class VARCHAR2(30);
3038
3039 BEGIN
3040 ---- RUN mode ----
3041 IF (funcmode = 'RUN') THEN
3042 -- get claim_id
3043 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3044 itemkey => itemkey,
3045 aname => 'OZF_CLAIM_CLASS'
3046 );
3047 IF l_claim_class = 'CLAIM' THEN
3048 resultout := 'COMPLETE:CLAIM';
3049 ELSE
3050 resultout := 'COMPLETE:DEDUCTION';
3051 END IF;
3052 RETURN;
3053 END IF;
3054 -- end RUN mode
3055
3056 ---- CANCEL mode -----
3057 IF (funcmode = 'CANCEL') THEN
3058 --resultout := 'COMPLETE:';
3059 RETURN;
3060 END IF;
3061 --
3062 ---- TIMEOUT mode ----
3063 IF (funcmode = 'TIMEOUT') THEN
3064 --resultout := 'COMPLETE:';
3065 return;
3066 END IF;
3067 --
3068 EXCEPTION
3069 WHEN OTHERS THEN
3070 WF_CORE.context(
3071 'OZF_AR_SETTLEMENT_PVT',
3072 'Check_Claim_Class',
3073 itemtype,
3074 itemkey,
3075 to_char(actid),
3076 funcmode
3077 );
3078 RAISE;
3079 END Check_Claim_Class;
3080
3081
3082
3083 PROCEDURE Handle_Error(
3084 p_itemtype IN VARCHAR2,
3085 p_itemkey IN VARCHAR2,
3086 p_msg_count IN NUMBER,
3087 p_msg_data IN VARCHAR2,
3088 p_process_name IN VARCHAR2,
3089 x_error_msg OUT NOCOPY VARCHAR2
3090 )
3091 IS
3092 l_msg_count NUMBER ;
3093 l_msg_data VARCHAR2(2000);
3094 l_final_msg VARCHAR2(4000);
3095 l_msg_index NUMBER ;
3096 l_err_subj VARCHAR2(240);
3097 l_claim_number VARCHAR2(30);
3098
3099
3100 BEGIN
3101 l_claim_number := WF_ENGINE.GetItemAttrText(
3102 itemtype => p_itemtype,
3103 itemkey => p_itemkey,
3104 aname => 'OZF_CLAIM_NUMBER'
3105 );
3106
3107 FND_MESSAGE.set_name ('OZF', 'OZF_SETL_WF_NTF_ERR');
3108 FND_MESSAGE.set_token ('CLAIM', l_claim_number, FALSE);
3109
3110 l_err_subj := SUBSTR(FND_MESSAGE.get, 1, 240);
3111
3112 Wf_Engine.SetItemAttrText(
3113 itemtype => p_itemtype,
3114 itemkey => p_itemkey ,
3115 aname => 'OZF_CSETL_ERR_SUBJ',
3116 avalue => l_err_subj );
3117
3118 FOR i IN 1..p_msg_count LOOP
3119 FND_MSG_PUB.get(
3120 p_msg_index => i,
3121 p_encoded => FND_API.g_false,
3122 p_data => l_msg_data,
3123 p_msg_index_out => l_msg_index
3124 );
3125 l_final_msg := l_final_msg ||
3126 l_msg_index ||
3127 ': ' ||
3128 l_msg_data ||
3129 fnd_global.local_chr(10);
3130 END LOOP ;
3131
3132 x_error_msg := l_final_msg;
3133
3134 WF_ENGINE.SetItemAttrText(
3135 itemtype => p_itemtype,
3136 itemkey => p_itemkey ,
3137 aname => 'OZF_CSETL_ERR_MSG',
3138 avalue => l_final_msg
3139 );
3140 END Handle_Error;
3141
3142
3143 PROCEDURE Check_Payment_Method(
3144 itemtype IN VARCHAR2,
3145 itemkey IN VARCHAR2,
3146 actid IN NUMBER,
3147 funcmode IN VARCHAR2,
3148 resultout OUT NOCOPY VARCHAR2
3149 )
3150 IS
3151 l_payment_method VARCHAR2(15);
3152
3153 BEGIN
3154 ---- RUN mode ----
3155 IF (funcmode = 'RUN') THEN
3156 -- get claim_id
3157 l_payment_method := WF_ENGINE.GetItemAttrText(
3158 itemtype => itemtype,
3159 itemkey => itemkey,
3160 aname => 'OZF_PAYMENT_METHOD_CODE'
3161 );
3162
3163 IF l_payment_method IN ( 'CHECK'
3164 , 'CONTRA_CHARGE'
3165 , 'CREDIT_MEMO'
3166 , 'REG_CREDIT_MEMO'
3167 , 'CHARGEBACK'
3168 , 'WRITE_OFF'
3169 , 'DEBIT_MEMO'
3170 , 'ON_ACCT_CREDIT'
3171 , 'RMA'
3172 , 'EFT'
3173 , 'WIRE'
3174 , 'AP_DEBIT'
3175 , 'AP_DEFAULT'
3176 ) THEN
3177 WF_ENGINE.SetItemAttrText(
3178 itemtype => itemtype,
3179 itemkey => itemkey,
3180 aname => 'OZF_CSETL_TYPE',
3181 avalue => 'SEEDED'
3182 );
3183 resultout := 'COMPLETE:Y';
3184 ELSE
3185 WF_ENGINE.SetItemAttrText(
3186 itemtype => itemtype,
3187 itemkey => itemkey,
3188 aname => 'OZF_CSETL_TYPE',
3189 avalue => 'ADHOC'
3190 );
3191 resultout := 'COMPLETE:N';
3192 END IF;
3193 RETURN;
3194 END IF;
3195 -- end RUN mode
3196
3197 ---- CANCEL mode -----
3198 IF (funcmode = 'CANCEL') THEN
3199 --resultout := 'COMPLETE:';
3200 RETURN;
3201 END IF;
3202 --
3203 ---- TIMEOUT mode ----
3204 IF (funcmode = 'TIMEOUT') THEN
3205 --resultout := 'COMPLETE:';
3206 return;
3207 END IF;
3208 --
3209 EXCEPTION
3210 WHEN OTHERS THEN
3211 WF_CORE.context(
3212 'OZF_AR_SETTLEMENT_PVT',
3213 'CHECK_PAYMENT_METHOD',
3214 itemtype,
3215 itemkey,
3216 to_char(actid),
3217 funcmode
3218 );
3219 RAISE;
3220 END Check_Payment_Method;
3221
3222
3223 PROCEDURE Prepare_Docs(
3224 itemtype IN VARCHAR2,
3225 itemkey IN VARCHAR2,
3226 actid IN NUMBER,
3227 funcmode IN VARCHAR2,
3228 resultout OUT NOCOPY VARCHAR2
3229 )
3230 IS
3231 l_next_status VARCHAR2(30);
3232
3233 BEGIN
3234
3235 ---- RUN mode ----
3236 IF (funcmode = 'RUN') THEN
3237 -- Transaction Identifier
3238 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
3239 itemkey => itemkey,
3240 aname => 'OZF_TRX_ID_1',
3241 avalue => 1001
3242 );
3243 -- Transaction Type
3244 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3245 itemkey => itemkey,
3246 aname => 'OZF_TRX_TYPE_1',
3247 avalue => 'CM'
3248 );
3249 -- Transaction Number
3250 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3251 itemkey => itemkey,
3252 aname => 'OZF_TRX_NUMBER_1',
3253 avalue => 'TST-1001'
3254 );
3255 -- Transaction Date
3256 WF_ENGINE.SetItemAttrDate( itemtype => itemtype,
3257 itemkey => itemkey,
3258 aname => 'OZF_TRX_DATE_1',
3259 avalue => SYSDATE
3260 );
3261 -- Transaction Amount
3262 WF_ENGINE.SetItemAttrNumber( itemtype => itemtype,
3263 itemkey => itemkey,
3264 aname => 'OZF_TRX_AMOUNT_1',
3265 avalue => 123456
3266 );
3267 -- Transaction Status
3268 WF_ENGINE.SetItemAttrText( itemtype => itemtype,
3269 itemkey => itemkey,
3270 aname => 'OZF_TRX_STATUS_1',
3271 avalue => 'PAID'
3272 );
3273
3274 resultout := 'COMPLETE:';
3275 RETURN;
3276 END IF;
3277 -- end RUN mode
3278
3279 ---- CANCEL mode -----
3280 IF (funcmode = 'CANCEL') THEN
3281 resultout := 'COMPLETE:';
3282 RETURN;
3283 END IF;
3284 --
3285 ---- TIMEOUT mode ----
3286 IF (funcmode = 'TIMEOUT') THEN
3287 resultout := 'COMPLETE:';
3288 return;
3289 END IF;
3290 --
3291 EXCEPTION
3292 WHEN OTHERS THEN
3293 WF_CORE.context(
3294 'OZF_AR_SETTLEMENT_PVT',
3295 'Prepare_Docs',
3296 itemtype,
3297 itemkey,
3298 to_char(actid),
3299 funcmode
3300 );
3301 RAISE;
3302 END Prepare_Docs;
3303
3304
3305 ------------------------------------------------------------------------------
3306 -- PROCEDURE
3307 -- Check_Auto_Setl_Process
3308 --
3309 -- Workflow cover:
3310 -- IN
3311 -- itemtype - A valid item type from (WF_ITEM_TYPES table).
3312 -- itemkey - A string generated from the application object's primary key.
3313 -- actid - The function activity(instance id).
3314 -- funcmode - Run/Cancel/Timeout
3315 -- OUT
3316 -- Resultout - 'COMPLETE:YES'
3317 -- - 'COMPLETE:NO'
3318 --
3319 -- USED BY ACTIVITIES
3320 -- <ITEM_TYPE> <ACTIVITY>
3321 -- OZF_AR_SETTLEMENT_PVT OZF_CHECK_PROMO_CLAIM
3322 ---------------------------------------------------------------------------------
3323 PROCEDURE Check_Auto_Setl_Process(
3324 itemtype IN VARCHAR2,
3325 itemkey IN VARCHAR2,
3326 actid IN NUMBER,
3327 funcmode IN VARCHAR2,
3328 resultout OUT NOCOPY VARCHAR2
3329 )
3330 IS
3331 l_automate_settlement VARCHAR2(1);
3332 l_claim_class VARCHAR2(30);
3333 l_payment_method VARCHAR2(30);
3334
3335 BEGIN
3336 ---- RUN mode ----
3337 IF (funcmode = 'RUN') THEN
3338 l_claim_class := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3339 itemkey => itemkey,
3340 aname => 'OZF_CLAIM_CLASS'
3341 );
3342
3343 l_payment_method := WF_ENGINE.GetItemAttrText( itemtype => itemtype,
3344 itemkey => itemkey,
3345 aname => 'OZF_PAYMENT_METHOD_CODE'
3346 );
3347
3348
3349 -- Modified for Bug4241187: Support for additional methods.
3350 IF l_payment_method = 'CONTRA_CHARGE' THEN
3351 resultout := 'COMPLETE:N';
3352 ELSIF l_payment_method in ( 'RMA', 'REG_CREDIT_MEMO') THEN
3353 resultout := 'COMPLETE:N';
3354 -- Bug4308173: This check always evaluates to N
3355 -- For RMA, WF is launched only when automate settlement is N
3356 -- For REG_CREDIT_MEMO, WF is launched when automate settlement is N or
3357 -- when settlement should be done by receivable role.
3358 ELSIF l_payment_method in ( 'CHECK','EFT','WIRE','AP_DEBIT','AP_DEFAULT') THEN
3359 resultout := 'COMPLETE:Y';
3360 ELSIF l_payment_method in ('CREDIT_MEMO','DEBIT_MEMO' ) THEN
3361 l_automate_settlement := NVL(FND_PROFILE.value('OZF_CLAIM_USE_AR_AUTOMATION'), 'Y');
3362 IF l_automate_settlement = 'Y' THEN
3363 resultout := 'COMPLETE:Y';
3364 ELSE
3365 resultout := 'COMPLETE:N';
3366 END IF;
3367 ELSE
3368 resultout := 'COMPLETE:N';
3369 END IF;
3370
3371 RETURN;
3372 END IF; -- end RUN mode
3373
3374 ---- CANCEL mode -----
3375 IF (funcmode = 'CANCEL') THEN
3376 resultout := 'COMPLETE:';
3377 RETURN;
3378 END IF;
3379 --
3380 ---- TIMEOUT mode ----
3381 IF (funcmode = 'TIMEOUT') THEN
3382 resultout := 'COMPLETE:';
3383 return;
3384 END IF;
3385 --
3386 EXCEPTION
3387 WHEN OTHERS THEN
3388 WF_CORE.context(
3389 'OZF_AR_SETTLEMENT_PVT',
3390 'Check_Auto_Setl_Process',
3391 itemtype,
3392 itemkey,
3393 to_char(actid),
3394 funcmode
3395 );
3396 RAISE;
3397 END Check_Auto_Setl_Process;
3398
3399 END OZF_AR_SETTLEMENT_PVT;