Oracle EBS: 料號建立後, UOM是否可修改?

問題: 料號建立後, 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)

Oracle EBS: 處理未產生分錄的交易(Return to supplier)

因倉庫重複作業, 造成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 10開啟Telnet功能

[開始]

-> 設定

  -> 應用程式

    -> 應用程式與功能

      -> 相關設定

        -> 程式和功能

          -> 開啟或關閉Windows功能

這也藏太深了...

Oracle EBS: 以API更改concurrent request hold狀態

問題: 以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)

Oracle EBS: 已有執行ad_ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX'), 但修改org item時, 存檔仍需數分鐘才完成

問題: 已有執行ad_ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX'), 但修改org item時, 存檔仍需數分鐘才完成

狀況:

1.CTXSYS.CTX_PENDING只有數百筆, select速度極快

2.CTXSYS.DR$WAITING有3萬多筆, 但select耗時近30秒, 即使table沒資料時也是同樣狀況

解決方案: 要執行alter table ctxsys.DR$WAITING move

--steps A

begin

      ad_ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX');

 end;

--steps B

alter table ctxsys.DR$WAITING move

或

ALTER TABLE CTXSYS.DR$WAITING SHRINK SPACE

減少CTXSYS.DR$WAITING,CTXSYS.DR$PENDING table中筆數的方法:

connect as ctxsys:

 select u.username, i.idx_name

 from dr$index i, dba_users u

 where u.user_id=i.idx_owner#

 and idx_id in (select pnd_cid from ctxsys.dr$pending);

exec ctx_ddl.sync_index('USERNAME.INDEX');

Ref:

1.请教：在新建物料和修改物料时非常慢是什么原因

http://www.itpub.net/thread-1440660-1-1.html

2.Can Users Manually Shrink DR$WAITING And DR$PENDING in the CTXSYS schema? (Doc ID 2447229.1)

3.E-Business Suite Applications Technology Stack Database Contains Millions Of Records In CTXSYS.DR$PENDING And CTXSYS.DR$WAITING Tables (Doc ID 382809.1)