[Home] [Help]
132: --sets the org context (needed for pjm validations)
133: wip_jsi_defaulter.default_values(p_wjsi_row => wjsi_row);
134:
135: /* Fixed bug 3977669 */
136: if ( wjsi_row.load_type <> wip_constants.create_sched and
137: wjsi_row.build_sequence is NULL) then
138:
139: if (wjsi_row.load_type = wip_constants.resched_job) then
140: select WDJ.build_sequence
135: /* Fixed bug 3977669 */
136: if ( wjsi_row.load_type <> wip_constants.create_sched and
137: wjsi_row.build_sequence is NULL) then
138:
139: if (wjsi_row.load_type = wip_constants.resched_job) then
140: select WDJ.build_sequence
141: into wjsi_row.build_sequence
142: from wip_discrete_jobs WDJ
143: where WDJ.wip_entity_id = wjsi_row.wip_entity_id ;
154: setup;
155:
156: --only perform a subset of the validations, mostly those odd cases in which the validation procedures
157: --modify or insert date
158: if(wip_jsi_utils.validation_level in (wip_constants.mrp, wip_constants.ato)) then
159: job_name;
160: job_id;
161: entity_type;
162: schedule_group_id;
361:
362:
363: if(l_serialOp is null) then
364: --in this case, we may need to clear the serialization op if the routing was re-exploded
365: if(l_loadType = wip_constants.resched_job) then
366: update wip_discrete_jobs wdj
367: set serialization_start_op = null
368: where wip_entity_id = l_wipID
369: and serialization_start_op <> 1
375: return;
376: end if;
377:
378: --if serial op provided, the load type must be discrete or ns job
379: if(l_loadType not in (wip_constants.create_job,
380: wip_constants.create_ns_job,
381: wip_constants.resched_job)) then
382: raise fnd_api.g_exc_unexpected_error;
383: end if;
376: end if;
377:
378: --if serial op provided, the load type must be discrete or ns job
379: if(l_loadType not in (wip_constants.create_job,
380: wip_constants.create_ns_job,
381: wip_constants.resched_job)) then
382: raise fnd_api.g_exc_unexpected_error;
383: end if;
384:
377:
378: --if serial op provided, the load type must be discrete or ns job
379: if(l_loadType not in (wip_constants.create_job,
380: wip_constants.create_ns_job,
381: wip_constants.resched_job)) then
382: raise fnd_api.g_exc_unexpected_error;
383: end if;
384:
385: --job must have an assembly, and the assembly must be serial controlled (predefined).
388: from wip_discrete_jobs wdj, mtl_system_items msi
389: where wdj.primary_item_id = msi.inventory_item_id
390: and wdj.organization_id = msi.organization_id
391: and wdj.wip_entity_id = l_wipID
392: and msi.serial_number_control_code = wip_constants.full_sn;
393:
394: open c_ops(v_wip_id => l_wipID);
395:
396: loop
417:
418: --job must be unreleased to change the serialization op on a
419: --reschedule request. This is to guarantee no txns
420: --have taken place.
421: if(l_loadType = wip_constants.resched_job) then
422: select 1
423: into g_dummy
424: from wip_discrete_jobs
425: where wip_entity_id = l_wipID
422: select 1
423: into g_dummy
424: from wip_discrete_jobs
425: where wip_entity_id = l_wipID
426: and status_type = wip_constants.unreleased;
427: end if;
428: exception
429: when others then
430: wip_jsi_utils.record_error('WIP_ML_SERIAL_START_OP');
463: and organization_id = p_org_id;
464: end populate_item;
465:
466: procedure setup is begin
467: if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
468: select status_type,
469: entity_type,
470: job_type,
471: start_quantity,
529: null;
530: end setup;
531:
532: procedure load_type is begin
533: if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
534: wip_constants.create_ns_job, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
535: raise fnd_api.g_exc_unexpected_error;
536: end if;
537: if((wjsi_row.load_type = wip_constants.resched_job and wdj_row.entity_type = wip_constants.eam) or
530: end setup;
531:
532: procedure load_type is begin
533: if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
534: wip_constants.create_ns_job, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
535: raise fnd_api.g_exc_unexpected_error;
536: end if;
537: if((wjsi_row.load_type = wip_constants.resched_job and wdj_row.entity_type = wip_constants.eam) or
538: (wjsi_row.load_type = wip_constants.resched_eam_job and wdj_row.entity_type <> wip_constants.eam)) then
533: if(wjsi_row.load_type not in (wip_constants.create_job, wip_constants.create_sched, wip_constants.resched_job,
534: wip_constants.create_ns_job, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
535: raise fnd_api.g_exc_unexpected_error;
536: end if;
537: if((wjsi_row.load_type = wip_constants.resched_job and wdj_row.entity_type = wip_constants.eam) or
538: (wjsi_row.load_type = wip_constants.resched_eam_job and wdj_row.entity_type <> wip_constants.eam)) then
539: raise fnd_api.g_exc_unexpected_error;
540: end if;
541: exception
534: wip_constants.create_ns_job, wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
535: raise fnd_api.g_exc_unexpected_error;
536: end if;
537: if((wjsi_row.load_type = wip_constants.resched_job and wdj_row.entity_type = wip_constants.eam) or
538: (wjsi_row.load_type = wip_constants.resched_eam_job and wdj_row.entity_type <> wip_constants.eam)) then
539: raise fnd_api.g_exc_unexpected_error;
540: end if;
541: exception
542: when others then
577:
578: procedure job_name is
579: l_count NUMBER;
580: begin
581: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
582: wjsi_row.job_name is not null) then
583: select count(*)
584: into l_count
585: from wip_entities
596: end job_name;
597:
598: procedure entity_type is
599: begin
600: if (wjsi_row.load_type in (wip_constants.create_eam_job,
601: wip_constants.resched_eam_job) or
602: wdj_row.entity_type in (WIP_CONSTANTS.LOTBASED, WIP_CONSTANTS.EAM)) then
603: raise fnd_api.g_exc_unexpected_error;
604: end if;
597:
598: procedure entity_type is
599: begin
600: if (wjsi_row.load_type in (wip_constants.create_eam_job,
601: wip_constants.resched_eam_job) or
602: wdj_row.entity_type in (WIP_CONSTANTS.LOTBASED, WIP_CONSTANTS.EAM)) then
603: raise fnd_api.g_exc_unexpected_error;
604: end if;
605:
598: procedure entity_type is
599: begin
600: if (wjsi_row.load_type in (wip_constants.create_eam_job,
601: wip_constants.resched_eam_job) or
602: wdj_row.entity_type in (WIP_CONSTANTS.LOTBASED, WIP_CONSTANTS.EAM)) then
603: raise fnd_api.g_exc_unexpected_error;
604: end if;
605:
606: exception
611:
612: procedure job_id is
613: l_count NUMBER;
614: begin
615: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
616: select count(*)
617: into l_count
618: from wip_entities
619: where wip_entity_id = wjsi_row.wip_entity_id;
636: if(wjsi_row.kanban_card_id is null) then
637: return;
638: end if;
639:
640: if (wip_jsi_utils.validation_level <> wip_constants.inv) then
641: raise fnd_api.g_exc_unexpected_error;
642: end if ;
643:
644: l_msg := 'WIP_ML_KB_UPDATE_FAILED';
642: end if ;
643:
644: l_msg := 'WIP_ML_KB_UPDATE_FAILED';
645: l_doc_header_id := wjsi_row.wip_entity_id ;
646: if(wjsi_row.load_type = wip_constants.create_job) then
647: l_doc_type := INV_Kanban_PVT.G_doc_type_Discrete_Job ;
648: elsif(wjsi_row.load_type = wip_constants.create_sched) then
649: l_doc_type := INV_Kanban_PVT.G_doc_type_Rep_Schedule ;
650: else
644: l_msg := 'WIP_ML_KB_UPDATE_FAILED';
645: l_doc_header_id := wjsi_row.wip_entity_id ;
646: if(wjsi_row.load_type = wip_constants.create_job) then
647: l_doc_type := INV_Kanban_PVT.G_doc_type_Discrete_Job ;
648: elsif(wjsi_row.load_type = wip_constants.create_sched) then
649: l_doc_type := INV_Kanban_PVT.G_doc_type_Rep_Schedule ;
650: else
651: raise fnd_api.g_exc_unexpected_error;
652: end if;
704: l_msg VARCHAR2(30) := 'WIP_ML_START_QUANTITY';
705: begin
706: if(wjsi_row.start_quantity < 0) then
707: raise fnd_api.g_exc_unexpected_error;
708: elsif(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
709: wjsi_row.start_quantity is null) then
710: raise fnd_api.g_exc_unexpected_error;
711: elsif(wjsi_row.load_type = wip_constants.create_job and wjsi_row.start_quantity = 0) then
712: raise fnd_api.g_exc_unexpected_error;
707: raise fnd_api.g_exc_unexpected_error;
708: elsif(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) and
709: wjsi_row.start_quantity is null) then
710: raise fnd_api.g_exc_unexpected_error;
711: elsif(wjsi_row.load_type = wip_constants.create_job and wjsi_row.start_quantity = 0) then
712: raise fnd_api.g_exc_unexpected_error;
713: elsif(wjsi_row.load_type = wip_constants.resched_job) then
714: if(wjsi_row.start_quantity = 0 and wdj_row.job_type = wip_constants.standard) then
715: raise fnd_api.g_exc_unexpected_error;
709: wjsi_row.start_quantity is null) then
710: raise fnd_api.g_exc_unexpected_error;
711: elsif(wjsi_row.load_type = wip_constants.create_job and wjsi_row.start_quantity = 0) then
712: raise fnd_api.g_exc_unexpected_error;
713: elsif(wjsi_row.load_type = wip_constants.resched_job) then
714: if(wjsi_row.start_quantity = 0 and wdj_row.job_type = wip_constants.standard) then
715: raise fnd_api.g_exc_unexpected_error;
716: elsif(wjsi_row.start_quantity < wdj_row.quantity_completed) then
717: l_msg := 'WIP_ML_RESCHEDULE_QUANTITY';
710: raise fnd_api.g_exc_unexpected_error;
711: elsif(wjsi_row.load_type = wip_constants.create_job and wjsi_row.start_quantity = 0) then
712: raise fnd_api.g_exc_unexpected_error;
713: elsif(wjsi_row.load_type = wip_constants.resched_job) then
714: if(wjsi_row.start_quantity = 0 and wdj_row.job_type = wip_constants.standard) then
715: raise fnd_api.g_exc_unexpected_error;
716: elsif(wjsi_row.start_quantity < wdj_row.quantity_completed) then
717: l_msg := 'WIP_ML_RESCHEDULE_QUANTITY';
718: raise fnd_api.g_exc_unexpected_error;
736: end start_quantity;
737:
738: procedure net_quantity is begin
739:
740: if(wjsi_row.load_type <> wip_constants.create_sched and wjsi_row.net_quantity is not null) then
741: if(wjsi_row.net_quantity not between 0 and wjsi_row.start_quantity) then
742: raise fnd_api.g_exc_unexpected_error;
743: --can't have net qty when creating a ns job w/no item or rescheduling a ns job w/no item
744: elsif(wjsi_row.net_quantity is not null and wjsi_row.net_quantity <> 0 and
741: if(wjsi_row.net_quantity not between 0 and wjsi_row.start_quantity) then
742: raise fnd_api.g_exc_unexpected_error;
743: --can't have net qty when creating a ns job w/no item or rescheduling a ns job w/no item
744: elsif(wjsi_row.net_quantity is not null and wjsi_row.net_quantity <> 0 and
745: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is null) or
746: (wjsi_row.load_type = wip_constants.resched_job and
747: wdj_row.job_type = wip_constants.nonstandard and
748: wdj_row.primary_item_id is null))) then
749: raise fnd_api.g_exc_unexpected_error;
742: raise fnd_api.g_exc_unexpected_error;
743: --can't have net qty when creating a ns job w/no item or rescheduling a ns job w/no item
744: elsif(wjsi_row.net_quantity is not null and wjsi_row.net_quantity <> 0 and
745: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is null) or
746: (wjsi_row.load_type = wip_constants.resched_job and
747: wdj_row.job_type = wip_constants.nonstandard and
748: wdj_row.primary_item_id is null))) then
749: raise fnd_api.g_exc_unexpected_error;
750: end if;
743: --can't have net qty when creating a ns job w/no item or rescheduling a ns job w/no item
744: elsif(wjsi_row.net_quantity is not null and wjsi_row.net_quantity <> 0 and
745: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is null) or
746: (wjsi_row.load_type = wip_constants.resched_job and
747: wdj_row.job_type = wip_constants.nonstandard and
748: wdj_row.primary_item_id is null))) then
749: raise fnd_api.g_exc_unexpected_error;
750: end if;
751: end if;
756:
757: end net_quantity;
758:
759: procedure firm_planned_flag is begin
760: if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761: (wjsi_row.firm_planned_flag = wip_constants.yes and
762: wdj_row.job_type = wip_constants.nonstandard and
763: wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764: (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
757: end net_quantity;
758:
759: procedure firm_planned_flag is begin
760: if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761: (wjsi_row.firm_planned_flag = wip_constants.yes and
762: wdj_row.job_type = wip_constants.nonstandard and
763: wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764: (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
765: raise fnd_api.g_exc_unexpected_error;
758:
759: procedure firm_planned_flag is begin
760: if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761: (wjsi_row.firm_planned_flag = wip_constants.yes and
762: wdj_row.job_type = wip_constants.nonstandard and
763: wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764: (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
765: raise fnd_api.g_exc_unexpected_error;
766: end if;
759: procedure firm_planned_flag is begin
760: if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761: (wjsi_row.firm_planned_flag = wip_constants.yes and
762: wdj_row.job_type = wip_constants.nonstandard and
763: wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764: (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
765: raise fnd_api.g_exc_unexpected_error;
766: end if;
767: exception
760: if((wjsi_row.firm_planned_flag = wip_constants.yes and wjsi_row.load_type = wip_constants.create_ns_job) or
761: (wjsi_row.firm_planned_flag = wip_constants.yes and
762: wdj_row.job_type = wip_constants.nonstandard and
763: wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) or
764: (wjsi_row.firm_planned_flag not in (wip_constants.yes, wip_constants.no))) then
765: raise fnd_api.g_exc_unexpected_error;
766: end if;
767: exception
768: when others then
771:
772: procedure repetitive_schedule_id is
773: l_rep_sched_count NUMBER;
774: begin
775: if(wjsi_row.load_type = wip_constants.create_sched) then
776: if(wjsi_row.repetitive_schedule_id is null) then
777: raise fnd_api.g_exc_unexpected_error;
778: else
779: select count(*)
792: wip_jsi_utils.abort_request;
793: end repetitive_schedule_id;
794:
795: procedure demand_class is begin
796: if(wjsi_row.demand_class is not null and wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
797: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
798: select 1
799: into g_dummy
800: from so_demand_classes_active_v
793: end repetitive_schedule_id;
794:
795: procedure demand_class is begin
796: if(wjsi_row.demand_class is not null and wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
797: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
798: select 1
799: into g_dummy
800: from so_demand_classes_active_v
801: where demand_class_code = wjsi_row.demand_class;
805: wip_jsi_utils.record_error('WIP_ML_DEMAND_CLASS');
806: end demand_class;
807:
808: procedure line_id is begin
809: if(wjsi_row.load_type = wip_constants.create_sched and wjsi_row.line_id is null) then
810: raise fnd_api.g_exc_unexpected_error;
811: elsif(wjsi_row.line_id is not null) then
812: select 1
813: into g_dummy
822: end line_id;
823:
824: procedure schedule_group_id is begin
825: if(wjsi_row.schedule_group_id is not null and
826: wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job, wip_constants.create_ns_job)) then
827: select 1
828: into g_dummy
829: from wip_schedule_groups_val_v
830: where schedule_group_id = wjsi_row.schedule_group_id
837: procedure build_sequence is
838: l_retval boolean;
839: begin
840: if(wjsi_row.build_sequence is not null and
841: wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job, wip_constants.create_ns_job)) then
842: l_retval := wip_validate.build_sequence(p_build_sequence => wjsi_row.build_sequence,
843: p_wip_entity_id => wjsi_row.wip_entity_id,
844: p_organization_id => wjsi_row.organization_id,
845: p_line_id => nvl(wjsi_row.line_id, wdj_row.line_id),
870: AND ORGANIZATION_ID = wjsi_row.Organization_Id;
871:
872: begin
873: --on job creation, status must be unreleased, released, on hold, or draft
874: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
875: wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
876: raise fnd_api.g_exc_unexpected_error;
877:
878: --on reschedule, status must be unreleased, released, complete charges allowed, on hold, cancelled, or pending scheduling
871:
872: begin
873: --on job creation, status must be unreleased, released, on hold, or draft
874: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
875: wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
876: raise fnd_api.g_exc_unexpected_error;
877:
878: --on reschedule, status must be unreleased, released, complete charges allowed, on hold, cancelled, or pending scheduling
879: elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
875: wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.hold, wip_constants.draft)) then
876: raise fnd_api.g_exc_unexpected_error;
877:
878: --on reschedule, status must be unreleased, released, complete charges allowed, on hold, cancelled, or pending scheduling
879: elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
880:
881: --if not changing the status type, skip status type validation processing
882: if(wjsi_row.status_type is null) then
883: return;
882: if(wjsi_row.status_type is null) then
883: return;
884: end if;
885:
886: if(wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
887: wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched) and
888: -- new comp_nochrg status is allowed in eam if old status is comp_chrg
889: (wjsi_row.status_type <> wip_constants.comp_nochrg or wdj_row.status_type <> wip_constants.comp_chrg
890: or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
883: return;
884: end if;
885:
886: if(wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
887: wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched) and
888: -- new comp_nochrg status is allowed in eam if old status is comp_chrg
889: (wjsi_row.status_type <> wip_constants.comp_nochrg or wdj_row.status_type <> wip_constants.comp_chrg
890: or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
891: raise fnd_api.g_exc_unexpected_error;
885:
886: if(wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
887: wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched) and
888: -- new comp_nochrg status is allowed in eam if old status is comp_chrg
889: (wjsi_row.status_type <> wip_constants.comp_nochrg or wdj_row.status_type <> wip_constants.comp_chrg
890: or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
891: raise fnd_api.g_exc_unexpected_error;
892:
893: --additionally, you can not reschedule jobs that are in certain statuses
886: if(wjsi_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
887: wip_constants.hold, wip_constants.cancelled, wip_constants.pend_sched) and
888: -- new comp_nochrg status is allowed in eam if old status is comp_chrg
889: (wjsi_row.status_type <> wip_constants.comp_nochrg or wdj_row.status_type <> wip_constants.comp_chrg
890: or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
891: raise fnd_api.g_exc_unexpected_error;
892:
893: --additionally, you can not reschedule jobs that are in certain statuses
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
890: or wjsi_row.load_type <> wip_constants.resched_eam_job)) then
891: raise fnd_api.g_exc_unexpected_error;
892:
893: --additionally, you can not reschedule jobs that are in certain statuses
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895: wip_constants.hold, wip_constants.cancelled)) then
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
891: raise fnd_api.g_exc_unexpected_error;
892:
893: --additionally, you can not reschedule jobs that are in certain statuses
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895: wip_constants.hold, wip_constants.cancelled)) then
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
892:
893: --additionally, you can not reschedule jobs that are in certain statuses
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895: wip_constants.hold, wip_constants.cancelled)) then
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
900: (wdj_row.status_type in (wip_constants.draft) and
893: --additionally, you can not reschedule jobs that are in certain statuses
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895: wip_constants.hold, wip_constants.cancelled)) then
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
900: (wdj_row.status_type in (wip_constants.draft) and
901: wjsi_row.status_type not in (wip_constants.cancelled)))) then
894: elsif(wdj_row.status_type not in (wip_constants.unreleased, wip_constants.released, wip_constants.comp_chrg,
895: wip_constants.hold, wip_constants.cancelled)) then
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
900: (wdj_row.status_type in (wip_constants.draft) and
901: wjsi_row.status_type not in (wip_constants.cancelled)))) then
902: --these statuses ok for rescheduling eam jobs
896: if(wjsi_row.load_type = wip_constants.resched_eam_job and
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
900: (wdj_row.status_type in (wip_constants.draft) and
901: wjsi_row.status_type not in (wip_constants.cancelled)))) then
902: --these statuses ok for rescheduling eam jobs
903: return;
904: end if;
897: --wdj_row.status_type in (wip_constants.pend_sched, wip_constants.draft)) then
898: (wdj_row.status_type in (wip_constants.pend_sched) or
899: -- draft cannot be changed to cancel for eam jobs
900: (wdj_row.status_type in (wip_constants.draft) and
901: wjsi_row.status_type not in (wip_constants.cancelled)))) then
902: --these statuses ok for rescheduling eam jobs
903: return;
904: end if;
905: l_msg := 'WIP_ML_WIP_DJ_RESCHEDULE';
905: l_msg := 'WIP_ML_WIP_DJ_RESCHEDULE';
906: raise fnd_api.g_exc_unexpected_error;
907: end if;
908: /* Bug 2308832 - Added below if condition for sales order/PO checking */
909: IF (wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
910: WIP_CONSTANTS.COMP_CHRG,
911: WIP_CONSTANTS.COMP_NOCHRG)) THEN
912:
913: /* Bug#2893368 - Added if condition to check whether primary item id
906: raise fnd_api.g_exc_unexpected_error;
907: end if;
908: /* Bug 2308832 - Added below if condition for sales order/PO checking */
909: IF (wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
910: WIP_CONSTANTS.COMP_CHRG,
911: WIP_CONSTANTS.COMP_NOCHRG)) THEN
912:
913: /* Bug#2893368 - Added if condition to check whether primary item id
914: in wip_job_schedule_interface is null */
907: end if;
908: /* Bug 2308832 - Added below if condition for sales order/PO checking */
909: IF (wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
910: WIP_CONSTANTS.COMP_CHRG,
911: WIP_CONSTANTS.COMP_NOCHRG)) THEN
912:
913: /* Bug#2893368 - Added if condition to check whether primary item id
914: in wip_job_schedule_interface is null */
915: if ( wjsi_row.primary_item_id is null ) then
928: * and give error when job linked to SO is being cancelled or
929: * complete-no charge.
930: */
931: if ( l_qty_reserved > 0 and
932: wjsi_row.status_type in (wip_constants.cancelled,
933: wip_constants.comp_nochrg)) then
934: l_msg := 'WIP_CANT_CANCEL_SO';
935: raise fnd_api.g_exc_unexpected_error;
936: end if;
929: * complete-no charge.
930: */
931: if ( l_qty_reserved > 0 and
932: wjsi_row.status_type in (wip_constants.cancelled,
933: wip_constants.comp_nochrg)) then
934: l_msg := 'WIP_CANT_CANCEL_SO';
935: raise fnd_api.g_exc_unexpected_error;
936: end if;
937:
935: raise fnd_api.g_exc_unexpected_error;
936: end if;
937:
938: if ( l_qty_reserved > 0 and
939: wjsi_row.status_type = wip_constants.comp_chrg) then
940: fnd_message.set_name('WIP','WIP_SO_EXISTS');
941: wip_jsi_utils.record_current_error(p_warning_only => true);
942: end if;
943:
946: if (wip_osp.po_req_exists ( wjsi_row.wip_entity_id,
947: null,
948: wjsi_row.organization_id,
949: null,
950: wip_constants.discrete
951: ) = TRUE ) then
952: IF (po_code_release_grp.Current_Release >=
953: po_code_release_grp.PRC_11i_Family_Pack_J) THEN
954:
956: INTO l_propagate_job_change_to_po
957: FROM wip_parameters wp
958: WHERE organization_id = wjsi_row.organization_id;
959:
960: IF(l_propagate_job_change_to_po = WIP_CONSTANTS.YES AND
961: wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
962: WIP_CONSTANTS.COMP_NOCHRG)) THEN
963: -- cancel PO/requisition associated to the job if cancel or
964: -- complete-no-charge
957: FROM wip_parameters wp
958: WHERE organization_id = wjsi_row.organization_id;
959:
960: IF(l_propagate_job_change_to_po = WIP_CONSTANTS.YES AND
961: wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
962: WIP_CONSTANTS.COMP_NOCHRG)) THEN
963: -- cancel PO/requisition associated to the job if cancel or
964: -- complete-no-charge
965: wip_osp.cancelPOReq(p_job_id => wjsi_row.wip_entity_id,
958: WHERE organization_id = wjsi_row.organization_id;
959:
960: IF(l_propagate_job_change_to_po = WIP_CONSTANTS.YES AND
961: wjsi_row.status_type IN (WIP_CONSTANTS.CANCELLED,
962: WIP_CONSTANTS.COMP_NOCHRG)) THEN
963: -- cancel PO/requisition associated to the job if cancel or
964: -- complete-no-charge
965: wip_osp.cancelPOReq(p_job_id => wjsi_row.wip_entity_id,
966: p_org_id => wjsi_row.organization_id,
969: IF(l_return_status <> fnd_api. g_ret_sts_success) THEN
970: -- If we are unable to cancel all PO/requisition associated
971: -- to this job, we will try to cancel as much as we can,
972: -- then user need to manually cancel the rest.
973: po_warning_flag := WIP_CONSTANTS.YES;
974: wip_jsi_utils.record_current_error(p_warning_only => true);
975: END IF; -- check return status
976: ELSE
977: -- propagate_job_change_to_po is manual or job status is
975: END IF; -- check return status
976: ELSE
977: -- propagate_job_change_to_po is manual or job status is
978: -- 'Complete'
979: po_warning_flag := WIP_CONSTANTS.YES;
980: fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
981: wip_jsi_utils.record_current_error(p_warning_only => true);
982: END IF;
983: ELSE
982: END IF;
983: ELSE
984: -- customer does not have PO patchset J onward, so behave the
985: -- old way
986: po_warning_flag := WIP_CONSTANTS.YES;
987: fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
988: wip_jsi_utils.record_current_error(p_warning_only => true);
989: END IF;
990: END IF; -- PO/requisition exists
993: /* Bug 3032515 - Added validation to prevent updation to completed/
994: cancelled/completed-no charges/closed jobs through planner module
995: for which source code is populated as MSC */
996: if ( wjsi_row.source_code = 'MSC' and wdj_row.status_type in
997: (wip_constants.comp_chrg, wip_constants.comp_nochrg,
998: wip_constants.cancelled, wip_constants.closed) ) then
999: wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
1000: wip_jsi_utils.abort_request;
1001: end if;
994: cancelled/completed-no charges/closed jobs through planner module
995: for which source code is populated as MSC */
996: if ( wjsi_row.source_code = 'MSC' and wdj_row.status_type in
997: (wip_constants.comp_chrg, wip_constants.comp_nochrg,
998: wip_constants.cancelled, wip_constants.closed) ) then
999: wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
1000: wip_jsi_utils.abort_request;
1001: end if;
1002:
1000: wip_jsi_utils.abort_request;
1001: end if;
1002:
1003: -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
1004: if ( wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED and
1005: wdj_row.status_type <> WIP_CONSTANTS.UNRELEASED and
1006: WIP_PICKING_PUB.Is_Job_Pick_Released(
1007: p_wip_entity_id => wjsi_row.wip_entity_id,
1008: p_org_id => wjsi_row.organization_id)) then
1001: end if;
1002:
1003: -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
1004: if ( wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED and
1005: wdj_row.status_type <> WIP_CONSTANTS.UNRELEASED and
1006: WIP_PICKING_PUB.Is_Job_Pick_Released(
1007: p_wip_entity_id => wjsi_row.wip_entity_id,
1008: p_org_id => wjsi_row.organization_id)) then
1009: l_msg := 'WIP_UNRLS_JOB/SCHED';
1017: wip_jsi_utils.abort_request;
1018: end status_type;
1019:
1020: procedure processing_work_days is begin
1021: if(wjsi_row.load_type = wip_constants.create_sched and
1022: (wjsi_row.processing_work_days <= 0 or wjsi_row.processing_work_days is null)) then
1023: raise fnd_api.g_exc_unexpected_error;
1024: end if;
1025: exception
1029:
1030: procedure daily_production_rate is
1031: l_max_line_rate NUMBER;
1032: begin
1033: if(wjsi_row.load_type = wip_constants.create_sched) then
1034: if(wjsi_row.daily_production_rate <= 0 or wjsi_row.daily_production_rate is null) then
1035: raise fnd_api.g_exc_unexpected_error;
1036: end if;
1037:
1060: X_Eng_Items_Flag NUMBER;
1061: begin
1062: l_see_eng_items_flag := fnd_profile.value('WIP_SEE_ENG_ITEMS');
1063:
1064: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1065: wip_constants.create_ns_job)) then
1066: if(wjsi_row.primary_item_id is null and wjsi_row.load_type <> wip_constants.create_ns_job) then
1067: l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1068: raise fnd_api.g_exc_unexpected_error;
1061: begin
1062: l_see_eng_items_flag := fnd_profile.value('WIP_SEE_ENG_ITEMS');
1063:
1064: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1065: wip_constants.create_ns_job)) then
1066: if(wjsi_row.primary_item_id is null and wjsi_row.load_type <> wip_constants.create_ns_job) then
1067: l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1068: raise fnd_api.g_exc_unexpected_error;
1069: elsif(wjsi_row.primary_item_id is not null) then
1062: l_see_eng_items_flag := fnd_profile.value('WIP_SEE_ENG_ITEMS');
1063:
1064: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
1065: wip_constants.create_ns_job)) then
1066: if(wjsi_row.primary_item_id is null and wjsi_row.load_type <> wip_constants.create_ns_job) then
1067: l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1068: raise fnd_api.g_exc_unexpected_error;
1069: elsif(wjsi_row.primary_item_id is not null) then
1070: if(primary_item_row.build_in_wip_flag <> 'Y' or
1068: raise fnd_api.g_exc_unexpected_error;
1069: elsif(wjsi_row.primary_item_id is not null) then
1070: if(primary_item_row.build_in_wip_flag <> 'Y' or
1071: primary_item_row.pick_components_flag <> 'N' or
1072: (l_see_eng_items_flag = wip_constants.no and primary_item_row.eng_item_flag = 'Y')) then
1073: l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1074: raise fnd_api.g_exc_unexpected_error;
1075: end if;
1076: if(wjsi_row.load_type = wip_constants.create_sched) then
1072: (l_see_eng_items_flag = wip_constants.no and primary_item_row.eng_item_flag = 'Y')) then
1073: l_msg := 'WIP_ML_PRIMARY_ITEM_ID';
1074: raise fnd_api.g_exc_unexpected_error;
1075: end if;
1076: if(wjsi_row.load_type = wip_constants.create_sched) then
1077: l_msg := 'WIP_ML_REPETITIVE_ITEM';
1078: select 1
1079: into g_dummy
1080: from wip_repetitive_items
1111: wip_jsi_utils.record_error('WIP_ML_ATO_ITEM_NO_BOM');
1112: wip_jsi_utils.abort_request;
1113: end if;
1114: end if;
1115: elsif(wjsi_row.load_type = wip_constants.create_eam_job and
1116: wjsi_row.primary_item_id is not null) then
1117: l_msg := 'WIP_ML_EAM_ACTIVITY';
1118:
1119: if(primary_item_row.eam_item_type <> 2) then
1177:
1178: procedure wip_supply_type is
1179: l_routing_count NUMBER;
1180: begin
1181: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1182: if(wjsi_row.wip_supply_type not in (wip_constants.push, wip_constants.assy_pull, wip_constants.op_pull,
1183: wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184: wip_constants.based_on_bom)) then
1185: --not a valid supply type
1178: procedure wip_supply_type is
1179: l_routing_count NUMBER;
1180: begin
1181: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1182: if(wjsi_row.wip_supply_type not in (wip_constants.push, wip_constants.assy_pull, wip_constants.op_pull,
1183: wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184: wip_constants.based_on_bom)) then
1185: --not a valid supply type
1186: raise fnd_api.g_exc_unexpected_error;
1179: l_routing_count NUMBER;
1180: begin
1181: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1182: if(wjsi_row.wip_supply_type not in (wip_constants.push, wip_constants.assy_pull, wip_constants.op_pull,
1183: wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184: wip_constants.based_on_bom)) then
1185: --not a valid supply type
1186: raise fnd_api.g_exc_unexpected_error;
1187: elsif(wjsi_row.load_type = wip_constants.create_ns_job and
1180: begin
1181: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1182: if(wjsi_row.wip_supply_type not in (wip_constants.push, wip_constants.assy_pull, wip_constants.op_pull,
1183: wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184: wip_constants.based_on_bom)) then
1185: --not a valid supply type
1186: raise fnd_api.g_exc_unexpected_error;
1187: elsif(wjsi_row.load_type = wip_constants.create_ns_job and
1188: wjsi_row.primary_item_id is null and
1183: wip_constants.bulk, wip_constants.vendor, wip_constants.phantom,
1184: wip_constants.based_on_bom)) then
1185: --not a valid supply type
1186: raise fnd_api.g_exc_unexpected_error;
1187: elsif(wjsi_row.load_type = wip_constants.create_ns_job and
1188: wjsi_row.primary_item_id is null and
1189: wjsi_row.wip_supply_type in (wip_constants.assy_pull, wip_constants.op_pull)) then
1190: --can't have pull components for ns job w/no assy
1191: raise fnd_api.g_exc_unexpected_error;
1185: --not a valid supply type
1186: raise fnd_api.g_exc_unexpected_error;
1187: elsif(wjsi_row.load_type = wip_constants.create_ns_job and
1188: wjsi_row.primary_item_id is null and
1189: wjsi_row.wip_supply_type in (wip_constants.assy_pull, wip_constants.op_pull)) then
1190: --can't have pull components for ns job w/no assy
1191: raise fnd_api.g_exc_unexpected_error;
1192: elsif(wjsi_row.wip_supply_type = wip_constants.op_pull) then
1193: select count(*)
1188: wjsi_row.primary_item_id is null and
1189: wjsi_row.wip_supply_type in (wip_constants.assy_pull, wip_constants.op_pull)) then
1190: --can't have pull components for ns job w/no assy
1191: raise fnd_api.g_exc_unexpected_error;
1192: elsif(wjsi_row.wip_supply_type = wip_constants.op_pull) then
1193: select count(*)
1194: into l_routing_count
1195: from bom_operational_routings
1196: where organization_id = wjsi_row.organization_id
1193: select count(*)
1194: into l_routing_count
1195: from bom_operational_routings
1196: where organization_id = wjsi_row.organization_id
1197: and assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id, wjsi_row.primary_item_id)
1198: and nvl(alternate_routing_designator, '@@@') = nvl(wjsi_row.alternate_routing_designator, '@@@')
1199: and nvl(cfm_routing_flag, 2) = 2; --ignore flow routings
1200: if(l_routing_count = 0) then
1201: --can't have op pulls if no routing exists!
1208: wip_jsi_utils.record_error('WIP_ML_WIP_SUPPLY_TYPE');
1209: end wip_supply_type;
1210:
1211: procedure routing_reference_id is begin
1212: if(wjsi_row.load_type = wip_constants.create_ns_job and
1213: wjsi_row.routing_reference_id is not null and
1214: (routing_ref_row.build_in_wip_flag <> 'Y' or
1215: routing_ref_row.pick_components_flag <> 'N' or
1216: routing_ref_row.eng_item_flag <> 'N')) then
1222: wip_jsi_utils.abort_request;
1223: end routing_reference_id;
1224:
1225: procedure bom_reference_id is begin
1226: if(wjsi_row.load_type = wip_constants.create_ns_job and
1227: wjsi_row.bom_reference_id is not null) then
1228: select 1
1229: into g_dummy
1230: from mtl_system_items
1245: l_rtg_item_id NUMBER;
1246: l_msg VARCHAR2(30);
1247: begin
1248: if(wjsi_row.alternate_routing_designator is not null) then
1249: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1250: l_rtg_item_id := wjsi_row.routing_reference_id;
1251: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1252: if(wdj_row.job_type = wip_constants.standard) then
1253: l_rtg_item_id := wdj_row.primary_item_id;
1247: begin
1248: if(wjsi_row.alternate_routing_designator is not null) then
1249: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1250: l_rtg_item_id := wjsi_row.routing_reference_id;
1251: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1252: if(wdj_row.job_type = wip_constants.standard) then
1253: l_rtg_item_id := wdj_row.primary_item_id;
1254: else
1255: l_rtg_item_id := wdj_row.routing_reference_id;
1248: if(wjsi_row.alternate_routing_designator is not null) then
1249: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1250: l_rtg_item_id := wjsi_row.routing_reference_id;
1251: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1252: if(wdj_row.job_type = wip_constants.standard) then
1253: l_rtg_item_id := wdj_row.primary_item_id;
1254: else
1255: l_rtg_item_id := wdj_row.routing_reference_id;
1256: end if;
1259: end if;
1260: l_is_flow_rtg := wip_cfm_filter.org_item_alt_is_cfm(wjsi_row.organization_id,
1261: l_rtg_item_id,
1262: wjsi_row.alternate_routing_designator);
1263: if(l_is_flow_rtg = wip_constants.yes) then
1264: l_msg := 'WIP_ERROR_CHOSEN_RTG_IS_CFM';
1265: raise fnd_api.g_exc_unexpected_error;
1266: end if;
1267: l_msg := 'WIP_ML_ALTERNATE_ROUTING';
1282: procedure alternate_bom_designator is
1283: l_bom_item_id NUMBER;
1284: begin
1285: if(wjsi_row.alternate_bom_designator is not null) then
1286: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1287: l_bom_item_id := wjsi_row.bom_reference_id;
1288: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1289: if(wdj_row.job_type = wip_constants.standard) then
1290: l_bom_item_id := wdj_row.primary_item_id;
1284: begin
1285: if(wjsi_row.alternate_bom_designator is not null) then
1286: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1287: l_bom_item_id := wjsi_row.bom_reference_id;
1288: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1289: if(wdj_row.job_type = wip_constants.standard) then
1290: l_bom_item_id := wdj_row.primary_item_id;
1291: else
1292: l_bom_item_id := wdj_row.bom_reference_id;
1285: if(wjsi_row.alternate_bom_designator is not null) then
1286: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1287: l_bom_item_id := wjsi_row.bom_reference_id;
1288: elsif(wjsi_row.load_type = wip_constants.resched_job) then
1289: if(wdj_row.job_type = wip_constants.standard) then
1290: l_bom_item_id := wdj_row.primary_item_id;
1291: else
1292: l_bom_item_id := wdj_row.bom_reference_id;
1293: end if;
1308: wip_jsi_utils.record_error('WIP_ML_ALTERNATE_BOM');
1309: end alternate_bom_designator;
1310:
1311: procedure project_id is begin
1312: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and
1313: wjsi_row.project_id is not null) then
1314: -- fix MOAC, set id so project view works
1315: fnd_profile.put('MFG_ORGANIZATION_ID',wjsi_row.organization_id);
1316: -- Bug 4890215. Performance Fix
1321: where mpv.project_id = ppp.project_id
1322: and mpv.project_id = wjsi_row.project_id
1323: and ppp.organization_id = wjsi_row.organization_id
1324: and ppp.organization_id = mp.organization_id
1325: and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
1326: end if;
1327: exception
1328: when others then
1329: wip_jsi_utils.record_error('WIP_ML_PROJECT_ID');
1332: procedure task_id is
1333: l_project_id NUMBER;
1334: begin
1335: if(wjsi_row.task_id is not null) then
1336: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job)) then
1337: l_project_id := wjsi_row.project_id;
1338: else
1339: l_project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1340: end if;
1354: l_result VARCHAR2(1);
1355: l_errcode VARCHAR2(80);
1356: l_message VARCHAR2(2000);
1357: begin
1358: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and wjsi_row.project_id is not null) then
1359: l_result := PJM_PROJECT.VALIDATE_PROJ_REFERENCES
1360: (x_inventory_org_id => wjsi_row.organization_id,
1361: x_project_id => wjsi_row.project_id,
1362: x_task_id => wjsi_row.task_id,
1395: if(wjsi_row.last_unit_completion_date is not null) then
1396: l_date_count := l_date_count + 1;
1397: end if;
1398:
1399: if(wjsi_row.load_type = wip_constants.create_ns_job and
1400: wjsi_row.routing_reference_id is null and
1401: l_date_count <> 2) then
1402: --must provide both dates when creating a ns job
1403: raise fnd_api.g_exc_unexpected_error;
1402: --must provide both dates when creating a ns job
1403: raise fnd_api.g_exc_unexpected_error;
1404: end if;
1405:
1406: if(wjsi_row.load_type = wip_constants.resched_job and
1407: wdj_row.job_type = wip_constants.nonstandard and
1408: wdj_row.routing_reference_id is null and
1409: (l_date_count = 1)) then
1410: --when rescheduling a ns job and providing one date, it must have a routing
1403: raise fnd_api.g_exc_unexpected_error;
1404: end if;
1405:
1406: if(wjsi_row.load_type = wip_constants.resched_job and
1407: wdj_row.job_type = wip_constants.nonstandard and
1408: wdj_row.routing_reference_id is null and
1409: (l_date_count = 1)) then
1410: --when rescheduling a ns job and providing one date, it must have a routing
1411: raise fnd_api.g_exc_unexpected_error;
1409: (l_date_count = 1)) then
1410: --when rescheduling a ns job and providing one date, it must have a routing
1411: raise fnd_api.g_exc_unexpected_error;
1412: end if;
1413: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
1414: l_date_count = 0) then
1415: --all job creations must have at least one date
1416: raise fnd_api.g_exc_unexpected_error;
1417: end if;
1414: l_date_count = 0) then
1415: --all job creations must have at least one date
1416: raise fnd_api.g_exc_unexpected_error;
1417: end if;
1418: if(wjsi_row.load_type = wip_constants.resched_job and
1419: l_date_count = 0 and
1420: wjsi_row.start_quantity is not null) then
1421: --when changing the quantity, you must also provide a date
1422: raise fnd_api.g_exc_unexpected_error;
1420: wjsi_row.start_quantity is not null) then
1421: --when changing the quantity, you must also provide a date
1422: raise fnd_api.g_exc_unexpected_error;
1423: end if;
1424: if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job) and
1425: (wjsi_row.allow_explosion = 'N' or wjsi_row.allow_explosion = 'n') and
1426: (l_date_count not in (0,2))) then
1427: --if not exploding, then the user must provide both dates or none at all
1428: raise fnd_api.g_exc_unexpected_error;
1450: l_msg := 'WIP_ML_REPETITIVE_DATES';
1451: --include repetitive dates in date provided count
1452: if(wjsi_row.last_unit_start_date is not null) then
1453: l_date_count := l_date_count + 1;
1454: if(wjsi_row.load_type <> wip_constants.create_sched) then
1455: wip_jsi_utils.record_ignored_column_warning('LAST_UNIT_START_DATE');
1456: end if;
1457: end if;
1458:
1457: end if;
1458:
1459: if(wjsi_row.first_unit_completion_date is not null) then
1460: l_date_count := l_date_count + 1;
1461: if(wjsi_row.load_type <> wip_constants.create_sched) then
1462: wip_jsi_utils.record_ignored_column_warning('FIRST_UNIT_COMPLETION_DATE');
1463: end if;
1464: end if;
1465:
1462: wip_jsi_utils.record_ignored_column_warning('FIRST_UNIT_COMPLETION_DATE');
1463: end if;
1464: end if;
1465:
1466: if(wjsi_row.load_type = wip_constants.create_sched) then
1467: if(l_date_count = 0) then
1468: --must provide at least one date for rep sched
1469: raise fnd_api.g_exc_unexpected_error;
1470: end if;
1649:
1650: procedure scheduling_method is
1651: l_msg_code VARCHAR2(30) := 'WIP_ML_SCHEDULING_METHOD';
1652: begin
1653: if(wjsi_row.load_type <> wip_constants.create_sched) then
1654: if(wjsi_row.scheduling_method not in (wip_constants.routing, wip_constants.leadtime,
1655: wip_constants.ml_manual)) then
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1650: procedure scheduling_method is
1651: l_msg_code VARCHAR2(30) := 'WIP_ML_SCHEDULING_METHOD';
1652: begin
1653: if(wjsi_row.load_type <> wip_constants.create_sched) then
1654: if(wjsi_row.scheduling_method not in (wip_constants.routing, wip_constants.leadtime,
1655: wip_constants.ml_manual)) then
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1651: l_msg_code VARCHAR2(30) := 'WIP_ML_SCHEDULING_METHOD';
1652: begin
1653: if(wjsi_row.load_type <> wip_constants.create_sched) then
1654: if(wjsi_row.scheduling_method not in (wip_constants.routing, wip_constants.leadtime,
1655: wip_constants.ml_manual)) then
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1654: if(wjsi_row.scheduling_method not in (wip_constants.routing, wip_constants.leadtime,
1655: wip_constants.ml_manual)) then
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1660: (wjsi_row.load_type = wip_constants.resched_job and
1661: wdj_row.job_type = wip_constants.nonstandard and
1662: wdj_row.routing_reference_id is null))) then
1655: wip_constants.ml_manual)) then
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1660: (wjsi_row.load_type = wip_constants.resched_job and
1661: wdj_row.job_type = wip_constants.nonstandard and
1662: wdj_row.routing_reference_id is null))) then
1663: --can not do lead time for ns jobs that have no routing reference
1656: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1660: (wjsi_row.load_type = wip_constants.resched_job and
1661: wdj_row.job_type = wip_constants.nonstandard and
1662: wdj_row.routing_reference_id is null))) then
1663: --can not do lead time for ns jobs that have no routing reference
1664: raise fnd_api.g_exc_unexpected_error;
1657: end if;
1658: if(wjsi_row.scheduling_method = wip_constants.leadtime and
1659: ((wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is null) or
1660: (wjsi_row.load_type = wip_constants.resched_job and
1661: wdj_row.job_type = wip_constants.nonstandard and
1662: wdj_row.routing_reference_id is null))) then
1663: --can not do lead time for ns jobs that have no routing reference
1664: raise fnd_api.g_exc_unexpected_error;
1665: elsif(wjsi_row.scheduling_method = wip_constants.ml_manual) then
1661: wdj_row.job_type = wip_constants.nonstandard and
1662: wdj_row.routing_reference_id is null))) then
1663: --can not do lead time for ns jobs that have no routing reference
1664: raise fnd_api.g_exc_unexpected_error;
1665: elsif(wjsi_row.scheduling_method = wip_constants.ml_manual) then
1666: if((wjsi_row.first_unit_start_date is null or wjsi_row.last_unit_completion_date is null) or
1667: wjsi_row.first_unit_start_date > wjsi_row.last_unit_completion_date) then
1668: raise fnd_api.g_exc_unexpected_error;
1669: end if;
1668: raise fnd_api.g_exc_unexpected_error;
1669: end if;
1670: else --routing
1671: if(wjsi_row.allow_explosion in ('n','N') and
1672: wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and
1673: (wjsi_row.first_unit_start_date is null or wjsi_row.last_unit_completion_date is null)) then
1674: --if not exploding, the user must provide both dates.
1675: l_msg_code := 'WIP_ML_SCHEDULING_METHOD2';
1676: raise fnd_api.g_exc_unexpected_error;
1688: l_inv_asset_flag VARCHAR2(1);
1689: l_restrict_subinv_code NUMBER;
1690:
1691: begin
1692: if(wjsi_row.load_type = wip_constants.create_ns_job and
1693: wjsi_row.primary_item_id is null and
1694: wjsi_row.completion_subinventory is not null) then
1695: raise fnd_api.g_exc_unexpected_error;
1696: end if;
1694: wjsi_row.completion_subinventory is not null) then
1695: raise fnd_api.g_exc_unexpected_error;
1696: end if;
1697:
1698: if(wjsi_row.load_type = wip_constants.create_eam_job and
1699: wjsi_row.rebuild_item_id is not null) then
1700: l_inv_item_id := wjsi_row.rebuild_item_id;
1701: select inventory_asset_flag, restrict_subinventories_code
1702: into l_inv_asset_flag, l_restrict_subinv_code
1709: l_restrict_subinv_code := primary_item_row.restrict_subinventories_code;
1710: end if;
1711: /*
1712: if(wjsi_row.completion_subinventory is not null and
1713: (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1714: (wjsi_row.load_type = wip_constants.create_eam_job and
1715: wjsi_row.rebuild_item_id is not null))) then
1716: if(l_inv_item_id is not null) then
1717: if(l_restrict_subinv_code = wip_constants.yes) then
1710: end if;
1711: /*
1712: if(wjsi_row.completion_subinventory is not null and
1713: (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1714: (wjsi_row.load_type = wip_constants.create_eam_job and
1715: wjsi_row.rebuild_item_id is not null))) then
1716: if(l_inv_item_id is not null) then
1717: if(l_restrict_subinv_code = wip_constants.yes) then
1718: if(l_inv_asset_flag = 'N') then
1713: (wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1714: (wjsi_row.load_type = wip_constants.create_eam_job and
1715: wjsi_row.rebuild_item_id is not null))) then
1716: if(l_inv_item_id is not null) then
1717: if(l_restrict_subinv_code = wip_constants.yes) then
1718: if(l_inv_asset_flag = 'N') then
1719: -- If restricted sub, non-asset item, must be in MTL_ITEM_SUB_VAL_V
1720: select 1
1721: into g_dummy
1766: l_restrict_locs NUMBER;
1767: l_item_loc_control NUMBER;
1768: begin
1769:
1770: if((wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1771: (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.rebuild_item_id is not null)) and
1772: wjsi_row.completion_subinventory is null) then
1773: if(wjsi_row.completion_locator_id is not null) then
1774: l_msg := 'WIP_ML_COMPLETION_LOCATOR';
1767: l_item_loc_control NUMBER;
1768: begin
1769:
1770: if((wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job) or
1771: (wjsi_row.load_type = wip_constants.create_eam_job and wjsi_row.rebuild_item_id is not null)) and
1772: wjsi_row.completion_subinventory is null) then
1773: if(wjsi_row.completion_locator_id is not null) then
1774: l_msg := 'WIP_ML_COMPLETION_LOCATOR';
1775: raise fnd_api.g_exc_unexpected_error;
1777: return;
1778: end if;
1779: end if;
1780:
1781: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1782:
1783: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1784: l_item_id := wjsi_row.rebuild_item_id;
1785: else
1779: end if;
1780:
1781: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job)) then
1782:
1783: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1784: l_item_id := wjsi_row.rebuild_item_id;
1785: else
1786: l_item_id := wdj_row.rebuild_item_id;
1787: end if;
1801: end if;
1802:
1803: --if rescheduling, sub/loc are not modifiable. Make sure new values don't get inserted
1804: --into the tables. For proj/task, default from the existing job if user is not changing them.
1805: if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
1806: wjsi_row.completion_locator_id := wdj_row.completion_locator_id;
1807: wjsi_row.project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1808: wjsi_row.task_id := nvl(wjsi_row.task_id, wdj_row.task_id);
1809: wjsi_row.completion_subinventory := wdj_row.completion_subinventory;
1810: end if;
1811: -- if rescheduling, sub/loc are now modifiable.
1812: -- will update from both wjsi.completion_subinventory and
1813: -- and wjsi.locator only if wjsi.completion_subinventory is not null
1814: if(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
1815: wjsi_row.project_id := nvl(wjsi_row.project_id, wdj_row.project_id);
1816: wjsi_row.task_id := nvl(wjsi_row.task_id, wdj_row.task_id);
1817: wjsi_row.completion_subinventory :=
1818: nvl(wjsi_row.completion_subinventory, wdj_row.completion_subinventory);
1850: end if;
1851: end if;
1852: end if;
1853: --done with project locator defaulting/validation.
1854: if(wjsi_row.load_type <> wip_constants.create_sched and
1855: wjsi_row.completion_subinventory is not null) then
1856: l_msg := 'WIP_ML_INVALID_LOCATOR';
1857: select sub.locator_type, mp.stock_locator_control_code
1858: into l_sub_loc_control, l_org_loc_control
1885: wip_jsi_utils.abort_request;
1886: end completion_locator_id;
1887:
1888: procedure due_date is begin
1889: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1890: if(wjsi_row.due_date is not null and wjsi_row.pm_schedule_id is null) then
1891: raise fnd_api.g_exc_unexpected_error;
1892: end if;
1893: elsif(wjsi_row.due_date is not null and wjsi_row.load_type = wip_constants.resched_eam_job) then
1889: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1890: if(wjsi_row.due_date is not null and wjsi_row.pm_schedule_id is null) then
1891: raise fnd_api.g_exc_unexpected_error;
1892: end if;
1893: elsif(wjsi_row.due_date is not null and wjsi_row.load_type = wip_constants.resched_eam_job) then
1894: if(wdj_row.status_type <> wip_constants.draft) then
1895: raise fnd_api.g_exc_unexpected_error;
1896: end if;
1897: end if;
1890: if(wjsi_row.due_date is not null and wjsi_row.pm_schedule_id is null) then
1891: raise fnd_api.g_exc_unexpected_error;
1892: end if;
1893: elsif(wjsi_row.due_date is not null and wjsi_row.load_type = wip_constants.resched_eam_job) then
1894: if(wdj_row.status_type <> wip_constants.draft) then
1895: raise fnd_api.g_exc_unexpected_error;
1896: end if;
1897: end if;
1898: exception
1902: end due_date;
1903:
1904: procedure date_released is begin
1905: if(wjsi_row.date_released > sysdate and
1906: wjsi_row.status_type = wip_constants.released) then
1907: raise fnd_api.g_exc_unexpected_error;
1908: end if;
1909: exception when others then
1910: wip_jsi_utils.record_error('WIP_ML_INVALID_RELEASE_DATE');
1911: wip_jsi_utils.abort_request;
1912: end date_released;
1913:
1914: procedure requested_start_date is begin
1915: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1916: if(wjsi_row.requested_start_date is not null and wjsi_row.pm_schedule_id is null) then
1917: raise fnd_api.g_exc_unexpected_error;
1918: end if;
1919: elsif(wjsi_row.load_type = wip_constants.resched_eam_job) then
1915: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1916: if(wjsi_row.requested_start_date is not null and wjsi_row.pm_schedule_id is null) then
1917: raise fnd_api.g_exc_unexpected_error;
1918: end if;
1919: elsif(wjsi_row.load_type = wip_constants.resched_eam_job) then
1920: if(wjsi_row.requested_start_date is not null and wdj_row.status_type <> wip_constants.draft) then
1921: raise fnd_api.g_exc_unexpected_error;
1922: end if;
1923: end if;
1916: if(wjsi_row.requested_start_date is not null and wjsi_row.pm_schedule_id is null) then
1917: raise fnd_api.g_exc_unexpected_error;
1918: end if;
1919: elsif(wjsi_row.load_type = wip_constants.resched_eam_job) then
1920: if(wjsi_row.requested_start_date is not null and wdj_row.status_type <> wip_constants.draft) then
1921: raise fnd_api.g_exc_unexpected_error;
1922: end if;
1923: end if;
1924: exception
1931: is_unit_effective_item boolean;
1932: l_bom_item_id NUMBER;
1933: begin
1934:
1935: if(wjsi_row.load_type = wip_constants.create_ns_job) then
1936: l_bom_item_id := wjsi_row.bom_reference_id;
1937: else
1938: l_bom_item_id := wjsi_row.primary_item_id;
1939: end if;
1964: end;
1965: end if;
1966:
1967: -- You cannot create a repetitive schedule for a unit effective assembly.
1968: if(wjsi_row.load_type = wip_constants.create_sched and
1969: is_unit_effective_item) then
1970: wip_jsi_utils.record_error('WIP_ML_NO_UNIT_EFF_SCHED');
1971: raise fnd_api.g_exc_unexpected_error;
1972: end if;
1971: raise fnd_api.g_exc_unexpected_error;
1972: end if;
1973:
1974: -- If this is a discrete job load...
1975: if (wjsi_row.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB))
1976: then
1977: -- It is an error to provide unit number for non-unit effective assemblies.
1978: if(not is_unit_effective_item and wjsi_row.end_item_unit_number is not null) then
1979: wip_jsi_utils.record_error('WIP_ML_UNIT_NUM_MEANINGLESS');
1987: end if;
1988: end if;
1989:
1990: --if request is for reschedule, keep as is for all cases except when job_status is unreleased
1991: if (wjsi_row.load_type in (WIP_CONSTANTS.RESCHED_JOB,
1992: WIP_CONSTANTS.RESCHED_LOT_JOB))
1993: then
1994: -- added for bug#2766839, added check when the assembly is not is_unit_effective_item
1995: if(not is_unit_effective_item) then
1988: end if;
1989:
1990: --if request is for reschedule, keep as is for all cases except when job_status is unreleased
1991: if (wjsi_row.load_type in (WIP_CONSTANTS.RESCHED_JOB,
1992: WIP_CONSTANTS.RESCHED_LOT_JOB))
1993: then
1994: -- added for bug#2766839, added check when the assembly is not is_unit_effective_item
1995: if(not is_unit_effective_item) then
1996: -- It is an error to provide unit number for non-unit effective assemblies.
1999: end if;
2000: wjsi_row.end_item_unit_number := null;
2001:
2002: -- if status is unreleased, the end_item_unit_number can be modified
2003: elsif (wdj_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2004: if (wjsi_row.end_item_unit_number is null) then
2005: begin
2006: select end_item_unit_number into wjsi_row.end_item_unit_number
2007: from wip_discrete_jobs
2063: l_tol_type NUMBER := wjsi_row.overcompletion_tolerance_type;
2064: l_tol_value NUMBER := wjsi_row.overcompletion_tolerance_value;
2065: l_msg VARCHAR2(30);
2066: begin
2067: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job) or
2068: (wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is not null)) then
2069: if(l_tol_value is not null and l_tol_type is not null) then
2070: if(l_tol_type not in (wip_constants.percent, wip_constants.amount)) then
2071: l_msg:= 'WIP_ML_COMP_TOLERANCE_TYPE';
2064: l_tol_value NUMBER := wjsi_row.overcompletion_tolerance_value;
2065: l_msg VARCHAR2(30);
2066: begin
2067: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job) or
2068: (wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is not null)) then
2069: if(l_tol_value is not null and l_tol_type is not null) then
2070: if(l_tol_type not in (wip_constants.percent, wip_constants.amount)) then
2071: l_msg:= 'WIP_ML_COMP_TOLERANCE_TYPE';
2072: raise fnd_api.g_exc_unexpected_error;
2066: begin
2067: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.resched_job) or
2068: (wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.primary_item_id is not null)) then
2069: if(l_tol_value is not null and l_tol_type is not null) then
2070: if(l_tol_type not in (wip_constants.percent, wip_constants.amount)) then
2071: l_msg:= 'WIP_ML_COMP_TOLERANCE_TYPE';
2072: raise fnd_api.g_exc_unexpected_error;
2073: end if;
2074: if(l_tol_value < 0) then
2085: wip_jsi_utils.record_error(l_msg);
2086: end overcompletion;
2087:
2088: procedure class_code is begin
2089: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
2090: wjsi_row.class_code is null) then
2091: raise fnd_api.g_exc_unexpected_error;
2092: end if;
2093: if(wjsi_row.load_type = wip_constants.create_job) then
2089: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
2090: wjsi_row.class_code is null) then
2091: raise fnd_api.g_exc_unexpected_error;
2092: end if;
2093: if(wjsi_row.load_type = wip_constants.create_job) then
2094: if(wjsi_row.project_id is null) then
2095: select 1
2096: into g_dummy
2097: from dual
2098: where exists(select 1
2099: from cst_cg_wip_acct_classes_v
2100: where class_code = wjsi_row.class_code
2101: and organization_id = wjsi_row.organization_id
2102: and class_type = wip_constants.discrete);
2103: else
2104: select 1
2105: into g_dummy
2106: from dual
2107: where exists(select 1
2108: from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
2109: where ccwac.class_code = wjsi_row.class_code
2110: and ccwac.organization_id = wjsi_row.organization_id
2111: and ccwac.class_type = wip_constants.discrete
2112: and mp.organization_id = wjsi_row.organization_id
2113: and ( mp.primary_cost_method = wip_constants.cost_std
2114: or ccwac.cost_group_id = (select costing_group_id
2115: from mrp_project_parameters mpp
2109: where ccwac.class_code = wjsi_row.class_code
2110: and ccwac.organization_id = wjsi_row.organization_id
2111: and ccwac.class_type = wip_constants.discrete
2112: and mp.organization_id = wjsi_row.organization_id
2113: and ( mp.primary_cost_method = wip_constants.cost_std
2114: or ccwac.cost_group_id = (select costing_group_id
2115: from mrp_project_parameters mpp
2116: where organization_id = wjsi_row.organization_id
2117: and mpp.project_id = wjsi_row.project_id)
2117: and mpp.project_id = wjsi_row.project_id)
2118: )
2119: );
2120: end if;
2121: elsif(wjsi_row.load_type = wip_constants.create_ns_job) then
2122: select 1
2123: into g_dummy
2124: from dual
2125: where exists(select 1
2126: from wip_non_standard_classes_val_v
2127: where class_code = wjsi_row.class_code
2128: and organization_id = wjsi_row.organization_id);
2129:
2130: elsif(wjsi_row.load_type = wip_constants.create_eam_job) then
2131: if(wjsi_row.project_id is null) then
2132: select 1
2133: into g_dummy
2134: from dual
2135: where exists(select 1
2136: from cst_cg_wip_acct_classes_v
2137: where class_code = wjsi_row.class_code
2138: and organization_id = wjsi_row.organization_id
2139: and class_type = wip_constants.eam);
2140: else
2141: select 1
2142: into g_dummy
2143: from dual
2144: where exists(select 1
2145: from cst_cg_wip_acct_classes_v ccwac, mtl_parameters mp
2146: where ccwac.class_code = wjsi_row.class_code
2147: and ccwac.organization_id = wjsi_row.organization_id
2148: and ccwac.class_type = wip_constants.eam
2149: and mp.organization_id = wjsi_row.organization_id
2150: and ( mp.primary_cost_method = wip_constants.cost_std
2151: or ccwac.cost_group_id = (select costing_group_id
2152: from mrp_project_parameters mpp
2146: where ccwac.class_code = wjsi_row.class_code
2147: and ccwac.organization_id = wjsi_row.organization_id
2148: and ccwac.class_type = wip_constants.eam
2149: and mp.organization_id = wjsi_row.organization_id
2150: and ( mp.primary_cost_method = wip_constants.cost_std
2151: or ccwac.cost_group_id = (select costing_group_id
2152: from mrp_project_parameters mpp
2153: where organization_id = wjsi_row.organization_id
2154: and mpp.project_id = wjsi_row.project_id)
2167: l_rtg_count NUMBER := 0; --if > 0 a routing exists
2168: l_qty NUMBER;
2169: l_msg VARCHAR2(30);
2170: begin
2171: if(wjsi_row.load_type = wip_constants.create_sched) then
2172: l_entity_type := wip_constants.repetitive;
2173: if(wjsi_row.first_unit_start_date is null) then
2174: if(wjsi_row.last_unit_start_date is not null) then
2175: l_sched_dir := wip_constants.lusd;
2168: l_qty NUMBER;
2169: l_msg VARCHAR2(30);
2170: begin
2171: if(wjsi_row.load_type = wip_constants.create_sched) then
2172: l_entity_type := wip_constants.repetitive;
2173: if(wjsi_row.first_unit_start_date is null) then
2174: if(wjsi_row.last_unit_start_date is not null) then
2175: l_sched_dir := wip_constants.lusd;
2176: elsif(wjsi_row.first_unit_completion_date is not null) then
2171: if(wjsi_row.load_type = wip_constants.create_sched) then
2172: l_entity_type := wip_constants.repetitive;
2173: if(wjsi_row.first_unit_start_date is null) then
2174: if(wjsi_row.last_unit_start_date is not null) then
2175: l_sched_dir := wip_constants.lusd;
2176: elsif(wjsi_row.first_unit_completion_date is not null) then
2177: l_sched_dir := wip_constants.fucd;
2178: else
2179: l_sched_dir := wip_constants.lucd;
2173: if(wjsi_row.first_unit_start_date is null) then
2174: if(wjsi_row.last_unit_start_date is not null) then
2175: l_sched_dir := wip_constants.lusd;
2176: elsif(wjsi_row.first_unit_completion_date is not null) then
2177: l_sched_dir := wip_constants.fucd;
2178: else
2179: l_sched_dir := wip_constants.lucd;
2180: end if;
2181:
2175: l_sched_dir := wip_constants.lusd;
2176: elsif(wjsi_row.first_unit_completion_date is not null) then
2177: l_sched_dir := wip_constants.fucd;
2178: else
2179: l_sched_dir := wip_constants.lucd;
2180: end if;
2181:
2182: wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2183: x_fixed_lead => primary_item_row.fixed_lead_time,
2183: x_fixed_lead => primary_item_row.fixed_lead_time,
2184: x_var_lead => primary_item_row.variable_lead_time,
2185: x_quantity => wjsi_row.processing_work_days * wjsi_row.daily_production_rate,
2186: x_proc_days => wjsi_row.processing_work_days,
2187: x_entity_type => wip_constants.repetitive,
2188: x_fusd => wjsi_row.first_unit_start_date,
2189: x_fucd => wjsi_row.first_unit_completion_date,
2190: x_lusd => wjsi_row.last_unit_start_date,
2191: x_lucd => wjsi_row.last_unit_completion_date,
2196: raise fnd_api.g_exc_unexpected_error;
2197: end if;
2198: end if;
2199: else
2200: if(wjsi_row.load_type = wip_constants.create_ns_job and wjsi_row.routing_reference_id is not null) then
2201: select count(*)
2202: into l_rtg_count
2203: from bom_operational_routings
2204: where assembly_item_id = wjsi_row.routing_reference_id
2205: and organization_id = wjsi_row.organization_id
2206: and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
2207: and nvl(cfm_routing_flag, 2) = 2;
2208: l_qty := wjsi_row.start_quantity;
2209: elsif(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job)) then
2210: select count(*)
2211: into l_rtg_count
2212: from bom_operational_routings
2213: where assembly_item_id = wjsi_row.primary_item_id
2214: and organization_id = wjsi_row.organization_id
2215: and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@')
2216: and nvl(cfm_routing_flag, 2) = 2;
2217: l_qty := wjsi_row.start_quantity;
2218: elsif(wjsi_row.load_type in (wip_constants.resched_job, wip_constants.resched_eam_job)) then
2219: select count(*)
2220: into l_rtg_count
2221: from wip_operations
2222: where wip_entity_id = wjsi_row.wip_entity_id;
2222: where wip_entity_id = wjsi_row.wip_entity_id;
2223: l_qty := nvl(wjsi_row.start_quantity, wdj_row.start_quantity);
2224: end if;
2225: --if no routing exists, update the scheduling method appropriately
2226: if(wjsi_row.scheduling_method = wip_constants.routing and l_rtg_count = 0) then
2227: if(wjsi_row.first_unit_start_date is not null and
2228: wjsi_row.last_unit_completion_date is not null) then
2229: wjsi_row.scheduling_method := wip_constants.ml_manual;
2230: else
2225: --if no routing exists, update the scheduling method appropriately
2226: if(wjsi_row.scheduling_method = wip_constants.routing and l_rtg_count = 0) then
2227: if(wjsi_row.first_unit_start_date is not null and
2228: wjsi_row.last_unit_completion_date is not null) then
2229: wjsi_row.scheduling_method := wip_constants.ml_manual;
2230: else
2231: wjsi_row.scheduling_method := wip_constants.leadtime;
2232: end if;
2233: end if;
2227: if(wjsi_row.first_unit_start_date is not null and
2228: wjsi_row.last_unit_completion_date is not null) then
2229: wjsi_row.scheduling_method := wip_constants.ml_manual;
2230: else
2231: wjsi_row.scheduling_method := wip_constants.leadtime;
2232: end if;
2233: end if;
2234:
2235: if((wjsi_row.first_unit_start_date is null and
2234:
2235: if((wjsi_row.first_unit_start_date is null and
2236: wjsi_row.last_unit_completion_date is not null) or
2237: (wjsi_row.last_unit_completion_date is not null and
2238: wjsi_row.scheduling_method = wip_constants.leadtime)) then
2239: /* Estimate Start Date */
2240: wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2241: x_fixed_lead => primary_item_row.fixed_lead_time,
2242: x_var_lead => primary_item_row.variable_lead_time,
2246: x_fusd => '',
2247: x_fucd => '',
2248: x_lusd => '',
2249: x_lucd => wjsi_row.last_unit_completion_date,
2250: x_sched_dir => wip_constants.lucd,
2251: x_est_date => wjsi_row.first_unit_start_date);
2252: if(wjsi_row.first_unit_start_date is null) then
2253: l_msg := 'WIP_ML_EST_LEADTIME';
2254: raise fnd_api.g_exc_unexpected_error;
2254: raise fnd_api.g_exc_unexpected_error;
2255: end if;
2256: elsif(wjsi_row.last_unit_completion_date is null and
2257: wjsi_row.first_unit_start_date is not null and
2258: wjsi_row.scheduling_method = wip_constants.leadtime) then
2259: /* Estimate Completion Date */
2260: wip_calendar.estimate_leadtime(x_org_id => wjsi_row.organization_id,
2261: x_fixed_lead => primary_item_row.fixed_lead_time,
2262: x_var_lead => primary_item_row.variable_lead_time,
2266: x_fusd => wjsi_row.first_unit_start_date,
2267: x_fucd => '',
2268: x_lusd => '',
2269: x_lucd => '',
2270: x_sched_dir => wip_constants.fusd,
2271: x_est_date => wjsi_row.last_unit_completion_date);
2272: if(wjsi_row.last_unit_completion_date is null) then
2273: l_msg := 'WIP_NO_CALENDAR';
2274: raise fnd_api.g_exc_unexpected_error;
2283: procedure bom_revision is
2284: l_start_date DATE;
2285: l_bom_item_id NUMBER;
2286: begin
2287: if(wjsi_row.load_type = wip_constants.create_ns_job) then
2288: l_bom_item_id := wjsi_row.bom_reference_id;
2289: else
2290: l_bom_item_id := wjsi_row.primary_item_id;
2291: end if;
2289: else
2290: l_bom_item_id := wjsi_row.primary_item_id;
2291: end if;
2292: l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2293: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2294: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2295: wip_revisions.bom_revision(p_organization_id => wjsi_row.organization_id,
2296: p_item_id => l_bom_item_id,
2297: p_revision => wjsi_row.bom_revision,
2290: l_bom_item_id := wjsi_row.primary_item_id;
2291: end if;
2292: l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2293: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2294: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2295: wip_revisions.bom_revision(p_organization_id => wjsi_row.organization_id,
2296: p_item_id => l_bom_item_id,
2297: p_revision => wjsi_row.bom_revision,
2298: p_revision_date => wjsi_row.bom_revision_date,
2313: l_start_date DATE;
2314: l_rtg_item_id NUMBER;
2315: l_count NUMBER;
2316: begin
2317: if(wjsi_row.load_type = wip_constants.create_ns_job) then
2318: l_rtg_item_id := wjsi_row.routing_reference_id;
2319: else
2320: l_rtg_item_id := wjsi_row.primary_item_id;
2321: end if;
2319: else
2320: l_rtg_item_id := wjsi_row.primary_item_id;
2321: end if;
2322: l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2323: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2324: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2325: select count(*)
2326: into l_count
2327: from bom_operational_routings
2320: l_rtg_item_id := wjsi_row.primary_item_id;
2321: end if;
2322: l_start_date := greatest(wjsi_row.first_unit_start_date, sysdate);
2323: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
2324: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2325: select count(*)
2326: into l_count
2327: from bom_operational_routings
2328: where assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id,
2324: wip_constants.create_ns_job, wip_constants.create_eam_job)) then
2325: select count(*)
2326: into l_count
2327: from bom_operational_routings
2328: where assembly_item_id = decode(wjsi_row.load_type, wip_constants.create_ns_job, wjsi_row.routing_reference_id,
2329: wjsi_row.primary_item_id)
2330: and organization_id = wjsi_row.organization_id
2331: and nvl(alternate_routing_designator, '@@') = nvl(wjsi_row.alternate_routing_designator, '@@');
2332: if(l_count > 0) then
2342: WIP_JSI_Utils.record_error('WIP_ML_ROUTING_REVISION');
2343: end routing_revision;
2344:
2345: procedure asset_group_id is begin
2346: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2347: if(wjsi_row.asset_group_id is not null) then
2348: select 1
2349: into g_dummy
2350: from mtl_system_items
2364: procedure asset_number is
2365: l_msg VARCHAR2(30);
2366: begin
2367: l_msg := 'WIP_ML_EAM_ASSET_NUM';
2368: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2369:
2370: -- Per EAM Enh. for H, this field is no longer mandatory
2371:
2372: if(wjsi_row.asset_group_id is null and
2389: wip_jsi_utils.record_error(l_msg);
2390: end asset_number;
2391:
2392: procedure rebuild_item_id is begin
2393: if(wjsi_row.load_type = wip_constants.create_eam_job and
2394: wjsi_row.rebuild_item_id is not null) then
2395: select 1
2396: into g_dummy
2397: from mtl_system_items
2405: wip_jsi_utils.abort_request;
2406: end rebuild_item_id;
2407:
2408: procedure rebuild_serial_number is begin
2409: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2410:
2411: if(wjsi_row.rebuild_item_id is null and
2412: wjsi_row.rebuild_serial_number is not null) then
2413: raise fnd_api.g_exc_unexpected_error;
2416: if(wjsi_row.rebuild_item_id is not null) then
2417: if(wjsi_row.rebuild_serial_number is null) then
2418: if(wjsi_row.maintenance_object_source = 1 and
2419: wjsi_row.maintenance_object_type = 1) then
2420: if(wjsi_row.status_type not in (wip_constants.draft, wip_constants.unreleased)) then
2421: raise fnd_api.g_exc_unexpected_error;
2422: end if;
2423: elsif(wjsi_row.maintenance_object_type not in (2, 3)) then
2424: raise fnd_api.g_exc_unexpected_error;
2449: end rebuild_serial_number;
2450:
2451: procedure parent_wip_entity_id is begin
2452: -- Per EAM Enh. for H, this field is no longer mandatory
2453: if(wjsi_row.load_type = wip_constants.create_eam_job
2454: and wjsi_row.parent_wip_entity_id is not null) then
2455: select 1
2456: into g_dummy
2457: from wip_entities
2455: select 1
2456: into g_dummy
2457: from wip_entities
2458: where wip_entity_id = wjsi_row.parent_wip_entity_id
2459: and entity_type = wip_constants.eam;
2460: end if;
2461: exception
2462: when others then
2463: wip_jsi_utils.record_error('WIP_ML_EAM_PARENT_ENTITY');
2463: wip_jsi_utils.record_error('WIP_ML_EAM_PARENT_ENTITY');
2464: end parent_wip_entity_id;
2465:
2466: procedure manual_rebuild_flag is begin
2467: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2468: if(wjsi_row.manual_rebuild_flag is not null and
2469: (wjsi_row.rebuild_item_id is null or
2470: wjsi_row.manual_rebuild_flag not in ('Y', 'N'))) then
2471: raise fnd_api.g_exc_unexpected_error;
2484: procedure owning_department is
2485: l_job_date DATE;
2486: l_disable_date DATE;
2487: begin
2488: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2489: wjsi_row.owning_department is not null) then
2490: l_job_date := nvl(wjsi_row.last_unit_completion_date, nvl(wjsi_row.first_unit_start_date, wdj_row.scheduled_completion_date));
2491: select disable_date
2492: into l_disable_date
2504: wip_jsi_utils.record_error('WIP_ML_EAM_OWNING_DEPT');
2505: end owning_department;
2506:
2507: procedure notification_required is begin
2508: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2509: wjsi_row.notification_required is not null and
2510: wjsi_row.notification_required not in ('Y', 'N')) then
2511: raise fnd_api.g_exc_unexpected_error;
2512: end if;
2515: wip_jsi_utils.record_error('WIP_ML_EAM_NOTIF_REQ');
2516: end notification_required;
2517:
2518: procedure shutdown_type is begin
2519: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2520: wjsi_row.shutdown_type is not null) then
2521: select 1
2522: into g_dummy
2523: from mfg_lookups
2530: wip_jsi_utils.record_error('WIP_ML_EAM_SHUTDOWN_TYPE');
2531: end shutdown_type;
2532:
2533: procedure tagout_required is begin
2534: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2535: wjsi_row.tagout_required is not null and
2536: wjsi_row.tagout_required not in ('Y', 'N')) then
2537: raise fnd_api.g_exc_unexpected_error;
2538: end if;
2541: wip_jsi_utils.record_error('WIP_ML_EAM_TAGOUT_REQUIRED');
2542: end tagout_required;
2543:
2544: procedure plan_maintenance is begin
2545: if(wjsi_row.load_type = wip_constants.create_eam_job and
2546: wjsi_row.plan_maintenance is not null and
2547: wjsi_row.plan_maintenance not in ('Y', 'N')) then
2548: raise fnd_api.g_exc_unexpected_error;
2549: end if;
2552: wip_jsi_utils.record_error('WIP_ML_EAM_PLAN_MAINTENANCE');
2553: end plan_maintenance;
2554:
2555: procedure work_order_type is begin
2556: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2557: wjsi_row.work_order_type is not null) then
2558: select 1
2559: into g_dummy
2560: from mfg_lookups
2567: wip_jsi_utils.record_error('WIP_ML_EAM_WORK_ORDER_TYPE');
2568: end work_order_type;
2569:
2570: procedure activity_type is begin
2571: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2572: wjsi_row.activity_type is not null) then
2573: select 1
2574: into g_dummy
2575: from mfg_lookups
2582: wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_TYPE');
2583: end activity_type;
2584:
2585: procedure activity_cause is begin
2586: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job,
2587: wip_constants.resched_job) and wjsi_row.activity_cause is not null) then
2588: select 1
2589: into g_dummy
2590: from mfg_lookups
2583: end activity_type;
2584:
2585: procedure activity_cause is begin
2586: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job,
2587: wip_constants.resched_job) and wjsi_row.activity_cause is not null) then
2588: select 1
2589: into g_dummy
2590: from mfg_lookups
2591: where lookup_type = 'MTL_EAM_ACTIVITY_CAUSE'
2599:
2600: procedure maintenance_object_type is
2601: l_serial_number_control_code NUMBER;
2602: begin
2603: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2604: wjsi_row.maintenance_object_type is not null) then
2605: if(wjsi_row.rebuild_item_id is null and wjsi_row.maintenance_object_type <> 1) then
2606: raise fnd_api.g_exc_unexpected_error;
2607: elsif(wjsi_row.rebuild_item_id is not null) then
2627: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_TYPE');
2628: end maintenance_object_type;
2629:
2630: procedure maintenance_object_source is begin
2631: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2632: wjsi_row.maintenance_object_source is not null) then
2633: if(wjsi_row.rebuild_item_id is null and
2634: wjsi_row.maintenance_object_source <> 1) then
2635: raise fnd_api.g_exc_unexpected_error;
2647: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_SOURCE');
2648: end maintenance_object_source;
2649:
2650: procedure maintenance_object_id is begin
2651: if(wjsi_row.load_type = wip_constants.create_eam_job and
2652: wjsi_row.maintenance_object_id is not null) then
2653:
2654: if(wjsi_row.maintenance_object_type = 1) then
2655: if(wjsi_row.rebuild_item_id is null) then
2675: end if;
2676:
2677: end if;
2678:
2679: if(wjsi_row.load_type = wip_constants.create_eam_job and
2680: wjsi_row.maintenance_object_id is null and
2681: wjsi_row.maintenance_object_type = 3) then
2682: raise fnd_api.g_exc_unexpected_error;
2683: end if;
2687: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_ID');
2688: end maintenance_object_id;
2689:
2690: procedure pm_schedule_id is begin
2691: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2692: wjsi_row.pm_schedule_id is not null and
2693: wjsi_row.primary_item_id is null) then
2694: raise fnd_api.g_exc_unexpected_error;
2695: end if;
2698: wip_jsi_utils.record_error('WIP_ML_EAM_PM_SCHEDULE_ID');
2699: end pm_schedule_id;
2700:
2701: procedure activity_source is begin
2702: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2703: wjsi_row.activity_source is not null) then
2704: select 1
2705: into g_dummy
2706: from mfg_lookups
2719: x_date_released date;
2720: x_organization_id number;
2721: x_period_exists varchar2(1);
2722: BEGIN
2723: If (wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2724: if (wjsi_row.date_released is not null) then
2725: WIP_JSI_Utils.record_ignored_column_warning ('DATE_RELEASED') ;
2726: end if ;
2727: Elsif (wjsi_row.status_type = WIP_CONSTANTS.RELEASED) then
2723: If (wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2724: if (wjsi_row.date_released is not null) then
2725: WIP_JSI_Utils.record_ignored_column_warning ('DATE_RELEASED') ;
2726: end if ;
2727: Elsif (wjsi_row.status_type = WIP_CONSTANTS.RELEASED) then
2728: if (nvl(wjsi_row.date_released,sysdate) > sysdate) then
2729: WIP_JSI_Utils.record_error('WIP_INVALID_RELEASE_DATE',TRUE) ;
2730:
2731: update wip_job_schedule_interface