-- select * from hib.documenttemplateworkflowtemplate order by workflowtemplateid,documenttemplateid
-- select * from hib.documenttemplateworkflowtemplate where documenttemplateid in (5,7,9,13,46) order by workflowtemplateid,documenttemplateid

--; Delete all entries in the documenttemplateworkflowtemplate table
Delete from hib.[documenttemplateworkflowtemplate]

/*
//		For completed NDS requests with another workflow pending resolution, prevent replacement of the following documents:
//			•	Research Request Memo			--documenttemplateid=4
//			•	Research Protocol				--documenttemplateid=18
//			•	CDW-Domain Checklist			--documenttemplateid=19
//			•	VA Form 9957					--documenttemplateid=10
//			•	Vital Status Rules of Behavior	--documenttemplateid=16
//			•	EHR Access Request Form			--documenttemplateid=43
		
//		The following documents are common to all workflows and may be changed by Change Request:
//			•	Research Study Institutional Review Board (IRB) Approval Letter	--documenttemplateid=5
//			•	Sample Informed Consent and HIPAA Authorization					--documenttemplateid=7
//			•	Research and Development (RD) Committee Approval Letter			--documenttemplateid=9
//			•	IRB Approval of Waiver of HIPAA-Compliant Authorization			--documenttemplateid=13
//			•	Real SSN Access Request											--documenttemplateid=46

--NDS-owned documents
select * from hib.documenttemplate where [name] like '%Research Request Memo%'			--documenttemplateid=4
select * from hib.documenttemplate where [name] like '%Research Protocol%'				--documenttemplateid=18
select * from hib.documenttemplate where [name] like '%CDW-Domain Checklist%'			--documenttemplateid=19	--verify this document template name (doesn't line up with the new BRD)
select * from hib.documenttemplate where [name] like '%VA Form 9957%'					--documenttemplateid=10
select * from hib.documenttemplate where [name] like '%Vital Status Rules of Behavior%'	--documenttemplateid=16
select * from hib.documenttemplate where [name] like '%EHR Access Request Form%'		--documenttemplateid=43

--common documents
select * from hib.documenttemplate where [name] like '%Research Study Institutional Review Board (IRB) Approval Letter%'	--documenttemplateid=5
select * from hib.documenttemplate where [name] like '%Sample Informed Consent and HIPAA Authorization%'					--documenttemplateid=7
select * from hib.documenttemplate where [name] like '%Research and Development (RD) Committee Approval Letter%'			--documenttemplateid=9
select * from hib.documenttemplate where [name] like '%IRB Approval of Waiver of HIPAA-Compliant Authorization%'			--documenttemplateid=13
select * from hib.documenttemplate where [name] like '%Real SSN Access Request%'											--documenttemplateid=46
*/

--NDS-owned documents
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (4,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (18,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (19,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (10,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (16,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (43,1)

--common documents (NDS Workflow)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (5,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (7,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (9,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (13,1)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (46,1)	--Real SSN Access Request

-------------------------------------------------------------------------------------------
--SQDUG-owned documents
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (47,2)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (48,2)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (49,2)

--common documents (SQDUG Workflow)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (5,2)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (7,2)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (9,2)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (13,2)


-------------------------------------------------------------------------------------------
--OEF/OIF-owned documents
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (42,3)	--OEF-OIF Data Use Agreement, OEF/OIF Roster File

--common documents (OEF/OIF Workflow)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (5,3)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (7,3)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (9,3)
Insert into hib.[documenttemplateworkflowtemplate] (documenttemplateid, workflowtemplateid)
values (13,3)
