5.11.2.5 Creating Views on Load Table
This step is applicable for loading ledger data from Type I or Type II load table. In
addition to load tables, views have to be created on the staging tables similar to the
view LSL that was created on LEDGER_STAT. A view has to be created on
each load table specifying the columns alias for each column within the load table.
Column alias should match the column alias specified for columns within
LEDGER_STAT table. LEDGER_STAT load procedure
identifies the source columns that need to be loaded using the column alias. Column
alias for LEDGER_STAT columns are specified in the user-defined
property (UDP) COLUMN_ALIAS within ERwin model. See the ERwin model for getting the
column alias for each of the LEDGER_STAT columns. View definition is
given following:
-- --------------------------------------------------------------------
-- Uncomment the m1..m12 columns if you plan to load a range of months (Type II Load table).
-- Add lines for all of the LEDGER_STAT user-defined leaf columns in the
place
-- indicated below. Don't forget to add commas if you need to.
-- --------------------------------------------------------------------
CREATE OR REPLACE VIEW &load_table_name._v AS SELECT ds, year_s, accum_type,
consolidat, isocrncycd, financ_id, org_id, gl_acct_id, cmn_coa_id, prdct_id,
baltypecd, -- -- NVL(m1,0) AS m1, -- NVL(m2,0) AS m2, -- NVL(m3,0) AS m3, --
NVL(m4,0) AS m4, -- NVL(m5,0) AS m5, -- NVL(m6,0) AS m6, -- NVL(m7,0) AS m7, --
NVL(m8,0) AS m8, -- NVL(m9,0) AS m9, -- NVL(m10,0) AS m10, -- NVL(m11,0) AS m11, --
NVL(m12,0) AS m12, -- NVL(one_month_amt,0) AS one -- --
-------------------------------------------------------------------- -- Other leaf
columns (PROPERTY_COLUMN from REV_COLUMN_PROPERTIES for LEDGER_STAT): --
-------------------------------------------------------------------- -- . . . --
FROM &load_table_name WHERE NVL(one_month_amt,0) <> 0; -- -- OR NVL(m1,0)
<> 0 -- OR NVL(m2,0) <> 0 -- OR NVL(m3,0) <> 0 -- OR NVL(m4,0) <> 0 --
OR NVL(m5,0) <> 0 -- OR NVL(m6,0) <> 0 -- OR NVL(m7,0) <> 0 -- OR NVL(m8,0)
<> 0 -- OR NVL(m9,0) <> 0 -- OR NVL(m10,0) <> 0 -- OR NVL(m11,0) <> 0 --
OR NVL(m12,0) <> 0;
In case, the custom dimensions are added to the load table, views need to be modified to reflect the same.