Home » Applications » Oracle Fusion Apps & E-Business Suite » Want to change the query of "Ordered item" on sales order form, But KFF is attached to tha (Oracle apps 11.5.10)
Want to change the query of "Ordered item" on sales order form, But KFF is attached to tha [message #405583] Thu, 28 May 2009 07:58 Go to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Hello,

I want to change the query of ordered item field on sales order form in Line items tab.

I found that this field is using KFF as its LOV. KFF is "System Items" in Inventory application.

But when I see the value set attached to the segment it is NONE type.

My question is how can a NONE type of value set contain values?

See attached file.
Re: Want to change the query of "Ordered item" on sales order form, But KFF is attached to [message #405597 is a reply to message #405583] Thu, 28 May 2009 09:17 Go to previous messageGo to next message
B.T.S.Ananth
Messages: 216
Registered: June 2006
Location: chennai / Hyderabad
Senior Member

Hi Parag,

Item Setup is the main setup in oracle apps.
generaly Validation Type None is selected to enter values at run time.

Items are appearing in front end because items are defined/ created in Item master
Nav: Inventory > Item > Item Master

1. value set can be any format like you can fix the charecter lenth, case, etc. but this will restrict only the values in that segment. for more information you can refer flexfield user guide

2. There are many types in Validation types like None, Dependent, independent, table, etc. when you are using other than None then have to load the data seperately in values then only it will display in the front end. but when you select None any time you can to to item master and create new item.

i hope i have cleared your doubts.








[Updated on: Thu, 28 May 2009 09:20]

Report message to a moderator

Re: Want to change the query of "Ordered item" on sales order form, But KFF is attached to [message #405615 is a reply to message #405597] Thu, 28 May 2009 12:11 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Thanks for the reply.

I agree with you.
But do you want to say that its impossible to change the query attached to this ordered item LOV?

Currently this value set was using one custom value set of NONE type. I change it to TABLE type to solve my purpose a below.

My aim is to show user only those items which satisfy following query criteria.

select msi.SEGMENT1 
from mtl_item_categories mic, mtl_categories_b mc, mtl_system_items_b msi
where mic.CATEGORY_ID = mc.CATEGORY_ID
and msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
and msi.ORGANIZATION_ID = mic.ORGANIZATION_ID 
and ( nvl(mc.SEGMENT20,mc.segment19) = :ORDER.SOLD_TO or nvl(mc.segment19,'-999') = :ORDER.SOLD_TO)
AND MIC.CATEGORY_SET_ID = 1100000199 
anD MC.STRUCTURE_ID = 50397




But in this query, I want to show items depending on Customer name, thats why i use :ORDER.SOLD_TO.

But during compilation of flexfield it gives warning message as
"The segment "SYSTEM ITEMS" uses value set "LION_UNIFORM_CODE_SYSTEM_ITEMS" which has BLOCK.FIELD reference "ORDER.SOLD_TO" in its Where clause or Additional Columns.

BLOCK.FIELD references are only supported in Oracle Forms."

I have hardcoded the values also which will return one record, so as to just test whether this table type of valu set is working or not.

But no use. it shows all the records which it was showing previously.

I tried with custom.pl, but since LOV name was wrong I couldnt move further.

So finally, is it possible to change the query of the LOV which satisfy above query criteria?

Also,
Look at Oracle's comment about this functionality:

"Key flexfield "System Items" segment has a value set attached to it which references ":system.current_form" to restrict ITEMS that appear in Purchasing forms LOV's."

[Updated on: Fri, 29 May 2009 00:32]

Report message to a moderator

Re: Want to change the query of "Ordered item" on sales order form, But KFF is attached to [message #405798 is a reply to message #405615] Fri, 29 May 2009 09:10 Go to previous messageGo to next message
B.T.S.Ananth
Messages: 216
Registered: June 2006
Location: chennai / Hyderabad
Senior Member

Hi parag,

your requirment is to restrict the items at organization level right?

it is a standard functionality available in oracle create one item master organisation then assign the items to your sold to organization.

this type of functionality you can do it in other way like divide the segment into two segment1 with org and segment2 with item so make segment1 independent and segment2 dependent but this process is very long you have to load the items seperatly into value set.

the query which you have mentiond will not work out because parameter functionality is not possible in KFF with any validation type.

Regards
Re: Want to change the query of "Ordered item" on sales order form, But KFF is attached to [message #405931 is a reply to message #405798] Mon, 01 June 2009 00:16 Go to previous message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Thanks for your reply.

My requirement is:

"Our customer(XYZ) order some items. But these items are ordered by contacts of customers, which are employees of customer XYZ. But both XYZ (Organization) and contact points (Person) are set as customer.

I want contacts points and customer to see only those items which are assigned as a category to them.

To achieve this, i have created one category containing segment19 as customer and segment20 as contacts of customer. I will restrict the items based on category assigned to it. "

Main question is, It is possible to change the query of KFF based items on form...
Previous Topic: cant make payment in AP
Next Topic: Custom.pll -- Raise Form_Trigger_Failure
Goto Forum:
  


Current Time: Fri Dec 09 13:38:25 CST 2016

Total time taken to generate the page: 0.09571 seconds