转自：http://blog.csdn.net/pan_tian/article/details/8633238

Oracle EBS中，如果获取Item，Organization等基础数据的信息。你可以写SQL来查，这种做法的不好处就是，在不同Procedure或者Function中，你可能需要写多次类似的SQL来取值，造成代码的冗余。EBS标准的做法是，通过Cache模式来时缓存需要的信息。

以获取Organization为例，系统中标准的做法是:

[sql] view plain copy print ?

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;

  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的定义

[sql] view plain copy print ?

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;

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等各种需要的信息，代码清晰，并减少不必要的冗余。

ITPub博客

[转]Cache Pattern

最新文章