[OM] create Deliveries時出錯 The action can not be performed because the selected records could not be locked.

在做create Deliveries時出錯
Error: The action can not be performed because the selected records could not be locked.

這應該是個bug，一般是WSH_DELIVERY_DETAILS裡的欄位多了空格尤其是ITEM_DESCRIPTION，將空格去掉，重新執行就可以解決了。

[OM] Customer Profile Amount 新增

剛好遇到有很多客戶需要批次建Customer Profile Amount ，下面是個範例，可以參考看看：

DECLARE
   P_CUSTOMER_PROFILE_REC    HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE;
   P_CUST_ACCT_RELATE_REC    HZ_CUST_ACCOUNT_V2PUB.CUST_ACCT_RELATE_REC_TYPE;
   P_CUST_PROFILE_AMT_REC    HZ_CUSTOMER_PROFILE_V2PUB.CUST_PROFILE_AMT_REC_TYPE;
  
   P_PROFILE_CLASS_ID NUMBER;
  
   V_CUST_ACCOUNT_ID           NUMBER;
   V_ACCOUNT_NUMBER            VARCHAR2(20);
   V_PARTY_ID                  NUMBER;
   V_PARTY_NUMBER              VARCHAR2(20);
   V_PROFILE_ID                NUMBER;
   V_RETURN_STATUS             VARCHAR2(1);
   V_MSG_COUNT                 NUMBER;
   V_MSG_DATA                  VARCHAR2(2000);
   V_CUST_ACCOUNT_PROFILE_ID   NUMBER;
   V_CUST_ACCT_PROFILE_AMT_ID  NUMBER;

BEGIN
   FND_GLOBAL.APPS_INITIALIZE(USER_ID         => 3631,
                                 RESP_ID      => 50279,
                                 RESP_APPL_ID => 660);
                                
   -- CREATE CUSTOMER PROFILE RECORD
   P_CUSTOMER_PROFILE_REC.PARTY_ID := 123433;
   P_CUSTOMER_PROFILE_REC.CUST_ACCOUNT_ID :=152127;
   P_CUSTOMER_PROFILE_REC.SITE_USE_ID := 7459;
   P_CUSTOMER_PROFILE_REC.CREATED_BY_MODULE := 'TCA_V2_API';
  
   HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE (
   fnd_api.g_true, --'T',
   P_CUSTOMER_PROFILE_REC ,
   fnd_api.g_true, --'T',
   V_CUST_ACCOUNT_PROFILE_ID ,
   V_RETURN_STATUS ,
   V_MSG_COUNT,
   V_MSG_DATA );

   -- CREATE CUSTOMER PROFILE AMOUNT RECORD
   P_CUST_PROFILE_AMT_REC.CUST_ACCOUNT_PROFILE_ID := V_CUST_ACCOUNT_PROFILE_ID;
   P_CUST_PROFILE_AMT_REC.CUST_ACCOUNT_ID := 152127;
   P_CUST_PROFILE_AMT_REC.SITE_USE_ID := 7459;
   P_CUST_PROFILE_AMT_REC.CURRENCY_CODE := 'TWD';
   P_CUST_PROFILE_AMT_REC.TRX_CREDIT_LIMIT := '';
   P_CUST_PROFILE_AMT_REC.OVERALL_CREDIT_LIMIT := '';
   P_CUST_PROFILE_AMT_REC.CREATED_BY_MODULE := 'TCA_V2_API';
  
   HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUST_PROFILE_AMT (
   fnd_api.g_true, --'T',
   fnd_api.g_true, --'T',
   P_CUST_PROFILE_AMT_REC ,
   V_CUST_ACCT_PROFILE_AMT_ID ,
   V_RETURN_STATUS ,
   V_MSG_COUNT ,
   V_MSG_DATA
   );

   COMMIT;
END;

[OM]OM、AR模組Base Table

Order Management (OM)：

1.OE_ORDER_HEADERS_ALL    -- Order Header info
2.OE_ORDER_LINES_ALL      -- Order line info
3.OE_TRANSACTION_TYPES_TL -- Order type info
4.OE_ORDER_HOLDS          -- Order Hold info
4.OE_HOLDS_ALL              -- Order Hold info
5.OE_HOLD_SOURCES           -- Order Hold source info
6.OE_HOLD_RELEASES        -- Hold Release info
7.WSH_DELIVERY_DETAILS    -- Delivery Detial Info
8.WSH_NEW_DELIVERIES      -- Delivery Header info
9.WSH_DELIVERY_ASSIGNMENTS -- Delivery Assignments info
10.WSH_TRIP_STOPS          -- Delivery trips info
11.HZ_CUST_ACCOUNTS        -- Customer info
12.HZ_PARTIES              -- Party info
13.HZ_CUST_SITE_USES_ALL   -- Customer site use info
14.HZ_CUST_ACCT_SITES_ALL  -- Customer Site Acct info
15.HZ_PARTY_SITES          -- Party site info
16.HZ_LOCATIONS            -- Customer Site Adderess
17.WSH_LOOKUPS             -- Shipping lookup info

Accounts Receivables (AR)：

1.RA_CUSTOMER_TRX_ALL            -- Receivable transaction info
2.RA_CUSTOMER_TRX_LINES_ALL      -- Transaction lines info
3.RA_CUST_TRX_LINE_GL_DIST_ALL   -- Transaction distribution info
4.AR_RECEIVABLE_APPLICATIONS_ALL -- Receiving application info
5.AR_CASH_RECEIPTS_ALL           -- Cash Receipt info
6.AR_TERMS                       -- Receivable Terms
7.HZ_CUST_ACCOUNTS               -- Customer info
8.HZ_PARTIES                     -- Party info
9.HZ_CUST_SITE_USES_ALL          -- Customer site use info
10.HZ_CUST_ACCT_SITES_ALL        -- Customer Site Acct info
11.HZ_PARTY_SITES                -- Party site info
12.HZ_LOCATIONS                  -- Customer Site Adderess

[OM] 訂單Line狀態：AWAITING INVOICE INTERFACE - PENDING COMPLETE DELIVERY

環境：EBS 11.5.10.2

簡述：訂單已出貨成功，存貨帳已扣，AR INTERFACE 無資料，懷疑workflow拋轉

            失敗沒Rollback。經過Load TAR後，測試結果是workflow拋轉另一張訂單失

            敗造成同一批次的其他單子卡單。

解決方案：

1. 先select wf_item_activity_statuses_v，找出error的order line id。

2.

set serveroutput on
Declare
l_line_id NUMBER := &line_id;
l_org_id NUMBER;
l_count NUMBER;
l_result VARCHAR2(30);
l_activity_label VARCHAR2(30);
l_file_val VARCHAR2(200);

Begin

fnd_client_info.set_org_context('&org_id');
oe_debug_pub.debug_on;
oe_debug_pub.initialize;
l_file_val := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.setdebuglevel(5);

select activity_label
into l_activity_label
from wf_item_activity_statuses_v
where item_type='OEOL'
and activity_name = 'INVOICE_INTERFACE'
and activity_status_code = 'ERROR'
and item_key = to_char(l_line_id);

dbms_output.put_line('activity label : '||l_activity_label);

OE_Standard_WF.OEOL_SELECTOR
(p_itemtype => 'OEOL'
,p_itemkey => to_char(l_line_id)
,p_actid => 12345
,p_funcmode => 'SET_CTX'
,p_result => l_result
);

wf_engine.handleError('OEOL', to_char(l_line_id), l_activity_label, 'RETRY',nu
ll);
dbms_output.put_line('File name '||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
End;
/
commit;

 

Drop Ship Order - Requisition Import 失敗

最近遇到Drop Ship的訂單，在開出PR時會失敗，此前的模組負責人都是請user重新開立，這次模組換人維護後，才知道user一直有這個困擾，查了一下metalink後，找到了解決方法。

1.利用SQL查詢出Requisition Import的原因：

select pri.transaction_id req_tranx_id
, msi.segment1 item_number
, oeh.order_number sales_order
, oel.LINE_NUMBER
, pri.process_flag process_flag
, pri.request_id conc_request_id
, poi.error_message
, ipr.ORGANIZATION_CODE
from mtl_system_items_b msi,
mtl_parameters ipr,
po_interface_errors poi,
oe_drop_ship_sources oed,
po_requisitions_interface_all pri,
oe_order_headers_all oeh,
oe_order_lines_all oel
where oeh.header_id = oed.header_id and
oel.header_id = oed.header_id and
oel.line_id = oed.line_id and
pri.transaction_id = poi.interface_transaction_id and
pri.interface_source_line_id = oed.drop_ship_source_id and
pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID and
pri.item_id = msi.inventory_item_id and
msi.organization_id = ipr.ORGANIZATION_ID and
oeh.order_number = :p_order_number

2.修正問題後（一般都是漏掉採購員等），update UPDATE PO_REQUISITIONS_INTERFACE_ALL

UPDATE PO_REQUISITIONS_INTERFACE_ALL
SET PROCESS_FLAG = NULL,
REQUISITION_HEADER_ID = NULL,
REQUISITION_LINE_ID = NULL,
REQ_DISTRIBUTION_ID = NULL
WHERE PROCESS_FLAG = 'ERROR'
AND INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
and REQUISITION_LINE_ID = :P_REQUISITION_ID -- 條件依據個人習慣

3.重送Requisition Import，完成。

月結作業 - OM 訂單管理系統

Step 1: Complete All Transaction for Current Period

Confirm Transaction had been Completed

A. Enter all S/O Transactions

B. Enter all RMA Order Transactions

C. Confirm Shipment Transactions

D. Confirm RMA Receipt/Receiving Transactions

E. Response to Workflow Approval Notifications

Step 2: Complete S/O Import Transactions for Current Period (Options)

External S/O Import Interface (Like EDI or XML Order)

A. Run “Order Import Request” in Oracle Order Management.

B. If have Error, Use “Import Order Collection” to review Import S/O

Oracle Internal S/O Import Interface (Like EDI or XML Order)

A. Run “Create Internal S/O” in Oracle Purchasing

B. Run “Order Import Request” in Oracle Order Management

C. If have Error, Use “Import Order Collection” to correct Import S/O.

Step 3: Review Held Order

Review Hold Order

1. Run “Orders on Credit Check Hold Report”

2. If have Held Order, following SOP to Process.

Step 4: Review Backorders

Review BackOrder Report

1. Run “Backorder Detail Report “ in Oracle Shipping

2. If have some urgent Shipment need to do, following SOP to do the Picking and Shipping process.

Step 5: Reconcile to Oracle Inventory (Inv)

Confirm all Shipment Interface to Oracle Interface

1. Check there is no any Delivery in “Interface Trip Stop”.

2. If have some Delivery, following SOP to do the Shipment process.