oracle 采购 税额,Oracle EBS 应收发票相关 脚本 [转]

Oracle EBS 应收发票相关 脚本

--应收发票主表

SELECT *

FROM RA_CUSTOMER_TRX_ALL A

WHERE A.TRX_NUMBER = '156640'

AND A.ORG_ID = 236;

--应收发票行表

SELECT B.EXTENDED_AMOUNT --不含税原币金额

,B.EXTENDED_ACCTD_AMOUNT --不含税本币金额

,B.GROSS_UNIT_SELLING_PRICE -- 含税单价

,B.GROSS_EXTENDED_AMOUNT --含税金额

,B.REVENUE_AMOUNT --收入金额

,B.UNIT_SELLING_PRICE --不含税单价

,B.UNIT_STANDARD_PRICE

,B.*

FROM RA_CUSTOMER_TRX_LINES_ALL B

WHERE B.CUSTOMER_TRX_ID = 1449740;

--发票分配表

SELECT C.AMOUNT --不含税原原币金额

,C.ACCTD_AMOUNT --不含税本币金额

,C.*

FROM RA_CUST_TRX_LINE_GL_DIST_ALL C

WHERE C.CUSTOMER_TRX_ID = 1449519;

--发票应收科目

SELECT RCTLGDA.*

FROM

RA_CUSTOMER_TRX_ALL RCA

,RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA

WHERE RCA.CUSTOMER_TRX_ID =

RCTLGDA.CUSTOMER_TRX_ID

AND RCTLGDA.ACCOUNT_CLASS =

'REC'

AND RCA.CUSTOMER_TRX_ID =

1337786;

--发票收入和税科目

SELECT RCTLGDA.*

FROM

RA_CUSTOMER_TRX_ALL RCA

,RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA

WHERE RCA.CUSTOMER_TRX_ID =

RCTLGDA.CUSTOMER_TRX_ID

AND RCTLGDA.ACCOUNT_CLASS

<> 'REC'

AND RCA.CUSTOMER_TRX_ID =

1337786;

--或

SELECT RCTLGDA.*

FROM

RA_CUSTOMER_TRX_ALL RCA

,RA_CUSTOMER_TRX_LINES_ALL RCL

,RA_CUST_TRX_LINE_GL_DIST_ALL RCTLGDA

WHERE RCA.CUSTOMER_TRX_ID =

RCL.CUSTOMER_TRX_ID

AND RCL.CUSTOMER_TRX_LINE_ID =

RCTLGDA.CUSTOMER_TRX_LINE_ID

AND RCA.CUSTOMER_TRX_ID =

1337786;

--发票收款计划表

SELECT ARP.AMOUNT_DUE_ORIGINAL --发票原始金额(含税)

,ARP.TAX_ORIGINAL --发票原始税额

,ARP.TAX_REMAINING --发票税余额

,ARP.AMOUNT_APPLIED --发票收款金额

,ARP.AMOUNT_LINE_ITEMS_ORIGINAL --发票行原始金额

,ARP.AMOUNT_LINE_ITEMS_REMAINING --发票行余额

,ARP.AMOUNT_DUE_REMAINING --到期余额

,ARP.ACCTD_AMOUNT_DUE_REMAINING

--本币到期余额 ,ARP.AMOUNT_ADJUSTED --发票调整金额

,ARP.AMOUNT_CREDITED --发票做过贷项通知单金额

,ARP.FREIGHT_ORIGINAL

,ARP.FREIGHT_REMAINING

,ARP.DISCOUNT_ORIGINAL

,ARP.DISCOUNT_REMAINING

,ARP.DISCOUNT_TAKEN_EARNED

,ARP.DISCOUNT_TAKEN_UNEARNED

,ARP.RECEIVABLES_CHARGES_CHARGED

,ARP.RECEIVABLES_CHARGES_REMAINING

,ARP.*

FROM AR_PAYMENT_SCHEDULES_ALL ARP

WHERE ARP.CUSTOMER_TRX_ID = 1485432 --Num:

80210055

--发票是否做过贷项

SELECT *

FROM RA_CUSTOMER_TRX_ALL RCA

WHERE RCA.PREVIOUS_CUSTOMER_TRX_ID = 1337786;

--ra_customer_trx_all.customer_trx_id

--发票现金收款金额

SELECT *

FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA

WHERE ARA.APPLIED_CUSTOMER_TRX_ID = 1337785

AND ARA.APPLICATION_TYPE =

'CASH'

AND ARA.STATUS =

'APP';

--发票被贷项通知单核销金额

SELECT *

FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA

WHERE ARA.APPLIED_CUSTOMER_TRX_ID = 1337786

AND ARA.APPLICATION_TYPE =

'CM'

AND ARA.STATUS = 'APP';

--发票调整金额

SELECT ADJ.*

FROM AR_ADJUSTMENTS_ALL ADJ

,RA_CUSTOMER_TRX_ALL RCT

WHERE RCT.CUSTOMER_TRX_ID =

ADJ.CUSTOMER_TRX_ID

AND ADJ.STATUS = 'A'

AND RCT.CUSTOMER_TRX_ID =

1337785;

--收款录入后产生的信息如下:

--收款主表

SELECT *

FROM AR_CASH_RECEIPTS_ALL ACRA

WHERE ACRA.RECEIPT_NUMBER = '20120106001';

--收款历史记录表

SELECT *

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH

WHERE ACRH.CASH_RECEIPT_ID = 304387;

--收款事务处理表

SELECT *

FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA

WHERE ARA.CASH_RECEIPT_ID = 304387;

--收款分配表

--收款录入时产生两条记录，SOURCE_ID分别对应

--SOURCE_TABLE为 'RA'

时SOURCE_ID对应的是AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID,为CRH时SOURCE_ID对应的是AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID

--下面SQL获取的是收款录入时产生的未核销科目及收款核销时产生的应收账款和未核销科目

SELECT *

FROM AR_DISTRIBUTIONS_ALL ADA

WHERE ADA.SOURCE_ID IN

(SELECT ARA.RECEIVABLE_APPLICATION_ID

FROM AR_RECEIVABLE_APPLICATIONS_ALL ARA

WHERE ARA.CASH_RECEIPT_ID = 304387)

AND ADA.SOURCE_TABLE =

'RA';

--收款录入时产生两条分录：现金和未核销，下面语句获取的是现金科目

SELECT *

FROM AR_DISTRIBUTIONS_ALL ADA

WHERE ADA.SOURCE_ID IN

(SELECT ACRH.CASH_RECEIPT_HISTORY_ID

FROM AR_CASH_RECEIPT_HISTORY_ALL ACRH

WHERE ACRH.CASH_RECEIPT_ID = 304387)

AND ADA.SOURCE_TABLE =

'CRH';

--应收发票收货方 收单方 到期日等

SELECT CT.CUSTOMER_TRX_ID CUSTOMER_TRX_ID

,CT.TRX_NUMBER TRX_NUMBER

,CT.OLD_TRX_NUMBER OLD_TRX_NUMBER

,CT_REL.TRX_NUMBER CT_RELATED_TRX_NUMBER

,CT.RECURRED_FROM_TRX_NUMBER CT_MODEL_TRX_NUMBER

,CT.TRX_DATE TRX_DATE

,ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(CT.CUSTOMER_TRX_ID

,CT.TERM_ID

,CT.TRX_DATE) TERM_DUE_DATE --到期日

,CT.PREVIOUS_CUSTOMER_TRX_ID PREVIOUS_CUSTOMER_TRX_ID

,CT.INITIAL_CUSTOMER_TRX_ID INITIAL_CUSTOMER_TRX_ID

,CT.RELATED_BATCH_SOURCE_ID RELATED_BATCH_SOURCE_ID

,CT.RELATED_CUSTOMER_TRX_ID RELATED_CUSTOMER_TRX_ID

,CT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID

,CT.BATCH_ID BATCH_ID

,CT.BATCH_SOURCE_ID BATCH_SOURCE_ID

,CT.REASON_CODE REASON_CODE

,CT.TERM_ID TERM_ID

,CT.PRIMARY_SALESREP_ID PRIMARY_SALESREP_ID

,CT.AGREEMENT_ID AGREEMENT_ID

,CT.CREDIT_METHOD_FOR_RULES CREDIT_METHOD_FOR_RULES

,CT.CREDIT_METHOD_FOR_INSTALLMENTS

CREDIT_METHOD_FOR_INSTALLMENTS

,CT.RECEIPT_METHOD_ID RECEIPT_METHOD_ID

,CT.INVOICING_RULE_ID INVOICING_RULE_ID

,CT.SHIP_VIA SHIP_VIA

,CT.FOB_POINT FOB_POINT

,CT.FINANCE_CHARGES

,CT.COMPLETE_FLAG COMPLETE_FLAG

,CT.CUSTOMER_BANK_ACCOUNT_ID CUSTOMER_BANK_ACCOUNT_ID

,CT.RECURRED_FROM_TRX_NUMBER RECURRED_FROM_TRX_NUMBER

,CT.STATUS_TRX STATUS_TRX

,CT.DEFAULT_TAX_EXEMPT_FLAG DEFAULT_TAX_EXEMPT_FLAG

,CT.SOLD_TO_CUSTOMER_ID SOLD_TO_CUSTOMER_ID