SQL Series 1000010

System Configurations > Contract Application Calculation > Contract Application SQL Definition

SQL Series 1000010

Set Period Starting Point

If the target table was last run for an earlier period, Current Period values are moved across to Previous Period Values (with addition if required). Finally all Current Period Values are set to zero.

SQL Identifier	Notes and Comments
SQL 1000010 - 1	Copy N1 Costs from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 1 UPDATE tblProjectCosts SET PCCostN1Prev = PCCostN1, PCCostN1LastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>) AND (PCCostN1Prev <> PCCostN1);
SQL 1000010 - 2	Copy N2 Costs from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 2 UPDATE tblProjectCosts SET PCCostN2Prev = PCCostN2, PCCostN2LastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>) AND (PCCostN2Prev <> PCCostN2);
SQL 1000010 - 3	Copy N3 Costs from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 3 UPDATE tblProjectCosts SET PCCostN3Prev = PCCostN3, PCCostN3LastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>) AND (PCCostN3Prev <> PCCostN3);
SQL 1000010 - 4	Copy N4 Costs from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 4 UPDATE tblProjectCosts SET PCCostN4Prev = PCCostN4, PCCostN4LastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>) AND (PCCostN4Prev <> PCCostN4);
SQL 1000010 - 5	Copy N99 Costs from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 5 UPDATE tblProjectCosts SET PCCostN99Prev = PCCostN99, PCCostN99LastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>) AND (PCCostN99Prev <> PCCostN99);
SQL 1000010 - 6	Copy Application Values for N1, N2, N3, N4, N99 and Total from previous run in to Previous Results Column if this is a first run in the current period. SQL 1000010 - 6 UPDATE tblProjectCosts SET PCPrevN1 = PCApplicN1, PCPrevN2 = PCApplicN2, PCPrevN3 = PCApplicN3, PCPrevN4 = PCApplicN4, PCPrevN99 = PCApplicN99, PCApplicTotalPrevPeriod = (PCApplicN1 + PCApplicN2 + PCApplicN3 + PCApplicN4), PCLastPeriod = PCCurrentPeriod WHERE (PCCurrentPeriod < <CURRENTPERIOD>);
SQL 1000010 - 7	Selects Scheme Numbers by Scheme Number Range - adding entries not already in the Application Table. 200000 - 799900, 990000, 100270. SQL 1000010 - 7 BLOCK RECAST SELECT ProjAutoGUID AS OBJECTGUID FROM tblProjects WHERE ((ProjSerialNo Between 200000 And 799900) OR (ProjSerialNo Between 100270 And 100279)) AND (ProjAutoGUID NOT IN (SELECT PCProjectGUID FROM tblProjectCosts)) APPLY AS INSERT INTO tblProjectCosts (PCProjectGUID) VALUES (<OBJECTGUID>);
SQL 1000010 - 8	Set Current Period N1, N2, N3, N4, N99 values to zero - and clear the CostsInCurrPeriod flag as a starting setup. SQL 1000010 - 8 UPDATE tblProjectCosts SET PCCurrN1 = 0, PCCurrN2 = 0, PCCurrN3 = 0, PCCurrN4 = 0, PCCurrN99 = 0, PCCostInCurrPeriod = 0;
SQL 1000010 - 9	Sets the Current Period value on all Records in the Application table. SQL 1000010 - 9 UPDATE tblProjectCosts SET PCCurrentPeriod = <CURRENTPERIOD>;
SQL 1000010 - 10	Identifies Schemes where Staff Time is allocated to the current Period through Timesheet Allocations on Activities, Works Stages. CostsInCurrPeriod flag is set for these. SQL 1000010 - 10 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM (tblStaffTimeSheetAlloc INNER JOIN (tblProjectWorksStage INNER JOIN tblProjectActivityCode ON tblProjectWorksStage.PWSStageId = tblProjectActivityCode.PACWorkStageId) ON tblStaffTimeSheetAlloc.STSProjectActCodeId = tblProjectActivityCode.PACActivityId) INNER JOIN tblProjects ON tblProjectWorksStage.PWSProjectId = tblProjects.ProjectId WHERE STSCostInvoiceNo = <CURRENTPERIOD>));
SQL 1000010 - 11	Identifies Schemes where Labour is allocated to the current Period through Allocation Sheets and Works Orders / Schemes Links. CostsInCurrPeriod flag is set for these. SQL 1000010 - 11 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM (tblLabPlantASLabour INNER JOIN tblWorksOrders ON tblLabPlantASLabour.ASAWOId = tblWorksOrders.WorksOrderId) INNER JOIN tblProjects ON tblWorksOrders.WOProjectId = tblProjects.ProjectId WHERE ASAInvoicePeriod = <CURRENTPERIOD>));
SQL 1000010 - 12	Identifies Schemes where Plant is allocated to the current Period through Allocation Sheets and Works Orders / Schemes Links. CostsInCurrPeriod flag is set for these. SQL 1000010 - 12 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM (tblLabPlantASPlant INNER JOIN tblWorksOrders ON tblLabPlantASPlant.ASAWOId = tblWorksOrders.WorksOrderId) INNER JOIN tblProjects ON tblWorksOrders.WOProjectId = tblProjects.ProjectId WHERE ASAInvoicePeriod = <CURRENTPERIOD>));
SQL 1000010 - 13	Identifies Schemes where Stock Materials is allocated to the current Period through Allocation Sheets and Works Orders / Schemes Links. CostsInCurrPeriod flag is set for these. SQL 1000010 - 13 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM (tblLabPlantASMaterials INNER JOIN tblWorksOrders ON tblLabPlantASMaterials.ASAWOId = tblWorksOrders.WorksOrderId) INNER JOIN tblProjects ON tblWorksOrders.WOProjectId = tblProjects.ProjectId WHERE ASAInvoicePeriod = <CURRENTPERIOD>));
SQL 1000010 - 14	Identifies Schemes where PL Materials (Imported) is allocated to the current Period through held Imported Data tables - tblMaterialsInvoiced. CostsInCurrPeriod flag is set for these. SQL 1000010 - 14 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM tblMaterialsInvoiced INNER JOIN (tblWorksOrders INNER JOIN tblProjects ON tblWorksOrders.WOProjectId = tblProjects.ProjectId) ON tblMaterialsInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId WHERE TINVInvoicePeriod = <CURRENTPERIOD>));
SQL 1000010 - 15	Identifies Schemes where SubContract Costs (Imported) is allocated to the current Period through held Imported Data tables - tblSubcontractInvoiced. CostsInCurrPeriod flag is set for these. SQL 1000010 - 15 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1 WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM tblSubcontractInvoiced INNER JOIN (tblWorksOrders INNER JOIN tblProjects ON tblWorksOrders.WOProjectId = tblProjects.ProjectId) ON tblSubcontractInvoiced.TINVWorksOrderId = tblWorksOrders.WorksOrderId WHERE TINVInvoicePeriod = <CURRENTPERIOD>));
SQL 1000010 - 16	Sets flags to force Costs In Current Period, and the Is DCP Flag, for Schemes in the 100270 Series. SQL 1000010 - 16 UPDATE tblProjectCosts SET PCCostInCurrPeriod = 1, PCSchemeDCP = 1 WHERE PCProjectGUID IN (SELECT DISTINCT ProjAutoGuid FROM tblProjects WHERE ProjSerialNo Between 100270 And 100279);
SQL 1000010 - 17	Sets all columns in the table for Costs and Application Values to Zero where Costs In Current Period have been identified. N1, N2, N3, N4 and Other set. SQL 1000010 - 17 UPDATE tblProjectCosts SET PCCostN1 = 0, PCCostN2 = 0, PCCostN3 = 0, PCCostN4Other = 0, PCCostN4Base = 0, PCCostN4 = 0, PCCostN99 = 0, PCApplicN1 = 0, PCApplicN2 = 0, PCApplicN3 = 0, PCApplicN4 = 0, PCApplicN99 = 0 WHERE PCCostInCurrPeriod = 1;