[Home] [Help]
PACKAGE BODY: APPS.WIP_REPETITIVE_UTILITIES
Source
1 PACKAGE BODY WIP_REPETITIVE_UTILITIES AS
2 /* $Header: wipreutb.pls 115.15 2003/06/11 11:29:05 panagara ship $ */
3
4 /*============================================================================
5 | SPLIT_SCHEDULE
6 | This procedure split a schedule into two consecutive schedules.
7 | A new schedule is created and the new schedule id is returned.
8 |
9 | PARAMETERS
10 | p_sched_id Id of the schedule to be split
11 | p_org_id Organization Id
12 | p_new_sched_id Id of the new schedule that was split from the old
13 | schedule
14 =============================================================================*/
15
16 PROCEDURE
17 split_schedule (p_sched_id IN NUMBER,
18 p_org_id IN NUMBER,
19 p_new_sched_id IN OUT NOCOPY NUMBER) IS
20 x_cal_code VARCHAR2(11);
21 x_excp_set_id NUMBER;
22 x_wip_id NUMBER;
23 x_rnd_proc_days NUMBER;
24 x_rnd_days_ran NUMBER;
25 x_rnd_days_left NUMBER;
26 x_fusd NUMBER;
27 x_fucd NUMBER;
28 x_lusd NUMBER;
29 x_lucd NUMBER;
30 x_first_op NUMBER;
31 x_user_id NUMBER;
32 x_login_id NUMBER;
33 x_request_id NUMBER;
34 x_appl_id NUMBER;
35 x_program_id NUMBER;
36 x_rate NUMBER;
37 x_qty_completed NUMBER;
38 x_act_proc_days NUMBER;
39 x_act_days_left NUMBER;
40 x_found BOOLEAN;
41 x_class_code VARCHAR2(11);
42 x_line_id NUMBER;
43 err_msg VARCHAR2(100);
44
45 CURSOR cal is
46 SELECT CALENDAR_CODE,
47 CALENDAR_EXCEPTION_SET_ID
48 FROM MTL_PARAMETERS
49 WHERE ORGANIZATION_ID = p_org_id;
50
51 CURSOR gen_info IS
52 SELECT WRS.WIP_ENTITY_ID,
53 WRS.DAILY_PRODUCTION_RATE,
54 WRS.PROCESSING_WORK_DAYS,
55 CEIL(WRS.PROCESSING_WORK_DAYS),
56 WRS.QUANTITY_COMPLETED,
57 CD1.NEXT_SEQ_NUM,
58 CD2.NEXT_SEQ_NUM,
59 CD3.PRIOR_SEQ_NUM,
60 CD4.PRIOR_SEQ_NUM
61 FROM BOM_CALENDAR_DATES CD1,
62 BOM_CALENDAR_DATES CD2,
63 BOM_CALENDAR_DATES CD3,
64 BOM_CALENDAR_DATES CD4,
65 WIP_REPETITIVE_SCHEDULES WRS
66 WHERE WRS.ORGANIZATION_ID = p_org_id
67 AND WRS.REPETITIVE_SCHEDULE_ID = p_sched_id
68 AND CD1.CALENDAR_CODE = x_cal_code
69 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
70 AND CD1.CALENDAR_DATE = TRUNC(WRS.FIRST_UNIT_START_DATE)
71 AND CD2.CALENDAR_CODE = x_cal_code
72 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
73 AND CD2.CALENDAR_DATE = TRUNC(WRS.FIRST_UNIT_COMPLETION_DATE)
74 AND CD3.CALENDAR_CODE = x_cal_code
75 AND CD3.EXCEPTION_SET_ID = x_excp_set_id
76 AND CD3.CALENDAR_DATE = TRUNC(WRS.LAST_UNIT_START_DATE)
77 AND CD4.CALENDAR_CODE = x_cal_code
78 AND CD4.EXCEPTION_SET_ID = x_excp_set_id
79 AND CD4.CALENDAR_DATE = TRUNC(WRS.LAST_UNIT_COMPLETION_DATE);
80
81 CURSOR first_op IS
82 SELECT NVL(MIN(OPERATION_SEQ_NUM), -1)
83 FROM WIP_OPERATIONS
84 WHERE ORGANIZATION_ID = p_org_id
85 AND WIP_ENTITY_ID = x_wip_id
86 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
87
88 CURSOR qty_completed IS
89 SELECT QUANTITY_RUNNING + QUANTITY_COMPLETED
90 FROM WIP_OPERATIONS
91 WHERE ORGANIZATION_ID = p_org_id
92 AND WIP_ENTITY_ID = x_wip_id
93 AND OPERATION_SEQ_NUM = x_first_op
94 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
95
96 CURSOR nu_sched_id IS
97 SELECT WIP_REPETITIVE_SCHEDULES_S.NEXTVAL
98 FROM DUAL;
99
100 CURSOR per_bal IS
101 SELECT WRS.line_id, WRI.class_code
102 FROM WIP_REPETITIVE_ITEMS WRI, WIP_REPETITIVE_SCHEDULES WRS
103 WHERE WRS.REPETITIVE_SCHEDULE_ID = p_new_sched_id
104 AND WRS.ORGANIZATION_ID = p_org_id
105 AND WRS.WIP_ENTITY_ID = x_wip_id
106 AND WRI.WIP_ENTITY_ID = x_wip_id
107 AND WRI.LINE_ID = wrs.line_id;
108
109 CURSOR wip_op_inst IS
110 SELECT distinct pk2_value
111 FROM fnd_attached_documents
112 WHERE pk1_value = to_char(x_wip_id)
113 AND pk3_value = to_char(p_org_id)
114 AND pk4_value = to_char(p_sched_id)
115 AND entity_name = 'WIP_REPETITIVE_OPERATIONS';
116
117
118 BEGIN
119
120 x_user_id := FND_GLOBAL.USER_ID;
121 x_request_id := FND_GLOBAL.CONC_REQUEST_ID;
122 x_login_id := FND_GLOBAL.LOGIN_ID;
123 x_appl_id := FND_GLOBAL.PROG_APPL_ID;
124 x_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
125
126 open cal;
127 fetch cal into x_cal_code, x_excp_set_id;
128 x_found := cal%NOTFOUND;
129 close cal;
130
131 if x_found then
132 fnd_message.set_name('WIP', 'WIP_DEFINE_INV_PARAMETERS');
133 app_exception.raise_exception;
134 end if;
135
136 -- get the next working fusd, fucd, lusd, lucd as well as other info
137
138 open gen_info;
139 FETCH gen_info INTO x_wip_id, x_rate, x_act_proc_days,
140 x_rnd_proc_days, x_qty_completed, x_fusd,
141 x_fucd, x_lusd, x_lucd;
142 close gen_info;
143
144 -- getting first operation in the schedule --
145
146 open first_op;
147 fetch first_op INTO x_first_op;
148 close first_op;
149
150 IF (x_first_op <> -1) THEN
151 open qty_completed;
152 fetch qty_completed into x_qty_completed;
153 close qty_completed;
154 END IF;
155
156 x_rnd_days_ran := ceil(x_qty_completed / x_rate);
157 x_rnd_days_left := x_rnd_proc_days - x_rnd_days_ran;
158 x_act_days_left := x_act_proc_days - x_rnd_days_ran;
159
160 IF (x_rnd_days_ran < x_rnd_proc_days) THEN
161 IF (x_rnd_days_ran <> 0) THEN
162 open nu_sched_id;
163 fetch nu_sched_id into p_new_sched_id;
164 close nu_sched_id;
165
166 -- inserting new schedule
167 INSERT INTO WIP_REPETITIVE_SCHEDULES
168 (REPETITIVE_SCHEDULE_ID, ORGANIZATION_ID,
169 LAST_UPDATE_DATE, LAST_UPDATED_BY,
170 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
171 REQUEST_ID, PROGRAM_APPLICATION_ID,
172 PROGRAM_ID, PROGRAM_UPDATE_DATE,
173 WIP_ENTITY_ID, LINE_ID,
174 DAILY_PRODUCTION_RATE, PROCESSING_WORK_DAYS,
175 STATUS_TYPE, FIRM_PLANNED_FLAG,
176 ALTERNATE_BOM_DESIGNATOR, COMMON_BOM_SEQUENCE_ID,
177 BOM_REVISION, BOM_REVISION_DATE,
178 ALTERNATE_ROUTING_DESIGNATOR, COMMON_ROUTING_SEQUENCE_ID,
179 ROUTING_REVISION, ROUTING_REVISION_DATE,
180 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE,
181 LAST_UNIT_START_DATE, LAST_UNIT_COMPLETION_DATE,
182 DATE_RELEASED, DATE_CLOSED,
183 QUANTITY_COMPLETED, DESCRIPTION,
184 DEMAND_CLASS, MATERIAL_ACCOUNT,
185 MATERIAL_OVERHEAD_ACCOUNT, MATERIAL_VARIANCE_ACCOUNT,
186 OUTSIDE_PROCESSING_ACCOUNT, OUTSIDE_PROC_VARIANCE_ACCOUNT,
187 OVERHEAD_ACCOUNT, OVERHEAD_VARIANCE_ACCOUNT,
188 RESOURCE_ACCOUNT, RESOURCE_VARIANCE_ACCOUNT,
189 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
190 ATTRIBUTE2, ATTRIBUTE3,
191 ATTRIBUTE4, ATTRIBUTE5,
192 ATTRIBUTE6, ATTRIBUTE7,
193 ATTRIBUTE8, ATTRIBUTE9,
194 ATTRIBUTE10, ATTRIBUTE11,
195 ATTRIBUTE12, ATTRIBUTE13,
196 ATTRIBUTE14, ATTRIBUTE15)
197 SELECT p_new_sched_id, WRS.ORGANIZATION_ID,
198 SYSDATE, x_user_id,
199 SYSDATE, x_user_id, x_login_id,
200 DECODE(x_request_id, 0, '', x_request_id),
201 DECODE(x_appl_id, 0, '', x_appl_id),
202 DECODE(x_program_id, 0, '', x_program_id),
203 DECODE(x_program_id, 0, '', SYSDATE),
204 WRS.WIP_ENTITY_ID, WRS.LINE_ID,
205 x_rate, x_act_days_left,
206 WRS.STATUS_TYPE, WRS.FIRM_PLANNED_FLAG,
207 WRS.ALTERNATE_BOM_DESIGNATOR, WRS.COMMON_BOM_SEQUENCE_ID,
208 WRS.BOM_REVISION, WRS.BOM_REVISION_DATE,
209 WRS.ALTERNATE_ROUTING_DESIGNATOR,
210 WRS.COMMON_ROUTING_SEQUENCE_ID,
211 WRS.ROUTING_REVISION, WRS.ROUTING_REVISION_DATE,
212 TO_DATE(TO_CHAR(CD1.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
213 TO_CHAR(WRS.FIRST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
214 WIP_CONSTANTS.DATETIME_FMT),
215 TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
216 TO_CHAR(WRS.FIRST_UNIT_COMPLETION_DATE, WIP_CONSTANTS.TIMESEC_FMT),
217 WIP_CONSTANTS.DATETIME_FMT),
218 WRS.LAST_UNIT_START_DATE, WRS.LAST_UNIT_COMPLETION_DATE,
219 SYSDATE, WRS.DATE_CLOSED,
220 0, WRS.DESCRIPTION,
221 WRS.DEMAND_CLASS, WRS.MATERIAL_ACCOUNT,
222 WRS.MATERIAL_OVERHEAD_ACCOUNT,
223 WRS.MATERIAL_VARIANCE_ACCOUNT,
224 WRS.OUTSIDE_PROCESSING_ACCOUNT,
225 WRS.OUTSIDE_PROC_VARIANCE_ACCOUNT,
226 WRS.OVERHEAD_ACCOUNT, WRS.OVERHEAD_VARIANCE_ACCOUNT,
227 WRS.RESOURCE_ACCOUNT, WRS.RESOURCE_VARIANCE_ACCOUNT,
228 WRS.ATTRIBUTE_CATEGORY, WRS.ATTRIBUTE1,
229 WRS.ATTRIBUTE2, WRS.ATTRIBUTE3,
230 WRS.ATTRIBUTE4, WRS.ATTRIBUTE5,
231 WRS.ATTRIBUTE6, WRS.ATTRIBUTE7,
232 WRS.ATTRIBUTE8, WRS.ATTRIBUTE9,
233 WRS.ATTRIBUTE10, WRS.ATTRIBUTE11,
234 WRS.ATTRIBUTE12, WRS.ATTRIBUTE13,
235 WRS.ATTRIBUTE14, WRS.ATTRIBUTE15
236 FROM BOM_CALENDAR_DATES CD1,
237 BOM_CALENDAR_DATES CD2,
238 WIP_REPETITIVE_SCHEDULES WRS
239 WHERE WRS.ORGANIZATION_ID = p_org_id
240 AND WRS.REPETITIVE_SCHEDULE_ID = p_sched_id
241 AND CD1.CALENDAR_CODE = x_cal_code
242 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
243 AND CD1.SEQ_NUM = x_fusd + x_rnd_days_ran
244 AND CD2.CALENDAR_CODE = x_cal_code
245 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
246 AND CD2.SEQ_NUM = x_fucd + x_rnd_days_ran;
247
248 --- get line_id and class_code for insert period balances (11/94)
249 open per_bal;
250 fetch per_bal INTO x_line_id, x_class_code;
251 close per_bal;
252
253 wip_change_status.insert_period_balances(x_wip_id, p_org_id,
254 p_new_sched_id, x_line_id,
255 x_class_code);
256
257 -- set processed days, lusd, lucd of the old schedule to reflect days the
258 -- schedule actually ran
259
260 UPDATE WIP_REPETITIVE_SCHEDULES
261 SET PROCESSING_WORK_DAYS = x_rnd_days_ran,
262 LAST_UNIT_START_DATE =
263 (SELECT TO_DATE(TO_CHAR(CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
264 TO_CHAR(LAST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
265 WIP_CONSTANTS.DATETIME_FMT)
266 FROM BOM_CALENDAR_DATES
267 WHERE CALENDAR_CODE = x_cal_code
268 AND EXCEPTION_SET_ID = x_excp_set_id
269 AND SEQ_NUM = x_lusd - x_rnd_days_left),
270 LAST_UNIT_COMPLETION_DATE =
271 (SELECT TO_DATE(TO_CHAR(CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
272 TO_CHAR(LAST_UNIT_COMPLETION_DATE,
273 WIP_CONSTANTS.TIMESEC_FMT),
274 WIP_CONSTANTS.DATETIME_FMT)
275 FROM BOM_CALENDAR_DATES
276 WHERE CALENDAR_CODE = x_cal_code
277 AND EXCEPTION_SET_ID = x_excp_set_id
278 AND SEQ_NUM = x_lucd - x_rnd_days_left),
279 LAST_UPDATE_DATE = SYSDATE,
280 LAST_UPDATED_BY = x_user_id,
281 REQUEST_ID =
282 DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
283 PROGRAM_APPLICATION_ID =
284 DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
285 PROGRAM_ID =
286 DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
287 PROGRAM_UPDATE_DATE =
288 DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
289 WHERE ORGANIZATION_ID = p_org_id
290 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
291
292 -- copy ops from old schedule for new schedule
293
294 INSERT INTO WIP_OPERATIONS
295 (WIP_ENTITY_ID, OPERATION_SEQ_NUM,
296 ORGANIZATION_ID, REPETITIVE_SCHEDULE_ID,
297 LAST_UPDATE_DATE, LAST_UPDATED_BY,
298 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
299 REQUEST_ID, PROGRAM_APPLICATION_ID,
300 PROGRAM_ID, PROGRAM_UPDATE_DATE,
301 OPERATION_SEQUENCE_ID, STANDARD_OPERATION_ID,
302 DEPARTMENT_ID, DESCRIPTION,
303 SCHEDULED_QUANTITY, QUANTITY_IN_QUEUE,
304 QUANTITY_RUNNING, QUANTITY_WAITING_TO_MOVE,
305 QUANTITY_REJECTED, QUANTITY_SCRAPPED,
306 QUANTITY_COMPLETED, DATE_LAST_MOVED,
307 CUMULATIVE_SCRAP_QUANTITY, /* Enh#2864382*/
308 FIRST_UNIT_START_DATE, FIRST_UNIT_COMPLETION_DATE,
309 LAST_UNIT_START_DATE, LAST_UNIT_COMPLETION_DATE,
310 PREVIOUS_OPERATION_SEQ_NUM, NEXT_OPERATION_SEQ_NUM,
311 COUNT_POINT_TYPE, BACKFLUSH_FLAG,
312 MINIMUM_TRANSFER_QUANTITY, LONG_DESCRIPTION,
313 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
314 ATTRIBUTE2, ATTRIBUTE3,
315 ATTRIBUTE4, ATTRIBUTE5,
316 ATTRIBUTE6, ATTRIBUTE7,
317 ATTRIBUTE8, ATTRIBUTE9,
318 ATTRIBUTE10, ATTRIBUTE11,
319 ATTRIBUTE12, ATTRIBUTE13,
320 ATTRIBUTE14, ATTRIBUTE15)
321 SELECT OPS.WIP_ENTITY_ID, OPS.OPERATION_SEQ_NUM,
322 OPS.ORGANIZATION_ID, p_new_sched_id,
323 SYSDATE, x_user_id,
324 SYSDATE, x_user_id, x_login_id,
325 DECODE(x_request_id, 0, '', x_request_id),
326 DECODE(x_appl_id, 0, '', x_appl_id),
327 DECODE(x_program_id, 0, '', x_program_id),
328 DECODE(x_program_id, 0, '', SYSDATE),
329 OPS.OPERATION_SEQUENCE_ID, OPS.STANDARD_OPERATION_ID,
330 OPS.DEPARTMENT_ID, OPS.DESCRIPTION,
331 x_rate * x_act_days_left,
332 DECODE(OPS.OPERATION_SEQ_NUM,
333 x_first_op, x_rate * x_act_days_left, 0),
334 0, 0,
335 0, 0,
336 0, '',0,
337 TO_DATE(TO_CHAR(CD1.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
338 TO_CHAR(OPS.FIRST_UNIT_START_DATE, WIP_CONSTANTS.TIMESEC_FMT),
339 WIP_CONSTANTS.DATETIME_FMT),
340 TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
341 TO_CHAR(OPS.FIRST_UNIT_COMPLETION_DATE, WIP_CONSTANTS.TIMESEC_FMT),
342 WIP_CONSTANTS.DATETIME_FMT),
343 OPS.LAST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
344 OPS.PREVIOUS_OPERATION_SEQ_NUM, OPS.NEXT_OPERATION_SEQ_NUM,
345 OPS.COUNT_POINT_TYPE, OPS.BACKFLUSH_FLAG,
346 OPS.MINIMUM_TRANSFER_QUANTITY, OPS.LONG_DESCRIPTION,
347 OPS.ATTRIBUTE_CATEGORY, OPS.ATTRIBUTE1,
348 OPS.ATTRIBUTE2, OPS.ATTRIBUTE3,
349 OPS.ATTRIBUTE4, OPS.ATTRIBUTE5,
350 OPS.ATTRIBUTE6, OPS.ATTRIBUTE7,
351 OPS.ATTRIBUTE8, OPS.ATTRIBUTE9,
352 OPS.ATTRIBUTE10, OPS.ATTRIBUTE11,
353 OPS.ATTRIBUTE12, OPS.ATTRIBUTE13,
354 OPS.ATTRIBUTE14, OPS.ATTRIBUTE15
355 FROM BOM_CALENDAR_DATES CD1,
356 BOM_CALENDAR_DATES CD2,
357 WIP_OPERATIONS OPS
358 WHERE OPS.ORGANIZATION_ID = p_org_id
359 AND OPS.WIP_ENTITY_ID = x_wip_id
360 AND OPS.REPETITIVE_SCHEDULE_ID = p_sched_id
361 AND CD1.CALENDAR_CODE = x_cal_code
362 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
363 AND CD1.SEQ_NUM =
364 (SELECT NEXT_SEQ_NUM + x_rnd_days_ran
365 FROM BOM_CALENDAR_DATES
366 WHERE CALENDAR_CODE = x_cal_code
367 AND EXCEPTION_SET_ID = x_excp_set_id
368 AND CALENDAR_DATE =
369 TRUNC(OPS.FIRST_UNIT_START_DATE))
370 AND CD2.CALENDAR_CODE = x_cal_code
371 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
372 AND CD2.SEQ_NUM =
373 (SELECT NEXT_SEQ_NUM + x_rnd_days_ran
374 FROM BOM_CALENDAR_DATES
375 WHERE CALENDAR_CODE = x_cal_code
376 AND EXCEPTION_SET_ID = x_excp_set_id
377 AND CALENDAR_DATE =
378 TRUNC(OPS.FIRST_UNIT_COMPLETION_DATE));
379
380 -- set the quantity in the queue to quantity not complete
381 -- and update lusd, lucd for the wip_operations table
382
383 UPDATE WIP_OPERATIONS OPS
384 SET QUANTITY_IN_QUEUE =
385 DECODE(OPERATION_SEQ_NUM,
386 x_first_op,
387 QUANTITY_IN_QUEUE - x_act_days_left * x_rate,
388 QUANTITY_IN_QUEUE),
389 SCHEDULED_QUANTITY = x_rate * x_rnd_days_ran,
390 LAST_UNIT_START_DATE =
391 (SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
392 TO_CHAR(OPS.LAST_UNIT_START_DATE,
393 WIP_CONSTANTS.TIMESEC_FMT),
394 WIP_CONSTANTS.DATETIME_FMT)
395 FROM BOM_CALENDAR_DATES CD1,
396 BOM_CALENDAR_DATES CD2
397 WHERE CD1.CALENDAR_CODE = x_cal_code
398 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
399 AND CD1.CALENDAR_DATE =
400 TRUNC(OPS.LAST_UNIT_START_DATE)
401 AND CD2.CALENDAR_CODE = x_cal_code
402 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
403 AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
404 LAST_UNIT_COMPLETION_DATE =
405 (SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
406 TO_CHAR(OPS.LAST_UNIT_COMPLETION_DATE,
407 WIP_CONSTANTS.TIMESEC_FMT),
408 WIP_CONSTANTS.DATETIME_FMT)
409 FROM BOM_CALENDAR_DATES CD1,
410 BOM_CALENDAR_DATES CD2
411 WHERE CD1.CALENDAR_CODE = x_cal_code
412 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
413 AND CD1.CALENDAR_DATE =
414 TRUNC(OPS.LAST_UNIT_COMPLETION_DATE)
415 AND CD2.CALENDAR_CODE = x_cal_code
416 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
417 AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
418 LAST_UPDATE_DATE = SYSDATE,
419 LAST_UPDATED_BY = x_user_id,
420 REQUEST_ID =
421 DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
422 PROGRAM_APPLICATION_ID =
423 DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
424 PROGRAM_ID =
425 DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
426 PROGRAM_UPDATE_DATE =
427 DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
428 WHERE ORGANIZATION_ID = p_org_id
429 AND WIP_ENTITY_ID = x_wip_id
430 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
431
432 INSERT INTO WIP_OPERATION_RESOURCES
433 (WIP_ENTITY_ID, OPERATION_SEQ_NUM,
434 RESOURCE_SEQ_NUM, ORGANIZATION_ID,
435 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
436 LAST_UPDATED_BY, CREATION_DATE,
437 CREATED_BY, LAST_UPDATE_LOGIN,
438 REQUEST_ID, PROGRAM_APPLICATION_ID,
439 PROGRAM_ID, PROGRAM_UPDATE_DATE,
440 RESOURCE_ID, UOM_CODE, BASIS_TYPE,
441 USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
442 SCHEDULED_FLAG, ASSIGNED_UNITS,
443 AUTOCHARGE_TYPE, STANDARD_RATE_FLAG,
444 APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
445 START_DATE, COMPLETION_DATE,
446 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
447 ATTRIBUTE2, ATTRIBUTE3,
448 ATTRIBUTE4, ATTRIBUTE5,
449 ATTRIBUTE6, ATTRIBUTE7,
450 ATTRIBUTE8, ATTRIBUTE9,
451 ATTRIBUTE10, ATTRIBUTE11,
452 ATTRIBUTE12, ATTRIBUTE13,
453 ATTRIBUTE14, ATTRIBUTE15)
454 SELECT R.WIP_ENTITY_ID, R.OPERATION_SEQ_NUM,
455 R.RESOURCE_SEQ_NUM, R.ORGANIZATION_ID,
456 p_new_sched_id, SYSDATE,
457 x_user_id, SYSDATE,
458 x_user_id, x_login_id,
459 DECODE(x_request_id, 0, '', x_request_id),
460 DECODE(x_appl_id, 0, '', x_appl_id),
461 DECODE(x_program_id, 0, '', x_program_id),
462 DECODE(x_program_id, 0, '', SYSDATE),
463 R.RESOURCE_ID, R.UOM_CODE, R.BASIS_TYPE,
464 R.USAGE_RATE_OR_AMOUNT, R.ACTIVITY_ID,
465 R.SCHEDULED_FLAG, R.ASSIGNED_UNITS,
466 R.AUTOCHARGE_TYPE, R.STANDARD_RATE_FLAG,
467 0, 0,
468 O.FIRST_UNIT_START_DATE, O.LAST_UNIT_COMPLETION_DATE,
469 R.ATTRIBUTE_CATEGORY, R.ATTRIBUTE1,
470 R.ATTRIBUTE2, R.ATTRIBUTE3,
471 R.ATTRIBUTE4, R.ATTRIBUTE5,
472 R.ATTRIBUTE6, R.ATTRIBUTE7,
473 R.ATTRIBUTE8, R.ATTRIBUTE9,
474 R.ATTRIBUTE10, R.ATTRIBUTE11,
475 R.ATTRIBUTE12, R.ATTRIBUTE13,
476 R.ATTRIBUTE14, R.ATTRIBUTE15
477 FROM WIP_OPERATION_RESOURCES R,
478 WIP_OPERATIONS O
479 WHERE R.WIP_ENTITY_ID = x_wip_id
480 AND R.ORGANIZATION_ID = p_org_id
481 AND R.REPETITIVE_SCHEDULE_ID = p_sched_id
482 AND O.WIP_ENTITY_ID = x_wip_id
483 AND O.ORGANIZATION_ID = p_org_id
484 AND O.REPETITIVE_SCHEDULE_ID = p_new_sched_id
485 AND R.OPERATION_SEQ_NUM = O.OPERATION_SEQ_NUM;
486
487 UPDATE WIP_OPERATION_RESOURCES OPS
488 SET COMPLETION_DATE =
489 (SELECT TO_DATE(TO_CHAR(CD2.CALENDAR_DATE, WIP_CONSTANTS.DATE_FMT) || ' ' ||
490 TO_CHAR(OPS.COMPLETION_DATE,
491 WIP_CONSTANTS.TIMESEC_FMT),
492 WIP_CONSTANTS.DATETIME_FMT)
493 FROM BOM_CALENDAR_DATES CD1,
494 BOM_CALENDAR_DATES CD2
495 WHERE CD1.CALENDAR_CODE = x_cal_code
496 AND CD1.EXCEPTION_SET_ID = x_excp_set_id
497 AND CD1.CALENDAR_DATE =
498 TRUNC(OPS.COMPLETION_DATE)
499 AND CD2.CALENDAR_CODE = x_cal_code
500 AND CD2.EXCEPTION_SET_ID = x_excp_set_id
501 AND CD2.SEQ_NUM = CD1.PRIOR_SEQ_NUM - x_rnd_days_left),
502 LAST_UPDATE_DATE = SYSDATE,
503 LAST_UPDATED_BY = x_user_id,
504 REQUEST_ID =
505 DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
506 PROGRAM_APPLICATION_ID =
507 DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
508 PROGRAM_ID =
509 DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
510 PROGRAM_UPDATE_DATE =
511 DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
512 WHERE ORGANIZATION_ID = p_org_id
513 AND WIP_ENTITY_ID = x_wip_id
514 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
515
516 /* jkent->lyao: Self-referential insertion? Hmm...
517 INSERT INTO WIP_OPERATION_INSTRUCTIONS
518 (WIP_ENTITY_ID, OPERATION_SEQ_NUM,
519 OPERATION_DESCRIPTION_CODE, ORGANIZATION_ID,
520 REPETITIVE_SCHEDULE_ID, LAST_UPDATE_DATE,
521 LAST_UPDATED_BY, CREATION_DATE,
522 CREATED_BY, LAST_UPDATE_LOGIN,
523 REQUEST_ID, PROGRAM_APPLICATION_ID,
524 PROGRAM_ID, PROGRAM_UPDATE_DATE,
525 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
526 . . .
527 ATTRIBUTE14, ATTRIBUTE15)
528 SELECT WIP_ENTITY_ID, OPERATION_SEQ_NUM,
529 OPERATION_DESCRIPTION_CODE, ORGANIZATION_ID,
530 p_new_sched_id, SYSDATE,
531 x_user_id, SYSDATE,
532 x_user_id, x_login_id,
533 DECODE(x_request_id, 0, '', x_request_id),
534 DECODE(x_appl_id, 0, '', x_appl_id),
535 DECODE(x_program_id, 0, '', x_program_id),
536 DECODE(x_program_id, 0, '', SYSDATE),
537 ATTRIBUTE_CATEGORY, ATTRIBUTE1,
538 . . .
539 ATTRIBUTE14, ATTRIBUTE15
540 FROM WIP_OPERATION_INSTRUCTIONS
541 WHERE WIP_ENTITY_ID = x_wip_id
542 AND ORGANIZATION_ID = p_org_id
543 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
544 */
545 FOR cur_rec IN wip_op_inst LOOP
546 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
547 X_FROM_ENTITY_NAME => 'WIP_REPETITIVE_OPERATIONS',
548 X_FROM_PK1_VALUE => to_char(x_wip_id),
549 X_FROM_PK2_VALUE => cur_rec.pk2_value,
550 X_FROM_PK3_VALUE => to_char(p_org_id ),
551 X_FROM_PK4_VALUE => to_char(p_sched_id),
552 X_TO_ENTITY_NAME => 'WIP_REPETITIVE_OPERATIONS',
553 X_TO_PK1_VALUE => to_char(x_wip_id),
554 X_TO_PK2_VALUE => cur_rec.pk2_value,
555 X_TO_PK3_VALUE => to_char(p_org_id),
556 X_TO_PK4_VALUE => to_char(p_new_sched_id),
557 X_CREATED_BY => x_user_id,
558 X_LAST_UPDATE_LOGIN => x_login_id,
559 X_PROGRAM_APPLICATION_ID => x_appl_id,
560 X_PROGRAM_ID => x_program_id,
561 X_REQUEST_ID => x_request_id);
562 END LOOP;
563
564
565 UPDATE WIP_REQUIREMENT_OPERATIONS R
566 SET REQUIRED_QUANTITY = QUANTITY_PER_ASSEMBLY *
567 x_rate * x_rnd_days_ran,
568 LAST_UPDATE_DATE = SYSDATE,
569 LAST_UPDATED_BY = x_user_id,
570 REQUEST_ID =
571 DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
572 PROGRAM_APPLICATION_ID =
573 DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
574 PROGRAM_ID =
575 DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
576 PROGRAM_UPDATE_DATE =
577 DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
578 WHERE WIP_ENTITY_ID = x_wip_id
579 AND ORGANIZATION_ID = p_org_id
580 AND REPETITIVE_SCHEDULE_ID = p_sched_id
581 AND REQUIRED_QUANTITY > 0;
582
583 -- x_rnd_days_ran = 0
584 ELSE
585
586 UPDATE WIP_OPERATIONS
587 SET QUANTITY_IN_QUEUE =
588 DECODE(OPERATION_SEQ_NUM,
589 x_first_op, x_rate * x_act_days_left,
590 QUANTITY_IN_QUEUE),
591 SCHEDULED_QUANTITY = x_rate * x_act_days_left,
592 LAST_UPDATE_DATE = SYSDATE,
593 LAST_UPDATED_BY = x_user_id,
594 REQUEST_ID =
595 DECODE(x_request_id, 0, REQUEST_ID, x_request_id),
596 PROGRAM_APPLICATION_ID =
597 DECODE(x_appl_id, 0, PROGRAM_APPLICATION_ID, x_appl_id),
598 PROGRAM_ID =
599 DECODE(x_program_id, 0, PROGRAM_ID, x_program_id),
600 PROGRAM_UPDATE_DATE =
601 DECODE(x_program_id, 0, PROGRAM_UPDATE_DATE, SYSDATE)
602 WHERE ORGANIZATION_ID = p_org_id
603 AND WIP_ENTITY_ID = x_wip_id
604 AND REPETITIVE_SCHEDULE_ID = p_sched_id;
605
606 END IF;
607
608 END IF;
609
610 END split_schedule;
611
612 /*==========================================================================+
613 |
614 | Roll_Forward
615 | Roll forward schedule
616 |
617 | PARAMETERS
618 | x_closed_sched_id Id of closed schedule
619 | x_rollfwd_sched_id Id of schedule being roll forward
620 | x_rollfwd_type roll forward types:
621 | ROLL_EC_IMP, ROLL_COMPLETE, ROLL_CANCEL
622 | x_org_id Org Id
623 | x_update_status True if want status to be updated
624 | x_class_code Class code
625 *===========================================================================*/
626
627 PROCEDURE roll_forward
628 (p_closed_sched_id IN NUMBER,
629 p_rollfwd_sched_id IN OUT NOCOPY NUMBER,
630 p_rollfwd_type IN NUMBER,
631 p_org_id IN NUMBER,
632 p_update_status IN BOOLEAN) IS
633
634 x_date_reqd DATE;
635 x_wip_id NUMBER;
636 x_line_id NUMBER;
637 x_class_code VARCHAR2(11);
638 x_closed_status_type NUMBER;
639 x_rollfwd_status_type NUMBER;
640 x_rollfwd_first_op NUMBER;
641 x_user_id NUMBER;
642 x_login_id NUMBER;
643 x_request_id NUMBER;
644 x_appl_id NUMBER;
645 x_program_id NUMBER;
646 x_qty_completed NUMBER;
647 x_rollfwd_qty NUMBER;
648 x_found_next_sched BOOLEAN := TRUE;
649
650 -- Constants --
651 NUM_DAYS_IN_10_YEARS CONSTANT NUMBER := 3650;
652
653 CURSOR gen_info IS
654 SELECT wrs.wip_entity_id,
655 wrs.line_id,
656 decode(p_rollfwd_type,
657 WIP_CONSTANTS.ROLL_EC_IMP,
658 0, wrs.quantity_completed)
659 FROM wip_repetitive_schedules wrs
660 WHERE wrs.organization_id = p_org_id
661 AND wrs.repetitive_schedule_id =
662 decode(p_rollfwd_type, WIP_CONSTANTS.ROLL_EC_IMP, p_rollfwd_sched_id,
663 p_closed_sched_id);
664
665 CURSOR get_nxt is
666 SELECT r1.repetitive_schedule_id,
667 r1.status_type,
668 r1.daily_production_rate * r1.processing_work_days,
669 r1.first_unit_start_date
670 FROM wip_repetitive_schedules r1,
671 wip_repetitive_schedules r2,
672 wip_parameters p
673 WHERE r1.organization_id = p_org_id
674 AND r2.organization_id = p_org_id
675 AND p.organization_id = p_org_id
676 AND r2.repetitive_schedule_id = p_closed_sched_id
677 AND r1.wip_entity_id = x_wip_id
678 AND r1.line_id = x_line_id
679 AND r1.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
680 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
681 AND r1.first_unit_start_date > r2.last_unit_start_date
682 AND r1.first_unit_start_date <= SYSDATE +
683 decode(r1.status_type, WIP_CONSTANTS.UNRELEASED,
684 p.autorelease_days,NUM_DAYS_IN_10_YEARS)
685 ORDER BY r1.first_unit_start_date;
686
687 CURSOR roll_ec IS
688 SELECT wrs.status_type,
689 wrs.daily_production_rate * wrs.processing_work_days,
690 wrs.first_unit_start_date
691 FROM wip_repetitive_schedules wrs
692 WHERE wrs.organization_id = p_org_id
693 AND wrs.repetitive_schedule_id = p_rollfwd_sched_id;
694
695 CURSOR nxt_fstop IS
696 SELECT nvl(min(wo.operation_seq_num), 1)
697 FROM wip_operations wo
698 WHERE wo.organization_id = p_org_id
699 AND wo.wip_entity_id = x_wip_id
700 AND wo.repetitive_schedule_id = p_rollfwd_sched_id;
701
702 CURSOR per_bal IS
703 SELECT WRI.class_code
704 FROM WIP_REPETITIVE_ITEMS WRI
705 WHERE WRI.ORGANIZATION_ID = p_org_id
706 AND WRI.WIP_ENTITY_ID = x_wip_id
707 AND WRI.LINE_ID = x_line_id;
708
709 x_dummy boolean;
710 x_rel VARCHAR2(100);
711 x_info VARCHAR2(100);
712
713 BEGIN
714
715 -- populate who columns --
716 x_user_id := fnd_global.user_id;
717 x_login_id := fnd_global.login_id;
718 x_request_id := fnd_global.conc_request_id;
719 x_appl_id := fnd_global.prog_appl_id;
720 x_program_id := fnd_global.conc_program_id;
721
722 -- get the assembly and line id of the closed schedule --
723 open gen_info;
724 fetch gen_info INTO x_wip_id, x_line_id, x_qty_completed;
725 close gen_info;
726
727 IF ((p_rollfwd_type = WIP_CONSTANTS.ROLL_COMPLETE) OR
728 (p_rollfwd_type = WIP_CONSTANTS.ROLL_CANCEL)) THEN
729
730 -- get the next schedule with start date after the last unit date of
731 -- the closed schedule
732 open get_nxt;
733 fetch get_nxt INTO p_rollfwd_sched_id, x_rollfwd_status_type,
734 x_rollfwd_qty, x_date_reqd;
735 if get_nxt%NOTFOUND then
736 x_found_next_sched := FALSE;
737 end if;
738 close get_nxt;
739
740 IF ((x_found_next_sched) AND
741 (x_rollfwd_status_type = WIP_CONSTANTS.UNRELEASED)) THEN
742
743 wip_change_status.check_repetitive_routing(x_wip_id,
744 p_org_id,
745 p_rollfwd_sched_id,
746 x_line_id);
747 END IF;
748
749 IF (p_rollfwd_type = WIP_CONSTANTS.ROLL_COMPLETE) THEN
750 IF (x_found_next_sched) THEN
751 x_closed_status_type := WIP_CONSTANTS.COMP_NOCHRG;
752 ELSE
753 x_closed_status_type := WIP_CONSTANTS.COMP_CHRG;
754 END IF;
755 ELSIF (p_rollfwd_type = WIP_CONSTANTS.ROLL_CANCEL) THEN
756 x_closed_status_type := WIP_CONSTANTS.CANCELLED;
757 END IF;
758
759 ELSIF (p_rollfwd_type = WIP_CONSTANTS.ROLL_EC_IMP) THEN
760 open roll_ec;
761 fetch roll_ec INTO x_rollfwd_status_type, x_rollfwd_qty,
762 x_date_reqd;
763 if roll_ec%NOTFOUND then
764 x_found_next_sched := FALSE;
765 end if;
766 close roll_ec;
767 END IF;
768
769 IF (x_found_next_sched) THEN
770
771 -- get the first operation of the next schedule
772
773 open nxt_fstop;
774 fetch nxt_fstop INTO x_rollfwd_first_op;
775 close nxt_fstop;
776
777 -- set the quantity issued as well as others info for the next
778 -- shedule in wip_requirement operation
779 UPDATE wip_requirement_operations new
780 SET new.quantity_issued =
781 (SELECT new.quantity_issued +
782 nvl(max(wro.quantity_issued -
783 (x_qty_completed * wro.quantity_per_assembly)), 0)
784 FROM wip_requirement_operations wro
785 WHERE wro.organization_id = p_org_id
786 AND wro.wip_entity_id = x_wip_id
787 AND wro.repetitive_schedule_id = p_closed_sched_id
788 AND new.inventory_item_id = wro.inventory_item_id
789 AND new.operation_seq_num = wro.operation_seq_num
790 AND wro.quantity_issued >
791 x_qty_completed * wro.quantity_per_assembly
792 AND wro.required_quantity >
793 decode(p_rollfwd_type,
794 WIP_CONSTANTS.ROLL_EC_IMP, -1, 0)),
795 new.last_updated_by = x_user_id,
796 new.last_update_date = SYSDATE,
797 new.request_id =
798 decode(x_request_id, 0, new.request_id, x_request_id),
799 new.program_application_id =
800 decode(x_appl_id, 0, new.program_application_id, x_appl_id),
801 new.program_id =
802 decode(x_program_id, 0, new.program_id, x_program_id),
803 new.program_update_date =
804 decode(x_program_id, 0, new.program_update_date, SYSDATE)
805 WHERE new.organization_id = p_org_id
806 AND new.wip_entity_id = x_wip_id
807 AND new.repetitive_schedule_id = p_rollfwd_sched_id;
808
809 -- insert into wip_requirement_operations
810
811 INSERT INTO wip_requirement_operations
812 (inventory_item_id, organization_id,
813 wip_entity_id, operation_seq_num,
814 repetitive_schedule_id, last_update_date,
815 last_updated_by, creation_date,
816 created_by, last_update_login,
817 request_id, program_application_id,
818 program_id, program_update_date,
819 component_sequence_id, department_id,
820 wip_supply_type, date_required,
821 required_quantity, quantity_issued,
822 quantity_per_assembly, comments,
823 supply_subinventory, supply_locator_id,
824 mrp_net_flag, mps_date_required,
825 mps_required_quantity,
826 segment1, segment2, segment3, segment4,
827 segment5, segment6, segment7, segment8,
828 segment9, segment10, segment11, segment12,
829 segment13, segment14, segment15, segment16,
830 segment17, segment18, segment19, segment20,
831 attribute_category, attribute1, attribute2,
832 attribute3, attribute4, attribute5,
833 attribute6, attribute7, attribute8,
834 attribute9, attribute10, attribute11,
835 attribute12, attribute13, attribute14,
836 attribute15)
837 SELECT wro.inventory_item_id, wro.organization_id,
838 wro.wip_entity_id, wro.operation_seq_num,
839 p_rollfwd_sched_id, SYSDATE,
840 x_user_id, SYSDATE,
841 x_user_id, x_login_id,
842 DECODE(x_request_id, 0, '', x_request_id),
843 DECODE(x_appl_id, 0, '', x_appl_id),
844 DECODE(x_program_id, 0, '', x_program_id),
845 DECODE(x_program_id, 0, '', SYSDATE),
846 wro.component_sequence_id, wo.department_id,
847 wro.wip_supply_type,
848 nvl(wo.first_unit_start_date, x_date_reqd),
849 0, wro.quantity_issued -
850 (x_qty_completed * wro.quantity_per_assembly),
851 0, wro.comments,
852 wro.supply_subinventory, wro.supply_locator_id,
853 wro.mrp_net_flag, wro.mps_date_required,
854 wro.mps_required_quantity,
855 wro.segment1, wro.segment2, wro.segment3, wro.segment4,
856 wro.segment5, wro.segment6, wro.segment7, wro.segment8,
857 wro.segment9, wro.segment10, wro.segment11, wro.segment12,
858 wro.segment13, wro.segment14, wro.segment15, wro.segment16,
859 wro.segment17, wro.segment18, wro.segment19, wro.segment20,
860 wro.attribute_category, wro.attribute1, wro.attribute2,
861 wro.attribute3, wro.attribute4, wro.attribute5,
862 wro.attribute6, wro.attribute7, wro.attribute8,
863 wro.attribute9, wro.attribute10, wro.attribute11,
864 wro.attribute12, wro.attribute13, wro.attribute14,
865 wro.attribute15
866 FROM wip_requirement_operations wro,
867 wip_operations wo
868 WHERE wro.organization_id = p_org_id
869 AND wo.organization_id (+) = p_org_id
870 AND wro.wip_entity_id = x_wip_id
871 AND wo.wip_entity_id (+) = x_wip_id
872 AND wro.repetitive_schedule_id = p_closed_sched_id
873 AND wo.repetitive_schedule_id (+) = p_rollfwd_sched_id
874 AND wro.operation_seq_num = wo.operation_seq_num (+)
875 AND wro.quantity_issued > x_qty_completed *
876 wro.quantity_per_assembly
877 AND wro.required_quantity > decode(p_rollfwd_type,
878 WIP_CONSTANTS.ROLL_EC_IMP, -1, 0)
879 AND NOT EXISTS
880 (SELECT 'does the requirement already exist?'
881 FROM wip_requirement_operations wro1
882 WHERE wro1.inventory_item_id = wro.inventory_item_id
883 AND wro1.operation_seq_num = wro.operation_seq_num
884 AND wro1.organization_id = p_org_id
885 AND wro1.wip_entity_id = x_wip_id
886 AND wro1.repetitive_schedule_id = p_rollfwd_sched_id);
887
888 -- update closed schedule in wip_requirement_operations
889
890 UPDATE wip_requirement_operations wro
891 SET wro.quantity_issued = x_qty_completed * wro.quantity_per_assembly,
892 wro.last_update_date = SYSDATE,
893 wro.last_updated_by = x_user_id,
894 wro.request_id = DECODE(x_request_id, 0, wro.request_id,
895 x_request_id),
896 wro.program_application_id =
897 decode(x_appl_id, 0, wro.program_application_id, x_appl_id),
898 wro.program_id = decode(x_program_id, 0, wro.program_id,
899 x_program_id),
900 wro.program_update_date =
901 decode(x_program_id, 0, wro.program_update_date, SYSDATE)
902 WHERE wro.organization_id = p_org_id
903 AND wro.wip_entity_id = x_wip_id
904 AND wro.repetitive_schedule_id = p_closed_sched_id
905 AND wro.quantity_issued > x_qty_completed * wro.quantity_per_assembly
906 AND wro.required_quantity > DECODE(p_rollfwd_type,
907 WIP_CONSTANTS.ROLL_EC_IMP, -1, 0);
908
909 END IF;
910
911 IF (p_update_status) THEN
912 IF ((p_rollfwd_type = WIP_CONSTANTS.ROLL_COMPLETE) OR
913 (p_rollfwd_type = WIP_CONSTANTS.ROLL_CANCEL)) THEN
914
915 -- set the closed status of closed schedule
916 UPDATE wip_repetitive_schedules wrs
917 SET wrs.status_type = x_closed_status_type,
918 wrs.date_closed =
919 decode(x_closed_status_type,
920 WIP_CONSTANTS.COMP_CHRG,
921 wrs.date_closed, SYSDATE),
922 wrs.last_updated_by = x_user_id,
923 wrs.last_update_date = SYSDATE,
924 wrs.request_id = decode(x_request_id, 0, wrs.request_id,
925 x_request_id),
926 wrs.program_application_id =
927 decode(x_appl_id, 0, wrs.program_application_id,
928 x_appl_id),
929 wrs.program_id = decode(x_program_id, 0, wrs.program_id,
930 x_program_id),
931 wrs.program_update_date =
932 decode(x_program_id, 0, wrs.program_update_date,
933 SYSDATE)
934 WHERE wrs.organization_id = p_org_id
935 AND wrs.repetitive_schedule_id = p_closed_sched_id;
936
937 END IF;
938 END IF;
939
940 IF (x_rollfwd_status_type = WIP_CONSTANTS.UNRELEASED) THEN
941
942 -- release next schedule
943 UPDATE wip_repetitive_schedules wrs
944 SET wrs.status_type = WIP_CONSTANTS.RELEASED,
945 wrs.date_released = SYSDATE,
946 wrs.last_update_date = SYSDATE,
947 wrs.last_updated_by = x_user_id,
948 wrs.request_id = decode(x_request_id, 0, wrs.request_id,
949 x_request_id),
950 wrs.program_application_id =
951 decode(x_appl_id, 0, wrs.program_application_id, x_appl_id),
952 wrs.program_id = decode(x_program_id, 0, wrs.program_id,
953 x_program_id),
954 wrs.program_update_date =
955 decode(x_program_id, 0, wrs.program_update_date, SYSDATE)
956 WHERE wrs.organization_id = p_org_id
957 AND wrs.repetitive_schedule_id = p_rollfwd_sched_id;
958
959 --- 12/94 get class code for wipipb
960 open per_bal;
961 fetch per_bal INTO x_class_code;
962 close per_bal;
963
964 wip_change_status.insert_period_balances(x_wip_id,
965 p_org_id, p_rollfwd_sched_id,
966 x_line_id, x_class_code);
967
968 x_dummy := fnd_release.get_release(x_rel, x_info);
969
970 if instr(x_info, 'SC') <> 0 then
971 wip_osp.release_validation(x_wip_id, p_org_id, p_rollfwd_sched_id);
972 end if;
973
974 -- set quantity in the queue
975
976 UPDATE wip_operations wo
977 SET wo.quantity_in_queue = x_rollfwd_qty,
978 wo.last_update_date = SYSDATE,
979 wo.last_updated_by = x_user_id,
980 wo.request_id = decode(x_request_id, 0, wo.request_id,
981 x_request_id),
982 wo.program_application_id =
983 decode(x_appl_id, 0, wo.program_application_id, x_appl_id),
984 wo.program_id = decode(x_program_id, 0, wo.program_id,
985 x_program_id),
986 wo.program_update_date =
987 decode(x_program_id, 0, wo.program_update_date, SYSDATE)
988 WHERE wo.organization_id = p_org_id
989 AND wo.wip_entity_id = x_wip_id
990 AND wo.repetitive_schedule_id = p_rollfwd_sched_id
991 AND wo.operation_seq_num = x_rollfwd_first_op;
992
993 END IF;
994
995 END ROLL_FORWARD;
996
997 PROCEDURE ROLL_FORWARD_COVER
998 (p_closed_sched_id IN NUMBER,
999 p_rollfwd_sched_id IN NUMBER,
1000 p_rollfwd_type IN NUMBER,
1001 p_org_id IN NUMBER,
1002 p_update_status IN NUMBER,
1003 p_success_flag OUT NOCOPY NUMBER,
1004 p_error_msg OUT NOCOPY VARCHAR2) IS
1005 x_sched NUMBER := p_rollfwd_sched_id;
1006 x_update_status BOOLEAN;
1007 BEGIN
1008
1009 IF p_update_status = 1 THEN
1010 x_update_status := TRUE;
1011 ELSE
1012 x_update_status := FALSE;
1013 END IF;
1014
1015 ROLL_FORWARD(p_closed_sched_id,
1016 x_sched,
1017 p_rollfwd_type,
1018 p_org_id,
1019 x_update_status);
1020
1021 p_success_flag := 1;
1022
1023 EXCEPTION
1024
1025 WHEN OTHERS THEN
1026 p_success_flag := 0;
1027 p_error_msg := FND_MESSAGE.get;
1028
1029 END ROLL_FORWARD_COVER;
1030
1031 PROCEDURE get_first_last_sched
1032 ( p_wip_entity_id IN NUMBER,
1033 p_org_id IN NUMBER,
1034 p_line_id IN NUMBER,
1035 x_first_sched_id OUT NOCOPY NUMBER,
1036 x_last_sched_id OUT NOCOPY NUMBER,
1037 x_error_mesg OUT NOCOPY VARCHAR2) is
1038 CURSOR first_sched IS
1039 select wrs.repetitive_schedule_id
1040 from wip_repetitive_schedules wrs
1041 where wrs.organization_id = p_org_id
1042 and wrs.wip_entity_id = p_wip_entity_id
1043 and wrs.line_id = p_line_id
1044 and wrs.status_type in (3,4)
1045 order by wrs.LAST_UNIT_START_DATE asc;
1046
1047 CURSOR last_sched IS
1048 select wrs.repetitive_schedule_id
1049 from wip_repetitive_schedules wrs
1050 where wrs.organization_id = p_org_id
1051 and wrs.wip_entity_id = p_wip_entity_id
1052 and wrs.line_id = p_line_id
1053 and wrs.status_type in (3,4)
1054 order by wrs.LAST_UNIT_START_DATE desc;
1055
1056 BEGIN
1057 x_first_sched_id := NULL;
1058 x_last_sched_id := NULL;
1059
1060 x_error_mesg := NULL;
1061
1062 OPEN first_sched;
1063 FETCH first_sched into x_first_sched_id;
1064
1065 IF( first_sched%NOTFOUND) then
1066 fnd_message.set_name('WIP', 'WIP_INT_ERROR_NO_SCHED ');
1067 fnd_message.set_token('ROUTINE','get_first_last_sched');
1068 x_error_mesg := fnd_message.get ;
1069 CLOSE first_sched;
1070 return;
1071 END IF;
1072
1073 OPEN last_sched;--if first schedule exists, last exists
1074 FETCH last_sched into x_last_sched_id;
1075
1076 CLOSE first_sched;
1077 CLOSE last_sched;
1078 END get_first_last_sched ;
1079
1080 FUNCTION get_line_id
1081 ( p_rep_sched_id IN NUMBER,
1082 p_org_id IN NUMBER) RETURN NUMBER IS
1083
1084 l_line_id number;
1085 cursor get_line (c_rep_sched_id number,
1086 c_org_id number) is
1087 select line_id
1088 from wip_repetitive_schedules wrs
1089 where wrs.repetitive_schedule_id = p_rep_sched_id
1090 and wrs.organization_id = p_org_id;
1091
1092 BEGIN
1093 if p_rep_sched_id = NULL then
1094 return NULL;
1095 end if;
1096
1097 open get_line(p_rep_sched_id, p_org_id);
1098 fetch get_line into l_line_id;
1099
1100 if (get_line%NOTFOUND) then
1101 l_line_id := NULL;
1102 end if;
1103
1104 CLOSE get_line;
1105 return l_line_id;
1106
1107 END get_line_id;
1108
1109
1110 END WIP_REPETITIVE_UTILITIES;