search

badili / erpnext

Badili's customisation of ERPNext
0 stars 0 forks source link

Report Against Sales Order #7

Closed gladwynlewis83 closed 8 years ago

gladwynlewis83 commented 8 years ago

Received/Delivered Qty and Balance qty fields to display Purchase order and Sales order. This is important, so that we can track what is pending, and what has been delivered (via Delivery Note). The main doc to be compared against should be the Sales Order (because that is the final document without any modifications)

gladwynlewis83 commented 8 years ago

So for example if we have a Supplier Order 0001 (SO-0001) with Item ABC Quantity: 30

And Delivery Order 0001-1 Against the Supplier Order mentioned above (DO-0001-1) with Item ABC Quantity: 25

The report should show 5 items against Item ABC as yet pending.

soloincc commented 8 years ago

I have checked this using the SO-00005 and it seems to be ok. I deleted 3 items from the delivery note and these items are shown as yet to be delivered

gladwynlewis83 commented 8 years ago

What's the name of the report?

soloincc commented 8 years ago

Ordered items to be delivered

gladwynlewis83 commented 8 years ago

select tabSales Order.name as "Sales Order:Link/Sales Order:120", tabSales Order.customer as "Customer:Link/Customer:120", tabSales Order.transaction_date as "Date:Date", tabSales Order.project as "Project", tabSales Order Item.item_code as "Item:Link/Item:120", tabSales Order Item.qty as "Qty:Float:140", tabSales Order Item.delivered_qty as "Delivered Qty:Float:140", (tabSales Order Item.qty - ifnull(tabSales Order Item.delivered_qty, 0)) as "Qty to Deliver:Float:140", tabSales Order Item.base_rate as "Rate:Float:140", tabSales Order Item.base_amount as "Amount:Float:140", ((tabSales Order Item.qty - ifnull(tabSales Order Item.delivered_qty, 0))*tabSales Order Item.base_rate) as "Amount to Deliver:Float:140", tabBin.actual_qty as "Available Qty:Float:120", tabBin.projected_qty as "Projected Qty:Float:120", tabSales Order.delivery_date as "Expected Delivery Date:Date:120", tabSales Order Item.item_name as "Item Name::150", tabSales Order Item.description as "Description::200", tabSales Order Item.item_group as "Item Group:Link/Item Group:120", tabSales Order Item.warehouse as "Warehouse:Link/Warehouse:200" from tabSales Order JOIN tabSales Order Item LEFT JOIN tabBin ON (tabBin.item_code = tabSales Order Item.item_code and tabBin.warehouse = tabSales Order Item.warehouse) where tabSales Order Item.parent = tabSales Order.name and tabSales Order.docstatus = 1 and tabSales Order.status not in ("Stopped", "Closed") and ifnull(tabSales Order Item.delivered_qty,0) < ifnull(tabSales Order Item.qty,0) order by tabSales Order.transaction_date asc

gladwynlewis83 commented 8 years ago

So this is the code. The Sales Order is linked, but it is not comparing against the Delivery Note. As you can see from the Report Code above.