@AbapCatalog.sqlViewName: 'PPORDITEMCALC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #CONSUMPTION
@VDM.private: true
@ObjectModel.usageType.serviceQuality: #P
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@EndUserText.label: 'PO Item Calculated fields'
define view P_PurOrderItemCalculation
as select from I_PurchaseOrderItem
association [1..1] to P_PurchaseOrderConvertedAmount as _ConvertedNetAmount on $projection.PurchaseOrder = _ConvertedNetAmount.PurchaseOrder
and $projection.PurchaseOrderItem = _ConvertedNetAmount.PurchaseOrderItem
/* Association to fetch Goods Receipt Quantity */
association [1..1] to R_PurgDocGoodsReceiptCalc as _GoodsReceiptCalc on $projection.PurchaseOrder = _GoodsReceiptCalc.PurchaseOrder
and $projection.PurchaseOrderItem = _GoodsReceiptCalc.PurchaseOrderItem
/* Association to fetch History fields per Purchasing Document */
association [0..1] to P_POItemHistoryCalc as _POItemHistory on $projection.PurchaseOrder = _POItemHistory.PurchaseOrder
and $projection.PurchaseOrderItem = _POItemHistory.PurchaseOrderItem
association [1..1] to R_POItemEnhcdNextDeliveryCalc as _POItemEnhcdNextDeliveryCalc on _POItemEnhcdNextDeliveryCalc.PurchaseOrder = $projection.PurchaseOrder
and _POItemEnhcdNextDeliveryCalc.PurchaseOrderItem = $projection.PurchaseOrderItem
{
key PurchaseOrder,
key PurchaseOrderItem,
key cast ('X' as migration_completed) as PurgDocMigrtnIsCmpltdForAnlyts,
PurchaseOrderItemUniqueID,
concat(concat(PurchaseOrder, '/'), PurchaseOrderItem) as FormattedPurchaseOrderItem,
PurchaseOrderItemText,
AccountAssignmentCategory,
MaterialGroup,
Material,
CompanyCode,
Plant,
StorageLocation,
_StorageLocation.StorageLocationName as StorageLocationName,
ServicePerformer,
ProductType,
IsReturnsItem,
cast('1' as numc1) as ObjectType,
OrderQuantity,
NetPriceAmount,
NetPriceQuantity,
_ConvertedNetAmount.NetAmountInCoCodeCurrency as NetAmountInCoCodeCurrency,
OrderPriceUnit,
PurchaseOrderQuantityUnit,
PurchaseOrderItemCategory,
DocumentCurrency,
_CompanyCode.Currency as CompanyCodeCurrency,
cast('' as /cpd/pfp_workitem_id) as WorkItem,
GoodsReceiptIsExpected,
IsCompletelyDelivered,
InvoiceIsExpected,
IsFinallyInvoiced,
MultipleAcctAssgmtDistribution,
_PurgDocumentItemCategory._Text[1:Language=$session.system_language].PurgDocExternalItemCategory as PurgDocExternalItemCategory,
_Plant.PlantName as PlantName,
_CompanyCode.CompanyCodeName as CompanyCodeName,
// //For contact Card
_PurchaseOrder.CreatedByUser as CreatedByUser,
//Purchase Order Header Fields
_PurchaseOrder.PurchasingOrganization as PurchasingOrganization,
_PurchaseOrder.PurchasingGroup as PurchasingGroup,
_PurchaseOrder._PurchasingGroup.PurchasingGroupName as PurchasingGroupName, //3255677
_PurchaseOrder.SupplyingPlant as SupplyingPlant,
_PurchaseOrder._SupplyingPlant.PlantName as SupplyingPlantName,
_PurgDocumentItemCategory._Text[1:Language=$session.system_language].PurgDocItemCategoryName as PurgDocItemCategoryName,
_PurchaseOrder.Supplier as Supplier,
_PurchaseOrder._Supplier.SupplierName as SupplierName,
_PurchaseOrder.PurchaseOrderDate as PurchaseOrderDate,
_PurchaseOrder.PurchaseOrderType as PurchasingDocumentType,
_PurchaseOrder.CreationDate as CreationDate,
_PurchaseOrder._PurchaseOrderType._Text[1:Language = $session.system_language].PurchasingDocumentTypeName as PurchasingDocumentTypeName,
_PurchaseOrder._Supplier.IsBusinessPurposeCompleted as IsBusinessPurposeCompleted,
/*** PO Item Calculated Fields ******/
/** Net Amount **/
case when IsReturnsItem = 'X'
then -abs(NetAmount)
else NetAmount
end as NetAmount,
/** Delivered Quantity **/
case when (PurchaseOrderItemCategory = 'A') then cast (0 as abap.quan(13, 3))
when IsReturnsItem = 'X' then -abs(_GoodsReceiptCalc.GoodsReceiptQuantity)
else _GoodsReceiptCalc.GoodsReceiptQuantity
end as GoodsReceiptQuantity,
/** Delivered Value **/
cast( case when (PurchaseOrderItemCategory = 'A' and ProductType = '1')
then 0
when (PurchaseOrderItemCategory = 'A' and ProductType = '2') //3356450
then _POItemHistory.GoodsReceiptAmount
//then _GoodsRecieptAmountCalc.GoodsReceiptAmount
when (_GoodsReceiptCalc.GoodsReceiptQuantity is null or OrderQuantity is null or OrderQuantity = 0)
then 0
when IsReturnsItem = 'X'
then -abs(_GoodsReceiptCalc.GoodsReceiptQuantity * division(NetAmount, OrderQuantity, 4))
else _GoodsReceiptCalc.GoodsReceiptQuantity * division(NetAmount, OrderQuantity, 4)
end as abap.curr(21,5)) as GoodsReceiptAmount,
/** Still to Be Delivered Quantity**/
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A') then cast(0 as abap.quan(13, 3))
when IsReturnsItem = 'X'
then
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then -abs(OrderQuantity)
else -abs(OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity)
end
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then OrderQuantity
else OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity
end
end as StillToBeDeliveredQuantity,
/** Still to be Delivered Value **/
cast(case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or (PurchaseOrderItemCategory = 'A' and ProductType = '1')) then 0
when (PurchaseOrderItemCategory = 'A' and ProductType = '2') //3356450
then
case when _POItemHistory.GoodsReceiptAmount is null then NetAmount
else NetAmount - _POItemHistory.GoodsReceiptAmount
end
when OrderQuantity is null or OrderQuantity = 0 then 0
when IsReturnsItem = 'X'
then
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then -abs(NetAmount)
else -abs((OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity) * division(NetAmount, OrderQuantity, 4))
end
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then NetAmount
else (OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity) * division(NetAmount, OrderQuantity, 4)
end
end as abap.curr(21,5)) as StillToBeDeliveredValue,
/** Invoiced Amount **/
case when (PurchaseOrderItemCategory = 'A') or _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then cast (0 as abap.quan(13, 3))
else _POItemHistory.InvoiceReceiptQty
end as InvoiceReceiptQty,
/** Invoiced Value **/
case when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then cast ( 0 as abap.curr(21,5))
else
_POItemHistory.InvoiceRcptAmtInCoCodeCrcy end as InvoiceRcptAmtInCoCodeCrcy,
/** Still to Invoice Quantity **/
case when (InvoiceIsExpected <> 'X' or IsFinallyInvoiced = 'X' or PurchaseOrderItemCategory = 'A') then cast(0 as abap.quan(13, 3))
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity > OrderQuantity //^3028096
then
case when IsReturnsItem = 'X'
then cast((-1 * _GoodsReceiptCalc.GoodsReceiptQuantity) - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
else cast(_GoodsReceiptCalc.GoodsReceiptQuantity - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
end
else
case when IsReturnsItem = 'X'
then cast((-1 * OrderQuantity) - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
else cast(OrderQuantity - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
end
end //v3028096
end as StillToInvoiceQuantity,
/** Still to Invoice Value **/
cast(case when (InvoiceIsExpected <> 'X' or IsFinallyInvoiced = 'X')
then 0
when (_POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null and IsReturnsItem = 'X')
then -abs(_ConvertedNetAmount.NetAmountInCoCodeCurrency)
when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then _ConvertedNetAmount.NetAmountInCoCodeCurrency
when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy >= _ConvertedNetAmount.NetAmountInCoCodeCurrency
then 0
when PurchaseOrderItemCategory = 'A'
then _ConvertedNetAmount.NetAmountInCoCodeCurrency - _POItemHistory.InvoiceRcptAmtInCoCodeCrcy
when OrderQuantity is null or OrderQuantity = 0
then 0
when IsReturnsItem = 'X'
then -abs((OrderQuantity - abs(_POItemHistory.InvoiceReceiptQty)) * division(_ConvertedNetAmount.NetAmountInCoCodeCurrency, OrderQuantity, 4))
when _POItemHistory.InvoiceReceiptQty >= OrderQuantity
then cast(0 as abap.curr(21,5))
else (OrderQuantity - abs(_POItemHistory.InvoiceReceiptQty)) * division(_ConvertedNetAmount.NetAmountInCoCodeCurrency, OrderQuantity, 4)
end as abap.curr(21,5)) as StillToInvoiceValue,
/**** Next Delivery Date ****/
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A') then cast('00000000' as abap.dats)
else _POItemEnhcdNextDeliveryCalc.ScheduleLineDeliveryDate
end as DeliveryDate,
/*** Next Delivery Quantity ***/
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A')
then cast( 0 as abap.quan(13,3))
/** When PO Item is not a 'Return Item' and the Open Quantity is picked from Analytical MMPUR_ANA_EKET table **/
when (IsReturnsItem = '' )
then _POItemEnhcdNextDeliveryCalc.ScheduleLineOpenQuantity
/** When PO Item is a 'Return Item' and the Open Quantity is picked from Analytical MMPUR_ANA_EKET table **/
when (IsReturnsItem = 'X' )
then -1 * _POItemEnhcdNextDeliveryCalc.ScheduleLineOpenQuantity
else
/** case when PO Item is not a Returns Item and Open Quantity is picked from EKET table **/
_POItemEnhcdNextDeliveryCalc.ScheduleLineOpenQuantity
end as ScheduleLineOpenQty,
/********* PO Item Calculated Fields ******/
//Required for DCL
_PurchaseOrder.PurchaseOrderType,
_PurchaseOrder,
/*** Association ***/
_ProductType
}
where
PurchasingDocumentDeletionCode <> 'L'
and _PurchaseOrder.PurchasingDocumentDeletionCode <> 'L'
and _PurchaseOrder.PurchasingDocumentIsAged = ' '
// filter out statistical Items (excluding Retail i.e Material = '')
and not( Material = '' and IsStatisticalItem = 'X' )
union all
select from I_PurchaseOrderItem
association [1..1] to P_PurchaseOrderConvertedAmount as _ConvertedNetAmount on $projection.PurchaseOrder = _ConvertedNetAmount.PurchaseOrder
and $projection.PurchaseOrderItem = _ConvertedNetAmount.PurchaseOrderItem
/* Association to fetch Goods Receipt Quantity */
association [1..1] to R_PurgDocGoodsReceiptCalc as _GoodsReceiptCalc on $projection.PurchaseOrder = _GoodsReceiptCalc.PurchaseOrder
and $projection.PurchaseOrderItem = _GoodsReceiptCalc.PurchaseOrderItem
/* Association to fetch History fields per Purchasing Document */
association [0..1] to P_POItemHistoryCalc as _POItemHistory on $projection.PurchaseOrder = _POItemHistory.PurchaseOrder
and $projection.PurchaseOrderItem = _POItemHistory.PurchaseOrderItem
association [1..1] to R_POItemNextDeliveryCalc as _POItemNextDeliveryCalc on _POItemNextDeliveryCalc.PurchaseOrder = $projection.PurchaseOrder
and _POItemNextDeliveryCalc.PurchaseOrderItem = $projection.PurchaseOrderItem
{
key PurchaseOrder,
key PurchaseOrderItem,
key cast (' ' as migration_completed) as PurgDocMigrtnIsCmpltdForAnlyts,
PurchaseOrderItemUniqueID,
concat(concat(PurchaseOrder, '/'), PurchaseOrderItem) as FormattedPurchaseOrderItem,
PurchaseOrderItemText,
AccountAssignmentCategory,
MaterialGroup,
Material,
CompanyCode,
Plant,
StorageLocation,
_StorageLocation.StorageLocationName as StorageLocationName,
ServicePerformer,
ProductType,
IsReturnsItem,
cast('1' as numc1) as ObjectType,
OrderQuantity,
NetPriceAmount,
NetPriceQuantity,
_ConvertedNetAmount.NetAmountInCoCodeCurrency as NetAmountInCoCodeCurrency,
OrderPriceUnit,
PurchaseOrderQuantityUnit,
PurchaseOrderItemCategory,
DocumentCurrency,
_CompanyCode.Currency as CompanyCodeCurrency,
cast('' as /cpd/pfp_workitem_id) as WorkItem,
GoodsReceiptIsExpected,
IsCompletelyDelivered,
InvoiceIsExpected,
IsFinallyInvoiced,
MultipleAcctAssgmtDistribution,
_PurgDocumentItemCategory._Text[1:Language=$session.system_language].PurgDocExternalItemCategory as PurgDocExternalItemCategory,
_Plant.PlantName as PlantName,
_CompanyCode.CompanyCodeName as CompanyCodeName,
// //For contact Card
_PurchaseOrder.CreatedByUser as CreatedByUser,
//Purchase Order Header Fields
_PurchaseOrder.PurchasingOrganization as PurchasingOrganization,
_PurchaseOrder.PurchasingGroup as PurchasingGroup,
_PurchaseOrder._PurchasingGroup.PurchasingGroupName as PurchasingGroupName, //3255677
_PurchaseOrder.SupplyingPlant as SupplyingPlant,
_PurchaseOrder._SupplyingPlant.PlantName as SupplyingPlantName,
_PurgDocumentItemCategory._Text[1:Language=$session.system_language].PurgDocItemCategoryName as PurgDocItemCategoryName,
// case
// when _PurchaseOrder.Supplier is null or _PurchaseOrder.Supplier = ''
// then _PurchaseOrder.SupplyingPlant
// else _PurchaseOrder.Supplier
// end as Supplier,
//
// case
// when _PurchaseOrder.Supplier is null or _PurchaseOrder.Supplier = ''
// then _PurchaseOrder._SupplyingPlant.PlantName
// else _PurchaseOrder._Supplier.SupplierName
// end as SupplierName,
_PurchaseOrder.Supplier as Supplier,
_PurchaseOrder._Supplier.SupplierName as SupplierName,
_PurchaseOrder.PurchaseOrderDate as PurchaseOrderDate,
_PurchaseOrder.PurchaseOrderType as PurchasingDocumentType,
_PurchaseOrder.CreationDate as CreationDate,
_PurchaseOrder._PurchaseOrderType._Text[1:Language = $session.system_language].PurchasingDocumentTypeName as PurchasingDocumentTypeName,
_PurchaseOrder._Supplier.IsBusinessPurposeCompleted as IsBusinessPurposeCompleted,
/*** PO Item Calculated Fields ******/
/** Net Amount **/
case when IsReturnsItem = 'X'
then -abs(NetAmount)
else NetAmount
end as NetAmount,
/** Delivered Quantity **/
case when (PurchaseOrderItemCategory = 'A') then cast (0 as abap.quan(13, 3))
when IsReturnsItem = 'X' then -abs(_GoodsReceiptCalc.GoodsReceiptQuantity)
else _GoodsReceiptCalc.GoodsReceiptQuantity
end as GoodsReceiptQuantity,
/** Delivered Value **/
cast( case when (PurchaseOrderItemCategory = 'A' and ProductType = '1')
then 0
when (PurchaseOrderItemCategory = 'A' and ProductType = '2')
then _POItemHistory.GoodsReceiptAmount
//then _GoodsRecieptAmountCalc.GoodsReceiptAmount
when (_GoodsReceiptCalc.GoodsReceiptQuantity is null or OrderQuantity is null or OrderQuantity = 0)
then 0
when IsReturnsItem = 'X'
then -abs(_GoodsReceiptCalc.GoodsReceiptQuantity * division(NetAmount, OrderQuantity, 4))
else _GoodsReceiptCalc.GoodsReceiptQuantity * division(NetAmount, OrderQuantity, 4)
end as abap.curr(21,5)) as GoodsReceiptAmount,
/** Still to Be Delivered Quantity**/
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A') then cast(0 as abap.quan(13, 3))
when IsReturnsItem = 'X'
then
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then -abs(OrderQuantity)
else -abs(OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity)
end
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then OrderQuantity
else OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity
end
end as StillToBeDeliveredQuantity,
/** Still to be Delivered Value **/
cast(case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or (PurchaseOrderItemCategory = 'A' and ProductType = '1')) then 0
when (PurchaseOrderItemCategory = 'A' and ProductType = '2')
then
case when _POItemHistory.GoodsReceiptAmount is null then NetAmount
else NetAmount - _POItemHistory.GoodsReceiptAmount
end
when OrderQuantity is null or OrderQuantity = 0 then 0
when IsReturnsItem = 'X'
then
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then -abs(NetAmount)
else -abs((OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity) * division(NetAmount, OrderQuantity, 4))
end
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity is null then NetAmount
else (OrderQuantity - _GoodsReceiptCalc.GoodsReceiptQuantity) * division(NetAmount, OrderQuantity, 4)
end
end as abap.curr(21,5)) as StillToBeDeliveredValue,
/** Invoiced Amount **/
case when (PurchaseOrderItemCategory = 'A') or _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then cast (0 as abap.quan(13, 3))
else _POItemHistory.InvoiceReceiptQty
end as InvoiceReceiptQty,
/** Invoiced Value **/
case when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then cast ( 0 as abap.curr(21,5))
else
_POItemHistory.InvoiceRcptAmtInCoCodeCrcy end as InvoiceRcptAmtInCoCodeCrcy,
/** Still to Invoice Quantity **/
case when (InvoiceIsExpected <> 'X' or IsFinallyInvoiced = 'X' or PurchaseOrderItemCategory = 'A') then cast(0 as abap.quan(13, 3))
else
case when _GoodsReceiptCalc.GoodsReceiptQuantity > OrderQuantity //^3028096
then
case when IsReturnsItem = 'X'
then cast((-1 * _GoodsReceiptCalc.GoodsReceiptQuantity) - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
else cast(_GoodsReceiptCalc.GoodsReceiptQuantity - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
end
else
case when IsReturnsItem = 'X'
then cast((-1 * OrderQuantity) - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
else cast(OrderQuantity - (coalesce(_POItemHistory.InvoiceReceiptQty, 0)) as abap.quan(13,3))
end
end //v3028096
end as StillToInvoiceQuantity,
/** Still to Invoice Value **/
cast(case when (InvoiceIsExpected <> 'X' or IsFinallyInvoiced = 'X')
then 0
when (_POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null and IsReturnsItem = 'X')
then -abs(_ConvertedNetAmount.NetAmountInCoCodeCurrency)
when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy is null
then _ConvertedNetAmount.NetAmountInCoCodeCurrency
when _POItemHistory.InvoiceRcptAmtInCoCodeCrcy >= _ConvertedNetAmount.NetAmountInCoCodeCurrency
then 0
when PurchaseOrderItemCategory = 'A'
then _ConvertedNetAmount.NetAmountInCoCodeCurrency - _POItemHistory.InvoiceRcptAmtInCoCodeCrcy
when OrderQuantity is null or OrderQuantity = 0
then 0
when IsReturnsItem = 'X'
then -abs((OrderQuantity - abs(_POItemHistory.InvoiceReceiptQty)) * division(_ConvertedNetAmount.NetAmountInCoCodeCurrency, OrderQuantity, 4))
when _POItemHistory.InvoiceReceiptQty >= OrderQuantity
then cast(0 as abap.curr(21,5))
else (OrderQuantity - abs(_POItemHistory.InvoiceReceiptQty)) * division(_ConvertedNetAmount.NetAmountInCoCodeCurrency, OrderQuantity, 4)
end as abap.curr(21,5)) as StillToInvoiceValue,
/**** Next Delivery Date ****/
// case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A') then cast('00000000' as abap.dats)
// else _POItemNextDeliveryCalc.ScheduleLineDeliveryDate
// end as DeliveryDate,
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A') then cast('00000000' as abap.dats)
// when _AnalyticalConfiguration.IsIntrastatReportingRelevant = 'X'
// then _POItemEnhcdNextDeliveryCalc.ScheduleLineDeliveryDate
else _POItemNextDeliveryCalc.ScheduleLineDeliveryDate
end as DeliveryDate,
/*** Next Delivery Quantity ***/
// case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A')
// then cast( 0 as abap.quan(13,3))
// when IsReturnsItem = 'X'
// then -1 * _POItemNextDeliveryCalc.ScheduleLineOpenQuantity
// else
// _POItemNextDeliveryCalc.ScheduleLineOpenQuantity
// end as ScheduleLineOpenQty,
//
case when (GoodsReceiptIsExpected <> 'X' or IsCompletelyDelivered = 'X' or PurchaseOrderItemCategory = 'A')
then cast( 0 as abap.quan(13,3))
/** When PO Item is a 'Return Item' and the Open Quantity is picked from EKET table **/
when (IsReturnsItem = 'X')
then -1 * _POItemNextDeliveryCalc.ScheduleLineOpenQuantity
else
// /** case when PO Item is not a Returns Item and Open Quantity is picked from EKET table **/
_POItemNextDeliveryCalc.ScheduleLineOpenQuantity
end as ScheduleLineOpenQty,
/********* PO Item Calculated Fields ******/
//Required for DCL
_PurchaseOrder.PurchaseOrderType,
_PurchaseOrder,
/*** Association ***/
_ProductType
}
where
PurchasingDocumentDeletionCode <> 'L'
and _PurchaseOrder.PurchasingDocumentDeletionCode <> 'L'
and _PurchaseOrder.PurchasingDocumentIsAged = ' '
// filter out statistical Items (excluding Retail i.e Material = '')
and not( Material = '' and IsStatisticalItem = 'X' )