Deltek Vision Connector
Intro
Deltek Vision is an ERP solution specifically for the project-focused firms, providing visibility into project budgets, schedules, and other project data. Use Domo's Deltek connector to retrieve data about campaigns, opportunities, employee expenses and repayments, and other Deltek entities. To learn more about the Deltek API, visit their page ( https://deltek.custhelp.com/app/answers/detail/a_id/62647/kw/web%20services%20api ).
You connect to your Deltek account in the Data Center. This topic discusses the fields and menus that are specific to the Deltek connector user interface. General information for adding DataSets, setting update schedules, and editing DataSet information is discussed in Adding a DataSet Using a Data Connector.
You can also connect to Deltek through the database that sits on premise on your network. In this case, use of SQL is required.
Prerequisites
To connect to your Deltek account and create a DataSet, you must have the following:
-
Your Deltek username
-
Your Deltek password
-
The domain name of your Deltek instance (for instance, if the instance was mycompany.deltek.com, the domain name would be mycompany)
-
The name of the database you want to retrieve data from
-
Your Deltek host name
-
Your Deltek host port number
Connecting to Your Deltek Account
This section enumerates the options in the Credentials and Details panes in the Deltek Connector page. The components of the other panes in this page, Scheduling and Name & Describe Your DataSet, are universal across most connector types and are discussed in greater length in Adding a DataSet Using a Data Connector.
Credentials Pane
This pane contains fields for entering credentials to connect to your Deltek account. The following table describes what is needed for each field:
Field |
Description |
---|---|
User | Enter your Deltek username. |
Password | Enter your Deltek password. |
Domain | Enter your Deltek domain name. |
Database | Enter the name of the Deltek database you want to retrieve data from. |
Host | Enter your Deltek host name. |
Port | Enter the port number of your Deltek host. |
Once you have entered valid Deltek credentials, you can use the same account any time you go to create a new Deltek DataSet. You can manage connector accounts in the Accounts tab in the Data Center. For more information about this tab, see Managing User Accounts for Connectors.
Details Pane
This pane contains a primary Reports menu, along with various other menus which may or may not appear depending on the report type you select.
Menu |
Description |
||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Report |
Select the Deltek report you want to run. The following reports are available:
|
||||||||||||||||||||||||||||||||||||||||||||||
Rolling Months of Data | Enter the number of months back you want to pull data for. |
Other Panes
For information about the remaining sections of the connector interface, including how to configure scheduling, retry, and update options, see Adding a DataSet Using a Data Connector.
Queries
The Deltek Vision connector uses the following queries:
APVouchers
create index cbatchindex on APVouchersControl(Batch);
create index mbatchindex on APVouchersMaster(Batch);
create index mmasterkeyindex on APVouchersMaster(MasterPKey);
create index dbatchindex on APVouchersDetail(Batch);
create index dmasterkeyindex on APVouchersDetail(MasterPKey);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.DefaultLiab, c.DefaultBank, c.DefaultDate, c.DefaultTaxCode, c.PostPeriod, c.PostSeq, c.Company, c.DefaultCurrencyCode, c.DefaultTax2Code, c.SubmittedBy, c.SubmittedDate, m.Vendor, m.InvoiceDate, m.Invoice, m.TransDate, m.LiabCode, m.BankCode, m.PayTerms, m.PayDate, m.Seq as MasterSeq, m.Voucher, m.CurrencyCode, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.BarCode, m.PaymentExchangeOverrideMethod, m.PaymentExchangeOverrideDate, m.PaymentExchangeOverrideRate, m.PaymentCurrencyCode, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.PKey, d.Seq as DetailSeq, d.Description, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.SuppressBill, d.TaxCode, d.NetAmount, d.TaxAmount, d.CurrencyExchangeOverrideRate, d.PONumber, d.PaymentExchangeRate, d.PaymentAmount, d.PaymentExchangeInfo, d.ExpenseCode, d.Tax2Code, d.Tax2Amount, d.CompoundTax from APVouchersControl c join APVouchersMaster m on c.Batch = m.Batch join APVouchersDetail d on m.Batch = d.Batch and m.MasterPKey = d.MasterPKey
APDisbursements
create index cbatchindex on APDisbursementsControl(Batch);
create index mbatchindex on APDisbursementsMaster(Batch);
create index mmasterkeyindex on APDisbursementsMaster(MasterPKey);
create index dbatchindex on APDisbursementsDetail(Batch);
create index dmasterkeyindex on APDisbursementsDetail(MasterPKey);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.DefaultLiab, c.DefaultBank, c.DefaultDate, c.DefaultTaxCode, c.PostPeriod, c.PostSeq, c.Company, c.DefaultCurrencyCode, c.DefaultTax2Code, c.SubmittedBy, c.SubmittedDate, m.Vendor, m.InvoiceDate, m.Invoice, m.TransDate, m.LiabCode, m.BankCode, m.PayTerms, m.PayDate, m.Posted, m.Seq as MasterSeq, m.Voucher, m.CurrencyCode, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.BarCode, m.PaymentExchangeOverrideMethod, m.PaymentExchangeOverrideDate, m.PaymentExchangeOverrideRate, m.PaymentCurrencyCode, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.PKey, d.Seq as DetailSeq, d.Description, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.SuppressBill, d.TaxCode, d.NetAmount, d.TaxAmount, d.CurrencyExchangeOverrideRate, d.PONumber, d.PaymentExchangeRate, d.PaymentAmount, d.PaymentExchangeInfo, d.ExpenseCode, d.Tax2Code, d.Tax2Amount, d.CompoundTax from APDisbursementsControl c join APDisbursementsMaster m on c.Batch = m.Batch join APDisbursementsDetail d on m.Batch = d.Batch and m.MasterPKey = d.MasterPKey
CashDisbursements
create index cbatchindex on CashDisbursementsControl(Batch);
create index mbatchindex on CashDisbursementsMaster(Batch);
create index mchecknoindex on CashDisbursementsMaster(CheckNo);
create index dbatchindex on CashDisbursementsDetail(Batch);
create index dchecknoindex on CashDisbursementsDetail(CheckNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.DefaultBank, c.DefaultTaxCode, c.PostPeriod, c.PostSeq, c.Company, c.DefaultTax2Code, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.BankCode, m.Payee, m.Posted, m.Seq as MasterSeq, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.CheckNo, d.PKey, d.Seq as DetailSeq, d.Description, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.TaxCode, d.NetAmount, d.TaxAmount, d.CurrencyExchangeOverrideRate, d.LinkCompany, d.Tax2Code, d.Tax2Amount, d.CompoundTax from CashDisbursementsControl c join CashDisbursementsMaster m on c.Batch = m.Batch join CashDisbursementsDetail d on m.Batch = d.Batch and m.CheckNo = d.CheckNo
CashReceipts
create index cbatchindex on CashReceiptsControl(Batch);
create index mbatchindex on CashReceiptsMaster(Batch);
create index mmasterkeyindex on CashReceiptsMaster(RefNo);
create index dbatchindex on CashReceiptsDetail(Batch);
create index dmasterkeyindex on CashReceiptsDetail(RefNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.DefaultBank, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.BankCode, m.TransComment, m.Posted, m.Seq as MasterSeq, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.RefNo, d.PKey, d.Seq as DetailSeq, d.Description, d.WBS1, d.WBS2, d.WBS3, d.Invoice, d.Account, d.Amount, d.Interest, d.TaxCode, d.TaxBasis, d.Retainer, d.CurrencyExchangeOverrideRate, d.SourceAmount, d.SourceExchangeInfo, d.LinkCompany from CashReceiptsControl c join CashReceiptsMaster m on c.Batch = m.Batch join CashReceiptsDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo
EmployeeExpenses
create index cbatchindex on EmployeeExpensesControl(Batch);
create index mbatchindex on EmployeeExpensesMaster(Batch);
create index mmasterkeyindex on EmployeeExpensesMaster(RefNo);
create index dbatchindex on EmployeeExpensesDetail(Batch);
create index dmasterkeyindex on EmployeeExpensesDetail(RefNo);
select c.Recurring, c.Creator, c.Period", c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.DefaultTaxCode, c.AdvanceAmount, c.Company, c.DefaultCurrencyCode, c.DefaultTax2Code, c.SubmittedBy, c.SubmittedDate, m.Employee, m.ReportDate, m.ReportName, m.Posted, m.Seq as MasterSeq, m.AdvanceAmount, m.BarCode, m.DefaultCurrencyCode, m.PaymentExchangeOverrideMethod, m.PaymentExchangeOverrideDate, m.PaymentExchangeOverrideRate, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.PKey, d.Seq as DetailSeq, d.TransDate, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.Description, d.SuppressBill, d.TaxCode, d.NetAmount, d.TaxAmount, d.PaymentExchangeRate, d.PaymentAmount, d.PaymentExchangeInfo, d.CurrencyExchangeOverrideRate, d.CurrencyCode, d.Tax2Code, d.Tax2Amount, d.CompoundTax from EmployeeExpensesControl c join EmployeeExpensesMaster m on c.Batch = m.Batch join EmployeeExpensesDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo
Invoices
create index cbatchindex on InvoicesControl(Batch);
create index mbatchindex on InvoicesMaster(Batch);
create index minvoiceindex on InvoicesMaster(Invoice);
create index mwbs1index on InvoicesMaster(WBS1);
create index mwbs2index on InvoicesMaster(WBS2);
create index mwbs3index on InvoicesMaster(WBS3);
create index dbatchindex on InvoicesDetail(Batch);
create index dinvoiceindex on InvoicesDetail(Invoice);
create index dwbs1index on InvoicesDetail(WBS1);
create index dwbs2index on InvoicesDetail(WBS2);
create index dwbs3index on InvoicesDetail(WBS3);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.RetTotal, c.DefaultTaxCode, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.TransComment, m.Posted, m.Seq as MasterSeq, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.CurrencyExchangeOverrideRate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.Invoice, d.WBS1, d.WBS2, d.WBS3, d.PKey, d.Seq as DetailSeq, d.InvoiceSection, d.Account, d.Amount, d.RetAmount, d.TaxCode, d.TaxBasis, d.Retainer, d.LinkWBS1, d.LinkWBS2, d.LinkWBS3 from InvoicesControl c join InvoicesMaster m on c.Batch = m.Batch join InvoicesDetail d on m.Batch = d.Batch and m.Invoice = d.Invoice and m.WBS1 = d.WBS1 and m.WBS2 = d.WBS2 and m.WBS3 = d.WBS3
JournalEntries
create index cbatchindex on JournalEntriesControl(Batch);
create index mbatchindex on JournalEntriesMaster(Batch);
create index mrefnoindex on JournalEntriesMaster(RefNo);
create index dbatchindex on JournalEntriesDetail(Batch);
create index drefnoindex on JournalEntriesDetail(RefNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.Company, c.DefaultCurrencyCode, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.Type, m.Description as MasterDescription, m.Posted, m.Seq as MasterSeq, m.UpdateBilled, m.CurrencyCode, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.RefNo, d.PKey, d.Seq as DetailSeq, d.WBS1, d.WBS2, d.WBS3, d.Account, d.DebitAmount, d.CreditAmount, d.Description as DetailDescription, d.TaxCode, d.TaxType, d.TaxDebitAmount, d.TaxCreditAmount, d.SuppressBill, d.CurrencyExchangeOverrideRate, d.LinkCompany, d.Tax2Code, d.Tax2DebitAmount, d.Tax2CreditAmount, d.DebitAmountFunctionalCurrency, d.CreditAmountFunctionalCurrency, d.DebitAmountProjectCurrency, d.CreditAmountProjectCurrency, d.CompoundTax from JournalEntriesControl c join JournalEntriesMaster m on c.Batch = m.Batch join JournalEntriesDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo
LaborAdjustments
create index cbatchindex on LaborAdjustmentsControl(Batch);
create index mbatchindex on LaborAdjustmentsMaster(Batch);
create index memployeeindex on LaborAdjustmentsMaster(Employee);
create index dbatchindex on LaborAdjustmentsDetail(Batch);
create index demployeeindex on LaborAdjustmentsDetail(Employee);
select c.Recurring, c.Creator, c.Period, c.StartDate, c.EndDate, c.RegHrsTotal, c.OvtHrsTotal, c.RegAmtTotal, c.OvtAmtTotal, c.BillExtTotal, c.SpecialOvtHrsTotal, c.SpecialOvtAmtTotal, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.Posted, m.Seq as MasterSeq, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.Employee, d.PKey, d.Seq as DetailSeq, d.TransDate, d.WBS1, d.WBS2, d.WBS3, d.LaborCode, d.RegHrs, d.OvtHrs, d.RegAmt, d.OvtAmt, d.BillExt, d.SuppressBill, d.TransComment, d.SpecialOvtHrs, d.SpecialOvtAmt, d.BillCategory, d.LinkCompany from LaborAdjustmentsControl c join LaborAdjustmentsMaster m on c.Batch = m.Batch join LaborAdjustmentsDetail d on m.Batch = d.Batch and m.Employee = d.Employee
MiscellaneousExpenses
create index cbatchindex on MiscellaneousExpensesControl(Batch);
create index mbatchindex on MiscellaneousExpensesMaster(Batch);
create index mrefnoindex on MiscellaneousExpensesMaster(RefNo);
create index dbatchindex on MiscellaneousExpensesDetail(Batch);
create index drefnoindex on MiscellaneousExpensesDetail(RefNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.Company, c.DefaultCurrencyCode, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.Description as MasterDescription, m.Posted, m.Seq as MasterSeq, m.CurrencyCode, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.RefNo, d.PKey, d.Seq as DetailSeq, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.Description as DetailDescription, d.CurrencyExchangeOverrideRate from MiscellaneousExpensesControl c join MiscellaneousExpensesMaster m on c.Batch = m.Batch join MiscellaneousExpensesDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo
PrintandReproductions
create index cbatchindex on PrintandReproductionsControl(Batch);
create index mbatchindex on PrintandReproductionsMaster(Batch);
create index mrefnoindex on PrintandReproductionsMaster(RefNo);
create index dbatchindex on PrintandReproductionsDetail(Batch);
create index drefnoindex on PrintandReproductionsDetail(RefNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.Company, c.DefaultCurrencyCode, c.SubmittedBy, c.SubmittedDate, m.TransDate, m.Description as MasterDescription, m.Posted, m.Seq as MasterSeq, m.CurrencyCode, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.RefNo, d.PKey, d.Seq as DetailSeq, d.Originals, d.Copies, d.WBS1, d.WBS2, d.WBS3, d.Account, d.Amount, d.Description as DetailDescription, d.CurrencyExchangeOverrideRate from PrintandReproductionsControl c join PrintandReproductionsMaster m on c.Batch = m.Batch join PrintandReproductionsDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo
Timesheets
create index cbatchindex on TimesheetsControl(Batch);
create index mbatchindex on TimesheetsMaster(Batch);
create index memployeeindex on TimesheetsMaster(Employee);
create index dbatchindex on TimesheetsDetail(Batch);
create index demployeeindex on TimesheetsDetail(Employee);
select c.Recurring, c.Creator, c.Period, c.StartDate, c.EndDate, c.RegHrsTotal, c.OvtHrsTotal, c.SpecialOvtHrsTotal, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.Posted, m.Seq as MasterSeq, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.Employee, d.PKey, d.Seq as DetailSeq, d.TransDate, d.WBS1, d.WBS2, d.WBS3, d.LaborCode, d.RegHrs, d.OvtHrs, d.TransComment, d.SpecialOvtHrs, d.BillCategory, d.Locale, d.RegAmt, d.OvtAmt, d.SpecialOvtAmt, d.ExchangeInfo, d.RegAmtProjectCurrency, d.OvtAmtProjectCurrency, d.SpecialOvtAmtProjectCurrency, d.ProjectExchangeInfo, d.BillExt, d.OvtPct, d.SpecialOvtPct, d.Rate, d.OvtRate, d.SpecialOvtRate, d.RateProjectCurrency, d.OvtRateProjectCurrency, d.SpecialOvtRateProjectCurrency from TimesheetsControl c join TimesheetsMaster m on c.Batch = m.Batch join TimesheetsDetail d on m.Batch = d.Batch and m.Employee = d.Employee
Units
create index cbatchindex on UnitsControl(Batch);
create index mbatchindex on UnitsMaster(Batch);
create index mmasterkeyindex on UnitsMaster(Unit);
create index dbatchindex on UnitsDetail(Batch);
create index dmasterkeyindex on UnitsDetail(Unit);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.Posted, m.Seq as MasterSeq, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.Unit, d.PKey, d.Seq as DetailSeq, d.TransDate, d.WBS1, d.WBS2, d.WBS3, d.TableNo, d.Account, d.Description, d.Quantity, d.CurrencyExchangeOverrideRate from UnitsControl c join UnitsMaster m on c.Batch = m.Batch join UnitsDetail d on m.Batch = d.Batch and m.Unit = d.Unit
UnitsbyProject
create index cbatchindex on UnitsbyProjectControl(Batch);
create index mbatchindex on UnitsbyProjectMaster(Batch);
create index mmasterkeyindex on UnitsbyProjectMaster(RefNo);
create index dbatchindex on UnitsbyProjectDetail(Batch);
create index dmasterkeyindex on UnitsbyProjectDetail(RefNo);
select c.Recurring, c.Creator, c.Period, c.EndDate, c.Total, c.PostPeriod, c.PostSeq, c.Company, c.SubmittedBy, c.SubmittedDate, m.Posted, m.Seq as MasterSeq, m.CurrencyExchangeOverrideMethod, m.CurrencyExchangeOverrideDate, m.Status, m.AuthorizedBy, m.RejectReason, m.ModUser, m.ModDate, d.RefNo, d.PKey, d.Seq as DetailSeq, d.TransDate, d.WBS1, d.WBS2, d.WBS3, d.Unit, d.TableNo, d.Account, d.Description, d.Quantity, d.CurrencyExchangeOverrideRate from UnitsbyProjectControl c join UnitsbyProjectMaster m on c.Batch = m.Batch join UnitsbyProjectDetail d on m.Batch = d.Batch and m.RefNo = d.RefNo