BuL6Tu6N

1--------------------------------------------------------------------------------------------------------------------------------------------------------------------
2-- Versão 1.11 -> Novas tabelas para controle de criação de carteiras (tb_tm_investor_portfolio_create_request, tb_tm_investor_portfolio_deleted)
3--------------------------------------------------------------------------------------------------------------------------------------------------------------------
4
5--> 1.10 -> Arrumando a sequence da tb_cei_executor_request, pois com reinserção da tb_cei_request ela havia descasado
6
7--> 1.9 -> Redefinindo insert da tb_cei_request, pois estava com problema de chave duplicada
8
9--> 1.8 -> Corrigido inserts que não estavam funcionando no script, novos Indices que melhoram os processos em geral
10
11--> 1.7 -> Algumas MVs alteradas e a tb_investor_broker_account retornada (não será alterada)
12
13--> 1.6 -> Organização por tabelas mais leves e verificação de Indices mais úteis
14--         > Tabelas com nome *_deleted não possuem indices, pois são usadas como log e não existem processos/aplicações que consultem elas
15
16--------------------------------------------------------------------------------------------------------------------------------------------------------------------
17-- 1. tb_cei_investors - OK (Tem que ser a primeira a ser inserida, pois há depedência em outras tabelas)
18--------------------------------------------------------------------------------------------------------------------------------------------------------------------
19ALTER TABLE tb_cei_investors RENAME CONSTRAINT tb_cei_investors_pk TO tb_cei_investors_bkp_pk;
20DROP TABLE IF EXISTS tb_cei_investors_new;
21CREATE TABLE tb_cei_investors_new (
22    id_cei_investor BIGSERIAL NOT NULL,
23    id_investor INTEGER NOT NULL,
24    username BIGINT NOT NULL,
25    password VARCHAR NOT NULL,
26    downloaded TIMESTAMP,
27    sincronized BOOLEAN DEFAULT false,
28    cd_status VARCHAR DEFAULT 'PENDING',
29    dt_reference INTEGER,
30    in_initial_import BOOLEAN DEFAULT false,
31    success_count INTEGER DEFAULT 0,
32    priority INTEGER DEFAULT 1,
33    use_validation BOOLEAN NOT NULL DEFAULT true,
34    dt_reference_resync INTEGER
35);
36ALTER TABLE tb_cei_investors_new ADD CONSTRAINT tb_cei_investors_pk PRIMARY KEY (id_cei_investor);
37-- ALTER TABLE tb_cei_investors_new ADD CONSTRAINT tb_cei_investors_status_fk FOREIGN KEY (cd_status) REFERENCES tb_cei_investors_status(cd_status); -- Essa fk não é necessária
38-- ALterada unique para ser possível cadastrar contas iguais para investors diferentes
39--ALTER TABLE tb_cei_investors_new ADD CONSTRAINT tb_cei_investors_uk UNIQUE (id_investor, username);
40
41-->> Necessario desativar os usuarios duplicados da tb_investor
42INSERT INTO tb_cei_investors_new (id_investor, username, password, downloaded, sincronized, cd_status, dt_reference, in_initial_import, success_count, priority, use_validation, dt_reference_resync)
43SELECT tb_cei_investors.id, TO_NUMBER(username, '999999999999999999')::BIGINT, tb_cei_investors.password, tb_cei_investors.downloaded, tb_cei_investors.sincronized, tb_cei_investors.cd_status, tb_cei_investors.dt_reference,
44    tb_cei_investors.in_initial_import, tb_cei_investors.success_count, tb_cei_investors.priority, tb_cei_investors.use_validation, tb_cei_investors.dt_reference_resync
45FROM tb_cei_investors
46INNER JOIN tb_investor on tb_cei_investors.id = tb_investor.id_investor
47WHERE tb_cei_investors.username IS NOT NULL AND tb_cei_investors.username <> '' AND tb_cei_investors.username <> '00000000000' AND tb_investor.cd_investor_status IN ('AT', 'RE', 'PE', 'PW');
48
49ALTER TABLE tb_cei_investors RENAME TO tb_cei_investors_bkp;
50ALTER TABLE tb_cei_investors_new RENAME TO tb_cei_investors;
51
52DROP TRIGGER trigger_delete_tb_cei_investors ON tb_cei_investors_bkp;
53-- A trigger abaixo se encontra no item "6. tb_cei_investors_deleted"
54CREATE TRIGGER trigger_delete_tb_cei_investors BEFORE DELETE ON tb_cei_investors FOR EACH ROW EXECUTE PROCEDURE fc_trigger_delete_tb_cei_investors();
55
56--> Usos da tabela:
57--> É usada em muitos join/consultas/atualizações pelos campos de id_cei_investor ou id_investor ou username ou cd_status -> integrator-process e vários outros
58-- O id_cei_investor é PK, então não será criado indice pra ele
59DROP INDEX IF EXISTS tb_cei_investors_investor_idx;
60--CREATE INDEX tb_cei_investors_investor_idx ON tb_cei_investors (id_investor);
61--CREATE INDEX tb_cei_investors_username_idx ON tb_cei_investors (username); -- O WHERE encontrado usava só o username, sem outros campos
62CREATE INDEX tb_cei_investors_status_idx ON tb_cei_investors (cd_status); -- O WHERE encontrado usava só o cd_status, sem outros campos
63
64-------insert dev, removendo todos os duplicados (que em prod não terão, pois o malves irá remover)
65/*
66with DUPLICADOS as (
67    select TO_NUMBER(username, '999999999999999999')::BIGINT  as username
68    from tb_cei_investors
69    WHERE tb_cei_investors.username <> ''
70    group by TO_NUMBER(username, '999999999999999999')::BIGINT having COUNT(username) > 1
71), duplicados_2 as (
72    select id from tb_cei_investors
73    WHERE tb_cei_investors.username <> ''
74    and TO_NUMBER(username, '999999999999999999')::BIGINT in (select username from duplicados)
75)
76INSERT INTO tb_cei_investors_new (id_investor, username, password, downloaded, sincronized, cd_status, dt_reference, in_initial_import, success_count, priority, use_validation, dt_reference_resync)
77SELECT tb_cei_investors.id, TO_NUMBER(username, '999999999999999999')::BIGINT, tb_cei_investors.password, tb_cei_investors.downloaded, tb_cei_investors.sincronized, tb_cei_investors.cd_status, tb_cei_investors.dt_reference,
78    tb_cei_investors.in_initial_import, tb_cei_investors.success_count, tb_cei_investors.priority, tb_cei_investors.use_validation, tb_cei_investors.dt_reference_resync
79FROM tb_cei_investors
80INNER JOIN tb_investor on tb_cei_investors.id = tb_investor.id_investor
81WHERE tb_cei_investors.username <> ''
82    AND tb_cei_investors.username IS NOT NULL
83    AND TO_NUMBER(tb_cei_investors.username, '999999999999999999')::BIGINT NOT IN (SELECT username FROM duplicados)
84    AND tb_cei_investors.id NOT IN (SELECT id FROM duplicados_2)
85    AND tb_cei_investors.username <> '00000000000'
86    AND tb_investor.cd_investor_status IN ('AT', 'RE', 'PE', 'PW');
87*/
88
89
90--------------------------------------------------------------------------------------------------------------------------------------------------------------------
91-- 2. tb_cei_brokerage_accounts - OK (Tem que ser uma das primeiras a serem reformadas, pois há depedência em outras tabelas)
92--------------------------------------------------------------------------------------------------------------------------------------------------------------------
93ALTER TABLE tb_cei_brokerage_accounts RENAME CONSTRAINT tb_cei_brokerage_accounts_pk TO tb_cei_brokerage_accounts_bkp_pk;
94ALTER TABLE tb_cei_brokerage_accounts RENAME TO tb_cei_brokerage_accounts_bkp;
95CREATE TABLE tb_cei_brokerage_accounts (
96    id_cei_brokerage_account BIGSERIAL NOT NULL,
97    id_cei_investor BIGINT NOT NULL,
98    brokerage_id INTEGER NOT NULL,
99    number VARCHAR(30)
100);
101ALTER TABLE tb_cei_brokerage_accounts ADD CONSTRAINT tb_cei_brokerage_accounts_pk PRIMARY KEY (id_cei_brokerage_account);
102-- ALTER TABLE tb_cei_brokerage_accounts ADD CONSTRAINT tb_cei_brokerage_accounts_fk FOREIGN KEY (brokerage_id) REFERENCES tb_cei_brokerages(brokerage_id); -- Retirei a FK, verificar necessidade
103INSERT INTO tb_cei_brokerage_accounts (id_cei_brokerage_account, id_cei_investor, brokerage_id, number)
104    SELECT id, tb_cei_investors.id_cei_investor, brokerage_id, number
105    FROM tb_cei_brokerage_accounts_bkp
106    INNER JOIN tb_cei_investors ON tb_cei_brokerage_accounts_bkp.investor_id = tb_cei_investors.id_investor;
107
108CREATE INDEX tb_cei_brokerage_accounts_number_cei_investor_idx ON tb_cei_brokerage_accounts (id_cei_investor, brokerage_id, number);
109--SELECT setval('"public"."tb_cei_brokerage_accounts_id_cei_brokerage_account_seq"'::regclass, (select MAX("id_cei_brokerage_account") FROM "public"."tb_cei_brokerage_accounts"));
110--SELECT setval('"public"."tb_cei_brokerage_accounts_id_seq"'::regclass, (select MAX("id_cei_brokerage_account") FROM "public"."tb_cei_brokerage_accounts"));
111--SELECT setval('"public"."tb_cei_brokerage_accounts_id_cei_brokerage_account_seq1"'::regclass, (select MAX("id_cei_brokerage_account") FROM "public"."tb_cei_brokerage_accounts"));
112-- OK, existem usos da PK e do índice acima
113
114--------------------------------------------------------------------------------------------------------------------------------------------------------------------
115-- 3. tb_cei_traded_assets_deleted - OK
116--------------------------------------------------------------------------------------------------------------------------------------------------------------------
117ALTER TABLE tb_cei_traded_assets_deleted RENAME TO tb_cei_traded_assets_deleted_bkp;
118CREATE TABLE tb_cei_traded_assets_deleted (
119    id_cei_traded_assets BIGINT,
120    id_cei_investor BIGINT,
121    id_cei_brokerage_account BIGINT,
122    entry_date DATE,
123    order_type VARCHAR(1),
124    market VARCHAR(40),
125    trading_code VARCHAR(20),
126    quantity INTEGER,
127    price NUMERIC(13,4),
128    value NUMERIC(13,4),
129    quotation_factor NUMERIC(8,4),
130    specification VARCHAR(40),
131    maturity_date DATE,
132    already_imported BOOLEAN DEFAULT FALSE
133);
134-- OK, tabela de logs de deleção
135INSERT INTO tb_cei_traded_assets_deleted (id_cei_traded_assets, id_cei_investor, id_cei_brokerage_account, entry_date,order_type,market, quantity, price, value, quotation_factor, specification, maturity_date, already_imported)
136SELECT id, id_cei_investor, account_id, entry_date,order_type,market, quantity, price, value, quotation_factor, specification, maturity_date, already_imported
137FROM tb_cei_traded_assets_deleted_bkp
138INNER JOIN tb_cei_investors ON tb_cei_traded_assets_deleted_bkp.investor_id = tb_cei_investors.id_investor;
139
140
141--------------------------------------------------------------------------------------------------------------------------------------------------------------------
142-- 4. tb_cei_assets_deleted - OK
143--------------------------------------------------------------------------------------------------------------------------------------------------------------------
144ALTER TABLE tb_cei_assets_deleted RENAME TO tb_cei_assets_deleted_bkp;
145CREATE TABLE tb_cei_assets_deleted (
146    id_cei_assets BIGINT,
147    id_cei_investor BIGINT,
148    id_cei_brokerage_account BIGINT,
149    entry_date DATE,
150    company VARCHAR,
151    type VARCHAR,
152    trading_code VARCHAR,
153    isin VARCHAR,
154    price NUMERIC(13,4),
155    quantity NUMERIC(13,4),
156    value NUMERIC(13,4),
157    quotation_factor NUMERIC(8,4),
158    dt_matutirity INTEGER,
159    derivative_guarantee BOOLEAN
160);
161-- OK, tabela de logs de deleção
162INSERT INTO tb_cei_assets_deleted (id_cei_assets, id_cei_investor, id_cei_brokerage_account, entry_date, company, type, trading_code, isin, price, quantity, value, quotation_factor, dt_matutirity, derivative_guarantee)
163SELECT id, id_cei_investor, account_id, entry_date, company, type,
164    trading_code, isin, price, quantity, value, quotation_factor, dt_matutirity, derivative_guarantee
165FROM tb_cei_assets_deleted_bkp
166INNER JOIN tb_cei_investors ON tb_cei_assets_deleted_bkp.investor_id = tb_cei_investors.id_investor;
167
168
169--------------------------------------------------------------------------------------------------------------------------------------------------------------------
170-- 5. tb_cei_bonds_personalized - OK
171--------------------------------------------------------------------------------------------------------------------------------------------------------------------
172ALTER TABLE tb_cei_bonds_personalized RENAME TO tb_cei_bonds_personalized_bkp;
173CREATE TABLE tb_cei_bonds_personalized (
174    id_cei_bonds_personalized BIGSERIAL NOT NULL,
175    id_cei_investor BIGINT NOT NULL,
176    id_institution INTEGER NOT NULL,
177    id_instrument INTEGER NOT NULL,
178    dt_application INTEGER,
179    cd_title VARCHAR,
180    vl_price_buy NUMERIC(23,8),
181    vl_current_price NUMERIC(23,8),
182    qt_buy NUMERIC(23,8)
183);
184ALTER TABLE tb_cei_bonds_personalized ADD CONSTRAINT tb_cei_bonds_personalized_pk PRIMARY KEY (id_cei_bonds_personalized);
185--> Usos da tabela:
186--> É usada num join o campo id_investor (esse campo não existe mais, agora é o id_cei_investor da tb_cei_investors) -> trademap-portal-ws !! Essa query não foi mexida no repositório pois usa uma tabela que será dropada (Ainda vou verificar esse ponto)
187--> É usada num join os campos (id_instrument, id_cei_investor, id_institution) -> integrator-process
188CREATE INDEX tb_cei_bonds_personalized_instrument_investor_idx ON tb_cei_bonds_personalized (id_instrument, id_cei_investor, id_institution);
189
190INSERT INTO tb_cei_bonds_personalized (id_cei_investor, id_institution, id_instrument, dt_application, cd_title,
191    vl_price_buy, vl_current_price, qt_buy)
192SELECT id_cei_investor, id_institution, id_instrument, dt_application, cd_title,
193    vl_price_buy, vl_current_price, qt_buy
194FROM tb_cei_bonds_personalized_bkp
195INNER JOIN tb_cei_investors ON tb_cei_bonds_personalized_bkp.id_investor = tb_cei_investors.id_investor;
196
197
198--------------------------------------------------------------------------------------------------------------------------------------------------------------------
199-- 6. tb_cei_custody_transfer - OK
200--------------------------------------------------------------------------------------------------------------------------------------------------------------------
201ALTER TABLE tb_cei_custody_transfer RENAME TO tb_cei_custody_transfer_bkp;
202CREATE TABLE tb_cei_custody_transfer (
203    id_cei_custody_transfer BIGSERIAL NOT NULL,
204    id_cei_investor BIGINT NOT NULL,
205    dt_reference INTEGER NOT NULL,
206    id_brokerage INTEGER NOT NULL,
207    cd_isin VARCHAR NOT NULL,
208    cd_account INTEGER,
209    is_btc_operation BOOLEAN,
210    is_with_change_ownership BOOLEAN,
211    id_destiny_instituition INTEGER,
212    cd_destiny_account INTEGER,
213    nm_company VARCHAR,
214    id_distribuition INTEGER,
215    ds_specification VARCHAR,
216    qt_ticker INTEGER
217);
218
219ALTER TABLE tb_cei_custody_transfer_bkp RENAME COLUMN id_destiny_brokerage TO id_destiny_instituition;
220ALTER TABLE tb_cei_custody_transfer ADD CONSTRAINT tb_cei_custody_transfer_pk PRIMARY KEY (id_cei_custody_transfer);
221ALTER TABLE tb_cei_custody_transfer ADD CONSTRAINT tb_cei_custody_transfer_uk UNIQUE (id_cei_investor, id_brokerage, dt_reference, cd_isin);
222--> Usos da tabela:
223--> Há deletes que usam o campos id_cei_investor e dt_reference no WHERE --> integrator-process
224CREATE INDEX tb_cei_custody_transfer_investor_idx ON tb_cei_custody_transfer (id_cei_investor); -- Criei só com o id_cei_investor, pois deve abranger mais cenários
225
226INSERT INTO tb_cei_custody_transfer (id_cei_investor, dt_reference, id_brokerage, cd_isin, cd_account, is_btc_operation, is_with_change_ownership,
227id_destiny_instituition, cd_destiny_account, nm_company, id_distribuition, ds_specification, qt_ticker)
228SELECT id_cei_investor, tb_cei_custody_transfer_bkp.dt_reference, id_brokerage, cd_isin, cd_account, is_btc_operation, is_with_change_ownership,
229    id_destiny_instituition, cd_destiny_account, nm_company, id_distribuition, ds_specification, qt_ticker
230FROM tb_cei_custody_transfer_bkp
231INNER JOIN tb_cei_investors ON tb_cei_custody_transfer_bkp.id_investor = tb_cei_investors.id_investor;
232
233
234--------------------------------------------------------------------------------------------------------------------------------------------------------------------
235-- 7. tb_cei_btc_instruments_deleted - OK (O number da tb_cei_brokerage_accounts tem hífen nas contas, na btc_instruments o cd_account é sem)
236--------------------------------------------------------------------------------------------------------------------------------------------------------------------
237ALTER TABLE tb_cei_btc_instruments_deleted RENAME TO tb_cei_btc_instruments_deleted_bkp;
238CREATE TABLE tb_cei_btc_instruments_deleted (
239    id_btc_instruments BIGINT,
240    id_cei_investor BIGINT,
241    id_cei_brokerage_account BIGINT,
242    id_brokerage INTEGER,
243    dt_reference INTEGER,
244    cd_loan_agreement INTEGER,
245    dt_register INTEGER,
246    dt_expiration INTEGER,
247    cd_ticker VARCHAR,
248    cd_isin VARCHAR,
249    qt_instrument INTEGER,
250    vl_reference_price NUMERIC(21,8),
251    vl_financial_amount NUMERIC(21,8),
252    ds_character_instrument VARCHAR,
253    vl_taker_rate NUMERIC(21,8),
254    vl_donor_rate NUMERIC(21,8),
255    vl_commission_taker NUMERIC(21,8),
256    vl_commission_donor NUMERIC(21,8),
257    ds_allows_early_sales_off BOOLEAN
258);
259--> OK, tabela de logs de deleção
260INSERT INTO tb_cei_btc_instruments_deleted (id_btc_instruments, id_cei_investor, id_cei_brokerage_account,dt_reference,cd_loan_agreement,dt_register,dt_expiration, cd_ticker, cd_isin, qt_instrument, vl_reference_price,
261    vl_financial_amount, ds_character_instrument, vl_taker_rate, vl_donor_rate, vl_commission_taker, vl_commission_donor,ds_allows_early_sales_off, id_brokerage)
262SELECT id_btc_instruments, tb_cei_investors.id_cei_investor, id_cei_brokerage_account,tb_cei_btc_instruments_deleted_bkp.dt_reference,cd_loan_agreement,dt_register,dt_expiration, cd_ticker, cd_isin, qt_instrument, vl_reference_price,
263    vl_financial_amount, ds_character_instrument, vl_taker_rate, vl_donor_rate, vl_commission_taker, vl_commission_donor,ds_allows_early_sales_off, id_brokerage
264FROM tb_cei_btc_instruments_deleted_bkp
265INNER JOIN tb_cei_investors ON tb_cei_btc_instruments_deleted_bkp.id_investor = tb_cei_investors.id_investor
266INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
267    AND tb_cei_btc_instruments_deleted_bkp.cd_account = replace(tb_cei_brokerage_accounts.number, '-', '') ;
268
269
270--------------------------------------------------------------------------------------------------------------------------------------------------------------------
271-- 8. tb_cei_investors_deleted - OK
272--------------------------------------------------------------------------------------------------------------------------------------------------------------------
273ALTER TABLE tb_cei_investors_deleted RENAME TO tb_cei_investors_deleted_bkp;
274CREATE TABLE tb_cei_investors_deleted (
275    id_cei_investor BIGINT,
276    id_investor INTEGER,
277    username BIGINT,
278    password VARCHAR,
279    downloaded TIMESTAMP,
280    sincronized BOOLEAN,
281    cd_status VARCHAR,
282    dt_reference INTEGER,
283    in_initial_import BOOLEAN,
284    success_count INTEGER,
285    priority INTEGER,
286    use_validation BOOLEAN,
287    dt_reference_resync INTEGER,
288    dh_deleted TIMESTAMP WITHOUT TIME ZONE,
289    application_name VARCHAR,
290    usename VARCHAR
291);
292--> OK, tabela de logs de deleção
293INSERT INTO tb_cei_investors_deleted (id_investor, username, password, downloaded, sincronized, cd_status, dt_reference,
294    in_initial_import, success_count, priority, use_validation, dt_reference_resync, dh_deleted, application_name, usename)
295SELECT id, TO_NUMBER(username, '999999999999999999')::BIGINT, password, downloaded, sincronized, cd_status, dt_reference,
296    in_initial_import, success_count, priority, use_validation, dt_reference_resync, dh_deleted, application_name, usename
297FROM tb_cei_investors_deleted_bkp;
298-->> function que insere na tb_cei_investors_deleted
299CREATE OR REPLACE FUNCTION public.fc_trigger_delete_tb_cei_investors()
300 RETURNS trigger
301 LANGUAGE plpgsql
302AS $function$
303BEGIN
304    INSERT INTO tb_cei_investors_deleted(id_cei_investor,id_investor,username,password,downloaded,sincronized,cd_status,dt_reference,in_initial_import,success_count,priority,use_validation,
305	dt_reference_resync,dh_deleted,application_name,usename)
306    VALUES (
307        old.id_cei_investor, old.id_investor, old.username, old.password,
308        old.downloaded, old.sincronized, old.cd_status, old.dt_reference,
309        old.in_initial_import, old.success_count, old.priority, old.use_validation, old.dt_reference_resync,
310        current_timestamp, (SELECT application_name::VARCHAR FROM pg_stat_activity WHERE pid = pg_backend_pid()),
311	(SELECT usename::VARCHAR FROM pg_stat_activity WHERE pid = pg_backend_pid()));
312    RETURN old;
313end;
314$function$
315
316
317--------------------------------------------------------------------------------------------------------------------------------------------------------------------
318-- 9. tb_tm_reprocess_request_last_status - OK
319--------------------------------------------------------------------------------------------------------------------------------------------------------------------
320DROP TABLE IF EXISTS tb_tm_reprocess_request_last_status_new;
321CREATE TABLE tb_tm_reprocess_request_last_status_new (
322    id_cei_investor BIGINT NOT NULL,
323    id_request BIGINT NOT NULL,
324    cd_status VARCHAR NOT NULL,
325    ds_stack_trace VARCHAR
326);
327ALTER TABLE tb_tm_reprocess_request_last_status_new ADD CONSTRAINT tb_tm_reprocess_request_last_status_pk PRIMARY KEY (id_cei_investor);
328--> Usos da tabela:
329--> É usada em join pelo id_investor que será o id_cei_investor -> is360-backoffice
330--> É usado o campo de cd_status em consultas ( ex: cd_status IN ('ERRO', 'EXEP') -> is360-backoffice
331-- Como a chave primária é o id_cei_investor, acho que num deve ter problema de desempenho usando só ela
332INSERT INTO tb_tm_reprocess_request_last_status_new (id_cei_investor, id_request, cd_status, ds_stack_trace)
333    SELECT tb_cei_investors.id_cei_investor, tb_tm_reprocess_request_last_status.id_request, tb_tm_reprocess_request_last_status.cd_status, tb_tm_reprocess_request_last_status.ds_stack_trace
334    FROM tb_tm_reprocess_request_last_status
335    INNER JOIN tb_cei_investors USING (id_investor);
336
337ALTER TABLE tb_tm_reprocess_request_last_status RENAME TO tb_tm_reprocess_request_last_status_bkp;
338ALTER TABLE tb_tm_reprocess_request_last_status_new RENAME TO tb_tm_reprocess_request_last_status;
339
340
341--------------------------------------------------------------------------------------------------------------------------------------------------------------------
342-- 10. tb_tm_custody_portfolio - OK
343--------------------------------------------------------------------------------------------------------------------------------------------------------------------
344ALTER TABLE tb_tm_custody_portfolio RENAME TO tb_tm_custody_portfolio_bkp;
345-- !!! Essa tabela não será mais usada, pelas modificações nos projetos não há mais uso dela, porém irei verificar se é certo isso
346
347
348--------------------------------------------------------------------------------------------------------------------------------------------------------------------
349-- 11. tb_tm_portfolio - OK
350--------------------------------------------------------------------------------------------------------------------------------------------------------------------
351ALTER TABLE tb_tm_portfolio RENAME TO tb_tm_portfolio_bkp;
352CREATE TABLE tb_tm_portfolio (
353    id_tm_portfolio BIGINT NOT NULL,
354    id_cei_investor BIGSERIAL NOT NULL,
355    nm_portfolio VARCHAR--,
356    -- tp_tm_portfolio VARCHAR --é sempre igual (Essa coluna sempre tem o valor de 'CUSTODY' então será retirada)
357);
358ALTER TABLE tb_tm_portfolio ADD CONSTRAINT tb_tm_portfolio_pk PRIMARY KEY (id_tm_portfolio, id_cei_investor);
359--> Usos da tabela:
360--> É usada muito o campo id_cei_investor em WHEREs -> trademap-portal-ws
361CREATE INDEX tb_tm_portfolio_investor_idx ON tb_tm_portfolio (id_cei_investor);
362
363INSERT INTO tb_tm_portfolio (id_tm_portfolio, id_cei_investor, nm_portfolio)
364SELECT id_tm_portfolio, id_cei_investor, nm_portfolio
365FROM tb_tm_portfolio_bkp
366INNER JOIN tb_cei_investors ON tb_tm_portfolio_bkp.id_investor = tb_cei_investors.id_investor
367
368
369--------------------------------------------------------------------------------------------------------------------------------------------------------------------
370-- 12. tb_cei_status - OK
371--------------------------------------------------------------------------------------------------------------------------------------------------------------------
372ALTER TABLE tb_cei_status RENAME CONSTRAINT tb_cei_status_pk TO tb_cei_status_bkp_pk;
373ALTER TABLE tb_cei_status RENAME TO tb_cei_status_bkp;
374CREATE TABLE tb_cei_status (
375    id_cei_investor BIGINT NOT NULL,
376    status VARCHAR NOT NULL,
377    modified TIMESTAMP WITHOUT TIME ZONE NOT NULL,
378    error VARCHAR,
379    stacktrace TEXT
380);
381ALTER TABLE tb_cei_status ADD CONSTRAINT tb_cei_status_pk PRIMARY KEY (id_cei_investor);
382--> Usos da tabela:
383--> É usada em join o campo id_cei_investor -> trademap-portal-ws
384-- id_cei_investor é chave primária, não precisa de mais
385INSERT INTO tb_cei_status (id_cei_investor, status, modified, error, stacktrace)
386SELECT tb_cei_investors.id_cei_investor, tb_cei_status_bkp.status, tb_cei_status_bkp.modified, tb_cei_status_bkp.error, tb_cei_status_bkp.stacktrace
387FROM tb_cei_status_bkp
388INNER JOIN tb_cei_investors ON tb_cei_status_bkp.investor_id = tb_cei_investors.id_investor;
389
390
391--------------------------------------------------------------------------------------------------------------------------------------------------------------------
392-- 13. tb_cei_bonds - OK
393--------------------------------------------------------------------------------------------------------------------------------------------------------------------
394ALTER TABLE tb_cei_bonds RENAME TO tb_cei_bonds_bkp;
395CREATE TABLE tb_cei_bonds (
396    id_cei_bonds BIGSERIAL NOT NULL,
397    id_cei_investor BIGINT,
398    brokerage_id INTEGER,
399    nu_cetip_brokerage INTEGER,
400    brokerage_nm VARCHAR,
401    entry_date DATE,
402    cd_bond_type VARCHAR,
403    cd_title VARCHAR,
404    cd_issuer VARCHAR,
405    cd_index VARCHAR,
406    dt_issue DATE,
407    dt_maturity DATE,
408    qtty NUMERIC(23,8),
409    vl_applied NUMERIC(23,8)
410);
411ALTER TABLE tb_cei_bonds ADD CONSTRAINT tb_cei_bonds_pk PRIMARY KEY (id_cei_bonds);
412--> Usos da tabela:
413--> É usada em WHERE o campo id_cei_investor -> integrator-process
414CREATE INDEX tb_cei_bonds_investor_idx ON tb_cei_bonds (id_cei_investor);
415--> É usada em JOIN o campo brokerage_id -> integrator-process
416-- Não criei para esse caso, tem que verificar necessidade
417
418INSERT INTO tb_cei_bonds (id_cei_investor, entry_date, brokerage_nm, cd_bond_type, cd_title, cd_issuer, cd_index, dt_issue, dt_maturity, brokerage_id, nu_cetip_brokerage, qtty, vl_applied)
419SELECT tb_cei_investors.id_cei_investor, tb_cei_bonds_bkp.entry_date, tb_cei_bonds_bkp.brokerage_nm, tb_cei_bonds_bkp.cd_bond_type, tb_cei_bonds_bkp.cd_title, tb_cei_bonds_bkp.cd_issuer, tb_cei_bonds_bkp.cd_index,
420tb_cei_bonds_bkp.dt_issue, tb_cei_bonds_bkp.dt_maturity, tb_cei_bonds_bkp.brokerage_id, tb_cei_bonds_bkp.nu_cetip_brokerage, tb_cei_bonds_bkp.qtty, tb_cei_bonds_bkp.vl_applied
421FROM tb_cei_bonds_bkp
422INNER JOIN tb_cei_investors ON tb_cei_bonds_bkp.investor_id = tb_cei_investors.id_investor;
423
424
425--------------------------------------------------------------------------------------------------------------------------------------------------------------------
426-- 14. tb_investor_cei_bonds - OK
427--------------------------------------------------------------------------------------------------------------------------------------------------------------------
428ALTER TABLE tb_investor_cei_bonds RENAME TO tb_investor_cei_bonds_bkp;
429CREATE TABLE tb_investor_cei_bonds (
430    id_investor_cei_bonds BIGSERIAL NOT NULL,
431    id_cei_investor BIGINT NOT NULL,
432    id_brokerage INTEGER NOT NULL,
433    id_instrument INTEGER NOT NULL,
434    account_broker INTEGER NOT NULL,
435    dt_application INTEGER NOT NULL,
436    side CHAR(1) NOT NULL,
437    cd_bond_type VARCHAR,
438    cd_title VARCHAR,
439    cd_issuer VARCHAR,
440    cd_index VARCHAR,
441    dt_issue INTEGER,
442    dt_maturity INTEGER,
443    ds_origin_trade VARCHAR,
444    qtty NUMERIC(23,8),
445    vl_applied NUMERIC(23,8)
446);
447ALTER TABLE tb_investor_cei_bonds ADD CONSTRAINT tb_investor_cei_bonds_pk PRIMARY KEY (id_investor_cei_bonds);
448--> Usos da tabela:
449--> É usada em JOIN o campo (id_instrument, id_brokerage id_cei_investor) !!!
450CREATE INDEX tb_investor_cei_bonds_instrument_investor_idx ON tb_investor_cei_bonds (id_instrument, id_brokerage, id_cei_investor);
451--> Parcial INDEX para verificar custódia mais rápido: (diminui uma query que estava em 150ms+ para 15ms-)
452CREATE INDEX tb_investor_cei_bonds_investor_custody_idx ON tb_investor_cei_bonds (id_instrument, id_cei_investor) WHERE side = 'B';
453
454INSERT INTO tb_investor_cei_bonds (id_cei_investor, id_brokerage, id_instrument, account_broker, dt_application, side, cd_bond_type,
455    cd_title, cd_issuer, cd_index, dt_issue, dt_maturity, ds_origin_trade, qtty, vl_applied)
456SELECT tb_cei_investors.id_cei_investor, id_brokerage, id_instrument, account_broker, dt_application, side, cd_bond_type,
457    cd_title, cd_issuer, cd_index, dt_issue, dt_maturity, ds_origin_trade, qtty, vl_applied
458FROM tb_investor_cei_bonds_bkp
459INNER JOIN tb_cei_investors ON tb_investor_cei_bonds_bkp.id_investor = tb_cei_investors.id_investor;
460
461
462--------------------------------------------------------------------------------------------------------------------------------------------------------------------
463-- 15. tb_cei_crawling_data - OK
464--------------------------------------------------------------------------------------------------------------------------------------------------------------------
465ALTER TABLE tb_cei_crawling_data RENAME CONSTRAINT tb_cei_crawling_data_pk TO tb_cei_crawling_data_bkp_pk;
466ALTER SEQUENCE tb_cei_crawling_data_id_crawling_data_seq RENAME TO tb_cei_crawling_data_bkp_id_crawling_data_seq;
467ALTER TABLE tb_cei_crawling_data RENAME TO tb_cei_crawling_data_bkp;
468CREATE TABLE tb_cei_crawling_data (
469    id_crawling_data BIGSERIAL NOT NULL,
470    id_cei_investor BIGINT NOT NULL,
471    cd_page VARCHAR NOT NULL,
472    dh_insert TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
473    id_request BIGINT NULL,
474    dt_crawling DATE NOT NULL,
475    vl_data VARCHAR NOT NULL
476);
477ALTER TABLE tb_cei_crawling_data ADD CONSTRAINT tb_cei_crawling_data_pk PRIMARY KEY (id_crawling_data);
478--> Usos da tabela:
479-- É usado os campos id_request e cd_page em WHERE -> integrator-process
480CREATE INDEX tb_cei_crawling_data_request_idx ON tb_cei_crawling_data (id_request);
481
482INSERT INTO tb_cei_crawling_data  (id_crawling_data, id_cei_investor, cd_page, dh_insert, id_request, dt_crawling, vl_data)
483SELECT tb_cei_crawling_data_bkp.id_crawling_data, tb_cei_investors.id_cei_investor, tb_cei_crawling_data_bkp.cd_page, tb_cei_crawling_data_bkp.dh_insert,
484tb_cei_crawling_data_bkp.id_request, tb_cei_crawling_data_bkp.dt_crawling, tb_cei_crawling_data_bkp.vl_data
485FROM tb_cei_crawling_data_bkp
486INNER JOIN tb_cei_investors USING (id_investor);
487
488SELECT setval('tb_cei_crawling_data_id_crawling_data_seq'::regclass, (select MAX(id_crawling_data) FROM tb_cei_crawling_data));
489
490
491--------------------------------------------------------------------------------------------------------------------------------------------------------------------
492-- 16. tb_investor_broker_account -->> Não será modificada por enquanto, verificar o impacto (Se o planejado irá funcionar)
493--------------------------------------------------------------------------------------------------------------------------------------------------------------------
494--> !!! Tabela importante, não foi possível verificar todos os usos e acho que ela não deveria ser mexida.
495-- ALTER TABLE tb_investor_broker_account RENAME CONSTRAINT tb_investor_broker_account_pk TO tb_investor_broker_account_bkp_pk;
496-- ALTER TABLE tb_investor_broker_account RENAME TO tb_investor_broker_account_bkp;
497-- CREATE TABLE tb_investor_broker_account (
498--     id_investor_broker_account BIGSERIAL NOT NULL,
499--     id_contract INTEGER NOT NULL,
500--     id_cei_investor BIGINT NOT NULL,
501--     id_broker INTEGER NOT NULL,
502--     account_number BIGINT NOT NULL,
503--     tp_account SMALLINT NOT NULL,
504--     is_active_account BOOLEAN DEFAULT TRUE
505-- );
506-- ALTER TABLE tb_investor_broker_account ADD CONSTRAINT tb_investor_broker_account_pk PRIMARY KEY (id_investor_broker_account);
507-- ALTER TABLE tb_investor_broker_account ADD CONSTRAINT tb_investor_broker_account_uk UNIQUE (id_contract, id_cei_investor, id_broker, account_number);
508-- CREATE INDEX tb_investor_broker_account_id_cei_investor_idx ON tb_investor_broker_account (id_cei_investor);
509
510-- INSERT INTO tb_investor_broker_account (id_contract, id_cei_investor, id_broker, account_number, tp_account, is_active_account)
511-- SELECT id_contract, id_cei_investor, id_broker, account_number, tp_account, is_active_account
512-- FROM tb_investor_broker_account_bkp
513-- INNER JOIN tb_cei_investors ON tb_investor_broker_account_bkp.id_investor = tb_cei_investors.id_investor;
514
515
516--------------------------------------------------------------------------------------------------------------------------------------------------------------------
517-- 17. tb_cei_public_bonds - OK (Tem que verificar o insert, o join com o campo de number, pois as tabelas tem tipos diferentes > Essa e a tb_cei_brokerage_accounts)
518--------------------------------------------------------------------------------------------------------------------------------------------------------------------
519ALTER TABLE tb_cei_public_bonds RENAME TO tb_cei_public_bonds_bkp;
520CREATE TABLE tb_cei_public_bonds (
521    id_cei_public_bonds BIGSERIAL NOT NULL,
522    id_cei_investor BIGINT NOT NULL,
523    id_cei_brokerage_account BIGINT NOT NULL,
524    entry_date DATE NOT NULL,
525    nm_public_bond VARCHAR(50),
526    tp_public_bond VARCHAR(30),
527    vl_price_origin NUMERIC(23,8) NOT NULL,
528    vl_price_brute NUMERIC(23,8) NOT NULL,
529    vl_interest_rate NUMERIC(23,8) NOT NULL,
530    quantity NUMERIC(23,8) NOT NULL,
531    value NUMERIC(23,8) NOT NULL,
532    type_bond VARCHAR,
533    yy_maturity INTEGER,
534    dt_maturity INTEGER
535);
536ALTER TABLE tb_cei_public_bonds ADD CONSTRAINT tb_cei_public_bonds_pk PRIMARY KEY (id_cei_public_bonds);
537--> Usos da tabela:
538--> É usada em consultas/deletes pelo campo id_cei_investor -> integrator-process
539--> Tem usos no cei_summarize-comparation (JOIN, não consegui verificar direito) -> cei-summarize-comparation
540CREATE INDEX tb_cei_public_bonds_investor_idx ON tb_cei_public_bonds (id_cei_investor);
541
542INSERT INTO tb_cei_public_bonds (id_cei_investor, id_cei_brokerage_account, entry_date, nm_public_bond,tp_public_bond, vl_price_origin, vl_price_brute, vl_interest_rate, quantity, value, type_bond, yy_maturity, dt_maturity)
543SELECT tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account, entry_date, nm_public_bond,tp_public_bond, vl_price_origin, vl_price_brute, vl_interest_rate,
544quantity, value, type_bond, yy_maturity, dt_maturity
545FROM tb_cei_public_bonds_bkp
546INNER JOIN tb_cei_investors ON tb_cei_public_bonds_bkp.investor_id = tb_cei_investors.id_investor
547INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor AND tb_cei_public_bonds_bkp.account_id = (replace(tb_cei_brokerage_accounts.number, '-', ''))::INTEGER;
548
549
550--------------------------------------------------------------------------------------------------------------------------------------------------------------------
551-- 18. tb_investor_cei_account - OK (Mas verificar sobre as colunas que existem hoje em produção)
552--------------------------------------------------------------------------------------------------------------------------------------------------------------------
553ALTER TABLE tb_investor_cei_account RENAME CONSTRAINT tb_investor_cei_account_pk TO tb_investor_cei_account_bkp_pk;
554DROP TABLE IF EXISTS tb_investor_cei_account_new;
555CREATE TABLE tb_investor_cei_account_new (
556    id_cei_account BIGSERIAL NOT NULL,
557    id_cei_investor BIGINT NOT NULL,
558    id_contract INTEGER NOT NULL,
559    id_brokerage INTEGER NOT NULL,
560    account_broker INTEGER NOT NULL,
561    account_number BIGINT NOT NULL,
562    tp_account VARCHAR(1),
563    nm_account VARCHAR(100)
564);
565ALTER TABLE tb_investor_cei_account_new ADD CONSTRAINT tb_investor_cei_account_pk PRIMARY KEY (id_cei_account);
566ALTER TABLE tb_investor_cei_account_new ADD CONSTRAINT tb_investor_cei_account_uk UNIQUE (id_cei_investor, id_contract, id_brokerage, account_broker, account_number);
567
568-- Em produção tem essas colunas: nm_account, id_fund, id_portfolio, account_portfolio
569-- (No script não tem essas colunas, e em produção não há nenhum com esses campos preenchidos na tb_investor_cei_account)
570INSERT INTO tb_investor_cei_account_new (id_contract, id_cei_investor, id_brokerage, account_broker, account_number, tp_account)
571    SELECT tb_investor_cei_account.id_contract, tb_cei_investors.id_cei_investor, tb_investor_cei_account.id_brokerage,
572    tb_investor_cei_account.account_broker, tb_investor_cei_account.account_number, tb_investor_cei_account.tp_account
573    FROM tb_investor_cei_account
574    INNER JOIN tb_cei_investors USING (id_investor);
575
576ALTER TABLE tb_investor_cei_account RENAME TO tb_investor_cei_account_bkp;
577ALTER TABLE tb_investor_cei_account_new RENAME TO tb_investor_cei_account;
578
579--> Usos da tabela:
580--> Consultas e deletes pelo id_cei_investor ou (id_cei_investor,account_number) -> integrator-process e carteira-ws
581CREATE INDEX tb_investor_cei_account_investor_idx ON tb_investor_cei_account (id_cei_investor); -- Criei só para o id_cei_investor, deve abranger mais casos usando só esse campo
582
583
584--------------------------------------------------------------------------------------------------------------------------------------------------------------------
585-- 19. tb_cei_term_assets - OK
586--------------------------------------------------------------------------------------------------------------------------------------------------------------------
587ALTER TABLE tb_cei_term_assets RENAME TO tb_cei_term_assets_bkp;
588CREATE TABLE tb_cei_term_assets (
589    id_cei_term_assets BIGSERIAL NOT NULL,
590    id_cei_investor BIGINT NOT NULL,
591    id_cei_brokerage_account BIGINT NOT NULL,
592    entry_date DATE NOT NULL,
593    id_term_contract INTEGER NOT NULL,
594    company VARCHAR NOT NULL,
595    type VARCHAR NOT NULL,
596    cd_ticker VARCHAR NOT NULL,
597    isin VARCHAR NOT NULL,
598    side VARCHAR NOT NULL,
599    qtty NUMERIC(13,4) NOT NULL,
600    price NUMERIC(13,4) NOT NULL,
601    value NUMERIC(13,4) NOT NULL,
602    dt_matutirity INTEGER NOT NULL,
603    distribution INTEGER NOT NULL
604);
605ALTER TABLE tb_cei_term_assets ADD CONSTRAINT tb_cei_term_assets_pk PRIMARY KEY (id_cei_term_assets);
606-- OK, porém estranho. Foi encontrado em todos os projetos do trademap somente um uso de INSERT nela. (Nenhum outro projeto lugar consulta ela? Pra que serve?)
607INSERT INTO tb_cei_term_assets (id_cei_investor, id_cei_brokerage_account,entry_date, id_term_contract, company, type, cd_ticker, isin, side, qtty, price, value, dt_matutirity, distribution)
608SELECT tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account,entry_date, id_term_contract, company, type, cd_ticker, isin, side, qtty, price, value, dt_matutirity, distribution
609FROM tb_cei_term_assets_bkp
610INNER JOIN tb_cei_investors ON tb_cei_term_assets_bkp.id_investor = tb_cei_investors.id_investor
611INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
612    AND tb_cei_term_assets_bkp.account = tb_cei_brokerage_accounts.id_cei_brokerage_account;
613
614CREATE INDEX tb_cei_term_assets_entry_dt_idx ON tb_cei_term_assets (id_cei_investor, id_cei_brokerage_account, entry_date);
615
616
617--------------------------------------------------------------------------------------------------------------------------------------------------------------------
618-- 20. tb_cei_quarantine_investor - OK
619--------------------------------------------------------------------------------------------------------------------------------------------------------------------
620ALTER TABLE tb_cei_quarantine_investor RENAME TO tb_cei_quarantine_investor_bkp;
621CREATE TABLE tb_cei_quarantine_investor (
622    id_quarantine_investor BIGSERIAL NOT NULL,
623    id_cei_investor BIGINT NOT NULL,
624    ds_reason VARCHAR NOT NULL,
625    cd_status VARCHAR NOT NULL,
626    id_request INTEGER NOT NULL,
627    dh_insertion TIMESTAMP WITHOUT TIME ZONE  NOT NULL DEFAULT NOW(),
628    id_user INTEGER,
629    dh_release TIMESTAMP WITHOUT TIME ZONE
630);
631ALTER TABLE tb_cei_quarantine_investor ADD CONSTRAINT tb_cei_quarantine_investor_pk PRIMARY KEY (id_quarantine_investor);
632--> Usos da tabela:
633--> Consultas pelo id_request -> integrator-process
634CREATE INDEX tb_cei_quarantine_investor_request_idx ON tb_cei_quarantine_investor (id_request);
635
636INSERT INTO tb_cei_quarantine_investor (id_quarantine_investor, id_cei_investor, ds_reason, cd_status, id_request, dh_insertion, id_user, dh_release)
637SELECT tb_cei_quarantine_investor_bkp.id_quarantine_investor, tb_cei_investors.id_cei_investor, tb_cei_quarantine_investor_bkp.ds_reason,
638tb_cei_quarantine_investor_bkp.cd_status, tb_cei_quarantine_investor_bkp.id_request, tb_cei_quarantine_investor_bkp.dh_insertion, tb_cei_quarantine_investor_bkp.id_user, dh_release
639FROM tb_cei_quarantine_investor_bkp
640INNER JOIN tb_cei_investors ON tb_cei_quarantine_investor_bkp.id_investor = tb_cei_investors.id_investor;
641SELECT setval('tb_cei_quarantine_investor_id_quarantine_investor_seq'::regclass, (select MAX(id_quarantine_investor ) FROM tb_cei_quarantine_investor));
642
643
644--------------------------------------------------------------------------------------------------------------------------------------------------------------------
645-- 21. tb_investor_cei_public_bonds - OK
646--------------------------------------------------------------------------------------------------------------------------------------------------------------------
647ALTER TABLE tb_investor_cei_public_bonds RENAME TO tb_investor_cei_public_bonds_bkp;
648CREATE TABLE tb_investor_cei_public_bonds (
649    id_public_bonds BIGSERIAL NOT NULL,
650    id_cei_investor BIGINT NOT NULL,
651    id_contract INTEGER NOT NULL,
652    id_brokerage INTEGER NOT NULL,
653    account_number BIGINT NOT NULL,
654    account_broker INTEGER NOT NULL,
655    tp_public_bond VARCHAR(20),
656    nm_public_bond VARCHAR(80),
657    dt_application INTEGER,
658    id_instrument INTEGER,
659    cd_isin VARCHAR(20),
660    dt_maturity INTEGER,
661    vl_price_origin NUMERIC(23,8),
662    vl_price_brute NUMERIC(23,8),
663    vl_interest_rate NUMERIC (23,8),
664    quantity NUMERIC (23,8),
665    vl_price_bond NUMERIC(23,8),
666    ds_origin_trade VARCHAR,
667    side CHAR(1),
668    id_public_bonds_base INTEGER
669);
670ALTER TABLE tb_investor_cei_public_bonds ADD CONSTRAINT tb_investor_cei_public_bonds_pk PRIMARY KEY (id_public_bonds);
671--> Usos da tabela:
672--> Consultas pelo id_cei_investor -> trademap-portal-ws
673CREATE INDEX tb_investor_cei_public_bonds_investor_idx ON tb_investor_cei_public_bonds (id_cei_investor);
674--> Usado id_instrument em JOINs -> carteira-ws
675-- Não fiz indice pra esse caso, verificar necessidade
676INSERT INTO tb_investor_cei_public_bonds (id_public_bonds, id_cei_investor, id_contract, id_brokerage, account_number, account_broker,
677    tp_public_bond, nm_public_bond, dt_application, id_instrument, cd_isin, dt_maturity, vl_price_origin, vl_price_brute,
678    vl_interest_rate, quantity, vl_price_bond, ds_origin_trade, side, id_public_bonds_base)
679SELECT seq, tb_cei_investors.id_cei_investor, id_contract, id_brokerage, account_number, account_broker,
680    tp_public_bond, nm_public_bond, dt_application, id_instrument, cd_isin, dt_maturity, vl_price_origin, vl_price_brute,
681    vl_interest_rate, quantity, vl_price_bond, ds_origin_trade, side, seq_base
682FROM tb_investor_cei_public_bonds_bkp
683INNER JOIN tb_cei_investors ON tb_investor_cei_public_bonds_bkp.id_investor = tb_cei_investors.id_investor;
684SELECT setval('"public"."tb_investor_cei_public_bonds_id_public_bonds_seq"'::regclass, (select MAX("id_public_bonds") FROM "public"."tb_investor_cei_public_bonds"));
685
686
687--------------------------------------------------------------------------------------------------------------------------------------------------------------------
688-- 22. tb_cei_executor_request_last - OK
689--------------------------------------------------------------------------------------------------------------------------------------------------------------------
690DROP TABLE IF EXISTS tb_cei_executor_request_last_new;
691CREATE TABLE tb_cei_executor_request_last_new (
692    id_cei_investor BIGINT NOT NULL,
693    id_request BIGINT NOT NULL,
694    cd_status VARCHAR NOT NULL,
695    stacktrace VARCHAR NULL
696);
697ALTER TABLE tb_cei_executor_request_last_new ADD CONSTRAINT tb_cei_executor_request_last_pk PRIMARY KEY (id_cei_investor);
698
699INSERT INTO tb_cei_executor_request_last_new
700    SELECT tb_cei_investors.id_cei_investor, tb_cei_executor_request_last.id_request,
701    tb_cei_executor_request_last.cd_status, tb_cei_executor_request_last.stacktrace
702    FROM tb_cei_executor_request_last
703    INNER JOIN tb_cei_investors USING (id_investor);
704
705ALTER TABLE tb_cei_executor_request_last RENAME TO tb_cei_executor_request_last_bkp;
706ALTER TABLE tb_cei_executor_request_last_new RENAME TO tb_cei_executor_request_last;
707--> Usos da tabela:
708-- Usa o id_request ou o id_cei_investor em JOIN -> carteira-ws
709CREATE INDEX tb_cei_executor_request_last_request_idx ON tb_cei_executor_request_last (id_request); -- PK é o id_cei_investor não precisa de outro índice
710
711
712--------------------------------------------------------------------------------------------------------------------------------------------------------------------
713-- 23. tb_cei_bonds_deleted - OK
714--------------------------------------------------------------------------------------------------------------------------------------------------------------------
715ALTER TABLE tb_cei_bonds_deleted RENAME TO tb_cei_bonds_deleted_bkp;
716CREATE TABLE tb_cei_bonds_deleted (
717    id_cei_bonds BIGSERIAL NOT NULL,
718    id_cei_investor BIGINT,
719    brokerage_id INTEGER,
720    nu_cetip_brokerage INTEGER,
721    brokerage_nm VARCHAR,
722    entry_date DATE,
723    cd_bond_type VARCHAR,
724    cd_title VARCHAR,
725    cd_issuer VARCHAR,
726    cd_index VARCHAR,
727    dt_issue DATE,
728    dt_maturity DATE,
729    qtty NUMERIC(23,8),
730    vl_applied NUMERIC(23,8)
731);
732-- OK, tabela usada para logs de deleção
733INSERT INTO tb_cei_bonds_deleted (id_cei_bonds, entry_date, id_cei_investor, brokerage_nm, cd_bond_type, cd_title, cd_issuer, cd_index, dt_issue, dt_maturity, brokerage_id, nu_cetip_brokerage, qtty, vl_applied)
734SELECT tb_cei_bonds_deleted_bkp.id, tb_cei_bonds_deleted_bkp.entry_date, tb_cei_investors.id_cei_investor, tb_cei_bonds_deleted_bkp.brokerage_nm, tb_cei_bonds_deleted_bkp.cd_bond_type, tb_cei_bonds_deleted_bkp.cd_title, tb_cei_bonds_deleted_bkp.cd_issuer, tb_cei_bonds_deleted_bkp.cd_index, tb_cei_bonds_deleted_bkp.dt_issue, tb_cei_bonds_deleted_bkp.dt_maturity, tb_cei_bonds_deleted_bkp.brokerage_id, tb_cei_bonds_deleted_bkp.nu_cetip_brokerage, tb_cei_bonds_deleted_bkp.qtty, vl_applied
735FROM tb_cei_bonds_deleted_bkp
736INNER JOIN tb_cei_investors ON tb_cei_bonds_deleted_bkp.investor_id = tb_cei_investors.id_investor;
737
738
739--------------------------------------------------------------------------------------------------------------------------------------------------------------------
740-- 24. tb_cei_option_assets - OK
741--------------------------------------------------------------------------------------------------------------------------------------------------------------------
742ALTER TABLE tb_cei_option_assets RENAME TO tb_cei_option_assets_bkp;
743ALTER TABLE tb_cei_option_assets_bkp RENAME CONSTRAINT tb_cei_option_assets_pkey TO tb_cei_option_assets_bkp_pk;
744CREATE TABLE tb_cei_option_assets (
745    id_cei_option_assets BIGSERIAL NOT NULL,
746    id_cei_investor BIGINT NOT NULL,
747    id_cei_brokerage_account BIGINT NOT NULL,
748    entry_date DATE NOT NULL,
749    company VARCHAR NOT NULL,
750    type VARCHAR NOT NULL,
751    style VARCHAR(1) NOT NULL,
752    cd_ticker VARCHAR NOT NULL,
753    dt_matutirity INTEGER NOT NULL,
754    position VARCHAR NOT NULL,
755    condition VARCHAR NOT NULL,
756    qtty_covered INTEGER NOT NULL,
757    qtty_uncovered INTEGER NOT NULL,
758    price NUMERIC(13,4) NOT NULL,
759    value NUMERIC(13,4) NOT NULL
760);
761ALTER TABLE tb_cei_option_assets ADD CONSTRAINT tb_cei_option_assets_pk PRIMARY KEY (id_cei_option_assets);
762-- OK, porém estranho. Foi encontrado em todos os projetos do trademap somente um uso de INSERT nela. (Nenhum outro projeto lugar consulta ela? Pra que serve?)
763INSERT INTO tb_cei_option_assets (id_cei_investor, id_cei_brokerage_account, dt_matutirity, entry_date, company, type, cd_ticker, style,
764position, condition, qtty_covered, qtty_uncovered, price, value)
765SELECT tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account, tb_cei_option_assets_bkp.dt_matutirity, tb_cei_option_assets_bkp.entry_date,
766tb_cei_option_assets_bkp.company, tb_cei_option_assets_bkp.type, tb_cei_option_assets_bkp.cd_ticker, tb_cei_option_assets_bkp.style, tb_cei_option_assets_bkp.position,
767tb_cei_option_assets_bkp.condition, tb_cei_option_assets_bkp.qtty_covered, tb_cei_option_assets_bkp.qtty_uncovered, tb_cei_option_assets_bkp.price, value
768FROM tb_cei_option_assets_bkp
769INNER JOIN tb_cei_investors ON tb_cei_option_assets_bkp.id_investor = tb_cei_investors.id_investor
770INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
771    AND tb_cei_option_assets_bkp.account = tb_cei_brokerage_accounts.id_cei_brokerage_account;
772
773
774--------------------------------------------------------------------------------------------------------------------------------------------------------------------
775-- 25. tb_cei_btc_instruments - OK (Mas o campo cd_loan_agreement só aceitou ser do tipo decimal - Corrigido campo id_btc_instruments)
776--------------------------------------------------------------------------------------------------------------------------------------------------------------------
777ALTER TABLE tb_cei_btc_instruments RENAME TO tb_cei_btc_instruments_bkp;
778ALTER TABLE tb_cei_btc_instruments_bkp RENAME CONSTRAINT tb_cei_btc_instruments_pkey TO tb_cei_btc_instruments_bkp_pk;
779CREATE TABLE tb_cei_btc_instruments (
780    id_btc_instruments BIGSERIAL NOT NULL,
781    id_cei_investor BIGINT NOT NULL,
782    id_cei_brokerage_account BIGINT NOT NULL,
783    id_brokerage INTEGER,
784    dt_reference INTEGER NOT NULL,
785    cd_loan_agreement DECIMAL NOT NULL,
786    dt_register INTEGER,
787    dt_expiration INTEGER,
788    cd_ticker VARCHAR NOT NULL,
789    cd_isin VARCHAR NOT NULL,
790    qt_instrument INTEGER NOT NULL,
791    vl_reference_price NUMERIC(21,8),
792    vl_financial_amount NUMERIC(21,8),
793    ds_character_instrument VARCHAR,
794    vl_taker_rate NUMERIC(21,8),
795    vl_donor_rate NUMERIC(21,8),
796    vl_commission_taker NUMERIC(21,8),
797    vl_commission_donor NUMERIC(21,8),
798    ds_allows_early_sales_off BOOLEAN
799);
800ALTER TABLE tb_cei_btc_instruments ADD CONSTRAINT tb_cei_btc_instruments_pk PRIMARY KEY (id_btc_instruments);
801CREATE INDEX tb_cei_btc_instruments_account_dt_reference_idx ON tb_cei_btc_instruments (id_cei_investor, dt_reference);
802--> Usos da tabela:
803-- Uso do cd_ticker ou (id_brokerage, dt_reference) em JOINs -> carteira-ws e integrator-process
804-- Não criei índice, talvez não tenha necessidade e esse anterior já satisfaça
805INSERT INTO tb_cei_btc_instruments (id_btc_instruments, id_cei_investor, id_cei_brokerage_account,dt_reference,cd_loan_agreement,dt_register,dt_expiration, cd_ticker, cd_isin, qt_instrument, vl_reference_price,
806    vl_financial_amount, ds_character_instrument, vl_taker_rate, vl_donor_rate, vl_commission_taker, vl_commission_donor,ds_allows_early_sales_off, id_brokerage)
807SELECT tb_cei_btc_instruments_bkp.id_btc_instruments, tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account,tb_cei_btc_instruments_bkp.dt_reference,cd_loan_agreement,dt_register,dt_expiration, cd_ticker, cd_isin, qt_instrument, vl_reference_price,
808    vl_financial_amount, ds_character_instrument, vl_taker_rate, vl_donor_rate, vl_commission_taker, vl_commission_donor,ds_allows_early_sales_off, id_brokerage
809FROM tb_cei_btc_instruments_bkp
810INNER JOIN tb_cei_investors ON tb_cei_btc_instruments_bkp.id_investor = tb_cei_investors.id_investor
811INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
812    AND tb_cei_btc_instruments_bkp.cd_account = tb_cei_brokerage_accounts.number;
813
814
815--------------------------------------------------------------------------------------------------------------------------------------------------------------------
816-- 26. tb_tm_reprocess_request - OK (Mas verificar esse join do insert, está certo?)
817--------------------------------------------------------------------------------------------------------------------------------------------------------------------
818DROP TABLE IF EXISTS tb_tm_reprocess_request_new;
819CREATE TABLE tb_tm_reprocess_request_new (
820    id_request BIGSERIAL NOT NULL,
821    id_cei_investor BIGINT NOT NULL,
822    dh_insert TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
823    cd_status VARCHAR NOT NULL DEFAULT 'PEND',
824    dt_reprocess_start INTEGER,
825    dt_reprocess_end INTEGER NOT NULL,
826    dh_message_sent TIMESTAMP WITHOUT TIME ZONE,
827    send_reprocess BOOLEAN,
828    stacktrace VARCHAR,
829    reimport_trades BOOLEAN DEFAULT FALSE,
830    id_cei_executor_request INTEGER,
831    cd_origin VARCHAR
832);
833ALTER TABLE tb_tm_reprocess_request_new ADD CONSTRAINT tb_tm_reprocess_request_pk PRIMARY KEY (id_request);
834
835INSERT INTO tb_tm_reprocess_request_new (id_cei_investor, dh_insert, cd_status, dt_reprocess_start, dt_reprocess_end,
836    dh_message_sent, send_reprocess, stacktrace, reimport_trades, id_cei_executor_request, cd_origin)
837    SELECT tb_cei_investors.id_cei_investor, tb_tm_reprocess_request.dh_insert, tb_tm_reprocess_request.cd_status, tb_tm_reprocess_request.dt_reprocess_start,
838    tb_tm_reprocess_request.dt_reprocess_end, dh_message_sent, send_reprocess, stacktrace, reimport_trades, id_cei_executor_request, cd_origin
839    FROM tb_tm_reprocess_request_last_status
840    INNER JOIN tb_tm_reprocess_request USING (id_request)
841    INNER JOIN tb_cei_investors ON tb_cei_investors.id_investor = tb_tm_reprocess_request.id_investor;
842
843ALTER TABLE tb_tm_reprocess_request RENAME TO tb_tm_reprocess_request_bkp;
844ALTER TABLE tb_tm_reprocess_request_new RENAME TO tb_tm_reprocess_request;
845DROP INDEX IF EXISTS tb_tm_reprocess_request_cd_status_idx;
846DROP INDEX IF EXISTS tb_tm_reprocess_request_id_investor_idx;
847CREATE INDEX tb_tm_reprocess_request_cd_status_idx ON tb_tm_reprocess_request (cd_status);
848CREATE INDEX tb_tm_reprocess_request_id_cei_investor_idx ON tb_tm_reprocess_request (id_cei_investor);
849-- OK, os índices acima já satisfazem
850
851
852--------------------------------------------------------------------------------------------------------------------------------------------------------------------
853-- 27. tb_cei_public_bonds_deleted - OK (Demorou uns minutos em comparação com os outros pra fazer o INSERT)
854--------------------------------------------------------------------------------------------------------------------------------------------------------------------
855ALTER TABLE tb_cei_public_bonds_deleted RENAME TO tb_cei_public_bonds_deleted_bkp;
856CREATE TABLE tb_cei_public_bonds_deleted (
857    id_cei_public_bonds BIGSERIAL NOT NULL,
858    id_cei_investor BIGINT NOT NULL,
859    id_cei_brokerage_account BIGINT NOT NULL,
860    entry_date DATE NOT NULL,
861    nm_public_bond VARCHAR(50),
862    tp_public_bond VARCHAR(30),
863    vl_price_origin NUMERIC(23,8) NOT NULL,
864    vl_price_brute NUMERIC(23,8) NOT NULL,
865    vl_interest_rate NUMERIC(23,8) NOT NULL,
866    quantity NUMERIC(23,8) NOT NULL,
867    value NUMERIC(23,8) NOT NULL,
868    type_bond VARCHAR,
869    yy_maturity INTEGER,
870    dt_maturity INTEGER
871);
872-- OK, tabela usada para logs de deleção
873INSERT INTO tb_cei_public_bonds_deleted (id_cei_public_bonds, id_cei_investor, id_cei_brokerage_account, entry_date, nm_public_bond,tp_public_bond, vl_price_origin, vl_price_brute, vl_interest_rate, quantity, value, type_bond, yy_maturity, dt_maturity)
874SELECT id, tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account, entry_date, nm_public_bond,tp_public_bond, vl_price_origin, vl_price_brute, vl_interest_rate, quantity, value, type_bond, yy_maturity, dt_maturity
875FROM tb_cei_public_bonds_deleted_bkp
876INNER JOIN tb_cei_investors ON tb_cei_public_bonds_deleted_bkp.investor_id = tb_cei_investors.id_investor
877INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
878    AND tb_cei_public_bonds_deleted_bkp.account_id = (replace(tb_cei_brokerage_accounts.number, '-', ''))::INTEGER;
879
880
881--------------------------------------------------------------------------------------------------------------------------------------------------------------------
882-- 28. tb_investor_cei_trade - OK (Também demorou uns minutos - muito mais que a anterior)
883--------------------------------------------------------------------------------------------------------------------------------------------------------------------
884ALTER TABLE tb_investor_cei_trade RENAME TO tb_investor_cei_trade_bkp;
885ALTER TABLE tb_investor_cei_trade_bkp RENAME CONSTRAINT tb_investor_cei_trade_pk TO tb_investor_cei_trade_bkp_pk;
886CREATE TABLE tb_investor_cei_trade (
887    id_investor_cei_trade BIGSERIAL NOT NULL,
888    id_cei_investor BIGINT NOT NULL,
889    account_number BIGINT NOT NULL,
890    account_broker INTEGER NOT NULL,
891    id_brokerage INTEGER NOT NULL,
892    dt_trade INTEGER NOT NULL,
893    id_stock INTEGER NOT NULL,
894    cd_stock VARCHAR(30),
895    id_instrument BIGINT,
896    side VARCHAR(1),
897    quantity INTEGER,
898    price NUMERIC(13,4),
899    vl_total NUMERIC(13,4),
900    is_pend_wizard BOOLEAN,
901    is_processed BOOLEAN,
902    is_virtual_trade BOOLEAN,
903    cd_status VARCHAR(20),
904    ds_origin_trade VARCHAR
905);
906ALTER TABLE tb_investor_cei_trade ADD CONSTRAINT tb_investor_cei_trade_pk PRIMARY KEY (id_investor_cei_trade);
907CREATE INDEX tb_investor_cei_trade_instrument_idx ON tb_investor_cei_trade (id_cei_investor, id_brokerage, id_instrument, dt_trade);
908CREATE INDEX tb_investor_cei_trade_stock_idx ON tb_investor_cei_trade (cd_stock);
909--> Usos da tabela:
910-- JOIN pelo id_brokerage ou cd_stock -> integrator-process
911-- Não criei índice, deve verificar a necessidade um dos índices criados já pode atender esse caso
912INSERT INTO tb_investor_cei_trade (id_cei_investor, account_number, account_broker, id_brokerage, dt_trade, id_stock, cd_stock,
913id_instrument, side, quantity, price, vl_total, is_pend_wizard, is_processed, is_virtual_trade, cd_status, ds_origin_trade)
914SELECT tb_cei_investors.id_cei_investor, account_number, account_broker, id_brokerage, dt_trade, id_stock, cd_stock,
915id_instrument, side, quantity, price, vl_total, is_pend_wizard, is_processed, is_virtual_trade, tb_investor_cei_trade_bkp.cd_status, ds_origin_trade
916FROM tb_investor_cei_trade_bkp
917INNER JOIN tb_cei_investors ON tb_investor_cei_trade_bkp.id_investor = tb_cei_investors.id_investor;
918
919
920--------------------------------------------------------------------------------------------------------------------------------------------------------------------
921-- 29. tb_tm_portfolio_operation - OK (Demorou 11 minutos)
922--------------------------------------------------------------------------------------------------------------------------------------------------------------------
923ALTER TABLE tb_tm_portfolio_operation RENAME TO tb_tm_portfolio_operation_bkp;
924CREATE TABLE tb_tm_portfolio_operation (
925    id_operation BIGSERIAL NOT NULL,
926    id_tm_portfolio INTEGER NOT NULL,
927    id_cei_investor BIGINT NOT NULL,
928    id_cei_brokerage_account BIGINT,
929    tp_operation VARCHAR,
930    dt_operation INTEGER,
931    id_instrument INTEGER,
932    id_institution INTEGER,
933    side CHAR(1),
934    qtty NUMERIC(23,8),
935    price NUMERIC(23,8),
936    dt_maturity INTEGER,
937    id_index VARCHAR,
938    pc_index NUMERIC(5,2),
939    pc_coupon NUMERIC(5,2),
940    cd_bond_type VARCHAR,
941    issuer VARCHAR,
942    cd_status VARCHAR(30),
943    ds_origin_trade VARCHAR,
944    is_virtual_trade BOOLEAN,
945    already_imported BOOLEAN DEFAULT false,
946    id_public_bonds INTEGER,
947    id_public_bonds_base INTEGER
948);
949ALTER TABLE tb_tm_portfolio_operation ADD CONSTRAINT tb_tm_portfolio_operation_pk PRIMARY KEY (id_operation);
950--> Usos da tabela:
951-- Muitos usos do id_cei_investor em consultas e JOINs -> integrator-process (Tem que revisar o processo de carteira-ws tbm, pois novos códigos foram adicionados usando o id_investor, chave antiga)
952CREATE INDEX tb_tm_portfolio_operation_investor_idx ON tb_tm_portfolio_operation (id_cei_investor);
953
954-- Isso vai inserir todas as operações que não tem o campo id_account nulo
955INSERT INTO tb_tm_portfolio_operation (id_tm_portfolio, id_cei_investor, id_cei_brokerage_account, tp_operation, dt_operation, id_instrument, id_institution,
956    side, qtty, price, dt_maturity, id_index, pc_index, pc_coupon, cd_bond_type, issuer, cd_status, ds_origin_trade, is_virtual_trade, already_imported, id_public_bonds, id_public_bonds_base)
957SELECT id_tm_portfolio, tb_cei_investors.id_cei_investor, tb_cei_brokerage_accounts.id_cei_brokerage_account, tp_operation, dt_operation, id_instrument, id_institution,
958    side, qtty, price, dt_maturity, id_index, pc_index, pc_coupon, cd_bond_type, issuer, tb_tm_portfolio_operation_bkp.cd_status, ds_origin_trade, is_virtual_trade, already_imported, seq, seq_base
959FROM tb_tm_portfolio_operation_bkp
960INNER JOIN tb_cei_investors ON tb_tm_portfolio_operation_bkp.id_investor = tb_cei_investors.id_investor
961INNER JOIN tb_cei_brokerage_accounts ON tb_cei_investors.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
962    AND tb_tm_portfolio_operation_bkp.id_account = (replace(tb_cei_brokerage_accounts.number, '-', ''))::INTEGER;
963
964-- Inserir operações restantes, que não têm id_account
965INSERT INTO tb_tm_portfolio_operation (id_tm_portfolio, id_cei_investor, tp_operation, dt_operation, id_instrument, id_institution,
966    side, qtty, price, dt_maturity, id_index, pc_index, pc_coupon, cd_bond_type, issuer, cd_status, ds_origin_trade, is_virtual_trade, already_imported, id_public_bonds , id_public_bonds_base )
967SELECT id_tm_portfolio, tb_cei_investors.id_cei_investor, tp_operation, dt_operation, id_instrument, id_institution,
968    side, qtty, price, dt_maturity, id_index, pc_index, pc_coupon, cd_bond_type, issuer, tb_tm_portfolio_operation_bkp.cd_status, ds_origin_trade, is_virtual_trade, already_imported, seq, seq_base
969FROM tb_tm_portfolio_operation_bkp
970INNER JOIN tb_cei_investors ON tb_tm_portfolio_operation_bkp.id_investor = tb_cei_investors.id_investor
971WHERE tb_tm_portfolio_operation_bkp.id_account IS NULL;
972
973
974--------------------------------------------------------------------------------------------------------------------------------------------------------------------
975-- 30. tb_cei_investors_snapshot - OK (Demorou 17 minutos)
976--------------------------------------------------------------------------------------------------------------------------------------------------------------------
977ALTER TABLE tb_cei_investors_snapshot RENAME TO tb_cei_investors_snapshot_bkp;
978CREATE TABLE tb_cei_investors_snapshot (
979    id_cei_investor_snapshot BIGSERIAL NOT NULL,
980    id_cei_investor BIGINT NOT NULL,
981    dt_snapshot INTEGER NOT NULL,
982    username BIGINT NULL,
983    password VARCHAR(50) NULL,
984    downloaded TIMESTAMP WITHOUT TIME ZONE,
985    sincronized BOOLEAN,
986    cd_status VARCHAR(30),
987    dt_reference INTEGER,
988    in_initial_import BOOLEAN
989);
990ALTER TABLE tb_cei_investors_snapshot ADD CONSTRAINT tb_cei_investors_snapshot_pk PRIMARY KEY (id_cei_investor_snapshot);
991--> Usos da tabela:
992-- Existe delete pelo dt_snapshot -> integrator-process
993CREATE INDEX tb_cei_investors_snapshotdelete_date_idx ON tb_cei_investors_snapshot (dt_snapshot);
994
995INSERT INTO tb_cei_investors_snapshot (id_cei_investor, dt_snapshot, username, password, downloaded, sincronized, cd_status, dt_reference, in_initial_import)
996SELECT tb_cei_investors.id_cei_investor, dt_snapshot, TO_NUMBER(tb_cei_investors_snapshot_bkp.username, '999999999999999999')::BIGINT, tb_cei_investors_snapshot_bkp.password,
997tb_cei_investors_snapshot_bkp.downloaded, tb_cei_investors_snapshot_bkp.sincronized, tb_cei_investors_snapshot_bkp.cd_status,
998tb_cei_investors_snapshot_bkp.dt_reference, tb_cei_investors_snapshot_bkp.in_initial_import
999FROM tb_cei_investors_snapshot_bkp
1000INNER JOIN tb_cei_investors ON tb_cei_investors_snapshot_bkp.id = tb_cei_investors.id_investor
1001WHERE tb_cei_investors_snapshot_bkp.username <> '';
1002
1003
1004--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1005-- 31. tb_cei_traded_assets - OK (Demorou 16 minutos)
1006--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1007ALTER TABLE tb_cei_traded_assets RENAME TO tb_cei_traded_assets_bkp;
1008ALTER TABLE tb_cei_traded_assets_bkp RENAME CONSTRAINT tb_cei_traded_assets_pk TO tb_cei_traded_assets_bkp_pk;
1009CREATE TABLE tb_cei_traded_assets (
1010    id_cei_traded_assets BIGSERIAL NOT NULL,
1011    id_cei_investor BIGINT NOT NULL,
1012    id_cei_brokerage_account BIGINT NOT NULL,
1013    entry_date DATE NOT NULL,
1014    order_type VARCHAR(1) NOT NULL,
1015    market VARCHAR(40) NOT NULL,
1016    trading_code VARCHAR(20) NOT NULL,
1017    quantity INTEGER NOT NULL,
1018    price NUMERIC(13,4) NOT NULL,
1019    value NUMERIC(13,4) NOT NULL,
1020    quotation_factor NUMERIC(8,4) NOT NULL,
1021    specification VARCHAR(40) NOT NULL,
1022    maturity_date DATE,
1023    already_imported BOOLEAN DEFAULT FALSE
1024);
1025ALTER TABLE tb_cei_traded_assets ADD CONSTRAINT tb_cei_traded_assets_pk PRIMARY KEY (id_cei_traded_assets);
1026CREATE INDEX tb_cei_traded_assets_id_cei_investor_idx ON tb_cei_traded_assets (id_cei_investor);
1027--> Usos da tabela:
1028-- Diversos JOINs pelo campo id_cei_brokerage_account -> integrator-process
1029CREATE INDEX tb_cei_traded_assets_brokerage_account_idx ON tb_cei_traded_assets (id_cei_brokerage_account);
1030-- Usos do trading_code no portofoli-manager-connector
1031CREATE INDEX tb_cei_traded_assets_trade_code_idx ON tb_cei_traded_assets (id_cei_investor, trading_code);
1032
1033INSERT INTO tb_cei_traded_assets (id_cei_investor, id_cei_brokerage_account, entry_date,order_type,market, trading_code, quantity, price, value, quotation_factor, specification, maturity_date, already_imported)
1034SELECT tb_cei_investors.id_cei_investor, account_id, entry_date,order_type,market, trading_code, quantity, price, value, quotation_factor, specification, maturity_date, already_imported
1035FROM tb_cei_traded_assets_bkp
1036INNER JOIN tb_cei_investors ON tb_cei_traded_assets_bkp.investor_id = tb_cei_investors.id_investor;
1037
1038
1039--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1040-- 32. tb_cei_executor_request - OK (Necessário verificar a fundo, como agora é pelo id_cei_investor, não é possível reinserir alguns usuários
1041--                                   que não estão vinculados ao CEI (na tb_cei_investors), e que já historicamente tiveram registro na tb_cei_executor_request)
1042--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1043CREATE TABLE tb_cei_executor_request_new (
1044    id_request BIGSERIAL NOT NULL,
1045    id_cei_investor BIGINT NOT NULL,
1046    dh_insert TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
1047    dh_execution_start TIMESTAMP WITHOUT TIME ZONE,
1048    dh_execution_end TIMESTAMP WITHOUT TIME ZONE,
1049    cd_status CHAR(4),
1050    ds_status VARCHAR,
1051    vl_executor_hostname VARCHAR,
1052    ds_origin_message VARCHAR,
1053    stacktrace VARCHAR,
1054    dt_reference INTEGER,
1055    in_initial_import BOOLEAN,
1056    user_request VARCHAR DEFAULT ''
1057);
1058ALTER TABLE tb_cei_executor_request_new ADD CONSTRAINT tb_cei_executor_request_pk PRIMARY KEY (id_request);
1059
1060
1061
1062INSERT INTO tb_cei_executor_request_new (id_cei_investor, dh_insert, dh_execution_start, dh_execution_end, cd_status, ds_status,
1063vl_executor_hostname, ds_origin_message, stacktrace, dt_reference, in_initial_import, user_request)
1064SELECT tb_cei_investors.id_cei_investor, dh_insert, dh_execution_start, dh_execution_end, tb_cei_executor_request.cd_status, tb_cei_executor_request.ds_status,
1065vl_executor_hostname, ds_origin_message, tb_cei_executor_request.stacktrace, tb_cei_executor_request.dt_reference,
1066tb_cei_executor_request.in_initial_import, tb_cei_executor_request.user_request
1067FROM tb_cei_executor_request
1068INNER JOIN tb_cei_investors ON tb_cei_investors.id_investor = tb_cei_executor_request.id_investor;
1069
1070ALTER TABLE tb_cei_executor_request RENAME TO tb_cei_executor_request_bkp;
1071ALTER TABLE tb_cei_executor_request_new RENAME TO tb_cei_executor_request;
1072DROP INDEX IF EXISTS tb_cei_executor_request_cd_status_initial_import_idx;
1073DROP INDEX IF EXISTS tb_cei_executor_request_ds_origin_message_dh_execution_end__idx;
1074DROP INDEX IF EXISTS tb_cei_executor_request_dt_reference_idx;
1075DROP INDEX IF EXISTS tb_cei_executor_request_status_investor_idx;
1076CREATE INDEX tb_cei_executor_request_cd_status_initial_import_idx ON tb_cei_executor_request (cd_status, in_initial_import);
1077CREATE INDEX tb_cei_executor_request_ds_origin_message_dh_execution_end_idx ON tb_cei_executor_request (ds_origin_message, dh_execution_end, cd_status);
1078CREATE INDEX tb_cei_executor_request_dt_reference_idx ON tb_cei_executor_request (dt_reference, id_cei_investor);
1079CREATE INDEX tb_cei_executor_request_status_investor_idx ON tb_cei_executor_request (id_cei_investor, cd_status);
1080--OK, os índíces acima parece atender aos casos verificados
1081
1082
1083--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1084-- 33. tb_cei_request - OK (O INSERT só pegará as requisições mais recentes de cada id_request diferente, por isso alguns históricos serão perdidos e
1085--                          por conta do novo id_cei_investor, tbm será perdido os que investors que se desvincularam do CEI - Demorou 58 minutos)
1086--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1087ALTER TABLE tb_cei_request RENAME TO tb_cei_request_bkp;
1088CREATE TABLE tb_cei_request (
1089    id_cei_executor_request BIGINT NOT NULL,
1090    tp_request VARCHAR(30) NOT NULL,
1091    id_cei_investor BIGINT NOT NULL,
1092    cd_status CHAR(4),
1093    dh_request TIMESTAMP WITHOUT TIME ZONE,
1094    dh_request_start TIMESTAMP WITHOUT TIME ZONE,
1095    dh_request_end TIMESTAMP WITHOUT TIME ZONE,
1096    msg VARCHAR,
1097    success BOOLEAN,
1098    error_code_customer VARCHAR(20)
1099);
1100ALTER TABLE tb_cei_request ADD CONSTRAINT tb_cei_request_pk PRIMARY KEY (id_cei_executor_request, tp_request);
1101--> OK, chave primária atende aos casos de uso
1102-- Indice para agilizar a reinserção na nova tabela
1103CREATE INDEX tb_cei_request_bkp_reinsert ON tb_cei_request_bkp (id_cei_executor_request, tp_request, id_request);
1104
1105INSERT INTO tb_cei_request (id_cei_executor_request, tp_request, id_cei_investor, cd_status, dh_request, msg, error_code_customer, success, dh_request_start, dh_request_end)
1106SELECT id_cei_executor_request, tp_request, tb_cei_investors.id_cei_investor, tb_cei_request_bkp.cd_status, dh_request, msg, error_code_customer, success, dh_request_start, dh_request_end
1107FROM tb_cei_request_bkp
1108INNER JOIN tb_cei_investors ON tb_cei_request_bkp.id_investor = tb_cei_investors.id_investor
1109WHERE tb_cei_request_bkp.id_cei_executor_request IS NOT NULL
1110ORDER BY id_cei_executor_request, id_request, dh_request
1111ON CONFLICT DO NOTHING;
1112
1113-- DROP do INDEX de reinserção pois não será mais utilizado
1114DROP INDEX tb_cei_request_bkp_reinsert;
1115
1116-- Arrumar a sequence da tabela: tb_cei_executor_request (Reinserindo o histórico irá ficar descasado)
1117SELECT setval('"public"."tb_cei_executor_request_new_id_request_seq"'::regclass, (select MAX("id_cei_executor_request")+1 FROM "public"."tb_cei_request"));
1118
1119-- INDEX para melhorar o search pelo id_cei_investor
1120CREATE INDEX tb_cei_request_cei_account_idx ON tb_cei_request(id_cei_investor);
1121
1122
1123--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1124-- 34. tb_investor_cei_assets - OK (Demorou 52 minutos sem índices - Criação dos dois indices demorou 37 minutos)
1125--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1126ALTER TABLE tb_investor_cei_assets RENAME TO tb_investor_cei_assets_bkp;
1127CREATE TABLE tb_investor_cei_assets (
1128    id_investor_cei_assets BIGSERIAL NOT NULL,
1129    id_contract INTEGER NOT NULL,
1130    id_cei_investor BIGINT NOT NULL,
1131    account_number BIGINT NOT NULL,
1132    account_broker INTEGER NOT NULL,
1133    id_brokerage INTEGER NOT NULL,
1134    dt_reference INTEGER NOT NULL,
1135    id_instrument INTEGER,
1136    id_stock INTEGER,
1137    cd_stock VARCHAR(20),
1138    quantity INTEGER,
1139    price NUMERIC(21,8),
1140    vl_total NUMERIC(21,8),
1141    price_cost NUMERIC(21,8)
1142);
1143ALTER TABLE tb_investor_cei_assets ADD CONSTRAINT tb_investor_cei_assets_pk PRIMARY KEY (id_investor_cei_assets);
1144
1145INSERT INTO tb_investor_cei_assets (id_contract, id_cei_investor, account_number, account_broker, id_brokerage,
1146dt_reference, id_instrument, id_stock, cd_stock, quantity, price, vl_total, price_cost)
1147SELECT id_contract, id_cei_investor, account_number, account_broker, id_brokerage, tb_investor_cei_assets_bkp.dt_reference,
1148    id_instrument, id_stock, cd_stock, quantity, price, vl_total, price_cost
1149FROM tb_investor_cei_assets_bkp
1150INNER JOIN tb_cei_investors ON tb_investor_cei_assets_bkp.id_investor = tb_cei_investors.id_investor;
1151
1152CREATE INDEX tb_investor_cei_assets_brokerage_instrument_idx ON tb_investor_cei_assets (id_cei_investor, id_brokerage, id_instrument, dt_reference);
1153--> Usos da tabela:
1154--> JOINs pelo cd_stock ou id_cei_investor -> integrator-proces
1155CREATE INDEX tb_investor_cei_assets_investor_idx ON tb_investor_cei_assets (id_cei_investor); -- Acho que com esse não tem necessidade de fazer um para o cd_stock
1156
1157
1158--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1159-- 35. tb_cei_assets - OK (Demorou 47 minutos sem índices - Criação dos dois indices demorou 28 minutos)
1160--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1161ALTER TABLE tb_cei_assets RENAME TO tb_cei_assets_bkp;
1162ALTER TABLE tb_cei_assets_bkp RENAME CONSTRAINT tb_cei_assets_pk TO tb_cei_assets_bkp_pk;
1163CREATE TABLE tb_cei_assets (
1164    id_cei_assets BIGSERIAL NOT NULL,
1165    id_cei_investor BIGINT NOT NULL,
1166    id_cei_brokerage_account BIGINT NOT NULL,
1167    entry_date DATE NOT NULL,
1168    company VARCHAR NOT NULL,
1169    type VARCHAR NOT NULL,
1170    trading_code VARCHAR NOT NULL,
1171    isin VARCHAR NOT NULL,
1172    price NUMERIC(13,4) NOT NULL,
1173    quantity NUMERIC(13,4) NOT NULL,
1174    value NUMERIC(13,4) NOT NULL,
1175    quotation_factor NUMERIC(8,4) NOT NULL,
1176    dt_matutirity INTEGER,
1177    derivative_guarantee BOOLEAN
1178);
1179ALTER TABLE tb_cei_assets ADD CONSTRAINT tb_cei_assets_pk PRIMARY KEY (id_cei_assets);
1180
1181INSERT INTO tb_cei_assets (id_cei_investor, id_cei_brokerage_account, entry_date, company, type, trading_code, isin, price, quantity, value, quotation_factor, dt_matutirity, derivative_guarantee)
1182SELECT tb_cei_investors.id_cei_investor, account_id, entry_date, company, type,
1183    trading_code, isin, price, quantity, value, quotation_factor, dt_matutirity, derivative_guarantee
1184FROM tb_cei_assets_bkp
1185INNER JOIN tb_cei_investors ON tb_cei_assets_bkp.investor_id = tb_cei_investors.id_investor;
1186
1187
1188CREATE INDEX tb_cei_assets_id_cei_investor_idx ON tb_cei_assets (id_cei_investor);
1189--> Usos da tabela:
1190-- JOINs que usam o id_cei_brokerage_account -> integrator-process
1191CREATE INDEX tb_cei_assets_brokerage_account_idx ON tb_cei_assets (id_cei_brokerage_account);
1192
1193
1194--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1195-- 36. tb_tm_investor_portfolio - OK (Criação rápida, alguns registros ficaram sem id_cei_investor,
1196--                                      acho que por conta de não terem registros na tb_cei_investors (Se desvincularam do cei))
1197--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1198----TABELA LIGAÇAO PORTFOLIO - CREATE IS STRUCTURE JOB (Essa tabela não existe, será uma tabela nova de um processo novo)
1199CREATE TABLE tb_tm_investor_portfolio (
1200    id_investor_portfolio BIGSERIAL NOT NULL,
1201    id_investor INTEGER NOT NULL,
1202    id_contract SMALLINT NOT NULL,
1203    id_portfolio BIGINT NOT NULL,
1204    nickname_portfolio VARCHAR NULL,
1205    desc_portfolio VARCHAR NULL,
1206    cpf_cnpj BIGINT NULL,
1207    document VARCHAR NULL,
1208    id_cei_investor BIGINT NULL,
1209    is_principal BOOLEAN DEFAULT TRUE
1210);
1211ALTER TABLE tb_tm_investor_portfolio ADD CONSTRAINT tb_tm_investor_portfolio_pk PRIMARY KEY (id_investor_portfolio);
1212ALTER TABLE tb_tm_investor_portfolio ADD CONSTRAINT tb_tm_investor_portfolio_uk UNIQUE (id_investor, id_contract, id_portfolio, cpf_cnpj);
1213
1214INSERT INTO tb_tm_investor_portfolio (id_investor, id_contract, id_portfolio, cpf_cnpj, document,  id_cei_investor, is_principal)
1215SELECT tb_as_portfolio.id_investor, tb_as_portfolio.id_contract, tb_as_portfolio.id_portfolio, tb_investor.cnpj_cpf, tb_investor.document_number, tb_cei_investors.id_cei_investor, TRUE
1216FROM tb_as_portfolio
1217INNER JOIN tb_investor USING (id_investor)
1218LEFT JOIN tb_cei_investors USING (id_investor)
1219WHERE tb_as_portfolio.id_contract = 2020
1220    AND tb_as_portfolio.id_asset = 97
1221    AND tb_as_portfolio.tp_tm_portfolio = 'CUSTODY';
1222
1223CREATE INDEX tb_tm_investor_portfolio_investor_idx ON tb_tm_investor_portfolio (id_cei_investor);
1224
1225
1226--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1227-- 37. tb_tm_bond_deleted - Tabela encontrada que não estava nos scripts (É rápida a inserção)
1228--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1229ALTER TABLE tb_tm_bond_deleted RENAME TO tb_tm_bond_deleted_bkp;
1230CREATE TABLE tb_tm_bond_deleted(
1231	id_cei_investor BIGINT NOT NULL,
1232	cd_ticker VARCHAR NOT NULL,
1233	ds_delete_origin VARCHAR NOT NULL,
1234	dh_delete TIMESTAMP NOT NULL DEFAULT NOW()
1235);
1236
1237INSERT INTO tb_tm_bond_deleted(id_cei_investor, cd_ticker, ds_delete_origin, dh_delete )
1238SELECT tb_cei_investors.id_cei_investor, cd_ticker, ds_delete_origin, dh_delete
1239FROM tb_tm_bond_deleted_bkp INNER JOIN tb_cei_investors ON tb_tm_bond_deleted_bkp.id_investor = tb_cei_investors.id_investor;
1240
1241
1242--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1243-- 38. tb_cei_securities - Tabela encontrada (Mas parece que não é usada, está vazia em produção)
1244--                          Se essa tabela for alterada é necessário modificar o método clearInvestorCei do carteira-ws para usar o id_cei_investor tbm. (Essa Patrick que fez inteira)
1245--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1246-- ALTER TABLE tb_cei_securities RENAME CONSTRAINT tb_cei_securities_pk TO tb_cei_securities_bkp_pk;
1247-- ALTER TABLE tb_cei_securities RENAME TO tb_cei_securities_bkp;
1248-- CREATE TABLE tb_cei_securities (
1249-- 	id_cei_securities BIGSERIAL NOT NULL,
1250-- 	id_cei_investor BIGINT NOT NULL,
1251-- 	brokerage_id INTEGER NOT NULL,
1252-- 	entry_date DATE NOT NULL,
1253-- 	instrument VARCHAR NOT NULL,
1254-- 	code VARCHAR NOT NULL,
1255-- 	issuer VARCHAR NOT NULL,
1256-- 	issue_date DATE NOT NULL,
1257-- 	due_date DATE NOT NULL,
1258-- 	available_quantity INTEGER NOT NULL,
1259-- 	unavailable_quantity INTEGER NOT NULL,
1260-- 	technical_reserve INTEGER NOT NULL,
1261-- 	blogged INTEGER NOT NULL,
1262-- 	warranty_amount_received INTEGER NOT NULL,
1263-- 	warranty_amount_provided INTEGER NOT NULL,
1264-- 	warranty_notes VARCHAR,
1265-- 	engravings_amount_received INTEGER NOT NULL,
1266-- 	engravings_amount_provided INTEGER NOT NULL,
1267-- 	engravings_counterparty INTEGER NOT NULL,
1268-- 	engravings_notes VARCHAR,
1269-- 	CONSTRAINT tb_cei_securities_pk PRIMARY KEY (id_cei_securities)
1270-- );
1271
1272-- Essas tabelas abaixo também existem, mas não foram encontradas no script antigo e os dados em produção são de 2017/2018, verificando em projetos
1273-- os usos delas:        tb_investor_cei_td  e  tb_investor_cei_fund
1274
1275-- Essas tabelas acima não foram encontrados usos, ou usos relevantes nos projetos.
1276
1277
1278--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1279-- 39. tb_tm_investor_portfolio_create_request - (Nova tabela, para controle de pedidos de criação de portfólios novos)
1280--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1281CREATE TABLE tb_tm_investor_portfolio_create_request(
1282  	id_request BIGSERIAL NOT NULL,
1283    id_investor INTEGER NOT NULL,
1284    id_contract SMALLINT NOT NULL,
1285    cpf_cnpj BIGINT NOT NULL,
1286    document VARCHAR NULL,
1287    nickname_portfolio VARCHAR NULL,
1288    desc_portfolio VARCHAR NULL,
1289    is_cnpj BOOLEAN DEFAULT FALSE,
1290    status VARCHAR NOT NULL DEFAULT 'PENDING'
1291);
1292
1293ALTER TABLE tb_tm_investor_portfolio_create_request ADD CONSTRAINT tb_tm_investor_portfolio_create_request_pk PRIMARY KEY (id_request);
1294
1295CREATE INDEX tb_tm_investor_portfolio_create_request_pend_idx ON tb_tm_investor_portfolio_create_request (id_investor, id_contract, status) WHERE status = 'PENDING';
1296
1297CREATE INDEX tb_tm_investor_portfolio_create_request_exists_idx ON tb_tm_investor_portfolio_create_request (id_contract, cpf_cnpj);
1298
1299
1300--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1301-- 40. tb_tm_investor_portfolio_deleted - OK (Criada para desvincular investidores de carteiras adicionais)
1302--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1303CREATE TABLE tb_tm_investor_portfolio_deleted (
1304    id_investor_portfolio BIGINT NOT NULL,
1305    id_investor INTEGER NOT NULL,
1306    id_contract SMALLINT NOT NULL,
1307    id_portfolio BIGINT NOT NULL,
1308    nickname_portfolio VARCHAR NULL,
1309    desc_portfolio VARCHAR NULL,
1310    cpf_cnpj BIGINT NULL,
1311    document VARCHAR NULL,
1312    id_cei_investor BIGINT NULL,
1313    is_principal BOOLEAN,
1314    dt_deleted TIMESTAMP NOT NULL DEFAULT NOW()
1315);
1316
1317
1318
1319
1320
1321--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1322-- Functions usadas pelos processos e alteradas/melhorados
1323--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1324-- Function encontrada que não havia sido alterada: fc_as_multibroker_operations_to_cancel
1325DROP FUNCTION fc_as_multibroker_operations_to_cancel(integer)
1326CREATE OR REPLACE FUNCTION public.fc_as_multibroker_operations_to_cancel(arg_id_cei_investor bigint) RETURNS TABLE(id_operation integer, dt_operation integer, id_instrument bigint, nu_brokerage_external integer) LANGUAGE plpgsql AS $function$ BEGIN RETURN QUERY (
1327    WITH cei_positions AS
1328  (SELECT TO_CHAR(MAX(entry_date), 'YYYYMMDD')::INTEGER AS entry_date, trading_code, tb_instrument.id_instrument, brokerage_id
1329   FROM tb_cei_traded_assets
1330   INNER JOIN tb_cei_brokerage_accounts ON tb_cei_traded_assets.id_cei_investor = tb_cei_brokerage_accounts.id_cei_investor
1331   AND tb_cei_traded_assets.id_cei_brokerage_account = tb_cei_brokerage_accounts.id_cei_brokerage_account
1332   INNER JOIN tb_instrument ON tb_cei_traded_assets.trading_code = tb_instrument.cd_ticker
1333   WHERE tb_cei_traded_assets.id_cei_investor = arg_id_cei_investor
1334   GROUP BY trading_code, tb_instrument.id_instrument, brokerage_id)
1335SELECT tb_asset_operation.id_operation, tb_asset_operation.dt_operation, tb_asset_operation.id_instrument, tb_brokerage.nu_brokerage_external::INT AS nu_brokerage_external
1336FROM tb_asset_operation
1337INNER JOIN tb_instrument USING (id_instrument)
1338INNER JOIN tb_as_custodian ON tb_asset_operation.id_custodian = tb_as_custodian.id_custodian
1339INNER JOIN tb_brokerage ON tb_as_custodian.id_brokerage = tb_brokerage.id_brokerage
1340INNER JOIN cei_positions ON tb_asset_operation.id_instrument = cei_positions.id_instrument
1341AND tb_asset_operation.dt_operation <= cei_positions.entry_date
1342AND tb_brokerage.nu_brokerage_external::INT = cei_positions.brokerage_id
1343WHERE cd_operation_origin IN ('ORS', 'CNC')
1344  AND cd_operation_state = 'A'
1345  AND id_fund IN
1346    (SELECT id_fund
1347     FROM tb_asset_fund
1348     WHERE (id_asset, id_portfolio) IN
1349         (SELECT tb_as_portfolio.id_asset, tb_as_portfolio.id_portfolio
1350          FROM tb_as_portfolio
1351          LEFT JOIN tb_tm_investor_portfolio ON tb_as_portfolio.id_portfolio = tb_tm_investor_portfolio.id_portfolio
1352          AND tb_as_portfolio.id_contract = tb_tm_investor_portfolio.id_contract
1353          WHERE tb_tm_investor_portfolio.id_cei_investor = arg_id_cei_investor
1354          AND tb_as_portfolio.tp_tm_portfolio = 'CUSTODY'))); END; $function$
1355
1356
1357-- Function nova (baseada em existente) para pegar eventos por range de datas, a query retorna de forma mais rápida (Dá pra fazer mais rápido, mas não sei se é melhor por agora, é usada no processo do pm-connector)
1358CREATE OR REPLACE FUNCTION public.fc_proh_events(p_date_event_init integer, p_date_event_end integer) RETURNS TABLE(id integer, reference_date integer, cd_type smallint, action_type character varying, event_desc character varying, cd_codisi character varying, cd_dismes smallint, cd_codneg character varying, nm_company character varying, cd_especi character varying, dt_datage integer, vl_vlrpro numeric, vl_fatgru numeric, cd_isipro character varying, cd_dispro smallint, cd_isiori character varying, cd_disori smallint, cd_isidir character varying, cd_disdir smallint, vl_presub numeric, dt_limsub integer, dt_datdiv integer, dy_inidiv integer, dy_fimdiv integer, cd_inddiv character varying, cd_indfra character varying, nm_nomfra character varying, vl_prefra numeric, vl_indbon boolean, cd_inccor smallint, nm_nomcor character varying, cd_percor smallint, nm_nomper character varying, dt_inicor integer, dt_fimcor integer, vl_numpar smallint, vl_perpar smallint, nm_nompar character varying, vl_parsub numeric, cd_admin integer, dt_inipro integer, numseq bigint, cd_sit character, dh_last_update TIMESTAMP WITHOUT time ZONE) LANGUAGE plpgsql AS $function$ DECLARE days Integer; BEGIN RETURN query
1359SELECT events1.id,
1360       events1.reference_date,
1361       events1.cd_type,
1362       events1.action_type,
1363       events1.event_desc,
1364       events1.cd_codisi,
1365       events1.cd_dismes,
1366       events1.cd_codneg,
1367       events1.nm_company,
1368       events1.cd_especi,
1369       events1.dt_datage,
1370       events1.vl_vlrpro,
1371       events1.vl_fatgru,
1372       events1.cd_isipro,
1373       events1.cd_dispro,
1374       events1.cd_isiori,
1375       events1.cd_disori,
1376       events1.cd_isidir,
1377       events1.cd_disdir,
1378       events1.vl_presub,
1379       events1.dt_limsub,
1380       events1.dt_datdiv,
1381       events1.dy_inidiv,
1382       events1.dy_fimdiv,
1383       events1.cd_inddiv,
1384       events1.cd_indfra,
1385       events1.nm_nomfra,
1386       events1.vl_prefra,
1387       events1.vl_indbon,
1388       events1.cd_inccor,
1389       events1.nm_nomcor,
1390       events1.cd_percor,
1391       events1.nm_nomper,
1392       events1.dt_inicor,
1393       events1.dt_fimcor,
1394       events1.vl_numpar,
1395       events1.vl_perpar,
1396       events1.nm_nompar,
1397       events1.vl_parsub,
1398       events1.cd_admin,
1399       events1.dt_inipro,
1400       events1.numseq,
1401       events1.cd_sit,
1402       events1.dh_last_update
1403FROM tb_corporate_action AS events1
1404WHERE events1.dt_inipro > p_date_event_init AND events1.dt_inipro <= p_date_event_end
1405UNION ALL
1406SELECT events2.id,
1407       events2.reference_date,
1408       events2.cd_type,
1409       events2.action_type,
1410       events2.event_desc,
1411       events2.cd_codisi,
1412       events2.cd_dismes,
1413       tb_as_stock_migration.cd_stock_to AS cd_codneg,
1414       events2.nm_company,
1415       events2.cd_especi,
1416       events2.dt_datage,
1417       events2.vl_vlrpro,
1418       events2.vl_fatgru,
1419       events2.cd_isipro,
1420       events2.cd_dispro,
1421       events2.cd_isiori,
1422       events2.cd_disori,
1423       events2.cd_isidir,
1424       events2.cd_disdir,
1425       events2.vl_presub,
1426       events2.dt_limsub,
1427       events2.dt_datdiv,
1428       events2.dy_inidiv,
1429       events2.dy_fimdiv,
1430       events2.cd_inddiv,
1431       events2.cd_indfra,
1432       events2.nm_nomfra,
1433       events2.vl_prefra,
1434       events2.vl_indbon,
1435       events2.cd_inccor,
1436       events2.nm_nomcor,
1437       events2.cd_percor,
1438       events2.nm_nomper,
1439       events2.dt_inicor,
1440       events2.dt_fimcor,
1441       events2.vl_numpar,
1442       events2.vl_perpar,
1443       events2.nm_nompar,
1444       events2.vl_parsub,
1445       events2.cd_admin,
1446       events2.dt_inipro,
1447       events2.numseq,
1448       events2.cd_sit,
1449       events2.dh_last_update
1450FROM tb_corporate_action AS events2
1451INNER JOIN tb_as_stock_migration ON tb_as_stock_migration.cd_stock_from = events2.cd_codneg
1452WHERE events2.dt_inipro > p_date_event_init AND events2.dt_inipro <= p_date_event_end; END; $function$
1453
1454
1455--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1456-- Indices que melhoram perfomance de queries que fazem parte do processo
1457--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1458
1459CREATE INDEX tb_as_reprocessing_request_portfolio_last_request_idx ON tb_as_reprocessing_request_portfolio (id_contract, id_asset, id_portfolio);
1460
1461CREATE INDEX tb_stock_options_idx ON tb_stock ((cd_stock||'E'));
1462
1463--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1464-- Materializeds Views que usam as tabelas alteradas
1465--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1466-- MV 1
1467-- Esta MV usa uma tabela que será excluida, portanto pode ser que não precise mais dela, se precisar terá que ser reformulada !
1468
1469-- DROP MATERIALIZED VIEW mv_cei_info;
1470-- CREATE MATERIALIZED VIEW public.mv_cei_info
1471-- TABLESPACE pg_default
1472-- AS
1473-- SELECT tb_tm_portfolio_operation.id_investor,
1474--        tb_investor.name,
1475--        tb_tm_portfolio_operation.id_institution,
1476--        tb_tm_institution.nm_institution,
1477--        tb_instrument.cd_instrument_type,
1478--        tb_instrument.id_instrument,
1479--        tb_instrument.cd_ticker,
1480--        sum( CASE WHEN (tb_tm_portfolio_operation.side = 'B'::bpchar) THEN tb_tm_portfolio_operation.qtty ELSE (- tb_tm_portfolio_operation.qtty) END) AS qtty,
1481--        tb_tm_custody_portfolio.vl_current_pu,
1482--        (sum( CASE WHEN (tb_tm_portfolio_operation.side = 'B'::bpchar) THEN tb_tm_portfolio_operation.qtty ELSE (- tb_tm_portfolio_operation.qtty) END) * tb_tm_custody_portfolio.vl_current_pu) AS vl_stock
1483-- FROM ((((tb_tm_portfolio_operation
1484--          JOIN tb_tm_institution ON ((tb_tm_institution.id_institution = tb_tm_portfolio_operation.id_institution)))
1485--         JOIN tb_tm_custody_portfolio ON (((tb_tm_portfolio_operation.id_investor = tb_tm_custody_portfolio.id_investor)
1486--                                           AND (tb_tm_portfolio_operation.id_instrument = tb_tm_custody_portfolio.id_instrument)
1487--                                           AND (tb_tm_portfolio_operation.id_institution = tb_tm_custody_portfolio.id_institution))))
1488--        JOIN tb_instrument ON ((tb_tm_portfolio_operation.id_instrument = tb_instrument.id_instrument)))
1489--       JOIN tb_investor ON ((tb_investor.id_investor = tb_tm_portfolio_operation.id_investor)))
1490-- WHERE (((tb_tm_portfolio_operation.ds_origin_trade)::text <> 'Manually'::text)
1491--        AND (tb_investor.id_investor IN
1492--               (SELECT tb_cei_investors.id_investor
1493--                FROM tb_cei_investors
1494--                WHERE ((tb_cei_investors.cd_status)::text = 'SUCCESS'::text))))
1495-- GROUP BY tb_tm_portfolio_operation.id_investor,
1496--          tb_investor.name,
1497--          tb_tm_portfolio_operation.id_institution,
1498--          tb_tm_institution.nm_institution,
1499--          tb_instrument.cd_instrument_type,
1500--          tb_instrument.id_instrument,
1501--          tb_instrument.cd_ticker,
1502--          tb_tm_custody_portfolio.vl_current_pu
1503-- ORDER BY tb_tm_portfolio_operation.id_investor,
1504--          tb_tm_portfolio_operation.id_institution,
1505--          tb_instrument.id_instrument
1506-- WITH DATA;
1507
1508-- MV 2
1509-- O campo de id_investor da tb_cei_executor_request foi trocado pelo id_cei_investor na query dessa mv
1510DROP MATERIALIZED VIEW mv_cei_last_sync;
1511CREATE MATERIALIZED VIEW public.mv_cei_last_sync
1512TABLESPACE pg_default
1513AS
1514WITH investor_last_execution AS
1515  (SELECT tb_cei_executor_request.id_cei_investor,
1516          (max(tb_cei_executor_request.dh_execution_end))::date AS dh_last_execution
1517   FROM tb_cei_executor_request
1518   WHERE ((tb_cei_executor_request.cd_status = 'EXEC'::bpchar)
1519          AND (tb_cei_executor_request.id_cei_investor IN
1520                 (SELECT tb_cei_investors.id_cei_investor AS id_cei_investor
1521                  FROM tb_cei_investors
1522                  WHERE ((tb_cei_investors.cd_status)::text = ANY (ARRAY[('SUCCESS'::character varying)::text, ('PENDING'::character varying)::text, ('RUNNING'::character varying)::text, ('SELENIUM_RESTART'::character varying)::text])))))
1523   GROUP BY tb_cei_executor_request.id_cei_investor)
1524SELECT investor_last_execution.dh_last_execution,
1525       count(*) AS COUNT
1526FROM investor_last_execution
1527GROUP BY investor_last_execution.dh_last_execution
1528ORDER BY investor_last_execution.dh_last_execution DESC
1529WITH DATA;
1530
1531--MV 3
1532-- Já foi alterada para usar o id_cei_investor
1533DROP MATERIALIZED VIEW mv_cei_request_last_status;
1534CREATE MATERIALIZED VIEW public.mv_cei_request_last_status
1535TABLESPACE pg_default
1536AS WITH dados AS (
1537         SELECT tb_cei_executor_request_1.id_cei_investor,
1538            max(tb_cei_executor_request_1.id_request) AS id_request
1539           FROM tb_cei_executor_request tb_cei_executor_request_1
1540          GROUP BY tb_cei_executor_request_1.id_cei_investor
1541        )
1542 SELECT tb_cei_executor_request.id_request,
1543    tb_cei_executor_request.id_cei_investor,
1544    tb_cei_investors.id_investor,
1545    tb_cei_investors.username,
1546    tb_cei_executor_request.dh_execution_start,
1547    tb_cei_executor_request.dh_execution_end,
1548    tb_cei_executor_request.cd_status,
1549    tb_cei_executor_request.vl_executor_hostname,
1550    tb_cei_executor_request.ds_origin_message,
1551    tb_cei_executor_request.stacktrace,
1552    tb_cei_executor_request.dt_reference,
1553    tb_cei_executor_request.in_initial_import,
1554    tb_cei_executor_request.dh_insert
1555   FROM dados
1556    INNER JOIN tb_cei_executor_request USING (id_request, id_cei_investor)
1557    INNER JOIN tb_cei_investors USING (id_cei_investor)
1558WITH DATA;
1559
1560--MV 4
1561-- Foi refeita
1562DROP MATERIALIZED VIEW mv_date_cei_assets;
1563CREATE MATERIALIZED VIEW public.mv_date_cei_assets
1564TABLESPACE pg_default
1565AS
1566SELECT tb_cei_investors.id_investor AS id_investor,
1567       to_char((max(tb_cei_assets.entry_date))::TIMESTAMP WITH time ZONE, 'YYYYMMDD'::text) AS entry_date
1568FROM (tb_cei_investors
1569      JOIN tb_cei_assets ON ((tb_cei_assets.id_cei_investor = tb_cei_investors.id_cei_investor)))
1570WHERE ((tb_cei_investors.cd_status)::text = 'SUCCESS'::text)
1571GROUP BY tb_cei_investors.id_investor
1572WITH DATA;
1573
1574-- MV 5
1575-- Não analisada completamente: mv_cei_new_error_daily_complete (Precisa refazer - A de baixo foi refeita)
1576DROP MATERIALIZED VIEW mv_cei_new_error_daily_complete
1577refresh materialized view mv_cei_new_error_daily;
1578CREATE MATERIALIZED VIEW public.mv_cei_new_error_daily_complete
1579TABLESPACE pg_default
1580AS
1581SELECT mv_cei_new_error_daily.id_investor,
1582       tb_cei_investors.dt_reference AS dt_reference_cei,
1583       mv_cei_new_error_daily.name,
1584       mv_cei_new_error_daily.cd_ticker,
1585       mv_cei_new_error_daily.status,
1586       mv_cei_new_error_daily.delta_qtd_is360,
1587       mv_cei_new_error_daily.delta_qtd_cei,
1588       mv_cei_new_error_daily.delta_aju,
1589       mv_cei_new_error_daily.delta_cei,
1590       mv_cei_new_error_daily.delta_vir,
1591       mv_cei_new_error_daily.delta_manual,
1592       mv_cei_new_error_daily.delta_event,
1593       mv_cei_new_error_daily.delta_cus,
1594       mv_cei_new_error_daily.delta_mb,
1595       mv_cei_new_error_daily.delta_pend,
1596       fc_get_cei_compare_error_description(tb_cei_investors.dt_reference, tb_as_reprocess_portfolio_last_status.cd_status, tb_as_reprocess_portfolio_last_status.ds_stack_trace, mv_cei_new_error_daily.old_qtty_is360, mv_cei_new_error_daily.old_qtty_cei, (mv_cei_new_error_daily.old_qtd_ajuste)::numeric, (mv_cei_new_error_daily.old_qtd_cei)::numeric, (mv_cei_new_error_daily.old_qtd_cei_vir)::numeric, (mv_cei_new_error_daily.old_qtd_manual)::numeric, (mv_cei_new_error_daily.old_qtd_evento)::numeric, (mv_cei_new_error_daily.old_qtd_cei_cus)::numeric, (mv_cei_new_error_daily.old_qtd_multibroker)::numeric, (mv_cei_new_error_daily.old_qtd_pend)::numeric, mv_cei_new_error_daily.old_qtd_manual_antes_18meses, mv_cei_new_error_daily.new_qtty_is360, mv_cei_new_error_daily.new_qtty_cei, (mv_cei_new_error_daily.new_qtd_ajuste)::numeric, (mv_cei_new_error_daily.new_qtd_cei)::numeric, (mv_cei_new_error_daily.new_qtd_cei_vir)::numeric, (mv_cei_new_error_daily.new_qtd_manual)::numeric, (mv_cei_new_error_daily.new_qtd_evento)::numeric, (mv_cei_new_error_daily.new_qtd_cei_cus)::numeric, (mv_cei_new_error_daily.new_qtd_multibroker)::numeric, (mv_cei_new_error_daily.new_qtd_pend)::numeric, mv_cei_new_error_daily.new_qtd_manual_antes_18meses) AS nm_error
1597FROM (((mv_cei_new_error_daily
1598        LEFT JOIN tb_as_portfolio ON (((tb_as_portfolio.id_contract = 2020)
1599                                       AND (tb_as_portfolio.id_asset = 97)
1600                                       AND (tb_as_portfolio.id_investor = mv_cei_new_error_daily.id_investor)
1601                                       AND ((tb_as_portfolio.tp_tm_portfolio)::text = 'CUSTODY'::text))))
1602       LEFT JOIN tb_as_reprocess_portfolio_last_status ON (((tb_as_reprocess_portfolio_last_status.id_contract = tb_as_portfolio.id_contract)
1603                                                            AND (tb_as_reprocess_portfolio_last_status.id_portfolio = tb_as_portfolio.id_portfolio))))
1604      LEFT JOIN tb_cei_investors ON ((tb_cei_investors.id_investor = mv_cei_new_error_daily.id_investor)))
1605WHERE ((mv_cei_new_error_daily.dt_reference = (now())::date)
1606       AND (mv_cei_new_error_daily.id_investor IN
1607              (SELECT DISTINCT mv_cei_comparation_last_execution.id_investor
1608               FROM mv_cei_comparation_last_execution)))
1609ORDER BY mv_cei_new_error_daily.name
1610WITH DATA;
1611
1612-- MV 6
1613-- Foi refeita, é necessário testar integralmente, deve ser analisado o desempenho tbm
1614DROP MATERIALIZED VIEW mv_cei_push_campaign;
1615CREATE MATERIALIZED VIEW public.mv_cei_push_campaign as
1616WITH investors AS
1617  (SELECT tb_cei_campaign.dh_push_sent,
1618          tb_cei_campaign.dh_list,
1619          tb_cei_campaign_investor.id_investor
1620   FROM (tb_cei_campaign
1621         JOIN tb_cei_campaign_investor USING (id_campaign))
1622   WHERE (tb_cei_campaign.status = 'A'::bpchar)),
1623     requests_valid AS
1624  (SELECT tb_cei_executor_request.id_request
1625   FROM tb_cei_executor_request
1626   WHERE ((tb_cei_executor_request.id_cei_investor IN
1627             (SELECT tb_cei_investors.id_cei_investor
1628              FROM investors investors_1 INNER JOIN tb_cei_investors ON investors_1.id_investor = tb_cei_investors.id_investor))
1629          AND (tb_cei_executor_request.dh_execution_end >=
1630                 (SELECT LEAST(investors_1.dh_push_sent, investors_1.dh_list) AS "least"
1631                  FROM investors investors_1 LIMIT 1))
1632          AND (tb_cei_executor_request.cd_status = 'EXEC'::bpchar))),
1633     sync_info AS
1634  (SELECT tb_cei_investors.id_investor,
1635          min(tb_cei_executor_request.dh_execution_end) AS dh_execution_end,
1636          count(*) AS COUNT
1637   FROM tb_cei_executor_request
1638   INNER JOIN tb_cei_investors ON tb_cei_executor_request.id_cei_investor = tb_cei_investors.id_cei_investor
1639   WHERE (tb_cei_executor_request.id_request IN
1640            (SELECT requests_valid.id_request
1641             FROM requests_valid))
1642   GROUP BY tb_cei_investors.id_investor)
1643SELECT investors.dh_push_sent,
1644       investors.dh_list,
1645       investors.id_investor,
1646       tb_investor.name,
1647       COALESCE(sync_info.COUNT, (0)::bigint) AS qtd_success,
1648       sync_info.dh_execution_end AS dh_insert,
1649       CASE
1650           WHEN (sync_info.id_investor IS NULL) THEN 'not_solved'::text
1651           WHEN (sync_info.dh_execution_end < investors.dh_push_sent) THEN 'solved_before_push'::text
1652           ELSE 'solved_after_push'::text
1653       END AS tp_success
1654FROM ((investors
1655       JOIN tb_investor USING (id_investor))
1656      LEFT JOIN sync_info USING (id_investor))
1657ORDER BY sync_info.dh_execution_end
1658WITH DATA;
1659
1660--MV 7
1661-- (Alterados campos que necessitavam para a query rodar)
1662DROP MATERIALIZED VIEW mv_cei_error_stock;
1663CREATE MATERIALIZED VIEW public.mv_cei_error_stock
1664TABLESPACE pg_default
1665AS
1666SELECT mv_cei_comparation_last_execution.id_investor,
1667       mv_cei_comparation_last_execution.cd_ticker,
1668       mv_cei_comparation_last_execution.qtty_is360,
1669       mv_cei_comparation_last_execution.qtty_cei,
1670       mv_cei_comparation_last_execution.qtd_ajuste,
1671       mv_cei_comparation_last_execution.qtd_cei,
1672       mv_cei_comparation_last_execution.qtd_cei_cus,
1673       mv_cei_comparation_last_execution.qtd_cei_vir,
1674       mv_cei_comparation_last_execution.qtd_evento,
1675       mv_cei_comparation_last_execution.qtd_manual,
1676       mv_cei_comparation_last_execution.qtd_multibroker,
1677       mv_cei_comparation_last_execution.qtd_pend,
1678       CASE
1679           WHEN (NOT mv_cei_comparation_last_execution.instrument_registered) THEN 'Ativo não cadastrado'::text
1680           WHEN ((COALESCE(tb_as_reprocess_portfolio_last_status.cd_status, 'NULL'::character varying))::text = ANY (ARRAY[('ERRO'::character varying)::text, ('EXEP'::character varying)::text])) THEN ((tb_as_reprocess_portfolio_last_status.cd_status)::text || ' no IS'::text)
1681           WHEN ((tb_tm_reprocess_request_last_status.cd_status)::text = 'ERRO'::text) THEN 'Erro no Integrador'::text
1682           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_pend, 0) > 0) THEN 'Pendente de reprocessamento'::text
1683           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_manual, (0)::bigint) > 0) THEN 'Trade Manual'::text
1684           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_multibroker, 0) > 0) THEN 'Trade Multibroker'::text
1685           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_manual_ante_18meses, 0) > 0) THEN 'Trade Manual Antes 18 Meses'::text
1686           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_ajuste, (0)::bigint) > 0) THEN 'Trade de Ajuste'::text
1687           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_cei_cus, 0) > 0) THEN 'Trade de Custódia'::text
1688           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_cei_vir, (0)::bigint) > 0) THEN 'Trade Virtual'::text
1689           WHEN (COALESCE(mv_cei_comparation_last_execution.qtd_evento, (0)::bigint) > 0) THEN 'Evento'::text
1690           WHEN ((COALESCE(mv_cei_comparation_last_execution.qtty_is360, (0)::numeric) = (0)::numeric)
1691                 AND (COALESCE(mv_cei_comparation_last_execution.qtty_cei, (0)::numeric) <> (0)::numeric)) THEN 'Não importou para IS, ou falta VIR/CUS'::text
1692           WHEN ((COALESCE(mv_cei_comparation_last_execution.qtty_is360, (0)::numeric) <> (0)::numeric)
1693                 AND (COALESCE(mv_cei_comparation_last_execution.qtty_cei, (0)::numeric) = (0)::numeric)
1694                 AND (mv_cei_comparation_last_execution.qtd_cei > 0)) THEN 'Capa CEI Desatualizada ou falta VIR/CUS'::text
1695           WHEN ((COALESCE(mv_cei_comparation_last_execution.qtty_is360, (0)::numeric) <> (0)::numeric)
1696                 AND (COALESCE(mv_cei_comparation_last_execution.qtty_cei, (0)::numeric) <> (0)::numeric)
1697                 AND (mv_cei_comparation_last_execution.qtd_cei > 0)) THEN 'Bug na capa do CEI ou no IS'::text
1698           WHEN ((COALESCE(mv_cei_comparation_last_execution.qtty_is360, (0)::numeric) <> (0)::numeric)
1699                 AND (COALESCE(mv_cei_comparation_last_execution.qtty_cei, (0)::numeric) = (0)::numeric)
1700                 AND (mv_cei_comparation_last_execution.qtd_cei = 0)
1701                 AND (mv_cei_comparation_last_execution.qtd_manual = 0)) THEN 'Reprocessamento Perdido ou Bug'::text
1702           ELSE 'Não parametrizado'::text
1703       END AS nm_error
1704FROM (((mv_cei_comparation_last_execution
1705        LEFT JOIN tb_as_portfolio ON (((tb_as_portfolio.id_contract = 2020)
1706                                       AND (tb_as_portfolio.id_asset = 97)
1707                                       AND (tb_as_portfolio.id_investor = mv_cei_comparation_last_execution.id_investor)
1708                                       AND ((tb_as_portfolio.tp_tm_portfolio)::text = 'CUSTODY'::text))))
1709       LEFT JOIN tb_as_reprocess_portfolio_last_status ON (((tb_as_reprocess_portfolio_last_status.id_contract = tb_as_portfolio.id_contract)
1710                                                            AND (tb_as_reprocess_portfolio_last_status.id_portfolio = tb_as_portfolio.id_portfolio))))
1711       LEFT JOIN tb_cei_investors ON mv_cei_comparation_last_execution.id_investor = tb_cei_investors.id_investor
1712       LEFT JOIN tb_tm_reprocess_request_last_status ON ((tb_tm_reprocess_request_last_status.id_cei_investor = tb_cei_investors.id_cei_investor)))
1713WHERE (mv_cei_comparation_last_execution.status = 'ERRADO'::text)
1714WITH DATA;
1715