[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT length(MAX(rra.axis_seq))
FROM rg_reports rr, rg_report_axes rra
WHERE rr.report_id = pn_cfs_report_id
AND rr.ROW_SET_ID = rra.axis_set_id;
SELECT DISTINCT *
FROM (SELECT jcjl.je_category,
jcjl.je_header_id,
--jcjl.journal_number --comment by Jianchao Chi for voucher number change, on 04-JAN-2011, and add the following line
jcvn.voucher_number journal_number,-- Update from "jcjl.journal_number"
jcjl.je_line_num,
jccaa.original_curr_code,
jcjl.description,
lpad(jccasa.axis_seq, pn_cash_flow_item_rule, '0') axis_seq,
DECODE(jccasa1.item_attribute,
'Inflow',
'1',
'Outflow',
'0',
'Others',
'2',
'9') item_attribute,
jccaa.original_amount,
jccaa.func_amount,
gps.start_date
FROM ja_cn_cfs_activities_all jccaa,
ja_cn_journal_lines jcjl,
ja_cn_cfs_assignments_all jccasa,
ja_cn_cfs_assign_sup_all jccasa1,
rg_reports rr,
gl_period_statuses gps,
ja_cn_voucher_number jcvn
--Update by Jianchao Chi, for cnaov2 upgrade 04-JAN-2011
--Add ja_cn_voucher_number table, voucher number and legal entity
--are fetched from ja_cn_voucher_number
--The new one is
----------------------------------------------------
--AND jcvn.je_header_id = jl.je_header_id
--AND jcvn.je_line_number = jl.je_line_num
----------------------------------------------------
WHERE jccaa.je_header_id = jcjl.je_header_id
AND jccaa.je_line_num = jcjl.je_line_num
--Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
AND jcvn.je_header_id = jcjl.je_header_id
AND jcvn.je_line_number = jcjl.je_line_num
AND jccaa.legal_entity_id = jcvn.legal_entity_id
AND jccaa.ledger_id = jcjl.ledger_id
AND gps.ledger_id = pn_ledger_id
AND gps.application_id = 101
AND gps.period_name = jccaa.period_name
AND (ABS(jccaa.original_amount) = ABS(jcjl.entered_dr) OR
ABS(jccaa.original_amount) = ABS(jcjl.entered_cr))
AND ABS(jccaa.original_amount) > 0
AND jccaa.transaction_type IN ('SLA', 'AGIS')
AND jccaa.legal_entity_id = pn_legal_entity_id
AND jccasa.chart_of_accounts_id = pn_coa_id
AND jccasa.detailed_cfs_item = jccaa.detailed_cfs_item
AND rr.report_id = pn_cfs_report_id
AND jccasa.axis_set_id = rr.row_set_id
AND jccasa1.chart_of_accounts_id =
jccasa.chart_of_accounts_id
AND jccasa1.axis_set_id = jccasa.axis_set_id
AND jccasa1.axis_seq = jccasa.axis_seq
AND jccaa.balancing_segment IN
(SELECT bal_seg_value
FROM ja_cn_ledger_le_bsv_gt
WHERE legal_entity_id = pn_legal_entity_id
AND ledger_id = pn_ledger_id)
AND jccaa.period_name IN
(SELECT period_name
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN ld_start_date AND
ld_end_date) AND
(end_date BETWEEN ld_start_date AND ld_end_date)))
AND jccaa.ledger_id = pn_ledger_id
UNION ALL
SELECT jcjl.je_category,
jcjl.je_header_id,
--jcjl.journal_number --comment by Jianchao Chi for voucher number change, on 04-JAN-2011, and add the following line
jcvn.voucher_number journal_number,-- Update from "jcjl.journal_number"
jcjl.je_line_num,
jccaa.original_curr_code,
jcjl.description,
lpad(jccasa.axis_seq, pn_cash_flow_item_rule, '0') axis_seq,
DECODE(jccasa1.item_attribute,
'Inflow',
'1',
'Outflow',
'0',
'Others',
'2',
'9') item_attribute,
jccaa.original_amount,
jccaa.func_amount,
gps.start_date
FROM ja_cn_cfs_activities_all jccaa,
ja_cn_journal_lines jcjl,
ja_cn_cfs_assignments_all jccasa,
ja_cn_cfs_assign_sup_all jccasa1,
rg_reports rr,
gl_period_statuses gps,
ja_cn_voucher_number jcvn
--Update by Jianchao Chi, for cnaov2 upgrade 04-JAN-2011
--Add ja_cn_voucher_number table, voucher number and legal entity
--are fetched from ja_cn_voucher_number
--The new one is
----------------------------------------------------
--AND jcvn.je_header_id = jl.je_header_id
--AND jcvn.je_line_number = jl.je_line_num
----------------------------------------------------
WHERE jccaa.trx_id = jcjl.je_header_id
AND jccaa.trx_line_id = jcjl.je_line_num
--Add the follow two conditions by Jianchao Chi to change the logic of legal entity, 05-JAN-2011
AND jcvn.je_header_id = jcjl.je_header_id
AND jcvn.je_line_number = jcjl.je_line_num
AND jccaa.legal_entity_id = jcvn.legal_entity_id
AND jccaa.ledger_id = jcjl.ledger_id
AND gps.ledger_id = pn_ledger_id
AND gps.application_id = 101
AND gps.period_name = jccaa.period_name
AND (ABS(jccaa.original_amount) = ABS(jcjl.entered_dr) OR
ABS(jccaa.original_amount) = ABS(jcjl.entered_cr))
AND ABS(jccaa.original_amount) > 0
AND jccaa.transaction_type = 'JOURNAL'
AND jccaa.legal_entity_id = pn_legal_entity_id
AND jccasa.chart_of_accounts_id = pn_coa_id
AND jccasa.detailed_cfs_item = jccaa.detailed_cfs_item
AND rr.report_id = pn_cfs_report_id
AND jccasa.axis_set_id = rr.row_set_id
AND jccasa1.chart_of_accounts_id =
jccasa.chart_of_accounts_id
AND jccasa1.axis_set_id = jccasa.axis_set_id
AND jccasa1.axis_seq = jccasa.axis_seq
AND jccaa.balancing_segment IN
(SELECT bal_seg_value
FROM ja_cn_ledger_le_bsv_gt
WHERE legal_entity_id = pn_legal_entity_id
AND ledger_id = pn_ledger_id)
AND jccaa.period_name IN
(SELECT period_name
FROM gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND ((start_date BETWEEN ld_start_date AND
ld_end_date) AND
(end_date BETWEEN ld_start_date AND ld_end_date)))
AND jccaa.ledger_id = pn_ledger_id)
ORDER BY 11, 3, 4;
SELECT start_date
INTO ld_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND to_char(period_year) = pv_accounting_year;
SELECT end_date
INTO ld_end_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND to_char(period_year) = pv_accounting_year;