[Home] [Help]
PACKAGE BODY: APPS.WIP_RES_INST_VALIDATIONS
Source
1 PACKAGE BODY WIP_RES_INST_VALIDATIONS AS
2 /* $Header: wiprivdb.pls 120.5.12020000.2 2013/01/18 12:40:52 sisankar ship $ */
3
4 function IS_Error(p_group_id number,
5 p_wip_entity_id number,
6 p_organization_id number,
7 p_substitution_type number,
8 p_operation_seq_num number,
9 p_resource_seq_num number) return number IS
10
11 x_count number := 0;
12
13 BEGIN
14
15 SELECT count(*)
16 INTO x_count
17 FROM WIP_JOB_DTLS_INTERFACE
18 WHERE group_id = p_group_id
19 AND process_status = WIP_CONSTANTS.ERROR
20 AND wip_entity_id = p_wip_entity_id
21 AND organization_id = p_organization_id
22 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
23 AND substitution_type= p_substitution_type
24 AND operation_seq_num= p_operation_seq_num
25 AND resource_seq_num = p_resource_seq_num;
26
27
28 IF x_count <> 0 THEN
29 return 1;
30 ELSE return 0;
31 END IF;
32
33 END IS_Error;
34
35 Procedure Valid_Dates(p_group_id number,
36 p_wip_entity_id number,
37 p_organization_id number,
38 p_substitution_type number,
39 p_operation_seq_num number,
40 p_resource_seq_num number,
41 p_instance_id number,
42 p_resource_type number) IS
43
44 cursor c_invalid_rows is
45 select interface_id
46 from wip_job_dtls_interface wjdi
47 where wjdi.group_id = p_group_id
48 and wjdi.process_phase = wip_constants.ml_validation
49 and wjdi.process_status in (wip_constants.running,
50 wip_constants.warning)
51 and wjdi.wip_entity_id = p_wip_entity_id
52 and wjdi.organization_id = p_organization_id
53 and wjdi.load_type = wip_job_details.wip_res_instance
54 and wjdi.substitution_type = p_substitution_type
55 and wjdi.operation_seq_num = p_operation_seq_num
56 and wjdi.resource_seq_num = p_resource_seq_num /* fix for bug4238691 */
57 and wjdi.substitution_type in
58 (wip_job_details.wip_add, wip_job_details.wip_change)
59 and (wjdi.start_date > wjdi.completion_date
60 or exists
61 (select 1
62 from wip_operation_resources wor
63 where wor.wip_entity_id = p_wip_entity_id
64 and wor.organization_id = p_organization_id
65 and wor.operation_seq_num = p_operation_seq_num
66 and wor.resource_seq_num = p_resource_seq_num
67 and (wor.start_date > wjdi.start_date
68 or wor.completion_date < wjdi.completion_date))
69 or (p_resource_type = 2 and
70 exists (select 1
71 from bom_resource_employees brem
72 where brem.organization_id = p_organization_id
73 and brem.instance_id = p_instance_id
74 and (brem.effective_start_date > wjdi.start_date
75 or brem.effective_end_date < wjdi.completion_date))));
76
77 l_error_exists boolean := false;
78 begin
79
80 for l_inv_row in c_invalid_rows loop
81 l_error_exists := true;
82 fnd_message.set_name('WIP', 'WIP_JDI_RI_INVALID_DATES');
83 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
84 if(wip_job_details.std_alone = 1) then
85 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
86 p_text => substr(fnd_message.get,1,500),
87 p_error_type => wip_jdi_utils.msg_error);
88 else
89 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
90 p_text => substr(fnd_message.get,1,500),
91 p_error_type => wip_jdi_utils.msg_error);
92 end if;
93 end loop;
94
95 if(l_error_exists) then
96 update wip_job_dtls_interface wjdi
97 set process_status = wip_constants.error
98 where wjdi.group_id = p_group_id
99 and wjdi.process_phase = wip_constants.ml_validation
100 and wjdi.process_status in (wip_constants.running,
101 wip_constants.warning)
102 and wjdi.wip_entity_id = p_wip_entity_id
103 and wjdi.organization_id = p_organization_id
104 and wjdi.load_type = wip_job_details.wip_res_instance
105 and wjdi.substitution_type = p_substitution_type
106 and wjdi.operation_seq_num = p_operation_seq_num
107 and wjdi.substitution_type in
108 (wip_job_details.wip_add, wip_job_details.wip_change)
109 and (wjdi.start_date > wjdi.completion_date
110 or exists
111 (select 1
112 from wip_operation_resources wor
113 where wor.wip_entity_id = p_wip_entity_id
114 and wor.organization_id = p_organization_id
115 and wor.operation_seq_num = p_operation_seq_num
116 and wor.resource_seq_num = p_resource_seq_num
117 and (wor.start_date > wjdi.start_date
118 or wor.completion_date < wjdi.completion_date))
119 or (p_resource_type = 2 and
120 exists (select 1
121 from bom_resource_employees brem
122 where brem.organization_id = p_organization_id
123 and brem.instance_id = p_instance_id
124 and (brem.effective_start_date > wjdi.start_date
125 or brem.effective_end_date < wjdi.completion_date))));
126
127 end if;
128 end Valid_Dates;
129
130
131 /* resource_seq_num, instance_id must not be null when add/change/delete
132 resource instance */
133 Procedure ResInst_Info_Exist(p_group_id number,
134 p_wip_entity_id number,
135 p_organization_id number,
136 p_substitution_type number,
137 p_operation_seq_num number,
138 p_resource_seq_num number,
139 p_resource_instance_id number,
140 p_resource_type number) IS
141
142 cursor c_invalid_rows is
143 select interface_id
144 from wip_job_dtls_interface wjdi
145 where wjdi.group_id = p_group_id
146 and wjdi.process_phase = wip_constants.ml_validation
147 and wjdi.process_status in (wip_constants.running,
148 wip_constants.warning)
149 and wjdi.wip_entity_id = p_wip_entity_id
150 and wjdi.organization_id = p_organization_id
151 and wjdi.load_type = wip_job_details.wip_res_instance
152 and wjdi.substitution_type = p_substitution_type
153 and wjdi.operation_seq_num = p_operation_seq_num
154 and wjdi.resource_seq_num = p_resource_seq_num
155 and wjdi.resource_instance_id = p_resource_instance_id
156 and ( wjdi.resource_seq_num is null
157 or (wjdi.substitution_type <> wip_job_details.wip_delete
158 and wjdi.resource_instance_id is null)
159 or (wjdi.substitution_type = wip_job_details.wip_add
160 and wjdi.resource_serial_number is null
161 and p_resource_type = 1 ));
162
163 l_error_exists boolean := false;
164 begin
165
166 for l_inv_row in c_invalid_rows loop
167 l_error_exists := true;
168 fnd_message.set_name('WIP', 'WIP_JDI_RES_INST_INFO_MISSING');
169 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
170 if(wip_job_details.std_alone = 1) then
171 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
172 p_text => substr(fnd_message.get,1,500),
173 p_error_type => wip_jdi_utils.msg_error);
174 else
175 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
176 p_text => substr(fnd_message.get,1,500),
177 p_error_type => wip_jdi_utils.msg_error);
178 end if;
179 end loop;
180
181 if(l_error_exists) then
182 update wip_job_dtls_interface wjdi
183 set process_status = wip_constants.error
184 where wjdi.group_id = p_group_id
185 and wjdi.process_phase = wip_constants.ml_validation
186 and wjdi.process_status in (wip_constants.running,
187 wip_constants.warning)
188 and wjdi.wip_entity_id = p_wip_entity_id
189 and wjdi.organization_id = p_organization_id
190 and wjdi.load_type = wip_job_details.wip_res_instance
191 and wjdi.substitution_type = p_substitution_type
192 and wjdi.operation_seq_num = p_operation_seq_num
193 and ( wjdi.resource_seq_num is null
194 or (wjdi.substitution_type <> wip_job_details.wip_delete
195 and wjdi.resource_instance_id is null)
196 or (wjdi.substitution_type = wip_job_details.wip_add
197 and wjdi.resource_serial_number is null
198 and p_resource_type = 1 ));
199 end if;
200 end resinst_info_exist;
201
202 Procedure Valid_Resource_Instance(p_group_id number,
203 p_wip_entity_id number,
204 p_organization_id number,
205 p_substitution_type number,
206 p_operation_seq_num number,
207 p_resource_seq_num number,
208 p_resource_instance_id number ) IS
209
210
211 cursor c_invalid_rows(p_dept_id number, p_resource_id number) is
212 select interface_id
213 from wip_job_dtls_interface wjdi
214 where wjdi.group_id = p_group_id
215 and wjdi.process_phase = wip_constants.ml_validation
216 and wjdi.process_status in (wip_constants.running,
217 wip_constants.warning)
218 and wjdi.wip_entity_id = p_wip_entity_id
219 and wjdi.organization_id = p_organization_id
220 and wjdi.load_type = wip_job_details.wip_res_instance
221 and wjdi.substitution_type = p_substitution_type
222 and wjdi.operation_seq_num = p_operation_seq_num
223 and wjdi.resource_seq_num = p_resource_seq_num
224 and wjdi.resource_instance_id = p_resource_instance_id
225 and wjdi.resource_instance_id not in ( select bdri.instance_id
226 from bom_dept_res_instances bdri,
227 bom_department_resources bdr
228 where bdr.department_id = p_dept_id
229 and bdr.resource_id = p_resource_id
230 and bdri.resource_id = bdr.resource_id
231 and bdri.department_id = nvl(bdr.share_from_dept_id,bdr.department_id));
232 l_error_exists boolean := false;
233 l_dept_id number;
234 l_resource_id number;
235 begin
236
237 select wo.department_id, wor.resource_id
238 into l_dept_id, l_resource_id
239 from wip_operations wo, wip_operation_resources wor
240 where wo.wip_entity_id = p_wip_entity_id
241 and wo.operation_seq_num = p_operation_seq_num
242 and wo.organization_id = p_organization_id
243 and wo.repetitive_schedule_id is null
244 and wor.wip_entity_id = p_wip_entity_id
245 and wor.operation_seq_num = p_operation_seq_num
246 and wor.resource_seq_num = p_resource_seq_num
247 and wor.organization_id = p_organization_id
248 and wor.repetitive_schedule_id is null;
249
250 -- Validate when adding resource instances
251 -- resources instances to be added must exist in department of the operation
252 -- and in the resource being added to
253 for l_inv_row in c_invalid_rows(l_dept_id, l_resource_id) loop
254 l_error_exists := true;
255 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_RES_INST_ID');
256 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
257 if(wip_job_details.std_alone = 1) then
258 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
259 p_text => substr(fnd_message.get,1,500),
260 p_error_type => wip_jdi_utils.msg_error);
261 else
262 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
263 p_text => substr(fnd_message.get,1,500),
264 p_error_type => wip_jdi_utils.msg_error);
265 end if;
266 end loop;
267 if(l_error_exists) then
268 update wip_job_dtls_interface wjdi
269 set process_status = wip_constants.error
270 where wjdi.group_id = p_group_id
271 and wjdi.process_phase = wip_constants.ml_validation
272 and wjdi.process_status in (wip_constants.running,
273 wip_constants.warning)
274 and wjdi.wip_entity_id = p_wip_entity_id
275 and wjdi.organization_id = p_organization_id
276 and wjdi.load_type in (wip_job_details.wip_res_instance, wjdi.load_type)
277 and wjdi.substitution_type = p_substitution_type
278 and wjdi.operation_seq_num = p_operation_seq_num
279 and wjdi.resource_seq_num = p_resource_seq_num
280 and wjdi.resource_instance_id = p_resource_instance_id
281 and wjdi.resource_instance_id not in ( select bdri.instance_id
282 from bom_dept_res_instances bdri,
283 bom_department_resources bdr
284 where bdr.department_id = l_dept_id
285 and bdr.resource_id = l_resource_id
286 and bdri.resource_id = bdr.resource_id
287 and bdri.department_id = nvl(bdr.share_from_dept_id,bdr.department_id));
288 end if;
289
290 end valid_resource_instance;
291
292
293 Procedure Valid_Serial_Number(p_group_id number,
294 p_wip_entity_id number,
295 p_organization_id number,
296 p_substitution_type number,
297 p_operation_seq_num number,
298 p_resource_seq_num number,
299 p_resource_instance_id number,
300 p_resource_serial_number varchar2 ) IS
301
302
303 cursor c_invalid_rows(p_dept_id number, p_resource_id number) is
304 select interface_id
305 from wip_job_dtls_interface wjdi
306 where wjdi.group_id = p_group_id
307 and wjdi.process_phase = wip_constants.ml_validation
308 and wjdi.process_status in (wip_constants.running,
309 wip_constants.warning)
310 and wjdi.wip_entity_id = p_wip_entity_id
311 and wjdi.organization_id = p_organization_id
312 and wjdi.load_type = wip_job_details.wip_res_instance
313 and wjdi.substitution_type = p_substitution_type
314 and wjdi.operation_seq_num = p_operation_seq_num
315 and wjdi.resource_seq_num = p_resource_seq_num
316 and wjdi.resource_instance_id = p_resource_instance_id
317 and p_resource_id in (select resource_id
318 from bom_resources
319 where resource_type = 1) /* machine type resource */
320 and p_resource_serial_number not in(
321 select bdri.serial_number
322 from bom_resource_equipments breq,
323 bom_dept_res_instances_eqp_v bdri,
324 mtl_system_items_kfv msik,
325 bom_department_resources bdr
326 where bdr.resource_id = p_resource_id
327 and bdr.department_id = p_dept_id
328 and bdri.resource_id = bdr.resource_id
329 and bdri.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
330 and breq.organization_id = p_organization_id
331 and breq.instance_id = bdri.instance_id
332 and breq.resource_id = bdri.resource_id
333 and msik.inventory_item_id = breq.inventory_item_id
334 and msik.organization_id = breq.organization_id);
335
336 l_error_exists boolean := false;
337 l_dept_id number;
338 l_resource_id number;
339 begin
340 select wo.department_id, wor.resource_id
341 into l_dept_id, l_resource_id
342 from wip_operations wo, wip_operation_resources wor
343 where wo.wip_entity_id = p_wip_entity_id
344 and wo.operation_seq_num = p_operation_seq_num
345 and wo.organization_id = p_organization_id
346 and wo.repetitive_schedule_id is null
347 and wor.wip_entity_id = p_wip_entity_id
348 and wor.operation_seq_num = p_operation_seq_num
349 and wor.resource_seq_num = p_resource_seq_num
350 and wor.organization_id = p_organization_id
351 and wor.repetitive_schedule_id is null;
352
353 -- Validate when adding resource instances for machine type resources
354 -- serial numbers to be added must exist in department of the operation
355 -- and in the resource being added to
356 for l_inv_row in c_invalid_rows(l_dept_id, l_resource_id) loop
357 l_error_exists := true;
358 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_INST_SERIAL');
359 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
360 if(wip_job_details.std_alone = 1) then
361 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
362 p_text => substr(fnd_message.get,1,500),
363 p_error_type => wip_jdi_utils.msg_error);
364 else
365 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
366 p_text => substr(fnd_message.get,1,500),
367 p_error_type => wip_jdi_utils.msg_error);
368 end if;
369 end loop;
370 if(l_error_exists) then
371
372 update wip_job_dtls_interface wjdi
373 set process_status = wip_constants.error
374 where wjdi.group_id = p_group_id
375 and wjdi.process_phase = wip_constants.ml_validation
376 and wjdi.process_status in (wip_constants.running,
377 wip_constants.warning)
378 and wjdi.wip_entity_id = p_wip_entity_id
379 and wjdi.organization_id = p_organization_id
380 and wjdi.load_type in (wip_job_details.wip_res_instance, wjdi.load_type)
381 and wjdi.substitution_type = p_substitution_type
382 and wjdi.operation_seq_num = p_operation_seq_num
383 and wjdi.resource_seq_num = p_resource_seq_num
384 and wjdi.resource_instance_id = p_resource_instance_id
385 and l_resource_id in (select resource_id
386 from bom_resources
387 where resource_type = 1) /* machine type resource */
388 and p_resource_serial_number not in(
389 select bdri.serial_number
390 from bom_resource_equipments breq,
391 bom_dept_res_instances_eqp_v bdri,
392 mtl_system_items_kfv msik,
393 bom_department_resources bdr
394 where bdr.resource_id = l_resource_id
395 and bdr.department_id = l_dept_id
396 and bdri.resource_id = bdr.resource_id
397 and bdri.department_id = nvl(bdr.share_from_dept_id, bdr.department_id)
398 and breq.organization_id = p_organization_id
399 and breq.instance_id = bdri.instance_id
400 and breq.resource_id = bdri.resource_id
401 and msik.inventory_item_id = breq.inventory_item_id
402 and msik.organization_id = breq.organization_id);
403
404 end if;
405
406 end valid_serial_number;
407
408 /* check for instances added matches assigned units, it must be either
409 equal to number of resource instances unless if no resource instance is defined
410 */
411 Procedure Match_Assigned_Units(p_group_id number,
412 p_wip_entity_id number,
413 p_organization_id number,
414 p_operation_seq_num number,
415 p_resource_seq_num number) IS
416
417 l_error_exists boolean := false;
418 l_count number;
419 l_assigned_units number;
420
421 cursor c_invalid_rows(p_count number, p_assigned_units number) is
422 select interface_id
423 from wip_job_dtls_interface wjdi
424 where wjdi.group_id = p_group_id
425 and wjdi.process_phase = wip_constants.ml_validation
426 and wjdi.process_status in (wip_constants.running,
427 wip_constants.warning)
428 and wjdi.wip_entity_id = p_wip_entity_id
429 and wjdi.organization_id = p_organization_id
430 and wjdi.load_type = wip_job_details.wip_res_instance
431 and wjdi.substitution_type = wip_job_details.wip_add
432 and wjdi.operation_seq_num = p_operation_seq_num
433 and wjdi.resource_seq_num = p_resource_seq_num
434 and p_count <> p_assigned_units;
435
436 l_ret_status varchar2(10);
437 -- Added for bug fix 5132582
438 l_add_count Number;
439 l_del_count Number;
440 l_loglevel Number := to_number(fnd_log.g_current_runtime_level);
441 l_retStatus Varchar2(1);
442 l_source_code VARCHAR2(30); -- Added for Fix #5752548
443 BEGIN
444 l_count := 0;
445
446 begin
447 select assigned_units into l_assigned_units
448 from wip_operation_resources
449 where wip_entity_id = p_wip_entity_id
450 and organization_id = p_organization_id
451 and operation_seq_num = p_operation_seq_num
452 and resource_seq_num = p_resource_seq_num;
453 exception
454 when no_data_found then
455 l_assigned_units := -1;
456 end;
457
458 -- Fix bug 5472387.
459 -- The logic to check for assigned units is as follows.
460 -- Get the assigned units value from wip_operation_resources. This
461 -- will be the benchmark against which we check for the total resource
462 -- instance number.
463 -- Get currently added resource instances from wip_op_resource_instances table.
464 -- Get the number of resource instances being added in the current run and add it
465 -- to the above number.
466 -- Get the number of resource instances being deleted in the current run and subtract it
467 -- from the above number.
468 -- Now, this number should be equal to the assigned_units value we got from the first step.
469
470 -- Fix bug #5752548:
471 -- Since ASCP will always insert entire instance data, massload program deletes
472 -- all existing resource instances in wip_op_resource_instances before adding
473 -- new instances inserted by ASCP. Hence, the existing instances should not be
474 -- counted when validating assigned units.
475
476 l_source_code := '';
477
478 begin
479 select source_code into l_source_code
480 from wip_job_schedule_interface
481 where wip_entity_id = p_wip_entity_id
482 and organization_id = p_organization_id
483 and group_id = p_group_id;
484 exception
485 when no_data_found then
486 l_source_code := '';
487 end;
488
489 if (l_source_code = 'MSC') then
490 l_count := 0;
491 else
492 Begin
493 select count(*) into l_count
494 from wip_op_resource_instances
495 where wip_entity_id = p_wip_entity_id
496 and organization_id = p_organization_id
497 and operation_seq_num = p_operation_seq_num
498 and resource_seq_num = p_resource_seq_num;
499 Exception
500 when no_data_found then
501 l_count := 0;
502 End;
503 end if;
504
505 -- end of bug fix #5752548
506
507 Begin
508 select count(*)
509 into l_add_count
510 from wip_job_dtls_interface
511 where group_id = p_group_id
512 and wip_entity_id = p_wip_entity_id
513 and organization_id = p_organization_id
514 and load_type = wip_job_details.wip_res_instance
515 and substitution_type = wip_job_details.wip_add
516 and operation_seq_num = p_operation_seq_num
517 and resource_seq_num = p_resource_seq_num;
518 Exception when no_data_found then
519 l_add_count := 0;
520 End;
521
522 Begin
523 select count(*)
524 into l_del_count
525 from wip_job_dtls_interface
526 where group_id = p_group_id
527 and wip_entity_id = p_wip_entity_id
528 and organization_id = p_organization_id
529 and load_type = wip_job_details.wip_res_instance
530 and substitution_type = wip_job_details.wip_delete
531 and operation_seq_num = p_operation_seq_num
532 and resource_seq_num = p_resource_seq_num;
533 Exception when no_data_found then
534 l_del_count := 0;
535 End;
536
537
538 if ( l_logLevel <= wip_constants.full_logging ) then
539 wip_logger.log('Number of current units = ' ||to_char(l_count), l_retStatus);
540 wip_logger.log('Number of units to be added = ' ||to_char(l_add_count), l_retStatus);
541 wip_logger.log('Number of units to be deleted = ' ||to_char(l_del_count), l_retStatus);
542 end if;
543 l_count := l_count + l_add_count - l_del_count;
544
545 -- End of bug fix 5132582
546
547 for l_inv_row in c_invalid_rows(l_count, l_assigned_units) loop
548 l_error_exists := true;
549 fnd_message.set_name('WIP', 'WIP_ASSIGNED_UNITS_ERROR');
550 if(wip_job_details.std_alone = 1) then
551 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
552 p_text => to_char(l_inv_row.interface_id)
553 || ':' || substr(fnd_message.get,1,500),
554 p_error_type => wip_jdi_utils.msg_error);
555 else
556 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
557 p_text => to_char(l_inv_row.interface_id)
558 || ':' || substr(fnd_message.get,1,500),
559 p_error_type => wip_jdi_utils.msg_error);
560 end if;
561 end loop;
562 if(l_error_exists) then
563 update wip_job_dtls_interface wjdi
564 set process_status = wip_constants.error
565 where wjdi.group_id = p_group_id
566 and wjdi.process_phase = wip_constants.ml_validation
567 and wjdi.process_status in (wip_constants.running,
568 wip_constants.warning)
569 and wjdi.wip_entity_id = p_wip_entity_id
570 and wjdi.organization_id = p_organization_id
571 and wjdi.load_type = wip_job_details.wip_res_instance
572 and wjdi.substitution_type = wip_job_details.wip_add
573 and wjdi.operation_seq_num = p_operation_seq_num
574 and wjdi.resource_seq_num = p_resource_seq_num
575 and l_count <> l_assigned_units;
576 end if;
577 END Match_Assigned_Units;
578
579 Procedure Add_Resource_Instance(p_group_id number,
580 p_wip_entity_id number,
581 p_organization_id number,
582 p_substitution_type number,
583 p_err_code out NOCOPY varchar2,
584 p_err_msg out NOCOPY varchar2) IS
585
586 x_err_code varchar2(30) := null;
587 x_err_msg varchar2(240) := NULL;
588
589 CURSOR res_info (p_group_id number,
590 p_wip_entity_id number,
591 p_organization_id number,
592 p_substitution_type number) IS
593 SELECT distinct operation_seq_num,
594 resource_seq_num, resource_id_old, resource_id_new,
595 resource_instance_id, usage_rate_or_amount,
596 last_update_date, last_updated_by, creation_date, created_by,
597 last_update_login, request_id, program_application_id,
598 program_id, program_update_date, resource_serial_number,
599 scheduled_flag, assigned_units, applied_resource_units,
600 applied_resource_value, uom_code, basis_type,
601 activity_id, autocharge_type, standard_rate_flag,
602 start_date, completion_date,attribute_category, attribute1,
603 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
604 attribute8,attribute9,attribute10,attribute11,attribute12,
605 attribute13,attribute14,attribute15, schedule_seq_num,
606 substitute_group_num, replacement_group_num, parent_seq_num, rowid
607 FROM WIP_JOB_DTLS_INTERFACE
608 WHERE group_id = p_group_id
609 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
610 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
611 AND wip_entity_id = p_wip_entity_id
612 AND organization_id = p_organization_id
613 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
614 AND substitution_type = p_substitution_type;
615
616 l_resource_type number;
617 l_return_status varchar2(100);
618
619 BEGIN
620
621 FOR cur_row IN res_info(p_group_id,
622 p_wip_entity_id,
623 p_organization_id,
624 p_substitution_type) LOOP
625
626 WIP_RES_INST_DEFAULT.Default_Res_Instance(p_group_id,
627 p_wip_entity_id,
628 p_organization_id,
629 p_substitution_type,
630 cur_row.operation_seq_num,
631 cur_row.resource_seq_num,
632 cur_row.resource_id_new,
633 cur_row.resource_instance_id,
634 cur_row.parent_seq_num,
635 cur_row.rowid,
636 x_err_code,
637 x_err_msg);
638
639 begin
640 select br.resource_type
641 into l_resource_type
642 from bom_resources br, wip_operation_resources wor
643 where wor.wip_entity_id = p_wip_entity_id
644 and wor.organization_id = p_organization_id
645 and wor.operation_seq_num = cur_row.operation_seq_num
646 and wor.resource_seq_num = cur_row.resource_seq_num
647 and br.resource_id = wor.resource_id;
648 exception
649 when others then
650 l_resource_type := null;
651 end;
652
653
654 ResInst_Info_Exist(p_group_id,
655 p_wip_entity_id,
656 p_organization_id,
657 p_substitution_type,
658 cur_row.operation_seq_num,
659 cur_row.resource_seq_num,
660 cur_row.resource_instance_id,
661 l_resource_type);
662
663
664 IF IS_Error(p_group_id,
665 p_wip_entity_id,
666 p_organization_id,
667 p_substitution_type,
668 cur_row.operation_seq_num,
669 cur_row.resource_seq_num)= 0 THEN
670
671 Valid_Dates(p_group_id,
672 p_wip_entity_id,
673 p_organization_id,
674 p_substitution_type,
675 cur_row.operation_seq_num,
676 cur_row.resource_seq_num,
677 cur_row.resource_instance_id,
678 l_resource_type);
679
680
681 IF IS_Error(p_group_id,
682 p_wip_entity_id,
683 p_organization_id,
684 p_substitution_type,
685 cur_row.operation_seq_num,
686 cur_row.resource_seq_num)= 0 THEN
687
688 Valid_Resource_Instance(p_group_id,
689 p_wip_entity_id,
690 p_organization_id,
691 p_substitution_type,
692 cur_row.operation_seq_num,
693 cur_row.resource_seq_num,
694 cur_row.resource_instance_id);
695
696 IF (IS_Error(p_group_id,
697 p_wip_entity_id,
698 p_organization_id,
699 p_substitution_type,
700 cur_row.operation_seq_num,
701 cur_row.resource_seq_num)= 0
702 AND l_resource_type = 1) THEN
703 /* validate serial number for machine type resource instances */
704 Valid_Serial_number(p_group_id,
705 p_wip_entity_id,
706 p_organization_id,
707 p_substitution_type,
708 cur_row.operation_seq_num,
709 cur_row.resource_seq_num,
710 cur_row.resource_instance_id,
711 cur_row.resource_serial_number);
712
713 End if;
714
715 -- Fixed for bug 5132582
716 -- The match_assigned_units call was inide the if condition of above Valid_Serial_number api
717 -- but Valid_Serial_number is called only for meachine resource type
718 -- We should call match assigned units call for both machine and employee resource
719 -- Hence I have brought this API out of the above if condition
720
721 IF IS_Error(p_group_id,
722 p_wip_entity_id,
723 p_organization_id,
724 p_substitution_type,
725 cur_row.operation_seq_num,
726 cur_row.resource_seq_num)= 0 THEN
727
728 Match_Assigned_Units(p_group_id,
729 p_wip_entity_id,
730 p_organization_id,
731 cur_row.operation_seq_num,
732 cur_row.resource_seq_num);
733
734 End If;
735 End If;
736 End If;
737
738 END LOOP;
739
740 exception
741 when others then
742 p_err_msg := 'wiprivdb, Add_Resource_Instance: ' || SQLERRM;
743 p_err_code := SQLCODE;
744
745 END Add_Resource_Instance;
746
747 /* operations, resources, etc all match and exist */
748 procedure Res_Instance_Job_Match (p_group_id number,
749 p_wip_entity_id number,
750 p_organization_id number,
751 p_substitution_type number,
752 p_operation_seq_num number,
753 p_resource_seq_num number,
754 p_instance_id number) IS
755
756 -- Job/op_seq_num/resource_seq_num/resource_id all match
757 -- Validate only when delete/change resources
758 cursor c_invalid_rows is
759 select interface_id
760 from wip_job_dtls_interface wjdi
761 where wjdi.group_id = p_group_id
762 and wjdi.process_phase = wip_constants.ml_validation
763 and wjdi.process_status in (wip_constants.running,
764 wip_constants.warning)
765 and wjdi.wip_entity_id = p_wip_entity_id
766 and wjdi.organization_id = p_organization_id
767 and wjdi.load_type = wip_job_details.wip_res_instance
768 and wjdi.substitution_type = p_substitution_type
769 and wjdi.operation_seq_num = p_operation_seq_num
770 and wjdi.resource_seq_num = p_resource_seq_num
771 and wjdi.resource_instance_id= p_instance_id
772 and not exists (select 1
773 from wip_op_resource_instances wori
774 where wori.wip_entity_id = wjdi.wip_entity_id
775 and wori.organization_id = wjdi.organization_id
776 and wori.operation_seq_num = wjdi.operation_seq_num
777 and wori.resource_seq_num = wjdi.resource_seq_num
778 and wori.instance_id = wjdi.resource_instance_id
779 );
780
781 l_error_exists boolean := false;
782 begin
783
784 for l_inv_row in c_invalid_rows loop
785 l_error_exists := true;
786 fnd_message.set_name('WIP', 'WIP_JDI_RES_INST_NOT_IN_JOB');
787 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
788 if(wip_job_details.std_alone = 1) then
789 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
790 p_text => substr(fnd_message.get,1,500),
791 p_error_type => wip_jdi_utils.msg_error);
792 else
793 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
794 p_text => substr(fnd_message.get,1,500),
795 p_error_type => wip_jdi_utils.msg_error);
796 end if;
797 end loop;
798
799 if(l_error_exists) then
800 update wip_job_dtls_interface wjdi
801 set process_status = wip_constants.error
802 where group_id = p_group_id
803 and process_phase = wip_constants.ml_validation
804 and process_status in (wip_constants.running,
805 wip_constants.warning)
806 and wip_entity_id = p_wip_entity_id
807 and organization_id = p_organization_id
808 and wjdi.load_type in (wip_job_details.wip_res_instance)
809 and wjdi.substitution_type = p_substitution_type
810 and wjdi.operation_seq_num = p_operation_seq_num
811 and wjdi.resource_seq_num = p_resource_seq_num
812 and wjdi.resource_instance_id= p_instance_id
813 and not exists (select 1
814 from wip_op_resource_instances wori
815 where wori.wip_entity_id = wjdi.wip_entity_id
816 and wori.organization_id = wjdi.organization_id
817 and wori.operation_seq_num = wjdi.operation_seq_num
818 and wori.resource_seq_num = wjdi.resource_seq_num
819 and wori.instance_id = wjdi.resource_instance_id
820 );
821
822 end if;
823 end Res_Instance_Job_Match;
824
825 Procedure Change_Resource_Instance(p_group_id number,
826 p_wip_entity_id number,
827 p_organization_id number,
828 p_substitution_type number,
829 p_err_code out NOCOPY varchar2,
830 p_err_msg out NOCOPY varchar2) IS
831
832 x_err_code varchar2(30) := null;
833 x_err_msg varchar2(240) := NULL;
834
835 CURSOR res_info (p_group_id number,
836 p_wip_entity_id number,
837 p_organization_id number,
838 p_substitution_type number) IS
839 SELECT distinct operation_seq_num,
840 resource_seq_num, resource_id_old, resource_id_new,
841 resource_instance_id, usage_rate_or_amount,
842 last_update_date, last_updated_by, creation_date, created_by,
843 last_update_login, request_id, program_application_id,
844 program_id, program_update_date,
845 scheduled_flag, assigned_units, applied_resource_units,
846 applied_resource_value, uom_code, basis_type,
847 activity_id, autocharge_type, standard_rate_flag,
848 start_date, completion_date,attribute_category, attribute1,
849 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
850 attribute8,attribute9,attribute10,attribute11,attribute12,
851 attribute13,attribute14,attribute15, schedule_seq_num,
852 substitute_group_num, replacement_group_num
853 FROM WIP_JOB_DTLS_INTERFACE
854 WHERE group_id = p_group_id
855 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
856 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
857 AND wip_entity_id = p_wip_entity_id
858 AND organization_id = p_organization_id
859 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
860 AND substitution_type = p_substitution_type;
861
862 l_resource_type number;
863
864 BEGIN
865
866 FOR cur_row IN res_info(p_group_id,
867 p_wip_entity_id,
868 p_organization_id,
869 p_substitution_type) LOOP
870
871 begin
872 select br.resource_type
873 into l_resource_type
874 from bom_resources br, wip_operation_resources wor
875 where wor.wip_entity_id = p_wip_entity_id
876 and wor.organization_id = p_organization_id
877 and wor.operation_seq_num = cur_row.operation_seq_num
878 and wor.resource_seq_num = cur_row.resource_seq_num
879 and br.resource_id = wor.resource_id;
880 exception
881 when others then
882 l_resource_type := null;
883 end;
884
885 ResInst_Info_Exist(p_group_id,
886 p_wip_entity_id,
887 p_organization_id,
888 p_substitution_type,
889 cur_row.operation_seq_num,
890 cur_row.resource_seq_num,
891 cur_row.resource_instance_id,
892 l_resource_type);
893
894 IF IS_Error(p_group_id,
895 p_wip_entity_id,
896 p_organization_id,
897 p_substitution_type,
898 cur_row.operation_seq_num,
899 cur_row.resource_seq_num)= 0 THEN
900
901 Valid_Dates(p_group_id,
902 p_wip_entity_id,
903 p_organization_id,
904 p_substitution_type,
905 cur_row.operation_seq_num,
906 cur_row.resource_seq_num,
907 cur_row.resource_instance_id,
908 l_resource_type);
909
910 IF IS_Error(p_group_id,
911 p_wip_entity_id,
912 p_organization_id,
913 p_substitution_type,
914 cur_row.operation_seq_num,
915 cur_row.resource_seq_num)= 0 THEN
916
917 -- Bug 5454843: when change resource instance,
918 -- do not call Res_Instance_Job_Match()
919 Valid_Resource_Instance(p_group_id,
920 p_wip_entity_id,
921 p_organization_id,
922 p_substitution_type,
923 cur_row.operation_seq_num,
924 cur_row.resource_seq_num,
925 cur_row.resource_instance_id);
926
927 IF IS_Error(p_group_id,
928 p_wip_entity_id,
929 p_organization_id,
930 p_substitution_type,
931 cur_row.operation_seq_num,
932 cur_row.resource_seq_num)= 0 THEN
933
934 update wip_op_resource_instances wori
935 set wori.instance_id = cur_row.resource_instance_id
936 where wori.wip_entity_id = p_wip_entity_id
937 and wori.organization_id = p_organization_id
938 and wori.operation_seq_num = cur_row.operation_seq_num
939 and wori.resource_seq_num = cur_row.resource_seq_num;
940
941 End If;
942
943 End If;
944 End If;
945
946 END LOOP;
947
948 exception
949 when others then
950 p_err_msg := 'wiprivdb, Change_Resource_Instance: ' || SQLERRM;
951 p_err_code := SQLCODE;
952
953 END Change_Resource_Instance;
954
955
956 Procedure Delete_Resource_Instance(p_group_id number,
957 p_wip_entity_id number,
958 p_organization_id number,
959 p_substitution_type number,
960 p_err_code out NOCOPY varchar2,
961 p_err_msg out NOCOPY varchar2) IS
962
963 x_err_code varchar2(30) := null;
964 x_err_msg varchar2(240) := NULL;
965
966 CURSOR res_info (p_group_id number,
967 p_wip_entity_id number,
968 p_organization_id number,
969 p_substitution_type number) IS
970 SELECT distinct operation_seq_num,
971 resource_seq_num, resource_id_old, resource_id_new,
972 resource_instance_id, usage_rate_or_amount,
973 last_update_date, last_updated_by, creation_date, created_by,
974 last_update_login, request_id, program_application_id,
975 program_id, program_update_date,
976 scheduled_flag, assigned_units, applied_resource_units,
977 applied_resource_value, uom_code, basis_type,
978 activity_id, autocharge_type, standard_rate_flag,
979 start_date, completion_date,attribute_category, attribute1,
980 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
981 attribute8,attribute9,attribute10,attribute11,attribute12,
982 attribute13,attribute14,attribute15, schedule_seq_num,
983 substitute_group_num, replacement_group_num
984 FROM WIP_JOB_DTLS_INTERFACE
985 WHERE group_id = p_group_id
986 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
987 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
988 AND wip_entity_id = p_wip_entity_id
989 AND organization_id = p_organization_id
990 AND load_type = WIP_JOB_DETAILS.WIP_RES_INSTANCE
991 AND substitution_type = p_substitution_type;
992
993 BEGIN
994
995 FOR cur_row IN res_info(p_group_id,
996 p_wip_entity_id,
997 p_organization_id,
998 p_substitution_type) LOOP
999
1000 ResInst_Info_Exist(p_group_id,
1001 p_wip_entity_id,
1002 p_organization_id,
1003 p_substitution_type,
1004 cur_row.operation_seq_num,
1005 cur_row.resource_seq_num,
1006 cur_row.resource_instance_id,
1007 null);
1008
1009 IF IS_Error(p_group_id,
1010 p_wip_entity_id,
1011 p_organization_id,
1012 p_substitution_type,
1013 cur_row.operation_seq_num,
1014 cur_row.resource_seq_num)= 0 THEN
1015
1016 Res_Instance_Job_Match(p_group_id,
1017 p_wip_entity_id,
1018 p_organization_id,
1019 p_substitution_type,
1020 cur_row.operation_seq_num,
1021 cur_row.resource_seq_num,
1022 cur_row.resource_instance_id);
1023
1024 End If;
1025
1026 END LOOP;
1027
1028 exception
1029 when others then
1030 p_err_msg := 'wiprivdb, Delete_Resource_Instance: ' || SQLERRM;
1031 p_err_code := SQLCODE;
1032
1033 END Delete_Resource_Instance;
1034
1035 END WIP_RES_INST_VALIDATIONS;