1 package body FND_LOAD_UTIL as
2 /* $Header: AFLDUTLB.pls 120.11 2011/06/20 22:20:12 smadhapp 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 -- CUSTOM_MODE=FORCE_SEED bug12675934 to overwrite only seed data.
132 if ((p_custom_mode='FORCE_SEED') and (p_db_id in (0,1,2,120,121,122,123,124,125,126,127,128,129))) then
133 return TRUE;
134 end if;
135
136 -- Handle cases where data was previously up/downloaded with
137 -- 'SEED'/1 owner instead of 'ORACLE'/2, but DOES have a version
138 -- date. These rows can be distinguished by the lud timestamp;
139 -- Rows without versions were uploaded with sysdate, rows with
140 -- versions were uploaded with a date (with time truncated) from
141 -- the file.
142
143 -- Check file row for SEED/version
144 l_file_id := p_file_id;
145 if ((l_file_id in (0,1)) and (p_file_lud = trunc(p_file_lud)) and
146 (p_file_lud < sysdate - .1)) then
147 l_file_id := 2;
148 end if;
149
150 -- Check db row for SEED/version.
151 -- NOTE: if db ludate < seed_data_window, then consider this to be
152 -- original seed data, never touched by FNDLOAD, even if it doesn't
153 -- have a timestamp.
154 l_db_id := p_db_id;
155 l_original_seed_data_window := to_date('01/01/1990','MM/DD/YYYY');
156 if ((l_db_id in (0,1)) and (p_db_lud = trunc(p_db_lud)) and
157 (p_db_lud > l_original_seed_data_window)) then
158 l_db_id := 2;
159 end if;
160
161
162 if (NLS_MODE) then
163 if (l_file_id in (0,1)) then
164 -- File owner is old FNDLOAD.
165 if (l_db_id in (0,1)) then
166 -- DB owner is also old FNDLOAD.
167 -- Over-write, but only if file ludate >= db ludate.
168 if (p_file_lud >= p_db_lud) then
169 retcode := TRUE;
170 else
171 retcode := FALSE;
172 end if;
173 else
174 retcode := FALSE;
175 end if;
176 elsif (l_file_id = 2) then
177 -- File owner is new FNDLOAD. Over-write if:
178 -- 1. Db owner is old FNDLOAD, or
179 -- 2. Db owner is new FNDLOAD, and file date >= db date
180 if ((l_db_id in (0,1)) or
181 ((l_db_id = 2) and (p_file_lud >= p_db_lud))) then
182 retcode := TRUE;
183 else
184 retcode := FALSE;
185 end if;
186 elsif ((l_file_id >= 120) and (l_file_id<=129)) then
187 -- File owner is R12 seed data, Over-write if:
188 -- 1. Db owner is (0, 1, 2,120..129) and l_db_id<l_file_id, or
189 -- 2. Db owner is file owner, and file date >= db date
190 if (
191 ((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
192 ((l_db_id = l_file_id) and (p_file_lud >= p_db_lud))
193 ) then
194 retcode := TRUE;
195 else
196 retcode := FALSE;
197 end if;
198
199
200 else
201 -- File owner is USER. Over-write if:
202 -- 1. Db owner is old or new FNDLOAD, or
203 -- 2. File date >= db date
204 if ((l_db_id in (0,1,2,120,121,122,123,124,125,126,127,128,129)) or
205 (trunc(p_file_lud) >= trunc(p_db_lud))) then
206 retcode := TRUE;
207 else
208 retcode := FALSE;
209 end if;
210 end if;
211 else
212 if (l_file_id in (0,1)) then
213 -- File owner is old FNDLOAD.
214 if (l_db_id in (0,1)) then
215 -- DB owner is also old FNDLOAD.
216 -- Over-write, but only if file ludate > db ludate.
217 if (p_file_lud > p_db_lud) then
218 retcode := TRUE;
219 else
220 retcode := FALSE;
221 end if;
222 else
223 retcode := FALSE;
224 end if;
225 elsif (l_file_id = 2) then
226 -- File owner is new FNDLOAD. Over-write if:
227 -- 1. Db owner is old FNDLOAD, or
228 -- 2. Db owner is new FNDLOAD, and file date > db date
229 if ((l_db_id in (0,1)) or
230 ((l_db_id = 2) and (p_file_lud > p_db_lud))) then
231 retcode := TRUE;
232 else
233 retcode := FALSE;
234 end if;
235 elsif ((l_file_id >= 120) and (l_file_id <= 129)) then
236 -- File owner is R12 seed data, Over-write if:
237 -- 1. Db owner is (0,1), or
238 -- 2. Db owner is 2 and p_file_lud != p_db_lud, or
239 -- 2. Db owner is 120 and file date > db date
240 if ((l_db_id in (0,1)) or
241 ((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
242 ((l_db_id = l_file_id) and (p_file_lud > p_db_lud))) then
243 retcode := TRUE;
244 else
245 retcode := FALSE;
246 end if;
247
248
249 else
250 -- File owner is USER. Over-write if:
251 -- 1. Db owner is old or new FNDLOAD, or
252 -- 2. File date > db date
253 if ((l_db_id in (0,1,2,120,121,122,123,124,125,126,127,128,129)) or
254 (trunc(p_file_lud) > trunc(p_db_lud))) then
255 retcode := TRUE;
256 else
257 retcode := FALSE;
258 end if;
259 end if;
260
261 end if;
262
263
264 if (NLS_MODE) then
265 if (retcode = FALSE) then
266 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED');
267 end if;
268 else
269 if (retcode = FALSE) then
270 fnd_message.set_name('FND', 'FNDLOAD_CUSTOMIZED_US');
271 end if;
272 end if;
273
274 return retcode;
275 end UPLOAD_TEST;
276
277 -- Bug 2438503 Routine to return NULL value.
278
279 function NULL_VALUE
280 return varchar2 is
281 begin
282 return '*NULL*';
283 end NULL_VALUE;
284
285 procedure SET_NLS_MODE is
286 begin
287 NLS_MODE:=TRUE;
288 end SET_NLS_MODE;
289
290 end FND_LOAD_UTIL;
[Home] [Help]