8tbpwV0F

1-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2
3DROP TABLE IF EXISTS tb_bond_title_doc_type;
4
5CREATE TABLE tb_bond_title_doc_type (
6    id_contract INTEGER NOT NULL,
7    cd_document_type VARCHAR NOT NULL,
8    ds_document_type VARCHAR NOT NULL,
9    CONSTRAINT tb_bond_title_doc_type_pk PRIMARY KEY (id_contract, cd_document_type)
10);
11
12INSERT INTO tb_bond_title_doc_type (id_contract, cd_document_type, ds_document_type ) VALUES
13(39, 'DIE', 'Documento de Informações Essenciais (DIE)', true),
14(39, 'CONTRACT_ADHESION', 'Termo de Adesão', true),
15(39, 'ADVERTISING_MATERIAL', 'Material Publicitário', false),
16(39, 'OTHER', 'Outros', false);
17
18
19-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20
21DROP TABLE tb_bond_title_document_status;
22
23CREATE TABLE tb_bond_title_document_status (
24	id_contract int4 NOT NULL,
25	cd_document_status varchar NOT NULL,
26	ds_document_status varchar NULL,
27	CONSTRAINT tb_bond_title_document_status_pk PRIMARY KEY (id_contract, cd_document_status)
28);
29
30INSERT INTO tb_bond_title_document_status(id_contract, cd_document_status, ds_document_status) VALUES
31(39, 'AT', 'Ativo'),
32(39, 'IN', 'Inativo');
33
34
35-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
36
37DROP TABLE tb_bond_title_document_logs;
38
39CREATE TABLE tb_bond_title_document_logs (
40	id_contract int4 NULL,
41	id_bond int4 NULL,
42	id_document int4 NULL,
43	id_user int4 NULL,
44	ds_document varchar NULL,
45	url_document varchar NULL,
46	ip varchar NULL,
47	user_agent varchar NULL,
48	dh_last_update timestamp NULL,
49	cd_document_type varchar NULL,
50	cd_document_status varchar NULL,
51	"action" varchar NULL
52);
53
54
55-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
56
57DROP TABLE IF EXISTS tb_bond_title_document;
58
59CREATE TABLE tb_bond_title_document (
60	id_contract int4 NOT NULL,
61	id_bond int4 NOT NULL,
62	id_document int4 NOT NULL,
63	id_user int4 NULL,
64	ds_document varchar NULL,
65	url_document varchar NULL,
66	ip varchar NULL,
67	user_agent varchar NULL,
68	dh_last_update timestamp NULL,
69	cd_document_type varchar NULL,
70	cd_document_status varchar NULL,
71	CONSTRAINT tb_bond_title_document_pk PRIMARY KEY (id_contract, id_bond, id_document),
72	CONSTRAINT tb_bond_title_document_status_tb_bond_title_document_fk FOREIGN KEY (id_contract,cd_document_status) REFERENCES public.dm_coe_document_status(id_contract,cd_document_status),
73	CONSTRAINT tb_bond_title_doc_type_tb_bond_title_document_fk FOREIGN KEY (id_contract,cd_document_type) REFERENCES tb_bond_title_doc_type(id_contract,cd_document_type),
74	CONSTRAINT tb_bond_title_tb_bond_title_document_fk FOREIGN KEY (id_contract, id_bond) REFERENCES tb_bond_title(id_contract, id_bond)
75);
76
77-- Function para gerar logs, criada antes da Trigger abaixo que executa ela
78
79CREATE OR REPLACE FUNCTION fun_tb_bond_title_document_logs()
80 RETURNS trigger
81 LANGUAGE plpgsql
82AS $function$
83    BEGIN
84        INSERT INTO PUBLIC.tb_bond_title_document_logs (
85            id_contract,
86            id_bond,
87            id_document,
88            id_user,
89            ds_document,
90            url_document,
91            ip,
92            user_agent,
93            dh_last_update,
94            cd_document_type,
95            cd_document_status,
96            action
97        ) VALUES (
98            NEW.id_contract,
99            NEW.id_bond,
100            NEW.id_document,
101            NEW.id_user,
102            NEW.ds_document,
103            NEW.url_document,
104            NEW.ip,
105            NEW.user_agent,
106            NEW.dh_last_update,
107            NEW.cd_document_type,
108            NEW.cd_document_status,
109            TG_OP
110        );
111        RETURN NEW;
112    RETURN NULL;
113    END;
114$function$
115
116-- Trigger para gerar os logs de documentos inseridos, atualizados ou inativos
117
118create trigger trigger_tb_bond_title_document_logs after
119insert
120    or
121update
122    on
123    tb_bond_title_document for each row execute procedure fun_tb_bond_title_document_logs();
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