oracle怎樣從ap發(fā)票查會計分錄

--1.發(fā)票創(chuàng)建時生成數(shù)據(jù)如下表

創(chuàng)新互聯(lián)成立于2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項目網(wǎng)站建設(shè)、做網(wǎng)站網(wǎng)站策劃，項目實施與項目整合能力。我們以讓每一個夢想脫穎而出為使命，1280元新余做網(wǎng)站,已為上家服務(wù),為新余各地企業(yè)和個人服務(wù),聯(lián)系電話:13518219792

--發(fā)票主表

SELECT * FROM AP_INVOICES_ALL A WHERE A.INVOICE_NUM = '20111213001';

--發(fā)票分配表

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL B WHERE B.INVOICE_ID = 697444;

--發(fā)票付款計劃表

SELECT * FROM AP_PAYMENT_SCHEDULES_ALL C WHERE C.INVOICE_ID = 697444; --From ap_invoices_all.invoice_id

--2.發(fā)票驗證并創(chuàng)建會計科目時產(chǎn)生的數(shù)據(jù)如下表

--發(fā)票驗證時產(chǎn)生的數(shù)據(jù)

SELECT *

FROM AP_ACCOUNTING_EVENTS_ALL D

WHERE D.SOURCE_ID = 697444

AND D.SOURCE_TABLE = 'AP_INVOICES'; --From ap_invoices_all.invoice_id

--發(fā)票創(chuàng)建會計科目時產(chǎn)生的分錄

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.發(fā)票分錄查詢(SOURCE_TABLE欄位分別是AP_INVOICE_DISTRIBUTIONS和AP_INVOICES，代表發(fā)票的分錄和發(fā)票分配的分錄)

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.發(fā)票付款時產(chǎn)生的數(shù)據(jù)如下表

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--負(fù)債科目

--AND AAL.SOURCE_ID = ACA.CHECK_ID--現(xiàn)金科目

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. 發(fā)票核銷預(yù)付款發(fā)票的金額

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; --發(fā)票ID

--6. 預(yù)付款發(fā)票核銷發(fā)票的金額

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 --預(yù)付款發(fā)票ID

AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN

('PREPAYMENT'

,'CREDIT'

,'DEBIT');

--發(fā)票是否被驗證的腳本

/*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) --發(fā)票是否已驗證，已驗證的狀態(tài)為APPROVED

,AI.*

FROM AP_INVOICES_ALL AI

WHERE AI.INVOICE_NUM IN ('2011110888'

,'20111202001');

--應(yīng)付發(fā)票及付款日記賬分錄追溯

--日記賬分錄

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;

--發(fā)票匹配接收時與接收的關(guān)聯(lián)關(guān)系

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;

--應(yīng)付總賬分錄追溯到付款

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'

--應(yīng)付總賬分錄追溯到發(fā)票

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'

oracle財務(wù)模塊

Oracle ERP 財務(wù)部分 包含了 總賬 應(yīng)收 應(yīng)付 現(xiàn)金 報銷 等模塊，樓主應(yīng)該是應(yīng)收會計，應(yīng)收一般針對公司或工廠的銷售訂單或服務(wù)訂單 而言，當(dāng)然也包含預(yù)收款部分，比如一個銷售合同被錄入系統(tǒng)后，發(fā)貨確認(rèn)后，一般會自動在應(yīng)收模塊生成相應(yīng)發(fā)票，如果公司沒有啟用Oracle 訂單模塊，那么

財務(wù)人員就要根據(jù)其他系統(tǒng)手工或?qū)霊?yīng)收發(fā)票了，應(yīng)收發(fā)票最終要傳到總賬，生成總賬日記賬憑證

Oracle怎么導(dǎo)出存儲過程

1、首先登錄到數(shù)據(jù)庫中，點擊右上角有個工具欄，點擊工具欄上的tools。

2、選擇導(dǎo)出用戶對象，點擊tools工具之后，向下查找Export User Objects功能，通過這個功能導(dǎo)出數(shù)據(jù)。

3、選擇需要導(dǎo)出的表或者序列或者存儲過程等。在Type列中展示的是對象屬性，表、序列和存儲過程等。這里要選擇存儲過程或者序列。

4、選擇多個導(dǎo)出的對象。按住ctrl鍵進(jìn)行多選導(dǎo)出的對象。點擊Output File輸出文件路徑，選擇文件路徑輸入文件名稱，后綴是sql。如果后綴是sql，則是一些數(shù)據(jù)庫語句，可以直接在其他應(yīng)用上執(zhí)行，

5、開始導(dǎo)出數(shù)據(jù)腳本。點擊Export按鈕，開始對選中的對象進(jìn)行導(dǎo)出成數(shù)據(jù)腳本。出的時候需要選擇一個目錄。

6、查看導(dǎo)出文件。導(dǎo)出完成后，雙擊打開導(dǎo)出后的文件，就可以看到是一些建表存儲過程等腳本。查看導(dǎo)出文件就可以看到是具體的腳本語言。

Oracle Personal Time and Expense

這是ORACLE很早期的產(chǎn)品，現(xiàn)在由Oracle Projects中的Oracle Internet Time替代了，這個可以在oracle官方網(wǎng)站下載

Oracle新建數(shù)據(jù)庫

Oracle新建數(shù)據(jù)庫可以參考以下操作方法：

1、首先點擊桌面左下角的開始圖標(biāo)；

2、在跳出的窗口中找到并點擊“Database Configuration Assistant”選項；

3、接著選擇“創(chuàng)建數(shù)據(jù)庫”并點擊“下一步”選項；

4、接著在跳出來的窗口中輸入數(shù)據(jù)庫的參數(shù)，點擊“下一步”；

5、會彈出對數(shù)據(jù)庫的一些概要，檢查無誤后點擊“完成”選項；

6、進(jìn)行數(shù)據(jù)庫“orcl”的創(chuàng)建；

7、耐心等待一段時間后，就創(chuàng)建成功了。如果不能解決問題，建議重復(fù)上述步驟再操作一遍試試看，如果還是不能解決問題，建議請專業(yè)的工作人員幫忙解決問題。

如何啟動oracle

1、打開服務(wù)窗口：使用 win+r 快捷鍵打開運行窗口，輸入命令?services.msc?按回車。

2、在標(biāo)準(zhǔn)視圖下找到 oracle 的 7個服務(wù)。

3、可以看到 7 個服務(wù)中有四個服務(wù)的啟動方式是自動運行的(綠線標(biāo)注的)。把這幾個服務(wù)的啟動方式改成手動。

注：（1）以O(shè)racleDBConsoleorcl 服務(wù)為例：右擊服務(wù)– 屬性。

（2）修改完成之后的效果，除 OracleJobSchedulerORCL 是禁用外，其他的都是手動啟動。

4、按照先后順序依次啟動服務(wù)”O(jiān)racle ServiceORCL“，”O(jiān)racleOraDb11g-home1TNSListener“。啟動方式：右擊服務(wù)–啟動，如下圖所示，即可成功啟動。

本文名稱：oracle如何報銷,oracle 報銷系統(tǒng)
URL標(biāo)題：http://chinadenli.net/article43/dsshphs.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供ChatGPT、網(wǎng)站導(dǎo)航、App開發(fā)、Google、微信小程序、網(wǎng)站排名

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)