[Home] [Help]
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
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 ;
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;
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
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;
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:
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).
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
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
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');
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,
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
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
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
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
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
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;
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:
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
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;
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';
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
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;
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;
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;
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';
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;
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
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;
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;
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;
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
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;
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;
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
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
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(*)
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
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;
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
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
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),
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
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
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;
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
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;
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
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,
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
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
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
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
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
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;
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';
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
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 */
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
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;
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:
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:
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:
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
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,
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,
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
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
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
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;
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:
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
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';
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
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:
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;
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
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
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
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
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
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
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;
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
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
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;
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(*)
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
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!
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
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
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;
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;
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;
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';
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;
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;
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;
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
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');
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;
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,
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;
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
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;
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;
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;
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;
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:
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:
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;
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;
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
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
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
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
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;
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
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;
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;
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;
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
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
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
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
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';
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;
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
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;
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);
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
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
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;
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
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
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;
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
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;
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;
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');
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
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.
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
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';
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;
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
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
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
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)
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
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)
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;
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
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;
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:
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,
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,
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;
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
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;
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
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,
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;
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,
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;
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;
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,
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,
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;
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
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,
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
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
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
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
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;
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;
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
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');
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;
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
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;
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
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;
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;
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
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
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
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'
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
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;
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
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;
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;
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
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
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