Fetch Purchase Receipt from Purhcase Order

Omar_Mohammed · July 22, 2023, 12:07pm

Hi,

I Build a report to show The purchase order (Draft and submit) time, and to be a full report I need to fetch the Purchase Receipt name if the Purchase Order has Purchase receipt.

That PO has To Bill status, I need to show the purchase receipt name

NCP · July 23, 2023, 4:18am

Hi @Omar_Mohammed,

Here we created the query report according to your scenario.
Please check it.

Query:

SELECT
    po.name AS "Purchase Order:Link/Purchase Order:200",
    po.transaction_date AS "Purchase Order Date:Date:150",
    po.status AS "Status:Data:150",
    GROUP_CONCAT(DISTINCT pr.name ORDER BY pr.posting_date DESC SEPARATOR ', ') AS "Purchase Receipts:Data:400",
    GROUP_CONCAT(DISTINCT pr.posting_date ORDER BY pr.posting_date DESC SEPARATOR ', ') AS "Purchase Receipt Dates:Data:200"
FROM
    `tabPurchase Order` po
LEFT JOIN
    `tabPurchase Receipt Item` pri ON po.name = pri.purchase_order
LEFT JOIN
    `tabPurchase Receipt` pr ON pri.parent = pr.name
WHERE
    po.docstatus < 2
GROUP BY
    po.name, po.transaction_date, po.status
ORDER BY
    po.name DESC

Output:

Please set your query in your report according to.

I hope this helps.

Thank You!

Omar_Mohammed · July 23, 2023, 6:40am

Thank you so much for your solution is very useful.

I use this query report

SELECT 
    v.docname AS "Purchase Order:Link/Purchase Order:180",
    po.status AS "PO Status:Data:80",
    po.owner AS "Created By:Link/User:170",
    po.creation AS "Created Time::200",
    v.owner AS "Submitted By:Link/User:150", 
    v.creation AS "Submitted Time::180",
    TIMESTAMPDIFF(MINUTE, po.creation, v.creation) AS "Time Difference (Minutes)"
FROM `tabPurchase Order` po
INNER JOIN `tabVersion` v ON po.name = v.docname
WHERE 
    v.ref_doctype = 'Purchase Order'
    AND v.data REGEXP '"status",\\s*"Draft",\\s*"To Receive and Bill"'
    AND v.creation BETWEEN %(from)s AND %(to)s

because the purpose of this report is to show the Dealy time in (minutes) from

draft PO (time)
submit PO (time)
draft PR (time)
submit PR (time)

Thank you again for reply

NCP · July 23, 2023, 12:53pm

Hi @Omar_Mohammed,

Please apply it.

SELECT 
    v.docname AS "Purchase Order:Link/Purchase Order:180",
    po.status AS "PO Status:Data:80",
    po.owner AS "Created By:Link/User:170",
    po.creation AS "Created Time::200",
    v.owner AS "Submitted By:Link/User:150", 
    v.creation AS "Submitted Time::Datetime:180",
    MIN(pr.creation) AS "PR Draft Time:Datetime:180",
    MAX(pr.creation) AS "PR Submitted Time:Datetime:180",
    MIN(CASE WHEN pr.docstatus=0 THEN pr.name ELSE NULL END) AS "Draft PR:Link/Purchase Receipt:200",
    MAX(CASE WHEN pr.docstatus=1 THEN pr.name ELSE NULL END) AS "Submitted PR:Link/Purchase Receipt:200",
    TIMESTAMPDIFF(MINUTE, po.creation, v.creation) AS "PO Processing Time (Minutes)",
    TIMESTAMPDIFF(MINUTE, MIN(pr.creation), MAX(pr.creation)) AS "PR Processing Time (Minutes)"
FROM `tabPurchase Order` po
INNER JOIN `tabVersion` v ON po.name = v.docname
LEFT JOIN `tabPurchase Receipt` pr ON po.name = pr.purchase_order
WHERE 
    v.ref_doctype = 'Purchase Order'
    AND v.data REGEXP '"status",\\s*"Draft",\\s*"To Receive and Bill"'
    AND v.creation BETWEEN %(from)s AND %(to)s
GROUP BY v.docname, po.status, po.owner, po.creation, v.owner, v.creation
ORDER BY v.docname DESC

Thank You!

Omar_Mohammed · July 23, 2023, 8:17pm

Thank you so much.

Phipsoft · July 24, 2023, 11:38am

Dear NCP:

Check this:

Omar_Mohammed · July 26, 2023, 11:00am

dear Nihantra,

I need your support to fetch the right submit time pr, because as below image is the same time, and this time is created time

SELECT 
    v.docname AS "Purchase Order:Link/Purchase Order:180",
    po.status AS "PO Status:Data:80",
    po.owner AS "Created By:Link/User:170",
    po.creation AS "Created Time::200",
    v.owner AS "Submitted By:Link/User:150", 
    v.creation AS "Submitted Time::Datetime:180",
    MIN(pr.creation) AS "PR Draft Time:Datetime:180",
    MAX(pr.creation) AS "PR Submitted Time:Datetime:180",
    pr.owner as "PR created by",
    MIN(CASE WHEN pr.docstatus=0 THEN pr.name ELSE NULL END) AS "Draft PR:Link/Purchase Receipt:200",
    MAX(CASE WHEN pr.docstatus=1 THEN pr.name ELSE NULL END) AS "Submitted PR:Link/Purchase Receipt:200",
    TIMESTAMPDIFF(MINUTE, po.creation, v.creation) AS "PO Processing Time (Minutes)",
    TIMESTAMPDIFF(MINUTE, MIN(pr.creation), MAX(pr.creation)) AS "PR Processing Time (Minutes)"
FROM `tabPurchase Order` po
INNER JOIN `tabVersion` v ON po.name = v.docname
LEFT JOIN
    `tabPurchase Receipt Item` pri ON po.name = pri.purchase_order
LEFT JOIN
    `tabPurchase Receipt` pr ON pri.parent = pr.name
WHERE 
    v.ref_doctype = 'Purchase Order'
    AND v.data REGEXP '"status",\\s*"Draft",\\s*"To Receive and Bill"'
    AND v.creation BETWEEN %(from)s AND %(to)s
    AND po.set_warehouse = %(warehouse)s
GROUP BY v.docname, po.status, po.owner, po.creation, v.owner, v.creation
ORDER BY v.docname DESC

NCP · July 26, 2023, 11:41am

Hmm ,

Try it.

    TIMESTAMPDIFF(MINUTE, (
        SELECT MIN(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    ), (
        SELECT MAX(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    )) AS "PR Processing Time (Minutes)"

Omar_Mohammed · July 26, 2023, 12:09pm

unfortunately , is still same wrong number, I think just one column if we solved it will solve all thing the column is PR submitted Time @NCP

NCP · July 26, 2023, 12:46pm

Again try it @Omar_Mohammed,

SELECT 
    v.docname AS "Purchase Order:Link/Purchase Order:180",
    po.status AS "PO Status:Data:80",
    po.owner AS "Created By:Link/User:170",
    po.creation AS "Created Time::200",
    v.owner AS "Submitted By:Link/User:150", 
    v.creation AS "Submitted Time:Datetime:180",
    (
        SELECT MIN(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    ) AS "PR Draft Time:Datetime:180",
    (
        SELECT MAX(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    ) AS "PR Submitted Time:Datetime:180",
    MIN(CASE WHEN pr.docstatus=0 THEN pr.name ELSE NULL END) AS "Draft PR:Link/Purchase Receipt:200",
    MAX(CASE WHEN pr.docstatus=1 THEN pr.name ELSE NULL END) AS "Submitted PR:Link/Purchase Receipt:200",
    TIMESTAMPDIFF(MINUTE, po.creation, v.creation) AS "PO Processing Time (Minutes)",
    TIMESTAMPDIFF(MINUTE, (
        SELECT MIN(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    ), (
        SELECT MAX(pr.creation)
        FROM `tabPurchase Receipt` pr
        WHERE po.name = pr.purchase_order
        AND pr.docstatus = 1
    )) AS "PR Processing Time (Minutes)"
FROM `tabPurchase Order` po
INNER JOIN `tabVersion` v ON po.name = v.docname
LEFT JOIN `tabPurchase Receipt` pr ON po.name = pr.purchase_order
WHERE 
    v.ref_doctype = 'Purchase Order'
    AND v.data REGEXP '"status",\\s*"Draft",\\s*"To Receive and Bill"'
    AND v.creation BETWEEN %(from)s AND %(to)s
GROUP BY v.docname, po.status, po.owner, po.creation, v.owner, v.creation
ORDER BY v.docname DESC

Thank You!

Omar_Mohammed · July 31, 2023, 11:08am

Sorry @NCP still is show the wrong time for submit, because when use

MAX(pr.creation)

Because I need to be more specific with Submit Time such as I do in Purchase Order I use Version doctype