[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FA_XLA_CMP_HEADER_PKG
Source
1 PACKAGE BODY fa_xla_cmp_header_pkg AS
2 /* $Header: faxlachb.pls 120.10.12020000.2 2012/07/19 12:37:56 dvjoshi 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_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
56 fnd_log.string(G_LEVEL_PROCEDURE,
57 G_MODULE_NAME||l_procedure_name||''.end'',
58 ''End of procedure'');
59 END IF;
60
61 EXCEPTION
62 WHEN others THEN
63 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
64 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
65 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
66 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
67 END IF;
68 raise;
69
70 end $proc_name$;
71
72 ';
73
74 C_PRIVATE_ROLLBACK CONSTANT VARCHAR2(32000) := '
75
76 PROCEDURE Load_header_data_rb IS
77
78 l_procedure_name varchar2(80) := ''load_header_data_rb'';
79
80 BEGIN
81
82 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
83 fnd_log.string(G_LEVEL_PROCEDURE,
84 G_MODULE_NAME||l_procedure_name||''.begin'',
85 ''Beginning of procedure'');
86 END IF;
87
88 if (NOT fa_xla_extract_util_pkg.G_secondary_special) then
89
90 INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
91 event_id ,
92 period_close_date ,
93 reversal_flag ,
94 transfer_to_gl_flag ,
95 accounting_date )
96 SELECT ctlgd.event_id ,
97 dp.CALENDAR_PERIOD_CLOSE_DATE ,
98 ''Y'' ,
99 decode(bc.GL_POSTING_ALLOWED_FLAG ,
100 ''YES'', ''Y'',
101 ''N''),
102 ctlgd.event_date
103 FROM xla_events_gt ctlgd,
104 fa_book_controls bc,
105 fa_deprn_periods dp,
106 fa_deprn_events ds
107 WHERE ctlgd.entity_code = ''DEPRECIATION''
108 AND ctlgd.event_type_code = ''ROLLBACK_DEPRECIATION''
109 AND ds.asset_id = ctlgd.source_id_int_1
110 AND ds.book_type_code = ctlgd.source_id_char_1
111 AND ds.period_counter = ctlgd.source_id_int_2
112 AND ds.deprn_run_id = ctlgd.source_id_int_3
113 AND bc.book_type_code = ctlgd.source_id_char_1
114 -- AND ds.book_type_code = ctlgd.valuation_method
115 AND ds.reversal_event_id = ctlgd.event_id
116 AND dp.book_type_code = ds.book_type_code
117 AND dp.period_counter = ds.period_counter;
118
119 end if;
120
121 IF (fa_xla_extract_util_pkg.G_secondary_special) then
122
123 INSERT INTO FA_XLA_EXT_HEADERS_B_GT (
124 event_id ,
125 period_close_date ,
126 reversal_flag ,
127 transfer_to_gl_flag ,
128 accounting_date )
129 SELECT ctlgd.event_id ,
130 dp.CALENDAR_PERIOD_CLOSE_DATE ,
131 ''Y'' ,
132 decode(bc.GL_POSTING_ALLOWED_FLAG ,
133 ''YES'', ''Y'',
134 ''N''),
135 ctlgd.event_date
136 FROM xla_events_gt ctlgd,
137 fa_mc_book_controls bc,
138 fa_deprn_periods dp,
139 fa_mc_deprn_Summary_h ds
140 WHERE ctlgd.entity_code = ''DEPRECIATION''
141 AND ctlgd.event_type_code = ''ROLLBACK_DEPRECIATION''
142 AND ds.asset_id = ctlgd.source_id_int_1
143 AND ds.book_type_code = ctlgd.source_id_char_1
144 AND ds.period_counter = ctlgd.source_id_int_2
145 AND ds.deprn_run_id = ctlgd.source_id_int_3
146 AND bc.book_type_code = ctlgd.source_id_char_1
147 AND bc.set_of_books_id = ctlgd.ledger_id
148 -- AND ds.book_type_code = ctlgd.valuation_method
149 AND ds.reversal_event_id = ctlgd.event_id
150 AND dp.book_type_code = ds.book_type_code
151 AND dp.period_counter = ds.period_counter;
152
153 END IF; -- special secondary condition
154
155 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
156 fnd_log.string(G_LEVEL_PROCEDURE,
157 G_MODULE_NAME||l_procedure_name||''.end'',
158 ''End of procedure'');
159 END IF;
160
161 EXCEPTION
162 WHEN others THEN
163 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
164 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
165 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
166 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
167 END IF;
168 raise;
169
170 END Load_header_data_rb ;
171
172 ';
173
174
175 --+==========================================================================+
176 --| |
177 --| Private global constants |
178 --| |
179 --+==========================================================================+
180
181 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
182 C_CREATED CONSTANT BOOLEAN := TRUE;
183
184 g_Max_line CONSTANT NUMBER := 225;
185 g_chr_quote CONSTANT VARCHAR2(10):='''';
186 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
187
188 g_log_level_rec fa_api_types.log_level_rec_type;
189
190 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
191
192 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
193 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
194 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
195 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
196 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
197 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
198
199 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_header_pkg.';
200
201
202 FUNCTION GenerateHeaderExtract
203 (p_extract_type IN VARCHAR2,
204 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
205
206 l_loop_total number := 1;
207 l_level varchar2(30);
208 l_extract_type varchar2(30);
209 l_proc_name varchar2(80);
210
211 l_array_pkg DBMS_SQL.VARCHAR2S;
212 l_BodyPkg VARCHAR2(32000);
213 l_array_body DBMS_SQL.VARCHAR2S;
214 l_procedure_name varchar2(80) := 'GenerateHeaderExtract';
215
216 invalid_mode EXCEPTION;
217
218 BEGIN
219
220 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
221 fnd_log.string(G_LEVEL_PROCEDURE,
222 G_MODULE_NAME||l_procedure_name||'.begin',
223 'Beginning of procedure');
224 END IF;
225
226 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
227 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
228
229
230 -- determine number of times to loop
231 if (p_extract_type = 'DEPRN' or
232 p_extract_type = 'DEF') then
233 l_loop_total := 1;
234 else
235 l_loop_total := 2;
236 end if;
237
238 for i in 1..l_loop_total loop
239
240 if (i = 1) then
241 if (p_extract_type = 'TRX') then
242 l_extract_type := 'TRX1';
243 l_proc_name := 'load_header_data_stg1';
244 else
245 l_extract_type := p_extract_type;
246 l_proc_name := 'load_header_data';
247 end if;
248 else -- 2
249 l_extract_type := 'TRX2';
250 l_proc_name := 'load_header_data_stg2';
251 end if;
252
253 l_bodypkg := C_PRIVATE_API_1;
254 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
255
256 if (i = 1) then
257 fa_cmp_string_pkg.CreateString
258 (p_package_text => l_BodyPkg
259 ,p_array_string => l_array_pkg);
260
261 else
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 end if;
271
272 -- call main util to dynamically determine statements to handle sources
273 if not fa_xla_cmp_sources_pkg.GenerateSourcesExtract
274 (p_extract_type => l_extract_type,
275 p_level => 'HEADER',
276 p_package_body => l_array_body) then
277 raise invalid_mode;
278 end if;
279
280
281 l_array_pkg :=
282 fa_cmp_string_pkg.ConcatTwoStrings
283 (p_array_string_1 => l_array_pkg
284 ,p_array_string_2 => l_array_body);
285
286 -- add the closing
287 l_bodypkg := C_PRIVATE_API_2;
288 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
289
290 fa_cmp_string_pkg.CreateString
291 (p_package_text => l_BodyPkg
292 ,p_array_string => l_array_body);
293
294 l_array_pkg :=
295 fa_cmp_string_pkg.ConcatTwoStrings
296 (p_array_string_1 => l_array_pkg
297 ,p_array_string_2 => l_array_body);
298
299 end loop;
300
301 -- for rollback deprn, need one more routine in deprn package
302 if (p_extract_type = 'DEPRN') then
303
304 l_bodypkg := C_PRIVATE_ROLLBACK;
305
306 fa_cmp_string_pkg.CreateString
307 (p_package_text => l_BodyPkg
308 ,p_array_string => l_array_body);
309
310 l_array_pkg :=
311 fa_cmp_string_pkg.ConcatTwoStrings
312 (p_array_string_1 => l_array_pkg
313 ,p_array_string_2 => l_array_body);
314
315 end if;
316
317 p_package_body := l_array_pkg;
318
319 RETURN TRUE;
320
321 EXCEPTION
322 WHEN invalid_mode THEN
323 RETURN FALSE;
324
325 WHEN OTHERS THEN
326 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
327 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
328 fnd_message.set_token('ORACLE_ERR',SQLERRM);
329 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
330 END IF;
331 RETURN FALSE;
332
333 END GenerateHeaderExtract;
334
335 END fa_xla_cmp_header_pkg;