Oracle EBS: ASCP展Planned Order之數量考量歷史分配問題

ASCP展Planned Order之數量考量歷史分配問題

因巿場狀況, user希望planned order依指定比例分配給不同廠商時, 只考慮新需求量.

系統既有行為, 是依歷史累計需求分配量計算.

User's Guide有一段:

  You can allocate planned orders taking into account historical allocations in unconstrained plans.

句子中用的是 can , 不是 must , 這就讓人好奇了.

瘋狂搜尋後, 在Oracle support找到相關文章, 有個profile:

  MSC: Sourcing History Start Date Offset(in months)

預設是null, 代表不限月份. 若指定為0, 就不會搜集舊資料.

 

Oracle自己也備註, 設定較高的值或是設定為null, 都會影響效能, 卻仍兩光把預設值定為null.

另有兩個ODS Load Parameters要調整:

  Recalculate Sourcing History = No
  Purge Sourcing History = Yes

以事後之明來看, 這問題真是簡單到讓人淚流滿面...

但是, 因為ASCP仍會把open的PO和PR列入分配計算, 所以還是不符合user的期望.     Orz...


Ref:

1.Advanced Supply Chain Planning (ASCP) Profile Plan Option Matrix (PDF Rev December-2017) (Doc ID 803583.1)

2.ASCP Plan Output Does Not Respect Allocation Percentage From Sourcing Rule. Need to verify Allocation Percent. (Doc ID 399468.1)

Oracle EBS: Vendor帳號變更密碼後, 用通知信的連結可登入系統, 但Return to Worklist時error

狀況:
1.廠商改密碼後, 可用系統通知信的連結登入
2.點擊Return to Worklist時出現Error Page
3.在User資料中, Last Updated By是GUEST

原因: 應是密碼變更程序未正常完成, 系統中資料不一致

處理方式:
1.user帳號填入停用日期, 存檔
2.清除停用日期, 存檔

完成.

Oracle EBS: 取得AR invoice balance資料

SQL:

SELECT a.trx_number,
       a.trx_date,
       b.customer_name,
       b.customer_number,
       a.amount_due_original,
       a.amount_due_remaining,
       a.amount_applied
 FROM ar_payment_schedules_all a
       ra_customers b
 where a.customer_id=b.customer_id
   and trx_number=<Give Inv Number>


Ref:  AR Invoice balance
https://it.toolbox.com/question/ar-invoice-balance-011709

Oracle EBS: Receiving Transaction Processer排程問題

問題: 原有的Receiving Transaction Processer排程會處理所有公司資料, 因故停了, 重排後卻只處理單一公司

原因: 若以單一公司的權限執行, 就只會處理單一公司在interface的資料.

解決方式: 需在System Administrator權限排程執行, 才會處理所有公司資料.

這應和各權限所能處理的公司有關. 雖然有點麻煩, 但以各公司各自執行的角度來看, 的確也多了些彈性.

Oracle EBS: [REP-2103: Column 'G_1' : PL/SQL formula returned invalid value or no value]

很久沒更動的Report突然在執行時error了, 訊息為:

  REP-2103: Column 'G_1' : PL/SQL formula returned invalid value or no value.
  REP-0069: Internal error
  REP-57054: In-process job terminated:Terminated with error:
  REP-2103: Column 'G_1' : PL/SQL formula returned invalid value or no value.

但G_1是在Data Model的Groups中, 不是資料欄位或Formula, 也沒有附加額外的program unit.

逐步測試發現, 符合特定條件的資料才會發生問題.

最後解法, 查看各個欄位及formula, 終於找到有個formula, 其中的回傳值會超過預訂長度, 因而error.

結論: 要記得作exception處理

心得: Oracle的error message本身就是個error...

Oracle EBS: [ORA-04031: unable to allocate xxxx bytes of shared memory]

連續兩天出現ORA-04031問題(ERROR_1, ERROR_2), 看了幾篇文章, 看起來是可以透過調整參數來解決.

依參考文件查了幾個值:
  request_failures: 3904
  last_failure_size: 4080
  shared_pool_reserved_min_alloc: 4000

符合"Shared Pool保留空間缺少連續空間"的狀況.


Ref:
1.診斷並解決ORA-04031 錯誤
http://blog.xuite.net/lin666765/blog/16796093-%E8%A8%BA%E6%96%B7%E4%B8%A6%E8%A7%A3%E6%B1%BAORA-04031+%E9%8C%AF%E8%AA%A4

2.ORA-04031: unable to allocate nn bytes of shared memory
http://www.dba-oracle.com/t_ora_04031_unable_to_allocate_shared_memory.htm

3.ORA-4031 Troubleshooting Tool (Doc ID 1521925.1)
https://support.oracle.com/epmos/faces/DocumentDisplay?id=1521925.1


ERROR_1:

ORACLE error 4031 in FDPSTP

Cause: FDPSTP failed due to ORA-04031: unable to allocate 63192 bytes of shared memory ("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_ST
+---------------------------------------------------------------------------+
Successfully resubmitted concurrent program QLTTRAMB with request ID 118229540 to start at 02-APR-2018 17:46:53 (ROUTINE=AFPSRS)


ERROR_2:

ORACLE error 4031 in FDPSTP

Cause: FDPSTP failed due to ORA-04031: unable to allocate 2088 bytes of shared memory ("shared pool","unknown object","Heap0: KGL","kgltbsgp")


ERROR_3(原有的排程沒了):

AFPSRS cannot resubmit your concurrent request 118474950. ORACLE error 4031 in FDPRRC

Cause: FDPRRC failed due to ORA-04031: unable to allocate 2416 bytes of shared memory ("shared pool","insert into Fnd_Concurrent_R...","CCursor","kkslpkp
+---------------------------------------------------------------------------+
Finished executing request completion options.
ORACLE error 4031 in FDPRRC

Cause: FDPRRC failed due to ORA-04031: unable to allocate 2416 bytes of shared memory ("shared pool","insert into Fnd_Concurrent_R...","CCursor","kkslpkp:kksclitval")