Oracle EBS 常用SQL及表结构

--应收发票主表 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