SELECT
      xadma.distrib_id
      , arra.receivable_application_id
      , arps.amount_due_original
      , (arra.amount_applied
                      * COALESCE(arra.trans_to_receipt_rate, 1) )
      , arra.application_type
      , arra.customer_trx_id
      , CASE WHEN  xactla.period_id IS NOT NULL
             OR  ( xactla.creditor IS NOT NULL
              AND xadma.subtipo_item  IN ( 'Renta'
                                         , 'Renta Interina'
                                         , 'Intereses'
                                         , 'Capital') )   THEN
                 xadma.amount_line
                  * ((arra.amount_applied
                      * COALESCE(arra.trans_to_receipt_rate, 1) )
                      /(SELECT
                           SUM(tbl_amount.amount_line
                            + tbl_amount.amount_line_iva) tot_invoice
                        FROM  XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
                        WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
               ELSE
                 0
               END  mtocredi_siniva
      , CASE WHEN  xactla.period_id IS NOT NULL
              OR  (xactla.creditor IS NOT NULL
              AND xadma.subtipo_item  IN ( 'Renta'
                                         , 'Renta Interina'
                                         , 'Intereses'
                                         , 'Capital')) THEN
               xadma.amount_line_iva
                * ((arra.amount_applied
                      * COALESCE(arra.trans_to_receipt_rate, 1) )
                   /(SELECT
                           SUM(tbl_amount.amount_line
                            + tbl_amount.amount_line_iva) tot_invoice
                        FROM  XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
                        WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
             ELSE
               0
             END mtocredi_iva
      , CASE WHEN xactla.period_id IS NOT NULL
              OR  (xactla.creditor IS NOT NULL
                AND xadma.subtipo_item  IN ( 'Renta'
                                           , 'Renta Interina'
                                           , 'Intereses'
                                           , 'Capital'))  THEN
               xactla.creditor
             ELSE
               ''
             END creditor
      , CASE WHEN (xactla.creditor IS NOT NULL
                    AND xactla.period_id IS NULL
                    AND (xadma.subtipo_item NOT IN ( 'Renta'
                                             , 'Renta Interina'
                                             , 'Intereses'
                                             , 'Capital')
                       OR xadma.subtipo_item IS NULL ))
               OR xactla.creditor IS NULL   THEN
               xadma.amount_line
                * ((arra.amount_applied
                      * COALESCE(arra.trans_to_receipt_rate, 1) )
                   /(SELECT
                       SUM(tbl_amount.amount_line
                        + tbl_amount.amount_line_iva) tot_invoice
                    FROM  XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
                    WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
             ELSE
               0
             END mtotip_siniva
      , CASE WHEN (xactla.creditor IS NOT NULL
                    AND xactla.period_id IS NULL
                    AND (xadma.subtipo_item NOT IN ( 'Renta'
                                             , 'Renta Interina'
                                             , 'Intereses'
                                             , 'Capital')
                        OR xadma.subtipo_item IS NULL  ))
               OR xactla.creditor IS NULL   THEN
               xadma.amount_line_iva
                * ((arra.amount_applied
                      * COALESCE(arra.trans_to_receipt_rate, 1) )
                  /(SELECT
                           SUM(tbl_amount.amount_line
                            + tbl_amount.amount_line_iva) tot_invoice
                        FROM  XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
                        WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
             ELSE
               0
             END mtotip_iva
      , xadma.cia
      , arra.apply_date
      , arra.creation_date
      , REPLACE(acr.receipt_number, '|', '') receipt_number
      , acr.receipt_date
      , CASE WHEN cban.bank_account_name LIKE 'BBVA-00201258826-MXN_FID%' THEN
             'BBVA-00201258826-MXN_FID'
           ELSE
              cban.bank_account_name
           END                          bank_account_name
    FROM xxtip.xxtip_ar_distrib_mtos_all      xadma
      , ar_payment_schedules_all              arps
      , ar_receivable_applications_all        arra
      , ar_cash_receipts_all                  acr
      , ar_receipt_method_accounts_all        arma
      , ce_bank_acct_uses_all                 ceba
      , ce_bank_accounts                      cban
      , xxtip.xxtip_ar_cust_trx_lines_all     xactla
    WHERE arps.customer_trx_id                  = xadma.customer_trx_id
      AND arra.applied_payment_schedule_id      = arps.payment_schedule_id
      AND acr.cash_receipt_id                   = arra.cash_receipt_id
      AND arma.remit_bank_acct_use_id           = acr.remit_bank_acct_use_id
      AND arma.receipt_method_id                = acr.receipt_method_id
      AND ceba.bank_acct_use_id                 = arma.remit_bank_acct_use_id
      AND cban.bank_account_id                  = ceba.bank_account_id
      AND xadma.customer_trx_line_id            = xactla.customer_trx_line_id
      AND xadma.trx_number                      = xactla.trx_number
      AND xadma.org_id                          = CASE WHEN TO_NUMBER(xactla.org_id) = 84 THEN
                                                         85
                                                       ELSE
                                                         TO_NUMBER(xactla.org_id)
                                                       END
      AND arra.amount_applied                   != 0