[Home] [Help]
PACKAGE BODY: APPS.FLM_SEQ_UI
Source
1 PACKAGE BODY flm_seq_ui AS
2 /* $Header: FLMSQUIB.pls 120.2.12010000.2 2008/09/04 15:21:34 adasa ship $ */
3
4
5 /******************************************************************
6 * To delete a task and its details in FLM_SEQ_* tables *
7 ******************************************************************/
8 PROCEDURE delete_tasks(p_seq_task_id IN NUMBER,
9 p_init_msg_list IN VARCHAR2,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_count OUT NOCOPY NUMBER,
12 x_msg_data OUT NOCOPY VARCHAR2)
13 IS
14 l_debug_line NUMBER;
15 BEGIN
16 SAVEPOINT flm_delete_tasks;
17 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
18 THEN
19 FND_MSG_PUB.initialize;
20 END IF;
21
22 x_return_status := FND_API.G_RET_STS_SUCCESS;
23
24 l_debug_line := 10;
25 DELETE FROM FLM_SEQ_TASK_EXCEPTIONS
26 WHERE SEQ_TASK_ID = p_seq_task_id;
27
28 l_debug_line := 20;
29 DELETE FROM FLM_SEQ_TASK_CONSTRAINTS
30 WHERE SEQ_TASK_ID = p_seq_task_id;
31
32 l_debug_line := 30;
33 DELETE FROM FLM_SEQ_TASK_DEMANDS
34 WHERE SEQ_TASK_ID = p_seq_task_id;
35
36 l_debug_line := 40;
37 DELETE FROM FLM_SEQ_TASK_LINES
38 WHERE SEQ_TASK_ID = p_seq_task_id;
39
40 l_debug_line := 50;
41 DELETE FROM FLM_FILTER_CRITERIA
42 WHERE CRITERIA_GROUP_ID = (SELECT DEMAND_CRITERIA_GROUP_ID FROM FLM_SEQ_TASKS WHERE SEQ_TASK_ID = p_seq_task_id);
43
44 l_debug_line := 60;
45 DELETE FROM FLM_SEQ_TASKS
46 WHERE SEQ_TASK_ID = p_seq_task_id;
47
48 l_debug_line := 70;
49
50 EXCEPTION
51 WHEN OTHERS THEN
52 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
53 ROLLBACK TO flm_delete_tasks;
54
55 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
56 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_tasks('||l_debug_line||')');
57 END IF;
58
59 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
60
61 END delete_tasks;
62
63 /*****************************************************************************************
64 * To delete a task and its details in FLM_SEQ_* tables. After that it commits *
65 *****************************************************************************************/
66 PROCEDURE delete_tasks_commit(p_seq_task_id IN NUMBER,
67 p_init_msg_list IN VARCHAR2,
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2)
71 IS
72 PRAGMA AUTONOMOUS_TRANSACTION;
73 l_debug_line NUMBER;
74 BEGIN
75 SAVEPOINT flm_delete_tasks_commit;
76 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
77 THEN
78 FND_MSG_PUB.initialize;
79 END IF;
80
81 x_return_status := FND_API.G_RET_STS_SUCCESS;
82
83 l_debug_line := 10;
84 delete_tasks(p_seq_task_id,'F', x_return_status, x_msg_count, x_msg_data);
85 l_debug_line := 20;
86
87 commit;
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
92 ROLLBACK TO flm_delete_tasks_commit;
93
94 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
95 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_tasks_commit('||l_debug_line||')');
96 END IF;
97
98 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
99
100 END delete_tasks_commit;
101
102
103
104
105
106
107
108 /******************************************************************
109 * To calculate available capacity of a given line for a given *
110 * period of time (p_start_date, p_end_date) inclusively; *
111 * the line is represented by (start, stop, hourly_rate) *
112 ******************************************************************/
113 /*fix bug#3827600
114 For counting the number of days in between, CALENDAR timezone is used
115 while for couting the exclude hours, CLIENT timezone is used.
116 The justification of the use of CLIENT timezone is that
117 the existing code has a lot of logic based on time range between 0 and 24.
118 */
119 PROCEDURE line_available_capacity(p_organization_id IN NUMBER,
120 p_start_time IN NUMBER,
121 p_stop_time IN NUMBER,
122 p_hourly_rate IN NUMBER,
123 p_start_date IN DATE,
124 p_end_date IN DATE,
125 p_init_msg_list IN VARCHAR2,
126 x_capacity OUT NOCOPY NUMBER,
127 x_return_status OUT NOCOPY VARCHAR2,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2
130 )
131 IS
132 l_capacity NUMBER := 0;
133 l_days NUMBER;
134 l_start_date DATE;
135 l_end_date DATE;
136
137 l_start_day DATE;
138 l_end_day DATE;
139
140 l_start_time NUMBER;
141 l_end_time NUMBER;
142
143 l_start_exclude NUMBER;
144 l_end_exclude NUMBER;
145 l_working_hours NUMBER;
146
147 l_debug_line NUMBER;
148
149 l_temp_date DATE; --fix bug#3170105
150
151 --fix bug#3827600
152 --Added new additional variables.
153 l_in_start_time NUMBER;
154 l_in_stop_time NUMBER;
155 --end of fix bug#3827600
156
157 FUNCTION get_excluded_hours(p_line_start IN NUMBER,
158 p_line_stop IN NUMBER,
159 p_start IN NUMBER,
160 p_end IN NUMBER) RETURN NUMBER
161 IS
162 l_start NUMBER;
163 l_end NUMBER;
164 l_return NUMBER;
165 BEGIN
166
167 if (p_line_start < p_start) then
168 l_start := p_start;
169 else
170 l_start := p_line_start;
171 end if;
172
173 if (p_line_stop < p_end) then
174 l_end := p_line_stop;
175 else
176 l_end := p_end;
177 end if;
178
179 l_return := l_end - l_start;
180
181 if (l_return < 0) then
182 l_return := 0;
183 end if;
184
185 return l_return;
186
187 END get_excluded_hours;
188
189 BEGIN
190 SAVEPOINT flm_line_available_capacity;
191 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
192 THEN
193 FND_MSG_PUB.initialize;
194 END IF;
195
196 l_debug_line := 10;
197 x_return_status := FND_API.G_RET_STS_SUCCESS;
198
199 --fix bug#3170105
200 flm_timezone.init_timezone(p_organization_id);
201
202 l_start_date := p_start_date;
203 l_end_date := p_end_date;
204
205 l_debug_line := 20;
206 l_start_day := MRP_CALENDAR.NEXT_WORK_DAY(p_organization_id,
207 1,
208 flm_timezone.server_to_calendar(l_start_date));
209
210 l_debug_line := 30;
211 l_end_day := MRP_CALENDAR.PREV_WORK_DAY(p_organization_id,
212 1,
213 flm_timezone.server_to_calendar(l_end_date));
214
215 if (l_start_day = flm_timezone.server_to_calendar(l_start_date)) then
216 --fix bug#3872600: Modified to get time component in client timezone
217 l_temp_date := flm_timezone.server_to_client(l_start_date);
218 l_start_time := to_char(l_temp_date, 'SSSSS');
219 else
220 l_start_time := -1;
221 end if;
222
223 if (l_end_day = flm_timezone.server_to_calendar(l_end_date)) then
224 --fix bug#3872600: Modified to get time component in client timezone
225 l_temp_date := flm_timezone.server_to_client(l_end_date);
226 l_end_time := to_char(l_temp_date, 'SSSSS');
227 else
228 l_end_time := -1;
229 end if;
230
231 l_debug_line := 40;
232 if (l_end_day < l_start_day) then
233 l_capacity := 0;
234 else
235 l_days := MRP_CALENDAR.DAYS_BETWEEN(p_organization_id,
236 1,
237 l_start_day,
238 l_end_day);
239 l_days := l_days + 1;
240
241 l_debug_line := 50;
242
243 --fix bug#3872600: Modified to get client version of the passed start and end time
244 l_temp_date := trunc(sysdate) + (p_start_time/86400);
245 l_temp_date := flm_timezone.server_to_client(l_temp_date);
246 l_in_start_time := to_char(l_temp_date, 'SSSSS');
247
248 l_temp_date := trunc(sysdate) + (p_stop_time/86400);
249 l_temp_date := flm_timezone.server_to_client(l_temp_date);
250 l_in_stop_time := to_char(l_temp_date, 'SSSSS');
251
252 l_debug_line := 60;
253 l_working_hours := (l_in_stop_time - l_in_start_time)/3600;
254
255 if l_working_hours <= 0 then
256 l_working_hours := l_working_hours + 24;
257 end if;
258
259 if (l_start_time = -1) then
260 l_start_exclude := 0;
261 elsif (l_start_time = 0) then
262 l_start_exclude := 0;
263 else
264 if (l_in_start_time < l_in_stop_time) then
265 l_start_exclude := get_excluded_hours(l_in_start_time/3600,
266 l_in_stop_time/3600,
267 0, l_start_time/3600);
268 else
269 /*fix bug#3838351
270 In the case of line start > line stop,
271 call the get_excluded_hours twice:
272 1. for working hours: 0 to line_stop
273 2. for working hours: line_start to 24
274 */
275 l_start_exclude := get_excluded_hours(0,
276 l_in_stop_time/3600,
277 0, l_start_time/3600);
278
279 l_start_exclude := l_start_exclude +
280 get_excluded_hours(l_in_start_time/3600,
281 24,
282 0, l_start_time/3600);
283 end if;
284 end if;
285
286 if (l_end_time = -1) then
287 l_end_exclude := 0;
288 elsif (l_end_time = 0) then
289 l_end_exclude := l_working_hours;
290 else
291 --fix bug#3838351: Same explanation as above.
292 if (l_in_start_time < l_in_stop_time) then
293 l_end_exclude := get_excluded_hours(l_in_start_time/3600,
294 l_in_stop_time/3600,
295 l_end_time/3600, 24);
296 else
297 l_end_exclude := get_excluded_hours(0,
298 l_in_stop_time/3600,
299 l_end_time/3600, 24);
300
301 l_end_exclude := l_end_exclude +
302 get_excluded_hours(l_in_start_time/3600,
303 24,
304 l_end_time/3600, 24);
305 end if;
306 end if;
307 --end of fix bug#3170105
308
309 l_debug_line := 70;
310 l_capacity := p_hourly_rate* (l_days * l_working_hours - l_start_exclude - l_end_exclude);
311
312 end if;
313
314 x_capacity := l_capacity;
315 l_debug_line := 80;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 x_capacity := -1;
321 ROLLBACK TO flm_line_available_capacity;
322
323 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
324 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'flm_line_available_capacity('||l_debug_line||')');
325 END IF;
326
327 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
328
329 END line_available_capacity;
330
331
332 /**********************************************************************************
333 * To insert demand from MRP_UNSCHEDULED_ORDERS_V to FLM_SEQ_TASK_DEMANDS table. *
334 **********************************************************************************/
335 PROCEDURE insert_demands(p_seq_task_id IN NUMBER,
336 p_max_rows IN NUMBER,
337 p_init_msg_list IN VARCHAR2,
338 x_return_status OUT NOCOPY VARCHAR2,
339 x_msg_count OUT NOCOPY NUMBER,
340 x_msg_data OUT NOCOPY VARCHAR2)
341 IS
342 l_criteria_group_id NUMBER;
343 l_alternate_routing_flag VARCHAR2(1);
344 l_seq_task_type NUMBER;
345 l_line_id NUMBER;
346 l_org_id NUMBER;
347 l_demand_start_date VARCHAR2(100);
348 l_demand_end_date VARCHAR2(100);
349 l_planning_flag VARCHAR2(1); --Added for bugfix:7305721
350
351 l_where VARCHAR2(5000);
352 l_filter VARCHAR2(4000);
353 l_return_status VARCHAR2(30);
354 l_msg_count NUMBER;
355 l_msg_data VARCHAR2(1000);
356 l_cursor INTEGER;
357 l_dummy INTEGER;
358 l_cursor_insert VARCHAR2(5000);
359 l_debug_line NUMBER;
360
361 l_cursor_cnt VARCHAR2(1000);
362 l_quantity NUMBER;
363 l_count_rows NUMBER;
364
365 CURSOR line_list IS
366 SELECT LINE_ID
367 FROM FLM_SEQ_TASK_LINES
368 WHERE SEQ_TASK_ID = p_seq_task_id;
369 BEGIN
370 SAVEPOINT flm_insert_demands;
371 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
372 THEN
373 FND_MSG_PUB.initialize;
374 END IF;
375
376 l_debug_line := 10;
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 --Added Planning Recomendation flag for bug fix:7305721
380 SELECT NVL(DEMAND_CRITERIA_GROUP_ID,-1),NVL(ALTERNATE_ROUTING_FLAG,'N'),SEQ_TASK_TYPE, ORGANIZATION_ID,
381 TO_CHAR(DEMAND_START_DATE,'DD-MON-RR HH24:MI:SS'), TO_CHAR(DEMAND_END_DATE,'DD-MON-RR HH24:MI:SS'),
382 NVL(HONOR_PLANNING_FLAG,'N')
383 INTO l_criteria_group_id,l_alternate_routing_flag,l_seq_task_type,l_org_id,
384 l_demand_start_date,l_demand_end_date,l_planning_flag
385 FROM FLM_SEQ_TASKS
386 WHERE SEQ_TASK_ID = p_seq_task_id;
387
388 l_debug_line := 20;
389 IF l_seq_task_type = FLM_CONSTANTS.SEQ_TASK_RESEQ THEN
390 x_return_status := FND_API.G_RET_STS_SUCCESS;
391 return;
392 END IF;
393
394 l_debug_line := 30;
395 FOR line_list_rec IN line_list LOOP
396 l_line_id := line_list_rec.line_id;
397 l_where := NULL;
398 l_filter := NULL;
399 l_cursor_insert := NULL;
400 l_cursor_cnt := NULL;
401
402 FLM_Util.init_bind;
403
404 IF (l_seq_task_type = FLM_CONSTANTS.SEQ_TASK_SO AND l_alternate_routing_flag = 'N') THEN
405 l_where := 'FROM FLM_SEQ_DEMAND_SALES_ORDERS_V MUOV WHERE '||
406 'MUOV.ALTERNATE_ROUTING_DESIGNATOR IS NULL AND ';
407 ELSIF (l_seq_task_type = FLM_CONSTANTS.SEQ_TASK_SO AND l_alternate_routing_flag = 'Y') THEN
408 l_where := 'FROM FLM_SEQ_DEMAND_SALES_ORDERS_V MUOV WHERE ';
409 ELSE
410 l_where := 'FROM FLM_SEQ_DEMAND_PLAN_ORDERS_V MUOV WHERE ';
411 --Added for bugfix:7305721
412 IF(l_alternate_routing_flag = 'N') THEN
413 l_where := l_where || 'MUOV.ALTERNATE_ROUTING_DESIGNATOR IS NULL AND ';
414 END IF;
415 IF(l_planning_flag = 'Y') THEN
416 l_where := l_where || 'MUOV.RECO_LINE_ID = MUOV.LINE_ID AND ';
417 END IF;
418
419 END IF;
420
421
422 l_where := l_where || 'MUOV.LINE_ID = :l_line_id AND MUOV.ORGANIZATION_ID = :l_org_id AND ';
423 l_where := l_where || 'MUOV.ORDER_DATE BETWEEN TO_DATE(:l_demand_start_date,' ||
424 '''DD-MON-RR HH24:MI:SS'') AND TO_DATE(:l_demand_end_date,' ||
425 '''DD-MON-RR HH24:MI:SS'')';
426 l_where := l_where || ' AND MUOV.ORDER_QUANTITY > 0';
427
428 l_debug_line := 40;
429 flm_filter_criteria_process.get_filter_clause(l_criteria_group_id,'MUOV',NULL,l_filter,
430 l_return_status,l_msg_count,l_msg_data);
431 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
432 x_return_status := l_return_status;
433 x_msg_count := l_msg_count;
434 x_msg_data := l_msg_data;
435 return;
436 END IF;
437 IF l_filter IS NOT NULL THEN
438 l_where := l_where || ' AND ' || l_filter;
439 END IF;
440
441 l_debug_line := 50;
442 -- Count the rows and quantity
443 l_cursor_cnt := 'SELECT COUNT(*), SUM(ORDER_QUANTITY) '||l_where;
444 l_cursor := dbms_sql.open_cursor;
445 dbms_sql.parse(l_cursor, l_cursor_cnt, dbms_sql.v7);
446
447 FLM_Util.add_bind(':l_line_id', l_line_id);
448 FLM_Util.add_bind(':l_org_id', l_org_id);
449 FLM_Util.add_bind(':l_demand_start_date', l_demand_start_date);
450 FLM_Util.add_bind(':l_demand_end_date', l_demand_end_date);
451 FLM_Util.do_binds(l_cursor);
452
453 dbms_sql.define_column(l_cursor, 1, l_count_rows);
454 dbms_sql.define_column(l_cursor, 2, l_quantity);
455 l_debug_line := 55;
456 l_dummy := dbms_sql.execute(l_cursor);
457 IF (dbms_sql.fetch_rows(l_cursor) > 0) THEN
458 dbms_sql.column_value(l_cursor,1, l_count_rows);
459 dbms_sql.column_value(l_cursor,2, l_quantity);
460 END IF;
461 dbms_sql.close_cursor(l_cursor);
462
463 IF (p_max_rows IS NOT NULL AND p_max_rows <> -1 AND l_count_rows > p_max_rows) THEN
464 ROLLBACK TO flm_insert_demands;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466
467 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
468 FND_MESSAGE.SET_NAME('FLM','FLM_SEQ_DEMAND_EXCEEDED');
469 FND_MSG_PUB.Add;
470 END IF;
471
472 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
473 return;
474 END IF;
475
476 IF (p_max_rows IS NULL OR p_max_rows <> -1) THEN
477 l_cursor_insert := 'INSERT INTO FLM_SEQ_TASK_DEMANDS (SEQ_TASK_ID,ALTERNATE_ROUTING_DESIGNATOR,LINE_ID,'||
478 'DEMAND_ID,SPLIT_NUMBER,OBJECT_VERSION_NUMBER,ORGANIZATION_ID,PRIMARY_ITEM_ID,'||
479 'OPEN_QTY,REQUESTED_QTY,FULFILLED_QTY,'||
480 'CREATED_BY,CREATION_DATE,LAST_UPDATE_LOGIN,'||
481 'LAST_UPDATE_DATE,LAST_UPDATED_BY,REQUEST_ID,PROGRAM_ID,PROGRAM_APPLICATION_ID,'||
482 'PROGRAM_UPDATE_DATE) ';
483
484 l_cursor_insert := l_cursor_insert || 'SELECT :p_seq_task_id,MUOV.ALTERNATE_ROUTING_DESIGNATOR,'||
485 'MUOV.LINE_ID,MUOV.DEMAND_SOURCE_LINE,1,1,MUOV.ORGANIZATION_ID,'||
486 'MUOV.INVENTORY_ITEM_ID,MUOV.ORDER_QUANTITY,MUOV.ORDER_QUANTITY,0,'||
487 'fnd_global.user_id,sysdate,'||
488 'fnd_global.login_id,sysdate,fnd_global.user_id,fnd_global.conc_request_id,'||
489 'fnd_global.conc_program_id,fnd_global.prog_appl_id,sysdate ';
490
491 l_cursor_insert := l_cursor_insert || l_where;
492
493 l_debug_line := 60;
494 l_cursor := dbms_sql.open_cursor;
495 dbms_sql.parse(l_cursor, l_cursor_insert, dbms_sql.v7);
496
497 FLM_Util.add_bind(':p_seq_task_id', p_seq_task_id);
498 FLM_Util.do_binds(l_cursor);
499
500 l_dummy := dbms_sql.execute(l_cursor);
501 dbms_sql.close_cursor(l_cursor);
502 l_debug_line := 70;
503
504 END IF;
505
506 G_DEMAND_QTY(l_line_id) := l_quantity;
507 END LOOP;
508
509 EXCEPTION
510 WHEN OTHERS THEN
511 ROLLBACK TO flm_insert_demands;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513
514 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
515 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'insert_demands('||l_debug_line||')');
516 END IF;
517
518 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
519
520 END insert_demands;
521
522 /*****************************************************
523 * To get demand qty from G_DEMAND_QTY PL/SQL table. *
524 *****************************************************/
525 PROCEDURE get_demand_qty(p_line_id IN NUMBER,
526 p_init_msg_list IN VARCHAR2,
527 x_demand_qty OUT NOCOPY NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2)
531 IS
532 l_debug_line NUMBER;
533 BEGIN
534 SAVEPOINT flm_get_demand_qty;
535 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
536 THEN
537 FND_MSG_PUB.initialize;
538 END IF;
539
540 l_debug_line := 10;
541 x_demand_qty := G_DEMAND_QTY(p_line_id);
542
543 l_debug_line := 20;
544 EXCEPTION
545 WHEN OTHERS THEN
546 ROLLBACK TO flm_get_demand_qty;
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548
549 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
550 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'get_demand_qty('||l_debug_line||')');
551 END IF;
552
553 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
554
555 END get_demand_qty;
556
557
558 /*******************************************************
559 * To delete demands from FLM_SEQ_TASK_DEMANDS table. *
560 *******************************************************/
561 PROCEDURE delete_demands(p_seq_task_id IN NUMBER,
562 p_init_msg_list IN VARCHAR2,
563 x_return_status OUT NOCOPY VARCHAR2,
564 x_msg_count OUT NOCOPY NUMBER,
565 x_msg_data OUT NOCOPY VARCHAR2)
566 IS
567 l_debug_line NUMBER;
568 BEGIN
569 SAVEPOINT flm_delete_demands;
570 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
571 THEN
572 FND_MSG_PUB.initialize;
573 END IF;
574
575 l_debug_line := 10;
576 x_return_status := FND_API.G_RET_STS_SUCCESS;
577
578 DELETE FROM FLM_SEQ_TASK_DEMANDS WHERE SEQ_TASK_ID = p_seq_task_id;
579 l_debug_line := 20;
580
581 EXCEPTION
582 WHEN OTHERS THEN
583 ROLLBACK TO flm_delete_demands;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585
586 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
587 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_demands('||l_debug_line||')');
588 END IF;
589
590 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
591
592 END delete_demands;
593
594 /*******************************************************
595 * To delete criteria from FLM_FILTER_CRITERIA table. *
596 *******************************************************/
597 PROCEDURE delete_criteria(p_seq_task_id IN NUMBER,
598 p_init_msg_list IN VARCHAR2,
599 x_return_status OUT NOCOPY VARCHAR2,
600 x_msg_count OUT NOCOPY NUMBER,
601 x_msg_data OUT NOCOPY VARCHAR2)
602 IS
603 l_debug_line NUMBER;
604 l_criteria_group_id NUMBER;
605 BEGIN
606 SAVEPOINT flm_delete_criteria;
607 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
608 THEN
609 FND_MSG_PUB.initialize;
610 END IF;
611
612 l_debug_line := 10;
613 x_return_status := FND_API.G_RET_STS_SUCCESS;
614
615 l_debug_line := 20;
616 SELECT NVL(DEMAND_CRITERIA_GROUP_ID,-1)
617 INTO l_criteria_group_id
618 FROM FLM_SEQ_TASKS
619 WHERE SEQ_TASK_ID = p_seq_task_id;
620
621 l_debug_line := 30;
622 DELETE FROM FLM_FILTER_CRITERIA WHERE CRITERIA_GROUP_ID = l_criteria_group_id;
623
624 l_debug_line := 40;
625
626 EXCEPTION
627 WHEN OTHERS THEN
628 ROLLBACK TO flm_delete_criteria;
629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
630
631 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
632 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_criteria('||l_debug_line||')');
633 END IF;
634
635 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
636
637 END delete_criteria;
638
639 /***************************************************************************************
640 * To insert lines from WIP_LINES into FLM_SEQ_TASK_LINES and all constraints on the *
641 * line default rule from FLM_SEQ_TASK_CONSTRAINTS into FLM_SEQ_TASK_CONSTRAINTS. *
642 ***************************************************************************************/
643 PROCEDURE insert_line_constraints(p_seq_task_id IN NUMBER,
644 p_line_id IN NUMBER,
645 p_org_id IN NUMBER,
646 p_init_msg_list IN VARCHAR2,
647 x_return_status OUT NOCOPY VARCHAR2,
648 x_msg_count OUT NOCOPY NUMBER,
649 x_msg_data OUT NOCOPY VARCHAR2)
650 IS
651 l_debug_line NUMBER;
652 BEGIN
653 SAVEPOINT flm_insert_line_constraints;
654 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
655 THEN
656 FND_MSG_PUB.initialize;
657 END IF;
658
659 l_debug_line := 10;
660 x_return_status := FND_API.G_RET_STS_SUCCESS;
661
662 INSERT INTO FLM_SEQ_TASK_LINES (
663 SEQ_TASK_ID,
664 LINE_ID,
665 OBJECT_VERSION_NUMBER,
666 ORGANIZATION_ID,
667 SEQ_DIRECTION,
668 START_TIME,
669 STOP_TIME,
670 HOURLY_RATE,
671 CONNECT_FLAG,
672 FIX_SEQUENCE_TYPE,
673 FIX_SEQUENCE_AMOUNT,
674 COMBINE_SCHEDULE_FLAG,
675 AVAILABLE_CAPACITY,
676 RESEQUENCED_QTY,
677 RULE_ID,
678 CREATED_BY,
679 CREATION_DATE,
680 LAST_UPDATE_LOGIN,
681 LAST_UPDATE_DATE,
682 LAST_UPDATED_BY,
683 REQUEST_ID,
684 PROGRAM_ID,
685 PROGRAM_APPLICATION_ID,
686 PROGRAM_UPDATE_DATE )
687 SELECT
688 p_seq_task_id,
689 p_line_id,
690 1,
691 ORGANIZATION_ID,
692 NVL(SEQ_DIRECTION,1),
693 START_TIME,
694 STOP_TIME,
695 MAXIMUM_RATE,
696 NVL(SEQ_CONNECT_FLAG,'N'),
697 NVL(SEQ_FIX_SEQUENCE_TYPE,1),
698 SEQ_FIX_SEQUENCE_AMOUNT,
699 NVL(SEQ_COMBINE_SCHEDULE_FLAG,'N'),
700 0,
701 0,
702 SEQ_DEFAULT_RULE_ID,
703 fnd_global.user_id,
704 sysdate,
705 fnd_global.login_id,
706 sysdate,
707 fnd_global.user_id,
708 NULL,
709 NULL,
710 NULL,
711 NULL
712 FROM WIP_LINES
713 WHERE LINE_ID = p_line_id AND ORGANIZATION_ID = p_org_id;
714
715 INSERT INTO FLM_SEQ_TASK_CONSTRAINTS (
716 SEQ_TASK_ID,
717 LINE_ID,
718 PARENT_CONSTRAINT_NUMBER,
719 CONSTRAINT_NUMBER,
720 OBJECT_VERSION_NUMBER,
721 ORGANIZATION_ID,
722 PRIORITY,
723 CONSTRAINT_TYPE,
724 CONSTRAINT_TYPE_VALUE1,
725 CONSTRAINT_TYPE_VALUE2,
726 CONSTRAINT_TYPE_VALUE3,
727 ATTRIBUTE_ID,
728 ATTRIBUTE_VALUE1_NAME,
729 ATTRIBUTE_VALUE2_NAME,
730 ATTRIBUTE_VALUE1_NUM,
731 ATTRIBUTE_VALUE2_NUM,
732 ATTRIBUTE_VALUE1_DATE,
733 ATTRIBUTE_VALUE2_DATE,
734 FULFILLED_TO_QTY,
735 VIOLATION_COUNT,
736 CREATED_BY,
737 CREATION_DATE,
738 LAST_UPDATE_LOGIN,
739 LAST_UPDATE_DATE,
740 LAST_UPDATED_BY,
741 REQUEST_ID,
742 PROGRAM_ID,
743 PROGRAM_APPLICATION_ID,
744 PROGRAM_UPDATE_DATE )
745 SELECT
746 p_seq_task_id,
747 p_line_id,
748 C.PARENT_CONSTRAINT_NUMBER,
749 C.CONSTRAINT_NUMBER,
750 1,
751 C.ORGANIZATION_ID,
752 C.PRIORITY,
753 C.CONSTRAINT_TYPE,
754 C.CONSTRAINT_TYPE_VALUE1,
755 C.CONSTRAINT_TYPE_VALUE2,
756 C.CONSTRAINT_TYPE_VALUE3,
757 C.ATTRIBUTE_ID,
758 C.ATTRIBUTE_VALUE1_NAME,
759 C.ATTRIBUTE_VALUE2_NAME,
760 C.ATTRIBUTE_VALUE1_NUM,
761 C.ATTRIBUTE_VALUE2_NUM,
762 C.ATTRIBUTE_VALUE1_DATE,
763 C.ATTRIBUTE_VALUE2_DATE,
764 0,
765 0,
766 fnd_global.user_id,
767 sysdate,
768 fnd_global.login_id,
769 sysdate,
770 fnd_global.user_id,
771 NULL,
772 NULL,
773 NULL,
774 NULL
775 FROM FLM_SEQ_RULE_CONSTRAINTS C, WIP_LINES L
776 WHERE C.RULE_ID = L.SEQ_DEFAULT_RULE_ID
777 AND L.LINE_ID = p_line_id
778 AND L.ORGANIZATION_ID = p_org_id;
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 ROLLBACK TO flm_insert_line_constraints;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784
785 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
786 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'insert_line_constraints('||l_debug_line||')');
787 END IF;
788
789 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
790
791 END insert_line_constraints;
792
793 /*****************************************************************************************************
794 * To delete lines from FLM_SEQ_TASK_LINES and line contraints from FLM_SEQ_TASK_CONSTRAINTS table. *
795 *****************************************************************************************************/
796 PROCEDURE delete_line_constraints(p_seq_task_id IN NUMBER,
797 p_init_msg_list IN VARCHAR2,
798 x_return_status OUT NOCOPY VARCHAR2,
799 x_msg_count OUT NOCOPY NUMBER,
800 x_msg_data OUT NOCOPY VARCHAR2)
801 IS
802 l_debug_line NUMBER;
803 BEGIN
804 SAVEPOINT flm_delete_line_constraints;
805 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
806 THEN
807 FND_MSG_PUB.initialize;
808 END IF;
809
810 l_debug_line := 10;
811 x_return_status := FND_API.G_RET_STS_SUCCESS;
812
813 DELETE FROM FLM_SEQ_TASK_LINES WHERE SEQ_TASK_ID = p_seq_task_id;
814 l_debug_line := 20;
815
816 DELETE FROM FLM_SEQ_TASK_CONSTRAINTS WHERE SEQ_TASK_ID = p_seq_task_id;
817 l_debug_line := 30;
818
819 EXCEPTION
820
821 WHEN OTHERS THEN
822 ROLLBACK TO flm_delete_line_constraints;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
824
825 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
826 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'delete_line_constraints('||l_debug_line||')');
827 END IF;
828
829 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
830
831 END delete_line_constraints;
832
833 /******************************************************************
834 * To get min wip_entity_id from WIP_FLOW_SCHEDULES PL/SQL table. *
835 ******************************************************************/
836 PROCEDURE get_min_wip_entity_id(p_start_date IN DATE,
837 p_org_id IN NUMBER,
838 p_init_msg_list IN VARCHAR2,
839 x_wip_entity_id OUT NOCOPY NUMBER,
840 x_return_status OUT NOCOPY VARCHAR2,
841 x_msg_count OUT NOCOPY NUMBER,
842 x_msg_data OUT NOCOPY VARCHAR2)
843 IS
844 l_debug_line NUMBER;
845 BEGIN
846 SAVEPOINT flm_get_min_wip_entity_id;
847 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
848 THEN
849 FND_MSG_PUB.initialize;
850 END IF;
851
852 l_debug_line := 10;
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854
855 SELECT min(wip_entity_id)
856 INTO x_wip_entity_id
857 FROM WIP_FLOW_SCHEDULES
858 WHERE scheduled_completion_date >= flm_timezone.client00_in_server(p_start_date) --fix bug#3170105
859 AND organization_id = p_org_id;
860
861 l_debug_line := 20;
862 EXCEPTION
863 WHEN OTHERS THEN
864 ROLLBACK TO flm_get_min_wip_entity_id;
865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
866
867 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
868 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'get_min_wip_entity_id('||l_debug_line||')');
869 END IF;
870
871 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
872
873 END get_min_wip_entity_id;
874
875 /*********************************************************************
876 * To clean up the raw UI data *
877 *********************************************************************/
878 PROCEDURE data_cleanup( p_init_msg_list IN VARCHAR2,
879 x_return_status OUT NOCOPY VARCHAR2,
880 x_msg_count OUT NOCOPY NUMBER,
881 x_msg_data OUT NOCOPY VARCHAR2)
882 IS
883 CURSOR l_task_cursor IS
884 SELECT seq_task_id
885 FROM FLM_SEQ_TASKS
886 WHERE seq_request_id = -1
887 AND creation_date < sysdate-2;
888
889 l_debug_line NUMBER;
890 l_return_status VARCHAR2(1000);
891 l_msg_count NUMBER;
892 l_msg_data VARCHAR2(1000);
893 BEGIN
894
895 SAVEPOINT flm_data_cleanup;
896 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
897 THEN
898 FND_MSG_PUB.initialize;
899 END IF;
900
901 l_debug_line := 10;
902 x_return_status := FND_API.G_RET_STS_SUCCESS;
903
904 FOR l_task_rec IN l_task_cursor LOOP
905 delete_tasks(l_task_rec.seq_task_id, 'F', l_return_status, l_msg_count, l_msg_data);
906 END LOOP;
907 l_debug_line := 20;
908
909 EXCEPTION
910 WHEN OTHERS THEN
911 ROLLBACK TO flm_data_cleanup;
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913
914 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
915 FND_MSG_PUB.Add_Exc_Msg ('flm_seq_ui' ,'data_cleanup('||l_debug_line||')');
916 END IF;
917
918 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
919
920 END data_cleanup;
921
922 /*****
923 * Get the flag that indicates whether Flow Sequencing
924 * is enabled, which is defined by the 'FLM_ENABLE_FLMSEQ'
925 * profile.
926 *****/
927 FUNCTION Get_FlmSeq_Enabled_Flag RETURN VARCHAR2
928 IS
929 flmseq_enabled_prf_value VARCHAR2(1) := 'N';
930 BEGIN
931 flmseq_enabled_prf_value := fnd_profile.value('FLM_ENABLE_FLMSEQ');
932 IF flmseq_enabled_prf_value = 'Y' THEN
933 RETURN flmseq_enabled_prf_value;
934 END IF;
935 RETURN 'N';
936 END Get_FlmSeq_Enabled_Flag;
937
938 /*****
939 * Determines whether Flow Sequencing is licensed. Flow Sequencing
940 * is 'licensed' if:
941 * (1) Flow Manufacturing installed; and
942 * (2) Flow Sequencing is enabled.
943 *****/
944 FUNCTION Get_FlmSeq_Licensed RETURN VARCHAR2
945 IS
946 flm_licensed VARCHAR2(1) := 'N';
947 flmseq_enabled_prf_value VARCHAR2(1) := 'N';
948 BEGIN
949 flm_licensed := flm_util.Get_Install_Status();
950 flmseq_enabled_prf_value := flm_seq_ui.Get_FlmSeq_Enabled_Flag();
951
952 IF flm_licensed = 'I' and flmseq_enabled_prf_value = 'Y'
953 THEN
954 RETURN 'Y';
955 END IF;
956
957 RETURN 'N';
958 END Get_FlmSeq_Licensed;
959
960
961 END flm_seq_ui;