(Back to B-FN Financials Module SQL Posted to Wiki Listing - B-FN Financials Module SQL Posted to Wiki )
TYPE: Frequently Requested SQL.
AUTHOR NAME: Zal Parchem
PURPOSE AND BACKGROUND: This SQL was created in 8.8 PL15. This SQL lists out the journal entries restricted by the date range and SAP B1 Chart of Account "Drawer" entered by the End User. The results are good for checking out discrepancies and researching journal entries at month-end. A special note about the WHERE statement - in this example, the TransType of -3 is not included in the results - make sure you check with your Financial Personnel to ensure they also want this type of transaction excluded from their results. You will probably notice there are several TransTypes included in the SQL which will never make it to the General Ledger - that was done on purpose to get all of the TransTypes together in one spot. The TransType list might not be complete and that is why there is a default of "RESEARCH" in the results. When you first start using this SQL, always use the SAP B1 Filter function on the results to see if there are any entries of "RESEARCH" - add them to your SQL, but make sure you come back to the Wiki and update the list. Another special note about the "Segment_X" entries - make sure you include all of the Segmentation fields for your company (this Customer only had 2). If you find this SQL helpful, copy and paste it into your system.
Here is a screen print of the SQL results:
Copy and Paste this SQL:
--B-FN Journal Entry List by Date Range Ver 1 ZP 2011 04 22
--DESCRIPTION: SQL lists out all Journal Entries by SAP B1 Drawer and identifies what type of transaction the JE is.
--Version 1 Zal Parchem 22 April 2011
T1.Segment_0 + '-' + T1.Segment_1 AS 'GL Acct',
WHEN T1.GroupMask = 1 THEN 'Assets'
WHEN T1.GroupMask = 2 THEN 'Liabilities'
WHEN T1.GroupMask = 3 THEN 'Equity'
WHEN T1.GroupMask = 4 THEN 'Revenue'
WHEN T1.GroupMask = 5 THEN 'COGS'
WHEN T1.GroupMask = 6 THEN 'Expenses'
WHEN T1.GroupMask = 7 THEN 'Financing'
WHEN T1.GroupMask = 8 THEN 'Oth Inc/Exp'
END AS 'Grp Mask Def',
T0.RefDate AS 'Post Date',
T0.TransId AS 'JE Numb',
WHEN T0.TransType = -1 THEN 'System Generated'
WHEN T0.TransType = -2 THEN 'Open Balance'
WHEN T0.TransType = -3 THEN 'Year End Closing'
WHEN T0.TransType = 13 THEN 'AR Invoice'
WHEN T0.TransType = 14 THEN 'AR Cred Memo'
WHEN T0.TransType = 15 THEN 'AR Delivery'
WHEN T0.TransType = 16 THEN 'AR Goods Ret'
WHEN T0.TransType = 17 THEN 'AR Sales Order'
WHEN T0.TransType = 18 THEN 'AP Invoice'
WHEN T0.TransType = 19 THEN 'AP Cred Memo'
WHEN T0.TransType = 20 THEN 'Goods Receipt PO'
WHEN T0.TransType = 21 THEN 'AP Goods Ret'
WHEN T0.TransType = 22 THEN 'AP Purch Ord'
WHEN T0.TransType = 23 THEN 'AR Sales Quote'
WHEN T0.TransType = 24 THEN 'Incoming Pay'
WHEN T0.TransType = 25 THEN 'Deposit'
WHEN T0.TransType = 30 THEN 'Jrnl Entry'
WHEN T0.TransType = 46 THEN 'Outgoing Pay'
WHEN T0.TransType = 56 THEN 'Chk for Payment'
WHEN T0.TransType = 58 THEN 'Inv Bal Adj'
WHEN T0.TransType = 59 THEN 'Rcpt frm Prod'
WHEN T0.TransType = 60 THEN 'Goods Issue'
WHEN T0.TransType = 67 THEN 'Inv Transfer'
WHEN T0.TransType = 68 THEN 'Work Instruct'
WHEN T0.TransType = 69 THEN 'Landed Costs'
WHEN T0.TransType = 76 THEN 'Posted Deposit'
WHEN T0.TransType = 132 THEN 'Correct Invoice'
WHEN T0.TransType = 162 THEN 'Inv Reval'
WHEN T0.TransType = 163 THEN 'AP Corr Inv'
WHEN T0.TransType = 164 THEN 'AP Corr Inv Rev'
WHEN T0.TransType = 165 THEN 'AR Corr Inv'
WHEN T0.TransType = 166 THEN 'AR Corr Inv Rev'
WHEN T0.TransType = 182 THEN 'BOE Deposit'
WHEN T0.TransType = 202 THEN 'Prod Ord'
WHEN T0.TransType = 203 THEN 'AR Down Pay'
WHEN T0.TransType = 204 THEN 'AP Down Pay'
WHEN T0.TransType = 321 THEN 'Manual Recon'
WHEN T0.TransType = 310000001 THEN 'Open Balance'
END AS 'Trans Def',
T0.TransType AS 'Trans Type',
T0.Line_ID +1 AS 'JE Line',
T0.Debit AS 'Debit',
T0.Credit AS 'Credit',
(T0.Debit - T0.Credit) AS 'Net'
INNER JOIN OACT T1
ON T0.Account = T1.AcctCode
T0.RefDate >= [%0]
AND T0.RefDate <= [%1]
AND T1.GroupMask = [%2]
AND T0.TransType < > -3