[Home] [Help]
PACKAGE BODY: APPS.GL_JOURNALS_AUTOCOPY
Source
1 PACKAGE BODY GL_JOURNALS_AUTOCOPY AS
2 /* $Header: glujecpb.pls 120.15 2012/01/25 09:33:55 degoel ship $ */
3
4 -- ********************************************************************
5
6 PROCEDURE do_autocopy(Jeb_id NUMBER,
7 New_Name VARCHAR2,
8 New_Period_Name VARCHAR2,
9 New_Eff_Date DATE,
10 X_Debug VARCHAR2 DEFAULT NULL) IS
11
12 GLUJECPB_FATAL_ERR EXCEPTION;
13 usr_id NUMBER;
14 log_id NUMBER;
15 dmode_profile fnd_profile_option_values.profile_option_value%TYPE;
16 dmode BOOLEAN;
17 new_jeb_id NUMBER;
18 bc_flag VARCHAR2(1);
19 approval_flag VARCHAR2(1);
20 tmp NUMBER;
21 x_org_id fnd_profile_option_values.profile_option_value%TYPE;
22 seq_num fnd_profile_option_values.profile_option_value%TYPE;
23 act_flag VARCHAR2(1);
24 org_id NUMBER;
25 temp NUMBER;
26 BEGIN
27
28 GL_MESSAGE.Func_Ent(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
29
30 -- Obtain user ID and login ID
31 usr_id := FND_GLOBAL.User_Id;
32 log_id := FND_GLOBAL.Login_Id;
33
34 -- Get profile option values
35 FND_PROFILE.GET('GL_DEBUG_MODE', dmode_profile);
36 FND_PROFILE.GET('ORG_ID', x_org_id);
37 FND_PROFILE.GET('UNIQUE:SEQ_NUMBERS', seq_num);
38
39 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
40 token_num => 3 ,
41 t1 =>'ROUTINE',
42 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
43 t2 =>'VARIABLE',
44 v2 =>'Debug Mode',
45 t3 =>'VALUE',
46 v3 => dmode_profile);
47
48 -- Determine if process will be run in debug mode
49 IF (NVL(X_Debug, 'N') <> 'N') OR (dmode_profile = 'Y') THEN
50 dmode := TRUE;
51
52 -- If debug mode, print out what we have so far
53 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
54 token_num => 3 ,
55 t1 =>'ROUTINE',
56 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
57 t2 =>'VARIABLE',
58 v2 =>'JEB_ID',
59 t3 =>'VALUE',
60 v3 => to_char(Jeb_id));
61 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
62 token_num => 3 ,
63 t1 =>'ROUTINE',
64 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
65 t2 =>'VARIABLE',
66 v2 =>'NEW_NAME',
67 t3 =>'VALUE',
68 v3 => New_Name);
69 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
70 token_num => 3 ,
71 t1 =>'ROUTINE',
72 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
73 t2 =>'VARIABLE',
74 v2 =>'NEW_PERIOD_NAME',
75 t3 =>'VALUE',
76 v3 => New_Period_Name);
77 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
78 token_num => 3 ,
79 t1 =>'ROUTINE',
80 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
81 t2 =>'VARIABLE',
82 v2 =>'NEW_EFF_DATE',
83 t3 =>'VALUE',
84 v3 => to_char(New_Eff_Date,'DD-MON-RR'));
85 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
86 token_num => 3 ,
87 t1 =>'ROUTINE',
88 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
89 t2 =>'VARIABLE',
90 v2 =>'ORG_ID',
91 t3 =>'VALUE',
92 v3 => Org_id);
93 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
94 token_num => 3 ,
95 t1 =>'ROUTINE',
96 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
97 t2 =>'VARIABLE',
98 v2 =>'SEQ_NUM',
99 t3 =>'VALUE',
100 v3 => Seq_Num);
101 ELSE
102 dmode := FALSE;
103 END IF;
104
105 -- Make sure the batch exists and get the actual_flag for use later on.
106 BEGIN
107 SELECT actual_flag
108 INTO act_flag
109 FROM gl_je_batches
110 WHERE je_batch_id = jeb_id;
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 GL_MESSAGE.Write_Log(msg_name =>'JECP0000',
114 token_num => 1,
115 t1 =>'JEB_ID',
116 v1 =>to_char(jeb_id));
117 Raise GLUJECPB_FATAL_ERR;
118 END;
119
120 -- Get the new je_batch_id
121 SELECT gl_je_batches_s.nextval
122 INTO new_jeb_id
123 FROM dual;
124
125 IF (SQL%ROWCOUNT <> 1) THEN
126 Raise GLUJECPB_FATAL_ERR;
127 END IF;
128
129 IF (dmode) THEN
130 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
131 token_num => 3 ,
132 t1 =>'ROUTINE',
133 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
134 t2 =>'VARIABLE',
135 v2 =>'NEW_JEB_ID',
136 t3 =>'VALUE',
137 v3 => to_char(new_jeb_id));
138 END IF;
139
140 -- Determine the appropriate approval and budgetary control settings
141 SELECT nvl(max(decode(enable_budgetary_control_flag, 'Y', 'Y', null)),'N'),
142 nvl(max(decode(enable_je_approval_flag, 'Y', 'Y', null)), 'N')
143 INTO bc_flag, approval_flag
144 FROM gl_je_headers jeh, gl_ledgers lgr
145 WHERE jeh.je_batch_id = jeb_id
146 AND lgr.ledger_id = jeh.ledger_id;
147
148 IF (SQL%ROWCOUNT <> 1) THEN
149 Raise GLUJECPB_FATAL_ERR;
150 END IF;
151
152 -- If approval is on, verify that AutoCopy journals require approval
153 IF (approval_flag = 'Y') THEN
154 SELECT journal_approval_flag
155 INTO approval_flag
156 FROM gl_je_sources
157 WHERE je_source_name = 'AutoCopy';--Modified the source from Manual to Autocopy as part of bug7373688
158 END IF;
159
160 IF (dmode) THEN
161 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
162 token_num => 3 ,
163 t1 =>'ROUTINE',
164 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
165 t2 =>'VARIABLE',
166 v2 =>'BC_FLAG',
167 t3 =>'VALUE',
168 v3 => bc_flag);
169 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
170 token_num => 3 ,
171 t1 =>'ROUTINE',
172 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
173 t2 =>'VARIABLE',
174 v2 =>'APPROVAL_FLAG',
175 t3 =>'VALUE',
176 v3 => approval_flag);
177 END IF;
178
179 -- Insert new batch here
180 INSERT INTO gl_je_batches
181 (je_batch_id,
182 chart_of_accounts_id, period_set_name, accounted_period_type,
183 name, status, status_verified, budgetary_control_status,
184 actual_flag, average_journal_flag,
185 default_effective_date, default_period_name,
186 date_created, description, control_total,
187 attribute1, attribute2, attribute3, attribute4, attribute5,
188 attribute6, attribute7, attribute8, attribute9, attribute10, context,
189 ussgl_transaction_code, org_id, approval_status_code,
190 creation_date, created_by,
191 last_update_date, last_updated_by, last_update_login)
192 SELECT
193 new_jeb_id,
194 chart_of_accounts_id, period_set_name, accounted_period_type,
195 New_Name, 'U', 'N', decode(bc_flag, 'Y', 'R', 'N'),
196 actual_flag, average_journal_flag,
197 New_eff_date, New_period_name,
198 sysdate, description, control_total,
199 attribute1, attribute2, attribute3, attribute4, attribute5,
200 attribute6, attribute7, attribute8, attribute9, attribute10, context,
201 ussgl_transaction_code, to_number(x_org_id),
202 decode(approval_flag, 'Y', 'R', 'Z'),
203 sysdate, usr_id, sysdate, usr_id, log_id
204 FROM gl_je_batches
205 WHERE je_batch_id = jeb_id;
206
207 IF (SQL%ROWCOUNT <> 1) THEN
208 Raise GLUJECPB_FATAL_ERR;
209 END IF;
210
211 temp := SQL%ROWCOUNT;
212 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
213 token_num => 2 ,
214 t1 =>'NUM',
215 v1 =>to_char(temp),
216 t2 =>'TABLE',
217 v2 =>'GL_JE_BATCHES');
218
219 DECLARE
220
221 CURSOR select_journals IS
222 SELECT jeh.je_header_id, jeh.ledger_id, jeh.je_category,
223 jeh.currency_code, jeh.currency_conversion_type,
224 jeh.currency_conversion_date, jeh.currency_conversion_rate,
225 lgr.currency_code
226 FROM gl_je_headers jeh, gl_ledgers lgr
227 WHERE jeh.je_batch_id = jeb_id
228 AND lgr.ledger_id = jeh.ledger_id
229 --Commented this as part of bug 7581299.
230 --AND jeh.parent_je_header_id IS NULL; /* See comments above */--Uncommented this as part of bug 7373688.
231 AND nvl(jeh.parent_je_header_id,0) = decode(lgr.ledger_category_code,'SECONDARY',nvl(jeh.parent_je_header_id,0),
232 'PRIMARY',0,
233 'ALC',-999999,0);
234
235
236 jeh_id NUMBER;
237 ledger_id NUMBER;
238 je_category VARCHAR2(25);
239 currency_code VARCHAR2(15);
240 conversion_date DATE;
241 conversion_type VARCHAR2(25);
242 conversion_rate NUMBER;
243 rev_method VARCHAR2(1);
244 rev_period VARCHAR2(15);
245 rev_date DATE;
246 funct_curr VARCHAR2(15);
247 BEGIN
248 temp := 0;
249
250 OPEN select_journals;
251 LOOP
252 FETCH select_journals
253 INTO jeh_id, ledger_id, je_category,
254 currency_code, conversion_type, conversion_date,
255 conversion_rate, funct_curr;
256 EXIT WHEN select_journals%NOTFOUND;
257
258 -- Clear out reversal fields
259 rev_method := null;
260 rev_period := null;
261 rev_date := null;
262
263 -- Get new default reversal information
264 gl_autoreverse_date_pkg.get_reversal_period_date(
265 X_Ledger_Id => ledger_id,
266 X_Je_Category => je_category,
267 X_Je_Source => 'AutoCopy',---Modified the source from Manual to Autocopy as part of bug7373688
268 X_Je_Period_Name => New_period_name,
269 X_Je_Date => New_eff_date,
270 X_Reversal_Method => rev_method,
271 X_Reversal_Period => rev_period,
272 X_Reversal_Date => rev_date);
273
274 -- Get default reversal method, if provided
275 IF (rev_method IS NULL) THEN
276 gl_autoreverse_date_pkg.get_default_reversal_method(
277 X_Ledger_Id => ledger_id,
278 X_Category_Name => je_category,
279 X_Reversal_Method_Code => rev_method);
280 END IF;
281
282 -- If the conversion type wasn't user, than try to get the
283 -- new conversion rate as of the new effective date. If you
284 -- can't get one, than leave the conversion information alone.
285 IF (conversion_type <> 'User') THEN
286 BEGIN
287 conversion_rate := gl_currency_api.get_rate(
288 currency_code,
289 funct_curr,
290 New_eff_date,
291 conversion_type);
292 conversion_date := New_eff_date;
293 EXCEPTION
294 WHEN gl_currency_api.no_rate THEN
295 null;
296 END;
297 ELSE
298 conversion_date := New_eff_date;
299 END IF;
300
301 IF (dmode) THEN
302 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
303 token_num => 3 ,
304 t1 =>'ROUTINE',
305 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
306 t2 =>'VARIABLE',
307 v2 =>'REV_METHOD',
308 t3 =>'VALUE',
309 v3 => rev_method);
310 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
311 token_num => 3 ,
312 t1 =>'ROUTINE',
313 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
314 t2 =>'VARIABLE',
315 v2 =>'REV_PERIOD',
316 t3 =>'VALUE',
317 v3 => rev_period);
318 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
319 token_num => 3 ,
320 t1 =>'ROUTINE',
321 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
322 t2 =>'VARIABLE',
323 v2 =>'REV_DATE',
324 t3 =>'VALUE',
325 v3 => to_char(rev_date,'DD-MON-RR'));
326 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
327 token_num => 3 ,
328 t1 =>'ROUTINE',
329 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
330 t2 =>'VARIABLE',
331 v2 =>'CONVERSION_RATE',
332 t3 =>'VALUE',
333 v3 => to_char(conversion_rate,
334 '999999999999.99999999999999'));
335 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
336 token_num => 3 ,
337 t1 =>'ROUTINE',
338 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
339 t2 =>'VARIABLE',
340 v2 =>'CONV_DATE',
341 t3 =>'VALUE',
342 v3 => to_char(conversion_date,
343 'DD-MON-RR'));
344 END IF;
345
346 INSERT INTO gl_je_headers
347 (je_batch_id, je_header_id, ledger_id,
348 je_category, je_source, default_effective_date, period_name,
349 name, currency_code, status, date_created,
350 multi_bal_seg_flag, actual_flag,
351 conversion_flag, encumbrance_type_id, budget_version_id,
352 accrual_rev_flag, accrual_rev_effective_date,
353 accrual_rev_period_name, accrual_rev_change_sign_flag,
354 description, control_total,
355 currency_conversion_type, currency_conversion_date,
356 currency_conversion_rate, external_reference,
357 attribute1, attribute2, attribute3, attribute4, attribute5,
358 attribute6, attribute7, attribute8, attribute9, attribute10,context,
359 ussgl_transaction_code, jgzz_recon_context, jgzz_recon_ref,
360 tax_status_code, reference_date, originating_bal_seg_value,
361 creation_date, created_by,
362 last_update_date, last_updated_by, last_update_login)
363 SELECT
364 new_jeb_id, gl_je_headers_s.nextval, ledger_id,
365 je_category, 'AutoCopy', New_eff_date, New_period_name,---Modified the source from Manual to Autocopy as part of bug7373688
366 decode(parent_je_header_id, NULL, name,
367 substrb(name, 1, (100 - (lengthb(to_char(je_header_id))+1)))
368 || ' ' || to_char(je_header_id)),
369 currency_code, 'U', sysdate,
370 'N', actual_flag,
371 conversion_flag, encumbrance_type_id, budget_version_id,
372 decode(rev_period, NULL, 'N', 'Y'), rev_date,
373 rev_period, rev_method,
374 description, control_total,
375 conversion_type, conversion_date,
376 conversion_rate, external_reference,
377 attribute1, attribute2, attribute3, attribute4, attribute5,
378 attribute6, attribute7, attribute8, attribute9, attribute10,context,
379 ussgl_transaction_code, jgzz_recon_context, jgzz_recon_ref,
380 'N', reference_date, originating_bal_seg_value,
381 sysdate, usr_id, sysdate, usr_id, log_id
382 FROM gl_je_headers
383 WHERE je_header_id = jeh_id;
384
385 IF (SQL%ROWCOUNT <> 1) THEN
386 Raise GLUJECPB_FATAL_ERR;
387 END IF;
388
389 temp := temp + 1;
390 END LOOP;
391 END;
392
393 temp := SQL%ROWCOUNT;
394 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
395 token_num => 2 ,
396 t1 =>'NUM',
397 v1 =>to_char(temp),
398 t2 =>'TABLE',
399 v2 =>'GL_JE_HEADERS');
400
401 -- Insert the new journal lines
402 INSERT INTO gl_je_lines
403 (je_header_id, je_line_num, ledger_id,
404 code_combination_id, period_name, effective_date,
405 status, entered_dr, entered_cr, accounted_dr, accounted_cr,
406 description, stat_amount, ignore_rate_flag,
407 attribute1, attribute2, attribute3, attribute4, attribute5,
408 attribute6, attribute7, attribute8, attribute9, attribute10, context,
409 attribute11, attribute12, attribute13, attribute14, attribute15,
410 attribute16, attribute17, attribute18, attribute19,attribute20,context2,
411 no1,--Added this as part of bug6521457
412 ussgl_transaction_code,
413 co_third_party, creation_date, created_by,
414 last_update_date, last_updated_by, last_update_login)
415 SELECT
416 jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
417 jel.code_combination_id, jeh2.period_name,
418 jeh2.default_effective_date,
419 'U', jel.entered_dr, jel.entered_cr,
420 decode(jel.ignore_rate_flag, 'Y', jel.accounted_dr, decode(jeh2.currency_code,'STAT',jel.accounted_dr,
421 decode(curr.minimum_accountable_unit,
422 NULL, round(jeh2.currency_conversion_rate * jel.entered_dr,
423 precision),
424 round(jeh2.currency_conversion_rate * jel.entered_dr
425 / curr.minimum_accountable_unit)
426 * curr.minimum_accountable_unit))),
427 decode(jel.ignore_rate_flag, 'Y', jel.accounted_cr,decode(jeh2.currency_code,'STAT',jel.accounted_cr,
428 decode(curr.minimum_accountable_unit,
429 NULL, round(jeh2.currency_conversion_rate * jel.entered_cr,
430 precision),
431 round(jeh2.currency_conversion_rate * jel.entered_cr
432 / curr.minimum_accountable_unit)
433 * curr.minimum_accountable_unit))),
434 jel.description, jel.stat_amount, jel.ignore_rate_flag,
435 jel.attribute1, jel.attribute2, jel.attribute3, jel.attribute4,
436 jel.attribute5, jel.attribute6, jel.attribute7, jel.attribute8,
437 jel.attribute9, jel.attribute10, jel.context,
438 jel.attribute11, jel.attribute12, jel.attribute13, jel.attribute14,
439 jel.attribute15, jel.attribute16, jel.attribute17, jel.attribute18,
440 jel.attribute19, jel.attribute20, jel.context2,
441 jel.no1,--Added this as part of bug6521457
442 jel.ussgl_transaction_code,
443 jel.co_third_party,
444 sysdate, usr_id, sysdate, usr_id, log_id
445 FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_ledgers lgr,
446 fnd_currencies curr, gl_je_lines jel
447 WHERE jeh1.je_batch_id = jeb_id
448 AND jeh2.je_batch_id = new_jeb_id
449 AND jeh2.name IN (jeh1.name,
450 substrb(jeh1.name,
451 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
452 || ' ' || to_char(jeh1.je_header_id))
453 AND lgr.ledger_id = jeh2.ledger_id
454 AND curr.currency_code = lgr.currency_code
455 AND jel.je_header_id = jeh1.je_header_id
456 AND nvl(jel.tax_line_flag,'N') = 'N';
457
458 temp := SQL%ROWCOUNT;
459 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
460 token_num => 2 ,
461 t1 =>'NUM',
462 v1 =>to_char(temp),
463 t2 =>'TABLE',
464 v2 =>'GL_JE_LINES');
465
466 -- Insert the new journal segment values
467 INSERT INTO gl_je_segment_values
468 (je_header_id, segment_type_code, segment_value,
469 creation_date, created_by,
470 last_update_date, last_updated_by, last_update_login)
471 SELECT
472 jeh2.je_header_id, sv.segment_type_code, sv.segment_value,
473 sysdate, usr_id, sysdate, usr_id, log_id
474 FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_je_segment_values sv
475 WHERE jeh1.je_batch_id = jeb_id
476 AND jeh2.je_batch_id = new_jeb_id
477 AND jeh2.name IN (jeh1.name,
478 substrb(jeh1.name,
479 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
480 || ' ' || to_char(jeh1.je_header_id))
481 AND sv.je_header_id = jeh1.je_header_id;
482
483 temp := SQL%ROWCOUNT;
484 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
485 token_num => 2 ,
486 t1 =>'NUM',
487 v1 =>to_char(temp),
488 t2 =>'TABLE',
489 v2 =>'GL_JE_SEGMENT_VALUES');
490
491 -- Insert reconciliation data
492 INSERT INTO gl_je_lines_recon
493 (je_header_id, je_line_num, ledger_id,
494 jgzz_recon_ref,
495 creation_date, created_by, last_update_date,
496 last_updated_by, last_update_login)
497 SELECT jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
498 rec.jgzz_recon_ref,
499 sysdate, usr_id, sysdate,
500 usr_id, log_id
501 FROM gl_je_batches jeb, gl_je_headers jeh1, gl_je_headers jeh2,
502 gl_ledgers lgr, gl_je_lines jel,
503 gl_code_combinations cc, gl_je_lines_recon rec
504 WHERE jeb.je_batch_id = jeb_id
505 AND jeb.average_journal_flag = 'N'
506 AND jeh1.je_batch_id = jeb_id
507 AND jeh1.actual_flag = 'A'
508 AND jeh1.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
509 AND lgr.ledger_id = jeh1.ledger_id
510 AND lgr.enable_reconciliation_flag = 'Y'
511 AND jeh2.je_batch_id = new_jeb_id
512 AND jeh2.name IN (jeh1.name,
513 substrb(jeh1.name,
514 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
515 || ' ' || to_char(jeh1.je_header_id))
516 AND jel.je_header_id = jeh1.je_header_id
517 AND nvl(jel.tax_line_flag,'N') = 'N'
518 AND cc.code_combination_id = jel.code_combination_id
519 AND cc.jgzz_recon_flag = 'Y'
520 AND rec.je_header_id(+) = jel.je_header_id
521 AND rec.je_line_num(+) = jel.je_line_num;
522
523 temp := SQL%ROWCOUNT;
524 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
525 token_num => 2 ,
526 t1 =>'NUM',
527 v1 =>to_char(temp),
528 t2 =>'TABLE',
529 v2 =>'GL_JE_LINES_RECON');
530
531 -- Fix the running totals for the journals
532 UPDATE gl_je_headers jeh
533 SET (running_total_dr, running_total_cr,
534 running_total_accounted_dr, running_total_accounted_cr)
535 = (SELECT sum(nvl(entered_dr,0)), sum(nvl(entered_cr,0)),
536 sum(nvl(accounted_dr,0)), sum(nvl(accounted_cr,0))
537 FROM gl_je_lines jel
538 WHERE jel.je_header_id = jeh.je_header_id)
539 WHERE jeh.je_batch_id = new_jeb_id;
540
541 -- Fix the batch running totals
542 UPDATE gl_je_batches jeb
543 SET (running_total_dr, running_total_cr,
544 running_total_accounted_dr, running_total_accounted_cr)
545 = (SELECT sum(running_total_dr),
546 sum(running_total_cr),
547 sum(running_total_accounted_dr),
548 sum(running_total_accounted_cr)
549 FROM gl_je_headers jeh
550 WHERE jeh.je_batch_id = jeb.je_batch_id)
551 WHERE jeb.je_batch_id = new_jeb_id;
552
553 -- If sequential numbering is on and this is an
554 -- actual batch, than try to get sequential numbering
555 -- information
556 IF ( (seq_num <> 'N') AND (act_flag = 'A')) THEN
557 DECLARE
558 je_category gl_je_headers.je_category%TYPE; /*Bug 6665535*/
559 lgr_id gl_je_headers.ledger_id%TYPE; /*Bug 6665535*/
560 effdate DATE;
561 seq_id NUMBER;
562 seq_val NUMBER;
563 row_id ROWID;
564 seq_result NUMBER;
565 je_name gl_je_headers.name%TYPE; /*Bug 6665535*/
566 CURSOR new_journals IS
567 SELECT rowid, ledger_id, je_category,
568 substrb(name, 25)
569 FROM gl_je_headers
570 WHERE je_batch_id = new_jeb_id;
571 BEGIN
572
573 OPEN new_journals;
574 LOOP
575 seq_val := NULL;
576 seq_id := NULL;
577 FETCH new_journals INTO row_id, lgr_id, je_category, je_name;
578 EXIT WHEN new_journals%NOTFOUND;
579
580 IF (dmode) THEN
581 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
582 token_num => 3 ,
583 t1 =>'ROUTINE',
584 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
585 t2 =>'VARIABLE',
586 v2 =>'JE_CATEGORY',
587 t3 =>'VALUE',
588 v3 => je_category);
589 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
590 token_num => 3 ,
591 t1 =>'ROUTINE',
592 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
593 t2 =>'VARIABLE',
594 v2 =>'LGR_ID',
595 t3 =>'VALUE',
596 v3 => to_char(lgr_id));
597 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
598 token_num => 3 ,
599 t1 =>'ROUTINE',
600 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
601 t2 =>'VARIABLE',
602 v2 =>'EFFDATE',
603 t3 =>'VALUE',
604 v3 => to_char(New_eff_date,
605 'DD-MON-RR'));
606 END IF;
607
608 seq_result := FND_SEQNUM.get_seq_val(
609 app_id => 101,
610 cat_code => je_category,
611 sob_id => lgr_id,
612 met_code => 'A',
613 trx_date => New_eff_date,
614 seq_val => seq_val,
615 docseq_id => seq_id,
616 suppress_warn => 'Y');
617
618 IF (dmode) THEN
619 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
620 token_num => 3 ,
621 t1 =>'ROUTINE',
622 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
623 t2 =>'VARIABLE',
624 v2 =>'SEQ_RESULT',
625 t3 =>'VALUE',
626 v3 => to_char(seq_result));
627 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
628 token_num => 3 ,
629 t1 =>'ROUTINE',
630 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
631 t2 =>'VARIABLE',
632 v2 =>'SEQ_VAL',
633 t3 =>'VALUE',
634 v3 => to_char(seq_val));
635 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
636 token_num => 3 ,
637 t1 =>'ROUTINE',
638 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
639 t2 =>'VARIABLE',
640 v2 =>'SEQ_ID',
641 t3 =>'VALUE',
642 v3 => to_char(seq_id));
643 END IF;
644
645 IF ((seq_result = 0) AND (seq_val IS NOT NULL)) THEN
646 UPDATE gl_je_headers
647 SET doc_sequence_id = seq_id,
648 doc_sequence_value = seq_val
649 WHERE rowid = row_id;
650 ELSIF (seq_num = 'A') THEN
651 GL_MESSAGE.Write_Log(msg_name =>'JECP0001',
652 token_num => 2 ,
653 t1 =>'NAME',
654 v1 => je_name);
655 Raise GLUJECPB_FATAL_ERR;
656 END IF;
657 END LOOP;
658 CLOSE new_journals;
659 END;
660 END IF;
661
662 -- Commit all work
663 FND_CONCURRENT.Af_Commit;
664
665 GL_MESSAGE.Func_Succ(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 Rollback;
670 GL_MESSAGE.Func_Fail(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
671 RAISE;
672 END do_autocopy;
673
674 -- ********************************************************************
675
676 PROCEDURE do_autocopy(errbuf OUT NOCOPY VARCHAR2,
677 retcode OUT NOCOPY VARCHAR2,
678 Jeb_id NUMBER,
679 New_Name VARCHAR2,
680 New_Period_Name VARCHAR2,
681 New_Eff_Date VARCHAR2,
682 X_Debug VARCHAR2 DEFAULT NULL) IS
683 BEGIN
684 GL_MESSAGE.Func_Ent(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
685 GL_JOURNALS_AUTOCOPY.do_autocopy(
686 Jeb_id => Jeb_id,
687 New_Name => New_Name,
688 New_Period_Name => New_Period_Name,
689 New_Eff_Date => to_date(New_Eff_Date, 'YYYY/MM/DD'),
690 X_Debug => X_Debug);
691 GL_MESSAGE.Func_Succ(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
692
693 EXCEPTION
694 WHEN OTHERS THEN
695 GL_MESSAGE.Func_Fail(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
696 errbuf := SQLERRM ;
697 retcode := '2';
698 ROLLBACK;
699 app_exception.raise_exception;
700 END do_autocopy;
701
702 END GL_JOURNALS_AUTOCOPY;