1 package body FND_OBJECT_TABLESPACES_PKG as
2 /* $Header: fndtobjb.pls 115.10 2004/04/22 22:14:20 sakhtar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'FND_OBJECT_TABLESPACES_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'fndtobjb.pls';
6
7 function OWNER_ID(
8 p_name in varchar2)
9 return number is
10 l_user_id number;
11 begin
12 if (p_name in ('SEED','CUSTOM')) then
13 -- Old loader seed data
14 return 1;
15 elsif (p_name = 'ORACLE') then
16 -- New loader seed data
17 return 2;
18 else
19 begin
20 -- User customized data
21 select user_id
22 into l_user_id
23 from fnd_user
24 where p_name = user_name;
25 return l_user_id;
26 exception
27 when no_data_found then
28 return -1;
29 end;
30 end if;
31 end OWNER_ID;
32
33 function UPLOAD_TEST(
34 p_file_id in number,
35 p_file_lud in date,
36 p_db_id in number,
37 p_db_lud in date,
38 p_custom_mode in varchar2)
39 return boolean is
40 l_db_id number;
41 l_file_id number;
42 l_original_seed_data_window date;
43 retcode boolean;
44 begin
45 -- CUSTOM_MODE=FORCE trumps all.
46 if (p_custom_mode = 'FORCE') then
47 retcode := TRUE;
48 return retcode;
49 end if;
50
51 -- Handle cases where data was previously up/downloaded with
52 -- 'SEED'/1 owner instead of 'ORACLE'/2, but DOES have a version
53 -- date. These rows can be distinguished by the lud timestamp;
54 -- Rows without versions were uploaded with sysdate, rows with
55 -- versions were uploaded with a date (with time truncated) from
56 -- the file.
57
58 -- Check file row for SEED/version
59 l_file_id := p_file_id;
60 if ((l_file_id in (0,1)) and (p_file_lud = trunc(p_file_lud)) and
61 (p_file_lud < sysdate - .1)) then
62 l_file_id := 2;
63 end if;
64
65 -- Check db row for SEED/version.
66 -- NOTE: if db ludate < seed_data_window, then consider this to be
67 -- original seed data, never touched by FNDLOAD, even if it doesn't
68 -- have a timestamp.
69 l_db_id := p_db_id;
70 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
71 if ((l_db_id in (0,1)) and (p_db_lud = trunc(p_db_lud)) and
72 (p_db_lud > l_original_seed_data_window)) then
73 l_db_id := 2;
74 end if;
75
76 if (l_file_id in (0,1)) then
77 -- File owner is old FNDLOAD.
78 if (l_db_id in (0,1)) then
79 -- DB owner is also old FNDLOAD.
80 -- Over-write, but only if file ludate >= db ludate.
81 if (p_file_lud >= p_db_lud) then
82 retcode := TRUE;
83 else
84 retcode := FALSE;
85 end if;
86 else
87 retcode := FALSE;
88 end if;
89 elsif (l_file_id = 2) then
90 -- File owner is new FNDLOAD. Over-write if:
91 -- 1. Db owner is old FNDLOAD, or
92 -- 2. Db owner is new FNDLOAD, and file date >= db date
93 if ((l_db_id in (0,1)) or
94 ((l_db_id = 2) and (p_file_lud >= p_db_lud))) then
95 retcode := TRUE;
96 else
97 retcode := FALSE;
98 end if;
99 else
100 -- File owner is USER. Over-write if:
101 -- 1. Db owner is old or new FNDLOAD, or
102 -- 2. File date >= db date
103 if ((l_db_id in (0,1,2)) or
104 (p_file_lud >= p_db_lud)) then
105 retcode := TRUE;
106 else
107 retcode := FALSE;
108 end if;
109 end if;
110
111 return retcode;
112 end UPLOAD_TEST;
113
114
115 PROCEDURE INSERT_ROW (
116 X_ROWID IN OUT NOCOPY VARCHAR2 ,
117 P_APPLICATION_ID IN NUMBER,
118 P_OBJECT_NAME IN VARCHAR2,
119 P_OBJECT_TYPE IN VARCHAR2,
120 P_TABLESPACE_TYPE IN VARCHAR2,
121 P_CUSTOM_TABLESPACE_TYPE IN VARCHAR2 DEFAULT NULL,
122 P_OBJECT_SOURCE IN VARCHAR2 DEFAULT NULL,
123 P_ORACLE_USERNAME IN VARCHAR2 DEFAULT NULL,
124 P_CUSTOM_FLAG IN VARCHAR2 DEFAULT NULL,
125 P_CREATION_DATE IN DATE DEFAULT NULL,
126 P_CREATED_BY IN NUMBER DEFAULT NULL,
127 P_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
128 P_LAST_UPDATED_BY IN NUMBER DEFAULT NULL,
129 P_LAST_UPDATE_LOGIN IN NUMBER DEFAULT NULL
130 ) is
131 cursor C is
132 select ROWID from fnd_object_tablespaces
133 where application_id = p_application_id
134 and object_name = p_object_name;
135 CURSOR c1 IS
136 select fou.oracle_username
137 from fnd_oracle_userid fou,
138 fnd_product_installations fpi
139 where fou.oracle_id = fpi.oracle_id
140 and fpi.application_id = p_application_id;
141 l_schema FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
142 BEGIN
143 if p_oracle_username IS NULL then
144 OPEN c1;
145 FETCH c1 INTO l_schema;
146 CLOSE c1;
147 end if;
148
149 insert into FND_OBJECT_TABLESPACES (
150 APPLICATION_ID,
151 OBJECT_NAME,
152 OBJECT_TYPE,
153 TABLESPACE_TYPE,
154 CUSTOM_TABLESPACE_TYPE,
155 OBJECT_SOURCE ,
156 ORACLE_USERNAME,
157 CUSTOM_FLAG ,
158 CREATION_DATE,
159 CREATED_BY,
160 LAST_UPDATE_DATE,
161 LAST_UPDATED_BY,
162 LAST_UPDATE_LOGIN
163 ) values (
164 P_APPLICATION_ID,
165 P_OBJECT_NAME,
166 P_OBJECT_TYPE,
167 P_TABLESPACE_TYPE,
168 P_CUSTOM_TABLESPACE_TYPE,
169 P_OBJECT_SOURCE ,
170 NVL(P_ORACLE_USERNAME, l_schema),
171 P_CUSTOM_FLAG ,
172 NVL(P_CREATION_DATE, sysdate),
173 NVL(P_CREATED_BY, -1),
174 NVL(P_LAST_UPDATE_DATE, sysdate),
175 NVL(P_LAST_UPDATED_BY, -1),
176 NVL(P_LAST_UPDATE_LOGIN, -1)
177 );
178
179 open c;
180 fetch c into X_ROWID;
181 if (c%notfound) then
182 close c;
183 raise no_data_found;
184 end if;
185 close c;
186
187 END INSERT_ROW;
188
189
190 PROCEDURE UPDATE_ROW (
191 P_APPLICATION_ID in NUMBER,
192 P_OBJECT_NAME in VARCHAR2,
193 P_OBJECT_TYPE in VARCHAR2,
194 P_TABLESPACE_TYPE in VARCHAR2 DEFAULT NULL,
195 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2 DEFAULT NULL,
196 P_OBJECT_SOURCE in VARCHAR2 DEFAULT NULL,
197 P_ORACLE_USERNAME in VARCHAR2 DEFAULT NULL,
198 P_CUSTOM_FLAG in VARCHAR2 DEFAULT NULL,
199 P_LAST_UPDATE_DATE in DATE DEFAULT NULL,
200 P_LAST_UPDATED_BY in NUMBER DEFAULT NULL,
201 P_LAST_UPDATE_LOGIN in NUMBER DEFAULT NULL
202 ) is
203 BEGIN
204 update FND_OBJECT_TABLESPACES set
205 OBJECT_TYPE = decode(P_OBJECT_TYPE, NULL, OBJECT_TYPE, p_OBJECT_TYPE),
206 TABLESPACE_TYPE = decode(P_TABLESPACE_TYPE, NULL, TABLESPACE_TYPE, p_TABLESPACE_TYPE),
207 CUSTOM_TABLESPACE_TYPE = decode(P_CUSTOM_TABLESPACE_TYPE, NULL, CUSTOM_TABLESPACE_TYPE, p_CUSTOM_TABLESPACE_TYPE),
208 OBJECT_SOURCE = decode(P_OBJECT_SOURCE, NULL, OBJECT_SOURCE, p_OBJECT_SOURCE),
209 ORACLE_USERNAME = decode(P_ORACLE_USERNAME, NULL, ORACLE_USERNAME, p_ORACLE_USERNAME),
210 CUSTOM_FLAG = decode(P_CUSTOM_FLAG, NULL, CUSTOM_FLAG, p_CUSTOM_FLAG),
211 LAST_UPDATE_DATE = decode(P_LAST_UPDATE_DATE, NULL, sysdate, p_LAST_UPDATE_DATE),
212 LAST_UPDATED_BY = decode(P_LAST_UPDATED_BY, NULL, -1, p_LAST_UPDATED_BY),
213 LAST_UPDATE_LOGIN = decode(P_LAST_UPDATE_LOGIN, NULL, -1, p_LAST_UPDATE_LOGIN)
214 where APPLICATION_ID = P_APPLICATION_ID
215 and OBJECT_NAME = P_OBJECT_NAME;
216
217 if (sql%notfound) then
218 raise no_data_found;
219 end if;
220
221 END UPDATE_ROW;
222
223 /* Overloaded version below */
224 PROCEDURE LOAD_ROW (
225 P_APPLICATION_ID in number,
226 P_OBJECT_NAME in VARCHAR2,
227 P_OBJECT_TYPE in VARCHAR2,
228 P_TABLESPACE_TYPE in VARCHAR2,
229 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
230 P_OBJECT_SOURCE in VARCHAR2,
231 P_ORACLE_USERNAME in VARCHAR2,
232 P_CUSTOM_FLAG in VARCHAR2,
233 P_CUSTOM_MODE in VARCHAR2
234 ) is
235 BEGIN
236 FND_OBJECT_TABLESPACES_PKG.LOAD_ROW (
237 P_APPLICATION_ID => P_APPLICATION_ID ,
238 P_OBJECT_NAME => P_OBJECT_NAME,
239 P_OBJECT_TYPE => P_OBJECT_TYPE,
240 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
241 P_CUSTOM_TABLESPACE_TYPE => P_CUSTOM_TABLESPACE_TYPE,
242 P_OBJECT_SOURCE => P_OBJECT_SOURCE,
243 P_ORACLE_USERNAME => P_ORACLE_USERNAME,
244 P_CUSTOM_FLAG => P_CUSTOM_FLAG,
245 P_LAST_UPDATED_BY => null,
246 P_CUSTOM_MODE => P_CUSTOM_MODE,
247 P_LAST_UPDATE_DATE => null
248 );
249 END LOAD_ROW;
250
251 /* Overloaded version above */
252 PROCEDURE LOAD_ROW (
253 P_APPLICATION_ID in NUMBER,
254 P_OBJECT_NAME in VARCHAR2,
255 P_OBJECT_TYPE in VARCHAR2,
256 P_TABLESPACE_TYPE in VARCHAR2,
257 P_CUSTOM_TABLESPACE_TYPE in VARCHAR2,
258 P_OBJECT_SOURCE in VARCHAR2,
259 P_ORACLE_USERNAME in VARCHAR2,
260 P_CUSTOM_FLAG in VARCHAR2,
261 P_LAST_UPDATED_BY in VARCHAR2,
262 P_CUSTOM_MODE in VARCHAR2,
263 P_LAST_UPDATE_DATE in VARCHAR2
264 ) is
265 row_id VARCHAR2(4000);
266 f_luby number; -- entity owner in file
267 f_ludate date; -- entity update date in file
268 db_luby number; -- entity owner in db
269 db_ludate date; -- entity update date in db
270 BEGIN
271 -- Translate owner to file_last_updated_by
272 f_luby := owner_id(P_LAST_UPDATED_BY);
273
274 -- Translate char last_update_date to date
275 f_ludate := nvl(to_date(P_last_update_date, 'YYYY/MM/DD'), sysdate);
276
277 select last_updated_by, last_update_date
278 into db_luby, db_ludate
279 from FND_OBJECT_TABLESPACES
280 where application_id = p_application_id
281 and object_name = p_object_name;
282
283 if (upload_test(f_luby, f_ludate, db_luby,db_ludate,
284 p_custom_mode)) then
285 FND_OBJECT_TABLESPACES_PKG.UPDATE_ROW (
286 P_APPLICATION_ID => P_application_id,
287 P_OBJECT_NAME => P_OBJECT_NAME,
288 P_OBJECT_TYPE => P_OBJECT_TYPE,
289 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
290 P_CUSTOM_TABLESPACE_TYPE => P_CUSTOM_TABLESPACE_TYPE,
291 P_OBJECT_SOURCE => P_OBJECT_SOURCE ,
292 P_ORACLE_USERNAME => P_ORACLE_USERNAME ,
293 P_CUSTOM_FLAG => P_CUSTOM_FLAG,
294 P_LAST_UPDATE_DATE => f_ludate,
295 P_LAST_UPDATED_BY => f_luby,
296 P_LAST_UPDATE_LOGIN => -1 );
297 end if;
298 EXCEPTION
299 when NO_DATA_FOUND then
300 FND_OBJECT_TABLESPACES_PKG.INSERT_ROW(
301 X_ROWID => row_id,
302 P_APPLICATION_ID => P_APPLICATION_ID,
303 P_OBJECT_NAME => P_OBJECT_NAME,
304 P_OBJECT_TYPE => P_OBJECT_TYPE,
305 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
306 P_CUSTOM_TABLESPACE_TYPE => P_CUSTOM_TABLESPACE_TYPE,
307 P_OBJECT_SOURCE => P_OBJECT_SOURCE ,
308 P_ORACLE_USERNAME => P_ORACLE_USERNAME ,
309 P_CUSTOM_FLAG => P_CUSTOM_FLAG,
310 P_CREATION_DATE => f_ludate,
311 P_CREATED_BY => f_luby,
312 P_LAST_UPDATE_DATE => f_ludate,
313 P_LAST_UPDATED_BY => f_luby,
314 P_LAST_UPDATE_LOGIN => -1 );
315 END LOAD_ROW;
316
317 PROCEDURE DELETE_ROW (
318 P_APPLICATION_ID in NUMBER,
322 BEGIN
319 P_OBJECT_NAME in VARCHAR2,
320 P_OBJECT_TYPE in VARCHAR2
321 ) is
323 delete from FND_OBJECT_TABLESPACES
324 where APPLICATION_ID = P_APPLICATION_ID
325 and OBJECT_NAME = P_OBJECT_NAME;
326
327 if (sql%notfound) then
328 raise no_data_found;
329 end if;
330 END DELETE_ROW;
331
332 END FND_OBJECT_TABLESPACES_PKG;
[Home] [Help]