[Home] [Help]
PACKAGE BODY: APPS.QP_LOADER_PVT
Source
1 package body qp_loader_pvt as
2 /* $Header: QPXVLDQB.pls 120.1 2005/06/08 17:09:28 appldev $ */
3
4 -- ==================================================
5 -- Constants and Types.
6 -- ==================================================
7 g_date_mask VARCHAR2(100) := 'YYYY/MM/DD HH24:MI:SS';
8 g_default_lud DATE;
9
10
11 -- ==================================================
12 -- Functions and Procedures.
13 -- ==================================================
14
15
16 function UPLOAD_TEST(
17 p_file_id in number,
18 p_file_lud in date,
19 p_db_id in number,
20 p_db_lud in date,
21 p_custom_mode in varchar2)
22 return boolean is
23 l_db_id number;
24 l_file_id number;
25 l_original_seed_data_window date;
26 retcode boolean;
27 begin
28 -- CUSTOM_MODE=FORCE trumps all.
29 if (p_custom_mode = 'FORCE') then
30 retcode := TRUE;
31 return retcode;
32 end if;
33
34 -- Handle cases where data was previously up/downloaded with
35 -- 'SEED'/1 owner instead of 'ORACLE'/2, but DOES have a version
36 -- date. These rows can be distinguished by the lud timestamp;
37 -- Rows without versions were uploaded with sysdate, rows with
38 -- versions were uploaded with a date (with time truncated) from
39 -- the file.
40
41 -- Check file row for SEED/version
42 l_file_id := p_file_id;
43 if ((l_file_id in (0,1)) and (p_file_lud = trunc(p_file_lud)) and
44 (p_file_lud < sysdate - .1)) then
45 l_file_id := 2;
46 end if;
47
48 -- Check db row for SEED/version.
49 -- NOTE: if db ludate < seed_data_window, then consider this to be
50 -- original seed data, never touched by FNDLOAD, even if it doesn't
51 -- have a timestamp.
52 l_db_id := p_db_id;
53 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
54 if ((l_db_id in (0,1)) and (p_db_lud = trunc(p_db_lud)) and
55 (p_db_lud > l_original_seed_data_window)) then
56 l_db_id := 2;
57 end if;
58
59 if (l_file_id in (0,1)) then
60 -- File owner is old FNDLOAD.
61 if (l_db_id in (0,1)) then
62 -- DB owner is also old FNDLOAD.
63 -- Over-write, but only if file ludate >= db ludate.
64 if (p_file_lud >= p_db_lud) then
65 retcode := TRUE;
66 else
67 retcode := FALSE;
68 end if;
69 else
70 retcode := FALSE;
71 end if;
72 elsif (l_file_id = 2) then
73 -- File owner is new FNDLOAD. Over-write if:
74 -- 1. Db owner is old FNDLOAD, or
75 -- 2. Db owner is new FNDLOAD, and file date >= db date
76 if ((l_db_id in (0,1)) or
77 ((l_db_id = 2) and (p_file_lud >= p_db_lud))) then
78 retcode := TRUE;
79 else
80 retcode := FALSE;
81 end if;
82 else
83 -- File owner is USER. Over-write if:
84 -- 1. Db owner is old or new FNDLOAD, or
85 -- 2. File date >= db date
86 if ((l_db_id in (0,1,2)) or
87 (p_file_lud >= p_db_lud)) then
88 retcode := TRUE;
89 else
90 retcode := FALSE;
91 end if;
92 end if;
93
94 if (retcode = FALSE) then
95 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED');
96 end if;
97 return retcode;
98 end UPLOAD_TEST;
99
100 FUNCTION is_upload_allowed(p_custom_mode IN VARCHAR2,
101 p_file_owner IN VARCHAR2,
102 p_file_last_update_date IN VARCHAR2,
103 p_db_last_updated_by IN NUMBER,
104 p_db_last_update_date IN DATE,
105 x_file_who IN OUT nocopy who_type)
106 RETURN BOOLEAN
107 IS
108 l_db_who who_type;
109 l_file_owner VARCHAR2(100);
110 l_db_owner VARCHAR2(100);
111 l_return BOOLEAN;
112 BEGIN
113 --
114 -- Set File (Source) WHO.
115 --
116 BEGIN
117 l_file_owner := p_file_owner;
118 x_file_who.last_updated_by := fnd_load_util.owner_id(l_file_owner);
119 --
120 -- Remove the time component from file LUD. We used to use Sysdate for
121 -- NULL case, but it is better to use a fixed date.
122 --
123 x_file_who.last_update_date := Trunc(Nvl(To_date(p_file_last_update_date,
124 g_date_mask),
125 g_default_lud));
126 x_file_who.last_update_login := 0;
127 x_file_who.created_by := x_file_who.last_updated_by;
128 x_file_who.creation_date := x_file_who.last_update_date;
129 EXCEPTION
130 WHEN OTHERS THEN
131 l_file_owner := 'SEED'; -- 1
132 x_file_who.last_updated_by := fnd_load_util.owner_id(l_file_owner);
133 x_file_who.last_update_date := Trunc(g_default_lud);
134 x_file_who.last_update_login := 0;
135 x_file_who.created_by := x_file_who.last_updated_by;
136 x_file_who.creation_date := x_file_who.last_update_date;
137 END;
138
139 --
140 -- Set DB (Destination) WHO
141 --
142 l_db_who.last_updated_by := Nvl(p_db_last_updated_by,
143 x_file_who.last_updated_by);
144 l_db_owner := fnd_load_util.owner_name(l_db_who.last_updated_by);
145 l_db_who.last_update_date := Nvl(p_db_last_update_date,
146 x_file_who.last_update_date - 1);
147 l_db_who.last_update_login := 0;
148 l_db_who.created_by := l_db_who.last_updated_by;
149 l_db_who.creation_date := l_db_who.last_update_date;
150
151 --
152 -- Check if UPLOAD is allowed. i.e. no customizations.
153 --
154 -- Return TRUE if
155 -- - custom_mode = 'FORCE'.
156 -- - db (destination) is owned by SEED but file (source)is not owned by SEED.
157 -- - owners are same but destination is older.
158 --
159 -- IF ((p_custom_mode = 'FORCE') OR
160 -- ((l_db_who.last_updated_by = 1) AND
161 -- (x_file_who.last_updated_by <> 1)) OR
162 -- ((l_db_who.last_updated_by = x_file_who.last_updated_by) AND
163 -- (l_db_who.last_update_date <= x_file_who.last_update_date)))
164
165 l_return := fnd_load_util.upload_test
166 (p_file_id => x_file_who.last_updated_by,
167 p_file_lud => x_file_who.last_update_date,
168 p_db_id => l_db_who.last_updated_by,
169 p_db_lud => l_db_who.last_update_date,
170 p_custom_mode => p_custom_mode);
171
172 IF (l_return IS NULL) THEN
173 l_return := FALSE;
174 END IF;
175
176 RETURN(l_return);
177 EXCEPTION
178 WHEN OTHERS THEN
179 RETURN(FALSE);
180 END is_upload_allowed;
181
182 Procedure qp_pte_source_sys_load_row (
183 x_pte_code in varchar2,
184 x_application_short_name in varchar2,
185 x_enabled_flag in varchar2,
186 x_custom_mode in varchar2,
187 x_last_update_date in varchar2,
188 x_owner in varchar2
189 )
190 is
191 l_user_id number := 0;
192 l_pte_source_system_id number;
193 l_db_last_updated_by NUMBER;
194 l_db_last_update_date DATE;
195 l_db_who who_type;
196
197 begin
198
199 if (x_owner = 'SEED') then
200 l_user_id := 1;
201 end if;
202
203 begin
204 select last_updated_by, last_update_date into l_db_last_updated_by, l_db_last_update_date
205 from qp_pte_source_systems
206 where pte_code = x_pte_code
207 and application_short_name = x_application_short_name
208 and rownum = 1;
209
210 IF (NOT is_upload_allowed
211 (p_custom_mode => x_custom_mode,
212 p_file_owner => x_owner,
213 p_file_last_update_date => x_last_update_date,
214 p_db_last_updated_by => l_db_last_updated_by,
215 p_db_last_update_date => l_db_last_update_date,
216 x_file_who => l_db_who))
217 THEN
218 null;
219 else
220 update qp_pte_source_systems
221 set enabled_flag = x_enabled_flag,
222 last_updated_by = l_user_id,
223 last_update_date =to_date(x_last_update_date,'YYYY/MM/DD'),
224 last_update_login = 0
225 where pte_code = x_pte_code
226 and application_short_name = x_application_short_name;
227
228 END IF;
229
230 exception
231 when no_data_found then
232 begin
233
234 select qp_pte_source_system_s.nextval into l_pte_source_system_id from dual;
235
236 insert into qp_pte_source_systems
237 (pte_source_system_id,
238 pte_code,
239 application_short_name,
240 enabled_flag,
241 creation_date,
242 created_by,
243 last_update_date,
244 last_update_login,
245 last_updated_by)
246 values
247 (l_pte_source_system_id,
248 x_pte_code,
249 x_application_short_name,
250 x_enabled_flag,
251 sysdate,
252 l_user_id,
253 to_date(x_last_update_date,'YYYY/MM/DD'),
254 0,
255 l_user_id);
256 end;
257 end;
258 end qp_pte_source_sys_load_row;
259
260 Procedure qp_pte_ss_fn_area_load_row (
261 x_pte_code in varchar2,
262 x_application_short_name in varchar2,
263 x_functional_area_id in varchar2,
264 x_enabled_flag in varchar2,
265 x_seeded_flag in varchar2,
266 x_custom_mode in varchar2,
267 x_last_update_date in varchar2,
268 x_owner in varchar2
269 )
270 is
271 l_user_id number := 0;
272 l_pte_source_system_id number;
273 l_pte_sourcesystem_fnarea_id number;
274 l_db_last_updated_by NUMBER;
275 l_db_last_update_date DATE;
276 l_db_who who_type;
277
278 begin
279
280 if (x_owner = 'SEED') then
281 l_user_id := 1;
282 end if;
283
284 begin
285 select pte_source_system_id into l_pte_source_system_id
286 from qp_pte_source_systems
287 where pte_code = x_pte_code
288 and application_short_name = x_application_short_name
289 and rownum = 1;
290 exception
291 when others then
292 null;
293 end;
294
295 if l_pte_source_system_id is not null then
296 begin
297 select pte_sourcesystem_fnarea_id,last_updated_by, last_update_date
298 into l_pte_sourcesystem_fnarea_id, l_db_last_updated_by, l_db_last_update_date
299 from QP_SOURCESYSTEM_FNAREA_MAP
300 where pte_source_system_id = l_pte_source_system_id
301 and functional_area_id = x_functional_area_id
302 and rownum = 1;
303
304 IF (NOT is_upload_allowed
305 (p_custom_mode => x_custom_mode,
306 p_file_owner => x_owner,
307 p_file_last_update_date => x_last_update_date,
308 p_db_last_updated_by => l_db_last_updated_by,
309 p_db_last_update_date => l_db_last_update_date,
310 x_file_who => l_db_who))
311 THEN
312 null;
313 else
314 update QP_SOURCESYSTEM_FNAREA_MAP
315 set enabled_flag = x_enabled_flag,
316 seeded_flag = x_seeded_flag,
317 last_updated_by = l_user_id,
318 last_update_date =to_date(x_last_update_date,'YYYY/MM/DD'),
319 last_update_login = 0
320 where pte_sourcesystem_fnarea_id = l_pte_sourcesystem_fnarea_id;
321
322 END IF;
323
324 exception
325 when no_data_found then
326 begin
327
328 select qp_pte_ss_fnarea_id_s.nextval into l_pte_sourcesystem_fnarea_id from dual;
329
330 insert into QP_SOURCESYSTEM_FNAREA_MAP ( pte_sourcesystem_fnarea_id,
331 pte_source_system_id,
332 functional_area_id,
333 enabled_flag,
334 seeded_flag,
335 creation_date,
336 created_by,
337 last_update_date,
338 last_update_login,
339 last_updated_by)
340 values (l_pte_sourcesystem_fnarea_id,
341 l_pte_source_system_id,
342 x_functional_area_id,
343 x_enabled_flag,
344 x_seeded_flag,
345 sysdate,
346 l_user_id,
347 to_date(x_last_update_date,'YYYY/MM/DD'),
348 0,
349 l_user_id);
350
351 end;
352 end;
353 end if;
354 end qp_pte_ss_fn_area_load_row;
355
356 procedure qp_pte_req_types_translate_row (
357 x_pte_code in varchar2,
358 x_request_type_code in varchar2,
359 x_order_level_global_struct in varchar2,
360 x_line_level_global_struct in varchar2,
361 x_order_level_view_name in varchar2,
362 x_line_level_view_name in varchar2,
363 x_enabled_flag in varchar2,
364 x_request_type_desc in varchar2,
365 x_custom_mode in varchar2,
366 x_last_update_date in varchar2,
367 x_owner in varchar2
368 )
369 is
370 begin
371
372 update qp_pte_request_types_tl set
373 request_type_desc = nvl(x_request_type_desc, request_type_desc),
374 last_update_date = to_date(x_last_update_date,'YYYY/MM/DD'),
375 last_updated_by = decode(x_owner, 'SEED', 1, 0),
376 last_update_login = 0,
377 source_lang = userenv('LANG')
378 where request_type_code = x_request_type_code
379 and userenv('LANG') in (language, source_lang);
380
381 end qp_pte_req_types_translate_row;
382
383 procedure qp_pte_req_types_load_row (
384 x_pte_code in varchar2,
385 x_request_type_code in varchar2,
386 x_order_level_global_struct in varchar2,
387 x_line_level_global_struct in varchar2,
388 x_order_level_view_name in varchar2,
389 x_line_level_view_name in varchar2,
390 x_enabled_flag in varchar2,
391 x_request_type_desc in varchar2,
392 x_custom_mode in varchar2,
393 x_last_update_date in varchar2,
394 x_owner in varchar2
395 )
396 is
397 l_user_id number := 0;
398
399 begin
400
401 if (x_owner = 'SEED') then
402 l_user_id := 1;
403 end if;
404
405 update qp_pte_request_types_b set
406 order_level_global_struct = x_order_level_global_struct,
407 line_level_global_struct = x_line_level_global_struct,
408 order_level_view_name = x_order_level_view_name,
409 line_level_view_name = x_line_level_view_name,
410 enabled_flag = x_enabled_flag,
411 last_updated_by = l_user_id,
412 last_update_date = to_date(x_last_update_date ,'YYYY/MM/DD'),
413 last_update_login = 0
414 where request_type_code = x_request_type_code
415 and pte_code = x_pte_code;
416
417 if sql%notfound then
418 insert into qp_pte_request_types_b (
419 request_type_code,
420 pte_code,
421 order_level_global_struct,
422 line_level_global_struct,
423 order_level_view_name,
424 line_level_view_name,
425 enabled_flag,
426 creation_date,
427 created_by,
428 last_update_date,
429 last_update_login,
430 last_updated_by
431 ) values (
432 x_request_type_code,
433 x_pte_code,
434 x_order_level_global_struct,
435 x_line_level_global_struct,
436 x_order_level_view_name,
437 x_line_level_view_name,
438 x_enabled_flag,
439 sysdate,
440 l_user_id,
441 to_date(x_last_update_date,'YYYY/MM/DD'),
442 0,
443 l_user_id
444 );
445
446 insert into qp_pte_request_types_tl (
447 request_type_code,
448 request_type_desc,
449 source_lang,
450 language,
451 creation_date,
452 created_by,
453 last_update_date,
454 last_update_login,
455 last_updated_by
456 ) select
457 x_request_type_code,
458 x_request_type_desc,
459 userenv('LANG'),
460 l.language_code,
461 sysdate,
462 l_user_id,
463 to_date(x_last_update_date,'YYYY/MM/DD'),
464 0,
465 l_user_id
466 from fnd_languages l
467 where l.installed_flag in ('I', 'B')
468 and not exists
469 (select null
470 from qp_pte_request_types_tl t,
471 qp_pte_request_types_b b
472 where t.request_type_code = x_request_type_code
473 and t.language = l.language_code);
474
475 else
476 update qp_pte_request_types_tl set
477 request_type_desc = nvl(x_request_type_desc, request_type_desc),
478 last_update_date=to_date(x_last_update_date,'YYYY/MM/DD'),
479 last_updated_by = l_user_id,
480 last_update_login = 0,
481 source_lang = userenv('LANG')
482 where request_type_code = x_request_type_code
483 and userenv('LANG') in (language, source_lang);
484 end if;
485
486 end qp_pte_req_types_load_row;
487
488 end qp_loader_pvt;