SQL Series 1000040

System Configurations > Contract Application Calculation > Contract Application SQL Definition

SQL Series 1000040

Post the Allocated Costs for the Current Period

Each SQL Script returns the Total Allocated Cost for each Scheme Record that have been allocated to the target Invoice Period. The returned record set must include the Target GUID Identifier as the first column field.

A separate SQL is used for each Class of Allocation Cost, with conditions being applied to define the target Scheme Class, and Works Stage (for Staff Time).

The SQL Script gives the Target Field Name on returned columns.

SQL Identifier	Notes and Comments
SQL 1000040 - 1	Totals Scheme Costs To Date for N1 From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MSST (4). SQL 1000040 - 1 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN1 From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 4) And (tblProjectCosts.PCCostInCurrPeriod = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN1 From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 4 And tblProjectCosts.PCCostInCurrPeriod = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN1 From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 4 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN1 From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 4 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN1 From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 4 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN1 From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 4 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 2	Totals Scheme Costs To Date for N2 From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MSDE (5). SQL 1000040 - 2 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN2 From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 5) And (tblProjectCosts.PCCostInCurrPeriod = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN2 From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 5 And tblProjectCosts.PCCostInCurrPeriod = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN2 From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 5 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN2 From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 5 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN2 From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 5 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN2 From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 5 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 3	Totals Scheme Costs To Date for N3 From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MSRC (7). SQL 1000040 - 3 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN3 From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 7) And (tblProjectCosts.PCCostInCurrPeriod = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN3 From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 7 And tblProjectCosts.PCCostInCurrPeriod = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN3 From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 7 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN3 From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 7 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN3 From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 7 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN3 From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 7 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 4	Totals Scheme Costs To Date for N4 (Base) From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MSWK (6). SQL 1000040 - 4 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN4Base From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 6) And (tblProjectCosts.PCCostInCurrPeriod = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN4Base From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 6 And tblProjectCosts.PCCostInCurrPeriod = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN4Base From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 6 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN4Base From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 6 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN4Base From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 6 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN4Base From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 6 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 5	Totals Scheme Costs To Date for N4 (Other) From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MCMP (11). SQL 100040 - 5 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN4Other From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 11) And (tblProjectCosts.PCCostInCurrPeriod = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN4Other From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 11 And tblProjectCosts.PCCostInCurrPeriod = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN4Other From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 11 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN4Other From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 11 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN4Other From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 11 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN4Other From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 11 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 6	Set the Final Account Cost in to PCCostN4Base for schemes that have been final accounted - and are schemes subject to Pain / Gain. This overrides the Costs taken from the standard live Cost Tables. SQL 1000040 - 6 SELECT tblProjects.ProjAutoGUID, Sum(ROUND(tblStaffTimeSheetAlloc.STSHoursBooked * ROUND((tblStaffRates.SRSalary + tblStaffRates.SRExpenses + tblStaffRates.SRCarAllowance) / tblStaffRates.SRTotalHours * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN3 From (tblMasterWorksStage Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join (((((tblStaffTimeSheetAlloc Inner Join (tblProjectWorksStage Inner Join tblProjectActivityCode On tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) On tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) Inner Join tblStaffRates On tblStaffTimeSheetAlloc.STSStaffRateId = tblStaffRates.SRIdentifier) Inner Join tblHAGradeUplifts On tblStaffRates.SRHAGrade = tblHAGradeUplifts.UpliftRateGrade) Inner Join tblDates On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo And tblStaffTimeSheetAlloc.STSDate = tblDates.DayDate) Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId) On tblMasterWorksStage.MWSStageId = tblProjectWorksStage.PWSMasterId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where (ltbWSCostClass.WSClassId = 10) And (tblProjectCosts.PCSchemeDCP = 1) And tblStaffTimeSheetAlloc.STSCostsInvoiced = 1 And tblStaffTimeSheetAlloc.STSCostInvoiceNo <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASLabour.ASADuration * ROUND(tblLabPlantASLabour.ASARate * tblHAGradeUplifts.UpliftRate * ltbWSCostClass.WSClassFactor, 2), 2)) As PCCostN3 From (((((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblLabPlantASLabour On tblWorksOrders.WorksOrderId = tblLabPlantASLabour.ASAWOId) Inner Join tblDates On tblLabPlantASLabour.ASAAllocSheetDate = tblDates.DayDate) Inner Join tblHAGradeUplifts On tblDates.AprilAnnual = tblHAGradeUplifts.UpliftYearNo) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where tblHAGradeUplifts.UpliftRateGrade = 4 And ltbWSCostClass.WSClassId = 10 And tblProjectCosts.PCSchemeDCP = 1 And tblLabPlantASLabour.ASAInvoiced = 1 And tblLabPlantASLabour.ASAInvoicePeriod <= <CURRENTPERIOD> Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum((ROUND(tblLabPlantASPlant.ASARate * tblLabPlantASPlant.ASADuration, 2) + tblLabPlantASPlant.ASACharge) * ltbWSCostClass.WSClassFactor * tblLabPlantASPlant.ASAQuantity) As PCCostN3 From (((tblWorksOrders Inner Join tblLabPlantASPlant On tblWorksOrders.WorksOrderId = tblLabPlantASPlant.ASAWOId) Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 10 And tblLabPlantASPlant.ASAInvoiced = 1 And tblLabPlantASPlant.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCSchemeDCP = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(ROUND(tblLabPlantASMaterials.ASAUnitCost * tblLabPlantASMaterials.ASAQuantity * ltbWSCostClass.WSClassFactor, 2)) As PCCostN3 From (((tblWorksOrders Left Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Left Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Left Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblLabPlantASMaterials On tblWorksOrders.WorksOrderId = tblLabPlantASMaterials.ASAWOId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 10 And tblLabPlantASMaterials.ASAInvoiced = 1 And tblLabPlantASMaterials.ASAInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCSchemeDCP = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblMaterialsInvoiced.TINVNetValue) As PCCostN3 From (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) Inner Join tblMaterialsInvoiced On tblWorksOrders.WorksOrderId = tblMaterialsInvoiced.TINVWorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 10 And tblMaterialsInvoiced.TINVInvoiced = 1 And tblMaterialsInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCSchemeDCP = 1 Group By tblProjects.ProjAutoGUID UNION Select tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValue) As PCCostN3 From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 10 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCSchemeDCP = 1 Group By tblProjects.ProjAutoGUID;
SQL 1000040 - 7	Totals Scheme Costs To Date for N4 (Other) From Staff, Labour, Plant, Materials, PL Materials, SubContract. Only Schemes with Costs in Period and for Schemes Works Stage Cost Class = MCMF (12). SQL 1000040 - 7 SELECT tblProjects.ProjAutoGUID, Sum(tblSubcontractInvoiced.TINVNetValueCMF) As PCCostN99 From tblSubcontractInvoiced Inner Join (((tblWorksOrders Inner Join tblProjectWorksStage On tblWorksOrders.WOPWorksStageId = tblProjectWorksStage.PWSStageId) Inner Join tblMasterWorksStage On tblProjectWorksStage.PWSMasterId = tblMasterWorksStage.MWSStageId) Inner Join ltbWSCostClass On tblMasterWorksStage.MWSClassification = ltbWSCostClass.WSClassId) On tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId Inner Join tblProjects On tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId Inner Join tblProjectCosts On tblProjects.ProjAutoGUID = tblProjectCosts.PCProjectGUID Where ltbWSCostClass.WSClassId = 12 And tblSubcontractInvoiced.TINVInvoiced = 1 And tblSubcontractInvoiced.TINVInvoicePeriod <= <CURRENTPERIOD> And tblProjectCosts.PCCostInCurrPeriod = 1 Group By tblProjects.ProjAutoGUID;