[Home] [Help]
PACKAGE BODY: APPS.FA_XLA_CMP_LINE_PKG
Source
1 PACKAGE BODY fa_xla_cmp_line_pkg AS
2 /* $Header: faxlaclb.pls 120.2 2006/03/17 22:49:01 bridgway noship $ */
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_line_pkg |
10 | |
11 | DESCRIPTION |
12 | This is a FA private package, which contains all the APIs required |
13 | for to create line 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 C_PRIVATE_API_2 CONSTANT VARCHAR2(32000) := '
53
54 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
55 fnd_log.string(G_LEVEL_PROCEDURE,
56 G_MODULE_NAME||l_procedure_name,
57 ''Rows inserted into lines: '' || to_char(SQL%ROWCOUNT));
58 END IF;
59
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
85
86
87 --+==========================================================================+
88 --| |
89 --| Private global constants |
90 --| |
91 --+==========================================================================+
92
93 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
94 C_CREATED CONSTANT BOOLEAN := TRUE;
95
96 g_Max_line CONSTANT NUMBER := 225;
97 g_chr_quote CONSTANT VARCHAR2(10):='''';
98 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
99
100 g_log_level_rec fa_api_types.log_level_rec_type;
101
102 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
103
104 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
105 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
106 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
107 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
108 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
109 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
110
111 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_line_pkg.';
112
113
114 FUNCTION GenerateLineExtract
115 (p_extract_type IN VARCHAR2,
116 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
117
118 l_loop_total number := 1;
119 l_level varchar2(30);
120 l_extract_type varchar2(30);
121 l_proc_name varchar2(80);
122
123 l_array_pkg DBMS_SQL.VARCHAR2S;
124 l_BodyPkg VARCHAR2(32000);
125 l_array_body DBMS_SQL.VARCHAR2S;
126 l_procedure_name varchar2(80) := 'GenerateLineExtract';
127
128 invalid_mode EXCEPTION;
129
130 BEGIN
131
132 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
133 fnd_log.string(G_LEVEL_PROCEDURE,
134 G_MODULE_NAME||l_procedure_name||'.begin',
135 'Beginning of procedure');
136 END IF;
137
138 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
139 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
140
141
142 -- determine number of times to loop
143 if (p_extract_type = 'DEPRN' or
144 p_extract_type = 'DEF') then
145
146 l_loop_total := 1;
147 else
148 l_loop_total := 10;
149 end if;
150
151 for i in 1..l_loop_total loop
152
153 if (i = 1) then
154 if (p_extract_type = 'TRX') then
155 l_extract_type := 'TRX1';
156 l_level := 'STG';
157 l_proc_name := 'load_line_data_stg1';
158 else
159 l_extract_type := p_extract_type;
160 l_level := 'LINE';
161 l_proc_name := 'load_line_data';
162 end if;
163
164 elsif (i = 2) then
165 l_extract_type := 'TRX2';
166 l_level := 'STG';
167 l_proc_name := 'load_line_data_stg2';
168
169 elsif (i = 3) then
170 l_extract_type := 'TRX3';
171 l_level := 'STG';
172 l_proc_name := 'load_line_data_stg2';
173
174 else
175 l_level := 'LINE';
176
177 if (i=4) then
178 l_extract_type := 'FIN1';
179 l_proc_name := 'load_line_data_fin1';
180 elsif (i=5) then
181 l_extract_type := 'FIN2';
182 l_proc_name := 'load_line_data_fin2';
183 elsif (i=6) then
184 l_extract_type := 'XFR';
185 l_proc_name := 'load_line_data_xfr';
186 elsif (i=7) then
187 l_extract_type := 'DIST1';
188 l_proc_name := 'load_line_data_dist';
189 elsif (i=8) then
190 l_extract_type := 'DIST2';
191 l_proc_name := 'load_line_data_dist';
192 elsif (i=9) then
193 l_extract_type := 'RET';
194 l_proc_name := 'load_line_data_ret';
195 elsif (i=10) then
196 l_extract_type := 'RES';
197 l_proc_name := 'load_line_data_res';
198 else
199 raise invalid_mode;
200 end if;
201
202 end if;
203
204
205 -- note that we have one corner case where DIST1/DIST2
206 -- role into the same procedure - needs to be accounted
207 -- for in the building of the procsa
208 --
209 -- same for inter asset splitting main vs group
210
211 if (i <> 8 and i <> 3) then
212
213 l_bodypkg := C_PRIVATE_API_1;
214 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
215
216 if (i = 1) then
217 fa_cmp_string_pkg.CreateString
218 (p_package_text => l_BodyPkg
219 ,p_array_string => l_array_pkg);
220
221 else
222 fa_cmp_string_pkg.CreateString
223 (p_package_text => l_BodyPkg
224 ,p_array_string => l_array_body);
225
226 l_array_pkg :=
227 fa_cmp_string_pkg.ConcatTwoStrings
228 (p_array_string_1 => l_array_pkg
229 ,p_array_string_2 => l_array_body);
230 end if;
231 end if;
232
233 -- call main util to dynamically determine statements to handle sources
234 if not fa_xla_cmp_sources_pkg.GenerateSourcesExtract
235 (p_extract_type => l_extract_type,
236 p_level => l_level,
237 p_package_body => l_array_body) then
238 raise invalid_mode;
239 end if;
240
241 l_array_pkg :=
242 fa_cmp_string_pkg.ConcatTwoStrings
243 (p_array_string_1 => l_array_pkg
244 ,p_array_string_2 => l_array_body);
245
246 -- add the debug for row counts
247 l_bodypkg := C_PRIVATE_API_2;
248
249 fa_cmp_string_pkg.CreateString
250 (p_package_text => l_BodyPkg
251 ,p_array_string => l_array_body);
252
253 l_array_pkg :=
254 fa_cmp_string_pkg.ConcatTwoStrings
255 (p_array_string_1 => l_array_pkg
256 ,p_array_string_2 => l_array_body);
257
258 if (i <> 7 and i <> 2) then -- not the first of two part proc
259 -- add the closing
260 l_bodypkg := C_PRIVATE_API_3;
261 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
262
263 fa_cmp_string_pkg.CreateString
264 (p_package_text => l_BodyPkg
265 ,p_array_string => l_array_body);
266
267 l_array_pkg :=
268 fa_cmp_string_pkg.ConcatTwoStrings
269 (p_array_string_1 => l_array_pkg
270 ,p_array_string_2 => l_array_body);
271
272 end if;
273
274 end loop;
275
276 p_package_body := l_array_pkg;
277
278 RETURN TRUE;
279
280 EXCEPTION
281 WHEN invalid_mode THEN
282 RETURN FALSE;
283
284 WHEN OTHERS THEN
285 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
286 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
287 fnd_message.set_token('ORACLE_ERR',SQLERRM);
288 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
289 END IF;
290 RETURN FALSE;
291
292 END GenerateLineExtract;
293
294 END fa_xla_cmp_line_pkg;