[Home] [Help]
PACKAGE BODY: APPS.GL_JOURNALS_AUTOCOPY
Source
1 PACKAGE BODY GL_JOURNALS_AUTOCOPY AS
2 /* $Header: glujecpb.pls 120.6.12010000.6 2008/11/25 08:45:04 skotakar 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 ussgl_transaction_code,
412 co_third_party, creation_date, created_by,
413 last_update_date, last_updated_by, last_update_login)
414 SELECT
415 jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
416 jel.code_combination_id, jeh2.period_name,
417 jeh2.default_effective_date,
418 'U', jel.entered_dr, jel.entered_cr,
419 decode(jel.ignore_rate_flag, 'Y', jel.accounted_dr,
420 decode(curr.minimum_accountable_unit,
421 NULL, round(jeh2.currency_conversion_rate * jel.entered_dr,
422 precision),
423 round(jeh2.currency_conversion_rate * jel.entered_dr
424 / curr.minimum_accountable_unit)
425 * curr.minimum_accountable_unit)),
426 decode(jel.ignore_rate_flag, 'Y', jel.accounted_cr,
427 decode(curr.minimum_accountable_unit,
428 NULL, round(jeh2.currency_conversion_rate * jel.entered_cr,
429 precision),
430 round(jeh2.currency_conversion_rate * jel.entered_cr
431 / curr.minimum_accountable_unit)
432 * curr.minimum_accountable_unit)),
433 jel.description, jel.stat_amount, jel.ignore_rate_flag,
434 jel.attribute1, jel.attribute2, jel.attribute3, jel.attribute4,
435 jel.attribute5, jel.attribute6, jel.attribute7, jel.attribute8,
436 jel.attribute9, jel.attribute10, jel.context,
437 jel.attribute11, jel.attribute12, jel.attribute13, jel.attribute14,
438 jel.attribute15, jel.attribute16, jel.attribute17, jel.attribute18,
439 jel.attribute19, jel.attribute20, jel.context2,
440 jel.ussgl_transaction_code,
441 jel.co_third_party,
442 sysdate, usr_id, sysdate, usr_id, log_id
443 FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_ledgers lgr,
444 fnd_currencies curr, gl_je_lines jel
445 WHERE jeh1.je_batch_id = jeb_id
446 AND jeh2.je_batch_id = new_jeb_id
447 AND jeh2.name IN (jeh1.name,
448 substrb(jeh1.name,
449 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
450 || ' ' || to_char(jeh1.je_header_id))
451 AND lgr.ledger_id = jeh2.ledger_id
452 AND curr.currency_code = lgr.currency_code
453 AND jel.je_header_id = jeh1.je_header_id
454 AND nvl(jel.tax_line_flag,'N') = 'N';
455
456 temp := SQL%ROWCOUNT;
457 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
458 token_num => 2 ,
459 t1 =>'NUM',
460 v1 =>to_char(temp),
461 t2 =>'TABLE',
462 v2 =>'GL_JE_LINES');
463
464 -- Insert the new journal segment values
465 INSERT INTO gl_je_segment_values
466 (je_header_id, segment_type_code, segment_value,
467 creation_date, created_by,
468 last_update_date, last_updated_by, last_update_login)
469 SELECT
470 jeh2.je_header_id, sv.segment_type_code, sv.segment_value,
471 sysdate, usr_id, sysdate, usr_id, log_id
472 FROM gl_je_headers jeh1, gl_je_headers jeh2, gl_je_segment_values sv
473 WHERE jeh1.je_batch_id = jeb_id
474 AND jeh2.je_batch_id = new_jeb_id
475 AND jeh2.name IN (jeh1.name,
476 substrb(jeh1.name,
477 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
478 || ' ' || to_char(jeh1.je_header_id))
479 AND sv.je_header_id = jeh1.je_header_id;
480
481 temp := SQL%ROWCOUNT;
482 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
483 token_num => 2 ,
484 t1 =>'NUM',
485 v1 =>to_char(temp),
486 t2 =>'TABLE',
490 INSERT INTO gl_je_lines_recon
487 v2 =>'GL_JE_SEGMENT_VALUES');
488
489 -- Insert reconciliation data
491 (je_header_id, je_line_num, ledger_id,
492 jgzz_recon_ref,
493 creation_date, created_by, last_update_date,
494 last_updated_by, last_update_login)
495 SELECT jeh2.je_header_id, jel.je_line_num, jel.ledger_id,
496 rec.jgzz_recon_ref,
497 sysdate, usr_id, sysdate,
498 usr_id, log_id
499 FROM gl_je_batches jeb, gl_je_headers jeh1, gl_je_headers jeh2,
500 gl_ledgers lgr, gl_je_lines jel,
501 gl_code_combinations cc, gl_je_lines_recon rec
502 WHERE jeb.je_batch_id = jeb_id
503 AND jeb.average_journal_flag = 'N'
504 AND jeh1.je_batch_id = jeb_id
505 AND jeh1.actual_flag = 'A'
506 AND jeh1.je_source NOT IN ('Move/Merge', 'Move/Merge Reversal')
507 AND lgr.ledger_id = jeh1.ledger_id
508 AND lgr.enable_reconciliation_flag = 'Y'
509 AND jeh2.je_batch_id = new_jeb_id
510 AND jeh2.name IN (jeh1.name,
511 substrb(jeh1.name,
512 1,(100-(lengthb(to_char(jeh1.je_header_id))+1)))
513 || ' ' || to_char(jeh1.je_header_id))
514 AND jel.je_header_id = jeh1.je_header_id
515 AND nvl(jel.tax_line_flag,'N') = 'N'
516 AND cc.code_combination_id = jel.code_combination_id
517 AND cc.jgzz_recon_flag = 'Y'
518 AND rec.je_header_id(+) = jel.je_header_id
519 AND rec.je_line_num(+) = jel.je_line_num;
520
521 temp := SQL%ROWCOUNT;
522 GL_MESSAGE.Write_Log(msg_name =>'SHRD0117',
523 token_num => 2 ,
524 t1 =>'NUM',
525 v1 =>to_char(temp),
526 t2 =>'TABLE',
527 v2 =>'GL_JE_LINES_RECON');
528
529 -- Fix the running totals for the journals
530 UPDATE gl_je_headers jeh
531 SET (running_total_dr, running_total_cr,
532 running_total_accounted_dr, running_total_accounted_cr)
533 = (SELECT sum(nvl(entered_dr,0)), sum(nvl(entered_cr,0)),
534 sum(nvl(accounted_dr,0)), sum(nvl(accounted_cr,0))
535 FROM gl_je_lines jel
536 WHERE jel.je_header_id = jeh.je_header_id)
537 WHERE jeh.je_batch_id = new_jeb_id;
538
539 -- Fix the batch running totals
540 UPDATE gl_je_batches jeb
541 SET (running_total_dr, running_total_cr,
542 running_total_accounted_dr, running_total_accounted_cr)
543 = (SELECT sum(running_total_dr),
544 sum(running_total_cr),
545 sum(running_total_accounted_dr),
546 sum(running_total_accounted_cr)
547 FROM gl_je_headers jeh
548 WHERE jeh.je_batch_id = jeb.je_batch_id)
549 WHERE jeb.je_batch_id = new_jeb_id;
550
551 -- If sequential numbering is on and this is an
552 -- actual batch, than try to get sequential numbering
553 -- information
554 IF ( (seq_num <> 'N') AND (act_flag = 'A')) THEN
555 DECLARE
556 je_category gl_je_headers.je_category%TYPE; /*Bug 6665535*/
557 lgr_id gl_je_headers.ledger_id%TYPE; /*Bug 6665535*/
558 effdate DATE;
559 seq_id NUMBER;
560 seq_val NUMBER;
561 row_id ROWID;
562 seq_result NUMBER;
563 je_name gl_je_headers.name%TYPE; /*Bug 6665535*/
564 CURSOR new_journals IS
565 SELECT rowid, ledger_id, je_category,
566 substrb(name, 25)
567 FROM gl_je_headers
568 WHERE je_batch_id = new_jeb_id;
569 BEGIN
570
571 OPEN new_journals;
572 LOOP
573 seq_val := NULL;
574 seq_id := NULL;
575 FETCH new_journals INTO row_id, lgr_id, je_category, je_name;
576 EXIT WHEN new_journals%NOTFOUND;
577
578 IF (dmode) THEN
579 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
580 token_num => 3 ,
581 t1 =>'ROUTINE',
582 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
583 t2 =>'VARIABLE',
584 v2 =>'JE_CATEGORY',
585 t3 =>'VALUE',
586 v3 => je_category);
587 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
588 token_num => 3 ,
589 t1 =>'ROUTINE',
590 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
591 t2 =>'VARIABLE',
592 v2 =>'LGR_ID',
593 t3 =>'VALUE',
594 v3 => to_char(lgr_id));
595 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
596 token_num => 3 ,
597 t1 =>'ROUTINE',
598 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
599 t2 =>'VARIABLE',
600 v2 =>'EFFDATE',
601 t3 =>'VALUE',
602 v3 => to_char(New_eff_date,
603 'DD-MON-RR'));
604 END IF;
605
606 seq_result := FND_SEQNUM.get_seq_val(
607 app_id => 101,
608 cat_code => je_category,
609 sob_id => lgr_id,
610 met_code => 'A',
611 trx_date => New_eff_date,
612 seq_val => seq_val,
616 IF (dmode) THEN
613 docseq_id => seq_id,
614 suppress_warn => 'Y');
615
617 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
618 token_num => 3 ,
619 t1 =>'ROUTINE',
620 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
621 t2 =>'VARIABLE',
622 v2 =>'SEQ_RESULT',
623 t3 =>'VALUE',
624 v3 => to_char(seq_result));
625 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
626 token_num => 3 ,
627 t1 =>'ROUTINE',
628 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
629 t2 =>'VARIABLE',
630 v2 =>'SEQ_VAL',
631 t3 =>'VALUE',
632 v3 => to_char(seq_val));
633 GL_MESSAGE.Write_Log(msg_name =>'SHRD0181',
634 token_num => 3 ,
635 t1 =>'ROUTINE',
636 v1 =>'GL_JOURNALS_AUTOCOPY.do_autocopy',
637 t2 =>'VARIABLE',
638 v2 =>'SEQ_ID',
639 t3 =>'VALUE',
640 v3 => to_char(seq_id));
641 END IF;
642
643 IF ((seq_result = 0) AND (seq_val IS NOT NULL)) THEN
644 UPDATE gl_je_headers
645 SET doc_sequence_id = seq_id,
646 doc_sequence_value = seq_val
647 WHERE rowid = row_id;
648 ELSIF (seq_num = 'A') THEN
649 GL_MESSAGE.Write_Log(msg_name =>'JECP0001',
650 token_num => 2 ,
651 t1 =>'NAME',
652 v1 => je_name);
653 Raise GLUJECPB_FATAL_ERR;
654 END IF;
655 END LOOP;
656 CLOSE new_journals;
657 END;
658 END IF;
659
660 -- Commit all work
661 FND_CONCURRENT.Af_Commit;
662
663 GL_MESSAGE.Func_Succ(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 Rollback;
668 GL_MESSAGE.Func_Fail(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
669 RAISE;
670 END do_autocopy;
671
672 -- ********************************************************************
673
674 PROCEDURE do_autocopy(errbuf OUT NOCOPY VARCHAR2,
675 retcode OUT NOCOPY VARCHAR2,
676 Jeb_id NUMBER,
677 New_Name VARCHAR2,
678 New_Period_Name VARCHAR2,
679 New_Eff_Date VARCHAR2,
680 X_Debug VARCHAR2 DEFAULT NULL) IS
681 BEGIN
682 GL_MESSAGE.Func_Ent(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
683 GL_JOURNALS_AUTOCOPY.do_autocopy(
684 Jeb_id => Jeb_id,
685 New_Name => New_Name,
686 New_Period_Name => New_Period_Name,
687 New_Eff_Date => to_date(New_Eff_Date, 'YYYY/MM/DD'),
688 X_Debug => X_Debug);
689 GL_MESSAGE.Func_Succ(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 GL_MESSAGE.Func_Fail(func_name => 'GL_JOURNALS_AUTOCOPY.do_autocopy');
694 errbuf := SQLERRM ;
695 retcode := '2';
696 ROLLBACK;
697 app_exception.raise_exception;
698 END do_autocopy;
699
700 END GL_JOURNALS_AUTOCOPY;