[Home] [Help]
PACKAGE BODY: APPS.WIP_REQUIREMENT_VALIDATIONS
Source
1 PACKAGE BODY WIP_REQUIREMENT_VALIDATIONS AS
2 /* $Header: wiprqvdb.pls 120.8 2006/11/06 23:39:30 ntangjee noship $ */
3
4 /* inventory_item_id_Old must not be null */
5 procedure del_req_info_exist(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) IS
10
11 cursor c_invalid_rows is
12 select interface_id
13 from wip_job_dtls_interface wjdi
14 where wjdi.group_id = p_group_id
15 and wjdi.process_phase = wip_constants.ml_validation
16 and wjdi.process_status in (wip_constants.running,
17 wip_constants.warning)
18 and wjdi.wip_entity_id = p_wip_entity_id
19 and wjdi.organization_id = p_organization_id
20 and wjdi.load_type = wip_job_details.wip_mtl_requirement
21 and wjdi.substitution_type = p_substitution_type
22 and wjdi.operation_seq_num = p_operation_seq_num
23 and wjdi.inventory_item_id_old is null;
24
25 l_error_exists boolean := false;
26 begin
27
28 for l_inv_row in c_invalid_rows loop
29 l_error_exists := true;
30 fnd_message.set_name('WIP', 'WIP_JDI_DEL_REQ_INFO_MISSING');
31 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
32 if(wip_job_details.std_alone = 1) then
33 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
34 p_text => substr(fnd_message.get,1,500),
35 p_error_type => wip_jdi_utils.msg_error);
36 else
37 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
38 p_text => substr(fnd_message.get,1,500),
39 p_error_type => wip_jdi_utils.msg_error);
40 end if;
41 end loop;
42
43 if(l_error_exists) then
44 update wip_job_dtls_interface wjdi
45 set process_status = wip_constants.error
46 where group_id = p_group_id
47 and process_phase = wip_constants.ml_validation
48 and process_status in (wip_constants.running,
49 wip_constants.warning)
50 and wip_entity_id = p_wip_entity_id
51 and organization_id = p_organization_id
52 and wjdi.load_type = wip_job_details.wip_mtl_requirement
53 and wjdi.substitution_type = p_substitution_type
54 and wjdi.operation_seq_num = p_operation_seq_num
55 and wjdi.inventory_item_id_old is null;
56 end if;
57 end del_req_info_exist;
58
59
60
61 /* operations, requirements, etc all match and exist */
62 procedure req_job_match (p_group_id in number,
63 p_wip_entity_id in number,
64 p_organization_id in number,
65 p_substitution_type in number,
66 p_operation_seq_num in number,
67 p_inventory_item_id_old in number) IS
68
69 cursor c_invalid_rows is
70 select wjdi.interface_id,
71 we.wip_entity_name,
72 wjdi.wip_entity_id,
73 wjdi.operation_seq_num,
74 msik.concatenated_segments item_name,
75 wjdi.inventory_item_id_old
76 from wip_job_dtls_interface wjdi,
77 wip_entities we,
78 mtl_system_items_kfv msik
79 where wjdi.group_id = p_group_id
80 and wjdi.process_phase = wip_constants.ml_validation
81 and wjdi.process_status in (wip_constants.running,
82 wip_constants.warning)
83 and wjdi.wip_entity_id = p_wip_entity_id
84 and wjdi.organization_id = p_organization_id
85 and wjdi.load_type = wip_job_details.wip_mtl_requirement
86 and wjdi.substitution_type = p_substitution_type
87 and wjdi.operation_seq_num = p_operation_seq_num
88 and wjdi.inventory_item_id_old = p_inventory_item_id_old
89 and wjdi.wip_entity_id = we.wip_entity_id
90 and wjdi.inventory_item_id_old = msik.inventory_item_id
91 and wjdi.organization_id = msik.organization_id
92 and not exists (select 1
93 from wip_requirement_operations wro
94 where wro.wip_entity_id = wjdi.wip_entity_id
95 and wro.organization_id = wjdi.organization_id
96 and wro.operation_seq_num = wjdi.operation_seq_num
97 and wro.inventory_item_id = wjdi.inventory_item_id_old);
98
99
100 l_error_exists boolean := false;
101 begin
102
103 for l_inv_row in c_invalid_rows loop
104 l_error_exists := true;
105 fnd_message.set_name('WIP', 'WIP_JDI_REQ_NOT_IN_JOB');
106 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
107 fnd_message.set_token('JOB', l_inv_row.wip_entity_name);
108 fnd_message.set_token('WEI', to_char(l_inv_row.wip_entity_id));
109 fnd_message.set_token('OPERATION', to_char(l_inv_row.operation_seq_num));
110 fnd_message.set_token('ITEM', l_inv_row.item_name);
111 fnd_message.set_token('ITEMID', to_char(l_inv_row.inventory_item_id_old));
112 if(wip_job_details.std_alone = 1) then
113 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
114 p_text => substr(fnd_message.get,1,500),
115 p_error_type => wip_jdi_utils.msg_error);
116 else
117 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
118 p_text => substr(fnd_message.get,1,500),
119 p_error_type => wip_jdi_utils.msg_error);
120 end if;
121 end loop;
122
123 if(l_error_exists) then
124 update wip_job_dtls_interface wjdi
125 set process_status = wip_constants.error
126 where group_id = p_group_id
127 and process_phase = wip_constants.ml_validation
128 and process_status in (wip_constants.running,
129 wip_constants.warning)
130 and wip_entity_id = p_wip_entity_id
131 and organization_id = p_organization_id
132 and wjdi.load_type = wip_job_details.wip_mtl_requirement
133 and wjdi.substitution_type = p_substitution_type
134 and wjdi.operation_seq_num = p_operation_seq_num
135 and wjdi.inventory_item_id_old = p_inventory_item_id_old
136 and not exists (select 1
137 from wip_requirement_operations wro
138 where wro.wip_entity_id = wjdi.wip_entity_id
139 and wro.organization_id = wjdi.organization_id
140 and wro.operation_seq_num = wjdi.operation_seq_num
141 and wro.inventory_item_id = wjdi.inventory_item_id_old);
142 end if;
143 end req_job_match;
144
145
146 procedure safe_delete(p_group_id in number,
147 p_wip_entity_id in number,
148 p_organization_id in number,
149 p_substitution_type in number,
150 p_operation_seq_num in number,
151 p_inventory_item_id_old in number) IS
152
153 x_quantity_issued number;
154 x_exist number := 0;
155
156 cursor c_invalid_wro_rows is
157 select interface_id
158 from wip_job_dtls_interface wjdi
159 where wjdi.group_id = p_group_id
160 and wjdi.process_phase = wip_constants.ml_validation
161 and wjdi.process_status in (wip_constants.running,
162 wip_constants.warning)
163 and wjdi.wip_entity_id = p_wip_entity_id
164 and wjdi.organization_id = p_organization_id
165 and wjdi.load_type = wip_job_details.wip_mtl_requirement
166 and wjdi.substitution_type = p_substitution_type
167 and wjdi.operation_seq_num = p_operation_seq_num
168 and wjdi.inventory_item_id_old = p_inventory_item_id_old
169 and exists (select 1
170 from wip_requirement_operations wro
171 where wro.wip_entity_id = wjdi.wip_entity_id
172 and wro.organization_id = wjdi.organization_id
173 and wro.operation_seq_num = wjdi.operation_seq_num
174 and wro.inventory_item_id = wjdi.inventory_item_id_old
175 and wro.quantity_issued <> 0);
176
177 cursor c_invalid_mmtt_rows is
178 select interface_id
179 from wip_job_dtls_interface wjdi
180 where wjdi.group_id = p_group_id
181 and wjdi.process_phase = wip_constants.ml_validation
182 and wjdi.process_status in (wip_constants.running,
183 wip_constants.warning)
184 and wjdi.wip_entity_id = p_wip_entity_id
185 and wjdi.organization_id = p_organization_id
186 and wjdi.load_type = wip_job_details.wip_mtl_requirement
187 and wjdi.substitution_type = p_substitution_type
188 and wjdi.operation_seq_num = p_operation_seq_num
189 and wjdi.inventory_item_id_old = p_inventory_item_id_old
190 and exists (select 1
191 from mtl_material_transactions_temp mmtt
192 where mmtt.transaction_source_id = wjdi.wip_entity_id
193 and mmtt.organization_id = wjdi.organization_id
194 and mmtt.operation_seq_num = wjdi.operation_seq_num
195 and mmtt.inventory_item_id = wjdi.inventory_item_id_old);
196
197 cursor c_invalid_mmt_rows is
198 select interface_id
199 from wip_job_dtls_interface wjdi
200 where wjdi.group_id = p_group_id
201 and wjdi.process_phase = wip_constants.ml_validation
202 and wjdi.process_status in (wip_constants.running,
203 wip_constants.warning)
204 and wjdi.wip_entity_id = p_wip_entity_id
205 and wjdi.organization_id = p_organization_id
206 and wjdi.load_type = wip_job_details.wip_mtl_requirement
207 and wjdi.substitution_type = p_substitution_type
208 and wjdi.operation_seq_num = p_operation_seq_num
209 and wjdi.inventory_item_id_old = p_inventory_item_id_old
210 and exists (select 1
211 from mtl_material_transactions mmt
212 where mmt.transaction_source_id = wjdi.wip_entity_id
213 and mmt.organization_id = wjdi.organization_id
214 and mmt.operation_seq_num = wjdi.operation_seq_num
215 and mmt.inventory_item_id = wjdi.inventory_item_id_old);
216
217
218
219 l_error_exists boolean := false;
220 begin
221
222 for l_inv_row in c_invalid_wro_rows loop
223 l_error_exists := true;
224 fnd_message.set_name('WIP', 'WIP_JDI_QTY_ISSUED');
225 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
226 if(wip_job_details.std_alone = 1) then
227 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
228 p_text => substr(fnd_message.get,1,500),
229 p_error_type => wip_jdi_utils.msg_error);
230 else
231 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
232 p_text => substr(fnd_message.get,1,500),
233 p_error_type => wip_jdi_utils.msg_error);
234 end if;
235 end loop;
236
237 if(l_error_exists) then
238 update wip_job_dtls_interface wjdi
239 set process_status = wip_constants.error
240 where group_id = p_group_id
241 and process_phase = wip_constants.ml_validation
242 and process_status in (wip_constants.running,
243 wip_constants.warning)
244 and wip_entity_id = p_wip_entity_id
245 and organization_id = p_organization_id
246 and wjdi.load_type = wip_job_details.wip_mtl_requirement
247 and wjdi.substitution_type = p_substitution_type
248 and wjdi.operation_seq_num = p_operation_seq_num
249 and wjdi.inventory_item_id_old = p_inventory_item_id_old
250 and exists (select 1
251 from wip_requirement_operations wro
252 where wro.wip_entity_id = wjdi.wip_entity_id
253 and wro.organization_id = wjdi.organization_id
254 and wro.operation_seq_num = wjdi.operation_seq_num
255 and wro.inventory_item_id = wjdi.inventory_item_id_old
256 and wro.quantity_issued > 0);
257 return;
258 end if;
259
260 for l_inv_row in c_invalid_mmtt_rows loop
261 l_error_exists := true;
262 fnd_message.set_name('WIP', 'WIP_JDI_REQ_JOB_PENDING');
263 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
264 if(wip_job_details.std_alone = 1) then
265 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
266 p_text => substr(fnd_message.get,1,500),
267 p_error_type => wip_jdi_utils.msg_error);
268 else
269 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
270 p_text => substr(fnd_message.get,1,500),
271 p_error_type => wip_jdi_utils.msg_error);
272 end if;
273 end loop;
274
275 if(l_error_exists) then
276 update wip_job_dtls_interface wjdi
277 set process_status = wip_constants.error
278 where group_id = p_group_id
279 and process_phase = wip_constants.ml_validation
280 and process_status in (wip_constants.running,
281 wip_constants.warning)
282 and wip_entity_id = p_wip_entity_id
283 and organization_id = p_organization_id
284 and wjdi.load_type = wip_job_details.wip_mtl_requirement
285 and wjdi.substitution_type = p_substitution_type
286 and wjdi.operation_seq_num = p_operation_seq_num
287 and wjdi.inventory_item_id_old = p_inventory_item_id_old
288 and exists (select 1
289 from mtl_material_transactions_temp mmtt
290 where mmtt.transaction_source_id = wjdi.wip_entity_id
291 and mmtt.organization_id = wjdi.organization_id
292 and mmtt.operation_seq_num = wjdi.operation_seq_num
293 and mmtt.inventory_item_id = wjdi.inventory_item_id_old);
294 return;
295 end if;
296
297 for l_inv_row in c_invalid_mmt_rows loop
298 l_error_exists := true;
299 fnd_message.set_name('WIP', 'WIP_JDI_REQ_JOB_PENDING');
300 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
301 if(wip_job_details.std_alone = 1) then
302 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
303 p_text => substr(fnd_message.get,1,500),
304 p_error_type => wip_jdi_utils.msg_error);
305 else
306 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
307 p_text => substr(fnd_message.get,1,500),
308 p_error_type => wip_jdi_utils.msg_error);
309 end if;
310 end loop;
311
312 if(l_error_exists) then
313 update wip_job_dtls_interface wjdi
314 set process_status = wip_constants.error
315 where group_id = p_group_id
316 and process_phase = wip_constants.ml_validation
317 and process_status in (wip_constants.running,
318 wip_constants.warning)
319 and wip_entity_id = p_wip_entity_id
320 and organization_id = p_organization_id
321 and wjdi.load_type = wip_job_details.wip_mtl_requirement
322 and wjdi.substitution_type = p_substitution_type
323 and wjdi.operation_seq_num = p_operation_seq_num
324 and wjdi.inventory_item_id_old = p_inventory_item_id_old
325 and exists (select 1
326 from mtl_material_transactions mmt
327 where mmt.transaction_source_id = wjdi.wip_entity_id
328 and mmt.organization_id = wjdi.organization_id
329 and mmt.operation_seq_num = wjdi.operation_seq_num
330 and mmt.inventory_item_id = wjdi.inventory_item_id_old);
331 end if;
332 end safe_delete;
333
334
335 /* main delete, call the above. If any validation fail, it won''t go on
336 with the next validations */
337 Procedure Delete_Req(p_group_id in number,
338 p_wip_entity_id in number,
339 p_organization_id in number,
340 p_substitution_type in number) IS
341
342 CURSOR req_info(p_group_Id number,
343 p_wip_entity_id number,
344 p_organization_id number,
345 p_substitution_type number) IS
346 SELECT distinct operation_seq_num,
347 inventory_item_id_old, inventory_item_id_new,
348 quantity_per_assembly,
349 last_update_date, last_updated_by, creation_date, created_by,
350 last_update_login, request_id, program_application_id,
351 program_id, program_update_date,
352 department_id, wip_supply_type, date_required,
353 required_quantity, quantity_issued, supply_subinventory,
354 supply_locator_id, mrp_net_flag, mps_required_quantity,
355 mps_date_required, attribute_category, attribute1,
356 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
357 attribute8,attribute9,attribute10,attribute11,attribute12,
358 attribute13,attribute14,attribute15
359 FROM WIP_JOB_DTLS_INTERFACE
360 WHERE group_id = p_group_id
361 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
362 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
363 AND wip_entity_id = p_wip_entity_id
364 AND organization_id = p_organization_id
365 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
366 AND substitution_type = p_substitution_type;
367
368 BEGIN
369 FOR cur_row IN req_info(p_group_id,
370 p_wip_entity_id,
371 p_organization_id,
372 p_substitution_type) LOOP
373 Del_Req_Info_Exist(p_group_id,
374 p_wip_entity_id,
375 p_organization_id,
376 p_substitution_type,
377 cur_row.operation_seq_num);
378
379 IF Info_Missing(p_group_id,
380 p_wip_entity_id,
381 p_organization_id,
382 p_substitution_type,
383 cur_row.operation_seq_num) = 0 THEN
384
385 REQ_JOB_Match (p_group_id,
386 p_wip_entity_id,
387 p_organization_id,
388 p_substitution_type,
389 cur_row.operation_seq_num,
390 cur_row.inventory_item_id_old);
391
392 IF IS_Error(p_group_id,
393 p_wip_entity_id,
394 p_organization_id,
395 p_substitution_type,
396 cur_row.operation_seq_num,
397 cur_row.inventory_item_id_old,
398 cur_row.inventory_item_id_new) = 0 THEN
399
400 Safe_Delete (p_group_id,
401 p_wip_entity_id,
402 p_organization_id,
403 p_substitution_type,
404 cur_row.operation_seq_num,
405 cur_row.inventory_item_id_old);
406
407 END IF;
408 END IF;
409 END LOOP;
410 END Delete_Req;
411
412
413 procedure add_req_info_exist(p_group_id in number,
414 p_wip_entity_id in number,
415 p_organization_id in number,
416 p_substitution_type in number,
417 p_operation_seq_num in number) IS
418 cursor c_invalid_rows is
419 select interface_id
420 from wip_job_dtls_interface wjdi
421 where wjdi.group_id = p_group_id
422 and wjdi.process_phase = wip_constants.ml_validation
423 and wjdi.process_status in (wip_constants.running,
424 wip_constants.warning)
425 and wjdi.wip_entity_id = p_wip_entity_id
426 and wjdi.organization_id = p_organization_id
427 and wjdi.load_type = wip_job_details.wip_mtl_requirement
428 and wjdi.substitution_type = p_substitution_type
429 and wjdi.operation_seq_num = p_operation_seq_num
430 and ( wjdi.inventory_item_id_new is null
431 or wjdi.quantity_per_assembly is null);
432
433 l_error_exists boolean := false;
434 begin
435
436 for l_inv_row in c_invalid_rows loop
437 l_error_exists := true;
438 fnd_message.set_name('WIP', 'WIP_JDI_ADD_REQ_INFO_MISSING');
439 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
440 if(wip_job_details.std_alone = 1) then
441 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
442 p_text => substr(fnd_message.get,1,500),
443 p_error_type => wip_jdi_utils.msg_error);
444 else
445 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
446 p_text => substr(fnd_message.get,1,500),
447 p_error_type => wip_jdi_utils.msg_error);
448 end if;
449 end loop;
450
451 if(l_error_exists) then
452 update wip_job_dtls_interface wjdi
453 set process_status = wip_constants.error
454 where group_id = p_group_id
455 and process_phase = wip_constants.ml_validation
456 and process_status in (wip_constants.running,
457 wip_constants.warning)
458 and wip_entity_id = p_wip_entity_id
459 and organization_id = p_organization_id
460 and wjdi.load_type = wip_job_details.wip_mtl_requirement
461 and wjdi.substitution_type = p_substitution_type
462 and wjdi.operation_seq_num = p_operation_seq_num
463 and ( wjdi.inventory_item_id_new is null
464 or wjdi.quantity_per_assembly is null);
465 end if;
466 end add_req_info_exist;
467
468
469
470 /* operations, requirements, should NOT exist; for add/change *
471 check for duplicate requirement/operations */
472 procedure req_job_not_exist (p_group_id in number,
473 p_wip_entity_id in number,
474 p_organization_id in number,
475 p_substitution_type in number,
476 p_operation_seq_num in number,
477 p_inventory_item_id_new in number) IS
478 cursor c_invalid_rows is
479 select interface_id
480 from wip_job_dtls_interface wjdi
481 where wjdi.group_id = p_group_id
482 and wjdi.process_phase = wip_constants.ml_validation
483 and wjdi.process_status in (wip_constants.running,
484 wip_constants.warning)
485 and wjdi.wip_entity_id = p_wip_entity_id
486 and wjdi.organization_id = p_organization_id
487 and wjdi.load_type = wip_job_details.wip_mtl_requirement
488 and wjdi.substitution_type = p_substitution_type
489 and wjdi.operation_seq_num = p_operation_seq_num
490 and wjdi.inventory_item_id_new = p_inventory_item_id_new
491 /* bug#2814045 */
492 and nvl(wjdi.inventory_item_id_new, -1) <> nvl(wjdi.inventory_item_id_old, -1)
493 and ( exists (select 1
494 from wip_requirement_operations wro
495 where wro.wip_entity_id = wjdi.wip_entity_id
496 and wro.organization_id = wjdi.organization_id
497 and wro.operation_seq_num = wjdi.operation_seq_num
498 and wro.inventory_item_id = wjdi.inventory_item_id_new)
499 or exists (select 1
500 from wip_job_dtls_interface wjdi2
501 where wjdi.interface_id <> wjdi2.interface_id
502 and wjdi.group_id = wjdi2.group_id
503 and wjdi.wip_entity_id = wjdi2.wip_entity_id
504 and wjdi.organization_id = wjdi2.organization_id
505 and wjdi.operation_seq_num = wjdi2.operation_seq_num
506 and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
507 );
508
509 l_error_exists boolean := false;
510 begin
511
512 for l_inv_row in c_invalid_rows loop
513 l_error_exists := true;
514 fnd_message.set_name('WIP', 'WIP_JDI_REQ_EXIST');
515 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
516 if(wip_job_details.std_alone = 1) then
517 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
518 p_text => substr(fnd_message.get,1,500),
519 p_error_type => wip_jdi_utils.msg_error);
520 else
521 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
522 p_text => substr(fnd_message.get,1,500),
523 p_error_type => wip_jdi_utils.msg_error);
524 end if;
525 end loop;
526
527 if(l_error_exists) then
528 update wip_job_dtls_interface wjdi
529 set process_status = wip_constants.error
530 where group_id = p_group_id
531 and process_phase = wip_constants.ml_validation
532 and process_status in (wip_constants.running,
533 wip_constants.warning)
534 and wip_entity_id = p_wip_entity_id
535 and organization_id = p_organization_id
536 and wjdi.load_type = wip_job_details.wip_mtl_requirement
537 and wjdi.substitution_type = p_substitution_type
538 and wjdi.operation_seq_num = p_operation_seq_num
539 and wjdi.inventory_item_id_new = p_inventory_item_id_new
540 /* Fix for Bug 5632150 */
541 /* and wjdi.inventory_item_id_new <> wjdi.inventory_item_id_old */
542 and nvl(wjdi.inventory_item_id_new,-1) <> nvl(wjdi.inventory_item_id_old,-1)
543 and ( exists (select 1
544 from wip_requirement_operations wro
545 where wro.wip_entity_id = wjdi.wip_entity_id
546 and wro.organization_id = wjdi.organization_id
547 and wro.operation_seq_num = wjdi.operation_seq_num
548 and wro.inventory_item_id = wjdi.inventory_item_id_new)
549 or exists (select 1
550 from wip_job_dtls_interface wjdi2
551 where wjdi.interface_id <> wjdi2.interface_id
552 and wjdi.group_id = wjdi2.group_id
553 and wjdi.wip_entity_id = wjdi2.wip_entity_id
554 and wjdi.organization_id = wjdi2.organization_id
555 and wjdi.operation_seq_num = wjdi2.operation_seq_num
556 and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
557 );
558 end if;
559 end req_job_not_exist;
560
561
562
563 /* for add/change only */
564 procedure valid_requirement(p_group_id in number,
565 p_wip_entity_id in number,
566 p_organization_id in number,
567 p_substitution_type in number,
568 p_operation_seq_num in number,
569 p_inventory_item_id_new in number) IS
570 cursor c_invalid_rows is
571 select interface_id
572 from wip_job_dtls_interface wjdi
573 where wjdi.group_id = p_group_id
574 and wjdi.process_phase = wip_constants.ml_validation
575 and wjdi.process_status in (wip_constants.running,
576 wip_constants.warning)
577 and wjdi.wip_entity_id = p_wip_entity_id
578 and wjdi.organization_id = p_organization_id
579 and wjdi.load_type = wip_job_details.wip_mtl_requirement
580 and wjdi.substitution_type = p_substitution_type
581 and wjdi.operation_seq_num = p_operation_seq_num
582 and wjdi.inventory_item_id_new = p_inventory_item_id_new
583 and not exists (select 1
584 from mtl_system_items msi
585 where msi.inventory_item_id = wjdi.inventory_item_id_new
586 and msi.organization_id = wjdi.organization_id);
587
588 /* bug#2811687 : begin */
589 cursor c_supply_types_invalid_rows is
590 select wjdi.interface_id
591 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
592 where wjdi.group_id = p_group_id
593 and wjdi.process_phase = wip_constants.ml_validation
594 and wjdi.process_status in (wip_constants.running,
595 wip_constants.warning)
596 and wjdi.wip_entity_id = p_wip_entity_id
597 and wjdi.organization_id = p_organization_id
598 and wjdi.load_type = wip_job_details.wip_mtl_requirement
599 and wjdi.substitution_type = p_substitution_type
600 and wjdi.operation_seq_num = p_operation_seq_num
601 and wjdi.inventory_item_id_new = p_inventory_item_id_new
602 and wjdi.parent_header_id = wjsi.header_id
603 and wjdi.group_id = wjsi.group_id
604 and wjdi.organization_id = wjsi.organization_id
605 and wjdi.wip_entity_id = wjsi.wip_entity_id
606 and (wjdi.wip_supply_type = wip_constants.phantom
607 or (wjsi.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)
608 and wjdi.wip_supply_type <> wip_constants.push));
609
610 cursor c_mrp_net_flag_invalid_rows is
611 select wjdi.interface_id
612 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
613 where wjdi.group_id = p_group_id
614 and wjdi.process_phase = wip_constants.ml_validation
615 and wjdi.process_status in (wip_constants.running,
616 wip_constants.warning)
617 and wjdi.wip_entity_id = p_wip_entity_id
618 and wjdi.organization_id = p_organization_id
619 and wjdi.load_type = wip_job_details.wip_mtl_requirement
620 and wjdi.substitution_type = p_substitution_type
621 and wjdi.operation_seq_num = p_operation_seq_num
622 and wjdi.inventory_item_id_new = p_inventory_item_id_new
623 and wjdi.parent_header_id = wjsi.header_id
624 and wjdi.group_id = wjsi.group_id
625 and wjdi.organization_id = wjsi.organization_id
626 and wjdi.wip_entity_id = wjsi.wip_entity_id
627 and (wjdi.mrp_net_flag not in (wip_constants.yes, wip_constants.no));
628
629 cursor c_auto_req_mat_invalid_rows is
630 select wjdi.interface_id
631 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
632 where wjdi.group_id = p_group_id
633 and wjdi.process_phase = wip_constants.ml_validation
634 and wjdi.process_status in (wip_constants.running,
635 wip_constants.warning)
636 and wjdi.wip_entity_id = p_wip_entity_id
637 and wjdi.organization_id = p_organization_id
638 and wjdi.load_type = wip_job_details.wip_mtl_requirement
639 and wjdi.substitution_type = p_substitution_type
640 and wjdi.operation_seq_num = p_operation_seq_num
641 and wjdi.inventory_item_id_new = p_inventory_item_id_new
642 and wjdi.parent_header_id = wjsi.header_id
643 and wjdi.group_id = wjsi.group_id
644 and wjdi.organization_id = wjsi.organization_id
645 and wjdi.wip_entity_id = wjsi.wip_entity_id
646 and (upper(wjdi.auto_request_material) not in ('Y', 'N'));
647 /* bug#2814045 : end */
648
649 /* bug 3112793 */
650 cursor c_direct_item_rows(p_profile_value IN number) is
651 select interface_id
652 from wip_job_dtls_interface wjdi
653 where wjdi.group_id=p_group_id
654 and wjdi.process_phase = wip_constants.ml_validation
655 and wjdi.process_status in ( wip_constants.running,
656 wip_constants.warning )
657 and wjdi.wip_entity_id = p_wip_entity_id
658 and wjdi.organization_id = p_organization_id
659 and wjdi.load_type = wip_job_details.wip_mtl_requirement
660 and wjdi.substitution_type = p_substitution_type
661 and wjdi.operation_seq_num = p_operation_seq_num
662 and wjdi.inventory_item_id_new = p_inventory_item_id_new
663 and NOT exists ( select 1 from mtl_system_items msi
664 where msi.inventory_item_id = wjdi.inventory_item_id_new
665 and msi.organization_id = wjdi.organization_id
666 and BOM_ENABLED_FLAG = 'Y'
667 and BOM_ITEM_TYPE = 4
668 and (( p_profile_value = WIP_CONSTANTS.YES)
669 or (ENG_ITEM_FLAG = 'N' and p_profile_value = WIP_CONSTANTS.NO))
670 );
671 /* end of 3112793 */
672
673 l_profile_value number ;
674 l_error_exists boolean := false;
675 begin
676
677 for l_inv_row in c_invalid_rows loop
678 l_error_exists := true;
679 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_MTL_REQ');
680 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
681 if(wip_job_details.std_alone = 1) then
682 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
683 p_text => substr(fnd_message.get,1,500),
684 p_error_type => wip_jdi_utils.msg_error);
685 else
686 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
687 p_text => substr(fnd_message.get,1,500),
688 p_error_type => wip_jdi_utils.msg_error);
689 end if;
690 end loop;
691
692 /* bug#2811687 : begin */
693 for l_inv_row in c_supply_types_invalid_rows loop
694 l_error_exists := true;
695 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SUPPLY_TYPE');
696 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
697 if(wip_job_details.std_alone = 1) then
698 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
699 p_text => substr(fnd_message.get,1,500),
700 p_error_type => wip_jdi_utils.msg_error);
701 else
702 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
703 p_text => substr(fnd_message.get,1,500),
704 p_error_type => wip_jdi_utils.msg_error);
705 end if;
706 end loop;
707
708 for l_inv_row in c_mrp_net_flag_invalid_rows loop
709 l_error_exists := true;
710 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_MRP_NET_FLAG');
711 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
712 if(wip_job_details.std_alone = 1) then
713 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
714 p_text => substr(fnd_message.get,1,500),
715 p_error_type => wip_jdi_utils.msg_error);
716 else
717 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
718 p_text => substr(fnd_message.get,1,500),
719 p_error_type => wip_jdi_utils.msg_error);
720 end if;
721 end loop;
722
723 for l_inv_row in c_auto_req_mat_invalid_rows loop
724 l_error_exists := true;
725 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_AUTO_REQ_MAT');
726 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
727 if(wip_job_details.std_alone = 1) then
728 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
729 p_text => substr(fnd_message.get,1,500),
730 p_error_type => wip_jdi_utils.msg_error);
731 else
732 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
733 p_text => substr(fnd_message.get,1,500),
734 p_error_type => wip_jdi_utils.msg_error);
735 end if;
736 end loop;
737 /* bug#2811687 : end */
738
739 l_profile_value := fnd_profile.value('WIP_SEE_ENG_ITEMS');
740 for l_inv_row in c_direct_item_rows ( l_profile_value ) loop
741 l_error_exists := true ;
742 fnd_message.set_name('WIP','WIP_JDI_DIRECT_ITEM');
743 fnd_message.set_token('INTERFACE',to_char(l_inv_row.interface_id));
744 if(wip_job_details.std_alone =1 ) then
745 wip_interface_err_utils.add_error (
746 p_interface_id => l_inv_row.interface_id,
747 p_text => substr(fnd_message.get,1,500),
748 p_error_type => wip_jdi_utils.msg_error);
749 else
750 wip_interface_err_utils.add_error (
751 p_interface_id => wip_jsi_utils.current_interface_id,
752 p_text => substr(fnd_message.get,1,500),
753 p_error_type => wip_jdi_utils.msg_error);
754 end if ;
755 end loop;
756
757 if(l_error_exists) then
758 update wip_job_dtls_interface wjdi
759 set process_status = wip_constants.error
760 where group_id = p_group_id
761 and process_phase = wip_constants.ml_validation
762 and process_status in (wip_constants.running,
763 wip_constants.warning)
764 and wip_entity_id = p_wip_entity_id
765 and organization_id = p_organization_id
766 and wjdi.load_type = wip_job_details.wip_mtl_requirement
767 and wjdi.substitution_type = p_substitution_type
768 and wjdi.operation_seq_num = p_operation_seq_num
769 and wjdi.inventory_item_id_new = p_inventory_item_id_new;
770 /* bug#2811687
771 and not exists (select 1
772 from mtl_system_items msi
773 where msi.inventory_item_id = wjdi.inventory_item_id_new
774 and msi.organization_id = wjdi.organization_id);
775 */
776 end if;
777
778 end valid_requirement;
779
780
781 /*Bug 4202200 */
782 procedure valid_requirement_supply_type
783 (p_group_id in number,
784 --need to fixed in forward port of 4142439 or 4159367, also see sql
785 --p_parent_header_id in number,
786 p_wip_entity_id in number,
787 p_organization_id in number,
788 p_substitution_type in number,
789 p_operation_seq_num in number,
790 p_inventory_item_id_old in number,
791 p_inventory_item_id_new in number) IS
792
793 /* This query is modified for bug 5216025. The join with WJSI is not required as the validation happens
794 only if there is a parent record in wip_job_schedule_interface for the same job.
795 */
796 cursor c_supply_types_invalid_rows is
797 select wjdi.interface_id
798 from wip_job_dtls_interface wjdi /*, wip_job_schedule_interface wjsi */
799 where wjdi.group_id = p_group_id
800 and wjdi.process_phase = wip_constants.ml_validation
801 and wjdi.process_status in (wip_constants.running,
802 wip_constants.warning)
803 and wjdi.wip_entity_id = p_wip_entity_id
804 and wjdi.organization_id = p_organization_id
805 and wjdi.load_type = wip_job_details.wip_mtl_requirement
806 and wjdi.substitution_type = p_substitution_type
807 and wjdi.operation_seq_num = p_operation_seq_num
808 and (wjdi.inventory_item_id_new = p_inventory_item_id_new
809 or p_inventory_item_id_new is null)
810 and (wjdi.inventory_item_id_old = p_inventory_item_id_old
811 or p_inventory_item_id_old is null)
812 and (p_inventory_item_id_old is not null or
813 p_inventory_item_id_new is not null)
814 /*and (wjdi.parent_header_id = p_parent_header_id or
815 WIP_JOB_DETAILS.STD_ALONE = 1)*/
816 /*and wjdi.organization_id = wjsi.organization_id
817 and wjdi.wip_entity_id = wjsi.wip_entity_id*/
818 and wjdi.wip_supply_type = wip_constants.op_pull
819 and not exists
820 (select 1 from wip_operations
821 where wip_entity_id = wjdi.wip_entity_id
822 and organization_id = wjdi.organization_id);
823
824
825 l_error_exists boolean := false;
826 begin
827
828 for l_inv_row in c_supply_types_invalid_rows loop
829 l_error_exists := true;
830 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SUP_TYPE_NO_OP');
831 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
832 if(wip_job_details.std_alone = 1) then
833 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
834 p_text => substr(fnd_message.get,1,500),
835 p_error_type => wip_jdi_utils.msg_error);
836 else
837 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
838 p_text => substr(fnd_message.get,1,500),
839 p_error_type => wip_jdi_utils.msg_error);
840 end if;
841
842 update wip_job_dtls_interface wjdi
843 set process_status = wip_constants.error
844 where interface_id = l_inv_row.interface_id;
845 end loop;
846
847 end valid_requirement_supply_type;
848 /*End of Bug Fix 4202200 */
849
850
851 /* main add, call the above */
852 Procedure add_Req(p_group_id in number,
853 p_wip_entity_id in number,
854 p_organization_id in number,
855 p_substitution_type in number) IS
856
857 x_err_code varchar2(30) := null;
858 x_err_msg varchar2(240) := NULL;
859
860 CURSOR req_info(p_group_Id number,
861 p_wip_entity_id number,
862 p_organization_id number,
863 p_substitution_type number) IS
864 SELECT distinct operation_seq_num,
865 inventory_item_id_old, inventory_item_id_new,
866 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
867 last_update_date, last_updated_by, creation_date, created_by,
868 last_update_login, request_id, program_application_id,
869 program_id, program_update_date,
870 department_id, wip_supply_type, date_required,
871 required_quantity, quantity_issued,
872 basis_type, /* LBM Project */
873 supply_subinventory,
874 supply_locator_id, mrp_net_flag, mps_required_quantity,
875 mps_date_required, attribute_category, attribute1,
876 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
877 attribute8,attribute9,attribute10,attribute11,attribute12,
878 attribute13,attribute14,attribute15
879 FROM WIP_JOB_DTLS_INTERFACE
880 WHERE group_id = p_group_id
881 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
882 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
883 AND wip_entity_id = p_wip_entity_id
884 AND organization_id = p_organization_id
885 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
886 AND substitution_type = p_substitution_type;
887
888 BEGIN
889 FOR cur_row IN req_info(p_group_id,
890 p_wip_entity_id,
891 p_organization_id,
892 p_substitution_type) LOOP
893
894 Add_Req_Info_Exist(p_group_id,
895 p_wip_entity_id,
896 p_organization_id,
897 p_substitution_type,
898 cur_row.operation_seq_num);
899
900 IF Info_Missing(p_group_id,
901 p_wip_entity_id,
902 p_organization_id,
903 p_substitution_type,
904 cur_row.operation_seq_num) = 0 THEN
905
906 REQ_JOB_NOT_EXIST (p_group_id,
907 p_wip_entity_id,
908 p_organization_id,
909 p_substitution_type,
910 cur_row.operation_seq_num,
911 cur_row.inventory_item_id_new);
912
913 IF IS_Error(p_group_id,
914 p_wip_entity_id,
915 p_organization_id,
916 p_substitution_type,
917 cur_row.operation_seq_num,
918 cur_row.inventory_item_id_old,
919 cur_row.inventory_item_id_new) = 0 THEN
920
921 Valid_Requirement (p_group_id,
922 p_wip_entity_id,
923 p_organization_id,
924 p_substitution_type,
925 cur_row.operation_seq_num,
926 cur_row.inventory_item_id_new);
927
928 IF IS_Error(p_group_id,
929 p_wip_entity_id,
930 p_organization_id,
931 p_substitution_type,
932 cur_row.operation_seq_num,
933 cur_row.inventory_item_id_old,
934 cur_row.inventory_item_id_new) = 0 THEN
935
936 WIP_REQUIREMENT_DEFAULT.Default_Requirement(
937 p_group_id,
938 p_wip_entity_id,
939 p_organization_id,
940 p_substitution_type,
941 cur_row.operation_seq_num,
942 cur_row.inventory_item_id_old,
943 cur_row.inventory_item_id_new,
944 round(cur_row.quantity_per_assembly, 6),
945 cur_row.basis_type, /* LBM Project */
946 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
947 x_err_code,
948 x_err_msg);
949
950 IF x_err_code is null THEN
951 Post_Default (p_group_id,
952 p_wip_entity_id,
953 p_organization_id,
954 p_substitution_type,
955 cur_row.operation_seq_num,
956 cur_row.inventory_item_id_new);
957 END IF;
958 END IF;
959 END IF;
960 END IF;
961
962 /*bug 4202200
963 If the job has no operations , supply_type can not be
964 operation pull
965 */
966
967 Valid_Requirement_Supply_Type (p_group_id,
968 --need to fixed in forward port of 4142439 or 4159367
969 --p_parent_header_id,
970 p_wip_entity_id,
971 p_organization_id,
972 p_substitution_type,
973 cur_row.operation_seq_num,
974 cur_row.inventory_item_id_old,
975 cur_row.inventory_item_id_new);
976
977 END LOOP;
978 END Add_Req;
979
980
981
982 /* called after defaulting */
983 procedure post_default(p_group_id number,
984 p_wip_entity_id number,
985 p_organization_id number,
986 p_substitution_type number,
987 p_operation_seq_num number,
988 p_inventory_item_id_new number) IS
989 cursor c_invalid_rows is
990 select interface_id
991 from wip_job_dtls_interface wjdi
992 where wjdi.group_id = p_group_id
993 and wjdi.process_phase = wip_constants.ml_validation
994 and wjdi.process_status in (wip_constants.running,
995 wip_constants.warning)
996 and wjdi.wip_entity_id = p_wip_entity_id
997 and wjdi.organization_id = p_organization_id
998 and wjdi.load_type = wip_job_details.wip_mtl_requirement
999 and wjdi.substitution_type = p_substitution_type
1000 and wjdi.operation_seq_num = p_operation_seq_num
1001 and wjdi.inventory_item_id_new = p_inventory_item_id_new
1002 and wjdi.wip_supply_type is null;
1003
1004 l_error_exists boolean := false;
1005 begin
1006
1007 -- Give Error if wip_supply_type is NULL
1008 -- Only do this validation when ADD/CHANGE Requirements
1009
1010 for l_inv_row in c_invalid_rows loop
1011 l_error_exists := true;
1012 fnd_message.set_name('WIP', 'WIP_JDI_NULL_SUPPLY_TYPE');
1013 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1014 if(wip_job_details.std_alone = 1) then
1015 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1016 p_text => substr(fnd_message.get,1,500),
1017 p_error_type => wip_jdi_utils.msg_error);
1018 else
1019 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1020 p_text => substr(fnd_message.get,1,500),
1021 p_error_type => wip_jdi_utils.msg_error);
1022 end if;
1023 end loop;
1024
1025 if(l_error_exists) then
1026 update wip_job_dtls_interface wjdi
1027 set process_status = wip_constants.error
1028 where group_id = p_group_id
1029 and process_phase = wip_constants.ml_validation
1030 and process_status in (wip_constants.running,
1031 wip_constants.warning)
1032 and wip_entity_id = p_wip_entity_id
1033 and organization_id = p_organization_id
1034 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1035 and wjdi.substitution_type = p_substitution_type
1036 and wjdi.operation_seq_num = p_operation_seq_num
1037 and wjdi.inventory_item_id_new = p_inventory_item_id_new
1038 and wjdi.wip_supply_type is null;
1039 end if;
1040 end post_default;
1041
1042
1043
1044 procedure chng_req_info_exist(p_group_id number,
1045 p_wip_entity_id number,
1046 p_organization_id number,
1047 p_substitution_type number,
1048 p_operation_seq_num number) IS
1049 cursor c_invalid_rows is
1050 select interface_id
1051 from wip_job_dtls_interface wjdi
1052 where wjdi.group_id = p_group_id
1053 and wjdi.process_phase = wip_constants.ml_validation
1054 and wjdi.process_status in (wip_constants.running,
1055 wip_constants.warning)
1056 and wjdi.wip_entity_id = p_wip_entity_id
1057 and wjdi.organization_id = p_organization_id
1058 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1059 and wjdi.substitution_type = p_substitution_type
1060 and wjdi.operation_seq_num = p_operation_seq_num
1061 and ( wjdi.inventory_item_id_old is null
1062 or (wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
1063 and wjdi.quantity_per_assembly is null
1064 and not exists
1065 ( select 1
1066 from bom_substitute_components bsc
1067 where bsc.substitute_component_id = wjdi.inventory_item_id_new
1068 and bsc.component_sequence_id =
1069 (select wro.component_sequence_id
1070 from wip_requirement_operations wro
1071 where wro.inventory_item_id = wjdi.inventory_item_id_old
1072 and wro.wip_entity_id = wjdi.wip_entity_id
1073 and wro.operation_seq_num = wjdi.operation_seq_num
1074 and wro.organization_id = wjdi.organization_id
1075 )
1076 and bsc.acd_type is null
1077 )
1078 )
1079 ) ;
1080
1081 l_error_exists boolean := false;
1082 begin
1083
1084 -- Give Error if wip_supply_type is NULL
1085 -- Only do this validation when ADD/CHANGE Requirements
1086
1087 for l_inv_row in c_invalid_rows loop
1088 l_error_exists := true;
1089 fnd_message.set_name('WIP', 'WIP_JDI_CHNG_REQ_INFO_MISSING');
1090 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1091 if(wip_job_details.std_alone = 1) then
1092 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1093 p_text => substr(fnd_message.get,1,500),
1094 p_error_type => wip_jdi_utils.msg_error);
1095 else
1096 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1097 p_text => substr(fnd_message.get,1,500),
1098 p_error_type => wip_jdi_utils.msg_error);
1099 end if;
1100 end loop;
1101
1102 if(l_error_exists) then
1103 update wip_job_dtls_interface wjdi
1104 set process_status = wip_constants.error
1105 where group_id = p_group_id
1106 and process_phase = wip_constants.ml_validation
1107 and process_status in (wip_constants.running,
1108 wip_constants.warning)
1109 and wip_entity_id = p_wip_entity_id
1110 and organization_id = p_organization_id
1111 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1112 and wjdi.substitution_type = p_substitution_type
1113 and wjdi.operation_seq_num = p_operation_seq_num
1114 and ( wjdi.inventory_item_id_old is null
1115 or ( wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
1116 and wjdi.quantity_per_assembly is null
1117 )
1118 );
1119 end if;
1120 end chng_req_info_exist;
1121
1122 Procedure derive_quantity(
1123 p_group_id in number,
1124 p_wip_entity_id in number,
1125 p_organization_id in number,
1126 p_substitution_type in number,
1127 p_operation_seq_num in number,
1128 p_inventory_item_id_old in number,
1129 p_inventory_item_id_new in number,
1130 p_quantity_per_assembly in number,
1131 p_required_quantity in number,
1132 p_basis_type in number, /* LBM Project */
1133 p_component_yield_factor in number, /*Component Yield Enhancement(Bug 4369064)*/
1134 p_err_code out nocopy varchar2,
1135 p_err_msg out nocopy varchar2) IS
1136
1137 x_required_quantity NUMBER;
1138 x_mps_required_quantity NUMBER;
1139 X_start_quantity number;
1140 X_quantity_per_assembly number ;
1141 X_component_yield_factor number; /*Component Yield Enhancement(Bug 4369064)*/
1142 X_start_quantity_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1143 X_quantity_per_assembly_wro number ; /*Component Yield Enhancement(Bug 4369064)*/
1144 X_component_yield_factor_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1145 X_required_quantity_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1146 x_qpa_val number; /*Component Yield Enhancement(Bug 4369064)*/
1147 x_rq_val number; /*Component Yield Enhancement(Bug 4369064)*/
1148 x_cyf_val number; /*Component Yield Enhancement(Bug 4369064)*/
1149
1150
1151 BEGIN
1152
1153 /* returns if mat is changed, defaulting for those are done in
1154 WIP_REQUIREMENT_DEFAULT.Default_Requirement */
1155 if (p_inventory_item_id_new is not null and p_inventory_item_id_new <> p_inventory_item_id_old ) then
1156 return;
1157 end if;
1158
1159 begin
1160 SELECT start_quantity
1161 INTO X_start_quantity
1162 FROM WIP_DISCRETE_JOBS
1163 WHERE wip_entity_id = p_wip_entity_id
1164 AND organization_id = p_organization_id;
1165
1166 /*Component Yield Enhancement(Bug 4369064)->Get the current values of qpa, req_qty and yield*/
1167 begin
1168 SELECT nvl(component_yield_factor,1),required_quantity,quantity_per_assembly
1169 INTO x_component_yield_factor_wro,x_required_quantity_wro,x_quantity_per_assembly_wro
1170 FROM wip_requirement_operations
1171 WHERE wip_entity_id = p_wip_entity_id
1172 AND organization_id = p_organization_id
1173 AND inventory_item_id = p_inventory_item_id_old;
1174 exception
1175 when no_data_found then
1176 return; /*Let the validation error be caught in valid_requirement*/
1177 end;
1178
1179 X_quantity_per_assembly := p_quantity_per_assembly;
1180 x_required_quantity := p_required_quantity;
1181 X_component_yield_factor := p_component_yield_factor; /*Component Yield Enhancement(Bug 4369064)*/
1182
1183 /*Component Yield Enhancement(Bug 4369064)
1184 Use following values while re-calculation of yield or required quantity*/
1185
1186 x_qpa_val := nvl(x_quantity_per_assembly,x_quantity_per_assembly_wro);
1187 x_rq_val := nvl(x_required_quantity,x_required_quantity_wro);
1188 x_cyf_val := nvl(x_component_yield_factor,x_component_yield_factor_wro);
1189
1190 /*Component Yield Enhancement(Bug 4369064)
1191 User can provide any combination of qpa, req_qty and yield in WJDI. So there would be 8 combinations
1192 in all. We have divided them in following categories
1193 1. When all of them are null -> Don't do anything
1194 2. When only req qty is provied -> ie user wanted to re-calculate yield from req qty.
1195 3. When req qty and QPA are provided -> calculate yield.
1196 4. When req qty and yield are provided -> calculate QPA.
1197 5. Rest of the cases -> re-calculate required quantity in all such cases
1198 */
1199 if x_quantity_per_assembly is null and x_component_yield_factor is null
1200 and x_required_quantity is null then
1201 null;
1202
1203 elsif x_quantity_per_assembly is null and x_component_yield_factor is null
1204 and x_required_quantity is not null then /*User entered required qty, calculate yield */
1205
1206 /* LBM Project changes have been re-evaluated by Jenny */
1207 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1208 x_component_yield_factor := round(x_qpa_val / x_required_quantity,6);
1209 else
1210 x_component_yield_factor := round(x_qpa_val * X_start_quantity / x_required_quantity,6);
1211 end if;
1212
1213 elsif x_required_quantity is not null and x_quantity_per_assembly is not null
1214 and x_component_yield_factor is null then /*User entered required qty and QPA, calculate yield */
1215
1216 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1217 x_component_yield_factor := round(x_qpa_val / x_required_quantity,6);
1218 else
1219 x_component_yield_factor := round(x_qpa_val * X_start_quantity / x_required_quantity,6);
1220 end if;
1221
1222 elsif x_required_quantity is not null and x_component_yield_factor is not null
1223 and x_quantity_per_assembly is null then /*User entered required qty and yield, calculate QPA */
1224
1225 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1226 X_quantity_per_assembly := round(x_cyf_val * x_required_quantity,6);
1227 else
1228 X_quantity_per_assembly := round(x_cyf_val * x_required_quantity / X_start_quantity ,6);
1229 end if;
1230
1231 else /*User entered QPA, calculate required qty */
1232 /*User entered yield, calculate required qty */
1233 /*User entered QPA and yield, calculate required qty */
1234 /*User entered QPA, required qty and yield, re-calculate required qty */
1235 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1236 x_required_quantity := round(x_qpa_val / x_cyf_val , 6);
1237 else
1238 x_required_quantity := round(x_start_quantity * x_qpa_val / x_cyf_val , 6);
1239 end if;
1240
1241 x_MPS_required_quantity := x_required_quantity;
1242
1243 end if;
1244
1245 if (x_required_quantity is not null) then
1246 UPDATE WIP_JOB_DTLS_INTERFACE
1247 SET quantity_per_assembly = nvl(X_quantity_per_assembly, quantity_per_assembly),
1248 required_quantity = x_required_quantity,
1249 mps_required_quantity = nvl(x_mps_required_quantity,mps_required_quantity),
1250 component_yield_factor = nvl(x_component_yield_factor,component_yield_factor)
1251 /*Component Yield Enhancement(Bug 4369064)*/
1252 WHERE group_id = p_group_id
1253 AND wip_entity_id = p_wip_entity_id
1254 AND organization_id = p_organization_id
1255 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1256 AND substitution_type = p_substitution_type
1257 AND operation_seq_num = p_operation_seq_num
1258 AND inventory_item_id_old = p_inventory_item_id_old;
1259 end if;
1260
1261 exception
1262 when others then
1263 p_err_msg := 'WIPRQVDB.pls<Procedure derive_quantity>:' || SQLERRM;
1264 p_err_code := SQLCODE;
1265 end;
1266
1267 END derive_quantity;
1268
1269 Procedure Change_Req(p_group_id in number,
1270 p_wip_entity_id in number,
1271 p_organization_id in number,
1272 p_substitution_type in number) IS
1273
1274 x_err_code varchar2(30) := null;
1275 x_err_msg varchar2(240) := NULL;
1276 CURSOR req_info(p_group_Id number,
1277 p_wip_entity_id number,
1278 p_organization_id number,
1279 p_substitution_type number) IS
1280 SELECT distinct operation_seq_num,
1281 inventory_item_id_old, inventory_item_id_new,
1282 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1283 last_update_date, last_updated_by, creation_date, created_by,
1284 last_update_login, request_id, program_application_id,
1285 program_id, program_update_date,
1286 department_id, wip_supply_type, date_required,
1287 required_quantity, quantity_issued,
1288 basis_type, /* LBM Project */
1289 supply_subinventory,
1290 supply_locator_id, mrp_net_flag, mps_required_quantity,
1291 mps_date_required, attribute_category, attribute1,
1292 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1293 attribute8,attribute9,attribute10,attribute11,attribute12,
1294 attribute13,attribute14,attribute15
1295 FROM WIP_JOB_DTLS_INTERFACE
1296 WHERE group_id = p_group_id
1297 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1298 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1299 AND wip_entity_id = p_wip_entity_id
1300 AND organization_id = p_organization_id
1301 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1302 AND substitution_type = p_substitution_type;
1303
1304 BEGIN
1305
1306 FOR cur_row IN req_info(p_group_id,
1307 p_wip_entity_id,
1308 p_organization_id,
1309 p_substitution_type) LOOP
1310
1311 derive_quantity(p_group_id,
1312 p_wip_entity_id,
1313 p_organization_id,
1314 p_substitution_type,
1315 cur_row.operation_seq_num,
1316 cur_row.inventory_item_id_old,
1317 cur_row.inventory_item_id_new,
1318 round(cur_row.quantity_per_assembly, 6),
1319 round(cur_row.required_quantity, 6),
1320 cur_row.basis_type, /* LBM Project */
1321 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
1322 x_err_code,
1323 x_err_msg);
1324 IF x_err_code is null then
1325
1326 Chng_Req_Info_Exist(p_group_id,
1327 p_wip_entity_id,
1328 p_organization_id,
1329 p_substitution_type,
1330 cur_row.operation_seq_num);
1331
1332 IF Info_Missing(p_group_id,
1333 p_wip_entity_id,
1334 p_organization_id,
1335 p_substitution_type,
1336 cur_row.operation_seq_num) = 0 THEN
1337 REQ_JOB_Match (p_group_id,
1338 p_wip_entity_id,
1339 p_organization_id,
1340 p_substitution_type,
1341 cur_row.operation_seq_num,
1342 cur_row.inventory_item_id_old);
1343
1344 IF IS_Error(p_group_id,
1345 p_wip_entity_id,
1346 p_organization_id,
1347 p_substitution_type,
1348 cur_row.operation_seq_num,
1349 cur_row.inventory_item_id_old,
1350 cur_row.inventory_item_id_new) = 0 AND
1351 cur_row.inventory_item_id_new IS NOT NULL THEN
1352
1353 REQ_JOB_NOT_EXIST (p_group_id,
1354 p_wip_entity_id,
1355 p_organization_id,
1356 p_substitution_type,
1357 cur_row.operation_seq_num,
1358 cur_row.inventory_item_id_new);
1359
1360 -- check that the new component not exist in same operation.
1361
1362 IF IS_Error(p_group_id,
1363 p_wip_entity_id,
1364 p_organization_id,
1365 p_substitution_type,
1366 cur_row.operation_seq_num,
1367 cur_row.inventory_item_id_old,
1368 cur_row.inventory_item_id_new) = 0 THEN
1369 Safe_Delete (p_group_id,
1370 p_wip_entity_id,
1371 p_organization_id,
1372 p_substitution_type,
1373 cur_row.operation_seq_num,
1374 cur_row.inventory_item_id_old);
1375
1376 IF IS_Error(p_group_id,
1377 p_wip_entity_id,
1378 p_organization_id,
1379 p_substitution_type,
1380 cur_row.operation_seq_num,
1381 cur_row.inventory_item_id_old,
1382 cur_row.inventory_item_id_new) = 0 AND
1383 cur_row.inventory_item_id_new IS NOT NULL THEN
1384
1385 Valid_Requirement (p_group_id,
1386 p_wip_entity_id,
1387 p_organization_id,
1388 p_substitution_type,
1389 cur_row.operation_seq_num,
1390 cur_row.inventory_item_id_new);
1391
1392 IF IS_Error(p_group_id,
1393 p_wip_entity_id,
1394 p_organization_id,
1395 p_substitution_type,
1396 cur_row.operation_seq_num,
1397 cur_row.inventory_item_id_old,
1398 cur_row.inventory_item_id_new) = 0 THEN
1399
1400 WIP_REQUIREMENT_DEFAULT.Default_Requirement(p_group_id,
1401 p_wip_entity_id,
1402 p_organization_id,
1403 p_substitution_type,
1404 cur_row.operation_seq_num,
1405 cur_row.inventory_item_id_old,
1406 cur_row.inventory_item_id_new,
1407 round(cur_row.quantity_per_assembly, 6),
1408 cur_row.basis_type, /* LBM Project */
1409 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
1410 x_err_code,
1411 x_err_msg);
1412
1413 IF x_err_code is null AND
1414 cur_row.inventory_item_id_new IS NOT NULL AND
1415 cur_row.inventory_item_id_new <> cur_row.inventory_item_id_old THEN /*Component Yield Enhancement(Bug 4369064)*/
1416
1417 Post_Default (p_group_id,
1418 p_wip_entity_id,
1419 p_organization_id,
1420 p_substitution_type,
1421 cur_row.operation_seq_num,
1422 cur_row.inventory_item_id_new);
1423 END IF;
1424 END IF;
1425 END IF;
1426 END IF;
1427 END IF;
1428 END IF;
1429 END IF;
1430
1431 /*bug 4202200 -> If the job has no operations , supply_type can not be
1432 operation pull; I have put this condition here since we should also be
1433 catering to cases where inventory_item_id_new is null */
1434
1435 Valid_Requirement_Supply_Type (p_group_id,
1436 --need to fixed in forward port of 4142439 or 4159367
1437 --p_parent_header_id,
1438 p_wip_entity_id,
1439 p_organization_id,
1440 p_substitution_type,
1441 cur_row.operation_seq_num,
1442 cur_row.inventory_item_id_old,
1443 cur_row.inventory_item_id_new);
1444
1445 END LOOP;
1446
1447 END Change_Req;
1448
1449 function IS_Error(p_group_id number,
1450 p_wip_entity_id number,
1451 p_organization_id number,
1452 p_substitution_type number,
1453 p_operation_seq_num number,
1454 p_inventory_item_id_old number,
1455 p_inventory_item_id_new number) return number IS
1456
1457 x_count number := 0;
1458
1459 BEGIN
1460
1461 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
1462 SELECT count(*)
1463 INTO x_count
1464 FROM WIP_JOB_DTLS_INTERFACE
1465 WHERE group_id = p_group_id
1466 AND process_status = WIP_CONSTANTS.ERROR
1467 AND wip_entity_id = p_wip_entity_id
1468 AND organization_id = p_organization_id
1469 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1470 AND substitution_type= p_substitution_type
1471 AND operation_seq_num= p_operation_seq_num
1472 AND inventory_item_id_old = p_inventory_item_id_old;
1473
1474 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
1475 SELECT count(*)
1476 INTO x_count
1477 FROM WIP_JOB_DTLS_INTERFACE
1478 WHERE group_id = p_group_id
1479 AND process_status = WIP_CONSTANTS.ERROR
1480 AND wip_entity_id = p_wip_entity_id
1481 AND organization_id = p_organization_id
1482 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1483 AND substitution_type= p_substitution_type
1484 AND operation_seq_num= p_operation_seq_num
1485 AND inventory_item_id_new = p_inventory_item_id_new;
1486
1487 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
1488 SELECT count(*)
1489 INTO x_count
1490 FROM WIP_JOB_DTLS_INTERFACE
1491 WHERE group_id = p_group_id
1492 AND process_status = WIP_CONSTANTS.ERROR
1493 AND wip_entity_id = p_wip_entity_id
1494 AND organization_id = p_organization_id
1495 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1496 AND substitution_type= p_substitution_type
1497 AND operation_seq_num= p_operation_seq_num
1498 AND inventory_item_id_old = p_inventory_item_id_old
1499 AND inventory_item_id_new = p_inventory_item_id_new;
1500
1501 END IF;
1502
1503 IF x_count <> 0 THEN
1504 return 1;
1505 ELSE
1506 return 0;
1507 END IF;
1508
1509 END IS_Error;
1510
1511 function Info_Missing(p_group_id number,
1512 p_wip_entity_id number,
1513 p_organization_id number,
1514 p_substitution_type number,
1515 p_operation_seq_num number) return number IS
1516
1517 x_count number := 0;
1518
1519 BEGIN
1520
1521 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
1522 SELECT count(*)
1523 INTO x_count
1524 FROM WIP_JOB_DTLS_INTERFACE
1525 WHERE group_id = p_group_id
1526 AND process_status = WIP_CONSTANTS.ERROR
1527 AND wip_entity_id = p_wip_entity_id
1528 AND organization_id = p_organization_id
1529 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1530 AND substitution_type= p_substitution_type
1531 AND operation_seq_num= p_operation_seq_num
1532 AND inventory_item_id_old IS NULL;
1533
1534 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
1535 SELECT count(*)
1536 INTO x_count
1537 FROM WIP_JOB_DTLS_INTERFACE
1538 WHERE group_id = p_group_id
1539 AND process_status = WIP_CONSTANTS.ERROR
1540 AND wip_entity_id = p_wip_entity_id
1541 AND organization_id = p_organization_id
1542 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1543 AND substitution_type= p_substitution_type
1544 AND operation_seq_num= p_operation_seq_num
1545 AND (inventory_item_id_new IS NULL
1546 OR quantity_per_assembly IS NULL);
1547
1548 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
1549 SELECT count(*)
1550 INTO x_count
1551 FROM WIP_JOB_DTLS_INTERFACE
1552 WHERE group_id = p_group_id
1553 AND process_status = WIP_CONSTANTS.ERROR
1554 AND wip_entity_id = p_wip_entity_id
1555 AND organization_id = p_organization_id
1556 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1557 AND substitution_type= p_substitution_type
1558 AND operation_seq_num= p_operation_seq_num
1559 AND (inventory_item_id_old IS NULL
1560 OR quantity_per_assembly IS NULL);
1561
1562 END IF;
1563
1564 IF x_count <> 0 THEN
1565 return 1;
1566 ELSE return 0;
1567 END IF;
1568
1569 END Info_Missing;
1570
1571 END WIP_REQUIREMENT_VALIDATIONS;