, , , , , ,

Form Personalization from Sales Order form and Purchase Order form aganist the Item

Form Personalization for Purchase Order and Sales Order while choose Item from procure material from Purchase order form or sell the Material from Sales Order form at the time we should know the that particular material is
  1. VATABLE
  2. EXCISEABLE
  3. RECOVERABLE (CLAIMABLE)
  4. ITEM CLASS ENABLED
    The above all condition satisfied we can proceed to procure material or sell the material other wise give the note message for warning but it should not stop any transaction.
Refer the screen shots below…
Expected output form Sales Order form
Form personalization
Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize
Condition :
PELEXVAT(${item.LINE.ORDERED_ITEM_DSP.value},${item.LINE.ship_from_org_id.value})=2
 Message Text :
= ‘THIS ‘ || :LINE.ORDERED_ITEM_DSP || ‘ ITEM EXCISE OR VAT NOT DEFINED PLS CHECK.’
 
PELEXVAT (Procedure)
CREATE OR REPLACE FUNCTION APPS.PELEXVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number 
IS 
FLAG Number;
BEGIN
Select (Case When Flag=0 Then 1 Else 2 End) as Flag Into Flag From(
Select Abs(Sum(F1+F2)-Sum(F3+F4)) Flag From(
Select (Case When Sum(F1)<>0 Then Sum(F1) Else 3 End ) as F1,
(Case When Sum(F2)<>0 Then Sum(F2) Else 4 End) as F2,
(Case When Sum(F3)<>0 Then Sum(F3) Else 5 End)as F3,
(Case When Sum(F4)<>0 Then Sum(F4) Else 6 End) as F4 From (
Select (Case When F1=1 Then Sum(F1) End) as F1,
(Case When F2=1 Then Sum(F2) End) as F2,
(Case When F3=1 Then Sum(F3) End) as F3,
(Case When F4=1 Then Sum(F4) End) as F4 From (
Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 5 End) as F1,
( Case When ATTRIBUTE_CODE=’MODVATABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F2,
( Case When ATTRIBUTE_CODE=’RECOVERABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 4 End) as F3,
( Case When ATTRIBUTE_CODE=’APPLICABLE’ And ATTRIBUTE_VALUE=’Y’ Then 1 Else 11 End) as F4
From ( Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,templ_org_regns_id,
ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (
select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4)));
DBMS_OUTPUT.Put_Line(Flag);
RETURN FLAG;
END;
/
Expected output form Purchase Order form
 
Form personalization
Nav:Open the form and choose Object trigger value → Help → Diagnostics → Custom code → Personalize
Condition :
PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},
${item.PO_HEADERS.SHIP_TO_ORG_ID.value})<>5
OR
PELEXITEMVAT(${item.PO_LINES.ITEM_NUMBER.value},
${item.PO_HEADERS.SHIP_TO_ORG_ID.value}) =0
PELEXITEMVAT (Procedure)
CREATE OR REPLACE Function APPS.PELEXITEMVAT(ITEM_CODE VARCHAR2,SHIP_ORG_ID NUMBER) RETURN Number
IS
FLAG Number;
BEGIN
Select (Case When Flag Is Null Then 0 Else Flag End ) as Flag Into Flag From (
Select Sum(F1)+Sum(F2)+SUM(F3)+SUM(F4)+SUM(F5) as Flag From (
Select (Case When F1=1 Then Sum(F1) End) as F1,
(Case When F2=1 Then Sum(F2) End) as F2,
(Case When F3=1 Then Sum(F3) End) as F3,
(Case When F4=1 Then Sum(F4) End) as F4,
(Case When F5=1 Then Sum(F5) End) as F5 From (
Select Distinct ( Case When ATTRIBUTE_CODE=’EXCISABLE’ Then 1 Else 5 End) as F1,
( Case When ATTRIBUTE_CODE=’MODVATABLE’ Then 1 Else 4 End) as F2,
( Case When ATTRIBUTE_CODE=’RECOVERABLE’ Then 1 Else 4 End) as F3,
( Case When ATTRIBUTE_CODE=’APPLICABLE’ Then 1 Else 11 End) as F4,
( Case When ATTRIBUTE_CODE=’ITEM’ Then 1 Else 11 End) as F5
From (
Select ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,CREATION_DATE,TEMPLATE_ID,
templ_org_regns_id,ORGANIZATION_ID,RGM_ITEM_REGNS_ID
From (
select Distinct A.ATTRIBUTE_CODE,ATTRIBUTE_VALUE, SEGMENT1,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’EXCISABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’MODVATABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’APPLICABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’RECOVERABLE’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct A.ATTRIBUTE_CODE, ATTRIBUTE_VALUE, SEGMENT1 ,A.CREATION_DATE,A.TEMPLATE_ID,
B.templ_org_regns_id,I.ORGANIZATION_ID,A.RGM_ITEM_REGNS_ID
From JAI_RGM_ITM_TMPL_ATTRS a,
jai_rgm_tmpl_itm_regns b,
jai_rgm_tmpl_org_regns c,
Mtl_System_Items_b I
Where b.inventory_item_id =I.INVENTORY_ITEM_ID
And c.templ_org_regns_id = b.templ_org_regns_id
And C.ORGANIZATION_ID=I.ORGANIZATION_ID
and c.template_id = a.template_id
And (ATTRIBUTE_CODE =’ITEM CLASS’ ) And SEGMENT1 =ITEM_CODE
And C.ORGANIZATION_ID=SHIP_ORG_ID
Union All
select Distinct ‘ITEM’ as ATTRIBUTE_CODE, ‘ITEM’ as ATTRIBUTE_VALUE, B.SEGMENT1 ,Null as CREATION_DATE,0 as TEMPLATE_ID,0 as templ_org_regns_id,B.ORGANIZATION_ID,0 as RGM_ITEM_REGNS_ID
From jai_rgm_tmpl_itm_regns A,Mtl_System_Items_B B
Where A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID And
B.SEGMENT1 =ITEM_CODE
And B.ORGANIZATION_ID=SHIP_ORG_ID )))Group By F1,F2,F3,F4,F5));
DBMS_OUTPUT.Put_Line(Flag);
RETURN FLAG;
END;
/
/by
, , ,

Form Personalization – How to Change Field Name

Form Personalization – How to Change Field Name

Form Personalization feature allows us to alter the behavior of Forms-based screens, including changing properties, displaying messages etc.
For a single form-function(a form running in a particular context based on parameters passed to it defined at function level) we can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform.

Here we will discuss about how can we change the field display name.

Basic Requirement

Our basic requirement is to change name the ‘Latest Start Date‘ field to ‘ABCD‘ in people Screen. Remember this name change should only be applicable for persons who are using ‘UK HRMS Manager’.

Solution ApproachForm Personalization feature is declarative and any personalization to form may interfere with base code of a  form.
before we start personalization please ensure that the following security profiles are properly set   1) FND_HIDE_DIAGNOSTICS (Hide Diagnostics menu entry)
  2) DIAGNOSTICS (Utilities:Diagnostics)

a) Now open the people & Assignment form from the navigator menu. Click on the ‘Latest Start Date field’. Now go to  Help >> Diagnostics >>  Properties >>  Item.
    It will display the ‘Object Properties’ window. Note Down the  Object2 value (HIRE_DATE) which is nothing but the name of the item.

b) Now to personalize the screen, go to  Help >> Diagnostics >> Custom Code >> Personalize
 Set the following values

Condition TabSeq:- Next highest available number.
Description:- Test Personalization
Level:- Function
Trigger Event:- WHEN-NEW-FORM-INSTANCE
Trigger Object**:-
Condition:-
Processing Mode:- Both
Scope:- Site
** Depending on the Trigger Event, this field may be Disabled, or Enabled and Required in which case it will validate against a List of Values. For example, if Trigger Event WHEN-NEW-ITEM-INSTANCE is selected, then we must enter a specific block.field for that trigger to be processed.

Action Tab
Seq:- 10
Type:- Property
Description:-
Language***:- All
Object Type:- Item
Target Object:- PERSON.HIRE_DATE (Search with string that we copied from step a)
Property Name:- PROMPT_TEXT
Value:- ABCD

 ***  Select ‘All’ to have the action processed for any language, or select a specific language.Generally text-related personalizations are applied for a specific
       language.

c) Validate the design and click on Apply Now.

Note:- 1) Since we have selected processing mode as ‘Both’, hence the field name ‘ABCD’ will appear under both the condition ‘New form is open’ and
             ‘Enter-Query Mode’.
                If we select the processing mode as ‘Only in Enter-Query Mode’, then we will see the original name of the field while opening the form.Where as  if we
            query the form it will execute the trigger event and change the name of the field.

      2) Each Rule consists of one or more Scope rows, and one or more Actions. If a Rule has no Scope rows or Action rows, it is not processed. Note that
         upon saving a Rule, if no Scope rows have been entered the form will automatically create a row at the Site level. If any scope matches the current
         runtime context then the Rule will be processed.

/by
, , ,

Calling Stored procedure through Forms Personalization

Steps:
1. Personalize the form
2. Create an action of type “BuiltIn”
3. BuiltIn Type for Action should be “Execute a Procedure”
4. Argument should be as below
=’declare
v_field_value VARCHAR2(200) ;
begin
plsql_package.procedurenameHere ;
end’
or alternately
=’declare
v_field_value VARCHAR2(200) ;
begin
XX_PRC(”’||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||”’);
end’
Note the syntax, after =, entire declare begin end is within single quote.
Also, there is no semi colon after “end”
You can pass field values as
”’||${item.BLOCKNAME.FIELDNAME.value}||”’
e.g.
A simple way to understand is the begin end block should be enclosed in single quotes and the fields should be enclosed in double quotes.
=‘begin
test_procedure( “’||:GLOBAL.field||’”,”‘||${item.block.field.value}||’”);
end’
/by
, , ,

Oracle Forms Personalization

With the Oracle E-Business Suite release 11.5.10, the Oracle has introduced a mechanism which revolutionizes the way the forms can be customized to fulfill the customer needs. For many years, Oracle Applications has provided a custom library using which the look and behavior of the standard forms can be altered, but the custom library modifications require extensive work on SQL and PL/SQL. In the release 11.5.10, Oracle has provided a simple and easy feature to implement the customer specific requirements without modifying the underlying forms code or CUSTOM library. Although CUSTOM library still can be used for forms customization to implement the complex business logic, the personalization feature provided in the latest release is easy, faster and requires minimum development effort.

Why Forms Personalization?

  • Oracle Supports personalization unlike customization.
  • Personalizations are stored in tables rather than files.
  • Will not have a bigger impact when you upgrade or apply patches to the environment.
  • Can be moved easily through FNDLOAD from one instance to other.
  • Can be restricted at site/responsibility/user level.
  • Easy to disable/enable with click of a button.
  • Personalization stores who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.
  • Can be applied to new responsibilities/users easily.
  • Can be restricted to a function or form.

What can be implemented through Forms Personalization?

The below can be done using Personalization: 
  • Zoom from one form to another.
  • Pass data from one form to another through global variables.
  • Change LOV values dynamically.
  • Enable/Disable/Hide fields dynamically
  • Display user friendly messages when required
  • Launch URL directly from oracle form
  • Execute PL/SQL programs through FORM_DDL package
  • Call custom libraries dynamically

Personalization Tables:

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_LIST
FND_FORM_CUSTOM_PROP_VALUES

Invoking the Personalization screen:

The personalization form should be used to implement the custom rules on a specific form. The specific form refers to the desired form on which you want to apply the custom business logic or modify the form behavior.
The personalization form is invoked by…
Menu Navigation: Help > Diagnostics > Custom Code > Personalize

Disable the personalization feature:

It is possible that a change you make completely breaks a form, to the point that it will not even run! Here’s how to recover:
  • On the pulldown menu, choose Help > Diagnostics > Custom Code > Off
    • This will disable all callouts to Forms Personalization
  • Run the form of interest
    • It should run now, because your changes were skipped
  • Invoke the Personalization screen and correct the problem
  • On the pulldown menu, choose Help > Diagnostics > Custom Code > Normal to re-enable processing of Personalizations

Limitations:

Although it is faster than a speeding bullet, it is not able to leap over tall buildings:
  • You can only change what Forms allows at runtime:
    • Cannot create new items
    • Cannot move items between canvases
    • Cannot display an item which is not on a canvas
    • Cannot set certain properties
    • Cannot change frames, graphics, boilerplate
  • You can only respond to certain Trigger Events:
    • WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-RECORD-INSTANCE, WHEN-NEW-ITEM-INSTANCE
    • WHEN-VALIDATE-RECORD (not in all forms)
    • Product-specific events
  • May interfere with, or be overridden by, base product code
  • Expected user is an Admin/Developer
    • Knowledge of Oracle Developer is extremely desirable
    • Knowledge of PL/SQL, Coding Standards and/or APIs required in some cases
  • Normal rules for customizations apply
    • Extensive testing in a Test environment is required!

Relationship with CUSTOM library:

  • CUSTOM is a stub library Oracle ships that receives Trigger Events. Customers are free to add any code they like to it.
  • CUSTOM and Form Personalizations drive off the same Trigger Events.
    • Form Personalizations are processed first, then the event is sent to CUSTOM
  • CUSTOM can do more because it has complete access to all PL/SQL and SQL.
  • But for most changes, Form Personalizations is adequate and is significantly simpler.
/by