[Home] [Help]
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;