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