2019年3月12日 星期二

日期運算的方式

下面是日期運算的方式

select
TO_CHAR(SYSDATE - INTERVAL '3' YEAR, 'YYYYMMDDHH24') ToYEAR,
--=>從系統日期算起往前 3 年
TO_CHAR(SYSDATE - INTERVAL '3' month, 'YYYYMMDDHH24') ToMONTH,
--=>從系統日期算起往前 3 個月
TO_CHAR(SYSDATE - INTERVAL '30' DAY, 'YYYYMMDDHH24') ToDay,
--=>從系統日期算起往前 30 天
TO_CHAR(SYSDATE - INTERVAL '30' HOUR, 'YYYYMMDDHH24') ToHOUR,
--=>從系統日期算起往前 30 小時
TO_CHAR(SYSDATE - INTERVAL '30' minute, 'YYYYMMDD HH24:MI:SS') ToMinute,
--=>從系統日期算起往前 30 分鍾
TO_CHAR(SYSDATE - INTERVAL '50' second,'YYYYMMDD HH24:MI:SS') ToSecond,
--=>從系統日期算起往前 50 秒
SYSDATE
--系統日期
from xxx
張貼者： 於 沒有留言:

2017年6月26日 星期一

Print QR Code in Oracle Reports

Print QR Code in Oracle Reports

https://community.oracle.com/thread/4008736
http://samizaffar.blogspot.tw/2014/03/print-qr-code-in-oracle-reports.html


https://blogs.oracle.com/xmlpublisher/quick-quips-on-qr-codes
張貼者： 於 沒有留言:

2015年5月15日 星期五

Update data from another Table 

UPDATE (
SELECT b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;








參考 http://blog.csdn.net/yuhua3272004/article/details/2776121










張貼者：



於





沒有留言:
  


































          

        

          

        

2015年4月6日 星期一


          

        









Oracle WIP Jobs Pending Resource Transactions, Pending Transactions in WIP_COST_TXN_INTERFACE Table














To find out the pending transactions from the application navigate to



WIP -> Discrete -> WIP Resource Transactions -> Pending Resource Transactions   and enter WIP Job name or any required parameter.



Here in this from you can find all the Pending Resource Transactions, if you could find your transaction in this form then that means your transaction got struck in Interface table [WIP_COST_TXN_INTERFACE].



Cause of Problem:



Usually this is caused if there is an problem with  ‘Cost Manager‘ and some times with  ‘Move Transaction Manager‘ too.



Solution:



Step 1: Identify your pending transactions in WIP_COST_TXN_INTERFACE table using the following query






SELECT transaction_id,
  request_id,
  GROUP_ID,
  process_status,
  wip_entity_name
FROM wip_cost_txn_interface
WHERE process_status IN (1, 3)
AND wip_entity_name   ='&ur_wipjob_name';









here



PROCESS_STATUS = 1 means Pending and

PROCESS_STATUS = 3 means Error



Note down all the transactions_id which you need to fix.



Step 2:  Take a backup of WIP_COST_TXN_INTERFACE table



Step 3:   Stop the Cost Manager & Move Transaction Manager



to stop these programs you need to navigate to INVENTORY –> Setup –>Transactions –> Interface Managers and then select the program and go to Tools menu.



Step 4: 


    

  • If the PROCESS_STATUS = 1 (Pending) , run the following script:
    • 







UPDATE wip_cost_txn_interface
  SET GROUP_ID = NULL,
  transaction_id = NULL
WHERE transaction_id IN (x, y, z);









x y z = transaction_ids of stuck transactions.



If  you want to fix all records in WIP_COST_TXN_INTERFACE table then run the following script:






UPDATE wip_cost_txn_interface
SET GROUP_ID = NULL,
transaction_id = NULL,
process_status = 1;








    

  • If the PROCESS_STATUS = 3 (Error) , run the following script:
    • 







UPDATE wip_cost_txn_interface
SET GROUP_ID = NULL,
process_status = 1
WHERE process_status = 3;









Step 5:  Restart the Cost Manager and Move Transaction Manager.



Navigate to Pending Resource Transaction Form and now you can’t find your transaction here as they are cleared,






No related posts.



http://oracleappsdna.com/2011/07/oracle-wip-jobs-pending-resource-transactions-pending-transactions-in-wip_cost_txn_interface-table/





這次使用的指令



Update WIP_COST_TXN_INTERFACE

Set GROUP_ID = NULL,

TRANSACTION_ID = NULL,

REQUEST_ID = NULL,

PROCESS_STATUS = 1

Where ORGANIZATION_ID = 86

AND WIP_ENTITY_ID in (214751, 211204)












張貼者：



於





沒有留言:
  


































          

        

          

        

2014年12月4日 星期四


          

        









WIP 工單用料 依照BOM重展










http://blog.163.com/xin_er_yes/blog/static/176215474201132113831529









張貼者：



於





沒有留言:
  


































          

        

          

        

2014年10月28日 星期二


          

        









Oracle報表 分析函數 相關文章










http://baoqiangwang.blog.51cto.com/1554549/582012



http://blog.sina.com.cn/s/blog_5904ea2801008ceh.html



Oracle分析函数

http://blog.itpub.net/6517/viewspace-611062/









張貼者：



於





沒有留言:
  


































          

        

          

        

2014年10月12日 星期日


          

        










message dictionary couldn't open the application message file












在 OM 的 MENU 上掛上一個請求群組, 但是當登入後第一次進入時, 都會發生 此訊息















解決方式如下:



這是FUNCTION > FORM >PARAMETER 的值



REQUEST_GROUP_CODE="XXHS_OM_STAT" REQUEST_GROUP_APPL_SHORT_NAME="ONT" TITLE="OM: HS 銷貨統計報表" USE_ORG="Y"



我把其中的 OM: 拿掉變成



REQUEST_GROUP_CODE="XXHS_OM_STAT" REQUEST_GROUP_APPL_SHORT_NAME="ONT" TITLE="HS 銷貨統計報表" USE_ORG="Y"



就OK了, 反覆測試過兩三次確認無誤


















張貼者：



於





沒有留言:
  


































          

        

          

        

2014年10月5日 星期日


          

        









Oracle 常用SQL語句優化










本文出處: http://www.aiisen.com/oracle-sql-opt-zh.html











閱讀更多 »







張貼者：



於





沒有留言:
  










































Oracle常用傻瓜問題1000問 [9I ADMIN]












Oracle常用傻瓜問題1000問 [9I ADMIN]








閱讀更多 »







張貼者：



於





沒有留言:
  


































          

        

          

        

2014年10月3日 星期五


          

        









oracle concepts for you: $FLEX$ and $PROFILES$.










oracle concepts for you: $FLEX$ and $PROFILES$.: What is $FLEX$ and $PROFILES$?   $FLEX$ and $PROFILES$ are Special Variables in oracle Apps environment and are used to hold   values   at r...









張貼者：



於





沒有留言:
  


































          

        

          

        

2014年10月2日 星期四


          

        










Auto-release Planned Orders














Auto-release Planned Orders



Instead of implementing and releasing planned orders by hand, you can enable the planning process to automatically release planned orders. During the planning run, all qualifying planned orders that fall within the release time fence will be released. Unlike manual release, you cannot modify order dates and quantities or the default implementation details.





Prerequisites

Before the planning process can release planned orders automatically, you must:




       Define an employee, associate a user name to the employee entry, and associate the employee to the application user. 


       Define a planner or planning entity and associate this planner with items you want controlled by the auto-release function.


       Supply Chain Planning users must define a material planner, supply chain planner or planning entity for the current organization and assign them to inventory items at the organization level. 

In addition, the planned orders must meet the following auto-release criteria:




    

  • the new due date lies within the auto-release time fence
    • 



    

  • the lead time is not compressed
    • 



    

  • the orders are for standard items (will not release models, option classes, and planning items)
    • 



    

  • the orders are not for Kanban items
    • 



    

  • the orders are for DRP planned items in a DRP plan, MPS planned items in an MPS plan, or MRP planned items in an MRP plan See: Auto-release Planned Orders.
    • 



    

  • the release time fence option is defined as anything other than Do not auto-release, Do not release (Kanban), or Null
    • 


Auto-release of repetitive schedules is not applicable to repetitively planned items. No material availability check is performed before WIP jobs are released.





   To enable the auto-release function:


    1. Navigate to the DRP, MPS, or MRP Names window.


    2. Enter an alphanumeric name that identifies a unique material requirements.


    3. Check Feedback to monitor the quantity of the planned order that has been implemented as discrete jobs, purchase orders, or purchase requisitions.


    This provides the planner visibility of the status of the plan at any point in time.


    4. Check Production to enable a plan to auto-release planned orders.


    5. Enter an inactive on date on which to disable the plan.


    As of this date, you can no longer update the plan, nor use the plan name in the planning process. You are still able to view information and run reports for disabled names.


    6. Select Yes to enable the Memory-based Planning Engine. You must enable the Memory-based Planning engine to use the auto-release function.


    7. Save your work.


    出處:http://docs.oracle.com/cd/A60725_05/html/comnls/us/mrp/planco03.htm











在實際使用上曾經遇過一個問題: 所有的設定都正確, 突然間有一天無法自動核發, 去看了DB 端的Package , 終於查出來問題所在:



   



  1. 由於我們公司需要將所有的工單都自動核發出來, 因此我將 Time Fence 設定的範圍拉到 600天



  2. 算了一下, 600天應該不到兩年對嗎? 錯!! 因為程式中計算是以工作曆的工作日來算, 600天已經超過兩年了



  3. 結果因為我的工作曆範圍只設定了未來的兩年半, 剛剛好不夠, 所以在計算時算不出來







 處理方法:  1. 將 Time Fence 設為 480天  



                    2. 工作曆至少設定2年以上







 結果就正常核發了



  














張貼者：



於





沒有留言:
  


































          

        

          

        

2014年3月24日 星期一


          

        









Error with Standard Costing cst_match_date_period














Error with Standard Costing cst_match_date_period








In this Document
Symptoms
Cause
Solution
References
Applies to:

Oracle Inventory Management - Version 11.5.10.CU2 to 12.1.3 [Release 11.5.10 to 12.1]
Oracle Cost Management - Version 11.5.10.1 to 12.0.4 [Release 11.5 to 12]
Information in this document applies to any platform.
Checked for relevance 19-JUN-2010


Symptoms

When attempting to cost Sales Order Pick and Internal Order Pick the following error occurs:

ERROR

Error Code =CST_MATCH_DATE_PERIOD

Error Explanation = The transaction date is not within the accounting period specified.


The issue can be reproduced at will with the following steps:

1. Oracle cost Management 
2. Inventory 
3. Transactions 
4. View Transactions 
5. select costed_flag option error

Cause

The cause of the issue is invalid / incorrect data in MTL_MATERIAL_TRANSACTIONS, the acct_period_id is getting stamped with the accounting is of the previous period. 

Solution

To identify the issue, please obtain the following scripts output:

1) 
SELECT mmt.transaction_id txn_id,
mmt.transaction_date txn_date,
mmt.acct_period_id txn_period_id,
o.acct_period_id org_period_id,
o.period_start_date st_date,
o.schedule_close_date cl_date
FROM mtl_material_transactions mmt,
org_acct_periods o
WHERE mmt.organization_id = o.organization_id
AND TRUNC(o.schedule_close_date) >= TRUNC(mmt.transaction_date)
AND TRUNC(o.period_start_date) <= TRUNC(mmt.transaction_date)
and mmt.transaction_date is not NULL
and mmt.acct_period_id <> o.acct_period_id ;

2) select * 
   from org_acct_periods 
    where organization_id = '&org_id_where_issue_exists';

-- To implement the solution, please execute the following steps:

1) Make a backup of MTL_MATERIAL_TRANSACTIONS before running the update script. 
2) Shut down the cost manager before the update. 
3) Run the following update to correct the acct_period_id:

Update mtl_material_transactions 
set acct_period_id = <insert correct acct period id> 
,   costed_flag = 'N' 
,   transaction_group_id = NULL 
,   transaction_set_id = NULL 
,   error_code = NULL 
,   error_explanation = NULL 
where costed_flag = 'E' 
and acct_period_id =  <current incorrect value> 
and error_code = 'CST_MATCH_DATE_PERIOD'
and organization_id = '<organization_id for the affected records>'

Note: The transaction_id could also be used as the basis for the update, but in this case the number of records made that difficult and uniqueness could be achieved with the four where clauses parameters above.

3) Restart the Cost Manager.














張貼者：



於





沒有留言:
  


































          

        

          

        

2013年10月6日 星期日


          

        










週期性成本分配 計算錯誤 CSTPALPC.create_dist_entry : 40
































1. 從主機端直接抓 LOG 檔 (日誌太大時, 無法從系統直接打開)



   A.
找路徑







select
logfile_name from fnd_concurrent_requests 



where
request_id = 2668178























然後用 ftp 直接抓下來, 這是純文字檔











2. 從 log 檔查到



CSTPAPBR.create_inv_ae_lines: 0: begin <<
transaction_id: 8498336



CSTPAPBR.create_inv_ae_lines: 16:
l_curr_rec.pri_currency = TWD; l_curr_rec.alt_currency = 



CSTPAPBR.create_inv_ae_lines: 16:
l_curr_rec.currency_conv_rate = -1



Pcu_cost_txn <<



Category: 1213 has no accounts defined



30005未定義科目給分類、成本型態、成本群組及合法實體.



CSTPALPC.dyn_proc_call : Error Calling Package



30005未定義科目給分類、成本型態、成本群組及合法實體.:
CSTPALPC.create_dist_entry : 40



Create_Acct_Entry >>> 



CSTPDPPC.dyn_proc_call : Error Calling Package



30005未定義科目給分類、成本型態、成本群組及合法實體.:
CSTPALPC.create_dist_entry : 40







3. 回到異動檔查詢 TRANSACTION_ID=8498336 的紀錄







SELECT * FROM
mtl_material_transactions WHERE
TRANSACTION_ID=8498336



查出資料如下















Inventory_item_id = 136589  但是 organization_id = 82 , 這是
組織 ID







用以下 SQL 查出料號







SELECT segment1 FROM
MTL_SYSTEM_ITEMS_B WHERE INVENTORY_ITEM_ID=136589 and
organization_id=82







4. 然後到料號補入成本分類資料 (組織要選對)。











附註: 







(1) 用來檢查庫存有異動但是沒有成本分類







select
MMT.TRANSACTION_ID,msi.segment1,mic.category_concat_segs
,mic.organization_id



from  mtl_material_transactions MMT,



mtl_system_items
msi,



MTL_ITEM_CATEGORIES_V
MIC



where
mic.CATEGORY_ID =
1213  ---T
B D  



and
mic.category_set_id =
1100000042



and
mic.INVENTORY_ITEM_ID =
msi.INVENTORY_ITEM_ID



AND
MMT.organization_id =
MSI.ORGANIZATION_ID



AND
MIC.organization_id =
MSI.ORGANIZATION_ID



and
MMT.INVENTORY_ITEM_ID =
msi.INVENTORY_ITEM_ID







(2) 用來檢查基本資料成本分類是TBD



select
msi.segment1,mic.category_concat_segs
,mic.organization_id



from
mtl_system_items msi,



MTL_ITEM_CATEGORIES_V
MIC



where
mic.CATEGORY_ID =
1213  ---T
B D  



and
mic.category_set_id =
1100000042



and
mic.organization_id =
82 ---  組織代號



and
mic.ORGANIZATION_ID =
msi.ORGANIZATION_ID



and
mic.INVENTORY_ITEM_ID =
msi.INVENTORY_ITEM_ID







(3) 檢查完全未設成本分類的料號



SELECT
msi.inventory_item_id,msi.segment1,



msi.organization_id



FROM
mtl_system_items_b msi



WHERE
msi.organization_id =
86 ---  組織代號



AND
MSI.INVENTORY_ITEM_ID IN
(SELECT
DISTINCT
inventory_item_id



FROM
mtl_material_transactions



WHERE
organization_id =86)
---  組織代號



AND
NOT
EXISTS(



SELECT
category_id



FROM
mtl_item_categories mic



WHERE
mic.inventory_item_id =
msi.inventory_item_id



AND
mic.organization_id =
msi.organization_id



AND
mic.category_set_id =
1100000042);
















張貼者：



於





沒有留言:
  


















標籤：

















          

        

          

        

2013年10月1日 星期二


          

        









檢查有沒有料號未設成本分類 (SQL)










偶而會有料號未設成本分類, 造成會計在算成本時出現錯誤, 可以用以下 SQL 來檢查:



SELECT msi.inventory_item_id,msi.segment1,msi.organization_id

FROM mtl_system_items_b msi

WHERE msi.organization_id = 86

AND MSI.INVENTORY_ITEM_ID IN (SELECT DISTINCT inventory_item_id

FROM mtl_material_transactions

WHERE organization_id =86)



AND NOT EXISTS(

SELECT category_id

FROM mtl_item_categories mic

WHERE mic.inventory_item_id = msi.inventory_item_id

AND mic.organization_id = msi.organization_id

AND mic.category_set_id = 1100000042);














張貼者：



於





沒有留言:
  


















標籤：

















        

      









訂閱：
文章 (Atom)