[Home] [Help]
PACKAGE BODY: APPS.WIP_CHANGE_STATUS
Source
1 PACKAGE BODY WIP_CHANGE_STATUS AS
2 /* $Header: wippcstb.pls 120.5 2007/10/15 23:00:52 kkonada 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
59 BEGIN
60 IF P_repetitive_schedule_id IS NULL THEN
61 INSERT INTO WIP_PERIOD_BALANCES
62 (ACCT_PERIOD_ID, WIP_ENTITY_ID,
63 LAST_UPDATE_DATE, LAST_UPDATED_BY,
64 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
65 ORGANIZATION_ID, CLASS_TYPE,
66 TL_RESOURCE_IN, TL_OVERHEAD_IN,
67 TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
68 PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
69 PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
70 TL_MATERIAL_OUT, TL_RESOURCE_OUT,
71 TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
72 PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
73 PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
74 PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
75 PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
76 PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
77 TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
78 TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
79 TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
80 SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id,
81 SYSDATE, X_user_id,
82 SYSDATE, X_user_id, X_login_id,
83 P_organization_id, WC.CLASS_TYPE,
84 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
85 0, 0, 0, 0, 0, 0, 0, 0
86 FROM ORG_ACCT_PERIODS OAP,
87 WIP_ACCOUNTING_CLASSES WC
88 WHERE WC.CLASS_CODE = P_class_code
89 AND WC.ORGANIZATION_ID = P_organization_id
90 AND OAP.ORGANIZATION_ID = P_organization_id
91 AND OAP.SCHEDULE_CLOSE_DATE >=
92 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_release_date,
93 P_organization_id))
94 AND OAP.PERIOD_CLOSE_DATE IS NULL
95 AND NOT EXISTS
96 (SELECT 'balance record already there'
97 FROM WIP_PERIOD_BALANCES WPB
98 WHERE WPB.WIP_ENTITY_ID = P_wip_entity_id
99 AND WPB.ACCT_PERIOD_ID = OAP.ACCT_PERIOD_ID
100 AND WPB.ORGANIZATION_ID = OAP.ORGANIZATION_ID);
101 ELSE
102 INSERT INTO WIP_PERIOD_BALANCES
103 (ACCT_PERIOD_ID, WIP_ENTITY_ID, REPETITIVE_SCHEDULE_ID,
104 LAST_UPDATE_DATE, LAST_UPDATED_BY,
105 CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN,
106 ORGANIZATION_ID, CLASS_TYPE,
107 TL_RESOURCE_IN, TL_OVERHEAD_IN,
108 TL_OUTSIDE_PROCESSING_IN, PL_MATERIAL_IN,
109 PL_MATERIAL_OVERHEAD_IN, PL_RESOURCE_IN,
110 PL_OVERHEAD_IN, PL_OUTSIDE_PROCESSING_IN,
111 TL_MATERIAL_OUT, TL_RESOURCE_OUT,
112 TL_OVERHEAD_OUT, TL_OUTSIDE_PROCESSING_OUT,
113 PL_MATERIAL_OUT, PL_MATERIAL_OVERHEAD_OUT,
114 PL_RESOURCE_OUT, PL_OVERHEAD_OUT,
115 PL_OUTSIDE_PROCESSING_OUT, PL_MATERIAL_OVERHEAD_VAR,
116 PL_MATERIAL_VAR, PL_OUTSIDE_PROCESSING_VAR,
117 PL_OVERHEAD_VAR, PL_RESOURCE_VAR,
118 TL_MATERIAL_VAR, TL_OUTSIDE_PROCESSING_VAR,
119 TL_OVERHEAD_VAR, TL_RESOURCE_VAR,
120 TL_MATERIAL_OVERHEAD_OUT, TL_MATERIAL_OVERHEAD_VAR)
121 SELECT OAP.ACCT_PERIOD_ID, P_wip_entity_id, P_repetitive_schedule_id,
122 SYSDATE, X_user_id,
123 SYSDATE, X_user_id, X_login_id,
124 P_organization_id, WC.CLASS_TYPE,
125 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
126 0, 0, 0, 0, 0, 0, 0, 0
127 FROM ORG_ACCT_PERIODS OAP,
128 WIP_ACCOUNTING_CLASSES WC
129 WHERE WC.CLASS_CODE = P_class_code
130 AND WC.ORGANIZATION_ID = P_organization_id
131 AND OAP.ORGANIZATION_ID = P_organization_id
132 AND OAP.PERIOD_CLOSE_DATE IS NULL
133 AND OAP.SCHEDULE_CLOSE_DATE >=
134 (SELECT NVL(MIN(
135 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(DATE_RELEASED,
136 P_organization_id))),
137 TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(P_RELEASE_DATE,
138 P_organization_id)))
139 FROM WIP_REPETITIVE_SCHEDULES
140 WHERE WIP_ENTITY_ID = P_wip_entity_id
141 AND ORGANIZATION_ID = P_organization_id
142 AND LINE_ID = P_line_id
143 AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED,
144 WIP_CONSTANTS.COMP_CHRG,
145 WIP_CONSTANTS.HOLD));
146 END IF;
147
148 /* It is possible that no records can be inserted in discrete
149 even if there is an open accounting period. This can happen when
150 reexploding jobs of Status failed load that were defined as released.
151 It can also happen if you unclose a job that was released in the current
152 accounting period.
153 The explicit rollback is needed by the Define Discrete form to roll
154 back other commit logic that we dont want to be executed again if
155 the user tries to recommit in the same session.
156 */
157
158 IF SQL%NOTFOUND THEN
159 IF P_repetitive_schedule_id IS NOT NULL THEN
160 FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ACCT_PERIOD');
161 ROLLBACK;
162 APP_EXCEPTION.RAISE_EXCEPTION;
163
164 ELSE
165 DECLARE
166 CURSOR C1 IS
167 SELECT 'x'
168 FROM ORG_ACCT_PERIODS
169 WHERE ORGANIZATION_ID = P_Organization_Id
170 AND TRUNC(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(
171 P_RELEASE_DATE,P_Organization_Id))
172 BETWEEN PERIOD_START_DATE AND SCHEDULE_CLOSE_DATE
173 AND PERIOD_CLOSE_DATE IS NULL;
174 dummy VARCHAR2(2);
175 BEGIN
176 OPEN C1;
177 FETCH C1 INTO dummy;
178 IF C1%NOTFOUND THEN
179 CLOSE C1;
180 FND_MESSAGE.SET_NAME('WIP', 'WIP_NO_ACCT_PERIOD');
181 ROLLBACK;
182 APP_EXCEPTION.RAISE_EXCEPTION;
183
184 END IF;
185 CLOSE C1;
186 END;
187 END IF;
188 END IF;
189
190 END INSERT_PERIOD_BALANCES;
191
192
193 PROCEDURE CHECK_REPETITIVE_ROUTING
194 (P_wip_entity_id NUMBER,
195 P_organization_id NUMBER,
196 P_repetitive_schedule_id NUMBER,
197 P_line_id NUMBER) IS
198
199 X_dummy VARCHAR2(30) := 'different routing';
200 X_diff_routing BOOLEAN := FALSE;
201
202 CURSOR check_routing IS
203 SELECT 'identical routing'
204 FROM wip_operations wo1,
205 wip_operations wo2,
206 wip_repetitive_schedules wrs
207 WHERE wrs.organization_id = P_organization_id
208 AND wo1.organization_id = P_organization_id
209 AND wo2.organization_id = P_organization_id
210 AND wrs.wip_entity_id = P_wip_entity_id
211 AND wo1.wip_entity_id = P_wip_entity_id
212 AND wo2.wip_entity_id = P_wip_entity_id
213 AND wo1.repetitive_schedule_id = P_repetitive_schedule_id
214 AND wrs.repetitive_schedule_id = wo2.repetitive_schedule_id
215 AND wrs.line_id = P_line_id
216 AND wrs.status_type in (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
217 WIP_CONSTANTS.HOLD)
218 AND wo1.operation_seq_num = wo2.operation_seq_num
219 AND wo1.department_id = wo2.department_id
220 AND wo1.count_point_type = wo2.count_point_type
221 AND wo1.backflush_flag = wo2.backflush_flag
222 HAVING count(*) =
223 (SELECT count(*)
224 FROM wip_operations O,
225 wip_repetitive_schedules S
226 WHERE O.organization_id = P_organization_id
227 AND S.organization_id = P_organization_id
228 AND O.wip_entity_id = P_wip_entity_id
229 AND S.wip_entity_id = P_wip_entity_id
230 AND S.line_id = P_line_id
231 AND S.status_type in (WIP_CONSTANTS.RELEASED,
232 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
233 AND O.repetitive_schedule_id = P_repetitive_schedule_id)
234 AND count(*) =
235 (SELECT count(*)
236 FROM wip_operations O,
237 wip_repetitive_schedules S
238 WHERE O.organization_id = P_organization_id
239 AND S.organization_id = P_organization_id
240 AND O.wip_entity_id = P_wip_entity_id
241 AND S.wip_entity_id = P_wip_entity_id
242 AND S.line_id = P_line_id
243 AND S.status_type in (WIP_CONSTANTS.RELEASED,
244 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
245 AND O.repetitive_schedule_id = S.repetitive_schedule_id);
246
247 BEGIN
248 LOCK TABLE WIP_VALID_INTRAOPERATION_STEPS IN EXCLUSIVE MODE;
249 OPEN check_routing;
250 FETCH check_routing INTO X_dummy;
251 X_diff_routing := check_routing%NOTFOUND;
252 CLOSE check_routing;
253 IF X_diff_routing THEN
254 FND_MESSAGE.SET_NAME('WIP', 'WIP_SAME_ROUTING');
255 APP_EXCEPTION.RAISE_EXCEPTION;
256 END IF;
257 END CHECK_REPETITIVE_ROUTING;
258
259
260 PROCEDURE RELEASE
261 (P_wip_entity_id NUMBER,
262 P_organization_id NUMBER,
263 P_repetitive_schedule_id NUMBER,
264 P_line_id NUMBER,
265 P_class_code VARCHAR2,
266 P_old_status_type NUMBER,
267 P_new_status_type NUMBER,
268 P_routing_exists OUT NOCOPY NUMBER,
269 P_release_date DATE DEFAULT SYSDATE) IS /* fix for bug 2424987 */
270
271 X_tot_op_qty number; /* For Bug 5859224 */
272
273 BEGIN
274 /* Bug 4955616. Removed WIP_CONSTANTS.CANCELLED from new status list and old status list*/
275 IF (P_new_status_type IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
276 WIP_CONSTANTS.HOLD) AND
277 P_old_status_type NOT IN (WIP_CONSTANTS.RELEASED,
278 WIP_CONSTANTS.COMP_CHRG,
279 WIP_CONSTANTS.HOLD)) THEN
280
281 INSERT_PERIOD_BALANCES (P_wip_entity_id, P_organization_id,
282 P_repetitive_schedule_id, P_line_id,
283 P_class_code, P_Release_Date); /* fix for bug 2424987 */
284
285 /*
286 Fix bug#2034660. Commented following call to wip_osp.release_validation.
287 Now wilmlx.ppc would be calling this procedure after processing rows in wip_job_dtls_interface
288
289
290
291 WIP_OSP.RELEASE_VALIDATION(P_Wip_Entity_Id,
292 P_Organization_Id,
293 P_Repetitive_Schedule_Id);
294
295 */
296
297 /* For Bug 5859224: load_queue API would be called only if the sum of quantity_in_queue,quantity_running
298 and quantity_completed of first operation is zero */
299 BEGIN
300 SELECT (nvl(QUANTITY_IN_QUEUE,0) + nvl(QUANTITY_RUNNING,0) + nvl(QUANTITY_COMPLETED,0))
301 INTO x_tot_op_qty FROM WIP_OPERATIONS
302 WHERE WIP_ENTITY_ID = P_wip_entity_id
303 AND ORGANIZATION_ID = P_organization_id
304 AND REPETITIVE_SCHEDULE_ID IS NULL
305 AND ROWNUM = 1 --Bug 6052835: EAM Work orders can have multiple start (independant) operations
306 AND PREVIOUS_OPERATION_SEQ_NUM IS NULL;
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 x_tot_op_qty := 0;
310 END;
311
312
313
314 IF x_tot_op_qty = 0 then
315 LOAD_QUEUE (P_wip_entity_id, P_organization_id,
316 P_repetitive_schedule_id, P_routing_exists);
317 END IF;
318
319 IF P_repetitive_schedule_id IS NOT NULL THEN
320 IF (P_new_status_type IN (WIP_CONSTANTS.RELEASED,
321 WIP_CONSTANTS.COMP_CHRG,
322 WIP_CONSTANTS.HOLD) AND
323 P_old_status_type NOT IN (WIP_CONSTANTS.RELEASED,
324 WIP_CONSTANTS.COMP_CHRG,
325 WIP_CONSTANTS.HOLD)) THEN
326 CHECK_REPETITIVE_ROUTING (P_wip_entity_id, P_organization_id,
327 P_repetitive_schedule_id, P_line_id);
328 END IF;
329 END IF;
330 END IF;
331 END RELEASE;
332
333
334 PROCEDURE PUT_JOB_ON_HOLD
335 (P_wip_entity_id NUMBER,
336 P_organization_id NUMBER) IS
337
338 CURSOR disc_info IS
339 SELECT STATUS_TYPE,
340 CLASS_CODE
341 FROM WIP_DISCRETE_JOBS
342 WHERE WIP_ENTITY_ID = P_wip_entity_id
343 AND ORGANIZATION_ID = P_organization_id
344 AND STATUS_TYPE IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
345 WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD,
346 WIP_CONSTANTS.PEND_SCHED);
347
348 X_status_type NUMBER;
349 X_class_code VARCHAR2(10) := '';
350 X_valid_job BOOLEAN;
351 X_routing_exists NUMBER;
352 X_user_id NUMBER := FND_GLOBAL.USER_ID;
353 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
354
355 BEGIN
356 OPEN disc_info;
357 FETCH disc_info INTO X_status_type, X_class_code;
358 X_valid_job := disc_info%FOUND;
359 CLOSE disc_info;
360
361 IF NOT X_valid_job THEN
362 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_HOLD');
363 APP_EXCEPTION.RAISE_EXCEPTION;
364 END IF;
365 RELEASE (P_wip_entity_id, P_organization_id, '', '',
366 X_class_code, X_status_type, WIP_CONSTANTS.HOLD,
367 X_routing_exists, SYSDATE); /* fix for bug 2424987 */
368
369 /* Fix for Bug#2034660 .Calling wip_osp.release_validation since release is not calling this procedure */
370
371 if x_status_type NOT IN (WIP_CONSTANTS.RELEASED,
372 WIP_CONSTANTS.COMP_CHRG,
373 WIP_CONSTANTS.HOLD,
374 WIP_CONSTANTS.CANCELLED) THEN
375 wip_osp.release_validation ( P_wip_entity_id, P_organization_id, '') ;
376
377 end if ;
378
379 UPDATE WIP_DISCRETE_JOBS
380 SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
381 DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
382 LAST_UPDATE_DATE = SYSDATE,
383 LAST_UPDATED_BY = X_user_id,
384 LAST_UPDATE_LOGIN = X_login_id
385 WHERE WIP_ENTITY_ID = P_wip_entity_id
386 AND ORGANIZATION_ID = P_organization_id;
387 END PUT_JOB_ON_HOLD;
388
389
390 PROCEDURE PUT_LINE_ON_HOLD
391 (P_wip_entity_id NUMBER,
392 P_line_id NUMBER,
393 P_organization_id NUMBER) IS
394
395 X_valid_schedule BOOLEAN := TRUE;
396 X_user_id NUMBER := FND_GLOBAL.USER_ID;
397 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
398
399 BEGIN
400 UPDATE WIP_REPETITIVE_SCHEDULES
401 SET STATUS_TYPE = WIP_CONSTANTS.HOLD,
402 LAST_UPDATE_DATE = SYSDATE,
403 LAST_UPDATED_BY = X_user_id,
404 LAST_UPDATE_LOGIN = X_login_id
405 WHERE LINE_ID = P_line_id
406 AND WIP_ENTITY_ID = P_wip_entity_id
407 AND ORGANIZATION_ID = P_organization_id
408 AND STATUS_TYPE IN (WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG,
409 WIP_CONSTANTS.HOLD);
410 END PUT_LINE_ON_HOLD;
411
412 PROCEDURE RELEASE_MES_WRAPPER
413 (P_wip_entity_id NUMBER,
414 P_organization_id NUMBER ) IS
415
416 CURSOR disc_info IS
417 SELECT STATUS_TYPE,
418 CLASS_CODE
419 FROM WIP_DISCRETE_JOBS
420 WHERE WIP_ENTITY_ID = P_wip_entity_id
421 AND ORGANIZATION_ID = P_organization_id;
422
423
424 X_status_type NUMBER;
425 X_class_code VARCHAR2(10) := '';
426 X_valid_job BOOLEAN;
427 X_routing_exists NUMBER;
428 X_user_id NUMBER := FND_GLOBAL.USER_ID;
429 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
430
431 BEGIN
432 OPEN disc_info;
433 FETCH disc_info INTO X_status_type, X_class_code;
434 X_valid_job := disc_info%FOUND;
435 CLOSE disc_info;
436
437 IF NOT X_valid_job THEN
438 FND_MESSAGE.SET_NAME('WIP', 'WIP_QA_ACTION_NO_HOLD');
439 APP_EXCEPTION.RAISE_EXCEPTION;
440 END IF;
441
442 RELEASE (P_wip_entity_id, P_organization_id, '', '',
443 X_class_code, X_status_type, WIP_CONSTANTS.RELEASED,
444 X_routing_exists, SYSDATE);
445
446
447 UPDATE WIP_DISCRETE_JOBS
448 SET STATUS_TYPE = WIP_CONSTANTS.RELEASED,
449 DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
450 LAST_UPDATE_DATE = SYSDATE,
451 LAST_UPDATED_BY = X_user_id,
452 LAST_UPDATE_LOGIN = X_login_id
453 WHERE WIP_ENTITY_ID = P_wip_entity_id
454 AND ORGANIZATION_ID = P_organization_id;
455
456 END RELEASE_MES_WRAPPER;
457
458
459 END WIP_CHANGE_STATUS;