P_ChmlCmplMktCovAndReq

@AbapCatalog:
{
  sqlViewName: 'PCCMKTCOVREQ',
  compiler.compareFilter: true
}

--Access Control: Authorizations Checks
@AccessControl:
{
  authorizationCheck: #NOT_REQUIRED
}

--Client Handling of the view
@ClientHandling.algorithm: #SESSION_VARIABLE

@ObjectModel.usageType.serviceQuality: #B

--VDM view type
@VDM.viewType: #COMPOSITE
@VDM.private: true

/*----------------------------------------------------------------------------------------------------------------------
    KeyFigure-View: Processing status of a CCI from point of view: market coverage.
------------------------------------------------------------------------------------------------------------------------*/

define view P_ChmlCmplMktCovAndReq

  as select from    ehfndv_cci_mcvrg  as _MC

    left outer join ehfndv_cci_mreq as _MR   on  _MC.chmlcmplncinfouuid = _MR.chmlcmplncinfouuid
                                              and _MC.country            = _MR.country

    left outer join ehfndv_cci_prod  as _PROD on _MC.chmlcmplncinfouuid = _PROD.chmlcmplncinfouuid

{
  _MC.chmlcmplncinfouuid   as chemicalcomplianceuuid,

  _PROD.chmlcmplncproduuid as chmlcmplncproduuid,

  _MC.country              as country,

  //request

  case _MR.chmlcmplncprodnisreqd
    when 'X'  then 1
    else 0
  end                      as ProductionRequested,

  case _MR.chmlcmplncimprtisreqd
    when 'X'  then 1
    else 0
  end                      as ImportRequested,

  case _MR.chmlcmplncexprtisreqd
    when 'X'  then 1
    else 0
  end                      as ExportRequested,

  case _MR.chmlcmplncsalesisreqd
    when 'X'  then 1
    else 0
  end                      as SalesRequested,

  //coverage

  case _MC.chmlcmplncprodnallwd
    when 'X' then 1
    when 'N' then 1
    else 0
  end                      as ProductionProcessed,

  case _MC.chmlcmplncimprtallwd
    when 'X' then 1
    when 'N' then 1
    else 0
  end                      as ImportProcessed,

  case _MC.chmlcmplncexprtallwd
    when 'X' then 1
    when 'N' then 1
    else 0
  end                      as ExportProcessed,

  case _MC.chmlcmplncsalesallwd
    when 'X' then 1
    when 'N' then 1
    else 0
  end                      as SalesProcessed,

  //unprocessed

  case _MR.chmlcmplncprodnisreqd
    when 'X'  then
      case _MC.chmlcmplncprodnallwd
        when 'X' then 0
        when 'N' then 0
      else 1
      end
    else 0
  end                      as ProductionUnprocessed,

  case _MR.chmlcmplncimprtisreqd
    when 'X'  then
      case _MC.chmlcmplncimprtallwd
        when 'X' then 0
        when 'N' then 0
      else 1
      end
    else 0
  end                      as ImportUnprocessed,

  case _MR.chmlcmplncexprtisreqd
    when 'X'  then
      case _MC.chmlcmplncexprtallwd
        when 'X' then 0
        when 'N' then 0
      else 1
      end
    else 0
  end                      as ExportUnprocessed,

  case _MR.chmlcmplncsalesisreqd
    when 'X'  then
      case _MC.chmlcmplncsalesallwd
        when 'X' then 0
        when 'N' then 0
      else 1
      end
    else 0
  end                      as SalesUnprocessed

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"EHFNDV_CCI_MCVRG",
"EHFNDV_CCI_MREQ",
"EHFNDV_CCI_PROD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/