Oracle EBS 应收发票取值

SELECT ct.trx_number
      ,ctl.description
      ,fnd_flex_ext.get_segs('SQLGL'
                            ,'GL#'
                            ,gcc.chart_of_accounts_id
                            ,gcc.code_combination_id) account_number
      ,gd.gl_date
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0))
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL))) entered_dr
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL)
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0)))) entered_cr
  FROM ra_customer_trx_all          ct  --发票头
      ,ra_customer_trx_lines_all    ctl  --发票行
      ,ra_cust_trx_line_gl_dist_all gd   --行分配表  ACCOUNT_CLASS字段 收入为REV，税为TAX,应收账款为REC
      ,gl_code_combinations         gcc
 WHERE gd.customer_trx_id = ct.customer_trx_id
   AND gd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND gcc.code_combination_id = gd.code_combination_id;
   AND ct.customer_trx_id = &customer_trx_id;

 

转载于:https://www.cnblogs.com/jenrry/p/10007348.html