1 package hr_dm_gen_main AUTHID CURRENT_USER as
2 /* $Header: perdmgn.pkh 120.0 2005/05/30 21:17:34 appldev noship $ */
3 --------------------------------
4 -- DATA STRUCTURE DEFINITIONS --
5 --------------------------------
6 --
7 -- Table information structure. It stores the various properties of info about
8 -- the table. Mostly the information is taken from HR_DM_TABLES table.
9 -- table_id - Id value of the table. Primary key of HR_DM_TABLES.
10 -- table_name - Name of the table
11 -- datetrack - 'Y' - for date track table
12 -- 'N' - for non datetrack table
13 -- surrogate_primary_key - whether table has a surrogate id column i.e single
14 -- column numeric key.
15 -- 'Y' - table has surrogate id column
16 -- 'N' - table does not have surrogate id column
17 -- surrogate_pk_column_name - Name of the surrogate id column. It will
18 -- contain value if surrogate_primary_key value is 'Y'.
19 -- alias - Alias of the table. Used in the select statement.
20 -- short_name - Short name of the table. Used to define the TUPS/TDS
21 -- package name
22 -- column_hierarchy - one or more column has foreign key on own table.
23 -- Column hierarchy are derived from HR_DM_HIERARCHIES table
24 -- with hierarchy type 'H' defined for this table.
25 -- table_hierarchy - business group_id has to be derived from parent table.
26 -- Parents table are derived from HR_DM_HIERARCHIES table
27 -- with hierarchy type 'PC' defined for this table.
28 -- use_non_pk_col_for_chk_row
29 -- - one or more column required, other than primary key column
30 -- to check whether row exists in the table or not. It is used
31 -- by TUPS.
32 -- resolve_pk - if 'Y' then add the code in the TUPS to get the latest
33 -- ID value.
34 -- ins_resolve_pk - If 'Y' then add the code to insert a record into the
35 -- hr_dm_resolve_pk table.
36 -- use_distinct - If 'Y' then the TDS download cursor will use 'distinct'
37 -- in the select statement. It will be used if it salisfies
38 -- all the below conditions
39 -- - tables has a AOL hierarchy i.e hierarchy type = 'A'
40 -- - has a table hierarchy i.e hierarchy type = 'PC'
41 -- - does not have 'long' data type.--
42 -- fk_to_aol_table - one or more column has foreign key on AOL table.
43 -- Columns are derived from HR_DM_HIERARCHIES table
44 -- with hierarchy type 'A' defined for this table.
45 -- missing_who_info - 'Y' - if table does not have WHO columns
46 -- 'N' - if table has WHO columns
47 -- missing_primary_key - 'Y' - if table does not have any primary key defined
48 -- in data base. In this case the logical primary
49 -- key is derived from HR_DM_HIERARCHIES table
50 -- with hierarchy type 'P' defined for this table.
51 -- 'N' - if the table has a primary key defined in database.
52 -- chk_row_exists_for_non_glb_tbl - It will contain following value :
53 -- 'Y' - add the code in the upload procedure of TUPS
54 -- to check that whether row exists in destination
55 -- database for non global data.
56 -- 'N' - No check is made.
57 --
58 -- who_link_alias - It tells the alias of table to be used to get the WHO
59 -- information. It is used for tables which does not have
60 -- explicit business group id and business group has to be
61 -- derived from the chain of parent tables. It uses this
62 -- alias to form the additive migration selection
63 -- criteria.
64 -- derive_sql_download_full - It contains the where clause for full migration
65 -- download cursor.
66 -- derive_sql_download_add - It contains the where clause for additive
67 -- migration download cursor. It is applicable for
68 -- date track table only.
69 -- derive_sql_calc_ranges - It contains the where clause for calculate ranges
70 -- cursor.
71 -- derive_sql_delete_source - It contains the where clause for delete data from
72 -- source table cursor.
73 -- derive_sql_source_tables - It stores the list of all tables used in the derive
74 -- sql to form the from clause of the cursor of TDS.
75 -- derive_sql_chk_source_tables - It stores the list of all tables used in the derive
76 -- sql to form the from clause of the cursor of
77 -- chk_row_exists procedure.
78 -- derive_sql_chk_row_exists - It contains the where clause for chk_row_exists procedure
79 -- in TUPS
80 -- global_data - 'Y' - table contains global data.
81 -- 'N' - table does not contain global data.
82 -- upload_table_name - Name of the table to be uploaded at destination.It
83 -- will be same in most of the cases except table like
84 -- HR_LOCATIONS which contains global data as well as
85 -- business group specific data.
86 -- use_distinct_download - 'Y' indicates that distinct should be used for the
87 -- download cursor
88 -- always_check_row - 'Y' indicates that check_row_exists is always used
89 -- sequence_name - name of the sequence to be used if a new id value is
90 -- required for insertion into the destination database
91 --
92 ----------------------------------------------------------------------------------
93 type t_table_info is record
94 (
95 migration_id hr_dm_migrations.migration_id%type,
96 table_id hr_dm_tables.table_id%type,
97 table_name hr_dm_tables.table_name%type,
98 datetrack hr_dm_tables.datetrack%type,
99 surrogate_primary_key varchar2(1),
100 surrogate_pk_column_name hr_dm_tables.surrogate_pk_column_name%type,
101 alias hr_dm_tables.table_alias%type,
102 short_name hr_dm_tables.short_name%type,
103 column_hierarchy varchar2(1),
104 table_hierarchy varchar2(1),
105 use_non_pk_col_for_chk_row varchar2(1),
106 resolve_pk varchar2(1),
107 ins_resolve_pk varchar2(1),
108 use_distinct varchar2(1),
109 fk_to_aol_table varchar2(1),
110 missing_who_info varchar2(1),
111 missing_primary_key varchar2(1),
112 chk_row_exists_for_non_glb_tbl varchar2(1),
113 who_link_alias hr_dm_tables.who_link_alias%type,
114 derive_sql_download_full hr_dm_tables.derive_sql_download_full%type,
115 derive_sql_download_add hr_dm_tables.derive_sql_download_add%type,
116 derive_sql_calc_ranges hr_dm_tables.derive_sql_calc_ranges%type,
117 derive_sql_delete_source hr_dm_tables.derive_sql_delete_source%type,
118 derive_sql_source_tables hr_dm_tables.derive_sql_source_tables%type,
119 derive_sql_chk_source_tables hr_dm_tables.derive_sql_chk_source_tables%type,
120 derive_sql_chk_row_exists hr_dm_tables.derive_sql_chk_row_exists%type,
121 global_data hr_dm_tables.global_data%type,
122 upload_table_name hr_dm_tables.upload_table_name%type,
123 use_distinct_download hr_dm_tables.use_distinct_download%type,
124 always_check_row hr_dm_tables.always_check_row%type,
125 sequence_name hr_dm_tables.sequence_name%type
126 );
127
128 type t_table_info_tbl is table of t_table_info index by binary_integer;
129
130 --
131 -- This table structure stores the information for the columns which have
132 -- have a foreign key to the aol table.
133 --
134 -- column_name - Name of the column of the table which has foreign key
135 -- on AOL table.
136 -- parent_table_id - Table Id (Primary key of HR_DM_TABLES) of the AOL table.
137 -- parent_table_name - Name of the AOL table.
138 -- parent_table_alias - Alias of the AOL table.
139 -- parent_column_name - Developer key of the AOL table.
140 -- parent_id_column_name - Primary key of AOL table.
141
142 type t_fk_to_aol_columns_info is record
143 (
144 column_name hr_dm_hierarchies.column_name%type,
145 parent_table_id hr_dm_hierarchies.parent_table_id%type,
146 parent_table_name hr_dm_tables.table_name%type,
147 parent_table_alias hr_dm_tables.table_alias%type,
148 parent_column_name hr_dm_hierarchies.parent_column_name%type,
149 parent_id_column_name hr_dm_hierarchies.parent_id_column_name%type
150 );
151
152 type t_fk_to_aol_columns_tbl is table of t_fk_to_aol_columns_info index by
153 binary_integer;
154 -- ------------------------- post_generate_validate --------------------------
155 -- Description:
156 -- This function is called immediately after Generate phase is marked as
157 -- completed. It checks following for each table listed in the Generate phase :
158 -- - If the status of TUPS or TDS pakage is invaild then it
159 -- - Generates the TUPS/TDS for the table. If it is still invalid
160 -- i.e TUPS/TDS generator staus is still invalid or any compilation
161 -- error, then it stops the processing.
162 -- - If there is no TUPS/TDS package then it Generates the package.
163 -- - If status of the phase item is other than 'C' then it generates the
164 -- TUPS/TDS for that table.
165 -- ------------------------------------------------------------------------
166 procedure post_generate_validate
167 (p_migration_id in number
168 );
169 -- ------------------------- slave_generator_for_tbl --------------------------
170 -- Description:
171 -- It generates TUPS/TDS for a given table.It calls
172 -- TUPS Generator to generate TUPS for the table
173 -- Seed the data into data pump for TUPS.
174 -- TDS Generator to generate TDS for the table.
175 --
176 -- ------------------------------------------------------------------------
177 procedure slave_generator_for_tbl
178 (
179 p_phase_item_id in number
180 );
181 -- ------------------------- slave_generator ------------------------------
182 -- Description:
183 -- It generates TUPS/TDS for all the tables in the Generator phase for the
184 -- given migration run.
185 -- It reads the unprocessed table from Phase_Item table.It calls
186 -- TUPS Generator to generate TUPS for the table
187 -- Seed the data into data pump for TUPS.
188 -- TDS Generator to generate TDS for the table.
189 -- Input Parameters :
190 -- p_migration_id - ID of the migration. Primary Key of
191 -- HR_DM_MIGRATIONS table.
192 -- p_concurrent_process - Can have following values :
193 -- 'Y' - Migration is run as a concurrent process
194 -- so create a log file.
195 -- 'N' - Migration is not run from concurrent
196 -- process,so don't create a log file.
197 -- p_last_migration_date - This parameter is added so as to have generic
198 -- master program which spawns slave processes.
199 -- This process does not use this parameter.
200 -- p_process_number - To prevent the locking issue each slave process
201 -- will be passed the process number by master.
202 -- Main cursor has been modified so as a row is
203 -- processed by one process only. This is achieve
204 -- by following:
205 -- MOD (primary_key, total_no_of_threads/slave_processes) + 1 = p_process_number
206 --
207 -- Output Parameters
208 -- errbuf - buffer for output message (for CM manager)
209 --
210 -- retcode - program return code (for CM manager)
211 --
212 -- ------------------------------------------------------------------------
213 procedure slave_generator
214 (
215 errbuf out nocopy varchar2,
216 retcode out nocopy number ,
217 p_migration_id in number ,
218 p_concurrent_process in varchar2 default 'Y',
219 p_last_migration_date in date,
220 p_process_number in number
221 );
222
223 -- ------------------------- chk_ins_resolve_pk ------------------------
224 -- Description:
225 -- It checks whether a table has a child table with hierarchy type 'L'.
226 -- ------------------------------------------------------------------------
227 function chk_ins_resolve_pk
228 (
229 p_table_id in number
230 ) return varchar2;
231
232
233 end hr_dm_gen_main;
[Home] [Help]