[Home] [Help]
121:
122: select *
123: into wjsi_row
124: from wip_job_schedule_interface
125: where rowid = wip_jsi_utils.current_rowid;
126:
127: -- Save Original org context
128: orig_org_context := nvl(fnd_profile.value('ORG_ID'), -1) ;
129:
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;
316: REQUEST_ID = decode(x_request_id,-1,REQUEST_ID,x_request_id),
317: PROGRAM_ID = decode(x_program_id,-1,PROGRAM_ID,x_program_id),
318: PROGRAM_UPDATE_DATE = SYSDATE,
319: PROGRAM_APPLICATION_ID = decode(x_application_id,-1,PROGRAM_APPLICATION_ID, x_application_id)
320: where rowid = wip_jsi_utils.current_rowid;
321:
322: -- Set Original org context since org context might have
323: -- different for pjm validation
324: if (orig_org_context <> -1) then
326: end if ;
327:
328: /* Fixed for Bug#3060266
329: Abort if any errors were detected. */
330: if (WIP_JSI_Utils.any_nonwarning_errors) then
331: WIP_JSI_Utils.abort_request ;
332: end if ;
333:
334: end validate;
327:
328: /* Fixed for Bug#3060266
329: Abort if any errors were detected. */
330: if (WIP_JSI_Utils.any_nonwarning_errors) then
331: WIP_JSI_Utils.abort_request ;
332: end if ;
333:
334: end validate;
335:
356: begin
357: select wip_entity_id, serialization_start_op, load_type, primary_item_id
358: into l_wipID, l_serialOp, l_loadType, l_primaryItemID
359: from wip_job_schedule_interface wjsi
360: where wjsi.rowid = wip_jsi_utils.current_rowid;
361:
362:
363: if(l_serialOp is null) then
364: --in this case, we may need to clear the serialization op if the routing was re-exploded
426: and status_type = wip_constants.unreleased;
427: end if;
428: exception
429: when others then
430: wip_jsi_utils.record_error('WIP_ML_SERIAL_START_OP');
431: wip_jsi_utils.abort_request;
432: end validate_serialization_op;
433:
434:
427: end if;
428: exception
429: when others then
430: wip_jsi_utils.record_error('WIP_ML_SERIAL_START_OP');
431: wip_jsi_utils.abort_request;
432: end validate_serialization_op;
433:
434:
435: /* private procedures */
539: raise fnd_api.g_exc_unexpected_error;
540: end if;
541: exception
542: when others then
543: wip_jsi_utils.record_error('WIP_ML_LOAD_TYPE');
544: wip_jsi_utils.abort_request;
545: end load_type;
546:
547: procedure organization_id is
540: end if;
541: exception
542: when others then
543: wip_jsi_utils.record_error('WIP_ML_LOAD_TYPE');
544: wip_jsi_utils.abort_request;
545: end load_type;
546:
547: procedure organization_id is
548: l_disable_date date;
570: raise fnd_api.g_exc_unexpected_error;
571: end if;
572: exception
573: when others then
574: wip_jsi_utils.record_error('WIP_ML_ORGANIZATION_ID');
575: wip_jsi_utils.abort_request;
576: end organization_id;
577:
578: procedure job_name is
571: end if;
572: exception
573: when others then
574: wip_jsi_utils.record_error('WIP_ML_ORGANIZATION_ID');
575: wip_jsi_utils.abort_request;
576: end organization_id;
577:
578: procedure job_name is
579: l_count NUMBER;
590: end if;
591: end if;
592: exception
593: when others then
594: wip_jsi_utils.record_error('WIP_ML_JOB_NAME');
595: wip_jsi_utils.abort_request;
596: end job_name;
597:
598: procedure entity_type is
591: end if;
592: exception
593: when others then
594: wip_jsi_utils.record_error('WIP_ML_JOB_NAME');
595: wip_jsi_utils.abort_request;
596: end job_name;
597:
598: procedure entity_type is
599: begin
604: end if;
605:
606: exception
607: when others then
608: wip_jsi_utils.record_error('WIP_ML_ENTITY_TYPE');
609: wip_jsi_utils.abort_request;
610: end entity_type;
611:
612: procedure job_id is
605:
606: exception
607: when others then
608: wip_jsi_utils.record_error('WIP_ML_ENTITY_TYPE');
609: wip_jsi_utils.abort_request;
610: end entity_type;
611:
612: procedure job_id is
613: l_count NUMBER;
622: end if;
623: end if;
624: exception
625: when others then
626: WIP_JSI_Utils.record_error('WIP_ML_WIP_ENTITY_ID') ;
627: wip_jsi_utils.abort_request;
628: end job_id;
629:
630: procedure kanban_card_id is
623: end if;
624: exception
625: when others then
626: WIP_JSI_Utils.record_error('WIP_ML_WIP_ENTITY_ID') ;
627: wip_jsi_utils.abort_request;
628: end job_id;
629:
630: procedure kanban_card_id is
631: l_doc_type NUMBER;
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';
669: raise fnd_api.g_exc_unexpected_error;
670: end if ;
671: exception
672: when others then
673: WIP_JSI_Utils.record_error(l_msg) ;
674: WIP_JSI_Utils.abort_request ;
675: end kanban_card_id ;
676:
677: procedure created_by is begin
670: end if ;
671: exception
672: when others then
673: WIP_JSI_Utils.record_error(l_msg) ;
674: WIP_JSI_Utils.abort_request ;
675: end kanban_card_id ;
676:
677: procedure created_by is begin
678: select 1
681: where user_id = wjsi_row.created_by
682: and sysdate between start_date and nvl(end_date, sysdate);
683: exception
684: when others then
685: wip_jsi_utils.record_error('WIP_ML_CREATED_BY');
686: WIP_JSI_Utils.abort_request ;
687: end created_by;
688:
689: procedure last_updated_by is begin
682: and sysdate between start_date and nvl(end_date, sysdate);
683: exception
684: when others then
685: wip_jsi_utils.record_error('WIP_ML_CREATED_BY');
686: WIP_JSI_Utils.abort_request ;
687: end created_by;
688:
689: procedure last_updated_by is begin
690: select 1
693: where user_id = wjsi_row.last_updated_by
694: and sysdate between start_date and nvl(end_date, sysdate);
695: exception
696: when others then
697: wip_jsi_utils.record_error('WIP_ML_LAST_UPDATED_BY');
698: WIP_JSI_Utils.abort_request ;
699: end last_updated_by;
700:
701: procedure start_quantity is
694: and sysdate between start_date and nvl(end_date, sysdate);
695: exception
696: when others then
697: wip_jsi_utils.record_error('WIP_ML_LAST_UPDATED_BY');
698: WIP_JSI_Utils.abort_request ;
699: end last_updated_by;
700:
701: procedure start_quantity is
702: l_reserved_qty NUMBER;
730: end if;
731: end if;
732: exception
733: when others then
734: wip_jsi_utils.record_error(l_msg);
735: wip_jsi_utils.abort_request;
736: end start_quantity;
737:
738: procedure net_quantity is begin
731: end if;
732: exception
733: when others then
734: wip_jsi_utils.record_error(l_msg);
735: wip_jsi_utils.abort_request;
736: end start_quantity;
737:
738: procedure net_quantity is begin
739:
750: end if;
751: end if;
752: exception
753: when others then
754: wip_jsi_utils.record_error('WIP_ML_NET_QUANTITY');
755: wip_jsi_utils.abort_request;
756:
757: end net_quantity;
758:
751: end if;
752: exception
753: when others then
754: wip_jsi_utils.record_error('WIP_ML_NET_QUANTITY');
755: wip_jsi_utils.abort_request;
756:
757: end net_quantity;
758:
759: procedure firm_planned_flag is begin
765: raise fnd_api.g_exc_unexpected_error;
766: end if;
767: exception
768: when others then
769: wip_jsi_utils.record_error('WIP_ML_FIRM_PLANNED_FLAG');
770: end firm_planned_flag;
771:
772: procedure repetitive_schedule_id is
773: l_rep_sched_count NUMBER;
787: end if;
788: end if;
789: exception
790: when others then
791: wip_jsi_utils.record_error('WIP_ML_REPETITIVE_SCHEDULE_ID');
792: wip_jsi_utils.abort_request;
793: end repetitive_schedule_id;
794:
795: procedure demand_class is begin
788: end if;
789: exception
790: when others then
791: wip_jsi_utils.record_error('WIP_ML_REPETITIVE_SCHEDULE_ID');
792: wip_jsi_utils.abort_request;
793: end repetitive_schedule_id;
794:
795: procedure demand_class is begin
796: if(wjsi_row.demand_class is not null and wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_sched,
801: where demand_class_code = wjsi_row.demand_class;
802: end if;
803: exception
804: when others then
805: wip_jsi_utils.record_error('WIP_ML_DEMAND_CLASS');
806: end demand_class;
807:
808: procedure line_id is begin
809: if(wjsi_row.load_type = wip_constants.create_sched and wjsi_row.line_id is null) then
816: and organization_id = wjsi_row.organization_id;
817: end if;
818: exception
819: when others then
820: wip_jsi_utils.record_error('WIP_ML_LINE_ID');
821: wip_jsi_utils.abort_request;
822: end line_id;
823:
824: procedure schedule_group_id is begin
817: end if;
818: exception
819: when others then
820: wip_jsi_utils.record_error('WIP_ML_LINE_ID');
821: wip_jsi_utils.abort_request;
822: end line_id;
823:
824: procedure schedule_group_id is begin
825: if(wjsi_row.schedule_group_id is not null and
830: where schedule_group_id = wjsi_row.schedule_group_id
831: and organization_id = wjsi_row.organization_id;
832: end if; exception
833: when others then
834: wip_jsi_utils.record_error('WIP_ML_SCHEDULE_GROUP');
835: end schedule_group_id;
836:
837: procedure build_sequence is
838: l_retval boolean;
849: end if;
850: end if;
851: exception
852: when others then
853: wip_jsi_utils.record_error('WIP_ML_BUILD_SEQUENCE');
854: end build_sequence;
855:
856: procedure status_type is
857: l_msg varchar2(30) := 'WIP_ML_STATUS_TYPE';
937:
938: if ( l_qty_reserved > 0 and
939: wjsi_row.status_type = wip_constants.comp_chrg) then
940: fnd_message.set_name('WIP','WIP_SO_EXISTS');
941: wip_jsi_utils.record_current_error(p_warning_only => true);
942: end if;
943:
944: /* End fix of 3927677 */
945:
970: -- If we are unable to cancel all PO/requisition associated
971: -- to this job, we will try to cancel as much as we can,
972: -- then user need to manually cancel the rest.
973: po_warning_flag := WIP_CONSTANTS.YES;
974: wip_jsi_utils.record_current_error(p_warning_only => true);
975: END IF; -- check return status
976: ELSE
977: -- propagate_job_change_to_po is manual or job status is
978: -- 'Complete'
977: -- propagate_job_change_to_po is manual or job status is
978: -- 'Complete'
979: po_warning_flag := WIP_CONSTANTS.YES;
980: fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
981: wip_jsi_utils.record_current_error(p_warning_only => true);
982: END IF;
983: ELSE
984: -- customer does not have PO patchset J onward, so behave the
985: -- old way
984: -- customer does not have PO patchset J onward, so behave the
985: -- old way
986: po_warning_flag := WIP_CONSTANTS.YES;
987: fnd_message.set_name('WIP', 'WIP_CANCEL_JOB/SCHED_OPEN_PO');
988: wip_jsi_utils.record_current_error(p_warning_only => true);
989: END IF;
990: END IF; -- PO/requisition exists
991: END IF; -- status is either 'Cancel','Complete', or 'Complete-no-charge'
992:
995: for which source code is populated as MSC */
996: if ( wjsi_row.source_code = 'MSC' and wdj_row.status_type in
997: (wip_constants.comp_chrg, wip_constants.comp_nochrg,
998: wip_constants.cancelled, wip_constants.closed) ) then
999: wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
1000: wip_jsi_utils.abort_request;
1001: end if;
1002:
1003: -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
996: if ( wjsi_row.source_code = 'MSC' and wdj_row.status_type in
997: (wip_constants.comp_chrg, wip_constants.comp_nochrg,
998: wip_constants.cancelled, wip_constants.closed) ) then
999: wip_jsi_utils.record_error('WIP_CANT_UPDATE_JOB');
1000: wip_jsi_utils.abort_request;
1001: end if;
1002:
1003: -- bug# 3436646: job cannot be changed to unreleased if it's been pick released
1004: if ( wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED and
1012: end if; -- JOB RESCHED : END
1013:
1014: exception
1015: when others then
1016: wip_jsi_utils.record_error(l_msg);
1017: wip_jsi_utils.abort_request;
1018: end status_type;
1019:
1020: procedure processing_work_days is begin
1013:
1014: exception
1015: when others then
1016: wip_jsi_utils.record_error(l_msg);
1017: wip_jsi_utils.abort_request;
1018: end status_type;
1019:
1020: procedure processing_work_days is begin
1021: if(wjsi_row.load_type = wip_constants.create_sched and
1023: raise fnd_api.g_exc_unexpected_error;
1024: end if;
1025: exception
1026: when others then
1027: wip_jsi_utils.record_error('WIP_ML_PROCESSING_WORK_DAYS');
1028: end processing_work_days;
1029:
1030: procedure daily_production_rate is
1031: l_max_line_rate NUMBER;
1042:
1043: if(l_max_line_rate < wjsi_row.daily_production_rate) then
1044: fnd_message.set_name('WIP', 'WIP_PROD_RATE_WARNING');
1045: fnd_message.set_token('ENTITY1', l_max_line_rate);
1046: wip_jsi_utils.record_current_error(p_warning_only => true);
1047: end if;
1048: end if;
1049: exception
1050: when others then
1047: end if;
1048: end if;
1049: exception
1050: when others then
1051: wip_jsi_utils.record_error('WIP_ML_DAILY_PRODUCTION_RATE');
1052: end daily_production_rate;
1053:
1054: procedure primary_item_id is
1055: l_see_eng_items_flag VARCHAR2(1);
1107: l_dummy := 0;
1108: end;
1109:
1110: if (l_dummy = 1) then
1111: wip_jsi_utils.record_error('WIP_ML_ATO_ITEM_NO_BOM');
1112: wip_jsi_utils.abort_request;
1113: end if;
1114: end if;
1115: elsif(wjsi_row.load_type = wip_constants.create_eam_job and
1108: end;
1109:
1110: if (l_dummy = 1) then
1111: wip_jsi_utils.record_error('WIP_ML_ATO_ITEM_NO_BOM');
1112: wip_jsi_utils.abort_request;
1113: end if;
1114: end if;
1115: elsif(wjsi_row.load_type = wip_constants.create_eam_job and
1116: wjsi_row.primary_item_id is not null) then
1170:
1171: end if;
1172: exception
1173: when others then
1174: wip_jsi_utils.record_error(l_msg);
1175: wip_jsi_utils.abort_request;
1176: end primary_item_id;
1177:
1178: procedure wip_supply_type is
1171: end if;
1172: exception
1173: when others then
1174: wip_jsi_utils.record_error(l_msg);
1175: wip_jsi_utils.abort_request;
1176: end primary_item_id;
1177:
1178: procedure wip_supply_type is
1179: l_routing_count NUMBER;
1204: end if;
1205: end if;
1206: exception
1207: when others then
1208: wip_jsi_utils.record_error('WIP_ML_WIP_SUPPLY_TYPE');
1209: end wip_supply_type;
1210:
1211: procedure routing_reference_id is begin
1212: if(wjsi_row.load_type = wip_constants.create_ns_job and
1217: raise fnd_api.g_exc_unexpected_error;
1218: end if;
1219: exception
1220: when others then
1221: wip_jsi_utils.record_error('WIP_ML_ROUTING_REFERENCE_ID');
1222: wip_jsi_utils.abort_request;
1223: end routing_reference_id;
1224:
1225: procedure bom_reference_id is begin
1218: end if;
1219: exception
1220: when others then
1221: wip_jsi_utils.record_error('WIP_ML_ROUTING_REFERENCE_ID');
1222: wip_jsi_utils.abort_request;
1223: end routing_reference_id;
1224:
1225: procedure bom_reference_id is begin
1226: if(wjsi_row.load_type = wip_constants.create_ns_job and
1235: and eng_item_flag = 'N';
1236: end if;
1237: exception
1238: when others then
1239: wip_jsi_utils.record_error('WIP_ML_BOM_REFERENCE_ID');
1240: wip_jsi_utils.abort_request;
1241: end bom_reference_id;
1242:
1243: procedure alternate_routing_designator is
1236: end if;
1237: exception
1238: when others then
1239: wip_jsi_utils.record_error('WIP_ML_BOM_REFERENCE_ID');
1240: wip_jsi_utils.abort_request;
1241: end bom_reference_id;
1242:
1243: procedure alternate_routing_designator is
1244: l_is_flow_rtg NUMBER;
1275: and routing_type = 1;
1276: end if;
1277: exception
1278: when others then
1279: wip_jsi_utils.record_error(l_msg);
1280: end alternate_routing_designator;
1281:
1282: procedure alternate_bom_designator is
1283: l_bom_item_id NUMBER;
1304: and assembly_type = 1;
1305: end if;
1306: exception
1307: when others then
1308: wip_jsi_utils.record_error('WIP_ML_ALTERNATE_BOM');
1309: end alternate_bom_designator;
1310:
1311: procedure project_id is begin
1312: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_eam_job) and
1325: and nvl(mp.project_reference_enabled, 2) = wip_constants.yes;
1326: end if;
1327: exception
1328: when others then
1329: wip_jsi_utils.record_error('WIP_ML_PROJECT_ID');
1330: end project_id;
1331:
1332: procedure task_id is
1333: l_project_id NUMBER;
1346:
1347: end if;
1348: exception
1349: when others then
1350: wip_jsi_utils.record_error('WIP_ML_TASK_ID');
1351: end task_id;
1352:
1353: procedure project_task_id is
1354: l_result VARCHAR2(1);
1374: when others then
1375: wip_utilities.get_message_stack(p_delete_stack => 'T',
1376: p_msg => l_message);
1377: if (l_result = PJM_PROJECT.G_VALIDATE_FAILURE) then
1378: wip_jsi_utils.record_error_text(l_message, false);
1379: wip_jsi_utils.abort_request;
1380: else
1381: wip_jsi_utils.record_error_text(l_message, true);
1382: end if;
1375: wip_utilities.get_message_stack(p_delete_stack => 'T',
1376: p_msg => l_message);
1377: if (l_result = PJM_PROJECT.G_VALIDATE_FAILURE) then
1378: wip_jsi_utils.record_error_text(l_message, false);
1379: wip_jsi_utils.abort_request;
1380: else
1381: wip_jsi_utils.record_error_text(l_message, true);
1382: end if;
1383: end project_task_id;
1377: if (l_result = PJM_PROJECT.G_VALIDATE_FAILURE) then
1378: wip_jsi_utils.record_error_text(l_message, false);
1379: wip_jsi_utils.abort_request;
1380: else
1381: wip_jsi_utils.record_error_text(l_message, true);
1382: end if;
1383: end project_task_id;
1384:
1385: procedure schedule_dates is
1451: --include repetitive dates in date provided count
1452: if(wjsi_row.last_unit_start_date is not null) then
1453: l_date_count := l_date_count + 1;
1454: if(wjsi_row.load_type <> wip_constants.create_sched) then
1455: wip_jsi_utils.record_ignored_column_warning('LAST_UNIT_START_DATE');
1456: end if;
1457: end if;
1458:
1459: if(wjsi_row.first_unit_completion_date is not null) then
1458:
1459: if(wjsi_row.first_unit_completion_date is not null) then
1460: l_date_count := l_date_count + 1;
1461: if(wjsi_row.load_type <> wip_constants.create_sched) then
1462: wip_jsi_utils.record_ignored_column_warning('FIRST_UNIT_COMPLETION_DATE');
1463: end if;
1464: end if;
1465:
1466: if(wjsi_row.load_type = wip_constants.create_sched) then
1642: end if;
1643: end if;
1644: exception
1645: when others then
1646: wip_jsi_utils.record_error(l_msg);
1647: wip_jsi_utils.abort_request;
1648: end schedule_dates;
1649:
1650: procedure scheduling_method is
1643: end if;
1644: exception
1645: when others then
1646: wip_jsi_utils.record_error(l_msg);
1647: wip_jsi_utils.abort_request;
1648: end schedule_dates;
1649:
1650: procedure scheduling_method is
1651: l_msg_code VARCHAR2(30) := 'WIP_ML_SCHEDULING_METHOD';
1678: end if;
1679: end if;
1680: exception
1681: when others then
1682: wip_jsi_utils.record_error(l_msg_code);
1683: wip_jsi_utils.abort_request;
1684: end scheduling_method;
1685:
1686: procedure completion_subinventory is
1679: end if;
1680: exception
1681: when others then
1682: wip_jsi_utils.record_error(l_msg_code);
1683: wip_jsi_utils.abort_request;
1684: end scheduling_method;
1685:
1686: procedure completion_subinventory is
1687: l_inv_item_id NUMBER;
1751: end if;
1752: */
1753: exception
1754: when others then
1755: wip_jsi_utils.record_error('WIP_ML_COMPLETION_SUBINVENTORY');
1756: wip_jsi_utils.abort_request;
1757: end completion_subinventory;
1758:
1759: procedure completion_locator_id is
1752: */
1753: exception
1754: when others then
1755: wip_jsi_utils.record_error('WIP_ML_COMPLETION_SUBINVENTORY');
1756: wip_jsi_utils.abort_request;
1757: end completion_subinventory;
1758:
1759: procedure completion_locator_id is
1760: l_prj_loc_id NUMBER;
1879: raise fnd_api.g_exc_unexpected_error;
1880: end if;
1881: end if;
1882: exception when others then
1883: wip_jsi_utils.record_error(l_msg);
1884: /* Fixed Bug#3060266 - should abort request if Invalid Locator */
1885: wip_jsi_utils.abort_request;
1886: end completion_locator_id;
1887:
1881: end if;
1882: exception when others then
1883: wip_jsi_utils.record_error(l_msg);
1884: /* Fixed Bug#3060266 - should abort request if Invalid Locator */
1885: wip_jsi_utils.abort_request;
1886: end completion_locator_id;
1887:
1888: procedure due_date is begin
1889: if(wjsi_row.load_type = wip_constants.create_eam_job) then
1896: end if;
1897: end if;
1898: exception
1899: when others then
1900: wip_jsi_utils.record_error('WIP_ML_DUE_DATE');
1901: wip_jsi_utils.abort_request;
1902: end due_date;
1903:
1904: procedure date_released is begin
1897: end if;
1898: exception
1899: when others then
1900: wip_jsi_utils.record_error('WIP_ML_DUE_DATE');
1901: wip_jsi_utils.abort_request;
1902: end due_date;
1903:
1904: procedure date_released is begin
1905: if(wjsi_row.date_released > sysdate and
1906: wjsi_row.status_type = wip_constants.released) then
1907: raise fnd_api.g_exc_unexpected_error;
1908: end if;
1909: exception when others then
1910: wip_jsi_utils.record_error('WIP_ML_INVALID_RELEASE_DATE');
1911: wip_jsi_utils.abort_request;
1912: end date_released;
1913:
1914: procedure requested_start_date is begin
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
1922: end if;
1923: end if;
1924: exception
1925: when others then
1926: wip_jsi_utils.record_error('WIP_ML_REQUESTED_START_DATE');
1927: wip_jsi_utils.abort_request;
1928: end requested_start_date;
1929:
1930: procedure end_item_unit_number is
1923: end if;
1924: exception
1925: when others then
1926: wip_jsi_utils.record_error('WIP_ML_REQUESTED_START_DATE');
1927: wip_jsi_utils.abort_request;
1928: end requested_start_date;
1929:
1930: procedure end_item_unit_number is
1931: is_unit_effective_item boolean;
1959: when too_many_rows then
1960: null; -- the query returning multiple rows is ok
1961: when others then
1962: fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
1963: wip_jsi_utils.record_current_error ;
1964: end;
1965: end if;
1966:
1967: -- You cannot create a repetitive schedule for a unit effective assembly.
1966:
1967: -- You cannot create a repetitive schedule for a unit effective assembly.
1968: if(wjsi_row.load_type = wip_constants.create_sched and
1969: is_unit_effective_item) then
1970: wip_jsi_utils.record_error('WIP_ML_NO_UNIT_EFF_SCHED');
1971: raise fnd_api.g_exc_unexpected_error;
1972: end if;
1973:
1974: -- If this is a discrete job load...
1975: if (wjsi_row.load_type in (WIP_CONSTANTS.CREATE_JOB, WIP_CONSTANTS.CREATE_NS_JOB))
1976: then
1977: -- It is an error to provide unit number for non-unit effective assemblies.
1978: if(not is_unit_effective_item and wjsi_row.end_item_unit_number is not null) then
1979: wip_jsi_utils.record_error('WIP_ML_UNIT_NUM_MEANINGLESS');
1980: end if;
1981:
1982: -- Unit number is required for unit effective assemblies.
1983: if(is_unit_effective_item and wjsi_row.end_item_unit_number is null) then
1981:
1982: -- Unit number is required for unit effective assemblies.
1983: if(is_unit_effective_item and wjsi_row.end_item_unit_number is null) then
1984: fnd_message.set_name('PJM', 'UEFF-UNIT NUMBER REQUIRED');
1985: wip_jsi_utils.record_current_error;
1986: raise fnd_api.g_exc_unexpected_error;
1987: end if;
1988: end if;
1989:
1994: -- added for bug#2766839, added check when the assembly is not is_unit_effective_item
1995: if(not is_unit_effective_item) then
1996: -- It is an error to provide unit number for non-unit effective assemblies.
1997: if ( wjsi_row.end_item_unit_number is not null) then
1998: wip_jsi_utils.record_error('WIP_ML_UNIT_NUM_MEANINGLESS');
1999: end if;
2000: wjsi_row.end_item_unit_number := null;
2001:
2002: -- if status is unreleased, the end_item_unit_number can be modified
2007: from wip_discrete_jobs
2008: where wip_entity_id =
2009: (select wip_entity_id
2010: from wip_job_schedule_interface
2011: where rowid = WIP_JSI_UTILS.CURRENT_ROWID
2012: );
2013: exception
2014: when others then
2015: FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2012: );
2013: exception
2014: when others then
2015: FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2016: WIP_JSI_Utils.record_current_error ;
2017: raise fnd_api.g_exc_unexpected_error;
2018: end ;
2019: else
2020: begin
2024: where wip_entity_id = wjsi_row.wip_entity_id;
2025: exception
2026: when others then
2027: FND_Message.set_name('WIP', 'WIP_ML_PRIMARY_ITEM_ID') ;
2028: WIP_JSI_Utils.record_current_error ;
2029: raise fnd_api.g_exc_unexpected_error;
2030: end ;
2031: end if;
2032:
2032:
2033: -- if status is not unreleased, end_item_unit_number is ignored.
2034: else
2035: if (wjsi_row.end_item_unit_number is not null) then
2036: wip_jsi_utils.record_ignored_column_warning('END_ITEM_UNIT_NUMBER');
2037: end if;
2038:
2039: begin
2040: select end_item_unit_number into wjsi_row.end_item_unit_number
2041: from wip_discrete_jobs
2042: where wip_entity_id =
2043: (select wip_entity_id
2044: from wip_job_schedule_interface
2045: where rowid = WIP_JSI_UTILS.CURRENT_ROWID
2046: );
2047: exception
2048: when others then
2049: FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2046: );
2047: exception
2048: when others then
2049: FND_Message.set_name('PJM', 'UEFF-UNIT NUMBER INVALID') ;
2050: WIP_JSI_Utils.record_current_error ;
2051: raise fnd_api.g_exc_unexpected_error;
2052: end ;
2053: end if;
2054: end if ;
2054: end if ;
2055:
2056: exception
2057: when others then
2058: wip_jsi_utils.abort_request;
2059:
2060: end end_item_unit_number;
2061:
2062: procedure overcompletion is
2081: end if;
2082: end if;
2083: exception
2084: when others then
2085: wip_jsi_utils.record_error(l_msg);
2086: end overcompletion;
2087:
2088: procedure class_code is begin
2089: if(wjsi_row.load_type in (wip_constants.create_job, wip_constants.create_ns_job, wip_constants.create_eam_job) and
2157: end if;
2158: end if;
2159: exception
2160: when others then
2161: wip_jsi_utils.record_error('WIP_ML_CLASS_CODE');
2162: end class_code;
2163:
2164: procedure estimate_lead_time is
2165: l_entity_type NUMBER;
2276: end if;
2277: end if;
2278: exception
2279: when others then
2280: wip_jsi_utils.record_error(l_msg);
2281: end estimate_lead_time;
2282:
2283: procedure bom_revision is
2284: l_start_date DATE;
2299: p_start_date => l_start_date);
2300: end if;
2301: exception
2302: when others then
2303: WIP_JSI_Utils.record_error('WIP_ML_BOM_REVISION') ;
2304: /* Fixed for bug#3063147
2305: When No valid bom revision exists for an item then mass load should be
2306: aborted.
2307: */
2304: /* Fixed for bug#3063147
2305: When No valid bom revision exists for an item then mass load should be
2306: aborted.
2307: */
2308: wip_jsi_utils.abort_request;
2309:
2310: end bom_revision;
2311:
2312: procedure routing_revision is
2338: end if;
2339: end if;
2340: exception
2341: when others then
2342: WIP_JSI_Utils.record_error('WIP_ML_ROUTING_REVISION');
2343: end routing_revision;
2344:
2345: procedure asset_group_id is begin
2346: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2356: end if;
2357: end if;
2358: exception
2359: when others then
2360: wip_jsi_utils.record_error('WIP_ML_EAM_ASSET_GROUP');
2361: wip_jsi_utils.abort_request;
2362: end asset_group_id;
2363:
2364: procedure asset_number is
2357: end if;
2358: exception
2359: when others then
2360: wip_jsi_utils.record_error('WIP_ML_EAM_ASSET_GROUP');
2361: wip_jsi_utils.abort_request;
2362: end asset_group_id;
2363:
2364: procedure asset_number is
2365: l_msg VARCHAR2(30);
2385: end if;
2386: end if;
2387: exception
2388: when others then
2389: wip_jsi_utils.record_error(l_msg);
2390: end asset_number;
2391:
2392: procedure rebuild_item_id is begin
2393: if(wjsi_row.load_type = wip_constants.create_eam_job and
2400: and eam_item_type = 3;
2401: end if;
2402: exception
2403: when others then
2404: wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_ITEM');
2405: wip_jsi_utils.abort_request;
2406: end rebuild_item_id;
2407:
2408: procedure rebuild_serial_number is begin
2401: end if;
2402: exception
2403: when others then
2404: wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_ITEM');
2405: wip_jsi_utils.abort_request;
2406: end rebuild_item_id;
2407:
2408: procedure rebuild_serial_number is begin
2409: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2444: end if;
2445: end if;
2446: exception
2447: when others then
2448: wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_SERIAL');
2449: end rebuild_serial_number;
2450:
2451: procedure parent_wip_entity_id is begin
2452: -- Per EAM Enh. for H, this field is no longer mandatory
2459: and entity_type = wip_constants.eam;
2460: end if;
2461: exception
2462: when others then
2463: wip_jsi_utils.record_error('WIP_ML_EAM_PARENT_ENTITY');
2464: end parent_wip_entity_id;
2465:
2466: procedure manual_rebuild_flag is begin
2467: if(wjsi_row.load_type = wip_constants.create_eam_job) then
2477: end if;
2478: end if;
2479: exception
2480: when others then
2481: wip_jsi_utils.record_error('WIP_ML_EAM_REBUILD_FLAG');
2482: end manual_rebuild_flag;
2483:
2484: procedure owning_department is
2485: l_job_date DATE;
2500: end if;
2501: end if;
2502: exception
2503: when others then
2504: wip_jsi_utils.record_error('WIP_ML_EAM_OWNING_DEPT');
2505: end owning_department;
2506:
2507: procedure notification_required is begin
2508: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2511: raise fnd_api.g_exc_unexpected_error;
2512: end if;
2513: exception
2514: when others then
2515: wip_jsi_utils.record_error('WIP_ML_EAM_NOTIF_REQ');
2516: end notification_required;
2517:
2518: procedure shutdown_type is begin
2519: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2526: and enabled_flag = 'Y';
2527: end if;
2528: exception
2529: when others then
2530: wip_jsi_utils.record_error('WIP_ML_EAM_SHUTDOWN_TYPE');
2531: end shutdown_type;
2532:
2533: procedure tagout_required is begin
2534: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2537: raise fnd_api.g_exc_unexpected_error;
2538: end if;
2539: exception
2540: when others then
2541: wip_jsi_utils.record_error('WIP_ML_EAM_TAGOUT_REQUIRED');
2542: end tagout_required;
2543:
2544: procedure plan_maintenance is begin
2545: if(wjsi_row.load_type = wip_constants.create_eam_job and
2548: raise fnd_api.g_exc_unexpected_error;
2549: end if;
2550: exception
2551: when others then
2552: wip_jsi_utils.record_error('WIP_ML_EAM_PLAN_MAINTENANCE');
2553: end plan_maintenance;
2554:
2555: procedure work_order_type is begin
2556: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2563: and enabled_flag = 'Y';
2564: end if;
2565: exception
2566: when others then
2567: wip_jsi_utils.record_error('WIP_ML_EAM_WORK_ORDER_TYPE');
2568: end work_order_type;
2569:
2570: procedure activity_type is begin
2571: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2578: and enabled_flag = 'Y';
2579: end if;
2580: exception
2581: when others then
2582: wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_TYPE');
2583: end activity_type;
2584:
2585: procedure activity_cause is begin
2586: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job,
2593: and enabled_flag = 'Y';
2594: end if;
2595: exception
2596: when others then
2597: wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_CAUSE');
2598: end activity_cause;
2599:
2600: procedure maintenance_object_type is
2601: l_serial_number_control_code NUMBER;
2623: end if;
2624: end if;
2625: exception
2626: when others then
2627: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_TYPE');
2628: end maintenance_object_type;
2629:
2630: procedure maintenance_object_source is begin
2631: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2643: end if;
2644: end if;
2645: exception
2646: when others then
2647: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_SOURCE');
2648: end maintenance_object_source;
2649:
2650: procedure maintenance_object_id is begin
2651: if(wjsi_row.load_type = wip_constants.create_eam_job and
2683: end if;
2684:
2685: exception
2686: when others then
2687: wip_jsi_utils.record_error('WIP_ML_EAM_MAINT_OBJECT_ID');
2688: end maintenance_object_id;
2689:
2690: procedure pm_schedule_id is begin
2691: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2694: raise fnd_api.g_exc_unexpected_error;
2695: end if;
2696: exception
2697: when others then
2698: wip_jsi_utils.record_error('WIP_ML_EAM_PM_SCHEDULE_ID');
2699: end pm_schedule_id;
2700:
2701: procedure activity_source is begin
2702: if(wjsi_row.load_type in (wip_constants.create_eam_job, wip_constants.resched_eam_job) and
2709: and enabled_flag = 'Y';
2710: end if;
2711: exception
2712: when others then
2713: wip_jsi_utils.record_error('WIP_ML_EAM_ACTIVITY_SOURCE');
2714: end activity_source;
2715:
2716:
2717: PROCEDURE validate_date_released is
2721: x_period_exists varchar2(1);
2722: BEGIN
2723: If (wjsi_row.status_type = WIP_CONSTANTS.UNRELEASED) then
2724: if (wjsi_row.date_released is not null) then
2725: WIP_JSI_Utils.record_ignored_column_warning ('DATE_RELEASED') ;
2726: end if ;
2727: Elsif (wjsi_row.status_type = WIP_CONSTANTS.RELEASED) then
2728: if (nvl(wjsi_row.date_released,sysdate) > sysdate) then
2729: WIP_JSI_Utils.record_error('WIP_INVALID_RELEASE_DATE',TRUE) ;
2725: WIP_JSI_Utils.record_ignored_column_warning ('DATE_RELEASED') ;
2726: end if ;
2727: Elsif (wjsi_row.status_type = WIP_CONSTANTS.RELEASED) then
2728: if (nvl(wjsi_row.date_released,sysdate) > sysdate) then
2729: WIP_JSI_Utils.record_error('WIP_INVALID_RELEASE_DATE',TRUE) ;
2730:
2731: update wip_job_schedule_interface
2732: set date_released = sysdate
2733: where rowid = WIP_JSI_Utils.current_rowid ;
2729: WIP_JSI_Utils.record_error('WIP_INVALID_RELEASE_DATE',TRUE) ;
2730:
2731: update wip_job_schedule_interface
2732: set date_released = sysdate
2733: where rowid = WIP_JSI_Utils.current_rowid ;
2734: end if;
2735:
2736: --else
2737: /* fix for bug 2424987 */
2743: and trunc(INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG(nvl(wjsi_row.date_released,sysdate),wjsi_row.organization_id)) between PERIOD_START_DATE and SCHEDULE_CLOSE_DATE
2744: and period_close_date is NULL;
2745: Exception
2746: When others then
2747: WIP_JSI_Utils.record_error('WIP_NO_ACCT_PERIOD',FALSE) ;
2748: WIP_JSI_Utils.abort_request;
2749: End;
2750: --end if;
2751:
2744: and period_close_date is NULL;
2745: Exception
2746: When others then
2747: WIP_JSI_Utils.record_error('WIP_NO_ACCT_PERIOD',FALSE) ;
2748: WIP_JSI_Utils.abort_request;
2749: End;
2750: --end if;
2751:
2752: End if;