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