https://netsuiteprofessionals.com logo
Join Slack
Powered by
I'm trying to get some information on transfer ord...
# suiteql
r
I'm trying to get some information on transfer orders that are not fully fulfilled/received and the locations impacted. The query is returning the same location for transaction.location and transactionline.transferlocation, which is unexpected
Copy code
SELECT T.id, T.transferlocation, TL.location, BUILTIN.DF(TL.item) as ITEM, TL.quantity, TL.quantityshiprecv from transaction as T INNER JOIN transactionline as TL ON TL.transaction = T.id WHERE T.type ='TrnfrOrd' AND (TL.quantity - TL.quantityshiprecv) > 0
I confirmed on the IF's and the TO that the transferlocation and location are different. inventorylocation is also the same T.location doesn't work, even though it's in the analytics workbook. Gives a reason, REMOVED - Field is removed
c
Try 
transactionline.inventoryreportinglocation
- when combined with quantity, that should show you where it moves from (negative quantity) and to (positive quantity). You'll need to find a way to remove duplicate lines though, which I'm looking into now.
r
inventoryreportinglocation
gives the same location as the others
c
When I query a 2 line TO, I get 7 lines, including the main line. 4 lines have the "From" location, and 2 have the "To" location. 2 of the "From" have the 
transactionlinetype
value of "ITEM", so those can be discarded.
r
hmm maybe my quantity remain is the restriction
this part
Copy code
(TL.quantity - TL.quantityshiprecv) > 0
c
Yep, that's it - try 
ABS(TL.quantity) - TL.quantityshiprecv > 0
Edited to fix parenthesis placement
quantity is negative for the FROM location
Also, you can filter by 
transferorderitemlineid IS NOT NULL
I'd like to figure out how to filter by 
transactionlinetype
, but even though the records catalog claims it's a string, there's something funky happening under the hood like transaction statuses. Using 
WHERE transactionlinetype != 'ITEM'
returns lines with 
transactionlinetype = 'ITEM'
...
r
same locations
Copy code
SELECT T.id, T.transferlocation, TL.inventoryreportinglocation, TL.location, BUILTIN.DF(TL.item) as ITEM, TL.quantity, TL.quantityshiprecv from transaction as T INNER JOIN transactionline as TL ON TL.transaction = T.id WHERE T.type ='TrnfrOrd' AND (ABS(TL.quantity) - TL.quantityshiprecv) > 0
I get more results, but locations are all the same
c
Odd - can you try 
WHERE ... ABS(TL.quantity) != TL.quantityshiprecv
?
r
Woops, I was wrong, some lines do have the correct locations.
and now I see the "multiple line" problem, which I might be able to sort out
c
transferorderitemlineid IS NOT NULL
should help with that
🙌 1
or: 
WHERE ... BUILTIN.DF(transactionlinetype) IN ('Transfer Order: Shipping', 'Transfer Order: Receiving')
25 Views
Open in Slack
PreviousNext
Powered by