[Home] [Help]
PACKAGE BODY: APPS.GL_JE_HEADERS_PKG
Source
1 PACKAGE BODY GL_JE_HEADERS_PKG as
2 /* $Header: glijhrvb.pls 120.23.12020000.2 2012/10/11 08:49:09 degoel ship $ */
3
4 PROCEDURE check_unique(batch_id NUMBER, header_name VARCHAR2,
5 row_id VARCHAR2) IS
6 CURSOR chk_duplicates is
7 SELECT 'Duplicate'
8 FROM GL_JE_HEADERS jeh
9 WHERE jeh.je_batch_id = batch_id
10 AND jeh.name = header_name
11 AND ( row_id is null
12 OR jeh.rowid <> row_id);
13 dummy VARCHAR2(100);
14 BEGIN
15 OPEN chk_duplicates;
16 FETCH chk_duplicates INTO dummy;
17
18 IF chk_duplicates%FOUND THEN
19 CLOSE chk_duplicates;
20 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_JE_HEADER_NAME');
21 app_exception.raise_exception;
22 END IF;
23
24 CLOSE chk_duplicates;
25
26 EXCEPTION
27 WHEN app_exceptions.application_exception THEN
28 RAISE;
29 WHEN OTHERS THEN
30 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
31 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.check_unique');
32 RAISE;
33 END check_unique;
34
35 FUNCTION get_unique_id RETURN NUMBER IS
36 CURSOR get_new_id IS
37 SELECT gl_je_headers_s.NEXTVAL
38 FROM dual;
39 new_id number;
40 BEGIN
41 OPEN get_new_id;
42 FETCH get_new_id INTO new_id;
43
44 IF get_new_id%FOUND THEN
45 CLOSE get_new_id;
46 return(new_id);
47 ELSE
48 CLOSE get_new_id;
49 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
50 fnd_message.set_token('SEQUENCE', 'GL_JE_HEADERS_S');
51 app_exception.raise_exception;
52 END IF;
53
54 EXCEPTION
55 WHEN app_exceptions.application_exception THEN
56 RAISE;
57 WHEN OTHERS THEN
58 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
59 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.get_unique_id');
60 RAISE;
61 END get_unique_id;
62
63 PROCEDURE delete_headers(batch_id NUMBER) IS
64 dummy NUMBER;
65 BEGIN
66 -- Delete all of the lines in that batch
67 DELETE gl_je_lines
68 WHERE je_header_id IN (SELECT je_header_id
69 FROM gl_je_headers
70 WHERE je_batch_id = batch_id);
71
72 -- Delete all of the segment value assignments in that
73 -- batch
74 dummy := gl_je_segment_values_pkg.delete_batch_segment_values(batch_id);
75
76 -- Delete all of the reconciliation lines
77 DELETE gl_je_lines_recon
78 WHERE je_header_id IN (SELECT je_header_id
79 FROM gl_je_headers
80 WHERE je_batch_id = batch_id);
81
82 -- Mark all of the reversals as no longer reversals, since the
83 -- original journal has been deleted. This is necessary to fix
84 -- bug #1001521
85 UPDATE gl_je_headers
86 SET reversed_je_header_id = null,
87 accrual_rev_je_header_id = decode(accrual_rev_status,
88 'R', accrual_rev_je_header_id,
89 null)
90 WHERE je_header_id IN
91 (SELECT accrual_rev_je_header_id
92 FROM gl_je_headers
93 WHERE je_batch_id = batch_id
94 AND accrual_rev_status = 'R');
95
96 -- Bug fix 2749073 Mark the original journal as reversible
97 -- incase if the reversed journal associated is deleted.
98 UPDATE gl_je_headers
99 SET accrual_rev_status = null,
100 accrual_rev_je_header_id = null,
101 accrual_rev_flag = 'Y'
102 WHERE je_header_id IN
103 (SELECT reversed_je_header_id
104 FROM gl_je_headers
105 WHERE je_batch_id = batch_id
106 AND reversed_je_header_id IS NOT NULL );
107
108 --Delete the respective rows from GL_IMPORT_REFERENCES
109 --Bug Fix :2894045
110 GL_IMPORT_REFERENCES_PKG.delete_batch ( batch_id);
111
112
113 -- Delete all of the headers in that batch
114 DELETE gl_je_headers
115 WHERE je_batch_id = batch_id;
116
117
118 EXCEPTION
119 WHEN app_exceptions.application_exception THEN
120 RAISE;
121 WHEN NO_DATA_FOUND THEN
122 null;
123 WHEN OTHERS THEN
124 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
125 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.delete_headers');
126 RAISE;
127 END delete_headers;
128
129 PROCEDURE change_effective_date(batch_id NUMBER,
130 new_effective_date DATE) IS
131 BEGIN
132 -- Update all of the lines in the header
133 UPDATE gl_je_lines
134 SET effective_date = new_effective_date
135 WHERE je_header_id IN (SELECT je_header_id
136 FROM gl_je_headers
137 WHERE je_batch_id = batch_id);
138
139 -- Update all of the headers in that batch
140 UPDATE gl_je_headers jeh
141 SET default_effective_date = new_effective_date,
142 currency_conversion_date =
143 (select decode(jeh.currency_code,
144 'STAT', new_effective_date,
145 lgr.currency_code, new_effective_date,
146 jeh.currency_conversion_date)
147 from gl_ledgers lgr
148 where lgr.ledger_id = jeh.ledger_id),
149 accrual_rev_effective_date
150 = (select decode(jeh.accrual_rev_status,
151 null, ps.start_date,
152 jeh.accrual_rev_effective_date)
153 from gl_period_statuses ps
154 where ps.application_id = 101
155 and ps.ledger_id = jeh.ledger_id
156 and ps.period_name = jeh.accrual_rev_period_name)
157 WHERE je_batch_id = batch_id;
158
159 EXCEPTION
160 WHEN app_exceptions.application_exception THEN
161 RAISE;
162 WHEN OTHERS THEN
163 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
164 fnd_message.set_token('PROCEDURE',
165 'gl_je_headers_pkg.change_effective_date');
166 RAISE;
167 END change_effective_date;
168
169 PROCEDURE calculate_totals( batch_id NUMBER,
170 running_total_dr IN OUT NOCOPY NUMBER,
171 running_total_cr IN OUT NOCOPY NUMBER,
172 running_total_accounted_dr IN OUT NOCOPY NUMBER,
173 running_total_accounted_cr IN OUT NOCOPY NUMBER
174 ) IS
175 CURSOR calc_totals is
176 SELECT sum(nvl(jeh.running_total_dr, 0)),
177 sum(nvl(jeh.running_total_cr, 0)),
178 sum(nvl(jeh.running_total_accounted_dr, 0)),
179 sum(nvl(jeh.running_total_accounted_cr, 0))
180 FROM GL_JE_HEADERS jeh
181 WHERE jeh.je_batch_id = batch_id
182 AND (jeh.display_alc_journal_flag is null or jeh.display_alc_journal_flag = 'Y');
183 BEGIN
184 OPEN calc_totals;
185 FETCH calc_totals INTO running_total_dr, running_total_cr,
186 running_total_accounted_dr,
187 running_total_accounted_cr;
188
189 IF calc_totals%NOTFOUND THEN
190 CLOSE calc_totals;
191 RAISE NO_DATA_FOUND;
192 END IF;
193
194 CLOSE calc_totals;
195
196 EXCEPTION
197 WHEN OTHERS THEN
198 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
199 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.calculate_totals');
200 RAISE;
201 END calculate_totals;
202
203
204 FUNCTION change_period(batch_id NUMBER,
205 period_name VARCHAR2,
206 effective_date DATE,
207 user_id NUMBER,
208 login_id NUMBER,
209 header_id NUMBER DEFAULT null,
210 currency_code VARCHAR2 DEFAULT null,
211 conversion_date DATE DEFAULT null,
212 conversion_type VARCHAR2 DEFAULT null,
213 conversion_rate NUMBER DEFAULT null
214 ) RETURN NUMBER IS
215 CURSOR convert_headers IS
216 SELECT jeh.je_header_id, jeh.currency_code, jeh.actual_flag,
217 jeh.currency_conversion_type, jeh.je_category,
218 jeh.ledger_id, lgr.currency_code, jeh.reversed_je_header_id
219 FROM gl_je_headers jeh, gl_ledgers lgr
220 WHERE jeh.je_batch_id = batch_id
221 AND lgr.ledger_id = jeh.ledger_id
222 ORDER BY jeh.name || to_char(jeh.ledger_id)
223 FOR UPDATE OF jeh.default_effective_date, jeh.period_name,
224 jeh.last_update_date, jeh.last_updated_by,
225 jeh.last_update_login, jeh.running_total_accounted_dr,
226 jeh.running_total_accounted_cr,
227 jeh.currency_conversion_rate;
228
229 CURSOR convert_header_range IS
230 SELECT jeh.je_header_id, jeh.currency_code, jeh.actual_flag,
231 jeh.currency_conversion_type, jeh.je_category,
232 jeh.ledger_id, lgr.currency_code, jeh.reversed_je_header_id
233 FROM gl_je_headers jeh, gl_ledgers lgr
234 WHERE jeh.je_batch_id = batch_id
235 AND lgr.ledger_id = jeh.ledger_id
236 AND jeh.name > (SELECT name || to_char(ledger_id)
237 FROM gl_je_headers
238 WHERE je_header_id = header_id)
239 ORDER BY jeh.name || to_char(jeh.ledger_id)
240 FOR UPDATE OF jeh.default_effective_date, jeh.period_name,
241 jeh.last_update_date, jeh.last_updated_by,
242 jeh.last_update_login, jeh.running_total_accounted_dr,
243 jeh.running_total_accounted_cr,
244 jeh.currency_conversion_rate;
245
246 -- Various information selected from the header
247 cheader_id GL_JE_HEADERS.JE_HEADER_ID%TYPE;
248 ccurrency_code GL_JE_HEADERS.CURRENCY_CODE%TYPE;
249 cje_category GL_JE_HEADERS.JE_CATEGORY%TYPE;
250 cje_actual_flag GL_JE_HEADERS.ACTUAL_FLAG%TYPE;
251 cconversion_date GL_JE_HEADERS.CURRENCY_CONVERSION_DATE%TYPE;
252 cconversion_type GL_JE_HEADERS.CURRENCY_CONVERSION_TYPE%TYPE;
253 cconversion_rate GL_JE_HEADERS.CURRENCY_CONVERSION_RATE%TYPE;
254 crev_jeh_id GL_JE_HEADERS.REVERSED_JE_HEADER_ID%TYPE;
255 cledger_id NUMBER;
256 cfunct_curr VARCHAR2(15);
257
258 -- Reversal information
259 reversal_option_code VARCHAR2(1);
260 reversal_change_sign_flag VARCHAR2(1);
261 reversal_period VARCHAR2(15);
262 reversal_date DATE;
263 period_code VARCHAR2(30);
264 date_code VARCHAR2(30);
265 autorev_flag VARCHAR2(1);
266 autopst_flag VARCHAR2(1);
267
268 -- Indicates whether or not the current record being processed
269 -- is a bad journal
270 bad_journal BOOLEAN := FALSE;
271
272 -- Keep track of whether or not to clear statistical amounts
273 clear_stat VARCHAR2(1) := 'N';
274
275 -- Denominator and Numerator rate to use for fixed rate relationships
276 denom_rate NUMBER;
277 numer_rate NUMBER;
278 tmp_rate NUMBER;
279
280 BEGIN
281
282 -- Check the parameters
283 IF (header_id IS NOT NULL) THEN
284 IF (currency_code IS NULL) THEN
285 fnd_message.set_name('FND', 'FORM_INVALID_ARGUMENT');
286 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.change_period');
287 fnd_message.set_token('ARGUMENT', 'currency_code');
288 fnd_message.set_token('VALUE', currency_code);
289 ELSIF (conversion_date IS NULL) THEN
290 fnd_message.set_name('FND', 'FORM_INVALID_ARGUMENT');
291 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.change_period');
292 fnd_message.set_token('ARGUMENT', 'conversion_date');
293 fnd_message.set_token('VALUE', conversion_date);
294 ELSIF (conversion_type IS NULL) THEN
295 fnd_message.set_name('FND', 'FORM_INVALID_ARGUMENT');
296 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.change_period');
297 fnd_message.set_token('ARGUMENT', 'conversion_type');
298 fnd_message.set_token('VALUE', conversion_type);
299 ELSIF (conversion_rate IS NULL) THEN
300 fnd_message.set_name('FND', 'FORM_INVALID_ARGUMENT');
301 fnd_message.set_token('PROCEDURE', 'gl_je_headers_pkg.change_period');
302 fnd_message.set_token('ARGUMENT', 'conversion_rate');
303 fnd_message.set_token('VALUE', conversion_rate);
304 END IF;
305 END IF;
306
307 -- Delete all the corrupted ALC journal lines and headers in this batch.
308 DELETE FROM GL_JE_LINES
309 WHERE je_header_id in
310 (SELECT je_header_id
311 FROM gl_je_headers
312 WHERE je_batch_id = batch_id
313 AND display_alc_journal_flag = 'N');
314
315 DELETE FROM GL_JE_HEADERS
316 WHERE je_batch_id = batch_id
317 AND display_alc_journal_flag = 'N';
318
319 -- If data for an erroneous header has been passed, then process
320 -- that header first, then all of the headers after that header.
321 IF (header_id IS NOT NULL) THEN
322 bad_journal := TRUE;
323 cheader_id := header_id;
324 ccurrency_code := currency_code;
325 cconversion_date := conversion_date;
326 cconversion_type := conversion_type;
327 cconversion_rate := conversion_rate;
328
329 SELECT jeh.je_category, jeh.actual_flag, jeh.ledger_id,
330 lgr.currency_code, jeh.reversed_je_header_id
331 INTO cje_category, cje_actual_flag, cledger_id,
332 cfunct_curr, crev_jeh_id
333 FROM gl_je_headers jeh, gl_ledgers lgr
334 WHERE jeh.je_header_id = header_id
335 AND lgr.ledger_id = jeh.ledger_id;
336
337 -- If the user has just changed the currency to stat, then
338 -- clear the statistical amounts
339 IF (ccurrency_code = 'STAT') THEN
340 clear_stat := 'Y';
341 ELSE
342 clear_stat := 'N';
343 END IF;
344
345 OPEN convert_header_range;
346
347 -- Otherwise, process all of the headers
348 ELSE
349 bad_journal := FALSE;
350 clear_stat := 'N';
351
352 -- Get the headers to be processed
353 OPEN convert_headers;
354 END IF;
355
356 LOOP
357 -- Setup the data, if necessary.
358 IF (NOT bad_journal) THEN
359
360 -- Do not clear statistical amounts
361 clear_stat := 'N';
362
363 -- Get the data to process
364 IF (header_id IS NULL) THEN
365 FETCH convert_headers INTO cheader_id, ccurrency_code,
366 cje_actual_flag,
367 cconversion_type, cje_category,
368 cledger_id, cfunct_curr,
369 crev_jeh_id;
370 EXIT WHEN convert_headers%NOTFOUND;
371 ELSE
372 FETCH convert_header_range INTO cheader_id, ccurrency_code,
373 cje_actual_flag,
374 cconversion_type, cje_category,
375 cledger_id, cfunct_curr,
376 crev_jeh_id;
377 EXIT WHEN convert_header_range%NOTFOUND;
378 END IF;
379
380 -- Default the conversion date to the effective date
381 cconversion_date := effective_date;
382
383 IF ( (ccurrency_code <> 'STAT')
384 AND (ccurrency_code <> cfunct_curr)
385 ) THEN
386 IF (gl_currency_api.is_fixed_rate(
387 ccurrency_code,
388 cfunct_curr,
389 cconversion_date) = 'Y') THEN
390 cconversion_type := 'EMU FIXED';
391 END IF;
392 END IF;
393 END IF;
394
395 -- Get the new reversal information
396 reversal_option_code := null;
397 reversal_change_sign_flag := null;
398 reversal_period := null;
399 reversal_date := null;
400 IF ((cje_actual_flag = 'A') AND (crev_jeh_id IS NULL)) THEN
401 BEGIN
402 gl_autoreverse_date_pkg.get_reversal_period_date(
403 X_Ledger_Id => cledger_id,
404 X_Je_Category => cje_category,
405 X_Je_Source => 'Manual',
406 X_Je_Period_Name => period_name,
407 X_Je_Date => effective_date,
408 X_Reversal_Method => reversal_change_sign_flag,
409 X_Reversal_Period => reversal_period,
410 X_Reversal_Date => reversal_date);
411 EXCEPTION
412 WHEN OTHERS THEN
413 null;
414 END;
415 END IF;
416
417 IF (reversal_change_sign_flag IS NULL) THEN
418 gl_autoreverse_date_pkg.get_default_reversal_method(
419 X_Ledger_Id => cledger_id,
420 X_Category_Name => cje_category,
421 X_Reversal_Method_Code => reversal_change_sign_flag);
422 END IF;
423
424 -- If the conversion type is User and this batch has not been
425 -- processed previously then there is no need to update the
426 -- conversion rate. Just go ahead and update the period,
427 -- effective date, and conversion date information.
428 -- (If this batch has been processed previously,
429 -- then it must originally have had a conversion type other than
430 -- user, so we can assume the conversion rate has changed.)
431 IF ( (cconversion_type = 'User')
432 AND (NOT bad_journal)) THEN
433
434 -- Update the period and effective date for the lines
435 gl_je_lines_pkg.update_lines(
436 cheader_id,
437 period_name,
438 effective_date,
439 -1,
440 -1,
441 null,
442 null,
443 'N',
444 clear_stat,
445 user_id,
446 login_id);
447
448 -- Update the period, effective date, and conversion date for the
449 -- header.
450 UPDATE gl_je_headers jeh
451 SET period_name = change_period.period_name,
452 default_effective_date = effective_date,
453 currency_conversion_date = cconversion_date,
454 accrual_rev_period_name = decode(accrual_rev_status,
455 NULL, reversal_period,
456 accrual_rev_period_name),
457 accrual_rev_flag = decode(accrual_rev_status,
458 NULL, decode(reversal_period,
459 NULL, 'N', 'Y'),
460 accrual_rev_flag),
461 accrual_rev_effective_date = decode(accrual_rev_status,
462 NULL, reversal_date,
463 accrual_rev_effective_date),
464 accrual_rev_change_sign_flag = decode(accrual_rev_status,
465 NULL, reversal_change_sign_flag,
466 accrual_rev_change_sign_flag),
467 last_update_date = sysdate,
468 last_updated_by = user_id,
469 last_update_login = login_id
470 WHERE jeh.je_header_id = cheader_id;
471
472 -- Otherwise, we need to update the conversion rate as well as the
473 -- period, effective date, and conversion date
474 ELSE
475
476 -- Get the conversion rate, if necessary.
477 IF (NOT bad_journal) THEN
478 BEGIN
479 cconversion_rate := 1;
480
481 cconversion_rate := gl_currency_api.get_rate(
482 ccurrency_code,
483 cfunct_curr,
484 cconversion_date,
485 cconversion_type);
486 EXCEPTION
487 WHEN gl_currency_api.no_rate THEN
488 -- Close the cursors
489 IF (header_id IS NULL) THEN
490 CLOSE convert_headers;
491 ELSE
492 CLOSE convert_header_range;
493 END IF;
494
495 RETURN(cheader_id);
496 END;
497 END IF;
498
499 -- Update the period, effective date, and conversion rate for the lines
500 IF (ccurrency_code = 'STAT') THEN
501 -- For functional or STAT currency, ignore and clear the
502 -- ignore rate flag
503 gl_je_lines_pkg.update_lines(
504 cheader_id,
505 period_name,
506 effective_date,
507 1,
508 cconversion_rate,
509 'STAT',
510 'STAT',
511 'Y',
512 clear_stat,
513 user_id,
514 login_id);
515 -- Update the period, effective date, and conversion rate for the lines
516 ELSIF (ccurrency_code = cfunct_curr) THEN
517 -- For functional or STAT currency, ignore and clear the
518 -- ignore rate flag
519 gl_je_lines_pkg.update_lines(
520 cheader_id,
521 period_name,
522 effective_date,
523 1,
524 cconversion_rate,
525 cfunct_curr,
526 cfunct_curr,
527 'Y',
528 clear_stat,
529 user_id,
530 login_id);
531 ELSIF (cconversion_type = 'EMU FIXED') THEN
532 -- If the rate is fixed,
533 -- then ignore and clear any ignore
534 -- rate flag.
535 gl_currency_api.get_triangulation_rate(
536 x_from_currency => ccurrency_code,
537 x_to_currency => cfunct_curr,
538 x_conversion_date => cconversion_date,
539 x_conversion_type => cconversion_type,
540 x_denominator => denom_rate,
541 x_numerator => numer_rate,
542 x_rate => tmp_rate);
543
544 gl_je_lines_pkg.update_lines(
545 cheader_id,
546 period_name,
547 effective_date,
548 denom_rate,
549 numer_rate,
550 ccurrency_code,
551 cfunct_curr,
552 'Y',
553 clear_stat,
554 user_id,
555 login_id);
556 ELSE
557 -- For other currencies, do not update the amounts of lines
558 -- with the conversion rate flag set.
559 gl_je_lines_pkg.update_lines(
560 cheader_id,
561 period_name,
562 effective_date,
563 1,
564 cconversion_rate,
565 ccurrency_code,
566 cfunct_curr,
567 'N',
568 clear_stat,
569 user_id,
570 login_id);
571 END IF;
572
573 -- Update the period, effective date, conversion date, and
574 -- accounted running totals for the header.
575 UPDATE gl_je_headers jeh
576 SET (running_total_dr, running_total_cr,
577 running_total_accounted_dr, running_total_accounted_cr,
578 period_name, default_effective_date,
579 currency_code, currency_conversion_date,
580 currency_conversion_type, currency_conversion_rate,
581 last_update_date, last_updated_by, last_update_login)
582 = (SELECT sum(nvl(jel.entered_dr, 0)),
583 sum(nvl(jel.entered_cr, 0)),
584 sum(nvl(jel.accounted_dr, 0)),
585 sum(nvl(jel.accounted_cr, 0)),
586 change_period.period_name, change_period.effective_date,
587 ccurrency_code, cconversion_date,
588 cconversion_type, cconversion_rate,
589 sysdate, user_id, login_id
590 FROM gl_je_lines jel
591 WHERE jel.je_header_id = jeh.je_header_id),
592 accrual_rev_period_name = decode(accrual_rev_status,
593 NULL, reversal_period,
594 accrual_rev_period_name),
595 accrual_rev_flag = decode(accrual_rev_status,
596 NULL, decode(reversal_period,
597 NULL, 'N', 'Y'),
598 accrual_rev_flag),
599 accrual_rev_effective_date = decode(accrual_rev_status,
600 NULL, reversal_date,
601 accrual_rev_effective_date),
602 accrual_rev_change_sign_flag = decode(accrual_rev_status,
603 NULL, reversal_change_sign_flag,
604 accrual_rev_change_sign_flag)
605 WHERE jeh.je_header_id = cheader_id;
606 END IF;
607
608 -- We have already processed the first bad journal by this point
609 bad_journal := FALSE;
610 END LOOP;
611
612 -- Close the cursors
613 IF (header_id IS NULL) THEN
614 CLOSE convert_headers;
615 ELSE
616 CLOSE convert_header_range;
617 END IF;
618
619 -- All the journals were completed, so return -1 to indicate
620 -- success
621 RETURN(-1);
622 END change_period;
623
624
625 FUNCTION max_effective_date(batch_id NUMBER) RETURN DATE IS
626 CURSOR get_date IS
627 SELECT max(default_effective_date)
628 FROM gl_je_headers jeh
629 WHERE jeh.je_batch_id = batch_id
630 AND (jeh.display_alc_journal_flag is null or jeh.display_alc_journal_flag = 'Y')
631 AND jeh.accrual_rev_status IS NULL;
632 max_effective_date DATE;
633 BEGIN
634
635 OPEN get_date;
636
637 FETCH get_date INTO max_effective_date;
638
639 IF (get_date%NOTFOUND) THEN
640 CLOSE get_date;
641 RAISE NO_DATA_FOUND;
642 ELSE
643 CLOSE get_date;
644 RETURN(max_effective_date);
645 END IF;
646 END max_effective_date;
647
648
649 FUNCTION needs_tax(batch_id NUMBER) RETURN BOOLEAN IS
650 CURSOR check_tax IS
651 SELECT max(decode(jeh.tax_status_code, 'R', 1, 0))
652 FROM gl_je_headers jeh
653 WHERE jeh.je_batch_id = batch_id;
654 dummy NUMBER;
655 BEGIN
656
657 OPEN check_tax;
658
659 FETCH check_tax INTO dummy;
660
661 IF (check_tax%NOTFOUND) THEN
662 dummy := 0;
663 END IF;
664
665 IF (nvl(dummy, 0) = 0) THEN
666 RETURN(FALSE);
667 ELSE
668 RETURN(TRUE);
669 END IF;
670 END needs_tax;
671
672
673 FUNCTION has_seqnum(batch_id NUMBER) RETURN BOOLEAN IS
674 CURSOR check_seqnum IS
675 SELECT 1
676 FROM dual
677 WHERE EXISTS (
678 SELECT 'has seqnum'
679 FROM gl_je_headers jeh
680 WHERE jeh.je_batch_id = batch_id
681 AND jeh.doc_sequence_value IS NOT NULL);
682 dummy NUMBER;
683 BEGIN
684
685 OPEN check_seqnum;
686
687 FETCH check_seqnum INTO dummy;
688
689 IF (check_seqnum%NOTFOUND) THEN
690 dummy := 0;
691 END IF;
692
693 CLOSE check_seqnum;
694
695 IF (nvl(dummy, 0) = 0) THEN
696 RETURN(FALSE);
697 ELSE
698 RETURN(TRUE);
699 END IF;
700 END has_seqnum;
701
702
703 PROCEDURE populate_fields(ledger_id NUMBER,
704 ledger_name IN OUT NOCOPY VARCHAR2,
705 je_source_name VARCHAR2,
706 user_je_source_name IN OUT NOCOPY VARCHAR2,
707 frozen_source_flag IN OUT NOCOPY VARCHAR2,
708 je_category_name VARCHAR2,
709 user_je_category_name IN OUT NOCOPY VARCHAR2,
710 period_name VARCHAR2,
711 start_date IN OUT NOCOPY DATE,
712 end_date IN OUT NOCOPY DATE,
713 period_year IN OUT NOCOPY NUMBER,
714 period_num IN OUT NOCOPY NUMBER,
715 currency_conversion_type VARCHAR2,
716 user_currency_conv_type IN OUT NOCOPY VARCHAR2,
717 budget_version_id NUMBER,
718 budget_name IN OUT NOCOPY VARCHAR2,
719 encumbrance_type_id NUMBER,
720 encumbrance_type IN OUT NOCOPY VARCHAR2,
721 accrual_rev_period_name VARCHAR2,
722 accrual_rev_start_date IN OUT NOCOPY DATE,
723 accrual_rev_end_date IN OUT NOCOPY DATE,
724 posting_acct_seq_version_id NUMBER,
725 posting_acct_seq_name IN OUT NOCOPY VARCHAR2,
726 close_acct_seq_version_id NUMBER,
727 close_acct_seq_name IN OUT NOCOPY VARCHAR2,
728 error_name IN OUT NOCOPY VARCHAR2) IS
729 closing_status VARCHAR2(1);
730 invalid_item VARCHAR2(30) := '';
731
732 tmp_closing_status VARCHAR2(1);
733 tmp_period_year NUMBER;
734 tmp_period_num NUMBER;
735 effective_date_rule_code VARCHAR2(1);
736 journal_approval_flag VARCHAR2(1);
737 tax_precision NUMBER;
738 tax_mau NUMBER;
739 BEGIN
740 error_name := '';
741
742 -- Get the ledger information
743 BEGIN
744 gl_ledgers_pkg.select_columns(
745 ledger_id,
746 ledger_name);
747 EXCEPTION
748 WHEN NO_DATA_FOUND THEN
749 invalid_item := 'LEDGER';
750 END;
751
752 -- Get the source information
753 BEGIN
754 gl_je_sources_pkg.select_columns(
755 je_source_name,
756 user_je_source_name,
757 effective_date_rule_code,
758 frozen_source_flag,
759 journal_approval_flag);
760 EXCEPTION
761 WHEN NO_DATA_FOUND THEN
762 invalid_item := 'SOURCE';
763 END;
764
765 -- Get the category information
766 BEGIN
767 gl_je_categories_pkg.select_columns(
768 je_category_name,
769 user_je_category_name);
770 EXCEPTION
771 WHEN NO_DATA_FOUND THEN
772 invalid_item := 'CATEGORY';
773 END;
774
775 -- Get the period information
776 BEGIN
777 gl_period_statuses_pkg.select_columns(
778 101,
779 ledger_id,
780 period_name,
781 closing_status,
782 start_date,
783 end_date,
784 period_num,
785 period_year);
786
787 IF (closing_status IS NULL) THEN
788 invalid_item := 'PERIOD';
789 END IF;
790 EXCEPTION
791 WHEN NO_DATA_FOUND THEN
792 invalid_item := 'PERIOD';
793 END;
794
795 -- Get the reversing period information
796 IF (accrual_rev_period_name IS NOT NULL) THEN
797 BEGIN
798 gl_period_statuses_pkg.select_columns(
799 101,
800 ledger_id,
801 accrual_rev_period_name,
802 tmp_closing_status,
803 accrual_rev_start_date,
804 accrual_rev_end_date,
805 tmp_period_num,
806 tmp_period_year);
807
808 IF (tmp_closing_status IS NULL) THEN
809 invalid_item := 'REVPERIOD';
810 END IF;
811 EXCEPTION
812 WHEN NO_DATA_FOUND THEN
813 invalid_item := 'REVPERIOD';
814 END;
815 END IF;
816 -- Get the conversion type information
817 BEGIN
818 gl_daily_conv_types_pkg.select_columns(
819 currency_conversion_type,
820 user_currency_conv_type);
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 invalid_item := 'CONVERSION_TYPE';
824 END;
825
826 -- Get the budget information
827 IF (budget_version_id IS NOT NULL) THEN
828 BEGIN
829 gl_budget_versions_pkg.select_columns(
830 budget_version_id,
831 budget_name);
832 EXCEPTION
833 WHEN NO_DATA_FOUND THEN
834 invalid_item := 'BUDGET';
835 END;
836
837 IF (budget_name IS NULL) THEN
838 invalid_item := 'BUDGET';
839 END IF;
840 END IF;
841
842 -- Get the encumbrance information
843 IF (encumbrance_type_id IS NOT NULL) THEN
844 BEGIN
845 gl_encumbrance_types_pkg.select_columns(
846 encumbrance_type_id,
847 encumbrance_type);
848 EXCEPTION
849 WHEN NO_DATA_FOUND THEN
850 invalid_item := 'ENCUMBRANCE';
851 END;
852
853 IF (encumbrance_type IS NULL) THEN
854 invalid_item := 'ENCUMBRANCE';
855 END IF;
856 END IF;
857
858 -- Get the sequence information
859 IF (posting_acct_seq_version_id IS NOT NULL) THEN
860 BEGIN
861 SELECT header_name || ':' || version_name
862 INTO posting_acct_seq_name
863 FROM fun_seq_versions
864 WHERE seq_version_id = posting_acct_seq_version_id;
865 EXCEPTION
866 WHEN NO_DATA_FOUND THEN NULL;
867 END;
868 END IF;
869
870 -- Get the sequence information
871 IF (close_acct_seq_version_id IS NOT NULL) THEN
872 BEGIN
873 SELECT header_name || ':' || version_name
874 INTO close_acct_seq_name
875 FROM fun_seq_versions
876 WHERE seq_version_id = close_acct_seq_version_id;
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN NULL;
879 END;
880 END IF;
881
882 IF (invalid_item IS NOT NULL) THEN
883 error_name := 'GL_JE_INVALID_' || invalid_item;
884 END IF;
885
886 END populate_fields;
887
888 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
889 X_Je_Header_Id IN OUT NOCOPY NUMBER,
890 X_Last_Update_Date DATE,
891 X_Last_Updated_By NUMBER,
892 X_Ledger_Id NUMBER,
893 X_Je_Category VARCHAR2,
894 X_Je_Source VARCHAR2,
895 X_Period_Name VARCHAR2,
896 X_Name VARCHAR2,
897 X_Currency_Code VARCHAR2,
898 X_Status VARCHAR2,
899 X_Date_Created DATE,
900 X_Accrual_Rev_Flag VARCHAR2,
901 X_Multi_Bal_Seg_Flag VARCHAR2,
902 X_Actual_Flag VARCHAR2,
903 X_Default_Effective_Date DATE,
904 X_Conversion_Flag VARCHAR2,
905 X_Creation_Date DATE,
906 X_Created_By NUMBER,
907 X_Last_Update_Login NUMBER,
908 X_Encumbrance_Type_Id NUMBER,
909 X_Budget_Version_Id NUMBER,
910 X_Balanced_Je_Flag VARCHAR2,
911 X_Balancing_Segment_Value VARCHAR2,
912 X_Je_Batch_Id IN OUT NOCOPY NUMBER,
913 X_From_Recurring_Header_Id NUMBER,
914 X_Unique_Date VARCHAR2,
915 X_Earliest_Postable_Date DATE,
916 X_Posted_Date DATE,
917 X_Accrual_Rev_Effective_Date DATE,
918 X_Accrual_Rev_Period_Name VARCHAR2,
919 X_Accrual_Rev_Status VARCHAR2,
920 X_Accrual_Rev_Je_Header_Id NUMBER,
921 X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
922 X_Description VARCHAR2,
923 X_Tax_Status_Code VARCHAR2,
924 X_Control_Total NUMBER,
925 X_Running_Total_Dr NUMBER,
926 X_Running_Total_Cr NUMBER,
927 X_Running_Total_Accounted_Dr NUMBER,
928 X_Running_Total_Accounted_Cr NUMBER,
929 X_Currency_Conversion_Rate NUMBER,
930 X_Currency_Conversion_Type VARCHAR2,
931 X_Currency_Conversion_Date DATE,
932 X_External_Reference VARCHAR2,
933 X_Originating_Bal_Seg_Value VARCHAR2,
934 X_Attribute1 VARCHAR2,
935 X_Attribute2 VARCHAR2,
936 X_Attribute3 VARCHAR2,
937 X_Attribute4 VARCHAR2,
938 X_Attribute5 VARCHAR2,
939 X_Attribute6 VARCHAR2,
940 X_Attribute7 VARCHAR2,
941 X_Attribute8 VARCHAR2,
942 X_Attribute9 VARCHAR2,
943 X_Attribute10 VARCHAR2,
944 X_Context VARCHAR2,
945 X_Global_Attribute1 VARCHAR2,
946 X_Global_Attribute2 VARCHAR2,
947 X_Global_Attribute3 VARCHAR2,
948 X_Global_Attribute4 VARCHAR2,
949 X_Global_Attribute5 VARCHAR2,
950 X_Global_Attribute6 VARCHAR2,
951 X_Global_Attribute7 VARCHAR2,
952 X_Global_Attribute8 VARCHAR2,
953 X_Global_Attribute9 VARCHAR2,
954 X_Global_Attribute10 VARCHAR2,
955 X_Global_Attribute_Category VARCHAR2,
956 X_Ussgl_Transaction_Code VARCHAR2,
957 X_Context2 VARCHAR2,
958 X_Doc_Sequence_Id NUMBER,
959 X_Doc_Sequence_Value NUMBER,
960 X_Header_Mode VARCHAR2,
961 X_Batch_Row_Id IN OUT NOCOPY VARCHAR2,
962 X_Batch_Name VARCHAR2,
963 X_Chart_of_Accounts_ID NUMBER,
964 X_Period_Set_Name VARCHAR2,
965 X_Accounted_Period_Type VARCHAR2,
966 X_Batch_Status VARCHAR2,
967 X_Status_Verified VARCHAR2,
968 X_Batch_Default_Effective_Date DATE,
969 X_Batch_Posted_Date DATE,
970 X_Batch_Date_Created DATE,
971 X_Budgetary_Control_Status VARCHAR2,
972 X_Approval_Status_Code VARCHAR2,
973 X_Batch_Control_Total IN OUT NOCOPY NUMBER,
974 X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
975 X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
976 X_Average_Journal_Flag VARCHAR2,
977 X_Org_Id NUMBER,
978 X_Posting_Run_Id NUMBER,
979 X_Request_Id NUMBER,
980 X_Packet_Id NUMBER,
981 X_Unreservation_Packet_Id NUMBER,
982 X_Jgzz_Recon_Context VARCHAR2,
983 X_Jgzz_Recon_Ref VARCHAR2,
984 X_Reference_Date DATE
985 ) IS
986 CURSOR C IS SELECT rowid FROM GL_JE_HEADERS
987
988 WHERE je_header_id = X_Je_Header_Id;
989 has_line VARCHAR2(1);
990 BEGIN
991
992 -- Make sure all journals have at least one line.
993 has_line := 'N';
994 IF (X_Je_Header_Id IS NOT NULL) THEN
995 BEGIN
996 SELECT 'Y'
997 INTO has_line
998 FROM gl_je_lines
999 WHERE je_header_id = X_Je_Header_Id
1000 AND rownum = 1;
1001 EXCEPTION
1002 WHEN NO_DATA_FOUND THEN
1003 has_line := 'N';
1004 END;
1005 END IF;
1006
1007 IF (has_line = 'N') THEN
1008 fnd_message.set_name('SQLGL', 'GL_JE_JOURNAL_W_NO_LINES');
1009 app_exception.raise_exception;
1010 END IF;
1011
1012 IF (X_Je_Batch_Id IS NULL) THEN
1013 X_Je_Batch_Id := gl_je_batches_pkg.get_unique_id;
1014 END IF;
1015
1016 INSERT INTO GL_JE_HEADERS(
1017 je_header_id,
1018 last_update_date,
1019 last_updated_by,
1020 ledger_id,
1021 je_category,
1022 je_source,
1023 period_name,
1024 name,
1025 currency_code,
1026 status,
1027 date_created,
1028 accrual_rev_flag,
1029 multi_bal_seg_flag,
1030 actual_flag,
1031 default_effective_date,
1032 conversion_flag,
1033 creation_date,
1034 created_by,
1035 last_update_login,
1036 encumbrance_type_id,
1037 budget_version_id,
1038 balanced_je_flag,
1039 balancing_segment_value,
1040 je_batch_id,
1041 from_recurring_header_id,
1042 unique_date,
1043 earliest_postable_date,
1044 posted_date,
1045 accrual_rev_effective_date,
1046 accrual_rev_period_name,
1047 accrual_rev_status,
1048 accrual_rev_je_header_id,
1049 accrual_rev_change_sign_flag,
1050 description,
1051 tax_status_code,
1052 control_total,
1053 running_total_dr,
1054 running_total_cr,
1055 running_total_accounted_dr,
1056 running_total_accounted_cr,
1057 currency_conversion_rate,
1058 currency_conversion_type,
1059 currency_conversion_date,
1060 external_reference,
1061 originating_bal_seg_value,
1062 attribute1,
1063 attribute2,
1064 attribute3,
1065 attribute4,
1066 attribute5,
1067 attribute6,
1068 attribute7,
1069 attribute8,
1070 attribute9,
1071 attribute10,
1072 context,
1073 global_attribute1,
1074 global_attribute2,
1075 global_attribute3,
1076 global_attribute4,
1077 global_attribute5,
1078 global_attribute6,
1079 global_attribute7,
1080 global_attribute8,
1081 global_attribute9,
1082 global_attribute10,
1083 global_attribute_category,
1084 ussgl_transaction_code,
1085 context2,
1086 doc_sequence_id,
1087 doc_sequence_value,
1088 jgzz_recon_context,
1089 jgzz_recon_ref,
1090 reference_date
1091 ) VALUES (
1092 X_Je_Header_Id,
1093 X_Last_Update_Date,
1094 X_Last_Updated_By,
1095 X_Ledger_Id,
1096 X_Je_Category,
1097 X_Je_Source,
1098 X_Period_Name,
1099 X_Name,
1100 X_Currency_Code,
1101 X_Status,
1102 X_Date_Created,
1103 X_Accrual_Rev_Flag,
1104 X_Multi_Bal_Seg_Flag,
1105 X_Actual_Flag,
1106 X_Default_Effective_Date,
1107 X_Conversion_Flag,
1108 X_Creation_Date,
1109 X_Created_By,
1110 X_Last_Update_Login,
1111 X_Encumbrance_Type_Id,
1112 X_Budget_Version_Id,
1113 X_Balanced_Je_Flag,
1114 X_Balancing_Segment_Value,
1115 X_Je_Batch_Id,
1116 X_From_Recurring_Header_Id,
1117 X_Unique_Date,
1118 X_Earliest_Postable_Date,
1119 X_Posted_Date,
1120 X_Accrual_Rev_Effective_Date,
1121 X_Accrual_Rev_Period_Name,
1122 X_Accrual_Rev_Status,
1123 X_Accrual_Rev_Je_Header_Id,
1124 X_Accrual_Rev_Change_Sign_Flag,
1125 X_Description,
1126 X_Tax_Status_Code,
1127 X_Control_Total,
1128 X_Running_Total_Dr,
1129 X_Running_Total_Cr,
1130 X_Running_Total_Accounted_Dr,
1131 X_Running_Total_Accounted_Cr,
1132 X_Currency_Conversion_Rate,
1133 X_Currency_Conversion_Type,
1134 X_Currency_Conversion_Date,
1135 X_External_Reference,
1136 X_Originating_Bal_Seg_Value,
1137 X_Attribute1,
1138 X_Attribute2,
1139 X_Attribute3,
1140 X_Attribute4,
1141 X_Attribute5,
1142 X_Attribute6,
1143 X_Attribute7,
1144 X_Attribute8,
1145 X_Attribute9,
1146 X_Attribute10,
1147 X_Context,
1148 X_Global_Attribute1,
1149 X_Global_Attribute2,
1150 X_Global_Attribute3,
1151 X_Global_Attribute4,
1152 X_Global_Attribute5,
1153 X_Global_Attribute6,
1154 X_Global_Attribute7,
1155 X_Global_Attribute8,
1156 X_Global_Attribute9,
1157 X_Global_Attribute10,
1158 X_Global_Attribute_Category,
1159 X_Ussgl_Transaction_Code,
1160 X_Context2,
1161 X_Doc_Sequence_Id,
1162 X_Doc_Sequence_Value,
1163 X_Jgzz_Recon_Context,
1164 X_Jgzz_Recon_Ref,
1165 X_Reference_Date
1166
1167 );
1168
1169 OPEN C;
1170 FETCH C INTO X_Rowid;
1171 if (C%NOTFOUND) then
1172 CLOSE C;
1173 RAISE NO_DATA_FOUND;
1174 end if;
1175 CLOSE C;
1176
1177 -- If we are in header mode, insert the batch
1178 IF (X_Header_Mode = 'Y') THEN
1179
1180 -- Return the correct values for the batch totals
1181 X_Batch_Control_Total := null;
1182 X_Batch_Running_Total_Dr := X_Running_Total_Dr;
1183 X_Batch_Running_Total_Cr := X_Running_Total_Cr;
1184
1185 GL_JE_BATCHES_PKG.Insert_Row(
1186 X_Rowid => X_Batch_Row_Id,
1187 X_Je_Batch_Id => X_Je_Batch_Id,
1188 X_Name => X_Batch_Name,
1189 X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
1190 X_Period_Set_Name => X_Period_Set_Name,
1191 X_Accounted_Period_Type => X_Accounted_Period_Type,
1192 X_Status => X_Batch_Status,
1193 X_Budgetary_Control_Status=>
1194 X_Budgetary_Control_Status,
1195 X_Approval_Status_Code => X_Approval_Status_Code,
1196 X_Status_Verified => X_Status_Verified,
1197 X_Actual_Flag => X_Actual_Flag,
1198 X_Default_Period_Name => X_Period_Name,
1199 X_Default_Effective_Date=>
1200 X_Batch_Default_Effective_Date,
1201 X_Posted_Date => X_Batch_Posted_Date,
1202 X_Date_Created =>
1203 X_Batch_Date_Created,
1204 X_Control_Total => X_Batch_Control_Total,
1205 X_Running_Total_Dr => X_Batch_Running_Total_Dr,
1206 X_Running_Total_Cr => X_Batch_Running_Total_Cr,
1207 X_Running_Total_Accounted_Dr =>
1208 X_Running_Total_Accounted_Dr,
1209 X_Running_Total_Accounted_Cr =>
1210 X_Running_Total_Accounted_Cr,
1211 X_Average_Journal_Flag => X_Average_Journal_Flag,
1212 X_Org_Id => X_Org_Id,
1213 X_Posting_Run_Id => X_Posting_Run_Id,
1214 X_Request_Id => X_Request_Id,
1215 X_Packet_Id => X_Packet_Id,
1216 X_Unreservation_Packet_Id=>
1217 X_Unreservation_Packet_Id,
1218 X_Creation_Date => X_Creation_Date,
1219 X_Created_By => X_Created_By,
1220 X_Last_Update_Date => X_Last_Update_Date,
1221 X_Last_Updated_By => X_Last_Updated_By,
1222 X_Last_Update_Login => X_Last_Update_Login);
1223 END IF;
1224 END Insert_Row;
1225
1226
1227 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1228
1229 X_Je_Header_Id NUMBER,
1230 X_Ledger_Id NUMBER,
1231 X_Je_Category VARCHAR2,
1232 X_Je_Source VARCHAR2,
1233 X_Period_Name VARCHAR2,
1234 X_Name VARCHAR2,
1235 X_Currency_Code VARCHAR2,
1236 X_Status VARCHAR2,
1237 X_Date_Created DATE,
1238 X_Accrual_Rev_Flag VARCHAR2,
1239 X_Multi_Bal_Seg_Flag VARCHAR2,
1240 X_Actual_Flag VARCHAR2,
1241 X_Default_Effective_Date DATE,
1242 X_Conversion_Flag VARCHAR2,
1243 X_Encumbrance_Type_Id NUMBER,
1244 X_Budget_Version_Id NUMBER,
1245 X_Balanced_Je_Flag VARCHAR2,
1246 X_Balancing_Segment_Value VARCHAR2,
1247 X_Je_Batch_Id NUMBER,
1248 X_From_Recurring_Header_Id NUMBER,
1249 X_Unique_Date VARCHAR2,
1250 X_Earliest_Postable_Date DATE,
1251 X_Posted_Date DATE,
1252 X_Accrual_Rev_Effective_Date DATE,
1253 X_Accrual_Rev_Period_Name VARCHAR2,
1254 X_Accrual_Rev_Status VARCHAR2,
1255 X_Accrual_Rev_Je_Header_Id NUMBER,
1256 X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
1257 X_Description VARCHAR2,
1258 X_Tax_Status_Code VARCHAR2,
1259 X_Control_Total NUMBER,
1260 X_Running_Total_Dr NUMBER,
1261 X_Running_Total_Cr NUMBER,
1262 X_Running_Total_Accounted_Dr NUMBER,
1263 X_Running_Total_Accounted_Cr NUMBER,
1264 X_Currency_Conversion_Rate NUMBER,
1265 X_Currency_Conversion_Type VARCHAR2,
1266 X_Currency_Conversion_Date DATE,
1267 X_External_Reference VARCHAR2,
1268 X_Attribute1 VARCHAR2,
1269 X_Attribute2 VARCHAR2,
1270 X_Attribute3 VARCHAR2,
1271 X_Attribute4 VARCHAR2,
1272 X_Attribute5 VARCHAR2,
1273 X_Attribute6 VARCHAR2,
1274 X_Attribute7 VARCHAR2,
1275 X_Attribute8 VARCHAR2,
1276 X_Attribute9 VARCHAR2,
1277 X_Attribute10 VARCHAR2,
1278 X_Context VARCHAR2,
1279 X_Ussgl_Transaction_Code VARCHAR2,
1280 X_Context2 VARCHAR2,
1281 X_Doc_Sequence_Id NUMBER,
1282 X_Doc_Sequence_Value NUMBER
1283 ) IS
1284 CURSOR C IS
1285 SELECT *
1286 FROM GL_JE_HEADERS
1287 WHERE rowid = X_Rowid
1288 FOR UPDATE of Je_Header_Id NOWAIT;
1289 Recinfo C%ROWTYPE;
1290 BEGIN
1291
1292 OPEN C;
1293 FETCH C INTO Recinfo;
1294 if (C%NOTFOUND) then
1295 CLOSE C;
1296 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1297 APP_EXCEPTION.RAISE_EXCEPTION;
1298 end if;
1299 CLOSE C;
1300 if (
1301 ( (Recinfo.je_header_id = X_Je_Header_Id)
1302 OR ( (Recinfo.je_header_id IS NULL)
1303 AND (X_Je_Header_Id IS NULL)))
1304 AND ( (Recinfo.ledger_id = X_Ledger_Id)
1305 OR ( (Recinfo.ledger_id IS NULL)
1306 AND (X_Ledger_Id IS NULL)))
1307 AND ( (Recinfo.je_category = X_Je_Category)
1308 OR ( (Recinfo.je_category IS NULL)
1309 AND (X_Je_Category IS NULL)))
1310 AND ( (Recinfo.je_source = X_Je_Source)
1311 OR ( (Recinfo.je_source IS NULL)
1312 AND (X_Je_Source IS NULL)))
1313 AND ( (Recinfo.period_name = X_Period_Name)
1314 OR ( (Recinfo.period_name IS NULL)
1315 AND (X_Period_Name IS NULL)))
1316 AND ( (Recinfo.name = X_Name)
1317 OR ( (Recinfo.name IS NULL)
1318 AND (X_Name IS NULL)))
1319 AND ( (Recinfo.currency_code = X_Currency_Code)
1320 OR ( (Recinfo.currency_code IS NULL)
1321 AND (X_Currency_Code IS NULL)))
1322 AND ( (Recinfo.status = X_Status)
1323 OR ( (Recinfo.status IS NULL)
1324 AND (X_Status IS NULL)))
1325 AND ( (trunc(Recinfo.date_created) = trunc(X_Date_Created))
1326 OR ( (Recinfo.date_created IS NULL)
1327 AND (X_Date_Created IS NULL)))
1328 AND ( (Recinfo.accrual_rev_flag = X_Accrual_Rev_Flag)
1329 OR ( (Recinfo.accrual_rev_flag IS NULL)
1330 AND (X_Accrual_Rev_Flag IS NULL)))
1331 AND ( (Recinfo.multi_bal_seg_flag = X_Multi_Bal_Seg_Flag)
1332 OR ( (Recinfo.multi_bal_seg_flag IS NULL)
1333 AND (X_Multi_Bal_Seg_Flag IS NULL)))
1334 AND ( (Recinfo.actual_flag = X_Actual_Flag)
1335 OR ( (Recinfo.actual_flag IS NULL)
1336 AND (X_Actual_Flag IS NULL)))
1337 AND ( (Recinfo.default_effective_date = X_Default_Effective_Date)
1338 OR ( (Recinfo.default_effective_date IS NULL)
1339 AND (X_Default_Effective_Date IS NULL)))
1340 AND ( (Recinfo.conversion_flag = X_Conversion_Flag)
1341 OR ( (Recinfo.conversion_flag IS NULL)
1342 AND (X_Conversion_Flag IS NULL)))
1343 AND ( (Recinfo.encumbrance_type_id = X_Encumbrance_Type_Id)
1344 OR ( (Recinfo.encumbrance_type_id IS NULL)
1345 AND (X_Encumbrance_Type_Id IS NULL)))
1346 AND ( (Recinfo.budget_version_id = X_Budget_Version_Id)
1347 OR ( (Recinfo.budget_version_id IS NULL)
1348 AND (X_Budget_Version_Id IS NULL)))
1349 AND ( (Recinfo.balanced_je_flag = X_Balanced_Je_Flag)
1350 OR ( (Recinfo.balanced_je_flag IS NULL)
1351 AND (X_Balanced_Je_Flag IS NULL)))
1352 AND ( (Recinfo.balancing_segment_value = X_Balancing_Segment_Value)
1353 OR ( (Recinfo.balancing_segment_value IS NULL)
1354 AND (X_Balancing_Segment_Value IS NULL)))
1355 AND ( (Recinfo.je_batch_id = X_Je_Batch_Id)
1356 OR ( (Recinfo.je_batch_id IS NULL)
1357 AND (X_Je_Batch_Id IS NULL)))
1358 AND ( (Recinfo.from_recurring_header_id = X_From_Recurring_Header_Id)
1359 OR ( (Recinfo.from_recurring_header_id IS NULL)
1360 AND (X_From_Recurring_Header_Id IS NULL)))
1361 AND ( (Recinfo.unique_date = X_Unique_Date)
1362 OR ( (Recinfo.unique_date IS NULL)
1363 AND (X_Unique_Date IS NULL)))
1364 AND ( (Recinfo.earliest_postable_date = X_Earliest_Postable_Date)
1365 OR ( (Recinfo.earliest_postable_date IS NULL)
1366 AND (X_Earliest_Postable_Date IS NULL)))
1367 AND ( (trunc(Recinfo.posted_date) = trunc(X_Posted_Date))
1368 OR ( (Recinfo.posted_date IS NULL)
1369 AND (X_Posted_Date IS NULL)))
1370 AND ( (Recinfo.accrual_rev_effective_date = X_Accrual_Rev_Effective_Date)
1371 OR ( (Recinfo.accrual_rev_effective_date IS NULL)
1372 AND (X_Accrual_Rev_Effective_Date IS NULL)))
1373 AND ( (Recinfo.accrual_rev_period_name = X_Accrual_Rev_Period_Name)
1374 OR ( (Recinfo.accrual_rev_period_name IS NULL)
1375 AND (X_Accrual_Rev_Period_Name IS NULL)))
1376 AND ( (Recinfo.accrual_rev_status = X_Accrual_Rev_Status)
1377 OR ( (Recinfo.accrual_rev_status IS NULL)
1378 AND (X_Accrual_Rev_Status IS NULL)))
1379 AND ( (Recinfo.accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id)
1380 OR ( (Recinfo.accrual_rev_je_header_id IS NULL)
1381 AND (X_Accrual_Rev_Je_Header_Id IS NULL)))
1382 AND ( (Recinfo.accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag)
1383 OR ( (Recinfo.accrual_rev_change_sign_flag IS NULL)
1384 AND (X_Accrual_Rev_Change_Sign_Flag IS NULL)))
1385 AND ( (Recinfo.description = X_Description)
1386 OR ( (Recinfo.description IS NULL)
1387 AND (X_Description IS NULL)))
1388 AND ( (Recinfo.tax_status_code = X_tax_status_code)
1389 OR ( (Recinfo.tax_status_code IS NULL)
1390 AND (X_Tax_Status_Code IS NULL)))
1391 AND ( (Recinfo.control_total = X_Control_Total)
1392 OR ( (Recinfo.control_total IS NULL)
1393 AND (X_Control_Total IS NULL)))
1394 AND ( (Recinfo.running_total_dr = X_Running_Total_Dr)
1395 OR ( (Recinfo.running_total_dr IS NULL)
1396 AND (X_Running_Total_Dr IS NULL)))
1397 AND ( (Recinfo.running_total_cr = X_Running_Total_Cr)
1398 OR ( (Recinfo.running_total_cr IS NULL)
1399 AND (X_Running_Total_Cr IS NULL)))
1400 AND ( (Recinfo.running_total_accounted_dr = X_Running_Total_Accounted_Dr)
1401 OR ( (Recinfo.running_total_accounted_dr IS NULL)
1402 AND (X_Running_Total_Accounted_Dr IS NULL)))
1403 AND ( (Recinfo.running_total_accounted_cr = X_Running_Total_Accounted_Cr)
1404 OR ( (Recinfo.running_total_accounted_cr IS NULL)
1405 AND (X_Running_Total_Accounted_Cr IS NULL)))
1406 AND ( (Recinfo.currency_conversion_rate = X_Currency_Conversion_Rate)
1407 OR ( (Recinfo.currency_conversion_rate IS NULL)
1408 AND (X_Currency_Conversion_Rate IS NULL)))
1409 AND ( (Recinfo.currency_conversion_type = X_Currency_Conversion_Type)
1410 OR ( (Recinfo.currency_conversion_type IS NULL)
1411 AND (X_Currency_Conversion_Type IS NULL)))
1412 AND ( (Recinfo.currency_conversion_date = X_Currency_Conversion_Date)
1413 OR ( (Recinfo.currency_conversion_date IS NULL)
1414 AND (X_Currency_Conversion_Date IS NULL)))
1415 AND ( (Recinfo.external_reference = X_External_Reference)
1416 OR ( (Recinfo.external_reference IS NULL)
1417 AND (X_External_Reference IS NULL)))
1418 AND ( (Recinfo.attribute1 = X_Attribute1)
1419 OR ( (rtrim(Recinfo.attribute1,' ') IS NULL)
1420 AND (X_Attribute1 IS NULL)))
1421 AND ( (Recinfo.attribute2 = X_Attribute2)
1422 OR ( (rtrim(Recinfo.attribute2,' ') IS NULL)
1423 AND (X_Attribute2 IS NULL)))
1424 AND ( (Recinfo.attribute3 = X_Attribute3)
1425 OR ( (rtrim(Recinfo.attribute3,' ') IS NULL)
1426 AND (X_Attribute3 IS NULL)))
1427 AND ( (Recinfo.attribute4 = X_Attribute4)
1428 OR ( (rtrim(Recinfo.attribute4,' ') IS NULL)
1429 AND (X_Attribute4 IS NULL)))
1430 AND ( (Recinfo.attribute5 = X_Attribute5)
1431 OR ( (rtrim(Recinfo.attribute5,' ') IS NULL)
1432 AND (X_Attribute5 IS NULL)))
1433 AND ( (Recinfo.attribute6 = X_Attribute6)
1434 OR ( (rtrim(Recinfo.attribute6,' ') IS NULL)
1435 AND (X_Attribute6 IS NULL)))
1436 AND ( (Recinfo.attribute7 = X_Attribute7)
1437 OR ( (rtrim(Recinfo.attribute7,' ') IS NULL)
1438 AND (X_Attribute7 IS NULL)))
1439 AND ( (Recinfo.attribute8 = X_Attribute8)
1440 OR ( (rtrim(Recinfo.attribute8,' ') IS NULL)
1441 AND (X_Attribute8 IS NULL)))
1442 AND ( (Recinfo.attribute9 = X_Attribute9)
1443 OR ( (rtrim(Recinfo.attribute9,' ') IS NULL)
1444 AND (X_Attribute9 IS NULL)))
1445 AND ( (Recinfo.attribute10 = X_Attribute10)
1446 OR ( (rtrim(Recinfo.attribute10,' ') IS NULL)
1447 AND (X_Attribute10 IS NULL)))
1448 AND ( (Recinfo.context = X_Context)
1449 OR ( (rtrim(Recinfo.context,' ') IS NULL)
1450 AND (X_Context IS NULL)))
1451 AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
1452 OR ( (Recinfo.ussgl_transaction_code IS NULL)
1453 AND (X_Ussgl_Transaction_Code IS NULL)))
1454 AND ( (Recinfo.context2 = X_Context2)
1455 OR ( (Recinfo.context2 IS NULL)
1456 AND (X_Context2 IS NULL)))
1457 AND ( (Recinfo.doc_sequence_id = X_Doc_Sequence_Id)
1458 OR ( (Recinfo.doc_sequence_id IS NULL)
1459 AND (X_Doc_Sequence_Id IS NULL)))
1460 AND ( (Recinfo.doc_sequence_value = X_Doc_Sequence_Value)
1461 OR ( (Recinfo.doc_sequence_value IS NULL)
1462 AND (X_Doc_Sequence_Value IS NULL)))
1463 ) then
1464 return;
1465 else
1466 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1467 APP_EXCEPTION.RAISE_EXCEPTION;
1468 end if;
1469 END Lock_Row;
1470
1471 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1472
1473 X_Je_Header_Id NUMBER,
1474 X_Ledger_Id NUMBER,
1475 X_Je_Category VARCHAR2,
1476 X_Je_Source VARCHAR2,
1477 X_Period_Name VARCHAR2,
1478 X_Name VARCHAR2,
1479 X_Currency_Code VARCHAR2,
1480 X_Status VARCHAR2,
1481 X_Date_Created DATE,
1482 X_Accrual_Rev_Flag VARCHAR2,
1483 X_Multi_Bal_Seg_Flag VARCHAR2,
1484 X_Actual_Flag VARCHAR2,
1485 X_Default_Effective_Date DATE,
1486 X_Conversion_Flag VARCHAR2,
1487 X_Encumbrance_Type_Id NUMBER,
1488 X_Budget_Version_Id NUMBER,
1489 X_Balanced_Je_Flag VARCHAR2,
1490 X_Balancing_Segment_Value VARCHAR2,
1491 X_Je_Batch_Id NUMBER,
1492 X_From_Recurring_Header_Id NUMBER,
1493 X_Unique_Date VARCHAR2,
1494 X_Earliest_Postable_Date DATE,
1495 X_Posted_Date DATE,
1496 X_Accrual_Rev_Effective_Date DATE,
1497 X_Accrual_Rev_Period_Name VARCHAR2,
1498 X_Accrual_Rev_Status VARCHAR2,
1499 X_Accrual_Rev_Je_Header_Id NUMBER,
1500 X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
1501 X_Description VARCHAR2,
1502 X_Tax_Status_Code VARCHAR2,
1503 X_Control_Total NUMBER,
1504 X_Running_Total_Dr NUMBER,
1505 X_Running_Total_Cr NUMBER,
1506 X_Running_Total_Accounted_Dr NUMBER,
1507 X_Running_Total_Accounted_Cr NUMBER,
1508 X_Currency_Conversion_Rate NUMBER,
1509 X_Currency_Conversion_Type VARCHAR2,
1510 X_Currency_Conversion_Date DATE,
1511 X_External_Reference VARCHAR2,
1512 X_Originating_Bal_Seg_Value VARCHAR2,
1513 X_Attribute1 VARCHAR2,
1514 X_Attribute2 VARCHAR2,
1515 X_Attribute3 VARCHAR2,
1516 X_Attribute4 VARCHAR2,
1517 X_Attribute5 VARCHAR2,
1518 X_Attribute6 VARCHAR2,
1519 X_Attribute7 VARCHAR2,
1520 X_Attribute8 VARCHAR2,
1521 X_Attribute9 VARCHAR2,
1522 X_Attribute10 VARCHAR2,
1523 X_Context VARCHAR2,
1524 X_Global_Attribute1 VARCHAR2,
1525 X_Global_Attribute2 VARCHAR2,
1526 X_Global_Attribute3 VARCHAR2,
1527 X_Global_Attribute4 VARCHAR2,
1528 X_Global_Attribute5 VARCHAR2,
1529 X_Global_Attribute6 VARCHAR2,
1530 X_Global_Attribute7 VARCHAR2,
1531 X_Global_Attribute8 VARCHAR2,
1532 X_Global_Attribute9 VARCHAR2,
1533 X_Global_Attribute10 VARCHAR2,
1534 X_Global_Attribute_Category VARCHAR2,
1535 X_Ussgl_Transaction_Code VARCHAR2,
1536 X_Context2 VARCHAR2,
1537 X_Doc_Sequence_Id NUMBER,
1538 X_Doc_Sequence_Value NUMBER,
1539 X_Header_Mode VARCHAR2,
1540 X_Batch_Row_Id VARCHAR2,
1541 X_Batch_Name VARCHAR2,
1542 X_Chart_of_Accounts_ID NUMBER,
1543 X_Period_Set_Name VARCHAR2,
1544 X_Accounted_Period_Type VARCHAR2,
1545 X_Batch_Status VARCHAR2,
1546 X_Status_Verified VARCHAR2,
1547 X_Batch_Default_Effective_Date DATE,
1548 X_Batch_Posted_Date DATE,
1549 X_Batch_Date_Created DATE,
1550 X_Budgetary_Control_Status VARCHAR2,
1551 X_Approval_Status_Code VARCHAR2,
1552 X_Batch_Control_Total NUMBER,
1553 X_Batch_Running_Total_Dr NUMBER,
1554 X_Batch_Running_Total_Cr NUMBER,
1555 X_Average_Journal_Flag VARCHAR2,
1556 X_Posting_Run_Id NUMBER,
1557 X_Request_Id NUMBER,
1558 X_Packet_Id NUMBER,
1559 X_Unreservation_Packet_Id NUMBER,
1560 X_Verify_Request_Completed VARCHAR2,
1561 X_Jgzz_Recon_Context VARCHAR2,
1562 X_Jgzz_Recon_Ref VARCHAR2,
1563 X_Reference_Date DATE
1564 ) IS
1565 CURSOR C IS
1566 SELECT *
1567 FROM GL_JE_HEADERS
1568 WHERE rowid = X_Rowid
1569 FOR UPDATE of Je_Header_Id NOWAIT;
1570 Recinfo C%ROWTYPE;
1571 BEGIN
1572
1573 OPEN C;
1574 FETCH C INTO Recinfo;
1575 if (C%NOTFOUND) then
1576 CLOSE C;
1577 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1578 APP_EXCEPTION.RAISE_EXCEPTION;
1579 end if;
1580 CLOSE C;
1581 if (
1582 ( (Recinfo.je_header_id = X_Je_Header_Id)
1583 OR ( (Recinfo.je_header_id IS NULL)
1584 AND (X_Je_Header_Id IS NULL)))
1585 AND ( (Recinfo.ledger_id = X_Ledger_Id)
1586 OR ( (Recinfo.ledger_id IS NULL)
1587 AND (X_Ledger_Id IS NULL)))
1588 AND ( (Recinfo.je_category = X_Je_Category)
1589 OR ( (Recinfo.je_category IS NULL)
1590 AND (X_Je_Category IS NULL)))
1591 AND ( (Recinfo.je_source = X_Je_Source)
1592 OR ( (Recinfo.je_source IS NULL)
1593 AND (X_Je_Source IS NULL)))
1594 AND ( (Recinfo.period_name = X_Period_Name)
1595 OR ( (Recinfo.period_name IS NULL)
1596 AND (X_Period_Name IS NULL)))
1597 AND ( (Recinfo.name = X_Name)
1598 OR ( (Recinfo.name IS NULL)
1599 AND (X_Name IS NULL)))
1600 AND ( (Recinfo.currency_code = X_Currency_Code)
1601 OR ( (Recinfo.currency_code IS NULL)
1602 AND (X_Currency_Code IS NULL)))
1603 AND ( (Recinfo.status = X_Status)
1604 OR ( (Recinfo.status IS NULL)
1605 AND (X_Status IS NULL)))
1606 AND ( (trunc(Recinfo.date_created) = trunc(X_Date_Created))
1607 OR ( (Recinfo.date_created IS NULL)
1608 AND (X_Date_Created IS NULL)))
1609 AND ( (Recinfo.accrual_rev_flag = X_Accrual_Rev_Flag)
1610 OR ( (Recinfo.accrual_rev_flag IS NULL)
1611 AND (X_Accrual_Rev_Flag IS NULL)))
1612 AND ( (Recinfo.multi_bal_seg_flag = X_Multi_Bal_Seg_Flag)
1613 OR ( (Recinfo.multi_bal_seg_flag IS NULL)
1614 AND (X_Multi_Bal_Seg_Flag IS NULL)))
1615 AND ( (Recinfo.actual_flag = X_Actual_Flag)
1616 OR ( (Recinfo.actual_flag IS NULL)
1617 AND (X_Actual_Flag IS NULL)))
1618 AND ( (Recinfo.default_effective_date = X_Default_Effective_Date)
1619 OR ( (Recinfo.default_effective_date IS NULL)
1620 AND (X_Default_Effective_Date IS NULL)))
1621 AND ( (Recinfo.conversion_flag = X_Conversion_Flag)
1622 OR ( (Recinfo.conversion_flag IS NULL)
1623 AND (X_Conversion_Flag IS NULL)))
1624 AND ( (Recinfo.encumbrance_type_id = X_Encumbrance_Type_Id)
1625 OR ( (Recinfo.encumbrance_type_id IS NULL)
1626 AND (X_Encumbrance_Type_Id IS NULL)))
1627 AND ( (Recinfo.budget_version_id = X_Budget_Version_Id)
1628 OR ( (Recinfo.budget_version_id IS NULL)
1629 AND (X_Budget_Version_Id IS NULL)))
1630 AND ( (Recinfo.balanced_je_flag = X_Balanced_Je_Flag)
1631 OR ( (Recinfo.balanced_je_flag IS NULL)
1632 AND (X_Balanced_Je_Flag IS NULL)))
1633 AND ( (Recinfo.balancing_segment_value = X_Balancing_Segment_Value)
1634 OR ( (Recinfo.balancing_segment_value IS NULL)
1635 AND (X_Balancing_Segment_Value IS NULL)))
1636 AND ( (Recinfo.je_batch_id = X_Je_Batch_Id)
1637 OR ( (Recinfo.je_batch_id IS NULL)
1638 AND (X_Je_Batch_Id IS NULL)))
1639 AND ( (Recinfo.from_recurring_header_id = X_From_Recurring_Header_Id)
1640 OR ( (Recinfo.from_recurring_header_id IS NULL)
1641 AND (X_From_Recurring_Header_Id IS NULL)))
1642 AND ( (Recinfo.unique_date = X_Unique_Date)
1643 OR ( (Recinfo.unique_date IS NULL)
1644 AND (X_Unique_Date IS NULL)))
1645 AND ( (Recinfo.earliest_postable_date = X_Earliest_Postable_Date)
1646 OR ( (Recinfo.earliest_postable_date IS NULL)
1647 AND (X_Earliest_Postable_Date IS NULL)))
1648 AND ( (trunc(Recinfo.posted_date) = trunc(X_Posted_Date))
1649 OR ( (Recinfo.posted_date IS NULL)
1650 AND (X_Posted_Date IS NULL)))
1651 AND ( (Recinfo.accrual_rev_effective_date = X_Accrual_Rev_Effective_Date)
1652 OR ( (Recinfo.accrual_rev_effective_date IS NULL)
1653 AND (X_Accrual_Rev_Effective_Date IS NULL)))
1654 AND ( (Recinfo.accrual_rev_period_name = X_Accrual_Rev_Period_Name)
1655 OR ( (Recinfo.accrual_rev_period_name IS NULL)
1656 AND (X_Accrual_Rev_Period_Name IS NULL)))
1657 AND ( (Recinfo.accrual_rev_status = X_Accrual_Rev_Status)
1658 OR ( (Recinfo.accrual_rev_status IS NULL)
1659 AND (X_Accrual_Rev_Status IS NULL)))
1660 AND ( (Recinfo.accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id)
1661 OR ( (Recinfo.accrual_rev_je_header_id IS NULL)
1662 AND (X_Accrual_Rev_Je_Header_Id IS NULL)))
1663 AND ( (Recinfo.accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag)
1664 OR ( (Recinfo.accrual_rev_change_sign_flag IS NULL)
1665 AND (X_Accrual_Rev_Change_Sign_Flag IS NULL)))
1666 AND ( (Recinfo.description = X_Description)
1667 OR ( (Recinfo.description IS NULL)
1668 AND (X_Description IS NULL)))
1669 AND ( (Recinfo.tax_status_code = X_tax_status_code)
1670 OR ( (Recinfo.tax_status_code IS NULL)
1671 AND (X_Tax_Status_Code IS NULL)))
1672 AND ( (Recinfo.control_total = X_Control_Total)
1673 OR ( (Recinfo.control_total IS NULL)
1674 AND (X_Control_Total IS NULL)))
1675 AND ( (Recinfo.running_total_dr = X_Running_Total_Dr)
1676 OR ( (Recinfo.running_total_dr IS NULL)
1677 AND (X_Running_Total_Dr IS NULL)))
1678 AND ( (Recinfo.running_total_cr = X_Running_Total_Cr)
1679 OR ( (Recinfo.running_total_cr IS NULL)
1680 AND (X_Running_Total_Cr IS NULL)))
1681 AND ( (Recinfo.running_total_accounted_dr = X_Running_Total_Accounted_Dr)
1682 OR ( (Recinfo.running_total_accounted_dr IS NULL)
1683 AND (X_Running_Total_Accounted_Dr IS NULL)))
1684 AND ( (Recinfo.running_total_accounted_cr = X_Running_Total_Accounted_Cr)
1685 OR ( (Recinfo.running_total_accounted_cr IS NULL)
1686 AND (X_Running_Total_Accounted_Cr IS NULL)))
1687 AND ( (Recinfo.currency_conversion_rate = X_Currency_Conversion_Rate)
1688 OR ( (Recinfo.currency_conversion_rate IS NULL)
1689 AND (X_Currency_Conversion_Rate IS NULL)))
1690 AND ( (Recinfo.currency_conversion_type = X_Currency_Conversion_Type)
1691 OR ( (Recinfo.currency_conversion_type IS NULL)
1692 AND (X_Currency_Conversion_Type IS NULL)))
1693 AND ( (Recinfo.currency_conversion_date = X_Currency_Conversion_Date)
1694 OR ( (Recinfo.currency_conversion_date IS NULL)
1695 AND (X_Currency_Conversion_Date IS NULL)))
1696 AND ( (Recinfo.external_reference = X_External_Reference)
1697 OR ( (Recinfo.external_reference IS NULL)
1698 AND (X_External_Reference IS NULL)))
1699 AND ( (Recinfo.originating_bal_seg_value = X_Originating_Bal_Seg_Value)
1700 OR ( (Recinfo.originating_bal_seg_value IS NULL)
1701 AND (X_Originating_Bal_Seg_Value IS NULL)))
1702 AND ( (Recinfo.attribute1 = X_Attribute1)
1703 OR ( (rtrim(Recinfo.attribute1,' ') IS NULL)
1704 AND (X_Attribute1 IS NULL)))
1705 AND ( (Recinfo.attribute2 = X_Attribute2)
1706 OR ( (rtrim(Recinfo.attribute2,' ') IS NULL)
1707 AND (X_Attribute2 IS NULL)))
1708 AND ( (Recinfo.attribute3 = X_Attribute3)
1709 OR ( (rtrim(Recinfo.attribute3,' ') IS NULL)
1710 AND (X_Attribute3 IS NULL)))
1711 AND ( (Recinfo.attribute4 = X_Attribute4)
1712 OR ( (rtrim(Recinfo.attribute4,' ') IS NULL)
1713 AND (X_Attribute4 IS NULL)))
1714 AND ( (Recinfo.attribute5 = X_Attribute5)
1715 OR ( (rtrim(Recinfo.attribute5,' ') IS NULL)
1716 AND (X_Attribute5 IS NULL)))
1717 AND ( (Recinfo.attribute6 = X_Attribute6)
1718 OR ( (rtrim(Recinfo.attribute6,' ') IS NULL)
1719 AND (X_Attribute6 IS NULL)))
1720 AND ( (Recinfo.attribute7 = X_Attribute7)
1721 OR ( (rtrim(Recinfo.attribute7,' ') IS NULL)
1722 AND (X_Attribute7 IS NULL)))
1723 AND ( (Recinfo.attribute8 = X_Attribute8)
1724 OR ( (rtrim(Recinfo.attribute8,' ') IS NULL)
1725 AND (X_Attribute8 IS NULL)))
1726 AND ( (Recinfo.attribute9 = X_Attribute9)
1727 OR ( (rtrim(Recinfo.attribute9,' ') IS NULL)
1728 AND (X_Attribute9 IS NULL)))
1729 AND ( (Recinfo.attribute10 = X_Attribute10)
1730 OR ( (rtrim(Recinfo.attribute10,' ') IS NULL)
1731 AND (X_Attribute10 IS NULL)))
1732 AND ( (Recinfo.context = X_Context)
1733 OR ( (rtrim(Recinfo.context,' ') IS NULL)
1734 AND (X_Context IS NULL)))
1735 AND ( (Recinfo.global_attribute1 = X_Global_Attribute1)
1736 OR ( (rtrim(Recinfo.global_attribute1,' ') IS NULL)
1737 AND (X_Global_Attribute1 IS NULL)))
1738 AND ( (Recinfo.global_attribute2 = X_Global_Attribute2)
1739 OR ( (rtrim(Recinfo.global_attribute2,' ') IS NULL)
1740 AND (X_Global_Attribute2 IS NULL)))
1741 AND ( (Recinfo.global_attribute3 = X_Global_Attribute3)
1742 OR ( (rtrim(Recinfo.global_attribute3,' ') IS NULL)
1743 AND (X_Global_Attribute3 IS NULL)))
1744 AND ( (Recinfo.global_attribute4 = X_Global_Attribute4)
1745 OR ( (rtrim(Recinfo.global_attribute4,' ') IS NULL)
1746 AND (X_Global_Attribute4 IS NULL)))
1747 AND ( (Recinfo.global_attribute5 = X_Global_Attribute5)
1748 OR ( (rtrim(Recinfo.global_attribute5,' ') IS NULL)
1749 AND (X_Global_Attribute5 IS NULL)))
1750 AND ( (Recinfo.global_attribute6 = X_Global_Attribute6)
1751 OR ( (rtrim(Recinfo.global_attribute6,' ') IS NULL)
1752 AND (X_Global_Attribute6 IS NULL)))
1753 AND ( (Recinfo.global_attribute7 = X_Global_Attribute7)
1754 OR ( (rtrim(Recinfo.global_attribute7,' ') IS NULL)
1755 AND (X_Global_Attribute7 IS NULL)))
1756 AND ( (Recinfo.global_attribute8 = X_Global_Attribute8)
1757 OR ( (rtrim(Recinfo.global_attribute8,' ') IS NULL)
1758 AND (X_Global_Attribute8 IS NULL)))
1759 AND ( (Recinfo.global_attribute9 = X_Global_Attribute9)
1760 OR ( (rtrim(Recinfo.global_attribute9,' ') IS NULL)
1761 AND (X_Global_Attribute9 IS NULL)))
1762 AND ( (Recinfo.global_attribute10 = X_Global_Attribute10)
1763 OR ( (rtrim(Recinfo.global_attribute10,' ') IS NULL)
1764 AND (X_Global_Attribute10 IS NULL)))
1765 AND ( (Recinfo.global_attribute_category = X_Global_Attribute_Category)
1766 OR ( (rtrim(Recinfo.global_attribute_category,' ') IS NULL)
1767 AND (X_Global_Attribute_Category IS NULL)))
1768 AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
1769 OR ( (Recinfo.ussgl_transaction_code IS NULL)
1770 AND (X_Ussgl_Transaction_Code IS NULL)))
1771 AND ( (Recinfo.context2 = X_Context2)
1772 OR ( (Recinfo.context2 IS NULL)
1773 AND (X_Context2 IS NULL)))
1774 AND ( (Recinfo.doc_sequence_id = X_Doc_Sequence_Id)
1775 OR ( (Recinfo.doc_sequence_id IS NULL)
1776 AND (X_Doc_Sequence_Id IS NULL)))
1777 AND ( (Recinfo.doc_sequence_value = X_Doc_Sequence_Value)
1778 OR ( (Recinfo.doc_sequence_value IS NULL)
1779 AND (X_Doc_Sequence_Value IS NULL)))
1780 AND ( (Recinfo.jgzz_recon_context = X_Jgzz_Recon_Context)
1781 OR ( (Recinfo.jgzz_recon_context IS NULL)
1782 AND (X_Jgzz_Recon_Context IS NULL)))
1783 AND ( (Recinfo.jgzz_recon_ref = X_Jgzz_Recon_Ref)
1784 OR ( (Recinfo.jgzz_recon_ref IS NULL)
1785 AND (X_Jgzz_Recon_Ref IS NULL)))
1786 AND ( (Recinfo.reference_date = X_Reference_Date)
1787 OR ( (Recinfo.reference_date IS NULL)
1788 AND (X_Reference_Date IS NULL)))
1789 ) then
1790 null;
1791 else
1792 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1793 APP_EXCEPTION.RAISE_EXCEPTION;
1794 end if;
1795
1796 -- If we are in header mode, then Lock the batch record
1797 IF (X_Header_Mode = 'Y') THEN
1798 GL_JE_BATCHES_PKG.Lock_Row(
1799 X_Rowid => X_Batch_Row_Id,
1800 X_Je_Batch_Id => X_Je_Batch_Id,
1801 X_Name => X_Batch_Name,
1802 X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
1803 X_Period_Set_Name => X_Period_Set_Name,
1804 X_Accounted_Period_Type => X_Accounted_Period_Type,
1805 X_Status => X_Batch_Status,
1806 X_Budgetary_Control_Status=>
1807 X_Budgetary_Control_Status,
1808 X_Approval_Status_Code => X_Approval_Status_Code,
1809 X_Status_Verified => X_Status_Verified,
1810 X_Actual_Flag => X_Actual_Flag,
1811 X_Default_Period_Name => X_Period_Name,
1812 X_Default_Effective_Date=>
1813 X_Batch_Default_Effective_Date,
1814 X_Posted_Date =>
1815 X_Batch_Posted_Date,
1816 X_Date_Created =>
1817 X_Batch_Date_Created,
1818 X_Control_Total =>
1819 X_Batch_Control_Total,
1820 X_Running_Total_Dr =>
1821 X_Batch_Running_Total_Dr,
1822 X_Running_Total_Cr =>
1823 X_Batch_Running_Total_Cr,
1824 X_Average_Journal_Flag =>
1825 X_Average_Journal_Flag,
1826 X_Posting_Run_Id =>
1827 X_Posting_Run_Id,
1828 X_Request_Id => X_Request_Id,
1829 X_Packet_Id => X_Packet_Id,
1830 X_Unreservation_Packet_Id=>
1831 X_Unreservation_Packet_Id,
1832 X_Verify_Request_Completed => X_Verify_Request_Completed);
1833 END IF;
1834 END Lock_Row;
1835
1836 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1837 X_Je_Header_Id NUMBER,
1838 X_Last_Update_Date DATE,
1839 X_Last_Updated_By NUMBER,
1840 X_Ledger_Id NUMBER,
1841 X_Je_Category VARCHAR2,
1842 X_Je_Source VARCHAR2,
1843 X_Period_Name VARCHAR2,
1844 X_Name VARCHAR2,
1845 X_Currency_Code VARCHAR2,
1846 X_Status VARCHAR2,
1847 X_Date_Created DATE,
1848 X_Accrual_Rev_Flag VARCHAR2,
1849 X_Multi_Bal_Seg_Flag VARCHAR2,
1850 X_Actual_Flag VARCHAR2,
1851 X_Default_Effective_Date DATE,
1852 X_Conversion_Flag VARCHAR2,
1853 X_Last_Update_Login NUMBER,
1854 X_Encumbrance_Type_Id NUMBER,
1855 X_Budget_Version_Id NUMBER,
1856 X_Balanced_Je_Flag VARCHAR2,
1857 X_Balancing_Segment_Value VARCHAR2,
1858 X_Je_Batch_Id NUMBER,
1859 X_From_Recurring_Header_Id NUMBER,
1860 X_Unique_Date VARCHAR2,
1861 X_Earliest_Postable_Date DATE,
1862 X_Posted_Date DATE,
1863 X_Accrual_Rev_Effective_Date DATE,
1864 X_Accrual_Rev_Period_Name VARCHAR2,
1865 X_Accrual_Rev_Status VARCHAR2,
1866 X_Accrual_Rev_Je_Header_Id NUMBER,
1867 X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
1868 X_Description VARCHAR2,
1869 X_Tax_Status_Code VARCHAR2,
1870 X_Control_Total NUMBER,
1871 X_Running_Total_Dr NUMBER,
1872 X_Running_Total_Cr NUMBER,
1873 X_Running_Total_Accounted_Dr NUMBER,
1874 X_Running_Total_Accounted_Cr NUMBER,
1875 X_Currency_Conversion_Rate NUMBER,
1876 X_Currency_Conversion_Type VARCHAR2,
1877 X_Currency_Conversion_Date DATE,
1878 X_External_Reference VARCHAR2,
1879 X_Attribute1 VARCHAR2,
1880 X_Attribute2 VARCHAR2,
1881 X_Attribute3 VARCHAR2,
1882 X_Attribute4 VARCHAR2,
1883 X_Attribute5 VARCHAR2,
1884 X_Attribute6 VARCHAR2,
1885 X_Attribute7 VARCHAR2,
1886 X_Attribute8 VARCHAR2,
1887 X_Attribute9 VARCHAR2,
1888 X_Attribute10 VARCHAR2,
1889 X_Context VARCHAR2,
1890 X_Ussgl_Transaction_Code VARCHAR2,
1891 X_Context2 VARCHAR2,
1892 X_Doc_Sequence_Id NUMBER,
1893 X_Doc_Sequence_Value NUMBER
1894 ) IS
1895 has_line VARCHAR2(1);
1896 BEGIN
1897
1898 -- Make sure all journals have at least one line.
1899 has_line := 'N';
1900 IF (X_Je_Header_Id IS NOT NULL) THEN
1901 BEGIN
1902 SELECT 'Y'
1903 INTO has_line
1904 FROM gl_je_lines
1905 WHERE je_header_id = X_Je_Header_Id
1906 AND rownum = 1;
1907 EXCEPTION
1908 WHEN NO_DATA_FOUND THEN
1909 has_line := 'N';
1910 END;
1911 END IF;
1912
1913 IF (has_line = 'N') THEN
1914 fnd_message.set_name('SQLGL', 'GL_JE_JOURNAL_W_NO_LINES');
1915 app_exception.raise_exception;
1916 END IF;
1917
1918 UPDATE GL_JE_HEADERS
1919 SET
1920
1921 je_header_id = X_Je_Header_Id,
1922 last_update_date = X_Last_Update_Date,
1923 last_updated_by = X_Last_Updated_By,
1924 ledger_id = X_Ledger_Id,
1925 je_category = X_Je_Category,
1926 je_source = X_Je_Source,
1927 period_name = X_Period_Name,
1928 name = X_Name,
1929 currency_code = X_Currency_Code,
1930 status = X_Status,
1931 date_created = X_Date_Created,
1932 accrual_rev_flag = X_Accrual_Rev_Flag,
1933 multi_bal_seg_flag = X_Multi_Bal_Seg_Flag,
1934 actual_flag = X_Actual_Flag,
1935 default_effective_date = X_Default_Effective_Date,
1936 conversion_flag = X_Conversion_Flag,
1937 last_update_login = X_Last_Update_Login,
1938 encumbrance_type_id = X_Encumbrance_Type_Id,
1939 budget_version_id = X_Budget_Version_Id,
1940 balanced_je_flag = X_Balanced_Je_Flag,
1941 balancing_segment_value = X_Balancing_Segment_Value,
1942 je_batch_id = X_Je_Batch_Id,
1943 from_recurring_header_id = X_From_Recurring_Header_Id,
1944 unique_date = X_Unique_Date,
1945 earliest_postable_date = X_Earliest_Postable_Date,
1946 posted_date = X_Posted_Date,
1947 accrual_rev_effective_date = X_Accrual_Rev_Effective_Date,
1948 accrual_rev_period_name = X_Accrual_Rev_Period_Name,
1949 accrual_rev_status = X_Accrual_Rev_Status,
1950 accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id,
1951 accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag,
1952 description = X_Description,
1953 tax_status_code = X_Tax_Status_Code,
1954 control_total = X_Control_Total,
1955 running_total_dr = X_Running_Total_Dr,
1956 running_total_cr = X_Running_Total_Cr,
1957 running_total_accounted_dr = X_Running_Total_Accounted_Dr,
1958 running_total_accounted_cr = X_Running_Total_Accounted_Cr,
1959 currency_conversion_rate = X_Currency_Conversion_Rate,
1960 currency_conversion_type = X_Currency_Conversion_Type,
1961 currency_conversion_date = X_Currency_Conversion_Date,
1962 external_reference = X_External_Reference,
1963 attribute1 = X_Attribute1,
1964 attribute2 = X_Attribute2,
1965 attribute3 = X_Attribute3,
1966 attribute4 = X_Attribute4,
1967 attribute5 = X_Attribute5,
1968 attribute6 = X_Attribute6,
1969 attribute7 = X_Attribute7,
1970 attribute8 = X_Attribute8,
1971 attribute9 = X_Attribute9,
1972 attribute10 = X_Attribute10,
1973 context = X_Context,
1974 ussgl_transaction_code = X_Ussgl_Transaction_Code,
1975 context2 = X_Context2,
1976 doc_sequence_id = X_Doc_Sequence_Id,
1977 doc_sequence_value = X_Doc_Sequence_Value
1978 WHERE rowid = X_rowid;
1979
1980 if (SQL%NOTFOUND) then
1981 RAISE NO_DATA_FOUND;
1982 end if;
1983
1984 END Update_Row;
1985
1986 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1987 X_Je_Header_Id NUMBER,
1988 X_Last_Update_Date DATE,
1989 X_Last_Updated_By NUMBER,
1990 X_Ledger_Id NUMBER,
1991 X_Je_Category VARCHAR2,
1992 X_Je_Source VARCHAR2,
1993 X_Period_Name VARCHAR2,
1994 X_Name VARCHAR2,
1995 X_Currency_Code VARCHAR2,
1996 X_Status VARCHAR2,
1997 X_Date_Created DATE,
1998 X_Accrual_Rev_Flag VARCHAR2,
1999 X_Multi_Bal_Seg_Flag VARCHAR2,
2000 X_Actual_Flag VARCHAR2,
2001 X_Default_Effective_Date DATE,
2002 X_Conversion_Flag VARCHAR2,
2003 X_Last_Update_Login NUMBER,
2004 X_Encumbrance_Type_Id NUMBER,
2005 X_Budget_Version_Id NUMBER,
2006 X_Balanced_Je_Flag VARCHAR2,
2007 X_Balancing_Segment_Value VARCHAR2,
2008 X_Je_Batch_Id NUMBER,
2009 X_From_Recurring_Header_Id NUMBER,
2010 X_Unique_Date VARCHAR2,
2011 X_Earliest_Postable_Date DATE,
2012 X_Posted_Date DATE,
2013 X_Accrual_Rev_Effective_Date DATE,
2014 X_Accrual_Rev_Period_Name VARCHAR2,
2015 X_Accrual_Rev_Status VARCHAR2,
2016 X_Accrual_Rev_Je_Header_Id NUMBER,
2017 X_Accrual_Rev_Change_Sign_Flag VARCHAR2,
2018 X_Description VARCHAR2,
2019 X_Tax_Status_Code VARCHAR2,
2020 X_Control_Total NUMBER,
2021 X_Running_Total_Dr NUMBER,
2022 X_Running_Total_Cr NUMBER,
2023 X_Running_Total_Accounted_Dr NUMBER,
2024 X_Running_Total_Accounted_Cr NUMBER,
2025 X_Currency_Conversion_Rate NUMBER,
2026 X_Currency_Conversion_Type VARCHAR2,
2027 X_Currency_Conversion_Date DATE,
2028 X_External_Reference VARCHAR2,
2029 X_Originating_Bal_Seg_Value VARCHAR2,
2030 X_Attribute1 VARCHAR2,
2031 X_Attribute2 VARCHAR2,
2032 X_Attribute3 VARCHAR2,
2033 X_Attribute4 VARCHAR2,
2034 X_Attribute5 VARCHAR2,
2035 X_Attribute6 VARCHAR2,
2036 X_Attribute7 VARCHAR2,
2037 X_Attribute8 VARCHAR2,
2038 X_Attribute9 VARCHAR2,
2039 X_Attribute10 VARCHAR2,
2040 X_Context VARCHAR2,
2041 X_Global_Attribute1 VARCHAR2,
2042 X_Global_Attribute2 VARCHAR2,
2043 X_Global_Attribute3 VARCHAR2,
2044 X_Global_Attribute4 VARCHAR2,
2045 X_Global_Attribute5 VARCHAR2,
2046 X_Global_Attribute6 VARCHAR2,
2047 X_Global_Attribute7 VARCHAR2,
2048 X_Global_Attribute8 VARCHAR2,
2049 X_Global_Attribute9 VARCHAR2,
2050 X_Global_Attribute10 VARCHAR2,
2051 X_Global_Attribute_Category VARCHAR2,
2052 X_Ussgl_Transaction_Code VARCHAR2,
2053 X_Context2 VARCHAR2,
2054 X_Doc_Sequence_Id NUMBER,
2055 X_Doc_Sequence_Value NUMBER,
2056 X_Effective_Date_Changed VARCHAR2,
2057 X_Header_Mode VARCHAR2,
2058 X_Batch_Row_Id VARCHAR2,
2059 X_Batch_Name VARCHAR2,
2060 X_Chart_of_Accounts_ID NUMBER,
2061 X_Period_Set_Name VARCHAR2,
2062 X_Accounted_Period_Type VARCHAR2,
2063 X_Batch_Status VARCHAR2,
2064 X_Status_Verified VARCHAR2,
2065 X_Batch_Default_Effective_Date DATE,
2066 X_Batch_Posted_Date DATE,
2067 X_Batch_Date_Created DATE,
2068 X_Budgetary_Control_Status VARCHAR2,
2069 X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
2070 X_Batch_Control_Total IN OUT NOCOPY NUMBER,
2071 X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
2072 X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
2073 X_Average_Journal_Flag VARCHAR2,
2074 X_Posting_Run_Id NUMBER,
2075 X_Request_Id NUMBER,
2076 X_Packet_Id NUMBER,
2077 X_Unreservation_Packet_Id NUMBER,
2078 Update_Effective_Date_Flag VARCHAR2,
2079 Update_Approval_Stat_Flag VARCHAR2,
2080 X_Jgzz_Recon_Context VARCHAR2,
2081 X_Jgzz_Recon_Ref VARCHAR2,
2082 X_Reference_Date DATE
2083 ) IS
2084 has_line VARCHAR2(1);
2085 BEGIN
2086
2087 -- Make sure all journals have at least one line.
2088 has_line := 'N';
2089 IF (X_Je_Header_Id IS NOT NULL) THEN
2090 BEGIN
2091 SELECT 'Y'
2092 INTO has_line
2093 FROM gl_je_lines
2094 WHERE je_header_id = X_Je_Header_Id
2095 AND rownum = 1;
2096 EXCEPTION
2097 WHEN NO_DATA_FOUND THEN
2098 has_line := 'N';
2099 END;
2100 END IF;
2101
2102 IF (has_line = 'N') THEN
2103 fnd_message.set_name('SQLGL', 'GL_JE_JOURNAL_W_NO_LINES');
2104 app_exception.raise_exception;
2105 END IF;
2106
2107 -- Update the lines effective date, if necessary
2108 IF (X_Effective_Date_Changed = 'Y') THEN
2109 gl_je_lines_pkg.update_lines(
2110 X_Je_Header_Id,
2111 X_Period_Name,
2112 X_Default_Effective_Date,
2113 -1,
2114 -1,
2115 null,
2116 null,
2117 'N',
2118 'N',
2119 X_Last_Updated_By,
2120 X_Last_Update_Login);
2121 END IF;
2122
2123 UPDATE GL_JE_HEADERS
2124 SET
2125
2126 je_header_id = X_Je_Header_Id,
2127 last_update_date = X_Last_Update_Date,
2128 last_updated_by = X_Last_Updated_By,
2129 ledger_id = X_Ledger_Id,
2130 je_category = X_Je_Category,
2131 je_source = X_Je_Source,
2132 period_name = X_Period_Name,
2133 name = X_Name,
2134 currency_code = X_Currency_Code,
2135 status = X_Status,
2136 date_created = X_Date_Created,
2137 accrual_rev_flag = X_Accrual_Rev_Flag,
2138 multi_bal_seg_flag = X_Multi_Bal_Seg_Flag,
2139 actual_flag = X_Actual_Flag,
2140 default_effective_date = X_Default_Effective_Date,
2141 conversion_flag = X_Conversion_Flag,
2142 last_update_login = X_Last_Update_Login,
2143 encumbrance_type_id = X_Encumbrance_Type_Id,
2144 budget_version_id = X_Budget_Version_Id,
2145 balanced_je_flag = X_Balanced_Je_Flag,
2146 balancing_segment_value = X_Balancing_Segment_Value,
2147 je_batch_id = X_Je_Batch_Id,
2148 from_recurring_header_id = X_From_Recurring_Header_Id,
2149 unique_date = X_Unique_Date,
2150 earliest_postable_date = X_Earliest_Postable_Date,
2151 posted_date = X_Posted_Date,
2152 accrual_rev_effective_date = X_Accrual_Rev_Effective_Date,
2153 accrual_rev_period_name = X_Accrual_Rev_Period_Name,
2154 accrual_rev_status = X_Accrual_Rev_Status,
2155 accrual_rev_je_header_id = X_Accrual_Rev_Je_Header_Id,
2156 accrual_rev_change_sign_flag = X_Accrual_Rev_Change_Sign_Flag,
2157 description = X_Description,
2158 tax_status_code = X_Tax_Status_Code,
2159 control_total = X_Control_Total,
2160 running_total_dr = X_Running_Total_Dr,
2161 running_total_cr = X_Running_Total_Cr,
2162 running_total_accounted_dr = X_Running_Total_Accounted_Dr,
2163 running_total_accounted_cr = X_Running_Total_Accounted_Cr,
2164 currency_conversion_rate = X_Currency_Conversion_Rate,
2165 currency_conversion_type = X_Currency_Conversion_Type,
2166 currency_conversion_date = X_Currency_Conversion_Date,
2167 external_reference = X_External_Reference,
2168 originating_bal_seg_value = X_Originating_Bal_Seg_Value,
2169 attribute1 = X_Attribute1,
2170 attribute2 = X_Attribute2,
2171 attribute3 = X_Attribute3,
2172 attribute4 = X_Attribute4,
2173 attribute5 = X_Attribute5,
2174 attribute6 = X_Attribute6,
2175 attribute7 = X_Attribute7,
2176 attribute8 = X_Attribute8,
2177 attribute9 = X_Attribute9,
2178 attribute10 = X_Attribute10,
2179 context = X_Context,
2180 global_attribute1 = X_Global_Attribute1,
2181 global_attribute2 = X_Global_Attribute2,
2182 global_attribute3 = X_Global_Attribute3,
2183 global_attribute4 = X_Global_Attribute4,
2184 global_attribute5 = X_Global_Attribute5,
2185 global_attribute6 = X_Global_Attribute6,
2186 global_attribute7 = X_Global_Attribute7,
2187 global_attribute8 = X_Global_Attribute8,
2188 global_attribute9 = X_Global_Attribute9,
2189 global_attribute10 = X_Global_Attribute10,
2190 global_attribute_category = X_Global_Attribute_Category,
2191 ussgl_transaction_code = X_Ussgl_Transaction_Code,
2192 context2 = X_Context2,
2193 doc_sequence_id = X_Doc_Sequence_Id,
2194 doc_sequence_value = X_Doc_Sequence_Value,
2195 jgzz_recon_context = X_Jgzz_Recon_Context,
2196 jgzz_recon_ref = X_Jgzz_Recon_Ref,
2197 reference_date = X_Reference_Date
2198 WHERE rowid = X_rowid;
2199
2200 if (SQL%NOTFOUND) then
2201 RAISE NO_DATA_FOUND;
2202 end if;
2203
2204 -- If we are in header mode, then update the batch
2205 -- Update the header first so that we can correctly
2206 -- calculate the running totals.
2207 IF (X_Header_Mode = 'Y') THEN
2208 GL_JE_BATCHES_PKG.Update_Row(
2209 X_Rowid => X_Batch_Row_Id,
2210 X_Je_Batch_Id => X_Je_Batch_Id,
2211 X_Name => X_Batch_Name,
2212 X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
2213 X_Period_Set_Name => X_Period_Set_Name,
2214 X_Accounted_Period_Type => X_Accounted_Period_Type,
2215 X_Status => X_Batch_Status,
2216 X_Budgetary_Control_Status=>
2217 X_Budgetary_Control_Status,
2218 X_Approval_Status_Code => X_Approval_Status_Code,
2219 X_Status_Verified => X_Status_Verified,
2220 X_Actual_Flag => X_Actual_Flag,
2221 X_Default_Period_Name => X_Period_Name,
2222 X_Default_Effective_Date=>
2223 X_Batch_Default_Effective_Date,
2224 X_Posted_Date => X_Batch_Posted_Date,
2225 X_Date_Created => X_Batch_Date_Created,
2226 X_Control_Total => X_Batch_Control_Total,
2227 X_Running_Total_Dr => X_Batch_Running_Total_Dr,
2228 X_Running_Total_Cr => X_Batch_Running_Total_Cr,
2229 X_Average_Journal_Flag => X_Average_Journal_Flag,
2230 X_Posting_Run_Id => X_Posting_Run_Id,
2231 X_Request_Id => X_Request_Id,
2232 X_Packet_Id => X_Packet_Id,
2233 X_Unreservation_Packet_Id=>
2234 X_Unreservation_Packet_Id,
2235 X_Last_Update_Date => X_Last_Update_Date,
2236 X_Last_Updated_By => X_Last_Updated_By,
2237 X_Last_Update_Login => X_Last_Update_Login,
2238 Update_Effective_Date_Flag => Update_Effective_Date_Flag,
2239 Update_Approval_Stat_Flag => Update_Approval_Stat_Flag);
2240 END IF;
2241
2242 END Update_Row;
2243
2244 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
2245 X_Je_Header_Id NUMBER,
2246 X_Header_Mode VARCHAR2,
2247 X_Batch_Row_Id VARCHAR2,
2248 X_Je_Batch_Id NUMBER,
2249 X_Ledger_Id NUMBER,
2250 X_Actual_Flag VARCHAR2,
2251 X_Period_Name VARCHAR2,
2252 X_Batch_Name VARCHAR2,
2253 X_Chart_of_Accounts_ID NUMBER,
2254 X_Period_Set_Name VARCHAR2,
2255 X_Accounted_Period_Type VARCHAR2,
2256 X_Batch_Status VARCHAR2,
2257 X_Status_Verified VARCHAR2,
2258 X_Batch_Default_Effective_Date DATE,
2259 X_Batch_Posted_Date DATE,
2260 X_Batch_Date_Created DATE,
2261 X_Budgetary_Control_Status VARCHAR2,
2262 X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
2263 X_Batch_Control_Total IN OUT NOCOPY NUMBER,
2264 X_Batch_Running_Total_Dr IN OUT NOCOPY NUMBER,
2265 X_Batch_Running_Total_Cr IN OUT NOCOPY NUMBER,
2266 X_Average_Journal_Flag VARCHAR2,
2267 X_Posting_Run_Id NUMBER,
2268 X_Request_Id NUMBER,
2269 X_Packet_Id NUMBER,
2270 X_Unreservation_Packet_Id NUMBER,
2271 X_Last_Updated_By NUMBER,
2272 X_Last_Update_Login NUMBER
2273 ) IS
2274 CURSOR count_headers IS
2275 SELECT count(*)
2276 FROM gl_je_headers
2277 WHERE je_batch_id = X_Je_Batch_Id;
2278
2279 CURSOR get_child IS
2280 SELECT gh.je_header_id,gh.je_batch_id,gl.ledger_category_code,gh.rowid,gl.ledger_id
2281 FROM gl_je_headers gh,gl_ledgers gl
2282 WHERE gh.parent_je_header_id = X_Je_Header_Id
2283 AND gh.ledger_id = gl.ledger_id
2284 AND gl.ledger_category_code in ('ALC','SECONDARY')
2285 AND gh.status = decode(gl.ledger_category_code, 'SECONDARY','U','ALC',gh.status);
2286
2287 header_count NUMBER := 0;
2288 l_je_header_id NUMBER;
2289 l_je_batch_id NUMBER;
2290 l_ledger_category_code VARCHAR2(15);
2291 l_rowid VARCHAR2(18);
2292 l_ledger_id NUMBER;
2293 dummy NUMBER;
2294 N_Rowid VARCHAR2(18);
2295 N_Actual_Flag VARCHAR2(1);
2296 N_Default_Period_Name VARCHAR2(15);
2297 N_Name VARCHAR2(100);
2298 N_Chart_of_Accounts_ID NUMBER;
2299 N_Period_Set_Name VARCHAR2(15);
2300 N_Accounted_Period_Type VARCHAR2(15);
2301 N_Status VARCHAR2(1);
2302 N_Status_Verified VARCHAR2(1);
2303 N_Default_Effective_Date DATE;
2304 N_Posted_Date DATE;
2305 N_Date_Created DATE;
2306 N_Budgetary_Control_Status VARCHAR2(1);
2307 N_Approval_Status_Code VARCHAR2(1);
2308 N_Control_Total NUMBER;
2309 N_Running_Total_Dr NUMBER;
2310 N_Running_Total_Cr NUMBER;
2311 N_Average_Journal_Flag VARCHAR2(1);
2312 N_Posting_Run_Id NUMBER;
2313 N_Request_Id NUMBER;
2314 N_Packet_Id NUMBER;
2315 N_Unreservation_Packet_Id NUMBER;
2316 N_Last_Updated_By NUMBER;
2317 N_Last_Update_Login NUMBER;
2318 BEGIN
2319 -- Delete any lines
2320 gl_je_lines_pkg.delete_lines(X_Je_Header_Id);
2321
2322 -- Delete the associated segment values
2323 dummy := gl_je_segment_values_pkg.delete_segment_values(X_Je_Header_Id);
2324
2325 -- Delete all of the reconciliation lines
2326 DELETE gl_je_lines_recon
2327 WHERE je_header_id = X_Je_Header_Id;
2328
2329 OPEN get_child;
2330 LOOP
2331 FETCH get_child INTO l_je_header_id,l_je_batch_id,l_ledger_category_code,l_rowid,l_ledger_id;
2332 EXIT WHEN get_child%NOTFOUND;
2333
2334 IF (l_ledger_category_code = 'ALC') THEN
2335 gl_je_lines_pkg.delete_lines(l_je_header_id);
2336 dummy := gl_je_segment_values_pkg.delete_segment_values(l_je_header_id);
2337
2338 -- Delete all of the reconciliation lines
2339 DELETE gl_je_lines_recon
2340 WHERE je_header_id = l_je_header_id;
2341
2342 ELSE
2343
2344 SELECT Rowid,
2345 Actual_Flag,
2346 Default_Period_Name,
2347 Name,
2348 Chart_of_Accounts_ID,
2349 Period_Set_Name,
2350 Accounted_Period_Type,
2351 Status,
2352 Status_Verified,
2353 Default_Effective_Date,
2354 Posted_Date,
2355 Date_Created,
2356 Budgetary_Control_Status,
2357 Approval_Status_Code,
2358 Control_Total,
2359 Running_Total_Dr,
2360 Running_Total_Cr,
2361 Average_Journal_Flag,
2362 Posting_Run_Id,
2363 Request_Id,
2364 Packet_Id,
2365 Unreservation_Packet_Id,
2366 Last_Updated_By,
2367 Last_Update_Login
2368 INTO N_Rowid,
2369 N_Actual_Flag,
2370 N_Default_Period_Name,
2371 N_Name,
2372 N_Chart_of_Accounts_ID,
2373 N_Period_Set_Name,
2374 N_Accounted_Period_Type,
2375 N_Status,
2376 N_Status_Verified,
2377 N_Default_Effective_Date,
2378 N_Posted_Date,
2379 N_Date_Created,
2380 N_Budgetary_Control_Status,
2381 N_Approval_Status_Code,
2382 N_Control_Total,
2383 N_Running_Total_Dr,
2384 N_Running_Total_Cr,
2385 N_Average_Journal_Flag,
2386 N_Posting_Run_Id,
2387 N_Request_Id,
2388 N_Packet_Id,
2389 N_Unreservation_Packet_Id,
2390 N_Last_Updated_By,
2391 N_Last_Update_Login
2392 FROM gl_je_batches
2393 WHERE je_batch_id = l_je_batch_id;
2394
2395 gl_je_headers_pkg.Delete_Row(l_rowid,
2396 l_je_header_id,
2397 X_Header_Mode,
2398 N_Rowid,
2399 l_je_batch_id,
2400 l_ledger_id,
2401 N_Actual_Flag,
2402 N_Default_Period_Name,
2403 N_Name,
2404 N_Chart_of_Accounts_ID,
2405 N_Period_Set_Name,
2406 N_Accounted_Period_Type,
2407 N_Status,
2408 N_Status_Verified,
2409 N_Default_Effective_Date,
2410 N_Posted_Date,
2411 N_Date_Created,
2412 N_Budgetary_Control_Status,
2413 N_Approval_Status_Code,
2414 N_Control_Total,
2415 N_Running_Total_Dr,
2416 N_Running_Total_Cr,
2417 N_Average_Journal_Flag,
2418 N_Posting_Run_Id,
2419 N_Request_Id,
2420 N_Packet_Id,
2421 N_Unreservation_Packet_Id,
2422 N_Last_Updated_By,
2423 N_Last_Update_Login );
2424
2425 END IF;
2426
2427 END LOOP;
2428 CLOSE get_child;
2429
2430 -- Mark the reversals as no longer reversals, since the
2431 -- original journal has been deleted. This is necessary to fix
2432 -- bug #1001521
2433 UPDATE gl_je_headers
2434 SET reversed_je_header_id = null,
2435 accrual_rev_je_header_id = decode(accrual_rev_status,
2436 'R', accrual_rev_je_header_id,
2437 null)
2438 WHERE je_header_id =
2439 (SELECT accrual_rev_je_header_id
2440 FROM gl_je_headers
2441 WHERE rowid = X_Rowid
2442 AND accrual_rev_status = 'R');
2443
2444 -- Bug fix 2749073 Mark the original journal as reversible
2445 -- incase if the reversed journal associated is deleted.
2446 UPDATE gl_je_headers
2447 SET accrual_rev_status = null,
2448 accrual_rev_je_header_id =null,
2449 accrual_rev_flag = 'Y'
2450 WHERE je_header_id =
2451 ( SELECT reversed_je_header_id
2452 FROM gl_je_headers
2453 WHERE je_header_id = X_Je_Header_Id
2454 AND reversed_je_header_id IS NOT NULL);
2455
2456 -- Mark the ALC reversals as no longer reversals, since the
2457 -- original journal has been deleted.
2458 UPDATE gl_je_headers
2459 SET reversed_je_header_id = null,
2460 accrual_rev_je_header_id = decode(accrual_rev_status,
2461 'R', accrual_rev_je_header_id,
2462 null)
2463 WHERE je_header_id =
2464 (SELECT gh.accrual_rev_je_header_id
2465 FROM gl_je_headers gh,gl_ledgers gl
2466 WHERE gh.parent_je_header_id = X_Je_Header_Id
2467 AND gh.accrual_rev_status = 'R'
2468 AND gh.ledger_id = gl.ledger_id
2469 AND gl.ledger_category_code = 'ALC');
2470
2471 --Mark the original journal as reversible
2472 -- incase if the reversed journal associated is deleted.
2473 UPDATE gl_je_headers
2474 SET accrual_rev_status = null,
2475 accrual_rev_je_header_id =null,
2476 accrual_rev_flag = 'Y'
2477 WHERE je_header_id =
2478 ( SELECT gh.reversed_je_header_id
2479 FROM gl_je_headers gh,gl_ledgers gl
2480 WHERE gh.parent_je_header_id = X_Je_Header_Id
2481 AND gh.reversed_je_header_id IS NOT NULL
2482 AND gh.ledger_id = gl.ledger_id
2483 AND gl.ledger_category_code = 'ALC');
2484
2485 --Delete the the corresponding ALCs when a primary journals is deleted.
2486 --DELETE FROM gl_je_headers
2487 --WHERE parent_je_header_id = X_Je_Header_Id;
2488 Delete from gl_je_headers glh
2489 where parent_je_header_id =X_Je_Header_Id
2490 and ledger_id in (SELECT ledger_id
2491 FROM gl_ledgers
2492 WHERE ledger_id = glh.ledger_id
2493 AND ledger_category_code = 'ALC');
2494
2495
2496 -- Delete the journal
2497 DELETE FROM GL_JE_HEADERS
2498 WHERE rowid = X_Rowid;
2499
2500 -- If we are deleting in the journal zone and we are in journal
2501 -- mode or if we are deleting from the folder zone, we need
2502 -- to update or delete the batch. We will delete
2503 -- the batch in the case where there are no other journals.
2504 IF (X_Header_Mode IN ('Y', 'F')) THEN
2505
2506 -- Determine the number of headers left in the batch
2507 OPEN count_headers;
2508 FETCH count_headers INTO header_count;
2509 CLOSE count_headers;
2510
2511 IF (header_count = 0) THEN
2512 GL_JE_BATCHES_PKG.delete_row(X_Batch_Row_id, X_Je_Batch_Id);
2513 ELSE
2514 GL_JE_BATCHES_PKG.Update_Row(
2515 X_Rowid => X_Batch_Row_Id,
2516 X_Je_Batch_Id => X_Je_Batch_Id,
2517 X_Name => X_Batch_Name,
2518 X_Chart_of_Accounts_Id => X_Chart_of_Accounts_Id,
2519 X_Period_Set_Name => X_Period_Set_Name,
2520 X_Accounted_Period_Type => X_Accounted_Period_Type,
2521 X_Status => X_Batch_Status,
2522 X_Budgetary_Control_Status=>
2523 X_Budgetary_Control_Status,
2524 X_Approval_Status_Code => X_Approval_Status_Code,
2525 X_Status_Verified => 'N',
2526 X_Actual_Flag => X_Actual_Flag,
2527 X_Default_Period_Name => X_Period_Name,
2528 X_Default_Effective_Date=>
2529 X_Batch_Default_Effective_Date,
2530 X_Posted_Date => X_Batch_Posted_Date,
2531 X_Date_Created => X_Batch_Date_Created,
2532 X_Control_Total => X_Batch_Control_Total,
2533 X_Running_Total_Dr => X_Batch_Running_Total_Dr,
2534 X_Running_Total_Cr => X_Batch_Running_Total_Cr,
2535 X_Average_Journal_Flag => X_Average_Journal_Flag,
2536 X_Posting_Run_Id => X_Posting_Run_Id,
2537 X_Request_Id => X_Request_Id,
2538 X_Packet_Id => X_Packet_Id,
2539 X_Unreservation_Packet_Id=>
2540 X_Unreservation_Packet_Id,
2541 X_Last_Update_Date => sysdate,
2542 X_Last_Updated_By => X_Last_Updated_By,
2543 X_Last_Update_Login => X_Last_Update_Login,
2544 Update_Effective_Date_Flag => 'N',
2545 Update_Approval_Stat_Flag => 'D');
2546 END IF;
2547
2548 END IF;
2549
2550 EXCEPTION
2551 WHEN NO_DATA_FOUND THEN
2552 null;
2553 END Delete_Row;
2554
2555 END GL_JE_HEADERS_PKG;