Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / PO balances with recei...
Microsoft Dynamics GP (Archived)

PO balances with receiving date restrictions

(0) ShareShare
ReportReport
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

  • Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: PO balances with receiving date restrictions

    Most welcome

    Thanks for the update, and never hesitate to share any further inquiries,

  • Pete72 Profile Picture
    Pete72 540 on at
    RE: PO balances with receiving date restrictions

    Thanks Mahmoud,

    Works perfectly.

  • Verified answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    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 Profile Picture
    Pete72 540 on at
    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 Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: PO balances with receiving date restrictions

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans