事务处理及分录代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】事务处理及分录
作者:Dezai.CN / 发布于2013/1/7/ 1297
--事务处理及分录
SELECT hou.NAME org_name,
       rct.trx_number,
       rct.trx_date,
       hca.account_number customer,
       hpt.party_name customer_name,
       'LGD' source_table,
       lgd.cust_trx_line_gl_dist_id source_id, 
       lgd.account_class acct_class, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
       gcc_ori.code_combination_id orig_ccid,
       gcc_ori.concatenated_segments orig_acct,
       lgd.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 lgd.account_class IN ('REC') THEN gcc_rec.segment4 WHEN
                lgd.account_class IN ('REV') THEN gcc_rev.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 lgd.account_class IN ('REC') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
          gcc_ori.segment5
         WHEN lgd.account_class IN ('REV') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rev.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,
       ra_cust_trx_line_gl_dist_all lgd,
       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 = lgd.customer_trx_id
      --AND lgd.account_set_flag = 'N'
   AND lgd.code_combination_id = gcc_ori.code_combination_id
   AND hpt.party_name = 'xxxxxx'

 

--调整
SELECT hou.NAME org_name,
       rct.trx_number,
       rct.trx_date,
       hca.account_number customer,
       hpt.party_name customer_name,
       'ADJ' source_table,
       adj.adjustment_id source_id, 
       adj.account_class acct_class, --ar_lookups.lookup_type = 'AUTOGL_TYPE'
       gcc_ori.code_combination_id orig_ccid,
       gcc_ori.concatenated_segments orig_acct,
       adj.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 lgd.account_class IN ('REC') THEN gcc_rec.segment4 WHEN
                lgd.account_class IN ('REV') THEN gcc_rev.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 lgd.account_class IN ('REC') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rec.segment4 || '.' ||
          gcc_ori.segment5
         WHEN lgd.account_class IN ('REV') THEN
          gcc_ori.segment1 || '.' || gcc_ori.segment2 || '.' || gcc_ori.segment3 || '.' || gcc_rev.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,
       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 adj.code_combination_id = gcc_ori.code_combination_id
   AND hpt.party_name = 'xxxxxx';
试试其它关键字
　事务处理及分录　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)