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