1 package ad_file_util as
2 /* $Header: adfiluts.pls 120.1.12000000.1 2007/01/16 19:31:29 appldev 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 -- none
153 --
154 procedure load_checkfile_info;
155
156 --
157 -- Procedure
158 -- update_timestamp
159 --
160 -- Purpose
161 -- Inserts/updates a row in AD_TIMESTAMPS corresponding to the
162 -- specified row type and attribute.
163 --
164 -- Arguments
165 -- in_type The row type
166 -- in_attribute The row attribute
167 -- in_timestamp A timestamp. Defaults to sysdate.
168 --
169 -- Notes
170 -- This is essentially the same as ad_invoker.update_timestamp
171 -- Added it here to make it easier to call from APPS.
172 --
173 procedure update_timestamp
174 (in_type in varchar2,
175 in_attribute in varchar2,
176 in_timestamp in date);
177
178 procedure update_timestamp
179 (in_type in varchar2,
180 in_attribute in varchar2);
181 --
182 --
183 --
184 -- Procedure
185 -- load_patch_onsite_vers_info
186 --
187 -- Purpose
188 -- Imports file information from ad_check_file_temp to ad_files and
189 -- ad_file_versions.
190 --
191 -- Only creates rows that don't already exist.
192 --
193 -- Processes all rows in ad_check_file_temp with active_flag='Y'.
194 --
195 -- To handle batch sizes:
196 --
197 -- 1) - fill up whole table with null active_flag
198 -- - In a loop:
199 -- - update a batch to have active_flag='Y'
200 -- - process the batch
201 -- - delete the batch
202 -- - using 'where rownum < batch+1' is handy here
203 --
204 -- 2) perform (truncate, load, process) cycles in an outer loop where
205 -- only <batch size> rows are loaded and processed at a time.
206 --
207 -- Calls load_file_info
208 --
209 -- Updates the file_version_id and file_version_id_2 columns of
210 -- ad_check_file_temp so that all rows point to the file_version_id
211 -- of the file versions referenced in the row.
212 --
213 -- Doesn't try to update ad_file_versions for rows in ad_check_file_temp
214 -- with manifest_vers='NA' or manifest_vers_2='NA'. These values mean
215 -- "no version for this file", so no corresponding record should be
216 -- created in ad_file_versions.
217 --
218 -- Arguments
219 -- none
220 --
221 procedure load_patch_onsite_vers_info;
222 --
223 --
224 --
225 -- Procedure
226 -- load_snapshot_file_info
227 --
228 -- Purpose
229 -- Create Snapshot data by
230 -- 1.Calls ad_file_versions and loads the file versions
231 -- into the ad_check_file_temp table .
232 -- 2.Updates rows in AD_SNAPSHOT_FILES from ad_check_file_temp
233 -- which have the same file_id, snapshot_id and containing_file_id
234 -- 3.Inserts those rows from ad_check_file_temp into AD_SNAPSHOT_FILES
235 -- which exists in ad_check_file_temp but are not in AD_SNAPSHOT_FILES.
236 -- for the given snapshot_id
237 -- 4.Delete those rows from AD_SNAPSHOT_FILES which exists
238 -- in AD_SNAPSHOT_FILES but do not exist in ad_check_file_temp
239 -- for the given snapshot_id
240 --
241 -- Arguments
242 -- is_upload pass TRUE if it is an upload otherwise FALSE
243 --
244 --
245 procedure load_snapshot_file_info
246 (snp_id number,
247 preserve_irep_flag number);
248 --
249 --
250 --
251 -- Procedure
252 -- load_preseeded_bugfixes
253 --
254 -- Purpose
255 -- Gets the bug_id from AD_BUGS for the bugnumbers in
256 -- in ad_check_file_temp table .
257 -- Creates new rows in the AD_BUGS for the new bugnumbers
258 -- and gets the bug_id for those bugnumbers and stores them
259 -- ad_check_file_temp table .
260 --
261 -- Inserts those BUG_IDs into AD_SNAPSHOT_BUGFIXES
262 --
263 --
264 -- Arguments
265 -- None
266 --
267 procedure load_preseeded_bugfixes;
268 --
269 --
270 procedure load_patch_hist_action
271 (bugs_processed out NOCOPY number,
272 actions_processed out NOCOPY number);
273
274 -- Procedure
275 -- create_global_view
276 -- Arguments
277 -- p_apps_system_name - Applications system name
278 -- Purpose
279 -- Procedure to create Global View snapshot using exisiting
280 -- current view snapshots for an applications system.
281 -- Notes
282
283 procedure create_global_view(p_apps_system_name varchar2);
284 --
285 --
286 -- Procedure
287 -- populate_snapshot_files_temp
288 -- Arguments
289 -- p_apps_system_name - Applications System Name
290 --
291 -- p_min_file_id - lower limit file_id in the range of file_ids
292 --
293 -- p_max_file_id - upper limit file_id in the range of file_ids
294 --
295 -- p_global_snapshot_id - Global snapshot_id
296 --
297 -- p_un_fnd - applsys username
298 --
299 -- p_iteration - which iteration (1,2,etc)
300 -- Purpose
301 -- This procedure populates temp table with a range of file_ids
302 -- processes the data and updates the ad_snapshot_files with negative
303 -- global snapshot_id
304 -- Notes
305 --
306
307 procedure populate_snapshot_files_temp(p_applications_sys_name varchar2,p_min_file_id number,
308 p_max_file_id number,p_global_snapshot_id number,
309 p_un_fnd varchar2,p_iteration number);
310
311 --
312 --
313 -- Procedure
314 -- populate_snapshot_bugs_temp
315 -- Arguments
316 -- p_apps_system_name - Applications System Name
317 --
318 -- p_min_bug_id - lower limit bugfix_id in the range of bugfix_id
319 --
320 -- p_max_bug_id - upper limit bugfix_id in the range of bugfix_id
321 --
322 -- p_global_snapshot_id - Global snapshot_id
323 --
324 -- p_un_fnd - applsys username
325 --
326 -- p_iteration - which iteration (1,2,etc)
327 -- Purpose
328 -- This procedure populates temp table with a range of file_ids
329 -- processes the data and updates the ad_snapshot_bugfixes with negative
330 -- global snapshot_id
331 -- Notes
332 --
333 procedure populate_snapshot_bugs_temp(p_applications_sys_name varchar2,p_min_bug_id number,
334 p_max_bug_id number,p_global_snapshot_id number,
335 p_un_fnd varchar2,p_iteration number);
336
337
338 -- Procedure
339 -- load_prepmode_checkfile_info
340 --
341 -- Purpose
342 -- Imports file information from ad_check_file_temp to
343 -- ad_premode_check_files table, when applying a patch in "prepare" mode.
344 --
345 -- Arguments
346 -- none
347 --
348 procedure load_prepmode_checkfile_info;
349
350
351 --
352 -- Procedure
353 -- cleanup_prepmode_checkfile_info
354 --
355 -- Purpose
356 -- deletes rows from ad_premode_check_files (called after the merge)
357 --
358 -- Arguments
359 -- none
360 --
361 procedure cln_prepmode_checkfile_info;
362 --
363 --
364 procedure load_snpst_file_server_info
365 (snp_id number);
366 --
367 --
368 end ad_file_util;
[Home] [Help]