SQL语句 - 应收事务处理查询

应收事务处理(包含会计分录信息)

SELECT hou.name                 业务实体,
       ct.trx_number            ar发票编号,
       ct.trx_date              发票日期,
       gd.gl_date               gl日期,
       hp.party_name            客户名称,
       ct.invoice_currency_code 币种,
       ct.exchange_rate         汇率,
       ct.exchange_date         汇率日期,
       gdc.user_conversion_type 汇率类型,
       al.entered_dr            原币借项,
       al.entered_cr            原币贷项,
       al.accounted_dr          本位币借项,
       al.accounted_cr          本位币贷项,
       gh.name                  日记账名称,
       gj.name                  日记账批名
  FROM xla_ae_headers               ah,
       xla_ae_lines                 al,
       xla.xla_events               ae,
       xla.xla_transaction_entities te,
       ra_customer_trx_all          ct,
       ra_cust_trx_line_gl_dist_all gd,
       gl_import_references         gir,
       hz_cust_accounts             hca,
       hr_operating_units           hou,
       gl_je_headers                gh,
       gl_je_batches                gj,
       hz_parties                   hp,
       gl_daily_conversion_types    gdc
 WHERE ae.event_id = ah.event_id
   AND ae.application_id = ah.application_id
   AND ah.ae_header_id = al.ae_header_id
   AND ah.application_id = al.application_id
   AND ae.entity_id = te.entity_id
   AND te.source_id_int_1 = ct.customer_trx_id
   AND ct.customer_trx_id = gd.customer_trx_id
   AND 'REC' = gd.account_class
   AND 'Y' = gd.latest_rec_flag
   AND gir.gl_sl_link_id(+) = al.gl_sl_link_id
   AND gir.gl_sl_link_table(+) = al.gl_sl_link_table
   AND hca.cust_account_id(+) = ct.bill_to_customer_id
   AND ae.application_id = 222
   AND te.entity_code = 'TRANSACTIONS'
   AND hou.organization_id = ct.org_id
   AND gh.je_header_id(+) = gir.je_header_id
   AND gj.je_batch_id(+) = gh.je_batch_id
   AND hp.party_id(+) = hca.party_id
   AND gdc.conversion_type(+) = ct.exchange_rate_type


应收事务处理(头信息)

SELECT distinct hou.name            业务实体,
       ct.trx_number                       ar发票编号,
       gd.gl_date                           GL日期,
       hp.party_name                   客户名称,
       ct.ATTRIBUTE1,
       bs.name                            来源
  FROM ra_customer_trx_all       ct,
       hz_cust_accounts          hca,
       hr_operating_units        hou,
       hz_parties                hp,
       RA_BATCH_SOURCES_ALL      bs,
        ra_cust_trx_line_gl_dist_all gd
 WHERE hca.cust_account_id(+) = ct.bill_to_customer_id
   AND hou.organization_id = ct.org_id
   AND hp.party_id(+) = hca.party_id
   AND bs.BATCH_SOURCE_ID = ct.BATCH_SOURCE_ID
   AND ct.customer_trx_id = gd.customer_trx_id

本文标题:SQL语句 - 应收事务处理查询

本文链接:http://catonisland.cn/post/239.html

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章