Whilst perusing the GL, there are entries which reference, “Periodic Posting Process.” The detail can then be viewed by drilling down on the JE or using the Inventory/WIP report. I’ve been trying to set up a BAQ which could be run for a GL Account segment to show the detail of the Periodic Posting Process entries. Does anyone know if there’s a way to get a report / BAQ that shows the journal entries posted to an account as well as the detail of the Periodic Posting Process entries?
Thanks, Tim
Yeah, it will take a little time and be a little frustrating though.
You will need these tables.
GLJrnDtl
TranGLC
PartTran
Then, depending on how much detail and for what transactions, you can go to the related transaction tables to get what isn’t in PartTran… The joins are a little tricky. Besides finding the correct keys, you’ll have to handle the formatting of values in the same key field on PartTran that relate to a different table that has a different format.
Inventory/WIP Reconciliation can provide this too. Choose “Posted” transactions and filter as needed.
You weren’t kidding about the joins being a little tricky. I was finally able to get it to work. Thanks for you assistance.
I’m struggling with the joins, could someone maybe share a screenshot? It would be much appreciated.
Thanks in advance.
Do you mind sharing the BAQ?
That would be cool!
In Epicor10, you can change the posting rules so more data is available, which is a much better way to go. Instead of “Periodic Posting”, for PO receipts for instance, I now have it show the Vendor ID / Vendor Name / PO # / Part.
Hello. I know this is a long shot two years after this post. Can you provide the framework for the BAQ?
In Epicor10, you can change the posting rules so more data is available, which is a much better way to go. Instead of “Periodic Posting”, for PO receipts for instance, I now have it show the Vendor ID / Vendor Name / PO # / Part.
Hello,
I am wondering if you could share your joins for this BAQ. I’ve been trying to do the same thing and believe I am very close to getting this to run successfully. I do not want to change our posting rules quite yet as we just went live 4 months ago, but the join notes would really help me out!
Thank you!
I ended up changing the posting rules instead of finishing the BAQ.
Okay thank you for your response! Happy New Year!
Were you able to get the BAQ done regarding periodic posting process?
I am following this thread, and haven’t seen any updates on the framework of the BAQ.
Can you chare the BAQ or the framework for it, please?
At this point, I am stuck and I am using the three tables as suggested.
When we upgraded to Epicor 10.2.600 Using the Posting Rules, I was able to change the Journal Transaction Descriptions so that’s what I did.
Instead of showing Periodic Posting for a PO receipt, for example, I now have it show the Vendor ID, Vendor Name, PO # and Part Number.
For job-related transactions, I have it show the Transaction Type, Job # and Project #.
I was not, unfortunately, and have been unable to get back to this to try and figure it out. I have three separate queries to pull information out right now and just put them all into one dashboard to make it a little easier.
Since I just spent a few hours figuring this out, I’ll share what I found and how I was able to link multiple tables to TranGLC. This information is covered in KB0028094: Adding GL Account to a BAQ
Overall, the BAQ Designer is just a GUI interface for building a SQL statement. To create the relationships between TranGLC and multiple tables, you need a more advanced SQL statement. Not many BAQ users realize that you can add expressions into Table Relations instead of just choosing fields from the dropdowns.
Per the KB article, when you link the tables to TranGLC, you need to specify the ‘RelatedToFile’. For example, if you link PartTran to TranGLC, you should specify TranGLC.RelatedToFile = ‘PartTran’. Also, since the TranGLC key fields are all data type NAVCHAR(50), you will need to convert some key fields of the linked tables to NAVCHAR(50). I will use the PartTran table as the example again. When you create the table relations, type in the expression CONVERT(NVARCHAR(50),PartTran.SysDate) instead of just choosing SysDate from the dropdown. Per the article, you need the full TableName.FieldName format. Please see the screenshot below for how to link TranGLC to PartTran.
For other tables, you will need to make similar conversions. Per the article (question 4), boolean and date fields require special attention. For boolean (true/false) fields, you will need to do a CASE statement instead of converting. For date fields, you will need to add a ‘1’ to the CONVERT function to specify the style. Please see the article for examples, but I also copy/pasted the Q&A for questions 3 and 4 below.
- How should I link TranGLC to the transaction table (e.g., APInvHed, InvcDtl)?
The TranGLC uses Company, Key1, Key2, Key3, Key4, Key5, and Key6 to link to the original table. The table name is stored in the RelatedToFile field. The values for Key1 through Key6 will match the Primary Key of the table. For example, to link TranGLC to the InvcDtl table you would use the following links: TranGLC.RelatedToFile = ‘InvcDtl’, TranGLC.Company = InvcDtl.Company, TranGLC.Key1 = InvcDtl.InvoiceNum, TranGLC.Key2 = InvcDtl.InvoiceLine
Note: if the Primary Key of the Transaction Table has more than 6 fields (excluding Company) then TranGLC.Key1 will match the SysRowID of the transaction table. For example, to match to the TaxDtl table, you would use the following links: TranGLC.RelatedToFile = ‘TaxDtl’, TranGLC.Company = TaxDtl.Company, TranGLC.Key1 = TaxDtl.SysRowID
- In BAQ Designer I get errors when linking non-character fields to the TranGLC Key fields (e.g., InvcDtl.InvoiceNum), how do I correct this?
The TranGLC Key fields require the data to be of type NVARCHAR(50). For most data types you can use the CONVERT syntax directly. However, date and boolean/bit/logical have special formatting to ensure it is culture invariant. Here are the conversions you should use for each data type.
INTEGER (e.g., InvcDtl.InvoiceNum): CONVERT( NVARCHAR(50), TableName.IntegerFieldName ) DECIMAL (e.g., CashBDtl.CashBookNum): CONVERT( NVARCHAR(50), TableName.DecimalFieldName ) UNIQUE IDENTIFIER (e.g., TaxDtl.SysRowID): CONVERT( NVARCHAR(50), TableName.UniqueIdentifierFieldName ) BOOLEAN/BIT/LOGICAL: (CASE WHEN TableName.BooleanField = 0 THEN 'False' ELSE 'True' END) DATE (e.g., PartTran.SysDate): CONVERT( NVARCHAR(50), TableName.DateField, 1)In the BAQ designer, you will need to click into the expression within the criteria and type in the CONVERT formula manually. This also requires using the full TableName.FieldName, you cannot use only the FieldName.
Hello Joel. I know this is probably a long shot, but could you provide the overall framework/SQL for the BAQ?
