@AbapCatalog.sqlViewName: 'IPDGCUBE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #MANDATORY
@Analytics.dataCategory: #CUBE
@ClientHandling.algorithm: #SESSION_VARIABLE
@EndUserText.label: 'Packaged Dangerous Goods Cube'
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
@ObjectModel.usageType:{ serviceQuality: #D, // < 500 msec
sizeCategory: #XL, // < 100.000.000
dataClass: #MIXED }
@VDM.viewType: #COMPOSITE
define view I_PackagedDngrsGdsCube
as select from I_CmplRqRslt
join I_ChmlCmplncMatlAssgmt on I_ChmlCmplncMatlAssgmt.ChmlCmplncInfoUUID = I_CmplRqRslt.ChmlCmplncInfoUUID // Assignment of Material to this PP
join I_ChmlCmplncProdAssgmt on I_ChmlCmplncProdAssgmt.ChmlCmplncInfoUUID = I_CmplRqRslt.ChmlCmplncInfoUUID // Assignment of UP to this PP
join I_CmplRqVersEnhanced on I_CmplRqRslt.CmplRqVersUUID = I_CmplRqVersEnhanced.CmplRqVersUUID // CRV Information
join I_CmplncPrpsRqmtAssgmt on I_CmplncPrpsRqmtAssgmt.CmplRqUUID = I_CmplRqVersEnhanced.CmplRqUUID // Purposes the CRV appears in
join I_ChmlCmplncPrpsAssgmt on I_ChmlCmplncPrpsAssgmt.CmplncPrpsUUID = I_CmplncPrpsRqmtAssgmt.CmplncPrpsUUID // Purpose is assigned to CRV
and I_ChmlCmplncPrpsAssgmt.ChmlCmplncInfoUUID = I_ChmlCmplncProdAssgmt.ChmlCmplncProdUUID // Purpose is assigned to UP
join I_CmplncPrps on I_CmplncPrps.CmplncPrpsUUID = I_CmplncPrpsRqmtAssgmt.CmplncPrpsUUID // Purpose information
join I_CmplRqRsltDngrsGdsBscDets on I_CmplRqRsltDngrsGdsBscDets.CmplRqRsltUUID = I_CmplRqRslt.CmplRqRsltUUID // Basic DG Details
join I_CmplRqRsltDaGdsMoOfTr on I_CmplRqRsltDaGdsMoOfTr.CmplRqRsltUUID = I_CmplRqRslt.CmplRqRsltUUID // MoT Info
left outer to one join I_PckgdDngrsGoodEnclosureDets on I_PckgdDngrsGoodEnclosureDets.ChmlCmplncInfoUUID = I_CmplRqRslt.ChmlCmplncInfoUUID // Enclosure information
association [0..1] to I_ChmlCmplncInfo as _ChmlCmplncInfo on $projection.ChmlCmplncInfoUUID = _ChmlCmplncInfo.ChmlCmplncInfoUUID
association [0..1] to I_Product as _Product on $projection.Product = _Product.Product
association [1..1] to I_CmplRqRsltProcessingStatus as _CmplRqRsltProcessingStatus on $projection.CmplRqRsltProcessingStatus = _CmplRqRsltProcessingStatus.CmplRqRsltProcessingStatus
association [0..1] to I_DescOfDngrsGdsPckg as _DescOfDngrsGdsPckg on $projection.DescriptionOfDngrsGdsPckg = _DescOfDngrsGdsPckg.DescriptionOfDngrsGdsPckg
association [0..1] to I_CmplRqRsltDaGdsMoOfTrDesc as _CmplRqRsltDaGdsMoOfTrDesc on $projection.CmplRqRsltDngrsGdsModeOfTrUUID = _CmplRqRsltDaGdsMoOfTrDesc.CmplRqRsltDngrsGdsModeOfTrUUID
and _CmplRqRsltDaGdsMoOfTrDesc.Language = $session.system_language
association [0..1] to I_CmplRqRsltDaGdsMoOfTrDesc as _CmplRqRsltDaGdsMoOfTrDesc_E on $projection.CmplRqRsltDngrsGdsModeOfTrUUID = _CmplRqRsltDaGdsMoOfTrDesc_E.CmplRqRsltDngrsGdsModeOfTrUUID
and _CmplRqRsltDaGdsMoOfTrDesc_E.Language = 'E'
association [1..1] to I_ClassifiedAsDangerousGood as _ClassifiedAsDangerousGood on $projection.ClassifiedAsDangerousGood = _ClassifiedAsDangerousGood.ClassifiedAsDangerousGood
association [1..1] to I_EnclosureVariant as _EnclosureVariant on $projection.EnclosureVariant = _EnclosureVariant.EnclosureVariant
association [1..1] to I_CmplncPrpsDimension as _CmplncPrps on $projection.CmplncPrpsUUID = _CmplncPrps.CmplncPrpsUUID
association [1..1] to I_DngrsGoodsModeOfTransport as _DngrsGoodsModeOfTransport on $projection.DngrsGoodsModeOfTransport = _DngrsGoodsModeOfTransport.DngrsGoodsModeOfTransport
association [1..1] to I_TransportPermission as _OverallTransportPermission on $projection.CmplRqRsltManualStatus = _OverallTransportPermission.TransportPermission
association [1..1] to I_TransportPermission as _TransportPermission on $projection.TransportPermission = _TransportPermission.TransportPermission
association [1..1] to I_DngrsGdsClassOrDivisionVH as _DangerousGoodsClass on $projection.DangerousGoodsClass = _DangerousGoodsClass.DangerousGoodsClass
association [1..1] to I_DngrsGdsPackingGroupVH as _DangerousGoodsPackingGroup on $projection.DangerousGoodsPackingGroup = _DangerousGoodsPackingGroup.DangerousGoodsPackingGroup
{
key I_CmplRqRslt.CmplRqRsltUUID,
key I_CmplRqRsltDaGdsMoOfTr.CmplRqRsltDngrsGdsModeOfTrUUID,
//@ObjectModel.foreignKey.association: '_CmplncPrps' <-- Commented because it prevents dropdowns in ALP
key I_CmplncPrps.CmplncPrpsUUID,
@Semantics.text: true
COALESCE(_CmplncPrps._Text[1: Language = $session.system_language ].CmplncPrpsName,
_CmplncPrps._Text[1: Language = 'E' ].CmplncPrpsName) as CmplncPrpsName,
// CmplRqVers
I_CmplRqRslt.ComplianceRequirement,
left( I_CmplRqVersEnhanced.CmplRqVersName, 250 ) as CmplRqVersName,
I_CmplRqVersEnhanced.CmplRqPattern,
// ChmlCmplncInfo
I_CmplRqRslt.ChmlCmplncInfoUUID,
I_CmplRqRslt._ChmlCmplncInfo.ChmlCmplncInternalName,
@ObjectModel.foreignKey.association: '_Product'
I_ChmlCmplncMatlAssgmt.Material as Product,
// Product Name
_Product._Text[1: Language = $session.system_language].ProductName,
// @ObjectModel.text.element: ['ProcessingStatusText']
//@ObjectModel.foreignKey.association: '_CmplRqRsltProcessingStatus' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRslt.CmplRqRsltProcessingStatus,
@Semantics.text: true
COALESCE (_CmplRqRsltProcessingStatus._Text[1: Language = $session.system_language ].ProcessingStatusText,
_CmplRqRsltProcessingStatus._Text[1: Language = 'E' ].ProcessingStatusText ) as ProcessingStatusText,
// @ObjectModel.text.element: ['CmplRqRsltCmplncStsTxt']
//@ObjectModel.foreignKey.association: '_OverallTransportPermission' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRslt.CmplRqRsltManualStatus,
@Semantics.text: true
COALESCE (_OverallTransportPermission._TransportPermissionText[1: Language = $session.system_language ].TransportPermissionName,
_OverallTransportPermission._TransportPermissionText[1: Language = 'E' ].TransportPermissionName ) as CmplRqRsltCmplncStsTxt,
// EnclosureDetails
// @ObjectModel.text.element: ['DescOfDngrsGdsPckgName']
// @ObjectModel.foreignKey.association: '_DescOfDngrsGdsPckg' <-- Commented because it prevents dropdowns in ALP
I_PckgdDngrsGoodEnclosureDets.DescriptionOfDngrsGdsPckg,
@Semantics.text: true
COALESCE (_DescOfDngrsGdsPckg._Text[1: Language = $session.system_language ].DescOfDngrsGdsPckgName,
_DescOfDngrsGdsPckg._Text[1: Language = 'E' ].DescOfDngrsGdsPckgName ) as DescOfDngrsGdsPckgName,
// DangerousGoodsDetails
// @ObjectModel.text.element: ['ClassifiedAsDangerousGoodName']
//@ObjectModel.foreignKey.association: '_ClassifiedAsDangerousGood' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDngrsGdsBscDets.ClassifiedAsDangerousGood as ClassifiedAsDangerousGood,
@Semantics.text: true
COALESCE( _ClassifiedAsDangerousGood._Text[1: Language = $session.system_language ].ClassifiedAsDangerousGoodName,
_ClassifiedAsDangerousGood._Text[1: Language = 'E' ].ClassifiedAsDangerousGoodName ) as ClassifiedAsDangerousGoodName,
I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumber,
I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumberType,
concat_with_space ( I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumberType,
I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsIdnNumber, 1) as DangerousGoodsIdnNumberCnctntd,
// @ObjectModel.foreignKey.association: '_DangerousGoodsClass' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsClass,
// @ObjectModel.foreignKey.association: '_DangerousGoodsPackingGroup' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDngrsGdsBscDets.DangerousGoodsPackingGroup,
// @ObjectModel.text.element: ['EnclosureVariantName']
// @ObjectModel.foreignKey.association: '_EnclosureVariant' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDngrsGdsBscDets.EnclosureVariant,
@Semantics.text: true
COALESCE (_EnclosureVariant._Text[1: Language = $session.system_language ].EnclosureVariantName,
_EnclosureVariant._Text[1: Language = 'E' ].EnclosureVariantName ) as EnclosureVariantName,
// Mode of Transport
// @ObjectModel.text.element: ['DngrsGoodsModeOfTransportName']
// @ObjectModel.foreignKey.association: '_DngrsGoodsModeOfTransport' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDaGdsMoOfTr.DngrsGoodsModeOfTransport,
@Semantics.text: true
COALESCE( _DngrsGoodsModeOfTransport._Text[1: Language = $session.system_language ].DngrsGoodsModeOfTransportName,
_DngrsGoodsModeOfTransport._Text[1: Language = 'E' ].DngrsGoodsModeOfTransportName ) as DngrsGoodsModeOfTransportName,
// @ObjectModel.text.element: ['TransportPermissionName']
// @ObjectModel.foreignKey.association: '_TransportPermission' <-- Commented because it prevents dropdowns in ALP
I_CmplRqRsltDaGdsMoOfTr.TransportPermission,
@Semantics.text: true
COALESCE( _TransportPermission._TransportPermissionText[1: Language = $session.system_language ].TransportPermissionName,
_TransportPermission._TransportPermissionText[1: Language = 'E' ].TransportPermissionName) as TransportPermissionName,
left( coalesce( _CmplRqRsltDaGdsMoOfTrDesc.DangerousGoodDescForDocuments,
_CmplRqRsltDaGdsMoOfTrDesc_E.DangerousGoodDescForDocuments ), 250 ) as DangerousGoodDescForDocuments,
left( coalesce( _CmplRqRsltDaGdsMoOfTrDesc.DngrsGoodDescForPackggDocsText,
_CmplRqRsltDaGdsMoOfTrDesc_E.DngrsGoodDescForPackggDocsText ), 250 ) as DngrsGoodDescForPackggDocsText,
left( coalesce( _CmplRqRsltDaGdsMoOfTrDesc.DngrsGoodHazardNotesDocsText,
_CmplRqRsltDaGdsMoOfTrDesc_E.DngrsGoodHazardNotesDocsText ), 250 ) as DngrsGoodHazardNotesDocsText,
@DefaultAggregation: #SUM
cast(1 as int4) as NmbrOfCmplRqRslts,
_ChmlCmplncInfo,
_CmplncPrps,
_Product,
_CmplRqRsltProcessingStatus,
_OverallTransportPermission,
_DescOfDngrsGdsPckg,
_ClassifiedAsDangerousGood,
_EnclosureVariant,
_DngrsGoodsModeOfTransport,
_TransportPermission,
_DangerousGoodsClass,
_DangerousGoodsPackingGroup
}
where
I_CmplRqRslt._ChmlCmplncInfo.ChmlCmplncInfoType = 'PP'
and I_CmplRqVersEnhanced.CmplRqApplicationComponent = 'DG'
and(
(
I_CmplRqRslt.CmplRqRsltProcessingStatus = 'IP'
and I_CmplRqRslt.CmplRqRsltCalculatedStatus != ''
)
or I_CmplRqRslt.CmplRqRsltProcessingStatus = 'RE'
or I_CmplRqRslt.CmplRqRsltProcessingStatus = 'OD'
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHMLCMPLNCINFO",
"I_CHMLCMPLNCMATLASSGMT",
"I_CHMLCMPLNCPRODASSGMT",
"I_CHMLCMPLNCPRPSASSGMT",
"I_CLASSIFIEDASDANGEROUSGOOD",
"I_CLASSIFIEDASDANGEROUSGOODTXT",
"I_CMPLNCPRPS",
"I_CMPLNCPRPSDIMENSION",
"I_CMPLNCPRPSRQMTASSGMT",
"I_CMPLNCPRPSTEXT",
"I_CMPLRQRSLT",
"I_CMPLRQRSLTDAGDSMOOFTR",
"I_CMPLRQRSLTDAGDSMOOFTRDESC",
"I_CMPLRQRSLTDNGRSGDSBSCDETS",
"I_CMPLRQRSLTPROCESSINGSTATUS",
"I_CMPLRQRSLTPROCGSTSTXT",
"I_CMPLRQVERSENHANCED",
"I_DESCOFDNGRSGDSPCKG",
"I_DESCOFDNGRSGDSPCKGTEXT",
"I_DNGRSGOODSMODEOFTRANSPORT",
"I_DNGRSGOODSMODEOFTRANSPORTTXT",
"I_ENCLOSUREVARIANT",
"I_ENCLOSUREVARIANTTEXT",
"I_PCKGDDNGRSGOODENCLOSUREDETS",
"I_PRODUCT",
"I_PRODUCTTEXT",
"I_TRANSPORTPERMISSION",
"I_TRANSPORTPERMISSIONTEXT"
],
"ASSOCIATED":
[
"I_CHMLCMPLNCINFO",
"I_CLASSIFIEDASDANGEROUSGOOD",
"I_CMPLNCPRPSDIMENSION",
"I_CMPLRQRSLTDAGDSMOOFTRDESC",
"I_CMPLRQRSLTPROCESSINGSTATUS",
"I_DESCOFDNGRSGDSPCKG",
"I_DNGRSGDSCLASSORDIVISIONVH",
"I_DNGRSGDSPACKINGGROUPVH",
"I_DNGRSGOODSMODEOFTRANSPORT",
"I_ENCLOSUREVARIANT",
"I_PRODUCT",
"I_TRANSPORTPERMISSION"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/