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