[Home] [Help]
PACKAGE BODY: APPS.WIP_REQUIREMENT_VALIDATIONS
Source
1 PACKAGE BODY WIP_REQUIREMENT_VALIDATIONS AS
2 /* $Header: wiprqvdb.pls 120.12.12020000.3 2013/01/24 06:24:04 akuppa ship $ */
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 = p_wip_entity_id
172 and wro.organization_id = p_organization_id
173 and wro.operation_seq_num = p_operation_seq_num
174 and wro.inventory_item_id = p_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 = p_wip_entity_id
193 and mmtt.organization_id = p_organization_id
194 and mmtt.operation_seq_num = p_operation_seq_num
195 and mmtt.inventory_item_id = p_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 = p_wip_entity_id
213 and mmt.organization_id = p_organization_id
214 and mmt.operation_seq_num = p_operation_seq_num
215 and mmt.inventory_item_id = p_inventory_item_id_old);
216
217 l_error_exists boolean := false;
218 begin
219
220 for l_inv_row in c_invalid_wro_rows loop
221 l_error_exists := true;
222 fnd_message.set_name('WIP', 'WIP_JDI_QTY_ISSUED');
223 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
224 if(wip_job_details.std_alone = 1) then
225 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
226 p_text => substr(fnd_message.get,1,500),
227 p_error_type => wip_jdi_utils.msg_error);
228 else
229 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
230 p_text => substr(fnd_message.get,1,500),
231 p_error_type => wip_jdi_utils.msg_error);
232 end if;
233 end loop;
234
235 if(l_error_exists) then
236 update wip_job_dtls_interface wjdi
237 set process_status = wip_constants.error
238 where group_id = p_group_id
239 and process_phase = wip_constants.ml_validation
240 and process_status in (wip_constants.running,
241 wip_constants.warning)
242 and wip_entity_id = p_wip_entity_id
243 and organization_id = p_organization_id
244 and wjdi.load_type = wip_job_details.wip_mtl_requirement
245 and wjdi.substitution_type = p_substitution_type
246 and wjdi.operation_seq_num = p_operation_seq_num
247 and wjdi.inventory_item_id_old = p_inventory_item_id_old
248 and exists (select 1
249 from wip_requirement_operations wro
250 where wro.wip_entity_id = wjdi.wip_entity_id
251 and wro.organization_id = wjdi.organization_id
252 and wro.operation_seq_num = wjdi.operation_seq_num
253 and wro.inventory_item_id = wjdi.inventory_item_id_old
254 and wro.quantity_issued > 0);
255 return;
256 end if;
257
258 for l_inv_row in c_invalid_mmtt_rows loop
259 l_error_exists := true;
260 fnd_message.set_name('WIP', 'WIP_JDI_REQ_JOB_PENDING');
261 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
262 if(wip_job_details.std_alone = 1) then
263 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
264 p_text => substr(fnd_message.get,1,500),
265 p_error_type => wip_jdi_utils.msg_error);
266 else
267 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
268 p_text => substr(fnd_message.get,1,500),
269 p_error_type => wip_jdi_utils.msg_error);
270 end if;
271 end loop;
272
273 if(l_error_exists) then
274 update wip_job_dtls_interface wjdi
275 set process_status = wip_constants.error
276 where group_id = p_group_id
277 and process_phase = wip_constants.ml_validation
278 and process_status in (wip_constants.running,
279 wip_constants.warning)
280 and wip_entity_id = p_wip_entity_id
281 and organization_id = p_organization_id
282 and wjdi.load_type = wip_job_details.wip_mtl_requirement
283 and wjdi.substitution_type = p_substitution_type
284 and wjdi.operation_seq_num = p_operation_seq_num
285 and wjdi.inventory_item_id_old = p_inventory_item_id_old
286 and exists (select 1
287 from mtl_material_transactions_temp mmtt
288 where mmtt.transaction_source_id = wjdi.wip_entity_id
289 and mmtt.organization_id = wjdi.organization_id
290 and mmtt.operation_seq_num = wjdi.operation_seq_num
291 and mmtt.inventory_item_id = wjdi.inventory_item_id_old);
292 return;
293 end if;
294
295 for l_inv_row in c_invalid_mmt_rows loop
296 l_error_exists := true;
297 fnd_message.set_name('WIP', 'WIP_JDI_REQ_JOB_PENDING');
298 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
299 if(wip_job_details.std_alone = 1) then
300 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
301 p_text => substr(fnd_message.get,1,500),
302 p_error_type => wip_jdi_utils.msg_error);
303 else
304 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
305 p_text => substr(fnd_message.get,1,500),
306 p_error_type => wip_jdi_utils.msg_error);
307 end if;
308 end loop;
309
310 if(l_error_exists) then
311 update wip_job_dtls_interface wjdi
312 set process_status = wip_constants.error
313 where group_id = p_group_id
314 and process_phase = wip_constants.ml_validation
315 and process_status in (wip_constants.running,
316 wip_constants.warning)
317 and wip_entity_id = p_wip_entity_id
318 and organization_id = p_organization_id
319 and wjdi.load_type = wip_job_details.wip_mtl_requirement
320 and wjdi.substitution_type = p_substitution_type
321 and wjdi.operation_seq_num = p_operation_seq_num
322 and wjdi.inventory_item_id_old = p_inventory_item_id_old
323 and exists (select 1
324 from mtl_material_transactions mmt
325 where mmt.transaction_source_id = wjdi.wip_entity_id
326 and mmt.organization_id = wjdi.organization_id
327 and mmt.operation_seq_num = wjdi.operation_seq_num
328 and mmt.inventory_item_id = wjdi.inventory_item_id_old);
329 end if;
330 end safe_delete;
331
332
333 /* main delete, call the above. If any validation fail, it won''t go on
334 with the next validations */
335 Procedure Delete_Req(p_group_id in number,
336 p_wip_entity_id in number,
337 p_organization_id in number,
338 p_substitution_type in number) IS
339
340 CURSOR req_info(p_group_Id number,
341 p_wip_entity_id number,
342 p_organization_id number,
343 p_substitution_type number) IS
344 SELECT distinct operation_seq_num,
345 inventory_item_id_old, inventory_item_id_new,
346 quantity_per_assembly,
347 last_update_date, last_updated_by, creation_date, created_by,
348 last_update_login, request_id, program_application_id,
349 program_id, program_update_date,
350 department_id, wip_supply_type, date_required,
351 required_quantity, quantity_issued, supply_subinventory,
352 supply_locator_id, mrp_net_flag, mps_required_quantity,
353 mps_date_required, attribute_category, attribute1,
354 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
355 attribute8,attribute9,attribute10,attribute11,attribute12,
356 attribute13,attribute14,attribute15
357 FROM WIP_JOB_DTLS_INTERFACE
358 WHERE group_id = p_group_id
359 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
360 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
361 AND wip_entity_id = p_wip_entity_id
362 AND organization_id = p_organization_id
363 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
364 AND substitution_type = p_substitution_type;
365
366 BEGIN
367 FOR cur_row IN req_info(p_group_id,
368 p_wip_entity_id,
369 p_organization_id,
370 p_substitution_type) LOOP
371 Del_Req_Info_Exist(p_group_id,
372 p_wip_entity_id,
373 p_organization_id,
374 p_substitution_type,
375 cur_row.operation_seq_num);
376
377 IF Info_Missing(p_group_id,
378 p_wip_entity_id,
379 p_organization_id,
380 p_substitution_type,
381 cur_row.operation_seq_num) = 0 THEN
382
383 REQ_JOB_Match (p_group_id,
384 p_wip_entity_id,
385 p_organization_id,
386 p_substitution_type,
387 cur_row.operation_seq_num,
388 cur_row.inventory_item_id_old);
389
390 IF IS_Error(p_group_id,
391 p_wip_entity_id,
392 p_organization_id,
393 p_substitution_type,
394 cur_row.operation_seq_num,
395 cur_row.inventory_item_id_old,
396 cur_row.inventory_item_id_new) = 0 THEN
397
398 Safe_Delete (p_group_id,
399 p_wip_entity_id,
400 p_organization_id,
401 p_substitution_type,
402 cur_row.operation_seq_num,
403 cur_row.inventory_item_id_old);
404
405 END IF;
406 END IF;
407 END LOOP;
408 END Delete_Req;
409
410
411 procedure add_req_info_exist(p_group_id in number,
412 p_wip_entity_id in number,
413 p_organization_id in number,
414 p_substitution_type in number,
415 p_operation_seq_num in number) IS
416 cursor c_invalid_rows is
417 select interface_id
418 from wip_job_dtls_interface wjdi
419 where wjdi.group_id = p_group_id
420 and wjdi.process_phase = wip_constants.ml_validation
421 and wjdi.process_status in (wip_constants.running,
422 wip_constants.warning)
423 and wjdi.wip_entity_id = p_wip_entity_id
424 and wjdi.organization_id = p_organization_id
425 and wjdi.load_type = wip_job_details.wip_mtl_requirement
426 and wjdi.substitution_type = p_substitution_type
427 and wjdi.operation_seq_num = p_operation_seq_num
428 and ( wjdi.inventory_item_id_new is null
429 or wjdi.quantity_per_assembly is null);
430
431 l_error_exists boolean := false;
432 begin
433
434 for l_inv_row in c_invalid_rows loop
435 l_error_exists := true;
436 fnd_message.set_name('WIP', 'WIP_JDI_ADD_REQ_INFO_MISSING');
437 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
438 if(wip_job_details.std_alone = 1) then
439 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
440 p_text => substr(fnd_message.get,1,500),
441 p_error_type => wip_jdi_utils.msg_error);
442 else
443 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
444 p_text => substr(fnd_message.get,1,500),
445 p_error_type => wip_jdi_utils.msg_error);
446 end if;
447 end loop;
448
449 if(l_error_exists) then
450 update wip_job_dtls_interface wjdi
451 set process_status = wip_constants.error
452 where group_id = p_group_id
453 and process_phase = wip_constants.ml_validation
454 and process_status in (wip_constants.running,
455 wip_constants.warning)
456 and wip_entity_id = p_wip_entity_id
457 and organization_id = p_organization_id
458 and wjdi.load_type = wip_job_details.wip_mtl_requirement
459 and wjdi.substitution_type = p_substitution_type
460 and wjdi.operation_seq_num = p_operation_seq_num
461 and ( wjdi.inventory_item_id_new is null
462 or wjdi.quantity_per_assembly is null);
463 end if;
464 end add_req_info_exist;
465
466
467
468 /* operations, requirements, should NOT exist; for add/change *
469 check for duplicate requirement/operations */
470 procedure req_job_not_exist (p_group_id in number,
471 p_wip_entity_id in number,
472 p_organization_id in number,
473 p_substitution_type in number,
474 p_operation_seq_num in number,
475 p_inventory_item_id_new in number) IS
476 cursor c_invalid_rows is
477 select interface_id
478 from wip_job_dtls_interface wjdi
479 where wjdi.group_id = p_group_id
480 and wjdi.process_phase = wip_constants.ml_validation
481 and wjdi.process_status in (wip_constants.running,
482 wip_constants.warning)
483 and wjdi.wip_entity_id = p_wip_entity_id
484 and wjdi.organization_id = p_organization_id
485 and wjdi.load_type = wip_job_details.wip_mtl_requirement
486 and wjdi.substitution_type = p_substitution_type
487 and wjdi.operation_seq_num = p_operation_seq_num
488 and wjdi.inventory_item_id_new = p_inventory_item_id_new
489 /* bug#2814045 */
490 and nvl(wjdi.inventory_item_id_new, -1) <> nvl(wjdi.inventory_item_id_old, -1)
491 and ( exists (select 1
492 from wip_requirement_operations wro
493 where wro.wip_entity_id = wjdi.wip_entity_id
494 and wro.organization_id = wjdi.organization_id
495 and wro.operation_seq_num = wjdi.operation_seq_num
496 and wro.inventory_item_id = wjdi.inventory_item_id_new)
497 or exists (select 1
498 from wip_job_dtls_interface wjdi2
499 where wjdi.interface_id <> wjdi2.interface_id
500 and wjdi.group_id = wjdi2.group_id
501 and wjdi.wip_entity_id = wjdi2.wip_entity_id
502 and wjdi.organization_id = wjdi2.organization_id
503 and wjdi.operation_seq_num = wjdi2.operation_seq_num
504 and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
505 );
506
507 l_error_exists boolean := false;
508 begin
509
510 for l_inv_row in c_invalid_rows loop
511 l_error_exists := true;
512 fnd_message.set_name('WIP', 'WIP_JDI_REQ_EXIST');
513 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
514 if(wip_job_details.std_alone = 1) then
515 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
516 p_text => substr(fnd_message.get,1,500),
517 p_error_type => wip_jdi_utils.msg_error);
518 else
519 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
520 p_text => substr(fnd_message.get,1,500),
521 p_error_type => wip_jdi_utils.msg_error);
522 end if;
523 end loop;
524
525 if(l_error_exists) then
526 update wip_job_dtls_interface wjdi
527 set process_status = wip_constants.error
528 where group_id = p_group_id
529 and process_phase = wip_constants.ml_validation
530 and process_status in (wip_constants.running,
531 wip_constants.warning)
532 and wip_entity_id = p_wip_entity_id
533 and organization_id = p_organization_id
534 and wjdi.load_type = wip_job_details.wip_mtl_requirement
535 and wjdi.substitution_type = p_substitution_type
536 and wjdi.operation_seq_num = p_operation_seq_num
537 and wjdi.inventory_item_id_new = p_inventory_item_id_new
538 /* Fix for Bug 5632150 */
539 /* and wjdi.inventory_item_id_new <> wjdi.inventory_item_id_old */
540 and nvl(wjdi.inventory_item_id_new,-1) <> nvl(wjdi.inventory_item_id_old,-1)
541 and ( exists (select 1
542 from wip_requirement_operations wro
543 where wro.wip_entity_id = wjdi.wip_entity_id
544 and wro.organization_id = wjdi.organization_id
545 and wro.operation_seq_num = wjdi.operation_seq_num
546 and wro.inventory_item_id = wjdi.inventory_item_id_new)
547 or exists (select 1
548 from wip_job_dtls_interface wjdi2
549 where wjdi.interface_id <> wjdi2.interface_id
550 and wjdi.group_id = wjdi2.group_id
551 and wjdi.wip_entity_id = wjdi2.wip_entity_id
552 and wjdi.organization_id = wjdi2.organization_id
553 and wjdi.operation_seq_num = wjdi2.operation_seq_num
554 and wjdi.inventory_item_id_new= wjdi2.inventory_item_id_new)
555 );
556 end if;
557 end req_job_not_exist;
558
559
560
561 /* for add/change only */
562 procedure valid_requirement(p_group_id in number,
563 p_wip_entity_id in number,
564 p_organization_id in number,
565 p_substitution_type in number,
566 p_operation_seq_num in number,
567 p_inventory_item_id_new in number) IS
568 cursor c_invalid_rows is
569 select interface_id
570 from wip_job_dtls_interface wjdi
571 where wjdi.group_id = p_group_id
572 and wjdi.process_phase = wip_constants.ml_validation
573 and wjdi.process_status in (wip_constants.running,
574 wip_constants.warning)
575 and wjdi.wip_entity_id = p_wip_entity_id
576 and wjdi.organization_id = p_organization_id
577 and wjdi.load_type = wip_job_details.wip_mtl_requirement
578 and wjdi.substitution_type = p_substitution_type
579 and wjdi.operation_seq_num = p_operation_seq_num
580 and wjdi.inventory_item_id_new = p_inventory_item_id_new
581 and not exists (select 1
582 from mtl_system_items msi
583 where msi.inventory_item_id = wjdi.inventory_item_id_new
584 and msi.organization_id = wjdi.organization_id);
585
586 /* bug#2811687 : begin */
587 cursor c_supply_types_invalid_rows is
588 select wjdi.interface_id
589 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
590 where wjdi.group_id = p_group_id
591 and wjdi.process_phase = wip_constants.ml_validation
592 and wjdi.process_status in (wip_constants.running,
593 wip_constants.warning)
594 and wjdi.wip_entity_id = p_wip_entity_id
595 and wjdi.organization_id = p_organization_id
596 and wjdi.load_type = wip_job_details.wip_mtl_requirement
597 and wjdi.substitution_type = p_substitution_type
598 and wjdi.operation_seq_num = p_operation_seq_num
599 and wjdi.inventory_item_id_new = p_inventory_item_id_new
600 and wjdi.parent_header_id = wjsi.header_id
601 and wjdi.group_id = wjsi.group_id
602 and wjdi.organization_id = wjsi.organization_id
603 and wjdi.wip_entity_id = wjsi.wip_entity_id
604 and (wjdi.wip_supply_type = wip_constants.phantom
605 or (wjsi.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)
606 and wjdi.wip_supply_type <> wip_constants.push));
607
608 cursor c_mrp_net_flag_invalid_rows is
609 select wjdi.interface_id
610 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
611 where wjdi.group_id = p_group_id
612 and wjdi.process_phase = wip_constants.ml_validation
613 and wjdi.process_status in (wip_constants.running,
614 wip_constants.warning)
615 and wjdi.wip_entity_id = p_wip_entity_id
616 and wjdi.organization_id = p_organization_id
617 and wjdi.load_type = wip_job_details.wip_mtl_requirement
618 and wjdi.substitution_type = p_substitution_type
619 and wjdi.operation_seq_num = p_operation_seq_num
620 and wjdi.inventory_item_id_new = p_inventory_item_id_new
621 and wjdi.parent_header_id = wjsi.header_id
622 and wjdi.group_id = wjsi.group_id
623 and wjdi.organization_id = wjsi.organization_id
624 and wjdi.wip_entity_id = wjsi.wip_entity_id
625 and (wjdi.mrp_net_flag not in (wip_constants.yes, wip_constants.no));
626
627 cursor c_auto_req_mat_invalid_rows is
628 select wjdi.interface_id
629 from wip_job_dtls_interface wjdi, wip_job_schedule_interface wjsi
630 where wjdi.group_id = p_group_id
631 and wjdi.process_phase = wip_constants.ml_validation
632 and wjdi.process_status in (wip_constants.running,
633 wip_constants.warning)
634 and wjdi.wip_entity_id = p_wip_entity_id
635 and wjdi.organization_id = p_organization_id
636 and wjdi.load_type = wip_job_details.wip_mtl_requirement
637 and wjdi.substitution_type = p_substitution_type
638 and wjdi.operation_seq_num = p_operation_seq_num
639 and wjdi.inventory_item_id_new = p_inventory_item_id_new
640 and wjdi.parent_header_id = wjsi.header_id
641 and wjdi.group_id = wjsi.group_id
642 and wjdi.organization_id = wjsi.organization_id
643 and wjdi.wip_entity_id = wjsi.wip_entity_id
644 and (upper(wjdi.auto_request_material) not in ('Y', 'N'));
645 /* bug#2814045 : end */
646
647 /* bug 3112793 */
648 cursor c_direct_item_rows(p_profile_value IN number) is
649 select interface_id
650 from wip_job_dtls_interface wjdi
651 where wjdi.group_id=p_group_id
652 and wjdi.process_phase = wip_constants.ml_validation
653 and wjdi.process_status in ( wip_constants.running,
654 wip_constants.warning )
655 and wjdi.wip_entity_id = p_wip_entity_id
656 and wjdi.organization_id = p_organization_id
657 and wjdi.load_type = wip_job_details.wip_mtl_requirement
658 and wjdi.substitution_type = p_substitution_type
659 and wjdi.operation_seq_num = p_operation_seq_num
660 and wjdi.inventory_item_id_new = p_inventory_item_id_new
661 and NOT exists ( select 1 from mtl_system_items msi
662 where msi.inventory_item_id = wjdi.inventory_item_id_new
663 and msi.organization_id = wjdi.organization_id
664 and BOM_ENABLED_FLAG = 'Y'
665 and BOM_ITEM_TYPE = 4
666 and (( p_profile_value = WIP_CONSTANTS.YES)
667 or (ENG_ITEM_FLAG = 'N' and p_profile_value = WIP_CONSTANTS.NO))
668 );
669 /* end of 3112793 */
670
671 l_profile_value number ;
672 l_error_exists boolean := false;
673 begin
674
675 for l_inv_row in c_invalid_rows loop
676 l_error_exists := true;
677 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_MTL_REQ');
678 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
679 if(wip_job_details.std_alone = 1) then
680 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
681 p_text => substr(fnd_message.get,1,500),
682 p_error_type => wip_jdi_utils.msg_error);
683 else
684 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
685 p_text => substr(fnd_message.get,1,500),
686 p_error_type => wip_jdi_utils.msg_error);
687 end if;
688 end loop;
689
690 /* bug#2811687 : begin */
691 for l_inv_row in c_supply_types_invalid_rows loop
692 l_error_exists := true;
693 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SUPPLY_TYPE');
694 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
695 if(wip_job_details.std_alone = 1) then
696 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
697 p_text => substr(fnd_message.get,1,500),
698 p_error_type => wip_jdi_utils.msg_error);
699 else
700 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
701 p_text => substr(fnd_message.get,1,500),
702 p_error_type => wip_jdi_utils.msg_error);
703 end if;
704 end loop;
705
706 for l_inv_row in c_mrp_net_flag_invalid_rows loop
707 l_error_exists := true;
708 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_MRP_NET_FLAG');
709 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
710 if(wip_job_details.std_alone = 1) then
711 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
712 p_text => substr(fnd_message.get,1,500),
713 p_error_type => wip_jdi_utils.msg_error);
714 else
715 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
716 p_text => substr(fnd_message.get,1,500),
717 p_error_type => wip_jdi_utils.msg_error);
718 end if;
719 end loop;
720
721 for l_inv_row in c_auto_req_mat_invalid_rows loop
722 l_error_exists := true;
723 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_AUTO_REQ_MAT');
724 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
725 if(wip_job_details.std_alone = 1) then
726 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
727 p_text => substr(fnd_message.get,1,500),
728 p_error_type => wip_jdi_utils.msg_error);
729 else
730 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
731 p_text => substr(fnd_message.get,1,500),
732 p_error_type => wip_jdi_utils.msg_error);
733 end if;
734 end loop;
735 /* bug#2811687 : end */
736
737 l_profile_value := fnd_profile.value('WIP_SEE_ENG_ITEMS');
738 for l_inv_row in c_direct_item_rows ( l_profile_value ) loop
739 l_error_exists := true ;
740 fnd_message.set_name('WIP','WIP_JDI_DIRECT_ITEM');
741 fnd_message.set_token('INTERFACE',to_char(l_inv_row.interface_id));
742 if(wip_job_details.std_alone =1 ) then
743 wip_interface_err_utils.add_error (
744 p_interface_id => l_inv_row.interface_id,
745 p_text => substr(fnd_message.get,1,500),
746 p_error_type => wip_jdi_utils.msg_error);
747 else
748 wip_interface_err_utils.add_error (
749 p_interface_id => wip_jsi_utils.current_interface_id,
750 p_text => substr(fnd_message.get,1,500),
751 p_error_type => wip_jdi_utils.msg_error);
752 end if ;
753 end loop;
754
755 if(l_error_exists) then
756 update wip_job_dtls_interface wjdi
757 set process_status = wip_constants.error
758 where group_id = p_group_id
759 and process_phase = wip_constants.ml_validation
760 and process_status in (wip_constants.running,
761 wip_constants.warning)
762 and wip_entity_id = p_wip_entity_id
763 and organization_id = p_organization_id
764 and wjdi.load_type = wip_job_details.wip_mtl_requirement
765 and wjdi.substitution_type = p_substitution_type
766 and wjdi.operation_seq_num = p_operation_seq_num
767 and wjdi.inventory_item_id_new = p_inventory_item_id_new;
768 /* bug#2811687
769 and not exists (select 1
770 from mtl_system_items msi
771 where msi.inventory_item_id = wjdi.inventory_item_id_new
772 and msi.organization_id = wjdi.organization_id);
773 */
774 end if;
775
776 end valid_requirement;
777
778
779 /*Bug 4202200 */
780 procedure valid_requirement_supply_type
781 (p_group_id in number,
782 --need to fixed in forward port of 4142439 or 4159367, also see sql
783 --p_parent_header_id in number,
784 p_wip_entity_id in number,
785 p_organization_id in number,
786 p_substitution_type in number,
787 p_operation_seq_num in number,
788 p_inventory_item_id_old in number,
789 p_inventory_item_id_new in number) IS
790
791 /* This query is modified for bug 5216025. The join with WJSI is not required as the validation happens
792 only if there is a parent record in wip_job_schedule_interface for the same job.
793 */
794 cursor c_supply_types_invalid_rows is
795 select wjdi.interface_id
796 from wip_job_dtls_interface wjdi /*, wip_job_schedule_interface wjsi */
797 where wjdi.group_id = p_group_id
798 and wjdi.process_phase = wip_constants.ml_validation
799 and wjdi.process_status in (wip_constants.running,
800 wip_constants.warning)
801 and wjdi.wip_entity_id = p_wip_entity_id
802 and wjdi.organization_id = p_organization_id
803 and wjdi.load_type = wip_job_details.wip_mtl_requirement
804 and wjdi.substitution_type = p_substitution_type
805 and wjdi.operation_seq_num = p_operation_seq_num
806 and (wjdi.inventory_item_id_new = p_inventory_item_id_new
807 or p_inventory_item_id_new is null)
808 and (wjdi.inventory_item_id_old = p_inventory_item_id_old
809 or p_inventory_item_id_old is null)
810 and (p_inventory_item_id_old is not null or
811 p_inventory_item_id_new is not null)
812 /*and (wjdi.parent_header_id = p_parent_header_id or
813 WIP_JOB_DETAILS.STD_ALONE = 1)*/
814 /*and wjdi.organization_id = wjsi.organization_id
815 and wjdi.wip_entity_id = wjsi.wip_entity_id*/
816 and wjdi.wip_supply_type = wip_constants.op_pull
817 and not exists
818 (select 1 from wip_operations
819 where wip_entity_id = wjdi.wip_entity_id
820 and organization_id = wjdi.organization_id);
821
822
823 l_error_exists boolean := false;
824 begin
825
826 for l_inv_row in c_supply_types_invalid_rows loop
827 l_error_exists := true;
828 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_SUP_TYPE_NO_OP');
829 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
830 if(wip_job_details.std_alone = 1) then
831 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
832 p_text => substr(fnd_message.get,1,500),
833 p_error_type => wip_jdi_utils.msg_error);
834 else
835 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
836 p_text => substr(fnd_message.get,1,500),
837 p_error_type => wip_jdi_utils.msg_error);
838 end if;
839
840 update wip_job_dtls_interface wjdi
841 set process_status = wip_constants.error
842 where interface_id = l_inv_row.interface_id;
843 end loop;
844
845 end valid_requirement_supply_type;
846 /*End of Bug Fix 4202200 */
847
848 Procedure Valid_Req_subinvlocator(p_group_id in number,
849 p_wip_entity_id in number,
850 p_organization_id in number,
851 p_substitution_type in number) is
852
853 CURSOR addsubinv_check IS
854 SELECT interface_id,inventory_item_id_new inventory_item_id,supply_subinventory,supply_locator_id,wip_supply_type
855 FROM WIP_JOB_DTLS_INTERFACE
856 WHERE group_id=p_group_id
857 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
858 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
859 AND wip_entity_id = p_wip_entity_id
860 AND organization_id = p_organization_id
861 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
862 AND substitution_type = WIP_JOB_DETAILS.WIP_ADD
863 AND supply_subinventory is not null;
864 CURSOR changesubinv_check IS
865 SELECT wjdi.interface_id,NVL(wjdi.inventory_item_id_new,wjdi.inventory_item_id_old) inventory_item_id,Decode(NVL(wjdi.wip_supply_type, wro.wip_supply_type),
866 2, Decode(wjdi.supply_subinventory,
867 NULL,
868 wro.supply_subinventory,
869 fnd_api.g_miss_char,
870 wro.supply_subinventory,
871 wjdi.supply_subinventory),
872 3, Decode(wjdi.supply_subinventory,
873 NULL,
874 wro.supply_subinventory,
875 fnd_api.g_miss_char,
876 wro.supply_subinventory,
877 wjdi.supply_subinventory),
878 Decode(wjdi.supply_subinventory,
879 NULL,
880 wro.supply_subinventory,
881 fnd_api.g_miss_char,
882 NULL,
883 wjdi.supply_subinventory)) supply_subinventory,
884 Decode(wjdi.supply_subinventory,
885 NULL,
886 wro.supply_locator_id,
887 fnd_api.g_miss_char,
888 Decode(NVL(wjdi.wip_supply_type, wro.wip_supply_type),
889 2, wro.supply_locator_id,
890 3, wro.supply_locator_id,
891 NULL),
892 Decode(wjdi.supply_locator_id,
893 fnd_api.g_miss_num,
894 NULL,
895 NULL,
896 wro.supply_locator_id,
897 wjdi.supply_locator_id)) supply_locator_id
898 ,NVL(WJDI.wip_supply_Type,wro.wip_supply_type) wip_supply_type
899 FROM WIP_JOB_DTLS_INTERFACE wjdi,WIP_REQUIREMENT_OPERATIONS wro
900 WHERE group_id=p_group_id
901 AND wjdi.process_phase = WIP_CONSTANTS.ML_VALIDATION
902 AND wjdi.process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
903 AND wjdi.wip_entity_id = p_wip_entity_id
904 AND wro.wip_entity_id=wjdi.wip_entity_id
905 AND wro.operation_seq_num=wjdi.operation_seq_num
906 AND wro.inventory_item_id=wjdi.inventory_item_id_old
907 AND wjdi.organization_id = p_organization_id
908 AND wjdi.load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
909 AND wjdi.substitution_type = WIP_JOB_DETAILS.WIP_CHANGE;
910 l_msg VARCHAR2(100);
911 l_inventory_itemid NUMBER;
912 rec changesubinv_check%ROWTYPE;
913 TYPE errorrec is RECORD ( interface_id NUMBER,
914 inventory_item_id NUMBER,
915 supply_subinventory WIP_REQUIREMENT_OPERATIONS.supply_subinventory%TYPE,
916 error_msg VARCHAR2(500));
917 TYPE error_typ IS TABLE OF errorrec INDEX BY BINARY_INTEGER;
918 error_tbl error_typ;
919 l_subinventory WIP_REQUIREMENT_OPERATIONS.supply_subinventory%TYPE;
920 l_wipsupply NUMBER;
921 l_locatorid NUMBER;
922 l_inventoryitem NUMBER;
923 l_interfaceid NUMBER;
924 l_orgLocCtl NUMBER;
925 l_subLocCtl NUMBER;
926 l_project_id NUMBER;
927 l_task_id NUMBER;
928 l_success BOOLEAN;
929 l_cmplocatorsgmt WIP_JOB_SCHEDULE_INTERFACE.COMPLETION_LOCATOR_SEGMENTS%TYPE ;
930 l_locationcontrol NUMBER;
931 l_restriclocation NUMBER;
932 l_locexception EXCEPTION;
933 l_dummy2 VARCHAR2(1);
934 l_projectLocID number;
935 l_indx NUMBER:=1;
936 BEGIN
937 IF p_substitution_type= WIP_JOB_DETAILS.WIP_ADD THEN
938 OPEN addsubinv_check;
939 ELSE
940 OPEN changesubinv_check;
941 END IF;
942
943 Loop
944 IF p_substitution_type= WIP_JOB_DETAILS.WIP_ADD THEN
945 FETCH addsubinv_check into REC;
946 EXIT WHEN addsubinv_check%NOTFOUND;
947 ELSE
948 FETCH changesubinv_check into REC;
949 EXIT WHEN changesubinv_check%NOTFOUND;
950 END IF;
951 IF rec.supply_subinventory is not null then
952 l_inventory_itemid:=rec.inventory_item_id;
953 l_interfaceid:=rec.interface_id;
954 l_subinventory:=rec.supply_subinventory;
955 l_locatorid:=rec.supply_locator_id;
956 l_msg := 'WIP_JDI_INVALID_SUBINV';
957 BEGIN
958 select msi.restrict_locators_code,msi.location_control_code,
959 sub.locator_type, mp.stock_locator_control_code,
960 wdj.project_id,wdj.task_id
961 into l_restriclocation,
962 l_locationcontrol,
963 l_subLocCtl,
964 l_orgLocCtl,
965 l_project_id,l_task_id
966 from mtl_system_items msi,wip_discrete_jobs wdj,mtl_secondary_inventories sub, mtl_parameters mp
967 where msi.inventory_item_id = l_inventory_itemid
968 and msi.organization_id = p_organization_id
969 and wdj.organization_id=msi.organization_id
970 and wdj.wip_entity_id=p_wip_entity_id
971 and sub.secondary_inventory_name = l_subinventory
972 and sub.organization_id = mp.organization_id
973 and mp.organization_id = p_organization_id;
974 EXCEPTION
975 WHEN NO_DATA_FOUND THEN
976 error_tbl(l_indx).interface_id:=l_interfaceid;
977 error_tbl(l_indx).inventory_item_id:=l_inventory_itemid;
978 error_tbl(l_indx).supply_subinventory:=l_subinventory;
979 error_tbl(l_indx).error_msg:=l_msg;
980 l_indx:=l_indx+1;
981 goto end_loop;
982 END;
983 wip_locator.validate(
984 p_organization_id => p_organization_id,
985 p_item_id => l_inventory_itemid,
986 p_subinventory_code => l_subinventory,
987 p_org_loc_control => l_orgLocCtl,
988 p_sub_loc_control => l_subLocCtl,
989 p_item_loc_control => l_locationcontrol,
990 p_restrict_flag => l_restriclocation,
991 p_neg_flag => '',
992 p_action => '',
993 p_project_id => l_project_id,
994 p_task_id => l_task_id,
995 p_locator_id => l_locatorid,
996 p_locator_segments => l_cmplocatorsgmt,
997 p_success_flag => l_success);
998
999 if ( not l_success ) then
1000 error_tbl(l_indx).interface_id:=l_interfaceid;
1001 error_tbl(l_indx).inventory_item_id:=l_inventory_itemid;
1002 error_tbl(l_indx).supply_subinventory:=l_subinventory;
1003 error_tbl(l_indx).error_msg:=l_msg;
1004 l_indx:=l_indx+1;
1005 goto end_loop;
1006 end if;
1007
1008 if ( l_project_id is not null) then
1009 if(pjm_project_locator.check_itemLocatorControl(p_organization_id,
1010 l_subinventory,
1011 l_locatorid,
1012 l_inventoryitem,
1013 2)) then
1014 pjm_project_locator.get_defaultProjectLocator(p_organization_id,
1015 l_locatorid,
1016 l_project_id,
1017 l_task_id,
1018 l_projectLocID);
1019 if ( l_projectLocID is not null ) then
1020 l_locatorid := l_projectLocID;
1021 if(not pjm_project_locator.check_project_references(p_organization_id,
1022 l_projectLocID,
1023 'SPECIFIC', -- validation mode
1024 'Y', -- required?
1025 l_project_id,
1026 l_task_id)) then
1027 l_msg := 'WIP_JDI_INVALID_LOC_PROJ_TASK';
1028 error_tbl(l_indx).interface_id:=l_interfaceid;
1029 error_tbl(l_indx).inventory_item_id:=l_inventory_itemid;
1030 error_tbl(l_indx).supply_subinventory:=l_subinventory;
1031 error_tbl(l_indx).error_msg:=l_msg;
1032 l_indx:=l_indx+1;
1033 goto end_loop;
1034 end if;
1035 end if;
1036 end if;
1037 end if;
1038 end if; -- IF rec.supply_subinventory is not null
1039 <<end_loop>>
1040 NULL;
1041 END LOOP;
1042 IF error_tbl.COUNT>0 THEN
1043 for indx in error_tbl.first..error_tbl.last loop
1044 fnd_message.set_name('WIP', error_tbl(indx).error_msg);
1045 fnd_message.set_token('INTERFACE', to_char(error_tbl(indx).interface_id));
1046 fnd_message.set_token('ITEM_ID', to_char(error_tbl(indx).inventory_item_id));
1047 wip_logger.log('Error in Valid_Req_subinvlocator,inventory_item_id='||to_char(error_tbl(indx).inventory_item_id),l_dummy2);
1048 wip_logger.log('Error in Valid_Req_subinvlocator,error_tbl(indx).error_msg='||error_tbl(indx).error_msg||';interface_id='||to_char(error_tbl(indx).interface_id),l_dummy2);
1049 fnd_message.set_token('SUBINV', error_tbl(indx).supply_subinventory);
1050 wip_interface_err_Utils.add_error(p_interface_id => error_tbl(indx).interface_id,
1051 p_text => substr(fnd_message.get,1,500),
1052 p_error_type => wip_jdi_utils.msg_error);
1053 update wip_job_dtls_interface wjdi
1054 set process_status = wip_constants.error
1055 where interface_id = error_tbl(indx).interface_id;
1056
1057 end loop;
1058 END IF;
1059
1060 IF p_substitution_type= WIP_JOB_DETAILS.WIP_ADD THEN
1061 close addsubinv_check;
1062 ELSE
1063 close changesubinv_check;
1064 END IF;
1065
1066 end Valid_Req_subinvlocator;
1067
1068 /* main add, call the above */
1069 Procedure add_Req(p_group_id in number,
1070 p_wip_entity_id in number,
1071 p_organization_id in number,
1072 p_substitution_type in number) IS
1073
1074 x_err_code varchar2(30) := null;
1075 x_err_msg varchar2(240) := NULL;
1076
1077 CURSOR req_info(p_group_Id number,
1078 p_wip_entity_id number,
1079 p_organization_id number,
1080 p_substitution_type number) IS
1081 SELECT distinct operation_seq_num,
1082 inventory_item_id_old, inventory_item_id_new,
1083 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1084 last_update_date, last_updated_by, creation_date, created_by,
1085 last_update_login, request_id, program_application_id,
1086 program_id, program_update_date,
1087 department_id, wip_supply_type, date_required,
1088 required_quantity, quantity_issued,
1089 basis_type, /* LBM Project */
1090 supply_subinventory,
1091 supply_locator_id, mrp_net_flag, mps_required_quantity,
1092 mps_date_required, attribute_category, attribute1,
1093 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1094 attribute8,attribute9,attribute10,attribute11,attribute12,
1095 attribute13,attribute14,attribute15
1096 FROM WIP_JOB_DTLS_INTERFACE
1097 WHERE group_id = p_group_id
1098 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1099 AND process_status in (WIP_CONSTANTS.RUNNING,WIP_CONSTANTS.WARNING)
1100 AND wip_entity_id = p_wip_entity_id
1101 AND organization_id = p_organization_id
1102 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1103 AND substitution_type = p_substitution_type;
1104
1105 BEGIN
1106 FOR cur_row IN req_info(p_group_id,
1107 p_wip_entity_id,
1108 p_organization_id,
1109 p_substitution_type) LOOP
1110
1111 Add_Req_Info_Exist(p_group_id,
1112 p_wip_entity_id,
1113 p_organization_id,
1114 p_substitution_type,
1115 cur_row.operation_seq_num);
1116
1117 IF Info_Missing(p_group_id,
1118 p_wip_entity_id,
1119 p_organization_id,
1120 p_substitution_type,
1121 cur_row.operation_seq_num) = 0 THEN
1122
1123 REQ_JOB_NOT_EXIST (p_group_id,
1124 p_wip_entity_id,
1125 p_organization_id,
1126 p_substitution_type,
1127 cur_row.operation_seq_num,
1128 cur_row.inventory_item_id_new);
1129
1130 IF IS_Error(p_group_id,
1131 p_wip_entity_id,
1132 p_organization_id,
1133 p_substitution_type,
1134 cur_row.operation_seq_num,
1135 cur_row.inventory_item_id_old,
1136 cur_row.inventory_item_id_new) = 0 THEN
1137
1138 Valid_Requirement (p_group_id,
1139 p_wip_entity_id,
1140 p_organization_id,
1141 p_substitution_type,
1142 cur_row.operation_seq_num,
1143 cur_row.inventory_item_id_new);
1144
1145 IF IS_Error(p_group_id,
1146 p_wip_entity_id,
1147 p_organization_id,
1148 p_substitution_type,
1149 cur_row.operation_seq_num,
1150 cur_row.inventory_item_id_old,
1151 cur_row.inventory_item_id_new) = 0 THEN
1152
1153 WIP_REQUIREMENT_DEFAULT.Default_Requirement(
1154 p_group_id,
1155 p_wip_entity_id,
1156 p_organization_id,
1157 p_substitution_type,
1158 cur_row.operation_seq_num,
1159 cur_row.inventory_item_id_old,
1160 cur_row.inventory_item_id_new,
1161 round(cur_row.quantity_per_assembly, 6),
1162 cur_row.basis_type, /* LBM Project */
1163 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
1164 x_err_code,
1165 x_err_msg);
1166
1167 IF x_err_code is null THEN
1168 Post_Default (p_group_id,
1169 p_wip_entity_id,
1170 p_organization_id,
1171 p_substitution_type,
1172 cur_row.operation_seq_num,
1173 cur_row.inventory_item_id_new);
1174 END IF;
1175 END IF;
1176 END IF;
1177 END IF;
1178
1179 /*bug 4202200
1180 If the job has no operations , supply_type can not be
1181 operation pull
1182 */
1183
1184 Valid_Requirement_Supply_Type (p_group_id,
1185 --need to fixed in forward port of 4142439 or 4159367
1186 --p_parent_header_id,
1187 p_wip_entity_id,
1188 p_organization_id,
1189 p_substitution_type,
1190 cur_row.operation_seq_num,
1191 cur_row.inventory_item_id_old,
1192 cur_row.inventory_item_id_new);
1193
1194 END LOOP;
1195 --Added the below procedure for bug#16064426 to validate subinventory and locator combination for requirements
1196 Valid_Req_subinvlocator(p_group_id ,
1197 p_wip_entity_id ,
1198 p_organization_id ,
1199 p_substitution_type );
1200 END Add_Req;
1201
1202
1203
1204 /* called after defaulting */
1205 procedure post_default(p_group_id number,
1206 p_wip_entity_id number,
1207 p_organization_id number,
1208 p_substitution_type number,
1209 p_operation_seq_num number,
1210 p_inventory_item_id_new number) IS
1211 cursor c_invalid_rows is
1212 select interface_id
1213 from wip_job_dtls_interface wjdi
1214 where wjdi.group_id = p_group_id
1215 and wjdi.process_phase = wip_constants.ml_validation
1216 and wjdi.process_status in (wip_constants.running,
1217 wip_constants.warning)
1218 and wjdi.wip_entity_id = p_wip_entity_id
1219 and wjdi.organization_id = p_organization_id
1220 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1221 and wjdi.substitution_type = p_substitution_type
1222 and wjdi.operation_seq_num = p_operation_seq_num
1223 and wjdi.inventory_item_id_new = p_inventory_item_id_new
1224 and wjdi.wip_supply_type is null;
1225
1226 l_error_exists boolean := false;
1227 begin
1228
1229 -- Give Error if wip_supply_type is NULL
1230 -- Only do this validation when ADD/CHANGE Requirements
1231
1232 for l_inv_row in c_invalid_rows loop
1233 l_error_exists := true;
1234 fnd_message.set_name('WIP', 'WIP_JDI_NULL_SUPPLY_TYPE');
1235 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1236 if(wip_job_details.std_alone = 1) then
1237 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1238 p_text => substr(fnd_message.get,1,500),
1239 p_error_type => wip_jdi_utils.msg_error);
1240 else
1241 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1242 p_text => substr(fnd_message.get,1,500),
1243 p_error_type => wip_jdi_utils.msg_error);
1244 end if;
1245 end loop;
1246
1247 if(l_error_exists) then
1248 update wip_job_dtls_interface wjdi
1249 set process_status = wip_constants.error
1250 where group_id = p_group_id
1251 and process_phase = wip_constants.ml_validation
1252 and process_status in (wip_constants.running,
1253 wip_constants.warning)
1254 and wip_entity_id = p_wip_entity_id
1255 and organization_id = p_organization_id
1256 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1257 and wjdi.substitution_type = p_substitution_type
1258 and wjdi.operation_seq_num = p_operation_seq_num
1259 and wjdi.inventory_item_id_new = p_inventory_item_id_new
1260 and wjdi.wip_supply_type is null;
1261 end if;
1262 end post_default;
1263
1264
1265
1266 procedure chng_req_info_exist(p_group_id number,
1267 p_wip_entity_id number,
1268 p_organization_id number,
1269 p_substitution_type number,
1270 p_operation_seq_num number) IS
1271 cursor c_invalid_rows is
1272 select interface_id
1273 from wip_job_dtls_interface wjdi
1274 where wjdi.group_id = p_group_id
1275 and wjdi.process_phase = wip_constants.ml_validation
1276 and wjdi.process_status in (wip_constants.running,
1277 wip_constants.warning)
1278 and wjdi.wip_entity_id = p_wip_entity_id
1279 and wjdi.organization_id = p_organization_id
1280 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1281 and wjdi.substitution_type = p_substitution_type
1282 and wjdi.operation_seq_num = p_operation_seq_num
1283 and ( wjdi.inventory_item_id_old is null
1284 or (wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
1285 and wjdi.quantity_per_assembly is null
1286 and not exists
1287 ( select 1
1288 from bom_substitute_components bsc
1289 where bsc.substitute_component_id = wjdi.inventory_item_id_new
1290 and bsc.component_sequence_id =
1291 (select wro.component_sequence_id
1292 from wip_requirement_operations wro
1293 where wro.inventory_item_id = wjdi.inventory_item_id_old
1294 and wro.wip_entity_id = wjdi.wip_entity_id
1295 and wro.operation_seq_num = wjdi.operation_seq_num
1296 and wro.organization_id = wjdi.organization_id
1297 )
1298 and bsc.acd_type is null
1299 )
1300 )
1301 ) ;
1302
1303 l_error_exists boolean := false;
1304 begin
1305
1306 -- Give Error if wip_supply_type is NULL
1307 -- Only do this validation when ADD/CHANGE Requirements
1308
1309 for l_inv_row in c_invalid_rows loop
1310 l_error_exists := true;
1311 fnd_message.set_name('WIP', 'WIP_JDI_CHNG_REQ_INFO_MISSING');
1312 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
1313 if(wip_job_details.std_alone = 1) then
1314 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
1315 p_text => substr(fnd_message.get,1,500),
1316 p_error_type => wip_jdi_utils.msg_error);
1317 else
1318 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
1319 p_text => substr(fnd_message.get,1,500),
1320 p_error_type => wip_jdi_utils.msg_error);
1321 end if;
1322 end loop;
1323
1324 if(l_error_exists) then
1325 update wip_job_dtls_interface wjdi
1326 set process_status = wip_constants.error
1327 where group_id = p_group_id
1328 and process_phase = wip_constants.ml_validation
1329 and process_status in (wip_constants.running,
1330 wip_constants.warning)
1331 and wip_entity_id = p_wip_entity_id
1332 and organization_id = p_organization_id
1333 and wjdi.load_type = wip_job_details.wip_mtl_requirement
1334 and wjdi.substitution_type = p_substitution_type
1335 and wjdi.operation_seq_num = p_operation_seq_num
1336 and ( wjdi.inventory_item_id_old is null
1337 or ( wjdi.inventory_item_id_old <> nvl(wjdi.inventory_item_id_new, wjdi.inventory_item_id_old)
1338 and wjdi.quantity_per_assembly is null
1339 )
1340 );
1341 end if;
1342 end chng_req_info_exist;
1343
1344 Procedure derive_quantity(
1345 p_group_id in number,
1346 p_wip_entity_id in number,
1347 p_organization_id in number,
1348 p_substitution_type in number,
1349 p_operation_seq_num in number,
1350 p_inventory_item_id_old in number,
1351 p_inventory_item_id_new in number,
1352 p_quantity_per_assembly in number,
1353 p_required_quantity in number,
1354 p_basis_type in number, /* LBM Project */
1355 p_component_yield_factor in number, /*Component Yield Enhancement(Bug 4369064)*/
1356 p_err_code out nocopy varchar2,
1357 p_err_msg out nocopy varchar2) IS
1358
1359 x_required_quantity NUMBER;
1360 x_mps_required_quantity NUMBER;
1361 X_start_quantity number;
1362 X_quantity_per_assembly number ;
1363 X_component_yield_factor number; /*Component Yield Enhancement(Bug 4369064)*/
1364 X_start_quantity_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1365 X_quantity_per_assembly_wro number ; /*Component Yield Enhancement(Bug 4369064)*/
1366 X_component_yield_factor_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1367 X_required_quantity_wro number; /*Component Yield Enhancement(Bug 4369064)*/
1368 x_qpa_val number; /*Component Yield Enhancement(Bug 4369064)*/
1369 x_rq_val number; /*Component Yield Enhancement(Bug 4369064)*/
1370 x_cyf_val number; /*Component Yield Enhancement(Bug 4369064)*/
1371
1372
1373 BEGIN
1374
1375 /* returns if mat is changed, defaulting for those are done in
1376 WIP_REQUIREMENT_DEFAULT.Default_Requirement */
1377 if (p_inventory_item_id_new is not null and p_inventory_item_id_new <> p_inventory_item_id_old ) then
1378 return;
1379 end if;
1380
1381 begin
1382 SELECT start_quantity
1383 INTO X_start_quantity
1384 FROM WIP_DISCRETE_JOBS
1385 WHERE wip_entity_id = p_wip_entity_id
1386 AND organization_id = p_organization_id;
1387
1388 /*Component Yield Enhancement(Bug 4369064)->Get the current values of qpa, req_qty and yield*/
1389 begin
1390 SELECT nvl(component_yield_factor,1),required_quantity,quantity_per_assembly
1391 INTO x_component_yield_factor_wro,x_required_quantity_wro,x_quantity_per_assembly_wro
1392 FROM wip_requirement_operations
1393 WHERE wip_entity_id = p_wip_entity_id
1394 AND organization_id = p_organization_id
1395 AND inventory_item_id = p_inventory_item_id_old;
1396 exception
1397 when no_data_found then
1398 return; /*Let the validation error be caught in valid_requirement*/
1399 end;
1400
1401 X_quantity_per_assembly := p_quantity_per_assembly;
1402 x_required_quantity := p_required_quantity;
1403 X_component_yield_factor := p_component_yield_factor; /*Component Yield Enhancement(Bug 4369064)*/
1404
1405 /*Component Yield Enhancement(Bug 4369064)
1406 Use following values while re-calculation of yield or required quantity*/
1407
1408 x_qpa_val := nvl(x_quantity_per_assembly,x_quantity_per_assembly_wro);
1409 x_rq_val := nvl(x_required_quantity,x_required_quantity_wro);
1410 x_cyf_val := nvl(x_component_yield_factor,x_component_yield_factor_wro);
1411
1412 /*Component Yield Enhancement(Bug 4369064)
1413 User can provide any combination of qpa, req_qty and yield in WJDI. So there would be 8 combinations
1414 in all. We have divided them in following categories
1415 1. When all of them are null -> Don't do anything
1416 2. When only req qty is provied -> ie user wanted to re-calculate yield from req qty.
1417 3. When req qty and QPA are provided -> calculate yield.
1418 4. When req qty and yield are provided -> calculate QPA.
1419 5. Rest of the cases -> re-calculate required quantity in all such cases
1420 */
1421 if x_quantity_per_assembly is null and x_component_yield_factor is null
1422 and x_required_quantity is null then
1423 null;
1424
1425 elsif x_quantity_per_assembly is null and x_component_yield_factor is null
1426 and x_required_quantity is not null then /*User entered required qty, calculate yield */
1427
1428 /* LBM Project changes have been re-evaluated by Jenny */
1429 /*bug 9932127: not calculating component_yield_factor, rather will default from BOM*/
1430 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1431 x_component_yield_factor := null; /*round(x_qpa_val / x_required_quantity,6);*/
1432 else
1433 x_component_yield_factor := null; /*round(x_qpa_val * X_start_quantity / x_required_quantity,6);*/
1434 end if;
1435
1436 elsif x_required_quantity is not null and x_quantity_per_assembly is not null
1437 and x_component_yield_factor is null then /*User entered required qty and QPA, calculate yield */
1438
1439 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1440 x_component_yield_factor := round(x_qpa_val / x_required_quantity,6);
1441 else
1442 x_component_yield_factor := round(x_qpa_val * X_start_quantity / x_required_quantity,6);
1443 end if;
1444
1445 elsif x_required_quantity is not null and x_component_yield_factor is not null
1446 and x_quantity_per_assembly is null then /*User entered required qty and yield, calculate QPA */
1447
1448 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1449 X_quantity_per_assembly := round(x_cyf_val * x_required_quantity,6);
1450 else
1451 X_quantity_per_assembly := round(x_cyf_val * x_required_quantity / X_start_quantity ,6);
1452 end if;
1453
1454 else /*User entered QPA, calculate required qty */
1455 /*User entered yield, calculate required qty */
1456 /*User entered QPA and yield, calculate required qty */
1457 /*User entered QPA, required qty and yield, re-calculate required qty */
1458 if p_basis_type = WIP_CONSTANTS.LOT_BASED_MTL then
1459 x_required_quantity := round(x_qpa_val / x_cyf_val , 6);
1460 else
1461 x_required_quantity := round(x_start_quantity * x_qpa_val / x_cyf_val , 6);
1462 end if;
1463
1464 x_MPS_required_quantity := x_required_quantity;
1465
1466 end if;
1467
1468 if (x_required_quantity is not null) then
1469 UPDATE WIP_JOB_DTLS_INTERFACE
1470 SET quantity_per_assembly = nvl(X_quantity_per_assembly, quantity_per_assembly),
1471 required_quantity = x_required_quantity,
1472 mps_required_quantity = nvl(x_mps_required_quantity,mps_required_quantity),
1473 component_yield_factor = nvl(x_component_yield_factor,component_yield_factor)
1474 /*Component Yield Enhancement(Bug 4369064)*/
1475 WHERE group_id = p_group_id
1476 AND wip_entity_id = p_wip_entity_id
1477 AND organization_id = p_organization_id
1478 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1479 AND substitution_type = p_substitution_type
1480 AND operation_seq_num = p_operation_seq_num
1481 AND inventory_item_id_old = p_inventory_item_id_old;
1482 end if;
1483
1484 exception
1485 when others then
1486 p_err_msg := 'WIPRQVDB.pls<Procedure derive_quantity>:' || SQLERRM;
1487 p_err_code := SQLCODE;
1488 end;
1489
1490 END derive_quantity;
1491
1492 Procedure Change_Req(p_group_id in number,
1493 p_wip_entity_id in number,
1494 p_organization_id in number,
1495 p_substitution_type in number) IS
1496
1497 x_err_code varchar2(30) := null;
1498 x_err_msg varchar2(240) := NULL;
1499 CURSOR req_info(p_group_Id number,
1500 p_wip_entity_id number,
1501 p_organization_id number,
1502 p_substitution_type number) IS
1503 SELECT distinct operation_seq_num,
1504 inventory_item_id_old, inventory_item_id_new,
1505 quantity_per_assembly,component_yield_factor, /*Component Yield Enhancement(Bug 4369064)*/
1506 last_update_date, last_updated_by, creation_date, created_by,
1507 last_update_login, request_id, program_application_id,
1508 program_id, program_update_date,
1509 department_id, wip_supply_type, date_required,
1510 required_quantity, quantity_issued,
1511 basis_type, /* LBM Project */
1512 supply_subinventory,
1513 supply_locator_id, mrp_net_flag, mps_required_quantity,
1514 mps_date_required, attribute_category, attribute1,
1515 attribute2,attribute3,attribute4,attribute5,attribute6,attribute7,
1516 attribute8,attribute9,attribute10,attribute11,attribute12,
1517 attribute13,attribute14,attribute15
1518 FROM WIP_JOB_DTLS_INTERFACE
1519 WHERE group_id = p_group_id
1520 AND process_phase = WIP_CONSTANTS.ML_VALIDATION
1521 AND process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1522 AND wip_entity_id = p_wip_entity_id
1523 AND organization_id = p_organization_id
1524 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1525 AND substitution_type = p_substitution_type;
1526
1527 BEGIN
1528
1529 FOR cur_row IN req_info(p_group_id,
1530 p_wip_entity_id,
1531 p_organization_id,
1532 p_substitution_type) LOOP
1533
1534 derive_quantity(p_group_id,
1535 p_wip_entity_id,
1536 p_organization_id,
1537 p_substitution_type,
1538 cur_row.operation_seq_num,
1539 cur_row.inventory_item_id_old,
1540 cur_row.inventory_item_id_new,
1541 round(cur_row.quantity_per_assembly, 6),
1542 round(cur_row.required_quantity, 6),
1543 cur_row.basis_type, /* LBM Project */
1544 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
1545 x_err_code,
1546 x_err_msg);
1547 IF x_err_code is null then
1548
1549 Chng_Req_Info_Exist(p_group_id,
1550 p_wip_entity_id,
1551 p_organization_id,
1552 p_substitution_type,
1553 cur_row.operation_seq_num);
1554
1555 IF Info_Missing(p_group_id,
1556 p_wip_entity_id,
1557 p_organization_id,
1558 p_substitution_type,
1559 cur_row.operation_seq_num) = 0 THEN
1560 REQ_JOB_Match (p_group_id,
1561 p_wip_entity_id,
1562 p_organization_id,
1563 p_substitution_type,
1564 cur_row.operation_seq_num,
1565 cur_row.inventory_item_id_old);
1566
1567 /* Bug 7758528. Material requirements form allows us to update transacted materials.
1568 So call to validate for transactions/ pending transactions will be done only if user
1569 is updating the component itself. */
1570
1571 IF IS_Error(p_group_id,
1572 p_wip_entity_id,
1573 p_organization_id,
1574 p_substitution_type,
1575 cur_row.operation_seq_num,
1576 cur_row.inventory_item_id_old,
1577 cur_row.inventory_item_id_new) = 0 AND
1578 nvl(cur_row.inventory_item_id_new, cur_row.inventory_item_id_old) <> cur_row.inventory_item_id_old THEN
1579
1580 REQ_JOB_NOT_EXIST (p_group_id,
1581 p_wip_entity_id,
1582 p_organization_id,
1583 p_substitution_type,
1584 cur_row.operation_seq_num,
1585 cur_row.inventory_item_id_new);
1586
1587 -- check that the new component not exist in same operation.
1588
1589 IF IS_Error(p_group_id,
1590 p_wip_entity_id,
1591 p_organization_id,
1592 p_substitution_type,
1593 cur_row.operation_seq_num,
1594 cur_row.inventory_item_id_old,
1595 cur_row.inventory_item_id_new) = 0 THEN
1596
1597 Safe_Delete (p_group_id,
1598 p_wip_entity_id,
1599 p_organization_id,
1600 p_substitution_type,
1601 cur_row.operation_seq_num,
1602 cur_row.inventory_item_id_old);
1603
1604 IF IS_Error(p_group_id,
1605 p_wip_entity_id,
1606 p_organization_id,
1607 p_substitution_type,
1608 cur_row.operation_seq_num,
1609 cur_row.inventory_item_id_old,
1610 cur_row.inventory_item_id_new) = 0 AND
1611 cur_row.inventory_item_id_new IS NOT NULL THEN
1612
1613 Valid_Requirement (p_group_id,
1614 p_wip_entity_id,
1615 p_organization_id,
1616 p_substitution_type,
1617 cur_row.operation_seq_num,
1618 cur_row.inventory_item_id_new);
1619
1620 IF IS_Error(p_group_id,
1621 p_wip_entity_id,
1622 p_organization_id,
1623 p_substitution_type,
1624 cur_row.operation_seq_num,
1625 cur_row.inventory_item_id_old,
1626 cur_row.inventory_item_id_new) = 0 THEN
1627
1628 WIP_REQUIREMENT_DEFAULT.Default_Requirement(p_group_id,
1629 p_wip_entity_id,
1630 p_organization_id,
1631 p_substitution_type,
1632 cur_row.operation_seq_num,
1633 cur_row.inventory_item_id_old,
1634 cur_row.inventory_item_id_new,
1635 round(cur_row.quantity_per_assembly, 6),
1636 cur_row.basis_type, /* LBM Project */
1637 cur_row.component_yield_factor,/*Component Yield Enhancement(Bug 4369064)*/
1638 x_err_code,
1639 x_err_msg);
1640
1641 IF x_err_code is null AND
1642 cur_row.inventory_item_id_new IS NOT NULL AND
1643 cur_row.inventory_item_id_new <> cur_row.inventory_item_id_old THEN /*Component Yield Enhancement(Bug 4369064)*/
1644
1645 Post_Default (p_group_id,
1646 p_wip_entity_id,
1647 p_organization_id,
1648 p_substitution_type,
1649 cur_row.operation_seq_num,
1650 cur_row.inventory_item_id_new);
1651 END IF;
1652 END IF;
1653 END IF;
1654 END IF;
1655 END IF;
1656 END IF;
1657 END IF;
1658
1659 /*bug 4202200 -> If the job has no operations , supply_type can not be
1660 operation pull; I have put this condition here since we should also be
1661 catering to cases where inventory_item_id_new is null */
1662
1663 Valid_Requirement_Supply_Type (p_group_id,
1664 --need to fixed in forward port of 4142439 or 4159367
1665 --p_parent_header_id,
1666 p_wip_entity_id,
1667 p_organization_id,
1668 p_substitution_type,
1669 cur_row.operation_seq_num,
1670 cur_row.inventory_item_id_old,
1671 cur_row.inventory_item_id_new);
1672
1673 END LOOP;
1674 --Added the below procedure for bug#16064426 to validate subinventory and locator combination for requirements
1675 Valid_Req_subinvlocator(p_group_id ,
1676 p_wip_entity_id ,
1677 p_organization_id ,
1678 p_substitution_type );
1679
1680
1681
1682 END Change_Req;
1683
1684 function IS_Error(p_group_id number,
1685 p_wip_entity_id number,
1686 p_organization_id number,
1687 p_substitution_type number,
1688 p_operation_seq_num number,
1689 p_inventory_item_id_old number,
1690 p_inventory_item_id_new number) return number IS
1691
1692 x_count number := 0;
1693
1694 BEGIN
1695
1696 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
1697 SELECT count(*)
1698 INTO x_count
1699 FROM WIP_JOB_DTLS_INTERFACE
1700 WHERE group_id = p_group_id
1701 AND process_status = WIP_CONSTANTS.ERROR
1702 AND wip_entity_id = p_wip_entity_id
1703 AND organization_id = p_organization_id
1704 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1705 AND substitution_type= p_substitution_type
1706 AND operation_seq_num= p_operation_seq_num
1707 AND inventory_item_id_old = p_inventory_item_id_old;
1708
1709 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
1710 SELECT count(*)
1711 INTO x_count
1712 FROM WIP_JOB_DTLS_INTERFACE
1713 WHERE group_id = p_group_id
1714 AND process_status = WIP_CONSTANTS.ERROR
1715 AND wip_entity_id = p_wip_entity_id
1716 AND organization_id = p_organization_id
1717 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1718 AND substitution_type= p_substitution_type
1719 AND operation_seq_num= p_operation_seq_num
1720 AND inventory_item_id_new = p_inventory_item_id_new;
1721
1722 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
1723 SELECT count(*)
1724 INTO x_count
1725 FROM WIP_JOB_DTLS_INTERFACE
1726 WHERE group_id = p_group_id
1727 AND process_status = WIP_CONSTANTS.ERROR
1728 AND wip_entity_id = p_wip_entity_id
1729 AND organization_id = p_organization_id
1730 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1731 AND substitution_type= p_substitution_type
1732 AND operation_seq_num= p_operation_seq_num
1733 AND inventory_item_id_old = p_inventory_item_id_old
1734 AND inventory_item_id_new = p_inventory_item_id_new;
1735
1736 END IF;
1737
1738 IF x_count <> 0 THEN
1739 return 1;
1740 ELSE
1741 return 0;
1742 END IF;
1743
1744 END IS_Error;
1745
1746 function Info_Missing(p_group_id number,
1747 p_wip_entity_id number,
1748 p_organization_id number,
1749 p_substitution_type number,
1750 p_operation_seq_num number) return number IS
1751
1752 x_count number := 0;
1753
1754 BEGIN
1755
1756 IF p_substitution_type = WIP_JOB_DETAILS.WIP_DELETE THEN
1757 SELECT count(*)
1758 INTO x_count
1759 FROM WIP_JOB_DTLS_INTERFACE
1760 WHERE group_id = p_group_id
1761 AND process_status = WIP_CONSTANTS.ERROR
1762 AND wip_entity_id = p_wip_entity_id
1763 AND organization_id = p_organization_id
1764 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1765 AND substitution_type= p_substitution_type
1766 AND operation_seq_num= p_operation_seq_num
1767 AND inventory_item_id_old IS NULL;
1768
1769 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_ADD THEN
1770 SELECT count(*)
1771 INTO x_count
1772 FROM WIP_JOB_DTLS_INTERFACE
1773 WHERE group_id = p_group_id
1774 AND process_status = WIP_CONSTANTS.ERROR
1775 AND wip_entity_id = p_wip_entity_id
1776 AND organization_id = p_organization_id
1777 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1778 AND substitution_type= p_substitution_type
1779 AND operation_seq_num= p_operation_seq_num
1780 AND (inventory_item_id_new IS NULL
1781 OR quantity_per_assembly IS NULL);
1782
1783 ELSIF p_substitution_type = WIP_JOB_DETAILS.WIP_CHANGE THEN
1784 SELECT count(*)
1785 INTO x_count
1786 FROM WIP_JOB_DTLS_INTERFACE
1787 WHERE group_id = p_group_id
1788 AND process_status = WIP_CONSTANTS.ERROR
1789 AND wip_entity_id = p_wip_entity_id
1790 AND organization_id = p_organization_id
1791 AND load_type = WIP_JOB_DETAILS.WIP_MTL_REQUIREMENT
1792 AND substitution_type= p_substitution_type
1793 AND operation_seq_num= p_operation_seq_num
1794 AND (inventory_item_id_old IS NULL
1795 OR quantity_per_assembly IS NULL);
1796
1797 END IF;
1798
1799 IF x_count <> 0 THEN
1800 return 1;
1801 ELSE return 0;
1802 END IF;
1803
1804 END Info_Missing;
1805
1806 END WIP_REQUIREMENT_VALIDATIONS;