[Home] [Help]
PACKAGE BODY: APPS.WIP_UNRELEASE
Source
1 PACKAGE BODY WIP_UNRELEASE AS
2 /* $Header: wippunrb.pls 120.6.12020000.5 2013/01/10 09:31:26 sjallipa ship $ */
3
4 PROCEDURE UNRELEASE(x_org_id IN NUMBER,
5 x_wip_id IN NUMBER,
6 x_rep_id IN NUMBER DEFAULT -1,
7 x_line_id IN NUMBER DEFAULT -1,
8 x_ent_type IN NUMBER) IS
9
10 ops_exist VARCHAR2(2);
11 charges_exist VARCHAR2(2);
12 po_req_exist VARCHAR2(20);
13
14 cursor check_discrete_charges is
15 SELECT DISTINCT 'X'
16 FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
17 WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
18 AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
19 AND DJ.WIP_ENTITY_ID = x_wip_id
20 AND DJ.ORGANIZATION_ID = x_org_id
21 AND (DJ.QUANTITY_COMPLETED <> 0
22 OR DJ.QUANTITY_SCRAPPED <> 0
23 OR VERIFY_WPB(x_org_id, x_wip_id) <>0 /*Added for BUG 7325661 (FP 6721407)*/
24 /*Commented for BUG 7325661 (FP 6721407) OR WPB.TL_RESOURCE_IN <> 0
25 OR WPB.TL_OVERHEAD_IN <> 0
26 OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
27 OR WPB.PL_MATERIAL_IN <> 0
28 OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
29 OR WPB.PL_RESOURCE_IN <> 0
30 OR WPB.PL_OVERHEAD_IN <> 0
31 OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
32 OR WPB.TL_MATERIAL_OUT <> 0
33 OR WPB.TL_RESOURCE_OUT <> 0
34 OR WPB.TL_OVERHEAD_OUT <> 0
35 OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
36 OR WPB.PL_MATERIAL_OUT <> 0
37 OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
38 OR WPB.PL_RESOURCE_OUT <> 0
39 OR WPB.PL_OVERHEAD_OUT <> 0
40 OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0*/
41 OR EXISTS (SELECT 'X'
42 FROM WIP_REQUIREMENT_OPERATIONS
43 WHERE ORGANIZATION_ID = x_org_id
44 AND WIP_ENTITY_ID = x_wip_id
45 AND QUANTITY_ISSUED <> 0)
46 OR EXISTS (SELECT 'X'
47 FROM WIP_MOVE_TXN_INTERFACE
48 WHERE ORGANIZATION_ID = x_org_id
49 AND WIP_ENTITY_ID = x_wip_id)
50 OR EXISTS (SELECT 'X'
51 FROM WIP_COST_TXN_INTERFACE
52 WHERE ORGANIZATION_ID = x_org_id
53 AND WIP_ENTITY_ID = x_wip_id)
54 OR EXISTS (SELECT 'X'
55 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
56 WHERE ORGANIZATION_ID = x_org_id
57 AND TRANSACTION_SOURCE_TYPE_ID = 5
58 AND TRANSACTION_SOURCE_ID = x_wip_id)
59 OR EXISTS (SELECT 'X'
60 FROM WIP_OPERATION_RESOURCES
61 WHERE ORGANIZATION_ID = x_org_id
62 AND WIP_ENTITY_ID = x_wip_id
63 AND APPLIED_RESOURCE_UNITS <> 0)
64 OR EXISTS (SELECT 'X' /*Bug 5462655 - Added to check uncosted/erred out transactions in MMT. */
65 FROM MTL_MATERIAL_TRANSACTIONS
66 WHERE ORGANIZATION_ID = x_org_id
67 AND TRANSACTION_SOURCE_TYPE_ID = 5
68 AND TRANSACTION_SOURCE_ID = x_wip_id
69 AND COSTED_FLAG IN ('N', 'E'))
70 /* Fix for Bug - 7197320(FP of 6691421) - Added to check if move transaction exist for job (Check if quantites
71 are present in any intra-operation step, other than first operation's Queue) */
72 OR EXISTS (SELECT 'X' FROM
73 (SELECT Decode (
74 (Sum(Decode(PREVIOUS_OPERATION_SEQ_NUM,NULL,0,QUANTITY_IN_QUEUE)) +
75 Sum(QUANTITY_RUNNING) +
76 Sum(QUANTITY_WAITING_TO_MOVE) +
77 Sum(QUANTITY_REJECTED) +
78 Sum(QUANTITY_SCRAPPED)), NULL, 'Y', 0, NULL, 'X') Result
79 FROM WIP_OPERATIONS
80 WHERE ORGANIZATION_ID = x_org_id
81 AND WIP_ENTITY_ID = x_wip_id) WHERE Result = 'X' ));
82
83 cursor check_repetitive_charges is
84 SELECT 'X'
85 FROM WIP_REPETITIVE_SCHEDULES RS, WIP_PERIOD_BALANCES WPB
86 WHERE RS.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
87 AND RS.ORGANIZATION_ID = WPB.ORGANIZATION_ID
88 AND RS.REPETITIVE_SCHEDULE_ID =
89 WPB.REPETITIVE_SCHEDULE_ID
90 AND RS.WIP_ENTITY_ID = x_wip_id
91 AND RS.ORGANIZATION_ID = x_org_id
92 AND RS.REPETITIVE_SCHEDULE_ID = x_rep_id
93 AND (RS.QUANTITY_COMPLETED <> 0
94 OR VERIFY_WPB(x_org_id, x_wip_id,x_rep_id) <>0 /*Added for bug 7325661 (FP 6721407)*/
95 /*OR WPB.TL_RESOURCE_IN <> 0 Removed for bug 7325661 (FP 6721407)
96 OR WPB.TL_OVERHEAD_IN <> 0
97 OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
98 OR WPB.PL_MATERIAL_IN <> 0
99 OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
100 OR WPB.PL_RESOURCE_IN <> 0
101 OR WPB.PL_OVERHEAD_IN <> 0
102 OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
103 OR WPB.TL_MATERIAL_OUT <> 0
104 OR WPB.TL_RESOURCE_OUT <> 0
105 OR WPB.TL_OVERHEAD_OUT <> 0
106 OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
107 OR WPB.PL_MATERIAL_OUT <> 0
108 OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
109 OR WPB.PL_RESOURCE_OUT <> 0
110 OR WPB.PL_OVERHEAD_OUT <> 0
111 OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0*/
112 OR EXISTS
113 (SELECT 'X'
114 FROM WIP_REQUIREMENT_OPERATIONS
115 WHERE ORGANIZATION_ID = x_org_id
116 AND WIP_ENTITY_ID = x_wip_id
117 AND REPETITIVE_SCHEDULE_ID = x_rep_id
118 AND QUANTITY_ISSUED <> 0)
119 OR EXISTS
120 (SELECT 'X'
121 FROM WIP_MOVE_TXN_INTERFACE
122 WHERE ORGANIZATION_ID = x_org_id
123 AND WIP_ENTITY_ID = x_wip_id
124 AND LINE_ID = x_line_id)
125 OR EXISTS
126 (SELECT 'X'
127 FROM WIP_COST_TXN_INTERFACE
128 WHERE ORGANIZATION_ID = x_org_id
129 AND WIP_ENTITY_ID = x_wip_id
130 AND LINE_ID = x_line_id)
131 OR EXISTS
132 (SELECT 'X'
133 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
134 WHERE ORGANIZATION_ID = x_org_id
135 AND TRANSACTION_SOURCE_ID = x_wip_id
136 AND TRANSACTION_SOURCE_TYPE_ID = 5
137 AND REPETITIVE_LINE_ID = x_line_id)
138 OR EXISTS
139 (SELECT 'X'
140 FROM WIP_OPERATIONS
141 WHERE WIP_ENTITY_ID = x_wip_id
142 AND ORGANIZATION_ID = x_org_id
143 AND REPETITIVE_SCHEDULE_ID = x_rep_id
144 AND QUANTITY_SCRAPPED <> 0)
145 OR EXISTS
146 (SELECT 'X'
147 FROM WIP_OPERATION_RESOURCES
148 WHERE ORGANIZATION_ID = x_org_id
149 AND WIP_ENTITY_ID = x_wip_id
150 AND REPETITIVE_SCHEDULE_ID = x_rep_id
151 AND APPLIED_RESOURCE_UNITS <> 0));
152 -- for bug fix 8977276 (FP 8946106)
153 CURSOR c_lock
154 IS select '1'
155 FROM WIP_OPERATIONS
156 WHERE WIP_ENTITY_ID = x_wip_id
157 AND ORGANIZATION_ID = x_org_id
158 for update nowait;
159
160 BEGIN
161
162 open c_lock; -- for bug fix 8977276 (FP 8946106)
163
164 IF (WIP_OSP.PO_REQ_EXISTS( p_wip_entity_id => x_wip_id
165 ,p_rep_sched_id => x_rep_id
166 ,p_organization_id => x_org_id
167 ,p_entity_type => x_ent_type ) = TRUE) THEN
168 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED_OPEN_PO');
169 raise fnd_api.g_exc_unexpected_error;/*Bug 9709677 */
170 END IF;
171 -- Bug 15945996
172 IF (WIP_WS_TIME_ENTRY.IS_CLOCK_PENDING(x_wip_id,null)<>'N') THEN
173 FND_MESSAGE.SET_NAME('WIP','WIP_PENDING_CLOCKS');
174 raise fnd_api.g_exc_unexpected_error;
175 END IF;
176 /* IF (WIP_WS_TIME_ENTRY.IS_JOB_ON(x_wip_id)='Y') THEN
177 FND_MESSAGE.SET_NAME('WIP','WIP_PENDING_JOB_ON');
178 raise fnd_api.g_exc_unexpected_error;
179 END IF;*/
180
181 IF (x_ent_type = 1) THEN
182 open check_discrete_charges;
183 fetch check_discrete_charges into charges_exist;
184
185 IF (check_discrete_charges%NOTFOUND) THEN
186
187 UPDATE WIP_OPERATIONS
188 SET QUANTITY_WAITING_TO_MOVE = 0,
189 QUANTITY_SCRAPPED = 0,
190 QUANTITY_REJECTED = 0,
191 QUANTITY_IN_QUEUE = 0,
192 QUANTITY_RUNNING = 0,
193 QUANTITY_COMPLETED = 0,
194 CUMULATIVE_SCRAP_QUANTITY = 0, /*Enh#2864382*/
195 PROGRESS_PERCENTAGE = NULL /* Bug#3318428*/
196 WHERE WIP_ENTITY_ID = x_wip_id
197 AND ORGANIZATION_ID = x_org_id;
198 ELSE
199 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
200 raise fnd_api.g_exc_unexpected_error;/*Bug 9709677 */
201 close check_discrete_charges;
202 RETURN;
203 END IF;
204
205 close check_discrete_charges;
206
207 ELSIF (x_ent_type = 2) THEN
208
209 open check_repetitive_charges;
210 fetch check_repetitive_charges into charges_exist;
211
212 IF (check_repetitive_charges%NOTFOUND) THEN
213 UPDATE WIP_OPERATIONS
214 SET QUANTITY_WAITING_TO_MOVE = 0,
215 QUANTITY_SCRAPPED = 0,
216 QUANTITY_REJECTED = 0,
217 QUANTITY_IN_QUEUE = 0,
218 QUANTITY_RUNNING = 0,
219 QUANTITY_COMPLETED = 0,
220 CUMULATIVE_SCRAP_QUANTITY = 0 /*Enh#2864382*/
221 WHERE WIP_ENTITY_ID = x_wip_id
222 AND ORGANIZATION_ID = x_org_id
223 AND REPETITIVE_SCHEDULE_ID = x_rep_id;
224 ELSE
225 FND_MESSAGE.SET_NAME('WIP','WIP_UNRLS_JOB/SCHED');
226 raise fnd_api.g_exc_unexpected_error;/*Bug 9709677 */
227 close check_repetitive_charges;
228 RETURN;
229 END IF;
230
231 close check_repetitive_charges;
232
233 END IF; -- end ent_type
234
235 DELETE FROM wip_period_balances
236 WHERE wip_entity_id = x_wip_id
237 AND NVL(repetitive_schedule_id, -1) =
238 NVL(x_rep_id, -1)
239 AND organization_id = x_org_id;
240
241 -- Undo changes to WRO as a result of Overcompletion
242 wip_overcompletion.undo_overcompletion
243 ( p_org_id => x_org_id,
244 p_wip_entity_id => x_wip_id,
245 p_rep_id => x_rep_id);
246
247 close c_lock;-- for bug fix 8977276 (FP 8946106)
248
249 -- for bug fix 8977276 (FP 8946106)
250 EXCEPTION
251 WHEN wip_constants.records_locked THEN
252 if(c_lock%ISOPEN) then
253 close c_lock;
254 end if;
255 fnd_message.set_name('WIP', 'WIP_LOCKED_ROW_ALREADY_LOCKED');
256 APP_EXCEPTION.RAISE_EXCEPTION;
257
258 /*Bug 9709677 */
259 WHEN fnd_api.g_exc_unexpected_error THEN
260 if(check_discrete_charges%ISOPEN) then
261 close check_discrete_charges;
262 end if;
263 if(check_repetitive_charges%ISOPEN) then
264 close check_repetitive_charges;
265 end if;
266 APP_EXCEPTION.RAISE_EXCEPTION;
267
268 when others then
269 if(c_lock%ISOPEN) then
270 close c_lock;
271 end if;
272 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
273 fnd_message.set_token('ERROR_TEXT', 'WIP_UNRELEASE.UNRELEASE: ' || SQLERRM);
274 APP_EXCEPTION.RAISE_EXCEPTION;/*Bug 9709677 */
275
276 END unrelease;
277
278 PROCEDURE UNRELEASE_MES_WRAPPER
279 (P_wip_entity_id NUMBER,
280 P_organization_id NUMBER
281 ) IS
282
283 X_user_id NUMBER := FND_GLOBAL.USER_ID;
284 X_login_id NUMBER := FND_GLOBAL.LOGIN_ID;
285 BEGIN
286 UNRELEASE(x_org_id =>P_organization_id,
287 x_wip_id =>P_wip_entity_id,
288 x_ent_type =>WIP_CONSTANTS.DISCRETE);
289
290
291 UPDATE WIP_DISCRETE_JOBS
292 SET STATUS_TYPE = WIP_CONSTANTS.UNRELEASED,
293 DATE_RELEASED = NVL(DATE_RELEASED, SYSDATE),
294 LAST_UPDATE_DATE = SYSDATE,
295 LAST_UPDATED_BY = X_user_id,
296 LAST_UPDATE_LOGIN = X_login_id
297 WHERE WIP_ENTITY_ID = P_wip_entity_id
298 AND ORGANIZATION_ID = P_organization_id;
299
300 END UNRELEASE_MES_WRAPPER;
301
302 /*Added FUNCTION VERIFY_WPB for bug 7325661 (FP 6721407)*/
303 FUNCTION VERIFY_WPB(x_org_id IN NUMBER,
304 x_wip_id IN NUMBER,
305 x_rep_id IN NUMBER DEFAULT NULL) RETURN NUMBER IS
306
307 L_TL_RESOURCE_IN NUMBER;
308 L_TL_OVERHEAD_IN NUMBER;
309 L_TL_OUTSIDE_PROCESSING_IN NUMBER;
310 L_PL_MATERIAL_IN NUMBER;
311 L_PL_MATERIAL_OVERHEAD_IN NUMBER;
312 L_PL_RESOURCE_IN NUMBER;
313 L_PL_OVERHEAD_IN NUMBER;
314 L_PL_OUTSIDE_PROCESSING_IN NUMBER;
315 L_TL_MATERIAL_OUT NUMBER;
316 L_TL_RESOURCE_OUT NUMBER;
317 L_TL_OVERHEAD_OUT NUMBER;
318 L_TL_OUTSIDE_PROCESSING_OUT NUMBER;
319 L_PL_MATERIAL_OUT NUMBER;
320 L_PL_MATERIAL_OVERHEAD_OUT NUMBER;
321 L_PL_RESOURCE_OUT NUMBER;
322 L_PL_OVERHEAD_OUT NUMBER;
323 L_PL_OUTSIDE_PROCESSING_OUT NUMBER;
324
325 BEGIN
326
327 SELECT sum(TL_RESOURCE_IN),
328 sum(TL_OVERHEAD_IN),
329 sum(TL_OUTSIDE_PROCESSING_IN),
330 sum(PL_MATERIAL_IN),
331 sum(PL_MATERIAL_OVERHEAD_IN),
332 sum(PL_RESOURCE_IN),
333 sum(PL_OVERHEAD_IN),
334 sum(PL_OUTSIDE_PROCESSING_IN),
335 sum(TL_MATERIAL_OUT),
336 sum(TL_RESOURCE_OUT),
337 sum(TL_OVERHEAD_OUT),
338 sum(TL_OUTSIDE_PROCESSING_OUT),
339 sum(PL_MATERIAL_OUT),
340 sum(PL_MATERIAL_OVERHEAD_OUT),
341 sum(PL_RESOURCE_OUT),
342 sum(PL_OVERHEAD_OUT),
343 sum(PL_OUTSIDE_PROCESSING_OUT)
344 INTO L_TL_RESOURCE_IN ,
345 L_TL_OVERHEAD_IN ,
346 L_TL_OUTSIDE_PROCESSING_IN ,
347 L_PL_MATERIAL_IN ,
348 L_PL_MATERIAL_OVERHEAD_IN ,
349 L_PL_RESOURCE_IN ,
350 L_PL_OVERHEAD_IN ,
351 L_PL_OUTSIDE_PROCESSING_IN ,
352 L_TL_MATERIAL_OUT ,
353 L_TL_RESOURCE_OUT ,
354 L_TL_OVERHEAD_OUT ,
355 L_TL_OUTSIDE_PROCESSING_OUT ,
356 L_PL_MATERIAL_OUT ,
357 L_PL_MATERIAL_OVERHEAD_OUT ,
358 L_PL_RESOURCE_OUT ,
359 L_PL_OVERHEAD_OUT ,
360 L_PL_OUTSIDE_PROCESSING_OUT
361 FROM wip_period_balances
362 WHERE wip_entity_id = x_wip_id
363 AND organization_id=x_org_id
364 AND nvl(repetitive_schedule_id , -1) = nvl(x_rep_id, -1);
365
366 IF (L_TL_RESOURCE_IN = 0 AND
367 L_TL_OVERHEAD_IN = 0 AND
368 L_TL_OUTSIDE_PROCESSING_IN = 0 AND
369 L_PL_MATERIAL_IN = 0 AND
370 L_PL_MATERIAL_OVERHEAD_IN = 0 AND
371 L_PL_RESOURCE_IN = 0 AND
372 L_PL_OVERHEAD_IN = 0 AND
373 L_PL_OUTSIDE_PROCESSING_IN = 0 AND
374 L_TL_MATERIAL_OUT = 0 AND
375 L_TL_RESOURCE_OUT = 0 AND
376 L_TL_OVERHEAD_OUT = 0 AND
377 L_TL_OUTSIDE_PROCESSING_OUT = 0 AND
378 L_PL_MATERIAL_OUT = 0 AND
379 L_PL_MATERIAL_OVERHEAD_OUT = 0 AND
380 L_PL_RESOURCE_OUT = 0 AND
381 L_PL_OVERHEAD_OUT = 0 AND
382 L_PL_OUTSIDE_PROCESSING_OUT = 0 ) THEN
383 RETURN 0;
384 ELSE
385 RETURN 1;
386 END IF;
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 RETURN 1;
391 END;
392
393 END WIP_UNRELEASE;