@AbapCatalog.sqlViewName: 'PDGRKFPREPDATA'
@AbapCatalog.preserveKey: true
//@EndUserText.label: 'Prepare Data for Key figures'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.type: #INHERITED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #D -- View will be consumed for Analytical Reporting
@ObjectModel.usageType.sizeCategory: #XXL -- No. of Records > 100,000
define view P_DGR_KF_PrepareData
with parameters
P_CompanyCode : bukrs,
P_PlantGroup : /accgo/e_plant_group,
P_DisplayUnitOfMeasure : cds_dgr_display_uom,
P_DailyGrainReportType : cds_dgr_report_type,
P_DailyGrainReportDate : cds_dgr_date
as select from P_DGR_KF_SegregateQuanty
( P_CompanyCode : :P_CompanyCode,
P_PlantGroup : :P_PlantGroup,
P_DisplayUnitOfMeasure : :P_DisplayUnitOfMeasure,
P_DailyGrainReportType : :P_DailyGrainReportType,
P_DailyGrainReportDate : :P_DailyGrainReportDate) as DGRKF
left outer join I_WrhsReceiptObligationType as OblgType on DGRKF.ObligationType = OblgType.WarehouseReceiptObligationType
-- Fill License Data on Measure Up Records for License/Unlicensed Qty Ratio
left outer join P_DGR_License_DataRecord
( P_DisplayUnitOfMeasure : :P_DisplayUnitOfMeasure,
P_DailyGrainReportType : :P_DailyGrainReportType,
P_DailyGrainReportDate : :P_DailyGrainReportDate) as LicenseDataforMeasureUp on DGRKF.MTMDataSourceType = 'N2' -- Goods Movement Records in Konvd Stack
and DGRKF.LoadDataCaptureEventType = '16' -- Measure Up Records
and DGRKF.Plant = LicenseDataforMeasureUp.Plant
and DGRKF.StorageLocation = LicenseDataforMeasureUp.StorageLocation
and DGRKF.PhysicalCommodity = LicenseDataforMeasureUp.PhysicalCommodity
left outer join I_AutoShipStorage as AutoShippable on DGRKF.Plant = AutoShippable.Plant
and DGRKF.StorageLocation = AutoShippable.StorageLocation
left outer join I_LoadDataCaptureHdr as LDCData on DGRKF.LoadDataCaptureObjectID = LDCData.LDCId
{
//P_DGR_KF_SegregateQuanty
key DGRKF.PricingKey,
key DGRKF.PricingDocumentItem,
key DGRKF.ConditionType,
key DGRKF.SubConditionType,
key DGRKF.VersStockRecordUUID,
key DGRKF.SourceDocument,
key DGRKF.SourceDocumentItem,
key DGRKF.QuantityHistorySequenceNumber,
key HandleLossRsrvId,
key HandleLossRsrvItem,
key DGRKF.HandleLossRsrvVersion,
key DGRDataSourceType,
key DGRQuantiyType,
DGRKF.CompanyCode,
DGRKF.Plant,
DGRKF.StorageLocation,
DGRKF.SourceDocumentBusObjType,
DGRKF.Material,
DGRKF.PhysicalCommodity,
DGRKF.MTMDataSourceType,
DGRKF.SourceTransactionEventDateTime,
DGRKF.SourceTransacEventEndDateTime,
// MTMCalculationGroup,
DGRKF.CommodityExposureQuantity,
DGRKF.CommodityExposureUnit,
DGRKF.QuantitySign,
DGRKF.LoadDataCaptureObjectID,
DGRKF.LoadDataCaptureEventType,
DGRKF.QuantityRepositoryUUID,
DGRKF.QuantityRepositoryVersion,
DGRKF.DiscPremQtyScheduleUniqueID,
DiscPremQtySchedValdtyVersion,
DGRKF.DiscPremQtyScheduleVersion,
DGRKF.DiscPremQtyScheduleType,
DGRKF.EndOfDayTimestamp,
DGRKF.PreviousEndOfDayTimestamp,
DGRKF.BegingingBookBalancetTimestamp,
DGRKF.DailyGrainReportDisplayUnit,
DGRKF.DailyGrainReportType,
DGRKF.DailyGrainReportDate,
LDCData.LDCIsMultipleBuySellRelevant,
// LicenseExposureUnit,
case when DGRKF.MTMDataSourceType = 'N2' -- Goods Movement Records in Konvd Stack
and DGRKF.LoadDataCaptureEventType = '16' -- Measure Up Records
then LicenseDataforMeasureUp.licenseStatus
else DGRKF.LicenseStatus
end as LicenseStatus,
DGRKF.PlantGroup,
ApplicationStatus,
WarehouseReceiptType,
WarehouseReceiptNo,
ObligationType,
OblgType.WrhsReceiptObligationTypeGroup as WrhsReceiptObligationTypeGroup,
StorageAgreement,
StorageAgreementItem,
IsHandleLossRsrvRecieptFwd,
IsHandleLossRsrvCancellFwd,
Supplier,
SubstitutedMaterial,
SubstitutedPlant,
IsSubstitutedRecord,
SttlInvoicePaymentDateTime,
BusinessPartnerClassification,
-- For Delta quantity with Bin Details
A2CommodityQuantityInBaseUnit, -- Total Gross Qty in base unit(on A2 Record) after Goods Movement
A2NetPriceExpsrQtyInBaseUnit, -- Total Net Qty in base unit(on A2 Record) after Goods Movement
GrossDocumentQty, -- Document Quantity
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case when DGRDataSourceType = '01' and
DGRQuantiyType = 'NT' and
((MTMDataSourceType = '02' and -- Stock On Hand Records
QuantitySign = -1) -- Only for Loads
or
MTMDataSourceType = 'E2') and -- Load Vs Unloads
A2CommodityQuantityInBaseUnit <> 0
then ((cast(A2NetPriceExpsrQtyInBaseUnit as abap.fltp) / cast(A2CommodityQuantityInBaseUnit as abap.fltp) ) * cast(GrossDocumentQty as abap.fltp))
else cast(0 as abap.fltp)
end as NetDocQtyWithBinDetails,
-- Licese Data for Measure Up Key Figures(Measure Up Split in Licesed and Unlicensed Key Figures)
LicenseDataforMeasureUp.LicenseQuantityUnit as LicenseExposureUnit,
LicenseDataforMeasureUp.LicenseQuantity as LicenseExposureQuantity,
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
cast(BeginingBookBalanceExposureQty as abap.quan( 17, 3) ) as BeginingBookBalanceExposureQty,
case when DGRKF.MTMDataSourceType = 'N2' -- Goods Movement Records in Konvd Stack
and DGRKF.LoadDataCaptureEventType = '16' -- Measure Up Records
then cast(LicenseDataforMeasureUp.LicenseQuantity as abap.quan( 17, 3) )
else cast(LicenseExposureQty as abap.quan( 17, 3) )
end as LicenseExposureQty,
cast(BeginBookBalOverrideAdjQty as abap.quan( 17, 3)) as BeginBookBalOverrideAdjQty,
cast(OverrideAdjustmentQty as abap.quan( 17, 3)) as OverrideAdjustmentQty,
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
cast(BegingingBookBalEventsQuantity as abap.quan( 17, 3) ) as BegingingBookBalEventsQuantity,
cast(CurrentBookBalanceQuantity as abap.quan( 17, 3) ) as CurrentBookBalanceQuantity,
cast(StorageQtyHistoryQuantity as abap.quan( 17, 3) ) as StorageQtyHistoryQuantity,
cast(HLRForwardQuantity as abap.quan( 17, 3) ) as HLRForwardQuantity,
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType < '18' and
DGRKF.LoadDataCaptureEventType <> '16' and
DGRKF.LoadDataCaptureObjectID is not initial and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalLDCLoadUnloadQty, -- BeginningBookBalLDCLoadUnloadQty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case when DGRKF.LoadDataCaptureEventType = '18' and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalLDCLoadLossQty, --BeginBookBalLoadLossLDCQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case when DGRKF.LoadDataCaptureEventType = '19' and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else 0
end as BeginBookBalLDCUnloadLossQty, --BeginBookBalUnLoadLossLDCQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType = '20' and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalProdnSrvcGMQty, --BeginBookBalProdnSrvcGdsMovQty, --ProdnSrvcGdsMovementQty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case when DGRKF.LoadDataCaptureEventType = '21' and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalProdnSrvcLossQty, --BeginBookBalLossProdnSrvcQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType = '22' and
DGRKF.LoadDataCaptureObjectID is initial and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalManualQty, --BeginningBookBalManualQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType = '16' and
DGRKF.LoadDataCaptureObjectID is initial and
PreviousEndOfDayTimestamp between SourceTransactionEventDateTime and SourceTransacEventEndDateTime and
MTMDataSourceType = 'N2'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) * -1 as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalMeasureUpQty, --BeginningBookBalManualQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType is initial or DGRKF.LoadDataCaptureEventType is null and
MTMDataSourceType = '02'
then cast((BegingingBookBalEventsQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as BeginBookBalOtherQuantity, --BeginBookBalOtherQuantity,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.MTMDataSourceType = 'E2' and
PreviousEndOfDayTimestamp between SourceTransactionEventDateTime and SourceTransacEventEndDateTime
then (cast(BegingingBookBalEventsQuantity as abap.quan( 17, 3)) * DGRKF.QuantitySign)
else cast(0 as abap.quan( 17, 3) )
end as BegingingBookBalLoadvsUnlodQty,
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.LoadDataCaptureEventType is initial or DGRKF.LoadDataCaptureEventType is null and
MTMDataSourceType = '02'
then cast((CurrentBookBalanceQuantity * DGRKF.QuantitySign) as abap.quan( 17, 3))
else cast(0 as abap.quan( 17, 3) )
end as DailyGrainRptOtherQuantity, --DailyGrainRptOtherQuantity,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.MTMDataSourceType = '02' and
(DGRKF.LoadDataCaptureEventType = '01' or
DGRKF.LoadDataCaptureEventType = '02' or
DGRKF.LoadDataCaptureEventType = '03' or
DGRKF.LoadDataCaptureEventType = '04' or
DGRKF.LoadDataCaptureEventType = '05' or
DGRKF.LoadDataCaptureEventType = '06' or
DGRKF.LoadDataCaptureEventType = '07' or
DGRKF.LoadDataCaptureEventType = '08' or
DGRKF.LoadDataCaptureEventType = '09' or
DGRKF.LoadDataCaptureEventType = '10' or
DGRKF.LoadDataCaptureEventType = '11' or
DGRKF.LoadDataCaptureEventType = '12' or
DGRKF.LoadDataCaptureEventType = '13' or
DGRKF.LoadDataCaptureEventType = '14' or
DGRKF.LoadDataCaptureEventType = '15' or
DGRKF.LoadDataCaptureEventType = '17' or
DGRKF.LoadDataCaptureEventType = '23' ) and
DGRKF.LoadDataCaptureEventType <> '16' and
DGRKF.LoadDataCaptureObjectID is not initial
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as LDCLoadUnloadQty, --Load + Unload Quantity
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '18' and
MTMDataSourceType = '02'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as LDCLoadLossQty, --LoadLossLDCQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '19' and
MTMDataSourceType = '02'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as LDCUnloadLossQty, --UnLoadLossLDCQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '20' and
MTMDataSourceType = '02'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as ProdnSrvcGoodsMovementQty, -- ProdnSrvcGdsMovementQty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '21' and
MTMDataSourceType = '02'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as ProdnSrvcLossQty, --LossProdnSrvcQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '22' and
DGRKF.LoadDataCaptureObjectID is initial and
MTMDataSourceType = '02'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign
else 0
end as abap.quan( 17, 3)) as ManualQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
cast(case when DGRKF.LoadDataCaptureEventType = '16' and
DGRKF.LoadDataCaptureObjectID is initial and
EndOfDayTimestamp between SourceTransactionEventDateTime and SourceTransacEventEndDateTime and
MTMDataSourceType = 'N2'
then CurrentBookBalanceQuantity * DGRKF.QuantitySign * -1
else 0
end as abap.quan( 17, 3)) as MeasureUpQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.MTMDataSourceType = 'E2' and
// EndOfDayTimestamp between SourceTransactionEventDateTime and SourceTransacEventEndDateTime
SourceTransactionEventDateTime between PreviousEndOfDayTimestamp and EndOfDayTimestamp -- E2 records Created Today
then (cast(CurrentBookBalanceQuantity as abap.quan( 17, 3)) * DGRKF.QuantitySign)
else cast(0 as abap.quan( 17, 3) )
end as LoadvsUnloadQty,
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
@DefaultAggregation:#NONE
case
when DGRKF.MTMDataSourceType = 'E2' and
SourceTransacEventEndDateTime between PreviousEndOfDayTimestamp and EndOfDayTimestamp -- E2 records Cleared Today
then (cast(CommodityExposureQuantity as abap.quan( 17, 3)) * DGRKF.QuantitySign)
else cast(0 as abap.quan( 17, 3) )
end as TodayClearedLoadvsUnloadQty,
DailyGrainRptCushionMaxQty,
DailyGrainRptCushionPercentage,
DailyGrainRptCushionUnit,
TradingContract,
TradingContractItem,
TradingContractStatus,
ApplicationDocType,
ApplicationGuid,
ApplicationVersion,
ApplicationDocCreationDate,
ApplicationDocAppliedDate,
ApplicationDocSettlementDate,
ContractIsConfirmed,
UnappliedQuantity,
AppliedQuantity,
UnassignedQuantity,
DailyGrainRptOpenStorage,
LoadDataCaptureApplInstr as LoadDataCaptureApplInstruction,
// Calculate the Unapplied Unloads Qty from Unapplied Qty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
case when ApplicationDocType = 'IAP' or ApplicationDocType = 'ICP' or
ApplicationDocType = 'PAD' or ApplicationDocType = 'RSD' //CAD's are excluded as that Qty is taken case in Unassigned KF
then UnappliedQuantity
else cast(0 as abap.quan( 17, 3 ))
end as UnappliedUnloadsQuantity,
// Calculate the Unapplied loads Qty from Unapplied Qty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
case when ApplicationDocType = 'IAS' or ApplicationDocType = 'ICS' or
ApplicationDocType = 'SAD' or ApplicationDocType = 'RPD'
then UnappliedQuantity
else cast(0 as abap.quan( 17, 3 ))
end as UnappliedLoadsQuantity,
// Calculate the Applied Unloads Qty from Applied Qty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
case when ApplicationDocType = 'CAD' or ApplicationDocType = 'IAP' or
ApplicationDocType = 'ICP' or ApplicationDocType = 'PAD' or
ApplicationDocType = 'RSD' //CAD's are NOT excluded as that Qty is taken out of Unassigned KF
then AppliedQuantity
else cast(0 as abap.quan( 17, 3 ))
end as AppliedUnloadsQuantity,
// Calculate the Unapplied loads Qty from Unapplied Qty
@Semantics.quantity.unitOfMeasure: 'CommodityExposureUnit'
case when ApplicationDocType = 'IAS' or ApplicationDocType = 'ICS' or
ApplicationDocType = 'SAD' or ApplicationDocType = 'RPD'
then AppliedQuantity
else cast(0 as abap.quan( 17, 3 ))
end as AppliedLoadsQuantity,
case
when
AutoShippable.Plant is not null and
AutoShippable.StorageLocation is not null
then 'X'
else ' '
end as StorageLocationIsAutoShippable
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_AUTOSHIPSTORAGE",
"I_LOADDATACAPTUREHDR",
"I_WRHSRECEIPTOBLIGATIONTYPE",
"P_DGR_KF_SEGREGATEQUANTY",
"P_DGR_LICENSE_DATARECORD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/