1 SELECT GCC.SEGMENT3,
 2        fnd_flex_ext.get_segs(‘SQLGL‘, ‘GL#‘, 50348, gcc.code_combination_id) acct,
 3        gl_flexfields_pkg.Get_Concat_Description(50348,
 4                                                 gcc.code_combination_id) acct_desc,
 5        AL.accounted_dr,
 6        AL.accounted_cr
 7 
 8   FROM AP_ACCOUNTING_EVENTS_ALL AE,
 9        AP_AE_HEADERS_ALL        AH,
10        AP_AE_LINES_ALL          AL,
11        GL_CODE_COMBINATIONS     GCC
12  WHERE AE.SOURCE_ID = &INVOICE_ID --发票ID
13    AND AE.SOURCE_TABLE = ‘AP_INVOICES‘
14    AND AE.ACCOUNTING_EVENT_ID = AH.ACCOUNTING_EVENT_ID
15    AND AH.AE_HEADER_ID = AL.AE_HEADER_ID
16    AND AL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID;

 1 --发票产生的分录信息
 2  SELECT gcc.segment3,
 3         fnd_flex_ext.get_segs(‘SQLGL‘,
 4                               ‘GL#‘,
 5                               50348,
 6                               gcc.code_combination_id) acct,
 7         gl_flexfields_pkg.Get_Concat_Description(50348,
 8                                                  gcc.code_combination_id) acct_desc,
 9         l.accounted_dr,
10         l.accounted_cr
11    FROM xla.xla_transaction_entities te,
12         xla_events                   e,
13         xla_ae_headers               h,
14         xla_ae_lines                 l,
15         gl_code_combinations         gcc
16   WHERE te.entity_code = ‘AP_INVOICES‘
17     AND te.application_id = 200
18     AND nvl(te.source_id_int_1, (-99)) = &invoice_id --Invoice_id
19     AND te.entity_id = h.entity_id
20     AND te.application_id = h.application_id
21     AND e.event_id = h.event_id
22     AND e.application_id = h.application_id
23     AND h.ae_header_id = l.ae_header_id
24     AND h.application_id = l.application_id
25     AND l.code_combination_id = gcc.code_combination_id;

     其它差异

1. R12新增加ap_invoice_lines表；

官方解释：

Oracle Payables introduces invoice lines as an entity between the invoice header and

invoice distributions. With the new model, the invoice header remains unchanged, and　continues to store information about the supplier who sent the invoice, the invoice　attributes, and remittance information.Invoice lines represent the goods (direct or indirect materials), service(s), and/orassociated tax/freight/miscellaneous charges invoiced. Invoice distributions store theaccounting, allocation and other detail information that makes up the invoice line. Thecharge allocation table used in prior releases to manage accounting allocations is　obsolete.During the upgrade, Oracle Payables creates invoice lines for all existing invoices,creating one line for every distribution available in the Release 11i distributions table,except in the case of reversal pairs. In those cases, Payables creates one line with a zero　amount.

1. 供应商银行信息表变化

1. 付款银行表变化

 取付款银行信息sql(R12):

 1 --付款银行信息
 2 SELECT AC.CHECK_NUMBER,
 3        CBA.BANK_ACCOUNT_ID BANK_ACCOUNT_ID,
 4        AC.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,
 5        AC.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,
 6        CBA.BANK_ACCOUNT_NAME,
 7        CBB.BANK_BRANCH_NAME
 8   FROM AP_CHECKS_ALL         AC,
 9        CE_BANK_ACCOUNTS      CBA,
10        CE_BANK_ACCT_USES_ALL CBAU,
11        CE_BANK_BRANCHES_V    CBB
12  WHERE AC.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID(+)
13    AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)
14    AND CBB.BRANCH_PARTY_ID(+) = CBA.BANK_BRANCH_ID;

          4.供应商表变化

            5.Payment Documents 表变化

             取Payment Documents SQL（R12）:

 1 Select cs.payment_document_name,
 2        DECODE(cs.MANUAL_PAYMENTS_ONLY_FLAG,
 3               ‘Y‘,
 4               ‘RECORDED‘,
 5               ‘N‘,
 6               ‘COMBINED‘)
 7   From ap_checks_all ch, CE_PAYMENT_DOCUMENTS CS, ap_lookup_codes lk
 8  Where CH.PAYMENT_DOCUMENT_ID = cs.PAYMENT_DOCUMENT_ID(+)
 9    AND DECODE(cs.MANUAL_PAYMENTS_ONLY_FLAG,
10               ‘Y‘,
11               ‘RECORDED‘,
12               ‘N‘,
13               ‘COMBINED‘) = lk.lookup_code(+)
14    AND lk.lookup_type(+) = ‘DISBURSEMENT TYPE‘;

             6.Payment Batch

表变化:

  批付款操作界面变化:

     11i:

     7.税

　　　　R11版本按照设置税码税率计算税；在R12版以后，在计税方面有了重大改变，税也成为一个独立的模块。其计税的逻辑也从以前单纯的税码和税率的关联转换为通过税则，来建                          立会计分类代码（fiscal classification code）与税率的联系。会计分类代码可以应用至交易方（Party）、产品（Product）以及事务处理（Transaction）之上。 具体介绍请参                     照<<TAX专题>>

　　　　　　　　　官方解释：

　　　　　　　　　　Oracle E-Business Tax manages tax across the E-Business Suite. In prior releases, the

                        setup, defaulting, and calculation of tax for Payables was managed within Payables

                        using tax codes, their associated rates, and a hierarchy of defaulting options. This method is still available in this release. During the upgrade, E-Business Tax migrates  the tax codes as appropriate within E-Business Tax so that your tax processing can work　the same way after the upgrade as it did before.New fields are added to the supplier, invoice, and invoice lines entities to track tax　attributes used by E-Business Tax. Many of these attributes were implemented withglobal descriptive flexfields in prior releases and are upgraded to regular fields on these　entities.

表变化:

          PO税查询sql:

 1 select zl.trx_id,
 2        zl.trx_line_id,
 3        zl.tax_regime_code,
 4        zl.tax,
 5        zl.tax_status_code,
 6        zl.tax_rate_code,
 7        zl.tax_rate
 8   from po_line_locations_all pll, zx_lines zl
 9  where pll.po_header_id = zl.trx_id
10    and pll.line_location_id = zl.trx_line_id
11    and zl.application_id(+) = 201
12    AND zl.trx_level_type(+) = ‘SHIPMENT‘;

AP Invoices应付发票【Open Interface】

　　　以R12为例，具体介绍应付发票导入，R12导入应付发票较11i有部分不同，比如:

           11i,ap_invoice_lines_interface弹性域写入发票分配行，r12默认写入发票行，可能过profile设定，把行上的弹性域带到分配行上，profile:AP_COPY Invoice Lines  Flex fied 设为Y;

               发票税导入：

　　　　   11i通过税码，税率产生税，R12按照发票行上的税信息产生税，具体的规则见下表：

             ap_invoices_interface

    ap_invoice_lines_interface

/*-- 本例以PO匹配发票为例,请注意导入税的取值*/
DECLARE

  LN_INVOICE_ID NUMBER;

BEGIN
  
  LN_INVOICE_ID := AP.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL;
  --Header
  INSERT INTO AP_INVOICES_INTERFACE
    (INVOICE_ID,
     INVOICE_NUM,
     INVOICE_TYPE_LOOKUP_CODE,
     INVOICE_DATE,
     VENDOR_SITE_ID,
     INVOICE_AMOUNT,
     INVOICE_CURRENCY_CODE,
     EXCHANGE_DATE,
     TERMS_ID,
     SOURCE,
     GROUP_ID,
     DESCRIPTION,
     PAYMENT_METHOD_LOOKUP_CODE,
     ORG_ID,
     PAYMENT_CURRENCY_CODE,
     VENDOR_ID,
     GOODS_RECEIVED_DATE,
     INVOICE_RECEIVED_DATE,
     PAYMENT_CROSS_RATE_DATE,
     GL_DATE,
     LAST_UPDATE_DATE
     
     )
    SELECT LN_INVOICE_ID,
           T.INVOICE_NUM||‘-‘||TO_CHAR(SYSDATE,‘YYYY/MM/DD‘),
           T.INVOICE_TYPE_LOOKUP_CODE,
           T.INVOICE_DATE,
           T.VENDOR_SITE_ID,
           T.INVOICE_AMOUNT,
           T.INVOICE_CURRENCY_CODE,
           T.EXCHANGE_DATE,
           T.TERMS_ID,
           T.SOURCE,
           10000 GROUP_ID,
           T.DESCRIPTION,
           T.PAYMENT_METHOD_LOOKUP_CODE,
           T.ORG_ID,
           T.PAYMENT_CURRENCY_CODE,
           T.VENDOR_ID,
           T.GOODS_RECEIVED_DATE,
           T.INVOICE_RECEIVED_DATE,
           T.PAYMENT_CROSS_RATE_DATE,
           T.GL_DATE,
           SYSDATE
      FROM AP_INVOICES_ALL T
     WHERE T.INVOICE_ID = 12261;
  
  -- LINES
  INSERT INTO AP_INVOICE_LINES_INTERFACE
    (INVOICE_ID,
     INVOICE_LINE_ID,
     LINE_TYPE_LOOKUP_CODE,
     DESCRIPTION,
     AMOUNT,
     PO_HEADER_ID,
     PO_LINE_ID,
     PO_LINE_LOCATION_ID,
     PO_DISTRIBUTION_ID,
     ASSETS_TRACKING_FLAG,
     QUANTITY_INVOICED,
     UNIT_PRICE,
     ORG_ID,
     PA_ADDITION_FLAG,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     LINE_NUMBER,
     LINE_GROUP_NUMBER
     )
  
    SELECT LN_INVOICE_ID,
           AP.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL INVOICE_LINE_ID,
           T.LINE_TYPE_LOOKUP_CODE,
           T.DESCRIPTION,
           T.AMOUNT,
           T.PO_HEADER_ID,
           T.PO_LINE_ID,
           T.PO_LINE_LOCATION_ID,
           T.PO_DISTRIBUTION_ID,
           T.ASSETS_TRACKING_FLAG,
           T.QUANTITY_INVOICED,
           T.UNIT_PRICE,
           T.ORG_ID,
           ‘N‘ PA_ADDITION_FLAG,
           T.LAST_UPDATE_DATE,
           SYSDATE,
           T.LINE_NUMBER,
           T.LINE_GROUP_NUMBER
           
      FROM AP_INVOICE_LINES_ALL T, AP_INVOICE_DISTRIBUTIONS_ALL T1
     WHERE T.INVOICE_ID = T1.INVOICE_ID
       AND T.LINE_NUMBER = T1.INVOICE_LINE_NUMBER
       AND T.LINE_TYPE_LOOKUP_CODE = ‘ITEM‘
       AND T.INVOICE_ID = 12261;

  
  -- TAX 
  INSERT INTO AP_INVOICE_LINES_INTERFACE
    (INVOICE_ID,
     INVOICE_LINE_ID,
     LINE_TYPE_LOOKUP_CODE,
     AMOUNT,
     ORG_ID,
     LAST_UPDATE_DATE,
     CREATION_DATE,
     PA_ADDITION_FLAG,
     LINE_NUMBER,
     LINE_GROUP_NUMBER,
     TAX_REGIME_CODE,
     TAX,
     TAX_RATE_CODE,
     TAX_STATUS_CODE,
     TAX_JURISDICTION_CODE,
     TAX_RATE,
     TAX_RATE_ID,
     PRORATE_ACROSS_FLAG
     
     )
  
    SELECT LN_INVOICE_ID,
           AP.AP_INVOICE_LINES_INTERFACE_S.NEXTVAL INVOICE_LINE_ID,
           LINE_TYPE_LOOKUP_CODE,
           AMOUNT,
           ORG_ID,
           SYSDATE,
           CREATION_DATE,
           ‘N‘ PA_ADDITION_FLAG,
           LINE_NUMBER,
           LINE_GROUP_NUMBER,
           TAX_REGIME_CODE,
           TAX,
           TAX_RATE_CODE,
           TAX_STATUS_CODE,
           TAX_JURISDICTION_CODE,
           TAX_RATE,
           TAX_RATE_ID,
           ‘Y‘ PRORATE_ACROSS_FLAG
           
      FROM AP_INVOICE_LINES_ALL T
     WHERE T.INVOICE_ID = 12261
       AND T.LINE_TYPE_LOOKUP_CODE = ‘TAX‘;
   COMMIT;
   EXCEPTION 
     WHEN OTHERS THEN 
       ROLLBACK;

END;

AP常用API

           使用API可在开发中得到便利，下面介绍AP模块几个常用的API:

           AP_INVOICES_PKG

           AP_INVOICES_INTERFACE_PKG

           AP_INVOICE_LINES_INTERFACE_PKG

           AP_IMPORT_INVOICES_PKG

           AP_IMPORT_VALIDATION_PKG

     R11

  R12

附 ：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是如何从业务模块数据取数的。