1 package body wip_jsi_defaulter as
2 /* $Header: wipjsidb.pls 120.5 2011/03/25 02:05:34 hliew ship $ */
3
4 /* private variables */
5 wjsi_row wip_job_schedule_interface%ROWTYPE;
6
7 /* forward declarations */
8
9 /* This package defaults all the column values in the wip mass load process. */
10 -- procedure row_by_row ;
11 procedure created_by_name;
12 procedure last_updated_by_name;
13 procedure organization; --should be first after standard who columns
14 procedure job_name;
15 procedure wip_entity_id;
16 procedure repetitive_schedule_id;
17 procedure kanban_card_id; --may default job properties based on kanban id
18 procedure schedule_group;
19 procedure line_code;
20 procedure project_number;
21 procedure task_number;--10
22 procedure firm_planned_flag;
23 procedure description;
24 procedure status_type;
25 procedure wip_supply_type;
26 procedure class_code;
27 procedure primary_item;
28 procedure start_quantity;
29 procedure net_quantity;
30 procedure overcompletion;
31 procedure asset_number;--20
32 procedure asset_group;
33 procedure parent_job_name;
34 procedure parent_wip_entity_id;
35 procedure rebuild_item;
36 procedure rebuild_serial_number;
37 procedure manual_rebuild_flag;
38 procedure first_unit_start_date;
39 /* Uncommented to fix bug #5912951-FP of 5891243 */
40 -- procedure last_unit_start_date;
41 -- procedure first_unit_completion_date;
42 -- procedure last_unit_completion_date;
43 procedure due_date;
44 procedure date_released;
45 procedure requested_start_date;
46 procedure processing_work_days;--30
47 procedure daily_production_rate;
48 procedure header_id;
49 procedure demand_class;
50 procedure build_sequence;
51 procedure routing_reference;
52 procedure bom_reference;
53 procedure alternate_routing_designator;
54 procedure alternate_bom_designator;
55 procedure bom_revision;
56 procedure routing_revision;--40
57 procedure bom_revision_date;
58 procedure routing_revision_date;
59 procedure lot_number;
60 procedure source_code;
61 procedure source_line_id;
62 procedure scheduling_method;
63 procedure completion_subinventory;
64 procedure completion_locator;
65 procedure priority;
66 procedure allow_explosion;
67 procedure end_item_unit_number; --must be after primary_item_id procedure
68 procedure owning_department;
69 procedure notification_required;--50
70 procedure shutdown_type;
71 procedure work_order_type;
72 procedure tagout_required;
73 procedure plan_maintenance;
74 procedure activity_type;
75 procedure activity_cause;--56
76 procedure material_issue_by_mo;
77 procedure serialization_start_op;
78 procedure maintenance_object_type;
79 procedure maintenance_object_id;
80 procedure maintenance_object_source;
81 procedure activity_source;
82 procedure pm_schedule_id;
83
84 /* public procedures */
85 procedure default_values(p_wjsi_row in out nocopy wip_job_schedule_interface%ROWTYPE) IS
86 BEGIN
87 wjsi_row := p_wjsi_row;
88 created_by_name;
89 last_updated_by_name;
90 organization;
91 job_name;
92 wip_entity_id;
93 repetitive_schedule_id;
94 kanban_card_id;
95 schedule_group;
96 line_code;
97 project_number;
98 task_number;--10
99 firm_planned_flag;
100 description;
101 status_type;
102 wip_supply_type;
103 --added for EAM
104 asset_group;--20
105 asset_number;
106 rebuild_item;
107 maintenance_object_type;
108 maintenance_object_source;
109 rebuild_serial_number;
110 maintenance_object_id;
111 pm_schedule_id;
112 parent_job_name;
113 parent_wip_entity_id;
114 --
115 class_code;
116 primary_item;
117 start_quantity;
118 net_quantity;
119 overcompletion;
120 first_unit_start_date;/* Uncommented to fix bug #5912951-FP of 5891243 */
121 -- last_unit_start_date;
122 -- first_unit_completion_date;
123 -- last_unit_completion_date;
124 due_date;
125 date_released;
126 requested_start_date;
127 processing_work_days;--30
128 daily_production_rate;
129 header_id;
130 demand_class;
131 build_sequence;
132 routing_reference;
133 bom_reference;
134 alternate_routing_designator;
135 alternate_bom_designator;
136 bom_revision;
137 routing_revision;--40
138 bom_revision_date;
139 routing_revision_date;
140 lot_number;
141 source_code;
142 source_line_id;
143 allow_explosion;
144 scheduling_method;
145 completion_subinventory;
146 completion_locator;
147 priority;
148 end_item_unit_number; --must be after primary_item_id
149 --added for EAM
150 owning_department;
151 activity_cause;--56
152 activity_source;
153 plan_maintenance;
154 notification_required;--50
155 work_order_type;
156 manual_rebuild_flag;
157 tagout_required;
158 shutdown_type;
159 activity_type;
160 --
161 material_issue_by_mo;
162 serialization_start_op;
163 p_wjsi_row := wjsi_row;
164 end default_values;
165
166 procedure default_serialization_op(p_rtgVal IN NUMBER) is
167 l_minOp NUMBER;
168 l_loadType NUMBER;
169 l_default NUMBER;
170 l_startOp NUMBER;
171 l_wipID NUMBER;
172 l_primaryItem NUMBER;
173 l_orgID NUMBER;
174 begin
175 select wp.default_serialization_start_op,
176 wjsi.load_type,
177 wjsi.serialization_start_op,
178 wjsi.wip_entity_id,
179 wjsi.primary_item_id,
180 wjsi.organization_id
181 into l_default,
182 l_loadType,
183 l_startOp,
184 l_wipID,
185 l_primaryItem,
186 l_orgID
187 from wip_parameters wp, wip_job_schedule_interface wjsi
188 where wjsi.rowid = wip_jsi_utils.current_rowid
189 and wjsi.organization_id = wp.organization_id;
190
191 if(l_startOp is not null OR
192 l_primaryItem is null) then
193 return;
194 end if;
195 --warnings are populated in default values as to provide messages to the
196 --user asap
197 if(l_loadType in (wip_constants.create_job, wip_constants.create_ns_job)) then
198 if(p_rtgVal is not null) then
199 update wip_discrete_jobs
200 set serialization_start_op = p_rtgVal
201 where wip_entity_id = l_wipID
202 and exists (select 1
203 from mtl_system_items
204 where inventory_item_id = l_primaryItem
205 and organization_id = l_orgID
206 and serial_number_control_code = wip_constants.full_sn);
207
208 elsif(l_default = wip_constants.yes) then
209 update wip_discrete_jobs
210 set serialization_start_op = (select nvl(min(operation_seq_num), 1)
211 from wip_operations
212 where wip_entity_id = l_wipID)
213 where wip_entity_id = l_wipID
214 and exists (select 1
215 from mtl_system_items
216 where inventory_item_id = l_primaryItem
217 and organization_id = l_orgID
218 and serial_number_control_code = wip_constants.full_sn);
219 end if;
220 end if;
221 end default_serialization_op;
222
223
224 /* private procedures */
225 procedure organization is
226 l_dummy NUMBER;
227 l_def_error boolean := true;
228 l_operating_unit NUMBER;
229 begin
230 --org_id defaulting
231 if(wjsi_row.organization_code is not null) then
232 if(wjsi_row.organization_id is null) then
233 select organization_id
234 into wjsi_row.organization_id
235 from mtl_parameters
236 where organization_code = wjsi_row.organization_code;
237 else
238 WIP_JSI_Utils.record_ignored_column_warning('ORGANIZATION_CODE');
239 end if;
240 end if;
241 l_def_error := false; --after this point exceptions are from validations
242
243 select to_number(org_information3) into l_operating_unit
244 from hr_organization_information
245 where organization_id = wjsi_row.organization_id
246 and org_information_context = 'Accounting Information' ;
247
248 --set the org context so future pjm validations succeed (their views are striped).
249 fnd_client_info.set_org_context(to_char(l_operating_unit));
250 exception
251 when others then
252 if(l_def_error) then
253 wip_jsi_utils.record_invalid_column_error('ORGANIZATION_CODE');
254 else
255 wip_jsi_utils.record_error('WIP_ML_ORGANIZATION_ID');
256 end if;
257 wip_jsi_utils.abort_request;
258 end organization;
259
260 procedure job_name is begin
261 --wip_entity_name defaulting
262
263 --if rescheduling job default name, warn if both name and id provided
264 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
265 if(wjsi_row.wip_entity_id is null) then
266 select wip_entity_id
267 into wjsi_row.wip_entity_id
268 from wip_entities
269 where wip_entity_name = wjsi_row.job_name
270 and organization_id = wjsi_row.organization_id;
271 elsif(wjsi_row.job_name is not null) then
272 WIP_JSI_Utils.record_ignored_column_warning('JOB_NAME');
273 end if;
274 --if rep sched, name is ignored
275 elsif(wjsi_row.load_type = wip_constants.create_sched) then
276 if(wjsi_row.job_name is not null) then
277 WIP_JSI_Utils.record_ignored_column_warning('JOB_NAME');
278 end if;
279 --when creating a job, if name isn't provided, create one
280 elsif(wjsi_row.job_name is null) then
281 /* Fix for Bug#2994658 */
282 if (wjsi_row.load_type = wip_constants.create_eam_job) then
283 select WORK_ORDER_PREFIX || wip_job_number_s.nextval
284 into wjsi_row.job_name
285 from wip_eam_parameters
286 where organization_id = wjsi_row.organization_id ;
287 else
288 select fnd_profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval
289 into wjsi_row.job_name
290 from dual;
291 end if ;
292 end if;
293 exception
294 when others then
295 wip_jsi_utils.record_invalid_column_error('JOB_NAME');
296 wip_jsi_utils.abort_request;
297 end job_name;
298
299 procedure wip_entity_id is
300 l_dummy NUMBER;
301 begin
302 if(wjsi_row.load_type = wip_constants.create_sched) then
303 if(wjsi_row.wip_entity_id is not null) then
304 wip_jsi_utils.record_ignored_column_warning('WIP_ENTITY_ID');
305 end if;
306 --if create job request, then ignore interface wip_entity and default from sequence
307 elsif(wjsi_row.load_type in (wip_constants.create_job,
308 wip_constants.create_ns_job,
309 wip_constants.create_eam_job)) then
310 if(wjsi_row.wip_entity_id is not null) then
311 wip_jsi_utils.record_ignored_column_warning('WIP_ENTITY_ID');
312 end if;
313 select wip_entities_s.nextval
314 into wjsi_row.wip_entity_id
315 from dual;
316 else --do minimal validation of wip_entity_id so future defaulting doesn't error
317 select 1
318 into l_dummy
319 from wip_entities
320 where wip_entity_id = wjsi_row.wip_entity_id
321 and organization_id = wjsi_row.organization_id;
322 end if;
323 exception
324 when others then
325 wip_jsi_utils.record_invalid_column_error('WIP_ENTITY_ID');
326 wip_jsi_utils.abort_request;
327 end wip_entity_id;
328
329 procedure schedule_group is begin
330 --schedule group defaulting
331 if(wjsi_row.load_type in (wip_constants.create_sched, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
332 if(wjsi_row.schedule_group_id is not null) then
333 wip_jsi_utils.record_ignored_column_warning('SCHEDULE_GROUP_ID');
334 end if;
335 if(wjsi_row.schedule_group_name is not null) then
336 wip_jsi_utils.record_ignored_column_warning('SCHEDULE_GROUP_NAME');
337 end if;
338 return;
339 end if;
340
341 if(wjsi_row.schedule_group_id is null) then
342 if(wjsi_row.schedule_group_name is not null) then
343 select schedule_group_id
344 into wjsi_row.schedule_group_id
345 from wip_schedule_groups_val_v
346 where schedule_group_name = wjsi_row.schedule_group_name
347 and organization_id = wjsi_row.organization_id;
348 end if;
349 else
350 wip_jsi_utils.record_ignored_column_warning('SCHEDULE_GROUP_NAME');
351 end if;
352 --if still null, default from job
353 if(wjsi_row.load_type = wip_constants.resched_job and
354 wjsi_row.schedule_group_id is null) then
355 begin
356 select schedule_group_id
357 into wjsi_row.schedule_group_id
358 from wip_discrete_jobs
359 where wip_entity_id = wjsi_row.wip_entity_id
360 and organization_id = wjsi_row.organization_id;
361 exception
362 when others then null;
363 end;
364 end if;
365 -- if still null and loading from CTO, insert new group
366 if(wjsi_row.schedule_group_id is null and
367 wjsi_row.source_code = 'WICDOL' and
368 wjsi_row.delivery_id is not null) then
369 begin
370 select schedule_group_id
371 into wjsi_row.schedule_group_id
372 from wip_schedule_groups wsg,
373 wsh_new_deliveries wds
374 where wds.delivery_id = wjsi_row.delivery_id
375 and wsg.schedule_group_name = wds.name
376 and wsg.organization_id = wjsi_row.organization_id;
377 exception
378 when no_data_found then
379 --having problems using dml returning...
380 select wip_schedule_groups_s.nextval
381 into wjsi_row.schedule_group_id
382 from dual;
383
384 insert into wip_schedule_groups (
385 schedule_group_id,
386 schedule_group_name,
387 organization_id,
388 description,
389 created_by,
390 last_updated_by,
391 creation_date,
392 last_update_date)
393 select wjsi_row.schedule_group_id,
394 wds.name,
395 wjsi_row.organization_id,
396 to_char(sysdate),
397 fnd_global.user_id,
398 fnd_global.user_id,
399 sysdate,
400 sysdate
401 from wsh_new_deliveries wds
402 where wds.delivery_id = wjsi_row.delivery_id;
403 end;
404 end if;
405 exception
406 when others then
407 wip_jsi_utils.record_invalid_column_error('SCHEDULE_GROUP_NAME');
408 wip_jsi_utils.abort_request;
409 end schedule_group;
410
411 --line code defaulting
412 procedure line_code is begin
413 if(wjsi_row.line_id is null and wjsi_row.line_code is not null) then
414 select line_id
415 into wjsi_row.line_id
416 from wip_lines_val_v
417 where line_code = wjsi_row.line_code
418 and organization_id = wjsi_row.organization_id;
419 elsif(wjsi_row.line_id is not null and wjsi_row.line_code is not null) then
420 WIP_JSI_Utils.record_ignored_column_warning('LINE_CODE');
421 end if;
422 exception
423 when others then
424 wip_jsi_utils.record_invalid_column_error('LINE_CODE');
425 wip_jsi_utils.abort_request;
426 end line_code;
427
428 procedure project_number is begin
429 if(wjsi_row.load_type = wip_constants.create_sched) then
430 if(wjsi_row.project_number is not null) then
431 WIP_JSI_Utils.record_ignored_column_warning('PROJECT_NUMBER');
432 end if;
433 if(wjsi_row.project_id is not null) then
434 WIP_JSI_Utils.record_ignored_column_warning('PROJECT_ID');
435 end if;
436 elsif(wjsi_row.project_number is not null and wjsi_row.project_id is null) then
437 -- fix MOAC, set id so project view works
438 fnd_profile.put('MFG_ORGANIZATION_ID',wjsi_row.organization_id);
439 select pjm_project.val_proj_numtoid(wjsi_row.project_number, wjsi_row.organization_id)
440 into wjsi_row.project_id
441 from dual;
442 elsif(wjsi_row.load_type not in (wip_constants.resched_job, wip_constants.resched_eam_job) and
443 wjsi_row.task_number is not null
444 and wjsi_row.task_id is not null) then
445 raise fnd_api.g_exc_unexpected_error;
446 end if;
447 exception
448 when others then
449 wip_jsi_utils.record_invalid_column_error('PROJECT_NUMBER');
450 wip_jsi_utils.abort_request;
451 end project_number;
452
453 procedure task_number is begin
454 if(wjsi_row.load_type = wip_constants.create_sched) then
455 if(wjsi_row.task_number is not null) then
456 WIP_JSI_Utils.record_ignored_column_warning('TASK_NUMBER');
457 end if;
458 if(wjsi_row.task_id is not null) then
459 WIP_JSI_Utils.record_ignored_column_warning('TASK_ID');
460 end if;
461 elsif(wjsi_row.task_number is not null and wjsi_row.task_id is null) then
462 if(wjsi_row.load_type = wip_constants.resched_job) then
463 select pa.task_id
464 into wjsi_row.task_id
465 from pa_tasks_expend_v pa, wip_discrete_jobs wdj
466 where wdj.wip_entity_id = wjsi_row.wip_entity_id
467 and pa.project_id = nvl(wjsi_row.project_id, wdj.project_id)
468 and pa.task_number = wjsi_row.task_number;
469 else
470 select task_id
471 into wjsi_row.task_id
472 from pa_tasks_expend_v
473 where project_id = wjsi_row.project_id
474 and task_number = wjsi_row.task_number;
475 end if;
476 elsif(wjsi_row.task_number is not null and wjsi_row.task_id is not null) then
477 wip_jsi_utils.record_ignored_column_warning('TASK_NUMBER');
478 end if;
479 exception
480 when others then
481 wip_jsi_utils.record_invalid_column_error('TASK_NUMBER');
482 wip_jsi_utils.abort_request;
483 end task_number;
484
485 procedure firm_planned_flag is begin
486 if(wjsi_row.firm_planned_flag is null and
487 wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
488 wip_constants.create_ns_job, wip_constants.create_eam_job)) then
489 wjsi_row.firm_planned_flag := wip_constants.no;
490 end if;
491 end firm_planned_flag;
492
493 procedure demand_class is begin
494 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
495 wjsi_row.demand_class is not null) then
496 wip_jsi_utils.record_ignored_column_warning('DEMAND_CLASS');
497 end if;
498 end demand_class;
499
500 procedure description is begin
501 if(wjsi_row.description is null) then
502 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job)) then
503 fnd_message.set_name('WIP','WIP_MLD_DESC');
504 fnd_message.set_token('LOAD_DATE', fnd_date.date_to_charDT(dateval => sysdate,calendar_aware => 2), false);
505 wjsi_row.description := fnd_message.get;
506 elsif(wjsi_row.load_type = wip_constants.create_sched) then
507 fnd_message.set_name('WIP','WIP_MLR_DESC');
508 fnd_message.set_token('LOAD_DATE', fnd_date.date_to_charDT(dateval => sysdate,calendar_aware => 2), false);
509 wjsi_row.description := fnd_message.get;
510 end if;
511 end if;
512 end description;
513
514 procedure build_sequence is begin
515 if(wjsi_row.load_type = wip_constants.create_sched and
516 wjsi_row.build_sequence is not null) then
517 wip_jsi_utils.record_ignored_column_warning('BUILD_SEQUENCE');
518 end if;
519 end build_sequence;
520
521 procedure status_type is begin
522 if(wjsi_row.load_type = wip_constants.create_sched and wjsi_row.status_type is not null) then
523 wip_jsi_utils.record_ignored_column_warning('STATUS_TYPE');
524 elsif(wjsi_row.status_type is null and wjsi_row.load_type in (wip_constants.create_job,
525 wip_constants.create_ns_job, wip_constants.create_eam_job)) then
526 wjsi_row.status_type := wip_constants.unreleased;
527 elsif (wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
528 wjsi_row.status_type is null) then
529 select wdj.status_type
530 into wjsi_row.status_type
531 from wip_discrete_jobs wdj
532 where wdj.wip_entity_id = wjsi_row.wip_entity_id
533 and wdj.organization_id = wjsi_row.organization_id;
534 end if;
535 end status_type;
536
537 procedure processing_work_days is begin
538 if(wjsi_row.load_type <> wip_constants.create_sched and
539 wjsi_row.processing_work_days is not null) then
540 wip_jsi_utils.record_ignored_column_warning('PROCESSING_WORK_DAYS');
541 end if;
542 end processing_work_days;
543
544 procedure daily_production_rate is begin
545 if(wjsi_row.load_type <> wip_constants.create_sched and
546 wjsi_row.daily_production_rate is not null) then
547 wip_jsi_utils.record_ignored_column_warning('DAILY_PRODUCTION_RATE');
548 end if;
549 end daily_production_rate;
550
551 procedure repetitive_schedule_id is begin
552 if(wjsi_row.load_type = wip_constants.create_sched) then
553 if(wjsi_row.repetitive_schedule_id is null) then
554 select wip_repetitive_schedules_s.nextval
555 into wjsi_row.repetitive_schedule_id
556 from dual;
557 end if;
558 elsif(wjsi_row.repetitive_schedule_id is not null) then
559 wip_jsi_utils.record_ignored_column_warning('REPETITIVE_SCHEDULE_ID');
560 end if;
561 end repetitive_schedule_id;
562
563 procedure kanban_card_id is
564 l_raw_job WIP_Work_Order_Pub.DiscreteJob_Rec_Type ;
565 l_defaulted_job WIP_Work_Order_Pub.DiscreteJob_Rec_Type ;
566 l_raw_sched WIP_Work_Order_Pub.RepSchedule_Rec_Type ;
567 l_defaulted_sched WIP_Work_Order_Pub.RepSchedule_Rec_Type ;
568 begin
569 if(wjsi_row.kanban_card_id is null or wip_jsi_utils.validation_level <> wip_constants.inv) then
570 return;
571 end if;
572
573
574 if(wjsi_row.load_type = wip_constants.create_job) then
575 l_raw_job := WIP_Work_Order_Pub.G_MISS_DISCRETEJOB_REC ;
576
577 l_raw_job.organization_id := wjsi_row.organization_id ;
578 l_raw_job.kanban_card_id := wjsi_row.kanban_card_id ;
579 l_raw_job.primary_item_id := nvl(wjsi_row.primary_item_id, l_raw_job.primary_item_id) ;
580 l_raw_job.completion_subinventory := nvl(wjsi_row.completion_subinventory, l_raw_job.completion_subinventory) ;
581 l_raw_job.completion_locator_id := nvl(wjsi_row.completion_locator_id, l_raw_job.completion_locator_id) ;
582 l_raw_job.start_quantity := nvl(wjsi_row.start_quantity, l_raw_job.start_quantity) ;
583
584 l_raw_job.action := WIP_Globals.G_OPR_DEFAULT_USING_KANBAN ;
585
586 WIP_Default_DiscreteJob.attributes(p_discreteJob_rec => l_raw_job,
587 x_discreteJob_rec => l_defaulted_job,
588 p_redefault => false);
589
590 l_defaulted_job := WIP_DiscreteJob_Util.convert_miss_to_null(l_defaulted_job) ;
591 wjsi_row.primary_item_id := l_defaulted_job.primary_item_id;
592 wjsi_row.completion_subinventory := l_defaulted_job.completion_subinventory;
593 wjsi_row.completion_locator_id := l_defaulted_job.completion_locator_id;
594 wjsi_row.start_quantity := l_defaulted_job.start_quantity;
595 elsif(wjsi_row.load_type = wip_constants.create_sched) then
596 l_raw_sched := WIP_Work_Order_Pub.G_MISS_REPSCHEDULE_REC ;
597
598 l_raw_sched.organization_id := wjsi_row.organization_id ;
599 l_raw_sched.kanban_card_id := wjsi_row.kanban_card_id ;
600 l_raw_sched.line_id := nvl(wjsi_row.line_id, l_raw_sched.line_id) ;
601 l_raw_sched.processing_work_days := nvl(wjsi_row.processing_work_days, l_raw_sched.processing_work_days) ;
602 l_raw_sched.first_unit_cpl_date := nvl(wjsi_row.first_unit_completion_date, l_raw_sched.first_unit_cpl_date) ;
603 l_raw_sched.daily_production_rate := nvl(wjsi_row.daily_production_rate, l_raw_sched.daily_production_rate) ;
604 l_raw_sched.action := WIP_Globals.G_OPR_DEFAULT_USING_KANBAN;
605
606 WIP_Default_RepSchedule.attributes(p_RepSchedule_rec => l_raw_sched,
607 x_RepSchedule_rec => l_defaulted_sched,
608 p_redefault => false);
609
610 l_defaulted_sched := WIP_RepSchedule_Util.convert_miss_to_null(l_defaulted_sched) ;
611 wjsi_row.line_id := l_defaulted_sched.line_id;
612 wjsi_row.processing_work_days := l_defaulted_sched.processing_work_days;
613 wjsi_row.first_unit_completion_date := l_defaulted_sched.first_unit_cpl_date;
614 wjsi_row.daily_production_rate := l_defaulted_sched.daily_production_rate;
615 else
616 -- A kanban reference makes sense only on a standard job or schedule
617 -- creation request.
618 raise fnd_api.g_exc_unexpected_error;
619 end if ;
620 exception when others then
621 wip_jsi_utils.record_error('WIP_ML_BAD_KB_LOAD') ;
622 wip_jsi_utils.abort_request ;
623 end kanban_card_id ;
624
625 procedure primary_item is begin
626 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
627 if(wjsi_row.primary_item_segments is not null) then
628 wip_jsi_utils.record_ignored_column_warning('PRIMARY_ITEM_SEGMENTS');
629 end if;
630 if(wjsi_row.primary_item_id is not null) then
631 wip_jsi_utils.record_ignored_column_warning('PRIMARY_ITEM_ID');
632 end if;
633 elsif(wjsi_row.primary_item_segments is not null) then
634 if(wjsi_row.primary_item_id is null) then
635 select inventory_item_id
636 into wjsi_row.primary_item_id
637 from mtl_system_items_kfv
638 where concatenated_segments = wjsi_row.primary_item_segments
639 and organization_id = wjsi_row.organization_id;
640 else
641 wip_jsi_utils.record_ignored_column_warning('PRIMARY_ITEM_SEGMENTS');
642 end if;
643 end if;
644 exception
645 when others then
646 wip_jsi_utils.record_invalid_column_error('PRIMARY_ITEM_SEGMENTS');
647 wip_jsi_utils.abort_request;
648 end primary_item;
649
650 procedure start_quantity is begin
651 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
652 if(wjsi_row.start_quantity is not null) then
653 wip_jsi_utils.record_ignored_column_warning('START_QUANTITY');
654 end if;
655 wjsi_row.start_quantity := 1;
656 elsif(wjsi_row.load_type = wip_constants.create_sched and
657 wjsi_row.start_quantity is not null) then
658 wip_jsi_utils.record_ignored_column_warning('START_QUANTITY');
659 end if;
660 end start_quantity;
661
662 procedure net_quantity is begin
663 if(wjsi_row.net_quantity is null) then
664 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job)) then
665 wjsi_row.net_quantity := wjsi_row.start_quantity;
666 elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
667 select decode(wdj.net_quantity,
668 wdj.start_quantity, wjsi_row.start_quantity,
669 least(wdj.net_quantity, nvl(wjsi_row.start_quantity, wdj.net_quantity)))
670 into wjsi_row.net_quantity
671 from wip_discrete_jobs wdj
672 where wdj.wip_entity_id = wjsi_row.wip_entity_id
673 and wdj.organization_id = wjsi_row.organization_id;
674 elsif(wjsi_row.load_type = wip_constants.create_ns_job) then
675 wjsi_row.net_quantity := 0;
676 end if;
677 else
678 if(wjsi_row.load_type = wip_constants.create_sched) then
679 wip_jsi_utils.record_ignored_column_warning('NET_QUANTITY');
680 end if;
681 end if;
682 exception
683 when others then
684 wip_jsi_utils.record_invalid_column_error('NET_QUANTITY');
685 wip_jsi_utils.abort_request;
686 end net_quantity;
687
688 procedure overcompletion is
689 l_tolType NUMBER;
690 l_tolValue NUMBER;
691 l_primaryItemId NUMBER;
692 begin
693 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job, wip_constants.create_sched)) then
694 if(wjsi_row.overcompletion_tolerance_type is not null) then
695 wip_jsi_utils.record_ignored_column_warning ('OVERCOMPLETION_TOLERANCE_TYPE');
696 end if;
697 if(wjsi_row.overcompletion_tolerance_value is not null) then
698 wip_jsi_utils.record_ignored_column_warning ('OVERCOMPLETION_TOLERANCE_VALUE');
699 end if;
700 elsif(wjsi_row.load_type = wip_constants.create_ns_job and
701 wjsi_row.primary_item_id is null) then
702 if(wjsi_row.overcompletion_tolerance_type is not null) then
703 wip_jsi_utils.record_ignored_column_warning ('OVERCOMPLETION_TOLERANCE_TYPE');
704 end if;
705 if(wjsi_row.overcompletion_tolerance_value is not null) then
706 wip_jsi_utils.record_ignored_column_warning ('OVERCOMPLETION_TOLERANCE_VALUE');
707 end if;
708 else
709 if(wjsi_row.load_type = wip_constants.resched_job) then
710 select overcompletion_tolerance_type, overcompletion_tolerance_value, primary_item_id
711 into l_tolType, l_tolValue, l_primaryItemId
712 from wip_discrete_jobs
713 where wip_entity_id = wjsi_row.wip_entity_id;
714 if(wjsi_row.overcompletion_tolerance_type is not null) then
715 wjsi_row.overcompletion_tolerance_type := l_tolType;
716 end if;
717 if(wjsi_row.overcompletion_tolerance_value is not null) then
718 wjsi_row.overcompletion_tolerance_value := l_tolValue;
719 end if;
720 else --job creation
721 l_primaryItemId := wjsi_row.primary_item_id;
722 end if;
723 if(wjsi_row.overcompletion_tolerance_type is null and
724 wjsi_row.overcompletion_tolerance_value is null and
725 l_primaryItemId is not null) then
726 --the only way this procedure error is if it can't find the item...thus
727 --the invalid assembly error below
728 WIP_Overcompletion.get_tolerance_default (p_primary_item_id => l_primaryItemId,
729 p_org_id => wjsi_row.organization_id,
730 p_tolerance_type => wjsi_row.overcompletion_tolerance_type,
731 p_tolerance_value => wjsi_row.overcompletion_tolerance_value);
732 end if;
733 end if;
734 exception
735 when others then
736 wip_jsi_utils.record_error('WIP_ML_PRIMARY_ITEM_ID');
737 wip_jsi_utils.abort_request;
738 end overcompletion;
739
740 procedure wip_supply_type is begin
741 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
742 if(wjsi_row.wip_supply_type is null) then
743 wjsi_row.wip_supply_type := wip_constants.based_on_bom;
744 end if;
745 elsif(wjsi_row.wip_supply_type is not null) then
746 wip_jsi_utils.record_ignored_column_warning ('WIP_SUPPLY_TYPE');
747 end if;
748 end wip_supply_type;
749
750 procedure class_code is
751 l_entityType NUMBER;
752 l_errMsg1 VARCHAR2(30);
753 l_errMsg2 VARCHAR2(30);
754 l_errClass1 VARCHAR2(30);
755 l_errClass2 VARCHAR2(30);
756 l_returnMsg VARCHAR2(200);
757 l_asset_number VARCHAR2(30);
758 l_asset_group_id NUMBER;
759 l_job_type NUMBER;
760 begin
761 l_errMsg1 := null;
762 l_errMsg2 := null;
763
764 if(wjsi_row.class_code is not null) then
765 return;
766 end if;
767
768 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job)) then
769 if(wjsi_row.load_type = wip_constants.create_job) then
770 l_entityType := wip_constants.discrete;
771 wjsi_row.class_code := wip_common.default_acc_class(x_org_id => wjsi_row.organization_id,
772 x_item_id => wjsi_row.primary_item_id,
773 x_entity_type => l_entityType,
774 x_project_id => wjsi_row.project_id,
775 x_err_mesg_1 => l_errMsg1,
776 x_err_mesg_2 => l_errMsg2,
777 x_err_class_1 => l_errClass1,
778 x_err_class_2 => l_errClass2);
779 else
780 l_entityType := wip_constants.eam;
781
782 if (wjsi_row.rebuild_item_id is null) then
783 l_job_type := 1; -- maintenance WO
784 l_asset_number := wjsi_row.asset_number;
785 l_asset_group_id := wjsi_row.asset_group_id;
786 else
787 l_job_type := 2; -- rebuild WO
788 l_asset_number := wjsi_row.rebuild_serial_number;
789 l_asset_group_id := wjsi_row.rebuild_item_id;
790 end if;
791
792 wip_eam_utils.default_acc_class( p_org_id => wjsi_row.organization_id,
793 p_job_type => l_job_type,
794 p_serial_number => l_asset_number,
795 p_asset_group_id => l_asset_group_id,
796 p_parent_wo_id => wjsi_row.parent_wip_entity_id,
797 p_asset_activity_id => wjsi_row.primary_item_id, -- Asset Activity
798 p_project_id => wjsi_row.project_id,
799 p_task_id => wjsi_row.task_id,
800 x_class_code => wjsi_row.class_code, -- WAC (return value)
801 x_return_status => l_returnMsg, -- Return Status
802 x_msg_data => l_errMsg1); -- Error messages
803 end if;
804 if (l_errMsg1 is not null) then
805 fnd_message.set_name('WIP', l_errMsg1);
806 fnd_message.set_token('class_code', l_errClass1, false);
807 wip_jsi_utils.record_current_error ;
808 end if ;
809 if (l_errMsg2 is not null) then
810 fnd_message.set_name('WIP', l_errMsg2);
811 fnd_message.set_token('class_code', l_errClass2, false);
812 wip_jsi_utils.record_current_error ;
813 end if ;
814 elsif(wjsi_row.load_type in (wip_constants.resched_job,
815 wip_constants.resched_eam_job,
816 wip_constants.create_sched)) then
817 if (wjsi_row.class_code is not null) then
818 wip_jsi_utils.record_ignored_column_warning ('CLASS_CODE');
819 return;
820 end if;
821
822 if (wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
823 wjsi_row.class_code is NULL) then
824 select class_code
825 into wjsi_row.class_code
826 from wip_discrete_jobs
827 where wip_entity_id = wjsi_row.wip_entity_id
828 and organization_id = wjsi_row.organization_id;
829 end if;
830 end if;
831 end class_code;
832
833 procedure routing_reference is begin
834 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
835 wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
836 if(wjsi_row.routing_reference_segments is not null) then
837 wip_jsi_utils.record_ignored_column_warning ('ROUTING_REFERENCE_SEGMENTS');
838 end if;
839 if(wjsi_row.routing_reference_id is not null) then
840 wip_jsi_utils.record_ignored_column_warning ('ROUTING_REFERENCE_ID');
841 end if;
842 elsif(wjsi_row.routing_reference_segments is not null) then
843 if(wjsi_row.routing_reference_id is null) then
844 select inventory_item_id
845 into wjsi_row.routing_reference_id
846 from mtl_system_items_kfv
847 where concatenated_segments = wjsi_row.routing_reference_segments
848 and organization_id = wjsi_row.organization_id;
849 else
850 wip_jsi_utils.record_ignored_column_warning('ROUTING_REFERENCE_SEGMENTS');
851 end if;
852 end if;
853 exception
854 when others then
855 wip_jsi_utils.record_invalid_column_error('ROUTING_REFERENCE_SEGMENTS');
856 wip_jsi_utils.abort_request;
857 end routing_reference;
858
859 procedure bom_reference is begin
860 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
861 wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
862 if(wjsi_row.bom_reference_segments is not null) then
863 wip_jsi_utils.record_ignored_column_warning ('BOM_REFERENCE_SEGMENTS');
864 end if;
865 if(wjsi_row.bom_reference_id is not null) then
866 wip_jsi_utils.record_ignored_column_warning ('BOM_REFERENCE_ID');
867 end if;
868 elsif(wjsi_row.bom_reference_segments is not null) then
869 if(wjsi_row.bom_reference_id is null) then
870 select inventory_item_id
871 into wjsi_row.bom_reference_id
872 from mtl_system_items_kfv
873 where concatenated_segments = wjsi_row.bom_reference_segments
874 and organization_id = wjsi_row.organization_id;
875 else
876 wip_jsi_utils.record_ignored_column_warning('BOM_REFERENCE_SEGMENTS');
877 end if;
878 end if;
879 exception
880 when others then
881 wip_jsi_utils.record_invalid_column_error('BOM_REFERENCE_SEGMENTS');
882 wip_jsi_utils.abort_request;
883 end bom_reference;
884
885 procedure alternate_routing_designator is begin
886 if(wjsi_row.load_type in (wip_constants.create_sched, wip_constants.resched_job, wip_constants.resched_eam_job) and
887 wjsi_row.alternate_routing_designator is not null) then
888 wip_jsi_utils.record_ignored_column_warning ('ALTERNATE_ROUTING_DESIGNATOR');
889 end if;
890 end alternate_routing_designator;
891
892 procedure alternate_bom_designator is begin
893 if(wjsi_row.load_type in (wip_constants.create_sched, wip_constants.resched_job, wip_constants.resched_eam_job) and
894 wjsi_row.alternate_bom_designator is not null) then
895 wip_jsi_utils.record_ignored_column_warning ('ALTERNATE_BOM_DESIGNATOR');
896 end if;
897 end alternate_bom_designator;
898
899 procedure bom_revision is begin
900 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
901 wjsi_row.bom_revision is not null) then
902 wip_jsi_utils.record_ignored_column_warning('BOM_REVISION');
903 elsif(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
904 if(wjsi_row.bom_revision_date is not null and wjsi_row.bom_revision is not null) then
905 wip_jsi_utils.record_ignored_column_warning('BOM_REVISION');
906 end if;
907 end if;
908 end bom_revision;
909
910 procedure bom_revision_date is begin
911 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
912 wjsi_row.bom_revision_date is not null) then
913 wip_jsi_utils.record_ignored_column_warning('BOM_REVISION_DATE');
914 end if;
915 end bom_revision_date;
916
917 procedure routing_revision is begin
918 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
919 wjsi_row.routing_revision is not null) then
920 wip_jsi_utils.record_ignored_column_warning('ROUTING_REVISION');
921 elsif(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
922 if(wjsi_row.routing_revision_date is not null and wjsi_row.routing_revision is not null) then
923 wip_jsi_utils.record_ignored_column_warning('ROUTING_REVISION');
924 end if;
925 end if;
926 end routing_revision;
927
928 procedure routing_revision_date is begin
929 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
930 wjsi_row.routing_revision_date is not null) then
931 wip_jsi_utils.record_ignored_column_warning('ROUTING_REVISION_DATE');
932 end if;
933 end routing_revision_date;
934
935 procedure lot_number is
936 l_primary_item_id NUMBER;
937 l_wip_name VARCHAR2(240);
938 begin
939 if(wjsi_row.load_type = wip_constants.create_sched and
940 wjsi_row.lot_number is not null) then
941 wip_jsi_utils.record_ignored_column_warning('LOT_NUMBER');
942 elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
943 select primary_item_id, wip_entity_name
944 into l_primary_item_id, l_wip_name
945 from wip_entities
946 where wip_entity_id = wjsi_row.wip_entity_id;
947
948 wjsi_row.lot_number := wip_lot_number_default.lot_number(p_item_id => l_primary_item_id,
949 p_organization_id => wjsi_row.organization_id,
950 p_lot_number => wjsi_row.lot_number,
951 p_job_name => l_wip_name,
952 p_default_flag => 0);
953 else --job creation
954 wjsi_row.lot_number := wip_lot_number_default.lot_number(p_item_id => wjsi_row.primary_item_id,
955 p_organization_id => wjsi_row.organization_id,
956 p_lot_number => wjsi_row.lot_number,
957 p_job_name => wjsi_row.job_name,
958 p_default_flag => 1);
959
960 end if;
961 exception
962 when others then
963 wip_jsi_utils.record_invalid_column_error('LOT_NUMBER');
964 wip_jsi_utils.abort_request;
965 end lot_number;
966
967 procedure source_code is begin
968 if(wjsi_row.load_type = wip_constants.create_sched and
969 wjsi_row.source_code is not null) then
970 WIP_JSI_Utils.Record_Ignored_Column_Warning ('SOURCE_CODE');
971 end if;
972 end source_code;
973
974 procedure source_line_id is begin
975 if(wjsi_row.load_type = wip_constants.create_sched and
976 wjsi_row.source_line_id is not null) then
977 WIP_JSI_Utils.Record_Ignored_Column_Warning ('SOURCE_LINE_ID');
978 end if;
979 end source_line_id;
980
981 procedure first_unit_start_date is begin
982
983 /* Fix for Bug#5912951-FP of 5891243. Following if condition needs to be
984 commented out as this will happen in scheduling_dates in wipjsivb.pls
985
986 if(wjsi_row.first_unit_start_date is null and
987 wjsi_row.load_type = wip_constants.create_sched) then
988 --if no routing used work days and last unit comp date to determine fusd
989 select calendar_date
990 into wjsi_row.first_unit_start_date
991 from bom_calendar_dates bcd, mtl_parameters mp
992 where mp.organization_id = wjsi_row.organization_id
993 and bcd.exception_set_id = mp.calendar_exception_set_id
994 and bcd.calendar_code = mp.calendar_code
995 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
996 from bom_calendar_dates b2
997 where b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
998 and b2.calendar_code = mp.calendar_code
999 and b2.exception_set_id = bcd.exception_set_id)
1000 and (exists (select 1
1001 from wip_lines wl
1002 where wl.line_id = wjsi_row.line_id
1003 and wl.line_schedule_type = 2))
1004 and (not exists (select 1
1005 from bom_operational_routings bor,
1006 wip_repetitive_items wri
1007 where wri.line_id = wjsi_row.line_id
1008 and nvl(bor.cfm_routing_flag,2) = 2
1009 and wri.primary_item_id = wjsi_row.primary_item_id
1010 and wri.organization_id = wjsi_row.organization_id
1011 and nvl(bor.alternate_routing_designator,'@@') =
1012 nvl(wri.alternate_routing_designator,'@@')
1013 and bor.organization_id = wri.organization_id
1014 and bor.assembly_item_id = wri.primary_item_id));
1015 end if;
1016 */
1017
1018 /* Bug 5912951-FP of 5891243:
1019 Populate original job start date as fusd. This will ensure that completed operation
1020 dates are updated as original job start date in update_routing procedure in wipschdb.pls .
1021 */
1022 if (wjsi_row.source_code = 'MSC' and
1023 wjsi_row.load_type = wip_constants.resched_job and
1024 wjsi_row.scheduling_method = wip_constants.ml_manual) then
1025 select wdj.scheduled_start_date
1026 into wjsi_row.first_unit_start_date
1027 from wip_discrete_jobs wdj
1028 where wdj.wip_entity_id = wjsi_row.wip_entity_id
1029 and wdj.organization_id = wjsi_row.organization_id
1030 and exists ( select operation_seq_num
1031 from wip_operations wo
1032 where wo.wip_entity_id = wdj.wip_entity_id and
1033 wo.organization_id = wdj.organization_id
1034 minus
1035 select operation_seq_num
1036 from wip_job_dtls_interface
1037 where group_id = wjsi_row.group_id
1038 and parent_header_id = wjsi_row.header_id
1039 and load_type = WIP_JOB_DETAILS.WIP_OPERATION
1040 ) ;
1041
1042 end if;
1043
1044 exception
1045 when no_data_found then
1046 null;
1047 when others then
1048 wip_jsi_utils.record_invalid_column_error('FIRST_UNIT_START_DATE');
1049 wip_jsi_utils.abort_request;
1050 end first_unit_start_date;
1051
1052 procedure last_unit_start_date is begin
1053 if(wjsi_row.last_unit_start_date is null and
1054 wjsi_row.load_type = wip_constants.create_sched) then
1055 --if no routing used work days and first unit comp date to determine fusd
1056 select calendar_date
1057 into wjsi_row.last_unit_start_date
1058 from bom_calendar_dates bcd, mtl_parameters mp
1059 where mp.organization_id = wjsi_row.organization_id
1060 and bcd.exception_set_id = mp.calendar_exception_set_id
1061 and bcd.calendar_code = mp.calendar_code
1062 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
1063 from bom_calendar_dates b2
1064 where b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
1065 and b2.calendar_code = mp.calendar_code
1066 and b2.exception_set_id = bcd.exception_set_id)
1067 and (exists (select 1
1068 from wip_lines wl
1069 where wl.line_id = wjsi_row.line_id
1070 and wl.line_schedule_type = 2))
1071 and (not exists (select 1
1072 from bom_operational_routings bor,
1073 wip_repetitive_items wri
1074 where wri.line_id = wjsi_row.line_id
1075 and nvl(bor.cfm_routing_flag,2) = 2
1076 and wri.primary_item_id = wjsi_row.primary_item_id
1077 and wri.organization_id = wjsi_row.organization_id
1078 and nvl(bor.alternate_routing_designator,'@@') =
1079 nvl(wri.alternate_routing_designator,'@@')
1080 and bor.organization_id = wri.organization_id
1081 and bor.assembly_item_id = wri.primary_item_id));
1082 end if;
1083 exception
1084 when no_data_found then
1085 wip_jsi_utils.record_ignored_column_warning('LAST_UNIT_START_DATE');
1086 null;
1087 when others then
1088 wip_jsi_utils.record_invalid_column_error('LAST_UNIT_START_DATE');
1089 wip_jsi_utils.abort_request;
1090 end last_unit_start_date;
1091
1092 procedure first_unit_completion_date is begin
1093 if(wjsi_row.first_unit_completion_date is null and
1094 wjsi_row.load_type = wip_constants.create_sched) then
1095 select calendar_date
1096 into wjsi_row.first_unit_completion_date
1097 from bom_calendar_dates bcd, mtl_parameters mp
1098 where mp.organization_id = wjsi_row.organization_id
1099 and bcd.exception_set_id = mp.calendar_exception_set_id
1100 and bcd.calendar_code = mp.calendar_code
1101 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
1102 from bom_calendar_dates b2
1103 where b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
1104 and b2.calendar_code = mp.calendar_code
1105 and b2.exception_set_id = bcd.exception_set_id)
1106 and (exists (select 1
1107 from wip_lines wl
1108 where wl.line_id = wjsi_row.line_id
1109 and wl.line_schedule_type = 2))
1110 and (not exists (select 1
1111 from bom_operational_routings bor,
1112 wip_repetitive_items wri
1113 where wri.line_id = wjsi_row.line_id
1114 and nvl(bor.cfm_routing_flag,2) = 2
1115 and wri.primary_item_id = wjsi_row.primary_item_id
1116 and wri.organization_id = wjsi_row.organization_id
1117 and nvl(bor.alternate_routing_designator,'@@') =
1118 nvl(wri.alternate_routing_designator,'@@')
1119 and bor.organization_id = wri.organization_id
1120 and bor.assembly_item_id = wri.primary_item_id));
1121 end if;
1122 exception
1123 when no_data_found then
1124 null;
1125 when others then
1126 wip_jsi_utils.record_invalid_column_error('FIRST_UNIT_COMPLETION_DATE');
1127 wip_jsi_utils.abort_request;
1128 end first_unit_completion_date;
1129
1130 procedure last_unit_completion_date is begin
1131 if(wjsi_row.last_unit_completion_date is null and
1132 wjsi_row.load_type = wip_constants.create_sched) then
1133 select calendar_date
1134 into wjsi_row.last_unit_completion_date
1135 from bom_calendar_dates bcd, mtl_parameters mp
1136 where mp.organization_id = wjsi_row.organization_id
1137 and bcd.exception_set_id = mp.calendar_exception_set_id
1138 and bcd.calendar_code = mp.calendar_code
1139 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
1140 from bom_calendar_dates b2
1141 where b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
1142 and b2.calendar_code = mp.calendar_code
1143 and b2.exception_set_id = bcd.exception_set_id)
1144 and (exists (select 1
1145 from wip_lines wl
1146 where wl.line_id = wjsi_row.line_id
1147 and wl.line_schedule_type = 2))
1148 and (not exists (select 1
1149 from bom_operational_routings bor,
1150 wip_repetitive_items wri
1151 where wri.line_id = wjsi_row.line_id
1152 and nvl(bor.cfm_routing_flag,2) = 2
1153 and wri.primary_item_id = wjsi_row.primary_item_id
1154 and wri.organization_id = wjsi_row.organization_id
1155 and nvl(bor.alternate_routing_designator,'@@') =
1156 nvl(wri.alternate_routing_designator,'@@')
1157 and bor.organization_id = wri.organization_id
1158 and bor.assembly_item_id = wri.primary_item_id));
1159 end if;
1160 exception
1161 when no_data_found then
1162 null;
1163 when others then
1164 wip_jsi_utils.record_invalid_column_error('LAST_UNIT_COMPLETION_DATE');
1165 wip_jsi_utils.abort_request;
1166 end last_unit_completion_date;
1167
1168 --if routing based line and item w/no routing is used, default the other values. conditions are necessary
1169 --b/c it is an error to provide the combination of dates listed in the if stmt.
1170 procedure schedule_dates is begin
1171 first_unit_start_date;
1172 first_unit_completion_date;
1173 last_unit_start_date;
1174 last_unit_completion_date;
1175 end schedule_dates;
1176
1177 procedure allow_explosion is begin
1178 if(wjsi_row.load_type = wip_constants.create_sched and
1179 wjsi_row.allow_explosion is not null) then
1180 wip_jsi_utils.record_ignored_column_warning('ALLOW_EXPLOSION');
1181 end if;
1182 end allow_explosion;
1183
1184 procedure scheduling_method is begin
1185 if(wjsi_row.load_type = wip_constants.create_sched and
1186 wjsi_row.scheduling_method is not null) then
1187 wip_jsi_utils.record_ignored_column_warning('SCHEDULING_METHOD');
1188 elsif(wjsi_row.scheduling_method is null) then
1189 if(wjsi_row.allow_explosion is null or
1190 upper(wjsi_row.allow_explosion) <> 'N') then
1191 wjsi_row.scheduling_method := wip_constants.routing;
1192 else
1193 wjsi_row.scheduling_method := wip_constants.ml_manual;
1194 end if;
1195 end if;
1196 end scheduling_method;
1197
1198 procedure completion_subinventory is begin
1199 if(wjsi_row.completion_subinventory is null) then
1200 if((wjsi_row.load_type = wip_constants.create_job) or
1201 (wjsi_row.load_type = wip_constants.create_eam_job and --for eam, comp sub in valid for rebuild jobs only
1202 (wjsi_row.rebuild_item_id is not null or wjsi_row.rebuild_item_segments is not null))) then
1203 select bor.completion_subinventory
1204 into wjsi_row.completion_subinventory
1205 from bom_operational_routings bor
1206 where bor.organization_id = wjsi_row.organization_id
1207 and nvl(bor.cfm_routing_flag,2) = 2
1208 and bor.assembly_item_id = wjsi_row.primary_item_id
1209 and nvl(bor.alternate_routing_designator,'@@@') =
1210 nvl(wjsi_row.alternate_routing_designator,'@@@');
1211 /*Fix for Bug#9765343 (FP of 9668143) Derive Completion Subinventory based on routing_reference_id
1212 for a Non-Standard Job*/
1213 elsif (wjsi_row.load_type = wip_constants.create_ns_job) then
1214 select bor.completion_subinventory
1215 into wjsi_row.completion_subinventory
1216 from bom_operational_routings bor
1217 where bor.organization_id = wjsi_row.organization_id
1218 and nvl(bor.cfm_routing_flag,2) = 2
1219 and bor.assembly_item_id = nvl(wjsi_row.routing_reference_id,wjsi_row.primary_item_id)
1220 and nvl(bor.alternate_routing_designator,'@@@') =
1221 nvl(wjsi_row.alternate_routing_designator,'@@@');
1222
1223 end if;
1224 elsif(wjsi_row.load_type in (wip_constants.create_sched,
1225 wip_constants.resched_job,
1226 wip_constants.resched_eam_job)) then
1227 wip_jsi_utils.record_ignored_column_warning('COMPLETION_SUBINVENTORY');
1228 end if;
1229 exception
1230 when no_data_found then
1231 null; -- no routing
1232 when others then
1233 wip_jsi_utils.record_invalid_column_error('COMPLETION_SUBINVENTORY');
1234 wip_jsi_utils.abort_request;
1235 end completion_subinventory;
1236
1237 procedure completion_locator is begin
1238 --note deriving locator id from segments is
1239 -- done in validation package as a part of the wip_locator pkg
1240 if(wjsi_row.completion_locator_id is null and
1241 wjsi_row.completion_locator_segments is null) then
1242 if((wjsi_row.load_type = wip_constants.create_job) or
1243 (wjsi_row.load_type = wip_constants.create_eam_job and --for eam, comp sub in valid for rebuild jobs only
1244 (wjsi_row.rebuild_item_id is not null or wjsi_row.rebuild_item_segments is not null))) then
1245 select bor.completion_locator_id
1246 into wjsi_row.completion_locator_id
1247 from bom_operational_routings bor
1248 where bor.organization_id = wjsi_row.organization_id
1249 and nvl(bor.cfm_routing_flag,2) = 2
1250 and bor.assembly_item_id = wjsi_row.primary_item_id
1251 and nvl(bor.alternate_routing_designator,'@@@') =
1252 nvl(wjsi_row.alternate_routing_designator,'@@@')
1253 /* Fixed for bug#3060266
1254 While defaulting the value for completion locator,completion sub inventory mus
1255 t be checked.Completion locator would be defaulted from
1256 table BOM_OPERATIONAL_ROUTINGS only if the completion sub inventory in
1257 BOM_OPERATIONAL_ROUTINGS is same as completion sub inventory given in
1258 table WIP_JOB_SCHEDULE_INTERFACE otherwise leave it null.
1259 */
1260 and bor.COMPLETION_SUBINVENTORY=wjsi_row.COMPLETION_SUBINVENTORY;
1261 /*Fix for Bug#9765343 (FP of 9668143) Derive Completion Locator based on routing_reference_id
1262 for a Non-Standard Job*/
1263 elsif (wjsi_row.load_type = wip_constants.create_ns_job) then
1264 select bor.completion_locator_id
1265 into wjsi_row.completion_locator_id
1266 from bom_operational_routings bor
1267 where bor.organization_id = wjsi_row.organization_id
1268 and nvl(bor.cfm_routing_flag,2) = 2
1269 and bor.assembly_item_id = nvl(wjsi_row.routing_reference_id,wjsi_row.primary_item_id)
1270 and nvl(bor.alternate_routing_designator,'@@@') =
1271 nvl(wjsi_row.alternate_routing_designator,'@@@')
1272 and bor.COMPLETION_SUBINVENTORY=wjsi_row.COMPLETION_SUBINVENTORY;
1273 end if;
1274 else
1275 if(wjsi_row.load_type in (wip_constants.create_job,
1276 wip_constants.create_ns_job,
1277 wip_constants.create_eam_job)) then
1278 if(wjsi_row.completion_locator_id is not null and
1279 wjsi_row.completion_locator_segments is not null) then
1280 wip_jsi_utils.record_ignored_column_warning('COMPLETION_LOCATOR_SEGMENTS');
1281 end if;
1282 else
1283 wip_jsi_utils.record_ignored_column_warning('COMPLETION_LOCATOR_ID');
1284 end if;
1285 end if;
1286 exception
1287 when no_data_found then
1288 null;
1289 when others then
1290 wip_jsi_utils.record_invalid_column_error('COMPLETION_LOCATOR');
1291 wip_jsi_utils.abort_request;
1292 end completion_locator;
1293
1294 procedure last_updated_by_name is begin
1295 if(wjsi_row.last_updated_by is null) then
1296 select user_id
1297 into wjsi_row.last_updated_by
1298 from fnd_user
1299 where user_name = wjsi_row.last_updated_by_name;
1300 elsif(wjsi_row.last_updated_by_name is not null) then --both name + id columns are populated
1301 WIP_JSI_Utils.record_ignored_column_warning('LAST_UPDATED_BY_NAME');
1302 end if;
1303 exception
1304 when others then
1305 wip_jsi_utils.record_invalid_column_error('LAST_UPDATED_BY_NAME');
1306 wip_jsi_utils.abort_request;
1307 end last_updated_by_name;
1308
1309 procedure created_by_name is begin
1310 if(wjsi_row.created_by is null) then
1311 select user_id
1312 into wjsi_row.created_by
1313 from fnd_user
1314 where user_name = wjsi_row.created_by_name;
1315 elsif(wjsi_row.created_by_name is not null) then --both name + id columns are populated
1316 WIP_JSI_Utils.record_ignored_column_warning('CREATED_BY_NAME');
1317 end if;
1318 exception
1319 when others then
1320 wip_jsi_utils.record_invalid_column_error('CREATED_BY_NAME');
1321 wip_jsi_utils.abort_request;
1322 end created_by_name;
1323
1324 procedure priority is begin
1325 if(wjsi_row.load_type = wip_constants.create_sched and
1326 wjsi_row.priority is not null) then
1327 wip_jsi_utils.record_ignored_column_warning('PRIORITY');
1328 end if;
1329 end priority;
1330
1331 --due_date/requested_start_date logic is:
1332 --if both are null and doing a job creation then first try to default due_date.
1333 --if both are still null then try to default requested_start_date
1334 procedure due_date is begin
1335 if(wjsi_row.due_date is null) then
1336 if(wjsi_row.requested_start_date is null and
1337 (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) OR
1338 (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.pm_schedule_id is not null))) then
1339 wjsi_row.due_date := wjsi_row.last_unit_completion_date;
1340 end if;
1341 elsif(wjsi_row.load_type = wip_constants.create_sched) then
1342 wip_jsi_utils.record_ignored_column_warning('DUE_DATE');
1343 end if;
1344 end due_date;
1345
1346 procedure date_released is begin
1347 if(wjsi_row.status_type = wip_constants.unreleased and
1348 wjsi_row.date_released is not null) then
1349 wip_jsi_utils.record_ignored_column_warning('DATE_RELEASED');
1350 end if;
1351 end date_released;
1352
1353 procedure requested_start_date is begin
1354 if(wjsi_row.requested_start_date is null) then
1355 if(wjsi_row.due_date is null and
1356 (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) OR
1357 (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.pm_schedule_id is not null))) then
1358 wjsi_row.requested_start_date := wjsi_row.first_unit_start_date;
1359 end if;
1360 elsif(wjsi_row.load_type = wip_constants.create_sched) then
1361 wip_jsi_utils.record_ignored_column_warning('REQUESTED_START_DATE');
1362 end if;
1363 end requested_start_date;
1364
1365 procedure header_id is begin
1366 if(wjsi_row.load_type = wip_constants.create_sched and
1367 wjsi_row.header_id is not null) then
1368 wip_jsi_utils.record_ignored_column_warning('HEADER_ID');
1369 end if;
1370 end header_id;
1371
1372 procedure end_item_unit_number is begin
1373 if ((wjsi_row.load_type in (wip_constants.create_sched, wip_constants.resched_job,
1374 wip_constants.resched_eam_job)) and
1375 (wjsi_row.end_item_unit_number is not null)) then
1376 wip_jsi_utils.record_ignored_column_warning('END_ITEM_UNIT_NUMBER');
1377 end if;
1378 end end_item_unit_number;
1379
1380 procedure asset_number is begin
1381 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1382 wip_constants.resched_job, wip_constants.resched_eam_job,
1383 wip_constants.create_ns_job) and
1384 wjsi_row.asset_number is not null) then
1385 wip_jsi_utils.record_ignored_column_warning('ASSET_NUMBER');
1386 end if;
1387 end asset_number;
1388
1389 procedure asset_group is begin
1390 if(wjsi_row.asset_group_id is not null and
1391 wjsi_row.load_type <> wip_constants.create_eam_job) then
1392 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_ID');
1393 end if;
1394
1395 if(wjsi_row.asset_group_segments is not null and
1396 wjsi_row.load_type <> wip_constants.create_eam_job) then
1397 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_SEGMENTS');
1398 elsif(wjsi_row.asset_group_segments is not null) then
1399 if(wjsi_row.asset_group_id is null) then
1400 select inventory_item_id
1401 into wjsi_row.asset_group_id
1402 from mtl_system_items_kfv
1403 where concatenated_segments = wjsi_row.asset_group_segments
1404 and organization_id = wjsi_row.organization_id;
1405 else
1406 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_SEGMENTS');
1407 end if;
1408 end if;
1409 exception
1410 when others then
1411 wip_jsi_utils.record_invalid_column_error('ASSET_GROUP_SEGMENTS');
1412 wip_jsi_utils.abort_request;
1413 end asset_group;
1414
1415 procedure parent_job_name is begin
1416 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1417 wjsi_row.parent_job_name is not null) then
1418 wip_jsi_utils.record_ignored_column_warning('PARENT_JOB_NAME');
1419 elsif(wjsi_row.parent_job_name is not null) then
1420 if(wjsi_row.parent_wip_entity_id is null) then
1421 select wip_entity_id
1422 into wjsi_row.parent_wip_entity_id
1423 from wip_entities
1424 where wip_entity_name = wjsi_row.parent_job_name
1425 and organization_id = wjsi_row.organization_id;
1426 else
1427 wip_jsi_utils.record_ignored_column_warning('PARENT_JOB_NAME');
1428 end if;
1429 end if;
1430 exception
1431 when others then
1432 wip_jsi_utils.record_invalid_column_error('PARENT_JOB_NAME');
1433 wip_jsi_utils.abort_request;
1434 end parent_job_name;
1435
1436 procedure parent_wip_entity_id is begin
1437 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1438 wjsi_row.parent_wip_entity_id is not null) then
1439 wip_jsi_utils.record_ignored_column_warning('PARENT_WIP_ENTITY_ID');
1440 end if;
1441 end parent_wip_entity_id;
1442
1443 procedure rebuild_item is begin
1444 if(wjsi_row.load_type <> wip_constants.create_eam_job) then
1445 if(wjsi_row.rebuild_item_segments is not null) then
1446 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_SEGMENTS');
1447 end if;
1448 if(wjsi_row.rebuild_item_id is not null) then
1449 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_ID');
1450 end if;
1451
1452 elsif(wjsi_row.rebuild_item_segments is not null) then
1453 if(wjsi_row.rebuild_item_id is null) then
1454 select inventory_item_id
1455 into wjsi_row.rebuild_item_id
1456 from mtl_system_items_kfv
1457 where concatenated_segments = wjsi_row.rebuild_item_segments
1458 and organization_id = wjsi_row.organization_id;
1459 else
1460 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_SEGMENTS');
1461 end if;
1462 end if;
1463 exception
1464 when others then
1465 wip_jsi_utils.record_invalid_column_error('REBUILD_ITEM_SEGMENTS');
1466 wip_jsi_utils.abort_request;
1467 end rebuild_item;
1468
1469 procedure rebuild_serial_number is begin
1470 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1471 wjsi_row.rebuild_serial_number is not null) then
1472 wip_jsi_utils.record_ignored_column_warning('REBUILD_SERIAL_NUMBER');
1473 end if;
1474 end rebuild_serial_number;
1475
1476 procedure manual_rebuild_flag is begin
1477 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1478 wjsi_row.manual_rebuild_flag is not null) then
1479 wip_jsi_utils.record_ignored_column_warning('MANUAL_REBUILD_FLAG');
1480 end if;
1481 end manual_rebuild_flag;
1482
1483 procedure owning_department is
1484 l_returnMsg VARCHAR2(200);
1485 l_msgCount NUMBER;
1486 l_errMsg VARCHAR2(30);
1487 begin
1488 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1489 if(wjsi_row.owning_department_code is not null) then
1490 wip_jsi_utils.record_ignored_column_warning('OWNING_DEPARTMENT_CODE');
1491 end if;
1492 if(wjsi_row.owning_department is not null) then
1493 wip_jsi_utils.record_ignored_column_warning('OWNING_DEPARTMENT');
1494 end if;
1495 else
1496 if(wjsi_row.owning_department is null and wjsi_row.owning_department_code is not null) then
1497 select department_id
1498 into wjsi_row.owning_department
1499 from bom_departments
1500 where department_code = wjsi_row.owning_department_code
1501 and organization_id = wjsi_row.organization_id;
1502 elsif(wjsi_row.owning_department is null and wjsi_row.owning_department_code is null) then
1503 wip_eamworkorder_pvt.get_eam_owning_dept_default(p_api_version => 1.0,
1504 p_init_msg_list => null,
1505 p_commit => null,
1506 p_validation_level => null,
1507 p_primary_item_id => wjsi_row.primary_item_id,
1508 p_organization_id => wjsi_row.organization_id,
1509 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1510 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1511 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1512 x_owning_department_id => wjsi_row.owning_department,
1513 x_return_status => l_returnMsg,
1514 x_msg_count => l_msgCount,
1515 x_msg_data => l_errMsg);
1516 if (l_errMsg is not null) then
1517 fnd_message.set_name('WIP', 'OWNING_DEPARTMENT');
1518 fnd_message.set_token('owning_department', wjsi_row.owning_department, false);
1519 wip_jsi_utils.record_current_error;
1520 end if ;
1521 elsif(wjsi_row.owning_department_code is not null) then
1522 WIP_JSI_Utils.record_ignored_column_warning('OWNING_DEPARTMENT_CODE');
1523 end if;
1524 end if;
1525 exception
1526 when others then
1527 wip_jsi_utils.record_invalid_column_error('OWNING_DEPARTMENT_CODE');
1528 wip_jsi_utils.abort_request;
1529 end owning_department;
1530
1531 procedure notification_required is
1532 l_returnMsg VARCHAR2(200);
1533 l_msgCount NUMBER;
1534 l_errMsg VARCHAR2(30);
1535 begin
1536 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1537 wjsi_row.notification_required is not null) then
1538 wip_jsi_utils.record_ignored_column_warning('NOTIFICATION_REQUIRED');
1539 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1540 wjsi_row.notification_required is null) then
1541 wip_eamworkorder_pvt.get_eam_notification_default(p_api_version => 1.0,
1542 p_init_msg_list => null,
1543 p_commit => null,
1544 p_validation_level => null,
1545 p_primary_item_id => wjsi_row.primary_item_id,
1546 p_organization_id => wjsi_row.organization_id,
1547 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1548 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1549 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1550 x_notification_flag => wjsi_row.notification_required,
1551 x_return_status => l_returnMsg,
1552 x_msg_count => l_msgCount,
1553 x_msg_data => l_errMsg);
1554 if (l_errMsg is not null) then
1555 fnd_message.set_name('WIP', 'NOTIFICATION_REQUIRED');
1556 fnd_message.set_token('notification_required', wjsi_row.notification_required, false);
1557 wip_jsi_utils.record_current_error;
1558 end if ;
1559 end if;
1560
1561 exception
1562 when others then
1563 wip_jsi_utils.record_invalid_column_error('NOTIFICATION_REQUIRED');
1564 wip_jsi_utils.abort_request;
1565 end notification_required;
1566
1567 procedure shutdown_type is
1568 l_returnMsg VARCHAR2(200);
1569 l_msgCount NUMBER;
1570 l_errMsg VARCHAR2(30);
1571 begin
1572 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1573 wjsi_row.shutdown_type is not null) then
1574 wip_jsi_utils.record_ignored_column_warning('SHUTDOWN_TYPE');
1575 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1576 wjsi_row.shutdown_type is null) then
1577 wip_eamworkorder_pvt.get_eam_shutdown_default(p_api_version => 1.0,
1578 p_init_msg_list => null,
1579 p_commit => null,
1580 p_validation_level => null,
1581 p_primary_item_id => wjsi_row.primary_item_id,
1582 p_organization_id => wjsi_row.organization_id,
1583 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1584 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1585 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1586 x_shutdown_type_code => wjsi_row.shutdown_type,
1587 x_return_status => l_returnMsg,
1588 x_msg_count => l_msgCount,
1589 x_msg_data => l_errMsg);
1590 if (l_errMsg is not null) then
1591 fnd_message.set_name('WIP', 'SHUTDOWN_TYPE');
1592 fnd_message.set_token('shutdown_type', wjsi_row.shutdown_type, false);
1593 wip_jsi_utils.record_current_error;
1594 end if ;
1595 end if;
1596
1597 exception
1598 when others then
1599 wip_jsi_utils.record_invalid_column_error('SHUTDOWN_TYPE');
1600 wip_jsi_utils.abort_request;
1601 end shutdown_type;
1602
1603 procedure work_order_type is begin
1604 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1605 wjsi_row.work_order_type is not null) then
1606 wip_jsi_utils.record_ignored_column_warning('WORK_ORDER_TYPE');
1607 end if;
1608 end work_order_type;
1609
1610 procedure tagout_required is
1611 l_returnMsg VARCHAR2(200);
1612 l_msgCount NUMBER;
1613 l_errMsg VARCHAR2(30);
1614 begin
1615 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1616 wjsi_row.tagout_required is not null) then
1617 wip_jsi_utils.record_ignored_column_warning('TAGOUT_REQUIRED');
1618 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1619 wjsi_row.tagout_required is null) then
1620 wip_eamworkorder_pvt.get_eam_tagout_default(p_api_version => 1.0,
1621 p_init_msg_list => null,
1622 p_commit => null,
1623 p_validation_level => null,
1624 p_primary_item_id => wjsi_row.primary_item_id,
1625 p_organization_id => wjsi_row.organization_id,
1626 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1627 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1628 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1629 x_tagout_required => wjsi_row.tagout_required,
1630 x_return_status => l_returnMsg,
1631 x_msg_count => l_msgCount,
1632 x_msg_data => l_errMsg);
1633 if (l_errMsg is not null) then
1634 fnd_message.set_name('WIP', 'TAGOUT_REQUIRED');
1635 fnd_message.set_token('tagout_required', wjsi_row.tagout_required, false);
1636 wip_jsi_utils.record_current_error;
1637 end if ;
1638 end if;
1639
1640 exception
1641 when others then
1642 wip_jsi_utils.record_invalid_column_error('TAGOUT_REQUIRED');
1643 wip_jsi_utils.abort_request;
1644 end tagout_required;
1645
1646 procedure plan_maintenance is begin
1647 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1648 wjsi_row.plan_maintenance is not null) then
1649 wip_jsi_utils.record_ignored_column_warning('PLAN_MAINTENANCE');
1650 elsif(wjsi_row.pm_schedule_id is not null) then
1651 wjsi_row.plan_maintenance := 'Y';
1652 end if;
1653 end plan_maintenance;
1654
1655 procedure activity_type is
1656 l_returnMsg VARCHAR2(200);
1657 l_msgCount NUMBER;
1658 l_errMsg VARCHAR2(30);
1659 begin
1660 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1661 wjsi_row.activity_type is not null) then
1662 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_TYPE');
1663 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1664 wjsi_row.activity_type is null) then
1665 wip_eamworkorder_pvt.get_eam_act_type_default(p_api_version => 1.0,
1666 p_init_msg_list => null,
1667 p_commit => null,
1668 p_validation_level => null,
1669 p_primary_item_id => wjsi_row.primary_item_id,
1670 p_organization_id => wjsi_row.organization_id,
1671 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1672 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1673 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1674 x_activity_type_code => wjsi_row.activity_type,
1675 x_return_status => l_returnMsg,
1676 x_msg_count => l_msgCount,
1677 x_msg_data => l_errMsg);
1678 if (l_errMsg is not null) then
1679 fnd_message.set_name('WIP', 'ACTIVITY_TYPE');
1680 fnd_message.set_token('activity_type', wjsi_row.activity_type, false);
1681 wip_jsi_utils.record_current_error;
1682 end if;
1683 end if;
1684
1685 exception
1686 when others then
1687 wip_jsi_utils.record_invalid_column_error('ACTIVITY_TYPE');
1688 wip_jsi_utils.abort_request;
1689 end activity_type;
1690
1691 procedure activity_cause is
1692 l_returnMsg VARCHAR2(200);
1693 l_msgCount NUMBER;
1694 l_errMsg VARCHAR2(30);
1695 begin
1696 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1697 wjsi_row.activity_cause is not null) then
1698 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_CAUSE');
1699 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1700 wjsi_row.activity_cause is null) then
1701 wip_eamworkorder_pvt.get_eam_act_cause_default(p_api_version => 1.0,
1702 p_init_msg_list => null,
1703 p_commit => null,
1704 p_validation_level => null,
1705 p_primary_item_id => wjsi_row.primary_item_id,
1706 p_organization_id => wjsi_row.organization_id,
1707 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1708 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1709 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1710 x_activity_cause_code => wjsi_row.activity_cause,
1711 x_return_status => l_returnMsg,
1712 x_msg_count => l_msgCount,
1713 x_msg_data => l_errMsg);
1714 if (l_errMsg is not null) then
1715 fnd_message.set_name('WIP', 'ACTIVITY_CAUSE');
1716 fnd_message.set_token('activity_cause', wjsi_row.activity_cause, false);
1717 wip_jsi_utils.record_current_error;
1718 end if;
1719 end if;
1720
1721 exception
1722 when others then
1723 wip_jsi_utils.record_invalid_column_error('ACTIVITY_CAUSE');
1724 wip_jsi_utils.abort_request;
1725 end activity_cause;
1726
1727 procedure serialization_start_op is
1728 begin
1729 if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.resched_job) and
1730 wjsi_row.serialization_start_op is not null) then
1731 wip_jsi_utils.record_ignored_column_warning('SERIALIZATION_START_OP');
1732 end if;
1733 end serialization_start_op;
1734
1735 procedure material_issue_by_mo is
1736 l_wms_enabled_flag VARCHAR(1);
1737 begin
1738 if (wjsi_row.load_type <> wip_constants.create_eam_job and
1739 (wjsi_row.load_type <> wip_constants.resched_eam_job or wjsi_row.status_type <> wip_constants.draft)
1740 and wjsi_row.material_issue_by_mo is not null) then
1741
1742 wip_jsi_utils.record_ignored_column_warning('MATERIAL_ISSUE_BY_MO');
1743
1744 select material_issue_by_mo
1745 into wjsi_row.material_issue_by_mo
1746 from wip_discrete_jobs
1747 where wip_entity_id = wjsi_row.wip_entity_id
1748 and organization_id = wjsi_row.organization_id;
1749
1750 elsif (wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1751 select wms_enabled_flag
1752 into l_wms_enabled_flag
1753 from mtl_parameters
1754 where organization_id = wjsi_row.organization_id;
1755
1756 if (upper(l_wms_enabled_flag) = 'Y') then
1757 wjsi_row.material_issue_by_mo := 'N';
1758 elsif (wjsi_row.material_issue_by_mo is null) then
1759 select material_issue_by_mo
1760 into wjsi_row.material_issue_by_mo
1761 from WIP_EAM_PARAMETERS
1762 where organization_id = wjsi_row.organization_id;
1763 end if;
1764
1765 end if;
1766 end material_issue_by_mo;
1767
1768 procedure maintenance_object_type is
1769 l_serial_number_control_code NUMBER;
1770 begin
1771 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1772 wjsi_row.maintenance_object_type is not null) then
1773 wip_jsi_utils.record_ignored_column_warning('MAINTENANCE_OBJECT_TYPE');
1774 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1775 wjsi_row.maintenance_object_type is null) then
1776 if(wjsi_row.rebuild_item_id is null) then
1777 wjsi_row.maintenance_object_type := 1; --MSN
1778 else
1779 select serial_number_control_code
1780 into l_serial_number_control_code
1781 from mtl_system_items
1782 where organization_id = wjsi_row.organization_id
1783 and inventory_item_id = wjsi_row.rebuild_item_id;
1784
1785 if(l_serial_number_control_code in (2, 5, 6)) then
1786 wjsi_row.maintenance_object_type := 1; --MSN
1787 else
1788 wjsi_row.maintenance_object_type := 2; --MSI
1789 end if;
1790
1791 end if;
1792 end if;
1793
1794 exception
1795 when no_data_found then
1796 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_TYPE');
1797 wip_jsi_utils.abort_request;
1798 when others then
1799 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_TYPE');
1800 wip_jsi_utils.abort_request;
1801 end maintenance_object_type;
1802
1803 procedure maintenance_object_id is
1804 begin
1805 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1806 wjsi_row.maintenance_object_id is not null) then
1807 wip_jsi_utils.record_ignored_column_warning('MAINTENANCE_OBJECT_ID');
1808 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1809 wjsi_row.maintenance_object_id is null) then
1810 if(wjsi_row.maintenance_object_type = 1) then
1811 if(wjsi_row.rebuild_item_id is null) then
1812 select gen_object_id
1813 into wjsi_row.maintenance_object_id
1814 from mtl_serial_numbers
1815 where current_organization_id = wjsi_row.organization_id
1816 and inventory_item_id = wjsi_row.asset_group_id
1817 and serial_number = wjsi_row.asset_number;
1818 elsif(wjsi_row.rebuild_serial_number is not null) then
1819 select gen_object_id
1820 into wjsi_row.maintenance_object_id
1821 from mtl_serial_numbers
1822 where current_organization_id = wjsi_row.organization_id
1823 and inventory_item_id = wjsi_row.rebuild_item_id
1824 and serial_number = wjsi_row.rebuild_serial_number;
1825 end if;
1826 elsif(wjsi_row.maintenance_object_type = 2) then
1827 wjsi_row.maintenance_object_id := wjsi_row.rebuild_item_id;
1828 end if;
1829 end if;
1830
1831 exception
1832 when no_data_found then
1833 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_ID');
1834 wip_jsi_utils.abort_request;
1835 when others then
1836 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_ID');
1837 wip_jsi_utils.abort_request;
1838 end maintenance_object_id;
1839
1840 procedure maintenance_object_source is begin
1841 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1842 wjsi_row.maintenance_object_source is not null) then
1843 wip_jsi_utils.record_ignored_column_warning('MAITENANCE_OBJECT_SOURCE');
1844 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1845 wjsi_row.maintenance_object_source is null) then
1846 wjsi_row.maintenance_object_source := 1; --EAM
1847 end if;
1848
1849 exception
1850 when others then
1851 wip_jsi_utils.record_invalid_column_error('MAITENANCE_OBJECT_SOURCE');
1852 wip_jsi_utils.abort_request;
1853 end maintenance_object_source;
1854
1855 procedure activity_source is
1856 l_returnMsg VARCHAR2(200);
1857 l_msgCount NUMBER;
1858 l_errMsg VARCHAR2(30);
1859 begin
1860 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1861 wjsi_row.activity_source is not null) then
1862 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_SOURCE');
1863 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1864 wjsi_row.activity_source is null) then
1865 wip_eamworkorder_pvt.get_eam_act_source_default(p_api_version => 1.0,
1866 p_init_msg_list => null,
1867 p_commit => null,
1868 p_validation_level => null,
1869 p_primary_item_id => wjsi_row.primary_item_id,
1870 p_organization_id => wjsi_row.organization_id,
1871 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1872 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1873 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1874 x_activity_source_code => wjsi_row.activity_source,
1875 x_return_status => l_returnMsg,
1876 x_msg_count => l_msgCount,
1877 x_msg_data => l_errMsg);
1878 if (l_errMsg is not null) then
1879 fnd_message.set_name('WIP', 'ACTIVITY_SOURCE');
1880 fnd_message.set_token('activity_source', wjsi_row.activity_source, false);
1881 wip_jsi_utils.record_current_error;
1882 end if;
1883 end if;
1884
1885 exception
1886 when others then
1887 wip_jsi_utils.record_invalid_column_error('ACTIVITY_SOURCE');
1888 wip_jsi_utils.abort_request;
1889 end activity_source;
1890
1891 procedure pm_schedule_id is begin
1892 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1893 wjsi_row.pm_schedule_id is not null) then
1894 wip_jsi_utils.record_ignored_column_warning('PM_SCHEDULE_ID');
1895 end if;
1896 end pm_schedule_id;
1897
1898 end wip_jsi_defaulter;
[Home] [Help]