E10 BAQ to SQL

Epicor ERP 10
1

We have EDA, and we have some BAQs that we will drop in the script to SQL and see if they run before we sync in EDA, only because I do not know how to write in SQL.
We have a LaborHed.CheckBox01, that is a UD field added in V9.
We we try to run the BAQ in SQL we get this message.

Msg 207, Level 16, State 1, Line 18
Invalid column name ‘CheckBox01’.

So in E10 the UD fields show in a table LaborHed_UD, so we tried changing the SQL
where (LaborHed.PayrollDate >= ‘01/01/2020’ and LaborHed.ActiveTrans = 0 and LaborHed_UD.CheckBox01 = 0)

and get this message
Msg 4104, Level 16, State 1, Line 18
The multi-part identifier “LaborHed_UD.CheckBox01” could not be bound.

Any suggestions?
Thanks

2

You will probably have to do a join between LaborHed and LaborHed_UD. In BAQ Epimagic takes care of it.

3

In SQL change the from table of any unknown columns to dbo from erp and that will give you the view which is the join of the system table and the _UD table.

1 Like
4

Thank you!

image515438.jpg

image558500.jpg

image980016.png

image171660.png

image875788.png

image529390.png

5

can you paste the SQL code in here ?

6

Here is the code, with the addition of joining the LaborHed_UD, and we are going to try it this morning.

What do you think?

select

[EmpBasic1].[Company] as [EmpBasic1_Company],

[EmpBasic1].[JCDept] as [EmpBasic1_JCDept],

[EmpBasic1].[EmpStatus] as [EmpBasic1_EmpStatus],

[EmpBasic1].[EmpID] as [EmpBasic1_EmpID],

(EmpBasic1.LastName + ', ’ + EmpBasic1.FirstName) as [Calculated_Employee],

[DirLbrErnHrs].[Calculated_DirLbrHrs] as [Calculated_DirLbrHrs],

[DirLbrErnHrs].[Calculated_ErnHrs] as [Calculated_ErnHrs],

[LbrHead].[Calculated_PayHours] as [Calculated_PayHours],

[LbrHead].[LaborHed_PayrollDate] as [LaborHed_PayrollDate]

from Erp.EmpBasic as EmpBasic1

inner join (select

[LaborHed].[Company] as [LaborHed_Company],

[LaborHed].[PayrollDate] as [LaborHed_PayrollDate],

[LaborHed].[EmployeeNum] as [LaborHed_EmployeeNum],

(sum(LaborHed.PayHours)) as [Calculated_PayHours]

from Erp.LaborHed as LaborHed

inner join erp.LaborHed_UD as LaborHed_UD on LaborHed.SysRowID = LaborHed_UD.ForeignSysRowID

where (LaborHed.PayrollDate >= ‘01/01/2020’ and LaborHed.ActiveTrans = 0 and LaborHed_UD.CheckBox01 = 0)

group by [LaborHed].[Company],

[LaborHed].[PayrollDate],

[LaborHed].[EmployeeNum]) as LbrHead on

EmpBasic1.Company = LbrHead.LaborHed_Company

and EmpBasic1.EmpID = LbrHead.LaborHed_EmployeeNum

left outer join (select

[LaborDtl].[Company] as [LaborDtl_Company],

[LaborDtl].[PayrollDate] as [LaborDtl_PayrollDate],

[EmpBasic].[JCDept] as [EmpBasic_JCDept],

[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],

(sum(LaborDtl.LaborHrs)) as [Calculated_DirLbrHrs],

(sum(LaborDtl.EarnedHrs)) as [Calculated_ErnHrs]

from Erp.LaborDtl as LaborDtl

inner join Erp.EmpBasic as EmpBasic on

LaborDtl.Company = EmpBasic.Company

and LaborDtl.EmployeeNum = EmpBasic.EmpID

where (LaborDtl.LaborType <> ‘I’ and LaborDtl.ActiveTrans = 0)

group by [LaborDtl].[Company],

[LaborDtl].[PayrollDate],

[EmpBasic].[JCDept],

[LaborDtl].[EmployeeNum]) as DirLbrErnHrs on

LbrHead.LaborHed_Company = DirLbrErnHrs.LaborDtl_Company

and LbrHead.LaborHed_EmployeeNum = DirLbrErnHrs.LaborDtl_EmployeeNum

and LbrHead.LaborHed_PayrollDate = DirLbrErnHrs.LaborDtl_PayrollDate

image515438.jpg

image558500.jpg

image980016.png

image171660.png

image875788.png

image529390.png

7

try this

select EmpBasic1.Company as EmpBasic1_Company,

EmpBasic1.JCDept as EmpBasic1_JCDept,

EmpBasic1.EmpStatus as EmpBasic1_EmpStatus,

EmpBasic1.EmpID as EmpBasic1_EmpID,

(EmpBasic1.LastName + ', ' + EmpBasic1.FirstName) as Calculated_Employee,

DirLbrErnHrs.Calculated_DirLbrHrs as Calculated_DirLbrHrs,

DirLbrErnHrs.Calculated_ErnHrs as Calculated_ErnHrs,

LbrHead.Calculated_PayHours as Calculated_PayHours,

LbrHead.LaborHed_PayrollDate as LaborHed_PayrollDate

from Erp.EmpBasic as EmpBasic1

inner join (select

LaborHed.Company as LaborHed_Company,

LaborHed.PayrollDate as LaborHed_PayrollDate,

LaborHed.EmployeeNum as LaborHed_EmployeeNum,

(sum(LaborHed.PayHours)) as Calculated_PayHours

from LaborHed as LaborHed

where (LaborHed.PayrollDate >= '01/01/2020' and LaborHed.ActiveTrans = 0 and LaborHed.CheckBox01 = 0)

group by LaborHed.Company,

LaborHed.PayrollDate,

LaborHed.EmployeeNum) as LbrHead on

EmpBasic1.Company = LbrHead.LaborHed_Company

and EmpBasic1.EmpID = LbrHead.LaborHed_EmployeeNum

left outer join (select

LaborDtl.Company as LaborDtl_Company,

LaborDtl.PayrollDate as LaborDtl_PayrollDate,

EmpBasic.JCDept as EmpBasic_JCDept,

LaborDtl.EmployeeNum as LaborDtl_EmployeeNum,

(sum(LaborDtl.LaborHrs)) as Calculated_DirLbrHrs,

(sum(LaborDtl.EarnedHrs)) as Calculated_ErnHrs

from Erp.LaborDtl as LaborDtl

inner join Erp.EmpBasic as EmpBasic on

LaborDtl.Company = EmpBasic.Company

and LaborDtl.EmployeeNum = EmpBasic.EmpID

where (LaborDtl.LaborType <> 'I' and LaborDtl.ActiveTrans = 0)

group by LaborDtl.Company,

LaborDtl.PayrollDate,

EmpBasic.JCDept,

LaborDtl.EmployeeNum) as DirLbrErnHrs on

LbrHead.LaborHed_Company = DirLbrErnHrs.LaborDtl_Company

and LbrHead.LaborHed_EmployeeNum = DirLbrErnHrs.LaborDtl_EmployeeNum

and LbrHead.LaborHed_PayrollDate = DirLbrErnHrs.LaborDtl_PayrollDate
8

once first UD field created on any Epicor table, the view table that you should use is without Erp. nor odb. just the name of the table

image
image1649×663 87.4 KB