Anuidades Não Cobradas

    0

    0

    Gonçalo Duque

    select c.ID as 'ID Cartão Atual', c.CREATE_DATE as 'Data Criação Cartão Atual', c.FK_CARD_ENTITY_STATUS_CODE as 'Estado Cartão Atual', e.DESCRIPTION as 'Estado Cartão Atual (Desc.)', c_previous.ID as 'ID Cartão Anterior', c_previous.CREATE_DATE as 'Data Criação Cartão Anterior', c_previous.FK_CARD_ENTITY_STATUS_CODE as 'Estado Cartão Anterior', con.FK_ACM_PRODUCT_CODE as 'Produto', c.FK_ACM_CONTRACT_ID as 'Contrato', a.ACCOUNT_NUMBER as 'Conta', con_a.ATTRIBUTE_KEY as 'Data da Próxima Anuidade', con_a.ATTRIBUTE_VALUE as 'Data da Última Anuidade'

    
    
    
    select
      c.id as 'ID Cartão Atual', 
      c.create_date as 'Data Criação Cartão Atual',
      c.fk_card_entity_status_code as 'Estado Cartão Atual',
      e.description as 'Estado Cartão Atual (Desc.)',
      c_previous.id as 'ID Cartão Anterior', 
      c_previous.create_date as 'Data Criação Cartão Anterior',
      c_previous.fk_card_entity_status_code as 'Estado Cartão Anterior',
      con.fk_acm_product_code as 'Produto',
      c.fk_acm_contract_id as 'Contrato',
      a.account_number as 'Conta', 
      con_a.attribute_key, 
      con_a.attribute_value as 'Data da Próxima Anuidade',
      con_a_p.attribute_key, 
      con_a_p.attribute_value as 'Data da Última Anuidade'
    from (
      select * , ROW_NUMBER() OVER (PARTITION BY fk_acm_contract_id ORDER BY create_date desc) row_num
      from acm_card c with (nolock)
      where fk_acm_contract_id is not null
    ) c
      left join acm_contract con with (nolock)
        on con.id = c.fk_acm_contract_id
      left join acm_contract_attribute con_a with (nolock)
        on con_a.fk_acm_contract_id = con.id 
        and con_a.attribute_key = 'NEXT_ANNUITY'
      left join acm_contract_attribute con_a_p with (nolock)
        on con_a_p.fk_acm_contract_id = con.id 
        and con_a_p.attribute_key = 'PREVIOUS_ANNUITY'
      left join acm_card c_previous with (nolock)
        on c_previous.id = c.fk_previous_card_id
      left join acm_entity_status e with (nolock)
        on e.entity_code = 'CARD' and e.status_code = c.fk_card_entity_status_code
      left join acm_account a with (nolock)
        on a.id = c.fk_acm_account_id
    where c.row_num = '1'
    and c.fk_card_entity_status_code not in ('PD', 'I2', 'I9', 'I5', '09','I16','0B','08','I12','I9')
    and con_a.attribute_value is null
    order by con_a_p.attribute_value asc
    
    
    
    
    Codiga Logo
    Codiga Hub
    • Rulesets
    • Playground
    • Snippets
    • Cookbooks
    soc-2 icon

    We are SOC-2 Compliance Certified

    G2 high performer medal

    Codiga – All rights reserved 2022.