夏普視野

[INV] Item update，利用API更新

2015-03-24T14:23:00.000+08:00

因為要開發在另一套BPM系統，所以這次借用API方式更新，網路上常用有兩種API:
ego_item_pub.process_items 和 inv_item_grp.update_item

目前測試兩種都可行，ego_item_pub也可以處理eng item，inv_item_grp我沒試過行不行。
錯誤的話Status會Return：E，基本上若是Return：U 就要debug了，一般只有S：成功或失敗，
偏偏我家的環境出現一個U，抓了半天才發現有人寫的db trigger object失效了＠＠"。

限制更新欄位跟interface一樣：
PRIMARY_UNIT_OF_MEASURE
...

TEST：
1. EGO_ITEM_PUB
DECLARE
L_ITEM_TABLE EGO_ITEM_PUB.ITEM_TBL_TYPE;
X_ITEM_TABLE EGO_ITEM_PUB.ITEM_TBL_TYPE;
V_MSG_CNT NUMBER(20);
V_RETURN VARCHAR2(1);
V_MSG_LIST ERROR_HANDLER.ERROR_TBL_TYPE;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(123, 50200, 401);

L_ITEM_TABLE(1).TRANSACTION_TYPE := 'UPDATE';
L_ITEM_TABLE(1).INVENTORY_ITEM_ID := 18000; -- INVENTORY_ITEM_ID;
L_ITEM_TABLE(1).ORGANIZATION_ID := 80; -- ORGANIZATION_ID;
L_ITEM_TABLE(1).ATTRIBUTE1 := 'TEST1';

EGO_ITEM_PUB.PROCESS_ITEMS (P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_TRUE,
P_ITEM_TBL => L_ITEM_TABLE,
X_ITEM_TBL => X_ITEM_TABLE,
--P_ROLE_GRANT_TBL => EGO_ITEM_PUB.G_MISS_ROLE_GRANT_TBL,
X_RETURN_STATUS => V_RETURN,
X_MSG_COUNT => V_MSG_CNT
);

DBMS_OUTPUT.PUT_LINE ('RETURN STATUS: ' || V_RETURN);
DBMS_OUTPUT.PUT_LINE ('ERROR MESSAGES: ');

ERROR_HANDLER.GET_MESSAGE_LIST (X_MESSAGE_LIST => V_MSG_LIST);

FOR I IN 1 .. V_MSG_LIST.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (V_MSG_LIST(I).MESSAGE_TEXT);
END LOOP;

END;

2. INV_ITEM_GRP
DECLARE
L_ITEM_REC INV_ITEM_GRP.ITEM_REC_TYPE;
L_ERROR_TBL INV_ITEM_GRP.ERROR_TBL_TYPE;
X_ITEM_REC INV_ITEM_GRP.ITEM_REC_TYPE;
L_RETURN_STATUS VARCHAR2(40);
BEGIN
L_ITEM_REC.INVENTORY_ITEM_ID := 18000;
L_ITEM_REC.ORGANIZATION_ID := 80;
L_ITEM_REC.PLANNER_CODE := 'EAD';
INV_ITEM_GRP.UPDATE_ITEM(P_COMMIT => FND_API.G_TRUE,
P_LOCK_ROWS => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_ITEM_REC => L_ITEM_REC,
X_ITEM_REC => X_ITEM_REC,
X_RETURN_STATUS => L_RETURN_STATUS,
X_ERROR_TBL => L_ERROR_TBL);
IF L_ERROR_TBL.COUNT() > 0 THEN
FOR I IN 1 .. L_ERROR_TBL.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(L_ERROR_TBL(I).MESSAGE_TEXT);
DBMS_OUTPUT.PUT_LINE(L_ERROR_TBL(I).MESSAGE_NAME);
END LOOP;
END IF;
END;

很久沒有更新了...

2015-03-23T14:21:00.000+08:00

當初，申請這個blog的目的只是為了紀錄EBS客製開發過程常用到的資訊，好讓一些和我一樣會遇到相同問題的朋友做為參考。
因為當時有關EBS客製開發的中文支援並不好找，問顧問有的收費還很誇張，加上自己英文很破，有些熱心的原文網站的範例也都不完整，需要自己測試。這幾年發現網路上這類的文章，已經足夠大家參考了，有的還非常專業，說文解字的，所以很久都沒有上來分享自己工作上一些常用的資訊了。
還有就是這幾年負責的都是各項年度專案的開發，也不在侷限於EBS客製這一快，大都是整合EBS、BPM、WEB、Reporting Service和手持裝置的整合開發，所以也很少有機會分享自己的小小心得。

[EBS] EBS r11無法在IE11瀏灠器下執行使用

2014-03-10T16:20:00.001+08:00

EBS r11無法在IE11瀏灠器下執行使用。
解決方法：
開啟IE11瀏灠器的功能選單內 [工具] > [相容性檢視設定]，加入EBS網址，重開IE既可。

[FORM] FRM-40654: Record has been updated. Requery block to see change

2014-01-14T16:23:00.001+08:00

不知道大家會不會常遇到FORM在修改資料時，就給你出現一句：
FRM-40654: Record has been updated. Requery block to see change

怎查都沒有lock時，一般都是欄位值前後有空白造成的，解決方法很簡單，
找出對應的table，把空白值update掉就好了。

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

2013-10-30T10:44:00.000+08:00

在做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 新增

2013-05-13T16:59:00.001+08:00

剛好遇到有很多客戶需要批次建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;

Form Personalization 裡判斷DB原始值

2013-05-10T16:35:00.001+08:00

有時候我們需要在Form Personalization 裡，取得某欄位原始值來做判斷時，可以利用：

${ITEM.BLOCK_NAME.ITEM_NAME.DATABASE_VALUE}

範例：
NVL(${ITEM.W_JOBS.START_QUANTITY.DATABASE_VALUE},0) != 0

[EBS] FNDLOAD

2012-10-04T16:27:00.001+08:00

ERP系統要新開一家公司，所有程式、權限環境同另一家公司，所以借助FNDLOAD。

關於FNDLOAD網路上已經也很多參考範例，不免俗還是貼一下：

利用prodmgr登入

Lookups
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct **.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME='**' LOOKUP_TYPE='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct **.ldt

Value Set
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct **.ldt VALUE_SET FLEX_VALUE_SET_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct **.ldt

Concurrent Program
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct **.ldt PROGRAM CONCURRENT_PROGRAM_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct **.ldt

Menu
FNDLOAD apps/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt MENU MENU_NAME='**'
FNDLOAD apps/ 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt

Responsibility
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct **.ldt FND_RESPONSIBILITY RESP_KEY='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct **.ldt

Functions/Forms
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt FUNCTION FUNCTION_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/dlseh 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct **.ldt

Profile
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct **.ldt PROFILE FND_PROFILE_OPTION_VALUES PROFILE_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct **.ldt

Request Group
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct **.ldt REQUEST_GROUP REQUEST_GROUP_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct **.ldt

Attachement
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afattach.lct **.ldt FND_ATTACHMENT_FUNCTIONS FUNCTION_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afattach.lct **.ldt

Personalization
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct **.ldt FND_FORM_CUSTOM_RULES FUNCTION_NAME='**' APPLICATION_SHORT_NAME='**'
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct **.ldt

其實他對應的.lct 是可以改的程式碼，我是都直接改寫，還有匯出來的檔案再改成新公司的程式編碼再匯入，Concurrent Program只要匯出Concurrent Program既可，他會一併匯出對應的Value Set，建議依照模組匯出較好處理。例如：

FNDLOAD apps/dorp 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_FTAP.ldt PROGRAM CONCURRENT_PROGRAM_NAME='XXP%'

[COST] Close Discrete Jobs客製

2012-10-02T13:21:00.001+08:00

系統越來越肥了，user反應工單關閉作業（Close Discrete Jobs）已經跑不動了，老是跑到一半不是DB的Undo不然就是Rollback Segment滿了掛了。所以只好寫了這隻小程式，因為r11工單關閉沒有Interface可用，所以借助WIP_DJ_CLOSE_TEMP使用，我把它放入排程，每天執行，範例如下：

-- 模擬使用者環境
fnd_global.apps_initialize(user_id => 1234, resp_id => 1234, resp_appl_id => 1234);

-- 完工
INSERT INTO WIP_DJ_CLOSE_TEMP(
                             WIP_ENTITY_ID,
                             ORGANIZATION_ID,
                             WIP_ENTITY_NAME,
                             STATUS_TYPE,
                             PRIMARY_ITEM_ID,
                             ACTUAL_CLOSE_DATE,
                             GROUP_ID)
                      SELECT
                             WE.WIP_ENTITY_ID,
                             WE.ORGANIZATION_ID,
                             WE.WIP_ENTITY_NAME,
                             WDJ.STATUS_TYPE,
                             WDJ.PRIMARY_ITEM_ID,
                             SYSDATE,123
                        FROM WIP_ENTITIES WE,
                             WIP_DISCRETE_JOBS WDJ
                       WHERE WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
                         AND WE.ORGANIZATION_ID = 123
                         AND STATUS_TYPE = 4 -- Complete
                         AND DATE_COMPLETED <= SYSDATE
                         AND NOT EXISTS (SELECT 1
                                           FROM WIP_MOVE_TXN_INTERFACE WMTI
                                          WHERE WMTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID)
                         AND NOT EXISTS (SELECT 1
                                           FROM WIP_COST_TXN_INTERFACE WCTI
                                          WHERE WCTI.WIP_ENTITY_ID = WE.WIP_ENTITY_ID);

-- 執行Close Discrete Jobs
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST
               ('WIP','WICDCL','','',FALSE,
                123,'','','','','','','','','',
                '','','',123,2,'','','',1,'',
                CHR(0),...);

COMMIT ;

[EBS] ORA-20001: FND-ORG_ID PROFILE CANNOT READ

2012-10-02T13:07:00.001+08:00

今天測試環境崩潰了，還好是測試環境：

原來已經有一堆受害者了，而且造成錯誤的原因竟然這麼簡單，只要把Profile「MO: Operating Unit」site level的值給清掉。

解決方法：

Oracle error -20001: ORA-20001: FND-ORG_ID PROFILE CANNOT READ: N, OPTION, MO: Operating Unit has been detected in FND_GLOBAL.INITIALIZE.

Solution

Following are steps to change the profile option from backend:

1. Get the Profile option ID using the following query:

SQL> Select PROFILE_OPTION_ID,PROFILE_OPTION_NAME from fnd_profile_options

where PROFILE_OPTION_NAME ='ORG_ID';

2. Run the following procedure which changes the profile option:

DECLARE

stat boolean;

BEGIN

dbms_output.disable;

dbms_output.enable(100000);

stat := FND_PROFILE.SAVE('ORG_ID',<enter the Profile ID from above query>, 'SITE');

IF stat THEN

dbms_output.put_line( 'Stat = TRUE - profile updated' );

ELSE

dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );

END IF;

commit;

END;

3) Restart the apache server

GL - AP Table關連

2012-07-30T17:18:00.001+08:00

AP Invoice:

AP Payment:

[GL]GL Interface 簡單範例

2012-07-30T13:19:00.001+08:00

Interface table:
GL_INTERFACE

Base table:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program:
Journal Import

簡單範例：
1.
SELECT GL_JOURNAL_IMPORT_S.NEXTVAL
INTO V_GROUP_ID
FROM DUAL;

SELECT CODE_COMBINATION_ID
INTO V_CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS_V
WHERE CHART_OF_ACCOUNTS_ID = XXXX;

INSERT INTO GL_INTERFACE (
         STATUS,
         SET_OF_BOOKS_ID,
         ACCOUNTING_DATE,
         CURRENCY_CODE,
         DATE_CREATED,
         CREATED_BY,
         ACTUAL_FLAG,
         USER_JE_CATEGORY_NAME,
         USER_JE_SOURCE_NAME,
         BUDGET_VERSION_ID,
         CODE_COMBINATION_ID,
         PERIOD_NAME,
         ENTERED_DR,
         GROUP_ID)
VALUES ('NEW',
         :PARAMETER.SET_OF_BOOKS_ID,
         SYSDATE,
         'TWD',
         SYSDATE,
         Fnd_Global.USER_ID,
         'B',
         'Accrual',
         'Spreadsheet',
         'test',
         V_CODE_COMBINATION_ID,
         V_PERIOD_NAME,
         100,
         V_GROUP_ID);

2.執行Journal Import

[INV]INV模組Base Table

2012-07-30T13:00:00.001+08:00

Invenvtory (INV):
------------------------
1.MTL_SYSTEM_ITEMS_B           -- Master item info
2.MTL_ONHAND_QUANITTIES        -- Item onhand qty info
3.MTL_RESERVATIONS             -- Item Reservation info
4.MTL_MATERIAL_TRANSACTIONS    -- Item Transaction info
5.MTL_ITEM_LOCATIONS           -- Item location info
6,MTL_CATEGERIES               -- Item Category info
7.MTL_ITEM_CATEGORIES          -- Invemtry Categry
8.MTL_SECONDARY_INVENTORIES    -- Subinventories info
9.ORG_ORGANIZATION_DEFINITIONS -- Organizaition info
10.MTL_TRANSACTION_ACCOUNTS    -- Item Transaction info
11.MTL_TXN_SOURCE_TYPES        -- Item Transaction sources
12.MTL_PARAMETERS              -- Inventory Parameters

[PO]PO、AP模組Base Table

2012-07-30T12:03:00.001+08:00

Purchase Order : (PO)
1.PO_REQUISITION_HEADERS_ALL -- Requisition Header info
2.PO_REQUISITION_LINES_ALL   -- Requisition Lines info
3.PO_REQ_DISTRIBUTIONS_ALL   -- Requisition Distribution info
4.PO_HEADERS_ALL             -- PO Header Info
5.PO_LINES_ALL               -- PO Line Info
6.PO_LINE_LOCATIONS_ALL      -- PO Line Shipment info
7.PO_DISTRIBUTIONS_ALL       -- PO Distribution info
8.RCV_SHIPMENT_HEADERS       -- Receiving header info
9.RCV_SHIPMENT_LINES         -- Receiving Lines info
10.RCV_TRANSACTIONS          -- Receiving transationd info
11.PO_VENDORS                -- Supplier Header info
12.PO_VENDOR_SITES_ALL       -- Supplier Site info
13.PO_VENDOR_SITE_CONTACTS   -- Supplier Site Contact info
14.HR_LOCATIONS              -- Supplier Site Address

Accounts Payable: (AP)
---------------------
1.AP_INVOICES_ALL               -- Invoice Header info
2.AP_INVOICE_DISTRIBUTIONS_ALL -- Invoice Line info
3.AP_CHECKS_ALL                 -- Check info
4.AP_INVOICE_PAYMENTS_ALL       -- Invoice Payment info
5.AP_PAYMENT_SCHEDULES_ALL      -- Payment Schedule info
6.AP_HOLDS_ALL                  -- Invoice Holds info
7.AP_LOOKUP_CODES               -- Payable lookup info
8.PO_VENDORS                    -- Supplier Header info
9.PO_VENDOR_SITES_ALL           -- Supplier Site info
10.PO_VENDOR_SITE_CONTACTS      -- Supplier Site Contact info
11.HR_LOCATIONS                 -- Supplier Site Address
12.AP_BANKS                     -- Bank Info
13.AP_BANK_BRANCHES             -- Bank Branch info
14.AP_AE_HEADERS_ALL            -- Accounitng header info
15.AP_AE_LINES_ALL              -- Accounting Lines info
16.AP_AE_ACCOUNTING_EVETS       -- Accounting events info
17.AP_TERMS                     -- Payment Terms

[OM]OM、AR模組Base Table

2012-07-30T11:47:00.001+08:00

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

[INV] 透過MTL_SYSTEM_ITEMS_INTERFACE將Item欄位清成NULL

2012-07-30T11:34:00.001+08:00

Updating Item Attributes to NULL
The method to update these columns to NULL is to use the following values：
for Numeric fields: insert -999999
for Character fields: insert '!'
for Date fields: the above list does not include any updateable date fields

[EBS]查詢使用者的職責權限

2012-05-01T13:35:00.001+08:00

SELECT FU.USER_NAME, FR.RESPONSIBILITY_NAME, FR.LANGUAGE, FURG.START_DATE, FURG.END_DATE
FROM FND_USER_RESP_GROUPS_DIRECT FURG,
       FND_USER FU,
       FND_RESPONSIBILITY_TL FR
WHERE FU.USER_NAME = :USER_NAME
   AND FURG.USER_ID = FU.USER_ID
   AND FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID

[Form Personalization] Function not available to this responsibility.Change responsibilities or contact your System Administrator

2011-11-28T16:16:00.001+08:00

當你想使用Personalization要去執行一支Report\ Requests, 一般會設定成

Launch SRS Form

不過若你會遇到:

Function not available to this responsibility.Change responsibilities or contact your System

Administrator

那應該是你使用的這個responsibility對應的MENU少了一支FORM:

FND_FNDRSRUN (Submit: Requests) function

把他加進你的MENU就可以使用Launch SRS Form

了

[INV] ITEM SUPPLY/DEMAND 客製運用

2011-06-18T09:37:00.001+08:00

DECLARE

   -- 測試料號
   CURSOR C1 IS
   SELECT MSI.INVENTORY_ITEM_ID,MSI.ORGANIZATION_ID SHIP_FROM_ORG_ID
     FROM MTL_SYSTEM_ITEMS MSI
    WHERE MSI.ORGANIZATION_ID = 104
      AND MSI.SEGMENT1 = 'MK001';

   V_SEQ_NUM        NUMBER;
   V_CUTOFF_DATE_J NUMBER;
   V_TOTAL_TEMP     VARCHAR2(30);
   V_AVAIL_QTY_TEMP VARCHAR2(30);

BEGIN
   -- 取得 CLIENT 端 PROFILE 值
   FND_GLOBAL.APPS_INITIALIZE(1168, 50270, 401);

   -- 設定存貨組織
   INV_GLOBALS.SET_ORG_ID(84);

   SELECT TO_CHAR (SYSDATE, 'J')
     INTO V_CUTOFF_DATE_J
     FROM DUAL;

   SELECT MTL_DEMAND_INTERFACE_S.NEXTVAL
     INTO V_SEQ_NUM
     FROM DUAL;

V_TOTAL_TEMP := 0;
V_AVAIL_QTY_TEMP := 0;

   FOR I IN C1 LOOP
       IF (INV_TM.LAUNCH('INXDSD',
          'GROUP_ID=' ||
          TO_CHAR(V_SEQ_NUM) ||
          ' ' ||
          'ORGANIZATION_ID=' ||
          TO_CHAR(I.SHIP_FROM_ORG_ID) ||
          ' ' ||
          'INVENTORY_ITEM_ID=' ||
          TO_CHAR(I.INVENTORY_ITEM_ID) ||
          ' ' ||
          'ONHAND_SOURCE=' ||
          '1'||
          ' ' ||
          'CUTOFF_DATE="' ||
          TO_CHAR(V_CUTOFF_DATE_J) ||
          '" ' ||
          'MRP_STATUS=' ||
          '1' ||
          ' ' ||
          'ONHAND_FIELD=V_TOTAL_TEMP ' ||
          'AVAIL_FIELD=V_AVAIL_QTY_TEMP',
          V_TOTAL_TEMP,
          V_AVAIL_QTY_TEMP) = FALSE) THEN

          DBMS_OUTPUT.PUT_LINE('FAILED');
       ELSE
          DBMS_OUTPUT.PUT_LINE('SUCCESS');
          DBMS_OUTPUT.PUT_LINE(V_TOTAL_TEMP);
          DBMS_OUTPUT.PUT_LINE(V_AVAIL_QTY_TEMP);
       END IF;
   END LOOP;
END;

取得結果：
SELECT * FROM MTL_SUPPLY_DEMAND_TEMP
WHERE SEQ_NUM = 0;

[PL/SQL] 數值金額轉英文範例

2011-04-28T13:30:00.001+08:00

很久以前google搜尋修改的數值金額轉英文範例，給友需要的朋友

create or replace function number2eng_sf(in_number in number ) return varchar2 is

type eArray is table of varchar2(255);

l_str eArray := eArray('',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ');

l_num varchar2(50) default trunc(in_number);
h_number number;
v_total varchar2(4000);

begin
   h_number := round(in_number*100)/100;
   for i in 1 .. l_str.count loop
       exit when l_num is null;

       if (substr(l_num, length(l_num)-2, 3) != 0 ) then
          v_total := to_char(to_date(substr(l_num, length(l_num)-2, 3),'J' ),'Jsp' )||l_str(i)||v_total;
       end if;

       l_num := substr( l_num, 1, length(l_num)-3 );
   end loop;

   if v_total is null then
      v_total := 'Zero' ;
   end if;

   if trunc( h_number ) > 1 then
      v_total := v_total || ' Dollars ';
   else
      v_total := v_total || ' Dollar ';
   end if;

   if to_char( h_number ) like '%.%' then
      l_num := substr(h_number,instr(h_number,'.')+1);
      if length(l_num) = 1 then
         l_num := l_num || '0';
      end if;

      if l_num > 0 then
         if l_num > 1 then
            v_total := v_total ||'And '||l_num||' Cents ';
         else
            v_total := v_total ||'And '||l_num||' Cent ';
         end if;
      end if;
   end if;

return v_total;
end;

[Oracle ERP] 讓EBS可以在IE8裡多重登入

2010-06-03T13:04:00.001+08:00

直接改iexplore捷徑，加入-nomerge ("C:\Program Files\Internet Explorer\iexplore.exe" –nomerge )，完成。

[wip] 很奇怪的PENDING MOVE TRANSACTION

2010-03-17T08:36:00.001+08:00

今天ERP發生了非常奇怪的WIP工單PENDING一直解不掉，後來發現PENDING MOVE TRANSACTION 裡工單的TRANSACTION ID與WIP_MOVE_TRANSACTIONS的TRANSACTION ID不一致，非常奇怪的事發生了。在11.5.10.2第一次遇到這神奇事件。

解決的方案就是把PENDING的工單TRANSACTION ID改成與WIP_MOVE_TRANSACTIONS的TRANSACTION ID一致，再推一次既可解開。

建立外部網址連結的Function

2010-01-22T10:19:00.001+08:00

有時客製模組需要搭配一些額外的報表，比如像是Reporting Service 的報表，這時就會有直接建立外部網址連結的需求。設定還蠻簡單的：

1.建立一個Form Function， Type選SSWA plsql function that opens a new window (Kiosk Mode)。

2.Web Host頁面，在Host Name輸入需要連結的網址，例如：http://www.google.com。

3.若有需要指向網站裡的網頁或子目錄，則需在Web HTML頁面，HTML Call欄位輸入，例如/abc/。

取消ERP MENU上的Close Other Forms

2010-01-13T11:42:00.001+08:00

隨著資料的增長，ERP系統效能及資源已不如以往，以前開放user可以開多個Form作業，現在為了節省資源，所以又得把Close Other Forms的功能拿掉。

把要鎖定的menu加入 Navigator: Disable Multiform 這個Function存檔既可。

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

2009-10-07T10:24:00.001+08:00

環境：EBS 11.5.10.2

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

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

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

解決方案：

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

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;