[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_SITE_V2PUB
Source
1 PACKAGE BODY HZ_PARTY_SITE_V2PUB AS
2 /*$Header: ARH2PSSB.pls 120.43 2006/07/31 23:39:52 baianand noship $ */
3
4 --------------------------------------------
5 -- declaration of global variables and types
6 --------------------------------------------
7
8 G_DEBUG_COUNT NUMBER := 0;
9 --G_DEBUG BOOLEAN := FALSE;
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_PARTY_SITE_V2PUB';
12
13 -- Bug 2197181: added for mix-n-match project.
14
15 g_pst_mixnmatch_enabled VARCHAR2(1);
16 g_pst_selected_datasources VARCHAR2(255);
17 g_pst_is_datasource_selected VARCHAR2(1) := 'N';
18 g_pst_entity_attr_id NUMBER;
19
20 G_MISS_CONTENT_SOURCE_TYPE CONSTANT VARCHAR2(30) := 'USER_ENTERED';
21
22
23 -- Code added for BUG 3714636
24
25 g_message_name VARCHAR2(1) :=NULL;
26 --------------------------------------------------
27 -- declaration of private procedures and functions
28 --------------------------------------------------
29
30 /*PROCEDURE enable_debug;
31
32 PROCEDURE disable_debug;
33 */
34
35 PROCEDURE do_create_party_site (
36 p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
37 x_party_site_id OUT NOCOPY NUMBER,
38 x_party_site_number OUT NOCOPY VARCHAR2,
39 x_return_status IN OUT NOCOPY VARCHAR2
40 );
41
42 PROCEDURE do_update_party_site(
43 p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
44 p_object_version_number IN OUT NOCOPY NUMBER,
45 x_return_status IN OUT NOCOPY VARCHAR2
46 );
47
48 PROCEDURE do_create_party_site_use(
49 p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
50 x_party_site_use_id OUT NOCOPY NUMBER,
51 x_return_status IN OUT NOCOPY VARCHAR2
52 );
53
54 PROCEDURE do_update_party_site_use(
55 p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
56 p_object_version_number IN OUT NOCOPY NUMBER,
57 x_return_status IN OUT NOCOPY VARCHAR2
58 );
59
60 PROCEDURE do_unmark_primary_per_type(
61 p_party_id IN NUMBER,
62 p_party_site_id IN NUMBER,
63 p_site_use_type IN VARCHAR2,
64 p_mode IN VARCHAR2 := NULL
65 );
66
67 PROCEDURE do_update_address(
68 p_party_id IN NUMBER,
69 p_location_id IN NUMBER
70 );
71
72 PROCEDURE do_unmark_address_flag(
73 p_party_id IN NUMBER,
74 p_party_site_id IN NUMBER := NULL,
75 p_mode IN VARCHAR2 := NULL
76 );
77 --
78 --- Following procedures are added in Party Site and Account Site status sync.
79 --
80 PROCEDURE update_acct_sites_status(
81 p_party_site_id IN NUMBER,
82 p_new_status IN VARCHAR2,
83 x_return_status IN OUT NOCOPY VARCHAR2
84 );
85 PROCEDURE inactivate_party_site_uses(
86 p_party_site_id IN NUMBER,
87 p_new_status IN VARCHAR2,
88 x_return_status IN OUT NOCOPY VARCHAR2
89 );
90 PROCEDURE inactivate_account_site_uses(
91 p_party_site_id IN NUMBER,
92 p_new_status IN VARCHAR2,
93 x_return_status IN OUT NOCOPY VARCHAR2
94 );
95 PROCEDURE cascade_site_status_changes(
96 p_party_site_id IN NUMBER,
97 p_new_status IN VARCHAR2,
98 x_return_status IN OUT NOCOPY VARCHAR2
99 );
100
101 PROCEDURE check_obsolete_columns (
102 p_create_update_flag IN VARCHAR2,
103 p_party_site_rec IN party_site_rec_type,
104 p_old_party_site_rec IN party_site_rec_type DEFAULT NULL,
105 x_return_status IN OUT NOCOPY VARCHAR2
106 );
107
108 -----------------------------
109 -- body of private procedures
110 -----------------------------
111
112 /**
113 * PRIVATE PROCEDURE enable_debug
114 *
115 * DESCRIPTION
116 * Turn on debug mode.
117 *
118 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
119 * HZ_UTILITY_V2PUB.enable_debug
120 *
121 * MODIFICATION HISTORY
122 *
123 * 07-23-2001 Jianying Huang o Created.
124
125 */
126
127 /*PROCEDURE enable_debug IS
128
129 BEGIN
130
131 G_DEBUG_COUNT := G_DEBUG_COUNT + 1;
132
133 IF G_DEBUG_COUNT = 1 THEN
134 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
135 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
136 THEN
137 HZ_UTILITY_V2PUB.enable_debug;
138 G_DEBUG := TRUE;
139 END IF;
140 END IF;
141
142 END enable_debug;
143 */
144
145
146 /**
147 * PRIVATE PROCEDURE disable_debug
148 *
149 * DESCRIPTION
150 * Turn off debug mode.
151 *
152 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
153 * HZ_UTILITY_V2PUB.disable_debug
154 *
155 * MODIFICATION HISTORY
156 *
157 * 07-23-2001 Jianying Huang o Created.
158 *
159 */
160
161 /*PROCEDURE disable_debug IS
162
163 BEGIN
164
165 IF G_DEBUG THEN
166 G_DEBUG_COUNT := G_DEBUG_COUNT - 1;
167
168 IF G_DEBUG_COUNT = 0 THEN
169 HZ_UTILITY_V2PUB.disable_debug;
170 G_DEBUG := FALSE;
171 END IF;
172 END IF;
173
174 END disable_debug;
175 */
176
177 /*===========================================================================+
178 | PROCEDURE
179 | do_create_party_site
180 |
181 | DESCRIPTION
182 | Creates party_site.
183 |
184 | SCOPE - PRIVATE
185 |
186 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
187 |
188 | ARGUMENTS : IN:
189 |
190 | OUT:
191 | x_party_site_id
192 | x_party_site_number
193 | IN/ OUT:
194 | p_party_site_rec
195 | x_return_status
196 |
197 | RETURNS : NONE
198 |
199 | NOTES
200 |
201 | MODIFICATION HISTORY
202 |
203 | 19-APR-2004 Rajib Ranjan Borah o Bug 3175816. Added GLOBAL_LOCATION_NUMBER
204 | to HZ_PARTY_SITES.
205 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
206 | For non-profile entities, the concept of
207 | select/de-select data-sources is obsoleted.
208 +===========================================================================*/
209
210 PROCEDURE do_create_party_site(
211 p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
212 x_party_site_id OUT NOCOPY NUMBER,
213 x_party_site_number OUT NOCOPY VARCHAR2,
214 x_return_status IN OUT NOCOPY VARCHAR2
215 ) IS
216
217 l_party_site_id NUMBER := p_party_site_rec.party_site_id;
218 l_party_site_number VARCHAR2(30) := p_party_site_rec.party_site_number;
219 l_gen_party_site_number VARCHAR2(1);
220 l_rowid ROWID := NULL;
221 l_dummy VARCHAR2(1);
222 l_debug_prefix VARCHAR2(30) := '';
223
224 -- Bug 2197181: Added l_loc_actual_content_source to denormalize actual_content_source into
225 -- hz_party_sites from hz_locations.
226
227 l_loc_actual_content_source hz_locations.actual_content_source%TYPE;
228 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
229 l_msg_count NUMBER;
230 l_msg_data VARCHAR2(2000);
231 l_country_code hz_parties.country%type;--4742586
232
233 BEGIN
234
235 -- Debug info.
236 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
237 hz_utility_v2pub.debug(p_message=>'do_create_party_site (+)',
238 p_prefix=>l_debug_prefix,
239 p_msg_level=>fnd_log.level_procedure);
240 END IF;
241
242 -- if primary key value is passed, check for uniqueness.
243 IF l_party_site_id IS NOT NULL AND
244 l_party_site_id <> FND_API.G_MISS_NUM
245 THEN
246 BEGIN
247 SELECT 'Y'
248 INTO l_dummy
249 FROM HZ_PARTY_SITES
250 WHERE PARTY_SITE_ID = l_party_site_id;
251
252 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
253 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_site_id');
254 FND_MSG_PUB.ADD;
255 RAISE FND_API.G_EXC_ERROR;
256
257 EXCEPTION
258 WHEN NO_DATA_FOUND THEN
259 NULL;
260 END;
261 END IF;
262
263 -- if GENERATE_PARTY_SITE_NUMBER is 'N', then if party_site_number is
264 -- not passed or is a duplicate raise error.
265 -- if GENERATE_PARTY_SITE_NUMBER is NULL or 'Y', generate party_site_number
266 -- from sequence till a unique value is obtained.
267
268 l_gen_party_site_number := fnd_profile.value('HZ_GENERATE_PARTY_SITE_NUMBER');
269
270 IF l_gen_party_site_number = 'N' THEN
271 IF l_party_site_number = FND_API.G_MISS_CHAR
272 OR
273 l_party_site_number IS NULL
274 THEN
275 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
276 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_site_number');
277 FND_MSG_PUB.ADD;
278 RAISE FND_API.G_EXC_ERROR;
279 END IF;
280
281 BEGIN
282 SELECT 'Y'
283 INTO l_dummy
284 FROM HZ_PARTY_SITES
285 WHERE PARTY_SITE_NUMBER = l_party_site_number;
286
287 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
288 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_site_number');
289 FND_MSG_PUB.ADD;
290 RAISE FND_API.G_EXC_ERROR;
291
292 EXCEPTION
293 WHEN NO_DATA_FOUND THEN
294 NULL;
295 END;
296
297 ELSIF l_gen_party_site_number = 'Y'
298 OR
299 l_gen_party_site_number IS NULL
300 THEN
301
302 IF l_party_site_number <> FND_API.G_MISS_CHAR
303 AND
304 l_party_site_number IS NOT NULL
305 THEN
306 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PARTY_SITE_NUM_AUTO_ON');
307 FND_MSG_PUB.ADD;
308 RAISE FND_API.G_EXC_ERROR;
309 END IF;
310
311 END IF;
312
313 x_party_site_number := l_party_site_number;
314
315 -- Bug 2197181: Added l_loc_actual_content_source to denormalize
316 -- actual_content_source into hz_party_sites from hz_locations.
317
318 HZ_REGISTRY_VALIDATE_V2PUB.validate_party_site(
319 'C',
320 p_party_site_rec,
321 l_rowid,
322 x_return_status,
323 l_loc_actual_content_source);
324
325 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
326 RAISE FND_API.G_EXC_ERROR;
327 END IF;
328
329 -- Bug 2197181: added for mix-n-match project. first check if user
330 -- has privilege to create user-entered data if mix-n-match is enabled.
331
332 -- SSM SST Integration and Extension
333 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
334
335 IF /*NVL(g_pst_mixnmatch_enabled, 'N') = 'Y' AND*/
336 l_loc_actual_content_source = G_MISS_CONTENT_SOURCE_TYPE
337 THEN
338 HZ_MIXNM_UTILITY.CheckUserCreationPrivilege (
339 p_entity_name => 'HZ_LOCATIONS',
340 p_entity_attr_id => g_pst_entity_attr_id,
341 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
342 p_actual_content_source => l_loc_actual_content_source,
343 x_return_status => x_return_status );
344 END IF;
345
346 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
347 RAISE FND_API.G_EXC_ERROR;
348 END IF;
349
350 -- Bug 2197181: added for mix-n-match project.
351 -- check if the data source is seleted.
352 /* SSM SST Integration and Extension
353 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
354 * There is no need to check if the data-source is selected.
355
356 g_pst_is_datasource_selected :=
357 HZ_MIXNM_UTILITY.isDataSourceSelected (
358 p_selected_datasources => g_pst_selected_datasources,
359 p_actual_content_source => l_loc_actual_content_source );
360 */
361 -- if this is the first active, visible party site,
362 -- we need to mark it with identifying flag = 'Y'.
363
364 BEGIN
365 -- Bug 2197181: Added the checking if the party site is visible
366 -- or not. The identifying address should be visible.
367
368 -- SSM SST Integration and Extension
369 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
370 -- There is no need to check if the data-source is selected.
371
372 SELECT 'Y' INTO l_dummy
373 FROM HZ_PARTY_SITES
374 WHERE PARTY_ID = p_party_site_rec.party_id
375 AND STATUS = 'A'
376 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
377 g_pst_selected_datasources, actual_content_source ) = 'Y'*/
378 AND ROWNUM = 1;
379
380 -- no exception raise, means 'a primary party site exist'
381
382 -- if the current party site is to be identifying, then unmark
383 -- the previous party sites with identifying flag = 'Y'.
384
385 -- Bug 2197181: added for mix-n-match project: the identifying_flag
386 -- can be set to 'Y' only if the party site will be visible. If it
387 -- is not visible, the flag must be reset to 'N'.
388
389 -- SSM SST Integration and Extension
390 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
391 -- There is no need to check if the data-source is selected.
392
393 IF p_party_site_rec.identifying_address_flag = 'Y' /*AND
394 g_pst_is_datasource_selected = 'Y'*/
395 THEN
396 -- Cahnged the below call to use the actual parameter name
397 -- to fix the bug # 5436273
398 do_unmark_address_flag(p_party_id => p_party_site_rec.party_id, p_mode => 'I');
399 ELSE
400 p_party_site_rec.identifying_address_flag := 'N';
401 END IF;
402
403 EXCEPTION
404 WHEN NO_DATA_FOUND THEN
405 -- this is the first visible, active address, so this will be
406 -- set as identifying address.
407
408 -- Bug 2197181: added for mix-n-match project: the identifying_flag
409 -- can be set to 'Y' only if the party site will be visible. If it is
410 -- not visible, the flag must be reset to 'N'.
411
412 -- SSM SST Integration and Extension
413 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
414 -- There is no need to check if the data-source is selected.
415
416 IF (NVL(p_party_site_rec.status, 'A') = 'A' OR
417 p_party_site_rec.status = FND_API.G_MISS_CHAR) /*AND
418 g_pst_is_datasource_selected = 'Y'*/
419 THEN
420 p_party_site_rec.identifying_address_flag := 'Y';
421 ELSE
422 p_party_site_rec.identifying_address_flag := 'N';
423 END IF;
424 END;
425
426 --denormalize primary address
427 IF p_party_site_rec.identifying_address_flag = 'Y' THEN
428 IF p_party_site_rec.party_id <> -1 THEN
429 do_update_address(
430 p_party_site_rec.party_id,
431 p_party_site_rec.location_id);
432 END IF;
433 END IF;
434
435 p_party_site_rec.party_site_id := l_party_site_id;
436 p_party_site_rec.party_site_number := l_party_site_number;
437
438 -- this is for orig_system_defaulting
439 IF p_party_site_rec.party_site_id = FND_API.G_MISS_NUM THEN
440 p_party_site_rec.party_site_id := NULL;
441 END IF;
442
443
444 -- Debug info.
445 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
446 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Insert_Row (+)',
447 p_prefix=>l_debug_prefix,
448 p_msg_level=>fnd_log.level_procedure);
449 END IF;
450
451 -- call table-handler.
452 HZ_PARTY_SITES_PKG.Insert_Row (
453 X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
454 X_PARTY_ID => p_party_site_rec.party_id,
455 X_LOCATION_ID => p_party_site_rec.location_id,
456 X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
457 X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
458 X_ATTRIBUTE1 => p_party_site_rec.attribute1,
459 X_ATTRIBUTE2 => p_party_site_rec.attribute2,
460 X_ATTRIBUTE3 => p_party_site_rec.attribute3,
461 X_ATTRIBUTE4 => p_party_site_rec.attribute4,
462 X_ATTRIBUTE5 => p_party_site_rec.attribute5,
463 X_ATTRIBUTE6 => p_party_site_rec.attribute6,
464 X_ATTRIBUTE7 => p_party_site_rec.attribute7,
465 X_ATTRIBUTE8 => p_party_site_rec.attribute8,
466 X_ATTRIBUTE9 => p_party_site_rec.attribute9,
467 X_ATTRIBUTE10 => p_party_site_rec.attribute10,
468 X_ATTRIBUTE11 => p_party_site_rec.attribute11,
469 X_ATTRIBUTE12 => p_party_site_rec.attribute12,
470 X_ATTRIBUTE13 => p_party_site_rec.attribute13,
471 X_ATTRIBUTE14 => p_party_site_rec.attribute14,
472 X_ATTRIBUTE15 => p_party_site_rec.attribute15,
473 X_ATTRIBUTE16 => p_party_site_rec.attribute16,
474 X_ATTRIBUTE17 => p_party_site_rec.attribute17,
475 X_ATTRIBUTE18 => p_party_site_rec.attribute18,
476 X_ATTRIBUTE19 => p_party_site_rec.attribute19,
477 X_ATTRIBUTE20 => p_party_site_rec.attribute20,
478 X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
479 X_LANGUAGE => p_party_site_rec.language,
480 X_MAILSTOP => p_party_site_rec.mailstop,
481 X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
482 X_STATUS => p_party_site_rec.status,
483 X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
484 X_ADDRESSEE => p_party_site_rec.addressee,
485 X_OBJECT_VERSION_NUMBER => 1,
486 X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
487 X_APPLICATION_ID => p_party_site_rec.application_id,
488 X_ACTUAL_CONTENT_SOURCE => l_loc_actual_content_source,
489 -- Bug 3175816.
490 X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
491 X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
492 );
493
494
495 -- Debug info.
496 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
497 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Insert_Row (-) ' ||
498 'x_party_site_id = ' || p_party_site_rec.party_site_id,
499 p_prefix=>l_debug_prefix,
500 p_msg_level=>fnd_log.level_procedure);
501 END IF;
502
503 if p_party_site_rec.orig_system is not null
504 and p_party_site_rec.orig_system <>fnd_api.g_miss_char
505 then
506 l_orig_sys_reference_rec.orig_system := p_party_site_rec.orig_system;
507 l_orig_sys_reference_rec.orig_system_reference := p_party_site_rec.orig_system_reference;
508 l_orig_sys_reference_rec.owner_table_name := 'HZ_PARTY_SITES';
509 l_orig_sys_reference_rec.owner_table_id := p_party_site_rec.party_site_id;
510 l_orig_sys_reference_rec.created_by_module := p_party_site_rec.created_by_module;
511
512 hz_orig_system_ref_pub.create_orig_system_reference(
513 FND_API.G_FALSE,
514 l_orig_sys_reference_rec,
515 x_return_status,
516 l_msg_count,
517 l_msg_data);
518 IF x_return_status <> fnd_api.g_ret_sts_success THEN
519 RAISE FND_API.G_EXC_ERROR;
520 END IF;
521 end if;
522
523 x_party_site_id := p_party_site_rec.party_site_id;
524 x_party_site_number := p_party_site_rec.party_site_number;
525 --Bug No. 4742586
526 BEGIN
527 Select country into l_country_code from hz_locations
528 where location_id = p_party_site_rec.location_id;
529
530 EXCEPTION
531 WHEN OTHERS THEN l_country_code := null;
532 END;
533 --Bug No. 4742586
534 -- Bug 4490715 : call eTax procedure to populate ZX_PARTY_TAX_PROFILE
535 ZX_PARTY_TAX_PROFILE_PKG.insert_row (
536 P_COLLECTING_AUTHORITY_FLAG => null,
537 P_PROVIDER_TYPE_CODE => null,
538 P_CREATE_AWT_DISTS_TYPE_CODE => null,
539 P_CREATE_AWT_INVOICES_TYPE_COD => null,
540 P_TAX_CLASSIFICATION_CODE => null,
541 P_SELF_ASSESS_FLAG => null,
542 P_ALLOW_OFFSET_TAX_FLAG => null,
543 P_REP_REGISTRATION_NUMBER => null,
544 P_EFFECTIVE_FROM_USE_LE => null,
545 P_RECORD_TYPE_CODE => null,
546 P_REQUEST_ID => null,
547 P_ATTRIBUTE1 => null,
548 P_ATTRIBUTE2 => null,
549 P_ATTRIBUTE3 => null,
550 P_ATTRIBUTE4 => null,
551 P_ATTRIBUTE5 => null,
552 P_ATTRIBUTE6 => null,
553 P_ATTRIBUTE7 => null,
554 P_ATTRIBUTE8 => null,
555 P_ATTRIBUTE9 => null,
556 P_ATTRIBUTE10 => null,
557 P_ATTRIBUTE11 => null,
558 P_ATTRIBUTE12 => null,
559 P_ATTRIBUTE13 => null,
560 P_ATTRIBUTE14 => null,
561 P_ATTRIBUTE15 => null,
562 P_ATTRIBUTE_CATEGORY => null,
563 P_PARTY_ID => x_party_site_id,
564 P_PROGRAM_LOGIN_ID => null,
565 P_PARTY_TYPE_CODE => 'THIRD_PARTY_SITE',
566 P_SUPPLIER_FLAG => null,
567 P_CUSTOMER_FLAG => null,
568 P_SITE_FLAG => null,
569 P_PROCESS_FOR_APPLICABILITY_FL => null,
570 P_ROUNDING_LEVEL_CODE => null,
571 P_ROUNDING_RULE_CODE => null,
572 P_WITHHOLDING_START_DATE => null,
573 P_INCLUSIVE_TAX_FLAG => null,
574 P_ALLOW_AWT_FLAG => null,
575 P_USE_LE_AS_SUBSCRIBER_FLAG => null,
576 P_LEGAL_ESTABLISHMENT_FLAG => null,
577 P_FIRST_PARTY_LE_FLAG => null,
578 P_REPORTING_AUTHORITY_FLAG => null,
579 X_RETURN_STATUS => x_return_status,
580 P_REGISTRATION_TYPE_CODE => null,--4742586
581 P_COUNTRY_CODE => l_country_code --4742586
582 );
583 IF x_return_status <> fnd_api.g_ret_sts_success THEN
584 RAISE FND_API.G_EXC_ERROR;
585 END IF;
586
587 -- Debug info.
588 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
589 hz_utility_v2pub.debug(p_message=>'do_create_party_site (-)',
590 p_prefix=>l_debug_prefix,
591 p_msg_level=>fnd_log.level_procedure);
592 END IF;
593
594 END do_create_party_site;
595
596
597 /*===========================================================================+
598 | PROCEDURE
599 | do_update_party_site
600 |
601 | DESCRIPTION
602 | Updates party_site.
603 |
604 | SCOPE - PRIVATE
605 |
606 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
607 |
608 | ARGUMENTS : IN:
609 | OUT:
610 | IN/ OUT:
611 | p_party_site_rec
612 | x_return_statue
613 |
614 | RETURNS : NONE
615 |
616 | NOTES
617 |
618 | MODIFICATION HISTORY
619 |
620 | 04-Dec-2001 Rajeshwari P Added the code to make an identifying address
621 | as inactive Only if any other active sites are
622 | are present else raise an error.
623 | 05-APR-2001 Rajeshwari P Bug 2306201.Partially reverting the fix
624 | made in Bug 1882511.No error is raised if
625 | the only site present is made inactive.
626 | 28-OCT-2003 Ramesh Ch Bug#2914238. Updated who columns.
627 |
628 | 19-APR-2004 Rajib Ranjan Borah o Bug 3175816. Added GLOBAL_LOCATION_NUMBER
629 | to HZ_PARTY_SITES.
630 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
631 | For non-profile entities, the concept of
632 | select/de-select data-sources is obsoleted.
633 |
634 +===========================================================================*/
635
636 PROCEDURE do_update_party_site(
637 p_party_site_rec IN OUT NOCOPY PARTY_SITE_REC_TYPE,
638 p_object_version_number IN OUT NOCOPY NUMBER,
639 x_return_status IN OUT NOCOPY VARCHAR2
640 ) IS
641
642 l_object_version_number NUMBER;
643 l_rowid ROWID;
644 ldup_rowid ROWID;
645 db_identifying_address_flag VARCHAR2(1);
646 db_actual_content_source hz_party_sites.actual_content_source%TYPE;
647 db_party_id NUMBER;
648 db_location_id NUMBER;
649 db_status VARCHAR2(1);
650 l_identifying_location_id NUMBER;
651 l_loc_actual_content_source hz_locations.actual_content_source%TYPE;
652 l_dummy VARCHAR2(1);
653 l_debug_prefix VARCHAR2(30) := '';
654
655 BEGIN
656
657 -- Debug info.
658 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
659 hz_utility_v2pub.debug(p_message=>'do_update_party_site (+)',
660 p_prefix=>l_debug_prefix,
661 p_msg_level=>fnd_log.level_procedure);
662 END IF;
663
664
665 -- check whether record has been updated by another user. If not, lock it.
666 BEGIN
667 SELECT OBJECT_VERSION_NUMBER,
668 PARTY_ID,
669 LOCATION_ID,
670 IDENTIFYING_ADDRESS_FLAG,
671 STATUS,
672 ROWID,
673 ACTUAL_CONTENT_SOURCE
674 INTO l_object_version_number,
675 db_party_id,
676 db_location_id,
677 db_identifying_address_flag,
678 db_status,
679 l_rowid,
680 db_actual_content_source
681 FROM HZ_PARTY_SITES
682 WHERE PARTY_SITE_ID = p_party_site_rec.party_site_id
683 FOR UPDATE OF PARTY_SITE_ID NOWAIT;
684
685 IF NOT
686 (
687 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
688 OR
689 (p_object_version_number IS NOT NULL AND
690 l_object_version_number IS NOT NULL AND
691 p_object_version_number = l_object_version_number
692 )
693 )
694 THEN
695 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
696 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_party_sites');
697 FND_MSG_PUB.ADD;
698 RAISE FND_API.G_EXC_ERROR;
699 END IF;
700
701 p_object_version_number := nvl(l_object_version_number, 1) + 1;
702
703 EXCEPTION WHEN NO_DATA_FOUND THEN
704 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
705 FND_MESSAGE.SET_TOKEN('RECORD', 'party site');
706 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_rec.party_site_id), 'null'));
707 FND_MSG_PUB.ADD;
708 RAISE FND_API.G_EXC_ERROR;
709 END;
710
711 -- Bug 2197181: added for mix-n-match project.
712 -- check if the data source is seleted.
713 /* SSM SST Integration and Extension
714 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
715 * There is no need to check if the data-source is selected.
716
717 g_pst_is_datasource_selected :=
718 HZ_MIXNM_UTILITY.isDataSourceSelected (
719 p_selected_datasources => g_pst_selected_datasources,
720 p_actual_content_source => db_actual_content_source );
721 */
722 -- call for validations.
723 HZ_REGISTRY_VALIDATE_V2PUB.validate_party_site(
724 'U',
725 p_party_site_rec,
726 l_rowid,
727 x_return_status,
728 l_loc_actual_content_source);
729
730 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733
734 -- if the current site is updated to be identifying one, then
735 -- unmark the previous party sites with identifying flag = 'Y'.
736
737
738 IF p_party_site_rec.identifying_address_flag = 'Y' AND
739 db_identifying_address_flag <> 'Y' /* AND
740 -- Bug 2197181: added for mix-n-match project: the identifying_flag
741 -- can be set to 'Y' only if the party site will be visible. If it
742 -- is not visible, the flag must be reset to 'N'.
743
744 -- SSM SST Integration and Extension
745 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
746 -- There is no need to check if the data-source is selected.
747
748 g_pst_is_datasource_selected = 'Y' */
749 THEN
750 do_unmark_address_flag(db_party_id, p_party_site_rec.party_site_id, 'U');
751 l_identifying_location_id := db_location_id;
752 ELSE
753 -- ignore the value of primary address flag if the flag
754 -- has been set to 'N' or NULL or FND_API.G_MISS_CHAR.
755 -- user can not unset a primary address flag by passing
756 -- value 'N' or NULL. to unset a primary flag, he/she
757 -- needs to select another address as primary. this
758 -- address will be unmarked automatically.
759
760 p_party_site_rec.identifying_address_flag := NULL;
761
762 -- If the current site is an identifying address site and
763 -- is being marked as inactive then set it as non-primary
764 -- and mark the next active site as identifying address.
765
766 IF (db_status = 'A' AND
767 p_party_site_rec.status = 'I' AND
768 db_identifying_address_flag = 'Y') OR
769 (db_status = 'I' AND
770 p_party_site_rec.status = 'A' /* AND
771 -- Bug 2197181: added for mix-n-match project: the
772 -- identifying_flag can be set to 'Y' only if the party site will be visible.
773
774 -- SSM SST Integration and Extension
775 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
776 -- There is no need to check if the data-source is selected.
777
778 g_pst_is_datasource_selected = 'Y' */)
779 THEN
780 BEGIN
781 -- Check if any other active, visible, party site is present.
782
783 SELECT ROWID, location_id
784 INTO ldup_rowid, l_identifying_location_id
785 FROM HZ_PARTY_SITES
786 WHERE party_site_id = (
787 SELECT min(party_site_id)
788 FROM hz_party_sites
789 WHERE party_id = db_party_id
790 AND status = 'A'
791 AND party_site_id <> p_party_site_rec.party_site_id
792 -- Bug 2197181: added for mix-n-match project: the identifying_flag
793 -- can be set to 'Y' only if the party site will be visible.
794
795 -- SSM SST Integration and Extension
796 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
797 -- There is no need to check if the data-source is selected.
798 /* AND HZ_MIXNM_UTILITY.isDataSourceSelected (
799 g_pst_selected_datasources,
800 actual_content_source) = 'Y'*/);
801
802 -- no exception means an active, visible party site was found.
803
804 -- set the new party site as identifying address and
805 -- set the current record as non identifying address.
806
807 IF p_party_site_rec.status = 'I' THEN
808 UPDATE HZ_PARTY_SITES
809 SET IDENTIFYING_ADDRESS_FLAG = 'Y',
810 last_update_date = hz_utility_v2pub.last_update_date,
811 last_updated_by = hz_utility_v2pub.last_updated_by,
812 last_update_login = hz_utility_v2pub.last_update_login,
813 request_id = hz_utility_v2pub.request_id,
814 program_id = hz_utility_v2pub.program_id,
815 program_application_id = hz_utility_v2pub.program_application_id,
816 program_update_date = hz_utility_v2pub.program_update_date
817 WHERE ROWID = ldup_rowid;
818
819 p_party_site_rec.identifying_address_flag := 'N';
820 END IF;
821 EXCEPTION
822 WHEN NO_DATA_FOUND THEN
823 -- no active sites present.
824
825 -- Make the current site as Inactive.
826 -- Bug 2306201: reset primary flag to 'N'
827 -- and null out NOCOPY address components in hz_parties
828
829 IF p_party_site_rec.status = 'I' THEN
830 l_identifying_location_id := null;
831 p_party_site_rec.identifying_address_flag := 'N';
832 ELSIF p_party_site_rec.status = 'A' THEN
833 -- if user is making the current location as active
834 -- and the current location is the only active,
835 -- visible location, make it as identifying address.
836
837 l_identifying_location_id := db_location_id;
838 p_party_site_rec.identifying_address_flag := 'Y';
839 END IF;
840 END;
841 END IF;
842 END IF;
843
844 --denormalize primary address
845 IF p_party_site_rec.identifying_address_flag IS NOT NULL THEN
846 IF db_party_id <> -1 THEN
847 do_update_address(
848 db_party_id,
849 l_identifying_location_id);
850 END IF;
851 END IF;
852
853 if (p_party_site_rec.orig_system is not null
854 and p_party_site_rec.orig_system <>fnd_api.g_miss_char)
855 and (p_party_site_rec.orig_system_reference is not null
856 and p_party_site_rec.orig_system_reference <>fnd_api.g_miss_char)
857 then
858 p_party_site_rec.orig_system_reference := null;
859 -- In mosr, we have bypassed osr nonupdateable validation
860 -- but we should not update existing osr, set it to null
861 end if;
862
863 -- Debug info.
864 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
865 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Update_Row (+) ',
866 p_prefix=>l_debug_prefix,
867 p_msg_level=>fnd_log.level_procedure);
868 END IF;
869
870 --Call to table-handler
871 HZ_PARTY_SITES_PKG.Update_Row (
872 X_Rowid => l_rowid,
873 X_PARTY_SITE_ID => p_party_site_rec.party_site_id,
874 X_PARTY_ID => p_party_site_rec.party_id,
875 X_LOCATION_ID => p_party_site_rec.location_id,
876 X_PARTY_SITE_NUMBER => p_party_site_rec.party_site_number,
877 X_ATTRIBUTE_CATEGORY => p_party_site_rec.attribute_category,
878 X_ATTRIBUTE1 => p_party_site_rec.attribute1,
879 X_ATTRIBUTE2 => p_party_site_rec.attribute2,
880 X_ATTRIBUTE3 => p_party_site_rec.attribute3,
881 X_ATTRIBUTE4 => p_party_site_rec.attribute4,
882 X_ATTRIBUTE5 => p_party_site_rec.attribute5,
883 X_ATTRIBUTE6 => p_party_site_rec.attribute6,
884 X_ATTRIBUTE7 => p_party_site_rec.attribute7,
885 X_ATTRIBUTE8 => p_party_site_rec.attribute8,
886 X_ATTRIBUTE9 => p_party_site_rec.attribute9,
887 X_ATTRIBUTE10 => p_party_site_rec.attribute10,
888 X_ATTRIBUTE11 => p_party_site_rec.attribute11,
889 X_ATTRIBUTE12 => p_party_site_rec.attribute12,
890 X_ATTRIBUTE13 => p_party_site_rec.attribute13,
891 X_ATTRIBUTE14 => p_party_site_rec.attribute14,
892 X_ATTRIBUTE15 => p_party_site_rec.attribute15,
893 X_ATTRIBUTE16 => p_party_site_rec.attribute16,
894 X_ATTRIBUTE17 => p_party_site_rec.attribute17,
895 X_ATTRIBUTE18 => p_party_site_rec.attribute18,
896 X_ATTRIBUTE19 => p_party_site_rec.attribute19,
897 X_ATTRIBUTE20 => p_party_site_rec.attribute20,
898 X_ORIG_SYSTEM_REFERENCE => p_party_site_rec.orig_system_reference,
899 X_LANGUAGE => p_party_site_rec.language,
900 X_MAILSTOP => p_party_site_rec.mailstop,
901 X_IDENTIFYING_ADDRESS_FLAG => p_party_site_rec.identifying_address_flag,
902 X_STATUS => p_party_site_rec.status,
903 X_PARTY_SITE_NAME => p_party_site_rec.party_site_name,
904 X_ADDRESSEE => p_party_site_rec.addressee,
905 X_OBJECT_VERSION_NUMBER => p_object_version_number,
906 X_CREATED_BY_MODULE => p_party_site_rec.created_by_module,
907 X_APPLICATION_ID => p_party_site_rec.application_id,
908 -- Bug 2197181 : actual_content_source is non-updateable.
909 X_ACTUAL_CONTENT_SOURCE => NULL,
910 -- Bug 3175816
911 X_GLOBAL_LOCATION_NUMBER => p_party_site_rec.global_location_number,
912 X_DUNS_NUMBER_C => p_party_site_rec.duns_number_c
913 );
914
915 -- Debug info.
916 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
917 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITES_PKG.Update_Row (-) ',
918 p_prefix=>l_debug_prefix,
919 p_msg_level=>fnd_log.level_procedure);
920 END IF;
921 --
922 --- If old and new statuses are different, then call
923 --- the cascade_site_status_changes procedure to synch
924 --- the party site and account status.
925 --
926 IF p_party_site_rec.status <> db_status THEN
927 -- Code modified for Bug 3714636 starts here
928 IF(g_message_name is null) THEN
929 BEGIN
930 SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
931 WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
932 AND language_code = userenv('LANG')
933 AND application_id = 222
934 AND rownum =1;
935 EXCEPTION
936 WHEN NO_DATA_FOUND THEN
937 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
938 FND_MESSAGE.SET_TOKEN('RECORD', 'Message Name');
939 --FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_use_rec.party_site_id), 'null'));
940 FND_MSG_PUB.ADD;
941 RAISE FND_API.G_EXC_ERROR;
942 END;
943 END IF;
944 IF g_message_name IS NOT NULL THEN
945 -- Code modified for Bug 3714636 ends here
946 cascade_site_status_changes(p_party_site_rec.party_site_id,
947 p_party_site_rec.status,
948 x_return_status);
949 END IF;
950 END IF;
951
952 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
953 RAISE FND_API.G_EXC_ERROR;
954 END IF;
955
956 -- Debug info.
957 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
958 hz_utility_v2pub.debug(p_message=> 'do_update_party_site (-)',
959 p_prefix=>l_debug_prefix,
960 p_msg_level=>fnd_log.level_procedure);
961 END IF;
962
963 END do_update_party_site;
964
965
966 /*===========================================================================+
967 | PROCEDURE
968 | do_create_party_site_use
969 |
970 | DESCRIPTION
971 | Creates party_site_use.
972 |
973 | SCOPE - PRIVATE
974 |
975 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
976 |
977 | ARGUMENTS : IN:
978 | OUT:
979 | x_party_site_use_id
980 | IN/ OUT:
981 | p_party_site_use_rec
982 | x_return_status
983 |
984 | RETURNS : NONE
985 |
986 | NOTES
987 |
988 | MODIFICATION HISTORY
989 | 10-NOV-2003 Rajib Ranjan Borah o Bug 2065191.While creation,set record to primary
990 | only if this is the first active record for the
991 | site_use_type of given party.
992 | Commented out unused variables and redundant code.
993 +===========================================================================*/
994
995 PROCEDURE do_create_party_site_use(
996 p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
997 x_party_site_use_id OUT NOCOPY NUMBER,
998 x_return_status IN OUT NOCOPY VARCHAR2
999 ) IS
1000
1001 l_party_site_use_id NUMBER := p_party_site_use_rec.party_site_use_id;
1002 l_rowid ROWID := NULL;
1003 -- Bug 2065191
1004 -- l_count NUMBER;
1005 l_exist VARCHAR2(1) := 'N';
1006 l_party_id NUMBER;
1007 -- Bug 2065191
1008 -- l_primary_per_type VARCHAR2(1) := p_party_site_use_rec.primary_per_type;
1009 -- l_msg_count NUMBER;
1010 -- l_msg_data VARCHAR2(2000);
1011 l_dummy VARCHAR2(1);
1012 l_debug_prefix VARCHAR2(30) := '';
1013
1014 BEGIN
1015
1016 -- Debug info.
1017 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1018 hz_utility_v2pub.debug(p_message=>'do_create_party_site_use (+)',
1019 p_prefix=>l_debug_prefix,
1020 p_msg_level=>fnd_log.level_procedure);
1021 END IF;
1022
1023 -- if primary key value is passed, check for uniqueness.
1024 IF l_party_site_use_id IS NOT NULL AND
1025 l_party_site_use_id <> FND_API.G_MISS_NUM
1026 THEN
1027 BEGIN
1028 SELECT 'Y'
1029 INTO l_dummy
1030 FROM HZ_PARTY_SITE_USES
1031 WHERE PARTY_SITE_USE_ID = l_party_site_use_id;
1032
1033 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
1034 FND_MESSAGE.SET_TOKEN('COLUMN', 'party_site_use_id');
1035 FND_MSG_PUB.ADD;
1036 RAISE FND_API.G_EXC_ERROR;
1037
1038 EXCEPTION
1039 WHEN NO_DATA_FOUND THEN
1040 NULL;
1041 END;
1042 END IF;
1043
1044 HZ_REGISTRY_VALIDATE_V2PUB.validate_party_site_use(
1045 'C',
1046 p_party_site_use_rec,
1047 l_rowid,
1048 x_return_status);
1049
1050 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1051 RAISE FND_API.G_EXC_ERROR;
1052 END IF;
1053
1054
1055 -- Bug 2065191
1056 IF p_party_site_use_rec.status = 'A'
1057 OR p_party_site_use_rec.status IS NULL
1058 OR p_party_site_use_rec.status = FND_API.G_MISS_CHAR
1059 THEN
1060 -- if this is the first active party site use per type,,
1061 -- we need to mark it with primary_per_type = 'Y'.
1062 SELECT PARTY_ID
1063 INTO l_party_id
1064 FROM HZ_PARTY_SITES
1065 WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
1066
1067
1068 BEGIN
1069 SELECT 'Y'
1070 INTO l_exist
1071 FROM HZ_PARTY_SITE_USES SU
1072 WHERE PARTY_SITE_ID IN (
1073 SELECT PARTY_SITE_ID
1074 FROM HZ_PARTY_SITES PS
1075 WHERE PS.PARTY_ID = l_party_id )
1076 AND SU.SITE_USE_TYPE = p_party_site_use_rec.site_use_type
1077 AND STATUS = 'A' -- Bug 2065191
1078 AND ROWNUM = 1;
1079
1080 IF p_party_site_use_rec.primary_per_type = 'Y' THEN
1081 -- unmark the previous site uses whose primary_per_type = 'Y'.
1082 do_unmark_primary_per_type(
1083 l_party_id,
1084 p_party_site_use_rec.party_site_id,
1085 p_party_site_use_rec.site_use_type, 'I' );
1086
1087 END IF;
1088 EXCEPTION
1089 --this is a new site use type
1090 WHEN NO_DATA_FOUND THEN
1091 -- l_primary_per_type := 'Y';-- Bug 2065191
1092 p_party_site_use_rec.primary_per_type:='Y';
1093 END;
1094 -- p_party_site_use_rec.primary_per_type := l_primary_per_type;--Bug 2065191.
1095 END IF;-- end if corresponding to if added for bug 2065191.
1096
1097 -- Debug info.
1098 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1099 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Insert_Row (+)',
1100 p_prefix=>l_debug_prefix,
1101 p_msg_level=>fnd_log.level_procedure);
1102 END IF;
1103
1104 -- call table-handler.
1105 HZ_PARTY_SITE_USES_PKG.Insert_Row (
1106 X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
1107 X_COMMENTS => p_party_site_use_rec.comments,
1108 X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
1109 X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
1110 X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
1111 X_STATUS => p_party_site_use_rec.status,
1112 X_OBJECT_VERSION_NUMBER => 1,
1113 X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
1114 X_APPLICATION_ID => p_party_site_use_rec.application_id
1115 );
1116
1117 x_party_site_use_id := p_party_site_use_rec.party_site_use_id;
1118
1119 -- Debug info.
1120 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1121 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Insert_Row (-) ' ||
1122 'x_party_site_use_id = ' || x_party_site_use_id,
1123 p_prefix=>l_debug_prefix,
1124 p_msg_level=>fnd_log.level_procedure);
1125 END IF;
1126
1127 -- Debug info.
1128 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1129 hz_utility_v2pub.debug(p_message=>'do_create_party_site_use (-)',
1130 p_prefix=>l_debug_prefix,
1131 p_msg_level=>fnd_log.level_procedure);
1132 END IF;
1133
1134 END do_create_party_site_use;
1135
1136
1137 /*===========================================================================+
1138 | PROCEDURE
1139 | do_update_party_site_use
1140 |
1141 | DESCRIPTION
1142 | Updates party_site_use.
1143 |
1144 | SCOPE - PRIVATE
1145 |
1146 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1147 |
1148 | ARGUMENTS : IN:
1149 | OUT:
1150 | IN/ OUT:
1151 | p_party_site_use_rec
1152 | x_return_status
1153 |
1154 | RETURNS : NONE
1155 |
1156 | NOTES
1157 |
1158 | MODIFICATION HISTORY
1159 +===========================================================================*/
1160
1161 PROCEDURE do_update_party_site_use(
1162 p_party_site_use_rec IN OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
1163 p_object_version_number IN OUT NOCOPY NUMBER,
1164 x_return_status IN OUT NOCOPY VARCHAR2
1165 ) IS
1166
1167 l_object_version_number NUMBER;
1168 l_rowid ROWID;
1169 l_party_id NUMBER;
1170 l_party_site_id NUMBER;
1171 -- Bug Fix: 3651716
1172 l_dup_rowid ROWID;
1173 l_status HZ_PARTY_SITE_USES.STATUS%TYPE;
1174 l_site_use_type HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE;
1175 l_primary_per_type HZ_PARTY_SITE_USES.PRIMARY_PER_TYPE%TYPE;
1176 l_msg_count NUMBER;
1177 l_msg_data VARCHAR2(2000);
1178 l_debug_prefix VARCHAR2(30) := '';
1179
1180 BEGIN
1181
1182 -- Debug info.
1183 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1184 hz_utility_v2pub.debug(p_message=>'do_update_party_site_use (+)',
1185 p_prefix=>l_debug_prefix,
1186 p_msg_level=>fnd_log.level_procedure);
1187 END IF;
1188
1189 -- check whether record has been updated by another user. If not, lock it.
1190 BEGIN
1191 SELECT OBJECT_VERSION_NUMBER,
1192 PARTY_SITE_ID,
1193 SITE_USE_TYPE,
1194 PRIMARY_PER_TYPE,
1195 STATUS,
1196 ROWID
1197 INTO l_object_version_number,
1198 l_party_site_id,
1199 l_site_use_type,
1200 l_primary_per_type,
1201 l_status,
1202 l_rowid
1203 FROM HZ_PARTY_SITE_USES
1204 WHERE PARTY_SITE_USE_ID = p_party_site_use_rec.party_site_use_id
1205 FOR UPDATE OF PARTY_SITE_USE_ID NOWAIT;
1206
1207 IF NOT
1208 (
1209 (p_object_version_number IS NULL AND l_object_version_number IS NULL)
1210 OR
1211 (p_object_version_number IS NOT NULL AND
1212 l_object_version_number IS NOT NULL AND
1213 p_object_version_number = l_object_version_number
1214 )
1215 )
1216 THEN
1217 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1218 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_SITE_USES');
1219 FND_MSG_PUB.ADD;
1220 RAISE FND_API.G_EXC_ERROR;
1221 END IF;
1222
1223 p_object_version_number := nvl(l_object_version_number, 1) + 1;
1224
1225 EXCEPTION WHEN NO_DATA_FOUND THEN
1226 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1227 FND_MESSAGE.SET_TOKEN('RECORD', 'party site use');
1228 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_use_rec.party_site_use_id), 'null'));
1229 FND_MSG_PUB.ADD;
1230 RAISE FND_API.G_EXC_ERROR;
1231 END;
1232
1233 -- unmark the previous site uses whose primary_per_type = 'Y'.
1234 BEGIN
1235 -- Modified for Bug Fix: 3651716
1236 SELECT PARTY_ID
1237 INTO l_party_id
1238 FROM HZ_PARTY_SITES
1239 WHERE PARTY_SITE_ID = l_party_site_id;
1240
1241 IF p_party_site_use_rec.primary_per_type = 'Y' AND
1242 p_party_site_use_rec.status = 'I' AND
1243 /* Bug Fix: 4203495 */
1244 l_primary_per_type <> 'Y'
1245 THEN
1246 NULL;
1247 ELSIF p_party_site_use_rec.primary_per_type = 'Y' AND
1248 l_primary_per_type <> 'Y'
1249 THEN
1250 do_unmark_primary_per_type(
1251 l_party_id,
1252 l_party_site_id,
1253 l_site_use_type, 'U' );
1254
1255 ELSE
1256 -- ignore the value of primary per type flag if the flag has been
1257 -- set to 'N' or NULL by passing FND_API.G_MISS_CHAR.
1258 -- user can not unset a primary per type flag by passing value 'N' or NULL.
1259 -- to unset a primary flag, he/she needs to select another site use as primary.
1260 -- This site use will be unmarked automatically.
1261 p_party_site_use_rec.primary_per_type := NULL;
1262
1263 -- Bug Fix: 3651716
1264 IF (l_status = 'A' AND
1265 p_party_site_use_rec.status = 'I' AND
1266 l_primary_per_type = 'Y' ) OR
1267 (l_status = 'I' AND
1268 p_party_site_use_rec.status = 'A')
1269 THEN
1270 BEGIN
1271 -- Check if any other active party site use exists
1272 SELECT ROWID
1273 INTO l_dup_rowid
1274 FROM HZ_PARTY_SITE_USES
1275 WHERE PARTY_SITE_USE_ID = (
1276 SELECT min(PARTY_SITE_USE_ID)
1277 FROM HZ_PARTY_SITE_USES
1278 WHERE PARTY_SITE_ID IN (
1279 SELECT PARTY_SITE_ID
1280 FROM HZ_PARTY_SITES
1281 WHERE PARTY_ID = l_party_id )
1282 AND STATUS = 'A'
1283 AND SITE_USE_TYPE = l_site_use_type
1284 AND PARTY_SITE_USE_ID <> p_party_site_use_rec.party_site_use_id);
1285
1286 IF p_party_site_use_rec.status = 'I' THEN
1287 UPDATE HZ_PARTY_SITE_USES
1288 SET PRIMARY_PER_TYPE = 'Y',
1289 last_update_date = hz_utility_v2pub.last_update_date,
1290 last_updated_by = hz_utility_v2pub.last_updated_by,
1291 last_update_login= hz_utility_v2pub.last_update_login,
1292 request_id = hz_utility_v2pub.request_id,
1293 program_id = hz_utility_v2pub.program_id,
1294 program_application_id = hz_utility_v2pub.program_application_id,
1295 program_update_date = hz_utility_v2pub.program_update_date
1296 WHERE ROWID = l_dup_rowid;
1297 p_party_site_use_rec.primary_per_type := 'N';
1298 END IF;
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 IF p_party_site_use_rec.status = 'I' THEN
1302 p_party_site_use_rec.primary_per_type := 'N';
1303 ELSIF p_party_site_use_rec.status = 'A' THEN
1304 p_party_site_use_rec.primary_per_type := 'Y';
1305 END IF;
1306 END;
1307 END IF;
1308 END IF;
1309
1310 EXCEPTION
1311 WHEN NO_DATA_FOUND THEN
1312 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1313 FND_MESSAGE.SET_TOKEN('FK', 'party site id');
1314 FND_MESSAGE.SET_TOKEN('COLUMN', 'party site id');
1315 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_party_sites');
1316 FND_MSG_PUB.ADD;
1317 RAISE FND_API.G_EXC_ERROR;
1318 END;
1319
1320 -- call for validations.
1321 -- Moved the validation for Bug Fix: 3651716
1322 HZ_REGISTRY_VALIDATE_V2PUB.validate_party_site_use(
1323 'U',
1324 p_party_site_use_rec,
1325 l_rowid,
1326 x_return_status);
1327
1328 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1329 RAISE FND_API.G_EXC_ERROR;
1330 END IF;
1331
1332 -- Debug info.
1333 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1334 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Update_Row (+) ',
1335 p_prefix=>l_debug_prefix,
1336 p_msg_level=>fnd_log.level_procedure);
1337 END IF;
1338
1339
1340
1341 -- call to table-handler
1342 HZ_PARTY_SITE_USES_PKG.Update_Row (
1343 X_Rowid => l_rowid,
1344 X_PARTY_SITE_USE_ID => p_party_site_use_rec.party_site_use_id,
1345 X_COMMENTS => p_party_site_use_rec.comments,
1346 X_PARTY_SITE_ID => p_party_site_use_rec.party_site_id,
1347 X_SITE_USE_TYPE => p_party_site_use_rec.site_use_type,
1348 X_PRIMARY_PER_TYPE => p_party_site_use_rec.primary_per_type,
1349 X_STATUS => p_party_site_use_rec.status,
1350 X_OBJECT_VERSION_NUMBER => p_object_version_number,
1351 X_CREATED_BY_MODULE => p_party_site_use_rec.created_by_module,
1352 X_APPLICATION_ID => p_party_site_use_rec.application_id
1353 );
1354
1355 -- Debug info.
1356 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1357 hz_utility_v2pub.debug(p_message=>'HZ_PARTY_SITE_USES_PKG.Update_Row (-) ',
1358 p_prefix=>l_debug_prefix,
1359 p_msg_level=>fnd_log.level_procedure);
1360 END IF;
1361
1362 -- Debug info.
1363 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1364 hz_utility_v2pub.debug(p_message=>'do_update_party_site_use (-)',
1365 p_prefix=>l_debug_prefix,
1366 p_msg_level=>fnd_log.level_procedure);
1367 END IF;
1368
1369 END do_update_party_site_use;
1370
1371
1372 /*===========================================================================+
1373 | PROCEDURE
1374 | do_update_address
1375 |
1376 | DESCRIPTION
1377 | Denormalize identifying address to hz_parties
1378 |
1379 | SCOPE - PRIVATE
1380 |
1381 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1382 |
1383 | ARGUMENTS : IN:
1384 | p_party_id
1385 | p_location_id
1386 | OUT:
1387 | IN/ OUT:
1388 |
1389 | RETURNS : NONE
1390 |
1391 | NOTES
1392 |
1393 | MODIFICATION HISTORY
1394 | 13-APR-2003 P.Suresh o Bug No 2820991. Populated the who columns when
1395 | denormalizing address into hz_parties.
1396 +===========================================================================*/
1397
1398 PROCEDURE do_update_address(
1399 p_party_id IN NUMBER,
1400 p_location_id IN NUMBER
1401 ) IS
1402
1403 CURSOR c_loc IS
1404 SELECT * FROM hz_locations
1405 WHERE location_id = p_location_id;
1406
1407 CURSOR c_party IS
1408 SELECT 'Y'
1409 FROM hz_parties
1410 WHERE party_id = p_party_id
1411 FOR UPDATE NOWAIT;
1412
1413 l_location_rec c_loc%ROWTYPE;
1414 l_exists VARCHAR2(1);
1415
1416 BEGIN
1417
1418 --check if party record is locked by any one else.
1419 BEGIN
1420 OPEN c_party;
1421 FETCH c_party INTO l_exists;
1422 CLOSE c_party;
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1426 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTIES');
1427 FND_MSG_PUB.ADD;
1428 RAISE FND_API.G_EXC_ERROR;
1429 END;
1430
1431 -- if location_id is null, we will null out NOCOPY the location
1432 -- components in hz_parties.
1433
1434 IF p_location_id IS NULL THEN
1435 l_location_rec.country := NULL;
1436 l_location_rec.address1 := NULL;
1437 l_location_rec.address2 := NULL;
1438 l_location_rec.address3 := NULL;
1439 l_location_rec.address4 := NULL;
1440 l_location_rec.city := NULL;
1441 l_location_rec.postal_code := NULL;
1442 l_location_rec.state := NULL;
1443 l_location_rec.province := NULL;
1444 l_location_rec.county := NULL;
1445
1446 -- Bug 2197181: After Mix-n-Match project, the de-normalized address can be a non-USER_ENTERED
1447 -- address. Therefore, commenting out NOCOPY the below statement.
1448
1449 -- l_location_rec.content_source_type := 'USER_ENTERED';
1450 ELSE
1451 --Open the cursor and fetch location components and
1452 --content_source_type.
1453
1454 OPEN c_loc;
1455 FETCH c_loc INTO l_location_rec;
1456 CLOSE c_loc;
1457 END IF;
1458
1459 -- Bug 2197181: After Mix-n-Match project, the de-normalized address can be a non-USER_ENTERED
1460 -- address. Therefore, commenting out NOCOPY the below 'IF' condition.
1461
1462 -- IF l_location_rec.content_source_type = 'USER_ENTERED' THEN
1463
1464 UPDATE hz_parties
1465 SET country = l_location_rec.country,
1466 address1 = l_location_rec.address1,
1467 address2 = l_location_rec.address2,
1468 address3 = l_location_rec.address3,
1469 address4 = l_location_rec.address4,
1470 city = l_location_rec.city,
1471 postal_code = l_location_rec.postal_code,
1472 state = l_location_rec.state,
1473 province = l_location_rec.province,
1474 county = l_location_rec.county,
1475 last_update_date = hz_utility_v2pub.last_update_date,
1476 last_updated_by = hz_utility_v2pub.last_updated_by,
1477 last_update_login = hz_utility_v2pub.last_update_login,
1478 request_id = hz_utility_v2pub.request_id,
1479 program_id = hz_utility_v2pub.program_id,
1480 program_application_id = hz_utility_v2pub.program_application_id,
1481 program_update_date = hz_utility_v2pub.program_update_date
1482 WHERE party_id = p_party_id;
1483
1484 END do_update_address;
1485
1486 /*===========================================================================+
1487 | PROCEDURE
1488 | do_unmark_address_flag
1489 |
1490 | DESCRIPTION
1491 | unmark the identifying_address_flag in hz_party_sites
1492 | for those party sites that are not identifying for
1493 | each party.
1494 |
1495 | SCOPE - PRIVATE
1496 |
1497 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1498 |
1499 | ARGUMENTS : IN:
1500 | p_party_id
1501 | p_party_site_id
1502 | p_mode
1503 | OUT:
1504 | IN/ OUT:
1505 |
1506 | RETURNS : NONE
1507 |
1508 | NOTES
1509 |
1510 | MODIFICATION HISTORY
1511 | Jianying Huang 28-SEP-00 Created. See bug 1403010.
1512 | Jianying Huang 28-FEB-01 Modified the update statement to use rowid
1513 | to do updating.
1514 | Rajib R Borah 16-SEP-03 Updated the who columns.
1515 | Ramesh Ch 28-OCT-03 Removed created_by and creation_date
1516 | columns during update.
1517 | avjha 11-JUL-06 Bug 5203798: Populate BOT incase of direct update.
1518 +===========================================================================*/
1519
1520 PROCEDURE do_unmark_address_flag(
1521 p_party_id IN NUMBER,
1522 p_party_site_id IN NUMBER := NULL,
1523 p_mode IN VARCHAR2 := NULL
1524 ) IS
1525
1526 CURSOR c_party_sites IS
1527 SELECT rowid, party_site_id
1528 FROM hz_party_sites
1529 WHERE party_id = p_party_id
1530 AND party_site_id <> nvl(p_party_site_id,-999)
1531 AND identifying_address_flag = 'Y'
1532 AND rownum = 1
1533 FOR UPDATE NOWAIT;
1534
1535 l_rowid VARCHAR2(100);
1536 l_party_site_id NUMBER;
1537 BEGIN
1538
1539
1540 --check if party record is locked by any one else.
1541 BEGIN
1542 OPEN c_party_sites;
1543 FETCH c_party_sites INTO l_rowid, l_party_site_id;
1544 CLOSE c_party_sites;
1545 EXCEPTION
1546 WHEN OTHERS THEN
1547 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1548 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_SITES');
1549 FND_MSG_PUB.ADD;
1550 RAISE FND_API.G_EXC_ERROR;
1551 END;
1552
1553 IF l_rowid IS NOT NULL THEN
1554 UPDATE hz_party_sites
1555 SET identifying_address_flag= 'N',
1556 --Bug number 2914238 .Updated the who columns.
1557 last_update_date = hz_utility_v2pub.last_update_date,
1558 last_updated_by = hz_utility_v2pub.last_updated_by,
1559 last_update_login = hz_utility_v2pub.last_update_login,
1560 request_id = hz_utility_v2pub.request_id,
1561 program_id = hz_utility_v2pub.program_id,
1562 program_application_id = hz_utility_v2pub.program_application_id,
1563 program_update_date = hz_utility_v2pub.program_update_date
1564 WHERE rowid = l_rowid;
1565
1566 --bug #5203798
1567 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1568 -- populate function for integration service
1569 HZ_POPULATE_BOT_PKG.pop_hz_party_sites(
1570 p_operation => p_mode,
1571 p_party_site_id => l_party_site_id);
1572 END IF;
1573
1574 END IF;
1575
1576 END do_unmark_address_flag;
1577
1578 /*===========================================================================+
1579 | PROCEDURE
1580 | do_unmark_primary_per_type
1581 |
1582 | DESCRIPTION
1583 | unmark the primary_per_type in hz_party_site_uses
1584 | for those site uses that are not primary for
1585 | each party.
1586 |
1587 | SCOPE - PRIVATE
1588 |
1589 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1590 |
1591 | ARGUMENTS : IN:
1592 | p_party_id
1593 | p_party_site_id
1594 | p_site_use_type
1595 | p_mode
1596 | OUT:
1597 | IN/ OUT:
1598 |
1599 | RETURNS : NONE
1600 |
1601 | NOTES
1602 |
1603 | MODIFICATION HISTORY
1604 | 28-OCT-2003 Ramesh Ch Bug#2914238. Updated who columns.
1605 | 11-JUL-06 avjha Bug 5203798: Populate BOT incase of direct update.
1606 |
1607 +===========================================================================*/
1608
1609 PROCEDURE do_unmark_primary_per_type(
1610 p_party_id IN NUMBER,
1611 p_party_site_id IN NUMBER,
1612 p_site_use_type IN VARCHAR2,
1613 p_mode IN VARCHAR2 := NULL
1614 ) IS
1615
1616 CURSOR c_party_site_uses IS
1617 SELECT ROWID, PARTY_SITE_USE_ID
1618 FROM HZ_PARTY_SITE_USES SU
1619 WHERE SU.PARTY_SITE_ID IN (
1620 SELECT PS.PARTY_SITE_ID
1621 FROM HZ_PARTY_SITES PS
1622 WHERE PARTY_ID = p_party_id )
1623 AND SU.PARTY_SITE_ID <> p_party_site_id
1624 AND SU.SITE_USE_TYPE = p_site_use_type
1625 AND SU.PRIMARY_PER_TYPE = 'Y'
1626 AND ROWNUM = 1
1627 FOR UPDATE NOWAIT;
1628
1629 l_rowid VARCHAR2(100);
1630 l_party_site_use_id NUMBER;
1631 BEGIN
1632
1633 -- check if party site use record is locked by any one else.
1634 -- notice the combination of party_site_id and site_use_type
1635 -- is unique.
1636
1637 BEGIN
1638 OPEN c_party_site_uses;
1639 FETCH c_party_site_uses INTO l_rowid, l_party_site_use_id;
1640 CLOSE c_party_site_uses;
1641 EXCEPTION
1642 WHEN OTHERS THEN
1643 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1644 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_SITE_USES');
1645 FND_MSG_PUB.ADD;
1646 RAISE FND_API.G_EXC_ERROR;
1647 END;
1648
1649 IF l_rowid IS NOT NULL THEN
1650 UPDATE HZ_PARTY_SITE_USES
1651 SET PRIMARY_PER_TYPE = 'N',
1652 last_update_date = hz_utility_v2pub.last_update_date,
1653 last_updated_by = hz_utility_v2pub.last_updated_by,
1654 last_update_login = hz_utility_v2pub.last_update_login,
1655 request_id = hz_utility_v2pub.request_id,
1656 program_id = hz_utility_v2pub.program_id,
1657 program_application_id = hz_utility_v2pub.program_application_id,
1658 program_update_date = hz_utility_v2pub.program_update_date
1659 WHERE ROWID = l_rowid;
1660
1661 --bug #5203798
1662 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1663 -- populate function for integration service
1664 HZ_POPULATE_BOT_PKG.pop_hz_party_site_uses(
1665 p_operation => p_mode,
1666 p_party_site_use_id => l_party_site_use_id);
1667 END IF;
1668 END IF;
1669
1670 END do_unmark_primary_per_type;
1671
1672
1673 ----------------------------
1674 -- body of public procedures
1675 ----------------------------
1676
1677 /*===========================================================================+
1678 | PROCEDURE
1679 | create_party_site
1680 |
1681 | DESCRIPTION
1682 | Creates party_site.
1683 |
1684 | SCOPE - PUBLIC
1685 |
1686 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1687 |
1688 | ARGUMENTS : IN:
1689 | p_init_msg_list
1690 | p_party_site_rec
1691 | OUT:
1692 | x_return_status
1693 | x_msg_count
1694 | x_msg_data
1695 | x_party_site_id
1696 | x_party_site_number
1697 | IN/ OUT:
1698 |
1699 | RETURNS : NONE
1700 |
1701 | NOTES
1702 |
1703 | MODIFICATION HISTORY
1704 |
1705 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1706 | For non-profile entities, the concept of
1707 | select/de-select data-sources is obsoleted.
1708 |
1709 +===========================================================================*/
1710
1711 PROCEDURE create_party_site (
1712 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1713 p_party_site_rec IN PARTY_SITE_REC_TYPE,
1714 x_party_site_id OUT NOCOPY NUMBER,
1715 x_party_site_number OUT NOCOPY VARCHAR2,
1716 x_return_status OUT NOCOPY VARCHAR2,
1717 x_msg_count OUT NOCOPY NUMBER,
1718 x_msg_data OUT NOCOPY VARCHAR2
1719 ) IS
1720
1721 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site';
1722 l_party_site_rec PARTY_SITE_REC_TYPE := p_party_site_rec;
1723
1724
1725 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1726 dss_msg_count NUMBER := 0;
1727 dss_msg_data VARCHAR2(2000):= null;
1728 l_test_security VARCHAR2(1):= 'F';
1729 l_debug_prefix VARCHAR2(30) := '';
1730
1731 BEGIN
1732 -- standard start of API savepoint
1733 SAVEPOINT create_party_site;
1734
1735 -- Check if API is called in debug mode. If yes, enable debug.
1736 --enable_debug;
1737
1738 -- Debug info.
1739 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1740 hz_utility_v2pub.debug(p_message=>'create_party_site (+)',
1741 p_prefix=>l_debug_prefix,
1742 p_msg_level=>fnd_log.level_procedure);
1743 END IF;
1744
1745 -- initialize message list if p_init_msg_list is set to TRUE.
1746 IF FND_API.to_Boolean(p_init_msg_list) THEN
1747 FND_MSG_PUB.initialize;
1748 END IF;
1749
1750 -- initialize API return status to success.
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752
1753 -- Bug 2197181: added for mix-n-match project. first load data
1754 -- sources for this entity.
1755
1756 -- Bug 2444678: Removed caching.
1757
1758 -- IF g_pst_mixnmatch_enabled IS NULL THEN
1759 /* SSM SST Integration and Extension
1760 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1761
1762 HZ_MIXNM_UTILITY.LoadDataSources(
1763 p_entity_name => 'HZ_LOCATIONS',
1764 p_entity_attr_id => g_pst_entity_attr_id,
1765 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
1766 p_selected_datasources => g_pst_selected_datasources );
1767 */
1768 -- END IF;
1769
1770 -- report error on obsolete columns based on profile
1771 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
1772 check_obsolete_columns (
1773 p_create_update_flag => 'C',
1774 p_party_site_rec => l_party_site_rec,
1775 x_return_status => x_return_status
1776 );
1777
1778 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1779 RAISE FND_API.G_EXC_ERROR;
1780 END IF;
1781 END IF;
1782
1783 -- call to business logic.
1784 do_create_party_site(
1785 l_party_site_rec,
1786 x_party_site_id,
1787 x_party_site_number,
1788 x_return_status
1789 );
1790
1791 -- Bug 2486394 -Check if the DSS security is granted to the user
1792 -- Bug 3818648: check dss profile before call 3.
1793 --
1794 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
1795 l_test_security :=
1796 hz_dss_util_pub.test_instance(
1797 p_operation_code => 'INSERT',
1798 p_db_object_name => 'HZ_PARTY_SITES',
1799 p_instance_pk1_value => x_party_site_id,
1800 p_user_name => fnd_global.user_name,
1801 x_return_status => dss_return_status,
1802 x_msg_count => dss_msg_count,
1803 x_msg_data => dss_msg_data);
1804
1805 if dss_return_status <> fnd_api.g_ret_sts_success THEN
1806 RAISE FND_API.G_EXC_ERROR;
1807 end if;
1808
1809 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
1810 --
1811 -- Bug 3835601: replaced the dss message with a more user friendly message
1812 --
1813 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_INSERT_PRIVILEGE');
1814 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
1815 fnd_message.get_string('AR', 'HZ_DSS_PARTY_ADDRESSES'));
1816 FND_MSG_PUB.ADD;
1817 RAISE FND_API.G_EXC_ERROR;
1818 end if;
1819 END IF;
1820
1821 -- Invoke business event system.
1822
1823 -- SSM SST Integration and Extension
1824 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
1825 -- There is no need to check if the data-source is selected.
1826
1827 IF x_return_status = FND_API.G_RET_STS_SUCCESS /* AND
1828 -- Bug 2197181: Added below condition for Mix-n-Match
1829 g_pst_is_datasource_selected = 'Y'*/
1830 THEN
1831 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
1832 HZ_BUSINESS_EVENT_V2PVT.create_party_site_event (
1833 l_party_site_rec );
1834 END IF;
1835
1836 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
1837 -- populate function for integration service
1838 HZ_POPULATE_BOT_PKG.pop_hz_party_sites(
1839 p_operation => 'I',
1840 p_party_site_id => x_party_site_id);
1841 END IF;
1842
1843 -- Call to indicate Party Site creation to DQM
1844 --Bug 4866187
1845 --Bug 5370799
1846 IF (p_party_site_rec.orig_system IS NULL OR p_party_site_rec.orig_system=FND_API.G_MISS_CHAR ) THEN
1847 HZ_DQM_SYNC.sync_party_site(l_party_site_rec.party_site_id,'C');
1848 END IF;
1849 END IF;
1850
1851 -- standard call to get message count and if count is 1, get message info.
1852 FND_MSG_PUB.Count_And_Get(
1853 p_encoded => FND_API.G_FALSE,
1854 p_count => x_msg_count,
1855 p_data => x_msg_data);
1856
1857 -- Debug info.
1858 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1859 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1860 p_msg_data=>x_msg_data,
1861 p_msg_type=>'WARNING',
1862 p_msg_level=>fnd_log.level_exception);
1863 END IF;
1864 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1865 hz_utility_v2pub.debug(p_message=>'create_party_site (-)',
1866 p_prefix=>l_debug_prefix,
1867 p_msg_level=>fnd_log.level_procedure);
1868 END IF;
1869
1870 -- Check if API is called in debug mode. If yes, disable debug.
1871 --disable_debug;
1872
1873 EXCEPTION
1874 WHEN FND_API.G_EXC_ERROR THEN
1875 ROLLBACK TO create_party_site;
1876 x_return_status := FND_API.G_RET_STS_ERROR;
1877 FND_MSG_PUB.Count_And_Get(
1878 p_encoded => FND_API.G_FALSE,
1879 p_count => x_msg_count,
1880 p_data => x_msg_data);
1881
1882 -- Debug info.
1883 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1884 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1885 p_msg_data=>x_msg_data,
1886 p_msg_type=>'ERROR',
1887 p_msg_level=>fnd_log.level_error);
1888 END IF;
1889 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1890 hz_utility_v2pub.debug(p_message=>'create_party_site (-)',
1891 p_prefix=>l_debug_prefix,
1892 p_msg_level=>fnd_log.level_procedure);
1893 END IF;
1894
1895 -- Check if API is called in debug mode. If yes, disable debug.
1896 --disable_debug;
1897
1898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1899 ROLLBACK TO create_party_site;
1900 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1901 FND_MSG_PUB.Count_And_Get(
1902 p_encoded => FND_API.G_FALSE,
1903 p_count => x_msg_count,
1904 p_data => x_msg_data);
1905
1906 -- Debug info.
1907 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1908 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1909 p_msg_data=>x_msg_data,
1910 p_msg_type=>'UNEXPECTED ERROR',
1911 p_msg_level=>fnd_log.level_error);
1912 END IF;
1913 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1914 hz_utility_v2pub.debug(p_message=>'create_party_site (-)',
1915 p_prefix=>l_debug_prefix,
1916 p_msg_level=>fnd_log.level_procedure);
1917 END IF;
1918
1919 -- Check if API is called in debug mode. If yes, disable debug.
1920 --disable_debug;
1921
1922 WHEN OTHERS THEN
1923 ROLLBACK TO create_party_site;
1924 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1926 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1927 FND_MSG_PUB.ADD;
1928 FND_MSG_PUB.Count_And_Get(
1929 p_encoded => FND_API.G_FALSE,
1930 p_count => x_msg_count,
1931 p_data => x_msg_data);
1932
1933 -- Debug info.
1934 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1935 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1936 p_msg_data=>x_msg_data,
1937 p_msg_type=>'SQL ERROR',
1938 p_msg_level=>fnd_log.level_error);
1939 END IF;
1940 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1941 hz_utility_v2pub.debug(p_message=>'create_party_site (-)',
1942 p_prefix=>l_debug_prefix,
1943 p_msg_level=>fnd_log.level_procedure);
1944 END IF;
1945
1946 -- Check if API is called in debug mode. If yes, disable debug.
1947 --disable_debug;
1948
1949 END create_party_site;
1950
1951
1952 /*===========================================================================+
1953 | PROCEDURE
1954 | update_party_site
1955 |
1956 | DESCRIPTION
1957 | Updates party_site.
1958 |
1959 | SCOPE - PUBLIC
1960 |
1961 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
1962 |
1963 | ARGUMENTS : IN:
1964 | p_init_msg_list
1965 | p_party_site_rec
1966 | OUT:
1967 | x_return_status
1968 | x_msg_count
1969 | x_msg_data
1970 | IN/ OUT:
1971 |
1972 | RETURNS : NONE
1973 |
1974 | NOTES
1975 |
1976 | MODIFICATION HISTORY
1977 |
1978 | 04-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
1979 | For non-profile entities, the concept of
1980 | select/de-select data-sources is obsoleted.
1981 +===========================================================================*/
1982
1983 PROCEDURE update_party_site (
1984 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1985 p_party_site_rec IN PARTY_SITE_REC_TYPE,
1986 p_object_version_number IN OUT NOCOPY NUMBER,
1987 x_return_status OUT NOCOPY VARCHAR2,
1988 x_msg_count OUT NOCOPY NUMBER,
1989 x_msg_data OUT NOCOPY VARCHAR2
1990 ) IS
1991
1992 l_api_name CONSTANT VARCHAR2(30) := 'update_party_site';
1993 l_party_site_rec PARTY_SITE_REC_TYPE := p_party_site_rec;
1994 l_old_party_site_rec PARTY_SITE_REC_TYPE;
1995
1996 dss_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1997 dss_msg_count NUMBER := 0;
1998 dss_msg_data VARCHAR2(2000):= null;
1999 l_test_security VARCHAR2(1):= 'F';
2000 l_release_name VARCHAR2(50);
2001 l_dummy VARCHAR2(1);
2002 l_debug_prefix VARCHAR2(30) := '';
2003 BEGIN
2004
2005 -- standard start of API savepoint
2006 SAVEPOINT update_party_site;
2007
2008 -- Check if API is called in debug mode. If yes, enable debug.
2009 --enable_debug;
2010
2011 -- Debug info.
2012 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2013 hz_utility_v2pub.debug(p_message=>'update_party_site (+)',
2014 p_prefix=>l_debug_prefix,
2015 p_msg_level=>fnd_log.level_procedure);
2016 END IF;
2017
2018 -- initialize message list if p_init_msg_list is set to TRUE.
2019 IF FND_API.to_Boolean(p_init_msg_list) THEN
2020 FND_MSG_PUB.initialize;
2021 END IF;
2022
2023 -- initialize API return status to success.
2024 x_return_status := FND_API.G_RET_STS_SUCCESS;
2025
2026 -- if party_site_id is not passed in, but orig system parameters are passed in
2027 -- get party_id
2028
2029 IF (p_party_site_rec.orig_system is not null
2030 and p_party_site_rec.orig_system <>fnd_api.g_miss_char)
2031 and (p_party_site_rec.orig_system_reference is not null
2032 and p_party_site_rec.orig_system_reference <>fnd_api.g_miss_char)
2033 and (p_party_site_rec.party_site_id = FND_API.G_MISS_NUM or p_party_site_rec.party_site_id is null) THEN
2034 hz_orig_system_ref_pub.get_owner_table_id
2035 (p_orig_system => p_party_site_rec.orig_system,
2036 p_orig_system_reference => p_party_site_rec.orig_system_reference,
2037 p_owner_table_name => 'HZ_PARTY_SITES',
2038 x_owner_table_id => l_party_site_rec.party_site_id,
2039 x_return_status => x_return_status);
2040 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2041 RAISE FND_API.G_EXC_ERROR;
2042 END IF;
2043 END IF;
2044
2045
2046 -- Get old records. Will be used by business event system.
2047 get_party_site_rec (
2048 p_party_site_id => l_party_site_rec.party_site_id,
2049 x_party_site_rec => l_old_party_site_rec,
2050 x_return_status => x_return_status,
2051 x_msg_count => x_msg_count,
2052 x_msg_data => x_msg_data );
2053
2054 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2055 RAISE FND_API.G_EXC_ERROR;
2056 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2058 END IF;
2059
2060 --- Bug 2486394 Check if the DSS security is granted to the user
2061 -- Bug 3818648: check dss profile before call test_instance.
2062 --
2063 IF NVL(fnd_profile.value('HZ_DSS_ENABLED'), 'N') = 'Y' THEN
2064 l_test_security :=
2065 hz_dss_util_pub.test_instance(
2066 p_operation_code => 'UPDATE',
2067 p_db_object_name => 'HZ_PARTY_SITES',
2068 p_instance_pk1_value => l_party_site_rec.party_site_id,
2069 p_user_name => fnd_global.user_name,
2070 x_return_status => dss_return_status,
2071 x_msg_count => dss_msg_count,
2072 x_msg_data => dss_msg_data);
2073
2074 if dss_return_status <> fnd_api.g_ret_sts_success THEN
2075 RAISE FND_API.G_EXC_ERROR;
2076 end if;
2077
2078 if (l_test_security <> 'T' OR l_test_security <> FND_API.G_TRUE) then
2079 --
2080 -- Bug 3835601: replaced the dss message with a more user friendly message
2081 --
2082 FND_MESSAGE.SET_NAME('AR', 'HZ_DSS_NO_UPDATE_PRIVILEGE');
2083 FND_MESSAGE.SET_TOKEN('ENTITY_NAME',
2084 fnd_message.get_string('AR', 'HZ_DSS_PARTY_ADDRESSES'));
2085 FND_MSG_PUB.ADD;
2086 RAISE FND_API.G_EXC_ERROR;
2087 end if;
2088 END IF;
2089
2090 -- Bug 2197181: added for mix-n-match project. first load data
2091 -- sources for this entity.
2092
2093 -- Bug 2444678: Removed caching.
2094
2095 -- IF g_pst_mixnmatch_enabled IS NULL THEN
2096 /* SSM SST Integration and Extension
2097 * For non-profile entities, the concept of select/de-select data-sources is obsoleted.
2098
2099 HZ_MIXNM_UTILITY.LoadDataSources(
2100 p_entity_name => 'HZ_LOCATIONS',
2101 p_entity_attr_id => g_pst_entity_attr_id,
2102 p_mixnmatch_enabled => g_pst_mixnmatch_enabled,
2103 p_selected_datasources => g_pst_selected_datasources );
2104 */
2105 -- END IF;
2106
2107 -- report error on obsolete columns based on profile
2108 IF NVL(FND_PROFILE.VALUE('HZ_API_ERR_ON_OBSOLETE_COLUMN'), 'Y') = 'Y' THEN
2109 check_obsolete_columns (
2110 p_create_update_flag => 'U',
2111 p_party_site_rec => l_party_site_rec,
2112 p_old_party_site_rec => l_old_party_site_rec,
2113 x_return_status => x_return_status
2114 );
2115
2116 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119 END IF;
2120
2121 -- call to business logic.
2122 do_update_party_site(
2123 l_party_site_rec,
2124 p_object_version_number,
2125 x_return_status
2126 );
2127
2128 -- Invoke business event system.
2129 -- SSM SST Integration and Extension
2130 -- For non-profile entities, the concept of select/de-select data-sources is obsoleted.
2131 -- There is no need to check if the data-source is selected.
2132
2133 IF x_return_status = FND_API.G_RET_STS_SUCCESS /*AND
2134 -- Bug 2197181: added for mix-n-match project.
2135 g_pst_is_datasource_selected = 'Y'*/
2136 THEN
2137 l_old_party_site_rec.orig_system := l_party_site_rec.orig_system;
2138 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2139 HZ_BUSINESS_EVENT_V2PVT.update_party_site_event (
2140 l_party_site_rec,
2141 l_old_party_site_rec );
2142 END IF;
2143
2144 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2145 -- populate function for integration service
2146 HZ_POPULATE_BOT_PKG.pop_hz_party_sites(
2147 p_operation => 'U',
2148 p_party_site_id => l_party_site_rec.party_site_id);
2149 END IF;
2150
2151 -- Call to indicate Party Site update to DQM
2152 HZ_DQM_SYNC.sync_party_site(l_party_site_rec.party_site_id,'U');
2153 END IF;
2154
2155 --
2156 --- Check if 11.5.10 is installed in the system;if yes,then check for
2157 --- party site status in old and new record type.If changed then call
2158 --- update_acct_sites_status to update account site,site uses status
2159 --
2160 IF l_party_site_rec.status <> l_old_party_site_rec.status THEN
2161 -- Code modified for Bug 3714636 starts here
2162 IF(g_message_name is null) THEN
2163 BEGIN
2164 SELECT 'X' into g_message_name FROM FND_NEW_MESSAGES
2165 WHERE message_name = 'HZ_INACTIVATE_ACCOUNT_SITE_UI'
2166 AND language_code = userenv('LANG')
2167 AND application_id = 222
2168 AND rownum =1;
2169 EXCEPTION
2170 WHEN NO_DATA_FOUND THEN
2171 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2172 FND_MESSAGE.SET_TOKEN('RECORD', 'Message Name');
2173 --FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_use_rec.party_site_id), 'null'));
2174 FND_MSG_PUB.ADD;
2175 RAISE FND_API.G_EXC_ERROR;
2176 END;
2177 END IF;
2178 --IF l_release_name not in ( '11.5.1','11.5.2','11.5.3','11.5.4','11.5.5','11.5.6','11.5.7','11.5.8','11.5.9') THEN
2179 IF g_message_name IS NOT NULL THEN
2180 -- Code modified for Bug 3714636 ends here
2181 update_acct_sites_status(l_party_site_rec.party_site_id,
2182 l_party_site_rec.status,
2183 x_return_status);
2184 -- IF x_return_status <> fnd_api.g_ret_sts_success THEN
2185 -- RAISE FND_API.G_EXC_ERROR;
2186 -- END IF;
2187
2188 END IF;
2189 END IF;
2190
2191 -- standard call to get message count and if count is 1, get message info.
2192 FND_MSG_PUB.Count_And_Get(
2193 p_encoded => FND_API.G_FALSE,
2194 p_count => x_msg_count,
2195 p_data => x_msg_data);
2196
2197
2198 -- Debug info.
2199 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2200 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2201 p_msg_data=>x_msg_data,
2202 p_msg_type=>'WARNING',
2203 p_msg_level=>fnd_log.level_exception);
2204 END IF;
2205 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2206 hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
2207 p_prefix=>l_debug_prefix,
2208 p_msg_level=>fnd_log.level_procedure);
2209 END IF;
2210
2211 -- Check if API is called in debug mode. If yes, disable debug.
2212 --disable_debug;
2213
2214 EXCEPTION
2215 WHEN FND_API.G_EXC_ERROR THEN
2216 ROLLBACK TO update_party_site;
2217 x_return_status := FND_API.G_RET_STS_ERROR;
2218 FND_MSG_PUB.Count_And_Get(
2219 p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222
2223 -- Debug info.
2224 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2225 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2226 p_msg_data=>x_msg_data,
2227 p_msg_type=>'ERROR',
2228 p_msg_level=>fnd_log.level_error);
2229 END IF;
2230 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2231 hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
2232 p_prefix=>l_debug_prefix,
2233 p_msg_level=>fnd_log.level_procedure);
2234 END IF;
2235
2236
2237 -- Check if API is called in debug mode. If yes, disable debug.
2238 --disable_debug;
2239
2240 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2241 ROLLBACK TO update_party_site;
2242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2243 FND_MSG_PUB.Count_And_Get(
2244 p_encoded => FND_API.G_FALSE,
2245 p_count => x_msg_count,
2246 p_data => x_msg_data);
2247
2248 -- Debug info.
2249 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2250 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2251 p_msg_data=>x_msg_data,
2252 p_msg_type=>'UNEXPECTED ERROR',
2253 p_msg_level=>fnd_log.level_error);
2254 END IF;
2255 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2256 hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
2257 p_prefix=>l_debug_prefix,
2258 p_msg_level=>fnd_log.level_procedure);
2259 END IF;
2260
2261 -- Check if API is called in debug mode. If yes, disable debug.
2262 --disable_debug;
2263
2264 WHEN OTHERS THEN
2265 ROLLBACK TO update_party_site;
2266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2268 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2269 FND_MSG_PUB.ADD;
2270 FND_MSG_PUB.Count_And_Get(
2271 p_encoded => FND_API.G_FALSE,
2272 p_count => x_msg_count,
2273 p_data => x_msg_data);
2274
2275 -- Debug info.
2276 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2277 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2278 p_msg_data=>x_msg_data,
2279 p_msg_type=>'SQL ERROR',
2280 p_msg_level=>fnd_log.level_error);
2281 END IF;
2282 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2283 hz_utility_v2pub.debug(p_message=>'update_party_site (-)',
2284 p_prefix=>l_debug_prefix,
2285 p_msg_level=>fnd_log.level_procedure);
2286 END IF;
2287
2288 -- Check if API is called in debug mode. If yes, disable debug.
2289 --disable_debug;
2290
2291 END update_party_site;
2292
2293
2294 /*===========================================================================+
2295 | PROCEDURE
2296 | create_party_site_use
2297 |
2298 | DESCRIPTION
2299 | Creates party_site_use.
2300 |
2301 | SCOPE - PUBLIC
2302 |
2303 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2304 |
2305 | ARGUMENTS : IN:
2306 | p_init_msg_list
2307 | p_party_site_use_rec
2308 | OUT:
2309 | x_return_status
2310 | x_msg_count
2311 | x_msg_data
2312 | x_party_site_use_id
2313 | IN/ OUT:
2314 |
2315 | RETURNS : NONE
2316 |
2317 | NOTES
2318 |
2319 | MODIFICATION HISTORY
2320 | Rashmi Goyal 31-AUG-99 Created
2321 |
2322 +===========================================================================*/
2323
2324 PROCEDURE create_party_site_use (
2325 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2326 p_party_site_use_rec IN PARTY_SITE_USE_REC_TYPE,
2327 x_party_site_use_id OUT NOCOPY NUMBER,
2328 x_return_status OUT NOCOPY VARCHAR2,
2329 x_msg_count OUT NOCOPY NUMBER,
2330 x_msg_data OUT NOCOPY VARCHAR2
2331 ) IS
2332
2333 l_api_name CONSTANT VARCHAR2(30) := 'create_party_site_use';
2334 l_party_site_use_rec PARTY_SITE_USE_REC_TYPE := p_party_site_use_rec;
2335 l_debug_prefix VARCHAR2(30) := '';
2336
2337 BEGIN
2338
2339 -- standard start of API savepoint
2340 SAVEPOINT create_party_site_use;
2341
2342 -- Check if API is called in debug mode. If yes, enable debug.
2343 --enable_debug;
2344
2345 -- Debug info.
2346 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2347 hz_utility_v2pub.debug(p_message=>'create_party_site_use (+)',
2348 p_prefix=>l_debug_prefix,
2349 p_msg_level=>fnd_log.level_procedure);
2350 END IF;
2351
2352
2353 -- initialize message list if p_init_msg_list is set to TRUE.
2354 IF FND_API.to_Boolean(p_init_msg_list) THEN
2355 FND_MSG_PUB.initialize;
2356 END IF;
2357
2358 -- initialize API return status to success.
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
2360
2361 -- call to business logic.
2362 do_create_party_site_use(
2363 l_party_site_use_rec,
2364 x_party_site_use_id,
2365 x_return_status
2366 );
2367
2368 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2369 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2370 -- Invoke business event system.
2371 HZ_BUSINESS_EVENT_V2PVT.create_party_site_use_event (
2372 l_party_site_use_rec );
2373 END IF;
2374
2375 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2376 -- populate function for integration service
2377 HZ_POPULATE_BOT_PKG.pop_hz_party_site_uses(
2378 p_operation => 'I',
2379 p_party_site_use_id => x_party_site_use_id);
2380 END IF;
2381 END IF;
2382
2383 -- standard call to get message count and if count is 1, get message info.
2384 FND_MSG_PUB.Count_And_Get(
2385 p_encoded => FND_API.G_FALSE,
2386 p_count => x_msg_count,
2387 p_data => x_msg_data);
2388
2389 -- Debug info.
2390 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2391 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2392 p_msg_data=>x_msg_data,
2393 p_msg_type=>'WARNING',
2394 p_msg_level=>fnd_log.level_exception);
2395 END IF;
2396 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2397 hz_utility_v2pub.debug(p_message=>'create_party_site_use (-)',
2398 p_prefix=>l_debug_prefix,
2399 p_msg_level=>fnd_log.level_procedure);
2400 END IF;
2401
2402 -- Check if API is called in debug mode. If yes, disable debug.
2403 --disable_debug;
2404
2405 EXCEPTION
2406 WHEN FND_API.G_EXC_ERROR THEN
2407 ROLLBACK TO create_party_site_use;
2408 x_return_status := FND_API.G_RET_STS_ERROR;
2409 FND_MSG_PUB.Count_And_Get(
2410 p_encoded => FND_API.G_FALSE,
2411 p_count => x_msg_count,
2412 p_data => x_msg_data);
2413
2414 -- Debug info.
2415 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2416 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2417 p_msg_data=>x_msg_data,
2418 p_msg_type=>'ERROR',
2419 p_msg_level=>fnd_log.level_error);
2420 END IF;
2421 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2422 hz_utility_v2pub.debug(p_message=>'create_party_site_use (-)',
2423 p_prefix=>l_debug_prefix,
2424 p_msg_level=>fnd_log.level_procedure);
2425 END IF;
2426
2427 -- Check if API is called in debug mode. If yes, disable debug.
2428 --disable_debug;
2429
2430 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2431 ROLLBACK TO create_party_site_use;
2432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2433 FND_MSG_PUB.Count_And_Get(
2434 p_encoded => FND_API.G_FALSE,
2435 p_count => x_msg_count,
2436 p_data => x_msg_data);
2437
2438 -- Debug info.
2439 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2440 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2441 p_msg_data=>x_msg_data,
2442 p_msg_type=>'UNEXPECTED ERROR',
2443 p_msg_level=>fnd_log.level_error);
2444 END IF;
2445 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2446 hz_utility_v2pub.debug(p_message=>'create_party_site_use (-)',
2447 p_prefix=>l_debug_prefix,
2448 p_msg_level=>fnd_log.level_procedure);
2449 END IF;
2450
2451 -- Check if API is called in debug mode. If yes, disable debug.
2452 --disable_debug;
2453
2454 WHEN OTHERS THEN
2455 ROLLBACK TO create_party_site_use;
2456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2457 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2458 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2459 FND_MSG_PUB.ADD;
2460 FND_MSG_PUB.Count_And_Get(
2461 p_encoded => FND_API.G_FALSE,
2462 p_count => x_msg_count,
2463 p_data => x_msg_data);
2464
2465 -- Debug info.
2466 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2467 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2468 p_msg_data=>x_msg_data,
2469 p_msg_type=>'SQL ERROR',
2470 p_msg_level=>fnd_log.level_error);
2471 END IF;
2472 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2473 hz_utility_v2pub.debug(p_message=>'create_party_site_use (-)',
2474 p_prefix=>l_debug_prefix,
2475 p_msg_level=>fnd_log.level_procedure);
2476 END IF;
2477
2478 -- Check if API is called in debug mode. If yes, disable debug.
2479 --disable_debug;
2480
2481 END create_party_site_use;
2482
2483
2484 /*===========================================================================+
2485 | PROCEDURE
2486 | update_party_site_use
2487 |
2488 | DESCRIPTION
2489 | Updates party_site_use.
2490 |
2491 | SCOPE - PUBLIC
2492 |
2493 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2494 |
2495 | ARGUMENTS : IN:
2496 | p_init_msg_list
2497 | p_party_site_use_rec
2498 | OUT:
2499 | x_return_status
2500 | x_msg_count
2501 | x_msg_data
2502 | IN/ OUT:
2503 |
2504 | RETURNS : NONE
2505 |
2506 | NOTES
2507 |
2508 | MODIFICATION HISTORY
2509 |
2510 +===========================================================================*/
2511
2512 PROCEDURE update_party_site_use (
2513 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2514 p_party_site_use_rec IN PARTY_SITE_USE_REC_TYPE,
2515 p_object_version_number IN OUT NOCOPY NUMBER,
2516 x_return_status OUT NOCOPY VARCHAR2,
2517 x_msg_count OUT NOCOPY NUMBER,
2518 x_msg_data OUT NOCOPY VARCHAR2
2519 ) IS
2520
2521 l_api_name CONSTANT VARCHAR2(30) := 'update_party_site_use';
2522 l_party_site_use_rec PARTY_SITE_USE_REC_TYPE := p_party_site_use_rec;
2523 l_old_party_site_use_rec PARTY_SITE_USE_REC_TYPE;
2524 l_debug_prefix VARCHAR2(30) := '';
2525
2526 BEGIN
2527
2528 -- standard start of API savepoint
2529 SAVEPOINT update_party_site_use;
2530
2531 -- Check if API is called in debug mode. If yes, enable debug.
2532 --enable_debug;
2533
2534 -- Debug info.
2535 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2536 hz_utility_v2pub.debug(p_message=>'update_party_site_use (+)',
2537 p_prefix=>l_debug_prefix,
2538 p_msg_level=>fnd_log.level_procedure);
2539 END IF;
2540
2541 -- initialize message list if p_init_msg_list is set to TRUE.
2542 IF FND_API.to_Boolean(p_init_msg_list) THEN
2543 FND_MSG_PUB.initialize;
2544 END IF;
2545
2546 -- initialize API return status to success.
2547 x_return_status := FND_API.G_RET_STS_SUCCESS;
2548
2549 --2290537
2550 get_party_site_use_rec (
2551 p_party_site_use_id => p_party_site_use_rec.party_site_use_id,
2552 x_party_site_use_rec => l_old_party_site_use_rec,
2553 x_return_status => x_return_status,
2554 x_msg_count => x_msg_count,
2555 x_msg_data => x_msg_data);
2556
2557 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2558 RAISE FND_API.G_EXC_ERROR;
2559 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2561 END IF;
2562
2563 -- call to business logic.
2564 do_update_party_site_use(
2565 l_party_site_use_rec,
2566 p_object_version_number,
2567 x_return_status
2568 );
2569
2570 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2571 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'Y')) THEN
2572 -- Invoke business event system.
2573 HZ_BUSINESS_EVENT_V2PVT.update_party_site_use_event (
2574 l_party_site_use_rec , l_old_party_site_use_rec );
2575 END IF;
2576
2577 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
2578 -- populate function for integration service
2579 HZ_POPULATE_BOT_PKG.pop_hz_party_site_uses(
2580 p_operation => 'U',
2581 p_party_site_use_id => l_party_site_use_rec.party_site_use_id);
2582 END IF;
2583 END IF;
2584
2585 -- standard call to get message count and if count is 1, get message info.
2586 FND_MSG_PUB.Count_And_Get(
2587 p_encoded => FND_API.G_FALSE,
2588 p_count => x_msg_count,
2589 p_data => x_msg_data);
2590
2591 -- Debug info.
2592 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2593 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2594 p_msg_data=>x_msg_data,
2595 p_msg_type=>'WARNING',
2596 p_msg_level=>fnd_log.level_exception);
2597 END IF;
2598 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2599 hz_utility_v2pub.debug(p_message=> 'update_party_site_use (-)',
2600 p_prefix=>l_debug_prefix,
2601 p_msg_level=>fnd_log.level_procedure);
2602 END IF;
2603
2604 -- Check if API is called in debug mode. If yes, disable debug.
2605 --disable_debug;
2606
2607 EXCEPTION
2608 WHEN FND_API.G_EXC_ERROR THEN
2609 ROLLBACK TO update_party_site_use;
2610 x_return_status := FND_API.G_RET_STS_ERROR;
2611 FND_MSG_PUB.Count_And_Get(
2612 p_encoded => FND_API.G_FALSE,
2613 p_count => x_msg_count,
2614 p_data => x_msg_data);
2615
2616 -- Debug info.
2617 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2618 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2619 p_msg_data=>x_msg_data,
2620 p_msg_type=>'ERROR',
2621 p_msg_level=>fnd_log.level_error);
2622 END IF;
2623 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2624 hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
2625 p_prefix=>l_debug_prefix,
2626 p_msg_level=>fnd_log.level_procedure);
2627 END IF;
2628
2629 -- Check if API is called in debug mode. If yes, disable debug.
2630 --disable_debug;
2631
2632 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2633 ROLLBACK TO update_party_site_use;
2634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2635 FND_MSG_PUB.Count_And_Get(
2636 p_encoded => FND_API.G_FALSE,
2637 p_count => x_msg_count,
2638 p_data => x_msg_data);
2639
2640 -- Debug info.
2641 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2642 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2643 p_msg_data=>x_msg_data,
2644 p_msg_type=>'UNEXPECTED ERROR',
2645 p_msg_level=>fnd_log.level_error);
2646 END IF;
2647 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2648 hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
2649 p_prefix=>l_debug_prefix,
2650 p_msg_level=>fnd_log.level_procedure);
2651 END IF;
2652
2653 -- Check if API is called in debug mode. If yes, disable debug.
2654 --disable_debug;
2655
2656 WHEN OTHERS THEN
2657 ROLLBACK TO update_party_site_use;
2658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2659 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2660 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2661 FND_MSG_PUB.ADD;
2662 FND_MSG_PUB.Count_And_Get(
2663 p_encoded => FND_API.G_FALSE,
2664 p_count => x_msg_count,
2665 p_data => x_msg_data);
2666
2667 -- Debug info.
2668 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2669 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2670 p_msg_data=>x_msg_data,
2671 p_msg_type=>'SQL ERROR',
2672 p_msg_level=>fnd_log.level_error);
2673 END IF;
2674 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2675 hz_utility_v2pub.debug(p_message=>'update_party_site_use (-)',
2676 p_prefix=>l_debug_prefix,
2677 p_msg_level=>fnd_log.level_procedure);
2678 END IF;
2679
2680 -- Check if API is called in debug mode. If yes, disable debug.
2681 --disable_debug;
2682
2683 END update_party_site_use;
2684
2685
2686 /*===========================================================================+
2687 | PROCEDURE
2688 | get_party_site_rec
2689 |
2690 | DESCRIPTION
2691 | Gets current record.
2692 |
2693 | SCOPE - PRIVATE
2694 |
2695 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2696 |
2697 | ARGUMENTS : IN:
2698 | p_init_msg_list
2699 | p_party_site_id
2700 | OUT:
2701 | x_party_site_rec
2702 | x_return_status
2703 | x_msg_count
2704 | x_msg_data
2705 | IN/ OUT:
2706 |
2707 | RETURNS : NONE
2708 |
2709 | NOTES
2710 |
2711 | MODIFICATION HISTORY
2712 |
2713 | 20-APR-2004 Rajib Ranjan Borah o Bug 3175816. Added global_location_number
2714 | to HZ_PARTY_SITES.
2715 +===========================================================================*/
2716
2717 PROCEDURE get_party_site_rec (
2718 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2719 p_party_site_id IN NUMBER,
2720 x_party_site_rec OUT NOCOPY PARTY_SITE_REC_TYPE,
2721 x_return_status OUT NOCOPY VARCHAR2,
2722 x_msg_count OUT NOCOPY NUMBER,
2723 x_msg_data OUT NOCOPY VARCHAR2
2724 ) IS
2725
2726 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
2727 l_actual_content_source VARCHAR2(30);
2728
2729 BEGIN
2730
2731 --Initialize message list if p_init_msg_list is set to TRUE.
2732 IF FND_API.to_Boolean(p_init_msg_list) THEN
2733 FND_MSG_PUB.initialize;
2734 END IF;
2735
2736 --Initialize API return status to success.
2737 x_return_status := FND_API.G_RET_STS_SUCCESS;
2738
2739 --Check whether primary key has been passed in.
2740 IF p_party_site_id IS NULL OR
2741 p_party_site_id = FND_API.G_MISS_NUM THEN
2742 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2743 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'party_site_id' );
2744 FND_MSG_PUB.ADD;
2745 RAISE FND_API.G_EXC_ERROR;
2746 END IF;
2747
2748 x_party_site_rec.party_site_id := p_party_site_id;
2749
2750 HZ_PARTY_SITES_PKG.Select_Row (
2751 X_PARTY_SITE_ID => x_party_site_rec.party_site_id,
2752 X_PARTY_ID => x_party_site_rec.party_id,
2753 X_LOCATION_ID => x_party_site_rec.location_id,
2754 X_PARTY_SITE_NUMBER => x_party_site_rec.party_site_number,
2755 X_ATTRIBUTE_CATEGORY => x_party_site_rec.attribute_category,
2756 X_ATTRIBUTE1 => x_party_site_rec.attribute1,
2757 X_ATTRIBUTE2 => x_party_site_rec.attribute2,
2758 X_ATTRIBUTE3 => x_party_site_rec.attribute3,
2759 X_ATTRIBUTE4 => x_party_site_rec.attribute4,
2760 X_ATTRIBUTE5 => x_party_site_rec.attribute5,
2761 X_ATTRIBUTE6 => x_party_site_rec.attribute6,
2762 X_ATTRIBUTE7 => x_party_site_rec.attribute7,
2763 X_ATTRIBUTE8 => x_party_site_rec.attribute8,
2764 X_ATTRIBUTE9 => x_party_site_rec.attribute9,
2765 X_ATTRIBUTE10 => x_party_site_rec.attribute10,
2766 X_ATTRIBUTE11 => x_party_site_rec.attribute11,
2767 X_ATTRIBUTE12 => x_party_site_rec.attribute12,
2768 X_ATTRIBUTE13 => x_party_site_rec.attribute13,
2769 X_ATTRIBUTE14 => x_party_site_rec.attribute14,
2770 X_ATTRIBUTE15 => x_party_site_rec.attribute15,
2771 X_ATTRIBUTE16 => x_party_site_rec.attribute16,
2772 X_ATTRIBUTE17 => x_party_site_rec.attribute17,
2773 X_ATTRIBUTE18 => x_party_site_rec.attribute18,
2774 X_ATTRIBUTE19 => x_party_site_rec.attribute19,
2775 X_ATTRIBUTE20 => x_party_site_rec.attribute20,
2776 X_ORIG_SYSTEM_REFERENCE => x_party_site_rec.orig_system_reference,
2777 X_LANGUAGE => x_party_site_rec.language,
2778 X_MAILSTOP => x_party_site_rec.mailstop,
2779 X_IDENTIFYING_ADDRESS_FLAG => x_party_site_rec.identifying_address_flag,
2780 X_STATUS => x_party_site_rec.status,
2781 X_PARTY_SITE_NAME => x_party_site_rec.party_site_name,
2782 X_ADDRESSEE => x_party_site_rec.addressee,
2783 X_CREATED_BY_MODULE => x_party_site_rec.created_by_module,
2784 X_APPLICATION_ID => x_party_site_rec.application_id,
2785 X_ACTUAL_CONTENT_SOURCE => l_actual_content_source,
2786 X_GLOBAL_LOCATION_NUMBER => x_party_site_rec.global_location_number /* Bug 3175816 */,
2787 X_DUNS_NUMBER_C => x_party_site_rec.duns_number_c
2788 );
2789
2790 --Standard call to get message count and if count is 1, get message info.
2791 FND_MSG_PUB.Count_And_Get(
2792 p_encoded => FND_API.G_FALSE,
2793 p_count => x_msg_count,
2794 p_data => x_msg_data );
2795
2796 EXCEPTION
2797 WHEN FND_API.G_EXC_ERROR THEN
2798 x_return_status := FND_API.G_RET_STS_ERROR;
2799
2800 FND_MSG_PUB.Count_And_Get(
2801 p_encoded => FND_API.G_FALSE,
2802 p_count => x_msg_count,
2803 p_data => x_msg_data );
2804
2805 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2807
2808 FND_MSG_PUB.Count_And_Get(
2809 p_encoded => FND_API.G_FALSE,
2810 p_count => x_msg_count,
2811 p_data => x_msg_data );
2812
2813 WHEN OTHERS THEN
2814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2815 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2816 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2817 FND_MSG_PUB.ADD;
2818 FND_MSG_PUB.Count_And_Get(
2819 p_encoded => FND_API.G_FALSE,
2820 p_count => x_msg_count,
2821 p_data => x_msg_data );
2822
2823 END get_party_site_rec;
2824
2825
2826
2827 /*===========================================================================+
2828 | PROCEDURE
2829 | get_party_site_use_rec
2830 |
2831 | DESCRIPTION
2832 | Gets current record.
2833 |
2834 | SCOPE - PRIVATE
2835 |
2836 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2837 |
2838 | ARGUMENTS : IN:
2839 | p_init_msg_list
2840 | p_party_site_id
2841 | OUT:
2842 | x_party_site_rec
2843 | x_return_status
2844 | x_msg_count
2845 | x_msg_data
2846 | IN/ OUT:
2847 |
2848 | RETURNS : NONE
2849 |
2850 | NOTES
2851 |
2852 | MODIFICATION HISTORY
2853 |
2854 +===========================================================================*/
2855
2856 PROCEDURE get_party_site_use_rec (
2857 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2858 p_party_site_use_id IN NUMBER,
2859 x_party_site_use_rec OUT NOCOPY PARTY_SITE_USE_REC_TYPE,
2860 x_return_status OUT NOCOPY VARCHAR2,
2861 x_msg_count OUT NOCOPY NUMBER,
2862 x_msg_data OUT NOCOPY VARCHAR2
2863 ) IS
2864
2865 l_api_name CONSTANT VARCHAR2(30) := 'get_party_site_rec';
2866
2867 BEGIN
2868
2869 --Initialize message list if p_init_msg_list is set to TRUE.
2870 IF FND_API.to_Boolean(p_init_msg_list) THEN
2871 FND_MSG_PUB.initialize;
2872 END IF;
2873
2874 --Initialize API return status to success.
2875 x_return_status := FND_API.G_RET_STS_SUCCESS;
2876
2877 --Check whether primary key has been passed in.
2878 IF p_party_site_use_id IS NULL OR
2879 p_party_site_use_id = FND_API.G_MISS_NUM THEN
2880 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
2881 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'p_party_site_use_id' );
2882 FND_MSG_PUB.ADD;
2883 RAISE FND_API.G_EXC_ERROR;
2884 END IF;
2885
2886 x_party_site_use_rec.party_site_use_id := p_party_site_use_id;
2887
2888 HZ_PARTY_SITE_USES_PKG.Select_Row (
2889 X_PARTY_SITE_USE_ID => x_party_site_use_rec.party_site_use_id,
2890 X_COMMENTS => x_party_site_use_rec.comments,
2891 X_PARTY_SITE_ID => x_party_site_use_rec.party_site_id,
2892 X_SITE_USE_TYPE => x_party_site_use_rec.site_use_type,
2893 X_PRIMARY_PER_TYPE => x_party_site_use_rec.primary_per_type,
2894 X_STATUS => x_party_site_use_rec.status,
2895 X_CREATED_BY_MODULE => x_party_site_use_rec.created_by_module,
2896 X_APPLICATION_ID => x_party_site_use_rec.application_id
2897 );
2898
2899 --Standard call to get message count and if count is 1, get message info.
2900 FND_MSG_PUB.Count_And_Get(
2901 p_encoded => FND_API.G_FALSE,
2902 p_count => x_msg_count,
2903 p_data => x_msg_data );
2904
2905 EXCEPTION
2906 WHEN FND_API.G_EXC_ERROR THEN
2907 x_return_status := FND_API.G_RET_STS_ERROR;
2908
2909 FND_MSG_PUB.Count_And_Get(
2910 p_encoded => FND_API.G_FALSE,
2911 p_count => x_msg_count,
2912 p_data => x_msg_data );
2913
2914 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2916
2917 FND_MSG_PUB.Count_And_Get(
2918 p_encoded => FND_API.G_FALSE,
2919 p_count => x_msg_count,
2920 p_data => x_msg_data );
2921
2922 WHEN OTHERS THEN
2923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2924 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
2925 FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
2926 FND_MSG_PUB.ADD;
2927 FND_MSG_PUB.Count_And_Get(
2928 p_encoded => FND_API.G_FALSE,
2929 p_count => x_msg_count,
2930 p_data => x_msg_data );
2931
2932 END get_party_site_use_rec;
2933
2934 --
2935 --- Following procedures are introduced in account and party site sync
2936 --
2937 /*===========================================================================+
2938 | PROCEDURE
2939 | update_acct_sites_status
2940 |
2941 | DESCRIPTION
2942 | Updates acct site status.
2943 |
2944 | SCOPE - PRIVATE
2945 |
2946 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
2947 |
2948 | ARGUMENTS : IN:
2949 | OUT:
2950 | IN/ OUT:
2951 | p_party_site_use_rec
2952 | x_return_status
2953 |
2954 | RETURNS : NONE
2955 |
2956 | NOTES
2957 |
2958 | MODIFICATION HISTORY
2959 +===========================================================================*/
2960
2961 PROCEDURE update_acct_sites_status(
2962 p_party_site_id IN NUMBER,
2963 p_new_status IN VARCHAR2,
2964 x_return_status IN OUT NOCOPY VARCHAR2
2965 ) IS
2966
2967 l_object_version_number NUMBER;
2968 l_rowid ROWID;
2969 l_party_id NUMBER;
2970 l_party_site_id NUMBER;
2971 l_site_use_type HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE;
2972 l_primary_per_type HZ_PARTY_SITE_USES.PRIMARY_PER_TYPE%TYPE;
2973 l_debug_prefix VARCHAR2(30) := '';
2974
2975 BEGIN
2976
2977 -- Debug info.
2978 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2979 hz_utility_v2pub.debug(p_message=> 'update_acct_sites_status (+)',
2980 p_prefix=>l_debug_prefix,
2981 p_msg_level=>fnd_log.level_procedure);
2982 END IF;
2983
2984 -- check whether record has been updated by another user. If not, lock it.
2985 BEGIN
2986 UPDATE HZ_CUST_ACCT_SITES_ALL
2987 SET STATUS = p_new_status,
2988 bill_to_flag = NULL,
2989 ship_to_flag = NULL,
2990 market_flag = NULL,
2991 last_update_date = hz_utility_v2pub.last_update_date,
2992 last_updated_by = hz_utility_v2pub.last_updated_by,
2993 last_update_login = hz_utility_v2pub.last_update_login,
2994 request_id = hz_utility_v2pub.request_id,
2995 program_id = hz_utility_v2pub.program_id,
2996 program_application_id = hz_utility_v2pub.program_application_id,
2997 program_update_date = hz_utility_v2pub.program_update_date
2998 WHERE PARTY_SITE_ID = p_party_site_id;
2999
3000 EXCEPTION WHEN NO_DATA_FOUND THEN
3001 x_return_status := FND_API.G_RET_STS_ERROR;
3002 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
3003 FND_MESSAGE.SET_TOKEN('RECORD', 'Account sites');
3004 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_id), 'null'));
3005 FND_MSG_PUB.ADD;
3006 RAISE FND_API.G_EXC_ERROR;
3007 END;
3008
3009 -- Debug info.
3010 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3011 hz_utility_v2pub.debug(p_message=>'update_acct_sites_status (-)',
3012 p_prefix=>l_debug_prefix,
3013 p_msg_level=>fnd_log.level_procedure);
3014 END IF;
3015
3016 END update_acct_sites_status;
3017
3018 /*===========================================================================+
3019 | PROCEDURE
3020 | inactivate_party_site_uses
3021 |
3022 | DESCRIPTION
3023 | Updates party site uses status.
3024 |
3025 | SCOPE - PRIVATE
3026 |
3027 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3028 |
3029 | ARGUMENTS : IN:
3030 | OUT:
3031 | IN/ OUT:
3032 | x_return_status
3033 |
3034 | RETURNS : NONE
3035 |
3036 | NOTES
3037 |
3038 | MODIFICATION HISTORY
3039 +===========================================================================*/
3040
3041 PROCEDURE inactivate_party_site_uses(
3042 p_party_site_id IN NUMBER,
3043 p_new_status IN VARCHAR2,
3044 x_return_status IN OUT NOCOPY VARCHAR2
3045 ) IS
3046
3047 l_object_version_number NUMBER;
3048 l_party_site_id NUMBER;
3049 l_site_use_type HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE;
3050 l_primary_per_type HZ_PARTY_SITE_USES.PRIMARY_PER_TYPE%TYPE;
3051 l_debug_prefix VARCHAR2(30) := '';
3052 /* Bug Fix: 4515314 */
3053 l_party_site_use_rec HZ_PARTY_SITE_V2PUB.party_site_use_rec_type;
3054 l_party_site_use_id HZ_PARTY_SITE_USES.party_site_use_id%type;
3055 l_msg_count number;
3056 l_msg_data varchar2(2000);
3057
3058 cursor c_active_use is
3059 select party_site_use_id , object_version_number
3060 from hz_party_site_uses
3061 where status = 'A'
3062 and party_site_id = p_party_site_id;
3063 BEGIN
3064
3065 -- Debug info.
3066 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3067 hz_utility_v2pub.debug(p_message=>'inactivate_party_site_uses (+)',
3068 p_prefix=>l_debug_prefix,
3069 p_msg_level=>fnd_log.level_procedure);
3070 END IF;
3071
3072 -- check whether record has been updated by another user. If not, lock it.
3073 BEGIN
3074
3075 open c_active_use;
3076 loop
3077 fetch c_active_use into l_party_site_use_id,l_object_version_number;
3078 exit when c_active_use%notfound;
3079 l_party_site_use_rec.party_site_use_id := l_party_site_use_id;
3080 l_party_site_use_rec.status := 'I';
3081 HZ_PARTY_SITE_V2PUB.update_party_site_use
3082 (p_init_msg_list => FND_API.G_TRUE,
3083 p_party_site_use_rec => l_party_site_use_rec,
3084 p_object_version_number => l_object_version_number,
3085 x_return_status => x_return_status,
3086 x_msg_count => l_msg_count,
3087 x_msg_data => l_msg_data);
3088
3089 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3090 RAISE FND_API.G_EXC_ERROR;
3091 END IF;
3092 end loop;
3093 close c_active_use;
3094
3095 /* UPDATE HZ_PARTY_SITE_USES
3096 SET STATUS = p_new_status,
3097 PRIMARY_PER_TYPE = null,
3098 last_update_date = hz_utility_v2pub.last_update_date,
3099 last_updated_by = hz_utility_v2pub.last_updated_by,
3100 last_update_login = hz_utility_v2pub.last_update_login,
3101 request_id = hz_utility_v2pub.request_id,
3102 program_id = hz_utility_v2pub.program_id,
3103 program_application_id = hz_utility_v2pub.program_application_id,
3104 program_update_date = hz_utility_v2pub.program_update_date
3105 WHERE PARTY_SITE_ID = p_party_site_id;*/
3106
3107 EXCEPTION WHEN NO_DATA_FOUND THEN
3108 x_return_status := FND_API.G_RET_STS_ERROR;
3109 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
3110 FND_MESSAGE.SET_TOKEN('RECORD', 'party site use');
3111 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_id), 'null'));
3112 FND_MSG_PUB.ADD;
3113 RAISE FND_API.G_EXC_ERROR;
3114 END;
3115
3116 -- Debug info.
3117 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3118 hz_utility_v2pub.debug(p_message=>'inactivate_party_site_uses (-)',
3119 p_prefix=>l_debug_prefix,
3120 p_msg_level=>fnd_log.level_procedure);
3121 END IF;
3122
3123 END inactivate_party_site_uses;
3124
3125 /*===========================================================================+
3126 | PROCEDURE
3127 | update_denorm_prim_flag
3128 |
3129 | DESCRIPTION
3130 | Updates primary flag in the acct site uses and denormalize in the
3131 | related cust acct site.
3132 |
3133 | SCOPE - PRIVATE
3134 |
3135 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3136 |
3137 | ARGUMENTS : IN: p_site_use_id , p_site_use_code
3138 | OUT:
3139 | IN/ OUT:
3140 |
3141 | RETURNS : NONE
3142 |
3143 | NOTES
3144 |
3145 | MODIFICATION HISTORY
3146 ===========================================================================+*/
3147
3148 PROCEDURE update_denorm_prim_flag (
3149 p_site_use_id IN NUMBER,
3150 p_site_use_code IN VARCHAR2
3151
3152 ) IS
3153
3154 l_debug_prefix VARCHAR2(30) := ''; --'denormalize_site_use_flag'
3155 l_cust_acct_site_id HZ_CUST_ACCT_SITES_ALL.cust_acct_site_id%TYPE;
3156
3157 BEGIN
3158
3159 -- Debug info.
3160 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3161 hz_utility_v2pub.debug(p_message=>'denormalize_site_use_flag (+)',
3162 p_prefix=>l_debug_prefix,
3163 p_msg_level=>fnd_log.level_procedure);
3164 END IF;
3165
3166 UPDATE HZ_CUST_SITE_USES_ALL
3167 SET PRIMARY_FLAG = 'Y',
3168 last_update_date = hz_utility_v2pub.last_update_date,
3169 last_updated_by = hz_utility_v2pub.last_updated_by,
3170 last_update_login = hz_utility_v2pub.last_update_login,
3171 request_id = hz_utility_v2pub.request_id,
3172 program_id = hz_utility_v2pub.program_id,
3173 program_application_id = hz_utility_v2pub.program_application_id,
3174 program_update_date = hz_utility_v2pub.program_update_date
3175 WHERE SITE_USE_ID = p_site_use_id
3176 RETURNING CUST_ACCT_SITE_ID INTO l_cust_acct_site_id;
3177
3178 IF p_site_use_code = 'BILL_TO' THEN
3179
3180 UPDATE HZ_CUST_ACCT_SITES_ALL
3181 SET BILL_TO_FLAG = 'P',
3182 last_update_date = hz_utility_v2pub.last_update_date,
3183 last_updated_by = hz_utility_v2pub.last_updated_by,
3184 last_update_login = hz_utility_v2pub.last_update_login,
3185 request_id = hz_utility_v2pub.request_id,
3186 program_id = hz_utility_v2pub.program_id,
3187 program_application_id = hz_utility_v2pub.program_application_id,
3188 program_update_date = hz_utility_v2pub.program_update_date
3189 WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
3190
3191 ELSIF p_site_use_code = 'SHIP_TO' THEN
3192
3193 UPDATE HZ_CUST_ACCT_SITES_ALL
3194 SET SHIP_TO_FLAG = 'P',
3195 last_update_date = hz_utility_v2pub.last_update_date,
3196 last_updated_by = hz_utility_v2pub.last_updated_by,
3197 last_update_login = hz_utility_v2pub.last_update_login,
3198 request_id = hz_utility_v2pub.request_id,
3199 program_id = hz_utility_v2pub.program_id,
3200 program_application_id = hz_utility_v2pub.program_application_id,
3201 program_update_date = hz_utility_v2pub.program_update_date
3202 WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
3203
3204 ELSIF p_site_use_code = 'MARKET' THEN
3205
3206 UPDATE HZ_CUST_ACCT_SITES_ALL
3207 SET MARKET_FLAG = 'P',
3208 last_update_date = hz_utility_v2pub.last_update_date,
3209 last_updated_by = hz_utility_v2pub.last_updated_by,
3210 last_update_login = hz_utility_v2pub.last_update_login,
3211 request_id = hz_utility_v2pub.request_id,
3212 program_id = hz_utility_v2pub.program_id,
3213 program_application_id = hz_utility_v2pub.program_application_id,
3214 program_update_date = hz_utility_v2pub.program_update_date
3215 WHERE CUST_ACCT_SITE_ID = l_cust_acct_site_id;
3216
3217 END IF;
3218
3219 -- Debug info.
3220 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3221 hz_utility_v2pub.debug(p_message=>'denormalize_site_use_flag (-)',
3222 p_prefix=>l_debug_prefix,
3223 p_msg_level=>fnd_log.level_procedure);
3224 END IF;
3225
3226 END update_denorm_prim_flag;
3227
3228
3229 /*===========================================================================+
3230 | PROCEDURE
3231 | inactivate_account_site_uses
3232 |
3233 | DESCRIPTION
3234 | Updates account site uses status.
3235 |
3236 | SCOPE - PRIVATE
3237 |
3238 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3239 |
3240 | ARGUMENTS : IN:
3241 | OUT:
3242 | IN/ OUT:
3243 | p_party_site_use_rec
3244 | x_return_status
3245 |
3246 | RETURNS : NONE
3247 |
3248 | NOTES
3249 |
3250 | MODIFICATION HISTORY
3251 +===========================================================================*/
3252
3253 PROCEDURE inactivate_account_site_uses(
3254 p_party_site_id IN NUMBER,
3255 p_new_status IN VARCHAR2,
3256 x_return_status IN OUT NOCOPY VARCHAR2
3257 ) IS
3258
3259 l_object_version_number NUMBER;
3260 l_rowid ROWID;
3261 l_party_id NUMBER;
3262 l_party_site_id NUMBER;
3263 l_site_use_type HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE;
3264 l_primary_per_type HZ_PARTY_SITE_USES.PRIMARY_PER_TYPE%TYPE;
3265 l_msg_count NUMBER;
3266 l_msg_data VARCHAR2(2000);
3267 l_debug_prefix VARCHAR2(30) := '';
3268 /* Bug 4515314 */
3269 l_site_use_id HZ_CUST_SITE_USES.SITE_USE_ID%TYPE;
3270 l_site_use_code HZ_CUST_SITE_USES.SITE_USE_CODE%TYPE;
3271 /* c_prim will pick the candidate acct site uses to be marked as primary
3272 once the acct sites sharing the p_party_site_id are made inactive
3273 even for multiple accounts sharing p_party_site_id */
3274 cursor c_prim is
3275 SELECT MIN(b.site_use_id) , site_use_code
3276 FROM hz_cust_acct_sites_all a , hz_cust_site_uses_all b
3277 WHERE a.cust_account_id in (select cust_account_id
3278 from hz_cust_acct_sites_all cas
3279 where cas.party_site_id = p_party_site_id)
3280 AND a.cust_acct_site_id = b.cust_acct_site_id
3281 AND b.status = 'A'
3282 AND a.status = 'A'
3283 GROUP BY a.cust_Account_id,b.org_id,b.site_use_code
3284 HAVING MAX(nvl(primary_flag,'N')) = 'N';
3285
3286
3287 BEGIN
3288
3289 -- Debug info.
3290 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3291 hz_utility_v2pub.debug(p_message=> 'inactivate_account_site_uses (+)',
3292 p_prefix=>l_debug_prefix,
3293 p_msg_level=>fnd_log.level_procedure);
3294 END IF;
3295
3296 -- check whether record has been updated by another user. If not, lock it.
3297 BEGIN
3298 UPDATE HZ_CUST_SITE_USES_ALL
3299 SET STATUS = p_new_status,
3300 primary_flag = 'N', --Bug 3370874
3301 last_update_date = hz_utility_v2pub.last_update_date,
3302 last_updated_by = hz_utility_v2pub.last_updated_by,
3303 last_update_login = hz_utility_v2pub.last_update_login,
3304 request_id = hz_utility_v2pub.request_id,
3305 program_id = hz_utility_v2pub.program_id,
3306 program_application_id = hz_utility_v2pub.program_application_id,
3307 program_update_date = hz_utility_v2pub.program_update_date
3308 WHERE CUST_ACCT_SITE_ID IN (
3309 SELECT CUST_ACCT_SITE_ID
3310 FROM HZ_CUST_ACCT_SITES_ALL
3311 WHERE PARTY_SITE_ID = p_party_site_id);
3312 /*Bug Fix: 4515314*/
3313 OPEN c_prim;
3314 LOOP
3315 FETCH c_prim into l_site_use_id , l_site_use_code;
3316 EXIT WHEN c_prim%NOTFOUND;
3317 update_denorm_prim_flag (l_site_use_id , l_site_use_code);
3318 END LOOP;
3319 CLOSE c_prim;
3320
3321 EXCEPTION WHEN NO_DATA_FOUND THEN
3322 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
3323 FND_MESSAGE.SET_TOKEN('RECORD', 'Party site use');
3324 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_party_site_id), 'null'));
3325 FND_MSG_PUB.ADD;
3326 RAISE FND_API.G_EXC_ERROR;
3327 END;
3328
3329 -- Debug info.
3330 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3331 hz_utility_v2pub.debug(p_message=>'inactivate_account_site_uses (-)',
3332 p_prefix=>l_debug_prefix,
3333 p_msg_level=>fnd_log.level_procedure);
3334 END IF;
3335
3336 END inactivate_account_site_uses;
3337
3338 /*===========================================================================+
3339 | PROCEDURE
3340 | cascade_site_status_changes
3341 |
3342 | DESCRIPTION
3343 | It cascades activation of activation to Account Sites and
3344 | Inactivation of to Party Site Uses,Account Sites and
3345 | Account Site Uses.
3346 |
3347 | SCOPE - PRIVATE
3348 |
3349 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
3350 |
3351 | ARGUMENTS : IN:
3352 | OUT:
3353 | IN/ OUT:
3354 | x_return_status
3355 |
3356 | RETURNS : NONE
3357 |
3358 | NOTES
3359 |
3360 | MODIFICATION HISTORY
3361 +===========================================================================*/
3362
3363 PROCEDURE cascade_site_status_changes(
3364 p_party_site_id IN NUMBER,
3365 p_new_status IN VARCHAR2,
3366 x_return_status IN OUT NOCOPY VARCHAR2
3367 ) IS
3368
3369 l_object_version_number NUMBER;
3370 l_rowid ROWID;
3371 l_party_id NUMBER;
3372 l_party_site_id NUMBER;
3373 l_site_use_type HZ_PARTY_SITE_USES.SITE_USE_TYPE%TYPE;
3374 l_primary_per_type HZ_PARTY_SITE_USES.PRIMARY_PER_TYPE%TYPE;
3375 l_msg_count NUMBER;
3376 l_msg_data VARCHAR2(2000);
3377 l_debug_prefix VARCHAR2(30) := '';
3378
3379 BEGIN
3380
3381 -- Debug info.
3382 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3383 hz_utility_v2pub.debug(p_message=>'cascade_site_status_changes (+)',
3384 p_prefix=>l_debug_prefix,
3385 p_msg_level=>fnd_log.level_procedure);
3386 END IF;
3387
3388 --
3389 --- Call update_acct_sites_status procedure
3390 --
3391 update_acct_sites_status(p_party_site_id,p_new_status,x_return_status);
3392
3393 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3394 RAISE FND_API.G_EXC_ERROR;
3395 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3396 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3397 END IF;
3398 --
3399 --- If Inactivating Party Site Uses
3400 --
3401 IF x_return_status = FND_API.G_RET_STS_SUCCESS and p_new_status = 'I' THEN
3402 inactivate_party_site_uses(p_party_site_id,p_new_status,x_return_status);
3403 --
3404 --- Inactivate account site uses
3405 --
3406 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3407 inactivate_account_site_uses(p_party_site_id,p_new_status,x_return_status);
3408 END IF;
3409 END IF;
3410 -- Debug info.
3411 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
3412 hz_utility_v2pub.debug(p_message=>'cascade_site_status_changes (-)',
3413 p_prefix=>l_debug_prefix,
3414 p_msg_level=>fnd_log.level_procedure);
3415 END IF;
3416 EXCEPTION
3417 WHEN FND_API.G_EXC_ERROR THEN
3418 x_return_status := FND_API.G_RET_STS_ERROR;
3419
3420 -- FND_MSG_PUB.Count_And_Get(
3421 -- p_encoded => FND_API.G_FALSE,
3422 -- p_count => x_msg_count,
3423 -- p_data => x_msg_data );
3424
3425 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3427
3428 -- FND_MSG_PUB.Count_And_Get(
3429 -- p_encoded => FND_API.G_FALSE,
3430 -- p_count => x_msg_count,
3431 -- p_data => x_msg_data );
3432
3433 WHEN OTHERS THEN
3434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3435 -- FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OTHERS_EXCEP' );
3436 -- FND_MESSAGE.SET_TOKEN( 'ERROR' ,SQLERRM );
3437 -- FND_MSG_PUB.ADD;
3438 -- FND_MSG_PUB.Count_And_Get(
3439 -- p_encoded => FND_API.G_FALSE,
3440 -- p_count => x_msg_count,
3441 -- p_data => x_msg_data );
3442
3443 END cascade_site_status_changes;
3444
3445 /**
3446 * PRIVATE PROCEDURE check_obsolete_columns
3447 *
3448 * DESCRIPTION
3449 * Check if user is using obsolete columns.
3450 *
3451 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
3452 *
3453 * MODIFICATION HISTORY
3454 *
3455 * 07-25-2005 Jianying Huang o Created.
3456 *
3457 */
3458
3459 PROCEDURE check_obsolete_columns (
3460 p_create_update_flag IN VARCHAR2,
3461 p_party_site_rec IN party_site_rec_type,
3462 p_old_party_site_rec IN party_site_rec_type DEFAULT NULL,
3463 x_return_status IN OUT NOCOPY VARCHAR2
3464 ) IS
3465
3466 BEGIN
3467
3468 -- check language
3469 IF (p_create_update_flag = 'C' AND
3470 p_party_site_rec.language IS NOT NULL AND
3471 p_party_site_rec.language <> FND_API.G_MISS_CHAR) OR
3472 (p_create_update_flag = 'U' AND
3473 p_party_site_rec.language IS NOT NULL AND
3474 p_party_site_rec.language <> p_old_party_site_rec.language)
3475 THEN
3476 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OBSOLETE_COLUMN');
3477 FND_MESSAGE.SET_TOKEN('COLUMN', 'language');
3478 FND_MSG_PUB.ADD;
3479 x_return_status := FND_API.G_RET_STS_ERROR;
3480 END IF;
3481
3482 END check_obsolete_columns;
3483
3484 END HZ_PARTY_SITE_V2PUB;