[Home] [Help]
PACKAGE BODY: APPS.WIP_RESOURCE_VALIDATIONS
Source
1 PACKAGE BODY WIP_RESOURCE_VALIDATIONS AS
2 /* $Header: wiprsvdb.pls 120.7.12000000.4 2007/03/10 02:13:19 ntangjee 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 and wjdi.replacement_group_num is not null
846 )
847 and ( not exists(select 1
848 from bom_resources br
849 where br.resource_id = p_resource_id_new
850 and ( br.disable_date > sysdate
851 or br.disable_date is null)
852 and br.organization_id = p_organization_id)
853 or not exists(select 1
854 from bom_department_resources bdr
855 where bdr.resource_id = p_resource_id_new
856 and bdr.department_id = wo.department_id)
857 );
858
859 l_error_exists boolean := false;
860 begin
861
862 -- Validate when adding resources
863 -- resources to be added must exist in BOM_RESOURCES, not disabled
864 for l_inv_row in c_invalid_rows loop
865 l_error_exists := true;
866 fnd_message.set_name('WIP', 'WIP_INVALID_RESOURCE');
867 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
868 if(wip_job_details.std_alone = 1) then
869 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
870 p_text => substr(fnd_message.get,1,500),
871 p_error_type => wip_jdi_utils.msg_error);
872 else
873 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
874 p_text => substr(fnd_message.get,1,500),
875 p_error_type => wip_jdi_utils.msg_error);
876 end if;
877 end loop;
878
879 if(l_error_exists) then
880 update wip_job_dtls_interface wjdi
881 set process_status = wip_constants.error
882 where wjdi.group_id = p_group_id
883 and wjdi.process_phase = wip_constants.ml_validation
884 and wjdi.process_status in (wip_constants.running,
885 wip_constants.warning)
886 and wjdi.wip_entity_id = p_wip_entity_id
887 and wjdi.organization_id = p_organization_id
888 and wjdi.load_type in (wip_job_details.wip_resource, wjdi.load_type)
889 and wjdi.substitution_type = p_substitution_type
890 and wjdi.operation_seq_num = p_operation_seq_num
891 and wjdi.resource_seq_num = p_resource_seq_num
892 and wjdi.resource_id_new = p_resource_id_new
893 and not ( wjdi.load_type = wip_job_details.wip_resource
894 and wjdi.substitution_type = wip_job_details.wip_change
895 and wjdi.substitute_group_num is not null
896 and wjdi.replacement_group_num is not null
897 )
898 and ( not exists(select 1
899 from bom_resources br
900 where br.resource_id = p_resource_id_new
901 and ( br.disable_date > sysdate
902 or br.disable_date is null)
903 and br.organization_id = p_organization_id)
904 or not exists(select 1
905 from bom_department_resources bdr, wip_operations wo
906 where bdr.resource_id = p_resource_id_new
907 and wo.wip_entity_id = p_wip_entity_id
908 and wo.operation_seq_num = p_operation_seq_num
909 and wo.organization_id = p_organization_id
910 and wo.repetitive_schedule_id is null
911 and bdr.department_id = wo.department_id)
912 );
913 end if;
914 end valid_resource;
915
916
917
918 Procedure Resource_Seq_Num(p_group_id number,
919 p_wip_entity_id number,
920 p_organization_id number,
921 p_substitution_type number,
922 p_operation_seq_num number,
923 p_resource_seq_num number ) IS
924
925 cursor c_invalid_rows is
926 select wjdi.interface_id
927 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
928 where wjdi.group_id = p_group_id
929 and wjdi.process_phase = wip_constants.ml_validation
930 and wjdi.process_status in (wip_constants.running,
931 wip_constants.warning)
932 and wjdi.wip_entity_id = p_wip_entity_id
933 and wjdi.organization_id = p_organization_id
934 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
935 and wjdi.substitution_type = p_substitution_type
936 and wjdi.operation_seq_num = p_operation_seq_num
937 and wjdi.resource_seq_num = p_resource_seq_num
938 and ( resource_seq_num <= 0
939 or exists (select 1
940 from wip_operation_resources wor
941 where wor.wip_entity_id = wjdi.wip_entity_id
942 and wor.organization_id = wjdi.organization_id
943 and wor.operation_seq_num = wjdi.operation_seq_num
944 and wor.resource_seq_num = wjdi.resource_seq_num)
945 or exists (select 1
946 from wip_sub_operation_resources wsor
947 where wsor.wip_entity_id = wjdi.wip_entity_id
948 and wsor.organization_id = wjdi.organization_id
949 and wsor.operation_seq_num = wjdi.operation_seq_num
950 and wsor.resource_seq_num = wjdi.resource_seq_num)
951 )
952 -- Bug#5752548 skip the Resource_Seq_Num validation for setup resources
953 -- inserted by ASCP because all existing setup resources would be deleted
954 -- before adding new setup resource.
955 and wjsi.organization_id = wjdi.organization_id
956 and wjsi.group_id = wjdi.group_id
957 and wjsi.header_id = wjdi.parent_header_id
958 and (wjsi.source_code <> 'MSC' or wjdi.parent_seq_num is null);
959
960 l_error_exists boolean := false;
961 begin
962
963 -- Validate when adding resources
964 -- resource_seq_num must not exist
965 for l_inv_row in c_invalid_rows loop
966 l_error_exists := true;
967 fnd_message.set_name('WIP', 'WIP_JDI_RES_SEQ_NUM_EXIST');
968 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
969 if(wip_job_details.std_alone = 1) then
970 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
971 p_text => substr(fnd_message.get,1,500),
972 p_error_type => wip_jdi_utils.msg_error);
973 else
974 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
975 p_text => substr(fnd_message.get,1,500),
976 p_error_type => wip_jdi_utils.msg_error);
977 end if;
978 end loop;
979
980 if(l_error_exists) then
981 update wip_job_dtls_interface wjdi
982 set process_status = wip_constants.error
983 where wjdi.group_id = p_group_id
984 and wjdi.process_phase = wip_constants.ml_validation
985 and wjdi.process_status in (wip_constants.running,
986 wip_constants.warning)
987 and wjdi.wip_entity_id = p_wip_entity_id
988 and wjdi.organization_id = p_organization_id
989 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
990 and wjdi.substitution_type = p_substitution_type
991 and wjdi.operation_seq_num = p_operation_seq_num
992 and wjdi.resource_seq_num = p_resource_seq_num
993 and ( resource_seq_num <= 0
994 or exists (select 1
995 from wip_operation_resources wor
996 where wor.wip_entity_id = wjdi.wip_entity_id
997 and wor.organization_id = wjdi.organization_id
998 and wor.operation_seq_num = wjdi.operation_seq_num
999 and wor.resource_seq_num = wjdi.resource_seq_num)
1000 );
1001 end if;
1002 end resource_seq_num;
1003
1004
1005
1006 Procedure Usage_Rate_Or_Amount(p_group_id number,
1007 p_wip_entity_id number,
1008 p_organization_id number,
1009 p_substitution_type number,
1010 p_operation_seq_num number,
1011 p_resource_seq_num number,
1012 p_resource_id_new number,
1013 p_usage_rate_or_amount number) IS
1014
1015 --the logic for invalid rows is actually in pl/sql for this procedure. This cursor
1016 --just selects all the rows that meet the parameter criteria.
1017 cursor c_invalid_rows is
1018 select interface_id, wjdi.usage_rate_or_amount
1019 from wip_job_dtls_interface wjdi
1020 where wjdi.group_id = p_group_id
1021 and wjdi.process_phase = wip_constants.ml_validation
1022 and wjdi.process_status in (wip_constants.running,
1023 wip_constants.warning)
1024 and wjdi.wip_entity_id = p_wip_entity_id
1025 and wjdi.organization_id = p_organization_id
1026 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1027 and wjdi.substitution_type = p_substitution_type
1028 and wjdi.operation_seq_num = p_operation_seq_num
1029 and wjdi.resource_seq_num = p_resource_seq_num
1030 and wjdi.resource_id_new = p_resource_id_new
1031 -- jy: no need to do this validation if doing res substitution
1032 and not ( wjdi.load_type = wip_job_details.wip_resource
1033 and wjdi.substitution_type = wip_job_details.wip_change
1034 and wjdi.substitute_group_num is not null
1035 and wjdi.replacement_group_num is not null
1036 );
1037
1038 l_error_exists boolean := false;
1039 l_hour_uom varchar2(50);
1040 l_hour_uom_class varchar2(200);
1041 l_uom_time_class_flag boolean;
1042 l_uom_class varchar2(10);
1043 l_Autocharge_Type number(38);
1044 begin
1045 l_hour_uom := FND_PROFILE.value('BOM:HOUR_UOM_CODE');
1046 l_hour_uom_class := wip_op_resources_utilities.get_uom_class(l_hour_uom);
1047
1048 select uom.uom_class
1049 into l_uom_class
1050 from bom_resources br, mtl_units_of_measure_vl uom
1051 where br.resource_id = p_resource_id_new
1052 and br.unit_of_measure = uom.uom_code;
1053
1054 if l_hour_uom_class = l_uom_class then
1055 l_uom_time_class_flag := true;
1056 else
1057 l_uom_time_class_flag := false;
1058 end if;
1059
1060 select autocharge_type
1061 into l_autocharge_type
1062 from bom_resources
1063 where resource_id = p_resource_id_new;
1064
1065 if(l_autocharge_type is null) then
1066
1067 for l_inv_row in c_invalid_rows loop
1068 l_error_exists := true;
1069 fnd_message.set_name('WIP', 'WIP_JDI_NULL_CHARGE_TYPE');
1070 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1071 if(wip_job_details.std_alone = 1) then
1072 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1073 p_text => substr(fnd_message.get,1,500),
1074 p_error_type => wip_jdi_utils.msg_error);
1075 else
1076 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1077 p_text => substr(fnd_message.get,1,500),
1078 p_error_type => wip_jdi_utils.msg_error);
1079 end if;
1080 end loop;
1081
1082 if(l_error_exists) then
1083 update wip_job_dtls_interface wjdi
1084 set process_status = wip_constants.error
1085 where wjdi.group_id = p_group_id
1086 and wjdi.process_phase = wip_constants.ml_validation
1087 and wjdi.process_status in (wip_constants.running,
1088 wip_constants.warning)
1089 and wjdi.wip_entity_id = p_wip_entity_id
1090 and wjdi.organization_id = p_organization_id
1091 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1092 and wjdi.substitution_type = p_substitution_type
1093 and wjdi.operation_seq_num = p_operation_seq_num
1094 and wjdi.resource_seq_num = p_resource_seq_num
1095 and wjdi.resource_id_new = p_resource_id_new
1096 and not ( wjdi.load_type = wip_job_details.wip_resource
1097 and wjdi.substitution_type = wip_job_details.wip_change
1098 and wjdi.substitute_group_num is not null
1099 and wjdi.replacement_group_num is not null
1100 );
1101
1102 return;
1103 end if;
1104 elsif (p_usage_rate_or_amount < 0 and
1105 (l_autocharge_type in (3,4) or l_uom_time_class_flag)) then
1106 for l_inv_row in c_invalid_rows loop
1107 if(l_inv_row.usage_rate_or_amount = p_usage_rate_or_amount) then
1108 l_error_exists := true;
1109 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_RATE');
1110 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1111 if(wip_job_details.std_alone = 1) then
1112 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1113 p_text => substr(fnd_message.get,1,500),
1114 p_error_type => wip_jdi_utils.msg_error);
1115 else
1116 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1117 p_text => substr(fnd_message.get,1,500),
1118 p_error_type => wip_jdi_utils.msg_error);
1119 end if;
1120 end if;
1121 end loop;
1122
1123 if(l_error_exists) then
1124 update wip_job_dtls_interface wjdi
1125 set process_status = wip_constants.error
1126 where wjdi.group_id = p_group_id
1127 and wjdi.process_phase = wip_constants.ml_validation
1128 and wjdi.process_status in (wip_constants.running,
1129 wip_constants.warning)
1130 and wjdi.wip_entity_id = p_wip_entity_id
1131 and wjdi.organization_id = p_organization_id
1132 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1133 and wjdi.substitution_type = p_substitution_type
1134 and wjdi.operation_seq_num = p_operation_seq_num
1135 and wjdi.resource_seq_num = p_resource_seq_num
1136 and wjdi.resource_id_new = p_resource_id_new
1137 and wjdi.usage_rate_or_amount = p_usage_rate_or_amount
1138 and not ( wjdi.load_type = wip_job_details.wip_resource
1139 and wjdi.substitution_type = wip_job_details.wip_change
1140 and wjdi.substitute_group_num is not null
1141 and wjdi.replacement_group_num is not null
1142 );
1143 end if;
1144 end if;
1145 end usage_rate_or_amount;
1146
1147 Procedure Assigned_Units(p_group_id number,
1148 p_wip_entity_id number,
1149 p_organization_id number,
1150 p_load_type number,
1151 p_substitution_type number,
1152 p_operation_seq_num number,
1153 p_resource_seq_num number) is
1154
1155 l_error_exists boolean := false;
1156 l_maximum_assigned_units number;
1157
1158 cursor c_invalid_rows is
1159 select interface_id
1160 from wip_job_dtls_interface wjdi
1161 where wjdi.group_id = p_group_id
1162 and wjdi.process_phase = wip_constants.ml_validation
1163 and wjdi.process_status in (
1164 wip_constants.running,
1165 wip_constants.warning)
1166 and wjdi.wip_entity_id = p_wip_entity_id
1167 and wjdi.organization_id = p_organization_id
1168 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1169 and wjdi.substitution_type = p_substitution_type
1170 and wjdi.operation_seq_num = p_operation_seq_num
1171 and wjdi.resource_seq_num = p_resource_seq_num
1172 and (wjdi.assigned_units <= 0
1173 or (wjdi.assigned_units is null
1174 and wjdi.substitution_type = wip_job_details.wip_add)
1175 or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
1176 and (wjdi.setup_id is not null or exists
1177 (select 1
1178 from bom_resources br
1179 where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1180 and br.batchable = 1
1181 ))
1182 )
1183 -- Bug 5172555
1184 -- The maximum_assigned_units should not be validated
1185 -- So commented out the clause
1186 -- ntungare Thu May 11 05:59:01 PDT 2006
1187 --
1188 -- or (wjdi.assigned_units >
1189 -- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
1190 );
1191
1192 begin
1193 if (p_substitution_type = wip_job_details.wip_change) then
1194 if (p_load_type = WIP_JOB_DETAILS.WIP_RESOURCE) then
1195 select maximum_assigned_units
1196 into l_maximum_assigned_units
1197 from wip_operation_resources
1198 where wip_entity_id = p_wip_entity_id
1199 and organization_id = p_organization_id
1200 and operation_seq_num = p_operation_seq_num
1201 and resource_seq_num = p_resource_seq_num;
1202 elsif (p_load_type = WIP_JOB_DETAILS.WIP_SUB_RES) then
1203 select maximum_assigned_units
1204 into l_maximum_assigned_units
1205 from wip_sub_operation_resources
1206 where wip_entity_id = p_wip_entity_id
1207 and organization_id = p_organization_id
1208 and operation_seq_num = p_operation_seq_num
1209 and resource_seq_num = p_resource_seq_num;
1210 end if;
1211 end if;
1212
1213 for l_inv_row in c_invalid_rows loop
1214 l_error_exists := true;
1215 fnd_message.set_name('WIP', 'WIP_INV_ASSIGNED_UNITS');
1216 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1217 if(wip_job_details.std_alone = 1) then
1218 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1219 p_text => substr(fnd_message.get,1,500),
1220 p_error_type => wip_jdi_utils.msg_error);
1221 else
1222 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1223 p_text => substr(fnd_message.get,1,500),
1224 p_error_type => wip_jdi_utils.msg_error);
1225 end if;
1226 end loop;
1227
1228 if(l_error_exists) then
1229 update wip_job_dtls_interface wjdi
1230 set wjdi.process_status = wip_constants.error
1231 where wjdi.group_id = p_group_id
1232 and wjdi.process_phase = wip_constants.ml_validation
1233 and wjdi.process_status in (wip_constants.running,
1234 wip_constants.warning)
1235 and wjdi.wip_entity_id = p_wip_entity_id
1236 and wjdi.organization_id = p_organization_id
1237 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1238 and wjdi.substitution_type = p_substitution_type
1239 and (wjdi.assigned_units <= 0
1240 or (wjdi.assigned_units is null
1241 and wjdi.substitution_type = wip_job_details.wip_add)
1242 or (wjdi.assigned_units is not null and wjdi.assigned_units <> 1
1243 and (wjdi.setup_id is not null or exists
1244 (select 1
1245 from bom_resources br
1246 where br.resource_id = nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1247 ))
1248 )
1249 -- Bug 5172555
1250 -- The maximum_assigned_units should not be validated
1251 -- So commented out the clause
1252 -- ntungare Thu May 11 05:59:01 PDT 2006
1253 --
1254 -- or (wjdi.assigned_units >
1255 -- nvl(wjdi.maximum_assigned_units, l_maximum_assigned_units))
1256 );
1257 end if;
1258
1259 end Assigned_Units;
1260
1261 Procedure Derive_Resource( p_group_id number,
1262 p_wip_entity_id number,
1263 p_organization_id number,
1264 p_substitution_type number,
1265 p_operation_seq_num number,
1266 p_resource_seq_num in out nocopy number,
1267 p_schedule_seq_num in number,
1268 p_parent_seq_num in number,
1269 p_rowid rowid,
1270 p_err_code out nocopy varchar2,
1271 p_err_msg out nocopy varchar2) is
1272 x_setup_id number := null;
1273 x_res_seq_num_max number := null;
1274 x_res_seq_num number := null;
1275 x_schedule_seq_num number;
1276 begin
1277
1278 if (p_substitution_type = wip_job_details.WIP_ADD) then
1279 -- default res_seq_num to be max existing res_seq_num + 10
1280 if (p_resource_seq_num is null) then
1281 begin
1282 select nvl(max(resource_seq_num), 0)
1283 into x_res_seq_num_max
1284 from wip_operation_resources
1285 where wip_entity_id = p_wip_entity_id
1286 AND organization_id = p_organization_id
1287 AND operation_seq_num = p_operation_seq_num;
1288 exception
1289 when no_data_found then
1290 x_res_seq_num_max := 0;
1291 end;
1292
1293 begin
1294 select nvl(max(resource_seq_num), 0)
1295 into x_res_seq_num
1296 from WIP_JOB_DTLS_INTERFACE
1297 where group_id = p_group_id
1298 and wip_entity_id = p_wip_entity_id
1299 and organization_id = p_organization_id
1300 and operation_seq_num = p_operation_seq_num
1301 and substitution_type = p_substitution_type;
1302 exception
1303 when no_data_found then
1304 x_res_seq_num := 0;
1305 end;
1306
1307 if x_res_seq_num_max < x_res_seq_num then
1308 x_res_seq_num_max := x_res_seq_num;
1309 end if;
1310
1311 x_res_seq_num_max := x_res_seq_num_max + 10;
1312
1313 UPDATE WIP_JOB_DTLS_INTERFACE
1314 SET resource_seq_num = x_res_seq_num_max
1315 WHERE rowid = p_rowid;
1316
1317 p_resource_seq_num := x_res_seq_num_max;
1318
1319 end if;
1320 /* Bug 4747951. For setup resource, get the schedule_seq_num
1321 of parent resource from interface if it exists since this
1322 will be the latest */
1323 if (p_parent_seq_num is not null) then
1324 begin
1325 begin
1326 select schedule_seq_num
1327 into x_schedule_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 resource_seq_num = p_parent_seq_num
1334 and load_type = 1
1335 and substitution_type in (2,3);
1336 exception
1337 when others then x_schedule_seq_num := 0;
1338 end;
1339 if (x_schedule_seq_num = 0) then
1340 begin
1341 select schedule_seq_num
1342 into x_schedule_seq_num
1343 from wip_operation_resources
1344 where wip_entity_id = p_wip_entity_id
1345 AND organization_id = p_organization_id
1346 AND operation_seq_num = p_operation_seq_num
1347 AND resource_seq_num = p_parent_seq_num;
1348 exception
1349 when no_data_found then
1350 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1351 end;
1352 end if;
1353 end;
1354 UPDATE WIP_JOB_DTLS_INTERFACE
1355 SET schedule_seq_num = x_schedule_seq_num
1356 WHERE rowid = p_rowid;
1357
1358 end if;
1359 elsif (p_substitution_type = wip_job_details.WIP_CHANGE) then
1360 begin
1361 select setup_id
1362 into x_setup_id
1363 from wip_operation_resources
1364 where wip_entity_id = p_wip_entity_id
1365 AND organization_id = p_organization_id
1366 AND operation_seq_num = p_operation_seq_num
1367 AND resource_seq_num = p_resource_seq_num;
1368 exception
1369 when no_data_found then
1370 return;
1371 end;
1372
1373 UPDATE WIP_JOB_DTLS_INTERFACE
1374 SET setup_id = nvl(setup_id, x_setup_id)
1375 WHERE rowid = p_rowid;
1376
1377 end if;
1378
1379 exception
1380 when others then
1381 p_err_msg := 'WIPRSVDB.pls<Procedure derive_resource>:' || SQLERRM;
1382 p_err_code := SQLCODE;
1383
1384 end Derive_Resource;
1385
1386
1387 /* main procedure to add resource, call the above */
1388 Procedure Add_Resource(p_group_id number,
1389 p_wip_entity_id number,
1390 p_organization_id number,
1391 p_substitution_type number) IS
1392
1393 x_err_code varchar2(30) := null;
1394 x_err_msg varchar2(240) := NULL;
1395
1396 CURSOR res_info (p_group_id number,
1397 p_wip_entity_id number,
1398 p_organization_id number,
1399 p_substitution_type number) IS
1400 SELECT distinct operation_seq_num,
1401 resource_seq_num, resource_id_old, resource_id_new,
1402 usage_rate_or_amount,
1403 last_update_date, last_updated_by, creation_date, created_by,
1404 last_update_login, request_id, program_application_id,
1405 program_id, program_update_date,
1406 scheduled_flag, assigned_units, applied_resource_units,
1407 applied_resource_value, uom_code, basis_type,
1408 activity_id, autocharge_type, standard_rate_flag,
1409 start_date, completion_date,attribute_category, attribute1,
1410 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1411 attribute8,attribute9,attribute10,attribute11,attribute12,
1412 attribute13,attribute14,attribute15, schedule_seq_num,
1413 substitute_group_num, replacement_group_num, parent_seq_num, rowid
1414 FROM WIP_JOB_DTLS_INTERFACE
1415 WHERE group_id = p_group_id
1416 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1417 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1418 AND wip_entity_id = p_wip_entity_id
1419 AND organization_id = p_organization_id
1420 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1421 AND substitution_type = p_substitution_type;
1422
1423 BEGIN
1424
1425 FOR cur_row IN res_info(p_group_id,
1426 p_wip_entity_id,
1427 p_organization_id,
1428 p_substitution_type) LOOP
1429
1430 derive_resource(p_group_id,
1431 p_wip_entity_id,
1432 p_organization_id,
1433 p_substitution_type,
1434 cur_row.operation_seq_num,
1435 cur_row.resource_seq_num,
1436 cur_row.schedule_seq_num,
1437 cur_row.parent_seq_num,
1438 cur_row.rowid,
1439 x_err_code,
1440 x_err_msg);
1441
1442 Add_Res_Info_Exist(p_group_id,
1443 p_wip_entity_id,
1444 p_organization_id,
1445 p_substitution_type,
1446 cur_row.operation_seq_num);
1447
1448 IF Info_Missing(p_group_id,
1449 p_wip_entity_id,
1450 p_organization_id,
1451 p_substitution_type,
1452 cur_row.operation_seq_num) = 0 THEN
1453
1454 Valid_Resource(p_group_id,
1455 p_wip_entity_id,
1456 p_organization_id,
1457 p_substitution_type,
1458 cur_row.operation_seq_num,
1459 cur_row.resource_seq_num,
1460 cur_row.resource_id_new);
1461
1462 IF IS_Error(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)= 0 THEN
1468
1469 val_add_res_dates(p_group_id,
1470 p_wip_entity_id,
1471 p_organization_id,
1472 p_substitution_type,
1473 cur_row.operation_seq_num,
1474 cur_row.resource_seq_num,
1475 cur_row.resource_id_new);
1476
1477 IF IS_Error(p_group_id,
1478 p_wip_entity_id,
1479 p_organization_id,
1480 p_substitution_type,
1481 cur_row.operation_seq_num,
1482 cur_row.resource_seq_num)= 0 THEN
1483
1484 Resource_Seq_Num(p_group_id,
1485 p_wip_entity_id,
1486 p_organization_id,
1487 p_substitution_type,
1488 cur_row.operation_seq_num,
1489 cur_row.resource_seq_num);
1490
1491 IF IS_Error(p_group_id,
1492 p_wip_entity_id,
1493 p_organization_id,
1494 p_substitution_type,
1495 cur_row.operation_seq_num,
1496 cur_row.resource_seq_num)= 0 THEN
1497
1498 Usage_Rate_Or_Amount(p_group_id,
1499 p_wip_entity_id,
1500 p_organization_id,
1501 p_substitution_type,
1502 cur_row.operation_seq_num,
1503 cur_row.resource_seq_num,
1504 cur_row.resource_id_new,
1505 cur_row.usage_rate_or_amount);
1506
1507 IF IS_Error(p_group_id,
1508 p_wip_entity_id,
1509 p_organization_id,
1510 p_substitution_type,
1511 cur_row.operation_seq_num,
1512 cur_row.resource_seq_num)= 0 THEN
1513
1514 Assigned_Units(p_group_id,
1515 p_wip_entity_id,
1516 p_organization_id,
1517 WIP_JOB_DETAILS.WIP_RESOURCE,
1518 p_substitution_type,
1519 cur_row.operation_seq_num,
1520 cur_row.resource_seq_num);
1521
1522 IF IS_Error(p_group_id,
1523 p_wip_entity_id,
1524 p_organization_id,
1525 p_substitution_type,
1526 cur_row.operation_seq_num,
1527 cur_row.resource_seq_num) = 0 THEN
1528
1529 Check_res_sched_subgroup (p_group_id,
1530 p_wip_entity_id,
1531 p_organization_id,
1532 p_substitution_type,
1533 cur_row.operation_seq_num,
1534 cur_row.resource_seq_num,
1535 cur_row.schedule_seq_num,
1536 cur_row.substitute_group_num,
1537 cur_row.replacement_group_num);
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 WIP_RESOURCE_DEFAULT.Default_Resource(
1546 p_group_id,
1547 p_wip_entity_id,
1548 p_organization_id,
1549 p_substitution_type,
1550 cur_row.operation_seq_num,
1551 cur_row.resource_seq_num,
1552 cur_row.resource_id_new,
1553 x_err_code,
1554 x_err_msg);
1555 END IF;
1556 END IF;
1557 END IF;
1558 END IF;
1559 END IF;
1560 END IF;
1561 END IF;
1562 END LOOP;
1563 END Add_Resource;
1564
1565 /* check for valid assigned units when changing resource assign units, it must be either
1566 equal to number of resource instances unless if no resource instance is defined
1567 */
1568 Procedure Validate_Assigned_Units(p_group_id number,
1569 p_wip_entity_id number,
1570 p_organization_id number,
1571 p_substitution_type number,
1572 p_operation_seq_num number,
1573 p_resource_seq_num number) IS
1574
1575 l_error_exists boolean := false;
1576 l_count number;
1577
1578 cursor c_invalid_rows is
1579 select interface_id
1580 from wip_job_dtls_interface wjdi
1581 where wjdi.group_id = p_group_id
1582 and wjdi.process_phase = wip_constants.ml_validation
1583 and wjdi.process_status in (wip_constants.running,
1584 wip_constants.warning)
1585 and wjdi.wip_entity_id = p_wip_entity_id
1586 and wjdi.organization_id = p_organization_id
1587 and wjdi.load_type = wip_job_details.wip_resource
1588 and wjdi.substitution_type = p_substitution_type
1589 and wjdi.operation_seq_num = p_operation_seq_num
1590 and wjdi.resource_seq_num = p_resource_seq_num
1591 and (wjdi.assigned_units < 0 or
1592 (nvl(wjdi.assigned_units,-1) <> l_count and l_count > 0));
1593
1594 BEGIN
1595 l_count := 0;
1596 begin
1597 select count(*) into l_count
1598 from wip_op_resource_instances
1599 where wip_entity_id = p_wip_entity_id
1600 and organization_id = p_organization_id
1601 and operation_seq_num = p_operation_seq_num
1602 and resource_seq_num = p_resource_seq_num;
1603 exception
1604 when no_data_found then
1605 null;
1606 end;
1607
1608 for l_inv_row in c_invalid_rows loop
1609 l_error_exists := true;
1610 fnd_message.set_name('WIP', 'WIP_ASSIGNED_UNITS_ERROR');
1611 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1612 if(wip_job_details.std_alone = 1) then
1613 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1614 p_text => substr(fnd_message.get,1,500),
1615 p_error_type => wip_jdi_utils.msg_error);
1616 else
1617 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1618 p_text => substr(fnd_message.get,1,500),
1619 p_error_type => wip_jdi_utils.msg_error);
1620 end if;
1621 end loop;
1622 if(l_error_exists) then
1623 update wip_job_dtls_interface wjdi
1624 set process_status = wip_constants.error
1625 where wjdi.group_id = p_group_id
1626 and wjdi.process_phase = wip_constants.ml_validation
1627 and wjdi.process_status in (wip_constants.running,
1628 wip_constants.warning)
1629 and wjdi.wip_entity_id = p_wip_entity_id
1630 and wjdi.organization_id = p_organization_id
1631 and wjdi.load_type in (wip_job_details.wip_resource)
1632 and wjdi.substitution_type = p_substitution_type
1633 and wjdi.operation_seq_num = p_operation_seq_num
1634 and wjdi.resource_seq_num = p_resource_seq_num
1635 and (wjdi.assigned_units < 0 or
1636 (wjdi.assigned_units <> l_count and l_count > 0));
1637 end if;
1638 END Validate_Assigned_Units;
1639
1640 Procedure Chng_Res_Info_Exist(p_group_id number,
1641 p_wip_entity_id number,
1642 p_organization_id number,
1643 p_substitution_type number,
1644 p_operation_seq_num number) IS
1645 cursor c_invalid_rows is
1646 select interface_id
1647 from wip_job_dtls_interface wjdi
1648 where wjdi.group_id = p_group_id
1649 and wjdi.process_phase = wip_constants.ml_validation
1650 and wjdi.process_status in (wip_constants.running,
1651 wip_constants.warning)
1652 and wjdi.wip_entity_id = p_wip_entity_id
1653 and wjdi.organization_id = p_organization_id
1654 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1655 and wjdi.substitution_type = p_substitution_type
1656 and wjdi.operation_seq_num = p_operation_seq_num
1657 and ( wjdi.resource_seq_num is null
1658 or wjdi.resource_id_old is null
1659 or wjdi.resource_id_new is null
1660 or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1661 and wjdi.usage_rate_or_amount is null
1662 )
1663 );
1664
1665 l_error_exists boolean := false;
1666 BEGIN
1667 /* we don't check up to usage_rate_or_amount since we assume up to
1668 resource_id_old and resource_id_new, it should be unique */
1669 for l_inv_row in c_invalid_rows loop
1670 l_error_exists := true;
1671 fnd_message.set_name('WIP', 'WIP_JDI_CHNG_RES_INFO_MISSING');
1672 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1673 if(wip_job_details.std_alone = 1) then
1674 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1675 p_text => substr(fnd_message.get,1,500),
1676 p_error_type => wip_jdi_utils.msg_error);
1677 else
1678 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1679 p_text => substr(fnd_message.get,1,500),
1680 p_error_type => wip_jdi_utils.msg_error);
1681 end if;
1682 end loop;
1683
1684 if(l_error_exists) then
1685 update wip_job_dtls_interface wjdi
1686 set process_status = wip_constants.error
1687 where wjdi.group_id = p_group_id
1688 and wjdi.process_phase = wip_constants.ml_validation
1689 and wjdi.process_status in (wip_constants.running,
1690 wip_constants.warning)
1691 and wjdi.wip_entity_id = p_wip_entity_id
1692 and wjdi.organization_id = p_organization_id
1693 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
1694 and wjdi.substitution_type = p_substitution_type
1695 and wjdi.operation_seq_num = p_operation_seq_num
1696 and ( wjdi.resource_seq_num is null
1697 or wjdi.resource_id_old is null
1698 or wjdi.resource_id_new is null
1699 or ( wjdi.resource_id_old <> nvl(wjdi.resource_id_new, wjdi.resource_id_old)
1700 and wjdi.usage_rate_or_amount is null
1701 )
1702 );
1703 end if;
1704 END Chng_Res_Info_Exist;
1705
1706 /* jy - Checks the validity of rows that change resources. There are two types of
1707 transactions. The first is to just change an existing resource in
1708 wip_operation_resources. The second is to substitute a
1709 resource in WOR with one in WSOR. To indicate a substitution:
1710 1) The substitution_group_num column must be either null or have the valid
1711 value for that res_seq_num/id.
1712 2) The replacement_group_num must be a valid value in WSOR (if it is the
1713 same as the one in WOR then nothing happens).
1714 First, we try to determine if a record is a valid substitution. If it contains
1715 wrong values for sub/repl group, it errors. If it doesn't contain wrong
1716 information but is not a substitution, we clear the two columns and validate it for a normal resource change.
1717 Note that with this validation, we don't allow users to change the sub/repl
1718 group of a resource through the dtls interface table.
1719 */
1720
1721 Procedure Change_Resource(p_group_id number,
1722 p_wip_entity_id number,
1723 p_organization_id number,
1724 p_substitution_type number) IS
1725
1726 x_err_code number := 0;
1727 x_err_msg varchar2(240) := NULL;
1728 l_sub_group_num number;
1729 l_repl_group_num number;
1730
1731 CURSOR res_info (p_group_id number,
1732 p_wip_entity_id number,
1733 p_organization_id number,
1734 p_substitution_type number) IS
1735 SELECT distinct operation_seq_num,
1736 resource_seq_num, resource_id_old, resource_id_new,
1737 usage_rate_or_amount,
1738 last_update_date, last_updated_by, creation_date, created_by,
1739 last_update_login, request_id, program_application_id,
1740 program_id, program_update_date,
1741 scheduled_flag, assigned_units, applied_resource_units,
1742 applied_resource_value, uom_code, basis_type,
1743 activity_id, autocharge_type, standard_rate_flag,
1744 start_date, completion_date,attribute_category, attribute1,
1745 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1746 attribute8,attribute9,attribute10,attribute11,attribute12,
1747 attribute13,attribute14,attribute15,
1748 schedule_seq_num, substitute_group_num,
1749 replacement_group_num, parent_seq_num, rowid
1750 FROM WIP_JOB_DTLS_INTERFACE
1751 WHERE group_id = p_group_id
1752 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1753 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1754 AND wip_entity_id = p_wip_entity_id
1755 AND organization_id = p_organization_id
1756 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1757 AND substitution_type = p_substitution_type;
1758
1759 BEGIN
1760 FOR cur_row IN res_info(p_group_id,
1761 p_wip_entity_id,
1762 p_organization_id,
1763 p_substitution_type) LOOP
1764
1765 derive_resource(p_group_id,
1766 p_wip_entity_id,
1767 p_organization_id,
1768 p_substitution_type,
1769 cur_row.operation_seq_num,
1770 cur_row.resource_seq_num,
1771 cur_row.schedule_seq_num,
1772 cur_row.parent_seq_num,
1773 cur_row.rowid,
1774 x_err_code,
1775 x_err_msg);
1776
1777 Chng_Res_Info_Exist(p_group_id,
1778 p_wip_entity_id,
1779 p_organization_id,
1780 p_substitution_type,
1781 cur_row.operation_seq_num);
1782
1783 IF Info_Missing(p_group_id,
1784 p_wip_entity_id,
1785 p_organization_id,
1786 p_substitution_type,
1787 cur_row.operation_seq_num) = 0 THEN
1788 RES_JOB_Match (p_group_id,
1789 p_wip_entity_id,
1790 p_organization_id,
1791 p_substitution_type,
1792 cur_row.operation_seq_num,
1793 cur_row.resource_seq_num,
1794 cur_row.resource_id_old);
1795
1796 IF IS_Error(p_group_id,
1797 p_wip_entity_id,
1798 p_organization_id,
1799 p_substitution_type,
1800 cur_row.operation_seq_num,
1801 cur_row.resource_seq_num) = 0 THEN
1802 /* fix for bug# 2043593 */
1803 If (cur_row.resource_id_old <> cur_row.resource_id_new) then
1804 Safe_Delete (p_group_id,
1805 p_wip_entity_id,
1806 p_organization_id,
1807 p_substitution_type,
1808 cur_row.operation_seq_num,
1809 cur_row.resource_seq_num,
1810 cur_row.resource_id_old);
1811 End if;
1812
1813 IF IS_Error(p_group_id,
1814 p_wip_entity_id,
1815 p_organization_id,
1816 p_substitution_type,
1817 cur_row.operation_seq_num,
1818 cur_row.resource_seq_num) = 0 THEN
1819 Safe_PO (p_group_id,
1820 p_wip_entity_id,
1821 p_organization_id,
1822 p_substitution_type,
1823 cur_row.operation_seq_num,
1824 cur_row.resource_seq_num,
1825 cur_row.resource_id_old);
1826
1827 IF IS_Error(p_group_id,
1828 p_wip_entity_id,
1829 p_organization_id,
1830 p_substitution_type,
1831 cur_row.operation_seq_num,
1832 cur_row.resource_seq_num) = 0 THEN
1833 Valid_Resource(p_group_id,
1834 p_wip_entity_id,
1835 p_organization_id,
1836 p_substitution_type,
1837 cur_row.operation_seq_num,
1838 cur_row.resource_seq_num,
1839 cur_row.resource_id_new);
1840
1841 IF IS_Error(p_group_id,
1842 p_wip_entity_id,
1843 p_organization_id,
1844 p_substitution_type,
1845 cur_row.operation_seq_num,
1846 cur_row.resource_seq_num)= 0 THEN
1847
1848 val_change_res_dates(p_group_id,
1849 p_wip_entity_id,
1850 p_organization_id,
1851 p_substitution_type,
1852 cur_row.operation_seq_num,
1853 cur_row.resource_seq_num,
1854 cur_row.resource_id_old);
1855
1856 IF IS_Error(p_group_id,
1857 p_wip_entity_id,
1858 p_organization_id,
1859 p_substitution_type,
1860 cur_row.operation_seq_num,
1861 cur_row.resource_seq_num) = 0 THEN
1862
1863 Usage_Rate_Or_Amount(p_group_id,
1864 p_wip_entity_id,
1865 p_organization_id,
1866 p_substitution_type,
1867 cur_row.operation_seq_num,
1868 cur_row.resource_seq_num,
1869 cur_row.resource_id_new,
1870 cur_row.usage_rate_or_amount);
1871
1872 IF IS_Error(p_group_id,
1873 p_wip_entity_id,
1874 p_organization_id,
1875 p_substitution_type,
1876 cur_row.operation_seq_num,
1877 cur_row.resource_seq_num)= 0 THEN
1878
1879 Assigned_Units(p_group_id,
1880 p_wip_entity_id,
1881 p_organization_id,
1882 WIP_JOB_DETAILS.WIP_RESOURCE,
1883 p_substitution_type,
1884 cur_row.operation_seq_num,
1885 cur_row.resource_seq_num);
1886
1887 IF IS_Error(p_group_id,
1888 p_wip_entity_id,
1889 p_organization_id,
1890 p_substitution_type,
1891 cur_row.operation_seq_num,
1892 cur_row.resource_seq_num) = 0 THEN
1893 Check_res_sched_subgroup (p_group_id,
1894 p_wip_entity_id,
1895 p_organization_id,
1896 p_substitution_type,
1897 cur_row.operation_seq_num,
1898 cur_row.resource_seq_num,
1899 cur_row.schedule_seq_num,
1900 cur_row.substitute_group_num,
1901 cur_row.replacement_group_num);
1902
1903 IF IS_Error(p_group_id,
1904 p_wip_entity_id,
1905 p_organization_id,
1906 p_substitution_type,
1907 cur_row.operation_seq_num,
1908 cur_row.resource_seq_num)= 0 THEN
1909 Validate_Assigned_Units(p_group_id,
1910 p_wip_entity_id,
1911 p_organization_id,
1912 p_substitution_type,
1913 cur_row.operation_seq_num,
1914 cur_row.resource_seq_num);
1915
1916 IF IS_Error(p_group_id,
1917 p_wip_entity_id,
1918 p_organization_id,
1919 p_substitution_type,
1920 cur_row.operation_seq_num,
1921 cur_row.resource_seq_num) = 0 THEN
1922 WIP_RESOURCE_DEFAULT.Default_Resource(
1923 p_group_id,
1924 p_wip_entity_id,
1925 p_organization_id,
1926 p_substitution_type,
1927 cur_row.operation_seq_num,
1928 cur_row.resource_seq_num,
1929 cur_row.resource_id_new,
1930 x_err_code,
1931 x_err_msg);
1932 END IF;
1933 END IF;
1934 END IF;
1935 END IF;
1936 END IF;
1937 END IF;
1938 END IF;
1939 END IF;
1940 END IF;
1941 END IF;
1942 END LOOP;
1943 END Change_Resource;
1944
1945 function IS_Error(p_group_id number,
1946 p_wip_entity_id number,
1947 p_organization_id number,
1948 p_substitution_type number,
1949 p_operation_seq_num number,
1950 p_resource_seq_num number) return number IS
1951
1952 x_count number := 0;
1953
1954 BEGIN
1955
1956 SELECT count(*)
1957 INTO x_count
1958 FROM WIP_JOB_DTLS_INTERFACE
1959 WHERE group_id = p_group_id
1960 AND process_status = WIP_CONSTANTS.ERROR
1961 AND wip_entity_id = p_wip_entity_id
1962 AND organization_id = p_organization_id
1963 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1964 AND substitution_type= p_substitution_type
1965 AND operation_seq_num= p_operation_seq_num
1966 AND resource_seq_num = p_resource_seq_num;
1967
1968
1969 IF x_count <> 0 THEN
1970 return 1;
1971 ELSE return 0;
1972 END IF;
1973
1974 END IS_Error;
1975
1976
1977 function Info_Missing(p_group_id number,
1978 p_wip_entity_id number,
1979 p_organization_id number,
1980 p_substitution_type number,
1981 p_operation_seq_num number) return number IS
1982
1983 x_count number := 0;
1984
1985 BEGIN
1986
1987 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
1988 SELECT count(*)
1989 INTO x_count
1990 FROM WIP_JOB_DTLS_INTERFACE
1991 WHERE group_id = p_group_id
1992 AND process_status = WIP_CONSTANTS.ERROR
1993 AND wip_entity_id = p_wip_entity_id
1994 AND organization_id = p_organization_id
1995 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
1996 AND substitution_type= p_substitution_type
1997 AND operation_seq_num= p_operation_seq_num
1998 AND (resource_seq_num IS NULL
1999 OR resource_id_old IS NULL);
2000
2001 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
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_id_new IS NULL
2013 OR usage_rate_or_amount IS NULL);
2014
2015 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
2016 SELECT count(*)
2017 INTO x_count
2018 FROM WIP_JOB_DTLS_INTERFACE
2019 WHERE group_id = p_group_id
2020 AND process_status = WIP_CONSTANTS.ERROR
2021 AND wip_entity_id = p_wip_entity_id
2022 AND organization_id = p_organization_id
2023 AND load_type = WIP_JOB_DETAILS.WIP_RESOURCE
2024 AND substitution_type= p_substitution_type
2025 AND operation_seq_num= p_operation_seq_num
2026 AND (resource_seq_num IS NULL
2027 OR resource_id_old IS NULL
2028 OR resource_id_new IS NULL
2029 OR usage_rate_or_amount IS NULL);
2030
2031 END IF;
2032
2033 IF x_count <> 0 THEN
2034 return 1;
2035 ELSE return 0;
2036 END IF;
2037
2038 END Info_Missing;
2039
2040
2041
2042 Procedure Delete_Sub_Resource (p_group_id number,
2043 p_wip_entity_id number,
2044 p_organization_id number,
2045 p_substitution_type number) IS
2046
2047 CURSOR res_info (p_group_id number,
2048 p_wip_entity_id number,
2049 p_organization_id number,
2050 p_substitution_type number) IS
2051 SELECT distinct operation_seq_num,
2052 resource_seq_num, resource_id_old, resource_id_new,
2053 usage_rate_or_amount,
2054 last_update_date, last_updated_by, creation_date, created_by,
2055 last_update_login, request_id, program_application_id,
2056 program_id, program_update_date,
2057 scheduled_flag, assigned_units, applied_resource_units,
2058 applied_resource_value, uom_code, basis_type,
2059 activity_id, autocharge_type, standard_rate_flag,
2060 start_date, completion_date,attribute_category, attribute1,
2061 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2062 attribute8,attribute9,attribute10,attribute11,attribute12,
2063 attribute13,attribute14,attribute15
2064 FROM WIP_JOB_DTLS_INTERFACE
2065 WHERE group_id = p_group_id
2066 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2067 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2068 AND wip_entity_id = p_wip_entity_id
2069 AND organization_id = p_organization_id
2070 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2071 AND substitution_type = p_substitution_type;
2072
2073 BEGIN
2074 FOR cur_row IN res_info(p_group_id,
2075 p_wip_entity_id,
2076 p_organization_id,
2077 p_substitution_type) LOOP
2078
2079 Del_Res_Info_Exist(p_group_id,
2080 p_wip_entity_id,
2081 p_organization_id,
2082 p_substitution_type,
2083 cur_row.operation_seq_num);
2084
2085 IF Info_Missing(p_group_id,
2086 p_wip_entity_id,
2087 p_organization_id,
2088 p_substitution_type,
2089 cur_row.operation_seq_num) = 0 THEN
2090
2091 RES_JOB_Match (p_group_id,
2092 p_wip_entity_id,
2093 p_organization_id,
2094 p_substitution_type,
2095 cur_row.operation_seq_num,
2096 cur_row.resource_seq_num,
2097 cur_row.resource_id_old);
2098
2099 END IF;
2100 END LOOP;
2101 END Delete_Sub_Resource;
2102
2103 /* jy - checks for a valid resource substitution row. */
2104 Procedure Check_Res_Substitution(p_group_id number,
2105 p_wip_entity_id number,
2106 p_organization_id number,
2107 p_substitution_type number,
2108 p_operation_seq_num number,
2109 p_resource_seq_num number,
2110 p_resource_id_old number) IS
2111
2112 cursor c_invalid_rows is
2113 select interface_id
2114 from wip_job_dtls_interface wjdi
2115 where wjdi.group_id = p_group_id
2116 and wjdi.process_phase = wip_constants.ml_validation
2117 and wjdi.process_status in (wip_constants.running,
2118 wip_constants.warning)
2119 and wjdi.wip_entity_id = p_wip_entity_id
2120 and wjdi.organization_id = p_organization_id
2121 and wjdi.load_type = wip_job_details.wip_resource
2122 and wjdi.substitution_type = p_substitution_type
2123 and wjdi.operation_seq_num = p_operation_seq_num
2124 and wjdi.resource_seq_num = p_resource_seq_num
2125 and wjdi.resource_id_old = p_resource_id_old
2126 and ( ( wjdi.substitute_group_num is not null
2127 and not exists (select 1
2128 from wip_operation_resources wor
2129 where wor.wip_entity_id = wjdi.wip_entity_id
2130 and wor.organization_id = wjdi.organization_id
2131 and wor.resource_id = wjdi.resource_id_old
2132 and wor.operation_seq_num = wjdi.operation_seq_num
2133 and wor.resource_seq_num = wjdi.resource_seq_num
2134 and wor.substitute_group_num = wjdi.substitute_group_num
2135 )
2136 )
2137 or ( wjdi.replacement_group_num is not null
2138 and not exists (select 1
2139 from wip_sub_operation_resources wsor,
2140 wip_operation_resources wor
2141 where wsor.wip_entity_id = wjdi.wip_entity_id
2142 and wsor.organization_id = wjdi.organization_id
2143 and wsor.operation_seq_num = wjdi.operation_seq_num
2144 and wor.wip_entity_id = wjdi.wip_entity_id
2145 and wor.organization_id = wjdi.organization_id
2146 and wor.resource_id = wjdi.resource_id_old
2147 and wor.operation_seq_num = wjdi.operation_seq_num
2148 and wor.resource_seq_num = wjdi.resource_seq_num
2149 and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
2150 and wsor.replacement_group_num = wjdi.replacement_group_num
2151 )
2152 and not exists (select 1
2153 from wip_operation_resources wor
2154 where wor.wip_entity_id = wjdi.wip_entity_id
2155 and wor.organization_id = wjdi.organization_id
2156 and wor.resource_id = wjdi.resource_id_old
2157 and wor.operation_seq_num = wjdi.operation_seq_num
2158 and wor.resource_seq_num = wjdi.resource_seq_num
2159 and ( (wor.substitute_group_num =
2160 nvl(wjdi.substitute_group_num,wor.substitute_group_num)) OR
2161 (wor.substitute_group_num is null and
2162 wjdi.substitute_group_num is null)
2163 )
2164 and wor.replacement_group_num = wjdi.replacement_group_num
2165 )
2166 )
2167 );
2168
2169 cursor c_not_sub_rows is
2170 select wjdi.substitute_group_num,
2171 wjdi.replacement_group_num
2172 from wip_job_dtls_interface wjdi
2173 where wjdi.group_id = p_group_id
2174 and wjdi.process_phase = wip_constants.ml_validation
2175 and wjdi.process_status in (wip_constants.running,
2176 wip_constants.warning)
2177 and wjdi.wip_entity_id = p_wip_entity_id
2178 and wjdi.organization_id = p_organization_id
2179 and wjdi.load_type = wip_job_details.wip_resource
2180 and wjdi.substitution_type = p_substitution_type
2181 and wjdi.operation_seq_num = p_operation_seq_num
2182 and wjdi.resource_seq_num = p_resource_seq_num
2183 and wjdi.resource_id_old = p_resource_id_old
2184 and ( wjdi.replacement_group_num is null
2185 or ( wjdi.replacement_group_num is not null
2186 and exists (select 1
2187 from wip_operation_resources wor
2188 where wor.wip_entity_id = wjdi.wip_entity_id
2189 and wor.organization_id = wjdi.organization_id
2190 and wor.resource_id = wjdi.resource_id_old
2191 and wor.operation_seq_num = wjdi.operation_seq_num
2192 and wor.resource_seq_num = wjdi.resource_seq_num
2193 and ( (wor.substitute_group_num =
2194 nvl(wjdi.substitute_group_num, wor.substitute_group_num)) OR
2195 (wor.substitute_group_num is null and
2196 wjdi.substitute_group_num is null)
2197 )
2198 and wor.replacement_group_num = wjdi.replacement_group_num
2199 )
2200 )
2201 )
2202 for update;
2203
2204 cursor c_sub_rows is
2205 select wip_entity_id,
2206 organization_id,
2207 resource_id_old,
2208 operation_seq_num,
2209 resource_seq_num,
2210 substitute_group_num
2211 from wip_job_dtls_interface
2212 where group_id = p_group_id
2213 and process_phase = wip_constants.ml_validation
2214 and process_status in (wip_constants.running,
2215 wip_constants.warning)
2216 and wip_entity_id = p_wip_entity_id
2217 and organization_id = p_organization_id
2218 and load_type = wip_job_details.wip_resource
2219 and substitution_type = p_substitution_type
2220 and operation_seq_num = p_operation_seq_num
2221 and resource_seq_num = p_resource_seq_num
2222 and resource_id_old = p_resource_id_old
2223 and replacement_group_num is not null
2224 for update;
2225
2226 l_error_exists boolean := false;
2227 l_sub_group_temp number;
2228
2229
2230 BEGIN
2231
2232 for l_inv_row in c_invalid_rows loop
2233 l_error_exists := true;
2234 fnd_message.set_name('WIP','WIP_JDI_RES_SUB_INFO_MISSING' );
2235 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
2236 if(wip_job_details.std_alone = 1) then
2237 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
2238 p_text => substr(fnd_message.get,1,500),
2239 p_error_type => wip_jdi_utils.msg_error);
2240 else
2241 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2242 p_text => substr(fnd_message.get,1,500),
2243 p_error_type => wip_jdi_utils.msg_error);
2244 end if;
2245 end loop;
2246
2247 if(l_error_exists) then
2248 update wip_job_dtls_interface wjdi
2249 set process_status = wip_constants.error
2250 where wjdi.group_id = p_group_id
2251 and wjdi.process_phase = wip_constants.ml_validation
2252 and wjdi.process_status in (wip_constants.running,
2253 wip_constants.warning)
2254 and wjdi.wip_entity_id = p_wip_entity_id
2255 and wjdi.organization_id = p_organization_id
2256 and wjdi.load_type = wip_job_details.wip_resource
2257 and wjdi.substitution_type = p_substitution_type
2258 and wjdi.operation_seq_num = p_operation_seq_num
2259 and wjdi.resource_seq_num = p_resource_seq_num
2260 and wjdi.resource_id_old = p_resource_id_old
2261 and ( ( wjdi.substitute_group_num is not null
2262 and not exists (select 1
2263 from wip_operation_resources wor
2264 where wor.wip_entity_id = wjdi.wip_entity_id
2265 and wor.organization_id = wjdi.organization_id
2266 and wor.resource_id = wjdi.resource_id_old
2267 and wor.operation_seq_num = wjdi.operation_seq_num
2268 and wor.resource_seq_num = wjdi.resource_seq_num
2269 and wor.substitute_group_num = wjdi.substitute_group_num
2270 )
2271 )
2272 or ( wjdi.replacement_group_num is not null
2273 and not exists (select 1
2274 from wip_sub_operation_resources wsor,
2275 wip_operation_resources wor
2276 where wsor.wip_entity_id = wjdi.wip_entity_id
2277 and wsor.organization_id = wjdi.organization_id
2278 and wsor.operation_seq_num = wjdi.operation_seq_num
2279 and wor.wip_entity_id = wjdi.wip_entity_id
2280 and wor.organization_id = wjdi.organization_id
2281 and wor.resource_id = wjdi.resource_id_old
2282 and wor.operation_seq_num = wjdi.operation_seq_num
2283 and wor.resource_seq_num = wjdi.resource_seq_num
2284 and wsor.substitute_group_num = nvl(wjdi.substitute_group_num, wor.substitute_group_num)
2285 and wsor.replacement_group_num = wjdi.replacement_group_num
2286 )
2287 )
2288 );
2289 RETURN;
2290 end if;
2291
2292 for l_ns_row in c_not_sub_rows loop
2293 update wip_job_dtls_interface wjdi
2294 set substitute_group_num = null,
2295 replacement_group_num = null
2296 where current of c_not_sub_rows;
2297 end loop;
2298
2299 for l_sub_row in c_sub_rows loop
2300 if l_sub_row.substitute_group_num is null then
2301 select wor.substitute_group_num
2302 into l_sub_group_temp
2303 from wip_operation_resources wor
2304 where wor.wip_entity_id = l_sub_row.wip_entity_id
2305 and wor.organization_id = l_sub_row.organization_id
2306 and wor.resource_id = l_sub_row.resource_id_old
2307 and wor.operation_seq_num = l_sub_row.operation_seq_num
2308 and wor.resource_seq_num = l_sub_row.resource_seq_num;
2309 update wip_job_dtls_interface
2310 set substitute_group_num = l_sub_group_temp
2311 where current of c_sub_rows;
2312 end if;
2313 end loop;
2314
2315 END Check_Res_Substitution;
2316
2317 Procedure Substitute_Info (p_group_id number,
2318 p_wip_entity_id number,
2319 p_organization_id number,
2320 p_substitution_type number,
2321 p_operation_seq_num number,
2322 p_resource_seq_num number) IS
2323 cursor c_invalid_rows is
2324 select interface_id
2325 from wip_job_dtls_interface wjdi
2326 where wjdi.group_id = p_group_id
2327 and wjdi.process_phase = wip_constants.ml_validation
2328 and wjdi.process_status in (wip_constants.running,
2329 wip_constants.warning)
2330 and wjdi.wip_entity_id = p_wip_entity_id
2331 and wjdi.organization_id = p_organization_id
2332 and wjdi.load_type = wip_job_details.wip_sub_res
2333 and wjdi.substitution_type = p_substitution_type
2334 and wjdi.operation_seq_num = p_operation_seq_num
2335 and wjdi.resource_seq_num = p_resource_seq_num
2336 and ( wjdi.schedule_seq_num < 0
2337 or wjdi.substitute_group_num is null
2338 or wjdi.substitute_group_num < 0
2339 or wjdi.replacement_group_num is null
2340 or wjdi.replacement_group_num < 0
2341 or not exists (select 1
2342 from wip_operation_resources wor
2343 where wor.wip_entity_id = wjdi.wip_entity_id
2344 and wor.organization_id = wjdi.organization_id
2345 and wor.operation_seq_num = wjdi.operation_seq_num
2346 and wor.substitute_group_num = wjdi.substitute_group_num)
2347 or exists (select 1
2348 from wip_operation_resources wor
2349 where wor.wip_entity_id = wjdi.wip_entity_id
2350 and wor.organization_id = wjdi.organization_id
2351 and wor.operation_seq_num = wjdi.operation_seq_num
2352 and wor.substitute_group_num = wjdi.substitute_group_num
2353 and wor.replacement_group_num = wjdi.replacement_group_num)
2354 );
2355
2356 l_error_exists boolean := false;
2357 begin
2358
2359 for l_inv_row in c_invalid_rows loop
2360 l_error_exists := true;
2361 fnd_message.set_name('WIP', 'WIP_JDI_RES_SUB_INFO_MISSING');
2362 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
2363 if(wip_job_details.std_alone = 1) then
2364 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
2365 p_text => substr(fnd_message.get,1,500),
2366 p_error_type => wip_jdi_utils.msg_error);
2367 else
2368 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2369 p_text => substr(fnd_message.get,1,500),
2370 p_error_type => wip_jdi_utils.msg_error);
2371 end if;
2372 end loop;
2373
2374 if(l_error_exists) then
2375 update wip_job_dtls_interface wjdi
2376 set process_status = wip_constants.error
2377 where wjdi.group_id = p_group_id
2378 and wjdi.process_phase = wip_constants.ml_validation
2379 and wjdi.process_status in (wip_constants.running,
2380 wip_constants.warning)
2381 and wjdi.wip_entity_id = p_wip_entity_id
2382 and wjdi.organization_id = p_organization_id
2383 and wjdi.load_type = wip_job_details.wip_sub_res
2384 and wjdi.substitution_type = p_substitution_type
2385 and wjdi.operation_seq_num = p_operation_seq_num
2386 and wjdi.resource_seq_num = p_resource_seq_num
2387 and ( wjdi.schedule_seq_num is null
2388 or wjdi.schedule_seq_num < 0
2389 or wjdi.substitute_group_num is null
2390 or wjdi.substitute_group_num < 0
2391 or wjdi.replacement_group_num is null
2392 or wjdi.replacement_group_num < 0
2393 or not exists (select 1
2394 from wip_operation_resources wor
2395 where wor.wip_entity_id = wjdi.wip_entity_id
2396 and wor.organization_id = wjdi.organization_id
2397 and wor.operation_seq_num = wjdi.operation_seq_num
2398 and wor.substitute_group_num = wjdi.substitute_group_num)
2399 or exists (select 1
2400 from wip_operation_resources wor
2401 where wor.wip_entity_id = wjdi.wip_entity_id
2402 and wor.organization_id = wjdi.organization_id
2403 and wor.operation_seq_num = wjdi.operation_seq_num
2404 and wor.substitute_group_num = wjdi.substitute_group_num
2405 and wor.replacement_group_num = wjdi.replacement_group_num)
2406 );
2407 end if;
2408 end substitute_info;
2409
2410
2411
2412 Procedure Add_Sub_Resource(p_group_id number,
2413 p_wip_entity_id number,
2414 p_organization_id number,
2415 p_substitution_type number) IS
2416
2417 x_err_code varchar2(30) := null;
2418 x_err_msg varchar2(240) := NULL;
2419
2420 CURSOR res_info (p_group_id number,
2421 p_wip_entity_id number,
2422 p_organization_id number,
2423 p_substitution_type number) IS
2424 SELECT distinct operation_seq_num,
2425 resource_seq_num, resource_id_old, resource_id_new,
2426 usage_rate_or_amount,
2427 last_update_date, last_updated_by, creation_date, created_by,
2428 last_update_login, request_id, program_application_id,
2429 program_id, program_update_date,
2430 scheduled_flag, assigned_units, applied_resource_units,
2431 applied_resource_value, uom_code, basis_type,
2432 activity_id, autocharge_type, standard_rate_flag,
2433 start_date, completion_date,attribute_category, attribute1,
2434 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2435 attribute8,attribute9,attribute10,attribute11,attribute12,
2436 attribute13,attribute14,attribute15,schedule_seq_num,
2437 substitute_group_num, replacement_group_num, parent_seq_num, rowid
2438 FROM WIP_JOB_DTLS_INTERFACE
2439 WHERE group_id = p_group_id
2440 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2441 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2442 AND wip_entity_id = p_wip_entity_id
2443 AND organization_id = p_organization_id
2444 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2445 AND substitution_type = p_substitution_type;
2446
2447
2448 BEGIN
2449 FOR cur_row IN res_info(p_group_id,
2450 p_wip_entity_id,
2451 p_organization_id,
2452 p_substitution_type) LOOP
2453
2454 derive_resource(p_group_id,
2455 p_wip_entity_id,
2456 p_organization_id,
2457 p_substitution_type,
2458 cur_row.operation_seq_num,
2459 cur_row.resource_seq_num,
2460 cur_row.schedule_seq_num,
2461 cur_row.parent_seq_num,
2462 cur_row.rowid,
2463 x_err_code,
2464 x_err_msg);
2465
2466 Add_Res_Info_Exist(p_group_id,
2467 p_wip_entity_id,
2468 p_organization_id,
2469 p_substitution_type,
2470 cur_row.operation_seq_num);
2471
2472 IF Info_Missing(p_group_id,
2473 p_wip_entity_id,
2474 p_organization_id,
2475 p_substitution_type,
2476 cur_row.operation_seq_num) = 0 THEN
2477
2478 Valid_Resource(p_group_id,
2479 p_wip_entity_id,
2480 p_organization_id,
2481 p_substitution_type,
2482 cur_row.operation_seq_num,
2483 cur_row.resource_seq_num,
2484 cur_row.resource_id_new);
2485
2486 IF IS_Error(p_group_id,
2487 p_wip_entity_id,
2488 p_organization_id,
2489 p_substitution_type,
2490 cur_row.operation_seq_num,
2491 cur_row.resource_seq_num)= 0 THEN
2492
2493 Resource_Seq_Num(p_group_id,
2494 p_wip_entity_id,
2495 p_organization_id,
2496 p_substitution_type,
2497 cur_row.operation_seq_num,
2498 cur_row.resource_seq_num);
2499
2500 IF IS_Error(p_group_id,
2501 p_wip_entity_id,
2502 p_organization_id,
2503 p_substitution_type,
2504 cur_row.operation_seq_num,
2505 cur_row.resource_seq_num)= 0 THEN
2506
2507 Usage_Rate_Or_Amount(p_group_id,
2508 p_wip_entity_id,
2509 p_organization_id,
2510 p_substitution_type,
2511 cur_row.operation_seq_num,
2512 cur_row.resource_seq_num,
2513 cur_row.resource_id_new,
2514 cur_row.usage_rate_or_amount);
2515
2516 IF IS_Error(p_group_id,
2517 p_wip_entity_id,
2518 p_organization_id,
2519 p_substitution_type,
2520 cur_row.operation_seq_num,
2521 cur_row.resource_seq_num)= 0 THEN
2522
2523 Assigned_Units(p_group_id,
2524 p_wip_entity_id,
2525 p_organization_id,
2526 WIP_JOB_DETAILS.WIP_SUB_RES,
2527 p_substitution_type,
2528 cur_row.operation_seq_num,
2529 cur_row.resource_seq_num);
2530
2531 IF IS_Error(p_group_id,
2532 p_wip_entity_id,
2533 p_organization_id,
2534 p_substitution_type,
2535 cur_row.operation_seq_num,
2536 cur_row.resource_seq_num)= 0 THEN
2537
2538 Substitute_Info(p_group_id,
2539 p_wip_entity_id,
2540 p_organization_id,
2541 p_substitution_type,
2542 cur_row.operation_seq_num,
2543 cur_row.resource_seq_num);
2544
2545 IF IS_Error(p_group_id,
2546 p_wip_entity_id,
2547 p_organization_id,
2548 p_substitution_type,
2549 cur_row.operation_seq_num,
2550 cur_row.resource_seq_num) = 0 THEN
2551
2552 Check_sub_sched_subgroup (p_group_id,
2553 p_wip_entity_id,
2554 p_organization_id,
2555 p_substitution_type,
2556 cur_row.operation_seq_num,
2557 cur_row.resource_seq_num,
2558 cur_row.schedule_seq_num,
2559 cur_row.substitute_group_num,
2560 cur_row.replacement_group_num);
2561
2562 IF IS_Error(p_group_id,
2563 p_wip_entity_id,
2564 p_organization_id,
2565 p_substitution_type,
2566 cur_row.operation_seq_num,
2567 cur_row.resource_seq_num) = 0 THEN
2568
2569 WIP_RESOURCE_DEFAULT.Default_Resource(
2570 p_group_id,
2571 p_wip_entity_id,
2572 p_organization_id,
2573 p_substitution_type,
2574 cur_row.operation_seq_num,
2575 cur_row.resource_seq_num,
2576 cur_row.resource_id_new,
2577 x_err_code,
2578 x_err_msg);
2579 END IF;
2580 END IF;
2581 END IF;
2582 END IF;
2583 END IF;
2584 END IF;
2585 END IF;
2586 END LOOP;
2587 END Add_Sub_Resource;
2588
2589
2590 Procedure Change_Sub_Resource(p_group_id number,
2591 p_wip_entity_id number,
2592 p_organization_id number,
2593 p_substitution_type number) IS
2594
2595 x_err_code number := 0;
2596 x_err_msg varchar2(240) := NULL;
2597 CURSOR res_info (p_group_id number,
2598 p_wip_entity_id number,
2599 p_organization_id number,
2600 p_substitution_type number) IS
2601 SELECT distinct operation_seq_num,
2602 resource_seq_num, resource_id_old, resource_id_new,
2603 usage_rate_or_amount,
2604 last_update_date, last_updated_by, creation_date, created_by,
2605 last_update_login, request_id, program_application_id,
2606 program_id, program_update_date,
2607 scheduled_flag, assigned_units, applied_resource_units,
2608 applied_resource_value, uom_code, basis_type,
2609 activity_id, autocharge_type, standard_rate_flag,
2610 start_date, completion_date,attribute_category, attribute1,
2611 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
2612 attribute8,attribute9,attribute10,attribute11,attribute12,
2613 attribute13,attribute14,attribute15,schedule_seq_num,
2614 substitute_group_num, replacement_group_num
2615 FROM WIP_JOB_DTLS_INTERFACE
2616 WHERE group_id = p_group_id
2617 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
2618 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
2619 AND wip_entity_id = p_wip_entity_id
2620 AND organization_id = p_organization_id
2621 AND load_type = WIP_JOB_DETAILS.WIP_SUB_RES
2622 AND substitution_type = p_substitution_type;
2623
2624
2625 BEGIN
2626 FOR cur_row IN res_info(p_group_id,
2627 p_wip_entity_id,
2628 p_organization_id,
2629 p_substitution_type) LOOP
2630
2631 Chng_Res_Info_Exist(p_group_id,
2632 p_wip_entity_id,
2633 p_organization_id,
2634 p_substitution_type,
2635 cur_row.operation_seq_num);
2636
2637 IF Info_Missing(p_group_id,
2638 p_wip_entity_id,
2639 p_organization_id,
2640 p_substitution_type,
2641 cur_row.operation_seq_num) = 0 THEN
2642 RES_JOB_Match (p_group_id,
2643 p_wip_entity_id,
2644 p_organization_id,
2645 p_substitution_type,
2646 cur_row.operation_seq_num,
2647 cur_row.resource_seq_num,
2648 cur_row.resource_id_old);
2649
2650 IF IS_Error(p_group_id,
2651 p_wip_entity_id,
2652 p_organization_id,
2653 p_substitution_type,
2654 cur_row.operation_seq_num,
2655 cur_row.resource_seq_num) = 0 THEN
2656 Safe_Delete (p_group_id,
2657 p_wip_entity_id,
2658 p_organization_id,
2659 p_substitution_type,
2660 cur_row.operation_seq_num,
2661 cur_row.resource_seq_num,
2662 cur_row.resource_id_old);
2663
2664 IF IS_Error(p_group_id,
2665 p_wip_entity_id,
2666 p_organization_id,
2667 p_substitution_type,
2668 cur_row.operation_seq_num,
2669 cur_row.resource_seq_num) = 0 THEN
2670
2671 Valid_Resource(p_group_id,
2672 p_wip_entity_id,
2673 p_organization_id,
2674 p_substitution_type,
2675 cur_row.operation_seq_num,
2676 cur_row.resource_seq_num,
2677 cur_row.resource_id_new);
2678
2679 IF IS_Error(p_group_id,
2680 p_wip_entity_id,
2681 p_organization_id,
2682 p_substitution_type,
2683 cur_row.operation_seq_num,
2684 cur_row.resource_seq_num) = 0 THEN
2685
2686 Substitute_Info(p_group_id,
2687 p_wip_entity_id,
2688 p_organization_id,
2689 p_substitution_type,
2690 cur_row.operation_seq_num,
2691 cur_row.resource_seq_num);
2692
2693 IF IS_Error(p_group_id,
2694 p_wip_entity_id,
2695 p_organization_id,
2696 p_substitution_type,
2697 cur_row.operation_seq_num,
2698 cur_row.resource_seq_num) = 0 THEN
2699
2700 Usage_Rate_Or_Amount(p_group_id,
2701 p_wip_entity_id,
2702 p_organization_id,
2703 p_substitution_type,
2704 cur_row.operation_seq_num,
2705 cur_row.resource_seq_num,
2706 cur_row.resource_id_new,
2707 cur_row.usage_rate_or_amount);
2708
2709 IF IS_Error(p_group_id,
2710 p_wip_entity_id,
2711 p_organization_id,
2712 p_substitution_type,
2713 cur_row.operation_seq_num,
2714 cur_row.resource_seq_num)= 0 THEN
2715
2716 Assigned_Units(p_group_id,
2717 p_wip_entity_id,
2718 p_organization_id,
2719 WIP_JOB_DETAILS.WIP_SUB_RES,
2720 p_substitution_type,
2721 cur_row.operation_seq_num,
2722 cur_row.resource_seq_num);
2723
2724 IF IS_Error(p_group_id,
2725 p_wip_entity_id,
2726 p_organization_id,
2727 p_substitution_type,
2728 cur_row.operation_seq_num,
2729 cur_row.resource_seq_num) = 0 THEN
2730
2731 Check_sub_sched_subgroup (p_group_id,
2732 p_wip_entity_id,
2733 p_organization_id,
2734 p_substitution_type,
2735 cur_row.operation_seq_num,
2736 cur_row.resource_seq_num,
2737 cur_row.schedule_seq_num,
2738 cur_row.substitute_group_num,
2739 cur_row.replacement_group_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 WIP_RESOURCE_DEFAULT.Default_Resource(
2749 p_group_id,
2750 p_wip_entity_id,
2751 p_organization_id,
2752 p_substitution_type,
2753 cur_row.operation_seq_num,
2754 cur_row.resource_seq_num,
2755 cur_row.resource_id_new,
2756 x_err_code,
2757 x_err_msg);
2758 END IF;
2759 END IF;
2760 END IF;
2761 END IF;
2762 END IF;
2763 END IF;
2764 END IF;
2765 END IF;
2766 END LOOP;
2767 END Change_Sub_Resource;
2768
2769
2770 Procedure check_res_sched_subgroup (p_group_id number,
2771 p_wip_entity_id number,
2772 p_organization_id number,
2773 p_substitution_type number,
2774 p_operation_seq_num number,
2775 p_resource_seq_num number,
2776 p_schedule_seq_num number,
2777 p_substitute_group_num number,
2778 p_replacement_group_num number) IS
2779 cursor sched_rows is
2780 select *
2781 from wip_job_dtls_interface wjdi
2782 where wjdi.group_id = p_group_id
2783 and wjdi.process_phase = wip_constants.ml_validation
2784 and wjdi.process_status in (wip_constants.running,
2785 wip_constants.warning)
2786 and wjdi.wip_entity_id = p_wip_entity_id
2787 and wjdi.organization_id = p_organization_id
2788 and wjdi.load_type = wip_job_details.wip_resource
2789 and wjdi.substitution_type = p_substitution_type
2790 and wjdi.operation_seq_num = p_operation_seq_num
2791 and wjdi.resource_seq_num = p_resource_seq_num
2792 and ( (wjdi.schedule_seq_num = p_schedule_seq_num)
2793 or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
2794 and ( (wjdi.substitute_group_num = p_substitute_group_num)
2795 or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
2796 and ( (wjdi.replacement_group_num = p_replacement_group_num)
2797 or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
2798 for update;
2799
2800 status VARCHAR2(1);
2801 sim_exists BOOLEAN;
2802 sched_seq NUMBER;
2803 sub_group NUMBER;
2804 repl_group NUMBER;
2805 l_res_sub number := 0;
2806 BEGIN
2807 for sched_row in sched_rows loop
2808 if ((sched_row.substitute_group_num <= 0) OR
2809 (sched_row.schedule_seq_num <= 0) OR
2810 (sched_row.replacement_group_num <= 0 AND
2811 p_substitution_type = WIP_JOB_DETAILS.WIP_ADD)) then
2812 /*Bug 5227753 - Added AND condition above for p_substitution_type in replacement group check*/
2813 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SCHED_SUB');
2814 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
2815 if(wip_job_details.std_alone = 1) then
2816 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
2817 p_text => substr(fnd_message.get,1,500),
2818 p_error_type => wip_jdi_utils.msg_error);
2819 else
2820 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2821 p_text => substr(fnd_message.get,1,500),
2822 p_error_type => wip_jdi_utils.msg_error);
2823 end if;
2824
2825 update wip_job_dtls_interface
2826 set process_status = wip_constants.error
2827 where current of sched_rows;
2828
2829 return;
2830 end if;
2831
2832 if (sched_row.substitution_type = wip_job_details.wip_add) then
2833 if (sched_row.substitute_group_num is not null) then
2834 begin
2835 select distinct replacement_group_num
2836 into repl_group
2837 from wip_operation_resources
2838 where wip_entity_id = p_wip_entity_id
2839 and repetitive_schedule_id is null
2840 and operation_seq_num = p_operation_seq_num
2841 and substitute_group_num = sched_row.substitute_group_num;
2842 exception
2843 when no_data_found then
2844 repl_group := 1;
2845 end;
2846
2847 update wip_job_dtls_interface
2848 set replacement_group_num = repl_group
2849 where current of sched_rows;
2850 else
2851 update wip_job_dtls_interface
2852 set replacement_group_num = null
2853 where current of sched_rows;
2854 end if;
2855 elsif (sched_row.substitution_type = wip_job_details.wip_change) then
2856 select schedule_seq_num,
2857 substitute_group_num,
2858 replacement_group_num
2859 into sched_seq,
2860 sub_group,
2861 repl_group
2862 from wip_operation_resources
2863 where wip_entity_id = p_wip_entity_id
2864 and repetitive_schedule_id is null
2865 and operation_seq_num = p_operation_seq_num
2866 and resource_seq_num = p_resource_seq_num;
2867
2868 if (sched_row.schedule_seq_num is null) then
2869 update wip_job_dtls_interface
2870 set schedule_seq_num = sched_seq
2871 where current of sched_rows;
2872 end if;
2873
2874 if (sched_row.substitute_group_num = fnd_api.g_miss_num) then
2875 update wip_job_dtls_interface
2876 set replacement_group_num = fnd_api.g_miss_num
2877 where current of sched_rows;
2878 elsif (sched_row.substitute_group_num is not null) then
2879 l_res_sub := 0;
2880
2881 if (sched_row.substitute_group_num = sub_group) then
2882 -- check if this is a resource substitution; if it is, leave it alone
2883 begin
2884 select 1
2885 into l_res_sub
2886 from dual
2887 where exists (select 1
2888 from wip_sub_operation_resources
2889 where wip_entity_id = p_wip_entity_id
2890 and operation_seq_num = p_operation_seq_num
2891 and substitute_group_num = sub_group
2892 and replacement_group_num = sched_row.replacement_group_num);
2893 exception
2894 when others then
2895 null;
2896 end;
2897 end if;
2898
2899 if (l_res_sub = 0) then
2900 begin
2901 select distinct replacement_group_num
2902 into repl_group
2903 from wip_operation_resources
2904 where wip_entity_id = p_wip_entity_id
2905 and repetitive_schedule_id is null
2906 and operation_seq_num = p_operation_seq_num
2907 and substitute_group_num = sched_row.substitute_group_num;
2908 exception
2909 when no_data_found then
2910 repl_group := 1;
2911 end;
2912
2913 update wip_job_dtls_interface
2914 set replacement_group_num = repl_group
2915 where current of sched_rows;
2916 end if;
2917 else
2918 update wip_job_dtls_interface
2919 set replacement_group_num = repl_group,
2920 substitute_group_num = sub_group
2921 where current of sched_rows;
2922 end if;
2923 end if;
2924 end loop;
2925 end check_res_sched_subgroup;
2926
2927 Procedure check_sub_sched_subgroup (p_group_id number,
2928 p_wip_entity_id number,
2929 p_organization_id number,
2930 p_substitution_type number,
2931 p_operation_seq_num number,
2932 p_resource_seq_num number,
2933 p_schedule_seq_num number,
2934 p_substitute_group_num number,
2935 p_replacement_group_num number) IS
2936 cursor sched_rows is
2937 select *
2938 from wip_job_dtls_interface wjdi
2939 where wjdi.group_id = p_group_id
2940 and wjdi.process_phase = wip_constants.ml_validation
2941 and wjdi.process_status in (wip_constants.running,
2942 wip_constants.warning)
2943 and wjdi.wip_entity_id = p_wip_entity_id
2944 and wjdi.organization_id = p_organization_id
2945 and wjdi.load_type = wip_job_details.wip_sub_res
2946 and wjdi.substitution_type = p_substitution_type
2947 and wjdi.operation_seq_num = p_operation_seq_num
2948 and wjdi.resource_seq_num = p_resource_seq_num
2949 and ( (wjdi.schedule_seq_num = p_schedule_seq_num)
2950 or (wjdi.schedule_seq_num is null and p_schedule_seq_num is null))
2951 and ( (wjdi.substitute_group_num = p_substitute_group_num)
2952 or (wjdi.substitute_group_num is null and p_substitute_group_num is null))
2953 and ( (wjdi.replacement_group_num = p_replacement_group_num)
2954 or (wjdi.replacement_group_num is null and p_replacement_group_num is null))
2955 for update;
2956
2957 status VARCHAR2(1);
2958 sim_exists BOOLEAN;
2959 sched_seq NUMBER;
2960 sub_group NUMBER;
2961 repl_group NUMBER;
2962 p_count NUMBER;
2963 begin
2964 for sched_row in sched_rows loop
2965 if ((sched_row.substitute_group_num <= 0) OR
2966 (sched_row.schedule_seq_num <= 0) OR
2967 (sched_row.replacement_group_num <= 0 AND
2968 p_substitution_type = WIP_JOB_DETAILS.WIP_ADD)) then
2969 /*Bug 5227753 - Added AND condition above for p_substitution_type in replacement group check*/
2970 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SCHED_SUB');
2971 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
2972 if(wip_job_details.std_alone = 1) then
2973 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
2974 p_text => substr(fnd_message.get,1,500),
2975 p_error_type => wip_jdi_utils.msg_error);
2976 else
2977 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2978 p_text => substr(fnd_message.get,1,500),
2979 p_error_type => wip_jdi_utils.msg_error);
2980 end if;
2981
2982 update wip_job_dtls_interface
2983 set process_status = wip_constants.error
2984 where current of sched_rows;
2985
2986 return;
2987 end if;
2988
2989 if (sched_row.substitution_type = wip_job_details.wip_add) then
2990 if (sched_row.substitute_group_num is null) then
2991 fnd_message.set_name('WIP', 'WIP_JDI_ALT_SUB_MISSING');
2992 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
2993 if(wip_job_details.std_alone = 1) then
2994 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
2995 p_text => substr(fnd_message.get,1,500),
2996 p_error_type => wip_jdi_utils.msg_error);
2997 else
2998 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
2999 p_text => substr(fnd_message.get,1,500),
3000 p_error_type => wip_jdi_utils.msg_error);
3001 end if;
3002
3003 update wip_job_dtls_interface
3004 set process_status = wip_constants.error
3005 where current of sched_rows;
3006
3007 return;
3008 end if;
3009 elsif (sched_row.substitution_type = wip_job_details.wip_change) then
3010 select schedule_seq_num,
3011 substitute_group_num,
3012 replacement_group_num
3013 into sched_seq,
3014 sub_group,
3015 repl_group
3016 from wip_sub_operation_resources
3017 where wip_entity_id = p_wip_entity_id
3018 and repetitive_schedule_id is null
3019 and operation_seq_num = p_operation_seq_num
3020 and resource_seq_num = p_resource_seq_num;
3021
3022 if (sched_row.schedule_seq_num is null) then
3023 update wip_job_dtls_interface
3024 set schedule_seq_num = sched_seq
3025 where current of sched_rows;
3026 end if;
3027
3028 if (sched_row.substitute_group_num = fnd_api.g_miss_num) then
3029 -- not allowed to erase the sub/repl group of an existing alt res
3030 update wip_job_dtls_interface
3031 set substitute_group_num = sub_group,
3032 replacement_group_num = repl_group
3033 where current of sched_rows;
3034 elsif (sched_row.substitute_group_num is not null) then
3035 if ((sched_row.substitute_group_num <> sub_group) AND
3036 (sched_row.replacement_group_num is null)) then
3037 fnd_message.set_name('WIP', 'WIP_JDI_ALT_SUB_MISSING');
3038 fnd_message.set_token('INTERFACE', to_char(sched_row.interface_id));
3039 if(wip_job_details.std_alone = 1) then
3040 wip_interface_err_Utils.add_error(p_interface_id => sched_row.interface_id,
3041 p_text => substr(fnd_message.get,1,500),
3042 p_error_type => wip_jdi_utils.msg_error);
3043 else
3044 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3045 p_text => substr(fnd_message.get,1,500),
3046 p_error_type => wip_jdi_utils.msg_error);
3047 end if;
3048
3049 update wip_job_dtls_interface
3050 set process_status = wip_constants.error
3051 where current of sched_rows;
3052
3053 return;
3054 end if;
3055 end if;
3056 end if;
3057 end loop;
3058 end check_sub_sched_subgroup;
3059
3060 Procedure Check_Sub_Groups (p_group_id NUMBER,
3061 p_organization_id NUMBER,
3062 p_wip_entity_id NUMBER) IS
3063 cursor c_invalid_rows (p_operation_seq_num NUMBER) is
3064 select interface_id
3065 from wip_job_dtls_interface wjdi
3066 where wjdi.group_id = p_group_id
3067 and wjdi.process_phase = wip_constants.ml_validation
3068 and wjdi.process_status in (wip_constants.running,
3069 wip_constants.warning)
3070 and wjdi.wip_entity_id = p_wip_entity_id
3071 and wjdi.organization_id = p_organization_id
3072 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
3073 and wjdi.operation_seq_num = p_operation_seq_num;
3074
3075 l_op_seq NUMBER;
3076 l_status VARCHAR2(1);
3077 l_error_msg VARCHAR2(2000);
3078 BEGIN
3079 wip_op_resources_utilities.validate_sub_groups(p_wip_entity_id, null, l_status, l_error_msg, l_op_seq);
3080
3081 if (l_status = fnd_api.g_ret_sts_error) then
3082 for l_inv_row in c_invalid_rows(l_op_seq) loop
3083 if(wip_job_details.std_alone = 1) then
3084 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
3085 p_text => substr(l_error_msg,1,500),
3086 p_error_type => wip_jdi_utils.msg_error);
3087 else
3088 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
3089 p_text => substr(l_error_msg,1,500),
3090 p_error_type => wip_jdi_utils.msg_error);
3091 end if;
3092 end loop;
3093
3094 update wip_job_dtls_interface wjdi
3095 set wjdi.process_status = wip_constants.error
3096 where wjdi.group_id = p_group_id
3097 and wjdi.process_phase = wip_constants.ml_validation
3098 and wjdi.process_status in (wip_constants.running,
3099 wip_constants.warning)
3100 and wjdi.wip_entity_id = p_wip_entity_id
3101 and wjdi.organization_id = p_organization_id
3102 and wjdi.load_type in (wip_job_details.wip_resource, wip_job_details.wip_sub_res)
3103 and wjdi.operation_seq_num = l_op_seq;
3104
3105 return;
3106 end if;
3107
3108 wip_op_resources_utilities.delete_orphaned_alternates(p_wip_entity_id, null, l_status);
3109 END Check_Sub_Groups;
3110
3111 END WIP_RESOURCE_VALIDATIONS;