[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT vals.legal_entity_id
FROM gl_ledger_le_bsv_specific_v vals
WHERE vals.segment_value = p_bsv
AND vals.ledger_id = p_ledger_id
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(vals.start_date, p_gl_date)) AND
TRUNC(NVL(vals.end_date, p_gl_date)));
SELECT fun_bal_pkg.get_segment_index(ledgers.chart_of_accounts_id,
p_segment_type),
ledgers.chart_of_accounts_id
FROM gl_ledgers ledgers
WHERE ledgers.ledger_id = p_ledger_id;
SELECT NVL( (SELECT ccid
FROM fun_inter_accounts accts
WHERE accts.ledger_id = p_ledger_id
AND accts.from_le_id = p_from_le_id
AND accts.to_le_id = p_to_le_id
AND accts.trans_bsv = p_from_bsv
AND accts.tp_bsv = p_to_bsv
AND accts.type = p_acct_type
AND accts.default_flag = 'Y'
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
AND TRUNC(NVL(accts.end_date, p_gl_date)))),
NVL((SELECT ccid
FROM fun_inter_accounts accts
WHERE accts.ledger_id = p_ledger_id
AND accts.from_le_id = p_from_le_id
AND accts.to_le_id = p_to_le_id
AND accts.trans_bsv = p_from_bsv
AND accts.tp_bsv = 'OTHER1234567890123456789012345'
AND accts.type = p_acct_type
AND accts.default_flag = 'Y'
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
AND TRUNC(NVL(accts.end_date, p_gl_date)))),
NVL((SELECT ccid
FROM fun_inter_accounts accts
WHERE accts.ledger_id = p_ledger_id
AND accts.from_le_id = p_from_le_id
AND accts.to_le_id = p_to_le_id
AND accts.trans_bsv = 'OTHER1234567890123456789012345'
AND accts.tp_bsv = p_to_bsv
AND accts.type = p_acct_type
AND accts.default_flag = 'Y'
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
AND TRUNC(NVL(accts.end_date, p_gl_date)))),
NVL((SELECT ccid
FROM fun_inter_accounts accts
WHERE accts.ledger_id = p_ledger_id
AND accts.from_le_id = p_from_le_id
AND accts.to_le_id = p_to_le_id
AND accts.trans_bsv = 'OTHER1234567890123456789012345'
AND accts.tp_bsv = 'OTHER1234567890123456789012345'
AND accts.type = p_acct_type
AND accts.default_flag = 'Y'
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
AND TRUNC(NVL(accts.end_date, p_gl_date)))),
(SELECT ccid
FROM fun_inter_accounts accts
WHERE accts.ledger_id = p_ledger_id
AND accts.from_le_id = p_from_le_id
AND accts.to_le_id = -99
AND accts.type = p_acct_type
AND accts.default_flag = 'Y'
AND (TRUNC(p_gl_date) BETWEEN TRUNC(NVL(accts.start_date, p_gl_date))
AND TRUNC(NVL(accts.end_date, p_gl_date)))))))) ccid
From Dual;
l_insert_flag VARCHAR2(1) := 'N'; --8200511
SELECT 'Y' INTO l_insert_flag
FROM FUN_INTER_ACCOUNTS_V
WHERE FROM_LE_ID = p_from_le
AND LEDGER_ID = p_ledger_id
AND TO_LE_ID = p_to_le
AND CCID = x_ccid
AND TYPE = p_acct_type
AND TRANS_BSV = p_from_bsv
AND TP_BSV = p_to_bsv;
l_insert_flag := 'N';
l_insert_flag := 'Y';
IF (l_insert_flag = 'N') THEN
INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
, LEDGER_ID
, TO_LE_ID
, CCID
, TYPE
, START_DATE
, DEFAULT_FLAG
, OBJECT_VERSION_NUMBER
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, TRANS_BSV
, TP_BSV
)
VALUES(p_from_le
, p_ledger_id
, p_to_le
, x_ccid
, p_acct_type
, SYSDATE
, NULL
, 1
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, fnd_global.login_id
, p_from_bsv
, p_to_bsv
);
SELECT 'Y' INTO l_insert_flag
FROM FUN_INTER_ACCOUNTS_V
WHERE FROM_LE_ID = p_to_le
AND LEDGER_ID = p_to_ledger_id
AND TO_LE_ID = p_from_le
AND CCID = x_reciprocal_ccid
AND TYPE = l_recip_acct_type
AND TRANS_BSV = p_to_bsv
AND TP_BSV = p_from_bsv;
l_insert_flag := 'N';
l_insert_flag := 'Y';
IF (l_insert_flag = 'N') THEN
INSERT INTO FUN_INTER_ACCOUNTS_ADDL(FROM_LE_ID
, LEDGER_ID
, TO_LE_ID
, CCID
, TYPE
, START_DATE
, DEFAULT_FLAG
, OBJECT_VERSION_NUMBER
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, TRANS_BSV
, TP_BSV
)
VALUES(p_to_le
, p_to_ledger_id
, p_from_le
, x_reciprocal_ccid
, l_recip_acct_type
, SYSDATE
, NULL
, 1
, FND_GLOBAL.USER_ID
, SYSDATE
, FND_GLOBAL.USER_ID
, SYSDATE
, fnd_global.login_id
, p_to_bsv
, p_from_bsv
);
SELECT NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = p_ledger_id
AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
AND opts.je_source_name = p_source_name
AND opts.je_category_name = p_category_name
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = p_ledger_id
AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
AND opts.je_source_name = p_source_name
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y'),
NVL((SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = p_ledger_id
AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = p_category_name
AND opts.status_flag = 'Y'),
(SELECT opts.template_id
FROM fun_balance_options opts
WHERE opts.ledger_id = p_ledger_id
AND Nvl(opts.le_id,-99) = Nvl(p_le_id,-99)
AND opts.je_source_name = 'Other'
AND opts.je_category_name = 'Other'
AND opts.status_flag = 'Y')))) template_id
From Dual;
SELECT NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
FROM fun_balance_accounts accts
WHERE accts.template_id = p_template_id
AND accts.dr_bsv = p_dr_bsv
AND accts.cr_bsv = p_cr_bsv),
NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
FROM fun_balance_accounts accts
WHERE accts.template_id = p_template_id
AND accts.dr_bsv = p_dr_bsv
AND accts.cr_bsv = 'OTHER1234567890123456789012345'),
NVL((SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
FROM fun_balance_accounts accts
WHERE accts.template_id = p_template_id
AND accts.dr_bsv = 'OTHER1234567890123456789012345'
AND accts.cr_bsv = p_cr_bsv),
(SELECT DECODE (p_acct_type, 'D', accts.dr_ccid, 'C', cr_ccid)
FROM fun_balance_accounts accts
WHERE accts.template_id = p_template_id
AND accts.dr_bsv = 'OTHER1234567890123456789012345'
AND accts.cr_bsv = 'OTHER1234567890123456789012345')))) ccid
From Dual;