********************************************************************************
********************************************************************************
*0. CRIAR ESTRUTURAS PARA EMISSAO DO RELATÓRIO:;

*DROP TABLE RELAT_PARCELAS_PENDENTES2;

proc sql;
create table RELAT_PARCELAS_PENDENTES2 (
    cd_pcer num(10),
    cd_psub num(10),
    cd_produto num(10) ,
    NM_PRODUTO char(200),
    NO_PROPOSTA num(19),
    DT_CONTRATACAO_PROPOSTA DATE,
    CD_TIPO_PROPOSTA char(10),
    DT_EMISSAO_PROPOSTA DATE,
    DT_VIGENCIA_PROPOSTA_INICIO DATE,
    DT_VIGENCIA_PROPOSTA_FIM DATE,
    NO_CERTIFICADO_INDIVIDUAL num,
    cd_fatura_proposta num(19),
    NO_FATURA_PROPOSTA num,
    VL_PAGAR num(15,2), 
    CD_STATUS char(4),
    DT_VENCIMENTO DATE,
    NM_PESSOA_PSUB char(200),  
    NO_DOC_PRINCIP_PSUB num(19), 
    NO_APOLICE num(19),
    CD_PROPOSTA_GRUPO num(19)
);

********************************************************************************
* 1. INSERT INICIAL NA TABELA (TEMP) DE RELATORIO;

INSERT INTO RELAT_PARCELAS_PENDENTES2 (
    cd_pcer,
    cd_psub,
    cd_produto,
    NM_PRODUTO,
    NO_PROPOSTA,
    DT_CONTRATACAO_PROPOSTA,
    CD_TIPO_PROPOSTA,
    DT_EMISSAO_PROPOSTA,
    DT_VIGENCIA_PROPOSTA_INICIO,
    DT_VIGENCIA_PROPOSTA_FIM,
    NO_CERTIFICADO_INDIVIDUAL,
    CD_FATURA_PROPOSTA,
    NO_FATURA_PROPOSTA,
    VL_PAGAR,
    CD_STATUS,
    DT_VENCIMENTO,
    NO_APOLICE,
    CD_PROPOSTA_GRUPO)
select 
    prop.cd_proposta as 'cd_pcer'n,
    psub.cd_proposta as 'cd_psub'n,
    p.cd_produto,
    P.NM_PRODUTO,
    PROP.NO_PROPOSTA,
    PROP.DT_CONTRATACAO_PROPOSTA,
    PROP.CD_TIPO_PROPOSTA,
    PROP.DT_EMISSAO_PROPOSTA,
    PROP.DT_VIGENCIA_PROPOSTA_INICIO,
    PROP.DT_VIGENCIA_PROPOSTA_FIM,
    PROP.NO_CERTIFICADO_INDIVIDUAL,
    FP.CD_FATURA_PROPOSTA,
    FP.NO_FATURA_PROPOSTA,
    FP.VL_PAGAR AS 'VALOR PARCELA'n, 
    FP.CD_STATUS AS 'STATUS PARCELA'n,
    FP.DT_VENCIMENTO, 
    PSUB.NO_APOLICE AS 'COD SUB REPRESENTANTE'n,
    PROP.CD_PROPOSTA_GRUPO
from
    vnova.smv_FATURA_PROPOSTA fp
    join vnova.smv_PROPOSTA prop on fp.cd_proposta = PROP.CD_PROPOSTA
    JOIN vnova.smv_PROPOSTA PSUB ON PSUB.CD_PROPOSTA = PROP.CD_PROPOSTA_GRUPO
    join vnova.smv_produto p on PROP.CD_PRODUTO = P.CD_PRODUTO
    join vnova.smv_empresa e on p.cd_empresa = e.cd_empresa
where
    E.CD_CIA_SUSEP = 40
    AND datepart(FP.DT_VENCIMENTO) < today()
    AND PROP.CD_TIPO_PROPOSTA = 'PCER'
    AND FP.CD_STATUS <> 'PAGA';
*desconsiderar as faturas pagas, recusadas, canceladas, inconsistentes, devolvidas, faturadas, arrecadadas;

*COMMIT;
    
********************************************************************************    
* 3. INCREMENTAR ESTRUTURA DO RELATORIO;
    
create index ix1_relatorio_pendencia on RELAT_PARCELAS_PENDENTES2(cd_pcer);
create index ix2_relatorio_pendencia on RELAT_PARCELAS_PENDENTES2(cd_psub);

ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_CONFIGURACAO_COMISSAO num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_CORRETOR num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_CORRETOR_MAPFRE num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_CORRETOR char(250);
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_CORRETOR_AGENCIA num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_AGENCIA num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_AGENCIA_MAPFRE num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_AGENCIA char(250);
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_SUCURSAL num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_SUCURSAL_MAPFRE num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_SUCURSAL char(250);
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_territorial num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_TERRITORIAL_MAPFRE num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_territorial char(250);
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_dgt num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_DGT_MAPFRE num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_dgt char(250);

********************************************************************************
* 4. INCREMENTAR RELATÓRIO: DADOS ESTRUTURA COMERCIAL E COMISSAO;

MERGE INTO RELAT_PARCELAS_PENDENTES2 R 
USING (
    SELECT
        ccp.cd_configuracao_comissao,
        CA.CD_CORRETOR_MAPFRE,
        C.CD_CORRETOR,
        C.NM_CORRETOR,        
        CCP.CD_CONFIG_COMISSAO_PROPOSTA, 
        CCP.CD_PROPOSTA,
        CA.CD_CORRETOR_AGENCIA,
        agencia.cd_ESTRUTURA_COMERCIAL AS CD_AGENCIA,
        agencia.cd_ESTRUTURA_COMERCIAL_MAPFRE AS CD_AGENCIA_MAPFRE,
        agencia.NM_ESTRUTURA_COMERCIAL AS NM_AGENCIA,
        sucursal.cd_ESTRUTURA_COMERCIAL AS CD_SUCURSAL,
        sucursal.cd_ESTRUTURA_COMERCIAL_MAPFRE AS CD_SUCURSAL_MAPFRE,
        sucursal.NM_ESTRUTURA_COMERCIAL AS NM_SUCURSAL,
        territorial.cd_ESTRUTURA_COMERCIAL AS CD_territorial,
        territorial.cd_ESTRUTURA_COMERCIAL_MAPFRE AS CD_territorial_MAPFRE,
        territorial.NM_ESTRUTURA_COMERCIAL AS NM_territorial,
        dgt.cd_ESTRUTURA_COMERCIAL AS CD_dgt,
        dgt.cd_ESTRUTURA_COMERCIAL_MAPFRE AS CD_DGT_MAPFRE,
        dgt.NM_ESTRUTURA_COMERCIAL AS NM_dgt
    FROM 
        vnova.smv_config_comissao_proposta CCP
        JOIN vnova.smv_CONFIGURACAO_COMISSAO CC ON CCP.CD_CONFIGURACAO_COMISSAO = CC.CD_CONFIGURACAO_COMISSAO
        JOIN vnova.smv_CORRETOR C ON CC.CD_CORRETOR = C.CD_CORRETOR
        JOIN vnova.smv_CORRETOR_AGENCIA CA ON CC.CD_CORRETOR_AGENCIA = CA.CD_CORRETOR_AGENCIA
        JOIN vnova.smv_estrutura_comercial agencia on ca.cd_agencia = agencia.CD_ESTRUTURA_COMERCIAL
        JOIN vnova.smv_estrutura_comercial sucursal on agencia.cd_estrut_comerc_superior = sucursal.cd_estrutura_comercial
        JOIN vnova.smv_estrutura_comercial territorial on sucursal.cd_estrut_comerc_superior = territorial.cd_estrutura_comercial
        JOIN vnova.smv_estrutura_comercial dgt on territorial.cd_estrut_comerc_superior = dgt.cd_estrutura_comercial
    ) 
    S ON (
        S.CD_PROPOSTA = R.cd_pcer AND
        S.CD_CONFIG_COMISSAO_PROPOSTA = (
            SELECT MAX(T.CD_CONFIG_COMISSAO_PROPOSTA)
                FROM vnova.smv_config_comissao_proposta T
                WHERE T.CD_PROPOSTA = S.CD_PROPOSTA
        )
    )
WHEN MATCHED THEN
    UPDATE SET 
        R.CD_CONFIGURACAO_COMISSAO = S.CD_CONFIGURACAO_COMISSAO,
        R.CD_CORRETOR_MAPFRE = S.CD_CORRETOR_MAPFRE,
        R.CD_CORRETOR = S.CD_CORRETOR, 
        R.NM_CORRETOR = S.NM_CORRETOR,
        R.CD_CORRETOR_AGENCIA = S.CD_CORRETOR_AGENCIA,
        r.CD_AGENCIA = s.CD_AGENCIA,
        r.nm_AGENCIA = s.nm_AGENCIA,
        r.CD_SUCURSAL = s.CD_SUCURSAL,
        r.NM_SUCURSAL = s.NM_SUCURSAL,
        r.CD_territorial = s.CD_territorial,
        r.NM_territorial = s.NM_territorial,
        r.CD_dgt = s.CD_dgt,
        r.NM_dgt = s.NM_dgt,
        R.CD_AGENCIA_MAPFRE = S.CD_AGENCIA_MAPFRE,
        R.CD_SUCURSAL_MAPFRE = S.CD_SUCURSAL_MAPFRE,
        R.CD_territorial_MAPFRE = S.CD_territorial_MAPFRE,
        R.CD_DGT_MAPFRE = S.CD_DGT_MAPFRE        
    ;

*COMMIT;   

********************************************************************************  
* 5. INCREMENTAR ESTRUTURA DO RELATORIO E PREENCHER SEGURADO;

ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_SEGURADO num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_SEGURADO char(250);

MERGE INTO RELAT_PARCELAS_PENDENTES2 R 
USING (
    SELECT
        pp.cd_proposta,
        p.CD_PESSOA as cd_segurado, 
        p.nm_pessoa as nm_segurado
    FROM 
        vnova.smv_PESSOA_PROPOSTA PP 
        JOIN vnova.smv_pessoa p on pp.cd_pessoa = p.cd_pessoa
    where
        pp.cd_relacao_pessoa_proposta = 'P'
        and pp.cd_tipo_pessoa_proposta = 'SEG'
    ) 
    S ON (
        S.CD_PROPOSTA = R.cd_pcer
    )
WHEN MATCHED THEN
    UPDATE SET 
        R.CD_SEGURADO = S.CD_SEGURADO,
        R.nm_segurado = S.nm_segurado
    ;

*COMMIT;  

********************************************************************************  
* 6. PREENCHER PESSOA SUBESTIPULANTE;
    
MERGE INTO RELAT_PARCELAS_PENDENTES2 R 
USING (
    SELECT
        pp.cd_proposta,
        p.NO_DOCUMENTO_PRINCIPAL as NO_DOC_PRINCIP_PSUB, 
        p.nm_pessoa as NM_PESSOA_PSUB
    FROM 
        vnova.smv_PESSOA_PROPOSTA PP 
        JOIN vnova.smv_pessoa p on pp.cd_pessoa = p.cd_pessoa
    where
        pp.cd_tipo_pessoa_proposta = 'SES'
    ) 
    S ON (
        S.CD_PROPOSTA = R.cd_psub
    )
WHEN MATCHED THEN
    UPDATE SET 
        R.NM_PESSOA_PSUB = S.NM_PESSOA_PSUB,
        R.NO_DOC_PRINCIP_PSUB = S.NO_DOC_PRINCIP_PSUB
    ;

*COMMIT; 

********************************************************************************  
* 7. INCREMENTAR ESTRUTURA E PREENCHER RAMO E GRUPO;

ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD CD_RAMO num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NO_RAMO num;
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NM_RAMO char(70);
ALTER TABLE RELAT_PARCELAS_PENDENTES2 ADD NO_GRUPO_RAMO char(70);

* OBSERVAÇÃO:
* O RAMO SERÁ OBTIDO DA COBERTURA BÁSICA DO PRODUTO
* O GRUPO SERÁ OBTIDO DO PRODUTO DIRETAMENTE E NÃO DA COBERTURA BÁSICA;

MERGE INTO RELAT_PARCELAS_PENDENTES2 R 
USING (
    ** (OBTER O CD_RAMO DA COBERTURA BÁSICA DO PRODUTO)
    SELECT DISTINCT 
          RM_COB_BAS.CD_RAMO,
          RM_COB_BAS.NO_RAMO,
          RM_COB_BAS.NM_RAMO,
          RM_PROD.NO_GRUPO_RAMO,
          PR.CD_PRODUTO
        FROM vnova.smv_PLANO_ITEM PI
          JOIN vnova.smv_PLANO PL ON PI.CD_PLANO = PL.CD_PLANO
          JOIN vnova.smv_PRODUTO PR ON PR.CD_PRODUTO = PL.CD_PRODUTO
          JOIN vnova.smv_EMPRESA E ON E.CD_EMPRESA = PR.CD_EMPRESA
          JOIN vnova.smv_RAMO RM_COB_BAS ON PI.CD_RAMO = RM_COB_BAS.CD_RAMO
          JOIN vnova.smv_RAMO RM_PROD ON PR.CD_RAMO = RM_PROD.CD_RAMO
        WHERE 
          CD_TIPO_PLANO_ITEM = 'BAS' 
          AND E.CD_CIA_SUSEP = 40
    ) 
    S ON (
        S.CD_PRODUTO = R.CD_PRODUTO
    )
WHEN MATCHED THEN
    UPDATE SET 
        R.CD_RAMO = S.CD_RAMO,
        R.NO_RAMO = S.NO_RAMO,
        R.NM_RAMO = S.NM_RAMO,
        R.NO_GRUPO_RAMO = S.NO_GRUPO_RAMO
    ;

*COMMIT;

********************************************************************************    
* EMITIR RELATÓRIO;

select
    CD_PRODUTO AS 'CÓD. PRODUTO'n, 
    NM_PRODUTO AS 'NOME PRODUTO'n, 
    NO_PROPOSTA AS 'NUMERO PROPOSTA'n, 
    DT_CONTRATACAO_PROPOSTA AS 'DT CONTRAT PROPOSTA'n, 
    CD_TIPO_PROPOSTA AS 'TIPO_PROPOSTA'n, 
    DT_EMISSAO_PROPOSTA AS 'DT EMISSAO PROPOSTA'n, 
    DT_VIGENCIA_PROPOSTA_INICIO AS 'INC VIGENCIA'n, 
    DT_VIGENCIA_PROPOSTA_FIM AS 'FIM VIGENCIA'n, 
    NO_CERTIFICADO_INDIVIDUAL AS 'CERTIFICADO INDIVIDUAL'n,
    CD_FATURA_PROPOSTA AS 'CÓDIGO FATURA DB'n,
    NO_FATURA_PROPOSTA AS 'NUMERO PARCELA'n, 
    VL_PAGAR AS 'VALOR PARCELA'n, 
    CD_STATUS AS 'STATUS PARCELA'n, 
    TO_CHAR(DT_VENCIMENTO, 'mm/yyyy') AS 'DT VENCIMENTO'n, 
    NM_PESSOA_PSUB AS 'NOME (SUBESTIPULANTE)'n,  
    NO_DOC_PRINCIP_PSUB AS 'DOCTO. (SUBESTIPULANTE)'n, 
    NO_APOLICE AS 'COD SUB REPRESENTANTE'n,
    CD_CORRETOR_MAPFRE,
    nm_corretor,
    CD_AGENCIA_MAPFRE,
    NM_AGENCIA,
    CD_SUCURSAL_MAPFRE,
    NM_SUCURSAL,
    CD_territorial_MAPFRE,
    NM_territorial,
    CD_DGT_MAPFRE,
    NM_DGT,
    NM_SEGURADO,
    NO_RAMO AS 'RAMO (DA COBERTURA BÁSICA)'n,
    NM_RAMO AS 'RAMO (DA COBERTURA BASICA)'n,
    NO_GRUPO_RAMO AS 'GRUPO RAMO (DO PRODUTO)'n
FROM RELAT_PARCELAS_PENDENTES2;