1 PACKAGE BODY IEX_OBJECT_FILTERS_PKG AS
2 /* $Header: iextobfb.pls 120.0 2004/01/24 03:22:20 appldev noship $ */
3
4 /* Insert_Row procedure */
5 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
6
7 PROCEDURE Insert_Row(x_rowid IN OUT NOCOPY VARCHAR2
8 ,p_OBJECT_FILTER_ID NUMBER
9 ,p_OBJECT_FILTER_TYPE VARCHAR2
10 ,p_OBJECT_FILTER_NAME VARCHAR2
11 ,p_OBJECT_ID NUMBER
12 ,p_SELECT_COLUMN VARCHAR2
13 ,p_ENTITY_NAME VARCHAR2
14 ,p_ACTIVE_FLAG VARCHAR2
15 ,p_OBJECT_VERSION_NUMBER NUMBER
16 ,p_PROGRAM_ID NUMBER DEFAULT NULL
17 ,p_REQUEST_ID NUMBER DEFAULT NULL
18 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
19 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
20 ,p_CREATED_BY NUMBER
21 ,p_CREATION_DATE DATE
22 ,p_LAST_UPDATED_BY NUMBER
23 ,p_LAST_UPDATE_DATE DATE
24 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
25 ) IS
26 CURSOR C IS SELECT ROWID FROM IEX_OBJECT_FILTERS
27 WHERE OBJECT_FILTER_ID = p_OBJECT_FILTER_ID;
28
29 BEGIN
30 INSERT INTO IEX_OBJECT_FILTERS
31 (
32 OBJECT_FILTER_ID
33 ,OBJECT_FILTER_TYPE
34 ,OBJECT_FILTER_NAME
35 ,OBJECT_ID
36 ,SELECT_COLUMN
37 ,ENTITY_NAME
38 ,ACTIVE_FLAG
39 ,OBJECT_VERSION_NUMBER
40 ,PROGRAM_ID
41 ,REQUEST_ID
42 ,PROGRAM_APPLICATION_ID
43 ,PROGRAM_UPDATE_DATE
44 ,CREATED_BY
45 ,CREATION_DATE
46 ,LAST_UPDATED_BY
47 ,LAST_UPDATE_DATE
48 ,LAST_UPDATE_LOGIN
49 ) VALUES (
50 p_OBJECT_FILTER_ID
51 ,p_OBJECT_FILTER_TYPE
52 ,p_OBJECT_FILTER_NAME
53 ,p_OBJECT_ID
54 ,p_SELECT_COLUMN
55 ,p_ENTITY_NAME
56 ,p_ACTIVE_FLAG
57 ,p_OBJECT_VERSION_NUMBER
58 ,p_PROGRAM_ID
59 ,p_REQUEST_ID
60 ,p_PROGRAM_APPLICATION_ID
61 ,p_PROGRAM_UPDATE_DATE
62 ,p_CREATED_BY
63 ,p_CREATION_DATE
64 ,p_LAST_UPDATED_BY
65 ,p_LAST_UPDATE_DATE
66 ,p_LAST_UPDATE_LOGIN
67 );
68
69 OPEN C;
70 FETCH C INTO x_rowid;
71 IF (C%NOTFOUND) THEN
72 CLOSE C;
73 RAISE NO_DATA_FOUND;
74 END IF;
75 CLOSE C;
76 END Insert_Row;
77
78 /* Update_Row procedure */
79 PROCEDURE Update_Row(x_rowid VARCHAR2
80 ,p_OBJECT_FILTER_ID NUMBER
81 ,p_OBJECT_FILTER_TYPE VARCHAR2
82 ,p_OBJECT_FILTER_NAME VARCHAR2
83 ,p_OBJECT_ID NUMBER
84 ,p_SELECT_COLUMN VARCHAR2
85 ,p_ENTITY_NAME VARCHAR2
86 ,p_ACTIVE_FLAG VARCHAR2
87 ,p_OBJECT_VERSION_NUMBER NUMBER
88 ,p_PROGRAM_ID NUMBER DEFAULT NULL
89 ,p_REQUEST_ID NUMBER DEFAULT NULL
90 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
91 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
92 ,p_CREATED_BY NUMBER
93 ,p_CREATION_DATE DATE
94 ,p_LAST_UPDATED_BY NUMBER
95 ,p_LAST_UPDATE_DATE DATE
96 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
97 ) IS
98 BEGIN
99 UPDATE IEX_OBJECT_FILTERS SET
100 OBJECT_FILTER_ID = p_OBJECT_FILTER_ID
101 ,OBJECT_FILTER_TYPE = p_OBJECT_FILTER_TYPE
102 ,OBJECT_FILTER_NAME = p_OBJECT_FILTER_NAME
103 ,OBJECT_ID = p_OBJECT_ID
104 ,SELECT_COLUMN = p_SELECT_COLUMN
105 ,ENTITY_NAME = p_ENTITY_NAME
106 ,ACTIVE_FLAG = p_ACTIVE_FLAG
107 ,OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER
108 ,PROGRAM_ID = p_PROGRAM_ID
109 ,REQUEST_ID = p_REQUEST_ID
110 ,PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID
111 ,PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE
112 ,CREATED_BY = p_CREATED_BY
113 ,CREATION_DATE = p_CREATION_DATE
114 ,LAST_UPDATED_BY = p_LAST_UPDATED_BY
115 ,LAST_UPDATE_DATE = p_LAST_UPDATE_DATE
116 ,LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN
117 WHERE rowid = x_rowid;
118
119 if (sql%notfound) then
120 raise no_data_found;
121 end if;
122 END Update_Row;
123
124 /* Delete_Row procedure */
125 PROCEDURE Delete_Row(x_rowid VARCHAR2) IS
126 BEGIN
127 DELETE FROM IEX_OBJECT_FILTERS
128 WHERE rowid = x_rowid;
129
130 if (sql%notfound) then
131 raise no_data_found;
132 end if;
133 END Delete_Row;
134
135 /* Lock_Row procedure */
136 PROCEDURE Lock_Row(x_rowid VARCHAR2
137 ,p_OBJECT_FILTER_ID NUMBER
138 ,p_OBJECT_FILTER_TYPE VARCHAR2
139 ,p_OBJECT_FILTER_NAME VARCHAR2
140 ,p_OBJECT_ID NUMBER
141 ,p_SELECT_COLUMN VARCHAR2
142 ,p_ENTITY_NAME VARCHAR2
143 ,p_ACTIVE_FLAG VARCHAR2
144 ,p_OBJECT_VERSION_NUMBER NUMBER
145 ,p_PROGRAM_ID NUMBER DEFAULT NULL
146 ,p_REQUEST_ID NUMBER DEFAULT NULL
147 ,p_PROGRAM_APPLICATION_ID NUMBER DEFAULT NULL
148 ,p_PROGRAM_UPDATE_DATE DATE DEFAULT NULL
149 ,p_CREATED_BY NUMBER
150 ,p_CREATION_DATE DATE
151 ,p_LAST_UPDATED_BY NUMBER
152 ,p_LAST_UPDATE_DATE DATE
153 ,p_LAST_UPDATE_LOGIN NUMBER DEFAULT NULL
154 ) IS
155 CURSOR C IS SELECT * FROM IEX_OBJECT_FILTERS
156 WHERE rowid = x_rowid
157 FOR UPDATE of OBJECT_FILTER_ID NOWAIT;
158 Recinfo C%ROWTYPE;
159 BEGIN
160 OPEN C;
161 FETCH C INTO Recinfo;
162 IF (C%NOTFOUND) THEN
163 CLOSE C;
164 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
165 APP_EXCEPTION.Raise_Exception;
166 END IF;
167 CLOSE C;
168
169 IF (
170 (Recinfo.OBJECT_FILTER_ID = p_OBJECT_FILTER_ID)
171 AND ( (Recinfo.OBJECT_FILTER_TYPE = p_OBJECT_FILTER_TYPE)
172 OR ( (Recinfo.OBJECT_FILTER_TYPE IS NULL)
173 AND (p_OBJECT_FILTER_TYPE IS NULL)))
174 AND ( (Recinfo.OBJECT_FILTER_NAME = p_OBJECT_FILTER_NAME)
175 OR ( (Recinfo.OBJECT_FILTER_NAME IS NULL)
176 AND (p_OBJECT_FILTER_NAME IS NULL)))
177 AND ( (Recinfo.OBJECT_ID = p_OBJECT_ID)
178 OR ( (Recinfo.OBJECT_ID IS NULL)
179 AND (p_OBJECT_ID IS NULL)))
180 AND ( (Recinfo.SELECT_COLUMN = p_SELECT_COLUMN)
181 OR ( (Recinfo.SELECT_COLUMN IS NULL)
182 AND (p_SELECT_COLUMN IS NULL)))
183 AND ( (Recinfo.ENTITY_NAME = p_ENTITY_NAME)
184 OR ( (Recinfo.ENTITY_NAME IS NULL)
185 AND (p_ENTITY_NAME IS NULL)))
186 AND ( (Recinfo.ACTIVE_FLAG = p_ACTIVE_FLAG)
187 OR ( (Recinfo.ACTIVE_FLAG IS NULL)
188 AND (p_ACTIVE_FLAG IS NULL)))
189 AND ( (Recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
190 OR ( (Recinfo.OBJECT_VERSION_NUMBER IS NULL)
191 AND (p_OBJECT_VERSION_NUMBER IS NULL)))
192 AND ( (Recinfo.PROGRAM_ID = p_PROGRAM_ID)
193 OR ( (Recinfo.PROGRAM_ID IS NULL)
194 AND (p_PROGRAM_ID IS NULL)))
195 AND ( (Recinfo.REQUEST_ID = p_REQUEST_ID)
196 OR ( (Recinfo.REQUEST_ID IS NULL)
197 AND (p_REQUEST_ID IS NULL)))
198 AND ( (Recinfo.PROGRAM_APPLICATION_ID = p_PROGRAM_APPLICATION_ID)
199 OR ( (Recinfo.PROGRAM_APPLICATION_ID IS NULL)
200 AND (p_PROGRAM_APPLICATION_ID IS NULL)))
201 AND ( (Recinfo.PROGRAM_UPDATE_DATE = p_PROGRAM_UPDATE_DATE)
202 OR ( (Recinfo.PROGRAM_UPDATE_DATE IS NULL)
203 AND (p_PROGRAM_UPDATE_DATE IS NULL)))
204 AND ( (Recinfo.CREATED_BY = p_CREATED_BY)
205 OR ( (Recinfo.CREATED_BY IS NULL)
206 AND (p_CREATED_BY IS NULL)))
207 AND ( (Recinfo.CREATION_DATE = p_CREATION_DATE)
208 OR ( (Recinfo.CREATION_DATE IS NULL)
209 AND (p_CREATION_DATE IS NULL)))
210 AND ( (Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY)
211 OR ( (Recinfo.LAST_UPDATED_BY IS NULL)
212 AND (p_LAST_UPDATED_BY IS NULL)))
213 AND ( (Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE)
214 OR ( (Recinfo.LAST_UPDATE_DATE IS NULL)
215 AND (p_LAST_UPDATE_DATE IS NULL)))
216 AND ( (Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN)
217 OR ( (Recinfo.LAST_UPDATE_LOGIN IS NULL)
218 AND (p_LAST_UPDATE_LOGIN IS NULL)))
219 ) THEN
220 return;
221 ELSE
222 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
223 APP_EXCEPTION.Raise_Exception;
224 END IF;
225 END Lock_Row;
226 END;
227