调整代码,OracleEBS代码库,德仔网

代码语言

代码分类

【OracleEBS】调整

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

--调整
SELECT hou.NAME org_name,
       rct.trx_number,
       rct.trx_date,
       hca.account_number customer,
       hpt.party_name customer_name,
       'ARD' source_table,
       ard.line_id source_id,
       ard.source_type,
       adj.code_combination_id,
       gcc_ori.code_combination_id orig_ccid,
       gcc_ori.concatenated_segments orig_acct,
       ard.amount_dr entered_dr,
       ard.amount_cr entered_cr,
       (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 adj.adjustment_type NOT IN ('REC') 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 adj.adjustment_type NOT IN ('REC') 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 ra_customer_trx_all      rct,
       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_adjustments_all       adj,
       ar_distributions_all     ard,
       gl_code_combinations_kfv gcc_ori
WHERE rct.org_id = hou.organization_id
   AND rct.bill_to_customer_id = hca.cust_account_id
   AND hca.party_id = hpt.party_id
   AND rct.bill_to_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 rct.customer_trx_id = adj.customer_trx_id
   AND ard.source_table = 'ADJ'
   AND ard.source_id = adj.adjustment_id
   AND ard.code_combination_id = gcc_ori.code_combination_id
   AND hpt.party_name = 'xxxxxx';

评论列表

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

试试其它关键字

　调整　

同语言下

可能有用的

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