[Home] [Help]
PACKAGE BODY: APPS.GL_JE_BATCHES_PKG
Source
1 PACKAGE BODY gl_je_batches_pkg AS
2 /* $Header: glijebab.pls 120.17.12020000.4 2012/10/11 09:24:11 degoel ship $ */
3
4 --
5 -- PUBLIC FUNCTIONS
6 --
7
8 PROCEDURE check_unique(batch_name VARCHAR2,
9 period_name VARCHAR2,
10 coa_id NUMBER,
11 cal_name VARCHAR2,
12 per_type VARCHAR2,
13 row_id VARCHAR2) IS
14 CURSOR chk_duplicates is
15 SELECT 'Duplicate'
16 FROM GL_JE_BATCHES jeb
17 WHERE jeb.name = batch_name
18 AND jeb.default_period_name = period_name
19 AND jeb.chart_of_accounts_id = coa_id
20 AND jeb.period_set_name = cal_name
21 AND jeb.accounted_period_type = per_type
22 AND ( row_id is null
23 OR jeb.rowid <> row_id);
24 dummy VARCHAR2(100);
25 BEGIN
26 OPEN chk_duplicates;
27 FETCH chk_duplicates INTO dummy;
28
29 IF chk_duplicates%FOUND THEN
30 CLOSE chk_duplicates;
31 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_JE_BATCH_NAME');
32 app_exception.raise_exception;
33 END IF;
34
35 CLOSE chk_duplicates;
36
37 EXCEPTION
38 WHEN app_exceptions.application_exception THEN
39 RAISE;
40 WHEN OTHERS THEN
41 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
42 fnd_message.set_token('PROCEDURE', 'gl_je_batches_pkg.check_unique');
43 RAISE;
44 END check_unique;
45
46 FUNCTION get_unique_id RETURN NUMBER IS
47 CURSOR get_new_id IS
48 SELECT gl_je_batches_s.NEXTVAL
49 FROM dual;
50 new_id number;
51 BEGIN
52 OPEN get_new_id;
53 FETCH get_new_id INTO new_id;
54
55 IF get_new_id%FOUND THEN
56 CLOSE get_new_id;
57 return(new_id);
58 ELSE
59 CLOSE get_new_id;
60 fnd_message.set_name('SQLGL', 'GL_ERROR_GETTING_UNIQUE_ID');
61 fnd_message.set_token('SEQUENCE', 'GL_JE_BATCHES_S');
62 app_exception.raise_exception;
63 END IF;
64
65 EXCEPTION
66 WHEN app_exceptions.application_exception THEN
67 RAISE;
68 WHEN OTHERS THEN
69 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
70 fnd_message.set_token('PROCEDURE', 'gl_je_batches_pkg.get_unique_id');
71 RAISE;
72 END get_unique_id;
73
74
75 FUNCTION has_lines(batch_id NUMBER) RETURN BOOLEAN IS
76 CURSOR chk_batch IS
77 SELECT 'Has Lines'
78 FROM dual
79 WHERE EXISTS (SELECT 'Found Line'
80 FROM gl_je_headers jeh, gl_je_lines jel
81 WHERE jeh.je_batch_id = batch_id
82 AND jel.je_header_id = jeh.je_header_id);
83 dummy VARCHAR2(100);
84 BEGIN
85 OPEN chk_batch;
86 FETCH chk_batch INTO dummy;
87
88 IF chk_batch%FOUND THEN
89 CLOSE chk_batch;
90 return(TRUE);
91 ELSE
92 CLOSE chk_batch;
93 return(FALSE);
94 END IF;
95
96 EXCEPTION
97 WHEN app_exceptions.application_exception THEN
98 RAISE;
99 WHEN OTHERS THEN
100 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
101 fnd_message.set_token('PROCEDURE', 'gl_je_batches_pkg.has_lines');
102 RAISE;
103 END has_lines;
104
105
109 FROM dual
106 FUNCTION needs_approval(batch_id NUMBER) RETURN BOOLEAN IS
107 CURSOR needs_apprvl IS
108 SELECT 'Needs Approval'
110 WHERE EXISTS (SELECT 'Needs Approval'
111 FROM gl_je_headers jeh, gl_ledgers lgr,
112 gl_je_sources src
113 WHERE jeh.je_batch_id = batch_id
114 AND lgr.ledger_id = jeh.ledger_id
115 AND lgr.enable_je_approval_flag = 'Y'
116 AND src.je_source_name = jeh.je_source
117 AND src.journal_approval_flag = 'Y');
118 dummy VARCHAR2(100);
119 BEGIN
120 OPEN needs_apprvl;
121 FETCH needs_apprvl INTO dummy;
122
123 IF needs_apprvl%FOUND THEN
124 CLOSE needs_apprvl;
125 return(TRUE);
126 ELSE
127 CLOSE needs_apprvl;
128 return(FALSE);
129 END IF;
130
131 EXCEPTION
132 WHEN app_exceptions.application_exception THEN
133 RAISE;
134 WHEN OTHERS THEN
135 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
136 fnd_message.set_token('PROCEDURE', 'gl_je_batches_pkg.needs_approval');
137 RAISE;
138 END needs_approval;
139
140 FUNCTION needs_tax(batch_id NUMBER) RETURN BOOLEAN IS
141 CURSOR needstx IS
142 SELECT 'Needs Tax'
143 FROM dual
144 WHERE EXISTS (SELECT 'Needs Tax'
145 FROM gl_je_headers jeh, gl_ledgers lgr
146 WHERE jeh.je_batch_id = batch_id
147 AND jeh.tax_status_code = 'R'
148 AND lgr.ledger_id = jeh.ledger_id
149 AND lgr.enable_automatic_tax_flag = 'Y');
150 dummy VARCHAR2(100);
151 BEGIN
152 OPEN needstx;
153 FETCH needstx INTO dummy;
154
155 IF needstx%FOUND THEN
156 CLOSE needstx;
157 return(TRUE);
158 ELSE
159 CLOSE needstx;
160 return(FALSE);
161 END IF;
162
163 EXCEPTION
164 WHEN app_exceptions.application_exception THEN
165 RAISE;
166 WHEN OTHERS THEN
167 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
168 fnd_message.set_token('PROCEDURE', 'gl_je_batches_pkg.needs_tax');
169 RAISE;
170 END needs_tax;
171
172
173 FUNCTION all_stat_headers( X_je_batch_id NUMBER ) RETURN BOOLEAN IS
174 CURSOR chk_all_stat_headers IS
175 SELECT
176 decode(count(*),
177 sum(decode(JH.currency_code, 'STAT', 1, 0)), 'All STAT',
178 'Not all STAT')
179 FROM
180 GL_JE_HEADERS JH
181 WHERE
182 JH.je_batch_id = X_je_batch_id
183 AND (JH.display_alc_journal_flag is null or JH.display_alc_journal_flag = 'Y');
184 dummy VARCHAR2(100);
185 BEGIN
186 OPEN chk_all_stat_headers;
187 FETCH chk_all_stat_headers INTO dummy;
188
189 IF ( chk_all_stat_headers%FOUND ) THEN
190 CLOSE chk_all_stat_headers;
191 RETURN( dummy = 'All STAT' );
192 ELSE
193 CLOSE chk_all_stat_headers;
194 return(FALSE);
195 END IF;
196 END all_stat_headers;
197
198 FUNCTION bc_ledger( X_je_batch_id NUMBER ) RETURN NUMBER IS
199 CURSOR get_ledger_id IS
200 SELECT
201 DISTINCT JH.ledger_id
202 FROM
203 GL_JE_HEADERS JH, GL_LEDGERS LGR
204 WHERE
205 JH.je_batch_id = X_je_batch_id
206 AND (JH.display_alc_journal_flag is null
207 or JH.display_alc_journal_flag = 'Y')
208 AND LGR.ledger_id = JH.ledger_id
209 /*Added ALC condition for bug13424574*/
210 AND LGR.ledger_category_code IN ('PRIMARY', 'SECONDARY','ALC')
211 AND LGR.enable_budgetary_control_flag = 'Y';
212 lgr_id NUMBER;
213 BEGIN
214 OPEN get_ledger_id;
215 FETCH get_ledger_id INTO lgr_id;
216
217 IF ( get_ledger_id%FOUND ) THEN
218 -- Found one ledger, so lets check for two
219 FETCH get_ledger_id INTO lgr_id;
220
221 IF (get_ledger_id%FOUND) THEN
222 -- Two ledgers with budgetary control on. Return -2 to indicate
223 -- an error
224 CLOSE get_ledger_id;
225 RETURN (-2);
226 ELSE
227 -- Only one ledger. Good case.
228 CLOSE get_ledger_id;
229 RETURN(lgr_id);
230 END IF;
231 ELSE
232 -- No valid ledgers. Return -1 to indicate an error
233 CLOSE get_ledger_id;
234 return(-1);
235 END IF;
236 END bc_ledger;
237
238 PROCEDURE populate_fields(x_je_batch_id NUMBER,
239 x_je_source_name IN OUT NOCOPY VARCHAR2,
240 frozen_source_flag IN OUT NOCOPY VARCHAR2,
241 one_of_ledgers_in_batch IN OUT NOCOPY NUMBER,
242 reversal_flag IN OUT NOCOPY VARCHAR2) IS
243 BEGIN
244 SELECT max(ledger_id), nvl(max(je_source), 'Manual'),
245 nvl(max(decode(reversed_je_header_id, NULL, NULL, 'Y')),'N')
246 INTO one_of_ledgers_in_batch, x_je_source_name, reversal_flag
247 FROM gl_je_headers
248 WHERE je_batch_id = x_je_batch_id
249 AND rownum = 1;
250
251 SELECT override_edits_flag
252 INTO frozen_source_flag
253 FROM gl_je_sources
254 WHERE je_source_name = x_je_source_name;
255 END;
256
257
258 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
259 X_Je_Batch_Id IN OUT NOCOPY NUMBER,
263 X_Accounted_Period_Type VARCHAR2,
260 X_Name VARCHAR2,
261 X_Chart_of_Accounts_ID NUMBER,
262 X_Period_Set_Name VARCHAR2,
264 X_Status VARCHAR2,
265 X_Budgetary_Control_Status VARCHAR2,
266 X_Approval_Status_Code VARCHAR2,
267 X_Status_Verified VARCHAR2,
268 X_Actual_Flag VARCHAR2,
269 X_Default_Period_Name VARCHAR2,
270 X_Default_Effective_Date DATE,
271 X_Posted_Date DATE,
272 X_Date_Created DATE,
273 X_Control_Total IN OUT NOCOPY NUMBER,
274 X_Running_Total_Dr IN OUT NOCOPY NUMBER,
275 X_Running_Total_Cr IN OUT NOCOPY NUMBER,
276 X_Running_Total_Accounted_Dr NUMBER,
277 X_Running_Total_Accounted_Cr NUMBER,
278 X_Average_Journal_Flag VARCHAR2,
279 X_Org_Id NUMBER,
280 X_Posting_Run_Id NUMBER,
281 X_Request_Id NUMBER,
282 X_Packet_Id NUMBER,
283 X_Unreservation_Packet_Id NUMBER,
284 X_Creation_Date DATE,
285 X_Created_By NUMBER,
286 X_Last_Update_Date DATE,
287 X_Last_Updated_By NUMBER,
288 X_Last_Update_Login NUMBER
289 ) IS
290
291 CURSOR C IS SELECT rowid FROM GL_JE_BATCHES
292 WHERE je_batch_id = X_Je_Batch_Id;
293
294 has_je VARCHAR2(1);
295 BEGIN
296
297 -- Make sure all batches have at least one journal.
298 has_je := 'N';
299 IF (X_Je_Batch_Id IS NOT NULL) THEN
300 BEGIN
301 SELECT 'Y'
302 INTO has_je
303 FROM gl_je_headers
304 WHERE je_batch_id = X_Je_Batch_Id
305 AND rownum = 1;
306
307 EXCEPTION
308 WHEN NO_DATA_FOUND THEN
309 has_je := 'N';
310 END;
311 END IF;
312
313 IF (has_je = 'N') THEN
314 fnd_message.set_name('SQLGL', 'GL_JE_BATCH_W_NO_JOURNALS');
315 app_exception.raise_exception;
316 END IF;
317
318 INSERT INTO GL_JE_BATCHES(
319 je_batch_id,
320 name,
321 chart_of_accounts_id,
322 period_set_name,
323 accounted_period_type,
324 status,
325 budgetary_control_status,
326 approval_status_code,
327 status_verified,
328 actual_flag,
329 default_period_name,
330 default_effective_date,
331 posted_date,
332 date_created,
333 posting_run_id,
334 request_id,
335 packet_id,
336 unreservation_packet_id,
337 running_total_dr,
338 running_total_cr,
339 running_total_accounted_dr,
340 running_total_accounted_cr,
341 average_journal_flag,
342 org_id,
343 creation_date,
344 created_by,
345 last_update_date,
346 last_updated_by,
347 last_update_login
348 ) VALUES (
349 X_Je_Batch_Id,
350 X_Name,
351 X_chart_of_accounts_id,
352 X_period_set_name,
353 X_accounted_period_type,
354 X_Status,
355 X_Budgetary_Control_Status,
356 X_Approval_Status_Code,
357 X_Status_Verified,
358 X_Actual_Flag,
359 X_Default_Period_Name,
360 X_Default_Effective_Date,
361 X_Posted_Date,
362 X_Date_Created,
363 X_Posting_Run_Id,
364 X_Request_Id,
365 X_Packet_Id,
366 X_Unreservation_Packet_Id,
367 X_Running_Total_Dr,
368 X_Running_Total_Cr,
369 X_Running_Total_Accounted_Dr,
370 X_Running_Total_Accounted_Cr,
371 X_Average_Journal_Flag,
372 X_Org_Id,
373 X_Creation_Date,
374 X_Created_By,
375 X_Last_Update_Date,
376 X_Last_Updated_By,
377 X_Last_Update_Login
378 );
379
380 OPEN C;
381 FETCH C INTO X_Rowid;
382 if (C%NOTFOUND) then
383 CLOSE C;
384 RAISE NO_DATA_FOUND;
385 end if;
386 CLOSE C;
387 END Insert_Row;
388
389
390 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
391 X_Je_Batch_Id NUMBER,
392 X_Name VARCHAR2,
393 X_Chart_of_Accounts_ID NUMBER,
394 X_Period_Set_Name VARCHAR2,
395 X_Accounted_Period_Type VARCHAR2,
396 X_Status VARCHAR2,
397 X_Budgetary_Control_Status VARCHAR2,
398 X_Approval_Status_Code VARCHAR2,
399 X_Status_Verified VARCHAR2,
403 X_Posted_Date DATE,
400 X_Actual_Flag VARCHAR2,
401 X_Default_Period_Name VARCHAR2,
402 X_Default_Effective_Date DATE,
404 X_Date_Created DATE,
405 X_Control_Total NUMBER,
406 X_Running_Total_Dr NUMBER,
407 X_Running_Total_Cr NUMBER,
408 X_Average_Journal_Flag VARCHAR2,
409 X_Posting_Run_Id NUMBER,
410 X_Request_Id NUMBER,
411 X_Packet_Id NUMBER,
412 X_Unreservation_Packet_Id NUMBER,
413 X_Verify_Request_Completed VARCHAR2
414 ) IS
415 CURSOR C IS
416 SELECT *
417 FROM GL_JE_BATCHES
418 WHERE rowid = X_Rowid
419 FOR UPDATE of Je_Batch_Id NOWAIT;
420 Recinfo C%ROWTYPE;
421 dev_request_phase VARCHAR2(30);
422 BEGIN
423 OPEN C;
424 FETCH C INTO Recinfo;
425 if (C%NOTFOUND) then
426 CLOSE C;
427 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 end if;
430 CLOSE C;
431
432 if (
433 ( (Recinfo.je_batch_id = X_Je_Batch_Id)
434 OR ( (Recinfo.je_batch_id IS NULL)
435 AND (X_Je_Batch_Id IS NULL)))
436 AND ( (Recinfo.name = X_Name)
437 OR ( (Recinfo.name IS NULL)
438 AND (X_Name IS NULL)))
439 AND (Recinfo.chart_of_accounts_id = X_Chart_of_Accounts_id)
440 AND (Recinfo.period_set_name = X_Period_Set_Name)
441 AND (Recinfo.accounted_period_type = X_Accounted_Period_Type)
442 AND ( (Recinfo.status = X_Status)
443 OR ( (Recinfo.status IS NULL)
444 AND (X_Status IS NULL)))
445 AND ( (Recinfo.budgetary_control_status = X_Budgetary_Control_Status)
446 OR ( (Recinfo.budgetary_control_status IS NULL)
447 AND (X_Budgetary_Control_Status IS NULL)))
448 AND ( (Recinfo.approval_status_code = X_Approval_Status_Code)
449 OR ( (Recinfo.approval_status_code IS NULL)
450 AND (X_Approval_Status_Code IS NULL)))
451 AND ( (Recinfo.status_verified = X_Status_Verified)
452 OR ( (Recinfo.status_verified IS NULL)
453 AND (X_Status_Verified IS NULL)))
454 AND ( (Recinfo.actual_flag = X_Actual_Flag)
455 OR ( (Recinfo.actual_flag IS NULL)
456 AND (X_Actual_Flag IS NULL)))
457 AND ( (Recinfo.default_period_name = X_Default_Period_Name)
458 OR ( (Recinfo.default_period_name IS NULL)
459 AND (X_Default_Period_Name IS NULL)))
460 AND ( (Recinfo.default_effective_date = X_Default_Effective_Date)
461 OR ( (Recinfo.default_effective_date IS NULL)
462 AND (X_Default_Effective_Date IS NULL)))
463 AND ( (trunc(Recinfo.posted_date) = trunc(X_Posted_Date))
464 OR ( (Recinfo.posted_date IS NULL)
465 AND (X_Posted_Date IS NULL)))
466 AND ( (trunc(Recinfo.date_created) = trunc(X_Date_Created))
467 OR ( (Recinfo.date_created IS NULL)
468 AND (X_Date_Created IS NULL)))
469 AND ( (Recinfo.control_total = X_Control_Total)
470 OR ( (Recinfo.control_total IS NULL)
471 AND (X_Control_Total IS NULL)))
472 AND ( (Recinfo.running_total_dr = X_Running_Total_Dr)
473 OR ( (Recinfo.running_total_dr IS NULL)
474 AND (X_Running_Total_Dr IS NULL)))
475 AND ( (Recinfo.running_total_cr = X_Running_Total_Cr)
476 OR ( (Recinfo.running_total_cr IS NULL)
477 AND (X_Running_Total_Cr IS NULL)))
478 AND ( (Recinfo.average_journal_flag = X_Average_Journal_Flag)
479 OR ( (Recinfo.average_journal_flag IS NULL)
480 AND (X_Average_Journal_Flag IS NULL)))
481 AND ( (Recinfo.posting_run_id = X_Posting_Run_Id)
482 OR ( (Recinfo.posting_run_id IS NULL)
483 AND (X_Posting_Run_Id IS NULL)))
484 AND ( (Recinfo.request_id = X_Request_Id)
485 OR ( (Recinfo.request_id IS NULL)
486 AND (X_Request_Id IS NULL)))
487 AND ( (Recinfo.packet_id = X_Packet_Id)
488 OR ( (Recinfo.packet_id IS NULL)
489 AND (X_Packet_Id IS NULL)))
490 AND ( (Recinfo.unreservation_packet_id = X_Unreservation_Packet_Id)
491 OR ( (Recinfo.unreservation_packet_id IS NULL)
492 AND (X_Unreservation_Packet_Id IS NULL)))
493 ) then
494
495 -- If the batch status indicates that it is being processed,
496 -- check to verify that it is actually still being processed.
497 IF (X_Status IN ('I', 'S')) THEN
498 -- If the user has already attempted to post this batch,
499 -- then get information about the results
500 IF (X_Request_Id IS NOT NULL) THEN
501 DECLARE
502 call_status BOOLEAN;
503 request_phase VARCHAR2(30);
504 request_status VARCHAR2(30);
505 dev_request_status VARCHAR2(30);
506 request_status_mesg VARCHAR2(255);
507 request_id NUMBER;
508 BEGIN
509 request_id := X_Request_Id;
510 call_status :=
511 fnd_concurrent.get_request_status(
512 request_id,
516 request_status,
513 'SQLGL',
514 'GLPPOS',
515 request_phase,
517 dev_request_phase,
518 dev_request_status,
519 request_status_mesg );
520 END;
521 END IF;
522 IF (nvl(dev_request_phase, 'COMPLETE') <> 'COMPLETE') THEN
523 FND_MESSAGE.set_name('SQLGL', 'GL_MJE_BATCH_BEING_PROCESSED');
524 app_exception.raise_exception;
525 END IF;
526 END IF;
527 else
528 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 end if;
531 END Lock_Row;
532
533 PROCEDURE Update_Row(X_Rowid VARCHAR2,
534 X_Je_Batch_Id NUMBER,
535 X_Name VARCHAR2,
536 X_Chart_of_Accounts_ID NUMBER,
537 X_Period_Set_Name VARCHAR2,
538 X_Accounted_Period_Type VARCHAR2,
539 X_Status VARCHAR2,
540 X_Budgetary_Control_Status VARCHAR2,
541 X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
542 X_Status_Verified VARCHAR2,
543 X_Actual_Flag VARCHAR2,
544 X_Default_Period_Name VARCHAR2,
545 X_Default_Effective_Date DATE,
546 X_Posted_Date DATE,
547 X_Date_Created DATE,
548 X_Control_Total IN OUT NOCOPY NUMBER,
549 X_Running_Total_Dr IN OUT NOCOPY NUMBER,
550 X_Running_Total_Cr IN OUT NOCOPY NUMBER,
551 X_Average_Journal_Flag VARCHAR2,
552 X_Posting_Run_Id NUMBER,
553 X_Request_Id NUMBER,
554 X_Packet_Id NUMBER,
555 X_Unreservation_Packet_Id NUMBER,
556 X_Last_Update_Date DATE,
557 X_Last_Updated_By NUMBER,
558 X_Last_Update_Login NUMBER,
559 Update_Effective_Date_Flag VARCHAR2,
560 Update_Approval_Stat_Flag VARCHAR2
561 ) IS
562 X_Running_Total_Accounted_Dr NUMBER;
563 X_Running_Total_Accounted_Cr NUMBER;
564 has_je VARCHAR2(1);
565 BEGIN
566
567 -- Make sure all batches have at least one journal.
568 has_je := 'N';
569 IF (X_Je_Batch_Id IS NOT NULL) THEN
570 BEGIN
571 SELECT 'Y'
572 INTO has_je
573 FROM gl_je_headers
574 WHERE je_batch_id = X_Je_Batch_Id
575 AND rownum = 1;
576 EXCEPTION
577 WHEN NO_DATA_FOUND THEN
578 has_je := 'N';
579 END;
580 END IF;
581
582 IF (has_je = 'N') THEN
583 fnd_message.set_name('SQLGL', 'GL_JE_BATCH_W_NO_JOURNALS');
584 app_exception.raise_exception;
585 END IF;
586
587 -- If the user changes the average journal flag to 'Y', then
588 -- we need to reinitialize all of the journals effective dates.
589 IF (Update_Effective_Date_Flag = 'Y') THEN
590 GL_JE_HEADERS_PKG.change_effective_date(X_Je_Batch_Id,
591 X_Default_Effective_Date);
592 END IF;
593
594 -- If the user starts the approval process, then we need to
595 -- refetch the approval status
596 IF (Update_Approval_Stat_Flag = 'Y') THEN
597 SELECT approval_status_code
598 INTO X_Approval_Status_Code
599 FROM gl_je_batches
600 WHERE rowid = X_RowId;
601
602 -- If a journal has been deleted, we may need to reset the
603 -- approval required flag. Check.
604 ELSIF (Update_Approval_Stat_Flag = 'D') THEN
605 -- If a journal was deleted, the batch shouldn't have been
606 -- posted or approved, but check anyway.
607 IF ( (X_status <> 'P')
608 AND (X_Approval_Status_Code <> 'A')
609 ) THEN
610 IF (gl_je_batches_pkg.needs_approval(X_Je_Batch_Id)) THEN
611 IF (X_Approval_Status_Code = 'Z') THEN
612 X_Approval_Status_Code := 'R';
613 END IF;
614 ELSE
615 X_Approval_Status_Code := 'Z';
616 END IF;
617 END IF;
618 END IF;
619
620 -- Recalculate the running totals
621 gl_je_headers_pkg.calculate_totals(
622 X_Je_Batch_Id,
623 X_Running_Total_Dr,
624 X_Running_Total_Cr,
625 X_Running_Total_Accounted_Dr,
626 X_Running_Total_Accounted_Cr);
627
628
629 -- To prevent conflicts where the same user is updating multiple headers
630 -- from the same batch, only update the status verified if you are
631 -- updating it to 'N'.
632 UPDATE GL_JE_BATCHES
633 SET
634 je_batch_id = X_Je_Batch_Id,
635 name = X_Name,
636 chart_of_accounts_id = X_Chart_of_Accounts_id,
637 period_set_name = X_Period_Set_Name,
638 accounted_period_type = X_Accounted_Period_Type,
639 status = X_Status,
640 budgetary_control_status = X_Budgetary_Control_Status,
641 approval_status_code = X_Approval_Status_Code,
642 status_verified = decode(X_Status_Verified,
643 'Y', status_verified,
644 X_Status_Verified),
645 actual_flag = X_Actual_Flag,
649 posting_run_id = X_Posting_Run_Id,
646 default_period_name = X_Default_Period_Name,
647 default_effective_date = X_Default_Effective_Date,
648 date_created = X_Date_Created,
650 request_id = X_Request_Id,
651 packet_id = X_Packet_Id,
652 unreservation_packet_id = X_Unreservation_Packet_Id,
653 control_total = X_Control_Total,
654 running_total_dr = X_Running_Total_Dr,
655 running_total_cr = X_Running_Total_Cr,
656 running_total_accounted_dr = X_Running_Total_Accounted_Dr,
657 running_total_accounted_cr = X_Running_Total_Accounted_Cr,
658 average_journal_flag = X_Average_Journal_Flag,
659 last_update_date = X_Last_Update_Date,
660 last_updated_by = X_Last_Updated_By,
661 last_update_login = X_Last_Update_Login
662 WHERE rowid = X_rowid;
663
664 if (SQL%NOTFOUND) then
665 RAISE NO_DATA_FOUND;
666 end if;
667
668 END Update_Row;
669
670 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
671 X_Je_Batch_Id IN OUT NOCOPY NUMBER,
672 X_Last_Update_Date DATE,
673 X_Last_Updated_By NUMBER,
674 X_Name VARCHAR2,
675 X_Chart_of_Accounts_ID NUMBER,
676 X_Period_Set_Name VARCHAR2,
677 X_Accounted_Period_Type VARCHAR2,
678 X_Status VARCHAR2,
679 X_Status_Verified VARCHAR2,
680 X_Actual_Flag VARCHAR2,
681 X_Default_Effective_Date DATE,
682 X_Creation_Date DATE,
683 X_Created_By NUMBER,
684 X_Last_Update_Login NUMBER,
685 X_Status_Reset_Flag VARCHAR2,
686 X_Default_Period_Name VARCHAR2,
687 X_Unique_Date VARCHAR2,
688 X_Earliest_Postable_Date DATE,
689 X_Posted_Date DATE,
690 X_Date_Created DATE,
691 X_Description VARCHAR2,
692 X_Control_Total NUMBER,
693 X_Running_Total_Dr NUMBER,
694 X_Running_Total_Cr NUMBER,
695 X_Running_Total_Accounted_Dr NUMBER,
696 X_Running_Total_Accounted_Cr NUMBER,
697 X_Average_Journal_Flag VARCHAR2,
698 X_Org_Id NUMBER,
699 X_Attribute1 VARCHAR2,
700 X_Attribute2 VARCHAR2,
701 X_Attribute3 VARCHAR2,
702 X_Attribute4 VARCHAR2,
703 X_Attribute5 VARCHAR2,
704 X_Attribute6 VARCHAR2,
705 X_Attribute7 VARCHAR2,
706 X_Attribute8 VARCHAR2,
707 X_Attribute9 VARCHAR2,
708 X_Attribute10 VARCHAR2,
709 X_Context VARCHAR2,
710 X_Budgetary_Control_Status VARCHAR2,
711 X_Approval_Status_Code VARCHAR2,
712 X_Posting_Run_Id NUMBER,
713 X_Request_Id NUMBER,
714 X_Packet_Id NUMBER,
715 X_Ussgl_Transaction_Code VARCHAR2,
716 X_Context2 VARCHAR2,
717 X_Unreservation_Packet_Id NUMBER,
718 X_Global_Attribute_Category VARCHAR2,
719 X_Global_Attribute1 VARCHAR2,
720 X_Global_Attribute2 VARCHAR2,
721 X_Global_Attribute3 VARCHAR2,
722 X_Global_Attribute4 VARCHAR2,
723 X_Global_Attribute5 VARCHAR2,
724 X_Global_Attribute6 VARCHAR2,
725 X_Global_Attribute7 VARCHAR2,
726 X_Global_Attribute8 VARCHAR2,
727 X_Global_Attribute9 VARCHAR2,
728 X_Global_Attribute10 VARCHAR2,
729 X_Global_Attribute11 VARCHAR2,
730 X_Global_Attribute12 VARCHAR2,
731 X_Global_Attribute13 VARCHAR2,
732 X_Global_Attribute14 VARCHAR2,
733 X_Global_Attribute15 VARCHAR2,
734 X_Global_Attribute16 VARCHAR2,
735 X_Global_Attribute17 VARCHAR2,
736 X_Global_Attribute18 VARCHAR2,
737 X_Global_Attribute19 VARCHAR2,
738 X_Global_Attribute20 VARCHAR2
739 ) IS
743 has_je VARCHAR2(1);
740 CURSOR C IS SELECT rowid FROM GL_JE_BATCHES
741
742 WHERE je_batch_id = X_Je_Batch_Id;
744 BEGIN
745
746 -- Make sure all batches have at least one journal.
747 has_je := 'N';
748 IF (X_Je_Batch_Id IS NOT NULL) THEN
749 BEGIN
750
751 SELECT 'Y'
752 INTO has_je
753 FROM gl_je_headers
754 WHERE je_batch_id = X_Je_Batch_Id
755 AND rownum = 1;
756 EXCEPTION
757 WHEN NO_DATA_FOUND THEN
758 has_je := 'N';
759 END;
760 END IF;
761
762 IF (has_je = 'N') THEN
763 fnd_message.set_name('SQLGL', 'GL_JE_BATCH_W_NO_JOURNALS');
764 app_exception.raise_exception;
765 END IF;
766
767 INSERT INTO GL_JE_BATCHES(
768 je_batch_id,
769 last_update_date,
770 last_updated_by,
771 name,
772 chart_of_accounts_id,
773 period_set_name,
774 accounted_period_type,
775 status,
776 status_verified,
777 actual_flag,
778 default_effective_date,
779 creation_date,
780 created_by,
781 last_update_login,
782 status_reset_flag,
783 default_period_name,
784 unique_date,
785 earliest_postable_date,
786 posted_date,
787 date_created,
788 description,
789 control_total,
790 running_total_dr,
791 running_total_cr,
792 running_total_accounted_dr,
793 running_total_accounted_cr,
794 average_journal_flag,
795 org_id,
796 attribute1,
797 attribute2,
798 attribute3,
799 attribute4,
800 attribute5,
801 attribute6,
802 attribute7,
803 attribute8,
804 attribute9,
805 attribute10,
806 context,
807 budgetary_control_status,
808 approval_status_code,
809 posting_run_id,
810 request_id,
811 packet_id,
812 ussgl_transaction_code,
813 context2,
814 unreservation_packet_id,
815 Global_Attribute_Category,
816 Global_Attribute1,
817 Global_Attribute2,
818 Global_Attribute3,
819 Global_Attribute4,
820 Global_Attribute5,
821 Global_Attribute6,
822 Global_Attribute7,
823 Global_Attribute8,
824 Global_Attribute9,
825 Global_Attribute10,
826 Global_Attribute11,
827 Global_Attribute12,
828 Global_Attribute13,
829 Global_Attribute14,
830 Global_Attribute15,
831 Global_Attribute16,
832 Global_Attribute17,
833 Global_Attribute18,
834 Global_Attribute19,
835 Global_Attribute20
836 ) VALUES (
837 X_Je_Batch_Id,
838 X_Last_Update_Date,
839 X_Last_Updated_By,
840 X_Name,
841 X_chart_of_accounts_id,
842 X_period_set_name,
843 X_accounted_period_type,
844 X_Status,
845 X_Status_Verified,
846 X_Actual_Flag,
847 X_Default_Effective_Date,
848 X_Creation_Date,
849 X_Created_By,
850 X_Last_Update_Login,
851 X_Status_Reset_Flag,
852 X_Default_Period_Name,
853 X_Unique_Date,
854 X_Earliest_Postable_Date,
855 X_Posted_Date,
856 X_Date_Created,
857 X_Description,
858 X_Control_Total,
859 X_Running_Total_Dr,
860 X_Running_Total_Cr,
861 X_Running_Total_Accounted_Dr,
862 X_Running_Total_Accounted_Cr,
863 X_Average_Journal_Flag,
864 X_Org_Id,
865 X_Attribute1,
866 X_Attribute2,
867 X_Attribute3,
868 X_Attribute4,
869 X_Attribute5,
870 X_Attribute6,
871 X_Attribute7,
872 X_Attribute8,
873 X_Attribute9,
874 X_Attribute10,
875 X_Context,
876 X_Budgetary_Control_Status,
877 X_Approval_Status_Code,
878 X_Posting_Run_Id,
879 X_Request_Id,
880 X_Packet_Id,
881 X_Ussgl_Transaction_Code,
882 X_Context2,
883 X_Unreservation_Packet_Id,
884 X_Global_Attribute_Category,
885 X_Global_Attribute1,
886 X_Global_Attribute2,
887 X_Global_Attribute3,
888 X_Global_Attribute4,
889 X_Global_Attribute5,
890 X_Global_Attribute6,
891 X_Global_Attribute7,
892 X_Global_Attribute8,
893 X_Global_Attribute9,
894 X_Global_Attribute10,
895 X_Global_Attribute11,
896 X_Global_Attribute12,
897 X_Global_Attribute13,
898 X_Global_Attribute14,
899 X_Global_Attribute15,
900 X_Global_Attribute16,
901 X_Global_Attribute17,
902 X_Global_Attribute18,
903 X_Global_Attribute19,
904 X_Global_Attribute20
905 );
906
907 OPEN C;
908 FETCH C INTO X_Rowid;
909 if (C%NOTFOUND) then
910 CLOSE C;
911 RAISE NO_DATA_FOUND;
912 end if;
913 CLOSE C;
914 END Insert_Row;
915
916
917 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
918
919 X_Je_Batch_Id NUMBER,
920 X_Name VARCHAR2,
921 X_Chart_of_Accounts_ID NUMBER,
925 X_Status_Verified VARCHAR2,
922 X_Period_Set_Name VARCHAR2,
923 X_Accounted_Period_Type VARCHAR2,
924 X_Status VARCHAR2,
926 X_Actual_Flag VARCHAR2,
927 X_Default_Effective_Date DATE,
928 X_Status_Reset_Flag VARCHAR2,
929 X_Default_Period_Name VARCHAR2,
930 X_Unique_Date VARCHAR2,
931 X_Earliest_Postable_Date DATE,
932 X_Posted_Date DATE,
933 X_Date_Created DATE,
934 X_Description VARCHAR2,
935 X_Control_Total NUMBER,
936 X_Running_Total_Dr NUMBER,
937 X_Running_Total_Cr NUMBER,
938 X_Running_Total_Accounted_Dr NUMBER,
939 X_Running_Total_Accounted_Cr NUMBER,
940 X_Average_Journal_Flag VARCHAR2,
941 X_Attribute1 VARCHAR2,
942 X_Attribute2 VARCHAR2,
943 X_Attribute3 VARCHAR2,
944 X_Attribute4 VARCHAR2,
945 X_Attribute5 VARCHAR2,
946 X_Attribute6 VARCHAR2,
947 X_Attribute7 VARCHAR2,
948 X_Attribute8 VARCHAR2,
949 X_Attribute9 VARCHAR2,
950 X_Attribute10 VARCHAR2,
951 X_Context VARCHAR2,
952 X_Budgetary_Control_Status VARCHAR2,
953 X_Approval_Status_Code VARCHAR2,
954 X_Posting_Run_Id NUMBER,
955 X_Request_Id NUMBER,
956 X_Packet_Id NUMBER,
957 X_Ussgl_Transaction_Code VARCHAR2,
958 X_Context2 VARCHAR2,
959 X_Unreservation_Packet_Id NUMBER,
960 X_Verify_Request_Completed VARCHAR2,
961 X_Global_Attribute_Category VARCHAR2,
962 X_Global_Attribute1 VARCHAR2,
963 X_Global_Attribute2 VARCHAR2,
964 X_Global_Attribute3 VARCHAR2,
965 X_Global_Attribute4 VARCHAR2,
966 X_Global_Attribute5 VARCHAR2,
967 X_Global_Attribute6 VARCHAR2,
968 X_Global_Attribute7 VARCHAR2,
969 X_Global_Attribute8 VARCHAR2,
970 X_Global_Attribute9 VARCHAR2,
971 X_Global_Attribute10 VARCHAR2,
972 X_Global_Attribute11 VARCHAR2,
973 X_Global_Attribute12 VARCHAR2,
974 X_Global_Attribute13 VARCHAR2,
975 X_Global_Attribute14 VARCHAR2,
976 X_Global_Attribute15 VARCHAR2,
977 X_Global_Attribute16 VARCHAR2,
978 X_Global_Attribute17 VARCHAR2,
979 X_Global_Attribute18 VARCHAR2,
980 X_Global_Attribute19 VARCHAR2,
981 X_Global_Attribute20 VARCHAR2
982 ) IS
983 CURSOR C IS
984 SELECT *
985 FROM GL_JE_BATCHES
986 WHERE rowid = X_Rowid
987 FOR UPDATE of Je_Batch_Id NOWAIT;
988 Recinfo C%ROWTYPE;
989 dev_request_phase VARCHAR2(30);
990 BEGIN
991 OPEN C;
992 FETCH C INTO Recinfo;
993 if (C%NOTFOUND) then
994 CLOSE C;
995 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
996 APP_EXCEPTION.RAISE_EXCEPTION;
997 end if;
998 CLOSE C;
999
1000
1001 if (
1002 ( (Recinfo.je_batch_id = X_Je_Batch_Id)
1003 OR ( (Recinfo.je_batch_id IS NULL)
1004 AND (X_Je_Batch_Id IS NULL)))
1005 AND ( (Recinfo.name = X_Name)
1006 OR ( (Recinfo.name IS NULL)
1007 AND (X_Name IS NULL)))
1008 AND (Recinfo.chart_of_accounts_id = X_Chart_of_Accounts_id)
1009 AND (Recinfo.period_set_name = X_Period_Set_Name)
1010 AND (Recinfo.accounted_period_type = X_Accounted_Period_Type)
1011 AND ( (Recinfo.status = X_Status)
1012 OR ( (Recinfo.status IS NULL)
1013 AND (X_Status IS NULL)))
1014 AND ( (Recinfo.status_verified = X_Status_Verified)
1015 OR ( (Recinfo.status_verified IS NULL)
1016 AND (X_Status_Verified IS NULL)))
1017 AND ( (Recinfo.actual_flag = X_Actual_Flag)
1018 OR ( (Recinfo.actual_flag IS NULL)
1019 AND (X_Actual_Flag IS NULL)))
1020 AND ( (Recinfo.default_effective_date = X_Default_Effective_Date)
1021 OR ( (Recinfo.default_effective_date IS NULL)
1022 AND (X_Default_Effective_Date IS NULL)))
1023 AND ( (Recinfo.status_reset_flag = X_Status_Reset_Flag)
1024 OR ( (Recinfo.status_reset_flag IS NULL)
1025 AND (X_Status_Reset_Flag IS NULL)))
1026 AND ( (Recinfo.default_period_name = X_Default_Period_Name)
1030 OR ( (Recinfo.unique_date IS NULL)
1027 OR ( (Recinfo.default_period_name IS NULL)
1028 AND (X_Default_Period_Name IS NULL)))
1029 AND ( (Recinfo.unique_date = X_Unique_Date)
1031 AND (X_Unique_Date IS NULL)))
1032 AND ( (Recinfo.earliest_postable_date = X_Earliest_Postable_Date)
1033 OR ( (Recinfo.earliest_postable_date IS NULL)
1034 AND (X_Earliest_Postable_Date IS NULL)))
1035 AND ( (trunc(Recinfo.posted_date) = trunc(X_Posted_Date))
1036 OR ( (Recinfo.posted_date IS NULL)
1037 AND (X_Posted_Date IS NULL)))
1038 AND ( (trunc(Recinfo.date_created) = trunc(X_Date_Created))
1039 OR ( (Recinfo.date_created IS NULL)
1040 AND (X_Date_Created IS NULL)))
1041 AND ( (Recinfo.description = X_Description)
1042 OR ( (Recinfo.description IS NULL)
1043 AND (X_Description IS NULL)))
1044 AND ( (Recinfo.control_total = X_Control_Total)
1045 OR ( (Recinfo.control_total IS NULL)
1046 AND (X_Control_Total IS NULL)))
1047 AND ( (Recinfo.running_total_dr = X_Running_Total_Dr)
1048 OR ( (Recinfo.running_total_dr IS NULL)
1049 AND (X_Running_Total_Dr IS NULL)))
1050 AND ( (Recinfo.running_total_cr = X_Running_Total_Cr)
1051 OR ( (Recinfo.running_total_cr IS NULL)
1052 AND (X_Running_Total_Cr IS NULL)))
1053 AND ( (Recinfo.running_total_accounted_dr = X_Running_Total_Accounted_Dr)
1054 OR ( (Recinfo.running_total_accounted_dr IS NULL)
1055 AND (X_Running_Total_Accounted_Dr IS NULL)))
1056 AND ( (Recinfo.running_total_accounted_cr = X_Running_Total_Accounted_Cr)
1057 OR ( (Recinfo.running_total_accounted_cr IS NULL)
1058 AND (X_Running_Total_Accounted_Cr IS NULL)))
1059 AND ( (Recinfo.average_journal_flag = X_Average_Journal_Flag)
1060 OR ( (Recinfo.average_journal_flag IS NULL)
1061 AND (X_Average_Journal_Flag IS NULL)))
1062 AND ( (Recinfo.attribute1 = X_Attribute1)
1063 OR ( (rtrim(Recinfo.attribute1,' ') IS NULL)
1064 AND (X_Attribute1 IS NULL)))
1065 AND ( (Recinfo.attribute2 = X_Attribute2)
1066 OR ( (rtrim(Recinfo.attribute2,' ') IS NULL)
1067 AND (X_Attribute2 IS NULL)))
1068 AND ( (Recinfo.attribute3 = X_Attribute3)
1069 OR ( (rtrim(Recinfo.attribute3,' ') IS NULL)
1070 AND (X_Attribute3 IS NULL)))
1071 AND ( (Recinfo.attribute4 = X_Attribute4)
1072 OR ( (rtrim(Recinfo.attribute4,' ') IS NULL)
1073 AND (X_Attribute4 IS NULL)))
1074 AND ( (Recinfo.attribute5 = X_Attribute5)
1075 OR ( (rtrim(Recinfo.attribute5,' ') IS NULL)
1076 AND (X_Attribute5 IS NULL)))
1077 AND ( (Recinfo.attribute6 = X_Attribute6)
1078 OR ( (rtrim(Recinfo.attribute6,' ') IS NULL)
1079 AND (X_Attribute6 IS NULL)))
1080 AND ( (Recinfo.attribute7 = X_Attribute7)
1081 OR ( (rtrim(Recinfo.attribute7,' ') IS NULL)
1082 AND (X_Attribute7 IS NULL)))
1083 AND ( (Recinfo.attribute8 = X_Attribute8)
1084 OR ( (rtrim(Recinfo.attribute8,' ') IS NULL)
1085 AND (X_Attribute8 IS NULL)))
1086 AND ( (Recinfo.attribute9 = X_Attribute9)
1087 OR ( (rtrim(Recinfo.attribute9,' ') IS NULL)
1088 AND (X_Attribute9 IS NULL)))
1089 AND ( (Recinfo.attribute10 = X_Attribute10)
1090 OR ( (rtrim(Recinfo.attribute10,' ') IS NULL)
1091 AND (X_Attribute10 IS NULL)))
1092 AND ( (Recinfo.context = X_Context)
1093 OR ( (rtrim(Recinfo.context,' ') IS NULL)
1094 AND (X_Context IS NULL)))
1095 AND ( (Recinfo.budgetary_control_status = X_Budgetary_Control_Status)
1096 OR ( (Recinfo.budgetary_control_status IS NULL)
1097 AND (X_Budgetary_Control_Status IS NULL)))
1098 AND ( (Recinfo.approval_status_code = X_Approval_Status_Code)
1099 OR ( (Recinfo.approval_status_code IS NULL)
1100 AND (X_Approval_Status_Code IS NULL)))
1101 AND ( (Recinfo.posting_run_id = X_Posting_Run_Id)
1102 OR ( (Recinfo.posting_run_id IS NULL)
1103 AND (X_Posting_Run_Id IS NULL)))
1104 AND ( (Recinfo.request_id = X_Request_Id)
1105 OR ( (Recinfo.request_id IS NULL)
1106 AND (X_Request_Id IS NULL)))
1107 AND ( (Recinfo.packet_id = X_Packet_Id)
1108 OR ( (Recinfo.packet_id IS NULL)
1109 AND (X_Packet_Id IS NULL)))
1110 AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
1111 OR ( (Recinfo.ussgl_transaction_code IS NULL)
1112 AND (X_Ussgl_Transaction_Code IS NULL)))
1113 AND ( (Recinfo.context2 = X_Context2)
1114 OR ( (Recinfo.context2 IS NULL)
1115 AND (X_Context2 IS NULL)))
1116 AND ( (Recinfo.unreservation_packet_id = X_Unreservation_Packet_Id)
1117 OR ( (Recinfo.unreservation_packet_id IS NULL)
1118 AND (X_Unreservation_Packet_Id IS NULL)))
1119 AND ( (Recinfo.global_attribute_category = X_Global_Attribute_Category)
1120 OR ( (Recinfo.global_attribute_category IS NULL)
1121 AND (X_Global_Attribute_Category IS NULL)))
1122 AND ( (Recinfo.global_attribute1 = X_Global_Attribute1)
1123 OR ( (Recinfo.global_attribute1 IS NULL)
1124 AND (X_Global_Attribute1 IS NULL)))
1125 AND ( (Recinfo.global_attribute2 = X_Global_Attribute2)
1126 OR ( (Recinfo.global_attribute2 IS NULL)
1127 AND (X_Global_Attribute2 IS NULL)))
1128 AND ( (Recinfo.global_attribute3 = X_Global_Attribute3)
1129 OR ( (Recinfo.global_attribute3 IS NULL)
1130 AND (X_Global_Attribute3 IS NULL)))
1134 AND ( (Recinfo.global_attribute5 = X_Global_Attribute5)
1131 AND ( (Recinfo.global_attribute4 = X_Global_Attribute4)
1132 OR ( (Recinfo.global_attribute4 IS NULL)
1133 AND (X_Global_Attribute4 IS NULL)))
1135 OR ( (Recinfo.global_attribute5 IS NULL)
1136 AND (X_Global_Attribute5 IS NULL)))
1137 AND ( (Recinfo.global_attribute6 = X_Global_Attribute6)
1138 OR ( (Recinfo.global_attribute6 IS NULL)
1139 AND (X_Global_Attribute6 IS NULL)))
1140 AND ( (Recinfo.global_attribute7 = X_Global_Attribute7)
1141 OR ( (Recinfo.global_attribute7 IS NULL)
1142 AND (X_Global_Attribute7 IS NULL)))
1143 AND ( (Recinfo.global_attribute8 = X_Global_Attribute8)
1144 OR ( (Recinfo.global_attribute8 IS NULL)
1145 AND (X_Global_Attribute8 IS NULL)))
1146 AND ( (Recinfo.global_attribute9 = X_Global_Attribute9)
1147 OR ( (Recinfo.global_attribute9 IS NULL)
1148 AND (X_Global_Attribute9 IS NULL)))
1149 AND ( (Recinfo.global_attribute10 = X_Global_Attribute10)
1150 OR ( (Recinfo.global_attribute10 IS NULL)
1151 AND (X_Global_Attribute10 IS NULL)))
1152 AND ( (Recinfo.global_attribute11 = X_Global_Attribute11)
1153 OR ( (Recinfo.global_attribute11 IS NULL)
1154 AND (X_Global_Attribute11 IS NULL)))
1155 AND ( (Recinfo.global_attribute12 = X_Global_Attribute12)
1156 OR ( (Recinfo.global_attribute12 IS NULL)
1157 AND (X_Global_Attribute12 IS NULL)))
1158 AND ( (Recinfo.global_attribute13 = X_Global_Attribute13)
1159 OR ( (Recinfo.global_attribute13 IS NULL)
1160 AND (X_Global_Attribute13 IS NULL)))
1161 AND ( (Recinfo.global_attribute14 = X_Global_Attribute14)
1162 OR ( (Recinfo.global_attribute14 IS NULL)
1163 AND (X_Global_Attribute14 IS NULL)))
1164 AND ( (Recinfo.global_attribute15 = X_Global_Attribute15)
1165 OR ( (Recinfo.global_attribute15 IS NULL)
1166 AND (X_Global_Attribute15 IS NULL)))
1167 AND ( (Recinfo.global_attribute16 = X_Global_Attribute16)
1168 OR ( (Recinfo.global_attribute16 IS NULL)
1169 AND (X_Global_Attribute16 IS NULL)))
1170 AND ( (Recinfo.global_attribute17 = X_Global_Attribute17)
1171 OR ( (Recinfo.global_attribute17 IS NULL)
1172 AND (X_Global_Attribute17 IS NULL)))
1173 AND ( (Recinfo.global_attribute18 = X_Global_Attribute18)
1174 OR ( (Recinfo.global_attribute18 IS NULL)
1175 AND (X_Global_Attribute18 IS NULL)))
1176 AND ( (Recinfo.global_attribute19 = X_Global_Attribute19)
1177 OR ( (Recinfo.global_attribute19 IS NULL)
1178 AND (X_Global_Attribute19 IS NULL)))
1179 AND ( (Recinfo.global_attribute20 = X_Global_Attribute20)
1180 OR ( (Recinfo.global_attribute20 IS NULL)
1181 AND (X_Global_Attribute20 IS NULL)))
1182 ) then
1183 -- If the batch status indicates that it is being processed,
1184 -- check to verify that it is actually still being processed.
1185 IF (X_Status IN ('I', 'S')) THEN
1186 -- If the user has already attempted to post this batch,
1187 -- then get information about the results
1188 IF (X_Request_Id IS NOT NULL) THEN
1189 DECLARE
1190 call_status BOOLEAN;
1191 request_phase VARCHAR2(30);
1192 request_status VARCHAR2(30);
1193 dev_request_status VARCHAR2(30);
1194 request_status_mesg VARCHAR2(255);
1195 request_id NUMBER;
1196 BEGIN
1197 request_id := X_Request_id;
1198 call_status :=
1199 fnd_concurrent.get_request_status(
1200 request_id,
1201 'SQLGL',
1202 'GLPPOS',
1203 request_phase,
1204 request_status,
1205 dev_request_phase,
1206 dev_request_status,
1207 request_status_mesg );
1208 END;
1209 END IF;
1210 IF (nvl(dev_request_phase, 'COMPLETE') <> 'COMPLETE') THEN
1211 FND_MESSAGE.set_name('SQLGL', 'GL_MJE_BATCH_BEING_PROCESSED');
1212 app_exception.raise_exception;
1213 END IF;
1214 END IF;
1215 else
1216 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1217 APP_EXCEPTION.RAISE_EXCEPTION;
1218 end if;
1219 END Lock_Row;
1220
1221 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1222 X_Je_Batch_Id NUMBER,
1223 X_Last_Update_Date DATE,
1224 X_Last_Updated_By NUMBER,
1225 X_Name VARCHAR2,
1226 X_Chart_of_Accounts_ID NUMBER,
1227 X_Period_Set_Name VARCHAR2,
1228 X_Accounted_Period_Type VARCHAR2,
1229 X_Status VARCHAR2,
1230 X_Status_Verified VARCHAR2,
1231 X_Actual_Flag VARCHAR2,
1232 X_Default_Effective_Date DATE,
1233 X_Last_Update_Login NUMBER,
1234 X_Status_Reset_Flag VARCHAR2,
1235 X_Default_Period_Name VARCHAR2,
1236 X_Unique_Date VARCHAR2,
1237 X_Earliest_Postable_Date DATE,
1238 X_Posted_Date DATE,
1239 X_Date_Created DATE,
1240 X_Description VARCHAR2,
1241 X_Control_Total NUMBER,
1242 X_Running_Total_Dr NUMBER,
1246 X_Average_Journal_Flag VARCHAR2,
1243 X_Running_Total_Cr NUMBER,
1244 X_Running_Total_Accounted_Dr NUMBER,
1245 X_Running_Total_Accounted_Cr NUMBER,
1247 X_Attribute1 VARCHAR2,
1248 X_Attribute2 VARCHAR2,
1249 X_Attribute3 VARCHAR2,
1250 X_Attribute4 VARCHAR2,
1251 X_Attribute5 VARCHAR2,
1252 X_Attribute6 VARCHAR2,
1253 X_Attribute7 VARCHAR2,
1254 X_Attribute8 VARCHAR2,
1255 X_Attribute9 VARCHAR2,
1256 X_Attribute10 VARCHAR2,
1257 X_Context VARCHAR2,
1258 X_Budgetary_Control_Status VARCHAR2,
1259 X_Approval_Status_Code IN OUT NOCOPY VARCHAR2,
1260 X_Posting_Run_Id NUMBER,
1261 X_Request_Id NUMBER,
1262 X_Packet_Id NUMBER,
1263 X_Ussgl_Transaction_Code VARCHAR2,
1264 X_Context2 VARCHAR2,
1265 X_Unreservation_Packet_Id NUMBER,
1266 Update_Effective_Date_Flag VARCHAR2,
1267 Update_Approval_Stat_Flag VARCHAR2,
1268 X_Global_Attribute_Category VARCHAR2,
1269 X_Global_Attribute1 VARCHAR2,
1270 X_Global_Attribute2 VARCHAR2,
1271 X_Global_Attribute3 VARCHAR2,
1272 X_Global_Attribute4 VARCHAR2,
1273 X_Global_Attribute5 VARCHAR2,
1274 X_Global_Attribute6 VARCHAR2,
1275 X_Global_Attribute7 VARCHAR2,
1276 X_Global_Attribute8 VARCHAR2,
1277 X_Global_Attribute9 VARCHAR2,
1278 X_Global_Attribute10 VARCHAR2,
1279 X_Global_Attribute11 VARCHAR2,
1280 X_Global_Attribute12 VARCHAR2,
1281 X_Global_Attribute13 VARCHAR2,
1282 X_Global_Attribute14 VARCHAR2,
1283 X_Global_Attribute15 VARCHAR2,
1284 X_Global_Attribute16 VARCHAR2,
1285 X_Global_Attribute17 VARCHAR2,
1286 X_Global_Attribute18 VARCHAR2,
1287 X_Global_Attribute19 VARCHAR2,
1288 X_Global_Attribute20 VARCHAR2
1289 ) IS
1290 current_average_journal VARCHAR2(1);
1291 has_je VARCHAR2(1);
1292 BEGIN
1293
1294 -- Make sure all batches have at least one journal.
1295 has_je := 'N';
1296 IF (X_Je_Batch_Id IS NOT NULL) THEN
1297 BEGIN
1298
1299 SELECT 'Y'
1300 INTO has_je
1301 FROM gl_je_headers
1302 WHERE je_batch_id = X_Je_Batch_Id
1303 AND rownum = 1;
1304 EXCEPTION
1305 WHEN NO_DATA_FOUND THEN
1306 has_je := 'N';
1307 END;
1308 END IF;
1309
1310 IF (has_je = 'N') THEN
1311 fnd_message.set_name('SQLGL', 'GL_JE_BATCH_W_NO_JOURNALS');
1312 app_exception.raise_exception;
1313 END IF;
1314
1315 -- If the user changes the average journal flag to 'Y', then
1316 -- we need to reinitialize all of the journals effective dates.
1317 IF (Update_Effective_Date_Flag = 'Y') THEN
1318 GL_JE_HEADERS_PKG.change_effective_date(X_Je_Batch_Id,
1319 X_Default_Effective_Date);
1320 END IF;
1321
1322 -- If the user starts the approval process, then we need to
1323 -- refetch the approval status
1324 IF (Update_Approval_Stat_Flag = 'Y') THEN
1325 SELECT approval_status_code
1326 INTO X_Approval_Status_Code
1327 FROM gl_je_batches
1328 WHERE rowid = X_RowId;
1329
1330 -- If a journal has been deleted, we may need to reset the
1331 -- approval required flag. Check.
1332 ELSIF (Update_Approval_Stat_Flag = 'D') THEN
1333 -- If a journal was deleted, the batch shouldn't have been
1334 -- posted or approved, but check anyway.
1335 IF ( (X_status <> 'P')
1336 AND (X_Approval_Status_Code <> 'A')
1337 ) THEN
1338 IF (gl_je_batches_pkg.needs_approval(X_Je_Batch_Id)) THEN
1339 IF (X_Approval_Status_Code = 'Z') THEN
1340 X_Approval_Status_Code := 'R';
1341 END IF;
1342 ELSE
1343 X_Approval_Status_Code := 'Z';
1344 END IF;
1345 END IF;
1346 END IF;
1347
1348 UPDATE GL_JE_BATCHES
1349 SET
1350
1351 je_batch_id = X_Je_Batch_Id,
1352 last_update_date = X_Last_Update_Date,
1353 last_updated_by = X_Last_Updated_By,
1354 name = X_Name,
1355 chart_of_accounts_id = X_Chart_of_Accounts_id,
1356 period_set_name = X_Period_Set_Name,
1357 accounted_period_type = X_Accounted_Period_Type,
1358 status = X_Status,
1359 status_verified = X_Status_Verified,
1360 actual_flag = X_Actual_Flag,
1364 default_period_name = X_Default_Period_Name,
1361 default_effective_date = X_Default_Effective_Date,
1362 last_update_login = X_Last_Update_Login,
1363 status_reset_flag = X_Status_Reset_Flag,
1365 unique_date = X_Unique_Date,
1366 earliest_postable_date = X_Earliest_Postable_Date,
1367 date_created = X_Date_Created,
1368 description = X_Description,
1369 control_total = X_Control_Total,
1370 running_total_dr = X_Running_Total_Dr,
1371 running_total_cr = X_Running_Total_Cr,
1372 running_total_accounted_dr = X_Running_Total_Accounted_Dr,
1373 running_total_accounted_cr = X_Running_Total_Accounted_Cr,
1374 average_journal_flag = X_Average_Journal_Flag,
1375 attribute1 = X_Attribute1,
1376 attribute2 = X_Attribute2,
1377 attribute3 = X_Attribute3,
1378 attribute4 = X_Attribute4,
1379 attribute5 = X_Attribute5,
1380 attribute6 = X_Attribute6,
1381 attribute7 = X_Attribute7,
1382 attribute8 = X_Attribute8,
1383 attribute9 = X_Attribute9,
1384 attribute10 = X_Attribute10,
1385 context = X_Context,
1386 budgetary_control_status = X_Budgetary_Control_Status,
1387 approval_status_code = X_Approval_Status_Code,
1388 posting_run_id = X_Posting_Run_Id,
1389 request_id = X_Request_Id,
1390 packet_id = X_Packet_Id,
1391 ussgl_transaction_code = X_Ussgl_Transaction_Code,
1392 context2 = X_Context2,
1393 unreservation_packet_id = X_Unreservation_Packet_Id,
1394 global_attribute_category = X_Global_Attribute_Category,
1395 global_attribute1 = X_Global_Attribute1,
1396 global_attribute2 = X_Global_Attribute2,
1397 global_attribute3 = X_Global_Attribute3,
1398 global_attribute4 = X_Global_Attribute4,
1399 global_attribute5 = X_Global_Attribute5,
1400 global_attribute6 = X_Global_Attribute6,
1401 global_attribute7 = X_Global_Attribute7,
1402 global_attribute8 = X_Global_Attribute8,
1403 global_attribute9 = X_Global_Attribute9,
1404 global_attribute10 = X_Global_Attribute10,
1405 global_attribute11 = X_Global_Attribute11,
1406 global_attribute12 = X_Global_Attribute12,
1407 global_attribute13 = X_Global_Attribute13,
1408 global_attribute14 = X_Global_Attribute14,
1409 global_attribute15 = X_Global_Attribute15,
1410 global_attribute16 = X_Global_Attribute16,
1411 global_attribute17 = X_Global_Attribute17,
1412 global_attribute18 = X_Global_Attribute18,
1413 global_attribute19 = X_Global_Attribute19,
1414 global_attribute20 = X_Global_Attribute20
1415 WHERE rowid = X_rowid;
1416
1417 if (SQL%NOTFOUND) then
1418 RAISE NO_DATA_FOUND;
1419 end if;
1420
1421 END Update_Row;
1422
1423 PROCEDURE Delete_Row(X_Rowid VARCHAR2, X_Je_Batch_Id NUMBER) IS
1424 bc_status VARCHAR2(1);
1425 approval_status VARCHAR2(1);
1426 batch_status VARCHAR2(1);
1427 request_id NUMBER;
1428 dev_request_phase VARCHAR2(30);
1429
1430 CURSOR get_child IS
1431 SELECT gb.rowid,gb.je_batch_id
1432 FROM gl_je_headers gh, gl_je_batches gb
1433 WHERE gb.je_batch_id = gh.je_batch_id
1434 AND gh.parent_je_header_id IN
1435 (select je_header_id
1436 from gl_je_headers
1437 where je_batch_id = X_Je_Batch_Id)
1438 AND gh.reversed_je_header_id IS NOT NULL
1439 AND gb.status <> 'P'
1440 AND gb.je_batch_id <> X_Je_Batch_Id;
1441
1442 l_je_batch_id NUMBER;
1443 l_rowid VARCHAR2(18);
1444 BEGIN
1445 SELECT budgetary_control_status,
1446 approval_status_code,
1447 status,
1448 request_id
1449 INTO bc_status, approval_status,
1450 batch_status, request_id
1451 FROM gl_je_batches
1452 WHERE rowid = X_Rowid;
1453
1454 -- Check if we are in the process of reserving funds for
1455 -- this batch
1456 IF (bc_status = 'I') THEN
1457 RAISE GL_MJE_RESERVING_FUNDS;
1458 END IF;
1459
1460 -- Check if we are in the process of reserving funds for
1461 -- this batch
1462 IF (bc_status = 'P') THEN
1463 RAISE GL_MJE_RESERVED_FUNDS;
1464 END IF;
1465
1466 -- Check if we are in the process of approving this batch
1467 IF (approval_status = 'I') THEN
1468 RAISE GL_MJE_APPROVING;
1469 END IF;
1470
1471 -- Check if we have posted this batch
1472 IF (batch_status = 'P') THEN
1473 RAISE GL_MJE_POSTED;
1474 END IF;
1475
1476 -- If the batch status indicates that it is being processed,
1477 -- check to verify that it is actually still being processed.
1478 IF (batch_status IN ('I', 'S')) THEN
1479 -- If the user has already attempted to post this batch,
1480 -- then get information about the results
1481 IF (request_id IS NOT NULL) THEN
1482 DECLARE
1483 call_status BOOLEAN;
1484 request_phase VARCHAR2(30);
1485 request_status VARCHAR2(30);
1486 dev_request_status VARCHAR2(30);
1487 request_status_mesg VARCHAR2(255);
1488 req_id NUMBER;
1489 BEGIN
1490 req_id := request_id;
1491 call_status :=
1492 fnd_concurrent.get_request_status(
1493 req_id,
1494 'SQLGL',
1495 'GLPPOS',
1496 request_phase,
1497 request_status,
1498 dev_request_phase,
1499 dev_request_status,
1500 request_status_mesg );
1501 END;
1502
1503 IF (dev_request_phase <> 'COMPLETE') THEN
1504 RAISE GL_MJE_POSTING;
1505 END IF;
1506
1507 END IF;
1508 END IF;
1509
1510 -- Adding code to delete scondary revresal journal also
1511 OPEN get_child;
1512 LOOP
1513 FETCH get_child INTO l_rowid,l_je_batch_id;
1514 EXIT WHEN get_child%NOTFOUND;
1515
1516 gl_je_batches_pkg.Delete_Row(l_rowid,l_je_batch_id);
1517
1518 END LOOP;
1519 CLOSE get_child;
1520
1521 -- Delete the journals
1522 gl_je_headers_pkg.delete_headers(X_Je_Batch_Id);
1523
1524 -- Delete the batches
1525 DELETE FROM GL_JE_BATCHES
1526 WHERE rowid = X_Rowid;
1527
1528
1529 EXCEPTION
1530 WHEN NO_DATA_FOUND THEN
1531 null;
1532 END Delete_Row;
1533
1534 END gl_je_batches_pkg;