1 PACKAGE ben_dm_data_util AUTHID CURRENT_USER AS
2 /* $Header: benfdmdutl.pkh 120.0 2006/05/04 04:41:22 nkkrishn noship $ */
3
4 --
5 --------------------------
6 -- CONSTANT DEFINITIONS --
7 --------------------------
8 c_newline constant varchar2(500) := fnd_global.newline;
9 -- Tables to assist in code generation.
10 --
11 type t_varchar2_tbl is table of varchar2(100) index by binary_integer;
12 g_columns_tbl t_varchar2_tbl;
13 g_proc_parameter_tbl t_varchar2_tbl;
14
15 type charTab is table of varchar2(32767) index by binary_integer;
16
17
18 -- Declare records
19 --
20
21 type pk_mapping_rec is record (target_id number
22 ,table_name varchar2(30)
23 ,source_id number
24 ,source_column varchar2(30)
25 ,business_group_name varchar2(80)
26 ) ;
27
28
29 --TYPE pk_maping_tbl IS TABLE OF pk_mapping_rec INDEX BY Binary_Integer;
30 TYPE pk_maping_tbl IS TABLE OF number INDEX BY varchar2(255);
31 g_pk_maping_tbl pk_maping_tbl ;
32 g_fk_maping_tbl pk_maping_tbl ;
33
34 type g_rm is record
35 (resolve_mapping_id number,
36 table_name varchar2(30),
37 source_id number,
38 column_name varchar2(30),
39 business_group_name varchar2(80));
40
41 type g_rm_type is table of g_rm index by binary_integer;
42
43 g_resolve_mapping_cache g_rm_type;
44
45
46
47
48 function get_mapping_target(
49 p_resolve_mapping_id in NUMBER
50 ) return number ;
51
52
53
54 function get_mapping_target(p_table_name in varchar2
55 ,p_source_id in number
56 ,p_source_column in varchar2
57 ,p_business_group_name in varchar2
58 ) return number ;
59
60
61 function get_cache_target(p_table_name in varchar2
62 ,p_source_id in number
63 ,p_source_column in varchar2
64 ,p_business_group_name in varchar2
65 ) return number ;
66
67 procedure create_pk_cache ( p_target_id in number
68 ,p_table_name in varchar2
69 ,p_source_id in number
70 ,p_source_column in varchar2
71 ,p_business_group_name in varchar2
72 ) ;
73
74
75 procedure create_fk_cache ;
76
77 procedure update_pk_mapping(
78 p_resolve_mapping_id in NUMBER DEFAULT null
79 ,p_target_id in NUMBER
80 ,p_table_name in VARCHAR2 DEFAULT null
81 ,p_column_name in VARCHAR2 DEFAULT null
82 ,p_source_id in NUMBER DEFAULT null
83 ,p_source_column in VARCHAR2 DEFAULT null
84 ,p_business_group_name in VARCHAR2 DEFAULT null
85 ,p_table_id in NUMBER DEFAULT null
86 ) ;
87
88
89 procedure create_pk_mapping(
90 p_resolve_mapping_id out nocopy NUMBER
91 ,p_table_name in VARCHAR2 default null
92 ,p_table_id in NUMBER default null
93 ,p_column_name in VARCHAR2
94 ,p_source_id in NUMBER
95 ,p_source_key in VARCHAR2
96 ,p_target_id in NUMBER default null
97 ,p_business_group_name in VARCHAR2
98 ,p_mapping_type in VARCHAR2 default 'D'
99 ,p_resolve_mapping_id1 in NUMBER default null
100 ,p_resolve_mapping_id2 in NUMBER default null
101 ,p_resolve_mapping_id3 in NUMBER default null
102 ,p_resolve_mapping_id4 in NUMBER default null
103 ,p_resolve_mapping_id5 in NUMBER default null
104 ,p_resolve_mapping_id6 in NUMBER default null
105 ,p_resolve_mapping_id7 in NUMBER default null
106 ,p_last_update_date in DATE default null
107 ,p_last_updated_by in NUMBER default null
108 ,p_last_update_login in NUMBER default null
109 ,p_created_by in NUMBER default null
110 ,p_creation_date in DATE default null ) ;
111
112
113 procedure create_entity_result(p_entity_result_id OUT NOCOPY NUMBER ,
114 p_migration_id IN NUMBER ,
115 p_table_name IN VARCHAR2 ,
116 p_group_order IN NUMBER ,
117 p_information1 IN VARCHAR2 DEFAULT NULL ,
118 p_information2 IN VARCHAR2 DEFAULT NULL ,
119 p_information3 IN VARCHAR2 DEFAULT NULL ,
120 p_information4 IN VARCHAR2 DEFAULT NULL ,
121 p_information5 IN VARCHAR2 DEFAULT NULL ,
122 p_information6 IN VARCHAR2 DEFAULT NULL ,
123 p_information7 IN VARCHAR2 DEFAULT NULL ,
124 p_information8 IN VARCHAR2 DEFAULT NULL ,
125 p_information9 IN VARCHAR2 DEFAULT NULL ,
126 p_information10 IN VARCHAR2 DEFAULT NULL ,
127 p_information11 IN VARCHAR2 DEFAULT NULL ,
128 p_information12 IN VARCHAR2 DEFAULT NULL ,
129 p_information13 IN VARCHAR2 DEFAULT NULL ,
130 p_information14 IN VARCHAR2 DEFAULT NULL ,
131 p_information15 IN VARCHAR2 DEFAULT NULL ,
132 p_information16 IN VARCHAR2 DEFAULT NULL ,
133 p_information17 IN VARCHAR2 DEFAULT NULL ,
134 p_information18 IN VARCHAR2 DEFAULT NULL ,
135 p_information19 IN VARCHAR2 DEFAULT NULL ,
136 p_information20 IN VARCHAR2 DEFAULT NULL ,
137 p_information21 IN VARCHAR2 DEFAULT NULL ,
138 p_information22 IN VARCHAR2 DEFAULT NULL ,
139 p_information23 IN VARCHAR2 DEFAULT NULL ,
140 p_information24 IN VARCHAR2 DEFAULT NULL ,
141 p_information25 IN VARCHAR2 DEFAULT NULL ,
142 p_information26 IN VARCHAR2 DEFAULT NULL ,
143 p_information27 IN VARCHAR2 DEFAULT NULL ,
144 p_information28 IN VARCHAR2 DEFAULT NULL ,
145 p_information29 IN VARCHAR2 DEFAULT NULL ,
146 p_information30 IN VARCHAR2 DEFAULT NULL ,
147 p_information31 IN VARCHAR2 DEFAULT NULL ,
148 p_information32 IN VARCHAR2 DEFAULT NULL ,
149 p_information33 IN VARCHAR2 DEFAULT NULL ,
150 p_information34 IN VARCHAR2 DEFAULT NULL ,
151 p_information35 IN VARCHAR2 DEFAULT NULL ,
152 p_information36 IN VARCHAR2 DEFAULT NULL ,
153 p_information37 IN VARCHAR2 DEFAULT NULL ,
154 p_information38 IN VARCHAR2 DEFAULT NULL ,
155 p_information39 IN VARCHAR2 DEFAULT NULL ,
156 p_information40 IN VARCHAR2 DEFAULT NULL ,
157 p_information41 IN VARCHAR2 DEFAULT NULL ,
158 p_information42 IN VARCHAR2 DEFAULT NULL ,
159 p_information43 IN VARCHAR2 DEFAULT NULL ,
160 p_information44 IN VARCHAR2 DEFAULT NULL ,
161 p_information45 IN VARCHAR2 DEFAULT NULL ,
162 p_information46 IN VARCHAR2 DEFAULT NULL ,
163 p_information47 IN VARCHAR2 DEFAULT NULL ,
164 p_information48 IN VARCHAR2 DEFAULT NULL ,
165 p_information49 IN VARCHAR2 DEFAULT NULL ,
166 p_information50 IN VARCHAR2 DEFAULT NULL ,
167 p_information51 IN VARCHAR2 DEFAULT NULL ,
168 p_information52 IN VARCHAR2 DEFAULT NULL ,
169 p_information53 IN VARCHAR2 DEFAULT NULL ,
170 p_information54 IN VARCHAR2 DEFAULT NULL ,
171 p_information55 IN VARCHAR2 DEFAULT NULL ,
172 p_information56 IN VARCHAR2 DEFAULT NULL ,
173 p_information57 IN VARCHAR2 DEFAULT NULL ,
174 p_information58 IN VARCHAR2 DEFAULT NULL ,
175 p_information59 IN VARCHAR2 DEFAULT NULL ,
176 p_information60 IN VARCHAR2 DEFAULT NULL ,
177 p_information61 IN VARCHAR2 DEFAULT NULL ,
178 p_information62 IN VARCHAR2 DEFAULT NULL ,
179 p_information63 IN VARCHAR2 DEFAULT NULL ,
180 p_information64 IN VARCHAR2 DEFAULT NULL ,
181 p_information65 IN VARCHAR2 DEFAULT NULL ,
182 p_information66 IN VARCHAR2 DEFAULT NULL ,
183 p_information67 IN VARCHAR2 DEFAULT NULL ,
184 p_information68 IN VARCHAR2 DEFAULT NULL ,
185 p_information69 IN VARCHAR2 DEFAULT NULL ,
186 p_information70 IN VARCHAR2 DEFAULT NULL ,
187 p_information71 IN VARCHAR2 DEFAULT NULL ,
188 p_information72 IN VARCHAR2 DEFAULT NULL ,
189 p_information73 IN VARCHAR2 DEFAULT NULL ,
190 p_information74 IN VARCHAR2 DEFAULT NULL ,
191 p_information75 IN VARCHAR2 DEFAULT NULL ,
192 p_information76 IN VARCHAR2 DEFAULT NULL ,
193 p_information77 IN VARCHAR2 DEFAULT NULL ,
194 p_information78 IN VARCHAR2 DEFAULT NULL ,
195 p_information79 IN VARCHAR2 DEFAULT NULL ,
196 p_information80 IN VARCHAR2 DEFAULT NULL ,
197 p_information81 IN VARCHAR2 DEFAULT NULL ,
198 p_information82 IN VARCHAR2 DEFAULT NULL ,
199 p_information83 IN VARCHAR2 DEFAULT NULL ,
200 p_information84 IN VARCHAR2 DEFAULT NULL ,
201 p_information85 IN VARCHAR2 DEFAULT NULL ,
202 p_information86 IN VARCHAR2 DEFAULT NULL ,
203 p_information87 IN VARCHAR2 DEFAULT NULL ,
204 p_information88 IN VARCHAR2 DEFAULT NULL ,
205 p_information89 IN VARCHAR2 DEFAULT NULL ,
206 p_information90 IN VARCHAR2 DEFAULT NULL ,
207 p_information91 IN VARCHAR2 DEFAULT NULL ,
208 p_information92 IN VARCHAR2 DEFAULT NULL ,
209 p_information93 IN VARCHAR2 DEFAULT NULL ,
210 p_information94 IN VARCHAR2 DEFAULT NULL ,
211 p_information95 IN VARCHAR2 DEFAULT NULL ,
212 p_information96 IN VARCHAR2 DEFAULT NULL ,
213 p_information97 IN VARCHAR2 DEFAULT NULL ,
214 p_information98 IN VARCHAR2 DEFAULT NULL ,
215 p_information99 IN VARCHAR2 DEFAULT NULL ,
216 p_information100 IN VARCHAR2 DEFAULT NULL ,
217 p_information101 IN VARCHAR2 DEFAULT NULL ,
218 p_information102 IN VARCHAR2 DEFAULT NULL ,
219 p_information103 IN VARCHAR2 DEFAULT NULL ,
220 p_information104 IN VARCHAR2 DEFAULT NULL ,
221 p_information105 IN VARCHAR2 DEFAULT NULL ,
222 p_information106 IN VARCHAR2 DEFAULT NULL ,
223 p_information107 IN VARCHAR2 DEFAULT NULL ,
224 p_information108 IN VARCHAR2 DEFAULT NULL ,
225 p_information109 IN VARCHAR2 DEFAULT NULL ,
226 p_information110 IN VARCHAR2 DEFAULT NULL ,
227 p_information111 IN VARCHAR2 DEFAULT NULL ,
228 p_information112 IN VARCHAR2 DEFAULT NULL ,
229 p_information113 IN VARCHAR2 DEFAULT NULL ,
230 p_information114 IN VARCHAR2 DEFAULT NULL ,
231 p_information115 IN VARCHAR2 DEFAULT NULL ,
232 p_information116 IN VARCHAR2 DEFAULT NULL ,
236 p_information120 IN VARCHAR2 DEFAULT NULL ,
233 p_information117 IN VARCHAR2 DEFAULT NULL ,
234 p_information118 IN VARCHAR2 DEFAULT NULL ,
235 p_information119 IN VARCHAR2 DEFAULT NULL ,
237 p_information121 IN VARCHAR2 DEFAULT NULL ,
238 p_information122 IN VARCHAR2 DEFAULT NULL ,
239 p_information123 IN VARCHAR2 DEFAULT NULL ,
240 p_information124 IN VARCHAR2 DEFAULT NULL ,
241 p_information125 IN VARCHAR2 DEFAULT NULL ,
242 p_information126 IN VARCHAR2 DEFAULT NULL ,
243 p_information127 IN VARCHAR2 DEFAULT NULL ,
244 p_information128 IN VARCHAR2 DEFAULT NULL ,
245 p_information129 IN VARCHAR2 DEFAULT NULL ,
246 p_information130 IN VARCHAR2 DEFAULT NULL ,
247 p_information131 IN NUMBER DEFAULT NULL ,
248 p_information132 IN NUMBER DEFAULT NULL ,
249 p_information133 IN NUMBER DEFAULT NULL ,
250 p_information134 IN NUMBER DEFAULT NULL ,
251 p_information135 IN NUMBER DEFAULT NULL ,
252 p_information136 IN NUMBER DEFAULT NULL ,
253 p_information137 IN NUMBER DEFAULT NULL ,
254 p_information138 IN NUMBER DEFAULT NULL ,
255 p_information139 IN NUMBER DEFAULT NULL ,
256 p_information140 IN NUMBER DEFAULT NULL ,
257 p_information141 IN NUMBER DEFAULT NULL ,
258 p_information142 IN NUMBER DEFAULT NULL ,
259 p_information143 IN NUMBER DEFAULT NULL ,
260 p_information144 IN NUMBER DEFAULT NULL ,
261 p_information145 IN NUMBER DEFAULT NULL ,
262 p_information146 IN NUMBER DEFAULT NULL ,
263 p_information147 IN NUMBER DEFAULT NULL ,
264 p_information148 IN NUMBER DEFAULT NULL ,
265 p_information149 IN NUMBER DEFAULT NULL ,
266 p_information150 IN NUMBER DEFAULT NULL ,
267 p_information151 IN NUMBER DEFAULT NULL ,
268 p_information152 IN NUMBER DEFAULT NULL ,
269 p_information153 IN NUMBER DEFAULT NULL ,
270 p_information154 IN NUMBER DEFAULT NULL ,
271 p_information155 IN NUMBER DEFAULT NULL ,
272 p_information156 IN NUMBER DEFAULT NULL ,
273 p_information157 IN NUMBER DEFAULT NULL ,
274 p_information158 IN NUMBER DEFAULT NULL ,
275 p_information159 IN NUMBER DEFAULT NULL ,
276 p_information160 IN NUMBER DEFAULT NULL ,
277 p_information161 IN NUMBER DEFAULT NULL ,
278 p_information162 IN NUMBER DEFAULT NULL ,
279 p_information163 IN NUMBER DEFAULT NULL ,
280 p_information164 IN NUMBER DEFAULT NULL ,
281 p_information165 IN NUMBER DEFAULT NULL ,
282 p_information166 IN NUMBER DEFAULT NULL ,
283 p_information167 IN NUMBER DEFAULT NULL ,
284 p_information168 IN NUMBER DEFAULT NULL ,
285 p_information169 IN NUMBER DEFAULT NULL ,
286 p_information170 IN NUMBER DEFAULT NULL ,
287 p_information171 IN NUMBER DEFAULT NULL ,
288 p_information172 IN NUMBER DEFAULT NULL ,
289 p_information173 IN NUMBER DEFAULT NULL ,
290 p_information174 IN NUMBER DEFAULT NULL ,
291 p_information175 IN NUMBER DEFAULT NULL ,
292 p_information176 IN NUMBER DEFAULT NULL ,
293 p_information177 IN NUMBER DEFAULT NULL ,
294 p_information178 IN NUMBER DEFAULT NULL ,
295 p_information179 IN NUMBER DEFAULT NULL ,
296 p_information180 IN NUMBER DEFAULT NULL ,
297 p_information181 IN NUMBER DEFAULT NULL ,
298 p_information182 IN NUMBER DEFAULT NULL ,
299 p_information183 IN NUMBER DEFAULT NULL ,
300 p_information184 IN NUMBER DEFAULT NULL ,
301 p_information185 IN NUMBER DEFAULT NULL ,
302 p_information186 IN NUMBER DEFAULT NULL ,
303 p_information187 IN NUMBER DEFAULT NULL ,
304 p_information188 IN NUMBER DEFAULT NULL ,
305 p_information189 IN NUMBER DEFAULT NULL ,
306 p_information190 IN NUMBER DEFAULT NULL ,
307 p_information191 IN NUMBER DEFAULT NULL ,
308 p_information192 IN NUMBER DEFAULT NULL ,
309 p_information193 IN NUMBER DEFAULT NULL ,
310 p_information194 IN NUMBER DEFAULT NULL ,
314 p_information198 IN NUMBER DEFAULT NULL ,
311 p_information195 IN NUMBER DEFAULT NULL ,
312 p_information196 IN NUMBER DEFAULT NULL ,
313 p_information197 IN NUMBER DEFAULT NULL ,
315 p_information199 IN NUMBER DEFAULT NULL ,
316 p_information200 IN NUMBER DEFAULT NULL ,
317 p_information201 IN NUMBER DEFAULT NULL ,
318 p_information202 IN NUMBER DEFAULT NULL ,
319 p_information203 IN NUMBER DEFAULT NULL ,
320 p_information204 IN NUMBER DEFAULT NULL ,
321 p_information205 IN NUMBER DEFAULT NULL ,
322 p_information206 IN NUMBER DEFAULT NULL ,
323 p_information207 IN NUMBER DEFAULT NULL ,
324 p_information208 IN NUMBER DEFAULT NULL ,
325 p_information209 IN NUMBER DEFAULT NULL ,
326 p_information210 IN NUMBER DEFAULT NULL ,
327 p_information211 IN DATE DEFAULT NULL ,
328 p_information212 IN DATE DEFAULT NULL ,
329 p_information213 IN DATE DEFAULT NULL ,
330 p_information214 IN DATE DEFAULT NULL ,
331 p_information215 IN DATE DEFAULT NULL ,
332 p_information216 IN DATE DEFAULT NULL ,
333 p_information217 IN DATE DEFAULT NULL ,
334 p_information218 IN DATE DEFAULT NULL ,
335 p_information219 IN DATE DEFAULT NULL ,
336 p_information220 IN DATE DEFAULT NULL ,
337 p_information221 IN DATE DEFAULT NULL ,
338 p_information222 IN DATE DEFAULT NULL ,
339 p_information223 IN DATE DEFAULT NULL ,
340 p_information224 IN DATE DEFAULT NULL ,
341 p_information225 IN DATE DEFAULT NULL ,
342 p_information226 IN DATE DEFAULT NULL ,
343 p_information227 IN DATE DEFAULT NULL ,
344 p_information228 IN DATE DEFAULT NULL ,
345 p_information229 IN DATE DEFAULT NULL ,
346 p_information230 IN DATE DEFAULT NULL ,
347 p_information231 IN DATE DEFAULT NULL ,
348 p_information232 IN DATE DEFAULT NULL ,
349 p_information233 IN DATE DEFAULT NULL ,
350 p_information234 IN DATE DEFAULT NULL ,
351 p_information235 IN DATE DEFAULT NULL ,
352 p_information236 IN DATE DEFAULT NULL ,
353 p_information237 IN DATE DEFAULT NULL ,
354 p_information238 IN DATE DEFAULT NULL ,
355 p_information239 IN DATE DEFAULT NULL ,
356 p_information240 IN DATE DEFAULT NULL ,
357 p_information241 IN DATE DEFAULT NULL ,
358 p_information242 IN DATE DEFAULT NULL ,
359 p_information243 IN DATE DEFAULT NULL ,
360 p_information244 IN DATE DEFAULT NULL ,
361 p_information245 IN DATE DEFAULT NULL
362 ) ;
363
364 function get_bg_id(p_business_group_name in VARCHAR2) Return Number;
365
366
367
368
369 procedure get_generator_version
370 (
371 p_generator_version out nocopy varchar2,
372 p_format_output in varchar2 default 'N'
373 ) ;
374
375 --
376 -- Delete Process during Upload of a Group.
377 --
378 procedure delete_process
379 (p_migration_id in number
380 ,p_group_order in number);
381
382
383
384 Procedure Load_table ( p_table_name in varchar2
385 ,p_owner in varchar2
386 ,p_last_update_date in varchar2
387 ,p_upload_table_name in varchar2
388 ,p_table_alias in varchar2
389 ,p_datetrack in varchar2
390 ,p_derive_sql in varchar2
391 ,p_surrogate_pk_column_name in varchar2
392 ,p_short_name in varchar2
393 ,p_sequence_name in varchar2
394 );
395
396 procedure load_table_order(
397 p_Table_name in varchar2
398 ,p_owner in varchar2
399 ,p_table_order in varchar2
400 ,p_last_update_date in varchar2
401 ) ;
402
403
404 procedure load_HIERARCHY(
405 p_Table_name in varchar2
406 ,p_column_name in varchar2
407 ,p_hierarchy_type in varchar2
408 ,p_owner in varchar2
409 ,p_last_update_date in varchar2
410 ,p_parent_table_name in varchar2
414
411 ,p_parent_column_name in varchar2
412 ,p_parent_id_column_name in varchar2
413 );
415
416 procedure load_mappings(
417 p_Table_name in varchar2
418 ,p_column_name in varchar2
419 ,p_owner in varchar2
420 ,p_last_update_date in varchar2
421 ,p_entity_result_column_name in varchar2
422 ) ;
423
424
425 procedure load_HR_PHASE_RULE(
426 p_MIGRATION_TYPE IN VARCHAR2
427 ,p_PHASE_NAME IN VARCHAR2
428 ,p_PREVIOUS_PHASE IN VARCHAR2
429 ,p_NEXT_PHASE IN VARCHAR2
430 ,p_DATABASE_LOCATION IN VARCHAR2
431 ,p_LAST_UPDATE_DATE IN VARCHAR2
432 ,p_OWNER IN VARCHAR2
433 ,p_SECURITY_GROUP_ID IN VARCHAR2
434 ) ;
435
436
437
438 Procedure update_gen_version (p_table_id in number
439 ,p_version in varchar2
440 ) ;
441
442 function get_dm_flag return varchar2 ;
443 pragma restrict_references (get_dm_flag,WNPS,WNDS);
444
445 end ben_dm_data_util;