PIR_ALL_ATTRIBUTES

@AbapCatalog.sqlViewName: 'PIR_V_ALL_ATT'
@ClientHandling.algorithm: #AUTOMATED
@AbapCatalog.compiler.compareFilter: true
@EndUserText.label: 'All Attributes from PBED and PBED_DRAFT'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #M, dataClass: #TRANSACTIONAL}
-- Find all PIR attributes from PBED/PBIM/PBID/PBED_DRAFT
-- for some periods (PDATU) PBEDs exist and no PBED_DRAFT or vice versa
-- The way we read them we return the PIR attributes with the unique key from below
define view Pir_All_Attributes as
-- The ones in PBED_DRAFT, but not in PBED --> only draft quantity
select from Pir_Union_Head as pir_head
    inner join pbed_draft as  pbed_draft
      on pbed_draft.bdzei = pir_head.bdzei
    left outer join pbed as  pbed
      on  pbed.bdzei = pir_head.bdzei
      and pbed.pdatu = pbed_draft.pdatu
    left outer to one join scal_tt_date as cal
      on cal.calendardate = pbed_draft.pdatu
{
  key   pir_head.matnr,
  key   pir_head.werks,
  key   pir_head.berid,
  key   pir_head.bedae,
  key   pir_head.versb,
  key   pbed_draft.pdatu,
        pir_head.pbdnr,
        case pbed_draft.entlu
          when '3' then cal.firstdayofmonthdate -- month -> 1.
          when '2' then dats_add_days(pbed_draft.pdatu, 1 - cast(cal.weekday as abap.int4), 'UNCHANGED') -- week: subtract weekday to reach Monday
          else pbed_draft.pdatu
        end as per_sdat,

        case pbed_draft.entlu
          when '3' then cal.lastdayofmonthdate  -- month -> 30. or 31. etc
          when '2' then dats_add_days(pbed_draft.pdatu, 7 - cast(cal.weekday as abap.int4), 'UNCHANGED')  -- week: add 7-weekday to reach Sunday
        end as per_edat,

        '' as loevr,
        pbed_draft.meins,
        pbed_draft.draft_qty,
        cast(0 as abap.dec(13,3)) as plan_qty,
        cast(0 as abap.dec(13,3)) as withdr_qty,
        cast(0 as abap.dec(13,3)) as open_qty,
        '' as acc_past,
        '' as released,
        pbed_draft.bdzei,
        pbed_draft.aenam,
        'X' as vervs,         -- draft is always maintained in the active version
        case pbed_draft.entlu
          when '1' then 'D'
          when '2' then 'W'
          when '3' then 'M'
          when '4' then 'P'
          when '5' then 'C'
        end as perkz_item,
        pbed_draft.laeda
}
where pbed.pdatu is null -- key exists in PBED_DRAFT, but not in PBED

union all
-- the ones in PBED, but not in PBED_DRAFT --> no draft quantity, but released
select from Pir_Union_Head as pir_head
    inner join pbed as  pbed
      on pbed.bdzei = pir_head.bdzei
    left outer join pbed_draft as  pbed_draft
      on  pbed_draft.bdzei = pir_head.bdzei
      and pbed_draft.pdatu = pbed.pdatu
    left outer to one join scal_tt_date as cal
      on cal.calendardate = pbed.pdatu
{
  key   pir_head.matnr,
  key   pir_head.werks,
  key   pir_head.berid,
  key   pir_head.bedae,
  key   pir_head.versb,
  key   pbed.pdatu,
        pir_head.pbdnr,  
        case pbed.entli
          when '3' then cal.firstdayofmonthdate -- month -> 1.
          when '2' then dats_add_days(pbed.pdatu, 1 - cast(cal.weekday as abap.int4), 'UNCHANGED') -- week: subtract weekday to reach Monday
          else pbed.pdatu
        end as per_sdat,

        case pbed.entli
          when '3' then cal.lastdayofmonthdate  -- month -> 30. or 31. etc
          when '2' then dats_add_days(pbed.pdatu, 7 - cast(cal.weekday as abap.int4), 'UNCHANGED')  -- week: add 7-weekday to reach Sunday
        end as per_edat,

        pbed.loevr,
        pbed.meins,
        case when pbed.loevr = '' then cast(pbed.plnmg + pbed.entmg as abap.dec(13,3))
             else cast(0 as abap.dec(13,3))
        end as draft_qty,   -- we take this a draft quantity, because there is nothing in draft for this PDATU
        case when pbed.loevr = '' then cast(pbed.plnmg + pbed.entmg as abap.dec(13,3))
             else cast(0 as abap.dec(13,3))
        end as plan_qty,    -- original planned quantity
        case when pbed.loevr = '' then pbed.entmg
             else cast(0 as abap.dec(13,3))
        end as withdr_qty,
        case when pbed.loevr = '' then pbed.plnmg
             else cast(0 as abap.dec(13,3))
        end as open_qty,    -- open = planned in PIR(!)
        pbed.acc_past,
        'X' as released,
        pbed.bdzei,
        pbed.aenam,
        pir_head.vervs,
        case pbed.entli
          when '1' then 'D'
          when '2' then 'W'
          when '3' then 'M'
          when '4' then 'P'
          when '5' then 'C'
        end as perkz_item,
        pbed.laeda
}
where pbed_draft.pdatu is null

union all
-- the ones in both, but only one
select from Pir_Union_Head as pir_head
    inner join pbed_draft as  pbed_draft
      on pbed_draft.bdzei = pir_head.bdzei
    inner join pbed as  pbed
      on  pbed.bdzei = pir_head.bdzei
      and pbed.pdatu = pbed_draft.pdatu
    left outer to one join scal_tt_date as cal
      on cal.calendardate = pbed.pdatu
{
  key   pir_head.matnr,
  key   pir_head.werks,
  key   pir_head.berid,
  key   pir_head.bedae,
  key   pir_head.versb,
  key   pbed.pdatu,
        pir_head.pbdnr,
        case pbed.entli
          when '3' then cal.firstdayofmonthdate -- month -> 1.
          when '2' then dats_add_days(pbed.pdatu, 1 - cast(cal.weekday as abap.int4), 'UNCHANGED') -- week: subtract weekday to reach Monday
          else pbed.pdatu
        end as per_sdat,

        case pbed.entli
          when '3' then cal.lastdayofmonthdate  -- month -> 30. or 31. etc
          when '2' then dats_add_days(pbed.pdatu, 7 - cast(cal.weekday as abap.int4), 'UNCHANGED')  -- week: add 7-weekday to reach Sunday
        end as per_edat,

        pbed.loevr,
        pbed.meins,
        pbed_draft.draft_qty    as draft_qty,   -- we take the draft always from pbed_draft
        case when pbed.loevr = '' then cast(pbed.plnmg + pbed.entmg as abap.dec(13,3))
             else cast(0 as abap.dec(13,3))
        end as plan_qty,    -- original planned quantity
        case when pbed.loevr = '' then pbed.entmg
             else cast(0 as abap.dec(13,3))
        end as withdr_qty,
        case when pbed.loevr = '' then pbed.plnmg
             else cast(0 as abap.dec(13,3))
        end as open_qty,    -- open = planned in PIR(!)
        pbed.acc_past,
        'X' as released,
        pbed.bdzei,
        pbed.aenam,
        pir_head.vervs,
        case pbed.entli
          when '1' then 'D'
          when '2' then 'W'
          when '3' then 'M'
          when '4' then 'P'
          when '5' then 'C'
        end as perkz_item,
        pbed.laeda
}