[Home] [Help]
PACKAGE BODY: APPS.XNB_CMN_PVT
Source
1 PACKAGE BODY XNB_CMN_PVT AS
2 /* $Header: XNBVCMNB.pls 120.18 2006/11/20 05:40:50 pselvam noship $ */
3
4 --Private Constants defined for this package.
5 --g_party_type: The XML Gateway Trading Partner Type is assumed to be 'C'-Customer here
6 g_party_type CONSTANT CHAR(1) NOT NULL DEFAULT 'C';
7
8 --XML Gateway Transaction Type for XNB
9 g_xnb_transation_type CONSTANT CHAR(3) NOT NULL DEFAULT 'XNB';
10 --XML Gateway Transaction Subtypes for the Various Messages
11 g_owner_change_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'OO';
12 g_item_update_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'IO';
13 -- g_account_add_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'AAO';
14 g_account_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'AO';
15 -- g_account_update_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'UAO';
16 g_salesorder_add_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'SOO';
17
18 g_grpso_add_txn_subtype CONSTANT VARCHAR2(5) NOT NULL DEFAULT 'GSOO';
19
20
21 /**** Function used to check whether the account message is to be published or not*/
22 /* This function is no longer used from TBI R12 : ksrikant*/
23
24
25 /*PROCEDURE check_acct_update_publish
26 (
27 itemtype IN VARCHAR2,
28 itemkey IN VARCHAR2,
29 actid IN NUMBER,
30 funcmode IN VARCHAR2,
31 resultout OUT NOCOPY VARCHAR2
32 )
33 AS
34 l_transaction_type VARCHAR2(15);
35 l_transaction_subtype VARCHAR2(10);
36 l_party_id NUMBER;
37 l_party_site_id NUMBER;
38 l_party_type VARCHAR2(30);
39 l_num NUMBER ;
40 l_event_name VARCHAR2(50);
41 l_doc_no VARCHAR2(30);
42 BEGIN
43
44 l_transaction_type := g_xnb_transation_type;
45 l_transaction_subtype := g_account_txn_subtype;
46 l_party_type := g_party_type;
47 l_num := 0;
48
49 l_event_name := wf_engine.getitemattrtext (
50 itemtype => itemtype,
51 itemkey => itemkey,
52 aname => 'ACCT_EVENT_NAME');
53
54 l_doc_no := wf_engine.getitemattrtext (
55 itemtype => itemtype,
56 itemkey => itemkey,
57 aname => 'ACCOUNT_NUMBER');
58
59 XNB_DEBUG.log('check_acct_update_publish',l_event_name);
60
61 IF l_event_name = 'oracle.apps.xnb.account.update' THEN
62
63 l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, 'XNB_ACCOUNT');
64
65 XNB_DEBUG.log('check_acct_update_publish',l_num);
66
67 IF l_num = 1 THEN
68 resultout := FND_API.G_TRUE;
69 END IF;
70
71 IF l_num = 2 THEN
72 resultout := FND_API.G_FALSE;
73 END IF;
74
75
76 END IF;
77
78 EXCEPTION
79 WHEN OTHERS THEN
80 RAISE_APPLICATION_ERROR (-20041, SQLERRM(SQLCODE));
81 resultout := 1;
82
83 END check_acct_update_publish; */
84
85 --Procedures defined by this packages
86 PROCEDURE set_item_attributes
87 (
88 itemtype IN VARCHAR2,
89 itemkey IN VARCHAR2,
90 actid IN NUMBER,
91 funcmode IN VARCHAR2,
92 resultout OUT NOCOPY VARCHAR2
93 )
94 AS
95
96 l_transaction_type VARCHAR2(15) ;
97 l_transaction_subtype VARCHAR2(10) ;
98 l_party_id NUMBER;
99 l_party_site_id NUMBER;
100 l_party_type VARCHAR2(30);
101 l_message_text VARCHAR2(100);
102 l_num NUMBER ;
103 l_doc_no NUMBER;
104 l_event_key VARCHAR2(100);
105 BEGIN
106
107 l_transaction_type := g_xnb_transation_type;
108 l_transaction_subtype := g_item_update_txn_subtype;
109 l_party_type := g_party_type;
110
111 l_num := 0;
112 ---------------------------------------------------------------------------------------
113 --Get the party details
114 --
115 ---------------------------------------------------------------------------------------
116 BEGIN
117
118 SELECT party_id,
119 party_site_id
120 INTO l_party_id,
121 l_party_site_id
122 FROM ecx_oag_controlarea_tp_v
123 WHERE transaction_type = l_transaction_type
124 AND transaction_subtype = l_transaction_subtype
125 AND party_type = l_party_type;
126
127 EXCEPTION
128
129 WHEN NO_DATA_FOUND THEN
130 RAISE_APPLICATION_ERROR (-20140, 'Party Information is Missing. Please check the Trading Partner Setup in XML Gateway');
131 resultout := 1;
132
133 WHEN OTHERS THEN
134 RAISE_APPLICATION_ERROR (-20041, SQLERRM(SQLCODE));
135 resultout := 1;
136 END;
137
138
139 ---------------------------------------------------------------------------------------
140 --Set all the required attributes
141 --
142 ---------------------------------------------------------------------------------------
143
144 wf_engine.setitemattrtext (
145 itemtype,
146 itemkey,
147 'ECX_TRANSACTION_TYPE',
148 l_transaction_type);
149
150 wf_engine.setitemattrtext (
151 itemtype,
152 itemkey,
153 'ECX_TRANSACTION_SUBTYPE',
154 l_transaction_subtype);
155
156 wf_engine.setitemattrtext (
157 itemtype,
158 itemkey,
159 'ECX_PARTY_ID',
160 l_party_id);
161
162 wf_engine.setitemattrtext (
163 itemtype,
164 itemkey,
165 'ECX_PARTY_SITE_ID',
166 l_party_site_id);
167
168 wf_engine.setitemattrtext (
169 itemtype,
170 itemkey,
171 'ECX_PARTY_TYPE',
172 l_party_type);
173
174 wf_engine.setitemattrtext (
175 itemtype,
176 itemkey,
177 'ECX_SEND_MODE',
178 'SYNCH');
179
180 wf_engine.setitemattrnumber (
181 itemtype,
182 itemkey,
183 'ECX_DEBUG_LEVEL',
184 1);
185
186 l_event_key := 'XNB'||'_PUBLISH_ITEM_'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
187
188 wf_engine.setitemattrtext (
189 itemtype,
190 itemkey,
191 'XML_EVENT_KEY',
192 l_event_key);
193
194 /***** Item Publish Changes incorporated*/
195 /* */
196 /* */
197
198 /* l_doc_no := wf_engine.getitemattrtext (
199 itemtype => itemtype,
200 itemkey => itemkey,
201 aname => 'ITEM_ID');
202
203
204 ---------------------------------------------------------------------------------------
205 --Check to see if the collaboration already exists for the Item
206 --if l_num = 2 Collaboration doesn't exist so Create
207 --else Collaboration exists so Update
208 ---------------------------------------------------------------------------------------
209
210 l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, l_transaction_type, l_transaction_subtype);
211
212 IF l_num = 2 THEN
213 resultout := FND_API.G_FALSE;
214 RETURN ;
215 ELSE
216
217 ---------------------------------------------------------------------------------------
218 --Update the MESSAGE_TEXT to reflect the Update of Collaboration
219 --
220 ---------------------------------------------------------------------------------------
221
222 l_message_text := l_transaction_type||l_transaction_subtype||l_party_id||l_doc_no||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
223
224 wf_engine.setitemattrtext (
225 itemtype,
226 itemkey,
227 'MESSAGE_TEXT',
228 l_message_text);
229
230 wf_engine.setitemattrdate (
231 itemtype,
232 itemkey,
233 'LAST_UPDATE_DATE',
234 sysdate);
235 resultout := FND_API.G_TRUE;
236 RETURN ;
237 END IF; */
238
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 RAISE_APPLICATION_ERROR (-20041, SQLERRM(SQLCODE));
243 resultout := 1;
244
245
246 -- Enf of Function
247 END set_item_attributes;
248
249 PROCEDURE set_acct_update_attributes (
250 itemtype IN VARCHAR2,
251 itemkey IN VARCHAR2,
252 actid IN NUMBER,
253 funcmode IN VARCHAR2,
254 resultout OUT NOCOPY VARCHAR2
255 )
256 AS
257
258 l_transaction_type VARCHAR2(15) ;
259 l_transaction_subtype VARCHAR2(10) ;
260 l_party_id NUMBER;
261 l_party_site_id NUMBER;
262 l_party_type VARCHAR2(30) ;
263 l_message_text VARCHAR2(100);
264 l_num NUMBER ;
265 l_doc_no VARCHAR2(30);
266 l_org_id NUMBER;
267 l_cust_ac_id NUMBER;
268 l_temp NUMBER;
269 l_event_key VARCHAR2(100);
270 l_event_name VARCHAR2(50);
271 l_ref_id VARCHAR2(200);
272 BEGIN
273
274 l_transaction_type := g_xnb_transation_type;
275 l_transaction_subtype := g_account_txn_subtype;
276 l_party_type := g_party_type;
277 l_num := 0;
278
279 ---------------------------------------------------------------------------------------
280 -- Get the Account Number and the Organization Id
281 --
282 ---------------------------------------------------------------------------------------
283 l_doc_no := wf_engine.getitemattrtext (
284 itemtype => itemtype,
285 itemkey => itemkey,
286 aname => 'ACCOUNT_NUMBER');
287
288 l_org_id := wf_engine.getitemattrtext (
289 itemtype => itemtype,
290 itemkey => itemkey,
291 aname => 'ACCT_ORG_ID');
292
293 ---------------------------------------------------------------------------------------
294 --Set the Organization Id
295 --
296 ---------------------------------------------------------------------------------------
297 /* R12 MOAC UPTAKE : ksrikant*/
298
299 /*dbms_application_info.set_client_info(l_org_id);*/
300
301
302 /* Functionality Included based on the Bug 3882580*/
303 ---------------------------------------------------------------------------------------
304 -- Determine whether a primary Bill_to exists for the Account
305 --
306 ----------------------------------------------------------------------------------------
307
308 BEGIN
309
310 SELECT cust_account_id
311 INTO l_cust_ac_id
312 FROM hz_cust_accounts
313 WHERE account_number = l_doc_no;
314
315 SELECT cust_account_id
316 INTO l_temp
317 FROM xnb_primary_bill_to_addr_v
318 WHERE cust_account_id = l_cust_ac_id
319 AND org_id = l_org_id;
320
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323 RAISE_APPLICATION_ERROR (-20110, 'Primary Bill_To does not exist for the Account Chosen, Assign a Primary Bill_to and Retry');
324 resultout := 1;
325
329 END;
326 WHEN OTHERS THEN
327 RAISE_APPLICATION_ERROR (-20042, SQLERRM(SQLCODE));
328 resultout := 1;
330
331
332 ---------------------------------------------------------------------------------------
333 --Get the party details
334 --
335 ---------------------------------------------------------------------------------------
336 BEGIN
337
338 SELECT party_id,
339 party_site_id
340 INTO l_party_id,
341 l_party_site_id
342 FROM ecx_oag_controlarea_tp_v
343 WHERE transaction_type = l_transaction_type
344 AND transaction_subtype = l_transaction_subtype
345 AND party_type = l_party_type;
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 RAISE_APPLICATION_ERROR (-20140, 'Party Information is Missing. Please check the Trading Partner Setup in XML Gateway');
350 resultout := 1;
351
352 WHEN OTHERS THEN
353 RAISE_APPLICATION_ERROR (-20043, SQLERRM(SQLCODE));
354 resultout := 1;
355 END;
356
357 ---------------------------------------------------------------------------------------
358 --Set all the required attributes
359 --
360 ---------------------------------------------------------------------------------------
361
362 wf_engine.setitemattrtext (
363 itemtype,
364 itemkey,
365 'ECX_TRANSACTION_TYPE',
366 l_transaction_type);
367
368 wf_engine.setitemattrtext (
369 itemtype,
370 itemkey,
371 'ECX_TRANSACTION_SUBTYPE',
372 l_transaction_subtype);
373
374 wf_engine.setitemattrtext (
375 itemtype,
376 itemkey,
377 'ECX_PARTY_ID',
378 l_party_id);
379
380 wf_engine.setitemattrtext (
381 itemtype,
382 itemkey,
383 'ECX_PARTY_SITE_ID',
384 l_party_site_id);
385
386 wf_engine.setitemattrtext (
387 itemtype,
388 itemkey,
389 'ECX_PARTY_TYPE',
390 l_party_type);
391
392 wf_engine.setitemattrtext (
393 itemtype,
394 itemkey,
395 'ECX_SEND_MODE',
396 'SYNCH');
397
398 wf_engine.setitemattrnumber (
399 itemtype,
400 itemkey,
401 'ECX_DEBUG_LEVEL',
402 3);
403
404 ---------------------------------------------------------------------------------------
405 --Set the Reference Id for the Account
406 --
407 ---------------------------------------------------------------------------------------
408
409 l_event_name := 'oracle.apps.xnb.account.update';
410 l_event_key := 'XNB'||'PUBLISH_ACCOUNT'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
411 l_ref_id := 'LM0001:'||l_event_name||':'||l_event_key;
412
413 wf_engine.setitemattrtext (
414 itemtype,
415 itemkey,
416 'REFERENCE_ID',
417 l_ref_id);
418
419 wf_engine.setitemattrtext (
420 itemtype,
421 itemkey,
422 'XML_EVENT_KEY',
423 l_event_key);
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 RAISE_APPLICATION_ERROR (-20044, SQLERRM(SQLCODE));
428 resultout := 1;
429
430 END set_acct_update_attributes;
431
432
433
434 PROCEDURE set_acct_attributes (
435 itemtype IN VARCHAR2,
436 itemkey IN VARCHAR2,
437 actid IN NUMBER,
438 funcmode IN VARCHAR2,
439 resultout OUT NOCOPY VARCHAR2
440 )
441 AS
442
443 l_transaction_type VARCHAR2(15) ;
444 l_transaction_subtype VARCHAR2(10) ;
445 l_party_id NUMBER;
449 l_num NUMBER ;
446 l_party_site_id NUMBER;
447 l_party_type VARCHAR2(30) ;
448 l_message_text VARCHAR2(100);
450 l_doc_no VARCHAR2(10);
451 l_org_id NUMBER;
452 l_cust_ac_id NUMBER;
453 l_temp NUMBER;
454 l_event_key VARCHAR2(100);
455 l_event_name VARCHAR2(50);
456 l_ref_id VARCHAR2(200);
457 BEGIN
458
459 l_transaction_type := g_xnb_transation_type;
460 l_transaction_subtype := g_account_txn_subtype;
461 l_party_type := g_party_type;
462 l_num := 0;
463
464 ---------------------------------------------------------------------------------------
465 -- Get the Account Number and the Organization Id
466 --
467 ---------------------------------------------------------------------------------------
468 l_doc_no := wf_engine.getitemattrtext (
469 itemtype => itemtype,
470 itemkey => itemkey,
471 aname => 'ACCOUNT_NUMBER');
472
473 l_org_id := wf_engine.getitemattrtext (
474 itemtype => itemtype,
475 itemkey => itemkey,
476 aname => 'ACCT_ORG_ID');
477
478 ---------------------------------------------------------------------------------------
479 --Set the Organization Id
480 --
481 ---------------------------------------------------------------------------------------
482
483 /* R12 MOAC UPTAKE : ksrikant*/
484 /*dbms_application_info.set_client_info(l_org_id);*/
485
486
487 /* Functionality Included based on the Bug 3882580*/
488 ---------------------------------------------------------------------------------------
489 -- Determine whether a primary Bill_to exists for the Account
490 --
491 ----------------------------------------------------------------------------------------
492
493 BEGIN
494
495 SELECT cust_account_id
496 INTO l_cust_ac_id
497 FROM hz_cust_accounts
498 WHERE account_number = l_doc_no;
499
500 SELECT cust_account_id
501 INTO l_temp
502 FROM xnb_primary_bill_to_addr_v
503 WHERE cust_account_id = l_cust_ac_id
504 AND org_id = l_org_id;
505
506 EXCEPTION
507 WHEN NO_DATA_FOUND THEN
508 RAISE_APPLICATION_ERROR (-20110, 'Primary Bill_To does not exist for the Account Chosen, Assign a Primary Bill_to and Retry');
509 resultout := 1;
510
511 WHEN OTHERS THEN
512 RAISE_APPLICATION_ERROR (-20046, SQLERRM(SQLCODE));
513 resultout := 1;
514 END;
515
516
517 ---------------------------------------------------------------------------------------
518 --Get the party details
519 --
520 ---------------------------------------------------------------------------------------
521 BEGIN
522
523 SELECT party_id,
524 party_site_id
525 INTO l_party_id,
526 l_party_site_id
527 FROM ecx_oag_controlarea_tp_v
528 WHERE transaction_type = l_transaction_type
529 AND transaction_subtype = l_transaction_subtype
530 AND party_type = l_party_type;
531
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 RAISE_APPLICATION_ERROR (-20140, 'Party Information is Missing. Please check the Trading Partner Setup in XML Gateway');
535 resultout := 1;
536
537 WHEN OTHERS THEN
538 RAISE_APPLICATION_ERROR (-20047, SQLERRM(SQLCODE));
539 resultout := 1;
540 END;
541
542 ---------------------------------------------------------------------------------------
543 --Set all the required attributes
544 --
545 ---------------------------------------------------------------------------------------
546
547 wf_engine.setitemattrtext (
548 itemtype,
549 itemkey,
550 'ECX_TRANSACTION_TYPE',
551 l_transaction_type);
552
553 wf_engine.setitemattrtext (
554 itemtype,
555 itemkey,
556 'ECX_TRANSACTION_SUBTYPE',
557 l_transaction_subtype);
558
559 wf_engine.setitemattrtext (
560 itemtype,
561 itemkey,
562 'ECX_PARTY_ID',
563 l_party_id);
564
565 wf_engine.setitemattrtext (
566 itemtype,
567 itemkey,
568 'ECX_PARTY_SITE_ID',
569 l_party_site_id);
570
571 wf_engine.setitemattrtext (
572 itemtype,
573 itemkey,
574 'ECX_PARTY_TYPE',
575 l_party_type);
576
577 wf_engine.setitemattrtext (
578 itemtype,
579 itemkey,
580 'ECX_SEND_MODE',
581 'SYNCH');
582
583 wf_engine.setitemattrnumber (
584 itemtype,
585 itemkey,
586 'ECX_DEBUG_LEVEL',
587 1);
588
589 ---------------------------------------------------------------------------------------
590 --Set the Reference Id for the Account
591 --
592 ---------------------------------------------------------------------------------------
593
594 l_event_name := 'oracle.apps.xnb.account.create';
595 l_event_key := 'XNB'||'PUBLISH_ACCOUNT'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
596 l_ref_id := 'LM0001:'||l_event_name||':'||l_event_key;
597
598 wf_engine.setitemattrtext (
599 itemtype,
600 itemkey,
601 'REFERENCE_ID',
602 l_ref_id);
603
604 wf_engine.setitemattrtext (
605 itemtype,
609
606 itemkey,
607 'XML_EVENT_KEY',
608 l_event_key);
610
611
612 ---------------------------------------------------------------------------------------
613 --Check to see if the collaboration already exists for the Account
614 --if l_num = 2 Collaboration doesn't exist so Create
615 --else Collaboration exists so Update
616 ---------------------------------------------------------------------------------------
617
618 l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, 'XNB_ACCOUNT');
619
620 IF l_num = 2 THEN
621 resultout := FND_API.G_FALSE;
622 RETURN ;
623 ELSE
624 ---------------------------------------------------------------------------------------
625 --Update the MESSAGE_TEXT to reflect the Update of Collaboration
626 --
627 ---------------------------------------------------------------------------------------
628
629 l_message_text := l_transaction_type||l_transaction_subtype||l_party_id||l_doc_no||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
630
631 wf_engine.setitemattrtext (
632 itemtype,
633 itemkey,
634 'MESSAGE_TEXT',
635 l_message_text);
636
637 wf_engine.setitemattrdate (
638 itemtype,
639 itemkey,
640 'LAST_UPDATE_DATE',
641 SYSDATE);
642 resultout := FND_API.G_TRUE;
643 RETURN ;
644 END IF;
645
646 EXCEPTION
647 WHEN OTHERS THEN
648 RAISE_APPLICATION_ERROR (-20048, SQLERRM(SQLCODE));
649 resultout := 1;
650
651 --End of Function
652 END set_acct_attributes;
653
654 PROCEDURE set_sales_order_attributes (
655 itemtype IN VARCHAR2,
656 itemkey IN VARCHAR2,
657 actid IN NUMBER,
658 funcmode IN VARCHAR2,
659 resultout OUT NOCOPY VARCHAR2)
660 AS
661
662 l_transaction_type VARCHAR2(15) ;
663 l_transaction_subtype VARCHAR2(10) ;
664 l_party_id NUMBER ;
665 l_party_site_id NUMBER ;
666 l_party_type VARCHAR2(30) ;
667 /* l_message_text VARCHAR2(100); */
668 l_num VARCHAR2(30);
669 l_doc_no NUMBER;
670 l_order_num NUMBER;
671 l_org_id NUMBER;
672
673 /* INVALID_COLLAB_EXCEPTION EXCEPTION;*/
674
675 BEGIN
676
677 l_transaction_type := g_xnb_transation_type;
678 l_transaction_subtype := g_salesorder_add_txn_subtype;
679 l_party_type := g_party_type;
680 l_num := 0;
681
682 ---------------------------------------------------------------------------------------
683 --Get the party details
684 --
685 ---------------------------------------------------------------------------------------
686 BEGIN
687
688 SELECT party_id,
689 party_site_id
690 INTO l_party_id,
691 l_party_site_id
692 FROM ecx_oag_controlarea_tp_v
693 WHERE transaction_type = l_transaction_type
694 AND transaction_subtype = l_transaction_subtype
695 AND party_type = l_party_type;
696
697 EXCEPTION
698
699 WHEN NO_DATA_FOUND THEN
700 RAISE_APPLICATION_ERROR (-20080, 'XML GATEWAY TRADING PARTNER SETUP IS INCOMPLETE');
701 resultout := 1;
702
703
704 WHEN OTHERS THEN
705 RAISE_APPLICATION_ERROR (-20081, SQLERRM(SQLCODE));
706 resultout := 1;
707 END;
708
709 ---------------------------------------------------------------------------------------
710 --Set all the required attributes
711 --
712 ---------------------------------------------------------------------------------------
713
714 wf_engine.setitemattrtext (
715 itemtype,
716 itemkey,
717 'ECX_TRANSACTION_TYPE',
718 l_transaction_type);
719
720 wf_engine.setitemattrtext (
721 itemtype,
722 itemkey,
723 'ECX_TRANSACTION_SUBTYPE',
724 l_transaction_subtype);
725
726 wf_engine.setitemattrtext (
727 itemtype,
728 itemkey,
729 'ECX_PARTY_ID',
730 l_party_id);
731
732 wf_engine.setitemattrtext (
733 itemtype,
734 itemkey,
735 'ECX_PARTY_SITE_ID',
736 l_party_site_id);
737
738 wf_engine.setitemattrtext (
739 itemtype,
740 itemkey,
741 'ECX_PARTY_TYPE',
742 l_party_type);
743
744 wf_engine.setitemattrtext (
745 itemtype,
746 itemkey,
747 'ECX_SEND_MODE',
748 'SYNCH');
749
750 wf_engine.setitemattrnumber (
751 itemtype,
752 itemkey,
753 'ECX_DEBUG_LEVEL',
754 1);
755
756 l_doc_no := wf_engine.getitemattrtext (
757 itemtype => itemtype,
758 itemkey => itemkey,
759 aname => 'SALES_ORDER_ID');
760
761 /* R12 MOAC UPTAKE : ksrikant */
762
763 /*22-Apr-2006 pselvam Bug Fix 5166267 - Action Tag Empty
764 Including the below to fetch org_id
765 */
766
767 l_org_id := wf_engine.getitemattrtext (
768 itemtype => itemtype,
769 itemkey => itemkey,
770 aname => 'SALE_ORG_ID');
771
772 BEGIN
773
774 SELECT ohdr.order_number
775 INTO l_order_num
776 FROM oe_order_headers_all ohdr,
777 oe_order_lines_all oline
778 WHERE ohdr.header_id = oline.header_id
779 AND oline.line_id = l_doc_no;
780
781 EXCEPTION
782
783 WHEN NO_DATA_FOUND THEN
784 RAISE_APPLICATION_ERROR (-20082, 'Order Number is Missing in while Creating CLN Doc Number');
785 resultout := 1;
786
787 WHEN OTHERS THEN
788 RAISE_APPLICATION_ERROR (-20083, SQLERRM(SQLCODE));
789 resultout := 1;
790 END;
791
792 ---------------------------------------------------------------------------------------
793 --Set the Organization Id
794 --
795 ---------------------------------------------------------------------------------------
796
797 /* R12 MOAC UPTAKE : ksrikant */
798 /* dbms_application_info.set_client_info(l_org_id);*/
799 --22-Apr-2006 pselvam Bug Fix 5166267 - Action Tag Empty
800 mo_global.set_policy_context('S',l_org_id);
801
802 -- Fix for Bug 3916658
803
804 l_num := l_order_num||':'||l_doc_no;
805
806 wf_engine.setitemattrtext (
807 itemtype,
808 itemkey,
809 'XNB_SALESORDER_NUM',
810 l_num);
811
812
816 --else Collaboration exists so Update
813 ---------------------------------------------------------------------------------------
814 --Check to see if the collaboration already exists for the Sales Order
815 --if l_num = 2 Collaboration doesn't exist so Create
817 ---------------------------------------------------------------------------------------
818
819 /* l_num := xnb_util_pvt.check_collaboration_doc_status (l_doc_no, l_transaction_type, l_transaction_subtype);
820
821 IF l_num = 2 THEN
822 resultout := FND_API.G_FALSE;
823 RETURN ;
824 ELSE
825 ---------------------------------------------------------------------------------------
826 --Collaboration Already Exists..
827 --
828 ---------------------------------------------------------------------------------------
829 resultout := FND_API.G_TRUE;
830 RETURN;
831 END IF;*/
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 RAISE_APPLICATION_ERROR (-20049, SQLERRM(SQLCODE));
836 resultout := 1;
837
838 --End of Function
839 END set_sales_order_attributes;
840
841 ------------------------------------------------------------------------
842 --This procedure sets the Workflow attributes required to initialize the
843 --XNB IB Ownership change workflow process.
844 --This procedure is called from the workflow process.
845 --The arguments correspond to the standard workflow function arguments.
846 --Returns status through OUT parameter resultout. True on Success, False on error.
847 -----------------------------------------------------------------------
848 PROCEDURE set_owner_attributes(itemtype IN VARCHAR2,
849 itemkey IN VARCHAR2,
850 actid IN NUMBER,
851 funcmode IN VARCHAR2,
852 resultout OUT NOCOPY VARCHAR2)
853 AS
854 l_transaction_type VARCHAR2(10) ; --XML GW Internal Txn Type - XNB
855 l_transaction_subtype VARCHAR2(10) ; --XML GW Internal Txn Subtype - Owner Outbound
856 l_party_id ecx_oag_controlarea_tp_v.party_id%TYPE; --Trading Partner ID (from XML Gateway setup)
857 l_party_site_id ecx_oag_controlarea_tp_v.party_site_id%TYPE; --Trading Partner Site ID (from XML Gateway setup)
858 l_party_type VARCHAR2(30) ; --XML Gateway Party Type
859
860 BEGIN
861
862 l_transaction_type := g_xnb_transation_type;
863 l_transaction_subtype := g_owner_change_txn_subtype;
864 l_party_type := g_party_type;
865
866 --Retrieve the required attributes for the workflow
867
868 ---------------------------------------------------------------------------------------
869 --Get the party details
870 --
871 ---------------------------------------------------------------------------------------
872 BEGIN
873
874 SELECT party_id,
875 party_site_id
876 INTO l_party_id,
877 l_party_site_id
878 FROM ecx_oag_controlarea_tp_v
879 WHERE transaction_type = l_transaction_type
883 EXCEPTION
880 AND transaction_subtype = l_transaction_subtype
881 AND party_type = l_party_type;
882
884
885 WHEN NO_DATA_FOUND THEN
886 RAISE_APPLICATION_ERROR (-20080, 'XML GATEWAY TRADING PARTNER SETUP IS INCOMPLETE');
887 resultout := 1;
888
889
890 WHEN OTHERS THEN
891 RAISE_APPLICATION_ERROR (-20081, SQLERRM(SQLCODE));
892 resultout := 1;
893 END;
894
895 ---------------------------------------------------------------------------------------
896 --Set all the required attributes
897 --
898 ---------------------------------------------------------------------------------------
899
900 --Set the Workflow attributes
901 wf_engine.setitemattrtext(
902 itemtype,
903 itemkey,
904 'ECX_TRANSACTION_TYPE',
905 l_transaction_type);
906
907 wf_engine.setitemattrtext(
908 itemtype,
909 itemkey,
910 'ECX_TRANSACTION_SUBTYPE',
911 l_transaction_subtype);
912
913 wf_engine.setitemattrtext(
914 itemtype,
915 itemkey,
916 'ECX_PARTY_ID',
917 l_party_id);
918
919 wf_engine.setitemattrtext(
920 itemtype,
921 itemkey,
922 'ECX_PARTY_SITE_ID',
923 l_party_site_id);
924
925 wf_engine.setitemattrtext(
926 itemtype,
927 itemkey,
928 'ECX_PARTY_TYPE',
929 l_party_type);
930
931 wf_engine.setitemattrtext(
932 itemtype,
933 itemkey,
934 'ECX_SEND_MODE',
935 'SYNCH');
936
937 --return Workflow lookup value TRUE to indicate success
938 resultout := FND_API.G_TRUE;
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 --return Workflow lookup value FALSE to indicate failure
943 resultout := FND_API.G_FALSE;
944 --End of Procedure
945 END set_owner_attributes;
946
947 -----------------------------------------------------------------------------------------------------------
948 --This procedure checks to see if the Inventory Item of the IB instance, whose details are to be published,
949 --is Provisionable. The current check involves only checking if the item is non-invoicable. Only non-invoicable
950 --items need be sent to the external billing application.
951 --Checks Inventory MTL_SYSTEM_ITEMS_B to see if, for the item
952 -- INVOICEABLE_ITEM_FLAG = "N" - If TRUE the item is to be invoiced by an external biller.
953 --Arguments: Standard workflow function args
954 --Returns: resultout. TRUE if the instance is provisionable (i.e INVOICEABLE_ITEM_FLAG = "N" )
955 -- FALSE if the instance is not provisionable (i.e INVOICEABLE_ITEM_FLAG = "N" )
956 --Called by the workflow process: XNBFLOWS/OWNER_CHANGE_PROCESS
957 --Errors: Are not handled. Would halt the workflow that calls this.
958 ------------------------------------------------------------------------------------------------------------
959 PROCEDURE check_item_provisionable(
960 itemtype IN VARCHAR2,
961 itemkey IN VARCHAR2,
962 actid IN NUMBER,
963 funcmode IN VARCHAR2,
964 resultout OUT NOCOPY VARCHAR2)
965 AS
966 l_instance_id NUMBER; --IB instance ID retrieved from the WF
967 l_invoiceable_flag VARCHAR2(2);
968 BEGIN
969 --Retrive the IB Instance ID from the workflow. INSTANCE_ID is NUMBER.
970 l_instance_id := to_number(wf_engine.getitemattrtext(
971 itemtype,
972 itemkey,
973 'IB_INSTANCE_ID'));
974
975 --Hit MTL_SYSTEM_ITEMS_B to see if the Item is invoicable
976 SELECT msib.invoiceable_item_flag
977 INTO l_invoiceable_flag
978 FROM mtl_system_items_b msib,
979 csi_item_instances cii
980 WHERE cii.instance_id = l_instance_id
981 AND msib.inventory_item_id = cii.inventory_item_id
982 AND msib.organization_id = cii.inv_master_organization_id;
983
984 IF (l_invoiceable_flag = 'N' ) THEN --Item is non-invoicable(provisionable)
985 resultout := FND_API.G_TRUE;
986 ELSE /* N */ --Item is not Provisionable
987 resultout := FND_API.G_FALSE;
988 END IF;
989 --End of Procedure
990 END check_item_provisionable;
991
992 ------------------------------------------------------------------------------------------------------------
993 --This procedure checks to see if the IB instance Owner Account, whose details are to be published, has been
994 --sent to a billing application.
995 --Retrieves the Account Number corresponding to the AccountID, from HZ_CUST_ACCOUNTS.
996 --The retrieved Account Number is set as a workflow Attribute. (Required by the next Workflow Activity)
997 --Retrieves the Inventory Master Org ID from CSI_ITEM_INSTANCES corresponding to the Instance ID in workflow
998 --The retrieved Org ID is set as a worfflow Attribute. (Required by the next Workflow Activity)
999 --Checks for a customer account collaboration with document number = ACCOUNT_NUMBER
1000 -- has been successfully completed with every biller sending atleast one SUCCESS CBOD to the message.
1001 --Arguments: Standard workflow function args
1002 --Returns: resultout. TRUE if the account is published and confirmed SUCCESS by all billers.
1006 -----------------------------------------------------------------------------------------------------------
1003 -- FALSE if the account-publish is not confirmed SUCCESS by all billers.
1004 --Exceptions: Left unhandled until proper error handling is introduced in the workflow
1005 --Called by the Workflow Item Type: XNBFLOWS, Process: OWNER_CHANGE_PROCESS
1007 PROCEDURE check_account_published(itemtype IN VARCHAR2,
1008 itemkey IN VARCHAR2,
1009 actid IN NUMBER,
1010 funcmode IN VARCHAR2,
1011 resultout OUT NOCOPY VARCHAR2)
1012 AS
1013
1014 l_instance_id csi_item_instances.instance_id%TYPE;
1015 l_inv_org_id csi_item_instances.inv_master_organization_id%TYPE;
1016 l_account_id csi_item_instances.owner_party_account_id%TYPE;
1017 l_account_number hz_cust_accounts.account_number%TYPE;
1018
1019 l_transaction_type VARCHAR2(5) ; --XML GW Internal Txn Type - XNB
1020 l_transaction_subtype VARCHAR2(5) ; --XML GW Internal Txn Subtype - Owner Outbound
1021 l_party_type VARCHAR2(30) ; --XML Gateway Party Type
1022 l_trading_partner_id NUMBER;
1023
1024 l_event_name VARCHAR2(50); --for the Account Sub Flow. Event Name
1025 l_event_key VARCHAR2(50); --for the Account Sub Flow. Event Name
1026 l_app_ref_id VARCHAR2(100); --for the Account Sub Flow. Application Ref ID for CLN update
1027
1028 l_ret NUMBER;
1029
1030 BEGIN
1031
1032 l_transaction_type := g_xnb_transation_type;
1033 l_transaction_subtype := g_account_txn_subtype;
1034 l_party_type := g_party_type;
1035
1036 --Retrive the Account ID from the workflow. Account ID is NUMBER
1037 l_account_id := to_number(
1038 wf_engine.getitemattrtext(
1039 itemtype,
1040 itemkey,
1041 'IB_OWNER_ACCT_ID_NEW') );
1042
1043 --Get Account Number from HZ_CUST_ACCOUNTS
1044 SELECT account_number
1045 INTO l_account_number
1046 FROM hz_cust_accounts
1047 WHERE cust_account_id = l_account_id;
1048
1049 --Set the Account Number as a workflow attribute
1050 wf_engine.setitemattrtext(
1051 itemtype,
1052 itemkey,
1053 'IB_OWNER_ACCT_NO_NEW',
1054 l_account_number);
1055
1056 --Retrive the Instance ID from the workflow. IB Instance ID is a NUMBER
1057 l_instance_id := to_number(
1058 wf_engine.getitemattrtext(
1059 itemtype,
1060 itemkey,
1061 'IB_INSTANCE_ID') );
1062
1063 --Get the Inventory Master Organization ID from CSI_ITEM_INSTANCES
1064 SELECT inv_master_organization_id
1065 INTO l_inv_org_id
1066 FROM csi_item_instances
1067 WHERE instance_id = l_instance_id;
1068
1069 --Set the Org ID as a workflow attribute
1070 wf_engine.setitemattrtext(
1071 itemtype,
1072 itemkey,
1073 'IB_INSTANCE_ORG_ID',
1074 to_char(l_inv_org_id));
1075
1076 --Call the util procedure and check Collaboration History, if the account is published
1077 l_ret := xnb_util_pvt.check_collaboration_doc_status (
1078 to_char(l_instance_id),
1079 'XNB_OWNER');
1080
1081 IF (l_ret = 2 OR l_ret = -1) THEN --If it was not found / or there was an error
1082 --The workflow will next branch to the account publish process and try to publish an account
1083
1084 l_event_name := 'oracle.apps.xnb.account.create';
1085 l_event_key := 'XNB_IBCHOWN_ACCTPUB:'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1086 l_app_ref_id := 'XNB_REF:' || l_event_name || l_event_key;
1087
1088 --Set the workflow attributes for this subflow
1089 --Event Name
1090
1091 wf_engine.setitemattrtext(
1092 itemtype,
1093 itemkey,
1094 'ACCT_EVENT_NAME',
1095 l_event_name);
1096
1097 --Event Key
1098 wf_engine.setitemattrtext(
1099 itemtype,
1100 itemkey,
1101 'ACCT_EVENT_KEY',
1102 l_event_key);
1103
1104 --Application Reference ID
1105 wf_engine.setitemattrtext(
1106 itemtype,
1107 itemkey,
1108 'REFERENCE_ID',
1109 l_app_ref_id);
1110
1111 resultout := FND_API.G_FALSE;
1112 ELSE /* 0 or 1 */ --Published success.
1113 resultout := FND_API.G_TRUE;
1114 END IF;
1115 --End of Function
1116 END check_account_published;
1117
1118
1119 ---------------------------------------------------------------------------------------------------
1120 --This procedure checks to see if a collaboration exists for this instance for OWNER CHANGE
1121 --Calls the util procedure 'validate_document'
1122 --Arguments: Standard workflow function args.
1123 --Returns status through result_out: FND_API.G_TRUE if exists, FND_API.G_FALSE if it does not exist.
1124 ---------------------------------------------------------------------------------------------------
1125 PROCEDURE check_owner_change_cln(
1126 itemtype IN VARCHAR2,
1127 itemkey IN VARCHAR2,
1128 actid IN NUMBER,
1129 funcmode IN VARCHAR2,
1130 resultout OUT NOCOPY VARCHAR2)
1131 AS
1132 l_transaction_type VARCHAR2(5) ; --XML GW Internal Txn Type - XNB
1133 l_transaction_subtype VARCHAR2(5) ; --XML GW Internal Txn Subtype - Owner Outbound
1134 l_party_type VARCHAR2(30) ; --XML Gateway Party Type
1135 l_trading_partner_id NUMBER;
1136 l_document_id NUMBER;
1137 l_ret NUMBER;
1138 BEGIN
1139
1140 l_transaction_type := g_xnb_transation_type;
1141 l_transaction_subtype := g_owner_change_txn_subtype;
1142 l_party_type := g_party_type;
1143
1144 --Retrive the instance ID (Collaboration Document ID) from the workflow
1145 l_document_id := wf_engine.getitemattrtext(
1146 itemtype,
1147 itemkey,
1148 'IB_INSTANCE_ID');
1149
1150 --Call the util procedure and check Collaboration History
1151 l_ret := xnb_util_pvt.check_collaboration_doc_status (
1152 l_document_id,
1153 'XNB_OWNER');
1154
1155
1156 IF (l_ret = 2 OR l_ret = -1) THEN --If it was not found / or there was an error
1157 resultout := FND_API.G_FALSE;
1158 ELSE /* 0 or 1 */ --Published success / Published failure
1159 resultout := FND_API.G_TRUE;
1160 END IF;
1161 --Exceptions left unhandled
1162 --End of Procedure
1163 END check_owner_change_cln;
1164
1165
1166
1167 /*** SalesOrder Node which is called whenever a Salesorder is booked */
1168 /* Sales order Node impregnated in Line Flow od SRVDV11i */
1169
1170 PROCEDURE publish_salesorder_info(itemtype IN VARCHAR2,
1171 itemkey IN VARCHAR2,
1172 actid IN NUMBER,
1173 funcmode IN VARCHAR2,
1174 resultout OUT NOCOPY VARCHAR2)
1175 AS
1176
1177 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1178 l_key varchar2(200) ;
1179 l_line_id NUMBER;
1180 l_org_id NUMBER;
1181 l_order_num NUMBER;
1182
1183 begin
1184
1185 l_line_id := to_number(itemkey);
1186
1187 -------------------------------------------------------------------------------------
1188 --Retrieve the Order Number
1189 --
1190 -------------------------------------------------------------------------------------
1191 BEGIN
1192
1193 SELECT ohdr.order_number
1194 INTO l_order_num
1195 FROM oe_order_headers_all ohdr,
1196 oe_order_lines_all oline
1197 WHERE ohdr.header_id = oline.header_id
1198 AND oline.line_id = l_line_id;
1199
1200 EXCEPTION
1201
1202 WHEN NO_DATA_FOUND THEN
1203 RAISE_APPLICATION_ERROR (-20084, 'Order Number is Missing. Please check the Database');
1204 resultout := 1;
1205
1206 WHEN OTHERS THEN
1207 RAISE_APPLICATION_ERROR (-20085, SQLERRM(SQLCODE));
1208 resultout := 1;
1209 END;
1210
1211 l_key := 'XNB:'||'SALESORDER_LINE : '||l_order_num||':'||l_line_id;
1212
1213 SELECT org_id into l_org_id from oe_order_lines_all
1214 WHERE line_id = l_line_id;
1215
1216 wf_event.AddParameterToList(p_name =>'SALES_ORDER_ID',p_value => l_line_id,p_parameterlist => l_parameter_list);
1217 wf_event.AddParameterToList(p_name =>'SALE_ORG_ID',p_value => l_org_id,p_parameterlist => l_parameter_list);
1218
1219 wf_event.raise( p_event_name => 'oracle.apps.xnb.salesorder.create',
1220 p_event_key => l_key,
1221 p_parameters => l_parameter_list);
1222
1223
1224 EXCEPTION
1225
1226 WHEN NO_DATA_FOUND THEN
1227 RAISE_APPLICATION_ERROR(-20100,' org_id does not exist for the oRDER NUMBER');
1228
1229 WHEN OTHERS THEN
1230 RAISE_APPLICATION_ERROR(-20108, SQLERRM(SQLCODE));
1231
1235 /*** Account Node which is called whenever a Salesorder is booked */
1232 END publish_salesorder_info;
1233
1234
1236 /* Account Node impregnated in Order Header Flow od SRVDV11i */
1237
1238 PROCEDURE publish_account_info(itemtype IN VARCHAR2,
1239 itemkey IN VARCHAR2,
1240 actid IN NUMBER,
1241 funcmode IN VARCHAR2,
1242 resultout OUT NOCOPY VARCHAR2)
1243 AS
1244
1245 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1246 l_key varchar2(200);
1247 l_account_number VARCHAR2(30);
1248 l_order_no NUMBER;
1249 l_org_id NUMBER;
1250 l_sold_to_org_id NUMBER;
1251
1252 --------------------------------------------------------------------------------
1253 -- Cursor to retrieve all the account numbers associated with the order
1254 --
1255 --------------------------------------------------------------------------------
1256
1257 CURSOR l_accounts (pl_order_number NUMBER,
1258 pl_org_id NUMBER)
1259 IS
1260 SELECT accounts.account_number
1261 FROM xnb_salesorder_accounts_v accounts
1262 WHERE accounts.order_number = pl_order_number
1263 AND accounts.org_id = pl_org_id;
1264
1265 begin
1266
1267 --------------------------------------------------------------------------------
1268 -- Retrieve the ordernumber from the OM Order Header Attribute
1269 --
1270 --------------------------------------------------------------------------------
1271
1272 l_order_no := WF_ENGINE.GETITEMATTRTEXT (ITEMTYPE => ITEMTYPE,
1273 ITEMKEY => ITEMKEY,
1274 ANAME => 'ORDER_NUMBER');
1275
1276 --------------------------------------------------------------------------------
1277 -- Retrieve the Organization Id from the ordernumber
1278 --
1279 --------------------------------------------------------------------------------
1280
1281 -- SELECT org_id INTO l_org_id FROM oe_order_headers_all
1282 -- WHERE order_number = l_order_no;
1283
1284 --------------------------------------------------------------------------------
1285 -- Retrieve the organization id from the OM Order Header Attribute
1286 --
1287 --------------------------------------------------------------------------------
1288
1289 l_org_id := WF_ENGINE.GETITEMATTRTEXT (ITEMTYPE => ITEMTYPE,
1290 ITEMKEY => ITEMKEY,
1291 ANAME => 'ORG_ID');
1292
1293 --------------------------------------------------------------------------------
1294 -- Iterate the Cursor to publish all the accounts
1295 --
1296 --------------------------------------------------------------------------------
1297
1298 OPEN l_accounts (l_order_no, l_org_id);
1299 FETCH l_accounts into l_account_number;
1300 WHILE (l_accounts%FOUND) LOOP
1301
1302 l_key := 'XNB:'||'ACCOUNT:'||l_account_number||':'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1303
1304 wf_event.AddParameterToList(p_name =>'ACCOUNT_NUMBER',p_value => l_account_number,p_parameterlist =>l_parameter_list);
1305 wf_event.AddParameterToList(p_name =>'ACCT_ORG_ID',p_value => l_org_id ,p_parameterlist => l_parameter_list);
1306
1307 --------------------------------------------------------------------------------
1308 -- Raise the event to publish the account number with the necessary parameters
1309 --
1310 --------------------------------------------------------------------------------
1311
1312
1313 wf_event.raise( p_event_name => 'oracle.apps.xnb.account.create',
1314 p_event_key => l_key,
1315 p_parameters => l_parameter_list);
1316
1317 FETCH l_accounts into l_account_number;
1318 END LOOP;
1319
1320 --------------------------------------------------------------------------------
1321 -- Close the Cursor
1322 --
1323 --------------------------------------------------------------------------------
1324 CLOSE l_accounts;
1325
1326 EXCEPTION
1327
1328 WHEN NO_DATA_FOUND THEN
1329 RAISE_APPLICATION_ERROR(-20100,' Org_id does not exist for the ORDER NUMBER');
1330
1331 WHEN OTHERS THEN
1332 CLOSE l_accounts;
1333 RAISE_APPLICATION_ERROR(-20108, SQLERRM(SQLCODE));
1334
1335 END publish_account_info;
1336
1337 Function check_subscribed_events(
1338 p_subscription_guid IN RAW,
1339 p_event IN OUT NOCOPY WF_EVENT_T
1340 )
1341 return VARCHAR2
1342 AS
1343
1344 l_event_name VARCHAR2(50);
1345 l_subscribed_event xnb_subscribed_events.event_name%TYPE;
1346 x_result VARCHAR2(20);
1347 l_err_name VARCHAR2(40);
1348 l_err_message VARCHAR2(100);
1349 l_err_stack VARCHAR2(1000);
1350
1351 CURSOR l_sub_events IS
1352 SELECT DISTINCT event_name FROM xnb_subscribed_events
1353 WHERE entity_type = 'ACCOUNT_UPDATE';
1354
1355 BEGIN
1356
1357 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS','Subscription has Triggered');
1358 l_event_name := p_event.geteventname();
1359 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS',l_event_name);
1360
1361 OPEN l_sub_events;
1362 FETCH l_sub_events INTO l_subscribed_event;
1363
1364 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS','AFTER FETCH');
1365
1366 WHILE (l_sub_events%FOUND) LOOP
1367 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS','INSIDE WHILE');
1368
1369 IF (l_subscribed_event = l_event_name) THEN
1370 publish_account_update(l_event_name, p_event);
1371 x_result := 'SUCCESS';
1372 CLOSE l_sub_events;
1373 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS','Successfully CHECKED x_result'||x_result);
1374 RETURN x_result;
1375 END IF;
1376
1380 CLOSE l_sub_events;
1377 FETCH l_sub_events INTO l_subscribed_event;
1378 END LOOP;
1379
1381 x_result := 'SUCCESS';
1382 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS','After Cursor Close,'||x_result);
1383 RETURN x_result;
1384
1385 EXCEPTION
1386
1387 WHEN OTHERS THEN
1388 wf_core.GET_ERROR (err_name => l_err_name,
1389 err_message => l_err_message,
1390 err_stack => l_err_stack,
1391 maxErrStackLength => 900);
1392 XNB_DEBUG.log('XNB_CMN_PVT.CHECK_SUBSCRIBED_EVENTS',l_err_name||' : '||l_err_message);
1393 CLOSE l_sub_events;
1394 x_result := 'ERROR';
1395 RETURN x_result;
1396
1397 END check_subscribed_events;
1398
1399 PROCEDURE publish_account_update(l_event_name IN VARCHAR2,
1400 p_event IN OUT NOCOPY WF_EVENT_T)
1401 AS
1402
1403 l_param_value VARCHAR2(60);
1404 l_rel_cust_acct_id VARCHAR2(60);
1405 l_pri_bill_to_site_id VARCHAR2(60);
1406 l_account_number VARCHAR2(30);
1407 l_org_id NUMBER;
1408 l_site_use_code VARCHAR2(50);
1409 l_site_use_id VARCHAR2(50);
1410 l_num NUMBER;
1411 l_flag CHAR;
1412 l_err_name VARCHAR2(40);
1413 l_err_message VARCHAR2(100);
1414 l_err_stack VARCHAR2(1000);
1415 l_table_name VARCHAR2(30);
1416
1417 /* 30-May-2006 pselvam ST1 Bug Fix 5254717 - Acct Update Org Id issue */
1418 l_user_id NUMBER;
1419 l_resp_id NUMBER;
1420 l_resp_appl_id NUMBER;
1421
1422 TYPE AccountNumTyp IS TABLE OF HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE
1423 INDEX BY BINARY_INTEGER;
1424
1425 l_acc_num AccountNumTyp;
1426
1427 BEGIN
1428
1429 /*30-May-2006 pselvam ST1 Bug Fix 5254717 - Acct Update Org Id issue*/
1430 --fnd_profile.GET( 'ORG_ID', l_org_id );
1431
1432 l_user_id := p_event.GetValueForParameter('USER_ID');
1433 l_resp_id := p_event.GetValueForParameter('RESP_ID');
1434 l_resp_appl_id := p_event.GetValueForParameter('RESP_APPL_ID');
1435
1436 FND_GLOBAL.apps_initialize(l_user_id, l_resp_id, l_resp_appl_id);
1437
1438 MO_GLOBAL.init('AR');
1439
1440 l_org_id := MO_UTILS.get_default_org_id;
1441
1442 /* R12 MOAC UPTAKE : ksrikant*/
1443 /*dbms_application_info.set_client_info(l_org_id);*/
1444
1445 IF l_event_name = 'oracle.apps.ar.hz.CustAccount.update' THEN
1446
1447 l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_ID');
1448 XNB_DEBUG.log('Event_subscription',l_param_value);
1449
1450 BEGIN
1451
1452
1453 SELECT ACCOUNT_NUMBER
1454 INTO l_account_number
1455 FROM HZ_CUST_ACCOUNTS
1456 WHERE CUST_ACCOUNT_ID = l_param_value;
1457
1458 XNB_DEBUG.log('oracle.apps.ar.hz.CustAccount.update',l_account_number);
1459
1460 EXCEPTION
1461
1462 WHEN NO_DATA_FOUND THEN
1463 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1464 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1465 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAccount.update');
1466 RAISE;
1467 END;
1468
1469 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1470
1471 IF l_num = 1 THEN
1472 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1473 END IF;
1474 /*C547_XNB - Obsolete Billing Preference information*/
1475 /*
1476 ELSIF l_event_name = 'oracle.apps.ar.hz.BillingPreference.create' OR l_event_name = 'oracle.apps.ar.hz.BillingPreference.update' THEN
1477
1478 l_param_value := p_event.GetValueForParameter('BILLING_PREFERENCES_ID');
1479 XNB_DEBUG.log('Event_subscription',l_param_value);
1480
1481 BEGIN
1482
1483 SELECT account_number
1484 INTO l_account_number
1485 FROM hz_billing_preferences bill_pref,
1486 hz_cust_accounts acc
1487 WHERE bill_pref.cust_account_id = acc.cust_account_id
1488 AND bill_pref.billing_preferences_id = l_param_value;
1489
1490 XNB_DEBUG.log('oracle.apps.ar.hz.BillingPreference',l_account_number);
1491
1492 EXCEPTION
1493
1494 WHEN NO_DATA_FOUND THEN
1495 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1496 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1497 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR BillingPreference');
1498 RAISE;
1499 END;
1500
1501 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1502
1503 IF l_num = 1 THEN
1504 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1505 END IF;
1506 */
1507 ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctRelate.create' OR l_event_name = 'oracle.apps.ar.hz.CustAcctRelate.update' THEN
1508
1509 l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_ID');
1510 l_rel_cust_acct_id := p_event.GetValueForParameter('RELATED_CUST_ACCOUNT_ID');
1511 XNB_DEBUG.log('Event_subscription',l_param_value);
1512
1513 BEGIN
1514
1515 SELECT distinct ACCT.ACCOUNT_NUMBER
1516 INTO l_account_number
1517 FROM HZ_CUST_ACCOUNTS ACCT,
1518 HZ_CUST_ACCT_RELATE_ALL ACCT_REL
1519 WHERE ACCT.CUST_ACCOUNT_ID = l_param_value
1520 AND ACCT.CUST_ACCOUNT_ID = ACCT_REL.CUST_ACCOUNT_ID;
1521
1525 l_param_value := l_rel_cust_acct_id;
1522 XNB_DEBUG.log('oracle.apps.ar.hz.CustAcctRelate','Account Number'||l_account_number);
1523 XNB_DEBUG.log('oracle.apps.ar.hz.CustAcctRelate','Related Cust Account Id '||l_rel_cust_acct_id);
1524
1526
1527 EXCEPTION
1528
1529 WHEN NO_DATA_FOUND THEN
1530 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1531 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1532 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctRelate');
1533 RAISE;
1534 END;
1535
1536 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1537
1538 IF l_num = 1 THEN
1539 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1540 END IF;
1541
1542
1543 ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.create' OR l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.update' THEN
1544 l_param_value := p_event.GetValueForParameter('SITE_USE_ID');
1545 XNB_DEBUG.log('Event_subscription',l_param_value);
1546
1547 BEGIN
1548
1549 /* R12 MOAC UPTAKE : ksrikant*/
1550
1551 SELECT site_use_code,
1552 primary_flag
1553 INTO l_site_use_code,
1554 l_flag
1555 FROM hz_cust_site_uses_all
1556 WHERE site_use_id = l_param_value;
1557
1558 EXCEPTION
1559
1560 WHEN NO_DATA_FOUND THEN
1561 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1562 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1563 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse');
1564 RAISE;
1565 END;
1566
1567 IF l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.create' THEN
1568
1569 XNB_DEBUG.log('Event_subscription','Inside If oracle.apps.ar.hz.CustAcctSiteUse.create');
1570
1571 IF l_site_use_code = 'BILL_TO' AND l_flag = 'Y' THEN
1572
1573
1574 BEGIN
1575
1576 /* R12 MOAC UPTAKE : ksrikant*/
1577
1578 SELECT b.account_number
1579 INTO l_account_number
1580 FROM hz_cust_site_uses_all p,
1581 hz_cust_acct_sites_all a,
1582 hz_cust_accounts b
1583 WHERE p.site_use_id = l_param_value
1584 AND a.cust_acct_site_id = p.cust_acct_site_id
1585 AND a.cust_account_id = b.cust_account_id;
1586
1587 XNB_DEBUG.log('oracle.apps.ar.hz.CustAcctSiteUse.create',l_account_number);
1588
1589 EXCEPTION
1590
1591 WHEN NO_DATA_FOUND THEN
1592 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1593 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1594 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse.create');
1595 RAISE;
1596 END;
1597
1598 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1599
1600 IF l_num = 1 THEN
1601 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1602 END IF;
1603
1604 END IF;
1605
1606 ELSIF l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.update' THEN
1607
1608 IF l_site_use_code = 'BILL_TO' THEN
1609
1610 BEGIN
1611
1612 /* R12 MOAC UPTAKE : ksrikant*/
1613
1614 SELECT b.account_number
1615 INTO l_account_number
1616 FROM hz_cust_site_uses_all p,
1617 hz_cust_acct_sites_all a,
1618 hz_cust_accounts b
1619 WHERE p.site_use_id = l_param_value
1620 AND a.cust_acct_site_id = p.cust_acct_site_id
1621 AND a.cust_account_id = b.cust_account_id;
1622
1623 XNB_DEBUG.log('oracle.apps.ar.hz.CustAcctSiteUse.update',l_account_number);
1624
1625 EXCEPTION
1626
1627 WHEN NO_DATA_FOUND THEN
1628 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1629 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1630 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustAcctSiteUse.update');
1631 RAISE;
1632 END;
1633
1634 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1635
1636 IF l_num = 1 THEN
1637 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1638 END IF;
1639
1640 END IF; -- END l_site_use_code = 'BILL_TO'
1641
1642 END IF; -- l_event_name = 'oracle.apps.ar.hz.CustAcctSiteUse.create'
1643
1644 ELSIF l_event_name = 'oracle.apps.ar.hz.CustProfileAmt.create' OR l_event_name = 'oracle.apps.ar.hz.CustProfileAmt.update' THEN
1645
1646 l_param_value := p_event.GetValueForParameter('CUST_ACCT_PROFILE_AMT_ID');
1647 XNB_DEBUG.log('Event_subscription',l_param_value);
1648
1649 BEGIN
1650
1651 --ST1 BUG Fix 5221801 - Cust Profile and Profile Amts
1652 SELECT account_number,
1653 pfl.site_use_id
1654 INTO l_account_number,
1655 l_site_use_id
1656 FROM HZ_CUST_PROFILE_AMTS pfl_amnts,
1657 HZ_CUSTOMER_PROFILES pfl,
1661 AND pfl_amnts.CUST_ACCT_PROFILE_AMT_ID = l_param_value;
1658 hz_cust_accounts acc
1659 WHERE pfl_amnts.cust_account_id = acc.cust_account_id
1660 AND pfl.cust_account_profile_id = pfl_amnts.cust_account_profile_id
1662
1663 XNB_DEBUG.log('oracle.apps.ar.hz.CustProfileAmt',l_account_number);
1664
1665 EXCEPTION
1666
1667 WHEN NO_DATA_FOUND THEN
1668 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1669 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1670 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR CustProfileAmt');
1671 RAISE;
1672 END;
1673
1674 IF l_site_use_id IS NULL THEN--ST1 BUG Fix 5221801 - Cust Profile and Profile Amts
1675 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1676 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR CustProfileAmt_'||l_num);
1677
1678 IF l_num = 1 THEN
1679 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1680 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR CustProfileAmt');
1681 END IF;
1682 END IF;
1683
1684 /* R12 Introduction of 4 new events : ksrikant*/
1685 ELSIF l_event_name = 'oracle.apps.ar.hz.Person.update' OR l_event_name = 'oracle.apps.ar.hz.Organization.update' THEN
1686
1687 l_param_value := p_event.GetValueForParameter('PARTY_ID');
1688 XNB_DEBUG.log('Event_subscription',l_param_value);
1689
1690 BEGIN
1691
1692 SELECT account_number
1693 BULK COLLECT INTO l_acc_num
1694 FROM hz_cust_accounts
1695 WHERE party_id = l_param_value;
1696
1697
1698 EXCEPTION
1699
1700 WHEN NO_DATA_FOUND THEN
1701 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1702 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1703 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR Party Update');
1704 RAISE;
1705
1706 WHEN OTHERS THEN
1707 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);
1708
1709 END;
1710
1711 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Before FOR Loop of Party');
1712
1713 FOR i IN 1..l_acc_num.COUNT LOOP
1714
1715 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Inside FOR Loop of Party');
1716 l_num := xnb_util_pvt.check_collaboration_doc_status (l_acc_num(i), 'XNB_ACCOUNT');
1717 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Party_'||l_acc_num(i)||'_'||l_num);
1718
1719 IF l_num = 1 THEN
1720 raise_acctupdate_event(l_acc_num(i), l_org_id, l_event_name, l_param_value);
1721 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Party Account Num_'||l_acc_num(i));
1722 END IF;
1723
1724 END LOOP;
1725
1726 ELSIF l_event_name = 'oracle.apps.ar.hz.CustomerProfile.update' THEN
1727
1728 l_param_value := p_event.GetValueForParameter('CUST_ACCOUNT_PROFILE_ID');
1729 XNB_DEBUG.log('Event_subscription',l_param_value);
1730
1731 BEGIN
1732 --ST1 BUG Fix 5221801 - Cust Profile and Profile Amts
1733 SELECT b.account_number,
1734 a.site_use_id
1735 INTO l_account_number,
1736 l_site_use_id
1737 FROM hz_customer_profiles a,
1738 hz_cust_accounts b
1739 WHERE a.cust_account_profile_id = l_param_value
1740 AND a.cust_account_id = b.cust_account_id;
1741
1742 XNB_DEBUG.log('oracle.apps.ar.hz.CustomerProfile',l_account_number);
1743
1744 EXCEPTION
1745
1746 WHEN NO_DATA_FOUND THEN
1747 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1748 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1749 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','Check the Cust_Account_Id for the Updated Customer Profile');
1750 RAISE;
1751
1752 WHEN OTHERS THEN
1753 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);
1754
1755 END;
1756
1757 IF l_site_use_id IS NULL THEN--ST1 BUG Fix 5221801 - Cust Profile and Profile Amts
1758 l_num := xnb_util_pvt.check_collaboration_doc_status (l_account_number, 'XNB_ACCOUNT');
1759 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Credit CustomerProfile_'||l_num);
1760
1761 IF l_num = 1 THEN
1762 raise_acctupdate_event(l_account_number, l_org_id, l_event_name, l_param_value);
1763 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Credit CustomerProfile');
1764 END IF;
1765 END IF;
1766
1767 ELSIF l_event_name = 'oracle.apps.ar.hz.ContactPoint.update' THEN
1768
1772 BEGIN
1769 l_param_value := p_event.GetValueForParameter('CONTACT_POINT_ID');
1770 XNB_DEBUG.log('Event_subscription',l_param_value);
1771
1773
1774 SELECT owner_table_name
1775 INTO l_table_name
1776 FROM hz_contact_points
1777 WHERE contact_point_id = l_param_value;
1778
1779 EXCEPTION
1780
1781 WHEN NO_DATA_FOUND THEN
1782 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1783 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1784 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','Check the Owner Table Name in HZ_CONTACT_POINTS');
1785 RAISE;
1786
1787 WHEN OTHERS THEN
1788 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);
1789
1790 END;
1791
1792 IF l_table_name = 'HZ_PARTIES' THEN
1793
1794 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', 'Inside IF l_table_name = hz_parties');
1795
1796 BEGIN
1797
1798 SELECT c.account_number
1799 BULK COLLECT INTO l_acc_num
1800 FROM hz_contact_points a,
1801 hz_parties b,
1802 hz_cust_accounts c
1803 WHERE a.contact_point_id = l_param_value
1804 AND a.owner_table_id = b.party_id
1805 AND b.party_id = c.party_id;
1806
1807 EXCEPTION
1808
1809 WHEN NO_DATA_FOUND THEN
1810 WF_CORE.CONTEXT('XNB_CMN_PVT_TEMP', 'PUBLISH_ACCT_UPDATE', p_event.getEventName());
1811 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1812 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE','ACCOUNT_NUMBER IS NULL FOR Contact Point Update');
1813 RAISE;
1814
1815 WHEN OTHERS THEN
1816 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCT_UPDATE', SQLERRM);
1817
1818 END;
1819
1820 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Before FOR Loop of Contact Point Update');
1821
1822 FOR i IN 1..l_acc_num.COUNT LOOP
1823
1824 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Inside FOR Loop of Contact Point Update');
1825
1826 l_num := xnb_util_pvt.check_collaboration_doc_status (l_acc_num(i), 'XNB_ACCOUNT');
1827 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','Check the collaboration FOR Contact Point_'||l_acc_num(i)||'_'||l_num);
1828
1829 IF l_num = 1 THEN
1830 raise_acctupdate_event(l_acc_num(i), l_org_id, l_event_name, l_param_value);
1831 XNB_DEBUG.log('XNB_CMN_PVT.PUBLISH_ACCOUNT_UPDATE','After Raising the Account Publish Event FOR Contact Point_'||l_acc_num(i));
1832 END IF;
1833
1834 END LOOP;
1835
1836 END IF; /* l_table_name = 'HZ_PARTIES' */
1837
1838 END IF; /* If l_event_name = EVENT_NAME */
1839
1840 END publish_account_update;
1841
1842
1843
1844
1845 PROCEDURE raise_acctupdate_event(
1846 p_account_number IN VARCHAR2,
1847 p_org_id IN NUMBER,
1848 p_event_name IN VARCHAR2,
1849 p_param_value IN VARCHAR2)
1850 AS
1851
1852 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1853 l_key VARCHAR2(200);
1854 l_err_name VARCHAR2(40);
1855 l_err_message VARCHAR2(100);
1856 l_err_stack VARCHAR2(1000);
1857 BEGIN
1858
1859 ------------------------------------------------------------------------------------
1860 --Generate the Key, set the required parameters and
1861 --raise the business event to publish the account update message
1862 --
1863 ------------------------------------------------------------------------------------
1864
1865 l_key := 'XNB:'||'ACCOUNT:'||p_account_number||':'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1866
1867 wf_event.AddParameterToList( p_name =>'ACCOUNT_NUMBER',
1868 p_value => p_account_number,
1869 p_parameterlist => l_parameter_list);
1870
1871 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.PUBLISH_ACCT_UPDATE','Org_id before Raising Event'||p_org_id);
1872
1873 wf_event.AddParameterToList( p_name =>'ACCT_ORG_ID',
1874 p_value => p_org_id,
1875 p_parameterlist => l_parameter_list);
1876
1877 wf_event.AddParameterToList( p_name =>'PARAMETER3',
1878 p_value => p_event_name,
1879 p_parameterlist => l_parameter_list);
1880
1881 wf_event.AddParameterToList( p_name =>'ECX_PARAMETER4',
1882 p_value => p_param_value,
1883 p_parameterlist => l_parameter_list);
1884
1885 wf_event.raise( p_event_name => 'oracle.apps.xnb.account.update',
1886 p_event_key => l_key,
1887 p_parameters => l_parameter_list);
1888
1889
1890 EXCEPTION
1891
1892 WHEN OTHERS THEN
1893 wf_core.GET_ERROR( err_name => l_err_name,
1894 err_message => l_err_message,
1895 err_stack => l_err_stack,
1896 maxErrStackLength => 900);
1897 XNB_DEBUG.log('XNB_CMN_PVT_TEMP.RAISE_ACCTUPDATE_EVENT',l_err_name||' : '||l_err_message);
1898 RAISE;
1899
1900 END RAISE_ACCTUPDATE_EVENT;
1901
1902
1903 PROCEDURE set_grpsales_order_attributes (
1904 itemtype IN VARCHAR2,
1905 itemkey IN VARCHAR2,
1906 actid IN NUMBER,
1907 funcmode IN VARCHAR2,
1908 resultout OUT NOCOPY VARCHAR2)
1909 AS
1910
1911 l_transaction_type VARCHAR2(15) ;
1912 l_transaction_subtype VARCHAR2(10) ;
1913 l_party_id NUMBER ;
1914 l_party_site_id NUMBER ;
1915 l_party_type VARCHAR2(30) ;
1916 l_event_key VARCHAR2(100);
1917 l_org_id NUMBER;
1918
1919 BEGIN
1920
1921 l_transaction_type := g_xnb_transation_type;
1922 l_transaction_subtype := g_grpso_add_txn_subtype;
1923 l_party_type := g_party_type;
1924
1925 l_event_key := 'XNB'||'PUBLISH_GSO'||to_char(SYSDATE,'DD-Mon-YYYY HH24:MI:SS');
1926
1927
1928 ---------------------------------------------------------------------------------------
1929 --Get the party details
1930 --
1931 ---------------------------------------------------------------------------------------
1932 BEGIN
1933
1934 SELECT party_id,
1935 party_site_id
1936 INTO l_party_id,
1937 l_party_site_id
1938 FROM ecx_oag_controlarea_tp_v
1939 WHERE transaction_type = l_transaction_type
1940 AND transaction_subtype = l_transaction_subtype
1944
1941 AND party_type = l_party_type;
1942
1943 EXCEPTION
1945 WHEN NO_DATA_FOUND THEN
1946 RAISE_APPLICATION_ERROR (-20080, 'XML GATEWAY TRADING PARTNER SETUP IS INCOMPLETE');
1947 resultout := 1;
1948
1949
1950 WHEN OTHERS THEN
1951 RAISE_APPLICATION_ERROR (-20081, SQLERRM(SQLCODE));
1952 resultout := 1;
1953 END;
1954
1955 ---------------------------------------------------------------------------------------
1956 -- Set all the required attributes
1957 --
1958 ---------------------------------------------------------------------------------------
1959
1960 wf_engine.setitemattrtext (
1961 itemtype,
1962 itemkey,
1963 'ECX_TRANSACTION_TYPE',
1964 l_transaction_type);
1965
1966 wf_engine.setitemattrtext (
1967 itemtype,
1968 itemkey,
1969 'ECX_TRANSACTION_SUBTYPE',
1970 l_transaction_subtype);
1971
1972 wf_engine.setitemattrtext (
1973 itemtype,
1974 itemkey,
1975 'ECX_PARTY_ID',
1976 l_party_id);
1977
1978 wf_engine.setitemattrtext (
1979 itemtype,
1980 itemkey,
1981 'ECX_PARTY_SITE_ID',
1982 l_party_site_id);
1983
1984 wf_engine.setitemattrtext (
1985 itemtype,
1986 itemkey,
1987 'ECX_PARTY_TYPE',
1988 l_party_type);
1989
1990 wf_engine.setitemattrtext (
1991 itemtype,
1992 itemkey,
1993 'ECX_SEND_MODE',
1994 'SYNCH');
1995
1996 wf_engine.setitemattrnumber (
1997 itemtype,
1998 itemkey,
1999 'ECX_DEBUG_LEVEL',
2000 1);
2001
2002 wf_engine.setitemattrtext (
2003 itemtype,
2004 itemkey,
2005 'XML_EVENT_KEY',
2006 l_event_key);
2007
2008 --22-Apr-2006 pselvam Bug Fix 5166267 - Action Tag Empty
2009 l_org_id := wf_engine.getitemattrtext (
2010 itemtype => itemtype,
2011 itemkey => itemkey,
2012 aname => 'SALE_ORG_ID');
2013
2014 mo_global.set_policy_context('S',l_org_id);
2015
2016
2017 --End of Function
2018 END set_grpsales_order_attributes;
2019
2020 /*** Procedure to publish the Grouped Salesorder Message */
2021
2022 PROCEDURE publish_grpsalesorder_info( itemtype IN VARCHAR2,
2023 itemkey IN VARCHAR2,
2024 actid IN NUMBER,
2025 funcmode IN VARCHAR2,
2026 resultout OUT NOCOPY VARCHAR2)
2027 AS
2028
2029 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
2030 l_key VARCHAR2(200);
2031 l_order_number NUMBER;
2032 l_org_id NUMBER;
2033 l_num NUMBER;
2034
2035
2036 BEGIN
2037 l_num := 0;
2038 --------------------------------------------------------------------------------
2039 -- Retrieve the ordernumber from the OM Order Header Attribute
2040 --
2041 --------------------------------------------------------------------------------
2042
2043 l_order_number := WF_ENGINE.GETITEMATTRTEXT ( ITEMTYPE => ITEMTYPE,
2044 ITEMKEY => ITEMKEY,
2045 ANAME => 'ORDER_NUMBER');
2046
2047 BEGIN
2048
2049 SELECT count(line.line_id)
2050 INTO l_num
2051 FROM oe_order_headers_all head,
2052 oe_order_lines_all line,
2053 mtl_system_items_vl item
2054 WHERE head.order_number = l_order_number
2055 AND head.header_id = line.header_id
2056 AND line.inventory_item_id = item.inventory_item_id
2057 AND item.organization_id = line.ship_from_org_id
2058 AND item.invoiceable_item_flag = 'N';
2059
2060 EXCEPTION
2061
2062 WHEN OTHERS THEN
2063 RAISE_APPLICATION_ERROR (-20011, SQLERRM(SQLCODE));
2064 resultout := 1;
2065
2066 END;
2067
2068 IF l_num = 0 THEN
2069 resultout := 0;
2070 RETURN;
2071 ELSE
2072 /*
2073 BEGIN
2074
2075 SELECT org_id
2076 INTO l_org_id
2077 FROM oe_order_headers_all
2078 WHERE order_number = l_order_number;
2079
2080 EXCEPTION
2081
2082 WHEN NO_DATA_FOUND THEN
2083 RAISE_APPLICATION_ERROR (-20080, 'ORG_ID is missing in ORDER Headers Table');
2084 resultout := 1;
2085
2086
2087 WHEN OTHERS THEN
2088 RAISE_APPLICATION_ERROR (-20081, SQLERRM(SQLCODE));
2089 resultout := 1;
2090
2091 END;
2092 */
2093
2094 --------------------------------------------------------------------------------
2095 -- Retrieve the orgnization id from the OM Order Header Attribute
2096 --
2097 --------------------------------------------------------------------------------
2098
2099 l_org_id := WF_ENGINE.GETITEMATTRTEXT ( ITEMTYPE => ITEMTYPE,
2100 ITEMKEY => ITEMKEY,
2101 ANAME => 'ORG_ID');
2102
2103
2104 l_key := 'XNB:'||'GRPSALESORDER:'||l_order_number;
2105 wf_event.AddParameterToList(p_name =>'ORDER_NUMBER', p_value => l_order_number, p_parameterlist =>l_parameter_list);
2106 wf_event.AddParameterToList(p_name =>'SALE_ORG_ID',p_value => l_org_id,p_parameterlist => l_parameter_list);
2107
2108 --------------------------------------------------------------------------------
2109 -- Raise the event to publish the grouped salesorder with the necessary parameters
2110 --
2111 --------------------------------------------------------------------------------
2112
2113 wf_event.raise( p_event_name => 'oracle.apps.xnb.groupedsalesorder.create',
2114 p_event_key => l_key,
2115 p_parameters => l_parameter_list);
2116 END IF;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 RAISE_APPLICATION_ERROR(-20108, SQLERRM(SQLCODE));
2121
2122 END publish_grpsalesorder_info;
2123
2124
2125 --End of Package
2126 END XNB_CMN_PVT;