Inventory/WIP Reconciliation

[Mel (Mel Malay)]

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

Thanks

Mel

[Mike (Mike Watkinson)]

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.

[amaragni (al maragni (USA - NY/NJ))]

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

[acicala (Andy Cicala)]

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.commailto:Acicala@trigemco.com

InvWIPReconDB.DBD (574 KB)

[jrmckelvy (Justin)]

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

[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’)

[jrmckelvy (Justin)]

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

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

[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