CODEWITHSUNDEEP
sqlsql-servert-sql
Adrian Warness
Adrian Warness

Reputation: 69

Find Unvouchered Purchase Orders

I am trying to create a SQL query that shows a list of all the purchase orders that not been vouchered. In our current system to check if a PO has been vouchered, members of the AP staff click into the PO and click on a tab where they are presented with data similar to the following.

This is an example of a Vouchered PO. The AP Purchase line vouchers the purchase.

enter image description here

An unvouchered PO would look like this. As you can see it is missing the line entries for the AP Purchase that mark it as Vouchered. enter image description here

If I wanted to recreate the results for the vouchered PO my code would look like this:

select 
imhstry_dte_effctve as DateEffective,
imhstry_srce_jrnl as SourceJournal,
prchseordr_id as 'PO ID',
sum(imhstry_qntty_ordrd) as QuantityOrdered,
sum(imhstry_qntty_rcvd) as QuantityReceived,
sum(imhstry_qntty_invcd_ap) as Vouchered
from imhstry
left join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
Where prchseordr_id = '1234' 
group by 
imhstry.imhstry_itm_dscrptn,
imhstry.imhstry_dte_effctve,
imhstry.imhstry_srce_jrnl,
prchseordr.prchseordr_id

What I would like to do is create a query that displays a simple list of all the Purchase Orders currently in our system that have not been vouchered and if possible all the PO's that have not been vouchered in full. I have a feeling this is pretty straight forward but I am stuck and cannot think of how to solve this. Any help is much appreciated.

Please let me know if this is not clear or more information is needed.

Upvotes: 0

Views: 79

Answers (1)

Serg
Serg

Reputation: 22811

First, 

left join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
Where prchseordr_id = '1234'

is effectively inner join. Are you sure? if left join is requried then change it

left join prchseordr on imhstry.imhstry_ordr_id = prchseordr.prchseordr_id
Where imhstry.imhstry_ordr_id = '1234'

From your sample data it looks like you need just drop imhstry_srce_jrnl from SELECT and GROUP BY and add HAVING sum(imhstry_qntty_invcd_ap) = 0 or HAVING sum(imhstry_qntty_invcd_ap) < sum(imhstry_qntty_ordrd) depending on requirements.

Upvotes: 2

Related Questions