SELECT GCC.SEGMENT3,

        fnd_flex_ext.get_segs('SQLGL', 'GL#', 50348, gcc.code_combination_id) acct,

        gl_flexfields_pkg.Get_Concat_Description(50348,

                                                 gcc.code_combination_id) acct_desc,

        AL.accounted_dr,

        AL.accounted_cr

   FROM AP_ACCOUNTING_EVENTS_ALL AE,

        AP_AE_HEADERS_ALL        AH,

        AP_AE_LINES_ALL          AL,

        GL_CODE_COMBINATIONS     GCC

  WHERE AE.SOURCE_ID = &INVOICE_ID --发票ID

    AND AE.SOURCE_TABLE = 'AP_INVOICES'

    AND AE.ACCOUNTING_EVENT_ID = AH.ACCOUNTING_EVENT_ID

    AND AH.AE_HEADER_ID = AL.AE_HEADER_ID

    AND AL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID;

 --发票产生的分录信息

  SELECT gcc.segment3,

         fnd_flex_ext.get_segs('SQLGL',

                               'GL#',

                               50348,

                               gcc.code_combination_id) acct,

         gl_flexfields_pkg.Get_Concat_Description(50348,

                                                  gcc.code_combination_id) acct_desc,

         l.accounted_dr,

         l.accounted_cr

    FROM xla.xla_transaction_entities te,

         xla_events                   e,

         xla_ae_headers               h,

         xla_ae_lines                 l,

         gl_code_combinations         gcc

   WHERE te.entity_code = 'AP_INVOICES'

     AND te.application_id = 200

     AND nvl(te.source_id_int_1, (-99)) = &invoice_id --Invoice_id

     AND te.entity_id = h.entity_id

     AND te.application_id = h.application_id

     AND e.event_id = h.event_id

     AND e.application_id = h.application_id

     AND h.ae_header_id = l.ae_header_id

     AND h.application_id = l.application_id

     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):

 --付款银行信息

 SELECT AC.CHECK_NUMBER,

        CBA.BANK_ACCOUNT_ID BANK_ACCOUNT_ID,

        AC.BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

        AC.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM,

        CBA.BANK_ACCOUNT_NAME,

        CBB.BANK_BRANCH_NAME

   FROM AP_CHECKS_ALL         AC,

        CE_BANK_ACCOUNTS      CBA,

        CE_BANK_ACCT_USES_ALL CBAU,

        CE_BANK_BRANCHES_V    CBB

  WHERE AC.CE_BANK_ACCT_USE_ID = CBAU.BANK_ACCT_USE_ID(+)

    AND CBAU.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID(+)

    AND CBB.BRANCH_PARTY_ID(+) = CBA.BANK_BRANCH_ID;

4.供应商表变化

5.Payment Documents 表变化

取Payment Documents SQL（R12）:

 Select cs.payment_document_name,

        DECODE(cs.MANUAL_PAYMENTS_ONLY_FLAG,

               'Y',

               'RECORDED',

               'N',

               'COMBINED')

   From ap_checks_all ch, CE_PAYMENT_DOCUMENTS CS, ap_lookup_codes lk

  Where CH.PAYMENT_DOCUMENT_ID = cs.PAYMENT_DOCUMENT_ID(+)

    AND DECODE(cs.MANUAL_PAYMENTS_ONLY_FLAG,

               'Y',

               'RECORDED',

               'N',

               'COMBINED') = lk.lookup_code(+)

    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:

 select zl.trx_id,

        zl.trx_line_id,

        zl.tax_regime_code,

        zl.tax,

        zl.tax_status_code,

        zl.tax_rate_code,

        zl.tax_rate

   from po_line_locations_all pll, zx_lines zl

  where pll.po_header_id = zl.trx_id

    and pll.line_location_id = zl.trx_line_id

    and zl.application_id(+) = 201

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