@AbapCatalog.sqlViewName: 'PMMOVERDUENRREL'
@VDM.viewType: #CONSUMPTION
//@Analytics.dataCategory: #CUBE
@AbapCatalog.preserveKey: true
@VDM.private : true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_OverdueNotReleased
with parameters
P_StartDate : mm_a_delivery_date,
P_EndDate : mm_a_delivery_date
as select from I_PurchaseOrderItem as PurchaseOrderItem
left outer join P_PurchaseOrderScheduleLine1 as PurchaseOrderScheduleLine on PurchaseOrderScheduleLine.PurchaseOrder = PurchaseOrderItem.PurchaseOrder
and PurchaseOrderScheduleLine.PurchaseOrderItem = PurchaseOrderItem.PurchaseOrderItem
and PurchaseOrderScheduleLine.OpenPurchaseOrderQuantity > 0
// inner join P_RelevantDates(P_Date : $parameters.P_Date , P_EvaluationTimeFrameInDays : $parameters.P_EvaluationTimeFrameInDays ) as _Date
// on I_PurchaseOrderScheduleLine.ScheduleLineDeliveryDate = _Date.CalendarDate
//
left outer join P_ScheduleLineConfirmation as ScheduleLineConfirmation on ScheduleLineConfirmation.PurchaseOrder = PurchaseOrderScheduleLine.PurchaseOrder
and ScheduleLineConfirmation.PurchaseOrderItem = PurchaseOrderScheduleLine.PurchaseOrderItem
and ScheduleLineConfirmation.PurchaseOrder = PurchaseOrderScheduleLine.PurchaseOrder
left outer join P_OverduePurOrdItemsCount on PurchaseOrderScheduleLine.PurchaseOrder = P_OverduePurOrdItemsCount.PurchaseOrder
and PurchaseOrderScheduleLine.PurchaseOrderItem = P_OverduePurOrdItemsCount.PurchaseOrderItem
association [1..1] to I_PurchasingOrganization as _PurchasingOrganization on $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization
association [1..1] to I_PurchasingGroup as _PurchasingGroup on $projection.purchasinggroup = _PurchasingGroup.PurchasingGroup
association [1..1] to I_Country as _Country on $projection.SupplierCountry = _Country.Country
{
key cast( PurchaseOrderItem.PurchaseOrder as vdm_purchaseorder ) as PurchaseOrder,
key cast( PurchaseOrderItem.PurchaseOrderItem as vdm_purchaseorderitem ) as PurchaseOrderItem,
PurchaseOrderItem.PurchaseOrderItemText,
_PurchaseOrderItem._PurchaseOrder.PurchasingOrganization as PurchasingOrganization,
_PurchasingOrganization,
_PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
_PurchasingGroup,
_PurchaseOrderItem.DocumentCurrency as Currency,
_PurchaseOrderItem.Material as Material,
_PurchaseOrderItem._PurchaseOrder.Supplier,
_PurchaseOrderItem.CompanyCode,
_PurchaseOrderItem.Plant,
_PurchaseOrderItem.MaterialGroup,
_PurchaseOrderItem.PurchaseOrderQuantityUnit,
_PurchaseOrderItem.OrderQuantity as PurchaseOrderQty,
// _Date.CalendarWeek as CalendarWeek,
//
// _Date.CalendarMonth as CalendarMonth,
//
// _Date.CalendarQuarter as CalendarQuarter,
//
// _Date.CalendarYear as CalendarYear,
_PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted,
min(ScheduleLineDeliveryDate) as ScheduleLineDeliveryDate,
// case _PurchaseOrderItem.GoodsReceiptIsExpected
// when ' '
// then cast( '00000000' as abap.dats(8))
// else min(ScheduleLineDeliveryDate)
// end as ScheduleLineDeliveryDate,
//
sum( OpenPurchaseOrderQuantity ) as OpenPurchaseOrderQuantity,
$session.system_date as today,
cast( _PurchaseOrder._Supplier._StandardAddress.Country as mm_a_supplier_country ) as SupplierCountry,
sum( OpenPurchaseOrderNetAmount ) as OpenPurchaseOrderNetAmount,
max( IsConfirmedScheduleLine ) as IsConfirmedScheduleLine,
case when NumberOfOverduePurOrdItm > 0
then 'X'
else ''
end as IsOverDue,
PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate,
_Country
//
}
where
// OpenPurchaseOrderQuantity>0 and
// PurchaseOrderScheduleLine.ScheduleLineDeliveryDate >= :P_StartDate and
// PurchaseOrderScheduleLine.ScheduleLineDeliveryDate <= :P_EndDate and
/// PurchaseOrderScheduleLine.ScheduleLineDeliveryDate <> '00000000' and
// ScheduleLineOrderQuantity > 0
// and ScheduleLineOrderQuantity <> RoughGoodsReceiptQty
// and _PurchaseOrderItem.GoodsReceiptIsExpected = 'X'
// and ScheduleLineDeliveryDate < :P_Date
//
PurchaseOrderItem.PurchasingDocumentDeletionCode = ''
and PurchaseOrderItem._PurchaseOrder.PurchasingDocumentDeletionCode = ''
and PurchaseOrderItem.IsCompletelyDelivered = ''
and(
(
PurchaseOrderItem._PurchaseOrder._Supplier.IsBusinessPurposeCompleted <> 'X'
)
or(
// PurchaseOrderItem._PurchaseOrder._Supplier.Supplier = ' '
PurchaseOrderItem._PurchaseOrder.Supplier = ' '
)
)
//and _PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus != 'X'
group by
PurchaseOrderItem.PurchaseOrder,
PurchaseOrderItem.PurchaseOrderItem,
PurchaseOrderItem.PurchaseOrderItemText,
_PurchaseOrderItem._PurchaseOrder.PurchasingOrganization,
_PurchaseOrderItem._PurchaseOrder.PurchasingGroup,
_PurchaseOrderItem.DocumentCurrency,
_PurchaseOrderItem.Material,
_PurchaseOrderItem.OrderQuantity,
_PurchaseOrderItem._PurchaseOrder.Supplier,
_PurchaseOrder._Supplier._StandardAddress.Country,
_PurchaseOrderItem.CompanyCode,
_PurchaseOrderItem.Plant,
_PurchaseOrderItem.MaterialGroup,
_PurchaseOrderItem.PurchaseOrderQuantityUnit,
_PurchaseOrderItem._PurchaseOrder.PurchasingCompletenessStatus,
_PurchaseOrderItem.GoodsReceiptIsExpected,
PurchaseOrderItem._PurchaseOrder.PurchaseOrderDate,
// _Date.CalendarWeek,
// _Date.CalendarMonth,
// _Date.CalendarQuarter,
// _Date.CalendarYear,
_PurchaseOrderItem._PurchaseOrder.ReleaseIsNotCompleted,
NumberOfOverduePurOrdItm