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