[Home] [Help]
PACKAGE BODY: APPS.XLA_TB_DEFN_DETAIL_PVT
Source
1 PACKAGE BODY XLA_TB_DEFN_DETAIL_PVT AS
2 /* $Header: xlathtbdtl.pkb 120.0 2005/10/07 12:12:37 svjoshi noship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | xla_tb_definition_PVT |
10 | |
11 | Description |
12 | This is a XLA package, which contains all the logic required |
13 | to maintain trial balance report definitions |
14 | |
15 | |
16 | HISTORY |
17 | 17-AUG-2005 M.Asada Created |
18 +===========================================================================*/
19
20 C_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'xla_tb_defn_detail_PVT';
21
22 --
23 --
24 --+==========================================================================+
25 --| |
26 --| PUBLIC PROCEDURE |
27 --| |
28 --| Create trial balance report definitions |
29 --| |
30 --| |
31 --+==========================================================================+
32 --
33 --
34 PROCEDURE Load_Row
35 (p_definition_code IN VARCHAR2
36 ,p_object_version_number IN NUMBER
37 ,p_code_combination_id IN NUMBER
38 ,p_flexfield_segment_code IN VARCHAR2
39 ,p_segment_value_from IN VARCHAR2
40 ,p_segment_value_to IN VARCHAR2
41 ,p_last_update_date IN VARCHAR2
42 ,p_owner IN VARCHAR2
43 ,p_custom_mode IN VARCHAR2) IS
44
45 CURSOR c_def IS
46 SELECT definition_code
47 ,object_version_number
48 ,defined_by_code
49 ,last_updated_by
50 ,last_update_date
51 FROM xla_tb_definitions_b
52 WHERE definition_code = p_definition_code;
53
54 CURSOR c_detail_f IS
55 SELECT 'Y'
56 FROM xla_tb_defn_details
57 WHERE definition_code = p_definition_code
58 AND code_combination_id = p_code_combination_id;
59
60 CURSOR c_detail_s IS
61 SELECT 'Y'
62 FROM xla_tb_defn_details
63 WHERE definition_code = p_definition_code
64 AND code_combination_id = p_segment_value_to;
65
66
67 l_definition_code VARCHAR2(30);
68 l_defined_by_code VARCHAR2(30);
69 l_last_updated_by NUMBER; -- owner in file
70 l_last_update_date DATE; -- last update date in file
71 l_db_object_version_number NUMBER; -- object version number in db
72 l_db_last_updated_by NUMBER; -- owner in db
73 l_db_last_update_date DATE; -- last update date in db
74 l_rowid ROWID;
75 l_dummy VARCHAR2(1);
76
77 BEGIN
78
79 l_last_updated_by := fnd_load_util.owner_id(p_owner);
80 l_last_update_date := NVL(TO_DATE(p_last_update_date, 'YYYY/MM/DD'), SYSDATE);
81
82 OPEN c_def;
83 FETCH c_def
84 INTO l_definition_code
85 ,l_db_object_version_number
86 ,l_defined_by_code
87 ,l_db_last_updated_by
88 ,l_db_last_update_date;
89
90 IF l_defined_by_code = 'FLEXFIELD' THEN
91
92 OPEN c_detail_f;
93 FETCH c_detail_f
94 INTO l_dummy;
95
96 IF (c_detail_f%NOTFOUND) THEN
97
98 Insert_Row (
99 p_rowid => l_rowid
100 ,p_definition_code => p_definition_code
101 ,p_object_version_number => l_db_object_version_number
102 ,p_code_combination_id => p_code_combination_id
103 ,p_flexfield_segment_code => p_flexfield_segment_code
104 ,p_segment_value_from => p_segment_value_from
105 ,p_segment_value_to => p_segment_value_to
106 ,p_creation_Date => l_last_update_date
107 ,p_Created_By => l_last_updated_by
108 ,p_Last_Update_Date => l_last_update_date
109 ,p_Last_Updated_By => l_last_updated_by
110 ,p_Last_Update_Login => 0);
111
112 END IF;
113
114 CLOSE c_detail_f;
115
116 ELSIF l_defined_by_code = 'SEGMENT' THEN
117
118 OPEN c_detail_s;
119 FETCH c_detail_s
120 INTO l_dummy;
121
122
123 IF (c_detail_s%NOTFOUND) THEN
124
125 Insert_Row (
126 p_rowid => l_rowid
127 ,p_definition_code => p_definition_code
128 ,p_object_version_number => l_db_object_version_number
129 ,p_code_combination_id => p_code_combination_id
130 ,p_flexfield_segment_code => p_flexfield_segment_code
131 ,p_segment_value_from => p_segment_value_from
132 ,p_segment_value_to => p_segment_value_to
133 ,p_creation_Date => l_last_update_date
134 ,p_Created_By => l_last_updated_by
135 ,p_Last_Update_Date => l_last_update_date
136 ,p_Last_Updated_By => l_last_updated_by
137 ,p_Last_Update_Login => 0);
138
139 ELSE
140
141 --
142 -- Recreate rows if allowed (segment_value_to might be updated by customers)
143 --
144 IF (fnd_load_util.upload_test(
145 p_file_id => l_last_updated_by
146 ,p_file_lud => l_last_update_date
147 ,p_db_id => l_db_last_updated_by
148 ,p_db_lud => l_db_last_update_date
149 ,p_custom_mode => p_custom_mode))
150 THEN
151
152 BEGIN
153
154 Delete_Row
155 (p_definition_code => p_definition_code
156 ,p_flexfield_segment_code => p_flexfield_segment_code
157 ,p_segment_value_from => p_segment_value_from);
158
159 EXCEPTION
160 WHEN NO_DATA_FOUND THEN
161 NULL;
162 END ;
163
164 Insert_Row (
165 p_rowid => l_rowid
166 ,p_definition_code => p_definition_code
167 ,p_object_version_number => l_db_object_version_number
168 ,p_code_combination_id => p_code_combination_id
169 ,p_flexfield_segment_code => p_flexfield_segment_code
170 ,p_segment_value_from => p_segment_value_from
171 ,p_segment_value_to => p_segment_value_to
172 ,p_creation_Date => l_last_update_date
173 ,p_Created_By => l_last_updated_by
174 ,p_last_update_date => l_last_update_date
175 ,p_last_updated_by => l_last_updated_by
176 ,p_last_update_login => 0);
177
178 END IF;
179
180 END IF;
181
182 CLOSE c_detail_s;
183
184 END IF;
185
186 CLOSE c_def;
187
188 EXCEPTION
189 WHEN xla_exceptions_pkg.application_exception THEN
190 RAISE;
191 WHEN OTHERS THEN
192 xla_exceptions_pkg.raise_message
193 ('XLA' , 'XLA_COMMON_FAILURE'
194 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'load_row'
195 ,'ERROR' , sqlerrm);
196 END Load_Row;
197
198 --
199 --
200 --+==========================================================================+
201 --| |
202 --| PUBLIC PROCEDURE |
203 --| |
204 --| Create trial balance report definitions |
205 --| |
206 --| |
207 --+==========================================================================+
208 --
209 --
210 PROCEDURE Insert_Row
211 (p_rowid IN OUT NOCOPY VARCHAR2
212 ,p_definition_code IN VARCHAR2
213 ,p_object_version_number IN NUMBER
214 ,p_code_combination_id IN NUMBER
215 ,p_flexfield_segment_code IN VARCHAR2
216 ,p_segment_value_from IN VARCHAR2
217 ,p_segment_value_to IN VARCHAR2
218 ,p_creation_date IN DATE
219 ,p_created_by IN NUMBER
220 ,p_last_update_date IN DATE
221 ,p_last_updated_by IN NUMBER
222 ,p_last_update_login IN NUMBER) IS
223
224 CURSOR c_def IS
225 SELECT defined_by_code
226 FROM xla_tb_definitions_b
227 WHERE definition_code = p_definition_code;
228
229 l_defined_by_code VARCHAR2(30);
230
231 l_code_combination_id NUMBER(15);
232 l_flexfield_segment_code VARCHAR2(30);
233 l_segment_value_from VARCHAR2(25);
234 l_segment_value_to VARCHAR2(25);
235
236 BEGIN
237
238 IF p_definition_code IS NULL THEN
239 RAISE no_data_found;
240 END IF;
241
242
243 OPEN c_def;
244 FETCH c_def
245 INTO l_defined_by_code;
246
247 IF l_defined_by_code = 'FLEXFIELD' THEN
248
249 l_code_combination_id := p_code_combination_id;
250 l_flexfield_segment_code := NULL;
251 l_segment_value_from := NULL;
252 l_segment_value_to := NULL;
253
254 ELSIF l_defined_by_code = 'SEGMENT' THEN
255
256 l_code_combination_id := NULL;
257 l_flexfield_segment_code := p_flexfield_segment_code;
258 l_segment_value_from := p_segment_value_from;
259 l_segment_value_to := p_segment_value_to;
260
261 END IF;
262
263 INSERT INTO xla_tb_defn_details
264 (
265 definition_detail_id
266 ,object_version_number
267 ,definition_code
268 ,flexfield_segment_code
269 ,segment_value_from
270 ,segment_value_to
271 ,code_combination_id
272 ,created_by
273 ,creation_date
274 ,last_updated_by
275 ,last_update_date
276 ,last_update_login
277 )
278 VALUES
279 (
280 xla_tb_defn_details_s.nextval
281 ,1 -- Ignore p_object_version_number
282 ,p_definition_code
283 ,l_flexfield_segment_code
284 ,l_segment_value_from
285 ,l_segment_value_to
286 ,l_code_combination_id
287 ,p_created_by
288 ,p_creation_date
289 ,p_last_updated_by
290 ,p_last_update_date
291 ,p_last_update_login
292 )
293 RETURNING rowid INTO p_rowid;
294
295 CLOSE c_def;
296
297 EXCEPTION
298 WHEN xla_exceptions_pkg.application_exception THEN
299 RAISE;
300 WHEN OTHERS THEN
301 xla_exceptions_pkg.raise_message
302 ('XLA' , 'XLA_COMMON_FAILURE'
303 ,'LOCATION' , C_PACKAGE_NAME || '.' || 'insert_row'
304 ,'ERROR' , sqlerrm);
305 END Insert_Row;
306
307 --+==========================================================================+
308 --| |
309 --| PUBLIC PROCEDURE |
310 --| |
311 --| Update trial balance report definition details |
312 --| |
313 --| |
314 --+==========================================================================+
315 --
316 --
317 -- No Update API for this table. Delete and recreate rows.
318 --
319
320 --
321 --
322 --+==========================================================================+
323 --| |
324 --| PUBLIC PROCEDURE |
325 --| |
326 --| Delete trial balance report definitions |
327 --| (Define by Flexfield) |
328 --| |
329 --+==========================================================================+
330 --
331 --
332 PROCEDURE Delete_Row
333 (p_definition_code IN VARCHAR2
334 ,p_code_combination_id IN NUMBER) IS
335 BEGIN
336
337 DELETE FROM xla_tb_defn_details
338 WHERE definition_code = p_definition_code
339 AND code_combination_id = p_code_combination_id;
340
341 IF SQL%NOTFOUND then
342 RAISE no_data_found;
343 END IF;
344
345 END Delete_Row;
346
347 --
348 --
349 --+==========================================================================+
350 --| |
351 --| PUBLIC PROCEDURE |
352 --| |
353 --| Delete trial balance report definitions |
354 --| (Defined by Segment) |
355 --| |
356 --+==========================================================================+
357 --
358 --
359 PROCEDURE Delete_Row
360 (p_definition_code IN VARCHAR2
361 ,p_flexfield_segment_code IN VARCHAR2
362 ,p_segment_value_from IN VARCHAR2) IS
363 BEGIN
364
365 DELETE FROM xla_tb_defn_details
366 WHERE definition_code = p_definition_code
367 AND flexfield_segment_code = p_flexfield_segment_code
368 AND segment_value_from = p_segment_value_from;
372 END IF;
369
370 IF SQL%NOTFOUND then
371 RAISE no_data_found;
373
374 END Delete_Row;
375
376 END XLA_TB_DEFN_DETAIL_PVT; -- end of package spec