# Inventory/WIP Reconciliation

**Category:** [Epicor ERP 10](https://www.epiusers.help/c/e10/5)
**Created:** 2016-11-22 19:56 UTC
**Views:** 7857
**Replies:** 7
**URL:** https://www.epiusers.help/t/inventory-wip-reconciliation/36394

---

## Post #1 by @Mel

How do I go about finding what tables are being used in this report? 

Thanks

 Mel

---

## Post #2 by @Mike

I would go to the report style and then jump to the related report data definition which should show the tables available to the report.

---

## Post #3 by @amaragni

Not necessarily - sometimes the RDD generates a table of data which is not the same as the source ERP tables ... and i think this is one of those ... 
but you can bet the farm PARTTRAN table is being used as the base

---

## Post #4 by @acicala

Attached is a dashboard I created to show POSTED Inventory/WIP transactions for both parts and labor for a given fiscal period. This was done in 10.1.400. It was originally developed in E9 so I’m pretty sure the tables haven’t changed. If nothing else, the BAQ will help you understand the table linkages.

Andy Cicala
Partner
[TGem_final]
Delivering Effective Business Solutions
Mobile: (973) 615-5033
www.trigemco.com
Acicala@trigemco.com<mailto:Acicala@trigemco.com>

<img src='/uploads/default/original/1X/019699ab7e0cb2a7bc3da46b82792b9a10e2b401.png' width='86' height='51'>



<a class='attachment' href='/uploads/default/original/1X/f6d056a48c7d9da32e01fa7ab2e451042c0a7113.DBD'>InvWIPReconDB.DBD</a> (574 KB)

---

## Post #5 by @jrmckelvy

What format is this attachment in? It would be great if you could post the SQL or in a more universal format. I just need query logic and tables used

---

## Post #6 by @PaulMorgan

its a dashboard....below are the two baq's in the dashboard

select 
	[TranGLC].[FiscalYear] as [TranGLC_FiscalYear],
	[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
	[TranGLC].[TranDate] as [TranGLC_TranDate],
	[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp],
	[TranGLC].[BookDebitAmount] as [TranGLC_BookDebitAmount],
	[TranGLC].[BookCreditAmount] as [TranGLC_BookCreditAmount],
	('Labor') as [Calculated_CalcTranType],
	(LaborDtl_JobHead.ProjectID) as [Calculated_CalcProjectID],
	[LaborDtl_JobHead].[Plant] as [LaborDtl_JobHead_Plant],
	('Labor') as [Calculated_CalcPartClass],
	[LaborDtl].[CreatedBy] as [LaborDtl_CreatedBy],
	[LaborDtl].[JobNum] as [LaborDtl_JobNum],
	[LaborDtl].[EmployeeNum] as [LaborDtl_EmployeeNum],
	[EmpBasic].[Name] as [EmpBasic_Name],
	[LaborDtl].[Shift] as [LaborDtl_Shift],
	[LaborDtl].[LaborQty] as [LaborDtl_LaborQty],
	[LaborDtl].[ScrapQty] as [LaborDtl_ScrapQty],
	[LaborDtl].[LaborHrs] as [LaborDtl_LaborHrs],
	[LaborDtl].[BurdenHrs] as [LaborDtl_BurdenHrs],
	[LaborDtl].[ScrapReasonCode] as [LaborDtl_ScrapReasonCode],
	[LaborDtl].[IndirectCode] as [LaborDtl_IndirectCode],
	[TranGLC].[SegValue1] as [TranGLC_SegValue1],
	[TranGLC].[SegValue2] as [TranGLC_SegValue2],
	[TranGLC].[SegValue3] as [TranGLC_SegValue3],
	[TranGLC].[SegValue4] as [TranGLC_SegValue4],
	[TranGLC].[JournalCode] as [TranGLC_JournalCode],
	[TranGLC].[JournalNum] as [TranGLC_JournalNum],
	[TranGLC].[JournalLine] as [TranGLC_JournalLine],
	[GLJrnDtl].[SourceModule] as [GLJrnDtl_SourceModule],
	[TranGLC].[Company] as [TranGLC_Company],
	[TranGLC].[BookID] as [TranGLC_BookID],
	[GLBook].[CurrencyCode] as [GLBook_CurrencyCode],
	[GLJrnDtl].[PostedBy] as [GLJrnDtl_PostedBy],
	[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
	[LaborDtl].[CreateDate] as [LaborDtl_CreateDate]
from Erp.TranGLC as TranGLC
inner join Erp.GLAcctDisp as GLAcctDisp on 
	TranGLC.Company = GLAcctDisp.Company
	and TranGLC.COACode = GLAcctDisp.COACode
	and TranGLC.GLAccount = GLAcctDisp.GLAccount
inner join Erp.GLJrnDtl as GLJrnDtl on 
	TranGLC.Company = GLJrnDtl.Company
	and TranGLC.FiscalYear = GLJrnDtl.FiscalYear
	and TranGLC.JournalNum = GLJrnDtl.JournalNum
	and TranGLC.JournalLine = GLJrnDtl.JournalLine
	and TranGLC.JournalCode = GLJrnDtl.JournalCode
	and TranGLC.BookID = GLJrnDtl.BookID
inner join Erp.GLBook as GLBook on 
	TranGLC.Company = GLBook.Company
	and TranGLC.BookID = GLBook.BookID
left outer join Erp.LaborDtl as LaborDtl on 
	TranGLC.Company = LaborDtl.Company
	and TranGLC.Key1 = Convert(nvarchar,LaborDtl.LaborHedSeq)
	and TranGLC.Key2 = Convert(nvarchar,LaborDtl.LaborDtlSeq)
left outer join Erp.JobHead as LaborDtl_JobHead on 
	LaborDtl.Company = LaborDtl_JobHead.Company
	and LaborDtl.JobNum = LaborDtl_JobHead.JobNum
inner join Erp.EmpBasic as EmpBasic on 
	LaborDtl.Company = EmpBasic.Company
	and LaborDtl.EmployeeNum = EmpBasic.EmpID
where (TranGLC.JournalCode = 'IJ'  and TranGLC.RelatedToFile = 'LaborDtl')


select 
	[TranGLC].[FiscalYear] as [TranGLC_FiscalYear],
	[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
	[TranGLC].[TranDate] as [TranGLC_TranDate],
	[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
	[GLAcctDisp].[GLAcctDisp] as [GLAcctDisp_GLAcctDisp],
	[TranGLC].[BookDebitAmount] as [TranGLC_BookDebitAmount],
	[TranGLC].[BookCreditAmount] as [TranGLC_BookCreditAmount],
	[PartTran].[TranType] as [PartTran_TranType],
	(case  
     when JobHead.ProjectID > '' then JobHead.ProjectID
 		when PORel.ProjectID > '' then PORel.ProjectID
 		when OrderDtl.ProjectID > '' then OrderDtl.ProjectID
 		when PORel.GloviaProjectID_c > '' then PORel.GloviaProjectID_c
     else ''
 end) as [Calculated_CalcProject],
	[PartTran].[Plant] as [PartTran_Plant],
	((case when PartClass.Description <> '' then PartClass.Description else PartTran_PartClass.Description end)) as [Calculated_CalcPartClass],
	[PartTran].[EntryPerson] as [PartTran_EntryPerson],
	[PartTran].[PartNum] as [PartTran_PartNum],
	[PartTran].[PartDescription] as [PartTran_PartDescription],
	[PartTran].[TranQty] as [PartTran_TranQty],
	[PartTran].[DimCode] as [PartTran_DimCode],
	[Vendor].[Name] as [Vendor_Name],
	[PartTran].[PONum] as [PartTran_PONum],
	[PartTran].[PackSlip] as [PartTran_PackSlip],
	[PartTran].[PackNum] as [PartTran_PackNum],
	[PartTran].[InvAdjReason] as [PartTran_InvAdjReason],
	[PartTran].[InvoiceNum] as [PartTran_InvoiceNum],
	[PartTran].[JobNum] as [PartTran_JobNum],
	[TranGLC].[SegValue1] as [TranGLC_SegValue1],
	[TranGLC].[SegValue2] as [TranGLC_SegValue2],
	[TranGLC].[SegValue3] as [TranGLC_SegValue3],
	[TranGLC].[SegValue4] as [TranGLC_SegValue4],
	[TranGLC].[JournalCode] as [TranGLC_JournalCode],
	[TranGLC].[JournalNum] as [TranGLC_JournalNum],
	[TranGLC].[JournalLine] as [TranGLC_JournalLine],
	[GLJrnDtl].[SourceModule] as [GLJrnDtl_SourceModule],
	[TranGLC].[Company] as [TranGLC_Company],
	[TranGLC].[BookID] as [TranGLC_BookID],
	[GLBook].[CurrencyCode] as [GLBook_CurrencyCode],
	[GLJrnDtl].[PostedBy] as [GLJrnDtl_PostedBy],
	[PartTran].[SysDate] as [PartTran_SysDate],
	[PartTran].[TranDate] as [PartTran_TranDate]
from Erp.TranGLC as TranGLC
inner join Erp.GLAcctDisp as GLAcctDisp on 
	TranGLC.Company = GLAcctDisp.Company
	and TranGLC.COACode = GLAcctDisp.COACode
	and TranGLC.GLAccount = GLAcctDisp.GLAccount
inner join Erp.GLJrnDtl as GLJrnDtl on 
	TranGLC.Company = GLJrnDtl.Company
	and TranGLC.FiscalYear = GLJrnDtl.FiscalYear
	and TranGLC.JournalNum = GLJrnDtl.JournalNum
	and TranGLC.JournalLine = GLJrnDtl.JournalLine
	and TranGLC.JournalCode = GLJrnDtl.JournalCode
	and TranGLC.BookID = GLJrnDtl.BookID
inner join Erp.GLBook as GLBook on 
	TranGLC.Company = GLBook.Company
	and TranGLC.BookID = GLBook.BookID
left outer join Erp.PartTran as PartTran on 
	TranGLC.Company = PartTran.Company
	and TranGLC.Key3 = PartTran.TranNum
	and ( PartTran.PartNum = '11051'  )

left outer join Erp.JobHead as JobHead on 
	PartTran.Company = JobHead.Company
	and PartTran.JobNum = JobHead.JobNum
left outer join Erp.PODetail as PODetail on 
	PartTran.Company = PODetail.Company
	and PartTran.PONum = PODetail.PONUM
	and PartTran.POLine = PODetail.POLine
left outer join Erp.PartClass as PartClass on 
	PODetail.Company = PartClass.Company
	and PODetail.ClassID = PartClass.ClassID
left outer join Erp.Vendor as Vendor on 
	PartTran.Company = Vendor.Company
	and PartTran.VendorNum = Vendor.VendorNum
left outer join Erp.PORel as PORel on 
	PartTran.Company = PORel.Company
	and PartTran.PONum = PORel.PONum
	and PartTran.POLine = PORel.POLine
	and PartTran.PORelNum = PORel.PORelNum
left outer join Erp.OrderDtl as OrderDtl on 
	PartTran.Company = OrderDtl.Company
	and PartTran.OrderNum = OrderDtl.OrderNum
	and PartTran.OrderLine = OrderDtl.OrderLine
left outer join Erp.Part as Part on 
	PartTran.Company = Part.Company
	and PartTran.PartNum = Part.PartNum
left outer join Erp.PartClass as PartTran_PartClass on 
	Part.Company = PartTran_PartClass.Company
	and Part.ClassID = PartTran_PartClass.ClassID
where (TranGLC.JournalCode = 'IJ'  and TranGLC.RelatedToFile = 'PartTran')

---

## Post #7 by @jrmckelvy

Thanks Paul, I suppose one thing that is a bit confusing with the query you shared is that it does not come out the same as the crystal report. When I ran a DB trace I found a number of queries, but not sure how to bridge the data queried to the report format seen

![image|690x132](upload://4efdmZ4l5ORO5pqVkcDtIOWBTQW.png)

Trace:

    declare
@P1 varchar(8)='CC',
@P2 varchar(12)='MAIN',
@P3 datetime2='2021-07-02 00:00:00',
@P4 datetime2='2021-07-30 00:00:00',
@P5 varchar(30)='Labor Detail',
@P6 varchar(32)='LaborDtl'

SELECT "Company", "RelatedToFile", "Key1", "Key2", "Key3", "Key4", "Key5", "Key6", "TGLCTranNum",
"GLAcctContext", "BookID", "COACode", "GLAccount", "UserCanModify", "SegValue1", "SegValue2", "SegValue3", 
"SegValue4", "SegValue5", "SegValue6", "SegValue7", "SegValue8", "SegValue9", "SegValue10", "SegValue11", 
"SegValue12", "SegValue13", "SegValue14", "SegValue15", "SegValue16", "SegValue17", "SegValue18", "SegValue19", 
"SegValue20", "SysGLControlType", "SysGLControlCode", "ExtCompanyID", "IsExternalCompany", "SysRowID", "SysRevID", 
"FiscalYear", "JournalCode", "JournalNum", "JournalLine", "TranDate", "TranSource", "LaborHedSeq", "LaborDtlSeq", 
"SysDate_", "SysTime", "TranNum", "ARInvoiceNum", "TransAmt", "InvoiceLine", "SeqNum", "VendorNum", "APInvoiceNum",
"CreateDate", "FiscalYearSuffix", "FiscalCalendarID", "CreditAmount", "DebitAmount", "BookCreditAmount", 
"BookDebitAmount", "CurrencyCode", "RecordType", "BitFlag", "CorrAccUID", "ABTUID", "RuleUID", "Statistical", 
"StatUOMCode", "DebitStatAmt", "CreditStatAmt", PROGRESS_RECID, PROGRESS_RECID_IDENT_ FROM "dbo"."TranGLC" 
WHERE (("Company" = @P1) AND ("BookID" = @P2) AND ("TranDate" >= @P3) AND ("TranDate" < DATEADD(DAY,1, @P4)) 
AND ("SysGLControlType" = @P5) AND ("RelatedToFile" = @P6)) 

ORDER BY "Company", "RelatedToFile", "Key1", "Key2", "Key3", "Key4", "Key5", "Key6", "TGLCTranNum" 
--,'CC','MAIN','2021-07-02 00:00:00','2021-07-30 00:00:00','Labor Detail','LaborDtl'

Declare
@P1 varchar(8)='CC',
@P2 varchar(12)='MAIN',
@P3 datetime2='2021-07-02 00:00:00',
@P4 datetime2='2021-07-30 00:00:00',
@P5 varchar(32)='PartTran'

SELECT "Company", "RelatedToFile", "Key1", "Key2", "Key3", "Key4", "Key5", "Key6", "TGLCTranNum", "GLAcctContext",
"BookID", "COACode", "GLAccount", "UserCanModify", "SegValue1", "SegValue2", "SegValue3", "SegValue4", "SegValue5",
"SegValue6", "SegValue7", "SegValue8", "SegValue9", "SegValue10", "SegValue11", "SegValue12", "SegValue13",
"SegValue14", "SegValue15", "SegValue16", "SegValue17", "SegValue18", "SegValue19", "SegValue20",
"SysGLControlType", "SysGLControlCode", "ExtCompanyID", "IsExternalCompany", "SysRowID", "SysRevID",
"FiscalYear", "JournalCode", "JournalNum", "JournalLine", "TranDate", "TranSource", "LaborHedSeq",
"LaborDtlSeq", "SysDate_", "SysTime", "TranNum", "ARInvoiceNum", "TransAmt", "InvoiceLine", "SeqNum",
"VendorNum", "APInvoiceNum", "CreateDate", "FiscalYearSuffix", "FiscalCalendarID", "CreditAmount",
"DebitAmount", "BookCreditAmount", "BookDebitAmount", "CurrencyCode", "RecordType", "BitFlag",
"CorrAccUID", "ABTUID", "RuleUID", "Statistical", "StatUOMCode", "DebitStatAmt", "CreditStatAmt",
PROGRESS_RECID, PROGRESS_RECID_IDENT_ FROM "dbo"."TranGLC" 
WHERE (("Company" = @P1) AND ("BookID" = @P2) AND ("TranDate" >= @P3) AND ("TranDate" < DATEADD(DAY,1, @P4)) 
AND ("RelatedToFile" = @P5)) 

ORDER BY "Company", "RelatedToFile", "Key1", "Key2", "Key3", "Key4", "Key5", "Key6", "TGLCTranNum" 

--,'CC','MAIN','2021-07-02 00:00:00','2021-07-30 00:00:00','PartTran'

Declare
@P1 varchar(8)='CC',
@P2 datetime2='2021-07-02 00:00:00',
@P3 datetime2='2021-07-02 00:00:00',
@P4 varchar(7)='MFG-VEN',
@P5 tinyint=1,
@P6 tinyint=0

SELECT "Company", "SysDate_", "SysTime", "TranNum", "PartNum", "WareHouseCode", "BinNum", 
"TranClass", "TranType", "InventoryTrans", "TranDate", "TranQty", "UM", "MtlUnitCost", "LbrUnitCost", 
"BurUnitCost", "SubUnitCost", "MtlBurUnitCost", "ExtCost", "CostMethod", "JobNum", "AssemblySeq", 
"JobSeqType", "JobSeq", "PackType", "PackNum", "PackLine", "PONum", "POLine", "PORelNum", "WareHouse2", 
"BinNum2", "OrderNum", "OrderLine", "OrderRelNum", "EntryPerson", "TranReference", "PartDescription", 
"RevisionNum", "VendorNum", "PurPoint", "POReceiptQty", "POUnitCost", "PackSlip", "InvoiceNum", 
"InvoiceLine", "InvAdjSrc", "InvAdjReason", "LotNum", "DimCode", "DUM", "DimConvFactor", "LotNum2", 
"DimCode2", "DUM2", "DimConvFactor2", "GLTrans", "PostedToGL", "FiscalYear", "FiscalPeriod", "JournalNum", 
"Costed", "DMRNum", "ActionNum", "RMANum", "COSPostingReqd", "JournalCode", "Plant", "Plant2", "CallNum", 
"CallLine", "MatNum", "JobNum2", "AssemblySeq2", "JobSeq2", "CustNum", "RMALine", "RMAReceipt", "RMADisp", 
"OtherDivValue", "PlantTranNum", "NonConfID", "MtlMtlUnitCost", "MtlLabUnitCost", "MtlSubUnitCost", 
"MtlBurdenUnitCost", "RefType", "RefCode", "LegalNumber", "BeginQty", "AfterQty", "BegBurUnitCost", 
"BegLbrUnitCost", "BegMtlBurUnitCost", "BegMtlUnitCost", "BegSubUnitCost", "AfterBurUnitCost", 
"AfterLbrUnitCost", "AfterMtlBurUnitCost", "AfterMtlUnitCost", "AfterSubUnitCost", "PlantCostValue", 
"Character01", "Character02", "Character03", "Character04", "Character05", "Character06", "Character07", 
"Character08", "Character09", "Character10", "Number01", "Number02", "Number03", "Number04", "Number05", 
"Number06", "Number07", "Number08", "Number09", "Number10", "Date01", "Date02", "Date03", "Date04", 
"Date05", "CheckBox01", "CheckBox02", "CheckBox03", "CheckBox04", "CheckBox05", "EmpID", "ReconcileNum", 
"CostID", "FIFODate", "FIFOSeq", "ActTranQty", "ActTransUOM", "InvtyUOM", "InvtyUOM2", "FIFOAction", 
"FiscalYearSuffix", "FiscalCalendarID", "SysRowID", "SysRevID", "BinType", "CCYear", "CCMonth", "CycleSeq", 
"ABTUID", "BaseCostMethod", "RevertStatus", "RevertID", "DropShipPackSlip", "BitFlag", "VarTarget", 
"FIFOSubSeq", "AltMtlUnitCost", "AltLbrUnitCost", "AltBurUnitCost", "AltSubUnitCost", "AltMtlBurUnitCost", 
"AltExtCost", "AltMtlMtlUnitCost", "AltMtlLabUnitCost", "AltMtlSubUnitCost", "AltMtlBurdenUnitCost", 
"TranDocTypeID", "PBInvNum", "LoanFlag", "AssetNum", "AdditionNum", "DisposalNum", "ProjProcessed", 
"AsOfDate", "AsOfSeq", "MscNum", PROGRESS_RECID, PROGRESS_RECID_IDENT_ FROM "dbo"."PartTran" 
WHERE (("Company" = @P1) AND ("TranDate" >= @P2 AND "TranDate" < DATEADD(DAY,1, @P3)) AND ("TranType" <> @P4) 
AND ("GLTrans" = @P5) AND ("PostedToGL" = @P6)) 
ORDER BY "Company", "TranDate", PROGRESS_RECID 

--,'CC','2021-07-02 00:00:00','2021-07-02 00:00:00','MFG-VEN',1,0


Declare
@P1 varchar(8)='CC',
@P2 datetime2='2021-07-02 00:00:00',
@P3 datetime2='2021-07-02 00:00:00',
@P4 tinyint=0,
@P5 tinyint=0

SELECT 
"Company", "EmployeeNum", "LaborHedSeq", "LaborDtlSeq", "LaborType", "LaborTypePseudo", "ReWork", 
"ReworkReasonCode", "JobNum", "AssemblySeq", "OprSeq", "JCDept", "ResourceGrpID", "OpCode", "LaborHrs", 
"BurdenHrs", "LaborQty", "ScrapQty", "ScrapReasonCode", "SetupPctComplete", "Complete", "IndirectCode", 
"LaborNote", "ExpenseCode", "LaborCollection", "AppliedToSchedule", "ClockInMInute", "ClockOutMinute", 
"ClockInDate", "ClockinTime", "ClockOutTime", "ActiveTrans", "OverRidePayRate", "LaborRate", "BurdenRate", 
"DspClockInTime", "DspClockOutTime", "ResourceID", "OpComplete", "EarnedHrs", "AddedOper", "PayrollDate", 
"PostedToGL", "FiscalYear", "FiscalPeriod", "JournalNum", "GLTrans", "JournalCode", "InspectionPending", 
"CallNum", "CallLine", "ServNum", "ServCode", "ResReasonCode", "WipPosted", "DiscrepQty", "DiscrpRsnCode", 
"ParentLaborDtlSeq", "LaborEntryMethod", "Obsolete905_ServiceTechEntry", "Character01", "Character02", 
"Character03", "Character04", "Character05", "Character06", "Character07", "Character08", "Character09", 
"Character10", "Number01", "Number02", "Number03", "Number04", "Number05", "Number06", "Number07", "Number08",
"Number09", "Number10", "Date01", "Date02", "Date03", "Date04", "Date05", "CheckBox01", "CheckBox02", "CheckBox03",
"CheckBox04", "CheckBox05", "Number11", "Number12", "Number13", "Number14", "Number15", "Number16", "Number17", 
"Number18", "Number19", "Number20", "Date06", "Date07", "Date08", "Date09", "Date10", "Date11", "Date12", "Date13",
"Date14", "Date15", "Date16", "Date17", "Date18", "Date19", "Date20", "CheckBox06", "CheckBox07", "CheckBox08", 
"CheckBox09", "CheckBox10", "CheckBox11", "CheckBox12", "CheckBox13", "CheckBox14", "CheckBox15", "CheckBox16", 
"CheckBox17", "CheckBox18", "CheckBox19", "CheckBox20", "ShortChar01", "ShortChar02", "ShortChar03", "ShortChar04",
"ShortChar05", "ShortChar06", "ShortChar07", "ShortChar08", "ShortChar09", "ShortChar10", "FiscalYearSuffix", 
"FiscalCalendarID", "SysRowID", "SysRevID", "BFLaborReq", "BitFlag", "ABTUID", "ProjectID", "PhaseID", "RoleCd", 
"TimeTypCd", "PBInvNum", "PMUID", "TaskSetID", "ApprovedDate", "ClaimRef", "QuickEntryCode", "TimeStatus", 
"CreatedBy", "CreateDate", "CreateTime", "ChangedBy", "ChangeDate", "ChangeTime", "ActiveTaskID", "LastTaskID", 
"CreatedViaTEWeekView", "CurrentWFStageID", "WFGroupID", "WFComplete", "ApprovalRequired", "SubmittedBy", 
"PBRateFrom", "PBCurrencyCode", "PBHours", "PBChargeRate", "PBChargeAmt", "DocPBChargeRate", "Rpt1PBChargeRate", 
"Rpt2PBChargeRate", "Rpt3PBChargeRate", "DocPBChargeAmt", "Rpt1PBChargeAmt", "Rpt2PBChargeAmt", "Rpt3PBChargeAmt", 
"Shift", "ActID", "DtailID", "ProjProcessed", "AsOfDate", "AsOfSeq", PROGRESS_RECID, PROGRESS_RECID_IDENT_ 
FROM "dbo"."LaborDtl" WHERE (("Company" = @P1) 
AND ("PayrollDate" >= @P2 AND "PayrollDate" < DATEADD(DAY,1, @P3)) 
AND ("PostedToGL" = @P4) AND ("ActiveTrans" = @P5)) 
ORDER BY "Company", "PayrollDate", "PostedToGL", "ActiveTrans", PROGRESS_RECID 

--'CC','2021-07-02 00:00:00','2021-07-02 00:00:00',0,0


declare
@P1 varchar(8)='CC',
@P2 tinyint=1,
@P3 tinyint=0,
@P4 varchar(8)='CC',
@P5 datetime2='2021-07-02 00:00:00',
@P6 datetime2='2021-07-02 00:00:00',
@P7 tinyint=1

SELECT T0."Company", T0."InvoiceNum", T0."InvoiceLine", T0."LineType", T0."ContractNum", T0."XPartNum", 
T0."XRevisionNum", T0."PartNum", T0."LineDesc", T0."IUM", T0."RevisionNum", T0."POLine", T0."TaxExempt", 
T0."TaxCatID", T0."Commissionable", T0."DiscountPercent", T0."UnitPrice", T0."DocUnitPrice", T0."PricePerCode", 
T0."OurOrderQty", T0."ExtPrice", T0."DocExtPrice", T0."Discount", T0."DocDiscount", T0."TotalMiscChrg", 
T0."DocTotalMiscChrg", T0."ProdCode", T0."OurShipQty", T0."PackNum", T0."PackLine", T0."OrderNum", 
T0."OrderLine", T0."OrderRelNum", T0."ShipToCustNum", T0."ShipToNum", T0."ShipDate", T0."ShipViaCode", 
T0."AdvanceBillCredit", T0."DocAdvanceBillCredit", T0."CustNum", T0."InvoiceComment", T0."ShpConNum", 
T0."MtlUnitCost", T0."LbrUnitCost", T0."BurUnitCost", T0."SubUnitCost", T0."MtlBurUnitCost", T0."COSPostingReqd", 
T0."COSPosted", T0."Character01", T0."Character02", T0."Character03", T0."Character04", T0."Character05", 
T0."Character06", T0."Character07", T0."Character08", T0."Character09", T0."Character10", T0."Number01", 
T0."Number02", T0."Number03", T0."Number04", T0."Number05", T0."Number06", T0."Number07", T0."Number08", 
T0."Number09", T0."Number10", T0."Date01", T0."Date02", T0."Date03", T0."Date04", T0."Date05", 
T0."CheckBox01", T0."CheckBox02", T0."CheckBox03", T0."CheckBox04", T0."CheckBox05", T0."ContractCode", 
T0."CallNum", T0."CallCode", T0."RMANum", T0."RMALine", T0."SalesCatID", T0."FiscalYear", T0."FiscalPeriod", 
T0."JournalCode", T0."JournalNum", T0."SellingOrderQty", T0."SellingShipQty", T0."SalesUM", T0."SellingFactor", 
T0."ProjectID", T0."MilestoneID", T0."ListPrice", T0."DocListPrice", T0."OrdBasedPrice", T0."DocOrdBasedPrice", 
T0."Number11", T0."Number12", T0."Number13", T0."Number14", T0."Number15", T0."Number16", T0."Number17", 
T0."Number18", T0."Number19", T0."Number20", T0."Date06", T0."Date07", T0."Date08", T0."Date09", T0."Date10", 
T0."Date11", T0."Date12", T0."Date13", T0."Date14", T0."Date15", T0."Date16", T0."Date17", T0."Date18", 
T0."Date19", T0."Date20", T0."CheckBox06", T0."CheckBox07", T0."CheckBox08", T0."CheckBox09", T0."CheckBox10", 
T0."CheckBox11", T0."CheckBox12", T0."CheckBox13", T0."CheckBox14", T0."CheckBox15", T0."CheckBox16", T0."CheckBox17", 
T0."CheckBox18", T0."CheckBox19", T0."CheckBox20", T0."ShortChar01", T0."ShortChar02", T0."ShortChar03", T0."ShortChar04", 
T0."ShortChar05", T0."ShortChar06", T0."ShortChar07", T0."ShortChar08", T0."ShortChar09", 
T0."ShortChar10", T0."AdvGainLoss", T0."SellingFactorDirection", T0."RepRate1", T0."RepRate2", 
T0."RepRate3", T0."RepRate4", T0."RepRate5", T0."RepSplit1", T0."RepSplit2", T0."RepSplit3", 
T0."RepSplit4", T0."RepSplit5", T0."BTCustNum", T0."JCMtlUnitCost", T0."JCLbrUnitCost", 
T0."JCBurUnitCost", T0."JCSubUnitCost", T0."JCMtlBurUnitCost", T0."ChangedBy", T0."ChangeDate", 
T0."ChangeTime", T0."RevChargeMethod", T0."OverrideReverseCharge", T0."RevChargeApplied", 
T0."TaxConnectCalc", T0."GetDfltTaxIds", T0."Rpt1AdvanceBillCredit", T0."Rpt2AdvanceBillCredit", 
T0."Rpt3AdvanceBillCredit", T0."Rpt1Discount", T0."Rpt2Discount", T0."Rpt3Discount", T0."Rpt1ExtPrice", 
T0."Rpt2ExtPrice", T0."Rpt3ExtPrice", T0."Rpt1ListPrice", T0."Rpt2ListPrice", T0."Rpt3ListPrice", 
T0."Rpt1OrdBasedPrice", T0."Rpt2OrdBasedPrice", T0."Rpt3OrdBasedPrice", T0."Rpt1TotalMiscChrg", 
T0."Rpt2TotalMiscChrg", T0."Rpt3TotalMiscChrg", T0."Rpt1UnitPrice", T0."Rpt2UnitPrice", 
T0."Rpt3UnitPrice", T0."Rpt1AdvGainLoss", T0."Rpt2AdvGainLoss", T0."Rpt3AdvGainLoss", 
T0."FiscalYearSuffix", T0."FiscalCalendarID", T0."SysRowID", T0."SysRevID", T0."TaxRegionCode", 
T0."UseOTS", T0."OTSName", T0."OTSAddress1", T0."OTSAddress2", T0."OTSAddress3", T0."OTSCity", 
T0."OTSState", T0."OTSZIP", T0."OTSResaleID", T0."OTSTaxRegionCode", T0."OTSContact", T0."OTSFaxNum", 
T0."OTSPhoneNum", T0."OTSCountryNum", T0."Plant", T0."WarehouseCode", T0."CallLine", 
T0."DropShipPackLine", T0."DropShipPackSlip", T0."BitFlag", T0."FinChargeCode", T0."ABTUID", 
T0."InUnitPrice", T0."DocInUnitPrice", T0."InExtPrice", T0."DocInExtPrice", T0."InDiscount", 
T0."DocInDiscount", T0."InTotalMiscChrg", T0."DocInTotalMiscChrg", T0."InListPrice", T0."DocInListPrice", 
T0."InOrdBasedPrice", T0."DocInOrdBasedPrice", T0."Rpt1InDiscount", T0."Rpt2InDiscount", 
T0."Rpt3InDiscount", T0."Rpt1InExtPrice", T0."Rpt2InExtPrice", T0."Rpt3InExtPrice", T0."Rpt1InListPrice", 
T0."Rpt2InListPrice", T0."Rpt3InListPrice", T0."Rpt1InOrdBasedPrice", T0."Rpt2InOrdBasedPrice", 
T0."Rpt3InOrdBasedPrice", T0."Rpt1InTotalMiscChrg", T0."Rpt2InTotalMiscChrg", T0."Rpt3InTotalMiscChrg", 
T0."Rpt1InUnitPrice", T0."Rpt2InUnitPrice", T0."Rpt3InUnitPrice", T0."CorrectionDtl", T0."AssetNum", 
T0."DisposalNum", T0."IncomeTaxCode", T0."PBLineType", T0."InvoiceLineRef", T0."InvoiceRef", T0."LotNum", 
T0."PBInvoiceLine", T0."RAID", T0."RADtlID", T0."DeferredRev", T0."RACode", T0."DefRevStart", T0."ChargeDefRev", 
T0."RenewalNbr", T0.PROGRESS_RECID, T0.PROGRESS_RECID_IDENT_, T1."Company", T1."OpenInvoice", T1."ClosedDate", 
T1."CreditMemo", T1."UnappliedCash", T1."CheckRef", T1."InvoiceSuffix", T1."GroupID", T1."Posted", 
T1."InvoiceNum", T1."InvoiceType", T1."DeferredRevenue", T1."OrderNum", T1."CustNum", T1."PONum", 
T1."EntryPerson", T1."FOB", T1."TermsCode", T1."InvoiceDate", T1."DueDate", T1."FiscalYear", T1."FiscalPeriod", 
T1."GLPosted", T1."InvoiceComment", T1."InvoiceAmt", T1."DocInvoiceAmt", T1."InvoiceBal", T1."DocInvoiceBal", 
T1."UnpostedBal", T1."DocUnpostedBal", T1."DepositCredit", T1."DocDepositCredit", T1."SalesRepList", T1."InvoiceRef", 
T1."RefCancelled", T1."RefCancelledBy", T1."StartUp", T1."PayDates", T1."PayAmounts", T1."DocPayAmounts", 
T1."PayDiscDate", T1."PayDiscAmt", T1."DocPayDiscAmt", T1."BillConNum", T1."InvoiceHeld", T1."Character01", 
T1."Character02", T1."Character03", T1."Character04", T1."Character05", T1."Character06", T1."Character07", 
T1."Character08", T1."Character09", T1."Character10", T1."Number01", T1."Number02", T1."Number03", T1."Number04", 
T1."Number05", T1."Number06", T1."Number07", T1."Number08", T1."Number09", T1."Number10", T1."Date01", T1."Date02", 
T1."Date03", T1."Date04", T1."Date05", T1."CheckBox01", T1."CheckBox02", T1."CheckBox03", T1."CheckBox04", 
T1."CheckBox05", T1."CurrencyCode", T1."ExchangeRate", T1."LockRate", T1."JournalNum", T1."JournalCode", 
T1."LineType", T1."RMANum", T1."Plant", T1."CardMemberName", T1."CardNumber", T1."CardType", T1."ExpirationMonth", 
T1."ExpirationYear", T1."CardID", T1."CardmemberReference", T1."LegalNumber", T1."ExternalID", T1."XRefInvoiceNum", 
T1."Number11", T1."Number12", T1."Number13", T1."Number14", T1."Number15", T1."Number16", T1."Number17", 
T1."Number18", T1."Number19", T1."Number20", T1."Date06", T1."Date07", T1."Date08", T1."Date09", 
T1."Date10", T1."Date11", T1."Date12", T1."Date13", T1."Date14", T1."Date15", T1."Date16", T1."Date17", 
T1."Date18", T1."Date19", T1."Date20", T1."CheckBox06", T1."CheckBox07", T1."CheckBox08", T1."CheckBox09", 
T1."CheckBox10", T1."CheckBox11", T1."CheckBox12", T1."CheckBox13", T1."CheckBox14", T1."CheckBox15", 
T1."CheckBox16", T1."CheckBox17", T1."CheckBox18", T1."CheckBox19", T1."CheckBox20", T1."ShortChar01", 
T1."ShortChar02", T1."ShortChar03", T1."ShortChar04", T1."ShortChar05", T1."ShortChar06", 
T1."ShortChar07", T1."ShortChar08", T1."ShortChar09", T1."ShortChar10", T1."DepGainLoss", T1."DNComments", 
T1."DNCustNbr", T1."DebitNote", T1."SoldToCustNum", T1."Consolidated", T1."BillToInvoiceAddress", 
T1."SoldToInvoiceAddress", T1."ProcessCard", T1."RepComm1", T1."RepComm2", T1."RepComm3", T1."RepComm4", 
T1."RepComm5", T1."RepRate1", T1."RepRate2", T1."RepRate3", T1."RepRate4", T1."RepRate5", T1."RepSales1", 
T1."RepSales2", T1."RepSales3", T1."RepSales4", T1."RepSales5", T1."RepSplit1", T1."RepSplit2", 
T1."RepSplit3", T1."RepSplit4", T1."RepSplit5", T1."CMType", T1."CCStreetAddr", T1."CCZip", 
T1."ChangedBy", T1."ChangeDate", T1."ChangeTime", T1."ReadyToCalc", T1."AutoPrintReady", T1."EDIReady", 
T1."RecalcBeforePost", T1."Rounding", T1."DocRounding", T1."Rpt1DepositCredit", T1."Rpt2DepositCredit", 
T1."Rpt3DepositCredit", T1."Rpt1InvoiceAmt", T1."Rpt2InvoiceAmt", T1."Rpt3InvoiceAmt", 
T1."Rpt1InvoiceBal", T1."Rpt2InvoiceBal", T1."Rpt3InvoiceBal", T1."Rpt1PayAmounts", T1."Rpt2PayAmounts", 
T1."Rpt3PayAmounts", T1."Rpt1PayDiscAmt", T1."Rpt2PayDiscAmt", T1."Rpt3PayDiscAmt", T1."Rpt1Rounding", 
T1."Rpt2Rounding", T1."Rpt3Rounding", T1."Rpt1UnpostedBal", T1."Rpt2UnpostedBal", T1."Rpt3UnpostedBal", 
T1."RateGrpCode", T1."DocDepApplied", T1."Rpt1DepGainLoss", T1."Rpt2DepGainLoss", T1."Rpt3DepGainLoss", 
T1."ApplyDate", T1."FiscalYearSuffix", T1."FiscalCalendarID", T1."SysRowID", T1."SysRevID", T1."TaxPoint", 
T1."TaxRateDate", T1."TaxRegionCode", T1."LastChrgCalcDate", T1."TranDocTypeID", T1."TotFinChrg", 
T1."DocumentPrinted", T1."PayDiscDays", T1."PayDiscPer", T1."BlockedFinChrg", T1."BlockedFinChrgReason", 
T1."WithholdAmt", T1."DocWithholdAmt", T1."Rpt1WithholdAmt", T1."Rpt2WithholdAmt", T1."Rpt3WithholdAmt", 
T1."BlockedRemLetters", T1."PayDiscPartPay", T1."BlockedRemLettersReason", T1."BitFlag", T1."ShipDate", 
T1."CurrRateDate", T1."PIPayment", T1."PMUID", T1."UseAltBillTo", T1."InPrice", T1."CorrectionInv", 
T1."TaxRateGrpCode", T1."LockTaxRate", T1."SEBankRef", T1."GUITaxTypeCode", T1."GUIFormatCode", 
T1."GUIDeductCode", T1."GUIWineCigarette", T1."ReversalDocAmount", T1."OrigDueDate", T1."HeadNum", 
T1."ARLOCID", T1."ContractRef", T1."OurBank", T1."ContractDate", T1."PBProjectID", T1."DepositAmt", 
T1."GUIExportBillNumber", T1."DocDepositAmt", T1."GUIDateOfExport", T1."Rpt1DepositAmt", T1."GUIExportType", 
T1."Rpt2DepositAmt", T1."GUIExportMark", T1."Rpt3DepositAmt", T1."GUIExportBillType", T1."DepUnallocatedAmt", 
T1."SummarizationDate", T1."DocDepUnallocatedAmt", T1."BillingDate", T1."Rpt1DepUnallocatedAmt", T1."BillingNumber", 
T1."Rpt2DepUnallocatedAmt", T1."ReadyToBill", T1."Rpt3DepUnallocatedAmt", T1."OvrDefTaxDate", T1."XRefContractNum", 
T1."XRefContractDate", T1."MainSite", T1."SiteCode", T1."BranchID", T1."CustAgentName", T1."CustAgentTaxRegNo", 
T1."ExportType", T1."ExportReportNo", T1."RealEstateNo", T1."Excluded", T1."Deferred", T1.PROGRESS_RECID, 
T1.PROGRESS_RECID_IDENT_, T1."timestamp" 
FROM "dbo"."InvcDtl" T0, "dbo"."InvcHead" T1 
WHERE ((T0."Company" = @P1) AND (T0."COSPostingReqd" = @P2) AND (T0."COSPosted" = @P3) AND (T1."Company" = @P4) AND (T1."ApplyDate" >= @P5 
AND T1."ApplyDate" < DATEADD(DAY,1, @P6)) AND (T1."Posted" = @P7) AND (T0."InvoiceNum" = T1."InvoiceNum")) 

--'CC',1,0,'CC','2021-07-02 00:00:00','2021-07-02 00:00:00',1

---

## Post #8 by @PaulMorgan

Hi Justin,

I have not looked at the two statements in detail at all and cant vouch for their accuracy etc.  I just download the dashboard posted by Andy and installed it to get the information I posted (ie from the BAQ queries in the dashboard).  One is labour related and one is part related.

cheers
Paul

---


**Canonical:** https://www.epiusers.help/t/inventory-wip-reconciliation/36394
**Original content:** https://www.epiusers.help/t/inventory-wip-reconciliation/36394