-- Table: dim.IMETA_Source_Description_Mapping -- DROP TABLE IF EXISTS dim."IMETA_Source_Description_Mapping"; CREATE TABLE IF NOT EXISTS dim."IMETA_Source_Description_Mapping" ( "BRACS_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Source_Description_Mapping_BRACS_Key_seq"'::regclass), "BRACSFA" character varying(255) COLLATE pg_catalog."default" NOT NULL, "Function" character varying(255) COLLATE pg_catalog."default" NOT NULL, CONSTRAINT "IMETA_Source_Description_Mapping_pkey" PRIMARY KEY ("BRACS_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS dim."IMETA_Source_Description_Mapping" OWNER to ; ----------- -- Table: dim.IMETA_Region_Mapping -- DROP TABLE IF EXISTS dim."IMETA_Region_Mapping"; CREATE TABLE IF NOT EXISTS dim."IMETA_Region_Mapping" ( "CoCd" character varying(255) COLLATE pg_catalog."default", "Sub Region" character varying(255) COLLATE pg_catalog."default", "Region" character varying(255) COLLATE pg_catalog."default", "BRACS Entity" character varying(255) COLLATE pg_catalog."default", "Consul" character varying(255) COLLATE pg_catalog."default", "Report" character varying(255) COLLATE pg_catalog."default", "Region BRACS" character varying(255) COLLATE pg_catalog."default", "Group" character varying(255) COLLATE pg_catalog."default", "Group BRACS" character varying(255) COLLATE pg_catalog."default", "J" character varying(255) COLLATE pg_catalog."default", "K" character varying(255) COLLATE pg_catalog."default", "L" character varying(255) COLLATE pg_catalog."default", "M" character varying(255) COLLATE pg_catalog."default", "N" character varying(255) COLLATE pg_catalog."default", "Region_Primary_Key" integer NOT NULL DEFAULT nextval('dim."IMETA_Region_Mapping_Region_Secondary_Key_seq"'::regclass), CONSTRAINT "IMETA_Region_Mapping_pkey" PRIMARY KEY ("Region_Primary_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS dim."IMETA_Region_Mapping" OWNER to ;-------- -- Table: dim.IMETA_Master_BRACS_to_SAP_Data_TA_BR_ -- DROP TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_"; CREATE TABLE IF NOT EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" ( "ZTBR_TransactionCode" integer NOT NULL DEFAULT nextval('dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR__ZTBR_TransactionCode_seq"'::regclass), "Acct Type" character varying(255) COLLATE pg_catalog."default", "Level 1" character varying(255) COLLATE pg_catalog."default", "Level 2" character varying(255) COLLATE pg_catalog."default", "Level 3" character varying(255) COLLATE pg_catalog."default", "GCoA" double precision, "Account Desc" character varying(255) COLLATE pg_catalog."default", "EXPENSE FLAG" character varying(255) COLLATE pg_catalog."default", "BRACS" double precision, "BRACS_DESC" character varying(255) COLLATE pg_catalog."default", "Loaddate" date, CONSTRAINT "ZTBR_TransactionCode_key" UNIQUE ("ZTBR_TransactionCode") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" OWNER to ; -------- -- Table: dim.IMETA_Entity_Mapping -- DROP TABLE IF EXISTS dim."IMETA_Entity_Mapping"; CREATE TABLE IF NOT EXISTS dim."IMETA_Entity_Mapping" ( "Entity_ID" integer NOT NULL DEFAULT nextval('dim."IMETA_Entity_Mapping_Entity_ID_seq"'::regclass), "Entity_Secondary_Key" integer, "Entity" character varying(255) COLLATE pg_catalog."default", "Entity Name" character varying(255) COLLATE pg_catalog."default", "Entity Level" integer, "Level 1" character varying(255) COLLATE pg_catalog."default", "Level 2" character varying(255) COLLATE pg_catalog."default", "Level 3" character varying(255) COLLATE pg_catalog."default", "Level 4" character varying(255) COLLATE pg_catalog."default", "Level 5" character varying(255) COLLATE pg_catalog."default", "Level 6" character varying(255) COLLATE pg_catalog."default", CONSTRAINT "IMETA_Entity_Mapping_pkey" PRIMARY KEY ("Entity_ID"), CONSTRAINT "IMETA_Entity_Mapping_Entity_Secondary_Key_key" UNIQUE ("Entity_Secondary_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS dim."IMETA_Entity_Mapping" OWNER to ; -- Trigger: trigger_increment_secondary_key -- DROP TRIGGER IF EXISTS trigger_increment_secondary_key ON dim."IMETA_Entity_Mapping"; CREATE TRIGGER trigger_increment_secondary_key BEFORE INSERT ON dim."IMETA_Entity_Mapping" FOR EACH ROW EXECUTE FUNCTION public.increment_secondary_key(); ---------- -- Table: dim.IMETA_Direct_Indirect_Mapping_New -- DROP TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New"; CREATE TABLE IF NOT EXISTS dim."IMETA_Direct_Indirect_Mapping_New" ( "BRACS Account Description" character varying(255) COLLATE pg_catalog."default", "CLASSIFICATION" character varying(255) COLLATE pg_catalog."default", "Direct_Secondary_Key" integer NOT NULL, "Direct_Primary_Key" integer NOT NULL, CONSTRAINT "IMETA_Direct_Indirect_Mapping_New_pkey_new" PRIMARY KEY ("Direct_Primary_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS dim."IMETA_Direct_Indirect_Mapping_New" OWNER to ; ------------------- -- Table: system.IMETA_ZTRB_MP$F_ZTBR_TA_BW -- DROP TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW"; CREATE TABLE IF NOT EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" ( "Company_Code" character varying(4) COLLATE pg_catalog."default", "Posting_Period" character varying(7) COLLATE pg_catalog."default", "Fiscal_Year" character varying(4) COLLATE pg_catalog."default", "Profit_Center" character varying(255) COLLATE pg_catalog."default", "Account_Number" character varying(255) COLLATE pg_catalog."default", "Business_Process" character varying(255) COLLATE pg_catalog."default", "Cost_Center" character varying(10) COLLATE pg_catalog."default", "Internal_Order" character varying(255) COLLATE pg_catalog."default", "Trading_Partner" character varying(255) COLLATE pg_catalog."default", "Amount_in_company_code_currency" numeric(17,2), "Company_code_currency" character varying(5) 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", "CC_Direct" 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", "RowInsertedTimestamp" date DEFAULT CURRENT_DATE, "RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP, "Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass), "Source_Description_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass), "Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass), "Entity_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass), "Region_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass), CONSTRAINT "ZTBR_TA_BW_PrimaryKey" PRIMARY KEY ("Master_BRACS_Secondary_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" OWNER to ------------------------------- INSERT INTO model."IMETA_ZTBR_BRACS_Model_TA_BW" ( "Company_Code", "Posting_Period", "Fiscal_Year", "Profit_Center", "Account_Number", "Business_Process", "Cost_Center", "Internal_Order", "Trading_Partner", "Amount_in_company_code_currency", "Company_code_currency", "BRACS_FA", "Expense_Type", "BRACS_ACCT_Key", "CC_Direct", "Segment_PC", "CC_Master_FA", "Master_BRACS_Secondary_Key", "Source_Description_Secondary_Key", "Direct_Indirect_Secondary_Key", "Entity_Secondary_Key", "Region_Secondary_Key", "RowInsertedTimestamp", "RowUpdatedTimestamp" ) SELECT ZTBR."Company_Code", CAST(ZTBR."Posting_Period" AS character varying(7)) AS "Posting_Period", ZTBR."Fiscal_Year", ZTBR."Profit_Center", ZTBR."Account_Number", ZTBR."Business_Process", ZTBR."Cost_Center", ZTBR."Internal_Order", ZTBR."Trading_Partner", ZTBR."Amount_in_company_code_currency", ZTBR."Company_code_currency", ZTBR."BRACS_FA", ZTBR."Expense_Type", ZTBR."BRACS_ACCT_Key", ZTBR."CC_Direct", ZTBR."Segment_PC", ZTBR."CC_Master_FA", ZTBR."Master_BRACS_Secondary_Key", ZTBR."Source_Description_Secondary_Key", ZTBR."Direct_Indirect_Secondary_Key", ZTBR."Entity_Secondary_Key", ZTBR."Region_Secondary_Key", CURRENT_DATE AS "RowInsertedTimestamp", CURRENT_TIMESTAMP AS "RowUpdatedTimestamp" FROM system."IMETA_ZTRB_MP$F_ZTBR_TA_BW" AS ZTBR JOIN dim."IMETA_Direct_Indirect_Mapping" AS DIM_DIR_IND ON ZTBR."Direct_Indirect_Secondary_Key" = DIM_DIR_IND."Direct_Secondary_Key" JOIN dim."IMETA_Entity_Mapping" AS DIM_ENT_MAP ON ZTBR."Entity_Secondary_Key" = DIM_ENT_MAP."Entity_Secondary_Key" JOIN dim."IMETA_Master_BRACS_to_SAP_Data_TA_BR_" AS DIM_MASTER_BRACS ON ZTBR."Master_BRACS_Secondary_Key" = DIM_MASTER_BRACS."ZTBR_TransactionCode" JOIN dim."IMETA_Source_Description_Mapping" AS DIM_SRC_DESC ON ZTBR."Source_Description_Secondary_Key" = DIM_SRC_DESC."BRACS_Key" JOIN dim."IMETA_Region_Mapping" AS DIM_REGION_MAP ON ZTBR."Region_Secondary_Key" = DIM_REGION_MAP."Region_Primary_Key"; -------------------- INSERT INTO -- Table: model.IMETA_ZTBR_BRACS_Model_TA_BW -- DROP TABLE IF EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW"; CREATE TABLE IF NOT EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW" ( "Company_Code" character varying(4) COLLATE pg_catalog."default", "Posting_Period" character varying(7) COLLATE pg_catalog."default", "Fiscal_Year" character varying(4) COLLATE pg_catalog."default", "Profit_Center" character varying(255) COLLATE pg_catalog."default", "Account_Number" character varying(255) COLLATE pg_catalog."default", "Business_Process" character varying(255) COLLATE pg_catalog."default", "Cost_Center" character varying(10) COLLATE pg_catalog."default", "Internal_Order" character varying(255) COLLATE pg_catalog."default", "Trading_Partner" character varying(255) COLLATE pg_catalog."default", "Amount_in_company_code_currency" numeric(17,2), "Company_code_currency" character varying(5) 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", "CC_Direct" 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", "Master_BRACS_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Master_BRACS_Secondary_Key_seq"'::regclass), "Source_Description_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Source_Description_Secondary_Key_seq"'::regclass), "Direct_Indirect_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Direct_Indirect_Secondary_Key_seq"'::regclass), "Entity_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Entity_Secondary_Key_seq"'::regclass), "Region_Secondary_Key" integer NOT NULL DEFAULT nextval('system."IMETA_ZTRB_MP$F_ZTBR_TA_BW_Region_Secondary_Key_seq"'::regclass), "RowInsertedTimestamp" date DEFAULT CURRENT_DATE, "RowUpdatedTimestamp" timestamp without time zone DEFAULT CURRENT_TIMESTAMP, CONSTRAINT "ZTBR_Query_Destination_pkey" PRIMARY KEY ("Master_BRACS_Secondary_Key") ) TABLESPACE pg_default; ALTER TABLE IF EXISTS model."IMETA_ZTBR_BRACS_Model_TA_BW" OWNER to apollia;