SELECT xel.subinventory_code 子库,
decode(xel.lot_number,'','来源,非库存') 批次,
cux_public_pkg.get_item_segment1(81, xeh.inventory_item_id)物料编码,
cux_public_pkg.get_item_description(81, xeh.inventory_item_id) 物料品名,
xeh.transaction_uom 单位,
sum(xdl.unrounded_entered_dr)借方金额,
sum(xdl.unrounded_entered_cr) 贷方金额
FROM XLA_AE_HEADERS AH,
XLA_AE_LINES AL,
xla_distribution_linksxdl,
gmf_xla_extract_headers xeh,
gmf_xla_extract_lines xel
WHERE AH.AE_HEADER_ID = AL.AE_HEADER_ID
--AND AH.APPLICATION_ID = 555
AND AH.PERIOD_NAME = '-03'
and al.CODE_COMBINATION_ID =
(select k.code_combination_id
from gl_code_combinations_kfv k
where k.concatenated_segments = '10.0.141103.0.0.0.0')
and al.ae_header_id = xdl.ae_header_id
and al.ae_line_num = xdl.ae_line_num
and al.application_id = xdl.application_id
and xdl.source_distribution_id_num_1 = xel.line_id(+)
and xel.header_id = xeh.header_id(+)
group by xel.subinventory_code,
xel.lot_number,
xeh.inventory_item_id,
xeh.transaction_uom