Fdm_Ref_Ref2_View

@AbapCatalog.sqlViewName: 'FDM_REF_REF2'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #P
@ObjectModel.usageType.sizeCategory: #L
@EndUserText.label: 'References to references to invoices'
define view Fdm_Ref_Ref2_View
  as select
    b.mandt,
    b.bukrs                                              as comp_code,
    b.gjahr                                              as fiscal_year,
    b.belnr                                              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,
    b.manst                                              as dunning_level,
    b.madat                                              as dunning_date,
    a.due_date                                           as due_date,
    b.netdt                                              as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.skfbt,
    b.zlsch,
    case b.shkzg when 'S' then b.wrbtr else -b.wrbtr end as i_w,
    a.p_w,
    a.c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end as i_d,
    a.p_d,
    b.h_waers                                            as i_c,
    a.p_c,
    a.sk1dt,
    a.sk2dt,
    a.zbd1p,
    a.zbd2p,
    a.d_w,
    a.d_d,
    a.d_c,
    b.augdt                                              as payment_date,
    b.mansp                                              as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    b.umskz                                              as spgl_ind,
    b.rebzj,
    b.rebzg,
    b.rebzz,
    a.cleared,
    a.byres
    from   Fdm_Inv_Ref2_View as a
    join bseg              as b on  a.mandt     = b.mandt
                                and a.comp_code = b.bukrs
                                and a.rebzj     = b.gjahr
                                and a.rebzg     = b.belnr
                                and a.rebzz     = b.buzei
                                and (
       a.rebzg != ''
       and a.rebzg != 'V'
     ) and b.filkd = ''
//here come the references to references to invoices with branch filkd

union all select
    b.mandt,
    b.bukrs                                              as comp_code,
    b.gjahr                                              as fiscal_year,
    b.belnr                                              as invoice_no,
    b.buzei                                              as doc_item,
    b.kunnr                                              as customer,
    b.filkd                                              as coll_debtor,
    b.h_bldat                                            as inv_date,
    b.h_budat                                            as posting_date,
    b.manst                                              as dunning_level,
    b.madat                                              as dunning_date,
    a.due_date                                           as due_date,
    b.netdt                                              as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.skfbt,
    b.zlsch,
    case b.shkzg when 'S' then b.wrbtr else -b.wrbtr end as i_w,
    a.p_w,
    a.c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end as i_d,
    a.p_d,
    b.h_waers                                            as i_c,
    a.p_c,
    a.sk1dt,
    a.sk2dt,
    a.zbd1p,
    a.zbd2p,
    a.d_w,
    a.d_d,
    a.d_c,
    b.augdt                                              as payment_date,
    b.mansp                                              as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    b.umskz                                              as spgl_ind,
    b.rebzj,
    b.rebzg,
    b.rebzz,
    a.cleared,
    a.byres
  from   Fdm_Inv_Ref2_View as a
    join bseg              as b on  a.mandt     = b.mandt
                                and a.comp_code = b.bukrs
                                and a.rebzj     = b.gjahr
                                and a.rebzg     = b.belnr
                                and a.rebzz     = b.buzei
                                and (
       a.rebzg != ''
       and a.rebzg != 'V'
     ) and b.filkd <> ''
//here come the references to invoices

  union all select
    mandt,
    comp_code,
    fiscal_year,
    invoice_no,
    doc_item,
    customer,
    coll_debtor,
    inv_date,
    posting_date,
    dunning_level,
    dunning_date,
    due_date,
    inv_due_date,
    xref1,
    xref2,
    xref3,
    skfbt,
    zlsch,
    i_w,
    p_w,
    c_w,
    i_d,
    p_d,
    i_c,
    p_c,
    sk1dt,
    sk2dt,
    zbd1p,
    zbd2p,
    d_w,
    d_d,
    d_c,
    payment_date,
    fdm_mansp,
    saknr,
    h_monat,
    pswsl,
    pswbt,
    vbeln,
    bschl,
    sgtxt,
    spgl_ind,
    rebzj,
    'ORI' as rebzg,
    rebzz,
    cleared,
    byres
  from Fdm_Inv_Ref2_View
  where
       rebzg = ''
    or rebzg = 'V'
//here come the open invoices without any payment so far

  union all select
    b.mandt,
    b.bukrs                                               as comp_code,
    b.gjahr                                               as fiscal_year,
    b.belnr                                               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,
    b.manst                                               as dunning_level,
    b.madat                                               as dunning_date,
    b.netdt                                               as due_date,
    b.netdt                                               as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.skfbt,
    b.zlsch,
    case b.shkzg when 'S' then b.wrbtr else -b.wrbtr end  as i_w,
    cast(0 as wrbtr)                                      as p_w,
    cast(0 as wrbtr)                                      as c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end  as i_d,
    cast(0 as dmbtr)                                      as p_d,
    b.h_waers                                             as i_c,
    '     '                                               as p_c,
    b.sk1dt,
    b.sk2dt,
    b.zbd1p,
    b.zbd2p,
    cast (0 as wrbtr)                                     as d_w,
    cast (0 as dmbtr)                                     as d_d,
    '     '                                               as d_c,
    cast('00000000' as abap.dats)                         as payment_date,
    b.mansp                                               as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    b.umskz                                               as spgl_ind,
    b.rebzj,
    'OPI'                                                 as rebzg,
    b.rebzz,
    ' '                                                   as cleared,
    ' '                                                   as byres
  from bseg as b
       left outer join fdm_bstat_range as f on b.bukrs = f.bukrs and b.umskz = f.umskz
  where
         koart = 'D'
    and ( ( h_bstat = 'S' and b.umskz = 'F' ) or h_bstat = '' )
    and  augbl = ''
    and(
         rebzg = ''
      or rebzg = 'V'
    ) and filkd = ''
    and f.fdm_sgl_excl_lis is null or f.fdm_sgl_excl_lis = ' '
  //here come the open invoices with customer as branch without any payment so far

  union all select
    b.mandt,
    b.bukrs                                               as comp_code,
    b.gjahr                                               as fiscal_year,
    b.belnr                                               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,
    b.manst                                               as dunning_level,
    b.madat                                               as dunning_date,
    b.netdt                                               as due_date,
    b.netdt                                               as inv_due_date,
    b.xref1,
    b.xref2,
    b.xref3,
    b.skfbt,
    b.zlsch,
    case b.shkzg when 'S' then b.wrbtr else -b.wrbtr end  as i_w,
    cast(0 as wrbtr)                                      as p_w,
    cast(0 as wrbtr)                                      as c_w,
    case b.shkzg when 'S' then b.dmbtr else -b.dmbtr end  as i_d,
    cast(0 as dmbtr)                                      as p_d,
    b.h_waers                                             as i_c,
    '     '                                               as p_c,
    b.sk1dt,
    b.sk2dt,
    b.zbd1p,
    b.zbd2p,
    cast (0 as wrbtr)                                     as d_w,
    cast (0 as dmbtr)                                     as d_d,
    '     '                                               as d_c,
    cast('00000000' as abap.dats)                         as payment_date,
    b.mansp                                               as fdm_mansp,
    b.saknr,
    b.h_monat,
    b.pswsl,
    b.pswbt,
    b.vbeln,
    b.bschl,
    b.sgtxt,
    b.umskz                                               as spgl_ind,
    b.rebzj,
    'OPI'                                                 as rebzg,
    b.rebzz,
    ' '                                                   as cleared,
    ' '                                                   as byres
  from bseg as b
       left outer join fdm_bstat_range as f on b.bukrs = f.bukrs and b.umskz = f.umskz
  where
         koart = 'D'
    and ( ( h_bstat = 'S' and b.umskz = 'F' ) or h_bstat = '' )
    and  augbl = ''
    and(
         rebzg = ''
      or rebzg = 'V'
    )  and filkd <> ''
    and f.fdm_sgl_excl_lis is null or f.fdm_sgl_excl_lis = ' '















/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FDM_INV_REF2_VIEW",
"BSEG",
"FDM_BSTAT_RANGE"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/