@AbapCatalog:
{
sqlViewName: 'PCCMKTREQCOV',
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: #COMPOSITE
@VDM.private: true
/*----------------------------------------------------------------------------------------------------------------------
Market requests with corresponding market coverages and product assignment for later calculations
------------------------------------------------------------------------------------------------------------------------*/
define view P_ChmlCmplMktReqAndCov
as
/*----------------------------------------------------------------------------------------------------------------------
Select all market requests and coverages for the unpackaged product
------------------------------------------------------------------------------------------------------------------------*/
select from ehfndv_cci_mreq as _MarketRequest
association [0..*] to ehfndv_cci_mcvrg as _MarketCoverage on _MarketRequest.chmlcmplncinfouuid = _MarketCoverage.chmlcmplncinfouuid
and _MarketRequest.country = _MarketCoverage.country
// dummy constant selection to avoid issues with case statement with constants (not null-preserving topic)
association [1..1] to P_ChmlCmplConstants as _Constants on _Constants.Constant1 = _Constants.Constant1
{
key _MarketRequest.chmlcmplncinfouuid as chemicalcomplianceuuid,
_MarketRequest.country as country,
// to be able to summarize the values later, the 0 values must also be returned
//request
case
when _MarketRequest.chmlcmplncprodnisreqd = 'X' then _Constants[inner].Constant1
when _MarketRequest.chmlcmplncprodnisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncprodnisreqd is null then _Constants[inner].Constant0 // chmlcmplncprodnisreqd is not set --> Request is not processed for sales allowed
end as ProductionRequested,
case
when _MarketRequest.chmlcmplncsalesisreqd = 'X' then _Constants[inner].Constant1
when _MarketRequest.chmlcmplncsalesisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncsalesisreqd is null then _Constants[inner].Constant0 // chmlcmplncsalesisreqd is not set --> Request is not processed for sales allowed
end as SalesRequested,
//coverage
case
when _MarketCoverage.chmlcmplncprodnallwd = 'X' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd = 'N' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd = 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd <> 'N' and _MarketCoverage.chmlcmplncprodnallwd <> 'X' and _MarketCoverage.chmlcmplncprodnallwd <> 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd is null then _Constants[inner].Constant0 // chmlcmplncprodnallwd is not set --> Request is not processed for sales allowed
end as ProductionProcessed,
case
when _MarketCoverage.chmlcmplncsalesallwd = 'X' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd = 'N' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd = 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd <> 'N' and _MarketCoverage.chmlcmplncsalesallwd <> 'X' and _MarketCoverage.chmlcmplncsalesallwd <> 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd is null then _Constants[inner].Constant0 // chmlcmplncsalesallwd is not set --> Request is not processed for sales allowed
end as SalesProcessed,
//unprocessed
case
when _MarketRequest.chmlcmplncprodnisreqd = 'X' then
case
when _MarketCoverage.chmlcmplncprodnallwd = 'X' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd = 'N' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd = 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd <> 'N' and _MarketCoverage.chmlcmplncprodnallwd <> 'X' and _MarketCoverage.chmlcmplncprodnallwd <> 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd is null then _Constants[inner].Constant1 // chmlcmplncprodnallwd is not set --> Request is not processed for sales allowed
end
when _MarketRequest.chmlcmplncprodnisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncprodnisreqd is null then _Constants[inner].Constant0
end as ProductionUnprocessed,
case
when _MarketRequest.chmlcmplncsalesisreqd = 'X' then
case
when _MarketCoverage.chmlcmplncsalesallwd = 'X' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd = 'N' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd = 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd <> 'N' and _MarketCoverage.chmlcmplncsalesallwd <> 'X' and _MarketCoverage.chmlcmplncsalesallwd <> 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd is null then _Constants[inner].Constant1 // chmlcmplncsalesallwd is not set --> Request is not processed for sales allowed
end
when _MarketRequest.chmlcmplncsalesisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncsalesisreqd is null then _Constants[inner].Constant0
end as SalesUnprocessed
}
/*----------------------------------------------------------------------------------------------------------------------
Select all market coverages specific for the packaged product (market requests are always for the unpackaged product)
------------------------------------------------------------------------------------------------------------------------*/
union all
select from ehfndv_cci_mreq as _MarketRequest
inner join ehfndv_cci_prod as _ProductAssignment on _MarketRequest.chmlcmplncinfouuid = _ProductAssignment.chmlcmplncinfouuid
inner join ehfndv_cci_mcvrg as _MarketCoverage on _ProductAssignment.chmlcmplncinfouuid = _MarketCoverage.chmlcmplncinfouuid
and _MarketRequest.country = _MarketCoverage.country
// dummy constant selection to avoid issues with case statement with constants (not null-preserving topic)
association [1..1] to P_ChmlCmplConstants as _Constants on _Constants.Constant1 = _Constants.Constant1
{
key _MarketRequest.chmlcmplncinfouuid as chemicalcomplianceuuid,
_MarketRequest.country as country,
// to be able to summarize the values later, the 0 values must also be returned
//request
case
when _MarketRequest.chmlcmplncprodnisreqd = 'X' then _Constants[inner].Constant1
when _MarketRequest.chmlcmplncprodnisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncprodnisreqd is null then _Constants[inner].Constant0 // chmlcmplncprodnisreqd is not set --> Request is not processed for sales allowed
end as ProductionRequested,
case
when _MarketRequest.chmlcmplncsalesisreqd = 'X' then _Constants[inner].Constant1
when _MarketRequest.chmlcmplncsalesisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncsalesisreqd is null then _Constants[inner].Constant0 // chmlcmplncsalesisreqd is not set --> Request is not processed for sales allowed
end as SalesRequested,
//coverage
case
when _MarketCoverage.chmlcmplncprodnallwd = 'X' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd = 'N' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd = 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd <> 'N' and _MarketCoverage.chmlcmplncprodnallwd <> 'X' and _MarketCoverage.chmlcmplncprodnallwd <> 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd is null then _Constants[inner].Constant0 // chmlcmplncprodnallwd is not set --> Request is not processed for sales allowed
end as ProductionProcessed,
case
when _MarketCoverage.chmlcmplncsalesallwd = 'X' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd = 'N' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd = 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd <> 'N' and _MarketCoverage.chmlcmplncsalesallwd <> 'X' and _MarketCoverage.chmlcmplncsalesallwd <> 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd is null then _Constants[inner].Constant0 // chmlcmplncsalesallwd is not set --> Request is not processed for sales allowed
end as SalesProcessed,
//unprocessed
case
when _MarketRequest.chmlcmplncprodnisreqd = 'X' then
case
when _MarketCoverage.chmlcmplncprodnallwd = 'X' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd = 'N' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd = 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncprodnallwd <> 'N' and _MarketCoverage.chmlcmplncprodnallwd <> 'X' and _MarketCoverage.chmlcmplncprodnallwd <> 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncprodnallwd is null then _Constants[inner].Constant1 // chmlcmplncprodnallwd is not set --> Request is not processed for sales allowed
end
when _MarketRequest.chmlcmplncprodnisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncprodnisreqd is null then _Constants[inner].Constant0
end as ProductionUnprocessed,
case
when _MarketRequest.chmlcmplncsalesisreqd = 'X' then
case
when _MarketCoverage.chmlcmplncsalesallwd = 'X' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd = 'N' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd = 'R' then _Constants[inner].Constant0
when _MarketCoverage.chmlcmplncsalesallwd <> 'N' and _MarketCoverage.chmlcmplncsalesallwd <> 'X' and _MarketCoverage.chmlcmplncsalesallwd <> 'R' then _Constants[inner].Constant1
when _MarketCoverage.chmlcmplncsalesallwd is null then _Constants[inner].Constant1 // chmlcmplncsalesallwd is not set --> Request is not processed for sales allowed
end
when _MarketRequest.chmlcmplncsalesisreqd <> 'X' then _Constants[inner].Constant0
when _MarketRequest.chmlcmplncsalesisreqd is null then _Constants[inner].Constant0
end as SalesUnprocessed
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_CHMLCMPLCONSTANTS",
"EHFNDV_CCI_MCVRG",
"EHFNDV_CCI_MREQ",
"EHFNDV_CCI_PROD"
],
"ASSOCIATED":
[
"P_CHMLCMPLCONSTANTS",
"EHFNDV_CCI_MCVRG"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/