class zmes_cl_odata_dac definition
  public
  final
  create public .

  public section.
    interfaces IF_AMDP_MARKER_HDB .

    class-methods: get_odata_header for table function zmes_od_doc_hdr.

  protected section.
  private section.
ENDCLASS.



CLASS ZMES_CL_ODATA_DAC IMPLEMENTATION.


method get_odata_header by database function
      FOR HDB
      LANGUAGE SQLSCRIPT
      OPTIONS READ-ONLY using zmes_td_doc_hdr zmes_td_docflown zmes_td_doc_pos dd07t zmes_td_doc_stat.

      declare clnt CONSTANT NVARCHAR(3) := SESSION_context('CLIENT');


      out_data = with
        chdr as ( select hdr.mandt,
                         hdr.docid,
                         aufnr,
                         doc_type,
                         lotid,
                         doc_type as order_type,
                         hdr.localization,
                         start_date,
                         description,
                         output_matnr,
                         output_matnr_name,
                         doc_status,
                         d7.ddtext as status_descr,
                         workcenter,
                         workcenter_descr,
                         st.changed_by as closed_by
                         from zmes_td_doc_hdr as hdr
                            left outer join dd07t as d7 on d7.domname    = 'ZMES_DOC_TYPE_D' and
                                                           d7.ddlanguage = 'L' and
                                                           d7.domvalue_l = hdr.doc_status
                            left outer join zmes_td_doc_stat as st on st.docid  = hdr.docid and
                                                                      st.status = 'C' and
                                                                      st.itemid = ''

                            where doc_category = 'Z' ),
        pquanrepl as ( select hdr.docid, pos_repl.itemid,
                          ( pos_repl.quan * ( -1 ) ) + sum( abs( pos.quan ) ) as planned_quantity
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            inner join zmes_td_doc_pos as pos_repl on pos_repl.docid = hdr.docid and pos_repl.itemid
                                                                                                     = pos.source_item

                            where pos.source_item <> ''

                            group by hdr.docid, pos_repl.itemid, pos_repl.quan ),

        pquan as ( select hdr.docid,
                          sum( quan ) as planned_quantity,
                          sum( repl.planned_quantity ) as repl_quantity,
                          item_type
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            left outer join pquanrepl as repl on repl.docid = hdr.docid and pos.itemid = repl.itemid
                            where pos.replacment = ''
                            group by hdr.docid, item_type ),
        poutquan as ( select hdr.docid,
                          sum( quan ) as planned_quantity,
                          sum(  repl.planned_quantity ) as repl_quantity
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            left outer join pquanrepl as repl on repl.docid = hdr.docid and pos.itemid = repl.itemid
                          where item_type = 'O' and
                          pos.replacment = ''
                            group by hdr.docid ),
        pmopquan as ( select hdr.docid,
                          sum( quan  ) as planned_quantity,
                          sum(  repl.planned_quantity ) as repl_quantity
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            left outer join pquanrepl as repl on repl.docid = hdr.docid and pos.itemid = repl.itemid
                          where item_type = 'O' and main_output = 'X' and
                          pos.replacment = ''

                            group by hdr.docid ),
        pinquan as ( select hdr.docid,
                          sum( quan ) as planned_quantity,
                          sum(  repl.planned_quantity ) as repl_quantity
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            left outer join pquanrepl as repl on repl.docid = hdr.docid and pos.itemid = repl.itemid
                          where item_type = 'I' and
                                    pos.replacment = ''
                            group by hdr.docid ),
         pinquanwnorm as ( select hdr.docid,
                          sum( quan ) as planned_quantity,
                          sum(  repl.planned_quantity ) as repl_quantity
                          from chdr as hdr
                            inner join zmes_td_doc_pos as pos on pos.docid = hdr.docid
                            left outer join pquanrepl as repl on repl.docid = hdr.docid and pos.itemid = repl.itemid
                          where item_type = 'I' and pos.consume_type <> 'N'
                          and pos.replacment = ''

                            group by hdr.docid ),
        dquan as ( select hdr.docid,
                          sum( quan ) as done_quantity,
                          move_type
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      df.issue_code = '' and
                                      df.reverse_move = ''
                                group by hdr.docid, move_type ),
       dquan_i as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      df.issue_code = '' and
                                      po.item_type = 'I' and
                                      df.reverse_move = ''
                                group by hdr.docid ),
      dquan_iwn as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      df.issue_code = '' and
                                      po.item_type = 'I' and
                                      po.consume_type <> 'N' and
                                      df.reverse_move = ''
                                group by hdr.docid ),
        dquan_o as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      po.item_type = 'O' and
                                      df.reverse_move = ''
                                group by hdr.docid ),
        dquan_mo as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      po.item_type = 'O' and
                                      po.main_output = 'X' and
                                      df.reverse_move = ''
                                group by hdr.docid ),

         dquan_mowic as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      df.issue_code = '' and
                                      po.item_type = 'O' and
                                      po.main_output = 'X' and
                                      df.reverse_move = ''
                                group by hdr.docid ),
        dquan_owic as ( select hdr.docid,
                          sum( df.quan ) as done_quantity
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            inner join zmes_td_doc_pos as po on po.docid = hdr.docid and
                                                          po.itemid = df.itemid
                                where reversed = '' and
                                      df.gi_top_hu = '' and
                                      df.gr_top_hu = '' and
                                      df.issue_code = '' and
                                      po.item_type = 'O' and
                                      df.reverse_move = ''
                                group by hdr.docid ),


        dfreg  as ( select distinct hdr.docid,
                          min( reg_date ) as first_reg,
                          max( reg_date ) as end_date
                          from chdr as hdr
                            inner join zmes_td_docflown as df on df.docid = hdr.docid
                            group by hdr.docid ),
        max_gr_df as (  select distinct hdr.docid,
                               max( df.created_at ) as created_at
                               from chdr as hdr
                               left outer join zmes_td_docflown as df on df.docid = hdr.docid
                               where move_type = 'GR'
                               group by hdr.docid ),
        bvfdat as ( select distinct hdr.docid,
                                    df.vfdat as end_date
                                    from chdr as hdr
                                    inner join max_gr_df as mdf on mdf.docid = hdr.docid
                                    left outer join zmes_td_docflown as df on df.docid = hdr.docid and df.created_at = mdf.created_at and
                                     df.move_type = 'GR' )



        select distinct hdr.mandt,
                        hdr.docid,
                        hdr.lotid,
                        hdr.order_type as doc_type,
                        hdr.localization,
                        hdr.aufnr,
                        hdr.description,
                        hdr.start_date,
                        hdr.output_matnr as matnr,
                        hdr.output_matnr_name as matnr_name,
                        pqo.planned_quantity as plan_quan_o,
                        case
                            when dqo.done_quantity is not null then dqo.done_quantity
                            else 0
                        end as done_quan_o,
                        hdr.doc_status as status,
                        hdr.status_descr,
                        pqi.planned_quantity as plan_quan_i,
                        case
                            when dqi.done_quantity is not null then dqi.done_quantity
                            else 0
                        end as done_quan_i,
                        first_reg,
                        bdat.end_date,
                        hdr.workcenter as work_center,
                        hdr.workcenter_descr as work_center_name,
                        case
                            when pmopquan.repl_quantity is not null then poutquan.planned_quantity +
                                case
                                    when pmopquan.repl_quantity > 0 then pmopquan.repl_quantity
                                    else 0
                                end
                            else poutquan.planned_quantity
                        end as output_pquan,
                        case
                            when pmopquan.repl_quantity is not null then pmopquan.planned_quantity +
                                case
                                    when pmopquan.repl_quantity > 0 then pmopquan.repl_quantity
                                    else 0
                                end
                            else pmopquan.planned_quantity
                        end as main_output_pquan,
                        case
                            when pinquan.repl_quantity is not null then pinquan.planned_quantity +
                                case
                                    when pinquan.repl_quantity > 0 then pinquan.repl_quantity
                                    else 0
                                 end
                            else pinquan.planned_quantity
                        end as inputs_pquan,

                        case
                            when pinquanwnorm.repl_quantity is not null then pinquanwnorm.planned_quantity +
                                case
                                    when pinquanwnorm.repl_quantity > 0 then pinquanwnorm.repl_quantity
                                    else 0
                                 end
                            else pinquanwnorm.planned_quantity
                        end as inp_wnorm_pquan,

                        dquan_owic.done_quantity as dquan_owic,
                        dquan_mowic.done_quantity as dquan_mowic,
                        dquan_mo.done_quantity as dquan_mo,
                        dquan_o.done_quantity as dquan_o,
                        dquan_i.done_quantity as dquan_i,
                        dquan_iwn.done_quantity as dquan_iwn
                        from chdr as hdr
                            left outer join pquan as pqo on pqo.docid = hdr.docid and pqo.item_type = 'O'
                            left outer join pquan as pqi on pqi.docid = hdr.docid and pqi.item_type = 'I'
                            left outer join dquan as dqo on dqo.docid = hdr.docid and move_type = 'GR'
                            left outer join dquan as dqi on dqi.docid = hdr.docid and ( dqi.move_type = 'GI' or
                                                                                        dqi.move_type = 'PGI' )
                            left outer join dfreg as dfr on dfr.docid = hdr.docid
                            left outer join bvfdat as bdat on bdat.docid = hdr.docid
                            left outer join poutquan on poutquan.docid = hdr.docid
                            left outer join pmopquan on pmopquan.docid = hdr.docid
                            left outer join pinquan on pinquan.docid = hdr.docid
                            left outer join pinquanwnorm on pinquanwnorm.docid = hdr.docid

                            left outer join dquan_owic on dquan_owic.docid = hdr.docid
                            left outer join dquan_mowic on dquan_mowic.docid = hdr.docid
                            left outer join dquan_mo on dquan_mo.docid = hdr.docid
                            left outer join dquan_o on dquan_o.docid = hdr.docid
                            left outer join dquan_i on dquan_i.docid = hdr.docid
                            left outer join dquan_iwn on dquan_iwn.docid = hdr.docid
                            left outer join pquanrepl on pquanrepl.docid = hdr.docid;



          return :out_data;


endmethod.
ENDCLASS.