Problem to be solved:
This article will be interesting for three reasons:
- If you are looking for a way to Select locked stock from a Sap Business One screen
- If you are looking for a way to reserve stock even before it is received on the warehouse
- If you want to use B1Up buttons for capturing WMS data, this is a nice example.
Products in Use: B1Up, WMS, SBO
Business Scenario: the customer wants to select in advance the stock for customers before it is even receipt and then sends this selected stock to a consignment warehouse.
How the solution works:
1. Lock the stock in advance for a customer. There are two possible ways of doing it: If there is an open Sales Order with any quantity open of Picklist Proposals 6.2.5. Inventory locking in advance [] (produmex.name)
- Previous case is also possible through the inventory report manual lockings with the extra advantage that lockings for a customer before any sales order is created are also possible 6.1. Inventory report [] (produmex.name)
In both occasions the result is a locking on the stock, in this case a lock on my B007 Item for the customer C001 in warehouse 1
2. Add a new button “Select Locked Stock” to the Delivery document with B1Up this button will load the locked quantity. The behavior of the screen is similar to the Select Stock function (WMS)
The Universal Function must be an SQL Report.
SELECT
PMX_INLD.ItemCode,
PMX_INLD.QualityStatusCode,
PMX_INLD.Quantity / UGP1.BaseQty AS Quantity,
OUOM.UomCode,
PMX_INVT.StorLocCode,
PMX_INLD.ItemTransactionalInfoKey,
PMX_ITRI.BatchNumber,
FORMAT(PMX_ITRI.BestBeforeDate,
'yyyyMMdd') AS BBD
FROM
PMX_INLD
LEFT JOIN PMX_INVT ON PMX_INVT.ItemCode = PMX_INLD.ItemCode
AND PMX_INVT.ItemTransactionalInfoKey = PMX_INLD.ItemTransactionalInfoKey
LEFT JOIN PMX_OSEL ON PMX_OSEL.PmxWhsCode = PMX_INLD.PmxWhsCode
AND PMX_OSEL.Code = PMX_INVT.StorLocCode
LEFT JOIN PMX_ITRI ON PMX_INVT.ItemTransactionalInfoKey = PMX_ITRI.InternalKey
LEFT JOIN OITM ON PMX_INVT.ItemCode = OITM.ItemCode
LEFT JOIN UGP1 ON OITM.UgpEntry = UGP1.UgpEntry
LEFT JOIN OUOM ON UGP1.UomEntry = OUOM.UomEntry
WHERE
PMX_INLD.ItemCode = $[$38.1.0.SELECTED]
AND PMX_INLD.CardCode = $[ODLN.CardCode]
AND PMX_OSEL.PmxWhsCode = $[$38.24.0.SELECTED]
AND OUOM.UomCode = $[$38.1470002145.0.SELECTED]
Bellow a view of the SQL Query of the B1Up Universal Function. The quantity column of the SQL report resulting on clicking the button must be editable:
Bellow the final view of the result of this step after clicking the “Select Locked Stock” button:
3. Once a row is selected on our B1Up “locked stock” window there is still the need to fill all WMS specific data back on SBO delivery document row, such as Quality Status, storage location, SSCC…
To do this add a new button “Add selected Stock” to the form that is opened by the SQL Report
The Universal function must be a Macro.
This macro sets the content of the WMS fields on the SAP document.
@STORE1 = 0; -- Number of selected input data
@STORE2 = 0; -- sum qty
@STORE3 = ''; -- Quality Status
@STORE4 = ''; -- Location
@STORE5 = ''; -- BAtch Number
@STORE6 = ''; -- Quantity
@STORE7 = 0; -- number of batches
@STORE8 = ''; -- empty data
@STORE9 = ''; -- BBD
WHILE (@STORE1 <= ($[LINE_COUNT(BOY_1)] -1)) -- while loop on the list of the stock
BEGIN
IF ( $[IS_ROW_SELECTED(BOY_1|@STORE1)] = 'Y' ) BEGIN
IF ($[$BOY_1.Quantity.0.@STORE1] > 0 ) BEGIN
@STORE7 = @STORE7 + 1;
@STORE2 = @STORE2 + $[$BOY_1.Quantity.0.@STORE1]
IF (@STORE6 = '') BEGIN
@STORE6 = @STORE6 + $[$BOY_1.Quantity.0.@STORE1]
END ELSE BEGIN
@STORE6 = @STORE6 + '|' + $[$BOY_1.Quantity.0.@STORE1]
@STORE8 = @STORE8 + '|'
END
IF (@STORE3 = '') BEGIN
@STORE3 = @STORE3 + $[$BOY_1.QualityStatusCode.0.@STORE1]
END ELSE BEGIN
@STORE3 = @STORE3 + '|' + $[$BOY_1.QualityStatusCode.0.@STORE1]
END
IF (@STORE4 = '') BEGIN
@STORE4 = @STORE4 + $[$BOY_1.StorLocCode.0.@STORE1]
END ELSE BEGIN
@STORE4 = @STORE4 + '|' + $[$BOY_1.StorLocCode.0.@STORE1]
END
IF (@STORE5 = '') BEGIN
@STORE5 = @STORE5 + $[$BOY_1.BatchNumber.0.@STORE1]
END ELSE BEGIN
@STORE5 = @STORE5 + '|' + $[$BOY_1.BatchNumber.0.@STORE1]
END
IF (@STORE9 = '') BEGIN
@STORE9 = @STORE9 + $[$BOY_1.BBD.0.@STORE1]
END ELSE BEGIN
@STORE9 = @STORE9 + '|' + $[$BOY_1.BBD.0.@STORE1]
END
END
END
@STORE1 = @STORE1 + 1;
END
Close();
Activate();
SET($[$38.U_PMX_LOCO.0.SELECTED]|@STORE4);
SET($[$38.U_PMX_BATC.0.SELECTED]|@STORE5);
SET($[$38.U_PMX_QYSC.0.SELECTED]|@STORE3);
SET($[$38.U_PMX_QUAN.0.SELECTED]|@STORE6);
SET($[$38.U_PMX_BAT2.0.SELECTED]|@STORE8);
SET($[$38.U_PMX_BBDT.0.SELECTED]|@STORE9);
SET($[$38.U_PMX_LUID.0.SELECTED]|@STORE8);
SET($[$38.U_PMX_SSCC.0.SELECTED]|@STORE8);
SET($[$38.U_PMX_PQY2.0.SELECTED]|@STORE8);
SET($[$38.11.NUMBER.SELECTED]|@STORE2);
IF (@STORE7 > 1)BEGIN
MessageBox(Set the batch allocation in SAP manually for batch numbers: @STORE5, quantites: @STORE6 );
ActivateRightClick(5896);
END
ELSE
BEGIN
ActivateRightClick(5896);
Activate();
SET($[$14.0.0]|@STORE5);
SET($[$4.234000059.0]|@STORE2)
Click($[$48.0.0]);
Click($[$1.0.0]);
Click($[$1.0.0]);
END
@STORE1 = 0;
@STORE2 = 0;
@STORE3 = '';
@STORE4 = '';
@STORE5 = '';
@STORE6 = '';
@STORE7 = 0;
@STORE8 = '';
@STORE9 = '';
The batch allocation will be set in SAP in case only one batch was selected. If multiple batches are selected, then the user receives a message, and the allocation must be set manually.
4. Add the following logic to SBO_SP_TransactionNotification. This will calculate the consumed quantity and update the item locked stock amounts.
----************************************************************************************************************************
----WMS Delivery validation: Calculate Locked Quantity
----************************************************************************************************************************
IF (@Object_Type = '15' AND @transaction_type In ('A') AND @error = 0)
BEGIN
DECLARE @LCK_KEY int
DECLARE @DEL_Qty decimal(15,6)
DECLARE @LCK_Qty decimal(15,6)
DECLARE DeliveryLockedItems CURSOR FOR
SELECT
LCK.LckKey,
MAX(ITL1.Quantity * -1) as Quantity,
MAX(CASE WHEN LCK.BatchNumber = OBTN.DistNumber AND OITL.CardCode = LCK.CardCode THEN LCK.Quantity ELSE 0 END) AS LockedForThisCustomer
FROM
ITL1
left join OITL on ITL1.LogEntry = OITL.LogEntry
left join OBTQ on OBTQ.ItemCode = ITL1.ItemCode AND OBTQ.SysNumber = ITL1.SysNumber
inner join OBTN on OBTQ.ItemCode = OBTN.ItemCode AND OBTN.AbsEntry = OBTQ.MdAbsEntry
left join OITM on ITL1.ItemCode = OITM.ItemCode
left join "@PMX_UBIG" on OITM.ItmsGrpCod = "@PMX_UBIG".U_ItmGrp
left join (
select PMX_INLD.InternalKey as LckKey, PMX_INLD.ItemCode , PMX_INLD.Quantity, PMX_ITRI.BatchNumber, PMX_INLD.CardCode
from PMX_INLD left join PMX_ITRI on PMX_ITRI.InternalKey = PMX_INLD.ItemTransactionalInfoKey
) as LCK on LCK.ItemCode = ITL1.ItemCode
WHERE
DocType = @Object_Type AND
OITL.DocEntry = @list_of_cols_val_tab_del AND
(LCK.BatchNumber = OBTN.DistNumber)
GROUP BY
LCK.LckKey, ITL1.LogEntry, ITL1.ItemCode, OBTN.DistNumber
OPEN DeliveryLockedItems
FETCH NEXT FROM DeliveryLockedItems INTO @LCK_KEY, @DEL_Qty, @LCK_Qty
WHILE @@FETCH_STATUS = 0
BEGIN
IF @LCK_Qty > @DEL_Qty BEGIN
UPDATE PMX_INLD SET Quantity = Quantity - @DEL_Qty where InternalKey = @LCK_KEY
END
ELSE BEGIN
IF @LCK_Qty = @DEL_Qty BEGIN
DELETE FROM PMX_INLD where InternalKey = @LCK_KEY
END
ELSE
BEGIN
SET @error = '90000'
SET @error_message = 'Delivered quantity is greater than the locked quantity.'
END
END
FETCH NEXT FROM DeliveryLockedItems INTO @LCK_KEY, @DEL_Qty, @LCK_Qty
END
CLOSE DeliveryLockedItems;
DEALLOCATE DeliveryLockedItems;
END
5. The final result after all the data is pushed to the SBO Sales Delivery and the “Add” button is hit that the locked stock selected on our B1Up screen is moved to the consignment warehouse by the delivery document.
Comments
0 comments
Please sign in to leave a comment.