[Home] [Help]
PACKAGE BODY: APPS.GL_ADD_RECON_UPGRADE_PKG
Source
1 PACKAGE BODY GL_ADD_RECON_UPGRADE_PKG AS
2 /* $Header: glurcnub.pls 120.2 2006/02/08 23:32:40 vtreiger noship $ */
3 --
4 -- PRIVATE GLOBAL VARIABLES
5 --
6 g_api CONSTANT VARCHAR2(40) := 'gl.plsql.GL_ADD_RECON_UPGRADE_PKG';
7 g_gl_je_lines_table CONSTANT VARCHAR2(30) := 'GL_JE_LINES';
8 g_table_name CONSTANT VARCHAR2(30) := 'GL_CODE_COMBINATIONS';
9 g_id_column CONSTANT VARCHAR2(30) := 'CODE_COMBINATION_ID';
10 g_script_name CONSTANT VARCHAR2(30) := 'glurcnub.pls';
11 --
12 g_std_ins CONSTANT VARCHAR2(1500) :=
13 'INSERT INTO gl_je_lines_recon ' ||
14 '(je_header_id,je_line_num,ledger_id,' ||
15 'jgzz_recon_status,jgzz_recon_date,' ||
16 'jgzz_recon_id,jgzz_recon_ref,' ||
17 'last_update_date,last_updated_by,' ||
18 'creation_date,created_by,last_update_login) ' ||
19 'SELECT /*+ ORDERED INDEX(c gl_code_combinations_u1) */ ' ||
20 'l.je_header_id,l.je_line_num,l.ledger_id, ' ||
21 'nvl(l.jgzz_recon_status_11i,' || '''' || 'U' || '''' || ')' ||
22 ',l.jgzz_recon_date_11i, ' ||
23 'l.jgzz_recon_id_11i,l.jgzz_recon_ref_11i, ' ||
24 'sysdate, -2, sysdate, -2, 1 ' ||
25 'FROM gl_code_combinations c, gl_je_lines l ' ||
26 ' WHERE c.code_combination_id between :start_id and :end_id ' ||
27 'AND c.code_combination_id = l.code_combination_id ' ||
28 'AND c.jgzz_recon_flag = ' || '''' || 'Y' || '''' ||
29 ' AND NOT EXISTS ' ||
30 '(SELECT /*+ ORDERED INDEX(r gl_je_lines_recon_u1) */ 1 ' ||
31 'FROM gl_je_lines_recon r ' ||
32 'WHERE r.je_header_id = l.je_header_id ' ||
33 'AND r.je_line_num = l.je_line_num) ';
34 --
35 -- PRIVATE FUNCTIONS
36 --
37 --
38 -- Function
39 -- prepare_recon_update
40 -- Purpose
41 -- Insert data into gl_je_lines_recon.
42 -- History
43 -- 08/26/2005 V Treiger Created
44 -- Arguments
45 -- x_start_id Start id for AD parallel range, gl_je_lines_recon upgrade only
46 -- x_end_id End id for AD parallel range, gl_je_lines_recon upgrade only
47 --
48 FUNCTION prepare_recon_update(x_start_id NUMBER DEFAULT NULL,
49 x_end_id NUMBER DEFAULT NULL)
50 RETURN NUMBER
51 IS
52 --
53 l_rows NUMBER := 0;
54 fn_name CONSTANT VARCHAR2(30) := 'PREPARE_RECON_UPDATE';
55 --
56 BEGIN
57 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
58 g_api || '.' || fn_name);
59 -- parameters
60 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
61 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
62 g_api || '.' || fn_name,
63 'x_src_table = ' || 'gl_je_lines');
64 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
65 g_api || '.' || fn_name,
66 'x_start_id = ' || x_start_id);
67 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
68 g_api || '.' || fn_name,
69 'x_end_id = ' || x_end_id);
70 END IF;
71 --
72 EXECUTE IMMEDIATE g_std_ins USING x_start_id, x_end_id;
73 l_rows := SQL%ROWCOUNT;
74 --
75 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
76 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
77 g_api || '.' || fn_name);
78 END IF;
79 RETURN l_rows;
80 EXCEPTION
81 WHEN OTHERS THEN
82 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
83 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
84 g_api || '.' || fn_name,
85 SUBSTR(SQLERRM, 1, 4000));
86 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
87 g_api || '.' || fn_name);
88 END IF;
89 RAISE;
90 RETURN l_rows;
91 END prepare_recon_update;
92 --
93 --
94 -- PUBLIC FUNCTIONS
95 --
96 PROCEDURE upgrade_recon(
97 x_errbuf OUT NOCOPY VARCHAR2,
98 x_retcode OUT NOCOPY VARCHAR2,
99 x_batch_size NUMBER,
100 x_num_workers NUMBER) IS
101 fn_name CONSTANT VARCHAR2(30) := 'UPGRADE_RECON';
102 SUBMIT_REQ_ERROR EXCEPTION;
103 --
104 l_req_data VARCHAR2(10);
105 l_req_id NUMBER;
106 --
107 l_retstatus BOOLEAN;
108 l_status VARCHAR2(30);
109 l_industry VARCHAR2(30);
110 l_table_owner VARCHAR2(30);
111 l_gl_schema VARCHAR2(30);
112 l_applsys_schema VARCHAR2(30);
113 BEGIN
114 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
115 g_api || '.' || fn_name);
116 -- parameters
117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
118 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
119 g_api || '.' || fn_name,
120 'x_batch_size = ' || x_batch_size);
121 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
122 g_api || '.' || fn_name,
123 'x_num_workers = ' || x_num_workers);
124 END IF;
125 -- AD_CONC_UTILS_PKG.submit_subrequests sets request data
126 l_req_data := FND_CONC_GLOBAL.request_data;
127 --
128 IF (l_req_data IS NULL) THEN -- First time
129 -- get schema name for GL and FND
130 l_retstatus := fnd_installation.get_app_info(
131 'SQLGL', l_status, l_industry, l_gl_schema);
132 IF ( (NOT l_retstatus)
133 OR (l_gl_schema is null)) THEN
134 raise_application_error(-20001,
135 'Cannot get schema name for product : SQLGL');
136 END IF;
137 --
138 l_retstatus := fnd_installation.get_app_info(
139 'FND', l_status, l_industry, l_applsys_schema);
140 IF ( (NOT l_retstatus)
141 OR (l_applsys_schema is null)) THEN
142 raise_application_error(-20001,
143 'Cannot get schema name for product : FND');
144 END IF;
145 --
146 -- Clean up AD update information in case number of workers changed
147 -- Note: this procedure implicitly commits
148 AD_PARALLEL_UPDATES_PKG.delete_update_information(
149 ad_parallel_updates_pkg.ID_RANGE,
150 l_gl_schema,
151 g_table_name,
152 g_script_name);
153 --
154 -- Submit child requests to update gl_je_lines_recon
155 AD_CONC_UTILS_PKG.submit_subrequests(
156 X_errbuf => x_errbuf,
157 X_retcode => x_retcode,
158 X_workerconc_app_shortname => 'SQLGL',
159 X_workerconc_progname => 'GLRCNINS',
160 X_batch_size => x_batch_size,
161 X_num_workers => x_num_workers,
162 X_argument4 => l_gl_schema);
163 --
164 -- If the request data hasn't been set, then the AD API did not
165 -- successfully submit all child requests.
166 l_req_data := FND_CONC_GLOBAL.request_data;
167 IF (l_req_data IS NULL) THEN
168 RAISE SUBMIT_REQ_ERROR;
169 END IF;
170 --
171 ELSE -- Restart case
172 -- check status of all subrequests ( since
173 -- the program is not really used for a restart)
174 -- * If we want to produce an execution report, it may be more effecient
175 -- not to use the API since that would mean we are getting
176 -- sub-requests and loop through them twice.
177 --
178 AD_CONC_UTILS_PKG.submit_subrequests(
179 X_errbuf => x_errbuf,
180 X_retcode => x_retcode,
181 -- for restart, the rest of the parameters are not really used
182 X_workerconc_app_shortname => 'SQLGL',
183 X_workerconc_progname => 'GLRCNINS',
184 X_batch_size => x_batch_size,
185 X_num_workers => x_num_workers,
186 X_argument4 => l_gl_schema);
187 --
188 IF (x_retcode = AD_CONC_UTILS_PKG.CONC_SUCCESS) THEN
189 UPDATE GL_SYSTEM_USAGES
190 SET reconciliation_upg_flag = 'Y',
191 last_update_date = sysdate,
192 last_updated_by = 1,
193 last_update_login = 0;
194 END IF;
195 --
196 END IF;
197 --
198 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
199 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
200 g_api || '.' || fn_name);
201 END IF;
202 --
203 EXCEPTION
204 WHEN SUBMIT_REQ_ERROR THEN
205 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
206 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
207 GL_MESSAGE.WRITE_LOG(msg_name => 'SHRD0055',
208 token_num => 1,
209 t1 => 'ROUTINE',
210 v1 => fn_name,
211 log_level => FND_LOG.LEVEL_PROCEDURE,
212 module => g_api || '.' || fn_name);
213 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
214 g_api || '.' || fn_name);
215 END IF;
216 RAISE;
217 WHEN OTHERS THEN
218 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
219 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
220 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
221 g_api || '.' || fn_name,
222 SUBSTR(SQLERRM, 1, 4000));
223 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
224 g_api || '.' || fn_name);
225 END IF;
226 RAISE;
227 END upgrade_recon;
228 --
229 PROCEDURE update_gl_je_lines_recon_table(
230 x_errbuf OUT NOCOPY VARCHAR2,
231 x_retcode OUT NOCOPY VARCHAR2,
232 x_batch_size NUMBER,
233 x_worker_Id NUMBER,
234 x_num_workers NUMBER,
235 x_argument4 VARCHAR2) IS
236 fn_name CONSTANT VARCHAR2(30) := 'UPDATE_GL_JE_LINES_RECON_TABLE';
237 --
238 l_any_rows_to_process BOOLEAN;
239 l_start_id NUMBER;
240 l_end_id NUMBER;
241 l_rows_processed NUMBER;
242 --
243 BEGIN
244 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Worker_Id : ' || X_Worker_Id);
245 FND_FILE.PUT_LINE(FND_FILE.LOG, 'X_Num_Workers : ' || X_Num_Workers);
246 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Session Id : ' ||
247 FND_GLOBAL.session_id);
248 --
249 GL_MESSAGE.FUNC_ENT(fn_name, FND_LOG.LEVEL_PROCEDURE,
250 g_api || '.' || fn_name);
251 -- parameters
252 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
253 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
254 g_api || '.' || fn_name,
255 'x_batch_size = ' || x_batch_size);
256 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_PROCEDURE,
257 g_api || '.' || fn_name,
258 'x_argument4 = ' || x_argument4);
259 END IF;
260 --
261 ad_parallel_updates_pkg.initialize_id_range(
262 ad_parallel_updates_pkg.ID_RANGE,
263 x_argument4,
264 g_table_name,
265 g_script_name,
266 g_id_column,
267 x_worker_id,
268 x_num_workers,
269 x_batch_size, 0);
270 --
271 ad_parallel_updates_pkg.get_id_range(
272 l_start_id,
273 l_end_id,
274 l_any_rows_to_process,
275 x_batch_size,
276 TRUE);
277 --
278 while (l_any_rows_to_process)
279 loop
280 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_start_id : ' || l_start_id);
281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_end_id : ' || l_end_id);
282 --
283 l_rows_processed := prepare_recon_update(l_start_id,l_end_id);
284 --
285 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_rows_processed : ' || l_rows_processed);
286 --
287 ad_parallel_updates_pkg.processed_id_range(
288 l_rows_processed,
289 l_end_id);
290 --
291 fnd_concurrent.af_commit;
292 --
293 ad_parallel_updates_pkg.get_id_range(
294 l_start_id,
295 l_end_id,
296 l_any_rows_to_process,
297 x_batch_size,
298 FALSE);
299 --
300 end loop;
301 --
302 x_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
303 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
304 GL_MESSAGE.FUNC_SUCC(fn_name, FND_LOG.LEVEL_PROCEDURE,
305 g_api || '.' || fn_name);
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 x_errbuf := SUBSTR(SQLERRM, 1, 240);
310 x_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
311 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL >= FND_LOG.LEVEL_PROCEDURE) THEN
312 GL_MESSAGE.WRITE_FNDLOG_STRING(FND_LOG.LEVEL_UNEXPECTED,
313 g_api || '.' || fn_name,
314 SUBSTR(SQLERRM, 1, 4000));
315 GL_MESSAGE.FUNC_FAIL(fn_name, FND_LOG.LEVEL_UNEXPECTED,
316 g_api || '.' || fn_name);
317 END IF;
318 RAISE;
319 END update_gl_je_lines_recon_table;
320
321 END GL_ADD_RECON_UPGRADE_PKG;