P_ChmlCmplncReqMigration

@AbapCatalog.sqlViewName: 'PCCMPLCNREQMIG'
@AbapCatalog.compiler.compareFilter: true

@ClientHandling.algorithm: #SESSION_VARIABLE

@AccessControl.authorizationCheck: #NOT_ALLOWED

@ObjectModel:
{
  usageType:
  {
    dataClass: #MASTER,
    sizeCategory: #M,
    serviceQuality: #B
  }
}

@VDM.viewType: #COMPOSITE
@VDM.private: true

-------------------------------------------------------------
-- IMPORTANT: View may not be changed after initial delivery!
-------------------------------------------------------------

define view P_ChmlCmplncReqMigration

  as select from I_ChmlCmplncReq as ChmlCmplncReqProduce

    inner join   I_ChmlCmplncReq as _ChmlCmplncReqSell on  _ChmlCmplncReqSell.Material                     = ChmlCmplncReqProduce.Material
                                                       and _ChmlCmplncReqSell.ChmlCmplncReqCmplncProc      = ChmlCmplncReqProduce.ChmlCmplncReqCmplncProc
                                                       and _ChmlCmplncReqSell.ChmlCmplncRequestOriginType  = ChmlCmplncReqProduce.ChmlCmplncRequestOriginType
                                                       and _ChmlCmplncReqSell.ChmlCmplncBusinessProcess    = 'SELL'
    -- if corresponding "sell in" request is closed, close corresponding "produce in" request must be closed
                                                       and _ChmlCmplncReqSell.ChmlCmplncRequestPrgrsStatus = 'CLOSED'

{
  key ChmlCmplncReqProduce.ChmlCmplncRequestUUID,

      ChmlCmplncReqProduce.ChmlCmplncReqCmplncProc,
      ChmlCmplncReqProduce.ChmlCmplncRequestOriginType,
      ChmlCmplncReqProduce.ChmlCmplncReqOriginProc,
      ChmlCmplncReqProduce.ChmlCmplncRequestPrgrsStatus,
      ChmlCmplncReqProduce.ChmlCmplncBusinessProcess,
      ChmlCmplncReqProduce.Material,

      ChmlCmplncReqProduce._ChmlCmplncInfo.ChmlCmplncInfoType,

      case
        when ChmlCmplncReqProduce._ChmlCmplncInfo.ChmlCmplncInfoType = 'PP'
            and ( ChmlCmplncReqProduce.ChmlCmplncRequestPrgrsStatus = 'NEW' or ChmlCmplncReqProduce.ChmlCmplncRequestPrgrsStatus = 'INPROG'  )
          then '00' -- not yet executed
        else '01' -- Migration not needed/ already done
      end as StatusField,

      ChmlCmplncReqProduce.ChmlCmplncInfoUUID,
      ChmlCmplncReqProduce.ChmlCmplncProdUUID,

      ChmlCmplncReqProduce.CreationUTCDateTime,
      ChmlCmplncReqProduce.CreatedByUser,
      ChmlCmplncReqProduce.LastChangeUTCDateTime,
      ChmlCmplncReqProduce.LastChangedByUser
}
where
    ChmlCmplncReqProduce._ChmlCmplncInfo.ChmlCmplncInfoType = 'PP'
  and ChmlCmplncReqProduce.ChmlCmplncReqCmplncProc     = 'PMA'
  and ChmlCmplncReqProduce.ChmlCmplncRequestOriginType = 'MM'
  and ChmlCmplncReqProduce.ChmlCmplncBusinessProcess   = 'PRODUCE'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHMLCMPLNCINFO",
"I_CHMLCMPLNCREQ"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/