展開BOM

function BeforeReport return boolean is
begin

DECLARE
  L_ORGANIZATION_NAME     VARCHAR2(240);
  L_EXPLODE_OPTION        VARCHAR2(80);
  L_RANGE_OPTION          VARCHAR2(80);
  L_SPECIFIC_ITEM         VARCHAR2(245);
  L_CATEGORY_SET          VARCHAR2(30);
  L_YES                   VARCHAR2(80);
  L_NO                    VARCHAR2(80);
  L_ALT_OPTION            VARCHAR2(80);
  L_ORDER_BY              VARCHAR2(80);
  l_seq_id                NUMBER;
  l_str                   varchar2(2000);
  l_bom_or_eng            NUMBER;
  l_err_msg               VARCHAR2(80);
  l_err_code              NUMBER;
  exploder_error          EXCEPTION;
  loop_error              EXCEPTION;
  item_id_null          EXCEPTION;
  table_name          VARCHAR2(20);
  t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
  l_org_name               VARCHAR2 (60);
  N   NUMBER :=0;
  l_org_id    NUMBER;
a number;
BEGIN
   TABLE_NAME := 'Begin_trigger';    
   /* Calling AOL user exit */
   SRW.USER_EXIT('FND SRWINIT');

   TABLE_NAME := 'Trace';
   IF :P_DEBUG = 'Y' THEN
      SRW.DO_SQL('ALTER SESSION SET SQL_TRACE TRUE');
   END IF;

   TABLE_NAME := 'Check_specific';
   IF (:P_RANGE_OPTION_TYPE = 1) AND
      (:P_ITEM_ID IS NULL) THEN
     SRW.USER_EXIT('FND MESSAGE_NAME NAME="MFG_REQUIRED_VALUE"');
     SRW.USER_EXIT('FND MESSAGE_TOKEN TOKEN="ENTITY" VALUE="specific item"');
         SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":P_MSG_BUF"');
     SRW.MESSAGE('999', :P_MSG_BUF);
         RAISE ITEM_ID_NULL;
   END IF;

   TABLE_NAME := 'Supress_detail';
   IF :P_PRINT_OPTION1_FLAG = 2 THEN
     SRW.SET_MAXROW('Q_ELEMENT', 0);
   END IF;


   TABLE_NAME := 'Ref_desg';
   IF :P_PRINT_OPTION4_FLAG = 2 THEN
     SRW.SET_MAXROW('Q_REF_DESIG', 0);
   END IF;

   TABLE_NAME := 'Sub_comps';
   IF :P_PRINT_OPTION5_FLAG = 2 THEN
     SRW.SET_MAXROW('Q_SUBS_COMPO', 0);
   END IF;

   TABLE_NAME := 'Org_define';   
   SELECT O.ORGANIZATION_NAME
   INTO   L_ORGANIZATION_NAME
   FROM   ORG_ORGANIZATION_DEFINITIONS O
   WHERE  O.ORGANIZATION_ID = :P_ORGANIZATION_ID;

   TABLE_NAME := 'Category_sets';
   IF :P_CATEGORY_SET_ID > 0 THEN
     SELECT CATEGORY_SET_NAME
     INTO   L_CATEGORY_SET
     FROM   MTL_CATEGORY_SETS
     WHERE  CATEGORY_SET_ID = :P_CATEGORY_SET_ID;
     :P_CATEGORY_SET := L_CATEGORY_SET;
   END IF;

  TABLE_NAME := 'Item_flexfields';
  IF :P_ITEM_ID > 0 THEN
    SELECT ITEM_NUMBER
    INTO   L_SPECIFIC_ITEM
    FROM   MTL_ITEM_FLEXFIELDS
    WHERE  ITEM_ID = :P_ITEM_ID
    AND    ORGANIZATION_ID = :P_ORGANIZATION_ID;
    :P_SPECIFIC_ITEM := L_SPECIFIC_ITEM;
  END IF;

   TABLE_NAME := 'Lookups1';
   SELECT SUBSTR(L1.MEANING,1,40),
          SUBSTR(L2.MEANING,1,40),
          SUBSTR(L3.MEANING,1,40),
          SUBSTR(L4.MEANING,1,40)
   INTO   L_EXPLODE_OPTION,        L_ALT_OPTION,
          L_RANGE_OPTION,          L_ORDER_BY
   FROM   MFG_LOOKUPS L1, MFG_LOOKUPS L2, MFG_LOOKUPS L3, MFG_LOOKUPS L4
   WHERE  L1.LOOKUP_TYPE = 'BOM_INQUIRY_DISPLAY_TYPE'
   AND    L1.LOOKUP_CODE = :P_EXPLODE_OPTION_TYPE
   AND    L2.LOOKUP_TYPE = 'MCG_AUTOLOAD_OPTION'
   AND    L2.LOOKUP_CODE = :P_ALT_OPTION_TYPE
   AND    L3.LOOKUP_TYPE = 'BOM_SELECTION_TYPE'  
   AND    L3.LOOKUP_CODE = :P_RANGE_OPTION_TYPE
   AND    L4.LOOKUP_TYPE = 'BOM_BILL_SORT_ORDER_TYPE'
   AND    L4.LOOKUP_CODE = :P_ORDER_BY_TYPE;
   
   TABLE_NAME := 'Lookups2';
   SELECT SUBSTR(L1.MEANING, 1, 4), SUBSTR(L2.MEANING, 1, 4)
   INTO   L_YES, L_NO
   FROM   MFG_LOOKUPS L1,  MFG_LOOKUPS L2
   WHERE  L1.LOOKUP_TYPE = 'SYS_YES_NO'
   AND    L1.LOOKUP_CODE = 1
   AND    L2.LOOKUP_TYPE = 'SYS_YES_NO'
   AND    L2.LOOKUP_CODE = 2;

  :P_YES := L_YES;
  :P_NO  := L_NO;

  TABLE_NAME := 'Print_option';
  IF (:P_PRINT_OPTION1_FLAG = 1) THEN
      :P_PRINT_OPTION1 := L_YES;
   ELSE
      :P_PRINT_OPTION1 := L_NO;
   END IF;

   IF (:P_PRINT_OPTION2_FLAG = 1) THEN
      :P_PRINT_OPTION2 := L_YES;
   ELSE
      :P_PRINT_OPTION2 := L_NO;
   END IF;

   IF :P_PRINT_OPTION3_FLAG = 1 THEN
      :P_PRINT_OPTION3 := L_YES;
   ELSE
      :P_PRINT_OPTION3 := L_NO;
   END IF;

   IF :P_PRINT_OPTION4_FLAG = 1 THEN
      :P_PRINT_OPTION4 := L_YES;
   ELSE
      :P_PRINT_OPTION4 := L_NO;
   END IF;

   IF :P_PRINT_OPTION5_FLAG = 1 THEN
      :P_PRINT_OPTION5 := L_YES;
   ELSE
      :P_PRINT_OPTION5 := L_NO;
   END IF;

   IF :P_PRINT_OPTION6_FLAG = 1 THEN
      :P_PRINT_OPTION6 := L_YES;
   ELSE
      :P_PRINT_OPTION6 := L_NO;
   END IF;

   IF :P_FULL_DESCRIPTION = 1 THEN
      :P_FULL_DESC_CHOICE := L_YES;
   ELSE
      :P_FULL_DESC_CHOICE := L_NO;
   END IF;
 
   IF :P_IMPL_FLAG = 1 THEN
      :P_IMPL := L_YES;
   ELSE
      :P_IMPL := L_NO;
   END IF;

   IF :P_PLAN_FACTOR_FLAG = 1 THEN
      :P_PLAN_FACTOR := L_YES;
   ELSE
      :P_PLAN_FACTOR := L_NO;
   END IF;


   TABLE_NAME := 'Assign_values';
   :P_ORGANIZATION_NAME := L_ORGANIZATION_NAME;
   :P_EXPLODE_OPTION := L_EXPLODE_OPTION;
   :P_RANGE_OPTION := L_RANGE_OPTION;
   :P_ALT_OPTION := L_ALT_OPTION;
   :P_ORDER_BY := L_ORDER_BY;

   IF :P_BOM_OR_ENG = 'BOM' THEN
     l_bom_or_eng := 1;
   ELSE
     l_bom_or_eng := 2;
   END IF;

/* Bug : #  1468219   : 10/20/2000  :  smusanna
   Added the following code to support the Large Organization Structures  */

   TABLE_NAME := 'Org Hierarchy';
   IF :P_ALL_ORGS = 1 then
      
           for C1 in       (select organization_id from MTL_PARAMETERS MP
                        where  master_organization_id =
                        (SELECT master_organization_id  FROM
                        MTL_PARAMETERS WHERE organization_id = :P_ORGANIZATION_ID )
                         AND MP.organization_id 
                        IN (SELECT organization_id
                       FROM ORG_ACCESS_VIEW
                         WHERE responsibility_id =
                        FND_PROFILE.Value('RESP_ID')
                         AND Resp_application_id =
                        FND_PROFILE.value('RESP_APPL_ID')))
                LOOP
                        N:=N+1;
                        t_org_code_list(N) := C1.organization_id;

                      
                END LOOP;
     :P_ALL_ORGS := 'Yes';

  ELSIF :P_ALL_ORGS = 2 THEN 
    IF :P_ORG_HIERARCHY IS NOT NULL THEN
/*              SELECT organization_name into l_org_name
                FROM   org_organization_definitions
                WHERE  organization_id = :P_ORGANIZATION_ID; */

        INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(
               :P_ORG_HIERARCHY, :P_ORGANIZATION_ID ,t_org_code_list );
 
      ELSIF :P_ORG_HIERARCHY IS  NULL THEN
            t_org_code_list (1) := :P_ORGANIZATION_ID;
   
     END IF;
      :P_ALL_ORGS :='No';
  ELSE
      t_org_code_list (1) := :P_ORGANIZATION_ID;   
  END IF; 
 
  SELECT BOM_LISTS_S.nextval into :P_SEQUENCE_ID1 from dual;
  

          FOR I in t_org_code_list.FIRST..t_org_code_list.LAST
     
          LOOP
           
        INSERT INTO BOM_LISTS (
        ORGANIZATION_ID,
        SEQUENCE_ID,
            ALTERNATE_DESIGNATOR )
        VALUES (
        t_org_code_list(I),
        :P_SEQUENCE_ID1,
            I);

          END LOOP;
   
 
 for I in t_org_code_list.FIRST..t_org_code_list.LAST
 LOOP
    :P_ORGANIZATION_ID := t_org_code_list(I);

  /* select a sequence id */
  TABLE_NAME := 'Select_sequence';
  SELECT BOM_LISTS_S.NEXTVAL
  INTO   l_seq_id
  FROM   DUAL;

  /* Copy to P_sequence_id for After report trigger to use */
  :P_sequence_id := l_seq_id;


  TABLE_NAME := 'Locator_flex';
   SRW.USER_EXIT('FND FLEXSQL CODE="MTLL"  NUM=":P_LOCATOR_STRUCTURE_ID"
                  APPL_SHORT_NAME="INV" UTPUT=":P_LOCATOR_FLEXDATA" 
                  MODE="SELECT" DISPLAY="ALL"   
                  TABLEALIAS="MIL"');

  TABLE_NAME := 'Item_flex';
  IF :P_RANGE_OPTION_TYPE = 2 THEN
   IF (:P_ITEM_FROM IS NOT NULL) THEN
    IF (:P_ITEM_TO IS NOT NULL) THEN
      SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
               UTPUT=":P_ASS_BETWEEN" PERATOR="BETWEEN" MODE="WHERE"
               OPERAND1=":P_ITEM_FROM" OPERAND2=":P_ITEM_TO"
               NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="MSI"');
    ELSE
       SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
               UTPUT=":P_ASS_BETWEEN" PERATOR=">=" MODE="WHERE"
               OPERAND1=":P_ITEM_FROM"
               NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="msi"');
    END IF;
   ELSE      /* :P_ITEM_FROM IS NULL */
    IF (:P_ITEM_TO IS NOT NULL) THEN 
       SRW.USER_EXIT('FND FLEXSQL CODE="MSTK" APPL_SHORT_NAME="INV"
               UTPUT=":P_ASS_BETWEEN" PERATOR="<=" MODE="WHERE"
               OPERAND1=":P_ITEM_TO"
               NUM=":P_ITEM_STRUCTURE_ID" TABLEALIAS="msi"');
    END IF;
   END IF;  /* item */

   TABLE_NAME := 'Category_flex';
   IF (:P_CATEGORY_FROM IS NOT NULL) THEN
    IF (:P_CATEGORY_TO IS NOT NULL) THEN
     SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
               UTPUT=":P_CAT_BETWEEN" PERATOR="BETWEEN" MODE="WHERE"      
               OPERAND1=":P_CATEGORY_FROM" OPERAND2=":P_CATEGORY_TO"
               NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
    ELSE
     SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
               UTPUT=":P_CAT_BETWEEN" PERATOR=">=" MODE="WHERE"
               OPERAND1=":P_CATEGORY_FROM"
               NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
    END IF;
   ELSE
    IF (:P_CATEGORY_TO IS NOT NULL) THEN
      SRW.USER_EXIT('FND FLEXSQL CODE="MCAT" APPL_SHORT_NAME="INV"
               UTPUT=":P_CAT_BETWEEN" PERATOR="<=" MODE="WHERE"
               OPERAND1=":P_CATEGORY_TO"
               NUM=":P_CATEGORY_STRUCTURE_ID" TABLEALIAS="mc"');
    END IF;
   END IF;  /* category */
  end if;   /* :P_RANGE_TYPE = 2 */


  TABLE_NAME := 'bom_lists';
  l_str :='INSERT INTO BOM_LISTS (SEQUENCE_ID, ASSEMBLY_ITEM_ID,
                                ALTERNATE_DESIGNATOR) ';

  TABLE_NAME := 'l_string'; 
  IF :P_RANGE_OPTION_TYPE = 1 THEN
    l_str := l_str ||
      '  SELECT DISTINCT '|| TO_CHAR(l_seq_id) ||',
                '|| TO_CHAR(:P_ITEM_ID) ||',
                bbom.alternate_bom_designator
         FROM   bom_bill_of_materials bbom
         WHERE  bbom.organization_id = '||TO_CHAR(:P_ORGANIZATION_ID)||'
         AND    bbom.assembly_item_id = '|| TO_CHAR(:P_ITEM_ID) ;
  ELSE
    l_str := l_str ||
       '  SELECT
                DISTINCT '|| TO_CHAR(l_seq_id) ||',
                msi.inventory_item_id,
                bbom.alternate_bom_designator
         FROM   mtl_item_categories mic,
                mtl_system_items msi,
                mtl_categories mc,
                bom_bill_of_materials bbom
         WHERE  '||:P_ASS_BETWEEN||'
         AND    msi.inventory_item_id = mic.inventory_item_id
         AND    msi.organization_id =
                '||TO_CHAR(:P_ORGANIZATION_ID)||'
         AND    mic.organization_id =
                '||TO_CHAR(:P_ORGANIZATION_ID)||'
         AND    mic.category_id = mc.category_id
         AND    mic.category_set_id =
                '||TO_CHAR(:P_CATEGORY_SET_ID)||'
         AND    mc.structure_id =
                '||TO_CHAR(:P_CATEGORY_STRUCTURE_ID)||'
         AND    '||:P_CAT_BETWEEN||'
         AND    msi.inventory_item_id = bbom.assembly_item_id
         AND    msi.organization_id = bbom.organization_id
       AND     msi.bom_enabled_flag = ''Y''';
   END IF;
 
   l_str := l_str ||
       '  AND    (  ('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 1)
                 OR
                   ('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 2
                    AND bbom.alternate_bom_designator IS NULL)
                 OR
                   ('||TO_CHAR(:P_ALT_OPTION_TYPE)||' = 3
                    AND NVL(bbom.alternate_bom_designator,''XXX'')=
                        NVL('''||:P_ALTERNATE_DESG||''', ''XXX''))
                )
          AND   (  ('''||:P_BOM_OR_ENG||''' = ''BOM''
                    AND bbom.assembly_type = 1)
                 OR
                   ('''||:P_BOM_OR_ENG||''' = ''ENG'')
                )' ;
  TABLE_NAME := 'do_sql';
  SRW.DO_SQL(l_str);

  TABLE_NAME := 'exploder';

   -- Fix Bug 641865
   -- If the revision date paramter is not entered then initialize the p_revision_date
   -- to current date.
  IF :p_revision_date IS NULL THEN
    :p_revision_date := to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS');
  END IF;

  /* Call BOM exploder */
   bompexpl.explosion_report
     (org_id => :P_ORGANIZATION_ID,
        order_by => :P_ORDER_BY_TYPE,
        list_id => l_seq_id,
        grp_id => :P_GROUP_ID,
        session_id => -1,
        levels_to_explode => :P_EXPLOSION_LEVEL,
        bom_or_eng => l_bom_or_eng,
        impl_flag => :P_IMPL_FLAG,
        explode_option => :P_EXPLODE_OPTION_TYPE,
        module => 2,
        cst_type_id => -1,
        std_comp_flag => -1,
        expl_qty => :P_EXPLOSION_QUANTITY,
      report_option => -1,
      req_id => :P_CONC_REQUEST_ID,
      lock_flag => -1,
      rollup_option => -1,
      alt_rtg_desg => '',
        alt_desg => :P_ALTERNATE_DESG,
        rev_date => :P_REVISION_DATE,
        err_msg => l_err_msg,
        error_code => l_err_code,
        cst_rlp_id => 0,
        verify_flag => :P_VERIFY_FLAG,
        plan_factor_flag => :P_PLAN_FACTOR_FLAG,
        incl_lt_flag => :P_PRINT_OPTION3_FLAG);

 END LOOP;

 
  if (:P_VERIFY_FLAG = 1 and l_err_code = 9999) then
     RETURN(TRUE);
  end if;

  if (:P_VERIFY_FLAG = 2 and l_err_code = 9999) then
    raise loop_error;
  end if;

  if l_err_code != 0 then
    if l_err_code = 9998 then -- maximum levels exceeded
      raise loop_error;
    else
      raise exploder_error;
    end if;
  end if;

  RETURN (TRUE);

    EXCEPTION
    WHEN SRW.DO_SQL_FAILURE THEN
        SRW.MESSAGE('1000', TABLE_NAME || SQLERRM);
        RETURN (FALSE);
    WHEN exploder_error THEN
        SRW.MESSAGE('1000', l_err_msg);
        RETURN (FALSE);
    WHEN loop_error THEN
          :P_ERR_MSG := l_err_msg;
          SRW.USER_EXIT('FND MESSAGE_NAME NAME=":P_ERR_MSG"');
          SRW.USER_EXIT('FND MESSAGE_GET OUTPUT_FIELD=":P_MSG_BUF"');
          SRW.MESSAGE(l_err_code, :P_MSG_BUF);
        RETURN (FALSE);
    WHEN ITEM_ID_NULL THEN
        raise SRW.PROGRAM_ABORT;
    WHEN OTHERS THEN
        SRW.MESSAGE('2000', TABLE_NAME || SQLERRM);            
        RETURN (FALSE);
END;   return (TRUE);
end;

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

转载于:http://blog.itpub.net/7960369/viewspace-231238/