@AbapCatalog:
{
-- SQL view name (16 characters)
sqlViewName: 'PCCRSTS',
--If it's true, the filter conditions are compared and, if they match, the associated join expression is evaluated only once
compiler.compareFilter: true
}
--Access Control: Authorizations Checks
@AccessControl:
{
authorizationCheck: #NOT_REQUIRED
}
--Client Handling of the view
@ClientHandling.algorithm: #SESSION_VARIABLE
--VDM view type
@VDM.viewType: #CONSUMPTION
@VDM.private: true
define view P_RawChmlCmplncStatus
as select from I_ChmlCmplncInfo as ChemicalCompliance
association [1..1] to P_RawChmlCmplncSupInProcCt as _SupInProcCount on _SupInProcCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [1..1] to P_RawChmlCmplncSupCompleteCt as _SupCompleteCount on _SupCompleteCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [1..1] to P_RawChmlCmplRqRsltInProcCt as _CRRInProcCount on _CRRInProcCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [1..1] to P_RawChmlCmplRqRsltReldCt as _CRRReldCount on _CRRReldCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [1..1] to P_RawChmlCmplRqRsltUnknownCt as _CRRUnknownCount on _CRRUnknownCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [0..1] to I_ChmlCmplncSuplrMatlStatus as _SupplierStatus on $projection.ChmlCmplncSuplrMatlStatus = _SupplierStatus.ChmlCmplncSuplrMatlStatus
association [0..1] to I_ChmlCmplncRawMatlProcgSts as _CmplRawMatlProcgSts on $projection.ChmlCmplncRawMatlProcgSts = _CmplRawMatlProcgSts.ChmlCmplncRawMatlProcgSts
association [0..1] to I_ChmlCmplncCmplRqRsltStatus as _CmplRqRsltStatus on $projection.ChmlCmplncCmplRqRsltStatus = _CmplRqRsltStatus.ChmlCmplncCmplRqRsltStatus
association [0..1] to I_ChmlCmplncRawMatlCmpstnSts as _CmplRawMatlCmpstnSts on $projection.ChmlCmplncRawMatlCmpstnSts = _CmplRawMatlCmpstnSts.ChmlCmplncRawMatlCmpstnSts
association [0..1] to P_ChmlAnalyticComposition as _CompStatus on _CompStatus.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
association [0..1] to P_ChmlCompsCompSumCT as _ComponentCount on _ComponentCount.ChmlCmplncInfoUUID = ChemicalCompliance.ChmlCmplncInfoUUID
and _ComponentCount.ChmlCompositionType = 'ANALYTIC1'
and _ComponentCount.ChmlCompositionStatus = 'IP'
association [1..1] to P_CChmlCmplConstants as _Constants on _Constants.ConstantNotExisting = _Constants.ConstantNotExisting
{
key ChmlCmplncInfoUUID,
_CRRInProcCount.recordCount as NmbrOfInProcessCmplRqRslts,
---------- Supplier material status -----------
case
when _SupInProcCount.recordCount > 0 then
_Constants[inner].ConstantInProcess --'INPROCESS' // there are supplier materials with status "In Process"
when _SupCompleteCount.recordCount > 0
then _Constants[inner].ConstantComplete --'COMPLETE' // all supplier materials are blocked, approved or outdated
else _Constants[inner].ConstantNone -- 'NONE'
end as ChmlCmplncSuplrMatlStatus,
--Determine criticality by status
--Calculation of ChmlCmplncSuplrMatlStsCritlty was in P_ChmlCmplncSuplrMatlCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when _SupInProcCount.recordCount > 0
--'INPROCESS'
then _Constants[inner].Constant2
when _SupCompleteCount.recordCount > 0
--'COMPLETE'
then _Constants[inner].Constant3 // all supplier materials are blocked, approved or outdated
else _Constants[inner].Constant2
end as ChmlCmplncSuplrMatlStsCritlty,
// _CRRInProcCount.recordCount as IPCt,
// _CRRUnknownCount.recordCount as unknownCt,
// _CompStatus.ChmlCmpstnInProcStatus,
// _ComponentCount.SumComponentCount as componentCt,
---------- Processing status (Combined Status) ----------
case
when (_CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount))
or (_CompStatus.ChmlCmpstnInProcStatus = 'IP' and _ComponentCount.SumComponentCount > 0)
then _Constants[inner].ConstantCompStatForrelease --'FORRELEASE' // at least one CR is "in process" and not "unknown" or composition IP exists with at least 1 component
when (_CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0))
and (_CompStatus.ChmlCmpstnInProcStatus = 'NE' and _CompStatus.ChmlCmpstnReldStatus = 'RE')
then _Constants[inner].ConstantComplete--'COMPLETE' // all CRRs are released and Composition is released (no IP version exists)
else _Constants[inner].ConstantCompStatNew -- 'NEW' // at least one CR is in status "unknown" or composition in IP exists with no components
end as ChmlCmplncRawMatlProcgSts,
--Determine criticality by status
--Calculation of ChmlCmplncRawMatlProcgStsCritl was in P_ChmlCmpRawMatProcStsCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when (_CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount))
or (_CompStatus.ChmlCmpstnInProcStatus = 'IP' and _ComponentCount.SumComponentCount > 0)
--'FORRELEASE'
then _Constants[inner].Constant2
when (_CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0))
and (_CompStatus.ChmlCmpstnInProcStatus = 'NE' and _CompStatus.ChmlCmpstnReldStatus = 'RE')
--'COMPLETE'
then _Constants[inner].Constant3
else _Constants[inner].Constant2
end as ChmlCmplncRawMatlProcgStsCritl,
-- Determine sort order by status
--Calculation of ChmlCmplncRawMatlProcgStsSort was in P_ChmlCmpRawMatProcStsCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when (_CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount))
or (_CompStatus.ChmlCmpstnInProcStatus = 'IP' and _ComponentCount.SumComponentCount > 0)
--'FORRELEASE'
then _Constants[inner].Constant1 // at least one CR is "in process" and not "unknown" or composition IP exists with at least 1 component
when (_CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0))
and (_CompStatus.ChmlCmpstnInProcStatus = 'NE' and _CompStatus.ChmlCmpstnReldStatus = 'RE')
--'COMPLETE'
then _Constants[inner].Constant3 // all CRRs are released and Composition is released (no IP version exists)
else _Constants[inner].Constant2 -- 'NEW' // at least one CR is in status "unknown" or composition in IP exists with no components
end as ChmlCmplncRawMatlProcgStsSort,
---------- Compliance Requirement Status ----------
case
when _CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount)
then _Constants[inner].ConstantCompStatForrelease // at least one CR is "in process" and not "unknown" or composition IP exists with at least 1 component
when _CRRUnknownCount.recordCount > 0
then _Constants[inner].ConstantCompStatNew --'NEW' // at least one CR is in status "unknown" or composition in IP exists with no components
when _CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0)
then _Constants[inner].ConstantComplete -- 'COMPLETE' // all CRRs are released and Composition is released (no IP version exists)
else _Constants[inner].ConstantNone -- 'NONE' // no CRR assinged to CCI
end as ChmlCmplncCmplRqRsltStatus,
--Determine criticality by status
--Calculation of ChmlCmplncRawMatlProcgStsSort was in P_ChmlCmpRawMatProcStsCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when _CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount)
--'Forrelease'
then _Constants[inner].Constant2
when _CRRUnknownCount.recordCount > 0
--'NEW'
then _Constants[inner].Constant2
when _CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0 )
--'COMPLETE'
then _Constants[inner].Constant3
else _Constants[inner].Constant2
end as ChmlCmplncCmplRqRsltStsCritlty,
-- Compliance Requirement Status
--Calculation of CmplRqRsltCmplncStsForSorting was in P_ChmlCmpRawMatProcStsCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when _CRRInProcCount.recordCount > 0 and
(_CRRUnknownCount.recordCount is null or _CRRUnknownCount.recordCount < _CRRInProcCount.recordCount)
--'Forrelease'
then _Constants[inner].Constant1 // at least one CR is in status "unknown" or composition in IP exists with no components
when _CRRUnknownCount.recordCount > 0
--'NEW'
then _Constants[inner].Constant2 // at least one CR is in status "unknown" or composition in IP exists with no components
when _CRRReldCount.recordCount > 0 and
(_CRRInProcCount.recordCount is null or _CRRInProcCount.recordCount = 0)
--'COMPLETE'
then _Constants[inner].Constant3 // all CRRs are released and Composition is released (no IP version exists)
else _Constants[inner].Constant1 -- 'NONE'
end as CmplRqRsltCmplncStsForSorting,
---------- Composition status----------
case
when _CompStatus.ChmlCmpstnInProcStatus = 'IP' and _ComponentCount.SumComponentCount > 0
then _Constants[inner].ConstantCompStatForrelease // No empty IP Composition exists
when _CompStatus.ChmlCmpstnInProcStatus = 'IP' and (_ComponentCount.SumComponentCount is null or _ComponentCount.SumComponentCount = 0)
then _Constants[inner].ConstantCompStatNew // Empty IP Composition exists
when _CompStatus.ChmlCmpstnInProcStatus = 'NE' and _CompStatus.ChmlCmpstnReldStatus = 'RE'
then _Constants[inner].ConstantComplete-- 'COMPLETE' // No IP but released composition exists
end as ChmlCmplncRawMatlCmpstnSts,
--Determine criticality by status
--Calculation of ChmlCmplncRawMatlCmpstnCritlty was in P_ChmlCmpRawMatCompStsCritlty
--Case on Case statement leads to materialization of this view. There it is refactored to this CDS View
case
when _CompStatus.ChmlCmpstnInProcStatus = 'IP' and _ComponentCount.SumComponentCount > 0
--'Forrelease'
then _Constants[inner].Constant2
when _CompStatus.ChmlCmpstnInProcStatus = 'IP' and (_ComponentCount.SumComponentCount is null or _ComponentCount.SumComponentCount = 0)
--'NEW'
then _Constants[inner].Constant2
when _CompStatus.ChmlCmpstnInProcStatus = 'NE' and _CompStatus.ChmlCmpstnReldStatus = 'RE'
-- 'COMPLETE'
then _Constants[inner].Constant3
else _Constants[inner].Constant2
end as ChmlCmplncRawMatlCmpstnCritlty,
// _RawMatStatus,
_SupplierStatus,
_CmplRawMatlProcgSts,
_CmplRqRsltStatus,
_CmplRawMatlCmpstnSts
}
where
ChemicalCompliance.MaterialIsSourced = 'X'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHMLCMPLNCINFO",
"P_CCHMLCMPLCONSTANTS",
"P_CHMLANALYTICCOMPOSITION",
"P_CHMLCOMPSCOMPSUMCT",
"P_RAWCHMLCMPLNCSUPCOMPLETECT",
"P_RAWCHMLCMPLNCSUPINPROCCT",
"P_RAWCHMLCMPLRQRSLTINPROCCT",
"P_RAWCHMLCMPLRQRSLTRELDCT",
"P_RAWCHMLCMPLRQRSLTUNKNOWNCT"
],
"ASSOCIATED":
[
"I_CHMLCMPLNCCMPLRQRSLTSTATUS",
"I_CHMLCMPLNCRAWMATLCMPSTNSTS",
"I_CHMLCMPLNCRAWMATLPROCGSTS",
"I_CHMLCMPLNCSUPLRMATLSTATUS",
"P_CCHMLCMPLCONSTANTS",
"P_CHMLANALYTICCOMPOSITION",
"P_CHMLCOMPSCOMPSUMCT",
"P_RAWCHMLCMPLNCSUPCOMPLETECT",
"P_RAWCHMLCMPLNCSUPINPROCCT",
"P_RAWCHMLCMPLRQRSLTINPROCCT",
"P_RAWCHMLCMPLRQRSLTRELDCT",
"P_RAWCHMLCMPLRQRSLTUNKNOWNCT"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/