收款历史代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】收款历史

作者:Dezai.CN / 发布于2013/1/7/ 405

--收款历史
SELECT hou.NAME org_name,
       acr.receipt_number,
       acr.receipt_date,
       hca.account_number customer,
       hpt.party_name customer_name,
       'CRH' source_table,
       crh.cash_receipt_history_id source_id, 
       --ard.source_type,
       crh.account_code_combination_id orig_ccid, --crh.bank_charge_account_ccid
       gcc_ori.concatenated_segments orig_acct,
       crh.amount,
       (SELECT gcc_new.code_combination_id
          FROM gl_code_combinations_kfv gcc_new
         WHERE gcc_new.segment1 = gcc_ori.segment1
           AND gcc_new.segment2 = gcc_ori.segment2
           AND gcc_new.segment3 = gcc_ori.segment3
           AND gcc_new.segment4 = (CASE WHEN crh.status IN ('REC', 'UNAPP') THEN gcc_rec.segment4 END)
           AND gcc_new.segment5 = gcc_ori.segment5
           AND gcc_new.chart_of_accounts_id = gcc_ori.chart_of_accounts_id) new_ccid,
       (CASE
         WHEN crh.status IN ('REC', 'UNAPP') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
          gcc_ori.segment5
       END) new_acct,
       gcc_rec.segment4 rec_acc,
       gcc_rev.segment4 rev_acc
  FROM ar_cash_receipts_all        acr,
       hr_operating_units          hou,
       hz_cust_accounts            hca,
       hz_parties                  hpt,
       hz_cust_site_uses_all       csu,
       gl_code_combinations_kfv    gcc_rec,
       gl_code_combinations_kfv    gcc_rev,
       ar_cash_receipt_history_all crh,
       gl_code_combinations_kfv    gcc_ori
WHERE acr.org_id = hou.organization_id
   AND acr.pay_from_customer = hca.cust_account_id
   AND hca.party_id = hpt.party_id
   AND acr.customer_site_use_id = csu.site_use_id
   AND csu.gl_id_rec = gcc_rec.code_combination_id(+)
   AND csu.gl_id_rev = gcc_rev.code_combination_id(+)
   AND acr.cash_receipt_id = crh.cash_receipt_id
   AND crh.account_code_combination_id = gcc_ori.code_combination_id
   AND hpt.party_name = 'xxxxxx'

评论列表

本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。

试试其它关键字

　收款历史　

同语言下

可能有用的

Dezai.CN贡献的其它代码(4037)