Oracle EBS: 修改AR due date

問題: 月結payment term的due date計算有誤, 每月26日之後的交易都少算一個月.

修改:

UPDATE AR_PAYMENT_SCHEDULES_ALL A

   SET A.DUE_DATE = ADD_MONTHS (A.DUE_DATE, 1)
 WHERE customer_trx_id IN (1825819, 1825818, 1825820)
 
 
UPDATE RA_CUSTOMER_TRX_ALL B
   SET B.TERM_DUE_DATE = ADD_MONTHS (B.TERM_DUE_DATE, 1)
 WHERE customer_trx_id IN (1825819, 1825818, 1825820)
 

先治標, 根本問題還要再解.

Oracle EBS: 更改payment term中的balance forward billing cycle

問題: 更改payment term中的balance forward billing cycle

解答: 只要payment term已在交易中使用過, 就不能更改balance forward billing cycle


Ref:
1.How To Update Currently Used Payment Term With Balance Forward Billing Cycle (Doc ID 1578298.1)

Oracle EBS: BI Publisher report使用RTF template不能高於63欄位的解法

問題: RTF template不能高於63欄位, 源自於MS word單一表格最高只有63欄位

暫解: 巢狀式表格, 但資料量大時, 會因Java heap memeory問題而失敗

解法: 改為使用XSL-FO template


Ref:
1.Limitation of BI Publisher report using RTF template having more than 63 columns and how to resolve it.
https://erporaclesy.wordpress.com/2018/06/01/limitation-of-bi-publisher-report-using-rtf-template-having-more-than-63-columns-and-how-to-resolve-it/

Oracle EBS: AR concept

AR concept

1.AP/AR Netting in R12
https://erporaclesy.wordpress.com/2014/11/13/apar-netting-in-r12/

2.AR: Balance Forward Billing in R12
https://erporaclesy.wordpress.com/2014/11/04/ar-balance-forward-billing-in-r12/

3.AR: Auto Invoice
https://erporaclesy.wordpress.com/2014/11/03/ar-auto-invoice-process/

 

Oracel EBS: 補入return to supplier之分錄

問題:
1.user進行return to supplier時, 不知怎麼作到一筆作業產生兩筆交易
2.在rcv_transactions_interface中, 第一筆正常處理完, 第二筆則是completed/error
3.在rcv_transactions的兩筆交易都有, 第二筆沒有分錄資料
4.第二筆因沒有庫存, 對INV的交易也卡住

針對沒有分錄, 可用以下procedure補入:

     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
     );


備註: 要先執行

    mo_global.init('PO');
    mo_global.set_policy_context('S',&operating_unit_id);

啟用Windows 7中的TFTP client

啟用Windows 7中的TFTP client

1.開始 -> 控制台

2.點擊[程式與功能]選項

 3.點擊[開啟或關閉Windows功能]


















4.出現請稍候提示



















5.找出並勾選[TFTP用互端]選項



















6.正在變更功能












等提示訊息消失, 就設定好了.

Labview: 開啟labview project時, 找不到REST Client.lvclass

問題: 開啟labview project時, 找不到REST Client.lvclass

解法: 依網路搜尋到的文字資料, 以檔名REST Client.lvclass儲存至以下路徑

C:\Program Files\National Instruments\LabVIEW 2013\vi.lib\addons\_JKI.lib\REST Client\classes\REST Client


看起來是過了~


Ref:
1.JKISoftware/JKI-HTTP-REST-Client
https://github.com/JKISoftware/JKI-HTTP-REST-Client/blob/master/source/classes/REST%20Client/REST%20Client.lvclass

Toad error: Can't initialize OCI. Error -1

安裝一套新軟體(Labview)有問題, 整台電腦都變慢, 執行重新啟動, 但畫面一直停在關機中的訊息顯示, 所以就手動強制關機.

重開機後似乎正常, 但執行Toad時出現error:

Can't initialize OCI. Error -1

網路查到要設定path, 但無效.

在Toad Installed Clients的Connect Using選項中, 有一項Oracle Root是紅色, 查看內容是沒有設定值, 比對另一組, 土法煉鋼, 在機碼:

  \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE

下, 自己新增了三組值:
ORACLE_HOME_NAME
ORACLE_HOME
SQLPATH

再重進Toad, 可以了.

原因不明, 問題也不一定完全解了, 但至少不用重灌Toad呀.

Oracle Report builder開啟後, 只有看到工作列圖示, 沒看到程式畫面

這應是程式操作問題.

Report builder開啟後, 只有看到工作列圖示, 沒看到程式畫面.

試了幾次都一樣, 登出重來也無效, 原本打算要重灌了.

忽然想到, 這像是程式畫面跳到桌面的範圍外, 所以看不到.

搜尋Window快捷鍵, 覺得這項用得到:

  ALT+SPACEBAR: 打開程式最左上角的功能表

實測果然帶出功能表, 選最大化, 成功.

雖然還不知道先前什麼動作造成這狀況, 至少先解了.


Ref:
1.Windows快捷鍵大全
http://my.so-net.net.tw/teamtoptech/q_a/78.htm

Oracle EBS: 針對未正常寄送的PO pdf重送 (II)

依user所提供sample,有些PO因狀況不同而沒列到(如以下SQL).

由於workflow已結束, 要以更新need by date並approve的方式發出PDF.

select wn.notification_id,

       wn.message_name,
       wn.begin_date,
       wn.mail_status,
       wn.item_key,
       wn.user_key,
       wn.subject
  from wf_notifications wn
 where 1=1
   --and wia.item_key ='2271515-175073'
   and wn.message_name = 'EMAIL_PO_PDF'
   and wn.mail_status ='ERROR'
   and wn.status = 'OPEN'
   and wn.begin_date >= to_date('20190801','yyyymmdd')
 
union all

select wn.notification_id,

       wn.message_name,
       wn.begin_date,
       wn.mail_status,
       wn.item_key,
       wn.user_key,
       wn.subject
  from wf_notifications wn
 where 1=1
   --and wia.item_key ='2271515-175073'
   and wn.message_name = 'PO_PO_HAS_BEEN_APPROVE'
   and wn.mail_status ='ERROR'
   and wn.status = 'CANCELED'
   and wn.begin_date >= to_date('20190801','yyyymmdd')

Oracle EBS: 針對未正常寄送的PO pdf重送

問題: 系統異常, 造成PO pdf檔未寄送, 在wf_notifications中的mail_status為ERROR

解法: 取 [SQL 1] 的item_key(並記錄 notification_id以作後續比對), 再參考 [SQL 2] 執行Retry. 

 
SQL 1:
 
select * from wf_notifications
 where message_name ='PO_EMAIL_PO_PDF_WITH_RESPONSE'
   and begin_date >= to_date('20190802','yyyymmdd')
   and status = 'OPEN'
   and nvl(mail_status,'X') ='ERROR'
   order by notification_id

SQL 2:

 
DECLARE
   CURSOR c1
   IS
      SELECT   pa.instance_label label, iass.activity_result_code RESULT,
               iass.item_type, iass.item_key, process_activity,
               pa.process_name
          FROM wf_item_activity_statuses iass, wf_process_activities pa
         WHERE iass.item_type = 'POAPPRV'
           --AND pa.process_name = 'EMAIL_DOC_PROCESS'
           AND pa.process_name = 'EMAIL_DOC_PROCESS_CO'
           AND iass.process_activity = pa.instance_id
           and iass.item_key = '2271515-175073'
           AND iass.begin_date >= TO_DATE ('20190101', 'yyyymmdd')
           and pa.instance_label = 'NOTIFY_WEB_SUPP_PDF'
           AND EXISTS (
                  SELECT ias.item_key
                    FROM wf_item_activity_statuses ias,
                         wf_process_activities pa
                   WHERE ias.item_type = 'POAPPRV'
                    --AND pa.process_name = 'EMAIL_DOC_PROCESS'
                     AND ias.item_key = iass.item_key
                     AND ias.process_activity = pa.instance_id
                     AND ias.activity_status = 'ERROR')
      ORDER BY iass.item_type;

BEGIN
   FOR v1 IN c1
   LOOP
      BEGIN
         wf_engine.handleerror (v1.item_type,
                                v1.item_key,
                                v1.process_name || ':' || v1.label,
                                'RETRY'
                               );
         COMMIT;

      EXCEPTION
         WHEN OTHERS
         THEN
           DBMS_OUTPUT.put_line (   'error in the following item key :'
                                  || v1.item_key
                                 );
      END;
   END LOOP;

END;