1 package body FND_LOAD_UTIL as
2 /* $Header: AFLDUTLB.pls 120.10.12010000.1 2008/07/25 14:15:56 appldev ship $ */
3
4
5 --
6 -- OWNER_NAME
7 -- Return owner tag to be used in FNDLOAD data file
8 -- IN
9 -- p_id - user_id of last_updated_by column
10 -- RETURNS
11 -- OWNER attribute value for FNDLOAD data file
12 --
13 function OWNER_NAME(
14 p_id in number)
15 return varchar2 is
16 l_owner_name varchar2(100);
17 l_release_name varchar2(50);
18 begin
19 -- Look for profile over-ride for internal seed env
20 l_owner_name := fnd_profile.value('FNDLOAD_OWNER');
21 if (l_owner_name is not null) then
22 return l_owner_name;
23 end if;
24
25 if (p_id in (0, 1, 2) or ((p_id >=120) and (p_id<=129))) then
26 begin
27 -- Seed data, old or new
28 -- get release name of source seed database
29 select RELEASE_NAME
30 into l_release_name
31 from FND_PRODUCT_GROUPS;
32
33 if ( l_release_name like '12.%.%') then
34 -- embody OWNER with RELEASE_NAME info only for R12 release
35 l_owner_name := 'ORACLE' || l_release_name;
36 else
37 -- old fashion to back support 11i development
38 l_owner_name := 'ORACLE';
39 end if;
40
41 exception
42 when no_data_found then
43 -- if FND_PRODUCT_GROUPS is not seeded, back to 11i old fashion
44 l_owner_name := 'ORACLE';
45 end;
46 else
47 begin
48 -- User customized data
49 select user_name
50 into l_owner_name
51 from fnd_user
52 where user_id = p_id;
53
54 exception
55 when no_data_found then
56 l_owner_name := 'USER';
57 end;
58 end if;
59
60 return l_owner_name;
61 end OWNER_NAME;
62
63 --
64 -- OWNER_ID
65 -- Return the user_id of the OWNER attribute
66 -- IN
67 -- p_name - OWNER attribute value from FNDLOAD data file
68 -- RETURNS
69 -- user_id of owner to use in who columns
70 --
71 function OWNER_ID(
72 p_name in varchar2)
73 return number is
74 l_user_id number;
75 begin
76 if (p_name in ('SEED','CUSTOM')) then
77 -- Old loader seed data
78 return 1;
79 elsif (p_name = 'ORACLE') then
80 -- New loader seed data
81 return 2;
82 elsif (p_name like 'ORACLE12._.%') then
83 return 120+to_number(substr(p_name,10,1));
84 else
85 begin
86 -- User customized data
87 select user_id
88 into l_user_id
89 from fnd_user
90 where p_name = user_name;
91 return l_user_id;
92 exception
93 when no_data_found then
94 return -1;
95 end;
96 end if;
97 end OWNER_ID;
98
99 --
100 -- UPLOAD_TEST
101 -- Test whether or not to over-write database row when uploading
102 -- data from FNDLOAD data file, based on owner attributes of both
103 -- database row and row in file being uploaded.
104 -- IN
105 -- p_file_id - FND_LOAD_UTIL.OWNER_ID(<OWNER attribute from data file>)
106 -- p_file_lud - LAST_UPDATE_DATE attribute from data file
107 -- p_db_id - LAST_UPDATED_BY of db row
108 -- p_db_lud - LAST_UPDATE_DATE of db row
109 -- p_custom_mode - CUSTOM_MODE FNDLOAD parameter value
110 -- RETURNS
111 -- TRUE if safe to over-write.
112 --
113 function UPLOAD_TEST(
114 p_file_id in number,
115 p_file_lud in date,
116 p_db_id in number,
117 p_db_lud in date,
118 p_custom_mode in varchar2)
119 return boolean is
120 l_db_id number;
121 l_file_id number;
122 l_original_seed_data_window date;
123 retcode boolean;
124 begin
125 -- CUSTOM_MODE=FORCE trumps all.
126 if (p_custom_mode = 'FORCE') then
127 retcode := TRUE;
128 return retcode;
129 end if;
130
131 -- Handle cases where data was previously up/downloaded with
132 -- 'SEED'/1 owner instead of 'ORACLE'/2, but DOES have a version
133 -- date. These rows can be distinguished by the lud timestamp;
134 -- Rows without versions were uploaded with sysdate, rows with
135 -- versions were uploaded with a date (with time truncated) from
136 -- the file.
137
138 -- Check file row for SEED/version
139 l_file_id := p_file_id;
140 if ((l_file_id in (0,1)) and (p_file_lud = trunc(p_file_lud)) and
141 (p_file_lud < sysdate - .1)) then
142 l_file_id := 2;
143 end if;
144
145 -- Check db row for SEED/version.
146 -- NOTE: if db ludate < seed_data_window, then consider this to be
147 -- original seed data, never touched by FNDLOAD, even if it doesn't
148 -- have a timestamp.
149 l_db_id := p_db_id;
150 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
151 if ((l_db_id in (0,1)) and (p_db_lud = trunc(p_db_lud)) and
152 (p_db_lud > l_original_seed_data_window)) then
153 l_db_id := 2;
154 end if;
155
156
157 if (NLS_MODE) then
158 if (l_file_id in (0,1)) then
159 -- File owner is old FNDLOAD.
160 if (l_db_id in (0,1)) then
161 -- DB owner is also old FNDLOAD.
162 -- Over-write, but only if file ludate >= db ludate.
163 if (p_file_lud >= p_db_lud) then
164 retcode := TRUE;
165 else
166 retcode := FALSE;
167 end if;
168 else
169 retcode := FALSE;
170 end if;
171 elsif (l_file_id = 2) then
172 -- File owner is new FNDLOAD. Over-write if:
173 -- 1. Db owner is old FNDLOAD, or
174 -- 2. Db owner is new FNDLOAD, and file date >= db date
175 if ((l_db_id in (0,1)) or
176 ((l_db_id = 2) and (p_file_lud >= p_db_lud))) then
177 retcode := TRUE;
178 else
179 retcode := FALSE;
180 end if;
181 elsif ((l_file_id >= 120) and (l_file_id<=129)) then
182 -- File owner is R12 seed data, Over-write if:
183 -- 1. Db owner is (0, 1, 2,120..129) and l_db_id<l_file_id, or
184 -- 2. Db owner is file owner, and file date >= db date
185 if (
186 ((l_db_id in (0,1,2) or ((l_db_id>=120) and (l_db_id<=129))) and (l_db_id<l_file_id)) or
187 ((l_db_id = l_file_id) and (p_file_lud >= p_db_lud))
188 ) then
189 retcode := TRUE;
190 else
191 retcode := FALSE;
192 end if;
193
194
195 else
196 -- File owner is USER. Over-write if:
197 -- 1. Db owner is old or new FNDLOAD, or
198 -- 2. File date >= db date
199 if ((l_db_id in (0,1,2,120,121,122,123,124,125,126,127,128,129)) or
200 (trunc(p_file_lud) >= trunc(p_db_lud))) then
201 retcode := TRUE;
202 else
203 retcode := FALSE;
204 end if;
205 end if;
206 else
207 if (l_file_id in (0,1)) then
208 -- File owner is old FNDLOAD.
209 if (l_db_id in (0,1)) then
210 -- DB owner is also old FNDLOAD.
211 -- Over-write, but only if file ludate > db ludate.
212 if (p_file_lud > p_db_lud) then
213 retcode := TRUE;
214 else
215 retcode := FALSE;
216 end if;
217 else
218 retcode := FALSE;
219 end if;
220 elsif (l_file_id = 2) then
221 -- File owner is new FNDLOAD. Over-write if:
222 -- 1. Db owner is old FNDLOAD, or
223 -- 2. Db owner is new FNDLOAD, and file date > db date
224 if ((l_db_id in (0,1)) or
225 ((l_db_id = 2) and (p_file_lud > p_db_lud))) then
226 retcode := TRUE;
227 else
228 retcode := FALSE;
229 end if;
230 elsif ((l_file_id >= 120) and (l_file_id <= 129)) then
231 -- File owner is R12 seed data, Over-write if:
232 -- 1. Db owner is (0,1), or
233 -- 2. Db owner is 2 and p_file_lud != p_db_lud, or
234 -- 2. Db owner is 120 and file date > db date
235 if ((l_db_id in (0,1)) or
236 ((l_db_id in ( 2,120,121,122,123,124,125,126,127,128,129) and (l_db_id < l_file_id)) and (p_file_lud <> p_db_lud)) or
237 ((l_db_id = l_file_id) and (p_file_lud > p_db_lud))) then
238 retcode := TRUE;
239 else
240 retcode := FALSE;
241 end if;
242
243
244 else
245 -- File owner is USER. Over-write if:
246 -- 1. Db owner is old or new FNDLOAD, or
247 -- 2. File date > db date
248 if ((l_db_id in (0,1,2,120,121,122,123,124,125,126,127,128,129)) or
249 (trunc(p_file_lud) > trunc(p_db_lud))) then
250 retcode := TRUE;
251 else
252 retcode := FALSE;
253 end if;
254 end if;
255
256 end if;
257
258
259 if (NLS_MODE) then
260 if (retcode = FALSE) then
261 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED');
262 end if;
263 else
264 if (retcode = FALSE) then
265 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED_US');
266 end if;
267 end if;
268
269 return retcode;
270 end UPLOAD_TEST;
271
272 -- Bug 2438503 Routine to return NULL value.
273
274 function NULL_VALUE
275 return varchar2 is
276 begin
277 return '*NULL*';
278 end NULL_VALUE;
279
280 procedure SET_NLS_MODE is
281 begin
282 NLS_MODE:=TRUE;
283 end SET_NLS_MODE;
284
285 end FND_LOAD_UTIL;
[Home] [Help]