P_HzdsSubstInvtrySubstProposal

DDL: P_HZDSSUBSTINVTRYSUBSTPROPOSAL SQL: PHASSUBINVPROPU Type: view CONSUMPTION Package: EHHSS_CNS_HSI

Private view for proposed substances for the HSI

P_HzdsSubstInvtrySubstProposal is a Consumption CDS View that provides data about "Private view for proposed substances for the HSI" in SAP S/4HANA. It reads from 8 data sources and exposes 4 fields with key fields ChemicalUUID, HzdsSubstInventoryUUID. Part of development package EHHSS_CNS_HSI.

8 data source(s) 4 fields

Data Sources (8)

SourceAliasJoin Type
I_ChemicalRevision _ChemicalRevision inner
I_ChemicalRoot _ChemicalRoot inner
I_EHSLocationStorageLocation _EHSLocationSL inner
I_EHSLocationRevision _HSILocation inner
I_HzdsSubstInventoryDraft _HzdsSubstInventoryDraft inner
I_MaterialDocumentItem_2 _MaterialDocumentItem inner
I_HzdsSubstInvtryRlvtMvtType _Movementtype inner
I_ChemicalMaterial I_ChemicalMaterial from

Parameters (2)

NameTypeDefault
P_StartDate budat
P_EndDate budat

Annotations (11)

NameValueLevelField
AbapCatalog.sqlViewName PHASSUBINVPROPU view
ClientHandling.algorithm #SESSION_VARIABLE view
AbapCatalog.compiler.compareFilter true view
AbapCatalog.preserveKey true view
VDM.viewType #CONSUMPTION view
AccessControl.authorizationCheck #NOT_REQUIRED view
VDM.private true view
DataAging.noAgingRestriction true view
ObjectModel.usageType.serviceQuality #C view
ObjectModel.usageType.sizeCategory #XXL view
ObjectModel.usageType.dataClass #MIXED view

Fields (4)

KeyFieldSource TableSource FieldDescription
KEY ChemicalUUID I_ChemicalMaterial ChemicalUUID
KEY HzdsSubstInventoryUUID I_HzdsSubstInventoryDraft HzdsSubstInventoryUUID
LastGoodsMovementPostingDate
CreationDateTime 
@AbapCatalog.sqlViewName: 'PHASSUBINVPROPU'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true
@DataAging.noAgingRestriction:true

@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED

define view P_HzdsSubstInvtrySubstProposal 
with parameters P_StartDate : budat,
                P_EndDate : budat

as select from I_ChemicalMaterial

   inner join I_ChemicalRevision              as _ChemicalRevision        on I_ChemicalMaterial.ChemicalRevisionUUID  = _ChemicalRevision.ChemicalRevisionUUID

   inner join I_ChemicalRoot                  as _ChemicalRoot            on _ChemicalRoot.ChemicalUUID               = I_ChemicalMaterial.ChemicalUUID    

   inner join I_MaterialDocumentItem_2        as _MaterialDocumentItem    on I_ChemicalMaterial.Material              = _MaterialDocumentItem.Material  // correction 3032388 15.03.2021

 
   inner join I_EHSLocationStorageLocation    as _EHSLocationSL           on _EHSLocationSL.StorageLocation           =  _MaterialDocumentItem.StorageLocation
                                                                         and _EHSLocationSL.Plant                     =  _MaterialDocumentItem.Plant   

   inner join I_EHSLocationRevision           as _HSILocation             on _HSILocation.EHSLocationUUID             = _EHSLocationSL.EHSLocationUUID
                                                                         and _HSILocation.EHSLocationRevisionUUID     = _EHSLocationSL.EHSLocationRevisionUUID
                                                                         
   inner join I_HzdsSubstInvtryRlvtMvtType    as _Movementtype            on _Movementtype.Plant = _HSILocation.Plant              
                                                                         and _Movementtype.GoodsMovementType = _MaterialDocumentItem.GoodsMovementType   
                                                                          
   inner join I_HzdsSubstInventoryDraft       as _HzdsSubstInventoryDraft on _HzdsSubstInventoryDraft.EHSLocationUUID = _EHSLocationSL.EHSLocationUUID
     
{
    key I_ChemicalMaterial.ChemicalUUID,
    key _HzdsSubstInventoryDraft.HzdsSubstInventoryUUID, 
    max(_MaterialDocumentItem.PostingDate) as LastGoodsMovementPostingDate,
    tstmp_to_dats(_ChemicalRoot.CreationDateTime, abap_system_timezone( $session.client, 'NULL'),  $session.client, 'NULL') as CreationDateTime    
   
}
where _MaterialDocumentItem.PostingDate   >= $parameters.P_StartDate
  and _MaterialDocumentItem.PostingDate   <= $parameters.P_EndDate
  and _ChemicalRevision.RevisionStartDate <= $session.system_date
  and _ChemicalRevision.RevisionEndDate   >= $session.system_date
  and _HSILocation.RevisionStartDate      <= $session.system_date
  and _HSILocation.RevisionEndDate        >= $session.system_date

group by I_ChemicalMaterial.ChemicalUUID, 
         _HzdsSubstInventoryDraft.HzdsSubstInventoryUUID,
         _ChemicalRoot.CreationDateTime

Learn More