Does anybody know how to join the purchase order l...
# suiteqlp
Pablo Schneiter
03/07/2023, 12:33 PMDoes anybody know how to join the purchase order line with the corresponding item receipt line? I can only find the item to join them but that's not enough if there are several po lines with the same item.
Pablo Schneiter
03/07/2023, 12:39 PMhere's my sql:
select receipt.id as "Receipt ID",
receiptline.item as "Item ID",
ABS(receiptline.foreignamount) as "Amount",
receipt.trandate as "DeliveryDate",
receipt.tranid as "DeliveryNumber",
receiptline.quantity as "Quantity",
receiptline.foreignamount / DECODE(receiptline.quantity,0,1,NVL(receiptline.quantity, 1)) as "UnitPrice",
BUILTIN.CF(poheader.status)
From
Transaction receipt,
transactionLine receiptline,
transaction poheader,
transactionLine poline
Where
receipt.type = 'ItemRcpt'
and receiptline.transaction = receipt.id
and poline.transaction = poheader.id
and receiptline.createdfrom = poheader.id
and receiptline.item = poline.item
and BUILTIN.CF(poheader.status) in ('PurchOrd:B', 'PurchOrd:D', 'PurchOrd:E', 'PurchOrd:F')
order by 1
c
CD
03/07/2023, 12:46 PMIf an item arrives & you haven't already received it in NetSuite, how do you know which PO line it's for?
p
Pablo Schneiter
03/07/2023, 1:07 PMwe have the same item with different "expected delivery dates", when an item arrives we choose the item where the expected delivery date corresponds to the delivery (or closest).
Pablo Schneiter
03/07/2023, 1:07 PMso the relationship between receipt line and po line is there somewhere, but I cannot find it in the data model
s
Selcuk Dogru
03/07/2023, 6:59 PMplease check the PreviousTransactionLineLink table. that should cover your requirement
p
Pablo Schneiter
03/08/2023, 7:27 AMThanks, I’ll try that