P_ChmlCmplncPrpsRqmtPmaCT

//@EndUserText.label: 'Count Compliance Requirements of all Purposes per Product'


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

@AccessControl:
{
  authorizationCheck: #NOT_REQUIRED
}

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

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

@ObjectModel.representativeKey: 'ChmlCmplncInfoUUID'

define view P_ChmlCmplncPrpsRqmtPmaCT
  --Select data from basic view 'Chemical compliance information'
  as select from I_ChmlCmplncInfo       as ChmlCmplInfo

  --Join master table
  --purpose assignment in chemical compliance information
    join         I_ChmlCmplncPrpsAssgmt as PurposeAssignment on ChmlCmplInfo.ChmlCmplncInfoUUID = PurposeAssignment.ChmlCmplncInfoUUID
  --Purpose
    join         I_CmplncPrpsRqmtAssgmt as PrpsCRAssgnmt     on PrpsCRAssgnmt.CmplncPrpsUUID = PurposeAssignment.CmplncPrpsUUID
  --Compliance Requirements
    join         I_CmplRqVers           as CRVersion         on PrpsCRAssgnmt.CmplRqUUID = CRVersion.CmplRqUUID
  --Compliance Requirement Results IP and RE  
    inner join I_CmplRqRslt as CRR    on CRR.ChmlCmplncInfoUUID = ChmlCmplInfo.ChmlCmplncInfoUUID
                                           and CRR.CmplRqVersUUID = CRVersion.CmplRqVersUUID
                                           and ( CRR.CmplRqRsltProcessingStatus = 'IP'
                                           or    CRR.CmplRqRsltProcessingStatus = 'RE' )


{
      --UUID of chemical compliance information
  key ChmlCmplInfo.ChmlCmplncInfoUUID,

      --Count compliance requirements
      cast( count( distinct CRVersion.CmplRqUUID ) as ehfnd_cci_number_of_cr_ma ) as NmbrOfCmplRqRslts
      
}
where
  CRVersion.CmplRqApplicationComponent      = 'PMA'
  and PrpsCRAssgnmt.CmplncPrpsRqmtAssgmtIsObslt <> 'X'
group by
  ChmlCmplInfo.ChmlCmplncInfoUUID