[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT currency_code
INTO n_ledger_currency
FROM gl_ledgers
WHERE ledger_id = n_ledger_id;
SELECT alc_ledger_type_code
INTO n_alc_ledger_type
FROM gl_ledgers
WHERE ledger_id = n_ledger_id;
SELECT alc_ledger_type_code
INTO l_alc_ledger_type
FROM gl_ledgers
WHERE ledger_id = n_ledger_id;
SELECT ledger_category_code
INTO n_ledger_category
FROM gl_ledgers
WHERE ledger_id = n_ledger_id;
SELECT ledger_category_code
INTO l_ledger_category
FROM gl_ledgers
WHERE ledger_id = n_ledger_id;
SELECT source_ledger_id
INTO l_src_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR
WHERE GLR.target_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'ALC'
AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND GLR.application_id = n_appl_id
AND GLR.relationship_enabled_flag = 'Y'
AND (n_org_id IS NULL
OR GLR.org_id = -99
OR GLR.org_id = NVL(n_org_id,-99))
AND (NVL(n_fa_book_code, '-99') = '-99'
OR EXISTS
(SELECT 'FA book type is enabled'
FROM FA_MC_BOOK_CONTROLS MC
WHERE MC.set_of_books_id = GLR.target_ledger_id
AND MC.book_type_code = n_fa_book_code
AND MC.primary_set_of_books_id = GLR.source_ledger_id
AND MC.enabled_flag = 'Y'))
AND rownum = 1;
SELECT source_ledger_id
INTO l_src_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR,
gl_ledgers lgr_c
WHERE GLR.target_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'SECONDARY'
AND GLR.relationship_type_code <> 'NONE'
AND GLR.application_id = n_appl_id
AND glr.target_ledger_id = lgr_c.ledger_id
AND nvl(lgr_c.complete_flag,'Y') = 'Y'
AND GLR.relationship_enabled_flag = 'Y'
AND (n_org_id IS NULL
OR GLR.org_id = -99
OR GLR.org_id = NVL(n_org_id,-99))
AND rownum = 1;
SELECT source_ledger_id
INTO l_src_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR
WHERE GLR.target_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'ALC'
AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND GLR.relationship_enabled_flag = 'Y'
AND rownum = 1;
SELECT source_ledger_id
INTO l_src_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR,
GL_LEDGERS LGR_C
WHERE GLR.target_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'SECONDARY'
AND GLR.relationship_type_code <> 'NONE'
AND GLR.relationship_enabled_flag = 'Y'
AND glr.target_ledger_id = lgr_c.ledger_id
AND nvl(lgr_c.complete_flag,'Y') = 'Y'
AND rownum = 1;
SELECT primary_ledger_id
INTO l_pri_ledger_id
FROM GL_LEDGER_RELATIONSHIPS
WHERE target_ledger_id = n_ledger_id
AND target_ledger_category_code = 'ALC'
AND relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND application_id = n_appl_id
AND (n_org_id IS NULL OR org_id = -99 OR org_id = NVL(n_org_id, -99))
AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
AND rownum = 1;
SELECT primary_ledger_id
INTO l_pri_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR,
GL_LEDGERS lgr_c
WHERE target_ledger_id = n_ledger_id
AND target_ledger_category_code = 'SECONDARY'
AND relationship_type_code <> 'NONE'
AND application_id = n_appl_id
AND (n_org_id IS NULL OR org_id = -99 OR org_id = NVL(n_org_id, -99))
AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
AND glr.target_ledger_id = lgr_c.ledger_id
AND nvl(lgr_c.complete_flag,'Y') = 'Y'
AND rownum = 1;
SELECT primary_ledger_id
INTO l_pri_ledger_id
FROM GL_LEDGER_RELATIONSHIPS
WHERE target_ledger_id = n_ledger_id
AND target_ledger_category_code = 'ALC'
AND relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
AND rownum = 1;
SELECT primary_ledger_id
INTO l_pri_ledger_id
FROM GL_LEDGER_RELATIONSHIPS GLR,
GL_LEDGERS lgr_c
WHERE target_ledger_id = n_ledger_id
AND target_ledger_category_code = 'SECONDARY'
AND relationship_type_code <> 'NONE'
AND relationship_enabled_flag = 'Y' -- Should we check if it is enabled???
AND glr.target_ledger_id = lgr_c.ledger_id
AND nvl(lgr_c.complete_flag,'Y') = 'Y'
AND rownum = 1;
l_insertSQL DBMS_SQL.VARCHAR2S;
SELECT ledger_category_code,
NVL(bal_seg_value_option_code, 'A'),
bal_seg_value_set_id
INTO l_ledger_category,
l_bsv_option,
l_bsv_vset_id
FROM GL_LEDGERS
WHERE ledger_id = p_ledger_id;
/* DELETE FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = p_ledger_id
OR ledger_id IN (
SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V
WHERE application_id = 101
AND source_ledger_id = p_ledger_id); */
DELETE FROM GL_LEDGER_LE_BSV_GT;
INSERT INTO GL_LEDGER_LE_BSV_GT
(LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE,
CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID,
BAL_SEG_COLUMN_NAME, BAL_SEG_VALUE, LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME,
START_DATE, END_DATE, RELATIONSHIP_ENABLED_FLAG
-- , SLA_SEQUENCING_FLAG
)
-- XLE uptake: Changed to get the LE name from the new XLE tables
SELECT lg.LEDGER_ID, lg.NAME, lg.SHORT_NAME, lg.LEDGER_CATEGORY_CODE,
lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE,
lg.BAL_SEG_VALUE_SET_ID, lg.BAL_SEG_COLUMN_NAME, bsv.SEGMENT_VALUE,
bsv.LEGAL_ENTITY_ID, le.NAME, bsv.START_DATE,
bsv.END_DATE, DECODE(lg.LEDGER_CATEGORY_CODE, 'PRIMARY', 'Y', 'N')
-- , bsv.SLA_SEQUENCING_FLAG
FROM GL_LEDGERS lg
, GL_LEDGER_RELATIONSHIPS rs
, GL_LEDGER_NORM_SEG_VALS bsv
, XLE_ENTITY_PROFILES le
, GL_LEDGERS lgr_c
WHERE ((rs.relationship_type_code = 'NONE'
AND rs.target_ledger_id = p_ledger_id)
OR
(rs.target_ledger_category_code = 'ALC'
AND rs.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND rs.source_ledger_id = p_ledger_id))
AND rs.application_id = 101
AND lg.ledger_id = rs.target_ledger_id
--Bug 4887990 Avoided the merge join
AND bsv.ledger_id = Decode(rs.relationship_type_code,
'NONE',rs.target_ledger_id,
rs.source_ledger_id)--p_ledger_id
AND rs.target_ledger_id = lgr_c.ledger_id
AND nvl(lgr_c.complete_flag,'Y') = 'Y'
AND bsv.segment_type_code = 'B'
-- We should exclude segment values with status code = 'D' since they
-- will be deleted by the flatten program when config is confirmed
-- AND bsv.status_code IS NULL
AND NVL(bsv.status_code, 'I') <> 'D'
AND le.legal_entity_id(+) = bsv.legal_entity_id;
l_insertSQL(l_line_no) :=
'INSERT INTO GL_LEDGER_LE_BSV_GT';
l_insertSQL(l_line_no) :=
'(LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE, ';
l_insertSQL(l_line_no) :=
' CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID, ';
l_insertSQL(l_line_no) :=
' BAL_SEG_COLUMN_NAME, BAL_SEG_VALUE, LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME, ';
l_insertSQL(l_line_no) :=
' START_DATE, END_DATE, RELATIONSHIP_ENABLED_FLAG) ';
SELECT nvl(fvt.application_table_name, 'FND_FLEX_VALUES')
, nvl(fvt.value_column_name, 'FLEX_VALUE')
, fvs.validation_type
INTO l_fv_table
, l_fv_col
, l_fv_type
FROM fnd_flex_value_sets fvs
, fnd_flex_validation_tables fvt
WHERE fvs.flex_value_set_id = l_bsv_vset_id
AND fvt.flex_value_set_id(+) = fvs.flex_value_set_id;
l_insertSQL(l_line_no) :=
'SELECT lg.LEDGER_ID, lg.NAME, lg.SHORT_NAME, lg.LEDGER_CATEGORY_CODE, ';
l_insertSQL(l_line_no) :=
' lg.CHART_OF_ACCOUNTS_ID, lg.BAL_SEG_VALUE_OPTION_CODE, ';
l_insertSQL(l_line_no) :=
' lg.BAL_SEG_VALUE_SET_ID, lg.BAL_SEG_COLUMN_NAME, bsv.'
|| l_fv_col || ', ';
l_insertSQL(l_line_no) :=
' NULL, NULL, bsv.START_DATE_ACTIVE, bsv.END_DATE_ACTIVE, ';
l_insertSQL(l_line_no) :=
' NULL, NULL, NULL, NULL, ';
l_insertSQL(l_line_no) :=
' DECODE(lg.LEDGER_CATEGORY_CODE, ''PRIMARY'', ''Y'', ''N'') ';
l_insertSQL(l_line_no) :=
'FROM GL_LEDGERS lg, '|| l_fv_table || ' bsv ';
l_insertSQL(l_line_no) :=
'WHERE (lg.ledger_id = :lg_id1 ';
l_insertSQL(l_line_no) :=
' OR lg.ledger_id IN ( ';
l_insertSQL(l_line_no) :=
' SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V ';
l_insertSQL(l_line_no) :=
' WHERE application_id = 101 ';
l_insertSQL(l_line_no) :=
' AND source_ledger_id = :lg_id2)) ';
l_insertSQL(l_line_no) :=
'AND bsv.flex_value_set_id = lg.bal_seg_value_set_id ';
l_insertSQL(l_line_no) := 'AND bsv.summary_flag = ''N'' ';
DBMS_SQL.PARSE(l_cursorID, l_insertSQL, 1, l_line_no, TRUE, dbms_sql.native);
UPDATE GL_LEDGER_LE_BSV_GT gt
SET gt.RELATIONSHIP_ENABLED_FLAG = 'Y'
WHERE (gt.LEDGER_CATEGORY_CODE = 'SECONDARY'
AND EXISTS (
SELECT 'Enabled RS exists' FROM GL_SECONDARY_LEDGER_RSHIPS_V rs
WHERE rs.ledger_id = gt.ledger_id
AND rs.relationship_enabled_flag = 'Y'))
OR (gt.LEDGER_CATEGORY_CODE = 'ALC'
AND EXISTS (
SELECT 'Enabled RS exists' FROM GL_ALC_LEDGER_RSHIPS_V rs
WHERE rs.ledger_id = gt.ledger_id
AND rs.application_id = 101
AND rs.relationship_enabled_flag = 'Y'));
SELECT LEDGER_ID
, LEDGER_NAME
, LEDGER_SHORT_NAME
, CURRENCY_CODE
, LEDGER_CATEGORY_CODE
BULK COLLECT INTO
l_rec_col.ledger_id,
l_rec_col.ledger_name,
l_rec_col.ledger_short_name,
l_rec_col.ledger_currency,
l_rec_col.ledger_category
FROM GL_LEDGER_LE_V
WHERE ledger_category_code IN (
DECODE(UPPER(NVL(p_get_primary_flag, 'Y')), 'Y', 'PRIMARY', 'NOT_INCLUDED'),
DECODE(UPPER(NVL(p_get_secondary_flag, 'N')), 'Y', 'SECONDARY', 'NOT_INCLUDED'),
DECODE(UPPER(NVL(p_get_alc_flag, 'N')), 'Y', 'ALC', 'NOT_INCLUDED'))
AND legal_entity_id = p_legal_entity_id
AND relationship_enabled_flag = 'Y'
ORDER BY DECODE(ledger_category_code, 'PRIMARY', 1, 2), ledger_id;
SELECT l_rec_col.ledger_id(i),
l_rec_col.ledger_name(i),
l_rec_col.ledger_short_name(i),
l_rec_col.ledger_currency(i),
l_rec_col.ledger_category(i)
INTO x_ledger_list(i).ledger_id,
x_ledger_list(i).ledger_name,
x_ledger_list(i).ledger_short_name,
x_ledger_list(i).ledger_currency,
x_ledger_list(i).ledger_category
FROM dual;
SELECT legal_entity_id
, legal_entity_name
BULK COLLECT INTO
l_rec_col.legal_entity_id,
l_rec_col.legal_entity_name
FROM GL_LEDGER_LE_V
WHERE ledger_id = p_ledger_id
AND legal_entity_id IS NOT NULL
AND relationship_enabled_flag = 'Y'
ORDER BY legal_entity_id;
SELECT l_rec_col.legal_entity_id(i),
l_rec_col.legal_entity_name(i)
INTO x_le_list(i).legal_entity_id,
x_le_list(i).legal_entity_name
FROM dual;
SELECT NVL(bal_seg_value_option_code,'A')
INTO l_bal_seg_opt
FROM GL_LEDGERS
WHERE ledger_id = p_ledger_id;
SELECT legal_entity_id
, legal_entity_name
BULK COLLECT INTO
l_rec_col.legal_entity_id,
l_rec_col.legal_entity_name
FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = p_ledger_id
AND bal_seg_value = p_bal_seg_value
AND ((p_bsv_eff_date IS NULL)
OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
AND legal_entity_id IS NOT NULL
ORDER BY legal_entity_id;
SELECT DISTINCT legal_entity_id
, legal_entity_name
BULK COLLECT INTO
l_rec_col.legal_entity_id,
l_rec_col.legal_entity_name
FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = p_ledger_id
AND ((p_bsv_eff_date IS NULL)
OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
AND legal_entity_id IS NOT NULL
ORDER BY legal_entity_id;
SELECT l_rec_col.legal_entity_id(i),
l_rec_col.legal_entity_name(i)
INTO x_le_list(i).legal_entity_id,
x_le_list(i).legal_entity_name
FROM dual;
** SELECT 'Y'
** INTO l_bsv_assigned
** FROM GL_LEDGER_LE_BSV_GT
** WHERE ledger_id = p_ledger_id
** AND bal_seg_value = p_bal_seg_value
** AND ((p_bsv_eff_date IS NULL)
** OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
** AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)));
SELECT lg.LEDGER_ID
INTO l_ledger_id
FROM GL_LEDGER_CONFIG_DETAILS cfDet,
GL_LEDGERS lg
WHERE cfDet.OBJECT_ID = p_legal_entity_id
AND cfDet.OBJECT_TYPE_CODE = 'LEGAL_ENTITY'
AND lg.CONFIGURATION_ID = cfDet.CONFIGURATION_ID
AND lg.LEDGER_CATEGORY_CODE = 'PRIMARY';
SELECT DECODE(bal_seg_value_option_code, 'I', 'N', 'Y')
INTO x_allow_all_bsv_flag
FROM GL_LEDGERS
WHERE ledger_id = l_ledger_id;
SELECT bal_seg_value
, legal_entity_id
, legal_entity_name
BULK COLLECT INTO
l_rec_col.bal_seg_value,
l_rec_col.legal_entity_id,
l_rec_col.legal_entity_name
FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = l_ledger_id
AND legal_entity_id = p_legal_entity_id
AND ((p_bsv_eff_date IS NULL)
OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
ORDER BY bal_seg_value, legal_entity_id;
SELECT legal_entity_name
INTO l_le_name
FROM GL_LEDGER_LE_V
WHERE ledger_id = l_ledger_id
AND legal_entity_id = p_legal_entity_id
AND relationship_enabled_flag = 'Y';
SELECT bal_seg_value
, NVL(legal_entity_id, p_legal_entity_id)
, NVL(legal_entity_name, l_le_name)
BULK COLLECT INTO
l_rec_col.bal_seg_value,
l_rec_col.legal_entity_id,
l_rec_col.legal_entity_name
FROM GL_LEDGER_LE_BSV_GT
WHERE ledger_id = l_ledger_id
AND ((p_bsv_eff_date IS NULL)
OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
ORDER BY bal_seg_value, legal_entity_id;
SELECT l_rec_col.bal_seg_value(i),
l_rec_col.legal_entity_id(i),
l_rec_col.legal_entity_name(i)
INTO x_bsv_list(i).bal_seg_value,
x_bsv_list(i).legal_entity_id,
x_bsv_list(i).legal_entity_name
FROM dual;
SELECT multi_currency_flag
INTO mrc_install
FROM fnd_product_groups
WHERE product_group_id = 1;
SELECT count(*)
INTO l_count
FROM GL_LEDGER_RELATIONSHIPS GLR
WHERE GLR.source_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'ALC'
AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND GLR.application_id = n_appl_id
AND GLR.relationship_enabled_flag = 'Y'
AND (n_org_id IS NULL
OR GLR.org_id = -99
OR GLR.org_id = NVL(n_org_id,-99))
AND (NVL(n_fa_book_code, '-99') = '-99'
OR EXISTS
(SELECT 'FA book type is enabled'
FROM FA_MC_BOOK_CONTROLS MC
WHERE MC.set_of_books_id = GLR.target_ledger_id
AND MC.book_type_code = n_fa_book_code
AND MC.primary_set_of_books_id = GLR.source_ledger_id
AND MC.enabled_flag = 'Y'));
SELECT count(*)
INTO l_count
FROM GL_LEDGER_RELATIONSHIPS GLR
WHERE GLR.target_ledger_id = n_ledger_id
AND GLR.target_ledger_category_code = 'ALC'
AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND GLR.application_id = n_appl_id
AND GLR.relationship_enabled_flag = 'Y'
AND (n_org_id IS NULL
OR GLR.org_id = -99
OR GLR.org_id = NVL(n_org_id,-99))
AND (NVL(n_fa_book_code, '-99') = '-99'
OR EXISTS
(SELECT 'FA book type is enabled'
FROM FA_MC_BOOK_CONTROLS MC
WHERE MC.set_of_books_id = GLR.target_ledger_id
AND MC.book_type_code = n_fa_book_code
AND MC.primary_set_of_books_id = GLR.source_ledger_id
AND MC.enabled_flag = 'Y'));
SELECT count(*)
INTO l_count
FROM GL_LEDGER_RELATIONSHIPS GLR
WHERE GLR.target_ledger_category_code = 'ALC'
AND GLR.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND GLR.application_id = n_appl_id
AND GLR.relationship_enabled_flag = 'Y';
SELECT distinct g.target_ledger_id
BULK COLLECT INTO n_alc_id_list
FROM gl_ledger_relationships g
WHERE g.source_ledger_id = n_src_ledger_id
AND g.target_ledger_category_code = 'ALC'
AND g.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND g.relationship_enabled_flag = 'Y';
SELECT g.ledger_id,
g.name,
g.short_name,
g.currency_code,
g.alc_ledger_type_code,
g.ledger_category_code,
g.sla_accounting_method_code,
f.precision,
f.minimum_accountable_unit,
DECODE(g.alc_ledger_type_code, 'SOURCE', 'P'
, 'TARGET', 'R'
, 'N'),
DECODE(g.alc_ledger_type_code, 'SOURCE', 'Primary'
, 'TARGET', 'Reporting'
, 'Notassigned')
BULK COLLECT INTO
l_ledger_rec_col.r_sob_id,
l_ledger_rec_col.r_sob_name,
l_ledger_rec_col.r_sob_short_name,
l_ledger_rec_col.r_sob_curr,
l_ledger_rec_col.r_alc_type,
l_ledger_rec_col.r_category,
l_ledger_rec_col.r_acct_method_code,
l_ledger_rec_col.r_precision,
l_ledger_rec_col.r_mau,
l_ledger_rec_col.r_sob_type,
l_ledger_rec_col.r_sob_user_type
FROM gl_ledgers g,
fnd_currencies f
-- Include ALC source ledger if n_include_source_ledger = 'Y' or NULL
WHERE ((upper(NVL(n_include_source_ledger, 'Y')) = 'Y'
AND g.ledger_id = l_src_ledger_id)
OR g.ledger_id IN (
SELECT glr.target_ledger_id -- ALC target ledgers
FROM gl_ledger_relationships glr
WHERE glr.source_ledger_id = l_src_ledger_id
AND glr.target_ledger_category_code = 'ALC'
AND glr.relationship_type_code IN ('SUBLEDGER', 'JOURNAL')
AND glr.application_id = n_appl_id
AND (n_org_id IS NULL
OR glr.org_id = -99
OR glr.org_id = NVL(n_org_id,-99))
AND (NVL(n_fa_book_code, '-99') = '-99'
OR EXISTS
(SELECT 'FA book type is enabled'
FROM FA_MC_BOOK_CONTROLS MC
WHERE MC.set_of_books_id = glr.target_ledger_id
AND MC.book_type_code = n_fa_book_code
AND MC.primary_set_of_books_id = glr.source_ledger_id
AND MC.enabled_flag = 'Y'))
AND glr.relationship_enabled_flag = 'Y'))
AND g.currency_code = f.currency_code
ORDER BY DECODE(g.ledger_category_code
, 'Primary' , 1
, 'Secondary', 2
, 3)
, g.ledger_id;
SELECT l_ledger_rec_col.r_sob_id(i),
l_ledger_rec_col.r_sob_name(i),
l_ledger_rec_col.r_sob_short_name(i),
l_ledger_rec_col.r_sob_curr(i),
l_ledger_rec_col.r_alc_type(i),
l_ledger_rec_col.r_category(i),
l_ledger_rec_col.r_acct_method_code(i),
l_ledger_rec_col.r_precision(i),
l_ledger_rec_col.r_mau(i),
l_ledger_rec_col.r_sob_type(i),
l_ledger_rec_col.r_sob_user_type(i)
INTO n_ledger_list(i).r_sob_id,
n_ledger_list(i).r_sob_name,
n_ledger_list(i).r_sob_short_name,
n_ledger_list(i).r_sob_curr,
n_ledger_list(i).r_alc_type,
n_ledger_list(i).r_category,
n_ledger_list(i).r_acct_method_code,
n_ledger_list(i).r_precision,
n_ledger_list(i).r_mau,
n_ledger_list(i).r_sob_type,
n_ledger_list(i).r_sob_user_type
FROM dual;
SELECT g.ledger_id,
g.name,
g.short_name,
g.currency_code,
g.alc_ledger_type_code,
g.ledger_category_code,
g.sla_accounting_method_code,
f.precision,
f.minimum_accountable_unit,
DECODE(g.alc_ledger_type_code, 'SOURCE', 'P'
, 'TARGET', 'R'
, 'N'),
DECODE(g.alc_ledger_type_code, 'SOURCE', 'Primary'
, 'TARGET', 'Reporting'
, 'Notassigned')
BULK COLLECT INTO
l_ledger_rec_col.r_sob_id,
l_ledger_rec_col.r_sob_name,
l_ledger_rec_col.r_sob_short_name,
l_ledger_rec_col.r_sob_curr,
l_ledger_rec_col.r_alc_type,
l_ledger_rec_col.r_category,
l_ledger_rec_col.r_acct_method_code,
l_ledger_rec_col.r_precision,
l_ledger_rec_col.r_mau,
l_ledger_rec_col.r_sob_type,
l_ledger_rec_col.r_sob_user_type
FROM gl_ledgers g,
fnd_currencies f
-- Include primary ledger only if n_include_primary_ledger is Y or NULL
WHERE ((upper(NVL(n_include_primary_ledger, 'Y')) = 'Y'
AND g.ledger_id = l_pri_ledger_id)
OR g.ledger_id IN (
SELECT glr.target_ledger_id -- Secondary Ledgers
FROM gl_ledger_relationships glr, gl_ledgers lgr_c
WHERE glr.primary_ledger_id = l_pri_ledger_id
AND glr.target_ledger_category_code = 'SECONDARY'
AND glr.relationship_type_code <> 'NONE'
AND glr.target_ledger_id = lgr_c.ledger_id
AND NVL(lgr_c.complete_flag,'Y') = 'Y'
AND glr.application_id = n_appl_id
AND (n_org_id IS NULL
OR glr.org_id = -99
OR glr.org_id = NVL(n_org_id,-99))
AND glr.relationship_enabled_flag = 'Y'))
AND g.currency_code = f.currency_code
ORDER BY DECODE(g.ledger_category_code
, 'Primary' , 1
, 'Secondary', 2
, 3)
, g.ledger_id;
SELECT l_ledger_rec_col.r_sob_id(i),
l_ledger_rec_col.r_sob_name(i),
l_ledger_rec_col.r_sob_short_name(i),
l_ledger_rec_col.r_sob_curr(i),
l_ledger_rec_col.r_alc_type(i),
l_ledger_rec_col.r_category(i),
l_ledger_rec_col.r_acct_method_code(i),
l_ledger_rec_col.r_precision(i),
l_ledger_rec_col.r_mau(i),
l_ledger_rec_col.r_sob_type(i),
l_ledger_rec_col.r_sob_user_type(i)
INTO n_ledger_list(i).r_sob_id,
n_ledger_list(i).r_sob_name,
n_ledger_list(i).r_sob_short_name,
n_ledger_list(i).r_sob_curr,
n_ledger_list(i).r_alc_type,
n_ledger_list(i).r_category,
n_ledger_list(i).r_acct_method_code,
n_ledger_list(i).r_precision,
n_ledger_list(i).r_mau,
n_ledger_list(i).r_sob_type,
n_ledger_list(i).r_sob_user_type
FROM dual;
ael_sob_info.delete;
SELECT currency_code, name
INTO ael_sob_info(j).currency_code,
ael_sob_info(j).sob_name
FROM gl_ledgers
WHERE ledger_id = l_aa(l_cnt).sob_id;
l_sob_list.delete;
l_sob_list.delete;
l_insert_statement DBMS_SQL.VARCHAR2S;
SELECT 'Already Populated'
INTO l_status
FROM dual
WHERE EXISTS
(SELECT 'x'
FROM gl_ledger_bsv_gt
WHERE ledger_id = n_ledger_id);
SELECT nvl(fvt.application_table_name, 'FND_FLEX_VALUES')
, nvl(fvt.value_column_name, 'FLEX_VALUE')
, fvs.validation_type
, nvl(fvt.meaning_column_name, 'DESCRIPTION')
INTO l_fv_table
, l_fv_col
, l_fv_type
, l_fv_description
FROM fnd_flex_value_sets fvs
, fnd_flex_validation_tables fvt
, gl_ledgers gl
WHERE fvs.flex_value_set_id = gl.bal_seg_value_set_id
AND gl.ledger_id = n_ledger_id
AND fvt.flex_value_set_id = fvs.flex_value_set_id;
INSERT INTO GL_LEDGER_BSV_GT
( FLEX_VALUE
,DESCRIPTION
,LEDGER_ID
)
SELECT FlexValues.FLEX_VALUE,
FlexValues.DESCRIPTION,
n_ledger_id
FROM GL_LEDGERS Ledgers,
FND_FLEX_VALUES_VL FlexValues
WHERE FlexValues.FLEX_VALUE_SET_ID = Ledgers.bal_seg_value_set_id
AND Ledgers.ledger_id = n_ledger_id
AND FlexValues.SUMMARY_FLAG = 'N';
l_insert_statement(l_line_num) := 'INSERT INTO gl_ledger_bsv_gt ';
l_insert_statement(l_line_num) := '(FLEX_VALUE,DESCRIPTION,LEDGER_ID)';
l_insert_statement(l_line_num) := 'SELECT '||l_fv_col||',';
l_insert_statement(l_line_num) := l_fv_description||','||n_ledger_id;
l_insert_statement(l_line_num) := 'FROM '||l_fv_table;
dbms_sql.parse(l_cursor, l_insert_statement,1,
l_line_Num, true, dbms_sql.native);