利用Oracle 11i E-Business之API展BOM应用实例

-- BOM Explosion with Oracle API
SET HEA ON
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET PAGES 3000
SET LINES 5000
SET COLSEP";"
SET HEADSEP ";"
SET SERVEROUTPUT ON SIZE 1000000
SPOOL C:/aaa.txt

DECLARE

    v_org_code              mtl_parameters.organization_code%TYPE := '&1' ;
    v_item                  mtl_system_items.segment1%TYPE := '&2';

    l_grp_id                NUMBER                  ;
    l_session_id            NUMBER                  ;
    l_org_id_s              NUMBER                  ;
    l_levels_to_explode     NUMBER  DEFAULT 20      ;
    l_module                NUMBER  DEFAULT 2       ; -- BOM = 2
    l_cst_type_id           NUMBER  DEFAULT 0       ; -- All cost = -1 else 0
    l_item_id               NUMBER                  ;
    l_bom_or_eng      NUMBER           ;
    l_using_req_quantity NUMBER            ;
    l_using_ass_dem_DATE DATE           ;
    l_rev_DATE              VARCHAR2(20)            ;
    l_err_msg               VARCHAR2(250)           ;
    l_error_code            NUMBER                  ;

    v_top_item              VARCHAR2(20)            ;
    v_assembly_item         VARCHAR2(20)            ;
    v_component_item        VARCHAR2(20)            ;
    v_organization_id       mtl_system_items.organization_id%TYPE;
    v_inventory_item_id     mtl_system_items.inventory_item_id%TYPE;
    v_cnt                   NUMBER                  ;

    CURSOR exploded_items
    IS
    SELECT
    group_id,
    top_item_id,
    assembly_item_id,
    component_item_id,
    organization_id,
    sort_order,
    plan_level,
       component_quantity,
       extended_quantity
    FROM
    bom_explosion_temp
    WHERE   group_id=l_grp_id
    AND nvl(disable_DATE, SYSDATE)>=SYSDATE
    ORDER BY item_num,operation_seq_num
    ;

BEGIN

    SELECT organization_id
    INTO   l_org_id_s
    FROM   mtl_parameters
    WHERE  1=1
      AND  organization_code = v_org_code;

    SELECT inventory_item_id
    INTO   l_item_id
    FROM   mtl_system_items
    WHERE  1=1
      AND  segment1 = v_item
      AND  organization_id = l_org_id_s;

    DBMS_OUTPUT.PUT_LINE('Group_ID|Oganization_id|Component_Item_id|Top_Item|Assembly_Item|Component_Item|Component_Qty|Extended_Qty');

    -- Group id
    SELECT  bom_explosion_temp_s.NEXTVAL
    INTO    l_grp_id
    FROM    dual    ;

    -- Session id
    SELECT  bom_explosion_temp_session_s.NEXTVAL
    INTO    l_session_id
    FROM    dual    ;

    -- Bom or Eng Item
    BEGIN
     SELECT  bbom.assembly_type
     INTO    l_bom_or_eng
     FROM    bom_bill_of_materials bbom
     WHERE   assembly_item_id= l_item_id
     AND     organization_id=l_org_id_s
         ;
    EXCEPTION
     WHEN NO_DATA_FOUND THEN
            l_bom_or_eng := 1;
       WHEN OTHERS THEN
          l_bom_or_eng := 1;
    END;

    -- Called BOM Explosion with Oracle API
    bompexpl.exploder_userexit(
      verify_flag             =>0,
      org_id                  =>l_org_id_s,
      order_by                =>1,
      grp_id                  =>l_grp_id,
      session_id              =>l_session_id,
      levels_to_explode       =>l_levels_to_explode,
      bom_or_eng              =>l_bom_or_eng,
      impl_flag               =>1,
      plan_factor_flag        =>2,
      explode_option          =>2,
      module                  =>l_module,
      cst_type_id             =>l_cst_type_id,
      std_comp_flag           =>2,
      expl_qty                =>1,
      item_id                 =>l_item_id,
      alt_desg                =>'',
      comp_code            =>'',
      rev_DATE                =>to_date(SYSDATE, 'YYYY/MM/DD HH24:MI'),
      err_msg                 =>l_err_msg,
      error_code              =>l_error_code
      );

    IF ( l_error_code <> 0 ) THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('ERROR: ' || l_err_msg);
    ELSE
        FOR comp IN exploded_items
        LOOP
            BEGIN
                SELECT segment1
                INTO v_top_item
                FROM mtl_system_items
                WHERE 1=1
                AND inventory_item_id = comp.top_item_id
                AND organization_id = l_org_id_s;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                v_top_item := NULL;
            END;

            BEGIN
                SELECT segment1
                INTO v_assembly_item
                FROM mtl_system_items
                WHERE 1=1
                AND inventory_item_id = comp.assembly_item_id
                AND organization_id = comp.organization_id;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                v_assembly_item := NULL;
            END;

            BEGIN
                SELECT segment1
                INTO v_component_item
                FROM mtl_system_items
                WHERE 1=1
                AND inventory_item_id = comp.component_item_id
                AND organization_id = comp.organization_id;--l_org_id_d;
            EXCEPTION
                WHEN NO_DATA_FOUND THEN
                v_component_item := NULL;
            END;

            DBMS_OUTPUT.PUT_LINE(comp.group_id || '|' || comp.organization_id || '|' || comp.component_item_id || '|' || v_top_item || '|' || v_assembly_item || '|'|| v_component_item || '|' || comp.component_quantity || '|' || comp.extended_quantity);
        END LOOP;
        DELETE FROM bom_explosion_temp WHERE group_id = l_grp_id; -- Delete records FROM bom_explosion_temp Table.
        COMMIT;
    END IF;
END;
/
SPOOL OFF