Reports: Pending Items Needed for Purchase

ERPNext User Forum
1

Hi,

I am unable to get this query to work:

select
 tabPurchase Request.name as “Indent No”,
 tabPurchase Request.transaction_date as “Indent Date”,
 tabPurchase Request Item.item_code as “Item Code”,
 tabPurchase Request Item.description as “Description”,
 tabPurchase Request Item.qty as “Qty”,
 tabPurchase Request Item.uom as "UoM",<br>&nbsp;tabPurchase Request Item.ordered_qty as "Ordered"<br><br>from<br>&nbsp;tabPurchase Request,tabPurchase Request Item<br>where<br>&nbsp;tabPurchase Request Item.parent = tabPurchase Request.name<br>&nbsp;and tabPurchase Request`.status = “Submitted”

 and `tabPurchase Request`.transaction_date <= curdate()

 and ifnull(`tabPurchase Request Item`.ordered_qty,0) < ifnull(`tabPurchase Request Item`.qty,0)


<!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;}

<![endif]–>
order by tabPurchase Request.transaction_date desc
2

Hi Aditya,

There is missing "&quot; character in <span style="color:rgb(0,0,255)"><span style="color:rgb(0,0,0)">tabPurchase Request Item.uom as "UoM". Please try the following query:


select

tabPurchase Request.name as "Indent No",
tabPurchase Request.transaction_date as "Indent Date",
tabPurchase Request Item.item_code as "Item Code",
tabPurchase Request Item.description as "Description",

tabPurchase Request Item.qty as "Qty",
tabPurchase Request Item.uom as "UoM",
tabPurchase Request Item.ordered_qty as "Ordered"

from
tabPurchase Request,tabPurchase Request Item

where
tabPurchase Request Item.parent = tabPurchase Request.name
and tabPurchase Request.status = "Submitted"

and tabPurchase Request.transaction_date <= curdate()

and ifnull(tabPurchase Request Item.ordered_qty,0) < ifnull(tabPurchase Request Item.qty,0)


order by tabPurchase Request.transaction_date desc


Regards,
Nabin Hait



On Mon, Dec 17, 2012 at 8:50 PM, Addy <ad…@gmail.com> wrote:

Hi,

I am unable to get this query to work:

select
`tabPurchase Request`.name as "Indent No",
`tabPurchase Request`.transaction_date as "Indent Date",
`tabPurchase Request Item`.item_code as "Item Code",
`tabPurchase Request Item`.description as "Description",
`tabPurchase Request Item`.qty as "Qty",
`tabPurchase Request Item.uom as "UoM",
`tabPurchase Request Item`.ordered_qty as "Ordered"

from
`tabPurchase Request`,`tabPurchase Request Item`
where
`tabPurchase Request Item`.parent = `tabPurchase Request`.name
and `tabPurchase Request`.status = "Submitted"

and `tabPurchase Request`.transaction_date <= curdate()

and ifnull(`tabPurchase Request Item`.ordered_qty,0) < ifnull(`tabPurchase Request Item`.qty,0)



order by tabPurchase Request.transaction_date desc

3

Thanks Nabin, the code works fine.

Is there a way I could check on these small errors myself, is there any software which could be used to catch such small errors. Like for HTML code I am using Adobe Dreamweaver, is there something for mysql query as well.


On Tuesday, December 18, 2012 10:50:19 AM UTC+5:30, Nabin Hait wrote:

Hi Aditya,

There is missing "" character in <span style="color:rgb(0,0,255)"><span style="color:rgb(0,0,0)">tabPurchase Request Item.uom as “UoM”. Please try the following query:


select

 tabPurchase Request.name as “Indent No”,
 tabPurchase Request.transaction_date as “Indent Date”,
 tabPurchase Request Item.item_code as “Item Code”,
 tabPurchase Request Item.description as “Description”,

 tabPurchase Request Item.qty as “Qty”,
 tabPurchase Request Item.uom as “UoM”,
 tabPurchase Request Item.ordered_qty as “Ordered”

from
 tabPurchase Request,tabPurchase Request Item

where
 tabPurchase Request Item.parent = tabPurchase Request.name
 and tabPurchase Request.status = “Submitted”

 and tabPurchase Request.transaction_date <= curdate()

 and ifnull(tabPurchase Request Item.ordered_qty,0) < ifnull(tabPurchase Request Item.qty,0)


order by tabPurchase Request.transaction_date desc


Regards,
Nabin Hait


On Mon, Dec 17, 2012 at 8:50 PM, Addy <ad…@gmail.com> wrote:

Hi,

I am unable to get this query to work:

select
 `tabPurchase Request`.name as "Indent No",
 `tabPurchase Request`.transaction_date as "Indent Date",
 `tabPurchase Request Item`.item_code as "Item Code",
 `tabPurchase Request Item`.description as "Description",
 `tabPurchase Request Item`.qty as "Qty",
 `tabPurchase Request Item.uom as "UoM",
 `tabPurchase Request Item`.ordered_qty as "Ordered"

from
 `tabPurchase Request`,`tabPurchase Request Item`
where
 `tabPurchase Request Item`.parent = `tabPurchase Request`.name
 and `tabPurchase Request`.status = "Submitted"

 and `tabPurchase Request`.transaction_date <= curdate()

 and ifnull(`tabPurchase Request Item`.ordered_qty,0) < ifnull(`tabPurchase Request Item`.qty,0)



order by tabPurchase Request.transaction_date desc