Overview
Container Management includes the ability to tie Purchase Orders to a container, see what’s in the container, and track the container appropriately keeping the MRP data relative and up to date. This DIY process can also be used for Sales if shipping in containers are required. Estimated time to develop: 3 hrs.
Sample Completed Project
With the B1Usability Package, you can display the container data however you wish. Below is an example we will use for this tutorial.
The Purchase Order can now be tied to a container, and the container record (UDT) can then provide a single source of truth as to what’s on the container, and when is it scheduled to arrive.
Step 1 – Create the UDT and UDF’s required to support this process.
Create the following tables using the standard SAP Business One Tools menu.
CONTAINERH = Container Header table – Create with object type Master Data
CONTAINERR = Container Rows table – Create with object type Master Data Rows
Step 2 – Create the User Defined Fields in our two new tables, and the Marketing Document Header table.
Step 3 – Register the two new tables created with the user defined fields as a data entry form. Tools > Customization Tools > Objects Registration Wizard and add this form to the Purchasing Menu.
Formatted Searches can then be created to easily populate the new Container form, which is thus used for reporting in the Cockpits.
Formatted Searches from the Container Form
Max Volume Cf
SELECT CASE
WHEN $[$18_U_Cb.0.0] = ’40Ft’ THEN 2390
ELSE 1195
END “Max Volume” FROM DUMMY
Purchase Ord No
SELECT T0.”DocNum”, T0.”CardCode”, T0.”CardName”, T0.”DocDate”, T0.”DocDueDate”, T0.”DocTotal” FROM OPOR T0 WHERE T0.”DocStatus” = ‘O’
Supplier
SELECT T0.”CardName” FROM OPOR T0 WHERE T0.”DocNum” = $[$0_U_G.C_0_2.0]
Item
SELECT T1.”ItemCode”, T1.”Dscription” FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry” WHERE T0.”DocNum” = $[$0_U_G.C_0_2.0]
Item Description
SELECT T1.”Dscription” FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry” WHERE T0.”DocNum” = $[$0_U_G.C_0_2.0] AND T1.”ItemCode” = $[$0_U_G.C_0_5.0]
Qty
SELECT T1.”Quantity” FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry” WHERE T0.”DocNum” = $[$0_U_G.C_0_2.0] AND T1.”ItemCode” = $[$0_U_G.C_0_5.0]
Volume Cf
SELECT (T0.”BVolume” * $[$0_U_G.C_0_7.0]) / 100 “Total volume” FROM OITM T0 WHERE T0.”ItemCode” = $[$0_U_G.C_0_5.0]
$ Value
SELECT T1.”LineTotal” FROM OPOR T0 INNER JOIN POR1 T1 ON T0.”DocEntry” = T1.”DocEntry” WHERE T0.”DocNum” = $[$0_U_G.C_0_2.0] AND T1.”ItemCode” = $[$0_U_G.C_0_5.0]
Queries used to Populate the new Container Cockpit
Container Tracking Grid, Container Distribution by Item Qty and Container Distribution by Item Volume (This query populates all of the container analytics in the container cockpit, thus allowing filters to be created and applied to the entire cockpit.
SELECT T0.”Code” “Key”,
T0.”U_Status” “Status”,
T0.”U_ShipDate” “Ship Date”,
T0.”U_EstArrivaldate” “ETA”,
T0.”U_actualdate” “Actual Arrival Date”,
T0.”U_Vessel” “Vessel ID”,
T0.”U_Container” “Container Size”,
T0.”U_MaxVolume” “Total Cft”,
T1.”U_PO” “PO No.”,
T1.”U_SupplierID” “Supplier ID”,
T1.”U_SuuplierName” “Supplier Name”,
T1.”U_Item” “Item”,
T1.”U_ItemDesc” “Item Description”,
T1.”U_Qty” “Qty”,
T1.”U_Volume” “Volume Cft”,
T1.”U_Value” “Value” FROM “SBODEMOUS_SW”.”@CONTAINERH” T0 INNER JOIN “SBODEMOUS_SW”.”@CONTAINERR” T1 ON T0.”Code” = T1.”Code”
*** The Cockpits are built with the B1Usability Package available for SAP Business One from BoyumIT.
