1 PACKAGE xla_cmp_tad_pkg AS
2 /* $Header: xlacptad.pkh 120.2.12010000.2 2008/09/05 22:06:33 vkasina ship $ */
3 /*======================================================================+
4 | Copyright (c) 1995-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_cmp_tad_pkg |
10 | |
11 | DESCRIPTION |
12 | Transaction Account Builder Engine Compiler |
13 | |
14 | HISTORY |
15 | 09-MAR-04 A.Quaglia Created |
16 | 04-JUN-04 A.Quaglia Removed amb_context param from |
17 | compile_application_tads_srs |
18 | |
19 | |
20 | |
21 | |
22 | |
23 +======================================================================*/
24 --Public constants
25 C_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
26 C_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
27 C_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) :=
28 FND_API.G_RET_STS_UNEXP_ERROR;
29 C_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
30 C_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
31 --Public types
32 TYPE gt_table_V30 IS TABLE OF VARCHAR2(30);
33 TYPE gt_table_V30_V30 IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(30);
34
35 --Dynamic Package Body variables
36 G_OA_MESSAGE CONSTANT VARCHAR2(1) := xla_exceptions_pkg.C_OA_MESSAGE;
37
38 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
39 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
40 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
41 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
42 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
43 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
44
45 --1-STATEMENT, 2-PROCEDURE, 3-EVENT, 4-EXCEPTION, 5-ERROR, 6-UNEXPECTED
46
47 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
48
49 g_log_level NUMBER;
50 g_log_enabled BOOLEAN;
51
52
53 C_TMPL_BATCH_BUILD_CCID_STMTS CONSTANT CLOB :=
54 '
55 --target_ccid not null and no segment value given
56 --we go straight to ccid validation
57 UPDATE $TABLE_NAME$ gt
58 SET gt.target_ccid = NVL( (SELECT gcc.code_combination_id
59 FROM gl_code_combinations gcc
60 WHERE gcc.chart_of_accounts_id = p_chart_of_accounts_id
61 AND gcc.template_id IS NULL
62 AND gcc.enabled_flag = ''Y''
63 AND gcc.code_combination_id = gt.target_ccid
64 )
65 ,-gt.target_ccid
66 )
67 ,gt.processed_flag = ''Y''
68 ,(
69 $C_TMPL_UPD_SET_SEGMENT_COMMAS$
70 )
71 = ( SELECT
72 $C_TMPL_SEL_NVL_SEGMENT_COMMAS$
73 FROM gl_code_combinations gcc
74 WHERE gcc.chart_of_accounts_id = p_chart_of_accounts_id
75 AND gcc.template_id IS NULL
76 AND gcc.enabled_flag = ''Y''
77 AND gcc.code_combination_id = gt.target_ccid
78 )
79 WHERE gt.target_ccid > 0
80 AND 0 = (
81 SELECT count(*)
82 FROM xla_tab_errors_gt xterr
83 WHERE xterr.base_rowid = gt.ROWID
84 )
85 $C_TMPL_WHERE_SEGMENT_NULL_ANDS$
86 ;
87
88 --log errors for non existent or disabled ccids
89 UPDATE $TABLE_NAME$ gt
90 SET gt.target_ccid = $TAD_PACKAGE_NAME_1$.log_ccid_not_found_error
91 (
92 ''BATCH'' --p_mode
93 ,gt.ROWID --p_rowid
94 ,NULL --p_line_index
95 ,p_chart_of_accounts_name --p_chart_of_accounts_name
96 ,-gt.target_ccid --p_ccid
97 ,''$TAD_PACKAGE_NAME_1$.trans_account_def_batch'' --p_calling_function_name
98 ,gt.account_type_code --p_account_type_code
99 )
100 WHERE gt.target_ccid < 0
101 AND 0 = (
102 SELECT count(*)
103 FROM xla_tab_errors_gt xterr
104 WHERE xterr.base_rowid = gt.ROWID
105 )
106 AND gt.processed_flag = ''Y''
107 ;
108
109
110 --target_ccid not null and some segment value given
111 --Lookup ccid for missing segment values
112 UPDATE $TABLE_NAME$ gt
113 SET (
114 $C_TMPL_UPD_SET_SEGMENT_COMMAS$
115 )
116 = ( SELECT
117 $C_TMPL_SEL_NVL_SEGMENT_COMMAS$
118 FROM gl_code_combinations gcc
119 WHERE gcc.chart_of_accounts_id = p_chart_of_accounts_id
120 AND gcc.template_id IS NULL
121 AND gcc.enabled_flag = ''Y''
122 AND gcc.code_combination_id = gt.target_ccid
123 )
124 WHERE gt.target_ccid > 0
125 AND gt.processed_flag IS NULL
126 AND 0 = (
127 SELECT count(*)
128 FROM xla_tab_errors_gt xterr
129 WHERE xterr.base_rowid = gt.ROWID
130 )
131 AND 1 = (
132 SELECT count(*)
133 FROM gl_code_combinations gcc
134 WHERE gcc.chart_of_accounts_id = p_chart_of_accounts_id
135 AND gcc.template_id IS NULL
136 AND gcc.enabled_flag = ''Y''
137 AND gcc.code_combination_id = gt.target_ccid
138 )
139 ;
140
141
142 --Here we consider also the lines where target_ccid is null
143 --If some rows still have missing segment values log an error
144 UPDATE $TABLE_NAME$ gt
145 SET gt.target_ccid = $TAD_PACKAGE_NAME_1$.log_null_segments_error
146 (
147 ''BATCH'' --p_mode
148 ,gt.ROWID --p_rowid
149 ,NULL --p_line_index
150 ,p_chart_of_accounts_name --p_chart_of_accounts_name
151 ,gt.target_ccid --p_ccid
152 ,''$TAD_PACKAGE_NAME_1$.trans_account_def_batch'' --p_calling_function_name
153 ,gt.account_type_code --p_account_type_code
154 )
155 WHERE gt.processed_flag IS NULL
156 AND ($C_TMPL_WHERE_SEGMENT_NULL_ORS$
157 )
158 AND 0 = (
159 SELECT count(*)
160 FROM xla_tab_errors_gt xterr
161 WHERE xterr.base_rowid = gt.ROWID
162 )
163 ;
164
165
166
167 --All valid rows now have the segment values
168 --Need to lookup for existing ccids from seg vals
169 --for all lines
170 UPDATE $TABLE_NAME$ gt
171 SET gt.target_ccid = (SELECT CASE gcc.enabled_flag
172 WHEN ''Y'' THEN gcc.code_combination_id
173 ELSE -gcc.code_combination_id
174 END
175 FROM gl_code_combinations gcc
176 WHERE gcc.chart_of_accounts_id = p_chart_of_accounts_id
177 AND gcc.template_id IS NULL
178 $C_TMPL_WHERE_SEGMENTS_EQUALS$
179 )
180 WHERE gt.processed_flag IS NULL
181 AND 0 = (
182 SELECT count(*)
183 FROM xla_tab_errors_gt xterr
184 WHERE xterr.base_rowid = gt.ROWID
185 )
186 ;
187
188
189 --Log errors for disabled ccids
190 UPDATE $TABLE_NAME$ gt
191 SET gt.target_ccid = $TAD_PACKAGE_NAME_1$.log_ccid_disabled_error
192 (
193 ''BATCH'' --p_mode
194 ,gt.ROWID --p_rowid
195 ,NULL --p_line_index
196 ,p_chart_of_accounts_name --p_chart_of_accounts_name
197 ,-gt.target_ccid --p_ccid
198 ,''$TAD_PACKAGE_NAME_1$.trans_account_def_batch'' --p_calling_function_name
199 ,gt.account_type_code --p_account_type_code
200 )
201 WHERE gt.target_ccid < 0
202 AND gt.processed_flag IS NULL
203 AND 0 = (
204 SELECT count(*)
205 FROM xla_tab_errors_gt xterr
206 WHERE xterr.base_rowid = gt.ROWID
207 )
208 ;
209
210
211 --Build the concatenated segments string for all the valid rows
212 UPDATE $TABLE_NAME$ gt
213 SET gt.concatenated_segments =
214 $C_TMPL_CONCAT_SEGMENTS$
215 WHERE 0 = (
216 SELECT count(*)
217 FROM xla_tab_errors_gt xterr
218 WHERE xterr.base_rowid = gt.ROWID
219 );
220
221
222 --Create missing ccids
223 UPDATE $TABLE_NAME$ gt
224 SET gt.target_ccid = $TAD_PACKAGE_NAME_1$.create_ccid --AFFIX
225 (
226 ''BATCH'' --p_mode
227 ,gt.ROWID --p_rowid
228 ,NULL --p_line_index
229 ,p_chart_of_accounts_id --p_chart_of_accounts_id
230 ,p_chart_of_accounts_name --p_chart_of_accounts_name
231 ,''$TAD_PACKAGE_NAME_1$.trans_account_def_batch'' --p_calling_function_name
232 ,l_current_date --p_validation_date
233 ,gt.concatenated_segments --p_concatenated_segments
234 )
235 WHERE gt.ROWID IN
236 (
237 SELECT MIN(gtint.ROWID)
238 FROM $TABLE_NAME$ gtint
239 WHERE gtint.target_ccid IS NULL
240 AND gtint.processed_flag IS NULL
241 AND 0 = (
242 SELECT count(*)
243 FROM xla_tab_errors_gt xterr
244 WHERE xterr.base_rowid = gtint.ROWID
245 )
246 GROUP BY gtint.concatenated_segments
247 )
248 ;
249
250
251 --Propagate the errors to all the other rows with the same segments
252 UPDATE $TABLE_NAME$ gt
253 SET gt.target_ccid =
254 ( SELECT $TAD_PACKAGE_NAME_1$.log_error
255 (
256 ''BATCH'' -- p_mode
257 ,gt.rowid -- p_rowid
258 ,NULL -- p_line_index
259 ,xtnc.msg_data -- p_encoded_message
260 )
261 FROM xla_tab_new_ccids_gt xtnc
262 WHERE xtnc.code_combination_id IS NULL
263 AND xtnc.concatenated_segments = gt.concatenated_segments
264 )
265 WHERE gt.target_ccid IS NULL
266 AND gt.processed_flag IS NULL
267 AND 0 = (
268 SELECT count(*)
269 FROM xla_tab_errors_gt xterr
270 WHERE xterr.base_rowid = gt.ROWID
271 )
272 AND gt.ROWID IN
273 ( SELECT gt.ROWID
274 FROM xla_tab_new_ccids_gt xtnc
275 WHERE xtnc.code_combination_id IS NULL
276 AND xtnc.concatenated_segments = gt.concatenated_segments
277 );
278
279
280 --Propagate the good ccids to all the other rows with the same segments
281 UPDATE $TABLE_NAME$ gt
282 SET gt.target_ccid = ( SELECT xtnc.code_combination_id
283 FROM xla_tab_new_ccids_gt xtnc
284 WHERE xtnc.code_combination_id IS NOT NULL
285 AND xtnc.concatenated_segments = gt.concatenated_segments
286 )
287 WHERE gt.target_ccid IS NULL
288 AND gt.processed_flag IS NULL
289 AND 0 = (
290 SELECT count(*)
291 FROM xla_tab_errors_gt xterr
292 WHERE xterr.base_rowid = gt.ROWID
293 )
294 AND gt.ROWID NOT IN (SELECT xtnc.base_rowid
295 FROM xla_tab_new_ccids_gt xtnc
296 );
297
298 --For lines having at least one error update the message count field
299 UPDATE $TABLE_NAME$ gt
300 SET gt.msg_count = (SELECT count(*)
301 FROM xla_tab_errors_gt xterr
302 WHERE xterr.base_rowid = gt.ROWID
303 )
304 WHERE 0 < (
305 SELECT count(*)
306 FROM xla_tab_errors_gt xterr
307 WHERE xterr.base_rowid = gt.ROWID
308 );
309
310 --For lines having exactly one error pull the message into the main table
311 UPDATE $TABLE_NAME$ gt
312 SET gt.msg_data = (SELECT xterr.msg_data
313 FROM xla_tab_errors_gt xterr
314 WHERE xterr.base_rowid = gt.ROWID
315 )
316 WHERE 1 = (
317 SELECT count(*)
318 FROM xla_tab_errors_gt xterr
319 WHERE xterr.base_rowid = gt.ROWID
320 );
321 ';
322
323
324 /*======================================================================+
325 | |
326 | Public Procedure |
327 | |
328 | compile_application_tads_srs |
329 | |
330 | SRS wrapper for compile_api |
331 | p_retcode := 0 means that the compilation was successful. |
332 | p_retcode := 2 means that errors were encountered and that the |
333 | generation of the API was unsuccessful. |
334 +======================================================================*/
335 PROCEDURE compile_application_tads_srs
336 ( p_errbuf OUT NOCOPY VARCHAR2
337 ,p_retcode OUT NOCOPY NUMBER
338 ,p_application_id IN NUMBER
339 );
340
341
342 /*======================================================================+
343 | |
344 | Public Function |
345 | |
346 | compile_application_tads |
347 | |
348 | It generates the Transaction Account Builder Engine for the specified |
349 | application and AMB Context Code. |
350 | It generates one package header and one package body in the <APPS> |
351 | schema for each enabled Transaction Account Definition. |
352 | |
353 | It returns a BOOLEAN value. |
354 | TRUE means that the compilation was successful. |
355 | FALSE means that errors were encountered and that the generation |
356 | of the API was unsuccessful. |
357 +======================================================================*/
358 FUNCTION compile_application_tads
359 ( p_application_id IN NUMBER
360 )
361 RETURN BOOLEAN
362 ;
363
364 /*======================================================================+
365 | |
366 | Public Function |
367 | |
368 | compile_tad |
369 | |
370 | It generates the Transaction Account Builder Engine for the specified |
371 | Transaction Account Definition. |
372 | It generates one package header and one package body in the <APPS> |
373 | schema. |
374 | |
375 | It returns a BOOLEAN value. |
376 | TRUE means that the compilation was successful. |
377 | FALSE means that errors were encountered and that the generation |
378 | of the API was unsuccessful. |
379 +======================================================================*/
380 FUNCTION compile_tad
381 ( p_application_id IN NUMBER
382 ,p_account_definition_code IN VARCHAR2
383 ,p_account_definition_type_code IN VARCHAR2
384 ,p_amb_context_code IN VARCHAR2
385 )
386 RETURN BOOLEAN;
387
388 /*======================================================================+
389 | |
390 | Public Function |
391 | |
392 | compile_tad_AUTONOMUS |
393 | |
394 | Same as compile_tad but peforms the actions in an autonomous |
395 | transaction in order not to delete the content of the global |
396 | temporary tables. |
397 | |
398 | |
399 | It returns a BOOLEAN value. |
400 | TRUE means that the compilation was successful. |
401 | FALSE means that errors were encountered and that the generation |
402 | of the API was unsuccessful. |
403 +======================================================================*/
404 FUNCTION compile_tad_AUTONOMOUS
405 ( p_application_id IN NUMBER
406 ,p_account_definition_code IN VARCHAR2
407 ,p_account_definition_type_code IN VARCHAR2
408 ,p_amb_context_code IN VARCHAR2
409 )
410 RETURN BOOLEAN;
411
412 /*======================================================================+
413 | |
414 | Public Function |
415 | |
416 | get_tad_package_name |
417 | |
418 | Builds the package name for the specified Transaction Account |
419 | Definitions. |
420 | |
421 | It assigns the hash_id if it is null. |
422 | |
423 | It returns a BOOLEAN value. |
424 | TRUE means that the function was successful. |
425 | FALSE means that errors were encountered
426 | |
427 +======================================================================*/
428
429 FUNCTION get_tad_package_name
430 (
431 p_application_id IN NUMBER
432 ,p_account_definition_code IN VARCHAR2
433 ,p_account_definition_type_code IN VARCHAR2
434 ,p_amb_context_code IN VARCHAR2
435 ,p_tad_package_name OUT NOCOPY VARCHAR2
436 )
437 RETURN BOOLEAN;
438
439
440
441
442 END xla_cmp_tad_pkg;
[Home] [Help]