[Home] [Help]
PACKAGE BODY: APPS.JA_CN_VOUCHER_NUM_MIG_PKG
Source
1 PACKAGE BODY JA_CN_VOUCHER_NUM_MIG_PKG AS
2 --$Header: JACNVNMB.pls 120.3 2011/05/13 03:35:55 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNVNMB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to migrate the history data |
13 --| |
14 --| PROCEDURE LIST |
15 --| Procedure Migration |
16 --| Procedure Migrate_voucher_number |
17 --| Procedure Migrate_voucher_num_periods |
18 --| |
19 --| HISTORY |
20 --| 29-Sep-2010 Jianchao Chi Created |
21 --| 14-Jan-2011 Chongwu Li Updated CNAO V1 to V2 upgrade |
22 --| 24-Jan-2011 Jianchao Chi Update for bug 11670727 |
23 --| 12-May-2011 Chongwu Li Updated for CFS seperated patch|
24 --| replace ja_cn_utility invoke for this method |
25 --| get_balancing_segment with JA_CN_VOUCHER_NUM_PKG|
26 --| tracked by bug 12541220 |
27 --+======================================================================*/
28 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_VOUCHER_NUM_MIGRATION_PKG';
29
30 --==========================================================================
31 -- PROCEDURE NAME:
32 --
33 -- Migrate_voucher_number Public
34 --
35 -- DESCRIPTION:
36 --
37 -- This function is to migrate the voucher number from gl_je_journal_lines
38 --
39 -- PARAMETERS:
40 -- In: pn_ledger_id Ledger ID
41 -- pn_legal_entity_id Legal Entity ID
42 -- pv_period_name Period Name
43 -- Out:
44 --
45 -- DESIGN REFERENCES:
46 --
47 --
48 -- CHANGE HISTORY:
49 -- 27-Sep-2010 Chongwu Li Created
50 -- 22-Oct-2010 Jianchao Chi Updated
51 -- 14-Jan-2011 Chongwu Li Updated
52 -- 14-Jan-2011 Jianchao Chi Updated for bug 11670727
53 --===========================================================================
54 PROCEDURE Migrate_voucher_number(pn_ledger_id IN NUMBER,
55 pn_legal_entity_id IN NUMBER,
56 pv_period_name IN VARCHAR2) IS
57
58 lv_procedure_name VARCHAR2(40) := 'Migrate_voucher_number';
59 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
60 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
61 ln_bsv_assigned_num NUMBER;
62 ln_bsv_mig_num NUMBER;
63 ln_no_bsv_mig_num NUMBER;
64
65 CURSOR bsv_legal_entity_cur IS
66 SELECT DISTINCT legal_entity_id
67 FROM gl_ledger_norm_seg_vals
68 WHERE ledger_id = pn_ledger_id;
69 --Cursor for Journals which has BSV assignment
70 CURSOR voucher_cur IS
71 SELECT jcjl.je_header_id,
72 jcjl.default_effective_date,
73 jcjl.period_name,
74 gjh.POSTING_ACCT_SEQ_VALUE,
75 jcjl.je_line_num,
76 jcjl.journal_number
77 FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
78 WHERE gjh.je_header_id = jcjl.je_header_id
79 AND jcjl.ledger_id = pn_ledger_id
80 -- CHOLI add this line for CNAO V1 to V2 Upgrade
81 AND jcjl.journal_number IS NOT NULL
82 --AND jcjl.status = 'P'
83 AND gjh.period_name = pv_period_name
84 AND EXISTS
85 (SELECT jclllbg.bal_seg_value
86 FROM ja_cn_ledger_le_bsv_gt jclllbg
87 WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(jcjl.code_combination_id) =
88 jclllbg.bal_seg_value
89 AND jclllbg.Ledger_Id = pn_ledger_id
90 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
91 AND NOT EXISTS
92 (SELECT je_header_id, je_line_number
93 FROM ja_cn_voucher_number jcvn
94 WHERE gjh.je_header_id = jcvn.je_header_id
95 AND jcjl.je_line_num = jcvn.je_line_number
96 AND jcvn.ledger_id = pn_ledger_id
97 AND jcvn.legal_entity_id = pn_legal_entity_id
98 AND jcvn.period_name = pv_period_name)
99 ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
100 gjh.default_effective_date ASC,
101 gjh.posted_date ASC,
102 gjh.je_header_id ASC;
103 --Cursor for Journals without BSV assignment
104 CURSOR voucher_no_bsv_cur IS
105 SELECT jcjl.je_header_id,
106 jcjl.default_effective_date,
107 jcjl.period_name,
108 gjh.POSTING_ACCT_SEQ_VALUE,
109 jcjl.je_line_num,
110 jcjl.journal_number
111 FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
112 WHERE gjh.je_header_id = jcjl.je_header_id
113 AND jcjl.ledger_id = pn_ledger_id
114 -- CHOLI add this line for CNAO V1 to V2 Upgrade
115 AND jcjl.journal_number IS NOT NULL
116 --AND jcjl.status = 'P'
117 AND gjh.period_name = pv_period_name
118 AND NOT EXISTS
119 (SELECT je_header_id, je_line_number
120 FROM ja_cn_voucher_number jcvn
121 WHERE gjh.je_header_id = jcvn.je_header_id
122 AND jcjl.je_line_num = jcvn.je_line_number
123 AND jcvn.ledger_id = pn_ledger_id
124 AND jcvn.legal_entity_id = pn_legal_entity_id
125 AND jcvn.period_name = pv_period_name)
126 ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
127 gjh.default_effective_date ASC,
128 gjh.posted_date ASC,
129 gjh.je_header_id ASC;
130 BEGIN
131 --logging for debug
132 IF (ln_proc_level >= ln_dbg_level) THEN
133 FND_LOG.STRING(ln_proc_level,
134 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
135 '.begin',
136 'Enter procedure');
137 END IF; --ln_proc_level>=ln_dbg_level
138 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
139 FND_FILE.put_line(FND_FILE.log,
140 lv_procedure_name || '.parameters:' ||
141 'pn_ledger_id=' || pn_ledger_id || ',' ||
142 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
143 'pv_period_name=' || pv_period_name);
144 BEGIN
145
146 SELECT COUNT(*) --Add for bug 11670727, to judge the migration amount is more then 0 or not
147 INTO ln_bsv_mig_num
148 FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
149 WHERE gjh.je_header_id = jcjl.je_header_id
150 AND jcjl.ledger_id = pn_ledger_id
151 --AND jcjl.status = 'P'
152 AND gjh.period_name = pv_period_name
153 AND EXISTS
154 (SELECT jclllbg.bal_seg_value
155 FROM ja_cn_ledger_le_bsv_gt jclllbg
156 WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(jcjl.code_combination_id) =
157 jclllbg.bal_seg_value
158 AND jclllbg.Ledger_Id = pn_ledger_id
159 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
160 AND NOT EXISTS
161 (SELECT je_header_id, je_line_number
162 FROM ja_cn_voucher_number jcvn
163 WHERE gjh.je_header_id = jcvn.je_header_id
164 AND jcjl.je_line_num = jcvn.je_line_number
165 AND jcvn.ledger_id = pn_ledger_id
166 AND jcvn.legal_entity_id = pn_legal_entity_id
167 AND jcvn.period_name = pv_period_name);
168
169 SELECT COUNT(*)
170 INTO ln_no_bsv_mig_num
171 FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
172 WHERE gjh.je_header_id = jcjl.je_header_id
173 AND jcjl.ledger_id = pn_ledger_id
174 --AND jcjl.status = 'P'
175 AND gjh.period_name = pv_period_name
176 AND NOT EXISTS
177 (SELECT je_header_id, je_line_number
178 FROM ja_cn_voucher_number jcvn
179 WHERE gjh.je_header_id = jcvn.je_header_id
180 AND jcjl.je_line_num = jcvn.je_line_number
181 AND jcvn.ledger_id = pn_ledger_id
182 AND jcvn.legal_entity_id = pn_legal_entity_id
183 AND jcvn.period_name = pv_period_name)
184 ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
185 gjh.default_effective_date ASC,
186 gjh.posted_date ASC,
187 gjh.je_header_id ASC;
188
189 SELECT count(*)
190 INTO ln_bsv_assigned_num
191 FROM ja_cn_ledger_le_bsv_gt
192 WHERE ledger_id = pn_ledger_id;
193 -- BSV is assigned, only the parameter legal entity will be handled.
194 IF (ln_bsv_assigned_num > 0) THEN
195 IF (ln_bsv_mig_num > 0) THEN
196 DELETE FROM ja_cn_voucher_number
197 WHERE ledger_id = pn_ledger_id
198 AND legal_entity_id = pn_legal_entity_id
199 AND period_name = pv_period_name
200 AND (data_source_tag <> 'M' OR data_source_tag is null);
201
202 FOR v_row IN voucher_cur LOOP
203 INSERT INTO ja_cn_voucher_number
204 (ledger_id,
205 legal_entity_id,
206 period_name,
207 je_header_id,
208 je_line_number,
209 voucher_number,
210 REQUEST_ID,
211 data_source_tag,
212 created_by,
213 creation_date,
214 last_updated_by,
215 last_update_date,
216 last_update_login)
217 VALUES
218 (pn_ledger_id,
219 pn_legal_entity_id,
220 pv_period_name,
221 v_row.je_header_id,
222 v_row.je_line_num,
223 v_row.journal_number,
224 fnd_global.CONC_REQUEST_ID,
225 'M',
226 fnd_global.user_id,
227 SYSDATE(),
228 fnd_global.user_id,
229 SYSDATE(),
230 fnd_global.login_id);
231 END LOOP; -- FOR v_row IN voucher_cur
232 END IF;
233 ELSE
234 IF (ln_no_bsv_mig_num > 0) THEN
235 DELETE FROM ja_cn_voucher_number
236 WHERE ledger_id = pn_ledger_id
237 AND period_name = pv_period_name
238 AND (data_source_tag <> 'M' OR data_source_tag is null);
239
240 FOR v_row IN voucher_no_bsv_cur LOOP
241 FOR v_row1 IN bsv_legal_entity_cur LOOP
242 INSERT INTO ja_cn_voucher_number
243 (ledger_id,
244 legal_entity_id,
245 period_name,
246 je_header_id,
247 je_line_number,
248 voucher_number,
249 REQUEST_ID,
250 data_source_tag,
251 created_by,
252 creation_date,
253 last_updated_by,
254 last_update_date,
255 last_update_login)
256 VALUES
257 (pn_ledger_id,
258 v_row1.legal_entity_id,
259 pv_period_name,
260 v_row.je_header_id,
261 v_row.je_line_num,
262 v_row.journal_number,
263 fnd_global.CONC_REQUEST_ID,
264 'M',
265 fnd_global.user_id,
266 SYSDATE(),
267 fnd_global.user_id,
268 SYSDATE(),
269 fnd_global.login_id);
270 END LOOP; -- FOR v_row1 IN bsv_legal_entity_cur
271 END LOOP; -- FOR v_row IN voucher_cur
272 END IF;
273 END IF;
274 EXCEPTION
275 WHEN OTHERS THEN
276 FND_FILE.put_line(FND_FILE.log,
277 lv_procedure_name || SQLCODE || SQLERRM);
278 RAISE;
279 END;
280 --logging for debug
281 IF (ln_proc_level >= ln_dbg_level) THEN
282 FND_LOG.STRING(ln_proc_level,
283 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
284 'Exit procedure');
285 END IF; -- (ln_proc_level>=ln_dbg_level)
286
287 END Migrate_voucher_number;
288
289 --==========================================================================
290 -- PROCEDURE NAME:
291 --
292 -- Migrate_voucher_num_periods Public
293 --
294 -- DESCRIPTION:
295 --
296 -- This function is to migrate the voucher number for the period range
297 --
298 -- PARAMETERS:
299 -- In: pn_ledger_id Ledger ID
300 -- pn_legal_entity_id Legal Entity ID
301 -- Out:
302 --
303 -- DESIGN REFERENCES:
304 --
305 --
306 -- CHANGE HISTORY:
307 -- 27-Sep-2010 Chongwu Li Created
308 -- 22-Oct-2010 Jianchao Chi Updated
309 --
310 --===========================================================================
311 PROCEDURE Migrate_voucher_num_periods(pn_ledger_id IN NUMBER,
312 pn_legal_entity_id IN NUMBER) IS
313 ln_flag VARCHAR2(10);
314 lv_procedure_name VARCHAR2(40) := 'Migrate_voucher_num_periods';
315 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
316 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
317 CURSOR periods_cur IS
318 SELECT period_name
319 FROM gl_period_statuses
320 WHERE application_id = 101
321 AND ledger_id = pn_ledger_id
322 AND period_name in
323 (select distinct period_name
324 from ja_cn_journal_lines
325 where ledger_id = pn_ledger_id
326 and legal_entity_id = pn_legal_entity_id)
327 AND adjustment_period_flag = 'N'
328 AND closing_status <> 'N'
329 AND closing_status <> 'F'
330 ORDER BY start_date;
331 BEGIN
332 --logging for debug
333 IF (ln_proc_level >= ln_dbg_level) THEN
334 FND_LOG.STRING(ln_proc_level,
335 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
336 '.begin',
337 'Enter procedure');
338 END IF; --ln_proc_level>=ln_dbg_level
339 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
340 FND_FILE.put_line(FND_FILE.log,
341 lv_procedure_name || '.parameters:' ||
342 'pn_ledger_id=' || pn_ledger_id || ',' ||
343 'pn_legal_entity_id=' || pn_legal_entity_id);
344 BEGIN
345 ln_flag := JA_CN_UTILITY.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id => pn_ledger_id,
346 p_Legal_Entity_Id => pn_legal_entity_id);
347 FND_FILE.put_line(FND_FILE.log,
348 lv_procedure_name || 'ln_flag = ' || ln_flag);
349 FOR v_row IN periods_cur LOOP
350 Migrate_voucher_number(pn_ledger_id => pn_ledger_id,
351 pn_legal_entity_id => pn_legal_entity_id,
352 pv_period_name => v_row.period_name);
353 END LOOP; -- FOR v_row IN periods_cur
354 EXCEPTION
355 WHEN OTHERS THEN
356 FND_FILE.put_line(FND_FILE.log,
357 lv_procedure_name || SQLCODE || SQLERRM);
358 RAISE;
359 END;
360 --logging for debug
361 IF (ln_proc_level >= ln_dbg_level) THEN
362 FND_LOG.STRING(ln_proc_level,
363 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
364 'Exit procedure');
365 END IF; -- (ln_proc_level>=ln_dbg_level)
366 END Migrate_voucher_num_periods;
367
368 --==========================================================================
369 -- PROCEDURE NAME:
370 --
371 -- Migration Public
372 --
373 -- DESCRIPTION:
374 --
375 -- This procedure is used to deal with the parameters and invoke the
376 -- "Migrate_voucher_num_periods" function to process the data.
377 --
378 -- PARAMETERS:
379 -- In: N/A
380 -- Out: TRUE/FALSE
381 --
382 -- DESIGN REFERENCES:
383 --
384 --
385 -- CHANGE HISTORY:
386 -- 18-Oct-2010 Jianchao Chi Created
387 --
388 --===========================================================================
389 PROCEDURE Migration(Errbuf OUT NOCOPY VARCHAR2,
390 Retcode OUT NOCOPY VARCHAR2,
391 P_RESP_APPLICATION_ID IN NUMBER,
392 P_LEGAL_ENTITY_TEMP IN NUMBER,
393 P_PROFILE_OPTION_VALUE IN NUMBER,
394 P_LEDGER_ID IN NUMBER,
395 P_LEDGER_NAME IN VARCHAR2,
396 P_LEGAL_ENTITY_ID IN NUMBER,
397 P_LEGAL_ENTITY IN VARCHAR2) IS
398
399 lv_procedure_name VARCHAR2(40) := 'beforeReport';
400 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
401 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
402 VOUCHER_NUMBER_FROM NUMBER;
403 VOUCHER_NUMBER_TO NUMBER;
404 JOURNAL_AMOUNT NUMBER;
405 l_Error_Msg VARCHAR2(300);
406 HAS_DATA_TAG VARCHAR2(10) := 'N';
407 CURSOR periods_cur IS
408 SELECT period_name
409 FROM gl_period_statuses
410 WHERE application_id = 101
411 AND ledger_id = P_LEDGER_ID
412 AND period_name in
413 (select distinct period_name
414 from ja_cn_journal_lines
415 where ledger_id = P_LEDGER_ID
416 and legal_entity_id = P_LEGAL_ENTITY_ID)
417 AND closing_status <> 'N'
418 AND closing_status <> 'F'
419 ORDER BY start_date;
420 BEGIN
421 --logging for debug
422 IF (ln_proc_level >= ln_dbg_level) THEN
423 FND_LOG.STRING(ln_proc_level,
424 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
425 '.begin',
426 'Enter procedure');
427 END IF; --ln_proc_level>=ln_dbg_level
428 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
429 FND_FILE.put_line(FND_FILE.log,
430 lv_procedure_name || '.parameters: ' ||
431 'P_LEDGER_ID=' || P_LEDGER_ID || ', ' ||
432 'P_LEGAL_ENTITY_ID=' || P_LEGAL_ENTITY_ID || ', ' ||
433 'P_PROFILE_OPTION_VALUE=' || P_PROFILE_OPTION_VALUE || ', ' ||
434 'P_RESP_APPLICATION_ID=' || P_RESP_APPLICATION_ID);
435
436 Migrate_voucher_num_periods(pn_ledger_id => P_LEDGER_ID,
437 pn_legal_entity_id => P_LEGAL_ENTITY_ID);
438 FOR v_row IN periods_cur LOOP
439 SELECT COUNT(VOUCHER_NUMBER), --JA_CN_VOUCHER_NUMBER_HAS_DATA
440 MAX(VOUCHER_NUMBER),
441 MIN(VOUCHER_NUMBER)
442 INTO JOURNAL_AMOUNT, VOUCHER_NUMBER_TO, VOUCHER_NUMBER_FROM
443 FROM (SELECT DISTINCT VOUCHER_NUMBER
444 FROM JA_CN_VOUCHER_NUMBER
445 WHERE PERIOD_NAME = v_row.period_name
446 AND REQUEST_ID = fnd_global.CONC_REQUEST_ID);
447 IF (JOURNAL_AMOUNT <> 0) THEN
448 Fnd_Message.Set_Name(Application => 'JA',
449 NAME => 'JA_CN_VOUCHER_NUM_MIG_HAS_DATA');
450 Fnd_Message.SET_TOKEN(TOKEN => 'PERIOD_NAME',
451 VALUE => v_row.period_name);
452 Fnd_Message.SET_TOKEN(TOKEN => 'VOUCHER_NUMBER_FROM',
453 VALUE => VOUCHER_NUMBER_FROM);
454 Fnd_Message.SET_TOKEN(TOKEN => 'VOUCHER_NUMBER_TO',
455 VALUE => VOUCHER_NUMBER_TO);
456 Fnd_Message.SET_TOKEN(TOKEN => 'MIGRATED_JOURNAL_AMOUNT',
457 VALUE => JOURNAL_AMOUNT);
458 l_Error_Msg := Fnd_Message.Get;
459
460 --Output error message
461 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
462 HAS_DATA_TAG := 'Y';
463 END IF;
464 END LOOP; -- FOR v_row IN periods_cur
465 IF (HAS_DATA_TAG = 'N') THEN
466 Fnd_Message.Set_Name(Application => 'JA',
467 NAME => 'JA_CN_VOUCHER_NUM_MIG_NO_DATA');
468 l_Error_Msg := Fnd_Message.Get;
469
470 --Output error message
471 Fnd_File.Put_Line(Fnd_File.Output, l_Error_Msg);
472 END IF;
473 EXCEPTION
474 WHEN OTHERS THEN
475 IF (ln_Proc_Level >= ln_dbg_level) THEN
476 Fnd_Log.STRING(ln_Proc_Level,
477 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
478 '. Other_Exception ',
479 SQLCODE || ':' || SQLERRM);
480 END IF; --(l_proc_level >= l_dbg_level)
481 END Migration;
482
483 END JA_CN_VOUCHER_NUM_MIG_PKG;
484