[Home] [Help]
PACKAGE BODY: APPS.WIP_OPERATION_VALIDATE
Source
1 Package Body WIP_OPERATION_VALIDATE as
2 /* $Header: wipopvdb.pls 120.1 2006/05/09 00:03:13 panagara noship $ */
3
4 /* Helper procedures to validate add/change operation load types */
5 procedure val_null_values(p_group_id in number,
6 p_parent_header_id in number, /* Fix for Bug#3636378 */
7 p_sub_type in number);
8 procedure val_dept(p_group_id in number,
9 p_wip_entity_id in number,
10 p_organization_id in number,
11 p_sub_type in number);
12 procedure val_dept_resources(p_group_id in number,
13 p_wip_entity_id in number,
14 p_organization_id in number,
15 p_sub_type in number);
16 procedure val_std_op(p_group_id in number,
17 p_wip_entity_id in number,
18 p_organization_id in number,
19 p_sub_type in number);
20 procedure val_mtq(p_group_id in number,
21 p_wip_entity_id in number,
22 p_organization_id in number,
23 p_sub_type in number);
24 procedure val_cnt_pnt(p_group_id in number,
25 p_wip_entity_id in number,
26 p_organization_id in number,
27 p_sub_type in number);
28 procedure val_bfl_flag(p_group_id in number,
29 p_wip_entity_id in number,
30 p_organization_id in number,
31 p_sub_type in number);
32
33 /* Helper procedures to validate add operation load types */
34 procedure val_add_op_seq_num(p_group_id in number,
35 p_wip_entity_id in number,
36 p_organization_id in number);
37 procedure val_add_sch_date(p_group_id in number,
38 p_wip_entity_id in number,
39 p_organization_id in number,
40 p_parent_header_id in number);
41
42 /* Helper procedures to validate change operation load types */
43 procedure val_change_op_seq_num(p_group_id in number,
44 p_wip_entity_id in number,
45 p_organization_id in number);
46 procedure val_change_sch_date(p_group_id in number,
47 p_wip_entity_id in number,
48 p_organization_id in number);
49
50 /* Public procedures */
51 Procedure Add_Operation (p_group_id in number,
52 p_parent_header_id in number,
53 p_wip_entity_id in number,
54 p_organization_id in number,
55 x_err_code out nocopy varchar2,
56 x_err_msg out nocopy varchar2,
57 x_return_status out nocopy varchar2 ) IS
58 begin
59
60 val_null_values(p_group_id => p_group_id,
61 p_parent_header_id => p_parent_header_id, /* Fix for Bug#3636378 */
62 p_sub_type => wip_job_details.wip_add);
63
64 val_dept(p_group_id => p_group_id,
65 p_wip_entity_id => p_wip_entity_id,
66 p_organization_id => p_organization_id,
67 p_sub_type => wip_job_details.wip_add);
68
69 val_std_op(p_group_id => p_group_id,
70 p_wip_entity_id => p_wip_entity_id,
71 p_organization_id => p_organization_id,
72 p_sub_type => wip_job_details.wip_add);
73
74 val_mtq(p_group_id => p_group_id,
75 p_wip_entity_id => p_wip_entity_id,
76 p_organization_id => p_organization_id,
77 p_sub_type => wip_job_details.wip_add);
78
79 val_cnt_pnt(p_group_id => p_group_id,
80 p_wip_entity_id => p_wip_entity_id,
81 p_organization_id => p_organization_id,
82 p_sub_type => wip_job_details.wip_add);
83
84 val_bfl_flag(p_group_id => p_group_id,
85 p_wip_entity_id => p_wip_entity_id,
86 p_organization_id => p_organization_id,
87 p_sub_type => wip_job_details.wip_add);
88
89 val_add_op_seq_num(p_group_id => p_group_id,
90 p_wip_entity_id => p_wip_entity_id,
91 p_organization_id => p_organization_id);
92
93 val_add_sch_date(p_group_id => p_group_id,
94 p_wip_entity_id => p_wip_entity_id,
95 p_organization_id => p_organization_id,
96 p_parent_header_id => p_parent_header_id);
97
98 Exception
99
100 when others then
101 x_return_status := FND_API.G_RET_STS_ERROR;
102 x_err_msg := 'ERROR IN WIPOPVDB.ADD_OPERATION: ' || SQLERRM;
103 x_err_code := to_char(SQLCODE);
104 return;
105
106 END Add_Operation;
107
108
109 Procedure Change_Operation (p_group_id in number,
110 p_parent_header_id in number,
111 p_wip_entity_id in number,
112 p_organization_id in number,
113 x_err_code out nocopy varchar2,
114 x_err_msg out nocopy varchar2,
115 x_return_status out nocopy varchar2 ) IS
116 BEGIN
117
118 val_null_values(p_group_id => p_group_id,
119 p_parent_header_id => p_parent_header_id, /* Fix for Bug#3636378 */
120 p_sub_type => wip_job_details.wip_change);
121
122 val_dept(p_group_id => p_group_id,
123 p_wip_entity_id => p_wip_entity_id,
124 p_organization_id => p_organization_id,
125 p_sub_type => wip_job_details.wip_change);
126
127 /* Fix for Bug#3546027 */
128 val_dept_resources(p_group_id => p_group_id,
129 p_wip_entity_id => p_wip_entity_id,
130 p_organization_id => p_organization_id,
131 p_sub_type => wip_job_details.wip_change);
132
133 val_std_op(p_group_id => p_group_id,
134 p_wip_entity_id => p_wip_entity_id,
135 p_organization_id => p_organization_id,
136 p_sub_type => wip_job_details.wip_change);
137
138 val_mtq(p_group_id => p_group_id,
139 p_wip_entity_id => p_wip_entity_id,
140 p_organization_id => p_organization_id,
141 p_sub_type => wip_job_details.wip_change);
142
143 val_cnt_pnt(p_group_id => p_group_id,
144 p_wip_entity_id => p_wip_entity_id,
145 p_organization_id => p_organization_id,
146 p_sub_type => wip_job_details.wip_change);
147
148 val_bfl_flag(p_group_id => p_group_id,
149 p_wip_entity_id => p_wip_entity_id,
150 p_organization_id => p_organization_id,
151 p_sub_type => wip_job_details.wip_change);
152
153 val_change_op_seq_num(p_group_id => p_group_id,
154 p_wip_entity_id => p_wip_entity_id,
155 p_organization_id => p_organization_id);
156
157 val_change_sch_date(p_group_id => p_group_id,
158 p_wip_entity_id => p_wip_entity_id,
159 p_organization_id => p_organization_id);
160
161 Exception
162
163 when others then
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 x_err_msg := 'ERROR IN WIPOPVDB.CHANGE_OPERATION: ' || SQLERRM;
166 x_err_code := to_char(SQLCODE);
167
168 return;
169
170 END Change_Operation;
171
172 procedure val_null_values(p_group_id in number,
173 p_parent_header_id number, /* Fix for Bug#3636378 */
174 p_sub_type in number) is
175 cursor c_invalid_add_rows is
176 select interface_id
177 from wip_job_dtls_interface
178 where group_id = p_group_id
179 and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
180 and process_phase = wip_constants.ml_validation /* Bug 2751349 */
181 and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
182 and substitution_type = p_sub_type
183 and load_type = wip_job_details.wip_operation
184 and ( operation_seq_num is null
185 or department_id is null
186 or first_unit_start_date is null
187 or first_unit_completion_date is null
188 or last_unit_start_date is null
189 or last_unit_completion_date is null
190 or minimum_transfer_quantity is null
191 or count_point_type is null
192 or backflush_flag is null
193 );
194
195 cursor c_invalid_change_rows is
196 select interface_id
197 from wip_job_dtls_interface
198 where group_id = p_group_id
199 and parent_header_id = p_parent_header_id /* Fix for Bug#3636378 */
200 and process_phase = wip_constants.ml_validation /* Bug 2751349 */
201 and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
202 and substitution_type = p_sub_type
203 and load_type = wip_job_details.wip_operation
204 and operation_seq_num is null;
205
206 l_error_exists boolean := false;
207 l_interface_id NUMBER;
208 begin
209 if(p_sub_type = wip_job_details.wip_add) then
210 open c_invalid_add_rows;
211 elsif(p_sub_type = wip_job_details.wip_change) then
212 open c_invalid_change_rows;
213 else
214 return;
215 end if;
216
217 loop
218 if(p_sub_type = wip_job_details.wip_add) then
219 fetch c_invalid_add_rows into l_interface_id;
220 exit when c_invalid_add_rows%NOTFOUND;
221 elsif(p_sub_type = wip_job_details.wip_change) then
222 fetch c_invalid_change_rows into l_interface_id;
223 exit when c_invalid_change_rows%NOTFOUND;
224 end if;
225
226 l_error_exists := true;
227 fnd_message.set_name('WIP', 'WIP_ADD_OP_MIS_VAL');
228 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
229 if(wip_job_details.std_alone = 1) then
230 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
231 p_text => substr(fnd_message.get,1,500),
232 p_error_type => wip_jdi_utils.msg_error);
233 else
234 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
235 p_text => substr(fnd_message.get,1,500),
236 p_error_type => wip_jdi_utils.msg_error);
237 end if;
238 end loop;
239 if(c_invalid_add_rows%ISOPEN) then
240 close c_invalid_add_rows;
241 elsif(c_invalid_change_rows%ISOPEN) then
242 close c_invalid_change_rows;
243 end if;
244
245 if(l_error_exists) then
246 if(p_sub_type = wip_job_details.wip_add) then
247 update wip_job_dtls_interface wjdi
248 set process_status = wip_constants.error
249 where group_id = p_group_id
250 and process_phase = wip_constants.ml_validation /* Bug 2751349 */
251 and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
252 and substitution_type = p_sub_type
253 and load_type = wip_job_details.wip_operation
254 and ( operation_seq_num IS NULL
255 OR department_id IS NULL
256 OR first_unit_start_date IS NULL
257 OR first_unit_completion_date IS NULL
258 OR last_unit_start_date IS NULL
259 OR last_unit_completion_date IS NULL
260 OR minimum_transfer_quantity IS NULL
261 OR count_point_type IS NULL
262 OR backflush_flag IS NULL
263 );
264 else
265 update wip_job_dtls_interface wjdi
266 set process_status = wip_constants.error
267 where group_id = p_group_id
268 and process_phase = wip_constants.ml_validation /* Bug 2751349 */
269 and process_status in (wip_constants.running, wip_constants.warning) /* Bug 2751349 */
270 and substitution_type = p_sub_type
271 and load_type = wip_job_details.wip_operation
272 and operation_seq_num IS NULL;
273 end if;
274 end if;
275 end val_null_values;
276
277
278
279 /*****************************************************************
280 CHECK THAT THE GIVEN P_DEPARTMENT_ID, P_ORGANIZATION_ID COMBINATION
281 EXISTS IN BOM_DEPARTMENTS TABLE.
282 *******************************************************************/
283 procedure val_dept(p_group_id in number,
284 p_wip_entity_id in number,
285 p_organization_id in number,
286 p_sub_type in number) is
287 cursor c_invalid_rows is
288 select interface_id
289 from wip_job_dtls_interface wjdi
290 where group_id = p_group_id
291 and process_phase = wip_constants.ml_validation
292 and process_status in (wip_constants.running,
293 wip_constants.warning)
294 and load_type = wip_job_details.wip_operation
295 and substitution_type = p_sub_type
296 and wip_entity_id = p_wip_entity_id
297 and organization_id = p_organization_id
298 and ( ( department_id is null --can not add w/null dept
299 and p_sub_type = wip_job_details.wip_add
300 )
301 or ( department_id is not null
302 and not exists (select 1
303 from bom_departments
304 where department_id = wjdi.department_id
305 and organization_id = wjdi.organization_id)
306 )
307 );
308
309 l_error_exists boolean := false;
310 begin
311 for l_inv_row in c_invalid_rows loop
312 l_error_exists := true;
313 fnd_message.set_name('WIP', 'WIP_DEPARTMENT_NOT_EXIST');
314 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
315 if(wip_job_details.std_alone = 1) then
316 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
317 p_text => substr(fnd_message.get,1,500),
318 p_error_type => wip_jdi_utils.msg_error);
319 else
320 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
321 p_text => substr(fnd_message.get,1,500),
322 p_error_type => wip_jdi_utils.msg_error);
323 end if;
324 end loop;
325
326 if(l_error_exists) then
327 update wip_job_dtls_interface wjdi
328 set process_status = wip_constants.error
329 where group_id = p_group_id
330 and process_phase = wip_constants.ml_validation
331 and process_status in (wip_constants.running,
332 wip_constants.warning)
333 and wip_entity_id = p_wip_entity_id
334 and organization_id = p_organization_id
335 and ( ( department_id is null
336 and p_sub_type = wip_job_details.wip_add
337 )
338 or ( department_id is not null
339 and not exists (select 1
340 from bom_departments
341 where department_id = wjdi.department_id
342 and organization_id = wjdi.organization_id)
343 )
344 );
345 end if;
346 end val_dept;
347
348 /* Added procedure for bug#3546027*/
349 procedure val_dept_resources(p_group_id in number,
350 p_wip_entity_id in number,
351 p_organization_id in number,
352 p_sub_type in number) is
353 cursor c_invalid_rows is
354 select interface_id,
355 operation_seq_num
356 from wip_job_dtls_interface wjdi
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 load_type = wip_job_details.wip_operation
362 and substitution_type = wip_job_details.wip_change
363 and wip_entity_id = p_wip_entity_id
364 and organization_id = p_organization_id
365 and department_id is not null
366 and exists (select 1
367 from wip_operation_resources wor,
368 wip_operations wo
369 where wo.wip_entity_id = wjdi.wip_entity_id
370 and wo.operation_seq_num = wjdi.operation_seq_num
371 and wo.organization_id = wjdi.organization_id
372 and wo.wip_entity_id = wor.wip_entity_id
373 and wo.operation_seq_num = wor.operation_seq_num
374 and wo.organization_id = wor.organization_id
375 and nvl(wo.repetitive_schedule_id, 1) =
376 nvl(wor.repetitive_schedule_id, 1)
377 and wo.department_id <> wjdi.department_id
378 ) ;
379
380 l_error_exists boolean := false;
381 begin
382
383 for l_inv_row in c_invalid_rows loop
384 l_error_exists := true;
385 fnd_message.set_name('WIP', 'WIP_DEPARTMENT_RESOURCES_EXIST');
386 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
387 fnd_message.set_token('OPERATION_SEQ_NUM', to_char(l_inv_row.operation_seq_num));
388 if(wip_job_details.std_alone = 1) then
389 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
390 p_text => substr(fnd_message.get,1,500),
391 p_error_type => wip_jdi_utils.msg_error);
392 else
393 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
394 p_text => substr(fnd_message.get,1,500),
395 p_error_type => wip_jdi_utils.msg_error);
396 end if;
397 end loop;
398
399 if(l_error_exists) then
400 update wip_job_dtls_interface wjdi
401 set process_status = wip_constants.error
402 where group_id = p_group_id
403 and process_phase = wip_constants.ml_validation
404 and process_status in (wip_constants.running,
405 wip_constants.warning)
406 and load_type = wip_job_details.wip_operation
407 and substitution_type = wip_job_details.wip_change
408 and wip_entity_id = p_wip_entity_id
409 and organization_id = p_organization_id
410 and department_id is not null
411 and exists (select 1
412 from wip_operation_resources wor,
413 wip_operations wo
414 where wo.wip_entity_id = wjdi.wip_entity_id
415 and wo.operation_seq_num = wjdi.operation_seq_num
416 and wo.organization_id = wjdi.organization_id
417 and wo.wip_entity_id = wor.wip_entity_id
418 and wo.operation_seq_num = wor.operation_seq_num
419 and wo.organization_id = wor.organization_id
420 and nvl(wo.repetitive_schedule_id, 1) =
421 nvl(wor.repetitive_schedule_id, 1)
422 and wo.department_id <> wjdi.department_id
423 ) ;
424 end if ;
425
426 end val_dept_resources;
427
428 /************VALIDATE STANDARD OPERATIONS **************************/
429 procedure val_std_op(p_group_id in number,
430 p_wip_entity_id in number,
431 p_organization_id in number,
432 p_sub_type in number) is
433 cursor c_invalid_rows is
434 select interface_id
435 from wip_job_dtls_interface wjdi
436 where group_id = p_group_id
437 and process_phase = wip_constants.ml_validation
438 and process_status in (wip_constants.running,
439 wip_constants.warning)
440 and load_type = wip_job_details.wip_operation
441 and substitution_type = p_sub_type
442 and wip_entity_id = p_wip_entity_id
443 and organization_id = p_organization_id
444 and standard_operation_id is not null
445 and standard_operation_id <> fnd_api.g_miss_num
446 and not exists (select 1
447 from bom_standard_operations
448 where standard_operation_id = wjdi.standard_operation_id
449 and organization_id = wjdi.organization_id);
450
451 l_error_exists boolean := false;
452 begin
453
454 for l_inv_row in c_invalid_rows loop
455 l_error_exists := true;
456 fnd_message.set_name('WIP', 'WIP_STD_OPER_NOT_EXIST');
457 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
458 if(wip_job_details.std_alone = 1) then
459 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
460 p_text => substr(fnd_message.get,1,500),
461 p_error_type => wip_jdi_utils.msg_error);
462 else
463 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
464 p_text => substr(fnd_message.get,1,500),
465 p_error_type => wip_jdi_utils.msg_error);
466 end if;
467 end loop;
468
469 if(l_error_exists) then
470 update wip_job_dtls_interface wjdi
471 set process_status = wip_constants.error
472 where group_id = p_group_id
473 and process_phase = wip_constants.ml_validation
474 and process_status in (wip_constants.running,
475 wip_constants.warning)
476 and load_type = wip_job_details.wip_operation
477 and substitution_type = p_sub_type
478 and wip_entity_id = p_wip_entity_id
479 and organization_id = p_organization_id
480 and standard_operation_id is not null
481 and standard_operation_id <> fnd_api.g_miss_num
482 and not exists (select 1
483 from bom_standard_operations
484 where standard_operation_id = wjdi.standard_operation_id
485 and organization_id = wjdi.organization_id);
486 end if;
487 end val_std_op;
488
489
490
491 /*************VALIDATE MINIMUM TRANSFER QUANTITY ************************/
492 procedure val_mtq(p_group_id in number,
493 p_wip_entity_id in number,
494 p_organization_id in number,
495 p_sub_type in number) is
496 cursor c_invalid_rows is
497 select interface_id
498 from wip_job_dtls_interface
499 where group_id = p_group_id
500 and process_phase = wip_constants.ml_validation
501 and process_status in (wip_constants.running,
502 wip_constants.warning)
503 and load_type = wip_job_details.wip_operation
504 and substitution_type = p_sub_type
505 and wip_entity_id = p_wip_entity_id
506 and organization_id = p_organization_id
507 and ( ( p_sub_type = wip_job_details.wip_add
508 and minimum_transfer_quantity is null
509 )
510 or minimum_transfer_quantity < 0);
511
512 l_error_exists boolean := false;
513 begin
514 for l_inv_row in c_invalid_rows loop
515 l_error_exists := true;
516 fnd_message.set_name('WIP', 'WIP_INVALID_MIN_XFER_QTY');
517 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
518 if(wip_job_details.std_alone = 1) then
519 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
520 p_text => substr(fnd_message.get,1,500),
521 p_error_type => wip_jdi_utils.msg_error);
522 else
523 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
524 p_text => substr(fnd_message.get,1,500),
525 p_error_type => wip_jdi_utils.msg_error);
526 end if;
527 end loop;
528
529 if(l_error_exists) then
530 update wip_job_dtls_interface wjdi
531 set process_status = wip_constants.error
532 where group_id = p_group_id
533 and process_phase = wip_constants.ml_validation
534 and process_status in (wip_constants.running,
535 wip_constants.warning)
536 and load_type = wip_job_details.wip_operation
537 and substitution_type = p_sub_type
538 and wip_entity_id = p_wip_entity_id
539 and organization_id = p_organization_id
540 and ( ( minimum_transfer_quantity is null
541 and p_sub_type = wip_job_details.wip_add
542 )
543 or minimum_transfer_quantity < 0 );
544 end if;
545 end val_mtq;
546
547
548
549 /***************VALIDATE COUNT_POINT_TYPE *********************/
550 procedure val_cnt_pnt(p_group_id in number,
551 p_wip_entity_id in number,
552 p_organization_id in number,
553 p_sub_type in number) is
554 cursor c_invalid_rows is
555 select interface_id
556 from wip_job_dtls_interface wjdi
557 where group_id = p_group_id
558 and process_phase = wip_constants.ml_validation
559 and process_status in (wip_constants.running,
560 wip_constants.warning)
561 and load_type = wip_job_details.wip_operation
562 and substitution_type = p_sub_type
563 and wip_entity_id = p_wip_entity_id
564 and organization_id = p_organization_id
565 and ( ( count_point_type is null
566 and p_sub_type = wip_job_details.wip_add
567 )
568 or ( count_point_type is not null
569 and not exists (select 1
570 from mfg_lookups mfg_l
571 where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
572 and mfg_l.lookup_code = wjdi.count_point_type )
573 )
574 );
575
576 l_error_exists boolean := false;
577 begin
578
579 for l_inv_row in c_invalid_rows loop
580 l_error_exists := true;
581 fnd_message.set_name('WIP', 'WIP_INVALID_COUNT_POINT');
582 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
583 if(wip_job_details.std_alone = 1) then
584 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
585 p_text => substr(fnd_message.get,1,500),
586 p_error_type => wip_jdi_utils.msg_error);
587 else
588 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
589 p_text => substr(fnd_message.get,1,500),
590 p_error_type => wip_jdi_utils.msg_error);
591 end if;
592 end loop;
593
594 if(l_error_exists) then
595 update wip_job_dtls_interface wjdi
596 set process_status = wip_constants.error
597 where group_id = p_group_id
598 and process_phase = wip_constants.ml_validation
599 and process_status in (wip_constants.running,
600 wip_constants.warning)
601 and load_type = wip_job_details.wip_operation
602 and substitution_type = p_sub_type
603 and wip_entity_id = p_wip_entity_id
604 and organization_id = p_organization_id
605 and ( ( count_point_type is null
606 and p_sub_type = wip_job_details.wip_add
607 )
608 or ( count_point_type is not null
609 and not exists (select 1
610 from mfg_lookups mfg_l
611 where mfg_l.lookup_type = 'BOM_COUNT_POINT_TYPE'
612 and mfg_l.lookup_code = wjdi.count_point_type )
613 )
614 );
615 end if;
616 end val_cnt_pnt;
617
618
619
620
621 /***************VALIDATE COUNT_POINT_TYPE *********************/
622 procedure val_bfl_flag(p_group_id in number,
623 p_wip_entity_id in number,
624 p_organization_id in number,
625 p_sub_type in number) is
626 cursor c_invalid_rows is
627 select interface_id
628 from wip_job_dtls_interface wjdi
629 where group_id = p_group_id
630 and process_phase = wip_constants.ml_validation
631 and process_status in (wip_constants.running,
632 wip_constants.warning)
633 and load_type = wip_job_details.wip_operation
634 and substitution_type = p_sub_type
635 and wip_entity_id = p_wip_entity_id
636 and organization_id = p_organization_id
637 and ( ( backflush_flag is null
638 and p_sub_type = wip_job_details.wip_add
639 )
640 or ( backflush_flag is not null
641 and not exists (select 1
642 from mfg_lookups mfg_l
643 where mfg_l.lookup_type = 'SYS_YES_NO'
644 and mfg_l.lookup_code = wjdi.backflush_flag)
645 )
646 );
647
648 l_error_exists boolean := false;
649 begin
650
651 for l_inv_row in c_invalid_rows loop
652 l_error_exists := true;
653 fnd_message.set_name('WIP', 'WIP_INVALID_BF_FLAG');
654 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
655 if(wip_job_details.std_alone = 1) then
656 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
657 p_text => substr(fnd_message.get,1,500),
658 p_error_type => wip_jdi_utils.msg_error);
659 else
660 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
661 p_text => substr(fnd_message.get,1,500),
662 p_error_type => wip_jdi_utils.msg_error);
663 end if;
664 end loop;
665
666 if(l_error_exists) then
667 update wip_job_dtls_interface wjdi
668 set process_status = wip_constants.error
669 where group_id = p_group_id
670 and load_type = wip_job_details.wip_operation
671 and substitution_type = p_sub_type
672 and process_phase = wip_constants.ml_validation
673 and process_status in (wip_constants.running,
674 wip_constants.warning)
675 and wip_entity_id = p_wip_entity_id
676 and organization_id = p_organization_id
677 and ( ( backflush_flag is null
678 and p_sub_type = wip_job_details.wip_add
679 )
680 or ( backflush_flag is not null
681 and not exists (select 1
682 from mfg_lookups mfg_l
683 where mfg_l.lookup_type = 'SYS_YES_NO'
684 and mfg_l.lookup_code = wjdi.backflush_flag)
685 )
686 );
687 end if;
688 end val_bfl_flag;
689
690
691
692 /*****************************************************************
693 CHECK THAT NO RECORDS EXIST IN WIP_OPERATIONS TABLE WITH THE GIVEN
694 WIP_ENTITY_ID, ORGANIZATION_ID AND OPERATION_SEQ_NUM.
695 *****************************************************************/
696 procedure val_add_op_seq_num(p_group_id in number,
697 p_wip_entity_id in number,
698 p_organization_id in number) is
699 cursor c_invalid_rows is
700 select interface_id, operation_seq_num
701 from wip_job_dtls_interface wjdi
702 where group_id = p_group_id
703 and process_phase = wip_constants.ml_validation
704 and process_status in (wip_constants.running,
705 wip_constants.warning)
706 and load_type = wip_job_details.wip_operation
707 and substitution_type = wip_job_details.wip_add
708 and wip_entity_id = p_wip_entity_id
709 and organization_id = p_organization_id
710 and (exists (select 1
711 from wip_operations
712 where wip_entity_id = wjdi.wip_entity_id
713 and organization_id = wjdi.organization_id
714 and operation_seq_num = wjdi.operation_seq_num) or
715 operation_seq_num <= 0);
716
717 l_error_exists boolean := false;
718 begin
719
720 for l_inv_row in c_invalid_rows loop
721 l_error_exists := true;
722 if(l_inv_row.operation_seq_num <= 10) then
723 fnd_message.set_name('WIP','WIP_GREATER_THAN');
724 fnd_message.set_token('ENTITY1', 'OPERATION SEQUENCE NUMBER-CAP', TRUE);
725 fnd_message.set_token('ENTITY2', '0', FALSE);
726 else
727 fnd_message.set_name('WIP', 'WIP_OPERATION_ALREADY_EXIST');
728 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
729 end if;
730 if(wip_job_details.std_alone = 1) then
731 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
732 p_text => substr(fnd_message.get,1,500),
733 p_error_type => wip_jdi_utils.msg_error);
734 else
735 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
736 p_text => substr(fnd_message.get,1,500),
737 p_error_type => wip_jdi_utils.msg_error);
738 end if;
739 end loop;
740
741 if(l_error_exists) then
742 update wip_job_dtls_interface wjdi
743 set process_status = wip_constants.error
744 where group_id = p_group_id
745 and load_type = wip_job_details.wip_operation
746 and substitution_type = wip_job_details.wip_add
747 and process_phase = wip_constants.ml_validation
748 and process_status in (wip_constants.running,
749 wip_constants.warning)
750 and wip_entity_id = p_wip_entity_id
751 and organization_id = p_organization_id
752 and (exists (select 1
753 from wip_operations
754 where wip_entity_id = wjdi.wip_entity_id
755 and organization_id = wjdi.organization_id
756 and operation_seq_num = wjdi.operation_seq_num)
757 or operation_seq_num <= 0);
758 end if;
759 end val_add_op_seq_num;
760
761
762
763 /*************VALIDATE SCHEDULE DATE **********************************/
764 procedure val_add_sch_date(p_group_id in number,
765 p_wip_entity_id in number,
766 p_organization_id in number,
767 p_parent_header_id in number) is
768 cursor c_ml_invalid_rows is
769 select interface_id
770 from wip_job_dtls_interface wjdi
771 where group_id = p_group_id
772 and process_phase = wip_constants.ml_validation
773 and process_status in (wip_constants.running,
774 wip_constants.warning)
775 and load_type = wip_job_details.wip_operation
776 and substitution_type = wip_job_details.wip_add
777 and wip_entity_id = p_wip_entity_id
778 and organization_id = p_organization_id
779 and parent_header_id = p_parent_header_id
780 and ( wjdi.first_unit_start_date is null
781 or wjdi.first_unit_completion_date is null
782 or wjdi.last_unit_start_date is null
783 or wjdi.last_unit_completion_date is null
784 /* bug3669728 begin */
785 or wjdi.first_unit_start_date > wjdi.last_unit_start_date
786 or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
787 or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
788 or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
789 /* bug3669728 end */
790 );
791 /*bug 3659006->should not check for operation FUSD,LUCD to be within job's start/completion dates */
792
793 cursor c_wdj_invalid_rows is
794 select interface_id
795 from wip_job_dtls_interface wjdi
796 where group_id = p_group_id
797 and process_phase = wip_constants.ml_validation
798 and process_status in (wip_constants.running,
799 wip_constants.warning)
800 and load_type = wip_job_details.wip_operation
801 and substitution_type = wip_job_details.wip_add
802 and wip_entity_id = p_wip_entity_id
803 and organization_id = p_organization_id
804 and ( first_unit_start_date is null
805 or first_unit_completion_date is null
806 or last_unit_start_date is null
807 or last_unit_completion_date is null
808 /* bug3669728 begin */
809 or wjdi.first_unit_start_date > wjdi.last_unit_start_date
810 or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
811 or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
812 or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
813 /* bug3669728 end */
814 );
815 /*bug 3659006->should not check for operation FUSD,LUCD to be within job's start/completion dates */
816
817 l_interface_id NUMBER;
818 l_error_exists boolean := false;
819 begin
820
821 if(wip_job_details.std_alone = 0) then
822 open c_ml_invalid_rows;
823 else
824 open c_wdj_invalid_rows;
825 end if;
826
827 loop
828 if(wip_job_details.std_alone = 0) then
829 fetch c_ml_invalid_rows into l_interface_id;
830 exit when c_ml_invalid_rows%NOTFOUND;
831 else
832 fetch c_wdj_invalid_rows into l_interface_id;
833 exit when c_wdj_invalid_rows%NOTFOUND;
834 end if;
835 l_error_exists := true; --loop executes only for invalid rows!
836 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_DATE');
837 fnd_message.set_token('INTERFACE', to_char(l_interface_id));
838 if(wip_job_details.std_alone = 1) then
839 wip_interface_err_Utils.add_error(p_interface_id => l_interface_id,
840 p_text => substr(fnd_message.get,1,500),
841 p_error_type => wip_jdi_utils.msg_error);
842 else
843 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
844 p_text => substr(fnd_message.get,1,500),
845 p_error_type => wip_jdi_utils.msg_error);
846 end if;
847
848
849 end loop;
850 if(c_ml_invalid_rows%ISOPEN) then
851 close c_ml_invalid_rows;
852 elsif(c_wdj_invalid_rows%ISOPEN) then
853 close c_wdj_invalid_rows;
854 end if;
855
856 if(l_error_exists) then
857 if(wip_job_details.std_alone = 1) then
858 update wip_job_dtls_interface wjdi
859 set process_status = wip_constants.error
860 where group_id = p_group_id
861 and process_phase = wip_constants.ml_validation
862 and process_status in (wip_constants.running,
863 wip_constants.warning)
864 and load_type = wip_job_details.wip_operation
865 and substitution_type = wip_job_details.wip_add
866 and wip_entity_id = p_wip_entity_id
867 and organization_id = p_organization_id
868 and ( wjdi.first_unit_start_date is null
869 or wjdi.first_unit_completion_date is null
870 or wjdi.last_unit_start_date is null
871 or wjdi.last_unit_completion_date is null
872 /* bug3669728 begin */
873 or wjdi.first_unit_start_date > wjdi.last_unit_start_date
874 or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
875 or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
876 or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
877 /* bug3669728 end */
878 );
879 else
880 update wip_job_dtls_interface wjdi
881 set process_status = wip_constants.error
882 where group_id = p_group_id
883 and process_phase = wip_constants.ml_validation
884 and process_status in (wip_constants.running,
885 wip_constants.warning)
886 and load_type = wip_job_details.wip_operation
887 and substitution_type = wip_job_details.wip_add
888 and wip_entity_id = p_wip_entity_id
889 and organization_id = p_organization_id
890 and parent_header_id = p_parent_header_id
891 and ( wjdi.first_unit_start_date is null
892 or wjdi.first_unit_completion_date is null
893 or wjdi.last_unit_start_date is null
894 or wjdi.last_unit_completion_date is null
895 /* bug3669728 begin */
896 or wjdi.first_unit_start_date > wjdi.last_unit_start_date
897 or wjdi.first_unit_completion_date > wjdi.last_unit_completion_date
898 or wjdi.first_unit_start_date > wjdi.first_unit_completion_date
899 or wjdi.last_unit_start_date > wjdi.last_unit_completion_date
900 /* bug3669728 end */
901 );
902 end if;
903 end if;
904 end val_add_sch_date;
905
906
907
908
909 /*****************************************************************
910 CHECK THAT A RECORD EXISTS IN WIP_OPERATIONS TABLE WITH THE GIVEN
911 WIP_ENTITY_ID, ORGANIZATION_ID AND OPERATION_SEQ_NUM.
912 *****************************************************************/
913 procedure val_change_op_seq_num(p_group_id in number,
914 p_wip_entity_id in number,
915 p_organization_id in number) is
916 cursor c_invalid_rows is
917 select interface_id
918 from wip_job_dtls_interface wjdi
919 where group_id = p_group_id
920 and process_phase = wip_constants.ml_validation
921 and process_status in (wip_constants.running,
922 wip_constants.warning)
923 and load_type = wip_job_details.wip_operation
924 and substitution_type = wip_job_details.wip_change
925 and wip_entity_id = p_wip_entity_id
926 and not exists (select 1
927 from wip_operations
928 where wip_entity_id = wjdi.wip_entity_id
929 and organization_id = wjdi.organization_id
930 and operation_seq_num = wjdi.operation_seq_num);
931
932 l_error_exists boolean := false;
933 begin
934
935 for l_inv_row in c_invalid_rows loop
936 l_error_exists := true;
937 fnd_message.set_name('WIP', 'WIP_OP_NOT_FOUND');
938 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
939 if(wip_job_details.std_alone = 1) then
940 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
941 p_text => substr(fnd_message.get,1,500),
942 p_error_type => wip_jdi_utils.msg_error);
943 else
944 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
945 p_text => substr(fnd_message.get,1,500),
946 p_error_type => wip_jdi_utils.msg_error);
947 end if;
948 end loop;
949
950 if(l_error_exists) then
951 update wip_job_dtls_interface wjdi
952 set process_status = wip_constants.error
953 where group_id = p_group_id
954 and process_phase = wip_constants.ml_validation
955 and process_status in (wip_constants.running,
956 wip_constants.warning)
957 and wip_entity_id = p_wip_entity_id
958 and not exists (select 1
959 from wip_operations
960 where wip_entity_id = wjdi.wip_entity_id
961 and organization_id = wjdi.organization_id
962 and operation_seq_num = wjdi.operation_seq_num);
963 end if;
964 end val_change_op_seq_num;
965
966
967
968 /*************VALIDATE SCHEDULE DATE **********************************/
969 procedure val_change_sch_date(p_group_id in number,
970 p_wip_entity_id in number,
971 p_organization_id in number) is
972
973 cursor c_invalid_rows is
974 select interface_id
975 from wip_job_dtls_interface wjdi
976 where group_id = p_group_id
977 and process_phase = wip_constants.ml_validation
978 and process_status in (wip_constants.running,
979 wip_constants.warning)
980 and load_type = wip_job_details.wip_operation
981 and substitution_type = wip_job_details.wip_change
982 and wip_entity_id = p_wip_entity_id
983 and organization_id = p_organization_id
984 and ( wjdi.last_unit_start_date is not null
985 or wjdi.first_unit_start_date is not null
986 or wjdi.first_unit_completion_date is not null
987 or wjdi.last_unit_completion_date is not null
988 )
989 and exists (select 1
990 from wip_operations wo
991 where (
992 /* bug#3669728 begin */
993 (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
994 (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
995 or
996 (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
997 (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
998 or
999 (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
1000 (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
1001 or
1002 (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
1003 (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
1004 /* bug#3669728 end */
1005 )
1006 and wip_entity_id = wjdi.wip_entity_id
1007 and organization_id = wjdi.organization_id
1008 and operation_seq_num = wjdi.operation_seq_num);
1009
1010 l_error_exists boolean := false;
1011 begin
1012
1013 for l_row in c_invalid_rows loop
1014 l_error_exists := true; --loop executes only for invalid rows!
1015 fnd_message.set_name('WIP', 'WIP_INVALID_SCHEDULE_DATE');
1016 fnd_message.set_token('INTERFACE', to_char(l_row.interface_id));
1017 if(wip_job_details.std_alone = 1) then
1018 wip_interface_err_Utils.add_error(p_interface_id => l_row.interface_id,
1019 p_text => substr(fnd_message.get,1,500),
1020 p_error_type => wip_jdi_utils.msg_error);
1021 else
1022 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1023 p_text => substr(fnd_message.get,1,500),
1024 p_error_type => wip_jdi_utils.msg_error);
1025 end if;
1026 end loop;
1027
1028 if(l_error_exists) then
1029 update wip_job_dtls_interface wjdi
1030 set process_status = wip_constants.error
1031 where group_id = p_group_id
1032 and process_phase = wip_constants.ml_validation
1033 and process_status in (wip_constants.running,
1034 wip_constants.warning)
1035 and load_type = wip_job_details.wip_operation
1036 and substitution_type = wip_job_details.wip_change
1037 and wip_entity_id = p_wip_entity_id
1038 and organization_id = p_organization_id
1039 and ( wjdi.last_unit_start_date is not null
1040 or wjdi.first_unit_start_date is not null
1041 or wjdi.first_unit_completion_date is not null
1042 or wjdi.last_unit_completion_date is not null
1043 )
1044 and exists (select 1
1045 from wip_operations wo
1046 where (
1047 /* bug#3669728 begin */
1048 (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
1049 (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date))
1050 or
1051 (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date)) >
1052 (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
1053 or
1054 (nvl(wjdi.first_unit_start_date, wo.first_unit_start_date)) >
1055 (nvl(wjdi.first_unit_completion_date, wo.first_unit_completion_date))
1056 or
1057 (nvl(wjdi.last_unit_start_date, wo.last_unit_start_date)) >
1058 (nvl(wjdi.last_unit_completion_date, wo.last_unit_completion_date))
1059 /* bug#3669728 end */
1060 )
1061 and wip_entity_id = wjdi.wip_entity_id
1062 and organization_id = wjdi.organization_id
1063 and operation_seq_num = wjdi.operation_seq_num);/*bug 3659006 */
1064
1065 /* Fix for Bug#3141768. Changed where condition */
1066
1067 end if;
1068
1069 end val_change_sch_date;
1070
1071 END WIP_OPERATION_VALIDATE;