[Home] [Help]
PACKAGE BODY: APPS.JA_CN_HISDATA_MIG_PKG
Source
1 PACKAGE BODY JA_CN_HISDATA_MIG_PKG AS
2 --$Header: JACNHDMB.pls 120.17 2011/05/13 03:33:46 choli noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation
5 --| Redwood Shores, CA, USA
6 --| All rights reserved.
7 --+=======================================================================
8 --| FILENAME
9 --| JACNHDMB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| |
13 --| This package is to provide CNAO V1 to V2 data |
14 --| migration procedures. |
15 --| |
16 --| PROCEDURE LIST |
17 --| |
18 --| PROCEDURE UpdateGLCashlines |
19 --| PROCEDURE UpdateGLItemizationlines |
20 --| PROCEDURE UpdateItemizationlines |
21 --| PROCEDURE MigrateHisData |
22 --| PROCEDURE MigrateHisDataCFS |
23 --| PROCEDURE MigrateHisDataDE |
24 --| |
25 --| HISTORY |
26 --| 05-Jan-2011 Chongwu Li Created |
27 --| 19-Jan-2011 Chongwu Li Updated, fix bug 11659281, 11655280 |
28 --| 24-Jan-2011 Jianchao Chi Updated for bug 10634017 |
29 --| 24-Jan-2011 Chongwu Li Updated, fix bug 11664438 |
30 --| 27-Jan-2011 Chongwu Li Updated, fix bug 11675517, 11683977 |
31 --| 28-Jan-2011 Jianchao Chi Updated, fix bug 11691910 |
32 --| 11-Feb-2011 Jianchao Chi Updated, fix bug 11683977 |
33 --| 22-Mar-2011 Chongwu Li Updated, fix bug 11904408 |
34 --| 11-Apr-2011 Chongwu Li Updated, fix bug 12344625 |
35 --| 13-Apr-2011 Chongwu Li Updated, fix bug 12349159 |
36 --| 19-Apr-2011 Chongwu Li updated, fix bug 12349128 |
37 --| 21-Apr-2011 Chongwu Li updated, fix bug 12373841 |
38 --| 21-Apr-2011 Jianchao Chi updated, fix bug 12353563 |
39 --| 26-Apr-2011 Chongwu Li updated, performance optimization |
40 --| 12-May-2011 Chongwu Li Updated for CFS seperated patch|
41 --| replace ja_cn_utility invoke for this method |
42 --| get_balancing_segment with JA_CN_VOUCHER_NUM_PKG|
43 --| tracked by bug 12541220 |
44 --+======================================================================*/
45
46 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_HISDATA_MIG_PKG';
47
48 --==========================================================================
49 -- FUNCTION NAME:
50 --
51 -- UpdateGLCashlines Public
52 --
53 -- DESCRIPTION:
54 --
55 -- This procedure is used to migrate the cash flow related lines data
56 -- in GL_JE_LINES. copy values from DFF column AttributeN to column
57 -- Global_Attribute6.
58 --
59 --
60 -- PARAMETERS:
61 -- In: pn_chart_account_id Chart of account ID
62 -- In: pn_legal_entitliy_id Legal entity ID
63 -- In : pn_ledger_id Leger Id
64 -- In : P_LEDGER_NAME Leger Name
65 -- In : P_LEGAL_ENTITY Legal Entity Name
66 -- In : pv_period_name Period Name
67 -- DESIGN REFERENCES:
68 --
69 --
70 -- CHANGE HISTORY:
71 --
72 -- 05-Jan-2011 Chongwu Li Created
73 -- 28-Jan-2011 Jianchao Chi Updated, fix bug 11691910
74 --
75 --===========================================================================
76 FUNCTION UpdateGLCashlines(pn_chart_account_id NUMBER, -- Change from Procedure, by Jianchao Chi for bug 11691910
77 pn_legal_entitliy_id NUMBER,
78 pn_ledger_id NUMBER,
79 P_LEDGER_NAME VARCHAR2,
80 P_LEGAL_ENTITY VARCHAR2,
81 pv_period_name VARCHAR2) RETURN NUMBER IS
82 -- Add return value by Jianchao Chi for bug 11691910
83 lv_flag VARCHAR2(10);
84 lv_enabled VARCHAR2(1);
85 lv_output_Msg VARCHAR2(4000);
86 ln_mig_numbers NUMBER;
87 lv_cashitem_column VARCHAR2(40);
88 lv_cashrelated_column VARCHAR2(40);
89 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
90 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
91 lv_procedure_name VARCHAR2(40) := 'UpdateCashlines';
92 ln_je_header_id NUMBER;
93 ln_je_line_num NUMBER;
94 TYPE ref_cursor_type IS REF CURSOR;
95 cur_cash_lines ref_cursor_type;
96 lv_cur_cash_lines_sql VARCHAR2(4000) := 'SELECT gjl.je_header_id, gjl.je_line_num
97 FROM gl_je_lines gjl, gl_je_headers gjh, ja_cn_dff_assignments jcda
98 WHERE gjl.ledger_id = ' ||
99 pn_ledger_id || '
100 AND gjh.ledger_id = gjl.ledger_id
101 AND gjh.period_name = ''' ||
102 pv_period_name || '''
103 AND gjh.period_name = gjl.period_name
104 AND EXISTS
105 (SELECT jclllbg.bal_seg_value
106 FROM ja_cn_ledger_le_bsv_gt jclllbg
107 WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(gjl.code_combination_id) =
108 jclllbg.bal_seg_value
109 AND jclllbg.Ledger_Id = ' ||
110 pn_ledger_id || '
111 AND jclllbg.Legal_Entity_Id = ' ||
112 pn_legal_entitliy_id || ')
113 AND gjh.je_header_id = gjl.je_header_id
114 AND gjl.context = jcda.context_code
115 AND jcda.chart_of_accounts_id = ' ||
116 pn_chart_account_id || '
117 AND jcda.descriptive_flexfield_name = ''GL_JE_LINES''
118 AND jcda.dff_title_code = ''GLLI''
119 AND gjh.je_category IN
120 ( SELECT gjc.je_category_name
121 FROM gl_je_categories gjc, ja_cn_dff_assignments jcda
122 WHERE jcda.chart_of_accounts_id = ' ||
123 pn_chart_account_id || '
124 AND gjc.$cash_related_attribute$ = ''Y''
125 AND jcda.descriptive_flexfield_name = ''GL_JE_CATEGORIES''
126 AND jcda.dff_title_code = ''JOCA''
127 AND gjc.CONTEXT = jcda.context_code)
128 AND gjl.Global_Attribute6 is null';
129 lv_update_cash_lines_sql VARCHAR2(4000) := 'UPDATE gl_je_lines gjl
130 SET gjl.Global_Attribute6 = gjl.$cashitem_column$
131 WHERE gjl.je_header_id = :1
132 AND gjl.je_line_num = :2';
133 BEGIN
134 --logging for debug
135 IF (ln_proc_level >= ln_dbg_level) THEN
136 FND_LOG.STRING(ln_proc_level,
137 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
138 '.begin',
139 'Enter procedure');
140 END IF; --ln_proc_level>=ln_dbg_level
141 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
142 FND_FILE.put_line(FND_FILE.log,
143 lv_procedure_name || '.parameters:' ||
144 'pn_chart_account_id=' || pn_chart_account_id || ',' ||
145 'pv_period_name=' || pv_period_name || ',' ||
146 'pn_ledger_id=' || pn_ledger_id || ',' ||
147 'pn_legal_entitliy_id=' || pn_legal_entitliy_id);
148 lv_flag := JA_CN_UTILITY.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id => pn_ledger_id,
149 p_Legal_Entity_Id => pn_legal_entitliy_id);
150 FND_FILE.put_line(FND_FILE.log,
151 lv_procedure_name || 'ln_flag = ' || lv_flag);
152 -- Find DFF cash related item attribute
153 BEGIN
154 SELECT jcda.attribute_column
155 INTO lv_cashrelated_column
156 FROM ja_cn_dff_assignments jcda
157 WHERE jcda.chart_of_accounts_id = pn_chart_account_id
158 AND jcda.descriptive_flexfield_name = 'GL_JE_CATEGORIES'
159 AND jcda.dff_title_code = 'JOCA';
160 EXCEPTION
161 WHEN OTHERS THEN
162 FND_FILE.put_line(FND_FILE.log,
163 lv_procedure_name || SQLCODE || SQLERRM);
164 RAISE;
165 END;
166 -- Construct the cursor to get all the cash related journal lines,
167 -- As well as Global_Attribute6 has no value;
168 lv_cur_cash_lines_sql := REPLACE(lv_cur_cash_lines_sql,
169 '$cash_related_attribute$',
170 lv_cashrelated_column);
171 -- Find the DFF cash flow item attribute column
172 BEGIN
173 SELECT jcda.attribute_column, b.enabled_flag
174 INTO lv_cashitem_column, lv_enabled
175 FROM fnd_descr_flex_column_usages b, ja_cn_dff_assignments jcda
176 WHERE (b.APPLICATION_ID = 101)
177 AND (b.descriptive_flexfield_name =
178 JCDA.descriptive_flexfield_name)
179 AND jcda.chart_of_accounts_id = pn_chart_account_id
180 AND jcda.descriptive_flexfield_name = 'GL_JE_LINES'
181 AND jcda.dff_title_code = 'GLLI'
182 AND b.descriptive_flex_context_code = jcda.context_code;
183 EXCEPTION
184 WHEN OTHERS THEN
185 FND_FILE.put_line(FND_FILE.log,
186 lv_procedure_name || SQLCODE || SQLERRM);
187 RAISE;
188 END;
189 -- Construct the update sql for GL_JE_LINES data migration
190 lv_update_cash_lines_sql := REPLACE(lv_update_cash_lines_sql,
191 '$cashitem_column$',
192 lv_cashitem_column);
193
194 ln_mig_numbers := 0;
195 IF (lv_enabled = 'Y') THEN
196 OPEN cur_cash_lines FOR lv_cur_cash_lines_sql;
197 LOOP
198 FETCH cur_cash_lines
199 INTO ln_je_header_id, ln_je_line_num;
200 EXIT WHEN cur_cash_lines%NOTFOUND;
201
202 EXECUTE IMMEDIATE lv_update_cash_lines_sql
203 USING ln_je_header_id, ln_je_line_num;
204 ln_mig_numbers := ln_mig_numbers + 1;
205 END LOOP; -- OPEN cur_cash_lines
206 CLOSE cur_cash_lines;
207 END IF; -- IF (lv_enabled = 'Y')
208
209 RETURN ln_mig_numbers;
210
211 --Comment by Jianchao Chi for bug 11691910
212 /* Fnd_Message.Set_Name(Application => 'JA',
213 NAME => 'JA_CN_DATA_MIGRATION_RESULT');
214 Fnd_Message.SET_TOKEN(TOKEN => 'LEGAL_ENTITY_NAME',
215 VALUE => P_LEGAL_ENTITY);
216 Fnd_Message.SET_TOKEN(TOKEN => 'LEGER_NAME',
217 VALUE => P_LEDGER_NAME);
218 Fnd_Message.SET_TOKEN(TOKEN => 'MIGRATED_JOURNAL_NUM',
219 VALUE => ln_mig_numbers);
220 lv_output_Msg := Fnd_Message.Get;
221
222 --Output error message
223 Fnd_File.Put_Line(Fnd_File.Output, lv_output_Msg);*/
224 --logging for debug
225 IF (ln_proc_level >= ln_dbg_level) THEN
226 FND_LOG.STRING(ln_proc_level,
227 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
228 'Exit procedure');
229 END IF; -- (ln_proc_level>=ln_dbg_level)
230 END UpdateGLCashlines;
231
232 --==========================================================================
233 -- FUNCTION NAME:
234 --
235 -- UpdateSLAHeaderLine Public
236 --
237 -- DESCRIPTION:
238 --
239 -- This procedure is used to migrate the header id and line number from
240 -- JA_CN_JOURNAL_LINES to ja_cn_cfs_activities_all for the SLA transaction
241 -- type.
242 --
243 --
244 -- PARAMETERS:
245 -- In: pn_legal_entitliy_id Legal entity ID
246 -- In : pn_ledger_id Leger Id
247 -- In : P_LEDGER_NAME Leger Name
248 -- In : P_LEGAL_ENTITY Legal Entity Name
249 -- In : pv_period_name Period Name
250 -- DESIGN REFERENCES:
251 --
252 --
253 -- CHANGE HISTORY:
254 --
255 -- 18-Apr-2011 Jianchao Chi Created
256 --
257 --===========================================================================
258 PROCEDURE UpdateSLAHeaderLine(pn_legal_entitliy_id NUMBER,
259 pn_ledger_id NUMBER,
260 P_LEDGER_NAME VARCHAR2,
261 P_LEGAL_ENTITY VARCHAR2,
262 pv_period_name VARCHAR2) IS
263 lv_flag VARCHAR2(10);
264 lv_enabled VARCHAR2(1);
265 lv_output_Msg VARCHAR2(4000);
266 ln_mig_numbers NUMBER;
267 lv_cashitem_column VARCHAR2(40);
268 lv_cashrelated_column VARCHAR2(40);
269 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
270 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
271 lv_procedure_name VARCHAR2(40) := 'UpdateSLAHeaderLine';
272 ln_je_header_id NUMBER;
273 ln_je_line_num NUMBER;
274
275 BEGIN
276 --logging for debug
277 IF (ln_proc_level >= ln_dbg_level) THEN
278 FND_LOG.STRING(ln_proc_level,
279 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
280 '.begin',
281 'Enter procedure');
282 END IF; --ln_proc_level>=ln_dbg_level
283 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
284 FND_FILE.put_line(FND_FILE.log,
285 lv_procedure_name || '.parameters:' ||
286 'pv_period_name=' || pv_period_name || ',' ||
287 'pn_ledger_id=' || pn_ledger_id || ',' ||
288 'pn_legal_entitliy_id=' || pn_legal_entitliy_id);
289 BEGIN
290 UPDATE ja_cn_cfs_activities_all jcca
291 SET jcca.je_header_id =
292 (SELECT DISTINCT jcjl.je_header_id
293 FROM JA_CN_JOURNAL_LINES jcjl,
294 Gl_Je_Headers Jeh,
295 Xla_Ae_Lines Ael,
296 Gl_Import_References Gir
297 WHERE Jeh.Je_Header_Id = jcjl.je_header_id
298 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
299 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
300 AND Gir.Je_Line_Num = jcjl.Je_Line_Num
301 AND Gir.Je_Header_Id = jcjl.Je_Header_Id
302 AND jcca.trx_id = ael.ae_header_id
303 AND jcca.trx_line_id = ael.ae_line_num
304 AND jcjl.legal_entity_id = pn_legal_entitliy_id
305 AND jcjl.ledger_id = pn_ledger_id
306 AND jcjl.period_name = pv_period_name),
307 jcca.je_line_num =
308 (SELECT DISTINCT jcjl.Je_Line_Num
309 FROM JA_CN_JOURNAL_LINES jcjl,
310 Gl_Je_Headers Jeh,
311 Xla_Ae_Lines Ael,
312 Gl_Import_References Gir
313 WHERE Jeh.Je_Header_Id = jcjl.je_header_id
314 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
315 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
316 AND Gir.Je_Line_Num = jcjl.Je_Line_Num
317 AND Gir.Je_Header_Id = jcjl.Je_Header_Id
318 AND jcca.trx_id = ael.ae_header_id
319 AND jcca.trx_line_id = ael.ae_line_num
320 AND jcjl.legal_entity_id = pn_legal_entitliy_id
321 AND jcjl.ledger_id = pn_ledger_id
322 AND jcjl.period_name = pv_period_name)
323 WHERE jcca.transaction_type = 'SLA'
324 AND (jcca.je_header_id IS NULL OR jcca.je_line_num IS NULL)
325 AND legal_entity_id = pn_legal_entitliy_id
326 AND ledger_id = pn_ledger_id;
327 EXCEPTION
328 WHEN OTHERS THEN
329 NULL;
330 END;
331 --logging for debug
332 IF (ln_proc_level >= ln_dbg_level) THEN
333 FND_LOG.STRING(ln_proc_level,
334 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
335 'Exit procedure');
336 END IF; -- (ln_proc_level>=ln_dbg_level)
337 END UpdateSLAHeaderLine;
338
339 --==========================================================================
340 -- FUNCTION NAME:
341 --
342 -- UpdateGLItemizationlines Public
343 --
344 -- DESCRIPTION:
345 --
346 -- This procedure is used to migrate the Itemazation lines data
347 -- in GL_JE_LINES. copy values from column Global_Attribute2 to
348 -- column Global_Attribute7, and set Global_Attribute2 to null.
349 --
350 --
351 -- PARAMETERS:
352 -- In: pn_legal_entitliy_id Legal entity ID
353 -- In : pn_ledger_id Leger Id
354 -- In : pv_period_name Period Name
355 -- DESIGN REFERENCES:
356 --
357 --
358 -- CHANGE HISTORY:
359 --
360 -- 05-Jan-2011 Chongwu Li Created
361 --
362 --===========================================================================
363 PROCEDURE UpdateGLItemizationlines(pn_legal_entitliy_id NUMBER,
364 pn_ledger_id NUMBER,
365 pv_period_name VARCHAR2) IS
366 lv_flag VARCHAR2(10);
367 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
368 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
369 lv_procedure_name VARCHAR2(40) := 'UpdateGLItemizationlines';
370
371 BEGIN
372
373 --logging for debug
374 IF (ln_proc_level >= ln_dbg_level) THEN
375 FND_LOG.STRING(ln_proc_level,
376 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
377 '.begin',
378 'Enter procedure');
379 END IF; --ln_proc_level>=ln_dbg_level
380 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
381 FND_FILE.put_line(FND_FILE.log,
382 lv_procedure_name || '.parameters:' ||
383 'pv_period_name=' || pv_period_name || ',' ||
384 'pn_ledger_id=' || pn_ledger_id || ',' ||
385 'pn_legal_entitliy_id=' || pn_legal_entitliy_id);
386 BEGIN
387 lv_flag := JA_CN_UTILITY.Populate_Ledger_Le_Bsv_Gt(p_Ledger_Id => pn_ledger_id,
388 p_Legal_Entity_Id => pn_legal_entitliy_id);
389 FND_FILE.put_line(FND_FILE.log,
390 lv_procedure_name || 'ln_flag = ' || lv_flag);
391 UPDATE gl_je_lines gjl
392 SET gjl.Global_Attribute7 = gjl.Global_Attribute2,
393 gjl.Global_Attribute2 = NULL
394 WHERE gjl.ledger_id = pn_ledger_id
395 AND gjl.period_name = pv_period_name
396 AND EXISTS
397 (SELECT jclllbg.bal_seg_value
398 FROM ja_cn_ledger_le_bsv_gt jclllbg
399 WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(gjl.code_combination_id) =
400 jclllbg.bal_seg_value
401 AND jclllbg.Ledger_Id = pn_ledger_id
402 AND jclllbg.Legal_Entity_Id = pn_legal_entitliy_id)
403 AND gjl.Global_Attribute7 IS NULL
404 AND gjl.Global_Attribute2 IS NOT NULL;
405
406 --logging for debug
407 IF (ln_proc_level >= ln_dbg_level) THEN
408 FND_LOG.STRING(ln_proc_level,
409 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
410 '.end',
411 'Exit procedure');
412 END IF; -- (ln_proc_level>=ln_dbg_level)
413 EXCEPTION
414 WHEN OTHERS THEN
415 FND_FILE.put_line(FND_FILE.log,
416 lv_procedure_name || SQLCODE || SQLERRM);
417 RAISE;
418 END;
419 END UpdateGLItemizationlines;
420
421 --==========================================================================
422 -- FUNCTION NAME:
423 --
424 -- UpdateItemizationlines Public
425 --
426 -- DESCRIPTION:
427 --
428 -- This procedure is used to migrate the Itemazation lines data
429 -- in ja_cn_journal_lines.
430 --
431 --
432 -- PARAMETERS:
433 -- In: pn_chart_account_id Chart of account ID
434 -- In: pn_legal_entitliy_id Legal entity ID
435 -- In : pn_ledger_id Leger Id
436 -- In : pv_period_name Period Name
437 -- DESIGN REFERENCES:
438 --
439 --
440 -- CHANGE HISTORY:
441 --
442 -- 05-Jan-2011 Chongwu Li Created
443 -- 19-Jan-2011 Chongwu Li Updated, fix bug 11659281, 11655280
444 -- Merge UpdateBalances logic together to remove
445 -- the duplicated warning message.
446 -- 24-Jan-2011 Jianchao Chi Updated for bug 10634017
447 -- 24-Jan-2011 Chongwu Li Updated, fix bug 11774438
448 -- 11-Feb-2011 Jianchao Chi Updated for bug 11683977
449 -- 13-Apr-2011 Chongwu Li fix bug 12349159
450 -- 21-Apr-2011 Chongwu Li updated, fix bug 12373841
451 --
452 --===========================================================================
453 /*PROCEDURE UpdateItemizationlines(pn_chart_account_id NUMBER,
454 pn_legal_entitliy_id NUMBER,
455 pn_ledger_id NUMBER,
456 pv_period_name VARCHAR2,
457 --pb_msg_tag BOOLEAN, --Add a parameter by Jianchao Chi for bug 11683977
458 lb_need_Update BOOLEAN,
459 lv_prject_seg VARCHAR2) IS
460 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
461 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
462 lv_procedure_name VARCHAR2(40) := 'UpdateItemizationlines';
463 ln_Subsidiary_number NUMBER;
464 lv_customer_seg VARCHAR2(40);
465 lv_supplier_seg VARCHAR2(40);
466 lv_employee_seg VARCHAR2(40);
467 lv_costcenter_column VARCHAR2(40);
468 lv_costcenter_seg VARCHAR2(40);
469
470 \* lv_project_sourrce_flag VARCHAR2(40);
471 --lv_project_ac_code VARCHAR2(40);
472 --lv_coa_segment VARCHAR2(40);
473 lv_prject_coa_seg VARCHAR2(40);
474 lv_prject_project_seg VARCHAR2(40);*\
475 --lv_prject_seg VARCHAR2(40);
476 lv_Error_Msg VARCHAR2(4000);
477 lb_Error_Status BOOLEAN;
478 -- lb_need_Update BOOLEAN := TRUE;
479 lv_update_sql VARCHAR2(1000);
480 \* lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
481 SET $to_column$ = $from_column$
482 WHERE jcjl.je_header_id = :1
483 AND jcjl.je_line_num = :2
484 AND jcjl.rowid = :3'; -- Chongwu fix bug 12373841*\
485
486 lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
487 SET $to_column$ = $from_column$
488 WHERE jcjl.rowid = :1'; -- Chongwu fix bug 12373841
489
490 CURSOR cur_itemiz_lines IS
491 SELECT jcjl.rowid, -- Chongwu fix bug 12373841
492 je_header_id,
493 je_line_num,
494 third_party_number,
495 third_party_type,
496 cost_center,
497 ppf.employee_number, --choli changed for bug 11774438
498 project_number
499 FROM ja_cn_journal_lines jcjl, per_people_f ppf --choli changed for bug 11774438
500 WHERE jcjl.legal_entity_id = pn_legal_entitliy_id
501 AND jcjl.ledger_id = pn_ledger_id
502 AND jcjl.journal_number IS NOT NULL
503 AND jcjl.period_name = pv_period_name
504 AND ppf.person_id(+) = jcjl.personnel_id; --choli added for bug 11774438
505
506 lv_bal_update_sql VARCHAR2(1000);
507 \* lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
508 SET $to_column$ = $from_column$
509 WHERE jcab.ledger_id = :1
510 AND jcab.legal_entity_id = :2
511 AND jcab.company_segment = :3
512 AND jcab.period_name = :4
513 AND jcab.currency_code = :5
514 AND nvl(jcab.cost_center,0) = nvl(:6,0)
515 AND nvl(jcab.project_number,0) = nvl(:7,0)
516 AND nvl(jcab.project_source,0) = nvl(:8,0)
517 AND nvl(jcab.account_segment,0) = nvl(:9,0)
518 AND nvl(jcab.personnel_id,0) = nvl(:10,0)
519 AND nvl(jcab.third_party_type,0) = nvl(:11,0)
520 AND nvl(jcab.third_party_id,0) = nvl(:12,0)';*\
521 lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
522 SET $to_column$ = $from_column$
523 WHERE jcab.rowid = :1';
524
525
526 CURSOR cur_balances IS
527 SELECT jcab.rowid,
528 ledger_id,
529 legal_entity_id,
530 company_segment,
531 period_name,
532 currency_code,
533 cost_center,
534 project_number,
535 project_source,
536 account_segment,
537 personnel_id,
538 ppf.employee_number, --choli changed for bug 11774438
539 third_party_type,
540 third_party_id,
541 third_party_number
542 FROM ja_cn_account_balances jcab, per_people_f ppf --choli changed for bug 11774438
543 WHERE jcab.legal_entity_id = pn_legal_entitliy_id
544 AND jcab.ledger_id = pn_ledger_id
545 AND jcab.period_name = pv_period_name
546 AND ppf.person_id(+) = jcab.personnel_id; --choli added for bug 11774438
547
548 CURSOR cur_setups IS
549 SELECT sam.subsidiary_segment_code,
550 sam.sources_code,
551 sam.context_code
552 FROM ja_cn_sub_acc_mapping sam
553 WHERE (chart_of_accounts_id = pn_chart_account_id)
554 AND context_code in
555 ('CUSTOMER',
556 'SUPPLIER',
557 'EMPLOYEE_SUPPLIER',
558 (SELECT application_column_name
559 FROM fnd_segment_attribute_values
560 WHERE segment_attribute_type = 'FA_COST_CTR'
561 AND attribute_value = 'Y'
562 AND (id_flex_num = pn_chart_account_id)
563 AND (id_flex_code = 'GL#')
564 AND (application_id = 101)));
565 BEGIN
566 --logging for debug
567
568 IF (ln_proc_level >= ln_dbg_level) THEN
569 FND_LOG.STRING(ln_proc_level,
570 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
571 '.begin',
572 'Enter procedure');
573 END IF; --ln_proc_level>=ln_dbg_level
574 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
575 FND_FILE.put_line(FND_FILE.log,
576 lv_procedure_name || '.parameters:' ||
577 'pn_chart_account_id=' || pn_chart_account_id || ',' ||
578 'pv_period_name=' || pv_period_name || ',' ||
579 'pn_ledger_id=' || pn_ledger_id || ',' ||
580 'pn_legal_entitliy_id=' || pn_legal_entitliy_id);
581
582 SELECT application_column_name
583 INTO lv_costcenter_column
584 FROM fnd_segment_attribute_values
585 WHERE segment_attribute_type = 'FA_COST_CTR'
586 AND attribute_value = 'Y'
587 AND (id_flex_num = pn_chart_account_id)
588 AND (id_flex_code = 'GL#')
589 AND (application_id = 101);
590 FND_FILE.put_line(FND_FILE.log,
591 lv_procedure_name || 'lv_costcenter_column = ' ||
592 lv_costcenter_column);
593 BEGIN
594 FOR v_row IN cur_setups LOOP
595 IF (v_row.sources_code = 'SLA' AND v_row.context_code = 'CUSTOMER') THEN
596 lv_customer_seg := v_row.subsidiary_segment_code;
597 ELSIF (v_row.sources_code = 'SLA' AND
598 v_row.context_code = 'SUPPLIER') THEN
599 lv_supplier_seg := v_row.subsidiary_segment_code;
600 ELSIF (v_row.sources_code = 'SLA' AND
601 v_row.context_code = 'EMPLOYEE_SUPPLIER') THEN
602 lv_employee_seg := v_row.subsidiary_segment_code;
603 ELSIF (v_row.sources_code = 'COA' AND
604 v_row.context_code = lv_costcenter_column) THEN
605 lv_costcenter_seg := v_row.subsidiary_segment_code;
606 END IF;
607 END LOOP;
608 EXCEPTION
609 WHEN OTHERS THEN
610 FND_FILE.put_line(FND_FILE.log,
611 lv_procedure_name || SQLCODE || SQLERRM);
612 RAISE;
613 END;
614 FND_FILE.put_line(FND_FILE.log,
615 lv_procedure_name || 'lv_customer_seg = ' ||
616 lv_customer_seg);
617 FND_FILE.put_line(FND_FILE.log,
618 lv_procedure_name || 'lv_supplier_seg = ' ||
619 lv_supplier_seg);
620 FND_FILE.put_line(FND_FILE.log,
621 lv_procedure_name || 'lv_employee_seg = ' ||
622 lv_employee_seg);
623 FND_FILE.put_line(FND_FILE.log,
624 lv_procedure_name || 'lv_costcenter_seg = ' ||
625 lv_costcenter_seg);
626 \*SELECT count(*)--Comment by Jianchao Chi for bug 11683977
627 INTO ln_Subsidiary_number
628 FROM ja_cn_sub_acc_mapping sam
629 WHERE (chart_of_accounts_id = pn_chart_account_id)
630 and context_code in
631 ('CUSTOMER', 'SUPPLIER', 'EMPLOYEE_SUPPLIER',
632 (SELECT application_column_name
633 FROM fnd_segment_attribute_values
634 WHERE segment_attribute_type = 'FA_COST_CTR'
635 AND attribute_value = 'Y'
636 AND (id_flex_num = pn_chart_account_id)
637 AND (id_flex_code = 'GL#')
638 AND (application_id = 101)));
639 FND_FILE.put_line(FND_FILE.log,
640 lv_procedure_name || 'ln_Subsidiary_number = ' || ln_Subsidiary_number);
641 IF (ln_Subsidiary_number < 4) THEN
642 Fnd_Message.Set_Name(Application => 'JA',
643 NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
644 lv_Error_Msg := Fnd_Message.Get;
645 --Output error message
646 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
647 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
648 Message => lv_Error_Msg);
649 --popup error message JA_CN_MISSING_SUBSIDIARY_SETUP
650 ELSE*\
651 -- update JA_CN_JOURNAL_LINES
652 FOR v_line IN cur_itemiz_lines LOOP
653 IF (v_line.third_party_type IS NOT NULL AND
654 v_line.third_party_type = 'C') THEN
655 -- Construct the update sql for Itemazation data migration
656 lv_update_sql := REPLACE(lv_update_template_sql,
657 '$to_column$',
658 lv_customer_seg);
659 lv_update_sql := REPLACE(lv_update_sql,
660 '$from_column$',
661 -- choli change the follow line to use third_party_number instead of third_party_id
662 -- to fix bug 11664438
663 '''' || v_line.third_party_number || '''');
664
665 EXECUTE IMMEDIATE lv_update_sql
666 USING v_line.rowid;
667
668 ELSIF (v_line.third_party_type IS NOT NULL AND
669 v_line.third_party_type = 'S') THEN
670 -- Construct the update sql for Itemazation data migration
671 lv_update_sql := REPLACE(lv_update_template_sql,
672 '$to_column$',
673 lv_supplier_seg);
674 lv_update_sql := REPLACE(lv_update_sql,
675 '$from_column$',
676 -- choli change the follow line to use third_party_number instead of third_party_id
677 -- to fix bug 11664438
678 '''' || v_line.third_party_number || '''');
679
680 EXECUTE IMMEDIATE lv_update_sql
681 USING v_line.rowid;
682 END IF;
683
684 IF (v_line.employee_number IS NOT NULL) THEN
685 lv_update_sql := REPLACE(lv_update_template_sql,
686 '$to_column$',
687 lv_employee_seg);
688 lv_update_sql := REPLACE(lv_update_sql,
689 '$from_column$',
690 '''' || v_line.employee_number || '''');
691
692 EXECUTE IMMEDIATE lv_update_sql
693 USING v_line.rowid;
694 END IF;
695 -- Construct the update sql for Itemazation data migration
696 IF (v_line.cost_center IS NOT NULL) THEN
697 lv_update_sql := REPLACE(lv_update_template_sql,
698 '$to_column$',
699 lv_costcenter_seg);
700 lv_update_sql := REPLACE(lv_update_sql,
701 '$from_column$',
702 '''' || v_line.cost_center || '''');
703
704 EXECUTE IMMEDIATE lv_update_sql
705 USING v_line.rowid;
706 END IF;
707 END LOOP; -- FOR v_line IN cur_itemiz_lines LOOP
708
709 FOR v_line IN cur_balances LOOP
710 IF (v_line.third_party_type IS NOT NULL AND
711 v_line.third_party_type = 'C') THEN
712 -- Construct the update sql for Itemazation data migration
713 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
714 '$to_column$',
715 lv_customer_seg);
716 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
717 '$from_column$',
718 -- choli change the follow line to use third_party_number instead of third_party_id
719 -- to fix bug 11664438
720 '''' || v_line.third_party_number || '''');
721 lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
722 lv_customer_seg || ' IS NULL ';
723 EXECUTE IMMEDIATE lv_bal_update_sql
724 USING v_line.rowid;
725
726 ELSIF (v_line.third_party_type IS NOT NULL AND
727 v_line.third_party_type = 'S') THEN
728 -- Construct the update sql for Itemazation data migration
729 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
730 '$to_column$',
731 lv_supplier_seg);
732 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
733 '$from_column$',
734 -- choli change the follow line to use third_party_number instead of third_party_id
735 -- to fix bug 11664438
736 '''' || v_line.third_party_number || '''');
737 lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
738 lv_supplier_seg || ' IS NULL ';
739 EXECUTE IMMEDIATE lv_bal_update_sql
740 USING v_line.rowid;
741 END IF;
742
743 IF (v_line.employee_number IS NOT NULL) THEN
744 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
745 '$to_column$',
746 lv_employee_seg);
747 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
748 '$from_column$',
749 '''' || v_line.employee_number || '''');
750 lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
751 lv_employee_seg || ' IS NULL ';
752 EXECUTE IMMEDIATE lv_bal_update_sql
753 USING v_line.rowid;
754 END IF;
755 -- Construct the update sql for Itemazation data migration
756 IF (v_line.cost_center IS NOT NULL) THEN
757 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
758 '$to_column$',
759 lv_costcenter_seg);
760 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
761 '$from_column$',
762 '''' || v_line.cost_center || '''');
763 lv_bal_update_sql := lv_bal_update_sql || ' AND ' ||
764 lv_costcenter_seg || ' IS NULL ';
765 EXECUTE IMMEDIATE lv_bal_update_sql
766 USING v_line.rowid;
767 END IF;
768 END LOOP; -- FOR v_line IN cur_balances LOOP
769
770 -- Update currency_conversion_type
771 UPDATE JA_CN_JOURNAL_LINES jc
772 SET currency_conversion_type =
773 (SELECT DISTINCT NVL(Ael.Currency_Conversion_Type,
774 Jeh.Currency_Conversion_Type) currency_conversion_type
775 FROM Gl_Je_Headers Jeh,
776 Xla_Ae_Lines Ael,
777 Gl_Import_References Gir,
778 JA_CN_JOURNAL_LINES jcjl
779 WHERE Jeh.Je_Header_Id = jcjl.je_header_id
780 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id(+)
781 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table(+)
782 AND Gir.Je_Line_Num(+) = jcjl.Je_Line_Num
783 AND Gir.Je_Header_Id(+) = jcjl.Je_Header_Id
784 AND jc.Je_Header_Id = jcjl.je_header_id
785 --Add by Jianchao Chi for bug 10634017
786 AND jc.Je_Line_Num = jcjl.Je_Line_Num)
787 WHERE jc.Ledger_Id = pn_ledger_id
788 AND jc.Legal_Entity_Id = pn_legal_entitliy_id;
789 -- choli fix bug 12349159, move the related setup check into MigrateHisDateDE
790 -- Handle project related update
791 -- update JA_CN_JOURNAL_LINES
792 IF (lb_need_Update) THEN
793 FOR v_line IN cur_itemiz_lines LOOP
794 -- Construct the update sql for Itemazation data migration
795 lv_update_sql := REPLACE(lv_update_template_sql,
796 '$to_column$',
797 lv_prject_seg);
798 lv_update_sql := REPLACE(lv_update_sql,
799 '$from_column$',
800 '''' || v_line.project_number || '''');
801
802 EXECUTE IMMEDIATE lv_update_sql
803 USING v_line.rowid;
804
805 END LOOP; --FOR v_line IN cur_itemiz_lines LOOP
806
807 FOR v_line IN cur_balances LOOP
808 -- Construct the update sql for Itemazation data migration
809 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
810 '$to_column$',
811 lv_prject_seg);
812 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
813 '$from_column$',
814 '''' || v_line.project_number || '''');
815 lv_bal_update_sql := lv_bal_update_sql || ' AND ' || lv_prject_seg ||
816 ' IS NULL ';
817 EXECUTE IMMEDIATE lv_bal_update_sql
818 USING v_line.rowid;
819
820 END LOOP; --v_line IN cur_balances LOOP
821
822 END IF; --(lb_need_Update) THEN
823 --END IF; --(ln_Subsidiary_number < 4) --Comment by Jianchao Chi for bug 11683977
824 --logging for debug
825 IF (ln_proc_level >= ln_dbg_level) THEN
826 FND_LOG.STRING(ln_proc_level,
827 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
828 'Exit procedure');
829 END IF; -- (ln_proc_level>=ln_dbg_level)
830 END UpdateItemizationlines;*/
831
832 --==========================================================================
833 -- FUNCTION NAME:
834 --
835 -- MigrateHisData Public
836 --
837 -- DESCRIPTION:
838 --
839 -- This procedure is main program, used to migrate all the historic data
840 -- by invoking above sub programs, as well as the voucher number migration
841 -- program.
842 --
843 --
844 -- PARAMETERS:
845 -- Out: Errbuf Error buffer
846 -- Out: Retcode Error code
847 -- In : P_COA_ID Chart of account ID
848 -- In: P_RESP_APPLICATION_ID Application ID
849 -- In: P_LEGAL_ENTITY_TEMP Legal entity temp
850 -- In : P_PROFILE_OPTION_VALUE Profile Option Value
851 -- In: P_LEDGER_ID Leger Id
852 -- In: P_LEDGER_NAME Leger Name
853 -- In : P_LEGAL_ENTITY_ID Legal entity ID
854 -- In: P_LEGAL_ENTITY Legal entity Name
855
856 --
857 -- DESIGN REFERENCES:
858 --
859 --
860 -- CHANGE HISTORY:
861 --
862 -- 05-Jan-2011 Chongwu Li Created
863 -- 19-Jan-2011 Chongwu Li Updated, fix bug 11655280
864 -- 28-Jan-2011 Jianchao Chi Updated, fix bug 11691910
865 -- 11-Feb-2011 Jianchao Chi Updated for bug 11683977
866 --
867 --===========================================================================
868 /*PROCEDURE MigrateHisData(Errbuf OUT NOCOPY VARCHAR2,
869 Retcode OUT NOCOPY VARCHAR2,
870 P_COA_ID IN NUMBER,
871 P_RESP_APPLICATION_ID IN NUMBER,
872 P_LEGAL_ENTITY_TEMP IN NUMBER,
873 P_PROFILE_OPTION_VALUE IN NUMBER,
874 P_LEDGER_ID IN NUMBER,
875 P_LEDGER_NAME IN VARCHAR2,
876 P_LEGAL_ENTITY_ID IN NUMBER,
877 P_LEGAL_ENTITY IN VARCHAR2) IS
878 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
879 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
880 lv_procedure_name VARCHAR2(40) := 'MigrateHisData';
881 lv_error_flag VARCHAR2(5);
882 lb_Error_Status BOOLEAN;
883
884 --Following 4 variables are added by Jianchao Chi for bug 11691910
885 ln_glcashlines_amount NUMBER := 0;
886 ln_glitemlines_amount NUMBER := 0;
887 ln_itemlines_amount NUMBER := 0;
888 lv_output_Msg VARCHAR2(4000);
889
890 --Following 2 variables are added by Jianchao Chi for bug 11683977
891 ln_Subsidiary_number NUMBER;
892 lv_Error_Msg VARCHAR2(4000);
893 lb_Msg_Tag BOOLEAN := TRUE;
894 lb_need_update BOOLEAN := TRUE;
895 CURSOR cur_periods IS
896 SELECT DISTINCT period_name
897 FROM ja_cn_journal_lines
898 WHERE ledger_id = P_LEDGER_ID
899 AND legal_entity_id = P_LEGAL_ENTITY_ID
900 AND journal_number IS NOT NULL;
901
902 BEGIN
903 --logging for debug
904 IF (ln_proc_level >= ln_dbg_level) THEN
905 FND_LOG.STRING(ln_proc_level,
906 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
907 '.begin',
908 'Enter procedure');
909 END IF; --ln_proc_level>=ln_dbg_level
910 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
911 FND_FILE.put_line(FND_FILE.log,
912 lv_procedure_name || '.parameters:' || 'P_COA_ID=' ||
913 P_COA_ID || ',' || 'P_LEDGER_ID=' || P_LEDGER_ID || ',' ||
914 'P_LEGAL_ENTITY_ID=' || P_LEGAL_ENTITY_ID);
915
916 SELECT count(*) -- Moved from UpdateItemizationlines function by Jianchao Chi for bug 11683977
917 INTO ln_Subsidiary_number
918 FROM ja_cn_sub_acc_mapping sam
919 WHERE (chart_of_accounts_id = P_COA_ID)
920 and context_code in
921 ('CUSTOMER',
922 'SUPPLIER',
923 'EMPLOYEE_SUPPLIER',
924 (SELECT application_column_name
925 FROM fnd_segment_attribute_values
926 WHERE segment_attribute_type = 'FA_COST_CTR'
927 AND attribute_value = 'Y'
928 AND (id_flex_num = P_COA_ID)
929 AND (id_flex_code = 'GL#')
930 AND (application_id = 101)));
931 FND_FILE.put_line(FND_FILE.log,
932 lv_procedure_name || 'ln_Subsidiary_number = ' ||
933 ln_Subsidiary_number);
934 IF (ln_Subsidiary_number < 4) THEN
935 Fnd_Message.Set_Name(Application => 'JA',
936 NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
937 lv_Error_Msg := Fnd_Message.Get;
938 --Output error message
939 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
940 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
941 Message => lv_Error_Msg);
942 END IF;
943
944 FOR v_line IN cur_periods LOOP
945
946 BEGIN
947 JA_CN_VOUCHER_NUM_MIG_PKG.Migrate_voucher_number(P_LEDGER_ID,
948 P_LEGAL_ENTITY_ID,
949 v_line.period_name);
950 --Invoke sub program UpdateGLCashlines to update GL_JE_LINES for cash items;
951 ln_glcashlines_amount := ln_glcashlines_amount + -- Add by Jianchao Chi for bug 11691910
952 UpdateGLCashlines(P_COA_ID,
953 P_LEGAL_ENTITY_ID,
954 P_LEDGER_ID,
955 P_LEDGER_NAME,
956 P_LEGAL_ENTITY,
957 v_line.period_name);
958
959 --Invoke sub program UpdateGLCashlines to update GL_JE_LINES for itemization items;
960
961 UpdateGLItemizationlines(P_LEGAL_ENTITY_ID,
962 P_LEDGER_ID,
963 v_line.period_name);
964
965 --Invoke sub program UpdateGLCashlines to update JA_CN_JOURNAL_LINES for itemization items;
966 IF (ln_Subsidiary_number >= 4) THEN
967 -- Add by Jianchao Chi for bug 11683977
968 UpdateItemizationlines(P_COA_ID,
969 P_LEGAL_ENTITY_ID,
970 P_LEDGER_ID,
971 v_line.period_name,
972 --lb_Msg_Tag,
973 lb_need_update);
974 lb_Msg_Tag := FALSE;
975 END IF;
976 -- Chongwu Li Updated, fix bug 11655280
977 \* UpdateBalances(P_COA_ID
978 ,P_LEGAL_ENTITY_ID
979 ,P_LEDGER_ID); *\
980 COMMIT;
981
982 --logging for debug
983 IF (ln_proc_level >= ln_dbg_level) THEN
984 FND_LOG.STRING(ln_proc_level,
985 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
986 '.end',
987 'Exit procedure');
988 END IF; -- (ln_proc_level>=ln_dbg_level)
989 EXCEPTION
990 WHEN OTHERS THEN
991 FND_FILE.put_line(FND_FILE.log,
992 lv_procedure_name || SQLCODE || SQLERRM);
993 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
994 Message => lv_procedure_name ||
995 SQLCODE ||
996 SQLERRM);
997 ROLLBACK;
998 END;
999 END LOOP;
1000
1001 --Added by Jianchao Chi for bug 11691910
1002 Fnd_Message.Set_Name(Application => 'JA',
1003 NAME => 'JA_CN_DATA_MIGRATION_RESULT');
1004 Fnd_Message.SET_TOKEN(TOKEN => 'LEGAL_ENTITY_NAME',
1005 VALUE => P_LEGAL_ENTITY);
1006 Fnd_Message.SET_TOKEN(TOKEN => 'LEGER_NAME', VALUE => P_LEDGER_NAME);
1007 Fnd_Message.SET_TOKEN(TOKEN => 'MIGRATED_JOURNAL_NUM',
1008 VALUE => ln_glcashlines_amount);
1009 lv_output_Msg := Fnd_Message.Get;
1010
1011 --Output error message
1012 Fnd_File.Put_Line(Fnd_File.Output, lv_output_Msg);
1013 END MigrateHisData;*/
1014
1015 --==========================================================================
1016 -- FUNCTION NAME:
1017 --
1018 -- MigrateHisDataCFS Public
1019 --
1020 -- DESCRIPTION:
1021 --
1022 -- This procedure is main program, used to migrate all the historic data
1023 -- by invoking above sub programs, as well as the voucher number migration
1024 -- program.
1025 --
1026 --
1027 -- PARAMETERS:
1028 -- Out: Errbuf Error buffer
1029 -- Out: Retcode Error code
1030 -- In : P_COA_ID Chart of account ID
1031 -- In: P_RESP_APPLICATION_ID Application ID
1032 -- In: P_LEGAL_ENTITY_TEMP Legal entity temp
1033 -- In : P_PROFILE_OPTION_VALUE Profile Option Value
1034 -- In: P_LEDGER_ID Leger Id
1035 -- In: P_LEDGER_NAME Leger Name
1036 -- In : P_LEGAL_ENTITY_ID Legal entity ID
1037 -- In: P_LEGAL_ENTITY Legal entity Name
1038
1039 --
1040 -- DESIGN REFERENCES:
1041 --
1042 --
1043 -- CHANGE HISTORY:
1044 --
1045 -- 22-Mar-2011 Chongwu Li Created
1046 -- 19-Apr-2011 Chongwu updated, bug 12349128
1047 -- 21-Apr-2011 Jianchao Chi update for bug 12353563
1048 --
1049 --===========================================================================
1050 PROCEDURE MigrateHisDataCFS(Errbuf OUT NOCOPY VARCHAR2,
1051 Retcode OUT NOCOPY VARCHAR2,
1052 P_COA_ID IN NUMBER,
1053 P_RESP_APPLICATION_ID IN NUMBER,
1054 P_LEGAL_ENTITY_TEMP IN NUMBER,
1055 P_PROFILE_OPTION_VALUE IN NUMBER,
1056 P_LEDGER_ID IN NUMBER,
1057 P_LEDGER_NAME IN VARCHAR2,
1058 P_LEGAL_ENTITY_ID IN NUMBER,
1059 P_LEGAL_ENTITY IN VARCHAR2) IS
1060 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1061 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1062 lv_procedure_name VARCHAR2(40) := 'MigrateHisDataCFS';
1063 lv_error_flag VARCHAR2(5);
1064 lb_Error_Status BOOLEAN;
1065 ln_glcashlines_amount NUMBER := 0;
1066 lv_output_Msg VARCHAR2(4000);
1067
1068 CURSOR cur_periods IS
1069 -- choli fix bug 12349128
1070 /* SELECT DISTINCT period_name
1071 FROM ja_cn_journal_lines
1072 WHERE ledger_id = P_LEDGER_ID
1073 AND legal_entity_id = P_LEGAL_ENTITY_ID
1074 AND journal_number IS NOT NULL;*/
1075
1076 SELECT DISTINCT period_name
1077 FROM gl_je_lines
1078 WHERE ledger_id = P_LEDGER_ID;
1079
1080
1081 BEGIN
1082 --logging for debug
1083 IF (ln_proc_level >= ln_dbg_level) THEN
1084 FND_LOG.STRING(ln_proc_level,
1085 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1086 '.begin',
1087 'Enter procedure');
1088 END IF; --ln_proc_level>=ln_dbg_level
1089 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
1090 FND_FILE.put_line(FND_FILE.log,
1091 lv_procedure_name || '.parameters:' || 'P_COA_ID=' ||
1092 P_COA_ID || ',' || 'P_LEDGER_ID=' || P_LEDGER_ID || ',' ||
1093 'P_LEGAL_ENTITY_ID=' || P_LEGAL_ENTITY_ID);
1094
1095
1096 FOR v_line IN cur_periods LOOP
1097 BEGIN
1098 --Invoke sub program UpdateGLCashlines to update GL_JE_LINES for cash items;
1099 ln_glcashlines_amount := ln_glcashlines_amount +
1100 UpdateGLCashlines(P_COA_ID,
1101 P_LEGAL_ENTITY_ID,
1102 P_LEDGER_ID,
1103 P_LEDGER_NAME,
1104 P_LEGAL_ENTITY,
1105 v_line.period_name);
1106
1107 COMMIT;
1108
1109 --Start bug 12353563 by jianchao chi --
1110 UpdateSLAHeaderLine(P_LEGAL_ENTITY_ID,
1111 P_LEDGER_ID,
1112 P_LEDGER_NAME,
1113 P_LEGAL_ENTITY,
1114 v_line.period_name);
1115 COMMIT;
1116 --End bug 12353563 by jianchao chi --
1117
1118 --logging for debug
1119 IF (ln_proc_level >= ln_dbg_level) THEN
1120 FND_LOG.STRING(ln_proc_level,
1121 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1122 '.end',
1123 'Exit procedure');
1124 END IF; -- (ln_proc_level>=ln_dbg_level)
1125 EXCEPTION
1126 WHEN OTHERS THEN
1127 FND_FILE.put_line(FND_FILE.log,
1128 lv_procedure_name || SQLCODE || SQLERRM);
1129 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
1130 Message => lv_procedure_name ||
1131 SQLCODE ||
1132 SQLERRM);
1133 ROLLBACK;
1134 END;
1135 END LOOP;
1136
1137 --Added by Jianchao Chi for bug 11691910
1138 Fnd_Message.Set_Name(Application => 'JA',
1139 NAME => 'JA_CN_DATA_MIGRATION_RESULT');
1140 Fnd_Message.SET_TOKEN(TOKEN => 'LEGAL_ENTITY_NAME',
1141 VALUE => P_LEGAL_ENTITY);
1142 Fnd_Message.SET_TOKEN(TOKEN => 'LEGER_NAME', VALUE => P_LEDGER_NAME);
1143 Fnd_Message.SET_TOKEN(TOKEN => 'MIGRATED_JOURNAL_NUM',
1144 VALUE => ln_glcashlines_amount);
1145 lv_output_Msg := Fnd_Message.Get;
1146
1147 --Output error message
1148 Fnd_File.Put_Line(Fnd_File.Output, lv_output_Msg);
1149 END MigrateHisDataCFS;
1150
1151 --==========================================================================
1152 -- FUNCTION NAME:
1153 --
1154 -- MigrateHisDataDE Public
1155 --
1156 -- DESCRIPTION:
1157 --
1158 -- This procedure is main program, used to migrate all the historic data
1159 -- by invoking above sub programs, as well as the voucher number migration
1160 -- program.
1161 --
1162 --
1163 -- PARAMETERS:
1164 -- Out: Errbuf Error buffer
1165 -- Out: Retcode Error code
1166 -- In : P_COA_ID Chart of account ID
1167 -- In: P_RESP_APPLICATION_ID Application ID
1168 -- In: P_LEGAL_ENTITY_TEMP Legal entity temp
1169 -- In : P_PROFILE_OPTION_VALUE Profile Option Value
1170 -- In: P_LEDGER_ID Leger Id
1171 -- In: P_LEDGER_NAME Leger Name
1172 -- In : P_LEGAL_ENTITY_ID Legal entity ID
1173 -- In: P_LEGAL_ENTITY Legal entity Name
1174
1175 --
1176 -- DESIGN REFERENCES:
1177 --
1178 --
1179 -- CHANGE HISTORY:
1180 --
1181 -- 22-Mar-2011 Chongwu Li Created
1182 -- 11-Apr-2011 Chongwu Li fix bug 12344625
1183 -- 13-Apr-2011 Chongwu Li fix bug 12349159
1184 -- 19-Apr-2011 Chongwu updated, bug 12349128
1185 --
1186 --===========================================================================
1187 PROCEDURE MigrateHisDataDE(Errbuf OUT NOCOPY VARCHAR2,
1188 Retcode OUT NOCOPY VARCHAR2,
1189 P_COA_ID IN NUMBER,
1190 P_RESP_APPLICATION_ID IN NUMBER,
1191 P_LEGAL_ENTITY_TEMP IN NUMBER,
1192 P_PROFILE_OPTION_VALUE IN NUMBER,
1193 P_LEDGER_ID IN NUMBER,
1194 P_LEDGER_NAME IN VARCHAR2,
1195 P_LEGAL_ENTITY_ID IN NUMBER,
1196 P_LEGAL_ENTITY IN VARCHAR2) IS
1197 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1198 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1199 lv_procedure_name VARCHAR2(40) := 'MigrateHisDataDE';
1200 lv_error_flag VARCHAR2(5);
1201 lb_Error_Status BOOLEAN;
1202
1203 ln_glitemlines_amount NUMBER := 0;
1204 ln_itemlines_amount NUMBER := 0;
1205 lv_output_Msg VARCHAR2(4000);
1206
1207 ln_Subsidiary_number NUMBER;
1208 lv_Error_Msg VARCHAR2(4000);
1209 --lb_Msg_Tag BOOLEAN := TRUE;
1210 lb_need_update BOOLEAN := TRUE;
1211
1212 lv_project_sourrce_flag VARCHAR2(40);
1213 lv_prject_coa_seg VARCHAR2(40);
1214 lv_prject_project_seg VARCHAR2(40);
1215 lv_prject_seg VARCHAR2(40);
1216
1217 CURSOR cur_periods IS
1218 -- choli fix bug 12349128
1219 /* SELECT DISTINCT period_name
1220 FROM ja_cn_journal_lines
1221 WHERE ledger_id = P_LEDGER_ID
1222 AND legal_entity_id = P_LEGAL_ENTITY_ID
1223 AND journal_number IS NOT NULL;*/
1224
1225 SELECT DISTINCT period_name
1226 FROM gl_je_lines
1227 WHERE ledger_id = P_LEDGER_ID;
1228
1229 BEGIN
1230 --logging for debug
1231 IF (ln_proc_level >= ln_dbg_level) THEN
1232 FND_LOG.STRING(ln_proc_level,
1233 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1234 '.begin',
1235 'Enter procedure');
1236 END IF; --ln_proc_level>=ln_dbg_level
1237 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
1238 FND_FILE.put_line(FND_FILE.log,
1239 lv_procedure_name || '.parameters:' || 'P_COA_ID=' ||
1240 P_COA_ID || ',' || 'P_LEDGER_ID=' || P_LEDGER_ID || ',' ||
1241 'P_LEGAL_ENTITY_ID=' || P_LEGAL_ENTITY_ID);
1242
1243 SELECT count(*) -- Moved from UpdateItemizationlines function by Jianchao Chi for bug 11683977
1244 INTO ln_Subsidiary_number
1245 FROM ja_cn_sub_acc_mapping sam
1246 WHERE (chart_of_accounts_id = P_COA_ID)
1247 and context_code in
1248 ('CUSTOMER',
1249 'SUPPLIER',
1250 'EMPLOYEE_SUPPLIER',
1251 (SELECT application_column_name
1252 FROM fnd_segment_attribute_values
1253 WHERE segment_attribute_type = 'FA_COST_CTR'
1254 AND attribute_value = 'Y'
1255 AND (id_flex_num = P_COA_ID)
1256 AND (id_flex_code = 'GL#')
1257 AND (application_id = 101)));
1258 FND_FILE.put_line(FND_FILE.log,
1259 lv_procedure_name || 'ln_Subsidiary_number = ' ||
1260 ln_Subsidiary_number);
1261 IF (ln_Subsidiary_number < 4) THEN
1262 lb_need_Update := FALSE;
1263 Fnd_Message.Set_Name(Application => 'JA',
1264 NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
1265 lv_Error_Msg := Fnd_Message.Get;
1266 --Output error message
1267 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1268 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1269 Message => lv_Error_Msg);
1270 END IF;
1271
1272 -- choli fix bug 12349159, move the related setup check from UpdateItemizationLines
1273 -- Handle the project related migration setup check.
1274 SELECT project_source_flag --, project_ac_code, coa_segment
1275 INTO lv_project_sourrce_flag --, lv_project_ac_code, lv_coa_segment
1276 FROM ja_cn_sub_acc_sources_all
1277 WHERE chart_of_accounts_id = P_COA_ID;
1278
1279 BEGIN
1280 SELECT sam.subsidiary_segment_code
1281 INTO lv_prject_project_seg
1282 FROM ja_cn_sub_acc_mapping sam
1283 WHERE (sam.chart_of_accounts_id = P_COA_ID)
1284 AND sam.context_code = 'PROJECT_NUMBER'
1285 AND sam.sources_code = 'PROJECT MODULE';
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 lv_prject_project_seg := NULL;
1289 END;
1290 FND_FILE.put_line(FND_FILE.log,
1291 lv_procedure_name || 'lv_prject_project_seg = ' ||
1292 lv_prject_project_seg);
1293 BEGIN
1294
1295 SELECT sam.subsidiary_segment_code
1296 INTO lv_prject_coa_seg
1297 FROM ja_cn_sub_acc_mapping sam
1298 WHERE sam.sources_code = 'COA'
1299 AND sam.chart_of_accounts_id = P_COA_ID
1300 AND sam.context_code IN
1301 (SELECT application_column_name
1302 FROM fnd_segment_attribute_values
1303 WHERE attribute_value = 'Y'
1304 AND (id_flex_num = P_COA_ID)
1305 AND (id_flex_code = 'GL#')
1306 AND (application_id = 101)
1307 AND application_column_name NOT IN
1308 (SELECT application_column_name
1309 FROM fnd_segment_attribute_values
1310 WHERE attribute_value = 'Y'
1311 AND (id_flex_num = P_COA_ID)
1312 AND (id_flex_code = 'GL#')
1313 AND (application_id = 101)
1314 and segment_attribute_type IN
1315 ('FA_COST_CTR', 'GL_BALANCING', 'GL_ACCOUNT')));
1316 EXCEPTION
1317 WHEN NO_DATA_FOUND THEN
1318 lv_prject_coa_seg := NULL;
1319 WHEN TOO_MANY_ROWS THEN
1320 Fnd_Message.Set_Name(Application => 'JA',
1321 NAME => 'JA_CN_ERROR_SUBSIDIARY_SETUP');
1322 lv_Error_Msg := Fnd_Message.Get;
1323 --Output error message
1324 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1325 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1326 Message => lv_Error_Msg);
1327 lb_need_Update := FALSE;
1328 END;
1329 FND_FILE.put_line(FND_FILE.log,
1330 lv_procedure_name || 'lv_prject_coa_seg = ' ||
1331 lv_prject_coa_seg);
1332 IF (lv_prject_coa_seg IS NOT NULL AND lv_prject_project_seg IS NOT NULL) THEN
1333
1334 Fnd_Message.Set_Name(Application => 'JA',
1335 NAME => 'JA_CN_ERROR_SUBSIDIARY_SETUP');
1336 lv_Error_Msg := Fnd_Message.Get;
1337 --Output error message
1338 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1339 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1340 Message => lv_Error_Msg);
1341
1342 -- popup JA_CN_ERROR_SUBSIDIARY_SETUP
1343 lb_need_Update := FALSE;
1344 END IF;
1345
1346 FND_FILE.put_line(FND_FILE.log,
1347 lv_procedure_name || 'lv_project_sourrce_flag = ' ||
1348 lv_project_sourrce_flag);
1349 /* FND_FILE.put_line(FND_FILE.log,
1350 lv_procedure_name || 'lv_project_ac_code = ' || lv_project_ac_code);
1351 FND_FILE.put_line(FND_FILE.log,
1352 lv_procedure_name || 'lv_coa_segment = ' || lv_coa_segment);*/
1353 IF (lb_need_Update) THEN
1354 -- Chongwu update for Project solution changes by anita FDD 0.6
1355 IF (lv_project_sourrce_flag = 'PA') THEN
1356 IF (lv_prject_coa_seg IS NULL AND lv_prject_project_seg IS NULL) THEN
1357
1358 Fnd_Message.Set_Name(Application => 'JA',
1359 NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
1360 lv_Error_Msg := Fnd_Message.Get;
1361 --Output error message
1362 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1363 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1364 Message => lv_Error_Msg);
1365
1366 -- popup JA_CN_ MISSING_SUBSIDIARY_SETUP
1367 lb_need_Update := FALSE;
1368 ELSIF (lv_prject_coa_seg IS NOT NULL AND
1369 lv_prject_project_seg IS NULL) THEN
1370
1371 Fnd_Message.Set_Name(Application => 'JA',
1372 NAME => 'JA_CN_ERROR_SUBSIDIARY_SETUP');
1373 lv_Error_Msg := Fnd_Message.Get;
1374 --Output error message
1375 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1376 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1377 Message => lv_Error_Msg);
1378
1379 -- popup JA_CN_ERROR_SUBSIDIARY_SETUP
1380 lb_need_Update := FALSE;
1381 ELSIF (lv_prject_coa_seg IS NULL AND
1382 lv_prject_project_seg IS NOT NULL) THEN
1383 lv_prject_seg := lv_prject_project_seg;
1384 END IF;
1385 -- Chongwu update for Project solution changes by anita FDD 0.6
1386 ELSIF (lv_project_sourrce_flag = 'COA') THEN
1387 IF (lv_prject_coa_seg IS NULL AND lv_prject_project_seg IS NULL) THEN
1388
1389 Fnd_Message.Set_Name(Application => 'JA',
1390 NAME => 'JA_CN_MISSING_SUBSIDIARY_SETUP');
1391 lv_Error_Msg := Fnd_Message.Get;
1392 --Output error message
1393 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1394 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1395 Message => lv_Error_Msg);
1396
1397 -- popup JA_CN_MISSING_SUBSIDIARY_SETUP
1398 lb_need_Update := FALSE;
1399 ELSIF (lv_prject_coa_seg IS NULL AND
1400 lv_prject_project_seg IS NOT NULL) THEN
1401
1402 Fnd_Message.Set_Name(Application => 'JA',
1403 NAME => 'JA_CN_ERROR_SUBSIDIARY_SETUP');
1404 lv_Error_Msg := Fnd_Message.Get;
1405 --Output error message
1406 Fnd_File.Put_Line(Fnd_File.Output, lv_Error_Msg);
1407 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'WARNING',
1408 Message => lv_Error_Msg);
1409
1410 -- popup JA_CN_INCORRECT_SUBSIDIARY_SETUP
1411 lb_need_Update := FALSE;
1412 ELSIF (lv_prject_coa_seg IS NOT NULL AND
1413 lv_prject_project_seg IS NULL) THEN
1414 lv_prject_seg := lv_prject_coa_seg;
1415 END IF;
1416 -- Chongwu Li Add follow two lines for fixing bug 11659281
1417 -- Chongwu update for Project solution changes by anita FDD 0.6
1418 ELSIF (lv_project_sourrce_flag = 'N') THEN
1419 lb_need_Update := FALSE;
1420 END IF; -- (lv_project_sourrce_flag ='PA') THEN
1421 END IF; --(lb_need_Update) THEN
1422 FND_FILE.put_line(FND_FILE.log,
1423 lv_procedure_name || 'lv_prject_seg = ' ||
1424 lv_prject_seg);
1425
1426 -- choli fix bug 12349159, move the related setup check from UpdateItemizationLines
1427
1428
1429
1430 FOR v_line IN cur_periods LOOP
1431
1432 BEGIN
1433 JA_CN_VOUCHER_NUM_MIG_PKG.Migrate_voucher_number(P_LEDGER_ID,
1434 P_LEGAL_ENTITY_ID,
1435 v_line.period_name);
1436
1437 --Invoke sub program UpdateGLCashlines to update GL_JE_LINES for itemization items;
1438
1439 UpdateGLItemizationlines(P_LEGAL_ENTITY_ID,
1440 P_LEDGER_ID,
1441 v_line.period_name);
1442
1443 --Invoke sub program UpdateGLCashlines to update JA_CN_JOURNAL_LINES for itemization items;
1444 IF (ln_Subsidiary_number >= 4) THEN
1445 UpdateItemizationlines(P_COA_ID,
1446 P_LEGAL_ENTITY_ID,
1447 P_LEDGER_ID,
1448 v_line.period_name,
1449 --lb_Msg_Tag,
1450 lb_need_update,
1451 lv_prject_seg);
1452 --lb_Msg_Tag := FALSE;
1453 END IF;
1454
1455 COMMIT;
1456
1457 --logging for debug
1458 IF (ln_proc_level >= ln_dbg_level) THEN
1459 FND_LOG.STRING(ln_proc_level,
1460 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1461 '.end',
1462 'Exit procedure');
1463 END IF; -- (ln_proc_level>=ln_dbg_level)
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 FND_FILE.put_line(FND_FILE.log,
1467 lv_procedure_name || SQLCODE || SQLERRM);
1468 lb_Error_Status := Fnd_Concurrent.Set_Completion_Status(Status => 'ERROR',
1469 Message => lv_procedure_name ||
1470 SQLCODE ||
1471 SQLERRM);
1472 lb_need_update := FALSE;
1473 ROLLBACK;
1474 END;
1475 END LOOP;
1476 -----choli fixed bug 12344625,
1477 IF(lb_need_update) THEN
1478 -----
1479 Fnd_Message.Set_Name(Application => 'JA',
1480 NAME => 'JA_CN_DATA_MIGRATION_RESULT_D');
1481 lv_output_Msg := Fnd_Message.Get;
1482
1483 --Output error message
1484 Fnd_File.Put_Line(Fnd_File.Output, lv_output_Msg);
1485 -----choli fixed bug 12344625,
1486 END IF;
1487 -----
1488 END MigrateHisDataDE;
1489
1490 --==========================================================================
1491 -- FUNCTION NAME:
1492 --
1493 -- UpdateItemizationlines Public
1494 --
1495 -- DESCRIPTION:
1496 --
1497 -- This procedure is used to migrate the Itemazation lines data
1498 -- in ja_cn_journal_lines.
1499 --
1500 --
1501 -- PARAMETERS:
1502 -- In: pn_chart_account_id Chart of account ID
1503 -- In: pn_legal_entitliy_id Legal entity ID
1504 -- In : pn_ledger_id Leger Id
1505 -- In : pv_period_name Period Name
1506 -- DESIGN REFERENCES:
1507 --
1508 --
1509 -- CHANGE HISTORY:
1510 --
1511 -- 05-Jan-2011 Chongwu Li Created
1512 -- 19-Jan-2011 Chongwu Li Updated, fix bug 11659281, 11655280
1513 -- Merge UpdateBalances logic together to remove
1514 -- the duplicated warning message.
1515 -- 24-Jan-2011 Jianchao Chi Updated for bug 10634017
1516 -- 24-Jan-2011 Chongwu Li Updated, fix bug 11774438
1517 -- 11-Feb-2011 Jianchao Chi Updated for bug 11683977
1518 -- 13-Apr-2011 Chongwu Li fix bug 12349159
1519 -- 21-Apr-2011 Chongwu Li updated, fix bug 12373841
1520 -- 26-Apr-2011 Chongwu Li updated, performance optimization
1521 --===========================================================================
1522 PROCEDURE UpdateItemizationlines(pn_chart_account_id NUMBER,
1523 pn_legal_entitliy_id NUMBER,
1524 pn_ledger_id NUMBER,
1525 pv_period_name VARCHAR2,
1526 --pb_msg_tag BOOLEAN, --Add a parameter by Jianchao Chi for bug 11683977
1527 lb_need_Update BOOLEAN,
1528 lv_prject_seg VARCHAR2) IS
1529 ln_Dbg_Level NUMBER := Fnd_Log.g_Current_Runtime_Level;
1530 ln_Proc_Level NUMBER := Fnd_Log.Level_Procedure;
1531 lv_procedure_name VARCHAR2(40) := 'UpdateItemizationlines';
1532 ln_Subsidiary_number NUMBER;
1533 lv_customer_seg VARCHAR2(40);
1534 lv_supplier_seg VARCHAR2(40);
1535 lv_employee_seg VARCHAR2(40);
1536 lv_costcenter_column VARCHAR2(40);
1537 lv_costcenter_seg VARCHAR2(40);
1538
1539 lv_Error_Msg VARCHAR2(4000);
1540 lb_Error_Status BOOLEAN;
1541 -- lb_need_Update BOOLEAN := TRUE;
1542 lv_update_sql VARCHAR2(1000);
1543
1544 lv_update_template_sql VARCHAR2(1000) := 'UPDATE ja_cn_journal_lines jcjl
1545 SET jcjl.$to_column$ = $from_column$
1546 WHERE jcjl.legal_entity_id = ' || pn_legal_entitliy_id ||'
1547 AND jcjl.ledger_id = ' || pn_ledger_id ||'
1548 AND jcjl.journal_number IS NOT NULL
1549 AND jcjl.period_name = ''' || pv_period_name ||'''
1550 AND jcjl.$to_column$ IS NULL';
1551
1552 lv_bal_update_sql VARCHAR2(1000);
1553 lv_bal_update_tpl_sql VARCHAR2(1000) := 'UPDATE ja_cn_account_balances jcab
1554 SET jcab.$to_column$ = $from_column$
1555 WHERE jcab.legal_entity_id = ' || pn_legal_entitliy_id ||'
1556 AND jcab.ledger_id = ' || pn_ledger_id ||'
1557 AND jcab.period_name = ''' || pv_period_name || '''
1558 AND jcab.$to_column$ IS NULL';
1559
1560 CURSOR cur_setups IS
1561 SELECT sam.subsidiary_segment_code,
1562 sam.sources_code,
1563 sam.context_code
1564 FROM ja_cn_sub_acc_mapping sam
1565 WHERE (chart_of_accounts_id = pn_chart_account_id)
1566 AND context_code in
1567 ('CUSTOMER',
1568 'SUPPLIER',
1569 'EMPLOYEE_SUPPLIER',
1570 (SELECT application_column_name
1571 FROM fnd_segment_attribute_values
1572 WHERE segment_attribute_type = 'FA_COST_CTR'
1573 AND attribute_value = 'Y'
1574 AND (id_flex_num = pn_chart_account_id)
1575 AND (id_flex_code = 'GL#')
1576 AND (application_id = 101)));
1577 BEGIN
1578 --logging for debug
1579
1580 IF (ln_proc_level >= ln_dbg_level) THEN
1581 FND_LOG.STRING(ln_proc_level,
1582 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
1583 '.begin',
1584 'Enter procedure');
1585 END IF; --ln_proc_level>=ln_dbg_level
1586 FND_FILE.PUT_LINE(FND_FILE.log, 'Enter: ' || lv_procedure_name);
1587 FND_FILE.put_line(FND_FILE.log,
1588 lv_procedure_name || '.parameters:' ||
1589 'pn_chart_account_id=' || pn_chart_account_id || ',' ||
1590 'pv_period_name=' || pv_period_name || ',' ||
1591 'pn_ledger_id=' || pn_ledger_id || ',' ||
1592 'pn_legal_entitliy_id=' || pn_legal_entitliy_id);
1593
1594 SELECT application_column_name
1595 INTO lv_costcenter_column
1596 FROM fnd_segment_attribute_values
1597 WHERE segment_attribute_type = 'FA_COST_CTR'
1598 AND attribute_value = 'Y'
1599 AND (id_flex_num = pn_chart_account_id)
1600 AND (id_flex_code = 'GL#')
1601 AND (application_id = 101);
1602 FND_FILE.put_line(FND_FILE.log,
1603 lv_procedure_name || 'lv_costcenter_column = ' ||
1604 lv_costcenter_column);
1605 BEGIN
1606 FOR v_row IN cur_setups LOOP
1607 IF (v_row.sources_code = 'SLA' AND v_row.context_code = 'CUSTOMER') THEN
1608 lv_customer_seg := v_row.subsidiary_segment_code;
1609 ELSIF (v_row.sources_code = 'SLA' AND
1610 v_row.context_code = 'SUPPLIER') THEN
1611 lv_supplier_seg := v_row.subsidiary_segment_code;
1612 ELSIF (v_row.sources_code = 'SLA' AND
1613 v_row.context_code = 'EMPLOYEE_SUPPLIER') THEN
1614 lv_employee_seg := v_row.subsidiary_segment_code;
1615 ELSIF (v_row.sources_code = 'COA' AND
1616 v_row.context_code = lv_costcenter_column) THEN
1617 lv_costcenter_seg := v_row.subsidiary_segment_code;
1618 END IF;
1619 END LOOP;
1620 EXCEPTION
1621 WHEN OTHERS THEN
1622 FND_FILE.put_line(FND_FILE.log,
1623 lv_procedure_name || SQLCODE || SQLERRM);
1624 RAISE;
1625 END;
1626 FND_FILE.put_line(FND_FILE.log,
1627 lv_procedure_name || 'lv_customer_seg = ' ||
1628 lv_customer_seg);
1629 FND_FILE.put_line(FND_FILE.log,
1630 lv_procedure_name || 'lv_supplier_seg = ' ||
1631 lv_supplier_seg);
1632 FND_FILE.put_line(FND_FILE.log,
1633 lv_procedure_name || 'lv_employee_seg = ' ||
1634 lv_employee_seg);
1635 FND_FILE.put_line(FND_FILE.log,
1636 lv_procedure_name || 'lv_costcenter_seg = ' ||
1637 lv_costcenter_seg);
1638
1639 -- update JA_CN_JOURNAL_LINES, Construct the update sql for Itemazation data migration
1640
1641 -- Update customer
1642 lv_update_sql := REPLACE(lv_update_template_sql,
1643 '$to_column$',
1644 lv_customer_seg);
1645 lv_update_sql := REPLACE(lv_update_sql,
1646 '$from_column$',
1647 'jcjl.third_party_number');
1648 lv_update_sql := lv_update_sql || ' AND jcjl.third_party_type = ''C'' ';
1649
1650 EXECUTE IMMEDIATE lv_update_sql;
1651
1652
1653 -- Update supplier
1654 lv_update_sql := REPLACE(lv_update_template_sql,
1655 '$to_column$',
1656 lv_supplier_seg);
1657 lv_update_sql := REPLACE(lv_update_sql,
1658 '$from_column$',
1659 'jcjl.third_party_number');
1660 lv_update_sql := lv_update_sql || ' AND jcjl.third_party_type = ''S'' ';
1661 EXECUTE IMMEDIATE lv_update_sql;
1662
1663
1664 -- Update employee
1665 lv_update_sql := REPLACE(lv_update_template_sql,
1666 '$to_column$',
1667 lv_employee_seg);
1668 lv_update_sql := REPLACE(lv_update_sql,
1669 '$from_column$',
1670 '(select distinct ppf.employee_number from per_people_f ppf where ppf.person_id(+) = jcjl.personnel_id)');
1671
1672 EXECUTE IMMEDIATE lv_update_sql;
1673
1674 -- Update cost center
1675 lv_update_sql := REPLACE(lv_update_template_sql,
1676 '$to_column$',
1677 lv_costcenter_seg);
1678 lv_update_sql := REPLACE(lv_update_sql,
1679 '$from_column$',
1680 'jcjl.cost_center');
1681
1682 EXECUTE IMMEDIATE lv_update_sql;
1683
1684
1685 -- Update balance table, Construct the update sql for balance data migration
1686 -- Update customer
1687 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
1688 '$to_column$',
1689 lv_customer_seg);
1690 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
1691 '$from_column$',
1692 'jcab.third_party_number');
1693 lv_bal_update_sql := lv_bal_update_sql || ' AND jcab.third_party_type = ''C'' ';
1694 EXECUTE IMMEDIATE lv_bal_update_sql;
1695
1696
1697 -- Update supplier
1698 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
1699 '$to_column$',
1700 lv_supplier_seg);
1701 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
1702 '$from_column$',
1703 'jcab.third_party_number');
1704 lv_bal_update_sql := lv_bal_update_sql || ' AND jcab.third_party_type = ''S'' ';
1705 EXECUTE IMMEDIATE lv_bal_update_sql;
1706
1707 -- Update employee
1708 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
1709 '$to_column$',
1710 lv_employee_seg);
1711 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
1712 '$from_column$',
1713 '(select distinct ppf.employee_number from per_people_f ppf where ppf.person_id(+) = jcab.personnel_id)');
1714
1715 EXECUTE IMMEDIATE lv_bal_update_sql;
1716
1717 -- Update cost center
1718
1719 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
1720 '$to_column$',
1721 lv_costcenter_seg);
1722 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
1723 '$from_column$',
1724 'jcab.cost_center');
1725
1726 EXECUTE IMMEDIATE lv_bal_update_sql;
1727
1728
1729
1730 -- Update currency_conversion_type
1731 UPDATE JA_CN_JOURNAL_LINES jc
1732 SET currency_conversion_type =
1733 (SELECT DISTINCT NVL(Ael.Currency_Conversion_Type,
1734 Jeh.Currency_Conversion_Type) currency_conversion_type
1735 FROM Gl_Je_Headers Jeh,
1736 Xla_Ae_Lines Ael,
1737 Gl_Import_References Gir,
1738 JA_CN_JOURNAL_LINES jcjl
1739 WHERE Jeh.Je_Header_Id = jcjl.je_header_id
1740 AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id(+)
1741 AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table(+)
1742 AND Gir.Je_Line_Num(+) = jcjl.Je_Line_Num
1743 AND Gir.Je_Header_Id(+) = jcjl.Je_Header_Id
1744 AND jc.Je_Header_Id = jcjl.je_header_id
1745 --Add by Jianchao Chi for bug 10634017
1746 AND jc.Je_Line_Num = jcjl.Je_Line_Num)
1747 WHERE jc.Ledger_Id = pn_ledger_id
1748 AND jc.Legal_Entity_Id = pn_legal_entitliy_id
1749 AND jc.period_name = pv_period_name
1750 AND jc.currency_conversion_type IS NULL;
1751 -- choli fix bug 12349159, move the related setup check into MigrateHisDateDE
1752 -- Handle project related update
1753 -- update JA_CN_JOURNAL_LINES
1754 IF (lb_need_Update) THEN
1755
1756 -- Update project
1757 lv_update_sql := REPLACE(lv_update_template_sql,
1758 '$to_column$',
1759 lv_prject_seg);
1760 lv_update_sql := REPLACE(lv_update_sql,
1761 '$from_column$',
1762 'project_number');
1763
1764 EXECUTE IMMEDIATE lv_update_sql;
1765
1766
1767 lv_bal_update_sql := REPLACE(lv_bal_update_tpl_sql,
1768 '$to_column$',
1769 lv_prject_seg);
1770 lv_bal_update_sql := REPLACE(lv_bal_update_sql,
1771 '$from_column$',
1772 'jcab.project_number');
1773 EXECUTE IMMEDIATE lv_bal_update_sql;
1774
1775
1776 END IF; --(lb_need_Update) THEN
1777
1778 --logging for debug
1779 IF (ln_proc_level >= ln_dbg_level) THEN
1780 FND_LOG.STRING(ln_proc_level,
1781 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
1782 'Exit procedure');
1783 END IF; -- (ln_proc_level>=ln_dbg_level)
1784 END UpdateItemizationlines;
1785
1786 END JA_CN_HISDATA_MIG_PKG;
1787
1788