December 12, 2023 at 6:09 pm
Below is a code i wanted to improve some readability and functionality:
GO
/****** Object: View [Prod].
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [Prod]. AS
---------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
MAN_ADJ_ALL.[FY]
,MAN_ADJ_ALL.[Period]
,MAN_ADJ_ALL.[Group]
--ADJUST THIRD PARTY TO ALLOCATED PLANT
,CASE WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Plant] ELSE MAN_ADJ_ALL.[Plant] END AS [Plant]
,MAN_ADJ_ALL.[Structure]
--ADJUST THIRD PARTY TO ALLOCATED Business Process
,CASE WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Business Process Activity] ELSE MAN_ADJ_ALL.[Business Process Activity] END AS [Business Process Activity]
,MAN_ADJ_ALL.[Cost Element]
--Adjustment of value of cost allocated, the split was informed by business and agreed, check with business yearly herein.
,CASE
WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' AND MAN_ADJ_ALL.[Structure] LIKE 'PB1' THEN
CASE
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA03' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Handling' THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA03' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Repair' THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA01' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Repair' THEN MAN_ADJ_ALL.[Value Adjustment] - MAN_ADJ_3RDPARTY.Amount
END--THIS IS AN AGREED APPROACH BY CONTROLLERSHIP (MOHAMMED JOOSUB)WITH SUPPLYCHAIN (DEBBIE ROWLAND IN 2022). THE 70%/30% SPLIT IS AN AGREED % SPLIT THAT OCCURS FOR ALL PLANT RELATED COSTS. STANDARD RULE.
ELSE
MAN_ADJ_ALL.[Value Adjustment]
END AS [Value]
FROM
(--adjustment of format to match into the plant metrics cost model
SELECT
[FY]
,[Period]
,MAN_ADJ.[AME Product Group] AS [Group]
,'ZA01' AS [Plant]
,'PB1' AS [Structure]
,CASE
WHEN MAN_ADJ.Metric IN('Fixed Depreciation','Fixed Labour Salary','Fixed Repairs and Maint.','Fixed Running Costs','Variable 3rdP Cycle1','Variable Consumption','Variable Labour','Variable Waste Disposal') THEN 'Repair'
WHEN MAN_ADJ.Metric IN('Fixed Rent Buiding','Fixed Forklift Costs','Variable Transport Costs') THEN 'Handling'
ELSE
'Not assigned'
END AS [Business Process Activity]
,MAN_ADJ.Metric AS [Cost Element]
,[Metric Value] *-1 AS [Value Adjustment]
FROM
(
SELECT
--Do not remove, this will help identify if there are issues with the datasource
--[Datasource]
--,
[FY]
,[Period]
,[AME Product Group]
,[Metric]
--Note that for pallets an expected variance in the value is expceted and is added to the plant metrics costs and aligned. But for other service lines/AME Product categories we expected a small cent variance.
,SUM([Metric Value]) AS [Metric Value]
FROM
(
SELECT
'MEA SLA' AS [Datasource]
--MEA SLA has been checked against the manual file and aligns for the period and service lines.
,[FY]
,[Period]
,CASE
WHEN [AME Product Group] LIKE 'Pallets assets' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'Automobile Assets' THEN 'Auto'
WHEN [AME Product Group] LIKE 'RC1' THEN 'Crates'
WHEN [AME Product Group] LIKE 'RB1' THEN 'Wooden Bins'
WHEN [AME Product Group] LIKE 'RB2' THEN 'Plastic Bins'
WHEN [AME Product Group] LIKE 'BC1' THEN 'IBC'
WHEN [AME Product Group] LIKE 'Customer Owned' THEN 'Cust Owned'
ELSE
'Not assigned'
END AS [AME Product Group]
,CASE
WHEN [Measure level two] LIKE 'Fixed Depreciation' THEN 'Fixed Depreciation'
WHEN [Measure level two] LIKE 'Fixed Forklift Costs' THEN 'Fixed Forklift Costs'
WHEN [Measure level two] LIKE 'Fixed Labour Salary' THEN 'Fixed Labour Salary'
WHEN [Measure level two] LIKE 'Fixed Rent Building' THEN 'Fixed Rent Buiding'
WHEN [Measure level two] LIKE 'Fixed Repairs and main' THEN 'Fixed Repairs and Maint.'
WHEN [Measure level two] LIKE 'Fixed Running Costs' THEN 'Fixed Running Costs'
WHEN [Measure level two] LIKE 'TP Service Centre' THEN 'TP Service Centre'
WHEN [Measure level two] LIKE 'Direct 3rd Party Heat' THEN 'Variable 3rdP Cycle1'--'TP Service Centre'--this was a business decision to included herein within the service centre. Adjust as per business.
WHEN [Measure level two] LIKE 'Variable Third Pary' THEN 'Variable 3rdP Cycle1' --Was Variable 3rdP Cycle1
WHEN [Measure level two] LIKE 'Variable Consumption' THEN 'Variable Consumption'
WHEN [Measure level two] LIKE 'Variable Labour' THEN 'Variable Labour '
WHEN [Measure level two] LIKE 'Variable waste Disposa' THEN 'Variable Waste Disposal'
WHEN [Measure level two] LIKE 'Variable Transport cos' THEN 'Variable Transport Costs'
WHEN [Measure level two] LIKE 'Variable Transport C' THEN 'Variable Transport Costs'
ELSE
'Not assigned'
END AS [Metric]
,[Value] *-1 AS [Metric Value]
FROM [Prod]. AS MEA_SLA
WHERE
[Measure level two] IN(
'Fixed Depreciation'--included
,'Fixed Forklift Costs'--included
,'Fixed Labour Salary'--included
,'Fixed Rent Building'--Included
,'Fixed Repairs and main'--included
,'Fixed Running Costs'--included
,'TP Service Centre'--included
,'Direct 3rd Party Heat'--included
,'Variable Third Pary'--included
,'Variable Consumption'--included
,'Variable Labour '--included
,'Variable waste Disposa'--included
,'Variable Transport cos'--included
,'Variable Transport C'--included
)
AND
(CASE
WHEN [AME Product Group] LIKE 'Pallets assets' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'Automobile Assets' THEN 'Auto'
WHEN [AME Product Group] LIKE 'RC1' THEN 'Crates'
WHEN [AME Product Group] LIKE 'RB1' THEN 'Wooden Bins'
WHEN [AME Product Group] LIKE 'RB2' THEN 'Plastic Bins'
WHEN [AME Product Group] LIKE 'BC1' THEN 'IBC'
WHEN [AME Product Group] LIKE 'Customer Owned' THEN 'Cust Owned'
ELSE
'Not assigned'
END ) != 'Not assigned'
----------------------------------------------------------------------------------------------------------------------------------------------------------------
UNION ALL
SELECT
'Plant costs' AS [Datasource]
,[FY]
,CASE
WHEN LEN([Period]) = 3 THEN [Period]
WHEN LEN([Period]) = 2 THEN 'P0' + RIGHT([Period],1)
END AS [Period]
,[AME Product Group]
,[Metric]
,[Metric Value]
FROM
(SELECT
'FY' + RIGHT([Fiscal year],2) AS [FY]
,'P' + RIGHT(LEFT([Posting period],3),2) AS [Period]
,CASE--This grouping was aligned with the orginal manual file and plant metrics alignments that existed. This aligns with MEA SLA grouping as well. Need to confirm yearly with business to agree.
WHEN [AME Product Group] LIKE 'PB1' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'PB2' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'PO1' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'AB1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AI1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AI2' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AO1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AP1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'RC1' THEN 'Crates'
WHEN [AME Product Group] LIKE 'RB1' THEN 'Wooden Bins'
WHEN [AME Product Group] LIKE 'RB2' THEN 'Plastic Bins'
WHEN [AME Product Group] LIKE 'BC1' THEN 'IBC'
WHEN [AME Product Group] LIKE 'CB0' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB1' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB2' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB5' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB7' THEN 'Cust Owned'
ELSE
'Not assigned'
END AS [AME Product Group]
,CASE
WHEN [Other Measures/KPI] = 'Ann/Stat hol pay Wag' Then 'Accrual for Leave - from Payroll'
WHEN [Other Measures/KPI] = 'Fixed Depreciation' Then 'Fixed Depreciation'
WHEN [Other Measures/KPI] = 'Fixed Forklift Costs' Then 'Fixed Forklift Costs'
WHEN [Other Measures/KPI] = 'Fixed Labour Salary' Then 'Fixed Labour Salary'
WHEN [Other Measures/KPI] = 'Fixed Rent Buiding' Then 'Fixed Rent Buiding'
WHEN [Other Measures/KPI] = 'Fixed Repair and Mai' Then 'Fixed Repairs and Maint.'
WHEN [Other Measures/KPI] = 'Fixed Running Costs' Then 'Fixed Running Costs'
WHEN [Other Measures/KPI] = 'GCOA/91354' Then 'Other Costs Allocated to Ops at Total Company Level'
WHEN [Other Measures/KPI] = 'O''time Waged Perm' Then 'Overtime Wage (Permanent Stuff)'
WHEN [Other Measures/KPI] = 'Raw Mat purch offset' Then 'Raw Material Purchase offset'
WHEN [Other Measures/KPI] = 'RawMat fr Inv consum' Then 'Raw Material from Inventory consumption'
WHEN [Other Measures/KPI] = 'Rep &Maint Land Buil' Then 'Repairs and Maintenance (Land & Building)'
WHEN [Other Measures/KPI] = 'TParty Serv Ctre Pro' THEN 'Variable 3rdP Cycle1'--'TP Service Centre'--this was a business decision to included herein within the service centre. Adjust as per business.
WHEN [Other Measures/KPI] = 'Variable 3rdP Cycle1' Then 'Variable 3rdP Cycle1' ---Was Variable Third Pary
WHEN [Other Measures/KPI] = 'Variable Consumption' Then 'Variable Consumption'
WHEN [Other Measures/KPI] = 'Variable Labour' Then 'Variable Labour '
WHEN [Other Measures/KPI] = 'Variable Transport C' Then 'Variable Transport Costs'
WHEN [Other Measures/KPI] = 'Variable Transport Costs' Then 'Variable Transport Costs'
WHEN [Other Measures/KPI] = 'Variable Waste Dispo' Then 'Variable Waste Disposal'
WHEN [Other Measures/KPI] = 'Wages Permanent' Then 'Wages (Permanent stuff)'
END AS [Metric]
,SUM([Metric value]) AS [Metric Value]
FROM [PROD].[I_Plant_Management_Metrics_Consol_MO_BW]
WHERE
[Type] IN ('PlantCosts')
AND
([Measure] != '0' OR [Measure] IS NOT NULL)
AND
[Other Measures/KPI] !='Raw Material from Inventory consumption'
AND
(CASE
WHEN [Other Measures/KPI] = 'Ann/Stat hol pay Wag' Then 'Accrual for Leave - from Payroll'
WHEN [Other Measures/KPI] = 'Fixed Depreciation' Then 'Fixed Depreciation'
WHEN [Other Measures/KPI] = 'Fixed Forklift Costs' Then 'Fixed Forklift Costs'
WHEN [Other Measures/KPI] = 'Fixed Labour Salary' Then 'Fixed Labour Salary'
WHEN [Other Measures/KPI] = 'Fixed Rent Buiding' Then 'Fixed Rent Buiding'
WHEN [Other Measures/KPI] = 'Fixed Repair and Mai' Then 'Fixed Repairs and Maint.'
WHEN [Other Measures/KPI] = 'Fixed Running Costs' Then 'Fixed Running Costs'
WHEN [Other Measures/KPI] = 'GCOA/91354' Then 'Other Costs Allocated to Ops at Total Company Level'
WHEN [Other Measures/KPI] = 'O''time Waged Perm' Then 'Overtime Wage (Permanent Stuff)'
WHEN [Other Measures/KPI] = 'Raw Mat purch offset' Then 'Raw Material Purchase offset'
WHEN [Other Measures/KPI] = 'RawMat fr Inv consum' Then 'Raw Material from Inventory consumption'
WHEN [Other Measures/KPI] = 'Rep &Maint Land Buil' Then 'Repairs and Maintenance (Land & Building)'
WHEN [Other Measures/KPI] = 'TParty Serv Ctre Pro' THEN 'Variable 3rdP Cycle1'--'TP Service Centre'--this was a business decision to included herein within the service centre. Adjust as per business.
WHEN [Other Measures/KPI] = 'Variable 3rdP Cycle1' Then 'Variable 3rdP Cycle1' --Was Variable third pary
WHEN [Other Measures/KPI] = 'Variable Consumption' Then 'Variable Consumption'
WHEN [Other Measures/KPI] = 'Variable Labour' Then 'Variable Labour '
WHEN [Other Measures/KPI] = 'Variable Transport C' Then 'Variable Transport C'
WHEN [Other Measures/KPI] = 'Variable Transport Costs' Then 'Variable Transport Costs'
WHEN [Other Measures/KPI] = 'Variable Waste Dispo' Then 'Variable Waste Disposal'
WHEN [Other Measures/KPI] = 'Wages Permanent' Then 'Wages (Permanent stuff)'
END) IN(
'Fixed Depreciation'
,'Fixed Forklift Costs'
,'Fixed Labour Salary'
,'Fixed Rent Buiding'
,'Fixed Repairs and Maint.'
,'Fixed Running Costs'
,'TP Service Centre'
,'Variable 3rdP Cycle1' --It was Variable Third Party
,'Variable Consumption'
,'Variable Labour '
,'Variable Waste Disposal'
,'Variable Transport Costs'
,'Variable Transport C')
GROUP BY
'FY' + RIGHT([Fiscal year],2)
,'P' + RIGHT(LEFT([Posting period],3),2)
,CASE--See comments above for explanation for grouping and not aligned with material master.
WHEN [AME Product Group] LIKE 'PB1' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'PB2' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'PO1' THEN 'All Pallets'
WHEN [AME Product Group] LIKE 'AB1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AI1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AI2' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AO1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'AP1' THEN 'Auto'
WHEN [AME Product Group] LIKE 'RC1' THEN 'Crates'
WHEN [AME Product Group] LIKE 'RB1' THEN 'Wooden Bins'
WHEN [AME Product Group] LIKE 'RB2' THEN 'Plastic Bins'
WHEN [AME Product Group] LIKE 'BC1' THEN 'IBC'
WHEN [AME Product Group] LIKE 'CB0' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB1' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB2' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB5' THEN 'Cust Owned'
WHEN [AME Product Group] LIKE 'CB7' THEN 'Cust Owned'
ELSE
'Not assigned'
END
,CASE
WHEN [Other Measures/KPI] = 'Ann/Stat hol pay Wag' Then 'Accrual for Leave - from Payroll'
WHEN [Other Measures/KPI] = 'Fixed Depreciation' Then 'Fixed Depreciation'
WHEN [Other Measures/KPI] = 'Fixed Forklift Costs' Then 'Fixed Forklift Costs'
WHEN [Other Measures/KPI] = 'Fixed Labour Salary' Then 'Fixed Labour Salary'
WHEN [Other Measures/KPI] = 'Fixed Rent Buiding' Then 'Fixed Rent Buiding'
WHEN [Other Measures/KPI] = 'Fixed Repair and Mai' Then 'Fixed Repairs and Maint.'
WHEN [Other Measures/KPI] = 'Fixed Running Costs' Then 'Fixed Running Costs'
WHEN [Other Measures/KPI] = 'GCOA/91354' Then 'Other Costs Allocated to Ops at Total Company Level'
WHEN [Other Measures/KPI] = 'O''time Waged Perm' Then 'Overtime Wage (Permanent Stuff)'
WHEN [Other Measures/KPI] = 'Raw Mat purch offset' Then 'Raw Material Purchase offset'
WHEN [Other Measures/KPI] = 'RawMat fr Inv consum' Then 'Raw Material from Inventory consumption'
WHEN [Other Measures/KPI] = 'Rep &Maint Land Buil' Then 'Repairs and Maintenance (Land & Building)'
WHEN [Other Measures/KPI] = 'TParty Serv Ctre Pro' Then 'TP Service Centre'
WHEN [Other Measures/KPI] = 'Variable 3rdP Cycle1' Then 'Variable 3rdP Cycle1' -----It was Variable Third Party
WHEN [Other Measures/KPI] = 'Variable Consumption' Then 'Variable Consumption'
WHEN [Other Measures/KPI] = 'Variable Labour' Then 'Variable Labour '
WHEN [Other Measures/KPI] = 'Variable Transport C' Then 'Variable Transport Costs'
WHEN [Other Measures/KPI] = 'Variable Transport Costs' Then 'Variable Transport Costs'
WHEN [Other Measures/KPI] = 'Variable Waste Dispo' Then 'Variable Waste Disposal'
WHEN [Other Measures/KPI] = 'Wages Permanent' Then 'Wages (Permanent stuff)'
END
) AS PLANT_COSTS
) AS PLANT_COSTS
GROUP BY
--[Datasource]
--,
[FY]
,[Period]
,[AME Product Group]
,[Metric]
) AS MAN_ADJ
WHERE
MAN_ADJ.[AME Product Group] LIKE 'All Pallets'
) AS MAN_ADJ_ALL
LEFT OUTER JOIN
-----------------------------------------------------------------------------------------------------------------------------------------------------
----ADJUSTMENT FOR THE 3RD PARTY HANDLING ACCOUNT FOR THE SPLIT FROM COST CENTRE REPORTING
(
SELECT
'FY' + CONVERT(VARCHAR(4),RIGHT(MAN_ADJ.[FY],2)) AS [FY]
,'P' + CONVERT(VARCHAR(3),MAN_ADJ.[Period]) AS [Period]
,MAN_ADJ.[Group]
,FORMAT_LINK.[Plant]
,MAN_ADJ.Structure
,FORMAT_LINK.[Business Process Activity]
,FORMAT_LINK.[Cost Element]
,FORMAT_LINK.Weighting
,[Amount]
FROM
(
SELECT
[Fiscal year] AS [FY]
,[Fiscal period] AS [Period]
,'All Pallets' AS [Group]
,'PB1' AS [Structure]
,[9 Series acc Description] AS [Cost Element]
----,Account Note link to cost centre reporting
,SUM([Amount]) AS [Amount]
FROM
(SELECT
RIGHT(GL_M.[Fiscal year period],4) AS [Fiscal year]
,RIGHT(LEFT(GL_M.[Fiscal year period],3),2) AS [Fiscal period]
,CONVERT(DATE,CONVERT(VARCHAR(10),(CASE
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) > 6 THEN RIGHT(GL_M.[Fiscal year period],4)
ELSE
RIGHT(GL_M.[Fiscal year period],4)-1
END)) + '-' +
CONVERT(VARCHAR(10),(CASE
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 1 THEN '07'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 2 THEN '08'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 3 THEN '09'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 4 THEN '10'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 5 THEN '11'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 6 THEN '12'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 7 THEN '01'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 8 THEN '02'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 9 THEN '03'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 10 THEN '04'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 11 THEN '05'
WHEN RIGHT(LEFT(GL_M.[Fiscal year period],3),2) = 12 THEN '06'
END)) + '-01') AS [Month]
--Selections
,CASE WHEN CONVERT(VARCHAR(10),GL.[9 Series acc]) IS NULL THEN [GL_M].[Account Number] ELSE CONVERT(VARCHAR(10),GL.[9 Series acc]) END AS [9 Series acc]
,CASE WHEN GL.[9 Series acc Description] IS NULL THEN 'Not assigned' ELSE GL.[9 Series acc Description] END AS [9 Series acc Description]
,GL_M.[Plant Name]
,GL_M.[Cost Center] AS [Cost centre]
--Data
,GL_M.[Account Number] + ' - ' + GL_M.[GL Element] AS [Account]
,GL_M.[Account Number]
,GL_M.[GL Element]
--,GL_M.[Accounting Document]
--,GL_M.[Posting Date]
--,GL_M.[Cost Element] AS [Text]
--Actual Costs
,GL_M.[Amount]
FROM
(
SELECT
COSTS_DATA.[Fiscal year period]
,COSTS_DATA.[Plant Name]
,COSTS_DATA.[Cost Center]
,COSTS_DATA.[G L Account] AS [Account Number]
,COSTS_DATA.[GL Element]
--,COSTS_DATA.[Accounting Document]
--,COSTS_DATA.[Posting Date]
--,COSTS_DATA.[Cost Element]
,CONVERT(FLOAT,COSTS_DATA.[Amount]) AS [Amount]
--FROM #DATA_COSTS
FROM
(SELECT
[SUMMARY].[Cost link up]
--Summary formating
,[SUMMARY].[Fiscal year period]
,[SUMMARY].[Plant Name]
,[SUMMARY].[Cost Center]
,[SUMMARY].[G L Account]
,[SUMMARY].[GL Element]
--Detail
--,[SUMMARY].[Accounting Document]
-- ,[SUMMARY].[Posting Date]
-- ,[SUMMARY].[Cost Element]
,SUM([SUMMARY].[Amount]) AS [Amount]
--INTO #DATA_COSTS
FROM
(SELECT
'Plant metric costs' AS [Cost link up]
,'0' +PCC.[Fiscal period] + '.' + PCC.[Fiscal year] AS [Fiscal year period]
,PCC.[Fiscal year]
,PCC.[Fiscal period]
,PCC.[Month]
,PCC.[9 Series acc]
,PCC.[9 Series acc Description]
,PCC.[Plant Name]
,PCC.[Cost centre] AS [Cost Center]
,PCC.[Account]
,PCC.[Account Number] AS [G L Account]
,PCC.[GL Element]
,PCC.[Accounting Document]
,PCC.[Posting Date]
,PCC.[Text]
,PCC.[Amount]
,CASE WHEN PCC.[Account Number] = '50101010' THEN 'Hide' ELSE 'Not' END AS [Hide/Not]
FROM [Prod]. AS PCC WITH (NOLOCK)
INNER JOIN
(SELECT
CASE
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA10'
WHEN [Local Currency] LIKE 'BWP' THEN 'BW10'
WHEN [Local Currency] LIKE 'ZWL' THEN 'ZW10'
WHEN [Local Currency] LIKE 'MZN' THEN 'MZ10'
WHEN [Local Currency] LIKE 'ZWL' THEN 'ZW20'
WHEN [Local Currency] LIKE 'ZMW' THEN 'ZM10'
--NOTE BELOW
WHEN [Local Currency] LIKE 'ZAR' THEN 'SZ10'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'NA10'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA30'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA40'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
END AS [Company code]
,[Local Currency]
,[Foreign Currency]
,[Conversion rate local to Foreign]
FROM [Prod]. AS COV
WHERE
(CASE
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA10'
WHEN [Local Currency] LIKE 'BWP' THEN 'BW10'
WHEN [Local Currency] LIKE 'ZWL' THEN 'ZW10'
WHEN [Local Currency] LIKE 'MZN' THEN 'MZ10'
WHEN [Local Currency] LIKE 'ZWL' THEN 'ZW20'
WHEN [Local Currency] LIKE 'ZMW' THEN 'ZM10'
--NOTE BELOW
WHEN [Local Currency] LIKE 'ZAR' THEN 'SZ10'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'NA10'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA30'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
WHEN [Local Currency] LIKE 'ZAR' THEN 'ZA40'--ONE TO ONE CONVERSION AND NOT IN CURRENCY TABLE.
END) IS NOT NULL
) AS CURR
ON
LEFT(PCC.[Cost centre],4) = CURR.[Company code]
WHERE
PCC.[Account Number] != '58001020'
OR
PCC.[Account Number] != '58099999'
OR
PCC.[Account Number] != '50705010') AS [SUMMARY]
WHERE
[Cost Center] LIKE 'ZA10PA0003'
AND
[G L Account] = '50509010'-- - TParty Serv Ctre Han
GROUP BY
[SUMMARY].[Cost link up]
--Summary formating
,[SUMMARY].[Fiscal year period]
,[SUMMARY].[Plant Name]
,[SUMMARY].[Cost Center]
,[SUMMARY].[G L Account]
,[SUMMARY].[GL Element]
) AS COSTS_DATA
) AS GL_M
LEFT OUTER JOIN
(SELECT
[5 Series acc]
,[5 Series acc Description]
,[9 Series acc]
,[9 Series acc Description]
,[Loaddate]
,[Month]
FROM [Prod].[SSA - Cost centre reporting - GL Mapping Base_Table_Other] AS GL
WHERE
CONVERT(DATE,GL.[Loaddate]) = CONVERT(DATE,(SELECT MAX(DISTINCT [loaddate]) FROM PROD.[SSA - Cost centre reporting - GL Mapping Base_Table_Other]))
AND
[9 Series acc Description] LIKE 'Variable 3rdP Cycle1'
) AS GL
ON
GL_M.[Account Number] = GL.[5 Series acc]
WHERE
[Account Number] LIKE '50509010'
) AS MAN_ADJ
GROUP BY
MAN_ADJ.[9 Series acc Description]
,MAN_ADJ.Account
,MAN_ADJ.[Fiscal year]
,MAN_ADJ.[Fiscal period]
) AS MAN_ADJ
LEFT OUTER JOIN
(SELECT
'All Pallets' AS [Group]
,'ZA01' AS [Plant]
,'PB1' AS [Structure]
,'Repair' AS [Business Process Activity]
,'Variable 3rdP Cycle1' AS [Cost Element]
,1 AS [Weighting]
UNION ALL
SELECT
'All Pallets' AS [Group]
,'ZA03' AS [Plant]
,'PB1' AS [Structure]
,'Repair' AS [Business Process Activity]
,'Variable 3rdP Cycle1' AS [Cost Element]
,0.7 AS [Weighting]
UNION ALL
SELECT
'All Pallets' AS [Group]
,'ZA03' AS [Plant]
,'PB1' AS [Structure]
,'Handling' AS [Business Process Activity]
,'Variable 3rdP Cycle1' AS [Cost Element]
,0.3 AS [Weighting]
) AS FORMAT_LINK
ON
MAN_ADJ.[Group] = FORMAT_LINK.[Group]
AND
MAN_ADJ.[Structure] = FORMAT_LINK.[Structure]
AND
MAN_ADJ.[Cost Element] = FORMAT_LINK.[Cost Element]
) AS MAN_ADJ_3RDPARTY
ON
MAN_ADJ_ALL.[FY] = MAN_ADJ_3RDPARTY.[FY]
AND
MAN_ADJ_ALL.[Period] = MAN_ADJ_3RDPARTY.[Period]
AND
MAN_ADJ_ALL.[Cost Element] = MAN_ADJ_3RDPARTY.[Cost Element]
--GO
GO
I used AI to improve code, do you agree? The above code is difficult to read and to understand.
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [Prod]. - Plant Metrics - Cost Adjustment_View_Other] AS
---------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT
MAN_ADJ_ALL.[FY],
MAN_ADJ_ALL.[Period],
MAN_ADJ_ALL.[Group],
CASE
WHEN MAN_ADJ_ALL.[Cost Element] = 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Plant]
ELSE MAN_ADJ_ALL.[Plant]
END AS [Plant],
MAN_ADJ_ALL.[Structure],
CASE
WHEN MAN_ADJ_ALL.[Cost Element] = 'Variable 3rdP Cycle1' THEN MAN_ADJ_3RDPARTY.[Business Process Activity]
ELSE MAN_ADJ_ALL.[Business Process Activity]
END AS [Business Process Activity],
MAN_ADJ_ALL.[Cost Element],
CASE
WHEN MAN_ADJ_ALL.[Cost Element] = 'Variable 3rdP Cycle1' AND MAN_ADJ_ALL.[Structure] = 'PB1' THEN
CASE
WHEN MAN_ADJ_3RDPARTY.[Plant] = 'ZA03' THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] = 'ZA01' THEN MAN_ADJ_ALL.[Value Adjustment] - MAN_ADJ_3RDPARTY.Amount
ELSE MAN_ADJ_ALL.[Value Adjustment]
END
ELSE
MAN_ADJ_ALL.[Value Adjustment]
END AS [Value]
FROM
(SELECT
/* Columns from the first subquery */ [FY],
[Period],
[Group],
[Plant],
[Structure],
[Business Process Activity],
[Cost Element],
[Value Adjustment]
FROM
/* The actual source tables and joins for the first subquery */ /* Example: */ Table1
JOIN Table2 ON Table1.Key = Table2.Key
/* More joins and conditions as needed */ ) AS MAN_ADJ_ALL
LEFT OUTER JOIN
(SELECT
/* Columns from the second subquery */ [FY],
[Period],
[Group],
[Plant],
[Business Process Activity],
[Cost Element],
[Amount],
[Weighting]
FROM
/* The actual source tables and joins for the second subquery */ /* Example: */ Table3
JOIN Table4 ON Table3.Key = Table4.Key
/* More joins and conditions as needed */ ) AS MAN_ADJ_3RDPARTY
ON
MAN_ADJ_ALL.[FY] = MAN_ADJ_3RDPARTY.[FY] AND
MAN_ADJ_ALL.[Period] = MAN_ADJ_3RDPARTY.[Period] AND
MAN_ADJ_ALL.[Cost Element] = MAN_ADJ_3RDPARTY.[Cost Element]
GO
With CTE's:
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER VIEW [Prod].
AS
WITH MAN_ADJ_ALL AS (
SELECT
[FY],
[Period],
[AME Product Group] AS [Group],
'ZA01' AS [Plant],
'PB1' AS [Structure],
CASE
WHEN Metric IN (
'Fixed Depreciation', 'Fixed Labour Salary', 'Fixed Repairs and Maint.', 'Fixed Running Costs',
'Variable 3rdP Cycle1', 'Variable Consumption', 'Variable Labour', 'Variable Waste Disposal'
) THEN 'Repair'
WHEN Metric IN (
'Fixed Rent Buiding', 'Fixed Forklift Costs', 'Variable Transport Costs'
) THEN 'Handling'
ELSE 'Not assigned'
END AS [Business Process Activity],
Metric AS [Cost Element],
[Metric Value] * -1 AS [Value Adjustment]
FROM [YourSourceTable] -- Replace with your actual source table or complex query
-- Additional JOINs or WHERE conditions go here
),
MAN_ADJ_3RDPARTY AS (
SELECT
'FY' + CONVERT(VARCHAR(4), RIGHT(FY, 2)) AS [FY],
'P' + CONVERT(VARCHAR(3), [Period]) AS [Period],
[Group],
[Plant],
[Structure],
[Business Process Activity],
[Cost Element],
[Weighting],
[Amount]
FROM [YourSecondSourceTable] -- Replace with your actual source table or complex query
-- Additional JOINs or WHERE conditions go here
)
SELECT
A.[FY],
A.[Period],
A.[Group],
CASE
WHEN A.[Cost Element] = 'Variable 3rdP Cycle1' THEN B.[Plant]
ELSE A.[Plant]
END AS [Plant],
A.[Structure],
CASE
WHEN A.[Cost Element] = 'Variable 3rdP Cycle1' THEN B.[Business Process Activity]
ELSE A.[Business Process Activity]
END AS [Business Process Activity],
A.[Cost Element],
CASE
WHEN A.[Cost Element] = 'Variable 3rdP Cycle1' AND A.[Structure] = 'PB1' THEN
CASE
WHEN B.[Plant] = 'ZA03' THEN B.[Amount] * B.[Weighting]
WHEN B.[Plant] = 'ZA01' THEN A.[Value Adjustment] - B.[Amount]
ELSE A.[Value Adjustment]
END
ELSE
A.[Value Adjustment]
END AS [Value]
FROM
MAN_ADJ_ALL A
LEFT JOIN
MAN_ADJ_3RDPARTY B ON A.[FY] = B.[FY] AND A.[Period] = B.[Period] AND A.[Cost Element] = B.[Cost Element]
GO
What would you change and why?
December 12, 2023 at 6:21 pm
That's a huge chunk of code and, in my opinion, asking such general questions is the sort of thing you'd employ a paid consultant to help with.
If you take the time to ask specific and bite-sized questions which demonstrate that you've at least tried to do some of the work yourself, rather than simply invoking AI and throwing out the results for comments, I'm sure that you'll get better results.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
December 12, 2023 at 6:27 pm
Have you checked it returns the same results?
December 12, 2023 at 6:42 pm
The code was not created by me. I am just investing some one else's code, this person is leaving us soon. I just want to make sure that the code does not have to be this difficult.
December 12, 2023 at 6:46 pm
The code was not created by me. I am just investing some one else's code, this person is leaving us soon. I just want to make sure that the code does not have to be this difficult.
Yes, but AI sometimes makes mistakes. I'm just asking because you need to make sure that the rewritten SQL is functionally the same as the original SQL.
December 12, 2023 at 7:03 pm
To add to what Jonathan said - do you understand the code? If person A is supporting it now and understands the code and they are leaving and you are picking up support of it, can you support it? If end users report that it is missing some data, can you modify it to add the missing data? If it is giving duplicate results, can you correct that? Even just running the code and getting the same results isn't always enough - it could be there are edge cases covered in the original code that are missed by the AI version.
It is one thing to rewrite it so it is easier for you to understand and to actually understand the code. My first step when looking at code that is long and wide is to run a SQL formatter on it (there are plenty out there, but I am a fan of SQL Prompt by RedGate for a paid option and Poor man's T-SQL formatter for a free one). Tweak the settings until the formatter is giving you output in a format you like and is easy for you to read and understand then learn and understand the code before you throw AI or any code altering tools at the code because YOU are being paid to support the code, not the AI. The AI may give you the same results today, but what happens when code changes are required?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 12, 2023 at 9:02 pm
I have a suggestion... how about having this "developer" document what each section of the code is actually doing in a business sense. It needs a proper flower box to explain its purpose and possible usage example and the code itself needs an explanation as to the business purpose for each Select and, perhaps, what the reason for the CASE operators are for.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2023 at 9:36 pm
I agree with Jeff - if the code is complex or confusing, it should have comments around those bits explaining the logic. Now, if the code is confusing to someone outside the business, that is a different thing. For example, if I was working in Finance and I was doing some standard tax calculations (for example) on a column in the view, I wouldn't bother to document it because they are standard for my industry and anyone taking over my work should understand this. In your scenario, the cost element and structure don't mean anything to me (why is it called "Variable 3rdP Cycle1"? why "Cycle1"? are there multiple cycles and you only care about the first? What is 3rdP? are there multiple P's? What are P's?), but to you, you could see that and go "ah, that is for when the 3rd person starts cycle 1". Same thing with PB1.
Curiosity got the better of me and I decided to look at the nested CASE statement that revolves around "ZA"'s and noticed that this (original code):
CASE
WHEN MAN_ADJ_ALL.[Cost Element] LIKE 'Variable 3rdP Cycle1' AND MAN_ADJ_ALL.[Structure] LIKE 'PB1' THEN
CASE
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA03' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Handling'
THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA03' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Repair'
THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] LIKE 'ZA01' AND MAN_ADJ_3RDPARTY.[Business Process Activity] LIKE 'Repair'
THEN MAN_ADJ_ALL.[Value Adjustment] - MAN_ADJ_3RDPARTY.Amount
END
ELSE
MAN_ADJ_ALL.[Value Adjustment]
END AS [Value]
appears to be shrunk down to this by the "AI":
CASE
WHEN MAN_ADJ_ALL.[Cost Element] = 'Variable 3rdP Cycle1' AND MAN_ADJ_ALL.[Structure] = 'PB1' THEN
CASE
WHEN MAN_ADJ_3RDPARTY.[Plant] = 'ZA03' THEN MAN_ADJ_3RDPARTY.Amount * MAN_ADJ_3RDPARTY.[Weighting]
WHEN MAN_ADJ_3RDPARTY.[Plant] = 'ZA01' THEN MAN_ADJ_ALL.[Value Adjustment] - MAN_ADJ_3RDPARTY.Amount
ELSE MAN_ADJ_ALL.[Value Adjustment]
END
ELSE
MAN_ADJ_ALL.[Value Adjustment]
END AS [Value]
and while I agree that the "AI" version is easier to read, you will notice that the check around the plant being ZA03 in the original also requires the business process activity to be handling or repair as the calculation is the same for those 2 values. For the check on ZA01 you also require that the business process activity to be repair. The AI version doesn't do these checks. It could be that the checks are redundant, but can you say with 100% certainty that ZA01 will NEVER have "handling" as the business process activity or that ZA03 will NEVER have a value other than handling or repair as the business process activity? I cannot say that for certain as I don't know your data or processes, BUT if it can, then the AI code will definitely give you bad data for those rows. JUST from that one bit being potentially wrong, I would call the entire AI version suspect and likely to be wrong in other places.
That being said, I don't see why the original developer used "like" for all those comparisons as they have no wildcard characters in the comparison... the AI is correct that it makes the code look cleaner to have "=" instead of "like". Now, that being said, there MAY be a reason why the original developer used "like" instead of "=" when doing the comparison (they are functionally equivalent when no wildcard is provided, but do not perform the same), but without talking to the original developer, I would be tempted to convert all the likes I could to = and do a performance comparison.
TL;DR version - AI appears to have made some mistakes. I found one with a VERY quick look over the code. I wasn't even looking for anything overly specific, just sections in the AI code that looked to be overly simplified. This leads me to suspect that the AI code is flawed and shouldn't be trusted to give the same result.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply