[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_STMT_SQL_LDR
Source
1 PACKAGE BODY CE_BANK_STMT_SQL_LDR AS
2 /* $Header: cesqldrb.pls 120.7 2006/06/06 00:33:19 bhchung ship $ */
3 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
4 --l_DEBUG varchar2(1) := 'Y';
5
6 /* 2421690
7 Start of Code Fix */
8
9 FUNCTION body_revision RETURN VARCHAR2 IS
10 BEGIN
11
12 RETURN '$Revision: 120.7 $';
13
14 END body_revision;
15
16 FUNCTION spec_revision RETURN VARCHAR2 IS
17 BEGIN
18
19 RETURN G_spec_revision;
20
21 END spec_revision;
22
23 /* End of Code Fix */
24
25 /* ---------------------------------------------------------------------
26 | PUBLIC PROCEDURE |
27 | Print_Report |
28 | |
29 | DESCRIPTION |
30 | This procedure submits a concurrent request to print the |
31 | Cash SQLLDR Report to print out NOCOPY the errors. |
32 | |
33 | CALLED BY |
34 | |
35 | HISTORY |
36 | 10-JUN-1999 Created Byung-Hyun Chung |
37 --------------------------------------------------------------------- */
38
39 PROCEDURE Print_Report(X_MAP_ID NUMBER,
40 X_DATA_FILE VARCHAR2) IS
41 req_id NUMBER;
42 request_id NUMBER;
43 reqid VARCHAR2(30);
44 number_of_copies NUMBER;
45 printer VARCHAR2(30);
46 print_style VARCHAR2(30);
47 save_output_flag VARCHAR2(30);
48 save_output_bool BOOLEAN;
49 BEGIN
50 IF l_DEBUG in ('Y', 'C') THEN
51 cep_standard.debug('>>CE_BANK_STMT_SQL_LDR.Print_Report');
52 END IF;
53 --
54 -- Get original request id
55 --
56 fnd_profile.get('CONC_REQUEST_ID', reqid);
57 request_id := to_number(reqid);
58 --
59 -- Get print options
60 --
61 IF l_DEBUG in ('Y', 'C') THEN
62 cep_standard.debug('Print_Report: ' || 'Request Id is ' || request_id);
63 END IF;
64 IF( NOT FND_CONCURRENT.GET_REQUEST_PRINT_OPTIONS(request_id,
65 number_of_copies,
66 print_style,
67 printer,
68 save_output_flag))THEN
69 IF l_DEBUG in ('Y', 'C') THEN
70 cep_standard.debug('Print_Report: ' || 'Message: get print options failed');
71 END IF;
72 ELSE
73 IF (save_output_flag = 'Y') THEN
74 save_output_bool := TRUE;
75 ELSE
76 save_output_bool := FALSE;
77 END IF;
78
79 IF( FND_CONCURRENT.GET_PROGRAM_ATTRIBUTES ('CE',
80 'CEBSLERR',
81 printer,
82 print_style,
83 save_output_flag)) THEN
84 IF l_DEBUG in ('Y', 'C') THEN
85 cep_standard.debug('Print_Report: ' || 'Message: get print options failed');
86 END IF;
87 END IF;
88
89 --
90 -- Set print options
91 --
92 IF (NOT FND_REQUEST.set_print_options(printer,
93 print_style,
94 number_of_copies,
95 save_output_bool)) THEN
96 IF l_DEBUG in ('Y', 'C') THEN
97 cep_standard.debug('Print_Report: ' || 'Set print options failed');
98 END IF;
99 END IF;
100 END IF;
101
102 req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
103 'CEBSLERR',
104 NULL,
105 to_date(to_char(sysdate,'YYYY/MM/DD'),'YYYY/MM/DD'),
106 FALSE,
107 'P_MAP_ID=' || to_char(X_MAP_ID),
108 'P_FILE_NAME=' || X_DATA_FILE);
109
110 IF l_DEBUG in ('Y', 'C') THEN
111 cep_standard.debug('<<CE_BANK_STMT_SQL_LDR.Print_Report');
112 END IF;
113 END Print_Report;
114
115 /* ---------------------------------------------------------------------
116 | PUBLIC PROCEDURE |
117 | Define_Conc_Program |
118 | |
119 | DESCRIPTION |
120 | Create executable and concurrent program definition when new |
121 | format is loaded for the first time. This is necessary |
122 | since executable and concurrent program has to be created per |
123 | control file. |
124 | CALLED BY |
125 | |
126 | HISTORY |
127 | 10-JUN-1999 Created Byung-Hyun Chung |
128 --------------------------------------------------------------------- */
129 FUNCTION Define_Conc_Program(X_ctl_file IN VARCHAR2) RETURN NUMBER IS
130 l_err NUMBER;
131 BEGIN
132 IF l_DEBUG in ('Y', 'C') THEN
133 cep_standard.debug('>>CE_BANK_STMT_SQL_LDR.Define_Conc_Program');
134 END IF;
135
136 --
137 -- Create Executable for the given control file.
138 --
139 FND_PROGRAM.executable('CESLREXC' || X_ctl_file, -- executable
140 'CE', -- application
141 'CESLREXC' || X_ctl_file, -- short name
142 'Executable for SQL*Loader ' || X_ctl_file, -- description
143 'SQL*Loader', -- execution method
144 X_ctl_file); -- execution file name
145
146
147 --
148 -- Create Concurrent Program Definition for the given control file.
149 --
150 FND_PROGRAM.register('Run SQL*Loader- ' || X_ctl_file, -- Program
151 'CE', -- application
152 'Y', -- enabled
153 'CESLRPRO'|| X_ctl_file, -- short name
154 'Program Definition for SQL*Loader ' || X_ctl_file, -- description
155 'CESLREXC' || X_ctl_file, -- executable name
156 'CE', -- executable application
157 NULL, -- execution option
158 NULL, -- priority
159 'Y', -- save output
160 'Y', -- print
161 NULL, -- cols
162 NULL, -- rows
163 NULL, -- style
164 'N', -- style required
165 NULL, -- printer
166 NULL, -- request type
167 NULL, -- request type application
168 'N', -- use in SRS
169 'N', -- allow diabled value
170 'N'); -- run alone
171
172 --
173 -- Create Concurrent Program Parameter Definition for the givel control file.
174 --
175 FND_PROGRAM.parameter('CESLRPRO'|| X_ctl_file, -- program short name
176 'CE', -- application
177 10, -- sequence
178 'Data File Name', -- parameter
179 'Data File Name', -- description
180 'Y', -- enabled
181 '50 chars', -- value set
182 NULL, -- default type
183 NULL, -- default value
184 'Y', -- required
185 'N', -- enable security
186 NULL, -- range
187 'Y', -- display
188 50, -- display size
189 50, -- description size
190 25, -- concatenated desc dize
191 'Data File Name', -- prompt
192 NULL); -- token
193
194 IF l_DEBUG in ('Y', 'C') THEN
195 cep_standard.debug('<<CE_BANK_STMT_SQL_LDR.Define_Conc_Program');
196 END IF;
197 RETURN 1;
198 EXCEPTION
199 WHEN OTHERS THEN
200 IF l_DEBUG in ('Y', 'C') THEN
201 cep_standard.debug('EXCEPTION: CE_BANK_STMT_SQL_LDR.Define_Conc_Program - '||
202 fnd_program.message() );
203 END IF;
204 --
205 -- Concurrent program definition already exist.
206 -- Still ok to run the program.
207 --
208 RETURN 1;
209 END Define_Conc_Program;
210
211 /* ---------------------------------------------------------------------
212 | PUBLIC PROCEDURE |
213 | Call_Sql_Loader |
214 | |
215 | DESCRIPTION |
216 | This procedure spwans SQL*Loader program and main loading |
217 | program. |
218 | |
219 | CALLED BY |
220 | |
221 | HISTORY |
222 | 10-JUN-1999 Created Byung-Hyun Chung |
223 --------------------------------------------------------------------- */
224 PROCEDURE Call_Sql_Loader(errbuf OUT NOCOPY VARCHAR2,
225 retcode OUT NOCOPY NUMBER,
226 X_process_option IN VARCHAR2,
227 X_loading_id IN NUMBER,
228 X_input_file IN VARCHAR2,
229 X_directory_path IN VARCHAR2,
230 X_bank_branch_id IN VARCHAR2,
231 X_bank_account_id IN VARCHAR2,
232 X_gl_date IN VARCHAR2,
233 X_org_id IN VARCHAR2,
234 X_receivables_trx_id IN NUMBER,
235 X_payment_method_id IN NUMBER,
236 X_nsf_handling IN VARCHAR2,
237 X_display_debug IN VARCHAR2,
238 X_debug_path IN VARCHAR2,
239 X_debug_file IN VARCHAR2,
240 X_gl_date_source IN VARCHAR2 DEFAULT NULL,
241 X_intra_day_flag IN VARCHAR2 DEFAULT 'N') IS
242
243 l_cnt NUMBER;
244 l_data_file VARCHAR2(80);
245 l_ctl_file VARCHAR2(30);
246 l_request_id NUMBER;
247 l_req_data VARCHAR(30);
248 G_conc_req_id NUMBER;
249
250 ldr_exception EXCEPTION;
251 BEGIN
252 IF l_DEBUG in ('Y', 'C') THEN
253 cep_standard.enable_debug(X_debug_path,
254 X_debug_file || '-CSL');
255 cep_standard.debug('>>CE_BANK_STMT_SQL_LDR.Call_Sql_Loader');
256 END IF;
257
258 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
259 CEP_STANDARD.init_security;
260
261 --
262 -- Reformat the input file name
263 --
264 IF (INSTR(X_directory_path, '\') <> 0 ) THEN
265 l_data_file := nvl(X_directory_path, '$CE_TOP\\bin') || '\\' || X_input_file;
266 ELSE
267 l_data_file := nvl(X_directory_path, '$CE_TOP/bin') || '/' || X_input_file;
268 END IF;
269
270 l_req_data := fnd_conc_global.request_data;
271
272 if(l_req_data IS NOT NULL)THEN
273 G_conc_req_id := to_number(l_req_data);
274 END IF;
275
276 --
277 -- Delete existing data in CE_SQLLDR_ERRORS table.
278 --
279 CE_SQLLDR_ERRORS_PKG.Delete_Row;
280
281 SELECT control_file_name
282 INTO l_ctl_file
283 FROM ce_bank_stmt_int_map
284 WHERE map_id = X_loading_id;
285
286 --
287 -- Reformat the control file name
288 --
289 l_ctl_file := replace(l_ctl_file, '.ctl');
290
291 IF l_DEBUG in ('Y', 'C') THEN
292 cep_standard.debug('Call_Sql_Loader: ' || 'Control file name: '|| l_ctl_file);
293 END IF;
294
295 --
296 -- If there is no concurrent program setup for the given control file then create one.
297 --
298 SELECT count(*)
299 INTO l_cnt
300 FROM fnd_concurrent_programs
301 WHERE application_id = 260
302 AND concurrent_program_name = 'CESLRPRO' || l_ctl_file;
303
304 IF l_DEBUG in ('Y', 'C') THEN
305 cep_standard.debug('l_cnt = ' || l_cnt ||
306 ', l_data_file = ' ||l_data_file );
307 END IF;
308
309 IF (l_cnt = 0 ) THEN
310 IF (Define_Conc_Program(l_ctl_file) <> 1) THEN
311 RAISE ldr_exception;
312 END IF;
313 END IF;
314
315 --
316 -- Submit request to execute SQL*Loader.
317 --
318 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
319 'CE','CESLRPRO'|| l_ctl_file,'','',NULL,
320 l_data_file, fnd_global.local_chr(0),
321 '','','','','','','','','','',
322 '','','','','','','','','','',
323 '','','','','','','','','','',
324 '','','','','','','','','','',
325 '','','','','','','','','','',
326 '','','','','','','','','','',
327 '','','','','','','','','','',
328 '','','','','','','','','','',
329 '','','','','','','','');
330
331 IF l_request_id = 0 THEN
332 IF l_DEBUG in ('Y', 'C') THEN
333 cep_standard.debug('Call_Sql_Loader: ' || 'EXCEPTION: Fail to submit cuncurrent request for SQL*Loader');
334 END IF;
335 RAISE ldr_exception;
336 END IF;
337
338 --
339 -- Submit request to execute actual loading program.
340 -- This concurrent program will transfer data from temporart table to interface tables.
341 --
342 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
343 'CE','CEBSLDR','','',NULL,
344 X_loading_id,
345 l_request_id,
346 l_data_file,
347 NVL(X_process_option, 'LOAD'),
348 X_gl_date,
349 X_org_id,
350 X_receivables_trx_id,
351 X_payment_method_id,
352 X_nsf_handling,
353 X_display_debug,
354 X_debug_path,
355 X_debug_file,
356 X_bank_branch_id,
357 X_bank_account_id,
358 X_intra_day_flag,
359 X_gl_date_source,
360 fnd_global.local_chr(0),
361 '','','','','','','','','','',
362 '','','','','','','','','','',
363 '','','','','','','','','','',
364 '','','','','','','','','','',
365 '','','','','','','','','','',
366 '','','','','','','','','','',
367 '','','','','','','','','','',
368 '','','');
369
370 IF l_request_id = 0 THEN
371 IF l_DEBUG in ('Y', 'C') THEN
372 cep_standard.debug('Call_Sql_Loader: ' || 'EXCEPTION: Fail to submit cuncurrent request for '|| 'CEBSLDR');
373 END IF;
374 RAISE ldr_exception;
375 END IF;
376
377 IF l_DEBUG in ('Y', 'C') THEN
378 cep_standard.debug('<<CE_BANK_STMT_SQL_LDR.Call_Sql_Loader');
379 cep_standard.disable_debug(X_display_debug);
380 END IF;
381
382 EXCEPTION
383 WHEN ldr_exception THEN
384 RAISE;
385 WHEN OTHERS THEN
386 IF l_DEBUG in ('Y', 'C') THEN
387 cep_standard.debug('EXCEPTION: CE_BANK_STMT_SQL_LDR.Call_Sql_Loader');
388 END IF;
389 RAISE;
390 END Call_Sql_Loader;
391
392 END CE_BANK_STMT_SQL_LDR;