[BOM] 正展BOM

 

在做一些成本分析時，常常會對BOM做正逆展開功能，所以就把他記錄下來，範例：
PROCEDURE xxx (IN_ORGANIZATION_ID   IN   NUMBER,
               IN_ITEM_ID           IN   NUMBER,
               IN_LEVELS_TO_EXPLODE IN   NUMBER) IS

   V_GROUP_ID               NUMBER;
   V_ERROR_MESSAGE          VARCHAR2(2000);
   V_ERROR_CODE             NUMBER;

BEGIN
   DELETE BOM_SMALL_EXPL_TEMP;

   SELECT BOM_EXPLOSION_TEMP_S.NEXTVAL
     INTO V_GROUP_ID
     FROM DUAL;
   -- 正展BOM
   BOMPXINQ.EXPLODER_USEREXIT(
           VERIFY_FLAG => 0,
           ORG_ID => IN_ORGANIZATION_ID,
           ORDER_BY => 1,
           GRP_ID => V_GROUP_ID,
           SESSION_ID => 0,
           LEVELS_TO_EXPLODE => IN_LEVELS_TO_EXPLODE, 
           BOM_OR_ENG => 1,                           
           IMPL_FLAG => 1,
           PLAN_FACTOR_FLAG => 2,
           EXPLODE_OPTION => 3,                       
           MODULE => 2,                               
           CST_TYPE_ID => 0,                          
           STD_COMP_FLAG => 2,
           EXPL_QTY => 1,                             
           ITEM_ID => IN_ITEM_ID,                     
           ALT_DESG => NULL,
           COMP_CODE => NULL,
           UNIT_NUMBER_FROM => NULL,
           UNIT_NUMBER_TO => NULL,
           REV_DATE => SYSDATE,
           SHOW_REV => 1,
           MATERIAL_CTRL => 2,
           LEAD_TIME => 2,
           ERR_MSG => V_ERROR_MESSAGE,
           ERROR_CODE => V_ERROR_CODE);
   IF V_ERROR_MESSAGE IS NOT NULL THEN
      RAISE...
   END IF;
   ...
EXCEPTION
   WHEN OTHERS THEN
        RAISE...
END;

OK，可以查詢結果看看
SELECT *
  FROM BOM_SMALL_EXPL_TEMP;

[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'