P_STOR_STL_DETAILS

@AbapCatalog.sqlViewName: 'P_STOR_STL_DTLS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@VDM.viewType: #BASIC

define view P_STOR_STL_DETAILS  as select from    /accgo/t_stlhead as stlmt_data
    inner join      /accgo/t_stlitem as stl_item_data on(
      stl_item_data.stl_header_guid = stlmt_data.stl_header_guid
    )
    inner join      jest             as stlmt_status  on(
       stlmt_status.objnr     = concat(
         'XS', concat(
           stlmt_data.settl_doc, stlmt_data.settl_yr
         )
       )
       and stlmt_status.inact = ' '
     )

    inner join      tj04             as stat_cntr //Configuration table for mapping Selection ID & Status

                                                      on(
                                                        stat_cntr.obtyp         =  'SSD'
                                                        and(
                                                          stlmt_status.stat     <> 'I7S57'
                                                          and stlmt_status.stat <> 'I7S58'
                                                          and stlmt_status.stat <> 'I7S59'
                                                          and stlmt_status.stat <> 'I7S60'
                                                        )
                                                      )

   
   /* inner join      /accgo/t_stlctnr as stlunit_grp // Settlment unit anf group linking

                                                      on stlunit_grp.stl_header_guid = stlmt_data.stl_header_guid

    left outer join /accgo/t_stlinv  as stlinvoice // Settlment Invoice table

                                                      on stlinvoice.group_guid = stlunit_grp.group_guid

    left outer join /accgo/t_billdue as bill_due //Settlment Billdue table

                                                      on bill_due.stl_header_guid = stlmt_data.stl_header_guid

    left outer join vbrk             as invoice_data  on invoice_data.vbeln = bill_due.billing_doc
                                                      or invoice_data.vbeln = bill_due.dm_cr_doc



    left outer join /accgo/t_evtreg  as evtreg // Event Registry table

                                                      on  evtreg.stl_header_guid = stlmt_data.stl_header_guid
                                                      and pr_status              = '3' */


{
  key stlmt_data.settl_doc,
  key stlmt_data.settl_yr,
      stlmt_data.settl_cat,
      stlmt_data.settl_type,
      stlmt_data.side            as settl_side,
      stlmt_data.appl_guid,
      stlmt_data.appl_version,
      stlmt_data.doc_typ,
      stlmt_data.settl_scenario,
      stlmt_data.side,
      stlmt_data.appldoc,
      stlmt_data.appldoc_item,
      stl_item_data.settl_item,
      stl_item_data.full_ship_qty,
      stl_item_data.tot_quantity_adj,
      stl_item_data.adj_uom,
      stl_item_data.doc_curr,
      stl_item_data.unit_quantity,
      stl_item_data.unit_uom,
      stl_item_data.contract_qty,
      stl_item_data.contract_uom,
      stl_item_data.document_qty,
      stl_item_data.document_uom
      //evtreg.changed_on          as revrec_date,


      //cast(case stlmt_data.side

       //when 'M' then tstmp_to_dats(stlinvoice.created_on, abap_system_timezone($session.client,'NULL'), $session.client, 'NULL')

      // when 'V' then invoice_data.erdat

       //end as dats )             as invoice_date



}
where
  (
       stlmt_data.settl_cat  = 'P'
    or stlmt_data.settl_cat  = 'F'
  )
  and  stlmt_data.settl_type = 'T'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"/ACCGO/T_STLHEAD",
"/ACCGO/T_STLITEM",
"JEST",
"TJ04"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/