This blog will discuss the Stock Transfer Order application.
Here are some snapshots of the custom STO app:
You can see the outbound and inbound delivery and shipments with the associated inbound and associated outbound delivery and shipments.
And the drilldown to the details in an Object page:
Topic 1 – Purchase Order line item to delivery item join issue because of different data types
So we want to show Stock Transfer Orders, Deliveries and Shipments – join all those tables and present that to the user. STOs are in the PO tables EKKO and EKPO. Simple right? But no…. slight issue. You can’t simply joining the STO data to the deliveries because the purchase order line item is 5 digits and the delivery reference document field is 6 characters to the type does not match.
Let’s look at the main join in my CDS view:
@AbapCatalog.sqlViewName: 'ZOTC_STOS'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Stock Transfer Orders'
@VDM.viewType: #CONSUMPTION
@UI.headerInfo.typeName: 'Result'
@UI.headerInfo.typeNamePlural: 'Results'
@OData.publish: true
define view ZOTC_C_STOCK_TRANSFER_ORD
as select distinct from I_PurchasingDocument as purchaseOrderHeader
// This view will create two records per order line item - one for header (order and line item combo) and one for line (for joining with deliveries and shipments)
left outer join ZOTC_I_PURCHASEORDER_LINE_HDR as _purchaseOrderItem on purchaseOrderHeader.PurchasingDocument = _purchaseOrderItem.PurchasingDocument
// This is to get the the aggregated shipped and open quantities at the Header order line item
left outer join ZOTC_I_PO_LINE_SHIPMENT_AGGR as _purchaseOrderItemShipped on _purchaseOrderItem.PurchasingDocument = _purchaseOrderItemShipped.ReferenceSDDocument
and _purchaseOrderItem.PurchasingDocumentItemConvert = _purchaseOrderItemShipped.ReferenceSDDocumentItem
and _purchaseOrderItem.linetype = 'H'
// This is to get the address for the supplier plant
left outer join ZOTC_I_PLANT_ADDRESS as _SupplyingPlantCountry on purchaseOrderHeader.SupplyingPlant = _SupplyingPlantCountry.Plant
// This is to get the address for the delivery plant
left outer join ZOTC_I_PLANT_ADDRESS as _DeliverToPlantCountry on _purchaseOrderItem.Plant = _DeliverToPlantCountry.Plant
// This is to get the deliveries and tie them to th eorder line item with the line type is L for line level
left outer join I_DeliveryDocumentItem as _DeliveryDocumentItem on _purchaseOrderItem.PurchasingDocument = _DeliveryDocumentItem.ReferenceSDDocument
and _purchaseOrderItem.PurchasingDocumentItemConvert = _DeliveryDocumentItem.ReferenceSDDocumentItem
and _purchaseOrderItem.linetype = 'L'
//left outer join I_DeliveryDocument as _DeliveryDocumentHeader on _DeliveryDocumentItem.DeliveryDocument = _DeliveryDocumentHeader.DeliveryDocument
left outer join I_DeliveryDocument as _DeliveryDocumentHeader on _DeliveryDocumentItem.DeliveryDocument = _DeliveryDocumentHeader.DeliveryDocument
left outer join ZOTC_I_SHIPMENTITEM as _shipmentItem on _DeliveryDocumentHeader.DeliveryDocument = _shipmentItem.Delivery
left outer join ZOTC_I_SHIPMENT as _shipment on _shipmentItem.ShipmentNumber = _shipment.ShipmentNumber
// Need to correlate the outbound and inbound deliveries
left outer join I_PurgDocSupplierConfirmation as _SupplierConfirmationOut on _purchaseOrderItem.PurchasingDocument = _SupplierConfirmationOut.PurchasingDocument
and _purchaseOrderItem.PurchasingDocumentItem = _SupplierConfirmationOut.PurchasingDocumentItem
and _DeliveryDocumentItem.DeliveryDocument = _SupplierConfirmationOut.ExternalReferenceDocumentID
and _DeliveryDocumentItem.DeliveryDocumentItem = _SupplierConfirmationOut.DeliveryDocumentItem
and _purchaseOrderItem.linetype = 'L'
left outer join ZOTC_I_SHIPMENTITEM as _AssociatedShipmentItemOut on _SupplierConfirmationOut.DeliveryDocument = _AssociatedShipmentItemOut.Delivery
and _shipment.ShipmentNumber <> ''
and _purchaseOrderItem.linetype = 'L'
left outer join I_PurgDocSupplierConfirmation as _SupplierConfirmationInb on _purchaseOrderItem.PurchasingDocument = _SupplierConfirmationInb.PurchasingDocument
and _purchaseOrderItem.PurchasingDocumentItem = _SupplierConfirmationInb.PurchasingDocumentItem
and _DeliveryDocumentItem.DeliveryDocument = _SupplierConfirmationInb.DeliveryDocument
and _DeliveryDocumentItem.DeliveryDocumentItem = _SupplierConfirmationInb.DeliveryDocumentItem
and _purchaseOrderItem.linetype = 'L'
left outer join ZOTC_I_SHIPMENTITEM as _AssociatedShipmentItemInb on _SupplierConfirmationInb.ExternalReferenceDocumentID = _AssociatedShipmentItemInb.Delivery
and _shipment.ShipmentNumber <> ''
and _purchaseOrderItem.linetype = 'L'Notice how I did the join from the STO in the purchase order item table to the delivery item table:
left outer join I_DeliveryDocumentItem as _DeliveryDocumentItem on _purchaseOrderItem.PurchasingDocument = _DeliveryDocumentItem.ReferenceSDDocument
and _purchaseOrderItem.PurchasingDocumentItemConvert = _DeliveryDocumentItem.ReferenceSDDocumentItem
and _purchaseOrderItem.linetype = 'L'Notice that I had to user the converted PurchasingDocumentItem to the DeliveryDocumentItem ReferenceSDDocumentItem:
_purchaseOrderItem.PurchasingDocumentItemConvert = _DeliveryDocumentItem.ReferenceSDDocumentItemSo in order to have this join to work, I need to convert the PurchasingDocumentItem from 5 digits to 6 digits. I do by appending one leading zero:
concat('0', ( cast(PurchasingDocumentItem as abap.char( 5 )))) as PurchasingDocumentItemConvertTopic 2 – aggregating STO line item delivery and shipment count for outbound and inbound shipments.
We wanted a way to aggregate which delivery items are PGId and which are not…
Note that if ItemGeneralIncompletionStatus, GoodsMovementStatus, ItemGdsMvtIncompletionSts area all ‘C’ then this is PGId.
Also, we make a distinction between Outbound and Inbound shipped quantities. If SDDocumentCategory is 7 then this is for an Inbound Shipment and if it’s J then this is for Outbound shipment):
@AbapCatalog.sqlViewName: 'ZOTC_PODELVDAN'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Purchase Order Line Shipment Aggregation'
define view ZOTC_I_PO_LINE_SHIPMENT_AGGR
as select from ZOTC_I_DELIVERYITEM_ANALYTICS
{
key ReferenceSDDocument,
key ReferenceSDDocumentItem,
key ReferenceSDDocumentCategory,
sum(ActualDeliveryQuantity) as ShippedQuantityForSOLine,
sum(case SDDocumentCategory
when '7' then ActualDeliveryQuantity
end) as InbShippedQuantityForSOLine,
sum(case SDDocumentCategory
when 'J' then ActualDeliveryQuantity
end) as OutShippedQuantityForSOLine,
ItemGeneralIncompletionStatus as SOLIGeneralIncompletionStatus,
ItemDeliveryIncompletionStatus as SOLIDeliveryIncompletionStatus,
GoodsMovementStatus as SOLIGoodsMovementStatus,
ItemGdsMvtIncompletionSts as SOLIGdsMvtIncompletionSts
}
where
(
ReferenceSDDocumentCategory <> 'C' //not C - not sales order
and ItemGeneralIncompletionStatus = 'C'
and GoodsMovementStatus = 'C'
and ItemGdsMvtIncompletionSts = 'C'
)
group by
ReferenceSDDocument,
ReferenceSDDocumentItem,
ReferenceSDDocumentCategory,
//SDProcessStatus,
ItemGeneralIncompletionStatus,
ItemDeliveryIncompletionStatus,
GoodsMovementStatus,
ItemGdsMvtIncompletionStsNote that I have a tweaked version of I_DeliveryItem_Analytics with the I_SalesDocumentItem commented out.
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@EndUserText.label: 'Core Analytics View for Delivery Items'
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck:#CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@AbapCatalog.sqlViewName: 'ZOTCDELDOCITMANA'
define view ZOTC_I_DELIVERYITEM_ANALYTICS
as select from I_DeliveryDocumentItem as DDI
//inner join I_SalesDocumentItem as SDI on DDI.ReferenceSDDocument = SDI.SalesDocument
// and DDI.ReferenceSDDocumentItem = SDI.SalesDocumentItem
association [0..1] to I_Currency as _StatisticsCurrency on $projection.StatisticsCurrency = _StatisticsCurrency.Currency
{
// Key
@ObjectModel.foreignKey.association: '_DeliveryDocument'
key DDI.DeliveryDocument,
key DDI.DeliveryDocumentItem,
//Dimensions
//category
@ObjectModel.foreignKey.association: '_SDDocumentCategory'
DDI.SDDocumentCategory,
@ObjectModel.foreignKey.association: '_ItemCategory'
DeliveryDocumentItemCategory,
@ObjectModel.foreignKey.association: '_SalesDocumentItemType'
DDI.SalesDocumentItemType,
//Admin
DDI.CreatedByUser,
@Semantics.systemDate.createdAt: true
DDI.CreationDate,
DDI.CreationTime,
@Semantics.systemDate.lastChangedAt: true
DDI.LastChangeDate,
//Organization (Team View,..)
@ObjectModel.foreignKey.association: '_SalesOrganization'
_DeliveryDocument.SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DDI.DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
DDI.Division,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
DepartmentClassificationByCust,
//Product
@ObjectModel.foreignKey.association: '_Material'
DDI.Material,
DDI.MaterialByCustomer,
@ObjectModel.foreignKey.association: '_OriginallyRequestedMaterial'
DDI.OriginallyRequestedMaterial,
DDI.InternationalArticleNumber,
DDI.Batch,
DDI.BatchClassification,
DDI.BatchBySupplier,
DDI.MaterialIsIntBatchManaged,
DDI.MaterialIsBatchManaged,
@ObjectModel.foreignKey.association: '_MaterialGroup'
DDI.MaterialGroup,
@ObjectModel.foreignKey.association: '_MaterialFreightGroup'
DDI.MaterialFreightGroup,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup1'
DDI.AdditionalMaterialGroup1,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup2'
DDI.AdditionalMaterialGroup2,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup3'
DDI.AdditionalMaterialGroup3,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup4'
DDI.AdditionalMaterialGroup4,
@ObjectModel.foreignKey.association: '_AdditionalMaterialGroup5'
DDI.AdditionalMaterialGroup5,
@ObjectModel.foreignKey.association: '_Plant'
DDI.Plant,
DDI._Plant.PlantName,
@ObjectModel.foreignKey.association: '_StorageLocation'
DDI.StorageLocation,
DDI._StorageLocation.StorageLocationName,
@ObjectModel.foreignKey.association: '_Warehouse'
DDI.Warehouse,
StorageBin,
StorageType,
@ObjectModel.foreignKey.association: '_InventorySpecialStockType'
DDI.InventorySpecialStockType,
ShelfLifeExpirationDate,
NumberOfSerialNumbers,
DDI.ProductConfiguration,
DDI.ProductHierarchyNode,
ManufactureDate,
//delivery
DeliveryDocumentItemText,
DDI.HigherLevelItem,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'DeliveryQuantityUnit'
ActualDeliveryQuantity,
DDI.QuantityIsFixed,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'DeliveryQuantityUnit'
OriginalDeliveryQuantity,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_DeliveryQuantityUnit'
DeliveryQuantityUnit,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'BaseUnit'
ActualDeliveredQtyInBaseUnit,
// @Semantics.unitOfMeasure: true //See Below in //Measures
// @ObjectModel.foreignKey.association: '_BaseUnit'
// DDI.BaseUnit,
DeliveryToBaseQuantityDnmntr,
DeliveryToBaseQuantityNmrtr,
ProductAvailabilityDate,
ProductAvailabilityTime,
DDI.DeliveryGroup,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
DDI.ItemGrossWeight,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
DDI.ItemNetWeight,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_ItemWeightUnit'
DDI.ItemWeightUnit,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'ItemVolumeUnit'
DDI.ItemVolume,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_ItemVolumeUnit'
DDI.ItemVolumeUnit,
InspectionLot,
InspectionPartialLot,
DDI.PartialDeliveryIsAllowed,
DDI.UnlimitedOverdeliveryIsAllowed,
DDI.OverdelivTolrtdLmtRatioInPct,
DDI.UnderdelivTolrtdLmtRatioInPct,
WarehouseStorageBin,
@ObjectModel.foreignKey.association: '_BOMExplosion'
BOMExplosion,
CustEngineeringChgStatus,
@ObjectModel.foreignKey.association: '_WarehouseStagingArea'
WarehouseStagingArea,
WarehouseStockCategory,
IssgOrRcvgStockCategory,
IssgOrRcvgMaterial,
IssuingOrReceivingPlant,
IssuingOrReceivingStorageLoc,
IssgOrRcvgBatch,
IssgOrRcvgValuationType,
IssgOrRcvgSpclStockInd,
PrimaryPostingSwitch,
StockType,
IsNotGoodsMovementsRelevant,
GLAccount,
GoodsMovementReasonCode,
SubsequentMovementType,
IsCompletelyDelivered,
AlternateProductNumber,
//pick pack load
@ObjectModel.foreignKey.association: '_PickingControl'
PickingControl,
@ObjectModel.foreignKey.association: '_LoadingGroup'
LoadingGroup,
@ObjectModel.foreignKey.association: '_GoodsMovementType'
GoodsMovementType,
//shipping
@ObjectModel.foreignKey.association: '_TransportationGroup'
TransportationGroup,
ReceivingPoint,
FixedShipgProcgDurationInDays,
VarblShipgProcgDurationInDays,
ProofOfDeliveryRelevanceCode,
//billing
@ObjectModel.foreignKey.association: '_ItemIsBillingRelevant'
DDI.ItemIsBillingRelevant,
@ObjectModel.foreignKey.association: '_ItemBillingBlockReason'
DDI.ItemBillingBlockReason,
@ObjectModel.foreignKey.association: '_PaymentGuaranteeForm'
PaymentGuaranteeForm,
IntercompanyBillingStatus,
//accounting
@ObjectModel.foreignKey.association: '_BusinessArea'
DDI.BusinessArea,
@ObjectModel.foreignKey.association: '_ControllingArea'
ControllingArea,
DDI.ProfitabilitySegment,
DDI.ProfitCenter,
--@ObjectModel.foreignKey.association: '_InventoryValuationType' -- AT 24.8.16 commented due to false positive ATC check POC_ANNOTA, OMFK
InventoryValuationType,
IsSeparateValuation,
ConsumptionPosting,
@ObjectModel.foreignKey.association: '_OrderID'
DDI.OrderID,
OrderItem,
CostCenter,
//reference
@ObjectModel.foreignKey.association: '_ReferenceSDDocument'
DDI.ReferenceSDDocument,
@ObjectModel.foreignKey.association: '_ReferenceSalesDocumentItem'
DDI.ReferenceSDDocumentItem,
@ObjectModel.foreignKey.association: '_ReferenceSDDocumentCategory'
DDI.ReferenceSDDocumentCategory,
@ObjectModel.foreignKey.association: '_LogicalSystem'
ReferenceDocumentLogicalSystem,
//sales
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup1'
AdditionalCustomerGroup1,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup2'
AdditionalCustomerGroup2,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup3'
AdditionalCustomerGroup3,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup4'
AdditionalCustomerGroup4,
@ObjectModel.foreignKey.association: '_AdditionalCustomerGroup5'
AdditionalCustomerGroup5,
DDI.RetailPromotion,
StatisticsDate,
//status
@ObjectModel.foreignKey.association: '_SDProcessStatus'
DDI.SDProcessStatus,
@ObjectModel.foreignKey.association: '_PickingConfirmationStatus'
PickingConfirmationStatus,
@ObjectModel.foreignKey.association: '_PickingStatus'
PickingStatus,
@ObjectModel.foreignKey.association: '_WarehouseActivityStatus'
WarehouseActivityStatus,
@ObjectModel.foreignKey.association: '_PackingStatus'
PackingStatus,
@ObjectModel.foreignKey.association: '_GoodsMovementStatus'
GoodsMovementStatus,
@ObjectModel.foreignKey.association: '_DeliveryRelatedBillingStatus'
DeliveryRelatedBillingStatus,
@ObjectModel.foreignKey.association: '_ProofOfDeliveryStatus'
ProofOfDeliveryStatus,
@ObjectModel.foreignKey.association: '_ItemGeneralIncompletionStatus'
DDI.ItemGeneralIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemDelivIncompletionSts'
DDI.ItemDeliveryIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemPickingIncompletionStatus'
ItemPickingIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemGdsMvtIncompletionSts'
ItemGdsMvtIncompletionSts,
@ObjectModel.foreignKey.association: '_ItemPackingIncompletionStatus'
ItemPackingIncompletionStatus,
@ObjectModel.foreignKey.association: '_ItemBillingIncompletionStatus'
DDI.ItemBillingIncompletionStatus,
//Customer
@ObjectModel.foreignKey.association: '_SoldToParty'
_DeliveryDocument.SoldToParty,
cast(_DeliveryDocument._SoldToParty.CustomerName as vdm_sold_to_name) as SoldToPartyName,
@ObjectModel.foreignKey.association: '_CustomerGroup'
_DeliveryDocument.CustomerGroup,
_DeliveryDocument._SoldToParty.CustomerClassification as SoldToPartyClassification,
//Geographics
@ObjectModel.foreignKey.association: '_SalesDistrict'
_DeliveryDocument.SalesDistrict,
// Misc Attributes (for DCL, I_CalenderDate-join)
@ObjectModel.foreignKey.association: '_ShippingPoint'
_DeliveryDocument.ShippingPoint, -- DCL Check for DeliveryDocument
_DeliveryDocument.BillingDocumentDate, -- For Join with I_CalendarDate and CurrencyConversion
//Measures
// @DefaultAggregation: #SUM
// @Semantics.amount.currencyCode: 'TransactionCurrency'
// case when SDI.IsReturnsItem = '' and
// ( DDI.DeliveryRelatedBillingStatus = 'A' or
// DDI.DeliveryRelatedBillingStatus = 'B'
// )
//
// then
// case when SDI.RequestedQuantityInBaseUnit > 0
// then
// cast(division(abs(SDI.NetAmount) * DDI.ActualDeliveredQtyInBaseUnit, SDI.RequestedQuantityInBaseUnit,3 ) as opn_dlv_for_inv_net_amt)
// else cast(0 as opn_dlv_for_inv_net_amt )
// end
// end
// as OpnOutbDelivsForInvcNetAmt,
// @Semantics.currencyCode: true
// @ObjectModel.foreignKey.association: '_TransactionCurrency'
// SDI.TransactionCurrency,
@Semantics.currencyCode: true
@ObjectModel.foreignKey.association: '_StatisticsCurrency'
_DeliveryDocument._SalesOrganization.SalesOrganizationCurrency as StatisticsCurrency, --Note: take statistical currency from central tvko (versus document)
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'BaseUnit'
case when
( DDI.DeliveryRelatedBillingStatus = 'A' or
DDI.DeliveryRelatedBillingStatus = 'B'
)
then
cast( ActualDeliveredQtyInBaseUnit as opn_dlv_for_inv_qty)
end
as OpnOutbDelivsForInvcQty,
@Semantics.unitOfMeasure: true
@ObjectModel.foreignKey.association: '_BaseUnit'
DDI.BaseUnit,
//Associations
@ObjectModel.association.type: [#TO_COMPOSITION_PARENT, #TO_COMPOSITION_ROOT]
DDI._DeliveryDocument,
DDI._Partner,
DDi._SDDocumentCategory,
DDI._ItemCategory,
DDI._SalesDocumentItemType,
DDI._CreatedByUser,
DDI._DistributionChannel,
DDI._Division,
DDI._SalesGroup,
DDI._SalesOffice,
DDI._Material,
DDI._OriginallyRequestedMaterial,
DDI._ProductHierarchyNode,
DDI._MaterialGroup,
DDI._MaterialFreightGroup,
DDI._AdditionalMaterialGroup1,
DDI._AdditionalMaterialGroup2,
DDI._AdditionalMaterialGroup3,
DDI._AdditionalMaterialGroup4,
DDI._AdditionalMaterialGroup5,
DDI._Plant,
DDI._Warehouse,
DDI._StorageLocation,
DDI._InventorySpecialStockType,
DDI._DeliveryQuantityUnit,
DDI._BaseUnit,
DDI._ItemWeightUnit,
DDI._ItemVolumeUnit,
DDI._BOMExplosion,
DDI._WarehouseStagingArea,
DDI._PickingControl,
DDI._LoadingGroup,
DDI._GoodsMovementType,
DDI._TransportationGroup,
DDI._ItemIsBillingRelevant,
DDI._ItemBillingBlockReason,
DDI._PaymentGuaranteeForm,
DDI._BusinessArea,
DDI._ControllingArea,
DDI._ProfitCenter,
DDI._InventoryValuationType,
DDI._OrderID,
DDI._CostCenter,
DDI._ReferenceSDDocument,
DDI._ReferenceSalesDocumentItem,
DDI._ReferenceSDDocumentCategory,
DDI._LogicalSystem,
DDI._AdditionalCustomerGroup1,
DDI._AdditionalCustomerGroup2,
DDI._AdditionalCustomerGroup3,
DDI._AdditionalCustomerGroup4,
DDI._AdditionalCustomerGroup5,
DDI._SDProcessStatus,
DDI._PickingConfirmationStatus,
DDI._PickingStatus,
DDI._WarehouseActivityStatus,
DDI._PackingStatus,
DDI._GoodsMovementStatus,
DDI._DeliveryRelatedBillingStatus,
DDI._ProofOfDeliveryStatus,
DDI._ItemGeneralIncompletionStatus,
DDI._ItemDelivIncompletionSts,
DDI._ItemPickingIncompletionStatus,
DDI._ItemGdsMvtIncompletionSts,
DDI._ItemPackingIncompletionStatus,
DDI._ItemBillingIncompletionStatus,
// some further associations (historical reasons, don't use them !)
DDI._DeliveryDocument._SalesOrganization,
DDI._DeliveryDocument._CustomerGroup,
DDI._DeliveryDocument._SalesDistrict,
DDI._DeliveryDocument._ShippingPoint,
DDI._DeliveryDocument._SoldToParty,
DDI._DeliveryDocument._SoldToParty._CustomerClassification
//_StatisticsCurrency,
//SDI._TransactionCurrency
}
//where SDI.StatisticalValueControl = ''Topic 3 – User status for the line item. This is to capture a simple status for the line:
check out the logic for determining PGId Delivery Shipment, Zero picked shipment, Shipment not PGId yet, Rejected, and Open:
@Consumption.valueHelp: '_UserQueryStatus'
@ObjectModel.text.element: ['User Query']
@UI.fieldGroup: [ { qualifier: 'OrderHeader', position: 80 } ]
@EndUserText.label: 'User Query'
case
when _purchaseOrderItem.linetype = 'L' and
_shipmentItem.ShipmentNumber <> '' and
_DeliveryDocumentItem.GoodsMovementStatus = 'C' and
_DeliveryDocumentItem.ItemGeneralIncompletionStatus = 'C' and
_DeliveryDocumentItem.ItemDeliveryIncompletionStatus = 'C' and
ActualDeliveryQuantity > 0 then 'P' //PGId Delivery shipment
when _purchaseOrderItem.linetype = 'L' and
_shipmentItem.ShipmentNumber <> '' and
_DeliveryDocumentItem.GoodsMovementStatus = 'C' and
_DeliveryDocumentItem.ItemGeneralIncompletionStatus = 'C' and
_DeliveryDocumentItem.ItemDeliveryIncompletionStatus = 'C' and
ActualDeliveryQuantity = 0 then 'Z' //Zero picked
when _purchaseOrderItem.linetype = 'L' and
_shipmentItem.ShipmentNumber <> '' then 'S' //Shipment not PGId yet
when _purchaseOrderItem.linetype = 'H' and
purchaseOrderHeader.PurchasingProcessingStatus = '08' then 'R' //Rejected sales order line item
when _purchaseOrderItem.linetype = 'H' and
(_purchaseOrderItem.OrderQuantity = _purchaseOrderItemShipped.OutShippedQuantityForSOLine) and
(_purchaseOrderItem.OrderQuantity = _purchaseOrderItemShipped.InbShippedQuantityForSOLine) then 'C' //Completed - Shipped quantity = order quantity
else 'O' //Let's treat all these others as open
end as UserQueryStatus,Topic 4 – Outbound and Inbound open quantity logic:
@UI.fieldGroup: [ { qualifier: 'OrderItem', position: 10 } ]
@EndUserText.label: 'ORLI Outbound Open Quantity'
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
case
when _purchaseOrderItem.linetype = 'H' then
case
when (_purchaseOrderItemShipped.OutShippedQuantityForSOLine > 0)
then (_purchaseOrderItem.OrderQuantity - _purchaseOrderItemShipped.OutShippedQuantityForSOLine)
else
_purchaseOrderItem.OrderQuantity
end
when _purchaseOrderItem.linetype = 'L' then
case
when (_purchaseOrderItemShipped.OutShippedQuantityForSOLine > 0)
then (_purchaseOrderItem.OrderQuantity - _purchaseOrderItemShipped.OutShippedQuantityForSOLine)
else
_purchaseOrderItem.OrderQuantity
end
end as SOLIOutboundOpenQuantity,
@UI.fieldGroup: [ { qualifier: 'OrderItem', position: 10 } ]
@EndUserText.label: 'ORLI Inbound Shipped Quantity'
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
_purchaseOrderItemShipped.InbShippedQuantityForSOLine,
@UI.fieldGroup: [ { qualifier: 'OrderItem', position: 10 } ]
@EndUserText.label: 'ORLI Inbound Open Quantity'
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
case
when _purchaseOrderItem.linetype = 'H' then
case
when (_purchaseOrderItemShipped.InbShippedQuantityForSOLine > 0)
then (_purchaseOrderItem.OrderQuantity - _purchaseOrderItemShipped.InbShippedQuantityForSOLine)
else
_purchaseOrderItem.OrderQuantity
end
when _purchaseOrderItem.linetype = 'L' then
case
when (_purchaseOrderItemShipped.InbShippedQuantityForSOLine > 0)
then (_purchaseOrderItem.OrderQuantity - _purchaseOrderItemShipped.InbShippedQuantityForSOLine)
else
_purchaseOrderItem.OrderQuantity
end
end as SOLIInboundOpenQuantity,Topic 5: Logic to get Associated Inbound delivery and Associated Inbound Shipment for an Outbound delivery and likewise an Associated Outbound delivery and Associated Outbound Shipment for Inbound delivery
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 20 } ]
@EndUserText.label: 'DLV InboundOutboundFlag'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when '7' then 'INB'
when 'T' then 'INB'
when 'J' then 'OTB'
else '' // instead of 'OTB' //everything else is OTB
end
as DeliveryInboundOutboundFlag,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 30 } ]
@EndUserText.label: 'DLV Outbound Delivery'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when 'J' then _DeliveryDocumentHeader.DeliveryDocument
else '' // instead of 'OTB' //everything else is OTB
end
as OutboundDelivery,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 35 } ]
@EndUserText.label: 'DLV Associated Inbound Delivery'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when 'J' then _SupplierConfirmationOut.DeliveryDocument
else '' // instead of 'OTB' //everything else is OTB
end
as AssociatedInboundDelivery,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 37 } ]
@EndUserText.label: 'DLV Associated Inbound Shipment'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when 'J' then _AssociatedShipmentItemOut.ShipmentNumber
else '' // instead of 'OTB' //everything else is OTB
end
as AssociatedInboundShipment,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 40 } ]
@EndUserText.label: 'SHP Outbound Shipment'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when 'J' then _shipmentItem.ShipmentNumber
else '' // instead of 'OTB' //everything else is OTB
end
as OutboundShipment,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 50 } ]
@EndUserText.label: 'DLV Inbound Delivery'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when '7' then _DeliveryDocumentHeader.DeliveryDocument
when 'T' then _DeliveryDocumentHeader.DeliveryDocument
else '' // instead of 'OTB' //everything else is OTB
end
as InboundDelivery,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 60 } ]
@EndUserText.label: 'DLV Inbound Shipment'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when '7' then _shipmentItem.ShipmentNumber
when 'T' then _shipmentItem.ShipmentNumber
else '' // instead of 'OTB' //everything else is OTB
end
as InboundShipment,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 50 } ]
@EndUserText.label: 'DLV Associated Outbound Delivery'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when '7' then _SupplierConfirmationInb.ExternalReferenceDocumentID
when 'T' then _SupplierConfirmationInb.ExternalReferenceDocumentID
else '' // instead of 'OTB' //everything else is OTB
end
as AssociatedOutboundDelivery,
@UI.fieldGroup: [ { qualifier: 'DeliveryDocument', position: 60 } ]
@EndUserText.label: 'DLV Associated Outbound Shipment'
case (_DeliveryDocumentHeader.SDDocumentCategory)
when '7' then _AssociatedShipmentItemInb.ShipmentNumber
when 'T' then _AssociatedShipmentItemInb.ShipmentNumber
else '' // instead of 'OTB' //everything else is OTB
end
as AssociatedOutboundShipment,Those are some of the topics that I had to work out while developing this application. The main thing was to capture the outbound and inbound deliveries and shipments and the associated deliveries and shipments to correlate the outbound and inbound information. Plus, we want to show the order quantity, open quantity, and shipped quantity to see the breakdown of the numbers.
Rating: 0 / 5 (0 votes)
