[Home] [Help]
PACKAGE BODY: APPS.JE_GR_STATUTORY
Source
1 PACKAGE BODY JE_GR_STATUTORY as
2 /* $Header: jegrstab.pls 120.5.12010000.2 2009/12/04 10:38:11 rshergil ship $ */
3
4 /*------------------------------------------------------------------+
5 | Package Cursor and Variables |
6 +------------------------------------------------------------------*/
7
8 /* The following cursor is used in procedures gl_sequence and gl_cutoff */
9
10 CURSOR c_headers (c_posting_run_id NUMBER, c_last_batch_id NUMBER) IS
11 SELECT jh.je_batch_id,
12 jh.je_header_id,
13 doc.code,
14 jh.ledger_id,
15 --jh.set_of_books_id,
16 jh.default_effective_date
17 -- sob.global_attribute_category
18 -- nvl(sob.global_attribute2, 'N'),
19 -- nvl(sob.global_attribute3, 'N')
20 FROM
21 gl_sets_of_books sob,
22 fnd_doc_sequence_categories doc,
23 fnd_application a,
24 gl_je_headers jh,
25 gl_je_batches jb
26 WHERE
27 jb.actual_flag = 'A' AND
28 jb.posting_run_id = c_posting_run_id AND
29 jh.je_batch_id = jb.je_batch_id AND
30 jb.status = 'I' AND
31 jh.je_batch_id > c_last_batch_id AND
32 a.application_short_name = 'SQLGL' AND
33 doc.code = jh.je_category AND
34 doc.application_id = a.application_id AND
35 sob.set_of_books_id = jh.ledger_id; --??
36 --
37 -- Fetch variables
38 --
39 v_pkg_batch_id GL_JE_BATCHES.je_batch_id%TYPE;
40 v_pkg_header_id GL_JE_HEADERS.je_header_id%TYPE;
41 v_pkg_trx_date DATE;
42 v_pkg_last_batch_id GL_JE_BATCHES.je_batch_id%TYPE := 0;
43 v_pkg_category_code FND_DOC_SEQUENCE_CATEGORIES.code%TYPE;
44 v_pkg_cat_application_id FND_DOC_SEQUENCE_CATEGORIES.application_id%TYPE;
45 v_pkg_ledger_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE; --??
46 --v_pkg_global_attribute GL_SETS_OF_BOOKS.global_attribute1%TYPE;
47 v_pkg_sequence_ax_journals VARCHAR2(150);
48 v_pkg_append_sequence VARCHAR2(150);
49
50 /*------------------------------------------------------------------+
51 | PROCEDURE: get_cutoff |
52 | DESCRIPTION |
53 | Get the cutoff rule for the given |
54 | SOB, category, application combination, once/session |
55 | Once cached, return the days and violation response |
56 | CALLED BY |
57 | check_cutoff |
58 +------------------------------------------------------------------*/
59 PROCEDURE get_cutoff (p_ledger_id IN NUMBER, --??
60 p_category_code IN VARCHAR2,
61 p_cat_application_id IN NUMBER,
62 p_cutoff_days OUT NOCOPY NUMBER,
63 p_violation_response OUT NOCOPY VARCHAR2,
64 p_retcode IN OUT NOCOPY NUMBER,
65 p_errmsg IN OUT NOCOPY VARCHAR2) IS
66
67 CURSOR c_cutoff_rules IS
68 SELECT category_code,
69 cat_application_id,
70 set_of_books_id, --??
71 days,
72 violation_response,
73 enabled_flag
74 FROM je_gr_cutoff_rules;
75
76 idx BINARY_INTEGER;
77 v_pkg_category_code FND_DOC_SEQUENCE_CATEGORIES.code%TYPE;
78 v_pkg_cat_application_id FND_DOC_SEQUENCE_CATEGORIES.application_id%TYPE;
79 v_pkg_ledger_id GL_SETS_OF_BOOKS.set_of_books_id%TYPE;
80 v_days JE_GR_CUTOFF_RULES.days%TYPE;
81 v_violation_response JE_GR_CUTOFF_RULES.violation_response%TYPE;
82 v_enabled_flag JE_GR_CUTOFF_RULES.enabled_flag%TYPE;
83
84 BEGIN
85 p_cutoff_days := '';
86 p_violation_response := '';
87 --
88 -- first call to this function in this DB session - cache all rules into a global table
89 --
90 IF (g_idx <= 0) THEN
91 OPEN c_cutoff_rules;
92 LOOP
93 FETCH c_cutoff_rules INTO v_pkg_category_code,
94 v_pkg_cat_application_id,
95 v_pkg_ledger_id, --??
96 v_days,
97 v_violation_response,
98 v_enabled_flag;
99
100 EXIT when c_cutoff_rules%NOTFOUND;
101 g_cutoff_rules(g_idx).category_code := v_pkg_category_code;
102 g_cutoff_rules(g_idx).cat_application_id := v_pkg_cat_application_id;
103 g_cutoff_rules(g_idx).ledger_id := v_pkg_ledger_id;--??
104
105 IF (v_enabled_flag = 'N') THEN
106 -- Bug 881630: Change the day range to a smaller one.
107 -- g_cutoff_rules(g_idx).days := 99999999;
108 g_cutoff_rules(g_idx).days := 99999;
109 ELSE
110 g_cutoff_rules(g_idx).days := v_days;
111 END IF;
112
113 g_cutoff_rules(g_idx).violation_response := v_violation_response;
114 g_idx := g_idx + 1;
115 END LOOP;
116 close c_cutoff_rules;
117 end if;
118 --
119 -- Get rule info, the purpose of the routine
120 --
121 FOR idx in 0..g_idx-1 LOOP
122 IF ((g_cutoff_rules(idx).category_code = p_category_code) AND
123 (g_cutoff_rules(idx).ledger_id = p_ledger_id) AND
124 (g_cutoff_rules(idx).cat_application_id = p_cat_application_id)) THEN
125 p_cutoff_days := g_cutoff_rules(idx).days;
126 p_violation_response := g_cutoff_rules(idx).violation_response;
127 exit;
128 END IF;
129 END LOOP;
130
131 EXCEPTION
132 WHEN OTHERS THEN p_retcode := -2;
133 END;
134
135 /*------------------------------------------------------------------+
136 | PROCEDURE: gl_sequence |
137 | DESCRIPTION |
138 | The routine to get the sequence information and update that |
139 | to all the journals |
140 | Phases: |
141 | 1. Get the sequential numbering option |
142 | 2. Loop the Journals in the main cursor/c_headers |
143 | 3. IF seq. numbering is used, get the sequence |
144 | 4.a IF Sequence failure, update batch and header |
145 | with error code |
146 | 4.b IF Sequence failure, update batch and header |
147 | with sequence number and name |
148 | CALLED BY |
149 +------------------------------------------------------------------*/
150 PROCEDURE gl_sequence (p_posting_run_id IN NUMBER,
151 p_retcode IN OUT NOCOPY NUMBER,
152 p_errmsg IN OUT NOCOPY VARCHAR2) IS
153
154 /*v_sequence_numbering VARCHAR2(1);
155 v_seq_value NUMBER;
156 v_seq_val NUMBER;
157 v_method_code VARCHAR2(1) := 'A'; /* Automatic entry methods only
158 v_db_seqname VARCHAR2(30);
159 v_sequence_id NUMBER;
160 v_sequence_name VARCHAR2(30);
161 v_je_attribute_category VARCHAR2(30) := 'JE.GR.GLXJEENT.HEADER'; /* Greece only
162 V_sob_attribute_category VARCHAR2(30) := 'JE.GR.GLXSTBKS.BOOKS'; /* Greece only
163 v_sequence_journals BOOLEAN;
164 v_pkg_sequence_ax_gl_journals BOOLEAN;
165 v_sobid varchar2(15);
166 v_je_header_id_flag NUMBER ;
167 v_je_header_id NUMBER ;
168 */
169
170 BEGIN
171 null;
172 /*v_pkg_last_batch_id := 0;
173 OPEN c_headers (p_posting_run_id, v_pkg_last_batch_id);
174 LOOP
175 FETCH c_headers INTO v_pkg_batch_id,
176 v_pkg_header_id,
177 v_pkg_category_code,
178 v_pkg_ledger_id, --??
179 v_pkg_trx_date,
180 --** v_pkg_global_attribute;
181 --** v_pkg_sequence_ax_journals,
182 --** v_pkg_append_sequence;
183
184 IF c_headers%NOTFOUND THEN
185 exit;
186 END IF;
187 v_pkg_cat_application_id := 7002; /* European Localizations
188
189 /* --Commented for Bug 1157174
190 -- Determine if journal should be sequenced. If it is an AX journal (subledgers) and
191 -- the set of books Global DFF states that those shouldn't be sequenced, set flag
192 -- sequence_journals accordingly NOTE: We select the code, that is not translated
193 --
194 IF (v_pkg_category_code in ('AR Subledger Entries', 'AP Subledger Entries',
195 'IC Subledger Entries') AND v_pkg_sequence_ax_journals = 'N') THEN
196 v_sequence_journals := FALSE;
197 ELSE
198 v_sequence_journals := TRUE;
199 END IF;
200
201
202 /* --Get the flag whether ax sequences journals or not.
203
204 -- FND_PROFILE.GET('GL_SET_OF_BKS_ID', v_sobid);
205 -- v_pkg_sequence_ax_gl_journals:= ax_setup_pkg.gl_sequencing(to_number(v_sobid));
206
207 -- Bug 4750571 AX will be obsoleted in R12 - v_pkg_sequence_ax_gl_journals
208 -- variable will always be FALSE so that package should assign the sequence
209
210 -- v_pkg_sequence_ax_gl_journals:= FALSE;
211
212 -- IF (v_pkg_category_code in ('AR Subledger Entries', 'AP Subledger Entries', 'IC Subledger Entries') AND
213 -- (v_pkg_sequence_ax_gl_journals)) THEN
214
215
216 -- Re-inserted the code:
217
218 IF (v_pkg_category_code in ('AR Subledger Entries', 'AP Subledger Entries',
219 'IC Subledger Entries') AND v_pkg_sequence_ax_journals = 'N') THEN
220 v_sequence_journals := FALSE;
221 ELSE
222 v_sequence_journals := TRUE;
223 END IF;
224
225
226 IF ((v_pkg_global_attribute = v_sob_attribute_category) and (v_sequence_journals)) THEN
227 BEGIN
228 /* v_seq_value := FND_SEQNUM.GET_NEXT_SEQUENCE (v_pkg_cat_application_id,
229 v_pkg_category_code,
230 v_pkg_ledger_id, --??
231 v_method_code,
232 v_pkg_trx_date,
233 v_db_seqname,
234 v_sequence_id);
235 -- Replaced the get_next_sequence with get_seq_val
236 v_seq_value := FND_SEQNUM.GET_SEQ_val (v_pkg_cat_application_id,
237 v_pkg_category_code,
238 v_pkg_ledger_id,--??
239 v_method_code,
240 v_pkg_trx_date,
241 v_seq_val,
242 v_sequence_id,
243 'Y','Y');
244
245 EXCEPTION
246 WHEN no_data_found THEN null;
247 END;
248 --
249 -- If no sequence value found
250 --
251 IF ((v_seq_value is NULL) or (v_sequence_id is NULL)) THEN
252 --
253 -- Set error status
254 --
255 UPDATE gl_je_batches
256 SET status = '<'
257 WHERE je_batch_id = v_pkg_batch_id;
258
259 UPDATE gl_je_headers
260 SET status = '<'
261 WHERE je_header_id = v_pkg_header_id;
262 --
263 -- Close cursor and re-open cursor to continue processing with next batch
264 --
265 v_pkg_last_batch_id := v_pkg_batch_id;
266 CLOSE c_headers;
267 OPEN c_headers (p_posting_run_id, v_pkg_last_batch_id);
268 ELSE /* Found a sequence value
269 SELECT name
270 INTO v_sequence_name
271 FROM fnd_document_sequences
272 WHERE doc_sequence_id = v_sequence_id;
273
274 -- Added this part to fix the bug 2700126
275
276 begin
277 v_je_header_id_flag :=0;
278 select je_header_id into v_je_header_id
279 from je_gr_je_header_sequence
280 where je_header_id = v_pkg_header_id;
281 exception
282 when no_data_found then
283 v_je_header_id_flag := 1;
284 end;
285
286 if v_je_header_id_flag =1 then
287 UPDATE gl_je_headers
288 SET global_attribute_category = v_je_attribute_category,
289 -- ** global_attribute1 = v_sequence_name,
290 -- ** global_attribute2 = v_seq_val,
291 external_reference = DECODE(v_pkg_append_sequence,
292 'Y', substr(v_seq_val || ' ' ||
293 v_sequence_name || ' ' ||
294 external_reference , 1, 80),
295 'N', external_reference,
296 external_reference)
297 WHERE je_header_id = v_pkg_header_id;
298
299 INSERT INTO je_gr_je_header_sequence (je_header_id,
300 doc_sequence_id,
301 doc_sequence_value,
302 creation_date,
303 created_by)
304 VALUES (v_pkg_header_id,
305 v_sequence_id,
306 v_seq_val,
307 sysdate,
308 FND_GLOBAL.user_id);
309 end if; -- end of modifications for the bug 2700126
310 END IF; /* Check if sequence value was found
311 --
312 -- Initialize variables for next fetch
313 --
314 v_seq_value := '';
315 v_sequence_id := '';
316 v_seq_val := '';
317 END IF; /* If v_pkg_global_attribute
318 END LOOP;
319 CLOSE c_headers;
320
321 p_retcode := 0;
322 p_errmsg := '';
323 EXCEPTION
324 WHEN OTHERS THEN
325 IF (c_headers%ISOPEN) THEN
326 close c_headers;
327 END IF;
328 p_retcode := -2;
329 p_errmsg := 'SQL ERROR-ALERT';
330 */
331 END;
332
333 /*------------------------------------------------------------------+
334 | PROCEDURE: check_cutoff |
335 | DESCRIPTION |
336 | Procedure to figure out if the Cutoff is to be violated |
337 | If there is GDFF poiting to Greece, we validate |
338 | CALLED BY |
339 | gl_cutoff |
340 +------------------------------------------------------------------*/
341 PROCEDURE check_cutoff (p_ledger_id IN NUMBER,
342 p_gldate IN DATE,
343 p_category_code IN VARCHAR2,
344 p_cat_application_id IN NUMBER,
345 p_retcode IN OUT NOCOPY NUMBER,
346 p_errmsg IN OUT NOCOPY VARCHAR2) IS
347
348 v_cutoff_days JE_GR_CUTOFF_RULES.days%TYPE := '';
349 v_violation_response JE_GR_CUTOFF_RULES.violation_response%TYPE := '';
350 v_global VARCHAR2(100) := '';
351 -- Bug 881630: Correct the definition of v_default_cutoff_days
352 -- v_default_cutoff_days JE_GR_CUTOFF_RULES.violation_response%TYPE;
353 v_default_cutoff_days JE_GR_CUTOFF_RULES.days%TYPE := '';
354
355 BEGIN
356 p_retcode := 0;
357 p_errmsg := '';
358 --
359 -- The Greek Global DFF stores the default cutoff days in global_attribute1
360 --
361 v_global:=JG_ZZ_SHARED_PKG.get_country(null, p_ledger_id);
362 /*SELECT global_attribute_category
363 --to_number(nvl(global_attribute1, 15))**
364 INTO v_global
365 v_default_cutoff_days
366 FROM gl_sets_of_books
367 WHERE set_of_books_id = p_ledger_id;--??
368 */
369 IF (v_global = 'GR') THEN
370
371 get_cutoff ( p_ledger_id,
372 p_category_code,
373 p_cat_application_id,
374 v_cutoff_days,
375 v_violation_response,
376 p_retcode,
377 p_errmsg);
378 v_default_cutoff_days:=to_number(fnd_profile.value('JEGR_DEF_CUTOFF_DAYS'));
379 -- p_retcode := -1;
380 IF (p_retcode = 0) THEN
381 IF (TRUNC(sysdate) - NVL(v_cutoff_days, v_default_cutoff_days) > p_gldate) THEN
382 --
383 -- If response = 'FAIL' or v_cutoff_days is NULL (no rule found,
384 -- set of books default cutoff days was used)
385 --
386 IF (v_violation_response = 'FAIL' or nvl(v_cutoff_days, -9) = -9) THEN
387 p_retcode := -1;
388 p_errmsg := 'JE_GR_CUTOFF_FAIL';
389 ELSE /* violation response is warning */
390 p_retcode := 1;
391 p_errmsg := 'JE_GR_CUTOFF_WARNING';
392 END IF;
393 END IF; /* cutoff > gl_date */
394 END IF; /* retcode = 0 */
395 END IF; /* v_global = ... */
396 EXCEPTION
397 WHEN OTHERS THEN p_retcode := -2;
398 p_errmsg := 'SQL ERROR-ALERT';
399 END;
400
401 /*------------------------------------------------------------------+
402 | PROCEDURE: gl_cutoff |
403 | DESCRIPTION |
404 | The main procedure for cutoff violation |
405 | Looks through all the batches, headers of the posting |
406 | Control id
407 | CALLED BY |
408 +------------------------------------------------------------------*/
409 PROCEDURE gl_cutoff (p_posting_run_id IN NUMBER,
410 p_retcode IN OUT NOCOPY NUMBER,
411 p_errmsg IN OUT NOCOPY VARCHAR2) IS
412 BEGIN
413 v_pkg_last_batch_id := 0;
414 p_retcode := 0;
415 p_errmsg := '';
416
417 OPEN c_headers (p_posting_run_id, v_pkg_last_batch_id);
418 LOOP
419 FETCH c_headers into v_pkg_batch_id,
420 v_pkg_header_id,
421 v_pkg_category_code,
422 v_pkg_ledger_id, --??
423 v_pkg_trx_date;
424 -- v_pkg_global_attribute;
425 -- v_pkg_sequence_ax_journals,
426 -- v_pkg_append_sequence;
427 IF (c_headers%NOTFOUND) THEN
428 exit;
429 END IF;
430 p_retcode := 0;
431 p_errmsg := '';
432 v_pkg_cat_application_id := 101; /* General Ledger */
433
434 check_cutoff ( v_pkg_ledger_id, --??
435 v_pkg_trx_date,
436 v_pkg_category_code,
437 v_pkg_cat_application_id,
438 p_retcode,
439 p_errmsg);
440 --
441 -- Check if cut off has been violated (retcode = -1)
442 -- Set error status
443 --
444
445 IF (p_retcode < 0) THEN
446 UPDATE gl_je_batches
447 SET status = '>'
448 WHERE je_batch_id = v_pkg_batch_id;
449
450 UPDATE gl_je_headers
451 SET status = '>'
452 WHERE je_header_id = v_pkg_header_id;
453 --
454 -- Close cursor and re-open cursor to continue processing with
455 -- next batch
456 --
457 v_pkg_last_batch_id := v_pkg_batch_id;
458 CLOSE c_headers;
459 OPEN c_headers (p_posting_run_id,
460 v_pkg_last_batch_id);
461 end if; /* p_retcode < 0 */
462 END LOOP;
463 close c_headers;
464 -- p_retcode := 0;
465 -- p_errmsg := '';
466 EXCEPTION
467 WHEN OTHERS THEN
468 IF (c_headers%ISOPEN) THEN
469 CLOSE c_headers;
470 END IF;
471 p_retcode := -3;
472 p_errmsg := 'SQL ERROR-ALERT';
473 END;
474
475 END JE_GR_STATUTORY;