[Home] [Help]
PACKAGE BODY: APPS.XLA_TAB_ATS_F_PKG
Source
1 PACKAGE BODY xla_tab_ats_f_pkg AS
2 /* $Header: xlathtabats.pkb 120.0 2005/09/05 16:53:28 jlarre noship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_tab_ats_f_pkg |
10 | |
11 | DESCRIPTION |
12 | Plsql table handler for table xla_tab_acct_type_srcs |
13 | |
14 | HISTORY |
15 | 01-SEP-2005 Jorge Larre Initial Creation |
16 | |
17 +======================================================================*/
18
19 --=======================================================================
20 -- *********** Local Trace Routine **********
21 --=======================================================================
22 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
24 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
25 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
27 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
28
29 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
30 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_tab_ats_f_pkg';
31
32 g_log_level NUMBER;
33 g_log_enabled BOOLEAN;
34
35 PROCEDURE trace
36 (p_msg IN VARCHAR2
37 ,p_module IN VARCHAR2
38 ,p_level IN NUMBER) IS
39 BEGIN
40 ----------------------------------------------------------------------------
41 -- Following is for FND log.
42 ----------------------------------------------------------------------------
43 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
44 fnd_log.message(p_level, p_module);
45 ELSIF p_level >= g_log_level THEN
46 fnd_log.string(p_level, p_module, p_msg);
47 END IF;
48
49 EXCEPTION
50 WHEN xla_exceptions_pkg.application_exception THEN
51 RAISE;
52
53 WHEN OTHERS THEN
54 xla_exceptions_pkg.raise_message
55 (p_location => 'xla_tab_ats_f_pkg.trace');
56 END trace;
57
58 /*======================================================================+
59 | |
60 | Procedure insert_row |
61 | |
62 +======================================================================*/
63 PROCEDURE insert_row
64 (x_rowid IN OUT NOCOPY VARCHAR2
65 ,x_application_id IN NUMBER
66 ,x_account_type_code IN VARCHAR2
67 ,x_source_application_id IN NUMBER
68 ,x_source_type_code IN VARCHAR2
69 ,x_source_code IN VARCHAR2
70 ,x_creation_date IN DATE
71 ,x_created_by IN NUMBER
72 ,x_last_update_date IN DATE
73 ,x_last_updated_by IN NUMBER
74 ,x_last_update_login IN NUMBER)
75 IS
76
77 CURSOR c IS
78 SELECT rowid
79 FROM xla_tab_acct_type_srcs
80 WHERE application_id = x_application_id
81 AND account_type_code = x_account_type_code
82 AND source_application_id = x_source_application_id
83 AND source_type_code = x_source_type_code
84 AND source_code = x_source_code;
85
86 l_log_module VARCHAR2(240);
87
88 BEGIN
89
90 IF g_log_enabled THEN
91 l_log_module := C_DEFAULT_MODULE||'.insert_row';
92 END IF;
93
94 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
95 trace(p_msg => 'BEGIN of procedure insert_row',
96 p_module => l_log_module,
97 p_level => C_LEVEL_PROCEDURE);
98 END IF;
99
100 INSERT INTO xla_tab_acct_type_srcs
101 (creation_date
102 ,created_by
103 ,application_id
104 ,account_type_code
105 ,source_application_id
106 ,source_type_code
107 ,source_code
108 ,last_update_date
109 ,last_updated_by
110 ,last_update_login)
111 VALUES
112 (x_creation_date
113 ,x_created_by
114 ,x_application_id
115 ,x_account_type_code
116 ,x_source_application_id
117 ,x_source_type_code
118 ,x_source_code
119 ,x_last_update_date
120 ,x_last_updated_by
121 ,x_last_update_login)
122 ;
123
124 OPEN c;
125 FETCH c INTO x_rowid;
126 IF (c%NOTFOUND) THEN
127 CLOSE c;
128 RAISE NO_DATA_FOUND;
129 END IF;
130 CLOSE c;
131
132 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
133 trace(p_msg => 'END of procedure insert_row',
134 p_module => l_log_module,
135 p_level => C_LEVEL_PROCEDURE);
136 END IF;
137
138 END insert_row;
139
140 /*======================================================================+
141 | |
142 | Procedure lock_row |
143 | This procedure does not do anything |
144 | |
145 +======================================================================*/
146 PROCEDURE lock_row
147 (x_application_id IN NUMBER
148 ,x_account_type_code IN VARCHAR2
149 ,x_source_application_id IN NUMBER
150 ,x_source_type_code IN VARCHAR2
151 ,x_source_code IN VARCHAR2)
152 IS
153
154 l_log_module VARCHAR2(240);
155
156 BEGIN
157
158 IF g_log_enabled THEN
159 l_log_module := C_DEFAULT_MODULE||'.lock_row';
160 END IF;
161
162 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
163 trace(p_msg => 'BEGIN of procedure lock_row',
164 p_module => l_log_module,
165 p_level => C_LEVEL_PROCEDURE);
166 END IF;
167
168 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
169 trace(p_msg => 'END of procedure lock_row',
170 p_module => l_log_module,
171 p_level => C_LEVEL_PROCEDURE);
172 END IF;
173
174 END lock_row;
175 /*======================================================================+
176 | |
177 | Procedure update_row |
178 | |
179 +======================================================================*/
180 PROCEDURE update_row
181 (x_application_id IN NUMBER
182 ,x_account_type_code IN VARCHAR2
183 ,x_source_application_id IN NUMBER
184 ,x_source_type_code IN VARCHAR2
185 ,x_source_code IN VARCHAR2
186 ,x_last_update_date IN DATE
187 ,x_last_updated_by IN NUMBER
188 ,x_last_update_login IN NUMBER)
189
190 IS
191
192 l_log_module VARCHAR2(240);
193 BEGIN
194 IF g_log_enabled THEN
195 l_log_module := C_DEFAULT_MODULE||'.update_row';
196 END IF;
197
198 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
199 trace(p_msg => 'BEGIN of procedure update_row',
200 p_module => l_log_module,
201 p_level => C_LEVEL_PROCEDURE);
202 END IF;
203
204 UPDATE xla_tab_acct_type_srcs
205 SET
206 last_updated_by = x_last_updated_by
207 ,last_update_login = x_last_update_login
208 WHERE application_id = x_application_id
209 AND account_type_code = x_account_type_code
210 AND source_application_id = x_source_application_id
211 AND source_type_code = x_source_type_code
212 AND source_code = x_source_code;
213
214 IF (SQL%NOTFOUND) THEN
215 RAISE NO_DATA_FOUND;
216 END IF;
217
218 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
219 trace(p_msg => 'END of procedure update_row',
220 p_module => l_log_module,
221 p_level => C_LEVEL_PROCEDURE);
222 END IF;
223
224 END update_row;
225 /*======================================================================+
226 | |
227 | Procedure delete_row |
228 | |
229 +======================================================================*/
230 PROCEDURE delete_row
231 (x_application_id IN NUMBER
232 ,x_account_type_code IN VARCHAR2
233 ,x_source_application_id IN NUMBER
234 ,x_source_type_code IN VARCHAR2
235 ,x_source_code IN VARCHAR2)
236
237 IS
238
239 l_log_module VARCHAR2(240);
240
241 BEGIN
242
243 IF g_log_enabled THEN
244 l_log_module := C_DEFAULT_MODULE||'.delete_row';
245 END IF;
246
247 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
248 trace(p_msg => 'BEGIN of procedure delete_row',
249 p_module => l_log_module,
250 p_level => C_LEVEL_PROCEDURE);
251 END IF;
252
253 DELETE FROM xla_tab_acct_type_srcs
254 WHERE application_id = x_application_id
255 AND account_type_code = x_account_type_code
256 AND source_application_id = x_source_application_id
257 AND source_type_code = x_source_type_code
258 AND source_code = x_source_code;
259
260 IF (SQL%NOTFOUND) THEN
261 RAISE NO_DATA_FOUND;
262 END IF;
263
264 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
265 trace(p_msg => 'END of procedure delete_row',
266 p_module => l_log_module,
267 p_level => C_LEVEL_PROCEDURE);
268 END IF;
269
270 END delete_row;
271
272 /*======================================================================+
273 | |
274 | Procedure load_row |
275 | |
276 +======================================================================*/
277 PROCEDURE load_row
278 (p_application_short_name IN VARCHAR2
279 ,p_account_type_code IN VARCHAR2
280 ,p_source_app_short_name IN VARCHAR2
281 ,p_source_type_code IN VARCHAR2
282 ,p_source_code IN VARCHAR2
283 ,p_owner IN VARCHAR2
284 ,p_last_update_date IN VARCHAR2)
285
286 IS
287
288 CURSOR c_app_id IS
289 SELECT application_id
290 FROM fnd_application
291 WHERE application_short_name = p_application_short_name;
292
293 CURSOR c_source_app_id IS
294 SELECT application_id
295 FROM fnd_application
296 WHERE application_short_name = p_source_app_short_name;
297
298 l_application_id INTEGER;
299 l_source_app_id INTEGER;
300 l_rowid ROWID;
301 l_exist VARCHAR2(1);
302 f_luby NUMBER; -- entity owner in file
303 f_ludate DATE; -- entity update date in file
304 db_luby NUMBER; -- entity owner in db
305 db_ludate DATE; -- entity update date in db
306 l_log_module VARCHAR2(240);
307
308 BEGIN
309
310 IF g_log_enabled THEN
311 l_log_module := C_DEFAULT_MODULE||'.load_row';
312 END IF;
313
314 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
315 trace(p_msg => 'BEGIN of procedure load_row',
316 p_module => l_log_module,
317 p_level => C_LEVEL_PROCEDURE);
318 END IF;
319
320 -- Translate owner to file_last_updated_by
321 f_luby := fnd_load_util.owner_id(p_owner);
322
323 -- Translate char last_update_date to date
324 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
325
326 OPEN c_app_id;
327 FETCH c_app_id INTO l_application_id;
328 CLOSE c_app_id;
329
330 OPEN c_source_app_id;
331 FETCH c_source_app_id INTO l_source_app_id;
332 CLOSE c_source_app_id;
333
334 BEGIN
335
336 SELECT last_updated_by, last_update_date
337 INTO db_luby, db_ludate
338 FROM xla_tab_acct_type_srcs
339 WHERE application_id = l_application_id
340 AND account_type_code = p_account_type_code
341 AND source_application_id = l_source_app_id
342 AND source_type_code = p_source_type_code
343 AND source_code = p_source_code;
344
345 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
346 xla_tab_ats_f_pkg.update_row
347 (x_application_id => l_application_id
348 ,x_account_type_code => p_account_type_code
349 ,x_source_application_id => l_source_app_id
350 ,x_source_type_code => p_source_type_code
351 ,x_source_code => p_source_code
352 ,x_last_update_date => f_ludate
353 ,x_last_updated_by => f_luby
354 ,x_last_update_login => 0);
355
356 END IF;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 xla_tab_ats_f_pkg.insert_row
361 (x_rowid => l_rowid
362 ,x_application_id => l_application_id
363 ,x_account_type_code => p_account_type_code
364 ,x_source_application_id => l_source_app_id
365 ,x_source_type_code => p_source_type_code
366 ,x_source_code => p_source_code
367 ,x_creation_date => f_ludate
368 ,x_created_by => f_luby
369 ,x_last_update_date => f_ludate
370 ,x_last_updated_by => f_luby
371 ,x_last_update_login => 0);
372 END;
373
374 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
375 trace(p_msg => 'END of procedure load_row',
376 p_module => l_log_module,
377 p_level => C_LEVEL_PROCEDURE);
378 END IF;
379
380 EXCEPTION
381 WHEN NO_DATA_FOUND THEN
382 null;
383 WHEN OTHERS THEN
384 xla_exceptions_pkg.raise_message
385 (p_location => 'xla_tab_ats_f_pkg.load_row');
386
387 END load_row;
388
389 end xla_tab_ats_f_pkg;