[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_INFO_PUB
Source
1 package body HZ_PARTY_INFO_PUB AS
2 /*$Header: ARHPTISB.pls 120.9 2005/12/07 19:33:06 acng ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_PARTY_INFO_PUB';
5
6 -- Bug 2197181: added for mix-n-match project.
7
8 g_cre_mixnmatch_enabled VARCHAR2(1);
9 g_cre_selected_datasources VARCHAR2(255);
10 g_cre_is_datasource_selected VARCHAR2(1) := 'N';
11 g_cre_entity_attr_id NUMBER;
12
13 procedure do_create_credit_ratings(
14 p_credit_ratings_rec IN OUT NOCOPY credit_ratings_rec_type,
15 x_credit_rating_id OUT NOCOPY NUMBER,
16 x_return_status IN OUT NOCOPY VARCHAR2
17 );
18
19 procedure do_update_credit_ratings(
20 p_credit_ratings_rec IN OUT NOCOPY credit_ratings_rec_type,
21 p_last_update_date IN OUT NOCOPY DATE,
22 x_return_status IN OUT NOCOPY VARCHAR2
23 );
24
25 procedure do_create_financial_profile(
26 p_financial_profile_rec IN OUT NOCOPY financial_profile_rec_type,
27 x_financial_profile_id OUT NOCOPY NUMBER,
28 x_return_status IN OUT NOCOPY VARCHAR2
29 );
30
31 procedure do_update_financial_profile(
32 p_financial_profile_rec IN OUT NOCOPY financial_profile_rec_type,
33 p_last_update_date IN OUT NOCOPY DATE,
34 x_return_status IN OUT NOCOPY VARCHAR2
35 );
36
37 /*===========================================================================+
38 | PROCEDURE
39 | create_credit_ratings
40 |
41 | DESCRIPTION
42 | Creates credit ratings.
43 |
44 | SCOPE - PUBLIC
45 |
46 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
47 |
48 | ARGUMENTS : IN:
49 | p_api_version
50 | p_init_msg_list
51 | p_commit
52 | p_credit_ratings_rec
53 | p_validation_level
54 | OUT:
55 | x_return_status
56 | x_msg_count
57 | x_msg_data
58 | x_credit_rating_id
59 | IN/ OUT:
60 |
61 | RETURNS : NONE
62 |
63 | NOTES
64 |
65 | MODIFICATION HISTORY
66 |
67 | 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
68 | For non-profile entities, the concept of
69 | select/de-select data-sources is obsoleted.
70 +===========================================================================
71
72 procedure create_credit_ratings(
73 p_api_version IN NUMBER,
74 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
75 p_commit IN VARCHAR2:= FND_API.G_FALSE,
76 p_credit_ratings_rec IN CREDIT_RATINGS_REC_TYPE,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_msg_count OUT NOCOPY NUMBER,
79 x_msg_data OUT NOCOPY VARCHAR2,
80 x_credit_rating_id OUT NOCOPY NUMBER,
81 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
82 ) IS
83
84 l_api_name CONSTANT VARCHAR2(30) := 'create credit ratings';
85 l_api_version CONSTANT NUMBER := 1.0;
86 l_credit_ratings_rec CREDIT_RATINGS_REC_TYPE := p_credit_ratings_rec;
87
88 BEGIN
89
90 --Standard start of API savepoint
91 SAVEPOINT create_credit_ratings_pub;
92
93 --Standard call to check for call compatibility.
94 IF NOT FND_API.Compatible_API_Call(
95 l_api_version,
96 p_api_version,
97 l_api_name,
98 G_PKG_NAME)
99 THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 --Initialize message list if p_init_msg_list is set to TRUE.
104 IF FND_API.to_Boolean(p_init_msg_list) THEN
105 FND_MSG_PUB.initialize;
106 END IF;
107
108 --Initialize API return status to success.
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110
111 -- Bug 2197181: added for mix-n-match project. first load data
112 -- sources for this entity. Then assign the actual_content_source
113 -- to the real data source. The value of content_source_type is
114 -- depended on if data source is seleted. If it is selected, we reset
115 -- content_source_type to user-entered. We also check if user
116 -- has the privilege to create user-entered data if mix-n-match
117 -- is enabled.
118
119 -- Bug 2444678: Removed caching.
120
121 -- IF g_cre_mixnmatch_enabled IS NULL THEN
122 * SSM SST Integration and Extension
123 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
124
125 HZ_MIXNM_UTILITY.LoadDataSources(
126 p_entity_name => 'HZ_CREDIT_RATINGS',
127 p_entity_attr_id => g_cre_entity_attr_id,
128 p_mixnmatch_enabled => g_cre_mixnmatch_enabled,
129 p_selected_datasources => g_cre_selected_datasources );
130
131 -- END IF;
132
133 HZ_MIXNM_UTILITY.AssignDataSourceDuringCreation (
134 p_entity_name => 'HZ_CREDIT_RATINGS',
135 p_entity_attr_id => g_cre_entity_attr_id,
136 p_mixnmatch_enabled => g_cre_mixnmatch_enabled,
137 p_selected_datasources => g_cre_selected_datasources,
138 p_content_source_type => l_credit_ratings_rec.content_source_type,
139 p_actual_content_source => l_credit_ratings_rec.actual_content_source,
140 x_is_datasource_selected => g_cre_is_datasource_selected,
141 x_return_status => x_return_status,
142 p_api_version => 'V1');
143
144 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
145 RAISE FND_API.G_EXC_ERROR;
146 END IF;
147
148 ******** this code is replaced to go via V2 API.
149 -- Call to business logic.
150 do_create_credit_ratings(
151 l_credit_ratings_rec,
152 x_credit_rating_id,
153 x_return_status );
154 *********
155
156 -- call to perform everything through V2 API
157 HZ_PARTY_INFO_V2PVT.v2_create_credit_rating
158 (l_credit_ratings_rec,
159 x_return_status,
160 x_credit_rating_id);
161
162 ********* this call will be from V2 API
163 -- Invoke business event system.
164 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
165 -- Bug 2197181: Added below condition for Mix-n-Match
166 g_cre_is_datasource_selected = 'Y'
167 THEN
168 HZ_BUSINESS_EVENT_V2PVT.create_credit_ratings_event(l_credit_ratings_rec);
169 END IF;
170 *********
171
172 --Standard check of p_commit.
173 IF FND_API.to_Boolean(p_commit) THEN
174 Commit;
175 END IF;
176
177 --Standard call to get message count and if count is 1, get message info.
178 FND_MSG_PUB.Count_And_Get(
179 p_encoded => FND_API.G_FALSE,
180 p_count => x_msg_count,
181 p_data => x_msg_data);
182
183 EXCEPTION
184 WHEN FND_API.G_EXC_ERROR THEN
185 ROLLBACK TO create_credit_ratings_pub;
186 x_return_status := FND_API.G_RET_STS_ERROR;
187
188 FND_MSG_PUB.Count_And_Get(
189 p_encoded => FND_API.G_FALSE,
190 p_count => x_msg_count,
191 p_data => x_msg_data);
192
193 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
194 ROLLBACK TO create_credit_ratings_pub;
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196
197 FND_MSG_PUB.Count_And_Get(
198 p_encoded => FND_API.G_FALSE,
199 p_count => x_msg_count,
200 p_data => x_msg_data);
201
202 WHEN OTHERS THEN
203 ROLLBACK TO create_credit_ratings_pub;
204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
205
206 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
207 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
208 FND_MSG_PUB.ADD;
209
210 FND_MSG_PUB.Count_And_Get(
211 p_encoded => FND_API.G_FALSE,
212 p_count => x_msg_count,
213 p_data => x_msg_data);
214
215 END create_credit_ratings;
216
217 END obsolete v1 api;
218 */
219 /*===========================================================================+
220 | PROCEDURE
221 | update_credit_ratings
222 |
223 | DESCRIPTION
224 | Updates credit ratings.
225 |
226 | SCOPE - PUBLIC
227 |
228 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
229 |
230 | ARGUMENTS : IN:
231 | p_api_version
232 | p_init_msg_list
233 | p_commit
234 | p_credit_ratings_rec
235 | p_validation_level
236 | OUT:
237 | x_return_status
238 | x_msg_count
239 | x_msg_data
240 | IN/ OUT:
241 | p_last_update_date
242 |
243 | RETURNS : NONE
244 |
245 | NOTES
246 |
247 | MODIFICATION HISTORY
248 |
249 | 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
250 | For non-profile entities, the concept of
251 | select/de-select data-sources is obsoleted.
252 +===========================================================================
253
254 procedure update_credit_ratings(
255 p_api_version IN NUMBER,
256 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
257 p_commit IN VARCHAR2:= FND_API.G_FALSE,
258 p_credit_ratings_rec IN CREDIT_RATINGS_REC_TYPE,
259 p_last_update_date IN OUT NOCOPY DATE,
260 x_return_status OUT NOCOPY VARCHAR2,
261 x_msg_count OUT NOCOPY NUMBER,
262 x_msg_data OUT NOCOPY VARCHAR2,
263 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
264 ) IS
265
266 l_api_name CONSTANT VARCHAR2(30) := 'update credit ratings';
267 l_api_version CONSTANT NUMBER := 1.0;
268 l_credit_ratings_rec CREDIT_RATINGS_REC_TYPE := p_credit_ratings_rec;
269 l_old_credit_ratings_rec CREDIT_RATINGS_REC_TYPE;
270
271 BEGIN
272
273 --Standard start of API savepoint
274 SAVEPOINT update_credit_ratings_pub;
275
276 --Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call(
278 l_api_version,
279 p_api_version,
280 l_api_name,
281 G_PKG_NAME)
282 THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285
286 --Initialize message list if p_init_msg_list is set to TRUE.
287 IF FND_API.to_Boolean(p_init_msg_list) THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290
291 --Initialize API return status to success.
292 x_return_status := FND_API.G_RET_STS_SUCCESS;
293
294 get_current_credit_rating(
295 1, FND_API.G_FALSE,
296 l_credit_ratings_rec.credit_rating_id,
297 l_old_credit_ratings_rec,
298 x_return_status,
299 x_msg_count,
300 x_msg_data);
301
302 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
303 RAISE FND_API.G_EXC_ERROR;
304 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 -- Bug 2197181: added for mix-n-match project. first load data
309 -- sources for this entity.
310
311 -- Bug 2444678: Removed caching.
312 * SSM SST Integration and Extension
313 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
314 * There is no need to check if the data-source is selected.
315
316 -- IF g_cre_mixnmatch_enabled IS NULL THEN
317 HZ_MIXNM_UTILITY.LoadDataSources(
318 p_entity_name => 'HZ_CREDIT_RATINGS',
319 p_entity_attr_id => g_cre_entity_attr_id,
320 p_mixnmatch_enabled => g_cre_mixnmatch_enabled,
321 p_selected_datasources => g_cre_selected_datasources );
322 -- END IF;
323
324 -- Bug 2197181: added for mix-n-match project.
325 -- check if the data source is seleted.
326
327 g_cre_is_datasource_selected :=
328 HZ_MIXNM_UTILITY.isDataSourceSelected (
329 p_selected_datasources => g_cre_selected_datasources,
330 p_actual_content_source => l_old_credit_ratings_rec.actual_content_source );
331
332 ********* replaced the following code to everything via V2 API
333 -- Call to business logic.
334 do_update_credit_ratings(
335 l_credit_ratings_rec,
336 p_last_update_date,
337 x_return_status );
338 ************
339
340 -- call to perform everything through V2 API
341 HZ_PARTY_INFO_V2PVT.v2_update_credit_rating
342 (l_credit_ratings_rec,
343 p_last_update_date,
344 x_return_status);
345
346 ********* this call will be from V2 API
347 -- Invoke business event system.
348 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
349 -- Bug 2197181: Added below condition for Mix-n-Match
350 g_cre_is_datasource_selected = 'Y'
351 THEN
352 HZ_BUSINESS_EVENT_V2PVT.update_credit_ratings_event( l_credit_ratings_rec);
353 END IF;
354 *************
355
356 --Standard check of p_commit.
357 IF FND_API.to_Boolean(p_commit) THEN
358 Commit;
359 END IF;
360
361 --Standard call to get message count and if count is 1, get message info.
362 FND_MSG_PUB.Count_And_Get(
363 p_encoded => FND_API.G_FALSE,
364 p_count => x_msg_count,
365 p_data => x_msg_data);
366
367 EXCEPTION
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO update_credit_ratings_pub;
370 x_return_status := FND_API.G_RET_STS_ERROR;
371
372 FND_MSG_PUB.Count_And_Get(
373 p_encoded => FND_API.G_FALSE,
374 p_count => x_msg_count,
375 p_data => x_msg_data);
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 ROLLBACK TO update_credit_ratings_pub;
379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
380
381 FND_MSG_PUB.Count_And_Get(
382 p_encoded => FND_API.G_FALSE,
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385
386 WHEN OTHERS THEN
387 ROLLBACK TO update_credit_ratings_pub;
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
389
390 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
391 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
392 FND_MSG_PUB.ADD;
393
394 FND_MSG_PUB.Count_And_Get(
395 p_encoded => FND_API.G_FALSE,
396 p_count => x_msg_count,
397 p_data => x_msg_data);
398
399 END update_credit_ratings;
400
401 END Obsolete v1 api
402
403 */
404
405 /*===========================================================================+
406 | PROCEDURE
407 | create_financial_profile
408 |
409 | DESCRIPTION
410 | Creates financial profile.
411 |
412 | SCOPE - PUBLIC
413 |
414 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
415 |
416 | ARGUMENTS : IN:
417 | p_api_version
418 | p_init_msg_list
419 | p_commit
420 | p_financial_profile_rec
421 | p_validation_level
422 | OUT:
423 | x_return_status
424 | x_msg_count
425 | x_msg_data
426 | x_financial_profile_id
427 | IN/ OUT:
428 |
429 | RETURNS : NONE
430 |
431 | NOTES
432 |
433 | MODIFICATION HISTORY
434 |
435 +===========================================================================*/
436
437 procedure create_financial_profile(
438 p_api_version IN NUMBER,
439 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
440 p_commit IN VARCHAR2:= FND_API.G_FALSE,
441 p_financial_profile_rec IN FINANCIAL_PROFILE_REC_TYPE,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 x_financial_profile_id OUT NOCOPY NUMBER,
446 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
447 ) IS
448 l_api_name CONSTANT VARCHAR2(30) := 'create financial profile';
449 l_api_version CONSTANT NUMBER := 1.0;
450 l_financial_profile_rec FINANCIAL_PROFILE_REC_TYPE := p_financial_profile_rec;
451
452 BEGIN
453 --Standard start of API savepoint
454 SAVEPOINT create_financial_profile_pub;
455 --Standard call to check for call compatibility.
456 IF NOT FND_API.Compatible_API_Call(
457 l_api_version,
458 p_api_version,
459 l_api_name,
460 G_PKG_NAME)
461 THEN
462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
463 END IF;
464
465 --Initialize message list if p_init_msg_list is set to TRUE.
466 IF FND_API.to_Boolean(p_init_msg_list) THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469
470 --Initialize API return status to success.
471 x_return_status := FND_API.G_RET_STS_SUCCESS;
472 /*
473 --Call to User-Hook pre Processing Procedure
474 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
475 hz_party_info_crmhk.create_financial_profile_pre(
476 l_financial_profile_rec,
477 x_return_status,
478 x_msg_count,
479 x_msg_data);
480
481 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
482 RAISE FND_API.G_EXC_ERROR;
483 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
484 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
485 FND_MESSAGE.SET_TOKEN('PROCEDURE',
486 'HZ_PARTY_INFO_CRMHK.CREATE_FINANCIAL_PROFILE_PRE');
487 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
488 FND_MSG_PUB.ADD;
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491
492 END IF;
493 */
494
495 -- Call to business logic.
496 -- Call PL/SQL wrapper over table handler
497 do_create_financial_profile( l_financial_profile_rec,
498 x_financial_profile_id,
499 x_return_status);
500 /*
501 --Call to User-Hook post Processing Procedure
502 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
503 hz_party_info_crmhk.create_financial_profile_post(
504 l_financial_profile_rec,
505 x_return_status,
506 x_msg_count,
507 x_msg_data);
508
509 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
510 RAISE FND_API.G_EXC_ERROR;
511 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
512 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
513 FND_MESSAGE.SET_TOKEN('PROCEDURE',
514 'HZ_PARTY_INFO_CRMHK.CREATE_FINANCIAL_PROFILE_POST');
515 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
516 FND_MSG_PUB.ADD;
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519
520 END IF;
521 */
522 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
523 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
524 hz_business_event_v2pvt.create_fin_profile_event(L_FINANCIAL_PROFILE_REC);
525 END IF;
526
527 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
528 -- populate function for integration service
529 HZ_POPULATE_BOT_PKG.pop_hz_financial_profile(
530 p_operation => 'I',
531 p_financial_profile_id => x_financial_profile_id);
532 END IF;
533 END IF;
534
535 --Standard check of p_commit.
536 IF FND_API.to_Boolean(p_commit) THEN
537 Commit;
538 END IF;
539
540 --Standard call to get message count and if count is 1, get message info.
541 FND_MSG_PUB.Count_And_Get(
542 p_encoded => FND_API.G_FALSE,
543 p_count => x_msg_count,
544 p_data => x_msg_data);
545 EXCEPTION
546 WHEN FND_API.G_EXC_ERROR THEN
547 ROLLBACK TO create_financial_profile_pub;
548 x_return_status := FND_API.G_RET_STS_ERROR;
549 FND_MSG_PUB.Count_And_Get(
550 p_encoded => FND_API.G_FALSE,
551 p_count => x_msg_count,
552 p_data => x_msg_data);
553
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 ROLLBACK TO create_financial_profile_pub;
556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
557 FND_MSG_PUB.Count_And_Get(
558 p_encoded => FND_API.G_FALSE,
559 p_count => x_msg_count,
560 p_data => x_msg_data);
561
562 WHEN OTHERS THEN
563 ROLLBACK TO create_financial_profile_pub;
564 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
565
566 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
567 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
568 FND_MSG_PUB.ADD;
569
570 FND_MSG_PUB.Count_And_Get(
571 p_encoded => FND_API.G_FALSE,
572 p_count => x_msg_count,
573 p_data => x_msg_data);
574
575 END create_financial_profile;
576
577 /*===========================================================================+
578 | PROCEDURE
579 | update_financial_profile
580 |
581 | DESCRIPTION
582 | Updates financial profile.
583 |
584 | SCOPE - PUBLIC
585 |
586 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
587 |
588 | ARGUMENTS : IN:
589 | p_api_version
590 | p_init_msg_list
591 | p_commit
592 | p_financial_profile_rec
593 | p_validation_level
594 | OUT:
595 | x_return_status
596 | x_msg_count
597 | x_msg_data
598 | IN/ OUT:
599 | p_last_update_date
600 |
601 | RETURNS : NONE
602 |
603 | NOTES
604 |
605 | MODIFICATION HISTORY
606 |
607 +===========================================================================*/
608
609 procedure update_financial_profile(
610 p_api_version IN NUMBER,
611 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
612 p_commit IN VARCHAR2:= FND_API.G_FALSE,
613 p_financial_profile_rec IN FINANCIAL_PROFILE_REC_TYPE,
614 p_last_update_date IN OUT NOCOPY DATE,
615 x_return_status OUT NOCOPY VARCHAR2,
616 x_msg_count OUT NOCOPY NUMBER,
617 x_msg_data OUT NOCOPY VARCHAR2,
618 p_validation_level IN NUMBER :=FND_API.G_VALID_LEVEL_FULL
619 ) IS
620 l_api_name CONSTANT VARCHAR2(30) := 'update financial profile';
621 l_api_version CONSTANT NUMBER := 1.0;
622 l_financial_profile_rec FINANCIAL_PROFILE_REC_TYPE := p_financial_profile_rec;
623
624 BEGIN
625 --Standard start of API savepoint
626 SAVEPOINT update_financial_profile_pub;
627 --Standard call to check for call compatibility.
628 IF NOT FND_API.Compatible_API_Call(
629 l_api_version,
630 p_api_version,
631 l_api_name,
632 G_PKG_NAME)
633 THEN
634 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
635 END IF;
636
637 --Initialize message list if p_init_msg_list is set to TRUE.
638 IF FND_API.to_Boolean(p_init_msg_list) THEN
639 FND_MSG_PUB.initialize;
640 END IF;
641
642 --Initialize API return status to success.
643 x_return_status := FND_API.G_RET_STS_SUCCESS;
644
645 /*
646 --Call to User-Hook pre Processing Procedure
647 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
648 hz_party_info_crmhk.update_financial_profile_pre(
649 l_financial_profile_rec,
650 x_return_status,
651 x_msg_count,
652 x_msg_data);
653
654 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
655 RAISE FND_API.G_EXC_ERROR;
656 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
657 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
658 FND_MESSAGE.SET_TOKEN('PROCEDURE',
659 'HZ_PARTY_INFO_CRMHK.UPDATE_FINANCIAL_PROFILE_PRE');
660 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
661 FND_MSG_PUB.ADD;
662 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
663 END IF;
664
665 END IF;
666 */
667
668 -- Call to business logic.
669 -- Call PL/SQL wrapper over table handler
670 do_update_financial_profile( l_financial_profile_rec,
671 p_last_update_date,
672 x_return_status);
673
674 /*
675 --Call to User-Hook post Processing Procedure
676 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' THEN
677 hz_party_info_crmhk.update_financial_profile_post(
678 l_financial_profile_rec,
679 x_return_status,
680 x_msg_count,
681 x_msg_data);
682
683 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
684 RAISE FND_API.G_EXC_ERROR;
685 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
686 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
687 FND_MESSAGE.SET_TOKEN('PROCEDURE',
688 'HZ_PARTY_INFO_CRMHK.UPDATE_FINANCIAL_PROFILE_POST');
689 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
690 FND_MSG_PUB.ADD;
691 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
692 END IF;
693
694 END IF;
695 */
696 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
697 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
698 hz_business_event_v2pvt.update_fin_profile_event(l_financial_profile_rec);
699 END IF;
700
701 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
702 -- populate function for integration service
703 HZ_POPULATE_BOT_PKG.pop_hz_financial_profile(
704 p_operation => 'U',
705 p_financial_profile_id => l_financial_profile_rec.financial_profile_id);
706 END IF;
707 END IF;
708
709 --Standard check of p_commit.
710 IF FND_API.to_Boolean(p_commit) THEN
711 Commit;
712 END IF;
713
714 --Standard call to get message count and if count is 1, get message info.
715 FND_MSG_PUB.Count_And_Get(
716 p_encoded => FND_API.G_FALSE,
717 p_count => x_msg_count,
718 p_data => x_msg_data);
719 EXCEPTION
720 WHEN FND_API.G_EXC_ERROR THEN
721 ROLLBACK TO update_financial_profile_pub;
722 x_return_status := FND_API.G_RET_STS_ERROR;
723 FND_MSG_PUB.Count_And_Get(
724 p_encoded => FND_API.G_FALSE,
725 p_count => x_msg_count,
726 p_data => x_msg_data);
727
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 ROLLBACK TO update_financial_profile_pub;
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 FND_MSG_PUB.Count_And_Get(
732 p_encoded => FND_API.G_FALSE,
733 p_count => x_msg_count,
734 p_data => x_msg_data);
735
736 WHEN OTHERS THEN
737 ROLLBACK TO update_financial_profile_pub;
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
739
740 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
741 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
742 FND_MSG_PUB.ADD;
743
744 FND_MSG_PUB.Count_And_Get(
745 p_encoded => FND_API.G_FALSE,
746 p_count => x_msg_count,
747 p_data => x_msg_data);
748
749 END update_financial_profile;
750
751 /*===========================================================================+
752 | PROCEDURE
753 | do_create_creidt_ratings
754 |
755 | DESCRIPTION
756 | Creates credit ratings.
757 |
758 | SCOPE - PRIVATE
759 |
760 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
761 |
762 | ARGUMENTS : IN:
763 | OUT:
764 | x_credit_rating_id
765 | IN/ OUT:
766 | p_credit_ratings_rec
767 | x_return_status
768 |
769 | RETURNS : NONE
770 |
771 | NOTES
772 |
773 | MODIFICATION HISTORY
774 | Jianying Huang 06-OCT-00 Bug 1428526: make *_rec parameters as
775 | 'IN OUT' in order to pass the changed record
776 | to caller.
777 |
778 +===========================================================================*/
779
780 procedure do_create_credit_ratings(
781 p_credit_ratings_rec IN OUT NOCOPY credit_ratings_rec_type,
782 x_credit_rating_id OUT NOCOPY NUMBER,
783 x_return_status IN OUT NOCOPY VARCHAR2
784 ) IS
785
786 l_credit_rating_id NUMBER:= p_credit_ratings_rec.credit_rating_id;
787 l_rowid ROWID := NULL;
788 l_count NUMBER;
789 x_msg_count NUMBER;
790 x_msg_data VARCHAR2(2000);
791
792 BEGIN
793
794 --Call to User-Hook pre Processing Procedure
795 --Bug 1363124: validation#3 of content_source_type
796 /*
797 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' AND
798 -- Bug 2197181: Modifed the condition
799 g_cre_is_datasource_selected = 'Y'
800 THEN
801 hz_party_info_crmhk.create_credit_ratings_pre(
802 p_credit_ratings_rec,
803 x_return_status,
804 x_msg_count,
805 x_msg_data);
806
807 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
808 RAISE FND_API.G_EXC_ERROR;
809 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
810 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
811 FND_MESSAGE.SET_TOKEN('PROCEDURE','HZ_PARTY_INFO_CRMHK.CREATE_CREDIT_RATINGS_PRE');
812 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
813 FND_MSG_PUB.ADD;
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF;
816 END IF;
817 */
818
819 -- if credit_rating_id is NULL, then generate PK.
820
821 IF l_credit_rating_id is NULL OR
822 l_credit_rating_id = FND_API.G_MISS_NUM
823 THEN
824 l_count := 1;
825 WHILE l_count > 0 LOOP
826 SELECT hz_credit_ratings_s.nextval
827 INTO l_credit_rating_id FROM dual;
828
829 SELECT count(*)
830 INTO l_count
831 FROM hz_credit_ratings
832 WHERE credit_rating_id = l_credit_rating_id;
833 END LOOP;
834 ELSE
835 l_count := 0;
836
837 SELECT count(*)
838 INTO l_count
839 FROM hz_credit_ratings
840 WHERE credit_rating_id = l_credit_rating_id;
841
842 IF l_count > 0 THEN
843 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
844 FND_MESSAGE.SET_TOKEN('COLUMN', 'credit_rating_id');
845 FND_MSG_PUB.ADD;
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848 END IF;
849
850 x_credit_rating_id := l_credit_rating_id;
851
852 -- validate credit ratings record
853
854 HZ_PARTY_INFO_VAL.validate_credit_ratings(
855 p_credit_ratings_rec, 'C', x_return_status);
856
857 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 -- sync obsolete column suit_judge_ind with new column suit_ind
862
863 IF (p_credit_ratings_rec.suit_judge_ind is not null AND
864 p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR) AND
865 ( p_credit_ratings_rec.suit_ind is null OR
866 p_credit_ratings_rec.suit_ind = FND_API.G_MISS_CHAR)
867 THEN
868 p_credit_ratings_rec.suit_ind := p_credit_ratings_rec.suit_judge_ind;
869 ELSIF nvl(p_credit_ratings_rec.suit_ind, FND_API.G_MISS_CHAR) <>
870 nvl(p_credit_ratings_rec.suit_judge_ind, FND_API.G_MISS_CHAR) THEN
871 p_credit_ratings_rec.suit_judge_ind := p_credit_ratings_rec.suit_ind;
872 END IF;
873
874 -- Bug 1428526: Should pass updated credit rating info. to caller.
875 -- Make sure to use values in p_credit_ratings_rec.* when calling insert table
876 -- handler. Need to update p_credit_ratings_rec first.
877
878 p_credit_ratings_rec.credit_rating_id := l_credit_rating_id;
879 /*
880 -- call table handler to insert a row
881 HZ_CREDIT_RATINGS_PKG.INSERT_ROW(
882 x_rowid => l_rowid,
883 x_credit_rating_id => p_credit_ratings_rec.credit_rating_id,
884 x_description => p_credit_ratings_rec.description,
885 x_party_id => p_credit_ratings_rec.party_id,
886 x_rating => p_credit_ratings_rec.rating,
887 x_rated_as_of_date => p_credit_ratings_rec.rated_as_of_date,
888 x_rating_organization => p_credit_ratings_rec.rating_organization,
889 x_created_by => hz_utility_pub.created_by,
890 x_creation_date => hz_utility_pub.creation_date,
891 x_last_update_login => hz_utility_pub.last_update_login,
892 x_last_update_date => hz_utility_pub.last_update_date,
893 x_last_updated_by => hz_utility_pub.last_updated_by,
894 x_request_id => hz_utility_pub.request_id,
895 x_program_application_id => hz_utility_pub.program_application_id,
896 x_program_id => hz_utility_pub.program_id,
897 x_wh_update_date => p_credit_ratings_rec.wh_update_date,
898 x_comments => p_credit_ratings_rec.comments,
899 x_det_history_ind => p_credit_ratings_rec.det_history_ind,
900 x_fincl_embt_ind => p_credit_ratings_rec.fincl_embt_ind,
901 x_criminal_proceeding_ind => p_credit_ratings_rec.criminal_proceeding_ind,
902 x_suit_judge_ind => p_credit_ratings_rec.suit_judge_ind,
903 x_claims_ind => p_credit_ratings_rec.claims_ind,
904 x_secured_flng_ind => p_credit_ratings_rec.secured_flng_ind,
905 x_fincl_lgl_event_ind => p_credit_ratings_rec.fincl_lgl_event_ind,
906 x_disaster_ind => p_credit_ratings_rec.disaster_ind,
907 x_oprg_spec_evnt_ind => p_credit_ratings_rec.oprg_spec_evnt_ind,
908 x_other_spec_evnt_ind => p_credit_ratings_rec.other_spec_evnt_ind,
909 x_content_source_type => p_credit_ratings_rec.content_source_type,
910 x_program_update_date => hz_utility_pub.program_update_date,
911 x_status => p_credit_ratings_rec.status,
912 x_avg_high_credit => p_credit_ratings_rec.avg_high_credit,
913 x_credit_score => p_credit_ratings_rec.credit_score,
914 x_credit_score_age => p_credit_ratings_rec.credit_score_age,
915 x_credit_score_class => p_credit_ratings_rec.credit_score_class,
916 x_credit_score_commentary => p_credit_ratings_rec.credit_score_commentary,
917 x_credit_score_commentary2 => p_credit_ratings_rec.credit_score_commentary2,
918 x_credit_score_commentary3 => p_credit_ratings_rec.credit_score_commentary3,
919 x_credit_score_commentary4 => p_credit_ratings_rec.credit_score_commentary4,
920 x_credit_score_commentary5 => p_credit_ratings_rec.credit_score_commentary5,
921 x_credit_score_commentary6 => p_credit_ratings_rec.credit_score_commentary6,
922 x_credit_score_commentary7 => p_credit_ratings_rec.credit_score_commentary7,
923 x_credit_score_commentary8 => p_credit_ratings_rec.credit_score_commentary8,
924 x_credit_score_commentary9 => p_credit_ratings_rec.credit_score_commentary9,
925 x_credit_score_commentary10 => p_credit_ratings_rec.credit_score_commentary10,
926 x_credit_score_date => p_credit_ratings_rec.credit_score_date,
927 x_credit_score_incd_default => p_credit_ratings_rec.credit_score_incd_default,
928 x_credit_score_natl_percentile => p_credit_ratings_rec.credit_score_natl_percentile,
929 x_failure_score => p_credit_ratings_rec.failure_score,
930 x_failure_score_age => p_credit_ratings_rec.failure_score_age,
931 x_failure_score_class => p_credit_ratings_rec.failure_score_class,
932 x_failure_score_commentary => p_credit_ratings_rec.failure_score_commentary,
933 x_failure_score_commentary2 => p_credit_ratings_rec.failure_score_commentary2,
934 x_failure_score_commentary3 => p_credit_ratings_rec.failure_score_commentary3,
935 x_failure_score_commentary4 => p_credit_ratings_rec.failure_score_commentary4,
936 x_failure_score_commentary5 => p_credit_ratings_rec.failure_score_commentary5,
937 x_failure_score_commentary6 => p_credit_ratings_rec.failure_score_commentary6,
938 x_failure_score_commentary7 => p_credit_ratings_rec.failure_score_commentary7,
939 x_failure_score_commentary8 => p_credit_ratings_rec.failure_score_commentary8,
940 x_failure_score_commentary9 => p_credit_ratings_rec.failure_score_commentary9,
941 x_failure_score_commentary10 => p_credit_ratings_rec.failure_score_commentary10,
942 x_failure_score_date => p_credit_ratings_rec.failure_score_date,
943 x_failure_score_incd_default => p_credit_ratings_rec.failure_score_incd_default,
944 x_failure_score_natnl_pcntl => p_credit_ratings_rec.failure_score_natnl_percentile,
945 x_failure_score_override_code => p_credit_ratings_rec.failure_score_override_code,
946 x_global_failure_score => p_credit_ratings_rec.global_failure_score,
947 x_debarment_ind => p_credit_ratings_rec.debarment_ind,
948 x_debarments_count => p_credit_ratings_rec.debarments_count,
949 x_debarments_date => p_credit_ratings_rec.debarments_date,
950 x_high_credit => p_credit_ratings_rec.high_credit,
951 x_maximum_credit_currency_code => p_credit_ratings_rec.maximum_credit_currency_code,
952 x_maximum_credit_rcmd => p_credit_ratings_rec.maximum_credit_rcmd ,
953 x_paydex_norm => p_credit_ratings_rec.paydex_norm,
954 x_paydex_score => p_credit_ratings_rec.paydex_score,
955 x_paydex_three_months_ago => p_credit_ratings_rec.paydex_three_months_ago,
956 x_credit_score_override_code => p_credit_ratings_rec.credit_score_override_code,
957 x_cr_scr_clas_expl => p_credit_ratings_rec.cr_scr_clas_expl,
958 x_low_rng_delq_scr => p_credit_ratings_rec.low_rng_delq_scr,
959 x_high_rng_delq_scr => p_credit_ratings_rec.high_rng_delq_scr,
960 x_delq_pmt_rng_prcnt => p_credit_ratings_rec.delq_pmt_rng_prcnt,
961 x_delq_pmt_pctg_for_all_firms => p_credit_ratings_rec.delq_pmt_pctg_for_all_firms,
962 x_num_trade_experiences => p_credit_ratings_rec.num_trade_experiences,
963 x_paydex_firm_days => p_credit_ratings_rec.paydex_firm_days,
964 x_paydex_firm_comment => p_credit_ratings_rec.paydex_firm_comment,
965 x_paydex_industry_days => p_credit_ratings_rec.paydex_industry_days,
966 x_paydex_industry_comment => p_credit_ratings_rec.paydex_industry_comment,
967 x_paydex_comment => p_credit_ratings_rec.paydex_comment,
968 x_suit_ind => p_credit_ratings_rec.suit_ind,
969 x_lien_ind => p_credit_ratings_rec.lien_ind,
970 x_judgement_ind => p_credit_ratings_rec.judgement_ind,
971 x_bankruptcy_ind => p_credit_ratings_rec.bankruptcy_ind,
972 x_no_trade_ind => p_credit_ratings_rec.no_trade_ind,
973 x_prnt_hq_bkcy_ind => p_credit_ratings_rec.prnt_hq_bkcy_ind,
974 x_num_prnt_bkcy_filing => p_credit_ratings_rec.num_prnt_bkcy_filing,
975 x_prnt_bkcy_filg_type => p_credit_ratings_rec.prnt_bkcy_filg_type,
976 x_prnt_bkcy_filg_chapter => p_credit_ratings_rec.prnt_bkcy_filg_chapter,
977 x_prnt_bkcy_filg_date => p_credit_ratings_rec.prnt_bkcy_filg_date,
978 x_num_prnt_bkcy_convs => p_credit_ratings_rec.num_prnt_bkcy_convs,
979 x_prnt_bkcy_conv_date => p_credit_ratings_rec.prnt_bkcy_conv_date,
980 x_prnt_bkcy_chapter_conv => p_credit_ratings_rec.prnt_bkcy_chapter_conv,
981 x_slow_trade_expl => p_credit_ratings_rec.slow_trade_expl,
982 x_negv_pmt_expl => p_credit_ratings_rec.negv_pmt_expl,
983 x_pub_rec_expl => p_credit_ratings_rec.pub_rec_expl,
984 x_business_discontinued => p_credit_ratings_rec.business_discontinued,
985 x_spcl_event_comment => p_credit_ratings_rec.spcl_event_comment ,
986 x_num_spcl_event => p_credit_ratings_rec.num_spcl_event,
987 x_spcl_event_update_date => p_credit_ratings_rec.spcl_event_update_date,
988 x_spcl_evnt_txt => p_credit_ratings_rec.spcl_evnt_txt,
989 x_actual_content_source => p_credit_ratings_rec.actual_content_source
990 );
991 */
992 --Call to User-Hook post Processing Procedure
993 /*
994 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' AND
995 -- Bug 2197181: Modifed the condition
996 g_cre_is_datasource_selected = 'Y'
997 THEN
998 hz_party_info_crmhk.create_credit_ratings_post(
999 p_credit_ratings_rec,
1000 x_return_status,
1001 x_msg_count,
1002 x_msg_data);
1003
1004 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1005 RAISE FND_API.G_EXC_ERROR;
1006 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1007 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
1008 FND_MESSAGE.SET_TOKEN('PROCEDURE','HZ_PARTY_INFO_CRMHK.CREATE_CREDIT_RATINGS_POST');
1009 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1010 FND_MSG_PUB.ADD;
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013 END IF;
1014 */
1015
1016 END do_create_credit_ratings;
1017
1018 /*===========================================================================+
1019 | PROCEDURE
1020 | do_update_credit_ratings
1021 |
1022 | DESCRIPTION
1023 | Updates credit ratings.
1024 |
1025 | SCOPE - PRIVATE
1026 |
1027 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1028 |
1029 | ARGUMENTS : IN:
1030 | OUT:
1031 | IN/ OUT:
1032 | p_credit_ratings_rec
1033 | p_last_update_date
1034 | x_return_status
1035 |
1036 | RETURNS : NONE
1037 |
1038 | NOTES
1039 |
1040 | MODIFICATION HISTORY
1041 | Jianying Huang 06-OCT-00 Bug 1428526: make *_rec parameters as
1042 | 'IN OUT' in order to pass the changed record
1043 | to caller.
1044 |
1045 +===========================================================================*/
1046
1047 procedure do_update_credit_ratings(
1048 p_credit_ratings_rec IN OUT NOCOPY credit_ratings_rec_type,
1049 p_last_update_date IN OUT NOCOPY DATE,
1050 x_return_status IN OUT NOCOPY VARCHAR2
1051 ) IS
1052
1053 l_rowid ROWID := NULL;
1054 l_last_update_date DATE;
1055 x_msg_count NUMBER;
1056 x_msg_data VARCHAR2(2000);
1057
1058 BEGIN
1059
1060 -- check required fields:
1061 IF p_credit_ratings_rec.credit_rating_id is NULL OR
1062 p_credit_ratings_rec.credit_rating_id = FND_API.G_MISS_NUM
1063 THEN
1064 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1065 FND_MESSAGE.SET_TOKEN('COLUMN', 'credit_rating_id');
1066 FND_MSG_PUB.ADD;
1067 RAISE FND_API.G_EXC_ERROR;
1068 END IF;
1069
1070 IF p_last_update_date IS NULL OR
1071 p_last_update_date = FND_API.G_MISS_DATE
1072 THEN
1073 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1074 FND_MESSAGE.SET_TOKEN('COLUMN', 'p_last_update_date');
1075 FND_MSG_PUB.ADD;
1076 RAISE FND_API.G_EXC_ERROR;
1077 END IF;
1078
1079 BEGIN
1080 -- check last update date.
1081 -- Bug 2197181: selecting actual_content_source for mix-n-match project.
1082
1083 SELECT rowid, last_update_date
1084 INTO l_rowid, l_last_update_date
1085 FROM HZ_CREDIT_RATINGS
1086 WHERE credit_rating_id = p_credit_ratings_rec.credit_rating_id
1087 AND to_char(last_update_date, 'DD-MON-YYYY HH:MI:SS') =
1088 to_char(p_last_update_date, 'DD-MON-YYYY HH:MI:SS')
1089 FOR UPDATE NOWAIT;
1090
1091 EXCEPTION
1092 WHEN NO_DATA_FOUND THEN
1093 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1094 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_CREDIT_RATINGS');
1095 FND_MSG_PUB.ADD;
1096 RAISE FND_API.G_EXC_ERROR;
1097 END;
1098
1099 --Call to User-Hook pre Processing Procedure
1100 --Bug 1363124: validation#3 of content_source_type
1101 /*
1102 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' AND
1103 -- Bug 2197181: Modifed the condition
1104 g_cre_is_datasource_selected = 'Y'
1105 THEN
1106 hz_party_info_crmhk.update_credit_ratings_pre(
1107 p_credit_ratings_rec,
1108 x_return_status,
1109 x_msg_count,
1110 x_msg_data);
1111
1112 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1113 RAISE FND_API.G_EXC_ERROR;
1114 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1115 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
1116 FND_MESSAGE.SET_TOKEN('PROCEDURE','HZ_PARTY_INFO_CRMHK.UPDATE_CREDIT_RATINGS_PRE');
1117 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1118 FND_MSG_PUB.ADD;
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121 END IF;
1122 */
1123
1124 -- validate credit rating record
1125 HZ_PARTY_INFO_VAL.validate_credit_ratings(
1126 p_credit_ratings_rec, 'U', x_return_status);
1127
1128 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1129 RAISE FND_API.G_EXC_ERROR;
1130 END IF;
1131
1132 -- sync obsolete column suit_judge_ind with new column suit_ind
1133 IF (p_credit_ratings_rec.suit_ind is NULL AND
1134 p_credit_ratings_rec.suit_judge_ind is not null ) OR
1135 (p_credit_ratings_rec.suit_ind is not NULL AND
1136 p_credit_ratings_rec.suit_ind <> FND_API.G_MISS_CHAR AND
1137 (p_credit_ratings_rec.suit_judge_ind is null OR
1138 (p_credit_ratings_rec.suit_judge_ind is not null AND
1139 p_credit_ratings_rec.suit_ind <> p_credit_ratings_rec.suit_judge_ind ) ) )
1140 THEN
1141 p_credit_ratings_rec.suit_judge_ind := p_credit_ratings_rec.suit_ind;
1142 ELSIF (p_credit_ratings_rec.suit_ind is not NULL AND
1143 p_credit_ratings_rec.suit_ind = FND_API.G_MISS_CHAR ) AND
1144 (p_credit_ratings_rec.suit_judge_ind is null OR
1145 (p_credit_ratings_rec.suit_judge_ind is not null AND
1146 p_credit_ratings_rec.suit_judge_ind <> FND_API.G_MISS_CHAR) )
1147 THEN
1148 p_credit_ratings_rec.suit_ind := p_credit_ratings_rec.suit_judge_ind;
1149 END IF;
1150
1151 -- pass back the last update date
1152 p_last_update_date := hz_utility_pub.LAST_UPDATE_DATE;
1153
1154 -- Bug 1428526: Should pass updated credit rating info. to caller.
1155 -- Make sure to use values in p_credit_ratings_rec.* when calling update table
1156 -- handler. Need to update p_credit_ratings_rec first.
1157 NULL;
1158 /*
1159 -- call table handler to update a row
1160 HZ_CREDIT_RATINGS_PKG.UPDATE_ROW(
1161 x_rowid => l_rowid,
1162 x_credit_rating_id => p_credit_ratings_rec.credit_rating_id,
1163 x_description => p_credit_ratings_rec.description,
1164 x_party_id => p_credit_ratings_rec.party_id,
1165 x_rating => p_credit_ratings_rec.rating,
1166 x_rated_as_of_date => p_credit_ratings_rec.rated_as_of_date,
1167 x_rating_organization => p_credit_ratings_rec.rating_organization,
1168 x_created_by => fnd_api.g_miss_num,
1169 x_creation_date => fnd_api.g_miss_date,
1170 x_last_update_login => hz_utility_pub.last_update_login,
1171 x_last_update_date => p_last_update_date,
1172 x_last_updated_by => hz_utility_pub.last_updated_by,
1173 x_request_id => hz_utility_pub.request_id,
1174 x_program_application_id => hz_utility_pub.program_application_id,
1175 x_program_id => hz_utility_pub.program_id,
1176 x_wh_update_date => p_credit_ratings_rec.wh_update_date,
1177 x_comments => p_credit_ratings_rec.comments,
1178 x_det_history_ind => p_credit_ratings_rec.det_history_ind,
1179 x_fincl_embt_ind => p_credit_ratings_rec.fincl_embt_ind,
1180 x_criminal_proceeding_ind => p_credit_ratings_rec.criminal_proceeding_ind,
1181 x_suit_judge_ind => p_credit_ratings_rec.suit_judge_ind,
1182 x_claims_ind => p_credit_ratings_rec.claims_ind,
1183 x_secured_flng_ind => p_credit_ratings_rec.secured_flng_ind,
1184 x_fincl_lgl_event_ind => p_credit_ratings_rec.fincl_lgl_event_ind,
1185 x_disaster_ind => p_credit_ratings_rec.disaster_ind,
1186 x_oprg_spec_evnt_ind => p_credit_ratings_rec.oprg_spec_evnt_ind,
1187 x_other_spec_evnt_ind => p_credit_ratings_rec.other_spec_evnt_ind,
1188 -- Bug 2197181 : content_source_type is obsolete and it is non-updateable.
1189 x_content_source_type => fnd_api.g_miss_char,
1190 x_program_update_date => hz_utility_pub.program_update_date,
1191 x_status => p_credit_ratings_rec.status,
1192 x_avg_high_credit => p_credit_ratings_rec.avg_high_credit,
1193 x_credit_score => p_credit_ratings_rec.credit_score,
1194 x_credit_score_age => p_credit_ratings_rec.credit_score_age,
1195 x_credit_score_class => p_credit_ratings_rec.credit_score_class,
1196 x_credit_score_commentary => p_credit_ratings_rec.credit_score_commentary,
1197 x_credit_score_commentary2 => p_credit_ratings_rec.credit_score_commentary2,
1198 x_credit_score_commentary3 => p_credit_ratings_rec.credit_score_commentary3,
1199 x_credit_score_commentary4 => p_credit_ratings_rec.credit_score_commentary4,
1200 x_credit_score_commentary5 => p_credit_ratings_rec.credit_score_commentary5,
1201 x_credit_score_commentary6 => p_credit_ratings_rec.credit_score_commentary6,
1202 x_credit_score_commentary7 => p_credit_ratings_rec.credit_score_commentary7,
1203 x_credit_score_commentary8 => p_credit_ratings_rec.credit_score_commentary8,
1204 x_credit_score_commentary9 => p_credit_ratings_rec.credit_score_commentary9,
1205 x_credit_score_commentary10 => p_credit_ratings_rec.credit_score_commentary10,
1206 x_credit_score_date => p_credit_ratings_rec.credit_score_date,
1207 x_credit_score_incd_default => p_credit_ratings_rec.credit_score_incd_default,
1208 x_credit_score_natl_percentile => p_credit_ratings_rec.credit_score_natl_percentile,
1209 x_failure_score => p_credit_ratings_rec.failure_score,
1210 x_failure_score_age => p_credit_ratings_rec.failure_score_age,
1211 x_failure_score_class => p_credit_ratings_rec.failure_score_class,
1212 x_failure_score_commentary => p_credit_ratings_rec.failure_score_commentary,
1213 x_failure_score_commentary2 => p_credit_ratings_rec.failure_score_commentary2,
1214 x_failure_score_commentary3 => p_credit_ratings_rec.failure_score_commentary3,
1215 x_failure_score_commentary4 => p_credit_ratings_rec.failure_score_commentary4,
1216 x_failure_score_commentary5 => p_credit_ratings_rec.failure_score_commentary5,
1217 x_failure_score_commentary6 => p_credit_ratings_rec.failure_score_commentary6,
1218 x_failure_score_commentary7 => p_credit_ratings_rec.failure_score_commentary7,
1219 x_failure_score_commentary8 => p_credit_ratings_rec.failure_score_commentary8,
1220 x_failure_score_commentary9 => p_credit_ratings_rec.failure_score_commentary9,
1221 x_failure_score_commentary10 => p_credit_ratings_rec.failure_score_commentary10,
1222 x_failure_score_date => p_credit_ratings_rec.failure_score_date,
1223 x_failure_score_incd_default => p_credit_ratings_rec.failure_score_incd_default,
1224 x_failure_score_natnl_pcntl => p_credit_ratings_rec.failure_score_natnl_percentile,
1225 x_failure_score_override_code => p_credit_ratings_rec.failure_score_override_code,
1226 x_global_failure_score => p_credit_ratings_rec.global_failure_score,
1227 x_debarment_ind => p_credit_ratings_rec.debarment_ind,
1228 x_debarments_count => p_credit_ratings_rec.debarments_count,
1229 x_debarments_date => p_credit_ratings_rec.debarments_date,
1230 x_high_credit => p_credit_ratings_rec.high_credit,
1231 x_maximum_credit_currency_code => p_credit_ratings_rec.maximum_credit_currency_code,
1232 x_maximum_credit_rcmd => p_credit_ratings_rec.maximum_credit_rcmd,
1233 x_paydex_norm => p_credit_ratings_rec.paydex_norm,
1234 x_paydex_score => p_credit_ratings_rec.paydex_score,
1235 x_paydex_three_months_ago => p_credit_ratings_rec.paydex_three_months_ago,
1236 x_credit_score_override_code => p_credit_ratings_rec.credit_score_override_code,
1237 x_cr_scr_clas_expl => p_credit_ratings_rec.cr_scr_clas_expl,
1238 x_low_rng_delq_scr => p_credit_ratings_rec.low_rng_delq_scr,
1239 x_high_rng_delq_scr => p_credit_ratings_rec.high_rng_delq_scr,
1240 x_delq_pmt_rng_prcnt => p_credit_ratings_rec.delq_pmt_rng_prcnt,
1241 x_delq_pmt_pctg_for_all_firms => p_credit_ratings_rec.delq_pmt_pctg_for_all_firms,
1242 x_num_trade_experiences => p_credit_ratings_rec.num_trade_experiences,
1243 x_paydex_firm_days => p_credit_ratings_rec.paydex_firm_days,
1244 x_paydex_firm_comment => p_credit_ratings_rec.paydex_firm_comment,
1245 x_paydex_industry_days => p_credit_ratings_rec.paydex_industry_days,
1246 x_paydex_industry_comment => p_credit_ratings_rec.paydex_industry_comment,
1247 x_paydex_comment => p_credit_ratings_rec.paydex_comment,
1248 x_suit_ind => p_credit_ratings_rec.suit_ind,
1249 x_lien_ind => p_credit_ratings_rec.lien_ind,
1250 x_judgement_ind => p_credit_ratings_rec.judgement_ind,
1251 x_bankruptcy_ind => p_credit_ratings_rec.bankruptcy_ind,
1252 x_no_trade_ind => p_credit_ratings_rec.no_trade_ind,
1253 x_prnt_hq_bkcy_ind => p_credit_ratings_rec.prnt_hq_bkcy_ind,
1254 x_num_prnt_bkcy_filing => p_credit_ratings_rec.num_prnt_bkcy_filing,
1255 x_prnt_bkcy_filg_type => p_credit_ratings_rec.prnt_bkcy_filg_type,
1256 x_prnt_bkcy_filg_chapter => p_credit_ratings_rec.prnt_bkcy_filg_chapter,
1257 x_prnt_bkcy_filg_date => p_credit_ratings_rec.prnt_bkcy_filg_date,
1258 x_num_prnt_bkcy_convs => p_credit_ratings_rec.num_prnt_bkcy_convs,
1259 x_prnt_bkcy_conv_date => p_credit_ratings_rec.prnt_bkcy_conv_date,
1260 x_prnt_bkcy_chapter_conv => p_credit_ratings_rec.prnt_bkcy_chapter_conv,
1261 x_slow_trade_expl => p_credit_ratings_rec.slow_trade_expl,
1262 x_negv_pmt_expl => p_credit_ratings_rec.negv_pmt_expl,
1263 x_pub_rec_expl => p_credit_ratings_rec.pub_rec_expl,
1264 x_business_discontinued => p_credit_ratings_rec.business_discontinued,
1265 x_spcl_event_comment => p_credit_ratings_rec.spcl_event_comment ,
1266 x_num_spcl_event => p_credit_ratings_rec.num_spcl_event,
1267 x_spcl_event_update_date => p_credit_ratings_rec.spcl_event_update_date,
1268 x_spcl_evnt_txt => p_credit_ratings_rec.spcl_evnt_txt,
1269 x_actual_content_source => p_credit_ratings_rec.actual_content_source
1270 );
1271 */
1272 /*
1273 --Call to User-Hook post Processing Procedure
1274 IF fnd_profile.value('HZ_EXECUTE_API_CALLOUTS') = 'Y' AND
1275 -- Bug 2197181: Modifed the condition
1276 g_cre_is_datasource_selected = 'Y'
1277 THEN
1278 hz_party_info_crmhk.update_credit_ratings_post(
1279 p_credit_ratings_rec,
1280 x_return_status,
1281 x_msg_count,
1282 x_msg_data);
1283
1284 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1285 RAISE FND_API.G_EXC_ERROR;
1286 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1287 FND_MESSAGE.SET_NAME('AR', 'HZ_API_HOOK_ERROR');
1288 FND_MESSAGE.SET_TOKEN('PROCEDURE','HZ_PARTY_INFO_CRMHK.UPDATE_CREDIT_RATINGS_POST');
1289 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1290 FND_MSG_PUB.ADD;
1291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1292 END IF;
1293 END IF;
1294 */
1295
1296 END do_update_credit_ratings;
1297
1298 /*===========================================================================+
1299 | PROCEDURE
1300 | do_create_financial_profile
1301 |
1302 | DESCRIPTION
1303 | Creates financial profile.
1304 |
1305 | SCOPE - PRIVATE
1306 |
1307 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1308 |
1309 | ARGUMENTS : IN:
1310 | OUT:
1311 | x_financial_profile_id
1312 | IN/ OUT:
1313 | p_financial_profile_rec
1314 | x_return_status
1315 |
1316 | RETURNS : NONE
1317 |
1318 | NOTES
1319 |
1320 | MODIFICATION HISTORY
1321 | Jianying Huang 06-OCT-00 Bug 1428526: make *_rec parameters as
1322 | 'IN OUT' in order to pass the changed record
1323 | to caller.
1324 |
1325 +===========================================================================*/
1326
1327 procedure do_create_financial_profile(
1328 p_financial_profile_rec IN OUT NOCOPY financial_profile_rec_type,
1329 x_financial_profile_id OUT NOCOPY NUMBER,
1330 x_return_status IN OUT NOCOPY VARCHAR2
1331 ) IS
1332 l_financial_profile_id NUMBER:= p_financial_profile_rec.financial_profile_id;
1333 l_rowid ROWID := NULL;
1334 l_count NUMBER;
1335 BEGIN
1336 -- if financial_profile_id is NULL, then generate PK.
1337
1338 IF l_financial_profile_id is NULL OR
1339 l_financial_profile_id = FND_API.G_MISS_NUM THEN
1340
1341 l_count := 1;
1342 WHILE l_count > 0 LOOP
1343 SELECT hz_financial_profile_s.nextval
1344 INTO l_financial_profile_id FROM dual;
1345
1346 SELECT count(*)
1347 INTO l_count
1348 FROM hz_financial_profile
1349 WHERE financial_profile_id = l_financial_profile_id;
1350 END LOOP;
1351 ELSE
1352 l_count := 0;
1353 SELECT count(*)
1354 INTO l_count
1355 FROM hz_financial_profile
1356 WHERE financial_profile_id = l_financial_profile_id;
1357
1358 if l_count > 0 then
1359 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
1360 FND_MESSAGE.SET_TOKEN('COLUMN', 'financial_profile_id');
1361 FND_MSG_PUB.ADD;
1362 RAISE FND_API.G_EXC_ERROR;
1363 end if;
1364
1365 END IF;
1366
1367 x_financial_profile_id := l_financial_profile_id;
1368
1369 -- validate credit ratings record
1370
1371 HZ_PARTY_INFO_VAL.validate_financial_profile(p_financial_profile_rec,'C',
1372 x_return_status);
1373
1374 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1375 RAISE FND_API.G_EXC_ERROR;
1376 END IF;
1377
1378 -- Bug 1428526: Should pass updated financial profile info. to caller.
1379 -- Make sure to use values in p_financial_profile_rec.* when calling insert table
1380 -- handler. Need to update p_financial_profile_rec first.
1381 p_financial_profile_rec.financial_profile_id := l_financial_profile_id;
1382 -- call table handler to insert a row
1383 HZ_FINANCIAL_PROFILE_PKG.INSERT_ROW(
1384 X_Rowid => l_rowid,
1385 X_FINANCIAL_PROFILE_ID => p_financial_profile_rec.financial_profile_id,
1386 X_ACCESS_AUTHORITY_DATE => p_financial_profile_rec.ACCESS_AUTHORITY_DATE,
1387 X_ACCESS_AUTHORITY_GRANTED => p_financial_profile_rec.ACCESS_AUTHORITY_GRANTED,
1388 X_BALANCE_AMOUNT => p_financial_profile_rec.BALANCE_AMOUNT,
1389 X_BALANCE_VERIFIED_ON_DATE => p_financial_profile_rec.BALANCE_VERIFIED_ON_DATE,
1390 X_FINANCIAL_ACCOUNT_NUMBER => p_financial_profile_rec.FINANCIAL_ACCOUNT_NUMBER,
1391 X_FINANCIAL_ACCOUNT_TYPE => p_financial_profile_rec.FINANCIAL_ACCOUNT_TYPE,
1392 X_FINANCIAL_ORG_TYPE => p_financial_profile_rec.FINANCIAL_ORG_TYPE,
1393 X_FINANCIAL_ORGANIZATION_NAME => p_financial_profile_rec.FINANCIAL_ORGANIZATION_NAME,
1394 X_CREATED_BY => hz_utility_pub.CREATED_BY,
1395 X_CREATION_DATE => hz_utility_pub.CREATION_DATE,
1396 X_PARTY_ID => p_financial_profile_rec.PARTY_ID,
1397 X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN,
1398 X_LAST_UPDATE_DATE => hz_utility_pub.LAST_UPDATE_DATE,
1399 X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
1400 X_REQUEST_ID => hz_utility_pub.REQUEST_ID,
1401 X_PROGRAM_APPLICATION_ID => hz_utility_pub.PROGRAM_APPLICATION_ID,
1402 X_PROGRAM_ID => hz_utility_pub.PROGRAM_ID,
1403 X_PROGRAM_UPDATE_DATE => hz_utility_pub.PROGRAM_UPDATE_DATE,
1404 X_WH_UPDATE_DATE => p_financial_profile_rec.WH_UPDATE_DATE,
1405 X_STATUS =>p_financial_profile_rec.STATUS
1406 );
1407
1408 END do_create_financial_profile;
1409
1410 /*===========================================================================+
1411 | PROCEDURE
1412 | do_update_financial_profile
1413 |
1414 | DESCRIPTION
1415 | Updates financial profile.
1416 |
1417 | SCOPE - PRIVATE
1418 |
1419 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1420 |
1421 | ARGUMENTS : IN:
1422 | OUT:
1423 | IN/ OUT:
1424 | p_financial_profile_rec
1425 | p_last_update_date
1426 | x_return_status
1427 |
1428 | RETURNS : NONE
1429 |
1430 | NOTES
1431 |
1432 | MODIFICATION HISTORY
1433 | Jianying Huang 06-OCT-00 Bug 1428526: make *_rec parameters as
1434 | 'IN OUT' in order to pass the changed record
1435 | to caller.
1436 |
1437 +===========================================================================*/
1438
1439 procedure do_update_financial_profile(
1440 p_financial_profile_rec IN OUT NOCOPY financial_profile_rec_type,
1441 p_last_update_date IN OUT NOCOPY DATE,
1442 x_return_status IN OUT NOCOPY VARCHAR2
1443 ) IS
1444 l_rowid ROWID := NULL;
1445 l_last_update_date DATE;
1446 BEGIN
1447 -- check required fields:
1448 IF p_financial_profile_rec.financial_profile_id is NULL OR
1449 p_financial_profile_rec.financial_profile_id = FND_API.G_MISS_NUM THEN
1450
1451 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1452 FND_MESSAGE.SET_TOKEN('COLUMN', 'financial_profile_id');
1453 FND_MSG_PUB.ADD;
1454 RAISE FND_API.G_EXC_ERROR;
1455
1456 END IF;
1457
1458 IF p_last_update_date IS NULL OR
1459 p_last_update_date = FND_API.G_MISS_DATE
1460 THEN
1461 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1462 FND_MESSAGE.SET_TOKEN('COLUMN', 'p_last_update_date');
1463 FND_MSG_PUB.ADD;
1464 RAISE FND_API.G_EXC_ERROR;
1465 END IF;
1466
1467 begin
1468 -- check last update date.
1469 SELECT rowid, last_update_date
1470 INTO l_rowid, l_last_update_date
1471 FROM HZ_FINANCIAL_PROFILE
1472 where financial_profile_id = p_financial_profile_rec.financial_profile_id
1473 AND to_char(last_update_date, 'DD-MON-YYYY HH:MI:SS') =
1474 to_char(p_last_update_date, 'DD-MON-YYYY HH:MI:SS')
1475 FOR UPDATE NOWAIT;
1476
1477 EXCEPTION WHEN NO_DATA_FOUND THEN
1478 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1479 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_FINANCIAL_PROFILE');
1480 FND_MSG_PUB.ADD;
1481 RAISE FND_API.G_EXC_ERROR;
1482 end;
1483 -- validate credit rating record
1484 HZ_PARTY_INFO_VAL.validate_financial_profile(p_financial_profile_rec,'U',
1485 x_return_status);
1486
1487 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1488 RAISE FND_API.G_EXC_ERROR;
1489 END IF;
1490
1491 -- pass back the last update date
1492 p_last_update_date := hz_utility_pub.LAST_UPDATE_DATE;
1493
1494 -- Bug 1428526: Should pass updated financial profile info. to caller.
1495 -- Make sure to use values in p_financial_profile_rec.* when calling update table
1496 -- handler. Need to update p_financial_profile_rec first.
1497 NULL;
1498 -- call table handler to update a row
1499 HZ_FINANCIAL_PROFILE_PKG.UPDATE_ROW(
1500 X_Rowid => l_rowid,
1501 X_FINANCIAL_PROFILE_ID => p_financial_profile_rec.FINANCIAL_PROFILE_ID,
1502 X_ACCESS_AUTHORITY_DATE => p_financial_profile_rec.ACCESS_AUTHORITY_DATE,
1503 X_ACCESS_AUTHORITY_GRANTED => p_financial_profile_rec.ACCESS_AUTHORITY_GRANTED,
1504 X_BALANCE_AMOUNT => p_financial_profile_rec.BALANCE_AMOUNT,
1505 X_BALANCE_VERIFIED_ON_DATE => p_financial_profile_rec.BALANCE_VERIFIED_ON_DATE,
1506 X_FINANCIAL_ACCOUNT_NUMBER => p_financial_profile_rec.FINANCIAL_ACCOUNT_NUMBER,
1507 X_FINANCIAL_ACCOUNT_TYPE => p_financial_profile_rec.FINANCIAL_ACCOUNT_TYPE,
1508 X_FINANCIAL_ORG_TYPE => p_financial_profile_rec.FINANCIAL_ORG_TYPE,
1509 X_FINANCIAL_ORGANIZATION_NAME => p_financial_profile_rec.FINANCIAL_ORGANIZATION_NAME,
1510 X_CREATED_BY => FND_API.G_MISS_NUM,
1511 X_CREATION_DATE => FND_API.G_MISS_DATE,
1512 X_PARTY_ID => p_financial_profile_rec.PARTY_ID,
1513 X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN,
1514 X_LAST_UPDATE_DATE => p_last_update_date,
1515 X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
1516 X_REQUEST_ID => hz_utility_pub.REQUEST_ID,
1517 X_PROGRAM_APPLICATION_ID => hz_utility_pub.PROGRAM_APPLICATION_ID,
1518 X_PROGRAM_ID => hz_utility_pub.PROGRAM_ID,
1519 X_PROGRAM_UPDATE_DATE => hz_utility_pub.PROGRAM_UPDATE_DATE,
1520 X_WH_UPDATE_DATE => p_financial_profile_rec.WH_UPDATE_DATE,
1521 X_STATUS => p_financial_profile_rec.STATUS
1522 );
1523
1524 END do_update_financial_profile;
1525
1526
1527 procedure get_current_credit_rating(
1528 p_api_version IN NUMBER,
1529 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
1530 p_credit_rating_id IN NUMBER,
1531 x_credit_ratings_rec OUT NOCOPY CREDIT_RATINGS_REC_TYPE,
1532 x_return_status IN OUT NOCOPY VARCHAR2,
1533 x_msg_count OUT NOCOPY NUMBER,
1534 x_msg_data OUT NOCOPY VARCHAR2
1535 ) IS
1536 l_api_name CONSTANT VARCHAR2(30) := 'get_current_credit_rating';
1537 l_api_version CONSTANT NUMBER := 1.0;
1538
1539 BEGIN
1540
1541 --Standard call to check for call compatibility.
1542 IF NOT FND_API.Compatible_API_Call(
1543 l_api_version,
1544 p_api_version,
1545 l_api_name,
1546 G_PKG_NAME)
1547 THEN
1548 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1549 END IF;
1550
1551 --Initialize message list if p_init_msg_list is set to TRUE.
1552 IF FND_API.to_Boolean(p_init_msg_list) THEN
1553 FND_MSG_PUB.initialize;
1554 END IF;
1555
1556 --Initialize API return status to success.
1557 x_return_status := FND_API.G_RET_STS_SUCCESS;
1558
1559 --Check whether primary key has been passed in.
1560 IF p_credit_rating_id IS NULL OR
1561 p_credit_rating_id = FND_API.G_MISS_NUM THEN
1562
1563 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1564 FND_MESSAGE.SET_TOKEN('COLUMN', 'credit_rating_id');
1565 FND_MSG_PUB.ADD;
1566 RAISE FND_API.G_EXC_ERROR;
1567 END IF;
1568
1569 BEGIN /* Just for select statement */
1570
1571 SELECT
1572 credit_rating_id,
1573 description,
1574 party_id,
1575 rating,
1576 rated_as_of_date,
1577 rating_organization,
1578 wh_update_date,
1579 comments,
1580 det_history_ind,
1581 fincl_embt_ind,
1582 criminal_proceeding_ind,
1583 suit_judge_ind,
1584 claims_ind,
1585 secured_flng_ind,
1586 fincl_lgl_event_ind,
1587 disaster_ind,
1588 oprg_spec_evnt_ind,
1589 other_spec_evnt_ind,
1590 content_source_type,
1591 status,
1592 avg_high_credit,
1593 credit_score ,
1594 credit_score_age,
1595 credit_score_class,
1596 credit_score_commentary,
1597 credit_score_commentary2,
1598 credit_score_commentary3,
1599 credit_score_commentary4,
1600 credit_score_commentary5,
1601 credit_score_commentary6,
1602 credit_score_commentary7,
1603 credit_score_commentary8,
1604 credit_score_commentary9,
1605 credit_score_commentary10,
1606 credit_score_date,
1607 credit_score_incd_default,
1608 credit_score_natl_percentile,
1609 failure_score,
1610 failure_score_age,
1611 failure_score_class,
1612 failure_score_commentary,
1613 failure_score_commentary2,
1614 failure_score_commentary3,
1615 failure_score_commentary4,
1616 failure_score_commentary5,
1617 failure_score_commentary6,
1618 failure_score_commentary7,
1619 failure_score_commentary8,
1620 failure_score_commentary9,
1621 failure_score_commentary10,
1622 failure_score_date,
1623 failure_score_incd_default,
1624 failure_score_natnl_percentile,
1625 failure_score_override_code,
1626 global_failure_score,
1627 debarment_ind,
1628 debarments_count,
1629 debarments_date,
1630 high_credit,
1631 maximum_credit_currency_code,
1632 maximum_credit_recommendation,
1633 paydex_norm,
1634 paydex_score,
1635 paydex_three_months_ago,
1636 credit_score_override_code,
1637 cr_scr_clas_expl,
1638 low_rng_delq_scr,
1639 high_rng_delq_scr,
1640 delq_pmt_rng_prcnt,
1641 delq_pmt_pctg_for_all_firms,
1642 num_trade_experiences,
1643 paydex_firm_days,
1644 paydex_firm_comment,
1645 paydex_industry_days,
1646 paydex_industry_comment,
1647 paydex_comment,
1648 suit_ind,
1649 lien_ind,
1650 judgement_ind,
1651 bankruptcy_ind,
1652 no_trade_ind,
1653 prnt_hq_bkcy_ind,
1654 num_prnt_bkcy_filing,
1655 prnt_bkcy_filg_type,
1656 prnt_bkcy_filg_chapter,
1657 prnt_bkcy_filg_date,
1658 num_prnt_bkcy_convs,
1659 prnt_bkcy_conv_date,
1660 prnt_bkcy_chapter_conv,
1661 slow_trade_expl,
1662 negv_pmt_expl,
1663 pub_rec_expl,
1664 business_discontinued,
1665 spcl_event_comment,
1666 num_spcl_event,
1667 spcl_event_update_date,
1668 spcl_evnt_txt,
1669 actual_content_source
1670 INTO
1671 x_credit_ratings_rec.credit_rating_id,
1672 x_credit_ratings_rec.description,
1673 x_credit_ratings_rec.party_id,
1674 x_credit_ratings_rec.rating,
1675 x_credit_ratings_rec.rated_as_of_date,
1676 x_credit_ratings_rec.rating_organization,
1677 x_credit_ratings_rec.wh_update_date,
1678 x_credit_ratings_rec.comments,
1679 x_credit_ratings_rec.det_history_ind,
1680 x_credit_ratings_rec.fincl_embt_ind,
1681 x_credit_ratings_rec.criminal_proceeding_ind,
1682 x_credit_ratings_rec.suit_judge_ind,
1683 x_credit_ratings_rec.claims_ind,
1684 x_credit_ratings_rec.secured_flng_ind,
1685 x_credit_ratings_rec.fincl_lgl_event_ind,
1686 x_credit_ratings_rec.disaster_ind,
1687 x_credit_ratings_rec.oprg_spec_evnt_ind,
1688 x_credit_ratings_rec.other_spec_evnt_ind,
1689 x_credit_ratings_rec.content_source_type,
1690 x_credit_ratings_rec.status,
1691 x_credit_ratings_rec.avg_high_credit,
1692 x_credit_ratings_rec.credit_score ,
1693 x_credit_ratings_rec.credit_score_age,
1694 x_credit_ratings_rec.credit_score_class,
1695 x_credit_ratings_rec.credit_score_commentary,
1696 x_credit_ratings_rec.credit_score_commentary2,
1697 x_credit_ratings_rec.credit_score_commentary3,
1698 x_credit_ratings_rec.credit_score_commentary4,
1699 x_credit_ratings_rec.credit_score_commentary5,
1700 x_credit_ratings_rec.credit_score_commentary6,
1701 x_credit_ratings_rec.credit_score_commentary7,
1702 x_credit_ratings_rec.credit_score_commentary8,
1703 x_credit_ratings_rec.credit_score_commentary9,
1704 x_credit_ratings_rec.credit_score_commentary10,
1705 x_credit_ratings_rec.credit_score_date,
1706 x_credit_ratings_rec.credit_score_incd_default,
1707 x_credit_ratings_rec.credit_score_natl_percentile,
1708 x_credit_ratings_rec.failure_score,
1709 x_credit_ratings_rec.failure_score_age,
1710 x_credit_ratings_rec.failure_score_class,
1711 x_credit_ratings_rec.failure_score_commentary,
1712 x_credit_ratings_rec.failure_score_commentary2,
1713 x_credit_ratings_rec.failure_score_commentary3,
1714 x_credit_ratings_rec.failure_score_commentary4,
1715 x_credit_ratings_rec.failure_score_commentary5,
1716 x_credit_ratings_rec.failure_score_commentary6,
1717 x_credit_ratings_rec.failure_score_commentary7,
1718 x_credit_ratings_rec.failure_score_commentary8,
1719 x_credit_ratings_rec.failure_score_commentary9,
1720 x_credit_ratings_rec.failure_score_commentary10,
1721 x_credit_ratings_rec.failure_score_date,
1722 x_credit_ratings_rec.failure_score_incd_default,
1723 x_credit_ratings_rec.failure_score_natnl_percentile,
1724 x_credit_ratings_rec.failure_score_override_code,
1725 x_credit_ratings_rec.global_failure_score,
1726 x_credit_ratings_rec.debarment_ind,
1727 x_credit_ratings_rec.debarments_count,
1728 x_credit_ratings_rec.debarments_date,
1729 x_credit_ratings_rec.high_credit,
1730 x_credit_ratings_rec.maximum_credit_currency_code,
1731 x_credit_ratings_rec.maximum_credit_rcmd,
1732 x_credit_ratings_rec.paydex_norm,
1733 x_credit_ratings_rec.paydex_score,
1734 x_credit_ratings_rec.paydex_three_months_ago,
1735 x_credit_ratings_rec.credit_score_override_code,
1736 x_credit_ratings_rec.cr_scr_clas_expl,
1737 x_credit_ratings_rec.low_rng_delq_scr,
1738 x_credit_ratings_rec.high_rng_delq_scr,
1739 x_credit_ratings_rec.delq_pmt_rng_prcnt,
1740 x_credit_ratings_rec.delq_pmt_pctg_for_all_firms,
1741 x_credit_ratings_rec.num_trade_experiences,
1742 x_credit_ratings_rec.paydex_firm_days,
1743 x_credit_ratings_rec.paydex_firm_comment,
1744 x_credit_ratings_rec.paydex_industry_days,
1745 x_credit_ratings_rec.paydex_industry_comment,
1746 x_credit_ratings_rec.paydex_comment,
1747 x_credit_ratings_rec.suit_ind,
1748 x_credit_ratings_rec.lien_ind,
1749 x_credit_ratings_rec.judgement_ind,
1750 x_credit_ratings_rec.bankruptcy_ind,
1751 x_credit_ratings_rec.no_trade_ind,
1752 x_credit_ratings_rec.prnt_hq_bkcy_ind,
1753 x_credit_ratings_rec.num_prnt_bkcy_filing,
1754 x_credit_ratings_rec.prnt_bkcy_filg_type,
1755 x_credit_ratings_rec.prnt_bkcy_filg_chapter,
1756 x_credit_ratings_rec.prnt_bkcy_filg_date,
1757 x_credit_ratings_rec.num_prnt_bkcy_convs,
1758 x_credit_ratings_rec.prnt_bkcy_conv_date,
1759 x_credit_ratings_rec.prnt_bkcy_chapter_conv,
1760 x_credit_ratings_rec.slow_trade_expl,
1761 x_credit_ratings_rec.negv_pmt_expl,
1762 x_credit_ratings_rec.pub_rec_expl,
1763 x_credit_ratings_rec.business_discontinued,
1764 x_credit_ratings_rec.spcl_event_comment,
1765 x_credit_ratings_rec.num_spcl_event,
1766 x_credit_ratings_rec.spcl_event_update_date,
1767 x_credit_ratings_rec.spcl_evnt_txt,
1768 x_credit_ratings_rec.actual_content_source
1769
1770 FROM hz_credit_ratings
1771 WHERE credit_rating_id = p_credit_rating_id;
1772
1773 EXCEPTION
1774 WHEN NO_DATA_FOUND THEN
1775 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1776 FND_MESSAGE.SET_TOKEN('RECORD', 'credit rating');
1777 FND_MESSAGE.SET_TOKEN('VALUE', to_char(p_credit_rating_id));
1778 FND_MSG_PUB.ADD;
1779 RAISE FND_API.G_EXC_ERROR;
1780 END;
1781 --Standard call to get message count and if count is 1, get message info.
1782 FND_MSG_PUB.Count_And_Get(
1783 p_encoded => FND_API.G_FALSE,
1784 p_count => x_msg_count,
1785 p_data => x_msg_data);
1786
1787 EXCEPTION
1788 WHEN FND_API.G_EXC_ERROR THEN
1789 x_return_status := FND_API.G_RET_STS_ERROR;
1790 FND_MSG_PUB.Count_And_Get(
1791 p_encoded => FND_API.G_FALSE,
1792 p_count => x_msg_count,
1793 p_data => x_msg_data);
1794
1795 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1797 FND_MSG_PUB.Count_And_Get(
1798 p_encoded => FND_API.G_FALSE,
1799 p_count => x_msg_count,
1800 p_data => x_msg_data);
1801 WHEN OTHERS THEN
1802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1803
1804 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1805 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1806 FND_MSG_PUB.ADD;
1807
1808 FND_MSG_PUB.Count_And_Get(
1809 p_encoded => FND_API.G_FALSE,
1810 p_count => x_msg_count,
1811 p_data => x_msg_data);
1812
1813 END get_current_credit_rating;
1814
1815 END HZ_PARTY_INFO_PUB;