狗爸的世界

Oracle ERP R12. - 內建SQL Package 可以得到會計節段說明資料

2016-12-26T16:28:00.000-08:00

單一節段的代碼說明

gl_flexfields_pkg.get_description_sql(chart_of_accounts_id,--- chart of account id

1,----- Position of segment

segment1 ---- Segment value

);

整段會計科目的說明

gl_flexfields_pkg.get_concat_description(chart_of_accounts_id,

code_combination_id

);

Oracle ERP R12. 無法複製貼上

2015-03-12T18:49:00.001-07:00

Oracle ERP R12. 中文環境下Export 亂碼之狀況排除

2014-02-26T17:12:00.003-08:00

說明 :
Oracle ERP Server之字元集與Client的字元集不符,導至Export產出之excel於Client開啟時,中文部分會呈現亂碼

解法 :
設定 FND: NATIVE CLIENT ENCODING 個人的Profile 為Client的字元集

例 : Client的字元集為繁體中文 ZHT16BIG5 , 設定如下

Oracle ERP R12. WF_NOTIFICATION.SEND (通知訊息功能)

2013-10-06T17:50:00.000-07:00

Sample Code :

PROCEDURE SEND_WF_NOTIFICATION(I_TO IN VARCHAR2,
I_SUBJECT IN VARCHAR2,
I_TEXT IN VARCHAR2)
IS
/*
I_TO : Oracle EBS帳號
I_SUBJECT : 要顯示在Worklist的主旨
I_TEXT : 內容
*/
V_NOTIFICATION_ID NUMBER;
--
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
V_NOTIFICATION_ID := WF_NOTIFICATION.SEND(ROLE => I_TO,
MSG_TYPE => 'CS_MSGS',
MSG_NAME => 'FYI_MESSAGE',
PRIORITY => 1);
WF_NOTIFICATION.SETATTRTEXT(NID => V_NOTIFICATION_ID,
ANAME => 'OBJECT_TYPE',
AVALUE => I_SUBJECT);
WF_NOTIFICATION.SETATTRTEXT(NID => V_NOTIFICATION_ID,
ANAME => 'MESSAGE_TEXT',
AVALUE => I_TEXT); --MESSAGE BODY
COMMIT;
END;

Oracle ERP R12. create locator using API (INV_LOC_WMS_PUB.CREATE_LOCATOR) - 建立Locator的API

2013-09-24T01:57:00.001-07:00

若有需要大量新增Locator 則其有二種方式 :

1. Data Load

2. 呼叫 INV_LOC_WMS_PUB.CREATE_LOCATOR 該API
用法 :
FND_GLOBAL.APPS_INITIALIZE(1114,
51007,
401);
FND_MSG_PUB.Initialize;

INV_LOC_WMS_PUB.CREATE_LOCATOR(X_RETURN_STATUS => :X_RETURN_STATUS,
X_MSG_COUNT => :X_MSG_COUNT,
X_MSG_DATA => :X_MSG_DATA,
X_INVENTORY_LOCATION_ID => :X_INVENTORY_LOCATION_ID,
X_LOCATOR_EXISTS => :X_LOCATOR_EXISTS,
P_ORGANIZATION_ID => :P_ORGANIZATION_ID,
P_ORGANIZATION_CODE => :P_ORGANIZATION_CODE,
P_CONCATENATED_SEGMENTS => 儲位,
P_DESCRIPTION => 儲位描述,
P_INVENTORY_LOCATION_TYPE => 3,
P_PICKING_ORDER => :P_PICKING_ORDER,
P_LOCATION_MAXIMUM_UNITS => :P_LOCATION_MAXIMUM_UNITS,
P_SUBINVENTORY_CODE => 倉別,
P_LOCATION_WEIGHT_UOM_CODE => :P_LOCATION_WEIGHT_UOM_CODE,
P_MAX_WEIGHT => :P_MAX_WEIGHT,
P_VOLUME_UOM_CODE => :P_VOLUME_UOM_CODE,
P_MAX_CUBIC_AREA => :P_MAX_CUBIC_AREA,
P_X_COORDINATE => :P_X_COORDINATE,
P_Y_COORDINATE => :P_Y_COORDINATE,
P_Z_COORDINATE => :P_Z_COORDINATE,
P_PHYSICAL_LOCATION_ID => :P_PHYSICAL_LOCATION_ID,
P_PICK_UOM_CODE => :P_PICK_UOM_CODE,
P_DIMENSION_UOM_CODE => :P_DIMENSION_UOM_CODE,
P_LENGTH => :P_LENGTH,
P_WIDTH => :P_WIDTH,
P_HEIGHT => :P_HEIGHT,
P_STATUS_ID =>1,
P_DROPPING_ORDER => :P_DROPPING_ORDER,
P_ATTRIBUTE_CATEGORY => :P_ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1 => :P_ATTRIBUTE1,
P_ATTRIBUTE2 => :P_ATTRIBUTE2,
P_ATTRIBUTE3 => :P_ATTRIBUTE3,
P_ATTRIBUTE4 => :P_ATTRIBUTE4,
P_ATTRIBUTE5 => :P_ATTRIBUTE5,
P_ATTRIBUTE6 => :P_ATTRIBUTE6,
P_ATTRIBUTE7 => :P_ATTRIBUTE7,
P_ATTRIBUTE8 => :P_ATTRIBUTE8,
P_ATTRIBUTE9 => :P_ATTRIBUTE9,
P_ATTRIBUTE10 => :P_ATTRIBUTE10,
P_ATTRIBUTE11 => :P_ATTRIBUTE11,
P_ATTRIBUTE12 => :P_ATTRIBUTE12,
P_ATTRIBUTE13 => :P_ATTRIBUTE13,
P_ATTRIBUTE14 => :P_ATTRIBUTE14,
P_ATTRIBUTE15 => :P_ATTRIBUTE15,
P_ALIAS => :P_ALIAS);

Oracle ERP R12. How to select oe_transaction_type_syn ?

2013-09-11T17:41:00.003-07:00

Before execute SQL :

declare
begin
MO_GLOBAL.SET_POLICY_CONTEXT('S',81); --Set Org_ID
end;

Then

select * from oe_transaction_types_syn;

Oracle ERP R12. Profile - OE: AutoPrice (自動更新Unit Selling Price時機設定)

2013-07-31T18:45:00.000-07:00

經同仁反應,訂單的單價常會不明原因自動被更改掉,而user都不知道.
經查發現原因如下:

因為訂單會指定price list ,故只要訂單項次有調整任何一欄位(unit selling price欄除外),則系統會再自動從price list中取回目前最新的設定.
而該行為的主要原因是控制在一Profile => OE: AutoPrice , 該Profile的值預設為空值所導致的關係.

以下是該Profile設定的說明 : (本人測試得來的結果)
空值 => 不論是Entered 或是 Booked狀態皆會自動取回最新的price list值
Entry => 只有在訂單狀態為Entered 時才會自動取回最新的price list值
Booking => 不論狀態如何皆不會自動取回最新的price list值.

Ps :
以上純為本人測試之結果,若讀者測試後有任何心得請不吝指教,以該本人更新正確之資訊~

<<2013/08/04 不具名的網友補充以下說明>>
通常業務流程而言, 應該定義為N (calculate_price_flag) , 避免訂單預定後仍可能因為手動或API變更而改變售價
*reference Doc ID 134326.1
· If it’s set to ‘Y’, both pricing and freight charge calculations are done.
· If it’s set to ‘P’, only the freight charge calculation is done.
'P' also indicates that modifiers assigned to a pricing phase
where freeze override is enabled could also be applied.
· If it’s set to ‘N’, neither pricing nor freight charge is calculated.

Oracle ERP R12. 如何透過Personalize功能呼叫另一form來回傳值. Get another form's result value (user personalize) .

2013-07-30T19:40:00.000-07:00

狀況假設 :

A-Form(Rcv_transaction) 要呼叫另一客制form (B-Form),由B-Form運算後產生一結果回轉A-Form

Step 1. 建立一function用來取回B-Form的result

例 :

create or replace function HPX_WEN_TEST_F(I_KEY VARCHAR2) return varchar2 is

T_Result VARCHAR2(100);

T_EXIT VARCHAR2(1) := 'N';

T_EXIT_TIME DATE := SYSDATE+5/(24*60);

begin

WHILE T_EXIT = 'N' LOOP

SELECT DECODE(COUNT(*),

0,'N',

'Y'),

MAX(HC.VALUE_2)

INTO T_EXIT,

T_RESULT

FROM HPX_CONFIG_T HC –假設該table為result

WHERE HC.KEY_ID = 'TEST'

AND HC.VALUE_1 = I_KEY; --Key值,用來確認所抓取的資料無誤

IF T_EXIT_TIME <= SYSDATE THEN

T_EXIT := 'Y'; --逾時離開以避免無窮迴圈

END IF;

END LOOP;

return(T_Result);

end HPX_WEN_TEST_F;

Step 2. 在A-Form的WHEN-NEW-FORM-INSTANCE 加入一LOCAL VARIABLE KEY的宣告,

及MENU BUTTON的宣告.

例 :

Step 3. 設定上述MENU BUTTON的功能行為.

例 :

埋入Debug用的訊息

將游標固定至等定ITEM

<重要>該步驟是為了A-Form重新取得控制取時只會觸發 WHEN-NEW-ITEM-INSTANCE 所以要知道是那一ITEM會被觸發

呼叫B-Form

注意 : 這裡的HPXINVF004是隨便設的,並不會真的寫入Result table ,這裡只是要模擬說明而已,假設會寫入Step 1的Result table

Step 4. 設定A-Form重新取得控制權後的行為動作.並由判斷KEY是否有值來確認是否要進入該程序

注意 : 由B-Form返回A-Form時只會觸發 WHEN-NEW-ITEM-INSTANCE , 故上步驟會先GO_ITEM.

呼叫Step 1. 的Function來取回Result Table的值

清除Key值,以避免誤動作

Oracle ERP R12. Unassign or delete large number of items .

2013-06-19T19:07:00.001-07:00

There is an option on the Tools menu of the Delete Item form that opens the Item Search screen.
There you can search on a number of criteria, including status, and the search results are populated in your Delete Group.
You can review the list, and delete unwanted entries (or add new ones, if necessary).

Then run the Delete process.

Reference from Metalink : ID 1347893.1

Oracle ERP R12. 使用者忘記密碼如何自行變更密碼

2013-03-25T23:33:00.005-07:00

分享同事提供的自行變更密碼做法:

1. 在登入畫面下方,點選”Login Assistance”

2. 輸入帳號,點選"Forgot Password" , 系統就會發送密碼重設的mail至該帳號上所定義的mail

3. 待收到mail後,點選mail內"Reset your password"的超連結,即可進入密碼重設的畫面.

狗爸的世界: Oracle ERP R12. 使用interface批量變更品號的operation code ....

2013-03-13T20:06:00.005-07:00

狗爸的世界: Oracle ERP R12. 使用interface批量變更品號的operation code ....: Sample code : DECLARE -- T_OLD_OP VARCHAR2(10) := '2T02'; --**** old operation code T_NEW_OP VARCHAR2(10) := '2S02...

狗爸的世界: Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privi...

2013-03-13T20:06:00.003-07:00

狗爸的世界: Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privi...: 以下Sample是當有新人時,可以快速複制user a 的權限至user b的作法 ~ My Sample code : (Package) /*PACKAGE HEADER*/ CREATE OR REPLACE PACKAGE XX_FND_USER_RESP_P...

狗爸的世界: Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to...

2013-03-13T20:06:00.001-07:00

狗爸的世界: Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to...: 以下是使用FND_USER_PKG.CREATEUSER 建立使用者的範例程式碼 Sample code : FND_USER_PKG.CREATEUSER(X_USER_NAME => :USER_NAME , --USER ACCOUNT ...

Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to use FND_USER_PKG.CREATEUSER ?

2013-03-12T19:15:00.001-07:00

以下是使用FND_USER_PKG.CREATEUSER 建立使用者的範例程式碼

Sample code :
FND_USER_PKG.CREATEUSER(X_USER_NAME => :USER_NAME , --USER ACCOUNT
X_OWNER => 'SEED',
X_UNENCRYPTED_PASSWORD => 'ABC234', --Default password
X_EMPLOYEE_ID => :EMPLOYEE_ID, --Mapping to employee id
X_EMAIL_ADDRESS => :EMAIL_ADDRESS); --user email

PS :
X_UNENCRYPTED_PASSWORD 需注意是否有啟用密碼的複雜度檢查，若有則必需符合其複雜度檢查規定

Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privileges from one user to another user ?

2013-03-12T19:01:00.000-07:00

以下Sample是當有新人時,可以快速複制user a 的權限至user b的作法 ~

My Sample code : (Package)

/*PACKAGE HEADER*/

CREATE OR REPLACE PACKAGE XX_FND_USER_RESP_PKG
IS
PROCEDURE XX_FND_COPY_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER,
I_MODE IN NUMBER DEFAULT 0);
--
PROCEDURE COPY_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER);
--
PROCEDURE DISABLE_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER);
--
PROCEDURE ENABLE_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER);
--
PROCEDURE DISABLE_USER_ALL_RESP(I_USER_ID IN NUMBER);
END XX_FND_USER_RESP_PKG;

/*PACKAGE BODY*/

CREATE OR REPLACE PACKAGE BODY XX_FND_USER_RESP_PKG
IS
PROCEDURE XX_FND_COPY_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER,
I_MODE IN NUMBER DEFAULT 0)
IS
/*
When Who What
===============================
20130313 WenShen Created for 複製某人權限至另一人

說明 :
1. I_MODE : 權限複製規則
0 : 只新增短少的權限,但不將即有卻失效的權限予以復權
1 : 新增短少的權限,且將即有卻失效的權限予以復權(且不影其它即有的權限)
2 : 完整複製,將原有權限全數失效後再執行I_MODE 2
*/
BEGIN
--
IF I_MODE = 0 THEN --只新增短少的權限,但不將即有卻失效的權限予以復權
--新增短少的權限
COPY_USER_RESP(I_FROM_USER_ID,
I_TO_USER_ID);
ELSIF I_MODE = 1 THEN --新增短少的權限,且將即有卻失效的權限予以復權
--Step 1. 新增短少的權限
COPY_USER_RESP(I_FROM_USER_ID,
I_TO_USER_ID);

--Step 2. 依來源USER權限設定將目標USER的權限的予以復權
ENABLE_USER_RESP(I_FROM_USER_ID,
I_TO_USER_ID);
ELSIF I_MODE = 2 THEN --完整複製,將原有權限全數失效後再執行I_MODE 2
--Step 1. 將所有權限予以停權
DISABLE_USER_ALL_RESP(I_TO_USER_ID);

--Step 2. 新增短少的權限
COPY_USER_RESP(I_FROM_USER_ID,
I_TO_USER_ID);

--Step 3. 依來源USER權限設定將目標USER的權限的予以復權
ENABLE_USER_RESP(I_FROM_USER_ID,
I_TO_USER_ID);
ELSE
NULL;
END IF;
END;
--
PROCEDURE COPY_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER)
IS
T_AP_ID NUMBER;
T_RESP_ID NUMBER;
--來源者權限表
CURSOR C_F_RESP
IS
SELECT FR.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS_DIRECT FUR
JOIN FND_RESPONSIBILITY FR
ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
AND (FR.END_DATE IS NULL OR FR.END_DATE >= SYSDATE)
WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
AND NOT EXISTS (SELECT 'X'
FROM FND_USER_RESP_GROUPS_DIRECT FUR2
WHERE FUR2.USER_ID = I_TO_USER_ID
AND FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID) --已存在不重覆轉
AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
BEGIN
FOR C1 IN C_F_RESP LOOP
--取得RESP資訊
SELECT FR.RESPONSIBILITY_ID,
FR.APPLICATION_ID
INTO T_RESP_ID,
T_AP_ID
FROM FND_RESPONSIBILITY FR
WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;

--CALL API
BEGIN
FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(USER_ID => I_TO_USER_ID,
RESPONSIBILITY_ID => T_RESP_ID,
RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
SECURITY_GROUP_ID => 0,
START_DATE => TRUNC(SYSDATE),
END_DATE => NULL,
DESCRIPTION => NULL);
COMMIT;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
--
PROCEDURE DISABLE_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER)
IS
T_AP_ID NUMBER;
T_RESP_ID NUMBER;
T_END_DATE DATE;
--來源者權限表
CURSOR C_F_RESP
IS
SELECT FR.RESPONSIBILITY_KEY,FUR2.START_DATE
FROM FND_USER_RESP_GROUPS_DIRECT FUR
JOIN FND_RESPONSIBILITY FR
ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
JOIN FND_USER_RESP_GROUPS_DIRECT FUR2
ON FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
AND FUR2.USER_ID = I_TO_USER_ID
WHERE FUR.END_DATE < SYSDATE --已失效的部分
AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
BEGIN
FOR C1 IN C_F_RESP LOOP
--取得RESP資訊
SELECT FR.RESPONSIBILITY_ID,
FR.APPLICATION_ID
INTO T_RESP_ID,
T_AP_ID
FROM FND_RESPONSIBILITY FR
WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;

--
IF C1.START_DATE IS NOT NULL THEN
IF C1.START_DATE > SYSDATE THEN
T_END_DATE := C1.START_DATE;
ELSE
T_END_DATE := SYSDATE;
END IF;
END IF;

--
BEGIN
FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID => I_TO_USER_ID,
RESPONSIBILITY_ID => T_RESP_ID,
RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
SECURITY_GROUP_ID => 0,
START_DATE => C1.START_DATE,
END_DATE => T_END_DATE,
DESCRIPTION => NULL);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
--
PROCEDURE ENABLE_USER_RESP(I_FROM_USER_ID IN NUMBER,
I_TO_USER_ID IN NUMBER)
IS
T_AP_ID NUMBER;
T_RESP_ID NUMBER;
--來源者權限表
CURSOR C_F_RESP
IS
SELECT FR.RESPONSIBILITY_KEY,FUR2.START_DATE
FROM FND_USER_RESP_GROUPS_DIRECT FUR
JOIN FND_RESPONSIBILITY FR
ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
JOIN FND_USER_RESP_GROUPS_DIRECT FUR2
ON FUR2.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
AND FUR2.USER_ID = I_TO_USER_ID
WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
AND FUR.USER_ID = I_FROM_USER_ID; --來源對象
BEGIN
FOR C1 IN C_F_RESP LOOP
--取得RESP資訊
SELECT FR.RESPONSIBILITY_ID,
FR.APPLICATION_ID
INTO T_RESP_ID,
T_AP_ID
FROM FND_RESPONSIBILITY FR
WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;

--
BEGIN
FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID => I_TO_USER_ID,
RESPONSIBILITY_ID => T_RESP_ID,
RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
SECURITY_GROUP_ID => 0,
START_DATE => C1.START_DATE,
END_DATE => NULL,
DESCRIPTION => NULL);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
--
PROCEDURE DISABLE_USER_ALL_RESP(I_USER_ID IN NUMBER)
IS
T_AP_ID NUMBER;
T_RESP_ID NUMBER;
T_END_DATE DATE;
--來源者權限表
CURSOR C_F_RESP
IS
SELECT FR.RESPONSIBILITY_KEY,FUR.START_DATE
FROM FND_USER_RESP_GROUPS_DIRECT FUR
JOIN FND_RESPONSIBILITY FR
ON FR.RESPONSIBILITY_ID = FUR.RESPONSIBILITY_ID
WHERE (FUR.END_DATE IS NULL OR FUR.END_DATE >= SYSDATE)
AND FUR.USER_ID = I_USER_ID; --來源對象
BEGIN
FOR C1 IN C_F_RESP LOOP
--取得RESP資訊
SELECT FR.RESPONSIBILITY_ID,
FR.APPLICATION_ID
INTO T_RESP_ID,
T_AP_ID
FROM FND_RESPONSIBILITY FR
WHERE FR.RESPONSIBILITY_KEY = C1.RESPONSIBILITY_KEY;

--
IF C1.START_DATE IS NOT NULL THEN
IF C1.START_DATE > SYSDATE THEN
T_END_DATE := C1.START_DATE;
ELSE
T_END_DATE := SYSDATE;
END IF;
END IF;
--
BEGIN
FND_USER_RESP_GROUPS_API.UPDATE_ASSIGNMENT(USER_ID => I_USER_ID,
RESPONSIBILITY_ID => T_RESP_ID,
RESPONSIBILITY_APPLICATION_ID => T_AP_ID,
SECURITY_GROUP_ID => 0,
START_DATE => C1.START_DATE,
END_DATE => T_END_DATE,
DESCRIPTION => NULL);
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
END XX_FND_USER_RESP_PKG;

/*使用說明 :*/

begin

XX_fnd_user_resp_pkg.hpx_fnd_copy_user_resp(i_from_user_id => :i_from_user_id, ç A使用者USER ID

i_to_user_id => :i_to_user_id, ç B使用者USER ID

i_mode => :i_mode); ç 複製規則,如下說明

end;

I_MODE : 權限複製規則

0 : 只新增使用者B短少的權限,但不將即有卻失效的權限予以復權

1 : 新增使用者B短少的權限,且將即有但失效的權限予以復權(且不影使用者B較使用者A多的權限)

2 : 完整複製,將使用者B原有權限全數失效後再執行I_MODE 2

Oracle DBA - ORA-01591 lock held by in-doubt distributed transaction

2013-02-07T01:22:00.001-08:00

ORA-01591

Step 1. Select 'Rollback force '''||local_tran_id||'''' from sys.pending_trans$;
Find out the local_tran_id.
Step 2. Try to commit or rollback.
rollback force LOCAL_TRAN_ID;
or
commit force LOCAL_TRAN_ID;
Step 3. If still error . then log in use sys and execute :
set transaction use rollback segment system;
delete from dba_2pc_pending where local_tran_id = LOCAL_TRAN_ID;
delete from pending_sessions$ where local_tran_id = LOCAL_TRAN_ID;
delete from pending_sub_sessions$ where local_tran_id = LOCAL_TRAN_ID;
commit;
rollback force LOCAL_TRAN_ID;

Oracle ERP R12. CST_INVALID_WIP (The wip entity is either not defined or does not have a period balance entry.) - Discrete Job

2013-02-04T16:50:00.001-08:00

Cause :

Records not found in WIP_PERIOD_BALANCES

(該工單於WIP_PERIOD_BALANCES裡，少了該交易期間的資料)

Solution :

I. Normally, when there are records in MTL_MATERIAL_TRANSACTIONS with the　Costed_Flag = 'E' you would resubmit these records by doing the following :

（一般而言，若MMT裡有發現COSTED_FLAG=E時，正常異常排除的步驟如下）

a. Backup the rows to be updated.　（將資料備份下來）

b. Turn off the Cost Manager.　（先暫停Cost Manager，以避免干擾接下來要執行的步驟）

c. Run the SQL statement:　（執行以下SQL）

UPDATE MTL_MATERIAL_TRANSACTIONS

SET costed_flag = 'N',

error_code = NULL,

error_explanation = NULL,

transaction_group_id = NULL

WHERE organization_id =

and transaction_source_id =

and costed_flag IS NOT NULL;

II. When this does not work, it is usually due to one or more of the following three conditions.

(若異常狀況仍存在，則可以是以下原因造成)

* Item costs are not defined for Frozen cost type in CST_ITEM_COSTS.

　　（CST_ITEM_COSTS沒有該品號的Frozen cost）

==> 請補建Item Cost 並Copy cost至Forezen cost

* WIP_PERIOD_BALANCES is missing rows for the acct_period_id.

　　 (該工單於WIP_PERIOD_BALANCES缺少該期間的餘額資料)

==> 請於WIP_PERIOD_BALANCES補該筆工單該Period的資料

NOTE: THIS DOES NOT APPLY TO CLOSED DISCRETE JOBS WDJ.STATUS_TYPE = 12

(注意 : 該動作不適用於Closed的工單)

Oracle ERP R12. 使用interface批量變更品號的operation code . (Mass changes item's operation code use BOM_OP_SEQUENCES_INTERFACE. )

2013-01-31T00:18:00.004-08:00

Sample code :

DECLARE
--
T_OLD_OP VARCHAR2(10) := '2T02'; --**** old operation code
T_NEW_OP VARCHAR2(10) := '2S02'; --**** new operation code
T_NEW_OP_ID NUMBER;
T_ORGANIZATION_ID NUMBER := 85; --organization id
T_DISABLE_DATE DATE := SYSDATE;
T_EFFECT_DATE DATE := SYSDATE+1/86400;
--
CURSOR C_LIST --item list ==> user define
IS
SELECT D.ORGANIZATION_ID,
D.INVENTORY_ITEM_ID,
A.ROUTING_SEQUENCE_ID,
B.OPERATION_SEQUENCE_ID,
B.OPERATION_SEQ_NUM,
C.STANDARD_OPERATION_ID
FROM BOM_OPERATIONAL_ROUTINGS A
JOIN BOM_OPERATION_SEQUENCES_V B--BOM_OPERATION_SEQUENCES B
ON A.COMMON_ROUTING_SEQUENCE_ID = B.ROUTING_SEQUENCE_ID
LEFT JOIN BOM_STANDARD_OPERATIONS C
ON B.STANDARD_OPERATION_ID = C.STANDARD_OPERATION_ID
JOIN MTL_SYSTEM_ITEMS_B D
ON A.ORGANIZATION_ID = D.ORGANIZATION_ID
AND A.ASSEMBLY_ITEM_ID = D.INVENTORY_ITEM_ID
JOIN BOM_DEPARTMENTS F
ON F.DEPARTMENT_ID = B.DEPARTMENT_ID
AND F.ORGANIZATION_ID = A.ORGANIZATION_ID
WHERE A.ORGANIZATION_ID = T_ORGANIZATION_ID
AND C.OPERATION_CODE = T_OLD_OP
AND (B.DISABLE_DATE IS NULL OR
B.DISABLE_DATE >= SYSDATE);
BEGIN
--Find the new operation id
SELECT BSO.STANDARD_OPERATION_ID
INTO T_NEW_OP_ID
FROM BOM_STANDARD_OPERATIONS BSO
WHERE BSO.ORGANIZATION_ID = T_ORGANIZATION_ID
AND BSO.OPERATION_CODE = T_NEW_OP;

FOR C1 IN C_LIST LOOP
--STEP 1. Disable old operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE
(ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
DISABLE_DATE,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
T_DISABLE_DATE,
'Update',
1,
1);
END;

--STEP 2. Add new operation
BEGIN
INSERT INTO BOM_OP_SEQUENCES_INTERFACE(
ASSEMBLY_ITEM_ID,
ORGANIZATION_ID,
ROUTING_SEQUENCE_ID,
OPERATION_SEQUENCE_ID,
OPERATION_SEQ_NUM,
EFFECTIVITY_DATE,
STANDARD_OPERATION_ID,
REFERENCE_FLAG,
TRANSACTION_TYPE,
PROCESS_FLAG,
BATCH_ID)
VALUES(C1.INVENTORY_ITEM_ID,
C1.ORGANIZATION_ID,
C1.ROUTING_SEQUENCE_ID,
C1.OPERATION_SEQUENCE_ID,
C1.OPERATION_SEQ_NUM,
T_EFFECT_DATE,
T_NEW_OP_ID,
1,
'Create',
1,
2);
END;
END LOOP;
END;

Step 3. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 1 (For disable old operation code)

Step 4. Run BOM & Routing Import (Bill and Routing Interface) ==> batch id is set to 2 (For add new operation code)

Oracle ERP R12. Receiving Transaction can not find the RT number .

2013-01-29T16:50:00.003-08:00

Receipt number(RT No.) can not find the experience to share with you in this.
If you are sure that points to receipt also learned that the receipt number(RT No.), but acceptance and delivery can not be found the receipt number(RT No.) in the Receiving Transaction Function. Please check the RCV_TRANSACTIONS_INTERFACE , and if so, then in the confirmation after such interface delete. After re-entering the Receiving Transaction should be able to find the receipt number (RT No.).
The Function of Receiving Transaction Receipt Number LOV will exclude the Interface are still part of the data, in order to ensure that the data would not have repeated transactions.

在這跟各位分享一下收料單找不到的經驗。
若你確定已進行點收也得知收料單，但是卻在Receiving Transaction該Function裡找不到該收料點進行驗收及入庫動作時。 請檢查RCV_TRANSACTIONS_INTERFACE 是否有該收料單Pending的資料，若有則在確認後將該筆interface刪除。之後再進入Receiving Transaction就應該可以找到該收料點了。
因為Receiving Transaction該Function的Receipt Number LOV會排除掉Interface尚有資料的部分，以確保同一筆資料不會有重覆的交易。

Oracle ERP R12. Delivery is closed but SO Line status is 'Picked'.

2013-01-14T00:23:00.002-08:00

今天User反應一狀況，其表示有一筆Delivery 已做Ship Confirm ，而且Delivery 的狀態也是呈現Closed ，但是訂單項次(SO Line)的Status卻仍停在Picked !

解法如下 (執行Interface Trip Stop - SRS):
OM Module => Shipping => Interface => Run => Interface Trip Stop - SRS

Oracle ERP R12. How to use the interface tables update attribute to null?

2013-01-13T18:51:00.002-08:00

一般而言透過interface table匯入資料時,其欄位值若為空值(null),則代表該欄位不匯入,也就是不會去動該欄位.
故若該欄位原本有資料,也不會將其清空為空值(null).
但若需要透過interface table來將該欄位值清空,那要怎麼做呢?
其實很簡單,方法如下:
1. 若為數字欄位,則輸入 -999999
2. 若為文字欄位,則輸入 "!".
====================================
If a field for numeric fields, fill -999999.
If a field for character fields, fill "!".

Oracle ERP R12. Add SO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

2013-01-07T17:25:00.000-08:00

以下程式碼範例是用來在SO (訂單)裡加入附件迴紋針說明的用法,分享給各位讀者.該範例是例用SHORTTEXT來紀錄訂單的正面麥頭及側面麥頭

CREATE PROCEDURE SO_SHORTTEXT(I_ORG_ID IN NUMBER,
I_HEADER_ID IN NUMBER,
I_TITLE IN VARCHAR2, --PGM_NAME
I_DESCRIPTION IN VARCHAR2, --TITLE DESCRIPTION
I_TEXT IN VARCHAR2,
O_DOCUMENT_ID OUT NUMBER,
O_ATTACHED_DOC_ID OUT NUMBER,
O_MEDIA_ID OUT NUMBER)
IS
T_ROWID ROWID;
T_SEQ_NUM NUMBER;
T_FND_USER_ID NUMBER := 0; --DEFAULT SYSADMIN
T_SHORT_DATATYPE_ID NUMBER := 1; --SHORT_TEXT
T_CATEGORY_ID NUMBER := 326; --Documents
T_EXISTS VARCHAR2(10);
BEGIN
FND_PROFILE.GET('USER_ID',T_FND_USER_ID);
--
IF T_FND_USER_ID IS NULL THEN
T_FND_USER_ID := 0;
END IF;
--
SELECT DECODE(COUNT(*),
0,'N',
'Y')
INTO T_EXISTS
FROM HPX_OE_ORDER_MARK_V HOOM
WHERE HOOM.HEADER_ID = I_HEADER_ID
AND HOOM.ORG_ID = I_ORG_ID
AND HOOM.DOCUMENT_DESCRIPTION = I_TITLE;
--
IF I_TITLE = '訂單正麥' THEN
T_SEQ_NUM := 10;
ELSIF I_TITLE = '訂單側麥' THEN
T_SEQ_NUM := 20;
ELSE
SELECT NVL(MAX(FAD.SEQ_NUM),0)+1
INTO T_SEQ_NUM
FROM FND_ATTACHED_DOCUMENTS FAD
WHERE 1=1
AND FAD.PK1_VALUE = TO_CHAR(I_HEADER_ID);
END IF;
--
IF T_EXISTS = 'N' THEN
FND_DOCUMENTS_PKG.INSERT_ROW(
X_Rowid => T_ROWID,
X_document_id => O_DOCUMENT_ID,
X_creation_date => SYSDATE,
X_created_by => T_FND_USER_ID,
X_last_update_date => SYSDATE,
X_last_updated_by => T_FND_USER_ID,
X_datatype_id => T_SHORT_DATATYPE_ID,
X_create_doc => 'Y',
X_category_id => T_CATEGORY_ID,
X_security_type => 4,
X_security_id => NULL,
X_description => I_DESCRIPTION,
X_media_id => O_MEDIA_ID,
X_title => I_TITLE,
X_language => 'US',
X_publish_flag => 'N', --This flag allow the file to share across multiple organization
X_usage_type => 'O'); --O:可被修改 / S:不能修改
ELSE
SELECT MAX(HOOM.media_id)
INTO O_MEDIA_ID
FROM HPX_OE_ORDER_MARK_V HOOM
WHERE HOOM.HEADER_ID = I_HEADER_ID
AND HOOM.ORG_ID = I_ORG_ID
AND HOOM.DOCUMENT_DESCRIPTION = I_TITLE;
END IF;
--
IF O_MEDIA_ID IS NOT NULL AND T_EXISTS = 'N' THEN
INSERT INTO FND_DOCUMENTS_SHORT_TEXT(MEDIA_ID,SHORT_TEXT)
VALUES(O_MEDIA_ID,I_TEXT);
--
SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL
INTO O_ATTACHED_DOC_ID
FROM DUAL;
--
INSERT INTO FND_ATTACHED_DOCUMENTS(
ATTACHED_DOCUMENT_ID,
DOCUMENT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEQ_NUM,
ENTITY_NAME,
PK1_VALUE,
AUTOMATICALLY_ADDED_FLAG,
CATEGORY_ID)
VALUES(
O_ATTACHED_DOC_ID,
O_DOCUMENT_ID,
SYSDATE,
T_FND_USER_ID,
SYSDATE,
T_FND_USER_ID,
T_SEQ_NUM,
'OE_ORDER_HEADERS',
TO_CHAR(I_HEADER_ID),
'N',
T_CATEGORY_ID);
ELSIF T_EXISTS = 'Y' THEN
UPDATE FND_DOCUMENTS_SHORT_TEXT FST
SET FST.SHORT_TEXT = I_TEXT
WHERE FST.MEDIA_ID = O_MEDIA_ID;
--
UPDATE FND_DOCUMENTS FD
SET FD.USAGE_TYPE = 'O' --O:可被修改 / S:不能修改
WHERE FD.MEDIA_ID = O_MEDIA_ID;
END IF;
END;

Oracle ERP R12. Add PO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

2013-01-07T17:11:00.007-08:00

以下程式碼範例是用來在PO (採購單)裡加入附件迴紋針說明的用法,分享給各位讀者:

Create PROCEDURE PO_SHORTTEXT(I_ORGANIZATION_ID IN NUMBER,
I_PO_HEADER_ID IN NUMBER,
I_TITLE IN VARCHAR2, --PGM_NAME
I_DESCRIPTION IN VARCHAR2, --TITLE DESCRIPTION
I_TEXT IN VARCHAR2,
O_DOCUMENT_ID OUT NUMBER,
O_ATTACHED_DOC_ID OUT NUMBER,
O_MEDIA_ID OUT NUMBER)
IS
T_ROWID ROWID;
T_SEQ_NUM NUMBER;
T_FND_USER_ID NUMBER := 0; --DEFAULT SYSADMIN
T_SHORT_DATATYPE_ID NUMBER := 1; --SHORT_TEXT
T_CATEGORY_ID NUMBER := 1000504; --Documents
BEGIN
FND_PROFILE.GET('USER_ID',T_FND_USER_ID);
--
SELECT A.CATEGORY_ID
INTO T_CATEGORY_ID
FROM FND_DOCUMENT_CATEGORIES_TL A
WHERE A.USER_NAME = 'Documents';
--
IF T_FND_USER_ID IS NULL THEN
T_FND_USER_ID := 0;
END IF;
--
SELECT NVL(MAX(FAD.SEQ_NUM),0)+1
INTO T_SEQ_NUM
FROM FND_ATTACHED_DOCUMENTS FAD
WHERE 1=1
AND FAD.PK1_VALUE = TO_CHAR(I_PO_HEADER_ID);
--
FND_DOCUMENTS_PKG.INSERT_ROW(
X_Rowid => T_ROWID,
X_document_id => O_DOCUMENT_ID,
X_creation_date => SYSDATE,
X_created_by => T_FND_USER_ID,
X_last_update_date => SYSDATE,
X_last_updated_by => T_FND_USER_ID,
X_datatype_id => T_SHORT_DATATYPE_ID,
X_create_doc => 'Y',
X_category_id => T_CATEGORY_ID,
X_security_type => 1,
X_security_id => I_ORGANIZATION_ID,
X_description => I_DESCRIPTION,
X_media_id => O_MEDIA_ID,
X_title => I_TITLE,
X_language => 'US',
X_publish_flag => 'N', --This flag allow the file to share across multiple organization
X_usage_type => 'S');
--
IF O_MEDIA_ID IS NOT NULL THEN
INSERT INTO FND_DOCUMENTS_SHORT_TEXT(MEDIA_ID,SHORT_TEXT)
VALUES(O_MEDIA_ID,I_TEXT);
--
SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL
INTO O_ATTACHED_DOC_ID
FROM DUAL;
--
INSERT INTO FND_ATTACHED_DOCUMENTS(
ATTACHED_DOCUMENT_ID,
DOCUMENT_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
SEQ_NUM,
ENTITY_NAME,
PK1_VALUE,
AUTOMATICALLY_ADDED_FLAG,
CATEGORY_ID)
VALUES(
O_ATTACHED_DOC_ID,
O_DOCUMENT_ID,
SYSDATE,
T_FND_USER_ID,
SYSDATE,
T_FND_USER_ID,
T_SEQ_NUM,
'PO_HEADERS',
TO_CHAR(I_PO_HEADER_ID),
'N',
T_CATEGORY_ID);
END IF;
END;

Oracle ERP R12. Add WIP_ENTITY ATTACHMENTS with FND_DOCUMENTS_PKG (API)

2013-01-07T17:00:00.003-08:00

以下程式碼範例是用來在WIP_ENTITY (工單)裡加入附件迴紋針說明的用法,分享給各位讀者:

CREATE PROCEDURE WO_SHORTTEXT(I_ORGANIZATION_ID IN NUMBER,

I_WIP_ENTITY_ID IN NUMBER,

I_TITLE IN VARCHAR2, --PGM_NAME

I_DESCRIPTION IN VARCHAR2, --TITLE DESCRIPTION

I_TEXT IN VARCHAR2,

O_DOCUMENT_ID OUT NUMBER,

O_ATTACHED_DOC_ID OUT NUMBER,

O_MEDIA_ID OUT NUMBER)

說明 :

1. 輸入參數

I_ORGANIZATION_ID ==> ORGANIZATION_ID

I_WIP_ENTITY_ID ==> 工單的WIP_ENTITY_ID

I_TITLE ==> 迴紋針的Title

I_DESCRIPTION ==> 迴紋針的說明

I_TEXT ==> 欲附加的文字內容

2. 輸出變數

O_DOCUMENT_ID / O_ATTACHED_DOC_ID / O_MEDIA_ID

T_ROWID ROWID;

T_SEQ_NUM NUMBER;

T_FND_USER_ID NUMBER := 0; --DEFAULT SYSADMIN

T_SHORT_DATATYPE_ID NUMBER := 1; --SHORT_TEXT

T_CATEGORY_ID NUMBER := 6; --WIP Job/Schedule Attachments

BEGIN

FND_PROFILE.GET('USER_ID',T_FND_USER_ID); --初始化環境變數

--找出工單的CATEGORY ID

SELECT A.CATEGORY_ID

INTO T_CATEGORY_ID

FROM FND_DOCUMENT_CATEGORIES A

WHERE A.NAME = 'WIP_ENTITY_ATTACHMENTS';

IF T_FND_USER_ID IS NULL THEN

T_FND_USER_ID := 0;

END IF;

--找出目前最後一個的文件序號再加一為新增的文件序號

SELECT NVL(MAX(FAD.SEQ_NUM),0)+1

INTO T_SEQ_NUM

FROM FND_ATTACHED_DOCUMENTS FAD

WHERE 1=1

AND FAD.PK1_VALUE = TO_CHAR(I_WIP_ENTITY_ID)

AND FAD.PK2_VALUE = TO_CHAR(I_ORGANIZATION_ID);

--呼叫API

FND_DOCUMENTS_PKG.INSERT_ROW(

X_Rowid => T_ROWID,

X_document_id => O_DOCUMENT_ID,

X_creation_date => SYSDATE,

X_created_by => T_FND_USER_ID,

X_last_update_date => SYSDATE,

X_last_updated_by => T_FND_USER_ID,

X_datatype_id => T_SHORT_DATATYPE_ID,

X_create_doc => 'Y',

X_category_id => T_CATEGORY_ID,

X_security_type => 1,

X_security_id => I_ORGANIZATION_ID,

X_description => I_DESCRIPTION,

X_media_id => O_MEDIA_ID,

X_title => I_TITLE,

X_language => 'US',

X_publish_flag => 'N', --This flag allow the file to share across multiple organization

X_usage_type => 'S');

IF O_MEDIA_ID IS NOT NULL THEN

--寫入內容

INSERT INTO FND_DOCUMENTS_SHORT_TEXT(MEDIA_ID,SHORT_TEXT)

VALUES(O_MEDIA_ID,I_TEXT);

SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL

INTO O_ATTACHED_DOC_ID

FROM DUAL;

--建立文件內容與迴紋針的關聯

INSERT INTO FND_ATTACHED_DOCUMENTS(

ATTACHED_DOCUMENT_ID,

DOCUMENT_ID,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

SEQ_NUM,

ENTITY_NAME,

PK1_VALUE,

PK2_VALUE,

AUTOMATICALLY_ADDED_FLAG,

CATEGORY_ID)

VALUES(

O_ATTACHED_DOC_ID,

O_DOCUMENT_ID,

SYSDATE,

T_FND_USER_ID,

SYSDATE,

T_FND_USER_ID,

T_SEQ_NUM,

'WIP_DISCRETE_JOBS',

TO_CHAR(I_WIP_ENTITY_ID),

TO_CHAR(I_ORGANIZATION_ID),

'N',

T_CATEGORY_ID);

END IF;

END;

Oracle ERP R12. <經驗分享> FA Mass Transfer

2013-01-04T00:46:00.004-08:00

當公司有同仁離職時,其所保管的固定資產常會用Mass Transfer功能進行大量移轉.

請注意,若執行後出現移轉失敗的情況,請先檢查這二位(離職人員及接數人員)是否已失效.

若有任何一方失效都會造成移轉的失敗. 請先將其生效,待其資產移轉完後再行失效.

Ps : 一般常見的是,離職人員已失效所造成的.

狗爸的世界

Oracle ERP R12. - 內建SQL Package 可以得到會計節段說明資料

Oracle ERP R12. 無法複製貼上

Oracle ERP R12. 中文環境下Export 亂碼之狀況排除

Oracle ERP R12. WF_NOTIFICATION.SEND (通知訊息功能)

Oracle ERP R12. create locator using API (INV_LOC_WMS_PUB.CREATE_LOCATOR) - 建立Locator的API

Oracle ERP R12. How to select oe_transaction_type_syn ?

Oracle ERP R12. Profile - OE: AutoPrice (自動更新Unit Selling Price時機設定)

Oracle ERP R12. 如何透過Personalize功能呼叫另一form來回傳值. Get another form's result value (user personalize) .

Oracle ERP R12. Unassign or delete large number of items .

Oracle ERP R12. 使用者忘記密碼 如何自行變更密碼

狗爸的世界: Oracle ERP R12. 使用interface批量變更品號的operation code ....

狗爸的世界: Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privi...

狗爸的世界: Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to...

Oracle ERP R12. 如何使用API (FND_USER_PKG)建立帳號. How to use FND_USER_PKG.CREATEUSER ?

Oracle ERP R12. - 如何快速複製使用者權限 , How to grant privileges from one user to another user ?

Oracle DBA - ORA-01591 lock held by in-doubt distributed transaction

Oracle ERP R12. CST_INVALID_WIP (The wip entity is either not defined or does not have a period balance entry.) - Discrete Job

Cause :

Solution :

Oracle ERP R12. 使用interface批量變更品號的operation code . (Mass changes item's operation code use BOM_OP_SEQUENCES_INTERFACE. )

Oracle ERP R12. Receiving Transaction can not find the RT number .

Oracle ERP R12. Delivery is closed but SO Line status is 'Picked'.

Oracle ERP R12. How to use the interface tables update attribute to null?

Oracle ERP R12. Add SO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

Oracle ERP R12. Add PO_SHORTTEXT with FND_DOCUMENTS_PKG (API)

Oracle ERP R12. Add WIP_ENTITY ATTACHMENTS with FND_DOCUMENTS_PKG (API)

Oracle ERP R12. <經驗分享> FA Mass Transfer

Oracle ERP R12. 使用者忘記密碼如何自行變更密碼