1 package ad_file_util AUTHID CURRENT_USER as
2 /* $Header: adfiluts.pls 120.1.12020000.3 2012/08/07 14:14:11 mkumandu ship $ */
3
4 error_buf varchar2(32760);
5
6 procedure lock_infrastructure;
7
8 procedure unlock_infrastructure;
9
10
11 --
12 -- Procedure
13 -- lock_and_empty_temp_table
14 --
15 -- Purpose
16 -- Serializes access to the AD_CHECK_FILE_TEMP table using a User Lock
17 -- (created using DBMS_LOCK mgmt services), and also empties the table.
18 -- This lock would be a session-level lock, and is intended to be released
19 -- when the calling script is totally done with its use of the temp table.
20 --
21 -- This is especially necessary when we have multiple scripts that use
22 -- the infrastructure built around AD_CHECK_FILE_TEMP, that perhaps could
23 -- be running in parallel. As of 2/25/02, we already a case for
24 -- this, viz. the snapshot preseeding scripts and the checkfile preseeding
25 -- scripts use the same temp table. In the absence of such a serializing
26 -- facility, they could end up stamping on each others feet (eg. creating
27 -- bugs as files and files as bugs!!)
28 --
29 -- Usage
30 -- Any script that uses the AD_CHECK_FILE_TEMP infrastructure must do the
31 -- following:
32 -- a) Call lock_and_empty_temp_table
33 -- b) Insert rows into AD_CHECK_FILE_TEMP
34 -- c) Gather statistics on AD_CHECK_FILE_TEMP
35 -- d) Call the relevant packaged-procedure that reads the temp table and
36 -- loads whatever is necessary.
37 -- e) Commit.
38 --
39 -- Then repeat steps (a) thru (e) for other rows. When all batches have
40 -- finished processing, then unlock_infrastructure() should be called to
41 -- release the User Lock at the very end.
42 --
43 -- Arguments
44 -- APPLSYS schema name
45 --
46 procedure lock_and_empty_temp_table
47 (p_un_fnd varchar2);
48
49 --
50 -- Procedure
51 -- load_file_info
52 --
53 -- Purpose
54 -- Imports file information from ad_check_file_temp to ad_files
55 --
56 -- Only creates rows that don't already exist.
57 --
58 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
59 --
60 -- To handle batch sizes:
61 --
62 -- 1) - fill up whole table with null active_flag
63 -- - In a loop:
64 -- - update a batch to have active_flag='Y'
65 -- - process the batch
66 -- - delete the batch
67 -- - using 'where rownum < batch+1' is handy here
68 --
69 -- 2) perform (truncate, load, process) cycles in an outer loop where
70 -- only <batch size> rows are loaded and processed at a time.
71 --
72 -- Updates the file_id column of ad_check_file_temp so that all
73 -- rows point to the file_id of the file referenced in the row.
74 --
75 -- Arguments
76 -- none
77 --
78 procedure load_file_info;
79
80 --
81 -- Procedure
82 -- load_file_version_info
83 --
84 -- Purpose
85 -- Imports file information from ad_check_file_temp to ad_files and
86 -- ad_file_versions.
87 --
88 -- Only creates rows that don't already exist.
89 --
90 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
91 --
92 -- To handle batch sizes:
93 --
94 -- 1) - fill up whole table with null active_flag
95 -- - In a loop:
96 -- - update a batch to have active_flag='Y'
97 -- - process the batch
98 -- - delete the batch
99 -- - using 'where rownum < batch+1' is handy here
100 --
101 -- 2) perform (truncate, load, process) cycles in an outer loop where
102 -- only <batch size> rows are loaded and processed at a time.
103 --
104 -- Calls load_file_info
105 --
106 -- Updates the file_version_id column of ad_check_file_temp so that all
107 -- rows point to the file_version_id of the file version referenced
108 -- in the row.
109 --
110 -- Arguments
111 -- none
112 --
113 procedure load_file_version_info;
114
115 --
116 -- Procedure
117 -- load_checkfile_info
118 --
119 -- Purpose
120 -- Imports file information from ad_check_file_temp to ad_files,
121 -- ad_file_versions, and ad_check_files.
122 --
123 -- Only creates rows in ad_files and ad_file_versions that don't
124 -- already exist. In ad_check_files, it creates rows that don't already
125 -- exist and also updates existing rows if the version to load is higher
126 -- than the current version in ad_check_files.
127 --
128 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
129 --
130 -- To handle batch sizes:
131 --
132 -- 1) - fill up whole table with null active_flag
133 -- - In a loop:
134 -- - update a batch to have active_flag='Y'
135 -- - process the batch
136 -- - delete the batch
137 -- - using 'where rownum < batch+1' is handy here
138 --
139 -- 2) perform (truncate, load, process) cycles in an outer loop where
140 -- only <batch size> rows are loaded and processed at a time.
141 --
142 -- Calls load_file_version_info
143 --
144 -- Updates the check_file_id column of ad_check_file_temp so that any
145 -- rows that were already in ad_check_files point to the check_file_id
146 -- of the (file, distinguisher) referenced in the row. Rows in
147 -- ad_check_file_temp that did not already have corresponding rows in
148 -- ad_check_files still have null values for check_file_id
149 -- (assuming they started out as null)
150 --
151 -- Arguments
152 -- p_ebr_flow => true populate ad_check_file_history table
153 -- => false doesn't populate ad_check_file_history table
154 --
155 procedure load_checkfile_info(p_ebr_flow boolean default false);
156
157 --
158 -- Procedure
159 -- update_timestamp
160 --
161 -- Purpose
162 -- Inserts/updates a row in AD_TIMESTAMPS corresponding to the
163 -- specified row type and attribute.
164 --
165 -- Arguments
166 -- in_type The row type
167 -- in_attribute The row attribute
168 -- in_timestamp A timestamp. Defaults to sysdate.
169 --
170 -- Notes
171 -- This is essentially the same as ad_invoker.update_timestamp
172 -- Added it here to make it easier to call from APPS.
173 --
174 procedure update_timestamp
175 (in_type in varchar2,
176 in_attribute in varchar2,
177 in_timestamp in date);
178
179 procedure update_timestamp
180 (in_type in varchar2,
181 in_attribute in varchar2);
182 --
183 --
184 --
185 -- Procedure
186 -- load_patch_onsite_vers_info
187 --
188 -- Purpose
189 -- Imports file information from ad_check_file_temp to ad_files and
190 -- ad_file_versions.
191 --
192 -- Only creates rows that don't already exist.
193 --
194 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
195 --
196 -- To handle batch sizes:
197 --
198 -- 1) - fill up whole table with null active_flag
199 -- - In a loop:
200 -- - update a batch to have active_flag='Y'
201 -- - process the batch
202 -- - delete the batch
203 -- - using 'where rownum < batch+1' is handy here
204 --
205 -- 2) perform (truncate, load, process) cycles in an outer loop where
206 -- only <batch size> rows are loaded and processed at a time.
207 --
208 -- Calls load_file_info
209 --
210 -- Updates the file_version_id and file_version_id_2 columns of
211 -- ad_check_file_temp so that all rows point to the file_version_id
212 -- of the file versions referenced in the row.
213 --
214 -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
215 -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
216 -- "no version for this file", so no corresponding record should be
217 -- created in ad_file_versions.
218 --
219 -- Arguments
220 -- none
221 --
222 procedure load_patch_onsite_vers_info;
223 --
224 --
225 --
226 -- Procedure
227 -- load_snapshot_file_info
228 --
229 -- Purpose
230 -- Create Snapshot data by
231 -- 1.Calls ad_file_versions and loads the file versions
232 -- into the ad_check_file_temp table .
233 -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
234 -- which have the same file_id, snapshot_id and containing_file_id
235 -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
236 -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
237 -- for the given snapshot_id
238 -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
239 -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
240 -- for the given snapshot_id
241 --
242 -- Arguments
243 -- is_upload pass TRUE if it is an upload otherwise FALSE
244 --
245 --
246 procedure load_snapshot_file_info
247 (snp_id number,
248 preserve_irep_flag number);
249 --
250 --
251 --
252 -- Procedure
253 -- load_preseeded_bugfixes
254 --
255 -- Purpose
256 -- Gets the bug_id from AD_BUGS for the bugnumbers in
257 -- in ad_check_file_temp table .
258 -- Creates new rows in the AD_BUGS for the new bugnumbers
259 -- and gets the bug_id for those bugnumbers and stores them
260 -- ad_check_file_temp table .
261 --
262 -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
263 --
264 --
265 -- Arguments
266 -- None
267 --
268 procedure load_preseeded_bugfixes;
269 --
270 --
271 procedure load_patch_hist_action
272 (bugs_processed out NOCOPY number,
273 actions_processed out NOCOPY number);
274
275 -- Procedure
276 -- create_global_view
277 -- Arguments
278 -- p_apps_system_name - Applications system name
279 -- Purpose
280 -- Procedure to create Global View snapshot using exisiting
281 -- current view snapshots for an applications system.
282 -- Notes
283
284 procedure create_global_view(p_apps_system_name varchar2,
285 p_is_run_flow boolean default true);
286 --
287 --
288 -- Procedure
289 -- populate_snapshot_files_temp
290 -- Arguments
291 -- p_apps_system_name - Applications System Name
292 --
293 -- p_min_file_id - lower limit file_id in the range of file_ids
294 --
295 -- p_max_file_id - upper limit file_id in the range of file_ids
296 --
297 -- p_global_snapshot_id - Global snapshot_id
298 --
299 -- p_un_fnd - applsys username
300 --
301 -- p_iteration - which iteration (1,2,etc)
302 -- Purpose
303 -- This procedure populates temp table with a range of file_ids
304 -- processes the data and updates the ad_snapshot_files with negative
305 -- global snapshot_id
306 -- Notes
307 --
308
309 procedure populate_snapshot_files_temp(p_applications_sys_name varchar2,p_min_file_id number,
310 p_max_file_id number,p_global_snapshot_id number,
311 p_un_fnd varchar2,p_iteration number,
312 p_is_run_flow boolean default true);
313
314 --
315 --
316 -- Procedure
317 -- populate_snapshot_bugs_temp
318 -- Arguments
319 -- p_apps_system_name - Applications System Name
320 --
321 -- p_min_bug_id - lower limit bugfix_id in the range of bugfix_id
322 --
323 -- p_max_bug_id - upper limit bugfix_id in the range of bugfix_id
324 --
325 -- p_global_snapshot_id - Global snapshot_id
326 --
327 -- p_un_fnd - applsys username
328 --
329 -- p_iteration - which iteration (1,2,etc)
330 -- Purpose
331 -- This procedure populates temp table with a range of file_ids
332 -- processes the data and updates the ad_snapshot_bugfixes with negative
333 -- global snapshot_id
334 -- Notes
335 --
336 procedure populate_snapshot_bugs_temp(p_applications_sys_name varchar2,p_min_bug_id number,
337 p_max_bug_id number,p_global_snapshot_id number,
338 p_un_fnd varchar2,p_iteration number,
339 p_is_run_flow boolean default true);
340
341
342 -- Procedure
343 -- load_prepmode_checkfile_info
344 --
345 -- Purpose
346 -- Imports file information from ad_check_file_temp to
347 -- ad_premode_check_files table, when applying a patch in "prepare" mode.
348 --
349 -- Arguments
350 -- none
351 --
352 procedure load_prepmode_checkfile_info;
353
354
355 --
356 -- Procedure
357 -- cleanup_prepmode_checkfile_info
358 --
359 -- Purpose
360 -- deletes rows from ad_premode_check_files (called after the merge)
361 --
362 -- Arguments
363 -- none
364 --
365 procedure cln_prepmode_checkfile_info;
366 --
367 --
368 procedure load_snpst_file_server_info
369 (snp_id number);
370 --
371 --
372 end ad_file_util;
[Home] [Help]