https://netsuiteprofessionals.com logo
Join Slack
Powered by
Why don't bin putaway worksheets have a correspond...
# suiteql
a
Why don't bin putaway worksheets have a corresponding subtracting transactionline like bin transfers?
c
What does the bin transfer subtracting transactionline subtract from?
a
Negative transactionline quantity from the bin your transferring.
c
Since the bin putaway is putting away items that have no bin into a bin, it makes sense to me that there's no corresponding negative transactionline
There's no bin to remove the quantity from
a
Why not two lines like this: Item Bin quantity 1 | null | -100 1 | B1 | 100
If I have a query as follows:
Copy code
SELECT Sum(inventoryassignment.quantity) from inventoryassignment group by inventoryassignment.bin
This will work for all other transactions but it will fail for bin putaway worksheets.
c
In what way does it fail?
a
Because If I have an Item receipt for 10 and a bin putaway worksheet for 10 then you will have a total of 20 on the transactionlines.
Copy code
| Transaction | Bin   | Quantity |
|-------------|-------|----------|
| IR1         |       | 10       |
| BWP1        | Bin 1 | 10       |
If I have an item receipt for 10 and a bin transfer for 10, NS will have three transactionlines
Copy code
| Transaction | Bin   | Quantity |
|-------------|-------|----------|
| IR1         | Bin 1 | 10       |
| BT1         | Bin 1 | -10      |
| BT1         | Bin 2 | 10       |
Which allows us to sum it to the true quantity of 10.
The full join would look like:
Copy code
FROM
  transaction
  INNER JOIN transactionline ON transactionline.transaction = transaction.id
  INNER JOIN inventoryassignment ON inventoryassignment.transactionline = transactionLine.id
  AND inventoryAssignment.transaction = transactionLine.transaction
c
What's the purpose of this query?
a
To determine inventory as of date.
c
Have you looked at anyone else's queries for this kind of thing, like this one from Tim Dietrich?
a
Yes this query will fail for bin putaway worksheets.
CC: @tdietrich
c
Does your query need to show bins as well as location inventory levels, and that's why you need to incorporate bin putaways?
a
Yes
c
What if you ignore transactionlines that don't have bins?
Or a subset of them at least, like Item Receipts if your business regularly receives them into no-bin then puts away?
a
I don't really know enough about the business to know which IRs i should ignore but i like solution 1 to ignore all transactions without bins
Only downside is i wont be able to track inventory outside of bins but that should be fine.
c
Depending on what your company's processes look like, there's the "Require Bins on All Transactions Except Item Receipts" accounting preference that can be enabled to enforce bins across the board except for IRs.
a
Ah I will look into that
Thanks for all the help
c
You bet!
4 Views
Open in Slack
PreviousNext
Powered by