1 PACKAGE BODY CTO_WIP_WORKFLOW_API_PK as
2 /* $Header: CTOWIPAB.pls 120.3 2010/07/21 08:00:08 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 --Adding INCLUDED item type code for SUN ER#9793792
173 --if ((upper(v_item_type_code) = 'STANDARD' OR upper(v_item_type_code) = 'OPTION') --fix for bug#1874380
174 IF ( (UPPER(v_item_type_code) = 'STANDARD'
175 OR UPPER(v_item_type_code) = 'OPTION'
176 OR UPPER(v_item_type_code) = 'INCLUDED'
177 )
178 AND v_ato_line_id = order_line_id
179 )
180 OR UPPER(v_item_type_code) = 'CONFIG'
181 THEN
182
183 --
184 -- an ATO item line or CONFIG line
185 -- check if the line status is CREATE_SUPPLY_ORDER_ELIGIBLE or
186 -- SHIP_LINE
187 --
188
189 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
190 'CREATE_SUPPLY_ORDER_ELIGIBLE',
191 'CREATE_SUPPLY_ORDER_ELIGIBLE',
192 v_activity_status_code);
193
194 if upper(v_activity_status_code) = 'NOTIFIED' then
195 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
196 TO_CHAR(order_line_id),
197 'CREATE_SUPPLY_ORDER_ELIGIBLE',
198 'RESERVED');
199
200 else
201 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
202 'SHIP_LINE', 'SHIP_LINE', v_activity_status_code);
203 if upper(v_activity_status_code) <> 'NOTIFIED' then
204 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
205 raise FND_API.G_EXC_ERROR;
206 end if;
207 end if;
208
209 -- display proper status to OM form
210 -- Added By Renga Kannan on 12/18/00 to get the debug messages
211
212 CTO_DEBUG('FIRST_WO_RESERVATION_CREATED',
213 'Calling Display_wf_status procedure for order_line_id='||to_char(order_line_id));
214
215 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
216
217 CTO_DEBUG('FIRST_WO_RESERVATION_CREATED',
218 'Return value from display_wf_status = '||to_char(return_value));
219
220 if return_value <> 1 then
221 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
222 raise FND_API.G_EXC_ERROR;
223 end if;
224
225 end if;
226
227
228 EXCEPTION
229
230 when FND_API.G_EXC_ERROR then
231 IF PG_DEBUG <> 0 THEN
232 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created raised expected error.',1);
233 END IF;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 CTO_MSG_PUB.Count_And_Get(
236 p_msg_count => x_msg_count,
237 p_msg_data => x_msg_data
238 );
239
240 when FND_API.G_EXC_UNEXPECTED_ERROR then
241 IF PG_DEBUG <> 0 THEN
242 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created raised unexpected error.',1);
243 END IF;
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
245 CTO_MSG_PUB.Count_And_Get(
246 p_msg_count => x_msg_count,
247 p_msg_data => x_msg_data
248 );
249
250 when others then
251 IF PG_DEBUG <> 0 THEN
252 oe_debug_pub.add ('first_wo_reservation_created: ' || 'first_wo_reservation_created others exception: '||sqlerrm,1);
253 END IF;
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
256 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
257 END IF;
258 CTO_MSG_PUB.Count_And_Get(
259 p_msg_count => x_msg_count,
260 p_msg_data => x_msg_data
261 );
262
263
264
265 END first_wo_reservation_created;
266
267
268 /**************************************************************************
269
270 Procedure: last_wo_reservation_deleted
271 Parameters: order_line_id - order_line_id
272 x_return_status - standard API output parameter
273 x_msg_count - "
274 x_msg_data - "
275 Description: This callback is used to inform the order line workflow that
276 the last reservation has been deleted for this sales order
277 line.
278
279 *****************************************************************************/
280
281 PROCEDURE last_wo_reservation_deleted(
282 order_line_id IN NUMBER,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_count OUT NOCOPY NUMBER,
285 x_msg_data OUT NOCOPY VARCHAR2
286 )
287 IS
288
289
290
291 l_api_name CONSTANT VARCHAR2(40) := 'last_wo_reservation_deleted';
292 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
293 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
294 v_activity_status_code VARCHAR2(8);
295 v_counter INTEGER;
296 v_counter2 INTEGER;
297 return_value INTEGER;
298 l_source_document_type_id NUMBER; --bugfix 1799874
299
300
301 BEGIN
302
303 x_return_status := FND_API.G_RET_STS_SUCCESS;
304
305 select item_type_code, ato_line_id
306 into v_item_type_code, v_ato_line_id
307 from oe_order_lines_all
308 where line_id = order_line_id;
309
310 --bugfix 1799874 start
311 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => order_line_id );
312 --bugfix 1799874 end
313
314 --Adding INCLUDED item type code for SUN ER#9793792
315 --if(((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
316 if(( (upper(v_item_type_code) = 'STANDARD')
317 OR (upper(v_item_type_code) = 'OPTION')
318 OR (upper(v_item_type_code) = 'INCLUDED')
319 )
320 and v_ato_line_id = order_line_id ) -- fix for bug# 1874380
321 or upper(v_item_type_code) = 'CONFIG'
322 then
323
324 -- an ATO item line or CONFIG line
325 -- check if the line status is SHIP_LINE
326
327 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
328 'SHIP_LINE',
329 'SHIP_LINE', v_activity_status_code);
330 if upper(v_activity_status_code) = 'NOTIFIED' then
331
332 v_counter := 0;
333
334 select count(*) into v_counter
335 from mtl_reservations
336 --where demand_source_type_id = 2
337 where demand_source_type_id =
338 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
339 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
340 and demand_source_line_id = order_line_id
341 and primary_reservation_quantity > 0;
342
343 -- bugfix 1799874 : as per adrian suherman, we need not worry about internal SO for wip_flow_schedules.
344
345
346 v_counter2 := 0;
347
348 /*****************************************************************
349 * The following Sql has changed by Renga Kannan to fix the sql
350 * for performance. The count(*) logic is replaced with the
351 * current logic to get better performance
352 * ***************************************************************/
353 Begin
354 select 1
355 into v_counter2
356 from dual
357 where exists(select 'x'
358 from wip_flow_schedules
359 where demand_source_type = 2
360 and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
361 Exception when no_data_found then
362 v_counter2 := 0;
363 End;
364
365
366 /* no reservation at all */
367 if v_counter = 0 and v_counter2 = 0 then
368 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
369 TO_CHAR(order_line_id),
370 'SHIP_LINE',
371 'UNRESERVE');
372 end if;
373
374 else
375 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
376 raise FND_API.G_EXC_ERROR;
377 end if;
378
379 -- display proper status to OM form
380
381 CTO_DEBUG('LAST_WO_RESERVATION_DELETED',
382 'Calling Display_wf_status procedure for order_line_id='||to_char(order_line_id));
383
384
385 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
386
387 CTO_DEBUG('LAST_WO_RESERVATION',
388 'Return value from display_wf_status = '||to_char(order_line_id));
389
390 if return_value <> 1 then
391 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
392 raise FND_API.G_EXC_ERROR;
393 end if;
394
395 end if;
396
397
398 EXCEPTION
399
400 when FND_API.G_EXC_ERROR then
401 IF PG_DEBUG <> 0 THEN
402 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised expected error.',1);
403 END IF;
404 x_return_status := FND_API.G_RET_STS_ERROR;
405 CTO_MSG_PUB.Count_And_Get(
406 p_msg_count => x_msg_count,
407 p_msg_data => x_msg_data
408 );
409
410
411 when FND_API.G_EXC_UNEXPECTED_ERROR then
412 IF PG_DEBUG <> 0 THEN
413 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised unexpected error.',1);
414 END IF;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 CTO_MSG_PUB.Count_And_Get(
417 p_msg_count => x_msg_count,
418 p_msg_data => x_msg_data
419 );
420
421 when others then
422 IF PG_DEBUG <> 0 THEN
423 oe_debug_pub.add ('last_wo_reservation_deleted: ' || 'last_wo_reservation_deleted raised others exception: '||sqlerrm,1);
424 END IF;
425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
427 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
428 END IF;
429 CTO_MSG_PUB.Count_And_Get(
430 p_msg_count => x_msg_count,
431 p_msg_data => x_msg_data
432 );
433
434
435 END last_wo_reservation_deleted;
436
437
438
439 /**************************************************************************
440
441 Procedure: flow_creation
442 Parameters: order_line_id - order_line_id
443 x_return_status - standard API output parameter
444 x_msg_count - "
445 x_msg_data - "
446 Description: This callback is used to inform the order line workflow that
447 the first flow schedule has been created for this sales order
448 line.
449
450 *****************************************************************************/
451
452
453 PROCEDURE flow_creation(
454 order_line_id IN NUMBER,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2)
458
459 IS
460
461 l_api_name CONSTANT VARCHAR2(40) := 'flow_creation';
462 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
463 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
464 v_activity_status_code VARCHAR2(8);
465 return_value INTEGER;
466
467
468 BEGIN
469
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471
472 select item_type_code, ato_line_id
473 into v_item_type_code, v_ato_line_id
474 from oe_order_lines_all
475 where line_id = order_line_id;
476
477 --Adding INCLUDED item type code for SUN ER#9793792
478 --if (((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
479 if (( (upper(v_item_type_code) = 'STANDARD')
480 OR (upper(v_item_type_code) = 'OPTION')
481 OR (upper(v_item_type_code) = 'INCLUDED')
482 )
483 and v_ato_line_id = order_line_id) -- fix for bug #1874380
484 or upper(v_item_type_code) = 'CONFIG' then
485
486 -- an ATO item line or CONFIG line
487 -- check if the line status is CREATE_SUPPLY_ORDER_ELIGIBLE or
488 -- SHIP_LINE
489
490 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
491 'CREATE_SUPPLY_ORDER_ELIGIBLE',
492 'CREATE_SUPPLY_ORDER_ELIGIBLE', v_activity_status_code);
493 if upper(v_activity_status_code) = 'NOTIFIED' then
494 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
495 TO_CHAR(order_line_id),
496 'CREATE_SUPPLY_ORDER_ELIGIBLE',
497 'RESERVED');
498
499 else
500 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
501 'SHIP_LINE', 'SHIP_LINE', v_activity_status_code);
502 if upper(v_activity_status_code) <> 'NOTIFIED' then
503 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
504 raise FND_API.G_EXC_ERROR;
505 end if;
506 end if;
507
508 -- display proper status to OM form
509
510 CTO_DEBUG('FLOW_CREATION',
511 ' display_wf_status procedure is called with order_line_id = '||to_char(order_line_id));
512
513 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
514
515 CTO_DEBUG('FLOW_CREATION',
516 ' The return value from display_wf_status = '||to_char(return_value));
517
518 if return_value <> 1 then
519 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
520 raise FND_API.G_EXC_ERROR;
521 end if;
522
523 end if;
524
525
526 EXCEPTION
527
528 when FND_API.G_EXC_ERROR then
529 IF PG_DEBUG <> 0 THEN
530 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised expected error.',1);
531 END IF;
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 CTO_MSG_PUB.Count_And_Get(
534 p_msg_count => x_msg_count,
535 p_msg_data => x_msg_data
536 );
537
538
539 when FND_API.G_EXC_UNEXPECTED_ERROR then
540 IF PG_DEBUG <> 0 THEN
541 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised unexpected error.',1);
542 END IF;
543 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
544 CTO_MSG_PUB.Count_And_Get(
545 p_msg_count => x_msg_count,
546 p_msg_data => x_msg_data
547 );
548
549 when others then
550 IF PG_DEBUG <> 0 THEN
551 oe_debug_pub.add ('flow_creation: ' || 'flow_creation raised others exception: '||sqlerrm,1);
552 END IF;
553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
554 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
555 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
556 END IF;
557 CTO_MSG_PUB.Count_And_Get(
558 p_msg_count => x_msg_count,
559 p_msg_data => x_msg_data
560 );
561
562
563 END flow_creation;
564
565
566 /**************************************************************************
567
568 Procedure: flow_deletion
569 Parameters: order_line_id - order_line_id
570 x_return_status - standard API output parameter
571 x_msg_count - "
572 x_msg_data - "
573 Description: This callback is used to inform the order line workflow that
574 the last flow schedule has been deleted for this sales order
575 line.
576
577 *****************************************************************************/
578
579 PROCEDURE flow_deletion(
580 order_line_id IN NUMBER,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_count OUT NOCOPY NUMBER,
583 x_msg_data OUT NOCOPY VARCHAR2
584 )
585 IS
586
587
588
589 l_api_name CONSTANT VARCHAR2(40) := 'flow_deletion';
590 v_item_type_code oe_order_lines_all.item_type_code%TYPE;
591 v_ato_line_id oe_order_lines_all.ato_line_id%TYPE;
592 v_activity_status_code VARCHAR2(8);
593 v_counter INTEGER;
594 v_counter2 INTEGER;
595 return_value INTEGER;
596 l_source_document_type_id NUMBER; --bugfix 1799874
597
598
599 BEGIN
600
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602
603 select item_type_code, ato_line_id
604 into v_item_type_code, v_ato_line_id
605 from oe_order_lines_all
606 where line_id = order_line_id;
607
608 --bugfix 1799874 start
609 l_source_document_type_id := CTO_UTILITY_PK.get_source_document_id ( pLineId => order_line_id );
610 --bugfix 1799874 end
611
612 --Adding INCLUDED item type code for SUN ER#9793792
613 --if (((upper(v_item_type_code) = 'STANDARD') OR (upper(v_item_type_code) = 'OPTION'))
614 if (( (upper(v_item_type_code) = 'STANDARD')
615 OR (upper(v_item_type_code) = 'OPTION')
616 OR (upper(v_item_type_code) = 'INCLUDED')
617 )
618 and v_ato_line_id = order_line_id ) -- fix for bug#1874380
619 or upper(v_item_type_code) = 'CONFIG'
620 then
621
622 -- an ATO item line or CONFIG line
623 -- check if the line status is SHIP_LINE
624
625 query_wf_activity_status(G_ITEM_TYPE_NAME, TO_CHAR(order_line_id),
626 'SHIP_LINE',
627 'SHIP_LINE', v_activity_status_code);
628 if upper(v_activity_status_code) = 'NOTIFIED' then
629
630 v_counter := 0;
631 select count(*) into v_counter
632 from mtl_reservations
633 -- where demand_source_type_id = 2
634 where demand_source_type_id =
635 decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
636 inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
637 and demand_source_line_id = order_line_id
638 and primary_reservation_quantity > 0;
639
640 -- bugfix 1799874 : as per adrian suherman, we need not worry about internal SO for wip_flow_schedules.
641
642 v_counter2 := 0;
643
644 -- bug 3840900. rkaza. 08/18/2004. select count(*) logic
645 -- is replaced with the current logic to get better performance
646
647 Begin
648 select 1
649 into v_counter2
650 from dual
651 where exists(select 'x'
652 from wip_flow_schedules
653 where demand_source_type = 2
654 and demand_source_line = to_char(order_line_id)); --Bugfix 6330114
655 Exception when no_data_found then
656 v_counter2 := 0;
657 End;
658
659 /* no flow schedule and reservation at all */
660 if v_counter = 0 and v_counter2 = 0 then
661 wf_engine.CompleteActivityInternalName(G_ITEM_TYPE_NAME,
662 TO_CHAR(order_line_id),
663 'SHIP_LINE',
664 'UNRESERVE');
665 end if;
666
667 else
668 cto_msg_pub.cto_message('BOM', 'CTO_INVALID_ACTIVITY_STATUS');
669 raise FND_API.G_EXC_ERROR;
670 end if;
671
672 -- display proper status to OM form
673
674 CTO_DEBUG('FLOW_DELETION',
675 ' display_wf_status is called with order_line_id = '||to_char(order_line_id));
676
677 return_value := CTO_WORKFLOW_API_PK.display_wf_status(order_line_id);
678
679 CTO_DEBUG('FLOW_DELETION',
680 ' Display_wf_status return value = '||to_char(return_value));
681
682 if return_value <> 1 then
683 cto_msg_pub.cto_message('BOM', 'CTO_ERROR_FROM_DISPLAY_STATUS');
684 raise FND_API.G_EXC_ERROR;
685 end if;
686
687 end if;
688
689
690 EXCEPTION
691
692 when FND_API.G_EXC_ERROR then
693 IF PG_DEBUG <> 0 THEN
694 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised expected error.',1);
695 END IF;
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 CTO_MSG_PUB.Count_And_Get(
698 p_msg_count => x_msg_count,
699 p_msg_data => x_msg_data
700 );
701
702
703 when FND_API.G_EXC_UNEXPECTED_ERROR then
704 IF PG_DEBUG <> 0 THEN
705 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised expected error.',1);
706 END IF;
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708 CTO_MSG_PUB.Count_And_Get(
709 p_msg_count => x_msg_count,
710 p_msg_data => x_msg_data
711 );
712
713 when others then
714 IF PG_DEBUG <> 0 THEN
715 oe_debug_pub.add ('flow_deletion: ' || 'flow_deletion raised others exception:'||sqlerrm,1);
716 END IF;
717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
718 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
719 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
720 END IF;
721 CTO_MSG_PUB.Count_And_Get(
722 p_msg_count => x_msg_count,
723 p_msg_data => x_msg_data
724 );
725
726 END flow_deletion;
727
728
729
730
731 /**************************************************************************
732
733 Procedure: query_wf_activity_status
734 Parameters: p_itemtype -
735 p_itemkey -
736 p_activity_label - "
737 p_activity_name - "
738 p_activity_status -
739 Description: this procedure is used to query a Workflow activity status
740
741 *****************************************************************************/
742
743 PROCEDURE query_wf_activity_status(
744 p_itemtype IN VARCHAR2,
745 p_itemkey IN VARCHAR2,
746 p_activity_label IN VARCHAR2,
747 p_activity_name IN VARCHAR2,
748 p_activity_status OUT NOCOPY VARCHAR2 )
749
750 IS
751
752
753 BEGIN
754
755 select activity_status
756 into p_activity_status
757 from wf_item_activity_statuses was
758 where was.item_type = p_itemtype
762 FROM wf_process_activities wpa
759 and was.item_key = p_itemkey
760 and was.process_activity in
761 (SELECT wpa.instance_id
763 WHERE wpa.activity_name = p_activity_name);
764
765 EXCEPTION
766
767 when others then
768 p_activity_status := 'NULL';
769
770 END query_wf_activity_status;
771
772 /**************************************************************************
773
774 Function: workflow_build_status
775 Parameters: order_line_id - order_line_id
776 Description: This API will be called by WIP to determine if a particular
777 sales order line is at the released phase of the workflow.
778 This function returns TRUE/FALSE.
779
780 *****************************************************************************/
781
782 FUNCTION workflow_build_status(
783 order_line_id IN NUMBER)
784 return INTEGER is
785
786 v_activity_status_code_1 VARCHAR2(8);
787 v_activity_status_code_2 VARCHAR2(8);
788
789 BEGIN
790
791 query_wf_activity_status('OEOL', TO_CHAR(order_line_id), 'SHIP_LINE',
792 'SHIP_LINE', v_activity_status_code_1);
793
794 query_wf_activity_status('OEOL', TO_CHAR(order_line_id),
795 'CREATE_SUPPLY_ORDER_ELIGIBLE',
796 'CREATE_SUPPLY_ORDER_ELIGIBLE',
797 v_activity_status_code_2);
798
799 if upper(v_activity_status_code_1) = 'ACTIVE' or
800 upper(v_activity_status_code_1) = 'NOTIFIED'or
801 upper(v_activity_status_code_2) = 'ACTIVE' or
802 upper(v_activity_status_code_2) = 'NOTIFIED'or
803 upper(v_activity_status_code_2) = 'COMPLETE'
804 then
805 return (1);
806 else
807 return (2);
808 end if;
809
810
811 EXCEPTION
812
813 when others then
814 return (2);
815
816 END workflow_build_status;
817
818 END CTO_WIP_WORKFLOW_API_PK;
[Home] [Help]