oracle销售开票数据流,ORACLE EBS AP发票到付款的数据流

--

1.发票创建时生成数据如下表

--

发票主表

SELECT

*

FROM AP_INVOICES_ALL A

WHERE A.INVOICE_NUM

=

'

20111213001

';

--

发票分配表

SELECT

*

FROM AP_INVOICE_DISTRIBUTIONS_ALL B

WHERE B.INVOICE_ID

=

697444;

--

发票付款计划表

SELECT

*

FROM AP_PAYMENT_SCHEDULES_ALL C

WHERE C.INVOICE_ID

=

697444;

--

From ap_invoices_all.invoice_id

--

2.发票验证并创建会计科目时产生的数据如下表

--

发票验证时产生的数据

SELECT

*

FROM AP_ACCOUNTING_EVENTS_ALL D

WHERE D.SOURCE_ID

=

697444

AND D.SOURCE_TABLE

=

'

AP_INVOICES

';

--

From ap_invoices_all.invoice_id

--

发票创建会计科目时产生的分录

SELECT

*

FROM AP_AE_HEADERS_ALL E

WHERE E.ACCOUNTING_EVENT_ID

=

1093101;

--

From AP_ACCOUNTING_EVENTS_ALL.souce_id = invoice_id

SELECT

*

FROM AP_AE_LINES_ALL G

WHERE G.AE_HEADER_ID

=

1088969;

--

3.发票分录查询(SOURCE_TABLE栏位分别是AP_INVOICE_DISTRIBUTIONS和AP_INVOICES，代表发票的分录和发票分配的分录)

SELECT AAL.SOURCE_TABLE

,AAL.

*

FROM AP_INVOICES_ALL AIA

,AP_ACCOUNTING_EVENTS_ALL AAE

,AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

WHERE AIA.INVOICE_NUM

=

'

20111213001

'

AND AIA.INVOICE_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_INVOICES

'

AND AAH.ACCOUNTING_EVENT_ID

= AAE.ACCOUNTING_EVENT_ID

AND AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID;

--

4.发票付款时产生的数据如下表

SELECT

*

FROM AP_INVOICE_PAYMENTS_ALL F

WHERE F.INVOICE_ID

=

697444;

--

CHECK_ID From AP_INVOICE_PAYMENTS_ALL

SELECT

*

FROM AP_CHECKS_ALL H

WHERE H.CHECK_ID

=

2357756;

--

CHECK_ID From AP_INVOICE_PAYMENTS_ALL.CHECK_ID

--

ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID

SELECT

*

FROM AP_ACCOUNTING_EVENTS_ALL D

WHERE D.ACCOUNTING_EVENT_ID

=

1086193;

--

ACCOUNTING_EVENT_ID From AP_INVOICE_PAYMENTS_ALL.ACCOUNTING_EVENT_ID

--

5.付款分录查询

SELECT AAL.

*

FROM AP_CHECKS_ALL ACA

,AP_ACCOUNTING_EVENTS_ALL AAE

--

,AP_INVOICE_PAYMENTS_ALL AIP

,AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

WHERE ACA.CHECK_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_CHECKS

'

AND AAE.ACCOUNTING_EVENT_ID

= AAH.ACCOUNTING_EVENT_ID

AND AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID

--

AND AAL.SOURCE_ID = AIP.INVOICE_PAYMENT_ID--负债科目

--

AND AAL.SOURCE_ID = ACA.CHECK_ID--现金科目

AND AAH.ACCOUNTING_DATE

>= TO_DATE(

'

20110907

'

,

'

yyyymmdd

')

AND AAH.ACCOUNTING_DATE

< TO_DATE(

'

20110908

'

,

'

yyyymmdd

')

AND AAH.GL_TRANSFER_FLAG

=

'

Y

'

AND AAH.ORG_ID

=

236;

--

6. 发票核销预付款发票的金额

SELECT AID1.ROWID ROW_ID

,AID1.INVOICE_ID INVOICE_ID

,AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID

,AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID

,AID1.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER

,(

-

1)

* AID1.AMOUNT PREPAY_AMOUNT_APPLIED

,NVL(AID2.PREPAY_AMOUNT_REMAINING

,AID2.AMOUNT) PREPAY_AMOUNT_REMAINING

,AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID

,AID1.ACCOUNTING_DATE ACCOUNTING_DATE

,AID1.PERIOD_NAME PERIOD_NAME

,AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID

,AID1.DESCRIPTION DESCRIPTION

,AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID

,AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID

,AID1.ORG_ID ORG_ID

,AI.INVOICE_NUM PREPAY_NUMBER

,AI.VENDOR_ID VENDOR_ID

,AI.VENDOR_SITE_ID VENDOR_SITE_ID

,ATC.TAX_ID TAX_ID

,ATC.NAME TAX_CODE

FROM AP_INVOICES_ALL AI

,AP_INVOICE_DISTRIBUTIONS_ALL AID1

,AP_INVOICE_DISTRIBUTIONS_ALL AID2

,AP_TAX_CODES ATC

WHERE AID1.PREPAY_DISTRIBUTION_ID

= AID2.INVOICE_DISTRIBUTION_ID

AND AI.INVOICE_ID

= AID2.INVOICE_ID

AND AID1.AMOUNT

<

0

AND NVL(AID1.REVERSAL_FLAG

,

'

N

')

!=

'

Y

'

AND AID1.TAX_CODE_ID

= ATC.TAX_ID(

+)

AND AID1.LINE_TYPE_LOOKUP_CODE

=

'

PREPAY

'

AND AID1.INVOICE_ID

=

676404;

--

发票ID

--

6. 预付款发票核销发票的金额

SELECT AID1.ROWID ROW_ID

,AID1.INVOICE_ID INVOICE_ID

,AID1.INVOICE_DISTRIBUTION_ID INVOICE_DISTRIBUTION_ID

,AID1.PREPAY_DISTRIBUTION_ID PREPAY_DISTRIBUTION_ID

,AID2.DISTRIBUTION_LINE_NUMBER PREPAY_DIST_NUMBER

,(

-

1)

* AID1.AMOUNT PREPAY_AMOUNT_APPLIED

,AID1.DIST_CODE_COMBINATION_ID DIST_CODE_COMBINATION_ID

,AID1.ACCOUNTING_DATE ACCOUNTING_DATE

,AID1.PERIOD_NAME PERIOD_NAME

,AID1.SET_OF_BOOKS_ID SET_OF_BOOKS_ID

,AID1.DESCRIPTION DESCRIPTION

,AID1.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID

,AID1.RCV_TRANSACTION_ID RCV_TRANSACTION_ID

,AID1.ORG_ID ORG_ID

,AI.INVOICE_NUM INVOICE_NUM

,AI.VENDOR_ID VENDOR_ID

,AI.VENDOR_SITE_ID VENDOR_SITE_ID

,ATC.TAX_ID TAX_ID

,ATC.NAME TAX_CODE

,AID2.INVOICE_ID PREPAY_ID

FROM AP_INVOICES_ALL AI

,AP_INVOICE_DISTRIBUTIONS_ALL AID1

,AP_INVOICE_DISTRIBUTIONS_ALL AID2

,AP_TAX_CODES ATC

WHERE AID1.PREPAY_DISTRIBUTION_ID

= AID2.INVOICE_DISTRIBUTION_ID

AND AI.INVOICE_ID

= AID1.INVOICE_ID

AND AID1.AMOUNT

<

0

AND NVL(AID1.REVERSAL_FLAG

,

'

N

')

!=

'

Y

'

AND AID2.TAX_CODE_ID

= ATC.TAX_ID(

+)

AND AID1.LINE_TYPE_LOOKUP_CODE

=

'

PREPAY

'

AND AID2.INVOICE_ID

=

676444

--

预付款发票ID

AND AI.INVOICE_TYPE_LOOKUP_CODE

NOT

IN

(

'

PREPAYMENT

'

,

'

CREDIT

'

,

'

DEBIT

');

--

发票是否被验证的脚本

/*

BEGIN

fnd_client_info.set_org_context(236);

END;

*/

SELECT AP_INVOICES_PKG.GET_APPROVAL_STATUS(AI.INVOICE_ID

,AI.INVOICE_AMOUNT

,AI.PAYMENT_STATUS_FLAG

,AI.INVOICE_TYPE_LOOKUP_CODE)

--

发票是否已验证，已验证的状态为APPROVED

,AI.

*

FROM AP_INVOICES_ALL AI

WHERE AI.INVOICE_NUM

IN (

'

2011110888

'

,

'

20111202001

');

--

应付发票及付款日记账分录追溯

--

日记账分录

SELECT AAL.

*

,GJL.

*

FROM AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

,GL_JE_HEADERS GJH

,GL_JE_LINES GJL

WHERE AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID

AND GJH.JE_HEADER_ID

= GJL.JE_HEADER_ID

AND GJL.GL_SL_LINK_ID

= AAL.GL_SL_LINK_ID

AND GJH.JE_SOURCE

=

'

Payables

'

AND GJH.PERIOD_NAME

=

'

Dec-11

'

AND AAH.AE_HEADER_ID

=

1097269;

--

发票匹配接收时与接收的关联关系

SELECT A.RCV_TRANSACTION_ID

--

rcv_transactions.transaction_id

,A.PO_DISTRIBUTION_ID

,A.

*

FROM AP_INVOICE_DISTRIBUTIONS_ALL A

WHERE A.INVOICE_ID

=

703771;

--

应付总账分录追溯到付款

SELECT

DISTINCT ACA.CHECK_NUMBER

,(

SELECT INVOICE_NUM

FROM AP_INVOICES_ALL AIA

WHERE AIA.INVOICE_ID

= AIP.INVOICE_ID) INVOICE_NUM

FROM AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

,GL_JE_HEADERS GJH

,GL_JE_LINES GJL

,GL_JE_BATCHES GJB

,AP_CHECKS_ALL ACA

,AP_ACCOUNTING_EVENTS_ALL AAE

,AP_INVOICE_PAYMENTS_ALL AIP

WHERE AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID

AND GJH.JE_HEADER_ID

= GJL.JE_HEADER_ID

AND GJL.GL_SL_LINK_ID

= AAL.GL_SL_LINK_ID

AND GJH.JE_SOURCE

=

'

Payables

'

AND GJB.JE_BATCH_ID

= GJH.JE_BATCH_ID

AND ACA.CHECK_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_CHECKS

'

AND AAE.ACCOUNTING_EVENT_ID

= AAH.ACCOUNTING_EVENT_ID

AND AIP.CHECK_ID

= ACA.CHECK_ID

AND GJH.PERIOD_NAME

=

'

Jan-12

'

AND GJB.NAME

=

'

12.01.11报销 51026 Payables 25920611: A 4855700

';

--

AND gjh.name = '800346773 Purchase Invoices CNY'

--

应付总账分录追溯到发票

SELECT

DISTINCT AIA.INVOICE_NUM

FROM AP_AE_HEADERS_ALL AAH

,AP_AE_LINES_ALL AAL

,GL_JE_HEADERS GJH

,GL_JE_LINES GJL

,GL_JE_BATCHES GJB

,AP_INVOICES_ALL AIA

,AP_ACCOUNTING_EVENTS_ALL AAE

WHERE AAH.AE_HEADER_ID

= AAL.AE_HEADER_ID

AND GJH.JE_HEADER_ID

= GJL.JE_HEADER_ID

AND GJL.GL_SL_LINK_ID

= AAL.GL_SL_LINK_ID

AND GJH.JE_SOURCE

=

'

Payables

'

AND GJB.JE_BATCH_ID

= GJH.JE_BATCH_ID

AND AIA.INVOICE_ID

= AAE.SOURCE_ID

AND AAE.SOURCE_TABLE

=

'

AP_INVOICES

'

AND AAH.ACCOUNTING_EVENT_ID

= AAE.ACCOUNTING_EVENT_ID

AND GJH.PERIOD_NAME

=

'

Jan-12

'

AND GJB.NAME

=

'

12.01.11报销 51026 Payables 25920611: A 4855700

'

--

AND gjh.name = '800346773 Purchase Invoices CNY'