Oracle EBS中,如果获取Item,Organization等基础数据的信息。你可以写SQL来查,这种做法的不好处就是,在不同Procedure或者Function中,你可能需要写多次类似的SQL来取值,造成代码的冗余。EBS标准的做法是,通过Cache模式来时缓存需要的信息。
以获取Organization为例,系统中标准的做法是:
l_return_value := INV_CACHE.set_org_rec(p_organization_id); IF NOT l_return_value THEN RAISE fnd_api.g_exc_unexpected_error; END IF; l_negative_inv_receipt_code := INV_CACHE.org_rec.negative_inv_receipt_code;
再看INV_CACHE.set_org_rec的定义
CREATE OR REPLACE PACKAGE inv_cache AUTHID CURRENT_USER AS ... org_rec mtl_parameters%ROWTYPE; ... END inv_cache; / CREATE OR REPLACE PACKAGE BODY inv_cache AS ... FUNCTION set_org_rec ( p_organization_id IN NUMBER ) RETURN BOOLEAN IS l_return_val BOOLEAN := FALSE; BEGIN IF org_rec.organization_id = p_organization_id THEN l_return_val := TRUE; ELSE SELECT * INTO org_rec FROM MTL_PARAMETERS WHERE organization_id = p_organization_id; pt_debug('p_organization_id:'||p_organization_id); l_return_val := TRUE; END IF; RETURN l_return_val; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN l_return_val; WHEN OTHERS THEN RETURN l_return_val; END set_org_rec; ... END inv_cache;
你可以用类似Cache Pattern来获取item,sub,move order,transaction type等各种需要的信息,代码清晰,并减少不必要的冗余。