[Home] [Help]
PACKAGE BODY: APPS.WIP_OP_RESOURCES_UTILITIES
Source
1 PACKAGE BODY WIP_OP_RESOURCES_UTILITIES AS
2 /* $Header: wiporutb.pls 120.2 2005/11/30 01:17:35 panagara noship $ */
3
4 PROCEDURE Check_Unique(X_Wip_Entity_Id NUMBER,
5 X_Organization_Id NUMBER,
6 X_Operation_Seq_Num NUMBER,
7 X_Resource_Seq_Num NUMBER,
8 X_Repetitive_Schedule_Id NUMBER) IS
9 res_count NUMBER := 0;
10 subres_count NUMBER := 0;
11 cursor discrete_check is
12 SELECT count(*)
13 FROM WIP_OPERATION_RESOURCES
14 WHERE ORGANIZATION_ID = X_Organization_Id
15 AND WIP_ENTITY_ID = X_Wip_Entity_Id
16 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
17 AND RESOURCE_SEQ_NUM = X_Resource_Seq_Num;
18 cursor sub_discrete_check is
19 SELECT count(*)
20 FROM WIP_SUB_OPERATION_RESOURCES
21 WHERE ORGANIZATION_ID = X_Organization_Id
22 AND WIP_ENTITY_ID = X_Wip_Entity_Id
23 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
24 AND RESOURCE_SEQ_NUM = X_Resource_Seq_Num;
25 cursor repetitive_check is
26 SELECT count(*)
27 FROM WIP_OPERATION_RESOURCES
28 WHERE ORGANIZATION_ID = X_Organization_Id
29 AND WIP_ENTITY_ID = X_Wip_Entity_Id
30 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
31 AND RESOURCE_SEQ_NUM = X_Resource_Seq_Num
32 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id;
33 BEGIN
34 IF X_Repetitive_Schedule_Id IS NULL then
35 open discrete_check;
36 fetch discrete_check into res_count;
37 close discrete_check;
38 open sub_discrete_check;
39 fetch sub_discrete_check into subres_count;
40 close sub_discrete_check;
41 ELSE
42 open repetitive_check;
43 fetch repetitive_check into res_count;
44 close repetitive_check;
45 END IF;
46
47 IF (res_count <> 0) OR (subres_count <> 0) THEN
48 FND_MESSAGE.set_name('WIP', 'WIP_ALREADY_EXISTS');
49 FND_MESSAGE.set_token('ENTITY1', 'resource sequence number-cap', TRUE);
50 FND_MESSAGE.raise_error;
51 APP_EXCEPTION.raise_exception;
52 END IF;
53 END Check_Unique;
54
55 PROCEDURE Check_One_Pomove(X_Wip_Entity_Id NUMBER,
56 X_Organization_Id NUMBER,
57 X_Operation_Seq_Num NUMBER,
58 X_Resource_Seq_Num NUMBER,
59 X_Repetitive_Schedule_Id NUMBER) IS
60 res_count NUMBER := 0;
61 cursor discrete_check is
62 SELECT count(*)
63 FROM WIP_OPERATION_RESOURCES
64 WHERE ORGANIZATION_ID = X_Organization_Id
65 AND WIP_ENTITY_ID = X_Wip_Entity_Id
66 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
67 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
68 AND AUTOCHARGE_TYPE = 4;
69 cursor repetitive_check is
70 SELECT count(*)
71 FROM WIP_OPERATION_RESOURCES
72 WHERE ORGANIZATION_ID = X_Organization_Id
73 AND WIP_ENTITY_ID = X_Wip_Entity_Id
74 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
75 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
76 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
77 AND AUTOCHARGE_TYPE = 4;
78 BEGIN
79 IF X_Repetitive_Schedule_Id IS NULL then
80 open discrete_check;
81 fetch discrete_check into res_count;
82 close discrete_check;
83 ELSE
84 open repetitive_check;
85 fetch repetitive_check into res_count;
86 close repetitive_check;
87 END IF;
88 IF (res_count <> 0) THEN
89 FND_MESSAGE.SET_NAME('WIP','WIP_ONE_POMOVE');
90 FND_MESSAGE.raise_error;
91 APP_EXCEPTION.RAISE_EXCEPTION;
92 END IF;
93 END Check_One_Pomove;
94
95 PROCEDURE Check_One_Prior(X_Wip_Entity_Id NUMBER,
96 X_Organization_Id NUMBER,
97 X_Operation_Seq_Num NUMBER,
98 X_Resource_Seq_Num NUMBER,
99 X_Repetitive_Schedule_Id NUMBER) IS
100 res_count NUMBER := 0;
101 cursor discrete_check is
102 SELECT count(*)
103 FROM WIP_OPERATION_RESOURCES
104 WHERE ORGANIZATION_ID = X_Organization_Id
105 AND WIP_ENTITY_ID = X_Wip_Entity_Id
106 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
107 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
108 AND SCHEDULED_FLAG = 3;
109 cursor repetitive_check is
110 SELECT count(*)
111 FROM WIP_OPERATION_RESOURCES
112 WHERE ORGANIZATION_ID = X_Organization_Id
113 AND WIP_ENTITY_ID = X_Wip_Entity_Id
114 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
115 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
116 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
117 AND SCHEDULED_FLAG = 3;
118 BEGIN
119 IF X_Repetitive_Schedule_Id IS NULL then
120 open discrete_check;
121 fetch discrete_check into res_count;
122 close discrete_check;
123 ELSE
124 open repetitive_check;
125 fetch repetitive_check into res_count;
126 close repetitive_check;
127 END IF;
128 IF (res_count <> 0) THEN
129 FND_MESSAGE.SET_NAME('WIP','WIP_ONE_SCHEDULED_PRIOR');
130 FND_MESSAGE.raise_error;
131 APP_EXCEPTION.RAISE_EXCEPTION;
132 END IF;
133 END Check_One_Prior;
134
135 PROCEDURE Check_One_Next(X_Wip_Entity_Id NUMBER,
136 X_Organization_Id NUMBER,
137 X_Operation_Seq_Num NUMBER,
138 X_Resource_Seq_Num NUMBER,
139 X_Repetitive_Schedule_Id NUMBER) IS
140 res_count NUMBER := 0;
141 cursor discrete_check is
142 SELECT count(*)
143 FROM WIP_OPERATION_RESOURCES
144 WHERE ORGANIZATION_ID = X_Organization_Id
145 AND WIP_ENTITY_ID = X_Wip_Entity_Id
146 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
147 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
148 AND SCHEDULED_FLAG = 4;
149 cursor repetitive_check is
150 SELECT count(*)
151 FROM WIP_OPERATION_RESOURCES
152 WHERE ORGANIZATION_ID = X_Organization_Id
153 AND WIP_ENTITY_ID = X_Wip_Entity_Id
154 AND REPETITIVE_SCHEDULE_ID = X_Repetitive_Schedule_Id
155 AND OPERATION_SEQ_NUM = X_Operation_Seq_Num
156 AND RESOURCE_SEQ_NUM <> X_Resource_Seq_Num
157 AND SCHEDULED_FLAG = 4;
158 BEGIN
159 IF X_Repetitive_Schedule_Id IS NULL then
160 open discrete_check;
161 fetch discrete_check into res_count;
162 close discrete_check;
163 ELSE
164 open repetitive_check;
165 fetch repetitive_check into res_count;
166 close repetitive_check;
167 END IF;
168 IF (res_count <> 0) THEN
169 FND_MESSAGE.SET_NAME('WIP','WIP_ONE_SCHEDULED_NEXT');
170 FND_MESSAGE.raise_error;
171 APP_EXCEPTION.raise_exception;
172 END IF;
173 END Check_One_Next;
174
175 FUNCTION Pending_Transactions(
176 X_Wip_Entity_Id NUMBER,
177 X_Organization_Id NUMBER,
178 X_Operation_Seq_Num NUMBER,
179 X_Resource_Seq_Num NUMBER,
180 X_Line_Id NUMBER) RETURN BOOLEAN IS
181 X_count NUMBER := 0;
182 tct BOOLEAN;
183 cursor discrete_wcti_check is
184 select 1
185 from wip_cost_txn_interface
186 where organization_id = X_Organization_Id
187 and wip_entity_id = X_Wip_Entity_Id
188 and operation_seq_num = X_Operation_Seq_Num
189 and resource_seq_num = X_Resource_Seq_Num;
190
191 cursor discrete_wt_check is
192 select 1
193 from wip_transactions
194 where organization_id = X_Organization_Id
195 and wip_entity_id = X_Wip_Entity_Id
196 and operation_seq_num = X_Operation_Seq_Num
197 and resource_seq_num = X_Resource_Seq_Num;
198
199 cursor repetitive_wcti_check is
200 select 1
201 from wip_cost_txn_interface
202 where organization_id = X_Organization_Id
203 and wip_entity_id = X_Wip_Entity_Id
204 and operation_seq_num = X_Operation_Seq_Num
205 and resource_seq_num = X_Resource_Seq_Num
206 and line_id = X_Line_Id;
207
208 cursor repetitive_wt_check is
209 select 1
210 from wip_transactions
211 where organization_id = X_Organization_Id
212 and wip_entity_id = X_Wip_Entity_Id
213 and operation_seq_num = X_Operation_Seq_Num
214 and resource_seq_num = X_Resource_Seq_Num
215 and line_id = X_Line_Id;
216
217 BEGIN
218 IF X_Line_Id IS NULL then
219 open discrete_wcti_check;
220 open discrete_wt_check;
221 fetch discrete_wcti_check into X_count;
222 fetch discrete_wt_check into X_count;
223 tct := (NOT (discrete_wcti_check%NOTFOUND
224 AND discrete_wt_check%NOTFOUND));
225 close discrete_wcti_check;
226 close discrete_wt_check;
227 ELSE
228 open repetitive_wcti_check;
229 open repetitive_wt_check;
230 fetch repetitive_wcti_check into X_count;
231 fetch repetitive_wt_check into X_count;
232 tct := (NOT (repetitive_wcti_check%NOTFOUND
233 AND repetitive_wt_check%NOTFOUND));
234 close repetitive_wcti_check;
235 close repetitive_wt_check;
236 END IF;
237 return tct;
238 END Pending_Transactions;
239
240 PROCEDURE Set_Resource_Dates(X_Wip_Entity_Id NUMBER,
241 X_Organization_Id NUMBER,
242 X_Operation_Seq_Num NUMBER,
243 X_Resource_Seq_Num NUMBER,
244 X_Repetitive_Schedule_Id NUMBER,
245 X_First_Unit_Start_Date DATE,
246 X_Last_Unit_Completion_Date DATE) IS
247 BEGIN
248 IF X_Repetitive_Schedule_Id is NULL THEN
249 UPDATE wip_operation_resources
250 SET start_date = X_First_Unit_Start_Date,
251 completion_date = X_Last_Unit_Completion_Date
252 WHERE wip_entity_id = X_Wip_Entity_Id
253 AND organization_id = X_Organization_Id
254 AND operation_seq_num = X_Operation_Seq_Num
255 AND resource_seq_num = X_Resource_Seq_Num;
256 ELSE
257 UPDATE wip_operation_resources
258 SET start_date = X_First_Unit_Start_Date,
259 completion_date = X_Last_Unit_Completion_Date
260 WHERE wip_entity_id = X_Wip_Entity_Id
261 AND organization_id = X_Organization_Id
262 AND operation_seq_num = X_Operation_Seq_Num
263 AND resource_seq_num = X_Resource_Seq_Num
264 AND repetitive_schedule_id = X_Repetitive_Schedule_Id;
265 END IF;
266 END Set_Resource_Dates;
267
268 FUNCTION Get_Uom_Class(X_Unit VARCHAR2)
269 RETURN VARCHAR2 IS
270 dummy VARCHAR2(20);
271 cursor get_class is
272 SELECT UOM_CLASS
273 FROM MTL_UOM_CONVERSIONS CON
274 WHERE CON.UOM_CODE = X_Unit
275 AND CON.INVENTORY_ITEM_ID = 0;
276 BEGIN
277 open get_class;
278 fetch get_class into dummy;
279 close get_class;
280 return dummy;
281 END Get_Uom_Class;
282
283
284 Procedure delete_orphaned_alternates (p_wip_entity_id in number,
285 p_schedule_id in number,
286 x_return_status out nocopy varchar2) is
287 begin
288 x_return_status := fnd_api.g_ret_sts_success;
289
290 delete from wip_sub_operation_resources wsor
291 where wip_entity_id = p_wip_entity_id
292 and nvl(repetitive_schedule_id, -1) = nvl(p_schedule_id, -1)
293 and not exists (select 1
294 from wip_operation_resources wor
295 where wor.wip_entity_id = p_wip_entity_id
296 and nvl(wor.repetitive_schedule_id, -1) = nvl(p_schedule_id, -1)
297 and wor.operation_seq_num = wsor.operation_seq_num
298 and wor.substitute_group_num = wsor.substitute_group_num);
299 exception
300 when others then
301 x_return_status := fnd_api.g_ret_sts_unexp_error;
302 end delete_orphaned_alternates;
303
304 PROCEDURE Validate_Sub_Groups (p_wip_entity_id NUMBER,
305 p_schedule_id NUMBER,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_data OUT NOCOPY VARCHAR2,
308 x_operation_seq_num OUT NOCOPY NUMBER) IS
309 cursor operations (p_wip_entity_id NUMBER, p_schedule_id NUMBER) is
310 select operation_seq_num
311 from wip_operations
312 where wip_entity_id = p_wip_entity_id
313 and nvl(repetitive_schedule_id,-1) = nvl(p_schedule_id, -1);
314
315 cursor op_resources (p_wip_entity_id NUMBER,
316 p_schedule_id NUMBER,
317 p_op_seq_num NUMBER) is
318 select * from (select resource_seq_num,
319 schedule_seq_num,
320 substitute_group_num,
321 scheduled_flag,
322 parent_resource_seq /* added for bug 4747951 */
323 from wip_operation_resources
324 where wip_entity_id = p_wip_entity_id
325 and nvl(repetitive_schedule_id,-1) = nvl(p_schedule_id,-1)
326 and operation_seq_num = p_op_seq_num
327 union
328 select resource_seq_num,
329 schedule_seq_num,
330 substitute_group_num,
331 scheduled_flag,
332 null parent_resource_seq /* added for bug 4747951 */
333 from wip_sub_operation_resources
334 where wip_entity_id = p_wip_entity_id
335 and nvl(repetitive_schedule_id,-1) = nvl(p_schedule_id,-1)
336 and operation_seq_num = p_op_seq_num
337 )
338 order by nvl(schedule_seq_num, resource_seq_num);
339
340 last_res_seq NUMBER := 0;
344 last_parent_resource_seq NUMBER := null; /* for bug 4747951 */
341 last_sub_group NUMBER := 0;
342 last_sched_seq NUMBER := 0;
343 last_scheduled_flag NUMBER := 0;
345 error_exists BOOLEAN := false;
346 BEGIN
347 for cur_op in operations (p_wip_entity_id, p_schedule_id) loop
348 last_res_seq := 0;
349 last_sub_group := 0;
350 last_sched_seq := 0;
351 last_scheduled_flag := 0;
352 last_parent_resource_seq := null;
353
354 for cur_opres in op_resources (p_wip_entity_id,
355 p_schedule_id,
356 cur_op.operation_seq_num) loop
357 if (last_res_seq <> 0) then
358 /* For bug 4747951. Skip below validation for setup resources */
359 if ((nvl(last_sched_seq,last_res_seq) = nvl(cur_opres.schedule_seq_num, cur_opres.resource_seq_num)) and
360 (last_parent_resource_seq is null and cur_opres.parent_resource_seq is null)) then
361 if (nvl(last_sub_group,-1) <> nvl(cur_opres.substitute_group_num,-1)) then
362 FND_MESSAGE.SET_NAME('WIP', 'SIM_RES_SAME_SUB_GROUP');
363 FND_MESSAGE.set_token( token => 'OP_SEQ',
364 value=> to_char(cur_op.operation_seq_num),
365 translate => FALSE);
366 FND_MESSAGE.set_token( token => 'RES_SEQ_1',
367 value=> to_char(last_res_seq),
368 translate => FALSE);
369 FND_MESSAGE.set_token( token => 'RES_SEQ_2',
370 value=> to_char(cur_opres.resource_seq_num),
371 translate => FALSE);
372 error_exists := true;
373 -- simultaneous resources: if one resource is Prior/Next, the others must also have the same
374 -- scheduling flag
375 elsif (last_scheduled_flag <> cur_opres.scheduled_flag) then
376 if ((((cur_opres.scheduled_flag = wip_constants.sched_prior) OR (cur_opres.scheduled_flag = wip_constants.sched_next)) AND (last_scheduled_flag <> wip_constants.sched_no))
377 OR (((last_scheduled_flag = wip_constants.sched_prior) OR (last_scheduled_flag = wip_constants.sched_next)) AND (cur_opres.scheduled_flag <> wip_constants.sched_no))) then
378 FND_MESSAGE.SET_NAME('WIP', 'SIM_RES_SAME_PRIOR_NEXT');
379 FND_MESSAGE.set_token( token => 'OP_SEQ',
380 value=> to_char(cur_op.operation_seq_num),
381 translate => FALSE);
382 FND_MESSAGE.set_token( token => 'RES_SEQ_1',
383 value=> to_char(last_res_seq),
384 translate => FALSE);
385 FND_MESSAGE.set_token( token => 'RES_SEQ_2',
386 value=> to_char(cur_opres.resource_seq_num),
387 translate => FALSE);
388 error_exists := true;
389 end if;
390 end if;
391 end if;
392 end if;
393
394 if (error_exists = true) then
395 x_return_status := fnd_api.g_ret_sts_error;
396 x_msg_data := fnd_message.get;
397 x_operation_seq_num := cur_op.operation_seq_num;
398 return;
399 end if;
400
401 last_res_seq := cur_opres.resource_seq_num;
402 last_sub_group := cur_opres.substitute_group_num;
403 last_sched_seq := cur_opres.schedule_seq_num;
404 last_scheduled_flag := cur_opres.scheduled_flag;
405 last_parent_resource_seq := cur_opres.parent_resource_seq;
406 end loop;
407 end loop;
408
409 x_return_status := fnd_api.g_ret_sts_success;
410 EXCEPTION
411 when others then
412 x_return_status := fnd_api.g_ret_sts_unexp_error;
413 end validate_sub_groups;
414
415 Procedure Update_Resource_Instances(p_wip_entity_id NUMBER,
416 p_org_id NUMBER) is
417 cursor operation_rsc (p_wip_entity_id NUMBER, p_org_id NUMBER) is
418 select operation_seq_num,
419 resource_seq_num,
420 start_date,
421 completion_date
422 from wip_operation_resources
423 where wip_entity_id = p_wip_entity_id
424 and organization_id = p_org_id;
425
426 Begin
427 for cur_rsc in operation_rsc (p_wip_entity_id, p_org_id) loop
428 UPDATE wip_op_resource_instances
429 SET start_date = cur_rsc.start_date,
430 completion_date = cur_rsc.completion_date
431 WHERE wip_entity_id = p_wip_entity_id
432 and organization_id = p_org_id
433 and operation_seq_num = cur_rsc.operation_seq_num
434 and resource_seq_num = cur_rsc.resource_seq_num;
435 end loop;
436 End Update_Resource_Instances;
437
438 END WIP_OP_RESOURCES_UTILITIES;