1 PACKAGE BODY CTO_WIP_WORKFLOW_API_PK as
2 /* $Header: CTOWIPAB.pls 120.1.12000000.2 2007/09/14 10:56:38 abhissri ship $ */
3 /*============================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +=============================================================================+
8 | |
9 | FILE NAME : CTOWIPAB.pls |
10 | |
11 | DESCRIPTION: |
12 | APIs are written for WIP and Flow support for OE-99 from |
13 | the CTO group. |
14 | first_reservation_created - inform the order line workflow |
15 | thaT the first reservation has been created for this sales |
16 | order line. |
17 | last_reservation_deleted - inform the order line workflow that|
18 | the last reservation has been deleted for this sales order |
19 | line. |
20 | workflow_build_status - to determine if a particular |
21 | sales order line is at the released phase of the workflow. |
22 | |
23 | |
24 | HISTORY : |
25 | July 22, 99 James Chiu Initial version |
26 | 12/18/2000 Renga Kannan |
27 | Added one utility procedure CTO_DEBUG which |
28 | will write the CTO debug messages |
29 | |
30 | 01/11/2001 Renga Kannan |
31 | The exception handling in CTO_DEBUG is added |
32 | so that the cto_debug will not fail in the case |
33 | of invalid directry specified. This fix is part |
34 | of bug # 1577006 |
35 | 08/16/2001 Kiran Konada, fix for bug#1874380 |
36 | to support ATO item under a PTO |
37 | item_type_code for an ato item under PTO |
38 | is 'OPTION' and top_model_line_id will NOT be |
39 | null, UNLIKE an ato item order, where |
40 | item_type_code = 'Standard' and |
41 | top_model_lined_id is null |
42 | This fix has actually been provided in |
43 | branched code 115.15.115.3 |
44 | |
45 | 06/01/2005 Renga Kannan
46 | Added NoCopy Hint |
47 =============================================================================*/
48
49
50
51 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_WIP_WORKFLOW_API_PK';
52 G_ITEM_TYPE_NAME CONSTANT VARCHAR2(30) := 'OEOL';
53
54
55 /******************************************************************************
56
57 Procedure : CTO_DEBUG
58 Parameters : proc_name --- Name of the procedure which is calling this utility
59 Text --- Debug message which needs to be written to the log file
60
61
62 Description : This utility will write the message into the CTO Debug file
63 in ctoDDHH24MISS.dbg format.
64
65
66
67 *********************************************************************************/
68
69 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
70
71 PROCEDURE CTO_DEBUG(
72 proc_name IN VARCHAR2,
73 text IN VARCHAR2) is
74 fname utl_file.file_type;
75
76 BEGIN
77
78 -- bugfix 2430063 : Only if OM:Debug Level is set to 5 or more, cto* debug
79 -- will be generated.
80
81
82 if ( gDebugLevel < 5 ) then
83 return;
84 end if;
85
86
87 if CTO_WIP_WORKFLOW_API_PK.file_dir is null then
88
89 /* -- begin bugfix 3511114: use v$parameter2 instead.
90 *
91 * select ltrim(rtrim(substr(value, instr(value,',',-1,1)+1)))
92 * into file_dir
93 * from v$parameter
94 * where name= 'utl_file_dir';
95 *
96 * -- end bugfix 3511114
97 * */
98
99 -- begin bugfix 3511114: use v$parameter2 instead.
100
101 select ltrim(rtrim(value))
102 into file_dir
103 from (select value from v$parameter2
104 where name='utl_file_dir'
105 order by rownum desc)
106 where rownum <2;
107
108 -- end bugfix 3511114
109
110 end if;
111
112 if CTO_WIP_WORKFLOW_API_PK.file_name is null then
113 file_name := 'cto_'||to_char(sysdate,'ddhh24miss')||'.dbg';
114 end if;
115
116 fname := utl_file.fopen(CTO_WIP_WORKFLOW_API_PK.file_dir,CTO_WIP_WORKFLOW_API_PK.file_name,'a');
117 utl_file.put_line(fname,proc_name||'::'||text);
118 utl_file.fflush(fname);
119 utl_file.fclose(fname);
120
121
122 EXCEPTION
123 when OTHERS then
124 -- The exception handling is added by renga Kannan on 01/11/2001
125 -- We don't want to stop other functinality becauseo of CTO_DEBUG erros.
126 -- The example cases are if the customer sets the utl_file_dir value as * or .
127 -- We need not create the debug message and need not faile too.
128 null;
129
130 END CTO_DEBUG;
131
132
133 /**************************************************************************
134
135 Procedure: first_wo_reservation_created
136 Parameters: order_line_id - order_line_id
137 x_return_status - standard API output parameter
138 x_msg_count - "
139 x_msg_data - "
140 Description: This callback is used to inform the order line workflow that
141 the first reservation has been created for this sales order
142 line.
143
144 *****************************************************************************/
145
146
147
148 PROCEDURE first_wo_reservation_created(
149 order_line_id IN NUMBER,
150 x_return_status OUT NOCOPY VARCHAR2,
151 x_msg_count OUT NOCOPY NUMBER,
152 x_msg_data OUT NOCOPY VARCHAR2)
153
154 IS
155
156 l_api_name CONSTANT varchar2(40) := 'first_wo_reservation_created';
157 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
158 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
159 v_activity_status_code varchar2(8);
160 return_value integer;
161
162
163 BEGIN
164
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 select item_type_code, ato_line_id
168 into v_item_type_code, v_ato_line_id
169 from oe_order_lines_all
170 where line_id = order_line_id;
171
172 if ((upper(v_item_type_code) = 'STANDARD' OR upper(v_item_type_code) = 'OPTION') --fix for bug#1874380
173 and v_ato_line_id = order_line_id)
174 or
175 upper(v_item_type_code) = 'CONFIG'
176 then
177
178 --
179 -- an ATO item line or CONFIG line
180 -- check if the line status is CREATE_SUPPLY_ORDER_ELIGIBLE or
181 -- SHIP_LINE
182 --
183
184 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
185 'CREATE_SUPPLY_ORDER_ELIGIBLE',
186 'CREATE_SUPPLY_ORDER_ELIGIBLE',
187 v_activity_status_code);
188
189 if upper(v_activity_status_code) = 'NOTIFIED' then
190 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
191 TO_CHAR(order_line_id),
192 'CREATE_SUPPLY_ORDER_ELIGIBLE',
193 'RESERVED');
194
195 else
196 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
197 'SHIP_LINE', 'SHIP_LINE', v_activity_status_code);
198 if upper(v_activity_status_code) <> 'NOTIFIED' then
199 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
200 raise FND_API.G_EXC_ERROR;
201 end if;
202 end if;
203
204 -- display proper status to OM form
205 -- Added By Renga Kannan on 12/18/00 to get the debug messages
206
207 CTO_DEBUG('FIRST_WO_RESERVATION_CREATED',
208 'Calling Display_wf_status procedure for order_line_id='||to_char(order_line_id));
209
210 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
211
212 CTO_DEBUG('FIRST_WO_RESERVATION_CREATED',
213 'Return value from display_wf_status = '||to_char(return_value));
214
215 if return_value <> 1 then
216 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
217 raise FND_API.G_EXC_ERROR;
218 end if;
219
220 end if;
221
222
223 EXCEPTION
224
225 when FND_API.G_EXC_ERROR then
226 IF PG_DEBUG <> 0 THEN
227 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created raised expected error.',1);
228 END IF;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 CTO_MSG_PUB.Count_And_Get(
231 p_msg_count => x_msg_count,
232 p_msg_data => x_msg_data
233 );
234
235 when FND_API.G_EXC_UNEXPECTED_ERROR then
236 IF PG_DEBUG <> 0 THEN
237 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created raised unexpected error.',1);
238 END IF;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
240 CTO_MSG_PUB.Count_And_Get(
241 p_msg_count => x_msg_count,
242 p_msg_data => x_msg_data
243 );
244
245 when others then
246 IF PG_DEBUG <> 0 THEN
247 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created others exception: '||sqlerrm,1);
248 END IF;
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
251 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
252 END IF;
253 CTO_MSG_PUB.Count_And_Get(
254 p_msg_count => x_msg_count,
255 p_msg_data => x_msg_data
256 );
257
258
259
260 END first_wo_reservation_created;
261
262
263 /**************************************************************************
264
265 Procedure: last_wo_reservation_deleted
266 Parameters: order_line_id - order_line_id
267 x_return_status - standard API output parameter
268 x_msg_count - "
269 x_msg_data - "
270 Description: This callback is used to inform the order line workflow that
271 the last reservation has been deleted for this sales order
272 line.
273
274 *****************************************************************************/
275
276 PROCEDURE last_wo_reservation_deleted(
277 order_line_id IN NUMBER,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2
281 )
282 IS
283
284
285
286 l_api_name CONSTANT VARCHAR2(40) := 'last_wo_reservation_deleted';
287 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
288 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
289 v_activity_status_code VARCHAR2(8);
290 v_counter INTEGER;
291 v_counter2 INTEGER;
292 return_value INTEGER;
293 l_source_document_type_id NUMBER; --bugfix 1799874
294
295
296 BEGIN
297
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 select item_type_code, ato_line_id
301 into v_item_type_code, v_ato_line_id
302 from oe_order_lines_all
303 where line_id = order_line_id;
304
305 --bugfix 1799874 start
306 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => order_line_id );
307 --bugfix 1799874 end
308
309 if(((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
310 and v_ato_line_id = order_line_id ) -- fix for bug# 1874380
311 or upper(v_item_type_code) = 'CONFIG'
312 then
313
314 -- an ATO item line or CONFIG line
315 -- check if the line status is SHIP_LINE
316
317 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
318 'SHIP_LINE',
319 'SHIP_LINE', v_activity_status_code);
320 if upper(v_activity_status_code) = 'NOTIFIED' then
321
322 v_counter := 0;
323
324 select count(*) into v_counter
325 from mtl_reservations
326 --where demand_source_type_id = 2
327 where demand_source_type_id =
328 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
329 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
330 and demand_source_line_id = order_line_id
331 and primary_reservation_quantity > 0;
332
333 -- bugfix 1799874 : as per adrian suherman, we need not worry about internal SO for wip_flow_schedules.
334
335
336 v_counter2 := 0;
337
338 /*****************************************************************
339 * The following Sql has changed by Renga Kannan to fix the sql
340 * for performance. The count(*) logic is replaced with the
341 * current logic to get better performance
342 * ***************************************************************/
343 Begin
344 select 1
345 into v_counter2
346 from dual
347 where exists(select 'x'
348 from wip_flow_schedules
349 where demand_source_type = 2
350 and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
351 Exception when no_data_found then
352 v_counter2 := 0;
353 End;
354
355
356 /* no reservation at all */
357 if v_counter = 0 and v_counter2 = 0 then
358 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
359 TO_CHAR(order_line_id),
360 'SHIP_LINE',
361 'UNRESERVE');
362 end if;
363
364 else
365 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
366 raise FND_API.G_EXC_ERROR;
367 end if;
368
369 -- display proper status to OM form
370
371 CTO_DEBUG('LAST_WO_RESERVATION_DELETED',
372 'Calling Display_wf_status procedure for order_line_id='||to_char(order_line_id));
373
374
375 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
376
377 CTO_DEBUG('LAST_WO_RESERVATION',
378 'Return value from display_wf_status = '||to_char(order_line_id));
379
380 if return_value <> 1 then
381 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
382 raise FND_API.G_EXC_ERROR;
383 end if;
384
385 end if;
386
387
388 EXCEPTION
389
390 when FND_API.G_EXC_ERROR then
391 IF PG_DEBUG <> 0 THEN
392 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised expected error.',1);
393 END IF;
394 x_return_status := FND_API.G_RET_STS_ERROR;
395 CTO_MSG_PUB.Count_And_Get(
396 p_msg_count => x_msg_count,
397 p_msg_data => x_msg_data
398 );
399
400
401 when FND_API.G_EXC_UNEXPECTED_ERROR then
402 IF PG_DEBUG <> 0 THEN
403 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised unexpected error.',1);
404 END IF;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 CTO_MSG_PUB.Count_And_Get(
407 p_msg_count => x_msg_count,
408 p_msg_data => x_msg_data
409 );
410
411 when others then
412 IF PG_DEBUG <> 0 THEN
413 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised others exception: '||sqlerrm,1);
414 END IF;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
417 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
418 END IF;
419 CTO_MSG_PUB.Count_And_Get(
420 p_msg_count => x_msg_count,
421 p_msg_data => x_msg_data
422 );
423
424
425 END last_wo_reservation_deleted;
426
427
428
429 /**************************************************************************
430
431 Procedure: flow_creation
432 Parameters: order_line_id - order_line_id
433 x_return_status - standard API output parameter
434 x_msg_count - "
435 x_msg_data - "
436 Description: This callback is used to inform the order line workflow that
437 the first flow schedule has been created for this sales order
438 line.
439
440 *****************************************************************************/
441
442
443 PROCEDURE flow_creation(
444 order_line_id IN NUMBER,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2)
448
449 IS
450
451 l_api_name CONSTANT VARCHAR2(40) := 'flow_creation';
452 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
453 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
454 v_activity_status_code VARCHAR2(8);
455 return_value INTEGER;
456
457
458 BEGIN
459
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 select item_type_code, ato_line_id
463 into v_item_type_code, v_ato_line_id
464 from oe_order_lines_all
465 where line_id = order_line_id;
466
467 if (((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
468 and v_ato_line_id = order_line_id) -- fix for bug #1874380
469 or upper(v_item_type_code) = 'CONFIG' then
470
471 -- an ATO item line or CONFIG line
472 -- check if the line status is CREATE_SUPPLY_ORDER_ELIGIBLE or
473 -- SHIP_LINE
474
475 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
476 'CREATE_SUPPLY_ORDER_ELIGIBLE',
477 'CREATE_SUPPLY_ORDER_ELIGIBLE', v_activity_status_code);
478 if upper(v_activity_status_code) = 'NOTIFIED' then
479 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
480 TO_CHAR(order_line_id),
481 'CREATE_SUPPLY_ORDER_ELIGIBLE',
482 'RESERVED');
483
484 else
485 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
486 'SHIP_LINE', 'SHIP_LINE', v_activity_status_code);
487 if upper(v_activity_status_code) <> 'NOTIFIED' then
488 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
489 raise FND_API.G_EXC_ERROR;
490 end if;
491 end if;
492
493 -- display proper status to OM form
494
495 CTO_DEBUG('FLOW_CREATION',
496 ' display_wf_status procedure is called with order_line_id = '||to_char(order_line_id));
497
498 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
499
500 CTO_DEBUG('FLOW_CREATION',
501 ' The return value from display_wf_status = '||to_char(return_value));
502
503 if return_value <> 1 then
504 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
505 raise FND_API.G_EXC_ERROR;
506 end if;
507
508 end if;
509
510
511 EXCEPTION
512
513 when FND_API.G_EXC_ERROR then
514 IF PG_DEBUG <> 0 THEN
515 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised expected error.',1);
516 END IF;
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 CTO_MSG_PUB.Count_And_Get(
519 p_msg_count => x_msg_count,
520 p_msg_data => x_msg_data
521 );
522
523
524 when FND_API.G_EXC_UNEXPECTED_ERROR then
525 IF PG_DEBUG <> 0 THEN
526 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised unexpected error.',1);
527 END IF;
528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
529 CTO_MSG_PUB.Count_And_Get(
530 p_msg_count => x_msg_count,
531 p_msg_data => x_msg_data
532 );
533
534 when others then
535 IF PG_DEBUG <> 0 THEN
536 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised others exception: '||sqlerrm,1);
537 END IF;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
540 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
541 END IF;
542 CTO_MSG_PUB.Count_And_Get(
543 p_msg_count => x_msg_count,
544 p_msg_data => x_msg_data
545 );
546
547
548 END flow_creation;
549
550
551 /**************************************************************************
552
553 Procedure: flow_deletion
554 Parameters: order_line_id - order_line_id
555 x_return_status - standard API output parameter
556 x_msg_count - "
557 x_msg_data - "
558 Description: This callback is used to inform the order line workflow that
559 the last flow schedule has been deleted for this sales order
560 line.
561
562 *****************************************************************************/
563
564 PROCEDURE flow_deletion(
565 order_line_id IN NUMBER,
566 x_return_status OUT NOCOPY VARCHAR2,
567 x_msg_count OUT NOCOPY NUMBER,
568 x_msg_data OUT NOCOPY VARCHAR2
569 )
570 IS
571
572
573
574 l_api_name CONSTANT VARCHAR2(40) := 'flow_deletion';
575 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
576 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
577 v_activity_status_code VARCHAR2(8);
578 v_counter INTEGER;
579 v_counter2 INTEGER;
580 return_value INTEGER;
581 l_source_document_type_id NUMBER; --bugfix 1799874
582
583
584 BEGIN
585
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 select item_type_code, ato_line_id
589 into v_item_type_code, v_ato_line_id
590 from oe_order_lines_all
591 where line_id = order_line_id;
592
593 --bugfix 1799874 start
594 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => order_line_id );
595 --bugfix 1799874 end
596
597 if (((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
598 and v_ato_line_id = order_line_id ) -- fix for bug#1874380
599 or upper(v_item_type_code) = 'CONFIG'
600 then
601
602 -- an ATO item line or CONFIG line
603 -- check if the line status is SHIP_LINE
604
605 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
606 'SHIP_LINE',
607 'SHIP_LINE', v_activity_status_code);
608 if upper(v_activity_status_code) = 'NOTIFIED' then
609
610 v_counter := 0;
611 select count(*) into v_counter
612 from mtl_reservations
613 -- where demand_source_type_id = 2
614 where demand_source_type_id =
615 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
616 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
617 and demand_source_line_id = order_line_id
618 and primary_reservation_quantity > 0;
619
620 -- bugfix 1799874 : as per adrian suherman, we need not worry about internal SO for wip_flow_schedules.
621
622 v_counter2 := 0;
623
624 -- bug 3840900. rkaza. 08/18/2004. select count(*) logic
625 -- is replaced with the current logic to get better performance
626
627 Begin
628 select 1
629 into v_counter2
630 from dual
631 where exists(select 'x'
632 from wip_flow_schedules
633 where demand_source_type = 2
634 and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
635 Exception when no_data_found then
636 v_counter2 := 0;
637 End;
638
639 /* no flow schedule and reservation at all */
640 if v_counter = 0 and v_counter2 = 0 then
641 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
642 TO_CHAR(order_line_id),
643 'SHIP_LINE',
644 'UNRESERVE');
645 end if;
646
647 else
648 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
649 raise FND_API.G_EXC_ERROR;
650 end if;
651
652 -- display proper status to OM form
653
654 CTO_DEBUG('FLOW_DELETION',
655 ' display_wf_status is called with order_line_id = '||to_char(order_line_id));
656
657 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
658
659 CTO_DEBUG('FLOW_DELETION',
660 ' Display_wf_status return value = '||to_char(return_value));
661
662 if return_value <> 1 then
663 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
664 raise FND_API.G_EXC_ERROR;
665 end if;
666
667 end if;
668
669
670 EXCEPTION
671
672 when FND_API.G_EXC_ERROR then
673 IF PG_DEBUG <> 0 THEN
674 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised expected error.',1);
675 END IF;
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 CTO_MSG_PUB.Count_And_Get(
678 p_msg_count => x_msg_count,
679 p_msg_data => x_msg_data
680 );
681
682
683 when FND_API.G_EXC_UNEXPECTED_ERROR then
684 IF PG_DEBUG <> 0 THEN
685 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised expected error.',1);
686 END IF;
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 CTO_MSG_PUB.Count_And_Get(
689 p_msg_count => x_msg_count,
690 p_msg_data => x_msg_data
691 );
692
693 when others then
694 IF PG_DEBUG <> 0 THEN
695 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised others exception:'||sqlerrm,1);
696 END IF;
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
699 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
700 END IF;
701 CTO_MSG_PUB.Count_And_Get(
702 p_msg_count => x_msg_count,
703 p_msg_data => x_msg_data
704 );
705
706 END flow_deletion;
707
708
709
710
711 /**************************************************************************
712
713 Procedure: query_wf_activity_status
714 Parameters: p_itemtype -
715 p_itemkey -
716 p_activity_label - "
717 p_activity_name - "
718 p_activity_status -
719 Description: this procedure is used to query a Workflow activity status
720
721 *****************************************************************************/
722
723 PROCEDURE query_wf_activity_status(
724 p_itemtype IN VARCHAR2,
725 p_itemkey IN VARCHAR2,
726 p_activity_label IN VARCHAR2,
727 p_activity_name IN VARCHAR2,
728 p_activity_status OUT NOCOPY VARCHAR2 )
729
730 IS
731
732
733 BEGIN
734
735 select activity_status
736 into p_activity_status
737 from wf_item_activity_statuses was
738 where was.item_type = p_itemtype
739 and was.item_key = p_itemkey
740 and was.process_activity in
741 (SELECT wpa.instance_id
742 FROM wf_process_activities wpa
743 WHERE wpa.activity_name = p_activity_name);
744
745 EXCEPTION
746
747 when others then
748 p_activity_status := 'NULL';
749
750 END query_wf_activity_status;
751
752 /**************************************************************************
753
754 Function: workflow_build_status
755 Parameters: order_line_id - order_line_id
756 Description: This API will be called by WIP to determine if a particular
757 sales order line is at the released phase of the workflow.
758 This function returns TRUE/FALSE.
759
760 *****************************************************************************/
761
762 FUNCTION workflow_build_status(
763 order_line_id IN NUMBER)
764 return INTEGER is
765
766 v_activity_status_code_1 VARCHAR2(8);
767 v_activity_status_code_2 VARCHAR2(8);
768
769 BEGIN
770
771 query_wf_activity_status('OEOL', TO_CHAR(order_line_id), 'SHIP_LINE',
772 'SHIP_LINE', v_activity_status_code_1);
773
774 query_wf_activity_status('OEOL', TO_CHAR(order_line_id),
775 'CREATE_SUPPLY_ORDER_ELIGIBLE',
776 'CREATE_SUPPLY_ORDER_ELIGIBLE',
777 v_activity_status_code_2);
778
779 if upper(v_activity_status_code_1) = 'ACTIVE' or
780 upper(v_activity_status_code_1) = 'NOTIFIED'or
781 upper(v_activity_status_code_2) = 'ACTIVE' or
782 upper(v_activity_status_code_2) = 'NOTIFIED'or
783 upper(v_activity_status_code_2) = 'COMPLETE'
784 then
785 return (1);
786 else
787 return (2);
788 end if;
789
790
791 EXCEPTION
792
793 when others then
794 return (2);
795
796 END workflow_build_status;
797
798 END CTO_WIP_WORKFLOW_API_PK;
[Home] [Help]