EBS总账信息查询

以下例子中，segment1=公司，segment2=部门， segment3=科目，segment4=子科目，segment5=往来,segment6=产品,segment7=项目
以下为查询语句：
select gjb.name,
gjh.name,
gjh.currency_code,
gjh.default_effective_date,
gcc.segment1 公司,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=74 and flv.FLEX_VALUE=gcc.segment1) 公司名称,
gcc.segment2 部门,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=75 and flv.FLEX_VALUE=gcc.segment2) 部门名称,
gcc.segment3 科目,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=76 and flv.FLEX_VALUE=gcc.segment3) 科目名称,
gcc.segment4 子目,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=77 and flv.FLEX_VALUE=gcc.segment4) 子目名称,
gcc.segment5 往来,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=78 and flv.FLEX_VALUE=gcc.segment5) 往来名称,
gcc.segment6 产品,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=79 and flv.FLEX_VALUE=gcc.segment6) 产品名称,
gcc.segment7 项目,
(select flv.DESCRIPTION from fnd_flex_values_vl flv where flv.FLEX_VALUE_SET_ID=80 and flv.FLEX_VALUE=gcc.segment7) 项目名称,
gjl.entered_dr,
gjl.entered_cr,
gjl.accounted_dr,
gjl.accounted_cr,
gjl.description
from gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
where gjb.je_batch_id = gjh.je_batch_id
and gjh.je_header_id = gjl.je_header_id
and gjl.code_combination_id = gcc.code_combination_id
and gjh.period_name = '2022-10'--期间
and gjh.status = 'P' --过账状态
and gcc.segment1 = 'Apple' --公司编码
order by gjh.default_effective_date,gjh.je_header_id

--查询科目余额
SELECT ba.currency_code
,
nvl(ba.begin_balance_dr, 0) - nvl(ba.begin_balance_cr, 0) begin_balance --期初余额 原币
,
decode(ba.currency_code,
'CNY',
nvl(ba.begin_balance_dr, 0) -
nvl(ba.begin_balance_cr, 0),
nvl(ba.begin_balance_dr_beq, 0) -
nvl(ba.begin_balance_cr_beq, 0)) begin_balance_beq --期初余额 本币
,
ba.period_net_dr --本期借方发生额 原币
,
ba.period_net_dr_beq --本期借方发生额 本币
,
ba.period_net_cr --本期贷方发生额 原币
,
ba.period_net_cr_beq --本期贷方发生额 本币
,
nvl(ba.period_net_dr_beq, 0) -
nvl(ba.period_net_cr_beq, 0) --PTD
,
(nvl(ba.begin_balance_dr, 0) -
nvl(ba.begin_balance_cr, 0) + nvl(ba.period_net_dr, 0) -
nvl(ba.period_net_cr, 0)) ytd_balance --期末余额 原币
,
(nvl(ba.begin_balance_dr_beq, 0) -
nvl(ba.begin_balance_cr_beq, 0) +
nvl(ba.period_net_dr_beq, 0) -
nvl(ba.period_net_cr_beq, 0)) ytd_balance_beq --期末余额 本币
from gl_balances ba
where code_combination_id = 82706--科目主键
and period_name = '2022-04'--期间
and ledger_id = 2042--分类账簿主键