1 PACKAGE BODY gl_autopost_pkg AS
2 /* $Header: glijeapb.pls 120.6 2006/05/25 15:24:59 abhjoshi noship $ */
3
4 --
5 -- PRIVATE FUNCTIONS
6 --
7 PROCEDURE Debug_Print_Var(X_Variable IN VARCHAR2,
8 X_Value IN VARCHAR2)
9 IS
10 BEGIN
11 raise_application_error(-20000,X_Variable||' =' ||X_Value);
12 --+ dbms_output.put_line(X_Variable||' = '||X_Value);
13 return;
14 END Debug_Print_Var;
15
16 PROCEDURE Debug_Print_Msg(X_Message IN VARCHAR2)
17 IS
18 BEGIN
19 raise_application_error(-20000, X_Message);
20 --+ dbms_output.put_line(X_Message);
21 return;
22 END Debug_Print_Msg;
23
24 --
25 -- PUBLIC FUNCTIONS
26 --
27
28 --
29 -- Procedure
30 -- Post_Batches
31 -- Purpose
32 -- Post batches based on specified criteria.
33 -- Details
34 -- This API can be used to post batches based on many different criteria:
35 -- batch id, source, category, actual_flag, period_name, batch name ...
36 -- History
37 -- 11-11-01 O Monnier Created
38 -- Arguments
39 -- X_Request_Id The posting request id
40 -- X_Count_Sel_Bat The number of batches selected for posting
41 PROCEDURE Post_Batches(X_Request_Id OUT NOCOPY NUMBER,
42 X_Count_Sel_Bat OUT NOCOPY NUMBER,
43 X_Access_Set_Id IN NUMBER,
44 X_Ledger_Id IN NUMBER,
45 X_Je_Batch_Id IN NUMBER DEFAULT NULL,
46 X_Je_Source_Name IN VARCHAR2 DEFAULT NULL,
47 X_Je_Category_Name IN VARCHAR2 DEFAULT NULL,
48 X_Actual_Flag IN VARCHAR2 DEFAULT NULL,
49 X_Period_Name IN VARCHAR2 DEFAULT NULL,
50 X_From_Day_Before IN NUMBER DEFAULT NULL,
51 X_To_Day_After IN NUMBER DEFAULT NULL,
52 X_Name IN VARCHAR2 DEFAULT NULL,
53 X_Description IN VARCHAR2 DEFAULT NULL,
54 X_Debug_Mode IN BOOLEAN DEFAULT FALSE
55 )
56 IS
57 CURSOR retrieve_ledger_info (p_ledger_id NUMBER) IS
58 SELECT chart_of_accounts_id,
59 enable_budgetary_control_flag,
60 enable_automatic_tax_flag,
61 enable_je_approval_flag,
62 GL_JE_POSTING_S.nextval
63 FROM GL_LEDGERS
64 WHERE ledger_id = p_ledger_id;
65
66 CURSOR single_ledger (p_posting_run_id NUMBER) IS
67 SELECT max(JEH.ledger_id)
68 FROM GL_JE_BATCHES JEB,
69 GL_JE_HEADERS JEH
70 WHERE JEB.status = 'S'
71 AND JEB.posting_run_id = p_posting_run_id
72 AND JEH.je_batch_id = JEB.je_batch_id
73 GROUP BY JEB.posting_run_id
74 HAVING count(distinct JEH.ledger_id) = 1;
75
76 CURSOR alc_exists (p_posting_run_id NUMBER) IS
77 SELECT '1'
78 FROM GL_JE_BATCHES JEB,
79 GL_JE_HEADERS JEH
80 WHERE JEB.status = 'S'
81 AND JEB.posting_run_id = p_posting_run_id
82 AND JEH.je_batch_id = JEB.je_batch_id
83 AND JEH.actual_flag <> 'B'
84 AND JEH.reversed_je_header_id IS NULL
85 AND EXISTS
86 (SELECT 1
87 FROM GL_LEDGER_RELATIONSHIPS LRL
88 WHERE LRL.source_ledger_id = JEH.ledger_id
89 AND LRL.target_ledger_category_code = 'ALC'
90 AND LRL.relationship_type_code IN ('JOURNAL', 'SUBLEDGER')
91 AND LRL.application_id = 101
92 AND LRL.relationship_enabled_flag = 'Y'
93 AND JEH.je_source NOT IN
94 (SELECT INC.je_source_name
95 FROM GL_JE_INCLUSION_RULES INC
96 WHERE INC.je_rule_set_id =
97 LRL.gl_je_conversion_set_id
98 AND INC.je_source_name = JEH.je_source
99 AND INC.je_category_name = 'Other'
100 AND INC.include_flag = 'N'
101 AND INC.user_updatable_flag = 'N'));
102
103 TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type
104 get_batches_cur EmpCurTyp; -- declare cursor variable
105 sqlstmt VARCHAR2(5000);
106
107 v_ledger_id VARCHAR2(15);
108 v_chart_of_accounts_id NUMBER(15);
109 v_enable_bc_flag VARCHAR2(1);
110 v_automatic_tax_flag VARCHAR2(1);
111 v_je_approval_flag VARCHAR2(1);
112 v_posting_run_id NUMBER(15);
113 v_single_ledger_id NUMBER(15);
114 v_debug_flag VARCHAR2(1) := '';
115
116 add_or BOOLEAN;
117
118 v_je_batch_id NUMBER(15);
119 v_status VARCHAR2(1);
120 v_default_period_name VARCHAR2(15);
121 v_actual_flag VARCHAR2(1);
122 v_b_request_id NUMBER(15);
123
124 call_status BOOLEAN;
125 rphase VARCHAR2(80);
126 rstatus VARCHAR2(80);
127 dphase VARCHAR2(30);
128 dstatus VARCHAR2(30);
129 message VARCHAR2(240);
130 dummy VARCHAR2(1);
131
132 v_count_sel_bat NUMBER(15) := 0;
133 v_request_id NUMBER(15) := 0;
134 ok_to_update_batch BOOLEAN;
135
136 INVALID_LEDGER_ID EXCEPTION;
137 REQUEST_FAILED EXCEPTION;
138
139 BEGIN
140 -- Debug Mode - Print the parameters passed
141 IF (X_Debug_Mode) THEN
142 Debug_Print_Var('X_Ledger_Id',X_Ledger_Id);
143 Debug_Print_Var('X_Access_Set_Id',X_Access_Set_Id);
144 Debug_Print_Var('X_Je_Batch_Id',X_Je_Batch_Id);
145 Debug_Print_Var('X_Je_Source_Name',X_Je_Source_Name);
146 Debug_Print_Var('X_Je_Category_Name',X_Je_Category_Name);
147 Debug_Print_Var('X_Actual_Flag',X_Actual_Flag);
148 Debug_Print_Var('X_Period_Name',X_Period_Name);
149 Debug_Print_Var('X_From_Day_Before',X_From_Day_Before);
150 Debug_Print_Var('X_To_Day_After',X_To_Day_After);
151 Debug_Print_Var('X_Name',X_Name);
152 Debug_Print_Var('X_Description',X_Description);
153 Debug_Print_Msg('X_Debug_Mode = TRUE');
154 Debug_Print_Msg('');
155 END IF;
156
157 -- Get the Ledger information and the posting_run_id
158 OPEN retrieve_ledger_info (X_Ledger_Id);
159
160 FETCH retrieve_ledger_info INTO v_chart_of_accounts_id,
161 v_enable_bc_flag,
162 v_automatic_tax_flag,
163 v_je_approval_flag,
164 v_posting_run_id;
165
166 IF (retrieve_ledger_info%NOTFOUND) THEN
167 CLOSE retrieve_ledger_info;
168 RAISE INVALID_LEDGER_ID;
169 ELSE
170 CLOSE retrieve_ledger_info;
171 END IF;
172
173 -- Debug Mode
174 IF (X_Debug_Mode) THEN
175 Debug_Print_Var('v_chart_of_accounts_id',v_chart_of_accounts_id);
176 Debug_Print_Var('v_enable_bc_flag',v_enable_bc_flag);
177 Debug_Print_Var('v_automatic_tax_flag',v_automatic_tax_flag);
178 Debug_Print_Var('v_je_approval_flag',v_je_approval_flag);
179 Debug_Print_Var('v_posting_run_id',v_posting_run_id);
180 Debug_Print_Msg('');
181 END IF;
182
183 -- Temp buffer for Ledger ID information
184 v_ledger_id := TO_CHAR(X_Ledger_Id,'FM999999999999999');
185
186 -- Construct the Dynamic SQL to select journal batches based on the
187 -- parameter passed to the function.
188 -- In order to be selected, all batches must be:
189 -- unposted
190 -- if defined, the control total must be the same as the running total
191 -- added budgetary_control_status filter for the bug 5003755.
192 sqlstmt := '
193 SELECT b.je_batch_id,
194 b.status,
195 b.default_period_name,
196 b.actual_flag,
197 b.request_id
198 FROM gl_je_batches b
199 WHERE (b.status < ''P'' OR b.status > ''P'')
200 AND b.status_verified = ''N''
201 AND b.budgetary_control_status != ''I''
202 AND greatest(nvl(b.running_total_dr,0),nvl(b.running_total_cr,0)) =
203 decode(b.control_total,null, greatest(nvl(b.running_total_dr,0),
204 nvl(b.running_total_cr,0)), b.control_total) ';
205
206 -- If Journal Approval is enabled, the Journal must have been approved
207 IF (v_je_approval_flag = 'Y') THEN
208 sqlstmt := sqlstmt
209 ||'AND (b.approval_status_code IN (''A'',''Z'')) ';
210 END IF;
211
212 -- If the Batch_id is provided
213 IF (X_Je_Batch_Id IS NOT NULL) THEN
214 sqlstmt := sqlstmt
215 ||'AND b.je_batch_id = '||TO_CHAR(X_Je_Batch_Id,'FM999999999999999')||' ';
216 END IF;
217
218 -- If the actual flag is provided
219 IF (X_Actual_Flag IS NOT NULL) THEN
220 sqlstmt := sqlstmt
221 ||'AND b.actual_flag = '''||X_Actual_Flag||''' ';
222 END IF;
223
224 -- If the batch name is provided
225 IF (X_Name IS NOT NULL) THEN
226 sqlstmt := sqlstmt
227 ||'AND b.name like '''||X_Name||''' ';
228 END IF;
229
230 -- If the description is provided
231 IF (X_Description IS NOT NULL) THEN
232 sqlstmt := sqlstmt
233 ||'AND b.description like '''||X_Description||''' ';
234 END IF;
235
236 -- Check at the header level if :
237 -- the period are valid
238 sqlstmt := sqlstmt||'
239 AND b.je_batch_id IN
240 (SELECT h.je_batch_id
241 FROM GL_LEDGERS ledger,
242 GL_JE_HEADERS h,
243 GL_BUDGETS glb,
244 GL_BUDGET_VERSIONS bv,
245 GL_PERIOD_STATUSES ps1,
246 GL_PERIOD_STATUSES ps2
247 WHERE ps2.ledger_id = '||v_ledger_id||'
248 AND ps2.application_id = 101
249 AND ps1.ledger_id (+) = '||v_ledger_id||'
250 AND ps1.application_id (+) = 101
251 AND ledger.ledger_id = '||v_ledger_id||'
252 AND h.ledger_id = '||v_ledger_id||'
253 AND h.period_name = ps2.period_name
254 AND ps2.period_year <=
255 decode(h.actual_flag,
256 ''E'', ledger.latest_encumbrance_year,
257 ''B'', glb.latest_opened_year,
258 ''A'', decode (ps2.closing_status,''O'',ps2.period_year, -1))
259 AND ps2.period_year >=
260 decode(h.actual_flag,
261 ''B'', ps1.period_year,
262 ps2.period_year)
263 AND b.je_batch_id = h.je_batch_id
264 AND h.budget_version_id = bv.budget_version_id (+)
265 AND bv.budget_name = glb.budget_name (+)
266 AND glb.status (+) != ''F''
267 AND ps1.period_name (+) = glb.first_valid_period_name ';
268
269 -- If the actual flag is provided
270 IF (X_Actual_Flag IS NOT NULL) THEN
271 sqlstmt := sqlstmt
272 ||'AND h.actual_flag = '''||X_Actual_Flag||''' ';
273 END IF;
274
275 -- If the period name is provided
276 IF (X_Period_Name IS NOT NULL) THEN
277 sqlstmt := sqlstmt
278 ||'AND h.period_name = '''||X_Period_Name||''' ';
279 END IF;
280
281 -- If Journal Source is provided
282 IF (X_Je_Source_Name IS NOT NULL) THEN
283 sqlstmt := sqlstmt
284 ||' AND h.je_source = '''||X_Je_Source_Name||''' ';
285
286 END IF;
287
288 -- Close Check at the header level
289 sqlstmt := sqlstmt
290 ||' ) ';
291
292 -- If some date range is provided, check that all the
293 -- journal effective dates are within the ranges.
294 IF (X_Je_Category_Name IS NOT NULL
295 OR X_From_Day_Before IS NOT NULL
296 OR X_To_Day_After IS NOT NULL) THEN
297
298 add_or := FALSE;
299
300 sqlstmt := sqlstmt||'
301 AND NOT EXISTS (SELECT 1
302 FROM GL_JE_HEADERS h
303 WHERE h.je_batch_id = b.je_batch_id
304 AND (';
305
306 IF (X_Je_Category_Name IS NOT NULL) THEN
307 sqlstmt := sqlstmt||'h.je_category <> '''||X_Je_Category_Name||''' ';
308 add_or := TRUE;
309 END IF;
310
311 IF (X_From_Day_Before IS NOT NULL) THEN
312 IF (add_or) THEN
313 sqlstmt := sqlstmt||' OR ';
314 END IF;
315
316 sqlstmt := sqlstmt||'h.default_effective_date <
317 (sysdate - '||TO_CHAR(X_From_Day_Before,'FM999999999999999')||') ';
318 add_or := TRUE;
319 END IF;
320
321 IF (X_To_Day_After IS NOT NULL) THEN
322 IF (add_or) THEN
323 sqlstmt := sqlstmt||' OR ';
324 END IF;
325
326 sqlstmt := sqlstmt||'h.default_effective_date >
327 (sysdate + '||TO_CHAR(X_From_Day_Before,'FM999999999999999')||') ';
328 END IF;
329
330 sqlstmt := sqlstmt||'))';
331
332 END IF;
333
334 -- Don't select taxable journals if journal tax is not calculated.
335 IF (v_automatic_tax_flag = 'Y'
336 AND X_Actual_flag = 'A'
337 AND X_Je_Source_Name = 'Manual') THEN
338 sqlstmt := sqlstmt||'
339 AND NOT EXISTS (SELECT 1
340 FROM GL_JE_HEADERS glh
341 WHERE glh.tax_status_code = ''R''
342 AND glh.je_batch_id = b.je_batch_id
343 AND b.actual_flag = ''A''
344 AND glh.currency_code != ''STAT''
345 AND glh.je_source = ''Manual'')';
346 END IF;
347
348 -- We need to lock the selected batches
349 sqlstmt := sqlstmt
350 ||'
351 FOR UPDATE OF status, posting_run_id NOWAIT ';
352
353 -- Debug Mode
354 IF (X_Debug_Mode) THEN
355 Debug_Print_Msg('SQL Statement to select batches:');
356 FOR i IN 0..(LENGTHB(sqlstmt)/250) LOOP
357 Debug_Print_Msg(SUBSTRB(sqlstmt,i*250+1,250));
358 END LOOP;
359 Debug_Print_Msg('');
360 END IF;
361
362 -- Open the Dynamic SQL to select journal batches
363 OPEN get_batches_cur FOR sqlstmt;
364
365 -- Loop through the selected journal batches
366 LOOP
367 FETCH get_batches_cur INTO v_je_batch_id,
368 v_status,
369 v_default_period_name,
370 v_actual_flag,
371 v_b_request_id; -- fetch next row
372
373 EXIT WHEN get_batches_cur%NOTFOUND; -- exit loop when last row is fetched
374
375 -- process row
376
377 -- If the current batch is in status 'SELECTED' or 'UNDERWAY',
378 -- check the Concurrent Request status.
379 IF (v_status IN ('S','I')) THEN
380 IF (v_b_request_id IS NULL) THEN
381 -- This should not happen but just in case
382 ok_to_update_batch := FALSE;
383 ELSE
384 call_status :=
385 FND_CONCURRENT.GET_REQUEST_STATUS(v_b_request_id,
386 null,
387 null,
388 rphase,
389 rstatus,
390 dphase,
391 dstatus,
392 message);
393
394 IF (NOT call_status) THEN
395 ok_to_update_batch := FALSE;
396
397 ELSIF (v_status = 'S' AND
398 dphase = 'COMPLETE') THEN
399 ok_to_update_batch := TRUE;
400
401 ELSIF (v_status = 'I' AND
402 dphase <> 'RUNNING') THEN
403 ok_to_update_batch := TRUE;
404
405 ELSE
406 ok_to_update_batch := FALSE;
407
408 END IF;
409 END IF;
410 ELSE
411 ok_to_update_batch := TRUE;
412 END IF;
413
414 -- Update Batch Status
415 IF (ok_to_update_batch) THEN
416 UPDATE gl_je_batches
417 SET status = 'S',
418 posting_run_id = v_posting_run_id
419 WHERE je_batch_id = v_je_batch_id;
420
421 v_count_sel_bat := v_count_sel_bat + 1;
422 END IF;
423
424 -- Debug Mode
425 IF (X_Debug_Mode) THEN
426 IF (ok_to_update_batch) THEN
427 Debug_Print_Msg('Batch with ID '||v_je_batch_id||' selected for posting');
428 ELSE
429 Debug_Print_Msg('Batch with ID '||v_je_batch_id||' is not ok to post');
430 END IF;
431 END IF;
432
433 END LOOP;
434
435 -- Debug Mode
436 IF (X_Debug_Mode) THEN
437 Debug_Print_Msg('');
438 Debug_Print_Msg('The number of batch selected for posting is :'||v_count_sel_bat);
439 Debug_Print_Msg('');
440 END IF;
441
442 IF (v_count_sel_bat > 0) THEN
443
444 -- Debug Mode
445 IF (X_Debug_Mode) THEN
446 v_debug_flag := 'Y';
447 END IF;
448
449 -- Set single_ledger_id to the journal ledger id if the batch
450 -- has journals only for a single ledger which has no enabled
451 -- journal or subledger RCs.
452 OPEN single_ledger(v_posting_run_id);
453 FETCH single_ledger INTO v_single_ledger_id;
454 IF single_ledger%NOTFOUND THEN
455 v_single_ledger_id := -99;
456 ELSE
457 OPEN alc_exists(v_posting_run_id);
458 FETCH alc_exists INTO dummy;
459 IF alc_exists%FOUND THEN
460 v_single_ledger_id := -99;
461 END IF;
462 CLOSE alc_exists;
463 END IF;
464 CLOSE single_ledger;
465
466 -- Submit Posting...
467 IF (v_single_ledger_id = -99) THEN
468 v_request_id :=
469 fnd_request.submit_request(
470 'SQLGL', 'GLPPOS', '', '', FALSE,
471 To_Char(v_single_ledger_id),
472 To_Char(X_access_set_id),
473 To_Char(v_chart_of_accounts_id),
474 To_Char(v_posting_run_id),
475 chr(0),
476 '','','','','','','','','','','','','','','','','','','','',
477 '','','','','','','','','','','','','','','','','','','','',
478 '','','','','','','','','','','','','','','','','','','','',
479 '','','','','','','','','','','','','','','','','','','','',
480 '','','','','','','','','','','','','','');
481 ELSE
482 v_request_id :=
483 fnd_request.submit_request(
484 'SQLGL', 'GLPPOSS', '', '', FALSE,
485 To_Char(v_single_ledger_id),
486 To_Char(X_access_set_id),
487 To_Char(v_chart_of_accounts_id),
488 To_Char(v_posting_run_id),
489 chr(0),
490 '','','','','','','','','','','','','','','','','','','','',
491 '','','','','','','','','','','','','','','','','','','','',
492 '','','','','','','','','','','','','','','','','','','','',
493 '','','','','','','','','','','','','','','','','','','','',
494 '','','','','','','','','','','','','','','');
495 END IF;
496
497 IF (v_request_id = 0) THEN
498 RAISE REQUEST_FAILED;
499 END IF;
500
501 -- Update the status of the consolidation batch to 'PS' in
502 -- gl_consolidation_history for Consolidation Workbench.
503 UPDATE GL_CONSOLIDATION_HISTORY
504 SET status = 'PS',
505 request_id = v_request_id
506 WHERE je_batch_id IN (SELECT je_batch_id
507 FROM gl_je_batches
508 WHERE posting_run_id = v_posting_run_id
509 AND status = 'S');
510
511 UPDATE GL_ELIMINATION_HISTORY EH
512 SET EH.status_code = 'PS',
513 EH.request_id = v_request_id
514 WHERE EH.je_batch_id IN (SELECT je_batch_id
515 FROM gl_je_batches
516 WHERE posting_run_id = v_posting_run_id
517 AND status = 'S');
518
519 END IF;
520
521 -- Debug Mode
522 IF (X_Debug_Mode) THEN
523 Debug_Print_Var('X_Request_Id',v_request_id);
524 Debug_Print_Var('X_Count_Sel_Bat',v_count_sel_bat);
525 Debug_Print_Msg('');
526 END IF;
527
528 X_Request_Id := v_request_id;
529 X_Count_Sel_Bat := v_count_sel_bat;
530
531 EXCEPTION
532 WHEN INVALID_LEDGER_ID THEN
533 fnd_message.set_name('SQLGL', 'GL_AUTOPOST_INVALID_LEDGER_ID');
534
535 IF (X_Debug_Mode) THEN
536 Debug_Print_Msg(fnd_message.get);
537 Debug_Print_Msg(substrb(SQLERRM, 1, 2000));
538 END IF;
539 app_exception.raise_exception;
540
541 WHEN REQUEST_FAILED THEN
542 fnd_message.set_name('SQLGL', 'SHRD0148');
543
544 IF (X_Debug_Mode) THEN
545 Debug_Print_Msg(fnd_message.get);
546 Debug_Print_Msg(substrb(SQLERRM, 1, 2000));
547 END IF;
548 app_exception.raise_exception;
549
550 WHEN OTHERS THEN
551 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
552 fnd_message.set_token('PROCEDURE', 'gl_autopost_pkg.post_batches');
553
554 IF (X_Debug_Mode) THEN
555 Debug_Print_Msg(fnd_message.get);
556 Debug_Print_Msg(substrb(SQLERRM, 1, 2000));
557 END IF;
558 app_exception.raise_exception;
559
560 END Post_Batches;
561
562 END gl_autopost_pkg;
563
[Home] [Help]