I need to reconcile AP Clearing. The g/l detail consists of IJ + PJ journalcodes.
Within IJ journal codes are 5 types: ADJ-PUR, PUR-INS, PUR-MTL, PUR-STK, and PUR-UKN and these g/l IJ code detail balances with the Inventory/WIP Reconciliation Report net amount, but this report does not give detail such as Vendor, PO, PackSlip, etc so I can’t cross-off matching transactions from the g/l PJ code detail (AP invoice). And, the AP data (PJ journalcode) does not break out into separate columns data we need to sort/match, such as Vendor, PO, PackSlip. The Supplier Invoice-Receipt Number report gives PO/PS info, but then it doesn’t have the IJ data.
How do I recreate the canned Inventory/WIP Reconciliation Report and then add to it the necessary other AP info I need? And/or, how do I update the Supplier Invoice-Receipt Number report to also give IJ transaction data (ADJ-PUR, PUR-INS, PUR-MTL, PUR-STK, and PUR-UKN)? Or, create one/two/three BAQs with the necessary data (from what tables) and merge the data and then manipulate the data to see what’s left over (i.e. the balance in the AP Clearing)?
I need these BAQs to be able to filter by: g/l fiscal period (to balance each month separately) and then by g/l account number (for the AP Clearing account, which is 2151)?
But, I still need help creating the BAQ(s). Maybe I cannot do ONE BAQ with all those tables? Maybe I need 2-3?
Maybe below?
Link TranGLC to PartTran
Link TranGLC to APInvExp and APInvDtl
Link TranGLC to APInvJob and APInv Dtl
But, I need SCREENSHOTS as I’ve tried creating and they don’t generate the correct data (missing PUR-MTL and PUR-UKN IJ codes, for example, even though I have all PUR-* coded)…
I need SCREENSHOTS:
Of the tables and joins with Table List (Query Builder | Phrase Build | Table List)
Of the tables and joins with Table Relations (Query Builder | Phrase Build | Table Relations)
Of the tables and joins with Table Criteria (Query Builder | Phrase Build | Table Criteria)
Table Display Fields (Query Builder | Display Fields | Column Select)
Table Sort Order (Query Builder | Display Fields | Sort Order)
And then any SubQuery info, if needed
In E9 I had one query. But that refused to work in E10. So I split the query in two for PJ and IJ respectively. I can send the same if you want. We are on K2022.2.7.
Is there anyone that can help with the necessary BAQ(s) I need to help me reconcile AP Clearing? Please. I need screenshots since I’ve been trying to build them and I’m doing something wrong as I don’t get the expected data.
I missed your message earlier. I am uploading the two queries now. They are PurchAccrual1 for IJ and PurchAccrual2 for PJ. All the columns are in the same order, but the second query has less columns. This will be evident from looking at them.
The calculated field WIP2 corresponds to the book amount in the GL. The common field that we use for matching the two is APInvDtl_PackSlip.
Both the queries have parameters. You will have to use your AP Clearing GL Segment number. PurchAccrual2.baq (24.0 KB) PurchAccrual1.baq (24.2 KB)
Since I’m not familiar, once I download these can I copy/paste them to a particular [Epicor] folder and double-click to launch? But how do I first update them to edit our g/l account number [since Notepad shows a bunch of junk when I try to use that program]?
I also use Crystal Reports for IQMS (for another company), and I would have to copy/save any Crystal Reports in a particular IQMS “reports” folder to launch.
Never mind. I figured out how to add vendor name to both BAQs. And, only some of the invoice numbers are showing for the IJ transactions, which probably make sense if no invoice has been entered yet, correct?
