SQL Series 1000060

System Configurations > Contract Application Calculation > Contract Application SQL Definition

SQL Series 1000060

Set Calculations and Values on the Target Application Record

A series of SQL Scripts that completes all calculations - totaling Values and Applying Pain / Gain Stages.

SQL Identifier	Notes and Comments
SQL 1000060 - 1	Set details for all Schemes fo Scheme Target Price, Final Account and Variation (Pain / Gain) to zero.. SQL 1000060 - 1 UPDATE tblProjectCosts SET PCCostN4Price = 0, PCCostN4FinalAccount = 0, PCSchemeCosted = 0, PCCostN4Variation = 0 WHERE (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 2	Set Scheme Target Prices for all schemes in to column PCCostN4Price. Prices taken from Scheme SPS Table and includes Inflation Uplifts (no Fees Uplift). SQL 1000060 - 2 BLOCK RECAST SELECT ProjAutoGUID AS OBJECTGUID, ROUND(((PIFBudgetLabour+ PIFBudgetWAOH+ PIFBudgetPlant+ PIFBudgetMaterials+ PIFBudgetSubContract+PIFBudgetOther)*PIFInflUplift), 2) AS PCBASEPRICE FROM (tblProjects INNER JOIN tblPIFProjects ON tblProjects.ProjectId = tblPIFProjects.PProjectId) INNER JOIN tblPIFs ON tblPIFProjects.PPIFIdentifier = tblPIFs.PIFIdentifier WHERE (PPTitleProject = 1) APPLY AS UPDATE tblProjectCosts SET PCCostN4Price = <PCBASEPRICE> WHERE (PCProjectGUID = <OBJECTGUID>) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 3	Set Scheme Final Account Costs for all schemes that have been Final Accounted in to column PCCostN4FinalAccount. Costs taken from Scheme SPS Table tblPIFPriceCostCompare (LPMSO). SQL 1000060 - 3 BLOCK RECAST SELECT ProjAutoGUID AS OBJECTGUID, Sum(PCCCostL+ PCCCostP + PCCCostM + PCCCostS + PCCCostO) AS PCFACOSTS FROM ((tblProjects INNER JOIN tblPIFProjects ON tblProjects.ProjectId = tblPIFProjects.PProjectId) INNER JOIN tblPIFs ON tblPIFProjects.PPIFIdentifier = tblPIFs.PIFIdentifier) INNER JOIN tblPIFPriceCostCompare ON tblPIFs.PIFIdentifier = tblPIFPriceCostCompare.PCCPIFId WHERE (PPTitleProject = 1) AND (PIFStatus = 5001) GROUP BY ProjAutoGUID APPLY AS UPDATE tblProjectCosts SET PCCostN4FinalAccount = <PCFACOSTS> WHERE (PCFinalAccLocked = <FALSE>) And (PCProjectGUID = <OBJECTGUID>);
SQL 1000060 - 4	Set the DCP Flag for All Schemes that come under DCP Costs range. Normally this is set earlier - but added here in the case when it is the FIRST occurrence of Costs in any Application. SQL 1000060 - 4 UPDATE tblProjectCosts SET PCSchemeDCP = 1 WHERE PCProjectGUID IN (SELECT ProjAutoGUID FROM tblProjects INNER JOIN tblMasterProjectList ON tblProjects.ProjMasterId = tblMasterProjectList.MPLProjectId WHERE (MPLSeriesNo = 100270));
SQL 1000060 - 5	Sets the Scheme Is costed flag for all prime Schemes. These range 210000 to 259999, 270000 to 299999, 410000 to 499999. This used later to pick out Pain / Gain schemes. SQL 1000060 - 5 UPDATE tblProjectCosts SET PCSchemeCosted = 1 WHERE PCProjectGUID IN (SELECT ProjAutoGUID FROM tblProjects WHERE (ProjSerialNo BETWEEN 210000 AND 259999) OR (ProjSerialNo BETWEEN 270000 AND 299999) OR (ProjSerialNo BETWEEN 410000 AND 499999)) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 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 1000060 - 6 UPDATE tblProjectCosts SET PCCostN4Base = PCCostN4FinalAccount WHERE (PCCostN4FinalAccount > 0) AND (PCSchemeCosted = 1) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 7	Set the Previous Period Costs for N1, N2, N3, N4, N99 where the Last Period flag equals the (Current Period - )1- i.e. already is the previous period and no Fees Uplift is set. This seeds the records. SQL 1000060 - 7 UPDATE tblProjectCosts SET PCPrevN1 = 0, PCPrevN2 = 0, PCPrevN3 = 0, PCPrevN4 = 0, PCPrevN99 = 0, PCLastPeriod = (<CurrentPeriod> - 1) WHERE (PCFeesUplift = 0);
SQL 1000060 - 8	Set the Contract Fees Uplift percentage - IMPORTANT. This sets from [tbsSettings]. SQL 1000060 - 8 BLOCK RECAST SELECT (CAST(SettingValue AS Float)+1) As FEESUPLIFT FROM tbsSettings where SettingGroup = 'System' And SettingName = 'PricingFeesOhead' Apply As UPDATE tblProjectCosts SET PCFeesUplift = <FEESUPLIFT> WHERE (PCFeesUplift = 0);
SQL 1000060 - 9	Establish the Difference between Cost Target Price for all Pain / Gain subjected schemes. SQL 1000060 - 9 UPDATE tblProjectCosts SET PCCostN4Variation = (PCCostN4Base - PCCostN4Price) / PCCostN4Price WHERE (PCCostN4FinalAccount > 0) AND (PCSchemeCosted = 1) AND (PCCostN4Price > 0) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 10	Establish the Pain Gain element within 20% Variation for all Pain / Gain subjected schemes. SQL 1000060 - 10 UPDATE tblProjectCosts SET PCCostN4PainGain = (PCCostN4Price - PCCostN4Base) * 0.5 WHERE (PCCostN4Variation BETWEEN -0.2 and 0.2) AND (PCSchemeCosted = 1) AND (PCCostN4FinalAccount > 0) AND (PCCostN4Price > 0) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 11	Establish the Pain Gain element for over 20% Variation for all Pain / Gain subjected schemes. SQL 1000060 - 11 UPDATE tblProjectCosts SET PCCostN4PainGain = ((PCCostN4Price * 1.2) - PCCostN4Base) * 0.25 - (PCCostN4Price * 0.1) WHERE (PCCostN4Variation > 0.2) AND (PCSchemeCosted = 1) AND (PCCostN4FinalAccount > 0) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 12	Set the overall Pain / Gain figure for under 20% variation - for all Pain / Gain subjected schemes. SQL 1000060 - 12 UPDATE tblProjectCosts SET PCCostN4PainGain = (PCCostN4Price * 0.1) + ((PCCostN4Price * 0.8) - PCCostN4Base) * 0.25 WHERE (PCCostN4Variation < -0.2) AND (PCSchemeCosted = 1) AND (PCCostN4FinalAccount > 0) And (PCFinalAccLocked = <FALSE>);
SQL 1000060 - 13	Calculate all Application To Date Totals - N1, N2, N3, N4, N99 and Application To Date. SQL 1000060 - 13 UPDATE tblProjectCosts SET PCApplicN1 = ROUND(PCCostN1 * PCFeesUplift,2), PCApplicN2 = ROUND(PCCostN2 * PCFeesUplift,2), PCApplicN3 = ROUND(PCCostN3 * PCFeesUplift,2), PCCostN4 = (PCCostN4Base + PCCostN4Other + PCCostN4PainGain), PCApplicN4 = ROUND((PCCostN4Base + PCCostN4Other + PCCostN4PainGain) * PCFeesUplift,2), PCApplicTotalToDate = ROUND(PCCostN1 * PCFeesUplift,2) + ROUND(PCCostN2 * PCFeesUplift,2) + ROUND(PCCostN3 * PCFeesUplift,2) + ROUND((PCCostN4Base + PCCostN4Other + PCCostN4PainGain) * PCFeesUplift,2), PCApplicN99 = PCPrevN99 + PCCostN99;
SQL 1000060 - 14	Calculate all Application Current Period Totals - N1, N2, N3, N4, N99 - Schemes with Costs in Current Period processed. SQL 1000060 - 14 UPDATE tblProjectCosts SET PCCurrN1 = PCApplicN1 - PCPrevN1, PCCurrN2 = PCApplicN2 - PCPrevN2, PCCurrN3 = PCApplicN3 - PCPrevN3, PCCurrN4 = PCApplicN4 - PCPrevN4;
SQL 1000060 - 15	Set Flag on Final Account to 'TRUE' as these are now all calculated. SQL 1000060 - 15 UPDATE tblProjectCosts SET PCFinalAccLocked = <TRUE> Where (PCFinalAccLocked = <FALSE>) And (PCCostN4FinalAccount > 0);
SQL 1000060 - 16	Calculate all Application Current Period Totals - N99 - Schemes with Costs in Current Period processed. SQL 1000060 - 16 UPDATE tblProjectCosts SET PCCurrN99 = PCApplicN99 - PCPrevN99 WHERE (PCCostInCurrPeriod = 1);