Thanks, Jason.
It’s not pretty due to the way Epicor handles split reps, but here it is…
select
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[SalesRepList] as [InvcHead_SalesRepList],
([Ice].num_entries(InvcHead.SalesRepList, '~')) as [Calculated_CountSalesRep],
(1) as [Calculated_RepNum],
([Ice].entry(1, InvcHead.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[InvoiceAmt] as [InvcHead_InvoiceAmt],
[InvcHead].[InvoiceBal] as [InvcHead_InvoiceBal],
[InvcHead].[PONum] as [InvcHead_PONum],
[InvcDtl].[RepRate1] as [InvcDtl_RepRate1],
[InvcDtl].[RepSplit1] as [InvcDtl_RepSplit1],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
[InvcDtl].[OrderLine] as [InvcDtl_OrderLine],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[CashDtl].[HeadNum] as [CashDtl_HeadNum],
[CashDtl].[TranDate] as [CashDtl_TranDate],
[CashDtl].[TranAmt] as [CashDtl_TranAmt],
[CashDtl].[TranType] as [CashDtl_TranType],
[CashDtl].[CheckRef] as [CashDtl_CheckRef],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[InvcDtl].[OurOrderQty] as [InvcDtl_OurOrderQty],
[InvcDtl].[OurShipQty] as [InvcDtl_OurShipQty],
[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
((case when InvcDtl.PricePerCode = 'C' then 100.0 else
(case when InvcDtl.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl.PricePerCode = 'C' then InvcDtl.UnitPrice / 100.0 else
(case when InvcDtl.PricePerCode = 'M' then InvcDtl.UnitPrice / 1000.0 else
InvcDtl.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl.UnitPrice / CostPer * InvcDtl.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl].[DiscountPercent] as [InvcDtl_DiscountPercent],
[InvcDtl].[Discount] as [InvcDtl_Discount],
(case when InvcHead.InvoiceAmt = 0.0 then 0.0
else CashDtl.TranAmt / InvcHead.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl.OurShipQty) - InvcDtl.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl.RepRate1 / 100.0) * (InvcDtl.RepSplit1 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
and ( InvcDtl.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
inner join Erp.CashDtl as CashDtl on
InvcHead.Company = CashDtl.Company
and InvcHead.InvoiceNum = CashDtl.InvoiceNum
and ( CashDtl.TranType = 'PayInv' )
where (InvcHead.SalesRepList <> '')
union
select
[InvcHead2].[InvoiceNum] as [InvcHead2_InvoiceNum],
[InvcHead2].[SalesRepList] as [InvcHead2_SalesRepList],
([Ice].num_entries(InvcHead2.SalesRepList, '~')) as [Calculated_CountSalesRep],
(2) as [Calculated_RepNum],
([Ice].entry(2, InvcHead2.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep2],
[InvcHead2].[InvoiceDate] as [InvcHead2_InvoiceDate],
[InvcHead2].[InvoiceAmt] as [InvcHead2_InvoiceAmt],
[InvcHead2].[InvoiceBal] as [InvcHead2_InvoiceBal],
[InvcHead2].[PONum] as [InvcHead2_PONum],
[InvcDtl2].[RepRate2] as [InvcDtl2_RepRate2],
[InvcDtl2].[RepSplit2] as [InvcDtl2_RepSplit2],
[InvcDtl2].[InvoiceLine] as [InvcDtl2_InvoiceLine],
[InvcDtl2].[OrderNum] as [InvcDtl2_OrderNum],
[InvcDtl2].[OrderLine] as [InvcDtl2_OrderLine],
[Customer2].[CustID] as [Customer2_CustID],
[Customer2].[Name] as [Customer2_Name],
[CashDtl2].[HeadNum] as [CashDtl2_HeadNum],
[CashDtl2].[TranDate] as [CashDtl2_TranDate],
[CashDtl2].[TranAmt] as [CashDtl2_TranAmt],
[CashDtl2].[TranType] as [CashDtl2_TranType],
[CashDtl2].[CheckRef] as [CashDtl2_CheckRef],
[InvcDtl2].[PartNum] as [InvcDtl2_PartNum],
[InvcDtl2].[OurOrderQty] as [InvcDtl2_OurOrderQty],
[InvcDtl2].[OurShipQty] as [InvcDtl2_OurShipQty],
[InvcDtl2].[UnitPrice] as [InvcDtl2_UnitPrice],
((case when InvcDtl2.PricePerCode = 'C' then 100.0 else
(case when InvcDtl2.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl2.PricePerCode = 'C' then InvcDtl2.UnitPrice / 100.0 else
(case when InvcDtl2.PricePerCode = 'M' then InvcDtl2.UnitPrice / 1000.0 else
InvcDtl2.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl2.UnitPrice / CostPer * InvcDtl2.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl2].[DiscountPercent] as [InvcDtl2_DiscountPercent],
[InvcDtl2].[Discount] as [InvcDtl2_Discount],
(case when InvcHead2.InvoiceAmt = 0.0 then 0.0
else CashDtl2.TranAmt / InvcHead2.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl2.OurShipQty) - InvcDtl2.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl2.RepRate2 / 100.0) * (InvcDtl2.RepSplit2 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead2
inner join Erp.InvcDtl as InvcDtl2 on
InvcHead2.Company = InvcDtl2.Company
and InvcHead2.InvoiceNum = InvcDtl2.InvoiceNum
and ( InvcDtl2.RepSplit2 > 0 and InvcDtl2.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer2 on
InvcHead2.Company = Customer2.Company
and InvcHead2.CustNum = Customer2.CustNum
inner join Erp.CashDtl as CashDtl2 on
InvcHead2.Company = CashDtl2.Company
and InvcHead2.InvoiceNum = CashDtl2.InvoiceNum
where (InvcHead2.SalesRepList <> '' and InvcHead2.SalesRepList like '%~%')
union
select
[InvcHead3].[InvoiceNum] as [InvcHead3_InvoiceNum],
[InvcHead3].[SalesRepList] as [InvcHead3_SalesRepList],
([Ice].num_entries(InvcHead3.SalesRepList, '~')) as [Calculated_CountSalesRep],
(3) as [Calculated_RepNum],
([Ice].entry(3, InvcHead3.SalesRepList+'~~~~~', '~')) as [Calculated_SalesRep],
[InvcHead3].[InvoiceDate] as [InvcHead3_InvoiceDate],
[InvcHead3].[InvoiceAmt] as [InvcHead3_InvoiceAmt],
[InvcHead3].[InvoiceBal] as [InvcHead3_InvoiceBal],
[InvcHead3].[PONum] as [InvcHead3_PONum],
[InvcDtl3].[RepRate3] as [InvcDtl3_RepRate3],
[InvcDtl3].[RepSplit3] as [InvcDtl3_RepSplit3],
[InvcDtl3].[InvoiceLine] as [InvcDtl3_InvoiceLine],
[InvcDtl3].[OrderNum] as [InvcDtl3_OrderNum],
[InvcDtl3].[OrderLine] as [InvcDtl3_OrderLine],
[Customer3].[CustID] as [Customer3_CustID],
[Customer3].[Name] as [Customer3_Name],
[CashDtl3].[HeadNum] as [CashDtl3_HeadNum],
[CashDtl3].[TranDate] as [CashDtl3_TranDate],
[CashDtl3].[TranAmt] as [CashDtl3_TranAmt],
[CashDtl3].[TranType] as [CashDtl3_TranType],
[CashDtl3].[CheckRef] as [CashDtl3_CheckRef],
[InvcDtl3].[PartNum] as [InvcDtl3_PartNum],
[InvcDtl3].[OurOrderQty] as [InvcDtl3_OurOrderQty],
[InvcDtl3].[OurShipQty] as [InvcDtl3_OurShipQty],
[InvcDtl3].[UnitPrice] as [InvcDtl3_UnitPrice],
((case when InvcDtl3.PricePerCode = 'C' then 100.0 else
(case when InvcDtl3.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl3.PricePerCode = 'C' then InvcDtl3.UnitPrice / 100.0 else
(case when InvcDtl3.PricePerCode = 'M' then InvcDtl3.UnitPrice / 1000.0 else
InvcDtl3.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl3.UnitPrice / CostPer * InvcDtl3.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl3].[DiscountPercent] as [InvcDtl3_DiscountPercent],
[InvcDtl3].[Discount] as [InvcDtl3_Discount],
(case when InvcHead3.InvoiceAmt = 0.0 then 0.0
else CashDtl3.TranAmt / InvcHead3.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl3.OurShipQty) - InvcDtl3.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl3.RepRate3 / 100.0) * (InvcDtl3.RepSplit3 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead3
inner join Erp.InvcDtl as InvcDtl3 on
InvcHead3.Company = InvcDtl3.Company
and InvcHead3.InvoiceNum = InvcDtl3.InvoiceNum
and ( InvcDtl3.RepSplit3 > 0 and InvcDtl3.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer3 on
InvcHead3.Company = Customer3.Company
and InvcHead3.CustNum = Customer3.CustNum
inner join Erp.CashDtl as CashDtl3 on
InvcHead3.Company = CashDtl3.Company
and InvcHead3.InvoiceNum = CashDtl3.InvoiceNum
where (InvcHead3.SalesRepList <> '' and InvcHead3.SalesRepList like '%~%')
union
select
[InvcHead4].[InvoiceNum] as [InvcHead4_InvoiceNum],
[InvcHead4].[SalesRepList] as [InvcHead4_SalesRepList],
([Ice].num_entries(InvcHead4.SalesRepList, '~')) as [Calculated_CountSalesRep],
(4) as [Calculated_RepNum],
([Ice].entry(4, InvcHead4.SalesRepList, '~')) as [Calculated_SalesRep],
[InvcHead4].[InvoiceDate] as [InvcHead4_InvoiceDate],
[InvcHead4].[InvoiceAmt] as [InvcHead4_InvoiceAmt],
[InvcHead4].[InvoiceBal] as [InvcHead4_InvoiceBal],
[InvcHead4].[PONum] as [InvcHead4_PONum],
[InvcDtl4].[RepRate4] as [InvcDtl4_RepRate4],
[InvcDtl4].[RepSplit4] as [InvcDtl4_RepSplit4],
[InvcDtl4].[InvoiceLine] as [InvcDtl4_InvoiceLine],
[InvcDtl4].[OrderNum] as [InvcDtl4_OrderNum],
[InvcDtl4].[OrderLine] as [InvcDtl4_OrderLine],
[Customer4].[CustID] as [Customer4_CustID],
[Customer4].[Name] as [Customer4_Name],
[CashDtl4].[HeadNum] as [CashDtl4_HeadNum],
[CashDtl4].[TranDate] as [CashDtl4_TranDate],
[CashDtl4].[TranAmt] as [CashDtl4_TranAmt],
[CashDtl4].[TranType] as [CashDtl4_TranType],
[CashDtl4].[CheckRef] as [CashDtl4_CheckRef],
[InvcDtl4].[PartNum] as [InvcDtl4_PartNum],
[InvcDtl4].[OurOrderQty] as [InvcDtl4_OurOrderQty],
[InvcDtl4].[OurShipQty] as [InvcDtl4_OurShipQty],
[InvcDtl4].[UnitPrice] as [InvcDtl4_UnitPrice],
((case when InvcDtl4.PricePerCode = 'C' then 100.0 else
(case when InvcDtl4.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl4.PricePerCode = 'C' then InvcDtl4.UnitPrice / 100.0 else
(case when InvcDtl4.PricePerCode = 'M' then InvcDtl4.UnitPrice / 1000.0 else
InvcDtl4.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl4.UnitPrice / CostPer * InvcDtl4.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl4].[DiscountPercent] as [InvcDtl4_DiscountPercent],
[InvcDtl4].[Discount] as [InvcDtl4_Discount],
(case when InvcHead4.InvoiceAmt = 0.0 then 0.0
else CashDtl4.TranAmt / InvcHead4.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl4.OurShipQty) - InvcDtl4.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl4.RepRate4 / 100.0) * (InvcDtl4.RepSplit4 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead4
inner join Erp.InvcDtl as InvcDtl4 on
InvcHead4.Company = InvcDtl4.Company
and InvcHead4.InvoiceNum = InvcDtl4.InvoiceNum
and ( InvcDtl4.RepSplit4 > 0 and InvcDtl4.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer4 on
InvcHead4.Company = Customer4.Company
and InvcHead4.CustNum = Customer4.CustNum
inner join Erp.CashDtl as CashDtl4 on
InvcHead4.Company = CashDtl4.Company
and InvcHead4.InvoiceNum = CashDtl4.InvoiceNum
where (InvcHead4.SalesRepList <> '' and InvcHead4.SalesRepList like '%~%')
union
select
[InvcHead5].[InvoiceNum] as [InvcHead5_InvoiceNum],
[InvcHead5].[SalesRepList] as [InvcHead5_SalesRepList],
([Ice].num_entries(InvcHead5.SalesRepList, '~')) as [Calculated_CountSalesRep],
(5) as [Calculated_RepNum],
([Ice].entry(5, InvcHead5.SalesRepList, '~')) as [Calculated_SalesRep],
[InvcHead5].[InvoiceDate] as [InvcHead5_InvoiceDate],
[InvcHead5].[InvoiceAmt] as [InvcHead5_InvoiceAmt],
[InvcHead5].[InvoiceBal] as [InvcHead5_InvoiceBal],
[InvcHead5].[PONum] as [InvcHead5_PONum],
[InvcDtl5].[RepRate5] as [InvcDtl5_RepRate5],
[InvcDtl5].[RepSplit5] as [InvcDtl5_RepSplit5],
[InvcDtl5].[InvoiceLine] as [InvcDtl5_InvoiceLine],
[InvcDtl5].[OrderNum] as [InvcDtl5_OrderNum],
[InvcDtl5].[OrderLine] as [InvcDtl5_OrderLine],
[Customer5].[CustID] as [Customer5_CustID],
[Customer5].[Name] as [Customer5_Name],
[CashDtl5].[HeadNum] as [CashDtl5_HeadNum],
[CashDtl5].[TranDate] as [CashDtl5_TranDate],
[CashDtl5].[TranAmt] as [CashDtl5_TranAmt],
[CashDtl5].[TranType] as [CashDtl5_TranType],
[CashDtl5].[CheckRef] as [CashDtl5_CheckRef],
[InvcDtl5].[PartNum] as [InvcDtl5_PartNum],
[InvcDtl5].[OurOrderQty] as [InvcDtl5_OurOrderQty],
[InvcDtl5].[OurShipQty] as [InvcDtl5_OurShipQty],
[InvcDtl5].[UnitPrice] as [InvcDtl5_UnitPrice],
((case when InvcDtl5.PricePerCode = 'C' then 100.0 else
(case when InvcDtl5.PricePerCode = 'M' then 1000.0 else
1.0 end) end)) as [Calculated_CostPer],
((case when InvcDtl5.PricePerCode = 'C' then InvcDtl5.UnitPrice / 100.0 else
(case when InvcDtl5.PricePerCode = 'M' then InvcDtl5.UnitPrice / 1000.0 else
InvcDtl5.UnitPrice end) end)) as [Calculated_TrueUnitPrice],
(InvcDtl5.UnitPrice / CostPer * InvcDtl5.OurOrderQty) as [Calculated_InvoiceLineTotal],
[InvcDtl5].[DiscountPercent] as [InvcDtl5_DiscountPercent],
[InvcDtl5].[Discount] as [InvcDtl5_Discount],
(case when InvcHead5.InvoiceAmt = 0.0 then 0.0
else CashDtl5.TranAmt / InvcHead5.InvoiceAmt
end) as [Calculated_LineCAPercent],
(((TrueUnitPrice * InvcDtl5.OurShipQty) - InvcDtl5.Discount) * LineCAPercent) as [Calculated_LineCA],
(LineCA * (InvcDtl5.RepRate5 / 100.0) * (InvcDtl5.RepSplit5 / 100.0)) as [Calculated_AmountDue]
from Erp.InvcHead as InvcHead5
inner join Erp.InvcDtl as InvcDtl5 on
InvcHead5.Company = InvcDtl5.Company
and InvcHead5.InvoiceNum = InvcDtl5.InvoiceNum
and ( InvcDtl5.RepSplit5 > 0 and InvcDtl5.ProdCode <> 'RETURNS' )
inner join Erp.Customer as Customer5 on
InvcHead5.Company = Customer5.Company
and InvcHead5.CustNum = Customer5.CustNum
inner join Erp.CashDtl as CashDtl5 on
InvcHead5.Company = CashDtl5.Company
and InvcHead5.InvoiceNum = CashDtl5.InvoiceNum
where (InvcHead5.SalesRepList <> '' and InvcHead5.SalesRepList like '%~%')