[Home] [Help]
PACKAGE BODY: APPS.HZ_PERSON_INTEREST_PKG
Source
1 PACKAGE BODY HZ_PERSON_INTEREST_PKG as
2 /* $Header: ARHPINTB.pls 120.8 2005/10/30 03:54:13 appldev ship $ */
3
4
5 PROCEDURE Insert_Row(
6 x_PERSON_INTEREST_ID IN OUT NOCOPY NUMBER,
7 x_LEVEL_OF_INTEREST IN VARCHAR2,
8 x_PARTY_ID IN NUMBER,
9 x_LEVEL_OF_PARTICIPATION IN VARCHAR2,
10 x_INTEREST_TYPE_CODE IN VARCHAR2,
11 x_SPORT_INDICATOR IN VARCHAR2,
12 x_INTEREST_NAME IN VARCHAR2,
13 x_COMMENTS IN VARCHAR2,
14 x_SUB_INTEREST_TYPE_CODE IN VARCHAR2,
15 x_TEAM IN VARCHAR2,
16 x_SINCE IN DATE,
17 x_OBJECT_VERSION_NUMBER IN NUMBER,
18 x_STATUS IN VARCHAR2,
19 x_CREATED_BY_MODULE IN VARCHAR2,
20 x_APPLICATION_ID IN NUMBER) IS
21
22 l_success VARCHAR2(1) := 'N';
23
24 BEGIN
25
26 WHILE l_success = 'N' LOOP
27 BEGIN
28 INSERT INTO HZ_PERSON_INTEREST(
29 PERSON_INTEREST_ID,
30 LEVEL_OF_INTEREST,
31 PARTY_ID,
32 LEVEL_OF_PARTICIPATION,
33 INTEREST_TYPE_CODE,
34 SPORT_INDICATOR,
35 INTEREST_NAME,
36 CREATED_BY,
37 CREATION_DATE,
38 LAST_UPDATE_LOGIN,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 REQUEST_ID,
42 PROGRAM_APPLICATION_ID,
43 PROGRAM_ID,
44 PROGRAM_UPDATE_DATE,
45 COMMENTS,
46 SUB_INTEREST_TYPE_CODE,
47 TEAM,
48 SINCE,
49 STATUS,
50 OBJECT_VERSION_NUMBER,
51 CREATED_BY_MODULE,
52 APPLICATION_ID
53
54 ) VALUES (
55 DECODE( x_PERSON_INTEREST_ID, FND_API.G_MISS_NUM, HZ_PERSON_INTEREST_S.NEXTVAL, NULL, HZ_PERSON_INTEREST_S.NEXTVAL, X_PERSON_INTEREST_ID ),
56 decode( x_LEVEL_OF_INTEREST, FND_API.G_MISS_CHAR, NULL,x_LEVEL_OF_INTEREST),
57 decode( x_PARTY_ID, FND_API.G_MISS_NUM, NULL,x_PARTY_ID),
58 decode( x_LEVEL_OF_PARTICIPATION, FND_API.G_MISS_CHAR, NULL,x_LEVEL_OF_PARTICIPATION),
59 decode( x_INTEREST_TYPE_CODE, FND_API.G_MISS_CHAR, NULL,x_INTEREST_TYPE_CODE),
60 decode( x_SPORT_INDICATOR, FND_API.G_MISS_CHAR, NULL,x_SPORT_INDICATOR),
61 decode( x_INTEREST_NAME, FND_API.G_MISS_CHAR, NULL,x_INTEREST_NAME),
62 HZ_UTILITY_V2PUB.CREATED_BY,
63 HZ_UTILITY_V2PUB.CREATION_DATE,
64 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
65 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
66 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
67 HZ_UTILITY_V2PUB.REQUEST_ID,
68 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
69 HZ_UTILITY_V2PUB.PROGRAM_ID,
70 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
71 decode( x_COMMENTS, FND_API.G_MISS_CHAR, NULL,x_COMMENTS),
72 decode( x_SUB_INTEREST_TYPE_CODE, FND_API.G_MISS_CHAR, NULL,x_SUB_INTEREST_TYPE_CODE),
73 decode( x_TEAM, FND_API.G_MISS_CHAR, NULL,x_TEAM),
74 decode( x_SINCE, FND_API.G_MISS_DATE, TO_DATE(NULL),x_SINCE),
75 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
76 decode( x_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, x_OBJECT_VERSION_NUMBER ),
77 decode( x_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, x_CREATED_BY_MODULE ),
78 HZ_UTILITY_V2PUB.APPLICATION_ID
79
80 ) RETURNING
81 PERSON_INTEREST_ID
82 INTO
83 X_PERSON_INTEREST_ID;
84
85 l_success := 'Y';
86
87 EXCEPTION
88 WHEN DUP_VAL_ON_INDEX THEN
89 IF INSTRB( SQLERRM, 'HZ_PERSON_INTEREST_U1' ) <> 0 OR
90 INSTRB( SQLERRM, 'HZ_PERSON_INTEREST_PK' ) <> 0
91 THEN
92 DECLARE
93 l_count NUMBER;
94 l_dummy VARCHAR2(1);
95 BEGIN
96 l_count := 1;
97 WHILE l_count > 0 LOOP
98 SELECT HZ_PERSON_INTEREST_S.NEXTVAL
99 INTO X_PERSON_INTEREST_ID FROM dual;
100 BEGIN
101 SELECT 'Y' INTO l_dummy
102 FROM HZ_PERSON_INTEREST
103 WHERE PERSON_INTEREST_ID = X_PERSON_INTEREST_ID;
104 l_count := 1;
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 l_count := 0;
108 END;
109 END LOOP;
110 END;
111 ELSE
112 RAISE;
113 END IF;
114
115 END;
116 END LOOP;
117
118 END Insert_Row;
119
120
121 PROCEDURE Delete_Row( x_PERSON_INTEREST_ID NUMBER
122 ) IS
123 BEGIN
124 DELETE FROM HZ_PERSON_INTEREST
125 WHERE PERSON_INTEREST_ID = x_PERSON_INTEREST_ID;
126 If (SQL%NOTFOUND) then
127 RAISE NO_DATA_FOUND;
128 End If;
129 END Delete_Row;
130
131
132
133 PROCEDURE Update_Row(
134 x_Rowid IN OUT NOCOPY VARCHAR2,
135 x_PERSON_INTEREST_ID IN NUMBER,
136 x_LEVEL_OF_INTEREST IN VARCHAR2,
137 x_PARTY_ID IN NUMBER,
138 x_LEVEL_OF_PARTICIPATION IN VARCHAR2,
139 x_INTEREST_TYPE_CODE IN VARCHAR2,
140 x_SPORT_INDICATOR IN VARCHAR2,
141 x_INTEREST_NAME IN VARCHAR2,
142 x_COMMENTS IN VARCHAR2,
143 x_SUB_INTEREST_TYPE_CODE IN VARCHAR2,
144 x_TEAM IN VARCHAR2,
145 x_SINCE IN DATE,
146 x_OBJECT_VERSION_NUMBER IN NUMBER,
147 x_STATUS IN VARCHAR2,
148 x_CREATED_BY_MODULE IN VARCHAR2,
149 x_APPLICATION_ID IN NUMBER
150 ) IS
151 BEGIN
152 Update HZ_PERSON_INTEREST
153 SET
154 PERSON_INTEREST_ID = decode( x_PERSON_INTEREST_ID, NULL, PERSON_INTEREST_ID, FND_API.G_MISS_NUM, NULL, x_PERSON_INTEREST_ID),
155 LEVEL_OF_INTEREST = decode( x_LEVEL_OF_INTEREST, NULL , LEVEL_OF_INTEREST, FND_API.G_MISS_CHAR, NULL, x_LEVEL_OF_INTEREST),
156 PARTY_ID = decode( x_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, x_PARTY_ID),
157 LEVEL_OF_PARTICIPATION = decode( x_LEVEL_OF_PARTICIPATION, NULL, LEVEL_OF_PARTICIPATION, FND_API.G_MISS_CHAR, NULL, x_LEVEL_OF_PARTICIPATION),
158 INTEREST_TYPE_CODE = decode( x_INTEREST_TYPE_CODE, NULL, INTEREST_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, x_INTEREST_TYPE_CODE),
159 SPORT_INDICATOR = decode( x_SPORT_INDICATOR, NULL, SPORT_INDICATOR, FND_API.G_MISS_CHAR, NULL, x_SPORT_INDICATOR),
160 INTEREST_NAME = decode( x_INTEREST_NAME, NULL, INTEREST_NAME, FND_API.G_MISS_CHAR, NULL, x_INTEREST_NAME),
161 -- Bug 3032780
162 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
163 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
164 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
165 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
167 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
168 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
169 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
170 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
171 COMMENTS = decode( x_COMMENTS, NULL, COMMENTS, FND_API.G_MISS_CHAR, NULL, x_COMMENTS),
172 SUB_INTEREST_TYPE_CODE = decode( x_SUB_INTEREST_TYPE_CODE, NULL, SUB_INTEREST_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, x_SUB_INTEREST_TYPE_CODE),
173 TEAM = decode( x_TEAM, NULL, TEAM, FND_API.G_MISS_CHAR, null, x_TEAM),
174 SINCE = decode( x_SINCE, NULL, SINCE, FND_API.G_MISS_DATE, NULL, x_SINCE),
175 STATUS = decode(x_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR,NULL,x_STATUS),
176 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
177 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
178 APPLICATION_ID = HZ_UTILITY_V2PUB.APPLICATION_ID
179
180
181 where rowid = X_RowId;
182
183 If (SQL%NOTFOUND) then
184 RAISE NO_DATA_FOUND;
185 End If;
186 END Update_Row;
187
188
189
190 PROCEDURE Lock_Row(
191
192 x_Rowid IN VARCHAR2,
193 x_PERSON_INTEREST_ID IN NUMBER,
194 x_LEVEL_OF_INTEREST IN VARCHAR2,
195 x_PARTY_ID IN NUMBER,
196 x_LEVEL_OF_PARTICIPATION IN VARCHAR2,
197 x_INTEREST_TYPE_CODE IN VARCHAR2,
198 x_SPORT_INDICATOR IN VARCHAR2,
199 x_INTEREST_NAME IN VARCHAR2,
200 x_CREATED_BY IN NUMBER,
201 x_CREATION_DATE IN DATE,
202 x_LAST_UPDATE_LOGIN IN NUMBER,
203 x_LAST_UPDATE_DATE IN DATE,
204 x_LAST_UPDATED_BY IN NUMBER,
205 x_REQUEST_ID IN NUMBER,
206 x_PROGRAM_APPLICATION_ID IN NUMBER,
207 x_PROGRAM_ID IN NUMBER,
208 x_PROGRAM_UPDATE_DATE IN DATE,
209 x_COMMENTS IN VARCHAR2,
210 x_SUB_INTEREST_TYPE_CODE IN VARCHAR2,
211 x_TEAM IN VARCHAR2,
212 x_SINCE IN DATE,
213 x_STATUS IN VARCHAR2,
214 x_CREATED_BY_MODULE IN VARCHAR2
215 ) IS
216 CURSOR C IS
217 SELECT *
218 FROM HZ_PERSON_INTEREST
219 WHERE rowid = x_Rowid
220 FOR UPDATE of PERSON_INTEREST_ID NOWAIT;
221 Recinfo C%ROWTYPE;
222 BEGIN
223 OPEN C;
224 FETCH C INTO Recinfo;
225 If (C%NOTFOUND) then
226 CLOSE C;
227 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
228 APP_EXCEPTION.RAISE_EXCEPTION;
229 End If;
230 CLOSE C;
231 if (
232 ( ( Recinfo.PERSON_INTEREST_ID = x_PERSON_INTEREST_ID)
233 OR ( ( Recinfo.PERSON_INTEREST_ID = NULL )
234 AND ( x_PERSON_INTEREST_ID = NULL )))
235 AND ( ( Recinfo.LEVEL_OF_INTEREST = x_LEVEL_OF_INTEREST)
236 OR ( ( Recinfo.LEVEL_OF_INTEREST = NULL )
237 AND ( x_LEVEL_OF_INTEREST = NULL )))
238 AND ( ( Recinfo.PARTY_ID = x_PARTY_ID)
239 OR ( ( Recinfo.PARTY_ID = NULL )
240 AND ( x_PARTY_ID = NULL )))
241 AND ( ( Recinfo.LEVEL_OF_PARTICIPATION = x_LEVEL_OF_PARTICIPATION)
242 OR ( ( Recinfo.LEVEL_OF_PARTICIPATION = NULL )
243 AND ( x_LEVEL_OF_PARTICIPATION = NULL )))
244 AND ( ( Recinfo.INTEREST_TYPE_CODE = x_INTEREST_TYPE_CODE)
245 OR ( ( Recinfo.INTEREST_TYPE_CODE = NULL )
246 AND ( x_INTEREST_TYPE_CODE = NULL )))
247 AND ( ( Recinfo.SPORT_INDICATOR = x_SPORT_INDICATOR)
248 OR ( ( Recinfo.SPORT_INDICATOR = NULL )
249 AND ( x_SPORT_INDICATOR = NULL )))
250 AND ( ( Recinfo.INTEREST_NAME = x_INTEREST_NAME)
251 OR ( ( Recinfo.INTEREST_NAME = NULL )
252 AND ( x_INTEREST_NAME = NULL )))
253 AND ( ( Recinfo.CREATED_BY = x_CREATED_BY)
254 OR ( ( Recinfo.CREATED_BY = NULL )
255 AND ( x_CREATED_BY = NULL )))
256 AND ( ( Recinfo.CREATION_DATE = x_CREATION_DATE)
257 OR ( ( Recinfo.CREATION_DATE = NULL )
258 AND ( x_CREATION_DATE = NULL )))
259 AND ( ( Recinfo.LAST_UPDATE_LOGIN = x_LAST_UPDATE_LOGIN)
260 OR ( ( Recinfo.LAST_UPDATE_LOGIN = NULL )
261 AND ( x_LAST_UPDATE_LOGIN = NULL )))
262 AND ( ( Recinfo.LAST_UPDATE_DATE = x_LAST_UPDATE_DATE)
263 OR ( ( Recinfo.LAST_UPDATE_DATE = NULL )
264 AND ( x_LAST_UPDATE_DATE = NULL )))
265 AND ( ( Recinfo.LAST_UPDATED_BY = x_LAST_UPDATED_BY)
266 OR ( ( Recinfo.LAST_UPDATED_BY = NULL )
267 AND ( x_LAST_UPDATED_BY = NULL )))
268 AND ( ( Recinfo.REQUEST_ID = x_REQUEST_ID)
269 OR ( ( Recinfo.REQUEST_ID = NULL )
270 AND ( x_REQUEST_ID = NULL )))
271 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = x_PROGRAM_APPLICATION_ID)
272 OR ( ( Recinfo.PROGRAM_APPLICATION_ID = NULL )
273 AND ( x_PROGRAM_APPLICATION_ID = NULL )))
274 AND ( ( Recinfo.PROGRAM_ID = x_PROGRAM_ID)
275 OR ( ( Recinfo.PROGRAM_ID = NULL )
276 AND ( x_PROGRAM_ID = NULL )))
277 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = x_PROGRAM_UPDATE_DATE)
278 OR ( ( Recinfo.PROGRAM_UPDATE_DATE = NULL )
279 AND ( x_PROGRAM_UPDATE_DATE = NULL )))
280 AND ( ( Recinfo.COMMENTS = x_COMMENTS)
281 OR ( ( Recinfo.COMMENTS = NULL )
282 AND ( x_COMMENTS = NULL )))
283 AND ( ( Recinfo.SUB_INTEREST_TYPE_CODE = x_SUB_INTEREST_TYPE_CODE)
284 OR ( ( Recinfo.SUB_INTEREST_TYPE_CODE = NULL )
285 AND ( x_SUB_INTEREST_TYPE_CODE = NULL )))
286 AND ( ( Recinfo.TEAM = x_TEAM)
287 OR ( ( Recinfo.TEAM = NULL )
288 AND ( x_TEAM = NULL )))
289 AND ( ( Recinfo.SINCE = x_SINCE)
290 OR ( ( Recinfo.SINCE = NULL )
291 AND ( x_SINCE = NULL )))
292
293 AND ( ( Recinfo.STATUS = x_STATUS)
294 OR ( ( Recinfo.STATUS = NULL )
295 AND ( x_STATUS = NULL )))
296 ) then
297 return;
298 else
299 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
300 APP_EXCEPTION.RAISE_EXCEPTION;
301 End If;
302 END Lock_Row;
303
304
305 PROCEDURE Select_Row (
306 x_person_interest_id IN OUT NOCOPY NUMBER,
307 x_level_of_interest OUT NOCOPY VARCHAR2,
308 x_party_id OUT NOCOPY NUMBER,
309 x_level_of_participation OUT NOCOPY VARCHAR2,
310 x_interest_type_code OUT NOCOPY VARCHAR2,
311 x_comments OUT NOCOPY VARCHAR2,
312 x_sport_indicator OUT NOCOPY VARCHAR2,
313 x_sub_interest_type_code OUT NOCOPY VARCHAR2,
314 x_interest_name OUT NOCOPY VARCHAR2,
315 x_team OUT NOCOPY VARCHAR2,
316 x_since OUT NOCOPY DATE,
317 x_status OUT NOCOPY VARCHAR2,
318 x_application_id OUT NOCOPY NUMBER,
319 x_created_by_module OUT NOCOPY VARCHAR2
320 ) IS
321 BEGIN
322
323 SELECT
324 NVL(person_interest_id, FND_API.G_MISS_NUM),
325 NVL(level_of_interest, FND_API.G_MISS_CHAR),
326 NVL(party_id, FND_API.G_MISS_NUM),
327 NVL(level_of_participation, FND_API.G_MISS_CHAR),
328 NVL(interest_type_code, FND_API.G_MISS_CHAR),
329 NVL(comments, FND_API.G_MISS_CHAR),
330 NVL(sport_indicator, FND_API.G_MISS_CHAR),
331 NVL(sub_interest_type_code, FND_API.G_MISS_CHAR),
332 NVL(interest_name, FND_API.G_MISS_CHAR),
333 NVL(team, FND_API.G_MISS_CHAR),
334 NVL(since, FND_API.G_MISS_DATE),
335 NVL(status, FND_API.G_MISS_CHAR),
336 NVL(application_id, FND_API.G_MISS_NUM),
337 NVL(created_by_module, FND_API.G_MISS_CHAR)
338 INTO
339 x_person_interest_id,
340 x_level_of_interest,
341 x_party_id,
342 x_level_of_participation,
343 x_interest_type_code,
344 x_comments,
345 x_sport_indicator,
346 x_sub_interest_type_code,
347 x_interest_name,
348 x_team,
349 x_since,
350 x_status,
351 x_application_id,
352 x_created_by_module
353 FROM HZ_PERSON_INTEREST
354 WHERE person_interest_id = x_person_interest_id;
355
356 EXCEPTION
357 WHEN NO_DATA_FOUND THEN
358 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
359 --2890664, Changed this message token
360 FND_MESSAGE.SET_TOKEN('RECORD', 'PERSON_INTEREST_REC');
361 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_person_interest_id));
362 FND_MSG_PUB.ADD;
363 RAISE FND_API.G_EXC_ERROR;
364
365 END Select_Row;
366
367
368 END HZ_PERSON_INTEREST_PKG;