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