隔了幾年再看, 有人提了另一個寫法, 可參考:
--
SET SERVEROUTPUT ON;
DECLARE
b_isvalid BOOLEAN;
BEGIN
b_isvalid :=
REGEXP_LIKE ('anyaddress@xyz123.com',
'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$');
IF b_isvalid
THEN
DBMS_OUTPUT.put_line ('It is a valid email address.');
ELSE
DBMS_OUTPUT.put_line ('It is Not a valid email address.');
END IF;
END;
/
Ref:
1.Validate Email Using PL/SQL Program
How to Validate Email Address Using PL/SQL? - foxinfotech.in
問題: 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'
以前的測試没成功, 原因就在於BPA勾選了Global欄位, 造成Cumulative Pricing欄位無法勾選.
果然有實際案例就單純多了.
Ref:
1.Cumulative Pricing in Blanket Purchase Agreement
http://appselangovan.blogspot.com/2015/07/bpa-blanket-purchase-agreement-with.html
2.Blanket Purchase Agreement
http://chenchioracleebsappsr12.blogspot.com/p/blog-page_5.html
http://chenchioracleebsappsr12.blogspot.com/p/bpa-cont1.html
http://chenchioracleebsappsr12.blogspot.com/p/create-blanket-release-against-bpa-3004.html
簡體中文環境Excel中繁體中文亂碼問題
在簡體中文環境要避免繁體中文亂碼出現. 在Excel的語言選項中要先增加 中文(台湾) , 並啟用設為默認(圖1).
在語言設定中僅有簡體中文時, 就會出現亂碼.
圖1.
擷取Oracle EBS R12中Legal Entity, Business group資料
Query to fetch Business group Details - 1
SELECT business_group_id,
organization_id,
NAME,
date_from,
date_to,
internal_address_line,
location_id,
comments,
default_start_time,
default_end_time,
working_hours,
frequency,
short_name,
method_of_generation_emp_num,
method_of_generation_apl_num,
grade_structure,
people_group_structure,
job_structure,
cost_allocation_structure,
position_structure,
legislation_code,
currency_code,
security_group_id,
enabled_flag,
competence_structure,
method_of_generation_cwk_num
FROM per_business_groups;
Query to fetch Business group Details - 2
SELECT o.organization_id,
o.organization_id,
otl.NAME,
o.date_from,
o.date_to,
o.internal_address_line,
o.location_id,
o.comments
FROM hr_all_organization_units o,
hr_all_organization_units_tl otl,
hr_organization_information o2,
hr_organization_information o3,
hr_organization_information o4
WHERE o.organization_id = otl.organization_id
AND o.organization_id = o2.organization_id(+)
AND o.organization_id = o3.organization_id
AND o.organization_id = o4.organization_id
AND o3.org_information_context = 'Business Group Information'
AND o2.org_information_context(+) = 'Work Day Information'
AND o4.org_information_context = 'CLASS'
AND o4.org_information1 = 'HR_BG'
AND o4.org_information2 = 'Y';
Query to fetch Legal Entity Details
SELECT xle_firstpty.NAME,
xle_firstpty.activity_code,
xle_firstpty.sub_activity_code,
xle_firstpty.registration_number,
xle_firstpty.effective_from,
xle_firstpty.location_id,
xle_firstpty.address_line_1,
xle_firstpty.address_line_2,
xle_firstpty.address_line_3,
xle_firstpty.town_or_city,
xle_firstpty.region_1,
xle_firstpty.region_2,
xle_firstpty.region_3,
xle_firstpty.postal_code,
xle_firstpty.country,
xle_firstpty.address_style,
xle_cont.contact_name,
xle_cont.contact_legal_id,
xle_cont.title,
xle_cont.job_title,
xle_cont.role
FROM xle_firstparty_information_v xle_firstpty,
xle_legal_contacts_v xle_cont
WHERE 1 = 1
AND XLE_FIRSTPTY.LEGAL_ENTITY_ID = C_REP_ENTITY_ID
AND xle_firstpty.legal_entity_id = xle_cont.entity_id(+);
Query to fetch legal entity details along with OU details
SELECT DISTINCT hrl.country,
hroutl_bg.NAME bg,
hroutl_bg.organization_id,
lep.legal_entity_id,
lep.NAME legal_entity,
hroutl_ou.NAME ou_name,
hroutl_ou.organization_id org_id,
hrl.location_id,
hrl.location_code,
glev.flex_segment_value
FROM xle_entity_profiles lep,
xle_registrations reg,
hr_locations_all hrl,
hz_parties hzp,
fnd_territories_vl ter,
hr_operating_units hro,
hr_all_organization_units_tl hroutl_bg,
hr_all_organization_units_tl hroutl_ou,
hr_organization_units gloperatingunitseo,
gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag = 'Y'
AND lep.party_id = hzp.party_id
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND hrl.location_id = reg.location_id
AND reg.identifying_flag = 'Y'
AND ter.territory_code = hrl.country
AND lep.legal_entity_id = hro.default_legal_context_id
AND gloperatingunitseo.organization_id = hro.organization_id
AND hroutl_bg.organization_id = hro.business_group_id
AND hroutl_ou.organization_id = hro.organization_id
AND glev.legal_entity_id = lep.legal_entity_id;
Ref:
1.Query to fetch Legal Entity, Business group details in Oracle Apps R12 - per_business_groups,xle_firstparty_information_v
http://www.shareoracleapps.com/2014/08/query-to-fetch-legal-entity-business-groups-ou-r12-oracle-apps.html
Ref: 免費雲端Jupyter Notebook報你知 - 台灣人工智慧學校 (aiacademy.tw)
文/台灣人工智慧學校AI工程師
前言
俗話說：「工欲善其事，必先利其器。」有好的工具能幫助你在機器學習的路途上少走崎嶇的路。我經常遇到 AIA 技術班的學員提問：「結業後學校內的 Jupyter Notebook 是不是不能再使用了？那我們之後該怎麼練習與實作呢？」這邊有兩種方法 (1)直接在本機端安裝環境 (2)使用雲端資源。第一種方法可以參考此篇文章 Suit up! 自己動手建置AI環境而這篇文章主要說明第二種方法。我將舉例四個網路上免費的資源，可以直接利用雲端的方式執行 Jupyter Notebook。除了基本CPU，有些平台也提供了免費GPU資源，讓大家操好操滿！
今天會介紹我的四個口袋名單。放心！每一個服務都是免費的～ 如果平時在外面沒隨身攜帶自己的筆電，但想展示你的程式，可以透過以下資源來存取你的Code，超方便！
Kaggle 是一個數據建模和數據分析競賽平台，你可以在上面不定時看到許多競賽，也可以看到許多公司將他們未解決的資料集公開分享出來，讓大家一起來解決。除此之外 Kaggle 使用者也可以公開分享自己的 kernel 讓大家參考你的程式，幸運的話你可以在這裡挖到寶。
註冊成功後會看到下方畫面（隨著時間，頁面可能會稍微長得不一樣），點選左邊欄 Notebooks 就可以看到自己的程式及他人分享的程式。點選右上角 New Notebook 就可建立一個新專案，這裡你可以選擇 R 語言或 Python。
新建一個新的 NoteBook
這邊我提供一個簡單線性回歸的範例，各位可以直進入此連結 Copy and Edit 到自己的帳號中。
我們都簡稱 Colab。這個平台是 Google 所維護的服務，他會與你的 Google Drive 連動，這也意味著你得程式碼將會備份在雲端硬碟中。基本上你只要打開雲端硬碟在安裝 Chrome 的相關套件就可以使用了～
這裡我也提供一個範例程式，各位可以跟著下面步驟並Fork到自己的雲端硬碟中。
若是初次使用的讀者，此時若沒看到 Google Colaboratory 的選項表示你的電腦尚未安裝插件。接著點選更多應用程式來下載。
之後會彈出一個視窗，在搜尋欄打上 colab 就會跳出相關插件。點進去後點擊安裝來下載應用(PS.以上步驟需在Google Chrome瀏覽器下完成)。
安裝完成後點選使用「Google Colaboratory」開啟。開啟後就會看到一個範例程式，因為這支程式是儲存在我的雲端硬碟中，若你想執行的話點選 File → Save a copy in Drive 將程式複製到你的雲端硬碟中。
如果你的專案需要使用到GPU的話可以點選 Runtime → Change runtime type。點擊進去設定後即可使用 GPU 或 TPU。
微軟的 Azure Notebooks 目前還處於開發測試階段，或許還不是很穩，但我們還是可以觀望。他的優點是能夠直接跟 GitHub 的專案連動，版本控制對工程師可說是非常重要的一環，大家應該沒忘記微軟早在 2018 年宣布收購 GitHub 的消息吧！
各位可以參考這個範例，打開後應該是 tree 結構的 Notebook，如果想換成下圖的編輯環境，可以在網址後面的 /tree 改成 /lab 就會跟下圖一樣了。由於目前還是測試階段，僅提供各位一個雲端服務的參考。日後發布正式版有多功能會再詳細介紹他。
mybinder 跟微軟的 Azure 有點異曲同工之妙。mybinder 主要可以貼上 GitHub 的專案網址，接著透過 Docker 建立一個虛擬映像，幫你在平台上建立一個虛擬環境。因為是免費的服務，所以許多套件要自行安裝，例如sklearn、OpenCV、TensorFlow……等，這個服務比較適合在開發一個新的演算法並放在 GitHub 上，且需要提供一個測試環境給使用者做 live demo。個人不推薦在上面直接開發。
以上四個免費雲平台都可以讓開發者在網路上自由開發，當然免費資源多少有些不便，以及使用上的限制。不過，有效地善用網路資源的確可以減少我們在開發上的負擔！
1.python3爬虫给seleium库所遇到的问题
https://blog.csdn.net/Omann/article/details/105074783
2.selenium.common.exceptions.WebDriverException: Message: Service geckodriver unexpectedly exited. Status code was: -11 - How to fix?
https://stackoverflow.com/questions/53989001/selenium-common-exceptions-webdriverexception-message-service-geckodriver-unex
--
Traceback (most recent call last):
File "C:\Users\UT\AppData\Local\Programs\Python\Python37-32\lib\site-packages\selenium\webdriver\common\service.py", line 76, in start
stdin=PIPE)
File "C:\Users\UT\AppData\Local\Programs\Python\Python37-32\lib\subprocess.py", line 775, in __init__
restore_signals, start_new_session)
File "C:\Users\UT\AppData\Local\Programs\Python\Python37-32\lib\subprocess.py", line 1178, in _execute_child
startupinfo)
FileNotFoundError: [WinError 2] 系統找不到指定的檔案。
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "<pyshell#1>", line 1, in <module>
browser = webdriver.Firefox()
File "C:\Users\UT\AppData\Local\Programs\Python\Python37-32\lib\site-packages\selenium\webdriver\firefox\webdriver.py", line 164, in __init__
self.service.start()
File "C:\Users\UT\AppData\Local\Programs\Python\Python37-32\lib\site-packages\selenium\webdriver\common\service.py", line 83, in start
os.path.basename(self.path), self.start_error_message)
selenium.common.exceptions.WebDriverException: Message: 'geckodriver' executable needs to be in PATH.
問題: 料號建立後, UOM是否可修改?
依Oracle文件所列, 12.2.9之前不行, 在12.2.9及之後的版本才有新功能.
變通作法1:
If item is newly created and NOT yet used in any transactions, then you can delete item using delete group functionality (Inventory > Items > Delete Items) and recreate the item.
Navigate to Inventory
Delete Items
Enter a Delete Group
Pick a Delete Group Type
Enter the Description
Enter Items to delete
Click Save
變通作法2:
Inactivate the item with bad UOM in future and create/replace it with new item with required UOM definition.
Navigate to Inventory
Items
Query the Item with the wrong UOM
Change the Item Status to Inactive
Click Save
變通作法3:
If the item has been transacted and has no outstanding orders, purchase orders or WIP jobs you can do the following if approved by your management.
Rename the existing item 'XYZ' to 'XYZ-Wrong UOM' and save the item (Here Naming is just an example, you can use your own nomenclature).
Navigate to Sysadmin
Set the profile INV: Updateable Item name to"Yes"
Navigate to Inventory Responsibility > Items> Master Items
Query the item number which is to be modified
The item name/number can be modified
Reset the value once required changes are done
Ref:
1.How to Update and or Change Item Unit of Measure (UOM) (Doc ID 1360479.1)
2.Item Setup FAQ (Doc ID 111742.1)
3.INVIDITM - How to Rename Inventory Items (Doc ID 255560.1)
因倉庫重複作業, 造成return兩次, 第二次分錄未產生, 且rcv_transactions已有交易, mtl_material_transactions無交易(因第二次已無庫存可退, 導致卡帳)
詢問oracle support後, 有提供datafix補rcv_transactions的分錄(df_missing_rrsl_r12_Org.sql).
1.需確認以下兩個table無對應資料(其中一個有值則無法重新產生分錄):
select *
from RCV_RECEIVING_SUB_LEDGER
where RCV_TRANSACTION_ID= 93197645 --Return to Supplier
select *
from rcv_accounting_events
where rcv_transaction_id = 93197645 --Return to Supplier
2.刪除rcv_transactions_interface return卡帳資料
3.待倉庫部門退料後，重吃MTL_MATERIAL_TRANSACTIONS_TEMP卡帳資料
4.執行datafix產生return分錄
-- df_missing_rrsl_r12_Org.sql
set serveroutput on size 1000000
set linesize 1500
set pagesize 10000
set verify off
SET TRIMSPOOL ON
SET TRIMOUT ON
spool cr_receipt_accounting_log.lst
DECLARE
L_RETURN_STATUS VARCHAR2(1);
L_MSG_DATA VARCHAR2(2000);
L_MSG_COUNT NUMBER;
L_PARENT_TRX_ID NUMBER;
l_stmt_num NUMBER;
L_PO_HEADER_ID NUMBER;
L_CONSIGNED_FLAG VARCHAR2(2);
CURSOR candidate_txns IS
SELECT T1.*
FROM RCV_TRANSACTIONS T1,
MTL_PARAMETERS mp
WHERE t1.organization_id = mp.ORGANIZATION_ID
AND NVL(mp.process_enabled_flag, 'N') <> 'Y'
AND t1.transaction_type in ('RECEIVE','RETURN TO VENDOR','CORRECT')
AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'INVENTORY')
AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'EXPENSE')
AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'SHOP FLOOR')
AND EXISTS
(SELECT 1
FROM PO_LINE_LOCATIONS_ALL poll
WHERE ACCRUE_ON_RECEIPT_FLAG='Y'
AND POLL.LINE_LOCATION_ID =T1.PO_LINE_LOCATION_ID)
AND NVL(COMMENTS,'!') NOT IN ('OPM RECEIPT','OPM Receipt Correction')
AND NOT EXISTS
(SELECT 1
FROM RCV_RECEIVING_SUB_LEDGER T2
WHERE T2.RCV_TRANSACTION_ID=T1.TRANSACTION_ID)
AND EXISTS ( SELECT 'GL and PO has OPEN PERIOD for the transaction_date'
FROM gl_period_statuses GL_PER,
gl_period_statuses PO_PER,
financials_system_params_all FSP
WHERE GL_PER.application_id = 101
AND PO_PER.application_id = 201
AND PO_PER.set_of_books_id = FSP.set_of_books_id
AND trunc(PO_PER.start_date) <= t1.transaction_date
AND trunc(PO_PER.end_date)+0.99999 >= t1.transaction_date
AND PO_PER.closing_status = 'O'
AND PO_PER.period_name = GL_PER.period_name
AND GL_PER.set_of_books_id = FSP.set_of_books_id
AND trunc(GL_PER.start_date) <= t1.transaction_date
AND trunc(GL_PER.end_date)+0.99999 >= t1.transaction_date
AND GL_PER.closing_status in ('O', 'F')
)
UNION ALL
SELECT T1.*
FROM RCV_TRANSACTIONS T1,
po_distributions_all POD,
MTL_PARAMETERS mp
WHERE t1.organization_id = mp.ORGANIZATION_ID
AND NVL(mp.process_enabled_flag, 'N') <> 'Y'
AND T1.transaction_type in ('DELIVER','RETURN TO RECEIVING','CORRECT')
AND NOT (T1.transaction_type = 'CORRECT' AND T1.destination_type_code = 'RECEIVING')
AND T1.po_distribution_id = POD.po_distribution_id
AND pod.accrue_on_receipt_flag = 'Y'
AND pod.destination_type_code = 'EXPENSE'
AND EXISTS
(SELECT 1
FROM PO_LINE_LOCATIONS_ALL poll
WHERE ACCRUE_ON_RECEIPT_FLAG='Y'
AND POLL.LINE_LOCATION_ID =T1.PO_LINE_LOCATION_ID)
AND NVL(COMMENTS,'!') NOT IN ('OPM RECEIPT','OPM Receipt Correction')
AND NOT EXISTS
(SELECT 1
FROM RCV_RECEIVING_SUB_LEDGER T2
WHERE T2.RCV_TRANSACTION_ID=T1.TRANSACTION_ID)
AND EXISTS ( SELECT 'GL and PO has OPEN PERIOD for the transaction_date'
FROM gl_period_statuses GL_PER,
gl_period_statuses PO_PER,
financials_system_params_all FSP
WHERE GL_PER.application_id = 101
AND PO_PER.application_id = 201
AND PO_PER.set_of_books_id = FSP.set_of_books_id
AND trunc(PO_PER.start_date) <= t1.transaction_date
AND trunc(PO_PER.end_date)+0.99999 >= t1.transaction_date
AND PO_PER.closing_status = 'O'
AND PO_PER.period_name = GL_PER.period_name
AND GL_PER.set_of_books_id = FSP.set_of_books_id
AND trunc(GL_PER.start_date) <= t1.transaction_date
AND trunc(GL_PER.end_date)+0.99999 >= t1.transaction_date
AND GL_PER.closing_status in ('O', 'F')
);
BEGIN
mo_global.init('PO');
mo_global.set_policy_context('S',&operating_unit_id);
dbms_output.put_line('mo global set successfully');
l_stmt_num := 10;
FOR c_rec IN candidate_txns LOOP
l_stmt_num := 20;
dbms_output.put_line('Receipt_TXN : '||c_rec.transaction_id);
dbms_output.put_line('PO Header ID : '||c_rec.po_header_id);
SELECT NVL(consigned_consumption_flag, 'N')
INTO l_consigned_flag
FROM PO_HEADERS_all
WHERE po_header_id = c_rec.po_header_id;
l_stmt_num := 30;
/*dbms_output.put_line('Consigned PO : '||l_consigned_flag);*/
IF (l_consigned_flag = 'N') THEN
l_stmt_num := 40;
RCV_AccrualAccounting_GRP.Create_AccountingEvents(
p_api_version => 1.0,
p_source_type => 'RECEIVING',
p_rcv_transaction_id => c_rec.transaction_id,
p_direct_delivery_flag=> null,
p_gl_group_id => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
l_stmt_num := 50;
IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
l_stmt_num := 60;
dbms_output.put_line('Error ocurred while creating Accounting for TXN: '|| TO_CHAR(c_rec.transaction_id));
RAISE FND_API.g_exc_unexpected_error ;
ELSE
l_stmt_num := 70;
dbms_output.put_line('Accounting created sucessfully for Txn : '||TO_CHAR(c_rec.transaction_id));
END IF;
END IF; /* consigned */
END LOOP;
l_stmt_num := 80;
COMMIT;
EXCEPTION
WHEN FND_API.g_exc_unexpected_error THEN
dbms_output.put_line('l_msg_count : '||l_msg_count);
dbms_output.put_line('l_msg_data : '||l_msg_data);
Rollback;
WHEN OTHERS THEN
dbms_output.put_line ( 'some error has occurred at stmt no '||l_stmt_num||' '||SQLCODE||' '||SQLERRM);
Rollback;
END;
/
spool off
在Windows 10開啟Telnet功能
[開始]
-> 設定
-> 應用程式
-> 應用程式與功能
-> 相關設定
-> 程式和功能
-> 開啟或關閉Windows功能
這也藏太深了...
問題: 以API更改concurrent request hold狀態
解法: FND_AMP_PRIVATE.TOGGLE_REQUEST_HOLD
Ref:
1.Concurrent Processing : Is There An API To Put Scheduled Concurrent Requests On Hold ? (Doc ID 2024621.1)
