@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
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CHEMICALMATERIAL",
"I_CHEMICALREVISION",
"I_CHEMICALROOT",
"I_EHSLOCATIONREVISION",
"I_EHSLOCATIONSTORAGELOCATION",
"I_HZDSSUBSTINVENTORYDRAFT",
"I_HZDSSUBSTINVTRYRLVTMVTTYPE",
"I_MATERIALDOCUMENTITEM_2"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/