1 package body WIP_JSI_Validator as
2 /* $Header: wipjsivb.pls 120.3 2006/05/26 09:56:42 saugupta noship $ */
3
4 procedure setup;
5 procedure load_type;
6 procedure organization_id;
7 procedure job_name;
8 procedure job_id;
9 procedure entity_type;
10 procedure kanban_card_id;
11 procedure created_by;
12 procedure last_updated_by;
13 procedure start_quantity;
14 procedure net_quantity;
15 procedure firm_planned_flag;
16 procedure demand_class;
17 procedure line_id;
18 procedure schedule_group_id;
19 procedure build_sequence;
20 procedure status_type;
21 procedure processing_work_days;
22 procedure daily_production_rate;
23 procedure repetitive_schedule_id;
24 procedure primary_item_id;
25 procedure wip_supply_type;
26 procedure routing_reference_id;
27 procedure bom_reference_id;
28 procedure alternate_routing_designator;
29 procedure alternate_bom_designator;
30 procedure project_id;
31 procedure task_id;
32 procedure project_task_id;
33 procedure schedule_dates;
34 procedure scheduling_method;
35 procedure completion_subinventory;
36 procedure completion_locator_id;
37 procedure due_date;
38 procedure date_released;
39 procedure requested_start_date;
40 procedure end_item_unit_number;
41 procedure overcompletion;
42 procedure class_code;
43 procedure estimate_lead_time;
44 procedure bom_revision;
45 procedure routing_revision;
46 procedure validate_date_released; /* 2424987 */
47
48 --added for eAM
49 procedure asset_group_id;
50 procedure asset_number;
51 procedure rebuild_item_id;
52 procedure rebuild_serial_number;
53 procedure parent_wip_entity_id;
54 procedure manual_rebuild_flag;
55 procedure owning_department;
56 procedure notification_required;
57 procedure shutdown_type;
58 procedure tagout_required;
59 procedure plan_maintenance;
60 procedure work_order_type;
61 procedure activity_type;
62 procedure activity_cause;
63 procedure maintenance_object_type;
64 procedure maintenance_object_source;
65 procedure maintenance_object_id;
66 procedure pm_schedule_id;
67 procedure activity_source;
68 --end eAM
69
70 type wdj_rec_t is RECORD (status_type NUMBER,
71 entity_type NUMBER,
72 job_type NUMBER,
73 start_quantity NUMBER,
74 quantity_completed NUMBER,
75 firm_planned_flag NUMBER,
76 primary_item_id NUMBER,
77 bom_reference_id NUMBER,
78 routing_reference_id NUMBER,
79 line_id NUMBER,
80 schedule_group_id NUMBER,
81 scheduled_completion_date DATE,
82 project_id NUMBER,
83 task_id NUMBER,
84 completion_subinventory VARCHAR2(30),
85 completion_locator_id NUMBER,
86 rebuild_item_id NUMBER);
87
88 type item_rec_t is RECORD(inventory_item_id NUMBER,
89 eam_item_type NUMBER,
90 pick_components_flag VARCHAR2(1),
91 build_in_wip_flag VARCHAR2(1),
92 eng_item_flag VARCHAR2(1),
93 inventory_asset_flag VARCHAR2(1),
94 restrict_subinventories_code NUMBER,
95 restrict_locators_code NUMBER,
96 location_control_code NUMBER,
97 fixed_lead_time NUMBER,
98 variable_lead_time NUMBER);
99
100
101 wjsi_row wip_job_schedule_interface%ROWTYPE;
102 wdj_row wdj_rec_t;
103 primary_item_row item_rec_t;
104 routing_ref_row item_rec_t;
105 g_dummy NUMBER;
106 orig_org_context number ;
107
108 procedure validate is
109
110 /* Added following variables for Bug# 4184566 and also in update statement */
111
112 x_request_id number ;
113 x_program_id number ;
114 x_application_id number ;
115
116 begin
117
118 x_request_id := fnd_global.conc_request_id ;
119 x_program_id := fnd_global.conc_program_id ;
120 x_application_id := fnd_global.prog_appl_id ;
121
122 select *
123 into wjsi_row
124 from wip_job_schedule_interface
125 where rowid = wip_jsi_utils.current_rowid;
126
127 -- Save Original org context
128 orig_org_context := nvl(fnd_profile.value('ORG_ID'), -1) ;
129
130 --defaults all values in record
131 --issues any ignored value warnings
132 --sets the org context (needed for pjm validations)
133 wip_jsi_defaulter.default_values(p_wjsi_row => wjsi_row);
134
135 /* Fixed bug 3977669 */
136 if ( wjsi_row.load_type <> wip_constants.create_sched and
137 wjsi_row.build_sequence is NULL) then
138
139 if (wjsi_row.load_type = wip_constants.resched_job) then
140 select WDJ.build_sequence
141 into wjsi_row.build_sequence
142 from wip_discrete_jobs WDJ
143 where WDJ.wip_entity_id = wjsi_row.wip_entity_id ;
144 else
145 wjsi_row.build_sequence := null;
146 end if;
147
148 wjsi_row.build_sequence := WIP_JSI_Hooks.get_default_build_sequence (
149 wjsi_row.interface_id,
150 wjsi_row.build_sequence);
151 end if;
152 --select records from other tables that need to be accessed multiple times throughout the validation
153 --procedures, e.g. mtl_system_items row for the primary item
154 setup;
155
156 --only perform a subset of the validations, mostly those odd cases in which the validation procedures
157 --modify or insert date
158 if(wip_jsi_utils.validation_level in (wip_constants.mrp, wip_constants.ato)) then
159 job_name;
160 job_id;
161 entity_type;
162 schedule_group_id;
163 demand_class;
164 primary_item_id;
165 scheduling_method;
166 project_task_id;
167 project_id;
168 task_id;
169 completion_subinventory;
170 completion_locator_id;
171 class_code;
172 estimate_lead_time;
173 due_date;
174 --date_released; /*check made in validate_date_released*/
175 requested_start_date;
176 overcompletion;
177 bom_revision; --added for bug 2375060
178 routing_revision; --added for bug 2375060
179 validate_date_released; /* 2424987 */
180 else --do full validations
181 load_type;
182 organization_id;
183 job_name;
184 job_id;
185 entity_type;
186 kanban_card_id;
187 created_by;
188 last_updated_by;
189 start_quantity;
190 net_quantity;
191 firm_planned_flag;
192 repetitive_schedule_id;
193 demand_class;
194 line_id;
195 schedule_group_id;
196 build_sequence;
197 status_type;
198 processing_work_days;
199 daily_production_rate;
200 --added for EAM
201 asset_group_id;
202 asset_number;
203 rebuild_item_id;
204 maintenance_object_type;
205 maintenance_object_source;
206 rebuild_serial_number;
207 maintenance_object_id;
208 --
209 primary_item_id;
210 wip_supply_type;
211 routing_reference_id;
212 bom_reference_id;
213 alternate_routing_designator;
214 alternate_bom_designator;
215 project_task_id;
216 project_id;
217 task_id;
218 schedule_dates;
219 scheduling_method;
220 completion_subinventory;
221 completion_locator_id;
222 due_date;
223 requested_start_date;
224 end_item_unit_number;
225 overcompletion;
226 --added for EAM
227 pm_schedule_id;
228 parent_wip_entity_id;
229 --
230 class_code;
231 estimate_lead_time;
232 bom_revision;
233 routing_revision;
234 validate_date_released; /* 2424987 */
235 --added for EAM
236 owning_department;
237 activity_cause;
238 activity_source;
239 plan_maintenance;
240 notification_required;
241 work_order_type;
242 manual_rebuild_flag;
243 tagout_required;
244 shutdown_type;
245 activity_type;
246 --
247 end if;
248 update wip_job_schedule_interface
249 set created_by = wjsi_row.created_by,
250 last_updated_by = wjsi_row.last_updated_by,
251 organization_id = wjsi_row.organization_id,
252 wip_entity_id = wjsi_row.wip_entity_id,
253 job_name = wjsi_row.job_name,
254 repetitive_schedule_id = wjsi_row.repetitive_schedule_id,
255 schedule_group_id = wjsi_row.schedule_group_id,
256 line_id = wjsi_row.line_id,
257 project_id = wjsi_row.project_id,
258 task_id = wjsi_row.task_id,
259 firm_planned_flag = wjsi_row.firm_planned_flag,
260 description = wjsi_row.description,
261 status_type = wjsi_row.status_type,
262 wip_supply_type = wjsi_row.wip_supply_type,
263 class_code = wjsi_row.class_code,
264 primary_item_id = wjsi_row.primary_item_id,
265 start_quantity = wjsi_row.start_quantity,
266 net_quantity = wjsi_row.net_quantity,
267 overcompletion_tolerance_type = wjsi_row.overcompletion_tolerance_type,
268 overcompletion_tolerance_value = wjsi_row.overcompletion_tolerance_value,
269 asset_number = wjsi_row.asset_number,--20
270 asset_group_id = wjsi_row.asset_group_id,
271 parent_job_name = wjsi_row.parent_job_name,
272 parent_wip_entity_id = wjsi_row.parent_wip_entity_id,
273 rebuild_item_id = wjsi_row.rebuild_item_id,
274 rebuild_serial_number = wjsi_row.rebuild_serial_number,
275 manual_rebuild_flag = wjsi_row.manual_rebuild_flag,
276 first_unit_start_date = wjsi_row.first_unit_start_date,
277 last_unit_start_date = wjsi_row.last_unit_start_date,
278 first_unit_completion_date = wjsi_row.first_unit_completion_date,
279 last_unit_completion_date = wjsi_row.last_unit_completion_date,
280 due_date = wjsi_row.due_date,
281 requested_start_date = wjsi_row.requested_start_date,
282 processing_work_days = wjsi_row.processing_work_days,--30
283 daily_production_rate = wjsi_row.daily_production_rate,
284 header_id = wjsi_row.header_id,
285 demand_class = wjsi_row.demand_class,
286 build_sequence = wjsi_row.build_sequence,
287 routing_reference_id = wjsi_row.routing_reference_id,
288 bom_reference_id = wjsi_row.bom_reference_id,
289 alternate_routing_designator = wjsi_row.alternate_routing_designator,
290 alternate_bom_designator = wjsi_row.alternate_bom_designator,
291 bom_revision = wjsi_row.bom_revision,
292 routing_revision = wjsi_row.routing_revision,--40
293 bom_revision_date = wjsi_row.bom_revision_date,
294 routing_revision_date = wjsi_row.routing_revision_date,
295 lot_number = wjsi_row.lot_number,
296 source_code = wjsi_row.source_code,
297 source_line_id = wjsi_row.source_line_id,
298 scheduling_method = wjsi_row.scheduling_method,
299 completion_subinventory = wjsi_row.completion_subinventory,
300 completion_locator_id = wjsi_row.completion_locator_id,
301 priority = wjsi_row.priority,
302 allow_explosion = wjsi_row.allow_explosion,
303 end_item_unit_number = wjsi_row.end_item_unit_number, --must be after primary_item_id
304 owning_department = wjsi_row.owning_department,
305 notification_required = wjsi_row.notification_required,--50
306 shutdown_type = wjsi_row.shutdown_type,
307 work_order_type = wjsi_row.work_order_type,
308 tagout_required = wjsi_row.tagout_required,
309 plan_maintenance = wjsi_row.plan_maintenance,
310 activity_type = wjsi_row.activity_type,
311 activity_cause = wjsi_row.activity_cause,
312 material_issue_by_mo = wjsi_row.material_issue_by_mo,
313 maintenance_object_id = wjsi_row.maintenance_object_id,
314 maintenance_object_type = wjsi_row.maintenance_object_type,
315 maintenance_object_source = wjsi_row.maintenance_object_source,
316 REQUEST_ID = decode(x_request_id,-1,REQUEST_ID,x_request_id),
317 PROGRAM_ID = decode(x_program_id,-1,PROGRAM_ID,x_program_id),
318 PROGRAM_UPDATE_DATE = SYSDATE,
319 PROGRAM_APPLICATION_ID = decode(x_application_id,-1,PROGRAM_APPLICATION_ID, x_application_id)
320 where rowid = wip_jsi_utils.current_rowid;
321
322 -- Set Original org context since org context might have
323 -- different for pjm validation
324 if (orig_org_context <> -1) then
325 fnd_client_info.set_org_context(to_char(orig_org_context)) ;
326 end if ;
327
328 /* Fixed for Bug#3060266
329 Abort if any errors were detected. */
330 if (WIP_JSI_Utils.any_nonwarning_errors) then
331 WIP_JSI_Utils.abort_request ;
332 end if ;
333
334 end validate;
335
336 --This procedure is slightly different from the other validation procedures.
337 --It cannot use the wjsi_row global b/c it is not called during the validation
338 --phase. It must be called after the explosion phase so WO will be populated
339 --with the job operations.
340 procedure validate_serialization_op is
341
342 l_startOp NUMBER;
343 l_wipID NUMBER;
344 l_primaryItemID NUMBER;
345 l_serialOp NUMBER;
346 l_loadType NUMBER;
347 l_jobType NUMBER;
348 cursor c_ops(v_wip_id number) is
349 select operation_seq_num
350 from wip_operations
351 where wip_entity_id = v_wip_id;
352
353 l_curOpSeq NUMBER;
354 l_rtgExists boolean := false;
355 l_opFound boolean := false;
356 begin
357 select wip_entity_id, serialization_start_op, load_type, primary_item_id
358 into l_wipID, l_serialOp, l_loadType, l_primaryItemID
359 from wip_job_schedule_interface wjsi
360 where wjsi.rowid = wip_jsi_utils.current_rowid;
361
362
363 if(l_serialOp is null) then
364 --in this case, we may need to clear the serialization op if the routing was re-exploded
365 if(l_loadType = wip_constants.resched_job) then
366 update wip_discrete_jobs wdj
367 set serialization_start_op = null
368 where wip_entity_id = l_wipID
369 and serialization_start_op <> 1
370 and not exists(select 1
371 from wip_operations wo
372 where wo.wip_entity_id = wdj.wip_entity_id
373 and wo.operation_seq_num = wdj.serialization_start_op);
374 end if;
375 return;
376 end if;
377
378 --if serial op provided, the load type must be discrete or ns job
379 if(l_loadType not in (wip_constants.create_job,
380 wip_constants.create_ns_job,
381 wip_constants.resched_job)) then
382 raise fnd_api.g_exc_unexpected_error;
383 end if;
384
385 --job must have an assembly, and the assembly must be serial controlled (predefined).
386 select 1
387 into g_dummy
388 from wip_discrete_jobs wdj, mtl_system_items msi
389 where wdj.primary_item_id = msi.inventory_item_id
390 and wdj.organization_id = msi.organization_id
391 and wdj.wip_entity_id = l_wipID
392 and msi.serial_number_control_code = wip_constants.full_sn;
393
394 open c_ops(v_wip_id => l_wipID);
395
396 loop
397 fetch c_ops into l_curOpSeq;
398 exit when c_ops%NOTFOUND;
399 l_rtgExists := true;
400 if(l_curOpSeq = l_serialOp) then
401 l_opFound := true;
402 exit;
403 end if;
404 end loop;
405
406 close c_ops;
407
408 --The routing exists, but an invalid op seq was provided
409 if(l_rtgExists and not l_opFound) then
410 raise fnd_api.g_exc_unexpected_error;
411 end if;
412
413 --If no routing exsts, the serialization op must be 1.
414 if(not l_rtgExists and l_serialOp <> 1) then
415 raise fnd_api.g_exc_unexpected_error;
416 end if;
417
418 --job must be unreleased to change the serialization op on a
419 --reschedule request. This is to guarantee no txns
420 --have taken place.
421 if(l_loadType = wip_constants.resched_job) then
422 select 1
423 into g_dummy
424 from wip_discrete_jobs
425 where wip_entity_id = l_wipID
426 and status_type = wip_constants.unreleased;
427 end if;
428 exception
429 when others then
430 wip_jsi_utils.record_error('WIP_ML_SERIAL_START_OP');
431 wip_jsi_utils.abort_request;
432 end validate_serialization_op;
433
434
435 /* private procedures */
436 procedure populate_item(p_item_id in number,
437 p_org_id in number,
438 x_item_row out nocopy item_rec_t) is begin
439 select inventory_item_id,
440 nvl(eam_item_type, -1),
441 pick_components_flag,
442 build_in_wip_flag,
443 eng_item_flag,
444 inventory_asset_flag,
445 restrict_subinventories_code,
446 restrict_locators_code,
447 location_control_code,
448 fixed_lead_time,
449 variable_lead_time
450 into x_item_row.inventory_item_id,
451 x_item_row.eam_item_type,
452 x_item_row.pick_components_flag,
453 x_item_row.build_in_wip_flag,
454 x_item_row.eng_item_flag,
455 x_item_row.inventory_asset_flag,
456 x_item_row.restrict_subinventories_code,
457 x_item_row.restrict_locators_code,
458 x_item_row.location_control_code,
459 x_item_row.fixed_lead_time,
460 x_item_row.variable_lead_time
461 from mtl_system_items
462 where inventory_item_id = p_item_id
463 and organization_id = p_org_id;
464 end populate_item;
465
466 procedure setup is begin
467 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
468 select status_type,
469 entity_type,
470 job_type,
471 start_quantity,
472 quantity_completed,
473 firm_planned_flag,
474 wdj.primary_item_id,
475 bom_reference_id,
476 routing_reference_id,
477 line_id,
478 schedule_group_id,
479 scheduled_completion_date,
480 project_id,
481 task_id,
482 completion_subinventory,
483 completion_locator_id,
484 rebuild_item_id
485 into wdj_row.status_type,
486 wdj_row.entity_type,
487 wdj_row.job_type,
488 wdj_row.start_quantity,
489 wdj_row.quantity_completed,
490 wdj_row.firm_planned_flag,
491 wdj_row.primary_item_id,
492 wdj_row.bom_reference_id,
493 wdj_row.routing_reference_id,
494 wdj_row.line_id,
495 wdj_row.schedule_group_id,
496 wdj_row.scheduled_completion_date,
497 wdj_row.project_id,
498 wdj_row.task_id,
499 wdj_row.completion_subinventory,
500 wdj_row.completion_locator_id,
501 wdj_row.rebuild_item_id
502 from wip_discrete_jobs wdj, wip_entities we
503 where wdj.wip_entity_id = wjsi_row.wip_entity_id
504 and we.wip_entity_id = wjsi_row.wip_entity_id;
505 if(wdj_row.primary_item_id is not null) then
506 populate_item(p_item_id => wdj_row.primary_item_id,
507 p_org_id => wjsi_row.organization_id,
508 x_item_row => primary_item_row);
509 end if;
510 if(wdj_row.routing_reference_id is not null) then
511 populate_item(p_item_id => wdj_row.routing_reference_id,
512 p_org_id => wjsi_row.organization_id,
513 x_item_row => routing_ref_row);
514 end if;
515 else --job/sched creation
516 if(wjsi_row.primary_item_id is not null) then
517 populate_item(p_item_id => wjsi_row.primary_item_id,
518 p_org_id => wjsi_row.organization_id,
519 x_item_row => primary_item_row);
520 end if;
521 if(wjsi_row.routing_reference_id is not null) then
522 populate_item(p_item_id => wjsi_row.routing_reference_id,
523 p_org_id => wjsi_row.organization_id,
524 x_item_row => routing_ref_row);
525 end if;
526 end if;
527 exception
528 when others then
529 null;
530 end setup;
531
532 procedure load_type is begin
533 if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
534 wip_constants.create_ns_job, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
535 raise fnd_api.g_exc_unexpected_error;
536 end if;
537 if((wjsi_row.load_type = wip_constants.resched_job and wdj_row.entity_type = wip_constants.eam) or
538 (wjsi_row.load_type = wip_constants.resched_eam_job and wdj_row.entity_type <> wip_constants.eam)) then
539 raise fnd_api.g_exc_unexpected_error;
540 end if;
541 exception
542 when others then
543 wip_jsi_utils.record_error('WIP_ML_LOAD_TYPE');
544 wip_jsi_utils.abort_request;
545 end load_type;
546
547 procedure organization_id is
548 l_disable_date date;
549 begin
550 -- Bug 4890215. Performance Fix
551 -- sugupta 26th-May-2006
552 /*
553 select ood.disable_date
554 into l_disable_date
555 from wip_parameters wp, mtl_parameters mp, org_organization_definitions ood
556 where wp.organization_id = mp.organization_id
557 and wp.organization_id = ood.organization_id
558 and wp.organization_id = wjsi_row.organization_id;
559 */
560 SELECT ood.date_to disable_date
561 INTO l_disable_date
562 FROM wip_parameters wp,
563 mtl_parameters mp ,
564 hr_organization_units ood
565 WHERE wp.organization_id = mp.organization_id
566 and wp.organization_id = ood.organization_id
567 and wp.organization_id = wjsi_row.organization_id;
568
569 if(l_disable_date < sysdate) then
570 raise fnd_api.g_exc_unexpected_error;
571 end if;
572 exception
573 when others then
574 wip_jsi_utils.record_error('WIP_ML_ORGANIZATION_ID');
575 wip_jsi_utils.abort_request;
576 end organization_id;
577
578 procedure job_name is
579 l_count NUMBER;
580 begin
581 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
582 wjsi_row.job_name is not null) then
583 select count(*)
584 into l_count
585 from wip_entities
586 where wip_entity_name = wjsi_row.job_name
587 and organization_id = wjsi_row.organization_id;
588 if(l_count > 0) then
589 raise fnd_api.g_exc_unexpected_error;
590 end if;
591 end if;
592 exception
593 when others then
594 wip_jsi_utils.record_error('WIP_ML_JOB_NAME');
595 wip_jsi_utils.abort_request;
596 end job_name;
597
598 procedure entity_type is
599 begin
600 if (wjsi_row.load_type in (wip_constants.create_eam_job,
601 wip_constants.resched_eam_job) or
602 wdj_row.entity_type in (WIP_CONSTANTS.LOTBASED, WIP_CONSTANTS.EAM)) then
603 raise fnd_api.g_exc_unexpected_error;
604 end if;
605
606 exception
607 when others then
608 wip_jsi_utils.record_error('WIP_ML_ENTITY_TYPE');
609 wip_jsi_utils.abort_request;
610 end entity_type;
611
612 procedure job_id is
613 l_count NUMBER;
614 begin
615 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
616 select count(*)
617 into l_count
618 from wip_entities
619 where wip_entity_id = wjsi_row.wip_entity_id;
620 if(l_count > 0) then
621 raise fnd_api.g_exc_unexpected_error;
622 end if;
623 end if;
624 exception
625 when others then
626 WIP_JSI_Utils.record_error('WIP_ML_WIP_ENTITY_ID') ;
627 wip_jsi_utils.abort_request;
628 end job_id;
629
630 procedure kanban_card_id is
631 l_doc_type NUMBER;
632 l_doc_header_id NUMBER;
633 l_status VARCHAR2(100);
634 l_msg VARCHAR2(30) := 'WIP_ML_KB_SRC_NOT_INV';
635 begin
636 if(wjsi_row.kanban_card_id is null) then
637 return;
638 end if;
639
640 if (wip_jsi_utils.validation_level <> wip_constants.inv) then
641 raise fnd_api.g_exc_unexpected_error;
642 end if ;
643
644 l_msg := 'WIP_ML_KB_UPDATE_FAILED';
645 l_doc_header_id := wjsi_row.wip_entity_id ;
646 if(wjsi_row.load_type = wip_constants.create_job) then
647 l_doc_type := INV_Kanban_PVT.G_doc_type_Discrete_Job ;
648 elsif(wjsi_row.load_type = wip_constants.create_sched) then
649 l_doc_type := INV_Kanban_PVT.G_doc_type_Rep_Schedule ;
650 else
651 raise fnd_api.g_exc_unexpected_error;
652 end if;
653
654 -- Tell Inventory to update the kanban card's supply status.
655 -- Abort this request if unsuccessful.
656 begin
657 inv_kanban_pvt.update_card_supply_status (
658 x_return_status => l_status,
659 p_kanban_card_id => wjsi_row.kanban_card_id,
660 p_supply_status => INV_Kanban_PVT.G_Supply_Status_InProcess,
661 p_document_type => l_doc_type,
662 p_document_header_id => l_doc_header_id);
663 exception
664 when others then
665 l_status := null ;
666 end ;
667
668 if((l_status is null) or (l_status <> fnd_api.g_ret_sts_success)) then
669 raise fnd_api.g_exc_unexpected_error;
670 end if ;
671 exception
672 when others then
673 WIP_JSI_Utils.record_error(l_msg) ;
674 WIP_JSI_Utils.abort_request ;
675 end kanban_card_id ;
676
677 procedure created_by is begin
678 select 1
679 into g_dummy
680 from fnd_user
681 where user_id = wjsi_row.created_by
682 and sysdate between start_date and nvl(end_date, sysdate);
683 exception
684 when others then
685 wip_jsi_utils.record_error('WIP_ML_CREATED_BY');
686 WIP_JSI_Utils.abort_request ;
687 end created_by;
688
689 procedure last_updated_by is begin
690 select 1
691 into g_dummy
692 from fnd_user
693 where user_id = wjsi_row.last_updated_by
694 and sysdate between start_date and nvl(end_date, sysdate);
695 exception
696 when others then
697 wip_jsi_utils.record_error('WIP_ML_LAST_UPDATED_BY');
698 WIP_JSI_Utils.abort_request ;
699 end last_updated_by;
700
701 procedure start_quantity is
702 l_reserved_qty NUMBER;
703 l_reservation_count NUMBER;
704 l_msg VARCHAR2(30) := 'WIP_ML_START_QUANTITY';
705 begin
706 if(wjsi_row.start_quantity < 0) then
707 raise fnd_api.g_exc_unexpected_error;
708 elsif(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
709 wjsi_row.start_quantity is null) then
710 raise fnd_api.g_exc_unexpected_error;
711 elsif(wjsi_row.load_type = wip_constants.create_job and wjsi_row.start_quantity = 0) then
712 raise fnd_api.g_exc_unexpected_error;
713 elsif(wjsi_row.load_type = wip_constants.resched_job) then
714 if(wjsi_row.start_quantity = 0 and wdj_row.job_type = wip_constants.standard) then
715 raise fnd_api.g_exc_unexpected_error;
716 elsif(wjsi_row.start_quantity < wdj_row.quantity_completed) then
717 l_msg := 'WIP_ML_RESCHEDULE_QUANTITY';
718 raise fnd_api.g_exc_unexpected_error;
719 else
720 select sum(primary_quantity), count(*)
721 into l_reserved_qty, l_reservation_count
722 from wip_reservations_v
723 where wip_entity_id = wjsi_row.wip_entity_id
724 and organization_id = wjsi_row.organization_id;
725 if(l_reservation_count > 0 and
726 wjsi_row.start_quantity < l_reserved_qty) then
727 l_msg := 'WIP_ML_RESCHEDULE_QUANTITY';
728 raise fnd_api.g_exc_unexpected_error;
729 end if;
730 end if;
731 end if;
732 exception
733 when others then
734 wip_jsi_utils.record_error(l_msg);
735 wip_jsi_utils.abort_request;
736 end start_quantity;
737
738 procedure net_quantity is begin
739
740 if(wjsi_row.load_type <> wip_constants.create_sched and wjsi_row.net_quantity is not null) then
741 if(wjsi_row.net_quantity not between 0 and wjsi_row.start_quantity) then
742 raise fnd_api.g_exc_unexpected_error;
743 --can't have net qty when creating a ns job w/no item or rescheduling a ns job w/no item
744 elsif(wjsi_row.net_quantity is not null and wjsi_row.net_quantity <> 0 and
745 ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is null) or
746 (wjsi_row.load_type = wip_constants.resched_job and
747 wdj_row.job_type = wip_constants.nonstandard and
748 wdj_row.primary_item_id is null))) then
749 raise fnd_api.g_exc_unexpected_error;
750 end if;
751 end if;
752 exception
753 when others then
754 wip_jsi_utils.record_error('WIP_ML_NET_QUANTITY');
755 wip_jsi_utils.abort_request;
756
757 end net_quantity;
758
759 procedure firm_planned_flag is begin
760 if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761 (wjsi_row.firm_planned_flag = wip_constants.yes and
762 wdj_row.job_type = wip_constants.nonstandard and
763 wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764 (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
765 raise fnd_api.g_exc_unexpected_error;
766 end if;
767 exception
768 when others then
769 wip_jsi_utils.record_error('WIP_ML_FIRM_PLANNED_FLAG');
770 end firm_planned_flag;
771
772 procedure repetitive_schedule_id is
773 l_rep_sched_count NUMBER;
774 begin
775 if(wjsi_row.load_type = wip_constants.create_sched) then
776 if(wjsi_row.repetitive_schedule_id is null) then
777 raise fnd_api.g_exc_unexpected_error;
778 else
779 select count(*)
780 into l_rep_sched_count
781 from wip_repetitive_schedules
782 where repetitive_schedule_id = wjsi_row.repetitive_schedule_id;
783
784 if(l_rep_sched_count > 0) then
785 raise fnd_api.g_exc_unexpected_error;
786 end if;
787 end if;
788 end if;
789 exception
790 when others then
791 wip_jsi_utils.record_error('WIP_ML_REPETITIVE_SCHEDULE_ID');
792 wip_jsi_utils.abort_request;
793 end repetitive_schedule_id;
794
795 procedure demand_class is begin
796 if(wjsi_row.demand_class is not null and wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
797 wip_constants.create_ns_job, wip_constants.create_eam_job)) then
798 select 1
799 into g_dummy
800 from so_demand_classes_active_v
801 where demand_class_code = wjsi_row.demand_class;
802 end if;
803 exception
804 when others then
805 wip_jsi_utils.record_error('WIP_ML_DEMAND_CLASS');
806 end demand_class;
807
808 procedure line_id is begin
809 if(wjsi_row.load_type = wip_constants.create_sched and wjsi_row.line_id is null) then
810 raise fnd_api.g_exc_unexpected_error;
811 elsif(wjsi_row.line_id is not null) then
812 select 1
813 into g_dummy
814 from wip_lines_val_v
815 where line_id = wjsi_row.line_id
816 and organization_id = wjsi_row.organization_id;
817 end if;
818 exception
819 when others then
820 wip_jsi_utils.record_error('WIP_ML_LINE_ID');
821 wip_jsi_utils.abort_request;
822 end line_id;
823
824 procedure schedule_group_id is begin
825 if(wjsi_row.schedule_group_id is not null and
826 wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job, wip_constants.create_ns_job)) then
827 select 1
828 into g_dummy
829 from wip_schedule_groups_val_v
830 where schedule_group_id = wjsi_row.schedule_group_id
831 and organization_id = wjsi_row.organization_id;
832 end if; exception
833 when others then
834 wip_jsi_utils.record_error('WIP_ML_SCHEDULE_GROUP');
835 end schedule_group_id;
836
837 procedure build_sequence is
838 l_retval boolean;
839 begin
840 if(wjsi_row.build_sequence is not null and
841 wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job, wip_constants.create_ns_job)) then
842 l_retval := wip_validate.build_sequence(p_build_sequence => wjsi_row.build_sequence,
843 p_wip_entity_id => wjsi_row.wip_entity_id,
844 p_organization_id => wjsi_row.organization_id,
845 p_line_id => nvl(wjsi_row.line_id, wdj_row.line_id),
846 p_schedule_group_id => nvl(wjsi_row.schedule_group_id, wdj_row.schedule_group_id));
847 if(not l_retval) then
848 raise fnd_api.g_exc_unexpected_error;
849 end if;
850 end if;
851 exception
852 when others then
853 wip_jsi_utils.record_error('WIP_ML_BUILD_SEQUENCE');
854 end build_sequence;
855
856 procedure status_type is
857 l_msg varchar2(30) := 'WIP_ML_STATUS_TYPE';
858 /* bug 2308832 - Added check_so_link cursor for sales order checking
859 of complete and cancelled statuses of jobs */
860 l_qty_reserved number := 0;
861 l_Primary_Item_Id number ; /* for Bug 2792736 */
862 l_propagate_job_change_to_po NUMBER;
863 l_return_status VARCHAR2(1);
864
865 CURSOR CHECK_SO_LINK IS
866 SELECT NVL(SUM(PRIMARY_QUANTITY),0)
867 FROM WIP_RESERVATIONS_V
868 WHERE WIP_ENTITY_ID = wjsi_row.Wip_Entity_Id
869 AND INVENTORY_ITEM_ID = nvl(wjsi_row.primary_item_id,l_Primary_Item_Id )
870 AND ORGANIZATION_ID = wjsi_row.Organization_Id;
871
872 begin
873 --on job creation, status must be unreleased, released, on hold, or draft
874 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
875 wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
876 raise fnd_api.g_exc_unexpected_error;
877
878 --on reschedule, status must be unreleased, released, complete charges allowed, on hold, cancelled, or pending scheduling
879 elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
880
881 --if not changing the status type, skip status type validation processing
882 if(wjsi_row.status_type is null) then
883 return;
884 end if;
885
886 if(wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
887 wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched) and
888 -- new comp_nochrg status is allowed in eam if old status is comp_chrg
889 (wjsi_row.status_type <> wip_constants.comp_nochrg or wdj_row.status_type <> wip_constants.comp_chrg
890 or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
891 raise fnd_api.g_exc_unexpected_error;
892
893 --additionally, you can not reschedule jobs that are in certain statuses
894 elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895 wip_constants.hold, wip_constants.cancelled)) then
896 if(wjsi_row.load_type = wip_constants.resched_eam_job and
897 --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898 (wdj_row.status_type in (wip_constants.pend_sched) or
899 -- draft cannot be changed to cancel for eam jobs
900 (wdj_row.status_type in (wip_constants.draft) and
901 wjsi_row.status_type not in (wip_constants.cancelled)))) then
902 --these statuses ok for rescheduling eam jobs
903 return;
904 end if;
905 l_msg := 'WIP_ML_WIP_DJ_RESCHEDULE';
906 raise fnd_api.g_exc_unexpected_error;
907 end if;
908 /* Bug 2308832 - Added below if condition for sales order/PO checking */
909 IF (wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
910 WIP_CONSTANTS.COMP_CHRG,
911 WIP_CONSTANTS.COMP_NOCHRG)) THEN
912
913 /* Bug#2893368 - Added if condition to check whether primary item id
914 in wip_job_schedule_interface is null */
915 if ( wjsi_row.primary_item_id is null ) then
916 select primary_item_id /* for Bug2792736 */
917 into l_Primary_Item_Id
918 from wip_discrete_jobs
919 where wip_entity_id = wjsi_row.wip_entity_id
920 and organization_id = wjsi_row.organization_id;
921 end if;
922
923 OPEN CHECK_SO_LINK;
924 FETCH CHECK_SO_LINK INTO l_qty_reserved;
925 CLOSE CHECK_SO_LINK;
926
927 /* Bug 3927677 -> Give warning when job linked to SO is being completed,
928 * and give error when job linked to SO is being cancelled or
929 * complete-no charge.
930 */
931 if ( l_qty_reserved > 0 and
932 wjsi_row.status_type in (wip_constants.cancelled,
933 wip_constants.comp_nochrg)) then
934 l_msg := 'WIP_CANT_CANCEL_SO';
935 raise fnd_api.g_exc_unexpected_error;
936 end if;
937
938 if ( l_qty_reserved > 0 and
939 wjsi_row.status_type = wip_constants.comp_chrg) then
940 fnd_message.set_name('WIP','WIP_SO_EXISTS');
941 wip_jsi_utils.record_current_error(p_warning_only => true);
942 end if;
943
944 /* End fix of 3927677 */
945
946 if (wip_osp.po_req_exists ( wjsi_row.wip_entity_id,
947 null,
948 wjsi_row.organization_id,
949 null,
950 wip_constants.discrete
951 ) = TRUE ) then
952 IF (po_code_release_grp.Current_Release >=
953 po_code_release_grp.PRC_11i_Family_Pack_J) THEN
954
955 SELECT propagate_job_change_to_po
956 INTO l_propagate_job_change_to_po
957 FROM wip_parameters wp
958 WHERE organization_id = wjsi_row.organization_id;
959
960 IF(l_propagate_job_change_to_po = WIP_CONSTANTS.YES AND
961 wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
962 WIP_CONSTANTS.COMP_NOCHRG)) THEN
963 -- cancel PO/requisition associated to the job if cancel or
964 -- complete-no-charge
965 wip_osp.cancelPOReq(p_job_id => wjsi_row.wip_entity_id,
966 p_org_id => wjsi_row.organization_id,
967 x_return_status => l_return_status);
968
969 IF(l_return_status <> fnd_api. g_ret_sts_success) THEN
970 -- If we are unable to cancel all PO/requisition associated
971 -- to this job, we will try to cancel as much as we can,
972 -- then user need to manually cancel the rest.
973 po_warning_flag := WIP_CONSTANTS.YES;
974 wip_jsi_utils.record_current_error(p_warning_only => true);
975 END IF; -- check return status
976 ELSE
977 -- propagate_job_change_to_po is manual or job status is
978 -- 'Complete'
979 po_warning_flag := WIP_CONSTANTS.YES;
980 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
981 wip_jsi_utils.record_current_error(p_warning_only => true);
982 END IF;
983 ELSE
984 -- customer does not have PO patchset J onward, so behave the
985 -- old way
986 po_warning_flag := WIP_CONSTANTS.YES;
987 fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
988 wip_jsi_utils.record_current_error(p_warning_only => true);
989 END IF;
990 END IF; -- PO/requisition exists
991 END IF; -- status is either 'Cancel','Complete', or 'Complete-no-charge'
992
993 /* Bug 3032515 - Added validation to prevent updation to completed/
994 cancelled/completed-no charges/closed jobs through planner module
995 for which source code is populated as MSC */
996 if ( wjsi_row.source_code = 'MSC' and wdj_row.status_type in
997 (wip_constants.comp_chrg, wip_constants.comp_nochrg,
998 wip_constants.cancelled, wip_constants.closed) ) then
999 wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
1000 wip_jsi_utils.abort_request;
1001 end if;
1002
1003 -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
1004 if ( wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED and
1005 wdj_row.status_type <> WIP_CONSTANTS.UNRELEASED and
1006 WIP_PICKING_PUB.Is_Job_Pick_Released(
1007 p_wip_entity_id => wjsi_row.wip_entity_id,
1008 p_org_id => wjsi_row.organization_id)) then
1009 l_msg := 'WIP_UNRLS_JOB/SCHED';
1010 raise fnd_api.g_exc_unexpected_error;
1011 end if;
1012 end if; -- JOB RESCHED : END
1013
1014 exception
1015 when others then
1016 wip_jsi_utils.record_error(l_msg);
1017 wip_jsi_utils.abort_request;
1018 end status_type;
1019
1020 procedure processing_work_days is begin
1021 if(wjsi_row.load_type = wip_constants.create_sched and
1022 (wjsi_row.processing_work_days <= 0 or wjsi_row.processing_work_days is null)) then
1023 raise fnd_api.g_exc_unexpected_error;
1024 end if;
1025 exception
1026 when others then
1027 wip_jsi_utils.record_error('WIP_ML_PROCESSING_WORK_DAYS');
1028 end processing_work_days;
1029
1030 procedure daily_production_rate is
1031 l_max_line_rate NUMBER;
1032 begin
1033 if(wjsi_row.load_type = wip_constants.create_sched) then
1034 if(wjsi_row.daily_production_rate <= 0 or wjsi_row.daily_production_rate is null) then
1035 raise fnd_api.g_exc_unexpected_error;
1036 end if;
1037
1038 select daily_maximum_rate
1039 into l_max_line_rate
1040 from wip_lines_val_v
1041 where line_id = wjsi_row.line_id;
1042
1043 if(l_max_line_rate < wjsi_row.daily_production_rate) then
1044 fnd_message.set_name('WIP', 'WIP_PROD_RATE_WARNING');
1045 fnd_message.set_token('ENTITY1', l_max_line_rate);
1046 wip_jsi_utils.record_current_error(p_warning_only => true);
1047 end if;
1048 end if;
1049 exception
1050 when others then
1051 wip_jsi_utils.record_error('WIP_ML_DAILY_PRODUCTION_RATE');
1052 end daily_production_rate;
1053
1054 procedure primary_item_id is
1055 l_see_eng_items_flag VARCHAR2(1);
1056 l_msg VARCHAR2(30);
1057 l_start_date DATE;
1058 l_end_date DATE;
1059 l_dummy NUMBER;
1060 X_Eng_Items_Flag NUMBER;
1061 begin
1062 l_see_eng_items_flag := fnd_profile.value('WIP_SEE_ENG_ITEMS');
1063
1064 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1065 wip_constants.create_ns_job)) then
1066 if(wjsi_row.primary_item_id is null and wjsi_row.load_type <> wip_constants.create_ns_job) then
1067 l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1068 raise fnd_api.g_exc_unexpected_error;
1069 elsif(wjsi_row.primary_item_id is not null) then
1070 if(primary_item_row.build_in_wip_flag <> 'Y' or
1071 primary_item_row.pick_components_flag <> 'N' or
1072 (l_see_eng_items_flag = wip_constants.no and primary_item_row.eng_item_flag = 'Y')) then
1073 l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1074 raise fnd_api.g_exc_unexpected_error;
1075 end if;
1076 if(wjsi_row.load_type = wip_constants.create_sched) then
1077 l_msg := 'WIP_ML_REPETITIVE_ITEM';
1078 select 1
1079 into g_dummy
1080 from wip_repetitive_items
1081 where line_id = wjsi_row.line_id
1082 and primary_item_id = wjsi_row.primary_item_id
1083 and organization_id = wjsi_row.organization_id;
1084 end if;
1085 end if;
1086
1087 if(wjsi_row.primary_item_id is not null) then
1088 X_Eng_Items_Flag := to_number(FND_PROFILE.value('WIP_SEE_ENG_ITEMS'));
1089 begin
1090 select 1
1091 into l_dummy
1092 from MTL_SYSTEM_ITEMS msi
1093 where msi.inventory_item_id = wjsi_row.primary_item_id
1094 and msi.organization_id= wjsi_row.organization_id
1095 and msi.replenish_to_order_flag = 'Y'
1096 and msi.bom_item_type = 4
1097 and not exists
1098 (SELECT COMMON_BILL_SEQUENCE_ID
1099 FROM BOM_BILL_OF_MATERIALS
1100 WHERE ASSEMBLY_ITEM_ID = wjsi_row.primary_item_id
1101 AND ORGANIZATION_ID = wjsi_row.organization_id
1102 AND nvl(ALTERNATE_BOM_DESIGNATOR,'none') =
1103 nvl(wjsi_row.alternate_bom_designator,'none')
1104 AND (ASSEMBLY_TYPE = 1 OR X_Eng_Items_Flag = 1));
1105 exception
1106 when no_data_found then
1107 l_dummy := 0;
1108 end;
1109
1110 if (l_dummy = 1) then
1111 wip_jsi_utils.record_error('WIP_ML_ATO_ITEM_NO_BOM');
1112 wip_jsi_utils.abort_request;
1113 end if;
1114 end if;
1115 elsif(wjsi_row.load_type = wip_constants.create_eam_job and
1116 wjsi_row.primary_item_id is not null) then
1117 l_msg := 'WIP_ML_EAM_ACTIVITY';
1118
1119 if(primary_item_row.eam_item_type <> 2) then
1120 raise fnd_api.g_exc_unexpected_error;
1121 end if;
1122
1123 if(wjsi_row.maintenance_object_type = 1) then
1124 if(wjsi_row.rebuild_item_id is null) then
1125 select start_date_active, end_date_active
1126 into l_start_date, l_end_date
1127 from mtl_eam_asset_activities
1128 where asset_activity_id = wjsi_row.primary_item_id
1129 and organization_id = wjsi_row.organization_id
1130 and inventory_item_id = wjsi_row.asset_group_id
1131 and serial_number = wjsi_row.asset_number;
1132 else
1133 select min(start_date_active), min(end_date_active)
1134 into l_start_date, l_end_date
1135 from mtl_eam_asset_activities
1136 where asset_activity_id = wjsi_row.primary_item_id
1137 and organization_id = wjsi_row.organization_id
1138 and inventory_item_id = wjsi_row.rebuild_item_id
1139 and serial_number = wjsi_row.rebuild_serial_number;
1140 end if;
1141 elsif(wjsi_row.maintenance_object_type = 2 and
1142 wjsi_row.rebuild_item_id is not null) then
1143
1144 select min(start_date_active), min(end_date_active)
1145 into l_start_date, l_end_date
1146 from mtl_eam_asset_activities
1147 where asset_activity_id = wjsi_row.primary_item_id
1148 and organization_id = wjsi_row.organization_id
1149 and inventory_item_id = wjsi_row.rebuild_item_id;
1150
1151 elsif(wjsi_row.maintenance_object_type = 3 and
1152 wjsi_row.rebuild_item_id is not null) then
1153
1154 select min(start_date_active), min(end_date_active)
1155 into l_start_date, l_end_date
1156 from mtl_eam_asset_activities
1157 where asset_activity_id = wjsi_row.primary_item_id
1158 and organization_id = wjsi_row.organization_id
1159 and inventory_item_id = wjsi_row.rebuild_item_id;
1160 end if;
1161
1162 if(l_start_date is not null and
1163 l_start_date > nvl(wjsi_row.first_unit_start_date, wjsi_row.last_unit_completion_date)) then
1164 raise fnd_api.g_exc_unexpected_error;
1165 end if;
1166 if(l_end_date is not null and
1167 l_end_date < nvl(wjsi_row.last_unit_completion_date, wjsi_row.first_unit_start_date)) then
1168 raise fnd_api.g_exc_unexpected_error;
1169 end if;
1170
1171 end if;
1172 exception
1173 when others then
1174 wip_jsi_utils.record_error(l_msg);
1175 wip_jsi_utils.abort_request;
1176 end primary_item_id;
1177
1178 procedure wip_supply_type is
1179 l_routing_count NUMBER;
1180 begin
1181 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1182 if(wjsi_row.wip_supply_type not in (wip_constants.push, wip_constants.assy_pull, wip_constants.op_pull,
1183 wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184 wip_constants.based_on_bom)) then
1185 --not a valid supply type
1186 raise fnd_api.g_exc_unexpected_error;
1187 elsif(wjsi_row.load_type = wip_constants.create_ns_job and
1188 wjsi_row.primary_item_id is null and
1189 wjsi_row.wip_supply_type in (wip_constants.assy_pull, wip_constants.op_pull)) then
1190 --can't have pull components for ns job w/no assy
1191 raise fnd_api.g_exc_unexpected_error;
1192 elsif(wjsi_row.wip_supply_type = wip_constants.op_pull) then
1193 select count(*)
1194 into l_routing_count
1195 from bom_operational_routings
1196 where organization_id = wjsi_row.organization_id
1197 and assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id, wjsi_row.primary_item_id)
1198 and nvl(alternate_routing_designator, '@@@') = nvl(wjsi_row.alternate_routing_designator, '@@@')
1199 and nvl(cfm_routing_flag, 2) = 2; --ignore flow routings
1200 if(l_routing_count = 0) then
1201 --can't have op pulls if no routing exists!
1202 raise fnd_api.g_exc_unexpected_error;
1203 end if;
1204 end if;
1205 end if;
1206 exception
1207 when others then
1208 wip_jsi_utils.record_error('WIP_ML_WIP_SUPPLY_TYPE');
1209 end wip_supply_type;
1210
1211 procedure routing_reference_id is begin
1212 if(wjsi_row.load_type = wip_constants.create_ns_job and
1213 wjsi_row.routing_reference_id is not null and
1214 (routing_ref_row.build_in_wip_flag <> 'Y' or
1215 routing_ref_row.pick_components_flag <> 'N' or
1216 routing_ref_row.eng_item_flag <> 'N')) then
1217 raise fnd_api.g_exc_unexpected_error;
1218 end if;
1219 exception
1220 when others then
1221 wip_jsi_utils.record_error('WIP_ML_ROUTING_REFERENCE_ID');
1222 wip_jsi_utils.abort_request;
1223 end routing_reference_id;
1224
1225 procedure bom_reference_id is begin
1226 if(wjsi_row.load_type = wip_constants.create_ns_job and
1227 wjsi_row.bom_reference_id is not null) then
1228 select 1
1229 into g_dummy
1230 from mtl_system_items
1231 where inventory_item_id = wjsi_row.bom_reference_id
1232 and organization_id = wjsi_row.organization_id
1233 and build_in_wip_flag = 'Y'
1234 and pick_components_flag = 'N'
1235 and eng_item_flag = 'N';
1236 end if;
1237 exception
1238 when others then
1239 wip_jsi_utils.record_error('WIP_ML_BOM_REFERENCE_ID');
1240 wip_jsi_utils.abort_request;
1241 end bom_reference_id;
1242
1243 procedure alternate_routing_designator is
1244 l_is_flow_rtg NUMBER;
1245 l_rtg_item_id NUMBER;
1246 l_msg VARCHAR2(30);
1247 begin
1248 if(wjsi_row.alternate_routing_designator is not null) then
1249 if(wjsi_row.load_type = wip_constants.create_ns_job) then
1250 l_rtg_item_id := wjsi_row.routing_reference_id;
1251 elsif(wjsi_row.load_type = wip_constants.resched_job) then
1252 if(wdj_row.job_type = wip_constants.standard) then
1253 l_rtg_item_id := wdj_row.primary_item_id;
1254 else
1255 l_rtg_item_id := wdj_row.routing_reference_id;
1256 end if;
1257 else
1258 l_rtg_item_id := wjsi_row.primary_item_id;
1259 end if;
1260 l_is_flow_rtg := wip_cfm_filter.org_item_alt_is_cfm(wjsi_row.organization_id,
1261 l_rtg_item_id,
1262 wjsi_row.alternate_routing_designator);
1263 if(l_is_flow_rtg = wip_constants.yes) then
1264 l_msg := 'WIP_ERROR_CHOSEN_RTG_IS_CFM';
1265 raise fnd_api.g_exc_unexpected_error;
1266 end if;
1267 l_msg := 'WIP_ML_ALTERNATE_ROUTING';
1268 select 1
1269 into g_dummy
1270 from bom_routing_alternates_v
1271 where assembly_item_id = l_rtg_item_id
1272 and alternate_routing_designator = wjsi_row.alternate_routing_designator
1273 and organization_id = wjsi_row.organization_id
1274 and nvl(cfm_routing_flag, 2) = 2 --ignore flow routings
1275 and routing_type = 1;
1276 end if;
1277 exception
1278 when others then
1279 wip_jsi_utils.record_error(l_msg);
1280 end alternate_routing_designator;
1281
1282 procedure alternate_bom_designator is
1283 l_bom_item_id NUMBER;
1284 begin
1285 if(wjsi_row.alternate_bom_designator is not null) then
1286 if(wjsi_row.load_type = wip_constants.create_ns_job) then
1287 l_bom_item_id := wjsi_row.bom_reference_id;
1288 elsif(wjsi_row.load_type = wip_constants.resched_job) then
1289 if(wdj_row.job_type = wip_constants.standard) then
1290 l_bom_item_id := wdj_row.primary_item_id;
1291 else
1292 l_bom_item_id := wdj_row.bom_reference_id;
1293 end if;
1294 else
1295 l_bom_item_id := wjsi_row.primary_item_id;
1296 end if;
1297
1298 select 1
1299 into g_dummy
1300 from bom_bill_alternates_v
1301 where assembly_item_id = l_bom_item_id
1302 and alternate_bom_designator = wjsi_row.alternate_bom_designator
1303 and organization_id = wjsi_row.organization_id
1304 and assembly_type = 1;
1305 end if;
1306 exception
1307 when others then
1308 wip_jsi_utils.record_error('WIP_ML_ALTERNATE_BOM');
1309 end alternate_bom_designator;
1310
1311 procedure project_id is begin
1312 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and
1313 wjsi_row.project_id is not null) then
1314 -- fix MOAC, set id so project view works
1315 fnd_profile.put('MFG_ORGANIZATION_ID',wjsi_row.organization_id);
1316 -- Bug 4890215. Performance Fix
1317 -- sugupta 26th-May-2006
1318 select mpv.project_id --this query will return multiple rows if the project has tasks
1319 into g_dummy
1320 from pjm_projects_v mpv, pjm_project_parameters ppp, mtl_parameters mp
1321 where mpv.project_id = ppp.project_id
1322 and mpv.project_id = wjsi_row.project_id
1323 and ppp.organization_id = wjsi_row.organization_id
1324 and ppp.organization_id = mp.organization_id
1325 and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
1326 end if;
1327 exception
1328 when others then
1329 wip_jsi_utils.record_error('WIP_ML_PROJECT_ID');
1330 end project_id;
1331
1332 procedure task_id is
1333 l_project_id NUMBER;
1334 begin
1335 if(wjsi_row.task_id is not null) then
1336 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1337 l_project_id := wjsi_row.project_id;
1338 else
1339 l_project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1340 end if;
1341
1342 if (PJM_PROJECT.val_task_idtonum(l_project_id, wjsi_row.task_id) is null)
1343 then
1344 raise fnd_api.g_exc_unexpected_error;
1345 end if;
1346
1347 end if;
1348 exception
1349 when others then
1350 wip_jsi_utils.record_error('WIP_ML_TASK_ID');
1351 end task_id;
1352
1353 procedure project_task_id is
1354 l_result VARCHAR2(1);
1355 l_errcode VARCHAR2(80);
1356 l_message VARCHAR2(2000);
1357 begin
1358 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and wjsi_row.project_id is not null) then
1359 l_result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
1360 (x_inventory_org_id => wjsi_row.organization_id,
1361 x_project_id => wjsi_row.project_id,
1362 x_task_id => wjsi_row.task_id,
1363 x_date1 => wjsi_row.first_unit_start_date,
1364 x_date2 => wjsi_row.last_unit_completion_date,
1365 x_calling_function => 'WILMLX',
1366 x_error_code => l_errcode
1367 );
1368
1369 if ( l_result <> PJM_PROJECT.G_VALIDATE_SUCCESS ) then
1370 raise fnd_api.g_exc_unexpected_error;
1371 end if;
1372 end if;
1373 exception
1374 when others then
1375 wip_utilities.get_message_stack(p_delete_stack => 'T',
1376 p_msg => l_message);
1377 if (l_result = PJM_PROJECT.G_VALIDATE_FAILURE) then
1378 wip_jsi_utils.record_error_text(l_message, false);
1379 wip_jsi_utils.abort_request;
1380 else
1381 wip_jsi_utils.record_error_text(l_message, true);
1382 end if;
1383 end project_task_id;
1384
1385 procedure schedule_dates is
1386 l_line_count NUMBER;
1387 l_rtg_count NUMBER;
1388 l_date_count NUMBER := 0;
1389 l_msg VARCHAR2(30) := 'WIP_ML_SCHEDULE_DATES';
1390 begin
1391 if(wjsi_row.first_unit_start_date is not null) then
1392 l_date_count := l_date_count + 1;
1393 end if;
1394
1395 if(wjsi_row.last_unit_completion_date is not null) then
1396 l_date_count := l_date_count + 1;
1397 end if;
1398
1399 if(wjsi_row.load_type = wip_constants.create_ns_job and
1400 wjsi_row.routing_reference_id is null and
1401 l_date_count <> 2) then
1402 --must provide both dates when creating a ns job
1403 raise fnd_api.g_exc_unexpected_error;
1404 end if;
1405
1406 if(wjsi_row.load_type = wip_constants.resched_job and
1407 wdj_row.job_type = wip_constants.nonstandard and
1408 wdj_row.routing_reference_id is null and
1409 (l_date_count = 1)) then
1410 --when rescheduling a ns job and providing one date, it must have a routing
1411 raise fnd_api.g_exc_unexpected_error;
1412 end if;
1413 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
1414 l_date_count = 0) then
1415 --all job creations must have at least one date
1416 raise fnd_api.g_exc_unexpected_error;
1417 end if;
1418 if(wjsi_row.load_type = wip_constants.resched_job and
1419 l_date_count = 0 and
1420 wjsi_row.start_quantity is not null) then
1421 --when changing the quantity, you must also provide a date
1422 raise fnd_api.g_exc_unexpected_error;
1423 end if;
1424 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
1425 (wjsi_row.allow_explosion = 'N' or wjsi_row.allow_explosion = 'n') and
1426 (l_date_count not in (0,2))) then
1427 --if not exploding, then the user must provide both dates or none at all
1428 raise fnd_api.g_exc_unexpected_error;
1429 end if;
1430 if(wjsi_row.first_unit_start_date is not null) then
1431 select 1
1432 into g_dummy
1433 from bom_calendar_dates bcd, mtl_parameters mp
1434 where mp.organization_id = wjsi_row.organization_id
1435 and mp.calendar_code = bcd.calendar_code
1436 and mp.calendar_exception_set_id = bcd.exception_set_id
1437 and bcd.calendar_date = trunc(wjsi_row.first_unit_start_date);
1438 end if;
1439 if(wjsi_row.last_unit_completion_date is not null) then
1440 select 1
1441 into g_dummy
1442 from bom_calendar_dates bcd, mtl_parameters mp
1443 where mp.organization_id = wjsi_row.organization_id
1444 and mp.calendar_code = bcd.calendar_code
1445 and mp.calendar_exception_set_id = bcd.exception_set_id
1446 and bcd.calendar_date = trunc(wjsi_row.last_unit_completion_date);
1447 end if;
1448
1449 --begin repetitive validation
1450 l_msg := 'WIP_ML_REPETITIVE_DATES';
1451 --include repetitive dates in date provided count
1452 if(wjsi_row.last_unit_start_date is not null) then
1453 l_date_count := l_date_count + 1;
1454 if(wjsi_row.load_type <> wip_constants.create_sched) then
1455 wip_jsi_utils.record_ignored_column_warning('LAST_UNIT_START_DATE');
1456 end if;
1457 end if;
1458
1459 if(wjsi_row.first_unit_completion_date is not null) then
1460 l_date_count := l_date_count + 1;
1461 if(wjsi_row.load_type <> wip_constants.create_sched) then
1462 wip_jsi_utils.record_ignored_column_warning('FIRST_UNIT_COMPLETION_DATE');
1463 end if;
1464 end if;
1465
1466 if(wjsi_row.load_type = wip_constants.create_sched) then
1467 if(l_date_count = 0) then
1468 --must provide at least one date for rep sched
1469 raise fnd_api.g_exc_unexpected_error;
1470 end if;
1471
1472 if(l_date_count <> 1) then
1473 --if you do not enter exactly one date then...
1474 select count(*)
1475 into l_line_count
1476 from wip_lines
1477 where organization_id = wjsi_row.organization_id
1478 and line_id = wjsi_row.line_id
1479 and line_schedule_type = 1; --fixed
1480 if(l_line_count > 0) then
1481 --the line can not have a fixed lead time
1482 raise fnd_api.g_exc_unexpected_error;
1483 end if;
1484 select count(*)
1485 into l_rtg_count
1486 from bom_operational_routings bor, wip_repetitive_items wri
1487 where wri.line_id = wjsi_row.line_id
1488 and nvl(bor.cfm_routing_flag, 2) = 2 --ignore flow rtgs
1489 and wri.primary_item_id = wjsi_row.primary_item_id
1490 and wri.organization_id = wjsi_row.organization_id
1491 and nvl(bor.alternate_routing_designator,'@@') = nvl(wri.alternate_routing_designator,'@@')
1492 and bor.organization_id = wri.organization_id
1493 and bor.assembly_item_id = wri.primary_item_id;
1494 if(l_rtg_count > 0) then
1495 --the line can not have a routing
1496 raise fnd_api.g_exc_unexpected_error;
1497 end if;
1498 end if;
1499
1500 select count(*)
1501 into l_rtg_count
1502 from bom_operational_routings bor, wip_repetitive_items wri
1503 where wri.line_id = wjsi_row.line_id
1504 and nvl(bor.cfm_routing_flag,2) = 2 --ignore flow routings
1505 and wri.primary_item_id = wjsi_row.primary_item_id
1506 and wri.organization_id = wjsi_row.organization_id
1507 and nvl(bor.alternate_routing_designator,'@@') = nvl(wri.alternate_routing_designator,'@@')
1508 and bor.organization_id = wri.organization_id
1509 and bor.assembly_item_id = wri.primary_item_id;
1510
1511 select count(*)
1512 into l_line_count
1513 from wip_lines_val_v
1514 where organization_id = wjsi_row.organization_id
1515 and line_id = wjsi_row.line_id
1516 and line_schedule_type = 2;
1517
1518
1519 --providing exactly the first dates or the last dates is an error condition
1520 if(not (l_date_count = 2 and
1521 ((wjsi_row.first_unit_start_date is not null and wjsi_row.first_unit_completion_date is not null) or
1522 (wjsi_row.last_unit_start_date is not null and wjsi_row.last_unit_completion_date is not null)))) then
1523 if(l_rtg_count = 0 and l_line_count > 0) then
1524 raise fnd_api.g_exc_unexpected_error;
1525 end if;
1526 end if;
1527
1528 if(l_line_count > 0 and l_rtg_count = 0) then
1529 --estimate schedule dates
1530 if(wjsi_row.first_unit_start_date is null) then
1531 -- Bug 4890215. Performance Fix
1532 -- sugupta 26th-May-2006
1533 /*
1534 select calendar_date
1535 into wjsi_row.first_unit_start_date
1536 from bom_calendar_dates bcd, mtl_parameters mp
1537 where mp.organization_id = wjsi_row.organization_id
1538 and bcd.exception_set_id = mp.calendar_exception_set_id
1539 and bcd.calendar_code = mp.calendar_code
1540 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
1541 from bom_calendar_dates b2
1542 where b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
1543 and b2.calendar_code = bcd.calendar_code
1544 and b2.exception_set_id = bcd.exception_set_id);
1545 */
1546 SELECT bcd.calendar_date
1547 INTO wjsi_row.first_unit_start_date
1548 FROM bom_calendar_dates bcd,
1549 mtl_parameters mp,
1550 bom_calendar_dates b2
1551 WHERE mp.organization_id = wjsi_row.organization_id
1552 and bcd.exception_set_id = mp.calendar_exception_set_id
1553 and bcd.calendar_code = mp.calendar_code
1554 and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
1555 and b2.calendar_date = trunc(wjsi_row.last_unit_start_date)
1556 and b2.calendar_code = mp.calendar_code;
1557 end if;
1558 if(wjsi_row.last_unit_start_date is null) then
1559 -- Bug 4890215. Performance Fix
1560 -- sugupta 26th-May-2006
1561 /*
1562 select calendar_date
1563 into wjsi_row.last_unit_start_date
1564 from bom_calendar_dates bcd, mtl_parameters mp
1565 where mp.organization_id = wjsi_row.organization_id
1566 and bcd.exception_set_id = mp.calendar_exception_set_id
1567 and bcd.calendar_code = mp.calendar_code
1568 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
1569 from bom_calendar_dates b2
1570 where b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
1571 and b2.calendar_code = bcd.calendar_code
1572 and b2.exception_set_id = bcd.exception_set_id);
1573 */
1574 SELECT bcd.calendar_date
1575 INTO wjsi_row.last_unit_start_date
1576 FROM bom_calendar_dates bcd,
1577 mtl_parameters mp,
1578 bom_calendar_dates b2
1579 WHERE mp.organization_id = wjsi_row.organization_id
1580 and bcd.exception_set_id = mp.calendar_exception_set_id
1581 and bcd.calendar_code = mp.calendar_code
1582 and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
1583 and b2.calendar_date = trunc(wjsi_row.first_unit_start_date)
1584 and b2.calendar_code = mp.calendar_code;
1585 end if;
1586 if(wjsi_row.first_unit_completion_date is null) then
1587 -- Bug 4890215. Performance Fix
1588 -- sugupta 26th-May-2006
1589 /*
1590 select calendar_date
1591 into wjsi_row.first_unit_completion_date
1592 from bom_calendar_dates bcd, mtl_parameters mp
1593 where mp.organization_id = wjsi_row.organization_id
1594 and bcd.exception_set_id = mp.calendar_exception_set_id
1595 and bcd.calendar_code = mp.calendar_code
1596 and seq_num = (select prior_seq_num - ceil(wjsi_row.processing_work_days)+1
1597 from bom_calendar_dates b2
1598 where b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
1599 and b2.calendar_code = bcd.calendar_code
1600 and b2.exception_set_id = bcd.exception_set_id);
1601 */
1602 SELECT bcd.calendar_date
1603 INTO wjsi_row.first_unit_completion_date
1604 FROM bom_calendar_dates bcd,
1605 mtl_parameters mp,
1606 bom_calendar_dates b2
1607 WHERE mp.organization_id = wjsi_row.organization_id
1608 and bcd.exception_set_id = mp.calendar_exception_set_id
1609 and bcd.calendar_code = mp.calendar_code
1610 and bcd.seq_num = b2.prior_seq_num - ceil(wjsi_row.processing_work_days)+1
1611 and b2.calendar_date = trunc(wjsi_row.last_unit_completion_date)
1612 and b2.calendar_code = mp.calendar_code;
1613 end if;
1614 if(wjsi_row.last_unit_completion_date is null) then
1615 -- Bug 4890215. Performance Fix
1616 -- sugupta 26th-May-2006
1617 /*
1618 select calendar_date
1619 into wjsi_row.last_unit_completion_date
1620 from bom_calendar_dates bcd, mtl_parameters mp
1621 where mp.organization_id = wjsi_row.organization_id
1622 and bcd.exception_set_id = mp.calendar_exception_set_id
1623 and bcd.calendar_code = mp.calendar_code
1624 and seq_num = (select next_seq_num + ceil(wjsi_row.processing_work_days)-1
1625 from bom_calendar_dates b2
1626 where b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
1627 and b2.calendar_code = bcd.calendar_code
1628 and b2.exception_set_id = bcd.exception_set_id) ;
1629 */
1630 SELECT bcd.calendar_date
1631 INTO wjsi_row.last_unit_completion_date
1632 FROM bom_calendar_dates bcd,
1633 mtl_parameters mp,
1634 bom_calendar_dates b2
1635 WHERE mp.organization_id = wjsi_row.organization_id
1636 and bcd.exception_set_id = mp.calendar_exception_set_id
1637 and bcd.calendar_code = mp.calendar_code
1638 and bcd.seq_num = b2.prior_seq_num + ceil(wjsi_row.processing_work_days)-1
1639 and b2.calendar_date = trunc(wjsi_row.first_unit_completion_date)
1640 and b2.calendar_code = mp.calendar_code;
1641 end if;
1642 end if;
1643 end if;
1644 exception
1645 when others then
1646 wip_jsi_utils.record_error(l_msg);
1647 wip_jsi_utils.abort_request;
1648 end schedule_dates;
1649
1650 procedure scheduling_method is
1651 l_msg_code VARCHAR2(30) := 'WIP_ML_SCHEDULING_METHOD';
1652 begin
1653 if(wjsi_row.load_type <> wip_constants.create_sched) then
1654 if(wjsi_row.scheduling_method not in (wip_constants.routing, wip_constants.leadtime,
1655 wip_constants.ml_manual)) then
1656 raise fnd_api.g_exc_unexpected_error;
1657 end if;
1658 if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659 ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1660 (wjsi_row.load_type = wip_constants.resched_job and
1661 wdj_row.job_type = wip_constants.nonstandard and
1662 wdj_row.routing_reference_id is null))) then
1663 --can not do lead time for ns jobs that have no routing reference
1664 raise fnd_api.g_exc_unexpected_error;
1665 elsif(wjsi_row.scheduling_method = wip_constants.ml_manual) then
1666 if((wjsi_row.first_unit_start_date is null or wjsi_row.last_unit_completion_date is null) or
1667 wjsi_row.first_unit_start_date > wjsi_row.last_unit_completion_date) then
1668 raise fnd_api.g_exc_unexpected_error;
1669 end if;
1670 else --routing
1671 if(wjsi_row.allow_explosion in ('n','N') and
1672 wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and
1673 (wjsi_row.first_unit_start_date is null or wjsi_row.last_unit_completion_date is null)) then
1674 --if not exploding, the user must provide both dates.
1675 l_msg_code := 'WIP_ML_SCHEDULING_METHOD2';
1676 raise fnd_api.g_exc_unexpected_error;
1677 end if;
1678 end if;
1679 end if;
1680 exception
1681 when others then
1682 wip_jsi_utils.record_error(l_msg_code);
1683 wip_jsi_utils.abort_request;
1684 end scheduling_method;
1685
1686 procedure completion_subinventory is
1687 l_inv_item_id NUMBER;
1688 l_inv_asset_flag VARCHAR2(1);
1689 l_restrict_subinv_code NUMBER;
1690
1691 begin
1692 if(wjsi_row.load_type = wip_constants.create_ns_job and
1693 wjsi_row.primary_item_id is null and
1694 wjsi_row.completion_subinventory is not null) then
1695 raise fnd_api.g_exc_unexpected_error;
1696 end if;
1697
1698 if(wjsi_row.load_type = wip_constants.create_eam_job and
1699 wjsi_row.rebuild_item_id is not null) then
1700 l_inv_item_id := wjsi_row.rebuild_item_id;
1701 select inventory_asset_flag, restrict_subinventories_code
1702 into l_inv_asset_flag, l_restrict_subinv_code
1703 from mtl_system_items
1704 where inventory_item_id = l_inv_item_id
1705 and organization_id = wjsi_row.organization_id;
1706 else
1707 l_inv_item_id := wjsi_row.primary_item_id;
1708 l_inv_asset_flag := primary_item_row.inventory_asset_flag;
1709 l_restrict_subinv_code := primary_item_row.restrict_subinventories_code;
1710 end if;
1711 /*
1712 if(wjsi_row.completion_subinventory is not null and
1713 (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1714 (wjsi_row.load_type = wip_constants.create_eam_job and
1715 wjsi_row.rebuild_item_id is not null))) then
1716 if(l_inv_item_id is not null) then
1717 if(l_restrict_subinv_code = wip_constants.yes) then
1718 if(l_inv_asset_flag = 'N') then
1719 -- If restricted sub, non-asset item, must be in MTL_ITEM_SUB_VAL_V
1720 select 1
1721 into g_dummy
1722 from mtl_item_sub_val_v
1723 where inventory_item_id = l_inv_item_id
1724 and organization_id = wjsi_row.organization_id
1725 and secondary_inventory_name = wjsi_row.completion_subinventory;
1726 else
1727 -- If restricted sub, asset item, must be in MTL_ITEM_AST_TRK_SUB_VAL_V
1728 select 1
1729 into g_dummy
1730 from mtl_item_sub_ast_trk_val_v
1731 where inventory_item_id = l_inv_item_id
1732 and organization_id = wjsi_row.organization_id
1733 and secondary_inventory_name = wjsi_row.completion_subinventory;
1734 end if;
1735 --now validate unrestricted items
1736 elsif(fnd_profile.value('INV:EXPENSE_TO_ASSET_TRANSFER') = 1) then
1737 select 1
1738 into g_dummy
1739 from mtl_subinventories_val_v
1740 where secondary_inventory_name = wjsi_row.completion_subinventory
1741 and organization_id = wjsi_row.organization_id
1742 and asset;
1743 else
1744 select 1
1745 into g_dummy
1746 from mtl_sub_ast_trk_val_v
1747 where secondary_inventory_name = wjsi_row.completion_subinventory
1748 and organization_id = wjsi_row.organization_id;
1749 end if;
1750 end if;
1751 end if;
1752 */
1753 exception
1754 when others then
1755 wip_jsi_utils.record_error('WIP_ML_COMPLETION_SUBINVENTORY');
1756 wip_jsi_utils.abort_request;
1757 end completion_subinventory;
1758
1759 procedure completion_locator_id is
1760 l_prj_loc_id NUMBER;
1761 l_msg VARCHAR2(30) := 'WIP_ML_LOCATOR_PROJ_TASK';
1762 l_org_loc_control NUMBER;
1763 l_sub_loc_control NUMBER;
1764 l_success boolean;
1765 l_item_id NUMBER;
1766 l_restrict_locs NUMBER;
1767 l_item_loc_control NUMBER;
1768 begin
1769
1770 if((wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1771 (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.rebuild_item_id is not null)) and
1772 wjsi_row.completion_subinventory is null) then
1773 if(wjsi_row.completion_locator_id is not null) then
1774 l_msg := 'WIP_ML_COMPLETION_LOCATOR';
1775 raise fnd_api.g_exc_unexpected_error;
1776 else
1777 return;
1778 end if;
1779 end if;
1780
1781 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1782
1783 if(wjsi_row.load_type = wip_constants.create_eam_job) then
1784 l_item_id := wjsi_row.rebuild_item_id;
1785 else
1786 l_item_id := wdj_row.rebuild_item_id;
1787 end if;
1788 if(l_item_id is not null) then
1789 select restrict_locators_code, location_control_code
1790 into l_restrict_locs, l_item_loc_control
1791 from mtl_system_items
1792 where inventory_item_id = l_item_id
1793 and organization_id = wjsi_row.organization_id;
1794 else
1795 return; --no need for locator validation
1796 end if;
1797 else
1798 l_restrict_locs := primary_item_row.restrict_locators_code;
1799 l_item_loc_control := primary_item_row.location_control_code;
1800 l_item_id := primary_item_row.inventory_item_id;
1801 end if;
1802
1803 --if rescheduling, sub/loc are not modifiable. Make sure new values don't get inserted
1804 --into the tables. For proj/task, default from the existing job if user is not changing them.
1805 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
1806 wjsi_row.completion_locator_id := wdj_row.completion_locator_id;
1807 wjsi_row.project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1808 wjsi_row.task_id := nvl(wjsi_row.task_id, wdj_row.task_id);
1809 wjsi_row.completion_subinventory := wdj_row.completion_subinventory;
1810 end if;
1811 -- if rescheduling, sub/loc are now modifiable.
1812 -- will update from both wjsi.completion_subinventory and
1813 -- and wjsi.locator only if wjsi.completion_subinventory is not null
1814 if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
1815 wjsi_row.project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1816 wjsi_row.task_id := nvl(wjsi_row.task_id, wdj_row.task_id);
1817 wjsi_row.completion_subinventory :=
1818 nvl(wjsi_row.completion_subinventory, wdj_row.completion_subinventory);
1819 if wjsi_row.completion_subinventory is not null then
1820 wjsi_row.completion_locator_id := nvl(wjsi_row.completion_locator_id,
1821 wdj_row.completion_locator_id);
1822 end if;
1823 end if;
1824
1825 -- Ask PJM to default the locator.
1826 -- If successful, PJM will set the output locator parameter.
1827 -- If unsuccessful, they will leave it at its current value or,
1828 -- if things really go wrong, throw a no_data_found.
1829 if(wjsi_row.project_id is not null) then
1830 if(pjm_project_locator.check_itemLocatorControl(wjsi_row.organization_id,
1831 wjsi_row.completion_subinventory,
1832 wjsi_row.completion_locator_id,
1833 l_item_id,
1834 2)) then
1835 pjm_project_locator.get_defaultProjectLocator(wjsi_row.organization_id,
1836 wjsi_row.completion_locator_id,
1837 wjsi_row.project_id,
1838 wjsi_row.task_id,
1839 l_prj_loc_id);
1840 if(l_prj_loc_id is not null) then
1841 wjsi_row.completion_locator_id := l_prj_loc_id; --for write to wjsi
1842 if(not pjm_project_locator.check_project_references(wjsi_row.organization_id,
1843 l_prj_loc_id,
1844 'SPECIFIC', -- validation mode
1845 'Y', -- required?
1846 wjsi_row.project_id,
1847 wjsi_row.task_id)) then
1848 raise fnd_api.g_exc_unexpected_error;
1849 end if;
1850 end if;
1851 end if;
1852 end if;
1853 --done with project locator defaulting/validation.
1854 if(wjsi_row.load_type <> wip_constants.create_sched and
1855 wjsi_row.completion_subinventory is not null) then
1856 l_msg := 'WIP_ML_INVALID_LOCATOR';
1857 select sub.locator_type, mp.stock_locator_control_code
1858 into l_sub_loc_control, l_org_loc_control
1859 from mtl_secondary_inventories sub, mtl_parameters mp
1860 where sub.secondary_inventory_name = wjsi_row.completion_subinventory
1861 and sub.organization_id = wjsi_row.organization_id
1862 and mp.organization_id = wjsi_row.organization_id;
1863
1864 wip_locator.validate(p_organization_id => wjsi_row.organization_id,
1865 p_item_id => l_item_id,
1866 p_subinventory_code => wjsi_row.completion_subinventory,
1867 p_org_loc_control => l_org_loc_control,
1868 p_sub_loc_control => l_sub_loc_control,
1869 p_item_loc_control => l_item_loc_control,
1870 p_restrict_flag => l_restrict_locs,
1871 p_neg_flag => '',
1872 p_action => '',
1873 p_project_id => wjsi_row.project_id,
1874 p_task_id => wjsi_row.task_id,
1875 p_locator_id => wjsi_row.completion_locator_id,
1876 p_locator_segments => wjsi_row.completion_locator_segments,
1877 p_success_flag => l_success);
1878 if(not l_success) then
1879 raise fnd_api.g_exc_unexpected_error;
1880 end if;
1881 end if;
1882 exception when others then
1883 wip_jsi_utils.record_error(l_msg);
1884 /* Fixed Bug#3060266 - should abort request if Invalid Locator */
1885 wip_jsi_utils.abort_request;
1886 end completion_locator_id;
1887
1888 procedure due_date is begin
1889 if(wjsi_row.load_type = wip_constants.create_eam_job) then
1890 if(wjsi_row.due_date is not null and wjsi_row.pm_schedule_id is null) then
1891 raise fnd_api.g_exc_unexpected_error;
1892 end if;
1893 elsif(wjsi_row.due_date is not null and wjsi_row.load_type = wip_constants.resched_eam_job) then
1894 if(wdj_row.status_type <> wip_constants.draft) then
1895 raise fnd_api.g_exc_unexpected_error;
1896 end if;
1897 end if;
1898 exception
1899 when others then
1900 wip_jsi_utils.record_error('WIP_ML_DUE_DATE');
1901 wip_jsi_utils.abort_request;
1902 end due_date;
1903
1904 procedure date_released is begin
1905 if(wjsi_row.date_released > sysdate and
1906 wjsi_row.status_type = wip_constants.released) then
1907 raise fnd_api.g_exc_unexpected_error;
1908 end if;
1909 exception when others then
1910 wip_jsi_utils.record_error('WIP_ML_INVALID_RELEASE_DATE');
1911 wip_jsi_utils.abort_request;
1912 end date_released;
1913
1914 procedure requested_start_date is begin
1915 if(wjsi_row.load_type = wip_constants.create_eam_job) then
1916 if(wjsi_row.requested_start_date is not null and wjsi_row.pm_schedule_id is null) then
1917 raise fnd_api.g_exc_unexpected_error;
1918 end if;
1919 elsif(wjsi_row.load_type = wip_constants.resched_eam_job) then
1920 if(wjsi_row.requested_start_date is not null and wdj_row.status_type <> wip_constants.draft) then
1921 raise fnd_api.g_exc_unexpected_error;
1922 end if;
1923 end if;
1924 exception
1925 when others then
1926 wip_jsi_utils.record_error('WIP_ML_REQUESTED_START_DATE');
1927 wip_jsi_utils.abort_request;
1928 end requested_start_date;
1929
1930 procedure end_item_unit_number is
1931 is_unit_effective_item boolean;
1932 l_bom_item_id NUMBER;
1933 begin
1934
1935 if(wjsi_row.load_type = wip_constants.create_ns_job) then
1936 l_bom_item_id := wjsi_row.bom_reference_id;
1937 else
1938 l_bom_item_id := wjsi_row.primary_item_id;
1939 end if;
1940 is_unit_effective_item := l_bom_item_id is not null and
1941 PJM_UNIT_EFF.ENABLED = 'Y' and
1942 PJM_Unit_Eff.unit_effective_item(l_bom_item_id,
1943 wjsi_row.organization_id) = 'Y';
1944
1945 -- If the assembly item is unit effective, validate the actual
1946 -- unit number value. The unit number must exist in the same _master_
1947 -- organization as the item. (We already validate that the item
1948 -- is in the organization identified by the ORGANIZATION_ID column.)
1949 if(is_unit_effective_item and wjsi_row.end_item_unit_number is not null) then
1950 begin
1951 select 1
1952 into g_dummy
1953 from pjm_unit_numbers_lov_v pun,
1954 mtl_parameters mp
1955 where pun.unit_number = wjsi_row.end_item_unit_number
1956 and mp.organization_id = wjsi_row.organization_id
1957 and mp.master_organization_id = pun.master_organization_id;
1958 exception
1959 when too_many_rows then
1960 null; -- the query returning multiple rows is ok
1961 when others then
1962 fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
1963 wip_jsi_utils.record_current_error ;
1964 end;
1965 end if;
1966
1967 -- You cannot create a repetitive schedule for a unit effective assembly.
1968 if(wjsi_row.load_type = wip_constants.create_sched and
1969 is_unit_effective_item) then
1970 wip_jsi_utils.record_error('WIP_ML_NO_UNIT_EFF_SCHED');
1971 raise fnd_api.g_exc_unexpected_error;
1972 end if;
1973
1974 -- If this is a discrete job load...
1975 if (wjsi_row.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB))
1976 then
1977 -- It is an error to provide unit number for non-unit effective assemblies.
1978 if(not is_unit_effective_item and wjsi_row.end_item_unit_number is not null) then
1979 wip_jsi_utils.record_error('WIP_ML_UNIT_NUM_MEANINGLESS');
1980 end if;
1981
1982 -- Unit number is required for unit effective assemblies.
1983 if(is_unit_effective_item and wjsi_row.end_item_unit_number is null) then
1984 fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER REQUIRED');
1985 wip_jsi_utils.record_current_error;
1986 raise fnd_api.g_exc_unexpected_error;
1987 end if;
1988 end if;
1989
1990 --if request is for reschedule, keep as is for all cases except when job_status is unreleased
1991 if (wjsi_row.load_type in (WIP_CONSTANTS.RESCHED_JOB,
1992 WIP_CONSTANTS.RESCHED_LOT_JOB))
1993 then
1994 -- added for bug#2766839, added check when the assembly is not is_unit_effective_item
1995 if(not is_unit_effective_item) then
1996 -- It is an error to provide unit number for non-unit effective assemblies.
1997 if ( wjsi_row.end_item_unit_number is not null) then
1998 wip_jsi_utils.record_error('WIP_ML_UNIT_NUM_MEANINGLESS');
1999 end if;
2000 wjsi_row.end_item_unit_number := null;
2001
2002 -- if status is unreleased, the end_item_unit_number can be modified
2003 elsif (wdj_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2004 if (wjsi_row.end_item_unit_number is null) then
2005 begin
2006 select end_item_unit_number into wjsi_row.end_item_unit_number
2007 from wip_discrete_jobs
2008 where wip_entity_id =
2009 (select wip_entity_id
2010 from wip_job_schedule_interface
2011 where rowid = WIP_JSI_UTILS.CURRENT_ROWID
2012 );
2013 exception
2014 when others then
2015 FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2016 WIP_JSI_Utils.record_current_error ;
2017 raise fnd_api.g_exc_unexpected_error;
2018 end ;
2019 else
2020 begin
2021 -- bug#2719927, bom revision code/reexplosion is based on bom_reference_id
2022 select primary_item_id into wjsi_row.bom_reference_id
2023 from wip_discrete_jobs
2024 where wip_entity_id = wjsi_row.wip_entity_id;
2025 exception
2026 when others then
2027 FND_Message.set_name('WIP', 'WIP_ML_PRIMARY_ITEM_ID') ;
2028 WIP_JSI_Utils.record_current_error ;
2029 raise fnd_api.g_exc_unexpected_error;
2030 end ;
2031 end if;
2032
2033 -- if status is not unreleased, end_item_unit_number is ignored.
2034 else
2035 if (wjsi_row.end_item_unit_number is not null) then
2036 wip_jsi_utils.record_ignored_column_warning('END_ITEM_UNIT_NUMBER');
2037 end if;
2038
2039 begin
2040 select end_item_unit_number into wjsi_row.end_item_unit_number
2041 from wip_discrete_jobs
2042 where wip_entity_id =
2043 (select wip_entity_id
2044 from wip_job_schedule_interface
2045 where rowid = WIP_JSI_UTILS.CURRENT_ROWID
2046 );
2047 exception
2048 when others then
2049 FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2050 WIP_JSI_Utils.record_current_error ;
2051 raise fnd_api.g_exc_unexpected_error;
2052 end ;
2053 end if;
2054 end if ;
2055
2056 exception
2057 when others then
2058 wip_jsi_utils.abort_request;
2059
2060 end end_item_unit_number;
2061
2062 procedure overcompletion is
2063 l_tol_type NUMBER := wjsi_row.overcompletion_tolerance_type;
2064 l_tol_value NUMBER := wjsi_row.overcompletion_tolerance_value;
2065 l_msg VARCHAR2(30);
2066 begin
2067 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job) or
2068 (wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is not null)) then
2069 if(l_tol_value is not null and l_tol_type is not null) then
2070 if(l_tol_type not in (wip_constants.percent, wip_constants.amount)) then
2071 l_msg:= 'WIP_ML_COMP_TOLERANCE_TYPE';
2072 raise fnd_api.g_exc_unexpected_error;
2073 end if;
2074 if(l_tol_value < 0) then
2075 l_msg := 'WIP_ML_COMP_TOLERANCE_NEGATIVE';
2076 raise fnd_api.g_exc_unexpected_error;
2077 end if;
2078 elsif(l_tol_value is not null or l_tol_type is not null) then
2079 l_msg := 'WIP_ML_COMP_TOLERANCE_NULL';--only one overcompletion column was provided
2080 raise fnd_api.g_exc_unexpected_error;
2081 end if;
2082 end if;
2083 exception
2084 when others then
2085 wip_jsi_utils.record_error(l_msg);
2086 end overcompletion;
2087
2088 procedure class_code is begin
2089 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
2090 wjsi_row.class_code is null) then
2091 raise fnd_api.g_exc_unexpected_error;
2092 end if;
2093 if(wjsi_row.load_type = wip_constants.create_job) then
2094 if(wjsi_row.project_id is null) then
2095 select 1
2096 into g_dummy
2097 from dual
2098 where exists(select 1
2099 from cst_cg_wip_acct_classes_v
2100 where class_code = wjsi_row.class_code
2101 and organization_id = wjsi_row.organization_id
2102 and class_type = wip_constants.discrete);
2103 else
2104 select 1
2105 into g_dummy
2106 from dual
2107 where exists(select 1
2108 from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
2109 where ccwac.class_code = wjsi_row.class_code
2110 and ccwac.organization_id = wjsi_row.organization_id
2111 and ccwac.class_type = wip_constants.discrete
2112 and mp.organization_id = wjsi_row.organization_id
2113 and ( mp.primary_cost_method = wip_constants.cost_std
2114 or ccwac.cost_group_id = (select costing_group_id
2115 from mrp_project_parameters mpp
2116 where organization_id = wjsi_row.organization_id
2117 and mpp.project_id = wjsi_row.project_id)
2118 )
2119 );
2120 end if;
2121 elsif(wjsi_row.load_type = wip_constants.create_ns_job) then
2122 select 1
2123 into g_dummy
2124 from dual
2125 where exists(select 1
2126 from wip_non_standard_classes_val_v
2127 where class_code = wjsi_row.class_code
2128 and organization_id = wjsi_row.organization_id);
2129
2130 elsif(wjsi_row.load_type = wip_constants.create_eam_job) then
2131 if(wjsi_row.project_id is null) then
2132 select 1
2133 into g_dummy
2134 from dual
2135 where exists(select 1
2136 from cst_cg_wip_acct_classes_v
2137 where class_code = wjsi_row.class_code
2138 and organization_id = wjsi_row.organization_id
2139 and class_type = wip_constants.eam);
2140 else
2141 select 1
2142 into g_dummy
2143 from dual
2144 where exists(select 1
2145 from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
2146 where ccwac.class_code = wjsi_row.class_code
2147 and ccwac.organization_id = wjsi_row.organization_id
2148 and ccwac.class_type = wip_constants.eam
2149 and mp.organization_id = wjsi_row.organization_id
2150 and ( mp.primary_cost_method = wip_constants.cost_std
2151 or ccwac.cost_group_id = (select costing_group_id
2152 from mrp_project_parameters mpp
2153 where organization_id = wjsi_row.organization_id
2154 and mpp.project_id = wjsi_row.project_id)
2155 )
2156 );
2157 end if;
2158 end if;
2159 exception
2160 when others then
2161 wip_jsi_utils.record_error('WIP_ML_CLASS_CODE');
2162 end class_code;
2163
2164 procedure estimate_lead_time is
2165 l_entity_type NUMBER;
2166 l_sched_dir NUMBER;
2167 l_rtg_count NUMBER := 0; --if > 0 a routing exists
2168 l_qty NUMBER;
2169 l_msg VARCHAR2(30);
2170 begin
2171 if(wjsi_row.load_type = wip_constants.create_sched) then
2172 l_entity_type := wip_constants.repetitive;
2173 if(wjsi_row.first_unit_start_date is null) then
2174 if(wjsi_row.last_unit_start_date is not null) then
2175 l_sched_dir := wip_constants.lusd;
2176 elsif(wjsi_row.first_unit_completion_date is not null) then
2177 l_sched_dir := wip_constants.fucd;
2178 else
2179 l_sched_dir := wip_constants.lucd;
2180 end if;
2181
2182 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2183 x_fixed_lead => primary_item_row.fixed_lead_time,
2184 x_var_lead => primary_item_row.variable_lead_time,
2185 x_quantity => wjsi_row.processing_work_days * wjsi_row.daily_production_rate,
2186 x_proc_days => wjsi_row.processing_work_days,
2187 x_entity_type => wip_constants.repetitive,
2188 x_fusd => wjsi_row.first_unit_start_date,
2189 x_fucd => wjsi_row.first_unit_completion_date,
2190 x_lusd => wjsi_row.last_unit_start_date,
2191 x_lucd => wjsi_row.last_unit_completion_date,
2192 x_sched_dir => l_sched_dir,
2193 x_est_date => wjsi_row.first_unit_start_date);
2194 if(wjsi_row.first_unit_start_date is null) then
2195 l_msg := 'WIP_ML_EST_LEADTIME';
2196 raise fnd_api.g_exc_unexpected_error;
2197 end if;
2198 end if;
2199 else
2200 if(wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is not null) then
2201 select count(*)
2202 into l_rtg_count
2203 from bom_operational_routings
2204 where assembly_item_id = wjsi_row.routing_reference_id
2205 and organization_id = wjsi_row.organization_id
2206 and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
2207 and nvl(cfm_routing_flag, 2) = 2;
2208 l_qty := wjsi_row.start_quantity;
2209 elsif(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job)) then
2210 select count(*)
2211 into l_rtg_count
2212 from bom_operational_routings
2213 where assembly_item_id = wjsi_row.primary_item_id
2214 and organization_id = wjsi_row.organization_id
2215 and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
2216 and nvl(cfm_routing_flag, 2) = 2;
2217 l_qty := wjsi_row.start_quantity;
2218 elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
2219 select count(*)
2220 into l_rtg_count
2221 from wip_operations
2222 where wip_entity_id = wjsi_row.wip_entity_id;
2223 l_qty := nvl(wjsi_row.start_quantity, wdj_row.start_quantity);
2224 end if;
2225 --if no routing exists, update the scheduling method appropriately
2226 if(wjsi_row.scheduling_method = wip_constants.routing and l_rtg_count = 0) then
2227 if(wjsi_row.first_unit_start_date is not null and
2228 wjsi_row.last_unit_completion_date is not null) then
2229 wjsi_row.scheduling_method := wip_constants.ml_manual;
2230 else
2231 wjsi_row.scheduling_method := wip_constants.leadtime;
2232 end if;
2233 end if;
2234
2235 if((wjsi_row.first_unit_start_date is null and
2236 wjsi_row.last_unit_completion_date is not null) or
2237 (wjsi_row.last_unit_completion_date is not null and
2238 wjsi_row.scheduling_method = wip_constants.leadtime)) then
2239 /* Estimate Start Date */
2240 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2241 x_fixed_lead => primary_item_row.fixed_lead_time,
2242 x_var_lead => primary_item_row.variable_lead_time,
2243 x_quantity => l_qty,
2244 x_proc_days => 0,
2245 x_entity_type => l_entity_type,
2246 x_fusd => '',
2247 x_fucd => '',
2248 x_lusd => '',
2249 x_lucd => wjsi_row.last_unit_completion_date,
2250 x_sched_dir => wip_constants.lucd,
2251 x_est_date => wjsi_row.first_unit_start_date);
2252 if(wjsi_row.first_unit_start_date is null) then
2253 l_msg := 'WIP_ML_EST_LEADTIME';
2254 raise fnd_api.g_exc_unexpected_error;
2255 end if;
2256 elsif(wjsi_row.last_unit_completion_date is null and
2257 wjsi_row.first_unit_start_date is not null and
2258 wjsi_row.scheduling_method = wip_constants.leadtime) then
2259 /* Estimate Completion Date */
2260 wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2261 x_fixed_lead => primary_item_row.fixed_lead_time,
2262 x_var_lead => primary_item_row.variable_lead_time,
2263 x_quantity => l_qty,
2264 x_proc_days => 0,
2265 x_entity_type => l_entity_type,
2266 x_fusd => wjsi_row.first_unit_start_date,
2267 x_fucd => '',
2268 x_lusd => '',
2269 x_lucd => '',
2270 x_sched_dir => wip_constants.fusd,
2271 x_est_date => wjsi_row.last_unit_completion_date);
2272 if(wjsi_row.last_unit_completion_date is null) then
2273 l_msg := 'WIP_NO_CALENDAR';
2274 raise fnd_api.g_exc_unexpected_error;
2275 end if;
2276 end if;
2277 end if;
2278 exception
2279 when others then
2280 wip_jsi_utils.record_error(l_msg);
2281 end estimate_lead_time;
2282
2283 procedure bom_revision is
2284 l_start_date DATE;
2285 l_bom_item_id NUMBER;
2286 begin
2287 if(wjsi_row.load_type = wip_constants.create_ns_job) then
2288 l_bom_item_id := wjsi_row.bom_reference_id;
2289 else
2290 l_bom_item_id := wjsi_row.primary_item_id;
2291 end if;
2292 l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2293 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2294 wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2295 wip_revisions.bom_revision(p_organization_id => wjsi_row.organization_id,
2296 p_item_id => l_bom_item_id,
2297 p_revision => wjsi_row.bom_revision,
2298 p_revision_date => wjsi_row.bom_revision_date,
2299 p_start_date => l_start_date);
2300 end if;
2301 exception
2302 when others then
2303 WIP_JSI_Utils.record_error('WIP_ML_BOM_REVISION') ;
2304 /* Fixed for bug#3063147
2305 When No valid bom revision exists for an item then mass load should be
2306 aborted.
2307 */
2308 wip_jsi_utils.abort_request;
2309
2310 end bom_revision;
2311
2312 procedure routing_revision is
2313 l_start_date DATE;
2314 l_rtg_item_id NUMBER;
2315 l_count NUMBER;
2316 begin
2317 if(wjsi_row.load_type = wip_constants.create_ns_job) then
2318 l_rtg_item_id := wjsi_row.routing_reference_id;
2319 else
2320 l_rtg_item_id := wjsi_row.primary_item_id;
2321 end if;
2322 l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2323 if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2324 wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2325 select count(*)
2326 into l_count
2327 from bom_operational_routings
2328 where assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id,
2329 wjsi_row.primary_item_id)
2330 and organization_id = wjsi_row.organization_id
2331 and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@');
2332 if(l_count > 0) then
2333 wip_revisions.routing_revision(p_organization_id => wjsi_row.organization_id,
2334 p_item_id => l_rtg_item_id,
2335 p_revision => wjsi_row.routing_revision,
2336 p_revision_date => wjsi_row.routing_revision_date,
2337 p_start_date => l_start_date);
2338 end if;
2339 end if;
2340 exception
2341 when others then
2342 WIP_JSI_Utils.record_error('WIP_ML_ROUTING_REVISION');
2343 end routing_revision;
2344
2345 procedure asset_group_id is begin
2346 if(wjsi_row.load_type = wip_constants.create_eam_job) then
2347 if(wjsi_row.asset_group_id is not null) then
2348 select 1
2349 into g_dummy
2350 from mtl_system_items
2351 where inventory_item_id = wjsi_row.asset_group_id
2352 and organization_id = wjsi_row.organization_id
2353 and eam_item_type = 1; -- asset group
2354 elsif(wjsi_row.rebuild_item_id is null) then
2355 raise fnd_api.g_exc_unexpected_error;
2356 end if;
2357 end if;
2358 exception
2359 when others then
2360 wip_jsi_utils.record_error('WIP_ML_EAM_ASSET_GROUP');
2361 wip_jsi_utils.abort_request;
2362 end asset_group_id;
2363
2364 procedure asset_number is
2365 l_msg VARCHAR2(30);
2366 begin
2367 l_msg := 'WIP_ML_EAM_ASSET_NUM';
2368 if(wjsi_row.load_type = wip_constants.create_eam_job) then
2369
2370 -- Per EAM Enh. for H, this field is no longer mandatory
2371
2372 if(wjsi_row.asset_group_id is null and
2373 wjsi_row.asset_number is not null) then
2374 raise fnd_api.g_exc_unexpected_error;
2375 end if;
2376
2377 if(wjsi_row.asset_group_id is not null) then
2378 select 1
2379 into g_dummy
2380 from mtl_serial_numbers
2381 where inventory_item_id = wjsi_row.asset_group_id
2382 and current_organization_id = wjsi_row.organization_id
2383 and serial_number = wjsi_row.asset_number
2384 and maintainable_flag = 'Y';
2385 end if;
2386 end if;
2387 exception
2388 when others then
2389 wip_jsi_utils.record_error(l_msg);
2390 end asset_number;
2391
2392 procedure rebuild_item_id is begin
2393 if(wjsi_row.load_type = wip_constants.create_eam_job and
2394 wjsi_row.rebuild_item_id is not null) then
2395 select 1
2396 into g_dummy
2397 from mtl_system_items
2398 where inventory_item_id = wjsi_row.rebuild_item_id
2399 and organization_id = wjsi_row.organization_id
2400 and eam_item_type = 3;
2401 end if;
2402 exception
2403 when others then
2404 wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_ITEM');
2405 wip_jsi_utils.abort_request;
2406 end rebuild_item_id;
2407
2408 procedure rebuild_serial_number is begin
2409 if(wjsi_row.load_type = wip_constants.create_eam_job) then
2410
2411 if(wjsi_row.rebuild_item_id is null and
2412 wjsi_row.rebuild_serial_number is not null) then
2413 raise fnd_api.g_exc_unexpected_error;
2414 end if;
2415
2416 if(wjsi_row.rebuild_item_id is not null) then
2417 if(wjsi_row.rebuild_serial_number is null) then
2418 if(wjsi_row.maintenance_object_source = 1 and
2419 wjsi_row.maintenance_object_type = 1) then
2420 if(wjsi_row.status_type not in (wip_constants.draft, wip_constants.unreleased)) then
2421 raise fnd_api.g_exc_unexpected_error;
2422 end if;
2423 elsif(wjsi_row.maintenance_object_type not in (2, 3)) then
2424 raise fnd_api.g_exc_unexpected_error;
2425 end if;
2426 else
2427 if(wjsi_row.maintenance_object_type = 1) then
2428 select 1
2429 into g_dummy
2430 from mtl_serial_numbers
2431 where serial_number = wjsi_row.rebuild_serial_number
2432 and inventory_item_id = wjsi_row.rebuild_item_id
2433 and current_organization_id = wjsi_row.organization_id
2434 and current_status in (1,3,4);--defined not used, resides in stores, issued out nocopy of stores (consistent w/EAM UI)
2435 elsif(wjsi_row.maintenance_object_type = 3) then
2436 select 1
2437 into g_dummy
2438 from mtl_serial_numbers
2439 where serial_number = wjsi_row.rebuild_serial_number
2440 and inventory_item_id = wjsi_row.rebuild_item_id
2441 and current_organization_id = wjsi_row.organization_id;
2442 end if;
2443 end if;
2444 end if;
2445 end if;
2446 exception
2447 when others then
2448 wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_SERIAL');
2449 end rebuild_serial_number;
2450
2451 procedure parent_wip_entity_id is begin
2452 -- Per EAM Enh. for H, this field is no longer mandatory
2453 if(wjsi_row.load_type = wip_constants.create_eam_job
2454 and wjsi_row.parent_wip_entity_id is not null) then
2455 select 1
2456 into g_dummy
2457 from wip_entities
2458 where wip_entity_id = wjsi_row.parent_wip_entity_id
2459 and entity_type = wip_constants.eam;
2460 end if;
2461 exception
2462 when others then
2463 wip_jsi_utils.record_error('WIP_ML_EAM_PARENT_ENTITY');
2464 end parent_wip_entity_id;
2465
2466 procedure manual_rebuild_flag is begin
2467 if(wjsi_row.load_type = wip_constants.create_eam_job) then
2468 if(wjsi_row.manual_rebuild_flag is not null and
2469 (wjsi_row.rebuild_item_id is null or
2470 wjsi_row.manual_rebuild_flag not in ('Y', 'N'))) then
2471 raise fnd_api.g_exc_unexpected_error;
2472 end if;
2473
2474 if(wjsi_row.manual_rebuild_flag is null and
2475 wjsi_row.rebuild_item_id is not null) then
2476 raise fnd_api.g_exc_unexpected_error;
2477 end if;
2478 end if;
2479 exception
2480 when others then
2481 wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_FLAG');
2482 end manual_rebuild_flag;
2483
2484 procedure owning_department is
2485 l_job_date DATE;
2486 l_disable_date DATE;
2487 begin
2488 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2489 wjsi_row.owning_department is not null) then
2490 l_job_date := nvl(wjsi_row.last_unit_completion_date, nvl(wjsi_row.first_unit_start_date, wdj_row.scheduled_completion_date));
2491 select disable_date
2492 into l_disable_date
2493 from bom_departments
2494 where department_id = wjsi_row.owning_department
2495 and organization_id = wjsi_row.organization_id;
2496
2497 if(l_disable_date is not null and
2498 l_disable_date < l_job_date) then
2499 raise fnd_api.g_exc_unexpected_error;
2500 end if;
2501 end if;
2502 exception
2503 when others then
2504 wip_jsi_utils.record_error('WIP_ML_EAM_OWNING_DEPT');
2505 end owning_department;
2506
2507 procedure notification_required is begin
2508 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2509 wjsi_row.notification_required is not null and
2510 wjsi_row.notification_required not in ('Y', 'N')) then
2511 raise fnd_api.g_exc_unexpected_error;
2512 end if;
2513 exception
2514 when others then
2515 wip_jsi_utils.record_error('WIP_ML_EAM_NOTIF_REQ');
2516 end notification_required;
2517
2518 procedure shutdown_type is begin
2519 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2520 wjsi_row.shutdown_type is not null) then
2521 select 1
2522 into g_dummy
2523 from mfg_lookups
2524 where lookup_type = 'BOM_EAM_SHUTDOWN_TYPE'
2525 and lookup_code = wjsi_row.shutdown_type
2526 and enabled_flag = 'Y';
2527 end if;
2528 exception
2529 when others then
2530 wip_jsi_utils.record_error('WIP_ML_EAM_SHUTDOWN_TYPE');
2531 end shutdown_type;
2532
2533 procedure tagout_required is begin
2534 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2535 wjsi_row.tagout_required is not null and
2536 wjsi_row.tagout_required not in ('Y', 'N')) then
2537 raise fnd_api.g_exc_unexpected_error;
2538 end if;
2539 exception
2540 when others then
2541 wip_jsi_utils.record_error('WIP_ML_EAM_TAGOUT_REQUIRED');
2542 end tagout_required;
2543
2544 procedure plan_maintenance is begin
2545 if(wjsi_row.load_type = wip_constants.create_eam_job and
2546 wjsi_row.plan_maintenance is not null and
2547 wjsi_row.plan_maintenance not in ('Y', 'N')) then
2548 raise fnd_api.g_exc_unexpected_error;
2549 end if;
2550 exception
2551 when others then
2552 wip_jsi_utils.record_error('WIP_ML_EAM_PLAN_MAINTENANCE');
2553 end plan_maintenance;
2554
2555 procedure work_order_type is begin
2556 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2557 wjsi_row.work_order_type is not null) then
2558 select 1
2559 into g_dummy
2560 from mfg_lookups
2561 where lookup_type = 'WIP_EAM_WORK_ORDER_TYPE'
2562 and lookup_code = wjsi_row.work_order_type
2563 and enabled_flag = 'Y';
2564 end if;
2565 exception
2566 when others then
2567 wip_jsi_utils.record_error('WIP_ML_EAM_WORK_ORDER_TYPE');
2568 end work_order_type;
2569
2570 procedure activity_type is begin
2571 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2572 wjsi_row.activity_type is not null) then
2573 select 1
2574 into g_dummy
2575 from mfg_lookups
2576 where lookup_type = 'MTL_EAM_ACTIVITY_TYPE'
2577 and lookup_code = wjsi_row.activity_type
2578 and enabled_flag = 'Y';
2579 end if;
2580 exception
2581 when others then
2582 wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_TYPE');
2583 end activity_type;
2584
2585 procedure activity_cause is begin
2586 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job,
2587 wip_constants.resched_job) and wjsi_row.activity_cause is not null) then
2588 select 1
2589 into g_dummy
2590 from mfg_lookups
2591 where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
2592 and lookup_code = wjsi_row.activity_cause
2593 and enabled_flag = 'Y';
2594 end if;
2595 exception
2596 when others then
2597 wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_CAUSE');
2598 end activity_cause;
2599
2600 procedure maintenance_object_type is
2601 l_serial_number_control_code NUMBER;
2602 begin
2603 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2604 wjsi_row.maintenance_object_type is not null) then
2605 if(wjsi_row.rebuild_item_id is null and wjsi_row.maintenance_object_type <> 1) then
2606 raise fnd_api.g_exc_unexpected_error;
2607 elsif(wjsi_row.rebuild_item_id is not null) then
2608
2609 select serial_number_control_code
2610 into l_serial_number_control_code
2611 from mtl_system_items
2612 where organization_id = wjsi_row.organization_id
2613 and inventory_item_id = wjsi_row.rebuild_item_id;
2614
2615 if(l_serial_number_control_code in (2, 5, 6) and
2616 wjsi_row.maintenance_object_type not in (1, 3)) then
2617 raise fnd_api.g_exc_unexpected_error;
2618 elsif(l_serial_number_control_code not in (2, 5, 6) and
2619 wjsi_row.maintenance_object_type not in(2, 3)) then
2620 raise fnd_api.g_exc_unexpected_error;
2621 end if;
2622
2623 end if;
2624 end if;
2625 exception
2626 when others then
2627 wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_TYPE');
2628 end maintenance_object_type;
2629
2630 procedure maintenance_object_source is begin
2631 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2632 wjsi_row.maintenance_object_source is not null) then
2633 if(wjsi_row.rebuild_item_id is null and
2634 wjsi_row.maintenance_object_source <> 1) then
2635 raise fnd_api.g_exc_unexpected_error;
2636 else
2637 select 1
2638 into g_dummy
2639 from mfg_lookups
2640 where lookup_type = 'WIP_MAINTENANCE_OBJECT_SOURCE'
2641 and lookup_code = wjsi_row.maintenance_object_source
2642 and enabled_flag = 'Y';
2643 end if;
2644 end if;
2645 exception
2646 when others then
2647 wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_SOURCE');
2648 end maintenance_object_source;
2649
2650 procedure maintenance_object_id is begin
2651 if(wjsi_row.load_type = wip_constants.create_eam_job and
2652 wjsi_row.maintenance_object_id is not null) then
2653
2654 if(wjsi_row.maintenance_object_type = 1) then
2655 if(wjsi_row.rebuild_item_id is null) then
2656 select 1
2657 into g_dummy
2658 from mtl_serial_numbers
2659 where current_organization_id = wjsi_row.organization_id
2660 and inventory_item_id = wjsi_row.asset_group_id
2661 and serial_number = wjsi_row.asset_number
2662 and gen_object_id = wjsi_row.maintenance_object_id;
2663 else
2664 select 1
2665 into g_dummy
2666 from mtl_serial_numbers
2667 where current_organization_id = wjsi_row.organization_id
2668 and inventory_item_id = wjsi_row.rebuild_item_id
2669 and serial_number = wjsi_row.rebuild_serial_number
2670 and gen_object_id = wjsi_row.maintenance_object_id;
2671 end if;
2672 elsif(wjsi_row.maintenance_object_type = 2 and
2673 wjsi_row.maintenance_object_id <> wjsi_row.rebuild_item_id) then
2674 raise fnd_api.g_exc_unexpected_error;
2675 end if;
2676
2677 end if;
2678
2679 if(wjsi_row.load_type = wip_constants.create_eam_job and
2680 wjsi_row.maintenance_object_id is null and
2681 wjsi_row.maintenance_object_type = 3) then
2682 raise fnd_api.g_exc_unexpected_error;
2683 end if;
2684
2685 exception
2686 when others then
2687 wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_ID');
2688 end maintenance_object_id;
2689
2690 procedure pm_schedule_id is begin
2691 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2692 wjsi_row.pm_schedule_id is not null and
2693 wjsi_row.primary_item_id is null) then
2694 raise fnd_api.g_exc_unexpected_error;
2695 end if;
2696 exception
2697 when others then
2698 wip_jsi_utils.record_error('WIP_ML_EAM_PM_SCHEDULE_ID');
2699 end pm_schedule_id;
2700
2701 procedure activity_source is begin
2702 if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2703 wjsi_row.activity_source is not null) then
2704 select 1
2705 into g_dummy
2706 from mfg_lookups
2707 where lookup_type = 'MTL_EAM_ACTIVITY_SOURCE'
2708 and lookup_code = wjsi_row.activity_source
2709 and enabled_flag = 'Y';
2710 end if;
2711 exception
2712 when others then
2713 wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_SOURCE');
2714 end activity_source;
2715
2716
2717 PROCEDURE validate_date_released is
2718 x_status_type number;
2719 x_date_released date;
2720 x_organization_id number;
2721 x_period_exists varchar2(1);
2722 BEGIN
2723 If (wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2724 if (wjsi_row.date_released is not null) then
2725 WIP_JSI_Utils.record_ignored_column_warning ('DATE_RELEASED') ;
2726 end if ;
2727 Elsif (wjsi_row.status_type = WIP_CONSTANTS.RELEASED) then
2728 if (nvl(wjsi_row.date_released,sysdate) > sysdate) then
2729 WIP_JSI_Utils.record_error('WIP_INVALID_RELEASE_DATE',TRUE) ;
2730
2731 update wip_job_schedule_interface
2732 set date_released = sysdate
2733 where rowid = WIP_JSI_Utils.current_rowid ;
2734 end if;
2735
2736 --else
2737 /* fix for bug 2424987 */
2738 Begin
2739 select 'X'
2740 into x_period_exists
2741 from org_acct_periods
2742 where organization_id = wjsi_row.organization_id
2743 and trunc(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(wjsi_row.date_released,sysdate),wjsi_row.organization_id)) between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
2744 and period_close_date is NULL;
2745 Exception
2746 When others then
2747 WIP_JSI_Utils.record_error('WIP_NO_ACCT_PERIOD',FALSE) ;
2748 WIP_JSI_Utils.abort_request;
2749 End;
2750 --end if;
2751
2752 End if;
2753
2754 END validate_date_released;
2755
2756 end wip_jsi_validator;
[Home] [Help]