[Home] [Help]
PACKAGE BODY: APPS.OE_ORDER_IMPORT_WF
Source
1 PACKAGE BODY OE_ORDER_IMPORT_WF AS
2 /* $Header: OEXWFOIB.pls 120.7 2005/12/09 05:02:20 kmuruges 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 => p_itemtype,
1766 x_return_status => l_return_status);
1767 End If;
1768
1769 if l_return_status = fnd_api.g_ret_sts_success then
1770 p_x_result := 'COMPLETE:COMPLETE';
1771 else
1772 p_x_result := 'COMPLETE:ERROR';
1773 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1774 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Showso_Wf');
1775 END IF;
1776 IF l_debug_level > 0 THEN
1777 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1778 END IF;
1779 end if;
1780 OE_STANDARD_WF.Save_Messages;
1781 OE_STANDARD_WF.Clear_Msg_Context;
1782
1783 END IF;
1784 --
1785 -- CANCEL mode - activity 'compensation'
1786 --
1787 -- This is an event point is called with the effect of the activity must
1788 -- be undone, for example when a process is reset to an earlier point
1789 -- due to a loop back.
1790 --
1791 if (p_funcmode = 'CANCEL') then
1792
1793 -- your cancel code goes here
1794 null;
1795
1796 -- no result needed
1797 p_x_result := 'COMPLETE';
1798 return;
1799 end if;
1800 Exception
1801 When Others Then
1802 p_x_result := 'COMPLETE:ERROR';
1803 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1804 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Showso_Wf');
1805 END IF;
1806 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'RAISE_EVENT_SHOWSO_WF',
1807 p_itemtype, p_itemkey, p_actid, p_funcmode);
1808 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1809 p_itemtype => p_itemtype,
1810 p_itemkey => p_itemkey);
1811 OE_STANDARD_WF.Save_Messages;
1812 OE_STANDARD_WF.Clear_Msg_Context;
1813 IF l_debug_level > 0 THEN
1814 oe_debug_pub.add( 'ERROR MESSAGE : '||SUBSTR ( SQLERRM , 1 , 200 ) , 1 ) ;
1815 END IF;
1816 RAISE;
1817 End Raise_Event_Showso_Wf;
1818
1819 Procedure Set_User_Key
1820 ( p_itemtype in varchar2,
1821 p_itemkey in varchar2,
1822 p_actid in number,
1823 p_funcmode in varchar2,
1824 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
1825 )
1826 Is
1827
1828 l_user_key Varchar2(240);
1829 l_transaction_type Varchar2(10);
1830 l_orig_sys_document_ref Varchar2(50) := NULL;
1831 l_customer_number Number;
1832 l_header_id Number;
1833 l_change_sequence Varchar2(50);
1834 --
1835 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1836 --
1837 Begin
1838
1839 IF p_funcmode = 'RUN' THEN
1840 OE_STANDARD_WF.Set_Msg_Context(p_actid);
1841 IF l_debug_level > 0 THEN
1842 oe_debug_pub.add( 'ENTERING SET_USER_KEY' ) ;
1843 END IF;
1844
1845 if (p_itemtype = OE_ORDER_IMPORT_WF.G_WFI_SHOW_SO) then
1846 l_header_id := wf_engine.GetItemAttrNumber(p_itemtype,
1847 p_itemkey,
1848 'HEADER_ID'
1849 );
1850 l_transaction_type := wf_engine.GetItemAttrText(p_itemtype,
1851 p_itemkey,
1852 'PARAMETER3'
1853 );
1854 begin
1855 select orig_sys_document_ref, sold_to_org_id, change_sequence
1856 into l_orig_sys_document_ref, l_customer_number, l_change_sequence
1857 from oe_order_headers
1858 where header_id = l_header_id;
1859 exception
1860 when others then
1861 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1862 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_user_key');
1863 END IF;
1864 -- in this case, we do the best we can, and just use the customer and transaction type for the user key
1865 end;
1866
1867 -- start exception management
1868 OE_MSG_PUB.set_msg_context(
1869 p_entity_code => 'ELECMSG_'||p_itemtype
1870 ,p_entity_id => to_number(p_itemkey)
1871 ,p_header_id => l_header_id
1872 ,p_line_id => null
1873 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1874 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1875 ,p_orig_sys_document_line_ref => null
1876 ,p_orig_sys_shipment_ref => null
1877 ,p_change_sequence => l_change_sequence
1878 ,p_source_document_type_id => null
1879 ,p_source_document_id => null
1880 ,p_source_document_line_id => null );
1881 -- end exception management
1882
1883 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1884 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;
1885 Else
1886 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_transaction_type; --OE_ACKNOWLEDGMENT_PUB.G_TRANSACTION_SSO;
1887 End If;
1888 IF l_debug_level > 0 THEN
1889 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1890 END IF;
1891 elsif (p_itemtype = OE_ORDER_IMPORT_WF.G_WFI_ORDER_IMPORT) then
1892 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype,
1893 p_itemkey,
1894 'PARAMETER2'
1895 );
1896 l_customer_number := wf_engine.GetItemAttrNumber(p_itemtype,
1897 p_itemkey,
1898 'PARAMETER4'
1899 );
1900 l_transaction_type := wf_engine.GetItemAttrText(p_itemtype,
1901 p_itemkey,
1902 'PARAMETER3'
1903 );
1904
1905 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
1906 l_change_sequence := wf_engine.GetItemAttrText(p_itemtype,
1907 p_itemkey,
1908 'PARAMETER7'
1909 );
1910 -- start exception management
1911 OE_MSG_PUB.set_msg_context(
1912 p_entity_code => 'ELECMSG_'||p_itemtype
1913 ,p_entity_id => p_itemkey
1914 ,p_header_id => null
1915 ,p_line_id => null
1916 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
1917 ,p_orig_sys_document_ref => l_orig_sys_document_ref
1918 ,p_orig_sys_document_line_ref => null
1919 ,p_orig_sys_shipment_ref => null
1920 ,p_change_sequence => l_change_sequence
1921 ,p_source_document_type_id => null
1922 ,p_source_document_id => null
1923 ,p_source_document_line_id => null );
1924 -- end exception management
1925
1926 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_change_sequence || ',' || l_transaction_type;
1927 Else
1928 l_user_key := l_orig_sys_document_ref || ',' || to_char(l_customer_number) || ',' || l_transaction_type;
1929 End If;
1930
1931
1932 wf_engine.SetItemAttrText (itemtype => p_itemtype,
1933 itemkey => p_itemkey,
1934 aname => 'USER_KEY',
1935 avalue => l_user_key);
1936
1937 IF l_debug_level > 0 THEN
1938 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1939 END IF;
1940 else -- now we will just assume that it is always passed in at this point
1941 -- TODO: we may need another else if to handle the ack being called from the conc_pgm or other workflow.
1942 l_user_key := wf_engine.GetItemAttrText(p_itemtype,
1943 p_itemkey,
1944 'USER_KEY'
1945 );
1946 IF l_debug_level > 0 THEN
1947 oe_debug_pub.add( 'USER_KEY = '||L_USER_KEY ) ;
1948 END IF;
1949
1950 end if;
1951 wf_engine.SetItemUserKey(itemtype => p_itemtype,
1952 itemkey => p_itemkey,
1953 userkey => l_user_key);
1954
1955
1956 p_x_result := 'COMPLETE:COMPLETE';
1957 IF l_debug_level > 0 THEN
1958 oe_debug_pub.add( 'EXITING SET_USER_KEY' ) ;
1959 END IF;
1960
1961 OE_STANDARD_WF.Save_Messages;
1962 OE_STANDARD_WF.Clear_Msg_Context;
1963
1964 END IF;
1965 --
1966 -- CANCEL mode - activity 'compensation'
1967 --
1968 -- This is an event point is called with the effect of the activity must
1969 -- be undone, for example when a process is reset to an earlier point
1970 -- due to a loop back.
1971 --
1972 if (p_funcmode = 'CANCEL') then
1973
1974 -- your cancel code goes here
1975 null;
1976
1977 -- no result needed
1978 p_x_result := 'COMPLETE';
1979 return;
1980 end if;
1981 Exception
1982 When Others Then
1983 p_x_result := 'COMPLETE:ERROR';
1984 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1985 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_user_key');
1986 END IF;
1987 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'SET_USER_KEY',
1988 p_itemtype, p_itemkey, p_actid, p_funcmode);
1989 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
1990 p_itemtype => p_itemtype,
1991 p_itemkey => p_itemkey);
1992 OE_STANDARD_WF.Save_Messages;
1993 OE_STANDARD_WF.Clear_Msg_Context;
1994 RAISE;
1995 End Set_User_Key;
1996
1997 Procedure Is_CBOD_Out_Reqd
1998 ( p_itemtype in varchar2,
1999 p_itemkey in varchar2,
2000 p_actid in number,
2001 p_funcmode in varchar2,
2002 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2003 ) is
2004
2005 l_confirmation VARCHAR2(2000);
2006 l_message_id NUMBER;
2007 --
2008 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2009 --
2010 Begin
2011
2012 IF p_funcmode = 'RUN' THEN
2013 IF l_debug_level > 0 THEN
2014 oe_debug_pub.add( 'ENTERING IS_CBOD_OUT_REQD' ) ;
2015 END IF;
2016 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2017 l_message_id := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2018 itemkey => p_itemkey,
2019 aname => 'PARAMETER5');
2020 IF l_debug_level > 0 THEN
2021 oe_debug_pub.add( 'L_MESSAGE_ID : '|| L_MESSAGE_ID ) ;
2022 END IF;
2023 Begin
2024 select confirmation
2025 into l_confirmation
2026 from ecx_oag_cbod_v
2027 where document_id = l_message_id;
2028 Exception
2029 When NO_DATA_FOUND then
2030 IF l_debug_level > 0 THEN
2031 oe_debug_pub.add( 'NO DATA FOUND IN IS_CBOD_OUT_REQD , RETURNING FALSE I.E. DO NOT SEND CBOD' ) ;
2032 END IF;
2033 p_x_result := 'F';
2034 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2035 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'is_cbod_out_reqd');
2036 END IF;
2037 When OTHERS then
2038 IF l_debug_level > 0 THEN
2039 oe_debug_pub.add( 'OTHERS IN IS_CBOD_OUT_REQD , RETURNING FALSE I.E. DO NOT SEND CBOD' ) ;
2040 END IF;
2041 p_x_result := 'F';
2042 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2043 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'is_cbod_out_reqd');
2044 END IF;
2045 End;
2046 IF l_debug_level > 0 THEN
2047 oe_debug_pub.add( 'L_CONFIRMATION : '|| L_CONFIRMATION ) ;
2048 END IF;
2049 -- since we are only dealing with successes in our implementation,
2050 -- we only issue the cbod when l_confirmation is set to 2
2051 If l_confirmation = '2' then
2052 p_x_result := 'T';
2053 Else p_x_result := 'F';
2054 End If;
2055
2056 --p_x_result := 'F';
2057 IF l_debug_level > 0 THEN
2058 oe_debug_pub.add( 'RETURN VALUE FOR IS_CBOD_OUT_REQD: ' || P_X_RESULT ) ;
2059 END IF;
2060 OE_STANDARD_WF.Save_Messages;
2061 OE_STANDARD_WF.Clear_Msg_Context;
2062
2063 END IF;
2064 --
2065 -- CANCEL mode - activity 'compensation'
2066 --
2067 -- This is an event point is called with the effect of the activity must
2068 -- be undone, for example when a process is reset to an earlier point
2069 -- due to a loop back.
2070 --
2071 if (p_funcmode = 'CANCEL') then
2072
2073 -- your cancel code goes here
2074 null;
2075
2076 -- no result needed
2077 p_x_result := 'COMPLETE';
2078 return;
2079 end if;
2080 Exception
2081 When Others Then
2082 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_CBOD_OUT_REQD',
2083 p_itemtype, p_itemkey, p_actid, p_funcmode);
2084 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2085 p_itemtype => p_itemtype,
2086 p_itemkey => p_itemkey);
2087 OE_STANDARD_WF.Save_Messages;
2088 OE_STANDARD_WF.Clear_Msg_Context;
2089 RAISE;
2090 End Is_CBOD_Out_Reqd;
2091
2092 /* This function is no longer used but we might use it in the future */
2093 Procedure Populate_CBOD_Out_Globals
2094 ( p_itemtype in varchar2,
2095 p_itemkey in varchar2,
2096 p_actid in number,
2097 p_funcmode in varchar2,
2098 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2099 ) Is
2100
2101 --
2102 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2103 --
2104 Begin
2105 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2106
2107 p_x_result := 'COMPLETE:COMPLETE'; --need this to avoid runtime error
2108 OE_STANDARD_WF.Save_Messages;
2109 OE_STANDARD_WF.Clear_Msg_Context;
2110 End Populate_CBOD_Out_Globals;
2111
2112 Procedure Set_CBOD_EVENT_KEY
2113 ( p_itemtype in varchar2,
2114 p_itemkey in varchar2,
2115 p_actid in number,
2116 p_funcmode in varchar2,
2117 p_x_result in out NOCOPY /* file.sql.39 change */ varchar2
2118 ) Is
2119 l_event_key_num number;
2120 --
2121 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2122 --
2123 Begin
2124
2125 IF p_funcmode = 'RUN' THEN
2126 /* l_event_key_num := wf_engine.GetItemAttrNumber (itemtype => p_itemtype,
2127 itemkey => p_itemkey,
2128 aname => 'PARAMETER5');
2129 */
2130 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2131 select oe_xml_message_seq_s.nextval
2132 into l_event_key_num
2133 from dual;
2134
2135 wf_engine.SetItemAttrText (itemtype => p_itemtype,
2136 itemkey => p_itemkey,
2137 aname => 'CBOD_EVENT_KEY',
2138 avalue => to_char(l_event_key_num));
2139 p_x_result := to_char(l_event_key_num);
2140 OE_STANDARD_WF.Save_Messages;
2141 OE_STANDARD_WF.Clear_Msg_Context;
2142
2143 END IF;
2144 --
2145 -- CANCEL mode - activity 'compensation'
2146 --
2147 -- This is an event point is called with the effect of the activity must
2148 -- be undone, for example when a process is reset to an earlier point
2149 -- due to a loop back.
2150 --
2151 if (p_funcmode = 'CANCEL') then
2152
2153 -- your cancel code goes here
2154 null;
2155
2156 -- no result needed
2157 p_x_result := 'COMPLETE';
2158 return;
2159 end if;
2160 Exception
2161 WHEN OTHERS then
2162 p_x_result := NULL;
2163 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2164 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'set_cbod_event_key');
2165 END IF;
2166 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'SET_CBOD_EVENT_KEY',
2167 p_itemtype, p_itemkey, p_actid, p_funcmode);
2168 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2169 p_itemtype => p_itemtype,
2170 p_itemkey => p_itemkey);
2171 OE_STANDARD_WF.Save_Messages;
2172 OE_STANDARD_WF.Clear_Msg_Context;
2173 RAISE;
2174 End Set_cbod_event_key;
2175
2176
2177
2178
2179 Procedure Raise_Event_Xmlint_Wf
2180 ( p_itemtype IN Varchar2,
2181 p_itemkey IN Varchar2,
2182 p_actid in number,
2183 p_funcmode IN Varchar2,
2184 p_x_result IN OUT NOCOPY /* file.sql.39 change */ Varchar2
2185 )
2186 Is
2187 l_transaction_type Varchar2(30);
2188 l_transaction_type_test1 Varchar2(30);
2189 l_transaction_type_test2 Varchar2(30);
2190 l_header_id Number;
2191 l_document_num Number;
2192 l_order_type_id Number;
2193 l_orig_sys_document_ref Varchar2(50);
2194 l_message_text Varchar2(500);
2195 l_return_status varchar2(1);
2196 l_processing_stage varchar2(30);
2197 l_doc_status varchar2(240);
2198 l_import_mode varchar2(15);
2199 l_setup_status varchar2(15);
2200 l_org_id NUMBER;
2201 l_document_id NUMBER;
2202 l_document_direction VARCHAR2(6);
2203 l_party_id NUMBER;
2204 l_party_site_id NUMBER;
2205 l_change_sequence VARCHAR2(50);
2206 l_sold_to_org_id NUMBER;
2207 l_conc_request_id NUMBER;
2208 l_xml_message_id Varchar2(240);
2209 l_internal_control_number Number;
2210 l_txn_token Varchar2(50);
2211 l_document_disposition VARCHAR2(20);
2212 l_customer_key_profile VARCHAR2(1) := 'N';
2213 l_response_flag VARCHAR2(1);
2214 l_standard_desc VARCHAR2(80);
2215 l_failure_ack_flag VARCHAR2(10) := 'N';
2216 --
2217 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2218 --
2219 Begin
2220
2221 IF p_funcmode = 'RUN' THEN
2222 IF l_debug_level > 0 THEN
2223 oe_debug_pub.add( 'ENTERING RAISE_EVENT_XMLINT_WF' ) ;
2224 oe_debug_pub.add( 'P_ITEMKEY => ' || P_ITEMKEY ) ;
2225 oe_debug_pub.add( 'P_ITEMTYPE => ' || P_ITEMTYPE ) ;
2226 END IF;
2227
2228 OE_STANDARD_WF.Set_Msg_Context(p_actid);
2229
2230 -- bug 3688227
2231 -- start bug 3688227
2232 -- this is all the info we have at this point
2233 OE_MSG_PUB.set_msg_context(
2234 p_entity_code => 'ELECMSG_'||p_itemtype
2235 ,p_entity_id => p_itemkey
2236 ,p_order_source_id => OE_ACKNOWLEDGMENT_PUB.G_XML_ORDER_SOURCE_ID
2237 );
2238 -- end bug 3688227
2239
2240 If OE_CODE_CONTROL.GET_CODE_RELEASE_LEVEL >= '110510' Then
2241 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
2242 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
2243 IF l_debug_level > 0 THEN
2244 oe_debug_pub.add( 'CUSTOMER KEY PROFILE SETTING = '||l_customer_key_profile ) ;
2245 END IF;
2246 End If;
2247
2248
2249 l_transaction_type_test1 := wf_engine.GetItemAttrText( p_itemtype
2250 , p_itemkey
2251 , 'ECX_TRANSACTION_SUBTYPE'
2252 );
2253
2254 l_transaction_type_test2 := wf_engine.GetItemAttrText( p_itemtype
2255 , p_itemkey
2256 , 'PARAMETER3'
2257 );
2258
2259 If l_transaction_type_test1 = Oe_Acknowledgment_Pub.G_TRANSACTION_POA
2260 OR l_transaction_type_test1 = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO Then
2261 --POA
2262 l_transaction_type := l_transaction_type_test1;
2263 Else
2264 --POI, CPO, or SSO
2265 l_transaction_type := l_transaction_type_test2;
2266 End If;
2267
2268 IF l_debug_level > 0 THEN
2269 oe_debug_pub.add( 'L_TRANSACTION_TYPE => ' || L_TRANSACTION_TYPE ) ;
2270 oe_debug_pub.add( 'L_TRANSACTION_TYPE_TEST1 => ' || L_TRANSACTION_TYPE_TEST1 ) ;
2271 oe_debug_pub.add( 'L_TRANSACTION_TYPE_TEST2 => ' || L_TRANSACTION_TYPE_TEST2 ) ;
2272 END IF;
2273
2274 l_processing_stage := wf_engine.GetActivityAttrText (p_itemtype,
2275 p_itemkey,
2276 p_actid,
2277 'PROCESSING_STAGE',
2278 TRUE);
2279
2280 --setting Orig Sys Document Ref (Partner Document Number) for SSO
2281 If l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO
2282 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO
2283 Then
2284 l_header_id := wf_engine.GetItemAttrNumber( p_itemtype
2285 , p_itemkey
2286 , 'HEADER_ID'
2287 );
2288 -- start bug 3688227
2289 OE_MSG_PUB.update_msg_context(
2290 p_header_id => l_header_id
2291 );
2292 -- end bug 3688227
2293
2294 IF l_debug_level > 0 THEN
2295 oe_debug_pub.add( 'L_HEADER_ID => ' || L_HEADER_ID ) ;
2296 END IF;
2297
2298 Begin
2299 Select orig_sys_document_ref
2300 Into l_orig_sys_document_ref
2301 From oe_order_headers
2302 Where header_id = l_header_id;
2303 -- start bug 3688227
2304 OE_MSG_PUB.update_msg_context(
2305 p_orig_sys_document_ref => l_orig_sys_document_ref
2306 );
2307 -- end bug 3688227
2308 Exception
2309 When Others Then
2310 p_x_result := 'COMPLETE:ERROR';
2311 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2312 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2313 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2314 END IF;
2315 -- start data fix project
2316 -- OE_STANDARD_WF.Save_Messages;
2317 -- OE_STANDARD_WF.Clear_Msg_Context;
2318 -- end data fix project
2319 End;
2320
2321 l_xml_message_id := wf_engine.GetItemAttrText( p_itemtype
2322 , p_itemkey
2323 , 'ECX_MSGID_ATTR'
2324 , TRUE
2325 );
2326
2327 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2328 -- also get the request id for 3A6 Conc Pgm,
2329 -- don't check for Start From Flow Item Attr because it is
2330 -- unnecessary, request id will be null for non-conc pgm cases
2331 IF l_transaction_type = OE_Acknowledgment_Pub.G_TRANSACTION_SSO THEN
2332 l_conc_request_id := wf_engine.GetItemAttrNumber(p_itemtype
2333 , p_itemkey
2334 , 'REQ_ID'
2335 , TRUE);
2336 END IF;
2337 END IF;
2338
2339 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2340 l_processing_stage := 'OUTBOUND_SENT';
2341 END IF;
2342 Elsif ((l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI) OR
2343 (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO) OR
2344 (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO)) Then
2345 --setting Orig Sys Document Ref for POI or CPO
2346
2347 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype
2348 , p_itemkey
2349 , 'PARAMETER2'
2350 );
2351 -- start bug 3688227
2352 OE_MSG_PUB.update_msg_context(
2353 p_orig_sys_document_ref => l_orig_sys_document_ref
2354 );
2355 -- end bug 3688227
2356
2357 l_internal_control_number := wf_engine.GetItemAttrNumber( p_itemtype
2358 , p_itemkey
2359 , 'PARAMETER5'
2360 );
2361
2362 --also get the request id for OI
2363 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2364 l_conc_request_id := wf_engine.GetItemAttrNumber(p_itemtype
2365 , p_itemkey
2366 , 'REQ_ID'
2367 , TRUE);
2368 IF l_transaction_type = OE_Acknowledgment_Pub.G_TRANSACTION_CHO THEN
2369 l_response_flag := wf_engine.GetItemAttrText(p_itemtype
2370 , p_itemkey
2371 , 'PARAMETER10'
2372 , TRUE);
2373 END IF;
2374 END IF;
2375
2376 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2377 l_processing_stage := 'IMPORT_SUCCESS';
2378 END IF;
2379 Else
2380
2381 --setting Orig Sys Document Ref for POA and CBODO
2382 l_orig_sys_document_ref := wf_engine.GetItemAttrText(p_itemtype
2383 , p_itemkey
2384 , 'ORIG_SYS_DOCUMENT_REF'
2385 );
2386 -- start bug 3688227
2387 OE_MSG_PUB.update_msg_context(
2388 p_orig_sys_document_ref => l_orig_sys_document_ref
2389 );
2390 -- end bug 3688227
2391
2392 l_xml_message_id := wf_engine.GetItemAttrText( p_itemtype
2393 , p_itemkey
2394 , 'ECX_MSGID_ATTR'
2395 , TRUE
2396 );
2397 IF OE_Code_Control.Get_Code_Release_Level < '110510' THEN
2398 l_processing_stage := 'OUTBOUND_SENT';
2399 END IF;
2400 End If;
2401 --Done Setting Orig Sys Document Ref (Partner Document Number)
2402
2403
2404
2405 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2406 l_org_id := wf_engine.GetItemAttrNumber ( p_itemtype
2407 , p_itemkey
2408 , 'ORG_ID'
2409 , TRUE
2410 );
2411 l_sold_to_org_id := wf_engine.GetItemAttrNumber( p_itemtype
2412 , p_itemkey
2413 , 'PARAMETER4'
2414 , TRUE);
2415 l_change_sequence := wf_engine.GetItemAttrText( p_itemtype
2416 , p_itemkey
2417 , 'PARAMETER7'
2418 , TRUE);
2419 -- start bug 3688227
2420 OE_MSG_PUB.update_msg_context(
2421 p_change_sequence => l_change_sequence
2422 );
2423 -- end bug 3688227
2424 END IF;
2425
2426 --Setting Document Number (Sales Order Number)
2427 -- we fetch it from the base table, only in the case when order import succeeded for an Inbound
2428 -- or if it is an SSO/CSO
2429 -- POA is handled separately
2430
2431 If (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO OR
2432 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO OR
2433 ((l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI OR
2434 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO OR
2435 l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO)
2436 AND l_processing_stage IN ('IMPORT_SUCCESS'))) Then
2437
2438
2439 Begin
2440 Select order_number, order_type_id, header_id
2441 Into l_document_num, l_order_type_id, l_header_id
2442 From oe_order_headers
2443 Where orig_sys_document_ref = l_orig_sys_document_ref
2444 And decode(l_customer_key_profile, 'Y',
2445 nvl(sold_to_org_id, -999), 1)
2446 = decode(l_customer_key_profile, 'Y',
2447 nvl(l_sold_to_org_id, -999), 1)
2448 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
2449
2450
2451 -- start bug 4195533
2452 OE_MSG_PUB.update_msg_context(
2453 p_header_id => l_header_id
2454 );
2455 -- end bug 4195533
2456
2457 Exception
2458 When Others Then
2459 p_x_result := 'COMPLETE:ERROR';
2460 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2461 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2462 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2463 END IF;
2464 -- start data fix project
2465 -- OE_STANDARD_WF.Save_Messages;
2466 -- OE_STANDARD_WF.Clear_Msg_Context;
2467 -- end data fix project
2468 IF l_debug_level > 0 THEN
2469 oe_debug_pub.add( 'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER FOR ' || P_ITEMTYPE ) ;
2470 END IF;
2471 End;
2472
2473 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO THEN --do nothing, just trap
2474 null;
2475
2476 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA THEN
2477
2478 l_failure_ack_flag := wf_engine.GetItemAttrText( p_itemtype
2479 , p_itemkey
2480 , 'ORDER_IMPORTED'
2481 );
2482
2483 -- Bug 4179657
2484 -- Failure acknowledgments were also resolving to an Order Number
2485 -- if there was an existing order in the system
2486 -- Therefore, use a parameter to indicate whether or not order
2487 -- import succeeded
2488
2489 If l_failure_ack_flag IN (G_WFR_COMPLETE,'Y') Then
2490 l_failure_ack_flag := 'N';
2491 Else
2492 l_failure_ack_flag := 'Y';
2493 End If;
2494 -- end bug 4179657
2495
2496 Begin
2497 -- try to get document num for "success" POA
2498
2499 Select order_number, order_type_id, header_id
2500 Into l_document_num, l_order_type_id, l_header_id
2501 From oe_order_headers
2502 Where orig_sys_document_ref = l_orig_sys_document_ref
2503 And decode(l_customer_key_profile, 'Y',
2504 nvl(sold_to_org_id, -999), 1)
2505 = decode(l_customer_key_profile, 'Y',
2506 nvl(l_sold_to_org_id, -999), 1)
2507 And order_source_id = Oe_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID;
2508
2509 -- start bug 4195533
2510 IF l_failure_ack_flag = 'N' THEN
2511 OE_MSG_PUB.update_msg_context(
2512 p_header_id => l_header_id
2513 );
2514 END IF;
2515 -- end bug 4195533
2516
2517 Exception
2518 When NO_DATA_FOUND then
2519 -- otherwise set null document num for "failure" POA
2520 l_document_num := NULL;
2521
2522 When Others Then
2523 p_x_result := 'COMPLETE:ERROR';
2524 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2525 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2526 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2527 END IF;
2528 OE_STANDARD_WF.Save_Messages;
2529 OE_STANDARD_WF.Clear_Msg_Context;
2530 IF l_debug_level > 0 THEN
2531 oe_debug_pub.add( 'EXCEPTION IN RAISE_EVENT_XMLINT...SELECTING DOCUMENT NUMBER' ) ;
2532 END IF;
2533 Return;
2534 End;
2535
2536 End If;
2537
2538 --Done Setting Document Number
2539
2540 IF OE_Code_Control.Get_Code_Release_Level >= '110510' THEN
2541 --Setting Document Id for outbound transactions
2542 If l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO
2543 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO Then
2544 l_document_id := p_itemkey;
2545 l_document_direction := 'OUT';
2546 Elsif l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO
2547 Or l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA Then
2548 l_document_id := wf_engine.GetItemAttrNumber (p_itemtype,
2549 p_itemkey,
2550 'PARAMETER5');
2551 l_document_direction := 'OUT';
2552 End If;
2553 If l_document_direction = 'OUT' Then
2554 l_party_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2555 p_itemkey,
2556 'ECX_PARTY_ID'));
2557 l_party_site_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2558 p_itemkey,
2559 'ECX_PARTY_SITE_ID'));
2560 End If;
2561 END IF;
2562
2563 --Setting Message Text based on what stage of the inbound/outbound processing we are
2564
2565 l_import_mode := wf_engine.GetActivityAttrText (p_itemtype,
2566 p_itemkey,
2567 p_actid,
2568 'IMPORT_MODE',
2569 TRUE);
2570 -- by setting the last argument to TRUE, we ensure that no error is thrown if the attr is not found
2571
2572 IF l_processing_stage = 'INBOUND_IFACE' THEN
2573 fnd_message.set_name('ONT', 'OE_OI_IFACE');
2574
2575 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2576 l_message_text := fnd_message.get;
2577 l_doc_status := 'ACTIVE';
2578 ELSIF l_processing_stage = 'PRE_IMPORT' THEN
2579
2580 If l_import_mode = 'ASYNCHRONOUS' Then
2581 fnd_message.set_name('ONT', 'OE_OI_IMPORT_MODE_ASYNC');
2582 Elsif l_import_mode = 'SYNCHRONOUS' Then
2583 fnd_message.set_name('ONT', 'OE_OI_IMPORT_MODE_SYNC');
2584 End If;
2585
2586 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2587
2588 l_message_text := fnd_message.get;
2589 l_doc_status := 'ACTIVE';
2590
2591 ELSIF l_processing_stage = 'IMPORT_SUCCESS' THEN
2592 If (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POI) Then
2593 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL');
2594
2595 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CPO) Then
2596 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL_CPO');
2597
2598 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CHO) Then
2599 l_message_text := fnd_message.get_string('ONT', 'OE_OI_IMPORT_SUCCESSFUL_CHO');
2600 End if;
2601 l_doc_status := 'SUCCESS';
2602 ELSIF l_processing_stage = 'IMPORT_FAILURE' THEN
2603 fnd_message.set_name('ONT', 'OE_OI_IMPORT_FAILURE');
2604 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2605
2606 l_message_text := fnd_message.get;
2607 l_doc_status := 'ERROR';
2608 ELSIF l_processing_stage = 'OUTBOUND_SENT' THEN
2609
2610 l_setup_status := wf_engine.GetActivityAttrText (p_itemtype,
2611 p_itemkey,
2612 p_actid,
2613 'PROCESSING_STATUS',
2614 TRUE);
2615 IF l_setup_status = 'ROSETTANET_SENT' Then
2616 Begin
2617 SELECT standard_desc
2618 INTO l_standard_desc
2619 FROM ecx_standards_vl
2620 WHERE standard_code = 'ROSETTANET';
2621 Exception
2622 When Others Then
2623 null;
2624 End;
2625 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2626 fnd_message.set_token ('TRANSACTION', l_standard_desc);
2627 l_message_text := fnd_message.get;
2628 Else
2629 IF (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_POA) Then
2630 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2631 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2632
2633 l_message_text := fnd_message.get;
2634 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_SSO) Then --SSO
2635
2636 l_message_text := fnd_message.get_string('ONT', 'OE_SO_SHOW_SO_SENT');
2637 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CSO) Then --CSO
2638 fnd_message.set_name('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2639 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2640
2641 l_message_text := fnd_message.get;
2642 Elsif (l_transaction_type = Oe_Acknowledgment_Pub.G_TRANSACTION_CBODO) Then --CBODO
2643 fnd_message.set_name ('ONT', 'OE_OA_ACKNOWLEDGMENT_SENT');
2644 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2645
2646 l_message_text := fnd_message.get;
2647 End If;
2648 End If;
2649 l_doc_status := 'SUCCESS';
2650 ELSIF l_processing_stage = 'OUTBOUND_SETUP' THEN
2651
2652 l_setup_status := wf_engine.GetActivityAttrText (p_itemtype,
2653 p_itemkey,
2654 p_actid,
2655 'PROCESSING_STATUS',
2656 TRUE);
2657 IF l_setup_status = 'SUCCESS' THEN
2658 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_SETUP');
2659 l_doc_status := 'ACTIVE';
2660 ELSIF l_setup_status = 'ERROR' THEN
2661 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_SETUP_ERR');
2662 l_doc_status := 'ERROR';
2663 END IF;
2664 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type)|| ' -');
2665
2666 l_message_text := fnd_message.get;
2667
2668 ELSIF l_processing_stage = 'OUTBOUND_TRIGGERED' THEN
2669 fnd_message.set_name('ONT', 'OE_OI_OUTBOUND_TRIGGERED');
2670 fnd_message.set_token ('TRANSACTION', Oe_Acknowledgment_Pub.EM_Transaction_Type (p_txn_code => l_transaction_type));
2671
2672 l_message_text := fnd_message.get;
2673
2674 l_doc_status := 'ACTIVE';
2675 END IF;
2676
2677 --Done Setting Message Text
2678
2679
2680 IF l_debug_level > 0 THEN
2681 oe_debug_pub.add('L_ORIG_SYS_DOCUMENT_REF:' || l_orig_sys_document_ref) ;
2682 oe_debug_pub.add('L_DOCUMENT_NUM:' || l_document_num) ;
2683 oe_debug_pub.add('L_MESSAGE_TEXT:' || l_message_text) ;
2684 oe_debug_pub.add('L_TRANSACTION_TYPE:' || l_transaction_type);
2685 oe_debug_pub.add('L_ORG_ID:' || l_org_id);
2686 oe_debug_pub.add('L_CHANGE_SEQUENCE:' || l_change_sequence);
2687 END IF;
2688
2689
2690 -- Orig Sys Document Ref is passed in for Partner Document Number
2691
2692 Oe_Acknowledgment_Pub.Raise_Event_Xmlint
2693 (p_order_source_id => OE_Acknowledgment_Pub.G_XML_ORDER_SOURCE_ID,
2694 p_partner_document_num => l_orig_sys_document_ref,
2695 p_message_text => l_message_text,
2696 p_document_num => l_document_num,
2697 p_order_type_id => l_order_type_id,
2698 p_itemtype => p_itemtype,
2699 p_itemkey => p_itemkey,
2700 p_transaction_type => OE_Acknowledgment_Pub.G_TRANSACTION_TYPE, --'ONT'
2701 p_transaction_subtype => l_transaction_type,
2702 p_doc_status => l_doc_status,
2703 p_org_id => l_org_id,
2704 p_sold_to_org_id => l_sold_to_org_id,
2705 p_change_sequence => l_change_sequence,
2706 p_document_direction => l_document_direction,
2707 p_xmlg_document_id => l_document_id,
2708 p_xmlg_partner_type => 'C',
2709 p_xmlg_party_id => l_party_id,
2710 p_xmlg_party_site_id => l_party_site_id,
2711 p_xmlg_icn => l_internal_control_number,
2712 p_xmlg_msgid => l_xml_message_id,
2713 p_document_disposition => l_document_disposition,
2714 p_processing_stage => l_processing_stage,
2715 p_conc_request_id => l_conc_request_id,
2716 p_response_flag => l_response_flag,
2717 p_header_id => l_header_id,
2718 p_failure_ack_flag => l_failure_ack_flag,
2719 x_return_status => l_return_status);
2720
2721 IF l_debug_level > 0 THEN
2722 oe_debug_pub.add( 'EXITING RAISE_EVENT_XMLINT_WF' ) ;
2723 END IF;
2724 p_x_result := 'COMPLETE:COMPLETE';
2725
2726
2727 -- start data fix project
2728 OE_STANDARD_WF.Save_Messages;
2729 OE_STANDARD_WF.Clear_Msg_Context;
2730 -- end data fix project
2731 END IF;
2732 --
2733 -- CANCEL mode - activity 'compensation'
2734 --
2735 -- This is an event point is called with the effect of the activity must
2736 -- be undone, for example when a process is reset to an earlier point
2737 -- due to a loop back.
2738 --
2739 if (p_funcmode = 'CANCEL') then
2740
2741 -- your cancel code goes here
2742 null;
2743
2744 -- no result needed
2745 p_x_result := 'COMPLETE';
2746 return;
2747 end if;
2748 Exception
2749 When Others Then
2750 p_x_result := 'COMPLETE:ERROR';
2751 p_x_result := FND_API.G_RET_STS_UNEXP_ERROR ;
2752 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2753 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Raise_Event_Xmlint_Wf');
2754 END IF;
2755 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'RAISE_EVENT_XMLINT_WF',
2756 p_itemtype, p_itemkey, p_actid, p_funcmode);
2757 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2758 p_itemtype => p_itemtype,
2759 p_itemkey => p_itemkey);
2760 OE_STANDARD_WF.Save_Messages;
2761 OE_STANDARD_WF.Clear_Msg_Context;
2762 IF l_debug_level > 0 THEN
2763 oe_debug_pub.add( 'WHEN OTHERS EXCEPTION IN RAISE_EVENT_XMLINT_WF'||SQLERRM ) ;
2764 END IF;
2765 RAISE;
2766 End Raise_Event_Xmlint_Wf;
2767
2768
2769 Procedure Is_OAG_or_RosettaNet
2770 ( p_itemtype IN Varchar2,
2771 p_itemkey IN Varchar2,
2772 p_actid in number,
2773 p_funcmode IN Varchar2,
2774 p_x_result IN OUT NOCOPY Varchar2
2775 )
2776 Is
2777 l_standard_code Varchar2(30);
2778 l_party_id Number;
2779 l_party_site_id Number;
2780 l_error_code Number;
2781 l_error_msg Varchar2(1000);
2782 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2783 l_debug_level_cln CONSTANT NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
2784
2785 Begin
2786
2787 IF (l_debug_level > 0) THEN
2788 oe_debug_pub.Add('ENTERING OE_ORDER_IMPORT_WF.IS_OAG_OR_ROSETTANET');
2789 oe_debug_pub.Add('With the following parameters:');
2790 oe_debug_pub.Add('itemtype:' || p_itemtype);
2791 oe_debug_pub.Add('itemkey:' || p_itemkey);
2792 oe_debug_pub.Add('actid:' || p_actid);
2793 oe_debug_pub.Add('funcmode:' || p_funcmode);
2794 END IF;
2795 -- Start of CLN debugs
2796 IF (l_debug_level_cln <= 1) THEN
2797 cln_debug_pub.Add('ENTERING OE_ORDER_IMPORT_WF.IS_OAG_OR_ROSETTANET',1);
2798 cln_debug_pub.Add('With the following parameters:', 1);
2799 cln_debug_pub.Add('itemtype:' || p_itemtype, 1);
2800 cln_debug_pub.Add('itemkey:' || p_itemkey, 1);
2801 cln_debug_pub.Add('actid:' || p_actid, 1);
2802 cln_debug_pub.Add('funcmode:' || p_funcmode, 1);
2803 END IF;
2804 -- End of CLN debugs
2805
2806
2807
2808 l_party_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2809 p_itemkey,
2810 'ECX_PARTY_ID'));
2811 l_party_site_id := to_number(wf_engine.GetItemAttrText (p_itemtype,
2812 p_itemkey,
2813 'ECX_PARTY_SITE_ID'));
2814
2815 SELECT standard_code
2816 INTO l_standard_code
2817 FROM ecx_tp_details_v
2818 WHERE tp_header_id = (SELECT tp_header_id FROM ecx_tp_headers
2819 WHERE party_id = l_party_id
2820 AND party_site_id = l_party_site_id
2821 AND party_type = 'C')
2822 AND transaction_type ='ONT'
2823 AND transaction_subtype = 'POA';
2824
2825 if (l_standard_code = 'ROSETTANET')
2826 then
2827 -- Reached Here. Successful execution.
2828 p_x_result:= 'ROSETTANET';
2829 else -- default will be OAG
2830 p_x_result := 'OAG';
2831 end if;
2832
2833 IF (l_debug_level > 0) THEN
2834 oe_debug_pub.Add('EXITING IS_OAG_OR_ROSETTANET Successfully');
2835 END IF;
2836 -- Start of CLN debugs
2837 IF (l_debug_level_cln <= 2) THEN
2838 cln_debug_pub.Add('EXITING IS_OAG_OR_ROSETTANET Successfully', 2);
2839 END IF;
2840 -- End of CLN debugs
2841
2842 Exception
2843 When Others Then
2844 l_error_code := SQLCODE;
2845 l_error_msg := SQLERRM;
2846 p_x_result := 'COMPLETE:ERROR';
2847 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2848 OE_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, 'Is_OAG_or_RosettaNet');
2849 END IF;
2850 WF_CORE.Context('OE_ORDER_IMPORT_WF', 'IS_OAG_OR_ROSETTANET',
2851 p_itemtype, p_itemkey, p_actid, p_funcmode);
2852 OE_STANDARD_WF.Add_Error_Activity_Msg(p_actid => p_actid,
2853 p_itemtype => p_itemtype,
2854 p_itemkey => p_itemkey);
2855 OE_STANDARD_WF.Save_Messages;
2856 OE_STANDARD_WF.Clear_Msg_Context;
2857 IF l_debug_level > 0 THEN
2858 oe_debug_pub.add( 'ERROR CODE : ' || l_error_code || ', ERROR MESSAGE : '||SUBSTR ( l_error_msg , 1 , 200 ) , 1 ) ;
2859 END IF;
2860 -- Start of CLN debugs
2861 IF (l_debug_level_cln <= 1) THEN
2862 cln_debug_pub.Add('Exception ' || ':' || l_error_code || ':' || l_error_msg, 1);
2863 cln_debug_pub.Add('Result out '|| p_x_result, 1);
2864 cln_debug_pub.Add('Exiting IS_OAG_OR_ROSETTANET with Error', 1);
2865 END IF;
2866 -- End of CLN debugs
2867
2868 RAISE;
2869 End Is_OAG_or_RosettaNet;
2870
2871 END OE_ORDER_IMPORT_WF;