[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_CERT_PKG
Source
1 PACKAGE BODY HZ_PARTY_CERT_PKG AS
2 /* $Header: ARHCERTB.pls 120.14 2005/10/30 04:17:45 appldev noship $ */
3
4 -- AUTHOR : CVIJAYAN ("VJN")
5 -- THIS API WAS CREATED TO UPDATE THE NEWLY CREATED COLUMN CERTIFICATION_LEVEL IN HZ_PARTIES
6 -- BASE BUG -- 3125139
7
8
9
10 /**
11 * PROCEDURE set_certification_level
12 *
13 * DESCRIPTION
14 * Sets the value of the newly added certification level flag in HZ_PARTIES .
15 *
16 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
17 *
18 *
19 * ARGUMENTS
20 *
21 *
22 * NOTES
23 *
24 * MODIFICATION HISTORY
25 *
26 * 08-06 -2002 Colathur Vijayan ("VJN") o Created.
27 * 15-FEB-2005 Rajeswari B o Bug No: 4181943 Added DQM Synchronization functionality.
28 */
29
30 PROCEDURE set_certification_level (
31 -- input parameters
32 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
33 p_party_id IN number,
34 p_cert_level IN VARCHAR2,
35 p_cert_reason_code IN VARCHAR2,
36 -- in/out parameters
37 x_object_version_number IN OUT NOCOPY NUMBER,
38 -- output parameters
39 x_return_status OUT NOCOPY VARCHAR2,
40 x_msg_count OUT NOCOPY NUMBER,
41 x_msg_data OUT NOCOPY VARCHAR2
42 )
43 IS
44
45 cursor get_party_type_csr is
46 select party_type
47 from hz_parties
48 where party_id = p_party_id;
49
50 l_object_version_number NUMBER;
51 l_rowid ROWID;
52 l_party_id NUMBER;
53 l_count NUMBER;
54 l_cert_reason_code varchar2(30);
55 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
56 dss_msg_count NUMBER := 0;
57 dss_msg_data VARCHAR2(2000):= null;
58 l_test_security VARCHAR2(1):= 'F';
59 l_party_type varchar2(30);
60
61 BEGIN
62
63 -- standard start of API savepoint
64 SAVEPOINT set_certification_level ;
65
66 -- initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.to_Boolean(p_init_msg_list) THEN
68 FND_MSG_PUB.initialize;
69 END IF;
70
71 -- initialize API return status to success.
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 -- check whether record has been updated by another user.
75 -- if not, lock it.
76
77 BEGIN
78 SELECT object_version_number, rowid , party_id
79 INTO l_object_version_number, l_rowid , l_party_id
80 FROM HZ_PARTIES
81 WHERE party_id = p_party_id
82 and party_type in ('ORGANIZATION','PERSON')
83 FOR UPDATE NOWAIT;
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
87 FND_MESSAGE.SET_TOKEN('RECORD', 'party of type organization');
88 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(P_PARTY_ID), 'NULL'));
89 FND_MSG_PUB.ADD;
90 RAISE FND_API.G_EXC_ERROR;
91 END;
92
93 -- If this happens the record has changed
94 IF NOT ((x_object_version_number is null and l_object_version_number is null)
95 OR (nvl(x_object_version_number,-1) = l_object_version_number))
96 THEN
97 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
98 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTIES');
99 FND_MSG_PUB.ADD;
100 RAISE FND_API.G_EXC_ERROR;
101 END IF;
102
103 x_object_version_number := nvl(l_object_version_number, 1) + 1;
104
105
106 l_cert_reason_code := p_cert_reason_code;
107 if (p_cert_level is null or p_cert_level = FND_API.G_MISS_CHAR)
108 then
109 l_cert_reason_code := null;
110 end if;
111
112
113 ----------------------------------
114 -- VALIDATIONS
115 ----------------------------------
116 -- validate the passed in certification status against ar_lookups
117 -- only if it is non-trivial ( neither null nor fnd_api.g_miss_char)
118
119 IF p_cert_level is not null and p_cert_level <> FND_API.G_MISS_CHAR
120 THEN
121 HZ_UTILITY_V2PUB.validate_lookup(
122 p_column => 'certification_level',
123 p_lookup_type => 'HZ_PARTY_CERT_LEVEL',
124 p_column_value => p_cert_level ,
125 x_return_status => x_return_status
126 );
127 END IF;
128
129 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
130 THEN
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133
134
135 -- validate the passed in certification reason code against ar_lookups
136 -- only if it is non-trivial ( neither null nor fnd_api.g_miss_char)
137
138 IF p_cert_reason_code is not null and p_cert_reason_code <> FND_API.G_MISS_CHAR
139 THEN
140 HZ_UTILITY_V2PUB.validate_lookup(
141 p_column => 'cert_reason_code',
142 p_lookup_type => 'HZ_PARTY_CERT_REASON',
143 p_column_value => p_cert_reason_code ,
144 x_return_status => x_return_status
145 );
146 END IF;
147
148 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
149 THEN
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153
154
155 -- take out DSS Check per PM
156 /* IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
157 l_test_security :=
158 hz_dss_util_pub.test_instance(
159 p_operation_code => 'UPDATE',
160 p_db_object_name => 'HZ_PARTIES',
161 p_instance_pk1_value => l_party_id,
162 p_user_name => fnd_global.user_name,
163 x_return_status => dss_return_status,
164 x_msg_count => dss_msg_count,
165 x_msg_data => dss_msg_data);
166
167 if dss_return_status <> fnd_api.g_ret_sts_success THEN
168 RAISE FND_API.G_EXC_ERROR;
169 end if;
170
171 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
172
173 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
174 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
175 hz_dss_util_pub.get_display_name('HZ_PARTIES', null));
176 FND_MSG_PUB.ADD;
177 RAISE FND_API.G_EXC_ERROR;
178 end if;
179 END IF;
180
181 */
182 -- do the actual update
183 UPDATE HZ_PARTIES SET
184 CERTIFICATION_LEVEL = DECODE( p_cert_level, FND_API.G_MISS_CHAR, NULL, p_cert_level ),
185 CERT_REASON_CODE = DECODE( l_cert_reason_code, FND_API.G_MISS_CHAR, NULL, l_cert_reason_code ),
186 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
187 CREATION_DATE = CREATION_DATE,
188 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
189 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID, --Bug No.4181943
190 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
191 CREATED_BY = CREATED_BY,
192 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
193 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
194 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
195 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER )
196 WHERE ROWID = l_rowid;
197
198 -- Start of changes Bug No: 4181943
199
200 -- do DQM sychronization
201
202 open get_party_type_csr;
203 fetch get_party_type_csr into l_party_type;
204 close get_party_type_csr;
205
206
207 if l_party_type = 'ORGANIZATION' then
208
209 HZ_DQM_SYNC.sync_org(p_party_id,'U');
210
211 elsif l_party_type = 'PERSON' then
212
213 HZ_DQM_SYNC.sync_person(p_party_id,'U');
214
215 end if;
216
217 -- End of changes Bug No: 4181943
218
219
220 -- standard call to get message count and if count is 1, get message info.
221 FND_MSG_PUB.Count_And_Get(
222 p_encoded => FND_API.G_FALSE,
223 p_count => x_msg_count,
224 p_data => x_msg_data);
225
226 EXCEPTION
227 WHEN FND_API.G_EXC_ERROR THEN
228 ROLLBACK TO set_certification_level;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MSG_PUB.Count_And_Get(
231 p_encoded => FND_API.G_FALSE,
232 p_count => x_msg_count,
233 p_data => x_msg_data);
234 WHEN OTHERS THEN
235 ROLLBACK TO set_certification_level ;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
238 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
239 FND_MSG_PUB.ADD;
240 FND_MSG_PUB.Count_And_Get(
241 p_encoded => FND_API.G_FALSE,
242 p_count => x_msg_count,
243 p_data => x_msg_data);
244 END set_certification_level ;
245
246 /* should only be called from DL UI.Since validation is done from UI. No additional
247 validation here */
248
249 PROCEDURE set_party_attributes(
250 -- input parameters
251 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
252 p_party_id IN number,
253 p_status IN VARCHAR2,
254 p_internal_flag IN VARCHAR2,
255 -- in/out parameters
256 x_object_version_number IN OUT NOCOPY NUMBER,
257 -- output parameters
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2
261 ) is
262 cursor get_party_type_csr is
263 select party_type
264 from hz_parties
265 where party_id = p_party_id;
266
267 l_party_type varchar2(30);
268 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
269 dss_msg_count NUMBER := 0;
270 dss_msg_data VARCHAR2(2000):= null;
271 l_test_security VARCHAR2(1):= 'F';
272
273 begin
274 -- standard start of API savepoint
275 SAVEPOINT set_party_attributes ;
276
277 -- initialize message list if p_init_msg_list is set to TRUE.
278 IF FND_API.to_Boolean(p_init_msg_list) THEN
279 FND_MSG_PUB.initialize;
280 END IF;
281
282 -- initialize API return status to success.
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284
285 -- Take out DSS Check per PM
286 /* IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
287 l_test_security :=
288 hz_dss_util_pub.test_instance(
289 p_operation_code => 'UPDATE',
290 p_db_object_name => 'HZ_PARTIES',
291 p_instance_pk1_value => p_party_id,
292 p_user_name => fnd_global.user_name,
293 x_return_status => dss_return_status,
294 x_msg_count => dss_msg_count,
295 x_msg_data => dss_msg_data);
296
297 if dss_return_status <> fnd_api.g_ret_sts_success THEN
298 RAISE FND_API.G_EXC_ERROR;
299 end if;
300
301 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
302
303 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
304 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
305 hz_dss_util_pub.get_display_name('HZ_PARTIES', null));
306 FND_MSG_PUB.ADD;
307 RAISE FND_API.G_EXC_ERROR;
308 end if;
309 END IF;
310 */
311
312 open get_party_type_csr;
313 fetch get_party_type_csr into l_party_type;
314 close get_party_type_csr;
315
316 if p_internal_flag is not null
317 then
318 if l_party_type = 'ORGANIZATION'
319 then
320
321
322 UPDATE HZ_ORGANIZATION_PROFILES SET
323 internal_flag = p_internal_flag,
324 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
325 CREATION_DATE = CREATION_DATE,
326 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
327 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID, --Bug No.4181943
328 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
329 CREATED_BY = CREATED_BY,
330 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
331 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
332 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
333 OBJECT_VERSION_NUMBER = nvl(object_version_number,1)+1
334 WHERE party_id = p_party_id;
335
336
337 elsif l_party_type = 'PERSON'
338 then
339
340 UPDATE HZ_PERSON_PROFILES SET
341 internal_flag = p_internal_flag,
342 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
343 CREATION_DATE = CREATION_DATE,
344 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
345 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID, --Bug No.4181943
346 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
347 CREATED_BY = CREATED_BY,
348 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
349 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
350 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
351 OBJECT_VERSION_NUMBER = nvl(object_version_number,1)+1
352 WHERE party_id = p_party_id;
353
354
355 end if;
356 end if; -- internal_flag is not null
357
358 if p_status is not null
359 then
360 UPDATE HZ_PARTIES SET
361 status = p_status,
362 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
363 CREATION_DATE = CREATION_DATE,
364 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
365 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID, --Bug No.4181943
366 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
367 CREATED_BY = CREATED_BY,
368 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
369 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
370 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
371 OBJECT_VERSION_NUMBER = nvl(object_version_number,1)+1
372 WHERE party_id = p_party_id;
373 end if;
374
375 -- Start of changes Bug No: 4181943
376
377 -- do DQM sychronization
378
379 if l_party_type = 'ORGANIZATION' then
380
381 HZ_DQM_SYNC.sync_org(p_party_id,'U');
382
383 elsif l_party_type = 'PERSON' then
384
385 HZ_DQM_SYNC.sync_person(p_party_id,'U');
386
387 end if;
388
389 -- End of changes Bug No: 4181943
390
391 -- standard call to get message count and if count is 1, get message info.
392 FND_MSG_PUB.Count_And_Get(
393 p_encoded => FND_API.G_FALSE,
394 p_count => x_msg_count,
395 p_data => x_msg_data);
396
397 EXCEPTION
398 WHEN FND_API.G_EXC_ERROR THEN
399 ROLLBACK TO set_party_attributes;
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 FND_MSG_PUB.Count_And_Get(
402 p_encoded => FND_API.G_FALSE,
403 p_count => x_msg_count,
404 p_data => x_msg_data);
405 WHEN OTHERS THEN
406 ROLLBACK TO set_party_attributes;
407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
408 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
409 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
410 FND_MSG_PUB.ADD;
411 FND_MSG_PUB.Count_And_Get(
412 p_encoded => FND_API.G_FALSE,
413 p_count => x_msg_count,
414 p_data => x_msg_data);
415
416 end;
417
418
419 END HZ_PARTY_CERT_PKG ;
420