oracle日記賬攤銷

A. oracle FA運行折舊時，出現無法讀取常式&ROUTINE中配置文件選項TIME_DIAGNOSTIC

--取兩個日期間的工作天數
SELECT COUNT(*) INTO vn
FROM (SELECT vod + ROWNUM - 1 RN
FROM DUAL
CONNECT BY ROWNUM <= vnd - vod)
WHERE TO_CHAR(RN, 'D') BETWEEN 2 AND 6;

--返回數字
RETURN vn;

--異常判斷並返回-1
EXCEPTION
WHEN OTHERS THEN
RETURN -1;
END fun_get_workdays;

然後select id,fun_get_workdays(begin_date date, end_date date) from table

B. oracle怎樣從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'

C. oracle在做日記賬分錄時會計科目是固定值而不能選擇其他，是做了什麼設置嗎

資料庫設置

D. oracle ebs 日記賬導入請求調用哪個後台

總賬裡面的日記賬導入用以下的表

select * from gl_interface gi

E. oracle中沖銷的日記賬過賬嗎

while (rs.next()) {
Bars bar = new Bars();
bar.setId(rs.getLong("id"));
bar.setName(rs.getString("name"));
bar.setType(rs.getInt("type"));
bar.setCreatorId(rs.getLong("creator_id"));
resultList.add(bar);
}

F. oracle ebs總帳日記帳可以批量提交審批嗎

Workflow可以發送兩種類型的通知：消息性通知，如「你的申請被審批了」，不需要接收者做出任何響應；回應式通知，如「GL日記賬需要你的審批」，接收者需要做出相應的Response，工作流才能繼續前進。
通知除了在系統中可以查看、處理外，也可以通過Mail查看、處理；可以本人處理，也可以轉交他人處理。

G. oracle總賬期間在什麼狀態下不可以錄入日記賬

1、日記帳是逐日逐筆登記——登記的依據是收款憑證、付款憑證。 2、總帳可以隨時登——在記帳憑證帳務處理程序下，根據記帳憑證登記。注意：太麻煩，規模小業務量少的單位採用。 可以十天半月登記一次——在科目匯總表帳務處理程序下

H. oracle 資產模塊怎麼做減值准備

直接在資產台帳上面修改資產的現值就OK了,注意要考慮前期的累計折舊差異是放在當期處理還是在資產剩餘年限攤銷！

I. oracle EBS 再輸入日記賬的時候 分類賬為空，可是我已經在系統設置里設置過了

系統管理員--配置文件設置了沒有？GL:分類賬