Oracle EBS Form个性化调用自定义程序库入门

调用自定义程序库是Oracle专为Oracle EBS所有Form序程提供一个内置的扩展的库文件，对于Oracle EBS准标Form程序上的菜单提供非常强大的个性化开发，

1.个性化表单:在“工具”菜单中添加子菜单:添加至“XXXX DRP来源补充规则”。

添加以后效果如下图

2.个性化表单:定义子菜单添加至“XXXX DRP来源补充规则”的触发事件。

变元其实就是CUSTOM.pll文件的event过程参数event_name的值。

3.下载自动定义程序库文件:custom.pll，该文件在$AU_TOP/resource/目录下。

4.修改custom.pll代码:
为了方面管理和最小程度上减少对准标代码的修改，客制化创建一个程序库文件:GB_CUSTOM.pll，然后用准标custom程序库文件调用它。

procedure event(event_name varchar2) is 
  -- 
  -- This procedure allows you to execute your code at specific events 
  -- including: 
  -- 
  --    ZOOM 
  --    WHEN-NEW-FORM-INSTANCE 
  --    WHEN-NEW-BLOCK-INSTANCE 
  --    WHEN-NEW-RECORD-INSTANCE 
  --    WHEN-NEW-ITEM-INSTANCE 
  --    WHEN-VALIDATE-RECORD 
  -- 
  -- Additionally, product-specific events will be passed via this 
  -- interface (see the Applications Technical Reference manuals for  
  -- a list of events that are available).  
  -- 
  -- By default this routine must perform 'null;'. 
  -- 
  -- Oracle Corporation reserves the right to change the events  
  -- available through this interface at any time. 
  -- 
  /* Sample code:
 
    form_name      varchar2(30) := name_in('system.current_form'); 
    block_name     varchar2(30) := name_in('system.cursor_block');  
    param_to_pass1 varchar2(255); 
    param_to_pass2 varchar2(255); 
  begin 
    -- Zoom event opens a new session of a form and 
    -- passes parameter values to the new session.  The parameters
    -- already exist in the form being opened.
    if (event_name = 'ZOOM') then   
      if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then 
        param_to_pass1 := name_in('ORDERS.order_id'); 
        param_to_pass2 := name_in('ORDERS.customer_name'); 
        fnd_function.execute(FUNCTION_NAME=>'DEM_DEMXXEOR',  
                             OPEN_FLAG=>'Y',  
                             SESSION_FLAG=>'Y',  
                             OTHER_PARAMS=>'ORDER_ID="'||param_to_pass1|| 
                               '" CUSTOMER_NAME="'||param_to_pass2||'"'); 
		-- all the extra single and double quotes account for 
		-- any spaces that might be in the passed values 
      end if; 

    -- This is an example of a product-specific event.  Note that as
    -- of Prod 15, this event doesn't exist.
    elsif (event_name = 'OE_LINES_PRICING') then 
      get_custom_pricing('ORDERS.item_id', 'ORDERS.price'); 

    -- This is an example of enforcing a company-specific business
    -- rule, in this case, that all vendor names must be uppercase.
    elsif (event_name = 'WHEN-VALIDATE-RECORD') then
      if (form_name = 'APXVENDR') then
        if (block_name = 'VENDOR') then
          copy(upper(name_in('VENDOR.NAME')), 'VENDOR.NAME');       
        end if;
      end if;
    else 
      null; 
    end if; 
  end event; 
  */ 
  -- 
  -- Real code starts here 
  -- 
  begin 
    ---添加调用客制化程序库文件的过程
    GB_CUSTOM.event(event_name); 
  end event; 

5.创建GB_CUSTOM.pll程序库文件代码

包过程定义代码:

PACKAGE GB_CUSTOM IS
  procedure event(event_name varchar2);
  procedure SaveAssignResource;
END;

包体代码：

PACKAGE BODY GB_CUSTOM IS
  procedure event(event_name varchar2) Is
  Begin
		If(event_name = 'SaveAssignResource') Then
				SaveAssignResource;
		End If;
  End;
  
  procedure SaveAssignResource Is
	  v_AssignmentId   Number;
	  v_ItemNum        Varchar2(30);
	  v_OrgizationId   Number;
	  v_ItemId         Number; --物料ID
	  v_SourcingRuleId Number; --来源补充规则ID
	  v_Count          Number;
	  v_RowId 				 Varchar2(50);
	  v_UserID			   Number;
	  CURSOR C IS
	    SELECT rowid
	      FROM mrp_sr_assignments
	     WHERE assignment_id = v_AssignmentId;
	Begin
	  v_ItemNum      := Name_In('MRP_SOURCING_RULES.SOURCING_RULE_NAME');
	  v_ItemNum      := Substr(v_ItemNum, 2, length(v_ItemNum));
	  v_OrgizationId := Name_In('MRP_SOURCING_RULES.ORGANIZATION_ID');
	  v_UserID			 := fnd_Profile.Value('USER_ID');
	  --查找是否存在记录
	  Begin
	    select ASSIGNMENT_ID
	      Into v_AssignmentId
	      From MRP_SR_ASSIGNMENTS_V
	     Where ORGANIZATION_ID = v_OrgizationId
	       And ENTITY_NAME = v_ItemNum;
	    If (Nvl(v_AssignmentId, 0) > 0) Then
	      Fnd_Message.Set_String('此来源补充规则已经分配');
	      Fnd_Message.show;
	      Return;
	    End If;
	  Exception
	    When No_Data_Found Then
	      SELECT mrp_sr_assignments_s.nextval Into v_AssignmentId From Dual;
	  End;
	  --找到出inventory_item_id
	  Begin
	    Select inventory_item_id
	      Into v_ItemId
	      From MTL_SYSTEM_ITEMS_B
	     Where ORGANIZATION_ID = v_OrgizationId
	       And SEGMENT1 = v_ItemNum;
	  Exception
	    When No_Data_Found Then
	      Fnd_Message.Set_String('物料不存在或已失效！不能添加');
	      Fnd_Message.show;
	      Return;
	  End;
	  --SOURCING_RULE_ID 来源补充规则是否存在
	  Begin
	    v_SourcingRuleId := Name_In('MRP_SOURCING_RULES.SOURCING_RULE_ID');
	    Select Count(*)
	      Into v_Count
	      From MRP_SOURCING_RULES
	     Where SOURCING_RULE_ID = v_SourcingRuleId;
	    If (Nvl(v_Count, 0) = 0) Then
	      Fnd_Message.Set_String('此来源补充规则不存在或已删除');
	      Fnd_Message.show;
	      Return;
	    End If;
	  Exception
	    When No_Data_Found Then
	      Fnd_Message.Set_String('此来源补充规则不存在或已删除');
	      Fnd_Message.show;
	      Return;
	  End;
	  --新增记录
	  INSERT INTO mrp_sr_assignments
	    (assignment_id,
	     assignment_type,
	     sourcing_rule_id,
	     sourcing_rule_type,
	     assignment_set_id,
	     last_update_date,
	     last_updated_by,
	     creation_date,
	     created_by,
	     last_update_login,
	     request_id,
	     program_application_id,
	     program_id,
	     program_update_date,
	     organization_id,
	     category_id,
	     category_set_id,
	     inventory_item_id,
	     secondary_inventory,
	     attribute_category,
	     attribute1,
	     attribute2,
	     attribute3,
	     attribute4,
	     attribute5,
	     attribute6,
	     attribute7,
	     attribute8,
	     attribute9,
	     attribute10,
	     attribute11,
	     attribute12,
	     attribute13,
	     attribute14,
	     attribute15,
	     customer_id,
	     ship_to_site_id)
	  VALUES
	    (v_AssignmentId, --:ASSIGNMENTS.ASSIGNMENT_ID,
	     6, --:assignments.Assignment_Type,
	     v_SourcingRuleId, --:assignments.Sourcing_Rule_Id,
	     1, --:assignments.Sourcing_Rule_Type,
	     1, --:assignments.Assignment_Set_Id,
	     Sysdate, --:assignments.Last_Update_Date,
	     v_UserID, --:assignments.Last_Updated_By,
	     Sysdate, --:assignments.Creation_Date,
	     v_UserID, --:assignments.Created_By,
	     -1, --:assignments.Last_Update_Login,
	     -1, --:assignments.Request_Id,
	     -1, --:assignments.Program_Application_Id,
	     -1, --:assignments.Program_Id,
	     sysdate, --:assignments.Program_Update_Date,
	     v_OrgizationId, --:assignments.Organization_Id,
	     Null, --:assignments.Category_Id,
	     Null, --:assignments.Category_Set_Id,
	     v_ItemId, --:assignments.Inventory_Item_Id,
	     Null, --:assignments.Secondary_Inventory,
	     Null, --:assignments.Attribute_Category,
	     Null, --:assignments.Attribute1,
	     Null, --:assignments.Attribute2,
	     Null, --:assignments.Attribute3,
	     Null, --:assignments.Attribute4,
	     Null, --:assignments.Attribute5,
	     Null, --:assignments.Attribute6,
	     Null, --:assignments.Attribute7,
	     Null, --:assignments.Attribute8,
	     Null, --:assignments.Attribute9,
	     Null, --:assignments.Attribute10,
	     Null, --:assignments.Attribute11,
	     Null, --:assignments.Attribute12,
	     Null, --:assignments.Attribute13,
	     Null, --:assignments.Attribute14,
	     Null, --:assignments.Attribute15,
	     Null, --:assignments.customer_id,
	     Null --:assignments.ship_to_site_id
	     );
	  OPEN C;
	  FETCH C
	    INTO v_RowId;
	  if (C%NOTFOUND) then
	    CLOSE C;
	    RAISE NO_DATA_FOUND;
	  end if;
	  CLOSE C;
	  Fnd_Message.Set_String('已经分配至“XXXX DRP来源补充规则”集中');
	  Fnd_Message.show;
	  FORMS_DDL('COMMIT');
	End;
END;

注意:由于客制化程序库调用了准标程序库Fnd_Message，必须要引用程序库文件FNDSQF.pll。
6.上传程序库文件和编译

将二文件至服务器目录:$AU_TOP/resource/
编译程序库文件：

f60gen module_type=library module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx

f60gen module_type=library module=GB_CUSTOM.pll userid=apps/apps output_file=GB_CUSTOM.plx

注意:以上编译的语法是在Oracle EBS 11i上执行。对于Oracle EBS R12编译的语法如下:

frmcmp_batch module_type=LIBRARY module=CUSTOM.pll userid=apps/apps

frmcmp_batch module_type=LIBRARY module=GB_CUSTOM.pll userid=apps/apps

7.关闭Oracle EBS 表单程序界面，重新点击Oracle EBS Web界面任意链接进入Form程序界面，然后测试代码效果

注意:每次修改上面二个程序库文件以后，必须要关闭所有的Form程序重新登入Form程序，修改的代码才能生效。因此，custom.pll的代码在初始化表单程序时已经加载的。

最后，补充内容说明一下。若在CUSTOM.pll文件中添加如下代码，则进入任何一个功能表单或职责切换都弹出提示。

  procedure event(event_name varchar2) Is
  Begin
    Fnd_Message.Set_String('测试CUSTOM');
    Fnd_Message.show;
  End;