[Home] [Help]
PACKAGE BODY: APPS.WIP_CHANGE_STATUS
Source
1 PACKAGE BODY WIP_CHANGE_STATUS AS
2 /* $Header: wippcstb.pls 120.12.12020000.3 2013/01/10 09:36:53 sjallipa ship $ */
3
4
5 PROCEDURE LOAD_QUEUE
6 (P_wip_entity_id NUMBER,
7 P_organization_id NUMBER,
8 P_repetitive_schedule_id NUMBER,
9 P_routing_exists OUT NOCOPY NUMBER) IS
10
11 X_user_id NUMBER := FND_GLOBAL.USER_ID;
12 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
13
14 BEGIN
15 IF (P_repetitive_schedule_id IS NULL) THEN
16 UPDATE WIP_OPERATIONS
17 SET QUANTITY_IN_QUEUE =
18 SCHEDULED_QUANTITY -
19 (QUANTITY_IN_QUEUE + QUANTITY_RUNNING + QUANTITY_COMPLETED),
20 LAST_UPDATED_BY = X_user_id,
21 LAST_UPDATE_DATE = SYSDATE,
22 LAST_UPDATE_LOGIN = X_login_id
23 WHERE WIP_ENTITY_ID = P_wip_entity_id
24 AND ORGANIZATION_ID = P_organization_id
25 AND REPETITIVE_SCHEDULE_ID IS NULL
26 AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
27 ELSE
28 UPDATE WIP_OPERATIONS
29 SET QUANTITY_IN_QUEUE =
30 SCHEDULED_QUANTITY -
31 (QUANTITY_IN_QUEUE + QUANTITY_RUNNING + QUANTITY_COMPLETED),
32 LAST_UPDATED_BY = X_user_id,
33 LAST_UPDATE_DATE = SYSDATE,
34 LAST_UPDATE_LOGIN = X_login_id
35 WHERE WIP_ENTITY_ID = P_wip_entity_id
36 AND ORGANIZATION_ID = P_organization_id
37 AND REPETITIVE_SCHEDULE_ID = P_repetitive_schedule_id
38 AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
39 END IF;
40 IF SQL%NOTFOUND THEN
41 P_routing_exists := WIP_CONSTANTS.NO;
42 ELSE
43 P_routing_exists := WIP_CONSTANTS.YES;
44 END IF;
45 END LOAD_QUEUE;
46
47
48 PROCEDURE INSERT_PERIOD_BALANCES
49 (P_wip_entity_id NUMBER,
50 P_organization_id NUMBER,
51 P_repetitive_schedule_id NUMBER,
52 P_line_id NUMBER,
53 P_class_code VARCHAR2,
54 P_release_date DATE DEFAULT SYSDATE) IS
55
56 X_user_id NUMBER := FND_GLOBAL.USER_ID;
57 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
58 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
59 x_returnStatus varchar2(1);
60 l_params wip_logger.param_tbl_t;
61
62 BEGIN
63 if (l_logLevel <= wip_constants.trace_logging) then
64 l_params(1).paramName := 'P_wip_entity_id';
65 l_params(1).paramValue := P_wip_entity_id;
66 l_params(2).paramName := 'P_organization_id';
67 l_params(2).paramValue := P_organization_id;
68 l_params(3).paramName := 'P_repetitive_schedule_id';
69 l_params(3).paramValue := P_repetitive_schedule_id;
70 l_params(4).paramName := 'P_line_id';
71 l_params(4).paramValue := P_line_id;
72 l_params(5).paramName := 'P_class_code';
73 l_params(5).paramValue := P_class_code;
74 l_params(6).paramName := 'P_release_date';
75 l_params(6).paramValue := P_release_date;
76
77 wip_logger.entryPoint(p_procName => 'WIP_CHANGE_STATUS.INSERT_PERIOD_BALANCES',
78 p_params => l_params,
79 x_returnStatus => x_returnStatus);
80 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
81 raise fnd_api.g_exc_unexpected_error;
82 end if;
83 end if;
84
85
86 IF P_repetitive_schedule_id IS NULL THEN
87 INSERT INTO WIP_PERIOD_BALANCES
88 (ACCT_PERIOD_ID, WIP_ENTITY_ID,
89 LAST_UPDATE_DATE, LAST_UPDATED_BY,
90 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
91 ORGANIZATION_ID, CLASS_TYPE,
92 TL_RESOURCE_IN, TL_OVERHEAD_IN,
93 TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
94 PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
95 PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
96 TL_MATERIAL_OUT, TL_RESOURCE_OUT,
97 TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
98 PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
99 PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
100 PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
101 PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
102 PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
103 TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
104 TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
105 TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
106 SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id,
107 SYSDATE, X_user_id,
108 SYSDATE, X_user_id, X_login_id,
109 P_organization_id, WC.CLASS_TYPE,
110 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
111 0, 0, 0, 0, 0, 0, 0, 0
112 FROM ORG_ACCT_PERIODS OAP,
113 WIP_ACCOUNTING_CLASSES WC
114 WHERE WC.CLASS_CODE = P_class_code
115 AND WC.ORGANIZATION_ID = P_organization_id
116 AND OAP.ORGANIZATION_ID = P_organization_id
117 AND OAP.SCHEDULE_CLOSE_DATE >=
118 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_release_date,
119 P_organization_id))
120 AND OAP.PERIOD_CLOSE_DATE IS NULL
121 AND NOT EXISTS
122 (SELECT 'balance record already there'
123 FROM WIP_PERIOD_BALANCES WPB
124 WHERE WPB.WIP_ENTITY_ID = P_wip_entity_id
125 AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
126 AND WPB.ORGANIZATION_ID = OAP.ORGANIZATION_ID);
127
128 if (l_logLevel <= wip_constants.trace_logging) then
129 wip_logger.log(p_msg => SQL%ROWCOUNT ||' of rows inserted into WPB ',
130 x_returnStatus => x_returnStatus);
131 end if;
132
133 ELSE
134 INSERT INTO WIP_PERIOD_BALANCES
135 (ACCT_PERIOD_ID, WIP_ENTITY_ID, REPETITIVE_SCHEDULE_ID,
136 LAST_UPDATE_DATE, LAST_UPDATED_BY,
137 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
138 ORGANIZATION_ID, CLASS_TYPE,
139 TL_RESOURCE_IN, TL_OVERHEAD_IN,
140 TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
141 PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
142 PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
143 TL_MATERIAL_OUT, TL_RESOURCE_OUT,
144 TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
145 PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
146 PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
147 PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
148 PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
149 PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
150 TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
151 TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
152 TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
153 SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id, P_repetitive_schedule_id,
154 SYSDATE, X_user_id,
155 SYSDATE, X_user_id, X_login_id,
156 P_organization_id, WC.CLASS_TYPE,
157 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
158 0, 0, 0, 0, 0, 0, 0, 0
159 FROM ORG_ACCT_PERIODS OAP,
160 WIP_ACCOUNTING_CLASSES WC
161 WHERE WC.CLASS_CODE = P_class_code
162 AND WC.ORGANIZATION_ID = P_organization_id
163 AND OAP.ORGANIZATION_ID = P_organization_id
164 AND OAP.PERIOD_CLOSE_DATE IS NULL
165 AND OAP.SCHEDULE_CLOSE_DATE >=
166 (SELECT NVL(MIN(
167 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(DATE_RELEASED,
168 P_organization_id))),
169 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_RELEASE_DATE,
170 P_organization_id)))
171 FROM WIP_REPETITIVE_SCHEDULES
172 WHERE WIP_ENTITY_ID = P_wip_entity_id
173 AND ORGANIZATION_ID = P_organization_id
174 AND LINE_ID = P_line_id
175 AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED,
176 WIP_CONSTANTS.COMP_CHRG,
177 WIP_CONSTANTS.HOLD));
178 if (l_logLevel <= wip_constants.trace_logging) then
179 wip_logger.log(p_msg => SQL%ROWCOUNT ||' of rows inserted into WPB ',
180 x_returnStatus => x_returnStatus);
181 end if;
182 END IF;
183
184 /* It is possible that no records can be inserted in discrete
185 even if there is an open accounting period. This can happen when
186 reexploding jobs of Status failed load that were defined as released.
187 It can also happen if you unclose a job that was released in the current
188 accounting period.
189 The explicit rollback is needed by the Define Discrete form to roll
190 back other commit logic that we dont want to be executed again if
191 the user tries to recommit in the same session.
192 */
193
194 IF SQL%NOTFOUND THEN
195 IF P_repetitive_schedule_id IS NOT NULL THEN
196
197 if (l_logLevel <= wip_constants.trace_logging) then
198 wip_logger.log(p_msg => 'WIP_NO_ACCT_PERIOD exception' ,
199 x_returnStatus => x_returnStatus);
200 end if;
201 FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ACCT_PERIOD');
202 ROLLBACK;
203 APP_EXCEPTION.RAISE_EXCEPTION;
204
205 ELSE
206 DECLARE
207 CURSOR C1 IS
208 SELECT 'x'
209 FROM ORG_ACCT_PERIODS
210 WHERE ORGANIZATION_ID = P_Organization_Id
211 AND TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
212 P_RELEASE_DATE,P_Organization_Id))
213 BETWEEN PERIOD_START_DATE AND SCHEDULE_CLOSE_DATE
214 AND PERIOD_CLOSE_DATE IS NULL;
215 dummy VARCHAR2(2);
216 BEGIN
217 OPEN C1;
218 FETCH C1 INTO dummy;
219 IF C1%NOTFOUND THEN
220 CLOSE C1;
221 if (l_logLevel <= wip_constants.trace_logging) then
222 wip_logger.log(p_msg => 'no open accouting period for the released date pass in, raise exception' ,
223 x_returnStatus => x_returnStatus);
224 end if;
225 FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ACCT_PERIOD');
226 ROLLBACK;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228
229 END IF;
230 CLOSE C1;
231 END;
232 END IF;
233 END IF;
234
235
236 if (l_logLevel <= wip_constants.trace_logging) then
237 wip_logger.exitPoint(p_procName => 'WIP_CHANGE_STATUS.INSERT_PERIOD_BALANCES',
238 p_procReturnStatus => 'S',
239 p_msg => 'Finished!',
240 x_returnStatus => x_returnStatus); --discard logging return status
241 end if;
242
243 END INSERT_PERIOD_BALANCES;
244
245
246 PROCEDURE CHECK_REPETITIVE_ROUTING
247 (P_wip_entity_id NUMBER,
248 P_organization_id NUMBER,
249 P_repetitive_schedule_id NUMBER,
250 P_line_id NUMBER) IS
251
252 X_dummy VARCHAR2(30) := 'different routing';
253 X_diff_routing BOOLEAN := FALSE;
254
255 CURSOR check_routing IS
256 SELECT 'identical routing'
257 FROM wip_operations wo1,
258 wip_operations wo2,
259 wip_repetitive_schedules wrs
260 WHERE wrs.organization_id = P_organization_id
261 AND wo1.organization_id = P_organization_id
262 AND wo2.organization_id = P_organization_id
263 AND wrs.wip_entity_id = P_wip_entity_id
264 AND wo1.wip_entity_id = P_wip_entity_id
265 AND wo2.wip_entity_id = P_wip_entity_id
266 AND wo1.repetitive_schedule_id = P_repetitive_schedule_id
267 AND wrs.repetitive_schedule_id = wo2.repetitive_schedule_id
268 AND wrs.line_id = P_line_id
269 AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
270 WIP_CONSTANTS.HOLD)
271 AND wo1.operation_seq_num = wo2.operation_seq_num
272 AND wo1.department_id = wo2.department_id
273 AND wo1.count_point_type = wo2.count_point_type
274 AND wo1.backflush_flag = wo2.backflush_flag
275 HAVING count(*) =
276 (SELECT count(*)
277 FROM wip_operations O,
278 wip_repetitive_schedules S
279 WHERE O.organization_id = P_organization_id
280 AND S.organization_id = P_organization_id
281 AND O.wip_entity_id = P_wip_entity_id
282 AND S.wip_entity_id = P_wip_entity_id
283 AND S.line_id = P_line_id
284 AND S.status_type in (WIP_CONSTANTS.RELEASED,
285 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
286 AND O.repetitive_schedule_id = P_repetitive_schedule_id)
287 AND count(*) =
288 (SELECT count(*)
289 FROM wip_operations O,
290 wip_repetitive_schedules S
291 WHERE O.organization_id = P_organization_id
292 AND S.organization_id = P_organization_id
293 AND O.wip_entity_id = P_wip_entity_id
294 AND S.wip_entity_id = P_wip_entity_id
298 AND O.repetitive_schedule_id = S.repetitive_schedule_id);
295 AND S.line_id = P_line_id
296 AND S.status_type in (WIP_CONSTANTS.RELEASED,
297 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
299
300
301 BEGIN
302 LOCK TABLE WIP_VALID_INTRAOPERATION_STEPS IN EXCLUSIVE MODE;
303 OPEN check_routing;
304 FETCH check_routing INTO X_dummy;
305 X_diff_routing := check_routing%NOTFOUND;
306 CLOSE check_routing;
307 IF X_diff_routing THEN
308 FND_MESSAGE.SET_NAME('WIP', 'WIP_SAME_ROUTING');
309 APP_EXCEPTION.RAISE_EXCEPTION;
310 END IF;
311 END CHECK_REPETITIVE_ROUTING;
312
313
314 PROCEDURE RELEASE
315 (P_wip_entity_id NUMBER,
316 P_organization_id NUMBER,
317 P_repetitive_schedule_id NUMBER,
318 P_line_id NUMBER,
319 P_class_code VARCHAR2,
320 P_old_status_type NUMBER,
321 P_new_status_type NUMBER,
322 P_routing_exists OUT NOCOPY NUMBER,
323 P_release_date DATE DEFAULT SYSDATE) IS /* fix for bug 2424987 */
324
325 X_tot_op_qty number; /* For Bug 5859224 */
326 l_release_date DATE; /* Bug 12849554 */
327 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
328 x_returnStatus varchar2(1);
329 l_params wip_logger.param_tbl_t;
330
331 BEGIN
332 if (l_logLevel <= wip_constants.trace_logging) then
333 l_params(1).paramName := 'P_wip_entity_id';
334 l_params(1).paramValue := P_wip_entity_id;
335 l_params(2).paramName := 'P_organization_id';
336 l_params(2).paramValue := P_organization_id;
337 l_params(3).paramName := 'P_repetitive_schedule_id';
338 l_params(3).paramValue := P_repetitive_schedule_id;
339 l_params(4).paramName := 'P_line_id';
340 l_params(4).paramValue := P_line_id;
341 l_params(5).paramName := 'P_class_code';
342 l_params(5).paramValue := P_class_code;
343 l_params(6).paramName := 'P_old_status_type';
344 l_params(6).paramValue := P_old_status_type;
345 l_params(7).paramName := 'P_new_status_type';
346 l_params(7).paramValue := P_new_status_type;
347 l_params(8).paramName := 'P_release_date';
348 l_params(8).paramValue := P_release_date;
349
350 wip_logger.entryPoint(p_procName => 'WIP_CHANGE_STATUS.RELEASE',
351 p_params => l_params,
352 x_returnStatus => x_returnStatus);
353 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
354 raise fnd_api.g_exc_unexpected_error;
355 end if;
356 end if;
357
358 /* Bug 13003859(FP of bug#12849554) when changing from on hold to release/complete, we want INSERT_PERIOD_BALANCES only inserting wpb for current acct period only to prevent backdated txn*/
359 IF(P_new_status_type IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG) AND P_old_status_type = WIP_CONSTANTS.HOLD) THEN
360 l_release_date := sysdate;
361 if (l_logLevel <= wip_constants.trace_logging) then
362 wip_logger.log(p_msg => 'Overiding released date to sysdate',
363 x_returnStatus => x_returnStatus);
364 end if;
365 ELSE
366 l_release_date := P_release_date;
367 END IF;
368
369
370 /* Bug 4955616. Removed WIP_CONSTANTS.CANCELLED from new status list and old status list*/
371 /* Bug 13004887(FP of 11935424 and 13003859) remove WIP_CONSTANTS.HOLD for EAM job. when change from hold to release for EAM work order,
372 EAM will call this method, we need to populate back all the missing WPB*/
373 IF (P_new_status_type IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
374 WIP_CONSTANTS.HOLD) AND
375 P_old_status_type NOT IN (WIP_CONSTANTS.RELEASED,
376 WIP_CONSTANTS.COMP_CHRG)) THEN
377
378 INSERT_PERIOD_BALANCES (P_wip_entity_id, P_organization_id,
379 P_repetitive_schedule_id, P_line_id,
380 P_class_code, l_Release_Date); /* fix for bug 2424987 *//* Bug 13003859 (FP of bug#12849554) */
381
382 if (l_logLevel <= wip_constants.trace_logging) then
383 wip_logger.log(p_msg => 'INSERT_PERIOD_BALANCES successfully',
384 x_returnStatus => x_returnStatus);
385 end if;
386
387 /*
388 Fix bug#2034660. Commented following call to wip_osp.release_validation.
389 Now wilmlx.ppc would be calling this procedure after processing rows in wip_job_dtls_interface
390
391
392
393 WIP_OSP.RELEASE_VALIDATION(P_Wip_Entity_Id,
394 P_Organization_Id,
395 P_Repetitive_Schedule_Id);
396
397 */
398
399 /*Bug 13004887(FP of 11935424 and 13003859) we only want to call INSERT_PERIOD_BALANCES when change EAM work orders from hold to release*/
400 IF(P_old_status_type <> WIP_CONSTANTS.HOLD) THEN
401 /* For Bug 5859224: load_queue API would be called only if the sum of quantity_in_queue,quantity_running
402 and quantity_completed of first operation is zero */
403 BEGIN
404 SELECT (nvl(QUANTITY_IN_QUEUE,0) + nvl(QUANTITY_RUNNING,0) + nvl(QUANTITY_COMPLETED,0))
405 INTO x_tot_op_qty FROM WIP_OPERATIONS
406 WHERE WIP_ENTITY_ID = P_wip_entity_id
407 AND ORGANIZATION_ID = P_organization_id
408 AND nvl(REPETITIVE_SCHEDULE_ID, 0) = nvl(p_repetitive_schedule_id, 0) --Bug 8670946
409 AND ROWNUM = 1 --Bug 6052835: EAM Work orders can have multiple start (independant) operations
410 AND PREVIOUS_OPERATION_SEQ_NUM IS NULL
411 for update nowait; /*Fix Bug 8977276 (FP 8946106)*/
412
413 /*For Bug 7511261:
414 Previously, when QUANTITY_IN_QUEUE, QUANTITY_RUNNING, QUANTITY_COMPLETED is not 0, the P_routing_exists
418 in WIP_OPERATIONS and so the P_routing_exists flag should be 1(WIP_CONSTANTS.YES). If it throws exception,
415 flag was not being set properly and is equal to NULL and resulted into the issue in bug 7304606.
416 If the sql SELECT (nvl(QUANTITY_IN_QUEUE,0) + nvl(QUANTITY_RUNNING,0) + nvl(QUANTITY_COMPLETED,0))
417 INTO x_tot_op_qty FROM WIP_OPERATIONS does not throw exception it implies that there is a routing exists
419 LOAD_QUEUE will handle the P_routing_exists flag
420 */
421 P_routing_exists := WIP_CONSTANTS.YES;
422
423
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 P_routing_exists := WIP_CONSTANTS.NO;
427 END;
428
429
430
431 IF (x_tot_op_qty = 0 AND P_routing_exists = WIP_CONSTANTS.YES)then
432 LOAD_QUEUE (P_wip_entity_id, P_organization_id,
433 P_repetitive_schedule_id, P_routing_exists);
434 END IF;
435
436 IF P_repetitive_schedule_id IS NOT NULL THEN
437 IF (P_new_status_type IN (WIP_CONSTANTS.RELEASED,
438 WIP_CONSTANTS.COMP_CHRG,
439 WIP_CONSTANTS.HOLD) AND
440 P_old_status_type NOT IN (WIP_CONSTANTS.RELEASED,
441 WIP_CONSTANTS.COMP_CHRG,
442 WIP_CONSTANTS.HOLD)) THEN
443 CHECK_REPETITIVE_ROUTING (P_wip_entity_id, P_organization_id,
444 P_repetitive_schedule_id, P_line_id);
445 END IF;
446 END IF;
447 END IF; /*Bug 13004887(FP of 11935424 and 13003859)*/
448 END IF;
449 if (l_logLevel <= wip_constants.trace_logging) then
450 wip_logger.exitPoint(p_procName => 'WIP_CHANGE_STATUS.RELEASE',
451 p_procReturnStatus => 'S',
452 p_msg => 'Finished!',
453 x_returnStatus => x_returnStatus); --discard logging return status
454 end if;
455
456 /*Fix Bug 8977276 (FP 8946106)*/
457 EXCEPTION
458 WHEN wip_constants.records_locked THEN
459 wip_logger.exitPoint(p_procName => 'WIP_CHANGE_STATUS.RELEASE',
460 p_procReturnStatus => 'E',
461 p_msg => 'WIP_LOCKED_ROW_ALREADY_LOCKED',
462 x_returnStatus => x_returnStatus);
463 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
464 FND_MESSAGE.raise_error;
465
466 END RELEASE;
467
468
469 PROCEDURE PUT_JOB_ON_HOLD
470 (P_wip_entity_id NUMBER,
471 P_organization_id NUMBER) IS
472
473 CURSOR disc_info IS
474 SELECT STATUS_TYPE,
475 CLASS_CODE
476 FROM WIP_DISCRETE_JOBS
477 WHERE WIP_ENTITY_ID = P_wip_entity_id
478 AND ORGANIZATION_ID = P_organization_id
479 AND STATUS_TYPE IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
480 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD,
481 WIP_CONSTANTS.PEND_SCHED);
482
483 X_status_type NUMBER;
484 X_class_code VARCHAR2(10) := '';
485 X_valid_job BOOLEAN;
486 X_routing_exists NUMBER;
487 X_user_id NUMBER := FND_GLOBAL.USER_ID;
488 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
489
490 BEGIN
491 OPEN disc_info;
492 FETCH disc_info INTO X_status_type, X_class_code;
493 X_valid_job := disc_info%FOUND;
494 CLOSE disc_info;
495
496 IF NOT X_valid_job THEN
497 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_HOLD');
498 APP_EXCEPTION.RAISE_EXCEPTION;
499 END IF;
500 RELEASE (P_wip_entity_id, P_organization_id, '', '',
501 X_class_code, X_status_type, WIP_CONSTANTS.HOLD,
502 X_routing_exists, SYSDATE); /* fix for bug 2424987 */
503
504 /* Fix for Bug#2034660 .Calling wip_osp.release_validation since release is not calling this procedure */
505
506 if x_status_type NOT IN (WIP_CONSTANTS.RELEASED,
507 WIP_CONSTANTS.COMP_CHRG,
508 WIP_CONSTANTS.HOLD,
509 WIP_CONSTANTS.CANCELLED) THEN
510 wip_osp.release_validation ( P_wip_entity_id, P_organization_id, '') ;
511
512 end if ;
513
514 -- Bug 15945996
515 IF (WIP_WS_TIME_ENTRY.IS_CLOCK_PENDING(P_wip_entity_id,null)<>'N') THEN
516 FND_MESSAGE.SET_NAME('WIP','WIP_PENDING_CLOCKS');
517 raise fnd_api.g_exc_unexpected_error;
518 END IF;
519 /* IF (WIP_WS_TIME_ENTRY.IS_JOB_ON(P_wip_entity_id)='Y') THEN
520 FND_MESSAGE.SET_NAME('WIP','WIP_PENDING_JOB_ON');
521 raise fnd_api.g_exc_unexpected_error;
522 END IF;*/
523
524 UPDATE WIP_DISCRETE_JOBS
525 SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
526 DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
527 LAST_UPDATE_DATE = SYSDATE,
528 LAST_UPDATED_BY = X_user_id,
529 LAST_UPDATE_LOGIN = X_login_id
530 WHERE WIP_ENTITY_ID = P_wip_entity_id
531 AND ORGANIZATION_ID = P_organization_id;
532 END PUT_JOB_ON_HOLD;
533
534
535 PROCEDURE PUT_LINE_ON_HOLD
536 (P_wip_entity_id NUMBER,
537 P_line_id NUMBER,
538 P_organization_id NUMBER) IS
539
540 X_valid_schedule BOOLEAN := TRUE;
541 X_user_id NUMBER := FND_GLOBAL.USER_ID;
542 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
543
544 BEGIN
545 UPDATE WIP_REPETITIVE_SCHEDULES
546 SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
547 LAST_UPDATE_DATE = SYSDATE,
548 LAST_UPDATED_BY = X_user_id,
549 LAST_UPDATE_LOGIN = X_login_id
550 WHERE LINE_ID = P_line_id
551 AND WIP_ENTITY_ID = P_wip_entity_id
555 END PUT_LINE_ON_HOLD;
552 AND ORGANIZATION_ID = P_organization_id
553 AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
554 WIP_CONSTANTS.HOLD);
556
557 PROCEDURE RELEASE_MES_WRAPPER
558 (P_wip_entity_id NUMBER,
559 P_organization_id NUMBER ) IS
560
561 CURSOR disc_info IS
562 SELECT STATUS_TYPE,
563 CLASS_CODE
564 FROM WIP_DISCRETE_JOBS
565 WHERE WIP_ENTITY_ID = P_wip_entity_id
566 AND ORGANIZATION_ID = P_organization_id;
567
568
569 X_status_type NUMBER;
570 X_class_code VARCHAR2(10) := '';
571 X_valid_job BOOLEAN;
572 X_routing_exists NUMBER;
573 X_user_id NUMBER := FND_GLOBAL.USER_ID;
574 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
575
576 BEGIN
577 OPEN disc_info;
578 FETCH disc_info INTO X_status_type, X_class_code;
579 X_valid_job := disc_info%FOUND;
580 CLOSE disc_info;
581
582 IF NOT X_valid_job THEN
583 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_HOLD');
584 APP_EXCEPTION.RAISE_EXCEPTION;
585 END IF;
586
587 RELEASE (P_wip_entity_id, P_organization_id, '', '',
588 X_class_code, X_status_type, WIP_CONSTANTS.RELEASED,
589 X_routing_exists, SYSDATE);
590
591 /*Add for bug 10013678*/
592 WIP_OSP.RELEASE_VALIDATION(P_wip_entity_id,
593 P_organization_id,
594 null);
595
596 UPDATE WIP_DISCRETE_JOBS
597 SET STATUS_TYPE = WIP_CONSTANTS.RELEASED,
598 DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
599 LAST_UPDATE_DATE = SYSDATE,
600 LAST_UPDATED_BY = X_user_id,
601 LAST_UPDATE_LOGIN = X_login_id
602 WHERE WIP_ENTITY_ID = P_wip_entity_id
603 AND ORGANIZATION_ID = P_organization_id;
604
605 END RELEASE_MES_WRAPPER;
606
607
608 END WIP_CHANGE_STATUS;