R12_专题知识总结提炼-AR模块

Posted on 2014-06-26 12:41 微笑的眼泪 阅读(...) 评论(...) 编辑 收藏

应收模块简介

应收模块是用来为企业提供应收款管理的模块。

当企业销售一笔商品或者发生其他影响收入和现金的业务的时候,需要在应收模块记账。

本文档以R12为例,11i可参考,只针对简单业务情况考虑,将应收可能产生的业务流程和相应会计分录进行整理,供参考。对于一些财务类报表,如三栏明细账等会有所帮助。

 

R12版本的应收模块可以从三方面取到会计分录:

1)         应收业务,例如应收发票的分配明细、应收收款的核销记录、收款历史等等,通过这里取得的数据是最为明细的。

2)         子模块帐,R12新增的特性,将各模块产生会计分录的逻辑集中到xla模块进行处理。在创建会计分录时会产生子模块的帐。可以关联到发票或收款编号,但是无法具体到发票分配行等特别明细的记录。

3)         总账,子模块传至总账的数据,无法具体到具体的业务,如需追溯只能通过gl_import_reference表来关联xla的表,进而追溯到发票和收款。

不管是应收业务还是子模块帐,各个帐户的期间发生额是可以与总账对账的。

应收发票

不管是OM导入应收发票还是手工录入应收发票,对企业来讲,大多数应收业务都是表示企业产生一笔应收款项,同时增加一笔收入。记账如下:

DR  应收款项-销售商品   XXX  表示企业销售商品而产生一笔应收款尚未收到
CR  销项税                         XXX 
CR  业务收入-销售商品   XXX 表示企业因为销售商品当期增加了一笔收入

1)         录入应收发票;完成完成以后,我们可以通过sql查找到应收发票的分录信息(发票分配)。此为具体到发票行的分配信息。参考ar_trx_001.sql。

SELECT ct.trx_number
      ,ctl.description
      ,fnd_flex_ext.get_segs('SQLGL'
                            ,'GL#'
                            ,gcc.chart_of_accounts_id
                            ,gcc.code_combination_id) account_number
      ,gd.gl_date
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0))
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL))) entered_dr
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL)
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0)))) entered_cr
  FROM ra_customer_trx_all          ct
      ,ra_customer_trx_lines_all    ctl
      ,ra_cust_trx_line_gl_dist_all gd
      ,gl_code_combinations         gcc
 WHERE gd.customer_trx_id = ct.customer_trx_id
   AND gd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND gcc.code_combination_id = gd.code_combination_id
   AND ct.customer_trx_id = &customer_trx_id;
View Code

2)         创建会计分录;第二步我们可以在应收模块创建会计分录,R12中,此时将汇总业务模块的分录(发票分配)而产生应收子模块的分录。取数参考ar_trx_xla_001.sql(含发票分录和贷项通知单核销)。

SELECT ct.trx_number
      ,l.accounting_class_code
      ,l.entered_dr
      ,l.entered_cr
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) account_number
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) account_description
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ra_customer_trx_all          ct
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.entity_code = 'TRANSACTIONS'
   AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
   AND ct.customer_trx_id = 3139;
View Code

3)         传送子模块分录至总账;如果第二步没有直接过账至总账,可以提交请求:将日记帐分录传送至 GL。

此时总账将生成来源为je_source=’Receivables’ and je_category=’Sales Invoices’、‘Debit Memos’或’Credit Memos‘的日记账分录。

贷项通知单和借项通知单

1)         借项通知单:

应收的借项通知单是用来增加应收的、相当于独立的发票。不可以核销。

例如当我们对应收发票收款以后发现收款有误,此时我们可以通过用借项通知单冲销收款的方式来实现。相当于重新通过借项通知单来向客户收款。

借项通知单分录与发票类似,增加应收款项、增加当期收入(参考ar_trx_001.sql及ar_trx_xla_001.sql):

DR 应收   XXX
CR
收入   XXX

2)         贷项通知单:

应收的贷项通知单是用来减少应收、冲原始发票的,常用来做销售退货业务。可以用来核销原始发票。

贷项通知单分录,正好冲减因为销售发票增加的应收和收入(参考ar_trx_001.sql及ar_trx_xla_001.sql):

DR 收入   XXX
CR
应收  XXX

3)         贷项通知单核销

DR  贷项通知单的应收
CR 
被核销发票的应收

可以通过sql查到核销的分录(分配信息)。参考ar_cmapp_001.sql。这里核销的时候oracle按照核销的总金额按比例分摊到被核销发票的每一行上,可以通过ar_distributions_all进行追溯。

SELECT cm.trx_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,ra.gl_date
      ,ct.trx_number applied_trx_number
      ,ad.acctd_amount_dr
      ,ad.acctd_amount_cr
  FROM ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_payment_schedules_all       ps
      ,ra_customer_trx_all            cm
      ,ra_customer_trx_all            ct
 WHERE ad.source_table = 'RA'
   AND ad.source_type = 'REC'
   AND ad.source_id = ra.receivable_application_id
   AND ra.customer_trx_id = cm.customer_trx_id
      --
   AND ra.applied_payment_schedule_id = ps.payment_schedule_id
   AND ps.customer_trx_id = ct.customer_trx_id(+)
   AND ra.customer_trx_id = 51671
View Code

1)         创建会计分录后,R12可以从xla查询到子模块的看会计分录。参考附件ar_trx_xla_001.sql。

SELECT ct.trx_number
      ,l.accounting_class_code
      ,l.entered_dr
      ,l.entered_cr
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) account_number
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) account_description
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ra_customer_trx_all          ct
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.entity_code = 'TRANSACTIONS'
   AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
   AND ct.customer_trx_id = 3139;
View Code

2)         传送子模块分录至总账;如果第二步没有直接过账至总账,可以提交请求:将日记帐分录传送至 GL。

        此时总账将生成来源为je_source=’Receivables’ and je_category=’Sales Invoices’、‘Debit Memos’或’Credit Memos‘的日记账分录。

贷项通知单和借项通知单

1)         借项通知单:

应收的借项通知单是用来增加应收的、相当于独立的发票。不可以核销。

例如当我们对应收发票收款以后发现收款有误,此时我们可以通过用借项通知单冲销收款的方式来实现。相当于重新通过借项通知单来向客户收款。

借项通知单分录与发票类似,增加应收款项、增加当期收入(参考ar_trx_001.sql及ar_trx_xla_001.sql):

DR 应收   XXX
CR
收入   XXX

2)         贷项通知单:

应收的贷项通知单是用来减少应收、冲原始发票的,常用来做销售退货业务。可以用来核销原始发票。

贷项通知单分录,正好冲减因为销售发票增加的应收和收入(参考ar_trx_001.sql及ar_trx_xla_001.sql):

DR 收入   XXX
CR
应收  XXX

3)         贷项通知单核销

DR  贷项通知单的应收
CR 
被核销发票的应收

可以通过sql查到核销的分录(分配信息)。参考ar_cmapp_001.sql。这里核销的时候oracle按照核销的总金额按比例分摊到被核销发票的每一行上,可以通过ar_distributions_all进行追溯。

SELECT cm.trx_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,ra.gl_date
      ,ct.trx_number applied_trx_number
      ,ad.acctd_amount_dr
      ,ad.acctd_amount_cr
  FROM ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_payment_schedules_all       ps
      ,ra_customer_trx_all            cm
      ,ra_customer_trx_all            ct
 WHERE ad.source_table = 'RA'
   AND ad.source_type = 'REC'
   AND ad.source_id = ra.receivable_application_id
   AND ra.customer_trx_id = cm.customer_trx_id
      --
   AND ra.applied_payment_schedule_id = ps.payment_schedule_id
   AND ps.customer_trx_id = ct.customer_trx_id(+)
   AND ra.customer_trx_id = 51671
View Code

1)         创建会计分录后,R12可以从xla查询到子模块的看会计分录。参考附件ar_trx_xla_001.sql。

2)         传至总账后将生成来源为je_source=’Receivables’ and je_category=’Credit Memos‘的日记账分录。、

应收发票调整

应收发票录入完成后可以通过菜单项:活动->调整进入应收发票调整界面。

主要用来税调整,坏账处理之类的动作。来相应增加减少应收款项。

如因为客户破产而无法全部追回应收款项则可以如下调整来减少应收:

DR 坏账
CR 
应收款项

1)         录入调整后,可以通过sql查找到调整的业务模块的分录(分配信息)。参考ar_adj_001.sql。

SELECT ct.trx_number
      ,adj.adjustment_number
      ,ad.amount_dr
      ,ad.amount_cr
      ,ad.source_table
      ,ad.source_type
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,ad.code_combination_id)
  FROM ar_distributions_all ad
      ,ar_adjustments_all   adj
      ,ra_customer_trx_all  ct
 WHERE ad.source_table = 'ADJ'
   AND ad.source_id = adj.adjustment_id
   AND adj.customer_trx_id = ct.customer_trx_id
   AND ct.customer_trx_id = 3958;
View Code

2)         同样,R12版本创建分录后可以查找到子模块的分录信息。参考附件ar_adj_xla_001.sql。

SELECT ct.trx_number
      ,l.accounting_class_code
      ,l.entered_dr
      ,l.entered_cr
      ,xla_oa_functions_pkg.get_ccid_description(50328,l.code_combination_id)
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ar_adjustments_all           adj
      ,ra_customer_trx_all          ct
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.ledger_id=2022
   AND te.entity_code = 'ADJUSTMENTS'
   AND nvl(te.source_id_int_1,(-99))=adj.adjustment_id
   AND adj.customer_trx_id = ct.customer_trx_id
   AND ct.customer_trx_id=3958;
View Code

3)         传至总账后将生成来源为je_source=’Receivables’ and je_category=’ Adjustment ‘的日记账分录。

  

收款和核销

1)         录入收款

收款录入表示企业收到客户现金(以银行存款为例,实际可能会有票据等其他收款方式),如果未核销表示尚未与具体的客户发票相关联。现金流量表需要在此做标识。此时的会计分录应为

DR  银行存款
CR  应收账款-未核销

这里的应收账款-未核销科目是一个中转科目。

2)         核销发票时的分录如下。

DR            应收款-未核销
CR             应收账款-发票

收款录入或者核销以后,可以通过sql查到收款的分录信息(相当于收款分配)。参考ar_rcpt_001.sql。

-- 收款核销,贷项通知单核销也是通过ar_receivable_applications_all表
SELECT cr.receipt_number
      ,ad.amount_dr
      ,ad.amount_cr
      ,ad.source_table
      ,ad.source_type
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,ad.code_combination_id)
      ,ad.creation_date
  FROM ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_cash_receipts_all           cr
 WHERE ad.source_table = 'RA'
   AND ad.source_id = ra.receivable_application_id
   AND ra.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
-- 收款记录
UNION ALL
SELECT cr.receipt_number
      ,ad.amount_dr
      ,ad.amount_cr
      ,ad.source_table
      ,ad.source_type
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,ad.code_combination_id)
      ,ad.creation_date
  FROM ar_distributions_all        ad
      ,ar_cash_receipt_history_all crh
      ,ar_cash_receipts_all        cr
 WHERE ad.source_table = 'CRH'
   AND ad.source_id = crh.cash_receipt_history_id
   AND crh.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
 ORDER BY creation_date;
View Code

3)         创建会计分录以后,R12版本则可以汇总生成xla的信息,也就是子模块的收款的会计分录。可以通过以下sql查到。

SELECT cr.receipt_number
      ,l.accounting_class_code
      ,l.entered_dr
      ,l.entered_cr
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id)
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ar_cash_receipts_all         cr
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.ledger_id = 2022
   AND te.entity_code = 'RECEIPTS'
   AND nvl(te.source_id_int_1, -99) = cr.cash_receipt_id
   AND nvl(te.source_id_int_1, -99) = &cash_receipt_id;
View Code

4)         传至总账后将生成来源为je_source=’Receivables’ and je_category=’ Receipts ‘的日记账分录。

杂项收款

杂项收款不核销,只相当于计一笔收到现金的账,一般常用于记录银行利息等影响现金类科目的业务。

以银行利息为例,杂项收款的分录如下:

DR   银行存款
CR   财务费用-利息收入

表示收到一笔银行利息收入存入银行存款。

1)    杂项收款录入后可以通过以下sql查找到分录信息(分配)。参考:ar_mcd_001.sql

SELECT cr.receipt_number,cr.cash_receipt_id
      ,ad.amount_dr
      ,ad.amount_cr
      ,ad.source_table
      ,ad.source_type
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,ad.code_combination_id)
      ,ad.creation_date
  FROM ar_distributions_all           ad
      ,ar_misc_cash_distributions_all mcd
      ,ar_cash_receipts_all           cr
 WHERE ad.source_table = 'MCD'
   AND ad.source_id = mcd.misc_cash_distribution_id
   AND mcd.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
View Code

3)    创建会计分录后,R12版本可以通过sql查询到子模块创建的分录。参考上面的sql:ar_rcpt_xla_001.sql

4)    传至总账后将生成来源为je_source=’Receivables’ and je_category=’ Misc Receipts ‘的日记账分录。

应收模块总账追溯

将以上各种应收业务类型对应的取会计分录的sql,union all在一起即是所有应收模块产生的会计分录。

R12

1)    通过发票分配等从业务角度取到的会计分录,参考如下sql

-- 应收发票、DM、CM
SELECT ct.trx_number
      ,fnd_flex_ext.get_segs('SQLGL'
                            ,'GL#'
                            ,gcc.chart_of_accounts_id
                            ,gcc.code_combination_id) account_number
      ,gd.gl_date
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0))
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL))) entered_dr
      ,to_number(decode(gd.account_class
                       ,'REC'
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,-nvl(gd.amount, 0)
                              ,NULL)
                       ,decode(sign(nvl(gd.amount, 0))
                              ,-1
                              ,NULL
                              ,nvl(gd.amount, 0)))) entered_cr
  FROM ra_customer_trx_all          ct
      ,ra_customer_trx_lines_all    ctl
      ,ra_cust_trx_line_gl_dist_all gd
      ,gl_code_combinations         gcc
 WHERE gd.customer_trx_id = ct.customer_trx_id
   AND gd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND gcc.code_combination_id = gd.code_combination_id
   UNION ALL
-- DM APP
SELECT cm.trx_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,ra.gl_date
      ,ad.acctd_amount_dr
      ,ad.acctd_amount_cr
  FROM ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_payment_schedules_all       ps
      ,ra_customer_trx_all            cm
      ,ra_customer_trx_all            ct
 WHERE ad.source_table = 'RA'
   AND ad.source_type = 'REC'
   AND ad.source_id = ra.receivable_application_id
   AND ra.customer_trx_id = cm.customer_trx_id
      --
   AND ra.applied_payment_schedule_id = ps.payment_schedule_id
   AND ps.customer_trx_id = ct.customer_trx_id(+)
   AND ra.customer_trx_id = 51671
   UNION ALL
-- Adjustment
SELECT ct.trx_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,adj.gl_date
      ,ad.amount_dr
      ,ad.amount_cr
  FROM ar_distributions_all ad
      ,ar_adjustments_all   adj
      ,ra_customer_trx_all  ct
 WHERE ad.source_table = 'ADJ'
   AND ad.source_id = adj.adjustment_id
   AND adj.customer_trx_id = ct.customer_trx_id
   AND ct.customer_trx_id = 3958
   UNION ALL
-- 收款核销
SELECT cr.receipt_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,ra.gl_date
      ,ad.amount_dr
      ,ad.amount_cr
  FROM ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_cash_receipts_all           cr
 WHERE ad.source_table = 'RA'
   AND ad.source_id = ra.receivable_application_id
   AND ra.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
-- 收款记录
UNION ALL
SELECT cr.receipt_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,crh.gl_date
      ,ad.amount_dr
      ,ad.amount_cr
  FROM ar_distributions_all        ad
      ,ar_cash_receipt_history_all crh
      ,ar_cash_receipts_all        cr
 WHERE ad.source_table = 'CRH'
   AND ad.source_id = crh.cash_receipt_history_id
   AND crh.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
-- 杂项收款
UNION ALL
SELECT cr.receipt_number
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ad.code_combination_id) account_number
      ,mcd.gl_date
      ,ad.amount_dr
      ,ad.amount_cr
  FROM ar_distributions_all           ad
      ,ar_misc_cash_distributions_all mcd
      ,ar_cash_receipts_all           cr
 WHERE ad.source_table = 'MCD'
   AND ad.source_id = mcd.misc_cash_distribution_id
   AND mcd.cash_receipt_id = cr.cash_receipt_id
   AND cr.cash_receipt_id = &cash_receipt_id
View Code

2)    通过xla取到的应收模块会计分录,参考如下sql

-- 发票,DM、CM、贷项通知单核销
SELECT ct.trx_number
      ,h.accounting_date gl_date
      ,l.entered_dr
      ,l.entered_cr
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) account_number
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) account_description
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ra_customer_trx_all          ct
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.entity_code = 'TRANSACTIONS'
   AND nvl(te.source_id_int_1, (-99)) = ct.customer_trx_id
   AND ct.customer_trx_id = 3139
UNION ALL
-- 收款、核销、杂项收款
SELECT cr.receipt_number
      ,h.accounting_date gl_date
      ,l.entered_dr
      ,l.entered_cr
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) account_number
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) account_description
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ar_cash_receipts_all         cr
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.ledger_id = 2022
   AND te.entity_code = 'RECEIPTS'
   AND nvl(te.source_id_int_1, -99) = cr.cash_receipt_id
   AND nvl(te.source_id_int_1, -99) = &cash_receipt_id
UNION ALL
-- ADJUSTMENTS
SELECT ct.trx_number
      ,h.accounting_date gl_date
      ,l.entered_dr
      ,l.entered_cr
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) account_number
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) account_description
  FROM xla_ae_headers               h
      ,xla_ae_lines                 l
      ,xla_events                   e
      ,xla.xla_transaction_entities te
      ,ar_adjustments_all           adj
      ,ra_customer_trx_all          ct
 WHERE h.application_id = l.application_id
   AND h.ae_header_id = l.ae_header_id
   AND h.application_id = e.application_id
   AND h.event_id = e.event_id
   AND h.application_id = te.application_id
   AND h.entity_id = te.entity_id
   AND te.application_id = 222
   AND te.ledger_id=2022
   AND te.entity_code = 'ADJUSTMENTS'
   AND nvl(te.source_id_int_1,(-99))=adj.adjustment_id
   AND adj.customer_trx_id = ct.customer_trx_id
   AND ct.customer_trx_id=3958;
View Code

3)    来自应收模块的总账日记账分录,参考如下sql

SELECT h.je_source
      ,h.je_category
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, l.code_combination_id) acct
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,l.code_combination_id) acct_descr
      ,l.entered_dr
      ,l.entered_cr
  FROM gl_je_headers        h
      ,gl_je_lines          l
      ,gl_code_combinations gcc
      ,gl_period_statuses   gps
 WHERE h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_source = 'Receivables'
   AND l.period_name = gps.period_name
   AND gps.application_id = 101
   AND gps.set_of_books_id = 2022
   AND h.actual_flag = 'A'
   AND h.period_name = '2009-01';
View Code

R12下总账追溯子模块,通过gl_import_reference表,如下sql:

SELECT h.je_source
      ,h.je_category
      ,fnd_flex_ext.get_segs('SQLGL', 'GL#', 50328, ael.code_combination_id) acct
      ,xla_oa_functions_pkg.get_ccid_description(50328
                                                ,ael.code_combination_id) acct_descr
      ,ael.entered_dr
      ,ael.entered_cr
  FROM gl_je_headers        h
      ,gl_je_lines          l
      ,gl_code_combinations gcc
      ,gl_period_statuses   gps
      ,gl_import_references ir
      ,xla_ae_lines         ael
 WHERE h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_source <> 'Receivables'
   AND l.period_name = gps.period_name
   AND gps.application_id = 101
   AND gps.set_of_books_id = 2022
   AND h.actual_flag = 'A'
   AND h.period_name = '2009-01'
   AND ir.je_header_id = l.je_header_id
   AND ir.je_line_num = l.je_line_num
   AND ael.gl_sl_link_id = ir.gl_sl_link_id
   AND ael.gl_sl_link_table = ir.gl_sl_link_table;
View Code

11i

附上以前写的一个11i的总账追溯子模块的三栏明细账的sql:11i_gl_journal_drill.sql,11i下测试通过,可以参考。

CREATE OR REPLACE VIEW CUX_GL_JOURNALS_V
(set_of_books_id, org_id, je_source, je_category, gl_date, period_name, effective_period_num, je_name, je_doc_num, sub_doc_num, code_combination_id, segment1, segment2, segment3, segment4, segment5, segment6, segment7, segment8, segment9, descr, third_party_name, party_site, currency_code, entered_dr, entered_cr, accounted_dr, accounted_cr, balance)
AS
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.name je_name
      ,h.doc_sequence_value je_doc_num
      ,to_char(ael.subledger_doc_sequence_value) ap_doc_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,decode(ae.source_table,'AP_CHECKS',ac.checkrun_name,ai.DESCRIPTION) descr
      ,pv.vendor_name
      ,pvs.vendor_site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches        b
      ,gl_je_headers        h
      ,gl_je_lines          l
      ,gl_code_combinations gcc
      ,gl_je_categories_v   jc
      ,gl_je_sources_v      js
      ,gl_period_statuses   gps
       --
      ,gl_import_references     i
      ,ap_ae_headers_all        aeh
      ,ap_ae_lines_all          ael
      ,ap_accounting_events_all ae
       --
      ,ap_invoices_all ai
      ,ap_checks_all   ac
       --
      ,po_vendors          pv
      ,po_vendor_sites_all pvs
 WHERE 1 = 1
      -- gl journal
   AND h.je_header_id = l.je_header_id
   AND h.je_batch_id = b.je_batch_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
   AND h.je_source = 'Payables'
   AND h.actual_flag = 'A'
   AND b.status='P'
      -- gl to ap
   AND l.je_header_id = i.je_header_id
   AND l.je_line_num = i.je_line_num
   AND i.gl_sl_link_id = ael.gl_sl_link_id
      -- ap journals
   AND ael.ae_header_id = aeh.ae_header_id
   AND h.period_name = aeh.period_name
   AND h.set_of_books_id = aeh.set_of_books_id
   AND aeh.accounting_event_id = ae.accounting_event_id
      --
   AND ael.third_party_id = pv.vendor_id(+)
   AND ael.third_party_sub_id = pvs.vendor_site_id(+)
   AND decode(ae.source_table, 'AP_INVOICES', ae.source_id, NULL) = ai.invoice_id(+)
   AND decode(ae.source_table, 'AP_CHECKS', ae.source_id, NULL) = ac.check_id(+)
   --AND ai.invoice_num='bz20071220lmx001'
-- Part2: AR Trade Receipts
UNION ALL
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,to_char(cr.receipt_number) ar_doc_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,csu.location descr
      ,ac.customer_name
      ,csu.location site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches                  b
      ,gl_je_lines                    l
      ,gl_code_combinations           gcc
      ,gl_je_headers                  h
      ,gl_je_categories_v             jc
      ,gl_je_sources_v                js
      ,gl_period_statuses             gps
      ,gl_import_references           i
      ,ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ar_cash_receipt_history_all    crh
      ,ar_cash_receipts_all           cr
      ,ar_receipt_methods             rm
      ,gl_sets_of_books               sob
      ,ar_customers                   ac
      ,hz_cust_site_uses_all          csu
 WHERE 1 = 1
      -- gl journals
   AND h.je_source = 'Receivables'
   AND h.je_category = 'Trade Receipts'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND l.je_header_id = h.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_batch_id = b.je_batch_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND h.set_of_books_id = sob.set_of_books_id
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
      -- gl to ar rcpt
   AND l.je_header_id = i.je_header_id
   AND l.je_line_num = i.je_line_num
   AND i.reference_3 = ad.line_id
      -- ar rcpt journals
   AND decode(ad.source_table, 'RA', ad.source_id, NULL) = ra.receivable_application_id(+)
   AND decode(ad.source_table, 'CRH', ad.source_id, NULL) = crh.cash_receipt_history_id(+)
   AND decode(ad.source_table, 'RA', ra.cash_receipt_id, 'CRH', crh.cash_receipt_id, NULL) = cr.cash_receipt_id
       -- addional info
   AND cr.receipt_method_id = rm.receipt_method_id
   AND cr.pay_from_customer = ac.customer_id(+)
   AND cr.customer_site_use_id = csu.site_use_id(+)
   --AND cr.receipt_number = 'test_ar_rcpt001'
-- Part3: AR Transactions & Credit Memos
UNION ALL
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,to_char(ct.trx_number) ar_doc_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,loc.address1 descr
      ,ac.customer_name
      ,csu.location site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches        b
      ,gl_je_headers        h
      ,gl_je_lines          l
      ,gl_code_combinations gcc
      ,gl_je_categories_v   jc
      ,gl_je_sources_v      js
      ,gl_period_statuses   gps
      ,gl_import_references i
       -- ra trx
      ,ra_customer_trx_all          ct
      ,ra_customer_trx_lines_all    ctl
      ,ra_cust_trx_line_gl_dist_all gd
       --
      ,ar_customers          ac
      ,hz_cust_site_uses_all csu
      ,hz_cust_acct_sites_all    hcas
      ,hz_party_sites        hps
      ,hz_locations          loc
 WHERE 1 = 1
      -- gl journal
   AND b.je_batch_id = h.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.je_header_id = i.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
   AND l.je_line_num = i.je_line_num
      -- gl to ar
   AND h.je_source = 'Receivables'
   AND h.je_category IN ('Sales Invoices', 'Credit Memos')
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND i.reference_3 = gd.cust_trx_line_gl_dist_id
      -- ar journal
   AND gd.customer_trx_id = ct.customer_trx_id
   AND gd.customer_trx_line_id = ctl.customer_trx_line_id(+)
   AND ct.bill_to_customer_id = ac.customer_id
   AND ct.bill_to_site_use_id = csu.site_use_id
   and csu.cust_acct_site_id=hcas.cust_acct_site_id(+)
   and hcas.party_site_id = hps.party_site_id(+)
   and hps.location_id = loc.location_id(+)
   --AND ct.trx_number = 'test_artrx001'
UNION ALL
-- Part4: AR Credit Memos Applications
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,to_char(trx_cm.trx_number) ar_doc_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,loc.address1 descr
      ,rc.customer_name
      ,csu.location site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches                  b
      ,gl_je_lines                    l
      ,gl_code_combinations           gcc
      ,gl_je_headers                  h
      ,gl_period_statuses             gps
      ,gl_import_references           i
      ,ar_distributions_all           ad
      ,ar_receivable_applications_all ra
      ,ra_customer_trx_all            trx_cm
      ,ra_customer_trx_all            trx_inv
      ,gl_je_categories_v             jc
      ,gl_je_sources_v                js
      ,ra_customers                   rc
      ,hz_cust_site_uses_all          csu
      ,hz_cust_acct_sites_all         hcas
      ,hz_party_sites                 hps
      ,hz_locations                   loc
 WHERE 1 = 1
   AND h.je_source = 'Receivables'
   AND h.je_category = 'Credit Memo Applications'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND h.je_batch_id = b.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
      -- gl to ar
   AND l.je_header_id = i.je_header_id
   AND l.je_line_num = i.je_line_num
   AND to_number(i.reference_3) = ad.line_id
      -- ar
   AND ad.source_table = 'RA'
   AND ad.source_id = ra.receivable_application_id
   AND ra.customer_trx_id = trx_cm.customer_trx_id
   AND ra.applied_customer_trx_id = trx_inv.customer_trx_id
   AND trx_cm.bill_to_customer_id = rc.customer_id
   AND trx_cm.bill_to_site_use_id = csu.site_use_id
   AND csu.cust_acct_site_id=hcas.cust_acct_site_id(+)
   AND hcas.party_site_id = hps.party_site_id(+)
   AND hps.location_id = loc.location_id(+)
   --AND trx_cm.trx_number = 'test_artrx003'
UNION ALL
-- Part5: Po Rcv & Return
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,to_char(poh.segment1) po_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,msi.concatenated_segments descr
      ,pv.vendor_name
      ,pvs.vendor_site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches        b
      ,gl_je_headers        h
      ,gl_je_lines          l
      ,gl_code_combinations gcc
      ,gl_je_categories_v   jc
      ,gl_je_sources_v      js
      ,gl_period_statuses   gps
      ,gl_import_references r
       --
      ,po_headers_all           poh
      ,po_lines_all             pol
      ,po_releases_all          pr
      ,po_line_locations_all    pll
      ,po_distributions_all     pod
      ,po_vendors          pv
      ,po_vendor_sites_all pvs
      ,rcv_receiving_sub_ledger rrs
       --
      ,rcv_transactions             rct
      ,rcv_shipment_headers         rsh
      ,rcv_shipment_lines           rsl
      ,org_organization_definitions ood
      ,mtl_system_items_kfv         msi
 WHERE 1 = 1
      -- gl journals
   AND b.je_batch_id = h.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
   AND h.je_source = 'Purchasing'
   AND h.je_category  = 'Receiving'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND l.je_header_id = r.je_header_id
   AND l.je_line_num = r.je_line_num
      -- gl to po rcv
   AND r.gl_sl_link_table = 'RSL'
   AND rrs.gl_sl_link_id = r.gl_sl_link_id
   AND rrs.rcv_transaction_id = r.reference_5
      -- PO
   AND poh.po_header_id = pol.po_header_id
   AND pol.po_line_id = pll.po_line_id
   AND pll.po_release_id = pr.po_release_id(+)
   AND pll.line_location_id = pod.line_location_id
   AND poh.vendor_id = pv.vendor_id
   AND poh.vendor_site_id = pvs.vendor_site_id
      -- PO to Rcv
   AND pod.po_distribution_id = rrs.reference3
   AND rrs.rcv_transaction_id = rct.transaction_id
   AND rct.shipment_header_id = rsh.shipment_header_id
   AND rct.shipment_line_id = rsl.shipment_line_id
      --
   AND rct.organization_id = ood.organization_id
   AND ood.set_of_books_id = rrs.set_of_books_id
   AND rsl.item_id = msi.inventory_item_id
   AND rct.organization_id = msi.organization_id
   --AND poh.segment1 = '20010800157'
UNION ALL
-- Part6: INV WIP
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,msik.concatenated_segments item_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,wip_type.meaning descr
      ,NULL vendor_name
      ,NULL vendor_site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches      b
      ,gl_je_lines        l
      ,gl_code_combinations gcc
      ,gl_je_headers      h
      ,gl_je_categories_v jc
      ,gl_je_sources_v    js
      ,gl_period_statuses   gps
       --
      ,gl_import_references r
       --
      ,wip_transaction_accounts wta
      ,wip_transactions         wt
      ,mfg_lookups              wip_type
      ,wip_entities             we
      ,mtl_system_items_kfv     msik
 WHERE 1 = 1
      -- gl
   AND h.je_source = 'Inventory'
   AND h.je_category = 'WIP'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND b.je_batch_id = h.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
      -- gl to inv
   AND r.je_header_id = h.je_header_id
   AND r.je_line_num = l.je_line_num
   AND r.gl_sl_link_id IS NOT NULL
   AND r.gl_sl_link_id = wta.gl_sl_link_id
   AND r.reference_3 IS NOT NULL
   AND r.reference_1 = to_char(wta.gl_batch_id)
   AND r.reference_3 = wta.transaction_id
   AND l.code_combination_id = wta.reference_account
      -- wip
   AND wta.transaction_id = wt.transaction_id
   AND wt.wip_entity_id = we.wip_entity_id
   AND wt.organization_id = we.organization_id
   AND we.primary_item_id = msik.inventory_item_id(+)
   AND we.organization_id = msik.organization_id(+)
      --
   AND wip_type.lookup_type(+) = 'WIP_TRANSACTION_TYPE'
   AND wip_type.lookup_code(+) = wt.transaction_type
   --AND h.je_header_id = 38335
   --AND l.je_line_num = 3
-- Part7: INV MTL - mmt.transaction_id = mta.transaction_id
UNION ALL
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,msi.concatenated_segments item_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,mtst.transaction_source_type_name descr
      ,NULL vendor_name
      ,NULL vendor_site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches      b
      ,gl_je_lines        l
      ,gl_code_combinations gcc
      ,gl_je_headers      h
      ,gl_je_categories_v jc
      ,gl_je_sources_v    js
       --
      ,gl_import_references      r
      ,gl_period_statuses        gps
      ,gl_sets_of_books          sob
      ,gl_daily_conversion_types glct
      ,mtl_transaction_accounts  mta
      ,mtl_material_transactions mmt
      ,mtl_transaction_types     mtt
      ,mtl_item_locations        mil
      ,mtl_parameters            mp
      ,mtl_txn_source_types      mtst
      ,mtl_system_items_vl       msi
 WHERE 1 = 1
      -- gl
   AND h.je_source = 'Inventory'
   AND h.je_category = 'MTL'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND b.je_batch_id = h.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
      -- gl to inv
   AND r.je_header_id = h.je_header_id
   AND r.je_line_num = l.je_line_num
   AND l.set_of_books_id = sob.set_of_books_id
   AND r.reference_1 = mta.gl_batch_id
   AND ((r.gl_sl_link_id IS NOT NULL AND r.reference_3 IS NOT NULL AND
       mta.transaction_id = r.reference_3 AND
       mta.gl_sl_link_id = r.gl_sl_link_id) OR
       (r.gl_sl_link_id IS NULL AND r.reference_3 IS NULL AND
       mta.reference_account = l.code_combination_id AND
       nvl(mta.currency_code,
             sob.currency_code) = h.currency_code AND
       decode(mta.encumbrance_type_id,
                NULL,
                'A',
                'E') = h.actual_flag AND
       nvl(mta.encumbrance_type_id,
             -1) = nvl(h.encumbrance_type_id,
                         -1) AND
       nvl(mmt.ussgl_transaction_code,
             '#ZZZ') = nvl(h.ussgl_transaction_code,
                             '#ZZZ')))
      -- inv
   AND mmt.transaction_id = mta.transaction_id
   AND (mmt.transaction_action_id NOT IN (2, 3, 5) OR
       (mmt.transaction_action_id IN (2, 3, 5) AND
       mmt.primary_quantity < 0 AND
       mmt.primary_quantity = mta.primary_quantity))
   AND mmt.inventory_item_id = mta.inventory_item_id
   AND mmt.organization_id = mp.organization_id
   AND mmt.organization_id = mil.organization_id(+)
   AND mmt.locator_id = mil.inventory_location_id(+)
   AND mmt.currency_conversion_type = glct.conversion_type(+)
   AND mtt.transaction_type_id = mmt.transaction_type_id
   AND mtst.transaction_source_type_id = mmt.transaction_source_type_id
   AND sob.set_of_books_id = gps.set_of_books_id
   AND gps.period_name = l.period_name
   AND gps.application_id = 401
   AND msi.inventory_item_id = mmt.inventory_item_id
   AND msi.organization_id = mmt.organization_id
   --AND h.je_header_id=1665
UNION ALL
-- Part8: INV MTL Part2 - mmt.transfer_transaction_id = mta.transaction_id
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.NAME je_name
      ,h.doc_sequence_value je_doc_num
      ,msi.concatenated_segments item_num
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,mtst.transaction_source_type_name descr
      ,NULL vendor_name
      ,NULL vendor_site_code
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr, 0) - nvl(l.entered_cr, 0) balance
  FROM gl_je_batches             b
      ,gl_je_lines               l
      ,gl_code_combinations      gcc
      ,gl_je_headers             h
      ,gl_je_categories_v        jc
      ,gl_je_sources_v           js
      ,gl_import_references      r
      ,gl_period_statuses        gps
      ,gl_sets_of_books          sob
      ,gl_daily_conversion_types glct
      ,mtl_transaction_accounts  mta
      ,mtl_material_transactions mmt
      ,mtl_transaction_types     mtt
      ,mtl_item_locations        mil
      ,mtl_parameters            mp
      ,mtl_txn_source_types      mtst
      ,mtl_system_items_vl       msi
 WHERE 1 = 1
   AND h.je_source = 'Inventory'
   AND h.je_category = 'MTL'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND b.je_batch_id = h.je_batch_id
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND r.je_header_id = h.je_header_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND r.je_line_num = l.je_line_num
   AND l.set_of_books_id = sob.set_of_books_id
      -- gl to mtl
   AND r.reference_1 = mta.gl_batch_id
   AND ((r.gl_sl_link_id IS NOT NULL AND r.reference_3 IS NOT NULL AND
       mta.transaction_id = r.reference_3 AND
       mta.gl_sl_link_id = r.gl_sl_link_id) OR
       (r.gl_sl_link_id IS NULL AND r.reference_3 IS NULL AND
       mta.reference_account = l.code_combination_id AND
       nvl(mta.currency_code,
             sob.currency_code) = h.currency_code AND
       decode(mta.encumbrance_type_id,
                NULL,
                'A',
                'E') = h.actual_flag AND
       nvl(mta.encumbrance_type_id,
             -1) = nvl(h.encumbrance_type_id,
                         -1) AND
       nvl(mmt.ussgl_transaction_code,
             '#ZZZ') = nvl(h.ussgl_transaction_code,
                             '#ZZZ')))
      --mmt
   AND mmt.transfer_transaction_id = mta.transaction_id
   AND mmt.transaction_action_id IN (2, 3, 5)
   AND mmt.primary_quantity > 0
   AND mmt.primary_quantity = mta.primary_quantity
   AND mmt.inventory_item_id = mta.inventory_item_id
   AND mmt.organization_id = mp.organization_id
   AND mmt.organization_id = mil.organization_id(+)
   AND mmt.locator_id = mil.inventory_location_id(+)
   AND mmt.currency_conversion_type = glct.conversion_type(+)
   AND mtt.transaction_type_id = mmt.transaction_type_id
   AND mtst.transaction_source_type_id = mmt.transaction_source_type_id
   AND sob.set_of_books_id = gps.set_of_books_id
   AND gps.period_name = l.period_name
   AND gps.application_id = 401
   AND msi.inventory_item_id = mmt.inventory_item_id
   AND msi.organization_id = mmt.organization_id
   --AND h.je_header_id=1665
-- Part9: FA Journals
UNION ALL
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.name je_name
      ,h.doc_sequence_value je_doc_num
      ,fa.asset_number
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,fa.asset_description descr
      ,NULL
      ,NULL
      ,h.currency_code
      ,nvl(fa.entered_dr,l.entered_dr)entered_dr
      ,nvl(fa.entered_cr,l.entered_cr)entered_cr
      ,nvl(fa.accounted_dr,l.accounted_dr)accounted_dr
      ,nvl(fa.accounted_cr,l.accounted_cr)accounted_cr
      ,nvl(fa.entered_dr,0) - nvl(fa.entered_cr,0) balance
  FROM gl_je_batches      b
      ,gl_je_headers      h
      ,gl_je_lines        l
      ,gl_code_combinations gcc
      ,gl_period_statuses   gps
      ,gl_je_categories_v jc
      ,gl_je_sources_v    js
       --
      ,fa_ael_gl_v        fa
 WHERE 1=1
   AND h.je_source='Assets'
   AND h.je_category <> 'Depreciation'
   AND h.actual_flag = 'A'
   AND b.status='P'
   AND b.je_batch_id = h.je_batch_id
   AND l.code_combination_id = gcc.code_combination_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND h.je_header_id = l.je_header_id
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
   --
   AND l.je_header_id = fa.je_header_id(+)
   AND l.je_line_num = fa.je_line_num(+)
   --
   --AND l.je_header_id=481
   --AND l.je_line_num = 1
-- Part10: Other Journals
UNION ALL
SELECT h.set_of_books_id
      ,b.org_id
      ,js.user_je_source_name je_source
      ,jc.user_je_category_name je_category
      ,h.default_effective_date gl_date
      ,l.period_name
      ,gps.effective_period_num
      ,h.name je_name
      ,h.doc_sequence_value je_doc_num
      ,NULL
      ,l.code_combination_id
      ,gcc.segment1
      ,gcc.segment2
      ,gcc.segment3
      ,gcc.segment4
      ,gcc.segment5
      ,gcc.segment6
      ,gcc.segment7
      ,gcc.segment8
      ,gcc.segment9
      ,l.DESCRIPTION descr
      ,NULL
      ,NULL
      ,h.currency_code
      ,l.entered_dr
      ,l.entered_cr
      ,l.accounted_dr
      ,l.accounted_cr
      ,nvl(l.entered_dr,0) - nvl(l.entered_cr,0) balance
  FROM gl_je_batches      b
      ,gl_je_headers      h
      ,gl_je_lines        l
      ,gl_period_statuses   gps
      ,gl_code_combinations gcc
      ,gl_je_categories_v jc
      ,gl_je_sources_v    js
 WHERE b.je_batch_id = h.je_batch_id
   AND h.je_category = jc.je_category_name
   AND h.je_source = js.je_source_name
   AND h.je_header_id = l.je_header_id
   AND l.code_combination_id = gcc.code_combination_id
   AND gps.set_of_books_id = l.set_of_books_id
   AND gps.application_id = 101
   AND gps.period_name = l.period_name
   AND b.status='P'
   AND h.actual_flag = 'A'
   AND ( (h.je_source='Assets' AND h.je_category = 'Depreciation')
          OR
         (h.je_source NOT IN ('Assets','Payables','Receivables','Inventory','Purchasing'))
        )
View Code

附 :R12中子模块的帐是如何产生的。

1) 在FORM:ARXRWMAI 的按钮 SLACEXEC.OK_BP 触发器中有提交创建的代码:

arp_sla_submit.which_case;

2)包arp_sla_submit在pll文件ARSLAOLS.pll中。

3)在arp_sla_submit.which_case中执行以下过程,将FORM界面上的信息传入过程

       submit_xla_accounting

4) submit_xla_accounting过程调用以下数据库package创建会计科目

  XLA_ACCOUNTING_PUB_PKG.accounting_program_document

5)在XLA_ACCOUNTING_PUB_PKG中根据p_offline_flag的取值,分别调用

xla_accounting_pkg.accounting_program_document

或者调用创建会计科目的请求

6)在xla_accounting_pkg中调用下面过程

xla_accounting_pkg.accounting_program_events

7)在events_processor在调用handle_accounting_hook调根据application_id分别调用

  CASE                                                                                          

      WHEN p_application_id = 200 THEN                                                 

            xla_ap_acct_hooks_pkg.main    

      WHEN p_application_id = 222 THEN

            xla_ar_acct_hooks_pkg.main   

      WHEN p_application_id = 140 THEN                                                         

            xla_fa_acct_hooks_pkg.main

      WHEN p_application_id = 260 THEN                                                         

            xla_ce_acct_hooks_pkg.main

      WHEN p_application_id = 555 THEN                                                         

            xla_gmf_acct_hooks_pkg.main   

      WHEN p_application_id = 801 THEN                                                         

            xla_pay_acct_hooks_pkg.main     

      ELSE                                                                          

  END CASE;              

其中event_name为extract。然后调用arp_xla_extract_main_pkg.extract 来取子模块的分配。其主要作用是将来自子模块的分配。AR的发票、调整、收款、核销等子模块的帐的分配插入ar_xla_lines_extract临时表,在这个package中可以看到xla是如何从业务模块数据取数的。

posts - 45, comments - 1, trackbacks - 0, articles - 0

Copyright © 微笑的眼泪