Delivery Note vs Sales invoice

ERPNext Stock/Inventory
, ,
1

I want to see the report in erp next where the delivery note is submited and sales invoice is not created

for example if the delivery not has two items

itemA - 100
ItemB - 200

but sales invoice is not created then report should show me the acutal quantity + delivery note item quantity for example actual is 23 so it should show 100+23

because delivery note minus the inventory then how to see this.

2

Hi @Sunil_Kumar1,

We provided the basic query report so please check it and add your logic according to the scenario.

SELECT
    dn.name AS 'Delivery Note:Link/Delivery Note:130',
    dn.customer AS 'Customer:Link/Customer:130',
    dni.item_code AS 'Item Code:Link/Item:120',
    dni.item_name AS 'Item Name:Data:150',
    dni.qty AS 'Delivered Qty:Float:130',
    dni.actual_qty AS 'Actual Qty in Warehouse:Float:150'
FROM
    `tabDelivery Note` dn
INNER JOIN
    `tabDelivery Note Item` dni ON dn.name = dni.parent
WHERE
    dn.docstatus = 1
    AND NOT EXISTS (
        SELECT
            name
        FROM
            `tabSales Invoice Item`
        WHERE
            delivery_note = dn.name
    )
ORDER BY
    dn.name, dni.item_code

Output:

image
image1920×892 44.1 KB

I hope this helps.

Thank You!

3

HI @NCP ,
Thanks for helping this is perfect but this is only showing the delivery note which are billed 0%

I want to compare the the items qty also so I can have a idea that which item is pending in which delivery note.

I am very new to these queries but tried a lot but did not work.
appreciate your help

4

Hi @Sunil_Kumar1,

You can check the post for learning.

Thank You!

5

Hi, @NCP

can you please guide me on this query i have made but this is only loading what can be done with this.

SELECT
    dn.name AS 'Delivery Note:Link/Delivery Note:130',
    dn.customer AS 'Customer:Link/Customer:130',
    dn.posting_date AS 'Date',
    dni.item_code AS 'Item Code:Link/Item:120',
    dni.item_name AS 'Item Name:Data:150',
    dni.stock_qty AS 'Delivered Qty:Float:130',
    dni.warehouse AS 'warehouse',
    dni.actual_qty AS 'Actual Qty in Warehouse:Float:150',
    IFNULL(si_item.stock_qty,0) AS 'SI Qty'

    
FROM
    `tabDelivery Note` dn
INNER JOIN
    `tabDelivery Note Item` dni ON dn.name = dni.parent
INNER JOIN
    `tabSales Invoice Item` si_item ON dni.item_code = si_item.item_code
INNER JOIN
    `tabSales Invoice` si ON si_item.parent = si.name


   WHERE
    dn.docstatus = '1'
ORDER BY
    dn.name, dni.item_code
6

try it.

SELECT
    dn.name AS 'Delivery Note:Link/Delivery Note:130',
    dn.customer AS 'Customer:Link/Customer:130',
    dn.posting_date AS 'Date',
    dni.item_code AS 'Item Code:Link/Item:120',
    dni.item_name AS 'Item Name:Data:150',
    dni.stock_qty AS 'Delivered Qty:Float:130',
    dni.warehouse AS 'Warehouse',
    dni.actual_qty AS 'Actual Qty in Warehouse:Float:150',
    IFNULL(si_item.stock_qty, 0) AS 'SI Qty'
FROM
    `tabDelivery Note` dn
INNER JOIN
    `tabDelivery Note Item` dni ON dn.name = dni.parent
LEFT JOIN
    `tabSales Invoice Item` si_item ON dni.item_code = si_item.item_code
LEFT JOIN
    `tabSales Invoice` si ON si_item.parent = si.name
WHERE
    dn.docstatus = '1'
ORDER BY
    dn.name, dni.item_code

Otherwise, check the base query report in your system.

image
image1920×992 92 KB

Thank You!