How to know the number of items issued for production against a particular sales order?

ERPNext Manufacturing
1

Hi Guys ,

How to view the number of items issued for production against a particular sales order . If there are 1200 numbers of a particular part to be delivered in a particular sales order . And I have issued three production orders 220 in each production order so I should calculate 220+220+220 sum it up and subtract it from sales order qty . Is there any way to see the pending qty to be issued for manufacturing?

Thanks

2

Hi @Muthu . There is no current report for these specifics. Though you can make your own via a query report or script report. And if you think this can be helpful for other users as well in the manufacturing industry, you can request PR and recommend this report in the community.

Regards

1 Like
3

Thanks for the speedy reply @creamdory . Could you hint me with a sample script report so that I could use that in building my report ?

Thanks

4

Hi @Muthu . You may take a look at Completed Production Order report query and replicate it with some additions to the query (you might want to join SO Item table to compare Production Qty to SO Qty). Hope this helps!

report.png1191×640 53.2 KB

Thanks

3 Likes
5

Thanks for continued support @creamdory . I tried doing many modifications to the query report , but the issue is , upon selecting a particular sales order if I had given 11 production orders 11 rows will appear and the strange thing is upon filtering the total value at the bottom of a query report will disappear.

Could you guide me with a script report ?

Thanks

6

Hi @Muthu ! It will really show 11 rows as it returns 11 rows from your query or loop. If you dont want this, you might want to now show the columns that will be returned in multiple (ex PRO ID as you mentioned 1 SO : Many PRO). You might want to just show the balance QTY (SO Qty - all the Completed PRO’s). It will only show you 1 line per SO.

7

Many thanks for the support @creamdory . Please guide me on how to achieve this .

Thanks

8

@creamdory maybe you can send a PR with this report!

2 Likes
9

Hi @creamdory . I did a query report . Now I want to return only those rows in which the issued value is greater than zero or null . Null means yet to issue the production order so I want null and greater than zero . How do I write the where clause ?

select 
 `tabSales Order`.`name` as "Sales Order:Link/Sales Order:120",
`tabSales Order`.`customer` as "Customer:Link/Customer:120",
 `tabSales Order Item`.item_code as "Item:Link/Item:120",
`tabSales Order Item`.item_name as "Des:Link/Item:120",
`tabSales Order Item`.qty as "Qty:Int:100",
 `tabSales Order Item`.delivered_qty as "Delivered Qty:Int:100",
 (`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "Qty to Deliver:Int:140",
`tabSales Order Item`.date as "Sch date:Date:100",
`tabItem`.thumbnail as "Cut wt:Data:60",
`tabProduct master`.grade as "Grade:Data:100",
`tabProduct master`.dieno as "Die no:Data:100",
`tabProduct master`.hammer as "Hammer:Data:50",
`tabProduct master`.rm_idl_cs as "Idl sec:Data:60",
`tabProduct master`.rm_alt_cs as "Alt sec:Data:60",
SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END) as "Issued:Int:80",
SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`produced_qty`
    END) as "Cut:Int:80",
(`tabSales Order Item`.qty - SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END)) as "To be issued:Int:80"

from
 `tabSales Order` 
LEFT JOIN `tabSales Order Item` ON ( `tabSales Order Item`.`parent` = `tabSales Order`.`name`
 and `tabSales Order`.docstatus = 1 )
LEFT JOIN `tabItem` ON `tabItem`.`item_code` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduct master` ON `tabProduct master`.`part_no` = `tabSales Order Item`.`item_code`
LEFT JOIN `tabProduction Order` ON (`tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item` and `tabSales Order`.`name` = `tabProduction Order`.`sales_order`)

where
`tabSales Order`.status  not in ("Stopped", "Closed", "Completed") and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)


GROUP BY `tabSales Order`.`name`;

Thanks

10

Hi Guys ,

I completed the report and here it goes . I feel this is really an important report for those who want to know the number of items issued for production against a particular sales order. It would be better if this report is pulled to our standard package .

select 
 `tabSales Order`.`name` as "Sales Order:Link/Sales Order:120",
`tabSales Order`.`customer` as "Customer:Link/Customer:120",
 `tabSales Order Item`.item_code as "Item:Link/Item:120",
`tabSales Order Item`.item_name as "Des:Link/Item:120",
`tabSales Order Item`.qty as "Qty:Int:100",
 `tabSales Order Item`.delivered_qty as "Delivered Qty:Int:100",
 (`tabSales Order Item`.qty - ifnull(`tabSales Order Item`.delivered_qty, 0)) as "Qty to Deliver:Int:140",
`tabSales Order Item`.date as "Sch date:Date:100",
ifnull(SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END),0) as "Issued:Int:80",
SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`produced_qty`
    END) as "Cut:Int:80",
(`tabSales Order Item`.qty - ifnull(SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END),0)) as "To be issued:Int:80"

from
 `tabSales Order` 
LEFT JOIN `tabSales Order Item` ON ( `tabSales Order Item`.`parent` = `tabSales Order`.`name`
 and `tabSales Order`.docstatus = 1 )

LEFT JOIN `tabProduction Order` ON (`tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item` and `tabSales Order`.`name` = `tabProduction Order`.`sales_order`)

where
`tabSales Order`.status  not in ("Stopped", "Closed", "Completed") and ifnull(`tabSales Order Item`.delivered_qty,0) < ifnull(`tabSales Order Item`.qty,0)


GROUP BY `tabSales Order`.`name` having (`tabSales Order Item`.qty - SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END)) > 0 or (`tabSales Order Item`.qty - SUM(CASE WHEN `tabSales Order`.`name` = `tabProduction Order`.`sales_order` and `tabSales Order Item`.`item_code` = `tabProduction Order`.`production_item`
         THEN `tabProduction Order`.`qty`
    END))is NULL;

Thanks

2 Likes
11

Guys , I could see a glitch with the above script . I do have around 8 items in a sales order but the above script shows only the first item in the sales order . Any idea ? @littlehera @creamdory

12

The SUM(CASE when ... in the SELECT statement my be filtering the records. I think the single record showing up is the only record which satisfies the SUM(CASE WHEN ...) Condition.

Hope it helps, @Muthu.

1 Like
13

Thanks for the reply @littlehera . Yeah I checked the SUM CASE WHEN condition but it seems to be okay , the statement is logical and seems to be correct . Any help ?

Dear Team please shed some light on this .

Thanks