Try ERPNext Buy Support Partners Foundation

Issue with SQL Query linking purchase order and Purchase receipt

Dear all,

I need to query the purchase order quantity for a specific purchase receipt and the corresponding description.

I have built the following query and encountering issues.

SELECT

tPR.name AS ‘GRN’,
tPO.name AS ‘PO_NUMBER’,
tPO.transaction_date AS ‘ORDER_DATE’,
tPOI.description AS ‘PO_DESCRIPTION’,
tPOI.qty AS ‘QUANTITY_ORDERED’,
tPRI.qty AS ‘QUANTITY_RECEIVED’
FROM
tabPurchase Receipt tPR
,tabPurchase Receipt Item tPRI
,tabPurchase Order tPO
,tabPurchase Order Item tPOI

WHERE

tPR.name = tPRI.parent
and tPO.name = tPRI.purchase_order
and tPO.name = tPOI.parent
AND tPR.name = %(name)s

When adding the table purchase order item table, the lines are duplicating.

Can you please confirm if i have missed any table?

Best regards,
Kartive

Try adding a DISTINCT after the SELECT

@Nahuel_Nso i tried but not working. :frowning:

Then the lines no son exactly equals

Hi,
Have you tried using ‘Group By’ on purchase order?

Doing a join on item_code in table Purchase Request Item and Purchase Order Item, the issue was resolved.
Capture

Best Regards,
Kartive