[Home] [Help]
PACKAGE BODY: APPS.JTF_SOURCES_PKG
Source
1 PACKAGE BODY JTF_SOURCES_PKG AS
2 /* $Header: jtfvsrcb.pls 120.5 2006/03/28 18:21:01 achanda ship $ */
3 --
4 -- arpatel 06/25/01 - Added related_id columns (1-5) to insert/update/lock procedures
5 --
6 -- sp 07/12/02 - Modified the access from JTF_SOURCES to JTF_SOURCES_ALL
7 -- as the view definition of JTF_SOURCES has changeds
8 --
9
10 PROCEDURE Insert_Row(
11 x_Rowid IN OUT NOCOPY VARCHAR2,
12 x_SOURCE_ID IN OUT NOCOPY NUMBER,
13 x_LAST_UPDATE_DATE IN DATE,
14 x_LAST_UPDATED_BY IN NUMBER,
15 x_CREATION_DATE IN DATE,
16 x_CREATED_BY IN NUMBER,
17 x_LAST_UPDATE_LOGIN IN NUMBER,
18 x_LOOKUP_CODE IN VARCHAR2,
19 x_LOOKUP_TYPE IN VARCHAR2,
20 x_MEANING IN VARCHAR2,
21 x_ENABLED_FLAG IN VARCHAR2,
22 x_DESCRIPTION IN VARCHAR2,
23 x_START_DATE_ACTIVE IN DATE,
24 x_END_DATE_ACTIVE IN DATE,
25 x_RELATED_ID1 IN NUMBER,
26 x_RELATED_ID2 IN NUMBER,
27 x_RELATED_ID3 IN NUMBER,
28 x_RELATED_ID4 IN NUMBER,
29 x_RELATED_ID5 IN NUMBER,
30 X_RSC_COL_NAME IN VARCHAR2,
31 X_ROLE_COL_NAME IN VARCHAR2,
32 X_GROUP_COL_NAME IN VARCHAR2,
33 X_RSC_LOV_SQL IN VARCHAR2,
34 X_RSC_ACCESS_LKUP IN VARCHAR2,
35 X_DENORM_VALUE_TABLE_NAME IN VARCHAR2,
36 X_DENORM_DEA_VALUE_TABLE_NAME IN VARCHAR2,
37 x_ORG_ID IN NUMBER
38 ) IS
39 CURSOR C IS SELECT rowid FROM JTF_SOURCES_ALL
40 WHERE SOURCE_ID = x_SOURCE_ID;
41 CURSOR C2 IS SELECT JTF_SOURCES_s.nextval FROM sys.dual;
42 BEGIN
43 If (x_SOURCE_ID IS NULL) then
44 OPEN C2;
45 FETCH C2 INTO x_SOURCE_ID;
46 CLOSE C2;
47 End If;
48 INSERT INTO JTF_SOURCES_ALL(
49 SOURCE_ID,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_LOGIN,
55 LOOKUP_CODE,
56 LOOKUP_TYPE,
57 MEANING,
58 ENABLED_FLAG,
59 DESCRIPTION,
60 START_DATE_ACTIVE,
61 END_DATE_ACTIVE,
62 RELATED_ID1,
63 RELATED_ID2,
64 RELATED_ID3,
65 RELATED_ID4,
66 RELATED_ID5,
67 RSC_COL_NAME,
68 ROLE_COL_NAME,
69 GROUP_COL_NAME,
70 RSC_LOV_SQL,
71 RSC_ACCESS_LKUP,
72 DENORM_VALUE_TABLE_NAME,
73 DENORM_DEA_VALUE_TABLE_NAME,
74 ORG_ID
75 ) VALUES (
76 x_SOURCE_ID,
77 decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_LAST_UPDATE_DATE),
78 decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATED_BY),
79 decode( x_CREATION_DATE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_CREATION_DATE),
80 decode( x_CREATED_BY, FND_API.G_MISS_NUM, NULL,x_CREATED_BY),
81 decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM, NULL,x_LAST_UPDATE_LOGIN),
82 decode( x_LOOKUP_CODE, FND_API.G_MISS_CHAR, NULL,x_LOOKUP_CODE),
83 decode( x_LOOKUP_TYPE, FND_API.G_MISS_CHAR, NULL,x_LOOKUP_TYPE),
84 decode( x_MEANING, FND_API.G_MISS_CHAR, NULL,x_MEANING),
85 decode( x_ENABLED_FLAG, FND_API.G_MISS_CHAR, NULL,x_ENABLED_FLAG),
86 decode( x_DESCRIPTION, FND_API.G_MISS_CHAR, NULL,x_DESCRIPTION),
87 decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_START_DATE_ACTIVE),
88 decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE, NULL,x_END_DATE_ACTIVE),
89 decode( x_RELATED_ID1, FND_API.G_MISS_NUM, NULL,x_RELATED_ID1),
90 decode( x_RELATED_ID2, FND_API.G_MISS_NUM, NULL,x_RELATED_ID2),
91 decode( x_RELATED_ID3, FND_API.G_MISS_NUM, NULL,x_RELATED_ID3),
92 decode( x_RELATED_ID4, FND_API.G_MISS_NUM, NULL,x_RELATED_ID4),
93 decode( x_RELATED_ID5, FND_API.G_MISS_NUM, NULL,x_RELATED_ID5),
94 decode( X_RSC_COL_NAME, FND_API.G_MISS_CHAR, NULL,X_RSC_COL_NAME),
95 decode( X_ROLE_COL_NAME, FND_API.G_MISS_CHAR, NULL,X_ROLE_COL_NAME),
96 decode( X_GROUP_COL_NAME, FND_API.G_MISS_CHAR, NULL,X_GROUP_COL_NAME),
97 decode( X_RSC_LOV_SQL, FND_API.G_MISS_CHAR, NULL,X_RSC_LOV_SQL),
98 decode( X_RSC_ACCESS_LKUP, FND_API.G_MISS_CHAR, NULL,X_RSC_ACCESS_LKUP),
99 decode( X_DENORM_VALUE_TABLE_NAME, FND_API.G_MISS_CHAR, NULL,X_DENORM_VALUE_TABLE_NAME),
100 decode( X_DENORM_DEA_VALUE_TABLE_NAME, FND_API.G_MISS_CHAR, NULL,X_DENORM_DEA_VALUE_TABLE_NAME),
101 decode( x_ORG_ID, FND_API.G_MISS_NUM, NULL,x_ORG_ID) );
102 OPEN C;
103 FETCH C INTO x_Rowid;
104 If (C%NOTFOUND) then
105 CLOSE C;
106 RAISE NO_DATA_FOUND;
107 End If;
108 End Insert_Row;
109
110
111
112 PROCEDURE Delete_Row( x_SOURCE_ID IN NUMBER
113 ) IS
114 BEGIN
115 DELETE FROM JTF_SOURCES_ALL
116 WHERE SOURCE_ID = x_SOURCE_ID;
117 If (SQL%NOTFOUND) then
118 RAISE NO_DATA_FOUND;
119 End If;
120 END Delete_Row;
121
122
123
124 PROCEDURE Update_Row(
125 x_Rowid IN VARCHAR2,
126 x_SOURCE_ID IN NUMBER,
127 x_LAST_UPDATE_DATE IN DATE,
128 x_LAST_UPDATED_BY IN NUMBER,
129 x_CREATION_DATE IN DATE,
130 x_CREATED_BY IN NUMBER,
131 x_LAST_UPDATE_LOGIN IN NUMBER,
132 x_LOOKUP_CODE IN VARCHAR2,
133 x_LOOKUP_TYPE IN VARCHAR2,
134 x_MEANING IN VARCHAR2,
135 x_ENABLED_FLAG IN VARCHAR2,
136 x_DESCRIPTION IN VARCHAR2,
137 x_START_DATE_ACTIVE IN DATE,
138 x_END_DATE_ACTIVE IN DATE,
139 x_RELATED_ID1 IN NUMBER,
140 x_RELATED_ID2 IN NUMBER,
141 x_RELATED_ID3 IN NUMBER,
142 x_RELATED_ID4 IN NUMBER,
143 x_RELATED_ID5 IN NUMBER,
144 x_ORG_ID IN NUMBER
145 ) IS
146 BEGIN
147 Update JTF_SOURCES_ALL
148 SET
149 SOURCE_ID = decode( x_SOURCE_ID, FND_API.G_MISS_NUM,SOURCE_ID,x_SOURCE_ID),
150 LAST_UPDATE_DATE = decode( x_LAST_UPDATE_DATE, FND_API.G_MISS_DATE,LAST_UPDATE_DATE,x_LAST_UPDATE_DATE),
151 LAST_UPDATED_BY = decode( x_LAST_UPDATED_BY, FND_API.G_MISS_NUM,LAST_UPDATED_BY,x_LAST_UPDATED_BY),
152 CREATION_DATE = decode( x_CREATION_DATE, FND_API.G_MISS_DATE,CREATION_DATE,x_CREATION_DATE),
153 CREATED_BY = decode( x_CREATED_BY, FND_API.G_MISS_NUM,CREATED_BY,x_CREATED_BY),
154 LAST_UPDATE_LOGIN = decode( x_LAST_UPDATE_LOGIN, FND_API.G_MISS_NUM,LAST_UPDATE_LOGIN,x_LAST_UPDATE_LOGIN),
155 LOOKUP_CODE = decode( x_LOOKUP_CODE, FND_API.G_MISS_CHAR,LOOKUP_CODE,x_LOOKUP_CODE),
156 LOOKUP_TYPE = decode( x_LOOKUP_TYPE, FND_API.G_MISS_CHAR,LOOKUP_TYPE,x_LOOKUP_TYPE),
157 MEANING = decode( x_MEANING, FND_API.G_MISS_CHAR,MEANING,x_MEANING),
158 ENABLED_FLAG = decode( x_ENABLED_FLAG, FND_API.G_MISS_CHAR,ENABLED_FLAG,x_ENABLED_FLAG),
159 DESCRIPTION = decode( x_DESCRIPTION, FND_API.G_MISS_CHAR,DESCRIPTION,x_DESCRIPTION),
160 START_DATE_ACTIVE = decode( x_START_DATE_ACTIVE, FND_API.G_MISS_DATE,START_DATE_ACTIVE,x_START_DATE_ACTIVE),
161 END_DATE_ACTIVE = decode( x_END_DATE_ACTIVE, FND_API.G_MISS_DATE,END_DATE_ACTIVE,x_END_DATE_ACTIVE),
162 RELATED_ID1 = decode( x_RELATED_ID1, FND_API.G_MISS_NUM, RELATED_ID1,x_RELATED_ID1),
163 RELATED_ID2 = decode( x_RELATED_ID2, FND_API.G_MISS_NUM, RELATED_ID2,x_RELATED_ID2),
164 RELATED_ID3 = decode( x_RELATED_ID3, FND_API.G_MISS_NUM, RELATED_ID3,x_RELATED_ID3),
165 RELATED_ID4 = decode( x_RELATED_ID4, FND_API.G_MISS_NUM, RELATED_ID4,x_RELATED_ID4),
166 RELATED_ID5 = decode( x_RELATED_ID5, FND_API.G_MISS_NUM, RELATED_ID5,x_RELATED_ID5),
167 ORG_ID = decode( x_ORG_ID, FND_API.G_MISS_NUM,ORG_ID,x_ORG_ID)
168 where SOURCE_ID = X_SOURCE_ID and
169 ( ORG_ID = x_ORG_ID OR ( ORG_ID IS NULL AND X_ORG_ID IS NULL)) ;
170
171 If (SQL%NOTFOUND) then
172 RAISE NO_DATA_FOUND;
173 End If;
174 END Update_Row;
175
176
177
178 PROCEDURE Lock_Row(
179 x_Rowid IN VARCHAR2,
180 x_SOURCE_ID IN NUMBER,
181 x_LAST_UPDATE_DATE IN DATE,
182 x_LAST_UPDATED_BY IN NUMBER,
183 x_CREATION_DATE IN DATE,
184 x_CREATED_BY IN NUMBER,
185 x_LAST_UPDATE_LOGIN IN NUMBER,
186 x_LOOKUP_CODE IN VARCHAR2,
187 x_LOOKUP_TYPE IN VARCHAR2,
188 x_MEANING IN VARCHAR2,
189 x_ENABLED_FLAG IN VARCHAR2,
190 x_DESCRIPTION IN VARCHAR2,
191 x_START_DATE_ACTIVE IN DATE,
192 x_END_DATE_ACTIVE IN DATE,
193 x_RELATED_ID1 IN NUMBER,
194 x_RELATED_ID2 IN NUMBER,
195 x_RELATED_ID3 IN NUMBER,
196 x_RELATED_ID4 IN NUMBER,
197 x_RELATED_ID5 IN NUMBER,
198 x_ORG_ID IN NUMBER
199 ) IS
200 CURSOR C IS
201 SELECT *
202 FROM JTF_SOURCES_ALL
203 WHERE rowid = x_Rowid
204 FOR UPDATE of SOURCE_ID NOWAIT;
205 Recinfo C%ROWTYPE;
206 BEGIN
207 OPEN C;
208 FETCH C INTO Recinfo;
209 If (C%NOTFOUND) then
210 CLOSE C;
211 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
212 APP_EXCEPTION.RAISE_EXCEPTION;
213 End If;
214 CLOSE C;
215 if (
216 ( ( Recinfo.SOURCE_ID = x_SOURCE_ID)
217 OR ( ( Recinfo.SOURCE_ID is NULL )
218 AND ( x_SOURCE_ID is NULL )))
219 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
220 OR ( ( Recinfo.LAST_UPDATE_DATE is NULL )
221 AND ( x_LAST_UPDATE_DATE is NULL )))
222 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
223 OR ( ( Recinfo.LAST_UPDATED_BY is NULL )
224 AND ( x_LAST_UPDATED_BY is NULL )))
225 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
226 OR ( ( Recinfo.CREATION_DATE is NULL )
227 AND ( x_CREATION_DATE is NULL )))
228 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
229 OR ( ( Recinfo.CREATED_BY is NULL )
230 AND ( x_CREATED_BY is NULL )))
231 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
232 OR ( ( Recinfo.LAST_UPDATE_LOGIN is NULL )
233 AND ( x_LAST_UPDATE_LOGIN is NULL )))
234 AND ( ( Recinfo.LOOKUP_CODE = x_LOOKUP_CODE)
235 OR ( ( Recinfo.LOOKUP_CODE is NULL )
236 AND ( x_LOOKUP_CODE is NULL )))
237 AND ( ( Recinfo.LOOKUP_TYPE = x_LOOKUP_TYPE)
238 OR ( ( Recinfo.LOOKUP_TYPE is NULL )
239 AND ( x_LOOKUP_TYPE is NULL )))
240 AND ( ( Recinfo.MEANING = x_MEANING)
241 OR ( ( Recinfo.MEANING is NULL )
242 AND ( x_MEANING is NULL )))
243 AND ( ( Recinfo.ENABLED_FLAG = x_ENABLED_FLAG)
244 OR ( ( Recinfo.ENABLED_FLAG is NULL )
245 AND ( x_ENABLED_FLAG is NULL )))
246 AND ( ( Recinfo.DESCRIPTION = x_DESCRIPTION)
247 OR ( ( Recinfo.DESCRIPTION is NULL )
248 AND ( x_DESCRIPTION is NULL )))
249 AND ( ( Recinfo.START_DATE_ACTIVE = x_START_DATE_ACTIVE)
250 OR ( ( Recinfo.START_DATE_ACTIVE is NULL )
251 AND ( x_START_DATE_ACTIVE is NULL )))
252 AND ( ( Recinfo.END_DATE_ACTIVE = x_END_DATE_ACTIVE)
253 OR ( ( Recinfo.END_DATE_ACTIVE is NULL )
254 AND ( x_END_DATE_ACTIVE is NULL )))
255 AND ( ( Recinfo.RELATED_ID1 = x_RELATED_ID1)
256 OR ( ( Recinfo.RELATED_ID1 is NULL )
257 AND ( x_RELATED_ID1 is NULL )))
258 AND ( ( Recinfo.RELATED_ID2 = x_RELATED_ID2)
259 OR ( ( Recinfo.RELATED_ID2 is NULL )
260 AND ( x_RELATED_ID2 is NULL )))
261 AND ( ( Recinfo.RELATED_ID3 = x_RELATED_ID3)
262 OR ( ( Recinfo.RELATED_ID3 is NULL )
263 AND ( x_RELATED_ID3 is NULL )))
264 AND ( ( Recinfo.RELATED_ID4 = x_RELATED_ID4)
265 OR ( ( Recinfo.RELATED_ID4 is NULL )
266 AND ( x_RELATED_ID4 is NULL )))
267 AND ( ( Recinfo.RELATED_ID5 = x_RELATED_ID5)
268 OR ( ( Recinfo.RELATED_ID5 is NULL )
269 AND ( x_RELATED_ID5 is NULL )))
270 AND ( ( Recinfo.ORG_ID = x_ORG_ID)
271 OR ( ( Recinfo.ORG_ID is NULL )
272 AND ( x_ORG_ID is NULL )))
273 ) then
274 return;
275 else
276 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
277 APP_EXCEPTION.RAISE_EXCEPTION;
278 End If;
279 END Lock_Row;
280
281 END JTF_SOURCES_PKG;