Skip to main content
Skip table of contents

JAMIS Prime Pre-Mapped Data Warehouse Tables / GIs

Click here for learning about Tableau, Power BI, and MS Excel templates for JAMIS Prime (Acumatica).

Tables

The following pre-mapped ETL+ tables (or SQL Views) are available for JAMIS Prime v7.0 or newer as of 07/2023. This applies to JAMIS Prime on-premises, private cloud, or public cloud (OData v3 and v4). Subject to change without notice.

Source

Table Name

Acumatica

Account

Acumatica

AccountClass

Acumatica

Address

Acumatica

AMBomItem

Acumatica

AMBomMatl

Acumatica

AMBomOper

Acumatica

AMBomOvhd

Acumatica

AMBomStep

Acumatica

AMOrderType

Acumatica

AMProdItem

Acumatica

AMProdMatl

Acumatica

AMProdOper

Acumatica

AMProdOvhd

Acumatica

AMProdStep

Acumatica

AMProdTool

Acumatica

AMProdTotal

Acumatica

APInvoice

Acumatica

APRegister

Acumatica

ARInvoice

Acumatica

ARRegister

Acumatica

ARTran

Acumatica

BAccount

Acumatica

Branch

Acumatica

Company

Acumatica

Contact

Acumatica

CRActivity

Acumatica

CRAddress

Acumatica

CRCampaign

Acumatica

CRContact

Acumatica

CROpportunity

Acumatica

CROpportunityProbability

Acumatica

CROpportunityRevision

Acumatica

CRSMEmail

Acumatica

CSAnswers

Acumatica

Customer

Acumatica

CustomerClass

Acumatica

EPEmployee

Acumatica

FinPeriod

Acumatica

GLHistory

Acumatica

GLHistoryByPeriod

Acumatica

GLTran

Acumatica

INItemClass

Acumatica

INItemCostHist

Acumatica

INItemCostHistByPeriod

Acumatica

INItemStats

Acumatica

INLocation

Acumatica

INLocationStatus

Acumatica

INRegister

Acumatica

INSite

Acumatica

INTran

Acumatica

InventoryItem

Acumatica

InventoryItemAMExtension

JAMIS Prime

EPEmployee

JAMIS Prime

JBPDetail

JAMIS Prime

JBPDetailValues

JAMIS Prime

JBPForecastBurdenAmountDetail

JAMIS Prime

JBPMaster

JAMIS Prime

JBPResourcePersonnel

JAMIS Prime

JBPType

JAMIS Prime

JCSOBS

JAMIS Prime

JCSOBSLevel

JAMIS Prime

JPMBillingRule

JAMIS Prime

JPMBillRevSummary

JAMIS Prime

JPMBurden

JAMIS Prime

JPMBurdenAmountType

JAMIS Prime

JPMBurdenPool

JAMIS Prime

JPMContract

JAMIS Prime

JPMContractClass

JAMIS Prime

JPMCostClass

JAMIS Prime

JPMCostClassType

JAMIS Prime

JPMCostElement

JAMIS Prime

JPMCosts

JAMIS Prime

JPMCostTypeCode

JAMIS Prime

JPMInvoiceHeader

JAMIS Prime

JPMInvoiceRule

JAMIS Prime

JPMJobCostBillingStatus

JAMIS Prime

JPMJobCostBurdenDetail

JAMIS Prime

JPMJobCostBurdenType

JAMIS Prime

JPMJobCostRevenueStatus

JAMIS Prime

JPMJobCostTran

JAMIS Prime

JPMLaborCategory

JAMIS Prime

JPMProjectBilling

JAMIS Prime

JPMSubContract

JAMIS Prime

JPMSubContractDetail

JAMIS Prime

JState

JAMIS Prime

JVendor

Acumatica

Ledger

Acumatica

Location

Acumatica

PMAccountGroup

Acumatica

PMBudget

Acumatica

PMProforma

Acumatica

PMProformaLine

Acumatica

PMProject

Acumatica

PMRegister

Acumatica

PMTask

Acumatica

PMTran

Acumatica

POLine

Acumatica

POOrder

Acumatica

Salesperson

Acumatica

Segment

Acumatica

SegmentValue

Acumatica

SOAddress

Acumatica

SOLine

Acumatica

SOOrder

Acumatica

SOOrderType

Acumatica

SOShipment

Acumatica

Sub

Acumatica

Users

Acumatica

Vendor

Data Warehouse ACM

4_Today

Data Warehouse ACM

5_Date

Data Warehouse ACM

6_DatePeriod

Data Warehouse ACM

GLz_02_AccountOverrides

Data Warehouse ACM

GLz_03_SubAccountSegments

Data Warehouse ACM

GLz_10_C3_Detail

Data Warehouse ACM

GLz_12_C1_MajorTotal

Data Warehouse ACM

GLz_14_C15_MinorTotal

Data Warehouse ACM

GLz_16_C2_MinorTotal

Data Warehouse ACM

GLz_50_Budget

Data Warehouse ACM

GLz_60_History_PL_YTD

Data Warehouse ACM

GLz_70_CashFlow

Data Warehouse ACM

INz_10_Inventory_Planning

Data Warehouse JAMIS

JDW-01-FactTranActRaw_v

Data Warehouse JAMIS

JDW-02-FactTranActTar_v

Data Warehouse JAMIS

JDW-03-FactTranActBill_v

Data Warehouse JAMIS

JDW-04-FactTranActRev_v

Data Warehouse JAMIS

JDW-05-FactBurdActRaw_v

Data Warehouse JAMIS

JDW-06-FactBurdActTar_v

Data Warehouse JAMIS

JDW-07-FactBurdActBill_v

Data Warehouse JAMIS

JDW-08-FactBurdActRev_v

Data Warehouse JAMIS

JDW-09-FactTranPlnRaw_v

Data Warehouse JAMIS

JDW-10-FactTranPlnTar_v

Data Warehouse JAMIS

JDW-11-FactTranPlnBill_v

Data Warehouse JAMIS

JDW-12-FactTranPlnRev_v

Data Warehouse JAMIS

JDW-13-FactBurdPlnRaw_v

Data Warehouse JAMIS

JDW-14-FactBurdPlnTar_v

Data Warehouse JAMIS

JDW-15-FactBurdPlnBill_v

Data Warehouse JAMIS

JDW-16-FactBurdPlnRev_v

Data Warehouse JAMIS

JDW-17-FactFeePlnRev_v

Data Warehouse JAMIS

JDW-18-FactFeePlnBill_v

Data Warehouse ACM

MFGz_BOM01_Item

Data Warehouse ACM

MFGz_BOM02_Component

Data Warehouse ACM

MFGz_BOM03_Header

Data Warehouse ACM

MFGz_BOM10_Level0

Data Warehouse ACM

MFGz_BOM11_Level1

Data Warehouse ACM

MFGz_BOM12_Level2

Data Warehouse ACM

MFGz_BOM13_Level3

Data Warehouse ACM

MFGz_BOM14_Level4

Data Warehouse ACM

MFGz_BOM15_Level5

Data Warehouse ACM

MFGz_ProdOrder01_FixedOH

Data Warehouse ACM

MFGz_ProdOrder02_Header

Data Warehouse ACM

MFGz_ProdOrder03_Labor

Data Warehouse ACM

MFGz_ProdOrder04_Mach

Data Warehouse ACM

MFGz_ProdOrder05_Matl

Data Warehouse ACM

MFGz_ProdOrder06_Tool

Data Warehouse ACM

MFGz_ProdOrder07_ToolDetail

Data Warehouse ACM

MFGz_ProdOrder08_VariableOH

Notes:

  • Some of the above tables and views will only be deployed using customized SOWs or via services.

  • All DAC tables can be extracted into the data warehouse using OData.

  • All JAMIS Prime SQL tables can be extracted when you have direct access to JAMIS Prime’s database.

  • We only recommend extracting tables and columns that are important for your reporting purposes.

JAMIS Prime MS SQL, OData v3, OData v4

The out-of-the-box ETL+ and data warehouse mappings have been designed to let users easily extract the data from JAMIS Prime’s MS SQL database when available, and/or via OData v3, and/or OData v4.

There are some pros and cons to each of these extraction methods. Most deployments rely on one extraction method only. Example of using more than one method: clients with an on-prem JAMIS Prime will find the SQL to SQL be the fastest extraction approach. However, there are some DAC tables not readily available in SQL, and it can be easier to just pull them via OData.

Source: JAMIS Prime: JAMIS Prime tables that have been pre-mapped in ETL+. They are part of DataSelf Step 1 - mirroring JAMIS Prime raw data in the data warehouse. This pre-mapping includes:

  • Popular tables and columns for BI and reporting. We only map popular tables and columns to keep the extraction efficient and less taxing on resources. It’s easy to add more tables and columns via ETL+.

  • Many columns have their data types adjusted for reporting purposes. For instance, OData extractions set string columns to varchar(max) which can’t be used for SQL table linking - these columns are changed to varchar(50) or others (users can easily change the data types via ETL+).

  • Large tables come pre-configured with delta refresh set up. This includes delta Load Type settings, as well as column data type formatting and indexing.

Source: Data Warehouse: These are special tables that provide enhanced reporting value. Summary:

  • 4_ 5_ and 6_ tables for advanced period analytics.

  • GLz_ tables for performing GL Trial Balances, P&L, B/S, and Cash Flow.

  • INz_ table(s) for inventory planning (combining Inventory On Hand Today, Open POs/WOs, Open SOs, and Sales projection).

  • MFGz tables for manufacturing BOM and Production Order reporting.

Table Columns Example

DataSelf ETL+ templates include adjustments to table columns' data types and indexes. Click here to learn more.

The following is an example of a JAMIS Prime table and its column names, indexes, and formatting in ETL+.

Column green icons indicate the data type and/or index definition have been set or changed in ETL+. Ex.: BAccountID (integer) has been set to an index (instead of the default AcctCD varchar index from OData).

Delta Load Example

DataSelf ETL+ templates include delta load configuration for popular large tables. This dramatically reduces the time to refresh the data warehouse. ETL+ Table Load Types.

The following is an example of ARTran using the Upsert feature.

Table Linking Example

The following is an example of how JAMIS Prime _AR_Aging_Today tables are linked in DataSelf’s out-of-the-box templates. This linking applies the same way for Tableau data sources, Power BI data sets, Excel, and other reporting tools.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.