Oracle ERP多阶BOM的应用

DECLARE
  V_ITEM VARCHAR2(240) := '5030521004'; -- item to explode
  V_ORG  VARCHAR2(3) := 'HQ'; -- org in which item is exploded

  V_CNT NUMBER := 0;

http://www.myhpf.co.uk/banner.asp?friend=295311

  V_ERR_MSG           VARCHAR2(240);
  V_ERR_CODE          NUMBER := 0;
  V_VERIFY_FLAG       NUMBER := 0; -- DEFAULT 0
  V_ONLINE_FLAG       NUMBER := 2; -- DEFAULT 0
  V_ITEM_ID           NUMBER := 0; -- set to inventory_item_id of item to explode
  V_ORG_ID            NUMBER := 0; -- set to organization_id of item to explode
  V_ALTERNATE         VARCHAR2(240) := NULL; -- DEFAULT null
  V_LIST_ID           NUMBER := 0; -- for reports (default 0)
  V_ORDER_BY          NUMBER := 1; -- DEFAULT 1
  V_GRP_ID            NUMBER := 0; --
  V_SESSION_ID        NUMBER := 0; -- DEFAULT 0
  V_REQ_ID            NUMBER := 0; -- DEFAULT 0
  V_PRGM_APPL_ID      NUMBER := -1; -- DEFAULT -1
  V_PRGM_ID           NUMBER := -1; -- DEFAULT -1
  V_LEVELS_TO_EXPLODE NUMBER := 1; -- DEFAULT 1
  V_BOM_OR_ENG        NUMBER := 1; -- DEFAULT 1
  V_IMPL_FLAG         NUMBER := 2; -- DEFAULT 1
  V_PLAN_FACTOR_FLAG  NUMBER := 2; -- DEFAULT 2
  V_INCL_LT_FLAG      NUMBER := 2; -- DEFAULT 2
  V_EXPLODE_OPTION    NUMBER := 2; -- DEFAULT 2
  V_MODULE            NUMBER := 2; -- DEFAULT 2
  V_CST_TYPE_ID       NUMBER := 0; -- DEFAULT 0
  V_STD_COMP_FLAG     NUMBER := 0; -- DEFAULT 0
  V_REV_DATE          VARCHAR2(240); --
  V_COMP_CODE         VARCHAR2(240) := NULL; --
  V_EXPL_QTY          NUMBER := 1; -- DEFAULT 1
 
BEGIN

  -- item revision will be based on this explode date.
  -- In this example, we use current date/time
  V_REV_DATE := TO_CHAR(SYSDATE);

  -- Find org_id
  SELECT MP.ORGANIZATION_ID
    INTO V_ORG_ID
    FROM INV.MTL_PARAMETERS MP
   WHERE MP.ORGANIZATION_CODE = V_ORG;

  -- Find item_id
  SELECT INVENTORY_ITEM_ID
    INTO V_ITEM_ID
    FROM APPS.MTL_ITEM_FLEXFIELDS
   WHERE ORGANIZATION_ID = V_ORG_ID
     AND ITEM_NUMBER = V_ITEM;

  -- v_grp_id is a unique identifier for this run of the exploder
  SELECT BOM.BOM_EXPLOSION_TEMP_S.NEXTVAL INTO V_GRP_ID FROM DUAL;

  -- determine maximum levels to explode from bom_explosions
  SELECT MAXIMUM_BOM_LEVEL
    INTO V_LEVELS_TO_EXPLODE
    FROM BOM.BOM_PARAMETERS
   WHERE ORGANIZATION_ID = V_ORG_ID;
 
  APPS.BOMPEXPL.EXPLODER_USEREXIT(V_VERIFY_FLAG,
                                  V_ORG_ID,
                                  V_ORDER_BY,
                                  V_GRP_ID,
                                  V_SESSION_ID,
                                  V_LEVELS_TO_EXPLODE,
                                  V_BOM_OR_ENG,
                                  V_IMPL_FLAG,
                                  V_PLAN_FACTOR_FLAG,
                                  V_EXPLODE_OPTION,
                                  V_MODULE,
                                  V_CST_TYPE_ID,
                                  V_STD_COMP_FLAG,
                                  V_EXPL_QTY,
                                  V_ITEM_ID,
                                  V_ALTERNATE,
                                  V_COMP_CODE,
                                  V_REV_DATE,
                                  V_ERR_MSG,
                                  V_ERR_CODE);

    
  IF (V_ERR_CODE <> 0) THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('ERROR: ' || V_ERR_MSG);
  ELSE
    SELECT COUNT(*)
      INTO V_CNT
      FROM BOM.BOM_EXPLOSION_TEMP
     WHERE GROUP_ID = V_GRP_ID;
    DBMS_OUTPUT.PUT_LINE('Count=' || V_CNT);
    --COMMIT;
    DBMS_OUTPUT.PUT_LINE('.');
    DBMS_OUTPUT.PUT_LINE('Group Id=' || V_GRP_ID);
    DBMS_OUTPUT.PUT_LINE('Org =' || V_ORG);
    DBMS_OUTPUT.PUT_LINE('Item =' || V_ITEM);
    DBMS_OUTPUT.PUT_LINE('Ord Id =' || V_ORG_ID);
    DBMS_OUTPUT.PUT_LINE('Item Id=' || V_ITEM_ID);
    DBMS_OUTPUT.PUT_LINE('Levels =' || V_LEVELS_TO_EXPLODE);
   
  END IF;
END;

 

SELECT T.GROUP_ID, T.* FROM BOM.BOM_EXPLOSION_TEMP T
WHERE T.TOP_ITEM_ID=298

 WHERE T.GROUP_ID = 10830
   AND T.PLAN_LEVEL > 0
  
SELECT MAX(T.GROUP_ID)
  FROM BOM_EXPLOSION_TEMP T

SELECT BOM_EXPLOSION_TEMP_S.CURRVAL FROM DUAL

SELECT * FROM MTL_ITEM_REVISIONS MIR
 WHERE MIR.INVENTORY_ITEM_ID = 314
   AND MIR.ORGANIZATION_ID = 81
  
  SELECT BOM.BOM_EXPLOSION_TEMP_S.NEXTVAL  FROM DUAL  
 
 
SELECT msi.SEGMENT1,MSI.INVENTORY_ITEM_ID FROM
BOM.BOM_BILL_OF_MATERIALS BBO,
inv.mtl_system_items_b msi
WHERE bbo.ORGANIZATION_ID=msi.ORGANIZATION_ID
and bbo.ASSEMBLY_ITEM_ID=msi.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID=81
and rownum<=10
and msi.SEGMENT1 like '503052100%' 

SELECT  MSI.SEGMENT1,T.*
FROM inv.mtl_system_items_b MSI,
BOM.BOM_EXPLOSION_TEMP T
WHERE MSI.ORGANIZATION_ID=T.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID=T.COMPONENT_ITEM_ID

ORDER BY COMPONENT_SEQUENCE_ID

http://www.myhpf.co.uk/banner.asp?friend=295311

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9880886/viewspace-444215/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9880886/viewspace-444215/