I_ChmlCmplncSuplrMatlAnlyts1

@AbapCatalog.sqlViewName: 'ICCSMASSA1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true

@AccessControl.personalData.blocking: #REQUIRED
@ObjectModel.representativeKey: 'Material'

@EndUserText.label: 'Dimension Raw Material Composition'

@AccessControl.authorizationCheck: #CHECK

@ClientHandling:
{
  type: #INHERITED,
  algorithm: #SESSION_VARIABLE
}

@ObjectModel.usageType:{
  dataClass: #MIXED,
  sizeCategory: #XL,
  serviceQuality: #D
}

@VDM.viewType: #COMPOSITE

@Metadata.allowExtensions: true

define view I_ChmlCmplncSuplrMatlAnlyts1
  as select from I_ChmlCmplncSuplrMatlEnhanced as ChmlCmplncSuplrMatlEnhanced

  -- inner join Composition
    inner join   I_ChmlComposition             as _ChemicalComposition on  ChmlCmplncSuplrMatlEnhanced.ChmlCmplncInfoUUID =  _ChemicalComposition.ChmlCmplncInfoUUID
                                                                       and _ChemicalComposition.ChmlCompositionType       <> 'MATLBSD1'
                                                                       and _ChemicalComposition.ChmlCompositionType       <> 'MATLBSD2'
                                                                       and _ChemicalComposition.ChmlCompositionStatus     =  'RE'
  -- Association to Business Partner
  association [1..1] to I_BusinessPartner as _BusinessPartner on $projection.BusinessPartnerSupplier = _BusinessPartner.BusinessPartner

{
       -- UUID of Raw Material
  key  ChmlCmplncSuplrMatlEnhanced.ChmlCmplncInfoUUID,

       -- Composition Type
  key  _ChemicalComposition.ChmlCompositionType,

       -- UUID of Company Substance from Composition of Raw Material
  key  _ChemicalComposition._ChmlComponent._Substance.SubstanceUUID,

       -- Business Partner Supplier
  key  BusinessPartnerSupplier,

       -- Logistic Material of Raw Material
  key  cast( ChmlCmplncSuplrMatlEnhanced._ChmlCmplncInfo._MaterialAssignment.Material as ehfnd_cci_product preserving type ) as Material,

       -- Indicator that Material assigned to unpackaged product is primary
  key  ChmlCmplncSuplrMatlEnhanced._ChmlCmplncInfo._MaterialAssignment.MatlAssgmtIsPrimary,

       -- UUID of Supplier Material
  key  ChmlCmplncSuplrMatlEnhanced.ChmlSuplrMatlUUID,

       -- Product Group
       ChmlCmplncSuplrMatlEnhanced._ChmlCmplncInfo._MaterialAssignment._Product.ProductGroup,

       -- UUID of Business Partner
       BusinessPartnerUUID,

       -- Phone Number
       _BusinessPartner._CurrentDefaultAddress._StandardAddress._DefaultPhoneNumber.PhoneNumber,

       -- Mail Adress
       _BusinessPartner._CurrentDefaultAddress._StandardAddress._DefaultEmailAddress.EmailAddress,

       -- Country of Supplier
       cast(_BusinessPartner._CurrentDefaultAddress._StandardAddress.Country as ehfnd_country_id preserving type )           as Country,

       -- Supplier Material Number
       _SupplierMaterial.SupplierMaterialNumber,

       -- Supplier Material Name
       _SupplierMaterial.ChmlSuplrMatlName,

       /* Associations */
       ChmlCmplncSuplrMatlEnhanced._SupplierMaterial,
       ChmlCmplncSuplrMatlEnhanced._ChmlCmplncInfo
}
where
  -- Filter Suppliers that are blocked or outdated
      ChmlCmplncSuplrMatlEnhanced.ChmlSuplrMatlSuplrSts <> 'BL'
  and ChmlCmplncSuplrMatlEnhanced.ChmlSuplrMatlSuplrSts <> 'OD'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESS",
"I_ADDRESSEMAILADDRESS",
"I_ADDRESSPHONENUMBER",
"I_BPCURRENTDEFAULTADDRESS",
"I_BUSINESSPARTNER",
"I_CHMLCMPLNCINFO",
"I_CHMLCMPLNCMATLASSGMT",
"I_CHMLCMPLNCSUPLRMATLENHANCED",
"I_CHMLCOMPONENT",
"I_CHMLCOMPOSITION",
"I_CHMLSUPLRMATL",
"I_PRODUCT",
"I_SUBSTANCEENHANCED"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_CHMLCMPLNCINFO",
"I_CHMLSUPLRMATL"
],
"BASE":
[
"I_CHMLCMPLNCSUPLRMATLENHANCED"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/