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