Job Traveler Customization

halcyon · November 15, 2023, 12:22pm

I have a custom version of the Job Traveler where I am trying to pull in the prime warehouse and bin for each raw material in the job as well as the part being produced. Problem is that I join to the PartPlant and PlantWhse tables on the JobHead table to get the prime warehouse and bin for the main part, but then can’t join in the RDD on the JobMtl table as well to get the fields for the raw materials!

Anyway around this? I’ve tried inner selects within the RDL file to get the fields specifically to no avail. RDL query below:

="SELECT JH.Company, JH.JobNum, JH.PartNum, JH.Calc_MultiJobParts, JH.PartNum_ShortChar04, JH.PartNum_ShortChar06, JH.PartNum_ShortChar10, JH.PartNum_ClassID, JH.PartNum_MtlAnalysisCode, JH.StartDate, JH.DueDate, JH.ShortChar01, JH.Number01, JH.SchedCode_Description, JH.ProdQty AS JobHead_ProdQty, JP.TargetJobNum, JP.OrderNum, JP.OrderLine, JP.OrderRelNum, JP.ProdQty, JA.AssemblySeq, JA.Calc_comment, JA.StartDate AS JA_StartDate, JA.PartNum_CheckBox07, JO.OprSeq, JO.OpCode, JO.RunQty, JO.Machines, JO.EstSetHours, JO.EstProdHours, JO.StartDate AS JobOper_StartDate, JO.DueDate AS JobOper_DueDate, JO.Calc_DispStatus, JO.ProdStandard, JO.OpCode_OpDesc, JO.OpDesc, JO.VendorNum_VendorID, JO.CommentText, JO.Calc_OPText, JO.FAQty, JO.PrimaryProdOpDtl, JO.PrimarySetupOpDtl, DENSE_RANK() OVER(PARTITION BY JA.Company, JA.JobNum ORDER BY JA.Company, JA.JobNum, JA.AssemblySeq) AS RunCountJobAssy, JAI.AssyCount, JA.RevisionNum, JA.Description, JOD.ResourceGrpID, JOD.ResourceGrpID_Description, JOD.OpDtlSeq, JOD.SetUpCrewSize, JOD.SetupOrProd, JOD.ProdCrewSize, JOD.ConcurrentCapacity, JOD.DailyProdRate, JOD.Calc_SchedResGrpDesc, JOD.Calc_ResGrpDesc, JOD.Calc_SchedResDesc, JOD.Calc_ResDesc, JOD.Calc_CapbltyDesc, JOD.CapabilityID, JOD.ResourceID, PW.WarehouseCode, PW.PrimBin, RG.JCDept_Description, RG.InputWhse, RG.Description AS RG_Description, (SELECT STRING_AGG(JM1.PartNum, CHAR(10)) FROM JobMtl_" + Parameters!TableGuid.Value + " JM1 WHERE JM1.JobNum = JH.JobNum AND JM1.AnalysisCode IN ('1003', '1010', '1030', '1045', '1050', '1061', '1066', '1073')) AS SteelList, SA.Parent, SA.Calc_ParentNum, SA.Calc_ParentDesc, SA.RelatedOperation, SA.AssemblySeq AS SubAsmbl_AssemblySeq, SA.PartNum AS SubAsmbl_PartNum, SA.Description AS SubAsmbl_Description, SA.RequiredQty AS SubAsmbl_RequiredQty, SA.PullQty AS SubAsmbl_PullQty, SA.IUM AS SubAsmbl_IUM, SA.WarehouseCode AS SubAsmbl_WarehouseCode, SA.Calc_DispStatus AS SubAsmbl_Calc_DispStatus, JM.MtlSeq, JM.RelatedOperation AS JobMtl_RelatedOperation, JM.PartNum AS JobMtl_PartNum, JM.PartNum_PartDescription, JM.PartNum_ShortChar04 AS JobMtl_PartNum_ShortChar04, JM.PartNum_ShortChar07 AS JobMtl_PartNum_ShortChar07, JM.RequiredQty, JM.IUM, JM.BackFlush, JM.IssuedComplete, JM.IssuedQty, JM.Ordered, JM.CheckBox01, JM.AssemblySeq AS JobMtl_AssemblySeq, JM.QtyPer, XFR.ShortChar01 AS XFR_ShortChar01, XFR.ShortChar02 AS XFR_ShortChar02, XFR.ShortChar03 AS XFR_ShortChar03, XFR.DocTypeID, ISNULL(RTU.ResourceGrpID, '') AS ResourceTimeUsed_ResourceGrpID, ISNULL(RTU.ResourceID, '') AS ResourceTimeUsed_ResourceID, PR.Name, CY.Calc_APSAvailable, PW.WarehouseCode AS PlantWhse_WarehouseCode, RG.InputWhse AS ResourceGroup_InputWhse, PO.PONum, PO.ReceivedQty AS PO_ReceivedQty, WB.Character01, JM.PartNum_QtyBearing, JM.PartNum_ClassID AS JM_PartNum_ClassID, 

(SELECT TOP 1 PPI1.PartNum FROM JobMtl_" + Parameters!TableGuid.Value + " AS JMI1 LEFT OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " AS PPI1 ON CStr(JMI1.PartNum) = CStr(PPI1.PartNum) AND PPI1.PartNum != '') AS TEST,

(SELECT WI1.Number01 FROM JobMtl_" + Parameters!TableGuid.Value + " AS JMI1 LEFT OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " AS PPI1 ON JMI1.PartNum = PPI1.PartNum LEFT OUTER JOIN PlantWhse_" + Parameters!TableGuid.Value + " AS PWI1 
ON PPI1.PartNum = PWI1.PartNum AND PPI1.PrimWhse = PWI1.WarehouseCode LEFT JOIN Warehse_" + Parameters!TableGuid.Value + " WI1 ON WI1.Plant = PWI1.Plant AND WI1.WarehouseCode = PWI1.WarehouseCode WHERE JMI1.JobNum = JM.JobNum AND JMI1.PartNum = JM.PartNum ) AS JM_Bldg,

(SELECT PWI2.WarehouseCode FROM JobMtl_" + Parameters!TableGuid.Value + " AS JMI2 LEFT OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " AS PPI2 ON JMI2.PartNum = PPI2.PartNum LEFT OUTER JOIN PlantWhse_" + Parameters!TableGuid.Value + " AS PWI2 
ON PPI2.PartNum = PWI2.PartNum AND PPI2.PrimWhse = PWI2.WarehouseCode WHERE JMI2.JobNum = JM.JobNum AND JMI2.PartNum = JM.PartNum) AS JM_PrimWhse,

(SELECT PWI3.PrimBin FROM JobMtl_" + Parameters!TableGuid.Value + " AS JMI3 LEFT OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " AS PPI3 ON JMI3.PartNum = PPI3.PartNum LEFT OUTER JOIN PlantWhse_" + Parameters!TableGuid.Value + " AS PWI3 
ON PPI3.PartNum = PWI3.PartNum AND PPI3.PrimWhse = PWI3.WarehouseCode WHERE JMI3.JobNum = JM.JobNum AND JMI3.PartNum = JM.PartNum) AS JM_PrimBin

 FROM JobHead_" + Parameters!TableGuid.Value + " JH 
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " JP ON JH.Company = JP.Company AND JH.JobNum = JP.JobNum 
LEFT OUTER JOIN XFileAttch_" + Parameters!TableGuid.Value + " XFA ON JH.Company = XFA.Company AND JH.JobNum = XFA.Key1 
LEFT OUTER JOIN XFileRef_" + Parameters!TableGuid.Value + " XFR ON XFA.Company = XFR.Company AND XFA.XFileRefNum = XFR.XFileRefNum 
LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " JA ON JP.Company = JA.Company AND JP.JobNum = JA.JobNum 
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " JO ON JA.Company = JO.Company AND JA.JobNum = JO.JobNum AND JA.AssemblySeq = JO.AssemblySeq 
LEFT OUTER JOIN (	
	SELECT	JA1.Company, JA1.JobNum, COUNT(DISTINCT JA1.AssemblySeq) AS AssyCount
	FROM	JobAsmbl_" + Parameters!TableGuid.Value + " JA1
	JOIN JobOper_" + Parameters!TableGuid.Value + " JO1 ON JA1.Company = JO1.Company AND JA1.JobNum = JO1.JobNum AND JA1.AssemblySeq = JO1.AssemblySeq
	GROUP BY JA1.Company, JA1.JobNum) JAI ON JA.Company = JAI.Company AND JA.JobNum = JAI.JobNum 
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " JOD ON JO.Company = JOD.Company AND JO.JobNum = JOD.JobNum AND JO.AssemblySeq = JOD.AssemblySeq AND JO.OprSeq = JOD.OprSeq 
LEFT OUTER JOIN PartPlant_" + Parameters!TableGuid.Value + " PP ON JH.Company = PP.Company AND JH.Plant = PP.Plant AND JH.PartNum = PP.PartNum 
LEFT OUTER JOIN PlantWhse_" + Parameters!TableGuid.Value + " PW ON PP.Company = PW.Company AND PP.Plant = PW.Plant AND PP.PrimWhse = PW.WarehouseCode AND PP.PartNum = PW.PartNum 
LEFT OUTER JOIN ResourceGroup_" + Parameters!TableGuid.Value + " RG ON JOD.Company = RG.Company AND JOD.ResourceGrpID = RG.ResourceGrpID 
LEFT OUTER JOIN SubAsmbl_" + Parameters!TableGuid.Value + " SA ON JA.Company = SA.Company AND JA.JobNum = SA.JobNum AND JA.AssemblySeq = SA.Parent 
LEFT OUTER JOIN ResourceTimeUsed_" + Parameters!TableGuid.Value + " RTU ON JOD.Company = RTU.Company AND JOD.JobNum = RTU.JobNum AND JOD.AssemblySeq = RTU.AssemblySeq AND JOD.OprSeq = RTU.OprSeq AND JOD.OpDtlSeq = RTU.OpDtlSeq 
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " JM ON JH.Company = JM.Company AND JH.JobNum = JM.JobNum 
LEFT OUTER JOIN Person_" + Parameters!TableGuid.Value + " PR ON JH.Company = PR.Company AND JH.PersonID = PR.PersonID 
LEFT OUTER JOIN Company_" + Parameters!TableGuid.Value + " CY ON JH.Company = CY.Company 
LEFT OUTER JOIN PORel_" + Parameters!TableGuid.Value + " PO ON JM.Company = PO.Company AND JM.JobNum = PO.JobNum AND JM.MtlSeq = PO.JobSeq 
LEFT OUTER JOIN WhseBin_" + Parameters!TableGuid.Value + " WB ON PW.Company = WB.Company AND PW.WarehouseCode = WB.WarehouseCode AND PW.PrimBin = WB.BinNum "```

halcyon · November 15, 2023, 2:18pm

Something I think could work would be to have a data directive run daily that populates a UD table with the part, prime warehouse, and bin, and then I can just join onto that table for the info. Other than the fact that this would prevent me from enabling any routing on the report style, I don’t see any other way around it.

CSmith · November 15, 2023, 3:53pm

You should be able to modify the RDD to provide the Primary Bin info if it does not already and is just excluded from output.

halcyon · November 15, 2023, 4:35pm

Unfortunately, since I am already joining the JobHead to the PartPlant and PlantWhse tables to get the Primary Bin info for the top level part, I am unable to join the JobMtl table to the same spot to get the Primary Bin info for the raw materials.

Topic		Replies	Views
Adding Bin to Job Traveler raw mats subreport Crystal definition Kinetic ERP crystal , job-traveler	3	269	June 29, 2023
Custom Job Traveler Report - Help Needed Kinetic ERP ssrs , reports	1	470	December 9, 2022
JobHead LEFT OUTER JOIN JobMtl drastically slows down Job Traveler Report Epicor ERP 10 ssrs	9	917	May 18, 2019
Add an OnHandQty column to BOM Listing report Kinetic ERP ssrs , reports	7	300	January 22, 2024
Sub Assembly Components repeating on traveler? Epicor ERP 10 e10 , customization	8	493	May 19, 2020

Job Traveler Customization

Related Topics