EBS :从表 fnd_flex_values_vl 获取账户描述.sql
其中公司段、公司段、等对应的FLEX_VALUE_SET_ID 值得自己查询。select he.order_number,
he.order_date,
ve.vendor_name,
ve.vendor_number,
ve.contact_name,
ve.telephone,
he.description hedescription,
li.line_number,
ite.item_number,
ite.item_name,
li.quantity,
li.unit_price,
li.quantity * li.unit_price,
gccb.SEGMENT1 || '-' || gccb.SEGMENT2 || '-' || gccb.SEGMENT3 || '-' ||
gccb.SEGMENT4 || '-' || gccb.SEGMENT5 segment, --费用账号
ffvl1.Description || '.' || ffvl2.Description || '.' ||
ffvl3.Description || '.' || ffvl4.Description || '.' ||
ffvl5.Description accDESCRIPTION, --账号描述
li.description lidescription
from trn_order_headers_4040 he,
trn_order_lines_4040 li,
trn_vendors_4040 ve,
trn_items_4040 ite,
gl_code_combinations gccb,
fnd_flex_values_vl ffvl1,
fnd_flex_values_vl ffvl2,
fnd_flex_values_vl ffvl3,
fnd_flex_values_vl ffvl4,
fnd_flex_values_vl ffvl5
where he.header_id = li.header_id
and he.vendor_id = ve.vendor_id
and li.item_id = ite.item_id
and gccb.code_combination_id = li.expense_account_id
And (ffvl1.flex_value = gccb.segment1 And
ffvl1.FLEX_VALUE_SET_ID = '1002470') -- 公司段
And (ffvl2.flex_value = gccb.segment2 And
ffvl2.FLEX_VALUE_SET_ID = '1002471') -- 部门段
And (ffvl3.flex_value = gccb.segment3 And
ffvl3.FLEX_VALUE_SET_ID = '1002472') -- 科目段
And (ffvl4.flex_value = gccb.segment4 And
ffvl4.FLEX_VALUE_SET_ID = '1002473') -- 子科目段
And (ffvl5.flex_value = gccb.segment5 And
ffvl5.FLEX_VALUE_SET_ID = '1002474') -- 公司间段
and he.order_number = :p_order_number
and he.order_date between
nvl(to_date(:P_DATE_FROM, 'RRRR/MM/DD HH24:MI:SS'), He.ORDER_DATE) and
nvl(to_date(:P_DATE_TO, 'RRRR/MM/DD HH24:MI:SS'), He.ORDER_DATE)
order by li.line_number;
页:
[1]