Hi, Can someone help me to understand what's wrong in this query. This query is running fine SuiteQL Tool however while running it in suitescript, it is returning no records - WITH q AS ( SELECT i.upccode AS item_upc, ABS( SUM(tl.quantity) ) AS in_transit, MAX( CASE WHEN tl.quantity < 0 THEN l.id END ) AS source_location, MAX( CASE WHEN tl.quantity > 0 THEN l.id END ) AS destination_location, t.tranid AS transaction_number FROM Transaction t JOIN TransactionLine tl ON (t.id = tl.transaction) JOIN Item i ON (tl.item = i.id) JOIN Location l ON (tl.location = l.id) JOIN LocationSubsidiaryMap lsm ON (l.id = lsm.location) JOIN Subsidiary s ON (lsm.subsidiary = s.id) WHERE t.recordtype IN ('transferorder', 'invtransfer') AND t.status IN ('TrnfrOrd:F', 'TrnfrOrd:E') AND tl.mainline = 'F' AND s.id = 35 GROUP BY i.upccode, t.tranid ) SELECT q.item_upc, q.source_location, q.destination_location, SUM(q.in_transit) AS qty_in_transit FROM q GROUP BY q.item_upc, q.source_location, q.destination_location ORDER BY q.item_upc, q.source_location, q.destination_location

Check permissions

I'm surprised it would work in the SuiteQL tool, but the record types for those two actually in the table are TrnfrOrd and InvTrnfr. Similarly the statuses that actually populate the tables if you look at them are just 'F' and 'E', no record type prefixes. I've just used straight SQL in an IDE so maybe that's not the issue and SuiteScript is smart enough to deal with that, but I'd give it a shot at least. Or Jen could be right also.