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;