EBS应收AR查询脚本

--应收发票主表
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
      ,CT.SOLD_TO_SITE_USE_ID SOLD_TO_SITE_USE_ID
      ,CT.SOLD_TO_CONTACT_ID SOLD_TO_CONTACT_ID
      ,CT.BILL_TO_CUSTOMER_ID BILL_TO_CUSTOMER_ID
      ,CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
      ,RAA_BILL.CUST_ACCT_SITE_ID RAA_BILL_TO_ADDRESS_ID
      ,CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
      ,RAC_BILL_PARTY.JGZZ_FISCAL_CODE BILL_TO_TAXPAYER_ID
      ,CT.SHIP_TO_CUSTOMER_ID
      ,CT.SHIP_TO_SITE_USE_ID
      ,RAA_SHIP.CUST_ACCT_SITE_ID RAA_SHIP_TO_ADDRESS_ID
      ,CT.SHIP_TO_CONTACT_ID
      ,RAC_SHIP_PARTY.JGZZ_FISCAL_CODE SHIP_TO_TAXPAYER_ID
      ,CT.REMIT_TO_ADDRESS_ID
      ,CT.INVOICE_CURRENCY_CODE
      ,CT.CREATED_FROM
      ,CT.SET_OF_BOOKS_ID
      ,CT.PRINTING_ORIGINAL_DATE
      ,CT.PRINTING_LAST_PRINTED
      ,CT.PRINTING_OPTION
      ,CT.PRINTING_COUNT
      ,CT.PRINTING_PENDING
      ,CT.LAST_PRINTED_SEQUENCE_NUM
      ,CT.PURCHASE_ORDER
      ,CT.PURCHASE_ORDER_REVISION
      ,CT.PURCHASE_ORDER_DATE
      ,CT.CUSTOMER_REFERENCE
      ,CT.CUSTOMER_REFERENCE_DATE
      ,CT.COMMENTS
      ,CT.INTERNAL_NOTES
      ,CT.EXCHANGE_RATE_TYPE
      ,CT.EXCHANGE_DATE
      ,CT.EXCHANGE_RATE
      ,CT.TERRITORY_ID
      ,CT.END_DATE_COMMITMENT
      ,CT.START_DATE_COMMITMENT
      ,CT.ORIG_SYSTEM_BATCH_NAME
      ,CT.SHIP_DATE_ACTUAL
      ,CT.WAYBILL_NUMBER
      ,CT.DOC_SEQUENCE_ID
      ,CT.DOC_SEQUENCE_VALUE
      ,CT.PAYING_CUSTOMER_ID
      ,CT.PAYING_SITE_USE_ID
      ,CT.DEFAULT_USSGL_TRANSACTION_CODE
      ,CT.LAST_UPDATE_DATE
      ,CT.LAST_UPDATED_BY
      ,CT.CREATION_DATE
      ,CT.CREATED_BY
      ,CT.LAST_UPDATE_LOGIN
      ,CT.REQUEST_ID
      ,RAC_BILL_PARTY.PARTY_NAME RAC_BILL_TO_CUSTOMER_NAME --收单方
      ,RAC_BILL.ACCOUNT_NUMBER RAC_BILL_TO_CUSTOMER_NUM
      ,SU_BILL.LOCATION SU_BILL_TO_LOCATION --收单地点
      ,RAA_BILL_LOC.ADDRESS1 RAA_BILL_TO_ADDRESS1 --收单地址
      ,RAA_BILL_LOC.ADDRESS2 RAA_BILL_TO_ADDRESS2
      ,RAA_BILL_LOC.ADDRESS3 RAA_BILL_TO_ADDRESS3_DB
      ,DECODE(RAA_BILL.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(RAA_BILL_LOC.ADDRESS_STYLE
                                                   ,RAA_BILL_LOC.ADDRESS3
                                                   ,RAA_BILL_LOC.ADDRESS4
                                                   ,RAA_BILL_LOC.CITY
                                                   ,RAA_BILL_LOC.COUNTY
                                                   ,RAA_BILL_LOC.STATE
                                                   ,RAA_BILL_LOC.PROVINCE
                                                   ,FT_BILL.TERRITORY_SHORT_NAME
                                                   ,RAA_BILL_LOC.POSTAL_CODE)) RAA_BILL_TO_ADDRESS3
      ,RAA_BILL_LOC.CITY RAA_BILL_TO_CITY
      ,RAA_BILL_LOC.COUNTY RAA_BILL_TO_COUNTY
      ,RAA_BILL_LOC.STATE RAA_BILL_TO_STATE
      ,RAA_BILL_LOC.PROVINCE RAA_BILL_TO_PROVINCE
      ,RAA_BILL_LOC.POSTAL_CODE RAA_BILL_TO_POSTAL_CODE
      ,FT_BILL.TERRITORY_SHORT_NAME FT_BILL_TO_COUNTRY
      ,DECODE(RAA_BILL.CUST_ACCT_SITE_ID
             ,NULL
             ,NULL
             ,ARH_ADDR_PKG.ARXTW_FORMAT_ADDRESS(RAA_BILL_LOC.ADDRESS_STYLE