今天开发一个ebs的form配料计划界面,其中需要使用lov来查询出物料的编码、物料id、物料类型、物料的主要单位,然会本人就编辑了如下的sql语句:
select MC.CONCATENATED_SEGMENTS item_type,
MIC.INVENTORY_ITEM_ID item_id,
T.SEGMENT1 item_num,
T.DESCRIPTION item_des,
B.PRIMARY_UOM_CODE major_unit
from MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B_KFV MC,
MTL_CATEGORY_SETS_TL MCST,
MTL_SYSTEM_ITEMS T,
MTL_SYSTEM_ITEMS_B B
where MIC.CATEGORY_ID = MC.CATEGORY_ID
AND mic.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND T.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
and T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
and mic.organization_id = b.organization_id
and t.organization_id = mic.organization_id
and mcst.category_set_name = 'Inventory'
AND MCST.LANGUAGE = userenv('LANG')
and t.organization_id = :parameter.org_id;
MIC.INVENTORY_ITEM_ID item_id,
T.SEGMENT1 item_num,
T.DESCRIPTION item_des,
B.PRIMARY_UOM_CODE major_unit
from MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B_KFV MC,
MTL_CATEGORY_SETS_TL MCST,
MTL_SYSTEM_ITEMS T,
MTL_SYSTEM_ITEMS_B B
where MIC.CATEGORY_ID = MC.CATEGORY_ID
AND mic.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND T.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
and T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
and mic.organization_id = b.organization_id
and t.organization_id = mic.organization_id
and mcst.category_set_name = 'Inventory'
AND MCST.LANGUAGE = userenv('LANG')
and t.organization_id = :parameter.org_id;
果不其然,达到我想要的预期的效果,速度方面的表现也是让人感到欣慰。但经过本人的思考,发现,物料的信息是随着时间来增长的,那么如果信息多的话,尽管查询语句很好,但是最后也会影响到系统的运行速度。因此,问题的解决不是最佳的方案。
然后,我就想起来可以使用lov的显示前过滤的功能来拟补这样的不足,打开自己的form然后在相应的lov属性上增加了过滤,但是问题就出现了。发布使用的时候总是会出现,未明确定义列。然后无法进行查询,这个问题困扰我很旧,然后就上网matlink等上寻求答案。经过网上的内容加上自己的摸索,最终发现,是这样的问题。这个过滤就是在最后加上where item_type like '%%' 这样的语句,如果有别名的存在,或者是出现t.item_id 这样的查询的话,那么就会出现上面的错误,默认的情况下是按照别名或者item_id这样进行约束的添加。找到问题的所在,答案就好解决了。修改sql语句。修改成如下的形式:
select item_num, item_id, item_type, item_des, major_unit
from (select MC.CONCATENATED_SEGMENTS item_type,
MIC.INVENTORY_ITEM_ID item_id,
T.SEGMENT1 item_num,
T.DESCRIPTION item_des,
B.PRIMARY_UOM_CODE major_unit
from MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B_KFV MC,
MTL_CATEGORY_SETS_TL MCST,
MTL_SYSTEM_ITEMS T,
MTL_SYSTEM_ITEMS_B B
where MIC.CATEGORY_ID = MC.CATEGORY_ID
AND mic.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND T.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
and T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
and mic.organization_id = b.organization_id
and t.organization_id = mic.organization_id
and mcst.category_set_name = 'Inventory'
AND MCST.LANGUAGE = userenv('LANG')
and t.organization_id = :parameter.org_id;
);
from (select MC.CONCATENATED_SEGMENTS item_type,
MIC.INVENTORY_ITEM_ID item_id,
T.SEGMENT1 item_num,
T.DESCRIPTION item_des,
B.PRIMARY_UOM_CODE major_unit
from MTL_ITEM_CATEGORIES MIC,
MTL_CATEGORIES_B_KFV MC,
MTL_CATEGORY_SETS_TL MCST,
MTL_SYSTEM_ITEMS T,
MTL_SYSTEM_ITEMS_B B
where MIC.CATEGORY_ID = MC.CATEGORY_ID
AND mic.CATEGORY_SET_ID = MCST.CATEGORY_SET_ID
AND T.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
and T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
and mic.organization_id = b.organization_id
and t.organization_id = mic.organization_id
and mcst.category_set_name = 'Inventory'
AND MCST.LANGUAGE = userenv('LANG')
and t.organization_id = :parameter.org_id;
);
运行,果然效果良好。
很喜欢这样的感觉。。。。。。。。。。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26663173/viewspace-715619/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26663173/viewspace-715619/