1 Package Body WIP_RES_USAGE_VALIDATE as
2 /* $Header: wipruvdb.pls 120.2 2006/09/01 06:00:23 panagara noship $ */
3 --is the resource sequence valid (not null)?
4 procedure validate_seq_num(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
5 --does the sequence number already exist in WOR?
6 procedure validate_res_seq_num(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
7 --are the dates populated?
8 procedure validate_dates(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
9 --assigned units > 0?
10 procedure validate_assigned_units(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
11 --times don't overlap?
12 procedure val_time_overlap_res_usage(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
13 procedure val_time_overlap_ri_usage(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
14 --times match slot?
15 procedure validate_time_slot(p_group_id in number, p_wip_entity_id in number, p_organization_id in number);
16
17 procedure derive_usages(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
18 cursor c_usage_rows is
19 select resource_seq_num, parent_seq_num, operation_seq_num, resource_id_new, rowid
20 from wip_job_dtls_interface wjdi
21 where group_id = p_group_id
22 and process_phase = wip_constants.ml_validation
23 and process_status in (wip_constants.running,
24 wip_constants.warning)
25 and wip_entity_id = p_wip_entity_id
26 and organization_id = p_organization_id
27 and load_type in (wip_job_details.wip_res_usage,
28 wip_job_details.wip_res_instance_usage)
29 and substitution_type = wip_job_details.wip_add;
30
31 l_res_seq number;
32 begin
33 for cur_row in c_usage_rows loop
34 if (cur_row.resource_seq_num is null and cur_row.parent_seq_num is not null) then
35 /* Fixed bug 5500805. We should not copy parent_seq_num to resource_seq_num but
36 we need to derive it from the parent resource of this setup resource.
37 This query works for both res_usage and res_instance_usage */
38 select resource_seq_num into l_res_seq
39 from wip_operation_resources
40 where organization_id = p_organization_id
41 and wip_entity_id = p_wip_entity_id
42 and operation_seq_num = cur_row.operation_seq_num
43 and resource_id = cur_row.resource_id_new
44 and parent_resource_seq = cur_row.parent_seq_num;
45
46 update wip_job_dtls_interface wjdi
47 set resource_seq_num = l_res_seq
48 where rowid = cur_row.rowid;
49
50 end if;
51 end loop;
52
53 end derive_usages;
54
55 Procedure Validate_Usage ( p_group_id in number,
56 p_wip_entity_id in number,
57 p_organization_id in number,
58 x_err_code out NOCOPY varchar2,
59 x_err_msg out NOCOPY varchar2,
60 x_return_status out NOCOPY varchar2 ) IS
61
62 BEGIN
63 IF p_group_id IS NULL OR
64 p_wip_entity_id IS NULL OR p_organization_id IS NULL THEN
65 x_err_code := SQLCODE;
66 x_err_msg := 'Error in wipruvdb.pls : Primary key cannot be null!';
67 x_return_status := FND_API.G_RET_STS_ERROR;
68 RETURN;
69 END IF;
70
71 derive_usages(p_group_id => p_group_id,
72 p_wip_entity_id => p_wip_entity_id,
73 p_organization_id => p_organization_id);
74
75 /**************CHECK THAT RESOURCE SEQ NUM IS NOT NULL ***************/
76 validate_seq_num(p_group_id => p_group_id,
77 p_wip_entity_id => p_wip_entity_id,
78 p_organization_id => p_organization_id);
79
80 /***********CHECK THAT IF SUCH A RESOURCE EXIST IN RESOURCE TABLE *********/
81 validate_res_seq_num(p_group_id => p_group_id,
82 p_wip_entity_id => p_wip_entity_id,
83 p_organization_id => p_organization_id);
84
85 /*************CHECK THAT START AND END DATE CANNOT BE NULL IF ADD**********/
86 validate_dates(p_group_id => p_group_id,
87 p_wip_entity_id => p_wip_entity_id,
88 p_organization_id => p_organization_id);
89
90 /*************CHECK THAT THE TIME SLOT CANNOT BE OVERLAPPED **********/
91 val_time_overlap_res_usage(p_group_id => p_group_id,
92 p_wip_entity_id => p_wip_entity_id,
93 p_organization_id => p_organization_id);
94 val_time_overlap_ri_usage(p_group_id => p_group_id,
95 p_wip_entity_id => p_wip_entity_id,
96 p_organization_id => p_organization_id);
97
98 validate_time_slot(p_group_id => p_group_id,
99 p_wip_entity_id => p_wip_entity_id,
100 p_organization_id => p_organization_id);
101
102 /***************CHECK THAT ASSIGNED UNITS CANNOT BE NULL ****************/
103 validate_assigned_units(p_group_id => p_group_id,
104 p_wip_entity_id => p_wip_entity_id,
105 p_organization_id => p_organization_id);
106
107 exception
108 When others then
109 x_err_code := SQLCODE;
110 x_err_msg := 'Error in wiprudfb: '|| SQLERRM;
111 x_return_status := FND_API.G_RET_STS_ERROR;
112 END VALIDATE_USAGE;
113
114 procedure validate_seq_num(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
115 cursor c_invalid_rows is
116 select interface_id
117 from wip_job_dtls_interface wjdi
118 where group_id = p_group_id
119 and process_phase = wip_constants.ml_validation
120 and process_status in (wip_constants.running,
121 wip_constants.warning)
122 and wip_entity_id = p_wip_entity_id
123 and organization_id = p_organization_id
124 and load_type in (wip_job_details.wip_res_usage,
125 wip_job_details.wip_res_instance_usage)
126 and substitution_type = wip_job_details.wip_add
127 and (resource_seq_num is null
128 or (load_type = wip_job_details.wip_res_instance_usage
129 and resource_instance_id is null));
130 l_error_exists boolean := false;
131 begin
132 for l_inv_row in c_invalid_rows loop
133 l_error_exists := true;
134 fnd_message.set_name('WIP', 'WIP_NOT_NULL_VAL_LACK');
135 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
136 if(wip_job_details.std_alone = 1) then
137 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
138 p_text => substr(fnd_message.get,1,500),
139 p_error_type => wip_jdi_utils.msg_error);
140 else
141 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
142 p_text => substr(fnd_message.get,1,500),
143 p_error_type => wip_jdi_utils.msg_error);
144 end if;
145 end loop;
146
147 if(l_error_exists) then
148 update wip_job_dtls_interface wjdi
149 set process_status = wip_constants.error
150 where group_id = p_group_id
151 and process_phase = wip_constants.ml_validation
152 and process_status in (wip_constants.running,
153 wip_constants.warning)
154 and wip_entity_id = p_wip_entity_id
155 and organization_id = p_organization_id
156 and load_type in (wip_job_details.wip_res_usage,
157 wip_job_details.wip_res_instance_usage)
158 and substitution_type = wip_job_details.wip_add
159 and (resource_seq_num is null
160 or (load_type = wip_job_details.wip_res_instance_usage
161 and resource_instance_id is null));
162 end if;
163 end validate_seq_num;
164
165
166 procedure validate_res_seq_num(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
167 cursor c_invalid_rows is
168 select interface_id
169 from wip_job_dtls_interface wjdi
170 where group_id = p_group_id
171 and process_phase = wip_constants.ml_validation
172 and process_status in (wip_constants.running,
173 wip_constants.warning)
174 and wip_entity_id = p_wip_entity_id
175 and organization_id = p_organization_id
176 and substitution_type = wip_job_details.wip_add
177 and ((load_type = wip_job_details.wip_res_instance_usage
178 and not exists (select 1
179 from wip_op_resource_instances
180 where wip_entity_id = wjdi.wip_entity_id
181 and organization_id = wjdi.organization_id
182 and operation_seq_num = wjdi.operation_seq_num
183 and resource_seq_num = wjdi.resource_seq_num
184 and instance_id = wjdi.resource_instance_id))
185 or (load_type = wip_job_details.wip_res_usage
186 and not exists (select 1
187 from wip_operation_resources
188 where wip_entity_id = wjdi.wip_entity_id
189 and organization_id = wjdi.organization_id
190 and operation_seq_num = wjdi.operation_seq_num
191 and resource_seq_num = wjdi.resource_seq_num)));
192 l_error_exists boolean := false;
193 begin
194 for l_inv_row in c_invalid_rows loop
195 l_error_exists := true;
196 fnd_message.set_name('WIP', 'WIP_JDI_RES_NOT_IN_JOB');
197 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
198 if(wip_job_details.std_alone = 1) then
199 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
200 p_text => substr(fnd_message.get,1,500),
201 p_error_type => wip_jdi_utils.msg_error);
202 else
203 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
204 p_text => substr(fnd_message.get,1,500),
205 p_error_type => wip_jdi_utils.msg_error);
206 end if;
207 end loop;
208
209 if(l_error_exists) then
210 update wip_job_dtls_interface wjdi
211 set process_status = wip_constants.error
212 where group_id = p_group_id
213 and process_phase = wip_constants.ml_validation
214 and process_status in (wip_constants.running,
215 wip_constants.warning)
216 and wip_entity_id = p_wip_entity_id
217 and organization_id = p_organization_id
218 and substitution_type = wip_job_details.wip_add
219 and ((load_type = wip_job_details.wip_res_instance_usage
220 and not exists (select 1
221 from wip_op_resource_instances
222 where wip_entity_id = wjdi.wip_entity_id
223 and organization_id = wjdi.organization_id
224 and operation_seq_num = wjdi.operation_seq_num
225 and resource_seq_num = wjdi.resource_seq_num
226 and instance_id = wjdi.resource_instance_id))
227 or (load_type = wip_job_details.wip_res_usage
228 and not exists (select 1
229 from wip_operation_resources
230 where wip_entity_id = wjdi.wip_entity_id
231 and organization_id = wjdi.organization_id
232 and operation_seq_num = wjdi.operation_seq_num
233 and resource_seq_num = wjdi.resource_seq_num)));
234
235 end if;
236 end validate_res_seq_num;
237
238 procedure validate_dates(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
239 cursor c_invalid_rows is
240 select interface_id
241 from wip_job_dtls_interface wjdi
242 where group_id = p_group_id
243 and process_phase = wip_constants.ml_validation
244 and process_status in (wip_constants.running,
245 wip_constants.warning)
246 and wip_entity_id = p_wip_entity_id
247 and organization_id = p_organization_id
248 and load_type in (wip_job_details.wip_res_usage,
249 wip_job_details.wip_res_instance_usage)
250 and substitution_type = wip_job_details.wip_add
251 and ( start_date is null
252 or completion_date is null
253 --Bug 5139799:Following 2 validations are added:
254 -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
255 -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
256 or start_date > completion_date
257 or exists (select 1
258 from wip_operation_resources wor,wip_op_resource_instances wori
259 where wor.wip_entity_id = p_wip_entity_id
260 and wor.operation_seq_num = wjdi.operation_seq_num
261 and wor.resource_seq_num = wjdi.resource_seq_num
262 and wor.wip_entity_id = wori.wip_entity_id(+)
263 and wor.operation_seq_num = wori.operation_seq_num(+)
264 and wor.resource_seq_num = wori.resource_seq_num(+)
265 and wjdi.resource_instance_id = wori.instance_id(+)
266 and (nvl(wori.start_date,wor.start_date) > wjdi.start_date
267 or nvl(wori.completion_date,wor.completion_date) <wjdi.completion_date)));
268
269 l_error_exists boolean := false;
270 begin
271
272 for l_inv_row in c_invalid_rows loop
273 l_error_exists := true;
274 fnd_message.set_name('WIP', 'WIP_INV_START_OR_END');
275 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
276 if(wip_job_details.std_alone = 1) then
277 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
278 p_text => substr(fnd_message.get,1,500),
279 p_error_type => wip_jdi_utils.msg_error);
280 else
281 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
282 p_text => substr(fnd_message.get,1,500),
283 p_error_type => wip_jdi_utils.msg_error);
284 end if;
285
286 end loop;
287
288 if(l_error_exists) then
289 update wip_job_dtls_interface wjdi
290 set process_status = wip_constants.error
291 where group_id = p_group_id
292 and process_phase = wip_constants.ml_validation
293 and process_status in (wip_constants.running,
294 wip_constants.warning)
295 and wip_entity_id = p_wip_entity_id
296 and organization_id = p_organization_id
297 and load_type in (wip_job_details.wip_res_usage,
298 wip_job_details.wip_res_instance_usage)
299 and substitution_type = wip_job_details.wip_add
300 and ( start_date is null
301 or completion_date is null
302 --Bug 5139799:Following 2 validations are added:
303 -- 1.Start date of usage is greater than or equal to start date of the corresponding resource or instance.
304 -- 2.Start and end dates of usage is in between start and end dates of corresponding resource or instance.
305 or start_date > completion_date
306 or exists (select 1
307 from wip_operation_resources wor,wip_op_resource_instances wori
308 where wor.wip_entity_id = p_wip_entity_id
309 and wor.operation_seq_num = wjdi.operation_seq_num
310 and wor.resource_seq_num = wjdi.resource_seq_num
311 and wor.wip_entity_id = wori.wip_entity_id(+)
312 and wor.operation_seq_num = wori.operation_seq_num(+)
313 and wor.resource_seq_num = wori.resource_seq_num(+)
314 and wjdi.resource_instance_id = wori.instance_id(+)
315 and (nvl(wori.start_date,wor.start_date) > wjdi.start_date
316 or nvl(wori.completion_date,wor.completion_date) <wjdi.completion_date)));
317 end if;
318 end validate_dates;
319
320 procedure validate_assigned_units(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
321 cursor c_invalid_rows is
322 select interface_id
323 from wip_job_dtls_interface wjdi
324 where group_id = p_group_id
325 and process_phase = wip_constants.ml_validation
326 and process_status in (wip_constants.running,
327 wip_constants.warning)
328 and wip_entity_id = p_wip_entity_id
329 and organization_id = p_organization_id
330 and load_type in (wip_job_details.wip_res_usage,
331 wip_job_details.wip_res_instance_usage)
332 and substitution_type = wip_job_details.wip_add
333 and ( assigned_units is null
334 or assigned_units <= 0);
335
336 l_error_exists boolean := false;
337 begin
338
339 for l_inv_row in c_invalid_rows loop
340 l_error_exists := true;
341 fnd_message.set_name('WIP', 'WIP_INV_ASSIGNED_UNITS');
342 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
343 if(wip_job_details.std_alone = 1) then
344 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
345 p_text => substr(fnd_message.get,1,500),
346 p_error_type => wip_jdi_utils.msg_error);
347 else
348 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
349 p_text => substr(fnd_message.get,1,500),
350 p_error_type => wip_jdi_utils.msg_error);
351 end if;
352 end loop;
353
354 if(l_error_exists) then
355 update wip_job_dtls_interface wjdi
356 set process_status = wip_constants.error
357 where group_id = p_group_id
358 and process_phase = wip_constants.ml_validation
359 and process_status in (wip_constants.running,
360 wip_constants.warning)
361 and wip_entity_id = p_wip_entity_id
362 and organization_id = p_organization_id
363 and load_type in (wip_job_details.wip_res_usage,
364 wip_job_details.wip_res_instance_usage)
365 and substitution_type = wip_job_details.wip_add
366 and ( assigned_units is null
367 or assigned_units < 0);
368 end if;
369 end validate_assigned_units;
370
371 procedure val_time_overlap_ri_usage(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
372
373 cursor c_invalid_res_inst_usage_rows is
374 select interface_id
375 from wip_job_dtls_interface wjdi
376 where group_id = p_group_id
377 and process_phase = wip_constants.ml_validation
378 and process_status in (wip_constants.running,
379 wip_constants.warning)
380 and wip_entity_id = p_wip_entity_id
381 and organization_id = p_organization_id
382 and load_type = wip_job_details.wip_res_instance_usage
383 and substitution_type = wip_job_details.wip_add
384 and exists (select 1
385 from wip_job_dtls_interface wjdi2
386 where wjdi2.group_id = wjdi.group_id
387 and wjdi2.wip_entity_id = p_wip_entity_id
388 and wjdi2.organization_id = p_organization_id
389 and wjdi2.operation_seq_num = wjdi.operation_seq_num
390 and wjdi2.resource_seq_num = wjdi.resource_seq_num
391 and wjdi2.load_type = wip_job_details.wip_res_instance_usage
392 and wjdi2.start_date < wjdi.start_date
393 and wjdi2.completion_date > wjdi.start_date);
394
395 l_error_exists boolean := false;
396 begin
397
398 for l_inv_row in c_invalid_res_inst_usage_rows loop
399 l_error_exists := true;
400 fnd_message.set_name('WIP', 'WIP_TIME_OVERLAPPED');
401 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
402 if(wip_job_details.std_alone = 1) then
403 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
404 p_text => substr(fnd_message.get,1,500),
405 p_error_type => wip_jdi_utils.msg_error);
406 else
407 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
408 p_text => substr(fnd_message.get,1,500),
409 p_error_type => wip_jdi_utils.msg_error);
410 end if;
411 end loop;
412
413 if(l_error_exists) then
414 update wip_job_dtls_interface wjdi
415 set process_status = wip_constants.error
416 where group_id = p_group_id
417 and process_phase = wip_constants.ml_validation
418 and process_status in (wip_constants.running,
419 wip_constants.warning)
420 and wip_entity_id = p_wip_entity_id
421 and organization_id = p_organization_id
422 and load_type = wip_job_details.wip_res_instance_usage
423 and substitution_type = wip_job_details.wip_add
424 and exists (select 1
425 from wip_job_dtls_interface wjdi2
426 where wjdi2.group_id = wjdi.group_id
427 and wjdi2.wip_entity_id = p_wip_entity_id
428 and wjdi2.organization_id = p_organization_id
429 and wjdi2.operation_seq_num = wjdi.operation_seq_num
430 and wjdi2.resource_seq_num = wjdi.resource_seq_num
431 and load_type = wip_job_details.wip_res_instance_usage
432 and wjdi2.rowid <> wjdi.rowid
433 and ( wjdi2.start_date between wjdi.start_date and wjdi.completion_date
434 or wjdi2.completion_date between wjdi.start_date and wjdi.completion_date));
435
436 end if;
437
438 end val_time_overlap_ri_usage;
439
440 procedure val_time_overlap_res_usage(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
441 cursor c_invalid_res_usage_rows is
442 select interface_id
443 from wip_job_dtls_interface wjdi
444 where group_id = p_group_id
445 and process_phase = wip_constants.ml_validation
446 and process_status in (wip_constants.running,
447 wip_constants.warning)
448 and wip_entity_id = p_wip_entity_id
449 and organization_id = p_organization_id
450 and load_type = wip_job_details.wip_res_usage
451 and substitution_type = wip_job_details.wip_add
452 and exists (select 1
453 from wip_job_dtls_interface wjdi2
454 where wjdi2.group_id = wjdi.group_id
455 and wjdi2.wip_entity_id = p_wip_entity_id
456 and wjdi2.organization_id = p_organization_id
457 and wjdi2.operation_seq_num = wjdi.operation_seq_num
458 and wjdi2.resource_seq_num = wjdi.resource_seq_num
459 and wjdi2.load_type = wip_job_details.wip_res_usage
460 and wjdi2.start_date < wjdi.start_date
461 and wjdi2.completion_date > wjdi.start_date);
462
463 l_error_exists boolean := false;
464 begin
465
466 for l_inv_row in c_invalid_res_usage_rows loop
467 l_error_exists := true;
468 fnd_message.set_name('WIP', 'WIP_TIME_OVERLAPPED');
469 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
470 if(wip_job_details.std_alone = 1) then
471 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
472 p_text => substr(fnd_message.get,1,500),
473 p_error_type => wip_jdi_utils.msg_error);
474 else
475 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
476 p_text => substr(fnd_message.get,1,500),
477 p_error_type => wip_jdi_utils.msg_error);
478 end if;
479 end loop;
480
481 if(l_error_exists) then
482 update wip_job_dtls_interface wjdi
483 set process_status = wip_constants.error
484 where group_id = p_group_id
485 and process_phase = wip_constants.ml_validation
486 and process_status in (wip_constants.running,
487 wip_constants.warning)
488 and wip_entity_id = p_wip_entity_id
489 and organization_id = p_organization_id
490 and load_type = wip_job_details.wip_res_usage
491 and substitution_type = wip_job_details.wip_add
492 and exists (select 1
493 from wip_job_dtls_interface wjdi2
494 where wjdi2.group_id = wjdi.group_id
495 and wjdi2.wip_entity_id = p_wip_entity_id
496 and wjdi2.organization_id = p_organization_id
497 and wjdi2.operation_seq_num = wjdi.operation_seq_num
498 and wjdi2.resource_seq_num = wjdi.resource_seq_num
499 and load_type = wip_job_details.wip_res_usage
500 and wjdi2.rowid <> wjdi.rowid
501 and ( wjdi2.start_date between wjdi.start_date and wjdi.completion_date
502 or wjdi2.completion_date between wjdi.start_date and wjdi.completion_date));
503
504 end if;
505
506 end val_time_overlap_res_usage;
507
508 procedure validate_time_slot(p_group_id in number, p_wip_entity_id in number, p_organization_id in number) is
509 cursor c_invalid_rows is
510 select interface_id
511 from wip_job_dtls_interface wjdi
512 where group_id = p_group_id
513 and process_phase = wip_constants.ml_validation
514 and process_status in (wip_constants.running,
515 wip_constants.warning)
516 and wip_entity_id = p_wip_entity_id
517 and organization_id = p_organization_id
518 and load_type = wip_job_details.wip_res_usage
519 and substitution_type = wip_job_details.wip_add
520 and ( not exists (select 1
521 from wip_job_dtls_interface wjdi2, wip_operation_resources wor
522 where wjdi2.group_id = wjdi.group_id
523 and wjdi2.wip_entity_id = p_wip_entity_id
524 and wjdi2.organization_id = p_organization_id
525 and wjdi2.operation_seq_num = wjdi.operation_seq_num
526 and wjdi2.resource_seq_num = wjdi.resource_seq_num
527 and wjdi2.load_type = wip_job_details.wip_res_usage
528 and wjdi2.substitution_type = wip_job_details.wip_add
529 and wor.wip_entity_id = p_wip_entity_id
530 and wor.organization_id = p_organization_id
531 and wor.operation_seq_num = wjdi2.operation_seq_num
532 and wor.resource_seq_num = wjdi2.resource_seq_num
533 and wor.start_date = wjdi2.start_date)
534 or not exists (select 1
535 from wip_job_dtls_interface wjdi2, wip_operation_resources wor
536 where wjdi2.group_id = wjdi.group_id
537 and wjdi2.wip_entity_id = p_wip_entity_id
538 and wjdi2.organization_id = p_organization_id
539 and wjdi2.operation_seq_num = wjdi.operation_seq_num
540 and wjdi2.resource_seq_num = wjdi.resource_seq_num
541 and wjdi2.load_type = wip_job_details.wip_res_usage
542 and wjdi2.substitution_type = wip_job_details.wip_add
543 and wor.wip_entity_id = p_wip_entity_id
544 and wor.organization_id = p_organization_id
545 and wor.operation_seq_num = wjdi2.operation_seq_num
546 and wor.resource_seq_num = wjdi2.resource_seq_num
547 and wor.completion_date = wjdi2.completion_date));
548 l_error_exists boolean := false;
549 begin
550 for l_inv_row in c_invalid_rows loop
551 l_error_exists := true;
552 fnd_message.set_name('WIP', 'WIP_DATE_NOT_MATCH');
553 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
554 if(wip_job_details.std_alone = 1) then
555 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
556 p_text => substr(fnd_message.get,1,500),
557 p_error_type => wip_jdi_utils.msg_error);
558 else
559 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
560 p_text => substr(fnd_message.get,1,500),
561 p_error_type => wip_jdi_utils.msg_error);
562 end if;
563 end loop;
564
565 if(l_error_exists) then
566 update wip_job_dtls_interface wjdi
567 set process_status = wip_constants.error
568 where group_id = p_group_id
569 and process_phase = wip_constants.ml_validation
570 and process_status in (wip_constants.running,
571 wip_constants.warning)
572 and wip_entity_id = p_wip_entity_id
573 and organization_id = p_organization_id
574 and load_type = wip_job_details.wip_res_usage
575 and substitution_type = wip_job_details.wip_add
576 and ( not exists (select 1
577 from wip_job_dtls_interface wjdi2, wip_operation_resources wor
578 where wjdi2.group_id = wjdi.group_id
579 and wjdi2.wip_entity_id = p_wip_entity_id
580 and wjdi2.organization_id = p_organization_id
581 and wjdi2.operation_seq_num = wjdi.operation_seq_num
582 and wjdi2.resource_seq_num = wjdi.resource_seq_num
583 and wjdi2.load_type = wip_job_details.wip_res_usage
584 and wjdi2.substitution_type = wip_job_details.wip_add
585 and wor.wip_entity_id = p_wip_entity_id
586 and wor.organization_id = p_organization_id
587 and wor.operation_seq_num = wjdi2.operation_seq_num
588 and wor.resource_seq_num = wjdi2.resource_seq_num
589 and wor.start_date = wjdi2.start_date)
590 or not exists (select 1
591 from wip_job_dtls_interface wjdi2, wip_operation_resources wor
592 where wjdi2.group_id = wjdi.group_id
593 and wjdi2.wip_entity_id = p_wip_entity_id
594 and wjdi2.organization_id = p_organization_id
595 and wjdi2.operation_seq_num = wjdi.operation_seq_num
596 and wjdi2.resource_seq_num = wjdi.resource_seq_num
597 and wjdi2.load_type = wip_job_details.wip_res_usage
598 and wjdi2.substitution_type = wip_job_details.wip_add
599 and wor.wip_entity_id = p_wip_entity_id
600 and wor.organization_id = p_organization_id
601 and wor.operation_seq_num = wjdi2.operation_seq_num
602 and wor.resource_seq_num = wjdi2.resource_seq_num
603 and wor.completion_date = wjdi2.completion_date));
604 end if;
605 end validate_time_slot;
606 end wip_res_usage_validate;
[Home] [Help]