Recreating the detailed General Journal Report SQL

One of our database gurus and I have been trying to recreate the Detailed General Journal Report. This report is run by going to Ledger > Accounting Window > Print General Journal > Change type to detail and specify the starting and ending account IDs. We are able to recreate the Summary report using the source table "JOURNAL" but cannot arrive back at the detail level. Does anyone know what table/s the detail level is saved in?

Joshua

Find more posts tagged with

VISUAL - Enterprise General Discussions

Comments

unknown

There are a number of tables which hold the detail. I have a created a number of reports and Excel queries which interrogate them directly. Start with JOURNAL_DIM, PROJECT_DIST (if you have Projects), REVENUE_DIST and REVALUE_DIST. If you can't get the detail you need from JOURNAL_DIM you'll have to go down to the various distribution detail tables - FIN_DIST_DETAIL for all the Finance transactions, and on the Manufacturing side look at PURCHASE_DIST, INV_TRANS_DIST etc etc.

I have created a SQL View which has over a dozen SQL Select statements unioned together to produce a single output table with every transaction type for the P&L (Revenue and Expense account types). It can be done but it's fiddly because not everything's in the same place!

Kim

unknown

Thanks Kim! So basically for every batch type, the detailed journal entry is in a separate table? So far I have a query referencing the Journal table to the ADJUSTMENT_DIST, PURCHASE_DIST, PAYABLE_DIST and GJ_DIST. we also have entries with batch types of APC, WIP, FG, SLS, IND, ARC and ARI. Are there separate tables for each of these batch types or are some combined?

unknown

I've copied below my SQL which is a union of 14 SELECT statements to pull together in a single table (SQL View actually) all transactions with details for accounts of type R or E - effectively all our P&L accounts.

What I've attempted to do is get enough details for each transaction type, but not all types have relevant data to fill all the columns so I use 'N/A' to fill columns where there is no relevant data, as with a Union you have to get each SELECT statement to output the same columns. Note that the criteria refer to our particular chart of accounts and department list.

The first 4 are Journal_dim records for PUR, ADJ, IND, WIPL - these separate so that each can be joined to a relevant table to get the detail which is different for each type.

The next one is FIN_DIST_DETAIL which covers API, APC, ARI, ARC, GJ, BAJ - only 1 SELECT statement needed here because the detail just comes from PAYABLES for API transactions - the other types don't have useful data in certain fields that we need.

Next are 6 SELECT statements on the PROJECT_DIST table covering types PURC MATL, PURC SERV, INV ADJ, WIP LABOR, WIP ISSUE, GJ LINE & PAYBL LINE. If you don't have a projects database you won't need these.

Then 1 SELECT statement for REVALUE_DIST

Next is a SELECT statement for REVENUE_DIST for everything generated by Revenue Recognition

Finally another Revenue Recognition Query, but this is a bespoke table for us so you definitely won't need it!

Hope this helps

Select

'Journal_Dim' As Data_Table,

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE As SOURCE_TYPE,

MBUK.dbo.JOURNAL_DIM.SOURCE_ID As SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.JOURNAL_DIM.POSTING_DATE As POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.DEBIT_AMOUNT - MBUK.dbo.JOURNAL_DIM.CREDIT_AMOUNT As

Amount,

MBUK.dbo.JOURNAL_DIM.ACCT_YEAR As ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.JOURNAL_DIM.DOCUMENT_DATE As DOCUMENT_DATE,

MBUK.dbo.JOURNAL_DIM.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.JOURNAL_DIM.WAREHOUSE_ID As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

MBUK.dbo.PURC_ORDER_LINE.USER_3 As [REF/DESCRIPTION],

MBUK.dbo.JOURNAL_DIM.VENDOR_ID As VENDOR_ID,

MBUK.dbo.JOURNAL_DIM.USER_ID As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

'N/A' As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.JOURNAL_DIM Left Join

MBUK.dbo.RECEIVER_LINE

On MBUK.dbo.JOURNAL_DIM.RECEIVER_ID = MBUK.dbo.RECEIVER_LINE.RECEIVER_ID And

MBUK.dbo.JOURNAL_DIM.RECEIVER_LINE_NO = MBUK.dbo.RECEIVER_LINE.LINE_NO

Left Join

MBUK.dbo.PURC_ORDER_LINE

On MBUK.dbo.RECEIVER_LINE.PURC_ORDER_ID =

MBUK.dbo.PURC_ORDER_LINE.PURC_ORDER_ID And

MBUK.dbo.RECEIVER_LINE.PURC_ORDER_LINE_NO = MBUK.dbo.PURC_ORDER_LINE.LINE_NO

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID

Where

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE = 'PUR' And

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Journal_Dim' As Data_Table,

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE As SOURCE_TYPE,

MBUK.dbo.JOURNAL_DIM.SOURCE_ID As SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.JOURNAL_DIM.POSTING_DATE As POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.DEBIT_AMOUNT - MBUK.dbo.JOURNAL_DIM.CREDIT_AMOUNT As

Amount,

MBUK.dbo.JOURNAL_DIM.ACCT_YEAR As ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.JOURNAL_DIM.DOCUMENT_DATE As DOCUMENT_DATE,

MBUK.dbo.JOURNAL_DIM.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.JOURNAL_DIM.WAREHOUSE_ID As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

MBUK.dbo.INVENTORY_TRANS.DESCRIPTION As [REF/DESCRIPTION],

MBUK.dbo.JOURNAL_DIM.VENDOR_ID As VENDOR_ID,

MBUK.dbo.JOURNAL_DIM.USER_ID As USER_ID,

MBUK.dbo.INVENTORY_TRANS.PART_ID As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.INVENTORY_TRANS.WORKORDER_BASE_ID As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.JOURNAL_DIM Inner Join

MBUK.dbo.INVENTORY_TRANS

On MBUK.dbo.JOURNAL_DIM.SOURCE_ID = MBUK.dbo.INVENTORY_TRANS.TRANSACTION_ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID

Where

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE = 'ADJ' And

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Journal_Dim' As Data_Table,

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE As SOURCE_TYPE,

MBUK.dbo.JOURNAL_DIM.SOURCE_ID As SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.JOURNAL_DIM.POSTING_DATE As POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.DEBIT_AMOUNT - MBUK.dbo.JOURNAL_DIM.CREDIT_AMOUNT As

Amount,

MBUK.dbo.JOURNAL_DIM.ACCT_YEAR As ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.JOURNAL_DIM.DOCUMENT_DATE As DOCUMENT_DATE,

MBUK.dbo.JOURNAL_DIM.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.JOURNAL_DIM.WAREHOUSE_ID As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

MBUK.dbo.LABOR_TICKET.DESCRIPTION As [REF/DESCRIPTION],

MBUK.dbo.JOURNAL_DIM.VENDOR_ID As VENDOR_ID,

MBUK.dbo.JOURNAL_DIM.USER_ID As USER_ID,

'N/A' As PART_ID,

MBUK.dbo.LABOR_TICKET.EMPLOYEE_ID As EMPLOYEE_ID,

MBUK.dbo.LABOR_TICKET.INDIRECT_ID As INDIRECT_ID,

MBUK.dbo.LABOR_TICKET.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.LABOR_TICKET.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.JOURNAL_DIM Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.LABOR_TICKET

On MBUK.dbo.JOURNAL_DIM.SOURCE_ID = MBUK.dbo.LABOR_TICKET.TRANSACTION_ID

Where

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE = 'IND' And

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Journal_Dim' As Data_Table,

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE As SOURCE_TYPE,

MBUK.dbo.JOURNAL_DIM.SOURCE_ID As SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.JOURNAL_DIM.POSTING_DATE As POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.DEBIT_AMOUNT - MBUK.dbo.JOURNAL_DIM.CREDIT_AMOUNT As

Amount,

MBUK.dbo.JOURNAL_DIM.ACCT_YEAR As ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.JOURNAL_DIM.DOCUMENT_DATE As DOCUMENT_DATE,

MBUK.dbo.JOURNAL_DIM.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.JOURNAL_DIM.WAREHOUSE_ID As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

MBUK.dbo.LABOR_TICKET.DESCRIPTION As [REF/DESCRIPTION],

MBUK.dbo.JOURNAL_DIM.VENDOR_ID As VENDOR_ID,

MBUK.dbo.JOURNAL_DIM.USER_ID As USER_ID,

'N/A' As PART_ID,

MBUK.dbo.LABOR_TICKET.EMPLOYEE_ID As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.LABOR_TICKET.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.LABOR_TICKET.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.JOURNAL_DIM Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.JOURNAL_DIM.ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.LABOR_TICKET

On MBUK.dbo.JOURNAL_DIM.TRANSACTION_ID =

MBUK.dbo.LABOR_TICKET.TRANSACTION_ID

Where

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE = 'WIPL' And

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Fin_Dist_Detail' As Data_Table,

MBUK.dbo.FIN_DIST_DETAIL.PROCESS_TYPE As SOURCE_TYPE,

MBUK.dbo.FIN_DIST_DETAIL.SOURCE_ID As SOURCE_ID,

MBUK.dbo.FIN_DIST_DETAIL.GL_ACCOUNT_ID As ACCOUNT_ID,

JnlDimSummary.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.FIN_DIST_DETAIL.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.FIN_DIST_DETAIL.AMOUNT * -1)

Else MBUK.dbo.FIN_DIST_DETAIL.AMOUNT End As Amount,

JnlDimSummary.ACCT_YEAR As ACCT_YEAR,

JnlDimSummary.ACCT_PERIOD As ACCT_PERIOD,

SourceRefs.[Document Date] As DOCUMENT_DATE,

Case

When SourceRefs.Department Is Null And

MBUK.dbo.FIN_DIST_DETAIL.GL_ACCOUNT_ID In ('870000', '870001', '730000',

'730001') Then '95' Else SourceRefs.Department End As DEPARTMENT_ID,

SourceRefs.Project As [PROJECT/WH],

SourceRefs.WBS As WBS,

SourceRefs.[Cost Category] As [Cost Category],

SourceRefs.[Source Ref] As [REF/DESCRIPTION],

SourceRefs.[Vendor/Customer] As VENDOR_ID,

JnlDimSummary.USER_ID As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

'N/A' As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.FIN_DIST_DETAIL Inner Join

(Select

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE,

MBUK.dbo.JOURNAL_DIM.SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID,

MBUK.dbo.JOURNAL_DIM.POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD,

MBUK.dbo.JOURNAL_DIM.ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.USER_ID

From

MBUK.dbo.JOURNAL_DIM

Group By

MBUK.dbo.JOURNAL_DIM.PROCESS_TYPE, MBUK.dbo.JOURNAL_DIM.SOURCE_ID,

MBUK.dbo.JOURNAL_DIM.CURRENCY_ID, MBUK.dbo.JOURNAL_DIM.POSTING_DATE,

MBUK.dbo.JOURNAL_DIM.ACCT_PERIOD, MBUK.dbo.JOURNAL_DIM.ACCT_YEAR,

MBUK.dbo.JOURNAL_DIM.USER_ID) JnlDimSummary

On MBUK.dbo.FIN_DIST_DETAIL.PROCESS_TYPE = JnlDimSummary.PROCESS_TYPE And

MBUK.dbo.FIN_DIST_DETAIL.SOURCE_ID = JnlDimSummary.SOURCE_ID And

MBUK.dbo.FIN_DIST_DETAIL.CURRENCY_ID = JnlDimSummary.CURRENCY_ID Left Join

(Select

'API' As [Source Type],

MBUK.dbo.PAYABLE_LINE.VOUCHER_ID As [Source ID],

MBUK.dbo.PAYABLE_LINE.LINE_NO As [Source Line],

MBUK.dbo.PAYABLE_LINE.REFERENCE As [Source Ref],

MBUK.dbo.PAYABLE_LINE.DEPARTMENT_ID As Department,

MBUK.dbo.PAYABLE.VENDOR_ID As [Vendor/Customer],

MBUK.dbo.PAYABLE_LINE.PROJECT_ID As Project,

MBUK.dbo.PAYABLE_LINE.WBS_CODE As WBS,

MBUK.dbo.PAYABLE_LINE.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.PAYABLE.INVOICE_DATE As [Document Date]

From

MBUK.dbo.PAYABLE_LINE Inner Join

MBUK.dbo.PAYABLE

On MBUK.dbo.PAYABLE_LINE.VOUCHER_ID = MBUK.dbo.PAYABLE.VOUCHER_ID

Union All

Select

'GJ' As [Source Type],

MBUK.dbo.GJ_LINE.GJ_ID As [Source ID],

MBUK.dbo.GJ_LINE.LINE_NO As [Source Line],

MBUK.dbo.GJ_LINE.REFERENCE As [Source Ref],

MBUK.dbo.GJ_LINE.DEPARTMENT_ID As Department,

'N/A' As [Vendor/Customer],

MBUK.dbo.GJ_LINE.PROJECT_ID As Project,

MBUK.dbo.GJ_LINE.WBS_CODE As WBS,

MBUK.dbo.GJ_LINE.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.GJ.GJ_DATE As [Document Date]

From

MBUK.dbo.GJ_LINE Inner Join

MBUK.dbo.GJ

On MBUK.dbo.GJ_LINE.GJ_ID = MBUK.dbo.GJ.ID

Union All

Select

'ARI' As [Source Type],

MBUK.dbo.RECEIVABLE_LINE.INVOICE_ID As [Source ID],

MBUK.dbo.RECEIVABLE_LINE.LINE_NO As [Source Line],

MBUK.dbo.RECEIVABLE_LINE.REFERENCE As [Source Ref],

'N/A' As Department,

MBUK.dbo.RECEIVABLE.CUSTOMER_ID As [Vendor/Customer],

'N/A' As Project,

'N/A' As WBS,

'N/A' As [Cost category],

MBUK.dbo.RECEIVABLE.INVOICE_DATE As [Document Date]

From

MBUK.dbo.RECEIVABLE_LINE Inner Join

MBUK.dbo.RECEIVABLE

On MBUK.dbo.RECEIVABLE_LINE.INVOICE_ID = MBUK.dbo.RECEIVABLE.INVOICE_ID

Union All

Select

'BAJ' As [Source Type],

MBUK.dbo.BANK_ADJ_LINE.ADJUSTMENT_ID As [Source ID],

MBUK.dbo.BANK_ADJ_LINE.LINE_NO As [Source Line],

MBUK.dbo.BANK_ADJ_LINE.REFERENCE As [Source Ref],

'N/A' As Department,

'N/A' As [Vendor/Customer],

'N/A' As Project,

'N/A' As WBS,

'N/A' As [Cost category],

MBUK.dbo.BANK_ADJUSTMENT.TRANSACTION_DATE As [Document Date]

From

MBUK.dbo.BANK_ADJ_LINE Inner Join

MBUK.dbo.BANK_ADJUSTMENT

On MBUK.dbo.BANK_ADJ_LINE.BANK_ACCOUNT_ID =

MBUK.dbo.BANK_ADJUSTMENT.BANK_ACCOUNT_ID And

MBUK.dbo.BANK_ADJ_LINE.ADJUSTMENT_ID =

MBUK.dbo.BANK_ADJUSTMENT.ADJUSTMENT_ID) SourceRefs

On MBUK.dbo.FIN_DIST_DETAIL.SOURCE_ID = SourceRefs.[Source ID] And

MBUK.dbo.FIN_DIST_DETAIL.SOURCE_LINE_NO = SourceRefs.[Source Line]

And MBUK.dbo.FIN_DIST_DETAIL.PROCESS_TYPE = SourceRefs.[Source Type]

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.FIN_DIST_DETAIL.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID

Where

MBUK.dbo.FIN_DIST_DETAIL.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_YEAR As ACCT_YEAR,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.PURC_ORDER_LINE.USER_3 As [REF/DESCRIPTION],

MBUK.dbo.PURCHASE_ORDER.VENDOR_ID As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

MBUK.dbo.PURC_ORDER_LINE.PART_ID As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.PROJECT_SUMMARY.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.PROJECT_SUMMARY.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.PURCHASE_ORDER

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.PURCHASE_ORDER.ID Inner Join

MBUK.dbo.PURC_ORDER_LINE

On MBUK.dbo.PURC_ORDER_LINE.PURC_ORDER_ID = MBUK.dbo.PURCHASE_ORDER.ID

Inner Join

(Select

MBUK.dbo.PURC_ORDER_LINE.PURC_ORDER_ID,

Min(MBUK.dbo.PURC_ORDER_LINE.LINE_NO) As Min_LINE_NO

From

MBUK.dbo.PURC_ORDER_LINE

Group By

MBUK.dbo.PURC_ORDER_LINE.PURC_ORDER_ID) [First PO Line#]

On MBUK.dbo.PURC_ORDER_LINE.PURC_ORDER_ID = [First PO Line#].PURC_ORDER_ID

And MBUK.dbo.PURC_ORDER_LINE.LINE_NO = [First PO Line#].Min_LINE_NO

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE Like 'PURC%'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

[Posting Periods PROJDIST-INVADJ].ACCT_YEAR As ACCT_YEAR,

[Posting Periods PROJDIST-INVADJ].ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.INVENTORY_TRANS.DESCRIPTION As [REF/DESCRIPTION],

'N/A' As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

MBUK.dbo.INVENTORY_TRANS.PART_ID As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.PROJECT_SUMMARY.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.PROJECT_SUMMARY.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.INVENTORY_TRANS

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.INVENTORY_TRANS.TRANSACTION_ID

Inner Join

(Select

[Period 1st PROJDIST-INVADJ].SOURCE_ID,

[Period 1st PROJDIST-INVADJ].DIST_NO,

[Period 1st PROJDIST-INVADJ].ENTRY_NO,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_YEAR,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_PERIOD

From

MBUK.dbo.FINANCIAL_PERIOD Inner Join

(Select

MBUK.dbo.PROJECT_DIST.SOURCE_ID,

MBUK.dbo.PROJECT_DIST.DIST_NO,

MBUK.dbo.PROJECT_DIST.ENTRY_NO,

Convert(datetime,'01' + '-' +

Convert(varchar,Month(MBUK.dbo.PROJECT_DIST.POSTING_DATE)) + '-' +

Convert(varchar,Year(MBUK.dbo.PROJECT_DIST.POSTING_DATE)),103) As

[Period 1st PROJDIST]

From

MBUK.dbo.PROJECT_DIST

Where

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE =

'INV ADJ') [Period 1st PROJDIST-INVADJ]

On [Period 1st PROJDIST-INVADJ].[Period 1st PROJDIST] =

MBUK.dbo.FINANCIAL_PERIOD.BEGIN_DATE

Where

MBUK.dbo.FINANCIAL_PERIOD.CALENDAR_ID =

'MBL') [Posting Periods PROJDIST-INVADJ]

On MBUK.dbo.PROJECT_DIST.SOURCE_ID =

[Posting Periods PROJDIST-INVADJ].SOURCE_ID And

MBUK.dbo.PROJECT_DIST.DIST_NO = [Posting Periods PROJDIST-INVADJ].DIST_NO

And

MBUK.dbo.PROJECT_DIST.ENTRY_NO = [Posting Periods PROJDIST-INVADJ].ENTRY_NO

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE = 'INV ADJ'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_YEAR As ACCT_YEAR,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.LABOR_TICKET.DESCRIPTION As [REF/DESCRIPTION],

'N/A' As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

'N/A' As PART_ID,

MBUK.dbo.LABOR_TICKET.EMPLOYEE_ID As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.LABOR_TICKET.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.LABOR_TICKET.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Left Join

MBUK.dbo.LABOR_TICKET

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.LABOR_TICKET.TRANSACTION_ID

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE = 'WIP LABOR'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_YEAR As ACCT_YEAR,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.INVENTORY_TRANS.DESCRIPTION As [REF/DESCRIPTION],

'N/A' As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

MBUK.dbo.INVENTORY_TRANS.PART_ID As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.INVENTORY_TRANS.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.PROJECT_SUMMARY.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.INVENTORY_TRANS

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.INVENTORY_TRANS.TRANSACTION_ID

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE = 'WIP ISSUE'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_YEAR As ACCT_YEAR,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

MBUK.dbo.GJ.DESCRIPTION As [REF/DESCRIPTION],

'N/A' As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.PROJECT_SUMMARY.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.PROJECT_SUMMARY.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.GJ

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.GJ.ID

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE = 'GJ LINE'

Union All

Select

'Project Dist' As Data_Table,

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE As SOURCE_TYPE,

MBUK.dbo.PROJECT_DIST.SOURCE_ID As SOURCE_ID,

MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.PROJECT_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.PROJECT_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.PROJECT_DIST.AMOUNT * -1)

Else MBUK.dbo.PROJECT_DIST.AMOUNT End As Amount,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_YEAR As ACCT_YEAR,

MBUK.dbo.PROJECT_SUMMARY.ACCOUNT_PERIOD As ACCT_PERIOD,

MBUK.dbo.PROJECT_DIST.CREATE_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_SUMMARY.DEPARTMENT_ID As DEPARTMENT_ID,

MBUK.dbo.PROJECT_DIST.PROJECT_ID As [PROJECT/WH],

MBUK.dbo.PROJECT_SUMMARY.WBS_CODE As WBS,

MBUK.dbo.PROJECT_SUMMARY.COST_CATEGORY_ID As [Cost Category],

'N/A' As [REF/DESCRIPTION],

MBUK.dbo.PAYABLE.VENDOR_ID As VENDOR_ID,

MBUK.dbo.PROJECT_DIST.USER_ID As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

MBUK.dbo.PROJECT_SUMMARY.WORKORDER_BASE_ID As WORK_ORDER,

MBUK.dbo.PROJECT_SUMMARY.RESOURCE_ID As RESOURCE_ID

From

MBUK.dbo.PROJECT_DIST Left Join

MBUK.dbo.PROJECT_SUMMARY

On MBUK.dbo.PROJECT_DIST.PROJECT_SUMMARY_ID = MBUK.dbo.PROJECT_SUMMARY.ID

Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.PROJECT_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.PAYABLE

On MBUK.dbo.PROJECT_DIST.SOURCE_ID = MBUK.dbo.PAYABLE.VOUCHER_ID

Where

MBUK.dbo.ACCOUNT.TYPE = 'E' And

MBUK.dbo.PROJECT_DIST.SOURCE_TYPE = 'PAYBL LINE'

Union All

Select

'Revalue_Dist' As Data_Table,

'REVAL' As SOURCE_TYPE,

MBUK.dbo.REVALUE_DIST.REVALUE_ID As SOURCE_ID,

MBUK.dbo.REVALUE_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.REVALUE_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.REVALUE_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.REVALUE_DIST.AMOUNT * -1)

Else MBUK.dbo.REVALUE_DIST.AMOUNT End As Amount,

[Posting Periods].ACCT_YEAR As ACCT_YEAR,

[Posting Periods].ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.REVALUE_DIST.CREATE_DATE As DOCUMENT_DATE,

'95' As DEPARTMENT_ID,

'N/A' As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

'Revaluation' As [REF/DESC],

'N/A' As VENDOR_ID,

'N/A' As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

'N/A' As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.REVALUE_DIST Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.REVALUE_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

(Select

[Period 1st].ID,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_YEAR,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_PERIOD

From

(Select Distinct

MBUK.dbo.REVALUE.ID,

Convert(datetime,'01' + '-' +

Convert(varchar,Month(MBUK.dbo.REVALUE.POSTING_DATE)) + '-' +

Convert(varchar,Year(MBUK.dbo.REVALUE.POSTING_DATE)),103) As [Period 1st]

From

MBUK.dbo.REVALUE) [Period 1st] Inner Join

MBUK.dbo.FINANCIAL_PERIOD

On MBUK.dbo.FINANCIAL_PERIOD.BEGIN_DATE = [Period 1st].[Period 1st])

[Posting Periods]

On MBUK.dbo.REVALUE_DIST.REVALUE_ID = [Posting Periods].ID

Where

MBUK.dbo.ACCOUNT.TYPE = 'E'

Union All

Select

'Revenue_Dist' As Data_Table,

'REV/COS' As SOURCE_TYPE,

Convert(varchar,MBUK.dbo.REVENUE_DIST.ID) As SOURCE_ID,

MBUK.dbo.REVENUE_DIST.GL_ACCOUNT_ID As ACCOUNT_ID,

MBUK.dbo.REVENUE_DIST.POSTING_DATE As POSTING_DATE,

Case MBUK.dbo.REVENUE_DIST.AMOUNT_TYPE

When 'CR' Then (MBUK.dbo.REVENUE_DIST.AMOUNT * -1)

Else MBUK.dbo.REVENUE_DIST.AMOUNT End As Amount,

[Posting Periods - Revenue].ACCT_YEAR As ACCT_YEAR,

[Posting Periods - Revenue].ACCT_PERIOD As ACCT_PERIOD,

MBUK.dbo.REVENUE_DIST.TRANSACTION_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_INFO.DIVISION_ID + 'M' As DEPARTMENT_ID,

MBUK.dbo.REVENUE_DIST.PROJECT_ID As [PROJECT/WH],

'N/A' As WBS,

'N/A' As [Cost Category],

MBUK.dbo.REVENUE_DETAIL.DESCRIPTION As [REF/DESC],

'N/A' As VENDOR_ID,

MBUK.dbo.REVENUE_DIST.USER_ID As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

'N/A' As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.REVENUE_DIST Inner Join

MBUK.dbo.ACCOUNT

On MBUK.dbo.REVENUE_DIST.GL_ACCOUNT_ID = MBUK.dbo.ACCOUNT.ID Inner Join

MBUK.dbo.REVENUE_DETAIL

On MBUK.dbo.REVENUE_DIST.ID = MBUK.dbo.REVENUE_DETAIL.REV_DIST_ID Inner Join

(Select

[Period 1st (Revenue)].ID,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_YEAR,

MBUK.dbo.FINANCIAL_PERIOD.ACCT_PERIOD

From

MBUK.dbo.FINANCIAL_PERIOD Inner Join

(Select Distinct

MBUK.dbo.REVENUE_DIST.ID,

Convert(datetime,'01' + '-' +

Convert(varchar,Month(MBUK.dbo.REVENUE_DIST.POSTING_DATE)) + '-' +

Convert(varchar,Year(MBUK.dbo.REVENUE_DIST.POSTING_DATE)),103) As

[Period 1st]

From

MBUK.dbo.REVENUE_DIST) [Period 1st (Revenue)]

On [Period 1st (Revenue)].[Period 1st] =

MBUK.dbo.FINANCIAL_PERIOD.BEGIN_DATE

Where

MBUK.dbo.FINANCIAL_PERIOD.CALENDAR_ID = 'MBL') [Posting Periods - Revenue]

On MBUK.dbo.REVENUE_DIST.ID = [Posting Periods - Revenue].ID Inner Join

MBUK.dbo.PROJECT_INFO

On MBUK.dbo.REVENUE_DIST.PROJECT_ID = MBUK.dbo.PROJECT_INFO.ID

Where

MBUK.dbo.REVENUE_DIST.CURRENCY_ID = 'GBP' And

MBUK.dbo.ACCOUNT.TYPE In ('E', 'R')

Union All

Select

'MBRevRec' As Data_table,

'MABEYREV' As SOURCE_TYPE,

MBUK.dbo.MBREVREC_IMPORT.GJ_ID As SOURCE_ID,

'100000' As ACCOUNT_ID,

MBUK.dbo.MBREVREC_IMPORT.POSTING_DATE As POSTING_DATE,

-MBUK.dbo.MBREVREC.Revenue As Amount,

MBUK.dbo.MBREVREC.Accounting_Year As ACCT_YEAR,

MBUK.dbo.MBREVREC.Accounting_Period As ACCT_PERIOD,

MBUK.dbo.MBREVREC_IMPORT.GJ_DATE As DOCUMENT_DATE,

MBUK.dbo.PROJECT_INFO.DIVISION_ID + 'M' As DEPARTMENT_ID,

MBUK.dbo.MBREVREC.Project_ID As [PROJECT/WH],

MBUK.dbo.MBREVREC.WBS_Code As WBS,

MBUK.dbo.MBREVREC.Cost_Group As [Cost Category],

'Mabey Revenue' As [REF/DESC],

'N/A' As VENDOR_ID,

'N/A' As USER_ID,

'N/A' As PART_ID,

'N/A' As EMPLOYEE_ID,

'N/A' As INDIRECT_ID,

'N/A' As WORK_ORDER,

'N/A' As RESOURCE_ID

From

MBUK.dbo.MBREVREC Inner Join

MBUK.dbo.MBREVREC_IMPORT

On MBUK.dbo.MBREVREC.IMPORT_ID = MBUK.dbo.MBREVREC_IMPORT.IMPORT_ID

Inner Join

MBUK.dbo.PROJECT_INFO

On MBUK.dbo.MBREVREC.Project_ID = MBUK.dbo.PROJECT_INFO.ID

unknown

Wow, thanks for all the info and incite. I'm going to have to work through this one on our end. I really appreciate your time in answering. Thanks again!

Joshua

Copyright © 2025 Infor. All rights reserved.