PO balances with receiving date restrictions

(0) Share

Report

Posted on by Pete72

540

Does anyone know of a report that allows you to see PO line balances based on qualifying the receiving date? I think I have the correct tables, namely the POP10110, POP10500 and POP30300 among others but when trying to design this in Crystal, when I pass a parameter for Receiving date to be less than or equal to a user provided date, any PO line items that have had no receivings disappear from the report. The purpose of this report is to obtain detailed listing of outstanding committments by date.

*This post is locked for comments

All responses (5)

Answers (1)

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: PO balances with receiving date restrictions

Most welcome

Thanks for the update, and never hesitate to share any further inquiries,
Pete72 540 on at

Like (0)

Report

RE: PO balances with receiving date restrictions

Thanks Mahmoud,

Works perfectly.
Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: PO balances with receiving date restrictions

The script mainly gathers all the data in POP10110 as the primary set of data, the left outer join ensures that even if the PO has no corresponding data in POP10500 (receiving details), the PO details will be included.

You are not supposed to pass the (POP10500.Date Received) as the date parameter on the report level, consider the (POP10110.Required Date). The Where condition included in your script will not retrieve any data as it includes a field that could retrieve a null value.

You may additionally consider another method as follows;

Include the ISNULL function in your SQl statement to eliminate NULL values, ISNULL(POP10500.DATERECD, 0) AS DATERECD

The same shall be applied on your Where condition

WHERE POP10110.PONUMBER = 'PO2014.00259'
AND CONVERT(DATE, ISNULL(POP10500.DATERECD, 0)) < ( '2013-01-01 ' )

Check below modifications;

Your feedback is highly appreciated,
Pete72 540 on at

Like (0)

Report

RE: PO balances with receiving date restrictions

Hello Mahmoud. thank you for your quick reply.

Here is the script as copied directly from Crystal:

SELECT POP10110.PONUMBER, POP10110.ORD, POP10500.POPRCTNM, POP10500.RCPTLNNM, POP10110.REQDATE, POP10110.POLNESTA, POP10500.QTYINVCD, POP10110.VENDORID, POP10500.APPYTYPE, POP10500.QTYRESERVED, POP10110.EXTDCOST, GL00105.ACTNUMST, POP10500.DATERECD

FROM (CRC.dbo.POP10110 POP10110 INNER JOIN CRC.dbo.GL00105 GL00105 ON POP10110.INVINDX=GL00105.ACTINDX) LEFT OUTER JOIN CRC.dbo.POP10500 POP10500 ON (POP10110.PONUMBER=POP10500.PONUMBER) AND (POP10110.ORD=POP10500.POLNENUM)

WHERE POP10110.PONUMBER='PO2014.00259' AND POP10500.DATERECD<{ts '2013-12-31 10:20:01'}

ORDER BY POP10110.PONUMBER, POP10110.ORD
Mahmoud Saadi 32,738 on at

Like (0)

Report

RE: PO balances with receiving date restrictions

Could you share the SQL script to better help resolve the problem,