The Prolecto Stock Ledger is a stock report in the Purchasing/Inventory subtab of the Item records. This report is a custom sublist originating from the ‘Prolecto Financial Saved Search Library’ bundle. The sublist’s results are derived from the search results of ‘Prolecto: Item Movement Stock (for sublist)’, also part of the same bundle. It was found that the cumulative columns in the report show inaccuracies in data.
The report is sorted by a formula column named ‘Sort Column’ and the cumulative data are also derived from formula columns in the saved search. However, on investigating the discrepancies in the cumulative quantity and cumulative value columns in the Prolecto Stock ledger, it has been identified that these discrepancies stem from multiple factors:
- Pertaining to a single Work order transaction, there were multiple transactions impacting the quantity column in the results such as work order completion, work order completion [work in process], and work order close transactions.
- For items that appear on multiple lines within the same transaction, the initial formula considered only one transaction line.
- When two transactions of the same type involving the same items were generated on the same day, the search formula accounted for only one of these transactions.
We can make the following modifications to the search to address these issues and ensure data accuracy:
- The consolidated quantity and the Sort column formulas were modified to account for the item internal ID and the transaction line ID along with the Transaction internal ID.
- The work order close records were excluded from the search as it does not impact the item on hand quantity.
- The Work order completion records with the transaction line type ‘Work In Process’ were excluded as these are in progress transactions and only those work order completion records are taken into consideration that impacts the item quantity on hand.
Modified formulas:
Sort Column – TO_CHAR({trandate}, ‘YYYYMMDD’) || /* Sequence below based on Answer Id: 28147. A value of 9 = unmapped/something wrong / DECODE({typecode}, ‘InvWksht’, ‘1’, / Assume first-of-day since until Enhancement 585465 is resolved / ‘VendBill’, ‘2’, ‘ItemRcpt’, DECODE({createdfrom.typecode}, ‘PurchOrd’, ‘2’, ‘TrnfrOrd’, ‘3’, ‘RtnAuth’, ‘6’, ‘9’ ), ‘InvAdjst’, (CASE WHEN {quantity} >= 0 THEN ‘2’ ELSE ‘5’ END), ‘Transfer’, ‘3’, ‘InvTrnfr’, ‘3’, ‘Build’, ‘3’, ‘ItemShip’, DECODE({createdfrom.typecode}, ‘TrnfrOrd’, ‘3’, ‘VendAuth’, ‘4’, ‘SalesOrd’, ‘5’, ‘9’ ), ‘VendCred’, ‘4’, / Not covered in Answer Id: 28147 but confirmed by NS support as the right mapping / ‘Unbuild’, ‘4’, ‘CustInvc’, ‘5’, ‘CashSale’, ‘5’, ‘CashRfnd’, ‘6’, / Not covered in Answer Id: 28147 but confirmed by NS support as the right mapping */ ‘CustCred’, ‘6’, ‘9’)|| TO_CHAR({internalid}, ‘000000009’) || TO_CHAR({item.internalid}, ‘000000009’) || TO_CHAR({line}, ‘000000009’)
Cumulative Quantity – TO_CHAR( SUM /* comment */(NVL({quantity},0)) OVER ( PARTITION BY {item} ORDER BY TO_CHAR({trandate}, ‘YYYYMMDD’) || DECODE({typecode}, ‘InvWksht’, ‘1’, ‘VendBill’, ‘2’, ‘ItemRcpt’, DECODE({createdfrom.typecode}, ‘PurchOrd’, ‘2’, ‘TrnfrOrd’, ‘3’, ‘RtnAuth’, ‘6’, ‘9’ ), ‘InvAdjst’, (CASE WHEN {quantity} >= 0 THEN ‘2’ ELSE ‘5’ END), ‘Transfer’, ‘3’, ‘InvTrnfr’, ‘3’, ‘Build’, ‘3’, ‘ItemShip’, DECODE({createdfrom.typecode}, ‘TrnfrOrd’, ‘3’, ‘VendAuth’, ‘4’, ‘SalesOrd’, ‘5’, ‘9’ ), ‘VendCred’, ‘4’, ‘Unbuild’, ‘4’, ‘CustInvc’, ‘5’, ‘CashSale’, ‘5’, ‘CashRfnd’, ‘6’, ‘CustCred’, ‘6’, ‘9’ ) || TO_CHAR({internalid}, ‘000000009’) || TO_CHAR({item.internalid}, ‘000000009’) || TO_CHAR({line}, ‘000000009’) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), ‘9,999,999,999’)
Cumulative Amount – TO_CHAR( SUM /* comment */(NVL({amount},0)) OVER ( PARTITION BY {item} ORDER BY TO_CHAR({trandate}, ‘YYYYMMDD’) || DECODE({typecode}, ‘InvWksht’, ‘1’, ‘VendBill’, ‘2’, ‘ItemRcpt’, DECODE({createdfrom.typecode}, ‘PurchOrd’, ‘2’, ‘TrnfrOrd’, ‘3’, ‘RtnAuth’, ‘6’, ‘9’ ), ‘InvAdjst’, (CASE WHEN {quantity} >= 0 THEN ‘2’ ELSE ‘5’ END), ‘Transfer’, ‘3’, ‘InvTrnfr’, ‘3’, ‘Build’, ‘3’, ‘ItemShip’, DECODE({createdfrom.typecode}, ‘TrnfrOrd’, ‘3’, ‘VendAuth’, ‘4’, ‘SalesOrd’, ‘5’, ‘9’ ), ‘VendCred’, ‘4’, ‘Unbuild’, ‘4’, ‘CustInvc’, ‘5’, ‘CashSale’, ‘5’, ‘CashRfnd’, ‘6’, ‘CustCred’, ‘6’, ‘9’ ) || TO_CHAR({internalid}, ‘000000009’) || TO_CHAR({item.internalid}, ‘000000009’) || TO_CHAR({line}, ‘000000009’) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ), ‘9,999,999,999.99’)