[Home] [Help]
PACKAGE BODY: APPS.FUN_RECIPIENT_WF
Source
1 PACKAGE BODY FUN_RECIPIENT_WF AS
2 /* $Header: FUN_RECI_WF_B.pls 120.54.12010000.8 2009/02/21 00:09:35 makansal ship $ */
3
4
5 ------------------------- PRIVATE METHODS ------------------------------
6
7 /*-----------------------------------------------------
8 * PRIVATE FUNCTION generate_event_key
9 * ----------------------------------------------------
10 * Get the attributes for the recipient WF.
11 * ---------------------------------------------------
12
13 function generate_event_key (
14 batch_id in number,
15 trx_id in number) return varchar2
16 is
17 l_result varchar2(64);
18 begin
19 l_result := to_char(batch_id)||'_'||to_char(trx_id)||sys_guid();
20 return l_result;
21 end generate_event_key;
22 */
23
24
25 /*-----------------------------------------------------
26 * PRIVATE PROCEDURE delete_trx_batch
27 * ----------------------------------------------------
28 * Lock and delete a transaction. And if that is
29 * the last transaction in the batch, then delete the
30 * batch too.
31 * ---------------------------------------------------*/
32
33 PROCEDURE delete_trx_batch (
34 p_batch_id IN number,
35 p_trx_id IN number)
36 IS
37 l_n_trx number;
38 BEGIN
39 -- Lock batch.
40 SELECT batch_id INTO l_n_trx
41 FROM fun_trx_batches
42 WHERE batch_id = p_batch_id
43 FOR UPDATE;
44
45 SELECT COUNT(h.trx_id) INTO l_n_trx
46 FROM fun_trx_batches b,
47 fun_trx_headers h
48 WHERE h.batch_id = b.batch_id AND
49 b.batch_id = p_batch_id;
50
51 DELETE FROM fun_dist_lines
52 WHERE line_id IN
53 ( SELECT line_id
54 FROM fun_trx_lines
55 WHERE trx_id = p_trx_id );
56
57 DELETE FROM fun_trx_lines
58 WHERE trx_id = p_trx_id;
59
60 DELETE FROM fun_trx_headers
61 WHERE trx_id = p_trx_id;
62
63 -- Delete batch if I'm last.
64 IF(l_n_trx = 1) THEN
65 DELETE FROM fun_trx_batches
66 WHERE batch_id = p_batch_id;
67 END IF;
68
69 END delete_trx_batch;
70
71
72 /*-----------------------------------------------------
73 * PRIVATE FUNCTION make_batch_rec
74 * ----------------------------------------------------
75 * Return a batch_rec_type for this batch.
76 * ---------------------------------------------------*/
77
78 FUNCTION make_batch_rec (
79 p_batch_id IN number) RETURN fun_trx_pvt.batch_rec_type
80 IS
81 l_rec fun_trx_pvt.batch_rec_type;
82 BEGIN
83 SELECT batch_id, batch_number, initiator_id,
84 from_le_id, from_ledger_id, control_total,
85 currency_code, exchange_rate_type, status,
86 description, trx_type_id, trx_type_code,
87 gl_date, batch_date, reject_allow_flag,
88 from_recurring_batch_id
89 INTO l_rec.batch_id, l_rec.batch_number, l_rec.initiator_id,
90 l_rec.from_le_id, l_rec.from_ledger_id, l_rec.control_total,
91 l_rec.currency_code, l_rec.exchange_rate_type, l_rec.status,
92 l_rec.description, l_rec.trx_type_id, l_rec.trx_type_code,
93 l_rec.gl_date, l_rec.batch_date, l_rec.reject_allowed,
94 l_rec.from_recurring_batch
95 FROM fun_trx_batches
96 WHERE batch_id = p_batch_id;
97
98 RETURN l_rec;
99 END make_batch_rec;
100
101
102
103 /*-----------------------------------------------------
104 * PRIVATE FUNCTION make_trx_rec
105 * ----------------------------------------------------
106 * Return a trx_rec_type for this trx.
107 * ---------------------------------------------------*/
108
109 FUNCTION make_trx_rec (
110 p_trx_id IN number) RETURN fun_trx_pvt.trx_rec_type
111 IS
112 l_rec fun_trx_pvt.trx_rec_type;
113 BEGIN
114 SELECT trx_id, initiator_id, recipient_id,
115 to_le_id, to_ledger_id, batch_id,
116 status, init_amount_cr, init_amount_dr,
117 reci_amount_cr, reci_amount_dr, ar_invoice_number,
118 invoice_flag, approver_id, approval_date,
119 original_trx_id, reversed_trx_id, from_recurring_trx_id,
120 initiator_instance_flag, recipient_instance_flag
121 INTO l_rec.trx_id, l_rec.initiator_id, l_rec.recipient_id,
122 l_rec.to_le_id, l_rec.to_ledger_id, l_rec.batch_id,
123 l_rec.status, l_rec.init_amount_cr, l_rec.init_amount_dr,
124 l_rec.reci_amount_cr, l_rec.reci_amount_dr, l_rec.ar_invoice_number,
125 l_rec.invoicing_rule, l_rec.approver_id, l_rec.approval_date,
126 l_rec.original_trx_id, l_rec.reversed_trx_id, l_rec.from_recurring_trx_id,
127 l_rec.initiator_instance, l_rec.recipient_instance
128 FROM fun_trx_headers
129 WHERE trx_id = p_trx_id;
130
131 RETURN l_rec;
132 END make_trx_rec;
133
134
135
136 /*-----------------------------------------------------
137 * PRIVATE FUNCTION make_dist_lines_tbl
138 * ----------------------------------------------------
139 * Return a dist_line_tbl_type for this trx.
140 * ---------------------------------------------------*/
141
142 FUNCTION make_dist_lines_tbl (
143 p_trx_id IN number) RETURN fun_trx_pvt.dist_line_tbl_type
144 IS
145 l_tbl fun_trx_pvt.dist_line_tbl_type;
146 CURSOR c_dist IS
147 SELECT d.dist_id, d.line_id, d.party_id,
148 d.party_type_flag, d.dist_type_flag, d.batch_dist_id,
149 d.amount_cr, d.amount_dr, d.ccid
150 FROM fun_dist_lines d, fun_trx_lines l
151 WHERE party_type_flag = 'R' AND
152 d.line_id = l.line_id AND
153 l.trx_id = p_trx_id;
154 i number := 1;
155 BEGIN
156 OPEN c_dist;
157
158 LOOP
159 DECLARE
160 l_rec fun_trx_pvt.dist_line_rec_type;
161 BEGIN
162 FETCH c_dist INTO l_rec.dist_id, l_rec.line_id, l_rec.party_id,
163 l_rec.party_type, l_rec.dist_type, l_rec.batch_dist_id,
164 l_rec.amount_cr, l_rec.amount_dr, l_rec.ccid;
165 EXIT WHEN c_dist%NOTFOUND;
166
167 --l_tbl(l_rec.dist_id) := l_rec;
168 l_tbl(i) := l_rec;
169 i := i + 1;
170 END;
171 END LOOP;
172
173 RETURN l_tbl;
174 END make_dist_lines_tbl;
175
176
177
178 ------------------------- PUBLIC METHODS ------------------------------
179
180 /*-----------------------------------------------------
181 * PROCEDURE get_attr
182 * ----------------------------------------------------
183 * Get the attributes for the recipient WF.
184 * ---------------------------------------------------*/
185
186 PROCEDURE get_attr (
187 itemtype IN varchar2,
188 itemkey IN varchar2,
189 actid IN number,
190 funcmode IN varchar2,
191 resultout IN OUT NOCOPY varchar2)
192 IS
193 l_batch_id number;
194 l_trx_id number;
195
196 l_batch_num varchar2(15);
197 l_trx_num varchar2(15);
198 l_trx_amt varchar2(20);
199 l_initiator_name varchar2(360);
200 l_recipient_name varchar2(360);
201
202 BEGIN
203 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
204 fnd_msg_pub.initialize;
205
206 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
207 itemkey => itemkey,
208 aname => 'BATCH_ID');
209 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
210 itemkey => itemkey,
211 aname => 'TRX_ID');
212
213 -- SELECT batch_number INTO l_batch_num
214 -- FROM fun_trx_batches
215 -- WHERE batch_id = l_batch_id;
216
217 /*
218 SELECT
219 b.batch_number,
220 ltrim(to_char(decode(nvl(b.running_total_cr,0),
221 0, b.running_total_dr,
222 b.running_total_cr),'999999999.99'))||' '||b.currency_code,
223 h.party_name
224 INTO l_batch_num, l_trx_amt, l_initiator_name
225 FROM fun_trx_batches b, hz_parties h
226 WHERE batch_id = l_batch_id
227 AND b.initiator_id = h.party_id;
228 */
229 -- added to get transaction amount not batch amount
230
231 select b.batch_number,ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
232 0,h.reci_amount_dr,
233 h.reci_amount_cr),'999999999.99'))||' '||b.currency_code
234 into l_batch_num, l_trx_amt
235 from fun_trx_headers h, fun_trx_batches b
236 where b.batch_id = l_batch_id
237 and h.trx_id = l_trx_id;
238
239 -- to get initiator name
240
241 SELECT init.party_name
242 INTO l_initiator_name
243 FROM fun_trx_headers,
244 hz_parties init
245 WHERE trx_id = l_trx_id
246 AND initiator_id = init.party_id;
247
248
249 -- Modfied the query below to retrieve the recipient org name
250 SELECT trx_number ,
251 rec.party_name
252
253 INTO l_trx_num, l_recipient_name
254 FROM fun_trx_headers,
255 hz_parties rec
256 WHERE trx_id = l_trx_id
257 AND recipient_id = rec.party_id;
258
259 UPDATE fun_trx_headers
260 SET reci_wf_key = itemkey
261 WHERE trx_id = l_trx_id;
262
263
264 wf_engine.SetItemAttrText(itemtype => itemtype,
265 itemkey => itemkey,
266 aname => 'BATCH_NUMBER',
267 avalue => l_batch_num);
268 wf_engine.SetItemAttrText(itemtype => itemtype,
269 itemkey => itemkey,
270 aname => 'TRX_NUMBER',
271 avalue => l_trx_num);
272 Begin
273 wf_engine.SetItemAttrText(itemtype => itemtype,
274 itemkey => itemkey,
275 aname => 'TRX_AMT',
276 avalue => l_trx_amt);
277 wf_engine.SetItemAttrText(itemtype => itemtype,
278 itemkey => itemkey,
279 aname => 'INITIATOR_NAME',
280 avalue => l_initiator_name);
281
282 wf_engine.SetItemAttrText(itemtype => itemtype,
283 itemkey => itemkey,
284 aname => 'RECIPIENT_NAME',
285 avalue => l_recipient_name);
286 Exception
287 When others then
288 NULL;
289 End;
290
291 -- TODO: #FROM_ROLE
292
293 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
294 RETURN;
295 END IF;
296
297 resultout := wf_engine.eng_null;
298 RETURN;
299
300 EXCEPTION
301 WHEN others THEN
302 wf_core.context('FUN_RECIPIENT_WF', 'GET_ATTR',
303 itemtype, itemkey, TO_CHAR(actid), funcmode);
304 RAISE;
305 END get_attr;
306
307
308
309 /*-----------------------------------------------------
310 * PROCEDURE validate_trx
311 * ----------------------------------------------------
312 * Call the Transaction API to validate the trx.
313 * ---------------------------------------------------*/
314
315 PROCEDURE validate_trx (
316 itemtype IN varchar2,
317 itemkey IN varchar2,
318 actid IN number,
319 funcmode IN varchar2,
320 resultout IN OUT NOCOPY varchar2)
321 IS
322 l_trx_id number;
323 l_batch_id number;
324 l_status varchar2(1);
325 l_msg_count number := 0;
326 l_msg_data varchar2(1000);
327 l_batch_rec fun_trx_pvt.batch_rec_type;
328 l_trx_rec fun_trx_pvt.trx_rec_type;
329 l_dist_line_tbl fun_trx_pvt.dist_line_tbl_type;
330 BEGIN
331 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
332 l_trx_id := wf_engine.GetItemAttrNumber
333 (itemtype => itemtype,
334 itemkey => itemkey,
335 aname => 'TRX_ID');
336 l_batch_id := wf_engine.GetItemAttrNumber
337 (itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'BATCH_ID');
340
341 -- Beware of NO_DATA_FOUND.
342 /*l_batch_rec := make_batch_rec(l_batch_id);
343 l_trx_rec := make_trx_rec(l_trx_id);
344 l_dist_line_tbl := make_dist_lines_tbl(l_trx_id);
345
346 fun_trx_pvt.recipient_validate(
347 1.0, 'T', fnd_api.g_valid_level_full,
348 l_status, l_msg_count, l_msg_data,
349 l_batch_rec, l_trx_rec, l_dist_line_tbl);
350 */
351 -- TODO
352 IF (l_msg_count = 0) THEN
353 resultout := wf_engine.eng_completed||':T';
354 ELSE
355 wf_engine.SetItemAttrText(itemtype, itemkey, 'ERROR',
356 fun_wf_common.concat_msg_stack(fnd_msg_pub.count_msg));
357 resultout := wf_engine.eng_completed||':F';
358 END IF;
359
360 RETURN;
361 END IF;
362
363 resultout := wf_engine.eng_null;
364 RETURN;
365
366 EXCEPTION
367 WHEN others THEN
368 wf_core.context('FUN_RECIPIENT_WF', 'VALIDATE_TRX',
369 itemtype, itemkey, TO_CHAR(actid), funcmode);
370 RAISE;
371 END validate_trx;
372
373
374
375 /*-----------------------------------------------------
376 * PROCEDURE delete_trx
377 * ----------------------------------------------------
378 * Delete the transaction from the recipient's DB.
379 * ---------------------------------------------------*/
380
381 PROCEDURE delete_trx (
382 itemtype IN varchar2,
383 itemkey IN varchar2,
384 actid IN number,
385 funcmode IN varchar2,
386 resultout IN OUT NOCOPY varchar2)
387 IS
388 l_trx_id number;
389 l_batch_id number;
390 l_n_trx_left number;
391 BEGIN
392 IF (funcmode = 'RUN') THEN
393 l_trx_id := wf_engine.GetItemAttrNumber
394 (itemtype => itemtype,
395 itemkey => itemkey,
396 aname => 'TRX_ID');
397 l_batch_id := wf_engine.GetItemAttrNumber
398 (itemtype => itemtype,
399 itemkey => itemkey,
400 aname => 'BATCH_ID');
401 delete_trx_batch(l_batch_id, l_trx_id);
402 END IF;
403
404 resultout := wf_engine.eng_null;
405 RETURN;
406
407 EXCEPTION
408 WHEN others THEN
409 wf_core.context('FUN_RECIPIENT_WF', 'VALIDATE_TRX',
410 itemtype, itemkey, TO_CHAR(actid), funcmode);
411 RAISE;
412 END delete_trx;
413
414
415
416
417 /*-----------------------------------------------------
418 * PROCEDURE is_gl_batch_mode
419 * ----------------------------------------------------
420 * Check whether GL transfer is in batch mode.
421 * ---------------------------------------------------*/
422
423 PROCEDURE is_gl_batch_mode (
424 itemtype IN varchar2,
425 itemkey IN varchar2,
426 actid IN number,
427 funcmode IN varchar2,
428 resultout IN OUT NOCOPY varchar2)
429 IS
430 l_result boolean;
431 BEGIN
432 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
433 l_result := fun_system_options_pkg.is_gl_batch();
434 IF (l_result) THEN
435 resultout := wf_engine.eng_completed||':T';
436 ELSE
437 resultout := wf_engine.eng_completed||':F';
438 END IF;
439
440 RETURN;
441 END IF;
442
443 resultout := wf_engine.eng_null;
444 RETURN;
445
446 EXCEPTION
447 WHEN others THEN
448 wf_core.context('FUN_RECIPIENT_WF', 'IS_GL_BATCH_MODE',
449 itemtype, itemkey, TO_CHAR(actid), funcmode);
450 RAISE;
451 END is_gl_batch_mode;
452
453
454 /*-----------------------------------------------------
455 * PROCEDURE check_invoice_reqd
456 * ----------------------------------------------------
457 * Check whether this transaction requires invoice.
458 * ---------------------------------------------------*/
459
460 PROCEDURE check_invoice_reqd (
461 itemtype IN varchar2,
462 itemkey IN varchar2,
463 actid IN number,
464 funcmode IN varchar2,
465 resultout IN OUT NOCOPY varchar2)
466 IS
467 l_trx_id number;
468 l_result varchar2(1);
469 BEGIN
470 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
471 l_trx_id := wf_engine.GetItemAttrNumber
472 (itemtype => itemtype,
473 itemkey => itemkey,
474 aname => 'TRX_ID');
475
476 SELECT invoice_flag INTO l_result
477 FROM fun_trx_headers
478 WHERE trx_id = l_trx_id;
479
480 IF (l_result = 'Y') THEN
481 resultout := wf_engine.eng_completed||':T';
482 ELSE
483 resultout := wf_engine.eng_completed||':F';
484 END IF;
485 RETURN;
486 END IF;
487
488 resultout := wf_engine.eng_null;
489 RETURN;
490
491 EXCEPTION
492 WHEN others THEN
493 wf_core.context('FUN_RECIPIENT_WF', 'CHECK_INVOICE_REQD',
494 itemtype, itemkey, TO_CHAR(actid), funcmode);
495 RAISE;
496 END check_invoice_reqd;
497
498
499 /*-----------------------------------------------------
500 * PROCEDURE check_approval_result
501 * ----------------------------------------------------
502 * Check status: APPROVED or REJECTED.
503 * ---------------------------------------------------*/
504
505 PROCEDURE check_approval_result (
506 itemtype IN varchar2,
507 itemkey IN varchar2,
508 actid IN number,
509 funcmode IN varchar2,
510 resultout IN OUT NOCOPY varchar2)
511 IS
512 l_trx_id number;
513 l_status varchar2(15);
514 BEGIN
515 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
516 l_trx_id := wf_engine.GetItemAttrNumber
517 (itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'TRX_ID');
520 SELECT status INTO l_status
521 FROM fun_trx_headers
522 WHERE trx_id = l_trx_id;
523
524 IF(l_status = 'APPROVED') THEN
525 resultout := wf_engine.eng_completed||':APPROVED';
526 ELSIF(l_status = 'REJECTED') THEN
527 resultout := wf_engine.eng_completed||':REJECTED';
528 ELSIF(l_status = 'ERROR') THEN
529 resultout := wf_engine.eng_completed||':ERROR';
530 ELSE
531 resultout := wf_engine.eng_error||':'||wf_engine.eng_null;
532 END IF;
533 RETURN;
534 END IF;
535
536 resultout := wf_engine.eng_null;
537
538 EXCEPTION
539 WHEN others THEN
540 wf_core.context('FUN_RECIPIENT_WF', 'check_approval_result',
541 itemtype, itemkey, TO_CHAR(actid), funcmode);
542 RAISE;
543 END check_approval_result;
544
545
546 /*-----------------------------------------------------
547 * PROCEDURE is_manual_approval
548 * ----------------------------------------------------
549 * Check whether this transaction requires manual
550 * approval.
551 * ---------------------------------------------------*/
552
553 PROCEDURE is_manual_approval (
554 itemtype IN varchar2,
555 itemkey IN varchar2,
556 actid IN number,
557 funcmode IN varchar2,
558 resultout IN OUT NOCOPY varchar2)
559 IS
560 l_batch_id number;
561 l_result varchar2(1);
562 l_trx_id number;
563 l_status varchar2(1);
564 l_msg_count number;
565 l_msg_data varchar2(1000);
566 e_gen_acct_error EXCEPTION;
567 BEGIN
568 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
569 l_batch_id := wf_engine.GetItemAttrNumber
570 (itemtype => itemtype,
571 itemkey => itemkey,
572 aname => 'BATCH_ID');
573 l_trx_id := wf_engine.GetItemAttrNumber
574 (itemtype => itemtype,
575 itemkey => itemkey,
576 aname => 'TRX_ID');
577
578 SELECT t.manual_approve_flag INTO l_result
579 FROM fun_trx_batches b, fun_trx_types_b t
580 WHERE b.batch_id = l_batch_id AND
581 b.trx_type_id = t.trx_type_id;
582
583 IF (l_result = 'Y') THEN
584 resultout := wf_engine.eng_completed||':T';
585
586 ELSE
587
588 resultout := wf_engine.eng_completed||':F';
589 END IF;
590 RETURN;
591 END IF;
592
593 resultout := wf_engine.eng_null;
594 RETURN;
595
596 EXCEPTION
597 WHEN e_gen_acct_error THEN
598 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
599 itemtype, itemkey, TO_CHAR(actid), funcmode);
600 RAISE;
601
602 WHEN others THEN
603 wf_core.context('FUN_RECIPIENT_WF', 'IS_MANUAL_APPROVAL',
604 itemtype, itemkey, TO_CHAR(actid), funcmode);
605 RAISE;
606 END is_manual_approval;
607
608
609 /*-----------------------------------------------------
610 * PROCEDURE abort_approval
611 * ----------------------------------------------------
612 * Abort the accounting and approval process
613 * ---------------------------------------------------*/
614
615 PROCEDURE abort_approval (
616 itemtype IN varchar2,
617 itemkey IN varchar2,
618 actid IN number,
619 funcmode IN varchar2,
620 resultout IN OUT NOCOPY varchar2)
621 IS
622 BEGIN
623 IF (funcmode = 'RUN') THEN
624 BEGIN
625 wf_engine.AbortProcess
626 (itemtype => itemtype,
627 itemkey => itemkey,
628 process => 'ACCOUNTING_AND_APPROVAL');
629 --exception
630 -- when others then null;
631 END;
632 END IF;
633
634 EXCEPTION
635 WHEN others THEN
636 wf_core.context('FUN_RECIPIENT_WF', 'ABORT_APPROVAL',
637 itemtype, itemkey, TO_CHAR(actid), funcmode);
638 RAISE;
639 END abort_approval;
640
641
642 /*-----------------------------------------------------
643 * PROCEDURE generate_approval_doc
644 * ----------------------------------------------------
645 * Generate the approval document.
646 * ---------------------------------------------------*/
647
648 PROCEDURE generate_approval_doc (
649 document_id IN number,
650 display_type IN varchar2,
651 document IN OUT NOCOPY varchar2,
652 document_type IN OUT NOCOPY varchar2)
653 IS
654 BEGIN
655 NULL;
656 -- TODO
657 END generate_approval_doc;
658
659
660 /*-----------------------------------------------------
661 * PROCEDURE is_same_instance
662 * ----------------------------------------------------
663 * Check whether the initiator and recipient are on the
664 * same instance.
665 * ---------------------------------------------------*/
666
667 PROCEDURE is_same_instance (
668 itemtype IN varchar2,
669 itemkey IN varchar2,
670 actid IN number,
671 funcmode IN varchar2,
672 resultout IN OUT NOCOPY varchar2)
673 IS
674 l_trx_id number;
675 l_result varchar2(1);
676 BEGIN
677 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
678 l_trx_id := wf_engine.GetItemAttrNumber
679 (itemtype => itemtype,
680 itemkey => itemkey,
681 aname => 'TRX_ID');
682
683 SELECT initiator_instance_flag INTO l_result
684 FROM fun_trx_headers
685 WHERE trx_id = l_trx_id;
686
687 IF (l_result = 'Y') THEN
688 resultout := wf_engine.eng_completed||':T';
689 ELSE
690 resultout := wf_engine.eng_completed||':F';
691 END IF;
692 RETURN;
693 END IF;
694
695 resultout := wf_engine.eng_null;
696 RETURN;
697
698 EXCEPTION
699 WHEN others THEN
700 wf_core.context('FUN_RECIPIENT_WF', 'IS_SAME_INSTANCE',
701 itemtype, itemkey, TO_CHAR(actid), funcmode);
702 RAISE;
703 END is_same_instance;
704
705
706
707 /*-----------------------------------------------------
708 * PROCEDURE get_contact
709 * ----------------------------------------------------
710 * Get the contact for this party.
711 * ---------------------------------------------------*/
712
713 PROCEDURE get_contact (
714 itemtype IN varchar2,
715 itemkey IN varchar2,
716 actid IN number,
717 funcmode IN varchar2,
718 resultout IN OUT NOCOPY varchar2)
719 IS
720 l_contact varchar2(30);
721 l_trx_id number;
722 l_party_id number;
723 BEGIN
724 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
725 l_trx_id := wf_engine.GetItemAttrNumber
726 (itemtype => itemtype,
727 itemkey => itemkey,
728 aname => 'TRX_ID');
729
730 SELECT recipient_id INTO l_party_id
731 FROM fun_trx_headers
732 WHERE trx_id = l_trx_id;
733
734 l_contact := fun_wf_common.get_contact_role(l_party_id);
735 wf_engine.SetItemAttrText(itemtype => itemtype,
736 itemkey => itemkey,
737 aname => 'CONTACT',
738 avalue => l_contact);
739 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
740 RETURN;
741 END IF;
742
743 resultout := wf_engine.eng_null;
744 RETURN;
745
746 EXCEPTION
747 WHEN others THEN
748 wf_core.context('FUN_RECIPIENT_WF', 'GET_CONTACT',
749 itemtype, itemkey, TO_CHAR(actid), funcmode);
750 RAISE;
751 END get_contact;
752
753
754
755 /*-----------------------------------------------------
756 * PROCEDURE check_allow_reject
757 * ----------------------------------------------------
758 * Check whether this transaction requires manual
759 * approval.
760 * ---------------------------------------------------*/
761
762 PROCEDURE check_allow_reject (
763 itemtype IN varchar2,
764 itemkey IN varchar2,
765 actid IN number,
766 funcmode IN varchar2,
767 resultout IN OUT NOCOPY varchar2)
768 IS
769 l_batch_id number;
770 l_result varchar2(1);
771 BEGIN
772 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
773 l_batch_id := wf_engine.GetItemAttrNumber
774 (itemtype => itemtype,
775 itemkey => itemkey,
776 aname => 'BATCH_ID');
777
778 SELECT reject_allow_flag INTO l_result
779 FROM fun_trx_batches
780 WHERE batch_id = l_batch_id;
781
782 IF (l_result = 'Y') THEN
783 resultout := wf_engine.eng_completed||':T';
784 ELSE
785 resultout := wf_engine.eng_completed||':F';
786 END IF;
787 RETURN;
788 END IF;
789
790 resultout := wf_engine.eng_null;
791 RETURN;
792
793 EXCEPTION
794 WHEN others THEN
795 wf_core.context('FUN_RECIPIENT_WF', 'CHECK_ALLOW_REJECT',
796 itemtype, itemkey, TO_CHAR(actid), funcmode);
797 RAISE;
798 END check_allow_reject;
799
800
801 /*-----------------------------------------------------
802 * PROCEDURE post_approval_ntf
803 * ----------------------------------------------------
804 * Check whether anyone has already approved or
805 * or rejected the transaction.
806 * ---------------------------------------------------*/
807
808 PROCEDURE post_approval_ntf (
809 itemtype IN varchar2,
810 itemkey IN varchar2,
811 actid IN number,
812 funcmode IN varchar2,
813 resultout IN OUT NOCOPY varchar2)
814 IS
815 l_nid number;
816 l_result varchar2(30);
817 l_trx_id number;
818 l_status varchar2(1);
819 l_msg_count number;
820 l_msg_data varchar2(1000);
821 l_reason varchar2(240);
822 l_forward_role wf_roles.name%TYPE;
823 l_valid_user VARCHAR2(1);
824 l_due_date DATE;
825 l_priority NUMBER;
826 l_message_name wf_notifications.message_name%TYPE;
827 l_message_type wf_notifications.message_type%TYPE;
828 l_user_role wf_notifications.recipient_role%TYPE;
829 l_ntf_status wf_notifications.status%TYPE;
830
831 l_approver_record ame_util.approverRecord2;
832 l_forwardee_record ame_util.approverRecord2;
833 l_user_id fnd_user.user_id%TYPE;
834
835 CURSOR c_get_userid (p_user_name VARCHAR2) IS
836 SELECT usr.user_id
837 FROM fnd_user usr
838 WHERE usr.user_name = p_user_name;
839
840 BEGIN
841 l_nid := wf_engine.context_nid;
842 l_result := wf_notification.GetAttrText(l_nid, 'RESULT');
843 l_trx_id := wf_engine.GetItemAttrNumber
844 (itemtype => itemtype,
845 itemkey => itemkey,
846 aname => 'TRX_ID');
847
848 IF(funcmode = 'RESPOND') THEN
849 l_approver_record.name := wf_engine.context_text;
850 IF l_approver_record.name IS NULL
851 THEN
852 -- This might happen if user has approved the transaction
853 -- from the Inbound Transaction UI
854 l_approver_record.name := wf_engine.GetItemAttrText
855 (itemtype => itemtype,
856 itemkey => itemkey,
857 aname => 'UI_ACTION_USER_NAME');
858
859 l_user_id := wf_engine.GetItemAttrNumber
860 (itemtype => itemtype,
861 itemkey => itemkey,
862 aname => 'UI_ACTION_USER_ID');
863
864 ELSE
865 -- Get the user id for the user name.
866 OPEN c_get_userid ( l_approver_record.name);
867 FETCH c_get_userid INTO l_user_id;
868 CLOSE c_get_userid;
869 END IF;
870
871 IF(l_result = 'APPROVE') or (l_result = 'APPROVED') THEN
872 l_approver_record.approval_status := AME_UTIL.approvedStatus;
873
874 ame_api2.updateApprovalStatus(
875 applicationIdIn => 435,
876 transactionTypeIn => 'FUN_IC_RECI_TRX',
877 transactionIdIn => l_trx_id,
878 approverIn => l_approver_record);
879
880 -- Update the approver id onto the fun_trx_headers table.
881 -- approver_id will always hold the id of the last person
882 -- to approve the transaction.
883 UPDATE fun_trx_headers
884 SET approver_id = l_user_id,
885 approval_date = SYSDATE
886 WHERE trx_id = l_trx_id;
887
888
889 ELSIF (l_result = 'REJECT') or (l_result = 'REJECTED')
890 THEN
891 l_reason := wf_engine.GetItemAttrText(itemtype, itemkey, 'REASON');
892
893 fun_trx_pvt.update_trx_status
894 (p_api_version => 1.0,
895 x_return_status => l_status,
896 x_msg_count => l_msg_count,
897 x_msg_data => l_msg_data,
898 p_trx_id => l_trx_id,
899 p_update_status_to => 'REJECTED');
900
901 UPDATE fun_trx_headers
902 SET reject_reason = l_reason
903 WHERE trx_id = l_trx_id;
904
905 -- Update the AME System with the 'APPROVED' status.
906 l_approver_record.approval_status := AME_UTIL.rejectStatus;
907
908 ame_api2.updateApprovalStatus(
909 applicationIdIn => 435,
910 transactionTypeIn => 'FUN_IC_RECI_TRX',
911 transactionIdIn => l_trx_id,
912 approverIn => l_approver_record);
913
914 END IF;
915
916 IF(l_status = fnd_api.g_ret_sts_success) THEN
917 resultout := wf_engine.eng_completed||':'||l_result;
918 RETURN;
919 ELSE
920 resultout := wf_engine.eng_error||':'||wf_engine.eng_null;
921 -- TODO: Process error
922 IF(l_msg_count >= 1) THEN
923 wf_core.Raise(fnd_msg_pub.get);
924 END IF;
925 END IF;
926
927 END IF; -- funcmode = 'RESPOND'
928
929 IF funcmode IN ('FORWARD', 'TRANSFER')
930 THEN
931 l_forward_role := wf_engine.context_text;
932
933 l_valid_user := fun_wf_common.is_user_valid_approver
934 (p_transaction_id => l_trx_id,
935 p_user_id => NULL,
936 p_role_name => l_forward_role,
937 p_org_type => 'R',
938 p_mode => 'WF');
939
940 IF l_valid_user = 'N'
941 THEN
942 wf_core.Raise('FUN_INVALID_USER_FORWARD');
943 ELSE
944 -- Get the role of the person to whom the notification was originally
945 -- assigned
946
947 wf_notification.getInfo(l_nid, l_user_role, l_message_type,
948 l_message_name, l_priority,
949 l_due_date, l_ntf_status);
950
951
952 l_approver_record.approval_status := AME_UTIL.forwardStatus;
953 l_approver_record.name := l_user_role;
954 l_forwardee_record.name := l_forward_role;
955
956 ame_api2.updateApprovalStatus(
957 applicationIdIn => 435,
958 transactionTypeIn => 'FUN_IC_RECI_TRX',
959 transactionIdIn => l_trx_id,
960 approverIn => l_approver_record,
961 forwardeeIn => l_forwardee_record);
962
963 END IF;
964
965 END IF; -- funcmode = 'FORWARD',
966
967 resultout := wf_engine.eng_null;
968
969 EXCEPTION
970 WHEN others THEN
971 wf_core.context('FUN_RECIPIENT_WF', 'POST_APPROVAL_NTF',
972 itemtype, itemkey, TO_CHAR(actid), funcmode);
973 RAISE;
974 END post_approval_ntf;
975
976
977
978 /*-----------------------------------------------------
979 * PROCEDURE check_ap_setup
980 * ----------------------------------------------------
981 * Check that AP is setup correctly with supplier and
982 * open period and all that.
983 * ---------------------------------------------------*/
984
985 PROCEDURE check_ap_setup (
986 itemtype IN varchar2,
987 itemkey IN varchar2,
988 actid IN number,
989 funcmode IN varchar2,
990 resultout IN OUT NOCOPY varchar2)
991 IS
992 l_trx_id number;
993 l_batch_id number;
994 l_vendor_id number;
995 l_site_id number;
996 l_gl_date date;
997 l_to_ledger_id number;
998 l_from_le_id number;
999 l_from_org_id number;
1000 l_to_le_id number;
1001 l_to_org_id number;
1002 l_period_status varchar2(1);
1003 l_success boolean := TRUE;
1004 x_msg_data VARCHAR2(1000);
1005 l_initiator_name varchar2(200);
1006 l_trx_amt varchar2(200);
1007 l_batch_num varchar2(15);
1008 l_trx_date date;
1009 l_recipient_id number;
1010 l_initiator_id number;
1011
1012
1013 BEGIN
1014 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
1015 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'BATCH_ID');
1018 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1019 itemkey => itemkey,
1020 aname => 'TRX_ID');
1021
1022 SELECT b.from_le_id, b.gl_date, h.to_le_id, h.to_ledger_id,
1023 fun_tca_pkg.get_ou_id(h.initiator_id),
1024 fun_tca_pkg.get_ou_id(h.recipient_id),
1025 b.batch_date, b.initiator_id, h.recipient_id
1026 INTO l_from_le_id, l_gl_date, l_to_le_id, l_to_ledger_id,
1027 l_from_org_id, l_to_org_id, l_trx_date,
1028 l_initiator_id,
1029 l_recipient_id
1030 FROM fun_trx_batches b,
1031 fun_trx_headers h
1032 WHERE b.batch_id = l_batch_id AND
1033 h.trx_id = l_trx_id AND
1034 h.batch_id = b.batch_id;
1035
1036 fnd_msg_pub.initialize;
1037
1038 -- Valid Org
1039 IF (l_to_org_id IS NULL) THEN
1040 fnd_message.set_name('FUN', 'FUN_API_INVALID_OU');
1041 fnd_msg_pub.add;
1042 l_success := FALSE;
1043 ELSE
1044 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'ORG_ID', l_to_org_id);
1045 END IF;
1046
1047 -- Valid period
1048 l_period_status := fun_gl_transfer.get_period_status(200, l_gl_date, l_to_ledger_id);
1049 IF (l_period_status NOT IN ('O', 'F')) THEN
1050 fnd_message.set_name('FUN', 'FUN_API_AP_PERIOD_NOT_OPEN');
1051 fnd_msg_pub.add;
1052 l_success := FALSE;
1053 END IF;
1054
1055 -- Valid supplier
1056 IF (NOT fun_trading_relation.get_supplier(
1057 'INTERCOMPANY',
1058 l_from_le_id,
1059 l_to_le_id,
1060 l_from_org_id,
1061 l_to_org_id,
1062 l_initiator_id,
1063 l_recipient_id,
1064 l_trx_date,
1065 x_msg_data,
1066 l_vendor_id,
1067 l_site_id)) THEN
1068 fnd_message.set_name('FUN', 'FUN_API_INVALID_SUPPLIER');
1069 fnd_msg_pub.add;
1070 l_success := FALSE;
1071 ELSE
1072 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'VENDOR_ID', l_vendor_id);
1073 wf_engine.SetItemAttrNumber(itemtype, itemkey, 'SITE_ID', l_site_id);
1074 END IF;
1075
1076 IF (l_success) THEN
1077 resultout := wf_engine.eng_completed||':T';
1078 ELSE
1079
1080 -- added by rani shergill for notifications - start
1081
1082 select ltrim(to_char(decode(nvl(h.reci_amount_cr,0),
1083 0,h.reci_amount_dr,
1084 h.reci_amount_cr),'999999999.99'))||' '||b.currency_code
1085 into l_trx_amt
1086 from fun_trx_headers h, fun_trx_batches b
1087 where b.batch_id = l_batch_id
1088 and h.trx_id = l_trx_id;
1089
1090 wf_engine.SetItemAttrText(itemtype => itemtype,
1091 itemkey => itemkey,
1092 aname => 'TRX_AMOUNT',
1093 avalue => l_trx_amt);
1094
1095
1096
1097 SELECT init.party_name
1098 INTO l_initiator_name
1099 FROM fun_trx_headers,
1100 hz_parties init
1101 WHERE trx_id = l_trx_id
1102 AND initiator_id = init.party_id;
1103
1104 wf_engine.SetItemAttrText(itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'INITIATOR_NAME',
1107 avalue => l_initiator_name);
1108
1109
1110 --added by rani - end
1111
1112 wf_engine.SetItemAttrText(itemtype, itemkey, 'ERROR',
1113 fun_wf_common.concat_msg_stack(fnd_msg_pub.count_msg));
1114 resultout := wf_engine.eng_completed||':F';
1115
1116 END IF;
1117 RETURN;
1118 END IF;
1119
1120 resultout := wf_engine.eng_null;
1121
1122 EXCEPTION
1123 WHEN others THEN
1124 wf_core.context('FUN_RECIPIENT_WF', 'CHECK_AP_SETUP',
1125 itemtype, itemkey, TO_CHAR(actid), funcmode);
1126 RAISE;
1127 END check_ap_setup;
1128
1129
1130
1131
1132 /*-----------------------------------------------------
1133 * PROCEDURE transfer_to_ap
1134 * ----------------------------------------------------
1135 * Transfer to AP. Wrapper for
1136 * FUN_AP_TRANSFER.LOCK_AND_TRANSFER.
1137 *
1138 * If LOCK_AND_TRANSFER returns false, it means the
1139 * status is incorrect, i.e. the trx is already
1140 * transferred. So we abort our WF process.
1141 * ---------------------------------------------------*/
1142
1143 PROCEDURE transfer_to_ap (
1144 itemtype IN varchar2,
1145 itemkey IN varchar2,
1146 actid IN number,
1147 funcmode IN varchar2,
1148 resultout IN OUT NOCOPY varchar2)
1149 IS
1150 l_vendor_id number;
1151 l_site_id number;
1152 l_trx_id number;
1153 l_batch_id number;
1154 l_batch_date date;
1155 l_gl_date date;
1156 l_approval_date date;
1157 l_org_id number;
1158 l_from_org_id number;
1159 l_currency varchar2(15);
1160 l_invoice_num varchar2(50);
1161 l_success boolean;
1162 l_status varchar2(15);
1163 l_exchange_rate_type fun_trx_batches.exchange_rate_type%TYPE;
1164 BEGIN
1165 IF (funcmode = 'RUN') THEN
1166 l_vendor_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1167 itemkey => itemkey,
1168 aname => 'VENDOR_ID');
1169 l_site_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'SITE_ID');
1172 l_org_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1173 itemkey => itemkey,
1174 aname => 'ORG_ID');
1175 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1176 itemkey => itemkey,
1177 aname => 'TRX_ID');
1178 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
1179 itemkey => itemkey,
1180 aname => 'BATCH_ID');
1181
1182 SELECT batch_date, gl_date, currency_code,
1183 exchange_rate_type,
1184 fun_tca_pkg.get_ou_id(initiator_id) from_org_id
1185 INTO l_batch_date, l_gl_date, l_currency,
1186 l_exchange_rate_type,
1187 l_from_org_id
1188 FROM fun_trx_batches
1189 WHERE batch_id = l_batch_id;
1190
1191 SELECT ar_invoice_number INTO l_invoice_Num
1192 FROM fun_trx_headers
1193 WHERE trx_id = l_trx_id;
1194
1195 l_success := fun_ap_transfer.lock_and_transfer(
1196 l_trx_id, l_batch_date, l_vendor_id,
1197 l_site_id, l_gl_date, l_currency,
1198 l_exchange_rate_type,
1199 'GLOBAL_INTERCOMPANY', l_approval_date,
1200 l_org_id, l_invoice_num,
1201 l_from_org_id);
1202
1203 IF (NOT l_success) THEN
1204 SELECT status INTO l_status
1205 FROM fun_trx_headers
1206 WHERE trx_id = l_trx_id;
1207
1208 IF (l_status <> 'XFER_AR') then
1209 wf_engine.AbortProcess(itemtype => itemtype,
1210 itemkey => itemkey,
1211 process => 'AP_TRANSFER');
1212 ELSE
1213 RAISE ap_transfer_failure;
1214 END IF;
1215 END IF;
1216
1217 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1218 END IF;
1219
1220 resultout := wf_engine.eng_null;
1221 EXCEPTION
1222 WHEN others THEN
1223 wf_core.context('FUN_RECIPIENT_WF', 'TRANSFER_TO_AP',
1224 itemtype, itemkey, TO_CHAR(actid), funcmode);
1225 RAISE;
1226 END transfer_to_ap;
1227
1228
1229
1230 /*-----------------------------------------------------
1231 * PROCEDURE raise_error
1232 * ----------------------------------------------------
1233 * Raise the error event.
1234 * ---------------------------------------------------*/
1235
1236 PROCEDURE raise_error (
1237 itemtype IN varchar2,
1238 itemkey IN varchar2,
1239 actid IN number,
1240 funcmode IN varchar2,
1241 resultout IN OUT NOCOPY varchar2)
1242 IS
1243 l_batch_id number;
1244 l_trx_id number;
1245 l_event_key varchar2(240);
1246 l_params wf_parameter_list_t := wf_parameter_list_t();
1247 BEGIN
1248 IF (funcmode = 'RUN') THEN
1249 l_batch_id := wf_engine.GetItemAttrNumber
1250 (itemtype => itemtype,
1251 itemkey => itemkey,
1252 aname => 'BATCH_ID');
1253 l_trx_id := wf_engine.GetItemAttrNumber
1254 (itemtype => itemtype,
1255 itemkey => itemkey,
1256 aname => 'TRX_ID');
1257 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
1258
1259 wf_event.AddParameterToList(p_name => 'TRX_ID',
1260 p_value => TO_CHAR(l_trx_id),
1261 p_parameterlist => l_params);
1262 wf_event.AddParameterToList(p_name => 'BATCH_ID',
1263 p_value => TO_CHAR(l_batch_id),
1264 p_parameterlist => l_params);
1265
1266 wf_event.raise(
1267 p_event_name => 'oracle.apps.fun.manualtrx.error.send',
1268 p_event_key => l_event_key,
1269 p_parameters => l_params);
1270
1271 l_params.delete();
1272 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1273 RETURN;
1274 END IF;
1275
1276 resultout := wf_engine.eng_null;
1277 RETURN;
1278
1279 EXCEPTION
1280 WHEN others THEN
1281 wf_core.context('FUN_RECIPIENT_WF', 'RAISE_ERROR',
1282 itemtype, itemkey, TO_CHAR(actid), funcmode);
1283 RAISE;
1284 END raise_error;
1285
1286
1287 /*-----------------------------------------------------
1288 * PROCEDURE raise_received
1289 * ----------------------------------------------------
1290 * Raise the received event.
1291 * ---------------------------------------------------*/
1292
1293 PROCEDURE raise_received (
1294 itemtype IN varchar2,
1295 itemkey IN varchar2,
1296 actid IN number,
1297 funcmode IN varchar2,
1298 resultout IN OUT NOCOPY varchar2)
1299 IS
1300 l_batch_id number;
1301 l_trx_id number;
1302 l_event_key varchar2(240);
1303 l_resp_id number;
1304 l_user_id NUMBER;
1305 l_appl_id NUMBER;
1306 l_params wf_parameter_list_t := wf_parameter_list_t();
1307 BEGIN
1308 -- Bug 7639191
1309 IF (funcmode = 'RUN') THEN
1310 l_batch_id := wf_engine.GetItemAttrNumber
1311 (itemtype => itemtype,
1312 itemkey => itemkey,
1313 aname => 'BATCH_ID');
1314 l_trx_id := wf_engine.GetItemAttrNumber
1315 (itemtype => itemtype,
1316 itemkey => itemkey,
1317 aname => 'TRX_ID');
1318 l_resp_id := wf_engine.GetItemAttrNumber
1319 (itemtype => itemtype,
1320 itemkey => itemkey,
1321 aname =>'RESP_ID');
1322 l_user_id := wf_engine.GetItemAttrNumber
1323 (itemtype => itemtype,
1324 itemkey => itemkey,
1325 aname =>'USER_ID');
1326 l_appl_id := wf_engine.GetItemAttrNumber
1327 (itemtype => itemtype,
1328 itemkey => itemkey,
1329 aname =>'APPL_ID');
1330
1331 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
1332
1333 wf_event.AddParameterToList(p_name => 'TRX_ID',
1334 p_value => TO_CHAR(l_trx_id),
1335 p_parameterlist => l_params);
1336 wf_event.AddParameterToList(p_name => 'BATCH_ID',
1337 p_value => TO_CHAR(l_batch_id),
1338 p_parameterlist => l_params);
1339 WF_EVENT.AddParameterToList(p_name=>'RESP_ID',
1340 p_value=>TO_CHAR(l_resp_id),
1341 p_parameterlist=>l_params);
1342 WF_EVENT.AddParameterToList(p_name=>'USER_ID',
1343 p_value=>TO_CHAR(l_user_id),
1344 p_parameterlist=>l_params);
1345 WF_EVENT.AddParameterToList(p_name=>'APPL_ID',
1346 p_value=>TO_CHAR(l_appl_id),
1347 p_parameterlist=>l_params);
1348
1349 wf_event.raise(
1350 p_event_name => 'oracle.apps.fun.manualtrx.reception.send',
1351 p_event_key => l_event_key,
1352 p_parameters => l_params);
1353
1354 l_params.delete();
1355 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1356 RETURN;
1357 END IF;
1358
1359 resultout := wf_engine.eng_null;
1360 RETURN;
1361
1362 EXCEPTION
1363 WHEN others THEN
1364 wf_core.context('FUN_RECIPIENT_WF', 'RAISE_RECEIVED',
1365 itemtype, itemkey, TO_CHAR(actid), funcmode);
1366 RAISE;
1367 END raise_received;
1368
1369
1370
1371 /*-----------------------------------------------------
1372 * PROCEDURE raise_reject
1373 * ----------------------------------------------------
1374 * Raise the rejection event.
1375 * ---------------------------------------------------*/
1376
1377 PROCEDURE raise_reject (
1378 itemtype IN varchar2,
1379 itemkey IN varchar2,
1380 actid IN number,
1381 funcmode IN varchar2,
1382 resultout IN OUT NOCOPY varchar2)
1383 IS
1384 l_batch_id number;
1385 l_trx_id number;
1386 l_event_key varchar2(240);
1387 l_params wf_parameter_list_t := wf_parameter_list_t();
1388 BEGIN
1389 IF (funcmode = 'RUN') THEN
1390 l_batch_id := wf_engine.GetItemAttrNumber
1391 (itemtype => itemtype,
1392 itemkey => itemkey,
1393 aname => 'BATCH_ID');
1394 l_trx_id := wf_engine.GetItemAttrNumber
1395 (itemtype => itemtype,
1396 itemkey => itemkey,
1397 aname => 'TRX_ID');
1398 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
1399
1400 wf_event.AddParameterToList(p_name => 'TRX_ID',
1401 p_value => TO_CHAR(l_trx_id),
1402 p_parameterlist => l_params);
1403 wf_event.AddParameterToList(p_name => 'BATCH_ID',
1404 p_value => TO_CHAR(l_batch_id),
1405 p_parameterlist => l_params);
1406
1407 wf_event.raise(
1408 p_event_name => 'oracle.apps.fun.manualtrx.rejection.send',
1409 p_event_key => l_event_key,
1410 p_parameters => l_params);
1411
1412 l_params.delete();
1413 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1414 RETURN;
1415 END IF;
1416
1417 resultout := wf_engine.eng_null;
1418 RETURN;
1419
1420 EXCEPTION
1421 WHEN others THEN
1422 wf_core.context('FUN_RECIPIENT_WF', 'RAISE_REJECT',
1423 itemtype, itemkey, TO_CHAR(actid), funcmode);
1424 RAISE;
1425 END raise_reject;
1426
1427
1428 /*-----------------------------------------------------
1429 * PROCEDURE raise_approve
1430 * ----------------------------------------------------
1431 * Raise the approve event.
1432 * ---------------------------------------------------*/
1433
1434 PROCEDURE raise_approve (
1435 itemtype IN varchar2,
1436 itemkey IN varchar2,
1437 actid IN number,
1438 funcmode IN varchar2,
1439 resultout IN OUT NOCOPY varchar2)
1440 IS
1441 l_batch_id number;
1442 l_trx_id number;
1443 l_event_key varchar2(240);
1444 l_params wf_parameter_list_t := wf_parameter_list_t();
1445 BEGIN
1446 IF (funcmode = 'RUN') THEN
1447 l_batch_id := wf_engine.GetItemAttrNumber
1448 (itemtype => itemtype,
1449 itemkey => itemkey,
1450 aname => 'BATCH_ID');
1451 l_trx_id := wf_engine.GetItemAttrNumber
1452 (itemtype => itemtype,
1453 itemkey => itemkey,
1454 aname => 'TRX_ID');
1455 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
1456
1457 wf_event.AddParameterToList(p_name => 'TRX_ID',
1458 p_value => TO_CHAR(l_trx_id),
1459 p_parameterlist => l_params);
1460 wf_event.AddParameterToList(p_name => 'BATCH_ID',
1461 p_value => TO_CHAR(l_batch_id),
1462 p_parameterlist => l_params);
1463
1464 wf_event.raise(
1465 p_event_name => 'oracle.apps.fun.manualtrx.approval.send',
1466 p_event_key => l_event_key,
1467 p_parameters => l_params);
1468
1469 l_params.delete();
1470 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1471 RETURN;
1472 END IF;
1473
1474 resultout := wf_engine.eng_null;
1475 RETURN;
1476
1477 EXCEPTION
1478 WHEN others THEN
1479 wf_core.context('FUN_RECIPIENT_WF', 'RAISE_APPROVE',
1480 itemtype, itemkey, TO_CHAR(actid), funcmode);
1481 RAISE;
1482 END raise_approve;
1483
1484
1485 /*-----------------------------------------------------
1486 * PROCEDURE raise_gl_transfer
1487 * ----------------------------------------------------
1488 * Raise the transfer to gl event.
1489 * ---------------------------------------------------*/
1490
1491 PROCEDURE raise_gl_transfer (
1492 itemtype IN varchar2,
1493 itemkey IN varchar2,
1494 actid IN number,
1495 funcmode IN varchar2,
1496 resultout IN OUT NOCOPY varchar2)
1497 IS
1498 l_party_id number;
1499 l_batch_id number;
1500 l_trx_id number;
1501 l_event_key varchar2(64);
1502 l_params wf_parameter_list_t := wf_parameter_list_t();
1503 BEGIN
1504 IF (funcmode = 'RUN') THEN
1505 l_batch_id := wf_engine.GetItemAttrNumber
1506 (itemtype => itemtype,
1507 itemkey => itemkey,
1508 aname => 'BATCH_ID');
1509 l_trx_id := wf_engine.GetItemAttrNumber
1510 (itemtype => itemtype,
1511 itemkey => itemkey,
1512 aname => 'TRX_ID');
1513 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
1514
1515 SELECT recipient_id INTO l_party_id
1516 FROM fun_trx_headers
1517 WHERE trx_id = l_trx_id;
1518
1519 wf_event.AddParameterToList(p_name => 'TRX_ID',
1520 p_value => TO_CHAR(l_trx_id),
1521 p_parameterlist => l_params);
1522 wf_event.AddParameterToList(p_name => 'BATCH_ID',
1523 p_value => TO_CHAR(l_batch_id),
1524 p_parameterlist => l_params);
1525 wf_event.AddParameterToList(p_name => 'PARTY_ID',
1526 p_value => TO_CHAR(l_party_id),
1527 p_parameterlist => l_params);
1528
1529 wf_event.raise(
1530 p_event_name => 'oracle.apps.fun.manualtrx.gl.transfer',
1531 p_event_key => l_event_key,
1532 p_parameters => l_params);
1533
1534 l_params.delete();
1535 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1536 RETURN;
1537 END IF;
1538
1539 resultout := wf_engine.eng_null;
1540 RETURN;
1541
1542 EXCEPTION
1543 WHEN others THEN
1544 wf_core.context('FUN_RECIPIENT_WF', 'RAISE_GL_TRANSFER',
1545 itemtype, itemkey, TO_CHAR(actid), funcmode);
1546 RAISE;
1547 END raise_gl_transfer;
1548
1549
1550 /*-----------------------------------------------------
1551 * PROCEDURE update_status_error
1552 * ----------------------------------------------------
1553 * Update status to error.
1554 * ---------------------------------------------------
1555
1556 PROCEDURE update_status_error (
1557 itemtype IN varchar2,
1558 itemkey IN varchar2,
1559 actid IN number,
1560 funcmode IN varchar2,
1561 resultout IN OUT NOCOPY varchar2)
1562 IS
1563 l_trx_id number;
1564 l_status varchar2(1);
1565 l_msg_count number;
1566 l_msg_data varchar2(1000);
1567 BEGIN
1568 IF (funcmode = 'RUN') THEN
1569 l_trx_id := wf_engine.GetItemAttrNumber
1570 (itemtype => itemtype,
1571 itemkey => itemkey,
1572 aname => 'TRX_ID');
1573
1574 fun_trx_pvt.update_trx_status
1575 (p_api_version => 1.0,
1576 x_return_status => l_status,
1577 x_msg_count => l_msg_count,
1578 x_msg_data => l_msg_data,
1579 p_trx_id => l_trx_id,
1580 p_update_status_to => 'ERROR');
1581 -- TODO: check return status
1582 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1583 RETURN;
1584 END IF;
1585
1586 resultout := wf_engine.eng_null;
1587 RETURN;
1588
1589 EXCEPTION
1590 WHEN others THEN
1591 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_ERROR',
1592 itemtype, itemkey, TO_CHAR(actid), funcmode);
1593 RAISE;
1594 END update_status_error;
1595 */
1596
1597 /*-----------------------------------------------------
1598 * PROCEDURE update_status_rejected
1599 * ----------------------------------------------------
1600 * Update status to rejected.
1601 * ---------------------------------------------------*/
1602
1603 PROCEDURE update_status_rejected (
1604 itemtype IN varchar2,
1605 itemkey IN varchar2,
1606 actid IN number,
1607 funcmode IN varchar2,
1608 resultout IN OUT NOCOPY varchar2)
1609 IS
1610 l_trx_id number;
1611 l_status varchar2(1);
1612 l_msg_count number;
1613 l_msg_data varchar2(1000);
1614 BEGIN
1615 IF (funcmode = 'RUN') THEN
1616 l_trx_id := wf_engine.GetItemAttrNumber
1617 (itemtype => itemtype,
1618 itemkey => itemkey,
1619 aname => 'TRX_ID');
1620
1621 fun_trx_pvt.update_trx_status
1622 (p_api_version => 1.0,
1623 x_return_status => l_status,
1624 x_msg_count => l_msg_count,
1625 x_msg_data => l_msg_data,
1626 p_trx_id => l_trx_id,
1627 p_update_status_to => 'REJECTED');
1628 -- TODO: check return status
1629 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1630 RETURN;
1631 END IF;
1632
1633 resultout := wf_engine.eng_null;
1634 RETURN;
1635
1636 EXCEPTION
1637 WHEN others THEN
1638 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_REJECTED',
1639 itemtype, itemkey, TO_CHAR(actid), funcmode);
1640 RAISE;
1641 END update_status_rejected;
1642
1643
1644 /*-----------------------------------------------------
1645 * PROCEDURE update_status_approved
1646 * ----------------------------------------------------
1647 * Update status to approved.
1648 * ---------------------------------------------------*/
1649
1650 PROCEDURE update_status_approved (
1651 itemtype IN varchar2,
1652 itemkey IN varchar2,
1653 actid IN number,
1654 funcmode IN varchar2,
1655 resultout IN OUT NOCOPY varchar2)
1656 IS
1657 l_trx_id number;
1658 l_status varchar2(1);
1659 l_msg_count number;
1660 l_msg_data varchar2(1000);
1661 e_gen_acct_error EXCEPTION;
1662 BEGIN
1663 IF (funcmode = 'RUN') THEN
1664 l_trx_id := wf_engine.GetItemAttrNumber
1665 (itemtype => itemtype,
1666 itemkey => itemkey,
1667 aname => 'TRX_ID');
1668
1669 fun_trx_pvt.update_trx_status
1670 (p_api_version => 1.0,
1671 x_return_status => l_status,
1672 x_msg_count => l_msg_count,
1673 x_msg_data => l_msg_data,
1674 p_trx_id => l_trx_id,
1675 p_update_status_to => 'APPROVED');
1676
1677 IF l_status <> FND_API.G_RET_STS_SUCCESS
1678 THEN
1679 resultout := wf_engine.eng_completed||':F';
1680 RETURN;
1681 END IF;
1682
1683 resultout := wf_engine.eng_completed||':'||'T';
1684 RETURN;
1685 END IF;
1686
1687 resultout := wf_engine.eng_completed||':'||'T';
1688 RETURN;
1689
1690 EXCEPTION
1691 WHEN e_gen_acct_error THEN
1692 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
1693 itemtype, itemkey, TO_CHAR(actid), funcmode);
1694 RAISE;
1695
1696 WHEN others THEN
1697 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_APPROVED',
1698 itemtype, itemkey, TO_CHAR(actid), funcmode);
1699 RAISE;
1700
1701 END update_status_approved;
1702
1703 /*-----------------------------------------------------
1704 * PROCEDURE approve_ntf
1705 * ----------------------------------------------------
1706 * Approve notification process from UI.
1707 * ---------------------------------------------------*/
1708
1709 procedure approve_ntf (
1710 p_batch_id in varchar2,
1711 p_trx_id in varchar2,
1712 p_eventkey in varchar2)
1713 IS
1714 l_status varchar2(1);
1715 l_msg_count number;
1716 l_msg_data varchar2(1000);
1717 l_result varchar2(1);
1718 l_approver_record ame_util.approverRecord2;
1719 l_activity_name varchar2(30);
1720
1721 BEGIN
1722 --Bug No:5897122.
1723 --Bug No: 6865713. Replaced l_trx_id with p_trx_id.
1724 create_wf_roles(p_trx_id);
1725 --End: 5897122.
1726 wf_engine.SetItemAttrText
1727 (itemtype => 'FUNRMAIN',
1728 itemkey => p_eventkey,
1729 aname => 'UI_ACTION_TYPE',
1730 avalue => 'APPROVE');
1731
1732 wf_engine.SetItemAttrText
1733 (itemtype => 'FUNRMAIN',
1734 itemkey => p_eventkey,
1735 aname => 'UI_ACTION_USER_NAME',
1736 avalue => FND_GLOBAL.USER_NAME);
1737
1738 wf_engine.SetItemAttrNumber
1739 (itemtype => 'FUNRMAIN',
1740 itemkey => p_eventkey,
1741 aname => 'UI_ACTION_USER_ID',
1742 avalue => FND_GLOBAL.USER_ID);
1743
1744 --get process/activity name
1745 SELECT WPA.ACTIVITY_NAME
1746 INTO l_activity_name
1747 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
1748 where WIAS.ITEM_TYPE = 'FUNRMAIN'
1749 and WIAS.ITEM_KEY = p_eventkey
1750 and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
1751 and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
1752 and ((WPA.PROCESS_NAME = 'RECIPIENT_APPROVAL'
1753 AND WPA.ACTIVITY_NAME IN ('FIX_ACCT_DIST_NTF',
1754 'FIX_ACCT_DIST_NTF_NO_REJ')) OR
1755 (WPA.PROCESS_NAME = 'SEND_APPROVAL_NOTIFICATION'
1756 AND WPA.ACTIVITY_NAME IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF')));
1757
1758 IF l_activity_name IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF',
1759 'FIX_ACCT_DIST_NTF_NO_REJ', 'FIX_ACCT_DIST_NTF' )
1760 THEN
1761 l_approver_record.name := FND_GLOBAL.USER_NAME;
1762 l_approver_record.approval_status := AME_UTIL.approvedStatus;
1763
1764 ame_api2.updateApprovalStatus(
1765 applicationIdIn => 435,
1766 transactionTypeIn => 'FUN_IC_RECI_TRX',
1767 transactionIdIn => p_trx_id,
1768 approverIn => l_approver_record);
1769
1770 -- Update the approver id onto the fun_trx_headers table.
1771 -- approver_id will always hold the id of the last person
1772 -- to approve the transaction.
1773 UPDATE fun_trx_headers
1774 SET approver_id = FND_GLOBAL.USER_ID,
1775 approval_date = SYSDATE
1776 WHERE trx_id = p_trx_id;
1777
1778 END IF;
1779
1780 IF l_activity_name IN ( 'FIX_ACCT_DIST_NTF', 'FIX_ACCT_DIST_NTF_NO_REJ')
1781 THEN
1782 wf_engine.CompleteActivityInternalName(
1783 itemtype => 'FUNRMAIN',
1784 itemkey => p_eventkey,
1785 activity => 'RECIPIENT_APPROVAL:'||l_activity_name,
1786 result => 'RETRY');
1787
1788 ELSIF l_activity_name = 'APPROVAL_NTF' then
1789 wf_engine.CompleteActivityInternalName(
1790 itemtype => 'FUNRMAIN',
1791 itemkey => p_eventkey,
1792 activity => 'SEND_APPROVAL_NOTIFICATION:'||l_activity_name,
1793 result => 'APPROVED');
1794
1795 ELSIF l_activity_name = 'APPROVAL_ONLY_NTF' then
1796 wf_engine.CompleteActivityInternalName(
1797 itemtype => 'FUNRMAIN',
1798 itemkey => p_eventkey,
1799 activity => 'SEND_APPROVAL_NOTIFICATION:'||l_activity_name,
1800 result => 'APPROVE');
1801 END IF;
1802
1803 EXCEPTION
1804 WHEN OTHERS THEN
1805 UPDATE fun_trx_headers
1806 SET status = 'RECEIVED'
1807 WHERE trx_id = p_trx_id;
1808 RAISE;
1809 END approve_ntf;
1810
1811 /*-----------------------------------------------------
1812 * PROCEDURE reject_ntf
1813 * ----------------------------------------------------
1814 * Reject notification process from UI.
1815 * ---------------------------------------------------*/
1816
1817 procedure reject_ntf (
1818 p_batch_id in varchar2,
1819 p_trx_id in varchar2,
1820 p_eventkey in varchar2)
1821 IS
1822 l_status varchar2(1);
1823 l_msg_count number;
1824 l_msg_data varchar2(1000);
1825 l_result varchar2(1);
1826 l_activity_name varchar2(30);
1827 l_approver_record ame_util.approverRecord2;
1828 BEGIN
1829 --Bug No:5897122.
1830 create_wf_roles(p_trx_id);
1831 --End: 5897122.
1832 wf_engine.SetItemAttrText
1833 (itemtype => 'FUNRMAIN',
1834 itemkey => p_eventkey,
1835 aname => 'UI_ACTION_TYPE',
1836 avalue => 'REJECT');
1837
1838 wf_engine.SetItemAttrText
1839 (itemtype => 'FUNRMAIN',
1840 itemkey => p_eventkey,
1841 aname => 'UI_ACTION_USER_NAME',
1842 avalue => FND_GLOBAL.USER_NAME);
1843
1844 wf_engine.SetItemAttrNumber
1845 (itemtype => 'FUNRMAIN',
1846 itemkey => p_eventkey,
1847 aname => 'UI_ACTION_USER_ID',
1848 avalue => FND_GLOBAL.USER_ID);
1849
1850 --check reject allowed
1851 SELECT reject_allow_flag INTO l_result
1852 FROM fun_trx_batches
1853 WHERE batch_id = p_batch_id;
1854
1855 IF (l_result = 'Y')
1856 THEN
1857
1858 --get process/activity name
1859 SELECT WPA.ACTIVITY_NAME
1860 INTO l_activity_name
1861 from WF_ITEM_ACTIVITY_STATUSES WIAS, WF_PROCESS_ACTIVITIES WPA
1862 where WIAS.ITEM_TYPE = 'FUNRMAIN'
1863 and WIAS.ITEM_KEY = p_eventkey
1864 and WIAS.ACTIVITY_STATUS = wf_engine.eng_notified
1865 and WIAS.PROCESS_ACTIVITY = WPA.INSTANCE_ID
1866 and ((WPA.PROCESS_NAME = 'RECIPIENT_APPROVAL'
1867 AND WPA.ACTIVITY_NAME IN ('FIX_ACCT_DIST_NTF')) OR
1868 (WPA.PROCESS_NAME = 'SEND_APPROVAL_NOTIFICATION'
1869 AND WPA.ACTIVITY_NAME IN ('APPROVAL_NTF', 'APPROVAL_ONLY_NTF')));
1870
1871 IF l_activity_name IN ('APPROVAL_NTF', 'FIX_ACCT_DIST_NTF' )
1872 THEN
1873 fun_trx_pvt.update_trx_status
1874 (p_api_version => 1.0,
1875 x_return_status => l_status,
1876 x_msg_count => l_msg_count,
1877 x_msg_data => l_msg_data,
1878 p_trx_id => p_trx_id,
1879 p_update_status_to => 'REJECTED');
1880
1881 -- Update the AME System with the 'APPROVED' status.
1882 l_approver_record.name := FND_GLOBAL.USER_NAME;
1883 l_approver_record.approval_status := AME_UTIL.rejectStatus;
1884
1885 ame_api2.updateApprovalStatus(
1886 applicationIdIn => 435,
1887 transactionTypeIn => 'FUN_IC_RECI_TRX',
1888 transactionIdIn => p_trx_id,
1889 approverIn => l_approver_record);
1890
1891 END IF;
1892
1893 IF l_activity_name = 'FIX_ACCT_DIST_NTF' then
1894 wf_engine.CompleteActivityInternalName(
1895 itemtype => 'FUNRMAIN',
1896 itemkey => p_eventkey,
1897 activity => 'RECIPIENT_APPROVAL:'||l_activity_name,
1898 result => 'REJECT');
1899
1900 ELSIF l_activity_name = 'APPROVAL_NTF' then
1901
1902 wf_engine.CompleteActivityInternalName(
1903 itemtype => 'FUNRMAIN',
1904 itemkey => p_eventkey,
1905 activity => 'SEND_APPROVAL_NOTIFICATION:APPROVAL_NTF',
1906 result => 'REJECTED');
1907 END IF;
1908 else
1909 --raise not allow to reject error
1910 null;
1911 end if;
1912
1913 EXCEPTION
1914 WHEN OTHERS THEN
1915 UPDATE fun_trx_headers
1916 SET status = 'RECEIVED'
1917 WHERE trx_id = p_trx_id;
1918 RAISE;
1919 END reject_ntf;
1920
1921 /*-----------------------------------------------------
1922 * PROCEDURE recipient_interco_acct
1923 * ----------------------------------------------------
1924 * Insert a default intercompany account for recipient
1925 * accounting to fun_dist_lines
1926 * ---------------------------------------------------*/
1927
1928 procedure recipient_interco_acct (
1929 itemtype in varchar2,
1930 itemkey in varchar2,
1931 actid in number,
1932 funcmode in varchar2,
1933 resultout in OUT NOCOPY varchar2)
1934 IS
1935 l_trx_id number;
1936 l_batch_id number;
1937 l_status varchar2(1);
1938 l_msg_count number := 0;
1939 l_msg_data varchar2(1000);
1940 l_from_le_id number;
1941 l_to_le_id number;
1942 l_reci_amount_cr number;
1943 l_reci_amount_dr number;
1944 l_dist_id number;
1945 l_ccid number;
1946 l_dist_exist number;
1947 BEGIN
1948 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1949 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'begin');
1950 END IF;
1951
1952 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
1953 l_batch_id := wf_engine.GetItemAttrNumber
1954 (itemtype => itemtype,
1955 itemkey => itemkey,
1956 aname => 'BATCH_ID');
1957
1958 l_trx_id := wf_engine.GetItemAttrNumber
1959 (itemtype => itemtype,
1960 itemkey => itemkey,
1961 aname => 'TRX_ID');
1962
1963 -- Check if recipient distributions already exist
1964 SELECT COUNT(*)
1965 INTO l_dist_exist
1966 FROM fun_dist_lines d
1967 WHERE d.trx_id = l_trx_id
1968 AND d.dist_type_flag = 'L'
1969 AND d.party_type_flag = 'R';
1970
1971 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1972 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'checking distributions');
1973 END IF;
1974
1975 IF l_dist_exist > 0
1976 THEN
1977 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1978 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'recipient dist exist');
1979 END IF;
1980
1981 resultout := wf_engine.eng_null;
1982 RETURN;
1983 END IF;
1984
1985
1986 SELECT b.from_le_id, h.to_le_id, h.reci_amount_cr, h.reci_amount_dr
1987 INTO l_from_le_id, l_to_le_id, l_reci_amount_cr, l_reci_amount_dr
1988 FROM FUN_TRX_BATCHES b, FUN_TRX_HEADERS h
1989 WHERE b.batch_id = l_batch_id
1990 AND b.batch_id = h.batch_id
1991 AND h.trx_id = l_trx_id;
1992
1993 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1994 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'fetched details from headers');
1995 END IF;
1996
1997
1998 -- Get Default ccid from SLA
1999 fun_recipient_wf.get_default_sla_ccid (
2000 p_trx_id => l_trx_id,
2001 x_ccid => l_ccid,
2002 x_status => l_status,
2003 x_msg_count => l_msg_count,
2004 x_msg_data => l_msg_data);
2005
2006 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2007 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'returned from call to get_default_sla_ccid');
2008 END IF;
2009
2010 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2011 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'l_ccid is '|| l_ccid);
2012 END IF;
2013
2014 IF l_ccid > 0
2015 THEN
2016
2017
2018 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2019 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Inserting distribution rows');
2020 END IF;
2021 -- Changes made for Bug # 6843857 to picks up recipient party id
2022 FOR crec in (SELECT
2023 dl.LINE_ID,
2024 --dl.PARTY_ID,
2025 h.RECIPIENT_ID,
2026 dl.amount_dr,
2027 dl.amount_cr
2028 --FUN_TRX_ENTRY_UTIL.GET_DEFAULT_CCID(l_to_le_id, l_from_le_id, 'P') CCID
2029 FROM fun_dist_lines dl, fun_trx_lines l, fun_trx_headers h
2030 WHERE l.trx_id = l_trx_id
2031 AND dl.trx_id = h.trx_id
2032 AND dl.line_id = l.line_id
2033 AND dl.party_type_flag = 'I'
2034 AND dl.dist_type_flag = 'L')
2035 LOOP
2036
2037
2038
2039 SELECT FUN_DIST_LINES_S.nextval INTO l_dist_id FROM dual;
2040
2041 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2042 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Inserting distribution ' || l_dist_id);
2043 END IF;
2044
2045 INSERT into FUN_DIST_LINES(
2046 DIST_ID,
2047 LINE_ID,
2048 DIST_NUMBER,
2049 PARTY_ID,
2050 PARTY_TYPE_FLAG,
2051 DIST_TYPE_FLAG,
2052 AMOUNT_CR,
2053 AMOUNT_DR,
2054 CCID,
2055 AUTO_GENERATE_FLAG,
2056 CREATED_BY,
2057 CREATION_DATE,
2058 LAST_UPDATED_BY,
2059 LAST_UPDATE_DATE,
2060 LAST_UPDATE_LOGIN,
2061 trx_id)
2062 VALUES(
2063 l_dist_id,
2064 crec.line_id,
2065 l_dist_id,
2066 --crec.party_id,
2067 crec.recipient_id,
2068 'R',
2069 'L',
2070 crec.amount_dr,
2071 crec.amount_cr,
2072 l_ccid,
2073 'N',
2074 FND_GLOBAL.USER_ID,
2075 sysdate,
2076 FND_GLOBAL.USER_ID,
2077 sysdate,
2078 FND_GLOBAL.LOGIN_ID,
2079 l_trx_id);
2080
2081 END LOOP;
2082
2083 END IF;
2084
2085 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2086 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Done inserting');
2087 END IF;
2088
2089 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
2090 RETURN;
2091 END IF;
2092
2093 resultout := wf_engine.eng_null;
2094 RETURN;
2095
2096 EXCEPTION
2097 WHEN others THEN
2098 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2099 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', 'Unexpected Error');
2100 END IF;
2101
2102 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2103 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.recipient_interco_acct', SQLERRM);
2104 END IF;
2105
2106 wf_core.context('FUN_RECIPIENT_WF', 'RECIPIENT_INTERCO_ACCT',
2107 itemtype, itemkey, TO_CHAR(actid), funcmode);
2108 RAISE;
2109 END recipient_interco_acct;
2110
2111
2112 /*-----------------------------------------------------
2113 * PROCEDURE check_acct_dist
2114 * ----------------------------------------------------
2115 * Call the Transaction API to validate account distributions
2116 * ---------------------------------------------------*/
2117
2118 PROCEDURE check_acct_dist (
2119 itemtype IN varchar2,
2120 itemkey IN varchar2,
2121 actid IN number,
2122 funcmode IN varchar2,
2123 resultout IN OUT NOCOPY varchar2)
2124 IS
2125 l_trx_id number;
2126 l_batch_id number;
2127 l_status varchar2(1);
2128 l_msg_count number := 0;
2129 l_msg_data varchar2(1000);
2130 l_batch_rec fun_trx_pvt.batch_rec_type;
2131 l_trx_rec fun_trx_pvt.trx_rec_type;
2132 l_dist_line_tbl fun_trx_pvt.dist_line_tbl_type;
2133 l_result boolean := true;
2134 l_trx_status varchar2(100);
2135
2136 BEGIN
2137 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
2138 l_trx_id := wf_engine.GetItemAttrNumber
2139 (itemtype => itemtype,
2140 itemkey => itemkey,
2141 aname => 'TRX_ID');
2142
2143 l_batch_id := wf_engine.GetItemAttrNumber
2144 (itemtype => itemtype,
2145 itemkey => itemkey,
2146 aname => 'BATCH_ID');
2147
2148 select status into l_trx_status
2149 from fun_trx_headers
2150 where trx_id = l_trx_id;
2151
2152 -- Beware of NO_DATA_FOUND.
2153 l_batch_rec := make_batch_rec(l_batch_id);
2154 l_trx_rec := make_trx_rec(l_trx_id);
2155 l_dist_line_tbl := make_dist_lines_tbl(l_trx_id);
2156
2157 fun_trx_pvt.recipient_validate(
2158 1.0, 'T', 50,
2159 l_status, l_msg_count, l_msg_data,
2160 l_batch_rec, l_trx_rec, l_dist_line_tbl);
2161
2162 IF (l_status = 'S') THEN
2163
2164 resultout := wf_engine.eng_completed||':T';
2165 RETURN;
2166 ELSE
2167 wf_engine.SetItemAttrText(itemtype, itemkey, 'ERROR_MESSAGE',
2168 fun_wf_common.concat_msg_stack(fnd_msg_pub.count_msg));
2169 resultout := wf_engine.eng_completed||':F';
2170 RETURN;
2171 END IF;
2172
2173 END IF;
2174
2175 resultout := wf_engine.eng_null;
2176 RETURN;
2177
2178 EXCEPTION
2179 WHEN others THEN
2180 UPDATE fun_trx_headers
2181 SET status = 'RECEIVED'
2182 WHERE trx_id = l_trx_id;
2183
2184 wf_core.context('FUN_RECIPIENT_WF', 'CHECK_ACCT_DIST',
2185 itemtype, itemkey, TO_CHAR(actid), funcmode);
2186 RAISE;
2187 END check_acct_dist;
2188
2189 /* ---------------------------------------------------------------------------
2190 Name : check_ui_apprvl_action
2191 Pre-reqs : None.
2192 Modifies : None.
2193 Function : This function is called by the Recipient Main workflow
2194 to check if an approval action was taken in the wflow
2195 even before an approval notification was sent.
2196 Parameters:
2197 IN : itemtype - Workflow Item Type
2198 itemkey - Workflow Item Key
2199 actid - Workflow Activity Id
2200 funcmode - Workflow Function Mode
2201 OUT : resultout - Result of the workflow function
2202 Notes : None.
2203 Testing : This function will be tested via workflow FUNRMAIN
2204 ------------------------------------------------------------------------------*/
2205
2206 PROCEDURE check_ui_apprvl_action (itemtype IN VARCHAR2,
2207 itemkey IN VARCHAR2,
2208 actid IN NUMBER,
2209 funcmode IN VARCHAR2,
2210 resultout OUT NOCOPY VARCHAR2 ) IS
2211
2212 l_action_type VARCHAR2(20);
2213 l_approver_record ame_util.approverRecord2;
2214 l_trx_id NUMBER;
2215 l_user_id fnd_user.user_id%TYPE;
2216
2217 l_status varchar2(1);
2218 l_msg_count number;
2219 l_msg_data varchar2(1000);
2220
2221 BEGIN
2222 l_action_type := wf_engine.GetItemAttrText (itemtype => itemtype,
2223 itemkey => itemkey,
2224 aname => 'UI_ACTION_TYPE');
2225
2226 l_approver_record.name := wf_engine.GetItemAttrText
2227 (itemtype => itemtype,
2228 itemkey => itemkey,
2229 aname => 'UI_ACTION_USER_NAME');
2230
2231 l_user_id := wf_engine.GetItemAttrNumber
2232 (itemtype => itemtype,
2233 itemkey => itemkey,
2234 aname => 'UI_ACTION_USER_ID');
2235
2236 l_trx_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
2237 itemkey => itemkey,
2238 aname => 'TRX_ID');
2239
2240 IF l_action_type = 'APPROVE'
2241 THEN
2242
2243 resultout := wf_engine.eng_completed||':'||'APPROVE';
2244
2245 ELSIF l_action_type = 'REJECT'
2246 THEN
2247 resultout := wf_engine.eng_completed||':'||'REJECT';
2248
2249 ELSIF Nvl(l_action_type,'NONE') = 'NONE'
2250 THEN
2251 resultout := wf_engine.eng_completed||':'||'NONE';
2252
2253 END IF;
2254
2255 END check_ui_apprvl_action;
2256
2257 /*-----------------------------------------------------
2258 * PROCEDURE generate_interco_acct
2259 * ----------------------------------------------------
2260 * Generats intercompany account distribution lines for both
2261 * the recipient and initiator
2262 * ---------------------------------------------------*/
2263
2264 PROCEDURE generate_interco_acct (
2265 p_trx_id IN NUMBER,
2266 x_status IN OUT NOCOPY VARCHAR2,
2267 x_msg_count IN OUT NOCOPY NUMBER,
2268 x_msg_data IN OUT NOCOPY VARCHAR2)
2269 IS
2270
2271 CURSOR c_dists (p_trx_id NUMBER,
2272 p_party_type VARCHAR2) IS
2273 SELECT
2274 dist.dist_id ,
2275 dist.dist_number ,
2276 dist.trx_id ,
2277 dist.line_id ,
2278 head.initiator_id ,
2279 head.recipient_id ,
2280 btch.from_le_id initiator_le_id ,
2281 head.to_le_id recipient_le_id ,
2282 btch.gl_date ,
2283 DECODE(p_party_type, 'I', btch.from_ledger_id,
2284 head.to_ledger_id) ledger_id ,
2285 dist.amount_cr ,
2286 dist.amount_dr ,
2287 dist.ccid ,
2288 fun_util.get_account_segment_value (DECODE(p_party_type, 'I',
2289 btch.from_ledger_id,
2290 head.to_ledger_id),
2291 dist.ccid,
2292 'GL_BALANCING') dist_bsv ,
2293 dist.description,
2294 head.init_amount_cr,
2295 head.init_amount_dr,
2296 head.reci_amount_cr,
2297 head.reci_amount_dr
2298 FROM fun_dist_lines dist,
2299 fun_trx_headers head,
2300 fun_trx_batches btch
2301 WHERE dist.trx_id = head.trx_id
2302 AND head.batch_id = btch.batch_id
2303 AND dist.party_type_flag = p_party_type
2304 AND head.trx_id = p_trx_id
2305 AND dist.dist_type_flag = 'L';
2306
2307 l_trx_id NUMBER;
2308 l_batch_id NUMBER;
2309 l_status VARCHAR2(1);
2310 l_msg_count NUMBER := 0;
2311 l_msg_data VARCHAR2(1000);
2312 l_ccid NUMBER;
2313 l_reciprocal_ccid NUMBER;
2314
2315 TYPE dist_line_rec_type IS RECORD (
2316 dist_id NUMBER (15),
2317 dist_number NUMBER (15),
2318 trx_id NUMBER (15),
2319 line_id NUMBER (15),
2320 initiator_id NUMBER,
2321 recipient_id NUMBER,
2322 initiator_le_id NUMBER,
2323 recipient_le_id NUMBER,
2324 gl_date DATE,
2325 ledger_id NUMBER,
2326 amount_cr NUMBER,
2327 amount_dr NUMBER,
2328 dist_ccid NUMBER,
2329 dist_bsv VARCHAR2(30),
2330 description fun_dist_lines.description%TYPE,
2331 init_amount_cr NUMBER,
2332 init_amount_dr NUMBER,
2333 reci_amount_cr NUMBER,
2334 reci_amount_dr NUMBER);
2335
2336 TYPE new_dist_rec_type IS RECORD (
2337 dist_id NUMBER (15),
2338 line_id NUMBER (15),
2339 dist_number NUMBER (15),
2340 party_id NUMBER,
2341 party_type_flag VARCHAR2 (1),
2342 dist_type_flag VARCHAR2 (1),
2343 batch_dist_id NUMBER,
2344 amount_cr NUMBER,
2345 amount_dr NUMBER,
2346 ccid NUMBER (15),
2347 description fun_dist_lines.description%TYPE,
2348 auto_generate_flag VARCHAR2(1),
2349 attribute1 VARCHAR2(150),
2350 attribute2 VARCHAR2(150),
2351 attribute3 VARCHAR2(150),
2352 attribute4 VARCHAR2(150),
2353 attribute5 VARCHAR2(150),
2354 attribute6 VARCHAR2(150),
2355 attribute7 VARCHAR2(150),
2356 attribute8 VARCHAR2(150),
2357 attribute9 VARCHAR2(150),
2358 attribute10 VARCHAR2(150),
2359 attribute11 VARCHAR2(150),
2360 attribute12 VARCHAR2(150),
2361 attribute13 VARCHAR2(150),
2362 attribute14 VARCHAR2(150),
2363 attribute15 VARCHAR2(150),
2364 attribute_category VARCHAR2(150),
2365 created_by NUMBER,
2366 creation_date DATE,
2367 last_updated_by NUMBER,
2368 last_update_date DATE,
2369 last_update_login NUMBER,
2370 trx_id NUMBER (15));
2371
2372 CURSOR c_dtls IS
2373 SELECT b.batch_id,
2374 b.initiator_id initiator_id
2375 ,b.from_le_id from_le_id
2376 ,b.from_ledger_id from_ledger_id
2377 ,b.currency_code currency_code
2378 ,b.attribute1 bat_attribute1
2379 ,b.attribute2 bat_attribute2
2380 ,b.attribute3 bat_attribute3
2381 ,b.attribute4 bat_attribute4
2382 ,b.attribute5 bat_attribute5
2383 ,b.attribute6 bat_attribute6
2384 ,b.attribute7 bat_attribute7
2385 ,b.attribute8 bat_attribute8
2386 ,b.attribute9 bat_attribute9
2387 ,b.attribute10 bat_attribute10
2388 ,b.attribute11 bat_attribute11
2389 ,b.attribute12 bat_attribute12
2390 ,b.attribute13 bat_attribute13
2391 ,b.attribute14 bat_attribute14
2392 ,b.attribute15 bat_attribute15
2393 ,b.attribute_category bat_attribute_category
2394 ,t.trx_id trx_id
2395 ,t.recipient_id recipient_id
2396 ,t.to_le_id to_le_id
2397 ,t.to_ledger_id to_ledger_id
2398 ,t.attribute1 trx_attribute1
2399 ,t.attribute2 trx_attribute2
2400 ,t.attribute3 trx_attribute3
2401 ,t.attribute4 trx_attribute4
2402 ,t.attribute5 trx_attribute5
2403 ,t.attribute6 trx_attribute6
2404 ,t.attribute7 trx_attribute7
2405 ,t.attribute8 trx_attribute8
2406 ,t.attribute9 trx_attribute9
2407 ,t.attribute10 trx_attribute10
2408 ,t.attribute11 trx_attribute11
2409 ,t.attribute12 trx_attribute12
2410 ,t.attribute13 trx_attribute13
2411 ,t.attribute14 trx_attribute14
2412 ,t.attribute15 trx_attribute15
2413 ,t.attribute_category trx_attribute_category
2414 ,y.trx_type_id trx_type_id
2415 ,y.manual_approve_flag manual_approve_flag
2416 ,y.allow_invoicing_flag allow_invoicing_flag
2417 ,y.vat_taxable_flag vat_taxable_flag
2418 ,y.allow_interest_accrual_flag allow_interest_accrual_flag
2419 ,y.attribute1 typ_attribute1
2420 ,y.attribute2 typ_attribute2
2421 ,y.attribute3 typ_attribute3
2422 ,y.attribute4 typ_attribute4
2423 ,y.attribute5 typ_attribute5
2424 ,y.attribute6 typ_attribute6
2425 ,y.attribute7 typ_attribute7
2426 ,y.attribute8 typ_attribute8
2427 ,y.attribute9 typ_attribute9
2428 ,y.attribute10 typ_attribute10
2429 ,y.attribute11 typ_attribute11
2430 ,y.attribute12 typ_attribute12
2431 ,y.attribute13 typ_attribute13
2432 ,y.attribute14 typ_attribute14
2433 ,y.attribute15 typ_attribute15
2434 ,y.attribute_category typ_attribute_category
2435 ,l.chart_of_accounts_id coa_id
2436 ,b.batch_date
2437 ,b.gl_date
2438 FROM fun_trx_batches b,
2439 fun_trx_headers t,
2440 fun_trx_types_vl y,
2441 gl_ledgers l
2442 WHERE b.batch_id = t.batch_id
2443 AND b.trx_type_id = y.trx_type_id
2444 AND t.to_ledger_id = l.ledger_id
2445 AND t.trx_id = p_trx_id;
2446
2447 l_trx_dtl_rec c_dtls%ROWTYPE;
2448
2449 CURSOR c_chk_sla (p_ledger_id IN NUMBER) IS
2450 SELECT amb_context_code,
2451 account_definition_code
2452 FROM fun_trx_acct_definitions
2453 WHERE ledger_id = p_ledger_id;
2454
2455 CURSOR c_get_bsv(p_ledger_id NUMBER,
2456 p_le_id NUMBER,
2457 p_gl_date DATE) IS
2458 SELECT vals.segment_value
2459 FROM gl_ledger_le_bsv_specific_v vals
2460 WHERE vals.legal_entity_id = p_le_id
2461 AND vals.ledger_id = p_ledger_id
2462 AND p_gl_date BETWEEN Nvl(vals.start_date, p_gl_date) AND Nvl(vals.end_date, p_gl_date)
2463 AND (SELECT COUNT(*)
2464 FROM gl_ledger_le_bsv_specific_v vals1
2465 WHERE vals1.legal_entity_id = p_le_id
2466 AND vals1.ledger_id = p_ledger_id
2467 AND p_gl_date BETWEEN Nvl(vals1.start_date, p_gl_date) AND Nvl(vals1.end_date, p_gl_date)) = 1;
2468
2469 l_amb_context_code fun_trx_acct_definitions.amb_context_code%TYPE;
2470 l_account_definition_code fun_trx_acct_definitions.account_definition_code%TYPE;
2471 l_initiator_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
2472 l_recipient_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
2473 -- Added for bug # 7520196
2474 l_init_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
2475 l_reci_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
2476
2477
2478 TYPE dist_line_tbl_type IS TABLE OF dist_line_rec_type
2479 INDEX BY BINARY_INTEGER;
2480
2481 TYPE new_dist_tbl_type IS TABLE OF new_dist_rec_type
2482 INDEX BY BINARY_INTEGER;
2483
2484 l_init_dist_tbl dist_line_tbl_type;
2485 l_reci_dist_tbl dist_line_tbl_type;
2486 l_new_dist_tbl new_dist_tbl_type;
2487 l_new_index NUMBER;
2488 l_init_count NUMBER;
2489 l_reci_count NUMBER;
2490 l_to_bsv VARCHAR2(30);
2491
2492 l_to_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
2493 l_from_coa_id gl_ledgers.chart_of_accounts_id%TYPE;
2494
2495 BEGIN
2496
2497 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2498 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'begin. p_trx_id = ' || p_trx_id);
2499 END IF;
2500
2501 x_status := FND_API.G_RET_STS_SUCCESS;
2502
2503 l_trx_id := p_trx_id;
2504 --Collect Iniator's info
2505
2506 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2507 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'open cursor c_dists for l_trx_id = ' || l_trx_id || 'and p_party_type = I' );
2508 END IF;
2509
2510 OPEN c_dists (p_trx_id => l_trx_id,
2511 p_party_type => 'I');
2512 FETCH c_dists BULK COLLECT INTO l_init_dist_tbl;
2513 CLOSE c_dists;
2514
2515 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2516 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'open cursor c_dists for l_trx_id = ' || l_trx_id || 'and p_party_type = R' );
2517 END IF;
2518
2519 -- Collect Recipients Info
2520 OPEN c_dists (p_trx_id => l_trx_id,
2521 p_party_type => 'R');
2522 FETCH c_dists BULK COLLECT INTO l_reci_dist_tbl;
2523 CLOSE c_dists;
2524
2525 l_new_index := 1;
2526 l_init_count := l_init_dist_tbl.COUNT;
2527 l_reci_count := l_reci_dist_tbl.COUNT;
2528 l_to_bsv := NULL;
2529
2530 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2531 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_init_count = ' || l_init_count || 'and l_reci_count = ' || l_reci_count );
2532 END IF;
2533
2534 IF l_init_count = 0 OR l_reci_count = 0
2535 THEN
2536 x_status := FND_API.G_RET_STS_ERROR;
2537 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2538 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'set x_status to ' || x_status );
2539 END IF;
2540 END IF;
2541
2542 -- Added for bug # 7520196
2543
2544 IF l_init_dist_tbl(1).initiator_le_id <> l_init_dist_tbl(1).recipient_le_id
2545 THEN
2546 IF l_init_dist_tbl(1).AMOUNT_DR IS NULL
2547 THEN
2548 SELECT MIN(DIST_BSV)
2549 INTO l_init_bsv
2550 FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2551 HEAD.TO_LEDGER_ID),
2552 DIST.CCID,'GL_BALANCING') DIST_BSV,
2553 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2554 FROM FUN_DIST_LINES DIST,
2555 FUN_TRX_HEADERS HEAD,
2556 FUN_TRX_BATCHES BTCH
2557 WHERE DIST.TRX_ID = HEAD.TRX_ID
2558 AND HEAD.BATCH_ID = BTCH.BATCH_ID
2559 AND HEAD.TRX_ID = p_trx_id
2560 GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2561 HEAD.TO_LEDGER_ID),
2562 DIST.CCID,'GL_BALANCING'),
2563 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2564 HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
2565 AND SUM(NVL(DIST.AMOUNT_CR,0.00)) <> 0.00);
2566
2567 SELECT MIN(DIST_BSV)
2568 INTO l_reci_bsv
2569 FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2570 HEAD.TO_LEDGER_ID),
2571 DIST.CCID,'GL_BALANCING') DIST_BSV,
2572 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2573 FROM FUN_DIST_LINES DIST,
2574 FUN_TRX_HEADERS HEAD,
2575 FUN_TRX_BATCHES BTCH
2576 WHERE DIST.TRX_ID = HEAD.TRX_ID
2577 AND HEAD.BATCH_ID = BTCH.BATCH_ID
2578 AND HEAD.TRX_ID = p_trx_id
2579 GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2580 HEAD.TO_LEDGER_ID),
2581 DIST.CCID,'GL_BALANCING'),
2582 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2583 HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
2584 AND SUM(NVL(DIST.AMOUNT_DR,0.00)) <> 0.00);
2585 ELSE
2586 SELECT MIN(DIST_BSV)
2587 INTO l_reci_bsv
2588 FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2589 HEAD.TO_LEDGER_ID),
2590 DIST.CCID,'GL_BALANCING') DIST_BSV,
2591 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2592 FROM FUN_DIST_LINES DIST,
2593 FUN_TRX_HEADERS HEAD,
2594 FUN_TRX_BATCHES BTCH
2595 WHERE DIST.TRX_ID = HEAD.TRX_ID
2596 AND HEAD.BATCH_ID = BTCH.BATCH_ID
2597 AND HEAD.TRX_ID = p_trx_id
2598 GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2599 HEAD.TO_LEDGER_ID),
2600 DIST.CCID,'GL_BALANCING'),
2601 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2602 HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
2603 AND SUM(NVL(DIST.AMOUNT_CR,0.00)) <> 0.00);
2604
2605 SELECT MIN(DIST_BSV)
2606 INTO l_init_bsv
2607 FROM (SELECT FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2608 HEAD.TO_LEDGER_ID),
2609 DIST.CCID,'GL_BALANCING') DIST_BSV,
2610 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2611 FROM FUN_DIST_LINES DIST,
2612 FUN_TRX_HEADERS HEAD,
2613 FUN_TRX_BATCHES BTCH
2614 WHERE DIST.TRX_ID = HEAD.TRX_ID
2615 AND HEAD.BATCH_ID = BTCH.BATCH_ID
2616 AND HEAD.TRX_ID = p_trx_id
2617 GROUP BY FUN_UTIL.GET_ACCOUNT_SEGMENT_VALUE(DECODE(DIST.PARTY_TYPE_FLAG,'I',BTCH.FROM_LEDGER_ID,
2618 HEAD.TO_LEDGER_ID),
2619 DIST.CCID,'GL_BALANCING'),
2620 DECODE(DIST.PARTY_TYPE_FLAG,'I', BTCH.FROM_LE_ID, HEAD.TO_LE_ID)
2621 HAVING SUM(NVL(DIST.AMOUNT_DR,0.00)) <> SUM(NVL(DIST.AMOUNT_CR,0.00))
2622 AND SUM(NVL(DIST.AMOUNT_DR,0.00)) <> 0.00);
2623 END IF;
2624
2625 IF (l_init_bsv IS NULL AND
2626 l_reci_bsv IS NULL)
2627 THEN
2628 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2629 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'All the lines are balanced.');
2630 END IF;
2631 x_status := FND_API.G_RET_STS_SUCCESS;
2632 RETURN;
2633 END IF;
2634 END IF;
2635
2636 l_ccid := -1;
2637
2638
2639 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2640 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Open Cursor c_dtls for initiator');
2641 END IF;
2642
2643 OPEN c_dtls;
2644 FETCH c_dtls INTO l_trx_dtl_rec;
2645 CLOSE c_dtls;
2646 -- Fetching the coa id of the initiator
2647 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2648 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Fetch the initiator COA ID');
2649 END IF;
2650
2651 SELECT l.chart_of_accounts_id
2652 INTO l_from_coa_id
2653 from gl_ledgers l
2654 WHERE l.ledger_id = l_trx_dtl_rec.from_ledger_id;
2655
2656 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_from_coa_id = ' || l_from_coa_id);
2658 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'open cursor c_chk_sla with ledger_id = ' || l_trx_dtl_rec.from_ledger_id);
2659 END IF;
2660
2661
2662 OPEN c_chk_sla (l_trx_dtl_rec.from_ledger_id);
2663 FETCH c_chk_sla INTO l_amb_context_code,
2664 l_account_definition_code;
2665 CLOSE c_chk_sla;
2666
2667 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2668 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_amb_context_code = ' || l_amb_context_code || ' l_account_definition_code = ' || l_account_definition_code);
2669 END IF;
2670
2671 -- If SLA TAB is set up then pull the value from there
2672
2673
2674
2675 IF l_amb_context_code IS NOT NULL AND l_account_definition_code IS NOT NULL
2676 THEN
2677
2678
2679 -- trying to get it from TAB set up
2680 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2681 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'open cursor c_get_bsv to fetch the bsv');
2682 END IF;
2683
2684 -- Derive values to be passed for recipient and initiator BSV
2685 -- Pass value only if 1 bsv is assigned to the LE.
2686 OPEN c_get_bsv(l_trx_dtl_rec.from_ledger_id,
2687 l_trx_dtl_rec.from_le_id,
2688 l_trx_dtl_rec.gl_date);
2689 FETCH c_get_bsv INTO l_initiator_bsv;
2690 CLOSE c_get_bsv;
2691
2692 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2693 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fetched initiator bsv = ' || l_initiator_bsv);
2694 END IF;
2695
2696
2697 OPEN c_get_bsv(l_trx_dtl_rec.to_ledger_id,
2698 l_trx_dtl_rec.to_le_id,
2699 l_trx_dtl_rec.gl_date);
2700 FETCH c_get_bsv INTO l_recipient_bsv;
2701 CLOSE c_get_bsv;
2702
2703 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2704 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fetched recipient bsv = ' || l_recipient_bsv);
2705 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'populating the PLSQL table fun_xla_tab_pkg.g_array_xla_tab(1)' );
2706 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_1 = ' || l_trx_dtl_rec.batch_id);
2707 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_2 = ' || l_trx_dtl_rec.trx_id);
2708 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_3 = ' || NULL);
2709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_4 = ' || NULL);
2710 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_5 = ' || NULL);
2711 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'account_type_code = ' || 'AGIS_INITIATOR_CLEAR_ACCOUNT');
2712 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute1 = ' || l_trx_dtl_rec.bat_attribute1);
2713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute10 = ' || l_trx_dtl_rec.bat_attribute10);
2714 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute11 = ' || l_trx_dtl_rec.bat_attribute11);
2715 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute12 = ' || l_trx_dtl_rec.bat_attribute12);
2716 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute13 = ' || l_trx_dtl_rec.bat_attribute13);
2717 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute14 = ' || l_trx_dtl_rec.bat_attribute14);
2718 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute15 = ' || l_trx_dtl_rec.bat_attribute15);
2719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute2 = ' || l_trx_dtl_rec.bat_attribute2);
2720 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute3 = ' || l_trx_dtl_rec.bat_attribute3);
2721 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute4 = ' || l_trx_dtl_rec.bat_attribute4);
2722 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute5 = ' || l_trx_dtl_rec.bat_attribute5);
2723 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute6 = ' || l_trx_dtl_rec.bat_attribute6);
2724 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute7 = ' || l_trx_dtl_rec.bat_attribute7);
2725 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute8 = ' || l_trx_dtl_rec.bat_attribute8);
2726 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute9 = ' || l_trx_dtl_rec.bat_attribute9);
2727 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_category_code = ' || l_trx_dtl_rec.bat_attribute_category);
2728 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_currency_code = ' || l_trx_dtl_rec.currency_code);
2729 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_from_ledger_id = ' || l_trx_dtl_rec.from_ledger_id);
2730 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_from_le_id = ' || l_trx_dtl_rec.from_le_id);
2731 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_initiator_bsv = ' || l_initiator_bsv);
2732 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_initiator_id = ' || l_trx_dtl_rec.initiator_id);
2733 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute1 = ' || l_trx_dtl_rec.trx_attribute1);
2734 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute2 = ' || l_trx_dtl_rec.trx_attribute2);
2735 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute3 = ' || l_trx_dtl_rec.trx_attribute3);
2736 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute4 = ' || l_trx_dtl_rec.trx_attribute4);
2737 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute5 = ' || l_trx_dtl_rec.trx_attribute5);
2738 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute6 = ' || l_trx_dtl_rec.trx_attribute6);
2739 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute7 = ' || l_trx_dtl_rec.trx_attribute7);
2740 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute8 = ' || l_trx_dtl_rec.trx_attribute8);
2741 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute9 = ' || l_trx_dtl_rec.trx_attribute9);
2742 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute10 = ' || l_trx_dtl_rec.trx_attribute10);
2743 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute11 = ' || l_trx_dtl_rec.trx_attribute11);
2744 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute12 = ' || l_trx_dtl_rec.trx_attribute12);
2745 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute13 = ' || l_trx_dtl_rec.trx_attribute13);
2746 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute14 = ' || l_trx_dtl_rec.trx_attribute14);
2747 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute15 = ' || l_trx_dtl_rec.trx_attribute15);
2748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute_category = ' || l_trx_dtl_rec.trx_attribute_category);
2749 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_recipient_bsv = ' || l_recipient_bsv);
2750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_recipient_id = ' || l_trx_dtl_rec.recipient_id);
2751 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_to_ledger_id = ' || l_trx_dtl_rec.to_ledger_id);
2752 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_to_le_id = ' || l_trx_dtl_rec.to_le_id);
2753 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_allow_interest_accr_flag = ' || l_trx_dtl_rec.allow_interest_accrual_flag);
2754 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_allow_invoicing_flag = ' || l_trx_dtl_rec.allow_invoicing_flag);
2755 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute1 = ' || l_trx_dtl_rec.typ_attribute1);
2756 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute2 = ' || l_trx_dtl_rec.typ_attribute2);
2757 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute3 = ' || l_trx_dtl_rec.typ_attribute3);
2758 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute4 = ' || l_trx_dtl_rec.typ_attribute4);
2759 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute5 = ' || l_trx_dtl_rec.typ_attribute5);
2760 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute6 = ' || l_trx_dtl_rec.typ_attribute6);
2761 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute7 = ' || l_trx_dtl_rec.typ_attribute7);
2762 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute8 = ' || l_trx_dtl_rec.typ_attribute8);
2763 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute9 = ' || l_trx_dtl_rec.typ_attribute9);
2764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute10 = ' || l_trx_dtl_rec.typ_attribute10);
2765 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute11 = ' || l_trx_dtl_rec.typ_attribute11);
2766 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute12 = ' || l_trx_dtl_rec.typ_attribute12);
2767 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute13 = ' || l_trx_dtl_rec.typ_attribute13);
2768 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute14 = ' || l_trx_dtl_rec.typ_attribute14);
2769 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute15 = ' || l_trx_dtl_rec.typ_attribute15);
2770 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute_category = ' || l_trx_dtl_rec.typ_attribute_category);
2771 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_manual_approve_flag = ' || l_trx_dtl_rec.manual_approve_flag);
2772 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_trx_type_id = ' || l_trx_dtl_rec.trx_type_id);
2773 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_vat_taxable_flag = ' || l_trx_dtl_rec.vat_taxable_flag);
2774 END IF;
2775
2776 -- Populate PLSQL Table
2777 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_1 := l_trx_dtl_rec.batch_id;
2778 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_2 := l_trx_dtl_rec.trx_id;
2779 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_3 := NULL;
2780 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_4 := NULL;
2781 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_5 := NULL;
2782 fun_xla_tab_pkg.g_array_xla_tab(1).account_type_code := 'AGIS_INITIATOR_CLEAR_ACCOUNT';
2783 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute1 := l_trx_dtl_rec.bat_attribute1;
2784 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute10 := l_trx_dtl_rec.bat_attribute10;
2785 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute11 := l_trx_dtl_rec.bat_attribute11;
2786 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute12 := l_trx_dtl_rec.bat_attribute12;
2787 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute13 := l_trx_dtl_rec.bat_attribute13;
2788 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute14 := l_trx_dtl_rec.bat_attribute14;
2789 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute15 := l_trx_dtl_rec.bat_attribute15;
2790 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute2 := l_trx_dtl_rec.bat_attribute2;
2791 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute3 := l_trx_dtl_rec.bat_attribute3;
2792 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute4 := l_trx_dtl_rec.bat_attribute4;
2793 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute5 := l_trx_dtl_rec.bat_attribute5;
2794 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute6 := l_trx_dtl_rec.bat_attribute6;
2795 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute7 := l_trx_dtl_rec.bat_attribute7;
2796 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute8 := l_trx_dtl_rec.bat_attribute8;
2797 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute9 := l_trx_dtl_rec.bat_attribute9;
2798 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_category_code := l_trx_dtl_rec.bat_attribute_category;
2799 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_currency_code := l_trx_dtl_rec.currency_code;
2800 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_ledger_id := l_trx_dtl_rec.from_ledger_id;
2801 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_le_id := l_trx_dtl_rec.from_le_id;
2802 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_bsv := l_initiator_bsv;
2803 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_id := l_trx_dtl_rec.initiator_id;
2804 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute1 := l_trx_dtl_rec.trx_attribute1;
2805 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute10 := l_trx_dtl_rec.trx_attribute10;
2806 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute11 := l_trx_dtl_rec.trx_attribute11;
2807 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute12 := l_trx_dtl_rec.trx_attribute12;
2808 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute13 := l_trx_dtl_rec.trx_attribute13;
2809 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute14 := l_trx_dtl_rec.trx_attribute14;
2810 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute15 := l_trx_dtl_rec.trx_attribute15;
2811 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute2 := l_trx_dtl_rec.trx_attribute2;
2812 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute3 := l_trx_dtl_rec.trx_attribute3;
2813 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute4 := l_trx_dtl_rec.trx_attribute4;
2814 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute5 := l_trx_dtl_rec.trx_attribute5;
2815 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute6 := l_trx_dtl_rec.trx_attribute6;
2816 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute7 := l_trx_dtl_rec.trx_attribute7;
2817 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute8 := l_trx_dtl_rec.trx_attribute8;
2818 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute9 := l_trx_dtl_rec.trx_attribute9;
2819 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute_category := l_trx_dtl_rec.trx_attribute_category;
2820 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_bsv := l_recipient_bsv;
2821 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_id := l_trx_dtl_rec.recipient_id;
2822 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_ledger_id := l_trx_dtl_rec.to_ledger_id;
2823 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_le_id := l_trx_dtl_rec.to_le_id;
2824 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_interest_accr_flag := l_trx_dtl_rec.allow_interest_accrual_flag;
2825 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_invoicing_flag := l_trx_dtl_rec.allow_invoicing_flag;
2826 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute1 := l_trx_dtl_rec.typ_attribute1;
2827 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute10 := l_trx_dtl_rec.typ_attribute10;
2828 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute11 := l_trx_dtl_rec.typ_attribute11;
2829 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute12 := l_trx_dtl_rec.typ_attribute12;
2830 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute13 := l_trx_dtl_rec.typ_attribute13;
2831 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute14 := l_trx_dtl_rec.typ_attribute14;
2832 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute15 := l_trx_dtl_rec.typ_attribute15;
2833 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute2 := l_trx_dtl_rec.typ_attribute2;
2834 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute3 := l_trx_dtl_rec.typ_attribute3;
2835 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute4 := l_trx_dtl_rec.typ_attribute4;
2836 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute5 := l_trx_dtl_rec.typ_attribute5;
2837 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute6 := l_trx_dtl_rec.typ_attribute6;
2838 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute7 := l_trx_dtl_rec.typ_attribute7;
2839 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute8 := l_trx_dtl_rec.typ_attribute8;
2840 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute9 := l_trx_dtl_rec.typ_attribute9;
2841 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute_category := l_trx_dtl_rec.typ_attribute_category;
2842 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_manual_approve_flag := l_trx_dtl_rec.manual_approve_flag;
2843 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_trx_type_id := l_trx_dtl_rec.trx_type_id;
2844 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_vat_taxable_flag := l_trx_dtl_rec.vat_taxable_flag;
2845
2846
2847 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2848 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Calling fun_xla_tab_pkg.run with following parameters');
2849 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_api_version = ' || '1.0');
2850 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_account_definition_type_code = ' || 'C');
2851 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_account_definition_code = ' || l_account_definition_code);
2852 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_transaction_coa_id = ' || l_from_coa_id);
2853 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_mode = ' || 'ONLINE');
2854 END IF;
2855
2856 fun_xla_tab_pkg.run(
2857 p_api_version => 1.0
2858 ,p_account_definition_type_code => 'C'
2859 ,p_account_definition_code => l_account_definition_code
2860 ,p_transaction_coa_id => l_from_coa_id
2861 ,p_mode => 'ONLINE'
2862 ,x_return_status => x_status
2863 ,x_msg_count => x_msg_count
2864 ,x_msg_data => x_msg_data );
2865
2866 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2867 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg returns status ' || x_status );
2868 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg x_msg_count = ' || x_msg_count );
2869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg x_msg_data = ' || x_msg_data );
2870 END IF;
2871
2872 IF x_status = FND_API.G_RET_STS_SUCCESS
2873 THEN
2874 l_ccid := fun_xla_tab_pkg.g_array_xla_tab(1).target_ccid;
2875 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2876 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_ccid =' || l_ccid);
2877 END IF;
2878
2879 END IF;
2880
2881 IF l_ccid <=0 OR x_status <> FND_API.G_RET_STS_SUCCESS -- Bug No : 7559411
2882 THEN
2883 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2884 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Error if l_ccid is not success or l_ccid is -1 and return');
2885 END IF;
2886 x_status := FND_API.G_RET_STS_ERROR;
2887 RETURN;
2888 END IF;
2889
2890 END IF;
2891
2892 IF l_amb_context_code IS NULL OR l_account_definition_code IS NULL OR l_ccid IS NULL THEN
2893
2894
2895
2896 -- SLA TAB is not set up or their is an error fetching CCID from TAB
2897 -- Trying to get the default ccid from Accouning Setups
2898 -- Generate 1 Liability account line (dist_type = 'P')
2899 -- Generate 1 Receivable account line (dist_type = 'R')
2900 -- irrespective of how many ever distributions (dist_type = 'L')
2901 -- are present.
2902
2903
2904
2905 -- Generate Initiator Distributions
2906 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2907 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'generate initiator distribution lines');
2908 END IF;
2909
2910 IF l_init_dist_tbl(1).initiator_le_id <> l_init_dist_tbl(1).recipient_le_id
2911
2912
2913
2914 THEN
2915
2916 -- p_to_ledger_id in this case is not really required as we
2917 -- are not interested in the reciprocal ccids.
2918 -- Hence passing it a dummy value
2919 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2920 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'calling fun_bal_utils_grp.get_intercompany_account with following parameters');
2921 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_api_version = ' ||'1.0');
2922 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_init_msg_list = ' || FND_API.G_TRUE);
2923 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_ledger_id = ' || l_init_dist_tbl(1).ledger_id);
2924 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_le = ' || l_init_dist_tbl(1).initiator_le_id);
2925 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_bsv = ' || l_init_bsv);
2926 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_ledger_id = ' || l_reci_dist_tbl(1).ledger_id);
2927 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_le = ' || l_init_dist_tbl(1).recipient_le_id);
2928 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_bsv = ' || l_reci_bsv);
2929 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_gl_date = ' || l_init_dist_tbl(1).gl_date);
2930 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_acct_type = ' || 'R');
2931 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_ccid = ' || l_ccid);
2932 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_reciprocal_ccid = ' || l_reciprocal_ccid);
2933 END IF;
2934
2935 fun_bal_utils_grp.get_intercompany_account
2936 (p_api_version => 1.0,
2937 p_init_msg_list => FND_API.G_TRUE,
2938 p_ledger_id => l_init_dist_tbl(1).ledger_id,
2939 p_from_le => l_init_dist_tbl(1).initiator_le_id,
2940 p_from_bsv => l_init_bsv,
2941 p_to_ledger_id => l_reci_dist_tbl(1).ledger_id,
2942 p_to_le => l_init_dist_tbl(1).recipient_le_id,
2943 p_to_bsv => l_reci_bsv,
2944 p_gl_date => l_init_dist_tbl(1).gl_date,
2945 p_acct_type => 'R',
2946 x_status => x_status,
2947 x_msg_count => x_msg_count,
2948 x_msg_data => x_msg_data,
2949 x_ccid => l_ccid,
2950 x_reciprocal_ccid => l_reciprocal_ccid);
2951 ELSE
2952 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2953 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'calling fun_bal_utils_grp.get_intracompany_account with following parameters');
2954 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_api_version = ' ||'1.0');
2955 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_init_msg_list = ' || FND_API.G_TRUE);
2956 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_ledger_id = ' || l_init_dist_tbl(1).ledger_id);
2957 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_le = ' || l_init_dist_tbl(1).initiator_le_id);
2958 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_source = ' || 'Global Intercompany');
2959 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_category = ' || 'Global Intercompany');
2960 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_dr_bsv = ' || l_init_bsv);
2961 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_cr_bsv = ' || l_reci_bsv);
2962 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_gl_date = ' || l_init_dist_tbl(1).gl_date);
2963 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_acct_type = ' || 'D');
2964 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_ccid = ' || l_ccid);
2965 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_reciprocal_ccid = ' || l_reciprocal_ccid);
2966 END IF;
2967
2968 fun_bal_utils_grp.get_intracompany_account
2969 (p_api_version => 1.0,
2970 p_init_msg_list => FND_API.G_TRUE,
2971 p_ledger_id => l_init_dist_tbl(1).ledger_id,
2972 p_from_le => l_init_dist_tbl(1).initiator_le_id,
2973 p_source => 'Global Intercompany',
2974 p_category => 'Global Intercompany',
2975 p_dr_bsv => l_init_dist_tbl(1).dist_bsv,
2976 p_cr_bsv => l_reci_dist_tbl(1).dist_bsv,
2977 p_gl_date => l_init_dist_tbl(1).gl_date,
2978 p_acct_type => 'D',
2979 x_status => x_status,
2980 x_msg_count => x_msg_count,
2981 x_msg_data => x_msg_data,
2982 x_ccid => l_ccid,
2983 x_reciprocal_ccid => l_reciprocal_ccid);
2984 END IF;
2985
2986
2987 IF l_ccid IS NULL OR l_ccid <=0 OR x_status <> FND_API.G_RET_STS_SUCCESS -- Bug No : 6969506
2988 THEN
2989 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2990 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Error if l_ccid is null or return status is not success and return');
2991 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_status = ' || x_status);
2992 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_msg_count = ' || x_msg_count);
2993 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_msg_data = ' || x_msg_data);
2994 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_ccid = ' || l_ccid);
2995 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_reciprocal_ccid = ' || l_reciprocal_ccid);
2996 END IF;
2997 x_status := FND_API.G_RET_STS_ERROR;
2998 RETURN;
2999 END IF;
3000
3001
3002 END IF;
3003
3004 IF (l_init_dist_tbl(1).init_amount_cr IS NULL or to_number(l_init_dist_tbl(1).init_amount_cr)=0) and (l_init_dist_tbl(1).init_amount_dr is null or to_number(l_init_dist_tbl(1).init_amount_dr)=0 ) -- Bug No : 6969506, 7013314
3005 THEN
3006 x_status := FND_API.G_RET_STS_ERROR;
3007 RETURN;
3008 END IF;
3009
3010 -- Now build the new distribution record
3011 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3012 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Build the initiator distribution lines');
3013 END IF;
3014 SELECT FUN_DIST_LINES_S.nextval
3015 INTO l_new_dist_tbl(l_new_index).dist_id
3016 FROM dual;
3017
3018 l_new_dist_tbl(l_new_index).line_id := l_init_dist_tbl(1).line_id;
3019 l_new_dist_tbl(l_new_index).trx_id := l_init_dist_tbl(1).trx_id;
3020 l_new_dist_tbl(l_new_index).dist_number := l_new_dist_tbl(l_new_index).dist_id;
3021 l_new_dist_tbl(l_new_index).party_id := l_init_dist_tbl(1).initiator_id;
3022 l_new_dist_tbl(l_new_index).party_type_flag := 'I';
3023 l_new_dist_tbl(l_new_index).dist_type_flag := 'R';
3024 l_new_dist_tbl(l_new_index).amount_cr := l_init_dist_tbl(1).init_amount_cr;
3025 l_new_dist_tbl(l_new_index).amount_dr := l_init_dist_tbl(1).init_amount_dr;
3026 l_new_dist_tbl(l_new_index).ccid := l_ccid;
3027 l_new_dist_tbl(l_new_index).description := l_init_dist_tbl(1).description;
3028 l_new_dist_tbl(l_new_index).auto_generate_flag := 'Y';
3029 l_new_dist_tbl(l_new_index).created_by := FND_GLOBAL.USER_ID;
3030 l_new_dist_tbl(l_new_index).creation_date := SYSDATE;
3031 l_new_dist_tbl(l_new_index).last_updated_by := FND_GLOBAL.USER_ID;
3032 l_new_dist_tbl(l_new_index).last_update_date := SYSDATE;
3033 l_new_dist_tbl(l_new_index).last_update_login := FND_GLOBAL.LOGIN_ID;
3034
3035 l_new_index := l_new_index + 1;
3036
3037 -- Generate Recipient Distributions
3038
3039
3040 -- Fetching the coa id of the recipient
3041
3042
3043 SELECT l.chart_of_accounts_id
3044 INTO l_to_coa_id
3045 from gl_ledgers l
3046 WHERE l.ledger_id = l_trx_dtl_rec.to_ledger_id;
3047
3048 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3049 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'recipient coa id = ' || l_to_coa_id);
3050 END IF;
3051
3052 --Checking whether TAB is set up for the recipient
3053
3054 OPEN c_chk_sla (l_trx_dtl_rec.to_ledger_id);
3055 FETCH c_chk_sla INTO l_amb_context_code,
3056 l_account_definition_code;
3057 CLOSE c_chk_sla;
3058
3059 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3060 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Check if TAB is setup for recipient l_amb_context_code = ' || l_amb_context_code || ' l_account_definition_code = ' || l_account_definition_code);
3061 END IF;
3062
3063
3064 IF l_amb_context_code IS NOT NULL AND l_account_definition_code IS NOT NULL
3065
3066
3067 THEN
3068
3069 -- trying to get it from TAB set up
3070
3071 -- Derive values to be passed for recipient and initiator BSV
3072 -- Pass value only if 1 bsv is assigned to the LE.
3073 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3074 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Derive the Initiator and recipient BSV to be passed');
3075 END IF;
3076
3077 OPEN c_get_bsv(l_trx_dtl_rec.from_ledger_id,
3078 l_trx_dtl_rec.from_le_id,
3079 l_trx_dtl_rec.gl_date);
3080 FETCH c_get_bsv INTO l_initiator_bsv;
3081 CLOSE c_get_bsv;
3082
3083 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3084 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'initiator BSV l_initiator_bsv = ' || l_initiator_bsv);
3085 END IF;
3086
3087 OPEN c_get_bsv(l_trx_dtl_rec.to_ledger_id,
3088 l_trx_dtl_rec.to_le_id,
3089 l_trx_dtl_rec.gl_date);
3090 FETCH c_get_bsv INTO l_recipient_bsv;
3091 CLOSE c_get_bsv;
3092
3093 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3094 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'recipient BSV l_recipient_bsv = ' || l_recipient_bsv);
3095 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'populating the PLSQL table fun_xla_tab_pkg.g_array_xla_tab(1)' );
3096 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_1 = ' || l_trx_dtl_rec.batch_id);
3097 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_2 = ' || l_trx_dtl_rec.trx_id);
3098 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_3 = ' || NULL);
3099 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_4 = ' || NULL);
3100 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'source_distribution_id_num_5 = ' || NULL);
3101 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'account_type_code = ' || 'AGIS_RECIPIENT_CLEAR_ACCOUNT');
3102 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute1 = ' || l_trx_dtl_rec.bat_attribute1);
3103 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute10 = ' || l_trx_dtl_rec.bat_attribute10);
3104 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute11 = ' || l_trx_dtl_rec.bat_attribute11);
3105 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute12 = ' || l_trx_dtl_rec.bat_attribute12);
3106 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute13 = ' || l_trx_dtl_rec.bat_attribute13);
3107 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute14 = ' || l_trx_dtl_rec.bat_attribute14);
3108 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute15 = ' || l_trx_dtl_rec.bat_attribute15);
3109 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute2 = ' || l_trx_dtl_rec.bat_attribute2);
3110 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute3 = ' || l_trx_dtl_rec.bat_attribute3);
3111 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute4 = ' || l_trx_dtl_rec.bat_attribute4);
3112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute5 = ' || l_trx_dtl_rec.bat_attribute5);
3113 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute6 = ' || l_trx_dtl_rec.bat_attribute6);
3114 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute7 = ' || l_trx_dtl_rec.bat_attribute7);
3115 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute8 = ' || l_trx_dtl_rec.bat_attribute8);
3116 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_attribute9 = ' || l_trx_dtl_rec.bat_attribute9);
3117 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_category_code = ' || l_trx_dtl_rec.bat_attribute_category);
3118 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_currency_code = ' || l_trx_dtl_rec.currency_code);
3119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_from_ledger_id = ' || l_trx_dtl_rec.from_ledger_id);
3120 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_from_le_id = ' || l_trx_dtl_rec.from_le_id);
3121 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_initiator_bsv = ' || l_initiator_bsv);
3122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftb_initiator_id = ' || l_trx_dtl_rec.initiator_id);
3123 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute1 = ' || l_trx_dtl_rec.trx_attribute1);
3124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute2 = ' || l_trx_dtl_rec.trx_attribute2);
3125 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute3 = ' || l_trx_dtl_rec.trx_attribute3);
3126 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute4 = ' || l_trx_dtl_rec.trx_attribute4);
3127 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute5 = ' || l_trx_dtl_rec.trx_attribute5);
3128 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute6 = ' || l_trx_dtl_rec.trx_attribute6);
3129 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute7 = ' || l_trx_dtl_rec.trx_attribute7);
3130 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute8 = ' || l_trx_dtl_rec.trx_attribute8);
3131 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute9 = ' || l_trx_dtl_rec.trx_attribute9);
3132 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute10 = ' || l_trx_dtl_rec.trx_attribute10);
3133 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute11 = ' || l_trx_dtl_rec.trx_attribute11);
3134 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute12 = ' || l_trx_dtl_rec.trx_attribute12);
3135 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute13 = ' || l_trx_dtl_rec.trx_attribute13);
3136 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute14 = ' || l_trx_dtl_rec.trx_attribute14);
3137 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute15 = ' || l_trx_dtl_rec.trx_attribute15);
3138 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_attribute_category = ' || l_trx_dtl_rec.trx_attribute_category);
3139 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_recipient_bsv = ' || l_recipient_bsv);
3140 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_recipient_id = ' || l_trx_dtl_rec.recipient_id);
3141 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_to_ledger_id = ' || l_trx_dtl_rec.to_ledger_id);
3142 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fth_to_le_id = ' || l_trx_dtl_rec.to_le_id);
3143 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_allow_interest_accr_flag = ' || l_trx_dtl_rec.allow_interest_accrual_flag);
3144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_allow_invoicing_flag = ' || l_trx_dtl_rec.allow_invoicing_flag);
3145 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute1 = ' || l_trx_dtl_rec.typ_attribute1);
3146 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute2 = ' || l_trx_dtl_rec.typ_attribute2);
3147 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute3 = ' || l_trx_dtl_rec.typ_attribute3);
3148 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute4 = ' || l_trx_dtl_rec.typ_attribute4);
3149 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute5 = ' || l_trx_dtl_rec.typ_attribute5);
3150 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute6 = ' || l_trx_dtl_rec.typ_attribute6);
3151 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute7 = ' || l_trx_dtl_rec.typ_attribute7);
3152 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute8 = ' || l_trx_dtl_rec.typ_attribute8);
3153 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute9 = ' || l_trx_dtl_rec.typ_attribute9);
3154 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute10 = ' || l_trx_dtl_rec.typ_attribute10);
3155 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute11 = ' || l_trx_dtl_rec.typ_attribute11);
3156 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute12 = ' || l_trx_dtl_rec.typ_attribute12);
3157 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute13 = ' || l_trx_dtl_rec.typ_attribute13);
3158 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute14 = ' || l_trx_dtl_rec.typ_attribute14);
3159 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute15 = ' || l_trx_dtl_rec.typ_attribute15);
3160 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_attribute_category = ' || l_trx_dtl_rec.typ_attribute_category);
3161 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_manual_approve_flag = ' || l_trx_dtl_rec.manual_approve_flag);
3162 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_trx_type_id = ' || l_trx_dtl_rec.trx_type_id);
3163 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'ftt_vat_taxable_flag = ' || l_trx_dtl_rec.vat_taxable_flag);
3164 END IF;
3165
3166 -- Populate PLSQL Table
3167 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_1 := l_trx_dtl_rec.batch_id;
3168 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_2 := l_trx_dtl_rec.trx_id;
3169 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_3 := NULL;
3170 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_4 := NULL;
3171 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_5 := NULL;
3172 fun_xla_tab_pkg.g_array_xla_tab(1).account_type_code := 'AGIS_RECIPIENT_CLEAR_ACCOUNT';
3173 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute1 := l_trx_dtl_rec.bat_attribute1;
3174 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute10 := l_trx_dtl_rec.bat_attribute10;
3175 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute11 := l_trx_dtl_rec.bat_attribute11;
3176 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute12 := l_trx_dtl_rec.bat_attribute12;
3177 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute13 := l_trx_dtl_rec.bat_attribute13;
3178 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute14 := l_trx_dtl_rec.bat_attribute14;
3179 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute15 := l_trx_dtl_rec.bat_attribute15;
3180 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute2 := l_trx_dtl_rec.bat_attribute2;
3181 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute3 := l_trx_dtl_rec.bat_attribute3;
3182 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute4 := l_trx_dtl_rec.bat_attribute4;
3183 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute5 := l_trx_dtl_rec.bat_attribute5;
3184 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute6 := l_trx_dtl_rec.bat_attribute6;
3185 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute7 := l_trx_dtl_rec.bat_attribute7;
3186 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute8 := l_trx_dtl_rec.bat_attribute8;
3187 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute9 := l_trx_dtl_rec.bat_attribute9;
3188 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_category_code := l_trx_dtl_rec.bat_attribute_category;
3189 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_currency_code := l_trx_dtl_rec.currency_code;
3190 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_ledger_id := l_trx_dtl_rec.from_ledger_id;
3191 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_le_id := l_trx_dtl_rec.from_le_id;
3192 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_bsv := l_initiator_bsv;
3193 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_id := l_trx_dtl_rec.initiator_id;
3194 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute1 := l_trx_dtl_rec.trx_attribute1;
3195 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute10 := l_trx_dtl_rec.trx_attribute10;
3196 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute11 := l_trx_dtl_rec.trx_attribute11;
3197 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute12 := l_trx_dtl_rec.trx_attribute12;
3198 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute13 := l_trx_dtl_rec.trx_attribute13;
3199 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute14 := l_trx_dtl_rec.trx_attribute14;
3200 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute15 := l_trx_dtl_rec.trx_attribute15;
3201 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute2 := l_trx_dtl_rec.trx_attribute2;
3202 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute3 := l_trx_dtl_rec.trx_attribute3;
3203 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute4 := l_trx_dtl_rec.trx_attribute4;
3204 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute5 := l_trx_dtl_rec.trx_attribute5;
3205 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute6 := l_trx_dtl_rec.trx_attribute6;
3206 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute7 := l_trx_dtl_rec.trx_attribute7;
3207 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute8 := l_trx_dtl_rec.trx_attribute8;
3208 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute9 := l_trx_dtl_rec.trx_attribute9;
3209 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute_category := l_trx_dtl_rec.trx_attribute_category;
3210 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_bsv := l_recipient_bsv;
3211 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_id := l_trx_dtl_rec.recipient_id;
3212 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_ledger_id := l_trx_dtl_rec.to_ledger_id;
3213 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_le_id := l_trx_dtl_rec.to_le_id;
3214 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_interest_accr_flag := l_trx_dtl_rec.allow_interest_accrual_flag;
3215 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_invoicing_flag := l_trx_dtl_rec.allow_invoicing_flag;
3216 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute1 := l_trx_dtl_rec.typ_attribute1;
3217 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute10 := l_trx_dtl_rec.typ_attribute10;
3218 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute11 := l_trx_dtl_rec.typ_attribute11;
3219 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute12 := l_trx_dtl_rec.typ_attribute12;
3220 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute13 := l_trx_dtl_rec.typ_attribute13;
3221 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute14 := l_trx_dtl_rec.typ_attribute14;
3222 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute15 := l_trx_dtl_rec.typ_attribute15;
3223 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute2 := l_trx_dtl_rec.typ_attribute2;
3224 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute3 := l_trx_dtl_rec.typ_attribute3;
3225 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute4 := l_trx_dtl_rec.typ_attribute4;
3226 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute5 := l_trx_dtl_rec.typ_attribute5;
3227 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute6 := l_trx_dtl_rec.typ_attribute6;
3228 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute7 := l_trx_dtl_rec.typ_attribute7;
3229 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute8 := l_trx_dtl_rec.typ_attribute8;
3230 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute9 := l_trx_dtl_rec.typ_attribute9;
3231 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute_category := l_trx_dtl_rec.typ_attribute_category;
3232 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_manual_approve_flag := l_trx_dtl_rec.manual_approve_flag;
3233 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_trx_type_id := l_trx_dtl_rec.trx_type_id;
3234 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_vat_taxable_flag := l_trx_dtl_rec.vat_taxable_flag;
3235
3236 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3237 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Calling fun_xla_tab_pkg.run with following parameters');
3238 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_api_version = ' || '1.0');
3239 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_account_definition_type_code = ' || 'C');
3240 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_account_definition_code = ' || l_account_definition_code);
3241 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_transaction_coa_id = ' || l_to_coa_id);
3242 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct','p_mode = ' || 'ONLINE');
3243 END IF;
3244
3245 fun_xla_tab_pkg.run(
3246 p_api_version => 1.0
3247 ,p_account_definition_type_code => 'C'
3248 ,p_account_definition_code => l_account_definition_code
3249 ,p_transaction_coa_id => l_to_coa_id
3250 ,p_mode => 'ONLINE'
3251 ,x_return_status => x_status
3252 ,x_msg_count => x_msg_count
3253 ,x_msg_data => x_msg_data );
3254
3255 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3256 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg returns status ' || x_status );
3257 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg x_msg_count = ' || x_msg_count );
3258 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'fun_xla_tab_pkg x_msg_data = ' || x_msg_data );
3259 END IF;
3260
3261
3262 IF x_status = FND_API.G_RET_STS_SUCCESS
3263 THEN
3264 l_ccid := fun_xla_tab_pkg.g_array_xla_tab(1).target_ccid;
3265 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3266 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'if fun_xla_tab_pkg returns success set ccid = ' || l_ccid);
3267 END IF;
3268 END IF;
3269
3270 IF l_ccid <=0 OR x_status <> FND_API.G_RET_STS_SUCCESS -- Bug No : 7559411
3271 THEN
3272 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3273 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Error if l_ccid is not success or l_ccid is -1 and return');
3274 END IF;
3275 x_status := FND_API.G_RET_STS_ERROR;
3276 RETURN;
3277 END IF;
3278
3279 END IF;
3280
3281 IF l_amb_context_code IS NULL OR l_account_definition_code IS NULL OR l_ccid IS NULL THEN
3282
3283 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3284 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'If TAB is not setup or ccid could not be derived then');
3285 END IF;
3286 -- SLA TAB is not set up or the ccid could not be derived from TAB Set up
3287 -- Trying to get the default ccid from Accouning Setups
3288 IF l_reci_dist_tbl(1).initiator_le_id <> l_reci_dist_tbl(1).recipient_le_id
3289 THEN
3290
3291 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3292 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'calling fun_bal_utils_grp.get_intercompany_account with following parameters');
3293 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_api_version = ' ||'1.0');
3294 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_init_msg_list = ' || FND_API.G_TRUE);
3295 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_ledger_id = ' || l_reci_dist_tbl(1).ledger_id);
3296 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_le = ' || l_reci_dist_tbl(1).initiator_le_id);
3297 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_bsv = ' || l_reci_bsv);
3298 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_ledger_id = ' || l_init_dist_tbl(1).ledger_id);
3299 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_le = ' || l_reci_dist_tbl(1).recipient_le_id);
3300 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_to_bsv = ' || l_init_bsv);
3301 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_gl_date = ' || l_reci_dist_tbl(1).gl_date);
3302 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_acct_type = ' || 'P');
3303 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_ccid = ' || l_ccid);
3304 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_reciprocal_ccid = ' || l_reciprocal_ccid);
3305 END IF;
3306
3307 fun_bal_utils_grp.get_intercompany_account
3308 (p_api_version => 1.0,
3309 p_init_msg_list => FND_API.G_TRUE,
3310 p_ledger_id => l_reci_dist_tbl(1).ledger_id,
3311 p_from_le => l_reci_dist_tbl(1).recipient_le_id,
3312 p_from_bsv => l_reci_bsv,
3313 p_to_ledger_id => l_init_dist_tbl(1).ledger_id,
3314 p_to_le => l_reci_dist_tbl(1).initiator_le_id,
3315 p_to_bsv => l_init_bsv,
3316 p_gl_date => l_reci_dist_tbl(1).gl_date,
3317 p_acct_type => 'P',
3318 x_status => x_status,
3319 x_msg_count => x_msg_count,
3320 x_msg_data => x_msg_data,
3321 x_ccid => l_ccid,
3322 x_reciprocal_ccid => l_reciprocal_ccid);
3323 ELSE
3324
3325 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3326 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'calling fun_bal_utils_grp.get_intracompany_account with following parameters');
3327 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_api_version = ' ||'1.0');
3328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_init_msg_list = ' || FND_API.G_TRUE);
3329 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_ledger_id = ' || l_reci_dist_tbl(1).ledger_id);
3330 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_from_le = ' || l_reci_dist_tbl(1).initiator_le_id);
3331 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_source = ' || 'Global Intercompany');
3332 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_category = ' || 'Global Intercompany');
3333 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_dr_bsv = ' || l_reci_bsv);
3334 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_cr_bsv = ' || l_init_bsv);
3335 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_gl_date = ' || l_reci_dist_tbl(1).gl_date);
3336 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'p_acct_type = ' || 'D');
3337 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_ccid = ' || l_ccid);
3338 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_reciprocal_ccid = ' || l_reciprocal_ccid);
3339 END IF;
3340
3341 fun_bal_utils_grp.get_intracompany_account
3342 (p_api_version => 1.0,
3343 p_init_msg_list => FND_API.G_TRUE,
3344 p_ledger_id => l_reci_dist_tbl(1).ledger_id,
3345 p_from_le => l_reci_dist_tbl(1).recipient_le_id,
3346 p_source => 'Global Intercompany',
3347 p_category => 'Global Intercompany',
3348 p_dr_bsv => l_reci_dist_tbl(1).dist_bsv,
3349 p_cr_bsv => l_init_dist_tbl(1).dist_bsv,
3350 p_gl_date => l_reci_dist_tbl(1).gl_date,
3351 p_acct_type => 'C',
3352 x_status => x_status,
3353 x_msg_count => x_msg_count,
3354 x_msg_data => x_msg_data,
3355 x_ccid => l_ccid,
3356 x_reciprocal_ccid => l_reciprocal_ccid);
3357 END IF;
3358
3359 IF l_ccid IS NULL OR l_ccid <=0 OR x_status <> FND_API.G_RET_STS_SUCCESS -- Bug No : 6969506
3360 THEN
3361 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3362 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Error if l_ccid is null or return status is not success and return');
3363 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_status = ' || x_status);
3364 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_msg_count = ' || x_msg_count);
3365 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'x_msg_data = ' || x_msg_data);
3366 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_ccid = ' || l_ccid);
3367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'l_reciprocal_ccid = ' || l_reciprocal_ccid);
3368 END IF;
3369 x_status := FND_API.G_RET_STS_ERROR;
3370 RETURN;
3371 END IF;
3372
3373 END IF;
3374
3375 IF (l_reci_dist_tbl(1).reci_amount_cr IS NULL or to_number(l_reci_dist_tbl(1).reci_amount_cr)=0) and (l_reci_dist_tbl(1).reci_amount_dr is null OR to_number(l_reci_dist_tbl(1).reci_amount_dr)=0) -- Bug No : 6969506, 7013314
3376 THEN
3377 x_status := FND_API.G_RET_STS_ERROR;
3378 RETURN;
3379 END IF;
3380
3381
3382 -- Now build the new distribution record
3383 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3384 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'Build the Distribution lines');
3385 END IF;
3386 SELECT FUN_DIST_LINES_S.nextval
3387 INTO l_new_dist_tbl(l_new_index).dist_id
3388 FROM dual;
3389
3390 l_new_dist_tbl(l_new_index).line_id := l_reci_dist_tbl(1).line_id;
3391 l_new_dist_tbl(l_new_index).trx_id := l_reci_dist_tbl(1).trx_id;
3392 l_new_dist_tbl(l_new_index).dist_number := l_new_dist_tbl(l_new_index).dist_id;
3393 l_new_dist_tbl(l_new_index).party_id := l_reci_dist_tbl(1).recipient_id;
3394 l_new_dist_tbl(l_new_index).party_type_flag := 'R';
3395 l_new_dist_tbl(l_new_index).dist_type_flag := 'P';
3396 l_new_dist_tbl(l_new_index).amount_cr := l_reci_dist_tbl(1).reci_amount_cr;
3397 l_new_dist_tbl(l_new_index).amount_dr := l_reci_dist_tbl(1).reci_amount_dr;
3398 l_new_dist_tbl(l_new_index).ccid := l_ccid;
3399 l_new_dist_tbl(l_new_index).description := l_reci_dist_tbl(1).description;
3400 l_new_dist_tbl(l_new_index).auto_generate_flag := 'Y';
3401 l_new_dist_tbl(l_new_index).created_by := FND_GLOBAL.USER_ID;
3402 l_new_dist_tbl(l_new_index).creation_date := SYSDATE;
3403 l_new_dist_tbl(l_new_index).last_updated_by := FND_GLOBAL.USER_ID;
3404 l_new_dist_tbl(l_new_index).last_update_date := SYSDATE;
3405 l_new_dist_tbl(l_new_index).last_update_login := FND_GLOBAL.LOGIN_ID;
3406
3407 l_new_index := l_new_index + 1;
3408
3409
3410
3411 IF x_status = FND_API.G_RET_STS_SUCCESS
3412
3413 THEN
3414 -- Delete existing intercompany account distribution lines if exists.
3415 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3416 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_interco_acct', 'If Sucessful Delete existing lines and insert.');
3417 END IF;
3418 DELETE fun_dist_lines
3419 WHERE trx_id = l_trx_id
3420 AND dist_type_flag IN ('R','P');
3421
3422
3423
3424 FORALL i IN 1..l_new_dist_tbl.COUNT
3425 INSERT into FUN_DIST_LINES
3426 VALUES l_new_dist_tbl(i);
3427
3428
3429 END IF;
3430 EXCEPTION
3431 WHEN others THEN
3432 x_status := FND_API.G_RET_STS_ERROR;
3433 END generate_interco_acct;
3434
3435
3436
3437
3438 /* ---------------------------------------------------------------------------
3439 Name : get_default_sla_ccid
3440 Pre-reqs : None.
3441 Modifies : None.
3442 Function : This function is called by recipient_interco_acct
3443 to get the default ccid from SLA
3444 Parameters:
3445 IN : p_trx_id -- fun_trx_headers.trx_id
3446 OUT : x_status -- FND_API.G_RET_STS_SUCCESS, ..UNEXP,..ERROR
3447 x_msg_count -- Number of messages
3448 x_msg_data -- Message data
3449 x_ccid -- CCID
3450 Notes : None.
3451 Testing : This function will be tested via workflow FUNRMAIN
3452 ------------------------------------------------------------------------------*/
3453 PROCEDURE get_default_sla_ccid (
3454 p_trx_id IN NUMBER,
3455 x_ccid IN OUT NOCOPY NUMBER,
3456 x_status IN OUT NOCOPY VARCHAR2,
3457 x_msg_count IN OUT NOCOPY NUMBER,
3458 x_msg_data IN OUT NOCOPY VARCHAR2)
3459 IS
3460
3461 CURSOR c_dtls IS
3462 SELECT b.batch_id,
3463 b.initiator_id initiator_id
3464 ,b.from_le_id from_le_id
3465 ,b.from_ledger_id from_ledger_id
3466 ,b.currency_code currency_code
3467 ,b.attribute1 bat_attribute1
3468 ,b.attribute2 bat_attribute2
3469 ,b.attribute3 bat_attribute3
3470 ,b.attribute4 bat_attribute4
3471 ,b.attribute5 bat_attribute5
3472 ,b.attribute6 bat_attribute6
3473 ,b.attribute7 bat_attribute7
3474 ,b.attribute8 bat_attribute8
3475 ,b.attribute9 bat_attribute9
3476 ,b.attribute10 bat_attribute10
3477 ,b.attribute11 bat_attribute11
3478 ,b.attribute12 bat_attribute12
3479 ,b.attribute13 bat_attribute13
3480 ,b.attribute14 bat_attribute14
3481 ,b.attribute15 bat_attribute15
3482 ,b.attribute_category bat_attribute_category
3483 ,t.trx_id trx_id
3484 ,t.recipient_id recipient_id
3485 ,t.to_le_id to_le_id
3486 ,t.to_ledger_id to_ledger_id
3487 ,t.attribute1 trx_attribute1
3488 ,t.attribute2 trx_attribute2
3489 ,t.attribute3 trx_attribute3
3490 ,t.attribute4 trx_attribute4
3491 ,t.attribute5 trx_attribute5
3492 ,t.attribute6 trx_attribute6
3493 ,t.attribute7 trx_attribute7
3494 ,t.attribute8 trx_attribute8
3495 ,t.attribute9 trx_attribute9
3496 ,t.attribute10 trx_attribute10
3497 ,t.attribute11 trx_attribute11
3498 ,t.attribute12 trx_attribute12
3499 ,t.attribute13 trx_attribute13
3500 ,t.attribute14 trx_attribute14
3501 ,t.attribute15 trx_attribute15
3502 ,t.attribute_category trx_attribute_category
3503 ,y.trx_type_id trx_type_id
3504 ,y.manual_approve_flag manual_approve_flag
3505 ,y.allow_invoicing_flag allow_invoicing_flag
3506 ,y.vat_taxable_flag vat_taxable_flag
3507 ,y.allow_interest_accrual_flag allow_interest_accrual_flag
3508 ,y.attribute1 typ_attribute1
3509 ,y.attribute2 typ_attribute2
3510 ,y.attribute3 typ_attribute3
3511 ,y.attribute4 typ_attribute4
3512 ,y.attribute5 typ_attribute5
3513 ,y.attribute6 typ_attribute6
3514 ,y.attribute7 typ_attribute7
3515 ,y.attribute8 typ_attribute8
3516 ,y.attribute9 typ_attribute9
3517 ,y.attribute10 typ_attribute10
3518 ,y.attribute11 typ_attribute11
3519 ,y.attribute12 typ_attribute12
3520 ,y.attribute13 typ_attribute13
3521 ,y.attribute14 typ_attribute14
3522 ,y.attribute15 typ_attribute15
3523 ,y.attribute_category typ_attribute_category
3524 ,l.chart_of_accounts_id coa_id
3525 ,b.batch_date
3526 ,b.gl_date
3527 FROM fun_trx_batches b,
3528 fun_trx_headers t,
3529 fun_trx_types_vl y,
3530 gl_ledgers l
3531 WHERE b.batch_id = t.batch_id
3532 AND b.trx_type_id = y.trx_type_id
3533 AND t.to_ledger_id = l.ledger_id
3534 AND t.trx_id = p_trx_id;
3535
3536 l_trx_dtl_rec c_dtls%ROWTYPE;
3537
3538 CURSOR c_chk_sla (p_ledger_id IN NUMBER) IS
3539 SELECT amb_context_code,
3540 account_definition_code
3541 FROM fun_trx_acct_definitions
3542 WHERE ledger_id = p_ledger_id;
3543
3544 CURSOR c_get_bsv(p_ledger_id NUMBER,
3545 p_le_id NUMBER,
3546 p_gl_date DATE) IS
3547 SELECT vals.segment_value
3548 FROM gl_ledger_le_bsv_specific_v vals
3549 WHERE vals.legal_entity_id = p_le_id
3550 AND vals.ledger_id = p_ledger_id
3551 AND p_gl_date BETWEEN Nvl(vals.start_date, p_gl_date) AND Nvl(vals.end_date, p_gl_date)
3552 AND (SELECT COUNT(*)
3553 FROM gl_ledger_le_bsv_specific_v vals1
3554 WHERE vals1.legal_entity_id = p_le_id
3555 AND vals1.ledger_id = p_ledger_id
3556 AND p_gl_date BETWEEN Nvl(vals1.start_date, p_gl_date) AND Nvl(vals1.end_date, p_gl_date)) = 1;
3557
3558 l_amb_context_code fun_trx_acct_definitions.amb_context_code%TYPE;
3559 l_account_definition_code fun_trx_acct_definitions.account_definition_code%TYPE;
3560 l_initiator_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
3561 l_recipient_bsv gl_ledger_le_bsv_specific_v.segment_value%TYPE;
3562
3563 BEGIN
3564 x_status := FND_API.G_RET_STS_SUCCESS;
3565 x_ccid := -1;
3566
3567 OPEN c_dtls;
3568 FETCH c_dtls INTO l_trx_dtl_rec;
3569 CLOSE c_dtls;
3570
3571 OPEN c_chk_sla (l_trx_dtl_rec.to_ledger_id);
3572 FETCH c_chk_sla INTO l_amb_context_code,
3573 l_account_definition_code;
3574 CLOSE c_chk_sla;
3575
3576 IF l_amb_context_code IS NULL OR l_account_definition_code IS NULL
3577 THEN
3578 -- SLA TAB is not set up hence unable to get
3579 -- default ccid
3580 RETURN;
3581 END IF;
3582
3583 -- Derive values to be passed for recipient and initiator BSV
3584 -- Pass value only if 1 bsv is assigned to the LE.
3585 OPEN c_get_bsv(l_trx_dtl_rec.from_ledger_id,
3586 l_trx_dtl_rec.from_le_id,
3587 l_trx_dtl_rec.gl_date);
3588 FETCH c_get_bsv INTO l_initiator_bsv;
3589 CLOSE c_get_bsv;
3590
3591 OPEN c_get_bsv(l_trx_dtl_rec.to_ledger_id,
3592 l_trx_dtl_rec.to_le_id,
3593 l_trx_dtl_rec.gl_date);
3594 FETCH c_get_bsv INTO l_recipient_bsv;
3595 CLOSE c_get_bsv;
3596
3597 -- Populate PLSQL Table
3598 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_1 := l_trx_dtl_rec.batch_id;
3599 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_2 := l_trx_dtl_rec.trx_id;
3600 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_3 := NULL;
3601 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_4 := NULL;
3602 fun_xla_tab_pkg.g_array_xla_tab(1).source_distribution_id_num_5 := NULL;
3603 fun_xla_tab_pkg.g_array_xla_tab(1).account_type_code := 'AGIS_RECIPIENT_DIST_ACCOUNT';
3604 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute1 := l_trx_dtl_rec.bat_attribute1;
3605 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute10 := l_trx_dtl_rec.bat_attribute10;
3606 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute11 := l_trx_dtl_rec.bat_attribute11;
3607 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute12 := l_trx_dtl_rec.bat_attribute12;
3608 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute13 := l_trx_dtl_rec.bat_attribute13;
3609 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute14 := l_trx_dtl_rec.bat_attribute14;
3610 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute15 := l_trx_dtl_rec.bat_attribute15;
3611 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute2 := l_trx_dtl_rec.bat_attribute2;
3612 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute3 := l_trx_dtl_rec.bat_attribute3;
3613 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute4 := l_trx_dtl_rec.bat_attribute4;
3614 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute5 := l_trx_dtl_rec.bat_attribute5;
3615 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute6 := l_trx_dtl_rec.bat_attribute6;
3616 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute7 := l_trx_dtl_rec.bat_attribute7;
3617 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute8 := l_trx_dtl_rec.bat_attribute8;
3618 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_attribute9 := l_trx_dtl_rec.bat_attribute9;
3619 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_category_code := l_trx_dtl_rec.bat_attribute_category;
3620 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_currency_code := l_trx_dtl_rec.currency_code;
3621 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_ledger_id := l_trx_dtl_rec.from_ledger_id;
3622 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_from_le_id := l_trx_dtl_rec.from_le_id;
3623 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_bsv := l_initiator_bsv;
3624 fun_xla_tab_pkg.g_array_xla_tab(1).ftb_initiator_id := l_trx_dtl_rec.initiator_id;
3625 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute1 := l_trx_dtl_rec.trx_attribute1;
3626 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute10 := l_trx_dtl_rec.trx_attribute10;
3627 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute11 := l_trx_dtl_rec.trx_attribute11;
3628 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute12 := l_trx_dtl_rec.trx_attribute12;
3629 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute13 := l_trx_dtl_rec.trx_attribute13;
3630 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute14 := l_trx_dtl_rec.trx_attribute14;
3631 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute15 := l_trx_dtl_rec.trx_attribute15;
3632 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute2 := l_trx_dtl_rec.trx_attribute2;
3633 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute3 := l_trx_dtl_rec.trx_attribute3;
3634 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute4 := l_trx_dtl_rec.trx_attribute4;
3635 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute5 := l_trx_dtl_rec.trx_attribute5;
3636 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute6 := l_trx_dtl_rec.trx_attribute6;
3637 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute7 := l_trx_dtl_rec.trx_attribute7;
3638 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute8 := l_trx_dtl_rec.trx_attribute8;
3639 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute9 := l_trx_dtl_rec.trx_attribute9;
3640 fun_xla_tab_pkg.g_array_xla_tab(1).fth_attribute_category := l_trx_dtl_rec.trx_attribute_category;
3641 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_bsv := l_recipient_bsv;
3642 fun_xla_tab_pkg.g_array_xla_tab(1).fth_recipient_id := l_trx_dtl_rec.recipient_id;
3643 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_ledger_id := l_trx_dtl_rec.to_ledger_id;
3644 fun_xla_tab_pkg.g_array_xla_tab(1).fth_to_le_id := l_trx_dtl_rec.to_le_id;
3645 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_interest_accr_flag := l_trx_dtl_rec.allow_interest_accrual_flag;
3646 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_allow_invoicing_flag := l_trx_dtl_rec.allow_invoicing_flag;
3647 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute1 := l_trx_dtl_rec.typ_attribute1;
3648 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute10 := l_trx_dtl_rec.typ_attribute10;
3649 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute11 := l_trx_dtl_rec.typ_attribute11;
3650 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute12 := l_trx_dtl_rec.typ_attribute12;
3651 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute13 := l_trx_dtl_rec.typ_attribute13;
3652 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute14 := l_trx_dtl_rec.typ_attribute14;
3653 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute15 := l_trx_dtl_rec.typ_attribute15;
3654 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute2 := l_trx_dtl_rec.typ_attribute2;
3655 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute3 := l_trx_dtl_rec.typ_attribute3;
3656 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute4 := l_trx_dtl_rec.typ_attribute4;
3657 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute5 := l_trx_dtl_rec.typ_attribute5;
3658 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute6 := l_trx_dtl_rec.typ_attribute6;
3659 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute7 := l_trx_dtl_rec.typ_attribute7;
3660 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute8 := l_trx_dtl_rec.typ_attribute8;
3661 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute9 := l_trx_dtl_rec.typ_attribute9;
3662 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_attribute_category := l_trx_dtl_rec.typ_attribute_category;
3663 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_manual_approve_flag := l_trx_dtl_rec.manual_approve_flag;
3664 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_trx_type_id := l_trx_dtl_rec.trx_type_id;
3665 fun_xla_tab_pkg.g_array_xla_tab(1).ftt_vat_taxable_flag := l_trx_dtl_rec.vat_taxable_flag;
3666
3667
3668 fun_xla_tab_pkg.run(
3669 p_api_version => 1.0
3670 ,p_account_definition_type_code => 'C'
3671 ,p_account_definition_code => l_account_definition_code
3672 ,p_transaction_coa_id => l_trx_dtl_rec.coa_id
3673 ,p_mode => 'ONLINE'
3674 ,x_return_status => x_status
3675 ,x_msg_count => x_msg_count
3676 ,x_msg_data => x_msg_data );
3677
3678 IF x_status = FND_API.G_RET_STS_SUCCESS
3679 THEN
3680 x_ccid := fun_xla_tab_pkg.g_array_xla_tab(1).target_ccid;
3681 END IF;
3682
3683 END get_default_sla_ccid;
3684
3685 /*-----------------------------------------------------
3686 * PROCEDURE generate_acct_lines
3687 * ----------------------------------------------------
3688 * Generate intercompany accounting lines
3689 * ---------------------------------------------------*/
3690
3691 PROCEDURE generate_acct_lines (
3692 itemtype IN varchar2,
3693 itemkey IN varchar2,
3694 actid IN number,
3695 funcmode IN varchar2,
3696 resultout IN OUT NOCOPY varchar2)
3697 IS
3698 l_trx_id number;
3699 l_status varchar2(1);
3700 l_msg_count number;
3701 l_msg_data varchar2(1000);
3702 l_error varchar2(2000);
3703 l_user_id NUMBER;
3704 l_resp_id NUMBER;
3705 l_appl_id NUMBER;
3706 BEGIN
3707
3708 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3709 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_acct_lines', 'Begin. itemtype = ' || itemtype || ' itemkey = ' || itemkey || ' actid = ' || actid || ' funcmode = ' || funcmode);
3710 END IF;
3711 -- Bug: 7639191
3712 l_resp_id := wf_engine.GetItemAttrNumber
3713 (itemtype => itemtype,
3714 itemkey => itemkey,
3715 aname =>'RESP_ID');
3716 l_user_id := wf_engine.GetItemAttrNumber
3717 (itemtype => itemtype,
3718 itemkey => itemkey,
3719 aname =>'USER_ID');
3720 l_appl_id := wf_engine.GetItemAttrNumber
3721 (itemtype => itemtype,
3722 itemkey => itemkey,
3723 aname =>'APPL_ID');
3724 IF (funcmode = 'RUN') THEN
3725 l_trx_id := wf_engine.GetItemAttrNumber
3726 (itemtype => itemtype,
3727 itemkey => itemkey,
3728 aname => 'TRX_ID');
3729 FND_GLOBAL.APPS_INITIALIZE(l_user_id,l_resp_id,l_appl_id);
3730
3731 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3732 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_acct_lines', 'l_trx_id = ' || l_trx_id);
3733 END IF;
3734
3735 -- Generate the intercompany account distributions
3736 generate_interco_acct (
3737 p_trx_id => l_trx_id,
3738 x_status => l_status,
3739 x_msg_count => l_msg_count,
3740 x_msg_data => l_msg_data);
3741
3742 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3743 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_acct_lines', 'after generate_interco_acct l_status = ' || l_status);
3744 END IF;
3745
3746 IF l_status <> FND_API.G_RET_STS_SUCCESS
3747 THEN
3748
3749 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3750 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_acct_lines', 'if l_status not equal to ' || FND_API.G_RET_STS_SUCCESS);
3751 END IF;
3752
3753 resultout := wf_engine.eng_completed||':F';
3754 fun_trx_pvt.update_trx_status
3755 (p_api_version => 1.0,
3756 x_return_status => l_status,
3757 x_msg_count => l_msg_count,
3758 x_msg_data => l_msg_data,
3759 p_trx_id => l_trx_id,
3760 p_update_status_to => 'ERROR');
3761
3762
3763 FOR i IN 1..l_msg_count
3764 LOOP
3765 l_error := l_error ||fnd_msg_pub.get(i, 'F') ||' ';
3766 END LOOP;
3767
3768 wf_engine.SetItemAttrText(itemtype => itemtype,
3769 itemkey => itemkey,
3770 aname => 'ERROR_MESSAGE',
3771 avalue => l_error);
3772 RETURN;
3773 END IF;
3774
3775 resultout := wf_engine.eng_completed||':'||'T';
3776 RETURN;
3777 END IF;
3778
3779 resultout := wf_engine.eng_completed||':'||'T';
3780 RETURN;
3781
3782 EXCEPTION
3783 WHEN others THEN
3784
3785 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3786 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_recipient_wf.generate_acct_lines', 'WHEN OTHERS EXCEPTION');
3787 END IF;
3788 wf_core.context('FUN_RECIPIENT_WF', 'generate_acct_lines',
3789 itemtype, itemkey, TO_CHAR(actid), funcmode);
3790 -- RAISE;
3791
3792 END generate_acct_lines;
3793
3794 -- Bug No:5897122. This cursor will fetch the user names to whom the Notification options is not active and
3795 -- having access to approve the transaction.
3796 procedure create_wf_roles (
3797 trx_id in varchar2)
3798 IS
3799 c_user_name varchar2(100);
3800 -- l_userTable WF_DIRECTORY.UserTable;
3801
3802 CURSOR get_user_names IS
3803 select 'X'
3804 from wf_local_user_roles role
3805 where role.role_name = 'FUN_ADHOC_RECI_'||trx_id
3806 and role.user_name = FND_GLOBAL.USER_NAME;
3807 BEGIN
3808 OPEN get_user_names;
3809 FETCH get_user_names INTO c_user_name;
3810
3811 IF get_user_names%NOTFOUND
3812 THEN
3813 CLOSE get_user_names;
3814
3815 --Bug NO:6526807 Using wf_directory.AddUsersToAdHocRole2 api instead of
3816 --wf_directory.AddUsersToAdHocRole so that the application can handle
3817 -- spaces in user name
3818
3819 -- wf_directory.AddUsersToAdHocRole(
3820 -- role_name => 'FUN_ADHOC_RECI_'||trx_id, role_users => c_user_name);
3821
3822 -- l_userTable(0) := c_user_name;
3823 -- wf_directory.AddUsersToAdHocRole2(
3824 -- role_name => 'FUN_ADHOC_RECI_'||trx_id, role_users => l_userTable);
3825
3826 wf_directory.AddUsersToAdHocRole(
3827 role_name => 'FUN_ADHOC_RECI_'||trx_id, role_users => c_user_name);
3828
3829 END IF;
3830
3831 END create_wf_roles;
3832 END;