收款数据抓取

SELECT t.name             收款方法,
       t.receipt_number   收款编号,
       t.status           状态,
       t.receipt_date     收款日期,
       t.gl_date          gl日期,
       t.currency_code    币种,
       t.amount           收款额,
       t.unapplied_amount 未核销金额,
       t.party_name       客户,
       t.account_number   客户编号,
       --t.attribute2 OMS销售订单号,
       --t.attribute3 EBANK流水号,
       gcc.segment1 || '.' || gcc.segment2 || '.' || gcc.segment3 || '.' ||
       gcc.segment4 || '.' || gcc.segment5 || '.' || gcc.segment6 || '.' ||
       gcc.segment7 账户,
       xl.accounted_dr 借方,
       xl.accounted_cr 贷方,
       xl.accounting_class_code 会计分类
  FROM (SELECT arm.name, --收款方法,
               acr.receipt_number, --收款编号,
               acr.status, --状态,
               acr.receipt_date, --收款日期,
               crh_first_posted.gl_date, --GL日期,
               acr.currency_code, --币种,
               acr.amount, --收款额,
               SUM(arr.amount_applied) unapplied_amount, --未核销金额,
               hp.party_name, --客户,
               ca.account_number, --编号,
               ca.attribute2, --OMS销售订单号,
               ca.attribute3, --EBANK流水号,
               acr.cash_receipt_id
          FROM ar_cash_receipts_all           acr,
               ar_receipt_methods             arm,
               ar_cash_receipt_history_all    crh_first_posted,
               hz_cust_site_uses_all          su,
               hz_cust_acct_sites_all         cas,
               hz_cust_accounts               ca,
               hz_parties                     hp,
               ar_payment_schedules_all       ps,
               ar_receivable_applications_all arr
         WHERE acr.receipt_method_id = arm.receipt_method_id
           AND acr.cash_receipt_id = crh_first_posted.cash_receipt_id
           AND acr.customer_site_use_id = su.site_use_id
           AND su.cust_acct_site_id = cas.cust_acct_site_id
           AND cas.cust_account_id = ca.cust_account_id
           AND ca.party_id = hp.party_id
           AND ps.payment_schedule_id = arr.payment_schedule_id
           AND arr.cash_receipt_id = acr.cash_receipt_id
           AND arr.status = 'UNAPP'
         GROUP BY arm.name, --收款方法
                  acr.receipt_number, --收款编号
                  acr.status, --状态
                  acr.receipt_date, --收款日期
                  crh_first_posted.gl_date, --GL日期
                  acr.currency_code, --币种
                  acr.amount, --收款额
                  hp.party_name, --客户
                  ca.account_number, --编号
                  ca.attribute2, --OMS销售订单号
                  ca.attribute3, --EBANK流水号
                  acr.cash_receipt_id) t,
       xla.xla_ae_headers xh,
       xla.xla_ae_lines xl,
       xla.xla_transaction_entities xte,
       gl.gl_code_combinations gcc,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_batches gjb,
       xla.xla_events xea
 WHERE xh.ae_header_id = xl.ae_header_id
   AND xte.entity_id = xh.entity_id
   AND t.cash_receipt_id = xte.source_id_int_1(+)
   AND gir.gl_sl_link_id(+) = xl.gl_sl_link_id
   AND gir.je_header_id = gjh.je_header_id(+)
   AND xl.code_combination_id = gcc.code_combination_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND xea.event_id = xte.entity_id
   AND xte.entity_code = 'RECEIPTS'
   AND t.gl_date > to_date('2014-01-01', 'YYYY-MM-DD')
   AND t.gl_date < to_date('2014-02-01', 'YYYY-MM-DD')
 ORDER BY t.receipt_number;