[Home] [Help]
PACKAGE BODY: APPS.OE_ERROR_WF
Source
1 PACKAGE BODY OE_ERROR_WF AS
2 /* $Header: OEXWERRB.pls 120.9.12000000.2 2007/10/14 15:01:45 vbkapoor ship $ */
3
4 TYPE T_NUM IS TABLE OF NUMBER;
5 TYPE T_V240 IS TABLE OF VARCHAR(240);
6 TYPE T_V8 IS TABLE OF VARCHAR(8);
7 TYPE T_V30 IS TABLE OF VARCHAR(30);
8 TYPE T_V2000 IS TABLE OF VARCHAR(2000);
9
10 TYPE Retry_Rec_Type IS RECORD (
11 item_key T_V240 := T_V240(),
12 activity_label T_V30 := T_V30(),
13 activity_name T_V30 := T_V30(),
14 activity_item_type T_V8 := T_V8(),
15 process_name T_V30 := T_V30(),
16 activity_id T_NUM := T_NUM(),
17 user_key T_V240 := T_V240(),
18 parent_item_type T_V8 := T_V8(),
19 parent_item_key T_V240 := T_V240(),
20 org_id T_NUM := T_NUM()
21 );
22
23 TYPE Msg_Rec_Type IS RECORD (
24 message_text T_V2000 := T_V2000()
25
26 );
27
28 TYPE Count_Rec_Type IS RECORD
29 ( concat_segment VARCHAR2(38) := NULL,
30 activity_display_name VARCHAR2(80) := NULL,
31 activity_name VARCHAR2(30) := NULL,
32 activity_item_type VARCHAR2(8) := NULL,
33 process_item_type VARCHAR2(8) := NULL,
34 initial_count NUMBER := NULL,
35 final_count NUMBER := NULL
36 );
37
38 TYPE Count_Tbl_Type IS TABLE OF Count_Rec_Type INDEX BY binary_integer;
39
40 TABLE_SIZE binary_integer := 2147483646; /*Size of the above Table*/
41
42 Count_Tbl Count_Tbl_Type;
43 Count_Rec Count_Rec_Type;
44
45 Procedure Get_EM_Key_Info (p_itemtype IN VARCHAR2,
46 p_itemkey IN VARCHAR2,
47 x_order_source_id OUT NOCOPY NUMBER,
48 x_orig_sys_document_ref OUT NOCOPY VARCHAR2,
49 x_sold_to_org_id OUT NOCOPY NUMBER,
50 x_change_sequence OUT NOCOPY VARCHAR2,
51 x_header_id OUT NOCOPY NUMBER,
52 x_org_id OUT NOCOPY NUMBER);
53
54
55
56 PROCEDURE Set_blanket_Descriptor ( itemtype IN VARCHAR2,
57 itemkey IN VARCHAR2,
58 err_itemtype IN varchar2,
59 err_itemkey IN varchar2
60 )
61 IS
62 l_header_id NUMBER;
63 l_order_number NUMBER;
64 l_order_type_id NUMBER;
65 l_order_type_name VARCHAR2(80);
66 l_order_category_code VARCHAR2(30);
67 l_order_type_txt VARCHAR2(2000);
68 l_header_txt VARCHAR2(2000);
69 l_descriptor VARCHAR2(2000);
70 --
71 --
72 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
73 --
74 l_salesrep VARCHAR2(240) := NULL;
75 l_salesrep_id NUMBER;
76 l_org_id NUMBER;
77 l_oper_unit_name VARCHAR2(240) := NULL;
78 l_version_number VARCHAR2(240);
79 l_flow_status_code VARCHAR2(30);
80 l_flow_status_code_meaning VARCHAR2(80);
81
82 l_oper_unit_name_text VARCHAR2(2000);
83 l_salesrep_text VARCHAR2(2000);
84
85 l_result_code VARCHAR2(30);
86 BEGIN
87 l_header_id := err_itemkey;
88 SELECT order_number, order_type_id, order_category_code,
89 org_id, VERSION_NUMBER, FLOW_STATUS_CODE
90 into l_order_number, l_order_type_id, l_order_category_code,
91 l_org_id, l_version_number, l_flow_status_code
92 from oe_blanket_headers_all
93 where header_id = err_itemkey;
94
95 SELECT T.NAME
96 INTO l_order_type_name
97 FROM OE_TRANSACTION_TYPES_TL T
98 WHERE T.LANGUAGE = userenv('LANG')
99 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
100
101 SELECT name
102 INTO l_oper_unit_name
103 FROM HR_OPerating_units
104 WHERE ORGANIZATION_ID = l_org_id;
105
106 IF l_flow_status_code is not NULL THEN
107 SELECT MEANING
108 INTO l_flow_status_code_meaning
109 FROM oe_lookups
110 where LOOKUP_CODE = l_flow_status_code
111 AND LOOKUP_TYPE = 'FLOW_STATUS';
112 END IF;
113
114 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
115 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
116 l_order_type_txt := fnd_message.get;
117 fnd_message.set_name('ONT', 'OE_BLKT_SALES_AGREEMENT');
118 fnd_message.set_token('BLANKET_NUMBER', to_char(l_order_number));
119 l_header_txt := fnd_message.get;
120
121 l_descriptor := substrb(l_order_type_txt || ', ' ||
122 l_header_txt, 1, 240);
123
124 wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_SHORT_DESCRIPTOR', l_descriptor);
125
126 wf_engine.SetItemAttrText(itemtype,itemkey, 'OPERATING_UNIT',
127 l_oper_unit_name);
128 fnd_message.set_name ('ONT', 'OE_WF_VERSION_NUMBER');
129 fnd_message.set_token('VERSION_NUMBER', l_version_number);
130 wf_engine.SetItemAttrText(itemtype,itemkey, 'VERSION_NUMBER',
131 FND_MESSAGE.GET);
132 fnd_message.set_name ('ONT', 'OE_WF_FLOW_STATUS');
133 fnd_message.set_token('FLOW_STATUS', l_flow_status_code_meaning);
134 wf_engine.SetItemAttrText(itemtype,itemkey, 'FLOW_STATUS',
135 FND_MESSAGE.GET);
136
137 wf_engine.SetItemAttrNumber(itemtype,itemkey, 'HEADER_ID',
138 l_header_id);
139
140 wf_engine.SetItemAttrText(itemtype,itemkey, 'TRANSACTION_DETAIL_URL', NULL);
141
142 END Set_blanket_Descriptor;
143 /**************************/
144
145
146
147 PROCEDURE purge_error_flow (p_item_type IN varchar2,
148 p_item_key IN varchar2)
149 -- x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
150 -- x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
151 -- x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
152 IS
153 cursor err_flow IS
154 select ITEM_TYPE, ITEM_KEY, ROOT_ACTIVITY
155 from wf_items
156 where ITEM_TYPE IN ('OMERROR','WFERROR')
157 and PARENT_ITEM_TYPE = p_item_type
158 and PARENT_ITEM_KEY = p_item_key
159 and END_DATE is null;
160 --
161 l_item_key varchar2(30);
162 l_item_type varchar2(30); -- := 'OMERROR';
163 l_process_name VARCHAR2(30);
164
165 BEGIN
166
167 -- There could be multiple error flows associated with this item key so
168 -- we want to purge all of them.
169 oe_debug_pub.add('Entering purge_error_flow for itemtype/itemkey:' || p_item_type || '/' || p_item_key);
170 open err_flow;
171 loop
172 fetch err_flow into l_item_type, l_item_key, l_process_name;
173 exit when err_flow%NOTFOUND;
174 OE_Debug_PUB.Add('Purge Error Flow for: ' || p_item_type || '/' || p_item_key);
175
176 /* Abort the process before it can be purged */
177 wf_engine.abortprocess(itemtype => l_item_type,
178 itemkey => l_item_key,
179 process => l_process_name);
180 /* Now purge the process */
181 wf_purge.items(itemtype => l_item_type,
182 itemkey => l_item_key,
183 force => TRUE,
184 docommit => false);
185 end loop;
186
187
188 oe_debug_pub.add('Exiting purge_error_flow' );
189 end purge_error_flow;
190
191 PROCEDURE Initialize_Errors( itemtype VARCHAR2,
192 itemkey VARCHAR2,
193 actid NUMBER,
194 funcmode VARCHAR2,
195 result OUT NOCOPY VARCHAR2 ) IS
196
197 l_error_itemtype VARCHAR2(8);
198 l_error_itemkey VARCHAR2(240);
199 l_error_name VARCHAR2(30);
200 l_error_msg VARCHAR2(2000);
201 l_timeout PLS_INTEGER;
202 l_administrator VARCHAR2(100);
203
204 BEGIN
205
206 IF (funcmode = 'RUN') THEN
207
208 --
209 -- Get the type and the key of the process that errored out
210 -- these were set in the erroring out process by Execute_Error_Process
211 --
212 l_error_itemkey := WF_ENGINE.GetItemAttrText(
213 itemtype => itemtype,
214 itemkey => itemkey,
215 aname => 'ERROR_ITEM_KEY' );
216 l_error_itemtype := WF_ENGINE.GetItemAttrText(
217 itemtype => itemtype,
218 itemkey => itemkey,
219 aname => 'ERROR_ITEM_TYPE' );
220
221 --
222 -- Check if the workflow administrator exists
223 -- If it does, then assign the notification to this role
224 --
225
226 begin
227 --if this item type doesnt exist an exception is raised.
228 l_administrator := WF_ENGINE.GetItemAttrText(
229 itemtype => l_error_itemtype,
230 itemkey => l_error_itemkey,
231 aname => 'WF_ADMINISTRATOR' );
232
233 /*begin
234 wf_engine.AssignActivity(itemtype,itemkey,
235 'OM_ERROR_RETRY_ONLY',
236 l_administrator);
237 exception
238 when OTHERS then
239 null;
240 end;*/ -- Commented for Bug# 5251478
241
242 wf_engine.AssignActivity(itemtype,itemkey,'R_ERROR_RETRY:NOTIFY',
243 l_administrator); -- Bug# 5251478
244
245 exception
246 when others then null;
247 end;
248
249 result := wf_engine.eng_completed;
250 ELSIF (funcmode = 'CANCEL') THEN
251 result := wf_engine.eng_completed;
252 END IF;
253 EXCEPTION
254 WHEN OTHERS THEN
255 WF_CORE.Context('OE_ERROR_WF', 'Initialize_Errors',
256 itemtype, itemkey, actid, funcmode);
257 RAISE;
258 END Initialize_Errors;
259
260
261 procedure update_process_messages (itemtype in varchar2,
262 itemkey in varchar2,
263 actid in number,
264 funcmode in varchar2,
265 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
266 is
267
268 l_conc_req_id number;
269 err_itemtype varchar2(8);
270 err_itemkey varchar2(240);
271 l_header_id number;
272 l_line_id number;
273 l_activity_id number;
274
275 l_conc_msg varchar2(2000);
276
277 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
278 --
279 l_conc_req_url varchar2(2000);
280 l_mgr_log varchar2(2000);
281 l_result boolean;
282 l_gwyuid varchar2(32);
283 l_two_task varchar2(64);
284 l_orig_sys_document_ref VARCHAR2(50);
285 l_change_sequence VARCHAR2(50);
286 l_order_source_id NUMBER;
287 l_sold_to_org_id NUMBER;
288 l_org_id NUMBER;
289 begin
290 if (funcmode = 'RUN' ) then
291 err_itemtype := Wf_Engine.GetItemAttrText(itemtype, itemkey,
292 'ERROR_ITEM_TYPE');
293 err_itemkey := Wf_Engine.GetItemAttrText(itemtype, itemkey,
294 'ERROR_ITEM_KEY');
295
296 l_conc_req_id := WF_ENGINE.GetItemAttrNumber(
297 itemtype => itemtype,
298 itemkey => itemkey,
299 aname => 'CONC_REQ_ID' );
300 IF err_itemtype = 'OEOH' THEN
301 l_header_id := to_number(err_itemkey);
302 ELSIF err_itemtype = 'OEOL' THEN
303 l_line_id := to_number(err_itemkey);
304 select header_id
305 into l_header_id
306 from oe_order_lines_all
307 where line_id = l_line_id;
308 ELSIF err_itemtype IN ('OEOI','OESO','OEOA','OEXWFEDI') THEN
309 -- submit it if order exists
310 Get_EM_Key_Info (p_itemtype => err_itemtype,
311 p_itemkey => err_itemkey,
312 x_order_source_id => l_order_source_id,
313 x_orig_sys_document_ref => l_orig_sys_document_ref,
314 x_sold_to_org_id => l_sold_to_org_id,
315 x_change_sequence => l_change_sequence,
316 x_header_id => l_header_id,
317 x_org_id => l_org_id);
318 END IF;
319 l_activity_id := WF_ENGINE.GetItemAttrNumber(
320 itemtype => itemtype,
321 itemkey => itemkey,
322 aname => 'ERROR_ACTIVITY_ID');
323
324 oe_standard_wf.set_msg_context(l_activity_id);
325
326 IF err_itemtype IN ('OEOI','OESO','OEOA','OEXWFEDI') THEN
327 OE_MSG_PUB.set_msg_context(
328 p_entity_code => 'ELECMSG_'||err_itemtype
329 ,p_entity_id => err_itemkey
330 ,p_header_id => l_header_id
331 ,p_line_id => null
332 ,p_order_source_id => l_order_source_id
333 ,p_orig_sys_document_ref => l_orig_sys_document_ref
334 ,p_orig_sys_document_line_ref => null
335 ,p_orig_sys_shipment_ref => null
336 ,p_change_sequence => l_change_sequence
337 ,p_source_document_type_id => null
338 ,p_source_document_id => null
339 ,p_source_document_line_id => null );
340
341 ELSE
342 oe_msg_pub.set_msg_context(
343 p_header_id => l_header_id
344 ,p_line_id => l_line_id);
345 END IF;
346 fnd_message.set_name('ONT', 'ONT_CONC_MSG');
347 fnd_message.set_token('CONC_REQ_ID', l_conc_req_id);
348 OE_MSG_PUB.Add;
349 OE_STANDARD_WF.Save_Messages;
350 OE_STANDARD_WF.Clear_Msg_Context;
351
352 fnd_message.set_name('ONT', 'ONT_CONC_MSG');
353 fnd_message.set_token('CONC_REQ_ID', l_conc_req_id);
354 l_conc_msg := fnd_message.get;
355 wf_engine.SetItemAttrText(itemtype, itemkey, 'ENTITY_DESCRIPTOR_LINE1',
356 l_conc_msg);
357
358 -- l_gwyuid := fnd_utilities.getenv('GWYUID');
359 -- l_two_task := fnd_utilities.getenv('TWO_TASK');
360
361 -- l_result := fnd_webfile.get_req_log_urls(
362 -- request_id => l_conc_req_id,
363 -- gwyuid => l_gwyuid,
364 -- two_task => l_two_task,
365 -- expire_time => null,
366 -- req_log => l_conc_req_url,
367 -- mgr_log => l_mgr_log);
368
369 --wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE1',
370 -- l_conc_req_url);
371
372 -- oe_debug_pub.add ('l_result:' || l_result);
373 -- oe_debug_pub.add ('URL:' || l_conc_req_url);
374 -- oe_debug_pub.add ('l_mgr_log:' || l_mgr_log);
375
376
377 resultout := 'COMPLETE';
378 return;
379 end if; -- funcmode = 'RUN'
380
381 if (funcmode = 'CANCEL') then
382
383 -- your cancel code goes here
384 null;
385
386 -- no result needed
387 resultout := 'COMPLETE';
388 return;
389 end if;
390
391 end update_process_messages;
392
393
394
395
396 procedure Set_entity_Descriptor(itemtype in varchar2,
397 itemkey in varchar2,
398 actid in number,
399 funcmode in varchar2,
400 resultout in out NOCOPY /* file.sql.39 change */ varchar2)
401 is
402 --
403 l_header_id NUMBER;
404 l_order_number NUMBER;
405 l_order_type_id NUMBER;
406 l_order_type_name VARCHAR2(80);
407 l_order_category_code VARCHAR2(30);
408 l_order_type_txt VARCHAR2(2000);
409 l_header_txt VARCHAR2(2000);
410 l_descriptor VARCHAR2(2000);
411 l_descriptor_line1 VARCHAR2(2000);
412 l_descriptor_line2 VARCHAR2(2000);
413 --
414 l_line_txt VARCHAR2(2000);
415 l_line_number NUMBER;
416 l_shipment_number NUMBER;
417 l_option_number NUMBER;
418 l_service_number NUMBER;
419 --
420 l_itemtype varchar2(8);
421 l_itemkey varchar2(240);
422 err_itemtype varchar2(8);
423 err_itemkey varchar2(240);
424 --
425 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
426 --
427 l_salesrep VARCHAR2(240) := NULL;
428 l_salesrep_id NUMBER;
429 l_org_id NUMBER;
430 l_oper_unit_name VARCHAR2(240) := NULL;
431 l_version_number VARCHAR2(240);
432 l_flow_status_code VARCHAR2(30);
433 l_flow_status_code_meaning VARCHAR2(80);
434
435 l_oper_unit_name_text VARCHAR2(2000);
436 l_salesrep_text VARCHAR2(2000);
437
438 l_url VARCHAR2(1000);
439 l_profile_val VARCHAR2(30);
440 l_result_code VARCHAR2(30);
441 l_concat_line_num VARCHAR2(30);
442 l_orig_sys_document_ref VARCHAR2(50);
443 l_change_sequence VARCHAR2(50);
444 l_order_source_id NUMBER;
445 l_sold_to_org_id NUMBER;
446 l_order_source VARCHAR2(240);
447 l_sold_to_org VARCHAR2(360);
448 l_order_exists BOOLEAN := false;
449 l_cust_number VARCHAR2(30);
450 begin
451 if (funcmode = 'RUN' ) then
452 -- Get the item key and item type of the error process
453
454 -- XXXX change this do we need this?
455 --FND_GLOBAL.Apps_Initialize(1318, 21623, 660);
456
457
458
459 err_itemtype := Wf_Engine.GetItemAttrText(itemtype, itemkey,
460 'ERROR_ITEM_TYPE');
461 err_itemkey := Wf_Engine.GetItemAttrText(itemtype, itemkey,
462 'ERROR_ITEM_KEY');
463
464 IF err_itemtype = 'OEBH' THEN
465 -- At this time we do not have the generate diagnostics for blankets.
466 l_result_code := 'BYPASS_REQUEST';
467 -- We use a different message for blankets.
468 wf_engine.SetItemAttrText(itemtype, itemkey, 'MESSAGE_NAME', 'OMERROR_MSG_NO_URL');
469 ELSE
470 l_profile_val := FND_PROFILE.VALUE('ONT_GENERATE_DIAGNOSTICS');
471 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' AND G_BATCH_RETRY_FLAG = 'N' THEN
472 l_result_code := 'SUBMIT_REQUEST';
473 ELSE
474 l_result_code := 'BYPASS_REQUEST';
475 END IF;
476 wf_engine.SetItemAttrText(itemtype, itemkey, 'MESSAGE_NAME', 'OMERROR_MSG');
477 END IF;
478
479
480
481
482 if err_itemtype = 'OEOH' OR err_itemtype = 'OENH' THEN
483
484 l_header_id := err_itemkey;
485
486 SELECT order_number, order_type_id, order_category_code,
487 org_id, SALESREP_ID, VERSION_NUMBER, FLOW_STATUS_CODE
488 into l_order_number, l_order_type_id, l_order_category_code,
489 l_org_id, l_salesrep_id, l_version_number, l_flow_status_code
490 from oe_order_headers_all
491 where header_id = err_itemkey;
492
493 SELECT T.NAME
494 INTO l_order_type_name
495 FROM OE_TRANSACTION_TYPES_TL T
496 WHERE T.LANGUAGE = userenv('LANG')
497 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
498
499 SELECT name
500 INTO l_oper_unit_name
501 FROM HR_OPerating_units
502 WHERE ORGANIZATION_ID = l_org_id;
503
504 SELECT name
505 INTO l_salesrep
506 FROM ra_salesreps
507 WHERE salesrep_id = l_salesrep_id;
508
509 IF l_flow_status_code is not NULL THEN
510 SELECT MEANING
511 INTO l_flow_status_code_meaning
512 FROM oe_lookups
513 where LOOKUP_CODE = l_flow_status_code
514 AND LOOKUP_TYPE = 'FLOW_STATUS';
515 END IF;
516
517 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
518 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
519 l_order_type_txt := fnd_message.get;
520 IF l_order_category_code = 'RETURN' THEN
521 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
522 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
523 l_header_txt := fnd_message.get;
524 ELSE
525 if err_itemtype = 'OENH' THEN
526 fnd_message.set_name('ONT', 'OE_NEGO_SALES_ORDER');
527 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
528 ELSE
529 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
530 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
531 END IF;
532 l_header_txt := fnd_message.get;
533 END IF;
534
535 -- fnd_message.set_name('ONT', 'OE_WF_OPER_UNIT');
536 -- fnd_message.set_token('OPER_UNIT', l_oper_unit_name);
537 -- l_oper_unit_name_text := fnd_message.get;
538
539 -- fnd_message.set_name('ONT', 'OE_WF_SALES_REP');
540 -- fnd_message.set_token('SALES_REP', l_salesrep);
541 -- l_salesrep_text := fnd_message.get;
542
543
544 l_descriptor := substrb(l_order_type_txt || ', ' ||
545 l_header_txt, 1, 240);
546 -- l_descriptor_line1 := l_oper_unit_name_text;
547 -- l_descriptor_line2 := l_salesrep_text;
548
549
550 wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_SHORT_DESCRIPTOR', l_descriptor);
551
552 wf_engine.SetItemAttrText(itemtype,itemkey, 'OPERATING_UNIT',
553 l_oper_unit_name);
554 fnd_message.set_name ('ONT', 'OE_WF_VERSION_NUMBER');
555 fnd_message.set_token('VERSION_NUMBER', l_version_number);
556 wf_engine.SetItemAttrText(itemtype,itemkey, 'VERSION_NUMBER',
557 FND_MESSAGE.GET);
558 fnd_message.set_name ('ONT', 'OE_WF_FLOW_STATUS');
559 fnd_message.set_token('FLOW_STATUS', l_flow_status_code_meaning);
560 wf_engine.SetItemAttrText(itemtype,itemkey, 'FLOW_STATUS',
561 FND_MESSAGE.GET);
562
563 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE1',
564 -- l_descriptor_line1);
565 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE2',
566 -- l_descriptor_line2);
567
568 wf_engine.SetItemAttrNumber(itemtype,itemkey, 'HEADER_ID',
569 l_header_id);
570
571 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110506' THEN
572 l_url := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/')||'/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE' || '&' || 'akRegionApplicationId=660' || '&' || 'HeaderId=' || l_header_id;
573 wf_engine.SetItemAttrText(itemtype,itemkey, 'TRANSACTION_DETAIL_URL', l_url);
574 END IF;
575
576
577 resultout := 'COMPLETE:' || l_result_code;
578 return;
579
580 elsif err_itemtype = 'OEOL' THEN
581
582 SELECT header_id, FLOW_STATUS_CODE,
583 line_number, shipment_number, option_number, service_number
584 into l_header_id, l_flow_status_code,
585 l_line_number, l_shipment_number, l_option_number, l_service_number
586 FROM oe_order_lines_all
587 WHERE line_id = err_itemkey;
588
589 SELECT order_number, order_type_id, order_category_code,
590 org_id, SALESREP_ID, VERSION_NUMBER
591 into l_order_number, l_order_type_id, l_order_category_code,
592 l_org_id, l_salesrep_id, l_version_number
593 from oe_order_headers_all
594 where header_id = l_header_id;
595
596 SELECT T.NAME
597 INTO l_order_type_name
598 FROM OE_TRANSACTION_TYPES_TL T
599 WHERE T.LANGUAGE = userenv('LANG')
600 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
601
602 SELECT name
603 INTO l_oper_unit_name
604 FROM HR_OPerating_units
605 WHERE ORGANIZATION_ID = l_org_id;
606
607 SELECT name
608 INTO l_salesrep
609 FROM ra_salesreps
610 WHERE salesrep_id = l_salesrep_id;
611
612 IF l_flow_status_code is not NULL then
613 SELECT MEANING
614 INTO l_flow_status_code_meaning
615 FROM oe_lookups
616 where LOOKUP_CODE = l_flow_status_code
617 AND LOOKUP_TYPE = 'LINE_FLOW_STATUS';
618 END IF;
619
620 IF l_order_category_code = 'RETURN' THEN
621 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
622 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
623 l_header_txt := fnd_message.get;
624 ELSE
625 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
626 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
627 l_header_txt := fnd_message.get;
628 END IF;
629
630 l_concat_line_num := OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(p_line_id => to_number(err_itemkey));
631
632 -- Do we need this?? XXXXXX
633 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
634 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
635 l_order_type_txt := fnd_message.get;
636
637 IF l_order_category_code = 'RETURN' THEN
638 fnd_message.set_name('ONT', 'OE_WF_CONCAT_RETURN_LINE');
639 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
640 fnd_message.set_token('CONCAT_LINE_NUMBER', l_concat_line_num);
641
642 l_line_txt := fnd_message.get;
643 ELSE
644 fnd_message.set_name('ONT', 'OE_WF_CONCAT_LINE');
645 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
646 fnd_message.set_token('CONCAT_LINE_NUMBER', l_concat_line_num);
647
648 l_line_txt := fnd_message.get;
649 END IF;
650
651 -- fnd_message.set_name('ONT', 'OE_WF_OPER_UNIT');
652 -- fnd_message.set_token('OPER_UNIT', l_oper_unit_name);
653 -- l_oper_unit_name_text := fnd_message.get;
654
655 -- fnd_message.set_name('ONT', 'OE_WF_SALES_REP');
656 -- fnd_message.set_token('SALES_REP', l_salesrep);
657 -- l_salesrep_text := fnd_message.get;
658
659
660 -- l_descriptor_line1 := l_oper_unit_name_text;
661 -- l_descriptor_line2 := l_salesrep_text;
662
663 -- Line Text
664 l_descriptor := substrb(l_order_type_txt || ', ' ||
665 l_line_txt, 1, 240);
666
667
668 wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_SHORT_DESCRIPTOR', l_descriptor);
669
670 wf_engine.SetItemAttrText(itemtype,itemkey, 'OPERATING_UNIT',
671 l_oper_unit_name);
672
673 fnd_message.set_name ('ONT', 'OE_WF_VERSION_NUMBER');
674 fnd_message.set_token('VERSION_NUMBER', l_version_number);
675 wf_engine.SetItemAttrText(itemtype,itemkey, 'VERSION_NUMBER',
676 FND_MESSAGE.GET);
677 fnd_message.set_name ('ONT', 'OE_WF_FLOW_STATUS');
678 fnd_message.set_token('FLOW_STATUS', l_flow_status_code_meaning);
679 wf_engine.SetItemAttrText(itemtype,itemkey, 'FLOW_STATUS',
680 FND_MESSAGE.GET);
681
682 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE1',
683 -- l_descriptor_line1);
684 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE2',
685 -- l_descriptor_line2);
686
687 wf_engine.SetItemAttrNumber(itemtype,itemkey, 'HEADER_ID',
688 l_header_id);
689 l_url := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/')||'/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE' || '&' || 'akRegionApplicationId=660' || '&' || 'HeaderId=' || l_header_id;
690 wf_engine.SetItemAttrText(itemtype,itemkey, 'TRANSACTION_DETAIL_URL', l_url);
691 resultout := 'COMPLETE:' || l_result_code;
692 return;
693
694 elsif err_itemtype IN ('OESO','OEOI','OEOA','OEXWFEDI') THEN
695
696 -- need to derive Order Source Id, Orig Sys Document Ref,
697 -- Sold To Org Id, Change Sequence
698 -- and Header id etc if the order exists
699
700 Get_EM_Key_Info (p_itemtype => err_itemtype,
701 p_itemkey => err_itemkey,
702 x_order_source_id => l_order_source_id,
703 x_orig_sys_document_ref => l_orig_sys_document_ref,
704 x_sold_to_org_id => l_sold_to_org_id,
705 x_change_sequence => l_change_sequence,
706 x_header_id => l_header_id,
707 x_org_id => l_org_id);
708
709
710 l_order_source := OE_Id_To_Value.Order_Source (p_order_source_id => l_order_source_id);
711 IF l_sold_to_org_id IS NOT NULL THEN
712 OE_Id_To_Value.Sold_To_Org (p_sold_to_org_id => l_sold_to_org_id,
713 x_org => l_sold_to_org,
714 x_customer_number => l_cust_number);
715 END IF;
716
717 FND_MESSAGE.SET_NAME ('ONT','OE_EM_KEY_INFO');
718 FND_MESSAGE.SET_TOKEN ('ORDER_SOURCE', l_order_source);
719 FND_MESSAGE.SET_TOKEN ('ORIG_SYS_DOCUMENT_REF', l_orig_sys_document_ref);
720 FND_MESSAGE.SET_TOKEN ('CUSTOMER', l_sold_to_org);
721 l_descriptor := FND_MESSAGE.GET;
722
723 wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_SHORT_DESCRIPTOR',
724 l_descriptor);
725
726 IF l_org_id IS NOT NULL THEN
727 SELECT name
728 INTO l_oper_unit_name
729 FROM HR_OPerating_units
730 WHERE ORGANIZATION_ID = l_org_id;
731 END IF;
732
733 If (l_header_id IS NOT NULL) Then
734 wf_engine.SetItemAttrText(itemtype, itemkey, 'MESSAGE_NAME', 'OMERROR_MSG');
735
736 SELECT order_number, order_type_id, order_category_code,
737 SALESREP_ID, VERSION_NUMBER, FLOW_STATUS_CODE
738 into l_order_number, l_order_type_id, l_order_category_code,
739 l_salesrep_id, l_version_number, l_flow_status_code
740 from oe_order_headers_all
741 where header_id = l_header_id;
742
743 BEGIN
744 SELECT T.NAME
745 INTO l_order_type_name
746 FROM OE_TRANSACTION_TYPES_TL T
747 WHERE T.LANGUAGE = userenv('LANG')
748 AND T.TRANSACTION_TYPE_ID = l_order_type_id;
749 EXCEPTION WHEN OTHERS THEN
750 l_order_type_name := NULL;
751 END;
752
753 BEGIN
754 SELECT MEANING
755 INTO l_flow_status_code_meaning
756 FROM oe_lookups
757 where LOOKUP_CODE = l_flow_status_code
758 AND LOOKUP_TYPE = 'FLOW_STATUS';
759 EXCEPTION WHEN OTHERS THEN
760 l_flow_status_code_meaning := NULL;
761 END;
762
763 fnd_message.set_name('ONT', 'OE_WF_ORDER_TYPE');
764 fnd_message.set_token('ORDER_TYPE', l_order_type_name);
765 l_order_type_txt := fnd_message.get;
766 IF l_order_category_code = 'RETURN' THEN
767 fnd_message.set_name('ONT', 'OE_WF_RETURN_ORDER');
768 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
769 l_header_txt := fnd_message.get;
770 ELSE
771 fnd_message.set_name('ONT', 'OE_WF_SALES_ORDER');
772 fnd_message.set_token('ORDER_NUMBER', to_char(l_order_number));
773 l_header_txt := fnd_message.get;
774 END IF;
775
776 -- fnd_message.set_name('ONT', 'OE_WF_OPER_UNIT');
777 -- fnd_message.set_token('OPER_UNIT', l_oper_unit_name);
778 -- l_oper_unit_name_text := fnd_message.get;
779
780 -- fnd_message.set_name('ONT', 'OE_WF_SALES_REP');
781 -- fnd_message.set_token('SALES_REP', l_salesrep);
782 -- l_salesrep_text := fnd_message.get;
783
784
785 l_descriptor_line2 := substrb(l_order_type_txt || ', ' ||
786 l_header_txt, 1, 240);
787 -- l_descriptor_line1 := l_oper_unit_name_text;
788 -- l_descriptor_line2 := l_salesrep_text;
789
790
791 wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE2', l_descriptor_line2);
792
793 fnd_message.set_name ('ONT', 'OE_WF_VERSION_NUMBER');
794 fnd_message.set_token('VERSION_NUMBER', l_version_number);
795 wf_engine.SetItemAttrText(itemtype,itemkey, 'VERSION_NUMBER',
796 FND_MESSAGE.GET);
797 fnd_message.set_name ('ONT', 'OE_WF_FLOW_STATUS');
798 fnd_message.set_token('FLOW_STATUS', l_flow_status_code_meaning);
799 wf_engine.SetItemAttrText(itemtype,itemkey, 'FLOW_STATUS',
800 FND_MESSAGE.GET);
801
802 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE1',
803 -- l_descriptor_line1);
804 -- wf_engine.SetItemAttrText(itemtype,itemkey, 'ENTITY_DESCRIPTOR_LINE2',
805 -- l_descriptor_line2);
806
807 IF OE_CODE_CONTROL.CODE_RELEASE_LEVEL >= '110506' THEN
808 l_url := rtrim(fnd_profile.Value('APPS_FRAMEWORK_AGENT'), '/')||'/OA_HTML/OA.jsp?akRegionCode=ORDER_DETAILS_PAGE' || '&' || 'akRegionApplicationId=660' || '&' || 'HeaderId=' || l_header_id;
809 wf_engine.SetItemAttrText(itemtype,itemkey, 'TRANSACTION_DETAIL_URL', l_url);
810 END IF;
811 else -- l_header_id is null so don't submit the concurrent request
812 l_result_code := 'BYPASS_REQUEST';
813 wf_engine.SetItemAttrText(itemtype,itemkey, 'TRANSACTION_DETAIL_URL', NULL);
814 wf_engine.SetItemAttrText(itemtype, itemkey, 'MESSAGE_NAME', 'OMERROR_MSG_NO_URL');
815
816 end if;
817 wf_engine.SetItemAttrText(itemtype,itemkey, 'OPERATING_UNIT',
818 l_oper_unit_name);
819 wf_engine.SetItemAttrNumber(itemtype,itemkey, 'HEADER_ID',
820 l_header_id);
821
822 resultout := 'COMPLETE:' || l_result_code;
823 return;
824
825 elsif err_itemtype = 'OEBH' THEN
826 Set_blanket_Descriptor ( itemtype, itemkey, err_itemtype, err_itemkey);
827 resultout := 'COMPLETE:' || l_result_code;
828 return;
829
830 end if; -- err_itemtype = 'OEOH'
831
832
833 end if; -- funcmode = 'RUN'
834
835 --
836 -- CANCEL mode - activity 'compensation'
837 --
838 -- This is an event point is called with the effect of the activity must
839 -- be undone, for example when a process is reset to an earlier point
840 -- due to a loop back.
841 --
842 if (funcmode = 'CANCEL') then
843
844 -- your cancel code goes here
845 null;
846
847 -- no result needed
848 resultout := 'COMPLETE';
849 return;
850 end if;
851
852 exception
853 when others then
854 Wf_Core.Context('OE_STANDARD_WF', 'STANDARD_BLOCK', itemtype,
855 itemkey, to_char(actid), funcmode);
856 raise;
857 end Set_entity_Descriptor;
858
859
860 -----------------------------------------------
861 -- The following two APIs are copied from WF --
862 -----------------------------------------------
863 -- -------------------------------------------------------------------
864 -- CheckErrorActive
865 -- checks if an error is still active and returns TRUE/FALSE.
866 -- Use this in an error process to exit out of a timeout loop
867 -- Called by default error process.
868 -- -------------------------------------------------------------------
869 PROCEDURE Check_Error_Active( itemtype IN VARCHAR2,
870 itemkey IN VARCHAR2,
871 actid IN NUMBER,
872 funcmode IN VARCHAR2,
873 result OUT NOCOPY VARCHAR2 ) IS
874
875 l_error_itemtype VARCHAR2(8);
876 l_error_itemkey VARCHAR2(240);
877 l_error_actid NUMBER;
878 status VARCHAR2(30);
879
880 cursor activity_status (litemtype varchar2, litemkey varchar2, lactid number ) is
881 select WIAS.ACTIVITY_STATUS
882 from WF_ITEM_ACTIVITY_STATUSES WIAS
883 where WIAS.ITEM_TYPE = litemtype
884 and WIAS.ITEM_KEY = litemkey
885 and WIAS.PROCESS_ACTIVITY = lactid;
886
887
888 BEGIN
889
890 IF (funcmode = 'RUN') THEN
891
892 --
893 -- Get the type and the key of the process that errored out
894 -- these were set in the erroring out process by Execute_Error_Process
895 --
896 l_error_itemkey := WF_ENGINE.GetItemAttrText(
897 itemtype => itemtype,
898 itemkey => itemkey,
899 aname => 'ERROR_ITEM_KEY' );
900 l_error_itemtype := WF_ENGINE.GetItemAttrText(
901 itemtype => itemtype,
902 itemkey => itemkey,
903 aname => 'ERROR_ITEM_TYPE' );
904
905 l_error_actid := WF_ENGINE.GetItemAttrText(
906 itemtype => itemtype,
907 itemkey => itemkey,
908 aname => 'ERROR_ACTIVITY_ID' );
909
910 open activity_status(l_error_itemtype, l_error_itemkey, l_error_actid);
911 fetch activity_status into status;
912 close activity_status;
913
914 if status = 'ERROR' then
915 result:='TRUE';
916 else
917 result:='FALSE';
918 end if;
919
920 END IF;
921
922 EXCEPTION
923 WHEN OTHERS THEN
924 WF_CORE.Context('OE_STANDARD_WF', 'Check_Error_Active',
925 itemtype, itemkey, actid, funcmode);
926 RAISE;
927 END Check_Error_Active;
928
929
930 -- ResetError
931 -- Reset the status of an errored activity in an WFERROR process.
932 -- OUT NOCOPY
933 -- result - 'NULL'
934 -- ACTIVITY ATTRIBUTES REFERENCED
935 -- COMMAND - 'SKIP' or 'RETRY'
936 -- 'SKIP' marks the errored activity complete and continues processing
937 -- 'RETRY' clears the errored activity and runs it again
938 -- RESULT - Result code to complete the activity with if COMMAND = 'SKIP'
939 procedure Reset_Error(itemtype in varchar2,
940 itemkey in varchar2,
941 actid in number,
942 funcmode in varchar2,
943 resultout in out nocopy varchar2)
944 is
945 cmd varchar2(8);
946 result varchar2(30);
947 err_itemtype varchar2(8);
948 err_itemkey varchar2(240);
949 err_actlabel varchar2(62);
950 wf_invalid_command exception;
951 err_actid number;
952 l_header_id number;
953 l_orig_sys_document_ref VARCHAR2(50);
954 l_change_sequence VARCHAR2(50);
955 l_order_source_id NUMBER;
956 l_sold_to_org_id NUMBER;
957 l_org_id NUMBER;
958 err_actname VARCHAR2(30);
959 err_actitemtype VARCHAR2(8);
960 begin
961 -- Do nothing in cancel or timeout mode
962 if (funcmode <> wf_engine.eng_run) then
963 resultout := wf_engine.eng_null;
964 return;
965 end if;
966
967 -- SYNCHMODE: Not allowed
968 if (itemkey = wf_engine.eng_synch) then
969 Wf_Core.Token('OPERATION', 'Wf_Standard.ResetError');
970 Wf_Core.Raise('WFENG_SYNCH_DISABLED');
971 end if;
972
973 -- Get RETRY or SKIP command
974 cmd := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid, 'COMMAND');
975
976 -- Get original errored activity info
977 err_itemtype := Wf_Engine.GetItemAttrText(itemtype, itemkey,
978 'ERROR_ITEM_TYPE');
979 err_itemkey := Wf_Engine.GetItemAttrText(itemtype, itemkey,
980 'ERROR_ITEM_KEY');
981 err_actlabel := Wf_Engine.GetItemAttrText(itemtype, itemkey,
982 'ERROR_ACTIVITY_LABEL');
983 if (cmd = wf_engine.eng_retry) then
984 -- Rerun activity
985
986
987 err_actid := Wf_Engine.GetItemAttrNumber(itemtype, itemkey,
988 'ERROR_ACTIVITY_ID');
989
990 l_header_id := Wf_Engine.GetItemAttrNumber(itemtype, itemkey,
991 'HEADER_ID');
992
993 if err_itemtype = 'OEOH' THEN
994 OE_MSG_PUB.Update_Status_Code(
995 p_header_id => l_header_id,
996 p_process_activity => err_actid,
997 p_status_code => 'CLOSED');
998
999 elsif err_itemtype = 'OEOL' THEN
1000 OE_MSG_PUB.Update_Status_Code(
1001 p_header_id => l_header_id,
1002 p_line_id => err_itemkey,
1003 p_process_activity => err_actid,
1004 p_status_code => 'CLOSED');
1005 elsif err_itemtype IN ('OEOA','OEOI','OESO','OEXWFEDI') THEN
1006
1007 Get_EM_Key_Info (p_itemtype => err_itemtype,
1008 p_itemkey => err_itemkey,
1009 x_order_source_id => l_order_source_id,
1010 x_orig_sys_document_ref => l_orig_sys_document_ref,
1011 x_sold_to_org_id => l_sold_to_org_id,
1012 x_change_sequence => l_change_sequence,
1013 x_header_id => l_header_id,
1014 x_org_id => l_org_id);
1015
1016 /* l_order_source_id := Wf_Engine.GetItemAttrNumber(itemtype, itemkey,
1017 'ORDER_SOURCE_ID');
1018 l_orig_sys_document_ref := Wf_Engine.GetItemAttrText(itemtype, itemkey,
1019 'ORIG_SYS_DOCUMENT_REF');*/
1020 OE_MSG_PUB.Update_Status_Code(
1021 p_order_source_id => l_order_source_id,
1022 p_orig_sys_document_ref => l_orig_sys_document_ref,
1023 p_entity_code => 'ELECMSG_'||err_itemtype,
1024 p_entity_id => to_number(err_itemkey),
1025 p_process_activity => err_actid,
1026 p_status_code => 'CLOSED');
1027 end if;
1028
1029 IF err_itemtype = 'OEOL' THEN
1030 BEGIN
1031 WF_Process_Activity.ActivityName (err_actid,err_actitemtype,err_actname);
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 err_actname := NULL;
1035 END;
1036 END IF;
1037
1038 IF NOT (err_itemtype = 'OEOL' AND err_actname IS NOT NULL AND err_actname = 'SHIP_LINE'
1039 AND Check_Closed_Delivery_Detail (err_itemkey, err_actid)) THEN
1040 Wf_Engine.HandleError(err_itemtype, err_itemkey, err_actlabel,
1041 cmd, '');
1042 END IF;
1043
1044 /* Disallow skip mode because it is too difficult to
1045 assign and validate the RESULT value
1046 elsif (cmd = wf_engine.eng_skip) then
1047 -- Get result code
1048 result := Wf_Engine.GetActivityAttrText(itemtype, itemkey, actid,
1049 'RESULT');
1050 -- Mark activity complete and continue processing
1051 Wf_Engine.HandleError(err_itemtype, err_itemkey, err_actlabel,
1052 cmd, result);
1053 */
1054 else
1055 raise wf_invalid_command;
1056 end if;
1057
1058 resultout := wf_engine.eng_null;
1059 exception
1060 when wf_invalid_command then
1061 Wf_Core.Context('OE_STANDARD_WF', 'Reset_Error', itemtype,
1062 itemkey, to_char(actid), funcmode);
1063 Wf_Core.Token('COMMAND', cmd);
1064 Wf_Core.Raise('WFSQL_COMMAND');
1065 when others then
1066 Wf_Core.Context('OE_STANDARD_WF', 'Reset_Error', itemtype,
1067 itemkey, to_char(actid), funcmode);
1068 raise;
1069 end Reset_Error;
1070
1071 Procedure Get_EM_Key_Info (p_itemtype IN VARCHAR2,
1072 p_itemkey IN VARCHAR2,
1073 x_order_source_id OUT NOCOPY NUMBER,
1074 x_orig_sys_document_ref OUT NOCOPY VARCHAR2,
1075 x_sold_to_org_id OUT NOCOPY NUMBER,
1076 x_change_sequence OUT NOCOPY VARCHAR2,
1077 x_header_id OUT NOCOPY NUMBER,
1078 x_org_id OUT NOCOPY NUMBER)
1079 IS
1080 l_customer_key_profile VARCHAR2(1);
1081 BEGIN
1082 If p_itemtype IN ('OEOI','OEOA','OESO') Then
1083
1084 x_order_source_id := 20;
1085
1086 if p_itemtype IN ('OESO','OEOA') then
1087 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'ORIG_SYS_DOCUMENT_REF');
1088 else
1089 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'PARAMETER2');
1090 end if;
1091
1092 x_sold_to_org_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'PARAMETER4');
1093
1094 x_change_sequence := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'PARAMETER7');
1095
1096 x_org_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'ORG_ID');
1097
1098 if p_itemtype = 'OESO' then
1099 x_header_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'HEADER_ID');
1100 else
1101 -- try to derive header id for OEOA and OEOI
1102 fnd_profile.get('ONT_INCLUDE_CUST_IN_OI_KEY', l_customer_key_profile);
1103 l_customer_key_profile := nvl(l_customer_key_profile, 'N');
1104
1105 Begin
1106 Select header_id
1107 Into x_header_id
1108 From oe_order_headers_all
1109 Where orig_sys_document_ref = x_orig_sys_document_ref
1110 And decode(l_customer_key_profile, 'Y',
1111 nvl(sold_to_org_id, -999), 1)
1112 = decode(l_customer_key_profile, 'Y',
1113 nvl(x_sold_to_org_id, -999), 1)
1114 And order_source_id = x_order_source_id;
1115
1116 Exception When Others Then
1117 x_header_id := NULL;
1118 End;
1119 end if;
1120 Elsif p_itemtype = 'OEXWFEDI' Then
1121 x_order_source_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'ORDER_SOURCE_ID');
1122
1123 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'ORIG_SYS_DOCUMENT_REF');
1124 x_sold_to_org_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'SOLD_TO_ORG_ID');
1125 x_org_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'ORG_ID');
1126 x_change_sequence := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'CHANGE_SEQUENCE');
1127 x_header_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'HEADER_ID');
1128
1129 End If;
1130
1131
1132
1133 End Get_EM_Key_Info;
1134
1135 -- overloaded leaner version for batch retry
1136 PROCEDURE Get_EM_Key_Info (p_itemtype IN VARCHAR2,
1137 p_itemkey IN VARCHAR2,
1138 x_order_source_id OUT NOCOPY NUMBER,
1139 x_orig_sys_document_ref OUT NOCOPY VARCHAR2)
1140 IS
1141 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1142 BEGIN
1143 IF l_debug_level > 0 THEN
1144 oe_debug_pub.add('Entering Get_EM_Key_Info');
1145 END IF;
1146
1147 If p_itemtype IN ('OEOI','OEOA','OESO') Then
1148
1149 x_order_source_id := 20;
1150
1151 if p_itemtype IN ('OESO','OEOA') then
1152 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'ORIG_SYS_DOCUMENT_REF');
1153 else
1154 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'PARAMETER2');
1155 end if;
1156
1157 ELSIF p_itemtype = 'OEXWFEDI' Then
1158
1159 x_order_source_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'ORDER_SOURCE_ID');
1160 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'ORIG_SYS_DOCUMENT_REF');
1161
1162 ELSIF p_itemtype = 'OEEM' THEN
1163
1164 x_order_source_id := wf_engine.GetItemAttrNumber (p_itemtype, p_itemkey, 'ORDER_SOURCE_ID');
1165 x_orig_sys_document_ref := wf_engine.GetItemAttrText (p_itemtype, p_itemkey, 'PARTNER_DOCUMENT_NO');
1166
1167 END IF;
1168 IF l_debug_level > 0 THEN
1169 oe_debug_pub.add('Exiting Get_EM_Key_Info with order_source_id: ' || x_order_source_id || ' and orig_sys_document_ref: ' || x_orig_sys_document_ref);
1170 END IF;
1171 END Get_EM_Key_Info;
1172
1173 FUNCTION Get_Activity_Display_Name (p_activity_item_type IN VARCHAR2,
1174 p_activity_name IN VARCHAR2)
1175 RETURN VARCHAR2
1176 IS
1177 l_activity_display_name VARCHAR2(80);
1178 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1179 BEGIN
1180 IF l_debug_level > 0 THEN
1181 oe_debug_pub.add('Entering Get_Activity_Display_Name');
1182 END IF;
1183 SELECT display_name
1184 INTO l_activity_display_name
1185 FROM WF_Activities_VL
1186 WHERE Name = p_activity_name
1187 AND Item_Type = p_activity_item_type
1188 AND Version = (SELECT max(version)
1189 FROM WF_Activities_VL
1190 WHERE Name = p_activity_name
1191 AND Item_Type = p_activity_item_type);
1192 IF l_debug_level > 0 THEN
1193 oe_debug_pub.add('Exiting Get_Activity_Display_Name with result: ' ||l_activity_display_name);
1194 END IF;
1195 RETURN l_activity_display_name;
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 IF l_debug_level > 0 THEN
1199 oe_debug_pub.add('Exception in Get_Activity_Display_Name, returning Activity Name instead: '||p_activity_name);
1200 END IF;
1201 RETURN p_activity_name;
1202 END Get_Activity_Display_Name;
1203
1204 PROCEDURE put(p_concat_segment IN VARCHAR2,
1205 p_activity_item_type IN VARCHAR2,
1206 p_activity_name IN VARCHAR2,
1207 p_process_item_type IN VARCHAR2,
1208 p_initial_count IN NUMBER DEFAULT NULL,
1209 p_final_count IN NUMBER DEFAULT NULL,
1210 x_activity_display_name OUT NOCOPY VARCHAR2)
1211 IS
1212 l_tab_index BINARY_INTEGER;
1213 l_stored BOOLEAN := FALSE;
1214 l_hash_value NUMBER;
1215 l_initial_count NUMBER;
1216 l_final_count NUMBER;
1217 l_activity_display_name VARCHAR2(80);
1218 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1219 BEGIN
1220 IF l_debug_level > 0 THEN
1221 oe_debug_pub.add('Entering Put');
1222 END IF;
1223
1224 l_initial_count := nvl(p_initial_count, 0);
1225 l_final_count := nvl(p_final_count, 0);
1226
1227 l_tab_index := dbms_utility.get_hash_value(p_concat_segment,1,TABLE_SIZE);
1228 IF l_debug_level > 0 THEN
1229 oe_debug_pub.add('Put:hash_value:'||l_tab_index,1);
1230 END IF;
1231 IF Count_Tbl.EXISTS(l_tab_index) THEN
1232 IF Count_Tbl(l_tab_index).concat_segment = p_concat_segment THEN
1233 Count_Tbl(l_tab_index).initial_count := Count_Tbl(l_tab_index).initial_count + l_initial_count;
1234 Count_Tbl(l_tab_index).final_count := Count_Tbl(l_tab_index).final_count + l_final_count;
1235 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1236 l_stored := TRUE;
1237 IF l_debug_level > 0 THEN
1238 oe_debug_pub.add(p_concat_segment || ' Initial ' || Count_Tbl(l_tab_index).initial_count);
1239 oe_debug_pub.add(p_concat_segment || ' Final ' || Count_Tbl(l_tab_index).final_count);
1240 END IF;
1241
1242 ELSE
1243 l_hash_value := l_tab_index;
1244 WHILE l_tab_index < TABLE_SIZE
1245 AND NOT l_stored LOOP
1246 IF Count_Tbl.EXISTS(l_tab_index) THEN
1247 IF Count_Tbl(l_tab_index).concat_segment = p_concat_segment THEN
1248 Count_Tbl(l_tab_index).initial_count := Count_Tbl(l_tab_index).initial_count + l_initial_count;
1249 Count_Tbl(l_tab_index).final_count := Count_Tbl(l_tab_index).final_count + l_final_count;
1250 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1251 l_stored := TRUE;
1252 IF l_debug_level > 0 THEN
1253 oe_debug_pub.add(p_concat_segment || ' 1Initial ' || Count_Tbl(l_tab_index).initial_count);
1254 oe_debug_pub.add(p_concat_segment || ' 1Final ' || Count_Tbl(l_tab_index).final_count);
1255 END IF;
1256 ELSE
1257 l_tab_index := l_tab_index +1;
1258 END IF;
1259 ELSE
1260 Count_Tbl(l_tab_index).initial_count := nvl(Count_Tbl(l_tab_index).initial_count,0) + l_initial_count;
1261 Count_Tbl(l_tab_index).final_count := nvl(Count_Tbl(l_tab_index).final_count,0) + l_final_count;
1262 Count_Tbl(l_tab_index).activity_display_name := Get_Activity_Display_Name (p_activity_item_type, p_activity_name);
1263 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1264 Count_Tbl(l_tab_index).activity_name := p_activity_name;
1265 Count_Tbl(l_tab_index).process_item_type := p_process_item_type;
1266 Count_Tbl(l_tab_index).concat_segment := p_concat_segment;
1267 IF l_debug_level > 0 THEN
1268 oe_debug_pub.add(p_concat_segment || ' 2Initial ' || Count_Tbl(l_tab_index).initial_count);
1269 oe_debug_pub.add(p_concat_segment || ' 2Final ' || Count_Tbl(l_tab_index).final_count);
1270 END IF;
1271 l_stored := TRUE;
1272 END IF;
1273 END LOOP;
1274 IF NOT l_stored THEN
1275 l_tab_index := 1;
1276 WHILE l_tab_index < l_hash_value
1277 AND NOT l_stored LOOP
1278 IF Count_Tbl.EXISTS(l_tab_index) THEN
1279 IF Count_Tbl(l_tab_index).concat_segment = p_concat_segment THEN
1280 Count_Tbl(l_tab_index).initial_count := Count_Tbl(l_tab_index).initial_count + l_initial_count;
1281 Count_Tbl(l_tab_index).final_count := Count_Tbl(l_tab_index).final_count + l_final_count;
1282 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1283 l_stored := TRUE;
1284 IF l_debug_level > 0 THEN
1285 oe_debug_pub.add(p_concat_segment || ' 3Initial ' || Count_Tbl(l_tab_index).initial_count);
1286 oe_debug_pub.add(p_concat_segment || ' 3Final ' || Count_Tbl(l_tab_index).final_count);
1287 END IF;
1288 ELSE
1289 l_tab_index := l_tab_index +1;
1290 END IF;
1291 ELSE
1292 Count_Tbl(l_tab_index).initial_count := nvl(Count_Tbl(l_tab_index).initial_count,0) + l_initial_count;
1293 Count_Tbl(l_tab_index).final_count := nvl(Count_Tbl(l_tab_index).final_count,0) + l_final_count;
1294 Count_Tbl(l_tab_index).activity_display_name := Get_Activity_Display_Name (p_activity_item_type, p_activity_name);
1295 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1296 Count_Tbl(l_tab_index).activity_name := p_activity_name;
1297 Count_Tbl(l_tab_index).process_item_type := p_process_item_type;
1298 Count_Tbl(l_tab_index).concat_segment := p_concat_segment;
1299 IF l_debug_level > 0 THEN
1300 oe_debug_pub.add(p_concat_segment || ' 4Initial ' || Count_Tbl(l_tab_index).initial_count);
1301 oe_debug_pub.add(p_concat_segment || ' 4Final ' || Count_Tbl(l_tab_index).final_count);
1302 END IF;
1303 l_stored := TRUE;
1304 END IF;
1305 END LOOP;
1306 END IF;
1307 END IF;
1308 ELSE
1309 Count_Tbl(l_tab_index) := Count_Rec;
1310 Count_Tbl(l_tab_index).initial_count := nvl(Count_Tbl(l_tab_index).initial_count,0) + l_initial_count;
1311 Count_Tbl(l_tab_index).final_count := nvl(Count_Tbl(l_tab_index).final_count,0) + l_final_count;
1312 Count_Tbl(l_tab_index).activity_display_name := Get_Activity_Display_Name (p_activity_item_type, p_activity_name);
1313 l_activity_display_name := Count_Tbl(l_tab_index).activity_display_name;
1314 Count_Tbl(l_tab_index).activity_name := p_activity_name;
1315 Count_Tbl(l_tab_index).process_item_type := p_process_item_type;
1316 Count_Tbl(l_tab_index).concat_segment := p_concat_segment;
1317 IF l_debug_level > 0 THEN
1318 oe_debug_pub.add(p_concat_segment || ' 5Initial ' || Count_Tbl(l_tab_index).initial_count);
1319 oe_debug_pub.add(p_concat_segment || ' 5Final ' || Count_Tbl(l_tab_index).final_count);
1320 END IF;
1321 l_stored := TRUE;
1322 END IF;
1323 x_activity_display_name := l_activity_display_name;
1324 IF l_debug_level > 0 THEN
1325 oe_debug_pub.add('Exiting Put with activity display name: ' || l_activity_display_name);
1326 END IF;
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 IF l_debug_level > 0 THEN
1330 oe_debug_pub.add('Exiting Put with unexpected error: ' || SQLERRM);
1331 END IF;
1332 NULL;
1333 END put;
1334
1335
1336 FUNCTION Check_Closed_Delivery_Detail (p_item_key IN VARCHAR2,
1337 p_process_activity IN NUMBER)
1338 RETURN BOOLEAN
1339 IS
1340 l_count NUMBER;
1341 l_source_code_oe CONSTANT VARCHAR2(2) := 'OE';
1342 l_released_status_closed CONSTANT VARCHAR2(1) := 'C';
1343 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1344 BEGIN
1345 IF l_debug_level > 0 THEN
1346 oe_debug_pub.add('Entering Check_Closed_Delivery_Detail');
1347 END IF;
1348 SELECT 1
1349 INTO l_count
1350 FROM wsh_delivery_details
1351 WHERE source_line_id = to_number(p_item_key)
1352 AND source_code = l_source_code_oe
1353 AND released_status = l_released_status_closed
1354 AND rownum < 2;
1355 IF l_debug_level > 0 THEN
1356 oe_debug_pub.add('Closed delivery detail exists');
1357 END IF;
1358
1359 BEGIN
1360 Wf_Item_Activity_Status.Create_Status (itemtype => 'OEOL',
1361 itemkey => p_item_key,
1362 actid => p_process_activity,
1363 status => wf_engine.eng_notified,
1364 result => wf_engine.eng_null,
1365 beginning => SYSDATE,
1366 ending => null);
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 IF l_debug_level > 0 THEN
1370 oe_debug_pub.add('Unexpected error: Cound not create notified status '|| SQLERRM);
1371 END IF;
1372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR; -- cause rollback in caller
1373 END;
1374
1375 IF l_debug_level > 0 THEN
1376 oe_debug_pub.add('Exiting Check_Closed_Delivery_Detail, Return True');
1377 END IF;
1378 RETURN TRUE;
1379 EXCEPTION
1380 WHEN NO_DATA_FOUND THEN
1381 IF l_debug_level > 0 THEN
1382 oe_debug_pub.add('Exiting Check_Closed_Delivery_Detail, Return False');
1383 END IF;
1384 RETURN FALSE;
1385 WHEN OTHERS THEN
1386 IF l_debug_level > 0 THEN
1387 oe_debug_pub.add('Exiting Check_Closed_Delivery_Detail, Return False and unexpected error '|| SQLERRM);
1388 END IF;
1389 RAISE;
1390 END Check_Closed_Delivery_Detail;
1391
1392 PROCEDURE Call_OM_Selector (p_item_type IN VARCHAR2,
1393 p_item_key IN VARCHAR2,
1394 p_activity_id IN NUMBER,
1395 p_mode IN VARCHAR2,
1396 p_x_result IN OUT NOCOPY VARCHAR2)
1397 IS
1398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1399 BEGIN
1400 IF l_debug_level > 0 THEN
1401 oe_debug_pub.add('Entering Call_OM_Selector');
1402 oe_debug_pub.add('Calling selector function for item type: '|| p_item_type || ' item key: ' || p_item_key || ' activity_id: ' || p_activity_id || ' mode: ' || p_mode);
1403 END IF;
1404
1405 p_x_result := NULL;
1406
1407 IF p_item_type = 'OEOH' THEN
1408 OE_Standard_Wf.OEOH_Selector (p_item_type,
1409 p_item_key,
1410 p_activity_id,
1411 p_mode, p_x_result);
1412 ELSIF p_item_type = 'OEOL' THEN
1413 OE_Standard_Wf.OEOL_Selector (p_item_type,
1414 p_item_key,
1415 p_activity_id,
1416 p_mode, p_x_result);
1417 ELSIF p_item_type = 'OEBH' THEN
1418 OE_Standard_Wf.OEBH_Selector (p_item_type,
1419 p_item_key,
1420 p_activity_id,
1421 p_mode, p_x_result);
1422 ELSIF p_item_type = 'OENH' THEN
1423 OE_Standard_Wf.OENH_Selector (p_item_type,
1424 p_item_key,
1425 p_activity_id,
1426 p_mode, p_x_result);
1427 ELSIF p_item_type = 'OEOI' THEN
1428 OE_Order_Import_Wf.OEOI_Selector (p_item_type,
1429 p_item_key,
1430 p_activity_id,
1431 p_mode, p_x_result);
1432 ELSIF p_item_type = 'OEOA' THEN
1433 OE_Order_Import_Wf.OEOA_Selector (p_item_type,
1434 p_item_key,
1435 p_activity_id,
1436 p_mode, p_x_result);
1437 ELSIF p_item_type = 'OESO' THEN
1438 OE_Order_Import_Wf.OESO_Selector (p_item_type,
1439 p_item_key,
1440 p_activity_id,
1441 p_mode, p_x_result);
1442 ELSIF p_item_type = 'OEEM' THEN
1443 OE_Elecmsgs_Pvt.OEEM_Selector (p_item_type,
1444 p_item_key,
1445 p_activity_id,
1446 p_mode, p_x_result);
1447 ELSIF p_item_type = 'OEXWFEDI' THEN
1448 OE_Update_Ack_Util.OE_Edi_Selector (p_item_type,
1449 p_item_key,
1450 p_activity_id,
1451 p_mode, p_x_result);
1452 END IF;
1453 IF l_debug_level > 0 THEN
1454 oe_debug_pub.add('Exiting Call_OM_Selector with result: ' || p_x_result);
1455 END IF;
1456
1457 END;
1458
1459 FUNCTION Activity_In_Error (p_item_type IN VARCHAR2,
1460 p_item_key IN VARCHAR2,
1461 p_activity_id IN VARCHAR2)
1462 RETURN BOOLEAN
1463 IS
1464 l_count NUMBER;
1465 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1466 l_error_status CONSTANT VARCHAR2(5) := 'ERROR';
1467 BEGIN
1468 l_count := 0;
1469
1470 BEGIN
1471 SELECT 1
1472 INTO l_count
1473 FROM WF_ITEM_ACTIVITY_STATUSES IAS
1474 WHERE IAS.ITEM_TYPE = p_item_type
1475 AND IAS.ITEM_KEY = p_item_key
1476 AND IAS.PROCESS_ACTIVITY = p_activity_id
1477 AND IAS.ACTIVITY_STATUS = l_error_status
1478 AND rownum = 1;
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 l_count := 0;
1482 END;
1483
1484 IF l_debug_level > 0 THEN
1485 oe_debug_pub.add ('Error count of ' || l_count);
1486 END IF;
1487
1488 RETURN (l_count <> 0);
1489
1490 END Activity_In_Error;
1491
1492
1493 PROCEDURE Parse_User_Key (p_item_type IN VARCHAR2,
1494 p_item_key IN VARCHAR2,
1495 p_user_key IN VARCHAR2,
1496 x_order_source_id OUT NOCOPY NUMBER,
1497 x_orig_sys_document_ref OUT NOCOPY VARCHAR2)
1498 IS
1499 l_pos NUMBER;
1500 l_pos2 NUMBER;
1501 l_pos3 NUMBER;
1502 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1503 BEGIN
1504 IF l_debug_level > 0 THEN
1505 oe_debug_pub.add('Entering Parse_User_Key');
1506 END IF;
1507 IF p_item_type IN ('OEOI', 'OESO', 'OEOA') THEN
1508 x_order_source_id := 20;
1509 l_pos := instr (p_user_key, ',');
1510 x_orig_sys_document_ref := substr(p_user_key, 1, l_pos-1);
1511
1512 ELSIF p_item_type = 'OEXWFEDI' THEN
1513 x_order_source_id := wf_engine.GetItemAttrText (p_item_type,
1514 p_item_key,
1515 'ORDER_SOURCE_ID'
1516 );
1517 l_pos := instr (p_user_key, ',');
1518 -- dbms_output.put_line(l_pos || ' x' || l_pos2|| ' y ' ||l_pos3);
1519
1520 x_orig_sys_document_ref := substr(p_user_key, 1, l_pos-1);
1521
1522 ELSIF p_item_type = 'OEEM' THEN
1523 l_pos := instr(p_user_key, ','); -- position of first comma
1524 l_pos2 := instr(p_user_key, ',',l_pos+1); -- position of second comma
1525 l_pos3 := instr(p_user_key, ',',l_pos2+1); -- position of third comma
1526 -- dbms_output.put_line(l_pos || ' x' || l_pos2|| ' y ' ||l_pos3);
1527 x_order_source_id := to_number(substr(p_user_key, 1, l_pos-1));
1528 x_orig_sys_document_ref := substr(p_user_key, l_pos2+1, l_pos3-l_pos2-1);
1529
1530 END IF;
1531
1532 IF l_debug_level > 0 THEN
1533 oe_debug_pub.add('Exiting Parse_User_Key with order_source_id: '|| x_order_source_id ||
1534 ' and orig_sys_document_ref : ' || x_orig_sys_document_ref);
1535 END IF;
1536 EXCEPTION
1537 WHEN OTHERS THEN
1538 NULL; -- don't completely bail as we can still check the WF item attrs
1539 IF l_debug_level > 0 THEN
1540 oe_debug_pub.add('Exiting Parse_User_Key with order_source_id: '|| x_order_source_id ||
1541 ' and orig_sys_document_ref : ' || x_orig_sys_document_ref || ' and unexpected error: ' || SQLERRM);
1542 END IF;
1543 END Parse_User_Key;
1544
1545 PROCEDURE Print_Open_Messages (p_item_type VARCHAR2,
1546 p_item_key VARCHAR2,
1547 p_activity_id VARCHAR2,
1548 p_order_source_id NUMBER,
1549 p_orig_sys_document_ref VARCHAR2,
1550 p_header_id NUMBER)
1551 IS
1552 l_msg_rec Msg_Rec_Type;
1553 l_open CONSTANT VARCHAR2(4) := 'OPEN';
1554 l_closed CONSTANT VARCHAR2(6) := 'CLOSED';
1555 l_entity_code VARCHAR2(30);
1556 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1557
1558 CURSOR l_msg_cursor_1 IS
1559 SELECT tl.message_text
1560 FROM oe_processing_msgs msg, oe_processing_msgs_tl tl
1561 WHERE msg.transaction_id = tl.transaction_id
1562 AND msg.header_id = to_number(p_item_key)
1563 AND nvl(msg.message_status_code, l_open) <> l_closed
1564 AND tl.language = USERENV('LANG')
1565 ORDER BY msg.transaction_id;
1566
1567
1568 CURSOR l_msg_cursor_2 IS
1569 SELECT tl.message_text
1570 FROM oe_processing_msgs msg, oe_processing_msgs_tl tl
1571 WHERE msg.transaction_id = tl.transaction_id
1572 AND msg.header_id = p_header_id
1573 AND msg.line_id = to_number (p_item_key)
1574 AND nvl(msg.message_status_code, l_open) <> l_closed
1575 AND tl.language = USERENV('LANG')
1576 ORDER BY msg.transaction_id;
1577
1578 CURSOR l_msg_cursor_3 IS
1579 SELECT tl.message_text
1580 FROM oe_processing_msgs msg, oe_processing_msgs_tl tl
1581 WHERE msg.transaction_id = tl.transaction_id
1582 AND msg.entity_id = to_number(p_item_key)
1583 AND msg.entity_code = l_entity_code
1584 AND nvl(msg.message_status_code, l_open) <> l_closed
1585 AND tl.language = USERENV('LANG')
1586 ORDER BY msg.transaction_id;
1587
1588 CURSOR l_msg_cursor_4 IS
1589 SELECT tl.message_text
1590 FROM oe_processing_msgs msg, oe_processing_msgs_tl tl
1591 WHERE msg.transaction_id = tl.transaction_id
1592 AND msg.entity_id = to_number(p_item_key)
1593 AND msg.entity_code = l_entity_code
1594 AND msg.order_source_id = p_order_source_id
1595 AND msg.original_sys_document_ref = p_orig_sys_document_ref
1596 AND nvl(msg.message_status_code, l_open) <> l_closed
1597 AND tl.language = USERENV('LANG')
1598 ORDER BY msg.transaction_id;
1599 BEGIN
1600 IF p_item_type IN ('OEOH', 'OENH') THEN
1601 OPEN l_msg_cursor_1;
1602 FETCH l_msg_cursor_1 BULK COLLECT INTO
1603 l_msg_rec.message_text LIMIT 1000;
1604 IF l_debug_level > 0 THEN
1605 oe_debug_pub.add('Fetched ' || l_msg_rec.message_text.count || ' records from msg cursor 1 for item type ' ||p_item_type);
1606 END IF;
1607 FOR i in 1..l_msg_rec.message_text.count LOOP
1608 FND_FILE.PUT_LINE(FND_FILE.LOG, ' '||l_msg_rec.message_text(i));
1609 END LOOP;
1610 CLOSE l_msg_cursor_1;
1611 ELSIF p_item_type = 'OEOL' THEN
1612 OPEN l_msg_cursor_2;
1613 FETCH l_msg_cursor_2 BULK COLLECT INTO
1614 l_msg_rec.message_text LIMIT 1000;
1615 IF l_debug_level > 0 THEN
1616 oe_debug_pub.add('Fetched ' || l_msg_rec.message_text.count || ' records from msg cursor 2 for item type ' ||p_item_type);
1617 END IF;
1618 FOR i in 1..l_msg_rec.message_text.count LOOP
1619 FND_FILE.PUT_LINE(FND_FILE.LOG, ' '||l_msg_rec.message_text(i));
1620 END LOOP;
1621 CLOSE l_msg_cursor_2;
1622 ELSIF p_item_type = 'OEBH' THEN
1623 l_entity_code := 'BLANKET_HEADER';
1624
1625 OPEN l_msg_cursor_3;
1626 FETCH l_msg_cursor_3 BULK COLLECT INTO
1627 l_msg_rec.message_text LIMIT 1000;
1628 IF l_debug_level > 0 THEN
1629 oe_debug_pub.add('Fetched ' || l_msg_rec.message_text.count || ' records from msg cursor 3 for item type ' ||p_item_type);
1630 END IF;
1631 FOR i in 1..l_msg_rec.message_text.count LOOP
1632 FND_FILE.PUT_LINE(FND_FILE.LOG, ' '||l_msg_rec.message_text(i));
1633 END LOOP;
1634 CLOSE l_msg_cursor_3;
1635 ELSIF p_item_type IN ('OEOI', 'OEOA', 'OESO', 'OEXWFEDI') THEN
1636 l_entity_code := 'ELECMSG_'||p_item_type;
1637
1638 OPEN l_msg_cursor_4;
1639 FETCH l_msg_cursor_4 BULK COLLECT INTO
1640 l_msg_rec.message_text LIMIT 1000;
1641 IF l_debug_level > 0 THEN
1642 oe_debug_pub.add('Fetched ' || l_msg_rec.message_text.count || ' records from msg cursor 4 for item type ' ||p_item_type);
1643 END IF;
1644 FOR i in 1..l_msg_rec.message_text.count LOOP
1645 FND_FILE.PUT_LINE(FND_FILE.LOG, ' '||l_msg_rec.message_text(i));
1646 END LOOP;
1647 CLOSE l_msg_cursor_4;
1648 END IF;
1649 EXCEPTION
1650 WHEN OTHERS THEN
1651 IF l_msg_cursor_1%ISOPEN THEN
1652 CLOSE l_msg_cursor_1;
1653 ELSIF l_msg_cursor_2%ISOPEN THEN
1654 CLOSE l_msg_cursor_2;
1655 ELSIF l_msg_cursor_3%ISOPEN THEN
1656 CLOSE l_msg_cursor_3;
1657 ELSIF l_msg_cursor_4%ISOPEN THEN
1658 CLOSE l_msg_cursor_4;
1659 END IF;
1660 END Print_Open_Messages;
1661
1662 FUNCTION get_lock (p_item_type IN VARCHAR2,
1663 p_item_key IN VARCHAR2)
1664 RETURN BOOLEAN IS
1665
1666 l_ord_num NUMBER;
1667 l_hdr_id NUMBER;
1668 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1669 BEGIN
1670
1671 IF l_debug_level > 0 THEN
1672 oe_debug_pub.add('Entering oe_error_wf.get_lock ');
1673 END IF;
1674
1675 IF p_item_type in ('OEOH','OENH') THEN
1676
1677 SELECT ORDER_NUMBER into l_ord_num
1678 FROM OE_ORDER_HEADERS_ALL
1679 WHERE header_id = to_number(p_item_key)
1680 FOR UPDATE NOWAIT;
1681
1682 ELSIF p_item_type = 'OEOL' THEN
1683
1684 SELECT header_id into l_hdr_id
1685 FROM OE_ORDER_LINES_ALL
1686 WHERE line_id = to_number(p_item_key)
1687 FOR UPDATE NOWAIT;
1688
1689 SELECT order_number into l_ord_num
1690 FROM OE_ORDER_HEADERS_ALL
1691 WHERE header_id = l_hdr_id
1692 FOR UPDATE NOWAIT;
1693
1694 ELSIF p_item_type = 'OEBH' THEN
1695
1696 SELECT header_id into l_hdr_id
1697 FROM OE_BLANKET_HEADERS_ALL
1698 WHERE header_id = to_number(p_item_key)
1699 FOR UPDATE NOWAIT;
1700
1701 END IF;
1702
1703 return true;
1704
1705 EXCEPTION
1706 /*
1707 WHEN TIMEOUT_ON_RESOURCE THEN
1708 IF l_debug_level > 0 THEN
1709 oe_debug_pub.add('TIMEOUT_ON_RESOURCE Exception while locking the record in oe_errors_wf.get_lock for item ' ||p_item_type||' with key '||p_item_key);
1710 oe_debug_pub.add('The SQL ERROR is '||substr(SQLERRM, 1, 512));
1711 END IF;
1712 return false;
1713 */
1714 WHEN OTHERS THEN
1715 IF l_debug_level > 0 THEN
1716 oe_debug_pub.add('Exception while locking the record in oe_errors_wf.get_lock for item ' ||p_item_type||' with key '||p_item_key);
1717 oe_debug_pub.add('The SQL ERROR is '||substr(SQLERRM, 1, 512));
1718 END IF;
1719 return false;
1720 -- raise;
1721 END get_lock;
1722
1723
1724
1725 PROCEDURE close_messages (p_item_type IN varchar2,
1726 p_item_key IN varchar2,
1727 p_activity_id IN NUMBER default null,
1728 p_header_id IN NUMBER default null,
1729 p_user_key IN varchar2 default null,
1730 x_order_source_id OUT NOCOPY NUMBER,
1731 x_orig_sys_document_ref OUT NOCOPY varchar2
1732 ) IS
1733
1734 l_order_source_id NUMBER;
1735 l_orig_sys_document_ref VARCHAR2(50);
1736
1737
1738 BEGIN
1739 IF p_item_type IN ('OEOH','OENH') THEN
1740
1741 OE_MSG_PUB.Update_Status_Code(
1742 p_header_id => to_number(p_item_key),
1743 p_process_activity => p_activity_id,
1744 p_status_code => 'CLOSED');
1745
1746 ELSIF p_item_type = 'OEBH' THEN
1747
1748 OE_MSG_PUB.Update_Status_Code(
1749 p_entity_code => 'BLANKET_HEADER',
1750 p_entity_id => to_number(p_item_key),
1751 p_process_activity => p_activity_id,
1752 p_status_code => 'CLOSED');
1753
1754 ELSIF p_item_type = 'OEOL' THEN
1755 OE_MSG_PUB.Update_Status_Code(
1756 p_header_id => p_header_id,
1757 p_line_id => to_number(p_item_key),
1758 p_process_activity => p_activity_id,
1759 p_status_code => 'CLOSED');
1760
1761 ELSIF p_item_type IN ('OEOI', 'OEOA', 'OESO', 'OEXWFEDI') THEN
1762 -- first try to parse the user key string
1763 -- in case we cannot derive the info from here,
1764 -- go to the WF item attr (more expensive)
1765 IF p_user_key IS NOT NULL THEN
1766 Parse_User_Key (p_item_type => p_item_type,
1767 p_item_key => p_item_key,
1768 p_user_key => p_user_key,
1769 x_order_source_id => l_order_source_id,
1770 x_orig_sys_document_ref => l_orig_sys_document_ref);
1771 END IF;
1772 IF l_order_source_id IS NULL or l_orig_sys_document_ref IS NULL THEN
1773 Get_EM_Key_Info (p_itemtype => p_item_type,
1774 p_itemkey => p_item_key,
1775 x_order_source_id => l_order_source_id,
1776 x_orig_sys_document_ref => l_orig_sys_document_ref);
1777 END IF;
1778 OE_MSG_PUB.Update_Status_Code(
1779 p_order_source_id => l_order_source_id,
1780 p_orig_sys_document_ref => l_orig_sys_document_ref,
1781 p_entity_code => 'ELECMSG_'||p_item_type,
1782 p_entity_id => to_number(p_item_key),
1783 p_process_activity => p_activity_id,
1784 p_status_code => 'CLOSED');
1785 ELSIF p_item_type = 'OMERROR' THEN
1786 null;
1787 END IF;
1788 x_order_source_id := l_order_source_id;
1789 x_orig_sys_document_ref := l_orig_sys_document_ref;
1790
1791 END close_messages;
1792
1793 PROCEDURE Retry_Flows (
1794 p_item_key IN VARCHAR2 DEFAULT NULL,
1795 p_item_type IN VARCHAR2,
1796 p_item_type_display_name IN VARCHAR2,
1797 p_activity_name IN VARCHAR2 DEFAULT NULL,
1798 p_activity_error_date_from IN DATE DEFAULT NULL,
1799 p_activity_error_date_to IN DATE DEFAULT NULL,
1800 p_mode IN VARCHAR2,
1801 x_return_status OUT NOCOPY VARCHAR2)
1802 IS
1803 l_error_status CONSTANT VARCHAR2(5) := 'ERROR';
1804 l_org_id CONSTANT VARCHAR2(6) := 'ORG_ID';
1805 l_retry_count NUMBER;
1806 l_commit_count NUMBER;
1807 p_x_result VARCHAR2(10);
1808 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1809 l_ignore_error_check BOOLEAN;
1810 l_header_id NUMBER;
1811 l_order_source_id NUMBER;
1812 l_orig_sys_document_ref VARCHAR2(50);
1813 l_activity_display_name VARCHAR2(80);
1814 l_error_msg VARCHAR2(512);
1815 l_last_org_id NUMBER;
1816 l_end_total_time NUMBER;
1817 l_start_total_time NUMBER;
1818 l_get_lock_failed BOOLEAN := false;
1819
1820 CURSOR l_retry_cursor_1 IS
1821 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1822 FROM WF_PROCESS_ACTIVITIES PA,
1823 WF_ITEMS I,
1824 WF_ITEM_ACTIVITY_STATUSES IAS,
1825 WF_ITEM_ATTRIBUTE_VALUES WAT,
1826 WF_ACTIVITIES WA
1827 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1828 AND IAS.ITEM_KEY = I.ITEM_KEY
1829 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1830 AND IAS.ITEM_TYPE = OE_GLOBALS.G_WFI_LIN
1831 AND I.PARENT_ITEM_KEY = p_item_key
1832 AND I.PARENT_ITEM_TYPE = OE_GLOBALS.G_WFI_HDR
1833 AND PA.PROCESS_ITEM_TYPE = OE_GLOBALS.G_WFI_LIN
1834 AND IAS.ACTIVITY_STATUS = l_error_status
1835 AND I.END_DATE IS NULL
1836 AND WAT.ITEM_TYPE = IAS.ITEM_TYPE
1837 AND WAT.ITEM_KEY = IAS.ITEM_KEY
1838 AND WAT.NAME = l_org_id
1839 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1840 AND WA.NAME = PA.ACTIVITY_NAME
1841 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1842 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1843 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1844 ORDER BY WAT.NUMBER_VALUE;
1845
1846 CURSOR l_retry_cursor_2 IS
1847 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1848 FROM WF_PROCESS_ACTIVITIES PA,
1849 WF_ITEMS I,
1850 WF_ITEM_ACTIVITY_STATUSES IAS,
1851 WF_ITEM_ATTRIBUTE_VALUES WAT,
1852 WF_ACTIVITIES WA
1853 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1854 AND IAS.ITEM_KEY = I.ITEM_KEY
1855 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1856 AND IAS.ITEM_TYPE = OE_GLOBALS.G_WFI_HDR
1857 AND IAS.ITEM_KEY = p_item_key
1858 AND PA.PROCESS_ITEM_TYPE = OE_GLOBALS.G_WFI_HDR
1859 AND IAS.ACTIVITY_STATUS = l_error_status
1860 AND I.END_DATE IS NULL
1861 AND WAT.ITEM_TYPE = IAS.ITEM_TYPE
1862 AND WAT.ITEM_KEY = IAS.ITEM_KEY
1863 AND WAT.NAME = l_org_id
1864 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1865 AND WA.NAME = PA.ACTIVITY_NAME
1866 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1867 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1868 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1869 ORDER BY WAT.NUMBER_VALUE;
1870
1871 CURSOR l_retry_cursor_3 IS
1872 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1873 FROM WF_PROCESS_ACTIVITIES PA,
1874 WF_ITEMS I,
1875 WF_ITEM_ACTIVITY_STATUSES IAS,
1876 WF_ITEM_ATTRIBUTE_VALUES WAT,
1877 WF_ACTIVITIES WA
1878 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1879 AND IAS.ITEM_KEY = I.ITEM_KEY
1880 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1881 AND IAS.ITEM_TYPE = p_item_type
1882 AND PA.PROCESS_ITEM_TYPE = p_item_type
1883 AND PA.ACTIVITY_NAME = p_activity_name
1884 AND IAS.ACTIVITY_STATUS = l_error_status
1885 AND IAS.BEGIN_DATE BETWEEN nvl(p_activity_error_date_from, IAS.BEGIN_DATE)
1886 AND nvl(p_activity_error_date_to, IAS.BEGIN_DATE)
1887 AND I.END_DATE IS NULL
1888 AND WAT.ITEM_TYPE = IAS.ITEM_TYPE
1889 AND WAT.ITEM_KEY = IAS.ITEM_KEY
1890 AND WAT.NAME = l_org_id
1891 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1892 AND WA.NAME = PA.ACTIVITY_NAME
1893 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1894 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1895 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1896 ORDER BY WAT.NUMBER_VALUE;
1897
1898 CURSOR l_retry_cursor_4 IS
1899 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1900 FROM WF_PROCESS_ACTIVITIES PA,
1901 WF_ITEMS I,
1902 WF_ITEM_ACTIVITY_STATUSES IAS,
1903 WF_ITEM_ATTRIBUTE_VALUES WAT,
1904 WF_ACTIVITIES WA
1905 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1906 AND IAS.ITEM_KEY = I.ITEM_KEY
1907 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1908 AND IAS.ITEM_TYPE = p_item_type
1909 AND PA.PROCESS_ITEM_TYPE = p_item_type
1910 AND IAS.ACTIVITY_STATUS = l_error_status
1911 AND IAS.BEGIN_DATE BETWEEN nvl(p_activity_error_date_from, IAS.BEGIN_DATE)
1912 AND nvl(p_activity_error_date_to, IAS.BEGIN_DATE)
1913 AND I.END_DATE IS NULL
1914 AND WAT.ITEM_TYPE = IAS.ITEM_TYPE
1915 AND WAT.ITEM_KEY = IAS.ITEM_KEY
1916 AND WAT.NAME = l_org_id
1917 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1918 AND WA.NAME = PA.ACTIVITY_NAME
1919 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1920 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1921 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1922 ORDER BY WAT.NUMBER_VALUE;
1923
1924 CURSOR l_retry_cursor_5 IS
1925 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1926 FROM WF_PROCESS_ACTIVITIES PA,
1927 WF_ITEMS I,
1928 WF_ITEM_ACTIVITY_STATUSES IAS,
1929 WF_ITEM_ATTRIBUTE_VALUES WAT,
1930 WF_ACTIVITIES WA
1931 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1932 AND IAS.ITEM_KEY = I.ITEM_KEY
1933 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1934 AND IAS.ITEM_TYPE = p_item_type
1935 AND PA.PROCESS_ITEM_TYPE = p_item_type
1936 AND PA.ACTIVITY_NAME = p_activity_name
1937 AND IAS.ACTIVITY_STATUS = l_error_status
1938 AND IAS.BEGIN_DATE BETWEEN nvl(p_activity_error_date_from, IAS.BEGIN_DATE)
1939 AND nvl(p_activity_error_date_to, IAS.BEGIN_DATE)
1940 AND I.END_DATE IS NULL
1941 AND WAT.ITEM_TYPE = I.PARENT_ITEM_TYPE
1942 AND WAT.ITEM_KEY = I.PARENT_ITEM_KEY
1943 AND WAT.NAME = l_org_id
1944 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1945 AND WA.NAME = PA.ACTIVITY_NAME
1946 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1947 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1948 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1949 ORDER BY WAT.NUMBER_VALUE;
1950
1951 CURSOR l_retry_cursor_6 IS
1952 SELECT IAS.ITEM_KEY, PA.INSTANCE_LABEL, PA.ACTIVITY_NAME, PA.PROCESS_NAME, IAS.PROCESS_ACTIVITY, I.USER_KEY, I.PARENT_ITEM_TYPE, I.PARENT_ITEM_KEY, PA.ACTIVITY_ITEM_TYPE, WAT.NUMBER_VALUE
1953 FROM WF_PROCESS_ACTIVITIES PA,
1954 WF_ITEMS I,
1955 WF_ITEM_ACTIVITY_STATUSES IAS,
1956 WF_ITEM_ATTRIBUTE_VALUES WAT,
1957 WF_ACTIVITIES WA
1958 WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
1959 AND IAS.ITEM_KEY = I.ITEM_KEY
1960 AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
1961 AND IAS.ITEM_TYPE = p_item_type
1962 AND PA.PROCESS_ITEM_TYPE = p_item_type
1963 AND IAS.ACTIVITY_STATUS = l_error_status
1964 AND IAS.BEGIN_DATE BETWEEN nvl(p_activity_error_date_from, IAS.BEGIN_DATE)
1965 AND nvl(p_activity_error_date_to, IAS.BEGIN_DATE)
1966 AND I.END_DATE IS NULL
1967 AND WAT.ITEM_TYPE = I.PARENT_ITEM_TYPE
1968 AND WAT.ITEM_KEY = I.PARENT_ITEM_KEY
1969 AND WAT.NAME = l_org_id
1970 AND WA.ITEM_TYPE = PA.ACTIVITY_ITEM_TYPE
1971 AND WA.NAME = PA.ACTIVITY_NAME
1972 AND WA.TYPE NOT IN ('PROCESS','FOLDER')
1973 AND I.BEGIN_DATE >= WA.BEGIN_DATE
1974 AND I.BEGIN_DATE < NVL(WA.END_DATE, I.BEGIN_DATE+1) --Modified for bug 6443885
1975 ORDER BY WAT.NUMBER_VALUE;
1976
1977 l_retry_rec Retry_Rec_Type;
1978
1979 BEGIN
1980 IF l_debug_level > 0 THEN
1981 oe_debug_pub.add('Entering Retry_Flows');
1982 END IF;
1983 G_BATCH_RETRY_FLAG := 'Y';
1984 l_commit_count := 0;
1985
1986 IF p_item_key IS NOT NULL THEN
1987 IF p_item_type = 'OEOL' THEN
1988 OPEN l_retry_cursor_1;
1989 ELSIF p_item_type = 'OEOH' THEN
1990 OPEN l_retry_cursor_2;
1991 END IF;
1992 ELSIF p_item_type = 'OMERROR' THEN
1993 IF p_activity_name IS NOT NULL THEN
1994 OPEN l_retry_cursor_5;
1995 ELSE
1996 OPEN l_retry_cursor_6;
1997 END IF;
1998 ELSIF p_activity_name IS NOT NULL THEN
1999 OPEN l_retry_cursor_3;
2000 ELSE
2001 OPEN l_retry_cursor_4;
2002 END IF;
2003
2004 ----------------------------------------------------------------------------
2005 -- Fetch and process errored work items
2006 ----------------------------------------------------------------------------
2007
2008 LOOP
2009 IF l_retry_cursor_1%ISOPEN THEN
2010 IF l_debug_level > 0 THEN
2011 oe_debug_pub.add ('fetching from cursor 1');
2012 END IF;
2013 FETCH l_retry_cursor_1 BULK COLLECT INTO
2014 l_retry_rec.item_key,
2015 l_retry_rec.activity_label,
2016 l_retry_rec.activity_name,
2017 l_retry_rec.process_name,
2018 l_retry_rec.activity_id,
2019 l_retry_rec.user_key,
2020 l_retry_rec.parent_item_type,
2021 l_retry_rec.parent_item_key,
2022 l_retry_rec.activity_item_type,
2023 l_retry_rec.org_id
2024 LIMIT 1000;
2025
2026 ELSIF l_retry_cursor_2%ISOPEN THEN
2027 IF l_debug_level > 0 THEN
2028 oe_debug_pub.add ('fetching from cursor 2');
2029 END IF;
2030 FETCH l_retry_cursor_2 BULK COLLECT INTO
2031 l_retry_rec.item_key,
2032 l_retry_rec.activity_label,
2033 l_retry_rec.activity_name,
2034 l_retry_rec.process_name,
2035 l_retry_rec.activity_id,
2036 l_retry_rec.user_key,
2037 l_retry_rec.parent_item_type,
2038 l_retry_rec.parent_item_key,
2039 l_retry_rec.activity_item_type,
2040 l_retry_rec.org_id
2041 LIMIT 1000;
2042
2043 ELSIF l_retry_cursor_3%ISOPEN THEN
2044 IF l_debug_level > 0 THEN
2045 oe_debug_pub.add ('fetching from cursor 3');
2046 END IF;
2047 FETCH l_retry_cursor_3 BULK COLLECT INTO
2048 l_retry_rec.item_key,
2049 l_retry_rec.activity_label,
2050 l_retry_rec.activity_name,
2051 l_retry_rec.process_name,
2052 l_retry_rec.activity_id,
2053 l_retry_rec.user_key,
2054 l_retry_rec.parent_item_type,
2055 l_retry_rec.parent_item_key,
2056 l_retry_rec.activity_item_type,
2057 l_retry_rec.org_id
2058 LIMIT 1000;
2059
2060 ELSIF l_retry_cursor_4%ISOPEN THEN
2061 IF l_debug_level > 0 THEN
2062 oe_debug_pub.add ('fetching from cursor 4');
2063 END IF;
2064 FETCH l_retry_cursor_4 BULK COLLECT INTO
2065 l_retry_rec.item_key,
2066 l_retry_rec.activity_label,
2067 l_retry_rec.activity_name,
2068 l_retry_rec.process_name,
2069 l_retry_rec.activity_id,
2070 l_retry_rec.user_key,
2071 l_retry_rec.parent_item_type,
2072 l_retry_rec.parent_item_key,
2073 l_retry_rec.activity_item_type,
2074 l_retry_rec.org_id
2075 LIMIT 1000;
2076
2077 ELSIF l_retry_cursor_5%ISOPEN THEN
2078 IF l_debug_level > 0 THEN
2079 oe_debug_pub.add ('fetching from cursor 5');
2080 END IF;
2081 FETCH l_retry_cursor_5 BULK COLLECT INTO
2082 l_retry_rec.item_key,
2083 l_retry_rec.activity_label,
2084 l_retry_rec.activity_name,
2085 l_retry_rec.process_name,
2086 l_retry_rec.activity_id,
2087 l_retry_rec.user_key,
2088 l_retry_rec.parent_item_type,
2089 l_retry_rec.parent_item_key,
2090 l_retry_rec.activity_item_type,
2091 l_retry_rec.org_id
2092 LIMIT 1000;
2093
2094 ELSIF l_retry_cursor_6%ISOPEN THEN
2095 IF l_debug_level > 0 THEN
2096 oe_debug_pub.add ('fetching from cursor 6');
2097 END IF;
2098 FETCH l_retry_cursor_6 BULK COLLECT INTO
2099 l_retry_rec.item_key,
2100 l_retry_rec.activity_label,
2101 l_retry_rec.activity_name,
2102 l_retry_rec.process_name,
2103 l_retry_rec.activity_id,
2104 l_retry_rec.user_key,
2105 l_retry_rec.parent_item_type,
2106 l_retry_rec.parent_item_key,
2107 l_retry_rec.activity_item_type,
2108 l_retry_rec.org_id
2109 LIMIT 1000;
2110
2111 END IF;
2112
2113 l_retry_count := l_retry_rec.item_key.count;
2114 IF l_debug_level > 0 THEN
2115 oe_debug_pub.add('Number of records in this fetch: '|| l_retry_count);
2116 END IF;
2117
2118 FOR i IN 1..l_retry_count LOOP
2119 BEGIN
2120 SAVEPOINT RETRY_FLOW_SAVEPOINT;
2121 IF l_debug_level > 0 THEN
2122 oe_debug_pub.add(' ');
2123 oe_debug_pub.add('Set savepoint for ' ||l_retry_rec.item_key(i));
2124 END IF;
2125
2126 ----------------------------------------------------------------------------
2127 -- Print User Key Info
2128 ----------------------------------------------------------------------------
2129 Put (p_item_type || l_retry_rec.activity_name(i),l_retry_rec.activity_item_type(i), l_retry_rec.activity_name(i),p_item_type,1, 0, l_activity_display_name);
2130
2131 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
2132 FND_FILE.PUT_LINE(FND_FILE.LOG, l_retry_rec.user_key(i));
2133 FND_MESSAGE.SET_NAME ('ONT', 'ONT_WF_ITEM_INFO');
2134 FND_MESSAGE.SET_TOKEN ('ITEM_TYPE', p_item_type_display_name);
2135 FND_MESSAGE.SET_TOKEN ('ITEM_KEY', l_retry_rec.item_key(i));
2136 FND_MESSAGE.SET_TOKEN ('ACTIVITY_NAME', l_activity_display_name);
2137 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
2138
2139 ----------------------------------------------------------------------------
2140 -- Initialize
2141 ----------------------------------------------------------------------------
2142 l_ignore_error_check := FALSE;
2143 l_header_id := NULL;
2144 l_order_source_id := NULL;
2145 l_orig_sys_document_ref := NULL;
2146
2147 IF p_item_type = 'OEOL' THEN
2148 -- fetch header id to make sure index is used
2149 SELECT header_id
2150 INTO l_header_id
2151 FROM OE_Order_Lines_All
2152 WHERE line_id = to_number(l_retry_rec.item_key(i));
2153
2154 IF l_debug_level > 0 THEN
2155 oe_debug_pub.add('Fetched header id ' || l_header_id);
2156 END IF;
2157 END IF;
2158
2159 IF p_mode = 'EXECUTE' THEN
2160 ----------------------------------------------------------------------------
2161 -- Call Selector function
2162 ----------------------------------------------------------------------------
2163
2164 p_x_result := NULL;
2165
2166 -- only call the selector function if org_id changes
2167 IF nvl(l_retry_rec.org_id(i),-99) <> nvl(l_last_org_id, -99) OR l_retry_rec.org_id(i) IS NULL THEN
2168 IF p_item_type = 'OMERROR' THEN
2169 Call_OM_Selector(p_item_type => l_retry_rec.parent_item_type(i),
2170 p_item_key => l_retry_rec.parent_item_key(i),
2171 p_activity_id => NULL, -- this should be ok as we do not have activity
2172 -- specific logic in the selector functions
2173 p_mode => 'TEST_CTX',
2174 p_x_result => p_x_result);
2175 IF p_x_result = 'FALSE' THEN
2176 -- call the parent selector function
2177 Call_OM_Selector(p_item_type => l_retry_rec.parent_item_type(i),
2178 p_item_key => l_retry_rec.parent_item_key(i),
2179 p_activity_id => NULL, -- this should be ok as we do not have activity
2180 -- specific logic in the selector functions
2181 p_mode => 'SET_CTX',
2182 p_x_result => p_x_result);
2183 END IF;
2184
2185 ELSE
2186
2187 Call_OM_Selector(p_item_type => p_item_type,
2188 p_item_key => l_retry_rec.item_key(i),
2189 p_activity_id => NULL, -- this should be ok as we do not have activity
2190 -- specific logic in the selector functions
2191 p_mode => 'TEST_CTX',
2192 p_x_result => p_x_result);
2193
2194 IF p_x_result = 'FALSE' THEN
2195 Call_OM_Selector(p_item_type => p_item_type,
2196 p_item_key => l_retry_rec.item_key(i),
2197 p_activity_id => NULL, -- this should be ok as we do not have activity
2198 -- specific logic in the selector functions
2199 p_mode => 'SET_CTX',
2200 p_x_result => p_x_result);
2201 END IF;
2202 END IF;
2203 l_last_org_id := l_retry_rec.org_id(i);
2204 IF l_debug_level > 0 THEN
2205 oe_debug_pub.add('Reset last org id to: ' || l_retry_rec.org_id(i));
2206 END IF;
2207 ELSE
2208 IF l_debug_level > 0 THEN
2209 oe_debug_pub.add('Org context unchanged, not calling selector function for org id: ' || l_retry_rec.org_id(i));
2210 END IF;
2211 END IF;
2212
2213 IF Activity_In_Error ( p_item_type => p_item_type,
2214 p_item_key => l_retry_rec.item_key(i),
2215 p_activity_id => l_retry_rec.activity_id(i)) THEN
2216 IF l_debug_level > 0 THEN
2217 oe_debug_pub.add('Activity still in error');
2218 END IF;
2219 IF NOT (p_item_type = 'OEOL' AND l_retry_rec.activity_name(i) = 'SHIP_LINE'
2220 AND Check_Closed_Delivery_Detail (l_retry_rec.item_key(i), l_retry_rec.activity_id(i))) THEN
2221 IF get_lock(p_item_type,l_retry_rec.item_key(i)) THEN
2222 l_get_lock_failed := false;
2223 IF l_debug_level > 0 THEN
2224 SELECT hsecs INTO l_start_total_time from v$timer;
2225 oe_debug_pub.add('Calling Handleerror with item key '||l_retry_rec.item_key(i) ||
2226 ' and activity ' || l_retry_rec.process_name(i)||':'||l_retry_rec.activity_label(i));
2227 END IF;
2228 ----------------------------------------------------------------------------
2229 -- Close Open Messages
2230 ----------------------------------------------------------------------------
2231
2232 close_messages (p_item_type => p_item_type,
2233 p_item_key => l_retry_rec.item_key(i),
2234 p_activity_id => l_retry_rec.activity_id(i),
2235 p_header_id => l_header_id,
2236 p_user_key => l_retry_rec.user_key(i),
2237 x_order_source_id => l_order_source_id,
2238 x_orig_sys_document_ref => l_orig_sys_document_ref);
2239
2240 ----------------------------------------------------------------------------
2241 -- Purge Error Flows
2242 ----------------------------------------------------------------------------
2243
2244 Purge_Error_Flow (p_item_type, l_retry_rec.item_key(i));
2245
2246 ----------------------------------------------------------------------------
2247 -- Ready to retry the activity
2248 ----------------------------------------------------------------------------
2249
2250 WF_ENGINE.HandleError(p_item_type,
2251 l_retry_rec.item_key(i),
2252 l_retry_rec.process_name(i)||':'||l_retry_rec.activity_label(i),
2253 'RETRY',
2254 NULL);
2255 ELSE
2256 l_get_lock_failed := true;
2257 END IF; --IF get_lock(p_item_type,l_retry_rec.item_key(i))
2258
2259 END IF;
2260 l_commit_count := l_commit_count + 1;
2261 IF l_debug_level > 0 THEN
2262 SELECT hsecs INTO l_end_total_time from v$timer;
2263 oe_debug_pub.add('Total time taken to retry above item is (sec) '
2264 ||((l_end_total_time-l_start_total_time)/100));
2265 oe_debug_pub.add('Commit count '|| l_commit_count);
2266 END IF;
2267 IF l_commit_count > 500 THEN
2268 IF l_debug_level > 0 THEN
2269 oe_debug_pub.add('Committed '|| l_commit_count || ' records');
2270 END IF;
2271 COMMIT;
2272 l_commit_count := 0;
2273 END IF;
2274 ELSE
2275 IF l_debug_level > 0 THEN
2276 oe_debug_pub.add('Activity no longer in error, no retry');
2277 END IF;
2278 l_ignore_error_check := TRUE; -- this enables us to avoid an extra SQL
2279 END IF;
2280 END IF; -- end EXECUTE mode
2281
2282 IF (NOT l_ignore_error_check) AND Activity_In_Error ( p_item_type => p_item_type,
2283 p_item_key => l_retry_rec.item_key(i),
2284 p_activity_id => l_retry_rec.activity_id(i)) THEN
2285 IF l_debug_level > 0 THEN
2286 oe_debug_pub.add('Activity still in error, log as failure');
2287 END IF;
2288 Put (p_item_type || l_retry_rec.activity_name(i),l_retry_rec.activity_item_type(i), l_retry_rec.activity_name(i),p_item_type,0, 1, l_activity_display_name);
2289 IF p_mode = 'EXECUTE' THEN
2290 IF l_get_lock_failed THEN
2291 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The above activity is not retried because header and line records can not be locked for update. Please try later');
2292 ELSE
2293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Retry of ' || l_activity_display_name || ' failed');
2294 END IF;--IF l_get_lock_failed
2295 END IF;
2296
2297 Print_Open_Messages ( p_item_type => p_item_type,
2298 p_item_key => l_retry_rec.item_key(i),
2299 p_activity_id => l_retry_rec.activity_id(i),
2300 p_header_id => l_header_id,
2301 p_order_source_id => l_order_source_id,
2302 p_orig_sys_document_ref => l_orig_sys_document_ref);
2303 ELSE
2304 IF l_debug_level > 0 THEN
2305 oe_debug_pub.add('Activity no longer in error, log as success');
2306 END IF;
2307 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Retry of ' || l_activity_display_name || ' succeeded');
2308
2309 END IF;
2310
2311 EXCEPTION
2312 WHEN OTHERS THEN
2313 ROLLBACK TO RETRY_FLOW_SAVEPOINT;
2314 l_error_msg := substr(SQLERRM, 1, 512);
2315 IF l_debug_level > 0 THEN
2316 oe_debug_pub.add ('Error during retry, log as failure and continue with next record ' || l_error_msg);
2317 END IF;
2318 Put (p_item_type || l_retry_rec.activity_name(i),l_retry_rec.activity_item_type(i), l_retry_rec.activity_name(i),p_item_type,0, 1, l_activity_display_name);
2319 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Retry of ' || l_activity_display_name || ' failed with unexpected error ' || l_error_msg);
2320 END;
2321 END LOOP;
2322
2323 IF l_retry_cursor_1%ISOPEN THEN
2324 EXIT WHEN l_retry_cursor_1%NOTFOUND;
2325 ELSIF l_retry_cursor_2%ISOPEN THEN
2326 EXIT WHEN l_retry_cursor_2%NOTFOUND;
2327 ELSIF l_retry_cursor_3%ISOPEN THEN
2328 EXIT WHEN l_retry_cursor_3%NOTFOUND;
2329 ELSIF l_retry_cursor_4%ISOPEN THEN
2330 EXIT WHEN l_retry_cursor_4%NOTFOUND;
2331 ELSIF l_retry_cursor_5%ISOPEN THEN
2332 EXIT WHEN l_retry_cursor_5%NOTFOUND;
2333 ELSIF l_retry_cursor_6%ISOPEN THEN
2334 EXIT WHEN l_retry_cursor_6%NOTFOUND;
2335 END IF;
2336
2337 END LOOP;
2338
2339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2340 IF l_retry_cursor_1%ISOPEN THEN
2341 CLOSE l_retry_cursor_1;
2342 ELSIF l_retry_cursor_2%ISOPEN THEN
2343 CLOSE l_retry_cursor_2;
2344 ELSIF l_retry_cursor_3%ISOPEN THEN
2345 CLOSE l_retry_cursor_3;
2346 ELSIF l_retry_cursor_4%ISOPEN THEN
2347 CLOSE l_retry_cursor_4;
2348 ELSIF l_retry_cursor_5%ISOPEN THEN
2349 CLOSE l_retry_cursor_5;
2350 ELSIF l_retry_cursor_6%ISOPEN THEN
2351 CLOSE l_retry_cursor_6;
2352 END IF;
2353
2354 IF l_debug_level > 0 THEN
2355 oe_debug_pub.add('Exiting Retry_Flows');
2356 END IF;
2357 EXCEPTION
2358 WHEN OTHERS THEN
2359 IF l_debug_level > 0 THEN
2360 oe_debug_pub.add('Entering Retry_Flows with unexpected error '|| SQLERRM);
2361 END IF;
2362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2363 IF l_retry_cursor_1%ISOPEN THEN
2364 CLOSE l_retry_cursor_1;
2365 ELSIF l_retry_cursor_2%ISOPEN THEN
2366 CLOSE l_retry_cursor_2;
2367 ELSIF l_retry_cursor_3%ISOPEN THEN
2368 CLOSE l_retry_cursor_3;
2369 ELSIF l_retry_cursor_4%ISOPEN THEN
2370 CLOSE l_retry_cursor_4;
2371 ELSIF l_retry_cursor_5%ISOPEN THEN
2372 CLOSE l_retry_cursor_5;
2373 ELSIF l_retry_cursor_6%ISOPEN THEN
2374 CLOSE l_retry_cursor_6;
2375 END IF;
2376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2377 END Retry_Flows;
2378
2379 PROCEDURE Print_Results (p_mode IN VARCHAR2, p_item_type_display_name IN VARCHAR2, p_item_type_display_name2 IN VARCHAR2)
2380 IS
2381 l_count_tbl Count_Tbl_Type;
2382 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2383 l_total NUMBER;
2384 l_item_type_display_name VARCHAR2(80);
2385 i NUMBER;
2386 BEGIN
2387 IF l_debug_level > 0 THEN
2388 oe_debug_pub.add('Entering Print_Results');
2389 END IF;
2390 l_count_tbl := Count_Tbl;
2391 fnd_file.put_line(FND_FILE.OUTPUT, '');
2392 fnd_file.put_line(FND_FILE.OUTPUT, 'SUMMARY');
2393 fnd_file.put_line(FND_FILE.OUTPUT, '');
2394
2395 l_total := 0;
2396
2397 IF p_mode = 'EXECUTE' THEN
2398 IF l_count_tbl.count > 0 THEN
2399 fnd_file.put_line (FND_FILE.OUTPUT, 'Activity Name Item Type Count');
2400 END IF;
2401
2402 i := l_count_tbl.FIRST;
2403 WHILE i IS NOT NULL LOOP
2404 IF l_count_tbl(i).process_item_type = 'OEOL' THEN
2405 l_item_type_display_name := p_item_type_display_name2;
2406 ELSE
2407 l_item_type_display_name := p_item_type_display_name;
2408 END IF;
2409
2410 fnd_file.put_line(FND_FILE.OUTPUT, rpad(l_count_tbl(i).activity_display_name,48, ' ') || ' ' || rpad(l_item_type_display_name,22, ' ') || ' ' || l_count_tbl(i).initial_count);
2411 l_total := l_total + l_count_tbl(i).initial_count;
2412 i:= l_count_tbl.NEXT(i);
2413 END LOOP;
2414 fnd_file.put_line(FND_FILE.OUTPUT, '');
2415 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Activities in Error prior to request: ' || l_total);
2416 fnd_file.put_line(FND_FILE.OUTPUT, '');
2417 END IF;
2418
2419 l_total := 0;
2420
2421 IF l_count_tbl.count > 0 THEN
2422 fnd_file.put_line (FND_FILE.OUTPUT, 'Activity Name Item Type Count');
2423 END IF;
2424 i := l_count_tbl.FIRST;
2425 WHILE i IS NOT NULL LOOP
2426 IF l_count_tbl(i).process_item_type = 'OEOL' THEN
2427 l_item_type_display_name := p_item_type_display_name2;
2428 ELSE
2429 l_item_type_display_name := p_item_type_display_name;
2430 END IF;
2431
2432 fnd_file.put_line(FND_FILE.OUTPUT, rpad(l_count_tbl(i).activity_display_name,48, ' ') || ' ' || rpad(l_item_type_display_name,22, ' ') || ' ' || l_count_tbl(i).final_count);
2433 l_total := l_total + l_count_tbl(i).final_count;
2434 i:= l_count_tbl.NEXT(i);
2435 END LOOP;
2436
2437 fnd_file.put_line(FND_FILE.OUTPUT, '');
2438 fnd_file.put_line(FND_FILE.OUTPUT, 'Number of Activities in Error after completion of request: ' || l_total);
2439
2440 IF l_debug_level > 0 THEN
2441 oe_debug_pub.add('Exiting Print_Results');
2442 END IF;
2443
2444 END Print_Results;
2445
2446 PROCEDURE EM_Batch_Retry_Conc_Pgm (
2447 errbuf OUT NOCOPY VARCHAR,
2448 retcode OUT NOCOPY NUMBER,
2449 p_item_key IN VARCHAR2,
2450 p_dummy1 IN VARCHAR2, -- this param is not used
2451 p_item_type IN VARCHAR2,
2452 p_activity_name IN VARCHAR2,
2453 p_activity_error_date_from IN VARCHAR2,
2454 p_activity_error_date_to IN VARCHAR2,
2455 p_mode IN VARCHAR2)
2456 IS
2457 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2458 l_return_status VARCHAR2(1);
2459 l_activity_error_date_from DATE;
2460 l_activity_error_date_to DATE;
2461 l_item_type_display_name VARCHAR2(80);
2462 l_item_type_display_name2 VARCHAR2(80);
2463 l_user_mode VARCHAR2(80);
2464 l_order_num NUMBER;
2465 l_act_display_name VARCHAR2(80);
2466
2467 BEGIN
2468 IF l_debug_level > 0 THEN
2469 oe_debug_pub.add('Entering EM_Batch_Retry_Conc_Pgm');
2470 END IF;
2471 retcode := 0;
2472 l_activity_error_date_from := fnd_date.canonical_to_date(p_activity_error_date_from);
2473 l_activity_error_date_to := fnd_date.canonical_to_date(p_activity_error_date_to) + 1 - 1/(24*60*60);
2474 SELECT display_name
2475 INTO l_item_type_display_name
2476 FROM wf_item_types_vl
2477 WHERE name = p_item_type;
2478
2479 if p_item_key is not null then
2480
2481 SELECT order_number into l_order_num
2482 from oe_order_headers_all
2483 where header_id = p_item_key;
2484
2485 end if;
2486
2487 SELECT MEANING into l_user_mode
2488 FROM OE_LOOKUPS
2489 WHERE LOOKUP_CODE= p_mode
2490 AND LOOKUP_TYPE='ONT_RETRY_MODE'
2491 AND ENABLED_FLAG='Y'
2492 AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE,SYSDATE) AND NVL(END_DATE_ACTIVE,SYSDATE);
2493
2494 IF p_activity_name is not null then
2495 SELECT DISPLAY_NAME into l_act_display_name FROM wf_activities_vl
2496 WHERE ROW_ID IN (SELECT MAX(ROWID) FROM WF_ACTIVITIES WA
2497 WHERE WA.TYPE NOT IN ('PROCESS','FOLDER')
2498 AND EXISTS (SELECT ACTIVITY_NAME FROM WF_PROCESS_ACTIVITIES WPA
2499 WHERE WA.NAME = WPA.ACTIVITY_NAME
2500 AND WPA.PROCESS_ITEM_TYPE = P_ITEM_TYPE
2501 AND WA.ITEM_TYPE=WPA.ACTIVITY_ITEM_TYPE)
2502 AND WA.VERSION = (SELECT MAX(WA2.VERSION) FROM WF_ACTIVITIES WA2
2503 WHERE WA2.ITEM_TYPE = WA.ITEM_TYPE
2504 AND WA2.NAME=WA.NAME)
2505 GROUP BY WA.NAME)
2506 AND NAME = p_activity_name;
2507 end if;
2508
2509
2510 -----------------------------------------------------------
2511 -- Log Output file
2512 -----------------------------------------------------------
2513
2514 fnd_file.put_line(FND_FILE.OUTPUT, 'Retry Activities in Error Concurrent Program');
2515 fnd_file.put_line(FND_FILE.OUTPUT, '');
2516 fnd_file.put_line(FND_FILE.OUTPUT, 'PARAMETERS');
2517 fnd_file.put_line(FND_FILE.OUTPUT, '');
2518 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Number: '||l_order_num);
2519 fnd_file.put_line(FND_FILE.OUTPUT, 'Item Type: '||l_item_type_display_name);
2520 fnd_file.put_line(FND_FILE.OUTPUT, 'Activity in Error: '|| l_act_display_name);
2521 fnd_file.put_line(FND_FILE.OUTPUT, 'Activity Error Date From: '|| l_activity_error_date_from);
2522 fnd_file.put_line(FND_FILE.OUTPUT, 'Activity Error Date To: '||l_activity_error_date_to);
2523 fnd_file.put_line(FND_FILE.OUTPUT, 'Mode: '||l_user_mode);
2524 fnd_file.put_line(FND_FILE.OUTPUT, '');
2525
2526
2527 -----------------------------------------------------------
2528 -- Validate Parameters
2529 -----------------------------------------------------------
2530 IF p_mode IS NULL OR p_item_type IS NULL THEN
2531 retcode := 0;
2532 errbuf := 'Required parameters Item Type and Mode cannot be null';
2533 fnd_file.put_line(FND_FILE.OUTPUT, '');
2534 fnd_file.put_line(FND_FILE.OUTPUT, errbuf);
2535 fnd_file.put_line(FND_FILE.OUTPUT, 'Program exited with code : '||retcode);
2536 IF l_debug_level > 0 THEN
2537 oe_debug_pub.add( 'Exiting with retcode '||retcode || ' and errbuf ' || errbuf ) ;
2538 END IF;
2539 RETURN;
2540 END IF;
2541
2542 -----------------------------------------------------------
2543 -- Initialize
2544 -----------------------------------------------------------
2545
2546 IF p_item_key IS NOT NULL OR p_item_type = 'OEOL' THEN
2547 SELECT display_name
2548 INTO l_item_type_display_name2
2549 FROM wf_item_types_vl
2550 WHERE name = OE_GLOBALS.G_WFI_LIN;
2551 END IF;
2552
2553 IF l_debug_level > 0 THEN
2554 oe_debug_pub.add('Item Type Display Name: ' || l_item_type_display_name);
2555 oe_debug_pub.add('Item Type Display Name2: ' || l_item_type_display_name2);
2556 END IF;
2557
2558 -----------------------------------------------------------
2559 -- Retry
2560 -----------------------------------------------------------
2561 IF p_item_key IS NOT NULL THEN
2562
2563 Retry_Flows (p_item_key => p_item_key,
2564 p_item_type => 'OEOL',
2565 p_item_type_display_name => l_item_type_display_name2,
2566 p_mode => p_mode,
2567 x_return_status => l_return_status);
2568
2569 Retry_Flows (p_item_key => p_item_key,
2570 p_item_type => 'OEOH',
2571 p_item_type_display_name => l_item_type_display_name,
2572 p_mode => p_mode,
2573 x_return_status => l_return_status);
2574
2575 ELSE
2576
2577 Retry_Flows (p_item_type => p_item_type,
2578 p_activity_name => p_activity_name,
2579 p_activity_error_date_from => l_activity_error_date_from,
2580 p_activity_error_date_to => l_activity_error_date_to,
2581 p_item_type_display_name => l_item_type_display_name,
2582 p_mode => p_mode,
2583 x_return_status => l_return_status);
2584
2585 END IF;
2586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2587 null; -- need to handle this case
2588 END IF;
2589
2590 -----------------------------------------------------------
2591 -- Print Results
2592 -----------------------------------------------------------
2593 Print_Results (p_mode => p_mode,
2594 p_item_type_display_name => l_item_type_display_name,
2595 p_item_type_display_name2 => l_item_type_display_name2);
2596
2597 G_BATCH_RETRY_FLAG := 'N';
2598 retcode := 0;
2599 IF l_debug_level > 0 THEN
2600 oe_debug_pub.add('Program exited normally');
2601 END IF;
2602 fnd_file.put_line(FND_FILE.OUTPUT, '');
2603 fnd_file.put_line(FND_FILE.OUTPUT, 'Program exited with code : '||retcode);
2604 IF l_debug_level > 0 THEN
2605 oe_debug_pub.add('Exiting EM_Batch_Retry_Conc_Pgm');
2606 END IF;
2607 EXCEPTION
2608 WHEN OTHERS THEN
2609 retcode := 2;
2610 errbuf := SQLERRM;
2611 G_BATCH_RETRY_FLAG := 'N';
2612 IF l_debug_level > 0 THEN
2613 oe_debug_pub.add( 'SQLERRM: '||SQLERRM||' SQLCODE:'||SQLCODE ) ;
2614 END IF;
2615 fnd_file.put_line(FND_FILE.OUTPUT, '');
2616 fnd_file.put_line(FND_FILE.OUTPUT, 'Program exited with code : '||retcode);
2617 fnd_file.put_line(FND_FILE.OUTPUT, 'SQLERRM: '||SQLERRM||' SQLCODE:'||SQLCODE );
2618 IF OE_MSG_PUB.Check_Msg_level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2619 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, 'EM_Batch_Retry_Conc_Pgm');
2620 End if;
2621
2622 END EM_Batch_Retry_Conc_Pgm;
2623
2624 end OE_ERROR_WF;