Oracle EBS: 修正PO interface資料重轉
select * from po_headers_interface
where ...
select * from po_lines_interface
where interface_header_id = ?19248184
update po_headers_interface
set process_code = null,
processing_id = null
where interface_header_id = ?19248184
update po_lines_interface
set process_code = null,
processing_id = null
where interface_header_id = ?19248184
select * from po_interface_errors
where interface_header_id = ?19248184
Oracle EBS: 修改RCV interface資料以重收貨
update rcv_headers_interface rhi
set processing_request_id=null ,
receipt_header_id=null ,
validation_flag = 'Y' ,
processing_status_code = 'PENDING'
where 1=1 --group_id = 2634307
and processing_status_code = 'ERROR'
and exists (select header_interface_id
from rcv_transactions_interface rti
where rhi.HEADER_INTERFACE_ID=rti.HEADER_INTERFACE_ID )
update rcv_transactions_interface
set request_id=null ,
processing_request_id=null ,
order_transaction_id=null ,
primary_quantity=null ,
primary_unit_of_measure=null ,
interface_transaction_qty=null ,
validation_flag = 'Y' ,
processing_status_code = 'PENDING' ,
transaction_status_code = 'PENDING'
where 1=1 --group_id = 2634307
and (processing_status_code = 'ERROR' or transaction_status_code = 'ERROR')
--------------------------------------------------------------------------------------
update rcv_headers_interface rhi
set processing_request_id=null ,
receipt_header_id=null ,
validation_flag = 'Y' ,
processing_status_code = 'PENDING'
where group_id = ?2638496
and processing_status_code = 'ERROR'
and exists (select header_interface_id
from rcv_transactions_interface rti
where rhi.HEADER_INTERFACE_ID=rti.HEADER_INTERFACE_ID )
update rcv_transactions_interface
set request_id=null ,
processing_request_id=null ,
order_transaction_id=null ,
primary_quantity=null ,
primary_unit_of_measure=null ,
interface_transaction_qty=null ,
validation_flag = 'Y' ,
processing_status_code = 'PENDING' ,
transaction_status_code = 'PENDING'
where group_id = ?2638496
Oracle EBS: 用於處理OM order之API
OE_ORDER_PUB.Process_order(
Standard Parameters
Specific Parameters)
功能包括:
CREATE
UPDATE
Reserve
Unreserve
Split line
Delete
Book order
Apply hold
Release hold
Ref:
1.Process Order API In Order Management (Doc ID 746787.1)
Q: OM的transaction type是否有API可用?
A: 沒有, 若要update, 可以直接改table, 但對於新增, 尚未能確定要寫入哪些table, 或許可以只處理以下兩個table:
OE_TRANSACTION_TYPES_ALL.
OE_TRANSACTION_TYPES_TL.
Ref:
1.API for creating transaction type in order management in r12
https://community.oracle.com/mosc/discussion/4297735/api-for-creating-transaction-type-in-order-management-in-r12
Receipt Routing常看到, 但沒特別注意有什麼差別. 既然有人問了, 就來查查.
依原始說明資料:
Direct Delivery: 收貨後直接入庫, 在同一筆交易中
Standard Receipt: 收貨與入庫是各自獨立的交易, 入庫前可作檢驗或轉倉
Inspection Required: 收貨後進行檢驗, 另一筆獨立交易作入庫
--
Shipments are received into a receiving location and put away in the same transaction. Put away happens automatically upon receipt creation.
Shipments are received into a receiving location and then put away in a separate transaction. Standard receipts can be inspected or transferred before put away.
Shipments are received into a receiving location and then inspected and put away in separate transactions. You can accept or reject material during the inspection, and put away to separate locations, based on the inspection result.
Ref:
1.Receipt Routing
https://docs.oracle.com/en/cloud/saas/supply-chain-management/23a/famli/receipt-routing.html#s20029748
標準功能不支援, 但可用SQL達成
--
DISCLAIMER: THE RE-OPENING OF A CLOSED INVENTORY PERIOD COULD POTENTIALLY CAUSE DATA CORRUPTION AND ANY DATA CORRUPTION CAUSED BY RE-OPENING A CLOSED INVENTORY PERIOD WILL BE THE RESPONSIBILITY OF THE CUSTOMER AND NO DATA FIX WILL BE PROVIDED FOR ANY DATA CORRUPTION THAT HAS BEEN CAUSED BY RE-OPENING A CLOSED PERIOD.
TEST THOROUGHLY ALL SCRIPTS ON A NON-PRODUCTION INSTANCE, FIRST BACKING UP ALL TABLE DATA PRIOR TO IMPLEMENTING IN PRODUCTION.
IF THERE IS CONCERN THAT RE-OPENING A CLOSED PERIOD MAY CAUSE DATA CORRUPTION PLEASE OPEN AN SR WITH ORACLE SUPPORT PRIOR TO RE-OPENING A CLOSED PERIOD.
-- A script to list all inventory periods for a specific organization
-- A script to reopen closed inventory accounting periods
-- The script will reopen all inventory periods for the specified
-- Delete scripts to remove the rows created during the period close process to prevent duplicate rows
-- organization starting from the specified accounting period.
-- The organization_id can be obtained from the MTL_PARAMETERS table.
-- The acct_period_id can be obtained from the ORG_ACCT_PERIODS table.
1. Backup the following tables:
org_acct_periods, mtl_period_summary, mtl_period_cg_summary, mtl_per_close_dtls and cst_period_close_summary.
2. SELECT acct_period_id period, open_flag, period_name name,
period_start_date, schedule_close_date, period_close_date
FROM org_acct_periods
WHERE organization_id = &org_id
order by 1,2;
3. UPDATE org_acct_periods
SET open_flag = 'Y',
period_close_date = NULL,
summarized_flag = 'N'
WHERE organization_id = &&org_id
AND acct_period_id >= &&acct_period_id;
DELETE mtl_period_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;
DELETE mtl_period_cg_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;
DELETE mtl_per_close_dtls
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;
DELETE cst_period_close_summary
WHERE organization_id = &org_id
AND acct_period_id >= &acct_period_id;
4. commit
5.Re-summarize all periods after problematic period again in order by running 'Period Close Reconciliation Report'.
Note:
The tables, mtl_period_summary, mtl_period_cg_summary and mtl_per_close_dtls are designed in 11i. After R12 upgrade they were not used.
But as the script is available from earlier days, those tables were kept like that without deleting from the resummarization script.
they can ignore these tables now.
The table cst_period_close_summary is the only table used in R12. The PCRR report picks the records from this table.
Ref:
1.Re-Open a Closed Inventory Accounting Period (Doc ID 472631.1)
問題: 開啟Oracle Reports出現閃退問題
原因: 可能有用過其他編碼儲存過檔案
解法: 新增環境變數
NLS_LANG = AMERICAN_AMERICA.UTF8
補充:
1.檔案路徑名稱不可有中文
2.若有在client端執行的Form , 中文會變亂碼
Oracle EBS: Supplier Item Catalog畫面Due Date之作用
在說明文件上查到:
11. Enter the Due Date to get documents that are current as of this date or future effective. If you access this window from the Requisitions or Purchase Orders window, the default is the due date on the originating document line.
Ref:
1.Finding Supplier Items
https://docs.oracle.com/cd/A60725_05/html/comnls/us/po/sicov01.htm
問題: 因要保留舊PO號碼, 故對調新舊PO號碼. 舊PO已approve過, 把新舊PO號碼對調後, 新PO無法正常approve
Error Message(在workflow及user收到的通知信上):
po.plsql.PO_DOCUMENT_ACTION_AUTH.approve:90:archive_po not successful - po.plsql.PO_DOCUMENT_ACTION_PVT.do_action:110:unexpected error in action call
原因: archive中已有相同PO號碼及版次, 造成unique key衝突
解法: 修改archive資料
Ref:
1.PO Approval Errors: Po.plsql.PO_DOCUMENT_ACTION_AUTH.Approve:90:Archive_po Not Successful: ORA-00001: (PO.PO_HEADERS_ARCHIVE_U2) (Doc ID 1296639.1)
Q: 使用excel格式(97-2003)的BI publisher report是否筆數能超過65536筆?
A: 不行, 只能使用RTF格式, 或是把資料分割到多到工作表中. 在BI Publisher 11.1.1.9及後版本可以產生xslx檔.
Ref:
1.Can we get more than 66K records by using BI publisher reports in excel output format ?
https://community.oracle.com/mosc/discussion/4115589/can-we-get-more-than-66k-records-by-using-bi-publisher-reports-in-excel-output-format
2.Splitting the Report into Multiple Sheets (Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher)
https://docs.oracle.com/middleware/11119/bip/BIPRD/create_excel_tmpl.htm#ext_multsheets
3.BI Publisher Excel Template Layout Output is Truncating Data to 65536 Rows (Doc ID 1469264.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=533024111404255&id=1469264.1&_afrWindowMode=0&_adf.ctrl-state=orvd8jckk_4
工單狀況與lookup_code對應
SELECT flv.language,
flv.lookup_code,
flv.meaning,
flv.description,
flv.source_lang,
flv.security_group_id,
flv.tag,
flttl.lookup_type,
flttl.view_application_id,
flttl.language,
flttl.meaning,
flttl.description
FROM applsys.fnd_lookup_values flv, applsys.fnd_lookup_types_tl flttl
WHERE (flttl.lookup_type = flv.lookup_type
AND flv.lookup_type LIKE 'WIP_JOB_STATUS');
號稱可在Edge/Chrome直接執行Oracle EBS的程式. 可試用, 有天數限制, 期滿後需購買授權.
ThinForms Chrome Extension
Run your Oracle EBS and Oracle Forms Applications with the latest Chrome Browser or Microsoft Edge (Chromium)
Cookie and SSO support, native EBS login
Fixes various Forms Bugs, e.g. TAB key in Database Login Screen
(simply enable in Extension Options)
And its simple - just click here go to the Chrome Web Store and install the Extension
Chrome uses the ThinForms Java Host App to run your Forms Applications - all required files will be downloaded for you by the Chrome Extension. Alternatively, you can directly install the ThinForms Java Host application from here
Ref:
1.TalkingByte
https://www.hello.talkingbyte.com/
問題: PO建立時未帶預設稅別, 需手動補入(SO也有相同狀況)
原因: Tax設定不完整
解法: 在Tax權限中補設定
Defaults and Controls
-> Application Tax Options
設定Defaulting Order 1及Defaulting Order 2
問題: 在Oracle support搜尋文章時, knowledge base區無資料
狀況:
1.搜尋文章時, knowledge base區無資料, 但後面的四區有
2.曾出現訊息: "Note: you have not selected the Knowledge Base as a source"訊息
原因: 不明, 其他人無相同狀況
解法: 把Knowledge Base的綠勾按掉再重選... Orz...
Ref:
1.Oracle E-Business Suite Performance Guide (Doc ID 1672174.1)
問題: cancel request時出現could not lock request訊息, 無法成功cancel
狀況:
1.以XML publisher寫的程式執行過久, 要cancel時出現訊息could not lock request
2.程式超過3小時後complete warning
3.log有訊息:
The Output Post-processor is running but has not picked up this request. No further attempts will be made to post-process this request, and the request will be marked with Warning status. Setting the profile option Concurrent: OPP Response Timeout to a higher value may be necessary.
解決方式:
1.Restart Output Post Processor
2.在DB中找出並cancel異常程式對應的session
3.手動更新異常concurrent request的狀態
Ref:
1.Concurrent request receiving Could Not Lock Request when canceling
https://doyensys.com/blogs/concurrent-request-receiving-could-not-lock-request-when-canceling/
2.could not lock request concurrent manager
https://mohamedazar.com/2019/05/16/could-not-lock-request-concurrent-manager/
Oracle EBS快捷鍵, 很少在用, 只作參考.
Ref:
1.oracle-ebs-shortcut-keys
http://oracleebsfundamentals.blogspot.com/2012/11/oracle-ebs-shortcut-keys.html
問題: AR transaction錯誤拋轉, 在GL要作reverse
作法:
Setup->Sources
Source:Receivables
Source Key:Receivables
Freeze Journals: 先disable讓User可以reverse
問題: 跨concurrent manager之程式設定互斥是否有效? 是否仍會依priority順序執行?
先說結論:
1.互斥仍有效
2.不一定會依priority執行
測試案例:
CM1: concurrent manager 1 , 可同時執行20支程式
CM2: concurrent manager 2 , 可同時執行5支程式
PA: program A , priority 5 , 執行於CM1
PB: program B , priority 60 , 執行於CM2
PA與PB互斥
先submit PB 600支, 已開始running後, 再submit PA.
結果PB一個接一個執行, 持續running, 而PA則是一直pending .
問題: 當數千筆的PO Output for Communication排隊執行, 會嚴重拖慢大程式的進度, 在已切不同concurrent manager執行的狀況下, 設定互斥並無預期中的效果, 最後想到只有hold一途.
試過之後可行, 作法如下.
hold_flag : Y/N
PO Output for Communication
concurrent_program_id : 45071
phase_code : R (Pending)
status_code: R (Normal)
--find requests
select * from fnd_concurrent_requests
where concurrent_program_id = 45071
and request_date> trunc(sysdate) -2
and phase_code = 'P' --(Pending)
and status_code = 'I' --(Normal)
--apply hold
update fnd_concurrent_requests
set hold_flag = 'Y'
where concurrent_program_id = 45071
and request_date> trunc(sysdate) -2
and phase_code = 'P' --(Pending)
and status_code = 'I' --(Normal)
--remove hold
update fnd_concurrent_requests
set hold_flag = 'N'
where concurrent_program_id = 45071
and request_date> trunc(sysdate) -2
and phase_code = 'P' --(Pending)
and status_code = 'I' --(Normal)
and hold_flag = 'Y'
