[BOM] 逆展BOM

  

procedure xxx (IN_ORGANIZATION_ID   IN  NUMBER, 
                          IN_ITEM_ID                       IN   NUMBER) is 
      ln_SEQUENCE_ID   number;
      X_char_date      varchar2(30);

   begin

       delete BOM_SMALL_IMPL_TEMP;
      -- 取 SEQUENCE
      select bom_implosion_temp_s.nextval
        into ln_SEQUENCE_ID
        from dual;

      -- 執行日期
      SELECT to_char(sysdate, 'YYYY/MM/DD HH24:MI')
        INTO X_char_date
        FROM dual;

/*     
| DESCRIPTION  : This file is a packaged procedure for the imploders.
|                This package contains 2 different imploders for the
|                single level and multi level implosion. The package
|                imploders calls the correct imploder based on the
|         # of levels to implode.
| Parameters:   org_id          organization_id
|               sequence_id     unique value to identify current implosion
|                               use value from sequence bom_small_impl_temp_s
|               levels_to_implode
|               eng_mfg_flag    1 - BOM
|                               2 - ENG
|               impl_flag       1 - implemented only
|                               2 - both impl and unimpl
|               display_option  1 - All
|                               2 - Current
|                               3 - Current and future
|               item_id         item id of asembly to explode
|               impl_date       explosion date dd-mon-rr hh24:mi
|               err_msg         error message out buffer
|               error_code      error code out.  returns sql error code
|                               if sql error, 9999 if loop detected.
|        organization_option
|                1 - Current Organization
|                2 - Organization Hierarchy
|                3 - All Organizations to which access is allowed
*/   
      -- 逆展成品
      BOMPIINQ.IMPLODER_USEREXIT (
                  sequence_id => ln_SEQUENCE_ID,
                  eng_mfg_flag => 1,              -- BOM
                  org_id => IN_ORGANIZATION_ID,
                  impl_flag => 1,                 --IMPLEMENTED_ONLY,
                  display_option => 1,            --All
                  levels_to_implode => 15,
                  item_id => IN_ITEM_ID,
                  impl_date => X_char_date,
                  unit_number_from => null,
                  unit_number_to => null,
                  err_msg => err_msg,
                  err_code => err_code,
                  organization_option => 1, 
                  organization_hierarchy => null,
                  serial_number_from => null,
                  serial_number_to => null
                  );

   end;

執行OK就可以應用BOM_SMALL_IMPL_TEMP的資料了。

[MRP] MRP連結PO的SQL

SELECT PLL.*
FROM PO_LINE_LOCATIONS_ALL PLL ,
      MRP_RECOMMENDATIONS MR,
      MRP_ITEM_PURCHASE_ORDERS MIPO
WHERE PLL.PO_LINE_ID       = MIPO.LINE_ID
  AND MR.DISPOSITION_ID    = MIPO.TRANSACTION_ID
  AND MR.ORGANIZATION_ID   = PLL.ORG_ID
  AND MR.COMPILE_DESIGNATOR   = 'XXX-MRP'

OSP 工單完工未扣投入卻有產出完成品

EBS：11.5.10.2

狀況：USER偶爾會反應OSP工單收完料後，工單狀態有完工， WIP PENDING MOVE TRANSACTIONS也沒卡住，

但是庫存有產出完成品卻沒扣掉投入品的庫存數。

之前在METALINK上LOAD過TAR，說要上 WIP的PATCH，結果測試環境的AP上完PATCH後EBS就掛了，所以就沒人趕放到正式環境。直到最近在處理幾筆PENDING的資料發現，只要利用WIP_MOVE_TXN_INTERFACE就可以解決問提了。

首先，將為扣帳的工單用SQL查出，然後再從WIP_MOVE_TRANSACTIONS_V裡把此筆工單的資料找出，Insert到WIP_MOVE_TXN_INTERFACE 重送PENDING的資料，這樣系統就會補扣投入品的庫存。

WIP_MOVE_TXN_INTERFACE對應的FORM

WIP_MOVE_TRANSACTIONS_V對應的FORM

Drop Ship Order - Requisition Import 失敗

最近遇到Drop Ship的訂單，在開出PR時會失敗，此前的模組負責人都是請user重新開立，這次模組換人維護後，才知道user一直有這個困擾，查了一下metalink後，找到了解決方法。

1.利用SQL查詢出Requisition Import的原因：

select pri.transaction_id req_tranx_id
, msi.segment1 item_number
, oeh.order_number sales_order
, oel.LINE_NUMBER
, pri.process_flag process_flag
, pri.request_id conc_request_id
, poi.error_message
, ipr.ORGANIZATION_CODE
from mtl_system_items_b msi,
mtl_parameters ipr,
po_interface_errors poi,
oe_drop_ship_sources oed,
po_requisitions_interface_all pri,
oe_order_headers_all oeh,
oe_order_lines_all oel
where oeh.header_id = oed.header_id and
oel.header_id = oed.header_id and
oel.line_id = oed.line_id and
pri.transaction_id = poi.interface_transaction_id and
pri.interface_source_line_id = oed.drop_ship_source_id and
pri.DESTINATION_ORGANIZATION_ID = msi.ORGANIZATION_ID and
pri.item_id = msi.inventory_item_id and
msi.organization_id = ipr.ORGANIZATION_ID and
oeh.order_number = :p_order_number

2.修正問題後（一般都是漏掉採購員等），update UPDATE PO_REQUISITIONS_INTERFACE_ALL

UPDATE PO_REQUISITIONS_INTERFACE_ALL
SET PROCESS_FLAG = NULL,
REQUISITION_HEADER_ID = NULL,
REQUISITION_LINE_ID = NULL,
REQ_DISTRIBUTION_ID = NULL
WHERE PROCESS_FLAG = 'ERROR'
AND INTERFACE_SOURCE_CODE = 'ORDER ENTRY'
and REQUISITION_LINE_ID = :P_REQUISITION_ID -- 條件依據個人習慣

3.重送Requisition Import，完成。