Oracle EBS中,如果获取Item,Organization等基础数据的信息。你可以写SQL来查,这种做法的不好处就是,在不同Procedure或者Function中,你可能需要写多次类似的SQL来取值,造成代码的冗余。EBS标准的做法是,通过Cache模式来时缓存需要的信息。
以获取Organization为例,系统中标准的做法是:
以获取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等各种需要的信息,代码清晰,并减少不必要的冗余。