[Home] [Help]
PACKAGE BODY: APPS.WPS_CAPACITY_CHANGES_PKG
Source
1 PACKAGE BODY WPS_CAPACITY_CHANGES_PKG AS
2 /* $Header: WPSCAPCB.pls 115.4 2002/12/20 23:36:44 sjchen ship $ */
3
4
5 /**
6 * This procedure is used to delete a resource exception
7 * This has a cascading effect, any attached instances will also be deleted
8 */
9 PROCEDURE Delete_Resource_Exception(X_Rowid VARCHAR2) is
10
11 l_department_id NUMBER;
12 l_resource_id NUMBER;
13 l_shift_num NUMBER;
14 l_action_type NUMBER;
15 l_simulation_set VARCHAR2(10);
16 l_from_date DATE;
17 l_instance_count NUMBER;
18
19 PRAGMA AUTONOMOUS_TRANSACTION;
20
21 BEGIN
22
23 SELECT DEPARTMENT_ID,RESOURCE_ID,SHIFT_NUM,FROM_DATE,
24 SIMULATION_SET,ACTION_TYPE
25 INTO l_department_id,l_resource_id,l_shift_num,l_from_date,
26 l_simulation_set,l_action_type
27 FROM BOM_RESOURCE_CHANGES
28 WHERE ROWID = X_Rowid;
29
30 SELECT COUNT(*) INTO l_instance_count
31 FROM BOM_RES_INSTANCE_CHANGES
32 WHERE DEPARTMENT_ID = l_department_id AND
33 RESOURCE_ID = l_resource_id AND
34 SHIFT_NUM = l_shift_num AND
35 FROM_DATE = l_from_date AND
36 SIMULATION_SET = l_simulation_set AND
37 ACTION_TYPE = l_action_type;
38
39 if( l_instance_count <> 0 ) then
40 DELETE FROM BOM_RES_INSTANCE_CHANGES
41 WHERE DEPARTMENT_ID = l_department_id AND
42 RESOURCE_ID = l_resource_id AND
43 SHIFT_NUM = l_shift_num AND
44 FROM_DATE = l_from_date AND
45 SIMULATION_SET = l_simulation_set AND
46 ACTION_TYPE = l_action_type;
47
48 end if;
49
50 DELETE FROM BOM_RESOURCE_CHANGES
51 WHERE rowid = X_Rowid;
52
53 COMMIT;
54
55 END Delete_Resource_Exception;
56
57
58 /**
59 * This procedure is used to delete a resource instance exception
60 * deleting instance exception will decrease the
61 * resource exception capacity units by 1
62 * If capacity change reaches 0, the resource exception will also be deleted
63 */
64
65 PROCEDURE Delete_Resinst_Exception(X_Rowid VARCHAR2) is
66
67 l_department_id NUMBER;
68 l_resource_id NUMBER;
69 l_shift_num NUMBER;
70 l_action_type NUMBER;
71 l_simulation_set VARCHAR2(10);
72 l_from_date DATE;
73 l_to_date DATE;
74 l_capacity_units NUMBER;
75 l_from_time NUMBER;
76 l_to_time NUMBER;
77 l_sch_instance NUMBER;
78 l_record_exists NUMBER;
79
80 PRAGMA AUTONOMOUS_TRANSACTION;
81
82 BEGIN
83
84 SELECT DEPARTMENT_ID,RESOURCE_ID,SHIFT_NUM,FROM_DATE,TO_DATE,
85 SIMULATION_SET,ACTION_TYPE,FROM_TIME,TO_TIME
86 INTO l_department_id,l_resource_id,l_shift_num,l_from_date,
87 l_to_date,l_simulation_set,l_action_type,l_from_time,
88 l_to_time
89 FROM BOM_RES_INSTANCE_CHANGES
90 WHERE ROWID = X_Rowid;
91
92 --Get the schedule to instance flag for the department resource
93 SELECT SCHEDULE_TO_INSTANCE
94 INTO l_sch_instance
95 FROM BOM_DEPARTMENT_RESOURCES
96 WHERE DEPARTMENT_ID = l_department_id AND
97 RESOURCE_ID = l_resource_id;
98
99 --Schedule_to_instance: 1 means resource is scheduled to instance
100 --Schedule_to_instance: 2 means resource is not scheduled to instance
101 l_record_exists := 0;
102
103 --Make the deletion at resource instance level consistent no matter
104 --the resource is scheduled to instance or not
105
106 --if(l_sch_instance = 1) then
107 SELECT COUNT(*) INTO l_record_exists
108 FROM BOM_RESOURCE_CHANGES
109 WHERE DEPARTMENT_ID = l_department_id AND
110 RESOURCE_ID = l_resource_id AND
111 SHIFT_NUM = l_shift_num AND
112 FROM_DATE = l_from_date AND
113 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
114 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
115 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
116 SIMULATION_SET = l_simulation_set AND
117 ACTION_TYPE = l_action_type;
118 --end if;
119
120 if (l_record_exists <> 0) then
121 SELECT CAPACITY_CHANGE INTO l_capacity_units
122 FROM BOM_RESOURCE_CHANGES
123 WHERE DEPARTMENT_ID = l_department_id AND
124 RESOURCE_ID = l_resource_id AND
125 SHIFT_NUM = l_shift_num AND
126 FROM_DATE = l_from_date AND
127 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
128 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
129 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
130 SIMULATION_SET = l_simulation_set AND
131 ACTION_TYPE = l_action_type;
132
133 if( (l_capacity_units > 1) OR (l_capacity_units < -1) ) then
134 if(l_capacity_units > 1 ) then
135 l_capacity_units := l_capacity_units -1;
136 elsif (l_capacity_units < 1) then
137 l_capacity_units := l_capacity_units - (-1);
138 end if;
139
140 UPDATE BOM_RESOURCE_CHANGES
141 SET CAPACITY_CHANGE = l_capacity_units
142 WHERE DEPARTMENT_ID = l_department_id AND
143 RESOURCE_ID = l_resource_id AND
144 SHIFT_NUM = l_shift_num AND
145 FROM_DATE = l_from_date AND
146 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
147 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
148 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
149 SIMULATION_SET = l_simulation_set AND
150 ACTION_TYPE = l_action_type;
151
152 elsif( (l_capacity_units = 1) OR (l_capacity_units = -1)) then
153 DELETE FROM BOM_RESOURCE_CHANGES
154 WHERE DEPARTMENT_ID = l_department_id AND
155 RESOURCE_ID = l_resource_id AND
156 SHIFT_NUM = l_shift_num AND
157 FROM_DATE = l_from_date AND
158 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
159 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
160 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
161 SIMULATION_SET = l_simulation_set AND
162 ACTION_TYPE = l_action_type;
163 end if;
164 end if;
165
166 DELETE FROM BOM_RES_INSTANCE_CHANGES
167 WHERE rowid = X_Rowid;
168
169 COMMIT;
170
171 END Delete_Resinst_Exception;
172
173
174 /**
175 * This procedure is used to update a resource exception
176 * updating a resource exception would update all the attached
177 * instances too
178 */
179
180 PROCEDURE Update_Resource_Exception(X_Rowid VARCHAR2,
181 X_Shift NUMBER,
182 X_Action NUMBER,
183 X_Units NUMBER,
184 X_From_Date DATE,
185 X_To_Date DATE,
186 X_From_Time NUMBER,
187 X_To_Time NUMBER,
188 X_User_Id NUMBER,
189 X_REASON_CODE VARCHAR2 DEFAULT NULL) is
190
191
192 l_rowid VARCHAR2(30);
193 l_shift_num NUMBER;
194 l_action_type NUMBER;
195 l_units NUMBER;
196 l_from_date DATE;
197 l_to_date DATE;
198 l_from_time NUMBER;
199 l_to_time NUMBER;
200 l_user_id NUMBER;
201
202 t_resource_id NUMBER;
203 t_department_id NUMBER;
204 t_simulation_set VARCHAR2(10);
205 t_shift_num NUMBER;
206 t_action_type NUMBER;
207 t_units NUMBER;
208 t_from_date DATE;
209 t_to_date DATE;
210 t_from_time NUMBER;
211 t_to_time NUMBER;
212
213
214 PRAGMA AUTONOMOUS_TRANSACTION;
215
216 BEGIN
217 l_rowid := X_Rowid;
218 l_shift_num := X_Shift;
219 l_action_type := X_Action;
220 l_units := X_Units;
221 l_from_date := X_From_Date;
222 l_to_date := X_To_Date;
223 l_from_time := X_From_Time;
224 l_to_time := X_To_Time;
225 l_user_id := X_User_Id;
226
227 if (l_action_type = 0) then
228 l_action_type := 2;
229 end if;
230
231 SELECT
232 RESOURCE_ID, DEPARTMENT_ID, SIMULATION_SET, SHIFT_NUM,
233 ACTION_TYPE, CAPACITY_CHANGE, FROM_DATE,
234 TO_DATE, FROM_TIME, TO_TIME
235 INTO
236 t_resource_id, t_department_id, t_simulation_set, t_shift_num,
237 t_action_type, t_units, t_from_date,
238 t_to_date, t_from_time, t_to_time
239 FROM BOM_RESOURCE_CHANGES
240 WHERE ROWID = l_rowid;
241
242
243 UPDATE BOM_RESOURCE_CHANGES
244 SET
245 SHIFT_NUM = l_shift_num,
246 ACTION_TYPE = l_action_type,
247 CAPACITY_CHANGE = l_units,
248 FROM_DATE = l_from_date,
249 TO_DATE = l_to_date,
250 FROM_TIME = l_from_time,
251 TO_TIME = l_to_time,
252 LAST_UPDATE_DATE = sysdate,
253 LAST_UPDATED_BY = l_user_id,
254 reason_code = x_reason_code
255 WHERE ROWID = l_rowid;
256
257
258 UPDATE BOM_RES_INSTANCE_CHANGES
259 SET
260 FROM_DATE = l_from_date,
261 TO_DATE = l_to_date,
262 FROM_TIME = l_from_time,
263 TO_TIME = l_to_time,
264 LAST_UPDATE_DATE = sysdate,
265 LAST_UPDATED_BY = l_user_id
266 WHERE
267 RESOURCE_ID = t_resource_id AND
268 DEPARTMENT_ID = t_department_id AND
269 SIMULATION_SET = t_simulation_set AND
270 SHIFT_NUM = t_shift_num AND
271 ACTION_TYPE = t_action_type AND
272 FROM_DATE = t_from_date AND
273 NVL(TO_DATE,SYSDATE) = NVL(t_to_date,SYSDATE) AND
274 NVL(FROM_TIME,0) = NVL(t_from_time,0) AND
275 NVL(TO_TIME,0) = NVL(t_to_time,0);
276
277 COMMIT;
278
279 END Update_Resource_Exception;
280
281 /**
282 * This procedure is used to insert a resource exception
283 */
284 PROCEDURE Insert_Resource_Exception(X_Resource_Id NUMBER,
285 X_Department_Id NUMBER,
286 X_Shift NUMBER,
287 X_Action NUMBER,
288 X_Units NUMBER,
289 X_From_Date DATE,
290 X_To_Date DATE,
291 X_From_Time NUMBER,
292 X_To_Time NUMBER,
293 X_Sim_Set VARCHAR2,
294 X_User_Id NUMBER,
295 X_REASON_CODE VARCHAR2 DEFAULT NULL) is
296 l_resource_id NUMBER;
297 l_department_id NUMBER;
298 l_shift_num NUMBER;
299 l_action_type NUMBER;
300 l_units NUMBER;
301 l_from_date DATE;
302 l_to_date DATE;
303 l_from_time NUMBER;
304 l_to_time NUMBER;
305 l_sim_set VARCHAR2(10);
306 l_record_count NUMBER;
307 l_user_id NUMBER;
308
309 PRAGMA AUTONOMOUS_TRANSACTION;
310
311 BEGIN
312 l_resource_id := X_Resource_Id;
313 l_department_id := X_Department_Id;
314 l_shift_num := X_Shift;
315 l_action_type := X_Action;
316 l_units := X_Units;
317 l_from_date := X_From_Date;
318 l_to_date := X_To_Date;
319 l_from_time := X_From_Time;
320 l_to_time := X_To_Time;
321 l_sim_set := X_Sim_Set;
322 l_user_id := X_User_Id;
323 l_record_count := 0;
324
325 if(l_action_type = 0) then
326 l_action_type := 2;
327 end if;
328
329 SELECT COUNT(*) INTO l_record_count
330 FROM BOM_RESOURCE_CHANGES
331 WHERE DEPARTMENT_ID = l_department_id AND
332 RESOURCE_ID = l_resource_id AND
333 SHIFT_NUM = l_shift_num AND
334 FROM_DATE = l_from_date AND
335 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
336 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
337 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
338 SIMULATION_SET = l_sim_set AND
339 ACTION_TYPE = l_action_type;
340
341 if (l_record_count = 0) then
342
343 INSERT INTO BOM_RESOURCE_CHANGES (
344 DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM, ACTION_TYPE,
345 CAPACITY_CHANGE, FROM_DATE, TO_DATE, FROM_TIME,
346 TO_TIME, SIMULATION_SET, LAST_UPDATE_DATE,
347 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, REASON_CODE)
348 VALUES
349 (l_department_id, l_resource_id, l_shift_num, l_action_type,
350 l_units, l_from_date, l_to_date, l_from_time,
351 l_to_time,l_sim_set, sysdate,
352 l_user_id, sysdate, l_user_id, X_REASON_CODE);
353 else
354 UPDATE BOM_RESOURCE_CHANGES
355 SET CAPACITY_CHANGE = CAPACITY_CHANGE + l_units,
356 LAST_UPDATE_DATE = sysdate,
357 LAST_UPDATED_BY = l_user_id
358 WHERE DEPARTMENT_ID = l_department_id AND
359 RESOURCE_ID = l_resource_id AND
360 SHIFT_NUM = l_shift_num AND
361 FROM_DATE = l_from_date AND
362 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
363 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
364 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
365 SIMULATION_SET = l_sim_set AND
366 ACTION_TYPE = l_action_type;
367 end if;
368
369 COMMIT;
370
371
372 END Insert_Resource_Exception;
373
374 /**
375 * This procedure is used to insert a resource instance exception
376 * Units will always be entered as 1 or -1 in the table
377 */
378
379 PROCEDURE Insert_ResInst_Exception(X_Resource_Id NUMBER,
380 X_Department_Id NUMBER,
381 X_Shift NUMBER,
382 X_Action NUMBER,
383 X_Units NUMBER,
384 X_From_Date DATE,
385 X_To_Date DATE,
386 X_From_Time NUMBER,
387 X_To_Time NUMBER,
388 X_Instance_Id NUMBER,
389 X_Serial_Num VARCHAR2,
390 X_Sim_Set VARCHAR2,
391 X_User_Id NUMBER,
392 X_REASON_CODE VARCHAR2 DEFAULT NULL) is
393
394
395 l_resource_id NUMBER;
396 l_department_id NUMBER;
397 l_shift_num NUMBER;
398 l_action_type NUMBER;
399 l_units NUMBER;
400 l_from_date DATE;
401 l_to_date DATE;
402 l_from_time NUMBER;
403 l_to_time NUMBER;
407 l_user_id NUMBER;
404 l_instance_id NUMBER;
405 l_serial_num VARCHAR2(30);
406 l_sim_set VARCHAR2(10);
408
409 PRAGMA AUTONOMOUS_TRANSACTION;
410
411 BEGIN
412 l_resource_id := X_Resource_Id;
413 l_department_id := X_Department_Id;
414 l_shift_num := X_Shift;
415 l_action_type := X_Action;
416 l_units := X_Units;
417 l_from_date := X_From_Date;
418 l_to_date := X_To_Date;
419 l_from_time := X_From_Time;
420 l_to_time := X_To_Time;
421 l_instance_id := X_instance_Id;
422 l_serial_num := X_Serial_Num;
423 l_sim_set := X_Sim_Set;
424 l_user_id := X_User_Id;
425
426 if(l_action_type = 0) then
427 l_action_type := 2;
428 end if;
429
430 INSERT INTO BOM_RES_INSTANCE_CHANGES (
431 DEPARTMENT_ID, RESOURCE_ID, SHIFT_NUM, ACTION_TYPE,
432 CAPACITY_CHANGE, FROM_DATE, TO_DATE, FROM_TIME,
433 TO_TIME, SIMULATION_SET, INSTANCE_ID, SERIAL_NUMBER,
434 LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
435 CREATED_BY, REASON_CODE)
436 VALUES
437 (l_department_id, l_resource_id, l_shift_num, l_action_type,
438 l_units, l_from_date, l_to_date, l_from_time,
439 l_to_time, l_sim_set, l_instance_id, l_serial_num,
440 sysdate, l_user_id, sysdate, l_user_id, x_reason_code);
441
442 COMMIT;
443
444
445 END Insert_ResInst_Exception;
446
447
448 /**
449 * This procedure is used to update a resource instance exception
450 */
451
452 PROCEDURE Update_ResInst_Exception (X_Rowid VARCHAR2,
453 X_Shift NUMBER,
454 X_Action NUMBER,
455 X_Units NUMBER,
456 X_From_Date DATE,
457 X_To_Date DATE,
458 X_From_Time NUMBER,
459 X_To_Time NUMBER,
460 X_Instance_Id NUMBER,
461 X_Serial_Num VARCHAR2,
462 X_User_Id NUMBER,
463 X_REASON_CODE VARCHAR2 DEFAULT NULL) is
464
465 l_rowid VARCHAR2(30);
466 l_shift_num NUMBER;
467 l_action_type NUMBER;
468 l_units NUMBER;
469 l_from_date DATE;
470 l_to_date DATE;
471 l_from_time NUMBER;
472 l_to_time NUMBER;
473 l_instance_id NUMBER;
474 l_serial_num VARCHAR2(30);
475 l_user_id NUMBER;
476
477 PRAGMA AUTONOMOUS_TRANSACTION;
478
479 BEGIN
480
481 l_rowid := X_Rowid;
482 l_shift_num := X_Shift;
483 l_action_type := X_Action;
484 l_units := X_Units;
485 l_from_date := X_From_Date;
486 l_to_date := X_To_Date;
487 l_from_time := X_From_Time;
488 l_to_time := X_To_Time;
489 l_instance_id := X_Instance_Id;
490 l_serial_num := X_Serial_Num;
491 l_user_id := X_User_Id;
492
493 if (l_action_type = 0) then
494 l_action_type := 2;
495 end if;
496
497 UPDATE BOM_RES_INSTANCE_CHANGES
498 SET
499 SHIFT_NUM = l_shift_num,
500 ACTION_TYPE = l_action_type,
501 CAPACITY_CHANGE = l_units,
502 FROM_DATE = l_from_date,
503 TO_DATE = l_to_date,
504 FROM_TIME = l_from_time,
505 TO_TIME = l_to_time,
506 LAST_UPDATED_BY = l_user_id,
507 LAST_UPDATE_DATE = sysdate,
508 REASON_CODE = x_reason_code
509 WHERE ROWID = l_rowid;
510 COMMIT;
511
512 END Update_ResInst_Exception;
513
514
515 /**
516 * This procedure is used to check whether insertion of
517 * instance exception will max out the assigned units of
518 * resource exception. This only make sense for
519 * for the case of resource which is scheduled to instance
520 */
521
522 PROCEDURE CheckResInstForInsert(X_Resource_Id NUMBER,
523 X_Department_Id NUMBER,
527 X_Units NUMBER,
524 X_Sim_Set VARCHAR2,
525 X_Shift NUMBER,
526 X_Action NUMBER,
528 X_From_Date DATE,
529 X_To_Date DATE,
530 X_From_Time NUMBER,
531 X_To_Time NUMBER,
532 X_Return_Id OUT NOCOPY NUMBER) is
533
534
535 l_resource_id NUMBER;
536 l_department_id NUMBER;
537 l_sim_set VARCHAR2(10);
538 l_shift_num NUMBER;
539 l_action_type NUMBER;
540 l_units NUMBER;
541 l_from_date DATE;
542 l_to_date DATE;
543 l_from_time NUMBER;
544 l_to_time NUMBER;
545 l_max_units NUMBER;
546 l_current_units NUMBER;
547 l_return_id NUMBER;
548 l_record_count NUMBER;
549
550 BEGIN
551
552 l_resource_id := X_Resource_Id;
553 l_department_id := X_Department_Id;
554 l_sim_set := X_Sim_Set;
555 l_shift_num := X_Shift;
556 l_action_type := X_Action;
557 l_units := X_Units;
558 l_from_date := X_From_Date;
559 l_to_date := X_To_Date;
560 l_from_time := X_From_Time;
561 l_to_time := X_To_Time;
562 l_current_units := 999999;
563 l_return_id := 0;
564 l_record_count := 0;
565
566 if (l_action_type = 0) then
567 l_action_type := 2;
568 end if;
569
570 SELECT CAPACITY_UNITS INTO l_max_units
571 FROM BOM_DEPARTMENT_RESOURCES
572 WHERE RESOURCE_ID = l_resource_id and
573 DEPARTMENT_ID = l_department_id;
574
575 SELECT COUNT(*) INTO l_record_count
576 FROM BOM_RESOURCE_CHANGES
577 WHERE DEPARTMENT_ID = l_department_id AND
578 RESOURCE_ID = l_resource_id AND
579 SHIFT_NUM = l_shift_num AND
580 FROM_DATE = l_from_date AND
581 NVL(TO_DATE,SYSDATE) = NVL(l_to_date,SYSDATE) AND
582 NVL(FROM_TIME,0) = NVL(l_from_time,0) AND
583 NVL(TO_TIME,0) = NVL(l_to_time,0) AND
584 SIMULATION_SET = l_sim_set AND
585 ACTION_TYPE = l_action_type;
586
587
588 if(l_record_count <> 0) then
589 SELECT nvl(CAPACITY_CHANGE,999999) into l_current_units
590 FROM BOM_RESOURCE_CHANGES
591 WHERE DEPARTMENT_ID = l_department_id AND
592 RESOURCE_ID = l_resource_id AND
593 SHIFT_NUM = l_shift_num AND
594 SIMULATION_SET = l_sim_set AND
595 ACTION_TYPE = l_action_type AND
596 FROM_DATE = l_from_date AND
597 nvl(TO_DATE,sysdate) = nvl(l_to_date,sysdate) AND
598 nvl(FROM_TIME,0) = nvl(l_from_time,0) AND
599 nvl(TO_TIME,0) = nvl(l_to_time,0);
600
601 end if;
602
603 if(l_current_units <> 999999) then
604 if (l_current_units + 1 > l_max_units) then
605 l_return_id := 1;
606 end if;
607 end if;
608
609 X_Return_Id := l_return_id;
610
611 END CheckResInstForInsert;
612
613
614
615 END WPS_CAPACITY_CHANGES_PKG;