@Alexis Not that you have ssms to run this against, but this is the query that is being run. I used @Banderson’s baq headers to male it work.
DECLARE @ParamList Ice.QueryParamTableType;
DECLARE @CurrentUserID NVARCHAR(max) = 'epicor';
DECLARE @Today as Date = GETDATE();
INSERT INTO @ParamList
(
ParamValue
)
VALUES
(N'EPIC06' -- ParamValue - nvarchar(max)
);
DECLARE @TerritoryLst TABLE (
Company NVARCHAR(10),
TerritoryID NVARCHAR(10))
INSERT INTO @TerritoryLst
(
Company,
TerritoryID
)
SELECT * FROM Erp.CompanyAuthTerritories('EPIC06',@ParamList);
INSERT INTO @ParamList
(
ParamValue
)
VALUES
(N'EPIC06' -- ParamValue - nvarchar(max)
);
DECLARE @_compList TABLE (
Company NVARCHAR(10),
TerritoryID NVARCHAR(10))
INSERT INTO @TerritoryLst
(
Company,
TerritoryID
)
SELECT * FROM Erp.CompanyAuthTerritories('EPIC06',@ParamList);
declare @AvailCompLst table(Company nvarchar(8) primary key);
insert into @AvailCompLst values(N'');
insert into @AvailCompLst(Company) select 'EPIC06';
with [_TerrCTE_SalesRep] as
(
select uc.Company as Company, sr.SalesRepCode, ISNull(sr.ViewAllTer,0) as ViewAllTer, (case when em.FeatureID is null then 0 else 1 end) as TrCrAllowed
from [Erp].[UserComp] uc
inner join @_compList on uc.Company = 'EPIC06' and uc.DcdUserID = @CurrentUserID
left join [Erp].[SaleAuth] sa on sa.Company = 'EPIC06' and sa.DcdUserID = uc.DcdUserID
left join [Erp].[SalesRep] sr on sr.Company = uc.Company and sr.SalesRepCode = sa.SalesRepCode
left join [Ice].[SysCompany] sc on sc.Company = uc.Company
left join [Ice].[EnabledModule] em on em.InstallationID = sc.InstallationID and em.FeatureID = '00000003-9662-4B1D-AD67-8D90C3599092'
)
insert into @TerritoryLst
select st.Company, st.TerritoryID -- CRM disabled
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st on st.Company = [_TerrCTE_SalesRep].Company and st.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 0
union
select st1.Company, st1.TerritoryID -- CRM enabled and ViewAllTerr=1
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st1 on st1.Company = [_TerrCTE_SalesRep].Company and st1.Inactive = 0
where [_TerrCTE_SalesRep].ViewAllTer = 1 and [_TerrCTE_SalesRep].TrCrAllowed = 1
union
select st2.Company, st2.TerritoryID -- CRM enabled and no SaleAuth
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTer] st2 on st2.Company = [_TerrCTE_SalesRep].Company and st2.Inactive = 0
where [_TerrCTE_SalesRep].TrCrAllowed = 1 and not exists(select top 1 1 from [Erp].[SaleAuth] sa2 where sa2.Company = [_TerrCTE_SalesRep].Company and sa2.DcdUserID = @CurrentUserID and sa2.SalesRepCode = [_TerrCTE_SalesRep].SalesRepCode)
union
select strp.Company, strp.TerritoryID -- CRM enabled and ViewAllTerr=0 and territories assigned
from [_TerrCTE_SalesRep]
inner join [Erp].[SalesTRP] strp on [_TerrCTE_SalesRep].ViewAllTer = 0 and [_TerrCTE_SalesRep].Company = strp.Company and [_TerrCTE_SalesRep].SalesRepCode = strp.SalesRepCode;
--queryid: DEV_EngAvailbyProj, query companyid: EPIC06, userid: epicor, user companyid: EPIC06
SET DATEFORMAT mdy;
with [SubQuery1] as
(select (CASE WHEN [ProjPOs].[PORelPROJ_XRelQty] >= 0 THEN [ProjPOs].[PORelPROJ_XRelQty] ELSE 0 END) [Calculated_Calc_ReceiptQty],(Sum((Case when [PartDtl].[RequirementFlag]=0 then [PartDtl].[Quantity] else (-1*[PartDtl].[Quantity]) end)) over (partition by [PartDtl].[PartNum] order by [PartDtl].[DueDate] , [PartDtl].[PONum] , [PartDtl].[POLine] , [PartDtl].[PORelNum] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)+isnull((CASE WHEN sum([PartBin].[OnhandQty]) >= 0 THEN sum([PartBin].[OnhandQty]) ELSE 0 END),0)) [Calculated_Calc_RunningSum2],[PartDtl].[IUM] [PartDtl_IUM],(Case when [PartDtl].[RequirementFlag]=0 then [PartDtl].[Quantity] else (-1*[PartDtl].[Quantity]) end) [Calculated_Calc_Quantity],[PartDtl].[JobNum] [PartDtl_JobNum],[PartDtl].[AssemblySeq] [PartDtl_AssemblySeq],[PartDtl].[JobSeq] [PartDtl_JobSeq],(CASE
WHEN [PartDtl].[SourceFile] = N'JM' THEN [PartDtl].[DueDate]
WHEN [PartDtl].[SourceFile] = N'PO' THEN [ProjPOs].[PORelPROJ_PromiseDt]
ELSE NULL END) [Calculated_Calc_JobPoDue],[PartDtl].[PONum] [PartDtl_PONum],[PartDtl].[POLine] [PartDtl_POLine],[PartDtl].[PORelNum] [PartDtl_PORelNum],[PartDtl].[DueDate] [PartDtl_DueDate],(Sum((Case when [PartDtl].[RequirementFlag]=0 then [PartDtl].[Quantity] else (-1*[PartDtl].[Quantity]) end)) over (partition by [PartDtl].[PartNum] order by [PartDtl].[DueDate])+isnull((CASE WHEN sum([PartBin].[OnhandQty]) >= 0 THEN sum([PartBin].[OnhandQty]) ELSE 0 END),0)) [Calculated_Calc_RunningSum],(CASE WHEN sum([PartBin].[OnhandQty]) >= 0 THEN sum([PartBin].[OnhandQty]) ELSE 0 END) [Calculated_Calc_OnHand],[PartDtl].[PartDescription] [PartDtl_PartDescription],[Part].[TypeCode] [Part_TypeCode],[PartDtl].[PartNum] [PartDtl_PartNum],[PartDtl].[RequirementFlag] [PartDtl_RequirementFlag],[PartDtl].[SourceFile] [PartDtl_SourceFile],[PartDtl].[Quantity] [PartDtl_Quantity],[Customer].[CustNum] [Customer_CustNum],[Customer].[Name] [Customer_Name],[JobProd].[OrderNum] [JobProd_OrderNum],[PartDtl].[Company] [PartDtl_Company],(dateadd(day, MAX([PartPlant].[LeadTime]), @Today)) [Calculated_Calc_LeadTime],(dateadd(day, (-1* MAX([PartPlant].[LeadTime])), [PartDtl].[DueDate])) [Calculated_Calc_LastDate],[PartPlant].[LeadTime] [PartPlant_LeadTime],[ProjPOs].[PORelPROJ_XRelQty] [PORelPROJ_XRelQty],[ProjPOs].[PORelPROJ_DueDate] [PORelPROJ_DueDate],[ProjPOs].[PORelPROJ_PromiseDt] [PORelPROJ_PromiseDt],(MAX(coalesce([ProjPOs].[PORelPROJ_ProjectID], [ProjJobs].[JobHead_ProjectID], N'NA'))) [Calculated_Cal_ProjID]
from ( [Erp].[PartDtl] inner join @AvailCompLst [AvailCLst_PartDtl] on [PartDtl].[Company] is null Or [PartDtl].[Company] = [AvailCLst_PartDtl].[Company])
left outer join [Erp].[PartBin] on [PartDtl].[Company] = [PartBin].[Company] And [PartDtl].[PartNum] = [PartBin].[PartNum]
left outer join [Erp].[PartPlant] on ([PartDtl].[Company] = [PartPlant].[Company] And [PartDtl].[PartNum] = [PartPlant].[PartNum] And [PartDtl].[Plant] = [PartPlant].[Plant]) and (([PartPlant].[Plant] is null or Exists (select 1 [c1]
from [Ice].[SysUserComp] [SysUserComp] cross apply [STRING_SPLIT](SysUserComp.PlantList, N'~')
where [SysUserComp].[Company] = [PartPlant].[Company] and [SysUserComp].[UserID] = @CurrentUserID and value = [PartPlant].[Plant]
)))
left outer join [Erp].[Part] on ([PartPlant].[Company] = [Part].[Company] And [PartPlant].[PartNum] = [Part].[PartNum]) and (([PartPlant].[Plant] is null or Exists (select 1 [c1]
from [Ice].[SysUserComp] [SysUserComp] cross apply [STRING_SPLIT](SysUserComp.PlantList, N'~')
where [SysUserComp].[Company] = [PartPlant].[Company] and [SysUserComp].[UserID] = @CurrentUserID and value = [PartPlant].[Plant]
)))
left outer join (select [PORelPROJ].[Company] [PORelPROJ_Company],[PORelPROJ].[PONum] [PORelPROJ_PONum],[PORelPROJ].[POLine] [PORelPROJ_POLine],[PORelPROJ].[PORelNum] [PORelPROJ_PORelNum],[PORelPROJ].[ProjectID] [PORelPROJ_ProjectID],[PORelPROJ].[XRelQty] [PORelPROJ_XRelQty],[PORelPROJ].[DueDate] [PORelPROJ_DueDate],[PORelPROJ].[PromiseDt] [PORelPROJ_PromiseDt]
from ( [Erp].[PORel] [PORelPROJ] inner join @AvailCompLst [AvailCLst_PORelPROJ] on [PORelPROJ].[Company] is null Or [PORelPROJ].[Company] = [AvailCLst_PORelPROJ].[Company])
) [ProjPOs] on [PartDtl].[Company] = [ProjPOs].[PORelPROJ_Company] And [PartDtl].[PONum] = [ProjPOs].[PORelPROJ_PONum] And [PartDtl].[POLine] = [ProjPOs].[PORelPROJ_POLine] And [PartDtl].[PORelNum] = [ProjPOs].[PORelPROJ_PORelNum]
left outer join (select [JobHead].[Company] [JobHead_Company],[JobHead].[JobNum] [JobHead_JobNum],[JobHead].[ProjectID] [JobHead_ProjectID]
from ( [Erp].[JobHead] inner join @AvailCompLst [AvailCLst_JobHead] on [JobHead].[Company] is null Or [JobHead].[Company] = [AvailCLst_JobHead].[Company])
) [ProjJobs] on [PartDtl].[Company] = [ProjJobs].[JobHead_Company] And [PartDtl].[JobNum] = [ProjJobs].[JobHead_JobNum]
left outer join ( [Erp].[JobProd] inner join @AvailCompLst [AvailCLst_JobProd] on [JobProd].[Company] is null Or [JobProd].[Company] = [AvailCLst_JobProd].[Company]) on [ProjJobs].[JobHead_Company] = [JobProd].[Company] And [ProjJobs].[JobHead_JobNum] = [JobProd].[JobNum]
left outer join [Erp].[OrderHed] on [OrderHed].[Company] = [JobProd].[Company] And [OrderHed].[OrderNum] = [JobProd].[OrderNum]
left outer join ( [Erp].[Customer] inner join @AvailCompLst [AvailCLst_Customer] on [Customer].[Company] is null Or [Customer].[Company] = [AvailCLst_Customer].[Company]) on ([OrderHed].[Company] = [Customer].[Company] And [OrderHed].[BTCustNum] = [Customer].[CustNum]) and ((([Customer].[TerritoryID] is null or [Customer].[TerritoryID] = N'') or exists (select 1
from @TerritoryLst [__TerritoryLst]
where [__TerritoryLst].[Company] = [Customer].[Company] and [__TerritoryLst].[TerritoryID] = [Customer].[TerritoryID]
) or exists (select 1 [c1]
from [Erp].[ShipTo] [ShipTo]
inner join @TerritoryLst [__TerritoryLst] on [ShipTo].[Company] = [__TerritoryLst].[Company] and [ShipTo].[TerritoryID] = [__TerritoryLst].[TerritoryID] Or [ShipTo].[TerritoryID] is null Or [ShipTo].[TerritoryID] = N''
where [ShipTo].[Company] = [Customer].[Company] and [ShipTo].[CustNum] = [Customer].[CustNum]
)))
group by [PartDtl].[IUM],[PartDtl].[JobNum],[PartDtl].[AssemblySeq],[PartDtl].[JobSeq],[PartDtl].[PONum],[PartDtl].[POLine],[PartDtl].[PORelNum],[PartDtl].[DueDate],[PartDtl].[PartDescription],[Part].[TypeCode],[PartDtl].[PartNum],[PartDtl].[RequirementFlag],[PartDtl].[SourceFile],[PartDtl].[Quantity],[Customer].[CustNum],[Customer].[Name],[JobProd].[OrderNum],[PartDtl].[Company],[PartPlant].[LeadTime],[ProjPOs].[PORelPROJ_XRelQty],[ProjPOs].[PORelPROJ_DueDate],[ProjPOs].[PORelPROJ_PromiseDt]
)
select *
from [SubQuery1] [SubQuery1]
where (Calculated_Cal_ProjID = N'KneeImplant')
order by [PartDtl_DueDate] ,[PartDtl_PONum] ,[PartDtl_POLine] ,[PartDtl_PORelNum] ,[PartDtl_JobNum]
