Monday, January 11, 2016

Oracle CUSTOM.PLL Scripts

How to make ‘Customer PO’ a Mandatory field?
For example to have customer PO number as a mandatory field
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
   if (form_name = 'OEXOEORD') then
    app_item_property2.set_property('ORDER.CUST_PO_NUMBER',REQUIRED, PROPERTY_ON); 
   end if;
end if;

How to restrict cases for Custom PO field?
For example restricting Lower case in Customer PO field
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
  if (form_name = 'OEXOEORD') then
     app_item_property2.set_property('ORDER.CUST_PO_NUMBER',CASE_RESTRICTION, UPPERCASE);
   end if;
end if;

How to change the background color for the field ‘Payment Terms’?
For example how to Change the background color to Fuchsia(r255g0b255)
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
  if (form_name = 'OEXOEORD') then
    app_item_property2.set_property('ORDER.TERMS',BACKGROUND_COLOR,'r255g0b255');
  end if;
end if;

How to change the prompt for a field?
For Changing the prompt of field Order Number on the Release Sales Order form
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
  if (form_name = 'WSHFRREL' AND block_name = 'RELEASE') then
     app_item_property2.set_property ('RELEASE.ORDER_NUMBER', prompt_text, 'Sales Order Number');
   end if;
end if;

How to make a field non editable field but the values needs to be defaulted?
For disabling the field
if (event_name = 'WHEN-NEW-ITEM-INSTANCE') then
  if (form_name = 'OEXOEORD' AND block_name = 'ORDER') then
   COPY ('Business World', 'ORDER.SOLD_TO');
   VALIDATE (item_scope);
   app_item_property2.set_property ('ORDER.SOLD_TO', enabled, property_false);
  end if;
end if;
The Customer Name field is grayed out but the value is passed by using Copy function.

How to hide Lines Tab in Sales Order form?
 my_tab_page_id TAB_PAGE;
 begin
   if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
      if (form_name = 'OEXOEORD') then
         my_tab_page_id := FIND_TAB_PAGE('ORDER_REGIONS.LINE_ITEMS');
         SET_TAB_PAGE_PROPERTY(my_tab_page_id,VISIBLE,property_FALSE);
      end if;
    end if;

How to rename ‘Lines’ tab to XX Items tab?
  my_tab_page_id TAB_PAGE;
  begin
     if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
        if (form_name = 'OEXOEORD') then
           my_tab_page_id := FIND_TAB_PAGE('ORDER_REGIONS.LINE_ITEMS');
           SET_TAB_PAGE_PROPERTY(my_tab_page_id,LABEL,'XX Items');
       end if;
     end if;

How to hide Book Order Button?
For example to hide Book button on the Sales Order Form
 if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
    if (form_name = 'OEXOEORD') then
       app_item_property2.set_property('ORDER_CONTROL.BOOK_ORDER',displayed, PROPERTY_false);
     end if;
  end if;

How to Rename Book Order Button?
 if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
   if (form_name = 'OEXOEORD') then
      app_item_property2.set_property('ORDER_CONTROL.BOOK_ORDER',Label,'Please confirm');
   end if;
 end if;

How to make a block read-only ?
 if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
    if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
       set_block_property(block_name, insert_allowed,property_false);
    end if;
 end if;

How to ensure user enters not more than 3 characters in ‘Customer PO’ field and exits the field ?
 if (form_name = 'OEXOEORD') then
   if LENGTH(name_in('ORDER.CUST_PO_NUMBER'))>3
     and
  GET_ITEM_PROPERTY('ORDER.CUST_PO_NUMBER',UPDATE_COLUMN) = 'TRUE'
     then
    V_REC_NUM := name_in('SYSTEM.CURSOR_RECORD');
    SET_RECORD_PROPERTY(V_REC_NUM,'INV_SUM_FOLDER',STATUS,NEW_STATUS);
     fnd_message.set_name('FND','PO Number must be <= 3 characters');
     fnd_message.Error;
     RAISE FORM_TRIGGER_FAILURE;
   end if;
 end if;

How to prevent a particular user from entering an Odd quantity for a particular item?
 b := fnd_profile.VALUE ('user_id');
 if (b = '1008697') then
   if (event_name = 'WHEN-VALIDATE-RECORD') then
      if (form_name = 'OEXOEORD' AND block_name = 'LINE') then
         if (NAME_IN ('LINE.ORDERED_ITEM_DSP') = 'AS54888') then
           if (MOD (NAME_IN ('LINE.ORDERED_QUANTITY'), 2) = 0) then
             fnd_message.set_string ('Even quantities for ' ||NAME_IN(LINE.ORDERED_ITEM_DSP)|| ' not allowed');
             fnd_message.show ();
             RAISE form_trigger_failure;
            end if;
              fnd_message.set_string('Entered quantity is Odd so no problem');
              fnd_message.show();
         end if;
        end if;
     end if;
   end if;

How to enable Zoom for a particular form?
For example to open the Onhand Quantity from Item Description field in lines tab of Sales Order form To enable the Zoom function
FUNCTION zoom_available
RETURN BOOLEAN
IS
form_name VARCHAR2 (30) := NAME_IN ('system.current_form');
block_name VARCHAR2 (30) := NAME_IN ('system.cursor_block');
BEGIN
 if (form_name = 'OEXOEORD' AND block_name = 'LINE') then
   RETURN TRUE;
 else
   RETURN FALSE;
 end if;
END zoom_available;

Following code helps to Onhand Quantity Form and to pass the item name to Onhand Quantity from Sales Order Form and navigate to Item field while clicking the Zoom button.
 procedure event(event_name varchar2) is
  param_to_pass1 VARCHAR2 (255);
  b varchar2(20);
 begin
  if (event_name = 'ZOOM') then
    if (form_name = 'OEXOEORD' AND block_name = 'LINE') then
       param_to_pass1 := NAME_IN ('LINE.ORDERED_ITEM_DSP');
           fnd_function.EXECUTE (function_name => 'INV_INVMATWB',
                                                   open_flag => 'Y',
                                                   session_flag => 'Y',
                                                   other_params => 'ITEMS="' || param_to_pass1 || '"' );
    end if;
  end if;
  if (event_name = 'WHEN-NEW-RECORD-INSTANCE') then
   if (form_name = 'INVMATWB' AND block_name = 'MATERIAL_QF') then
       b := fnd_profile.VALUE ('user_name');
       fnd_message.set_string (NAME_IN ('parameter.ITEMS')||'is entered by user' ||b);
       fnd_message.show ();
       GO_ITEM ('MATERIAL_QF.ITEM');
       COPY (NAME_IN ('parameter.ITEMS'), ('MATERIAL_QF.ITEM'));
       VALIDATE (item_scope);
    END IF;
  end event;

How to enable a Special button?
Below is an example showing the menu button from sales order form
 a menuitem;
 Begin
  a := FIND_MENU_ITEM ('SPECIAL.SPECIAL15');
  if (event_name = 'WHEN-NEW-BLOCK-INSTANCE') then
    if (form_name = 'OEXOEORD' AND block_name = 'LINE') then
       app_special2.instantiate ('SPECIAL15', 'Query Form');
       SET_MENU_ITEM_PROPERTY (a, displayed, property_true);
       SET_MENU_ITEM_PROPERTY (a, enabled, property_true);
    end if;
  end if;
  if (event_name = 'SPECIAL15') then
    if (form_name = 'INVIDITM') then
     fnd_function.EXECUTE (function_name => 'INV_INVMATWB',
                           open_flag => 'Y',
                           session_flag => 'Y' );
    end if;
  end if;

How to switch off the custom code at the run time?
This is similar to switching off the custom code using Help --> Diagnostics --> Custom --> Custom Off
Note: Switching off customization means that Codes written in custom library and for personalization are switched off. This does not switch off Custom triggers or Custom code written on the standard forms.

A sample code for switch off customization:
 IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
   IF fnd_profile.value('USER_ID') =1318 THEN
    copy ( 'OFF' , 'GLOBAL.APP_CUSTOM_MODE' ) ;
   ELSE
     copy ( 'NORMAL' , 'GLOBAL.APP_CUSTOM_MODE' ) ;
   END IF ;

 END IF ;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect