ADDING LOV IN ERP

ADDING LOV IN ERP

The below steps are change the LOV in “Supplier” field in the “Define User” form using Form Personalization (just want to list the suppliers who have the location in” NY (New York)”) on Oracle Apps R12:

1. Open the Define User Form on System Administrator Responsibility (Security > Users > Define)
2. Open the Personalization form using the navigation “Help > Diagnostics > Custom Code > Personalize”
3. Enter the sequence number as 10 and description “Change Value of Supplier LOV”
4. Select the Trigger Event as “WHEN-NEW-FORM-INSTANCE” and save the changes
5. On Actions tab, enter or select the following values
   • Sequence = 10
   • Type = Builtin
   • Description = Create New Record Group
   • Language = All
   • Enabled = Yes
6. On the right side of Actions tab, enter or select the following values
   • Builtin Type = Create Record Group from Query
   • Arguments = select full_name, supplier_name, supplier_location, supplier_contact_id from icx_supplier_contact_lov where supplier_location like ‘%NY%’ order by full_name, supplier_name, supplier_location
   • Group Name = EY_SUPPLIER_GROUP
7. Click on the “Validate” button, then click on “Apply Now” button
8. On next Actions tab, enter or select the following values
   • Sequence = 20
   • Type = Property
   • Description = Assign New Record Group
   • Language = All
   • Enabled = Yes
9. On the right side of Actions tab, enter or select the following values
   • Object Type = LOV
   • Target Object = SUPPLIER_LOV
   • Property Name = GROUP_NAME
   • Value = EY_SUPPLIER_GROUP
10. Validate All (Menu Tool > Validate All), Apply and Save the changes
11. Close the Define User form and open it again.

For any queries contact mzeeshan.shaikh@hotmail.com

Displaying onhand quantities

DISPLAYING ONHAND QUANTITY.

We can display onhand quantity in a different responsibility using form personalization.

First create a function at database level which calculates on hand quantity for a particular item in a particular organization. This can be done using

create function CUSTONHANDQUANTY(ITEM_ID in NUMBER, ORG in NUMBER)

return number is

onhand number;

begin

SELECT SUM(TRANSACTION_QUANTITY)

INTO onhand

FROM MTL_MATERIAL_TRANSACTIONS

WHERE INVENTORY_ITEM_ID = ITEM_ID

AND ORGANIZATION_ID = ORG;

return(onhand);

end;

This function takes two arguments item id and organization id and returns the onhand quantity, which will be then displayed to user.

Now customize the form where you want to display this quantity.

Here are the steps:

Here we uses requisition form on which we will display a message which shows the onhand quantity.

Login to ERP and select purchasing responsibility then select change organization option. This is mandatory to get onhand quantity.

Here select your organization and navigate to requisition form.

Navigate to Help > Diagnostics > Custom Code > Personalization

Here it will ask for password of apps enter the password and another form will display.

This is personalization form.

Insert seq no and description

Level = function and

Enabled = yes

On condition tab select trigger event to WHEN-NEW-ITEM-INSTANCE and trigger object will be LINES.ITEM_REVISION.

LINES.ITEM_REVISION is the field on which the trigger executes.

Condition = ${ITEM.LINES.ITEM_NUMBER.VALUE} IS NOT NULL

Select actions tab

Insert Seq no

Type = property

Description = Declaring global variable

Language = All

Enabled = Yes

Object type = Global variable

Target object = Onhand

Property name = Value

Value = =CUSTONHANDQUANTY(:LINES.ITEM_ID,:GLOBAL.ORG_ID)

Here we selects object type to Global variable which declares a global variable which can be use further.

Target object is the name of new global variable and in value clause we calls the function which we created in first step on database level and passes the values for item id and organization id respectively. The quantity returned by this function will be stored in variable. Hence we can use this variable in further steps to display onhand quantity.

Now insert another record in next line to show the message displaying onhand quantity.

Select actions tab

Insert seq no

Type = Message

Description = Displaying message

Language = All

Enabled = Yes

Message type = Show

Message text = ${Global.onhand}

Here type message indicates that this is message and message type show means a simple user message. In message text Global.onhand is used to call the global variable which we created in previous step.

Save and exit all the forms.

Recall requisitions form and check whether it is working properly or not.

For any queries contact mzeeshan.shaikh@hotmail.com