x Want to participate? Join the group to interact
  • Replies 9 replies
  • Subscribers 135 subscribers
  • Views 3917 views
  • Users 0 members are here
Browse Forums
Announcements
268
topics
260
replies
Databases and Operating Systems
757
topics
2k
replies
Financials Suite
1k
topics
3k
replies
General Discussion
2k
topics
10k
replies
Operational Suite
851
topics
2k
replies
Reporting and Analytics
1k
topics
3k
replies
Reports, Macros, and Customizations
876
topics
3k
replies
Sage CRM for Sage 300
182
topics
410
replies
Ric S

Purchase Order Clearing Account Reconciliation

Posted By

Using Sage 300 Premium 2018, with Purchasing and Inventory Control modules.

The PO Clearing account on the balance sheet has no means of reconciliation or audit report that proves the GL.

I need a report that shows PO receipts that reconciles to the balance of the GL.

Any suggestions?

Parents

  • With 31 March our year-end, we recently used a new query for Purchase Order Clearing Account Reconciliation (GRNI or Goods Received Not Invoiced) .

    Purchase Order Clearing Accounts are typically set up under I/C Account Sets, Payables Clearing and also P/O GL Integration, Non-Inventory Payables Clearing Account.

    The following SQL query is being used via Excel Powerpivot:

    SELECT
    [POAAPC].*
    FROM [POAAPC]
    ----------------------------
    INNER JOIN
    ----------------------------
    (
    SELECT
    [POAAPC].[DOCZSEQ],
    [POAAPC].[ITEMNO],
    [POAAPC].[VENDOR]
    FROM [POAAPC]
    WHERE ([POAAPC].[FISCYEAR] <> N'2023') --- Needs updating each year
    GROUP BY
    [POAAPC].[DOCZSEQ],
    [POAAPC].[ITEMNO],
    [POAAPC].[VENDOR]
    HAVING SUM([POAAPC].[SCEXTENDED]) <> 0
    ) AS POAAPCcopy
    -------------------------------------------------
    ON ( (POAAPCcopy.[DOCZSEQ] = [POAAPC].[DOCZSEQ]) AND
    (POAAPCcopy.[ITEMNO] = [POAAPC].[ITEMNO]) AND
    (POAAPCcopy.[VENDOR] = [POAAPC].[VENDOR]) )
    WHERE
    ( ([POAAPC].[FISCYEAR] = N'2018') OR
    ([POAAPC].[FISCYEAR] = N'2019') OR
    ([POAAPC].[FISCYEAR] = N'2020') OR
    ([POAAPC].[FISCYEAR] = N'2021') OR
    ([POAAPC].[FISCYEAR] = N'2022') ) --- Needs new line each year

    This may help some accounting folks who are challenged with reconciling Accounts Payable clearing accounts.

Reply

  • With 31 March our year-end, we recently used a new query for Purchase Order Clearing Account Reconciliation (GRNI or Goods Received Not Invoiced) .

    Purchase Order Clearing Accounts are typically set up under I/C Account Sets, Payables Clearing and also P/O GL Integration, Non-Inventory Payables Clearing Account.

    The following SQL query is being used via Excel Powerpivot:

    SELECT
    [POAAPC].*
    FROM [POAAPC]
    ----------------------------
    INNER JOIN
    ----------------------------
    (
    SELECT
    [POAAPC].[DOCZSEQ],
    [POAAPC].[ITEMNO],
    [POAAPC].[VENDOR]
    FROM [POAAPC]
    WHERE ([POAAPC].[FISCYEAR] <> N'2023') --- Needs updating each year
    GROUP BY
    [POAAPC].[DOCZSEQ],
    [POAAPC].[ITEMNO],
    [POAAPC].[VENDOR]
    HAVING SUM([POAAPC].[SCEXTENDED]) <> 0
    ) AS POAAPCcopy
    -------------------------------------------------
    ON ( (POAAPCcopy.[DOCZSEQ] = [POAAPC].[DOCZSEQ]) AND
    (POAAPCcopy.[ITEMNO] = [POAAPC].[ITEMNO]) AND
    (POAAPCcopy.[VENDOR] = [POAAPC].[VENDOR]) )
    WHERE
    ( ([POAAPC].[FISCYEAR] = N'2018') OR
    ([POAAPC].[FISCYEAR] = N'2019') OR
    ([POAAPC].[FISCYEAR] = N'2020') OR
    ([POAAPC].[FISCYEAR] = N'2021') OR
    ([POAAPC].[FISCYEAR] = N'2022') ) --- Needs new line each year

    This may help some accounting folks who are challenged with reconciling Accounts Payable clearing accounts.

Children
No Data

*Community Hub is the new name for Sage City