Oracle R12 Personalize LOV in form

Objective:

                Personalize Subinventory LOV in Production Supervisor > Batch Details screen. The condition is, if the item in block MTL_TRX_LINE is having defined Supply Subinventory, then the Subinventory LOV should only have the Supply Subinventory for the item, otherwise all the subinventories should come. It also includes a condition that Line Type should be Ingredient.

Navigation:

Help > Diagnostics > Custom Code > Personalize

Condition:

:GME_MATERIAL_DETAILS.LINE_TYPE = -1 and (select process_supply_subinventory from mtl_system_items_b msi where msi.inventory_item_id = :MTL_TRX_LINE.INVENTORY_ITEM_ID and msi.organization_id = :MTL_TRX_LINE.ORGANIZATION_ID) is not null

Then click on Actions tab.

First we need to create a record group which has to be assigned to the LOV during run time. The record group query should have the same number of columns as given in the Record Group query in form (fmb). 

Actions Type: Builtin

Builtin Type: Create Record Group from Query

Argument:

select msi.process_supply_subinventory secondary_inventory_name,msiv.description, quantity_tracked, asset_inventory, locator_type, material_account from mtl_system_items_b msi,mtl_subinventories_val_v msiv

where msi.inventory_item_id = :MTL_TRX_LINE.INVENTORY_ITEM_ID and msi.organization_id = :MTL_TRX_LINE.ORGANIZATION_ID

and msiv.secondary_inventory_name = msi.process_supply_subinventory

Group Name can be any name which has to be given in the LOV in next step.

The record group which got created to be assigned to the LOV. The LOV name of the field Subinventory can be taken by opening the form (fmb file) in Forms Builder.

The target object name refers to the LOV name.