Aloz 的 Oracle ERP 天地

直接就貼 Sample Code 上來 (以 Update "Schedule Ship Date" 為例)
DECLARE
L_USER_ID NUMBER;
L_RESP_ID NUMBER;
L_APPL_ID NUMBER;
L_HEADER_REC_IN OE_ORDER_PUB.HEADER_REC_TYPE;
L_LINE_TBL_IN OE_ORDER_PUB.LINE_TBL_TYPE;
L_ACTION_REQUEST_TBL_IN OE_ORDER_PUB.REQUEST_TBL_TYPE;
L_HEADER_REC_OUT OE_ORDER_PUB.HEADER_REC_TYPE;
L_LINE_TBL_OUT OE_ORDER_PUB.LINE_TBL_TYPE;
L_HEADER_VAL_REC_OUT OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
L_HEADER_ADJ_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
L_HEADER_ADJ_VAL_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
L_HEADER_PRICE_ATT_TBL_OUT OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
L_HEADER_ADJ_ATT_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
L_HEADER_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
L_HEADER_SCREDIT_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
L_HEADER_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
L_LINE_VAL_TBL_OUT OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
L_LINE_ADJ_TBL_OUT OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
L_LINE_ADJ_VAL_TBL_OUT OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
L_LINE_PRICE_ATT_TBL_OUT OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
L_LINE_ADJ_ATT_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
L_LINE_ADJ_ASSOC_TBL_OUT OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
L_LINE_SCREDIT_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
L_LINE_SCREDIT_VAL_TBL_OUT OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
L_LOT_SERIAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
L_LOT_SERIAL_VAL_TBL_OUT OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
L_ACTION_REQUEST_TBL_OUT OE_ORDER_PUB.REQUEST_TBL_TYPE;
L_CHR_PROGRAM_UNIT_NAME VARCHAR2 (100);
L_CHR_RET_STATUS VARCHAR2 (1000) := NULL;
L_MSG_COUNT NUMBER := 0;
L_MSG_DATA VARCHAR2 (2000);
L_NUM_API_VERSION NUMBER := 1.0;

CURSOR C_SO_DETAILS IS
SELECT OH.HEADER_ID
, OL.LINE_ID
, OH.ORDER_NUMBER AS SO_NUMBER
, MSI.SEGMENT1
, OL.LINE_NUMBER || '.' || OL.SHIPMENT_NUMBER AS LINE
, OL.INVENTORY_ITEM_ID
FROM OE_ORDER_HEADERS_ALL OH
, OE_ORDER_LINES_ALL OL
, MTL_SYSTEM_ITEMS MSI
WHERE 0 = 0
AND OH.HEADER_ID = OL.HEADER_ID
AND OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND OL.LINE_ID = 1
;
BEGIN
SELECT USER_ID
INTO L_USER_ID
FROM FND_USER
WHERE USER_NAME = 'ABC'
;

SELECT RESPONSIBILITY_ID
, APPLICATION_ID
INTO L_RESP_ID
, L_APPL_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME LIKE 'Order Management Super User'
;

FND_GLOBAL.APPS_INITIALIZE (L_USER_ID, L_RESP_ID, L_APPL_ID);

FOR ISO_REC IN C_SO_DETAILS LOOP
L_LINE_TBL_IN (1) := OE_ORDER_PUB.G_MISS_LINE_REC;

L_LINE_TBL_IN (1).LINE_ID := ISO_REC.LINE_ID;
L_LINE_TBL_IN (1).SCHEDULE_SHIP_DATE := SYSDATE; --TO_DATE(ISO_REC.UPDATE_SSD,'DD-MON-RRRR HH24:MI:SS');
L_LINE_TBL_IN (1).OPERATION := OE_GLOBALS.G_OPR_UPDATE;

OE_MSG_PUB.DELETE_MSG;

OE_ORDER_PUB.PROCESS_ORDER (
P_API_VERSION_NUMBER => L_NUM_API_VERSION
, P_INIT_MSG_LIST => FND_API.G_FALSE
, P_RETURN_VALUES => FND_API.G_FALSE
, P_ACTION_COMMIT => FND_API.G_FALSE
, P_LINE_TBL => L_LINE_TBL_IN
, X_HEADER_REC => L_HEADER_REC_OUT
, X_HEADER_VAL_REC => L_HEADER_VAL_REC_OUT
, X_HEADER_ADJ_TBL => L_HEADER_ADJ_TBL_OUT
, X_HEADER_ADJ_VAL_TBL => L_HEADER_ADJ_VAL_TBL_OUT
, X_HEADER_PRICE_ATT_TBL => L_HEADER_PRICE_ATT_TBL_OUT
, X_HEADER_ADJ_ATT_TBL => L_HEADER_ADJ_ATT_TBL_OUT
, X_HEADER_ADJ_ASSOC_TBL => L_HEADER_ADJ_ASSOC_TBL_OUT
, X_HEADER_SCREDIT_TBL => L_HEADER_SCREDIT_TBL_OUT
, X_HEADER_SCREDIT_VAL_TBL => L_HEADER_SCREDIT_VAL_TBL_OUT
, X_LINE_TBL => L_LINE_TBL_OUT
, X_LINE_VAL_TBL => L_LINE_VAL_TBL_OUT
, X_LINE_ADJ_TBL => L_LINE_ADJ_TBL_OUT
, X_LINE_ADJ_VAL_TBL => L_LINE_ADJ_VAL_TBL_OUT
, X_LINE_PRICE_ATT_TBL => L_LINE_PRICE_ATT_TBL_OUT
, X_LINE_ADJ_ATT_TBL => L_LINE_ADJ_ATT_TBL_OUT
, X_LINE_ADJ_ASSOC_TBL => L_LINE_ADJ_ASSOC_TBL_OUT
, X_LINE_SCREDIT_TBL => L_LINE_SCREDIT_TBL_OUT
, X_LINE_SCREDIT_VAL_TBL => L_LINE_SCREDIT_VAL_TBL_OUT
, X_LOT_SERIAL_TBL => L_LOT_SERIAL_TBL_OUT
, X_LOT_SERIAL_VAL_TBL => L_LOT_SERIAL_VAL_TBL_OUT
, X_ACTION_REQUEST_TBL => L_ACTION_REQUEST_TBL_OUT
, X_RETURN_STATUS => L_CHR_RET_STATUS
, X_MSG_COUNT => L_MSG_COUNT
, X_MSG_DATA => L_MSG_DATA
);

L_MSG_DATA := NULL;

IF L_CHR_RET_STATUS <> 'S' THEN
FOR IINDX IN 1 .. L_MSG_COUNT LOOP
L_MSG_DATA := L_MSG_DATA || ' ' || OE_MSG_PUB.GET (IINDX);
END LOOP;
END IF;

DBMS_OUTPUT.ENABLE (10000);
DBMS_OUTPUT.PUT_LINE ('Sales Order => ' || ISO_REC.SO_NUMBER || ' - Line Number => ' || ISO_REC.LINE);
DBMS_OUTPUT.PUT_LINE ('Return Status: ' || L_CHR_RET_STATUS);
DBMS_OUTPUT.PUT_LINE ('Error Message: ' || L_MSG_DATA);
END LOOP;

COMMIT;
END;

如果你的 Responsibility 的 Menu 是套用標準的 OM Menu (ONT_SUPER_USER) 或 OM > Customer 功能是使用標準的 Submenu (ONT_CUSTOMERS), 那你有可能會遇到這個問題, 可是使用 AR 下的 Customer 正常....

- 情境:
1. 點選 Customer Standard

Order Organizer 是很好用的查詢介面, 除了不能維護資料之外, 其他的功能都與 Sales Order 沒有太大的不同. 不過, 也是有它獨特的功能可以介紹, 就是自定查尋 (Query)

當在 Order Organizer Find 畫面輸入完查詢條件之後, 會看到結果視窗, 左手邊就是要介紹的功能 :
- Search Results : 目前的查詢結果

看到對岸論壇有人在問, Customer 主檔的 Business Purpose 中的 Location 欄位可不可以自動給值?
雄雄想到, 我們家的系統一直以來都是自動給值的 (序號), 所以這應該是有一個設定可以調整, 很快的就找到了 :
OM Superuser > Setup > Customers > System Options > Trans and Customers > Automatic Site Numbering
當這個設定有勾選, Location 欄位就會反灰, 在存檔後自動給予一編號, 反之, 則開放給使用者自行給予, 且為必填

想要找看看, 在 11i 的版本中, 是不是 API 給 Blanket Sales Agreement (BSA) 使用, 結果很遺憾的沒有, 只有 R12 限定 (囧)
Doc ID : 790223.1
Purpose
This note is intended to show how the creation of Blanket Sales Agreement action can be simulated using the newly available Public API in R12.1.1. .
Scope and Application
The intended audience is for those with technical knowledge of Oracle Applications Code, the usage of public APIs, and those familiar with creating custom solutions for unique business requirements.
How to create Blanket Sales Agreement using public API OE_BLANKET_PUB.Process_Blanket in Oracle Order Management
Assumption: 
=========== 
The following setups related to the Blanket Sales Agreement are not in the scope of this document  
and we assume that these setup steps are already in place. 
1. Setup of order transaction type 
2. Setup of document sequence 
3. Setup of document assignment 
The Blanket Sales Agreement feature has been around from Release 11.5.9 and  
its creation in Order Management has been supported from Blanket Sales Agreement Form. 
From Release 12.1.1, support has been provided to create BSA from backend using the newly  
available public API OE_BLANKET_PUB.Process_Blanket(). 
The sales Agreement functionality supports standard, ATO items and Kits 
and also includes the ability to create releases by order import and process order API.  
Sales Agreement header created usually includes the following information 
   - Customer, ship to , Bill to , Version e.t.c 
   - Effective Dates  
   - Payment and freight terms between the customer and supplier 
   - Sales Agreement Min and Max quantity 
   - Control flag to determine whether you can exceed the maximum value 
   - Pricing information such as Standard price lists or Sales Agreement price lists. 
Sales Agreement Lines Include the following information 
   - Item categories to store the categories and ALL ITEMS to cover all items. 
   - Min and Max quantity agreed by the customer and supplier 
   - Price including the choice of price list in addition to line level  
     modifiers: Discount % and discount amount. 
   - Effective dates
Procedure Parameter Description: 
******************************** 
OE_BLANKET_PUB.process_Blanket() accepts the following parameters

之前有寫過一篇 Defaulting Rule 與 Package (http://aloz0101.pixnet.net/blog/post/17066963), 不過裡面沒有提到 Defaulting Rule 的設定方式, 就用這篇來說吧
畫面路徑 : OM > Setup > Rules > Defaulting
範例功能 : 如何設定自動給 Subinventory

當 User 操作 RMA 單時, 選擇了 Reference 後, Book order 會發生 "Quantity cannot be greater than original quantity" 的錯誤訊息. 不過, 標準畫面很難查的出來是哪一張 RMA 單 Reference 了同一張單子, 所以無法處理.
SELECT OOH1.ORDER_NUMBER A1
, OOL1.ORDERED_ITEM A2
, OOL1.LINE_NUMBER A3
, OOL1.ORDERED_QUANTITY A4
, OOH1.FLOW_STATUS_CODE A5
, OOH2.ORDER_NUMBER B1
, OOL2.LINE_NUMBER B1
, OOL2.ORDERED_QUANTITY B3
, OOH2.FLOW_STATUS_CODE B4
, OOH3.ORDER_NUMBER C1
, OOL3.LINE_NUMBER C2
, OOL3.ORDERED_QUANTITY C3
, OOH3.FLOW_STATUS_CODE C4
FROM ONT.OE_ORDER_HEADERS_ALL OOH1
, ONT.OE_ORDER_LINES_ALL OOL1
, ONT.OE_ORDER_LINES_ALL OOL2
, ONT.OE_ORDER_HEADERS_ALL OOH2
, ONT.OE_ORDER_LINES_ALL OOL3
, ONT.OE_ORDER_HEADERS_ALL OOH3
WHERE OOH1.HEADER_ID = OOL1.HEADER_ID
AND OOL1.LINE_CATEGORY_CODE = 'RETURN'
AND OOH1.ORDER_NUMBER = '當前的訂單編號'
AND OOL1.REFERENCE_LINE_ID = OOL2.LINE_ID
AND OOH2.HEADER_ID = OOL2.HEADER_ID
AND OOL1.REFERENCE_LINE_ID = OOL3.REFERENCE_LINE_ID(+)
AND OOH3.HEADER_ID(+) = OOL3.HEADER_ID
AND OOH1.ORDER_NUMBER <> OOH3.ORDER_NUMBER

OM 模組底下有一個功能 : Processing Constraints (路徑 : Setup > Rules > Security > Processing Constraints), 此功能是用來定義, 在 OM 模組所屬的畫面, 什麼時候要做什麼, 什麼時候不能做什麼
畫面很簡單, 最上面的部份就是選擇 Application 與 畫面 (Entity)
中間的部分就是設定 Constraints 的地方 :

上週發生了一個新的 Import Error : "Ship To is required on a booked order", 一直看 Ship To 相關欄位都看不出所以然, 後來才發現是別的欄位造成的
OM 模組有一個 Package : OE_DEPENDENCIES, 是 Oracle 用來定義 OM 畫面每一個欄位的主從關係, 從這個 Package 可以知道, 當使用者更新了某欄位, 會連帶哪些欄位會"重新帶出"預設值, 例如 : 
l_index := OE_HEADER_UTIL.G_INVOICE_TO_ORG * G_MAX ;
g_dep_tbl(l_index ).attribute   := OE_HEADER_UTIL.G_PAYMENT_TERM;
g_dep_tbl(l_index +1 ).attribute  := OE_HEADER_UTIL.G_INVOICE_TO_CONTACT;
g_dep_tbl(l_index +2 ).attribute  := OE_HEADER_UTIL.G_PRICE_LIST;
g_dep_tbl(l_index +3 ).attribute  := OE_HEADER_UTIL.G_FOB_POINT;
g_dep_tbl(l_index +4 ).attribute  := OE_HEADER_UTIL.G_FREIGHT_TERMS;
g_dep_tbl(l_index +5 ).attribute  := OE_HEADER_UTIL.G_TAX_EXEMPT_NUMBER;

在之前的某篇文章有提到, Credit Check 中有一個功能 : Override Manual Release, 一直到昨天才發現我對這個功能的意義是錯的
如果有勾選 Override Manual Release, OK, 在 Days to Honor Manual Release 所設定的天數之內, 不會再被 Credit Hold, 超過此天數就會再重新被 Hold (當然, 必須要觸發到)
那, 沒有勾選 Override Manual Release 呢?