Обсуждение: Using a Conversion Table

CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2"
(
    "Company_Code" character varying(255) COLLATE pg_catalog."default",
    "Posting_Period" integer,
    "Fiscal_Year" integer,
    "Profit_Center" character varying(255) COLLATE pg_catalog."default",
    "Account_Number" integer,
    "Business_Process" character varying(255) COLLATE pg_catalog."default",
    "Internal_Order" character varying(255) COLLATE pg_catalog."default",
    "Amount_in_Company_Code_Currency" numeric,
    "Company_Code_Currency" character varying(255) COLLATE pg_catalog."default",
    "BRACS_FA" character varying(255) COLLATE pg_catalog."default",
    "Expense_Type" character varying(255) COLLATE pg_catalog."default",
    "BRACS_ACCT_Key" character varying(255) COLLATE pg_catalog."default",
    "Segment_PC" character varying(255) COLLATE pg_catalog."default",
    "CC_Master_FA" character varying(255) COLLATE pg_catalog."default",
    "Loaddate" date DEFAULT CURRENT_DATE,
    "Row_Hash" text COLLATE pg_catalog."default",
    "LoadTime" timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    "ZTBR_TransactionCode" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT "IMETA_ZTRB_MP$F_ZTBR_TA_BW_pkey2" PRIMARY KEY ("ZTBR_TransactionCode")
)

CREATE TABLE IF NOT EXISTS dim."IMETA_Master_Currency_Data_TA_BR"
(
    "Currency" character varying(255) COLLATE pg_catalog."default",
    "Currency name" character varying(255) COLLATE pg_catalog."default",
    "Currency from" character varying(255) COLLATE pg_catalog."default",
    "Scenario" character varying(255) COLLATE pg_catalog."default",
    "Fiscal year" double precision,
    "Fiscal period" character varying(255) COLLATE pg_catalog."default",
    "Currency from value" double precision,
    "Currency to value" double precision,
    "Loaddate" date
)

TABLESPACE pg_default;

SELECT
    z."Fiscal_Year",
    SUM(z."Amount_in_Company_Code_Currency") AS Total_Amount,
    ROUND(SUM(z."Amount_in_Company_Code_Currency" / CASE
        WHEN c."Currency" = 'USD' THEN 1
        ELSE c."Currency to value" END)::numeric, 2) AS Total_Amount_USD
FROM
    system."IMETA_ZTRB_MP$F_ZTBR_TA_BW2" z
LEFT JOIN
    (SELECT
        "Currency",
        "Currency to value"
     FROM
        dim."IMETA_Master_Currency_Data_TA_BR"
     WHERE
        "Scenario" = 'Actual'  -- Adjust the scenario as needed
        AND "Fiscal year" = 2024) c  -- Adjust the fiscal year as needed
ON
    z."Company_Code_Currency" = c."Currency"
WHERE
    z."Fiscal_Year" = 2024
GROUP BY
    z."Fiscal_Year";

-- 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",
    t."Fiscal_Year",
    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."DIM_BRACS_Account_Description" AS "Acct_Type",
    t."DIM_Classification",
    t."DIM_Direct_Primary_Key",
    t."DIM_Order",
    t."SDM_BRACSFA",
    t."SDM_Function",
    t."BRACS_Level_1",
    t."BRACS_Level_2",
    t."BRACS_Level_3",
    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",
    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;