CREATE TABLE [dbo].[liu_sts](
	[site_cd] [varchar](4) NOT NULL,
	[liu_sts_cat_cd] [varchar](20) NOT NULL,
	[liu_sts_typ_cd] [varchar](25) NOT NULL,
	[liu_sts_status] [varchar](3) NOT NULL,
	[liu_sts_desc] [varchar](255) NULL,
	[liu_sts_disable_flag] [varchar](1) NULL,
	[liu_sts_email_flag] [varchar](1) NULL,
	[audit_user] [varchar](50) NULL,
	[audit_date] [datetime] NOT NULL,
	[column1] [varchar](60) NULL,
	[column2] [varchar](60) NULL,
	[column3] [varchar](60) NULL,
	[column4] [varchar](60) NULL,
	[column5] [varchar](60) NULL,
	[RowID] [numeric](12, 0) IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO


DELETE	cf_table
WHERE	table_name = 'liu_sts'
GO

INSERT	cf_table (	table_name,           
					table_desc,
					custom_report,
					audit_user,
					audit_date )
VALUES (			'liu_sts',
					'Linen Inventory Checklist In Use Status Record',
					'0',
					'admin',
					GETDATE())
GO

DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_cat_cd'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_cat_cd', 
		'Category:', 
		'Category:', 
		'Category', 
		'Category', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_typ_cd'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_typ_cd', 
		'Type:', 
		'Type:', 
		'Type', 
		'Type', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO

DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_status'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_status', 
		'Status:', 
		'Status:', 
		'Status', 
		'Status', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO



DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_desc'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_desc', 
		'Description:', 
		'Description:', 
		'Description', 
		'Description', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO


DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_disable_flag'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_disable_flag', 
		'Disable?:', 
		'Disable?:', 
		'Disable?', 
		'Disable?', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO


DELETE	cf_label
WHERE	table_name = 'liu_sts'
AND 	column_name = 'liu_sts_email_flag'
GO

INSERT 	cf_label (language_cd, table_name, column_name, 
		default_label,
		customize_label, 
		default_header, 
		customize_header, 
		cfr_flag, cf_label_required, audit_user, audit_date ) 
SELECT 	language_cd, 'liu_sts', 'liu_sts_email_flag', 
		'Email:', 
		'Email:', 
		'Email', 
		'Email', 
		'0', '0', 'admin', getdate() 
FROM	cf_language 
GO


Insert into sts_cat (site_cd, sts_cat_cat_cd, sts_cat_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'Linen Inventory Checklist In Use Status', 'admin', getdate() From cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM sts_cat s
    WHERE s.site_cd = c.site_cd
      AND s.sts_cat_cat_cd = 'CHECKLIST IN USE'
);
GO

Insert into sts_typ (site_cd, sts_typ_cat_cd, sts_typ_typ_cd, sts_typ_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'OPEN', 'Open Status', 'admin', getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM sts_typ s
    WHERE s.site_cd = c.site_cd
      AND s.sts_typ_cat_cd = 'CHECKLIST IN USE'
	  AND s.sts_typ_typ_cd = 'OPEN'
);
GO

Insert into sts_typ (site_cd, sts_typ_cat_cd, sts_typ_typ_cd, sts_typ_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'CLOSE', 'Close Status', 'admin', getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM sts_typ s
    WHERE s.site_cd = c.site_cd
      AND s.sts_typ_cat_cd = 'CHECKLIST IN USE'
	  AND s.sts_typ_typ_cd = 'CLOSE'
);
GO

Insert into sts_typ (site_cd, sts_typ_cat_cd, sts_typ_typ_cd, sts_typ_desc, audit_user, audit_date) 
Select c.site_cd, 'CHECKLIST IN USE', 'CANCEL', 'Cancel Status', 'admin', getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM sts_typ s
    WHERE s.site_cd = c.site_cd
      AND s.sts_typ_cat_cd = 'CHECKLIST IN USE'
	  AND s.sts_typ_typ_cd = 'CANCEL'
);
GO

Insert into liu_sts (site_cd, liu_sts_cat_cd, liu_sts_typ_cd, liu_sts_status, liu_sts_disable_flag, liu_sts_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'OPEN', 'OPN', '0', 'Open', 'admin',  getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM liu_sts s
    WHERE s.site_cd = c.site_cd
      AND s.liu_sts_cat_cd = 'CHECKLIST IN USE'
	  AND s.liu_sts_typ_cd = 'OPEN'
	  AND s.liu_sts_status = 'OPN'
);
GO

Insert into liu_sts (site_cd, liu_sts_cat_cd, liu_sts_typ_cd, liu_sts_status, liu_sts_disable_flag, liu_sts_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'CLOSE', 'CLO', '0', 'Close', 'admin',  getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM liu_sts s
    WHERE s.site_cd = c.site_cd
      AND s.liu_sts_cat_cd = 'CHECKLIST IN USE'
	  AND s.liu_sts_typ_cd = 'CLOSE'
	  AND s.liu_sts_status = 'CLO'
);
GO

Insert into liu_sts (site_cd, liu_sts_cat_cd, liu_sts_typ_cd, liu_sts_status, liu_sts_disable_flag, liu_sts_desc, audit_user, audit_date)
Select c.site_cd, 'CHECKLIST IN USE', 'CANCEL', 'CNX', '0', 'Cancel', 'admin',  getdate() from cf_site c
WHERE NOT EXISTS (
    SELECT 1 
    FROM liu_sts s
    WHERE s.site_cd = c.site_cd
      AND s.liu_sts_cat_cd = 'CHECKLIST IN USE'
	  AND s.liu_sts_typ_cd = 'CANCEL'
	  AND s.liu_sts_status = 'CNX'
);
GO