1 package body wip_jsi_defaulter as
2 /* $Header: wipjsidb.pls 120.2.12000000.2 2007/05/03 22:40:34 vjambhek 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(sysdate), 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(sysdate), 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 end if;
1212 elsif(wjsi_row.load_type in (wip_constants.create_sched,
1213 wip_constants.resched_job,
1214 wip_constants.resched_eam_job)) then
1215 wip_jsi_utils.record_ignored_column_warning('COMPLETION_SUBINVENTORY');
1216 end if;
1217 exception
1218 when no_data_found then
1219 null; -- no routing
1220 when others then
1221 wip_jsi_utils.record_invalid_column_error('COMPLETION_SUBINVENTORY');
1222 wip_jsi_utils.abort_request;
1223 end completion_subinventory;
1224
1225 procedure completion_locator is begin
1226 --note deriving locator id from segments is
1227 -- done in validation package as a part of the wip_locator pkg
1228 if(wjsi_row.completion_locator_id is null and
1229 wjsi_row.completion_locator_segments is null) then
1230 if((wjsi_row.load_type = wip_constants.create_job) or
1231 (wjsi_row.load_type = wip_constants.create_eam_job and --for eam, comp sub in valid for rebuild jobs only
1232 (wjsi_row.rebuild_item_id is not null or wjsi_row.rebuild_item_segments is not null))) then
1233 select bor.completion_locator_id
1234 into wjsi_row.completion_locator_id
1235 from bom_operational_routings bor
1236 where bor.organization_id = wjsi_row.organization_id
1237 and nvl(bor.cfm_routing_flag,2) = 2
1238 and bor.assembly_item_id = wjsi_row.primary_item_id
1239 and nvl(bor.alternate_routing_designator,'@@@') =
1240 nvl(wjsi_row.alternate_routing_designator,'@@@')
1241 /* Fixed for bug#3060266
1242 While defaulting the value for completion locator,completion sub inventory mus
1243 t be checked.Completion locator would be defaulted from
1244 table BOM_OPERATIONAL_ROUTINGS only if the completion sub inventory in
1245 BOM_OPERATIONAL_ROUTINGS is same as completion sub inventory given in
1246 table WIP_JOB_SCHEDULE_INTERFACE otherwise leave it null.
1247 */
1248 and bor.COMPLETION_SUBINVENTORY=wjsi_row.COMPLETION_SUBINVENTORY;
1249 end if;
1250 else
1251 if(wjsi_row.load_type in (wip_constants.create_job,
1252 wip_constants.create_ns_job,
1253 wip_constants.create_eam_job)) then
1254 if(wjsi_row.completion_locator_id is not null and
1255 wjsi_row.completion_locator_segments is not null) then
1256 wip_jsi_utils.record_ignored_column_warning('COMPLETION_LOCATOR_SEGMENTS');
1257 end if;
1258 else
1259 wip_jsi_utils.record_ignored_column_warning('COMPLETION_LOCATOR_ID');
1260 end if;
1261 end if;
1262 exception
1263 when no_data_found then
1264 null;
1265 when others then
1266 wip_jsi_utils.record_invalid_column_error('COMPLETION_LOCATOR');
1267 wip_jsi_utils.abort_request;
1268 end completion_locator;
1269
1270 procedure last_updated_by_name is begin
1271 if(wjsi_row.last_updated_by is null) then
1272 select user_id
1273 into wjsi_row.last_updated_by
1274 from fnd_user
1275 where user_name = wjsi_row.last_updated_by_name;
1276 elsif(wjsi_row.last_updated_by_name is not null) then --both name + id columns are populated
1277 WIP_JSI_Utils.record_ignored_column_warning('LAST_UPDATED_BY_NAME');
1278 end if;
1279 exception
1280 when others then
1281 wip_jsi_utils.record_invalid_column_error('LAST_UPDATED_BY_NAME');
1282 wip_jsi_utils.abort_request;
1283 end last_updated_by_name;
1284
1285 procedure created_by_name is begin
1286 if(wjsi_row.created_by is null) then
1287 select user_id
1288 into wjsi_row.created_by
1289 from fnd_user
1290 where user_name = wjsi_row.created_by_name;
1291 elsif(wjsi_row.created_by_name is not null) then --both name + id columns are populated
1292 WIP_JSI_Utils.record_ignored_column_warning('CREATED_BY_NAME');
1293 end if;
1294 exception
1295 when others then
1296 wip_jsi_utils.record_invalid_column_error('CREATED_BY_NAME');
1297 wip_jsi_utils.abort_request;
1298 end created_by_name;
1299
1300 procedure priority is begin
1301 if(wjsi_row.load_type = wip_constants.create_sched and
1302 wjsi_row.priority is not null) then
1303 wip_jsi_utils.record_ignored_column_warning('PRIORITY');
1304 end if;
1305 end priority;
1306
1307 --due_date/requested_start_date logic is:
1308 --if both are null and doing a job creation then first try to default due_date.
1309 --if both are still null then try to default requested_start_date
1310 procedure due_date is begin
1311 if(wjsi_row.due_date is null) then
1312 if(wjsi_row.requested_start_date is null and
1313 (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) OR
1314 (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.pm_schedule_id is not null))) then
1315 wjsi_row.due_date := wjsi_row.last_unit_completion_date;
1316 end if;
1317 elsif(wjsi_row.load_type = wip_constants.create_sched) then
1318 wip_jsi_utils.record_ignored_column_warning('DUE_DATE');
1319 end if;
1320 end due_date;
1321
1322 procedure date_released is begin
1323 if(wjsi_row.status_type = wip_constants.unreleased and
1324 wjsi_row.date_released is not null) then
1325 wip_jsi_utils.record_ignored_column_warning('DATE_RELEASED');
1326 end if;
1327 end date_released;
1328
1329 procedure requested_start_date is begin
1330 if(wjsi_row.requested_start_date is null) then
1331 if(wjsi_row.due_date is null and
1332 (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) OR
1333 (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.pm_schedule_id is not null))) then
1334 wjsi_row.requested_start_date := wjsi_row.first_unit_start_date;
1335 end if;
1336 elsif(wjsi_row.load_type = wip_constants.create_sched) then
1337 wip_jsi_utils.record_ignored_column_warning('REQUESTED_START_DATE');
1338 end if;
1339 end requested_start_date;
1340
1341 procedure header_id is begin
1342 if(wjsi_row.load_type = wip_constants.create_sched and
1343 wjsi_row.header_id is not null) then
1344 wip_jsi_utils.record_ignored_column_warning('HEADER_ID');
1345 end if;
1346 end header_id;
1347
1348 procedure end_item_unit_number is begin
1349 if ((wjsi_row.load_type in (wip_constants.create_sched, wip_constants.resched_job,
1350 wip_constants.resched_eam_job)) and
1351 (wjsi_row.end_item_unit_number is not null)) then
1352 wip_jsi_utils.record_ignored_column_warning('END_ITEM_UNIT_NUMBER');
1353 end if;
1354 end end_item_unit_number;
1355
1356 procedure asset_number is begin
1357 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1358 wip_constants.resched_job, wip_constants.resched_eam_job,
1359 wip_constants.create_ns_job) and
1360 wjsi_row.asset_number is not null) then
1361 wip_jsi_utils.record_ignored_column_warning('ASSET_NUMBER');
1362 end if;
1363 end asset_number;
1364
1365 procedure asset_group is begin
1366 if(wjsi_row.asset_group_id is not null and
1367 wjsi_row.load_type <> wip_constants.create_eam_job) then
1368 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_ID');
1369 end if;
1370
1371 if(wjsi_row.asset_group_segments is not null and
1372 wjsi_row.load_type <> wip_constants.create_eam_job) then
1373 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_SEGMENTS');
1374 elsif(wjsi_row.asset_group_segments is not null) then
1375 if(wjsi_row.asset_group_id is null) then
1376 select inventory_item_id
1377 into wjsi_row.asset_group_id
1378 from mtl_system_items_kfv
1379 where concatenated_segments = wjsi_row.asset_group_segments
1380 and organization_id = wjsi_row.organization_id;
1381 else
1382 wip_jsi_utils.record_ignored_column_warning('ASSET_GROUP_SEGMENTS');
1383 end if;
1384 end if;
1385 exception
1386 when others then
1387 wip_jsi_utils.record_invalid_column_error('ASSET_GROUP_SEGMENTS');
1388 wip_jsi_utils.abort_request;
1389 end asset_group;
1390
1391 procedure parent_job_name is begin
1392 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1393 wjsi_row.parent_job_name is not null) then
1394 wip_jsi_utils.record_ignored_column_warning('PARENT_JOB_NAME');
1395 elsif(wjsi_row.parent_job_name is not null) then
1396 if(wjsi_row.parent_wip_entity_id is null) then
1397 select wip_entity_id
1398 into wjsi_row.parent_wip_entity_id
1399 from wip_entities
1400 where wip_entity_name = wjsi_row.parent_job_name
1401 and organization_id = wjsi_row.organization_id;
1402 else
1403 wip_jsi_utils.record_ignored_column_warning('PARENT_JOB_NAME');
1404 end if;
1405 end if;
1406 exception
1407 when others then
1408 wip_jsi_utils.record_invalid_column_error('PARENT_JOB_NAME');
1409 wip_jsi_utils.abort_request;
1410 end parent_job_name;
1411
1412 procedure parent_wip_entity_id is begin
1413 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1414 wjsi_row.parent_wip_entity_id is not null) then
1415 wip_jsi_utils.record_ignored_column_warning('PARENT_WIP_ENTITY_ID');
1416 end if;
1417 end parent_wip_entity_id;
1418
1419 procedure rebuild_item is begin
1420 if(wjsi_row.load_type <> wip_constants.create_eam_job) then
1421 if(wjsi_row.rebuild_item_segments is not null) then
1422 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_SEGMENTS');
1423 end if;
1424 if(wjsi_row.rebuild_item_id is not null) then
1425 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_ID');
1426 end if;
1427
1428 elsif(wjsi_row.rebuild_item_segments is not null) then
1429 if(wjsi_row.rebuild_item_id is null) then
1430 select inventory_item_id
1431 into wjsi_row.rebuild_item_id
1432 from mtl_system_items_kfv
1433 where concatenated_segments = wjsi_row.rebuild_item_segments
1434 and organization_id = wjsi_row.organization_id;
1435 else
1436 wip_jsi_utils.record_ignored_column_warning('REBUILD_ITEM_SEGMENTS');
1437 end if;
1438 end if;
1439 exception
1440 when others then
1441 wip_jsi_utils.record_invalid_column_error('REBUILD_ITEM_SEGMENTS');
1442 wip_jsi_utils.abort_request;
1443 end rebuild_item;
1444
1445 procedure rebuild_serial_number is begin
1446 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1447 wjsi_row.rebuild_serial_number is not null) then
1448 wip_jsi_utils.record_ignored_column_warning('REBUILD_SERIAL_NUMBER');
1449 end if;
1450 end rebuild_serial_number;
1451
1452 procedure manual_rebuild_flag is begin
1453 if(wjsi_row.load_type <> wip_constants.create_eam_job and
1454 wjsi_row.manual_rebuild_flag is not null) then
1455 wip_jsi_utils.record_ignored_column_warning('MANUAL_REBUILD_FLAG');
1456 end if;
1457 end manual_rebuild_flag;
1458
1459 procedure owning_department is
1460 l_returnMsg VARCHAR2(200);
1461 l_msgCount NUMBER;
1462 l_errMsg VARCHAR2(30);
1463 begin
1464 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1465 if(wjsi_row.owning_department_code is not null) then
1466 wip_jsi_utils.record_ignored_column_warning('OWNING_DEPARTMENT_CODE');
1467 end if;
1468 if(wjsi_row.owning_department is not null) then
1469 wip_jsi_utils.record_ignored_column_warning('OWNING_DEPARTMENT');
1470 end if;
1471 else
1472 if(wjsi_row.owning_department is null and wjsi_row.owning_department_code is not null) then
1473 select department_id
1474 into wjsi_row.owning_department
1475 from bom_departments
1476 where department_code = wjsi_row.owning_department_code
1477 and organization_id = wjsi_row.organization_id;
1478 elsif(wjsi_row.owning_department is null and wjsi_row.owning_department_code is null) then
1479 wip_eamworkorder_pvt.get_eam_owning_dept_default(p_api_version => 1.0,
1480 p_init_msg_list => null,
1481 p_commit => null,
1482 p_validation_level => null,
1483 p_primary_item_id => wjsi_row.primary_item_id,
1484 p_organization_id => wjsi_row.organization_id,
1485 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1486 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1487 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1488 x_owning_department_id => wjsi_row.owning_department,
1489 x_return_status => l_returnMsg,
1490 x_msg_count => l_msgCount,
1491 x_msg_data => l_errMsg);
1492 if (l_errMsg is not null) then
1493 fnd_message.set_name('WIP', 'OWNING_DEPARTMENT');
1494 fnd_message.set_token('owning_department', wjsi_row.owning_department, false);
1495 wip_jsi_utils.record_current_error;
1496 end if ;
1497 elsif(wjsi_row.owning_department_code is not null) then
1498 WIP_JSI_Utils.record_ignored_column_warning('OWNING_DEPARTMENT_CODE');
1499 end if;
1500 end if;
1501 exception
1502 when others then
1503 wip_jsi_utils.record_invalid_column_error('OWNING_DEPARTMENT_CODE');
1504 wip_jsi_utils.abort_request;
1505 end owning_department;
1506
1507 procedure notification_required is
1508 l_returnMsg VARCHAR2(200);
1509 l_msgCount NUMBER;
1510 l_errMsg VARCHAR2(30);
1511 begin
1512 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1513 wjsi_row.notification_required is not null) then
1514 wip_jsi_utils.record_ignored_column_warning('NOTIFICATION_REQUIRED');
1515 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1516 wjsi_row.notification_required is null) then
1517 wip_eamworkorder_pvt.get_eam_notification_default(p_api_version => 1.0,
1518 p_init_msg_list => null,
1519 p_commit => null,
1520 p_validation_level => null,
1521 p_primary_item_id => wjsi_row.primary_item_id,
1522 p_organization_id => wjsi_row.organization_id,
1523 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1524 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1525 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1526 x_notification_flag => wjsi_row.notification_required,
1527 x_return_status => l_returnMsg,
1528 x_msg_count => l_msgCount,
1529 x_msg_data => l_errMsg);
1530 if (l_errMsg is not null) then
1531 fnd_message.set_name('WIP', 'NOTIFICATION_REQUIRED');
1532 fnd_message.set_token('notification_required', wjsi_row.notification_required, false);
1533 wip_jsi_utils.record_current_error;
1534 end if ;
1535 end if;
1536
1537 exception
1538 when others then
1539 wip_jsi_utils.record_invalid_column_error('NOTIFICATION_REQUIRED');
1540 wip_jsi_utils.abort_request;
1541 end notification_required;
1542
1543 procedure shutdown_type is
1544 l_returnMsg VARCHAR2(200);
1545 l_msgCount NUMBER;
1546 l_errMsg VARCHAR2(30);
1547 begin
1548 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1549 wjsi_row.shutdown_type is not null) then
1550 wip_jsi_utils.record_ignored_column_warning('SHUTDOWN_TYPE');
1551 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1552 wjsi_row.shutdown_type is null) then
1553 wip_eamworkorder_pvt.get_eam_shutdown_default(p_api_version => 1.0,
1554 p_init_msg_list => null,
1555 p_commit => null,
1556 p_validation_level => null,
1557 p_primary_item_id => wjsi_row.primary_item_id,
1558 p_organization_id => wjsi_row.organization_id,
1559 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1560 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1561 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1562 x_shutdown_type_code => wjsi_row.shutdown_type,
1563 x_return_status => l_returnMsg,
1564 x_msg_count => l_msgCount,
1565 x_msg_data => l_errMsg);
1566 if (l_errMsg is not null) then
1567 fnd_message.set_name('WIP', 'SHUTDOWN_TYPE');
1568 fnd_message.set_token('shutdown_type', wjsi_row.shutdown_type, false);
1569 wip_jsi_utils.record_current_error;
1570 end if ;
1571 end if;
1572
1573 exception
1574 when others then
1575 wip_jsi_utils.record_invalid_column_error('SHUTDOWN_TYPE');
1576 wip_jsi_utils.abort_request;
1577 end shutdown_type;
1578
1579 procedure work_order_type is begin
1580 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1581 wjsi_row.work_order_type is not null) then
1582 wip_jsi_utils.record_ignored_column_warning('WORK_ORDER_TYPE');
1583 end if;
1584 end work_order_type;
1585
1586 procedure tagout_required is
1587 l_returnMsg VARCHAR2(200);
1588 l_msgCount NUMBER;
1589 l_errMsg VARCHAR2(30);
1590 begin
1591 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1592 wjsi_row.tagout_required is not null) then
1593 wip_jsi_utils.record_ignored_column_warning('TAGOUT_REQUIRED');
1594 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1595 wjsi_row.tagout_required is null) then
1596 wip_eamworkorder_pvt.get_eam_tagout_default(p_api_version => 1.0,
1597 p_init_msg_list => null,
1598 p_commit => null,
1599 p_validation_level => null,
1600 p_primary_item_id => wjsi_row.primary_item_id,
1601 p_organization_id => wjsi_row.organization_id,
1602 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1603 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1604 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1605 x_tagout_required => wjsi_row.tagout_required,
1606 x_return_status => l_returnMsg,
1607 x_msg_count => l_msgCount,
1608 x_msg_data => l_errMsg);
1609 if (l_errMsg is not null) then
1610 fnd_message.set_name('WIP', 'TAGOUT_REQUIRED');
1611 fnd_message.set_token('tagout_required', wjsi_row.tagout_required, false);
1612 wip_jsi_utils.record_current_error;
1613 end if ;
1614 end if;
1615
1616 exception
1617 when others then
1618 wip_jsi_utils.record_invalid_column_error('TAGOUT_REQUIRED');
1619 wip_jsi_utils.abort_request;
1620 end tagout_required;
1621
1622 procedure plan_maintenance is begin
1623 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1624 wjsi_row.plan_maintenance is not null) then
1625 wip_jsi_utils.record_ignored_column_warning('PLAN_MAINTENANCE');
1626 elsif(wjsi_row.pm_schedule_id is not null) then
1627 wjsi_row.plan_maintenance := 'Y';
1628 end if;
1629 end plan_maintenance;
1630
1631 procedure activity_type is
1632 l_returnMsg VARCHAR2(200);
1633 l_msgCount NUMBER;
1634 l_errMsg VARCHAR2(30);
1635 begin
1636 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1637 wjsi_row.activity_type is not null) then
1638 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_TYPE');
1639 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1640 wjsi_row.activity_type is null) then
1641 wip_eamworkorder_pvt.get_eam_act_type_default(p_api_version => 1.0,
1642 p_init_msg_list => null,
1643 p_commit => null,
1644 p_validation_level => null,
1645 p_primary_item_id => wjsi_row.primary_item_id,
1646 p_organization_id => wjsi_row.organization_id,
1647 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1648 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1649 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1650 x_activity_type_code => wjsi_row.activity_type,
1651 x_return_status => l_returnMsg,
1652 x_msg_count => l_msgCount,
1653 x_msg_data => l_errMsg);
1654 if (l_errMsg is not null) then
1655 fnd_message.set_name('WIP', 'ACTIVITY_TYPE');
1656 fnd_message.set_token('activity_type', wjsi_row.activity_type, false);
1657 wip_jsi_utils.record_current_error;
1658 end if;
1659 end if;
1660
1661 exception
1662 when others then
1663 wip_jsi_utils.record_invalid_column_error('ACTIVITY_TYPE');
1664 wip_jsi_utils.abort_request;
1665 end activity_type;
1666
1667 procedure activity_cause is
1668 l_returnMsg VARCHAR2(200);
1669 l_msgCount NUMBER;
1670 l_errMsg VARCHAR2(30);
1671 begin
1672 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1673 wjsi_row.activity_cause is not null) then
1674 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_CAUSE');
1675 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1676 wjsi_row.activity_cause is null) then
1677 wip_eamworkorder_pvt.get_eam_act_cause_default(p_api_version => 1.0,
1678 p_init_msg_list => null,
1679 p_commit => null,
1680 p_validation_level => null,
1681 p_primary_item_id => wjsi_row.primary_item_id,
1682 p_organization_id => wjsi_row.organization_id,
1683 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1684 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1685 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1686 x_activity_cause_code => wjsi_row.activity_cause,
1687 x_return_status => l_returnMsg,
1688 x_msg_count => l_msgCount,
1689 x_msg_data => l_errMsg);
1690 if (l_errMsg is not null) then
1691 fnd_message.set_name('WIP', 'ACTIVITY_CAUSE');
1692 fnd_message.set_token('activity_cause', wjsi_row.activity_cause, false);
1693 wip_jsi_utils.record_current_error;
1694 end if;
1695 end if;
1696
1697 exception
1698 when others then
1699 wip_jsi_utils.record_invalid_column_error('ACTIVITY_CAUSE');
1700 wip_jsi_utils.abort_request;
1701 end activity_cause;
1702
1703 procedure serialization_start_op is
1704 begin
1705 if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.resched_job) and
1706 wjsi_row.serialization_start_op is not null) then
1707 wip_jsi_utils.record_ignored_column_warning('SERIALIZATION_START_OP');
1708 end if;
1709 end serialization_start_op;
1710
1711 procedure material_issue_by_mo is
1712 l_wms_enabled_flag VARCHAR(1);
1713 begin
1714 if (wjsi_row.load_type <> wip_constants.create_eam_job and
1715 (wjsi_row.load_type <> wip_constants.resched_eam_job or wjsi_row.status_type <> wip_constants.draft)
1716 and wjsi_row.material_issue_by_mo is not null) then
1717
1718 wip_jsi_utils.record_ignored_column_warning('MATERIAL_ISSUE_BY_MO');
1719
1720 select material_issue_by_mo
1721 into wjsi_row.material_issue_by_mo
1722 from wip_discrete_jobs
1723 where wip_entity_id = wjsi_row.wip_entity_id
1724 and organization_id = wjsi_row.organization_id;
1725
1726 elsif (wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1727 select wms_enabled_flag
1728 into l_wms_enabled_flag
1729 from mtl_parameters
1730 where organization_id = wjsi_row.organization_id;
1731
1732 if (upper(l_wms_enabled_flag) = 'Y') then
1733 wjsi_row.material_issue_by_mo := 'N';
1734 elsif (wjsi_row.material_issue_by_mo is null) then
1735 select material_issue_by_mo
1736 into wjsi_row.material_issue_by_mo
1737 from WIP_EAM_PARAMETERS
1738 where organization_id = wjsi_row.organization_id;
1739 end if;
1740
1741 end if;
1742 end material_issue_by_mo;
1743
1744 procedure maintenance_object_type is
1745 l_serial_number_control_code NUMBER;
1746 begin
1747 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1748 wjsi_row.maintenance_object_type is not null) then
1749 wip_jsi_utils.record_ignored_column_warning('MAINTENANCE_OBJECT_TYPE');
1750 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1751 wjsi_row.maintenance_object_type is null) then
1752 if(wjsi_row.rebuild_item_id is null) then
1753 wjsi_row.maintenance_object_type := 1; --MSN
1754 else
1755 select serial_number_control_code
1756 into l_serial_number_control_code
1757 from mtl_system_items
1758 where organization_id = wjsi_row.organization_id
1759 and inventory_item_id = wjsi_row.rebuild_item_id;
1760
1761 if(l_serial_number_control_code in (2, 5, 6)) then
1762 wjsi_row.maintenance_object_type := 1; --MSN
1763 else
1764 wjsi_row.maintenance_object_type := 2; --MSI
1765 end if;
1766
1767 end if;
1768 end if;
1769
1770 exception
1771 when no_data_found then
1772 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_TYPE');
1773 wip_jsi_utils.abort_request;
1774 when others then
1775 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_TYPE');
1776 wip_jsi_utils.abort_request;
1777 end maintenance_object_type;
1778
1779 procedure maintenance_object_id is
1780 begin
1781 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1782 wjsi_row.maintenance_object_id is not null) then
1783 wip_jsi_utils.record_ignored_column_warning('MAINTENANCE_OBJECT_ID');
1784 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1785 wjsi_row.maintenance_object_id is null) then
1786 if(wjsi_row.maintenance_object_type = 1) then
1787 if(wjsi_row.rebuild_item_id is null) then
1788 select gen_object_id
1789 into wjsi_row.maintenance_object_id
1790 from mtl_serial_numbers
1791 where current_organization_id = wjsi_row.organization_id
1792 and inventory_item_id = wjsi_row.asset_group_id
1793 and serial_number = wjsi_row.asset_number;
1794 elsif(wjsi_row.rebuild_serial_number is not null) then
1795 select gen_object_id
1796 into wjsi_row.maintenance_object_id
1797 from mtl_serial_numbers
1798 where current_organization_id = wjsi_row.organization_id
1799 and inventory_item_id = wjsi_row.rebuild_item_id
1800 and serial_number = wjsi_row.rebuild_serial_number;
1801 end if;
1802 elsif(wjsi_row.maintenance_object_type = 2) then
1803 wjsi_row.maintenance_object_id := wjsi_row.rebuild_item_id;
1804 end if;
1805 end if;
1806
1807 exception
1808 when no_data_found then
1809 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_ID');
1810 wip_jsi_utils.abort_request;
1811 when others then
1812 wip_jsi_utils.record_invalid_column_error('MAINTENANCE_OBJECT_ID');
1813 wip_jsi_utils.abort_request;
1814 end maintenance_object_id;
1815
1816 procedure maintenance_object_source is begin
1817 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1818 wjsi_row.maintenance_object_source is not null) then
1819 wip_jsi_utils.record_ignored_column_warning('MAITENANCE_OBJECT_SOURCE');
1820 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1821 wjsi_row.maintenance_object_source is null) then
1822 wjsi_row.maintenance_object_source := 1; --EAM
1823 end if;
1824
1825 exception
1826 when others then
1827 wip_jsi_utils.record_invalid_column_error('MAITENANCE_OBJECT_SOURCE');
1828 wip_jsi_utils.abort_request;
1829 end maintenance_object_source;
1830
1831 procedure activity_source is
1832 l_returnMsg VARCHAR2(200);
1833 l_msgCount NUMBER;
1834 l_errMsg VARCHAR2(30);
1835 begin
1836 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1837 wjsi_row.activity_source is not null) then
1838 wip_jsi_utils.record_ignored_column_warning('ACTIVITY_SOURCE');
1839 elsif(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1840 wjsi_row.activity_source is null) then
1841 wip_eamworkorder_pvt.get_eam_act_source_default(p_api_version => 1.0,
1842 p_init_msg_list => null,
1843 p_commit => null,
1844 p_validation_level => null,
1845 p_primary_item_id => wjsi_row.primary_item_id,
1846 p_organization_id => wjsi_row.organization_id,
1847 p_maintenance_object_type => wjsi_row.maintenance_object_type,
1848 p_maintenance_object_id => wjsi_row.maintenance_object_id,
1849 p_rebuild_item_id => wjsi_row.rebuild_item_id,
1850 x_activity_source_code => wjsi_row.activity_source,
1851 x_return_status => l_returnMsg,
1852 x_msg_count => l_msgCount,
1853 x_msg_data => l_errMsg);
1854 if (l_errMsg is not null) then
1855 fnd_message.set_name('WIP', 'ACTIVITY_SOURCE');
1856 fnd_message.set_token('activity_source', wjsi_row.activity_source, false);
1857 wip_jsi_utils.record_current_error;
1858 end if;
1859 end if;
1860
1861 exception
1862 when others then
1863 wip_jsi_utils.record_invalid_column_error('ACTIVITY_SOURCE');
1864 wip_jsi_utils.abort_request;
1865 end activity_source;
1866
1867 procedure pm_schedule_id is begin
1868 if(wjsi_row.load_type not in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
1869 wjsi_row.pm_schedule_id is not null) then
1870 wip_jsi_utils.record_ignored_column_warning('PM_SCHEDULE_ID');
1871 end if;
1872 end pm_schedule_id;
1873
1874 end wip_jsi_defaulter;
[Home] [Help]