[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_IMPORT_WF
Source
1 PACKAGE BODY OE_ORDER_IMPORT_WF AS
2 /* $Header: OEXWFOIB.pls 120.8 2011/09/16 11:52:47 slagiset ship $ */
3
4 PROCEDURE OEOI_SELECTOR
5 ( p_itemtype in varchar2,
6 p_itemkey in varchar2,
7 p_actid in number,
8 p_funcmode in varchar2,
9 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
10 )
11 IS
12 l_user_id NUMBER;
13 l_resp_id NUMBER;
14 l_resp_appl_id NUMBER;
15 l_org_id NUMBER;
16 l_current_org_id NUMBER;
17 l_client_org_id NUMBER;
18 l_parameter1 NUMBER;
19
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 BEGIN
24
25 IF l_debug_level > 0 THEN
26 oe_debug_pub.add( 'ENTERING OEOI_SELECTOR PROCEDURE' ) ;
27
28 oe_debug_pub.add( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
29 END IF;
30
31 -- {
32 IF (p_funcmode = 'RUN') THEN
33
34 IF l_debug_level > 0 THEN
35 oe_debug_pub.add( 'P_FUNCMODE IS RUN' ) ;
36 END IF;
37 p_x_result := G_WFR_COMPLETE;
38
39 -- Engine calls SET_CTX just before activity execution
40
41 ELSIF(p_funcmode = 'SET_CTX') THEN
42
43 IF l_debug_level > 0 THEN
44 oe_debug_pub.add( 'P_FUNCMODE IS SET_CTX' ) ;
45 END IF;
46
47 l_org_id := wf_engine.GetItemAttrNumber( G_WFI_ORDER_IMPORT
48 , p_itemkey
49 , 'ORG_ID'
50 );
51
52 IF l_debug_level > 0 THEN
53 oe_debug_pub.add(' l_org_id =>' || l_org_id);
54 END IF;
55
56 mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
57 p_x_result := G_WFR_COMPLETE;
58
59 ELSIF (p_funcmode = 'TEST_CTX') THEN
60
61 /* Delete Following before check
62 l_org_id := wf_engine.GetItemAttrNumber
63 (itemtype => p_itemtype,
64 itemkey => p_itemkey,
65 aname => 'ORG_ID');
66 If l_org_id is Null Then
67 Begin
68 Select org_id
69 Into l_org_id
70 From oe_headers_interface
71 Where orig_sys_document_ref = p_itemkey
72 And order_source_id = '20';
73 Exception
74 When Others Then
75 Null;
76 End;
77 End If;
78
79 */
80
81
82
83 If p_itemtype = G_WFI_ORDER_IMPORT Then
84 l_parameter1 := wf_engine.GetItemAttrNumber
85 (itemtype => p_itemtype,
86 itemkey => p_itemkey,
87 aname => 'PARAMETER1');
88 End If;
89
90 IF l_debug_level > 0 THEN
91 oe_debug_pub.add( 'WF_PARA1 = '||L_PARAMETER1 ) ;
92 END IF;
93 wf_engine.SetItemAttrText (itemtype => p_itemtype,
94 itemkey => p_itemkey,
95 aname => 'ORG_ID',
96 avalue => l_parameter1);
97
98 l_org_id := l_parameter1;
99
100 IF l_debug_level > 0 THEN
101 oe_debug_pub.add( 'l_org_id (from workflow)=>'|| l_org_id ) ;
102 END IF;
103
104 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id)
105 THEN
106 p_x_result := 'FALSE';
107 ELSE
108 p_x_result := 'TRUE';
109 END IF;
110
111
112 END IF;
113 -- p_funcmode }
114
115 EXCEPTION
116 WHEN OTHERS THEN NULL;
117 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'OEOI_SELECTOR',
118 p_itemtype, p_itemkey, p_actid, p_funcmode);
119 RAISE;
120
121 END OEOI_SELECTOR;
122
123 PROCEDURE OESO_SELECTOR
124 ( p_itemtype in varchar2,
125 p_itemkey in varchar2,
126 p_actid in number,
127 p_funcmode in varchar2,
128 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
129 )
130 IS
131 l_user_id NUMBER;
132 l_resp_id NUMBER;
133 l_resp_appl_id NUMBER;
134 l_org_id NUMBER;
135 l_current_org_id NUMBER;
136 l_client_org_id NUMBER;
137 l_parameter1 NUMBER;
138
139 --
140 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
141 --
142 BEGIN
143
144 IF l_debug_level > 0 THEN
145 oe_debug_pub.add( 'ENTERING OESO_SELECTOR PROCEDURE' ) ;
146
147 oe_debug_pub.add( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
148 END IF;
149
150 -- {
151 IF (p_funcmode = 'RUN') THEN
152
153 IF l_debug_level > 0 THEN
154 oe_debug_pub.add( 'P_FUNCMODE IS RUN' ) ;
155 END IF;
156 p_x_result := G_WFR_COMPLETE;
157
158 -- Engine calls SET_CTX just before activity execution
159
160 ELSIF(p_funcmode = 'SET_CTX') THEN
161
162 IF l_debug_level > 0 THEN
163 oe_debug_pub.add( 'P_FUNCMODE IS SET_CTX' ) ;
164 END IF;
165 l_org_id := wf_engine.GetItemAttrNumber( G_WFI_SHOW_SO
166 , p_itemkey
167 , 'ORG_ID'
168 );
169
170 IF l_debug_level > 0 THEN
171 oe_debug_pub.add(' l_org_id =>' || l_org_id);
172 END IF;
173
174 mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
175 p_x_result := G_WFR_COMPLETE;
176
177 ELSIF (p_funcmode = 'TEST_CTX') THEN
178
179 IF l_debug_level > 0 THEN
180 oe_debug_pub.add( 'WF_PARA1 = '||L_PARAMETER1 ) ;
181 END IF;
182
183 l_org_id := wf_engine.GetItemAttrNumber( G_WFI_SHOW_SO
184 , p_itemkey
185 , 'ORG_ID'
186 );
187 IF l_debug_level > 0 THEN
188 oe_debug_pub.add( 'l_org_id (from workflow)=>'|| l_org_id ) ;
189 END IF;
190
191 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id) THEN
192 p_x_result := 'FALSE';
193 ELSE
194 p_x_result := 'TRUE';
195 END IF;
196
197 END IF;
198 -- p_funcmode }
199
200 EXCEPTION
201 WHEN OTHERS THEN NULL;
202 WF_CORE.Context('OE_ORDER_SHOW_SO', 'OESO_SELECTOR',
203 p_itemtype, p_itemkey, p_actid, p_funcmode);
204 RAISE;
205
206 END OESO_SELECTOR;
207
208
209 PROCEDURE OEOA_SELECTOR
210 ( p_itemtype in varchar2,
211 p_itemkey in varchar2,
212 p_actid in number,
213 p_funcmode in varchar2,
214 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
215 )
216 IS
217 l_user_id NUMBER;
218 l_resp_id NUMBER;
219 l_resp_appl_id NUMBER;
220 l_org_id NUMBER;
221 l_current_org_id NUMBER;
222 l_client_org_id NUMBER;
223 l_parameter1 NUMBER;
224
225 --
226 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
227 --
228 BEGIN
229
230 IF l_debug_level > 0 THEN
231 oe_debug_pub.add( 'ENTERING OEOA_SELECTOR PROCEDURE' ) ;
232
233 oe_debug_pub.add( 'THE WORKFLOW FUNCTION MODE IS: FUNCMODE='||P_FUNCMODE ) ;
234 END IF;
235
236 -- {
237 IF (p_funcmode = 'RUN') THEN
238
239 IF l_debug_level > 0 THEN
240 oe_debug_pub.add( 'P_FUNCMODE IS RUN' ) ;
241 END IF;
242 p_x_result := G_WFR_COMPLETE;
243
244 -- Engine calls SET_CTX just before activity execution
245
246 ELSIF(p_funcmode = 'SET_CTX') THEN
247
248 IF l_debug_level > 0 THEN
249 oe_debug_pub.add( 'P_FUNCMODE IS SET_CTX' ) ;
250 END IF;
251
252 l_org_id := wf_engine.GetItemAttrNumber( G_WFI_ORDER_ACK
253 , p_itemkey
254 , 'ORG_ID'
255 );
256
257 IF l_debug_level > 0 THEN
258 oe_debug_pub.add(' l_org_id =>' || l_org_id);
259 END IF;
260
261 mo_global.set_policy_context(p_access_mode => 'S', p_org_id=>l_Org_Id);
262
263 p_x_result := G_WFR_COMPLETE;
264
265 ELSIF (p_funcmode = 'TEST_CTX') THEN
266 -- don't need this code because the org id is either passed from the
267 -- OEOI flow or set directly in the function call
268 /*If p_itemtype = G_WFI_ORDER_ACK Then
269 l_parameter1 := wf_engine.GetItemAttrNumber
270 (itemtype => p_itemtype,
271 itemkey => p_itemkey,
272 aname => 'ORG_ID');
273 End If;
274
275 IF l_debug_level > 0 THEN
276 oe_debug_pub.add( 'WF_PARA1 = '||L_PARAMETER1 ) ;
277 END IF;
278 wf_engine.SetItemAttrText (itemtype => p_itemtype,
279 itemkey => p_itemkey,
280 aname => 'ORG_ID',
281 avalue => l_parameter1);
282 */
283 l_org_id := wf_engine.GetItemAttrNumber( G_WFI_ORDER_ACK
284 , p_itemkey
285 , 'ORG_ID'
286 );
287 IF l_debug_level > 0 THEN
288 oe_debug_pub.add( 'l_org_id (from workflow)=>'|| l_org_id ) ;
289 END IF;
290
291 IF (NVL(mo_global.get_current_org_id,-99) <> l_Org_Id)
292 THEN
293 p_x_result := 'FALSE';
294 ELSE
295 p_x_result := 'TRUE';
296 END IF;
297
298 END IF;
299 -- p_funcmode }
300
301 EXCEPTION
302 WHEN OTHERS THEN NULL;
303 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'OEOA_SELECTOR',
304 p_itemtype, p_itemkey, p_actid, p_funcmode);
305 RAISE;
306
307 END OEOA_SELECTOR;
308
309
310 PROCEDURE CALL_WF_PURGE
311 ( p_itemtype in varchar2,
312 p_itemkey in varchar2
313 )
314 IS
315 --
316 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
317 --
318 BEGIN
319
320 Update WF_ITEMS
321 Set END_DATE = sysdate - .01
322 Where ITEM_TYPE = p_itemtype
323 And ITEM_KEY = p_itemkey;
324
325 if sql%rowcount > 0 then
326 WF_PURGE.ITEMS(p_itemtype, p_itemkey, sysdate, TRUE);
327 end if;
328
329 EXCEPTION
330 WHEN OTHERS THEN
331 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
332 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'CALL_WF_PURGE');
333 END IF;
334 IF l_debug_level > 0 THEN
335 oe_debug_pub.add( 'OTHERS IN CALL_WF_PURGE MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
336 END IF;
337 END CALL_WF_PURGE;
338
339 PROCEDURE START_ORDER_IMPORT
340 ( p_itemtype in varchar2,
341 p_itemkey in varchar2,
342 p_actid in number,
343 p_funcmode in varchar2,
344 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
345 )
346 IS
347 l_errbuf VARCHAR2(2000);
348 l_retcode NUMBER;
349 l_order_source VARCHAR2(240) := '20';
350 l_operation_code VARCHAR2(30);
351 -- l_debug_level NUMBER :=
352 -- to_number(nvl(fnd_profile.value('ONT_DEBUG_LEVEL'),'0'));
353 l_request_id NUMBER;
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(2000);
356 l_return_status VARCHAR2(1);
357
358 new_request_id NUMBER;
359
360 l_sync VARCHAR2(30) :=
361 fnd_profile.value('ONT_TRANSACTION_PROCESSING');
362
363 l_phase VARCHAR2(30);
364 l_status VARCHAR2(30);
365 l_dev_phase VARCHAR2(30);
366 l_dev_status VARCHAR2(30);
367 l_message VARCHAR2(240);
368 l_exists VARCHAR2(30);
369
370 --
371 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
372 --
373 BEGIN
374
375 IF p_funcmode = 'RUN' THEN
376 IF l_debug_level > 0 THEN
377 oe_debug_pub.add( 'ENTERING START_ORDER_IMPORT' ) ;
378
379 oe_debug_pub.add( 'START_ORDER_IMPORT ACTIVITY ID = '||P_ACTID ) ;
380 END IF;
381
382 -- { If the Profile option ONT_TRANSACTION_PROCESSING is not set or not
383 -- SYNCHRONOUS then no need to start the concurrent process as this will
384 -- be done by user, in BATCH/ASYNCHRONOUS
385
386 OE_STANDARD_WF.Set_Msg_Context(p_actid);
387 If nvl(l_sync, 'SYNCHRONOUS') = 'ASYNCHRONOUS' Then
388 IF l_debug_level > 0 THEN
389 oe_debug_pub.add( 'NO NEED TO START THE CONC. PROGRAM AS PROFILE IS NOT SET' ) ;
390 END IF;
391 p_x_result := G_WFR_NOT_ELIGIBLE;
392 return;
393 End If;
394
395 p_x_result := G_WFR_COMPLETE;
396
397 wf_engine.SetItemAttrText (itemtype => p_itemtype,
398 itemkey => p_itemkey,
399 aname => 'OM_DEBUG_LEVEL',
400 avalue => l_debug_level);
401 -- }
402 OE_STANDARD_WF.Save_Messages;
403 OE_STANDARD_WF.Clear_Msg_Context;
404 IF l_debug_level > 0 THEN
405 oe_debug_pub.add( 'EXITING START_ORDER_IMPORT' ) ;
406 END IF;
407
408 END IF;
409 --
410 -- CANCEL mode - activity 'compensation'
411 --
412 -- This is an event point is called with the effect of the activity must
413 -- be undone, for example when a process is reset to an earlier point
414 -- due to a loop back.
415 --
416 if (p_funcmode = 'CANCEL') then
417
418 -- your cancel code goes here
419 null;
420
421 -- no result needed
422 p_x_result := 'COMPLETE';
423 return;
424 end if;
425 EXCEPTION
426
427 WHEN OTHERS THEN
428 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
429 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'start_order_import');
430 END IF;
431 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'START_ORDER_IMPORT',
432 p_itemtype, p_itemkey, p_actid, p_funcmode);
433 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
434 p_itemtype => p_itemtype,
435 p_itemkey => p_itemkey);
436 OE_STANDARD_WF.Save_Messages;
437 OE_STANDARD_WF.Clear_Msg_Context;
438 IF l_debug_level > 0 THEN
439 oe_debug_pub.add( 'IN EXCEPTION ' || SQLERRM ) ;
440 END IF;
441 IF l_debug_level > 0 THEN
442 oe_debug_pub.add( 'EXITING START_ORDER_IMPORT WITH OTHERS EXCEPTION' ) ;
443 END IF;
444 RAISE;
445 END;
446
447
448 PROCEDURE IS_OI_COMPLETE
449 ( p_itemtype in varchar2,
450 p_itemkey in varchar2,
451 p_actid in number,
452 p_funcmode in varchar2,
453 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
454 )
455 IS
456 l_orig_sys_document_ref VARCHAR2(50);
457 l_errbuf VARCHAR2(2000);
458 l_retcode NUMBER;
459 l_request_id NUMBER;
460 l_exists VARCHAR2(30);
461 l_result VARCHAR2(1);
462 l_activity_result VARCHAR2(30);
463 l_activity_status VARCHAR2(8);
464 l_activity_id NUMBER;
465 l_xml_transaction_type_code VARCHAR2(30);
466 l_sold_to_org_id NUMBER;
467 l_change_sequence VARCHAR2(50);
468 l_customer_key_profile VARCHAR2(1) := 'N';
469
470 --
471 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
472 --
473 BEGIN
474
475 IF p_funcmode = 'RUN' THEN
476 IF l_debug_level > 0 THEN
477 oe_debug_pub.add( 'ENTERING IS_OI_COMPLETE' ) ;
478 END IF;
479 OE_STANDARD_WF.Set_Msg_Context(p_actid);
480
481 get_activity_result( p_itemtype => p_itemtype
482 , p_itemkey => p_itemkey
483 , p_activity_name => 'RUN_ORDER_IMPORT'
484 , x_return_status => l_result
485 , x_activity_result => l_activity_result
486 , x_activity_status_code => l_activity_status
487 , x_activity_id => l_activity_id
488 );
489
490
491 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
492 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
493 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
494 IF l_debug_level > 0 THEN
495 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
496 END IF;
497 End If;
498
499
500 IF l_debug_level > 0 THEN
501 oe_debug_pub.add( 'L_ACTIVITY_ID IS ' || L_ACTIVITY_ID ) ;
502
503 oe_debug_pub.add( 'L_REQUEST_ID IS ' || L_REQUEST_ID ) ;
504 END IF;
505 l_request_id := wf_engine.GetItemAttrNumber( itemtype => p_itemtype
506 , itemkey => p_itemkey
507 , aname => 'REQ_ID'
508 );
509 IF l_debug_level > 0 THEN
510 oe_debug_pub.add( 'L_REQUEST_ID IS ' || L_REQUEST_ID ) ;
511 END IF;
512 Begin
513 Select request_id
514 Into l_request_id
515 From fnd_concurrent_requests
516 Where parent_request_id = l_request_id;
517
518 wf_engine.SetItemAttrNumber (itemtype => p_itemtype,
519 itemkey => p_itemkey,
520 aname => 'REQ_ID',
521 avalue => l_request_id);
522 Exception
523 When Others Then
524 IF l_debug_level > 0 THEN
525 oe_debug_pub.add( 'OTHERS EXCEPTION: SETTING L_REQUEST_ID TO NULL' ) ;
526 END IF;
527 l_request_id := Null;
528 End;
529
530 l_orig_sys_document_ref := wf_engine.GetItemAttrText( itemtype => p_itemtype,
531 itemkey => p_itemkey,
532 aname => 'PARAMETER2'
533 );
534 l_xml_transaction_type_code := wf_engine.GetItemAttrText( itemtype => p_itemtype,
535 itemkey => p_itemkey,
536 aname => 'PARAMETER3'
537 );
538
539 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
540 l_sold_to_org_id := wf_engine.GetItemAttrNumber( itemtype => p_itemtype,
541 itemkey => p_itemkey,
542 aname => 'PARAMETER4'
543 );
544
545 l_change_sequence := wf_engine.GetItemAttrText( itemtype => p_itemtype,
546 itemkey => p_itemkey,
547 aname => 'PARAMETER7'
548 );
549 End If;
550
551 IF l_debug_level > 0 THEN
552 oe_debug_pub.add( 'L_ORIG_SYS_DOCUMENT_REF IS ' || L_ORIG_SYS_DOCUMENT_REF ) ;
553 oe_debug_pub.add( 'L_SOLD_TO_ORG_ID IS ' || L_SOLD_TO_ORG_ID ) ;
554 oe_debug_pub.add( 'L_CHANGE_SEQUENCE IS ' || L_CHANGE_SEQUENCE ) ;
555 END IF;
556
557 -- start exception management
558 OE_MSG_PUB.set_msg_context(
559 p_entity_code => 'ELECMSG_'||p_itemtype
560 ,p_entity_id => p_itemkey
561 ,p_header_id => null
562 ,p_line_id => null
563 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
564 ,p_orig_sys_document_ref => l_orig_sys_document_ref
565 ,p_orig_sys_document_line_ref => null
566 ,p_orig_sys_shipment_ref => null
567 ,p_change_sequence => l_change_sequence
568 ,p_source_document_type_id => null
569 ,p_source_document_id => null
570 ,p_source_document_line_id => null );
571 -- end exception management
572
573 Begin
574 Select request_id
575 Into l_request_id
576 From oe_headers_interface
577 Where order_source_id = 20
578 And orig_sys_document_ref = l_orig_sys_document_ref
579 And request_id = nvl(l_request_id, request_id)
580 And decode(l_customer_key_profile, 'Y',
581 nvl(sold_to_org_id, -999), 1)
582 = decode(l_customer_key_profile, 'Y',
583 nvl(l_sold_to_org_id, -999), 1)
584 And nvl( change_sequence, ' ')
585 = nvl(l_change_sequence, ' ')
586 And xml_transaction_type_code = l_xml_transaction_type_code
587 And error_flag = 'Y';
588 p_x_result := G_WFR_INCOMPLETE;
589 Exception
590 When NO_DATA_FOUND Then
591 IF l_debug_level > 0 THEN
592 oe_debug_pub.add( 'G_WFR_COMPLETE , L_REQUEST_ID = '|| L_REQUEST_ID ) ;
593 END IF;
594 p_x_result := G_WFR_COMPLETE;
595 When OTHERS Then
596 p_x_result := G_WFR_NOT_ELIGIBLE;
597 End;
598
599 wf_engine.SetItemAttrText (itemtype => p_itemtype,
600 itemkey => p_itemkey,
601 aname => 'ORDER_IMPORTED',
602 avalue => p_x_result);
603
604 If p_x_result = G_WFR_INCOMPLETE Then
605 wf_engine.SetItemAttrNumber (itemtype => p_itemtype,
606 itemkey => p_itemkey,
607 aname => 'REQ_ID',
608 avalue => l_request_id);
609 End If;
610
611 OE_STANDARD_WF.Save_Messages;
612 OE_STANDARD_WF.Clear_Msg_Context;
613 IF l_debug_level > 0 THEN
614 oe_debug_pub.add( 'EXITING IS_OI_COMPLETE' ) ;
615 END IF;
616 END IF;
617
618 --
619 -- CANCEL mode - activity 'compensation'
620 --
621 -- This is an event point is called with the effect of the activity must
622 -- be undone, for example when a process is reset to an earlier point
623 -- due to a loop back.
624 --
625 if (p_funcmode = 'CANCEL') then
626
627 -- your cancel code goes here
628 null;
629
630 -- no result needed
631 p_x_result := 'COMPLETE';
632 return;
633 end if;
634
635
636 EXCEPTION
637
638 WHEN OTHERS THEN
639 p_x_result := G_WFR_NOT_ELIGIBLE;
640 wf_engine.SetItemAttrText (itemtype => p_itemtype,
641 itemkey => p_itemkey,
642 aname => 'ORDER_IMPORTED',
643 avalue => p_x_result);
644 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
645 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'is_oi_complete');
646 END IF;
647 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_OI_COMPLETE',
648 p_itemtype, p_itemkey, p_actid, p_funcmode);
649 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
650 p_itemtype => p_itemtype,
651 p_itemkey => p_itemkey);
652 OE_STANDARD_WF.Save_Messages;
653 OE_STANDARD_WF.Clear_Msg_Context;
654 IF l_debug_level > 0 THEN
655 oe_debug_pub.add( 'IN EXCEPTION ' || SQLERRM ) ;
656
657 oe_debug_pub.add( 'EXITING IS_OI_COMPLETE WITH OTHERS EXCEPTION' ) ;
658 END IF;
659 RAISE;
660 END IS_OI_COMPLETE;
661
662 PROCEDURE set_delivery_data
663 ( p_itemtype in varchar2,
664 p_itemkey in varchar2,
665 p_actid in number,
666 p_funcmode in varchar2,
667 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
668 )
669 IS
670
671 l_cust_acct_site_id NUMBER;
672 l_party_id NUMBER;
673 l_party_site_id NUMBER;
674 l_header_id NUMBER;
675 l_document_id NUMBER;
676 l_order_imported VARCHAR2(10);
677 l_start_from_flow Varchar2(6);
678 l_parameter2 NUMBER;
679 l_transaction_type Varchar2(3);
680 l_transaction_subtype Varchar2(6);
681 l_orig_sys_document_ref VARCHAR2(50);
682 l_original_transaction_type VARCHAR2(6);
683 l_xml_transaction_type_code VARCHAR2(30);
684 l_request_id NUMBER;
685 l_sold_to_org_id NUMBER;
686 l_change_sequence VARCHAR2(50);
687 l_org_id NUMBER;
688 --
689 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
690 --
691 BEGIN
692
693 IF p_funcmode = 'RUN' THEN
694 IF l_debug_level > 0 THEN
695 oe_debug_pub.add( 'ENTERING SET_DELIVERY_DATA' ) ;
696
697 oe_debug_pub.add( 'P_ITEMTYPE => ' || P_ITEMTYPE ) ;
698 END IF;
699 IF l_debug_level > 0 THEN
700 oe_debug_pub.add( 'P_ITEMKEY => ' || P_ITEMKEY ) ;
701 END IF;
702 --ecx_debug.g_debug_level := 3;
703 OE_STANDARD_WF.Set_Msg_Context(p_actid);
704 l_start_from_flow := wf_engine.GetItemAttrText( p_itemtype
705 , p_itemkey
706 , 'START_FROM_FLOW'
707 );
708
709 l_orig_sys_document_ref := wf_engine.GetItemAttrText( p_itemtype
710 , p_itemkey
711 , 'ORIG_SYS_DOCUMENT_REF'
712 );
713 l_original_transaction_type := wf_engine.GetItemAttrText( p_itemtype
714 , p_itemkey
715 , 'PARAMETER3'
716 );
717 -- bug 3561088
718 -- Note: for OESO, the document id is the item key and this
719 -- assignment is done directly in the workflow
720 If p_itemtype = G_WFI_ORDER_ACK Then
721 l_document_id := wf_engine.GetItemAttrNumber( p_itemtype
722 , p_itemkey
723 , 'PARAMETER5'
724 , TRUE);
725 End If;
726 -- end bug 3561088
727
728 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
729 l_sold_to_org_id := wf_engine.GetItemAttrNumber( p_itemtype
730 , p_itemkey
731 , 'PARAMETER4'
732 );
733 /* added for the message context setting */
734 l_change_sequence := wf_engine.GetItemAttrText( p_itemtype
735 , p_itemkey
736 , 'PARAMETER7'
737 , TRUE
738 );
739 End If;
740
741 -- start bug 3688227
742 OE_MSG_PUB.set_msg_context(
743 p_entity_code => 'ELECMSG_'||p_itemtype
744 ,p_entity_ref => null
745 ,p_entity_id => p_itemkey
746 ,p_header_id => null
747 ,p_line_id => null
748 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
749 ,p_orig_sys_document_ref => l_orig_sys_document_ref
750 ,p_change_sequence => l_change_sequence
751 ,p_orig_sys_document_line_ref => null
752 ,p_orig_sys_shipment_ref => null
753 ,p_source_document_type_id => null
754 ,p_source_document_id => null
755 ,p_source_document_line_id => null
756 ,p_attribute_code => null
757 ,p_constraint_id => null
758 );
759 -- end bug 3688227
760
761 If (l_start_from_flow = G_WFI_IMPORT_PGM) Then
762 l_request_id := wf_engine.GetItemAttrNumber( p_itemtype
763 , p_itemkey
764 , 'REQ_ID'
765 );
766 Elsif (l_start_from_flow = G_WFI_ORDER_IMPORT) Then
767 l_request_id := wf_engine.GetItemAttrNumber( G_WFI_ORDER_IMPORT
768 , p_itemkey
769 , 'REQ_ID'
770 );
771 End If;
772
773 -- reassign to a meaningful variable name for clarity in SQL statements
774 l_xml_transaction_type_code := l_original_transaction_type;
775
776 IF l_debug_level > 0 THEN
777 oe_debug_pub.add( 'L_ORIG_SYS_DOCUMENT_REF = ' || L_ORIG_SYS_DOCUMENT_REF ) ;
778 oe_debug_pub.add( 'L_ORIGINAL_TRANSACTION_TYPE = ' || L_ORIGINAL_TRANSACTION_TYPE ) ;
779 oe_debug_pub.add ( 'L_XML_TRANSACTION_TYPE_CODE = ' || L_XML_TRANSACTION_TYPE_CODE);
780 oe_debug_pub.add( 'L_SOLD_TO_ORG_ID IS ' || L_SOLD_TO_ORG_ID ) ;
781 oe_debug_pub.add( 'L_DOCUMENT_ID IS ' || L_DOCUMENT_ID ) ;
782 oe_debug_pub.add( 'L_REQUEST_ID = ' || L_REQUEST_ID ) ;
783 END IF;
784
785 If p_itemtype = G_WFI_ORDER_ACK and
786 l_start_from_flow = Oe_Globals.G_WFI_LIN Then
787 p_x_result := G_WFR_NOT_ELIGIBLE;
788 Return;
789 End If;
790
791 IF l_debug_level > 0 THEN
792 oe_debug_pub.add( 'START FROM FLOW = '||L_START_FROM_FLOW ) ;
793 END IF;
794 If l_start_from_flow = G_WFI_ORDER_IMPORT Then
795 IF l_debug_level > 0 THEN
796 oe_debug_pub.add( 'START FROM FLOW = '|| L_START_FROM_FLOW ) ;
797 END IF;
798 l_order_imported := wf_engine.GetItemAttrText( G_WFI_ORDER_IMPORT
799 , p_itemkey
800 , 'ORDER_IMPORTED'
801 );
802 If l_start_from_flow = G_WFI_ORDER_IMPORT And
803 l_order_imported = G_WFR_COMPLETE Then
804 IF l_debug_level > 0 THEN
805 oe_debug_pub.add( 'START FROM FLOW IS OEOI AND IMPORT COMPLETE' ) ;
806 END IF;
807 l_header_id := Oe_Acknowledgment_Pub.get_header_id
808 ( p_orig_sys_document_ref => l_orig_sys_document_ref,
809 p_line_id => Null,
810 p_sold_to_org_id => l_sold_to_org_id);
811 End If;
812
813 Elsif p_itemtype = G_WFI_SHOW_SO Then
814 IF l_debug_level > 0 THEN
815 oe_debug_pub.add( 'ELSIF P_ITEMTYPE = '|| P_ITEMTYPE ) ;
816 END IF;
817 If l_start_from_flow = Oe_Globals.G_WFI_LIN Then
818 IF l_debug_level > 0 THEN
819 oe_debug_pub.add( 'ELSIF L_START_FROM_FLOW = '|| L_START_FROM_FLOW ) ;
820 END IF;
821 l_header_id := Oe_Acknowledgment_Pub.get_header_id
822 ( p_orig_sys_document_ref => Null,
823 p_line_id => p_itemkey,
824 p_sold_to_org_id => l_sold_to_org_id);
825 Elsif l_start_from_flow IN (G_WFI_PROC, G_WFI_CONC_PGM) Then
826 IF l_debug_level > 0 THEN
827 oe_debug_pub.add( 'ELSIF L_START_FROM_FLOW = '|| L_START_FROM_FLOW ) ;
828 END IF;
829 l_header_id := wf_engine.GetItemAttrNumber( G_WFI_SHOW_SO
830 , p_itemkey
831 , 'HEADER_ID'
832 );
833 Elsif l_start_from_flow = Oe_Globals.G_WFI_HDR Then
834 IF l_debug_level > 0 THEN
835 oe_debug_pub.add( 'ELSIF L_START_FROM_FLOW = '|| L_START_FROM_FLOW ) ;
836 END IF;
837 l_header_id := p_itemkey; -- Item Key is the header id when started from header flow
838 End If;
839 l_order_imported := G_WFR_COMPLETE;
840
841 Elsif p_itemtype = G_WFI_ORDER_ACK Then
842 IF l_debug_level > 0 THEN
843 oe_debug_pub.add( 'ELSIF P_ITEMTYPE = '|| P_ITEMTYPE ) ;
844 END IF;
845 If l_start_from_flow = Oe_Globals.G_WFI_HDR Then
846 IF l_debug_level > 0 THEN
847 oe_debug_pub.add( 'ELSIF L_START_FROM_FLOW = '|| L_START_FROM_FLOW ) ;
848 END IF;
849 l_header_id := p_itemkey;
850 Elsif l_start_from_flow in (G_WFI_IMPORT_PGM, G_WFI_PROC) Then
851 --check if the order is imported.
852 l_order_imported := wf_engine.GetItemAttrText( p_itemtype
853 , p_itemkey
854 , 'ORDER_IMPORTED'
855 );
856 If l_order_imported = 'Y' Then
857 l_order_imported := G_WFR_COMPLETE;
858
859 IF l_debug_level > 0 THEN
860 oe_debug_pub.add( 'START FROM FLOW IS OEXVIMPB.PLS' ) ;
861 END IF;
862 l_header_id := Oe_Acknowledgment_Pub.get_header_id
863 ( p_orig_sys_document_ref => l_orig_sys_document_ref,
864 p_line_id => Null,
865 p_sold_to_org_id => l_sold_to_org_id);
866 Else
867 l_order_imported := G_WFR_INCOMPLETE;
868 End If;
869 Else
870 p_x_result := G_WFR_NOT_ELIGIBLE;
871 Return;
872 End If;
873 --l_order_imported := G_WFR_COMPLETE;
874 End If;
875
876 IF l_debug_level > 0 THEN
877 oe_debug_pub.add( 'L_ORDER_IMPORTED = '||L_ORDER_IMPORTED ) ;
878 END IF;
879
880 -- bug 3688227
881 IF l_header_id IS NOT NULL AND l_header_id <> FND_API.G_MISS_NUM THEN
882 OE_MSG_PUB.update_msg_context(
883 p_header_id => l_header_id
884 );
885 END IF;
886 -- end bug 3688227
887
888 Begin
889 If l_order_imported = G_WFR_INCOMPLETE Then
890 IF l_debug_level > 0 THEN
891 oe_debug_pub.add( 'IN IF L_ORDER_IMPORT => ' || L_ORDER_IMPORTED ) ;
892 END IF;
893 -- check the usage of the ship_to_org_id
894 Select sold_to_org_id, ship_to_org_id
895 into l_party_id, l_party_site_id
896 from oe_headers_interface
897 where orig_sys_document_ref = l_orig_sys_document_ref
898 And xml_transaction_type_code = l_xml_transaction_type_code
899 And request_id = l_request_id
900 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add( 'SOLD_TO_ORG_ID IN IF => ' || L_PARTY_ID ) ;
903 END IF;
904 Else
905 IF l_debug_level > 0 THEN
906 oe_debug_pub.add( 'IN ELSIF L_ORDER_IMPORT => ' || L_ORDER_IMPORTED ) ;
907 END IF;
908 Select header_id, sold_to_org_id
909 Into l_header_id, l_party_id
910 From oe_order_headers
911 Where header_id = l_header_id
912 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
913 IF l_debug_level > 0 THEN
914 oe_debug_pub.add( 'HEADER_ID IN ELSIF =>' || L_HEADER_ID ) ;
915
916 oe_debug_pub.add( 'XML_MESSAGE_ID IN ELSIF =>' || L_DOCUMENT_ID ) ;
917
918 oe_debug_pub.add( 'SOLD_TO_ORG_ID IN ELSIF => ' || L_PARTY_ID ) ;
919 END IF;
920
921 End If;
922 Exception
923 /* When no_data_found then
924 IF l_debug_level > 0 THEN
925 oe_debug_pub.add( 'IN WHEN NO DATA FOUND!!!!!') ;
926 oe_debug_pub.add( 'L_PARTY_ID in when no data found => ' || L_PARTY_ID ) ;
927 END IF;*/
928 When Others Then
929 IF l_debug_level > 0 THEN
930 oe_debug_pub.add( 'SELECT OF THE DATA FAILED FOR THE DOCUMENT' ) ;
931 oe_debug_pub.add( 'L_PARTY_ID in when no data found => ' || L_PARTY_ID ) ;
932 END IF;
933 if l_order_imported = G_WFR_INCOMPLETE Then
934 fnd_message.set_name ('ONT', 'OE_OI_CUST_NOT_FOUND');
935 fnd_message.set_token ('OPT_TABLE', 'in oe_headers_interface');
936 fnd_message.set_token ('DOC_ID', l_orig_sys_document_ref);
937 oe_msg_pub.add;
938 else
939 fnd_message.set_name ('ONT', 'OE_OI_CUST_NOT_FOUND');
940 fnd_message.set_token ('OPT_TABLE', 'in oe_order_headers');
941 fnd_message.set_token ('DOC_ID', l_header_id);
942 oe_msg_pub.add;
943 end if;
944 null;
945 End;
946
947
948 IF l_debug_level > 0 THEN
949 oe_debug_pub.add( 'BEFORE SELECT FOR THE ACCOUNT SITE AND PARTY ID' ) ;
950 END IF;
951 l_org_id := MO_GLOBAL.Get_Current_Org_Id;
952 SELECT /* MOAC_SQL_CHANGE */ a.cust_acct_site_id, a.party_site_id, c.party_id
953 Into l_cust_acct_site_id, l_party_site_id, l_party_id
954 From hz_cust_acct_sites_all a, hz_cust_site_uses_all b, hz_cust_accounts c
955 Where a.cust_acct_site_id = b.cust_acct_site_id
956 And a.cust_account_id = l_party_id
957 And a.cust_account_id = c.cust_account_id
958 /* And NVL(a.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),
959 1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
960 NVL(TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO'),1,1),
961 ' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99) */
962 And a.org_id = l_org_id
963 And b.site_use_code = 'SOLD_TO'
964 And b.primary_flag = 'Y'
965 And b.status = 'A'
966 And a.status ='A'; --bug 2752321
967
968 IF l_debug_level > 0 THEN
969 oe_debug_pub.add( 'AFTER SELECT FOR THE ACCOUNT SITE AND PARTY ID AND PARTY_SITE_ID' || L_CUST_ACCT_SITE_ID || ' & ' || L_PARTY_ID || ' & ' || L_PARTY_SITE_ID ) ;
970 END IF;
971 l_transaction_type := Oe_Acknowledgment_Pub.G_TRANSACTION_TYPE;
972 IF l_debug_level > 0 THEN
973 oe_debug_pub.add( 'L_TRANSACTION_TYPE = '||L_TRANSACTION_TYPE ) ;
974 END IF;
975
976 If p_itemtype = G_WFI_SHOW_SO Then
977 l_transaction_subtype := l_original_transaction_type; --Oe_Acknowledgment_Pub.G_TRANSACTION_SSO;
978 Elsif p_itemtype = G_WFI_ORDER_ACK Then
979 l_transaction_subtype := Oe_Acknowledgment_Pub.G_TRANSACTION_POA;
980 End If;
981 IF l_debug_level > 0 THEN
982 oe_debug_pub.add( 'L_TRANSACTION_SUBTYPE = '||L_TRANSACTION_SUBTYPE ) ;
983 END IF;
984
985 wf_engine.SetItemAttrText (itemtype => p_itemtype,
986 itemkey => p_itemkey,
987 aname => 'ECX_TRANSACTION_TYPE',
988 avalue => l_transaction_type);
989
990 wf_engine.SetItemAttrText (itemtype => p_itemtype,
991 itemkey => p_itemkey,
992 aname => 'ECX_TRANSACTION_SUBTYPE',
993 avalue => l_transaction_subtype);
994
995 wf_engine.SetItemAttrText (itemtype => p_itemtype,
996 itemkey => p_itemkey,
997 aname => 'ECX_PARTY_ID',
998 avalue => to_char(l_party_id));
999
1000 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1001 itemkey => p_itemkey,
1002 aname => 'ECX_PARTY_SITE_ID',
1003 avalue => to_char(l_party_site_id));
1004
1005
1006 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1007 itemkey => p_itemkey,
1008 aname => 'ECX_DOCUMENT_ID',
1009 avalue => to_char(l_document_id));
1010
1011 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1012 itemkey => p_itemkey,
1013 aname => 'ECX_PARAMETER1',
1014 avalue => l_orig_sys_document_ref);
1015
1016 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1017 itemkey => p_itemkey,
1018 aname => 'ECX_PARAMETER3',
1019 avalue => l_original_transaction_type);
1020
1021
1022 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1023 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1024 itemkey => p_itemkey,
1025 aname => 'ECX_PARAMETER4',
1026 avalue => to_char(l_sold_to_org_id));
1027
1028 --setting putting sequence value in workflow for 3a6/3a7
1029 If p_itemtype = G_WFI_SHOW_SO then
1030 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1031 itemkey => p_itemkey,
1032 aname => 'ECX_PARAMETER5',
1033 avalue => p_itemkey);
1034 End If;
1035 Else
1036
1037 --setting putting sequence value in workflow for 3a6/3a7
1038 If p_itemtype = G_WFI_SHOW_SO then
1039 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1040 itemkey => p_itemkey,
1041 aname => 'ECX_PARAMETER4',
1042 avalue => p_itemkey);
1043 End If;
1044 End If;
1045
1046 If p_itemtype = G_WFI_ORDER_ACK Then
1047 If (l_start_from_flow = G_WFI_ORDER_IMPORT) Then
1048 l_parameter2 := wf_engine.GetItemAttrNumber
1049 (itemtype => OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT,
1050 itemkey => p_itemkey,
1051 aname => 'REQ_ID');
1052 Elsif (l_start_from_flow = G_WFI_IMPORT_PGM) Then
1053 l_parameter2 := wf_engine.GetItemAttrNumber
1054 (itemtype => OE_ORDER_IMPORT_WF.G_WFI_ORDER_ACK,
1055 itemkey => p_itemkey,
1056 aname => 'REQ_ID');
1057 End If;
1058
1059 wf_engine.SetItemAttrText(itemtype => p_itemtype,
1060 itemkey => p_itemkey,
1061 aname => 'ECX_PARAMETER2',
1062 avalue => l_parameter2);
1063
1064 End If;
1065
1066 p_x_result := 'COMPLETE:COMPLETE';
1067 OE_STANDARD_WF.Save_Messages;
1068 OE_STANDARD_WF.Clear_Msg_Context;
1069 IF l_debug_level > 0 THEN
1070 oe_debug_pub.add( 'EXITING SET_DELIVERY_DATA' ) ;
1071 END IF;
1072
1073 END IF;
1074 --
1075 -- CANCEL mode - activity 'compensation'
1076 --
1077 -- This is an event point is called with the effect of the activity must
1078 -- be undone, for example when a process is reset to an earlier point
1079 -- due to a loop back.
1080 --
1081 if (p_funcmode = 'CANCEL') then
1082
1083 -- your cancel code goes here
1084 null;
1085
1086 -- no result needed
1087 p_x_result := 'COMPLETE';
1088 return;
1089 end if;
1090
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 p_x_result := 'COMPLETE:ERROR';
1094 p_x_result := FND_API.G_RET_STS_ERROR;
1095 fnd_message.set_name ('ONT', 'OE_OI_CUST_SITE_NOT_FOUND');
1096 fnd_message.set_token ('CUST_ID', l_party_id);
1097 oe_msg_pub.add;
1098 OE_STANDARD_WF.Save_Messages;
1099 OE_STANDARD_WF.Clear_Msg_Context;
1100 WHEN OTHERS THEN
1101 p_x_result := 'COMPLETE:ERROR';
1102 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
1103 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1104 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_delivery_data');
1105 END IF;
1106 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'SET_DELIVERY_DATA',
1107 p_itemtype, p_itemkey, p_actid, p_funcmode);
1108 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1109 p_itemtype => p_itemtype,
1110 p_itemkey => p_itemkey);
1111 OE_STANDARD_WF.Save_Messages;
1112 OE_STANDARD_WF.Clear_Msg_Context;
1113 IF l_debug_level > 0 THEN
1114 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1115 END IF;
1116 RAISE;
1117 END set_delivery_data;
1118
1119 PROCEDURE is_partner_setup
1120 ( p_itemtype in varchar2,
1121 p_itemkey in varchar2,
1122 p_actid in number,
1123 p_funcmode in varchar2,
1124 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
1125 )
1126 IS
1127
1128 l_party_id NUMBER;
1129 l_party_site_id NUMBER;
1130 l_header_id NUMBER;
1131 l_document_id NUMBER;
1132 l_order_imported VARCHAR2(10);
1133 l_cust_acct_site_id NUMBER;
1134 l_transaction_type VARCHAR2(25) := 'ONT';
1135 l_transaction_subtype VARCHAR2(25) := 'POA';
1136 l_is_delivery_reqd Varchar2(1);
1137 l_return_status Varchar2(30);
1138 l_orig_sys_document_ref VARCHAR2(50);
1139 l_request_id NUMBER;
1140 l_xml_transaction_type_code VARCHAR2(30);
1141 l_sold_to_org_id NUMBER;
1142 l_change_sequence VARCHAR2(50);
1143 l_customer_key_profile VARCHAR2(1) := 'N';
1144 --
1145 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1146 --
1147 BEGIN
1148
1149 IF p_funcmode = 'RUN' THEN
1150 /*
1151 insert into alok values('222');
1152 insert into alok values(fnd_profile.value('CONC_REQUEST_ID'));
1153 oe_debug_pub.G_DEBUG_MODE := 'FILE';
1154 oe_debug_pub.Debug_ON;
1155 */
1156 IF l_debug_level > 0 THEN
1157 oe_debug_pub.add( 'ENTERING IS_PARTNER_SETUP PROCEDURE' ) ;
1158 END IF;
1159
1160 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1161 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
1162 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
1163 IF l_debug_level > 0 THEN
1164 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
1165 END IF;
1166 End If;
1167
1168 OE_STANDARD_WF.Set_Msg_Context(p_actid);
1169 If p_itemtype = G_WFI_ORDER_IMPORT Then
1170 l_order_imported := wf_engine.GetItemAttrText
1171 (itemtype => p_itemtype,
1172 itemkey => p_itemkey,
1173 aname => 'ORDER_IMPORTED');
1174
1175 IF l_debug_level > 0 THEN
1176 oe_debug_pub.add( 'L_ORDER_IMPORTED ' || L_ORDER_IMPORTED ) ;
1177 END IF;
1178
1179 l_orig_sys_document_ref := wf_engine.GetItemAttrText
1180 (itemtype => p_itemtype,
1181 itemkey => p_itemkey,
1182 aname => 'PARAMETER2');
1183
1184 l_xml_transaction_type_code := wf_engine.GetItemAttrText
1185 (itemtype => p_itemtype,
1186 itemkey => p_itemkey,
1187 aname => 'PARAMETER3');
1188
1189 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1190 l_sold_to_org_id := wf_engine.GetItemAttrNumber
1191 (itemtype => p_itemtype,
1192 itemkey => p_itemkey,
1193 aname => 'PARAMETER4');
1194
1195 l_change_sequence := wf_engine.GetItemAttrText
1196 (itemtype => p_itemtype,
1197 itemkey => p_itemkey,
1198 aname => 'PARAMETER7');
1199 End If;
1200
1201 -- start bug 3688227
1202 OE_MSG_PUB.set_msg_context(
1203 p_entity_code => 'ELECMSG_'||p_itemtype
1204 ,p_entity_ref => null
1205 ,p_entity_id => p_itemkey
1206 ,p_header_id => null
1207 ,p_line_id => null
1208 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1209 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1210 ,p_change_sequence => l_change_sequence
1211 ,p_orig_sys_document_line_ref => null
1212 ,p_orig_sys_shipment_ref => null
1213 ,p_source_document_type_id => null
1214 ,p_source_document_id => null
1215 ,p_source_document_line_id => null
1216 ,p_attribute_code => null
1217 ,p_constraint_id => null
1218 );
1219 -- end bug 3688227
1220
1221 IF l_debug_level > 0 THEN
1222 oe_debug_pub.add( 'L_ORIG_SYS_DOCUMENT_REF ' || L_ORIG_SYS_DOCUMENT_REF ) ;
1223 oe_debug_pub.add( 'L_XML_TRANSACTION_TYPE_CODE ' || L_XML_TRANSACTION_TYPE_CODE ) ;
1224 oe_debug_pub.add( 'L_SOLD_TO_ORG_ID ' || L_SOLD_TO_ORG_ID ) ;
1225 oe_debug_pub.add( 'L_CHANGE_SEQUENCE ' || L_CHANGE_SEQUENCE ) ;
1226 END IF;
1227 l_request_id := wf_engine.GetItemAttrNumber( itemtype => p_itemtype
1228 , itemkey => p_itemkey
1229 , aname => 'REQ_ID'
1230 );
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add( 'L_REQUEST_ID ' || L_REQUEST_ID ) ;
1233 END IF;
1234 If l_order_imported = G_WFR_COMPLETE Then
1235
1236 Select header_id, xml_message_id, sold_to_org_id, ship_to_org_id
1237 Into l_header_id, l_document_id, l_party_id, l_party_site_id
1238 From oe_order_headers
1239 Where order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID
1240 And orig_sys_document_ref = l_orig_sys_document_ref
1241 And decode(l_customer_key_profile, 'Y',
1242 nvl(sold_to_org_id, -999), 1)
1243 = decode(l_customer_key_profile, 'Y',
1244 nvl(l_sold_to_org_id, -999), 1);
1245
1246 Else
1247 -- check the usage of the ship_to_org_id
1248 Select xml_message_id, sold_to_org_id, ship_to_org_id
1249 into l_document_id, l_party_id, l_party_site_id
1250 from oe_headers_interface
1251 where order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID
1252 And orig_sys_document_ref = l_orig_sys_document_ref
1253 And decode(l_customer_key_profile, 'Y',
1254 nvl(sold_to_org_id, -999), 1)
1255 = decode(l_customer_key_profile, 'Y',
1256 nvl(l_sold_to_org_id, -999), 1)
1257 And nvl(change_sequence, ' ')
1258 = nvl(l_change_sequence, ' ')
1259 And xml_transaction_type_code = l_xml_transaction_type_code
1260 And request_id = l_request_id;
1261
1262 End If;
1263
1264 Else
1265 Select header_id, xml_message_id, sold_to_org_id, ship_to_org_id
1266 Into l_header_id, l_document_id, l_party_id, l_party_site_id
1267 From oe_order_headers
1268 Where header_id = p_itemkey
1269 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
1270 End If;
1271
1272 -- start exception management
1273 If l_header_id is not null then
1274 OE_MSG_PUB.update_msg_context(
1275 p_header_id => l_header_id
1276 );
1277 End If;
1278 -- end exception management
1279
1280 IF l_debug_level > 0 THEN
1281 oe_debug_pub.add( 'L_PARTY_ID ' || L_PARTY_ID ) ;
1282 oe_debug_pub.add( 'L_PARTY_SITE_ID ' || L_PARTY_SITE_ID ) ;
1283 oe_debug_pub.add( 'BEFORE CALL TO ISDELIVERY REQ' ) ;
1284 END IF;
1285 OE_Acknowledgment_Pub.Is_Delivery_Required
1286 (
1287 p_customer_id => l_party_id,
1288 p_transaction_type => l_transaction_type,
1289 p_transaction_subtype => l_transaction_subtype,
1290 x_party_id => l_party_id,
1291 x_party_site_id => l_party_site_id,
1292 x_is_delivery_required => l_is_delivery_reqd,
1293 x_return_status => l_return_status
1294 );
1295 IF l_debug_level > 0 THEN
1296 oe_debug_pub.add( 'AFTER CALL TO ISDELIVERY REQ' ) ;
1297 END IF;
1298 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
1299 wf_engine.SetItemAttrText (p_itemtype,
1300 p_itemkey,
1301 'ECX_PARTY_ID',
1302 l_party_id);
1303 wf_engine.SetItemAttrText (p_itemtype,
1304 p_itemkey,
1305 'ECX_PARTY_SITE_ID',
1306 l_party_site_id);
1307 END IF;
1308 p_x_result := l_is_delivery_reqd;
1309 OE_STANDARD_WF.Save_Messages;
1310 OE_STANDARD_WF.Clear_Msg_Context;
1311
1312 END IF;
1313 --
1314 -- CANCEL mode - activity 'compensation'
1315 --
1316 -- This is an event point is called with the effect of the activity must
1317 -- be undone, for example when a process is reset to an earlier point
1318 -- due to a loop back.
1319 --
1320 if (p_funcmode = 'CANCEL') then
1321
1322 -- your cancel code goes here
1323 null;
1324
1325 -- no result needed
1326 p_x_result := 'COMPLETE';
1327 return;
1328 end if;
1329 EXCEPTION
1330 WHEN NO_DATA_FOUND THEN
1331 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
1332 p_x_result := FND_API.G_RET_STS_ERROR;
1333 ELSE
1334 p_x_result := 'N';
1335 END IF;
1336 fnd_message.set_name ('ONT', 'OE_OI_CUST_NOT_FOUND');
1337 fnd_message.set_token ('OPT_TABLE', '');
1338 fnd_message.set_token ('DOC_ID', nvl(l_orig_sys_document_ref,p_itemkey));
1339 oe_msg_pub.add;
1340 OE_STANDARD_WF.Save_Messages;
1341 OE_STANDARD_WF.Clear_Msg_Context;
1342 WHEN OTHERS THEN
1343 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
1344 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
1345 ELSE
1346 p_x_result := 'N';
1347 END IF;
1348 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1349 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'IS_Partner_Setup');
1350 END IF;
1351 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_PARTNER_SETUP',
1352 p_itemtype, p_itemkey, p_actid, p_funcmode);
1353 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1354 p_itemtype => p_itemtype,
1355 p_itemkey => p_itemkey);
1356 OE_STANDARD_WF.Save_Messages;
1357 OE_STANDARD_WF.Clear_Msg_Context;
1358 IF l_debug_level > 0 THEN
1359 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1360 END IF;
1361 RAISE;
1362
1363 END is_partner_setup;
1364
1365 PROCEDURE Process_Xml_Acknowledgment_Wf
1366 ( p_itemtype in varchar2,
1367 p_itemkey in varchar2,
1368 p_actid in number,
1369 p_funcmode in varchar2,
1370 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
1371 )
1372 IS
1373 l_errbuf VARCHAR2(2000);
1374 l_return_status VARCHAR2(30);
1375 l_start_from_flow Varchar2(30);
1376 l_line_id Number;
1377 l_orig_sys_document_ref VARCHAR2(50);
1378 l_transaction_type Varchar2(30);
1379 l_header_id Number;
1380 l_request_id Number;
1381 l_sold_to_org_id Number;
1382 l_change_sequence Varchar2(50);
1383
1384 --
1385 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1386 --
1387 Begin
1388
1389 IF p_funcmode = 'RUN' THEN
1390 IF l_debug_level > 0 THEN
1391 oe_debug_pub.add( 'ENTERING OEXWFOI.PROCESS_XML_ACKNOWLEDGMENT_WF' ) ;
1392 END IF;
1393 OE_STANDARD_WF.Set_Msg_Context(p_actid);
1394
1395 l_start_from_flow := wf_engine.GetItemAttrText( p_itemtype
1396 , p_itemkey
1397 , 'START_FROM_FLOW'
1398 );
1399 IF l_debug_level > 0 THEN
1400 oe_debug_pub.add( 'BEFORE PARAMETER3' ) ;
1401 END IF;
1402 l_transaction_type := wf_engine.GetItemAttrText( p_itemtype
1403 , p_itemkey
1404 , 'PARAMETER3'
1405 );
1406 IF l_debug_level > 0 THEN
1407 oe_debug_pub.add( 'PARAMETER3 ='||L_TRANSACTION_TYPE ) ;
1408 END IF;
1409
1410
1411 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1412 l_sold_to_org_id := wf_engine.GetItemAttrNumber( p_itemtype
1413 , p_itemkey
1414 , 'PARAMETER4'
1415 );
1416
1417
1418 IF l_debug_level > 0 THEN
1419 oe_debug_pub.add( 'SOLD_TO_ORG_ID ='|| L_SOLD_TO_ORG_ID ) ;
1420 END IF;
1421
1422
1423 l_change_sequence := wf_engine.GetItemAttrText( p_itemtype
1424 , p_itemkey
1425 , 'PARAMETER7'
1426 );
1427
1428
1429 IF l_debug_level > 0 THEN
1430 oe_debug_pub.add( 'CHANGE_SEQUENCE ='|| L_CHANGE_SEQUENCE ) ;
1431 END IF;
1432 End If;
1433
1434
1435 If p_itemtype = G_WFI_SHOW_SO Then
1436 IF l_debug_level > 0 THEN
1437 oe_debug_pub.add( 'OEXWFOI: P_ITEMTYPE IS '||P_ITEMTYPE ) ;
1438
1439 oe_debug_pub.add( 'OEXWFOI: P_ITEMKEY IS '||P_ITEMKEY ) ;
1440 END IF;
1441
1442 l_line_id := wf_engine.GetItemAttrNumber( p_itemtype
1443 , p_itemkey
1444 , 'LINE_ID'
1445 );
1446 l_header_id := wf_engine.GetItemAttrNumber( p_itemtype
1447 , p_itemkey
1448 , 'HEADER_ID'
1449 );
1450
1451 -- exception management
1452 l_orig_sys_document_ref := wf_engine.GetItemAttrText( p_itemtype
1453 , p_itemkey
1454 , 'ORIG_SYS_DOCUMENT_REF'
1455 );
1456 -- end exception management
1457 -- start bug 3688227
1458 OE_MSG_PUB.set_msg_context(
1459 p_entity_code => 'ELECMSG_'||p_itemtype
1460 ,p_entity_ref => null
1461 ,p_entity_id => p_itemkey
1462 ,p_header_id => l_header_id
1463 ,p_line_id => null
1464 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1465 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1466 ,p_change_sequence => l_change_sequence
1467 ,p_orig_sys_document_line_ref => null
1468 ,p_orig_sys_shipment_ref => null
1469 ,p_source_document_type_id => null
1470 ,p_source_document_id => null
1471 ,p_source_document_line_id => null
1472 ,p_attribute_code => null
1473 ,p_constraint_id => null
1474 );
1475 -- end bug 3688227
1476
1477
1478 IF l_debug_level > 0 THEN
1479 oe_debug_pub.add( 'OEXWFOIB: RETRIEVING HEADER_ID....HEADER_ID IS '||L_HEADER_ID ) ;
1480 END IF;
1481
1482
1483 OE_Acknowledgment_Pub.Process_Xml_Acknowledgment(
1484 p_orig_sys_document_ref => p_itemkey,
1485 p_itemtype => p_itemtype,
1486 p_start_from_flow => l_start_from_flow,
1487 p_transaction_type => l_transaction_type,
1488 p_line_id => l_line_id,
1489 p_header_id => l_header_id,
1490 p_request_id => to_number(p_itemkey),
1491 p_sold_to_org_id => l_sold_to_org_id,
1492 p_change_sequence => l_change_sequence,
1493 x_return_status => l_return_status);
1494 Else
1495
1496 If l_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT then
1497 l_request_id := wf_engine.GetItemAttrNumber
1498 (itemtype => OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT,
1499 itemkey => p_itemkey,
1500 aname => 'REQ_ID');
1501 Elsif l_start_from_flow = OE_ORDER_IMPORT_WF.G_WFI_IMPORT_PGM then
1502 l_request_id := wf_engine.GetItemAttrNumber
1503 (itemtype => p_itemtype,
1504 itemkey => p_itemkey,
1505 aname => 'REQ_ID');
1506 End if;
1507
1508 IF l_debug_level > 0 THEN
1509 oe_debug_pub.add( 'REQUEST_ID IN ELSE ='||L_REQUEST_ID ) ;
1510
1511 oe_debug_pub.add( 'OEXWFOI: P_ITEMTYPE IS '||P_ITEMTYPE ) ;
1512 END IF;
1513 l_orig_sys_document_ref := wf_engine.GetItemAttrText( p_itemtype
1514 , p_itemkey
1515 , 'ORIG_SYS_DOCUMENT_REF'
1516 );
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add( 'OEXWFOI: REF ='||L_ORIG_SYS_DOCUMENT_REF ) ;
1519 END IF;
1520
1521 -- start bug 3688227
1522 OE_MSG_PUB.set_msg_context(
1523 p_entity_code => 'ELECMSG_'||p_itemtype
1524 ,p_entity_ref => null
1525 ,p_entity_id => p_itemkey
1526 ,p_header_id => null
1527 ,p_line_id => null
1528 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1529 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1530 ,p_change_sequence => l_change_sequence
1531 ,p_orig_sys_document_line_ref => null
1532 ,p_orig_sys_shipment_ref => null
1533 ,p_source_document_type_id => null
1534 ,p_source_document_id => null
1535 ,p_source_document_line_id => null
1536 ,p_attribute_code => null
1537 ,p_constraint_id => null
1538 );
1539 -- end bug 3688227
1540
1541 OE_Acknowledgment_Pub.Process_Xml_Acknowledgment(
1542 p_orig_sys_document_ref => l_orig_sys_document_ref,
1543 p_itemtype => p_itemtype,
1544 p_start_from_flow => l_start_from_flow,
1545 p_transaction_type => l_transaction_type,
1546 p_header_id => l_header_id,
1547 p_request_id => l_request_id,
1548 p_sold_to_org_id => l_sold_to_org_id,
1549 p_change_sequence => l_change_sequence,
1550 x_return_status => l_return_status);
1551 End If;
1552
1553 IF l_debug_level > 0 THEN
1554 oe_debug_pub.add( 'EXITING OEXWFOI.PROCESS_XML_ACKNOWLEDGMENT_WF' ) ;
1555 END IF;
1556
1557 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
1558 p_x_result := 'COMPLETE:COMPLETE';
1559 ELSE
1560 p_x_result := G_WFR_COMPLETE;
1561 END IF;
1562 OE_STANDARD_WF.Save_Messages;
1563 OE_STANDARD_WF.Clear_Msg_Context;
1564
1565 END IF;
1566 --
1567 -- CANCEL mode - activity 'compensation'
1568 --
1569 -- This is an event point is called with the effect of the activity must
1570 -- be undone, for example when a process is reset to an earlier point
1571 -- due to a loop back.
1572 --
1573 if (p_funcmode = 'CANCEL') then
1574
1575 -- your cancel code goes here
1576 null;
1577
1578 -- no result needed
1579 p_x_result := 'COMPLETE';
1580 return;
1581 end if;
1582 exception
1583 when others then
1584 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
1585 p_x_result := 'COMPLETE:ERROR';
1586 ELSE
1587 p_x_result := G_WFR_INCOMPLETE;
1588 END IF;
1589 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1590 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'process_xml_acknowledgment_wf');
1591 END IF;
1592 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'PROCESS_XML_ACKNOWLEDGMENT_WF',
1593 p_itemtype, p_itemkey, p_actid, p_funcmode);
1594 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1595 p_itemtype => p_itemtype,
1596 p_itemkey => p_itemkey);
1597 OE_STANDARD_WF.Save_Messages;
1598 OE_STANDARD_WF.Clear_Msg_Context;
1599 IF l_debug_level > 0 THEN
1600 oe_debug_pub.add( 'EXITING WITH WHEN OTHERS EXCEPTION' ) ;
1601 END IF;
1602 RAISE;
1603 End Process_Xml_Acknowledgment_Wf;
1604
1605 PROCEDURE Get_Activity_Result
1606 ( p_itemtype IN VARCHAR2
1607 , p_itemkey IN VARCHAR2
1608 , p_activity_name IN VARCHAR2
1609 , x_return_status OUT NOCOPY VARCHAR2
1610
1611 , x_activity_result OUT NOCOPY VARCHAR2
1612
1613 , x_activity_status_code OUT NOCOPY VARCHAR2
1614
1615 , x_activity_id OUT NOCOPY NUMBER
1616
1617 )
1618 IS
1619
1620 --
1621 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1622 --
1623 BEGIN
1624
1625 IF l_debug_level > 0 THEN
1626 oe_debug_pub.add( 'ENTERING OE_OI_WF.GET_ACTIVITY_RESULT '|| P_ITEMTYPE||'/'||P_ITEMKEY||'/'||P_ACTIVITY_NAME , 1 ) ;
1627 END IF;
1628
1629 SELECT wias.ACTIVITY_STATUS, wias.ACTIVITY_RESULT_CODE, wias.PROCESS_ACTIVITY
1630 INTO x_activity_status_code, x_activity_result, x_activity_id
1631 FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
1632 WHERE wias.ITEM_KEY = p_itemkey
1633 AND wias.ITEM_TYPE = p_itemtype
1634 AND wpa.ACTIVITY_NAME = p_activity_name
1635 AND wias.PROCESS_ACTIVITY = wpa.INSTANCE_ID;
1636
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638
1639 IF l_debug_level > 0 THEN
1640 oe_debug_pub.add( 'EXITING OE_OI_WF.GET_ACTIVITY_RESULT '||X_ACTIVITY_RESULT||'/'||X_RETURN_STATUS , 1 ) ;
1641 END IF;
1642
1643 EXCEPTION
1644 WHEN NO_DATA_FOUND THEN
1645 x_return_status := FND_API.G_RET_STS_ERROR;
1646 WHEN OTHERS THEN
1647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1648 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1649 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Get_Activity_Result');
1650 END IF;
1651 IF l_debug_level > 0 THEN
1652 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1653 END IF;
1654
1655 END Get_Activity_Result;
1656
1657 Procedure Raise_Event_Showso_Wf
1658 ( p_itemtype in varchar2,
1659 p_itemkey in varchar2,
1660 p_actid in number,
1661 p_funcmode in varchar2,
1662 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
1663 )
1664 Is
1665 --l_return_status varchar2(1);
1666 l_sold_to_org_id number;
1667 l_orig_sys_document_ref varchar2(50);
1668 l_order_source_id Number;
1669 l_change_sequence varchar2(50);
1670 l_is_delivery_reqd varchar2(1) := 'N';
1671 l_return_status Varchar2(30);
1672 l_party_id Number;
1673 l_party_site_id Number;
1674 l_order_number Number;
1675 l_order_type_id Number;
1676 l_org_id Number;
1677 --
1678 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1679 --
1680 Begin
1681
1682 IF p_funcmode = 'RUN' THEN
1683 OE_STANDARD_WF.Set_Msg_Context(p_actid);
1684 If p_itemtype = Oe_Globals.G_WFI_HDR Then
1685 SELECT order_source_id, orig_sys_document_ref, sold_to_org_id, change_sequence, order_number, order_type_id, org_id
1686 INTO l_order_source_id, l_orig_sys_document_ref, l_sold_to_org_id, l_change_sequence, l_order_number, l_order_type_id, l_org_id
1687 FROM oe_order_headers
1688 WHERE header_id = p_itemkey;
1689
1690 -- start bug 3688227
1691 -- this message context should be header, since this activity will
1692 -- be called from the order workflow
1693 OE_MSG_PUB.set_msg_context(
1694 p_entity_code => 'HEADER'
1695 ,p_entity_ref => null
1696 ,p_entity_id => null
1697 ,p_header_id => to_number(p_itemkey)
1698 ,p_line_id => null
1699 ,p_order_source_id => l_order_source_id -- CANNOT hardcode 20 here
1700 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1701 ,p_change_sequence => l_change_sequence
1702 ,p_orig_sys_document_line_ref => null
1703 ,p_orig_sys_shipment_ref => null
1704 ,p_source_document_type_id => null
1705 ,p_source_document_id => null
1706 ,p_source_document_line_id => null
1707 ,p_attribute_code => null
1708 ,p_constraint_id => null
1709 );
1710 -- end bug 3688227
1711
1712 OE_Acknowledgment_Pub.Raise_Event_XMLInt (
1713 p_order_source_id => l_order_source_id,
1714 p_partner_document_num => l_orig_sys_document_ref,
1715 p_sold_to_org_id => l_sold_to_org_id,
1716 p_itemtype => NULL,
1717 p_itemkey => NULL,
1718 p_transaction_type => NULL,
1719 p_message_text => NULL,
1720 p_document_num => l_order_number,
1721 p_order_type_id => l_order_type_id,
1722 p_change_sequence => l_change_sequence,
1723 p_org_id => l_org_id,
1724 p_header_id => p_itemkey,
1725 p_subscriber_list => 'DEFAULT',
1726 p_line_ids => 'ALL',
1727 x_return_status => l_return_status);
1728
1729 IF l_order_source_id = OE_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID THEN
1730 OE_Acknowledgment_Pub.Is_Delivery_Required
1731 (
1732 p_customer_id => l_sold_to_org_id,
1733 p_transaction_type => OE_Acknowledgment_Pub.G_TRANSACTION_TYPE,
1734 p_transaction_subtype => OE_Acknowledgment_Pub.G_TRANSACTION_SSO,
1735 x_party_id => l_party_id,
1736 x_party_site_id => l_party_site_id,
1737 x_is_delivery_required => l_is_delivery_reqd,
1738 x_return_status => l_return_status
1739 );
1740 IF l_is_delivery_reqd = 'Y' THEN
1741
1742 Oe_Acknowledgment_Pub.Raise_Event_Showso
1743 (p_header_id => p_itemkey,
1744 p_line_id => Null,
1745 p_customer_id => l_sold_to_org_id,
1746 p_orig_sys_document_ref => l_orig_sys_document_ref,
1747 p_change_sequence => l_change_sequence,
1748 p_itemtype => G_WFI_CONC_PGM,
1749 p_party_id => l_party_id,
1750 p_party_site_id => l_party_site_id,
1751 p_commit_flag => 'N',
1752 x_return_status => l_return_status);
1753 ELSE
1754 l_return_status := fnd_api.g_ret_sts_success;
1755 END IF;
1756 ELSE
1757 l_return_status := fnd_api.g_ret_sts_success;
1758 END IF;
1759 Elsif p_itemtype = Oe_Globals.G_WFI_LIN Then
1760 Oe_Acknowledgment_Pub.Raise_Event_Showso
1761 (p_header_id => Null,
1762 p_line_id => p_itemkey,
1763 p_customer_id => Null,
1764 p_orig_sys_document_ref => Null,
1765 p_itemtype => Null, --12985628 changed p_item_type from p_item_type to NULL
1766 p_commit_flag => 'N', --12985628 added p_commit_flag as 'N' to avoid p COMMIT when this activity is used in Line workflow
1767 x_return_status => l_return_status);
1768 End If;
1769
1770 if l_return_status = fnd_api.g_ret_sts_success then
1771 p_x_result := 'COMPLETE:COMPLETE';
1772 else
1773 p_x_result := 'COMPLETE:ERROR';
1774 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1775 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Showso_Wf');
1776 END IF;
1777 IF l_debug_level > 0 THEN
1778 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1779 END IF;
1780 end if;
1781 OE_STANDARD_WF.Save_Messages;
1782 OE_STANDARD_WF.Clear_Msg_Context;
1783
1784 END IF;
1785 --
1786 -- CANCEL mode - activity 'compensation'
1787 --
1788 -- This is an event point is called with the effect of the activity must
1789 -- be undone, for example when a process is reset to an earlier point
1790 -- due to a loop back.
1791 --
1792 if (p_funcmode = 'CANCEL') then
1793
1794 -- your cancel code goes here
1795 null;
1796
1797 -- no result needed
1798 p_x_result := 'COMPLETE';
1799 return;
1800 end if;
1801 Exception
1802 When Others Then
1803 p_x_result := 'COMPLETE:ERROR';
1804 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1805 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Showso_Wf');
1806 END IF;
1807 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'RAISE_EVENT_SHOWSO_WF',
1808 p_itemtype, p_itemkey, p_actid, p_funcmode);
1809 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1810 p_itemtype => p_itemtype,
1811 p_itemkey => p_itemkey);
1812 OE_STANDARD_WF.Save_Messages;
1813 OE_STANDARD_WF.Clear_Msg_Context;
1814 IF l_debug_level > 0 THEN
1815 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1816 END IF;
1817 RAISE;
1818 End Raise_Event_Showso_Wf;
1819
1820 Procedure Set_User_Key
1821 ( p_itemtype in varchar2,
1822 p_itemkey in varchar2,
1823 p_actid in number,
1824 p_funcmode in varchar2,
1825 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
1826 )
1827 Is
1828
1829 l_user_key Varchar2(240);
1830 l_transaction_type Varchar2(10);
1831 l_orig_sys_document_ref Varchar2(50) := NULL;
1832 l_customer_number Number;
1833 l_header_id Number;
1834 l_change_sequence Varchar2(50);
1835 --
1836 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1837 --
1838 Begin
1839
1840 IF p_funcmode = 'RUN' THEN
1841 OE_STANDARD_WF.Set_Msg_Context(p_actid);
1842 IF l_debug_level > 0 THEN
1843 oe_debug_pub.add( 'ENTERING SET_USER_KEY' ) ;
1844 END IF;
1845
1846 if (p_itemtype = OE_ORDER_IMPORT_WF.G_WFI_SHOW_SO) then
1847 l_header_id := wf_engine.GetItemAttrNumber(p_itemtype,
1848 p_itemkey,
1849 'HEADER_ID'
1850 );
1851 l_transaction_type := wf_engine.GetItemAttrText(p_itemtype,
1852 p_itemkey,
1853 'PARAMETER3'
1854 );
1855 begin
1856 select orig_sys_document_ref, sold_to_org_id, change_sequence
1857 into l_orig_sys_document_ref, l_customer_number, l_change_sequence
1858 from oe_order_headers
1859 where header_id = l_header_id;
1860 exception
1861 when others then
1862 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1863 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_user_key');
1864 END IF;
1865 -- in this case, we do the best we can, and just use the customer and transaction type for the user key
1866 end;
1867
1868 -- start exception management
1869 OE_MSG_PUB.set_msg_context(
1870 p_entity_code => 'ELECMSG_'||p_itemtype
1871 ,p_entity_id => to_number(p_itemkey)
1872 ,p_header_id => l_header_id
1873 ,p_line_id => null
1874 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1875 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1876 ,p_orig_sys_document_line_ref => null
1877 ,p_orig_sys_shipment_ref => null
1878 ,p_change_sequence => l_change_sequence
1879 ,p_source_document_type_id => null
1880 ,p_source_document_id => null
1881 ,p_source_document_line_id => null );
1882 -- end exception management
1883
1884 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1885 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_change_sequence || ',' || l_transaction_type; --OE_ACKNOWLEDGMENT_PUB.G_TRANSACTION_SSO;
1886 Else
1887 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_transaction_type; --OE_ACKNOWLEDGMENT_PUB.G_TRANSACTION_SSO;
1888 End If;
1889 IF l_debug_level > 0 THEN
1890 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1891 END IF;
1892 elsif (p_itemtype = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT) then
1893 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype,
1894 p_itemkey,
1895 'PARAMETER2'
1896 );
1897 l_customer_number := wf_engine.GetItemAttrNumber(p_itemtype,
1898 p_itemkey,
1899 'PARAMETER4'
1900 );
1901 l_transaction_type := wf_engine.GetItemAttrText(p_itemtype,
1902 p_itemkey,
1903 'PARAMETER3'
1904 );
1905
1906 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1907 l_change_sequence := wf_engine.GetItemAttrText(p_itemtype,
1908 p_itemkey,
1909 'PARAMETER7'
1910 );
1911 -- start exception management
1912 OE_MSG_PUB.set_msg_context(
1913 p_entity_code => 'ELECMSG_'||p_itemtype
1914 ,p_entity_id => p_itemkey
1915 ,p_header_id => null
1916 ,p_line_id => null
1917 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1918 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1919 ,p_orig_sys_document_line_ref => null
1920 ,p_orig_sys_shipment_ref => null
1921 ,p_change_sequence => l_change_sequence
1922 ,p_source_document_type_id => null
1923 ,p_source_document_id => null
1924 ,p_source_document_line_id => null );
1925 -- end exception management
1926
1927 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_change_sequence || ',' || l_transaction_type;
1928 Else
1929 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_transaction_type;
1930 End If;
1931
1932
1933 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1934 itemkey => p_itemkey,
1935 aname => 'USER_KEY',
1936 avalue => l_user_key);
1937
1938 IF l_debug_level > 0 THEN
1939 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1940 END IF;
1941 else -- now we will just assume that it is always passed in at this point
1942 -- TODO: we may need another else if to handle the ack being called from the conc_pgm or other workflow.
1943 l_user_key := wf_engine.GetItemAttrText(p_itemtype,
1944 p_itemkey,
1945 'USER_KEY'
1946 );
1947 IF l_debug_level > 0 THEN
1948 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1949 END IF;
1950
1951 end if;
1952 wf_engine.SetItemUserKey(itemtype => p_itemtype,
1953 itemkey => p_itemkey,
1954 userkey => l_user_key);
1955
1956
1957 p_x_result := 'COMPLETE:COMPLETE';
1958 IF l_debug_level > 0 THEN
1959 oe_debug_pub.add( 'EXITING SET_USER_KEY' ) ;
1960 END IF;
1961
1962 OE_STANDARD_WF.Save_Messages;
1963 OE_STANDARD_WF.Clear_Msg_Context;
1964
1965 END IF;
1966 --
1967 -- CANCEL mode - activity 'compensation'
1968 --
1969 -- This is an event point is called with the effect of the activity must
1970 -- be undone, for example when a process is reset to an earlier point
1971 -- due to a loop back.
1972 --
1973 if (p_funcmode = 'CANCEL') then
1974
1975 -- your cancel code goes here
1976 null;
1977
1978 -- no result needed
1979 p_x_result := 'COMPLETE';
1980 return;
1981 end if;
1982 Exception
1983 When Others Then
1984 p_x_result := 'COMPLETE:ERROR';
1985 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1986 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_user_key');
1987 END IF;
1988 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'SET_USER_KEY',
1989 p_itemtype, p_itemkey, p_actid, p_funcmode);
1990 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1991 p_itemtype => p_itemtype,
1992 p_itemkey => p_itemkey);
1993 OE_STANDARD_WF.Save_Messages;
1994 OE_STANDARD_WF.Clear_Msg_Context;
1995 RAISE;
1996 End Set_User_Key;
1997
1998 Procedure Is_CBOD_Out_Reqd
1999 ( p_itemtype in varchar2,
2000 p_itemkey in varchar2,
2001 p_actid in number,
2002 p_funcmode in varchar2,
2003 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2004 ) is
2005
2006 l_confirmation VARCHAR2(2000);
2007 l_message_id NUMBER;
2008 --
2009 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2010 --
2011 Begin
2012
2013 IF p_funcmode = 'RUN' THEN
2014 IF l_debug_level > 0 THEN
2015 oe_debug_pub.add( 'ENTERING IS_CBOD_OUT_REQD' ) ;
2016 END IF;
2017 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2018 l_message_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2019 itemkey => p_itemkey,
2020 aname => 'PARAMETER5');
2021 IF l_debug_level > 0 THEN
2022 oe_debug_pub.add( 'L_MESSAGE_ID : '|| L_MESSAGE_ID ) ;
2023 END IF;
2024 Begin
2025 select confirmation
2026 into l_confirmation
2027 from ecx_oag_cbod_v
2028 where document_id = l_message_id;
2029 Exception
2030 When NO_DATA_FOUND then
2031 IF l_debug_level > 0 THEN
2032 oe_debug_pub.add( 'NO DATA FOUND IN IS_CBOD_OUT_REQD , RETURNING FALSE I.E. DO NOT SEND CBOD' ) ;
2033 END IF;
2034 p_x_result := 'F';
2035 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2036 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'is_cbod_out_reqd');
2037 END IF;
2038 When OTHERS then
2039 IF l_debug_level > 0 THEN
2040 oe_debug_pub.add( 'OTHERS IN IS_CBOD_OUT_REQD , RETURNING FALSE I.E. DO NOT SEND CBOD' ) ;
2041 END IF;
2042 p_x_result := 'F';
2043 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2044 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'is_cbod_out_reqd');
2045 END IF;
2046 End;
2047 IF l_debug_level > 0 THEN
2048 oe_debug_pub.add( 'L_CONFIRMATION : '|| L_CONFIRMATION ) ;
2049 END IF;
2050 -- since we are only dealing with successes in our implementation,
2051 -- we only issue the cbod when l_confirmation is set to 2
2052 If l_confirmation = '2' then
2053 p_x_result := 'T';
2054 Else p_x_result := 'F';
2055 End If;
2056
2057 --p_x_result := 'F';
2058 IF l_debug_level > 0 THEN
2059 oe_debug_pub.add( 'RETURN VALUE FOR IS_CBOD_OUT_REQD: ' || P_X_RESULT ) ;
2060 END IF;
2061 OE_STANDARD_WF.Save_Messages;
2062 OE_STANDARD_WF.Clear_Msg_Context;
2063
2064 END IF;
2065 --
2066 -- CANCEL mode - activity 'compensation'
2067 --
2068 -- This is an event point is called with the effect of the activity must
2069 -- be undone, for example when a process is reset to an earlier point
2070 -- due to a loop back.
2071 --
2072 if (p_funcmode = 'CANCEL') then
2073
2074 -- your cancel code goes here
2075 null;
2076
2077 -- no result needed
2078 p_x_result := 'COMPLETE';
2079 return;
2080 end if;
2081 Exception
2082 When Others Then
2083 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_CBOD_OUT_REQD',
2084 p_itemtype, p_itemkey, p_actid, p_funcmode);
2085 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2086 p_itemtype => p_itemtype,
2087 p_itemkey => p_itemkey);
2088 OE_STANDARD_WF.Save_Messages;
2089 OE_STANDARD_WF.Clear_Msg_Context;
2090 RAISE;
2091 End Is_CBOD_Out_Reqd;
2092
2093 /* This function is no longer used but we might use it in the future */
2094 Procedure Populate_CBOD_Out_Globals
2095 ( p_itemtype in varchar2,
2096 p_itemkey in varchar2,
2097 p_actid in number,
2098 p_funcmode in varchar2,
2099 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2100 ) Is
2101
2102 --
2103 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2104 --
2105 Begin
2106 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2107
2108 p_x_result := 'COMPLETE:COMPLETE'; --need this to avoid runtime error
2109 OE_STANDARD_WF.Save_Messages;
2110 OE_STANDARD_WF.Clear_Msg_Context;
2111 End Populate_CBOD_Out_Globals;
2112
2113 Procedure Set_CBOD_EVENT_KEY
2114 ( p_itemtype in varchar2,
2115 p_itemkey in varchar2,
2116 p_actid in number,
2117 p_funcmode in varchar2,
2118 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2119 ) Is
2120 l_event_key_num number;
2121 --
2122 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2123 --
2124 Begin
2125
2126 IF p_funcmode = 'RUN' THEN
2127 /* l_event_key_num := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2128 itemkey => p_itemkey,
2129 aname => 'PARAMETER5');
2130 */
2131 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2132 select oe_xml_message_seq_s.nextval
2133 into l_event_key_num
2134 from dual;
2135
2136 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2137 itemkey => p_itemkey,
2138 aname => 'CBOD_EVENT_KEY',
2139 avalue => to_char(l_event_key_num));
2140 p_x_result := to_char(l_event_key_num);
2141 OE_STANDARD_WF.Save_Messages;
2142 OE_STANDARD_WF.Clear_Msg_Context;
2143
2144 END IF;
2145 --
2146 -- CANCEL mode - activity 'compensation'
2147 --
2148 -- This is an event point is called with the effect of the activity must
2149 -- be undone, for example when a process is reset to an earlier point
2150 -- due to a loop back.
2151 --
2152 if (p_funcmode = 'CANCEL') then
2153
2154 -- your cancel code goes here
2155 null;
2156
2157 -- no result needed
2158 p_x_result := 'COMPLETE';
2159 return;
2160 end if;
2161 Exception
2162 WHEN OTHERS then
2163 p_x_result := NULL;
2164 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2165 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_cbod_event_key');
2166 END IF;
2167 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'SET_CBOD_EVENT_KEY',
2168 p_itemtype, p_itemkey, p_actid, p_funcmode);
2169 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2170 p_itemtype => p_itemtype,
2171 p_itemkey => p_itemkey);
2172 OE_STANDARD_WF.Save_Messages;
2173 OE_STANDARD_WF.Clear_Msg_Context;
2174 RAISE;
2175 End Set_cbod_event_key;
2176
2177
2178
2179
2180 Procedure Raise_Event_Xmlint_Wf
2181 ( p_itemtype IN Varchar2,
2182 p_itemkey IN Varchar2,
2183 p_actid in number,
2184 p_funcmode IN Varchar2,
2185 p_x_result IN OUT NOCOPY /* file.sql.39 change */ Varchar2
2186 )
2187 Is
2188 l_transaction_type Varchar2(30);
2189 l_transaction_type_test1 Varchar2(30);
2190 l_transaction_type_test2 Varchar2(30);
2191 l_header_id Number;
2192 l_document_num Number;
2193 l_order_type_id Number;
2194 l_orig_sys_document_ref Varchar2(50);
2195 l_message_text Varchar2(500);
2196 l_return_status varchar2(1);
2197 l_processing_stage varchar2(30);
2198 l_doc_status varchar2(240);
2199 l_import_mode varchar2(15);
2200 l_setup_status varchar2(15);
2201 l_org_id NUMBER;
2202 l_document_id NUMBER;
2203 l_document_direction VARCHAR2(6);
2204 l_party_id NUMBER;
2205 l_party_site_id NUMBER;
2206 l_change_sequence VARCHAR2(50);
2207 l_sold_to_org_id NUMBER;
2208 l_conc_request_id NUMBER;
2209 l_xml_message_id Varchar2(240);
2210 l_internal_control_number Number;
2211 l_txn_token Varchar2(50);
2212 l_document_disposition VARCHAR2(20);
2213 l_customer_key_profile VARCHAR2(1) := 'N';
2214 l_response_flag VARCHAR2(1);
2215 l_standard_desc VARCHAR2(80);
2216 l_failure_ack_flag VARCHAR2(10) := 'N';
2217 --
2218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2219 --
2220 Begin
2221
2222 IF p_funcmode = 'RUN' THEN
2223 IF l_debug_level > 0 THEN
2224 oe_debug_pub.add( 'ENTERING RAISE_EVENT_XMLINT_WF' ) ;
2225 oe_debug_pub.add( 'P_ITEMKEY => ' || P_ITEMKEY ) ;
2226 oe_debug_pub.add( 'P_ITEMTYPE => ' || P_ITEMTYPE ) ;
2227 END IF;
2228
2229 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2230
2231 -- bug 3688227
2232 -- start bug 3688227
2233 -- this is all the info we have at this point
2234 OE_MSG_PUB.set_msg_context(
2235 p_entity_code => 'ELECMSG_'||p_itemtype
2236 ,p_entity_id => p_itemkey
2237 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
2238 );
2239 -- end bug 3688227
2240
2241 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
2242 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
2243 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
2244 IF l_debug_level > 0 THEN
2245 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
2246 END IF;
2247 End If;
2248
2249
2250 l_transaction_type_test1 := wf_engine.GetItemAttrText( p_itemtype
2251 , p_itemkey
2252 , 'ECX_TRANSACTION_SUBTYPE'
2253 );
2254
2255 l_transaction_type_test2 := wf_engine.GetItemAttrText( p_itemtype
2256 , p_itemkey
2257 , 'PARAMETER3'
2258 );
2259
2260 If l_transaction_type_test1 = Oe_Acknowledgment_Pub.G_TRANSACTION_POA
2261 OR l_transaction_type_test1 = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO Then
2262 --POA
2263 l_transaction_type := l_transaction_type_test1;
2264 Else
2265 --POI, CPO, or SSO
2266 l_transaction_type := l_transaction_type_test2;
2267 End If;
2268
2269 IF l_debug_level > 0 THEN
2270 oe_debug_pub.add( 'L_TRANSACTION_TYPE => ' || L_TRANSACTION_TYPE ) ;
2271 oe_debug_pub.add( 'L_TRANSACTION_TYPE_TEST1 => ' || L_TRANSACTION_TYPE_TEST1 ) ;
2272 oe_debug_pub.add( 'L_TRANSACTION_TYPE_TEST2 => ' || L_TRANSACTION_TYPE_TEST2 ) ;
2273 END IF;
2274
2275 l_processing_stage := wf_engine.GetActivityAttrText (p_itemtype,
2276 p_itemkey,
2277 p_actid,
2278 'PROCESSING_STAGE',
2279 TRUE);
2280
2281 --setting Orig Sys Document Ref (Partner Document Number) for SSO
2282 If l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO
2283 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO
2284 Then
2285 l_header_id := wf_engine.GetItemAttrNumber( p_itemtype
2286 , p_itemkey
2287 , 'HEADER_ID'
2288 );
2289 -- start bug 3688227
2290 OE_MSG_PUB.update_msg_context(
2291 p_header_id => l_header_id
2292 );
2293 -- end bug 3688227
2294
2295 IF l_debug_level > 0 THEN
2296 oe_debug_pub.add( 'L_HEADER_ID => ' || L_HEADER_ID ) ;
2297 END IF;
2298
2299 Begin
2300 Select orig_sys_document_ref
2301 Into l_orig_sys_document_ref
2302 From oe_order_headers
2303 Where header_id = l_header_id;
2304 -- start bug 3688227
2305 OE_MSG_PUB.update_msg_context(
2306 p_orig_sys_document_ref => l_orig_sys_document_ref
2307 );
2308 -- end bug 3688227
2309 Exception
2310 When Others Then
2311 p_x_result := 'COMPLETE:ERROR';
2312 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2313 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2314 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2315 END IF;
2316 -- start data fix project
2317 -- OE_STANDARD_WF.Save_Messages;
2318 -- OE_STANDARD_WF.Clear_Msg_Context;
2319 -- end data fix project
2320 End;
2321
2322 l_xml_message_id := wf_engine.GetItemAttrText( p_itemtype
2323 , p_itemkey
2324 , 'ECX_MSGID_ATTR'
2325 , TRUE
2326 );
2327
2328 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2329 -- also get the request id for 3A6 Conc Pgm,
2330 -- don't check for Start From Flow Item Attr because it is
2331 -- unnecessary, request id will be null for non-conc pgm cases
2332 IF l_transaction_type = OE_Acknowledgment_Pub.G_TRANSACTION_SSO THEN
2333 l_conc_request_id := wf_engine.GetItemAttrNumber(p_itemtype
2334 , p_itemkey
2335 , 'REQ_ID'
2336 , TRUE);
2337 END IF;
2338 END IF;
2339
2340 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2341 l_processing_stage := 'OUTBOUND_SENT';
2342 END IF;
2343 Elsif ((l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI) OR
2344 (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO) OR
2345 (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO)) Then
2346 --setting Orig Sys Document Ref for POI or CPO
2347
2348 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype
2349 , p_itemkey
2350 , 'PARAMETER2'
2351 );
2352 -- start bug 3688227
2353 OE_MSG_PUB.update_msg_context(
2354 p_orig_sys_document_ref => l_orig_sys_document_ref
2355 );
2356 -- end bug 3688227
2357
2358 l_internal_control_number := wf_engine.GetItemAttrNumber( p_itemtype
2359 , p_itemkey
2360 , 'PARAMETER5'
2361 );
2362
2363 --also get the request id for OI
2364 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2365 l_conc_request_id := wf_engine.GetItemAttrNumber(p_itemtype
2366 , p_itemkey
2367 , 'REQ_ID'
2368 , TRUE);
2369 IF l_transaction_type = OE_Acknowledgment_Pub.G_TRANSACTION_CHO THEN
2370 l_response_flag := wf_engine.GetItemAttrText(p_itemtype
2371 , p_itemkey
2372 , 'PARAMETER10'
2373 , TRUE);
2374 END IF;
2375 END IF;
2376
2377 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2378 l_processing_stage := 'IMPORT_SUCCESS';
2379 END IF;
2380 Else
2381
2382 --setting Orig Sys Document Ref for POA and CBODO
2383 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype
2384 , p_itemkey
2385 , 'ORIG_SYS_DOCUMENT_REF'
2386 );
2387 -- start bug 3688227
2388 OE_MSG_PUB.update_msg_context(
2389 p_orig_sys_document_ref => l_orig_sys_document_ref
2390 );
2391 -- end bug 3688227
2392
2393 l_xml_message_id := wf_engine.GetItemAttrText( p_itemtype
2394 , p_itemkey
2395 , 'ECX_MSGID_ATTR'
2396 , TRUE
2397 );
2398 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2399 l_processing_stage := 'OUTBOUND_SENT';
2400 END IF;
2401 End If;
2402 --Done Setting Orig Sys Document Ref (Partner Document Number)
2403
2404
2405
2406 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2407 l_org_id := wf_engine.GetItemAttrNumber ( p_itemtype
2408 , p_itemkey
2409 , 'ORG_ID'
2410 , TRUE
2411 );
2412 l_sold_to_org_id := wf_engine.GetItemAttrNumber( p_itemtype
2413 , p_itemkey
2414 , 'PARAMETER4'
2415 , TRUE);
2416 l_change_sequence := wf_engine.GetItemAttrText( p_itemtype
2417 , p_itemkey
2418 , 'PARAMETER7'
2419 , TRUE);
2420 -- start bug 3688227
2421 OE_MSG_PUB.update_msg_context(
2422 p_change_sequence => l_change_sequence
2423 );
2424 -- end bug 3688227
2425 END IF;
2426
2427 --Setting Document Number (Sales Order Number)
2428 -- we fetch it from the base table, only in the case when order import succeeded for an Inbound
2429 -- or if it is an SSO/CSO
2430 -- POA is handled separately
2431
2432 If (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO OR
2433 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO OR
2434 ((l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI OR
2435 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO OR
2436 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO)
2437 AND l_processing_stage IN ('IMPORT_SUCCESS'))) Then
2438
2439
2440 Begin
2441 Select order_number, order_type_id, header_id
2442 Into l_document_num, l_order_type_id, l_header_id
2443 From oe_order_headers
2444 Where orig_sys_document_ref = l_orig_sys_document_ref
2445 And decode(l_customer_key_profile, 'Y',
2446 nvl(sold_to_org_id, -999), 1)
2447 = decode(l_customer_key_profile, 'Y',
2448 nvl(l_sold_to_org_id, -999), 1)
2449 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
2450
2451
2452 -- start bug 4195533
2453 OE_MSG_PUB.update_msg_context(
2454 p_header_id => l_header_id
2455 );
2456 -- end bug 4195533
2457
2458 Exception
2459 When Others Then
2460 p_x_result := 'COMPLETE:ERROR';
2461 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2462 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2463 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2464 END IF;
2465 -- start data fix project
2466 -- OE_STANDARD_WF.Save_Messages;
2467 -- OE_STANDARD_WF.Clear_Msg_Context;
2468 -- end data fix project
2469 IF l_debug_level > 0 THEN
2470 oe_debug_pub.add( 'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER FOR ' || P_ITEMTYPE ) ;
2471 END IF;
2472 End;
2473
2474 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO THEN --do nothing, just trap
2475 null;
2476
2477 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA THEN
2478
2479 l_failure_ack_flag := wf_engine.GetItemAttrText( p_itemtype
2480 , p_itemkey
2481 , 'ORDER_IMPORTED'
2482 );
2483
2484 -- Bug 4179657
2485 -- Failure acknowledgments were also resolving to an Order Number
2486 -- if there was an existing order in the system
2487 -- Therefore, use a parameter to indicate whether or not order
2488 -- import succeeded
2489
2490 If l_failure_ack_flag IN (G_WFR_COMPLETE,'Y') Then
2491 l_failure_ack_flag := 'N';
2492 Else
2493 l_failure_ack_flag := 'Y';
2494 End If;
2495 -- end bug 4179657
2496
2497 Begin
2498 -- try to get document num for "success" POA
2499
2500 Select order_number, order_type_id, header_id
2501 Into l_document_num, l_order_type_id, l_header_id
2502 From oe_order_headers
2503 Where orig_sys_document_ref = l_orig_sys_document_ref
2504 And decode(l_customer_key_profile, 'Y',
2505 nvl(sold_to_org_id, -999), 1)
2506 = decode(l_customer_key_profile, 'Y',
2507 nvl(l_sold_to_org_id, -999), 1)
2508 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
2509
2510 -- start bug 4195533
2511 IF l_failure_ack_flag = 'N' THEN
2512 OE_MSG_PUB.update_msg_context(
2513 p_header_id => l_header_id
2514 );
2515 END IF;
2516 -- end bug 4195533
2517
2518 Exception
2519 When NO_DATA_FOUND then
2520 -- otherwise set null document num for "failure" POA
2521 l_document_num := NULL;
2522
2523 When Others Then
2524 p_x_result := 'COMPLETE:ERROR';
2525 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2526 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2527 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2528 END IF;
2529 OE_STANDARD_WF.Save_Messages;
2530 OE_STANDARD_WF.Clear_Msg_Context;
2531 IF l_debug_level > 0 THEN
2532 oe_debug_pub.add( 'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER' ) ;
2533 END IF;
2534 Return;
2535 End;
2536
2537 End If;
2538
2539 --Done Setting Document Number
2540
2541 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2542 --Setting Document Id for outbound transactions
2543 If l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO
2544 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO Then
2545 l_document_id := p_itemkey;
2546 l_document_direction := 'OUT';
2547 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO
2548 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA Then
2549 l_document_id := wf_engine.GetItemAttrNumber (p_itemtype,
2550 p_itemkey,
2551 'PARAMETER5');
2552 l_document_direction := 'OUT';
2553 End If;
2554 If l_document_direction = 'OUT' Then
2555 l_party_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2556 p_itemkey,
2557 'ECX_PARTY_ID'));
2558 l_party_site_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2559 p_itemkey,
2560 'ECX_PARTY_SITE_ID'));
2561 End If;
2562 END IF;
2563
2564 --Setting Message Text based on what stage of the inbound/outbound processing we are
2565
2566 l_import_mode := wf_engine.GetActivityAttrText (p_itemtype,
2567 p_itemkey,
2568 p_actid,
2569 'IMPORT_MODE',
2570 TRUE);
2571 -- by setting the last argument to TRUE, we ensure that no error is thrown if the attr is not found
2572
2573 IF l_processing_stage = 'INBOUND_IFACE' THEN
2574 fnd_message.set_name('ONT', 'OE_OI_IFACE');
2575
2576 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2577 l_message_text := fnd_message.get;
2578 l_doc_status := 'ACTIVE';
2579 ELSIF l_processing_stage = 'PRE_IMPORT' THEN
2580
2581 If l_import_mode = 'ASYNCHRONOUS' Then
2582 fnd_message.set_name('ONT', 'OE_OI_IMPORT_MODE_ASYNC');
2583 Elsif l_import_mode = 'SYNCHRONOUS' Then
2584 fnd_message.set_name('ONT', 'OE_OI_IMPORT_MODE_SYNC');
2585 End If;
2586
2587 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2588
2589 l_message_text := fnd_message.get;
2590 l_doc_status := 'ACTIVE';
2591
2592 ELSIF l_processing_stage = 'IMPORT_SUCCESS' THEN
2593 If (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI) Then
2594 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL');
2595
2596 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO) Then
2597 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL_CPO');
2598
2599 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO) Then
2600 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL_CHO');
2601 End if;
2602 l_doc_status := 'SUCCESS';
2603 ELSIF l_processing_stage = 'IMPORT_FAILURE' THEN
2604 fnd_message.set_name('ONT', 'OE_OI_IMPORT_FAILURE');
2605 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2606
2607 l_message_text := fnd_message.get;
2608 l_doc_status := 'ERROR';
2609 ELSIF l_processing_stage = 'OUTBOUND_SENT' THEN
2610
2611 l_setup_status := wf_engine.GetActivityAttrText (p_itemtype,
2612 p_itemkey,
2613 p_actid,
2614 'PROCESSING_STATUS',
2615 TRUE);
2616 IF l_setup_status = 'ROSETTANET_SENT' Then
2617 Begin
2618 SELECT standard_desc
2619 INTO l_standard_desc
2620 FROM ecx_standards_vl
2621 WHERE standard_code = 'ROSETTANET';
2622 Exception
2623 When Others Then
2624 null;
2625 End;
2626 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2627 fnd_message.set_token ('TRANSACTION', l_standard_desc);
2628 l_message_text := fnd_message.get;
2629 Else
2630 IF (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA) Then
2631 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2632 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2633
2634 l_message_text := fnd_message.get;
2635 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO) Then --SSO
2636
2637 l_message_text := fnd_message.get_string('ONT', 'OE_SO_SHOW_SO_SENT');
2638 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO) Then --CSO
2639 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2640 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2641
2642 l_message_text := fnd_message.get;
2643 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO) Then --CBODO
2644 fnd_message.set_name ('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2645 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2646
2647 l_message_text := fnd_message.get;
2648 End If;
2649 End If;
2650 l_doc_status := 'SUCCESS';
2651 ELSIF l_processing_stage = 'OUTBOUND_SETUP' THEN
2652
2653 l_setup_status := wf_engine.GetActivityAttrText (p_itemtype,
2654 p_itemkey,
2655 p_actid,
2656 'PROCESSING_STATUS',
2657 TRUE);
2658 IF l_setup_status = 'SUCCESS' THEN
2659 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_SETUP');
2660 l_doc_status := 'ACTIVE';
2661 ELSIF l_setup_status = 'ERROR' THEN
2662 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_SETUP_ERR');
2663 l_doc_status := 'ERROR';
2664 END IF;
2665 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2666
2667 l_message_text := fnd_message.get;
2668
2669 ELSIF l_processing_stage = 'OUTBOUND_TRIGGERED' THEN
2670 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_TRIGGERED');
2671 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2672
2673 l_message_text := fnd_message.get;
2674
2675 l_doc_status := 'ACTIVE';
2676 END IF;
2677
2678 --Done Setting Message Text
2679
2680
2681 IF l_debug_level > 0 THEN
2682 oe_debug_pub.add('L_ORIG_SYS_DOCUMENT_REF:' || l_orig_sys_document_ref) ;
2683 oe_debug_pub.add('L_DOCUMENT_NUM:' || l_document_num) ;
2684 oe_debug_pub.add('L_MESSAGE_TEXT:' || l_message_text) ;
2685 oe_debug_pub.add('L_TRANSACTION_TYPE:' || l_transaction_type);
2686 oe_debug_pub.add('L_ORG_ID:' || l_org_id);
2687 oe_debug_pub.add('L_CHANGE_SEQUENCE:' || l_change_sequence);
2688 END IF;
2689
2690
2691 -- Orig Sys Document Ref is passed in for Partner Document Number
2692
2693 Oe_Acknowledgment_Pub.Raise_Event_Xmlint
2694 (p_order_source_id => OE_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID,
2695 p_partner_document_num => l_orig_sys_document_ref,
2696 p_message_text => l_message_text,
2697 p_document_num => l_document_num,
2698 p_order_type_id => l_order_type_id,
2699 p_itemtype => p_itemtype,
2700 p_itemkey => p_itemkey,
2701 p_transaction_type => OE_Acknowledgment_Pub.G_TRANSACTION_TYPE, --'ONT'
2702 p_transaction_subtype => l_transaction_type,
2703 p_doc_status => l_doc_status,
2704 p_org_id => l_org_id,
2705 p_sold_to_org_id => l_sold_to_org_id,
2706 p_change_sequence => l_change_sequence,
2707 p_document_direction => l_document_direction,
2708 p_xmlg_document_id => l_document_id,
2709 p_xmlg_partner_type => 'C',
2710 p_xmlg_party_id => l_party_id,
2711 p_xmlg_party_site_id => l_party_site_id,
2712 p_xmlg_icn => l_internal_control_number,
2713 p_xmlg_msgid => l_xml_message_id,
2714 p_document_disposition => l_document_disposition,
2715 p_processing_stage => l_processing_stage,
2716 p_conc_request_id => l_conc_request_id,
2717 p_response_flag => l_response_flag,
2718 p_header_id => l_header_id,
2719 p_failure_ack_flag => l_failure_ack_flag,
2720 x_return_status => l_return_status);
2721
2722 IF l_debug_level > 0 THEN
2723 oe_debug_pub.add( 'EXITING RAISE_EVENT_XMLINT_WF' ) ;
2724 END IF;
2725 p_x_result := 'COMPLETE:COMPLETE';
2726
2727
2728 -- start data fix project
2729 OE_STANDARD_WF.Save_Messages;
2730 OE_STANDARD_WF.Clear_Msg_Context;
2731 -- end data fix project
2732 END IF;
2733 --
2734 -- CANCEL mode - activity 'compensation'
2735 --
2736 -- This is an event point is called with the effect of the activity must
2737 -- be undone, for example when a process is reset to an earlier point
2738 -- due to a loop back.
2739 --
2740 if (p_funcmode = 'CANCEL') then
2741
2742 -- your cancel code goes here
2743 null;
2744
2745 -- no result needed
2746 p_x_result := 'COMPLETE';
2747 return;
2748 end if;
2749 Exception
2750 When Others Then
2751 p_x_result := 'COMPLETE:ERROR';
2752 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2753 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2754 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2755 END IF;
2756 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'RAISE_EVENT_XMLINT_WF',
2757 p_itemtype, p_itemkey, p_actid, p_funcmode);
2758 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2759 p_itemtype => p_itemtype,
2760 p_itemkey => p_itemkey);
2761 OE_STANDARD_WF.Save_Messages;
2762 OE_STANDARD_WF.Clear_Msg_Context;
2763 IF l_debug_level > 0 THEN
2764 oe_debug_pub.add( 'WHEN OTHERS EXCEPTION IN RAISE_EVENT_XMLINT_WF'||SQLERRM ) ;
2765 END IF;
2766 RAISE;
2767 End Raise_Event_Xmlint_Wf;
2768
2769
2770 Procedure Is_OAG_or_RosettaNet
2771 ( p_itemtype IN Varchar2,
2772 p_itemkey IN Varchar2,
2773 p_actid in number,
2774 p_funcmode IN Varchar2,
2775 p_x_result IN OUT NOCOPY Varchar2
2776 )
2777 Is
2778 l_standard_code Varchar2(30);
2779 l_party_id Number;
2780 l_party_site_id Number;
2781 l_error_code Number;
2782 l_error_msg Varchar2(1000);
2783 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2784 l_debug_level_cln CONSTANT NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
2785
2786 Begin
2787
2788 IF (l_debug_level > 0) THEN
2789 oe_debug_pub.Add('ENTERING OE_ORDER_IMPORT_WF.IS_OAG_OR_ROSETTANET');
2790 oe_debug_pub.Add('With the following parameters:');
2791 oe_debug_pub.Add('itemtype:' || p_itemtype);
2792 oe_debug_pub.Add('itemkey:' || p_itemkey);
2793 oe_debug_pub.Add('actid:' || p_actid);
2794 oe_debug_pub.Add('funcmode:' || p_funcmode);
2795 END IF;
2796 -- Start of CLN debugs
2797 IF (l_debug_level_cln <= 1) THEN
2798 cln_debug_pub.Add('ENTERING OE_ORDER_IMPORT_WF.IS_OAG_OR_ROSETTANET',1);
2799 cln_debug_pub.Add('With the following parameters:', 1);
2800 cln_debug_pub.Add('itemtype:' || p_itemtype, 1);
2801 cln_debug_pub.Add('itemkey:' || p_itemkey, 1);
2802 cln_debug_pub.Add('actid:' || p_actid, 1);
2803 cln_debug_pub.Add('funcmode:' || p_funcmode, 1);
2804 END IF;
2805 -- End of CLN debugs
2806
2807
2808
2809 l_party_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2810 p_itemkey,
2811 'ECX_PARTY_ID'));
2812 l_party_site_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2813 p_itemkey,
2814 'ECX_PARTY_SITE_ID'));
2815
2816 SELECT standard_code
2817 INTO l_standard_code
2818 FROM ecx_tp_details_v
2819 WHERE tp_header_id = (SELECT tp_header_id FROM ecx_tp_headers
2820 WHERE party_id = l_party_id
2821 AND party_site_id = l_party_site_id
2822 AND party_type = 'C')
2823 AND transaction_type ='ONT'
2824 AND transaction_subtype = 'POA';
2825
2826 if (l_standard_code = 'ROSETTANET')
2827 then
2828 -- Reached Here. Successful execution.
2829 p_x_result:= 'ROSETTANET';
2830 else -- default will be OAG
2831 p_x_result := 'OAG';
2832 end if;
2833
2834 IF (l_debug_level > 0) THEN
2835 oe_debug_pub.Add('EXITING IS_OAG_OR_ROSETTANET Successfully');
2836 END IF;
2837 -- Start of CLN debugs
2838 IF (l_debug_level_cln <= 2) THEN
2839 cln_debug_pub.Add('EXITING IS_OAG_OR_ROSETTANET Successfully', 2);
2840 END IF;
2841 -- End of CLN debugs
2842
2843 Exception
2844 When Others Then
2845 l_error_code := SQLCODE;
2846 l_error_msg := SQLERRM;
2847 p_x_result := 'COMPLETE:ERROR';
2848 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2849 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Is_OAG_or_RosettaNet');
2850 END IF;
2851 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_OAG_OR_ROSETTANET',
2852 p_itemtype, p_itemkey, p_actid, p_funcmode);
2853 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2854 p_itemtype => p_itemtype,
2855 p_itemkey => p_itemkey);
2856 OE_STANDARD_WF.Save_Messages;
2857 OE_STANDARD_WF.Clear_Msg_Context;
2858 IF l_debug_level > 0 THEN
2859 oe_debug_pub.add( 'ERROR CODE : ' || l_error_code || ', ERROR MESSAGE : '||SUBSTR ( l_error_msg , 1 , 200 ) , 1 ) ;
2860 END IF;
2861 -- Start of CLN debugs
2862 IF (l_debug_level_cln <= 1) THEN
2863 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
2864 cln_debug_pub.Add('Result out '|| p_x_result, 1);
2865 cln_debug_pub.Add('Exiting IS_OAG_OR_ROSETTANET with Error', 1);
2866 END IF;
2867 -- End of CLN debugs
2868
2869 RAISE;
2870 End Is_OAG_or_RosettaNet;
2871
2872 END OE_ORDER_IMPORT_WF;