学步园

--来源于应收日记帐类型为销售发票（标准视图：XLA_AR_INV_AEL_GL_V）
  --取凭证行追溯到的应收业务数据
select gjh.je_header_id,
       
       gjl.je_line_num,
       
       gjh.description, --摘要
       
       gjh.doc_sequence_value, --凭证号
       
       gjh.period_name, --期间
       
       gcc.segment6 item_number, --产品
       
       gcc.segment4 sub_accounts, --科目
       
       to_char(xag.trx_date, 'YYYY-MM-DD') trx_date， nvl(ctl.quantity_credited, ctl.quantity_invoiced) quantity, --数量
       
       nvl(ctl.revenue_amount, 0) rev_amount --金额

  from gl_je_headers_v gjh,
       
       gl_je_lines_v gjl,
       
       (select jeh.je_header_id je_header_id,
               
               r.je_line_num je_line_num,
               
               ct.trx_number trx_number_displayed,
               
               ctl.line_number trx_line_number,
               
               ct.trx_date
        
          from gl_je_headers jeh,
               
               gl_import_references r,
               
               ra_cust_trx_line_gl_dist ctlgd,
               
               ra_customer_trx ct,
               
               ra_customer_trx_lines ctl
        
         where ctlgd.customer_trx_line_id = ctl.customer_trx_line_id(+)
              
           and nvl(ctlgd.org_id, -99) = nvl(ctl.org_id(+), -99)
              
           and ctlgd.account_set_flag = 'N'
              
           and ct.customer_trx_id = ctlgd.customer_trx_id
              
           and nvl(ct.org_id, -99) = nvl(ctlgd.org_id, -99)
              
           and ctlgd.cust_trx_line_gl_dist_id = to_number(r.reference_3)
              
           and r.reference_10 = 'RA_CUST_TRX_LINE_GL_DIST'
              
           and r.je_header_id = jeh.je_header_id
              
           and jeh.je_category in ('Sales Invoices', 'Credit Memos',
               
                'Debit Memos', 'Chargebacks')) xag,
       
       ra_customer_trx ct,
       
       ra_customer_trx_lines ctl,
       
       gl_code_combinations_kfv gcc
 where gjh.je_source = 'Receivables' --应收款管理系统
      
   and gjl.je_header_id = gjh.je_header_id
      
   and gjh.status = 'P' --已过帐
      
   and gjl.code_combination_id = gcc.code_combination_id
      
   and gcc.segment1 = '330007' --公司段
      
   and gcc.segment4 = '600101' --科目段：主营业务收入-内部主营业务收入
      
   and xag.application_id = 222
      
   and xag.je_header_id = gjl.je_header_id
      
   and xag.je_line_num = gjl.je_line_num
      
   and xag.trx_number_displayed = ct.trx_number
      
   and xag.trx_line_number = ctl.line_number
      
   and ct.customer_trx_id = ctl.customer_trx_id(+)
      
   and ctl.line_type(+) = 'LINE'
      
   and gjh.period_name = '2008-05'