[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT legal_entity_id
FROM gl_ledger_norm_seg_vals
WHERE ledger_id = pn_ledger_id;
SELECT jcjl.je_header_id,
jcjl.default_effective_date,
jcjl.period_name,
gjh.POSTING_ACCT_SEQ_VALUE,
jcjl.je_line_num,
jcjl.journal_number
FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
WHERE gjh.je_header_id = jcjl.je_header_id
AND jcjl.ledger_id = pn_ledger_id
-- CHOLI add this line for CNAO V1 to V2 Upgrade
AND jcjl.journal_number IS NOT NULL
--AND jcjl.status = 'P'
AND gjh.period_name = pv_period_name
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(jcjl.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
AND NOT EXISTS
(SELECT je_header_id, je_line_number
FROM ja_cn_voucher_number jcvn
WHERE gjh.je_header_id = jcvn.je_header_id
AND jcjl.je_line_num = jcvn.je_line_number
AND jcvn.ledger_id = pn_ledger_id
AND jcvn.legal_entity_id = pn_legal_entity_id
AND jcvn.period_name = pv_period_name)
ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
gjh.default_effective_date ASC,
gjh.posted_date ASC,
gjh.je_header_id ASC;
SELECT jcjl.je_header_id,
jcjl.default_effective_date,
jcjl.period_name,
gjh.POSTING_ACCT_SEQ_VALUE,
jcjl.je_line_num,
jcjl.journal_number
FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
WHERE gjh.je_header_id = jcjl.je_header_id
AND jcjl.ledger_id = pn_ledger_id
-- CHOLI add this line for CNAO V1 to V2 Upgrade
AND jcjl.journal_number IS NOT NULL
--AND jcjl.status = 'P'
AND gjh.period_name = pv_period_name
AND NOT EXISTS
(SELECT je_header_id, je_line_number
FROM ja_cn_voucher_number jcvn
WHERE gjh.je_header_id = jcvn.je_header_id
AND jcjl.je_line_num = jcvn.je_line_number
AND jcvn.ledger_id = pn_ledger_id
AND jcvn.legal_entity_id = pn_legal_entity_id
AND jcvn.period_name = pv_period_name)
ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
gjh.default_effective_date ASC,
gjh.posted_date ASC,
gjh.je_header_id ASC;
SELECT COUNT(*) --Add for bug 11670727, to judge the migration amount is more then 0 or not
INTO ln_bsv_mig_num
FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
WHERE gjh.je_header_id = jcjl.je_header_id
AND jcjl.ledger_id = pn_ledger_id
--AND jcjl.status = 'P'
AND gjh.period_name = pv_period_name
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_VOUCHER_NUM_PKG.get_balancing_segment(jcjl.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
AND NOT EXISTS
(SELECT je_header_id, je_line_number
FROM ja_cn_voucher_number jcvn
WHERE gjh.je_header_id = jcvn.je_header_id
AND jcjl.je_line_num = jcvn.je_line_number
AND jcvn.ledger_id = pn_ledger_id
AND jcvn.legal_entity_id = pn_legal_entity_id
AND jcvn.period_name = pv_period_name);
SELECT COUNT(*)
INTO ln_no_bsv_mig_num
FROM gl_je_headers gjh, ja_cn_journal_lines jcjl
WHERE gjh.je_header_id = jcjl.je_header_id
AND jcjl.ledger_id = pn_ledger_id
--AND jcjl.status = 'P'
AND gjh.period_name = pv_period_name
AND NOT EXISTS
(SELECT je_header_id, je_line_number
FROM ja_cn_voucher_number jcvn
WHERE gjh.je_header_id = jcvn.je_header_id
AND jcjl.je_line_num = jcvn.je_line_number
AND jcvn.ledger_id = pn_ledger_id
AND jcvn.legal_entity_id = pn_legal_entity_id
AND jcvn.period_name = pv_period_name)
ORDER BY NVL(gjh.POSTING_ACCT_SEQ_VALUE, -1) ASC,
gjh.default_effective_date ASC,
gjh.posted_date ASC,
gjh.je_header_id ASC;
SELECT count(*)
INTO ln_bsv_assigned_num
FROM ja_cn_ledger_le_bsv_gt
WHERE ledger_id = pn_ledger_id;
DELETE FROM ja_cn_voucher_number
WHERE ledger_id = pn_ledger_id
AND legal_entity_id = pn_legal_entity_id
AND period_name = pv_period_name
AND (data_source_tag <> 'M' OR data_source_tag is null);
INSERT INTO ja_cn_voucher_number
(ledger_id,
legal_entity_id,
period_name,
je_header_id,
je_line_number,
voucher_number,
REQUEST_ID,
data_source_tag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(pn_ledger_id,
pn_legal_entity_id,
pv_period_name,
v_row.je_header_id,
v_row.je_line_num,
v_row.journal_number,
fnd_global.CONC_REQUEST_ID,
'M',
fnd_global.user_id,
SYSDATE(),
fnd_global.user_id,
SYSDATE(),
fnd_global.login_id);
DELETE FROM ja_cn_voucher_number
WHERE ledger_id = pn_ledger_id
AND period_name = pv_period_name
AND (data_source_tag <> 'M' OR data_source_tag is null);
INSERT INTO ja_cn_voucher_number
(ledger_id,
legal_entity_id,
period_name,
je_header_id,
je_line_number,
voucher_number,
REQUEST_ID,
data_source_tag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES
(pn_ledger_id,
v_row1.legal_entity_id,
pv_period_name,
v_row.je_header_id,
v_row.je_line_num,
v_row.journal_number,
fnd_global.CONC_REQUEST_ID,
'M',
fnd_global.user_id,
SYSDATE(),
fnd_global.user_id,
SYSDATE(),
fnd_global.login_id);
SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = pn_ledger_id
AND period_name in
(select distinct period_name
from ja_cn_journal_lines
where ledger_id = pn_ledger_id
and legal_entity_id = pn_legal_entity_id)
AND adjustment_period_flag = 'N'
AND closing_status <> 'N'
AND closing_status <> 'F'
ORDER BY start_date;
SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = P_LEDGER_ID
AND period_name in
(select distinct period_name
from ja_cn_journal_lines
where ledger_id = P_LEDGER_ID
and legal_entity_id = P_LEGAL_ENTITY_ID)
AND closing_status <> 'N'
AND closing_status <> 'F'
ORDER BY start_date;
SELECT COUNT(VOUCHER_NUMBER), --JA_CN_VOUCHER_NUMBER_HAS_DATA
MAX(VOUCHER_NUMBER),
MIN(VOUCHER_NUMBER)
INTO JOURNAL_AMOUNT, VOUCHER_NUMBER_TO, VOUCHER_NUMBER_FROM
FROM (SELECT DISTINCT VOUCHER_NUMBER
FROM JA_CN_VOUCHER_NUMBER
WHERE PERIOD_NAME = v_row.period_name
AND REQUEST_ID = fnd_global.CONC_REQUEST_ID);