拆分日记账追溯视图-来源应收日记帐（销售发票）

--来源于应收日记帐类型为销售发票（标准视图：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 '