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