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