1 PACKAGE BODY cst_upd_gir_mta_wta AS
2 /* $Header: CSTGIRMWB.pls 120.1.12020000.3 2013/01/25 08:11:19 pbasrani ship $ */
3
4 -- Local procedures and variables
5
6 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7 bulk_size NUMBER := 10000;
8
9
10 ------------------------------------------------------------------------------------
11 -- API name : debug
12 -- Type : Private
13 -- Function : Procedure to log messages
14 --
15 -- Pre-reqs :
16 -- Parameters :
17 -- IN : line IN VARCHAR2
18 --
19 -- OUT :
20 --
21 -- End of comments
22 -------------------------------------------------------------------------------------
23 PROCEDURE debug
24 ( line IN VARCHAR2,
25 msg_prefix IN VARCHAR2 DEFAULT 'CST',
26 msg_module IN VARCHAR2 DEFAULT 'cst_upd_gir_mta_wta',
27 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
28 IS
29 l_msg_prefix VARCHAR2(64);
30 l_msg_level NUMBER;
31 l_msg_module VARCHAR2(256);
32 l_beg_end_suffix VARCHAR2(15);
33 l_org_cnt NUMBER;
34 l_line VARCHAR2(32767);
35 BEGIN
36
37 l_line := line ||'->'||TO_CHAR(SYSDATE, 'DD-MM-YY HH24:MI:SS');
38 l_msg_prefix := msg_prefix;
39 l_msg_level := msg_level;
40 l_msg_module := msg_module;
41
42 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0) THEN
43 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
44 END IF;
45
46 IF l_msg_level <> FND_LOG.LEVEL_EXCEPTION AND PG_DEBUG = 'N' THEN
47 RETURN;
48 END IF;
49
50 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
51 FND_LOG.STRING(l_msg_level, l_msg_module, SUBSTRB(l_line,1,4000));
52 END IF;
53
54 EXCEPTION
55 WHEN OTHERS THEN RAISE;
56 END debug;
57
58 ------------------------------------------------------------------------------------
59 -- API name : cst_sl_link_upg_mta
60 -- Type : Public
61 -- Function : Procedure to update link id in MTA, XAL for a particular GL Batch Id
62 --
63 -- Pre-reqs :
64 -- Parameters :
65 -- IN : p_je_batch_id IN NUMBER
66 --
67 -- OUT :
68 --
69 -- End of comments
70 -------------------------------------------------------------------------------------
71 PROCEDURE cst_sl_link_upg_mta (p_je_batch_id IN NUMBER,
72 p_rerun_mode IN VARCHAR2 DEFAULT 'N' ) --Flexible Logic
73 AS
74
75 CURSOR c_gl_lines is
76 select gl.je_header_id,
77 gl.je_line_num,
78 gl.code_combination_id,
79 nvl(gh.ussgl_transaction_code, '*'),
80 gh.currency_code,
81 (select sob.currency_code from gl_sets_of_books sob where sob.set_of_books_id = gh.ledger_id),
82 decode(gl.entered_dr, NULL, 0, 1) + decode(gl.entered_cr, NULL, 0, -1)
83 from gl_je_headers gh,
84 gl_je_lines gl
85 where gh.je_batch_id = p_je_batch_id
86 and gl.je_header_id = gh.je_header_id
87 and gh.je_category = 'MTL'
88 and gh.actual_flag = 'A'
89 and exists (select 1 from gl_import_references jir
90 where jir.je_header_id = gl.je_header_id
91 and jir.je_line_num = gl.je_line_num
92 and jir.gl_sl_link_table = 'MTA'
93 and (jir.gl_sl_link_id is null or p_rerun_mode = 'Y') --Flexible Logic
94 and jir.reference_3 is null);
95
96 TYPE je_header_id_type IS TABLE OF gl_je_lines.je_header_id%TYPE;
97 TYPE je_line_num_type IS TABLE OF gl_je_lines.je_line_num%TYPE;
98 TYPE code_combination_id_type IS TABLE OF gl_je_lines.code_combination_id%TYPE;
99 TYPE ussgl_transaction_code_type IS TABLE OF gl_je_headers.ussgl_transaction_code%TYPE;
100 TYPE gl_currency_code_type IS TABLE OF gl_je_headers.currency_code%TYPE;
101 TYPE ledger_currency_code_type IS TABLE OF gl_sets_of_books.currency_code%TYPE;
102
103 je_header_id_tab je_header_id_type;
104 je_line_num_tab je_line_num_type;
105 code_combination_id_tab code_combination_id_type;
106 ussgl_transaction_code_tab ussgl_transaction_code_type;
107 gl_currency_code_tab gl_currency_code_type;
108 ledger_currency_code_tab ledger_currency_code_type;
109 gl_sign_flag_tab DBMS_SQL.NUMBER_TABLE;
110
111 CURSOR c_gir_links IS
112 select gir_rowid,
113 gl_sl_link_id
114 from cst_gl_summary_links_temp;
115
116 TYPE gir_rowid_type IS TABLE OF cst_gl_summary_links_temp.gir_rowid%TYPE;
117 TYPE gl_sl_link_id_type IS TABLE OF cst_gl_summary_links_temp.gl_sl_link_id%TYPE;
118
119 gir_rowid_tab gir_rowid_type;
120 gl_sl_link_id_tab gl_sl_link_id_type;
121
122 CURSOR c_mta IS
123 select /*+ ORDERED */
124 mta.rowid,
125 mta.inv_sub_ledger_id,
126 sl.gl_sl_link_id
127 from cst_gl_summary_links_temp sl,
128 mtl_transaction_accounts mta
129 where mta.gl_batch_id = sl.gl_batch_id
130 and mta.reference_account = sl.reference_account
131 and sl.gl_currency_code = nvl(mta.currency_code, sl.ledger_currency_code)
132 and sl.ussgl_transaction_code = nvl(mta.ussgl_transaction_code, '*')
133 and ( sl.gl_dr_cr_flag = 0
134 or (sl.gl_dr_cr_flag = 1 and mta.base_transaction_value > 0)
135 or (sl.gl_dr_cr_flag = -1 and mta.base_transaction_value < 0)
136 )
137 and ( mta.gl_sl_link_id is null
138 --To correct data during During re-runs
139 or exists ( select 1 from gl_import_references R
140 where R.gl_sl_link_table = 'MTA'
141 and R.gl_sl_link_id = mta.gl_sl_link_id
142 and R.reference_3 is null )) --Support Flexible Logic
143 and mta.encumbrance_type_id is null
144 order by sl.gl_sl_link_id;
145
146 TYPE inv_sub_ledger_id_type IS TABLE OF mtl_transaction_accounts.inv_sub_ledger_id%TYPE;
147
148 mta_rowid_tab gir_rowid_type;
149 inv_sub_ledger_id_tab inv_sub_ledger_id_type;
150
151 l_count NUMBER;
152 l_stmnt_num NUMBER;
153
154 BEGIN
155 /*
156 Flexible Logic means the idea to correct the user data, if
157 there exists a bug with this logic. The flexible logic used
158 here do not depend upon NULL gl_sl_link_id's for identifying
159 records needing datafix. In this way, Developers can have code
160 fix for any bugs and ask user to run the data fix for the same
161 gl_batch_id and can correct the data.
162 Note: This flexible logic is disabled by default as the je_headers
163 picked for datafix is restricted by NULL gl_sl_link_id in c_gl_lines
164 */
165
166 debug('cst_sl_link_upg_mta +');
167 debug(' p_je_batch_id :'||p_je_batch_id);
168
169 debug(' >Inserting data in SL');
170 l_count := 0;
171 OPEN c_gl_lines;
172 LOOP
173 l_count := l_count + 1;
174 l_stmnt_num := 0;
175 FETCH c_gl_lines bulk collect
176 INTO je_header_id_tab,
177 je_line_num_tab,
178 code_combination_id_tab,
179 ussgl_transaction_code_tab,
180 gl_currency_code_tab,
181 ledger_currency_code_tab,
182 gl_sign_flag_tab
183 limit bulk_size;
184
185 debug(' L'||l_count||': '||je_header_id_tab.COUNT||' Records fetched by cursor');
186 IF je_header_id_tab.count = 0 THEN
187 EXIT;
188 END IF;
189
190 --Deleting duplicate records from gl_import_references
191 l_stmnt_num := 10;
192 FORALL i IN 1..je_header_id_tab.COUNT
193 delete /*+ index(jir gl_import_references_n1) */
194 from gl_import_references jir
195 where jir.je_header_id = je_header_id_tab(i)
196 and jir.je_line_num = je_line_num_tab(i)
197 and exists
198 ( select /*+ index(jir1 gl_import_references_n1) */ 1
199 from gl_import_references jir1
200 where jir1.je_header_id = jir.je_header_id
201 and jir1.je_line_num = jir.je_line_num
202 and jir1.je_batch_id = jir.je_batch_id
203 and nvl(jir1.reference_1, -1) = nvl(jir.reference_1, -1)
204 and nvl(jir1.reference_2, -1) = nvl(jir.reference_2, -1)
205 and jir1.reference_3 is null and jir.reference_3 is null
206 and jir1.gl_sl_link_table = jir.gl_sl_link_table
207 --and jir1.gl_sl_link_id = jir.gl_sl_link_id --Support Flexible Logic
208 and ( ( jir.gl_sl_link_id is null and jir1.gl_sl_link_id is null --Flexible Logic
209 and jir.rowid < jir1.rowid )
210 --'<' below is deliberatley used to support the flexible logic in c_mta ORDER BY clause
211 or ( jir.gl_sl_link_id < jir1.gl_sl_link_id ))); --Flexible Logic
212
213 debug(' L'||l_count||': Deleted '||SQL%ROWCOUNT||' rows from GIR');
214
215 l_stmnt_num := 20;
216 FORALL i in 1..je_header_id_tab.count
217 insert into cst_gl_summary_links_temp
218 ( je_header_id,
219 je_line_num,
220 gl_batch_id,
221 reference_account,
222 gl_currency_code,
223 ussgl_transaction_code,
224 gl_dr_cr_flag,
225 ledger_currency_code,
226 gir_rowid,
227 gl_sl_link_id
228 )
229 select /*+ index(jir gl_import_references_n1) */
230 je_header_id_tab(i),
231 je_line_num_tab(i),
232 nvl(jir.reference_1, -1),
233 code_combination_id_tab(i),
234 gl_currency_code_tab(i),
235 ussgl_transaction_code_tab(i),
236 gl_sign_flag_tab(i),
237 ledger_currency_code_tab(i),
238 jir.rowid,
239 nvl(jir.gl_sl_link_id, xla_gl_sl_link_id_s.nextval) --Support Flexible Logic
240 from gl_import_references jir
241 where jir.je_header_id = je_header_id_tab(i)
242 and jir.je_line_num = je_line_num_tab(i);
243
244 debug(' L'||l_count||': Inserted '||SQL%ROWCOUNT||' rows in SL');
245
246 EXIT WHEN c_gl_lines%NOTFOUND;
247 END LOOP;
248 CLOSE c_gl_lines;
249 debug(' <Inserting data in SL');
250
251 select count(*) into l_count
252 from cst_gl_summary_links_temp;
253
254 IF l_count <> 0 THEN
255
256 /* For the same
257 gl_sl_link_table, gl_batch_id, reference_account, gl_currency_code, ussgl_transaction_code
258 there can be only the following valid cases for gl_dr_cr_flag
259 Note: The primary key extends to gl_dr_cr_flag also
260 1) Single row with value 0 -- Valid
261 2) Single row with value +1 -- Valid
262 3) Single row with value -1 -- Valid
263 4) Two rows with values +1 & -1 -- Valid
264 5) Two rows with values 0 & +1 -- Invalid
265 6) Two rows with values 0 & -1 -- Invalid
266 7) Three rows with values 0, +1 & -1 -- Invalid
267
268 The below query does the validation check for the above cases */
269
270 debug(' >Validating data in SL');
271 l_stmnt_num := 30;
272 select count(*) into l_count
273 from cst_gl_summary_links_temp link1
274 where link1.gl_dr_cr_flag = 0
275 and exists ( select 1 from cst_gl_summary_links_temp link2
276 where link2.gl_batch_id = link1.gl_batch_id
277 and link2.reference_account = link1.reference_account
278 and link2.gl_currency_code = link1.gl_currency_code
279 and link2.ussgl_transaction_code = link1.ussgl_transaction_code
280 and link2.gl_dr_cr_flag in (1,-1))
281 and rownum < 2;
282
283 IF l_count > 0 THEN
284 debug(' Error validating data for cst_gl_summary_links_temp. l_count = '||l_count);
285 END IF;
286
287 /* In the above table for the single row cases the gl_dr_cr_flag
288 should be updated to zero. Hence gl_dr_cr_flag for 2 & 3 should
289 be updated to zero */
290
291 l_stmnt_num := 40;
292 update cst_gl_summary_links_temp link1
293 set link1. gl_dr_cr_flag = 0
294 where link1.gl_dr_cr_flag in (1,-1)
295 and not exists ( select 1 from cst_gl_summary_links_temp link2
296 where link1.gl_batch_id = link2.gl_batch_id
297 and link1.reference_account = link2.reference_account
298 and link1.gl_currency_code = link2.gl_currency_code
299 and link1.ussgl_transaction_code = link2.ussgl_transaction_code
300 and ( ( link1.gl_dr_cr_flag = 1 and link2.gl_dr_cr_flag = -1 )
301 or ( link1.gl_dr_cr_flag = -1 and link2.gl_dr_cr_flag = 1 )
302 )
303 );
304 debug(' Corrected '||SQL%ROWCOUNT||' rows in SL');
305 debug(' <Validating data in SL');
306
307 debug(' >Populating GL_SL_LINK_ID in GIR');
308 l_count := 0;
309 OPEN c_gir_links;
310 LOOP
311 l_count := l_count + 1;
312 l_stmnt_num := 50;
313 FETCH c_gir_links bulk collect
314 into gir_rowid_tab,
315 gl_sl_link_id_tab
316 limit bulk_size;
317
318 debug(' L'||l_count||': '||gir_rowid_tab.COUNT||' Records fetched by cursor');
319 IF gir_rowid_tab.count = 0 THEN
320 EXIT;
321 END IF;
322
323 l_stmnt_num := 60;
324 FORALL i in 1..gir_rowid_tab.count
325 update gl_import_references gir
326 set gir.gl_sl_link_id = gl_sl_link_id_tab(i)
327 where gir.rowid = gir_rowid_tab(i)
328 and gir.gl_sl_link_id is null; --Support Flexible Logic
329
330 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in GIR');
331
332 EXIT WHEN c_gir_links%NOTFOUND;
333 END LOOP;
334 CLOSE c_gir_links;
335 debug(' <Populating GL_SL_LINK_ID in GIR');
336
337 debug(' >Updating GL_SL_LINK_ID for MTA and XAL');
338 l_count := 0;
339 OPEN c_mta;
340 LOOP
341 l_count := l_count + 1;
342 l_stmnt_num := 70;
343 FETCH c_mta bulk collect
344 into mta_rowid_tab,
345 inv_sub_ledger_id_tab,
346 gl_sl_link_id_tab
347 limit bulk_size;
348
349 debug(' L'||l_count||': '||mta_rowid_tab.COUNT||' Records fetched by cursor');
350 IF mta_rowid_tab.count = 0 THEN
351 EXIT;
352 END IF;
353
354 l_stmnt_num := 80;
355 FORALL i in 1..mta_rowid_tab.count
356 update mtl_transaction_accounts mta
357 set mta.gl_sl_link_id = gl_sl_link_id_tab(i)
358 where mta.rowid = mta_rowid_tab(i);
359
360 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in MTA');
361
362 l_stmnt_num := 90;
363 FORALL i in 1..mta_rowid_tab.count
364 update /*+ index(xal xla_ae_lines_u1) */ xla_ae_lines xal
365 set xal.gl_sl_link_id = gl_sl_link_id_tab(i)
366 where xal.application_id = 707
367 and xal.gl_sl_link_table = 'MTA'
368 and (xal.ae_header_id, xal.ae_line_num)
369 in (select /*+ index(xdl xla_distribution_links_n1) */
370 xdl.ae_header_id,
371 xdl.ae_line_num
372 from xla_distribution_links xdl
373 where XDL.application_id = 707 /*Added for bug 16217359 */
374 AND xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
375 and xdl.source_distribution_id_num_1 = inv_sub_ledger_id_tab(i));
376
377 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in XAL');
378
379 EXIT WHEN c_mta%NOTFOUND;
380 END LOOP;
381 CLOSE c_mta;
382 debug(' <Updating GL_SL_LINK_ID for MTA and XAL');
383
384 ELSE
385 debug(' No MTA records to be updated for gl_batch_id : '||p_je_batch_id);
386 END IF;
387
388 commit;
389 debug('cst_sl_link_upg_mta -');
390 EXCEPTION
391 WHEN OTHERS THEN
392 IF c_mta%ISOPEN THEN CLOSE c_mta; END IF;
393 IF c_gir_links%ISOPEN THEN CLOSE c_gir_links; END IF;
394 IF c_gl_lines%ISOPEN THEN CLOSE c_gl_lines; END IF;
395 debug('EXCEPTION OTHERS cst_sl_link_upg_mta ('||l_stmnt_num||') :'||SQLERRM);
396 RAISE;
397 END cst_sl_link_upg_mta;
398
399 ------------------------------------------------------------------------------------
400 -- API name : cst_sl_link_upg_wta
401 -- Type : Public
402 -- Function : Procedure to update link id in WTA, XAL for a particular GL Batch Id
403 --
404 -- Pre-reqs :
405 -- Parameters :
406 -- IN : p_je_batch_id IN NUMBER
407 --
408 -- OUT :
409 --
410 -- End of comments
411 -------------------------------------------------------------------------------------
412 PROCEDURE cst_sl_link_upg_wta (p_je_batch_id IN NUMBER,
413 p_rerun_mode IN VARCHAR2 DEFAULT 'N' ) --Flexible Logic
414 AS
415
416 CURSOR c_gl_lines is
417 select gl.je_header_id,
418 gl.je_line_num,
419 gl.code_combination_id,
420 nvl(gh.ussgl_transaction_code, '*'),
421 gh.currency_code,
422 (select sob.currency_code from gl_sets_of_books sob where sob.set_of_books_id = gh.ledger_id),
423 decode(gl.entered_dr, NULL, 0, 1) + decode(gl.entered_cr, NULL, 0, -1)
424 from gl_je_headers gh,
425 gl_je_lines gl
426 where gh.je_batch_id = p_je_batch_id
427 and gl.je_header_id = gh.je_header_id
428 and gh.je_category = 'WIP'
429 and gh.actual_flag = 'A'
430 and exists (select 1 from gl_import_references jir
431 where jir.je_header_id = gl.je_header_id
432 and jir.je_line_num = gl.je_line_num
433 and jir.gl_sl_link_table = 'WTA'
434 and (jir.gl_sl_link_id is null or p_rerun_mode = 'Y') --Flexible Logic
435 and jir.reference_3 is null);
436
437 TYPE je_header_id_type IS TABLE OF gl_je_lines.je_header_id%TYPE;
438 TYPE je_line_num_type IS TABLE OF gl_je_lines.je_line_num%TYPE;
439 TYPE code_combination_id_type IS TABLE OF gl_je_lines.code_combination_id%TYPE;
440 TYPE ussgl_transaction_code_type IS TABLE OF gl_je_headers.ussgl_transaction_code%TYPE;
441 TYPE gl_currency_code_type IS TABLE OF gl_je_headers.currency_code%TYPE;
442 TYPE ledger_currency_code_type IS TABLE OF gl_sets_of_books.currency_code%TYPE;
443
444 je_header_id_tab je_header_id_type;
445 je_line_num_tab je_line_num_type;
446 code_combination_id_tab code_combination_id_type;
447 ussgl_transaction_code_tab ussgl_transaction_code_type;
448 gl_currency_code_tab gl_currency_code_type;
449 ledger_currency_code_tab ledger_currency_code_type;
450 gl_sign_flag_tab DBMS_SQL.NUMBER_TABLE;
451
452 CURSOR c_gir_links IS
453 select gir_rowid,
454 gl_sl_link_id
455 from cst_gl_summary_links_temp;
456
457 TYPE gir_rowid_type IS TABLE OF cst_gl_summary_links_temp.gir_rowid%TYPE;
458 TYPE gl_sl_link_id_type IS TABLE OF cst_gl_summary_links_temp.gl_sl_link_id%TYPE;
459
460 gir_rowid_tab gir_rowid_type;
461 gl_sl_link_id_tab gl_sl_link_id_type;
462
463 CURSOR c_wta IS
464 select /*+ ORDERED */
465 wta.rowid,
466 wta.wip_sub_ledger_id,
467 sl.gl_sl_link_id
468 from cst_gl_summary_links_temp sl,
469 wip_transaction_accounts wta
470 where wta.gl_batch_id = sl.gl_batch_id
471 and wta.reference_account = sl.reference_account
472 and sl.gl_currency_code = nvl(wta.currency_code, sl.ledger_currency_code)
473
474 and ( sl.gl_dr_cr_flag = 0
475 or (sl.gl_dr_cr_flag = 1 and wta.base_transaction_value > 0)
476 or (sl.gl_dr_cr_flag = -1 and wta.base_transaction_value < 0)
477 )
478 and ( wta.gl_sl_link_id is null
479 --To correct data during During re-runs
480 or exists ( select 1 from gl_import_references R
481 where R.gl_sl_link_table = 'WTA'
482 and R.gl_sl_link_id = wta.gl_sl_link_id
483 and R.reference_3 is null )) --Support Flexible Logic
484 order by sl.gl_sl_link_id;
485
486 TYPE wip_sub_ledger_id_type IS TABLE OF wip_transaction_accounts.wip_sub_ledger_id%TYPE;
487
488 wta_rowid_tab gir_rowid_type;
489 wip_sub_ledger_id_tab wip_sub_ledger_id_type;
490
491 l_count NUMBER;
492 l_stmnt_num NUMBER;
493
494 BEGIN
495 /*
496 Flexible Logic means the idea to correct the user data, if
497 there exists a bug with this logic. The flexible logic used
498 here do not depend upon NULL gl_sl_link_id's for identifying
499 records needing datafix. In this way, Developers can have code
500 fix for any bugs and ask user to run the data fix for the same
501 gl_batch_id and can correct the data.
502 Note: This flexible logic is disabled by default as the je_headers
503 picked for datafix is restricted by NULL gl_sl_link_id in c_gl_lines
504 */
505
506 debug('cst_sl_link_upg_wta +');
507 debug(' p_je_batch_id :'||p_je_batch_id);
508
509 debug(' >Inserting data in SL');
510 l_count := 0;
511 OPEN c_gl_lines;
512 LOOP
513 l_count := l_count + 1;
514 l_stmnt_num := 0;
515 FETCH c_gl_lines bulk collect
516 INTO je_header_id_tab,
517 je_line_num_tab,
518 code_combination_id_tab,
519 ussgl_transaction_code_tab,
520 gl_currency_code_tab,
521 ledger_currency_code_tab,
522 gl_sign_flag_tab
523 limit bulk_size;
524
525 debug(' L'||l_count||': '||je_header_id_tab.COUNT||' Records fetched by cursor');
526 IF je_header_id_tab.count = 0 THEN
527 EXIT;
528 END IF;
529
530 --Deleting duplicate records from gl_import_references
531 l_stmnt_num := 10;
532 FORALL i IN 1..je_header_id_tab.COUNT
533 delete /*+ index(jir gl_import_references_n1) */
534 from gl_import_references jir
535 where jir.je_header_id = je_header_id_tab(i)
536 and jir.je_line_num = je_line_num_tab(i)
537 and exists
538 ( select /*+ index(jir1 gl_import_references_n1) */ 1
539 from gl_import_references jir1
540 where jir1.je_header_id = jir.je_header_id
541 and jir1.je_line_num = jir.je_line_num
542 and jir1.je_batch_id = jir.je_batch_id
543 and nvl(jir1.reference_1, -1) = nvl(jir.reference_1, -1)
544 and nvl(jir1.reference_2, -1) = nvl(jir.reference_2, -1)
545 and jir1.reference_3 is null and jir.reference_3 is null
546 and jir1.gl_sl_link_table = jir.gl_sl_link_table
547 --and jir1.gl_sl_link_id = jir.gl_sl_link_id --Support Flexible Logic
548 and ( ( jir.gl_sl_link_id is null and jir1.gl_sl_link_id is null --Flexible Logic
549 and jir.rowid < jir1.rowid )
550 --'<' below is deliberatley used to support the flexible logic in c_wta ORDER BY clause
551 or ( jir.gl_sl_link_id < jir1.gl_sl_link_id ))); --Flexible Logic
552
553 debug(' L'||l_count||': Deleted '||SQL%ROWCOUNT||' rows from GIR');
554
555 l_stmnt_num := 20;
556 FORALL i in 1..je_header_id_tab.count
557 insert into cst_gl_summary_links_temp
558 ( je_header_id,
559 je_line_num,
560 gl_batch_id,
561 reference_account,
562 gl_currency_code,
563 ussgl_transaction_code,
564 gl_dr_cr_flag,
565 ledger_currency_code,
566 gir_rowid,
567 gl_sl_link_id
568 )
569 select /*+ index(jir gl_import_references_n1) */
570 je_header_id_tab(i),
571 je_line_num_tab(i),
572 nvl(jir.reference_1, -1),
573 code_combination_id_tab(i),
574 gl_currency_code_tab(i),
575 ussgl_transaction_code_tab(i),
576 gl_sign_flag_tab(i),
577 ledger_currency_code_tab(i),
578 jir.rowid,
579 nvl(jir.gl_sl_link_id, xla_gl_sl_link_id_s.nextval) --Support Flexible Logic
580 from gl_import_references jir
581 where jir.je_header_id = je_header_id_tab(i)
582 and jir.je_line_num = je_line_num_tab(i);
583
584 debug(' L'||l_count||': Inserted '||SQL%ROWCOUNT||' rows in SL');
585
586 EXIT WHEN c_gl_lines%NOTFOUND;
587 END LOOP;
588 CLOSE c_gl_lines;
589 debug(' <Inserting data in SL');
590
591 select count(*) into l_count
592 from cst_gl_summary_links_temp;
593
594 IF l_count <> 0 THEN
595
596 /* For the same
597 gl_sl_link_table, gl_batch_id, reference_account, gl_currency_code, ussgl_transaction_code
598 there can be only the following valid cases for gl_dr_cr_flag
599 Note: The primary key extends to gl_dr_cr_flag also
600 1) Single row with value 0 -- Valid
601 2) Single row with value +1 -- Valid
602 3) Single row with value -1 -- Valid
603 4) Two rows with values +1 & -1 -- Valid
604 5) Two rows with values 0 & +1 -- Invalid
605 6) Two rows with values 0 & -1 -- Invalid
606 7) Three rows with values 0, +1 & -1 -- Invalid
607
608 The below query does the validation check for the above cases */
609
610 debug(' >Validating data in SL');
611 l_stmnt_num := 30;
612 select count(*) into l_count
613 from cst_gl_summary_links_temp link1
614 where link1.gl_dr_cr_flag = 0
615 and exists ( select 1 from cst_gl_summary_links_temp link2
616 where link2.gl_batch_id = link1.gl_batch_id
617 and link2.reference_account = link1.reference_account
618 and link2.gl_currency_code = link1.gl_currency_code
619 and link2.ussgl_transaction_code = link1.ussgl_transaction_code
620 and link2.gl_dr_cr_flag in (1,-1))
621 and rownum < 2;
622
623 IF l_count > 0 THEN
624 debug(' Error validating data for cst_gl_summary_links_temp. l_count = '||l_count);
625 END IF;
626
627 /* In the above table for the single row cases the gl_dr_cr_flag
628 should be updated to zero. Hence gl_dr_cr_flag for 2 & 3 should
629 be updated to zero */
630
631 l_stmnt_num := 40;
632 update cst_gl_summary_links_temp link1
633 set link1. gl_dr_cr_flag = 0
634 where link1.gl_dr_cr_flag in (1,-1)
635 and not exists ( select 1 from cst_gl_summary_links_temp link2
636 where link1.gl_batch_id = link2.gl_batch_id
637 and link1.reference_account = link2.reference_account
638 and link1.gl_currency_code = link2.gl_currency_code
639 and link1.ussgl_transaction_code = link2.ussgl_transaction_code
640 and ( ( link1.gl_dr_cr_flag = 1 and link2.gl_dr_cr_flag = -1 )
641 or ( link1.gl_dr_cr_flag = -1 and link2.gl_dr_cr_flag = 1 )
642 )
643 );
644 debug(' Corrected '||SQL%ROWCOUNT||' rows in SL');
645 debug(' <Validating data in SL');
646
647 debug(' >Populating GL_SL_LINK_ID in GIR');
648 l_count := 0;
649 OPEN c_gir_links;
650 LOOP
651 l_count := l_count + 1;
652 l_stmnt_num := 50;
653 FETCH c_gir_links bulk collect
654 into gir_rowid_tab,
655 gl_sl_link_id_tab
656 limit bulk_size;
657
658 debug(' L'||l_count||': '||gir_rowid_tab.COUNT||' Records fetched by cursor');
659 IF gir_rowid_tab.count = 0 THEN
660 EXIT;
661 END IF;
662
663 l_stmnt_num := 60;
664 FORALL i in 1..gir_rowid_tab.count
665 update gl_import_references gir
666 set gir.gl_sl_link_id = gl_sl_link_id_tab(i)
667 where gir.rowid = gir_rowid_tab(i)
668 and gir.gl_sl_link_id is null; --Support Flexible Logic
669
670 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in GIR');
671
672 EXIT WHEN c_gir_links%NOTFOUND;
673 END LOOP;
674 CLOSE c_gir_links;
675 debug(' <Populating GL_SL_LINK_ID in GIR');
676
677 debug(' >Updating GL_SL_LINK_ID for WTA and XAL');
678 l_count := 0;
679 OPEN c_wta;
680 LOOP
681 l_count := l_count + 1;
682 l_stmnt_num := 70;
683 FETCH c_wta bulk collect
684 into wta_rowid_tab,
685 wip_sub_ledger_id_tab,
686 gl_sl_link_id_tab
687 limit bulk_size;
688
689 debug(' L'||l_count||': '||wta_rowid_tab.COUNT||' Records fetched by cursor');
690 IF wta_rowid_tab.count = 0 THEN
691 EXIT;
692 END IF;
693
694 l_stmnt_num := 80;
695 FORALL i in 1..wta_rowid_tab.count
696 update wip_transaction_accounts wta
697 set wta.gl_sl_link_id = gl_sl_link_id_tab(i)
698 where wta.rowid = wta_rowid_tab(i);
699
700 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in WTA');
701
702 l_stmnt_num := 90;
703 FORALL i in 1..wta_rowid_tab.count
704 update /*+ index(xal xla_ae_lines_u1) */ xla_ae_lines xal
705 set xal.gl_sl_link_id = gl_sl_link_id_tab(i)
706 where xal.application_id = 707
707 and xal.gl_sl_link_table = 'WTA'
708 and (xal.ae_header_id, xal.ae_line_num)
709 in (select /*+ index(xdl xla_distribution_links_n1) */
710 xdl.ae_header_id,
711 xdl.ae_line_num
712 from xla_distribution_links xdl
713 where XDL.application_id =707 /*Added for bug 16217359*/
714 AND xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'
715 and xdl.source_distribution_id_num_1 = wip_sub_ledger_id_tab(i));
716
717 debug(' L'||l_count||': Updated '||SQL%ROWCOUNT||' rows in XAL');
718
719 EXIT WHEN c_wta%NOTFOUND;
720 END LOOP;
721 CLOSE c_wta;
722 debug(' <Updating GL_SL_LINK_ID for WTA and XAL');
723
724 ELSE
725 debug(' No WTA records to be updated for gl_batch_id : '||p_je_batch_id);
726 END IF;
727
728 commit;
729 debug('cst_sl_link_upg_wta -');
730 EXCEPTION
731 WHEN OTHERS THEN
732 IF c_wta%ISOPEN THEN CLOSE c_wta; END IF;
733 IF c_gir_links%ISOPEN THEN CLOSE c_gir_links; END IF;
734 IF c_gl_lines%ISOPEN THEN CLOSE c_gl_lines; END IF;
735 debug('EXCEPTION OTHERS cst_sl_link_upg_wta ('||l_stmnt_num||') :'||SQLERRM);
736 RAISE;
737 END cst_sl_link_upg_wta;
738
739 ------------------------------------------------------------------------------------
740 -- API name : update_mta_wta
741 -- Type : Public
742 -- Function : Procedure to update links in MTA and WTA for summary mode transactions
743 --
744 -- Pre-reqs :
745 -- Parameters :
746 -- IN : p_from_date IN VARCHAR2
747 -- p_to_date IN VARCHAR2
748 -- p_ledger_id IN NUMBER
749 --
750 -- OUT :
751 --
752 -- End of comments
753 -------------------------------------------------------------------------------------
754 PROCEDURE update_mta_wta
755 (errbuf OUT NOCOPY VARCHAR2,
756 retcode OUT NOCOPY NUMBER,
757 p_from_date IN VARCHAR2,
758 p_to_date IN VARCHAR2,
759 p_ledger_id IN NUMBER)
760 IS
761
762 CURSOR c_glb ( l_from_date IN DATE,
763 l_to_date IN DATE ) IS
764 SELECT DISTINCT gh.je_batch_id
765 FROM gl_period_statuses gps,
766 gl_je_headers gh
767 WHERE gps.set_of_books_id = p_ledger_id
768 AND gps.migration_status_code = 'U'
769 AND gps.application_id = 401
770 AND gps.start_date >= l_from_date
771 AND gps.end_date <= l_to_date
772 AND gh.ledger_id = gps.set_of_books_id
773 AND gh.period_name = gps.period_name
774 AND gh.je_source = 'Cost Management'
775 AND gh.je_category in ('MTL', 'WIP');
776
777 l_je_batch_id NUMBER;
778 l_from_date DATE;
779 l_to_date DATE;
780
781 BEGIN
782 debug('update_mta_wta +');
783 debug(' p_from_date :'||p_from_date);
784 debug(' p_to_date :'||p_to_date);
785 debug(' p_ledger_id :'||p_ledger_id);
786
787
788 -- l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
789 -- l_to_date := to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
790
791 l_from_date := to_date(p_from_date,'YYYY/MM/DD');
792 l_to_date := to_date(p_to_date,'YYYY/MM/DD');
793
794 OPEN c_glb(l_from_date, l_to_date);
795 LOOP
796 FETCH c_glb INTO l_je_batch_id;
797 debug(' l_je_batch_id :'||l_je_batch_id);
798 EXIT WHEN c_glb%NOTFOUND;
799 cst_sl_link_upg_mta (p_je_batch_id => l_je_batch_id);
800 cst_sl_link_upg_wta (p_je_batch_id => l_je_batch_id);
801 END LOOP;
802 CLOSE c_glb;
803
804 debug('update_mta_wta -');
805
806 EXCEPTION
807 WHEN OTHERS THEN
808 IF c_glb%ISOPEN THEN CLOSE c_glb; END IF;
809 debug('EXCEPTION OTHERS update_mta_wta :'||SQLERRM);
810 RAISE;
811 END update_mta_wta;
812
813 END;
[Home] [Help]