-- DROP TABLE pcms.iwr; -- DROP TABLE pcms.iwr_tml; -- DROP TABLE pcms.iwr_status; -- DROP TABLE pcms.iwr_type; CREATE TABLE pcms.iwr ( Id UNIQUEIDENTIFIER DEFAULT NEWID() NOT NULL , iwr_seqno bigint , event_seqno bigint , open_by_seqno bigint NOT NULL -- security_user_seqno , approve_by_seqno bigint , close_by_seqno bigint , iwr_priority_seqno bigint NOT NULL , iwr_route_to_seqno bigint NOT NULL , iwr_constraint_seqno bigint NOT NULL , iwr_status_seqno bigint NOT NULL , open_dt DATETIME NOT NULL , approve_dt DATETIME , close_dt DATETIME , required_compl_dt DATETIME , sys_work_order_no varchar(16) , mnt_work_order_no varchar(16) , headline varchar(80) NOT NULL , released_for_approval_yn varchar(1) NOT NULL , [STATUS] varchar(8) NOT NULL , add_dt DATETIME , chg_dt DATETIME , xfer_dt DATETIME , xfer_status varchar(20) , send_ext_sys_yn varchar(1) NOT NULL , iwr_notify_type_seqno bigint NOT NULL , mnt_work_order_dt DATETIME , notification_no varchar(16) , notification_dt DATETIME , asset_location_ind varchar(1) , mgmt_chg_no varchar(50) , iwr_criticality_seqno bigint NOT NULL , work_type_seqno bigint NOT NULL , event_chklist_catg_seqno bigint , perpetual_yn varchar(1) NOT NULL , equip_seqno bigint NOT NULL , circuit_seqno bigint , component_seqno bigint , master_scheduler_seqno bigint , iwr_type_seqno bigint NOT NULL , iwr_job_plan_seqno bigint NOT NULL , [scope] varchar(max) , wo_status_codes varchar(200) , not_status_codes varchar(200) , sap_updates_pending_yn varchar(1) NOT NULL , delete_trigger_action varchar(6) , insert_trigger_action varchar(6) , update_trigger_action varchar(6) , mnt_work_order_finish_dt DATETIME , latitude NUMERIC(8, 6) , longitude NUMERIC(9, 6) , notes varchar(max) , finding varchar(max) , iwr_issue_type_seqno bigint NOT NULL , iwr_category_seqno bigint NOT NULL , iwr_cause_seqno bigint NOT NULL , iwr_interim_status_seqno bigint NOT NULL , sap_long_text varchar(max) , CreatedBy varchar(150) NOT NULL , CreatedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ModifiedBy varchar(150) NOT NULL , ModifiedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ArchiveTF bit DEFAULT 0 NOT NULL , CONSTRAINT PK_iwr PRIMARY KEY (Id) ); CREATE TABLE pcms.iwr_tml ( iwr_seqno bigint --Need to populate upon sending to PCMS , tml_seqno bigint --Need to populate upon sending to PCMS , FK_iwrId UNIQUEIDENTIFIER NOT NULL INDEX IX_FK_iwrId , FK_tmlId UNIQUEIDENTIFIER NOT NULL INDEX IX_FK_tmlId , CONSTRAINT FK_iwr_iwr FOREIGN KEY (FK_iwrId) REFERENCES pcms.tml (Id) , CONSTRAINT FK_iwr_tml_tml FOREIGN KEY (FK_tmlId) REFERENCES pcms.tml (Id)); CREATE TABLE pcms.iwr_status ( iwr_status_seqno bigint NOT NULL , iwr_status_code varchar(10) NOT NULL , [name] varchar(50) , sys_iwr_group_seqno bigint NOT NULL , CreatedBy varchar(150) NOT NULL , CreatedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ModifiedBy varchar(150) NOT NULL , ModifiedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ArchiveTF bit DEFAULT 0 NOT NULL ); CREATE TABLE pcms.iwr_type ( iwr_type_seqno bigint NOT NULL , code varchar(10) NOT NULL , [name] varchar(50) , req_for_approval_yn varchar(1) NOT NULL , CreatedBy varchar(150) NOT NULL , CreatedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ModifiedBy varchar(150) NOT NULL , ModifiedDate datetime2(0) DEFAULT GETUTCDATE() NOT NULL , ArchiveTF bit DEFAULT 0 NOT NULL);