-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathbudget_all.schema_only.sql
550 lines (490 loc) · 17.2 KB
/
budget_all.schema_only.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
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: budget_expenditures; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_expenditures (
head smallint NOT NULL,
name character varying(128),
actual2006 integer,
approved2006 integer,
revised2006 integer,
estimate2006 integer,
actual2007 integer,
approved2007 integer,
revised2007 integer,
estimate2007 integer,
actual2008 integer,
approved2008 integer,
revised2008 integer,
estimate2008 integer,
actual2009 integer,
approved2009 integer,
revised2009 integer,
estimate2009 integer,
actual2010 integer,
approved2010 integer,
revised2010 integer,
estimate2010 integer,
actual2011 integer,
approved2011 integer,
revised2011 integer,
estimate2011 integer,
actual2012 integer,
approved2012 integer,
revised2012 integer,
estimate2012 integer,
actual2013 integer,
approved2013 integer,
revised2013 integer,
estimate2013 integer,
actual2014 integer,
approved2014 integer,
revised2014 integer,
estimate2014 integer
);
ALTER TABLE public.budget_expenditures OWNER TO scmp;
--
-- Name: budget_expenditures_heads; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_expenditures_heads (
head_no smallint NOT NULL,
head_name character varying(512)
);
ALTER TABLE public.budget_expenditures_heads OWNER TO scmp;
--
-- Name: budget_expenditures_programmes; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_expenditures_programmes (
year smallint NOT NULL,
head_no smallint NOT NULL,
programme_no smallint NOT NULL,
programme_name character varying(512),
programme_aim text,
actual numeric,
original numeric,
revised numeric,
estimate numeric
);
ALTER TABLE public.budget_expenditures_programmes OWNER TO scmp;
--
-- Name: budget_expenditures_totals; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_expenditures_totals (
head_no smallint NOT NULL,
head_name character varying(512),
actual_recurrent_2003 integer,
approved_recurrent_2004 integer,
revised_recurrent_2004 integer,
estimate_recurrent_2005 integer,
actual_nonrecurrent_2003 integer,
approved_nonrecurrent_2004 integer,
revised_nonrecurrent_2004 integer,
estimate_nonrecurrent_2005 integer,
actual_operating_2003 integer,
approved_operating_2004 integer,
revised_operating_2004 integer,
estimate_operating_2005 integer,
actual_plant_equipment_works_2003 integer,
approved_plant_equipment_works_2004 integer,
revised_plant_equipment_works_2004 integer,
estimate_plant_equipment_works_2005 integer,
actual_subventions_2003 integer,
approved_subventions_2004 integer,
revised_subventions_2004 integer,
estimate_subventions_2005 integer,
actual_capital_2003 integer,
approved_capital_2004 integer,
revised_capital_2004 integer,
estimate_capital_2005 integer,
actual_expenditure_2003 integer,
approved_expenditure_2004 integer,
revised_expenditure_2004 integer,
estimate_expenditure_2005 integer,
actual_recurrent_2004 integer,
approved_recurrent_2005 integer,
revised_recurrent_2005 integer,
estimate_recurrent_2006 integer,
actual_nonrecurrent_2004 integer,
approved_nonrecurrent_2005 integer,
revised_nonrecurrent_2005 integer,
estimate_nonrecurrent_2006 integer,
actual_operating_2004 integer,
approved_operating_2005 integer,
revised_operating_2005 integer,
estimate_operating_2006 integer,
actual_plant_equipment_works_2004 integer,
approved_plant_equipment_works_2005 integer,
revised_plant_equipment_works_2005 integer,
estimate_plant_equipment_works_2006 integer,
actual_subventions_2004 integer,
approved_subventions_2005 integer,
revised_subventions_2005 integer,
estimate_subventions_2006 integer,
actual_capital_2004 integer,
approved_capital_2005 integer,
revised_capital_2005 integer,
estimate_capital_2006 integer,
actual_expenditure_2004 integer,
approved_expenditure_2005 integer,
revised_expenditure_2005 integer,
estimate_expenditure_2006 integer,
actual_recurrent_2005 integer,
approved_recurrent_2006 integer,
revised_recurrent_2006 integer,
estimate_recurrent_2007 integer,
actual_nonrecurrent_2005 integer,
approved_nonrecurrent_2006 integer,
revised_nonrecurrent_2006 integer,
estimate_nonrecurrent_2007 integer,
actual_operating_2005 integer,
approved_operating_2006 integer,
revised_operating_2006 integer,
estimate_operating_2007 integer,
actual_plant_equipment_works_2005 integer,
approved_plant_equipment_works_2006 integer,
revised_plant_equipment_works_2006 integer,
estimate_plant_equipment_works_2007 integer,
actual_subventions_2005 integer,
approved_subventions_2006 integer,
revised_subventions_2006 integer,
estimate_subventions_2007 integer,
actual_capital_2005 integer,
approved_capital_2006 integer,
revised_capital_2006 integer,
estimate_capital_2007 integer,
actual_expenditure_2005 integer,
approved_expenditure_2006 integer,
revised_expenditure_2006 integer,
estimate_expenditure_2007 integer,
actual_recurrent_2006 integer,
approved_recurrent_2007 integer,
revised_recurrent_2007 integer,
estimate_recurrent_2008 integer,
actual_nonrecurrent_2006 integer,
approved_nonrecurrent_2007 integer,
revised_nonrecurrent_2007 integer,
estimate_nonrecurrent_2008 integer,
actual_operating_2006 integer,
approved_operating_2007 integer,
revised_operating_2007 integer,
estimate_operating_2008 integer,
actual_plant_equipment_works_2006 integer,
approved_plant_equipment_works_2007 integer,
revised_plant_equipment_works_2007 integer,
estimate_plant_equipment_works_2008 integer,
actual_subventions_2006 integer,
approved_subventions_2007 integer,
revised_subventions_2007 integer,
estimate_subventions_2008 integer,
actual_capital_2006 integer,
approved_capital_2007 integer,
revised_capital_2007 integer,
estimate_capital_2008 integer,
actual_expenditure_2006 integer,
approved_expenditure_2007 integer,
revised_expenditure_2007 integer,
estimate_expenditure_2008 integer,
actual_recurrent_2007 integer,
approved_recurrent_2008 integer,
revised_recurrent_2008 integer,
estimate_recurrent_2009 integer,
actual_nonrecurrent_2007 integer,
approved_nonrecurrent_2008 integer,
revised_nonrecurrent_2008 integer,
estimate_nonrecurrent_2009 integer,
actual_operating_2007 integer,
approved_operating_2008 integer,
revised_operating_2008 integer,
estimate_operating_2009 integer,
actual_plant_equipment_works_2007 integer,
approved_plant_equipment_works_2008 integer,
revised_plant_equipment_works_2008 integer,
estimate_plant_equipment_works_2009 integer,
actual_subventions_2007 integer,
approved_subventions_2008 integer,
revised_subventions_2008 integer,
estimate_subventions_2009 integer,
actual_capital_2007 integer,
approved_capital_2008 integer,
revised_capital_2008 integer,
estimate_capital_2009 integer,
actual_expenditure_2007 integer,
approved_expenditure_2008 integer,
revised_expenditure_2008 integer,
estimate_expenditure_2009 integer,
actual_recurrent_2008 integer,
approved_recurrent_2009 integer,
revised_recurrent_2009 integer,
estimate_recurrent_2010 integer,
actual_nonrecurrent_2008 integer,
approved_nonrecurrent_2009 integer,
revised_nonrecurrent_2009 integer,
estimate_nonrecurrent_2010 integer,
actual_operating_2008 integer,
approved_operating_2009 integer,
revised_operating_2009 integer,
estimate_operating_2010 integer,
actual_plant_equipment_works_2008 integer,
approved_plant_equipment_works_2009 integer,
revised_plant_equipment_works_2009 integer,
estimate_plant_equipment_works_2010 integer,
actual_subventions_2008 integer,
approved_subventions_2009 integer,
revised_subventions_2009 integer,
estimate_subventions_2010 integer,
actual_capital_2008 integer,
approved_capital_2009 integer,
revised_capital_2009 integer,
estimate_capital_2010 integer,
actual_expenditure_2008 integer,
approved_expenditure_2009 integer,
revised_expenditure_2009 integer,
estimate_expenditure_2010 integer,
actual_recurrent_2009 integer,
approved_recurrent_2010 integer,
revised_recurrent_2010 integer,
estimate_recurrent_2011 integer,
actual_nonrecurrent_2009 integer,
approved_nonrecurrent_2010 integer,
revised_nonrecurrent_2010 integer,
estimate_nonrecurrent_2011 integer,
actual_operating_2009 integer,
approved_operating_2010 integer,
revised_operating_2010 integer,
estimate_operating_2011 integer,
actual_plant_equipment_works_2009 integer,
approved_plant_equipment_works_2010 integer,
revised_plant_equipment_works_2010 integer,
estimate_plant_equipment_works_2011 integer,
actual_subventions_2009 integer,
approved_subventions_2010 integer,
revised_subventions_2010 integer,
estimate_subventions_2011 integer,
actual_capital_2009 integer,
approved_capital_2010 integer,
revised_capital_2010 integer,
estimate_capital_2011 integer,
actual_expenditure_2009 integer,
approved_expenditure_2010 integer,
revised_expenditure_2010 integer,
estimate_expenditure_2011 integer,
actual_recurrent_2010 integer,
approved_recurrent_2011 integer,
revised_recurrent_2011 integer,
estimate_recurrent_2012 integer,
actual_nonrecurrent_2010 integer,
approved_nonrecurrent_2011 integer,
revised_nonrecurrent_2011 integer,
estimate_nonrecurrent_2012 integer,
actual_operating_2010 integer,
approved_operating_2011 integer,
revised_operating_2011 integer,
estimate_operating_2012 integer,
actual_plant_equipment_works_2010 integer,
approved_plant_equipment_works_2011 integer,
revised_plant_equipment_works_2011 integer,
estimate_plant_equipment_works_2012 integer,
actual_subventions_2010 integer,
approved_subventions_2011 integer,
revised_subventions_2011 integer,
estimate_subventions_2012 integer,
actual_capital_2010 integer,
approved_capital_2011 integer,
revised_capital_2011 integer,
estimate_capital_2012 integer,
actual_expenditure_2010 integer,
approved_expenditure_2011 integer,
revised_expenditure_2011 integer,
estimate_expenditure_2012 integer,
actual_recurrent_2011 integer,
approved_recurrent_2012 integer,
revised_recurrent_2012 integer,
estimate_recurrent_2013 integer,
actual_nonrecurrent_2011 integer,
approved_nonrecurrent_2012 integer,
revised_nonrecurrent_2012 integer,
estimate_nonrecurrent_2013 integer,
actual_operating_2011 integer,
approved_operating_2012 integer,
revised_operating_2012 integer,
estimate_operating_2013 integer,
actual_plant_equipment_works_2011 integer,
approved_plant_equipment_works_2012 integer,
revised_plant_equipment_works_2012 integer,
estimate_plant_equipment_works_2013 integer,
actual_subventions_2011 integer,
approved_subventions_2012 integer,
revised_subventions_2012 integer,
estimate_subventions_2013 integer,
actual_capital_2011 integer,
approved_capital_2012 integer,
revised_capital_2012 integer,
estimate_capital_2013 integer,
actual_expenditure_2011 integer,
approved_expenditure_2012 integer,
revised_expenditure_2012 integer,
estimate_expenditure_2013 integer
);
ALTER TABLE public.budget_expenditures_totals OWNER TO scmp;
--
-- Name: budget_policy_areas; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_policy_areas (
policy_area_id smallint NOT NULL,
policy_area character varying(128),
officer character varying(64) NOT NULL,
head_no smallint NOT NULL,
head_name character varying(128),
programmes character varying(64)
);
ALTER TABLE public.budget_policy_areas OWNER TO scmp;
--
-- Name: budget_revenues; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_revenues (
head smallint NOT NULL,
name character varying(128),
actual2006 integer,
approved2006 integer,
revised2006 integer,
estimate2006 integer,
actual2007 integer,
approved2007 integer,
revised2007 integer,
estimate2007 integer,
actual2008 integer,
approved2008 integer,
revised2008 integer,
estimate2008 integer,
actual2009 integer,
approved2009 integer,
revised2009 integer,
estimate2009 integer,
actual2010 integer,
approved2010 integer,
revised2010 integer,
estimate2010 integer,
actual2011 integer,
approved2011 integer,
revised2011 integer,
estimate2011 integer,
actual2012 integer,
approved2012 integer,
revised2012 integer,
estimate2012 integer,
actual2013 integer,
approved2013 integer,
revised2013 integer,
estimate2013 integer,
actual2014 integer,
approved2014 integer,
revised2014 integer,
estimate2014 integer
);
ALTER TABLE public.budget_revenues OWNER TO scmp;
--
-- Name: budget_subheads; Type: TABLE; Schema: public; Owner: scmp; Tablespace:
--
CREATE TABLE budget_subheads (
year smallint,
head_no smallint,
subhead_name character varying(512),
subhead_no character(3),
item_no character(3),
title character varying(256),
note_actual character varying(256),
note_original character varying(256),
note_revised character varying(256),
note_estimate character varying(256),
reimbursement bigint,
actual bigint,
approved bigint,
revised bigint,
estimate bigint
);
ALTER TABLE public.budget_subheads OWNER TO scmp;
--
-- Name: budget_subheads_expenditures; Type: VIEW; Schema: public; Owner: scmp
--
CREATE VIEW budget_subheads_expenditures AS
SELECT budget_subheads.year,
budget_subheads.head_no,
budget_subheads.subhead_no,
budget_subheads.subhead_name,
budget_subheads.reimbursement,
budget_subheads.item_no,
budget_subheads.title,
budget_subheads.actual,
budget_subheads.revised,
budget_subheads.estimate
FROM budget_subheads
WHERE ((((budget_subheads.head_no > 11) OR (budget_subheads.head_no < (-11))) AND ((budget_subheads.subhead_name)::text !~~ 'Total, %'::text)) AND ((budget_subheads.subhead_name)::text !~~ 'Total Expenditure%'::text))
ORDER BY budget_subheads.year, budget_subheads.head_no, budget_subheads.subhead_no;
ALTER TABLE public.budget_subheads_expenditures OWNER TO scmp;
--
-- Name: budget_subheads_revenues; Type: VIEW; Schema: public; Owner: scmp
--
CREATE VIEW budget_subheads_revenues AS
SELECT budget_subheads.year,
budget_subheads.head_no,
budget_subheads.subhead_name,
budget_subheads.subhead_no,
budget_subheads.item_no,
budget_subheads.title,
budget_subheads.note_actual,
budget_subheads.note_original,
budget_subheads.note_revised,
budget_subheads.note_estimate,
budget_subheads.reimbursement,
budget_subheads.actual,
budget_subheads.approved,
budget_subheads.revised,
budget_subheads.estimate
FROM budget_subheads
WHERE ((((budget_subheads.head_no <= 11) AND (budget_subheads.head_no >= (-11))) AND ((budget_subheads.subhead_name)::text <> 'Total'::text)) AND ((budget_subheads.subhead_name)::text <> 'Sub-total'::text))
ORDER BY budget_subheads.year, budget_subheads.head_no, budget_subheads.subhead_no;
ALTER TABLE public.budget_subheads_revenues OWNER TO scmp;
--
-- Name: budget_expenditures_heads_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_expenditures_heads
ADD CONSTRAINT budget_expenditures_heads_pkey PRIMARY KEY (head_no);
--
-- Name: budget_expenditures_programmes_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_expenditures_programmes
ADD CONSTRAINT budget_expenditures_programmes_pkey PRIMARY KEY (year, head_no, programme_no);
--
-- Name: budget_expenditures_totals_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_expenditures_totals
ADD CONSTRAINT budget_expenditures_totals_pkey PRIMARY KEY (head_no);
--
-- Name: budget_policy_areas_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_policy_areas
ADD CONSTRAINT budget_policy_areas_pkey PRIMARY KEY (policy_area_id, officer, head_no);
--
-- Name: expenditures_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_expenditures
ADD CONSTRAINT expenditures_pkey PRIMARY KEY (head);
--
-- Name: revenues_pkey; Type: CONSTRAINT; Schema: public; Owner: scmp; Tablespace:
--
ALTER TABLE ONLY budget_revenues
ADD CONSTRAINT revenues_pkey PRIMARY KEY (head);
--
-- PostgreSQL database dump complete
--