[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT settlement_method_number, settlement_method_name
FROM (SELECT ffv.flex_value settlement_method_number,
ffv.description settlement_method_name
FROM fnd_flex_values_vl ffv, fnd_flex_value_sets ffvs
WHERE ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffvs.flex_value_set_name = 'JA_CN_SETTLEMENT_METHOD')
--Only existing settlement method in Itemized Journal table should be exported
WHERE settlement_method_number IN
(SELECT jl.settlement_method_number
FROM ja_cn_journal_lines jl, ja_cn_voucher_number jcvn
--Update by Jianchao Chi, for cnaov2 upgrade, 06-Jan-2011
--Add ja_cn_voucher_number table, voucher number and legal entity
--are fetched from ja_cn_voucher_number
--The previous one was:
----------------------------------------------------
--FROM ja_cn_journal_lines jl
--WHERE jl.legal_entity_id = pn_legal_entity
----------------------------------------------------
--Add the following three condidions
WHERE jcvn.legal_entity_id = pn_legal_entity --Legal entity ID parameter
AND jcvn.je_header_id = jl.je_header_id
AND jcvn.je_line_number = jl.je_line_num
AND jl.ledger_id = pn_ledger -- Ledger ID Parameter
AND jl.period_name IN
(SELECT gps.period_name
FROM gl_period_statuses gps
WHERE gps.ledger_id = jl.ledger_id
AND gps.application_id = 101
AND gps.period_year = pn_account_year --Accounting Year parameter
))
ORDER BY settlement_method_number;