[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_HEADER_PKG
Source
1 PACKAGE BODY fa_xla_cmp_header_pkg AS
2 /* $Header: faxlachb.pls 120.1.12010000.2 2008/07/31 07:25:01 sbhaskar ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +============================================================================+
8 | PACKAGE NAME |
9 | fa_xla_cmp_header_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for to create header level extract for each extract type |
14 | |
15 | |
16 | HISTORY |
17 | 25-FEB-2006 BRIDGWAY Created |
18 | |
19 +===========================================================================*/
20
21
22 --+============================================+
23 --| |
24 --| PRIVATE PROCEDURES/FUNCTIONS |
25 --| |
26 --+============================================+
27
28
29 C_PRIVATE_API_1 CONSTANT VARCHAR2(32000) := '
30
31 /*======================================================================+
32 | |
33 | Private Function |
34 | $proc_name$ |
35 | |
36 +======================================================================*/
37
38 PROCEDURE $proc_name$ IS
39
40 l_procedure_name varchar2(80) := ''$proc_name$'';
41
42 BEGIN
43
44 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
45 fnd_log.string(G_LEVEL_PROCEDURE,
46 G_MODULE_NAME||l_procedure_name||''.begin'',
47 ''Beginning of procedure'');
48 END IF;
49
50 ';
51
52
53 C_PRIVATE_API_2 CONSTANT VARCHAR2(32000) := '
54
55 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
56 fnd_log.string(G_LEVEL_PROCEDURE,
57 G_MODULE_NAME||l_procedure_name,
58 ''Rows inserted into headers: '' || to_char(SQL%ROWCOUNT));
59 END IF;
60
61 ';
62
63 C_PRIVATE_API_3 CONSTANT VARCHAR2(32000) := '
64
65 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
66 fnd_log.string(G_LEVEL_PROCEDURE,
67 G_MODULE_NAME||l_procedure_name||''.end'',
68 ''End of procedure'');
69 END IF;
70
71 EXCEPTION
72 WHEN others THEN
73 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
74 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
75 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
76 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
77 END IF;
78 raise;
79
80 end $proc_name$;
81
82 ';
83
84 C_PRIVATE_ROLLBACK CONSTANT VARCHAR2(32000) := '
85
86 PROCEDURE Load_header_data_rb IS
87
88 l_procedure_name varchar2(80) := ''load_header_data_rb'';
89
90 BEGIN
91
92 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
93 fnd_log.string(G_LEVEL_PROCEDURE,
94 G_MODULE_NAME||l_procedure_name||''.begin'',
95 ''Beginning of procedure'');
96 END IF;
97
98 INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
99 event_id ,
100 period_close_date ,
101 reversal_flag ,
102 transfer_to_gl_flag ,
103 accounting_date )
104 SELECT ctlgd.event_id ,
105 dp.CALENDAR_PERIOD_CLOSE_DATE ,
106 ''Y'' ,
107 decode(bc.GL_POSTING_ALLOWED_FLAG ,
108 ''YES'', ''Y'',
109 ''N''),
110 dp.CALENDAR_PERIOD_CLOSE_DATE
111 FROM xla_events_gt ctlgd,
112 fa_book_controls bc,
113 fa_deprn_periods dp,
114 fa_deprn_events ds
115 WHERE ctlgd.entity_code = ''DEPRECIATION''
116 AND ctlgd.event_type_code = ''ROLLBACK_DEPRECIATION''
117 AND ds.asset_id = ctlgd.source_id_int_1
118 AND ds.book_type_code = ctlgd.source_id_char_1
119 AND ds.period_counter = ctlgd.source_id_int_2
120 AND ds.deprn_run_id = ctlgd.source_id_int_3
121 AND bc.book_type_code = ctlgd.source_id_char_1
122 -- AND ds.book_type_code = ctlgd.valuation_method
123 AND ds.reversal_event_id = ctlgd.event_id
124 AND dp.book_type_code = ds.book_type_code
125 AND dp.period_counter = ds.period_counter;
126
127
128 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
129 fnd_log.string(G_LEVEL_PROCEDURE,
130 G_MODULE_NAME||l_procedure_name||''.end'',
131 ''End of procedure'');
132 END IF;
133
134 EXCEPTION
135 WHEN others THEN
136 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
137 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
138 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
139 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
140 END IF;
141 raise;
142
143 END Load_header_data_rb ;
144
145 ';
146
147
148 --+==========================================================================+
149 --| |
150 --| Private global constants |
151 --| |
152 --+==========================================================================+
153
154 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
155 C_CREATED CONSTANT BOOLEAN := TRUE;
156
157 g_Max_line CONSTANT NUMBER := 225;
158 g_chr_quote CONSTANT VARCHAR2(10):='''';
159 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
160
161 g_log_level_rec fa_api_types.log_level_rec_type;
162
163 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
164
165 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
166 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
167 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
168 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
169 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
170 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
171
172 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_header_pkg.';
173
174
175 FUNCTION GenerateHeaderExtract
176 (p_extract_type IN VARCHAR2,
177 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
178
179 l_loop_total number := 1;
180 l_level varchar2(30);
181 l_extract_type varchar2(30);
182 l_proc_name varchar2(80);
183
184 l_array_pkg DBMS_SQL.VARCHAR2S;
185 l_BodyPkg VARCHAR2(32000);
186 l_array_body DBMS_SQL.VARCHAR2S;
187 l_procedure_name varchar2(80) := 'GenerateHeaderExtract';
188
189 invalid_mode EXCEPTION;
190
191 BEGIN
192
193 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
194 fnd_log.string(G_LEVEL_PROCEDURE,
195 G_MODULE_NAME||l_procedure_name||'.begin',
196 'Beginning of procedure');
197 END IF;
198
199 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
200 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
201
202
203 -- determine number of times to loop
204 if (p_extract_type = 'DEPRN' or
205 p_extract_type = 'DEF') then
206 l_loop_total := 1;
207 else
208 l_loop_total := 2;
209 end if;
210
211 for i in 1..l_loop_total loop
212
213 if (i = 1) then
214 if (p_extract_type = 'TRX') then
215 l_extract_type := 'TRX1';
216 l_proc_name := 'load_header_data_stg1';
217 else
218 l_extract_type := p_extract_type;
219 l_proc_name := 'load_header_data';
220 end if;
221 else -- 2
222 l_extract_type := 'TRX2';
223 l_proc_name := 'load_header_data_stg2';
224 end if;
225
226 l_bodypkg := C_PRIVATE_API_1;
227 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
228
229 if (i = 1) then
230 fa_cmp_string_pkg.CreateString
231 (p_package_text => l_BodyPkg
232 ,p_array_string => l_array_pkg);
233
234 else
235 fa_cmp_string_pkg.CreateString
236 (p_package_text => l_BodyPkg
237 ,p_array_string => l_array_body);
238
239 l_array_pkg :=
240 fa_cmp_string_pkg.ConcatTwoStrings
241 (p_array_string_1 => l_array_pkg
242 ,p_array_string_2 => l_array_body);
243 end if;
244
245 -- call main util to dynamically determine statements to handle sources
246 if not fa_xla_cmp_sources_pkg.GenerateSourcesExtract
247 (p_extract_type => l_extract_type,
248 p_level => 'HEADER',
249 p_package_body => l_array_body) then
250 raise invalid_mode;
251 end if;
252
253
254 l_array_pkg :=
255 fa_cmp_string_pkg.ConcatTwoStrings
256 (p_array_string_1 => l_array_pkg
257 ,p_array_string_2 => l_array_body);
258
259 -- add the debug for row counts
260 l_bodypkg := C_PRIVATE_API_2;
261
262 fa_cmp_string_pkg.CreateString
263 (p_package_text => l_BodyPkg
264 ,p_array_string => l_array_body);
265
266 l_array_pkg :=
267 fa_cmp_string_pkg.ConcatTwoStrings
268 (p_array_string_1 => l_array_pkg
269 ,p_array_string_2 => l_array_body);
270
271 -- add the closing
272 l_bodypkg := C_PRIVATE_API_3;
273 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
274
275 fa_cmp_string_pkg.CreateString
276 (p_package_text => l_BodyPkg
277 ,p_array_string => l_array_body);
278
279 l_array_pkg :=
280 fa_cmp_string_pkg.ConcatTwoStrings
281 (p_array_string_1 => l_array_pkg
282 ,p_array_string_2 => l_array_body);
283
284 end loop;
285
286 -- for rollback deprn, need one more routine in deprn package
287 if (p_extract_type = 'DEPRN') then
288
289 l_bodypkg := C_PRIVATE_ROLLBACK;
290
291 fa_cmp_string_pkg.CreateString
292 (p_package_text => l_BodyPkg
293 ,p_array_string => l_array_body);
294
295 l_array_pkg :=
296 fa_cmp_string_pkg.ConcatTwoStrings
297 (p_array_string_1 => l_array_pkg
298 ,p_array_string_2 => l_array_body);
299
300 end if;
301
302 p_package_body := l_array_pkg;
303
304 RETURN TRUE;
305
306 EXCEPTION
307 WHEN invalid_mode THEN
308 RETURN FALSE;
309
310 WHEN OTHERS THEN
311 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
312 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
313 fnd_message.set_token('ORACLE_ERR',SQLERRM);
314 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
315 END IF;
316 RETURN FALSE;
317
318 END GenerateHeaderExtract;
319
320 END fa_xla_cmp_header_pkg;