1 package body wip_validateMLHeader_pvt as
2 /* $Header: wipmlhvb.pls 120.44.12020000.7 2013/04/02 18:36:05 pding ship $ */
3
4 g_pkgName constant varchar2(30) := 'wip_validateMLHeader_pvt';
5
6 type num_tbl_t is table of number;
7
8 validationError constant number := 1;
9 validationWarning constant number := 2;
10
11 type wdj_rec_t is RECORD (wip_entity_name varchar2(240),
12 status_type NUMBER,
13 entity_type NUMBER,
14 job_type NUMBER,
15 start_quantity NUMBER,
16 quantity_completed NUMBER,
17 firm_planned_flag NUMBER,
18 primary_item_id NUMBER,
19 bom_reference_id NUMBER,
20 routing_reference_id NUMBER,
21 line_id NUMBER,
22 schedule_group_id NUMBER,
23 scheduled_completion_date DATE,
24 project_id NUMBER,
25 task_id NUMBER,
26 overcompletion_tolerance_type NUMBER,
27 overcompletion_tolerance_value NUMBER,
28 completion_subinventory VARCHAR2(30),
29 completion_locator_id NUMBER,
30 build_sequence NUMBER,
31 class_code VARCHAR2(10)); --fix for bug 16079296
32
33 type item_rec_t is RECORD(inventory_item_id NUMBER,
34 pick_components_flag VARCHAR2(1),
35 build_in_wip_flag VARCHAR2(1),
36 eng_item_flag VARCHAR2(1),
37 inventory_asset_flag VARCHAR2(1),
38 restrict_subinventories_code NUMBER,
39 restrict_locators_code NUMBER,
40 location_control_code NUMBER,
41 fixed_lead_time NUMBER,
42 variable_lead_time NUMBER);
43
44 wjsi_row wip_job_schedule_interface%ROWTYPE;
45 wdj_row wdj_rec_t;
46 primary_item_row item_rec_t;
47
48 procedure loadInterfaceError(p_interfaceTbl in out nocopy num_tbl_t,
49 p_text in varchar2,
50 p_type in number);
51
52 procedure groupValidateMLHeader(p_groupID in number,
53 p_validationLevel in number,
54 x_returnStatus out nocopy varchar2,
55 x_errorMsg out nocopy varchar2);
56
57 procedure lineValidateMLHeader(p_groupID in number,
58 p_validationLevel in number,
59 x_returnStatus out nocopy varchar2,
60 x_errorMsg out nocopy varchar2);
61
62 procedure setup(p_rowid in rowid);
63
64 procedure estimateLeadTime(p_rowid in rowid,
65 x_errorMsg out nocopy varchar2);
66
67 procedure validateProjectTask(p_rowid in rowid,
68 x_errorMsg out nocopy varchar2);
69
70 procedure validateClassCode(p_rowid in rowid,
71 x_errorMsg out nocopy varchar2);
72
73 procedure validateBOMRevision(p_rowid in rowid,
74 x_errorMsg out nocopy varchar2);
75
76 procedure validateRoutingRevision(p_rowid in rowid,
77 x_errorMsg out nocopy varchar2);
78
79 procedure validateStartQuantity(p_rowid in rowid,
80 x_errorMsg out nocopy varchar2);
81
82 procedure validateOvercompletion(p_rowid in rowid,
83 x_errorMsg out nocopy varchar2);
84
85 procedure validateSubinvLocator(p_rowid in rowid,
86 x_errorMsg out nocopy varchar2);
87
88 procedure validateLotNumber(p_rowid in rowid,
89 x_errorMsg out nocopy varchar2);
90
91 /* Fix for #6117094. Added following procedure */
92 procedure deriveScheduleDate(p_rowid in rowid,
93 x_errorMsg out nocopy varchar2);
94
95 procedure validateStatusType(p_rowid in rowid,
96 x_errorMsg out nocopy varchar2);
97
98 procedure validateBuildSequence(p_rowid in rowid,
99 x_errorMsg out nocopy varchar2);
100
101 procedure validateEndItemUnitNumber(p_rowid in rowid,
102 x_errorMsg out nocopy varchar2);
103
104 procedure validateDailyProductionRate(p_rowid in rowid,
105 x_errorMsg out nocopy varchar2);
106
107 procedure validateRepScheduleDates(p_rowid in rowid,
108 x_errorMsg out nocopy varchar2);
109
110 procedure validateKanban(p_rowid in rowid,
111 p_validationLevel in number,
112 x_errorMsg out nocopy varchar2);
113
114 --
115 -- This procedure defaults and validates all the columns in wip_job_schedule_interface table.
116 -- It does group validation where it can and does line validation otherwise. For a particular
117 -- column, the default and validation logic might be splitted in two different places if it needs
118 -- both line and group validation.
119 -- The only exception is for column serialization_start_op. The default and validation has to be
120 -- done after the routing explosion. We have two seperate APIs for this purpose.
121 --
122 procedure validateMLHeader(p_groupID in number,
123 p_validationLevel in number,
124 x_returnStatus out nocopy varchar2,
125 x_errorMsg out nocopy varchar2) is
126 l_params wip_logger.param_tbl_t;
127 l_procName varchar2(30) := 'validateMLHeader';
128 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
129 l_retStatus varchar2(1);
130
131 begin
132 x_returnStatus := fnd_api.g_ret_sts_success;
133 if (l_logLevel <= wip_constants.trace_logging) then
134 l_params(1).paramName := 'p_groupID';
135 l_params(1).paramValue := p_groupID;
136 l_params(2).paramName := 'p_validationLevel';
137 l_params(2).paramValue := p_validationLevel;
138 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
139 p_params => l_params,
140 x_returnStatus => x_returnStatus);
141 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
142 raise fnd_api.g_exc_unexpected_error;
143 end if;
144 end if;
145
146 -- do the group validation
147 groupValidateMLHeader(p_groupID,
148 p_validationLevel,
149 x_returnStatus,
150 x_errorMsg);
151 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
152 raise fnd_api.g_exc_unexpected_error;
153 end if;
154
155 -- do the line validation
156 lineValidateMLHeader(p_groupID,
157 p_validationLevel,
158 x_returnStatus,
159 x_errorMsg);
160 if ( x_returnStatus <> fnd_api.g_ret_sts_success ) then
161 raise fnd_api.g_exc_unexpected_error;
162 end if;
163
164 if (l_logLevel <= wip_constants.trace_logging) then
165 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
166 p_procReturnStatus => x_returnStatus,
167 p_msg => 'success',
168 x_returnStatus => l_retStatus);
169 end if;
170
171 exception
172 when fnd_api.g_exc_unexpected_error then
173 if(l_logLevel <= wip_constants.trace_logging) then
174 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
175 p_procReturnStatus => x_returnStatus,
176 p_msg => 'unexp error:' || x_errorMsg,
177 x_returnStatus => l_retStatus);
178 end if;
179 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
180 end validateMLHeader;
181
182
183 procedure groupValidateMLHeader(p_groupID in number,
184 p_validationLevel in number,
185 x_returnStatus out nocopy varchar2,
186 x_errorMsg out nocopy varchar2) is
187 l_params wip_logger.param_tbl_t;
188 l_procName varchar2(30) := 'groupValidateMLHeader';
189 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
190 l_retStatus varchar2(1);
191 l_msg varchar2(2000);
192
193 l_interfaceTbl num_tbl_t;
194 l_schedGroupID number;
195 l_description varchar2(240);
196 l_see_eng_items_flag varchar2(1);
197
198 --Fix for bug#4186944. Update REQUEST_ID
199 x_request_id number ;
200 x_program_id number ;
201 x_application_id number ;
202
203 begin
204 x_returnStatus := fnd_api.g_ret_sts_success;
205
206 --Fix for bug#4186944. Update REQUEST_ID
207 x_request_id := fnd_global.conc_request_id ;
208 x_program_id := fnd_global.conc_program_id ;
209 x_application_id := fnd_global.prog_appl_id ;
210
211 if (l_logLevel <= wip_constants.trace_logging) then
212 l_params(1).paramName := 'p_groupID';
213 l_params(1).paramValue := p_groupID;
214 l_params(2).paramName := 'p_validationLevel';
215 l_params(2).paramValue := p_validationLevel;
216 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
217 p_params => l_params,
218 x_returnStatus => x_returnStatus);
219 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
220 raise fnd_api.g_exc_unexpected_error;
221 end if;
222 end if;
223
224 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
225
226 --
227 -- validate load type
228 --
229 update wip_job_schedule_interface wjsi
230 set wjsi.process_status = WIP_CONSTANTS.ERROR,
231 wjsi.last_update_date = sysdate
232 where wjsi.group_id = p_groupID
233 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
234 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
235 and wjsi.load_type not in (WIP_CONSTANTS.CREATE_JOB,
236 WIP_CONSTANTS.CREATE_SCHED,
237 WIP_CONSTANTS.RESCHED_JOB,
238 WIP_CONSTANTS.CREATE_NS_JOB)
239 returning wjsi.interface_id bulk collect into l_interfaceTbl;
240
241 if ( sql%rowcount > 0 ) then
242 fnd_message.set_name('WIP', 'WIP_ML_LOAD_TYPE');
243 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
244 end if;
245
246 --
247 --Fix for bug#4186944. Update REQUEST_ID
248 --
249 update wip_job_schedule_interface wjsi
250 set wjsi.request_id = decode(x_request_id,-1,wjsi.request_id,x_request_id),
251 wjsi.program_id = decode(x_program_id,-1,wjsi.program_id,x_program_id),
252 wjsi.program_application_id = decode(x_application_id,-1,wjsi.program_application_id, x_application_id)
253 where wjsi.group_id = p_groupID
254 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
255 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
256
257
258 --
259 -- default and validate column created_by, created_by_name
260 --
261 update wip_job_schedule_interface wjsi
262 set wjsi.process_status = WIP_CONSTANTS.ERROR,
263 wjsi.last_update_date = sysdate
264 where wjsi.group_id = p_groupID
265 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
266 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
267 and wjsi.created_by_name is not null
268 and wjsi.created_by is null
269 and not exists (select 1
270 from fnd_user usr
271 where usr.user_name = wjsi.created_by_name)
272 returning wjsi.interface_id bulk collect into l_interfaceTbl;
273
274 if ( sql%rowcount > 0 ) then
275 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
276 fnd_message.set_token('COLUMN', 'CREATED_BY_NAME', false);
277 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
278 end if;
279
280 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
281
282 update wip_job_schedule_interface wjsi
283 set wjsi.process_status = WIP_CONSTANTS.WARNING,
284 wjsi.last_update_date = sysdate
285 where wjsi.group_id = p_groupID
286 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
287 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
288 and wjsi.created_by_name is not null
289 and wjsi.created_by is not null
290 returning wjsi.interface_id bulk collect into l_interfaceTbl;
291
292 if ( sql%rowcount > 0 ) then
293 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
294 fnd_message.set_token('COLUMN', 'CREATED_BY_NAME', false);
295 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
296 end if;
297
298 update wip_job_schedule_interface wjsi
299 set wjsi.created_by = (select usr.user_id
300 from fnd_user usr
301 where usr.user_name = wjsi.created_by_name),
302 wjsi.last_update_date = sysdate
303 where wjsi.group_id = p_groupID
304 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
305 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
306 and wjsi.created_by_name is not null
307 and wjsi.created_by is null;
308
309 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
310 update wip_job_schedule_interface wjsi
311 set wjsi.process_status = WIP_CONSTANTS.ERROR,
312 wjsi.last_update_date = sysdate
313 where wjsi.group_id = p_groupID
314 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
315 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
316 and not exists (select 1
317 from fnd_user usr
318 where usr.user_id = wjsi.created_by
319 and sysdate between usr.start_date and nvl(end_date, sysdate))
320 returning wjsi.interface_id bulk collect into l_interfaceTbl;
321
322 if ( sql%rowcount > 0 ) then
323 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
324 fnd_message.set_token('COLUMN', 'CREATED_BY', false);
325 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
326 end if;
327
328
329 --
330 -- default and validate last_updated_by_name and last_updated_by
331 --
332 update wip_job_schedule_interface wjsi
333 set wjsi.process_status = WIP_CONSTANTS.ERROR,
334 wjsi.last_update_date = sysdate
335 where wjsi.group_id = p_groupID
336 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
337 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
338 and wjsi.last_updated_by_name is not null
339 and wjsi.last_updated_by is null
340 and not exists (select 1
341 from fnd_user usr
342 where usr.user_name = wjsi.last_updated_by_name)
343 returning wjsi.interface_id bulk collect into l_interfaceTbl;
344
345 if ( sql%rowcount > 0 ) then
346 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
347 fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY_NAME', false);
348 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
349 end if;
350 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
351
352 update wip_job_schedule_interface wjsi
353 set wjsi.process_status = WIP_CONSTANTS.WARNING,
354 wjsi.last_update_date = sysdate
355 where wjsi.group_id = p_groupID
356 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
357 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
358 and wjsi.last_updated_by_name is not null
359 and wjsi.last_updated_by is not null
360 returning wjsi.interface_id bulk collect into l_interfaceTbl;
361
362 if ( sql%rowcount > 0 ) then
363 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
364 fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY_NAME', false);
365 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
366 end if;
367
368 update wip_job_schedule_interface wjsi
369 set wjsi.last_updated_by = (select usr.user_id
370 from fnd_user usr
371 where usr.user_name = wjsi.last_updated_by_name),
372 wjsi.last_update_date = sysdate
373 where wjsi.group_id = p_groupID
374 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
375 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
376 and wjsi.last_updated_by_name is not null
377 and wjsi.last_updated_by is null;
378
379 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
380
381 update wip_job_schedule_interface wjsi
382 set wjsi.process_status = WIP_CONSTANTS.ERROR,
383 wjsi.last_update_date = sysdate
384 where wjsi.group_id = p_groupID
385 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
386 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
387 and not exists (select 1
388 from fnd_user usr
389 where usr.user_id = wjsi.last_updated_by
390 and sysdate between usr.start_date and nvl(end_date, sysdate))
391 returning wjsi.interface_id bulk collect into l_interfaceTbl;
392
393 if ( sql%rowcount > 0 ) then
394 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
395 fnd_message.set_token('COLUMN', 'LAST_UPDATED_BY', false);
396 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
397 end if;
398
399
400 --
401 -- default and validate organization_code and organization_id
402 --
403 -- Bug 4890514. Performance Fix
404 -- saugupta 25th-May-2006
405 update wip_job_schedule_interface wjsi
406 set wjsi.process_status = WIP_CONSTANTS.ERROR,
407 wjsi.last_update_date = sysdate
408 where wjsi.group_id = p_groupID
409 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
410 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
411 and wjsi.organization_code is not null
412 and wjsi.organization_id is null
413 and not exists (select 1
414 from mtl_parameters ood
415 where ood.organization_code = wjsi.organization_code)
416 returning wjsi.interface_id bulk collect into l_interfaceTbl;
417
418 if ( sql%rowcount > 0 ) then
419 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
420 fnd_message.set_token('COLUMN', 'ORGANIZATION_CODE', false);
421 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
422 end if;
423
424 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
425
426 update wip_job_schedule_interface wjsi
427 set wjsi.process_status = WIP_CONSTANTS.WARNING,
428 wjsi.last_update_date = sysdate
429 where wjsi.group_id = p_groupID
430 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
431 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
432 and wjsi.organization_code is not null
433 and wjsi.organization_id is not null
434 returning wjsi.interface_id bulk collect into l_interfaceTbl;
435
436 if ( sql%rowcount > 0 ) then
437 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
438 fnd_message.set_token('COLUMN', 'ORGANIZATION_CODE', false);
439 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
440 end if;
441
442 -- Bug 4890514. Performance Fix
443 -- saugupta 25th-May-2006
444 update wip_job_schedule_interface wjsi
445 set wjsi.organization_id = (select ood.organization_id
446 from mtl_parameters ood
447 where ood.organization_code = wjsi.organization_code),
448 wjsi.last_update_date = sysdate
449 where wjsi.group_id = p_groupID
450 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
451 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
452 and wjsi.organization_code is not null
453 and wjsi.organization_id is null;
454
455
456 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
457 -- Bug 4890514. Performance Fix
458 -- saugupta 25th-May-2006
459 update wip_job_schedule_interface wjsi
460 set wjsi.process_status = WIP_CONSTANTS.ERROR,
461 wjsi.last_update_date = sysdate
462 where wjsi.group_id = p_groupID
463 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
464 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
465 and not exists (select 1
466 from wip_parameters wp,
467 mtl_parameters mp,
468 hr_organization_units ood
469 where wp.organization_id = mp.organization_id
470 and wp.organization_id = ood.organization_id
471 and wp.organization_id = wjsi.organization_id
472 and sysdate < nvl(ood.date_to, sysdate + 1))
473 returning wjsi.interface_id bulk collect into l_interfaceTbl;
474
475 if ( sql%rowcount > 0 ) then
476 fnd_message.set_name('WIP', 'WIP_ML_ORGANIZATION_ID');
477 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
478 end if;
479
480 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
481
482
483 --
484 -- default and validate job_name and wip_entity_id column
485 --
486 update wip_job_schedule_interface wjsi
487 set wjsi.process_status = WIP_CONSTANTS.ERROR,
488 wjsi.last_update_date = sysdate
489 where wjsi.group_id = p_groupID
490 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
491 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
492 and wjsi.job_name is not null
493 and wjsi.wip_entity_id is null
494 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
495 and not exists (select 1
496 from wip_entities we
497 where we.wip_entity_name = wjsi.job_name
498 and we.organization_id = wjsi.organization_id)
499 returning wjsi.interface_id bulk collect into l_interfaceTbl;
500
501 if ( sql%rowcount > 0 ) then
502 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
503 fnd_message.set_token('COLUMN', 'JOB_NAME', false);
504 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
505 end if;
506
507 update wip_job_schedule_interface wjsi
508 set wjsi.process_status = WIP_CONSTANTS.WARNING,
509 wjsi.last_update_date = sysdate
510 where wjsi.group_id = p_groupID
511 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
512 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
513 and wjsi.job_name is not null
514 and ( (wjsi.wip_entity_id is not null
515 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB) OR
516 (wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED) )
517 returning wjsi.interface_id bulk collect into l_interfaceTbl;
518
519 if ( sql%rowcount > 0 ) then
520 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
521 fnd_message.set_token('COLUMN', 'JOB_NAME', false);
522 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
523 end if;
524
525 update wip_job_schedule_interface wjsi
526 set wjsi.job_name = fnd_profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval,
527 wjsi.last_update_date = sysdate
528 where wjsi.group_id = p_groupID
529 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
530 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
531 and wjsi.job_name is null
532 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB);
533
534 update wip_job_schedule_interface wjsi
535 set wjsi.wip_entity_id = (select we.wip_entity_id
536 from wip_entities we
537 where we.wip_entity_name = wjsi.job_name
538 and we.organization_id = wjsi.organization_id),
539 wjsi.last_update_date = sysdate
540 where wjsi.group_id = p_groupID
541 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
542 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
543 and wjsi.job_name is not null
544 and wjsi.wip_entity_id is null
545 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB;
546
547 update wip_job_schedule_interface wjsi
548 set wjsi.process_status = WIP_CONSTANTS.ERROR,
549 wjsi.last_update_date = sysdate
550 where wjsi.group_id = p_groupID
551 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
552 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
553 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
554 and exists (select 1
555 from wip_entities we
556 where we.wip_entity_name = wjsi.job_name
557 and we.organization_id = wjsi.organization_id)
558 returning wjsi.interface_id bulk collect into l_interfaceTbl;
559
560 if ( sql%rowcount > 0 ) then
561 fnd_message.set_name('WIP', 'WIP_ML_JOB_NAME');
562 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
563 end if;
564
565 --
566 -- validate wip entity id
567 --
568 update wip_job_schedule_interface wjsi
569 set wjsi.process_status = WIP_CONSTANTS.WARNING,
570 wjsi.last_update_date = sysdate
571 where wjsi.group_id = p_groupID
572 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
573 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
574 and wjsi.wip_entity_id is not null
575 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
576 WIP_CONSTANTS.CREATE_NS_JOB,
577 WIP_CONSTANTS.CREATE_SCHED)
578 returning wjsi.interface_id bulk collect into l_interfaceTbl;
579
580 if ( sql%rowcount > 0 ) then
581 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
582 fnd_message.set_token('COLUMN', 'WIP_ENTITY_ID', false);
583 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
584 end if;
585
586 update wip_job_schedule_interface wjsi
587 set wip_entity_id = wip_entities_s.nextval,
588 last_update_date = sysdate
589 where wjsi.group_id = p_groupID
590 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
591 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
592 and wjsi.wip_entity_id is null
593 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
594 WIP_CONSTANTS.CREATE_NS_JOB);
595
596 update wip_job_schedule_interface wjsi
597 set wjsi.process_status = WIP_CONSTANTS.ERROR,
598 wjsi.last_update_date = sysdate
599 where wjsi.group_id = p_groupID
600 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
601 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
602 and ( ( wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
603 and not exists (select 1
604 from wip_entities we
605 where we.organization_id = wjsi.organization_id
606 and we.wip_entity_id = wjsi.wip_entity_id))
607 OR ( wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
608 and exists (select 1
609 from wip_entities we
610 where we.organization_id = wjsi.organization_id
611 and we.wip_entity_id = wjsi.wip_entity_id)))
612 returning wjsi.interface_id bulk collect into l_interfaceTbl;
613
614 if ( sql%rowcount > 0 ) then
615 fnd_message.set_name('WIP', 'WIP_ML_WIP_ENTITY_ID');
616 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
617 end if;
618
619 --
620 -- validate for entity type
621 --
622 update wip_job_schedule_interface wjsi
623 set wjsi.process_status = WIP_CONSTANTS.ERROR,
624 wjsi.last_update_date = sysdate
625 where wjsi.group_id = p_groupID
626 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
627 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
628 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
629 and exists (select 1
630 from wip_entities we
631 where we.wip_entity_id = wjsi.wip_entity_id
632 and we.organization_id = wjsi.organization_id
633 and we.entity_type <> 1)
634 returning wjsi.interface_id bulk collect into l_interfaceTbl;
635
636 if ( sql%rowcount > 0 ) then
637 fnd_message.set_name('WIP', 'WIP_ML_ENTITY_TYPE');
638 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
639 end if;
640
641 --
642 -- default and validate repetitve_schedule_id
643 --
644 update wip_job_schedule_interface wjsi
645 set wjsi.process_status = WIP_CONSTANTS.WARNING,
646 wjsi.last_update_date = sysdate
647 where wjsi.group_id = p_groupID
648 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
649 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
650 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
651 and wjsi.repetitive_schedule_id is not null
652 returning wjsi.interface_id bulk collect into l_interfaceTbl;
653
654 if ( sql%rowcount > 0 ) then
655 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
656 fnd_message.set_token('COLUMN', 'REPETITIVE_SCHEDULE_ID', false);
657 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
658 end if;
659
660 update wip_job_schedule_interface wjsi
661 set wjsi.repetitive_schedule_id = wip_repetitive_schedules_s.nextval,
662 wjsi.last_update_date = sysdate
663 where wjsi.group_id = p_groupID
664 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
665 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
666 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED;
667
668 --
669 -- default and validate schedule_group_id and schedule_group_name
670 --
671 update wip_job_schedule_interface wjsi
672 set wjsi.process_status = WIP_CONSTANTS.WARNING,
673 wjsi.last_update_date = sysdate
674 where wjsi.group_id = p_groupID
675 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
676 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
677 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
678 and wjsi.schedule_group_id is not null
679 returning wjsi.interface_id bulk collect into l_interfaceTbl;
680
681 if ( sql%rowcount > 0 ) then
682 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
683 fnd_message.set_token('COLUMN', 'SCHEDULE_GRPUP_ID', false);
684 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
685 end if;
686
687 update wip_job_schedule_interface wjsi
688 set wjsi.process_status = WIP_CONSTANTS.WARNING,
689 wjsi.last_update_date = sysdate
690 where wjsi.group_id = p_groupID
691 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
692 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
693 and (wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED or
694 wjsi.schedule_group_id is not null)
695 and wjsi.schedule_group_name is not null
696 returning wjsi.interface_id bulk collect into l_interfaceTbl;
697
698 if ( sql%rowcount > 0 ) then
699 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
700 fnd_message.set_token('COLUMN', 'SCHEDULE_GRPUP_NAME', false);
701 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
702 end if;
703
704 update wip_job_schedule_interface wjsi
705 set wjsi.process_status = WIP_CONSTANTS.ERROR,
706 wjsi.last_update_date = sysdate
707 where wjsi.group_id = p_groupID
708 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
709 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
710 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
711 WIP_CONSTANTS.RESCHED_JOB,
712 WIP_CONSTANTS.CREATE_NS_JOB)
713 and wjsi.schedule_group_id is null
714 and wjsi.schedule_group_name is not null
715 and not exists (select 1
716 from wip_schedule_groups_val_v sg
717 where sg.schedule_group_name = wjsi.schedule_group_name
718 and sg.organization_id = wjsi.organization_id)
719 returning wjsi.interface_id bulk collect into l_interfaceTbl;
720
721 if ( sql%rowcount > 0 ) then
722 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
723 fnd_message.set_token('COLUMN', 'SCHEDULE_GROUP_NAME', false);
724 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
725 end if;
726
727 update wip_job_schedule_interface wjsi
728 set wjsi.schedule_group_id =
729 (select sg.schedule_group_id
730 from wip_schedule_groups_val_v sg
731 where sg.schedule_group_name = wjsi.schedule_group_name
732 and sg.organization_id = wjsi.organization_id),
733 wjsi.last_update_date = sysdate
734 where wjsi.group_id = p_groupID
735 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
736 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
737 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
738 WIP_CONSTANTS.RESCHED_JOB,
739 WIP_CONSTANTS.CREATE_NS_JOB)
740 and wjsi.schedule_group_id is null
741 and wjsi.schedule_group_name is not null;
742
743 -- if still null, default from job if load type is reschedule job
744 -- Bug 15903332
745 /* update wip_job_schedule_interface wjsi
746 set wjsi.schedule_group_id =
747 (select wdj.schedule_group_id
748 from wip_discrete_jobs wdj
749 where wdj.wip_entity_id = wjsi.wip_entity_id
750 and wdj.organization_id = wjsi.organization_id),
751 wjsi.last_update_date = sysdate
752 where wjsi.group_id = p_groupID
753 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
754 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
755 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
756 and wjsi.schedule_group_id is null; */
757
758 -- if still null, try to default from the delivery_id provided
759 update wip_job_schedule_interface wjsi
760 set wjsi.schedule_group_id =
761 (select wsg.schedule_group_id
762 from wip_schedule_groups wsg,
763 wsh_new_deliveries wds
764 where wds.delivery_id = wjsi.delivery_id
765 and wsg.schedule_group_name = wds.name
766 and wsg.organization_id = wjsi.organization_id),
767 wjsi.last_update_date = sysdate
768 where wjsi.group_id = p_groupID
769 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
770 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
771 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
772 WIP_CONSTANTS.RESCHED_JOB,
773 WIP_CONSTANTS.CREATE_NS_JOB)
774 and wjsi.schedule_group_id is null
775 and wjsi.source_code = 'WICDOL'
776 and wjsi.delivery_id is not null
777 and exists (select wsg.schedule_group_id
778 from wip_schedule_groups wsg,
779 wsh_new_deliveries wds
780 where wds.delivery_id = wjsi.delivery_id
781 and wsg.schedule_group_name = wds.name
782 and wsg.organization_id = wjsi.organization_id);
783
784 -- if still null and loading from CTO, insert new groups
785 select wjsi.interface_id
786 bulk collect into l_interfaceTbl
787 from wip_job_schedule_interface wjsi
788 where wjsi.group_id = p_groupID
789 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
790 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
791 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
792 WIP_CONSTANTS.RESCHED_JOB,
793 WIP_CONSTANTS.CREATE_NS_JOB)
794 and wjsi.schedule_group_id is null
795 and wjsi.source_code = 'WICDOL'
796 and wjsi.delivery_id is not null;
797
798 if ( l_interfaceTbl.count > 0 ) then
799 for i in 1 .. l_interfaceTbl.count loop
800 select wip_schedule_groups_s.nextval into l_schedGroupID from dual;
801 insert into wip_schedule_groups(
802 schedule_group_id,
803 schedule_group_name,
804 organization_id,
805 description,
806 created_by,
807 last_updated_by,
808 creation_date,
809 last_update_date)
810 select l_schedGroupID,
811 wds.name,
812 wjsi.organization_id,
813 to_char(sysdate),
814 fnd_global.user_id,
815 fnd_global.user_id,
816 sysdate,
817 sysdate
818 from wsh_new_deliveries wds,
819 wip_job_schedule_interface wjsi
820 where wds.delivery_id = wjsi.delivery_id
821 and wjsi.interface_id = l_interfaceTbl(i);
822
823 update wip_job_schedule_interface
824 set schedule_group_id = l_schedGroupID,
825 last_update_date = sysdate
826 where interface_id = l_interfaceTbl(i);
827 end loop;
828 l_interfaceTbl.delete;
829 end if;
830
831 update wip_job_schedule_interface wjsi
832 set wjsi.process_status = WIP_CONSTANTS.ERROR,
833 wjsi.last_update_date = sysdate
834 where wjsi.group_id = p_groupID
835 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
836 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
837 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
838 WIP_CONSTANTS.RESCHED_JOB,
839 WIP_CONSTANTS.CREATE_NS_JOB)
840 and wjsi.schedule_group_id is not null
841 and not exists (select 1
842 from wip_schedule_groups_val_v sg
843 where sg.schedule_group_id = wjsi.schedule_group_id
844 and sg.organization_id = wjsi.organization_id)
845 returning wjsi.interface_id bulk collect into l_interfaceTbl;
846
847 if ( sql%rowcount > 0 ) then
848 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULE_GROUP');
849 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
850 end if;
851
852 --
853 -- default build_sequence
854 --
855 update wip_job_schedule_interface wjsi
856 set wjsi.process_status = WIP_CONSTANTS.WARNING,
857 wjsi.last_update_date = sysdate
858 where wjsi.group_id = p_groupID
859 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
860 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
861 and wjsi.build_sequence is not null
862 and wjsi.load_type = wip_constants.create_sched
863 returning wjsi.interface_id bulk collect into l_interfaceTbl;
864
865 if ( sql%rowcount > 0 ) then
866 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
867 fnd_message.set_token('COLUMN', 'BUILD_SEQUENCE', false);
868 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
869 end if;
870
871 --
872 -- default and validate line_code and line_id
873 --
874
875 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
876
877 update wip_job_schedule_interface wjsi
878 set wjsi.process_status = WIP_CONSTANTS.ERROR,
879 wjsi.last_update_date = sysdate
880 where wjsi.group_id = p_groupID
881 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
882 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
883 and wjsi.line_code is not null
884 and wjsi.line_id is null
885 and not exists (select 1
886 from wip_lines_val_v wl
887 where wl.line_code = wjsi.line_code
888 and wl.organization_id = wjsi.organization_id)
889 returning wjsi.interface_id bulk collect into l_interfaceTbl;
890
891 if ( sql%rowcount > 0 ) then
892 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
893 fnd_message.set_token('COLUMN', 'LINE_CODE', false);
894 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
895 end if;
896 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
897
898 update wip_job_schedule_interface wjsi
899 set wjsi.process_status = WIP_CONSTANTS.WARNING,
900 wjsi.last_update_date = sysdate
901 where wjsi.group_id = p_groupID
902 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
903 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
904 and wjsi.line_code is not null
905 and wjsi.line_id is not null
906 returning wjsi.interface_id bulk collect into l_interfaceTbl;
907
908 if ( sql%rowcount > 0 ) then
909 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
910 fnd_message.set_token('COLUMN', 'LINE_CODE', false);
911 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
912 end if;
913
914 update wip_job_schedule_interface wjsi
915 set line_id = (select wl.line_id
916 from wip_lines_val_v wl
917 where wl.line_code = wjsi.line_code
918 and wl.organization_id = wjsi.organization_id),
919 last_update_date = sysdate
920 where wjsi.group_id = p_groupID
921 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
922 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
923 and line_code is not null
924 and line_id is null;
925
926 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
927
928 update wip_job_schedule_interface wjsi
929 set wjsi.process_status = WIP_CONSTANTS.ERROR,
930 wjsi.last_update_date = sysdate
931 where wjsi.group_id = p_groupID
932 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
933 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
934 and wjsi.line_id is not null
935 and not exists (select 1
936 from wip_lines_val_v wl
937 where wl.line_id = wjsi.line_id
938 and wl.organization_id = wjsi.organization_id)
939 returning wjsi.interface_id bulk collect into l_interfaceTbl;
940
941 if ( sql%rowcount > 0 ) then
942 fnd_message.set_name('WIP', 'WIP_ML_LINE_ID');
943 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
944 end if;
945
946 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
947
948 -- ignore serialization_start_op for repetitive
949 update wip_job_schedule_interface wjsi
950 set wjsi.process_status = WIP_CONSTANTS.WARNING,
951 wjsi.last_update_date = sysdate
952 where wjsi.group_id = p_groupID
953 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
954 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
955 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
956 and wjsi.serialization_start_op is not null
957 returning wjsi.interface_id bulk collect into l_interfaceTbl;
958
959 if ( sql%rowcount > 0 ) then
960 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
961 fnd_message.set_token('COLUMN', 'SERIALIZATION_START_OP', false);
962 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
963 end if;
964
965 --
966 -- default and validate project_number and project_id
967 --
968 update wip_job_schedule_interface wjsi
969 set wjsi.process_status = WIP_CONSTANTS.WARNING,
970 wjsi.last_update_date = sysdate
971 where wjsi.group_id = p_groupID
972 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
973 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
974 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
975 and wjsi.project_number is not null
976 returning wjsi.interface_id bulk collect into l_interfaceTbl;
977
978 if ( sql%rowcount > 0 ) then
979 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
980 fnd_message.set_token('COLUMN', 'PROJECT_NUMBER', false);
981 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
982 end if;
983
984 update wip_job_schedule_interface wjsi
985 set wjsi.process_status = WIP_CONSTANTS.WARNING,
986 wjsi.last_update_date = sysdate
987 where wjsi.group_id = p_groupID
988 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
989 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
990 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
991 and wjsi.project_id is not null
992 returning wjsi.interface_id bulk collect into l_interfaceTbl;
993
994 if ( sql%rowcount > 0 ) then
995 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
996 fnd_message.set_token('COLUMN', 'PROJECT_ID', false);
997 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
998 end if;
999
1000 --
1001 -- default and validate task_number and task_id
1002 --
1003 update wip_job_schedule_interface wjsi
1004 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1005 wjsi.last_update_date = sysdate
1006 where wjsi.group_id = p_groupID
1007 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1008 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1009 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
1010 and wjsi.task_number is not null
1011 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1012
1013 if ( sql%rowcount > 0 ) then
1014 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1015 fnd_message.set_token('COLUMN', 'TASK_NUMBER', false);
1016 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1017 end if;
1018
1019 update wip_job_schedule_interface wjsi
1020 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1021 wjsi.last_update_date = sysdate
1022 where wjsi.group_id = p_groupID
1023 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1024 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1025 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
1026 and wjsi.task_id is not null
1027 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1028
1029 if ( sql%rowcount > 0 ) then
1030 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1031 fnd_message.set_token('COLUMN', 'TASK_ID', false);
1032 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1033 end if;
1034
1035 --
1036 -- default and validate firm_planned_flag
1037 --
1038 update wip_job_schedule_interface wjsi
1039 set wjsi.firm_planned_flag = WIP_CONSTANTS.NO,
1040 wjsi.last_update_date = sysdate
1041 where wjsi.group_id = p_groupID
1042 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1043 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1044 and wjsi.firm_planned_flag is null
1045 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
1046 WIP_CONSTANTS.CREATE_NS_JOB,
1047 WIP_CONSTANTS.CREATE_SCHED);
1048
1049 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1050
1051 update wip_job_schedule_interface wjsi
1052 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1053 wjsi.last_update_date = sysdate
1054 where wjsi.group_id = p_groupID
1055 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1056 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1057 and ( (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB and
1058 wjsi.firm_planned_flag = WIP_CONSTANTS.YES)
1059 or (wjsi.firm_planned_flag = WIP_CONSTANTS.YES and
1060 wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1061 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1062 from wip_discrete_jobs wdj
1063 where wdj.wip_entity_id = wjsi.wip_entity_id
1064 and wdj.organization_id = wjsi.organization_id))
1065 or (wjsi.firm_planned_flag not in (WIP_CONSTANTS.YES, WIP_CONSTANTS.NO)))
1066 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1067
1068 if ( sql%rowcount > 0 ) then
1069 fnd_message.set_name('WIP', 'WIP_ML_FIRM_PLANNED_FLAG');
1070 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1071 end if;
1072 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1073
1074 --
1075 -- default description
1076 --
1077 fnd_message.set_name('WIP','WIP_MLD_DESC');
1078 fnd_message.set_token('LOAD_DATE', fnd_date.date_to_charDT(dateval => sysdate,calendar_aware => 2), false);
1079 l_description := fnd_message.get;
1080 update wip_job_schedule_interface wjsi
1081 set wjsi.description = l_description,
1082 wjsi.last_update_date = sysdate
1083 where wjsi.group_id = p_groupID
1084 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1085 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1086 and wjsi.description is null
1087 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB,
1088 WIP_CONSTANTS.CREATE_NS_JOB);
1089
1090 fnd_message.set_name('WIP','WIP_MLR_DESC');
1091 fnd_message.set_token('LOAD_DATE', fnd_date.date_to_charDT(dateval => sysdate,calendar_aware => 2), false);
1092 l_description := fnd_message.get;
1093 update wip_job_schedule_interface wjsi
1094 set wjsi.description = l_description,
1095 wjsi.last_update_date = sysdate
1096 where wjsi.group_id = p_groupID
1097 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1098 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1099 and wjsi.description is null
1100 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED;
1101
1102 --
1103 -- default and validate primary_item_id and primary_item_segments
1104 --
1105 update wip_job_schedule_interface wjsi
1106 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1107 wjsi.last_update_date = sysdate
1108 where wjsi.group_id = p_groupID
1109 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1110 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1111 and (wjsi.load_type = wip_constants.resched_job or
1112 wjsi.primary_item_id is not null)
1113 and wjsi.primary_item_segments is not null
1114 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1115
1116 if ( sql%rowcount > 0 ) then
1117 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1118 fnd_message.set_token('COLUMN', 'PRIMARY_ITEM_SEGMENTS', false);
1119 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1120 end if;
1121
1122 update wip_job_schedule_interface wjsi
1123 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1124 wjsi.last_update_date = sysdate
1125 where wjsi.group_id = p_groupID
1126 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1127 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1128 and wjsi.load_type = wip_constants.resched_job
1129 and wjsi.primary_item_id is not null
1130 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1131
1132 if ( sql%rowcount > 0 ) then
1133 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1134 fnd_message.set_token('COLUMN', 'PRIMARY_ITEM_ID', false);
1135 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1136 end if;
1137
1138 update wip_job_schedule_interface wjsi
1139 set primary_item_id = (select wdj.primary_item_id
1140 from wip_discrete_jobs wdj
1141 where wdj.organization_id = wjsi.organization_id
1142 and wdj.wip_entity_id = wjsi.wip_entity_id)
1143 where wjsi.group_id = p_groupID
1144 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1145 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1146 and wjsi.load_type = wip_constants.resched_job;
1147
1148 update wip_job_schedule_interface wjsi
1149 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1150 wjsi.last_update_date = sysdate
1151 where wjsi.group_id = p_groupID
1152 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1153 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1154 and wjsi.primary_item_segments is not null
1155 and wjsi.primary_item_id is null
1156 and wjsi.load_type in (wip_constants.create_job,
1157 wip_constants.create_ns_job,
1158 wip_constants.create_sched)
1159 and not exists (select 1
1160 from mtl_system_items_kfv msik
1161 where msik.organization_id = wjsi.organization_id
1162 and msik.concatenated_segments = wjsi.primary_item_segments)
1163 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1164
1165 if ( sql%rowcount > 0 ) then
1166 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
1167 fnd_message.set_token('COLUMN', 'PRIMARY_ITEM_SEGMENTS', false);
1168 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1169 end if;
1170
1171 update wip_job_schedule_interface wjsi
1172 set wjsi.primary_item_id = (select msik.inventory_item_id
1173 from mtl_system_items_kfv msik
1174 where msik.organization_id = wjsi.organization_id
1175 and msik.concatenated_segments = wjsi.primary_item_segments),
1176 wjsi.last_update_date = sysdate
1177 where wjsi.group_id = p_groupID
1178 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1179 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1180 and wjsi.load_type in (wip_constants.create_job,
1181 wip_constants.create_ns_job,
1182 wip_constants.create_sched)
1183 and wjsi.primary_item_segments is not null
1184 and wjsi.primary_item_id is null;
1185
1186 update wip_job_schedule_interface wjsi
1187 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1188 wjsi.last_update_date = sysdate
1189 where wjsi.group_id = p_groupID
1190 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1191 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1192 and wjsi.primary_item_id is not null
1193 and ( wjsi.load_type in (wip_constants.create_job, wip_constants.create_sched)
1194 or (wjsi.load_type = wip_constants.create_ns_job and wjsi.primary_item_id is not null) )
1195 and not exists (select 1
1196 from mtl_system_items msi
1197 where msi.organization_id = wjsi.organization_id
1198 and msi.inventory_item_id = wjsi.primary_item_id)
1199 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1200
1201 if ( sql%rowcount > 0 ) then
1202 fnd_message.set_name('WIP', 'WIP_ML_PRIMARY_ITEM_ID');
1203 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1204 end if;
1205
1206 l_see_eng_items_flag := fnd_profile.value('WIP_SEE_ENG_ITEMS');
1207 update wip_job_schedule_interface wjsi
1208 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1209 wjsi.last_update_date = sysdate
1210 where wjsi.group_id = p_groupID
1211 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1212 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1213 and ( (wjsi.primary_item_id is null and
1214 wjsi.load_type in (wip_constants.create_job, wip_constants.create_sched))
1215 or (wjsi.primary_item_id is not null and
1216 wjsi.load_type in (wip_constants.create_job,
1217 wip_constants.create_ns_job,
1218 wip_constants.create_sched) and
1219 ( 'Y' <> (select msi.build_in_wip_flag
1220 from mtl_system_items msi
1221 where msi.organization_id = wjsi.organization_id
1222 and msi.inventory_item_id = wjsi.primary_item_id) or
1223 'N' <> (select msi.pick_components_flag
1224 from mtl_system_items msi
1225 where msi.organization_id = wjsi.organization_id
1226 and msi.inventory_item_id = wjsi.primary_item_id) or
1227 (l_see_eng_items_flag = wip_constants.no and
1228 'Y' = (select msi.eng_item_flag
1229 from mtl_system_items msi
1230 where msi.organization_id = wjsi.organization_id
1231 and msi.inventory_item_id = wjsi.primary_item_id)))))
1232 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1233
1234 if ( sql%rowcount > 0 ) then
1235 fnd_message.set_name('WIP', 'WIP_ML_PRIMARY_ITEM_ID');
1236 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1237 end if;
1238
1239 update wip_job_schedule_interface wjsi
1240 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1241 wjsi.last_update_date = sysdate
1242 where wjsi.group_id = p_groupID
1243 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1244 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1245 and wjsi.load_type = wip_constants.create_sched
1246 and not exists (select 1
1247 from wip_repetitive_items wri
1248 where wri.line_id = wjsi.line_id
1249 and wri.primary_item_id = wjsi.primary_item_id
1250 and wri.organization_id = wjsi.organization_id)
1251 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1252
1253 if ( sql%rowcount > 0 ) then
1254 fnd_message.set_name('WIP', 'WIP_ML_REPETITIVE_ITEM');
1255 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1256 end if;
1257
1258 /* FP bug 4378684. No need to validate whether ATO item has bill or not.
1259 This validation is removed per request by CTO team */
1260
1261 /* update wip_job_schedule_interface wjsi
1262 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1263 wjsi.last_update_date = sysdate
1264 where wjsi.group_id = p_groupID
1265 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1266 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1267 and wjsi.load_type in (wip_constants.create_job,
1268 wip_constants.create_sched,
1269 wip_constants.create_ns_job)
1270 and wjsi.primary_item_id is not null
1271 and 'Y' = (select msi.replenish_to_order_flag
1272 from mtl_system_items msi
1273 where msi.organization_id = wjsi.organization_id
1274 and msi.inventory_item_id = wjsi.primary_item_id)
1275 and 4 = (select msi.bom_item_type
1276 from mtl_system_items msi
1277 where msi.organization_id = wjsi.organization_id
1278 and msi.inventory_item_id = wjsi.primary_item_id)
1279 and not exists (select 1
1280 from bom_bill_of_materials bom
1281 where bom.assembly_item_id = wjsi.primary_item_id
1282 and bom.organization_id = wjsi.organization_id
1283 and nvl(bom.alternate_bom_designator, '@@@') =
1284 nvl(wjsi.alternate_bom_designator, '@@@')
1285 and (bom.assembly_type = 1 or l_see_eng_items_flag = 1))
1286 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1287
1288 if ( sql%rowcount > 0 ) then
1289 fnd_message.set_name('WIP', 'WIP_ML_ATO_ITEM_NO_BOM');
1290 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1291 end if;
1292 */
1293
1294 --
1295 -- default and validate status_type
1296 -- more validation code in the validate line procedure
1297 --
1298 update wip_job_schedule_interface wjsi
1299 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1300 wjsi.last_update_date = sysdate
1301 where wjsi.group_id = p_groupID
1302 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1303 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1304 and wjsi.load_type = WIP_CONSTANTS.CREATE_SCHED
1305 and wjsi.status_type is not null
1306 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1307
1308 if ( sql%rowcount > 0 ) then
1309 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1310 fnd_message.set_token('COLUMN', 'STATUS_TYPE', false);
1311 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1312 end if;
1313
1314 update wip_job_schedule_interface wjsi
1315 set wjsi.status_type = WIP_CONSTANTS.UNRELEASED,
1316 wjsi.last_update_date = sysdate
1317 where wjsi.group_id = p_groupID
1318 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1319 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1320 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
1321 and wjsi.status_type is null;
1322
1323 update wip_job_schedule_interface wjsi
1324 set wjsi.status_type = (select wdj.status_type
1325 from wip_discrete_jobs wdj
1326 where wdj.wip_entity_id = wjsi.wip_entity_id
1327 and wdj.organization_id = wjsi.organization_id),
1328 wjsi.last_update_date = sysdate
1329 where wjsi.group_id = p_groupID
1330 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1331 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1332 and wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
1333 and wjsi.status_type is null;
1334
1335 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1336
1337 update wip_job_schedule_interface wjsi
1338 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1339 wjsi.last_update_date = sysdate
1340 where wjsi.group_id = p_groupID
1341 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1342 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1343 and ( (wjsi.load_type in (WIP_CONSTANTS.CREATE_NS_JOB,
1344 WIP_CONSTANTS.CREATE_JOB) and
1345 wjsi.status_type not in (WIP_CONSTANTS.UNRELEASED,
1346 WIP_CONSTANTS.RELEASED,
1347 WIP_CONSTANTS.HOLD))
1348 or (wjsi.load_type = wip_constants.resched_job and
1349 wjsi.status_type is not null and
1350 wjsi.status_type not in (wip_constants.unreleased,
1351 wip_constants.released,
1352 wip_constants.comp_chrg,
1353 wip_constants.hold,
1354 wip_constants.cancelled)) )
1355 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1356
1357 if ( sql%rowcount > 0 ) then
1358 fnd_message.set_name('WIP', 'WIP_ML_STATUS_TYPE');
1359 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1360 end if;
1361
1362 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1363
1364
1365
1366 --
1367 -- default and validate routing_reference and routing_reference_id
1368 --
1369 /* Modified for bug 5479283. while re-scheduling non-std job we do consider reference fields. */
1370 update wip_job_schedule_interface wjsi
1371 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1372 wjsi.last_update_date = sysdate
1373 where wjsi.group_id = p_groupID
1374 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1375 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1376 and ( wjsi.load_type in (wip_constants.create_job,
1377 wip_constants.create_sched,
1378 wip_constants.resched_job)
1379 or (wjsi.load_type = wip_constants.create_ns_job and
1380 wjsi.routing_reference_id is not null) )
1381 and wjsi.routing_reference_segments is not null
1382 and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1383 from wip_discrete_jobs wdj
1384 where wdj.wip_entity_id = wjsi.wip_entity_id
1385 and wdj.organization_id = wjsi.organization_id)
1386 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1387
1388 if ( sql%rowcount > 0 ) then
1389 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1390 fnd_message.set_token('COLUMN', 'ROUTING_REFERENCE_SEGMENTS', false);
1391 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1392 end if;
1393 /* Modified for bug 5479283. while re-scheduling non-std job we do consider reference fields. */
1394 update wip_job_schedule_interface wjsi
1395 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1396 wjsi.last_update_date = sysdate
1397 where wjsi.group_id = p_groupID
1398 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1399 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1400 and wjsi.load_type in (wip_constants.create_job,
1401 wip_constants.create_sched,
1402 wip_constants.resched_job)
1403 and wjsi.routing_reference_id is not null
1404 and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1405 from wip_discrete_jobs wdj
1406 where wdj.wip_entity_id = wjsi.wip_entity_id
1407 and wdj.organization_id = wjsi.organization_id)
1408 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1409
1410 if ( sql%rowcount > 0 ) then
1411 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1412 fnd_message.set_token('COLUMN', 'ROUTING_REFERENCE_ID', false);
1413 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1414 end if;
1415
1416 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1417 /* Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1418 update wip_job_schedule_interface wjsi
1419 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1420 wjsi.last_update_date = sysdate
1421 where wjsi.group_id = p_groupID
1422 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1423 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1424 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1425 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB
1426 and WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1427 from wip_discrete_jobs wdj
1428 where wdj.wip_entity_id = wjsi.wip_entity_id
1429 and wdj.organization_id = wjsi.organization_id)))
1430 and wjsi.routing_reference_segments is not null
1431 and wjsi.routing_reference_id is null
1432 and not exists (select 1
1433 from mtl_system_items_kfv msik
1434 where msik.organization_id = wjsi.organization_id
1435 and msik.concatenated_segments = wjsi.routing_reference_segments)
1436 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1437
1438 if ( sql%rowcount > 0 ) then
1439 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
1440 fnd_message.set_token('COLUMN', 'ROUTING_REFERENCE_SEGMENTS', false);
1441 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1442 end if;
1443 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1444 /* Modified for bug 5479283. When Null is passed for routing reference, old value is maintained. */
1445 update wip_job_schedule_interface wjsi
1446 set routing_reference_id = decode(wjsi.routing_reference_segments,null,(select wdj.routing_reference_id
1447 from wip_discrete_jobs wdj
1448 where wdj.wip_entity_id = wjsi.wip_entity_id
1449 and wdj.organization_id = wjsi.organization_id),
1450 (select inventory_item_id
1451 from mtl_system_items_kfv msik
1452 where msik.organization_id = wjsi.organization_id
1453 and msik.concatenated_segments = wjsi.routing_reference_segments)),
1454 wjsi.last_update_date = sysdate
1455 where wjsi.group_id = p_groupID
1456 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1457 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1458 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1459 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1460 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1461 from wip_discrete_jobs wdj
1462 where wdj.wip_entity_id = wjsi.wip_entity_id
1463 and wdj.organization_id = wjsi.organization_id)))
1464 --and wjsi.routing_reference_segments is not null
1465 and wjsi.routing_reference_id is null ;
1466
1467 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1468 /*Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1469 update wip_job_schedule_interface wjsi
1470 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1471 wjsi.last_update_date = sysdate
1472 where wjsi.group_id = p_groupID
1473 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1474 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1475 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1476 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1477 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1478 from wip_discrete_jobs wdj
1479 where wdj.wip_entity_id = wjsi.wip_entity_id
1480 and wdj.organization_id = wjsi.organization_id)))
1481 and wjsi.routing_reference_id is not null
1482 and not exists (select 1
1483 from mtl_system_items_kfv msik
1484 where msik.organization_id = wjsi.organization_id
1485 and msik.inventory_item_id = wjsi.routing_reference_id)
1486 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1487
1488 if ( sql%rowcount > 0 ) then
1489 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
1490 fnd_message.set_token('COLUMN', 'ROUTING_REFERENCE_ID', false);
1491 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1492 end if;
1493 /*Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1494 update wip_job_schedule_interface wjsi
1495 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1496 wjsi.last_update_date = sysdate
1497 where wjsi.group_id = p_groupID
1498 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1499 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1500 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1501 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1502 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1503 from wip_discrete_jobs wdj
1504 where wdj.wip_entity_id = wjsi.wip_entity_id
1505 and wdj.organization_id = wjsi.organization_id)))
1506 and wjsi.routing_reference_id is not null
1507 and ('Y' <> (select msi.build_in_wip_flag
1508 from mtl_system_items msi
1509 where msi.organization_id = wjsi.organization_id
1510 and msi.inventory_item_id = wjsi.routing_reference_id) or
1511 'N' <> (select msi.pick_components_flag
1512 from mtl_system_items msi
1513 where msi.organization_id = wjsi.organization_id
1514 and msi.inventory_item_id = wjsi.routing_reference_id) or
1515 (l_see_eng_items_flag = wip_constants.no and
1516 'Y' = (select msi.eng_item_flag
1517 from mtl_system_items msi
1518 where msi.organization_id = wjsi.organization_id
1519 and msi.inventory_item_id = wjsi.routing_reference_id)))
1520 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1521
1522 if ( sql%rowcount > 0 ) then
1523 fnd_message.set_name('WIP', 'WIP_ML_ROUTING_REFERENCE_ID');
1524 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1525 end if;
1526 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1527
1528
1529 --
1530 -- default and validate alternate_routing_designator
1531 --
1532
1533 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1534
1535 /*Modified for bug 5479283. When Null is passed for alt routing designator, old value is maintained.
1536 When g_miss_char is passed for alt routing designator, updated to primary routing/bom.*/
1537 update wip_job_schedule_interface wjsi
1538 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1539 wjsi.last_update_date = sysdate
1540 where wjsi.group_id = p_groupID
1541 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1542 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1543 and wjsi.alternate_routing_designator <> fnd_api.g_miss_char
1544 and ( (wjsi.alternate_routing_designator is not null
1545 and not exists (select 1
1546 from bom_operational_routings bor
1547 where bor.alternate_routing_designator = wjsi.alternate_routing_designator
1548 and bor.organization_id = wjsi.organization_id
1549 and bor.assembly_item_id = decode(wjsi.load_type,
1550 wip_constants.create_ns_job, wjsi.routing_reference_id,
1551 wip_constants.resched_job, decode((select wdj.job_type
1552 from wip_discrete_jobs wdj
1553 where wdj.organization_id = wjsi.organization_id
1554 and wdj.wip_entity_id = wjsi.wip_entity_id),
1555 1, wjsi.primary_item_id, wjsi.routing_reference_id),
1556 wjsi.primary_item_id)
1557 and nvl(bor.cfm_routing_flag, 2) = 2
1558 and (bor.routing_type = 1 or (bor.routing_type = 2 and 1 = to_number(l_see_eng_items_flag))) ))
1559 /* bug 4227345 */
1560 or (wjsi.alternate_routing_designator = fnd_api.g_miss_char
1561 and wjsi.load_type in (wip_constants.create_job,
1562 wip_constants.create_ns_job,
1563 wip_constants.create_sched)))
1564 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1565 if ( sql%rowcount > 0 ) then
1566 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_ROUTING');
1567 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1568 end if;
1569 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1570
1571 /*Modified for bug 5479283. When Null is passed for alt routing designator, old value is maintained.
1572 When g_miss_char is passed for alt routing designator, updated to primary routing/bom.*/
1573 update wip_job_schedule_interface wjsi
1574 set wjsi.alternate_routing_designator = decode(wjsi.alternate_routing_designator,fnd_api.g_miss_char,null,null,(select wdj.alternate_routing_designator
1575 from wip_discrete_jobs wdj
1576 where wdj.organization_id = wjsi.organization_id
1577 and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_routing_designator),
1578 wjsi.last_update_date = sysdate
1579 where wjsi.group_id = p_groupID
1580 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1581 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1582 and wjsi.load_type = wip_constants.resched_job
1583 and (wjsi.alternate_routing_designator is null
1584 or wjsi.alternate_routing_designator = fnd_api.g_miss_char);
1585
1586
1587 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1588
1589 -- you can not change the routing designator for jobs not in unreleased status
1590 update wip_job_schedule_interface wjsi
1591 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1592 wjsi.last_update_date = sysdate
1593 where wjsi.group_id = p_groupID
1594 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1595 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1596 and wjsi.load_type = wip_constants.resched_job
1597 and nvl(wjsi.alternate_routing_designator, '@@-@@@') <>
1598 nvl( (select wdj.alternate_routing_designator
1599 from wip_discrete_jobs wdj
1600 where wdj.organization_id = wjsi.organization_id
1601 and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1602 and (select status_type
1603 from wip_discrete_jobs wdj
1604 where wdj.organization_id = wjsi.organization_id
1605 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1606 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1607
1608 if ( sql%rowcount > 0 ) then
1609 fnd_message.set_name('WIP', 'WIP_ALTERNATE_ROUTING_NOCHANGE');
1610 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1611 end if;
1612 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1613
1614 --
1615 -- default routing_revision and routing_revision_date
1616 --
1617 update wip_job_schedule_interface wjsi
1618 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1619 wjsi.last_update_date = sysdate
1620 where wjsi.group_id = p_groupID
1621 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1622 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1623 and wjsi.load_type = wip_constants.create_sched
1624 and wjsi.routing_revision_date is not null
1625 and wjsi.routing_revision is not null
1626 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1627
1628 if ( sql%rowcount > 0 ) then
1629 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1630 fnd_message.set_token('COLUMN', 'ROUTING_REVISION', false);
1631 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1632 end if;
1633
1634 /* Fix for bug 5020741. Do not default routing revision/revision date while updating jobs.
1635 update wip_job_schedule_interface wjsi
1636 set wjsi.routing_revision = (select wdj.routing_revision
1637 from wip_discrete_jobs wdj
1638 where wdj.organization_id = wjsi.organization_id
1639 and wdj.wip_entity_id = wjsi.wip_entity_id),
1640 wjsi.last_update_date = sysdate
1641 where wjsi.group_id = p_groupID
1642 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1643 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1644 and wjsi.load_type = wip_constants.resched_job
1645 and (wjsi.bom_revision = fnd_api.g_miss_char
1646 or wjsi.bom_revision is null);
1647
1648 update wip_job_schedule_interface wjsi
1649 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1650 wjsi.last_update_date = sysdate
1651 where wjsi.group_id = p_groupID
1652 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1653 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1654 and wjsi.load_type = wip_constants.resched_job
1655 and nvl(wjsi.routing_revision, '@@-@@@') <>
1656 nvl( (select wdj.routing_revision
1657 from wip_discrete_jobs wdj
1658 where wdj.organization_id = wjsi.organization_id
1659 and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1660 and (select status_type
1661 from wip_discrete_jobs wdj
1662 where wdj.organization_id = wjsi.organization_id
1663 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1664 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1665
1666 if ( sql%rowcount > 0 ) then
1667 fnd_message.set_name('WIP', 'WIP_ROUTING_REVISION_NOCHANGE');
1668 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1669 end if;
1670
1671 update wip_job_schedule_interface wjsi
1672 set wjsi.routing_revision_date = (select wdj.routing_revision_date
1673 from wip_discrete_jobs wdj
1674 where wdj.organization_id = wjsi.organization_id
1675 and wdj.wip_entity_id = wjsi.wip_entity_id),
1676 wjsi.last_update_date = sysdate
1677 where wjsi.group_id = p_groupID
1678 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1679 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1680 and wjsi.load_type = wip_constants.resched_job
1681 and ( wjsi.routing_revision_date is null
1682 or wjsi.routing_revision_date = fnd_api.g_miss_date);
1683
1684 update wip_job_schedule_interface wjsi
1685 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1686 wjsi.last_update_date = sysdate
1687 where wjsi.group_id = p_groupID
1688 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1689 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1690 and wjsi.load_type = wip_constants.resched_job
1691 and nvl(wjsi.routing_revision_date, fnd_api.g_miss_date) <>
1692 nvl((select wdj.routing_revision_date
1693 from wip_discrete_jobs wdj
1694 where wdj.organization_id = wjsi.organization_id
1695 and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
1696 and (select wdj.status_type
1697 from wip_discrete_jobs wdj
1698 where wdj.organization_id = wjsi.organization_id
1699 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1700 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1701
1702 if ( sql%rowcount > 0 ) then
1703 fnd_message.set_name('WIP', 'WIP_ROUTING_REV_DATE_NOCHANGE');
1704 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1705 end if;
1706 */
1707 --
1708 -- default and validate bom_reference and bom_reference_id
1709 --
1710 /* Modified for bug 5479283. while re-scheduling non-std job we do consider reference fields. */
1711 update wip_job_schedule_interface wjsi
1712 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1713 wjsi.last_update_date = sysdate
1714 where wjsi.group_id = p_groupID
1715 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1716 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1717 and ( wjsi.load_type in (wip_constants.create_job,
1718 wip_constants.create_sched,
1719 wip_constants.resched_job)
1720 or (wjsi.load_type = wip_constants.create_ns_job and
1721 wjsi.bom_reference_id is not null) )
1722 and wjsi.bom_reference_segments is not null
1723 and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1724 from wip_discrete_jobs wdj
1725 where wdj.wip_entity_id = wjsi.wip_entity_id
1726 and wdj.organization_id = wjsi.organization_id)
1727 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1728
1729 if ( sql%rowcount > 0 ) then
1730 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1731 fnd_message.set_token('COLUMN', 'BOM_REFERENCE_SEGMENTS', false);
1732 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1733 end if;
1734 /* Modified for bug 5479283. while re-scheduling non-std job we do consider reference fields. */
1735 update wip_job_schedule_interface wjsi
1736 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1737 wjsi.last_update_date = sysdate
1738 where wjsi.group_id = p_groupID
1739 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1740 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1741 and wjsi.load_type in (wip_constants.create_job,
1742 wip_constants.create_sched,
1743 wip_constants.resched_job)
1744 and wjsi.bom_reference_id is not null
1745 and WIP_CONSTANTS.NONSTANDARD <> (select wdj.job_type
1746 from wip_discrete_jobs wdj
1747 where wdj.wip_entity_id = wjsi.wip_entity_id
1748 and wdj.organization_id = wjsi.organization_id)
1749 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1750
1751 if ( sql%rowcount > 0 ) then
1752 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1753 fnd_message.set_token('COLUMN', 'BOM_REFERENCE_ID', false);
1754 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1755 end if;
1756
1757
1758 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1759 /*Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1760 update wip_job_schedule_interface wjsi
1761 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1762 wjsi.last_update_date = sysdate
1763 where wjsi.group_id = p_groupID
1764 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1765 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1766 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1767 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1768 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1769 from wip_discrete_jobs wdj
1770 where wdj.wip_entity_id = wjsi.wip_entity_id
1771 and wdj.organization_id = wjsi.organization_id)))
1772 and wjsi.bom_reference_segments is not null
1773 and wjsi.bom_reference_id is null
1774 and not exists (select 1
1775 from mtl_system_items_kfv msik
1776 where msik.organization_id = wjsi.organization_id
1777 and msik.concatenated_segments = wjsi.bom_reference_segments)
1778 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1779
1780 if ( sql%rowcount > 0 ) then
1781 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
1782 fnd_message.set_token('COLUMN', 'BOM_REFERENCE_SEGMENTS', false);
1783 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1784 end if;
1785 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1786 /*Modified for bug 5479283. When Null is passed for bom reference, old value is maintained. */
1787 update wip_job_schedule_interface wjsi
1788 set bom_reference_id = decode(wjsi.bom_reference_segments,null,(select wdj.bom_reference_id
1789 from wip_discrete_jobs wdj
1790 where wdj.wip_entity_id = wjsi.wip_entity_id
1791 and wdj.organization_id = wjsi.organization_id),
1792 (select inventory_item_id
1793 from mtl_system_items_kfv msik
1794 where msik.organization_id = wjsi.organization_id
1795 and msik.concatenated_segments = wjsi.bom_reference_segments)),
1796 wjsi.last_update_date = sysdate
1797 where wjsi.group_id = p_groupID
1798 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1799 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1800 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1801 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1802 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1803 from wip_discrete_jobs wdj
1804 where wdj.wip_entity_id = wjsi.wip_entity_id
1805 and wdj.organization_id = wjsi.organization_id)))
1806 --and wjsi.bom_reference_segments is not null
1807 and (wjsi.bom_reference_id is null or wjsi.bom_reference_id=fnd_api.g_miss_num);
1808
1809 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1810 /*Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1811 update wip_job_schedule_interface wjsi
1812 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1813 wjsi.last_update_date = sysdate
1814 where wjsi.group_id = p_groupID
1815 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1816 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1817 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1818 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1819 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1820 from wip_discrete_jobs wdj
1821 where wdj.wip_entity_id = wjsi.wip_entity_id
1822 and wdj.organization_id = wjsi.organization_id)))
1823 and wjsi.bom_reference_id is not null
1824 and not exists (select 1
1825 from mtl_system_items_kfv msik
1826 where msik.organization_id = wjsi.organization_id
1827 and msik.inventory_item_id = wjsi.bom_reference_id)
1828 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1829
1830 if ( sql%rowcount > 0 ) then
1831 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
1832 fnd_message.set_token('COLUMN', 'BOM_REFERENCE_ID', false);
1833 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1834 end if;
1835 /*Modified for bug 5479283. Validation should happen while re-scheduling non-std job */
1836 update wip_job_schedule_interface wjsi
1837 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1838 wjsi.last_update_date = sysdate
1839 where wjsi.group_id = p_groupID
1840 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1841 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1842 and (wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
1843 OR (wjsi.load_type = WIP_CONSTANTS.RESCHED_JOB and
1844 WIP_CONSTANTS.NONSTANDARD = (select wdj.job_type
1845 from wip_discrete_jobs wdj
1846 where wdj.wip_entity_id = wjsi.wip_entity_id
1847 and wdj.organization_id = wjsi.organization_id)))
1848 and wjsi.bom_reference_id is not null
1849 and ('Y' <> (select msi.build_in_wip_flag
1850 from mtl_system_items msi
1851 where msi.organization_id = wjsi.organization_id
1852 and msi.inventory_item_id = wjsi.bom_reference_id) or
1853 'N' <> (select msi.pick_components_flag
1854 from mtl_system_items msi
1855 where msi.organization_id = wjsi.organization_id
1856 and msi.inventory_item_id = wjsi.bom_reference_id) or
1857 (l_see_eng_items_flag = wip_constants.no and
1858 'Y' = (select msi.eng_item_flag
1859 from mtl_system_items msi
1860 where msi.organization_id = wjsi.organization_id
1861 and msi.inventory_item_id = wjsi.bom_reference_id)))
1862 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1863
1864 if ( sql%rowcount > 0 ) then
1865 fnd_message.set_name('WIP', 'WIP_ML_BOM_REFERENCE_ID');
1866 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1867 end if;
1868 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1869
1870
1871 --
1872 -- default and validate alternate_bom_designator
1873 --
1874 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1875 /*Modified for bug 5479283. When Null is passed for alt bom designator, old value is maintained.
1876 When g_miss_char is passed for alt bom designator, updated to primary routing/bom. */
1877 update wip_job_schedule_interface wjsi
1878 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1879 wjsi.last_update_date = sysdate
1880 where wjsi.group_id = p_groupID
1881 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1882 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1883 and wjsi.alternate_bom_designator <> fnd_api.g_miss_char
1884 and ( (wjsi.alternate_bom_designator is not null
1885 and not exists (select 1
1886 from bom_bill_alternates_v bba
1887 where bba.alternate_bom_designator = wjsi.alternate_bom_designator
1888 and bba.organization_id = wjsi.organization_id
1889 and bba.assembly_item_id = decode(wjsi.load_type,
1890 wip_constants.create_ns_job, wjsi.bom_reference_id,
1891 wip_constants.resched_job, decode((select wdj.job_type
1892 from wip_discrete_jobs wdj
1893 where wdj.organization_id = wjsi.organization_id
1894 and wdj.wip_entity_id = wjsi.wip_entity_id),
1895 1, wjsi.primary_item_id, wjsi.bom_reference_id),
1896 wjsi.primary_item_id)
1897 and (bba.assembly_type = 1 or (bba.assembly_type = 2 and 1 = to_number(l_see_eng_items_flag))) ))
1898 /* bug 4227345 */
1899 or (wjsi.alternate_bom_designator = fnd_api.g_miss_char
1900 and wjsi.load_type in (wip_constants.create_job,
1901 wip_constants.create_ns_job,
1902 wip_constants.create_sched)))
1903 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1904
1905 if ( sql%rowcount > 0 ) then
1906 fnd_message.set_name('WIP', 'WIP_ML_ALTERNATE_BOM');
1907 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1908 end if;
1909 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1910
1911 /*Modified for bug 5479283. When Null is passed for alt bom designator, old value is maintained.
1912 When g_miss_char is passed for alt bom designator, updated to primary routing/bom. */
1913
1914 update wip_job_schedule_interface wjsi
1915 set wjsi.alternate_bom_designator = decode(wjsi.alternate_bom_designator,fnd_api.g_miss_char,null,null,
1916 (select wdj.alternate_bom_designator
1917 from wip_discrete_jobs wdj
1918 where wdj.organization_id = wjsi.organization_id
1919 and wdj.wip_entity_id = wjsi.wip_entity_id),wjsi.alternate_bom_designator),
1920 wjsi.last_update_date = sysdate
1921 where wjsi.group_id = p_groupID
1922 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1923 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1924 and wjsi.load_type = wip_constants.resched_job
1925 and (wjsi.alternate_bom_designator is null
1926 or wjsi.alternate_bom_designator =fnd_api.g_miss_char);
1927
1928 -- you can not change the bom designator for jobs not in unreleased status
1929 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
1930 update wip_job_schedule_interface wjsi
1931 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1932 wjsi.last_update_date = sysdate
1933 where wjsi.group_id = p_groupID
1934 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1935 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1936 and wjsi.load_type = wip_constants.resched_job
1937 and nvl(wjsi.alternate_bom_designator, '@@-@@@') <>
1938 nvl( (select wdj.alternate_bom_designator
1939 from wip_discrete_jobs wdj
1940 where wdj.organization_id = wjsi.organization_id
1941 and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
1942 and (select status_type
1943 from wip_discrete_jobs wdj
1944 where wdj.organization_id = wjsi.organization_id
1945 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
1946 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1947
1948 if ( sql%rowcount > 0 ) then
1949 fnd_message.set_name('WIP', 'WIP_ALTERNATE_BOM_NOCHANGE');
1950 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
1951 end if;
1952 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
1953
1954 --
1955 -- default bom_revision and bom_revision_date
1956 --
1957 update wip_job_schedule_interface wjsi
1958 set wjsi.process_status = WIP_CONSTANTS.WARNING,
1959 wjsi.last_update_date = sysdate
1960 where wjsi.group_id = p_groupID
1961 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1962 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1963 and wjsi.load_type = wip_constants.create_sched
1964 and wjsi.bom_revision_date is not null
1965 and wjsi.bom_revision is not null
1966 returning wjsi.interface_id bulk collect into l_interfaceTbl;
1967
1968 if ( sql%rowcount > 0 ) then
1969 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
1970 fnd_message.set_token('COLUMN', 'BOM_REVISION', false);
1971 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
1972 end if;
1973
1974 /* Fix for bug 5020741. Do not default bom revision/revision date while updating jobs.
1975 update wip_job_schedule_interface wjsi
1976 set wjsi.bom_revision = (select wdj.bom_revision
1977 from wip_discrete_jobs wdj
1978 where wdj.organization_id = wjsi.organization_id
1979 and wdj.wip_entity_id = wjsi.wip_entity_id),
1980 wjsi.last_update_date = sysdate
1981 where wjsi.group_id = p_groupID
1982 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1983 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1984 and wjsi.load_type = wip_constants.resched_job
1985 and (wjsi.bom_revision = fnd_api.g_miss_char
1986 or wjsi.bom_revision is null);
1987
1988 update wip_job_schedule_interface wjsi
1989 set wjsi.process_status = WIP_CONSTANTS.ERROR,
1990 wjsi.last_update_date = sysdate
1991 where wjsi.group_id = p_groupID
1992 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
1993 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
1994 and wjsi.load_type = wip_constants.resched_job
1995 and nvl(wjsi.bom_revision, '@@-@@@') <>
1996 nvl( (select wdj.bom_revision
1997 from wip_discrete_jobs wdj
1998 where wdj.organization_id = wjsi.organization_id
1999 and wdj.wip_entity_id = wjsi.wip_entity_id), '@@-@@@')
2000 and (select status_type
2001 from wip_discrete_jobs wdj
2002 where wdj.organization_id = wjsi.organization_id
2003 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
2004 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2005
2006 if ( sql%rowcount > 0 ) then
2007 fnd_message.set_name('WIP', 'WIP_BOM_REVISION_NOCHANGE');
2008 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2009 end if;
2010
2011 update wip_job_schedule_interface wjsi
2012 set wjsi.bom_revision_date = (select wdj.bom_revision_date
2013 from wip_discrete_jobs wdj
2014 where wdj.organization_id = wjsi.organization_id
2015 and wdj.wip_entity_id = wjsi.wip_entity_id),
2016 wjsi.last_update_date = sysdate
2017 where wjsi.group_id = p_groupID
2018 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2019 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2020 and wjsi.load_type = wip_constants.resched_job
2021 and ( wjsi.bom_revision_date = fnd_api.g_miss_date
2022 or wjsi.bom_revision_date is null);
2023
2024 update wip_job_schedule_interface wjsi
2025 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2026 wjsi.last_update_date = sysdate
2027 where wjsi.group_id = p_groupID
2028 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2029 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2030 and wjsi.load_type = wip_constants.resched_job
2031 and nvl(wjsi.bom_revision_date, fnd_api.g_miss_date) <>
2032 nvl( (select wdj.bom_revision_date
2033 from wip_discrete_jobs wdj
2034 where wdj.organization_id = wjsi.organization_id
2035 and wdj.wip_entity_id = wjsi.wip_entity_id), fnd_api.g_miss_date)
2036 and (select status_type
2037 from wip_discrete_jobs wdj
2038 where wdj.organization_id = wjsi.organization_id
2039 and wdj.wip_entity_id = wjsi.wip_entity_id) <> 1
2040 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2041
2042 if ( sql%rowcount > 0 ) then
2043 fnd_message.set_name('WIP', 'WIP_BOM_REV_DATE_NOCHANGE');
2044 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2045 end if;
2046
2047 */
2048
2049
2050 --
2051 -- default and validate wip_supply_type
2052 --
2053 update wip_job_schedule_interface wjsi
2054 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2055 wjsi.last_update_date = sysdate
2056 where wjsi.group_id = p_groupID
2057 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2058 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2059 and wjsi.load_type in (wip_constants.create_sched, wip_constants.resched_job)
2060 and wjsi.wip_supply_type is not null
2061 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2062
2063 if ( sql%rowcount > 0 ) then
2064 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2065 fnd_message.set_token('COLUMN', 'WIP_SUPPLY_TYPE', false);
2066 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2067 end if;
2068
2069 update wip_job_schedule_interface wjsi
2070 set wjsi.wip_supply_type = wip_constants.based_on_bom,
2071 wjsi.last_update_date = sysdate
2072 where wjsi.group_id = p_groupID
2073 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2074 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2075 and wjsi.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB)
2076 and wjsi.wip_supply_type is null;
2077
2078 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
2079 update wip_job_schedule_interface wjsi
2080 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2081 wjsi.last_update_date = sysdate
2082 where wjsi.group_id = p_groupID
2083 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2084 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2085 and ( (wjsi.load_type in (WIP_CONSTANTS.CREATE_NS_JOB,
2086 WIP_CONSTANTS.CREATE_JOB) and
2087 wjsi.wip_supply_type not in (wip_constants.push,
2088 wip_constants.assy_pull,
2089 wip_constants.op_pull,
2090 wip_constants.bulk,
2091 wip_constants.vendor,
2092 wip_constants.phantom,
2093 wip_constants.based_on_bom))
2094 or (wjsi.load_type = wip_constants.create_ns_job and
2095 wjsi.primary_item_id is null and
2096 wjsi.wip_supply_type in (wip_constants.assy_pull,
2097 wip_constants.op_pull))
2098 or (wjsi.wip_supply_type = wip_constants.op_pull and
2099 not exists
2100 (select 1
2101 from bom_operational_routings bor
2102 where bor.organization_id = wjsi.organization_id
2103 and bor.assembly_item_id = decode(wjsi.load_type,
2104 wip_constants.create_ns_job, wjsi.routing_reference_id,
2105 wjsi.primary_item_id)
2106 and nvl(alternate_routing_designator, '@@@') =
2107 nvl(wjsi.alternate_routing_designator, '@@@')
2108 and nvl(cfm_routing_flag, 2) = 2)))
2109 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2110
2111 if ( sql%rowcount > 0 ) then
2112 fnd_message.set_name('WIP', 'WIP_ML_WIP_SUPPLY_TYPE');
2113 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2114 end if;
2115 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
2116
2117 --
2118 -- default and validate start_quantity
2119 --
2120 update wip_job_schedule_interface wjsi
2121 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2122 wjsi.last_update_date = sysdate
2123 where wjsi.group_id = p_groupID
2124 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2125 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2126 and wjsi.start_quantity is not null
2127 and ( wjsi.load_type = wip_constants.create_sched
2128 or (wjsi.load_type = wip_constants.resched_job and
2129 (select wdj.status_type
2130 from wip_discrete_jobs wdj
2131 where wdj.organization_id = wjsi.organization_id
2132 and wdj.wip_entity_id = wjsi.wip_entity_id) not in
2133 (wip_constants.unreleased,
2134 wip_constants.released,
2135 wip_constants.comp_chrg,
2136 wip_constants.hold)) )
2137 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2138
2139 if ( sql%rowcount > 0 ) then
2140 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2141 fnd_message.set_token('COLUMN', 'START_QUANTITY', false);
2142 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2143 end if;
2144
2145 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
2146 update wip_job_schedule_interface wjsi
2147 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2148 wjsi.last_update_date = sysdate
2149 where wjsi.group_id = p_groupID
2150 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2151 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2152 and ( wjsi.start_quantity < 0
2153 or (wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
2154 wjsi.start_quantity is null)
2155 or (wjsi.load_type = wip_constants.create_job and
2156 wjsi.start_quantity = 0)
2157 or (wjsi.load_type = wip_constants.resched_job and
2158 wjsi.start_quantity = 0 and
2159 wip_constants.standard = (select wdj.job_type
2160 from wip_discrete_jobs wdj
2161 where wdj.organization_id = wjsi.organization_id
2162 and wdj.wip_entity_id = wjsi.wip_entity_id)))
2163 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2164
2165 if ( sql%rowcount > 0 ) then
2166 fnd_message.set_name('WIP', 'WIP_ML_START_QUANTITY');
2167 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2168 end if;
2169
2170 update wip_job_schedule_interface wjsi
2171 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2172 wjsi.last_update_date = sysdate
2173 where wjsi.group_id = p_groupID
2174 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2175 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2176 and wjsi.load_type = wip_constants.resched_job
2177 and wjsi.start_quantity is not null
2178 and ( wjsi.start_quantity < (select wdj.quantity_completed
2179 from wip_discrete_jobs wdj
2180 where wdj.organization_id = wjsi.organization_id
2181 and wdj.wip_entity_id = wjsi.wip_entity_id)
2182 or (0 < (select count(*)
2183 from wip_reservations_v wr
2184 where wr.wip_entity_id = wjsi.wip_entity_id
2185 and wr.organization_id = wjsi.organization_id) and
2186 wjsi.start_quantity < (select sum(wr.primary_quantity)
2187 from wip_reservations_v wr
2188 where wr.organization_id = wjsi.organization_id
2189 and wr.wip_entity_id = wjsi.wip_entity_id)))
2190 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2191
2192 if ( sql%rowcount > 0 ) then
2193 fnd_message.set_name('WIP', 'WIP_ML_RESCHEDULE_QUANTITY');
2194 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2195 end if;
2196 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
2197
2198 --
2199 -- default and validate net_quantity
2200 --
2201 update wip_job_schedule_interface wjsi
2202 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2203 wjsi.last_update_date = sysdate
2204 where wjsi.group_id = p_groupID
2205 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2206 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2207 and wjsi.load_type = wip_constants.create_sched
2208 and wjsi.net_quantity is not null
2209 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2210
2211 if ( sql%rowcount > 0 ) then
2212 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2213 fnd_message.set_token('COLUMN', 'NET_QUANTITY', false);
2214 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2215 end if;
2216
2217 update wip_job_schedule_interface wjsi
2218 set wjsi.net_quantity = wjsi.start_quantity,
2219 wjsi.last_update_date = sysdate
2220 where wjsi.group_id = p_groupID
2221 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2222 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2223 and wjsi.load_type = wip_constants.create_job
2224 and wjsi.net_quantity is null;
2225
2226 update wip_job_schedule_interface wjsi
2227 set wjsi.net_quantity = (select decode(wdj.net_quantity,
2228 wdj.start_quantity, wjsi.start_quantity,
2229 least(wdj.net_quantity, nvl(wjsi.start_quantity, wdj.net_quantity)))
2230 from wip_discrete_jobs wdj
2231 where wdj.wip_entity_id = wjsi.wip_entity_id
2232 and wdj.organization_id = wjsi.organization_id),
2233 wjsi.last_update_date = sysdate
2234 where wjsi.group_id = p_groupID
2235 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2236 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2237 and wjsi.load_type = wip_constants.resched_job
2238 and wjsi.net_quantity is null;
2239
2240 update wip_job_schedule_interface wjsi
2241 set wjsi.net_quantity = 0,
2242 wjsi.last_update_date = sysdate
2243 where wjsi.group_id = p_groupID
2244 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2245 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2246 and wjsi.load_type = wip_constants.create_ns_job
2247 and wjsi.net_quantity is null;
2248
2249 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
2250 update wip_job_schedule_interface wjsi
2251 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2252 wjsi.last_update_date = sysdate
2253 where wjsi.group_id = p_groupID
2254 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2255 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2256 and wjsi.net_quantity is not null
2257 and wjsi.load_type in (wip_constants.create_job,
2258 wip_constants.create_ns_job,
2259 wip_constants.resched_job)
2260 and ( wjsi.net_quantity not between 0 and (select nvl(wjsi.start_quantity,wdj.start_quantity)
2261 from wip_discrete_jobs wdj
2262 where wdj.organization_id = wjsi.organization_id
2263 and wdj.wip_entity_id = wjsi.wip_entity_id) /*Fix for Bug 6522139*/
2264 or ( wjsi.net_quantity <> 0
2265 and ((wjsi.load_type = wip_constants.create_ns_job and wjsi.primary_item_id is null) or
2266 (wjsi.load_type = wip_constants.resched_job and
2267 wip_constants.nonstandard = (select wdj.job_type
2268 from wip_discrete_jobs wdj
2269 where wdj.organization_id = wjsi.organization_id
2270 and wdj.wip_entity_id = wjsi.wip_entity_id) and
2271 (select wdj.primary_item_id
2272 from wip_discrete_jobs wdj
2273 where wdj.organization_id = wjsi.organization_id
2274 and wdj.wip_entity_id = wjsi.wip_entity_id) is null))))
2275 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2276
2277 if ( sql%rowcount > 0 ) then
2278 fnd_message.set_name('WIP', 'WIP_ML_NET_QUANTITY');
2279 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2280 end if;
2281 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
2282
2283 --
2284 -- overcompletion_tolerance_type and overcompletion_tolerance_value
2285 --
2286 update wip_job_schedule_interface wjsi
2287 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2288 wjsi.last_update_date = sysdate
2289 where wjsi.group_id = p_groupID
2290 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2291 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2292 and wjsi.overcompletion_tolerance_type is not null
2293 and ( wjsi.load_type = wip_constants.create_sched
2294 or (wjsi.load_type = wip_constants.create_ns_job and
2295 wjsi.primary_item_id is null))
2296 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2297
2298 if ( sql%rowcount > 0 ) then
2299 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2300 fnd_message.set_token('COLUMN', 'OVERCOMPLETION_TOLERANCE_TYPE', false);
2301 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2302 end if;
2303
2304 update wip_job_schedule_interface wjsi
2305 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2306 wjsi.last_update_date = sysdate
2307 where wjsi.group_id = p_groupID
2308 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2309 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2310 and wjsi.overcompletion_tolerance_value is not null
2311 and ( wjsi.load_type = wip_constants.create_sched
2312 or (wjsi.load_type = wip_constants.create_ns_job and
2313 wjsi.primary_item_id is null))
2314 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2315
2316 if ( sql%rowcount > 0 ) then
2317 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2318 fnd_message.set_token('COLUMN', 'OVERCOMPLETION_TOLERANCE_VALUE', false);
2319 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2320 end if;
2321
2322 --
2323 -- default due_date
2324 --
2325 update wip_job_schedule_interface wjsi
2326 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2327 wjsi.last_update_date = sysdate
2328 where wjsi.group_id = p_groupID
2329 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2330 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2331 and wjsi.due_date is not null
2332 and wjsi.load_type = wip_constants.create_sched
2333 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2334
2335 if ( sql%rowcount > 0 ) then
2336 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2337 fnd_message.set_token('COLUMN', 'DUE_DATE', false);
2338 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2339 end if;
2340
2341 update wip_job_schedule_interface wjsi
2342 set wjsi.due_date = wjsi.last_unit_completion_date,
2343 wjsi.last_update_date = sysdate
2344 where wjsi.group_id = p_groupID
2345 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2346 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2347 and wjsi.due_date is null
2348 and wjsi.requested_start_date is null
2349 and wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job);
2350
2351 --
2352 -- validate date_released
2353 --
2354 update wip_job_schedule_interface wjsi
2355 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2356 wjsi.last_update_date = sysdate
2357 where wjsi.group_id = p_groupID
2358 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2359 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2360 and wjsi.date_released is not null
2361 and wjsi.status_type = wip_constants.unreleased
2362 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2363
2364 if ( sql%rowcount > 0 ) then
2365 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2366 fnd_message.set_token('COLUMN', 'DATE_RELEASED', false);
2367 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2368 end if;
2369
2370 update wip_job_schedule_interface wjsi
2371 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2372 wjsi.last_update_date = sysdate
2373 where wjsi.group_id = p_groupID
2374 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2375 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2376 and nvl(wjsi.date_released, sysdate) > sysdate
2377 and wjsi.status_type = wip_constants.released
2378 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2379
2380 if ( sql%rowcount > 0 ) then
2381 fnd_message.set_name('WIP', 'WIP_INVALID_RELEASE_DATE');
2382 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2383 -- set it to sysdate
2384 update wip_job_schedule_interface wjsi
2385 set wjsi.date_released = sysdate,
2386 wjsi.last_update_date = sysdate
2387 where wjsi.group_id = p_groupID
2388 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2389 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2390 and wjsi.date_released > sysdate
2391 and wjsi.status_type = wip_constants.released;
2392 end if;
2393
2394 update wip_job_schedule_interface wjsi
2395 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2396 wjsi.last_update_date = sysdate
2397 where wjsi.group_id = p_groupID
2398 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2399 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2400 and wjsi.status_type = wip_constants.released
2401 and exists (select 1
2402 from wip_discrete_jobs
2403 where wip_entity_id = wjsi.wip_entity_id
2404 and organization_Id = wjsi.organization_id
2405 and (status_type IS NULL OR
2406 status_type NOT IN (wip_constants.released,wip_constants.comp_chrg,
2407 wip_constants.cancelled, wip_constants.hold)))
2408 and not exists (select 1
2409 from org_acct_periods oap
2410 where oap.organization_id = wjsi.organization_id
2411 and trunc(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(wjsi.date_released,sysdate), wjsi.organization_id))
2412 between oap.period_start_date and oap.schedule_close_date
2413 and oap.period_close_date is null)
2414 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2415
2416 if ( sql%rowcount > 0 ) then
2417 fnd_message.set_name('WIP', 'WIP_NO_ACCT_PERIOD');
2418 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2419 end if;
2420
2421
2422 --
2423 -- requested_start_date
2424 --
2425 update wip_job_schedule_interface wjsi
2426 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2427 wjsi.last_update_date = sysdate
2428 where wjsi.group_id = p_groupID
2429 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2430 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2431 and wjsi.requested_start_date is not null
2432 and wjsi.load_type = wip_constants.create_sched
2433 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2434
2435 if ( sql%rowcount > 0 ) then
2436 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2437 fnd_message.set_token('COLUMN', 'REQUESTED_START_DATE', false);
2438 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2439 end if;
2440
2441 update wip_job_schedule_interface wjsi
2442 set wjsi.requested_start_date = wjsi.first_unit_start_date,
2443 wjsi.last_update_date = sysdate
2444 where wjsi.group_id = p_groupID
2445 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2446 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2447 and wjsi.due_date is null
2448 and wjsi.requested_start_date is null
2449 and wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job);
2450
2451 --
2452 -- header_id
2453 --
2454 update wip_job_schedule_interface wjsi
2455 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2456 wjsi.last_update_date = sysdate
2457 where wjsi.group_id = p_groupID
2458 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2459 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2460 and wjsi.header_id is not null
2461 and wjsi.load_type = wip_constants.create_sched
2462 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2463
2464 if ( sql%rowcount > 0 ) then
2465 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2466 fnd_message.set_token('COLUMN', 'HEADER_ID', false);
2467 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2468 end if;
2469
2470 --
2471 -- default and validate processing_work_days
2472 --
2473 update wip_job_schedule_interface wjsi
2474 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2475 wjsi.last_update_date = sysdate
2476 where wjsi.group_id = p_groupID
2477 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2478 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2479 and wjsi.processing_work_days is not null
2480 and wjsi.load_type <> wip_constants.create_sched
2481 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2482
2483 if ( sql%rowcount > 0 ) then
2484 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2485 fnd_message.set_token('COLUMN', 'PROCESSING_WORK_DAYS', false);
2486 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2487 end if;
2488
2489 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
2490 update wip_job_schedule_interface wjsi
2491 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2492 wjsi.last_update_date = sysdate
2493 where wjsi.group_id = p_groupID
2494 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2495 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2496 and wjsi.load_type = wip_constants.create_sched
2497 and (wjsi.processing_work_days <= 0 or wjsi.processing_work_days is null)
2498 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2499
2500 if ( sql%rowcount > 0 ) then
2501 fnd_message.set_name('WIP', 'WIP_ML_PROCESSING_WORK_DAYS');
2502 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2503 end if;
2504 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
2505
2506 --
2507 -- default and validate daily_production_rate
2508 --
2509 update wip_job_schedule_interface wjsi
2510 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2511 wjsi.last_update_date = sysdate
2512 where wjsi.group_id = p_groupID
2513 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2514 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2515 and wjsi.daily_production_rate is not null
2516 and wjsi.load_type <> wip_constants.create_sched
2517 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2518
2519 if ( sql%rowcount > 0 ) then
2520 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2521 fnd_message.set_token('COLUMN', 'DAILY_PRODUCTION_RATE', false);
2522 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2523 end if;
2524
2525 if(p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)) then
2526 update wip_job_schedule_interface wjsi
2527 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2528 wjsi.last_update_date = sysdate
2529 where wjsi.group_id = p_groupID
2530 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2531 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2532 and wjsi.load_type = wip_constants.create_sched
2533 and (wjsi.daily_production_rate <= 0 or wjsi.daily_production_rate is null)
2534 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2535
2536 if ( sql%rowcount > 0 ) then
2537 fnd_message.set_name('WIP', 'WIP_ML_PROCESSING_WORK_DAYS');
2538 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2539 end if;
2540 end if; /*p_validationLevel NOT IN (wip_constants.mrp, wip_constants.ato)*/
2541
2542 --
2543 -- default and validate demand_class
2544 --
2545 update wip_job_schedule_interface wjsi
2546 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2547 wjsi.last_update_date = sysdate
2548 where wjsi.group_id = p_groupID
2549 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2550 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2551 and wjsi.demand_class is not null
2552 and wjsi.load_type = wip_constants.resched_job
2553 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2554
2555 if ( sql%rowcount > 0 ) then
2556 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2557 fnd_message.set_token('COLUMN', 'DEMAND_CLASS', false);
2558 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2559 end if;
2560
2561 update wip_job_schedule_interface wjsi
2562 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2563 wjsi.last_update_date = sysdate
2564 where wjsi.group_id = p_groupID
2565 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2566 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2567 and wjsi.load_type in (wip_constants.create_sched,
2568 wip_constants.create_job,
2569 wip_constants.create_ns_job)
2570 and wjsi.demand_class is not null
2571 and not exists (select 1
2572 from so_demand_classes_active_v sdc
2573 where sdc.demand_class_code = wjsi.demand_class)
2574 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2575
2576 if ( sql%rowcount > 0 ) then
2577 fnd_message.set_name('WIP', 'WIP_ML_DEMAND_CLASS');
2578 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2579 end if;
2580
2581 --
2582 -- default and validate completion_subinventory
2583 --
2584 update wip_job_schedule_interface wjsi
2585 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2586 wjsi.last_update_date = sysdate
2587 where wjsi.group_id = p_groupID
2588 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2589 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2590 and wjsi.completion_subinventory is not null
2591 and wjsi.load_type in (wip_constants.resched_job,
2592 wip_constants.create_sched)
2593 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2594
2595 if ( sql%rowcount > 0 ) then
2596 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2597 fnd_message.set_token('COLUMN', 'COMPLETION_SUBINVENTORY', false);
2598 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2599 end if;
2600
2601 update wip_job_schedule_interface wjsi
2602 set wjsi.completion_subinventory =
2603 (select bor.completion_subinventory
2604 from bom_operational_routings bor
2605 where bor.organization_id = wjsi.organization_id
2606 and nvl(bor.cfm_routing_flag,2) = 2
2607 and bor.assembly_item_id = Decode(wjsi.load_type,
2608 wip_constants.create_job,
2609 wjsi.primary_item_id,
2610 wip_constants.create_ns_job,
2611 nvl(wjsi.routing_reference_id,wjsi.primary_item_id)) -- Bug 9765343 for ns job
2612 and nvl(bor.alternate_routing_designator,'@@@') =
2613 nvl(wjsi.alternate_routing_designator, '@@@')),
2614 wjsi.last_update_date = sysdate
2615 where wjsi.group_id = p_groupID
2616 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2617 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2618 and wjsi.completion_subinventory is null
2619 and wjsi.load_type IN (wip_constants.create_job,
2620 wip_constants.create_ns_job) ; -- Bug 9765343 for ns job
2621
2622 update wip_job_schedule_interface wjsi
2623 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2624 wjsi.last_update_date = sysdate
2625 where wjsi.group_id = p_groupID
2626 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2627 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2628 and wjsi.load_type = wip_constants.create_ns_job
2629 and wjsi.primary_item_id is null
2630 and wjsi.completion_subinventory is not null
2631 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2632
2633 if ( sql%rowcount > 0 ) then
2634 fnd_message.set_name('WIP', 'WIP_ML_COMPLETION_SUBINVENTORY');
2635 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2636 end if;
2637
2638 --
2639 -- Default completion_locator_id
2640 --
2641 update wip_job_schedule_interface wjsi
2642 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2643 wjsi.last_update_date = sysdate
2644 where wjsi.group_id = p_groupID
2645 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2646 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2647 and wjsi.completion_locator_id is not null
2648 and wjsi.load_type in (wip_constants.resched_job,
2649 wip_constants.create_sched)
2650 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2651
2652 if ( sql%rowcount > 0 ) then
2653 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2654 fnd_message.set_token('COLUMN', 'COMPLETION_LOCATOR_ID', false);
2655 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2656 end if;
2657
2658 update wip_job_schedule_interface wjsi
2659 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2660 wjsi.last_update_date = sysdate
2661 where wjsi.group_id = p_groupID
2662 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2663 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2664 and wjsi.completion_locator_id is not null
2665 and wjsi.completion_locator_segments is not null
2666 and wjsi.load_type in (wip_constants.create_job,
2667 wip_constants.create_ns_job)
2668 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2669
2670 if ( sql%rowcount > 0 ) then
2671 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2672 fnd_message.set_token('COLUMN', 'COMPLETION_LOCATOR_SEGMENTS', false);
2673 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2674 end if;
2675
2676 update wip_job_schedule_interface wjsi
2677 set wjsi.completion_locator_id =
2678 (select bor.completion_locator_id
2679 from bom_operational_routings bor
2680 where bor.organization_id = wjsi.organization_id
2681 and nvl(bor.cfm_routing_flag, 2) = 2
2682 and bor.assembly_item_id = Decode(wjsi.load_type,
2683 wip_constants.create_job,
2684 wjsi.primary_item_id,
2685 wip_constants.create_ns_job,
2686 nvl(wjsi.routing_reference_id,wjsi.primary_item_id)) -- Bug 9765343 for ns job
2687 and nvl(bor.alternate_routing_designator,'@@@') =
2688 nvl(wjsi.alternate_routing_designator, '@@@')
2689 and bor.completion_subinventory = wjsi.completion_subinventory),
2690 wjsi.last_update_date = sysdate
2691 where wjsi.group_id = p_groupID
2692 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2693 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2694 and wjsi.completion_locator_id is null
2695 and wjsi.completion_locator_segments is null
2696 and wjsi.load_type IN (wip_constants.create_job,
2697 wip_constants.create_ns_job) ; -- Bug 9765343 for ns job
2698
2699 update wip_job_schedule_interface wjsi
2700 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2701 wjsi.last_update_date = sysdate
2702 where wjsi.group_id = p_groupID
2703 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2704 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2705 and wjsi.load_type in (wip_constants.create_ns_job, wip_constants.create_job)
2706 and wjsi.completion_subinventory is null
2707 and wjsi.completion_locator_id is not null
2708 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2709
2710 if ( sql%rowcount > 0 ) then
2711 fnd_message.set_name('WIP', 'WIP_ML_COMPLETION_LOCATOR');
2712 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2713 end if;
2714
2715 --
2716 -- default and validate lot_number
2717 --
2718 update wip_job_schedule_interface wjsi
2719 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2720 wjsi.last_update_date = sysdate
2721 where wjsi.group_id = p_groupID
2722 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2723 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2724 and wjsi.lot_number is not null
2725 and wjsi.load_type = wip_constants.create_sched
2726 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2727
2728 if ( sql%rowcount > 0 ) then
2729 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2730 fnd_message.set_token('COLUMN', 'LOT_NUMBER', false);
2731 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2732 end if;
2733
2734 --
2735 -- default and validate source_code and source_line_id
2736 --
2737 update wip_job_schedule_interface wjsi
2738 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2739 wjsi.last_update_date = sysdate
2740 where wjsi.group_id = p_groupID
2741 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2742 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2743 and wjsi.source_code is not null
2744 and wjsi.load_type = wip_constants.create_sched
2745 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2746
2747 if ( sql%rowcount > 0 ) then
2748 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2749 fnd_message.set_token('COLUMN', 'SOURCE_CODE', false);
2750 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2751 end if;
2752
2753 update wip_job_schedule_interface wjsi
2754 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2755 wjsi.last_update_date = sysdate
2756 where wjsi.group_id = p_groupID
2757 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2758 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2759 and wjsi.source_line_id is not null
2760 and wjsi.load_type = wip_constants.create_sched
2761 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2762
2763 if ( sql%rowcount > 0 ) then
2764 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2765 fnd_message.set_token('COLUMN', 'SOURCE_LINE_ID', false);
2766 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2767 end if;
2768
2769 --
2770 -- default and validate scheduling_method
2771 --
2772 update wip_job_schedule_interface wjsi
2773 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2774 wjsi.last_update_date = sysdate
2775 where wjsi.group_id = p_groupID
2776 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2777 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2778 and wjsi.scheduling_method is not null
2779 and wjsi.load_type = wip_constants.create_sched
2780 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2781
2782 if ( sql%rowcount > 0 ) then
2783 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2784 fnd_message.set_token('COLUMN', 'SCHEDULING_METHOD', false);
2785 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2786 end if;
2787
2788 /*bug 7568044: setting scheduling_method to be manual for a non-standard discrete job in case when no routing reference is provided*/
2789 update wip_job_schedule_interface wjsi
2790 set wjsi.scheduling_method = wip_constants.ml_manual,
2791 wjsi.last_update_date = sysdate
2792 where wjsi.group_id = p_groupID
2793 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2794 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2795 and wjsi.scheduling_method is null
2796 and wjsi.load_type = WIP_CONSTANTS.CREATE_NS_JOB
2797 and wjsi.routing_reference_id is null;
2798
2799 update wip_job_schedule_interface wjsi
2800 set wjsi.scheduling_method = wip_constants.routing,
2801 wjsi.last_update_date = sysdate
2802 where wjsi.group_id = p_groupID
2803 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2804 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2805 and wjsi.scheduling_method is null
2806 and ( wjsi.allow_explosion is null
2807 or upper(wjsi.allow_explosion) <> 'N');
2808
2809 update wip_job_schedule_interface wjsi
2810 set wjsi.scheduling_method = wip_constants.ml_manual,
2811 wjsi.last_update_date = sysdate
2812 where wjsi.group_id = p_groupID
2813 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2814 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2815 and wjsi.scheduling_method is null
2816 and upper(wjsi.allow_explosion) = 'N';
2817
2818 update wip_job_schedule_interface wjsi
2819 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2820 wjsi.last_update_date = sysdate
2821 where wjsi.group_id = p_groupID
2822 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2823 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2824 and wjsi.load_type in (wip_constants.create_ns_job,
2825 wip_constants.create_job,
2826 wip_constants.resched_job)
2827 and wjsi.scheduling_method not in (wip_constants.routing,
2828 wip_constants.leadtime,
2829 wip_constants.ml_manual)
2830 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2831
2832 if ( sql%rowcount > 0 ) then
2833 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULING_METHOD');
2834 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2835 end if;
2836
2837 update wip_job_schedule_interface wjsi
2838 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2839 wjsi.last_update_date = sysdate
2840 where wjsi.group_id = p_groupID
2841 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2842 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2843 and ( (wjsi.scheduling_method = wip_constants.leadtime
2844 and ((wjsi.load_type = wip_constants.create_ns_job and
2845 wjsi.routing_reference_id is null) or
2846 (wjsi.load_type = wip_constants.resched_job and
2847 (select wdj.job_type
2848 from wip_discrete_jobs wdj
2849 where wdj.organization_id = wjsi.organization_id
2850 and wdj.wip_entity_id = wjsi.wip_entity_id) = wip_constants.nonstandard and
2851 (select wdj.routing_reference_id
2852 from wip_discrete_jobs wdj
2853 where wdj.organization_id = wjsi.organization_id
2854 and wdj.wip_entity_id = wjsi.wip_entity_id) is null)))
2855 or (wjsi.scheduling_method = wip_constants.ml_manual
2856 and (wjsi.first_unit_start_date is null or
2857 wjsi.last_unit_completion_date is null or
2858 wjsi.first_unit_start_date > wjsi.last_unit_completion_date)))
2859 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2860
2861 if ( sql%rowcount > 0 ) then
2862 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULING_METHOD');
2863 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2864 end if;
2865
2866 update wip_job_schedule_interface wjsi
2867 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2868 wjsi.last_update_date = sysdate
2869 where wjsi.group_id = p_groupID
2870 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2871 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2872 and wjsi.scheduling_method = wip_constants.routing
2873 and wjsi.allow_explosion in ('n', 'N')
2874 and wjsi.load_type = wip_constants.create_job
2875 and (wjsi.first_unit_start_date is null or
2876 wjsi.last_unit_completion_date is null)
2877 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2878
2879 if ( sql%rowcount > 0 ) then
2880 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULING_METHOD2');
2881 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
2882 end if;
2883
2884 --
2885 -- default and validate allow_explosion
2886 --
2887 update wip_job_schedule_interface wjsi
2888 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2889 wjsi.last_update_date = sysdate
2890 where wjsi.group_id = p_groupID
2891 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2892 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2893 and wjsi.allow_explosion is not null
2894 and wjsi.load_type = wip_constants.create_sched
2895 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2896
2897 if ( sql%rowcount > 0 ) then
2898 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2899 fnd_message.set_token('COLUMN', 'ALLOW_EXPLOSION', false);
2900 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2901 end if;
2902
2903 --
2904 -- default and validate allow_explosion
2905 --
2906 update wip_job_schedule_interface wjsi
2907 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2908 wjsi.last_update_date = sysdate
2909 where wjsi.group_id = p_groupID
2910 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2911 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2912 and wjsi.priority is not null
2913 and wjsi.load_type = wip_constants.create_sched
2914 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2915
2916 if ( sql%rowcount > 0 ) then
2917 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2918 fnd_message.set_token('COLUMN', 'PRIORITY', false);
2919 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2920 end if;
2921
2922 --
2923 -- default and validate end_item_unit_number
2924 --
2925 update wip_job_schedule_interface wjsi
2926 set wjsi.process_status = WIP_CONSTANTS.WARNING,
2927 wjsi.last_update_date = sysdate
2928 where wjsi.group_id = p_groupID
2929 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2930 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2931 and wjsi.end_item_unit_number is not null
2932 and wjsi.load_type in (wip_constants.create_sched,
2933 wip_constants.resched_job)
2934 returning wjsi.interface_id bulk collect into l_interfaceTbl;
2935
2936 if ( sql%rowcount > 0 ) then
2937 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
2938 fnd_message.set_token('COLUMN', 'END_ITEM_UNIT_NUMBER', false);
2939 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
2940 end if;
2941
2942 --
2943 -- validate schedule dates
2944 --
2945 if ( p_validationLevel not in (wip_constants.mrp, wip_constants.ato) ) then
2946 /* Modified For bug 5479283. To check against current value of routing reference id.*/
2947 update wip_job_schedule_interface wjsi
2948 set wjsi.process_status = WIP_CONSTANTS.ERROR,
2949 wjsi.last_update_date = sysdate
2950 where wjsi.group_id = p_groupID
2951 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
2952 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
2953 and ( (wjsi.load_type = wip_constants.create_ns_job and
2954 --must provide both dates when creating a ns job
2955 wjsi.routing_reference_id is null and
2956 (wjsi.first_unit_start_date is null or wjsi.last_unit_completion_date is null))
2957 or (wjsi.load_type = wip_constants.resched_job and
2958 --when rescheduling a ns job and providing one date, it must have a routing
2959 wip_constants.nonstandard = (select wdj.job_type
2960 from wip_discrete_jobs wdj
2961 where wdj.organization_id = wjsi.organization_id
2962 and wdj.wip_entity_id = wjsi.wip_entity_id) and
2963 /* (select wdj.routing_reference_id
2964 from wip_discrete_jobs wdj
2965 where wdj.organization_id = wjsi.organization_id
2966 and wdj.wip_entity_id = wjsi.wip_entity_id) */ wjsi.routing_reference_id is null and
2967 (select count(*)
2968 from wip_operations
2969 where organization_id = wjsi.organization_id
2970 and wip_entity_id = wjsi.wip_entity_id) = 0 and
2971 ((wjsi.first_unit_start_date is not null or wjsi.last_unit_completion_date is not null)
2972 /* Bug fix : 8407567 If scheduling method is manual, then both the dates are needed */
2973 and wjsi.scheduling_method <> wip_constants.ml_manual))
2974 or (wjsi.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
2975 --all job creations must have at least one date
2976 wjsi.first_unit_start_date is null and
2977 wjsi.last_unit_completion_date is null)
2978 or (wjsi.load_type = wip_constants.resched_job and
2979 --when changing the quantity, you must also provide a date
2980 wjsi.start_quantity is not null and
2981 wjsi.first_unit_start_date is null and
2982 wjsi.last_unit_completion_date is null)
2983 -- Commented this for Bug 13620858. Forms allow re-scheduling based on routing without re-explosion.
2984 /* or (wjsi.load_type = wip_constants.resched_job and
2985 --if not exploding, then the user must provide both dates or none at all
2986 wjsi.allow_explosion in ('N', 'n') and
2987 ((wjsi.first_unit_start_date is not null and wjsi.last_unit_completion_date is null) or
2988 (wjsi.first_unit_start_date is null and wjsi.last_unit_completion_date is not null))) */
2989 or (wjsi.first_unit_start_date is not null and
2990 not exists (select 1
2991 from bom_calendar_dates bcd,
2992 mtl_parameters mp
2993 where mp.organization_id = wjsi.organization_id
2994 and mp.calendar_code = bcd.calendar_code
2995 and mp.calendar_exception_set_id = bcd.exception_set_id
2996 and bcd.calendar_date = trunc(wjsi.first_unit_start_date)))
2997 or (wjsi.last_unit_completion_date is not null and
2998 not exists (select 1
2999 from bom_calendar_dates bcd,
3000 mtl_parameters mp
3001 where mp.organization_id = wjsi.organization_id
3002 and mp.calendar_code = bcd.calendar_code
3003 and mp.calendar_exception_set_id = bcd.exception_set_id
3004 and bcd.calendar_date = trunc(wjsi.last_unit_completion_date))))
3005 returning wjsi.interface_id bulk collect into l_interfaceTbl;
3006
3007
3008 if ( sql%rowcount > 0 ) then
3009 fnd_message.set_name('WIP', 'WIP_ML_SCHEDULE_DATES');
3010 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
3011 end if;
3012
3013 update wip_job_schedule_interface wjsi
3014 set wjsi.process_status = WIP_CONSTANTS.WARNING,
3015 wjsi.last_update_date = sysdate
3016 where wjsi.group_id = p_groupID
3017 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
3018 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
3019 and wjsi.last_unit_start_date is not null
3020 and wjsi.load_type <> wip_constants.create_sched
3021 returning wjsi.interface_id bulk collect into l_interfaceTbl;
3022
3023 if ( sql%rowcount > 0 ) then
3024 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
3025 fnd_message.set_token('COLUMN', 'LAST_UNIT_START_DATE', false);
3026 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
3027 end if;
3028
3029 update wip_job_schedule_interface wjsi
3030 set wjsi.process_status = WIP_CONSTANTS.WARNING,
3031 wjsi.last_update_date = sysdate
3032 where wjsi.group_id = p_groupID
3033 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
3034 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
3035 and wjsi.first_unit_completion_date is not null
3036 and wjsi.load_type <> wip_constants.create_sched
3037 returning wjsi.interface_id bulk collect into l_interfaceTbl;
3038
3039 if ( sql%rowcount > 0 ) then
3040 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
3041 fnd_message.set_token('COLUMN', 'FIRST_UNIT_COMPLETION_DATE', false);
3042 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationWarning);
3043 end if;
3044 end if;
3045
3046 --
3047 -- validate serialization_start_op
3048 --
3049 update wip_job_schedule_interface wjsi
3050 set wjsi.process_status = WIP_CONSTANTS.ERROR,
3051 wjsi.last_update_date = sysdate
3052 where wjsi.group_id = p_groupID
3053 and wjsi.process_phase = WIP_CONSTANTS.ML_VALIDATION
3054 and wjsi.process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING)
3055 and wjsi.serialization_start_op is not null
3056 and wjsi.load_type = wip_constants.create_sched
3057 returning wjsi.interface_id bulk collect into l_interfaceTbl;
3058
3059 if ( sql%rowcount > 0 ) then
3060 fnd_message.set_name('WIP', 'WIP_ML_SERIAL_START_OP');
3061 loadInterfaceError(l_interfaceTbl, fnd_message.get, validationError);
3062 end if;
3063
3064 if (l_logLevel <= wip_constants.trace_logging) then
3065 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
3066 p_procReturnStatus => x_returnStatus,
3067 p_msg => 'success',
3068 x_returnStatus => l_retStatus);
3069 end if;
3070
3071 exception
3072 when others then
3073 if(l_logLevel <= wip_constants.trace_logging) then
3074 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
3075 p_procReturnStatus => x_returnStatus,
3076 p_msg => 'unexp error:' || SQLERRM,
3077 x_returnStatus => l_retStatus);
3078 end if;
3079 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3080 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
3081 p_procedure_name => l_procName,
3082 p_error_text => SQLERRM);
3083 wip_utilities.get_message_stack(p_msg => l_msg);
3084 x_errorMsg := substrb(l_msg, 1, 240);
3085 end groupValidateMLHeader;
3086
3087
3088 procedure lineValidateMLHeader(p_groupID in number,
3089 p_validationLevel in number,
3090 x_returnStatus out nocopy varchar2,
3091 x_errorMsg out nocopy varchar2) is
3092 l_params wip_logger.param_tbl_t;
3093 l_procName varchar2(30) := 'lineValidateMLHeader';
3094 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
3095 l_retStatus varchar2(1);
3096 l_msg varchar2(2000);
3097 l_orginalOrgContext number;
3098 l_operatingUnit number;
3099
3100 cursor c_line is
3101 select rowid,
3102 interface_id
3103 from wip_job_schedule_interface
3104 where group_id = p_groupID
3105 and process_phase = WIP_CONSTANTS.ML_VALIDATION
3106 and process_status in (WIP_CONSTANTS.RUNNING, WIP_CONSTANTS.WARNING);
3107 begin
3108 x_returnStatus := fnd_api.g_ret_sts_success;
3109 if (l_logLevel <= wip_constants.trace_logging) then
3110 l_params(2).paramName := 'p_validationLevel';
3111 l_params(2).paramValue := p_validationLevel;
3112 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
3113 p_params => l_params,
3114 x_returnStatus => x_returnStatus);
3115 if(x_returnStatus <> fnd_api.g_ret_sts_success) then
3116 raise fnd_api.g_exc_unexpected_error;
3117 end if;
3118 end if;
3119
3120 for line in c_line loop
3121 begin
3122 setup(line.rowid);
3123
3124 if (l_logLevel <= wip_constants.trace_logging) then
3125 wip_logger.log('begin to validate record with interface_id: ' || wjsi_row.interface_id,
3126 l_retStatus);
3127 end if;
3128
3129 validateKanban(line.rowid, p_validationLevel, l_msg);
3130
3131 -- save the original org context
3132 l_orginalOrgContext := nvl(fnd_profile.value('ORG_ID'), -1);
3133
3134 -- set the org context for future PJM validation
3135 -- Bug 4890514. Performance Fix
3136 -- saugupta 25th-May-2006
3137 /*
3138 select ood.operating_unit
3139 into l_operatingUnit
3140 from wip_parameters wp,
3141 mtl_parameters mp,
3142 org_organization_definitions ood
3143 where wp.organization_id = mp.organization_id
3144 and wp.organization_id = ood.organization_id
3145 and wp.organization_id = wjsi_row.organization_id
3146 and sysdate < nvl(ood.disable_date, sysdate+1);
3147 fnd_client_info.set_org_context(to_char(l_operatingUnit));
3148 */
3149 SELECT
3150 decode(hoi.org_information_context, 'Accounting Information',
3151 to_number(hoi.org_information3), to_number(null)) operating_unit
3152 INTO l_operatingUnit
3153 FROM hr_organization_units hou,
3154 wip_parameters wp,
3155 mtl_parameters mp,
3156 hr_organization_information hoi
3157 WHERE hou.organization_id = hoi.organization_id
3158 and ( hoi.org_information_context || '') = 'Accounting Information'
3159 and wp.organization_id = mp.organization_id
3160 and wp.organization_id = hou.organization_id
3161 and wp.organization_id = wjsi_row.organization_id
3162 and sysdate < nvl(hou.date_to, sysdate+1);
3163
3164 validateProjectTask(line.rowid, l_msg);
3165 validateSubinvLocator(line.rowid, l_msg);
3166
3167 -- restore the original org context
3168 if ( l_orginalOrgContext <> -1 ) then
3169 fnd_client_info.set_org_context(to_char(l_orginalOrgContext));
3170 end if;
3171
3172 estimateLeadTime(line.rowid, l_msg);
3173 deriveScheduleDate(line.rowid, l_msg) ; /* 6117094 */
3174
3175 validateLotNumber(line.rowid, l_msg);
3176 validateClassCode(line.rowid, l_msg);
3177 validateBOMRevision(line.rowid, l_msg);
3178 validateRoutingRevision(line.rowid, l_msg);
3179 validateStartQuantity(line.rowid, l_msg);
3180 validateOvercompletion(line.rowid, l_msg);
3181 --Bug 5210075:Validate status type should be called always irrespective of
3182 --validation level
3183 validateStatusType(line.rowid, l_msg);
3184 if ( p_validationLevel not in (wip_constants.mrp, wip_constants.ato) ) then
3185 --Bug 5210075:Call to procedure validatestatustype is commented out.
3186 --validateStatusType(line.rowid, l_msg);
3187 validateBuildSequence(line.rowid, l_msg);
3188 validateEndItemUnitNumber(line.rowid, l_msg);
3189 validateDailyProductionRate(line.rowid, l_msg);
3190 validateRepScheduleDates(line.rowid, l_msg);
3191 end if;
3192
3193 exception
3194 when line_validation_error then
3195 if (l_logLevel <= wip_constants.trace_logging) then
3196 wip_logger.log('Validation Error happened on interface_id ' || line.interface_id || ': ' || l_msg,
3197 l_retStatus);
3198 end if;
3199 end;
3200 --Bug#13483213:bom_revision_Date and routing_revision_date are no longer truncated to the seconds. The actual timestamp is updated.
3201
3202 update wip_job_schedule_interface
3203 set project_id = wjsi_row.project_id,
3204 task_id = wjsi_row.task_id,
3205 status_type = wjsi_row.status_type,
3206 class_code = wjsi_row.class_code,
3207 overcompletion_tolerance_type = wjsi_row.overcompletion_tolerance_type,
3208 overcompletion_tolerance_value = wjsi_row.overcompletion_tolerance_value,
3209 first_unit_start_date = to_date(to_char(wjsi_row.first_unit_start_date,
3210 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3211 last_unit_start_date = to_date(to_char(wjsi_row.last_unit_start_date,
3212 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3213 first_unit_completion_date = to_date(to_char(wjsi_row.first_unit_completion_date,
3214 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3215 last_unit_completion_date = to_date(to_char(wjsi_row.last_unit_completion_date,
3216 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3217 build_sequence = wjsi_row.build_sequence,
3218 bom_revision = wjsi_row.bom_revision,
3219 routing_revision = wjsi_row.routing_revision,
3220 bom_revision_date = wjsi_row.bom_revision_date,
3221 routing_revision_date = wjsi_row.routing_revision_date,
3222 due_date = to_date(to_char(wjsi_row.due_date,
3223 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3224 requested_start_date = to_date(to_char(wjsi_row.requested_start_date,
3225 wip_constants.dt_nosec_fmt), wip_constants.dt_nosec_fmt),
3226 lot_number = wjsi_row.lot_number,
3227 completion_subinventory = wjsi_row.completion_subinventory,
3228 completion_locator_id = wjsi_row.completion_locator_id,
3229 scheduling_method = wjsi_row.scheduling_method,
3230 end_item_unit_number = wjsi_row.end_item_unit_number
3231 where rowid = line.rowid;
3232 end loop;
3233
3234
3235 if (l_logLevel <= wip_constants.trace_logging) then
3236 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
3237 p_procReturnStatus => x_returnStatus,
3238 p_msg => 'success',
3239 x_returnStatus => l_retStatus);
3240 end if;
3241
3242 exception
3243 when others then
3244 if(l_logLevel <= wip_constants.trace_logging) then
3245 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
3246 p_procReturnStatus => x_returnStatus,
3247 p_msg => 'unexp error:' || SQLERRM,
3248 x_returnStatus => l_retStatus);
3249 end if;
3250 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
3251 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkgName,
3252 p_procedure_name => l_procName,
3253 p_error_text => SQLERRM);
3254 wip_utilities.get_message_stack(p_msg => l_msg);
3255 x_errorMsg := substrb(l_msg, 1, 240);
3256 end lineValidateMLHeader;
3257
3258
3259 procedure setup(p_rowid in rowid) is
3260 l_primaryItemID number;
3261 begin
3262 select *
3263 into wjsi_row
3264 from wip_job_schedule_interface
3265 where rowid = p_rowid;
3266
3267 if ( wjsi_row.load_type = wip_constants.resched_job ) then
3268 select we.wip_entity_name,
3269 wdj.status_type,
3270 we.entity_type,
3271 wdj.job_type,
3272 wdj.start_quantity,
3273 wdj.quantity_completed,
3274 wdj.firm_planned_flag,
3275 wdj.primary_item_id,
3276 wdj.bom_reference_id,
3277 wdj.routing_reference_id,
3278 wdj.line_id,
3279 wdj.schedule_group_id,
3280 wdj.scheduled_completion_date,
3281 wdj.project_id,
3282 wdj.task_id,
3283 wdj.overcompletion_tolerance_type,
3284 wdj.overcompletion_tolerance_value,
3285 wdj.completion_subinventory,
3286 wdj.completion_locator_id,
3287 wdj.build_sequence,
3288 wdj.class_code
3289 into wdj_row.wip_entity_name,
3290 wdj_row.status_type,
3291 wdj_row.entity_type,
3292 wdj_row.job_type,
3293 wdj_row.start_quantity,
3294 wdj_row.quantity_completed,
3295 wdj_row.firm_planned_flag,
3296 wdj_row.primary_item_id,
3297 wdj_row.bom_reference_id,
3298 wdj_row.routing_reference_id,
3299 wdj_row.line_id,
3300 wdj_row.schedule_group_id,
3301 wdj_row.scheduled_completion_date,
3302 wdj_row.project_id,
3303 wdj_row.task_id,
3304 wdj_row.overcompletion_tolerance_type,
3305 wdj_row.overcompletion_tolerance_value,
3306 wdj_row.completion_subinventory,
3307 wdj_row.completion_locator_id,
3308 wdj_row.build_sequence,
3309 wdj_row.class_code
3310 from wip_discrete_jobs wdj,
3311 wip_entities we
3312 where wdj.wip_entity_id = wjsi_row.wip_entity_id
3313 and we.wip_entity_id = wdj.wip_entity_id;
3314
3315 l_primaryItemID := wdj_row.primary_item_id;
3316 else
3317 l_primaryItemID := wjsi_row.primary_item_id;
3318 end if;
3319
3320 if ( l_primaryItemID is not null ) then
3321 select inventory_item_id,
3322 pick_components_flag,
3323 build_in_wip_flag,
3324 eng_item_flag,
3325 inventory_asset_flag,
3326 restrict_subinventories_code,
3327 restrict_locators_code,
3328 location_control_code,
3329 fixed_lead_time,
3330 variable_lead_time
3331 into primary_item_row.inventory_item_id,
3332 primary_item_row.pick_components_flag,
3333 primary_item_row.build_in_wip_flag,
3334 primary_item_row.eng_item_flag,
3335 primary_item_row.inventory_asset_flag,
3336 primary_item_row.restrict_subinventories_code,
3337 primary_item_row.restrict_locators_code,
3338 primary_item_row.location_control_code,
3339 primary_item_row.fixed_lead_time,
3340 primary_item_row.variable_lead_time
3341 from mtl_system_items
3342 where inventory_item_id = l_primaryItemID
3343 and organization_id = wjsi_row.organization_id;
3344 end if;
3345 end setup;
3346
3347
3348 procedure estimateLeadTime(p_rowid in rowid,
3349 x_errorMsg out nocopy varchar2) is
3350 l_schedDir number;
3351 l_rtgCount number;
3352 l_qty number;
3353 l_msg varchar2(30);
3354 begin
3355 if ( wjsi_row.load_type = wip_constants.create_sched ) then
3356 if(wjsi_row.first_unit_start_date is null) then
3357 if(wjsi_row.last_unit_start_date is not null) then
3358 l_schedDir := wip_constants.lusd;
3359 elsif(wjsi_row.first_unit_completion_date is not null) then
3360 l_schedDir := wip_constants.fucd;
3361 else
3362 l_schedDir := wip_constants.lucd;
3363 end if;
3364
3365 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
3366 x_fixed_lead => primary_item_row.fixed_lead_time,
3367 x_var_lead => primary_item_row.variable_lead_time,
3368 x_quantity => wjsi_row.processing_work_days * wjsi_row.daily_production_rate,
3369 x_proc_days => wjsi_row.processing_work_days,
3370 x_entity_type => wip_constants.repetitive,
3371 x_fusd => wjsi_row.first_unit_start_date,
3372 x_fucd => wjsi_row.first_unit_completion_date,
3373 x_lusd => wjsi_row.last_unit_start_date,
3374 x_lucd => wjsi_row.last_unit_completion_date,
3375 x_sched_dir => l_schedDir,
3376 x_est_date => wjsi_row.first_unit_start_date);
3377 if(wjsi_row.first_unit_start_date is null) then
3378 l_msg := 'WIP_ML_EST_LEADTIME';
3379 raise fnd_api.g_exc_unexpected_error;
3380 end if;
3381 end if;
3382 else
3383 if(wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is not null) then
3384 select count(*)
3385 into l_rtgCount
3386 from bom_operational_routings
3387 where assembly_item_id = wjsi_row.routing_reference_id
3388 and organization_id = wjsi_row.organization_id
3389 and nvl(alternate_routing_designator, '@@@') =
3390 nvl(wjsi_row.alternate_routing_designator, '@@@')
3391 and nvl(cfm_routing_flag, 2) = 2;
3392 l_qty := wjsi_row.start_quantity;
3393 elsif(wjsi_row.load_type = wip_constants.create_job) then
3394 select count(*)
3395 into l_rtgCount
3396 from bom_operational_routings
3397 where assembly_item_id = wjsi_row.primary_item_id
3398 and organization_id = wjsi_row.organization_id
3399 and nvl(alternate_routing_designator, '@@@') =
3400 nvl(wjsi_row.alternate_routing_designator, '@@@')
3401 and nvl(cfm_routing_flag, 2) = 2;
3402 l_qty := wjsi_row.start_quantity;
3403 elsif(wjsi_row.load_type = wip_constants.resched_job) then
3404
3405 /* Modified for Bug 9286094. Modified logic to default scheduling method. */
3406 if ( nvl(wjsi_row.allow_explosion, 'Y') in ('n', 'N') ) then
3407 select count(*)
3408 into l_rtgCount
3409 from wip_operations
3410 where wip_entity_id = wjsi_row.wip_entity_id;
3411 else
3412 select count(*)
3413 into l_rtgCount
3414 from bom_operational_routings
3415 where organization_id = wjsi_row.organization_id
3416 and assembly_item_id = nvl(wjsi_row.routing_reference_id,wjsi_row.primary_item_id)
3417 and nvl(alternate_routing_designator, '@@@^@@@') = nvl(wjsi_row.alternate_routing_designator, '@@@^@@@')
3418 and nvl(cfm_routing_flag, 2) = 2;
3419 end if;
3420 l_qty := nvl(wjsi_row.start_quantity, wdj_row.start_quantity);
3421 end if;
3422 --if no routing exists, update the scheduling method appropriately
3423 if(wjsi_row.scheduling_method = wip_constants.routing and l_rtgCount = 0) then
3424 if(wjsi_row.first_unit_start_date is not null and
3425 wjsi_row.last_unit_completion_date is not null) then
3426 wjsi_row.scheduling_method := wip_constants.ml_manual;
3427 else
3428 wjsi_row.scheduling_method := wip_constants.leadtime;
3429 end if;
3430 end if;
3431 if(wjsi_row.first_unit_start_date is null and
3432 wjsi_row.last_unit_completion_date is not null and
3433 wjsi_row.scheduling_method = wip_constants.leadtime) then
3434 /* Estimate Start Date */
3435 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
3436 x_fixed_lead => primary_item_row.fixed_lead_time,
3437 x_var_lead => primary_item_row.variable_lead_time,
3438 x_quantity => l_qty,
3439 x_proc_days => 0,
3440 x_entity_type => wip_constants.discrete,
3441 x_fusd => '',
3442 x_fucd => '',
3443 x_lusd => '',
3444 x_lucd => wjsi_row.last_unit_completion_date,
3445 x_sched_dir => wip_constants.lucd,
3446 x_est_date => wjsi_row.first_unit_start_date);
3447 if(wjsi_row.first_unit_start_date is null) then
3448 l_msg := 'WIP_ML_EST_LEADTIME';
3449 raise fnd_api.g_exc_unexpected_error;
3450 end if;
3451 elsif(wjsi_row.last_unit_completion_date is null and
3452 wjsi_row.first_unit_start_date is not null and
3453 wjsi_row.scheduling_method = wip_constants.leadtime) then
3454 /* Estimate Completion Date */
3455 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
3456 x_fixed_lead => primary_item_row.fixed_lead_time,
3457 x_var_lead => primary_item_row.variable_lead_time,
3458 x_quantity => l_qty,
3459 x_proc_days => 0,
3460 x_entity_type => wip_constants.discrete,
3461 x_fusd => wjsi_row.first_unit_start_date,
3462 x_fucd => '',
3463 x_lusd => '',
3464 x_lucd => '',
3465 x_sched_dir => wip_constants.fusd,
3466 x_est_date => wjsi_row.last_unit_completion_date);
3467 if(wjsi_row.last_unit_completion_date is null) then
3468 l_msg := 'WIP_NO_CALENDAR';
3469 raise fnd_api.g_exc_unexpected_error;
3470 end if;
3471 -- Added for Bug 9385806.
3472 elsif (wjsi_row.last_unit_completion_date is null and
3473 wjsi_row.first_unit_start_date is null and
3474 wjsi_row.scheduling_method = wip_constants.leadtime) then
3475 if wjsi_row.load_type = wip_constants.resched_job then
3476
3477 select wdj.scheduled_start_date into wjsi_row.first_unit_start_date
3478 from wip_discrete_jobs wdj
3479 where wdj.wip_entity_id = wjsi_row.wip_entity_id;
3480
3481 /* Estimate Completion Date */
3482 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
3483 x_fixed_lead => primary_item_row.fixed_lead_time,
3484 x_var_lead => primary_item_row.variable_lead_time,
3485 x_quantity => l_qty,
3486 x_proc_days => 0,
3487 x_entity_type => wip_constants.discrete,
3488 x_fusd => wjsi_row.first_unit_start_date,
3489 x_fucd => '',
3490 x_lusd => '',
3491 x_lucd => '',
3492 x_sched_dir => wip_constants.fusd,
3493 x_est_date => wjsi_row.last_unit_completion_date);
3494 if(wjsi_row.last_unit_completion_date is null) then
3495 l_msg := 'WIP_NO_CALENDAR';
3496 raise fnd_api.g_exc_unexpected_error;
3497 end if;
3498 else
3499 l_msg := 'WIP_ML_SCHEDULE_DATES';
3500 raise fnd_api.g_exc_unexpected_error;
3501 end if;
3502 end if;
3503 end if;
3504 exception
3505 when others then
3506 fnd_message.set_name('WIP', l_msg);
3507 x_errorMsg := fnd_message.get;
3508 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3509 raise line_validation_error;
3510 end estimateLeadTime;
3511
3512
3513 procedure validateProjectTask(p_rowid in rowid,
3514 x_errorMsg out nocopy varchar2) is
3515 l_dummy number;
3516 l_projectID number;
3517 l_errCode varchar2(80);
3518 l_result varchar2(1);
3519 l_ou_context number;
3520 begin
3521 -- fix MOAC, set id so project view works
3522 fnd_profile.put('MFG_ORGANIZATION_ID',wjsi_row.organization_id);
3523
3524 -- Set Additional OU context when Flow is from AFAS as required by Projects in Bug 12674069.
3525 SELECT TO_NUMBER(org_information3) into l_ou_context
3526 FROM hr_organization_information
3527 WHERE org_information_context = 'Accounting Information'
3528 AND organization_id = wjsi_row.organization_id;
3529
3530 MO_GLOBAL.set_policy_context(p_access_mode => 'S',p_org_id => l_ou_context);
3531
3532 if ( wjsi_row.load_type <> wip_constants.create_sched ) then
3533 begin
3534 if ( wjsi_row.project_number is not null and wjsi_row.project_id is null) then
3535 -- Bug 4890514. Performance Fix
3536 -- saugupta 25th-May-2006
3537 select mpv.project_id --if the project has tasks, this query returns multiple rows
3538 into wjsi_row.project_id
3539 from pjm_projects_v mpv,
3540 pjm_project_parameters ppp
3541 where mpv.project_number = wjsi_row.project_number
3542 and mpv.project_id = ppp.project_id
3543 and ppp.organization_id = wjsi_row.organization_id;
3544 end if;
3545 exception
3546 when others then
3547 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
3548 fnd_message.set_token('COLUMN', 'PROJECT_NUMBER', false);
3549 x_errorMsg := fnd_message.get;
3550 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3551 raise line_validation_error;
3552 end;
3553 end if;
3554
3555 if ( wjsi_row.load_type <> wip_constants.create_sched and
3556 wjsi_row.project_id is not null) then
3557 begin
3558 -- Bug 4890514. Performance Fix
3559 -- saugupta 25th-May-2006
3560 select mpv.project_id --this query will return multiple rows if the project has tasks
3561 into l_dummy
3562 from pjm_projects_v mpv,
3563 pjm_project_parameters ppp,
3564 mtl_parameters mp
3565 where mpv.project_id = ppp.project_id
3566 and mpv.project_id = wjsi_row.project_id
3567 and ppp.organization_id = wjsi_row.organization_id
3568 and ppp.organization_id = mp.organization_id
3569 and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
3570 exception
3571 when others then
3572 fnd_message.set_name('WIP', 'WIP_ML_PROJECT_ID');
3573 x_errorMsg := fnd_message.get;
3574 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3575 raise line_validation_error;
3576 end;
3577 end if;
3578
3579 if ( wjsi_row.load_type <> wip_constants.create_sched and
3580 wjsi_row.task_number is not null and wjsi_row.task_id is null) then
3581 begin
3582 if ( wjsi_row.load_type = wip_constants.resched_job ) then
3583 select pa.task_id
3584 into wjsi_row.task_id
3585 from pa_tasks_expend_v pa,
3586 wip_discrete_jobs wdj
3587 where wdj.wip_entity_id = wjsi_row.wip_entity_id
3588 and pa.project_id = nvl(wjsi_row.project_id, wdj.project_id)
3589 and pa.task_number = wjsi_row.task_number;
3590 else
3591 select task_id
3592 into wjsi_row.task_id
3593 from pa_tasks_expend_v
3594 where project_id = wjsi_row.project_id
3595 and task_number = wjsi_row.task_number;
3596 end if;
3597 exception
3598 when others then
3599 fnd_message.set_name('WIP', 'WIP_ML_FIELD_INVALID');
3600 fnd_message.set_token('COLUMN', 'TASK_NUMBER', false);
3601 x_errorMsg := fnd_message.get;
3602 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3603 raise line_validation_error;
3604 end;
3605 end if;
3606
3607 if ( wjsi_row.task_id is not null ) then
3608 if ( wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) ) then
3609 l_projectID := wjsi_row.project_id;
3610 else
3611 l_projectID := nvl(wjsi_row.project_id, wdj_row.project_id);
3612 end if;
3613 begin
3614
3615 if (PJM_PROJECT.val_task_idtonum(l_projectID, wjsi_row.task_id)
3616 is null) then
3617 raise fnd_api.g_exc_unexpected_error;
3618 end if;
3619
3620 exception
3621 when others then
3622 fnd_message.set_name('WIP', 'WIP_ML_TASK_ID');
3623 x_errorMsg := fnd_message.get;
3624 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3625 raise line_validation_error;
3626 end;
3627 end if;
3628
3629 if ( wjsi_row.load_type = wip_constants.create_job and
3630 wjsi_row.project_id is not null ) then
3631 l_result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
3632 (x_inventory_org_id => wjsi_row.organization_id,
3633 x_project_id => wjsi_row.project_id,
3634 x_task_id => wjsi_row.task_id,
3635 x_date1 => wjsi_row.first_unit_start_date,
3636 x_date2 => wjsi_row.last_unit_completion_date,
3637 x_calling_function => 'WILMLX',
3638 x_error_code => l_errCode
3639 );
3640 if ( l_result <> PJM_PROJECT.G_VALIDATE_SUCCESS ) then
3641 wip_utilities.get_message_stack(p_delete_stack => 'T',
3642 p_msg => x_errorMsg);
3643 if ( l_result = PJM_PROJECT.G_VALIDATE_FAILURE ) then
3644 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3645 raise line_validation_error;
3646 else
3647 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationWarning);
3648 end if;
3649 end if;
3650 end if;
3651 end validateProjectTask;
3652
3653
3654 procedure validateClassCode(p_rowid in rowid,
3655 x_errorMsg out nocopy varchar2) is
3656 l_dummy NUMBER;
3657 l_errMsg1 VARCHAR2(30);
3658 l_errMsg2 VARCHAR2(30);
3659 l_errClass1 VARCHAR2(30);
3660 l_errClass2 VARCHAR2(30);
3661 l_checkClassFlag VARCHAR2(1) := 'N'; --fix for bug 16079296
3662 begin
3663
3664 /* fix for bug 16079296;removed the wip_constants.resched_job from load_type in list as this should be allowed */
3665
3666 if ( wjsi_row.class_code is not null ) then
3667 if (wjsi_row.load_type = wip_constants.resched_job) and (wjsi_row.class_code <> wdj_row.class_code) then
3668 l_checkClassFlag := 'Y';
3669 elsif wjsi_row.load_type = wip_constants.create_sched then
3670 fnd_message.set_name('WIP', 'WIP_ML_FIELD_IGNORED');
3671 fnd_message.set_token('COLUMN', 'CLASS_CODE', false);
3672 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
3673 end if;
3674 end if;
3675
3676 -- default the class code
3677 if ( wjsi_row.class_code is null ) then
3678 if ( wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) ) then
3679 wjsi_row.class_code := wip_common.default_acc_class(
3680 x_org_id => wjsi_row.organization_id,
3681 x_item_id => wjsi_row.primary_item_id,
3682 x_entity_type => wip_constants.discrete,
3683 x_project_id => wjsi_row.project_id,
3684 x_err_mesg_1 => l_errMsg1,
3685 x_err_mesg_2 => l_errMsg2,
3686 x_err_class_1 => l_errClass1,
3687 x_err_class_2 => l_errClass2);
3688 if ( l_errMsg1 is not null) then
3689 fnd_message.set_name('WIP', l_errMsg1);
3690 fnd_message.set_token('class_code', l_errClass1, false);
3691 x_errorMsg := fnd_message.get;
3692 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3693 raise line_validation_error;
3694 end if ;
3695 if (l_errMsg2 is not null) then
3696 fnd_message.set_name('WIP', l_errMsg2);
3697 fnd_message.set_token('class_code', l_errClass2, false);
3698 x_errorMsg := fnd_message.get;
3699 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3700 raise line_validation_error;
3701 end if ;
3702 elsif ( wjsi_row.load_type = wip_constants.resched_job ) then
3703 select wdj.class_code
3704 into wjsi_row.class_code
3705 from wip_discrete_jobs wdj
3706 where wdj.wip_entity_id = wjsi_row.wip_entity_id
3707 and wdj.organization_id = wjsi_row.organization_id;
3708 end if;
3709 end if;
3710
3711 begin
3712 if ( wjsi_row.load_type = wip_constants.create_job or l_checkClassFlag = 'Y' ) then
3713 if(wjsi_row.project_id is null) then
3714 -- Bug 12741768
3715 -- Since the projectid is null, this will check if the default cost group of the organization has any
3716 -- accounting classes assigned.
3717 -- The accounting class retrieved earlier should be :
3718 -- a) Discrete accounting class for that organization
3719 -- b) If accounting classes are assigned to the default cost group, then the accounting class should be part of the assigned accounting classes.
3720
3721
3722 select 1 into l_dummy
3723 from
3724 ( SELECT DISTINCT ccwac.class_code
3725 from cst_cg_wip_acct_classes_v ccwac,mtl_parameters mp
3726 where ccwac.class_code = wjsi_row.class_code
3727 and ccwac.organization_id = wjsi_row.organization_id
3728 and ccwac.class_type = wip_constants.discrete
3729 AND ccwac.organization_id=mp.organization_id
3730 AND ( not exists
3731 (SELECT 1
3732 FROM cst_cg_wip_acct_classes_v ccwac2 ,mtl_parameters mp
3733 WHERE mp.organization_id=wjsi_row.organization_id
3734 AND ccwac2.cost_group_id = mp.default_cost_group_id
3735 AND ccwac2.organization_id = mp.organization_id
3736 AND ccwac2.class_type=wip_constants.discrete
3737 )
3738 OR ( ccwac.cost_group_id = mp.default_cost_group_id )));
3739
3740 else
3741 select 1 into l_dummy
3742 from dual
3743 where exists(select 1
3744 from cst_cg_wip_acct_classes_v ccwac,
3745 mtl_parameters mp
3746 where ccwac.class_code = wjsi_row.class_code
3747 and ccwac.organization_id = wjsi_row.organization_id
3748 and ccwac.class_type = wip_constants.discrete
3749 and mp.organization_id = wjsi_row.organization_id
3750 and ( mp.primary_cost_method = wip_constants.cost_std
3751 or ccwac.cost_group_id = (select costing_group_id
3752 from mrp_project_parameters mpp
3753 where organization_id = wjsi_row.organization_id
3754 and mpp.project_id = wjsi_row.project_id)));
3755 end if;
3756 elsif ( wjsi_row.load_type = wip_constants.create_ns_job ) then
3757 select 1 into l_dummy
3758 from dual
3759 where exists(select 1
3760 from wip_non_standard_classes_val_v
3761 where class_code = wjsi_row.class_code
3762 and organization_id = wjsi_row.organization_id);
3763 end if;
3764 exception
3765 when others then
3766 fnd_message.set_name('WIP', 'WIP_ML_CLASS_CODE');
3767 x_errorMsg := fnd_message.get;
3768 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3769 raise line_validation_error;
3770 end;
3771 end validateClassCode;
3772
3773 procedure validateBOMRevision(p_rowid in rowid,
3774 x_errorMsg out nocopy varchar2) is
3775 l_bomItemID number;
3776 begin
3777 if(wjsi_row.load_type = wip_constants.create_ns_job) then
3778 l_bomItemID := wjsi_row.bom_reference_id;
3779 else
3780 l_bomItemID := wjsi_row.primary_item_id;
3781 end if;
3782
3783 if(wjsi_row.load_type in (wip_constants.create_job,
3784 wip_constants.create_sched,
3785 wip_constants.create_ns_job) ) then
3786 --Bug 5464449: Default revision date from wdj
3787 IF wjsi_row.load_type = wip_constants.RESCHED_JOB and
3788 wjsi_row.wip_entity_id is not null and
3789 wjsi_row.bom_revision_date IS NULL THEN
3790 BEGIN
3791 select bom_revision_date
3792 into wjsi_row.bom_revision_date
3793 from wip_discrete_jobs
3794 where wip_entity_id = wjsi_row.wip_entity_id;
3795 EXCEPTION
3796 WHEN OTHERS THEN
3797 wjsi_row.bom_revision_date := NULL;
3798 END;
3799 END IF;
3800 --Bug 5464449: End of changes.
3801 wip_revisions.bom_revision(
3802 p_organization_id => wjsi_row.organization_id,
3803 p_item_id => l_bomItemID,
3804 p_revision => wjsi_row.bom_revision,
3805 p_revision_date => wjsi_row.bom_revision_date,
3806 p_start_date => greatest(nvl(wjsi_row.first_unit_start_date, wjsi_row.last_unit_completion_date), sysdate));
3807 end if;
3808 exception
3809 when others then
3810 fnd_message.set_name('WIP', 'WIP_ML_BOM_REVISION');
3811 x_errorMsg := fnd_message.get;
3812 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3813 raise line_validation_error;
3814 end validateBOMRevision;
3815
3816
3817 procedure validateRoutingRevision(p_rowid in rowid,
3818 x_errorMsg out nocopy varchar2) is
3819 l_rtgItemID number;
3820 l_count number;
3821 l_ret_status varchar2(20);
3822
3823 begin
3824 if(wjsi_row.load_type = wip_constants.create_ns_job) then
3825 l_rtgItemID := wjsi_row.routing_reference_id;
3826 else
3827 l_rtgItemID := wjsi_row.primary_item_id;
3828 end if;
3829
3830 if(wjsi_row.load_type in (wip_constants.create_job,
3831 wip_constants.create_sched,
3832 wip_constants.create_ns_job,
3833 wip_constants.RESCHED_JOB --5194524
3834 )
3835 ) then
3836
3837 wip_logger.log('ENTERED into Validate routing revision',l_ret_status);
3838
3839 select count(*)
3840 into l_count
3841 from bom_operational_routings
3842 where assembly_item_id = decode(wjsi_row.load_type,
3843 wip_constants.create_ns_job, wjsi_row.routing_reference_id,
3844 wjsi_row.primary_item_id
3845 )
3846 and organization_id = wjsi_row.organization_id
3847 and nvl(alternate_routing_designator, '@@@') =
3848 nvl(wjsi_row.alternate_routing_designator, '@@@');
3849 --Bug 5464449: Default revision date from wdj
3850 IF wjsi_row.load_type = wip_constants.RESCHED_JOB and
3851 wjsi_row.wip_entity_id is not null and
3852 wjsi_row.routing_revision_date IS NULL THEN
3853 BEGIN
3854 select routing_revision_date
3855 into wjsi_row.routing_revision_date
3856 from wip_discrete_jobs
3857 where wip_entity_id = wjsi_row.wip_entity_id;
3858 EXCEPTION
3859 WHEN OTHERS THEN
3860 wjsi_row.routing_revision_date := NULL;
3861 END;
3862 END IF;
3863 --Bug 5464449:End of changes.
3864 if(l_count > 0) then
3865 wip_logger.log('calling wip_revisions.routing_revision',l_ret_status);
3866 --l_start_date := greatest(nvl(wjsi_row.first_unit_start_date, wjsi_row.last_unit_completion_date), sysdate);
3867
3868 wip_revisions.routing_revision(p_organization_id => wjsi_row.organization_id,
3869 p_item_id => l_rtgItemID,
3870 p_revision => wjsi_row.routing_revision,
3871 p_revision_date => wjsi_row.routing_revision_date,
3872 --bugifx 5364387 added outer nvl
3873 p_start_date => nvl(greatest(nvl(wjsi_row.first_unit_start_date, wjsi_row.last_unit_completion_date), sysdate),sysdate)
3874
3875 );
3876 end if;
3877 end if;
3878 exception
3879 when others then
3880 fnd_message.set_name('WIP', 'WIP_ML_ROUTING_REVISION');
3881 x_errorMsg := fnd_message.get;
3882 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3883 raise line_validation_error;
3884 end validateRoutingRevision;
3885
3886
3887 procedure validateStartQuantity(p_rowid in rowid,
3888 x_errorMsg out nocopy varchar2) is
3889 l_minOp number;
3890 l_queueQty number;
3891 l_scheduledQty number;
3892 begin
3893
3894 if ( wjsi_row.load_type = wip_constants.resched_job and
3895 wjsi_row.start_quantity <> wdj_row.start_quantity ) then
3896
3897 if ( wjsi_row.start_quantity < wdj_row.quantity_completed ) then
3898 raise fnd_api.g_exc_unexpected_error;
3899 end if;
3900 -- Fix for 5999220 Added if condition by mraman
3901 if ( wdj_row.status_type <> wip_constants.unreleased ) then
3902 select nvl(min(operation_seq_num), fnd_api.g_miss_num)
3903 into l_minOp
3904 from wip_operations
3905 where organization_id = wjsi_row.organization_id
3906 and wip_entity_id = wjsi_row.wip_entity_id
3907 and nvl(repetitive_schedule_id, -1) = nvl(wjsi_row.repetitive_schedule_id, -1);
3908
3909 if ( l_minOp <> fnd_api.g_miss_num ) then
3910 select quantity_in_queue,
3911 scheduled_quantity
3912 into l_queueQty,
3913 l_scheduledQty
3914 from wip_operations
3915 where organization_id = wjsi_row.organization_id
3916 and wip_entity_id = wjsi_row.wip_entity_id
3917 and nvl(repetitive_schedule_id, -1) = nvl(wjsi_row.repetitive_schedule_id, -1)
3918 and operation_seq_num = l_minOp;
3919
3920 if ( l_queueQty < l_scheduledQty - wjsi_row.start_quantity ) then
3921 raise fnd_api.g_exc_unexpected_error;
3922 end if; -- end if (l_queueQty < l_scheduledQty - wjsi_row.start_quantity )
3923 end if ; -- end if (l_minOp <> fnd_api.g_miss_num )
3924 end if; -- end if ( wdj_row.status_type <> wip_constants.unreleased )
3925
3926 /* bug 5350660. Show warning if job is already pick released */
3927 if ( wip_picking_pub.is_job_pick_released(wjsi_row.wip_entity_id,
3928 wjsi_row.repetitive_schedule_id,
3929 wjsi_row.organization_id)) then
3930 fnd_message.set_name('WIP', 'WIP_QTY_REQ_CHANGE_WARNING');
3931 setInterfaceError(p_rowid, wjsi_row.interface_id, substr(fnd_message.get, 1, 500), validationWarning);
3932 end if;
3933 end if;
3934 exception
3935 when others then
3936 fnd_message.set_name('WIP', 'WIP_LOWER_JOB_QTY');
3937 x_errorMsg := fnd_message.get;
3938 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
3939 raise line_validation_error;
3940 end validateStartQuantity;
3941
3942
3943 procedure validateStatusType(p_rowid in rowid,
3944 x_errorMsg out nocopy varchar2) is
3945 l_qtyReserved number;
3946 l_propagate_job_change_to_po number;
3947 l_msg varchar2(30);
3948 l_retStatus varchar2(1);
3949 l_old_status number := 0 ; /* Fix for Bug#4406036 */
3950
3951 cursor check_so_link is
3952 select nvl(sum(wr.primary_quantity), 0)
3953 from wip_reservations_v wr
3954 where wr.wip_entity_id = wjsi_row.wip_entity_id
3955 and wr.inventory_item_id = nvl(wjsi_row.primary_item_id, wdj_row.primary_item_id)
3956 and wr.organization_id = wjsi_row.organization_id;
3957 begin
3958 if ( wjsi_row.load_type <> wip_constants.resched_job ) then
3959 return;
3960 end if;
3961
3962 /* Fix for Bug#4406036. Added following sql statement to get existing
3963 status of job. Warning is applicable only if current status of job
3964 is anything other than complete or cancelled
3965 */
3966 begin
3967 select status_type
3968 into l_old_status
3969 from wip_discrete_jobs
3970 where wip_entity_id = wjsi_row.wip_entity_id ;
3971 end ;
3972 /* Bug 13075675 -> Give warning when job linked to SO is being completed,
3973 * and give error when job linked to SO is being cancelled or
3974 * complete-no charge.
3975 */
3976 /* if ( wjsi_row.status_type in (wip_constants.cancelled,
3977 wip_constants.comp_chrg)
3978 and (l_old_status not in (wip_constants.cancelled, wip_constants.comp_chrg,
3979 wip_constants.comp_nochrg))) then
3980 open check_so_link;
3981 fetch check_so_link into l_qtyReserved;
3982 close check_so_link;
3983
3984 if ( l_qtyReserved > 0 ) then
3985 l_msg := 'WIP_CANT_CANCEL_SO';
3986 raise fnd_api.g_exc_unexpected_error;
3987 end if;
3988 end if; */
3989
3990
3991 open check_so_link;
3992 fetch check_so_link into l_qtyReserved;
3993 close check_so_link;
3994
3995 if ( l_qtyReserved > 0 and wjsi_row.status_type in (wip_constants.cancelled,
3996 wip_constants.comp_nochrg) -- 13075675
3997 and (l_old_status not in (wip_constants.cancelled, wip_constants.comp_chrg,
3998 wip_constants.comp_nochrg))) then
3999
4000 l_msg := 'WIP_CANT_CANCEL_SO';
4001 raise fnd_api.g_exc_unexpected_error;
4002 end if;
4003
4004 if ( l_qtyReserved > 0 and
4005 wjsi_row.status_type = wip_constants.comp_chrg) then
4006 fnd_message.set_name('WIP','WIP_SO_EXISTS');
4007 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
4008 end if;
4009 -- 13075675 @end
4010
4011 if ( wjsi_row.status_type in (wip_constants.cancelled,
4012 wip_constants.comp_chrg,
4013 wip_constants.comp_nochrg)
4014 and (l_old_status not in (wip_constants.cancelled, wip_constants.comp_chrg,
4015 wip_constants.comp_nochrg))) then
4016 if ( wip_osp.po_req_exists(wjsi_row.wip_entity_id,
4017 null,
4018 wjsi_row.organization_id,
4019 null,
4020 wip_constants.discrete) = true ) then
4021 if ( po_code_release_grp.Current_Release >=
4022 po_code_release_grp.PRC_11i_Family_Pack_J ) then
4023 select propagate_job_change_to_po
4024 into l_propagate_job_change_to_po
4025 from wip_parameters wp
4026 where organization_id = wjsi_row.organization_id;
4027
4028 if ( l_propagate_job_change_to_po = wip_constants.yes and
4029 wjsi_row.status_type in (wip_constants.cancelled,
4030 wip_constants.comp_nochrg) ) then
4031 -- cancel PO/requisition associated to the job if cancel or
4032 -- complete-no-charge
4033 wip_osp.cancelPOReq(p_job_id => wjsi_row.wip_entity_id,
4034 p_org_id => wjsi_row.organization_id,
4035 x_return_status => l_retStatus);
4036 if ( l_retStatus <> fnd_api.g_ret_sts_success ) then
4037 po_warning_flag := WIP_CONSTANTS.YES;
4038 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
4039 end if;
4040 else
4041 -- propagate_job_change_to_po is manual or job status is 'Complete'
4042 po_warning_flag := WIP_CONSTANTS.YES;
4043 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
4044 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
4045 end if;
4046 else
4047 -- customer does not have PO patchset J onward, so behave the the old way
4048 po_warning_flag := WIP_CONSTANTS.YES;
4049 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
4050 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
4051 end if;
4052 end if; -- po/requisiton exist
4053 end if;
4054
4055 -- Bug 3032515 - Added validation to prevent updation to completed/
4056 -- cancelled/completed-no charges/closed jobs through planner module
4057 -- for which source code is populated as MSC
4058 if ( wjsi_row.source_code = 'MSC' and
4059 wdj_row.status_type in (wip_constants.comp_chrg, wip_constants.comp_nochrg,
4060 wip_constants.cancelled, wip_constants.closed) ) then
4061 l_msg := 'WIP_CANT_UPDATE_JOB';
4062 raise fnd_api.g_exc_unexpected_error;
4063 end if;
4064
4065 -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
4066 if ( wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED and
4067 wdj_row.status_type <> WIP_CONSTANTS.UNRELEASED and
4068 WIP_PICKING_PUB.Is_Job_Pick_Released(
4069 p_wip_entity_id => wjsi_row.wip_entity_id,
4070 p_org_id => wjsi_row.organization_id) ) then
4071 l_msg := 'WIP_UNRLS_JOB/SCHED';
4072 raise fnd_api.g_exc_unexpected_error;
4073 end if;
4074 exception
4075 when others then
4076 fnd_message.set_name('WIP', l_msg);
4077 x_errorMsg := fnd_message.get;
4078 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4079 raise line_validation_error;
4080 end validateStatusType;
4081
4082
4083 procedure validateBuildSequence(p_rowid in rowid,
4084 x_errorMsg out nocopy varchar2) is
4085 l_retval boolean;
4086 l_buildSeq number;
4087 begin
4088 if ( wjsi_row.load_type = wip_constants.create_sched ) then
4089 return;
4090 end if;
4091
4092 if ( wjsi_row.build_sequence is null ) then
4093 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4094 l_buildSeq := wdj_row.build_sequence;
4095 else
4096 l_buildSeq := null;
4097 end if;
4098
4099 wjsi_row.build_sequence := wip_jsi_hooks.get_default_build_sequence(wjsi_row.interface_id,
4100 l_buildSeq);
4101 end if;
4102
4103 if ( wjsi_row.build_sequence is not null ) then
4104 l_retval := wip_validate.build_sequence(
4105 p_build_sequence => wjsi_row.build_sequence,
4106 p_wip_entity_id => wjsi_row.wip_entity_id,
4107 p_organization_id => wjsi_row.organization_id,
4108 p_line_id => nvl(wjsi_row.line_id, wdj_row.line_id),
4109 p_schedule_group_id => nvl(wjsi_row.schedule_group_id, wdj_row.schedule_group_id) );
4110 if( not l_retval ) then
4111 fnd_message.set_name('WIP', 'WIP_ML_BUILD_SEQUENCE');
4112 x_errorMsg := fnd_message.get;
4113 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4114 raise line_validation_error;
4115 end if;
4116 end if;
4117 end validateBuildSequence;
4118
4119
4120 procedure validateEndItemUnitNumber(p_rowid in rowid,
4121 x_errorMsg out nocopy varchar2) is
4122 l_bomItemID number;
4123 l_isUnitEffective boolean;
4124 l_dummy number;
4125 l_msg varchar2(30);
4126 begin
4127 --Bug#16002070:Added NVL when bom reference is null for unit effective items
4128 if ( wjsi_row.load_type = wip_constants.create_ns_job) then
4129 l_bomitemID := NVL(wjsi_row.bom_reference_id,wjsi_row.primary_item_id);
4130 else
4131 l_bomItemID := wjsi_row.primary_item_id;
4132 end if;
4133 l_isUnitEffective := l_bomItemID is not null and
4134 pjm_unit_eff.enabled = 'Y' and
4135 pjm_unit_eff.unit_effective_item(l_bomItemID,
4136 wjsi_row.organization_id) = 'Y';
4137
4138 if( l_isUnitEffective and wjsi_row.end_item_unit_number is not null) then
4139 begin
4140 select 1
4141 into l_dummy
4142 from pjm_unit_numbers_lov_v pun,
4143 mtl_parameters mp
4144 where pun.unit_number = wjsi_row.end_item_unit_number
4145 and mp.organization_id = wjsi_row.organization_id
4146 and mp.master_organization_id = pun.master_organization_id;
4147 exception
4148 when too_many_rows then
4149 null; -- the query returning multiple rows is ok
4150 when others then
4151 fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
4152 x_errorMsg := fnd_message.get;
4153 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4154 raise line_validation_error;
4155 end;
4156 end if;
4157
4158 -- You cannot create a repetitive schedule for a unit effective assembly.
4159 if( l_isUnitEffective and wjsi_row.load_type = wip_constants.create_sched ) then
4160 l_msg := 'WIP_ML_NO_UNIT_EFF_SCHED';
4161 raise fnd_api.g_exc_unexpected_error;
4162 end if;
4163
4164 -- It is an error to provide unit number for non-unit effective assemblies.
4165 if (not l_isUnitEffective and wjsi_row.end_item_unit_number is not null) then
4166 l_msg := 'WIP_ML_UNIT_NUM_MEANINGLESS';
4167 raise fnd_api.g_exc_unexpected_error;
4168 end if;
4169
4170 if (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job)) then
4171 -- Unit number is required for unit effective assemblies.
4172 if( l_isUnitEffective and wjsi_row.end_item_unit_number is null) then
4173 fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER REQUIRED');
4174 x_errorMsg := fnd_message.get;
4175 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4176 raise line_validation_error;
4177 end if;
4178 end if;
4179
4180 --if request is for reschedule, keep as is for all cases except when job_status is unreleased
4181 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4182 if ( wjsi_row.end_item_unit_number is null) then
4183 begin
4184 select wdj.end_item_unit_number
4185 into wjsi_row.end_item_unit_number
4186 from wip_discrete_jobs wdj
4187 where wdj.wip_entity_id = wjsi_row.wip_entity_id;
4188 exception
4189 when others then
4190 fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID');
4191 x_errorMsg := fnd_message.get;
4192 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4193 raise line_validation_error;
4194 end;
4195 end if;
4196
4197 if ( wjsi_row.status_type = wip_constants.unreleased and
4198 wjsi_row.end_item_unit_number is not null ) then
4199 -- bug#2719927, bom revision code/reexplosion is based on bom_reference_id
4200 select primary_item_id
4201 into wjsi_row.bom_reference_id
4202 from wip_discrete_jobs
4203 where wip_entity_id = wjsi_row.wip_entity_id;
4204 end if;
4205
4206 if ( wjsi_row.status_type <> wip_constants.unreleased and
4207 wjsi_row.end_item_unit_number is not null ) then
4208 fnd_message.set_name('WIP', 'END_ITEM_UNIT_NUMBER');
4209 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationWarning);
4210 -- Added by Renga Kannan
4211 -- Fixed for bug 5332672
4212 -- Added code to get the end unit item number from wip_discrete_jobs and populate
4213 -- to interface table
4214
4215 begin
4216 select end_item_unit_number into wjsi_row.end_item_unit_number
4217 from wip_discrete_jobs
4218 where wip_entity_id =
4219 (select wip_entity_id
4220 from wip_job_schedule_interface
4221 where rowid = p_rowid
4222 );
4223 exception
4224 when others then
4225 FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
4226 WIP_JSI_Utils.record_current_error ;
4227 raise fnd_api.g_exc_unexpected_error;
4228 end ;
4229
4230 -- End of bug fix 5332672
4231 end if;
4232 end if;
4233 exception
4234 when fnd_api.g_exc_unexpected_error then
4235 fnd_message.set_name('WIP', l_msg);
4236 x_errorMsg := fnd_message.get;
4237 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4238 raise line_validation_error;
4239 end validateEndItemUnitNumber;
4240
4241
4242 procedure validateDailyProductionRate(p_rowid in rowid,
4243 x_errorMsg out nocopy varchar2) is
4244 l_maxLineRate number;
4245 begin
4246 if( wjsi_row.load_type <> wip_constants.create_sched ) then
4247 return;
4248 end if;
4249
4250 select daily_maximum_rate
4251 into l_maxLineRate
4252 from wip_lines_val_v
4253 where line_id = wjsi_row.line_id;
4254
4255 if( l_maxLineRate < wjsi_row.daily_production_rate ) then
4256 fnd_message.set_name('WIP', 'WIP_PROD_RATE_WARNING');
4257 fnd_message.set_token('ENTITY1', l_maxLineRate);
4258 setInterfaceError(p_rowid, wjsi_row.interface_id, fnd_message.get, validationWarning);
4259 end if;
4260 end validateDailyProductionRate;
4261
4262
4263 procedure validateRepScheduleDates(p_rowid in rowid,
4264 x_errorMsg out nocopy varchar2) is
4265 l_dateCount number := 0;
4266 l_lineCount number;
4267 l_rtgCount number;
4268 begin
4269 if ( wjsi_row.load_type <> wip_constants.create_sched ) then
4270 return;
4271 end if;
4272
4273 if ( wjsi_row.first_unit_start_date is not null ) then
4274 l_dateCount := l_dateCount + 1;
4275 end if;
4276
4277 if ( wjsi_row.last_unit_completion_date is not null ) then
4278 l_dateCount := l_dateCount + 1;
4279 end if;
4280
4281 if ( wjsi_row.first_unit_completion_date is not null ) then
4282 l_dateCount := l_dateCount + 1;
4283 end if;
4284
4285 if ( wjsi_row.last_unit_start_date is not null ) then
4286 l_dateCount := l_dateCount + 1;
4287 end if;
4288
4289 if ( l_dateCount = 0 ) then
4290 --must provide at least one date for rep sched
4291 raise fnd_api.g_exc_unexpected_error;
4292 end if;
4293
4294 if ( l_dateCount <> 1 ) then
4295 select count(*)
4296 into l_lineCount
4297 from wip_lines
4298 where organization_id = wjsi_row.organization_id
4299 and line_id = wjsi_row.line_id
4300 and line_schedule_type = 1; --fixed
4301 if( l_lineCount > 0 ) then
4302 --the line can not have a fixed lead time
4303 raise fnd_api.g_exc_unexpected_error;
4304 end if;
4305
4306 select count(*)
4307 into l_rtgCount
4308 from bom_operational_routings bor,
4309 wip_repetitive_items wri
4310 where wri.line_id = wjsi_row.line_id
4311 and nvl(bor.cfm_routing_flag, 2) = 2 --ignore flow rtgs
4312 and wri.primary_item_id = wjsi_row.primary_item_id
4313 and wri.organization_id = wjsi_row.organization_id
4314 and nvl(bor.alternate_routing_designator,'@@@') = nvl(wri.alternate_routing_designator, '@@@')
4315 and bor.organization_id = wri.organization_id
4316 and bor.assembly_item_id = wri.primary_item_id;
4317 if ( l_rtgCount > 0 ) then
4318 --the line can not have a routing
4319 raise fnd_api.g_exc_unexpected_error;
4320 end if;
4321 end if;
4322
4323 select count(*)
4324 into l_rtgCount
4325 from bom_operational_routings bor,
4326 wip_repetitive_items wri
4327 where wri.line_id = wjsi_row.line_id
4328 and nvl(bor.cfm_routing_flag,2) = 2 --ignore flow routings
4329 and wri.primary_item_id = wjsi_row.primary_item_id
4330 and wri.organization_id = wjsi_row.organization_id
4331 and nvl(bor.alternate_routing_designator,'@@@') = nvl(wri.alternate_routing_designator,'@@@')
4332 and bor.organization_id = wri.organization_id
4333 and bor.assembly_item_id = wri.primary_item_id;
4334
4335 select count(*)
4336 into l_lineCount
4337 from wip_lines_val_v
4338 where organization_id = wjsi_row.organization_id
4339 and line_id = wjsi_row.line_id
4340 and line_schedule_type = 2;
4341
4342 --providing exactly the first dates or the last dates is an error condition
4343 if( not (l_dateCount = 2 and
4344 ((wjsi_row.first_unit_start_date is not null and wjsi_row.first_unit_completion_date is not null) or
4345 (wjsi_row.last_unit_start_date is not null and wjsi_row.last_unit_completion_date is not null))) ) then
4346 if ( l_rtgCount = 0 and l_lineCount > 0 ) then
4347 raise fnd_api.g_exc_unexpected_error;
4348 end if;
4349 end if;
4350
4351 if ( l_lineCount > 0 and l_rtgCount = 0 ) then
4352 -- estimate schedule dates
4353 if ( wjsi_row.first_unit_start_date is null ) then
4354 -- Bug 4890514. Performance Fix
4355 -- saugupta 25th-May-2006
4356 /*
4357 select calendar_date
4358 into wjsi_row.first_unit_start_date
4359 from bom_calendar_dates bcd,
4360 mtl_parameters mp
4361 where mp.organization_id = wjsi_row.organization_id
4362 and bcd.exception_set_id = mp.calendar_exception_set_id
4363 and bcd.calendar_code = mp.calendar_code
4364 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
4365 from bom_calendar_dates b2
4366 where b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
4367 and b2.calendar_code = bcd.calendar_code
4368 and b2.exception_set_id = bcd.exception_set_id);
4369 */
4370 SELECT bcd.calendar_date
4371 INTO wjsi_row.first_unit_start_date
4372 FROM bom_calendar_dates bcd,
4373 mtl_parameters mp,
4374 bom_calendar_dates b2
4375 WHERE mp.organization_id = wjsi_row.organization_id
4376 and bcd.exception_set_id = mp.calendar_exception_set_id
4377 and bcd.calendar_code = mp.calendar_code
4378 and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
4379 and b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
4380 and b2.calendar_code = mp.calendar_code;
4381
4382 end if;
4383
4384 if(wjsi_row.last_unit_start_date is null) then
4385 -- Bug 4890514. Performance Fix
4386 -- saugupta 25th-May-2006
4387 /*
4388 select calendar_date
4389 into wjsi_row.last_unit_start_date
4390 from bom_calendar_dates bcd,
4391 mtl_parameters mp
4392 where mp.organization_id = wjsi_row.organization_id
4393 and bcd.exception_set_id = mp.calendar_exception_set_id
4394 and bcd.calendar_code = mp.calendar_code
4395 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
4396 from bom_calendar_dates b2
4397 where b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
4398 and b2.calendar_code = bcd.calendar_code
4399 and b2.exception_set_id = bcd.exception_set_id);
4400 */
4401 SELECT bcd.calendar_date
4402 INTO wjsi_row.last_unit_start_date
4403 FROM bom_calendar_dates bcd,
4404 mtl_parameters mp,
4405 bom_calendar_dates b2
4406 WHERE mp.organization_id = wjsi_row.organization_id
4407 and bcd.exception_set_id = mp.calendar_exception_set_id
4408 and bcd.calendar_code = mp.calendar_code
4409 and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
4410 and b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
4411 and b2.calendar_code = mp.calendar_code;
4412
4413 end if;
4414
4415 if(wjsi_row.first_unit_completion_date is null) then
4416 -- Bug 4890514. Performance Fix
4417 -- saugupta 25th-May-2006
4418 /*
4419 select calendar_date
4420 into wjsi_row.first_unit_completion_date
4421 from bom_calendar_dates bcd,
4422 mtl_parameters mp
4423 where mp.organization_id = wjsi_row.organization_id
4424 and bcd.exception_set_id = mp.calendar_exception_set_id
4425 and bcd.calendar_code = mp.calendar_code
4426 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
4427 from bom_calendar_dates b2
4428 where b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
4429 and b2.calendar_code = bcd.calendar_code
4430 and b2.exception_set_id = bcd.exception_set_id);
4431 */
4432
4433 SELECT bcd.calendar_date
4434 INTO wjsi_row.first_unit_completion_date
4435 FROM bom_calendar_dates bcd,
4436 mtl_parameters mp,
4437 bom_calendar_dates b2
4438 WHERE mp.organization_id = wjsi_row.organization_id
4439 and bcd.exception_set_id = mp.calendar_exception_set_id
4440 and bcd.calendar_code = mp.calendar_code
4441 and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
4442 and b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
4443 and b2.calendar_code = mp.calendar_code;
4444
4445 end if;
4446
4447 if(wjsi_row.last_unit_completion_date is null) then
4448 -- Bug 4890514. Performance Fix
4449 -- saugupta 25th-May-2006
4450 /*
4451 select calendar_date
4452 into wjsi_row.last_unit_completion_date
4453 from bom_calendar_dates bcd, mtl_parameters mp
4454 where mp.organization_id = wjsi_row.organization_id
4455 and bcd.exception_set_id = mp.calendar_exception_set_id
4456 and bcd.calendar_code = mp.calendar_code
4457 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
4458 from bom_calendar_dates b2
4459 where b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
4460 and b2.calendar_code = bcd.calendar_code
4461 and b2.exception_set_id = bcd.exception_set_id);
4462 */
4463
4464 SELECT bcd.calendar_date
4465 INTO wjsi_row.last_unit_completion_date
4466 FROM bom_calendar_dates bcd,
4467 mtl_parameters mp,
4468 bom_calendar_dates b2
4469 WHERE mp.organization_id = wjsi_row.organization_id
4470 and bcd.exception_set_id = mp.calendar_exception_set_id
4471 and bcd.calendar_code = mp.calendar_code
4472 and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
4473 and b2.calendar_date = trunc(wjsi_row.first_unit_completion_date )
4474 and b2.calendar_code = mp.calendar_code;
4475 end if;
4476 end if;
4477 exception
4478 when others then
4479 fnd_message.set_name('WIP', 'WIP_ML_REPETITIVE_DATES');
4480 x_errorMsg := fnd_message.get;
4481 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4482 raise line_validation_error;
4483 end validateRepScheduleDates;
4484
4485 --
4486 -- This procedure is not called during validatoin phase. It must be called after the explosion
4487 -- so work order will be populated with job operations.
4488 --
4489 procedure defaultSerializationStartOp(p_rowid in rowid,
4490 p_rtgVal in number) is
4491 l_minOp NUMBER;
4492 l_loadType NUMBER;
4493 l_default NUMBER;
4494 l_startOp NUMBER;
4495 l_wipID NUMBER;
4496 l_primaryItem NUMBER;
4497 l_orgID NUMBER;
4498 begin
4499 select wp.default_serialization_start_op,
4500 wjsi.load_type,
4501 wjsi.serialization_start_op,
4502 wjsi.wip_entity_id,
4503 wjsi.primary_item_id,
4504 wjsi.organization_id
4505 into l_default,
4506 l_loadType,
4507 l_startOp,
4508 l_wipID,
4509 l_primaryItem,
4510 l_orgID
4511 from wip_parameters wp, wip_job_schedule_interface wjsi
4512 where wjsi.rowid = p_rowid
4513 and wjsi.organization_id = wp.organization_id;
4514
4515 if( l_startOp is not null or l_primaryItem is null ) then
4516 return;
4517 end if;
4518
4519 if ( l_loadType in (wip_constants.create_job, wip_constants.create_ns_job) ) then
4520 if ( p_rtgVal is not null ) then
4521 update wip_discrete_jobs
4522 set serialization_start_op = p_rtgVal
4523 where wip_entity_id = l_wipID
4524 and exists (select 1
4525 from mtl_system_items
4526 where inventory_item_id = l_primaryItem
4527 and organization_id = l_orgID
4528 and serial_number_control_code = wip_constants.full_sn);
4529
4530 elsif ( l_default = wip_constants.yes ) then
4531 update wip_discrete_jobs
4532 set serialization_start_op = (select nvl(min(operation_seq_num), 1)
4533 from wip_operations
4534 where wip_entity_id = l_wipID)
4535 where wip_entity_id = l_wipID
4536 and exists (select 1
4537 from mtl_system_items
4538 where inventory_item_id = l_primaryItem
4539 and organization_id = l_orgID
4540 and serial_number_control_code = wip_constants.full_sn);
4541 end if;
4542 end if;
4543 end defaultSerializationStartOp;
4544
4545 --
4546 -- Unlike other procedure, this one has to be called after the explosion. We can only validate op related
4547 -- after the explosion and the possible details loading.
4548 --
4549 procedure validateSerializationStartOp(p_rowid in rowid,
4550 x_returnStatus out nocopy varchar2,
4551 x_errorMsg out nocopy varchar2) is
4552 l_wipEntityID number;
4553 l_serialOp number;
4554 l_loadType number;
4555 l_interfaceID number;
4556
4557 l_curOpSeq number;
4558 l_rtgExists boolean;
4559 l_opFound boolean;
4560 l_dummy number;
4561
4562 cursor c_ops(p_wipEntityId number) is
4563 select operation_seq_num
4564 from wip_operations
4565 where wip_entity_id = p_wipEntityId;
4566 begin
4567 x_returnStatus := fnd_api.g_ret_sts_success;
4568
4569 select wip_entity_id,
4570 serialization_start_op,
4571 load_type,
4572 interface_id
4573 into l_wipEntityID, l_serialOp, l_loadType, l_interfaceID
4574 from wip_job_schedule_interface wjsi
4575 where wjsi.rowid = p_rowid;
4576
4577 if ( l_serialOp is null ) then
4578 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4579 --in this case, we may need to clear the serialization op if the routing was re-exploded
4580 update wip_discrete_jobs wdj
4581 set serialization_start_op = null
4582 where wip_entity_id = l_wipEntityID
4583 and serialization_start_op <> 1
4584 and not exists(select 1
4585 from wip_operations wo
4586 where wo.wip_entity_id = wdj.wip_entity_id
4587 and wo.operation_seq_num = wdj.serialization_start_op);
4588 end if;
4589
4590 return;
4591 end if;
4592
4593 --job must have an assembly, and the assembly must be serial controlled (predefined).
4594 select 1
4595 into l_dummy
4596 from wip_discrete_jobs wdj,
4597 mtl_system_items msi
4598 where wdj.primary_item_id = msi.inventory_item_id
4599 and wdj.organization_id = msi.organization_id
4600 and wdj.wip_entity_id = l_wipEntityID
4601 and msi.serial_number_control_code = wip_constants.full_sn;
4602
4603 open c_ops(l_wipEntityId);
4604 loop
4605 fetch c_ops into l_curOpSeq;
4606 exit when c_ops%NOTFOUND;
4607 l_rtgExists := true;
4608 if(l_curOpSeq = l_serialOp) then
4609 l_opFound := true;
4610 exit;
4611 end if;
4612 end loop;
4613 close c_ops;
4614
4615 --The routing exists, but an invalid op seq was provided
4616 if( l_rtgExists and not l_opFound ) then
4617 raise fnd_api.g_exc_unexpected_error;
4618 end if;
4619
4620 --If no routing exsts, the serialization op must be 1.
4621 if( not l_rtgExists and l_serialOp <> 1 ) then
4622 raise fnd_api.g_exc_unexpected_error;
4623 end if;
4624
4625 -- job must be unreleased to change the serialization op on a
4626 -- reschedule request. This is to guarantee no txns have taken place.
4627 if ( l_loadType = wip_constants.resched_job ) then
4628 select 1
4629 into l_dummy
4630 from wip_discrete_jobs
4631 where wip_entity_id = l_wipEntityID
4632 and status_type = wip_constants.unreleased;
4633 end if;
4634 exception
4635 when others then
4636 x_returnStatus := fnd_api.g_ret_sts_unexp_error;
4637 fnd_message.set_name('WIP', 'WIP_ML_SERIAL_START_OP');
4638 x_errorMsg := fnd_message.get;
4639 setInterfaceError(p_rowid, l_interfaceID, x_errorMsg, validationError);
4640 end validateSerializationStartOp;
4641
4642
4643 procedure validateSubinvLocator(p_rowid in rowid,
4644 x_errorMsg out nocopy varchar2) is
4645 l_projectLocID number;
4646 l_subLocCtl number;
4647 l_orgLocCtl number;
4648 l_success boolean;
4649 l_msg varchar2(30);
4650 begin
4651 /*Bug 5446216 (FP Bug 5504790): Subinventory can be passed as null during job rescheduling. */
4652 if ( /* wjsi_row.completion_subinventory is null or */
4653 wjsi_row.load_type in (wip_constants.create_sched)) then
4654 --Bug 5191031:It is allowed to modify locator during job rescheduling.
4655 --Hence validation below should be executed when load_type is resched_job
4656 --wjsi_row.load_type in (wip_constants.create_sched,
4657 -- wip_constants.resched_job) ) then
4658 return;
4659 end if;
4660
4661 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4662 wjsi_row.project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
4663 wjsi_row.task_id := nvl(wjsi_row.task_id, wdj_row.task_id);
4664 /*Bug 5446216 (FP Bug 5504790): Need not copy old value if value is fnd_api.g_miss_char*/
4665 if wjsi_row.completion_subinventory is null then
4666 if ( wjsi_row.completion_locator_id is null) then
4667 wjsi_row.completion_locator_id := wdj_row.completion_locator_id;
4668 end if;
4669 elsif (wjsi_row.completion_subinventory = fnd_api.g_miss_char) then
4670 wjsi_row.completion_locator_id := null;
4671 end if;
4672 wjsi_row.completion_subinventory :=
4673 nvl(wjsi_row.completion_subinventory, wdj_row.completion_subinventory);
4674
4675 --Bug 5191031:Following defaulting prevents the following update:
4676 --Update the completion sub inv and locator of a job from SX and LX to SY and NULL.
4677 --where SY is not locator controlled.
4678 --if (wjsi_row.completion_subinventory is not null) then
4679 -- wjsi_row.completion_locator_id := nvl(wjsi_row.completion_locator_id,
4680 -- wdj_row.completion_locator_id);
4681 --end if;
4682 end if;
4683
4684 if ( wjsi_row.project_id is not null) then
4685 if(pjm_project_locator.check_itemLocatorControl(wjsi_row.organization_id,
4686 wjsi_row.completion_subinventory,
4687 wjsi_row.completion_locator_id,
4688 primary_item_row.inventory_item_id,
4689 2)) then
4690 pjm_project_locator.get_defaultProjectLocator(wjsi_row.organization_id,
4691 wjsi_row.completion_locator_id,
4692 wjsi_row.project_id,
4693 wjsi_row.task_id,
4694 l_projectLocID);
4695 if ( l_projectLocID is not null ) then
4696 wjsi_row.completion_locator_id := l_projectLocID;
4697 if(not pjm_project_locator.check_project_references(wjsi_row.organization_id,
4698 l_projectLocID,
4699 'SPECIFIC', -- validation mode
4700 'Y', -- required?
4701 wjsi_row.project_id,
4702 wjsi_row.task_id)) then
4703 l_msg := 'WIP_ML_LOCATOR_PROJ_TASK';
4704 raise fnd_api.g_exc_unexpected_error;
4705 end if;
4706 end if;
4707 end if;
4708 end if;
4709
4710 /* Bug 5446216 (FP Bug 5504790):Need not validate locator when subinventory is null */
4711 if(wjsi_row.load_type <> wip_constants.create_sched and
4712 ((wjsi_row.completion_subinventory IS NOT NULL AND wjsi_row.completion_subinventory <> fnd_api.g_miss_char)
4713 OR (wjsi_row.completion_locator_id IS NOT NULL AND wjsi_row.completion_locator_id <> fnd_api.g_miss_num))) then
4714
4715 l_msg := 'WIP_ML_INVALID_LOCATOR';
4716
4717 select sub.locator_type, mp.stock_locator_control_code
4718 into l_subLocCtl, l_orgLocCtl
4719 from mtl_secondary_inventories sub,
4720 mtl_parameters mp
4721 where sub.secondary_inventory_name = wjsi_row.completion_subinventory
4722 and sub.organization_id = wjsi_row.organization_id
4723 and mp.organization_id = wjsi_row.organization_id;
4724
4725 wip_locator.validate(
4726 p_organization_id => wjsi_row.organization_id,
4727 p_item_id => primary_item_row.inventory_item_id,
4728 p_subinventory_code => wjsi_row.completion_subinventory,
4729 p_org_loc_control => l_orgLocCtl,
4730 p_sub_loc_control => l_subLocCtl,
4731 p_item_loc_control => primary_item_row.location_control_code,
4732 p_restrict_flag => primary_item_row.restrict_locators_code,
4733 p_neg_flag => '',
4734 p_action => '',
4735 p_project_id => wjsi_row.project_id,
4736 p_task_id => wjsi_row.task_id,
4737 p_locator_id => wjsi_row.completion_locator_id,
4738 p_locator_segments => wjsi_row.completion_locator_segments,
4739 p_success_flag => l_success);
4740
4741 if ( not l_success ) then
4742 raise fnd_api.g_exc_unexpected_error;
4743 end if;
4744
4745 end if;
4746
4747 exception
4748 when others then
4749 fnd_message.set_name('WIP', l_msg);
4750 x_errorMsg := fnd_message.get;
4751 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4752 raise line_validation_error;
4753 end validateSubinvLocator;
4754
4755 /* Fix for #6117094. Added following procedure not to change job date when
4756 source is MSC and completed operation (not passed by ASCP) exists on a
4757 job
4758 */
4759 procedure deriveScheduleDate(p_rowid in rowid,
4760 x_errorMsg out nocopy varchar2) is
4761 l_params wip_logger.param_tbl_t;
4762 l_ret_status varchar2(20);
4763 l_logLevel number := to_number(fnd_log.g_current_runtime_level);
4764 l_procName varchar2(30) := 'deriveScheduleDate';
4765 begin
4766 if (l_logLevel <= wip_constants.trace_logging) then
4767 l_params(1).paramName := 'p_rowid';
4768 l_params(1).paramValue := p_rowid;
4769 wip_logger.entryPoint(p_procName => g_pkgName || '.' || l_procName,
4770 p_params => l_params,
4771 x_returnStatus => l_ret_status);
4772 if(l_ret_status <> fnd_api.g_ret_sts_success) then
4773 raise fnd_api.g_exc_unexpected_error;
4774 end if;
4775 end if;
4776
4777
4778 /* Populate original job start date as fusd. This will ensure that completed operation
4779 dates are updated as original job start date in update_routing procedure in wipschdb.pls .
4780 */
4781
4782 if (wjsi_row.source_code = 'MSC' and
4783 wjsi_row.load_type = wip_constants.resched_job and
4784 wjsi_row.scheduling_method = wip_constants.ml_manual) then
4785
4786 begin
4787 select wdj.scheduled_start_date
4788 into wjsi_row.first_unit_start_date
4789 from wip_discrete_jobs wdj
4790 where wdj.wip_entity_id = wjsi_row.wip_entity_id
4791 and wdj.organization_id = wjsi_row.organization_id
4792 and exists ( select operation_seq_num
4793 from wip_operations wo
4794 where wo.wip_entity_id = wdj.wip_entity_id and
4795 wo.organization_id = wdj.organization_id
4796 minus
4797 select operation_seq_num
4798 from wip_job_dtls_interface
4799 where group_id = wjsi_row.group_id
4800 and parent_header_id = wjsi_row.header_id
4801 and load_type = WIP_JOB_DETAILS.WIP_OPERATION
4802 ) ;
4803 wip_logger.log(' Repopulated Scheduled Start Date as' || wjsi_row.first_unit_start_date,
4804 l_ret_status );
4805 exception
4806 when NO_DATA_FOUND then
4807 null;
4808 end;
4809 end if;
4810
4811 if (l_logLevel <= wip_constants.trace_logging) then
4812 wip_logger.exitPoint(p_procName => g_pkgName || '.' || l_procName,
4813 p_procReturnStatus => null,
4814 p_msg => 'success',
4815 x_returnStatus => l_ret_status);
4816 end if;
4817
4818 end deriveScheduleDate;
4819
4820 procedure validateLotNumber(p_rowid in rowid,
4821 x_errorMsg out nocopy varchar2) is
4822 begin
4823 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4824 wjsi_row.lot_number := wip_lot_number_default.lot_number(
4825 p_item_id => wdj_row.primary_item_id,
4826 p_organization_id => wjsi_row.organization_id,
4827 p_lot_number => wjsi_row.lot_number,
4828 p_job_name => wdj_row.wip_entity_name,
4829 p_default_flag => 0);
4830 elsif ( wjsi_row.load_type in (wip_constants.create_job,
4831 wip_constants.create_ns_job) ) then
4832 wjsi_row.lot_number := wip_lot_number_default.lot_number(
4833 p_item_id => wjsi_row.primary_item_id,
4834 p_organization_id => wjsi_row.organization_id,
4835 p_lot_number => wjsi_row.lot_number,
4836 p_job_name => wjsi_row.job_name,
4837 p_default_flag => 1);
4838 end if;
4839 end validateLotNumber;
4840
4841
4842 procedure validateKanban(p_rowid in rowid,
4843 p_validationLevel in number,
4844 x_errorMsg out nocopy varchar2) is
4845 l_raw_job WIP_Work_Order_Pub.DiscreteJob_Rec_Type ;
4846 l_defaulted_job WIP_Work_Order_Pub.DiscreteJob_Rec_Type ;
4847 l_raw_sched WIP_Work_Order_Pub.RepSchedule_Rec_Type ;
4848 l_defaulted_sched WIP_Work_Order_Pub.RepSchedule_Rec_Type ;
4849
4850 l_doc_type NUMBER;
4851 l_doc_header_id NUMBER;
4852 l_status VARCHAR2(100);
4853 l_msg varchar2(30);
4854 l_valid_card Number := 0;
4855 begin
4856 if ( wjsi_row.kanban_card_id is null ) then
4857 return;
4858 end if;
4859
4860 begin
4861 select 1
4862 into l_valid_card
4863 from mtl_kanban_cards
4864 where kanban_card_id = wjsi_row.kanban_card_id
4865 and source_type = 4
4866 and supply_status = 4; /* empty production kanban */
4867 exception
4868 when others then
4869 null;
4870 end;
4871
4872 if ( p_validationLevel <> wip_constants.inv and l_valid_card = 0 ) then
4873 l_msg := 'WIP_ML_KB_SRC_NOT_INV';
4874 raise fnd_api.g_exc_unexpected_error;
4875 else
4876 if ( wjsi_row.load_type = wip_constants.create_job ) then
4877 l_raw_job := WIP_Work_Order_Pub.G_MISS_DISCRETEJOB_REC;
4878
4879 l_raw_job.organization_id := wjsi_row.organization_id;
4880 l_raw_job.kanban_card_id := wjsi_row.kanban_card_id;
4881 l_raw_job.primary_item_id := nvl(wjsi_row.primary_item_id, l_raw_job.primary_item_id);
4882 l_raw_job.completion_subinventory := nvl(wjsi_row.completion_subinventory, l_raw_job.completion_subinventory);
4883 l_raw_job.completion_locator_id := nvl(wjsi_row.completion_locator_id, l_raw_job.completion_locator_id);
4884 l_raw_job.start_quantity := nvl(wjsi_row.start_quantity, l_raw_job.start_quantity);
4885 l_raw_job.action := WIP_Globals.G_OPR_DEFAULT_USING_KANBAN;
4886
4887 WIP_Default_DiscreteJob.attributes(p_discreteJob_rec => l_raw_job,
4888 x_discreteJob_rec => l_defaulted_job,
4889 p_redefault => false);
4890
4891 l_defaulted_job := WIP_DiscreteJob_Util.convert_miss_to_null(l_defaulted_job);
4892 wjsi_row.primary_item_id := l_defaulted_job.primary_item_id;
4893 wjsi_row.completion_subinventory := l_defaulted_job.completion_subinventory;
4894 wjsi_row.completion_locator_id := l_defaulted_job.completion_locator_id;
4895 wjsi_row.start_quantity := l_defaulted_job.start_quantity;
4896 elsif ( wjsi_row.load_type = wip_constants.create_sched) then
4897 l_raw_sched := WIP_Work_Order_Pub.G_MISS_REPSCHEDULE_REC;
4898
4899 l_raw_sched.organization_id := wjsi_row.organization_id;
4900 l_raw_sched.kanban_card_id := wjsi_row.kanban_card_id ;
4901 l_raw_sched.line_id := nvl(wjsi_row.line_id, l_raw_sched.line_id);
4902 l_raw_sched.processing_work_days := nvl(wjsi_row.processing_work_days, l_raw_sched.processing_work_days);
4903 l_raw_sched.first_unit_cpl_date := nvl(wjsi_row.first_unit_completion_date, l_raw_sched.first_unit_cpl_date);
4904 l_raw_sched.daily_production_rate := nvl(wjsi_row.daily_production_rate, l_raw_sched.daily_production_rate);
4905 l_raw_sched.action := WIP_Globals.G_OPR_DEFAULT_USING_KANBAN;
4906
4907 WIP_Default_RepSchedule.attributes(p_RepSchedule_rec => l_raw_sched,
4908 x_RepSchedule_rec => l_defaulted_sched,
4909 p_redefault => false);
4910
4911 l_defaulted_sched := WIP_RepSchedule_Util.convert_miss_to_null(l_defaulted_sched);
4912 wjsi_row.line_id := l_defaulted_sched.line_id;
4913 wjsi_row.processing_work_days := l_defaulted_sched.processing_work_days;
4914 wjsi_row.first_unit_completion_date := l_defaulted_sched.first_unit_cpl_date;
4915 wjsi_row.daily_production_rate := l_defaulted_sched.daily_production_rate;
4916 else
4917 l_msg := 'WIP_ML_BAD_KB_LOAD';
4918 raise fnd_api.g_exc_unexpected_error;
4919 end if;
4920 end if;
4921
4922 l_msg := 'WIP_ML_KB_UPDATE_FAILED';
4923 l_doc_header_id := wjsi_row.wip_entity_id ;
4924 if(wjsi_row.load_type = wip_constants.create_job) then
4925 l_doc_type := INV_Kanban_PVT.G_doc_type_Discrete_Job ;
4926 elsif(wjsi_row.load_type = wip_constants.create_sched) then
4927 l_doc_type := INV_Kanban_PVT.G_doc_type_Rep_Schedule ;
4928 else
4929 raise fnd_api.g_exc_unexpected_error;
4930 end if;
4931
4932 -- Tell Inventory to update the kanban card's supply status.
4933 -- Abort this request if unsuccessful.
4934 begin
4935 inv_kanban_pvt.update_card_supply_status (
4936 x_return_status => l_status,
4937 p_kanban_card_id => wjsi_row.kanban_card_id,
4938 p_supply_status => INV_Kanban_PVT.G_Supply_Status_InProcess,
4939 p_document_type => l_doc_type,
4940 p_document_header_id => l_doc_header_id);
4941 exception
4942 when others then
4943 l_status := null ;
4944 end ;
4945
4946 if((l_status is null) or (l_status <> fnd_api.g_ret_sts_success)) then
4947 raise fnd_api.g_exc_unexpected_error;
4948 end if ;
4949 exception
4950 when others then
4951 fnd_message.set_name('WIP', l_msg);
4952 x_errorMsg := fnd_message.get;
4953 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
4954 raise line_validation_error;
4955 end validateKanban;
4956
4957
4958 procedure validateOvercompletion(p_rowid in rowid,
4959 x_errorMsg out nocopy varchar2) is
4960 l_msg varchar2(30);
4961 begin
4962 if ( wjsi_row.load_type = wip_constants.resched_job ) then
4963 if ( wjsi_row.overcompletion_tolerance_type is null ) then
4964 wjsi_row.overcompletion_tolerance_type := wdj_row.overcompletion_tolerance_type;
4965 end if;
4966 if ( wjsi_row.overcompletion_tolerance_value is null ) then
4967 wjsi_row.overcompletion_tolerance_value := wdj_row.overcompletion_tolerance_value;
4968 end if;
4969 end if;
4970
4971 if ( wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
4972 wjsi_row.primary_item_id is not null and
4973 wjsi_row.overcompletion_tolerance_type is null and
4974 wjsi_row.overcompletion_tolerance_value is null ) then
4975 wip_overcompletion.get_tolerance_default(
4976 p_primary_item_id => wjsi_row.primary_item_id,
4977 p_org_id => wjsi_row.organization_id,
4978 p_tolerance_type => wjsi_row.overcompletion_tolerance_type,
4979 p_tolerance_value => wjsi_row.overcompletion_tolerance_value);
4980
4981 end if;
4982
4983 if ( wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job) or
4984 (wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is not null) ) then
4985 if ( wjsi_row.overcompletion_tolerance_type is not null and
4986 wjsi_row.overcompletion_tolerance_value is not null ) then
4987 if ( wjsi_row.overcompletion_tolerance_type not in (wip_constants.percent, wip_constants.amount) ) then
4988 l_msg:= 'WIP_ML_COMP_TOLERANCE_TYPE';
4989 raise fnd_api.g_exc_unexpected_error;
4990 end if;
4991 if( wjsi_row.overcompletion_tolerance_value < 0 ) then
4992 l_msg := 'WIP_ML_COMP_TOLERANCE_NEGATIVE';
4993 raise fnd_api.g_exc_unexpected_error;
4994 end if;
4995 elsif ( wjsi_row.overcompletion_tolerance_type is not null or
4996 wjsi_row.overcompletion_tolerance_value is not null ) then
4997 -- only one overcompletion column was provided
4998 l_msg := 'WIP_ML_COMP_TOLERANCE_NULL';
4999 raise fnd_api.g_exc_unexpected_error;
5000 end if;
5001 end if;
5002 exception
5003 when others then
5004 fnd_message.set_name('WIP', l_msg);
5005 x_errorMsg := fnd_message.get;
5006 setInterfaceError(p_rowid, wjsi_row.interface_id, x_errorMsg, validationError);
5007 raise line_validation_error;
5008 end validateOvercompletion;
5009
5010 procedure loadInterfaceError(p_interfaceTbl in out nocopy num_tbl_t,
5011 p_text in varchar2,
5012 p_type in number) is
5013 begin
5014 /*Bug 16529960: error message p_text passed in can be larger than the database's maximum column width.
5015 Adding substr to avoid unexpected exception ORA-12899*/
5016 for i in 1 .. p_interfaceTbl.count loop
5017 insert into wip_interface_errors(
5018 interface_id,
5019 error_type,
5020 error,
5021 last_update_date,
5022 creation_date,
5023 created_by,
5024 last_update_login,
5025 last_updated_by
5026 )values(
5027 p_interfaceTbl(i),
5028 p_type,
5029 substr(p_text,1,500),
5030 sysdate,
5031 sysdate,
5032 fnd_global.user_id,
5033 fnd_global.login_id,
5034 fnd_global.user_id);
5035 end loop;
5036 -- clear the interface id table
5037 p_interfaceTbl.delete;
5038 end loadInterfaceError;
5039
5040
5041 procedure setInterfaceError(p_rowid in rowid,
5042 p_interfaceID in number,
5043 p_text in varchar2,
5044 p_type in number) is
5045 l_processStatus number;
5046 begin
5047 l_processStatus := wip_constants.error;
5048 if ( p_type = validationWarning ) then
5049 l_processStatus := wip_constants.warning;
5050 end if;
5051
5052 update wip_job_schedule_interface
5053 set process_status = l_processStatus,
5054 last_update_date = sysdate
5055 where rowid = p_rowid;
5056 /*Bug 16529960: error message p_text passed in can be larger than the database's maximum column width.
5057 Adding substr to avoid unexpected exception ORA-12899*/
5058 insert into wip_interface_errors(
5059 interface_id,
5060 error_type,
5061 error,
5062 last_update_date,
5063 creation_date,
5064 created_by,
5065 last_update_login,
5066 last_updated_by
5067 )values(
5068 p_interfaceID,
5069 p_type,
5070 substr(p_text,1,500),
5071 sysdate,
5072 sysdate,
5073 fnd_global.user_id,
5074 fnd_global.login_id,
5075 fnd_global.user_id
5076 );
5077 end setInterfaceError;
5078
5079 end wip_validateMLHeader_pvt;
[Home] [Help]