IWRTBLEs

-- 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);