[Home] [Help]
PACKAGE BODY: APPS.WIP_RESOURCE_VALIDATIONS
Source
1 PACKAGE BODY WIP_RESOURCE_VALIDATIONS AS
2 /* $Header: wiprsvdb.pls 120.12 2011/10/05 01:03:04 pding ship $ */
3
4 /* forward declarations */
5 Procedure check_res_sched_subgroup (p_group_id in number,
6 p_wip_entity_id in number,
7 p_organization_id in number,
8 p_substitution_type in number,
9 p_operation_seq_num in number,
10 p_resource_seq_num in number,
11 p_schedule_seq_num in number,
12 p_substitute_group_num in number,
13 p_replacement_group_num in number);
14
15 Procedure check_sub_sched_subgroup (p_group_id number,
16 p_wip_entity_id number,
17 p_organization_id number,
18 p_substitution_type number,
19 p_operation_seq_num number,
20 p_resource_seq_num number,
21 p_schedule_seq_num number,
22 p_substitute_group_num number,
23 p_replacement_group_num number);
24
25 /* resource_seq_num, resource_id NOT NULL when delete/change resource */
26 procedure del_res_info_exist(p_group_id in number,
27 p_wip_entity_id in number,
28 p_organization_id in number,
29 p_substitution_type in number,
30 p_operation_seq_num in number) IS
31
32 cursor c_invalid_rows is
33 select interface_id
34 from wip_job_dtls_interface wjdi
35 where wjdi.group_id = p_group_id
36 and wjdi.process_phase = wip_constants.ml_validation
37 and wjdi.process_status in (wip_constants.running,
38 wip_constants.warning)
39 and wjdi.wip_entity_id = p_wip_entity_id
40 and wjdi.organization_id = p_organization_id
41 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
42 and wjdi.substitution_type = p_substitution_type
43 and wjdi.operation_seq_num = p_operation_seq_num
44 and ( wjdi.resource_seq_num is null
45 or wjdi.resource_id_old is null);
46
47 l_error_exists boolean := false;
48 begin
49
50 for l_inv_row in c_invalid_rows loop
51 l_error_exists := true;
52 fnd_message.set_name('WIP', 'WIP_JDI_DEL_RES_INFO_MISSING');
53 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
54 if (wip_job_details.std_alone = 1) then
55 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
56 p_text => substr(fnd_message.get,1,500),
57 p_error_type => wip_jdi_utils.msg_error);
58 else
59 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
60 p_text => substr(fnd_message.get,1,500),
61 p_error_type => wip_jdi_utils.msg_error);
62 end if;
63 end loop;
64
65 if(l_error_exists) then
66 update wip_job_dtls_interface wjdi
67 set process_status = wip_constants.error
68 where group_id = p_group_id
69 and process_phase = wip_constants.ml_validation
70 and process_status in (wip_constants.running,
71 wip_constants.warning)
72 and wip_entity_id = p_wip_entity_id
73 and organization_id = p_organization_id
74 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
75 and wjdi.substitution_type = p_substitution_type
76 and wjdi.operation_seq_num = p_operation_seq_num
77 and ( wjdi.resource_seq_num is null
78 or wjdi.resource_id_old is null);
79 end if;
80 end del_res_info_exist;
81
82
83 /* operations, resources, etc all match and exist */
84 procedure res_job_match (p_group_id number,
85 p_wip_entity_id number,
86 p_organization_id number,
87 p_substitution_type number,
88 p_operation_seq_num number,
89 p_resource_seq_num number,
90 p_resource_id_old number) IS
91
92 -- Job/op_seq_num/resource_seq_num/resource_id_old all match
93 -- Validate only when delete/change resources
94 cursor c_invalid_rows is
95 select interface_id
96 from wip_job_dtls_interface wjdi
97 where wjdi.group_id = p_group_id
98 and wjdi.process_phase = wip_constants.ml_validation
99 and wjdi.process_status in (wip_constants.running,
100 wip_constants.warning)
101 and wjdi.wip_entity_id = p_wip_entity_id
102 and wjdi.organization_id = p_organization_id
103 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
104 and wjdi.substitution_type = p_substitution_type
105 and wjdi.operation_seq_num = p_operation_seq_num
106 and wjdi.resource_seq_num = p_resource_seq_num
107 and wjdi.resource_id_old = p_resource_id_old
108 and not ( (exists (select 1
109 from wip_operation_resources wor
110 where wor.wip_entity_id = wjdi.wip_entity_id
111 and wor.organization_id = wjdi.organization_id
112 and wor.operation_seq_num = wjdi.operation_seq_num
113 and wor.resource_seq_num = wjdi.resource_seq_num
114 and wor.resource_id = wjdi.resource_id_old))
115 or
116 (exists (select 1
117 from wip_sub_operation_resources wsor
118 where wsor.wip_entity_id = wjdi.wip_entity_id
119 and wsor.organization_id = wjdi.organization_id
120 and wsor.operation_seq_num = wjdi.operation_seq_num
121 and wsor.resource_seq_num = wjdi.resource_seq_num
122 and wsor.resource_id = wjdi.resource_id_old))
123 );
124
125 l_error_exists boolean := false;
126 begin
127
128 for l_inv_row in c_invalid_rows loop
129 l_error_exists := true;
130 fnd_message.set_name('WIP', 'WIP_JDI_RES_NOT_IN_JOB');
131 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
132 if(wip_job_details.std_alone = 1) then
133 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
134 p_text => substr(fnd_message.get,1,500),
135 p_error_type => wip_jdi_utils.msg_error);
136 else
137 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
138 p_text => substr(fnd_message.get,1,500),
139 p_error_type => wip_jdi_utils.msg_error);
140 end if;
141 end loop;
142
143 if(l_error_exists) then
144 update wip_job_dtls_interface wjdi
145 set process_status = wip_constants.error
146 where group_id = p_group_id
147 and process_phase = wip_constants.ml_validation
148 and process_status in (wip_constants.running,
149 wip_constants.warning)
150 and wip_entity_id = p_wip_entity_id
151 and organization_id = p_organization_id
152 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
153 and wjdi.substitution_type = p_substitution_type
154 and wjdi.operation_seq_num = p_operation_seq_num
155 and wjdi.resource_seq_num = p_resource_seq_num
156 and wjdi.resource_id_old = p_resource_id_old
157 and not ( (exists (select 1
158 from wip_operation_resources wor
159 where wor.wip_entity_id = wjdi.wip_entity_id
160 and wor.organization_id = wjdi.organization_id
161 and wor.operation_seq_num = wjdi.operation_seq_num
162 and wor.resource_seq_num = wjdi.resource_seq_num
163 and wor.resource_id = wjdi.resource_id_old))
164 or
165 (exists (select 1
166 from wip_sub_operation_resources wsor
167 where wsor.wip_entity_id = wjdi.wip_entity_id
168 and wsor.organization_id = wjdi.organization_id
169 and wsor.operation_seq_num = wjdi.operation_seq_num
170 and wsor.resource_seq_num = wjdi.resource_seq_num
171 and wsor.resource_id = wjdi.resource_id_old))
172 );
173 end if;
174 end res_job_match;
175
176
177 Procedure Safe_Delete (p_group_id number,
178 p_wip_entity_id number,
179 p_organization_id number,
180 p_substitution_type number,
181 p_operation_seq_num number,
182 p_resource_seq_num number,
183 p_resource_id_old number) IS
184
185 cursor c_invalid_wor_rows is
186 select interface_id
187 from wip_job_dtls_interface wjdi, wip_operation_resources wor
188 where wjdi.group_id = p_group_id
189 and wjdi.process_phase = wip_constants.ml_validation
190 and wjdi.process_status in (wip_constants.running,
191 wip_constants.warning)
192 and wjdi.wip_entity_id = p_wip_entity_id
193 and wjdi.organization_id = p_organization_id
194 and wjdi.load_type = wip_job_details.wip_resource
195 and wjdi.substitution_type = p_substitution_type
196 and wjdi.operation_seq_num = p_operation_seq_num
197 and wor.wip_entity_id = p_wip_entity_id
198 and wor.organization_id = p_organization_id
199 and wor.operation_seq_num = p_operation_seq_num
200 and wor.resource_seq_num = p_resource_seq_num
201 and wor.resource_id = p_resource_id_old
202 and wor.applied_resource_units <> 0;
203
204 cursor c_invalid_txn_rows is
205 select interface_id
206 from wip_job_dtls_interface wjdi
207 where wjdi.group_id = p_group_id
208 and wjdi.process_phase = wip_constants.ml_validation
209 and wjdi.process_status in (wip_constants.running,
210 wip_constants.warning)
211 and wjdi.wip_entity_id = p_wip_entity_id
212 and wjdi.organization_id = p_organization_id
213 and wjdi.load_type = wip_job_details.wip_resource
214 and wjdi.substitution_type = p_substitution_type
215 and wjdi.operation_seq_num = p_operation_seq_num
216 and ( exists (select 1
217 from wip_transactions wt
218 where wt.wip_entity_id = p_wip_entity_id
219 and wt.organization_id = p_organization_id
220 and wt.operation_seq_num = p_operation_seq_num
221 and wt.resource_seq_num = p_resource_seq_num
222 and wt.resource_id = p_resource_id_old)
223 or exists (select 1
224 from wip_cost_txn_interface wcti, bom_resources br
225 where wcti.wip_entity_id = p_wip_entity_id
226 and wcti.organization_id = p_organization_id
227 and wcti.operation_seq_num = p_operation_seq_num
228 and wcti.resource_seq_num = p_resource_seq_num
229 and wcti.resource_code = br.resource_code (+)
230 and wcti.organization_id = br.organization_id (+)
231 and ( wcti.resource_id = p_resource_id_old
232 or br.resource_id = p_resource_id_old))
233 );
234 l_error_exists boolean := false;
235 begin
236
237 -- applied_resource_units in WIP_OPERATION_RESOURCES must be 0
238 for l_inv_row in c_invalid_wor_rows loop
239 l_error_exists := true;
240 fnd_message.set_name('WIP', 'WIP_JDI_RES_APPLIED');
241 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
242 if(wip_job_details.std_alone = 1) then
243 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
244 p_text => substr(fnd_message.get,1,500),
245 p_error_type => wip_jdi_utils.msg_error);
246 else
247 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
248 p_text => substr(fnd_message.get,1,500),
249 p_error_type => wip_jdi_utils.msg_error);
250 end if;
251 end loop;
252
253 if(l_error_exists) then
254 update wip_job_dtls_interface wjdi
255 set wjdi.process_status = wip_constants.error
256 where wjdi.group_id = p_group_id
257 and wjdi.process_phase = wip_constants.ml_validation
258 and wjdi.process_status in (wip_constants.running,
259 wip_constants.warning)
260 and wjdi.wip_entity_id = p_wip_entity_id
261 and wjdi.organization_id = p_organization_id
262 and wjdi.load_type = wip_job_details.wip_resource
263 and wjdi.substitution_type = p_substitution_type
264 and wjdi.operation_seq_num = p_operation_seq_num
265 and exists (select 1
266 from wip_operation_resources wor
267 where wor.wip_entity_id = p_wip_entity_id
268 and wor.organization_id = p_organization_id
269 and wor.operation_seq_num = p_operation_seq_num
270 and wor.resource_seq_num = p_resource_seq_num
271 and wor.resource_id = p_resource_id_old
272 and wor.applied_resource_units <> 0);
273 return;
274 end if;
275
276 --now check for [pending] transactions
277 for l_inv_row in c_invalid_txn_rows loop
278 l_error_exists := true;
279 fnd_message.set_name('WIP', 'WIP_JDI_DELETE_RESOURCE');
280 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
281 if(wip_job_details.std_alone = 1) then
282 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
283 p_text => substr(fnd_message.get,1,500),
284 p_error_type => wip_jdi_utils.msg_error);
285 else
286 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
287 p_text => substr(fnd_message.get,1,500),
288 p_error_type => wip_jdi_utils.msg_error);
289 end if;
290 end loop;
291
292 if(l_error_exists) then
293 update wip_job_dtls_interface wjdi
294 set process_status = wip_constants.error
295 where wjdi.group_id = p_group_id
296 and wjdi.process_phase = wip_constants.ml_validation
297 and wjdi.process_status in (wip_constants.running,
298 wip_constants.warning)
299 and wjdi.wip_entity_id = p_wip_entity_id
300 and wjdi.organization_id = p_organization_id
301 and wjdi.load_type = wip_job_details.wip_resource
302 and wjdi.substitution_type = p_substitution_type
303 and wjdi.operation_seq_num = p_operation_seq_num
304 and ( exists (select 1
305 from wip_transactions wt
306 where wt.wip_entity_id = p_wip_entity_id
307 and wt.organization_id = p_organization_id
308 and wt.operation_seq_num = p_operation_seq_num
309 and wt.resource_seq_num = p_resource_seq_num
310 and wt.resource_id = p_resource_id_old)
311 or exists (select 1
312 from wip_cost_txn_interface wcti, bom_resources br
313 where wcti.wip_entity_id = p_wip_entity_id
314 and wcti.organization_id = p_organization_id
315 and wcti.operation_seq_num = p_operation_seq_num
316 and wcti.resource_seq_num = p_resource_seq_num
317 and wcti.resource_code = br.resource_code (+)
318 and wcti.organization_id = br.organization_id (+)
319 and ( wcti.resource_id = p_resource_id_old
320 or br.resource_id = p_resource_id_old))
321 );
322 end if;
323
324 end safe_delete;
325
326 /* outside processing */
327 Procedure Safe_PO (p_group_id number,
328 p_wip_entity_id number,
329 p_organization_id number,
330 p_substitution_type number,
331 p_operation_seq_num number,
332 p_resource_seq_num number,
333 p_resource_id_old number) IS
334
335 /* Bug 5004087 (FP of 4747215): Added WIP_RESOURCE_SEQ_NUM condition when checking
336 for existing PO/REQ. */
337 cursor c_invalid_rows is
338 select interface_id
339 from wip_job_dtls_interface wjdi
340 where wjdi.group_id = p_group_id
341 and wjdi.process_phase = wip_constants.ml_validation
342 and wjdi.process_status in (wip_constants.running,
343 wip_constants.warning)
344 and wjdi.wip_entity_id = p_wip_entity_id
345 and wjdi.organization_id = p_organization_id
346 and wjdi.load_type = wip_job_details.wip_resource
347 and wjdi.substitution_type = p_substitution_type
348 and wjdi.operation_seq_num = p_operation_seq_num
349 and wjdi.resource_seq_num = p_resource_seq_num
350 and wjdi.resource_id_old = p_resource_id_old
351 -- Bug 4321480 - Modified the exists clause to exclude Cancelled PO/POR.
352 and (exists
353 ( SELECT 'PO/REQ Linked'
354 FROM PO_RELEASES_ALL PR,
355 PO_HEADERS_ALL PH,
356 PO_DISTRIBUTIONS_ALL PD,
357 PO_LINE_LOCATIONS_ALL PLL
358 WHERE pd.po_line_id IS NOT NULL
359 AND pd.line_location_id IS NOT NULL
360 AND PD.WIP_ENTITY_ID = p_wip_entity_id
361 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
362 AND (p_operation_seq_num is NULL OR
363 PD.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
364 AND PD.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215)*/
365 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
366 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
367 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
368 -- check cancel flag at shipment level instead of at header level
369 -- because PO will cancel upto shipment level
370 AND (pll.cancel_flag IS NULL OR
371 pll.cancel_flag = 'N')
372 AND (PLL.QUANTITY_RECEIVED <
373 (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
374 AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
375 UNION ALL
376 SELECT 'PO/REQ Linked'
377 FROM PO_REQUISITION_LINES_ALL PRL
378 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
379 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
380 AND (p_operation_seq_num is NULL OR
381 PRL.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
382 AND PRL.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
383 AND nvl(PRL.cancel_flag, 'N') = 'N'
384 AND PRL.LINE_LOCATION_ID is NULL
385 UNION ALL
386 SELECT 'PO/REQ Linked'
387 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
388 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
389 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
390 AND (p_operation_seq_num is NULL OR
391 PRI.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
392 AND PRI.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */));
393
394 -- l_error_exists boolean := false;
395 l_warning_exists boolean := false;
396 l_propagate_job_change_to_po NUMBER;
397 l_return_status VARCHAR2(1);
398 l_error_text VARCHAR2(2000);
399 begin
400 SELECT propagate_job_change_to_po
401 INTO l_propagate_job_change_to_po
402 FROM wip_parameters
403 WHERE organization_id = p_organization_id;
404
405 -- There can't be any outside processing going on with the job
406 for l_inv_row in c_invalid_rows loop
407 IF(po_code_release_grp.Current_Release >=
408 po_code_release_grp.PRC_11i_Family_Pack_J AND
409 l_propagate_job_change_to_po = WIP_CONSTANTS.YES) THEN
410 -- cancel all PO/requisitions associate to this job/operation
411 wip_osp.cancelPOReq(p_job_id => p_wip_entity_id,
412 p_org_id => p_organization_id,
413 p_op_seq_num => p_operation_seq_num,
414 x_return_status => l_return_status);
415
416 IF(l_return_status <> fnd_api. g_ret_sts_success) THEN
417 l_warning_exists := true;
418 -- If we are unable to cancel all PO/requisition associated to this
419 -- job, we will try to cancel as much as we can, then user need to
420 -- manually cancel the rest.
421 wip_utilities.get_message_stack(p_msg =>l_error_text);
422 IF(wip_job_details.std_alone = 1) THEN
423 wip_interface_err_Utils.add_error(
424 p_interface_id => l_inv_row.interface_id,
425 p_text => substrb(l_error_text,1,500),
426 p_error_type => wip_jdi_utils.msg_warning);
427 ELSE
428 wip_interface_err_Utils.add_error(
429 p_interface_id => wip_jsi_utils.current_interface_id,
430 p_text => substrb(l_error_text,1,500),
431 p_error_type => wip_jdi_utils.msg_warning);
432 END IF;
433 END IF; -- check return status
434 ELSE
435 -- propagate_job_change_to_po is manual or customer does not have PO FPJ
436 l_warning_exists := true;
437 fnd_message.set_name('WIP', 'WIP_DELETE_OSP_RESOURCE');
438 l_error_text := fnd_message.get;
439 IF(wip_job_details.std_alone = 1) THEN
440 wip_interface_err_Utils.add_error(
441 p_interface_id => l_inv_row.interface_id,
442 p_text => l_error_text,
443 p_error_type => wip_jdi_utils.msg_warning);
444 ELSE
445 wip_interface_err_Utils.add_error(
446 p_interface_id => wip_jsi_utils.current_interface_id,
447 p_text => l_error_text,
448 p_error_type => wip_jdi_utils.msg_warning);
449 END IF;
450 END IF; -- propagate_job_change_to_po check
451 end loop;
452
453 if(l_warning_exists) then
454 update wip_job_dtls_interface wjdi
455 set process_status = wip_constants.warning
456 where wjdi.group_id = p_group_id
457 and wjdi.process_phase = wip_constants.ml_validation
458 and wjdi.process_status in (wip_constants.running,
459 wip_constants.warning)
460 and wjdi.wip_entity_id = p_wip_entity_id
461 and wjdi.organization_id = p_organization_id
462 and wjdi.load_type = wip_job_details.wip_resource
463 and wjdi.substitution_type = p_substitution_type
464 and wjdi.operation_seq_num = p_operation_seq_num
465 and wjdi.resource_seq_num = p_resource_seq_num
466 and wjdi.resource_id_old = p_resource_id_old
467 -- Bug 4321480 - Modified the exists clause to exclude Cancelled PO/POR.
468 and (exists
469 ( SELECT 'PO/REQ Linked'
470 FROM PO_RELEASES_ALL PR,
471 PO_HEADERS_ALL PH,
472 PO_DISTRIBUTIONS_ALL PD,
473 PO_LINE_LOCATIONS_ALL PLL
474 WHERE pd.po_line_id IS NOT NULL
475 AND pd.line_location_id IS NOT NULL
476 AND PD.WIP_ENTITY_ID = p_wip_entity_id
477 AND PD.DESTINATION_ORGANIZATION_ID = p_organization_id
478 AND (p_operation_seq_num is NULL OR
479 PD.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
480 AND PD.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
481 AND PH.PO_HEADER_ID = PD.PO_HEADER_ID
482 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
483 AND PR.PO_RELEASE_ID (+) = PD.PO_RELEASE_ID
484 -- check cancel flag at shipment level instead of at header level
485 -- because PO will cancel upto shipment level
486 AND (pll.cancel_flag IS NULL OR
487 pll.cancel_flag = 'N')
488 AND (PLL.QUANTITY_RECEIVED <
489 (PLL.QUANTITY-PLL.QUANTITY_CANCELLED))
490 AND nvl(pll.closed_code,'OPEN') <> 'FINALLY CLOSED'
491 UNION ALL
492 SELECT 'PO/REQ Linked'
493 FROM PO_REQUISITION_LINES_ALL PRL
494 WHERE PRL.WIP_ENTITY_ID = p_wip_entity_id
495 AND PRL.DESTINATION_ORGANIZATION_ID = p_organization_id
496 AND (p_operation_seq_num is NULL OR
497 PRL.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
498 AND PRL.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */
499 AND nvl(PRL.cancel_flag, 'N') = 'N'
500 AND PRL.LINE_LOCATION_ID is NULL
501 UNION ALL
502 SELECT 'PO/REQ Linked'
503 FROM PO_REQUISITIONS_INTERFACE_ALL PRI
504 WHERE PRI.WIP_ENTITY_ID = p_wip_entity_id
505 AND PRI.DESTINATION_ORGANIZATION_ID = p_organization_id
506 AND (p_operation_seq_num is NULL OR
507 PRI.WIP_OPERATION_SEQ_NUM = p_operation_seq_num)
508 AND PRI.WIP_RESOURCE_SEQ_NUM = p_resource_seq_num /* Bug 5004087 (FP of 4747215) */));
509
510 end if;
511 end safe_po;
512
513
514 /* main procedure, call the above four */
515 Procedure Delete_Resource (p_group_id number,
516 p_wip_entity_id number,
517 p_organization_id number,
518 p_substitution_type number) IS
519
520 CURSOR res_info (p_group_id number,
521 p_wip_entity_id number,
522 p_organization_id number,
523 p_substitution_type number) IS
524 SELECT distinct operation_seq_num,
525 resource_seq_num, resource_id_old, resource_id_new,
526 usage_rate_or_amount,
527 last_update_date, last_updated_by, creation_date, created_by,
528 last_update_login, request_id, program_application_id,
529 program_id, program_update_date,
530 scheduled_flag, assigned_units, applied_resource_units,
531 applied_resource_value, uom_code, basis_type,
532 activity_id, autocharge_type, standard_rate_flag,
533 start_date, completion_date,attribute_category, attribute1,
534 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
535 attribute8,attribute9,attribute10,attribute11,attribute12,
536 attribute13,attribute14,attribute15
537 FROM WIP_JOB_DTLS_INTERFACE
538 WHERE group_id = p_group_id
539 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
540 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
541 AND wip_entity_id = p_wip_entity_id
542 AND organization_id = p_organization_id
543 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
544 AND substitution_type = p_substitution_type;
545
546 BEGIN
547 FOR cur_row IN res_info(p_group_id,
548 p_wip_entity_id,
549 p_organization_id,
550 p_substitution_type) LOOP
551
552 Del_Res_Info_Exist(p_group_id,
553 p_wip_entity_id,
554 p_organization_id,
555 p_substitution_type,
556 cur_row.operation_seq_num);
557
558 IF Info_Missing(p_group_id,
559 p_wip_entity_id,
560 p_organization_id,
561 p_substitution_type,
562 cur_row.operation_seq_num) = 0 THEN
563
564 RES_JOB_Match (p_group_id,
565 p_wip_entity_id,
566 p_organization_id,
567 p_substitution_type,
568 cur_row.operation_seq_num,
569 cur_row.resource_seq_num,
570 cur_row.resource_id_old);
571
572 IF IS_Error(p_group_id,
573 p_wip_entity_id,
574 p_organization_id,
575 p_substitution_type,
576 cur_row.operation_seq_num,
577 cur_row.resource_seq_num) = 0 THEN
578
579 Safe_Delete (p_group_id,
580 p_wip_entity_id,
581 p_organization_id,
582 p_substitution_type,
583 cur_row.operation_seq_num,
584 cur_row.resource_seq_num,
585 cur_row.resource_id_old);
586
587 IF IS_Error(p_group_id,
588 p_wip_entity_id,
589 p_organization_id,
590 p_substitution_type,
591 cur_row.operation_seq_num,
592 cur_row.resource_seq_num) = 0 THEN
593
594 Safe_PO (p_group_id,
595 p_wip_entity_id,
596 p_organization_id,
597 p_substitution_type,
598 cur_row.operation_seq_num,
599 cur_row.resource_seq_num,
600 cur_row.resource_id_old);
601 END IF;
602 END IF;
603 END IF;
604 END LOOP;
605 END Delete_Resource;
606
607
608 /* resource_seq_num, resource_id, usage_rate_or_amount must not be null
609 when add resource */
610 Procedure Add_Res_Info_Exist(p_group_id number,
611 p_wip_entity_id number,
612 p_organization_id number,
613 p_substitution_type number,
614 p_operation_seq_num number) IS
615
616 cursor c_invalid_rows is
617 select interface_id
618 from wip_job_dtls_interface wjdi
619 where wjdi.group_id = p_group_id
620 and wjdi.process_phase = wip_constants.ml_validation
621 and wjdi.process_status in (wip_constants.running,
622 wip_constants.warning)
623 and wjdi.wip_entity_id = p_wip_entity_id
624 and wjdi.organization_id = p_organization_id
625 and wjdi.load_type = wip_job_details.wip_resource
626 and wjdi.substitution_type = p_substitution_type
627 and wjdi.operation_seq_num = p_operation_seq_num
628 and (wjdi.usage_rate_or_amount is null
629 or wjdi.resource_id_new is null);
630
631 l_error_exists boolean := false;
632 begin
633
634 for l_inv_row in c_invalid_rows loop
635 l_error_exists := true;
636 fnd_message.set_name('WIP', 'WIP_JDI_ADD_RES_INFO_MISSING');
637 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
638 if(wip_job_details.std_alone = 1) then
639 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
640 p_text => substr(fnd_message.get,1,500),
641 p_error_type => wip_jdi_utils.msg_error);
642 else
643 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
644 p_text => substr(fnd_message.get,1,500),
645 p_error_type => wip_jdi_utils.msg_error);
646 end if;
647 end loop;
648
649 if(l_error_exists) then
650 update wip_job_dtls_interface wjdi
651 set process_status = wip_constants.error
652 where wjdi.group_id = p_group_id
653 and wjdi.process_phase = wip_constants.ml_validation
654 and wjdi.process_status in (wip_constants.running,
655 wip_constants.warning)
656 and wjdi.wip_entity_id = p_wip_entity_id
657 and wjdi.organization_id = p_organization_id
658 and wjdi.load_type = wip_job_details.wip_resource
659 and wjdi.substitution_type = p_substitution_type
660 and wjdi.operation_seq_num = p_operation_seq_num
661 and (wjdi.usage_rate_or_amount is null
662 or wjdi.resource_id_new is null);
663 end if;
664 end add_res_info_exist;
665
666 Procedure val_add_res_dates(p_group_id number,
667 p_wip_entity_id number,
668 p_organization_id number,
669 p_substitution_type number,
670 p_operation_seq_num number,
671 p_resource_seq_num number,
672 p_resource_id_new number ) IS
673
674
675 cursor c_invalid_rows is
676 select interface_id
677 from wip_job_dtls_interface wjdi
678 where wjdi.group_id = p_group_id
679 and wjdi.process_phase = wip_constants.ml_validation
680 and wjdi.process_status in (wip_constants.running,
681 wip_constants.warning)
682 and wjdi.wip_entity_id = p_wip_entity_id
683 and wjdi.organization_id = p_organization_id
684 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
685 and wjdi.substitution_type = p_substitution_type
686 and wjdi.operation_seq_num = p_operation_seq_num
687 and wjdi.resource_seq_num = p_resource_seq_num
688 and wjdi.resource_id_new = p_resource_id_new
689 and nvl(wjdi.start_date, sysdate) > nvl(wjdi.completion_date, sysdate);
690
691 l_error_exists boolean := false;
692 begin
693
694 -- Validate when adding/updating resources
695 -- resource dates must be valid
696 for l_inv_row in c_invalid_rows loop
697 l_error_exists := true;
698 fnd_message.set_name('WIP', 'WIP_INVALID_RESOURCE_DATES');
699 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
700 if(wip_job_details.std_alone = 1) then
701 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
702 p_text => substr(fnd_message.get,1,500),
703 p_error_type => wip_jdi_utils.msg_error);
704 else
705 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
706 p_text => substr(fnd_message.get,1,500),
707 p_error_type => wip_jdi_utils.msg_error);
708 end if;
709 end loop;
710
711 if(l_error_exists) then
712 update wip_job_dtls_interface wjdi
713 set process_status = wip_constants.error
714 where wjdi.group_id = p_group_id
715 and wjdi.process_phase = wip_constants.ml_validation
716 and wjdi.process_status in (wip_constants.running,
717 wip_constants.warning)
718 and wjdi.wip_entity_id = p_wip_entity_id
719 and wjdi.organization_id = p_organization_id
720 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
721 and wjdi.substitution_type = p_substitution_type
722 and wjdi.operation_seq_num = p_operation_seq_num
723 and wjdi.resource_seq_num = p_resource_seq_num
724 and wjdi.resource_id_new = p_resource_id_new
725 and nvl(wjdi.start_date, sysdate) > nvl(wjdi.completion_date, sysdate);
726 end if;
727
728 end val_add_res_dates;
729
730 Procedure val_change_res_dates(p_group_id number,
731 p_wip_entity_id number,
732 p_organization_id number,
733 p_substitution_type number,
734 p_operation_seq_num number,
735 p_resource_seq_num number,
736 p_resource_id_old number ) IS
737
738
739 cursor c_invalid_rows is
740 select interface_id
741 from wip_job_dtls_interface wjdi
742 where wjdi.group_id = p_group_id
743 and wjdi.process_phase = wip_constants.ml_validation
744 and wjdi.process_status in (wip_constants.running,
745 wip_constants.warning)
746 and wjdi.wip_entity_id = p_wip_entity_id
747 and wjdi.organization_id = p_organization_id
748 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
749 and wjdi.substitution_type = p_substitution_type
750 and wjdi.operation_seq_num = p_operation_seq_num
751 and wjdi.resource_seq_num = p_resource_seq_num
752 and wjdi.resource_id_old = p_resource_id_old
753 and (wjdi.start_date is not null
754 or wjdi.completion_date is not null)
755 and exists
756 (select 1
757 from wip_operation_resources wor
758 where wor.wip_entity_id = wjdi.wip_entity_id
759 and wor.organization_id = wjdi.organization_id
760 and wor.operation_seq_num = wjdi.operation_seq_num
761 and wor.resource_seq_num = wjdi.resource_seq_num
762 and wor.resource_id = wjdi.resource_id_old
763 and nvl(wjdi.start_date, wor.start_date) > nvl(wjdi.completion_date, wor.completion_date));
764
765 l_error_exists boolean := false;
766 begin
767
768 -- Validate when adding/updating resources
769 -- resource dates must be valid
770 for l_inv_row in c_invalid_rows loop
771 l_error_exists := true;
772 fnd_message.set_name('WIP', 'WIP_INVALID_RESOURCE_DATES');
773 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
774 if(wip_job_details.std_alone = 1) then
775 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
776 p_text => substr(fnd_message.get,1,500),
777 p_error_type => wip_jdi_utils.msg_error);
778 else
779 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
780 p_text => substr(fnd_message.get,1,500),
781 p_error_type => wip_jdi_utils.msg_error);
782 end if;
783 end loop;
784
785 if(l_error_exists) then
786 update wip_job_dtls_interface wjdi
787 set process_status = wip_constants.error
788 where wjdi.group_id = p_group_id
789 and wjdi.process_phase = wip_constants.ml_validation
790 and wjdi.process_status in (wip_constants.running,
791 wip_constants.warning)
792 and wjdi.wip_entity_id = p_wip_entity_id
793 and wjdi.organization_id = p_organization_id
794 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
795 and wjdi.substitution_type = p_substitution_type
796 and wjdi.operation_seq_num = p_operation_seq_num
797 and wjdi.resource_seq_num = p_resource_seq_num
798 and wjdi.resource_id_old = p_resource_id_old
799 and (wjdi.start_date is not null
800 or wjdi.completion_date is not null)
801 and exists
802 (select 1
803 from wip_operation_resources wor
804 where wor.wip_entity_id = wjdi.wip_entity_id
805 and wor.organization_id = wjdi.organization_id
806 and wor.operation_seq_num = wjdi.operation_seq_num
807 and wor.resource_seq_num = wjdi.resource_seq_num
808 and wor.resource_id = wjdi.resource_id_old
809 and nvl(wjdi.start_date, wor.start_date) > nvl(wjdi.completion_date, wor.completion_date));
810
811 end if;
812
813 end val_change_res_dates;
814
815 Procedure Valid_Resource(p_group_id number,
816 p_wip_entity_id number,
817 p_organization_id number,
818 p_substitution_type number,
819 p_operation_seq_num number,
820 p_resource_seq_num number,
821 p_resource_id_new number ) IS
822
823
824 cursor c_invalid_rows is
825 select interface_id
826 from wip_job_dtls_interface wjdi, wip_operations wo
827 where wjdi.group_id = p_group_id
828 and wjdi.process_phase = wip_constants.ml_validation
829 and wjdi.process_status in (wip_constants.running,
830 wip_constants.warning)
831 and wjdi.wip_entity_id = p_wip_entity_id
832 and wjdi.organization_id = p_organization_id
833 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
834 and wjdi.substitution_type = p_substitution_type
835 and wjdi.operation_seq_num = p_operation_seq_num
836 and wjdi.resource_seq_num = p_resource_seq_num
837 and wjdi.resource_id_new = p_resource_id_new
838 and wo.wip_entity_id = p_wip_entity_id
839 and wo.operation_seq_num = p_operation_seq_num
840 and wo.organization_id = p_organization_id
841 and wo.repetitive_schedule_id is null
842 and not ( wjdi.load_type = wip_job_details.wip_resource
843 and wjdi.substitution_type = wip_job_details.wip_change
844 and wjdi.substitute_group_num is not null
845 -- Bug 12937792. Replacement Group can be Null.
846 --and wjdi.replacement_group_num is not null
847 )
848 and ( not exists(select 1
849 from bom_resources br
850 where br.resource_id = p_resource_id_new
851 and ( br.disable_date > sysdate
852 or br.disable_date is null)
853 and br.organization_id = p_organization_id)
854 or not exists(select 1
855 from bom_department_resources bdr
856 where bdr.resource_id = p_resource_id_new
857 and bdr.department_id = wo.department_id)
858 );
859
860 l_error_exists boolean := false;
861 begin
862
863 -- Validate when adding resources
864 -- resources to be added must exist in BOM_RESOURCES, not disabled
865 for l_inv_row in c_invalid_rows loop
866 l_error_exists := true;
867 fnd_message.set_name('WIP', 'WIP_INVALID_RESOURCE');
868 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
869 if(wip_job_details.std_alone = 1) then
870 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
871 p_text => substr(fnd_message.get,1,500),
872 p_error_type => wip_jdi_utils.msg_error);
873 else
874 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
875 p_text => substr(fnd_message.get,1,500),
876 p_error_type => wip_jdi_utils.msg_error);
877 end if;
878 end loop;
879
880 if(l_error_exists) then
881 update wip_job_dtls_interface wjdi
882 set process_status = wip_constants.error
883 where wjdi.group_id = p_group_id
884 and wjdi.process_phase = wip_constants.ml_validation
885 and wjdi.process_status in (wip_constants.running,
886 wip_constants.warning)
887 and wjdi.wip_entity_id = p_wip_entity_id
888 and wjdi.organization_id = p_organization_id
889 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
890 and wjdi.substitution_type = p_substitution_type
891 and wjdi.operation_seq_num = p_operation_seq_num
892 and wjdi.resource_seq_num = p_resource_seq_num
893 and wjdi.resource_id_new = p_resource_id_new
894 and not ( wjdi.load_type = wip_job_details.wip_resource
895 and wjdi.substitution_type = wip_job_details.wip_change
896 and wjdi.substitute_group_num is not null
897 -- Bug 12937792. Replacement Group can be Null.
898 --and wjdi.replacement_group_num is not null
899 )
900 and ( not exists(select 1
901 from bom_resources br
902 where br.resource_id = p_resource_id_new
903 and ( br.disable_date > sysdate
904 or br.disable_date is null)
905 and br.organization_id = p_organization_id)
906 or not exists(select 1
907 from bom_department_resources bdr, wip_operations wo
908 where bdr.resource_id = p_resource_id_new
909 and wo.wip_entity_id = p_wip_entity_id
910 and wo.operation_seq_num = p_operation_seq_num
911 and wo.organization_id = p_organization_id
912 and wo.repetitive_schedule_id is null
913 and bdr.department_id = wo.department_id)
914 );
915 end if;
916 end valid_resource;
917
918
919
920 Procedure Resource_Seq_Num(p_group_id number,
921 p_wip_entity_id number,
922 p_organization_id number,
923 p_substitution_type number,
924 p_operation_seq_num number,
925 p_resource_seq_num number ) IS
926
927 cursor c_invalid_rows is
928 select wjdi.interface_id
929 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
930 where wjdi.group_id = p_group_id
931 and wjdi.process_phase = wip_constants.ml_validation
932 and wjdi.process_status in (wip_constants.running,
933 wip_constants.warning)
934 and wjdi.wip_entity_id = p_wip_entity_id
935 and wjdi.organization_id = p_organization_id
936 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
937 and wjdi.substitution_type = p_substitution_type
938 and wjdi.operation_seq_num = p_operation_seq_num
939 and wjdi.resource_seq_num = p_resource_seq_num
940 and ( resource_seq_num <= 0
941 or exists (select 1
942 from wip_operation_resources wor
943 where wor.wip_entity_id = wjdi.wip_entity_id
944 and wor.organization_id = wjdi.organization_id
945 and wor.operation_seq_num = wjdi.operation_seq_num
946 and wor.resource_seq_num = wjdi.resource_seq_num)
947 or exists (select 1
948 from wip_sub_operation_resources wsor
949 where wsor.wip_entity_id = wjdi.wip_entity_id
950 and wsor.organization_id = wjdi.organization_id
951 and wsor.operation_seq_num = wjdi.operation_seq_num
952 and wsor.resource_seq_num = wjdi.resource_seq_num)
953 )
954 -- Bug#5752548 skip the Resource_Seq_Num validation for setup resources
955 -- inserted by ASCP because all existing setup resources would be deleted
956 -- before adding new setup resource.
957 and wjsi.organization_id = wjdi.organization_id
958 and wjsi.group_id = wjdi.group_id
959 and wjsi.header_id = wjdi.parent_header_id
960 and (wjsi.source_code <> 'MSC' or wjdi.parent_seq_num is null);
961
962 l_error_exists boolean := false;
963 begin
964
965 -- Validate when adding resources
966 -- resource_seq_num must not exist
967 for l_inv_row in c_invalid_rows loop
968 l_error_exists := true;
969 fnd_message.set_name('WIP', 'WIP_JDI_RES_SEQ_NUM_EXIST');
970 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
971 if(wip_job_details.std_alone = 1) then
972 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
973 p_text => substr(fnd_message.get,1,500),
974 p_error_type => wip_jdi_utils.msg_error);
975 else
976 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
977 p_text => substr(fnd_message.get,1,500),
978 p_error_type => wip_jdi_utils.msg_error);
979 end if;
980 end loop;
981
982 if(l_error_exists) then
983 update wip_job_dtls_interface wjdi
984 set process_status = wip_constants.error
985 where wjdi.group_id = p_group_id
986 and wjdi.process_phase = wip_constants.ml_validation
987 and wjdi.process_status in (wip_constants.running,
988 wip_constants.warning)
989 and wjdi.wip_entity_id = p_wip_entity_id
990 and wjdi.organization_id = p_organization_id
991 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
992 and wjdi.substitution_type = p_substitution_type
993 and wjdi.operation_seq_num = p_operation_seq_num
994 and wjdi.resource_seq_num = p_resource_seq_num
995 and ( resource_seq_num <= 0
996 or exists (select 1
997 from wip_operation_resources wor
998 where wor.wip_entity_id = wjdi.wip_entity_id
999 and wor.organization_id = wjdi.organization_id
1000 and wor.operation_seq_num = wjdi.operation_seq_num
1001 and wor.resource_seq_num = wjdi.resource_seq_num)
1002 );
1003 end if;
1004 end resource_seq_num;
1005
1006
1007
1008 Procedure Usage_Rate_Or_Amount(p_group_id number,
1009 p_wip_entity_id number,
1010 p_organization_id number,
1011 p_substitution_type number,
1012 p_operation_seq_num number,
1013 p_resource_seq_num number,
1014 p_resource_id_new number,
1015 p_usage_rate_or_amount number) IS
1016
1017 --the logic for invalid rows is actually in pl/sql for this procedure. This cursor
1018 --just selects all the rows that meet the parameter criteria.
1019 cursor c_invalid_rows is
1020 select interface_id, wjdi.usage_rate_or_amount
1021 from wip_job_dtls_interface wjdi
1022 where wjdi.group_id = p_group_id
1023 and wjdi.process_phase = wip_constants.ml_validation
1024 and wjdi.process_status in (wip_constants.running,
1025 wip_constants.warning)
1026 and wjdi.wip_entity_id = p_wip_entity_id
1027 and wjdi.organization_id = p_organization_id
1028 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1029 and wjdi.substitution_type = p_substitution_type
1030 and wjdi.operation_seq_num = p_operation_seq_num
1031 and wjdi.resource_seq_num = p_resource_seq_num
1032 and wjdi.resource_id_new = p_resource_id_new
1033 -- jy: no need to do this validation if doing res substitution
1034 and not ( wjdi.load_type = wip_job_details.wip_resource
1035 and wjdi.substitution_type = wip_job_details.wip_change
1036 and wjdi.substitute_group_num is not null
1037 -- Bug 12937792. Replacement Group can be Null.
1038 -- and wjdi.replacement_group_num is not null
1039 );
1040
1041 l_error_exists boolean := false;
1042 l_hour_uom varchar2(50);
1043 l_hour_uom_class varchar2(200);
1044 l_uom_time_class_flag boolean;
1045 l_uom_class varchar2(10);
1046 l_Autocharge_Type number(38);
1047 begin
1048 l_hour_uom := FND_PROFILE.value('BOM:HOUR_UOM_CODE');
1049 l_hour_uom_class := wip_op_resources_utilities.get_uom_class(l_hour_uom);
1050
1051 select uom.uom_class
1052 into l_uom_class
1053 from bom_resources br, mtl_units_of_measure_vl uom
1054 where br.resource_id = p_resource_id_new
1055 and br.unit_of_measure = uom.uom_code;
1056
1057 if l_hour_uom_class = l_uom_class then
1058 l_uom_time_class_flag := true;
1059 else
1060 l_uom_time_class_flag := false;
1061 end if;
1062
1063 select autocharge_type
1064 into l_autocharge_type
1065 from bom_resources
1066 where resource_id = p_resource_id_new;
1067
1068 if(l_autocharge_type is null) then
1069
1070 for l_inv_row in c_invalid_rows loop
1071 l_error_exists := true;
1072 fnd_message.set_name('WIP', 'WIP_JDI_NULL_CHARGE_TYPE');
1073 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1074 if(wip_job_details.std_alone = 1) then
1075 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1076 p_text => substr(fnd_message.get,1,500),
1077 p_error_type => wip_jdi_utils.msg_error);
1078 else
1079 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1080 p_text => substr(fnd_message.get,1,500),
1081 p_error_type => wip_jdi_utils.msg_error);
1082 end if;
1083 end loop;
1084
1085 if(l_error_exists) then
1086 update wip_job_dtls_interface wjdi
1087 set process_status = wip_constants.error
1088 where wjdi.group_id = p_group_id
1089 and wjdi.process_phase = wip_constants.ml_validation
1090 and wjdi.process_status in (wip_constants.running,
1091 wip_constants.warning)
1092 and wjdi.wip_entity_id = p_wip_entity_id
1093 and wjdi.organization_id = p_organization_id
1094 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1095 and wjdi.substitution_type = p_substitution_type
1096 and wjdi.operation_seq_num = p_operation_seq_num
1097 and wjdi.resource_seq_num = p_resource_seq_num
1098 and wjdi.resource_id_new = p_resource_id_new
1099 and not ( wjdi.load_type = wip_job_details.wip_resource
1100 and wjdi.substitution_type = wip_job_details.wip_change
1101 and wjdi.substitute_group_num is not null
1102 -- Bug 12937792. Replacement Group can be Null.
1103 --and wjdi.replacement_group_num is not null
1104 );
1105
1106 return;
1107 end if;
1108 elsif (p_usage_rate_or_amount < 0 and
1109 (l_autocharge_type in (3,4) or l_uom_time_class_flag)) then
1110 for l_inv_row in c_invalid_rows loop
1111 if(l_inv_row.usage_rate_or_amount = p_usage_rate_or_amount) then
1112 l_error_exists := true;
1113 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_RATE');
1114 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1115 if(wip_job_details.std_alone = 1) then
1116 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1117 p_text => substr(fnd_message.get,1,500),
1118 p_error_type => wip_jdi_utils.msg_error);
1119 else
1120 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1121 p_text => substr(fnd_message.get,1,500),
1122 p_error_type => wip_jdi_utils.msg_error);
1123 end if;
1124 end if;
1125 end loop;
1126
1127 if(l_error_exists) then
1128 update wip_job_dtls_interface wjdi
1129 set process_status = wip_constants.error
1130 where wjdi.group_id = p_group_id
1131 and wjdi.process_phase = wip_constants.ml_validation
1132 and wjdi.process_status in (wip_constants.running,
1133 wip_constants.warning)
1134 and wjdi.wip_entity_id = p_wip_entity_id
1135 and wjdi.organization_id = p_organization_id
1136 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1137 and wjdi.substitution_type = p_substitution_type
1138 and wjdi.operation_seq_num = p_operation_seq_num
1139 and wjdi.resource_seq_num = p_resource_seq_num
1140 and wjdi.resource_id_new = p_resource_id_new
1141 and wjdi.usage_rate_or_amount = p_usage_rate_or_amount
1142 and not ( wjdi.load_type = wip_job_details.wip_resource
1143 and wjdi.substitution_type = wip_job_details.wip_change
1144 and wjdi.substitute_group_num is not null
1145 -- Bug 12937792. Replacement Group can be Null.
1146 -- and wjdi.replacement_group_num is not null
1147 );
1148 end if;
1149 end if;
1150 end usage_rate_or_amount;
1151
1152 Procedure Assigned_Units(p_group_id number,
1153 p_wip_entity_id number,
1154 p_organization_id number,
1155 p_load_type number,
1156 p_substitution_type number,
1157 p_operation_seq_num number,
1158 p_resource_seq_num number) is
1159
1160 l_error_exists boolean := false;
1161 l_maximum_assigned_units number;
1162 l_dummy2 VARCHAR2(1);
1163 l_params wip_logger.param_tbl_t;
1164 l_check NUMBER:=0;
1165
1166 cursor c_invalid_rows is
1167 select interface_id
1168 from wip_job_dtls_interface wjdi
1169 where wjdi.group_id = p_group_id
1170 and wjdi.process_phase = wip_constants.ml_validation
1171 and wjdi.process_status in (
1172 wip_constants.running,
1173 wip_constants.warning)
1174 and wjdi.wip_entity_id = p_wip_entity_id
1175 and wjdi.organization_id = p_organization_id
1176 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1177 and wjdi.substitution_type = p_substitution_type
1178 and wjdi.operation_seq_num = p_operation_seq_num
1179 and wjdi.resource_seq_num = p_resource_seq_num
1180 and (wjdi.assigned_units <= 0
1181 or (wjdi.assigned_units is null
1182 and wjdi.substitution_type = wip_job_details.wip_add)
1183 or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
1184 and (wjdi.setup_id is not null or exists
1185 (select 1
1186 from bom_resources br
1187 where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1188 and br.batchable = 1
1189 ))
1190 )
1191 -- Bug 5172555
1192 -- The maximum_assigned_units should not be validated
1193 -- So commented out the clause
1194 -- ntungare Thu May 11 05:59:01 PDT 2006
1195 --
1196 -- or (wjdi.assigned_units >
1197 -- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
1198 );
1199
1200 begin
1201
1202 /* BEGIN commented for Bug 11844798. Since l_maximum_assigned_units is not used.
1203 if (p_substitution_type = wip_job_details.wip_change) then
1204 if (p_load_type = WIP_JOB_DETAILS.WIP_RESOURCE) then
1205 BEGIN
1206 select maximum_assigned_units
1207 into l_maximum_assigned_units
1208 from wip_operation_resources
1209 where wip_entity_id = p_wip_entity_id
1210 and organization_id = p_organization_id
1211 and operation_seq_num = p_operation_seq_num
1212 and resource_seq_num = p_resource_seq_num;
1213 EXCEPTION
1214 WHEN NO_DATA_FOUND THEN
1215 select maximum_assigned_units
1216 into l_maximum_assigned_units
1217 from wip_sub_operation_resources
1218 where wip_entity_id = p_wip_entity_id
1219 and organization_id = p_organization_id
1220 and operation_seq_num = p_operation_seq_num
1221 and resource_seq_num = p_resource_seq_num;
1222 END;
1223
1224 elsif (p_load_type = WIP_JOB_DETAILS.WIP_SUB_RES) then
1225 select maximum_assigned_units
1226 into l_maximum_assigned_units
1227 from wip_sub_operation_resources
1228 where wip_entity_id = p_wip_entity_id
1229 and organization_id = p_organization_id
1230 and operation_seq_num = p_operation_seq_num
1231 and resource_seq_num = p_resource_seq_num;
1232 end if;
1233 end if;
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 wip_logger.log('Error in the call to WOR',l_dummy2);
1237 for wor_rec in (select * from wip_operation_resources where wip_entity_id = p_wip_entity_id
1238 and organization_id = p_organization_id order by operation_seq_num,resource_seq_num) loop
1239 wip_logger.log('Operation Seq_num: '||wor_rec.operation_seq_num||',resource_seq_num='||wor_rec.resource_seq_num||',maximum_assigned_units= '||wor_rec.maximum_assigned_units,l_dummy2);
1240 end loop;
1241 raise;
1242 END;
1243 */
1244
1245 for l_inv_row in c_invalid_rows loop
1246 l_error_exists := true;
1247 fnd_message.set_name('WIP', 'WIP_INV_ASSIGNED_UNITS');
1248 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1249 if(wip_job_details.std_alone = 1) then
1250 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1251 p_text => substr(fnd_message.get,1,500),
1252 p_error_type => wip_jdi_utils.msg_error);
1253 else
1254 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1255 p_text => substr(fnd_message.get,1,500),
1256 p_error_type => wip_jdi_utils.msg_error);
1257 end if;
1258 end loop;
1259
1260 if(l_error_exists) then
1261 update wip_job_dtls_interface wjdi
1262 set wjdi.process_status = wip_constants.error
1263 where wjdi.group_id = p_group_id
1264 and wjdi.process_phase = wip_constants.ml_validation
1265 and wjdi.process_status in (wip_constants.running,
1266 wip_constants.warning)
1267 and wjdi.wip_entity_id = p_wip_entity_id
1268 and wjdi.organization_id = p_organization_id
1269 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1270 and wjdi.substitution_type = p_substitution_type
1271 and (wjdi.assigned_units <= 0
1272 or (wjdi.assigned_units is null
1273 and wjdi.substitution_type = wip_job_details.wip_add)
1274 or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
1275 and (wjdi.setup_id is not null or exists
1276 (select 1
1277 from bom_resources br
1278 where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1279 ))
1280 )
1281 -- Bug 5172555
1282 -- The maximum_assigned_units should not be validated
1283 -- So commented out the clause
1284 -- ntungare Thu May 11 05:59:01 PDT 2006
1285 --
1286 -- or (wjdi.assigned_units >
1287 -- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
1288 );
1289 end if;
1290
1291 end Assigned_Units;
1292
1293 Procedure Derive_Resource( p_group_id number,
1294 p_wip_entity_id number,
1295 p_organization_id number,
1296 p_substitution_type number,
1297 p_operation_seq_num number,
1298 p_resource_seq_num in out nocopy number,
1299 p_schedule_seq_num in number,
1300 p_parent_seq_num in number,
1301 p_rowid rowid,
1302 p_err_code out nocopy varchar2,
1303 p_err_msg out nocopy varchar2) is
1304 x_setup_id number := null;
1305 x_res_seq_num_max number := null;
1306 x_res_seq_num number := null;
1307 x_schedule_seq_num number;
1308 begin
1309
1310 if (p_substitution_type = wip_job_details.WIP_ADD) then
1311 -- default res_seq_num to be max existing res_seq_num + 10
1312 if (p_resource_seq_num is null) then
1313 begin
1314 select nvl(max(resource_seq_num), 0)
1315 into x_res_seq_num_max
1316 from wip_operation_resources
1317 where wip_entity_id = p_wip_entity_id
1318 AND organization_id = p_organization_id
1319 AND operation_seq_num = p_operation_seq_num;
1320 exception
1321 when no_data_found then
1322 x_res_seq_num_max := 0;
1323 end;
1324
1325 begin
1326 select nvl(max(resource_seq_num), 0)
1327 into x_res_seq_num
1328 from WIP_JOB_DTLS_INTERFACE
1329 where group_id = p_group_id
1330 and wip_entity_id = p_wip_entity_id
1331 and organization_id = p_organization_id
1332 and operation_seq_num = p_operation_seq_num
1333 and substitution_type = p_substitution_type;
1334 exception
1335 when no_data_found then
1336 x_res_seq_num := 0;
1337 end;
1338
1339 if x_res_seq_num_max < x_res_seq_num then
1340 x_res_seq_num_max := x_res_seq_num;
1341 end if;
1342
1343 x_res_seq_num_max := x_res_seq_num_max + 10;
1344
1345 UPDATE WIP_JOB_DTLS_INTERFACE
1346 SET resource_seq_num = x_res_seq_num_max
1347 WHERE rowid = p_rowid;
1348
1349 p_resource_seq_num := x_res_seq_num_max;
1350
1351 end if;
1352 /* Bug 4747951. For setup resource, get the schedule_seq_num
1353 of parent resource from interface if it exists since this
1354 will be the latest */
1355 if (p_parent_seq_num is not null) then
1356 begin
1357 begin
1358 select schedule_seq_num
1359 into x_schedule_seq_num
1360 from wip_job_dtls_interface
1361 where group_id = p_group_id
1362 and wip_entity_id = p_wip_entity_id
1363 and organization_id = p_organization_id
1364 and operation_seq_num = p_operation_seq_num
1365 and resource_seq_num = p_parent_seq_num
1366 and load_type = 1
1367 and substitution_type in (2,3);
1368 exception
1369 when others then x_schedule_seq_num := 0;
1370 end;
1371 if (x_schedule_seq_num = 0) then
1372 begin
1373 select schedule_seq_num
1374 into x_schedule_seq_num
1375 from wip_operation_resources
1376 where wip_entity_id = p_wip_entity_id
1377 AND organization_id = p_organization_id
1378 AND operation_seq_num = p_operation_seq_num
1379 AND resource_seq_num = p_parent_seq_num;
1380 exception
1381 when no_data_found then
1382 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1383 end;
1384 end if;
1385 end;
1386 UPDATE WIP_JOB_DTLS_INTERFACE
1387 SET schedule_seq_num = x_schedule_seq_num
1388 WHERE rowid = p_rowid;
1389
1390 end if;
1391 elsif (p_substitution_type = wip_job_details.WIP_CHANGE) then
1392 begin
1393 select setup_id
1394 into x_setup_id
1395 from wip_operation_resources
1396 where wip_entity_id = p_wip_entity_id
1397 AND organization_id = p_organization_id
1398 AND operation_seq_num = p_operation_seq_num
1399 AND resource_seq_num = p_resource_seq_num;
1400 exception
1401 when no_data_found then
1402 return;
1403 end;
1404
1405 UPDATE WIP_JOB_DTLS_INTERFACE
1406 SET setup_id = nvl(setup_id, x_setup_id)
1407 WHERE rowid = p_rowid;
1408
1409 end if;
1410
1411 exception
1412 when others then
1413 p_err_msg := 'WIPRSVDB.pls<Procedure derive_resource>:' || SQLERRM;
1414 p_err_code := SQLCODE;
1415
1416 end Derive_Resource;
1417
1418
1419 /* main procedure to add resource, call the above */
1420 Procedure Add_Resource(p_group_id number,
1421 p_wip_entity_id number,
1422 p_organization_id number,
1423 p_substitution_type number) IS
1424
1425 x_err_code varchar2(30) := null;
1426 x_err_msg varchar2(240) := NULL;
1427
1428 CURSOR res_info (p_group_id number,
1429 p_wip_entity_id number,
1430 p_organization_id number,
1431 p_substitution_type number) IS
1432 SELECT distinct operation_seq_num,
1433 resource_seq_num, resource_id_old, resource_id_new,
1434 usage_rate_or_amount,
1435 last_update_date, last_updated_by, creation_date, created_by,
1436 last_update_login, request_id, program_application_id,
1437 program_id, program_update_date,
1438 scheduled_flag, assigned_units, applied_resource_units,
1439 applied_resource_value, uom_code, basis_type,
1440 activity_id, autocharge_type, standard_rate_flag,
1441 start_date, completion_date,attribute_category, attribute1,
1442 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1443 attribute8,attribute9,attribute10,attribute11,attribute12,
1444 attribute13,attribute14,attribute15, schedule_seq_num,
1445 substitute_group_num, replacement_group_num, parent_seq_num, rowid
1446 FROM WIP_JOB_DTLS_INTERFACE
1447 WHERE group_id = p_group_id
1448 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1449 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1450 AND wip_entity_id = p_wip_entity_id
1451 AND organization_id = p_organization_id
1452 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1453 AND substitution_type = p_substitution_type;
1454
1455 BEGIN
1456
1457 FOR cur_row IN res_info(p_group_id,
1458 p_wip_entity_id,
1459 p_organization_id,
1460 p_substitution_type) LOOP
1461
1462 derive_resource(p_group_id,
1463 p_wip_entity_id,
1464 p_organization_id,
1465 p_substitution_type,
1466 cur_row.operation_seq_num,
1467 cur_row.resource_seq_num,
1468 cur_row.schedule_seq_num,
1469 cur_row.parent_seq_num,
1470 cur_row.rowid,
1471 x_err_code,
1472 x_err_msg);
1473
1474 Add_Res_Info_Exist(p_group_id,
1475 p_wip_entity_id,
1476 p_organization_id,
1477 p_substitution_type,
1478 cur_row.operation_seq_num);
1479
1480 IF Info_Missing(p_group_id,
1481 p_wip_entity_id,
1482 p_organization_id,
1483 p_substitution_type,
1484 cur_row.operation_seq_num) = 0 THEN
1485
1486 Valid_Resource(p_group_id,
1487 p_wip_entity_id,
1488 p_organization_id,
1489 p_substitution_type,
1490 cur_row.operation_seq_num,
1491 cur_row.resource_seq_num,
1492 cur_row.resource_id_new);
1493
1494 IF IS_Error(p_group_id,
1495 p_wip_entity_id,
1496 p_organization_id,
1497 p_substitution_type,
1498 cur_row.operation_seq_num,
1499 cur_row.resource_seq_num)= 0 THEN
1500
1501 val_add_res_dates(p_group_id,
1502 p_wip_entity_id,
1503 p_organization_id,
1504 p_substitution_type,
1505 cur_row.operation_seq_num,
1506 cur_row.resource_seq_num,
1507 cur_row.resource_id_new);
1508
1509 IF IS_Error(p_group_id,
1510 p_wip_entity_id,
1511 p_organization_id,
1512 p_substitution_type,
1513 cur_row.operation_seq_num,
1514 cur_row.resource_seq_num)= 0 THEN
1515
1516 Resource_Seq_Num(p_group_id,
1517 p_wip_entity_id,
1518 p_organization_id,
1519 p_substitution_type,
1520 cur_row.operation_seq_num,
1521 cur_row.resource_seq_num);
1522
1523 IF IS_Error(p_group_id,
1524 p_wip_entity_id,
1525 p_organization_id,
1526 p_substitution_type,
1527 cur_row.operation_seq_num,
1528 cur_row.resource_seq_num)= 0 THEN
1529
1530 Usage_Rate_Or_Amount(p_group_id,
1531 p_wip_entity_id,
1532 p_organization_id,
1533 p_substitution_type,
1534 cur_row.operation_seq_num,
1535 cur_row.resource_seq_num,
1536 cur_row.resource_id_new,
1537 cur_row.usage_rate_or_amount);
1538
1539 IF IS_Error(p_group_id,
1540 p_wip_entity_id,
1541 p_organization_id,
1542 p_substitution_type,
1543 cur_row.operation_seq_num,
1544 cur_row.resource_seq_num)= 0 THEN
1545
1546 Assigned_Units(p_group_id,
1547 p_wip_entity_id,
1548 p_organization_id,
1549 WIP_JOB_DETAILS.WIP_RESOURCE,
1550 p_substitution_type,
1551 cur_row.operation_seq_num,
1552 cur_row.resource_seq_num);
1553
1554 IF IS_Error(p_group_id,
1555 p_wip_entity_id,
1556 p_organization_id,
1557 p_substitution_type,
1558 cur_row.operation_seq_num,
1559 cur_row.resource_seq_num) = 0 THEN
1560
1561 Check_res_sched_subgroup (p_group_id,
1562 p_wip_entity_id,
1563 p_organization_id,
1564 p_substitution_type,
1565 cur_row.operation_seq_num,
1566 cur_row.resource_seq_num,
1567 cur_row.schedule_seq_num,
1568 cur_row.substitute_group_num,
1569 cur_row.replacement_group_num);
1570
1571 IF IS_Error(p_group_id,
1572 p_wip_entity_id,
1573 p_organization_id,
1574 p_substitution_type,
1575 cur_row.operation_seq_num,
1576 cur_row.resource_seq_num) = 0 THEN
1577 WIP_RESOURCE_DEFAULT.Default_Resource(
1578 p_group_id,
1579 p_wip_entity_id,
1580 p_organization_id,
1581 p_substitution_type,
1582 cur_row.operation_seq_num,
1583 cur_row.resource_seq_num,
1584 cur_row.resource_id_new,
1585 x_err_code,
1586 x_err_msg);
1587 END IF;
1588 END IF;
1589 END IF;
1590 END IF;
1591 END IF;
1592 END IF;
1593 END IF;
1594 END LOOP;
1595 END Add_Resource;
1596
1597 /* check for valid assigned units when changing resource assign units, it must be either
1598 equal to number of resource instances unless if no resource instance is defined
1599 */
1600 Procedure Validate_Assigned_Units(p_group_id number,
1601 p_wip_entity_id number,
1602 p_organization_id number,
1603 p_substitution_type number,
1604 p_operation_seq_num number,
1605 p_resource_seq_num number) IS
1606
1607 l_error_exists boolean := false;
1608 l_count number;
1609
1610 cursor c_invalid_rows is
1611 select interface_id
1612 from wip_job_dtls_interface wjdi
1613 where wjdi.group_id = p_group_id
1614 and wjdi.process_phase = wip_constants.ml_validation
1615 and wjdi.process_status in (wip_constants.running,
1616 wip_constants.warning)
1617 and wjdi.wip_entity_id = p_wip_entity_id
1618 and wjdi.organization_id = p_organization_id
1619 and wjdi.load_type = wip_job_details.wip_resource
1620 and wjdi.substitution_type = p_substitution_type
1621 and wjdi.operation_seq_num = p_operation_seq_num
1622 and wjdi.resource_seq_num = p_resource_seq_num
1623 and (wjdi.assigned_units < 0 or
1624 (nvl(wjdi.assigned_units,-1) <> l_count and l_count > 0));
1625
1626 BEGIN
1627 l_count := 0;
1628 begin
1629 select count(*) into l_count
1630 from wip_op_resource_instances
1631 where wip_entity_id = p_wip_entity_id
1632 and organization_id = p_organization_id
1633 and operation_seq_num = p_operation_seq_num
1634 and resource_seq_num = p_resource_seq_num;
1635 exception
1636 when no_data_found then
1637 null;
1638 end;
1639
1640 for l_inv_row in c_invalid_rows loop
1641 l_error_exists := true;
1642 fnd_message.set_name('WIP', 'WIP_ASSIGNED_UNITS_ERROR');
1643 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1644 if(wip_job_details.std_alone = 1) then
1645 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1646 p_text => substr(fnd_message.get,1,500),
1647 p_error_type => wip_jdi_utils.msg_error);
1648 else
1649 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1650 p_text => substr(fnd_message.get,1,500),
1651 p_error_type => wip_jdi_utils.msg_error);
1652 end if;
1653 end loop;
1654 if(l_error_exists) then
1655 update wip_job_dtls_interface wjdi
1656 set process_status = wip_constants.error
1657 where wjdi.group_id = p_group_id
1658 and wjdi.process_phase = wip_constants.ml_validation
1659 and wjdi.process_status in (wip_constants.running,
1660 wip_constants.warning)
1661 and wjdi.wip_entity_id = p_wip_entity_id
1662 and wjdi.organization_id = p_organization_id
1663 and wjdi.load_type in (wip_job_details.wip_resource)
1664 and wjdi.substitution_type = p_substitution_type
1665 and wjdi.operation_seq_num = p_operation_seq_num
1666 and wjdi.resource_seq_num = p_resource_seq_num
1667 and (wjdi.assigned_units < 0 or
1668 (wjdi.assigned_units <> l_count and l_count > 0));
1669 end if;
1670 END Validate_Assigned_Units;
1671
1672 Procedure Chng_Res_Info_Exist(p_group_id number,
1673 p_wip_entity_id number,
1674 p_organization_id number,
1675 p_substitution_type number,
1676 p_operation_seq_num number) IS
1677 cursor c_invalid_rows is
1678 select interface_id
1679 from wip_job_dtls_interface wjdi
1680 where wjdi.group_id = p_group_id
1681 and wjdi.process_phase = wip_constants.ml_validation
1682 and wjdi.process_status in (wip_constants.running,
1683 wip_constants.warning)
1684 and wjdi.wip_entity_id = p_wip_entity_id
1685 and wjdi.organization_id = p_organization_id
1686 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1687 and wjdi.substitution_type = p_substitution_type
1688 and wjdi.operation_seq_num = p_operation_seq_num
1689 and ( wjdi.resource_seq_num is null
1690 or wjdi.resource_id_old is null
1691 or wjdi.resource_id_new is null
1692 or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1693 and wjdi.usage_rate_or_amount is null
1694 )
1695 );
1696
1697 l_error_exists boolean := false;
1698 BEGIN
1699 /* we don't check up to usage_rate_or_amount since we assume up to
1700 resource_id_old and resource_id_new, it should be unique */
1701 for l_inv_row in c_invalid_rows loop
1702 l_error_exists := true;
1703 fnd_message.set_name('WIP', 'WIP_JDI_CHNG_RES_INFO_MISSING');
1704 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1705 if(wip_job_details.std_alone = 1) then
1706 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1707 p_text => substr(fnd_message.get,1,500),
1708 p_error_type => wip_jdi_utils.msg_error);
1709 else
1710 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1711 p_text => substr(fnd_message.get,1,500),
1712 p_error_type => wip_jdi_utils.msg_error);
1713 end if;
1714 end loop;
1715
1716 if(l_error_exists) then
1717 update wip_job_dtls_interface wjdi
1718 set process_status = wip_constants.error
1719 where wjdi.group_id = p_group_id
1720 and wjdi.process_phase = wip_constants.ml_validation
1721 and wjdi.process_status in (wip_constants.running,
1722 wip_constants.warning)
1723 and wjdi.wip_entity_id = p_wip_entity_id
1724 and wjdi.organization_id = p_organization_id
1725 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1726 and wjdi.substitution_type = p_substitution_type
1727 and wjdi.operation_seq_num = p_operation_seq_num
1728 and ( wjdi.resource_seq_num is null
1729 or wjdi.resource_id_old is null
1730 or wjdi.resource_id_new is null
1731 or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1732 and wjdi.usage_rate_or_amount is null
1733 )
1734 );
1735 end if;
1736 END Chng_Res_Info_Exist;
1737
1738 /* jy - Checks the validity of rows that change resources. There are two types of
1739 transactions. The first is to just change an existing resource in
1740 wip_operation_resources. The second is to substitute a
1741 resource in WOR with one in WSOR. To indicate a substitution:
1742 1) The substitution_group_num column must be either null or have the valid
1743 value for that res_seq_num/id.
1744 2) The replacement_group_num must be a valid value in WSOR (if it is the
1745 same as the one in WOR then nothing happens).
1746 First, we try to determine if a record is a valid substitution. If it contains
1747 wrong values for sub/repl group, it errors. If it doesn't contain wrong
1748 information but is not a substitution, we clear the two columns and validate it for a normal resource change.
1749 Note that with this validation, we don't allow users to change the sub/repl
1750 group of a resource through the dtls interface table.
1751 */
1752
1753 Procedure Change_Resource(p_group_id number,
1754 p_wip_entity_id number,
1755 p_organization_id number,
1756 p_substitution_type number) IS
1757
1758 x_err_code number := 0;
1759 x_err_msg varchar2(240) := NULL;
1760 l_sub_group_num number;
1761 l_repl_group_num number;
1762 l_dummy2 VARCHAR2(1);
1763 l_logLevel number;
1764 l_params wip_logger.param_tbl_t;
1765
1766 CURSOR res_info (p_group_id number,
1767 p_wip_entity_id number,
1768 p_organization_id number,
1769 p_substitution_type number) IS
1770 SELECT distinct operation_seq_num,
1771 resource_seq_num, resource_id_old, resource_id_new,
1772 usage_rate_or_amount,
1773 last_update_date, last_updated_by, creation_date, created_by,
1774 last_update_login, request_id, program_application_id,
1775 program_id, program_update_date,
1776 scheduled_flag, assigned_units, applied_resource_units,
1777 applied_resource_value, uom_code, basis_type,
1778 activity_id, autocharge_type, standard_rate_flag,
1779 start_date, completion_date,attribute_category, attribute1,
1780 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1781 attribute8,attribute9,attribute10,attribute11,attribute12,
1782 attribute13,attribute14,attribute15,
1783 schedule_seq_num, substitute_group_num,
1784 replacement_group_num, parent_seq_num, rowid
1785 FROM WIP_JOB_DTLS_INTERFACE
1786 WHERE group_id = p_group_id
1787 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1788 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1789 AND wip_entity_id = p_wip_entity_id
1790 AND organization_id = p_organization_id
1791 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1792 AND substitution_type = p_substitution_type;
1793
1794 BEGIN
1795
1796
1797 FOR cur_row IN res_info(p_group_id,
1798 p_wip_entity_id,
1799 p_organization_id,
1800 p_substitution_type) LOOP
1801
1802 derive_resource(p_group_id,
1803 p_wip_entity_id,
1804 p_organization_id,
1805 p_substitution_type,
1806 cur_row.operation_seq_num,
1807 cur_row.resource_seq_num,
1808 cur_row.schedule_seq_num,
1809 cur_row.parent_seq_num,
1810 cur_row.rowid,
1811 x_err_code,
1812 x_err_msg);
1813
1814 Chng_Res_Info_Exist(p_group_id,
1815 p_wip_entity_id,
1816 p_organization_id,
1817 p_substitution_type,
1818 cur_row.operation_seq_num);
1819
1820 IF Info_Missing(p_group_id,
1821 p_wip_entity_id,
1822 p_organization_id,
1823 p_substitution_type,
1824 cur_row.operation_seq_num) = 0 THEN
1825
1826 RES_JOB_Match (p_group_id,
1827 p_wip_entity_id,
1828 p_organization_id,
1829 p_substitution_type,
1830 cur_row.operation_seq_num,
1831 cur_row.resource_seq_num,
1832 cur_row.resource_id_old);
1833
1834 IF IS_Error(p_group_id,
1835 p_wip_entity_id,
1836 p_organization_id,
1837 p_substitution_type,
1838 cur_row.operation_seq_num,
1839 cur_row.resource_seq_num) = 0 THEN
1840
1841 /* fix for bug# 2043593 */
1842 If (cur_row.resource_id_old <> cur_row.resource_id_new) then
1843
1844 Safe_Delete (p_group_id,
1845 p_wip_entity_id,
1846 p_organization_id,
1847 p_substitution_type,
1848 cur_row.operation_seq_num,
1849 cur_row.resource_seq_num,
1850 cur_row.resource_id_old);
1851 End if;
1852
1853 IF IS_Error(p_group_id,
1854 p_wip_entity_id,
1855 p_organization_id,
1856 p_substitution_type,
1857 cur_row.operation_seq_num,
1858 cur_row.resource_seq_num) = 0 THEN
1859
1860 Safe_PO (p_group_id,
1861 p_wip_entity_id,
1862 p_organization_id,
1863 p_substitution_type,
1864 cur_row.operation_seq_num,
1865 cur_row.resource_seq_num,
1866 cur_row.resource_id_old);
1867
1868 IF IS_Error(p_group_id,
1869 p_wip_entity_id,
1870 p_organization_id,
1871 p_substitution_type,
1872 cur_row.operation_seq_num,
1873 cur_row.resource_seq_num) = 0 THEN
1874
1875 Valid_Resource(p_group_id,
1876 p_wip_entity_id,
1877 p_organization_id,
1878 p_substitution_type,
1879 cur_row.operation_seq_num,
1880 cur_row.resource_seq_num,
1881 cur_row.resource_id_new);
1882
1883 IF IS_Error(p_group_id,
1884 p_wip_entity_id,
1885 p_organization_id,
1886 p_substitution_type,
1887 cur_row.operation_seq_num,
1888 cur_row.resource_seq_num)= 0 THEN
1889
1890 val_change_res_dates(p_group_id,
1891 p_wip_entity_id,
1892 p_organization_id,
1893 p_substitution_type,
1894 cur_row.operation_seq_num,
1895 cur_row.resource_seq_num,
1896 cur_row.resource_id_old);
1897
1898 IF IS_Error(p_group_id,
1899 p_wip_entity_id,
1900 p_organization_id,
1901 p_substitution_type,
1902 cur_row.operation_seq_num,
1903 cur_row.resource_seq_num) = 0 THEN
1904
1905 Usage_Rate_Or_Amount(p_group_id,
1906 p_wip_entity_id,
1907 p_organization_id,
1908 p_substitution_type,
1909 cur_row.operation_seq_num,
1910 cur_row.resource_seq_num,
1911 cur_row.resource_id_new,
1912 cur_row.usage_rate_or_amount);
1913
1914 IF IS_Error(p_group_id,
1915 p_wip_entity_id,
1916 p_organization_id,
1917 p_substitution_type,
1918 cur_row.operation_seq_num,
1919 cur_row.resource_seq_num)= 0 THEN
1920
1921 Assigned_Units(p_group_id,
1922 p_wip_entity_id,
1923 p_organization_id,
1924 WIP_JOB_DETAILS.WIP_RESOURCE,
1925 p_substitution_type,
1926 cur_row.operation_seq_num,
1927 cur_row.resource_seq_num);
1928
1929 IF IS_Error(p_group_id,
1930 p_wip_entity_id,
1931 p_organization_id,
1932 p_substitution_type,
1933 cur_row.operation_seq_num,
1934 cur_row.resource_seq_num) = 0 THEN
1935
1936 Check_res_sched_subgroup (p_group_id,
1937 p_wip_entity_id,
1938 p_organization_id,
1939 p_substitution_type,
1940 cur_row.operation_seq_num,
1941 cur_row.resource_seq_num,
1942 cur_row.schedule_seq_num,
1943 cur_row.substitute_group_num,
1944 cur_row.replacement_group_num);
1945
1946 IF IS_Error(p_group_id,
1947 p_wip_entity_id,
1948 p_organization_id,
1949 p_substitution_type,
1950 cur_row.operation_seq_num,
1951 cur_row.resource_seq_num)= 0 THEN
1952
1953 Validate_Assigned_Units(p_group_id,
1954 p_wip_entity_id,
1955 p_organization_id,
1956 p_substitution_type,
1957 cur_row.operation_seq_num,
1958 cur_row.resource_seq_num);
1959
1960 IF IS_Error(p_group_id,
1961 p_wip_entity_id,
1962 p_organization_id,
1963 p_substitution_type,
1964 cur_row.operation_seq_num,
1965 cur_row.resource_seq_num) = 0 THEN
1966
1967 WIP_RESOURCE_DEFAULT.Default_Resource(
1968 p_group_id,
1969 p_wip_entity_id,
1970 p_organization_id,
1971 p_substitution_type,
1972 cur_row.operation_seq_num,
1973 cur_row.resource_seq_num,
1974 cur_row.resource_id_new,
1975 x_err_code,
1976 x_err_msg);
1977
1978 END IF;
1979 END IF;
1980 END IF;
1981 END IF;
1982 END IF;
1983 END IF;
1984 END IF;
1985 END IF;
1986 END IF;
1987 END IF;
1988 END LOOP;
1989 END Change_Resource;
1990
1991 function IS_Error(p_group_id number,
1992 p_wip_entity_id number,
1993 p_organization_id number,
1994 p_substitution_type number,
1995 p_operation_seq_num number,
1996 p_resource_seq_num number) return number IS
1997
1998 x_count number := 0;
1999
2000 BEGIN
2001
2002 SELECT count(*)
2003 INTO x_count
2004 FROM WIP_JOB_DTLS_INTERFACE
2005 WHERE group_id = p_group_id
2006 AND process_status = WIP_CONSTANTS.ERROR
2007 AND wip_entity_id = p_wip_entity_id
2008 AND organization_id = p_organization_id
2009 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
2010 AND substitution_type= p_substitution_type
2011 AND operation_seq_num= p_operation_seq_num
2012 AND resource_seq_num = p_resource_seq_num;
2013
2014
2015 IF x_count <> 0 THEN
2016 return 1;
2017 ELSE return 0;
2018 END IF;
2019
2020 END IS_Error;
2021
2022
2023 function Info_Missing(p_group_id number,
2024 p_wip_entity_id number,
2025 p_organization_id number,
2026 p_substitution_type number,
2027 p_operation_seq_num number) return number IS
2028
2029 x_count number := 0;
2030
2031 BEGIN
2032
2033 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
2034 SELECT count(*)
2035 INTO x_count
2036 FROM WIP_JOB_DTLS_INTERFACE
2037 WHERE group_id = p_group_id
2038 AND process_status = WIP_CONSTANTS.ERROR
2039 AND wip_entity_id = p_wip_entity_id
2040 AND organization_id = p_organization_id
2041 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
2042 AND substitution_type= p_substitution_type
2043 AND operation_seq_num= p_operation_seq_num
2044 AND (resource_seq_num IS NULL
2045 OR resource_id_old IS NULL);
2046
2047 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
2048 SELECT count(*)
2049 INTO x_count
2050 FROM WIP_JOB_DTLS_INTERFACE
2051 WHERE group_id = p_group_id
2052 AND process_status = WIP_CONSTANTS.ERROR
2053 AND wip_entity_id = p_wip_entity_id
2054 AND organization_id = p_organization_id
2055 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
2056 AND substitution_type= p_substitution_type
2057 AND operation_seq_num= p_operation_seq_num
2058 AND (resource_id_new IS NULL
2059 OR usage_rate_or_amount IS NULL);
2060
2061 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
2062 SELECT count(*)
2063 INTO x_count
2064 FROM WIP_JOB_DTLS_INTERFACE
2065 WHERE group_id = p_group_id
2066 AND process_status = WIP_CONSTANTS.ERROR
2067 AND wip_entity_id = p_wip_entity_id
2068 AND organization_id = p_organization_id
2069 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
2070 AND substitution_type= p_substitution_type
2071 AND operation_seq_num= p_operation_seq_num
2072 AND (resource_seq_num IS NULL
2073 OR resource_id_old IS NULL
2074 OR resource_id_new IS NULL
2075 OR usage_rate_or_amount IS NULL);
2076
2077 END IF;
2078
2079 IF x_count <> 0 THEN
2080 return 1;
2081 ELSE return 0;
2082 END IF;
2083
2084 END Info_Missing;
2085
2086 Procedure Delete_Sub_Resource (p_group_id number,
2087 p_wip_entity_id number,
2088 p_organization_id number,
2089 p_substitution_type number) IS
2090
2091 CURSOR res_info (p_group_id number,
2092 p_wip_entity_id number,
2093 p_organization_id number,
2094 p_substitution_type number) IS
2095 SELECT distinct operation_seq_num,
2096 resource_seq_num, resource_id_old, resource_id_new,
2097 usage_rate_or_amount,
2098 last_update_date, last_updated_by, creation_date, created_by,
2099 last_update_login, request_id, program_application_id,
2100 program_id, program_update_date,
2101 scheduled_flag, assigned_units, applied_resource_units,
2102 applied_resource_value, uom_code, basis_type,
2103 activity_id, autocharge_type, standard_rate_flag,
2104 start_date, completion_date,attribute_category, attribute1,
2105 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2106 attribute8,attribute9,attribute10,attribute11,attribute12,
2107 attribute13,attribute14,attribute15
2108 FROM WIP_JOB_DTLS_INTERFACE
2109 WHERE group_id = p_group_id
2110 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2111 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2112 AND wip_entity_id = p_wip_entity_id
2113 AND organization_id = p_organization_id
2114 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2115 AND substitution_type = p_substitution_type;
2116
2117 BEGIN
2118 FOR cur_row IN res_info(p_group_id,
2119 p_wip_entity_id,
2120 p_organization_id,
2121 p_substitution_type) LOOP
2122
2123 Del_Res_Info_Exist(p_group_id,
2124 p_wip_entity_id,
2125 p_organization_id,
2126 p_substitution_type,
2127 cur_row.operation_seq_num);
2128
2129 IF Info_Missing(p_group_id,
2130 p_wip_entity_id,
2131 p_organization_id,
2132 p_substitution_type,
2133 cur_row.operation_seq_num) = 0 THEN
2134
2135 RES_JOB_Match (p_group_id,
2136 p_wip_entity_id,
2137 p_organization_id,
2138 p_substitution_type,
2139 cur_row.operation_seq_num,
2140 cur_row.resource_seq_num,
2141 cur_row.resource_id_old);
2142
2143 END IF;
2144 END LOOP;
2145 END Delete_Sub_Resource;
2146
2147 /* jy - checks for a valid resource substitution row. */
2148 Procedure Check_Res_Substitution(p_group_id number,
2149 p_wip_entity_id number,
2150 p_organization_id number,
2151 p_substitution_type number,
2152 p_operation_seq_num number,
2153 p_resource_seq_num number,
2154 p_resource_id_old number) IS
2155
2156 cursor c_invalid_rows is
2157 select interface_id
2158 from wip_job_dtls_interface wjdi
2159 where wjdi.group_id = p_group_id
2160 and wjdi.process_phase = wip_constants.ml_validation
2161 and wjdi.process_status in (wip_constants.running,
2162 wip_constants.warning)
2163 and wjdi.wip_entity_id = p_wip_entity_id
2164 and wjdi.organization_id = p_organization_id
2165 and wjdi.load_type = wip_job_details.wip_resource
2166 and wjdi.substitution_type = p_substitution_type
2167 and wjdi.operation_seq_num = p_operation_seq_num
2168 and wjdi.resource_seq_num = p_resource_seq_num
2169 and wjdi.resource_id_old = p_resource_id_old
2170 and ( ( wjdi.substitute_group_num is not null
2171 and not exists (select 1
2172 from wip_operation_resources wor
2173 where wor.wip_entity_id = wjdi.wip_entity_id
2174 and wor.organization_id = wjdi.organization_id
2175 and wor.resource_id = wjdi.resource_id_old
2176 and wor.operation_seq_num = wjdi.operation_seq_num
2177 and wor.resource_seq_num = wjdi.resource_seq_num
2178 and wor.substitute_group_num = wjdi.substitute_group_num
2179 )
2180 )
2181 or ( --Bug 12937792. Replacement group null.
2182 --wjdi.replacement_group_num is not null and
2183 not exists (select 1
2184 from wip_sub_operation_resources wsor,
2185 wip_operation_resources wor
2186 where wsor.wip_entity_id = wjdi.wip_entity_id
2187 and wsor.organization_id = wjdi.organization_id
2188 and wsor.operation_seq_num = wjdi.operation_seq_num
2189 and wor.wip_entity_id = wjdi.wip_entity_id
2190 and wor.organization_id = wjdi.organization_id
2191 and wor.resource_id = wjdi.resource_id_old
2192 and wor.operation_seq_num = wjdi.operation_seq_num
2193 and wor.resource_seq_num = wjdi.resource_seq_num
2194 and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
2195 and wsor.replacement_group_num = wjdi.replacement_group_num
2196 )
2197 and not exists (select 1
2198 from wip_operation_resources wor
2199 where wor.wip_entity_id = wjdi.wip_entity_id
2200 and wor.organization_id = wjdi.organization_id
2201 and wor.resource_id = wjdi.resource_id_old
2202 and wor.operation_seq_num = wjdi.operation_seq_num
2203 and wor.resource_seq_num = wjdi.resource_seq_num
2204 and ( (wor.substitute_group_num =
2205 nvl(wjdi.substitute_group_num,wor.substitute_group_num)) OR
2206 (wor.substitute_group_num is null and
2207 wjdi.substitute_group_num is null)
2208 )
2209 and wor.replacement_group_num = wjdi.replacement_group_num
2210 )
2211 )
2212 );
2213
2214 cursor c_not_sub_rows is
2215 select wjdi.substitute_group_num,
2216 wjdi.replacement_group_num
2217 from wip_job_dtls_interface wjdi
2218 where wjdi.group_id = p_group_id
2219 and wjdi.process_phase = wip_constants.ml_validation
2220 and wjdi.process_status in (wip_constants.running,
2221 wip_constants.warning)
2222 and wjdi.wip_entity_id = p_wip_entity_id
2223 and wjdi.organization_id = p_organization_id
2224 and wjdi.load_type = wip_job_details.wip_resource
2225 and wjdi.substitution_type = p_substitution_type
2226 and wjdi.operation_seq_num = p_operation_seq_num
2227 and wjdi.resource_seq_num = p_resource_seq_num
2228 and wjdi.resource_id_old = p_resource_id_old
2229 and ( wjdi.replacement_group_num is null
2230 or ( wjdi.replacement_group_num is not null
2231 and exists (select 1
2232 from wip_operation_resources wor
2233 where wor.wip_entity_id = wjdi.wip_entity_id
2234 and wor.organization_id = wjdi.organization_id
2235 and wor.resource_id = wjdi.resource_id_old
2236 and wor.operation_seq_num = wjdi.operation_seq_num
2237 and wor.resource_seq_num = wjdi.resource_seq_num
2238 and ( (wor.substitute_group_num =
2239 nvl(wjdi.substitute_group_num, wor.substitute_group_num)) OR
2240 (wor.substitute_group_num is null and
2241 wjdi.substitute_group_num is null)
2242 )
2243 and wor.replacement_group_num = nvl(wjdi.replacement_group_num,0)
2244 )
2245 )
2246 )
2247 for update;
2248
2249 cursor c_sub_rows is
2250 select wip_entity_id,
2251 organization_id,
2252 resource_id_old,
2253 operation_seq_num,
2254 resource_seq_num,
2255 substitute_group_num
2256 from wip_job_dtls_interface
2257 where group_id = p_group_id
2258 and process_phase = wip_constants.ml_validation
2259 and process_status in (wip_constants.running,
2260 wip_constants.warning)
2261 and wip_entity_id = p_wip_entity_id
2262 and organization_id = p_organization_id
2263 and load_type = wip_job_details.wip_resource
2264 and substitution_type = p_substitution_type
2265 and operation_seq_num = p_operation_seq_num
2266 and resource_seq_num = p_resource_seq_num
2267 and resource_id_old = p_resource_id_old
2268 and replacement_group_num is not null
2269 for update;
2270
2271 l_error_exists boolean := false;
2272 l_sub_group_temp number;
2273
2274
2275 BEGIN
2276
2277 for l_inv_row in c_invalid_rows loop
2278 l_error_exists := true;
2279 fnd_message.set_name('WIP','WIP_JDI_RES_SUB_INFO_MISSING' );
2280 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
2281 if(wip_job_details.std_alone = 1) then
2282 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
2283 p_text => substr(fnd_message.get,1,500),
2284 p_error_type => wip_jdi_utils.msg_error);
2285 else
2286 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2287 p_text => substr(fnd_message.get,1,500),
2288 p_error_type => wip_jdi_utils.msg_error);
2289 end if;
2290 end loop;
2291
2292 if(l_error_exists) then
2293 update wip_job_dtls_interface wjdi
2294 set process_status = wip_constants.error
2295 where wjdi.group_id = p_group_id
2296 and wjdi.process_phase = wip_constants.ml_validation
2297 and wjdi.process_status in (wip_constants.running,
2298 wip_constants.warning)
2299 and wjdi.wip_entity_id = p_wip_entity_id
2300 and wjdi.organization_id = p_organization_id
2301 and wjdi.load_type = wip_job_details.wip_resource
2302 and wjdi.substitution_type = p_substitution_type
2303 and wjdi.operation_seq_num = p_operation_seq_num
2304 and wjdi.resource_seq_num = p_resource_seq_num
2305 and wjdi.resource_id_old = p_resource_id_old
2306 and ( ( wjdi.substitute_group_num is not null
2307 and not exists (select 1
2308 from wip_operation_resources wor
2309 where wor.wip_entity_id = wjdi.wip_entity_id
2310 and wor.organization_id = wjdi.organization_id
2311 and wor.resource_id = wjdi.resource_id_old
2312 and wor.operation_seq_num = wjdi.operation_seq_num
2313 and wor.resource_seq_num = wjdi.resource_seq_num
2314 and wor.substitute_group_num = wjdi.substitute_group_num
2315 )
2316 )
2317 or ( -- Bug 12937792. Replacement Group can be Null.
2318 wjdi.replacement_group_num is not null and
2319 not exists (select 1
2320 from wip_sub_operation_resources wsor,
2321 wip_operation_resources wor
2322 where wsor.wip_entity_id = wjdi.wip_entity_id
2323 and wsor.organization_id = wjdi.organization_id
2324 and wsor.operation_seq_num = wjdi.operation_seq_num
2325 and wor.wip_entity_id = wjdi.wip_entity_id
2326 and wor.organization_id = wjdi.organization_id
2327 and wor.resource_id = wjdi.resource_id_old
2328 and wor.operation_seq_num = wjdi.operation_seq_num
2329 and wor.resource_seq_num = wjdi.resource_seq_num
2330 and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
2331 and wsor.replacement_group_num = wjdi.replacement_group_num
2332 )
2333 )
2334 );
2335 RETURN;
2336 end if;
2337
2338 for l_ns_row in c_not_sub_rows loop
2339 update wip_job_dtls_interface wjdi
2340 set substitute_group_num = null,
2341 replacement_group_num = null
2342 where current of c_not_sub_rows;
2343 end loop;
2344
2345 for l_sub_row in c_sub_rows loop
2346 if l_sub_row.substitute_group_num is null then
2347 select wor.substitute_group_num
2348 into l_sub_group_temp
2349 from wip_operation_resources wor
2350 where wor.wip_entity_id = l_sub_row.wip_entity_id
2351 and wor.organization_id = l_sub_row.organization_id
2352 and wor.resource_id = l_sub_row.resource_id_old
2353 and wor.operation_seq_num = l_sub_row.operation_seq_num
2354 and wor.resource_seq_num = l_sub_row.resource_seq_num;
2355 update wip_job_dtls_interface
2356 set substitute_group_num = l_sub_group_temp
2357 where current of c_sub_rows;
2358 end if;
2359 end loop;
2360
2361 END Check_Res_Substitution;
2362
2363 Procedure Substitute_Info (p_group_id number,
2364 p_wip_entity_id number,
2365 p_organization_id number,
2366 p_substitution_type number,
2367 p_operation_seq_num number,
2368 p_resource_seq_num number) IS
2369 cursor c_invalid_rows is
2370 select interface_id
2371 from wip_job_dtls_interface wjdi
2372 where wjdi.group_id = p_group_id
2373 and wjdi.process_phase = wip_constants.ml_validation
2374 and wjdi.process_status in (wip_constants.running,
2375 wip_constants.warning)
2376 and wjdi.wip_entity_id = p_wip_entity_id
2377 and wjdi.organization_id = p_organization_id
2378 and wjdi.load_type = wip_job_details.wip_sub_res
2379 and wjdi.substitution_type = p_substitution_type
2380 and wjdi.operation_seq_num = p_operation_seq_num
2381 and wjdi.resource_seq_num = p_resource_seq_num
2382 and ( wjdi.schedule_seq_num < 0
2383 or wjdi.substitute_group_num is null
2384 or wjdi.substitute_group_num < 0
2385 -- Bug 12937792. Replacement Group can be Null.
2386 -- or wjdi.replacement_group_num is null
2387 or wjdi.replacement_group_num < 0
2388 or not exists (select 1
2389 from wip_operation_resources wor
2390 where wor.wip_entity_id = wjdi.wip_entity_id
2391 and wor.organization_id = wjdi.organization_id
2392 and wor.operation_seq_num = wjdi.operation_seq_num
2393 and wor.substitute_group_num = wjdi.substitute_group_num)
2394 or exists (select 1
2395 from wip_operation_resources wor
2396 where wor.wip_entity_id = wjdi.wip_entity_id
2397 and wor.organization_id = wjdi.organization_id
2398 and wor.operation_seq_num = wjdi.operation_seq_num
2399 and wor.substitute_group_num = wjdi.substitute_group_num
2400 and wor.replacement_group_num = wjdi.replacement_group_num)
2401 );
2402
2403 l_error_exists boolean := false;
2404 begin
2405
2406 for l_inv_row in c_invalid_rows loop
2407 l_error_exists := true;
2408 fnd_message.set_name('WIP', 'WIP_JDI_RES_SUB_INFO_MISSING');
2409 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
2410 if(wip_job_details.std_alone = 1) then
2411 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
2412 p_text => substr(fnd_message.get,1,500),
2413 p_error_type => wip_jdi_utils.msg_error);
2414 else
2415 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2416 p_text => substr(fnd_message.get,1,500),
2417 p_error_type => wip_jdi_utils.msg_error);
2418 end if;
2419 end loop;
2420
2421 if(l_error_exists) then
2422 update wip_job_dtls_interface wjdi
2423 set process_status = wip_constants.error
2424 where wjdi.group_id = p_group_id
2425 and wjdi.process_phase = wip_constants.ml_validation
2426 and wjdi.process_status in (wip_constants.running,
2427 wip_constants.warning)
2428 and wjdi.wip_entity_id = p_wip_entity_id
2429 and wjdi.organization_id = p_organization_id
2430 and wjdi.load_type = wip_job_details.wip_sub_res
2431 and wjdi.substitution_type = p_substitution_type
2432 and wjdi.operation_seq_num = p_operation_seq_num
2433 and wjdi.resource_seq_num = p_resource_seq_num
2434 and ( wjdi.schedule_seq_num is null
2435 or wjdi.schedule_seq_num < 0
2436 or wjdi.substitute_group_num is null
2437 or wjdi.substitute_group_num < 0
2438 -- Bug 12937792. Replacement Group can be Null.
2439 -- or wjdi.replacement_group_num is null
2440 or wjdi.replacement_group_num < 0
2441 or not exists (select 1
2442 from wip_operation_resources wor
2443 where wor.wip_entity_id = wjdi.wip_entity_id
2444 and wor.organization_id = wjdi.organization_id
2445 and wor.operation_seq_num = wjdi.operation_seq_num
2446 and wor.substitute_group_num = wjdi.substitute_group_num)
2447 or exists (select 1
2448 from wip_operation_resources wor
2449 where wor.wip_entity_id = wjdi.wip_entity_id
2450 and wor.organization_id = wjdi.organization_id
2451 and wor.operation_seq_num = wjdi.operation_seq_num
2452 and wor.substitute_group_num = wjdi.substitute_group_num
2453 and wor.replacement_group_num = wjdi.replacement_group_num)
2454 );
2455 end if;
2456 end substitute_info;
2457
2458
2459
2460 Procedure Add_Sub_Resource(p_group_id number,
2461 p_wip_entity_id number,
2462 p_organization_id number,
2463 p_substitution_type number) IS
2464
2465 x_err_code varchar2(30) := null;
2466 x_err_msg varchar2(240) := NULL;
2467
2468 CURSOR res_info (p_group_id number,
2469 p_wip_entity_id number,
2470 p_organization_id number,
2471 p_substitution_type number) IS
2472 SELECT distinct operation_seq_num,
2473 resource_seq_num, resource_id_old, resource_id_new,
2474 usage_rate_or_amount,
2475 last_update_date, last_updated_by, creation_date, created_by,
2476 last_update_login, request_id, program_application_id,
2477 program_id, program_update_date,
2478 scheduled_flag, assigned_units, applied_resource_units,
2479 applied_resource_value, uom_code, basis_type,
2480 activity_id, autocharge_type, standard_rate_flag,
2481 start_date, completion_date,attribute_category, attribute1,
2482 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2483 attribute8,attribute9,attribute10,attribute11,attribute12,
2484 attribute13,attribute14,attribute15,schedule_seq_num,
2485 substitute_group_num, replacement_group_num, parent_seq_num, rowid
2486 FROM WIP_JOB_DTLS_INTERFACE
2487 WHERE group_id = p_group_id
2488 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2489 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2490 AND wip_entity_id = p_wip_entity_id
2491 AND organization_id = p_organization_id
2492 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2493 AND substitution_type = p_substitution_type;
2494
2495
2496 BEGIN
2497 FOR cur_row IN res_info(p_group_id,
2498 p_wip_entity_id,
2499 p_organization_id,
2500 p_substitution_type) LOOP
2501
2502 derive_resource(p_group_id,
2503 p_wip_entity_id,
2504 p_organization_id,
2505 p_substitution_type,
2506 cur_row.operation_seq_num,
2507 cur_row.resource_seq_num,
2508 cur_row.schedule_seq_num,
2509 cur_row.parent_seq_num,
2510 cur_row.rowid,
2511 x_err_code,
2512 x_err_msg);
2513
2514 Add_Res_Info_Exist(p_group_id,
2515 p_wip_entity_id,
2516 p_organization_id,
2517 p_substitution_type,
2518 cur_row.operation_seq_num);
2519
2520 IF Info_Missing(p_group_id,
2521 p_wip_entity_id,
2522 p_organization_id,
2523 p_substitution_type,
2524 cur_row.operation_seq_num) = 0 THEN
2525
2526 Valid_Resource(p_group_id,
2527 p_wip_entity_id,
2528 p_organization_id,
2529 p_substitution_type,
2530 cur_row.operation_seq_num,
2531 cur_row.resource_seq_num,
2532 cur_row.resource_id_new);
2533
2534 IF IS_Error(p_group_id,
2535 p_wip_entity_id,
2536 p_organization_id,
2537 p_substitution_type,
2538 cur_row.operation_seq_num,
2539 cur_row.resource_seq_num)= 0 THEN
2540
2541 Resource_Seq_Num(p_group_id,
2542 p_wip_entity_id,
2543 p_organization_id,
2544 p_substitution_type,
2545 cur_row.operation_seq_num,
2546 cur_row.resource_seq_num);
2547
2548 IF IS_Error(p_group_id,
2549 p_wip_entity_id,
2550 p_organization_id,
2551 p_substitution_type,
2552 cur_row.operation_seq_num,
2553 cur_row.resource_seq_num)= 0 THEN
2554
2555 Usage_Rate_Or_Amount(p_group_id,
2556 p_wip_entity_id,
2557 p_organization_id,
2558 p_substitution_type,
2559 cur_row.operation_seq_num,
2560 cur_row.resource_seq_num,
2561 cur_row.resource_id_new,
2562 cur_row.usage_rate_or_amount);
2563
2564 IF IS_Error(p_group_id,
2565 p_wip_entity_id,
2566 p_organization_id,
2567 p_substitution_type,
2568 cur_row.operation_seq_num,
2569 cur_row.resource_seq_num)= 0 THEN
2570
2571 Assigned_Units(p_group_id,
2572 p_wip_entity_id,
2573 p_organization_id,
2574 WIP_JOB_DETAILS.WIP_SUB_RES,
2575 p_substitution_type,
2576 cur_row.operation_seq_num,
2577 cur_row.resource_seq_num);
2578
2579 IF IS_Error(p_group_id,
2580 p_wip_entity_id,
2581 p_organization_id,
2582 p_substitution_type,
2583 cur_row.operation_seq_num,
2584 cur_row.resource_seq_num)= 0 THEN
2585
2586 Substitute_Info(p_group_id,
2587 p_wip_entity_id,
2588 p_organization_id,
2589 p_substitution_type,
2590 cur_row.operation_seq_num,
2591 cur_row.resource_seq_num);
2592
2593 IF IS_Error(p_group_id,
2594 p_wip_entity_id,
2595 p_organization_id,
2596 p_substitution_type,
2597 cur_row.operation_seq_num,
2598 cur_row.resource_seq_num) = 0 THEN
2599
2600 Check_sub_sched_subgroup (p_group_id,
2601 p_wip_entity_id,
2602 p_organization_id,
2603 p_substitution_type,
2604 cur_row.operation_seq_num,
2605 cur_row.resource_seq_num,
2606 cur_row.schedule_seq_num,
2607 cur_row.substitute_group_num,
2608 cur_row.replacement_group_num);
2609
2610 IF IS_Error(p_group_id,
2611 p_wip_entity_id,
2612 p_organization_id,
2613 p_substitution_type,
2614 cur_row.operation_seq_num,
2615 cur_row.resource_seq_num) = 0 THEN
2616
2617 WIP_RESOURCE_DEFAULT.Default_Resource(
2618 p_group_id,
2619 p_wip_entity_id,
2620 p_organization_id,
2621 p_substitution_type,
2622 cur_row.operation_seq_num,
2623 cur_row.resource_seq_num,
2624 cur_row.resource_id_new,
2625 x_err_code,
2626 x_err_msg);
2627 END IF;
2628 END IF;
2629 END IF;
2630 END IF;
2631 END IF;
2632 END IF;
2633 END IF;
2634 END LOOP;
2635 END Add_Sub_Resource;
2636
2637
2638 Procedure Change_Sub_Resource(p_group_id number,
2639 p_wip_entity_id number,
2640 p_organization_id number,
2641 p_substitution_type number) IS
2642
2643 x_err_code number := 0;
2644 x_err_msg varchar2(240) := NULL;
2645 CURSOR res_info (p_group_id number,
2646 p_wip_entity_id number,
2647 p_organization_id number,
2648 p_substitution_type number) IS
2649 SELECT distinct operation_seq_num,
2650 resource_seq_num, resource_id_old, resource_id_new,
2651 usage_rate_or_amount,
2652 last_update_date, last_updated_by, creation_date, created_by,
2653 last_update_login, request_id, program_application_id,
2654 program_id, program_update_date,
2655 scheduled_flag, assigned_units, applied_resource_units,
2656 applied_resource_value, uom_code, basis_type,
2657 activity_id, autocharge_type, standard_rate_flag,
2658 start_date, completion_date,attribute_category, attribute1,
2659 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2660 attribute8,attribute9,attribute10,attribute11,attribute12,
2661 attribute13,attribute14,attribute15,schedule_seq_num,
2662 substitute_group_num, replacement_group_num
2663 FROM WIP_JOB_DTLS_INTERFACE
2664 WHERE group_id = p_group_id
2665 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2666 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2667 AND wip_entity_id = p_wip_entity_id
2668 AND organization_id = p_organization_id
2669 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2670 AND substitution_type = p_substitution_type;
2671
2672
2673 BEGIN
2674 FOR cur_row IN res_info(p_group_id,
2675 p_wip_entity_id,
2676 p_organization_id,
2677 p_substitution_type) LOOP
2678
2679 Chng_Res_Info_Exist(p_group_id,
2680 p_wip_entity_id,
2681 p_organization_id,
2682 p_substitution_type,
2683 cur_row.operation_seq_num);
2684
2685 IF Info_Missing(p_group_id,
2686 p_wip_entity_id,
2687 p_organization_id,
2688 p_substitution_type,
2689 cur_row.operation_seq_num) = 0 THEN
2690 RES_JOB_Match (p_group_id,
2691 p_wip_entity_id,
2692 p_organization_id,
2693 p_substitution_type,
2694 cur_row.operation_seq_num,
2695 cur_row.resource_seq_num,
2696 cur_row.resource_id_old);
2697
2698 IF IS_Error(p_group_id,
2699 p_wip_entity_id,
2700 p_organization_id,
2701 p_substitution_type,
2702 cur_row.operation_seq_num,
2703 cur_row.resource_seq_num) = 0 THEN
2704 Safe_Delete (p_group_id,
2705 p_wip_entity_id,
2706 p_organization_id,
2707 p_substitution_type,
2708 cur_row.operation_seq_num,
2709 cur_row.resource_seq_num,
2710 cur_row.resource_id_old);
2711
2712 IF IS_Error(p_group_id,
2713 p_wip_entity_id,
2714 p_organization_id,
2715 p_substitution_type,
2716 cur_row.operation_seq_num,
2717 cur_row.resource_seq_num) = 0 THEN
2718
2719 Valid_Resource(p_group_id,
2720 p_wip_entity_id,
2721 p_organization_id,
2722 p_substitution_type,
2723 cur_row.operation_seq_num,
2724 cur_row.resource_seq_num,
2725 cur_row.resource_id_new);
2726
2727 IF IS_Error(p_group_id,
2728 p_wip_entity_id,
2729 p_organization_id,
2730 p_substitution_type,
2731 cur_row.operation_seq_num,
2732 cur_row.resource_seq_num) = 0 THEN
2733
2734 Substitute_Info(p_group_id,
2735 p_wip_entity_id,
2736 p_organization_id,
2737 p_substitution_type,
2738 cur_row.operation_seq_num,
2739 cur_row.resource_seq_num);
2740
2741 IF IS_Error(p_group_id,
2742 p_wip_entity_id,
2743 p_organization_id,
2744 p_substitution_type,
2745 cur_row.operation_seq_num,
2746 cur_row.resource_seq_num) = 0 THEN
2747
2748 Usage_Rate_Or_Amount(p_group_id,
2749 p_wip_entity_id,
2750 p_organization_id,
2751 p_substitution_type,
2752 cur_row.operation_seq_num,
2753 cur_row.resource_seq_num,
2754 cur_row.resource_id_new,
2755 cur_row.usage_rate_or_amount);
2756
2757 IF IS_Error(p_group_id,
2758 p_wip_entity_id,
2759 p_organization_id,
2760 p_substitution_type,
2761 cur_row.operation_seq_num,
2762 cur_row.resource_seq_num)= 0 THEN
2763
2764 Assigned_Units(p_group_id,
2765 p_wip_entity_id,
2766 p_organization_id,
2767 WIP_JOB_DETAILS.WIP_SUB_RES,
2768 p_substitution_type,
2769 cur_row.operation_seq_num,
2770 cur_row.resource_seq_num);
2771
2772 IF IS_Error(p_group_id,
2773 p_wip_entity_id,
2774 p_organization_id,
2775 p_substitution_type,
2776 cur_row.operation_seq_num,
2777 cur_row.resource_seq_num) = 0 THEN
2778
2779 Check_sub_sched_subgroup (p_group_id,
2780 p_wip_entity_id,
2781 p_organization_id,
2782 p_substitution_type,
2783 cur_row.operation_seq_num,
2784 cur_row.resource_seq_num,
2785 cur_row.schedule_seq_num,
2786 cur_row.substitute_group_num,
2787 cur_row.replacement_group_num);
2788
2789 IF IS_Error(p_group_id,
2790 p_wip_entity_id,
2791 p_organization_id,
2792 p_substitution_type,
2793 cur_row.operation_seq_num,
2794 cur_row.resource_seq_num) = 0 THEN
2795
2796 WIP_RESOURCE_DEFAULT.Default_Resource(
2797 p_group_id,
2798 p_wip_entity_id,
2799 p_organization_id,
2800 p_substitution_type,
2801 cur_row.operation_seq_num,
2802 cur_row.resource_seq_num,
2803 cur_row.resource_id_new,
2804 x_err_code,
2805 x_err_msg);
2806 END IF;
2807 END IF;
2808 END IF;
2809 END IF;
2810 END IF;
2811 END IF;
2812 END IF;
2813 END IF;
2814 END LOOP;
2815 END Change_Sub_Resource;
2816
2817
2818 Procedure check_res_sched_subgroup (p_group_id number,
2819 p_wip_entity_id number,
2820 p_organization_id number,
2821 p_substitution_type number,
2822 p_operation_seq_num number,
2823 p_resource_seq_num number,
2824 p_schedule_seq_num number,
2825 p_substitute_group_num number,
2826 p_replacement_group_num number) IS
2827 cursor sched_rows is
2828 select *
2829 from wip_job_dtls_interface wjdi
2830 where wjdi.group_id = p_group_id
2831 and wjdi.process_phase = wip_constants.ml_validation
2832 and wjdi.process_status in (wip_constants.running,
2833 wip_constants.warning)
2834 and wjdi.wip_entity_id = p_wip_entity_id
2835 and wjdi.organization_id = p_organization_id
2836 and wjdi.load_type = wip_job_details.wip_resource
2837 and wjdi.substitution_type = p_substitution_type
2838 and wjdi.operation_seq_num = p_operation_seq_num
2839 and wjdi.resource_seq_num = p_resource_seq_num
2840 and ( (wjdi.schedule_seq_num = p_schedule_seq_num)
2841 or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
2842 and ( (wjdi.substitute_group_num = p_substitute_group_num)
2843 or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
2844 and ( (wjdi.replacement_group_num = p_replacement_group_num)
2845 or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
2846 for update;
2847
2848 status VARCHAR2(1);
2849 sim_exists BOOLEAN;
2850 sched_seq NUMBER;
2851 sub_group NUMBER;
2852 repl_group NUMBER;
2853 l_res_sub number := 0;
2854 l_dummy2 VARCHAR2(1);
2855 l_params wip_logger.param_tbl_t;
2856 BEGIN
2857
2858 for sched_row in sched_rows loop
2859
2860 if ((sched_row.substitute_group_num <= 0) OR
2861 (sched_row.schedule_seq_num <= 0) OR
2862 (sched_row.replacement_group_num <= 0 AND
2863 p_substitution_type = WIP_JOB_DETAILS.WIP_ADD)) then
2864
2865 /*Bug 5227753 - Added AND condition above for p_substitution_type in replacement group check*/
2866 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SCHED_SUB');
2867 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
2868 if(wip_job_details.std_alone = 1) then
2869 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
2870 p_text => substr(fnd_message.get,1,500),
2871 p_error_type => wip_jdi_utils.msg_error);
2872 else
2873 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2874 p_text => substr(fnd_message.get,1,500),
2875 p_error_type => wip_jdi_utils.msg_error);
2876 end if;
2877
2878 update wip_job_dtls_interface
2879 set process_status = wip_constants.error
2880 where current of sched_rows;
2881 return;
2882 end if;
2883
2884 if (sched_row.substitution_type = wip_job_details.wip_add) then
2885 if (sched_row.substitute_group_num is not null) then
2886 begin
2887 select distinct replacement_group_num
2888 into repl_group
2889 from wip_operation_resources
2890 where wip_entity_id = p_wip_entity_id
2891 and repetitive_schedule_id is null
2892 and operation_seq_num = p_operation_seq_num
2893 and substitute_group_num = sched_row.substitute_group_num;
2894 exception
2895 when no_data_found then
2896 repl_group := 1;
2897 end;
2898
2899 update wip_job_dtls_interface
2900 set replacement_group_num = repl_group
2901 where current of sched_rows;
2902
2903 else
2904 update wip_job_dtls_interface
2905 set replacement_group_num = null
2906 where current of sched_rows;
2907 end if;
2908
2909 elsif (sched_row.substitution_type = wip_job_details.wip_change) then
2910
2911 begin
2912 select schedule_seq_num,
2913 substitute_group_num,
2914 replacement_group_num
2915 into sched_seq,
2916 sub_group,
2917 repl_group
2918 from wip_operation_resources
2919 where wip_entity_id = p_wip_entity_id
2920 and repetitive_schedule_id is null
2921 and operation_seq_num = p_operation_seq_num
2922 and resource_seq_num = p_resource_seq_num;
2923 exception
2924 WHEN NO_DATA_FOUND THEN
2925 select schedule_seq_num,
2926 substitute_group_num,
2927 replacement_group_num
2928 into sched_seq,
2929 sub_group,
2930 repl_group
2931 from wip_sub_operation_resources
2932 where wip_entity_id = p_wip_entity_id
2933 and repetitive_schedule_id is null
2934 and operation_seq_num = p_operation_seq_num
2935 and resource_seq_num = p_resource_seq_num;
2936 end;
2937
2938 if (sched_row.schedule_seq_num is null) then
2939 update wip_job_dtls_interface
2940 set schedule_seq_num = sched_seq
2941 where current of sched_rows;
2942 end if;
2943
2944 if (sched_row.substitute_group_num = fnd_api.g_miss_num) then
2945
2946 update wip_job_dtls_interface
2947 set replacement_group_num = fnd_api.g_miss_num
2948 where current of sched_rows;
2949 elsif (sched_row.substitute_group_num is not null) then
2950
2951 l_res_sub := 0;
2952
2953 if (sched_row.substitute_group_num = sub_group) then
2954
2955 -- check if this is a resource substitution; if it is, leave it alone
2956 begin
2957 select 1
2958 into l_res_sub
2959 from dual
2960 where exists (select 1
2961 from wip_sub_operation_resources
2962 where wip_entity_id = p_wip_entity_id
2963 and operation_seq_num = p_operation_seq_num
2964 and substitute_group_num = sub_group
2965 and nvl(replacement_group_num,0) = nvl(sched_row.replacement_group_num,0));/*Bug 13045227*/
2966 exception
2967 when others then
2968 null;
2969 end;
2970 end if;
2971 if (l_res_sub = 0) then
2972
2973 begin
2974 select distinct replacement_group_num
2975 into repl_group
2976 from wip_operation_resources
2977 where wip_entity_id = p_wip_entity_id
2978 and repetitive_schedule_id is null
2979 and operation_seq_num = p_operation_seq_num
2980 and substitute_group_num = sched_row.substitute_group_num;
2981 exception
2982 when no_data_found then
2983 repl_group := 1;
2984 end;
2985
2986 update wip_job_dtls_interface
2987 set replacement_group_num = repl_group
2988 where current of sched_rows;
2989 end if;
2990 else
2991 update wip_job_dtls_interface
2992 set replacement_group_num = repl_group,
2993 substitute_group_num = sub_group
2994 where current of sched_rows;
2995 end if;
2996 end if;
2997 end loop;
2998 end check_res_sched_subgroup;
2999
3000 Procedure check_sub_sched_subgroup (p_group_id number,
3001 p_wip_entity_id number,
3002 p_organization_id number,
3003 p_substitution_type number,
3004 p_operation_seq_num number,
3005 p_resource_seq_num number,
3006 p_schedule_seq_num number,
3007 p_substitute_group_num number,
3008 p_replacement_group_num number) IS
3009 cursor sched_rows is
3010 select *
3011 from wip_job_dtls_interface wjdi
3012 where wjdi.group_id = p_group_id
3013 and wjdi.process_phase = wip_constants.ml_validation
3014 and wjdi.process_status in (wip_constants.running,
3015 wip_constants.warning)
3016 and wjdi.wip_entity_id = p_wip_entity_id
3017 and wjdi.organization_id = p_organization_id
3018 and wjdi.load_type = wip_job_details.wip_sub_res
3019 and wjdi.substitution_type = p_substitution_type
3020 and wjdi.operation_seq_num = p_operation_seq_num
3021 and wjdi.resource_seq_num = p_resource_seq_num
3022 and ( (wjdi.schedule_seq_num = p_schedule_seq_num)
3023 or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
3024 and ( (wjdi.substitute_group_num = p_substitute_group_num)
3025 or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
3026 and ( (wjdi.replacement_group_num = p_replacement_group_num)
3027 or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
3028 for update;
3029
3030 status VARCHAR2(1);
3031 sim_exists BOOLEAN;
3032 sched_seq NUMBER;
3033 sub_group NUMBER;
3034 repl_group NUMBER;
3035 p_count NUMBER;
3036 begin
3037 for sched_row in sched_rows loop
3038 if ((sched_row.substitute_group_num <= 0) OR
3039 (sched_row.schedule_seq_num <= 0) OR
3040 (sched_row.replacement_group_num <= 0 AND
3041 p_substitution_type = WIP_JOB_DETAILS.WIP_ADD)) then
3042 /*Bug 5227753 - Added AND condition above for p_substitution_type in replacement group check*/
3043 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SCHED_SUB');
3044 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
3045 if(wip_job_details.std_alone = 1) then
3046 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
3047 p_text => substr(fnd_message.get,1,500),
3048 p_error_type => wip_jdi_utils.msg_error);
3049 else
3050 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3051 p_text => substr(fnd_message.get,1,500),
3052 p_error_type => wip_jdi_utils.msg_error);
3053 end if;
3054
3055 update wip_job_dtls_interface
3056 set process_status = wip_constants.error
3057 where current of sched_rows;
3058
3059 return;
3060 end if;
3061
3062 if (sched_row.substitution_type = wip_job_details.wip_add) then
3063 if (sched_row.substitute_group_num is null) then
3064 fnd_message.set_name('WIP', 'WIP_JDI_ALT_SUB_MISSING');
3065 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
3066 if(wip_job_details.std_alone = 1) then
3067 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
3068 p_text => substr(fnd_message.get,1,500),
3069 p_error_type => wip_jdi_utils.msg_error);
3070 else
3071 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3072 p_text => substr(fnd_message.get,1,500),
3073 p_error_type => wip_jdi_utils.msg_error);
3074 end if;
3075
3076 update wip_job_dtls_interface
3077 set process_status = wip_constants.error
3078 where current of sched_rows;
3079
3080 return;
3081 end if;
3082 elsif (sched_row.substitution_type = wip_job_details.wip_change) then
3083 select schedule_seq_num,
3084 substitute_group_num,
3085 replacement_group_num
3086 into sched_seq,
3087 sub_group,
3088 repl_group
3089 from wip_sub_operation_resources
3090 where wip_entity_id = p_wip_entity_id
3091 and repetitive_schedule_id is null
3092 and operation_seq_num = p_operation_seq_num
3093 and resource_seq_num = p_resource_seq_num;
3094
3095 if (sched_row.schedule_seq_num is null) then
3096 update wip_job_dtls_interface
3097 set schedule_seq_num = sched_seq
3098 where current of sched_rows;
3099 end if;
3100
3101 if (sched_row.substitute_group_num = fnd_api.g_miss_num) then
3102 -- not allowed to erase the sub/repl group of an existing alt res
3103 update wip_job_dtls_interface
3104 set substitute_group_num = sub_group,
3105 replacement_group_num = repl_group
3106 where current of sched_rows;
3107 elsif (sched_row.substitute_group_num is not null) then
3108 if ((sched_row.substitute_group_num <> sub_group) AND
3109 (sched_row.replacement_group_num is null)) then
3110 fnd_message.set_name('WIP', 'WIP_JDI_ALT_SUB_MISSING');
3111 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
3112 if(wip_job_details.std_alone = 1) then
3113 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
3114 p_text => substr(fnd_message.get,1,500),
3115 p_error_type => wip_jdi_utils.msg_error);
3116 else
3117 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3118 p_text => substr(fnd_message.get,1,500),
3119 p_error_type => wip_jdi_utils.msg_error);
3120 end if;
3121
3122 update wip_job_dtls_interface
3123 set process_status = wip_constants.error
3124 where current of sched_rows;
3125
3126 return;
3127 end if;
3128 end if;
3129 end if;
3130 end loop;
3131 end check_sub_sched_subgroup;
3132
3133 Procedure Check_Sub_Groups (p_group_id NUMBER,
3134 p_organization_id NUMBER,
3135 p_wip_entity_id NUMBER) IS
3136 cursor c_invalid_rows (p_operation_seq_num NUMBER) is
3137 select interface_id
3138 from wip_job_dtls_interface wjdi
3139 where wjdi.group_id = p_group_id
3140 and wjdi.process_phase = wip_constants.ml_validation
3141 and wjdi.process_status in (wip_constants.running,
3142 wip_constants.warning)
3143 and wjdi.wip_entity_id = p_wip_entity_id
3144 and wjdi.organization_id = p_organization_id
3145 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
3146 and wjdi.operation_seq_num = p_operation_seq_num;
3147
3148 l_op_seq NUMBER;
3149 l_status VARCHAR2(1);
3150 l_error_msg VARCHAR2(2000);
3151 BEGIN
3152 wip_op_resources_utilities.validate_sub_groups(p_wip_entity_id, null, l_status, l_error_msg, l_op_seq);
3153
3154 if (l_status = fnd_api.g_ret_sts_error) then
3155 for l_inv_row in c_invalid_rows(l_op_seq) loop
3156 if(wip_job_details.std_alone = 1) then
3157 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
3158 p_text => substr(l_error_msg,1,500),
3159 p_error_type => wip_jdi_utils.msg_error);
3160 else
3161 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3162 p_text => substr(l_error_msg,1,500),
3163 p_error_type => wip_jdi_utils.msg_error);
3164 end if;
3165 end loop;
3166
3167 update wip_job_dtls_interface wjdi
3168 set wjdi.process_status = wip_constants.error
3169 where wjdi.group_id = p_group_id
3170 and wjdi.process_phase = wip_constants.ml_validation
3171 and wjdi.process_status in (wip_constants.running,
3172 wip_constants.warning)
3173 and wjdi.wip_entity_id = p_wip_entity_id
3174 and wjdi.organization_id = p_organization_id
3175 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
3176 and wjdi.operation_seq_num = l_op_seq;
3177
3178 return;
3179 end if;
3180
3181 wip_op_resources_utilities.delete_orphaned_alternates(p_wip_entity_id, null, l_status);
3182 END Check_Sub_Groups;
3183
3184 END WIP_RESOURCE_VALIDATIONS;