PACKAGE BODY: APPS.JTM_LOOKUPS_ACC_PKG

Source

1 PACKAGE BODY JTM_LOOKUPS_ACC_PKG AS
2 /* $Header: jtmvluab.pls 120.1 2005/08/24 02:19:49 saradhak noship $ */
3 -- Start of Comments
4 --
5 -- NAME
6 --   JTM_LOOKUPS_ACC_PKG
7 --
8 -- PURPOSE
9 --   TABLE-LEVEL PACKAGE for JTM_FND_LOOKUPS_ACC.
10 --
11 --   PROCEDURES:
12 --
13 --
14 -- NOTES
15 --
16 --
17 -- HISTORY
18 --   04-09-2002 YOHUANG Created.
19 --
20 -- End of Comments
21 --
22 --
23 --
24 G_PKG_NAME            CONSTANT VARCHAR2(30) := 'JTM_LOOKUPS_ACC_PKG';
25 G_FILE_NAME           CONSTANT VARCHAR2(12) := 'jtmvluab.pls';
26 --
27 --
28 -- ACCESS_ID is generated from SEQUENCE. Later ACCESS_ID will be removed.
29 -- It handles the DUPLICATE_VALUE on INDEX Exception.
30 -- For Application Specific ACC tables, the counter is always 1.
31 PROCEDURE INSERT_ROW (
32    X_LOOKUP_TYPE                     IN VARCHAR2 ,
33    X_VIEW_APPLICATION_ID             IN NUMBER ,
34    X_SECURITY_GROUP_ID               IN NUMBER ,
35    X_APPLICATION_ID                  IN NUMBER ,
36    X_ACCESS_ID                     OUT NOCOPY NUMBER
37 ) IS
38 
39 BEGIN
40 
41     SELECT JTM_FND_LOOKUPS_ACC_S.NEXTVAL
42     INTO X_ACCESS_ID FROM DUAL;
43 
44     INSERT INTO JTM_FND_LOOKUPS_ACC (
45             ACCESS_ID  ,
46             LAST_UPDATE_DATE ,
47             LAST_UPDATED_BY  ,
48             CREATION_DATE    ,
49             CREATED_BY       ,
50             LOOKUP_TYPE      ,
51             VIEW_APPLICATION_ID ,
52             SECURITY_GROUP_ID,
53             APPLICATION_ID   ,
54             COUNTER
55      )
56      VALUES (
57             X_ACCESS_ID ,
58             SYSDATE,
59             1,
60             SYSDATE,
61             1,
62             X_LOOKUP_TYPE,
63             X_VIEW_APPLICATION_ID,
64             X_SECURITY_GROUP_ID,
65             X_APPLICATION_ID,
66             1
67     );
68 
69 EXCEPTION
70     WHEN DUP_VAL_ON_INDEX THEN
71          FND_MESSAGE.set_name('JTM', 'JTM_UNIQUE_INDEX_VIOLATION');
72          FND_MSG_PUB.add;
73          APP_EXCEPTION.raise_exception;
74     WHEN OTHERS THEN
75          RAISE;
76 END  INSERT_ROW;
77 
78 -- For Application Specific ACC table, there won't be any update allowed.
79 PROCEDURE UPDATE_ROW (
80    X_LOOKUP_TYPE                     IN VARCHAR2 ,
81    X_VIEW_APPLICATION_ID             IN NUMBER ,
82    X_SECURITY_GROUP_ID               IN NUMBER ,
83    X_APPLICATION_ID                  IN NUMBER
84 ) IS
85 
86 BEGIN
87     UPDATE JTM_FND_LOOKUPS_ACC
88     SET LAST_UPDATE_DATE = SYSDATE,
89         LAST_UPDATED_BY  = 1,
90         CREATED_BY       = 1,
91         CREATION_DATE    = CREATION_DATE,
92         LOOKUP_TYPE = X_LOOKUP_TYPE ,
93         VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID,
94         SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
95         APPLICATION_ID = X_APPLICATION_ID
96     WHERE LOOKUP_TYPE = X_LOOKUP_TYPE
97     AND   VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
98     AND   SECURITY_GROUP_ID   = X_SECURITY_GROUP_ID
99     AND   APPLICATION_ID      = X_APPLICATION_ID;
100 
101     IF ( SQL%NOTFOUND ) THEN
102         RAISE NO_DATA_FOUND;
103     END IF;
104 
105 END UPDATE_ROW;
106 
107 
108 -- For Deletion, later on we might need to add an "EXPRIATION_DATE" Column to support deletion
109 -- Through FNDLOADER
110 PROCEDURE DELETE_ROW (
111    X_LOOKUP_TYPE                     IN VARCHAR2 ,
112    X_VIEW_APPLICATION_ID             IN NUMBER ,
113    X_SECURITY_GROUP_ID               IN NUMBER ,
114    X_APPLICATION_ID                  IN NUMBER
115 ) IS
116 
117 BEGIN
118     DELETE FROM JTM_FND_LOOKUPS_ACC
119     WHERE LOOKUP_TYPE = X_LOOKUP_TYPE
120     AND   VIEW_APPLICATION_ID = X_VIEW_APPLICATION_ID
121     AND   SECURITY_GROUP_ID = X_SECURITY_GROUP_ID
122     AND   APPLICATION_ID = X_APPLICATION_ID;
123 
124     IF ( SQL%NOTFOUND ) THEN
125         RAISE NO_DATA_FOUND;
126     END IF;
127 
128 END DELETE_ROW;
129 
130 END JTM_LOOKUPS_ACC_PKG;