[Home] [Help]
PACKAGE BODY: APPS.WIP_SERIAL_ASSOC_VALIDATIONS
Source
1 PACKAGE BODY WIP_SERIAL_ASSOC_VALIDATIONS AS
2 /* $Header: wipsrvdb.pls 120.2 2006/08/31 15:03:32 sisankar noship $ */
3
4 /* forward declarations */
5 procedure create_serials(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 x_return_status out nocopy varchar2);
10
11 procedure del_info_exists(p_group_id in number,
12 p_wip_entity_id in number,
13 p_organization_id in number,
14 p_substitution_type in number,
15 x_return_status out nocopy varchar2);
16
17 procedure add_info_exists(p_group_id in number,
18 p_wip_entity_id in number,
19 p_organization_id in number,
20 p_substitution_type in number,
21 x_return_status out nocopy varchar2);
22
23 --make sure the serial is available for association
24 procedure unused_serial_exists(p_group_id in number,
25 p_wip_entity_id in number,
26 p_organization_id in number,
27 p_substitution_type in number,
28 x_return_status out nocopy varchar2);
29
30 procedure used_serial_exists(p_group_id in number,
31 p_wip_entity_id in number,
32 p_organization_id in number,
33 p_substitution_type in number,
34 x_return_status out nocopy varchar2);
35
36 procedure valid_parent_load_type(p_group_id in number,
37 p_wip_entity_id in number,
38 p_organization_id in number,
39 p_substitution_type in number,
40 x_return_status out nocopy varchar2);
41
42 procedure valid_job_exists(p_group_id in number,
43 p_wip_entity_id in number,
44 p_organization_id in number,
45 p_substitution_type in number,
46 x_return_status out nocopy varchar2);
47
48
49 procedure change_serial(p_group_id in number,
50 p_wip_entity_id in number,
51 p_organization_id in number,
52 p_substitution_type in number) is
53 l_ret_status VARCHAR2(10);
54 begin
55 valid_parent_load_type(p_group_id => p_group_id,
56 p_wip_entity_id => p_wip_entity_id,
57 p_organization_id => p_organization_id,
58 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
59 x_return_status => l_ret_status);
60
61 if(l_ret_status = fnd_api.g_ret_sts_success) then
62 valid_job_exists(p_group_id => p_group_id,
63 p_wip_entity_id => p_wip_entity_id,
64 p_organization_id => p_organization_id,
65 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
66 x_return_status => l_ret_status);
67 end if;
68
69
70 if(l_ret_status = fnd_api.g_ret_sts_success) then
71 add_info_exists(p_group_id => p_group_id,
72 p_wip_entity_id => p_wip_entity_id,
73 p_organization_id => p_organization_id,
74 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
75 x_return_status => l_ret_status);
76 end if;
77
78 --if serials do not yet exist in msn, create them here.
79 if(l_ret_status = fnd_api.g_ret_sts_success) then
80 create_serials(p_group_id => p_group_id,
81 p_wip_entity_id => p_wip_entity_id,
82 p_organization_id => p_organization_id,
83 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
84 x_return_status => l_ret_status);
85 end if;
86
87 if(l_ret_status = fnd_api.g_ret_sts_success) then
88 unused_serial_exists(p_group_id => p_group_id,
89 p_wip_entity_id => p_wip_entity_id,
90 p_organization_id => p_organization_id,
91 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
92 x_return_status => l_ret_status);
93 end if;
94
95 if(l_ret_status = fnd_api.g_ret_sts_success) then
96 del_info_exists(p_group_id => p_group_id,
97 p_wip_entity_id => p_wip_entity_id,
98 p_organization_id => p_organization_id,
99 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
100 x_return_status => l_ret_status);
101 end if;
102
103 if(l_ret_status = fnd_api.g_ret_sts_success) then
104 used_serial_exists(p_group_id => p_group_id,
105 p_wip_entity_id => p_wip_entity_id,
106 p_organization_id => p_organization_id,
107 p_substitution_type => WIP_JOB_DETAILS.WIP_CHANGE,
108 x_return_status => l_ret_status);
109 end if;
110 end change_serial;
111
112 procedure add_serial(p_group_id in number,
113 p_wip_entity_id in number,
114 p_organization_id in number,
115 p_substitution_type in number) is
116 l_ret_status VARCHAR2(10);
117 begin
118 valid_parent_load_type(p_group_id => p_group_id,
119 p_wip_entity_id => p_wip_entity_id,
120 p_organization_id => p_organization_id,
121 p_substitution_type => WIP_JOB_DETAILS.WIP_ADD,
122 x_return_status => l_ret_status);
123
124 if(l_ret_status = fnd_api.g_ret_sts_success) then
125 valid_job_exists(p_group_id => p_group_id,
126 p_wip_entity_id => p_wip_entity_id,
127 p_organization_id => p_organization_id,
128 p_substitution_type => WIP_JOB_DETAILS.WIP_ADD,
129 x_return_status => l_ret_status);
130 end if;
131
132 if(l_ret_status = fnd_api.g_ret_sts_success) then
133 add_info_exists(p_group_id => p_group_id,
134 p_wip_entity_id => p_wip_entity_id,
135 p_organization_id => p_organization_id,
136 p_substitution_type => WIP_JOB_DETAILS.WIP_ADD,
137 x_return_status => l_ret_status);
138 end if;
139
140 --if serials do not yet exist in msn, create them here.
141 if(l_ret_status = fnd_api.g_ret_sts_success) then
142 create_serials(p_group_id => p_group_id,
143 p_wip_entity_id => p_wip_entity_id,
144 p_organization_id => p_organization_id,
145 p_substitution_type => WIP_JOB_DETAILS.WIP_ADD,
146 x_return_status => l_ret_status);
147 end if;
148
149 if(l_ret_status = fnd_api.g_ret_sts_success) then
150 unused_serial_exists(p_group_id => p_group_id,
151 p_wip_entity_id => p_wip_entity_id,
152 p_organization_id => p_organization_id,
153 p_substitution_type => WIP_JOB_DETAILS.WIP_ADD,
154 x_return_status => l_ret_status);
155 end if;
156 end add_serial;
157
158
159 procedure delete_serial(p_group_id in number,
160 p_wip_entity_id in number,
161 p_organization_id in number,
162 p_substitution_type in number) is
163 l_ret_status VARCHAR2(10) := fnd_api.g_ret_sts_success;
164 begin
165 valid_parent_load_type(p_group_id => p_group_id,
166 p_wip_entity_id => p_wip_entity_id,
167 p_organization_id => p_organization_id,
168 p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
169 x_return_status => l_ret_status);
170
171 if(l_ret_status = fnd_api.g_ret_sts_success) then
172 valid_job_exists(p_group_id => p_group_id,
173 p_wip_entity_id => p_wip_entity_id,
174 p_organization_id => p_organization_id,
175 p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
176 x_return_status => l_ret_status);
177 end if;
178
179 if(l_ret_status = fnd_api.g_ret_sts_success) then
180 del_info_exists(p_group_id => p_group_id,
181 p_wip_entity_id => p_wip_entity_id,
182 p_organization_id => p_organization_id,
183 p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
184 x_return_status => l_ret_status);
185 end if;
186
187
188 if(l_ret_status = fnd_api.g_ret_sts_success) then
189 used_serial_exists(p_group_id => p_group_id,
190 p_wip_entity_id => p_wip_entity_id,
191 p_organization_id => p_organization_id,
192 p_substitution_type => WIP_JOB_DETAILS.WIP_DELETE,
193 x_return_status => l_ret_status);
194 end if;
195 end delete_serial;
196
197 procedure del_info_exists(p_group_id in number,
198 p_wip_entity_id in number,
199 p_organization_id in number,
200 p_substitution_type in number,
201 x_return_status out nocopy varchar2) is
202
203 cursor c_invalid_rows is
204 select interface_id
205 from wip_job_dtls_interface wjdi
206 where wjdi.group_id = p_group_id
207 and wjdi.process_phase = wip_constants.ml_validation
208 and wjdi.process_status in (wip_constants.running,
209 wip_constants.pending,
210 wip_constants.warning)
211 and wjdi.wip_entity_id = p_wip_entity_id
212 and wjdi.organization_id = p_organization_id
213 and wjdi.load_type = wip_job_details.wip_serial
214 and wjdi.substitution_type = p_substitution_type
215 and wjdi.serial_number_old is null;
216
217 begin
218 x_return_status := fnd_api.g_ret_sts_success;
219 for l_inv_row in c_invalid_rows loop
220 x_return_status := fnd_api.g_ret_sts_error;
221 fnd_message.set_name('WIP', 'WIP_JDI_OLD_SERIAL_MISSING');
222 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
223 if(wip_job_details.std_alone = 1) then
224 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
225 p_text => substr(fnd_message.get,1,500),
226 p_error_type => wip_jdi_utils.msg_error);
227 else
228 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
229 p_text => substr(fnd_message.get,1,500),
230 p_error_type => wip_jdi_utils.msg_error);
231 end if;
232 end loop;
233
234 if(x_return_status <> fnd_api.g_ret_sts_success) then
235 update wip_job_dtls_interface wjdi
236 set process_status = wip_constants.error
237 where group_id = p_group_id
238 and process_phase = wip_constants.ml_validation
239 and process_status in (wip_constants.running,
240 wip_constants.pending,
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_serial
245 and wjdi.substitution_type = p_substitution_type
246 and wjdi.serial_number_old is null;
247 end if;
248 end del_info_exists;
249
250 procedure create_serials(p_group_id in number,
251 p_wip_entity_id in number,
252 p_organization_id in number,
253 p_substitution_type in number,
254 x_return_status out nocopy varchar2) is
255 cursor c_serials is
256 select interface_id,
257 we.primary_item_id,
258 wjdi.serial_number_new,
259 wjdi.rowid
260 from wip_job_dtls_interface wjdi,
261 wip_entities we
262 where wjdi.group_id = p_group_id
263 and wjdi.process_phase = wip_constants.ml_validation
264 and wjdi.process_status in (wip_constants.running,
265 wip_constants.pending,
266 wip_constants.warning)
267 and wjdi.wip_entity_id = p_wip_entity_id
268 and wjdi.organization_id = p_organization_id
269 and wjdi.load_type = wip_job_details.wip_serial
270 and wjdi.substitution_type = p_substitution_type
271 and wjdi.wip_entity_id = we.wip_entity_id
272 and not exists(select 1
273 from mtl_serial_numbers
274 where serial_number = wjdi.serial_number_new
275 and inventory_item_id = we.primary_item_id
276 and current_organization_id = wjdi.organization_id);
277
278 l_start_serial_num VARCHAR2(30);
279 l_end_serial_num VARCHAR2(30);
280 l_error_msg VARCHAR(2000);
281 l_return_status VARCHAR2(1);
282 begin
283 x_return_status := fnd_api.g_ret_sts_success;
284 for l_ser_rec in c_serials loop
285 l_start_serial_num := l_ser_rec.serial_number_new;
286 wip_utilities.generate_serials(p_org_id => p_organization_id,
287 p_item_id => l_ser_rec.primary_item_id,
288 p_qty => 1,
289 p_wip_entity_id => null, --processing code will fill this in later.
290 p_revision => null,
291 p_lot => null,
292 x_start_serial => l_start_serial_num,
293 x_end_serial => l_end_serial_num,
294 x_return_status => l_return_status,
295 x_err_msg => l_error_msg);
296
297 if(l_return_status <> fnd_api.g_ret_sts_success) then
298 x_return_status := fnd_api.g_ret_sts_error;
299 fnd_message.set_name('WIP', 'WIP_JDI_SER_CREATION_FAILED');
300 fnd_message.set_token('INTERFACE', l_ser_rec.interface_id);
301 fnd_message.set_token('MESSAGE', l_error_msg);
302 if(wip_job_details.std_alone = 1) then
303 wip_interface_err_Utils.add_error(p_interface_id => l_ser_rec.interface_id,
304 p_text => substr(fnd_message.get,1,500),
305 p_error_type => wip_jdi_utils.msg_error);
306 else
307 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
308 p_text => substr(fnd_message.get,1,500),
309 p_error_type => wip_jdi_utils.msg_error);
310 end if;
311 update wip_job_dtls_interface wjdi
312 set process_status = wip_constants.error
313 where rowid = l_ser_rec.rowid;
314 end if;
315 end loop;
316 end create_serials;
317
318 procedure add_info_exists(p_group_id in number,
319 p_wip_entity_id in number,
320 p_organization_id in number,
321 p_substitution_type in number,
322 x_return_status out nocopy varchar2) is
323 cursor c_invalid_rows is
324 select interface_id
325 from wip_job_dtls_interface wjdi
326 where wjdi.group_id = p_group_id
327 and wjdi.process_phase = wip_constants.ml_validation
328 and wjdi.process_status in (wip_constants.running,
329 wip_constants.pending,
330 wip_constants.warning)
331 and wjdi.wip_entity_id = p_wip_entity_id
332 and wjdi.organization_id = p_organization_id
333 and wjdi.load_type = wip_job_details.wip_serial
334 and wjdi.substitution_type = p_substitution_type
335 and wjdi.serial_number_new is null;
336
337 begin
338 x_return_status := fnd_api.g_ret_sts_success;
339 for l_inv_row in c_invalid_rows loop
340 x_return_status := fnd_api.g_ret_sts_error;
341 fnd_message.set_name('WIP', 'WIP_JDI_NEW_SERIAL_MISSING');
342 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
343 if(wip_job_details.std_alone = 1) then
344 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
345 p_text => substr(fnd_message.get,1,500),
346 p_error_type => wip_jdi_utils.msg_error);
347 else
348 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
349 p_text => substr(fnd_message.get,1,500),
350 p_error_type => wip_jdi_utils.msg_error);
351 end if;
352 end loop;
353
354 if(x_return_status <> fnd_api.g_ret_sts_success) then
355 update wip_job_dtls_interface wjdi
356 set process_status = wip_constants.error
357 where group_id = p_group_id
358 and process_phase = wip_constants.ml_validation
359 and process_status in (wip_constants.running,
360 wip_constants.pending,
361 wip_constants.warning)
362 and wip_entity_id = p_wip_entity_id
363 and organization_id = p_organization_id
364 and wjdi.load_type = wip_job_details.wip_serial
365 and wjdi.substitution_type = p_substitution_type
366 and wjdi.serial_number_new is null;
367 end if;
368 end add_info_exists;
369
370 procedure unused_serial_exists(p_group_id in number,
371 p_wip_entity_id in number,
372 p_organization_id in number,
373 p_substitution_type in number,
374 x_return_status out nocopy varchar2) is
375 cursor c_invalid_rows is
376 select interface_id
377 from wip_job_dtls_interface wjdi
378 where wjdi.group_id = p_group_id
379 and wjdi.process_phase = wip_constants.ml_validation
380 and wjdi.process_status in (wip_constants.running,
381 wip_constants.pending,
382 wip_constants.warning)
383 and wjdi.wip_entity_id = p_wip_entity_id
384 and wjdi.organization_id = p_organization_id
385 and wjdi.load_type = wip_job_details.wip_serial
386 and wjdi.substitution_type = p_substitution_type
387 and not exists(select 1
388 from mtl_serial_numbers msn, wip_entities we
389 where msn.serial_number = wjdi.serial_number_new
390 and msn.current_organization_id = wjdi.organization_id
391 and msn.wip_entity_id is null
392 and msn.group_mark_id is null
393 and msn.current_status in (1,6) --defined not used /* Modified for Bug 5466955 */
394 and msn.inventory_item_id = we.primary_item_id
395 and we.wip_entity_id = p_wip_entity_id);
396
397 begin
398 x_return_status := fnd_api.g_ret_sts_success;
399 for l_inv_row in c_invalid_rows loop
400 x_return_status := fnd_api.g_ret_sts_error;
401 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_UNUSED_SERIAL');
402 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
403 if(wip_job_details.std_alone = 1) then
404 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
405 p_text => substr(fnd_message.get,1,500),
406 p_error_type => wip_jdi_utils.msg_error);
407 else
408 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
409 p_text => substr(fnd_message.get,1,500),
410 p_error_type => wip_jdi_utils.msg_error);
411 end if;
412 end loop;
413
414 if(x_return_status <> fnd_api.g_ret_sts_success) then
415 update wip_job_dtls_interface wjdi
416 set process_status = wip_constants.error
417 where group_id = p_group_id
418 and process_phase = wip_constants.ml_validation
419 and process_status in (wip_constants.running,
420 wip_constants.pending,
421 wip_constants.warning)
422 and wip_entity_id = p_wip_entity_id
423 and organization_id = p_organization_id
424 and wjdi.load_type = wip_job_details.wip_serial
425 and wjdi.substitution_type = p_substitution_type
426 and not exists(select 1
427 from mtl_serial_numbers msn, wip_entities we
428 where msn.serial_number = wjdi.serial_number_new
429 and msn.current_organization_id = wjdi.organization_id
430 and msn.wip_entity_id is null
431 and msn.group_mark_id is null
432 and msn.current_status in (1,6) --defined not used /* Modified for Bug 5466955 */
433 and msn.inventory_item_id = we.primary_item_id
434 and we.wip_entity_id = p_wip_entity_id);
435
436 end if;
437 end unused_serial_exists;
438
439 procedure used_serial_exists(p_group_id in number,
440 p_wip_entity_id in number,
441 p_organization_id in number,
442 p_substitution_type in number,
443 x_return_status out nocopy varchar2) is
444
445 cursor c_invalid_rows is
446 select interface_id
447 from wip_job_dtls_interface wjdi
448 where wjdi.group_id = p_group_id
449 and wjdi.process_phase = wip_constants.ml_validation
450 and wjdi.process_status in (wip_constants.running,
451 wip_constants.pending,
452 wip_constants.warning)
453 and wjdi.wip_entity_id = p_wip_entity_id
454 and wjdi.organization_id = p_organization_id
455 and wjdi.load_type = wip_job_details.wip_serial
456 and wjdi.substitution_type = p_substitution_type
457 and not exists(select 1
458 from mtl_serial_numbers msn, wip_entities we
459 where msn.serial_number = wjdi.serial_number_old
460 and msn.current_organization_id = wjdi.organization_id
461 and msn.wip_entity_id = p_wip_entity_id
462 and msn.group_mark_id = p_wip_entity_id
463 and msn.operation_seq_num is null
464 and msn.current_status = 1 --defined not used
465 and msn.inventory_item_id = we.primary_item_id
466 and we.wip_entity_id = p_wip_entity_id);
467
468 begin
469 x_return_status := fnd_api.g_ret_sts_success;
470 for l_inv_row in c_invalid_rows loop
471 x_return_status := fnd_api.g_ret_sts_error;
472 fnd_message.set_name('WIP', 'WIP_JDI_INVALID_USED_SERIAL');
473 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
474 if(wip_job_details.std_alone = 1) then
475 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
476 p_text => substr(fnd_message.get,1,500),
477 p_error_type => wip_jdi_utils.msg_error);
478 else
479 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
480 p_text => substr(fnd_message.get,1,500),
481 p_error_type => wip_jdi_utils.msg_error);
482 end if;
483 end loop;
484
485 if(x_return_status <> fnd_api.g_ret_sts_success) then
486 update wip_job_dtls_interface wjdi
487 set process_status = wip_constants.error
488 where group_id = p_group_id
489 and process_phase = wip_constants.ml_validation
490 and process_status in (wip_constants.running,
491 wip_constants.pending,
492 wip_constants.warning)
493 and wip_entity_id = p_wip_entity_id
494 and organization_id = p_organization_id
495 and wjdi.load_type = wip_job_details.wip_serial
496 and wjdi.substitution_type = p_substitution_type
497 and not exists(select 1
498 from mtl_serial_numbers msn, wip_entities we
499 where msn.serial_number = wjdi.serial_number_old
500 and msn.current_organization_id = wjdi.organization_id
501 and msn.wip_entity_id = p_wip_entity_id
502 and msn.group_mark_id = p_wip_entity_id
503 and msn.operation_seq_num is null
504 and msn.current_status = 1 --defined not used
505 and msn.inventory_item_id = we.primary_item_id
506 and we.wip_entity_id = p_wip_entity_id);
507
508 end if;
509 end used_serial_exists;
510
511 procedure valid_parent_load_type(p_group_id in number,
512 p_wip_entity_id in number,
513 p_organization_id in number,
514 p_substitution_type in number,
515 x_return_status out nocopy varchar2) is
516 cursor c_invalid_rows is
517 select interface_id
518 from wip_job_dtls_interface wjdi
519 where wjdi.group_id = p_group_id
520 and wjdi.process_phase = wip_constants.ml_validation
521 and wjdi.process_status in (wip_constants.running,
522 wip_constants.pending,
523 wip_constants.warning)
524 and wjdi.wip_entity_id = p_wip_entity_id
525 and wjdi.organization_id = p_organization_id
526 and wjdi.substitution_type = p_substitution_type
527 and wjdi.load_type = wip_job_details.wip_serial
528 and not exists (select 1
529 from wip_job_schedule_interface wjsi
530 where wjsi.header_id = wjdi.parent_header_id
531 and wjsi.group_id = wjdi.group_id
532 and wjsi.load_type in (wip_constants.create_job,
533 wip_constants.create_ns_job,
534 wip_constants.resched_job));
535
536 begin
537 x_return_status := fnd_api.g_ret_sts_success;
538 for l_inv_row in c_invalid_rows loop
539 x_return_status := fnd_api.g_ret_sts_error;
540 fnd_message.set_name('WIP', 'WIP_JDI_SER_INV_LOAD_TYPE');
541 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
542 if(wip_job_details.std_alone = 1) then
543 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
544 p_text => substr(fnd_message.get,1,500),
545 p_error_type => wip_jdi_utils.msg_error);
546 else
547 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
548 p_text => substr(fnd_message.get,1,500),
549 p_error_type => wip_jdi_utils.msg_error);
550 end if;
551 end loop;
552
553 if(x_return_status <> fnd_api.g_ret_sts_success) then
554 update wip_job_dtls_interface wjdi
555 set process_status = wip_constants.error
556 where group_id = p_group_id
557 and process_phase = wip_constants.ml_validation
558 and process_status in (wip_constants.running,
559 wip_constants.pending,
560 wip_constants.warning)
561 and wip_entity_id = p_wip_entity_id
562 and organization_id = p_organization_id
563 and wjdi.substitution_type = p_substitution_type
564 and wjdi.load_type = wip_job_details.wip_serial
565 and not exists (select 1
566 from wip_job_schedule_interface wjsi
567 where wjsi.header_id = wjdi.parent_header_id
568 and wjsi.group_id = wjdi.group_id
569 and wjsi.load_type in (wip_constants.create_job,
570 wip_constants.create_ns_job,
571 wip_constants.resched_job));
572 end if;
573 end valid_parent_load_type;
574
575 procedure valid_job_exists(p_group_id in number,
576 p_wip_entity_id in number,
577 p_organization_id in number,
578 p_substitution_type in number,
579 x_return_status out nocopy varchar2) is
580 cursor c_invalid_rows is
581 select interface_id
582 from wip_job_dtls_interface wjdi
583 where wjdi.group_id = p_group_id
584 and wjdi.process_phase = wip_constants.ml_validation
585 and wjdi.process_status in (wip_constants.running,
586 wip_constants.pending,
587 wip_constants.warning)
588 and wjdi.wip_entity_id = p_wip_entity_id
589 and wjdi.organization_id = p_organization_id
590 and wjdi.substitution_type = p_substitution_type
591 and wjdi.load_type = wip_job_details.wip_serial
592 and not exists (select 1
593 from wip_job_schedule_interface wjsi, wip_discrete_jobs wdj
594 where wjsi.header_id = wjdi.parent_header_id
595 and wjsi.group_id = wjdi.group_id
596 and wjsi.wip_entity_id = wdj.wip_entity_id
597 and wdj.serialization_start_op is not null
598 and wdj.status_type in (wip_constants.unreleased,
599 wip_constants.released,
600 wip_constants.hold,
601 wip_constants.comp_chrg));
602
603 begin
604 x_return_status := fnd_api.g_ret_sts_success;
605 for l_inv_row in c_invalid_rows loop
606 x_return_status := fnd_api.g_ret_sts_error;
607 fnd_message.set_name('WIP', 'WIP_JDI_SER_JOB_STATUS');
608 fnd_message.set_token('INTERFACE', to_char(l_inv_row.interface_id));
609 if(wip_job_details.std_alone = 1) then
610 wip_interface_err_Utils.add_error(p_interface_id => l_inv_row.interface_id,
611 p_text => substr(fnd_message.get,1,500),
612 p_error_type => wip_jdi_utils.msg_error);
613 else
614 wip_interface_err_Utils.add_error(p_interface_id => wip_jsi_utils.current_interface_id,
615 p_text => substr(fnd_message.get,1,500),
616 p_error_type => wip_jdi_utils.msg_error);
617 end if;
618 end loop;
619
620 if(x_return_status <> fnd_api.g_ret_sts_success) then
621 update wip_job_dtls_interface wjdi
622 set process_status = wip_constants.error
623 where group_id = p_group_id
624 and process_phase = wip_constants.ml_validation
625 and process_status in (wip_constants.running,
626 wip_constants.pending,
627 wip_constants.warning)
628 and wip_entity_id = p_wip_entity_id
629 and organization_id = p_organization_id
630 and wjdi.substitution_type = p_substitution_type
631 and wjdi.load_type = wip_job_details.wip_serial
632 and not exists (select 1
633 from wip_discrete_jobs wdj
634 where wjdi.wip_entity_id = wdj.wip_entity_id
635 and wdj.serialization_start_op is not null
636 and wdj.status_type in (wip_constants.unreleased,
637 wip_constants.released,
638 wip_constants.hold,
639 wip_constants.comp_chrg));
640 end if;
641 end valid_job_exists;
642 end wip_serial_assoc_validations;