PACKAGE BODY: APPS.POS_ORG_HASH_PKG

Source

1 PACKAGE BODY pos_org_hash_pkg AS
2 /* $Header: POSORGHB.pls 115.1 2002/11/20 22:08:26 jazhang noship $ */
3 
4 FUNCTION get_hashkey(p_org_id IN NUMBER) RETURN VARCHAR2
5   IS
6      PRAGMA autonomous_transaction;
7 
8      l_hashkey pos_org_hash.hashkey%TYPE;
9 
10      CURSOR l_org_id_cur IS
11 	SELECT 1
12 	  FROM hr_operating_units
13 	  WHERE organization_id = p_org_id;
14 
15      CURSOR l_hashkey_cur IS
16      SELECT hashkey
17        FROM pos_org_hash
18        WHERE org_id = p_org_id;
19 
20      l_temp_number NUMBER;
21 BEGIN
22    -- validate p_org_id
23    OPEN l_org_id_cur;
24    FETCH l_org_id_cur INTO l_temp_number;
25    IF l_org_id_cur%notfound THEN
26       CLOSE l_org_id_cur;
27       raise_application_error(-20001, 'invalid operating unit id ' || p_org_id, TRUE);
28    END IF;
29    CLOSE l_org_id_cur;
30 
31    -- query from pos_org_hash table
32    OPEN l_hashkey_cur;
33    FETCH l_hashkey_cur INTO l_hashkey;
34    IF l_hashkey_cur%notfound THEN
35       -- create a new row when not found
36       l_hashkey := icx_call.encrypt3(p_org_id);
37       INSERT INTO pos_org_hash(org_id, hashkey) VALUES (p_org_id, l_hashkey);
38       COMMIT;
39    END IF;
40    CLOSE l_hashkey_cur;
41 
42    RETURN l_hashkey;
43 
44 END get_hashkey;
45 
46 FUNCTION get_org_id_by_key(p_hashkey IN VARCHAR2) RETURN NUMBER
47 IS
48 
49 l_org_id pos_org_hash.org_id%TYPE := NULL;
50 
51 CURSOR l_org_id_cur IS
52   SELECT org_id
53   FROM   pos_org_hash
54   WHERE  hashkey = p_hashkey;
55 
56 BEGIN
57 
58   IF p_hashkey IS NULL THEN
59     RAISE NO_DATA_FOUND;
60   END IF;
61 
62   OPEN l_org_id_cur;
63   FETCH l_org_id_cur INTO l_org_id;
64   IF l_org_id_cur%NOTFOUND THEN
65     CLOSE l_org_id_cur;
66     RAISE NO_DATA_FOUND;
67   END IF;
68   CLOSE l_org_id_cur;
69 
70   RETURN l_org_id;
71 
72 END get_org_id_by_key;
73 
74 END pos_org_hash_pkg;