Fdm_Inv_Ref2_View

@AbapCatalog.sqlViewName: 'FDM_INV_REF2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
@ObjectModel.usageType.sizeCategory: #L
@EndUserText.label: 'Open Invoices and References to invoices'
define view Fdm_Inv_Ref2_View
  as select
//references to invoices without branch

    b.mandt,
    b.bukrs                                               as comp_code,
    b.h_hwaer                                             as local_currency,
    b.gjahr                                               as fiscal_year,
    case a.umskz
      when 'F' then a.belnr
      else b.belnr end                                    as invoice_no,
    b.buzei                                               as doc_item,
    b.kunnr                                               as customer,
    b.kunnr                                               as coll_debtor,
    b.h_bldat                                             as inv_date,
    b.h_budat                                             as posting_date,
    case a.rebzt when 'V' then a.manst else b.manst end   as dunning_level,
    // residuals: take dunning values from new open item

    case a.rebzt when 'V' then a.madat else b.madat end   as dunning_date,
    case a.rebzt
       when 'Z' then b.netdt  //partial payment - invoice still open

    else a.netdt end                                      as due_date,
    b.netdt                                               as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.zlsch,
    b.skfbt,
    case b.shkzg
       when 'S' then b.wrbtr else -b.wrbtr
    end as                                                i_w,
    case a.rebzt
       when 'V' then case
          when a.h_waers = b.h_waers then case a.shkzg
            when 'H' then case b.shkzg
              when 'S' then -a.wrbtr - b.wrbtr
              else -a.wrbtr + b.wrbtr end
            else case b.shkzg
              when 'S' then a.wrbtr - b.wrbtr
              else a.wrbtr + b.wrbtr end
            end
          else cast(0 as wrbtr) end
       when 'G' then cast(0 as wrbtr)
       when 'H' then cast(0 as wrbtr)
       else case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
    end                                                  as p_w,
    case a.rebzt
       when 'G' then case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
       when 'H' then case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
       else cast(0 as wrbtr)
    end                                                  as c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end as i_d,
    case a.rebzt when 'V' then a.dmbtr - b.dmbtr
                          else
    case a.shkzg when 'S' then a.dmbtr else -a.dmbtr end
    end                                                  as p_d,
    b.h_waers                                            as i_c,
    a.h_waers                                            as p_c,
    b.h_hwaer                                            as d_c,
    case when k.wrbtr is null then cast(0 as wrbtr)
       else k.wrbtr  end                                 as d_w,
    case when k.dmbtr is null then cast(0 as wrbtr)
       else k.dmbtr end                                  as d_d,
    b.augdt,
    a.sk1dt,
    a.sk2dt,
    a.zbd1p,
    a.zbd2p,
    a.h_budat                                             as payment_date,
    b.mansp                                               as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    a.umskz                                               as spgl_ind,
    a.gjahr                                               as gjahr,
    a.bukrs                                               as bukrs,
    a.belnr                                               as belnr,
    b.rebzj                                               as rebzj,
    b.rebzg                                               as rebzg,
    b.rebzz                                               as rebzz,
    b.augbl                                               as augbl,
    b.auggj                                               as auggj,
    case a.rebzt when 'V' then 'X' else ' ' end           as byres,
    case b.augbl when '' then ' ' else 'X' end            as cleared
  from         bseg as a
    inner join bseg as b on  a.mandt = b.mandt
                         and a.bukrs = b.bukrs
                         and a.rebzj = b.gjahr
                         and a.rebzg = b.belnr
                         and a.rebzz = b.buzei
                         and a.koart = 'D'
                         and ( ( a.h_bstat = 'S' and a.umskz = 'F' ) or a.h_bstat = '' )
                         and a.augbl = ''
                         and (
       a.rebzg != ''
       and a.rebzg != 'V'
     ) and b.filkd = ''
       left outer join fdm_bstat_range as f on a.bukrs = f.bukrs and a.umskz = f.umskz
       left outer join FIN_DISCOUNTS_VIEW as k on a.mandt = k.mandt
                           and a.bukrs = k.bukrs
                           and a.belnr = k.belnr
                           and a.gjahr = k.gjahr
                           and a.rebzt = 'V'
   where f.fdm_sgl_excl_lis is null or f.fdm_sgl_excl_lis = ' '
   union all select
//references to branch invoices, treat branch (filkd) as customer

    b.mandt,
    b.bukrs                                               as comp_code,
    b.h_hwaer                                             as local_currency,
    b.gjahr                                               as fiscal_year,
    case a.umskz
      when 'F' then a.belnr
      else b.belnr end                                    as invoice_no,
    b.buzei                                               as doc_item,
//    b.filkd                                               as customer,

    b.kunnr                                               as customer,
    b.filkd                                               as coll_debtor,
    b.h_bldat                                             as inv_date,
    b.h_budat                                             as posting_date,
    case a.rebzt when 'V' then a.manst else b.manst end   as dunning_level,
    case a.rebzt when 'V' then a.madat else b.madat end   as dunning_date,
    case a.rebzt
       when 'Z' then b.netdt  //partial payment - invoice still open

    else a.netdt end                                      as due_date,
    b.netdt                                               as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.zlsch,
    b.skfbt,
    case b.shkzg
       when 'S' then b.wrbtr else -b.wrbtr
    end as                                                i_w,
    case a.rebzt
       when 'V' then case
          when a.h_waers = b.h_waers then case a.shkzg
            when 'H' then case b.shkzg
              when 'S' then -a.wrbtr - b.wrbtr
              else -a.wrbtr + b.wrbtr end
            else case b.shkzg
              when 'S' then a.wrbtr - b.wrbtr
              else a.wrbtr + b.wrbtr end
            end
          else cast(0 as wrbtr) end
       when 'G' then cast(0 as wrbtr)
       when 'H' then cast(0 as wrbtr)
       else case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
    end                                                  as p_w,
    case a.rebzt
       when 'G' then case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
       when 'W' then case a.shkzg
          when 'S' then a.wrbtr
          else -a.wrbtr
          end
       else cast(0 as wrbtr)
    end                                                  as c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end as i_d,
    case a.rebzt when 'V' then a.dmbtr - b.dmbtr
                          else
    case a.shkzg when 'S' then a.dmbtr else -a.dmbtr end
    end                                                  as p_d,
    b.h_waers                                            as i_c,
    a.h_waers                                            as p_c,
    b.h_hwaer                                            as d_c,
    case when k.wrbtr is null then cast(0 as wrbtr)
       else k.wrbtr  end                                 as d_w,
    case when k.dmbtr is null then cast(0 as wrbtr)
       else k.dmbtr end                                  as d_d,
    b.augdt,
    a.sk1dt,
    a.sk2dt,
    a.zbd1p,
    a.zbd2p,
    a.h_budat                                             as payment_date,
    b.mansp                                               as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    a.umskz                                               as spgl_ind,
    a.gjahr                                               as gjahr,
    a.bukrs                                               as bukrs,
    a.belnr                                               as belnr,
    b.rebzj                                               as rebzj,
    b.rebzg                                               as rebzg,
    b.rebzz                                               as rebzz,
    b.augbl                                               as augbl,
    b.auggj                                               as auggj,
    case a.rebzt when 'V' then 'X' else ' ' end           as byres,
    case b.augbl when '' then ' ' else 'X' end            as cleared
  from         bseg as a
    inner join bseg as b on  a.mandt = b.mandt
                         and a.bukrs = b.bukrs
                         and a.rebzj = b.gjahr
                         and a.rebzg = b.belnr
                         and a.rebzz = b.buzei
                         and a.koart = 'D'
                         and ( a.h_bstat = 'S' or a.h_bstat = '' )
                         and a.augbl = ''
                         and (
       a.rebzg != ''
       and a.rebzg != 'V'
     ) and b.filkd <> ''
       left outer join fdm_bstat_range as f on a.bukrs = f.bukrs and a.umskz = f.umskz
       left outer join FIN_DISCOUNTS_VIEW as k on a.mandt = k.mandt
                           and a.bukrs = k.bukrs
                           and a.belnr = k.belnr
                           and a.gjahr = k.gjahr
                           and a.rebzt = 'V'
       where f.fdm_sgl_excl_lis is null or f.fdm_sgl_excl_lis = ' '