[Home] [Help]
PACKAGE BODY: APPS.FUN_INITIATOR_WF_PKG
Source
1 PACKAGE BODY FUN_INITIATOR_WF_PKG AS
2 /* $Header: funwintb.pls 120.26.12010000.5 2008/09/24 11:44:03 makansal ship $ */
3
4
5 -- Set workflow item attributes for the process
6
7 /*-----------------------------------------------------|
8 | PROCEDURE SET_ATTRIBUTES |
9 |------------------------------------------------------|
10 | Parameters item_type IN Varchar2 |
11 | item_key IN Varchar2 |
12 | act_id IN NUMBER |
13 | funcmode IN Varchar2 |
14 | resultout IN Varchar2 |
15 | |
16 |------------------------------------------------------|
17 | Description |
18 | Set the attributes of the WF process |
19 | |
20 | |
21 | |
22 | |
23 | |
24 |-----------------------------------------------------*/
25
26
27 PROCEDURE SET_ATTRIBUTES (itemtype IN VARCHAR2,
28 itemkey IN VARCHAR2,
29 actid IN NUMBER,
30 funcmode IN VARCHAR2,
31 resultout OUT NOCOPY VARCHAR2)
32
33 IS
34
35 l_batch_id NUMBER;
36 l_trx_id NUMBER;
37 l_initiator_id NUMBER;
38 l_recipient_id NUMBER;
39 l_batch_number Varchar2(15);
40 l_gl_date DATE;
41 l_batch_date DATE;
42 l_recipient_name Varchar2(360);
43 l_invoice_flag Varchar2(1);
44 l_event_name Varchar2(240);
45 l_status_code Varchar2(15);
46 l_role_name Varchar2(30);
47 l_event_key Varchar2(64);
48 l_trx_amt NUMBER;
49 l_currency Varchar2(15);
50
51 l_initiator_name VARCHAR2(360);
52 l_trx_number fun_trx_headers.trx_number%TYPE;
53
54 BEGIN
55 l_status_code :='Test';
56 IF (funcmode = 'RUN') THEN
57
58 -- Obtain the batch_id and trx_id
59
60 -- Note the attributes BATCH_ID and TRX_ID are set
61 -- by the receiving event
62
63 l_batch_id := wf_engine.getitemattrnumber(itemtype,
64 itemkey,
65 'BATCH_ID');
66
67 l_trx_id := wf_engine.getitemattrnumber(itemtype,
68 itemkey,
69 'TRX_ID');
70
71
72
73 -- obtain the attrubutes value from the database
74 BEGIN
75
76 -- Changes for AME Uptake, 3671923. Bidisha S, 28 Jun 2004
77 -- Modified the query below to retrieve the recipient org name
78 -- and the transaction number
79 SELECT ftb.batch_number,
80 ftb.gl_date,
81 ftb.batch_date,
82 fth.initiator_id,
83 fth.recipient_id,
84 fth.invoice_flag,
85 -- decode(fth.init_amount_cr,0, fth.init_amount_dr, NULL, fth.init_amount_dr, fth.init_amount_cr),
86 -- Bug No. 6854675 Changed the select statement to fetch a numeric value
87 --ltrim(to_char(decode(nvl(ftb.running_total_cr,0),0,ftb.running_total_dr,ftb.running_total_cr),'999999999.99')),
88 decode(nvl(ftb.running_total_cr,0),0,ftb.running_total_dr,ftb.running_total_cr),
89 ftb.currency_code,
90 hzp.party_name ,
91 fth.trx_number,
92 ini.party_name
93 INTO l_batch_number,
94 l_gl_date,
95 l_batch_date,
96 l_initiator_id,
97 l_recipient_id,
98 l_invoice_flag,
99 l_trx_amt,
100 l_currency,
101 l_recipient_name,
102 l_trx_number,
103 l_initiator_name
104 FROM fun_trx_batches ftb,
105 fun_trx_headers fth,
106 hz_parties hzp,
107 hz_parties ini
108 WHERE ftb.batch_id = l_batch_id
109 AND ftb.batch_id=fth.batch_id
110 AND fth.recipient_id=hzp.party_id
111 AND fth.initiator_id=ini.party_id
112 AND fth.trx_id=l_trx_id;
113 EXCEPTION
114 WHEN NO_DATA_FOUND THEN
115 NULL;
116
117 END;
118 -- set the attribute
119
120 -- batch number
121 wf_engine.setitemattrtext(itemtype,
122 itemkey,
123 'BATCH_NUMBER',
124 l_batch_number);
125
126 -- gl_date
127 wf_engine.setitemattrdate(itemtype,
128 itemkey,
129 'GL_DATE',
130 l_gl_date);
131
132 -- batch_date
133
134 wf_engine.setitemattrdate(itemtype,
135 itemkey,
136 'BATCH_DATE',
137 l_batch_date);
138
139 -- initiator_id
140
141 wf_engine.setitemattrnumber(itemtype,
142 itemkey,
143 'INITIATOR_ID',
144 l_initiator_id);
145 -- recipient_id
146
147 wf_engine.setitemattrnumber(itemtype,
148 itemkey,
149 'RECIPIENT_ID',
150 l_recipient_id);
151
152 -- invoice_flag
153
154 wf_engine.setitemattrtext(itemtype,
155 itemkey,
156 'INVOICE_FLAG',
157 l_invoice_flag);
158
159 -- transaction amount
160
161 wf_engine.setitemattrnumber(itemtype,
162 itemkey,
163 'TRX_AMT',
164 l_trx_amt);
165
166
167 -- Currency
168
169 wf_engine.setitemattrtext(itemtype,
170 itemkey,
171 'CURRENCY',
172 l_currency);
173
174 -- recipient name
175
176 wf_engine.setitemattrtext(itemtype,
177 itemkey,
178 'RECIPIENT_NAME',
179 l_recipient_name);
180
181
182
183 /* Start of changes for AME Uptake, 3671923. Bidisha S, 29 Jun 2004 */
184
185 wf_engine.setitemattrtext(itemtype,
186 itemkey,
187 'INITIATOR_NAME',
188 l_initiator_name);
189
190 wf_engine.setitemattrtext(itemtype,
191 itemkey,
192 'TRX_NUMBER',
193 l_trx_number);
194 -- Contact information
195 --l_role_name :=FUN_WF_COMMON.get_contact_role(l_initiator_id);
196
197 -- wf_engine.setitemattrtext(itemtype,
198 -- itemkey,
199 -- 'CONTACT',
200 -- l_role_name);
201
202 /* End of changes for AME Uptake, 3671923. Bidisha S, 29 Jun 2004 */
203
204
205 -- Get the status code from the event name
206
207 l_event_name := rtrim(ltrim(lower(wf_engine.getitemattrtext(itemtype,
208 itemkey,
209 'EVENT_NAME')), ' '), ' ');
210
211 -- Case check
212 if(INSTR(l_event_name, 'oracle.apps.fun.manualtrx.complete.receive')<>0) then
213 l_status_code:='COMPLETE';
214 elsif (INSTR(l_event_name, 'oracle.apps.fun.manualtrx.rejection.receive')<>0) then
215 l_status_code :='REJECTED';
216 elsif (INSTR(l_event_name, 'oracle.apps.fun.manualtrx.approval.receive')<>0) then
217 l_status_code :='APPROVED';
218
219 -- set the event key for the AR/GL transfer event
220 l_event_key :=FUN_INITIATOR_WF_PKG.generate_key(p_batch_id =>l_batch_id,
221 p_trx_id => l_trx_id);
222
223 wf_engine.setitemattrtext(itemtype,
224 itemkey,
225 'TRANSFER_KEY',
226 l_event_key);
227
228 elsif (INSTR(l_event_name,'oracle.apps.fun.manualtrx.reception.receive')<>0) then
229 l_status_code :='RECEIVED';
230 elsif (INSTR(l_event_name, 'oracle.apps.fun.manualtrx.error.receive')<>0) then
231 l_status_code :='ERROR';
232 else
233 l_status_code :='UNEXPECTED';
234 end if;
235
236 -- set the status code
237
238 wf_engine.setitemattrtext(itemtype,
239 itemkey,
240 'STATUS',
241 l_status_code);
242
243 wf_engine.setitemattrnumber(itemtype,
244 itemkey,
245 'BATCH_ID',
246 l_batch_id);
247
248 wf_engine.setitemattrnumber(itemtype,
249 itemkey,
250 'TRX_ID',
251 l_trx_id);
252
253 wf_engine.setitemattrnumber(itemtype,
254 itemkey,
255 'PARTY_ID',
256 l_initiator_id);
257
258 -- no result needed
259 resultout := 'COMPLETE';
260 return;
261 END IF;
262
263 EXCEPTION
264
265 WHEN OTHERS THEN
266
267 -- Rcords this function call in the error system
268 -- in the case of an exception.
269
270 wf_core.context('FUN_INITIATOR_WF_PKG', 'SET_ATTRIBUTES',
271 itemtype, itemkey, to_char(actid), funcmode);
272
273 END SET_ATTRIBUTES;
274
275
276
277
278 -- Update Intercompany Transaction Status
279
280 /*-----------------------------------------------------|
281 | PROCEDURE UPDATE_STATUS |
282 |------------------------------------------------------|
283 | Parameters item_type IN Varchar2 |
284 | item_key IN Varchar2 |
285 | act_id IN NUMBER |
286 | funcmode IN Varchar2 |
287 | resultout IN Varchar2 |
288 | |
289 |------------------------------------------------------|
290 | Description |
291 | Update the intercompany transaction |
292 | status |
293 | |
294 | |
295 | |
296 | |
297 |-----------------------------------------------------*/
298
299
300 PROCEDURE UPDATE_STATUS ( itemtype in varchar2,
301 itemkey in varchar2,
302 actid in number,
303 funcmode in varchar2,
304 resultout out NOCOPY varchar2)
305
306 IS
307
308 -- Local variables
309
310 l_trx_id NUMBER;
311 l_batch_id NUMBER;
312 l_status VARCHAR2(15);
313 l_return_status Varchar2(1);
314 l_message_count NUMBER;
315 l_message_data Varchar2(1000);
316
317 BEGIN
318
319 -- get the attribute values
320
321 l_trx_id := wf_engine.GetItemAttrNumber( itemtype
322 , itemkey
323 , 'TRX_ID'
324 );
325
326 l_batch_id := wf_engine.GetItemAttrNumber( itemtype
327 , itemkey
328 , 'BATCH_ID'
329 );
330
331 l_status := wf_engine.GetItemAttrText( itemtype
332 ,itemkey
333 , 'STATUS'
334 );
335
336
337 -- Note: This function will call Transaction API
338 -- to update the status of a transaction
339 IF (funcmode = 'RUN') THEN
340
341 FUN_TRX_PVT.update_trx_status(p_api_version =>1.0,
342 x_return_status =>l_return_status,
343 x_msg_count => l_message_count,
344 x_msg_data => l_message_data,
345 p_trx_id => l_trx_id,
346 p_update_status_to => l_status);
347
348 -- Handle the API call return
349
350 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
351
352 raise FND_API.G_EXC_ERROR;
353 END IF;
354
355
356 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
357
358 raise FND_API.G_EXC_UNEXPECTED_ERROR;
359 END IF;
360
361 -- Assume the status update is successful
362
363
364 if( l_status='APPROVED') then
365 resultout := 'COMPLETE:APPROVED';
366 elsif (l_status ='REJECTED' ) then
367 resultout := 'COMPLETE:REJECTED';
368 elsif (l_status ='ERROR') then
369 resultout :='COMPLETE:ERROR';
370 else
371 resultout := 'COMPLETE:OTHERS';
372
373 END IF;
374
375 ELSE
376 resultout := 'COMPLETE';
377 END IF;
378
379 EXCEPTION
380 WHEN OTHERS THEN
381 WF_CORE.Context('FUN_INITIATOR_WF_PKG', 'UPDATE_STATUS',
382 itemtype, itemkey, actid, funcmode);
383 RAISE;
384
385
386 END UPDATE_STATUS;
387
388
389
390 -- Transfer the transaction to AR interface table
391 --
392
393 /*-----------------------------------------------------|
394 | PROCEDURE TRANSFER_AR |
395 |------------------------------------------------------|
396 | Parameters itemtype IN Varchar2 |
397 | itemkey IN Varchar2 |
398 | actid IN NUMBER |
399 | funcmode IN Varchar2 |
400 | resultout IN Varchar2 |
401 | |
402 |------------------------------------------------------|
403 | Description |
404 | Transfer the intercompany transaction to |
405 | AR interface table: AR_INTERFACE_LINES_ALL |
406 | |
407 | |
408 | |
409 | |
410 |-----------------------------------------------------*/
411
412
413
414 PROCEDURE TRANSFER_AR (itemtype IN VARCHAR2,
418 resultout OUT NOCOPY VARCHAR2)
415 itemkey IN VARCHAR2,
416 actid IN NUMBER,
417 funcmode IN VARCHAR2,
419
420 IS
421 l_batch_id NUMBER;
422 l_trx_id NUMBER;
423 l_recipient_id NUMBER;
424 l_trx_type_id Number;
425 l_line AR_Interface_line;
426 l_dist_line AR_Interface_Dist_line;
427 l_ou_id Number;
428 l_ledger_id Number;
429 l_le_id Number;
430 l_ap_ou_id Number;
431 l_ap_le_id Number;
432 l_initiator_id Number;
433 l_success boolean;
434 l_return_status Varchar2(1);
435 l_customer_id Number;
436 l_address_id Number;
437 l_site_use_id Number;
438 l_ar_trx_type_id Number;
439 l_ar_memo_line_id Number;
440 l_ar_trx_type_name RA_CUST_TRX_TYPES_ALL.name%TYPE; -- <bug 3450031>
441 l_ar_memo_line_name AR_MEMO_LINES_ALL_VL.name%TYPE; -- <bug 3450031>
442 l_default_term_id NUMBER;
443 l_message_count NUMBER;
444 l_message_data Varchar2(1000);
445 l_count Number:=0;
446 x_msg_data varchar2(1000);-- for the message returned form get_customer
447 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
448 l_event_key VARCHAR2(240);
449 -- added for bug: 7271703
450 l_batch_num varchar2(15);
451
452 -- Cursor to retrieve the line information
453 CURSOR c_line (p_batch_id IN Number,
454 p_trx_id IN Number) IS
455 SELECT decode(ftl.init_amount_cr,
456 0, ftl.init_amount_dr,
457 NULL, ftl.init_amount_dr,
458 ftl.init_amount_cr * (-1)),
459 ftl.line_id
460 FROM FUN_TRX_LINES ftl
461 WHERE p_trx_id = ftl.trx_id;
462
463 -- cursor to retrieve the initiator and recipient party / LE ID
464 -- Bug 7271703. also fetching batch number in cursor c_info
465
466 cursor c_info(p_trx_id IN Number) IS
467 SELECT ftb.batch_number,
468 ftb.initiator_id,
469 ftb.from_le_id,
470 ftb.from_ledger_id,
471 fth.recipient_id,
472 fth.to_le_id,
473 ftb.trx_type_id,
474 -- fth.ledger_id,
475 ftb.exchange_rate_type,
476 ftb.currency_code,
477 ftb.description,
478 ftb.gl_date,
479 ftb.batch_id,
480 fth.trx_id,
481 ftb.from_ledger_id,
482 ftb.batch_date
483 FROM FUN_TRX_BATCHES ftb,
484 FUN_TRX_HEADERS fth
485 WHERE fth.trx_id=p_trx_id
486 AND fth.batch_id=ftb.batch_id
487 AND fth.status='APPROVED';
488
489 -- cursor to retrieve the distribution information
490
491 CURSOR c_dist (p_trx_id IN Number) IS
492 SELECT
493 DECODE(FDL.dist_type_flag, 'L',
494 decode(fdl.amount_cr,
495 0, fdl.amount_dr * (-1),
496 NULL, fdl.amount_dr * (-1),
497 fdl.amount_cr),
498 'R', NULL,
499 NULL), -- <bug 3450031>
500 DECODE(FDL.dist_type_flag, 'L', NULL,
501 'R', 100,
502 NULL), -- <bug 3450031>
503 DECODE(FDL.dist_type_flag, 'R', 'REC',
504 'L', 'REV',
505 NULL), -- <bug 3450031>
506 fdl.ccid,
507 fth.batch_id,
508 fth.trx_id,
509 ftl.line_id
510 FROM FUN_TRX_HEADERS fth,
511 FUN_TRX_LINES ftl,
512 FUN_DIST_LINES fdl
513 WHERE fth.trx_id=p_trx_id
514 AND ftl.trx_id=fth.trx_id
515 AND ftl.line_id=fdl.line_id
516 AND fdl.party_type_flag='I';
517
518 BEGIN
519
520 if(funcmode='RUN') then
521
522 -- get the batch_id from the item attributes
523
524 l_batch_id := wf_engine.getitemattrnumber(itemtype,
525 itemkey,
526 'BATCH_ID');
527
528 l_trx_id := wf_engine.getitemattrnumber(itemtype,
529 itemkey,
530 'TRX_ID');
531 WF_EVENT.AddParameterToList(p_name=>'BATCH_ID',
532 p_value=>to_char(l_batch_id),
533 p_parameterlist =>l_parameter_list
534 );
535
536
537 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
538 p_value=>to_char(l_trx_id),
539 p_parameterlist =>l_parameter_list
540 );
541
542
543 wf_engine.setitemattrtext(itemtype,
544 itemkey,
548 -- set the generic variables
545 'ERROR_MSG',
546 'Number of Lines' || l_count || 'Trx:' || l_trx_id || 'Test1');
547
549
550 open c_info(l_trx_id);
551 LOOP
552 fetch c_info INTO
553 l_batch_num,
554 l_initiator_id,
555 l_le_id,
556 l_ledger_id,
557 l_recipient_id,
558 l_ap_le_id,
559 l_trx_type_id,
560 l_line.conversion_type,
561 l_line.currency_code,
562 l_line.description,
563 l_line.gl_date,
564 l_line.interface_line_attribute1,
565 l_line.interface_line_attribute2,
566 l_line.set_of_books_id,
567 l_line.trx_date;
568 exit when c_info%NOTFOUND;
569 END LOOP;
570 CLOSE c_info;
571
572 wf_engine.setitemattrtext(itemtype,
573 itemkey,
574 'ERROR_MSG',
575 'Number of Lines' || l_count || 'init:' || l_initiator_id || 'Test2');
576
577 -- Obtain the OU id
578
579 l_ou_id :=FUN_TCA_PKG.get_ou_id(l_initiator_id);
580 l_ap_ou_id :=FUN_TCA_PKG.get_ou_id(l_recipient_id);
581
582 if((l_ou_id is NULL) OR (l_ap_ou_id is NULL)) then
583 raise FND_API.G_EXC_UNEXPECTED_ERROR;
584 END IF;
585
586
587 -- Transfer to table RA_INTERFACE_LINES
588
589
590 open c_line(l_batch_id, l_trx_id);
591
592
593 LOOP
594
595 -- Amounts Transferred to AR should be
596 -- Init Trx Amount: 1000 Cr, AR Amount: -1000
597 -- Init Trx Amount: -1000 Cr, AR Amount: 1000
598 -- Init Trx Amount: 1000 Dr, AR Amount: 1000
599 -- Init Trx Amount: -1000 Dr, AR Amount: -1000
600
601 FETCH c_line INTO
602 l_line.amount,
603 l_line.interface_line_attribute3;
604
605 EXIT WHEN c_line%NOTFOUND;
606 l_line.org_id:=l_ou_id;
607 --added this for gscc warning
608 l_line.BATCH_SOURCE_NAME:= 'Global Intercompany';
609 l_line.INTERFACE_LINE_CONTEXT:='INTERNAL_ALLOCATIONS';
610 l_line.LINE_TYPE:='LINE';
611 l_line.UOM_NAME :='Each';
612 l_count:=l_count+1;
613 -- obtain the AR transaction type / memo_line
614
615
616 -- <bug 3450031>
617 FUN_TRX_TYPES_PUB.get_trx_type_map (
618 p_org_id => l_ou_id,
619 p_trx_type_id => l_trx_type_id,
620 p_trx_date => l_line.trx_date,
621 x_memo_line_id => l_ar_memo_line_id,
622 x_memo_line_name => l_ar_memo_line_name,
623 x_ar_trx_type_id => l_ar_trx_type_id,
624 x_ar_trx_type_name => l_ar_trx_type_name,
625 x_default_term_id => l_default_term_id);
626
627
628 IF ((l_ar_memo_line_name IS NULL)
629 OR (l_ar_trx_type_name IS NULL)) THEN
630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
631 END IF;
632 -- <bug 3450031 end>
633
634 -- Obtain the customer_id and address_id
635
636 -- <bug 3450031>
637 -- Change the passin parameters
638 l_success :=
639 FUN_TRADING_RELATION.get_customer(
640 p_source => 'INTERCOMPANY',
641 p_trans_le_id => l_ap_le_id,
642 p_tp_le_id => l_le_id,
643 p_trans_org_id => l_ap_ou_id,
644 p_tp_org_id => l_ou_id,
645 p_trans_organization_id => l_recipient_id,
646 p_tp_organization_id => l_initiator_id,
647 x_msg_data => x_msg_data,
648 x_cust_acct_id => l_customer_id,
649 x_cust_acct_site_id => l_address_id,
650 x_site_use_id => l_site_use_id);
651
652 if((l_success<>true) OR (l_customer_id is NULL)
653 OR (l_address_id is NULL)) then
654 raise FND_API.G_EXC_UNEXPECTED_ERROR;
655 END IF;
656
657 l_line.orig_system_bill_customer_id:=l_customer_id;
658 l_line.orig_system_bill_address_id :=l_address_id;
659
660 -- insert the line into the AR interface table
661 wf_engine.setitemattrtext(itemtype,
662 itemkey,
663 'ERROR_MSG',
664 'SOURCE:' || l_line.batch_source_name
665 || 'LINE TYPE: ' || l_line.line_type
666 || 'DESCRIPTION:' || l_line.description
667 || 'CURRENCY_CODE:' || l_line.currency_code
668 || 'Set_of_Book:' || l_line.set_of_books_id
669 || 'Conversion Type:' || l_line.conversion_type
670 || 'Test2.5');
671
672
673 -- Bug: 6788142 Added PRIMARY_SALESREP_ID field in the insert query.
674 -- insert into AR Interface table
675 -- Bug: 7271703 populating the INTERFACE_LINE_ATTRIBUTE4 column
676 -- with the batch_number.
677 INSERT INTO RA_INTERFACE_LINES_ALL
678 (
679 AMOUNT,
680 BATCH_SOURCE_NAME,
681 CONVERSION_TYPE,
682 CURRENCY_CODE,
683 CUST_TRX_TYPE_ID, -- <bug 3450031>
684 CUST_TRX_TYPE_NAME, -- <bug 3450031>
685 DESCRIPTION,
686 GL_DATE,
687 INTERFACE_LINE_ATTRIBUTE1,
688 INTERFACE_LINE_ATTRIBUTE2,
692 LINE_TYPE,
689 INTERFACE_LINE_ATTRIBUTE3,
690 INTERFACE_LINE_ATTRIBUTE4,
691 INTERFACE_LINE_CONTEXT,
693 MEMO_LINE_ID, -- <bug 3450031>
694 MEMO_LINE_NAME, -- <bug 3450031>
695 ORG_ID,
696 ORIG_SYSTEM_BILL_ADDRESS_ID,
697 ORIG_SYSTEM_BILL_CUSTOMER_ID,
698 SET_OF_BOOKS_ID,
699 TRX_DATE,
700 UOM_NAME,
701 TAXABLE_FLAG,
702 TERM_ID,
703 LEGAL_ENTITY_ID,
704 SOURCE_EVENT_CLASS_CODE,
705 PRIMARY_SALESREP_ID
706 )
707 VALUES
708 (
709 l_line.AMOUNT,
710 l_line.BATCH_SOURCE_NAME,
711 l_line.CONVERSION_TYPE,
712 l_line.CURRENCY_CODE,
713 l_ar_trx_type_id,
714 l_ar_trx_type_name, -- <bug 3450031>
715 NVL(l_line.DESCRIPTION,
716 'Transactions from Global Intercompany'), -- <bug 3450031>
717 l_line.GL_DATE,
718 l_line.INTERFACE_LINE_ATTRIBUTE1,
719 l_line.INTERFACE_LINE_ATTRIBUTE2,
720 l_line.INTERFACE_LINE_ATTRIBUTE3,
721 l_batch_num,
722 l_line.INTERFACE_LINE_CONTEXT,
723 l_line.LINE_TYPE,
724 l_ar_memo_line_id,
725 l_ar_memo_line_name, -- <bug 3450031>
726 l_line.ORG_ID,
727 l_line.ORIG_SYSTEM_BILL_ADDRESS_ID,
728 l_line.ORIG_SYSTEM_BILL_CUSTOMER_ID,
729 l_line.SET_OF_BOOKS_ID,
730 l_line.TRX_DATE,
731 l_line.UOM_NAME,
732 'S',
733 l_default_term_id,
734 l_le_id,
735 'INTERCOMPANY_TRX',
736 '-3'
737 );
738
739 -- Bug No. 6788142. Inserting into RA_INTERFACE_SALESCREDITS_ALL table
740
741 INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
742 (
743 INTERFACE_LINE_CONTEXT ,
744 INTERFACE_LINE_ATTRIBUTE1 ,
745 INTERFACE_LINE_ATTRIBUTE2 ,
746 INTERFACE_LINE_ATTRIBUTE3 ,
747 INTERFACE_LINE_ATTRIBUTE4 ,
748 INTERFACE_LINE_ATTRIBUTE5 ,
749 INTERFACE_LINE_ATTRIBUTE6 ,
750 INTERFACE_LINE_ATTRIBUTE7 ,
751 INTERFACE_LINE_ATTRIBUTE8 ,
752 INTERFACE_LINE_ATTRIBUTE9 ,
753 INTERFACE_LINE_ATTRIBUTE10 ,
754 INTERFACE_LINE_ATTRIBUTE11 ,
755 INTERFACE_LINE_ATTRIBUTE12 ,
756 INTERFACE_LINE_ATTRIBUTE13 ,
757 INTERFACE_LINE_ATTRIBUTE14 ,
758 INTERFACE_LINE_ATTRIBUTE15,
759 SALES_CREDIT_PERCENT_SPLIT,
760 SALES_CREDIT_TYPE_ID,
761 SALESREP_ID,
762 ORG_ID
763 )
764 VALUES
765 (
766 l_line.INTERFACE_LINE_CONTEXT,
767 l_line.INTERFACE_LINE_ATTRIBUTE1,
768 l_line.INTERFACE_LINE_ATTRIBUTE2,
769 l_line.INTERFACE_LINE_ATTRIBUTE3,
770 NULL,
771 NULL,
772 NULL,
773 NULL,
774 NULL,
775 NULL,
776 NULL,
777 NULL,
778 NULL,
779 NULL,
780 NULL,
781 NULL,
782 '100',
783 '1',
784 '-3',
785 l_line.ORG_ID
786 );
787
788 wf_engine.setitemattrtext(itemtype,
789 itemkey,
790 'ERROR_MSG',
791 'Number of Lines: After Insert' || l_count || 'ORG:' || l_line.org_id || 'Test2.75');
792
793 END LOOP;
794
795 close c_line;
796
797 wf_engine.setitemattrtext(itemtype,
798 itemkey,
799 'ERROR_MSG',
800 'Number of Lines' || l_count || 'Test3');
801
802
803 -- Insert into the AR distrubution table
804 -- Amounts Transferred to AR should be
805 -- Ini Dst Amount: 1000 Dr, AR Amount: -1000
806 -- Ini Dst Amount: -1000 Dr, AR Amount: 1000
807 -- Ini Dst Amount: 1000 Cr, AR Amount: 1000
808 -- Ini Dst Amount: -1000 Cr, AR Amount: -1000
809
810 open c_dist(l_trx_id);
811 LOOP
812 FETCH c_dist INTO
813 l_dist_line.AMOUNT,
814 l_dist_line.percent, --<bug 3450031>
815 l_dist_line.account_class, -- <bug 3450031>
816 l_dist_line.CODE_COMBINATION_ID,
817 l_dist_line.INTERFACE_LINE_ATTRIBUTE1,
818 l_dist_line.INTERFACE_LINE_ATTRIBUTE2,
819 l_dist_line.INTERFACE_LINE_ATTRIBUTE3;
820
821 exit when c_dist%NOTFOUND;
822 l_dist_line.ORG_ID :=l_ou_id;
823 l_dist_line.INTERFACE_LINE_CONTEXT:='INTERNAL_ALLOCATIONS';
824 -- Insert the value into the distribution table
825
826
827 INSERT INTO RA_INTERFACE_DISTRIBUTIONS_ALL --<bug 3450031>
828 (
829 ACCOUNT_CLASS,
830 AMOUNT,
831 percent, --<bug 3450031>
832 CODE_COMBINATION_ID,
833 INTERFACE_LINE_ATTRIBUTE1,
834 INTERFACE_LINE_ATTRIBUTE2,
835 INTERFACE_LINE_ATTRIBUTE3,
836 INTERFACE_LINE_CONTEXT,
837 ORG_ID
838 )
839 VALUES
840 (
841 l_dist_line.ACCOUNT_CLASS,
842 l_dist_line.AMOUNT,
843 l_dist_line.percent, --<bug 3450031>
844 l_dist_line.CODE_COMBINATION_ID,
845 l_dist_line.INTERFACE_LINE_ATTRIBUTE1,
846 l_dist_line.INTERFACE_LINE_ATTRIBUTE2,
847 l_dist_line.INTERFACE_LINE_ATTRIBUTE3,
848 l_dist_line.INTERFACE_LINE_CONTEXT,
849 l_dist_line.ORG_ID
850 );
851
852 END LOOP;
853 CLOSE c_dist;
854
855 -- <bug 3450031>
856 -- uncomment update status logic
857 -- Update the status
858
859 FUN_TRX_PVT.update_trx_status(p_api_version =>1.0,
860 x_return_status =>l_return_status,
861 x_msg_count => l_message_count,
862 x_msg_data => l_message_data,
863 p_trx_id => l_trx_id,
864 p_update_status_to => 'XFER_AR');
865
866 -- Handle the API call return
867
868 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
869
870 raise FND_API.G_EXC_ERROR;
871 END IF;
872
873
874 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
875
876 raise FND_API.G_EXC_UNEXPECTED_ERROR;
877 END IF;
878
879
880
881 -- Do we need commit? No
882 /*
883 wf_engine.setitemattrtext(itemtype,
884 itemkey,
885 'ERROR_MSG',
886 'Number of Lines' || l_count || 'Trx:' || l_trx_id || 'Test');
887 */
888
889
890 COMMIT;
891 resultout := 'COMPLETE';
892 return;
893
894
895 END IF; -- end of the run mode
896
897
898 -- Cancel mode
899
900 IF (funcmode = 'CANCEL') THEN
901
902 -- extra cancel code goes here
903
904 null;
905
906 -- no result needed
907 resultout := 'COMPLETE';
908 return;
909 END IF;
910
911
912 EXCEPTION
913
914
915
916 WHEN OTHERS THEN
917 -- Rcords this function call in the error system
918 -- in the case of an exception.
919 wf_core.context('FUN_INITIATOR_WF_PKG', 'TRANSFER_AR',
920 itemtype, itemkey, to_char(actid), funcmode);
921
922 END TRANSFER_AR;
923
924
925
926
927 /*-----------------------------------------------------|
928 | PROCEDURE CHECK_AR_SETUP |
929 |------------------------------------------------------|
930 | Parameters item_type IN Varchar2 |
931 | item_key IN Varchar2 |
932 | actid IN NUMBER |
933 | funcmode IN Varchar2 |
934 | resultout IN Varchar2 |
935 | |
936 |------------------------------------------------------|
937 | Description |
938 | Check the AR setup information |
939 | |
940 | Return true/false |
941 | |
942 | It will call intercompany transaction API |
943 | to check AR related setup information |
944 |-----------------------------------------------------*/
945
946
947 PROCEDURE ChECK_AR_SETUP (itemtype IN VARCHAR2,
948 itemkey IN VARCHAR2,
949 actid IN NUMBER,
950 funcmode IN VARCHAR2,
954
951 resultout OUT NOCOPY VARCHAR2)
952
953 IS
955 l_message_count Number;
956 l_status Varchar2(1);
957 l_msg_data Varchar2(2000);
958 l_message Varchar2(2000);
959 l_trx_amt Number;
960 l_trx_id Number;
961 l_batch_id Number;
962
963 BEGIN
964
965 IF (funcmode = 'RUN') THEN
966
967
968 l_status:=FND_API.G_RET_STS_SUCCESS;
969
970 l_trx_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
971 itemkey => itemkey,
972 aname => 'TRX_ID');
973
974 l_batch_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
975 itemkey => itemkey,
976 aname => 'BATCH_ID');
977
978 -- call transaction API to check the ar setup
979 fun_trx_pvt.ar_transfer_validate (
980 p_api_version => 1.0,
981 p_init_msg_list => FND_API.G_TRUE,
982 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
983 x_return_status => l_status,
984 x_msg_count => l_message_count,
985 x_msg_data => l_msg_data,
986 p_batch_id => l_batch_id,
987 p_trx_id => l_trx_id);
988
989 -- Bug: 7319371 Changing the query to make the l_trx_amt number reginal independent
990 -- Also since the fun_trx_batches table is not used in the query removing a reference to the same
991
992 --select ltrim(to_char(decode(nvl(h.reci_amount_cr,0),0,h.reci_amount_dr,h.reci_amount_cr),'999999999.99'))
993 --into l_trx_amt
994 --from fun_trx_headers h, fun_trx_batches b
995 --where h.trx_id = l_trx_id
996 --and b.batch_id = l_batch_id;
997
998 SELECT LTRIM(TO_CHAR(DECODE(NVL(H.RECI_AMOUNT_CR,0),0,H.RECI_AMOUNT_DR,
999 H.RECI_AMOUNT_CR),
1000 '999999999D99'))
1001 INTO l_trx_amt
1002 FROM FUN_TRX_HEADERS H
1003 WHERE H.TRX_ID = l_trx_id;
1004
1005 -- Bug: 7319371 END
1006
1007
1008 wf_engine.setitemattrnumber( itemtype => itemtype,
1009 itemkey => itemkey,
1010 aname => 'TRX_AMT',
1011 avalue => l_trx_amt);
1012
1013 -- Check the return status
1014 if(l_status <> FND_API.G_RET_STS_SUCCESS) THEN
1015
1016 -- assembling the error message
1017
1018 l_message :=FUN_WF_COMMON.concat_msg_stack(l_message_count);
1019
1020 -- set the item attribute ERROR_MSG
1021
1022 wf_engine.setitemattrtext(itemtype,
1023 itemkey,
1024 'ERROR_MSG',
1025 l_message);
1026 -- return false
1027 resultout:='COMPLETE:F';
1028 ELSE
1029 -- AR setup is vcalid
1030 resultout := 'COMPLETE:T';
1031 return;
1032 END IF;
1033 END IF;
1034
1035 EXCEPTION
1036
1037 WHEN OTHERS THEN
1038
1039 -- Rcords this function call in the error system
1040 -- in the case of an exception.
1041
1042 wf_core.context('FUN_INITIATOR_WF_PKG', 'CHECK_AR_SETUP',
1043 itemtype, itemkey, to_char(actid), funcmode);
1044
1045 RAISE;
1046
1047 END CHECK_AR_SETUP;
1048
1049
1050
1051
1052 /*-----------------------------------------------------|
1053 | PROCEDURE GET_INVOICE |
1054 |------------------------------------------------------|
1055 | Parameters p_subscription_guid IN RAW |
1056 | p_event IN OUT |
1057 | WF_EVENT_T |
1058 | |
1059 | Return Varchar2 |
1060 | |
1061 |------------------------------------------------------|
1062 | Description |
1063 | This is the event subscription function |
1064 | for AR Autoinvoice transfer event: |
1065 | oracle.apps.ar.batch.AutoInvoice.run |
1066 | to check AR related setup information |
1067 | |
1068 | It will retrieve AR invoice from AR table |
1069 | and then raise AR transfer complete event |
1070 |-----------------------------------------------------*/
1071
1072
1073 FUNCTION GET_INVOICE (p_subscription_guid IN RAW,
1074 p_event IN OUT NOCOPY WF_EVENT_T)
1075 return Varchar2 IS
1076
1077 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1078 l_parameter_list_out wf_parameter_list_t := wf_parameter_list_t();
1079 l_parameter_t wf_parameter_t;
1080 l_parameter_name l_parameter_t.name%type;
1081 l_parameter_value l_parameter_t.value%type;
1082
1083 l_request_id Number;
1084 i PLS_Integer;
1085 l_batch_source_id Number;
1086 l_batch_id Number;
1090 l_message_data Varchar2(1000);
1087 l_trx_id Number;
1088 l_return_status Varchar2(1);
1089 l_message_count NUMBER;
1091 l_event_key Varchar2(240);
1092
1093 -- cursor to retrieve all imported transactions
1094 cursor c_trans(p_request_id IN NUMBER) IS
1095
1096 SELECT rct.trx_number invoice_number,
1097 rct.interface_header_attribute1 batch_id,
1098 rct.interface_header_attribute2 trx_id
1099 FROM ra_customer_trx_all rct,
1100 ra_batch_sources_all rbs
1101 WHERE rct.request_id=p_request_id
1102 AND rct.batch_source_id=rbs.batch_source_id
1103 AND NVL(rct.org_id, -99) =NVL(rbs.org_id, -99)
1104 AND rbs.name='Global Intercompany';
1105
1106
1107 BEGIN
1108 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
1109
1110 -- Retieve the transaction number from AR table (note we are working with
1111 -- ALL table now)
1112
1113 -- We need raise individual event for each transaction
1114 -- (assume the import is successful)
1115
1116 FOR l_trans IN c_trans(l_request_id)
1117 LOOP
1118 -- Update the AR invoice number
1119
1120 UPDATE FUN_TRX_HEADERS
1121 SET ar_invoice_number = l_trans.invoice_number
1122 WHERE trx_id = l_trans.trx_id
1123 AND batch_id = l_trans.batch_id
1124 AND ar_invoice_number IS NULL;
1125
1126
1127 IF SQL%ROWCOUNT = 1
1128 THEN
1129 -- Raise the AR transfer complete event
1130
1131 -- Initiate the parameter list
1132 l_parameter_list_out :=wf_parameter_list_t();
1133
1134 WF_EVENT.AddParameterToList(p_name=>'BATCH_ID',
1135 p_value=>TO_CHAR(l_trans.batch_id),
1136 p_parameterlist=>l_parameter_list_out);
1137
1138 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
1139 p_value=>TO_CHAR(l_trans.trx_id),
1140 p_parameterlist=>l_parameter_list_out);
1141
1142 WF_EVENT.AddParameterToList(p_name=>'INVOICE_NUM',
1143 p_value=>TO_CHAR(l_trans.invoice_number),
1144 p_parameterlist=>l_parameter_list_out);
1145
1146 -- generate the event key
1147
1148 l_event_key := FUN_INITIATOR_WF_PKG.GENERATE_KEY
1149 (p_batch_id=>l_trans.batch_id,
1150 p_trx_id=>l_trans.trx_id);
1151
1152
1153 -- Raise the event
1154
1155 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.manualtrx.arcomplete.send',
1156 p_event_key =>l_event_key,
1157 p_parameters =>l_parameter_list_out);
1158
1159 l_parameter_list_out.delete();
1160
1161
1162 END IF;
1163
1164 COMMIT;
1165
1166 END LOOP;
1167
1168 RETURN 'SUCCESS';
1169
1170 EXCEPTION
1171
1172 WHEN OTHERS THEN
1173
1174 -- Rcords this function call in the error system
1175 -- in the case of an exception.
1176
1177 wf_core.context('FUN_INITIATOR_WF_PKG', 'GET_INVOICE',
1178 p_event.getEventName(), p_subscription_guid);
1179 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1180 return 'ERROR';
1181
1182
1183 END GET_INVOICE;
1184
1185
1186
1187
1188 /*-----------------------------------------------------|
1189 | PROCEDURE GENERATE_KEY |
1190 |------------------------------------------------------|
1191 | Parameters p_trx_id IN NUMBER |
1192 | |
1193 | Return Varchar2 |
1194 | |
1195 |------------------------------------------------------|
1196 | Description |
1197 | Generate the key for events |
1198 |-----------------------------------------------------*/
1199
1200
1201
1202
1203
1204 FUNCTION GENERATE_KEY( p_batch_id in NUMBER,
1205 p_trx_id in NUMBER) return Varchar2
1206 IS
1207
1208 BEGIN
1209 return to_char(p_batch_id) || '_' || to_char(p_trx_id) || '_' || SYS_GUID();
1210
1211 EXCEPTION
1212
1213 WHEN OTHERS THEN
1214 RAISE;
1215
1216
1217 END GENERATE_KEY;
1218
1219 -------------------------------------------------------------------------------
1220 --Start of Comments
1221 --Function:
1222 -- After transactions are interfaced to AR and invoices have been created,
1223 -- FUN_TRX_HEADERS.ar_invoice_number will be updated with the corresonding
1224 -- AR_CUSTOMER_TRX_ALL.trx_number
1225
1226 -- THIS PROCESS IS NOT USED ANYMORE
1227 -- INSTEAD GET_INVOICE IS USED
1228
1229 --End of Comments
1230 -------------------------------------------------------------------------------
1231
1232 PROCEDURE update_trx_headers(p_request_id IN NUMBER)
1233 IS
1237
1234 l_trx_header_type fun_trx_header_type;
1235
1236 BEGIN
1238
1239 NULL;
1240
1241 /* NOT USED ANYMORE
1242
1243 -- Get AR invoice number given autoinvoice conc. request id
1244 SELECT TO_NUMBER(RCT.interface_header_attribute1),
1245 TO_NUMBER(RCT.interface_header_attribute2),
1246 RCT.trx_number
1247 BULK COLLECT INTO
1248 l_trx_header_type.attribute1,
1249 l_trx_header_type.attribute2,
1250 l_trx_header_type.invoice_number
1251 FROM RA_CUSTOMER_TRX_ALL RCT,
1252 RA_BATCH_SOURCES_ALL RBS
1253 WHERE RBS.name = 'Global Intercompany'
1254 AND RBS.batch_source_id = RCT.batch_source_id
1255 AND RBS.org_id = RCT.org_id
1256 AND RCT.request_id = p_request_id;
1257
1258 IF SQL%NOTFOUND THEN
1259 RETURN;
1260 END IF;
1261
1262 -- update FUN_TRX_HEADERS with the corresponding invoice number
1263 FORALL i IN 1..l_trx_header_type.attribute1.COUNT
1264 UPDATE FUN_TRX_HEADERS
1265 SET ar_invoice_number = l_trx_header_type.invoice_number(i)
1266 WHERE batch_id = l_trx_header_type.attribute1(i)
1267 AND trx_id = l_trx_header_type.attribute2(i);
1268
1269 */
1270
1271
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 NULL;
1275 END update_trx_headers;
1276
1277
1278 ---------------------------------------------------------------------------
1279 --Start of Comments
1280 --Function:
1281 -- After transactions are interfaced to AR and invoices have been created,
1282 -- it will get AR invoice number based on autoinvice conc. program
1283 -- request_id, and then FUN_TRX_HEADERS.ar_invoice_number will be updated
1284 -- with the corresonding AR invoice number
1285
1286 -- THIS PROCESS IS NOT USED ANYMORE
1287 -- INSTEAD, GET_INVOICE IS CALLED FROM AR BUSINESS EVENT
1288
1289 --End of Comments
1290 ---------------------------------------------------------------------------
1291
1292
1293 PROCEDURE post_ar_invoice(
1294 itemtype IN varchar2,
1295 itemkey IN varchar2,
1296 actid IN number,
1297 funcmode IN varchar2,
1298 resultout IN OUT NOCOPY varchar2)
1299 IS
1300 l_request_id NUMBER;
1301
1302 BEGIN
1303
1304 /*
1305 IF (funcmode = 'RUN') THEN
1306 fnd_msg_pub.initialize;
1307
1308 l_request_id := wf_engine.GetItemAttrNumber(
1309 itemtype => itemtype,
1310 itemkey => itemkey,
1311 aname => 'REQUEST_ID');
1312
1313 update_trx_headers(l_request_id);
1314
1315 commit;
1316 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
1317
1318 RETURN;
1319 END IF;
1320 */
1321
1322 resultout := wf_engine.eng_null;
1323 RETURN;
1324
1325 EXCEPTION
1326 WHEN OTHERS THEN
1327 wf_core.context('FUN_INITIATOR_WF_PKG', 'post_ar_invoice',
1328 itemtype, itemkey, TO_CHAR(actid), funcmode);
1329
1330 RAISE;
1331 END post_ar_invoice;
1332
1333
1334 END FUN_INITIATOR_WF_PKG;
1335