[Home] [Help]
PACKAGE BODY: APPS.CTO_FLOW_SCHEDULE
Source
1 package body CTO_FLOW_SCHEDULE as
2 /* $Header: CTOFLSCB.pls 120.3 2006/06/26 23:02:23 rekannan noship $ */
3 /*----------------------------------------------------------------------------+
4 | Copyright (c) 1998 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 | Oracle Manufacturing |
7 +-----------------------------------------------------------------------------+
8 |
9 | FILE NAME :
10 | DESCRIPTION :
11 | This file creates a packaged Procedures which create
12 | flow schedules for ATO items/ configured items.
13 | To remain a 'noship' file till further decision
14 |
15 | HISTORY : June 30, 1999 Initial Version Angela Makalintal
16 |
17 | DEc 12, 2002 Kiran Konada
18 | Added code for ML supply enchancement
19 | Feb 18,2002 Kiran Konada
20 | bugfix 2803881
21 | user_id (cetaed by) is passed
22 | to create_subassembly_jobs program
23 |
24 | June 1 , 2005 Added Nocopy Hint Renga Kannan
25 |
26 | Mar 07, 2005 Kiran Konada
27 | performance bugfix 4905864
28 | removed dependency on mrp_unscheduled_orders_v
29 |
30 *============================================================================*/
31 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
32
33 PROCEDURE cto_fs(
34 p_config_line_id in varchar2,
35 x_return_status out NoCopy varchar2,
36 x_msg_name out NoCopy varchar2,
37 x_msg_txt out NoCopy varchar2 )
38 IS
39 l_stmt_num number := 0;
40 l_line_id number;
41 l_mfg_org_id number;
42 l_x_wip_entity_id number;
43 l_x_org_id number;
44 l_x_return_status varchar2(1);
45 l_x_msg_count number;
46 l_x_msg_data varchar2(240);
47
48 l_return_status varchar2(1);
49
50 --ml supply var's
51 l_mlsupply_parameter number := 1; --may need to chnage later 1 =auto-created , 2 =a toiutem and autocreated
52 l_created_by number := null ; --bugfix 2803881
53 l_error_message varchar2(70) := null;
54 l_message_name varchar2(30) := null;
55
56 CREATE_FLOW_SCHED_ERROR exception;
57 SCHEDULE_FLOW_SCHED_ERROR exception;
58
59 BEGIN
60 x_return_status := FND_API.G_RET_STS_SUCCESS;
61 l_stmt_num := 100;
62 cto_create_fs(p_config_line_id,
63 l_x_wip_entity_id,
64 l_x_return_status,
65 l_x_msg_count,
66 l_x_msg_data,
67 x_msg_name);
68
69 if (l_x_return_status = FND_API.G_RET_STS_ERROR) then
70 IF PG_DEBUG <> 0 THEN
71 oe_debug_pub.add('cto_fs: ' || 'Expected error in cto_create_fs.', 1);
72 END IF;
73 raise FND_API.G_EXC_ERROR;
74
75 elsif (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
76 IF PG_DEBUG <> 0 THEN
77 oe_debug_pub.add('cto_fs: ' || 'UnExpected error in cto_create_fs.', 1);
78 END IF;
79 raise FND_API.G_EXC_UNEXPECTED_ERROR;
80
81 end if;
82
83 IF PG_DEBUG <> 0 THEN
84 oe_debug_pub.add('cto_fs: ' || 'Success in cto_create_fs.', 1);
85 END IF;
86
87 l_stmt_num := 110;
88 cto_schedule_fs (p_config_line_id,
89 l_x_wip_entity_id,
90 l_x_return_status,
91 l_x_msg_count,
92 l_x_msg_data,
93 x_msg_name);
94
95 if (l_x_return_status = FND_API.G_RET_STS_ERROR) then
96 IF PG_DEBUG <> 0 THEN
97 oe_debug_pub.add('cto_fs: ' || 'Expected error in cto_schedule_fs.', 1);
98 END IF;
99 raise FND_API.G_EXC_ERROR;
100
101 elsif (l_x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
102 IF PG_DEBUG <> 0 THEN
103 oe_debug_pub.add('cto_fs: ' || 'UnExpected error in cto_schedule_fs.', 1);
104 END IF;
105 raise FND_API.G_EXC_UNEXPECTED_ERROR;
106 end if;
107
108
109 --looking for ML supply org parameter value
110 --change the if clause after sajani changes her code
111 SELECT ENABLE_LOWER_LEVEL_SUPPLY,oel.created_by
112 INTO l_mlsupply_parameter,l_created_by --bugfix 2803881
113 FROM bom_parameters bp,
114 oe_order_lines_all oel
115 WHERE oel.line_id = p_config_line_id
116 AND oel.ship_from_org_id = bp.organization_id;
117
118 --ml supply code
119
120 IF (l_mlsupply_parameter in (2,3)) THEN --auto created config =2, auto created configs + ato items = 3
121
122 oe_debug_pub.add ('cto_fs: ' || 'before calling create sub-assembly jobs',1);
123
124 l_stmt_num := 111;
125 CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs(
126 l_mlsupply_parameter,
127 p_config_line_id,
128 null,
129 null,
130 null,
131 null,
132 null,
133 null,
134 null,
135 l_created_by,--bugfix 2803881
136 null,
137 l_return_status,
138 l_error_message,
139 l_message_name
140 );
141
142
143 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
144 IF PG_DEBUG <> 0 THEN
145 oe_debug_pub.add ('cto_fs: ' || 'failed after CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs ' || l_return_status ,1);
146 oe_debug_pub.add ('cto_fs: ' || 'error message' || l_error_message ,1);
147 END IF;
148 RAISE FND_API.G_EXC_ERROR;
149 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
150 IF PG_DEBUG <> 0 THEN
151 oe_debug_pub.add ('cto_fs: ' || ' failed after CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs' || l_return_status ,1);
152 oe_debug_pub.add ('cto_fs: ' || 'error message' || l_error_message ,1);
153 END IF;
154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
155 ELSE
156 IF PG_DEBUG <> 0 THEN
157 oe_debug_pub.add('cto_fs: ' || 'success from CTO_SUBASSEMBLY_SUP_PK.create_subassembly_jobs' ,1);
158 oe_debug_pub.add('cto_fs: ' || l_error_message ,1);
159
160 END IF;
161 END IF;
162
163
164
165 END IF; --l_mlsupply_parameter
166
167
168
169
170
171 IF PG_DEBUG <> 0 THEN
172 oe_debug_pub.add('cto_fs: ' || 'Success in cto_schedule_fs.', 1);
173
174 oe_debug_pub.add('cto_fs: ' || 'Msg Data:' || l_x_msg_data, 1);
175 END IF;
176
177 EXCEPTION
178
179 when FND_API.G_EXC_ERROR then
180 x_msg_txt := 'CTOFLSCB.cto_fs: raised expected error in stmt:' || to_char(l_stmt_num) || ':' ||
181 l_x_msg_data;
182 if (x_msg_name is null) then
183 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
184 end if;
185 x_return_status :=FND_API.G_RET_STS_ERROR;
186
187
188 when FND_API.G_EXC_UNEXPECTED_ERROR then
189 x_msg_txt := 'CTOFLSCB.cto_fs: raised Unexpected error in stmt:' || to_char(l_stmt_num) || ':' ||
190 l_x_msg_data;
191 if (x_msg_name is null) then
192 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
193 end if;
194 x_return_status :=FND_API.G_RET_STS_UNEXP_ERROR;
195 raise;
196
197 when OTHERS then
198 x_msg_txt := 'CTOFLSCB.cto_fs: ' || to_char(l_stmt_num) || ':' ||
199 substrb(sqlerrm, 1, 100);
200 if (x_msg_name is null) then
201 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
202 end if;
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 raise;
205
206 END cto_fs;
207
208
209
210 PROCEDURE cto_create_fs (
211 p_config_line_id in varchar2,
212 x_wip_entity_id out NoCopy number,
213 x_return_status out NoCopy varchar2,
214 x_msg_count out NoCopy number,
215 x_msg_data out NoCopy varchar2,
216 x_msg_name out NoCopy varchar2 )
217
218
219 IS
220
221 l_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
222 l_x_flow_schedule_rec mrp_flow_schedule_pub.flow_schedule_rec_type;
223 l_x_flow_schedule_val_rec mrp_flow_schedule_pub.flow_schedule_val_rec_type;
224 l_config_item_id number;
225 l_org_id number;
226 l_return_status varchar2(1);
227 l_msg_count number;
228 l_msg_data varchar2(240);
229 l_stmt_num number := 0;
230
231 CREATE_FLOW_SCHED_ERROR exception;
232
233 BEGIN
234
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 l_stmt_num := 90;
238 select inventory_item_id, ship_from_org_id
239 into l_config_item_id, l_org_id
240 from oe_order_lines_all oel
241 where oel.line_id = p_config_line_id;
242
243 --perf bugfix 4905864 (high shared memory)sql id 14505192(drop4)
244 --replacing mrp_unscheduled_orders_v with query on BOR,wip_line and oe_order_lines_all
245 --Verified with Yun.lin
246
247 l_stmt_num := 95;
248 SELECT wl.LINE_ID
249 INTO l_flow_schedule_rec.line_id
250 FROM BOM_OPERATIONAL_ROUTINGS BOR,
251 wip_lines wl
252 WHERE BOR.ASSEMBLY_ITEM_ID = l_config_item_id
253 AND BOR.ORGANIZATION_ID = l_org_id
254 AND BOR.CFM_ROUTING_FLAG = 1
255 AND BOR.alternate_routing_designator IS NULL
256 AND wl.line_id = bor.line_id
257 AND wl.organization_id = bor.organization_id;
258
259 l_stmt_num := 100;
260 --get demand_source_header_id from mtl_sales order ,similar to mrp_unscheduled_orders_v
261 --planned qty should be converted to primary uom as flow honors only primary uom
262 --planned qty conversion is done by same INV api being used in view mrp_unscheduled_orders_v
263 --demand_source_type is 2 as in mrp_unscheduled_orders_v
264
265 SELECT INV_SALESORDER.GET_SALESORDER_FOR_OEHEADER(HEADER_ID),
266 line_id,
267 to_char(NULL),
268 2,
269 inventory_item_id,
270 INV_DECIMALS_PUB.GET_PRIMARY_QUANTITY(SHIP_FROM_ORG_ID ,INVENTORY_ITEM_ID,ORDER_QUANTITY_UOM ,ORDERED_QUANTITY),
271 ship_from_org_id,
272 project_id,
273 schedule_ship_date,
274 task_id
275 INTO l_flow_schedule_rec.demand_source_header_id,
276 l_flow_schedule_rec.demand_source_line,
277 l_flow_schedule_rec.demand_source_delivery,
278 l_flow_schedule_rec.demand_source_type,
279 l_flow_schedule_rec.primary_item_id,
280 l_flow_schedule_rec.planned_quantity,
281 l_flow_schedule_rec.organization_id,
282 l_flow_schedule_rec.project_id,
283 l_flow_schedule_rec.scheduled_completion_date,
284 l_flow_schedule_rec.task_id
285 FROM oe_order_lines_all
286 WHERE line_id = p_config_line_id;
287
288
289
290 IF PG_DEBUG <> 0 THEN
291 oe_debug_pub.add('cto_create_fs: ' || 'Header ID: ' ||
292 to_char(l_flow_schedule_rec.demand_source_header_id),2);
293
294 oe_debug_pub.add('cto_create_fs: ' || 'Demand Source Line ID: ' ||
295 l_flow_schedule_rec.demand_source_line,1);
296 END IF;
297 -- oe_debug_pub.add('Demand Source Delivery: ' ||
298 -- to_char(l_flow_schedule_rec.demand_source_delivery),1);
299 IF PG_DEBUG <> 0 THEN
300 oe_debug_pub.add('cto_create_fs: ' || 'Demand Source Type: ' ||
301 to_char(l_flow_schedule_rec.demand_source_type),2);
302
303 oe_debug_pub.add('cto_create_fs: ' || 'Line ID: ' ||
304 to_char(l_flow_schedule_rec.line_id),2);
305
306 oe_debug_pub.add('cto_create_fs: ' || 'Organization ID: ' ||
307 to_char(l_flow_schedule_rec.organization_id),2);
308
309 oe_debug_pub.add('cto_create_fs: ' || 'Planed Quantity: ' ||
310 to_char(l_flow_schedule_rec.planned_quantity),2);
311
312 oe_debug_pub.add('cto_create_fs: ' || 'Primary Item ID: ' ||
313 to_char(l_flow_schedule_rec.primary_item_id),2);
314
315 oe_debug_pub.add('cto_create_fs: ' || 'Project ID: ' ||
316 to_char(l_flow_schedule_rec.project_id),2);
317
318 oe_debug_pub.add('cto_create_fs: ' || 'Schedule Completion Date: ' ||
319 to_char(l_flow_schedule_rec.scheduled_completion_date),2);
320
321 oe_debug_pub.add('cto_create_fs: ' || 'Task ID: ' ||
322 to_char(l_flow_schedule_rec.task_id),2);
323 END IF;
324
325 /** the following sql%rowcount does not make sense
326 since the above select will raise no-data-found
327 if no rows are selected.
328
329 -- if (SQL%ROWCOUNT <= 0) then
330 -- raise FND_API.G_EXC_ERROR;
331 -- end if;
332
333 ***/
334
335 l_flow_schedule_rec.operation := MRP_GLOBALS.G_OPR_CREATE;
336 l_flow_schedule_rec.scheduled_flag := 3;
337 -- Fixed bug 5153755
338 -- In R12, Flow team mandated the Calling APIs to ass the request id
339 -- It is mandatroy field. As per Yun Lin, we are adding the following stmt
340 l_flow_schedule_rec.request_id := USERENV('SESSIONID');
341
342
343
344 l_stmt_num := 110;
345 MRP_Flow_Schedule_PUB.Process_Flow_Schedule(
346 p_api_version_number => 1.0,
347 p_init_msg_list => FND_API.G_TRUE,
348 x_return_status => l_return_status,
349 x_msg_count => l_msg_count,
350 x_msg_data => l_msg_data,
351 p_flow_schedule_rec => l_flow_schedule_rec,
352 x_flow_schedule_rec => l_x_flow_schedule_rec,
353 x_flow_schedule_val_rec => l_x_flow_schedule_val_rec
354 );
355
356
357 if (x_return_status = FND_API.G_RET_STS_ERROR) then
358 IF PG_DEBUG <> 0 THEN
359 oe_debug_pub.add('cto_create_fs: ' || 'Expected error in Process Flow Schedule with status: ' || l_return_status, 1);
360 END IF;
361 raise FND_API.G_EXC_ERROR;
362
363 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
364 IF PG_DEBUG <> 0 THEN
365 oe_debug_pub.add('cto_create_fs: ' || 'UnExpected error in Process Flow Schedule with status: ' || l_return_status, 1);
366 END IF;
367 raise FND_API.G_EXC_UNEXPECTED_ERROR;
368
369 else
370 IF PG_DEBUG <> 0 THEN
371 oe_debug_pub.add('cto_create_fs: ' || 'Success in Process Flow Schedule.',1);
372 END IF;
373 if (l_x_flow_schedule_rec.wip_entity_id is not NULL) then
374 x_wip_entity_id := l_x_flow_schedule_rec.wip_entity_id;
375 IF PG_DEBUG <> 0 THEN
376 oe_debug_pub.add('cto_create_fs: ' || 'WIP Entity ID: ' || to_char(x_wip_entity_id),2);
377 END IF;
378 end if;
379
380 end if; /* Create Flow Schedule Success */
381
382 EXCEPTION
383
384 when FND_API.G_EXC_ERROR then
385
386 x_msg_data := 'CTOFLSCB.cto_create_fs raised expected error: ' || to_char(l_stmt_num) || ':' || l_msg_data;
387 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
388 x_return_status := FND_API.G_RET_STS_ERROR;
389 cto_msg_pub.cto_message('BOM', x_msg_name);
390 IF PG_DEBUG <> 0 THEN
391 oe_debug_pub.add('cto_create_fs: ' || x_msg_data,1);
392 END IF;
393 CTO_MSG_PUB.Count_And_Get
394 (p_msg_count => x_msg_count
395 ,p_msg_data => x_msg_data
396 );
397
398
399
400 when FND_API.G_EXC_UNEXPECTED_ERROR then
401
402 x_msg_data := 'CTOFLSCB.cto_create_fs raised unexp error: ' || to_char(l_stmt_num) || ':' || l_msg_data;
403 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
405 cto_msg_pub.cto_message('BOM', x_msg_name);
406 IF PG_DEBUG <> 0 THEN
407 oe_debug_pub.add('cto_create_fs: ' || x_msg_data, 1);
408 END IF;
409 CTO_MSG_PUB.Count_And_Get
410 (p_msg_count => x_msg_count
411 ,p_msg_data => x_msg_data
412 );
413
414
415 when NO_DATA_FOUND then
416 x_msg_data := 'CTOFLSCB.cto_create_fs: raised no-data-found' || to_char(l_stmt_num);
417 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
419 cto_msg_pub.cto_message('BOM', x_msg_name);
420 IF PG_DEBUG <> 0 THEN
421 oe_debug_pub.add('cto_create_fs: ' || x_msg_data,1);
422 END IF;
423 CTO_MSG_PUB.Count_And_Get
424 (p_msg_count => x_msg_count
425 ,p_msg_data => x_msg_data
426 );
427 raise;
428
429
430 when OTHERS then
431 x_msg_data := 'CTOFLSCB.cto_create_fs: ' ||
432 to_char(l_stmt_num) || ':' ||
433 substrb(sqlerrm, 1, 100);
434 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436 cto_msg_pub.cto_message('BOM', x_msg_name);
437 IF PG_DEBUG <> 0 THEN
438 oe_debug_pub.add('cto_create_fs: ' || x_msg_data,1);
439 END IF;
440 CTO_MSG_PUB.Count_And_Get
441 (p_msg_count => x_msg_count
442 ,p_msg_data => x_msg_data
443 );
444 raise;
445
446 END cto_create_fs;
447
448 PROCEDURE cto_schedule_fs (p_config_line_id IN varchar2,
449 p_wip_entity_id IN NUMBER,
450 x_return_status OUT NoCopy VARCHAR2,
451 x_msg_count OUT NoCopy NUMBER,
452 x_msg_data OUT NoCopy VARCHAR2,
453 x_msg_name out NoCopy varchar2 )
454
455 IS
456
457 l_stmt_num NUMBER := 0;
458 l_x_msg_count NUMBER;
459 l_x_msg_data VARCHAR2(240);
460 l_x_return_status VARCHAR2(1);
461 l_line_id NUMBER;
462 l_org_id NUMBER;
463 l_ship_date DATE;
464 l_rule_id NUMBER;
465 l_planned_qty NUMBER;
466 l_config_item_id NUMBER;
467 l_completion_date DATE;
468 --SCHEDULE_FLOW_SCHED_ERROR exception;
469
470 BEGIN
471
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473
474 l_stmt_num := 100;
475 -- Verify SQL.
476 select wfs.line_id, wfs.organization_id, oel.schedule_ship_date,
477 wfs.primary_item_id, wfs.planned_quantity
478 into l_line_id, l_org_id, l_ship_date, l_config_item_id, l_planned_qty
479 from wip_flow_schedules wfs,
480 oe_order_lines_all oel
481 where oel.line_id = to_number(p_config_line_id)
482 and wfs.organization_id = oel.ship_from_org_id
483 and wfs.wip_entity_id = p_wip_entity_id
484 and scheduled_flag = 3;
485
486 l_stmt_num := 110;
487
488 --
489 -- bugfix 2306314: Corrected the logic. Instead of checking rule_id is null in an IF condn,
490 -- we shoud check for no-data-found
491 --
492 begin
493 select rule_id
494 into l_rule_id
495 from mrp_scheduling_rules
496 where default_flag = 'Y'
497 and rownum = 1;
498
499 exception
500 when no_data_found then
501 -- No Default Rule so cannot schedule a flow schedule
502 IF PG_DEBUG <> 0 THEN
503 oe_debug_pub.add ('cto_schedule_fs: ' || 'Error: Default Rule does not exist. Cannot schedule a flow schedule.',1);
504 END IF;
505 raise FND_API.G_EXC_ERROR;
506
507 end;
508
509 IF PG_DEBUG <> 0 THEN
510 oe_debug_pub.add('cto_schedule_fs: ' || 'Rule ID: ' || to_char(l_rule_id),1);
511
512 oe_debug_pub.add('cto_schedule_fs: ' || 'Line ID: ' || to_char(l_line_id),1);
513
514 oe_debug_pub.add('cto_schedule_fs: ' || 'Order Line ID: ' || p_config_line_id,2);
515
516 oe_debug_pub.add('cto_schedule_fs: ' || 'WIP Entity ID: ' || to_char(p_wip_entity_id),2);
517 END IF;
518
519 l_stmt_num := 115;
520 l_completion_date := MRP_FLOW_SCHEDULE_PUB.get_first_unit_completion_date(
521 p_api_version_number => 1.0,
522 x_return_status => l_x_return_status,
523 x_msg_count => l_x_msg_count,
524 x_msg_data => l_x_msg_data,
525 p_org_id => l_org_id,
526 p_item_id => l_config_item_id,
527 p_qty => l_planned_qty,
528 p_line_id => l_line_id,
529 p_start_date => sysdate);
530
531 if (x_return_status = FND_API.G_RET_STS_ERROR) then
532 IF PG_DEBUG <> 0 THEN
533 oe_debug_pub.add('cto_schedule_fs: ' || 'Expected error in Get Completion Date with status ' || l_x_return_status,1);
534 END IF;
535 raise FND_API.G_EXC_ERROR;
536
537 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
538 IF PG_DEBUG <> 0 THEN
539 oe_debug_pub.add('cto_schedule_fs: ' || 'UnExpected error in Get Completion Date with status ' || l_x_return_status,1);
540 END IF;
541 raise FND_API.G_EXC_UNEXPECTED_ERROR;
542
543 end if;
544
545
546 IF PG_DEBUG <> 0 THEN
547 oe_debug_pub.add('cto_schedule_fs: ' || 'Success in getting completion date: Completion Date is '
548 || to_char(l_completion_date), 1);
549 END IF;
550
551 if (trunc(l_completion_date) > trunc(l_ship_date)) then
552
553 IF PG_DEBUG <> 0 THEN
554 oe_debug_pub.add('cto_schedule_fs: ' || 'Completion date is greater than ship date.',1);
555
556 oe_debug_pub.add('cto_schedule_fs: ' || 'Completion date is: ' || to_char(l_completion_date), 1);
557
558 oe_debug_pub.add('cto_schedule_fs: ' || 'Ship Date is: ' || to_char(l_ship_date),1);
559 END IF;
560
561 x_msg_name := 'CTO_LATE_COMPLETION_DATE';
562 raise FND_API.G_EXC_ERROR;
563
564 end if;
565
566
567 l_stmt_num := 120;
568 MRP_FLOW_SCHEDULE_PUB.Line_Schedule(
569 p_api_version_number => 1.0,
570 x_return_status => l_x_return_status,
571 x_msg_count => l_x_msg_count,
572 x_msg_data => l_x_msg_data,
573 p_rule_id => l_rule_id,
574 p_line_id => l_line_id,
575 p_org_id => l_org_id,
576 p_sched_start_date => l_completion_date,
577 p_sched_end_date => l_ship_date,
578 p_update => 1.0);
579
580 if (x_return_status = FND_API.G_RET_STS_ERROR) then
581 IF PG_DEBUG <> 0 THEN
582 oe_debug_pub.add('cto_schedule_fs: ' || 'Expected error in MRP_FLOW_SCHEDULE_PUB.Line Schedule with status ' || l_x_return_status,1);
583 END IF;
584 raise FND_API.G_EXC_ERROR;
585
586 elsif (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
587 IF PG_DEBUG <> 0 THEN
588 oe_debug_pub.add('cto_schedule_fs: ' || 'UnExpected error in MRP_FLOW_SCHEDULE_PUB.Line Schedule with status ' || l_x_return_status,1);
589 END IF;
590 raise FND_API.G_EXC_UNEXPECTED_ERROR;
591
592 else
593 IF PG_DEBUG <> 0 THEN
594 oe_debug_pub.add('cto_schedule_fs: ' || 'Success in MRP_FLOW_SCHEDULE_PUB.Line_Schedule.', 1);
595 END IF;
596 end if;
597
598 EXCEPTION
599
600 WHEN FND_API.G_EXC_ERROR THEN
601
602 x_msg_data := 'CTOFLSCB.cto_schedule_fs raised exp error: ' || to_char(l_stmt_num) || ':' || l_x_msg_data;
603 if x_msg_name is null then
604 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
605 end if;
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 cto_msg_pub.cto_message('BOM', x_msg_name);
608 IF PG_DEBUG <> 0 THEN
609 oe_debug_pub.add('cto_schedule_fs: ' || x_msg_data, 1);
610 END IF;
611 CTO_MSG_PUB.Count_And_Get
612 (p_msg_count => x_msg_count
613 ,p_msg_data => x_msg_data
614 );
615
616
617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
618
619 x_msg_data := 'CTOFLSCB.cto_schedule_fs raised unexp error: ' || to_char(l_stmt_num) || ':' || l_x_msg_data;
620 if x_msg_name is null then
621 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
622 end if;
623 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
624 cto_msg_pub.cto_message('BOM', x_msg_name);
625 IF PG_DEBUG <> 0 THEN
626 oe_debug_pub.add('cto_schedule_fs: ' || x_msg_data, 1);
627 END IF;
628 CTO_MSG_PUB.Count_And_Get
629 (p_msg_count => x_msg_count
630 ,p_msg_data => x_msg_data
631 );
632
633
634 when OTHERS then
635 x_msg_data := 'CTOFLSCB.cto_schedule_fs: ' || to_char(l_stmt_num) || ':'
636 || substrb(sqlerrm, 1, 100);
637 if x_msg_name is null then
638 x_msg_name := 'CTO_CREATE_FLOW_SCHED_ERROR';
639 end if;
640 IF PG_DEBUG <> 0 THEN
641 oe_debug_pub.add('cto_schedule_fs: ' || x_msg_data,1);
642 END IF;
643 cto_msg_pub.cto_message('BOM', x_msg_name);
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645 CTO_MSG_PUB.Count_And_Get
646 (p_msg_count => x_msg_count
647 ,p_msg_data => x_msg_data
648 );
649
650 END cto_schedule_fs;
651
652 end CTO_FLOW_SCHEDULE;