[Home] [Help]
PACKAGE BODY: APPS.JL_CO_GL_MG_MEDIA_PKG
Source
1 PACKAGE BODY JL_CO_GL_MG_MEDIA_PKG AS
2 /* $Header: jlcogmgb.pls 120.5.12010000.2 2008/08/04 12:51:30 vgadde ship $ */
3
4 foreign_nit CONSTANT VARCHAR2(10) := '444444444';
5 x_message VARCHAR2(2000);
6 x_trx_count NUMBER;
7 x_bal_count NUMBER;
8
9 x_nit jl_co_gl_nits.nit%TYPE;
10 x_name jl_co_gl_nits.name%TYPE;
11 x_type jl_co_gl_nits.type%TYPE;
12 x_verifying_digit jl_co_gl_nits.verifying_digit%TYPE;
13
14 x_literal_code jl_co_gl_mg_literals.literal_code%TYPE;
15
16 x_reported_flag jl_co_gl_mg_lines.reported_flag%TYPE;
17 x_first_value jl_co_gl_mg_lines.first_reported_value%TYPE;
18 x_second_value jl_co_gl_mg_lines.second_reported_value%TYPE;
19 x_mg_header_id jl_co_gl_mg_headers.mg_header_id%TYPE;
20
21 count_process_flag NUMBER := 0;
22 count_status NUMBER := 0;
23
24 x_file_handle UTL_FILE.FILE_TYPE;
25
26 TYPE get_movement_record IS RECORD (
27 mg_header_id jl_co_gl_mg_headers.mg_header_id%TYPE,
28 mg_line_id jl_co_gl_mg_lines.mg_line_id%TYPE,
29 literal_id jl_co_gl_mg_literals.literal_id%TYPE,
30 foreign_reported_flag jl_co_gl_mg_literals.foreign_reported_flag%TYPE,
31 foreign_description jl_co_gl_mg_literals.foreign_description%TYPE,
32 domestic_reported_flag jl_co_gl_mg_literals.domestic_reported_flag%TYPE,
33 reported_value jl_co_gl_mg_configs.reported_value%TYPE,
34 nit_id jl_co_gl_nits.nit_id%TYPE,
35 config_id jl_co_gl_mg_configs.config_id%TYPE,
36 literal_literal_id jl_co_gl_mg_configs.literal_literal_id%TYPE,
37 range_id jl_co_gl_mg_ranges.range_id%TYPE,
38 send_back_flag jl_co_gl_mg_lines.send_back_flag%TYPE,
39 origin jl_co_gl_mg_lines.origin%TYPE,
40 amount jl_co_gl_mg_lines.first_reported_value%TYPE
41 );
42
43 get_move_rec get_movement_record;
44 null_get_move_rec get_movement_record;
45
46 x_error_code NUMBER;
47 x_error_text VARCHAR2(2000);
48
49 x_last_updated_by NUMBER(15);
50 x_last_update_login NUMBER(15);
51 x_request_id NUMBER(15);
52 x_program_application_id NUMBER(15);
53 x_program_id NUMBER(15);
54 x_sysdate DATE;
55
56 TYPE flat_file_tab_type IS TABLE OF VARCHAR2(2000)
57 INDEX BY BINARY_INTEGER;
58 tab_flat_file flat_file_tab_type;
59 tab_record_counter NUMBER := 0;
60
61 LOCATION_ID_DOES_NOT_EXIST EXCEPTION;
62 HEADERS_STATUS_Y EXCEPTION;
63
64
65
66 /***************************************
67 Procedure to get standard 'who' columns
68 ***************************************/
69
70 PROCEDURE find_who_columns IS
71
72 BEGIN
73
74 x_last_updated_by := fnd_global.user_id;
75 x_last_update_login := fnd_global.login_id;
76 x_request_id := fnd_global.conc_request_id;
77 x_program_application_id := fnd_global.prog_appl_id;
78 x_program_id := fnd_global.conc_program_id;
79 x_sysdate := SYSDATE;
80
81 END find_who_columns;
82
83
84
85 /*******************************
86 Procedure to write to flat file
87 *******************************/
88
89 PROCEDURE put_line(which IN NUMBER,
90 buffer IN VARCHAR2) IS
91
92 BEGIN
93
94 fnd_file.put_line(which, buffer);
95
96 EXCEPTION
97
98 WHEN UTL_FILE.INVALID_PATH THEN
99
100 fnd_message.set_name('JL', 'JL_CO_GL_MG_INVALID_PATH');
101 x_error_text := SUBSTR(fnd_message.get, 1, 100);
102 app_exception.raise_exception (exception_type => 'APP',
103 exception_code =>
104 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_INVALID_PATH'),
105 exception_text => x_error_text);
106
107 WHEN UTL_FILE.INVALID_MODE THEN
108
109 fnd_message.set_name('JL', 'JL_CO_GL_MG_INVALID_MODE');
110 x_error_text := SUBSTR(fnd_message.get, 1, 100);
111 app_exception.raise_exception (exception_type => 'APP',
112 exception_code =>
113 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_INVALID_MODE'),
114 exception_text => x_error_text);
115
116 WHEN UTL_FILE.WRITE_ERROR THEN
117
118 fnd_message.set_name('JL', 'JL_CO_GL_MG_WRITE_ERROR');
119 x_error_text := SUBSTR(fnd_message.get, 1, 100);
120 app_exception.raise_exception (exception_type => 'APP',
121 exception_code =>
122 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_WRITE_ERROR'),
123 exception_text => x_error_text);
124
125 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
126
127 fnd_message.set_name('JL', 'JL_CO_GL_MG_INVALID_FILEHANDLE');
128 x_error_text := SUBSTR(fnd_message.get, 1, 100);
129 app_exception.raise_exception (exception_type => 'APP',
130 exception_code =>
131 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_INVALID_FILEHANDLE'),
132 exception_text => x_error_text);
133
134 WHEN UTL_FILE.INVALID_OPERATION THEN
135
136 fnd_message.set_name('JL', 'JL_CO_GL_MG_INVALID_OPERATION');
137 x_error_text := SUBSTR(fnd_message.get, 1, 100);
138 app_exception.raise_exception (exception_type => 'APP',
139 exception_code =>
140 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_INVALID_OPERATION'),
141 exception_text => x_error_text);
142
143 WHEN OTHERS THEN
144
145 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
146 x_error_text := SUBSTR(fnd_message.get, 1, 100);
147 ROLLBACK;
148 app_exception.raise_exception (exception_type => 'APP',
149 exception_code =>
150 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_FA_GENERAL_ERROR'),
151 exception_text => x_error_text);
152
153 END put_line;
154
155
156
157 /**********************************************************************
158 Procedure to get nit information from jl_co_gl_nits for a given nit_id
159 **********************************************************************/
160
161 PROCEDURE get_nit_info
162 (p_nit_id IN jl_co_gl_nits.nit_id%TYPE
163 ) IS
164 BEGIN
165
166 /*****************************************
167 Select NIT information from jl_co_gl_nits
168 *****************************************/
169
170 SELECT nit,
171 name,
172 type,
173 DECODE(verifying_digit, NULL, ' ', verifying_digit)
174 INTO x_nit,
175 x_name,
176 x_type,
177 x_verifying_digit
178 FROM jl_co_gl_nits
179 WHERE nit_id = p_nit_id;
180
181 EXCEPTION
182
183 WHEN NO_DATA_FOUND THEN
184
185 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
186 fnd_message.set_token('GENERIC_TEXT',
187 'Exception "NO_DATA_FOUND" for selection of nit information from JL_CO_GL_NITS table');
188 x_error_text := SUBSTR(fnd_message.get, 1, 100);
189 app_exception.raise_exception (exception_type => 'APP',
190 exception_code =>
191 jl_zz_fa_utilities_pkg.get_app_errnum('AR', 'GENERIC_MESSAGE'),
192 exception_text => x_error_text);
193
194 WHEN OTHERS THEN
195
196 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
197 fnd_message.set_token('GENERIC_TEXT',
198 'Exception "OTHERS" for selection of nit information from JL_CO_GL_NITS table');
199 x_error_text := SUBSTR(fnd_message.get, 1, 100);
200 app_exception.raise_exception (exception_type => 'APP',
201 exception_code =>
202 jl_zz_fa_utilities_pkg.get_app_errnum('AR', 'GENERIC_MESSAGE'),
203 exception_text => x_error_text);
204
205 END get_nit_info;
206
207
208
209 /****************************************************************
210 Procedure to validate and insert into jl_co_gl_mg_lines table.
211 This procedure will be called only when the amount returned from
212 cursors trx_cur and bal_cur is more than zero
213 ****************************************************************/
214
215 PROCEDURE get_movement_insert(in_rec IN get_movement_record) IS
216
217 BEGIN
218
219 /*******************************************************
220 Call the procedure to get nit information for each call
221 *******************************************************/
222
223 get_nit_info(in_rec.nit_id);
224
225 /****************************************************************************
226 Initialize the x_reported_flag to 'Y', if in_rec.amount is greater than zero
227 ****************************************************************************/
228
229 x_reported_flag := 'Y';
230
231 /****************************************
232 Report foreign people with NIT 444444444
233 ****************************************/
234
235 IF x_type = 'FOREIGN_ENTITY' THEN
236
237 /***********************************************
238 Report foreign if literal foreign flag is 'Yes'
239 ***********************************************/
240
241 IF in_rec.foreign_reported_flag = 'N' THEN
242 x_reported_flag := 'N';
243 ELSE
244 x_name := in_rec.foreign_description;
245 END IF;
246
247 ELSE
248
249 /*************************************************
250 Report national if literal national flag is 'Yes'
251 *************************************************/
252
253 IF in_rec.domestic_reported_flag = 'N' THEN
254 x_reported_flag := 'N';
255 END IF;
256
257 END IF;
258
259 IF x_reported_flag = 'Y' THEN
260
261 /******************************
262 First or Second Reported Value
263 ******************************/
264
265 IF in_rec.reported_value = '1' THEN
266 x_first_value := in_rec.amount;
267 x_second_value := 0;
268 ELSE
269 x_first_value := 0;
270 x_second_value := in_rec.amount;
271 END IF;
272
273 /****************************************
274 Set reported_flag to 'N' if x_nit is '0'
275 ****************************************/
276
277 IF x_nit = '0' THEN
278 x_reported_flag := 'N';
279 END IF;
280
281 /**********************************
282 Insert rows into jl_co_gl_mg_lines
283 **********************************/
284
285 BEGIN
286
287 INSERT INTO jl_co_gl_mg_lines
288 (mg_line_id,
289 mg_header_id,
290 literal_id,
291 reported_value,
292 reported_flag,
293 send_back_flag,
294 origin,
295 nit_id,
296 third_party_name,
297 first_reported_value,
298 second_reported_value,
299 config_id,
300 literal_literal_id,
301 range_id,
302 created_by,
303 creation_date,
304 last_updated_by,
305 last_update_date,
306 last_update_login
307 )
308 VALUES
309 (in_rec.mg_line_id, /*mg_line_id*/
310 in_rec.mg_header_id, /*mg_header_id*/
311 in_rec.literal_id, /*literal_id*/
312 in_rec.reported_value, /*reported_value*/
313 x_reported_flag, /*reported_flag*/
314 in_rec.send_back_flag, /*send_back_flag*/
315 in_rec.origin, /*origin*/
316 in_rec.nit_id, /*nit_id*/
317 substr(x_name,1,60), /*third_party_name*/
318 x_first_value, /*first_reported_value*/
319 x_second_value, /*second_reported_value*/
320 in_rec.config_id, /*config_id*/
321 in_rec.literal_literal_id, /*literal_literal_id*/
322 in_rec.range_id, /*range_id*/
323 x_last_updated_by, /*created_by*/
324 x_sysdate, /*creation_date*/
325 x_last_updated_by, /*last_updated_by*/
326 x_sysdate, /*last_update_date*/
327 x_last_update_login /*last_update_login*/
328 );
329
330 EXCEPTION
331
332 WHEN OTHERS THEN
333
334 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
335 fnd_message.set_token('GENERIC_TEXT',
336 'Exception "OTHERS" while inserting into jl_co_gl_mg_lines table');
337 x_error_text := SUBSTR(fnd_message.get, 1, 100);
338 ROLLBACK;
339 app_exception.raise_exception (exception_type => 'APP',
340 exception_code =>
341 jl_zz_fa_utilities_pkg.get_app_errnum('AR', 'GENERIC_MESSAGE'),
342 exception_text => x_error_text);
343
344 END;
345
346 END IF;
347
348 EXCEPTION
349
350 WHEN NO_DATA_FOUND THEN
351
352 x_error_code := SQLCODE;
353 x_error_text := SUBSTR(SQLERRM, 1, 200);
354 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
355
356 WHEN OTHERS THEN
357
358 x_error_code := SQLCODE;
359 x_error_text := SUBSTR(SQLERRM, 1, 200);
360 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
361
362 END get_movement_insert;
363
364
365
366 /*********************************************************************
367 PROCEDURE
368 get_movement
369
370 DESCRIPTION
371 Use this procedure to insert transactions and balances from nit
372 tables into jl_co_gl_mg_headers and jl_co_gl_mg_lines tables, for a
373 set of literal/sub-literal, reported_value (called report_group)
374 for a given range of accounts from magnetic media set-up tables
375
376 PURPOSE:
377 Oracle Applications Rel 11.0
378
379 PARAMETERS:
380 p_set_of_books_id
381 p_reported_year
382 p_period_start
383 p_period_end
384 p_literal_start
385 p_literal_end
386
387 HISTORY:
388 23-DEC-1998 Raja Reddy Kappera Created
389
390 **********************************************************************/
391
392
393 PROCEDURE get_movement
394 (ERRBUF OUT NOCOPY VARCHAR2,
395 RETCODE OUT NOCOPY VARCHAR2,
396 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
397 p_reported_year IN jl_co_gl_mg_literals.reported_year%TYPE,
398 p_period_start IN gl_periods.period_num%TYPE,
399 p_period_end IN gl_periods.period_num%TYPE,
400 p_literal_start IN jl_co_gl_mg_literals.literal_code%TYPE,
401 p_literal_end IN jl_co_gl_mg_literals.literal_code%TYPE
402 ) IS
403
404 x_mg_hdr_count NUMBER;
405
406 /********************************************************************
407 Cursor to select rows from jl_co_gl_mg_literals, jl_co_gl_mg_configs
408 and jl_co_gl_mg_ranges (accounting ranges) tables
409 ********************************************************************/
410
411 CURSOR literal_cur IS
412
413 SELECT mgl.literal_id literal_id,
414 mgl.foreign_reported_flag foreign_reported_flag,
415 mgl.domestic_reported_flag domestic_reported_flag,
416 mgl.foreign_description foreign_description,
417 mgc.config_id config_id,
418 mgc.reported_value reported_value,
419 mgc.movement_type movement_type,
420 mgc.threshold_value rep_threshold_value,
421 mgc.literal_literal_id literal_literal_id,
422 mgr.range_id range_id
423 FROM jl_co_gl_mg_ranges mgr,
424 jl_co_gl_mg_configs mgc,
425 jl_co_gl_mg_literals mgl
426 WHERE mgr.config_id = mgc.config_id
427 AND mgc.literal_id = mgl.literal_id
428 AND mgl.set_of_books_id = p_set_of_books_id
429 AND mgl.reported_year = p_reported_year
430 AND mgl.literal_code BETWEEN p_literal_start AND p_literal_end
431 ORDER BY mgc.movement_type,
432 mgl.literal_id,
433 mgc.config_id,
434 mgr.range_id;
435
436 /*************************************************
437 Cursor for selecting rows from jl_co_gl_trx table
438 *************************************************/
439
440 CURSOR trx_cur (x_movement_type jl_co_gl_mg_configs.movement_type%TYPE,
441 x_range_id jl_co_gl_mg_ranges.range_id%TYPE) IS
442
443 SELECT t.nit_id nit_id,
444 DECODE(x_movement_type,
445 '1', SUM(NVL(t.accounted_dr, 0)),
446 '2', SUM(NVL(t.accounted_cr, 0)),
447 '3', SUM(NVL(t.accounted_dr, 0)) - SUM(NVL(t.accounted_cr, 0)),
448 '4', SUM(NVL(t.accounted_cr, 0)) - SUM(NVL(t.accounted_dr, 0)),
449 0
450 ) amount
451 FROM jl_co_gl_trx t,
452 jl_co_gl_nits nit,
453 gl_sets_of_books sob1
454 WHERE t.set_of_books_id = sob1.set_of_books_id
455 AND nit.nit_id = t.nit_id
456 AND sob1.set_of_books_id = p_set_of_books_id
457 AND t.period_name IN (SELECT p.period_name
458 FROM gl_periods p,
459 gl_period_types pt,
460 gl_period_sets ps,
461 gl_sets_of_books sob2
462 WHERE p.period_year = p_reported_year
463 AND p.period_num BETWEEN p_period_start
464 AND p_period_end
465 AND p.adjustment_period_flag = 'N'
466 AND p.period_type = pt.period_type
467 AND pt.period_type = sob2.accounted_period_type
468 AND p.period_set_name = ps.period_set_name
469 AND ps.period_set_name = sob2.period_set_name
470 AND sob2.set_of_books_id = p_set_of_books_id
471 )
472 --AND t.code_combination_id IN
473 AND exists
474 (SELECT 1
475 FROM gl_code_combinations cc,
476 jl_co_gl_mg_ranges r
477 WHERE cc.code_combination_id = t.code_combination_id
478 AND r.range_id = x_range_id
479 AND cc.chart_of_accounts_id = sob1.chart_of_accounts_id
480 AND NVL(cc.segment1,0) BETWEEN NVL(r.segment1_low,0) AND NVL(r.segment1_high,0)
481 AND NVL(cc.segment2,0) BETWEEN NVL(r.segment2_low,0) AND NVL(r.segment2_high,0)
482 AND NVL(cc.segment3,0) BETWEEN NVL(r.segment3_low,0) AND NVL(r.segment3_high,0)
483 AND NVL(cc.segment4,0) BETWEEN NVL(r.segment4_low,0) AND NVL(r.segment4_high,0)
484 AND NVL(cc.segment5,0) BETWEEN NVL(r.segment5_low,0) AND NVL(r.segment5_high,0)
485 AND NVL(cc.segment6,0) BETWEEN NVL(r.segment6_low,0) AND NVL(r.segment6_high,0)
486 AND NVL(cc.segment7,0) BETWEEN NVL(r.segment7_low,0) AND NVL(r.segment7_high,0)
487 AND NVL(cc.segment8,0) BETWEEN NVL(r.segment8_low,0) AND NVL(r.segment8_high,0)
488 AND NVL(cc.segment9,0) BETWEEN NVL(r.segment9_low,0) AND NVL(r.segment9_high,0)
489 AND NVL(cc.segment10,0) BETWEEN NVL(r.segment10_low,0) AND NVL(r.segment10_high,0)
490 AND NVL(cc.segment11,0) BETWEEN NVL(r.segment11_low,0) AND NVL(r.segment11_high,0)
491 AND NVL(cc.segment12,0) BETWEEN NVL(r.segment12_low,0) AND NVL(r.segment12_high,0)
492 AND NVL(cc.segment13,0) BETWEEN NVL(r.segment13_low,0) AND NVL(r.segment13_high,0)
493 AND NVL(cc.segment14,0) BETWEEN NVL(r.segment14_low,0) AND NVL(r.segment14_high,0)
494 AND NVL(cc.segment15,0) BETWEEN NVL(r.segment15_low,0) AND NVL(r.segment15_high,0)
495 AND NVL(cc.segment16,0) BETWEEN NVL(r.segment16_low,0) AND NVL(r.segment16_high,0)
496 AND NVL(cc.segment17,0) BETWEEN NVL(r.segment17_low,0) AND NVL(r.segment17_high,0)
497 AND NVL(cc.segment18,0) BETWEEN NVL(r.segment18_low,0) AND NVL(r.segment18_high,0)
498 AND NVL(cc.segment19,0) BETWEEN NVL(r.segment19_low,0) AND NVL(r.segment19_high,0)
499 AND NVL(cc.segment20,0) BETWEEN NVL(r.segment20_low,0) AND NVL(r.segment20_high,0)
500 AND NVL(cc.segment21,0) BETWEEN NVL(r.segment21_low,0) AND NVL(r.segment21_high,0)
501 AND NVL(cc.segment22,0) BETWEEN NVL(r.segment22_low,0) AND NVL(r.segment22_high,0)
502 AND NVL(cc.segment23,0) BETWEEN NVL(r.segment23_low,0) AND NVL(r.segment23_high,0)
503 AND NVL(cc.segment24,0) BETWEEN NVL(r.segment24_low,0) AND NVL(r.segment24_high,0)
504 AND NVL(cc.segment25,0) BETWEEN NVL(r.segment25_low,0) AND NVL(r.segment25_high,0)
505 AND NVL(cc.segment26,0) BETWEEN NVL(r.segment26_low,0) AND NVL(r.segment26_high,0)
506 AND NVL(cc.segment27,0) BETWEEN NVL(r.segment27_low,0) AND NVL(r.segment27_high,0)
507 AND NVL(cc.segment28,0) BETWEEN NVL(r.segment28_low,0) AND NVL(r.segment28_high,0)
508 AND NVL(cc.segment29,0) BETWEEN NVL(r.segment29_low,0) AND NVL(r.segment29_high,0)
509 AND NVL(cc.segment30,0) BETWEEN NVL(r.segment30_low,0) AND NVL(r.segment30_high,0)
510 )
511 GROUP BY t.nit_id;
512
513 /*****************************************************
514 Cursor for selecting rows from jl_co_gl_balance table
515 *****************************************************/
516
517 -- Bug 4018828 - Comment out the join to period_name in the subquery retrieving
518 -- the max period num. This ensures that only the balances for last active
519 -- period for which there exists transactions are taken into account
520 CURSOR bal_cur (x_movement_type jl_co_gl_mg_configs.movement_type%TYPE,
521 x_range_id jl_co_gl_mg_ranges.range_id%TYPE) IS
522
523 SELECT b.nit_id nit_id,
524 DECODE(x_movement_type,
525 '5', SUM(NVL(b.begin_balance_dr, 0)) - SUM(NVL(b.begin_balance_cr, 0)) +
526 SUM(NVL(b.period_net_dr, 0)) - SUM(NVL(b.period_net_cr, 0)),
527 '6', SUM(NVL(b.begin_balance_cr, 0)) - SUM(NVL(b.begin_balance_dr, 0)) +
528 SUM(NVL(b.period_net_cr, 0)) - SUM(NVL(b.period_net_dr, 0)),
529 0
530 ) amount
531 FROM jl_co_gl_balances b,
532 gl_sets_of_books sob1
533 WHERE b.set_of_books_id = sob1.set_of_books_id
534 AND b.currency_code = sob1.currency_code
535 AND sob1.set_of_books_id = p_set_of_books_id
536 AND b.period_num =
537 (SELECT MAX(b1.period_num)
538 FROM jl_co_gl_balances b1
539 WHERE b.set_of_books_id = b1.set_of_books_id
540 AND b.code_combination_id = b1.code_combination_id
541 AND b.nit_id = b1.nit_id
542 --AND b.period_name = b1.period_name
543 AND b1.period_num <= p_period_end
544 AND b1.period_year = p_reported_year
545 )
546 AND b.period_name IN
547 (SELECT p.period_name
548 FROM gl_periods p,
549 gl_period_types pt,
550 gl_period_sets ps,
551 gl_sets_of_books sob2
552 WHERE p.period_year = p_reported_year
553 AND p.period_num BETWEEN p_period_start
554 AND p_period_end
555 AND p.adjustment_period_flag = 'N'
556 AND p.period_type = pt.period_type
557 AND pt.period_type = sob2.accounted_period_type
558 AND p.period_set_name = ps.period_set_name
559 AND ps.period_set_name = sob2.period_set_name
560 AND sob2.set_of_books_id = p_set_of_books_id
561 )
562 AND b.code_combination_id IN
563 (SELECT code_combination_id
564 FROM gl_code_combinations cc,
565 jl_co_gl_mg_ranges r,
566 gl_sets_of_books sob3
567 WHERE r.range_id = x_range_id
568 AND cc.chart_of_accounts_id = sob3.chart_of_accounts_id
569 AND sob3.set_of_books_id = p_set_of_books_id
570 AND NVL(cc.segment1,0) BETWEEN NVL(r.segment1_low,0) AND NVL(r.segment1_high,0)
571 AND NVL(cc.segment2,0) BETWEEN NVL(r.segment2_low,0) AND NVL(r.segment2_high,0)
572 AND NVL(cc.segment3,0) BETWEEN NVL(r.segment3_low,0) AND NVL(r.segment3_high,0)
573 AND NVL(cc.segment4,0) BETWEEN NVL(r.segment4_low,0) AND NVL(r.segment4_high,0)
574 AND NVL(cc.segment5,0) BETWEEN NVL(r.segment5_low,0) AND NVL(r.segment5_high,0)
575 AND NVL(cc.segment6,0) BETWEEN NVL(r.segment6_low,0) AND NVL(r.segment6_high,0)
576 AND NVL(cc.segment7,0) BETWEEN NVL(r.segment7_low,0) AND NVL(r.segment7_high,0)
577 AND NVL(cc.segment8,0) BETWEEN NVL(r.segment8_low,0) AND NVL(r.segment8_high,0)
578 AND NVL(cc.segment9,0) BETWEEN NVL(r.segment9_low,0) AND NVL(r.segment9_high,0)
579 AND NVL(cc.segment10,0) BETWEEN NVL(r.segment10_low,0) AND NVL(r.segment10_high,0)
580 AND NVL(cc.segment11,0) BETWEEN NVL(r.segment11_low,0) AND NVL(r.segment11_high,0)
581 AND NVL(cc.segment12,0) BETWEEN NVL(r.segment12_low,0) AND NVL(r.segment12_high,0)
582 AND NVL(cc.segment13,0) BETWEEN NVL(r.segment13_low,0) AND NVL(r.segment13_high,0)
583 AND NVL(cc.segment14,0) BETWEEN NVL(r.segment14_low,0) AND NVL(r.segment14_high,0)
584 AND NVL(cc.segment15,0) BETWEEN NVL(r.segment15_low,0) AND NVL(r.segment15_high,0)
585 AND NVL(cc.segment16,0) BETWEEN NVL(r.segment16_low,0) AND NVL(r.segment16_high,0)
586 AND NVL(cc.segment17,0) BETWEEN NVL(r.segment17_low,0) AND NVL(r.segment17_high,0)
587 AND NVL(cc.segment18,0) BETWEEN NVL(r.segment18_low,0) AND NVL(r.segment18_high,0)
588 AND NVL(cc.segment19,0) BETWEEN NVL(r.segment19_low,0) AND NVL(r.segment19_high,0)
589 AND NVL(cc.segment20,0) BETWEEN NVL(r.segment20_low,0) AND NVL(r.segment20_high,0)
590 AND NVL(cc.segment21,0) BETWEEN NVL(r.segment21_low,0) AND NVL(r.segment21_high,0)
591 AND NVL(cc.segment22,0) BETWEEN NVL(r.segment22_low,0) AND NVL(r.segment22_high,0)
592 AND NVL(cc.segment23,0) BETWEEN NVL(r.segment23_low,0) AND NVL(r.segment23_high,0)
593 AND NVL(cc.segment24,0) BETWEEN NVL(r.segment24_low,0) AND NVL(r.segment24_high,0)
594 AND NVL(cc.segment25,0) BETWEEN NVL(r.segment25_low,0) AND NVL(r.segment25_high,0)
595 AND NVL(cc.segment26,0) BETWEEN NVL(r.segment26_low,0) AND NVL(r.segment26_high,0)
596 AND NVL(cc.segment27,0) BETWEEN NVL(r.segment27_low,0) AND NVL(r.segment27_high,0)
597 AND NVL(cc.segment28,0) BETWEEN NVL(r.segment28_low,0) AND NVL(r.segment28_high,0)
598 AND NVL(cc.segment29,0) BETWEEN NVL(r.segment29_low,0) AND NVL(r.segment29_high,0)
599 AND NVL(cc.segment30,0) BETWEEN NVL(r.segment30_low,0) AND NVL(r.segment30_high,0)
600 )
601 GROUP BY b.nit_id;
602
603
604 BEGIN <<get_movement>>
605
606 x_trx_count := 0;
607 x_bal_count := 0;
608
609 fnd_message.set_name('FND', 'CONC-ARGUMENTS');
610 fnd_file.put_line( fnd_file.log, fnd_message.get);
611 fnd_file.put_line(fnd_file.log, '----------------------------------------');
612 fnd_message.set_name('JL', 'JL_CO_GL_MG_SET_OF_BOOKS_ID');
613 fnd_message.set_token('SET_OF_BOOKS_ID', p_set_of_books_id);
614 put_line( fnd_file.log, fnd_message.get);
615 fnd_message.set_name('JL', 'JL_CO_GL_MG_REPORTED_YEAR');
616 fnd_message.set_token('REPORTED_YEAR', p_reported_year);
617 put_line( fnd_file.log, fnd_message.get);
618 fnd_message.set_name('JL', 'JL_CO_GL_MG_PERIOD_START');
619 fnd_message.set_token('PERIOD_START', p_period_start);
620 put_line( fnd_file.log, fnd_message.get);
621 fnd_message.set_name('JL', 'JL_CO_GL_MG_PERIOD_END');
622 fnd_message.set_token('PERIOD_END', p_period_end);
623 put_line( fnd_file.log, fnd_message.get);
624 fnd_message.set_name('JL', 'JL_CO_GL_MG_LITERAL_START');
625 fnd_message.set_token('LITERAL_START', p_literal_start);
626 put_line( fnd_file.log, fnd_message.get);
627 fnd_message.set_name('JL', 'JL_CO_GL_MG_LITERAL_END');
628 fnd_message.set_token('LITERAL_END', p_literal_end);
629 put_line( fnd_file.log, fnd_message.get);
630 fnd_file.put_line(fnd_file.log, '----------------------------------------');
631
632
633 /******************************************************
634 Check for JL_CO_GL_MG_HEADERS.STATUS = 'Y'. If any row
635 exists with status of 'Y' then give a message to USER
636 and exist the procedure
637 ******************************************************/
638
639 BEGIN
640
641 SELECT count(*)
642 INTO count_status
643 FROM jl_co_gl_mg_headers
644 WHERE set_of_books_id = p_set_of_books_id
645 AND reported_year = p_reported_year
646 AND status = 'Y';
647
648 EXCEPTION
649
650 WHEN OTHERS THEN
651 NULL;
652
653 END;
654
655 IF count_status > 0 THEN
656
657 RAISE HEADERS_STATUS_Y;
658
659 END IF;
660
661
662 /****************************************
663 Delete rows from jl_co_gl_mg_lines and
664 jl_co_gl_mg_headers for given parameters
665 ****************************************/
666
667 BEGIN
668
669 DELETE FROM jl_co_gl_mg_lines
670 WHERE mg_header_id IN (SELECT mg_header_id
671 FROM jl_co_gl_mg_headers
672 WHERE set_of_books_id = p_set_of_books_id
673 AND reported_year = p_reported_year
674 )
675 AND literal_id IN (SELECT literal_id
676 FROM jl_co_gl_mg_literals
677 WHERE set_of_books_id = p_set_of_books_id
678 AND reported_year = p_reported_year
679 AND literal_code BETWEEN p_literal_start
680 AND p_literal_end
681 )
682 AND origin = 'A';
683
684 IF SQL%FOUND THEN
685 COMMIT;
686 fnd_message.set_name('JL', 'JL_CO_GL_MG_DELETE');
687 fnd_message.set_token('NUMBER', TO_CHAR(SQL%ROWCOUNT));
688 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LINES');
689 put_line( fnd_file.log, fnd_message.get);
690 ELSE
691 NULL;
692 fnd_message.set_name('JL', 'JL_CO_GL_MG_NOT_DELETE');
693 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LINES');
694 put_line( fnd_file.log, fnd_message.get);
695 END IF;
696
697 EXCEPTION
698
699 WHEN OTHERS THEN
700
701 x_error_code := SQLCODE;
702 x_error_text := SUBSTR(SQLERRM,1,200);
703 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
704
705 END;
706
707 BEGIN
708
709 DELETE FROM jl_co_gl_mg_headers
710 WHERE reported_year = p_reported_year
711 AND set_of_books_id = p_set_of_books_id
712 AND mg_header_id NOT IN (SELECT mg_header_id
713 FROM jl_co_gl_mg_lines
714 );
715
716 IF SQL%FOUND THEN
717 COMMIT;
718 fnd_message.set_name('JL', 'JL_CO_GL_MG_DELETE');
719 fnd_message.set_token('NUMBER', TO_CHAR(SQL%ROWCOUNT));
720 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_HEADERS');
721 put_line( fnd_file.log, fnd_message.get);
722 ELSE
723 NULL;
724 fnd_message.set_name('JL', 'JL_CO_GL_MG_NOT_DELETE');
725 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_HEADERS');
726 put_line( fnd_file.log, fnd_message.get);
727 END IF;
728
729 EXCEPTION
730
731 WHEN OTHERS THEN
732
733 x_error_code := SQLCODE;
734 x_error_text := SUBSTR(SQLERRM,1,200);
735 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
736
737 END;
738
739 /***********************
740 Find who_columns values
741 ***********************/
742
743 find_who_columns;
744
745 /******************************************************************
746 Insert a row for the given parameters in jl_co_gl_mg_headers table
747 ******************************************************************/
748
749 BEGIN
750
751 BEGIN
752
753 SELECT jl_co_gl_mg_headers_s.NEXTVAL
754 INTO x_mg_header_id
755 FROM SYS.DUAL;
756
757 END;
758
759 INSERT INTO jl_co_gl_mg_headers
760 (mg_header_id,
761 set_of_books_id,
762 reported_year,
763 status,
764 created_by,
765 creation_date,
766 last_updated_by,
767 last_update_date,
768 last_update_login
769 )
770 VALUES
771 (x_mg_header_id, /*mg_header_id*/
772 p_set_of_books_id, /*set_of_books_id*/
773 p_reported_year, /*reported_year*/
774 'N', /*status*/
775 x_last_updated_by, /*created_by*/
776 x_sysdate, /*creation_date*/
777 x_last_updated_by, /*last_updated_by*/
778 x_sysdate, /*last_update_date*/
779 x_last_update_login /*last_update_login*/
780 );
781
782 COMMIT;
783
784 fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
785 fnd_message.set_token('NUMBER', '1');
786 fnd_message.set_token('TYPE', ' ');
787 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_HEADERS');
788 put_line( fnd_file.log, fnd_message.get);
789
790 EXCEPTION
791
792 WHEN OTHERS THEN
793
794 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
795 fnd_message.set_token('GENERIC_TEXT',
796 'Exception "OTHERS" while inserting into jl_co_gl_mg_headers table');
797 x_error_text := SUBSTR(fnd_message.get, 1, 100);
798 ROLLBACK;
799 app_exception.raise_exception (exception_type => 'APP',
800 exception_code =>
801 jl_zz_fa_utilities_pkg.get_app_errnum('AR', 'GENERIC_MESSAGE'),
802 exception_text => x_error_text);
803
804 END;
805
806
807 FOR literal_rec IN literal_cur LOOP
808
809 get_move_rec.mg_header_id := x_mg_header_id;
810 get_move_rec.literal_id := literal_rec.literal_id;
811 get_move_rec.foreign_reported_flag := literal_rec.foreign_reported_flag;
812 get_move_rec.foreign_description := literal_rec.foreign_description;
813 get_move_rec.domestic_reported_flag := literal_rec.domestic_reported_flag;
814 get_move_rec.reported_value := literal_rec.reported_value;
815 get_move_rec.config_id := literal_rec.config_id;
816 get_move_rec.literal_literal_id := literal_rec.literal_literal_id;
817 get_move_rec.range_id := literal_rec.range_id;
818 get_move_rec.send_back_flag := 'N';
819 get_move_rec.origin := 'A';
820
821 /****************************************************************************
822 Select nit_id and sum(amount) from jl_co_gl_balances and jl_co_gl_trx tables
823 ****************************************************************************/
824
825 IF literal_rec.movement_type IN ('1', '2', '3', '4') THEN
826
827 FOR trx_rec IN trx_cur (literal_rec.movement_type,
828 literal_rec.range_id) LOOP
829
830 IF trx_rec.amount > 0 THEN
831
832 get_move_rec.nit_id := trx_rec.nit_id;
833 get_move_rec.amount := trx_rec.amount;
834
835 SELECT jl_co_gl_mg_lines_s.NEXTVAL
836 INTO get_move_rec.mg_line_id
837 FROM SYS.DUAL;
838
839 get_movement_insert (get_move_rec);
840
841 x_trx_count := x_trx_count + 1;
842
843 /*get_move_rec := null_get_move_rec;*/
844
845 END IF;
846
847 END LOOP;
848
849 ELSE
850
851 FOR bal_rec IN bal_cur (literal_rec.movement_type,
852 literal_rec.range_id) LOOP
853
854 IF bal_rec.amount > 0 THEN
855
856 get_move_rec.nit_id := bal_rec.nit_id;
857 get_move_rec.amount := bal_rec.amount;
858
859 SELECT jl_co_gl_mg_lines_s.NEXTVAL
860 INTO get_move_rec.mg_line_id
861 FROM SYS.DUAL;
862
863 get_movement_insert (get_move_rec);
864
865 x_bal_count := x_bal_count + 1;
866
867 /*get_move_rec := null_get_move_rec;*/
868
869 END IF;
870
871 END LOOP;
872
873 END IF;
874
875 END LOOP;
876
877 IF x_trx_count > 0 THEN
878
879 fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
880 fnd_message.set_token('NUMBER', TO_CHAR(x_trx_count));
881 fnd_message.set_token('TYPE', 'NIT_TRANSACTIONS');
882 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LINES');
883 put_line( fnd_file.log, fnd_message.get);
884
885 END IF;
886
887 IF x_bal_count > 0 THEN
888
889 fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
890 fnd_message.set_token('NUMBER', TO_CHAR(x_bal_count));
891 fnd_message.set_token('TYPE', 'NIT_BALANCES');
892 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LINES');
893 put_line( fnd_file.log, fnd_message.get);
894
895 END IF;
896
897 IF x_trx_count = 0 AND x_bal_count = 0 THEN
898 DELETE FROM jl_co_gl_mg_headers
899 WHERE reported_year = p_reported_year
900 AND set_of_books_id = p_set_of_books_id
901 AND mg_header_id NOT IN (SELECT mg_header_id
902 FROM jl_co_gl_mg_lines
903 );
904 COMMIT;
905
906 END IF;
907
908 /**************************************************************************
909 Update JL_CO_GL_MG_LITERALS.PROCESSED_FLAG to 'M' for the given Parameters
910 **************************************************************************/
911
912 UPDATE jl_co_gl_mg_literals
913 SET processed_flag = 'M'
914 WHERE set_of_books_id = p_set_of_books_id
915 AND reported_year = p_reported_year
916 AND literal_code BETWEEN p_literal_start AND p_literal_end;
917
918 IF SQL%FOUND THEN
919 COMMIT;
920 END IF;
921
922 EXCEPTION
923
924 WHEN HEADERS_STATUS_Y THEN
925
926 x_message := '----***************** W A R N I N G **********************----';
927 put_line( fnd_file.log, x_message);
928 fnd_message.set_name('JL', 'JL_CO_GL_MG_FILE_SENT');
929 fnd_message.set_token('YEAR', p_reported_year);
930 x_error_text := fnd_message.get;
931 put_line( fnd_file.log, x_error_text);
932 x_error_text := SUBSTR(x_error_text, 1, 100);
933 x_message := '----******************************************************----';
934 put_line( fnd_file.log, x_message);
935 app_exception.raise_exception (exception_type => 'APP',
936 exception_code =>
937 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_FILE_SENT'),
938 exception_text => x_error_text);
939
940 WHEN OTHERS THEN
941
942 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
943 fnd_file.put_line( fnd_file.log, fnd_message.get);
944 x_error_code := SQLCODE;
945 x_error_text := SUBSTR(SQLERRM,1,200);
946 ROLLBACK;
947 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
948
949 END get_movement;
950
951
952
953
954 /*******************************************************************
955 PROCEDURE
956 threshold
957
958 DESCRIPTION
959 Use this procedure to apply Parent Report Grouping Threshold,
960 Literal Threshold and Child Report Grouping Threshold to the rows
961 in jl_co_gl_mg_lines table.
962
963 PURPOSE:
964 Oracle Applications Rel 11.0
965
966 PARAMETERS:
967 p_set_of_books_id
968 p_reported_year
969 p_literal_start
970 p_literal_end
971
972 HISTORY:
973 23-DEC-1998 Raja Reddy Kappera Created
974
975 *******************************************************************/
976
977
978 PROCEDURE threshold
979 (ERRBUF OUT NOCOPY VARCHAR2,
980 RETCODE OUT NOCOPY VARCHAR2,
981 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
982 p_reported_year IN jl_co_gl_mg_literals.reported_year%TYPE,
983 p_literal_start IN jl_co_gl_mg_literals.literal_code%TYPE,
984 p_literal_end IN jl_co_gl_mg_literals.literal_code%TYPE
985 ) IS
986
987 x_foreign_reported_flag jl_co_gl_mg_literals.foreign_reported_flag%TYPE;
988 x_domestic_reported_flag jl_co_gl_mg_literals.domestic_reported_flag%TYPE;
989 x_threshold_foreign_flag jl_co_gl_mg_literals.threshold_foreign_flag%TYPE;
990 x_threshold_domestic_flag jl_co_gl_mg_literals.threshold_domestic_flag%TYPE;
991 x_lit_threshold_value jl_co_gl_mg_literals.threshold_value%TYPE;
992 x_config_id_parent jl_co_gl_mg_configs.config_id_parent%TYPE;
993
994 /************************************
995 Parent Report Group Threshold Cursor
996 ************************************/
997
998 CURSOR rg_threshold_cur IS
999
1000 SELECT mgl.mg_header_id mg_header_id,
1001 mgl.literal_id literal_id,
1002 mgl.reported_value reported_value,
1003 mgl.reported_flag reported_flag,
1004 mgl.nit_id nit_id,
1005 mgl.third_party_name third_party_name,
1006 mgl.config_id config_id,
1007 c.threshold_value threshold_value,
1008 SUM(mgl.first_reported_value) first_reported_value,
1009 SUM(mgl.second_reported_value) second_reported_value
1010 FROM jl_co_gl_mg_configs c,
1011 jl_co_gl_mg_literals l,
1012 jl_co_gl_mg_lines mgl,
1013 jl_co_gl_mg_headers mgh
1014 WHERE mgl.mg_header_id = mgh.mg_header_id
1015 AND mgh.reported_year = p_reported_year
1016 AND mgh.set_of_books_id = p_set_of_books_id
1017 AND mgl.literal_id = l.literal_id
1018 AND l.literal_code BETWEEN p_literal_start AND p_literal_end
1019 AND mgl.config_id = c.config_id
1020 AND c.config_id_parent IS NULL
1021 GROUP BY mgl.mg_header_id,
1022 mgl.literal_id,
1023 mgl.reported_value,
1024 mgl.reported_flag,
1025 mgl.nit_id,
1026 mgl.third_party_name,
1027 mgl.config_id,
1028 c.threshold_value
1029 ORDER BY mgl.mg_header_id,
1030 mgl.literal_id,
1031 mgl.reported_value,
1032 mgl.reported_flag,
1033 mgl.nit_id,
1034 mgl.third_party_name,
1035 mgl.config_id,
1036 c.threshold_value;
1037
1038 /************************
1039 Literal Threshold Cursor
1040 ************************/
1041
1042 CURSOR lit_threshold_cur IS
1043
1044 SELECT mgl.mg_header_id mg_header_id,
1045 mgl.nit_id nit_id,
1046 mgl.literal_literal_id literal_literal_id,
1047 l.threshold_value threshold_value,
1048 l.threshold_foreign_flag threshold_foreign_flag,
1049 l.threshold_domestic_flag threshold_domestic_flag,
1050 SUM(mgl.first_reported_value) first_reported_value,
1051 SUM(mgl.second_reported_value) second_reported_value
1052 FROM jl_co_gl_mg_literals l,
1053 jl_co_gl_mg_lines mgl,
1054 jl_co_gl_mg_headers mgh
1055 WHERE mgl.mg_header_id = mgh.mg_header_id
1056 AND mgh.reported_year = p_reported_year
1057 AND mgh.set_of_books_id = p_set_of_books_id
1058 AND mgl.literal_literal_id = l.literal_id
1059 AND l.literal_code BETWEEN p_literal_start AND p_literal_end
1060 GROUP BY mgl.mg_header_id,
1061 mgl.nit_id,
1062 mgl.literal_literal_id,
1063 l.threshold_value,
1064 l.threshold_foreign_flag,
1065 l.threshold_domestic_flag
1066 ORDER BY mgl.mg_header_id,
1067 mgl.nit_id,
1068 mgl.literal_literal_id,
1069 l.threshold_value,
1070 l.threshold_foreign_flag,
1071 l.threshold_domestic_flag;
1072
1073 /***********************************
1074 Child Report Group Threshold Cursor
1075 ***********************************/
1076
1077 CURSOR child_threshold_cur IS
1078
1079 SELECT mgl.mg_header_id mg_header_id,
1080 mgl.config_id config_id,
1081 mgl.nit_id nit_id
1082 FROM jl_co_gl_mg_configs c,
1083 jl_co_gl_mg_literals l,
1084 jl_co_gl_mg_lines mgl,
1085 jl_co_gl_mg_headers mgh
1086 WHERE mgl.mg_header_id = mgh.mg_header_id
1087 AND mgh.reported_year = p_reported_year
1088 AND mgh.set_of_books_id = p_set_of_books_id
1089 AND mgl.config_id = c.config_id
1090 AND c.literal_id = l.literal_id
1091 AND c.config_id_parent IS NULL
1092 AND l.literal_code BETWEEN p_literal_start AND p_literal_end
1093 AND mgl.reported_flag = 'N'
1094 GROUP BY mgl.mg_header_id,
1095 mgl.config_id,
1096 mgl.nit_id
1097 ORDER BY mgl.mg_header_id,
1098 mgl.config_id,
1099 mgl.nit_id;
1100
1101
1102 BEGIN <<threshold>>
1103
1104 fnd_message.set_name('FND', 'CONC-ARGUMENTS');
1105 fnd_file.put_line( fnd_file.log, fnd_message.get);
1106 fnd_file.put_line(fnd_file.log, '----------------------------------------');
1107 fnd_message.set_name('JL', 'JL_CO_GL_MG_SET_OF_BOOKS_ID');
1108 fnd_message.set_token('SET_OF_BOOKS_ID', p_set_of_books_id);
1109 put_line( fnd_file.log, fnd_message.get);
1110 fnd_message.set_name('JL', 'JL_CO_GL_MG_REPORTED_YEAR');
1111 fnd_message.set_token('REPORTED_YEAR', p_reported_year);
1112 put_line( fnd_file.log, fnd_message.get);
1113 fnd_message.set_name('JL', 'JL_CO_GL_MG_LITERAL_START');
1114 fnd_message.set_token('LITERAL_START', p_literal_start);
1115 put_line( fnd_file.log, fnd_message.get);
1116 fnd_message.set_name('JL', 'JL_CO_GL_MG_LITERAL_END');
1117 fnd_message.set_token('LITERAL_END', p_literal_end);
1118 put_line( fnd_file.log, fnd_message.get);
1119 fnd_file.put_line(fnd_file.log, '----------------------------------------');
1120
1121 /******************************************************
1122 Check for JL_CO_GL_MG_HEADERS.STATUS = 'Y'. If any row
1123 exists with status of 'Y' then give a message to USER
1124 and exist the procedure
1125 ******************************************************/
1126
1127 BEGIN
1128
1129 SELECT count(*)
1130 INTO count_status
1131 FROM jl_co_gl_mg_headers
1132 WHERE set_of_books_id = p_set_of_books_id
1133 AND reported_year = p_reported_year
1134 AND status = 'Y';
1135
1136 EXCEPTION
1137
1138 WHEN OTHERS THEN
1139 NULL;
1140
1141 END;
1142
1143 IF count_status > 0 THEN
1144
1145 RAISE HEADERS_STATUS_Y;
1146
1147 END IF;
1148
1149
1150 FOR rg_threshold_rec IN rg_threshold_cur LOOP
1151
1152 /**************************************************************
1153 Call the procedure to get nit info. for each row of the cursor
1154 **************************************************************/
1155
1156 get_nit_info(rg_threshold_rec.nit_id);
1157
1158 /***********************************************************************
1159 Select Lietarl information and Configs(Reported Group) information from
1160 jl_co_gl_literals and jl_co_gl_configs for each row of the cursor
1161 ***********************************************************************/
1162
1163 BEGIN
1164
1165 SELECT l.foreign_reported_flag,
1166 l.domestic_reported_flag,
1167 l.threshold_foreign_flag,
1168 l.threshold_domestic_flag
1169 INTO x_foreign_reported_flag,
1170 x_domestic_reported_flag,
1171 x_threshold_foreign_flag,
1172 x_threshold_domestic_flag
1173 FROM jl_co_gl_mg_literals l
1174 WHERE l.literal_id = rg_threshold_rec.literal_id;
1175
1176 EXCEPTION
1177
1178 WHEN NO_DATA_FOUND THEN
1179
1180 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1181 fnd_message.set_token('GENERIC_TEXT',
1182 'Exception "NO_DATA_FOUND" for selection of flags from JL_CO_GL_MG_LITERALS table');
1183 put_line(fnd_file.log, fnd_message.get);
1184 x_error_code := SQLCODE;
1185 x_error_text := SUBSTR(SQLERRM,1,200);
1186 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1187
1188 WHEN TOO_MANY_ROWS THEN
1189
1190 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1191 fnd_message.set_token('GENERIC_TEXT',
1192 'Exception "TOO_MANY_ROWS" for selection of flags from JL_CO_GL_MG_LITERALS table');
1193 put_line(fnd_file.log, fnd_message.get);
1194 x_error_code := SQLCODE;
1195 x_error_text := SUBSTR(SQLERRM,1,200);
1196 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1197
1198 WHEN OTHERS THEN
1199
1200 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1201 fnd_message.set_token('GENERIC_TEXT',
1202 'Exception "OTHERS" for selection of flags from JL_CO_GL_MG_LITERALS table');
1203 put_line(fnd_file.log, fnd_message.get);
1204 x_error_code := SQLCODE;
1205 x_error_text := SUBSTR(SQLERRM,1,200);
1206 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1207
1208 END;
1209
1210 /*********************************************
1211 Update jl_co_gl_mg_lines.reported_flag to "N"
1212 that are not required to be reported
1213 *********************************************/
1214
1215 IF (x_type = 'FOREIGN_ENTITY' AND
1216 x_foreign_reported_flag = 'N') OR
1217 (x_type <> 'FOREIGN_ENTITY' AND
1218 x_domestic_reported_flag = 'N') THEN
1219
1220 UPDATE jl_co_gl_mg_lines
1221 SET reported_flag = 'N'
1222 WHERE mg_header_id = rg_threshold_rec.mg_header_id
1223 AND literal_id = rg_threshold_rec.literal_id
1224 AND reported_value = rg_threshold_rec.reported_value
1225 AND reported_flag = rg_threshold_rec.reported_flag
1226 AND nit_id = rg_threshold_rec.nit_id
1227 AND third_party_name = rg_threshold_rec.third_party_name
1228 AND config_id = rg_threshold_rec.config_id;
1229
1230 COMMIT;
1231
1232 ELSE
1233
1234 /*********************************************
1235 Apply Parent Report Grouping Threshold values
1236 *********************************************/
1237
1238 IF (x_type = 'FOREIGN_ENTITY' AND
1239 x_threshold_foreign_flag = 'Y') OR
1240 (x_type <> 'FOREIGN_ENTITY' AND
1241 x_threshold_domestic_flag = 'Y') THEN
1242
1243 IF (rg_threshold_rec.reported_value = '1' AND
1244 rg_threshold_rec.first_reported_value < rg_threshold_rec.threshold_value) OR
1245 (rg_threshold_rec.reported_value = '2' AND
1246 rg_threshold_rec.second_reported_value < rg_threshold_rec.threshold_value) THEN
1247
1248 UPDATE jl_co_gl_mg_lines
1249 SET reported_flag = 'N'
1250 WHERE mg_header_id = rg_threshold_rec.mg_header_id
1251 AND literal_id = rg_threshold_rec.literal_id
1252 AND reported_value = rg_threshold_rec.reported_value
1253 AND reported_flag = rg_threshold_rec.reported_flag
1254 AND nit_id = rg_threshold_rec.nit_id
1255 AND third_party_name = rg_threshold_rec.third_party_name
1256 AND config_id = rg_threshold_rec.config_id;
1257
1258 COMMIT;
1259
1260 END IF;
1261
1262 END IF;
1263
1264 END IF;
1265
1266 END LOOP;
1267
1268
1269 FOR lit_threshold_rec IN lit_threshold_cur LOOP
1270
1271 /**************************************************************
1272 Call the procedure to get nit info. for each row of the cursor
1273 **************************************************************/
1274
1275 get_nit_info(lit_threshold_rec.nit_id);
1276
1277 /******************************
1278 Apply Literal Threshold values
1279 ******************************/
1280
1281 IF (x_type = 'FOREIGN_ENTITY' AND
1282 lit_threshold_rec.threshold_foreign_flag = 'Y') OR
1283 (x_type <> 'FOREIGN_ENTITY' AND
1284 lit_threshold_rec.threshold_domestic_flag = 'Y') THEN
1285
1286 IF ((lit_threshold_rec.first_reported_value +
1287 lit_threshold_rec.second_reported_value) >=
1288 lit_threshold_rec.threshold_value) THEN
1289
1290 UPDATE jl_co_gl_mg_lines
1291 SET reported_flag = 'Y'
1292 WHERE mg_header_id = lit_threshold_rec.mg_header_id
1293 AND literal_literal_id = lit_threshold_rec.literal_literal_id
1294 AND nit_id = lit_threshold_rec.nit_id;
1295
1296 COMMIT;
1297
1298 END IF;
1299
1300 END IF;
1301
1302 END LOOP;
1303
1304
1305 FOR child_threshold_rec IN child_threshold_cur LOOP
1306
1307 /************************************************************************
1308 Update jl_co_gl_mg_lines for the selected parent config_id in the cursor
1309 ************************************************************************/
1310
1311 UPDATE jl_co_gl_mg_lines
1312 SET reported_flag = 'N'
1313 WHERE mg_header_id = child_threshold_rec.mg_header_id
1314 AND config_id IN (SELECT config_id
1315 FROM jl_co_gl_mg_configs
1316 WHERE config_id_parent = child_threshold_rec.config_id
1317 )
1318 AND nit_id = child_threshold_rec.nit_id;
1319
1320 COMMIT;
1321
1322 END LOOP;
1323
1324 /**************************************************************************
1325 Update JL_CO_GL_MG_LITERALS.PROCESSED_FLAG to 'M' for the given Parameters
1326 **************************************************************************/
1327
1328 UPDATE jl_co_gl_mg_literals
1329 SET processed_flag = 'T'
1330 WHERE set_of_books_id = p_set_of_books_id
1331 AND reported_year = p_reported_year
1332 AND literal_code BETWEEN p_literal_start AND p_literal_end
1333 AND processed_flag = 'M';
1334
1335 IF SQL%FOUND THEN
1336 COMMIT;
1337 END IF;
1338
1339 /****************************************************
1340 Check for JL_CO_GL_MG_LITERALS.PROCESSED_FLAG = 'N'.
1341 If any row exists, give a message to USER
1342 ****************************************************/
1343
1344 SELECT count(*)
1345 INTO count_process_flag
1346 FROM jl_co_gl_mg_literals
1347 WHERE set_of_books_id = p_set_of_books_id
1348 AND reported_year = p_reported_year
1349 AND LENGTH(literal_code) = 4
1350 AND processed_flag = 'N';
1351
1352 IF count_process_flag > 0 THEN
1353
1354 x_message := '----***************** W A R N I N G **********************----';
1355 put_line( fnd_file.log, x_message);
1356 fnd_message.set_name('JL', 'JL_CO_GL_MG_TH_ALERT');
1357 fnd_message.set_token('NUMBER', TO_CHAR(count_process_flag));
1358 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LITERALS');
1359 x_error_text := fnd_message.get;
1360 put_line( fnd_file.log, x_error_text);
1361 x_error_text := SUBSTR(x_error_text, 1, 100);
1362 x_message := '----******************************************************----';
1363 put_line( fnd_file.log, x_message);
1364 app_exception.raise_exception (exception_type => 'APP',
1365 exception_code =>
1366 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_TH_ALERT'),
1367 exception_text => x_error_text);
1368
1369 END IF;
1370
1371 EXCEPTION
1372
1373 WHEN HEADERS_STATUS_Y THEN
1374
1375 x_message := '----***************** W A R N I N G **********************----';
1376 put_line( fnd_file.log, x_message);
1377 fnd_message.set_name('JL', 'JL_CO_GL_MG_FILE_SENT');
1378 fnd_message.set_token('YEAR', p_reported_year);
1379 x_error_text := fnd_message.get;
1380 put_line( fnd_file.log, x_error_text);
1381 x_error_text := SUBSTR(x_error_text, 1, 100);
1382 x_message := '----******************************************************----';
1383 put_line( fnd_file.log, x_message);
1384 app_exception.raise_exception (exception_type => 'APP',
1385 exception_code =>
1386 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_FILE_SENT'),
1387 exception_text => x_error_text);
1388
1389 WHEN OTHERS THEN
1390
1391 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
1392 fnd_file.put_line( fnd_file.log, fnd_message.get);
1393 x_error_code := SQLCODE;
1394 x_error_text := SUBSTR(SQLERRM,1,200);
1395 ROLLBACK;
1396 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1397
1398 END threshold;
1399
1400
1401
1402 /*********************************************************************
1403 PROCEDURE
1404 generate_mg_media
1405
1406 DESCRIPTION
1407 Use this procedure to generate magnetic media flat file in standard
1408 out directory of application i.e. $APPLCSF/$APPLOUT
1409 with a file name consisting of request_id
1410
1411 PURPOSE:
1412 Oracle Applications Rel 11.0
1413
1414 PARAMETERS:
1415 p_set_of_books_id
1416 p_reported_year
1417 p_label
1418
1419 HISTORY:
1420 23-DEC-1998 Raja Reddy Kappera Created
1421
1422 *********************************************************************/
1423
1424
1425 PROCEDURE generate_mg_media
1426 (ERRBUF OUT NOCOPY VARCHAR2,
1427 RETCODE OUT NOCOPY VARCHAR2,
1428 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
1429 p_legal_entity_id IN xle_entity_profiles.legal_entity_id%TYPE,
1430 p_reported_year IN jl_co_gl_mg_literals.reported_year%TYPE,
1431 p_label IN VARCHAR2
1432 ) IS
1433
1434 /*************************************************************************
1435 Get the location id from jg_zz_company_info.get_location_id.
1436 Profile option JGZZ_COMP_ID is to be setup for non multi org environments
1437 *************************************************************************/
1438
1439 --p_location_id hr.hr_locations_all.location_id%TYPE := jg_zz_company_info.get_location_id;
1440
1441 company_name xle_firstparty_information_v.name%TYPE;
1442 company_nit xle_firstparty_information_v.registration_number%TYPE;
1443 --company_vdigit hr.hr_locations_all.global_attribute12%TYPE;
1444 economic_activity_code xle_firstparty_information_v.activity_code%TYPE;
1445 company_address xle_firstparty_information_v.address_line_1%TYPE;
1446 area_code hr.hr_locations_all.telephone_number_1%TYPE;
1447 telephone_number hr.hr_locations_all.telephone_number_2%TYPE;
1448 city_code xle_firstparty_information_v.town_or_city%TYPE;
1449 identifi_register VARCHAR2(2000);
1450 movement_register VARCHAR2(2000);
1451 closed_register VARCHAR2(2000);
1452 count_literal NUMBER;
1453 total_value NUMBER;
1454 MG_GENERAL_ALERT EXCEPTION;
1455
1456
1457 CURSOR generate_cur IS
1458
1459 SELECT mglit.literal_code literal_code,
1460 DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit)
1461 nit_number,
1462 SUM(mgl.first_reported_value) first_reported_value,
1463 SUM(mgl.second_reported_value) second_reported_value
1464 FROM jl_co_gl_mg_lines mgl,
1465 jl_co_gl_nits n,
1466 jl_co_gl_mg_literals mglit,
1467 jl_co_gl_mg_headers mgh
1468 WHERE mgl.mg_header_id = mgh.mg_header_id
1469 AND mgh.reported_year = p_reported_year
1470 AND mgh.set_of_books_id = p_set_of_books_id
1471 AND mgl.reported_flag = 'Y'
1472 AND mgl.nit_id = n.nit_id
1473 AND mgl.literal_id = mglit.literal_id
1474 GROUP BY mglit.literal_code,
1475 DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit)
1476 ORDER BY mglit.literal_code,
1477 DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit);
1478
1479
1480 BEGIN <<generate_mg_media>>
1481
1482 fnd_message.set_name('FND', 'CONC-ARGUMENTS');
1483 fnd_file.put_line( fnd_file.log, fnd_message.get);
1484 fnd_file.put_line(fnd_file.log, '----------------------------------------');
1485 fnd_message.set_name('JL', 'JL_CO_GL_MG_LEGAL_ENTITY_ID');
1486 fnd_message.set_token('LEGAL_ENTITY_ID', p_legal_entity_id);
1487 put_line( fnd_file.log, fnd_message.get);
1488 fnd_message.set_name('JL', 'JL_CO_GL_MG_SET_OF_BOOKS_ID');
1489 fnd_message.set_token('SET_OF_BOOKS_ID', p_set_of_books_id);
1490 put_line( fnd_file.log, fnd_message.get);
1491 fnd_message.set_name('JL', 'JL_CO_GL_MG_REPORTED_YEAR');
1492 fnd_message.set_token('REPORTED_YEAR', p_reported_year);
1493 put_line( fnd_file.log, fnd_message.get);
1494 fnd_message.set_name('JL', 'JL_CO_GL_MG_LABEL');
1495 fnd_message.set_token('LABEL', p_label);
1496 put_line( fnd_file.log, fnd_message.get);
1497 fnd_file.put_line(fnd_file.log, '----------------------------------------');
1498
1499
1500 /******************************************************
1501 Check for JL_CO_GL_MG_HEADERS.STATUS = 'Y'. If any row
1502 exists with status of 'Y' then give a message to USER
1503 and exist the procedure
1504 ******************************************************/
1505
1506 BEGIN
1507
1508 SELECT count(*)
1509 INTO count_status
1510 FROM jl_co_gl_mg_headers
1511 WHERE set_of_books_id = p_set_of_books_id
1512 AND reported_year = p_reported_year
1513 AND status = 'Y';
1514
1515 EXCEPTION
1516
1517 WHEN OTHERS THEN
1518 NULL;
1519
1520 END;
1521
1522 IF count_status > 0 THEN
1523
1524 RAISE HEADERS_STATUS_Y;
1525
1526 END IF;
1527
1528 /*********************************************************
1529 Check for JL_CO_GL_MG_LITERALS.PROCESSED_FLAG = 'T'. If
1530 any row exists other than 'T' then give a message to USER
1531 and exit the procedure
1532 *********************************************************/
1533
1534 BEGIN
1535
1536 SELECT count(*)
1537 INTO count_process_flag
1538 FROM jl_co_gl_mg_literals
1539 WHERE set_of_books_id = p_set_of_books_id
1540 AND reported_year = p_reported_year
1541 AND LENGTH(literal_code) = 4
1542 AND processed_flag <> 'T';
1543
1544 EXCEPTION
1545
1546 WHEN OTHERS THEN
1547 NULL;
1548
1549 END;
1550
1551 IF count_process_flag > 0 THEN
1552
1553 RAISE MG_GENERAL_ALERT;
1554
1555 END IF;
1556
1557 /***************************************************************
1558 Select Company Information required for identification register
1559 and closed register from HR_LOCATIONS Table
1560 ***************************************************************/
1561
1562 BEGIN
1563
1564 SELECT NVL(le.address_line_1||DECODE(le.address_line_2, NULL, ' ', ',')||
1565 le.address_line_2||DECODE(le.address_line_3, NULL, ' ', ',')||
1566 le.address_line_3, 'No Address') address,
1567 NVL(hr.telephone_number_1, '0'),
1568 NVL(hr.telephone_number_2, '0'),
1569 NVL(le.name, 'No Company Name'),
1570 NVL(le.registration_number, 'No Nit'),
1571 --NVL(global_attribute12, 'x'),
1572 NVL(le.town_or_city, 'x'),
1573 NVL(le.activity_code, 'x')
1574 INTO company_address,
1575 area_code,
1576 telephone_number,
1577 company_name,
1578 company_nit,
1579 --company_vdigit,
1580 city_code,
1581 economic_activity_code
1582 FROM xle_firstparty_information_v le,
1583 hr_locations hr
1584 WHERE le.legal_entity_id = p_legal_entity_id
1585 AND hr.location_id = le.location_id;
1586
1587 EXCEPTION
1588
1589 WHEN NO_DATA_FOUND THEN
1590
1591 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1592 fnd_message.set_token('GENERIC_TEXT',
1593 'Exception "NO_DATA_FOUND" while selecting company information');
1594 put_line(fnd_file.log, fnd_message.get);
1595 x_error_code := SQLCODE;
1596 x_error_text := SUBSTR(SQLERRM,1,200);
1597 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1598
1599 WHEN TOO_MANY_ROWS THEN
1600
1601 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1602 fnd_message.set_token('GENERIC_TEXT',
1603 'Exception "TOO_MANY_ROWS" while selecting company information');
1604 put_line(fnd_file.log, fnd_message.get);
1605 x_error_code := SQLCODE;
1606 x_error_text := SUBSTR(SQLERRM,1,200);
1607 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1608
1609 WHEN OTHERS THEN
1610
1611 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
1612 fnd_message.set_token('GENERIC_TEXT',
1613 'Exception "OTHERS" while selecting company information');
1614 put_line(fnd_file.log, fnd_message.get);
1615 x_error_code := SQLCODE;
1616 x_error_text := SUBSTR(SQLERRM,1,200);
1617 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1618
1619 END;
1620
1621
1622 identifi_register := '1'||
1623 TO_CHAR(p_reported_year)||
1624 '31'||
1625 LPAD(RTRIM(company_nit), 14, '0')||
1626 --RTRIM(company_vdigit)||
1627 ' '||
1628 RPAD(company_name, 60, ' ')||
1629 LPAD(RTRIM(economic_activity_code), 4, '0')||
1630 ' '||
1631 TO_CHAR(SYSDATE, 'YYYYMMDD')||
1632 LPAD(RTRIM(p_label), 4, '0')||
1633 ' ';
1634
1635 count_literal := 0;
1636 total_value := 0;
1637
1638 FOR generate_rec IN generate_cur LOOP
1639
1640 IF generate_rec.nit_number = foreign_nit THEN
1641
1642 /***********************************************
1643 Report foreign if literal foreign flag is 'Yes'
1644 ***********************************************/
1645
1646 x_verifying_digit := ' ';
1647
1648 BEGIN
1649
1650 SELECT foreign_description
1651 INTO x_name
1652 FROM jl_co_gl_mg_literals
1653 WHERE set_of_books_id = p_set_of_books_id
1654 AND reported_year = p_reported_year
1655 AND literal_code = generate_rec.literal_code;
1656
1657 EXCEPTION
1658
1659 WHEN NO_DATA_FOUND THEN
1660 NULL;
1661
1662 WHEN OTHERS THEN
1663 NULL;
1664
1665 END;
1666
1667 ELSE
1668
1669 BEGIN
1670
1671 SELECT name,
1672 NVL(verifying_digit, ' ') vd
1673 INTO x_name,
1674 x_verifying_digit
1675 FROM jl_co_gl_nits
1676 WHERE nit = generate_rec.nit_number;
1677
1678 EXCEPTION
1679
1680 WHEN NO_DATA_FOUND THEN
1681 NULL;
1682
1683 WHEN OTHERS THEN
1684 NULL;
1685
1686 END;
1687
1688 END IF;
1689
1690 x_literal_code := generate_rec.literal_code;
1691
1692 x_nit := generate_rec.nit_number;
1693
1694 count_literal := count_literal + 1;
1695
1696 movement_register := '2'||
1697 TO_CHAR(x_literal_code)||
1698 LPAD(x_nit, 14, '0')||
1699 x_verifying_digit||
1700 ' '||
1701 SUBSTR(RPAD(x_name, 60, ' '), 1, 60)||
1702 LPAD(TO_CHAR(generate_rec.first_reported_value), 20, '0')||
1703 LPAD(TO_CHAR(generate_rec.second_reported_value), 20, '0')||
1704 ' '||
1705 ' ';
1706
1707 tab_record_counter := tab_record_counter + 1;
1708 tab_flat_file (tab_record_counter) := movement_register;
1709 movement_register := null;
1710
1711 total_value := total_value +
1712 generate_rec.first_reported_value +
1713 generate_rec.second_reported_value;
1714
1715 END LOOP;
1716
1717 /**********************************************
1718 Write identification register to the flat file
1719 **********************************************/
1720
1721 put_line(fnd_file.output, identifi_register);
1722
1723 fnd_message.set_name('JL', 'JL_CO_GL_MG_INDENTI_REGISTER');
1724 put_line( fnd_file.log, fnd_message.get);
1725
1726 /****************************************
1727 Write movement_register to the flat file
1728 ****************************************/
1729
1730 IF tab_record_counter > 0 THEN
1731
1732 FOR g_count IN 1..tab_record_counter LOOP
1733
1734 movement_register := tab_flat_file (g_count);
1735
1736 put_line(fnd_file.output, movement_register);
1737
1738 END LOOP;
1739
1740 fnd_message.set_name('JL', 'JL_CO_GL_MG_MOVE_REGISTER');
1741 fnd_message.set_token('NUMBER', tab_record_counter);
1742 put_line( fnd_file.log, fnd_message.get);
1743
1744 ELSE
1745
1746 NULL;
1747
1748 fnd_message.set_name('JL', 'JL_CO_GL_MG_NO_MOVE_REGISTER');
1749 x_message := fnd_message.get;
1750 put_line( fnd_file.log, x_message);
1751 put_line( fnd_file.output, x_message);
1752
1753 END IF;
1754
1755 closed_register := '3'||
1756 ' '||
1757 LPAD(RTRIM(area_code), 5, '0')||
1758 LPAD(RTRIM(telephone_number), 7, '0')||
1759 SUBSTR(RPAD(company_address, 40, ' '), 1, 40)||
1760 LPAD(RTRIM(city_code), 5, '0')||
1761 LPAD(TO_CHAR(count_literal), 10, '0')||
1762 LPAD(total_value, 20, '0')||
1763 ' ';
1764
1765 /**************************************
1766 Write closed_register to the flat file
1767 **************************************/
1768
1769 put_line(fnd_file.output, closed_register);
1770
1771 fnd_message.set_name('JL', 'JL_CO_GL_MG_CLOSE_REGISTER');
1772 put_line( fnd_file.log, fnd_message.get);
1773
1774 /********************************************
1775 Update the jl_co_gl_mg_headers.status to 'Y'
1776 for final generation is done for DIAN
1777 ********************************************/
1778
1779 UPDATE jl_co_gl_mg_headers
1780 SET status = 'Y'
1781 WHERE reported_year = p_reported_year
1782 AND set_of_books_id = p_set_of_books_id
1783 AND EXISTS (SELECT 1
1784 FROM gl_period_statuses stat,
1785 gl_periods p,
1786 gl_sets_of_books sob,
1787 gl_period_types pt,
1788 gl_period_sets ps,
1789 fnd_application a
1790 WHERE a.application_short_name = 'SQLGL'
1791 AND stat.application_id = a.application_id
1792 AND stat.closing_status = 'P'
1793 AND stat.period_year = p_reported_year
1794 AND stat.set_of_books_id = sob.set_of_books_id
1795 AND sob.set_of_books_id = p_set_of_books_id
1796 AND stat.period_type = pt.period_type
1797 AND stat.period_name = p.period_name
1798 AND p.period_set_name = ps.period_set_name
1799 AND p.period_type = pt.period_type
1800 AND p.period_year = p_reported_year
1801 AND sob.accounted_period_type = pt.period_type
1802 AND sob.period_set_name = ps.period_set_name);
1803
1804 IF SQL%FOUND THEN
1805 COMMIT;
1806 END IF;
1807
1808 EXCEPTION
1809
1810 WHEN HEADERS_STATUS_Y THEN
1811
1812 x_message := '----***************** W A R N I N G **********************----';
1813 put_line( fnd_file.log, x_message);
1814 fnd_message.set_name('JL', 'JL_CO_GL_MG_FILE_SENT');
1815 fnd_message.set_token('YEAR', p_reported_year);
1816 x_error_text := fnd_message.get;
1817 put_line( fnd_file.log, x_error_text);
1818 x_error_text := SUBSTR(x_error_text, 1, 100);
1819 x_message := '----******************************************************----';
1820 put_line( fnd_file.log, x_message);
1821 app_exception.raise_exception (exception_type => 'APP',
1822 exception_code =>
1823 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_FILE_SENT'),
1824 exception_text => x_error_text);
1825
1826
1827 WHEN MG_GENERAL_ALERT THEN
1828
1829 x_message := '----***************** W A R N I N G **********************----';
1830 put_line( fnd_file.log, x_message);
1831 fnd_message.set_name('JL', 'JL_CO_GL_MG_GEN_ALERT');
1832 fnd_message.set_token('NUMBER', TO_CHAR(count_process_flag));
1833 fnd_message.set_token('TABLE', 'JL_CO_GL_MG_LITERALS');
1834 x_error_text := SUBSTR(fnd_message.get, 1, 200);
1835 put_line( fnd_file.log, x_error_text);
1836 x_error_text := SUBSTR(x_error_text, 1, 100);
1837 x_message := '----******************************************************----';
1838 put_line( fnd_file.log, x_message);
1839 app_exception.raise_exception (exception_type => 'APP',
1840 exception_code =>
1841 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_GEN_ALERT'),
1842 exception_text => x_error_text);
1843
1844 WHEN LOCATION_ID_DOES_NOT_EXIST THEN
1845
1846 fnd_message.set_name('JL', 'JL_CO_GL_MG_NO_LOCATION_ID');
1847 x_error_text := SUBSTR(fnd_message.get, 1, 100);
1848 app_exception.raise_exception (exception_type => 'APP',
1849 exception_code =>
1850 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_GL_MG_NO_LOCATION_ID'),
1851 exception_text => x_error_text);
1852
1853 WHEN OTHERS THEN
1854
1855 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
1856 fnd_file.put_line( fnd_file.log, fnd_message.get);
1857 x_error_code := SQLCODE;
1858 x_error_text := SUBSTR(SQLERRM,1,200);
1859 RAISE_APPLICATION_ERROR( x_error_code, x_error_text);
1860
1861 END generate_mg_media;
1862
1863
1864 END JL_CO_GL_MG_MEDIA_PKG;