[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.4.12020000.2 2012/07/19 12:39:36 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_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 l_secondary number := 0;
42
43 BEGIN
44
45 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
46 fnd_log.string(G_LEVEL_PROCEDURE,
47 G_MODULE_NAME||l_procedure_name||''.begin'',
48 ''Beginning of procedure'');
49 END IF;
50
51 IF (fa_xla_extract_util_pkg.G_secondary_special) THEN
52 l_secondary := 1;
53 END IF;
54
55 ';
56
57 C_PRIVATE_API_2 CONSTANT VARCHAR2(32000) := '
58
59 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
60 fnd_log.string(G_LEVEL_PROCEDURE,
61 G_MODULE_NAME||l_procedure_name||''.end'',
62 ''End of procedure'');
63 END IF;
64
65 EXCEPTION
66 WHEN others THEN
67 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
68 fnd_message.set_name(''OFA'',''FA_SHARED_ORACLE_ERR'');
69 fnd_message.set_token(''ORACLE_ERR'',SQLERRM);
70 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
71 END IF;
72 raise;
73
74 end $proc_name$;
75
76 ';
77
78
79
80
81 --+==========================================================================+
82 --| |
83 --| Private global constants |
84 --| |
85 --+==========================================================================+
86
87 C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
88 C_CREATED CONSTANT BOOLEAN := TRUE;
89
90 g_Max_line CONSTANT NUMBER := 225;
91 g_chr_quote CONSTANT VARCHAR2(10):='''';
92 g_chr_newline CONSTANT VARCHAR2(10):= fa_cmp_string_pkg.g_chr_newline;
93
94 g_log_level_rec fa_api_types.log_level_rec_type;
95
96 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
97
98 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
99 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
100 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
101 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
102 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
103 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
104
105 G_MODULE_NAME CONSTANT VARCHAR2(50):= 'fa.plsql.fa_xla_cmp_line_pkg.';
106
107
108 FUNCTION GenerateLineExtract
109 (p_extract_type IN VARCHAR2,
110 p_package_body OUT NOCOPY DBMS_SQL.VARCHAR2S) RETURN BOOLEAN IS
111
112 l_loop_total number := 1;
113 l_level varchar2(30);
114 l_extract_type varchar2(30);
115 l_proc_name varchar2(80);
116
117 l_array_pkg DBMS_SQL.VARCHAR2S;
118 l_BodyPkg VARCHAR2(32000);
119 l_array_body DBMS_SQL.VARCHAR2S;
120 l_procedure_name varchar2(80) := 'GenerateLineExtract';
121
122 invalid_mode EXCEPTION;
123
124 BEGIN
125
126 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
127 fnd_log.string(G_LEVEL_PROCEDURE,
128 G_MODULE_NAME||l_procedure_name||'.begin',
129 'Beginning of procedure');
130 END IF;
131
132 l_array_body := fa_cmp_string_pkg.g_null_varchar2s;
133 l_array_pkg := fa_cmp_string_pkg.g_null_varchar2s;
134
135
136 -- determine number of times to loop
137 if (p_extract_type = 'DEPRN' or
138 p_extract_type = 'DEF') then
139
140 l_loop_total := 1;
141 else
142 l_loop_total := 9;
143 end if;
144
145 for i in 1..l_loop_total loop
146
147 if (i = 1) then
148 if (p_extract_type = 'TRX') then
149 l_extract_type := 'TRX1';
150 l_level := 'STG';
151 l_proc_name := 'load_line_data_stg1';
152 else
153 l_extract_type := p_extract_type;
154 l_level := 'LINE';
155 l_proc_name := 'load_line_data';
156 end if;
157
158 elsif (i = 2) then
159 l_extract_type := 'TRX2';
160 l_level := 'STG';
161 l_proc_name := 'load_line_data_stg2';
162
163 else
164 l_level := 'LINE';
165
166 if (i=3) then
167 l_extract_type := 'FIN1';
168 l_proc_name := 'load_line_data_fin1';
169 elsif (i=4) then
170 l_extract_type := 'FIN2';
171 l_proc_name := 'load_line_data_fin2';
172 elsif (i=5) then
173 l_extract_type := 'XFR';
174 l_proc_name := 'load_line_data_xfr';
175 elsif (i=6) then
176 l_extract_type := 'DIST1';
177 l_proc_name := 'load_line_data_dist';
178 elsif (i=7) then
179 l_extract_type := 'DIST2';
180 l_proc_name := 'load_line_data_dist';
181 elsif (i=8) then
182 l_extract_type := 'RET';
183 l_proc_name := 'load_line_data_ret';
184 elsif (i=9) then
185 l_extract_type := 'RES';
186 l_proc_name := 'load_line_data_res';
187 else
188 raise invalid_mode;
189 end if;
190
191 end if;
192
193
194 -- note that we have one corner case where DIST1/DIST2
195 -- role into the same procedure - needs to be accounted
196 -- for in the building of the procsa
197 --
198 -- same for inter asset splitting main vs group (no longer true - 3 is no longer used)
199
200 if (i <> 7) then
201
202 l_bodypkg := C_PRIVATE_API_1;
203 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
204
205 if (i = 1) then
206 fa_cmp_string_pkg.CreateString
207 (p_package_text => l_BodyPkg
208 ,p_array_string => l_array_pkg);
209
210 else
211 fa_cmp_string_pkg.CreateString
212 (p_package_text => l_BodyPkg
213 ,p_array_string => l_array_body);
214
215 l_array_pkg :=
216 fa_cmp_string_pkg.ConcatTwoStrings
217 (p_array_string_1 => l_array_pkg
218 ,p_array_string_2 => l_array_body);
219 end if;
220 end if;
221
222 -- call main util to dynamically determine statements to handle sources
223 if not fa_xla_cmp_sources_pkg.GenerateSourcesExtract
224 (p_extract_type => l_extract_type,
225 p_level => l_level,
226 p_package_body => l_array_body) then
227 raise invalid_mode;
228 end if;
229
230 l_array_pkg :=
231 fa_cmp_string_pkg.ConcatTwoStrings
232 (p_array_string_1 => l_array_pkg
233 ,p_array_string_2 => l_array_body);
234
235
236 if (i <> 6) then -- not the first of two part proc
237 -- add the closing
238 l_bodypkg := C_PRIVATE_API_2;
239 l_bodypkg := REPLACE(l_bodypkg,'$proc_name$',l_proc_name);
240
241 fa_cmp_string_pkg.CreateString
242 (p_package_text => l_BodyPkg
243 ,p_array_string => l_array_body);
244
245 l_array_pkg :=
246 fa_cmp_string_pkg.ConcatTwoStrings
247 (p_array_string_1 => l_array_pkg
248 ,p_array_string_2 => l_array_body);
249
250 end if;
251
252 end loop;
253
254 p_package_body := l_array_pkg;
255
256 RETURN TRUE;
257
258 EXCEPTION
259 WHEN invalid_mode THEN
260 RETURN FALSE;
261
262 WHEN OTHERS THEN
263 IF (G_LEVEL_UNEXPECTED >= G_CURRENT_RUNTIME_LEVEL ) THEN
264 fnd_message.set_name('OFA','FA_SHARED_ORACLE_ERR');
265 fnd_message.set_token('ORACLE_ERR',SQLERRM);
266 FND_LOG.MESSAGE (G_LEVEL_UNEXPECTED,G_MODULE_NAME||l_procedure_name,TRUE);
267 END IF;
268 RETURN FALSE;
269
270 END GenerateLineExtract;
271
272 END fa_xla_cmp_line_pkg;