[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
Select NIT information from jl_co_gl_nits
*****************************************/
SELECT nit,
name,
type,
DECODE(verifying_digit, NULL, ' ', verifying_digit)
INTO x_nit,
x_name,
x_type,
x_verifying_digit
FROM jl_co_gl_nits
WHERE nit_id = p_nit_id;
'Exception "NO_DATA_FOUND" for selection of nit information from JL_CO_GL_NITS table');
'Exception "OTHERS" for selection of nit information from JL_CO_GL_NITS table');
Procedure to validate and insert into jl_co_gl_mg_lines table.
This procedure will be called only when the amount returned from
cursors trx_cur and bal_cur is more than zero
****************************************************************/
PROCEDURE get_movement_insert(in_rec IN get_movement_record) IS
BEGIN
/*******************************************************
Call the procedure to get nit information for each call
*******************************************************/
get_nit_info(in_rec.nit_id);
Insert rows into jl_co_gl_mg_lines
**********************************/
BEGIN
INSERT INTO jl_co_gl_mg_lines
(mg_line_id,
mg_header_id,
literal_id,
reported_value,
reported_flag,
send_back_flag,
origin,
nit_id,
third_party_name,
first_reported_value,
second_reported_value,
config_id,
literal_literal_id,
range_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(in_rec.mg_line_id, /*mg_line_id*/
in_rec.mg_header_id, /*mg_header_id*/
in_rec.literal_id, /*literal_id*/
in_rec.reported_value, /*reported_value*/
x_reported_flag, /*reported_flag*/
in_rec.send_back_flag, /*send_back_flag*/
in_rec.origin, /*origin*/
in_rec.nit_id, /*nit_id*/
substr(x_name,1,60), /*third_party_name*/
x_first_value, /*first_reported_value*/
x_second_value, /*second_reported_value*/
in_rec.config_id, /*config_id*/
in_rec.literal_literal_id, /*literal_literal_id*/
in_rec.range_id, /*range_id*/
x_last_updated_by, /*created_by*/
x_sysdate, /*creation_date*/
x_last_updated_by, /*last_updated_by*/
x_sysdate, /*last_update_date*/
x_last_update_login /*last_update_login*/
);
'Exception "OTHERS" while inserting into jl_co_gl_mg_lines table');
END get_movement_insert;
Use this procedure to insert transactions and balances from nit
tables into jl_co_gl_mg_headers and jl_co_gl_mg_lines tables, for a
set of literal/sub-literal, reported_value (called report_group)
for a given range of accounts from magnetic media set-up tables
PURPOSE:
Oracle Applications Rel 11.0
PARAMETERS:
p_set_of_books_id
p_reported_year
p_period_start
p_period_end
p_literal_start
p_literal_end
HISTORY:
23-DEC-1998 Raja Reddy Kappera Created
**********************************************************************/
PROCEDURE get_movement
(ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2,
p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
p_reported_year IN jl_co_gl_mg_literals.reported_year%TYPE,
p_period_start IN gl_periods.period_num%TYPE,
p_period_end IN gl_periods.period_num%TYPE,
p_literal_start IN jl_co_gl_mg_literals.literal_code%TYPE,
p_literal_end IN jl_co_gl_mg_literals.literal_code%TYPE
) IS
x_mg_hdr_count NUMBER;
Cursor to select rows from jl_co_gl_mg_literals, jl_co_gl_mg_configs
and jl_co_gl_mg_ranges (accounting ranges) tables
********************************************************************/
CURSOR literal_cur IS
SELECT mgl.literal_id literal_id,
mgl.foreign_reported_flag foreign_reported_flag,
mgl.domestic_reported_flag domestic_reported_flag,
mgl.foreign_description foreign_description,
mgc.config_id config_id,
mgc.reported_value reported_value,
mgc.movement_type movement_type,
mgc.threshold_value rep_threshold_value,
mgc.literal_literal_id literal_literal_id,
mgr.range_id range_id
FROM jl_co_gl_mg_ranges mgr,
jl_co_gl_mg_configs mgc,
jl_co_gl_mg_literals mgl
WHERE mgr.config_id = mgc.config_id
AND mgc.literal_id = mgl.literal_id
AND mgl.set_of_books_id = p_set_of_books_id
AND mgl.reported_year = p_reported_year
AND mgl.literal_code BETWEEN p_literal_start AND p_literal_end
ORDER BY mgc.movement_type,
mgl.literal_id,
mgc.config_id,
mgr.range_id;
Cursor for selecting rows from jl_co_gl_trx table
*************************************************/
CURSOR trx_cur (x_movement_type jl_co_gl_mg_configs.movement_type%TYPE,
x_range_id jl_co_gl_mg_ranges.range_id%TYPE) IS
SELECT t.nit_id nit_id,
DECODE(x_movement_type,
'1', SUM(NVL(t.accounted_dr, 0)),
'2', SUM(NVL(t.accounted_cr, 0)),
'3', SUM(NVL(t.accounted_dr, 0)) - SUM(NVL(t.accounted_cr, 0)),
'4', SUM(NVL(t.accounted_cr, 0)) - SUM(NVL(t.accounted_dr, 0)),
0
) amount
FROM jl_co_gl_trx t,
jl_co_gl_nits nit,
gl_sets_of_books sob1
WHERE t.set_of_books_id = sob1.set_of_books_id
AND nit.nit_id = t.nit_id
AND sob1.set_of_books_id = p_set_of_books_id
AND t.period_name IN (SELECT p.period_name
FROM gl_periods p,
gl_period_types pt,
gl_period_sets ps,
gl_sets_of_books sob2
WHERE p.period_year = p_reported_year
AND p.period_num BETWEEN p_period_start
AND p_period_end
AND p.adjustment_period_flag = 'N'
AND p.period_type = pt.period_type
AND pt.period_type = sob2.accounted_period_type
AND p.period_set_name = ps.period_set_name
AND ps.period_set_name = sob2.period_set_name
AND sob2.set_of_books_id = p_set_of_books_id
)
--AND t.code_combination_id IN
AND exists
(SELECT 1
FROM gl_code_combinations cc,
jl_co_gl_mg_ranges r
WHERE cc.code_combination_id = t.code_combination_id
AND r.range_id = x_range_id
AND cc.chart_of_accounts_id = sob1.chart_of_accounts_id
AND NVL(cc.segment1,0) BETWEEN NVL(r.segment1_low,0) AND NVL(r.segment1_high,0)
AND NVL(cc.segment2,0) BETWEEN NVL(r.segment2_low,0) AND NVL(r.segment2_high,0)
AND NVL(cc.segment3,0) BETWEEN NVL(r.segment3_low,0) AND NVL(r.segment3_high,0)
AND NVL(cc.segment4,0) BETWEEN NVL(r.segment4_low,0) AND NVL(r.segment4_high,0)
AND NVL(cc.segment5,0) BETWEEN NVL(r.segment5_low,0) AND NVL(r.segment5_high,0)
AND NVL(cc.segment6,0) BETWEEN NVL(r.segment6_low,0) AND NVL(r.segment6_high,0)
AND NVL(cc.segment7,0) BETWEEN NVL(r.segment7_low,0) AND NVL(r.segment7_high,0)
AND NVL(cc.segment8,0) BETWEEN NVL(r.segment8_low,0) AND NVL(r.segment8_high,0)
AND NVL(cc.segment9,0) BETWEEN NVL(r.segment9_low,0) AND NVL(r.segment9_high,0)
AND NVL(cc.segment10,0) BETWEEN NVL(r.segment10_low,0) AND NVL(r.segment10_high,0)
AND NVL(cc.segment11,0) BETWEEN NVL(r.segment11_low,0) AND NVL(r.segment11_high,0)
AND NVL(cc.segment12,0) BETWEEN NVL(r.segment12_low,0) AND NVL(r.segment12_high,0)
AND NVL(cc.segment13,0) BETWEEN NVL(r.segment13_low,0) AND NVL(r.segment13_high,0)
AND NVL(cc.segment14,0) BETWEEN NVL(r.segment14_low,0) AND NVL(r.segment14_high,0)
AND NVL(cc.segment15,0) BETWEEN NVL(r.segment15_low,0) AND NVL(r.segment15_high,0)
AND NVL(cc.segment16,0) BETWEEN NVL(r.segment16_low,0) AND NVL(r.segment16_high,0)
AND NVL(cc.segment17,0) BETWEEN NVL(r.segment17_low,0) AND NVL(r.segment17_high,0)
AND NVL(cc.segment18,0) BETWEEN NVL(r.segment18_low,0) AND NVL(r.segment18_high,0)
AND NVL(cc.segment19,0) BETWEEN NVL(r.segment19_low,0) AND NVL(r.segment19_high,0)
AND NVL(cc.segment20,0) BETWEEN NVL(r.segment20_low,0) AND NVL(r.segment20_high,0)
AND NVL(cc.segment21,0) BETWEEN NVL(r.segment21_low,0) AND NVL(r.segment21_high,0)
AND NVL(cc.segment22,0) BETWEEN NVL(r.segment22_low,0) AND NVL(r.segment22_high,0)
AND NVL(cc.segment23,0) BETWEEN NVL(r.segment23_low,0) AND NVL(r.segment23_high,0)
AND NVL(cc.segment24,0) BETWEEN NVL(r.segment24_low,0) AND NVL(r.segment24_high,0)
AND NVL(cc.segment25,0) BETWEEN NVL(r.segment25_low,0) AND NVL(r.segment25_high,0)
AND NVL(cc.segment26,0) BETWEEN NVL(r.segment26_low,0) AND NVL(r.segment26_high,0)
AND NVL(cc.segment27,0) BETWEEN NVL(r.segment27_low,0) AND NVL(r.segment27_high,0)
AND NVL(cc.segment28,0) BETWEEN NVL(r.segment28_low,0) AND NVL(r.segment28_high,0)
AND NVL(cc.segment29,0) BETWEEN NVL(r.segment29_low,0) AND NVL(r.segment29_high,0)
AND NVL(cc.segment30,0) BETWEEN NVL(r.segment30_low,0) AND NVL(r.segment30_high,0)
)
GROUP BY t.nit_id;
Cursor for selecting rows from jl_co_gl_balance table
*****************************************************/
-- Bug 4018828 - Comment out the join to period_name in the subquery retrieving
-- the max period num. This ensures that only the balances for last active
-- period for which there exists transactions are taken into account
CURSOR bal_cur (x_movement_type jl_co_gl_mg_configs.movement_type%TYPE,
x_range_id jl_co_gl_mg_ranges.range_id%TYPE) IS
SELECT b.nit_id nit_id,
DECODE(x_movement_type,
'5', SUM(NVL(b.begin_balance_dr, 0)) - SUM(NVL(b.begin_balance_cr, 0)) +
SUM(NVL(b.period_net_dr, 0)) - SUM(NVL(b.period_net_cr, 0)),
'6', SUM(NVL(b.begin_balance_cr, 0)) - SUM(NVL(b.begin_balance_dr, 0)) +
SUM(NVL(b.period_net_cr, 0)) - SUM(NVL(b.period_net_dr, 0)),
0
) amount
FROM jl_co_gl_balances b,
gl_sets_of_books sob1
WHERE b.set_of_books_id = sob1.set_of_books_id
AND b.currency_code = sob1.currency_code
AND sob1.set_of_books_id = p_set_of_books_id
AND b.period_num =
(SELECT MAX(b1.period_num)
FROM jl_co_gl_balances b1
WHERE b.set_of_books_id = b1.set_of_books_id
AND b.code_combination_id = b1.code_combination_id
AND b.nit_id = b1.nit_id
--AND b.period_name = b1.period_name
AND b1.period_num <= p_period_end
AND b1.period_year = p_reported_year
)
AND b.period_name IN
(SELECT p.period_name
FROM gl_periods p,
gl_period_types pt,
gl_period_sets ps,
gl_sets_of_books sob2
WHERE p.period_year = p_reported_year
AND p.period_num BETWEEN p_period_start
AND p_period_end
AND p.adjustment_period_flag = 'N'
AND p.period_type = pt.period_type
AND pt.period_type = sob2.accounted_period_type
AND p.period_set_name = ps.period_set_name
AND ps.period_set_name = sob2.period_set_name
AND sob2.set_of_books_id = p_set_of_books_id
)
AND b.code_combination_id IN
(SELECT code_combination_id
FROM gl_code_combinations cc,
jl_co_gl_mg_ranges r,
gl_sets_of_books sob3
WHERE r.range_id = x_range_id
AND cc.chart_of_accounts_id = sob3.chart_of_accounts_id
AND sob3.set_of_books_id = p_set_of_books_id
AND NVL(cc.segment1,0) BETWEEN NVL(r.segment1_low,0) AND NVL(r.segment1_high,0)
AND NVL(cc.segment2,0) BETWEEN NVL(r.segment2_low,0) AND NVL(r.segment2_high,0)
AND NVL(cc.segment3,0) BETWEEN NVL(r.segment3_low,0) AND NVL(r.segment3_high,0)
AND NVL(cc.segment4,0) BETWEEN NVL(r.segment4_low,0) AND NVL(r.segment4_high,0)
AND NVL(cc.segment5,0) BETWEEN NVL(r.segment5_low,0) AND NVL(r.segment5_high,0)
AND NVL(cc.segment6,0) BETWEEN NVL(r.segment6_low,0) AND NVL(r.segment6_high,0)
AND NVL(cc.segment7,0) BETWEEN NVL(r.segment7_low,0) AND NVL(r.segment7_high,0)
AND NVL(cc.segment8,0) BETWEEN NVL(r.segment8_low,0) AND NVL(r.segment8_high,0)
AND NVL(cc.segment9,0) BETWEEN NVL(r.segment9_low,0) AND NVL(r.segment9_high,0)
AND NVL(cc.segment10,0) BETWEEN NVL(r.segment10_low,0) AND NVL(r.segment10_high,0)
AND NVL(cc.segment11,0) BETWEEN NVL(r.segment11_low,0) AND NVL(r.segment11_high,0)
AND NVL(cc.segment12,0) BETWEEN NVL(r.segment12_low,0) AND NVL(r.segment12_high,0)
AND NVL(cc.segment13,0) BETWEEN NVL(r.segment13_low,0) AND NVL(r.segment13_high,0)
AND NVL(cc.segment14,0) BETWEEN NVL(r.segment14_low,0) AND NVL(r.segment14_high,0)
AND NVL(cc.segment15,0) BETWEEN NVL(r.segment15_low,0) AND NVL(r.segment15_high,0)
AND NVL(cc.segment16,0) BETWEEN NVL(r.segment16_low,0) AND NVL(r.segment16_high,0)
AND NVL(cc.segment17,0) BETWEEN NVL(r.segment17_low,0) AND NVL(r.segment17_high,0)
AND NVL(cc.segment18,0) BETWEEN NVL(r.segment18_low,0) AND NVL(r.segment18_high,0)
AND NVL(cc.segment19,0) BETWEEN NVL(r.segment19_low,0) AND NVL(r.segment19_high,0)
AND NVL(cc.segment20,0) BETWEEN NVL(r.segment20_low,0) AND NVL(r.segment20_high,0)
AND NVL(cc.segment21,0) BETWEEN NVL(r.segment21_low,0) AND NVL(r.segment21_high,0)
AND NVL(cc.segment22,0) BETWEEN NVL(r.segment22_low,0) AND NVL(r.segment22_high,0)
AND NVL(cc.segment23,0) BETWEEN NVL(r.segment23_low,0) AND NVL(r.segment23_high,0)
AND NVL(cc.segment24,0) BETWEEN NVL(r.segment24_low,0) AND NVL(r.segment24_high,0)
AND NVL(cc.segment25,0) BETWEEN NVL(r.segment25_low,0) AND NVL(r.segment25_high,0)
AND NVL(cc.segment26,0) BETWEEN NVL(r.segment26_low,0) AND NVL(r.segment26_high,0)
AND NVL(cc.segment27,0) BETWEEN NVL(r.segment27_low,0) AND NVL(r.segment27_high,0)
AND NVL(cc.segment28,0) BETWEEN NVL(r.segment28_low,0) AND NVL(r.segment28_high,0)
AND NVL(cc.segment29,0) BETWEEN NVL(r.segment29_low,0) AND NVL(r.segment29_high,0)
AND NVL(cc.segment30,0) BETWEEN NVL(r.segment30_low,0) AND NVL(r.segment30_high,0)
)
GROUP BY b.nit_id;
SELECT count(*)
INTO count_status
FROM jl_co_gl_mg_headers
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND status = 'Y';
Delete rows from jl_co_gl_mg_lines and
jl_co_gl_mg_headers for given parameters
****************************************/
BEGIN
DELETE FROM jl_co_gl_mg_lines
WHERE mg_header_id IN (SELECT mg_header_id
FROM jl_co_gl_mg_headers
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
)
AND literal_id IN (SELECT literal_id
FROM jl_co_gl_mg_literals
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND literal_code BETWEEN p_literal_start
AND p_literal_end
)
AND origin = 'A';
fnd_message.set_name('JL', 'JL_CO_GL_MG_DELETE');
fnd_message.set_name('JL', 'JL_CO_GL_MG_NOT_DELETE');
DELETE FROM jl_co_gl_mg_headers
WHERE reported_year = p_reported_year
AND set_of_books_id = p_set_of_books_id
AND mg_header_id NOT IN (SELECT mg_header_id
FROM jl_co_gl_mg_lines
);
fnd_message.set_name('JL', 'JL_CO_GL_MG_DELETE');
fnd_message.set_name('JL', 'JL_CO_GL_MG_NOT_DELETE');
Insert a row for the given parameters in jl_co_gl_mg_headers table
******************************************************************/
BEGIN
BEGIN
SELECT jl_co_gl_mg_headers_s.NEXTVAL
INTO x_mg_header_id
FROM SYS.DUAL;
INSERT INTO jl_co_gl_mg_headers
(mg_header_id,
set_of_books_id,
reported_year,
status,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(x_mg_header_id, /*mg_header_id*/
p_set_of_books_id, /*set_of_books_id*/
p_reported_year, /*reported_year*/
'N', /*status*/
x_last_updated_by, /*created_by*/
x_sysdate, /*creation_date*/
x_last_updated_by, /*last_updated_by*/
x_sysdate, /*last_update_date*/
x_last_update_login /*last_update_login*/
);
fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
'Exception "OTHERS" while inserting into jl_co_gl_mg_headers table');
Select nit_id and sum(amount) from jl_co_gl_balances and jl_co_gl_trx tables
****************************************************************************/
IF literal_rec.movement_type IN ('1', '2', '3', '4') THEN
FOR trx_rec IN trx_cur (literal_rec.movement_type,
literal_rec.range_id) LOOP
IF trx_rec.amount > 0 THEN
get_move_rec.nit_id := trx_rec.nit_id;
SELECT jl_co_gl_mg_lines_s.NEXTVAL
INTO get_move_rec.mg_line_id
FROM SYS.DUAL;
get_movement_insert (get_move_rec);
SELECT jl_co_gl_mg_lines_s.NEXTVAL
INTO get_move_rec.mg_line_id
FROM SYS.DUAL;
get_movement_insert (get_move_rec);
fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
fnd_message.set_name('JL', 'JL_CO_GL_MG_INSERT');
DELETE FROM jl_co_gl_mg_headers
WHERE reported_year = p_reported_year
AND set_of_books_id = p_set_of_books_id
AND mg_header_id NOT IN (SELECT mg_header_id
FROM jl_co_gl_mg_lines
);
Update JL_CO_GL_MG_LITERALS.PROCESSED_FLAG to 'M' for the given Parameters
**************************************************************************/
UPDATE jl_co_gl_mg_literals
SET processed_flag = 'M'
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND literal_code BETWEEN p_literal_start AND p_literal_end;
SELECT mgl.mg_header_id mg_header_id,
mgl.literal_id literal_id,
mgl.reported_value reported_value,
mgl.reported_flag reported_flag,
mgl.nit_id nit_id,
mgl.third_party_name third_party_name,
mgl.config_id config_id,
c.threshold_value threshold_value,
SUM(mgl.first_reported_value) first_reported_value,
SUM(mgl.second_reported_value) second_reported_value
FROM jl_co_gl_mg_configs c,
jl_co_gl_mg_literals l,
jl_co_gl_mg_lines mgl,
jl_co_gl_mg_headers mgh
WHERE mgl.mg_header_id = mgh.mg_header_id
AND mgh.reported_year = p_reported_year
AND mgh.set_of_books_id = p_set_of_books_id
AND mgl.literal_id = l.literal_id
AND l.literal_code BETWEEN p_literal_start AND p_literal_end
AND mgl.config_id = c.config_id
AND c.config_id_parent IS NULL
GROUP BY mgl.mg_header_id,
mgl.literal_id,
mgl.reported_value,
mgl.reported_flag,
mgl.nit_id,
mgl.third_party_name,
mgl.config_id,
c.threshold_value
ORDER BY mgl.mg_header_id,
mgl.literal_id,
mgl.reported_value,
mgl.reported_flag,
mgl.nit_id,
mgl.third_party_name,
mgl.config_id,
c.threshold_value;
SELECT mgl.mg_header_id mg_header_id,
mgl.nit_id nit_id,
mgl.literal_literal_id literal_literal_id,
l.threshold_value threshold_value,
l.threshold_foreign_flag threshold_foreign_flag,
l.threshold_domestic_flag threshold_domestic_flag,
SUM(mgl.first_reported_value) first_reported_value,
SUM(mgl.second_reported_value) second_reported_value
FROM jl_co_gl_mg_literals l,
jl_co_gl_mg_lines mgl,
jl_co_gl_mg_headers mgh
WHERE mgl.mg_header_id = mgh.mg_header_id
AND mgh.reported_year = p_reported_year
AND mgh.set_of_books_id = p_set_of_books_id
AND mgl.literal_literal_id = l.literal_id
AND l.literal_code BETWEEN p_literal_start AND p_literal_end
GROUP BY mgl.mg_header_id,
mgl.nit_id,
mgl.literal_literal_id,
l.threshold_value,
l.threshold_foreign_flag,
l.threshold_domestic_flag
ORDER BY mgl.mg_header_id,
mgl.nit_id,
mgl.literal_literal_id,
l.threshold_value,
l.threshold_foreign_flag,
l.threshold_domestic_flag;
SELECT mgl.mg_header_id mg_header_id,
mgl.config_id config_id,
mgl.nit_id nit_id
FROM jl_co_gl_mg_configs c,
jl_co_gl_mg_literals l,
jl_co_gl_mg_lines mgl,
jl_co_gl_mg_headers mgh
WHERE mgl.mg_header_id = mgh.mg_header_id
AND mgh.reported_year = p_reported_year
AND mgh.set_of_books_id = p_set_of_books_id
AND mgl.config_id = c.config_id
AND c.literal_id = l.literal_id
AND c.config_id_parent IS NULL
AND l.literal_code BETWEEN p_literal_start AND p_literal_end
AND mgl.reported_flag = 'N'
GROUP BY mgl.mg_header_id,
mgl.config_id,
mgl.nit_id
ORDER BY mgl.mg_header_id,
mgl.config_id,
mgl.nit_id;
SELECT count(*)
INTO count_status
FROM jl_co_gl_mg_headers
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND status = 'Y';
Select Lietarl information and Configs(Reported Group) information from
jl_co_gl_literals and jl_co_gl_configs for each row of the cursor
***********************************************************************/
BEGIN
SELECT l.foreign_reported_flag,
l.domestic_reported_flag,
l.threshold_foreign_flag,
l.threshold_domestic_flag
INTO x_foreign_reported_flag,
x_domestic_reported_flag,
x_threshold_foreign_flag,
x_threshold_domestic_flag
FROM jl_co_gl_mg_literals l
WHERE l.literal_id = rg_threshold_rec.literal_id;
'Exception "NO_DATA_FOUND" for selection of flags from JL_CO_GL_MG_LITERALS table');
'Exception "TOO_MANY_ROWS" for selection of flags from JL_CO_GL_MG_LITERALS table');
'Exception "OTHERS" for selection of flags from JL_CO_GL_MG_LITERALS table');
Update jl_co_gl_mg_lines.reported_flag to "N"
that are not required to be reported
*********************************************/
IF (x_type = 'FOREIGN_ENTITY' AND
x_foreign_reported_flag = 'N') OR
(x_type <> 'FOREIGN_ENTITY' AND
x_domestic_reported_flag = 'N') THEN
UPDATE jl_co_gl_mg_lines
SET reported_flag = 'N'
WHERE mg_header_id = rg_threshold_rec.mg_header_id
AND literal_id = rg_threshold_rec.literal_id
AND reported_value = rg_threshold_rec.reported_value
AND reported_flag = rg_threshold_rec.reported_flag
AND nit_id = rg_threshold_rec.nit_id
AND third_party_name = rg_threshold_rec.third_party_name
AND config_id = rg_threshold_rec.config_id;
UPDATE jl_co_gl_mg_lines
SET reported_flag = 'N'
WHERE mg_header_id = rg_threshold_rec.mg_header_id
AND literal_id = rg_threshold_rec.literal_id
AND reported_value = rg_threshold_rec.reported_value
AND reported_flag = rg_threshold_rec.reported_flag
AND nit_id = rg_threshold_rec.nit_id
AND third_party_name = rg_threshold_rec.third_party_name
AND config_id = rg_threshold_rec.config_id;
UPDATE jl_co_gl_mg_lines
SET reported_flag = 'Y'
WHERE mg_header_id = lit_threshold_rec.mg_header_id
AND literal_literal_id = lit_threshold_rec.literal_literal_id
AND nit_id = lit_threshold_rec.nit_id;
Update jl_co_gl_mg_lines for the selected parent config_id in the cursor
************************************************************************/
UPDATE jl_co_gl_mg_lines
SET reported_flag = 'N'
WHERE mg_header_id = child_threshold_rec.mg_header_id
AND config_id IN (SELECT config_id
FROM jl_co_gl_mg_configs
WHERE config_id_parent = child_threshold_rec.config_id
)
AND nit_id = child_threshold_rec.nit_id;
Update JL_CO_GL_MG_LITERALS.PROCESSED_FLAG to 'M' for the given Parameters
**************************************************************************/
UPDATE jl_co_gl_mg_literals
SET processed_flag = 'T'
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND literal_code BETWEEN p_literal_start AND p_literal_end
AND processed_flag = 'M';
SELECT count(*)
INTO count_process_flag
FROM jl_co_gl_mg_literals
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND LENGTH(literal_code) = 4
AND processed_flag = 'N';
SELECT mglit.literal_code literal_code,
DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit)
nit_number,
SUM(mgl.first_reported_value) first_reported_value,
SUM(mgl.second_reported_value) second_reported_value
FROM jl_co_gl_mg_lines mgl,
jl_co_gl_nits n,
jl_co_gl_mg_literals mglit,
jl_co_gl_mg_headers mgh
WHERE mgl.mg_header_id = mgh.mg_header_id
AND mgh.reported_year = p_reported_year
AND mgh.set_of_books_id = p_set_of_books_id
AND mgl.reported_flag = 'Y'
AND mgl.nit_id = n.nit_id
AND mgl.literal_id = mglit.literal_id
GROUP BY mglit.literal_code,
DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit)
ORDER BY mglit.literal_code,
DECODE(n.type, 'FOREIGN_ENTITY', foreign_nit, n.nit);
SELECT count(*)
INTO count_status
FROM jl_co_gl_mg_headers
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND status = 'Y';
SELECT count(*)
INTO count_process_flag
FROM jl_co_gl_mg_literals
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND LENGTH(literal_code) = 4
AND processed_flag <> 'T';
Select Company Information required for identification register
and closed register from HR_LOCATIONS Table
***************************************************************/
BEGIN
SELECT NVL(le.address_line_1||DECODE(le.address_line_2, NULL, ' ', ',')||
le.address_line_2||DECODE(le.address_line_3, NULL, ' ', ',')||
le.address_line_3, 'No Address') address,
NVL(hr.telephone_number_1, '0'),
NVL(hr.telephone_number_2, '0'),
NVL(le.name, 'No Company Name'),
NVL(le.registration_number, 'No Nit'),
--NVL(global_attribute12, 'x'),
NVL(le.town_or_city, 'x'),
NVL(le.activity_code, 'x')
INTO company_address,
area_code,
telephone_number,
company_name,
company_nit,
--company_vdigit,
city_code,
economic_activity_code
FROM xle_firstparty_information_v le,
hr_locations hr
WHERE le.legal_entity_id = p_legal_entity_id
AND hr.location_id = le.location_id;
'Exception "NO_DATA_FOUND" while selecting company information');
'Exception "TOO_MANY_ROWS" while selecting company information');
'Exception "OTHERS" while selecting company information');
SELECT foreign_description
INTO x_name
FROM jl_co_gl_mg_literals
WHERE set_of_books_id = p_set_of_books_id
AND reported_year = p_reported_year
AND literal_code = generate_rec.literal_code;
SELECT name,
NVL(verifying_digit, ' ') vd
INTO x_name,
x_verifying_digit
FROM jl_co_gl_nits
WHERE nit = generate_rec.nit_number;
Update the jl_co_gl_mg_headers.status to 'Y'
for final generation is done for DIAN
********************************************/
UPDATE jl_co_gl_mg_headers
SET status = 'Y'
WHERE reported_year = p_reported_year
AND set_of_books_id = p_set_of_books_id
AND EXISTS (SELECT 1
FROM gl_period_statuses stat,
gl_periods p,
gl_sets_of_books sob,
gl_period_types pt,
gl_period_sets ps,
fnd_application a
WHERE a.application_short_name = 'SQLGL'
AND stat.application_id = a.application_id
AND stat.closing_status = 'P'
AND stat.period_year = p_reported_year
AND stat.set_of_books_id = sob.set_of_books_id
AND sob.set_of_books_id = p_set_of_books_id
AND stat.period_type = pt.period_type
AND stat.period_name = p.period_name
AND p.period_set_name = ps.period_set_name
AND p.period_type = pt.period_type
AND p.period_year = p_reported_year
AND sob.accounted_period_type = pt.period_type
AND sob.period_set_name = ps.period_set_name);