-
Notifications
You must be signed in to change notification settings - Fork 3
/
Risk_MasterData_Latest.sql
116 lines (114 loc) · 13.5 KB
/
Risk_MasterData_Latest.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
INSERT INTO public.risk_questions (id,question,category,"riskLevel","questionNo","is_PSB","isStrategicContact") VALUES
('ee895822-a724-4fe7-9792-4f1f61c7dd95','Are there unique complex variables within this procurement project? Such as: <ul><li>The services currently reside outside of government with a vendor</li><li>There may require a transition from one vendor to another</li><li>There are a small number of individual requirements to define</li><li>There is a potential labour impact to the procurement</li><li>Other high impact unique feature not listed</li></ul>',2,1,7,true,false)
,('2cf75e3a-fba2-4202-84f3-f025c47c7f19','What is the supplier capability and willingness?',2,2,8,true,false)
,('1c0b9190-ccf9-4239-9209-11d737933a8b','Consider the following criteria regarding knowledge and experience:<ul><li>The project will use a proven approach</li><li>This type of project has been done before in the BC Gov</li><li>The project will use resources that have been applied to this type of project before</li></ul>',2,2,9,true,false)
,('a08e56db-b334-4270-affc-a8f6e2c0fd0a','What level of priority is the project to the owning Ministry?',3,2,1,false,false)
,('e11046b5-dfcf-4343-aff8-651670cc783f','Does the organization anticipate a shortage of available internal personnel with appropriate skills during a significant period of the project? (i.e. SME''s, PM''s, Change Management, etc.)',3,2,2,false,false)
,('396d3b41-ac62-48a6-b311-9d06b2d889a5','How many total people (part-time or full-time on the project, including BC Gov employees and individual contractors) are required to complete this procurement project at its peak activity?',3,2,3,true,false)
,('af58b76a-bd24-4d98-8db1-8adb2650ed83','Has the assigned lead from the project team worked on a project of this size and complexity before?',3,2,4,true,false)
,('e969a6eb-2819-445f-a3f5-1aad40ad75b4','What is the estimated Total Contract Value of this procurement project?',1,1,1,false,false)
,('183b80f2-4688-41fd-ae1c-5a6e7f777c3f','Does this procurement project have a documented project plan with clearly identified Roles & Responsibilities?',1,2,3,false,false)
,('ef0491c5-4715-4f97-a314-21c26164a5d2','Is the project susceptible to time delays? Time delays can have a number of causes, such as the following:<ul><li>Crosses Election periods</li><li>Key timelines happen around End of Fiscal</li><li>Complex Governance Requirements</li><li>Known Capacity Issues</li><li>Timelines are compressed or unattainable</li><li>Involves emerging technologies</li></ul>',1,1,4,true,true)
;
INSERT INTO public.risk_questions (id,question,category,"riskLevel","questionNo","is_PSB","isStrategicContact") VALUES
('a19f05bd-8172-4994-b991-fc717b0e46f2','Are there any unique socio-economic considerations that must be taken into account?',4,2,1,false,false)
,('7867249a-657a-489a-9203-1754ed8885cc','How many separate contracts are estimated as a result this procurement project?',1,1,2,false,false)
,('94ec86da-f955-4e3d-b0ba-932b3044abdb','When is the expected date of the contract execution or start date?',2,2,1,false,false)
,('1f71e8bc-5b88-40c3-ab99-553368ebf685','How many Ministries or BPS Entities are directly involved in the procurement project?',2,2,2,false,false)
,('ec300240-d516-40d4-b9b3-8a23d8f4e951','Will the resulting contract offer government an opportunity for innovation or modernization?',2,2,3,false,false)
,('c81159f4-42bb-48b1-8338-592df3b43eff','Will the services being procured be public facing?',2,2,4,false,false)
,('7f7cb3f0-7668-438d-97bc-db053ed2e767','Will the procurement result in a contract that is one of a larger bundle of contracts supporting a strategic initiative? (i.e. requires an integrator)',2,2,5,true,true)
,('4b667ac6-0015-46ce-bb49-4b19a8d3c3b3','What is the anticipated procurement approach?',2,1,6,true,true)
,('2d4fd180-ef23-4134-8014-be2ec8ed1ec5','Will there be high public interest in the project?',4,2,2,false,false)
,('ba44a355-b2dc-4c86-b9d0-5a0a68fb55bc','Are there unique challenges to ensure that this project complies with relevant Treasury Board policy requirements, such as those regarding security, GBA+, privacy, and management of government information?',4,2,3,true,false)
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('4ddd151e-751c-43e3-9f07-794b1003de70','Potentially',2,2,'2d4fd180-ef23-4134-8014-be2ec8ed1ec5')
,('cb3d0fec-11c3-467e-a688-0307910225f7','Yes',3,3,'2d4fd180-ef23-4134-8014-be2ec8ed1ec5')
,('2d8b22af-1b06-4728-830a-2fb0726b85ca','No',1,1,'2d4fd180-ef23-4134-8014-be2ec8ed1ec5')
,('cf38beb1-58af-43c3-be69-0706a0806df8','Five or more contracts',10,5,'7867249a-657a-489a-9203-1754ed8885cc')
,('2cc39349-373b-4aa6-9046-9bd23fa24610','One contract',2,1,'7867249a-657a-489a-9203-1754ed8885cc')
,('e6379225-7cd4-48e9-9d3b-7fee086dd006','Two contracts',4,2,'7867249a-657a-489a-9203-1754ed8885cc')
,('88ea130f-75dd-4dc1-a12d-4f0d6c4e81e5','Three contracts',6,3,'7867249a-657a-489a-9203-1754ed8885cc')
,('07b9cbd0-719b-4f07-8713-ff220267239c','Four contracts',8,4,'7867249a-657a-489a-9203-1754ed8885cc')
,('1836784b-ed08-44b7-a898-8cf3f5119fe4','Potentially',2,2,'a19f05bd-8172-4994-b991-fc717b0e46f2')
,('f45d442e-ab56-4067-ab93-86ee3fa0d595','Yes',3,3,'a19f05bd-8172-4994-b991-fc717b0e46f2')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('848bd7de-d59d-4175-8851-04c5c104ab9a','No',1,1,'a19f05bd-8172-4994-b991-fc717b0e46f2')
,('af07c63d-4c96-40ad-83e5-a9e06ab64fb6','over $10 million',10,5,'e969a6eb-2819-445f-a3f5-1aad40ad75b4')
,('78500ca2-c957-4999-96a2-360e5ec78f45','>50k',2,1,'e969a6eb-2819-445f-a3f5-1aad40ad75b4')
,('41a02614-dae7-4c48-b1d6-b4c241e2d62f','>250k',4,2,'e969a6eb-2819-445f-a3f5-1aad40ad75b4')
,('f24e1de4-824b-4eec-aac0-5dade4d3c2b9','500k-1 million',6,3,'e969a6eb-2819-445f-a3f5-1aad40ad75b4')
,('23a93640-3a41-44b9-9f9a-71ee45f7bffb','$1-10 million',8,4,'e969a6eb-2819-445f-a3f5-1aad40ad75b4')
,('9c5029ec-f3c0-4a28-be70-433f1a1122cb','No opportunity for innovation',1,1,'ec300240-d516-40d4-b9b3-8a23d8f4e951')
,('5641fe2c-a6d2-4434-9d0b-15a1c99bbb22','Potential opportunity',2,2,'ec300240-d516-40d4-b9b3-8a23d8f4e951')
,('6c175262-5a0e-4120-b1ac-1fd3d65943ba','Some opportunity',3,3,'ec300240-d516-40d4-b9b3-8a23d8f4e951')
,('e047e857-e7d8-468e-8380-d2990f3bc384','Definite opportunity for innovation',4,4,'ec300240-d516-40d4-b9b3-8a23d8f4e951')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('c581d97e-a8de-4456-b124-2b988262a81f','Yes or Not Required',1,1,'183b80f2-4688-41fd-ae1c-5a6e7f777c3f')
,('b36e7eff-a464-4417-89b8-0a11ce629fe7','Partially',2,2,'183b80f2-4688-41fd-ae1c-5a6e7f777c3f')
,('c21977d3-6311-447b-aea5-0fb7d725326a','No',3,3,'183b80f2-4688-41fd-ae1c-5a6e7f777c3f')
,('d0abe350-e32f-427d-b6cc-b22ad1fc9893','In over 24 months',5,5,'94ec86da-f955-4e3d-b0ba-932b3044abdb')
,('464f4657-85c0-47b3-8a09-4097d0df174f','In under 1 month',1,1,'94ec86da-f955-4e3d-b0ba-932b3044abdb')
,('df250e69-b749-46b8-9aaa-97fca80ce0b0','In 2-6 months',2,2,'94ec86da-f955-4e3d-b0ba-932b3044abdb')
,('0c8e249f-bc3a-4b7c-ab30-bebc1b95d0d0','In 7-12 months',3,3,'94ec86da-f955-4e3d-b0ba-932b3044abdb')
,('576e0081-9202-47c0-b888-686dd29f820a','In 12-24 months',4,4,'94ec86da-f955-4e3d-b0ba-932b3044abdb')
,('beeb71d9-b976-483d-a00b-070d5b648f92','The project involves only one Ministry or Entity.',1,1,'1f71e8bc-5b88-40c3-ab99-553368ebf685')
,('ff3978b8-3025-4e2e-aac3-7873b6cc74d9','The project involves two other Ministries or Entities.',2,2,'1f71e8bc-5b88-40c3-ab99-553368ebf685')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('0eeae774-9273-4fa1-bcf4-a0072bf69821','The project involves three other Ministries or Entities.',3,3,'1f71e8bc-5b88-40c3-ab99-553368ebf685')
,('7e5e6063-6e37-415e-9367-b2f9ab1c7f5f','The project involves at least four other Ministries or Entities.',4,4,'1f71e8bc-5b88-40c3-ab99-553368ebf685')
,('77cb51a1-66e4-4f5e-a2ce-5b8e6600753c','Yes',4,4,'c81159f4-42bb-48b1-8338-592df3b43eff')
,('f6319753-11fe-4e28-8ed4-6195a6b9bbb7','No',1,1,'c81159f4-42bb-48b1-8338-592df3b43eff')
,('19dc1942-b79f-447b-9004-29f0a299ac6a','Limited',2,2,'c81159f4-42bb-48b1-8338-592df3b43eff')
,('087c00f6-9541-413b-b300-a8f3f6e5bd69','Some',3,3,'c81159f4-42bb-48b1-8338-592df3b43eff')
,('7748816d-ab15-448d-a4b7-a14476d247d1','The project is a critical priority: all resources necessary will be allocated to it.',1,1,'a08e56db-b334-4270-affc-a8f6e2c0fd0a')
,('917b957a-4614-4421-bb32-25f317028005','The project is a normal priority: resources may be shared with a project of equal or higher priority.',3,2,'a08e56db-b334-4270-affc-a8f6e2c0fd0a')
,('cd589aa2-f653-4121-abb0-746c54b42a1b','The project is a not a priority: resources may be diverted to a project of equal or higher priority.',5,3,'a08e56db-b334-4270-affc-a8f6e2c0fd0a')
,('efe7a811-e2b2-4136-ac76-ad5754236d5c','No',1,1,'e11046b5-dfcf-4343-aff8-651670cc783f')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('c94f6e3a-f9d0-4347-9786-46c439664e99','Yes',3,2,'e11046b5-dfcf-4343-aff8-651670cc783f')
,('23815f9c-c74e-4ea6-bade-0da084a3833e',' No, the project is not susceptible.',2,1,'ef0491c5-4715-4f97-a314-21c26164a5d2')
,('8bb16bd3-3102-4c3c-9285-53f86a2aee6c','Yes, the project is moderately susceptible; time delays will have minor effects on the schedule.',6,2,'ef0491c5-4715-4f97-a314-21c26164a5d2')
,('a7cecc23-7b00-4497-911d-3dffb14498aa','Yes, the project is highly susceptible; time delays will have major effects on the schedule.',10,3,'ef0491c5-4715-4f97-a314-21c26164a5d2')
,('169b59d2-c2ba-4e70-90f2-bead560abd6e','No',1,1,'7f7cb3f0-7668-438d-97bc-db053ed2e767')
,('bbb4fc0f-a7e8-453e-a9e7-5c0ded9f0f19','Yes',3,2,'7f7cb3f0-7668-438d-97bc-db053ed2e767')
,('98a38c05-c026-4c6c-b8d3-bcd0f213b2fe','Directed (sole-source, NOI).',2,1,'4b667ac6-0015-46ce-bb49-4b19a8d3c3b3')
,('46a73ee2-4dc5-49dc-a647-23d16fc2cdd9','A standing offer call-up or Supply Arrangement',4,2,'4b667ac6-0015-46ce-bb49-4b19a8d3c3b3')
,('147aefa0-34c5-4777-809e-dfcfec232759','A public tender (request for quotation, invitation to tender, request for proposal).',6,3,'4b667ac6-0015-46ce-bb49-4b19a8d3c3b3')
,('444707f4-2de0-4e70-a81b-dad881f023f2','Use of new procurement tools (but have been approved by LSB/PSB)',8,4,'4b667ac6-0015-46ce-bb49-4b19a8d3c3b3')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('c28e4b9d-8f45-489e-83af-72c288f4b8d4','Complex process (NRFP, JSRFP, etc...).',10,5,'4b667ac6-0015-46ce-bb49-4b19a8d3c3b3')
,('e40d11ff-4c86-4088-bf4c-41af9350afb4','None of the statements are true.',0,1,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('db3d0033-406f-4e86-91f5-4a974649d806','One of the statements is true.',2,2,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('09a308f8-3972-4623-865c-37740a21a57e','Two of the statements are true.',4,3,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('b15d201c-ba31-4bd4-b30a-cd50cfdf4182','Three of the statements are true.',6,4,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('02b626dd-00f0-4f36-b786-649fa5c69f75','Four of the statements are true.',8,5,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('9d8f6078-99c6-480f-bb31-c7240121c42d','All Five of the statements are true.',10,6,'ee895822-a724-4fe7-9792-4f1f61c7dd95')
,('6e87e312-d7be-4e53-bcf7-c88fb3418a2e','There are qualified suppliers in the market willing to work with the BC Gov and are capable of providing these services',1,1,'2cf75e3a-fba2-4202-84f3-f025c47c7f19')
,('a0cf0ff6-68b1-45ee-aabc-d64a8a7459b1','There is a limited number of qualified suppliers in the market or some suppliers are reluctant to work with the BC Gov',3,2,'2cf75e3a-fba2-4202-84f3-f025c47c7f19')
,('0e053577-cd27-4523-98ab-a6d9717cf5f8','It is estimated that only one supplier or there are no qualified suppliers that can meet the requirements.',5,3,'2cf75e3a-fba2-4202-84f3-f025c47c7f19')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('cc7e4067-8ede-4e29-bc97-b2403c5ab720','All three criteria are met.',1,1,'1c0b9190-ccf9-4239-9209-11d737933a8b')
,('5eef2187-0221-480c-9465-615f9b82fb6b','Two of the three criteria are met.',2,2,'1c0b9190-ccf9-4239-9209-11d737933a8b')
,('0b7cff89-3545-4263-baab-a7c08236cb6b','One of the three criteria is met.',3,3,'1c0b9190-ccf9-4239-9209-11d737933a8b')
,('e2c212ae-e0ae-4bc2-91fc-5732c195559d','None of the three criteria are met.',4,4,'1c0b9190-ccf9-4239-9209-11d737933a8b')
,('e114025b-21c1-44ed-abb6-d5688ec573ec','1',1,1,'396d3b41-ac62-48a6-b311-9d06b2d889a5')
,('010e233b-fcc6-45aa-a2c9-8d2c46665e4d','2-5',2,2,'396d3b41-ac62-48a6-b311-9d06b2d889a5')
,('1dc52fbd-7f9b-422c-b4f5-53d32e13b431','6-11',3,3,'396d3b41-ac62-48a6-b311-9d06b2d889a5')
,('5dd68b4e-ad62-4f36-af5e-bcc7c39c2bcb','12-20',4,4,'396d3b41-ac62-48a6-b311-9d06b2d889a5')
,('710fcba6-2467-408e-847b-59d2dac4aa57','20',5,5,'396d3b41-ac62-48a6-b311-9d06b2d889a5')
,('d2caec47-98ac-4fdc-bf35-26c33fba33c1','Yes',1,1,'af58b76a-bd24-4d98-8db1-8adb2650ed83')
;
INSERT INTO public.risk_answers (id,answer,score,order_id,"questionId") VALUES
('6bd6dbd9-7b56-44ab-a4ce-a54dfd3019ad','No',3,2,'af58b76a-bd24-4d98-8db1-8adb2650ed83')
,('c63862fb-45a1-4553-bc2d-00a596295fd2','The project fully complies with all applicable policies. Alternatively, the project is not subject to any of these policies.',1,1,'ba44a355-b2dc-4c86-b9d0-5a0a68fb55bc')
,('6e423de0-3f4b-4bd2-8b63-b646c0d4e8ba','There are some challenges associated with policy requirements, but the project team is adequately equipped to address these.',2,2,'ba44a355-b2dc-4c86-b9d0-5a0a68fb55bc')
,('49c74b7a-5d7a-4efa-86a6-f7c1d6db0f50','There are some challenges associated with policy requirements and there is a lack of confidence that policy requirements can be met on schedule and within the budget.',3,3,'ba44a355-b2dc-4c86-b9d0-5a0a68fb55bc')
;