Check Code Correction Current Period Prior Period Movement

Please assist me?
I am trying to calculate
Current Period Prior Period Movement from my data, the date columns
are:
[image: image.png]

Please check my code for any errors, code runs, i just want to make sure it
does what it's supposed to do.
-- View: model.IMETA_ZTRB_BRACS_Model_TA_BW_View

-- DROP VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View"

AS

SELECT t."ZTBR_TransactionCode",

t."Company_Code",

t."Posting_Period", -- This is a period within a year

t."Fiscal_Year", -- This contains years 2019 to present

t."Profit_Center",

t."Account_Number",

t."Business_Process",

t."Internal_Order",

t."Amount_in_Company_Code_Currency",

t."Company_Code_Currency",

t."BRACS_FA",

t."Expense_Type",

t."Primary_ZTBR_TransactionCode",

t."Acct_Type",

t."BRACS_Level_1",

t."BRACS_Level_2",

t."BRACS_Level_3",

t."GCoA",

t."Account_Desc",

t."EXPENSE_FLAG",

t."BRACS",

t."BRACS_DESC",

t."Roll_Up_Currency",

t."Roll_Up_Account_Description",

t."BRACS_Account",

t."BRACS_Account_Description",

t."IS_BS",

t."Classification",

t."Roll_Up_Function",

t."Region",

t."Roll_Up",

t."Entity",

t."Entity_Name",

t."Entity_Level",

t."Entity_Level_1",

t."Entity_Level_2",

t."Entity_Level_3",

t."Entity_Level_4",

t."Entity_Level_5",

t."Entity_Level_6",

t."Region_Mapping_CoCd",

t."Region_Mapping_Sub_Region",

t."Region_Mapping_Region",

t."Region_Mapping_BRACS_Entity",

t."Region_Mapping_Consul",

t."Region_Mapping_Report",

t."Region_Mapping_Region_BRACS",

t."Region_Mapping_Group",

t."Region_Mapping_Group_BRACS",

t."DIM_BRACS_Account_Description",

t."DIM_Classification",

t."DIM_Direct_Primary_Key",

t."DIM_Order",

t."SDM_BRACSFA",

t."SDM_Function",

round((t."Amount_in_Company_Code_Currency"::double precision /
curr."Conversion rate")::numeric, 2) AS "Amount in USD",

CASE

WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"

ELSE NULL::numeric

END AS "Current Period",

CASE

WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN
t."Amount_in_Company_Code_Currency"

ELSE NULL::numeric

END AS "Prior Period",

CASE

WHEN t."Fiscal_Year"::double precision = date_part('year'::text,
CURRENT_DATE) THEN t."Amount_in_Company_Code_Currency"

WHEN t."Fiscal_Year"::double precision = (date_part('year'::text,
CURRENT_DATE) - 1::double precision) THEN -
t."Amount_in_Company_Code_Currency"

ELSE NULL::numeric

END AS "Movement"

FROM model."IMETA_ZTRB_BRACS_Model_TA_BW3" t

LEFT JOIN ( SELECT "IMETA_Master_Currency_Data_TA_BR"."Currency",

"IMETA_Master_Currency_Data_TA_BR"."Currency name",

CASE

WHEN "IMETA_Master_Currency_Data_TA_BR"."Currency"::text =
'USD'::text THEN 1::double precision

ELSE "IMETA_Master_Currency_Data_TA_BR"."Currency to value"

END AS "Conversion rate"

FROM dim."IMETA_Master_Currency_Data_TA_BR"

WHERE "IMETA_Master_Currency_Data_TA_BR"."Scenario"::text =
'BUD'::text) curr ON t."Company_Code_Currency" = curr."Currency"::text;

ALTER TABLE model."IMETA_ZTRB_BRACS_Model_TA_BW_View"

OR

-- Drop the view if it exists to prevent errors
DROP VIEW IF EXISTS model."IMETA_ZTRB_BRACS_Model_TA_BW_View";

-- Create or replace the view
CREATE OR REPLACE VIEW model."IMETA_ZTRB_BRACS_Model_TA_BW_View" AS
SELECT
t."ZTBR_TransactionCode",
-- ... (other columns)
t."SDM_Function",
-- Convert the amount to USD
ROUND((t."Amount_in_Company_Code_Currency" / curr."Conversion
rate")::numeric, 2) AS "Amount in USD",
-- Calculate the Current Period Amount
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Current Period",
-- Calculate the Prior Period Amount
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Prior Period",
-- Calculate the Movement
CASE
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) THEN
t."Amount_in_Company_Code_Currency"
WHEN t."Fiscal_Year"::integer = EXTRACT(YEAR FROM CURRENT_DATE) - 1
THEN -t."Amount_in_Company_Code_Currency"
ELSE NULL
END AS "Movement"
FROM
model."IMETA_ZTRB_BRACS_Model_TA_BW3" t
LEFT JOIN
(SELECT
"Currency",
"Currency name",
CASE
WHEN "Currency" = 'USD' THEN 1
ELSE "Currency to value"
END AS "Conversion rate"
FROM dim."IMETA_Master_Currency_Data_TA_BR"
WHERE "Scenario" = 'BUD') curr
ON t."Company_Code_Currency" = curr."Currency";

-- Change the ownership of the view
ALTER VIEW
select * from model."IMETA_ZTRB_BRACS_Model_TA_BW_View" limit 500 OWNER TO
apollia;